ftd

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

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

Фильтр-Отобразить все.

Фильтрация с помощью формы данных. Excel позволяет работать с отдельными записями списка с помощью экранной формы (рис. 7.29). Основные операции обработки записей списка: последовательный просмотр записей, поиск или фильтрация записей по критериям сравнения, создание новых и удаление существующих записей списка.

Рис.7.29. Окно Формы

При установке курсора в область списка и выполнении команды ДанныеФорма на экран выводится форма, в составе которой имена полей — названия столбцов списка.

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

Для создания новой записи нажимается кнопка Добавить, выполняется заполнение пустых полей экранной формы; для перехода между полями формы используются курсор мыши либо клавиша ТаЬ. При повторном нажатии кнопки Добавить сформированная запись добавляется в конец списка.

Для удаления текущей записи нажимается кнопка Удалить. Удаленные записи не могут быть восстановлены, при их удалении происходит сдвиг всех остальных записей списка.

С помощью экранной формы задаются критерии сравнения. Для этого нажимается кнопка Критерии, форма очищается для ввода условий поиска в

полях формы с помощью кнопки 0чистить, а название кнопки Критерии заменяется на название Правка. После ввода критериев сравнения нажимаются кнопки Назад-Далее для просмотра отфильтрованных записей в нужном направлении.

При просмотре можно удалять и корректировать отфильтрованные записи списка. Для возврата к форме нажимается кнопка Правка, для выхода из формы — кнопка 3акрыть.

7.2. ТАБЛИЧНЫЙ ПРОЦЕССОР. Структурирование

При работе с большими таблицами часто приходится временно закрывать или открывать вложенные друг в друга части таблицы на разных иерархических уровнях. Для этих целей выполняется структурирование таблицы.

Структурирование таблицы — группирование строк и столбцов.

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

Структурирование выполняется с помощью команды Данные-Группа и Структура, а затем выбирается конкретный способ — автоматический или

ручной.

При ручном способе структурирования необходимо предварительно выделить область — смежные строки или столбцы. Затем вводится команда Данные-Группа и Структура-Группировать, которая вызывает окно Группирование для указания варианта группировки — по строкам или столбцам.

В результате создается структура таблицы со следующими элементами слева и/или сверху на служебном поле:

•линии уровней структуры, показывающие соответствующие группы иерархического уровня;

•кнопка (+) для раскрытия групп структурированной таблицы;

•кнопка (–) — для скрытия групп структурированной таблицы;

•кнопки Номера уровней 1, 2, 3 — для открытия или скрытия соответствующего уровня.

Для открытия (закрытия) определенного уровня иерархии необходимо

щелкнуть на номере уровня кнопки с номерами 1, 2, 3 и т.д. Для открытия (закрытия) иерархической ветви нажимаются кнопки «+»плюс, «-» (минус).

На рис. 7.30 показан фрагмент структурированной таблицы по учебным группам (по строкам), что показано в левом поле линией и кнопками со знаком плюс и минус. Кроме того, создан структурный элемент (линия в верхнем поле) в столбцах, позволяющий скрыть или показать столбцы (Код предмета, Табельный № преподавателя, Вид занятий).

Если внутри структурной части выделить группу и выполнить команду Данные-Группа и Структура-Группировать, будет создан вложенный структурный элемент нижнего иерархического уровня. При выделении группы, охватывающей другие структурные части таблицы, и выполнении команды Данные-Группа и Структура-Группировать создается структурный элемент верхнего иерархического уровня. Максимальное число уровней – 8.

Для отмены одного структурного компонента производится выделение области и выполняется команда Данные-Группа и Структура-Разгруппировать. Для отмены всех структурных компонентов таблицы — команда Данные-

Группа и Структура-Удалить структуру.

Рис. 7.30. Пример структурированной таблицы

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

После ввода в таблицу исходных данных и формул курсор устанавливается в произвольную ячейку списка и выполняется команда Данные-Группа и Структура-Создать структуру (или Создание структуры). Все структурные части таблицы создаются автоматически.

Структурированную таблицу можно выводить на печать в открытом или закрытом виде.

Пример такой таблицы приведен на рис.7.31.

Рис. 7.31. Фрагмент структурированной таблицы

В таблице расчета заработной платы введены столбцы, в которых по каждому работнику по формулам рассчитываются: общий налог, итоговая сумма доплат и сумма в выдаче. Кроме того, по каждому виду начислений (по столбцам) в строке Итого рассчитывается с помощью функции СУММ общая сумма. Порядок следования исходных данных и результатов (итогов) — слева направо, сверху вниз, что позволяет применить автоструктурирование таблицы.

Структурирование таблицы с автоматическим подведением итогов

Команда Данные-Итоги создает структуру таблицы и одновременно вставляет строки промежуточных и общих итогов для выбранных столбцов в соответствии с заданной функцией (табл.7.19).

Табл. 7.19

Функции при подведении автоматических итогов по группам

Операция Итоги позволяет выполнить детальный анализ сводных показателей для групп данных, объединенных каким-либо общим признаком.

Чтобы формирование итогов имело смысл, необходимо предварительно отсортировать исходные данные по желаемому признаку: (Рис. 7.32)

Рис. 7.32. Ввод данных в диалоговое окно

В окне Промежуточные итоги в поле (При каждом изменении) задать имя этого поля (в данном случае Фамилия).

Вполе Операция задать функцию выполнения (в данном случае Сумма).

Вполе Добавить итоги по… (выбрать Выручка), затем нажать ОК. результаты показаны в таблице.

Поля и переключатели в окне Промежуточные итоги имеют следующий смысл.

•Операция: определяет вид «итоговой» функции.

•Итоги выводятся под столбцом, который был выбран в разделе

Добавить итоги по:.

•Заменить текущие итоги – заменяет все полученные ранее итоги новыми. Если убрать флажок, то новые будут вставлены под полученными ранее, а не заместят их. Это позволяет выполнить процедуру Итоги несколько раз.

•Конец страницы между группами – автоматически вставляет конец страницы перед каждой группой данных, для которой вычисляются итоги. Это обеспечивает печать каждой группы на новой странице.

•Итоги под данными – помещает строки итогов и общих итогов под соответствующими данными. В противном случае они будут размещены вверху над данными.

•Убрать все – удаляет все итоги из таблицы

Вид окна с итоговой функцией приведен на рис. 7.33

Рис. 7.33. Пример окна с итоговой функцией

7.2. ТАБЛИЧНЫЙ ПРОЦЕССОР. Консолидация данных

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

Консолидация — агрегирование (объединение) данных, представленных в исходных областях-источниках.

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

Результат консолидации находится в области-назначения. Таблица консолидации создается путем применения функции обработки к исходным значениям. Области-источники могут находиться на различных листах или рабочих книгах. В консолидации может участвовать до 255 областейисточников. (Рис. 7.34).

Консолидированная таблица

Рис. 7.34. Представление о консолидации данных

Существуют следующие варианты консолидации данных:

•с помощью формул, где используются ссылки;

•по расположению данных для одинаково организованных областейисточников (фиксированное расположение);

•по категориям для различающихся по своей структуре области данных;

•с помощью сводной таблицы;

•консолидация внешних данных.

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

•все области на одном листе — в ссылках указывается адрес блока ячеек, например D1:C8;

•области на разных листах — в ссылках указывается название листа, диапазон, например лист1 !D1 :лист2!С8;

•области в разных книгах, на разных листах — в ссылках указывается название книги, название листа, диапазон, например,

[книга! ] лист1 !D 1: [книга2] лист2!С8.

При консолидации по расположению данных все источники имеют одинаковое расположение данных источников (имена категорий данных в выделяемые области-источники не включаются). Данные имеют одинаковую структуру, фиксированное расположение ячеек и могут быть консолидированы с определенной функцией обработки (среднее значение, максимальное, минимальное и т.п.) по их расположению. Для консолидации данных курсор устанавливается в область места назначения. Выполняется команда Данные, Консолидация, выбирается вариант и задаются условия консолидации.

Пример. На отдельных листах рабочей книги по каждой учебной группе хранятся сведения о среднем балле по фиксированному перечню предметов в разрезе видов занятий (рис.7.35). Серым цветом показана консолидируемая область источников.

Консолидируемая область

Рис.7.35. Пример областей-источников при консолидации данных

по расположению

При консолидации по категориям области-источники содержат однотипные данные, но организованные в различных областях-источниках неодинаково. Для консолидации данных по категориям используются имена строк и/или столбцов (имена включаются в выделенные области-источники). Выполняется команда Данные-Консолидация, выбирается вариант и задаются условия консолидации.

Пример. На рабочих листах представлена информация областейисточников в виде структуры (рис. 7.36). Число строк — переменное, состав предметов и виды занятий повторяются и могут рассматриваться как имена столбцов для консолидации по категориям. Серым цветом показана консолидируемая область источников.

Табл.7.20

При консолидации внешних данных в диалоговом окне Консолидация следует нажать кнопку 0бзор, в диалоговом окне Обзор выбрать файл, содержащий области-источники для добавления к списку, а затем добавить ссылку на ячейку или указать имя блока ячеек.

Переключатель Создавать связи с исходными данными создает при консолидации связи области назначения к областям-источникам. При изменениях в области назначения автоматически обновляются результаты консолидации.

Внимание! Нельзя корректировать ссылки на области-источники (добавлять или удалять новые области-источники) при наличии флажка переключателя Создавать связи с исходными данными.

В окне «Список диапазонов» для текущего рабочего листа итогов консолидации перечислены ссылки на все области-источники. Ссылки можно модифицировать: добавить новые области-источника, удалить существующие области-источника либо изменить его конфигурацию, если только до этого не был выбран переключатель Создавать связь с исходными данными.

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

7.2. ТАБЛИЧНЫЙ ПРОЦЕССОР. Сводные таблицы

Инструмент Сводная таблица из меню Данные является чрезвычайно мощным и удобным средством анализа и трансформации данных. С его помощью можно сделать практически любой «разрез» таблицы, получить итоги по любым данным.

Сводная таблица во многом объединяет возможности других обобщающих инструментов, таких как Итоги, Фильтр, Консолидация.

С помощью сводной таблицы можно произвольным образом изменить структуру исходных данных, располагая заголовки строк и столбцов удобным