Для управления большими массивами данных используются специальные программы, предназначенные для работы с базами данных. В Excel также имеются средства для обработки данных, организованных по аналогичному принципу, – это, так называемые, функции списка. В виде списка можно представить таблицу, строки которой содержат однородную информацию. Список характеризует не содержимое таблицы, а способ ее организации. Отдельные записи (комплекты данных списка) должны быть однородны по строкам и/или столбцам. В таблице не должно быть объединенных ячеек! Однако только однородность данных списка по строкам делает возможным применение функции фильтров (если данные однородны по столбцам, таблицу следует транспонировать). Примером базы данных является список адресов, в котором указаны названия и адреса различных фирм или фамилии людей.

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

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

При автоматическом выделении области списка Excel в поисках меток (названий) столбцов сравнивает содержимое первой и второй строк области списка. Если данные в этих строках различаются по типу, Excel принимает первую (верхнюю) строку в качестве строки названий. Данные этой строки будут исключены из обрабатываемой области списка.

Если Excel не обнаружит различий между типами данных в первой и второй строках, то на экране появится сообщение о том, что программа не смогла обнаружить названий для образования меток столбцов. В этом случае программа выдаст запрос, следует ли использовать в качестве меток столбцов данные первой строки выделенной области. Пользователь может согласиться нажатием [ОК] или отменить выполнение операции.

Основными возможностями при работе с базами данных являются:

· организация ввода данных;

· просмотр данных;

· поиск данных по заданному критерию;

· сортировка данных;

· фильтрация данных;

· подведение итогов.

Для работы с таблицей как с базой данных в Excel используется на ленте вкладка Данные. Эта команда корректно работает только с таблицами, в которых названия колонок занимают одну строку. Так, например, для работы с командой Данные таблица Учет движения материалов на складах должна быть преобразована к виду, представленному на рис. 16.

Рисунок 16 – Таблица 1 в виде списка (базы данных)

Приведем таблицу Учет движения материалов на складах к виду, представленному на рисунке 16.

Создайте копию листа «Товары», чтобы легко было вернуться к первоначальному порядку данных. Для этого активизируйте его и щелкните по вкладке листа «Товары» [КЛ_П], в раскрывшемся окне выберите [Переместить/скопировать]. В окне [Переместить или скопировать] установите флажок [Создавать копию] и нажмите [ОК]. Переименуйте полученный лист «Товары(2)» на «Сортировка».

Для преобразования таблицы сделайте следующее:

Выделите строку 2 щелчком мыши на номере строки и удалите ее щелкну [КЛ_П] мыши и выбрав в раскрывшемся диалоговом окне [Удалить]. Аналогично удалите итоговую строку.

Дополните шапку таблицы недостающими заголовками.

Работа с формой

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

Для того чтобы открыть окно формы необходимо добавить кнопку [Форма]  на панель быстрого доступа . Для этого:

1. Щелкните стрелку на панели быстрого доступа и выберите элемент [Другие команды].

2. В поле [Выбрать команды] из щелкните элемент [Все команды].

3. В списке выберите кнопку [Форма]  и щелкните элемент [Добавить].

Рисунок 17 – Окно формы

Работа с формой предполагает:

1. Перемещение по полям записи мышью или клавишей [Tab];

2. Перемещение по записям с помощью полосы прокрутки и кнопок [Назад] и [Далее];

3. Ввод или просмотр записей. Достоинством ввода информации через форму является автоматическое копирование формул и автоматическая поддержка форматов данных.

4. Удаление и добавление записей с помощью кнопок [Добавить] и [Удалить]. При добавлении новой записи, она всегда добавляется в конец таблицы.

5. Редактирование значений полей в записях.

6. Поиск данных по критерию с помощью кнопки [Критерии], после нажатия которой в появившемся окне в соответствующих полях задаются критерии. Задание критериев позволяет просматривать через окно [Формы] только те записи, которые удовлетворяют некоторым условиям поиска (критериям).

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

* – для обозначения произвольного количества символов;

? – для обозначения одного символа.

Например, при необходимости выбора всех записей с фамилиями студентов, начинающихся с буквы «К», в качестве критерия поиска следует ввести К*. А при задании критерия К?рсанов, будут выбираться фамилии Кирсанов, Керсанов, Корсанов и т.п.

В критериях при поиске числовых значений можно использовать операторы сравнения: =, <, >, <>, <=, >=.

Чтобы задать несколько критериев поиска, следует указать их в различных полях, тогда они объединяются логическим И. Задание нескольких критериев позволяет сузить область поиска.

Сортировка данных

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

Рисунок 18 – Окно сортировки

Если сортировка ведется по нескольким ключам, то строки с одинаковыми значениями в столбце, указанном в поле Сортировать по, сортируются в порядке, определяемом столбцом, указанным в поле Затем по. Строки с одинаковыми значениями в первых двух столбцах сортируются по столбцу, указанному в поле [В последнюю очередь, по].

Рассмотрим порядок сортировки таблицы «Учет движения материалов на складах» по двум ключам: 1 – номер склада, 2 – код материала.

1. Активизируйте лист «Сортировка».

2. Установите курсор в любую ячейку диапазона A2:F7, который необходимо отсортировать, т.е. диапазона таблицы вместе с шапкой.

3. Выберите команду [Сортировка], которая находится на ленте во вкладке [Данные], в группе [Сортировка и фильтр]. В окне [Сортировка] (Рисунок 18) в раскрывающемся списке [Сортировать по] выберите поле, по которому следует выполнить сортировку в первую очередь – это «№ склада». Далее справа выберите в поле [Сортировка] – [Значение] и в поле [Порядок] – [По возрастанию].

4. В окне [Сортировка] активируйте команду [Добавить уровень]. В поле [Затем по] выберите второй ключ сортировки – поле [Код материала], сортировка – [значение], порядок сортировки – [по возрастанию]. Нажмите кнопку [ОК]. Вид таблицы после выполнения сортировки представлен на рисунке 19. Обратите внимание, что записи, имеющие одинаковые значения номера склада, выстроились в порядке возрастания кода материала.

Рисунок 19 – Таблица 1 после сортировки

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

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

В Excel существует возможность фильтрации данных с помощью Фильтра и Расширенного фильтра.

Использование фильтра

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

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

Фильтр предоставляет несколько видов фильтрации данных:

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

2. Выбор записей по условию производится командой Условие, в результате чего открывается диалоговое окно Пользовательский автофильтр, где задают критерии с участием одного или двух условий с использованием шаблонов(”?” и “*”) , операций сравнения и логических операций «И», «ИЛИ».

3. Выбор первых наибольших или наименьших n значений позволяет выполнить команда [Первые 10].

4. Команда [Все] восстанавливает на экране все скрытые фильтром строки таблицы.

Рассмотрим использование фильтра на примере таблицы «Учет движения материалов на складах». Пусть из таблицы необходимо выбрать информацию о материалах со склада №2, у которых остаток на конец месяца больше 50.

Создайте копию листа «Сортировка» и назовите его «Фильтр».

Установите курсор в любую ячейку диапазона A2:F7.

Выберите команду команды [Фильтр], которая находится на ленте, на вкладке [Данные] в группе [Сортировка и фильтр].

Ячейки с названиями полей превращаются в раскрывающиеся списки.

Раскройте список в столбце «№ склада» для включения в критерий.

Выберите строку, где номер склада равен «2».

Раскройте список столбца «Остаток на конец месяца», в раскрывшемся диалоговом окне выберите команду [Числовые фильтры] – [Больше], в появившемся диалоговом окне в правом поле введите значение «50» и нажмите [ОК]. На экране останется одна строка, удовлетворяющая условиям отбора. Остальные строки таблицы окажутся скрытыми.

Рисунок 20 – Окно фильтрации по условию пользователя

Для отмены всех условий отбора надо снять выделение с команды Фильтр в группе Сортировка и фильтр.