Арифметические и тригонометрические

Применение электронных таблиц для расчетов. Использование встроенных функций в MS Excel.

Назначение MS Excel и возможности обработки данных в среде электронных таблиц

Электронные таблицы.

§ Расширенные возможности книг.

§ Вычисления по формулам с использование функций.

§ Использование списков.

§ Консолидация и группировка данных.

§ Фильтрация данных.

§ Построение таблиц.

§ Построение графиков и диаграмм.

§ Статистическая обработка данных.

§ Решение оптимизационных задач. Анализ данных.

§ Решение финансовых задач.

§ Создание сводных таблиц.

§ Совместное использование данных.

Формула — это краткая запись некоторой последовательности действий, приводящих к конкретному результату. Формула может содержать не более 1024 символов. Структуру и порядок элементов в формуле определяет ее синтаксис.

Все формулы в Excel должны начинаться со знака равенства. Формулы содержат вычисляемые элементы (операнды) и операторы. Операндами могут быть константы, ссылки или диапазоны ссылок, заголовки, имена, функции. В Excel включено 4 вида операторов: арифметические, текстовые, операторы сравнения, адресные операторы.

Арифметические операторы (+, -, *, /, %, ^) используются для выполнения основных математических вычислений над числами.

Операторы сравнения (=, >, <, >=, <=, <>) используются для обозначения операций сравнения двух чисел. Результатом вычисления формул, содержащих операторы сравнения, являются логические значения Истина или Ложь.

Адресные операторы объединяют диапазоны ячеек для осуществления вычислений. К адресным операторам относятся: оператор диапазона, который ссылается на все ячейки между границами диапазона включительно. Например, СУММ(В5:В15,С15:С25);

Приоритет выполнения операций:

1 операторы ссылок (адресные) «:», «,», «»;

2 знаковый минус «-»;

3 вычисление процента %;

4 арифметические ^, *, /, +, -;

5 текстовый оператор &;

6 операторы сравнений =, <, >, <=, >=, <>.

Для изменения порядка вычисления в формуле используются скобки.

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

В Excel существуют три типа ссылок: относительные, абсолютные, смешанные.

Относительная ссыл ка (A5) - указывает на ячейку, основываясь на ее положении относительно ячейки, в которой находится формула, например «на две строки выше». При перемещении формулы относительная ссылка изменяется, ориентируясь на ту позицию, в которую переносится формула.

Абсолютными ($A$5) - являются ссылки на ячейки, имеющие фиксированное расположение на листе. Эти ссылки не изменяются при копировании формул. Абсолютная ссылка содержит знак $ перед именем столбца и именем строки.

Смешанные ссылки (A$5) — это ссылки, являющиеся комбинацией относительных и абсолютных ссылок. Например, фиксированный столбец и относительная строка: $D7.

Ссылки на ячейки других листов книги имеют следующий формат:

<имя раб.листа>!ссылка на ячейку, например, Лист2!А1:А10.

Excel позволяет ссылаться на диапазон ячеек нескольких рабочих листов. Такая ссылка называется объемной. Например, Лист1:Лист5!$А$1:$D$3.

Ссылки на ячейки других книг имеют следующий формат:

[имя книги]<имя листа>!ссылка на ячейку, например: [книга2]ЛистЗ!Е5:Е15.

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

В общем виде любая функция может быть записана в виде:

=<имя_функции>(аргументы)

Вводить функции рекомендуется строчными буквами. Если ввод функции осуществлен правильно, Excel сам преобразует строчные буквы в прописные.

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

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

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

§ Информационные, предназначенные для определения типа данных, хранимых в ячейках.

§ Логические, предназначенные для проверки выполнения условия или нескольких условий (ЕСЛИ, И, ИЛИ, НЕ, ИСТИНА, ЛОЖЬ).

§ Статистические, предназначенные для выполнения статистического анализа данных.

§ Финансовые, предназначенные для осуществления типичных финансовых расчетов, таких как вычисление суммы платежа по ссуде, объема периодической выплаты по вложению или ссуде, стоимости вложения или ссуды по завершении всех платежей.

§ Функции баз данных, предназначенные для анализа данных из списков или баз данных.

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

§ Функции работы с датой и временем. Они позволяют анализировать и работать со значениями даты и времени в формулах.

Нестандартные функции. Это функции, созданные пользователем для собственных нужд. Создание функций осуществляется с помощью языка Visual Basic.

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

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

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

В Excel определено семь ошибочных значений:

#ДЕЛ/0! — попытка деления на 0. Эта ошибка обычно возникает, если в формуле делитель ссылается на пустую ячейку;

#ИМЯ? — в формуле используется имя, отсутствующее в списке имен диалога Присвоение имени. Excel также вводит это ошибочное значение в том случае, когда строка символов не заключена в двойные кавычки;

#ЗНАЧ! — выдается при указании аргумента или операнда недопустимого типа, например, введена математическая формула, которая ссылается на текстовое значение, а также в том случае, когда Excel не может исправить формулу средствами автоисправления;

#ССЫЛКА! — отсутствует диапазон ячеек, на который ссылается формула (возможно, он удален);

#Н/Д — нет данных для вычислений. Аргумент функции или операнд формулы является ссылкой на ячейку, не содержащую данных. Любая формула, которая ссылается на ячейки, содержащие #Н/Д, возвращает значение #Н/Д;

#ЧИСЛО! — задан неправильный аргумент функции, например, v(-5). #ЧИСЛО!,может также указывать на то, что значение формулы слишком велико или слишком мало и не может быть представлено на листе;

#ПУСТО! — в формуле указано пересечение диапазонов, но эти диапазоны не имеют общих ячеек.

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

3.4.3 Визуализация табличных данных с помощью диаграмм и
графиков

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

Основные компоненты диаграммы:

Тип диаграммы — в Excel типы диаграмм делятся на стандартные и нестандартные. К последним относятся как пользовательские типы диаграмм, создаваемые путем настройки пользовательских диаграмм, так и смешанные диаграммы Excel (например, гистограмма с графиком). При выборе типа диаграммы в диалоговом окне мастера диаграмм в специальной области приводится пример и краткое описание выбранной диаграммы.

Исходные данные диаграммы задаются ссылкой на соответствующий диапазон. Исходные данные разбиваются по рядам (множество значений одного параметра). В свою очередь ряды состоят из точек — конкретных значений параметра. По умолчанию Excel считает, что диаграмма должна содержать меньше рядов, чем точек. Соответственно выбирается ориентация диаграммы. Пользователь может изменить ориентацию диаграммы, выбрав переключатель Ряды в строках/столбцах диалогового окна мастера диаграмм. Исходные данные необязательно должны располагаться в одном блоке ячеек. Чтобы построить диаграмму для несмежных данных, следует перед запуском мастера диаграмм выделить каждый отдельный блок исходных точек при нажатой клавише Ctrl.

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

Оси диаграммы. Наличие и количество осей определяется типом диаграммы. Пользователь может контролировать вывод осей на диаграмме. Если данные для оси X являются датами, то Excel распознает этот факт и автоматически применяет временное масштабирование к этой оси;

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

Легенда диаграммы — это заголовки рядов, размещаемые по умолчанию в правой части диаграммы. Выбор заголовков рядов производится автоматически, если в выделенный диапазон включены заголовки строк и столбцов. В противном случае ряды именуются как Ряд1, Ряд 2 и т. д. Пользователь может изменить имя, введя нужный текст или ячейки с нужным текстом в поле Имя второго диалогового окна мастера диаграмм. Можно также изменить местоположение или вообще удалить легенду с диаграммы. Команды форматирования позволяют изменить используемый в диаграмме шрифт.

Заголовки значений — идентифицирует точки в рядах данных. Обычно отображаются вдоль оси X. Выбор заголовков значений производится автоматически, если в выделенный диапазон включены заголовки строк и столбцов. В противном случае для точек используются стандартные обозначения 1, 2, 3 и т. д. Их можно изменить, введя свои текстовые значения или ссылки на диапазон рабочего листа с нужными значениями в поле Подписи по оси х второго диалогового окна мастера диаграмм.

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

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

Excel может внедрить диаграмму в рабочий лист или поместить ее на отдельном листе диаграммы. При создании диаграммы в существующем рабочем листе ее размеры и положение устанавливаются по умолчанию. Настроить размеры и положение диаграммы можно с помощью мыши. Клавиши Ctrl или Shift позволяют сохранить положение центра диаграммы или ее пропорции (отношение ширины диаграммы к ее высоте) при изменении ее размеров. При изменении размеров диаграммы настраиваются также размеры всех ее текстовых элементов. Если пользователь не удовлетворен этой настройкой, можно использовать команды форматирования для увеличения или уменьшения размеров текста.

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

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

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

§ отображать отсутствующие точки данных нулевыми значениями;

§ интерполировать отсутствующие точки данных.

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

Диаграмма сохраняется вместе с книгой, в которой она находится. Диаграмма становится доступной, когда открыта книга.

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

Если нужно изменить конкретный элемент диаграммы, его следует выделить (например, выбрав соответствующий элемент из раскрывающегося списка кнопки Элемент диаграммы на панели инструментов Диаграммы). Можно просто щелкнуть на' нужном элементе диаграммы. Excel облегчает проверку правильности выделения элемента, выводя его наименование в левом конце строки формул.

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

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

При форматировании диаграммы можно выполнять следующие действия.

§ Изменить расположение элементов диаграммы.

§ Изменить размеры и переместить область построения. Область построения — это часть диаграммы, в которой отображаются данные.

§ Форматировать текстовые элементы диаграммы (в том числе изменять ориентацию текста).

§ Форматировать и изменять шкалы осей.

§ Задавать вывод и форматирование сетки.

§ Форматировать ряды и маркеры данных.

§ Отображать в графиках коридоры колебания и полосы повышения и понижения.

§ Отображать линии проекции в графиках и диаграммах с областями.

§ Отделять сектора круга и кольца.

§ Форматировать фоновые области.

§ Настраивать объемные виды диаграммы.

§ Добавлять, удалять, изменять данные в диаграммах, изменять порядок отображения рядов.

§ Использовать многоуровневые категории. Excel позволяет группировать категории. Например, рядами данных являются месяцы, а категориями — различные города, в которых расположены предприятия, но категории классифицируются еще и по государствам, в которых находятся города.

§ Применять линии тренда. Линия тренда — это линия регрессии, которая аппроксимирует (сглаживает) точки данных, или линия скользящего среднего.

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

§ Изменять исходные значения с помощью маркеров данных. В плоских линейчатых диаграммах, гистограммах, графиках и точечных диаграммах можно проводить анализ типа «обратный, что будет, если».