Создание нового списка
Печать таблиц и диаграмм
Консолидация данных
Анализ данных с помощью сводных таблиц
Использование электронных таблиц как баз данных
Построение и редактирование диаграмм
Обработка и представление данных средствами Excel
1. Выполнение расчётов с применением формул и функций
– 1 –
В Excelсуществуетнескольковозможностей производить вычисления над данными, содержащимися в ячейках:
· можно непосредственно в ячейке, как в обычном калькуляторе, выполнить необходимое вычисление, набрав с клавиатуры числа и знаки арифметических операций. В результате в ячейке отобразится результат этого вычисления. Этот способ, однако, требует больших затрат со стороны пользователя. Другой недостаток этого способа состоит в том, что при изменении исходных данных не произойдёт изменения значений в ячейках с результатом расчета, поэтому действия по вычислению придётся повторить заново;
· не повторять действия по расчёту значений после изменения исходных данных можно при использовании формул. Преимущество их использования заключается в том, что можно изменять значения в ячейке, включённой в формулу, не изменяя при этом саму формулу. Excel автоматически пересчитает значения в ячейках, содержащих формулы, после внесения новых исходных данных;
· более сложные вычисления в Excelпроизводятся при помощи дополнительных программ, входящих в комплект поставки Excel, которые используются для решения различных специальных задач. Например, с помощью программы условного анализа («что - если») можно прогнозировать развитие некоторой ситуации; функция подбора параметра позволяет при заданном результате вычисления определить значения зависимых ячеек; программа поиска решения осуществляет поиск значений, с некоторой степенью точности удовлетворяющих определённым условиям и т.д.. Задание параметров для вычислений по этим программам производится в специальных диалоговых окнах. Сами вычисления выполняются на основании многократного повторения одних и тех же действий, пока результат не будет удовлетворять всем поставленным условиям.
Выполнение расчётов с применением формул
Формула представляет собой сочетание констант, операторов, ссылок, функций. Формулы используются для выполнения вычислений и анализа данных рабочего листа.
Операторы определяют способ вычисления результата на основании отдельных элементов формулы. Excel различает три вида операторов: арифметические, операторы сравнения и текстовый оператор. Наиболее часто в формулах применяются арифметические операторы:
+, - сложение, вычитание,
*,/ умножение, деление,
%определение значения процента,
^возведение в степень.
Ввод формулы начинается со знака равенства. Далее следует ввести константы, операторы и ссылки на ячейки, значения которых участвуют в вычислении.
При вводе формулы ссылки можно набирать непосредственно с клавиатуры (английскими буквами), а можно выполнить щелчок по ячейке, которая должна быть указана в ссылке. Данная ячейка будет обрамлена пунктирной бегущей рамкой, а её адрес появится во вводимой формуле.
После подтверждения ввода в ячейке отобразится результат вычисления, а содержащаяся в ячейке формула будет отображаться в строке формул.
В Excelимеется возможность установить на рабочий лист режим отображения формул вместо режима отображения результатов. Для этого нужно установить флажок параметра Формулы на вкладке Вид окна диалога Параметры (Сервис / Параметры), при этом автоматически увеличится ширина столбцов в два раза. Данный режим обычно устанавливается, если в ячейки рабочего листа вводится большое количество формул и поэтому требуется осуществлять оперативный контроль за правильностью их ввода.
Часто при создании таблиц приходится выполнять одинаковые вычисления для разных данных, поэтому ввод формул в вычисляемые ячейки можно осуществлять путём заполнения или копирования. Так как ссылки в формуле задают позицию ячеек относительно активной ячейки, то при копировании формулы адреса ячеек в ссылках будут автоматически изменены. Такие ссылки называют относительными ссылками на ячейку. Относительная ссылка воспринимается программой как указание маршрута (направление движения и расстояния) к адресуемой ячейке, содержащей формулу. При копировании или перемещении формулы относительные ссылки будут изменены таким образом, что маршрут (направление движения и расстояние) сохранится. При этом адресоваться будут уже другие ячейки.
Другой тип адресации, которая используется в Excel, - абсолютная. Абсолютный тип ссылки задаёт абсолютные координаты ячейки в рабочем листе (относительно левого верхнего угла таблицы). При перемещении или копировании формулы абсолютная ссылка на ячейку изменена не будет и на новом месте формула будет ссылаться на ту же ячейку. В абсолютной ссылке на ячейку перед именем столбца и номером строки указывается знак доллара $, который можно ввести в ссылку с клавиатуры или использовать для этого специальную клавишу -F4. В Excel также применяется смешанная ссылка: в ней абсолютной является только часть - или имя столбца, или номер строки.
Поменять тип адресации в ссылке можно непосредственно при вводе адреса ячейки в формулу или когда формула уже введена в ячейку путём редакции её содержимого. При использовании клавиши F4 это производится следующим образом:
- сначала необходимо, чтобы курсор ввода в формуле был установлен на ссылке или сразу после неё,
- затем нужно нажимать клавишу F4столько раз, пока у ссылки не появится нужный тип адресации,
- после этого нужно продолжить ввод формулы или подтвердить произведённые изменения.
Выполнение расчётов с применением функций
При проведении вычислений в формулах возможно использование функций. Под функцией понимают объединение нескольких вычислительных операций для решения определённой задачи. Иначе говоря, функции - это стандартные формулы, которые обеспечивают выполнение определённого набора операций над данным диапазоном величин. Обращение к функции всегда состоит из имени функции, круглых скобок и аргументов функции. Имя функции определяет операции, которые будут выполняться; аргумент показывает адреса ячеек тех величин, с которыми будет работать функция. В качестве аргумента функции может быть использована другая функция. Функции можно вводить вручную или с помощью Мастера функций. Ввод функций вручную используют редко, так как этот способ сопряжён с ошибками.
Если ячейка должна содержать только одну функцию, то при вводе её можно сразу вызывать Мастера функций, не набирая знак равенства в качестве первого символа - он будет вставлен в ячейку Мастером функций автоматически. Если же функция является частью вводимой в ячейку формулы, то набор знака равенства в начале ввода обязателен.
Мастер функций запускается автоматически после выбора команды Вставка / Функция... или щелчка по кнопке Вставка функции в панели инструментов Стандартная. В результате на экран будет выведено диалоговое окно Мастера функций. В этом окне список Категория содержит различные категории функций, сгруппированных по тематическому признаку. При выборе любой категории в списке Функция отобразятся функции данной категории. При выделении любой из функций в нижней части диалогового окна будет выведен синтаксис и краткая справка о назначении выбранной функции. После выбора нужной функции необходимо щелчком по кнопке Далее осуществить переход ко второму окну Мастера функций, в котором указываются аргументы для функции. В верхней части данного окна отображается имя и описание функции, между ними - поля для ввода аргументов функции и отображения их значений. Имя и описание выбранного аргумента функции располагаются в нижней части окна. При задании в качестве аргумента адресов ячеек или диапазонов ячеек нужно свернуть окно Мастера функций щелчком по кнопке с красной стрелкой (она располагается справа от поля ввода аргумента) и выделить нужную область в таблице. Адреса первой и последней ячеек области будут автоматически представлены в поле аргумента. Если были выделены несмежные диапазоны ячеек, то в поле аргумента они будут выведены через точку с запятой (;). В поле Значение, расположенном в левом верхнем углу окна, выводится результат вычисления функции. После щелчка по кнопке Готово мастер вставит функцию в текущую ячейку.
Также как и в Word для вычисления итогов в таблице Excelпредлагает функцию автосуммирования. В панели инструментов Стандартная для неё выведена специальная кнопка Автосуммирование. Для того чтобы воспользоваться данной функцией, можно установить указатель ячейки на ячейке, расположенной непосредственно снизу (справа) от ячеек со слагаемыми значениями. Excelпросмотрит рабочий лист сначала вверх, а затем влево от ячейки с формулой в поисках значений, которые можно сложить. Далее необходимо на панели инструментов Стандартная щелкнуть на кнопке Автосуммирование. Excelвставит функцию определения суммы в ячейку и предложит диапазон ячеек, значения которых следует сложить. Предложенный программой диапазон обозначается в рабочем листе бегущей рамкой. При необходимости предложенный диапазон можно изменить, выделив его указателем мыши при нажатой левой кнопке мыши. Если в ячейке будет представлена только сама функция СУММ(), то значит программа не смогла обнаружить подходящих ячеек со значениями, которые можно сложить. В пустых скобках функции будет отображён курсор ввода, это означает, что программа ждёт указания нужного диапазона ячеек. Завершается ввод формулы для определения суммы нажатием клавиши Enter. Подсчитать сумму данных, расположенных в столбце, можно и другим способом. Для этого перед нажатием кнопки Автосуммирование нужно выделить определённый диапазон ячеек, щёлкнуть на кнопке Автосуммирование и Excelавтоматически вставит функцию суммы в первую пустую ячейку, расположенную под выделенным диапазоном.
– 2 –
В Excelдиаграмма является формой графического представления числовых значений. Она также может быть использована для анализа данных рабочего листа. В зависимости от типа решаемой задачи можно определять вид её графического отображения. При этом речь может идти как о создании встроенной диаграммы, т.е. расположенной непосредственно на рабочем листе, так и о диаграмме, создаваемой на отдельном листе диаграммы. Диаграмма и данные рабочего листа, на основе которых она построена, связаны друг с другом независимо от места размещения диаграммы. Изменение данных в листе автоматически приведёт к изменениям в диаграмме.
Основные понятия и элементы диаграммы (см. рис. 1):
Тип диаграммы - определяет вид графического представления данных, в Excelиспользуется 15 типов диаграмм: 8 плоских, 6 объёмных и один смешанный.
Ряд данных - группа связанных точек данных диаграммы, построенных на основе значений одной строки или одного столбца таблицы и отображённых в диаграмме одним цветом.
Точка данных - описывает в диаграмме отдельное значение ряда данных.
Категория - несколько точек данных, являющихся элементами различных рядов и представляющие данные одного столбца (строки) рабочего листа (для линейчатой диаграммы и гистограммы), т.е. если для ряда данных используются значения строки, то значения столбцов образуют категории (и наоборот). Иначе говоря, категория является как бы «заголовком» для значений, отображаемых в диаграмме.
Любая диаграмма имеет две фоновые области: большая называется областью диаграммы, а меньшая - областью построения (чертежом).
Условные обозначения:
1 – надпись (название диаграммы) 7 – линия сетки
2 – надпись (название оси Х) 8 – ряды данных
3 – надпись (название категории) 9– точка данных
4– легенда 10 – метка данных
5 – деление оси 11 – шкала оси
6 – метка оси 12 – область построения
Рис. 1 Основные элементы диаграмм
Область диаграммы - включает в себя все элементы диаграммы: ряды данных, оси, название диаграммы и легенду. Элементы диаграммы поясняют данные, изображённые в ней, повышают информативность и читаемость диаграммы:
· В каждой диаграмме могут отображаться надписи, которые позволяют сделать диаграмму более выразительной и понятной. В качестве надписей в диаграмме используются названия диаграммы и её осей. Название диаграммы по умолчанию размещается по центру в верхней части диаграммы, название вертикальной оси - слева от оси, название горизонтальной оси - под названиями категорий.
· По осям диаграммы отображаются значения рядов данных (ось Y) и категории (ось Х). Шкала каждой оси разделяется на равные отрезки делениями, которые значительно облегчают считывание значений отдельных точек данных. Рядом с делениями могут располагаться их метки. По шкале оси X деления по умолчанию отображаются между отдельными категориями, а на шкале оси Y - рядом с каждой меткой. По умолчанию при создании диаграммы в неё будут добавлены основные деления осей, но пользователь может между ними задать вставку промежуточных делений. В диаграммах кроме основных осей могут отображаться и вспомогательные, которые применяют, если значения отдельных рядов сильно отличаются друг от друга.
· Оси диаграммы могут дополняться линиями сетки - это горизонтальные или вертикальные линии, которые помогают определить положение маркеров данных относительно шкал осей. Основные линии сетки продолжают основные деления шкалы, а промежуточные линии сетки - промежуточные деления шкалы.
· По умолчанию в правой части диаграммы может отображаться легенда, которая служит для идентификации рядов данных, т.е. в легенде выводится информация о том, как изображены в диаграмме значения различных рядов данных. Применение легенды целесообразно, если в диаграмме отображены как минимум два различных ряда данных.
Область построения (чертёж) - включает в себя собственно диаграмму. В плоской диаграмме чертёж ограничен осями, в объёмной - включает в себя также имена категорий, название диаграммы, шкалу и название осей.
Для построения диаграммы Excelпредоставляет в помощь пользователю мастера диаграмм, который последовательно в четырех окнах диалога запрашивает необходимую информацию и создаёт, в соответствии с ней, диаграмму. Мастер диаграмм создаёт основу диаграммы, которую можно будет затем настроить и видоизменить.
Для создания диаграммы в рабочем листе необходимо выполнить следующие действия:
1. Выделить в таблице данные для построения диаграммы. При этом желательно, чтобы левый столбец выделенного диапазона ячеек содержал названия строк, а первая строка - названия столбцов. Тогда мастер сразу включит их в диаграмму, расположив по осям или вставив в легенду.
Если таблица содержит итоговые строки, то перед построением диаграммы по данным этой таблицы в ней следует скрыть строки с итогами.
2. Щёлкнуть по кнопке Мастер диаграмм на панели инструментов Стандартная (или выполнить команду Вставка4Диаграмма). В результате на экране появится первое окно Мастера диаграмм, в котором выбирается нужный тип и подтип (формат) диаграммы щелчком по соответствующим элементам, а затем - по кнопке Далее
3. Во втором окне на вкладке Диапазон данных в поле Диапазон будет отображён выделенный диапазон ячеек. При необходимости можно указать другой диапазон ячеек путём ввода с клавиатуры или выделения в рабочем листе с помощью мыши, а также указать способ расположения данных – по строкам или по столбцам. Для перехода к третьему окну мастера нужно щёлкнуть по кнопке Далее.
4. На вкладках третьего окна мастера диаграмм задаются заголовки диаграммы, вывод легенды и способ ее расположения, выбираются параметры вывода подписей данных, вывод линий сетки и осей координат. После этого переходят к последнему окну мастера диаграмм щелчком по кнопке Далее.
5. В четвертом окне нужно указать, где будет располагаться диаграмма – на табличном листе или на специальном листе диаграмм. При этом можно ввести название листа диаграмм или указать на каком табличном листе поместить диаграмму. По окончании этих действий - щёлкнуть по кнопке Готово
В случае выбора способа размещения на рабочем листе на нем отобразится диаграмма в выделенном состоянии, т.е. на её рамке будут видны маркеры выделения. При этом пункт меню Данные будет заменен на Диаграмма. Меню Формат будет содержать только две команды, первая из которых будет предназначена для оформления выделенного элемента диаграммы.
Готовую диаграмму можно отредактировать, т.е. скопировать или удалить (клавиша Del), изменить размеры путём перетаскивания мышью за маркеры выделения, а также переместить всю область диаграммы по рабочему листу (если указатель мыши имеет вид стрелки). У диаграммы также можно поменять оформление, ввести недостающие или исправить существующие надписи, поменять цвета линий, фон, единицы измерения и шаг по осям и т.д.. Для этого следует предварительно выделить нужный элемент и применить к нему любую команду из меню Формат, Диаграмма, контекстного меню.
Выделение любого элемента диаграммы приведёт к появлению вокруг него прямоугольной рамки с маркерами выделения, а в поле имени - имени выделенного элемента. Это позволит перетаскивать внутри области диаграммы выделенные элементы, а также изменять их размеры. Например, выделив заголовок диаграммы и установив в нём курсор ввода, можно исправить или заново набрать его. Для выделения отдельного элемента группы (например, ряд данных или легенда), сначала нужно щёлкнуть на нужной группе элементов, а затем - ещё раз на нужном элементе (например, на точке данных или значке ключа легенды). Чтобы снять выделение элемента диаграммы или самой диаграммы, следует нажать клавишу Esc.
Готовую диаграмму можно дополнить различными графическими объектами - стрелками, поясняющими надписями, графическими фигурами и т.д.. Графический объект не является собственно составной частью рабочего листа, а только помещён на (прозрачной) поверхности листа. Для создания и изменения графического объекта Excelпредоставляет в помощь пользователю специальную панель инструментов Рисование, которая выводится на экран после щелчка по одноимённой кнопке. Любую надпись в диаграмме можно не набирать с клавиатуры, а связать поле надписи с содержимым определённой ячейки. Для этого необходимо выделить в диаграмме нужную надпись, ввести знак равенства, затем щёлкнуть в рабочем листе по соответствующей ячейке и подтвердить ввод.
Двойной щелчок по любому выделенному элементу диаграммы приведёт к выводу на экран соответствующего диалогового окна, в котором можно изменить параметры форматирования выделенного элемента. Диалоговое окно для выделенного элемента можно также вызвать выбором соответствующей команды из меню Формат. Если щелчок был произведён по заголовку диаграммы, то появится окно Форматирование названия диаграммы, если по оси - Форматирование оси. Дважды щёлкнув по элементу группы (например, сектору круговой диаграммы), можно поменять какие-нибудь параметры для этого элемента (например, цвет или узор). Если щёлкнуть на свободном участке области диаграммы или области построения, то появятся диалоговые окна для форматирования этих областей. Даже параметры линий сетки можно отредактировать, если дважды щёлкнуть по одной из них.
Кроме этого, рядом с диаграммой, когда она впервые появляется на листе, будет отображаться дополнительная панель инструментов Диаграмма, с помощью кнопок которой можно:
- поменять тип диаграммы,
- задать диаграмму по умолчанию,
- снова обратиться к мастеру диаграмм, чтобы поменять область данных или вместо рядов данных по строке попросить показывать ряды данных по столбцам,
- показать или убрать горизонтальные линии сетки,
- вставить / убрать легенду.
При выборе способа размещения на отдельном листе диаграмм лист с диаграммой будет вставлен перед текущим листом. При этом над диаграммой можно выполнить все перечисленные выше действия кроме изменения размеров самой диаграммы.
– 3 –
Одной из возможностей эффективно обрабатывать большие массивы данных в Excel является организация их в форме списка. Списком называют массив рабочего листа, организованный по принципу базы данных. При создании таблицы на рабочем листе Excel автоматически распознаёт список и соответствующим образом обрабатывает его. Графы таблицы при этом называют полями данных, а строки – записями. Поля должны содержать данные одного типа и формата, каждому полю присваивается имя. Имена полей рекомендуется размещать в первой строке списка, при этом возможен перенос по словам в пределах одной ячейки. Формат ячеек с именами полей должен отличаться от формата ячеек с данными списка, только в этом случае Excel автоматически распознает содержимое ячеек первой строки в качестве имён полей. В список нельзя включать пустые строки или столбцы.
Для создания нового списка нужно выполнить следующую последовательность действий:
¨в ячейки первой строки (или первых двух строк) введите имена полей списка;
¨присвойте этим ячейкам необходимые параметры форматирования;
¨приведите ширину столбцов в соответствие с заданной шириной имён полей;
¨введите одну запись списка;
¨в ячейках с результатами вычислений поместите необходимые формулы;
¨установите на ячейки первой записи списка форматы в соответствии с введёнными данными;
¨затем производится ввод следующих записей списка и анализ данных списка при помощи фильтров и итогов (см. далее).