8. Списки и базы данных ms Excel.

Microsoft Excel позволяет работать с реляционными структурами, которые носят название списка или базы данных Excel. Список — таблица прямоугольной конфигурации, столбцы таблицы считаются полями, строки - записями базы данных реляционного типа. Список должен отвечать ряду требований:

• список целиком помещается на одном рабочем листе, максимальный размер списка — 65 535 строк и 256 столбцов;

• список должен отделяться от других данных рабочего листа хотя бы одним незаполненным столбцом и/или строкой;

• имена столбцов списка располагаются в первой строке таблицы, имена могут быть многострочными с переносами слов;

• ячейки одного столбца списка содержат однородную информацию.

Традиционные операции со списками:

1. Ввод и редактирование данных списка.

2. Сортировка — упорядочивание записей (строк/столбцов таблицы) списка.

3. Фильтрация (отбор) записей списка по условиям.

4. Агрегирование информации списка, вычисление статистических оценок (свод­ные таблицы, промежуточные итоги).

5. Структурирование данных (формирование новых группировок строк и столб­цов).

6. Консолидация (объединение данных нескольких списков с формированием общих итогов).

В список можно вносить любые данные: персональные данные о служащих, данные о реализации продукции, перечень складских запасов, телефонные номера и адреса клиентов и даже рецепты приготовления различных блюд. Любую информацию, которую нужно хранить, систематизировать, искать и обобщать, можно оформить в виде списка. Структура списка.

Список состоит из трех основных структурных элементов:

• Записи (строка) - подобна карточкам библиотечного каталога. Каждая запись должна содержать полное описание конкретного элемента.

• Поля - это отдельные элементы данных в записи. Каждое поле в записи

можно считать объектом поиска или сортировки. Записи могут включать столько полей, сколько потребуется. Чем больше полей у элементов списка, тем легче будет в дальнейшем получить нужную информацию.

• Заглавная строка состоит из заголовков столбцов и располагается в

самом начале списка. Заголовки - это метки соответствующих полей. Ехсеl использует их при сортировке, поиске выдаче отчетов по спискам. Для облегчения работы со списком можно выделять заглавную строку с помощью форматирования. Заглавная строка располагается в самом начале списка. В списке Ехсеl каждая ячейка в столбце - это поле, а каждая строка - запись.

Рассмотрим базу данных на примере

№	Клиент	Номер товара	Кол-во	Дата заказа	Цена	Сумма
1	АО"Весна"	С009	3	27.01.2004	10000	30000
2	МП"Вист"	СО10	2	22.01. 2004	20000	40000
3	МП"Вист"	СО10	2	22.01. 2004	20000	40000
4	АО"Весна"	С009	5	24.01. 2004	10000	50000
5	АО"Весна"	СОЮ	1	20.01. 2004	20000	20000
6	ТОО"Улыбка"	С005	8	29.01. 2004	5000	40000
7	МП"Вист"	С008	4	23.01. 2004	10000	40000
8	АО"Шлем"	С008	2	27.01.2004	30000	60000
9	МП"Вист"	С009	6	22.01.2004	10000	60000
10	АО"Шлем"	С006	5	21.01.2004	35000	175000
11	АО"Шлем"	С005	10	21.01.2004	5000	50000
12	ТОО"Прима"	С009	3	22.01.2004	10000	30000
13	ТОО"Прима"	СО10	2	17.01.2004	20000	40000
14	АО"Шлем"	С007	4	21.01.2004	40000	160000
15	АО"Рассвет"	С007	3	21.01.2004	40000	120000
16	АО"Рассвет"	С008	2	17.01.2004	30000	60000
17	ТОО"Улыбка"	С006	3	28.01.2004	35000	105000

При создании списка не следует:

• отделять заглавную строку от записей пустыми строками или строками, содержащими пунктиры, линии и т.д.

• начинать поле с пробела;

• размещать на листе со списком другие данные.

Способы создания списка

Самый простой способ создания списка, которым можно построить за три шага.

1) Сформировать заглавную строку. В каждом столбце этой строки ввести название соответствующего поля записи.

2

) Щелкнуть на любой из ячеек заглавной строки, затем в менюДанные выбрать команду Форма. На экране появится диалоговое окно, содержащее поля, названия и количество которых соответствует созданным заголовкам столбцов.

3) Ввести данные в каждую графу диалогового окна. Для перехода из графы в графу использовать клавишу Таб. Закончив ввод, щелкнуть по кнопке Добавить. Данные из граф диалогового окна будут помещены в соответствующие поля записи списка. После этого можно вводить следующую запись. Затем снова щелкнуть по кнопке Добавить - еще одна запись помещена в список.

Можно также построить список, вводя данные непосредственно в соответствующую ячейку списка.

Сортировка списков.

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

1. Вначале сортируются в заданном порядке записи по содержимому первого поля.

2. Выполняется дополнительная сортировка записей, у которых элементы по первому полю оказались одинаковыми (сортировка таких записей осуществляется в заданном для второго поля порядке).

3. В последнюю очередь происходит сортировка тех записей, у которых элементы по первому и второму полю оказались одинаковыми (с применением своего порядка сортировки, заданного для третьего поля).

Если задан возрастающий порядок сортировки по определенному полю, то записи будут расположены в зависимости от содержимого этого поля:

• числа сортируются от наименьшего отрицательного до наибольшего положительного;

• значения даты и времени будут отсортированы в порядке от наиболее раннего до наиболее позднего времени;

• текст - в алфавитном порядке от А до 2, затем от А до Я;

• логические значения - сначала значения ЛОЖЬ, а затем значения ИСТИНА;

• тексты сообщений об ошибках, возникших при вычислении по формулам, будут отсортированы в порядке их следования.

Расположение записей при сортировке списка в убывающем порядке противоположно рассмотренном у. Для сортировки списка необходимо выполнить следующие действия:

1. Активизировать любую ячейку списка.

2. Выполнить команду Сортровка... меню Данные. При этом Ехсеl автоматически выделит диапазон из всех ячеек списка и на экране появится диалоговое окно Сортировка диапазона.

3. Задать нужный порядок сортировки по одному, двум или трем полям списка, выбрать их имена в соответствующих полях диалогового окна.

4. Нажать ОК.:

Пример 1. Провести сортировку столбца "Клиент" в алфавитном порядке.

а) проконтролировать, что курсор находится в списке.

б) Выполнить команды Данные. Сортировка.

Пример 2: Отсортировать по "Дате заказа" (по убыванию).

Пример 3: Отсортировать список по "клиенту" и по "количеству товара". Сначала необходимо вернуть список в первозданный вид (столбец № отсортировать по возрастанию).

Указание В поле "сортировать по" выбрать Клиент, а в поле " Затем по"-количество товара

Фильтрация данных.

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

Второй способ рекомендуется использовать в следующих случаях:

• когда условие отбора должно одновременно применяться к ячейкам двух и более столбцов;

• когда к ячейкам одного столбца необходимо применить три и более условий отбора;

• когда в условии отбора используется значение, полученное в результате вычисления заданной формулы.

Чтобы обработать таблицу с помощью автофильтра, необходимо вначале выбрать в качестве активной любую ее ячейку. После этого в меню Данные.Фильтр.Автофильтр. Как только команда будет выполнена, в первой строке таблицы рядом с именем каждого поля появятся кнопки со стрелками. Нажатие любой из этих кнопок приводит к открытию соответствующего набора строк. Каждая строка набора представляет собой одно из неповторяющихся значений ячеек выбранного столбца. Дополнительно к значениям в каждом наборе будут присутствовать строки с именами (Все), (Первые 10...) и (Условие).

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

Для того чтобы после выполненной «автофильтрации» вернуться к отображению на экране всех записей списка., можно использовать следующие приемы:

1. Последовательно нажимать кнопки с синими стрелками (т.е. кнопки полей, по которым была выполнена фильтрация) и в открывающихся при этом наборах строк выбирать строку (Все)

2. Выполнить команду Отобразить все меню Данные.

3. Отказаться от применения Автофильтра. Для этого следует еще раз выполнить команду Автофильтр меню Данные. Все кнопки автофильтра, которые были в первой строке списка, исчезнут, а на экране высветится таблица в ее первоначальном виде.

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

Диапазон критериев оформляется следующим образом: в первой строке записываются (или копируются) имена полей списка, для которых задаются условия, а во второй и последующих строках вводятся непосредственно сами условия отбора.

Пример 4: Необходимо получить полную информацию об АО " Весна".

В Меню Данные выбрать Фильтр. Автофильтр и щелкнуть по кнопке-стрелке Клиент

Пример 5. Получить информацию о Клиентах, сделавших покупку 22.01

Пример 6. Получить информацию Клиентах, купивших более чем 4-х единиц товара

Указание. Выбрать в поле Количестве товара Условие, на экране появится окно Пользовательского фильтра

.

Пример 7. Получить полную информацию об АО "Весна" и АО "Шлем".

Вновь вызвать Пользовательский фильтр

Пример 8. Получить сведения о клиентах, сделавших покупку с 23.01 по 27.01.

Вычисление промежуточных итогов.

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

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

Составим итоговую таблицу по Клиенту

1. В меню Данные выбрать команду Сортировка, в поле Сортировать по выбрать значение «.Клиент»,

2. Вменю Данные выбрать команду Итоги. На экране появится окно Промежуточные итоги.

3. В поле При каждом изменении в выбрать Клиент.

4

. В полеОперация выбрать Сумма.

5. В списке Добавить итоги выбрать Сумма и Количество

6. ОК

В результате этих действий появится итоговая таблица:

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

Строки с вычисленными промежуточными итогами можно разместить не под, а над каждой группой записей. В этом случае необходимо сбросить флажок Итоги под данными в окне Промежуточные итоги.

Пример 9. Получить информацию общих итогов по каждому клиенту и построить диаграмму по Клиенту и по Сумме