Тема: информационные технологии табличных процессоров. Применение функций в Excel. Графическое представление данных в Excel.

Цель работы:

- научиться работать с Мастером функций;

- научиться осуществлять расчет функций;

- ознакомиться с приёмами работы по созданию и редактированию графиков и диаграмм в MS Excel;

- освоить мастер диаграмм табличного процессора при построении графиков и диаграмм различных типов.

Теоретические сведения

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

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

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

Типы функций. Для удобства работы функции в Excel разбиты по категориям:

- финансовые;

- дата и время

- математические;

- статистические;

- ссылки и массивы;

- работа с базой данных;

- текстовые;

- логические;

- проверка свойств и значений;

- инженерные;

- аналитические.

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

Для создания функций удобно использовать мастер функций. Вызвать его можно щелкнув в строке формул. Работа с мастером функций состоит из двух шагов. На первом шаге выбирается необходимая функция из списка функций (рисунок 7.1).

Рисунок 7.1 Окно мастера функций, шаг 1

На втором шаге описываются аргументы функции (рисунок 7.2).

Рисунок 7.2 Аргументы функции СчетЕсли

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

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

В Microsoft Excel существует 2 видадиаграмм: внедреннаядиаграмма и лист диаграммы.

Внедренная диаграмма – это объект-диаграмма, которая помещается в лист Excel и сохраняется вместе с этим листом при сохранении всей книги Excel.

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

Элементы диаграммы:

- ряды данных - это та информация (строки или столбцы), на основе которой будет строиться диаграмма. Каждому ряду данных на диаграмме соответствует отдельный цвет или способ обозначения, указанный на легенде диаграммы. Диаграммы всех типов, кроме круговой, могут содержать несколько рядов данных;

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

- категории: если для рядов данных используются значения столбцов, то значения строк - это категории и наоборот;

- подписи значений - подпись с дополнительными сведениями о маркере данных, представляющем одну точку данных или значение ячейки листа;

- маркер данных - полоса, область, точка, сегмент или другой объект на диаграмме, соответствующий значению одной ячейки ряда данных. Например, в круговой диаграмме это сегмент круга. Маркеры данных одного цвета на диаграмме образуют ряд данных.

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

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

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

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

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

- круговая и кольцевая диаграммы показывают соотношение частей в целом;

- точечная диаграмма подходит для отображения соотношения между двумя наборами данных, один из которых откладывается по оси Х, а второй по оси У. Эта диаграмма отображает неравномерность интервалов и группировки данных и часто используется для представления данных экспериментального характера;

- диаграмма с областями подчеркивает изменение общего количества в течение какого-то периода времени, показывая сумму введенных значений. Она также отображает вклад отдельных значений в общую сумму;

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

- поверхностная диаграмма позволяют, используя три оси X,Y,Z, показать зависимость трех наборов данных. Поверхностные диаграммы позволяют “прочувствовать” трехмерные данные и позволяют наблюдать их объемный вид, что не всегда доступно при использовании других типов диаграмм;

- пузырьковая диаграмма – напоминает точечную диаграмму. Но в ней размер точки данных описывает значение этой точки. Для построения этой диаграммы необходимо создать три столбца данных: первый из них содержит значение по оси Х, второй - по оси У, а третий определяет размер каждого пузырька;

- биржевые диаграммы - диаграммы, разработанные специально для биржевой деятельности. Excel предлагает несколько типов этих диаграмм:

1) максимальная – минимальная – закрытие;

2) начальная - максимальная –минимальная – закрытие;

3) объем - максимальная –минимальная – закрытие;

4) объем – начальная - максимальная - минимальная – закрытие.

Для любого из типов данные должны быть расположены в порядке, описанном в названии. Например, при создании диаграммы Максимальная – минимальная - закрытие необходимо создать следующие столбцы данных: дата, максимальная цена, минимальная цена и цена на момент закрытия.

Диаграммы в виде цилиндра, конуса, пирамиды: в таком формате могут быть представлены гистограммы, линейчатые диаграммы. В этом типе столбики гистограмм или полосы линейчатой диаграммы заменяются на цилиндры, конусы, пирамиды.

Нестандартные типы диаграмм. Excel предлагает большое количество нестандартных диаграмм, обладающих теми или иными особенностями многих основных типов. Если хочется добавить в диаграмму что-нибудь непривычное – то рекомендуется выбрать наиболее подходящую диаграмму именно из нестандартных типов.

 

Для построения графиков и диаграмм во вкладке Вставка в панели Диаграммы выбирается нужный тип диаграммы (рисунок 7.3), дальнейшее оформление диаграммы выполняется с помощью вкладок Конструктор (рисунок 7.4) и Макет (рисунок 7.5).

Рисунок 7.3 Типы диаграмм

Рисунок 7.4 Макеты и стили диаграмм

Рисунок 7.5 Команды оформления диаграммы

Задание 1. Используя Мастер функций,определить среднее, минимальное, максимальное значение функции F(x) (используя данные задания 1 лабораторной работы номер 6).

Задание 2. Используя функцию СУММЕСЛИ выполнить вычисления (используя данные задания 2 лабораторной работы номер 6):

1. вычислить суммарный факт по продукции прокат;

2. вычислить суммарный план по продукции сталь;

3. вычислить суммарный факт >1500;

4. вычислить суммарный план <=1800.

Задание 3. Используя данные задания 1 лабораторной работы номер 6, построить график функции F(x) и оформить его в соответствии с рисунком 7.6.

Рисунок 7.6 График функции F(x)

Задание 4. Используя данные задания 2 лабораторной работы номер 6, построить гистограмму, отображающую плановое и фактическое количество каждого наименования продукции в соответствии с рисунком 7.7.

Рисунок 7.7 Плановое и фактическое количество каждого наименования продукции

Контрольные вопросы.

1. Для чего применяется Мастер функций?

2. Что такое аргументы функции?

3. Какие категории функций Вы знаете?

4. Как воспользоваться справкой в Мастере функций?

5. Что такое автосуммирование?

6. Как построить диаграмму?

7. Элементы диаграммы: ряд, легенда, маркер.

8. Как произвести редактирование диаграммы?

9. Как произвести форматирование диаграммы?

10. Способы размещения диаграммы?

Литература[3, 7, 8]

Лабораторная работа №8.