В Excel для различных типов вычислений имеется большое число встроенных функций: математических, статистических, логических, текстовых, информационных и т.д.
Функции вводятся обычным набором с клавиатуры или более предпочтительным способом – с помощью Мастера функций, диалоговое окно вызывается командой Вставка, Функция.
Все функции разделены на категории, каждая из которых включает в себя определенный набор функций.
Построение диаграмм
Диаграммы – это графическое представление данных. Они используются для анализа и сравнения данных, представления их в наглядном виде.
Диаграмма состоит из элементов: линий, столбиков, секторов, точек и т.п. Каждому элементу диаграммы соответствует число в таблице. Числа и элементы диаграммы связаны между собой таким образом, что при изменении чисел автоматически изменяется изображение элементов диаграммы и наоборот.
Различают два типа диаграмм:
Внедренные диаграммы – сохраняются на рабочем листе вместе с данными;
Диаграммные листы – диаграмма в формате полного экрана на новом листе.
Диаграмма создается с помощью Мастера диаграмм, вызываемого командой Вставка, Диаграмма или кнопкой.
Мастер диаграмм позволяет строить диаграммы 14 стандартных типов плоскостного и объемного представления (с областями, линейчатая, гистограмма, график, кольцевая, лепестковая, точечная, пузырьковая, поверхностная и др.) и 22 нестандартных типа.
Мастер диаграмм осуществляет построение новой диаграммы в интерактивном режиме за четыре шага только для выделенного блока ячеек – диапазона (области) данных для построения диаграммы.
Сводные таблицы
Команда Данные, Сводная таблица вызывает мастера сводных таблиц для построения сводов – итогов определенных видов на основании данных списков, других сводных таблиц, внешних баз данных, нескольких разрозненных областей данных электронной таблицы. Сводная таблица обеспечивает различные способы агрегирования информации.
Мастер сводных таблиц осуществляет построение сводной таблицы в несколько этапов:
Этап 1. Указание вида источника сводной таблицы. В зависимости от вида источника изменяются последующие этапы работы по созданию сводной таблицы.
Этап 2. Указание диапазона ячеек, содержащего исходные данные. Список (база данных) должен обязательно содержать имена полей (столбцов).
Этап 3. Построение макета сводной таблицы. Структура сводной таблицы состоит из следующих областей:
Страница – на ней размещаются поля, значения которых обеспечивают отбор записей на первом уровне; на странице может быть размещено несколько полей, между которыми устанавливается иерархия связи – сверху вниз; страницу определять необязательно;
Столбец – поля размещаются слева направо, обеспечивая группировку данных сводной таблицы по иерархии полей; при условии существования области страницы или строки определять столбец необязательно;
Строка – поля размещаются сверху вниз, обеспечивая группировку данных таблицы по иерархии полей; при условии существования области страницы или столбцов определять строку необязательно;
Данные – поля, по которым подводятся итоги, согласно выбранной функции; область определять необязательно.
Размещение полей выполняется путем их перетаскивания при нажатой левой кнопке мыши в определенную область макета. Каждое поле размещается только один раз в областях: страница, строка или столбец. По этим полям можно формировать группы и получать итоговые значения в области данные – группировочные поля. В области данные могут находиться поля произвольных типов, одно и то же поле может многократно размещаться в области данные. Для каждого такого поля задается вид функции и выполняется необходимая настройка.
Для изменения структуры сводной таблицы выполняется перемещение полей из одной области в другую. Для сводных таблиц существен порядок следования полей (слева направо, сверху вниз), изменяется порядок следования полей также путем их перемещения.
В макете сводной таблицы можно выполнить настройку параметров полей, размещенных в области данных.
Этап 4. Выбор места расположения и параметров сводной таблицы. В появляющемся на четвертом этапе диалоговом окне можно выбрать место расположения сводной таблицы, установив переключатель новый лист или существующий лист, для которого необходимо задать диапазон размещения.
Консолидация данных.
Одним из способов получения итоговой информации является консолидация данных, которая выполняется в соответствии с выбранной функцией обработки.
Консолидация – это агрегирование (объединение) данных, представленных в исходных областях-источниках.
Результат консолидации находится в области-назначения. Таблица консолидации создается путем применения функции обработки к исходным значениям. Области-источники могут находится на различных листах или рабочих книгах. В консолидации может участвовать до 255 областей – источников.
Существуют следующие варианты консолидации данных:
· С помощью формул, где используются ссылки;
· По расположению данных для одинаково организованных областей-источников (фиксированное положение);
· По категориям для различающихся по своей структуре области данных;
· С помощью сводной таблицы;
· Консолидация внешних данных.
При консолидации данных с помощью формул используемые в них ссылки могут иметь разное представление в зависимости от взаимного расположения областей-источника и области-назначения:
· Все области на одном листе – в ссылках указывается адрес блока ячеек;
· Области на разных листах – в ссылках указывается название листа, диапазон;
· Области в разных книгах, на разных листах – в ссылках указывается название книги, название листа, диапазон.
При консолидации по расположению данных все источники имеют одинаковое расположение данных источников (имена категорий данных в выделяемые области-источники не включаются). Данные имеют одинаковую структуру, фиксированное расположение ячеек и могут быть консолидированы с определенной функцией обработки (среднее значение, максимальное, минимальное и т.п.) по их расположению. Для консолидации данных курсор устанавливается в область места назначения. Выполняется команда Данные, Консолидация, выбирается вариант и задаются условия консолидации.
При консолидации по категориям области-источники содержат однотипные данные, но организованные в различных областях-источниках неодинаково. Для консолидации данных по категориям используются имена строк и/или столбцов (имена включаются в выделенные области-источники). Выполняется команда Данные, консолидация, выбирается вариант и задаются условия консолидации.
Условия консолидации задаются в диалоговом окне «Консолидация». В окне «Функция» выбирается функция консолидации данных. Для каждой области-источника строится ссылка, для чего курсор устанавливается в поле ссылки, затем переходят в область-источника для выделения блока ячеек и нажимается кнопка «Добавить».
При консолидации по категориям область имен входит в выделение, устанавливаются флажки подписи верхней строки или значения левого столбца. Excel автоматически переносит эти имена в область назначения.
Ссылка может иметь любую из следующих форм:
- ссылки на ячейки источники и назначение на одном листе
- ссылки на лист и ячейки источники и назначение на разных листах
- ссылки на книгу, лист и ячейки источники и назначение в разных книгах
- полный путь и все ссылки источники и назначение в различных местах
диска
- имя поименованной области область-источник поименована
При консолидации внешних данных в диалоговом окне «Консолидация» следует нажать кнопку «Обзор», в диалоговом окне «Обзор» выбрать файл, содержащий области-источники для добавления к списку, а затем добавить ссылку на ячейку или указать имя блока ячеек.
Переключатель Создавать связи с исходными данными создает при консолидации связи области назначения к областям-источникам. При изменениях в области назначения автоматически обновляются результаты консолидации.
В окне «Список диапазонов» для текущего рабочего листа итогов консолидации перечислены ссылки на все области-источники. Ссылки можно модифицировать: добавить новые области источника, удалить существующие области-источника либо изменить его конфигурацию, если только до этого не был выбран переключатель Создавать связь с исходными данными.
Для одного листа итогов консолидации набор ссылок на области-источники постоянен, на нем можно построить несколько видов консолидации с помощью различных функций. Курсор переставляется в новое место, выполняется команда Данные, Консолидация, выбирается другая функция для получения сводной информации.