Работа со списками

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

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

Операции со списками. Просмотр БД - листание записей списка - выполняют при помощи линейки прокрутки. Очистка поля - < Del > на клавиатуре; удаление целой записи - Delete (Удалить). Поиск записей и их сортировка реализуются при помощи группы команд – Главная - Редактирование (Найти и Выделить и Найти и заменить) Последние две во мноом похожи между собой, имеют диалоговые окна, в которых исползуются одинаковые параметры настройки.

Например, если в поле ФИО задать Н*, будут выданы все фа­ми­лии, начинающиеся с буквы Н; для соотно­шения < =300, выбираются строки-записи со значениями переменной этого поля не более 300. Для выборки данных используют фильтр (Данные -кн. Сортировка и Фильтр или группа команд Сортировка и фильтрация) начинают с установки курсора на ключевое поле. Режим Автофильтр позволяет создать три типа фильтров: по значениям списка, по форматам и по условиям. Для установки автофильтра помещают в таблице и – Данные-Сортировка и фильтр - Фильтр и в строке заголовков столбцов появляются кнопки со стрелками, при нажатии на котрые можно установить нужное значение.

Упорядочивание записей таблицы начинают с установки курсора на любую ячейку списка данных (если сортируются все строки) или выделяют область сортировки и - команда Сортировка. Появляе­тся диалоговое окно с полями Сорти­ро­вать по.. и Затем по., которые позволяют задать до 3 ключей (уровней) сорти­ровки при наличии групп данных, т.е. данных, имеющих одинаковые значения в ключевом поле. Так, если упорядочить данные некоторого списка с информацией о сотрудниках по полю Пол, список будет разбит на две группы - женщин и мужчин. В пределах каждой группы можно еще раз отсортировать данные - по фамилии, по году рождения и т.д. Такая сортировка называется многоуровневой (команда Настраиваемая сортировка). Она позволяет быстро формировать диапазоны строк в списках по одному сложному условию.

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

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

Чтобы получить проме­жуточные итоги для заданной (Исходной) таблицы, нужно выполнить следующую цепочку действий:

Данные - Итоги - При каждом изменении в - Наименование - Использовать функцию: - Сумма - Добавить итоги по: - Колич - Стоим - Заменить текущие итоги - Итоги под данными - OK.

Промежуточные итоги

Упорядоч. исходная таблица

Можно понизить уровень детали­­за­ции списка. При выделении яч-ки с про­межут. итогом левее вертикал. линейки появится кнопка со знаком " - " для удаления с экрана данного итога. Кнопка " +" повышает уровень детализации списка.

Удаление промежуточных итогов выполняется при помощи команды Данные-Промежу­точные итоги-Удалить все итоги

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

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

В поле Функция выбирают необходимую операцию для получения итоговых данных. Если вместе с данными выделяются имена полей в столбцах или наименования строк, то их отмечают как заголовки в полях Использовать метки. Активизация поля Создавать связи с исходными данными ус­тановит режим автоматического обновления объединенных данных при изменениях в таблицах.

Пример 1. Консолидация данных двух таблиц
(Склад 1 и Склад 2) - три варианта

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

Ниже представлены три варианта консолидации двух таблиц, полученные при выделении разных интервалов обеих таблиц.

Консолидация по всем полям

Консолидация по 2, 3, 4 полю

двух таблиц

Сводные таблицы (Вставка-Сводные таблицы).

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

Для облегчения процесса создания и обработки таких таблиц применяется Мастер сводных таблиц. С его помощью из полей исходной таблицы создается новая структура - макет сводной таблицы (в дальнейшем СТ). При этом некоторые поля исходной таблицы используются для группировки данных, над данными других полей выполняются суммирующие или другие операции. Если некоторое поле определяется как поле столбца в сводной таблице, а другое поле как поле строки, то в ячейке СТ на их пересечении будет находиться итоговый результат (сумма, количество, диапазон..). Лучше всего поясняется этот эффективный механизм на конкретных примерах.

Пример 3 П Л А Н С О З Д А Н И Я К Н И Г И С в о д н ы е т а б л и ц ы

Пример 4 ПОСТУПЛЕНИЕ ТОВАРОВ НА СКЛАД С в о д н ы е т а б л и ц ы

Примененный к оформлению сводной таблицы Автоформат затеняет поля, перенесенные в область Строки, Столбца и Страницы. Щелчок на клавише со стрелкой раскрывает страницу с данными для другого города.