Практическая работа № 5
Формулы и функции. Диаграммы и графики.
Цель занятия: научиться применять формулы, использовать функции СРЗНАЧ, МИН, МАКС, ЕСЛИ, РАНГ; выучить различные способы вызова функций; научиться строить графики и диаграммы, используя табличные данные.
Теоретические основы работы:
Использование функций.
Программа MS Excel предоставляет пользователю большое количество специальных функций для различных вычислений. Функция представляет собой формулу, выполняющую определённые операции. Обращение к функции происходит посредством указания её имени, после чего следуют круглые скобки, в которых указываются аргументы, отделяющиеся точкой с запятой. Если в качестве аргументов функции используются другие функции, то они называются вложенными.
Функции можно вызвать несколькими способами:
1. можно выполнить вызов функции, если непосредственно с клавиатуры ввести её имя, скобки и аргументы;
2. если в нужной ячейке поставить знак =, то в левой части строки формул вместо адреса ячейки появится раскрывающийся список, где будут 10 недавно использованных функций, а также слова «другие функции»;
3. можно воспользоваться значком 
на строке формул для вызова функции;
4. можно выполнить команду Вставка/Функция.
5. в раскрывающемся списке кнопки Автосумма 
на Стандартной панели инструментов есть некоторые функции, а также команда Другие функции.
Замечание: в случае некорректной записи функции появится сообщение об ошибке.
В случае вызова полного списка функций появится диалоговое окно (рис. 24).
Рис. 24
В данном диалоговом окне можно выбрать категорию Все функции, тогда появится полный перечень встроенных в программу функций, по каждой из которых можно прочитать справку. В справочной информации описывается, для чего применяется функция и даётся пример с её использованием.
|
|
Использование функции автосуммирования:
Программа Excel предлагает быстрый способ суммирования содержимого нескольких ячеек строки или столбца с помощью кнопки Автосумма , расположенной на Стандартной панели инструментов. Для суммирования значений ячеек строк или столбцов нужно выделить ячейки, значения которых подлежат суммированию, а также пустую ячейку строки или столбца, в которой будет размещаться результат. Затем щёлкнуть на кнопке Автосумма на Стандартной панели инструментов.
Использование функций СРЗНАЧ(), МИН(), МАКС():
Любым вышеописанным способом вызвать окно нужной функции на экран, после того как выделена ячейка для значения функции. Диапазон ячеек для подсчёта можно выделить мышью, а можно ввести с клавиатуры непосредственно в окно функции. Затем в окне функции нажать ОК. Для проверки выделить полученное значение и проверить запись функции в строке формул.
Использование функции ЕСЛИ:
В окне функции три строки, которые подлежат заполнению. В первой строке пишется условие, которое необходимо проверить. Во второй строке пишется выражение (это может быть текстовая строка), которое выполнится в том случае, если проверяемое условие является верным. В третьей строке пишется выражение (это может быть текстовая строка), которое выполнится в том случае, если проверяемое условие является неверным. Адреса ячеек можно вводить с клавиатуры, а можно выделять мышью.
|
|
Стандартные функции программы MS Excel:
| Категория | Назначение функций |
| Финансовые | Вычисляют процентные ставки, ежемесячные отчисления, амортизационные отчисления |
| Дата и время | Возвращают в различных форматах день недели, время и дату |
| Математические и тригонометрические | Определяют абсолютные величины, косинусы и логарифмы |
| Статистические | Вычисляют средние значения, наибольшее и наименьшее числа в диапазоне, коэффициенты распределения Стьюдента, тестируют на предмет независимости выборок |
| Ссылки и массивы | Вычисляют и возвращают значения из диапазона; создают гиперссылки для веб-документов |
| Работа с базой данных | Выполняют различного рода анализ данных, находящихся в списках или в базах данных |
| Текстовые | Преобразуют регистр текста, обрезают символы с правого или левого конца текстовой строки, объединяют текстовые строки |
| Логические | Вычисляют выражения и возвращают значения ЛОЖЬ или ИСТИНА, которые используются при выполнении другого действия или форматирования |
| Информационные | Возвращают в WINDOWS информацию о текущем статусе ячейки, объекта или среды |
| Инженерные | Выполняют операции с комплексными переменными, преобразования из одной системы счисления в другую и т.д. |
Вложенные функции.
Функции могут быть вложенными, то есть одна функция может служить аргументом другой. Важно не запутаться с аргументами функций, а также с числом открывающих и закрывающих круглых скобок. Например, для подсчёта значения в ячейке может быть записано следующее выражение:
|
|
= МИН(СРЗНАЧ(К4:К67, МАКС(Е56:о897), МИН(А6:А45))
Ошибки в формулах.
Значение ошибки зависит от типа ошибки, допущенной в формуле. Первым символом значения ошибки является диез(#), за ним следует текст. Этот текст может завершаться восклицательным знаком или знаком вопроса.
Описание ошибок.
###### Выводится в том случае, когда ширина столбца недостаточна для размещения результатов вычисления формулы, а также, если значения даты или времени являются отрицательными числами.
#REF! (#ССЫЛКА!) Свидетельствует о том, что формула содержит ссылку на несуществующие (например, удалённые) ячейки.
#DIV/O! (#ДЕЛ/О!) Появляется при делении на ноль.
#NUM! (#ЧИСЛО!) Появляется в случае нарушения правил при задании операций, приятых в математике, или в результате некорректного определения аргументов функции (например, в качестве аргумента функции арифметического квадратного корня берётся отрицательное число). Также, если результат вычислений выходит за допустимый для программы диапазон значений.
#NAME! (#ИМЯ!) Указывает на то, что при вводе имени допущена ошибка и программа не может найти нужное имя ни среди имён функций, ни среди имён диапазонов.
#NULL! (#ПУСТО!) При неверном указании пересечения диапазонов, то есть, если диапазоны не имеют общих ячеек.
#N/A (#Н/Д) Появляется, когда не заданы один или несколько аргументов функции и когда происходит обращение к недоступной пользовательской функции.
#VALUE! (#ЗНАЧ!) В случае ввода аргумента или операнда недопустимого типа.
ПОРЯДОК ВЫПОЛНЕНИЯ РАБОТЫ
1. Откройте окно текстового процессора Microsoft Excel. К имеющимся трём листам добавьте ещё один. Переименуйте листы: Выручка, Себестоимость, График, Прибыль.
2. На лист Выручка занесите и оформите первую таблицу (рис.25). Сохраните изменения в файле Книги.
| Выручка от реализации книжной продукции ЭКОМ | ||||||||
| Магазин | Всего за 4 года | Доля в общей выручке в % | Ранг | Тенденция за последние 2 года | ||||
| Дом книги | 4564,5 | 3983,2 | 3654,3 | |||||
| Книжный мир | 3656,2 | 4564,3 | 4564,4 | |||||
| Знание | 654,1 | 644,1 | ||||||
| Наука | 863,1 | 1023,2 | 752,1 | |||||
| Мысль | 5896,2 | |||||||
| Книж ный двор | 926,1 | 952,1 | 756,2 | |||||
| Книго люб | 5698,2 | 6549,2 | 6987,5 | 7256,2 | ||||
| Итого |
Рис. 25
3. На лист Себестоимость занесите и оформите вторую таблицу (рис.26). Сохраните изменения в файле Книги.
| Себестоимость книжной продукции издательств ЭКОМ | |||||||||
| Магазин | Всего за4 года | Среднег. Себест. | Макс. Себ. | Мин. Себ.. | |||||
| Дом книги | 2584,5 | 2563,2 | 2586,1 | ||||||
| Книжный мир | 2256,2 | 2664,3 | 2756,2 | ||||||
| Знание | 456,1 | 412,1 | |||||||
| Наука | 763,1 | 915,2 | 612,1 | ||||||
| Мысль | 4563,2 | 4213,1 | 3918,1 | ||||||
| Книжный двор | 736,1 | 823,1 | 615,1 | 918,4 | |||||
| Книголюб | 4668,2 | 4789,2 | 4987,3 | ||||||
| Итого |
Рис.26
4. На лист Прибыль занесите и оформите третью таблицу (рис.27). Сохраните изменения в файле Книги.
| Прибыль от реализации книжной продукции издательства ЭКОМ | |||||||
| Магазин | Всего за 4 года | Доля в общей выручке в % | |||||
| Дом книги | 1068,2 | ||||||
| Книжный мир | 1808,2 | ||||||
| Знание | |||||||
| Наука | |||||||
| Мысль | 2068,9 | ||||||
| Книжный двор | 141,1 | 163,4 | |||||
| Книголюб | 2133,2 | ||||||
| Итого |
Рис.27
5. На листе Выручка рассчитайте выручку для каждого магазина за 4 года автосуммированием по строкам. Затем автосуммированием по столбцу рассчитайте Итоговую выручку.
6. Рассчитайте долю каждого магазина в общей выручке. Пояснение: итоговая выручка – 100%, выяснить, сколько процентов составляет выручка каждого магазина в общей выручке. Для всех ячеек этого столбика задать Процентный формат. Разделить выручку магазина на общую выручку, причём адрес ячейки с общей выручкой нужно сделать абсолютным. Общее количество процентов должно быть равно 100, доказать это автосуммированием. Сохраните изменения.
7. Расставим магазины по местам в зависимости от выручки с помощью функции РАНГ. Вызвать функцию РАНГ в ячейке Н4 и в окне функции почитать справку с примером использования этой функции. Растянуть функцию от ячейки Н4 вниз за маркер автозаполнения, подумать, какие адреса в формуле нужно сделать абсолютными.
8. В столбце Тенденция за последние 3 года должны быть следующие слова: Стабильный рост, если выручка за четвёртый год больше выручки за третий; Стабильное снижение, если выручка за четвёртый год меньше выручки за третий; Неизменное состояние, если выручка на протяжении последних двух лет неизменна; Нестабильные изменения – во всех остальных случаях. Пояснение: использовать несколько вложенных функций ЕСЛИ. Сохраните изменения.
9. На листе Себестоимость посчитать выручку каждого магазина за 4 года, затем, итоговую выручку с помощью Автосуммы. Сохраните изменения.
10. С помощью функции СРЗНАЧ рассчитайте среднюю выручку каждого магазина за 4 года, формулу растяните за маркер автозаполнения. Сохраните изменения.
11. С помощью функции МАКС рассчитайте максимальную выручку каждого магазина за 4 года, формулу растяните за маркер автозаполнения. Сохраните изменения.
12. С помощью функции МИН рассчитайте минимальную выручку каждого магазина за 4 года, формулу растяните за маркер автозаполнения. Сохраните изменения.
13. Рассчитайте итоговую среднюю, минимальную, максимальную выручки. Сохраните изменения.
14. На листе Прибыль рассчитайте выручку за 4 года и долю в общей выручке для всех магазинов аналогично предыдущим пунктам.
15. На листе График постройте график Выручки для всех магазинов (Рис. 28).
Рис. 28
16. На этом же листе расположить две диаграммы: круговую объёмную, отражающую долю магазинов в общей прибыли (Рис. 29) и объёмную нормированную гистограмму для отражения прибыли магазинов по годам (Рис. 30). Сохраните изменения.
Рис. 29
Рис. 30
Требования к отчёту:
1. Опишите, сколькими способами можно вызвать функцию для подсчёта среднего значения по некоторому диапазону данных.
2. Перечислите все функции категории Текстовые.
3. Что такое «вложенные функции»?
4. Опишите, как вы считали столбик «Всего за 4 года».
5. Опишите, как вы считали среднее значение.
6. Опишите, как вы считали минимальное значение.
7. Опишите, как вы считали максимальное значение.
8. Опишите, как вы считали Долю магазинов в общей выручке.
9. Опишите, как вы находили Ранг каждого магазина.
10. Опишите, как вы выводили Тенденцию за последние 2 года дл я каждого магазина.
Контрольные вопросы.
1. Чем функции отличаются от формул?
2. Какими способами можно вызвать функции на экран?
3. Как получить справку по функции?
4. Как работает функция Автосуммы?
5. Как работают функции СРЗНАЧ, МИН, МАКС?
6. Как работает функция ЕСЛИ?
7. Какие категории функций вы знаете?
8. Что такое вложенные функции?
9. Какие типы ошибок могут быть в формулах и функциях?
10. Что показывает функция РАНГ?