Использование функций базы данных
В Excel есть возможность сразу же в процессе поиска производить действия над числами, содержащимися в некоторых полях БД. Для этого используются функции БД, которые позволяют выполнить следующие действия: арифметические операции и статистический анализ величин, находящихся в отфильтрованных записях. При этом в ходе такого анализа определяются как минимальные так и максимальные значения, а также среднее и дисперсия. Вызов функций происходит кнопкой Мастер функций, далее – категория Работа с БД, где выбрать нужную функцию. Эти функции являются удобным средством анализа информации, содержащейся в БД.
Подведение промежуточных итогов
В понимании Excelподведение промежуточных итогов состоит в автоматическом разбиении строк на группы по изменению значения в ячейках одного столбца и вычисления математической функции над значением в ячейках одной группы. Т.о., таблица разбивается на несколько групп строк с одинаковым значением одного поля, и по каждой группе подводится итог, а затем - итог по всей таблице. В качестве итога может выполняться максимальное или минимальное значение, сумма, среднее количество элементов, стандартное отклонение и т. д.
Например, требуется подсчитать итог за год и общие итоги по зарплате для каждого работника.
Порядок действий:
1) щёлкнуть внутри списка.
2) Выбрать из меню ДанныекомандуИтоги.
3) В окне Промежуточные итоги в списке При каждом изменении выбрать то поле, повторяющиеся элементы которого будут сгруппированы для подведения итогов. Например, год.
4) Затем в списке Операция выбрать нужную операцию над данными, например Сумма.
5) В списке Добавить итоги по выбрать данные, которые должны участвовать в расчетах, установив флажки требуемых столбцов данных.
6) Установить требуемые флажки в полях: а) заменить текущие итоги, б) конец страницы между группами, чтобы печать одной группы осуществлялась на отдельной странице, в) итоги под данными, чтобы размещать итоговые данные под детальными данными,.
Т.о. получим таблицу, в которой появятся итоги.
Замечание: 1) Группировать можно и по фамилиям, но предварительно нужно отсортировать данные по полю Фамилия (одинаковые фамилии должны стоять рядом)
2) Для удаления промежуточных итогов: Данные/Итоги/Промежуточные итоги и нажать кнопку Убрать все.
Сводные таблицы
Подробный анализ данных из таблицы базы данных требует возможности получения сводной информации по записям базы данных. Результаты такого анализа могут быть представлены в виде сводной таблицы или сводной диаграммы.
Сводные таблицы могут строиться на базе внешнего источника данных или на основе списка Excel.
Сводная таблица состоит из четырех областей: Страница, Строка, Столбец и Данные.
Каждой области сводной таблицы должны соответствовать одно или несколько полей базы данных. Исключение делается только для поля Страница, которое можно оставить пустым. При заполнении сводной таблицы данные из соответствующих полей берутся автоматически. Для заполнения этих полей служат кнопки, названия которых совпадают с названиями полей базы данных. Поместить кнопку в нужное поле можно методом перетаскивания. В сводной таблице используются только те поля, которые необходимы. Незадействованные поля в отборе и вычислениях не участвуют.
Область Страница играет роль внешнего фильтра. Данные в остальной части таблицы относятся к значению, выбранному в этом поле. Область Строка содержит значения, определяющие содержимое строки таблицы и задает второй уровень сортировки. Аналогичным образом, область Столбцы определяет содержимое столбцов. В отличие от области Страница, в сводную таблицу помещаются данные для всех возможных значений полей, указанных в этих областях.
Область Данные определяет само содержание таблицы. В отличие от всех остальных областей, к данным, попадающим в ячейку таблицы, применяется функция для итоговых вычислений (по умолчанию – суммирование). Если необходимо изменить эту функцию, надо дважды щелкнуть на соответствующей кнопке и выбрать нужную операцию из раскрывающегося списка.
Кроме стандартного набора итоговых функций, можно использовать и дополнительные вычисления. Для этого надо щелкнуть на кнопке Дополнительно, выбрать нужное значение из раскрывающегося списка Дополнительные вычисления.
Замечание: непосредственно в сводной таблице нельзя изменять и вводить данные, можно только изменять форматирование и выбирать различные параметры вычислений.
Сводные таблицы создаются с помощью Мастера сводных таблиц.
Порядок действий для построения сводной таблицы:
1) щёлкнуть внутри списка,
2) из меню Данныевыбрать команду Сводная таблица. При этом будет загружен мастер сводных таблиц,
3) задать тип источника данных и тип отчёта, который необходим,
4) указать диапазон списка,
5) задать размещение планируемой сводной таблицы на новом листе или существующем. В этом же окне щелкаем кнопку макет или Готово,
6) перетащить нужные поля из Списка полей сводной таблицы в нужные области или можно выбрать требуемое значение в списке Размещение данных и нажать кнопку Поместить,
7) в сводную таблицу можно добавить определенный фильтр данных, для этого нужно переместить необходимое поле в область Страница, Для области Страница справа отображается раскрывающийся список, который и позволяет выбрать разные условия фильтрации,
8) выбрать вид сводной таблицы, нажав кнопку Формат отчета на Панели инструментов Сводные таблицы или из меню Формат.
Замечания:
1) Для того, чтобы данные в таблице обновлялись нужно щёлкнуть правой кнопкой в области данных и выбрать из контекстного меню команду Обновить данные.
2) Для настройки внешнего вида сводной таблицы надо щелкнуть на кнопке Параметры. Здесь можно задать имя листа сводной таблицы, включить режим суммирования данных по строкам и столбцам и др.
3) При создании сводной таблицы автоматически открывается и панель инструментов Сводные таблицы.
4) Если требуется изменить вид сводной таблицы, то это можно сделать с помощью кнопок, помещенных в таблице в соответствующих областях. При щелчке на соответствующей раскрывающей кнопке появляется небольшое окно, в котором можно отключить некоторые из отображаемых показателей
5) Щелкнув в пределах сводной таблицы правой кнопкой мыши и выбрав в контекстном меню пункт Отобразить страницы, можно разделить единую сводную таблицу на набор таблиц, соответствующих разным значениям, которые можно выбрать в области страницы.
6) Сводная таблица остается динамически связанной с данными, на основании которых она была построена. Для каждого значения можно выяснить, как оно было получено. Для этого дважды щелкаем на ячейке. При этом автоматически создается новый рабочий лист, содержащий выборку записей, использованных при вычислении данного значения.
7) Для создания сводной диаграммы по существующей сводной таблице используем кнопку Мастер диаграмм на панели Сводные таблицы.
Чтобы создать сводную диаграмму непосредственно на основе базы данных, надо запустить Мастер сводных таблиц командой Данные/Сводная таблица и на первом этапе работы мастера установить переключатель Сводная диаграмма. Формирование содержимого диаграммы осуществляется точно так же, как формирование сводной таблицы: перетаскиванием кнопок, соответствующих полям базы данных, в нужные области диаграммы.
Связывание ячеек
Связывание ячеек – это процесс, при котором ячейки из исходной таблицы (источника) не переносятся физически в другую таблицу (приемник), а создается связь с этими ячейками (записывается ссылка на них). При обращении к вставленным ячейкам будет происходить обращение к исходным ячейкам. Если в книге Excel есть таблица, в которой всегда содержится свежая информация, используемая в других книгах, можно не заботиться об ее обновлении во всех книгах. Достаточно просто вставить в ячейки этих книг связи с ячейками с обновленной информацией.
Предположим, что мы хотим видеть в некоторой ячейке листа, то, что находится в ячейке другого листа (другой книги). Для этого:
1. Выделить ячейки на листе-источнике и копировать их.
2. Сделать активной ячейку листа-приемника, с которой начинается ввод данных.
3. Команды Правка/Специальная вставка. Появится ДО «Специальная вставка».
4. В окне «Специальная вставка» в группе Вставить установить переключатель - все, а в группе Операция переключатель – нет.
5. Щелкнуть на кнопке Вставить связь.
Группа Операция содержит арифметические операции, которые могут быть применены к связываемым данным.
При открытии книг со связанными данными, значения в ячейках книги, содержащей связи, должны обновляться.
При этом вначале надо открыть книгу с исходными данными, тогда книга со связанными данными автоматически обновится при открытии.
Консолидация
Консолидация – это возможность Excel, позволяющая в одной ячейке обобщающей таблицы объединить значения из множества ячеек таблиц-источников посредством задаваемой математической функции (суммирование, среднее или др.). Обновление производится так же, как и в случае со связанными ячейками.