Работа со списками
Список - это организованная специальным образом информация таблицы - каждый столбец содержит данные одного типа(поле), каждая строка в списке называется записью. Все записи имеют одинаковую структуру, т.е. состоят из одних и тех же полей. Организация данных в списке аналогична организации баз данных, хотя термин база данных применяется чаще по отношению к информации, загруженной в таблицу из внешних баз данных. Такую информацию можно сортировать, фильтровать, суммировать, подводить промежуточные итоги, группировать данные.
Создание списка.Выполняется в любом месте рабочего листа. В ячейки вводят первую строку списка с именами полей (столбцов), которые определяют структуру списка, и следующую строку с данными - первую запись и все остальные.
Операции со списками. Просмотр БД - листание записей списка - выполняют при помощи линейки прокрутки. Очистка поля - <Del> на клавиатуре; удаление целой записи - Delete (Удалить). Поиск записей и их сортировка реализуются при помощи группы команд – Главная-Редактирование (Найти и Выделить и Найти и заменить) Последние две во мноом похожи между собой, имеют диалоговые окна, в которых исползуются одинаковые параметры настройки.
Например, если в поле ФИО задать Н*, будут выданы все фамилии, начинающиеся с буквы Н; для соотношения <=300, выбираются строки-записи со значениями переменной этого поля не более 300. Для выборки данных используют фильтр (Данные-кн.Сортировка и Фильтр или группа команд Сортировка и фильтрация) начинают с установки курсора на ключевое поле. Режим Автофильтр позволяет создать три типа фильтров: по значениям списка, по форматам и по условиям. Для установки автофильтра помещают в таблице и – Данные-Сортировка и фильтр-Фильтр и в строке заголовков столбцов появляются кнопки со стрелками, при нажатии на котрые можно установить нужное значение.
Упорядочивание записей таблицы начинают с установки курсора на любую ячейку списка данных (если сортируются все строки) или выделяют область сортировки и - команда Сортировка. Появляется диалоговое окно с полями Сортировать по.. и Затем по., которые позволяют задать до 3 ключей (уровней) сортировки при наличии групп данных, т.е. данных, имеющих одинаковые значения в ключевом поле. Так, если упорядочить данные некоторого списка с информацией о сотрудниках по полю Пол, список будет разбит на две группы - женщин и мужчин. В пределах каждой группы можно еще раз отсортировать данные - по фамилии, по году рождения и т.д. Такая сортировка называется многоуровневой (команда Настраиваемая сортировка). Она позволяет быстро формировать диапазоны строк в списках по одному сложному условию.
Промежуточные итоги (Данные-Структура-Промежуточные итоги). Эта функция позволяет получить итоговые значения для отдельных групп данных. Операцию нужно начинать с сортировки всех строк таблицы по тому полю, для групп данных которого будут вычисляться итоговые значения. Затем для получения итогов устанавливают курсор в любую ячейку списка, выбирают команду Данные-Итоги, в диалоговом окне устанавливают необходимые опции.
Нижерассматривается пример обработки упорядоченных данных Потребительской корзины.
Чтобы получить промежуточные итоги для заданной (Исходной) таблицы, нужно выполнить следующую цепочку действий:
Данные - Итоги - При каждом изменении в - Наименование - Использовать функцию: - Сумма - Добавить итоги по: - Колич - Стоим - Заменить текущие итоги - Итоги под данными - OK.
Промежуточные итоги
Наимен | Колич | Цена | Стоим |
молоко | 3,5 | 10,5 | |
молоко | |||
молоко Всего | 58,5 | ||
овощи | 3,5 | 17,5 | |
овощи | |||
овощи Всего | 49,5 | ||
хлеб | |||
хлеб | 2,5 | 2,5 | |
хлеб Всего | 6,5 | ||
Общий итог | 114,5 |
Упорядоч. исходная таблица
Наимен | Колич | Цена | Стоим |
молоко | 3,5 | 10,5 | |
молоко | |||
овощи | 3,5 | 17,5 | |
овощи | |||
хлеб | |||
хлеб | 2,5 | 2,5 |
Можно понизить уровень детализации списка. При выделении яч-ки с промежут. итогом левее вертикал. линейки появится кнопка со знаком "-" для удаления с экрана данного итога. Кнопка "+" повышает уровень детализации списка.
Удаление промежуточных итогов выполняется при помощи команды Данные-Промежуточные итоги-Удалить все итоги
Наимен | Колич | Цена | Стоим |
молоко Всего | 58,5 | ||
овощи Всего | 49,5 | ||
хлеб Всего | 6,5 | ||
Общий итог | 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 полю
|
Ниже представлены три варианта консолидации двух таблиц, полученные при выделении разных интервалов обеих таблиц .
Консолидация по всем полям
Консолидация по 2,3,4 полю
двух таблиц
Товар | Откуда | Дата | Стоим |
Видео | 3757,0 | ||
Книга | 310,5 | ||
Аудио | 1792,7 |
Откуда | Дата | Стоим |
Киев | 1329,5 | |
С.-П. | 2326,0 | |
Москва | 2204,7 |
Сводные таблицы (Вставка-Сводные таблицы).
Позволяют легко и быстро преобразовать структуру данных большого списка, чтобы данные сгруппировать и подытожить, сделать наглядными основные результаты вычислений и проанализировать информацию с различных точек зрения. Являются наиболее мощным средством по сравнению с методом консолидации и "промежуточными итогами".
Для облегчения процесса создания и обработки таких таблиц применяется Мастер сводных таблиц. С его помощью из полей исходной таблицы создается новая структура - макет сводной таблицы (в дальнейшем СТ). При этом некоторые поля исходной таблицы используются для группировки данных, над данными других полей выполняются суммирующие или другие операции. Если некоторое поле определяется как поле столбца в сводной таблице, а другое поле как поле строки, то в ячейке СТ на их пересечении будет находиться итоговый результат (сумма, количество, диапазон..). Лучше всего поясняется этот эффективный механизм на конкретных примерах.
|
Наим | Колич | Цена | Стоим | Дата | |
сыр | 01.03.08 | ||||
хлеб | 08.03.08 | ||||
мясо | 2,5 | 32,5 | 08.03.08 | ||
молоко | 3,5 | 06.03.08 | |||
мясо | 0,4 | 9,2 | 12.03.08 | ||
хлеб | 12.03.08 | ||||
молоко | 3,5 | 10,5 | 16.03.08 | ||
сыр | 16.03.08 | ||||
хлеб | 23.03.08 | ||||
сыр | 23.03.08 | ||||
СТ-1 | С в о д н ы е т а б л и ц ы | СТ-2 | |||
Сумма по полю Колич | Сумма по полю Стоим | ||||
Наим | Всего | Наим | Всего | ||
молоко | молоко | 17,5 | |||
мясо | 2,9 | мясо | 41,7 | ||
сыр | сыр | ||||
хлеб | хлеб | ||||
Общий итог | 20,9 | Общий итог | 135,2 | ||
Пример 3 П Л А Н С О З Д А Н И Я К Н И Г И С в о д н ы е т а б л и ц ы
| Раздел | Автор | Страниц | Знаков | Дата | ||||||||||||||||||||||||||||||||||||
1.1. | Иванов | 05.07.04 | |||||||||||||||||||||||||||||||||||||||
1.2. | Иванов | 17.09.04 | |||||||||||||||||||||||||||||||||||||||
2.1. | Иванов | 30.11.04 | |||||||||||||||||||||||||||||||||||||||
2.2. | Петров | 21.06.04 | |||||||||||||||||||||||||||||||||||||||
2.3. | Котов | 11.09.04 | |||||||||||||||||||||||||||||||||||||||
2.4. | Котов | 01.02.05 | |||||||||||||||||||||||||||||||||||||||
3.1. | Петров | 13.09.04 | |||||||||||||||||||||||||||||||||||||||
3.2. | Котов | 13.03.05 | |||||||||||||||||||||||||||||||||||||||
3.3. | Петров | 27.11.04 | |||||||||||||||||||||||||||||||||||||||
| 4.1. | Иванов | 15.04.05 |
Сумма по полю Знаков | А в т о р | ||||
Глава | Иванов | Котов | Петров | Итог | |
Общий итог |
Пример 4ПОСТУПЛЕНИЕ ТОВАРОВ НА СКЛАД С в о д н ы е т а б л и ц ы
ОТКУДА | Киев | ¯ | ||||||
Сумма по полю СТОИМ | ДАТА | |||||||
ТОВАР | фев | мар | май | июн | Общий итог | |||
Видео | ||||||||
Книга | 144,5 | 199,5 | ||||||
Общий итог | 144,5 | 1882,5 | ||||||
Примененный к оформлению сводной таблицы Автоформат затеняет поля, перенесенные в область Строки, Столбца и Страницы. Щелчок на клавише со стрелкой раскрывает страницу с данными для другого города.
ОТКУДА | С.-П. | ¯ | ||||||||||
Сумма по полю СТОИМ | ДАТА | |||||||||||
ТОВАР | янв | фев | мар | май | июн | Общий итог | ||||||
Аудио | ||||||||||||
Видео | ||||||||||||
Книга | ||||||||||||
Общий итог | ||||||||||||
Дата добавления: 2014-12-13; просмотров: 1957;