8 Обработка и представление данных средствами Excel

1. Выполнение расчётов с применением формул и функций

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

3. Использование электронных таблиц как баз данных

4. Анализ данных с помощью сводных таблиц

5. Консолидация данных

6. Печать таблиц и диаграмм

– 1 –

В Excel существует несколько возможностей производить вычисления над данными, содержащимися в ячейках:

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

• не повторять действия по расчёту значений после изменения исходных данных можно при использовании формул. Преимущество их использования заключается в том, что можно изменять значения в ячейке, включённой в формулу, не изменяя при этом саму формулу. Excel автоматически пересчитает значения в ячейках, содержащих формулы, после внесения новых исходных данных;

• более сложные вычисления в Excel производятся при помощи дополнительных программ, входящих в комплект поставки Excel, которые используются для решения различных специальных задач. Например, с помощью программы условного анализа («что - если») можно прогнозировать развитие некоторой ситуации; функция подбора параметра позволяет при заданном результате вычисления определить значения зависимых ячеек; программа поиска решения осуществляет поиск значений, с некоторой степенью точности удовлетворяющих определённым условиям и т.д.. Задание параметров для вычислений по этим программам производится в специальных диалоговых окнах. Сами вычисления выполняются на основании многократного повторения одних и тех же действий, пока результат не будет удовлетворять всем поставленным условиям.

Выполнение расчётов с применением формул

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

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

+, - сложение, вычитание,

*, / умножение, деление,

% определение значения процента,

^ возведение в степень.

Ввод формулы начинается со знака равенства. Далее следует ввести константы, операторы и ссылки на ячейки, значения которых участвуют в вычислении.

При вводе формулы ссылки можно набирать непосредственно с клавиатуры (английскими буквами), а можно выполнить щелчок по ячейке, которая должна быть указана в ссылке. Данная ячейка будет обрамлена пунктирной бегущей рамкой, а её адрес появится во вводимой формуле.

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

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

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

Другой тип адресации, которая используется в Excel, - абсолютная. Абсолютный тип ссылки задаёт абсолютные координаты ячейки в рабочем листе (относительно левого верхнего угла таблицы). При перемещении или копировании формулы абсолютная ссылка на ячейку изменена не будет и на новом месте формула будет ссылаться на ту же ячейку. В абсолютной ссылке на ячейку перед именем столбца и номером строки указывается знак доллара $, который можно ввести в ссылку с клавиатуры или использовать для этого специальную клавишу - F4. В Excel также применяется смешанная ссылка: в ней абсолютной является только часть - или имя столбца, или номер строки.

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

• сначала необходимо, чтобы курсор ввода в формуле был установлен на ссылке или сразу после неё,

• затем нужно нажимать клавишу F4 столько раз, пока у ссылки не появится нужный тип адресации,

• после этого нужно продолжить ввод формулы или подтвердить произведённые изменения.