Обзор встроенных функций MS Excel

• Введение
• 1. Теоретическая часть
• 1.1 Возможности Excel при работе с функциями
• 1.2 Математические функции
• 1.2.1 Функция СУММ
• 1.2.2 Функция ОКРУГЛ
• 1.2.3 Функция ПРОИЗВЕД
• 1.2.4 Функция КОРЕНЬ
• 1.2.5 Функция ЕЧИСЛО
• 1.3 Логарифмические функции
• 1.3.1 Функция LOG
• 1.3.2 Функция LN
• 1.3.3 Функция ЕХР
• 1.4 Текстовые функции
• 1.4.1 Функция ТЕКСТ
• 1.4.2 Функция СОВПАД
• 1.4.3 Функции ЕТЕКСТ и ЕНЕТЕКСТ
• 1.5 Логические функции
• 1.5.1 Логические выражения
• 1.5.2 Функция ЕСЛИ
• 1.5.3 Функции И, ИЛИ и НЕ
• 2. Практическая часть
• 2.1 Общая характеристика задачи
• 2.2 Описание алгоритма решения задачи
• Список использованной литературы

Курсовая работа состоит из двух частей — теоретической и практической.

В теоретической части рассматривается тема «Обзор встроенных функций MS Excel», которая является весьма актуальной, потому, что табличные редакторы на сегодняшний день, одни из самых распространенных программных продуктов, используемые во всем мире. Они без специальных навыков позволяют создавать достаточно сложные приложения, которые удовлетворяют до 90% запросов средних пользователей.

В практической части помощью пакетов прикладных программ (ППП) будут решены и описаны следующие задачи:

· создание таблиц и заполнение таблиц данными;

· применение математических формул для выполнения запросов в ППП;

· построение графиков

Технические средства персонального компьютера, использованного для выполнения курсовой работы:

процессор: CPU INTEL Pentium IV 2400 Мгц;

оперативная память: SD RAM 512 Мб;

жесткий диск: HDD 120 Гб;

Программные средства:

· Операционная система Windows XP;

· Microsoft Word 2007;

· Microsoft Excel 2007.

компьютер таблица excel график

1. Теоретическая часть

1.1 Возможности Excel при работе с функциями

Функции в Excel используются для выполнения стандартных вычислений в рабочих книгах. Значения, которые используются для вычисления функций, называются аргументами. Значения, возвращаемые функциями в качестве ответа, называются результатами. Помимо встроенных функций вы можете использовать в вычислениях пользовательские функции, которые создаются при помощи средств Excel. Чтобы использовать функцию, нужно ввести ее как часть формулы в ячейку рабочего листа. Последовательность, в которой должны располагаться используемые в формуле символы, называется синтаксисом функции. Все функции используют одинаковые основные правила синтаксиса. Если вы нарушите правила синтаксиса, Excel выдаст сообщение о том, что в формуле имеется ошибка.

Если функция появляется в самом начале формулы, ей должен предшествовать знак равенства, как и во всякой другой формуле.

Аргументы функции записываются в круглых скобках сразу за названием функции и отделяются друг от друга символом точка с запятой «;». Скобки позволяют Excel определить, где начинается и где заканчивается список аргументов. Внутри скобок должны располагаться аргументы. Помните о том, что при записи функции должны присутствовать открывающая и закрывающая скобки, при этом не следует вставлять пробелы между названием функции и скобками.

В качестве аргументов можно использовать числа, текст, логические значения, массивы, значения ошибок или ссылки. Аргументы могут быть как константами, так и формулами. В свою очередь эти формулы могут содержать другие функции. Функции, являющиеся аргументом другой функции, называются вложенными. В формулах Excel можно использовать до семи уровней вложенности функций.

Задаваемые входные параметры должны иметь допустимые для данного аргумента значения. Некоторые функции могут иметь необязательные аргументы, которые могут отсутствовать при вычислении значения функции.

Типы функций:

Для удобства работы функции в Excel разбиты по категориям: функции управления базами данных и списками, функции даты и времени, DDE/Внешние функции, инженерные функции, финансовые, информационные, логические, функции просмотра и ссылок. Кроме того, присутствуют следующие категории функций: статистические, текстовые и математические.

При помощи текстовых функций имеется возможность обрабатывать текст: извлекать символы, находить нужные, записывать символы в строго определенное место текста и многое другое.

Логические функции помогают создавать сложные формулы, которые в зависимости от выполнения тех или иных условий будут совершать различные виды обработки данных.

В Excel широко представлены математические функции. Например, можно выполнять различные операции с матрицами: умножать, находить обратную, транспонировать.

Функции просмотра и ссылок позволяет «просматривать» информацию, хранящуюся в списке или таблице, а также обрабатывать ссылки.

1.2 Математические функции

В Microsoft Excel имеется целый ряд встроенных математических функций, позволяющих легко и быстро выполнять различные специализированные вычисления. Кроме того, множество математических функций включено в надстройку Пакет анализа.

1.2.1 Функция СУММ

Функция СУММ суммирует множество чисел. Эта функция имеет следующий синтаксис: =СУММ (числа).

Аргумент числа может включать до 30 элементов, каждый из которых может быть числом, формулой, диапазоном или ссылкой на ячейку, содержащую ли возвращающую числовое значение. Функция СУММ игнорирует аргументы, которые ссылаются на пустые ячейки, текстовые или логические значения.

Поскольку СУММ является очень популярной функцией, на стандартной панели инструментов имеется специальная кнопка для ввода этой функции. Если вы выделите ячейку и нажмете кнопку Автосумма (кнопка с символом (), Ехсеl создаст формулу =СУММ () и предложит ячейки для суммирования. Например, если выделить ячейку С16 в листе, представленном ранее на рис. 1, и затем нажать кнопку Авто сумма, Excel предложит формулу =СУММ (С4: С15) и выведет подвижную рамку вокруг диапазона ячеек, использованного в формуле в качестве аргумента.

Если предложенный аргумент функции СУММ является правильным, нажмите клавишу Enter или еще раз кнопку Автосумма для фиксации ввода формулы и удаления подвижной рамки. Если предложенный аргумент неверен, его можно изменить, выделив, пока присутствует подвижная рамка, правильный диапазон ячеек. Excel заменит предложенный аргумент выделенным диапазоном и перерисует подвижную рамку вокруг вашего выделения.

1.2.2 Функция ОКРУГЛ

Функция ОКРУГЛ округляет число, задаваемое ее аргументом, до указанного количества десятичных разрядов и имеет следующий синтаксис: =ОКРУГЛ (число; количество_цифр)

Аргумент число может быть числом, ссылкой на ячейку, в которой содержится число, или формулой, возвращающей числовое значение. Аргумент количество_цифр, который может быть любым положительным или отрицательным целым числом, определяет, сколько цифр будет округляться. Задание отрицательного аргумента количество_цифр округляет до указанного количества разрядов слева от десятичной запятой, а задание аргумента количество_цифр равным 0 округляет до ближайшего целого числа. Excel округляет цифры, которые меньше 5, с недостатком (вниз), а цифры, которые больше или равны 5, с избытком (вверх). Следующая таблица содержит несколько примеров действия функции ОКРУГЛ.

Функции ОКРУГЛВНИЗ и ОКРУГЛВВЕРХ имеют такой же синтаксис, как и функция ОКРУГЛ. Как указывают их имена, они округляют значения вниз (с недостатком) или вверх (с избытком).

1.2.3 Функция ПРОИЗВЕД

Функция ПРОИЗВЕД перемножает все числа, задаваемые ее аргументами, и имеет следующий синтаксис: =ПРОИЗВЕД (число1; число2;.). Функция ПРОИЗВЕД может иметь до 30 аргументов. Excel игнорирует любые пустые ячейки, текстовые и логические значения.

1.2.4 Функция КОРЕНЬ

Функция КОРЕНЬ возвращает положительный квадратный корень числа и имеет следующий синтаксис: =КОРЕНЬ (число)

Аргумент число должен быть положительным числом. Например, следующая функция возвращает значение 2: =КОРЕНЬ (4)

Если число отрицательное, КОРЕНЬ возвращает ошибочное значение: =#ЧИСЛО!

1.2.5 Функция ЕЧИСЛО

Функция ЕЧИСЛО определяет, является ли значение число и имеет следующий синтаксис: =ЕЧИСЛО (значение)

Предположим, что вы хотите узнать, является ли значение в ячейке А5 числом. Следующая формула возвращает значение ИСТИНА, если ячейка А5 содержит число или формулу, возвращающую число; в противном случае она возвращает ЛОЖЬ: =ЕЧИСЛО (А5)

1.3 Логарифмические функции

Excel поддерживает пять встроенных логарифмических функций: LOG10, LOG, LN, ЕХР и СТЕПЕНЬ. В этом разделе я расскажу только про функции LOG, LN и ЕХР. Надстройка Пакет анализа предоставляет еще несколько дополнительных и более сложных логарифмических функций.

1.3.1 Функция LOG

Функция LOG возвращает логарифм положительного числа по заданному основанию. Эта функция имеет следующий синтаксис: =LOG (число; основание')

Например, следующая формула возвращает значение 2,321 928 095, то есть логарифм 5 по основанию 2: =LOG (5;

2) Если вы не укажете аргумент основание, Excel примет его равным 10.

1.3.2 Функция LN

Функция LN возвращает натуральный (по основанию е) логарифм положительного числа, указанного в качестве аргумента. Эта функция имеет следующий синтаксис: =LN (число). Например, следующая формула возвращает значение 0,693 147 181: =LN (2)

1.3.3 Функция ЕХР

Функция ЕХР вычисляет значение константы е (приблизительно 2,71 828 183), возведенной в заданную степень. Эта функция имеет следующий синтаксис: =ЕХР (число) Например, следующая формула возвращает значение 7,389 056 099 (12,718 281 828×2,718 281 828): =ЕХР (2). Функция ЕХР является обратной по отношению к LN. Например, пусть ячейка А1 содержит формулу =LN (8), тогда следующая формула возвращает значение 8: =ЕХР (А1)

1.4 Текстовые функции

Текстовые функции преобразуют числовые текстовые значения в числа и числовые значения в строки символов (текстовые строки), а также позволяют выполнять над строками символов различные операции.

1.4.1 Функция ТЕКСТ

Функция ТЕКСТ преобразует число в текстовую строку с заданным форматом. Эта функция имеет следующий синтаксис: =ТЕКСТ (значение; форма). Аргумент значение может быть любым числом, формулой или ссылкой на ячейку. Аргумент формат определяет, в каком виде отображается возвращаемая строка. Для задания необходимого формата

можно использовать любой из символов форматирования ($, #, 0 и т. д.) за исключением звездочки (*). Использование формата Общий не допускается. Например, следующая формула возвращает текстовую строку 24,50: =ТЕКСТ (98/4; ''0,00'').

1.4.2 Функция СОВПАД

Функция СОВПАД сравнивает две строки текста на полную идентичность с учетом регистра букв. Различие в форматировании игнорируется. Эта функция имеет следующий синтаксис: =СОВПАД (текст1; текст.2).

Если аргументы текст 1 и текст2 идентичны с учетом регистра букв, функция СОВПАД возвращает значение ИСТИНА, в противном случае СОВПАД возвращает ЛОЖЬ. Аргументы текст1 и текст2 должны быть строками символов, заключенными в двойные кавычки, или ссылками на ячейки, в. которых содержится текст. Например, если ячейки А5 и А6 рабочего листа содержат один и тот же текст Итого, следующая формула возвращает значение ИСТИНА: =СОВПАД (А5; А6)

1.4.3 Функции ЕТЕКСТ и ЕНЕТЕКСТ

Функции ЕТЕКСТ и ЕНЕТЕКСТ проверяют, является ли значение текстовым. Эти функции имеют следующий синтасис: =ЕТЕКСТ (значение), =ЕНЕТЕКСТ (значение).

Предположим, вы хотите определить, является ли значение в ячейке С5 текстом. Если в ячейке С5 находится текст или формула, которая возвращает текст, можно использовать формулу: =ЕТЕКСТ (С5). В этом случае Excel возвращает логическое значение ИСТИНА. Аналогично, если вы проверите ту же ячейку, используя формулу =ЕНЕТЕКСТ (С5) Excel возвращает логическое значение ЛОЖЬ.

1.5 Логические функции

Microsoft Excel имеет богатый набор логических функций, некоторые из них включены в надстройку Пакет анализа. Большинство логических функций используют логические выражения для определения истинности заданного условия.

1.5.1 Логические выражения

Логические выражения используются для записи условий, в которых сравниваются числа, функции, формулы, текстовые или логические значения. Например, каждая из представленных ниже формул является логическим выражением:

=А1>А2; =5−3<5*2; =СРЗНАЧ (В1: В6); =СУММ (6; 7;

8); =С2="Среднее" =СЧЁТ (А1: А10); =СЧЁТ (В1: В10); =ДЛСТР (А1) =10

Любое логическое выражение должно содержать, по крайней мере, один оператор сравнения, который определяет отношение между элементами логического выражения. Например, в логическом выражении А1>А2 оператор больше (>) сравнивает значения в ячейках А1 и А2. Следующая таблица содержит список операторов сравнения Excel.

Список операторов сравнения Microsoft Excel.

Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0). Например, следующее логическое выражение возвращает значение ИСТИНА, если значение в ячейке Z1 равно 10, и ЛОЖЬ, если Z1 содержит любое другое значение: =Z1=10

1.5.2 Функция ЕСЛИ

Функция ЕСЛИ имеет следующий синтаксис:

=ЕСЛИ (логическое_выражение; значение_если_истина; значение_если_ложь)

Например, следующая формула возвращает число 5, если значение в ячейке А6 меньше 22: =ЕСЛИ (А6<22; 5;

10). В противном случае формула возвращает 10.

В качестве аргументов функции ЕСЛИ можно использовать другие функции. Например, следующая формула возвращает сумму значений в ячейках от А1 до А10, если эта сумма положительна: =ЕСЛИ (СУММ (А1: А10) >0; СУММ (А1: А10); 0). В противном случае формула возвращает 0.

В функции ЕСЛИ можно также использовать текстовые аргументы. Например, лист, представленный на рис. 7, содержит результаты экзаменов для группы студентов. Следующая формула в ячейке G4 проверяет средний балл, содержащийся в ячейке F4: =ЕСЛИ (С4>75%; «Сдал»; «Не сдал»).

Если средний балл оказывается больше 75%, функция возвращает текст Сдал; если же средний балл меньше или равен 75%, функция возвращает текст Не сдал.

Рис. 1. Функция ЕСЛИ возвращает текстовую строку

Вы можете использовать текстовые аргументы в функции ЕСЛИ, чтобы при невыполнении условия она возвращала пустую строку вместо 0. Например, следующая формула возвращает пустую строку (««), если логическое выражение имеет значение ЛОЖЬ: =ЕСЛИ (СУММ (А1: А10) >0; СУММ (А1: А10); «»). Аргумент логическое_выражение функции ЕСЛИ может содержать текстовое значение. Например, пусть имеется формула =ЕСЛИ (А1="Тест»; 100; 200)

Эта формула возвращает значение 100, если ячейка А1 содержит строку Тест, и 200, если в ней находится любое другое значение. Совпадение между сравниваемыми текстовыми значениями должно быть точным, но без учета регистра.

1.5.3 Функции И, ИЛИ и НЕ

Три дополнительные функции — И, ИЛИ и НЕ — позволяют создавать сложные логические выражения. Эти функции работают в сочетании с простыми операторами сравнения: =, >, <, >=, <= и <>. Функции И и ИЛИ могут иметь до 30 логических аргументов и имеют следующий синтаксис:

=И (логическое_значение1; логическое_значение2;.; логическое_значениеЗО) =ИЛИ (логическое_значение1; логическое_значение2;.; логическое_значениеЗО) Функция НЕ имеет только один аргумент и следующий синтаксис: =НЕ (логическое_значенне)

Аргументы функций И, ИЛИ и НЕ могут быть логическими выражениями, массивами или ссылками на ячейки, содержащие логические значения.

Предположим, вы хотите, чтобы программа Excel возвратила текст Сдал, если студент имеет средний балл больше 75 и меньше 5 пропусков занятий без уважительных причин. В листе, представленном на рис. 8, мы использовали для этого формулу

=ЕСЛИ (И (С4<5; Р4>75); «Сдал»; «Не сдал»)

Рис. 2. Функция И позволяет создавать сложные логические выражения

Хотя функция ИЛИ имеет те же аргументы, что и И, результаты получаются совершенно различными. Например, следующая формула возвращает текст Сдал, если средний балл больше 75 или если студент имеет меньше 5 пропусков занятий без уважительных причин: =ЕСЛИ (ИЛИ (С4<5; Р4>75%); «Сдал»; «Не сдал»). Таким образом, функция ИЛИ возвращает логическое значение ИСТИНА, если хотя бы одно из логических выражений истинно, а функция И возвращает логическое значение ИСТИНА, только если все логические выражения истинны.

Функция НЕ меняет значение своего аргумента на противоположное логическое значение и обычно используется в сочетании с другими функциями. Эта функция возвращает логическое значение ИСТИНА, если аргумент имеет значение ЛОЖЬ, и логическое значение ЛОЖЬ, если аргумент имеет значение ИСТИНА. Например, следующая формула возвращает текст Прошел, если значение в ячейке А1 не равно 2: =ЕСЛИ (НЕ (А1=2); «Прошел»; «Не прошел»)

2. Практическая часть

2.1 Общая характеристика задачи

Агентство по грузоперевозкам «Летучий голландец» предоставляет услуги по перевозке грузов по различным маршрутам. Данные о маршрутах, выполненных в течение недели, по каждому водителю приведены на рис. 1. Справочные данные о технических характеристиках автомобилей и протяженности маршрутов приведены на рис. 2.

Построить таблицы по приведенным ниже данным.

Выполнить расчет количества израсходованного топлива каждым водителем и веса перевезенного груза, данные расчета занести в таблицу (рис. 1).

Организовать межтабличные связи для автоматического формирования ведомости расхода топлива за неделю.

Сформировать и заполнить ведомость расхода горючего каждым водителем за неделю (рис. 3).

Результаты расчета количества израсходованного топлива за неделю представить в графическом виде.

Сведения о выполненных маршрутах

Рис. 1. Данные о выполненных маршрутах

Рис. 2. Технические характеристики автомобилей и данные о протяженности выполняемых рейсов

Рис. 3. Ведомость расхода горючего

2.2 Описание алгоритма решения задачи

1. Запустить табличный процессор MS Excel.

2. Создать книгу с именем «Горючее» .

3. Лист 1 переименовать в лист с названием «Маршруты» .

4. На рабочем листе «Маршруты» MS Excel создать таблицу с базовыми данными о выполненных маршрутах.

5. Заполнить таблицу с базовыми данными о выполненных маршрутах (рис. 1.).

Рис. 1. Расположение таблицы «Сведения о выполненных маршрутах» на рабочем листе «Маршруты» MS Excel

6. Лист 2 переименовать в лист с названием «Автомобили и рейсы» .

7. На рабочем листе «Автомобили и рейсы» MS Excel создать таблицы с базовыми данными о технических характеристиках автомобилей и с данными о протяженности рейсов.

8. Заполнить таблицы с базовыми данными о технических характеристиках автомобилей и с данными о протяженности рейсов (рис. 2.).

Рис. 2. Расположение таблиц «Технические характеристики автомобилей» и «Протяженность рейсов» на рабочем листе «Условно-постоянная информация» MS Excel

9. Разработать структуру шаблона таблицы «Ведомость зарплаты за месяц» (рис. 3.).

Рис. 3. Структура шаблона таблицы «Сведения о выполненных маршрутах»

10. Заполнить графу «Протяженность рейса, км.» таблицы «Сведения о выполненных маршрутах», находящейся на листе «Маршруты» следующим образом:

· Занести в ячейку F4 формулу:

=ПРОСМОТР (D4; 'Автомобили и рейсы '! G5: H8)

· Размножить введенную в ячейку F4 формулу для остальных ячеек (с F4 по F10) данной графы. Таким образом, будет выполнен цикл, управляющим параметром которого является номер строки.

· Занести в ячейку F11 формулу:

=СУММ (F4: F10)

· Занести в ячейку F12 формулу:

=СРЗНАЧ (F4: F10)

11. Заполнить аналогичным способом графу «Расход топлива на 100 км, л.» таблицы «Сведения о выполненных маршрутах», находящейся на листе «Маршруты» .

12. Заполнить графу «Израсходовано топлива, л.», таблицы «Сведения о выполненных маршрутах», находящейся на листе «Маршруты», следующим образом:

· Занести в ячейку H4 формулу:

=F4*G4/100

· Размножить введенную в ячейку H4 формулу для остальных ячеек (с H4 по H10) данной графы.

· Занести в ячейку H11 формулу:

=СУММ (H4: H10)

· Занести в ячейку H12 формулу:

=СРЗНАЧ (H4: H10)

13. Заполнить графу «Грузоподъемность, т.», таблицы «Сведения о выполненных маршрутах», находящейся на листе «Маршруты», следующим образом:

· Занести в ячейку I4 формулу:

=ПРОСМОТР (C4; Автомобили и рейсы '! B5: B7; 'Автомобили и рейсы'! D5: D7)

· Размножить введенную в ячейку I4 формулу для остальных ячеек (с I4 по I10) данной графы.

· Занести в ячейку I11 формулу:

=СУММ (I4: I10)

· Занести в ячейку I12 формулу:

=СРЗНАЧ (I4: I10)

14. Заполнить графу «Вес перевезенного груза, т», таблицы «Сведения о выполненных маршрутах», находящейся на листе «Маршруты», следующим образом:

· Занести в ячейку J4 формулу:

=E4*I4

Размножить введенную в ячейку J4 формулу для остальных ячеек (с J4 по J10) данной графы.

· Занести в ячейку J11 формулу:

=СУММ (J4: J10)

· Занести в ячейку J12 формулу:

=СРЗНАЧ (J4: J10)

15. Итоговая таблица «Сведения о выполненных маршрутах», находящаяся на листе «Маршруты» представлена на рисунке 4.

Рис. 3. Расположение итоговой таблицы «Сведения о выполненных маршрутах» на рабочем листе «Маршруты» MS Excel

16. Лист 3 переименовать в лист с названием «Ведомость» .

17. На рабочем листе «Ведомость» MS Excel создать ведомость расхода горючего.

18. Заполнить графу «№ рейса», таблицы «Ведомость расхода горючего», находящейся на листе «Ведомость», следующим образом:

· Занести в ячейку C12 формулу:

=Маршруты! D4

Размножить введенную в ячейку C12 формулу для остальных ячеек (с C12 по C19) данной графы.

19. Аналогичным способом заполнить графы «Выполнено рейсов, шт.» и «Израсходовано топлива, л.» таблицы «Ведомость расхода горючего», находящейся на листе «Ведомость» .

20. Выходная «ведомость расхода горючего» представлена на рисунке 4.

Рис. 4. Выходная «ведомость расхода горючего»

21. Лист 5 переименовать в лист с названием «График» .

22. Результаты вычислений представить графически (рис.5).

Рис. 5. Результаты вычислений в графическом виде

1. Вечерина Т. В. «Эффективная работа с Excel 2007», М., 2007.

2. Гаевский А. Ю. «Самоучитель работы на компьютере», М., 2008.

3. Леонтьев В. П. Новейшая энциклопедия персонального компьютера 2005 // Олма-Пресс, 2008.

4. Встроенная справка MS Excel 2007.

5. Информатика. Методические указания по выполнению и темы курсовых работ. Для студентов 2 курса всех специальностей. / ВЗФЭИ. М.: Финстатинформ, 2006.