МЕТОДИЧЕСКАЯ РАЗРАБОТКА
для студентов
к практическому занятию по теме
«Базовые технологии преобразования информации. Понятие функции. Основные функции Excel. Работа с мастером функции. Искатель диапазона. Построение графиков и диаграмм. »
1. Научно-методическое обоснование темы:
Изучить основные функции Excel и способы их использования
в табличных расчетах.
Для выполнения вычислений в формулах Excel можно использовать также встроенные функции Excel. Функции Excel используются для расчета арифметических и логических выражений, проведения операций над данными. Выделяют также функции работы с датой и временем.
Развить и закрепить навыки создания и редактирования графиков и диаграмм в Excel в соответствии с предъявляемыми требованиями.
Для решения коммуникативных проблем необходимо представлять табличную информацию в графическом виде, облегчающем восприятие информации, упрощающем процесс сравнения разнородных данных. Для этого используются различные средства, основные из которых графики и диаграммы. Хотя графики и диаграммы могут быть достаточно эффективными средствами коммуникации (способствовать пониманию материала), но они также способны легко вводить получателя информации в заблуждение, если неправильно выбран тип графического средства или графическая конструкция некорректна.
Учитывая широкие возможности программы Microsoft Excel в области математических и статистических расчетов, часто возникает необходимость отобразить полученные результаты графически. Это позволяет решать самые разнообразные задачи. Microsoft Excel обладает обширными возможностями по графическому представлению информации, легкостью создания и внесения изменений. Диаграммы являются средством наглядного представления данных и облегчают выполнение сравнений, выявление закономерностей и тенденций данных.
2. Краткая теория:
1. Понятие функции.
Выражения, которые используются при вычислении функций, называют аргументами функции. Аргументами функции могут быть различные выражения, ссылки, диапазоны данных, а также другие функции. Аргументы функции Excel заключаются в круглые скобки и указываются через точку с запятой «;».
Последовательность, в которой должны следовать аргументы функции, называют синтаксисом функции.
Наименование функции и ее синтаксис составляют формат функции.
Наиболее общий формат функций Excel
= <Наименование функции> (<список аргументов>)
Наименование функции не должно содержать пробелов и должно начинаться с буквы. Пробелы между наименованием функции и круглыми скобками не допускаются. Скобки используются для определения начала и конца списка аргументов и должны быть парными. Пробелы перед скобками и после скобок не допускаются.
Список аргументов может состоять из выражений различного типа. Тип каждого выражения в списке аргументов должен быть допустимым для данного аргумента в соответствии с синтаксисом.
Как известно, любая формула Excel начинается со знака равенства за которым следует арифметическое, логическое или какое-либо другое выражение, в состав которых могут входить функции Excel. Если функция стоит в самом начале формулы, то ей должен предшествовать знак равенства.
Функции, являющиеся аргументами другой функции или входящие в состав ее выражений, называют вложенными. В Excel можно использовать до семи уровней вложенности функций.
Каждая встроенная функция принадлежит какой либо категории.
2. Основные функции Excel.
По типу обрабатываемых данных можно привести следующую классификацию встроенных функций Excel.
1. Математические функции – наиболее часто используемые функции для обработки числовых значений.
2. Логическиефункции предназначены для обеспечения условных вычислений в математических выражениях и обработки логических выражений.
3. Текстовые функции предназначены для текстовой информации
4. Функции для обработки даты и времени.
Полностью приводить синтаксис и описание встроенных представляется излишним, т.к. смысл большинства функций очевиден из их названия. Полное описание и синтаксис встроенной функции отражается в диалоге мастера функций при ее выборе (как показано на рис. выше).
Среди основных математических функций можно выделить следующие группы:
- функции преобразования типов и их приведения (ОКРВВЕРХ,
ОКРВНИЗ, ОКРУГЛ, ОКРУГЛВВЕРХ, ОКРУГЛВНИЗ – для операций округления, ОТБР – отбрасывание дробной части, ЦЕЛОЕ – преобразование в целое число).
- тригонометрические функции (SIN, COS, TAN, ASIN, ACOS и др.);
- логарифмические (EXP, LOG, LN, LOG10)
- арифметические (СУММ, ПРОИЗВЕД, ЗНАК, КОРЕНЬ, СТЕПЕНЬ, ФАКТ, ОСТАТ);
- функции обработки массивов (СУММ, ПРОИЗВ, СУММКВ,
СУММПРОИЗВ, СУММЕСЛИ – различного рода суммирование; МОПРЕД, МУМНОЖ – операции с матрицами).
Логические функции (И, ИЛИ, ЕСЛИ, НЕ, ИСТИНА, ЛОЖЬ).
Основные текстовые функции:
ЛЕВСИМВ, ПРАВСИМВ, ПСТР – выделение определенного количества символов в строке.
ДЛСТР – длина строки.
ЗНАЧЕН – преобразование текста в число.
Т – преобразует число в текст.
ТЕКСТ – преобразует число в текст и форматирует его.
ПОВТОР – повторяет заданный текст определенное число раз.
ПРОПИС – преобразует все буквы строки в прописные.
СТРОЧН - преобразует все буквы строки в строчные.
СЖПРОБЕЛЫ – удаляет из текста лишние пробелы, кроме пробелов между словами.
Функции «Дата и время»
В Excel дата и время имеют общее представление в едином типе данных (тип «Дата и время »), представляющим собой определенное число. Представление даты и времени как некоторого целого числа необходимо для упрощения операций с датой и временем. Для пользователя числовое представление даты и времени является прозрачным, т.е. дата и время отражаются в ячейках Excel в привычном для человека виде. Если выделить группу ячеек, содержащих данные о дате и времени, и назначить в качестве формата ячейки «Общие » (Фор м ат ® Я чейки … ® Вид ® Общий), то будет отражено числовое представление даты и времени вместо традиционного. Для того, чтобы вернуться к традиционному представлению даты и времени необходимо также выделить группу ячеек и назначить им тип «Дата и время » (Фор м ат ® Я чейки ® Вид ® Дата и время).
СЕГОДНЯ – возвращает текущую дату.
ГОД, ДЕНЬ, МЕСЯЦ – возвращают соответственно год, день, месяц в виде отдельного числа.
ДАТАЗНАЧ – преобразует дату в текстовом представлении в числовой тип «Дата и время ».
ДАТА – возвращает дату в числовом формате на основе параметров год, месяц, число.
ВРЕМЯ – возвращает время в формате даты в виде числа на основе параметров час и минута.
СЕКУНДЫ, МИНУТЫ, ЧАС – возвращает из формата «Дата и время» секунды, минуты и час соответственно.
Специальные категории встроенных функций Excel.
1. Финансовые функции (см. Раздел 3 настоящей книги).
2. Статистические функции (МИН, МАКС, СРЗНАЧ, СЧЕТЕСЛИ).
3. Функции для работы с базами данных.
4. Функции для работы со ссылками и массивами.
Приведем синтаксис и назначение некоторых часто используемых встроенных функций.
СУММ – суммирование значений ячеек
Синтаксис:
СУММ (число1; число2; …)
где число1, число2 … - определенное число, диапазон ячеек или наименование диапазона.
Поскольку сложение значений ячеек с использованием функции СУММ является одной из самых распространенных задач в Excel для выполнения данной задачи существует инструмент Автосумма.
Для использования средства Автосумма необходимо перейти курсором на ячейку, которая будет содержать сумму. Далее активировать инструмент и выделить мышью определенный диапазон, после чего нажать Enter.
Можно провести аналогичную операцию по-другому:
1. Выделить диапазон суммируемых ячеек вместе с итоговой строкой/столбцом и нажать Alt + =.
ЕСЛИ – выполнение операций в соответствии с условием
Синтаксис:
ЕСЛИ (логическое_выражение; значение_истина; значение_ложь)
Функция ЕСЛИ возвращает значение_истина, если условие истинно и значение_ложь, если условие ложно.
СЧЕТЕСЛИ - количество непустых ячеек в указанном диапазоне, удовлетворяющих заданному критерию.
Синтаксис:
СЧЕТЕСЛИ (интервал; критерий),
где интервал – область ячеек, в котором подсчитывается количество ячеек; критерий – критерий в форме числа, выражения или текста, который определяет какие ячейки необходимо подсчитывать. Например, «>2500 ».
СУММЕСЛИ – сумма ячеек, значения которых отвечает определенному критерию.
Синтаксис:
СУММЕСЛИ (интервал; критерий; сумм_интервал),
где интервал – область ячеек, для которых вычисляется критерий; критерий – критерий в форме числа выражения или текста; сумм_интервал – ячейки для суммирования.
МАКС – максимальное значение из списка чисел.
Синтаксис:
МАКС (число1; число2; …),
где число1, число2 … - конкретные числа, области ячеек или названия диапазонов.
МАКС – минимальное значение из списка чисел.
Синтаксис:
МИН(число1; число2; …),
где число1, число2 … - конкретные числа, области ячеек или названия диапазонов.
МОДА – наиболее часто встречающееся число
Синтаксис:
МОДА (число1; число2; …),
СРЗНАЧ – расчет среднего арифметического
Синтаксис:
СРЗНАЧ (число1; число2; …),
| Можно вводить функции в рабочем листе прямо с клавиатуры или с помощью команды "Функция" меню "Вставка". При вводе функции с клавиатуры лучше использовать строчные буквы. Когда ввод функции закончен, Excel изменит буквы в имени функции на прописные, если оно было введено правильно. Если буквы не изменяются, значит, имя функции введено неверно. Если вы выделите ячейку и выберите в меню "Вставка" команду "Функция", Excel выведет окно диалога "Мастер функций". Немного быстрее можно этого добиться, нажав клавишу со значком функции в строке формул. |
Открыть это окно можно также с помощью кнопки "Вставка функции" на стандартной панели инструментов.
В этом окне сначала выберите категорию в списке "Категория" и затем в алфавитном списке "Функция" укажите нужную функцию.
Excel введет знак равенства, имя функции и пару круглых скобок. Затем Excel откроет второе окно диалога мастера функций.
Второе окно диалога мастера функций содержит по одному полю для каждого аргумента выбранной функции. Если функция имеет переменное число аргументов, это окно диалога при воде дополнительных аргументов расширяется. Описание аргумента, поле которого содержит точку вставки, выводится в нижней части окна диалога.
Справа от каждого поля аргумента отображается его текущее значение. Это очень удобно, когда вы используете ссылки или имена. Текущее значение функции отображается внизу окна диалога.
Нажмите кнопку "ОК" и созданная функция появится в строке формул.
Использование пакета анализа
Пакет анализа представляет собой надстройку Excel– набор средств и инструментов, расширяющих стандартные возможности Excel, связанные с анализом данных.
Возможности надстройки Пакет анализа данных:
1. Однофакторный дисперсионный анализ.
2. Двухфакторный дисперсионный анализ с повторениями.
3. Двухфакторный дисперсионный анализ без повторений.
4. Корреляция.
5. Ковариация.
6. Описательная статистика.
7. Экспоненциальное сглаживание.
8.Двухвыборочный F-тест для дисперсий.
9. Анализ Фурье.
10. Гистограмма.
11. Скользящее среднее.
12. Генерация случайных чисел.
13. Ранг и персентиль.
14. Регрессия.
15. Выборка.
16. Парный двухвыборочиый t-тест для средних.
17.Двухвыборочный t-тест с одинаковыми дисперсиями.
18.Двухвыборочный t-тест с разными дисперсиями.
19.Двухвыборочный z-тест для средних.
Для того, чтобы воспользоваться одним из инструментов Пакета анализа исследуемые данные следует представить в виде таблицы, где столбцами являются соответствующие показатели. При создании таблицы Excel информация вводится в отдельные ячейки. Совокупность ячеек, содержащих анализируемые данные, называется входным интервалом.
Для того, чтобы воспользоваться набором средств и инструментов Пакет анализа необходимо произвести его установку. Необходимо выполнить следующие действия:
Открыть панель быстрого доступа. И из меню выбрать Другие команды.
Появиться диалоговое окно Параметры Excel, в правом окне выбрать функцию Пакет анализа VBA.
Щелкнуть по кнопке Перейти и нажать на ОК. Появиться диалоговое окно Надстройки.
Ставим флажок в поле Пакет анализа и подтверждаем свой выбор нажатием на клавишу ОК.Т.о. установили пакет анализа. Для дальнейшей работы на ленте выбираем вкладку Данные. И выбираем функцию Анализ данных.
Выберите команду Данные►Анализ►Анализ данных. В списке Инструменты анализа открывшегося окна выберите пункт Описательная статистика.
Появляется диалоговое окно Описательная статистика.
Заполните окно параметров статистики следующим образом:
В поле Входной интервал – диапазон ячеек с данными для статанализа, в поле Выходной интервал- адрес ячейки, куда запишется результат анализа.В параметрах вывода, устанавливаем флажок в поле Итоговая статистика и нажимаем на ОК.
В результате произведенных операций получим рассчитанные показатели описательной статистики.
Искатель диапазона.
Искатель диапазона – инструмент Excel, предназначенный для визуального выбора ячеек и диапазонов ячеек. После активации искателя диапазона (нажать на показанную на рисунке кнопку) пользователь переходит к выделению необходимых ячеек и диапазонов ячеек.
Выделение диапазонов ячеек мышью производится по принципу «Нажат и потащил».с клавиатуры Shift+«стрелки». Если при выделении необходимо выделить несколько ячеек, расположенных в разных местах листа, то выделение производится щелчком мыши по выделяемым ячейкам при удержании клавиши Ctrl. После выделения необходимых ячеек в завершение выбора необходимо нажать Enter. Результат представлен на рисунке.
4. Ввести все необходимые аргументы функции и нажать Оk. Функция со всеми аргументами будет вставлена в строку формул.
Диаграмма – это способ наглядного представления информации, заданной в виде таблицы чисел. Демонстрация данных с помощью диаграмм является более наглядной и эффективной для восприятия.
Создание диаграммы
Диаграммы строятся на основе данных, содержащихся на рабочем листе, поэтому перед созданием диаграммы они должны быть введены. Диаграммы в Excel динамические, т. е. автоматически обновляются после изменения данных, на основе которых построены. Диаграмма может быть размещена как на листе с данными, так и на отдельном листе (занимая весь лист). Основные элементы диаграммы показаны на рис.1
Элементы диаграммы
По умолчанию диаграмма состоит из следующих элементов:
- Ряды данных – представляют главную ценность, т.к. визуализируют данные;
- Легенда – содержит названия рядов и пример их оформления;
- Оси – шкала с определенной ценой промежуточных делений;
- Область построения – является фоном для рядов данных;
- Линии сетки.
Помимо упомянутых выше объектов, могут быть добавлены такие как:
- Названия диаграммы;
- Линий проекции – нисходящие от рядов данных на горизонтальную ось линии;
- Линия тренда;
- Подписи данных – числовое значение для точки данных ряда;
- И другие нечасто используемые элементы.
Для создания диаграммы необходимо, предварительно выделив диапазон данных, нажать кнопку нужного типа диаграммы на панели Диаграммы вкладки Вставка.
После этого выбранная диаграмма сразу же будет построена на листе. Одновременно на ленте главного меню появится контекстный инструмент Работа с диаграммами, содержащий вкладки: Конструктор, Макет и Формат. На этих вкладках расположены основные инструменты, позволяющие отформатировать и изменить диаграмму.
Изменение диаграммы
Диаграмма может быть изменена как с использованием кнопок соответствующих панелей, так и пунктов контекстного меню по нажатию правой кнопки на выделенном элементе диаграммы.
Вкладка Конструктор состоит из панелей: Тип, Данные, Макеты диаграмм, Стили диаграмм, Расположение. Основные операции, выполняемые этими инструментами: изменение типа и расположения диаграммы, ее данных и стиля.
Рис. Вкладка Конструктор
Панель Тип предназначена для изменения типа построенной диаграммы. Пункт Сохранить как шаблон позволяет сохранить для дальнейшего использования измененную пользователем диаграмму.
На панели Данные можно выбрать новый или откорректировать выбранный диапазон данных (добавить/удалить ряды данных), поменять местами данные в строке на данные в столбце.
На панели Макеты диаграмм можно применить один из предлагаемых макетов оформления диаграмм данного типа.
Панель Стили диаграмм позволяют применить стилевое оформления диаграммы в целом.
Панель Расположение предназначена для изменения месторасположения диаграммы: переместить на другой лист или разместить на отдельном.
Вкладка Макет содержит панели: Текущий фрагмент, Вставить, Подписи, Оси, Фон, Анализ, Свойства. Эти инструменты предназначены для добавления и настройки отдельных элементов диаграммы.
Рис. Вкладка Макет
Панель Текущий фрагмент позволяет выбрать нужный элемент диаграммы (в списке Элементы диаграммы) и отформатировать его (кнопка Формат выделенного фрагмента), либо вернуть форматирование к первоначальному виду (кнопка Восстановить форматирование стиля).
Панель Вставить нужна для вставки рисунков, фигур и надписей в диаграмму.
Панель Подписи позволяет добавить и разместить в диаграмму подписи соответствующих элементов: заголовка диаграммы, названия осей, легенды, данных, таблицы данных.
С помощью панели Оси на диаграмме отображаются основные оси и линии сетки.
Панель Фон предназначена для настройки параметров области построения, а также элементов трехмерных диаграмм (Стенка диаграммы, Основание диаграммы, Поворот объемной фигуры).
Вкладка Формат (рис. 6.7) включает панели: Текущий фрагмент, Стили фигур, Стили WordArt, Упорядочить и Размер с инструментами, позволяющими отформатировать отдельные элементы диаграммы.
Рис. Вкладка Формат
Панель Текущий фрагмент работает аналогично такой же панели на вкладке Макет и позволяет отформатировать выбранный элемент.
На панели Стили фигур можно настроить нужный стиль оформления, задать заливку цветом, окантовку (контур), эффекты для выбранного элемента диаграммы.
Панель Стили WordArt позволяет задать оформление текстовых элементов, присутствующих в диаграмме (подписи по осям, заголовки, легенду и проч.)
Панель Упорядочить позволяет управлять размещением объектов (диаграмм) на листе относительно друг друга.
Панель Размер предназначена для задания точного размера и масштаба диаграммы.