Формирование промежуточных итогов
Применение расширенного фильтра
При применении расширенного списка критерии отбора задаются непосредственно на рабочем листе (в отдельном диапазоне ячеек). Диапазон ячеек рабочего листа, в котором указаны критерии расширенного фильтра, называют диапазоном критериев (условий).
Перед применением расширенного фильтра сначала необходимо создать диапазон критериев. Для этого выше или ниже списка в ячейках одной (верхней) строки нужно указать имена полей, для которых задаются критерии (например, копированием соответствующих имён полей из списка). В сроке (строках), расположенной непосредственно под строкой с именами полей, нужно ввести необходимые условия. При задании критериев можно использовать символы подстановки ? и *. Критерии, заданные в одной строке, будут выполняться одновременно (соответствует логическому оператору И). Если указать критерии в разных строках, то произойдёт соединение критериев оператором ИЛИ.
Перед запуском расширенного фильтра необходимо обязательно поместить указатель ячейки внутри списка (или выделить список), а затем выбрать команду Данные / Фильтр / Расширенный фильтр. В появившемся диалоговом окне в поле Диапазон условий нужно указать соответствующий диапазон в рабочем листе. Если есть необходимость в создании копии отфильтрованного списка, то следует выделить поле Скопировать результат в другое место и указать диапазон вставки в поле Поместить результат в диапазон. В качестве диапазона расположения результата можно задать диапазон только текущего рабочего листа, предварительно туда следует скопировать имена нужных полей и сформировать шапку выходного документа.
При использовании вычисляемых условий отбора в диапазоне критериев необходимо соблюдать следующее:
· Заголовок над ячейкой с вычисляемым условием не должен совпадать ни с одним именем поля в списке.
· В условии после знака = следует указать относительную ссылку на первую ячейку нужного поля, затем оператор сравнения, а после него - абсолютную ссылку на ячейку с формулой (эталоном), со значением которой будут сравниваться все значения указанного поля. Ячейку с формулой следует поместить вне списка и диапазона критериев.
Проанализировать данные списка можно путем определения в нём автоматических промежуточных итогов. При этом список будет структурирован, и вы можете скрыть или отобразить отдельные детальные данные щелчком по кнопкам с номерами соответствующих уровней, которые появятся слева от рабочего листа.
Добавление итогов следует производить только после предварительной сортировки списка по значению поля, в соответствии с которым отдельные записи списка должны быть разбиты на группы. Затем нужно выбрать команду Данные / Итоги. В появившемся диалоговом окне нужно выбрать:
· Из списка При каждом изменении в – поле, по которому список разбит на группы.
· В списке Операция - функцию, которая должна использоваться при определении промежуточных итогов.
· В поле Добавить итоги – поля, по которым должны быть подсчитаны промежуточные итоги.
· Если сбросить флажок в поле Итоги под данными, то итоги будут помещены сверху данных.
· Если установить флажок в поле Конец страницы между группами, то при печати списка отдельные группы записей будут выведены на разных страницах.
· Если сбросить флажок параметра Заменить текущие итоги, то можно получить вложенные промежуточные итоги для подгрупп записей, предварительно упорядочив их соответствующим образом.
Удаление промежуточных итогов происходит:
· При нажатии кнопки Убрать все в диалоговом окне Промежуточные итоги.
· При пересортировке списка, о чём будет выведено предупреждающее сообщение.
· При замене отображённых итогов новыми (при условии, что установлен флажок параметра Заменить текущие итоги).
– 4 –
Первоначально построение сводной таблицы производится с помощью Мастера сводной таблицы. Для его запуска следует предварительно выделить одну ячейку, относящуюся к базе данных, и выполнить команду Данные4Сводные таблицы.
На первом этапе работы мастера выбирается тип данных – В списке или базе данных Excel и тип оформления – Сводная таблица. После щелчка по кнопке Далее надо уточнить, правильно ли выбран диапазон базы данных. В последнем (третьем) окне мастера сводной таблицы следует выбрать местоположение сводной таблицы – в большинстве случаев следует размещать ее на новом рабочем листе. В этом же окне можно сформировать содержание и оформление сводной таблицы.
Для выбора содержания нужно щелкнуть по кнопке макет. В появившемся диалоговом окне будет отображен макет сводной таблицы, состоящий из четырех областей: Страница, Строка, Столбец и Данные. Справа будут отображены кнопки с названиями полей базы данных.
Каждой области сводной таблицы должны соответствовать одно или несколько полей базы данных. Исключение делается только для поля Страница, которое можно оставить пустым. Поместить кнопку с названием поля нужную область можно методом перетаскивания.
Область Страница играет роль внешнего фильтра. Данные в остальной части таблицы относятся к значению, выбранному в этом поле. Область Строкасодержит значения, определяющие содержимое строки таблицы, и задает второй уровень сортировки. Аналогичным образом, область Столбец определяет содержимое столбцов. Область Данные определяет собственно содержимое таблицы. К данным этой области по умолчанию применяется функция суммирования. Если необходимо изменить эту функцию, то надо дважды щелкнуть на соответствующей кнопке и выбрать нужную операцию из раскрывающегося списка. В появившемся окне кроме стандартного набора функций можно выбрать и дополнительные вычисления. Для этого нужно щелкнуть по кнопке Дополнительно.
Для настройки внешнего вида сводной таблицы надо щелкнуть по кнопке Параметры в третьем окне мастера сводной таблицы. В появившемся окне можно задать имя листа сводной таблицы, включить (отключить) режим суммирования данных по строкам и столбцам, разрешить (запретить) доступ к информации, использованной при вычислении, выполнить другие настройки.
После щелчка по кнопке Готово сводная таблица строится автоматически. При создании сводной таблицы автоматически открывается и панель инструментов Сводные таблицы.
С помощью кнопок в соответствующих областях таблицы можно изменить ее внешний вид. Для этого нужно щелкнуть по раскрывающей кнопке и в появившемся окне отключить некоторые из отображаемых показателей. Для области Страницасправа отображается раскрывающийся список, позволяющий выбрать разные условия фильтрации. Кроме того, щелкнув в пределах сводной таблицы правой кнопкой мыши и выбрав в контекстном меню пункт Отобразить страницы, можно разделить единую сводную таблицу на набор таблиц, соответствующих разным значениям, которые можно выбрать в области Страница.
Сводная страница остается динамически связанной с данными, на основании которых она была построена. Например, чтобы узнать, каким образом было получено то или иное значение, нужно дважды щелкнуть по соответствующей ячейке. При этом автоматически создается рабочий лист, содержащий выборку записей, использованных при вычислении данного значения.
При изменении исходных данных данные сводной таблицы обновляются при выборе одноименной команды в контекстном меню сводной таблицы. Однако, при удалении или добавлении записей в базу данных обновление произойдет только после повторного вызова мастера сводной таблицы. Для этого достаточно воспользоваться кнопкой Мастер сводных таблиц панели инструментов Сводные таблицы. В появившемся окне щелчком по кнопке Назад следует перейти ко второму окну мастера и произвести повторное выделение всех записей базы данных, а затем щелкнуть по кнопке Готово.
По данным сводной таблицы можно построить сводную диаграмму. Для этого достаточно щелкнуть по кнопке Мастер диаграмм панели инструментов Сводные таблицы.
– 5 –
Консолидация данных – это объединение данных, представленных в исходных областях источниках. Результат консолидации находится в области назначения. Консолидированная таблица создается путем применения функции обработки к исходным значениям. Области-источники могут находиться на различных листах или рабочих книгах.
Существуют следующие варианты консолидации данных:
1. С помощью формул, где используются ссылки
2. По расположению данных для одинаково организованных областей-источников
3. По категориям для различающихся по своей структуре области данных
4. С помощью сводной таблицы
5. Консолидация внешних данных
À можно выполнить при помощи команды Специальная вставка. Для этого ячейки с исходными данными копируются в буфер обмена, а затем в области назначения выполняют командуСпециальная вставка, в диалоговом окне которой следует щелкнуть по кнопке Вставить связь.
Á выполняется при помощи команды Данные4Консолидация. В появившемся диалоговом окне в поле Функция выбирается функция обработки данных, а в поле Список диапазонов перечисляют области-источники. Это происходит путем выделения в поле Ссылка очередной области и добавления ее к списку щелчком по кнопке Добавить. Так как в данном случае данные находятся в таблицах с одинаковыми названиями строк и столбцов, то в качестве областей-источников достаточно указывать ячейки с исходными данными. При установленном флажке Создавать связи с исходными данными будет происходить автоматическое обновление консолидированной таблицы при изменении исходных данных в областях-источниках.
 выполняется аналогично предыдущему способу. Так как области-источники в данном случае имеют различный набор наименований строк и столбцов, то их следует включать в область выделения. В окне Консолидация следует установить флажки в поля Подписи верхней строки или Значения левого столбца, тогда они будут автоматически перенесены в область назначения
Ä при этом способе в окне Консолидация дополнительно следует нажать кнопку Обзор и в появившемся окне выбрать файл, содержащий области-источники.
– 6 –
Прежде чем печатать таблицу, стоит разбить её на страницы, чтобы уже перед печатью знать, сколько печатных страниц она займёт. Для этого необходимо установить флажок параметра Авторазбиение на страницы на вкладке Вид диалогового окна Параметры. Excel, примерно так же, как и Word, отметит границы страниц тонкой пунктирной линией в соответствии с установками, указанными в Параметрах страницы. Если заданное таким образом разбиение на страницы не устраивает пользователя, то можно произвести принудительное разбиение на страницы. Для этого нужно выделить ячейку ниже и справа от того места, где требуется вставить разрыв страницы. Эта ячейка окажется в левом верхнем углу новой страницы. Затем выбрать команду Вставка / Разрыв страницы. Границы страниц при этом отобразятся в виде полужирных пунктирных линий.
Перед печатью необходимо, задав параметры страницы, просмотреть выводимую информацию в режиме предварительного просмотра. В Excel эти операции выполняются так же, как и в Word. Поэтому рассмотрим только отличия в соответствующих командах этих приложений.
Для команды Параметры страницы существуют следующие отличия:
· на вкладке Страница можно задать увеличение или уменьшение листа при выводе его на печать (указав масштаб изображения в процентах к исходному;
· на вкладке Поля дополнительно можно указать центрирование выводимой информации по горизонтали и вертикали;
· на вкладке Колонтитулы, если нет необходимости в печати на каждой странице в верхней части имени текущего рабочего листа, а в нижней - номера печатаемой страницы с дополнительным текстом Страница, то нужно в полях Верхний колонтитул и Нижний колонтитул открыть список и выбрать позицию(нет);
· на вкладке Лист можно определить содержимое определённых строк и (или) столбцов в качестве заголовков и распечатать на всех страницах сверху или слева от данных.
Запуск печати можно осуществить щелчком по кнопкам Печать на панели инструментов Стандартная, в режиме предварительного просмотра и окна диалогаПараметры страницы. При этом на печать будет выведен текущий лист с учётом заданных ранее параметров. Если необходимо распечатать только часть текущего листа, несколько страниц или всю книгу, или несколько копий, следует выбрать команду Файл / Печать и задать в появившемся диалоговом окне нужные параметры.
При выводе на печать диаграммы, помещённой на отдельном листе диаграмм, можно определить её размер. Данная процедура выполняется на соответствующей вкладке окна диалога Параметры страницы (только в том случае, если текущим листом является лист диаграммы).
Если установить в поле Размер диаграммы при выводе на печать переключатель в положение:
· Использовать всю страницу - диаграмма заполнит всю страницу вплоть до полей. Пропорции диаграммы при этом сохранены не будут.
· Уместить на странице - вертикальные и горизонтальные размеры диаграммы будут изменяться с сохранением пропорций до тех пор, пока один из размеров не достигнет полей страницы.
· Пользовательский - размер диаграммы при выводе на печать будет соответствовать размеру диаграммы в листе диаграмм.