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

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

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

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

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

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

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

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

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

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

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

Наимен	Колич	Цена	Стоим
молоко	3	3,5	10,5
молоко	4	12	48
молоко Всего	7		58,5
овощи	5	3,5	17,5
овощи	4	8	32
овощи Всего	9		49,5
хлеб	2	2	4
хлеб	1	2,5	2,5
хлеб Всего	3		6,5
Общий итог	19		114,5

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

Наимен	Колич	Цена	Стоим
молоко	3	3,5	10,5
молоко	4	12	48
овощи	5	3,5	17,5
овощи	4	8	32
хлеб	2	2	4
хлеб	1	2,5	2,5

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

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

Наимен	Колич	Цена	Стоим
молоко Всего	7		58,5
овощи Всего	9		49,5
хлеб Всего	3		6,5
Общий итог	19		114,5

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

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

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

	С к л а д 1
ТОВАР	ОТКУДА	ДАТА	СТОИМ
Книга	Киев	мар	55,0
Видео	С.-П.	янв	425,0
Книга	Киев	фев	16,5
Аудио	Москва	мар	148,0
Видео	С.-П.	апр	520,0
Аудио	С.-П.	май	623,0
Книга	Москва	июн	58,0
Аудио	Москва	янв	132,7
Видео	Москва	фев	455,0
Видео	Москва	апр	400,0

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

	С к л а д 2
ТОВАР	ОТКУДА	ДАТА	СТОИМ
Видео	Киев	мар	781,0
Книга	С.-П.	апр	59,0
Книга	Москва	май	122,0
Видео	Киев	июн	477,0
Аудио	Москва	мар	356,0
Аудио	Москва	апр	533,0
Видео	С.-П.	мар	699,0

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

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

Дата	Стоим
янв	557,7
фев	471,5
мар	2039,0
апр	1512,0
май	745,0
июн	535,0

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

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

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

двух таблиц

	Товар		Откуда			Дата	Стоим
	Видео						3757,0
	Книга						310,5
	Аудио						1792,7

Откуда	Дата	Стоим
Киев		1329,5
С.-П.		2326,0
Москва		2204,7

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

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

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

П

Создание СТ-1 в данном примере свелось к группировке данных по полю Наим и суммированию данных по полямКолич иСтоим, для чего курсор был установлен в начало создавае­мой таблицы, а затем выполнены следующие действия:

Данные-Сводные таблицы-Далее>-выделение исходной таблицы-Далее>-появляется третье окноМастерасводных таблиц, в котором справа представлены все поля исходной таб­лицы, а в центремакет сводной таблицы.

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

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

Таблица СТ-2 построена аналогично.

ример 2. Формирование потребительской корзины

Наим	Колич	Цена	Стоим	Дата
сыр	1	12	12	01.03.08
хлеб	3	2	6	08.03.08
мясо	2,5	13	32,5	08.03.08
молоко	2	3,5	7	06.03.08
мясо	0,4	23	9,2	12.03.08
хлеб	3	2	6	12.03.08
молоко	3	3,5	10,5	16.03.08
сыр	1	12	12	16.03.08
хлеб	2	2	4	23.03.08
сыр	3	12	36	23.03.08
СТ-1	С в о д н ы е т а б л и ц ы				СТ-2
Сумма по полю Колич			Сумма по полю Стоим
Наим	Всего		Наим		Всего
молоко	5		молоко		17,5
мясо	2,9		мясо		41,7
сыр	5		сыр		60
хлеб	8		хлеб		16
Общий итог	20,9		Общий итог		135,2

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

Сумма по полю Стр. А в т о р Глава Иванов Котов Петров Итог 1 8 0 0 8 2 10 11 3 24 3 0 9 5 14 4 7 0 0 7 Общий итог 25 20 8 53 Глава	Раздел	Автор	Страниц	Знаков	Дата
1	1.1.	Иванов	3	5634	05.07.04
1	1.2.	Иванов	5	8916	17.09.04
2	2.1.	Иванов	10	15490	30.11.04
2	2.2.	Петров	3	4900	21.06.04
2	2.3.	Котов	6	10675	11.09.04
2	2.4.	Котов	5	8234	01.02.05
3	3.1.	Петров	2	987	13.09.04
3	3.2.	Котов	9	14980	13.03.05
3	3.3.	Петров	3	5634	27.11.04
Здесь поле Главаразмещено в области строки, а полеАвторв области столбца, оба поля использованы для группировки данных. А в областьДанныеперемещены значения количества страниц (для 1-й СТ) и знаков (для 2-й), к которым по умолчанию применяется операция суммирования. 4	4.1.	Иванов	7	8465	15.04.05

Сумма по полю Знаков		А в т о р
	Глава	Иванов	Котов	Петров	Итог
	1	14550	0	0	14550
	2	15490	18909	4900	39299
	3	0	14980	6621	21601
	4	8465	0	0	8465
	Общий итог	38505	33889	11521	83915

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

О ТОВАР ОТКУДА ДАТА СТОИМ Книга Киев мар 55,0 Видео С.-П. янв 425,0 Книга Киев фев 16,5 Аудио Москва мар 148,0 Видео С.-П. апр 520,0 Аудио С.-П. май 623,0 Книга Москва июн 58,0 Аудио Москва янв 132,7 Видео Москва фев 455,0 Видео Киев мар 781,0 Книга С.-П. апр 59,0 Книга Москва май 122,0 Видео Киев июн 477,0 Аудио Москва мар 356,0 Аудио Москва апр 533,0 Книга Киев фев 128,0 Видео С.-П. мар 699,0 Аудио С.-П. июн 134,0 ТКУДА	Киев		
Сумма по полю СТОИМ	ДАТА
ТОВАР	фев	мар		май	июн		Общий итог
Видео	0	781		425	477		1683
Книга	144,5	55		0	0		199,5
О бщий итог	144,5	836		425	477		1882,5

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

ОТКУДА		С.-П.		
Сумма по полю СТОИМ	ДАТА
ТОВАР	янв		фев	мар		май		июн		Общий итог
Аудио	0		0	0		623		134		757
Видео	425		699	520		0		0		1644
Книга	0		0	59		0		0		59
Общий итог	425		699	579		623		134		2460