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

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

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

Операции со списками. Просмотр БД - листание записей списка - выполняют при помощи линейки прокрутки. Очистка поля - <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 полю
Дата Стоим
янв 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.Формирование потребительской корзины

Наим Колич Цена Стоим Дата
сыр 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  
Здесь поле Глава размещено в области строки, а поле Автор в области столбца, оба поля использованы для группировки данных. А в область Данные перемещены значения количества страниц (для 1-й СТ) и знаков (для 2-й), к которым по умолчанию применяется операция суммирования.  
4

4.1. Иванов 15.04.05  

 

Сумма по полю Знаков А в т о р  
  Глава Иванов Котов Петров Итог
 
 
 
 
  Общий итог

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

ОТКУДА Киев ¯      
           
Сумма по полю СТОИМ ДАТА    
ТОВАР фев мар май июн Общий итог
Видео
Книга 144,5 199,5
Общий итог 144,5 1882,5
                 

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

ОТКУДА С.-П. ¯        
             
Сумма по полю СТОИМ ДАТА    
ТОВАР янв фев мар май июн Общий итог
Аудио
Видео
Книга
Общий итог
                         







Дата добавления: 2014-12-13; просмотров: 1951;


Поиск по сайту:

При помощи поиска вы сможете найти нужную вам информацию.

Поделитесь с друзьями:

Если вам перенёс пользу информационный материал, или помог в учебе – поделитесь этим сайтом с друзьями и знакомыми.
helpiks.org - Хелпикс.Орг - 2014-2024 год. Материал сайта представляется для ознакомительного и учебного использования. | Поддержка
Генерация страницы за: 0.016 сек.