Списки в Excel – это таблицы, строки которых содержат однородную информацию. В Excel список называют базой данных, при этом строки таблицы – это записи базы данных, а столбцы – поля записей. Чтобы таблица Excel воспринималась, как база данных, столбцам должны быть присвоины однострочные имена, которые будут использоваться как имена полей записей базы данных. При этом необходимо соблюдать некоторые правила:
1. на одном рабочем листе не следует помещать более одного списка;
2. отделять список от других данных рабочего листа хотя бы одним свободным столбцом или одной свободной строкой.;
3. имена полей списка должны располагаться в первой строке таблицы;
4. чтобы отделить имена полей от данных, следует поместить рамку по нижнему краю ячеек строки с именами столбцов;
5. каждый столбец списка должен содержать во всех строках однотипные данные;
6. не следует вводить дополнительные пробелы в начале ячеек данных, поскольку они влияют на сортировку и поиск;
7. внутри списка не должно быть полностью пустых строк и столбцов.
Фильтрация списков в Excel. Как правило базы данных используются для поиска и отображения определенной информации. В Excel для этих целей служат фильтры. Имеются две разновидности фильтров Автофильтр и Расширенный фильтр.
Автофильтр. Для использования автофильтра надо поставить активную ячейку в область списка или выделить весь список, причем обязательно с заголовками столбцов. По команде Данные Þ Фильтр в строке заголовков таблицы появляются кнопки с раскрывающимися списками значений. В автофильтре можно осуществлять поиск данных по двум правилам сравнения - по точному значению или по условию.
Точное значение для сравнения выбирается из раскрывающегося списка для указанного поля (рис. 5.31). В этом же списке можно задать опцию отображения первых десяти значений для текстовых полей и первых n наибольших или наименьших значений для числовых. При выборе в раскрывающемся списке опции Настраиваемые фильтры вы попадаете в диалоговое окно задания критерия отбора, который состоит из двух частей, связанных между собой логической связкой И, либо ИЛИ (рис. 5.32). Каждая часть условия включает оператор отношения, значение, которое может выбираться из списка или содержать шаблонные символы «*», «?».
Рисунок 5.31 - Таблица с установленным автофильтром и открытым списком для поля «Оклад».
Рисунок 5.32 - Окно задания условия отбора.
Можно задать условия для отбора нескольких столбцов независимо друг от друга, фильтрация выполняется по всем условиям одновременно. Все записи, не прошедшие через фильтр, будут скрыты. Отфильтрованные записи можно выделить и скопировать в другое место, очистить содержимое, удалить и т. д.
Расширенный фильтр. Команда Данные Þ Сортировка и фильтр Þ Дополнительно использует два типа критерия для поиска данных – сравнение и вычисляемый критерий.
Важной особенностью этого режима является необходимость формирования заранее, до выполнения самой команды фильтрации, специальной области называемой Диапазон условий. Он располагается в нескольких строках и столбцах над списком, следовательно, если вы предполагаете применение расширенного фильтра, то следует над таблицей оставить пустые строки.
Условия создаются по следующим правилам (рис. 5.33):
- состав столбцов диапазона условий определяется столбцами, по которым задаются условия фильтрации записей, причем порядок их следования безразличен;
- метки критериев должны точно совпадать с названиями столбцов исходного списка;
- ниже меток располагаются критерии сравнения типа.
Правила формирования множественного критерия (для нескольких полей):
- если критерии (условия) указываются в каждом столбце, то они считаются связанными условием «И»;
- если условия записаны в нескольких строках, то они считаются связанными условием «ИЛИ».
Рисунок 5.33 - Пример задания условия отбора записей в расширенном фильтре.
Вычисляемый критерий представляет собой формулу, записанную в строке условий интервала критериев, которая возвращает логическое значение Истина или Ложь. Формула строится с использованием адресов ячеек, встроенных функций, констант различных типов (число, текст, дата, логическая константа), операторов отношения.
Когда условия отбора сформированы, мы открываем диалоговое окно расширенного фильтра (рис. 5.34), в котором задаем область размещения результата отбора записей, область списка (определяется по умолчанию), диапазон условий.
Рисунок 5.34 - Диалоговое окно задания параметров расширенного фильтра.
Для снятия действия условий фильтрации выполняется команда Данные Þ Сортировка и фильтр Þ Очистить.
Сортировка списков в Excel в заданном порядке выполняется с помощью команды Данные Þ Сортировка, предварительно выделив весь список с заголовками столбцов, кроме итоговых строк таблицы, если они присутствуют. Включение заголовков столбцов в область выделения облегчает настройку сортировки, т. к. эти имена можно использовать в качестве ключей сортировки. В противном случае ключами сортировки будут стандартные имена столбцов таблицы Excel.
В диалоговом окне, которое открывается командой Сортировка, можно задать до трех ключей с указанием порядка сортировки. Сортировка выполняется сначала по первому ключу, затем в строках с совпадающим значением первого ключа – по второму ключу, наконец, в строках с одинаковыми значениями первого и второго ключа – по третьему ключу.
1. Форма данных. При установке курсора в область списка и щелчка мышкой на кнопке в панели быстрого доступа Форма на экран выводится форма, в составе которой имена полей – название столбцов списка. С помощью формы можно просматривать записи, вводить новые, удалять, осуществлять отбор записей по критериям. Для просмотра записей используется полоска прокрутки или кнопки <Далее>, <Назад>, выводится индикатор номера записи. При просмотре записей возможно их редактирование. Поля, не содержащие формул, доступны для редактирования, а вычисляемые или защищенные поля не редактируются. При отсутствии кнопки форма на панели быстрого доступа ее можно достать с помощью команды Кнопка Office Þ Параметры Þ Настройка Þ Все команды.
| Рисунок 5.35 |
Если таблица уже содержит итоговые строки, в нее можно добавить новые итоговые значения, рассчитанные с помощью другой функции. Для этого в окне Промежуточные итоги следует снять флажок Заменить текущие итоги и задать нужное поле и функцию. Два других флажка позволяют размещать итоги под или над строками данных и выводить каждую группу значений данных на отдельном листе. Если промежуточные итоги больше не нужны, то список можно привести в исходное состояние, для этого достаточно щелкнуть на кнопке Убрать все.
Команда Итоги позволяет выполнять следующие операции - Сумма, Количество, Среднее, Максимальное, Минимальное, Произведение и т. п.
Создание структуры.Многие типичные электронные таблицы создаются в иерархическом стиле. Например, рабочий лист, предназначенный для ведения бюджета, может содержать столбец для каждого месяца года, после которого помещается столбец с итогами. Для каждой строки статьи расходов итоговый столбец суммирует значения в каждом месячном столбце. В структурах такого рода можно считать, что месячные столбцы подчинены итоговому столбцу, потому что их значения вносят вклад в значение итогового столбца.
Подобным образом строки расходов могут быть также организованы в иерархическом порядке, с группировкой по категориям статей расходов. Microsoft Excel может преобразовать рабочие листы такого типа в структурированные документы.
Единственное различие между структурированными и неструктурированными листами состоит в том, что мы получаем возможность оперативно скрывать и отображать столбцы и строки с данными, находящимися между итоговыми значениями, например столбцы с объемами продаж по месяцам, заключенные между столбцами суммы продаж по кварталам. Без структурирования вам придется скрывать каждую группу столбцов и строк вручную, а в структурированном рабочем листе можно мгновенно сжать структуру документа для изменения уровня детализации.
Чтобы автоматически создать структуру для части рабочего листа, сначала выделите интересующую вас область листа. В меню выберите команду Данные Þ СтруктураÞ Группировать Þ Создать структуру. Чтобы создать структуру для всего листа, выделите одну ячейку перед выбором команды Создать структуру. Через некоторое время, зависящее от сложности рабочего листа, на экране появится уже структурированный документ.
Консолидация данных. Другим способом получения итоговой информации является консолидация – агрегирование согласно выбранной функции обработки данных, представленных в исходных областях-источниках. Результат консолидации находится в области назначения. Области-источники могут находиться на различных листах или рабочих книгах. В консолидации может участвовать до 255 областей-источников, а сами источники могут быть закрыты во время консолидации.
Для консолидации данных курсор устанавливается в область назначения. Развернуть ленту Данные щелкнуть на кнопке Консолидация, выбирается вариант и задаются условия консолидации.
Существуют следующие варианты консолидации:
- консолидация по расположению для одинаково организованных источников (фиксированное расположение);
- консолидация по категориям для различающихся по расположению данных;
- консолидация внешних данных.
При консолидации по расположению все источники имеют одинаковое расположение данных источников, что позволяет использовать ссылки на файлы и ячейки для консолидирования таблицы. Данные имеют одинаковую структуру, фиксированное расположение ячеек и могут быть консолидированы с определенной функцией обработки (максимальное, минимальное, среднее значение и т. п.) по их расположению.
При консолидации по категориям области-источники содержат однотипные данные, но организованные в различных областях-источниках неодинаково. Для консолидации данных по категориям используются метки строк и столбцов либо как строк, так и столбцов, которые должны совпадать. Метки и консолидируемые данные должны находиться в непосредственной близости друг с другом.
При консолидации внешних данных следует нажать кнопку Пролистать, в диалоговом окне Пролистать выбрать файл, содержащий области-источники для добавления к списку, а затем добавить ссылку на ячейку или указать имя блока ячеек.
Переключатель Создавать Связи с Исходными Данными создает при консолидации связи области назначения к областям-источникам.