ОБРАБОТКА ДАННЫХ В ЭЛЕКТРОННОЙ ТАБЛИЦЕ
Представление электронной таблицы в виде списка
Список или база данных – это один из способов организации данных на рабочем листе. Он создается как помеченный ряд, состоящий из строк с однотипными данными. Например, перечень сотрудников некоторого отдела, в котором столбцы имеют следующие имена: ФИО, Всего начислено, Всего удержано, Сумма к выдаче.
Данные, организованные в список, называются базой данных. При этом строки таблицы – записи базы данных, а столбцы – поля записей. Чтобы превратить таблицу Excel в список, необходимо присвоить столбцам имена, которые будут использоваться как имена полей записей. При создании базы данных необходимо выполнять следующие правила:
1. На одном рабочем листе не следует размещать более одного списка, т.к. некоторые операции, например фильтрация, работают в определенный момент только с одним списком.
2. Следует отделять список данных от других данных рабочего листа хотя бы одним незаполненным столбцом или одной незаполненной строкой. Это поможет автоматически выделить список при выполнении фильтрации или при сортировке данных.
3. В заголовках столбцов списка данных не следует объединять ячейки.
4. Имена столбцов должны располагаться в первой строке списка, т.к. Excel всегда первую строку рассматривает как заголовки столбцов.
5. Для того чтобы отделить заголовки столбцов от основных данных можно использовать шрифт, выравнивание, формат, отличные от тех, которыми отражены основные данные таблицы. Нельзя оставлять пустую строку между заголовками столбцов и основными данными, не рекомендуется для отделения использовать пунктирную линию.
Сортировка данных в таблице
Сортировка данных в Excel имеет положительные и отрицательные стороны. Положительные – универсальность операции и удобный пользовательский интерфейс. Отрицательные: легкость, с которой таблица может быть превращена в набор беспорядочных данных.
Для проведения сортировки необходимо выделить ячейку таблицы и выбрать команду Данные, Сортировка. Excel автоматически выделит весь список. Если в первой строке находятся имена полей, то они не будут включены в сортировку. Надо иметь в виду, что итоговая строка исходного списка также будет включена в сортировку, поэтому иногда бывает целесообразнее вручную выделить диапазон данных для сортировки.
В трех полях ввода окна Сортировка можно задать ключи, по которым данные будут сортироваться. Можно также задать параметры: установить порядок сортировки по первому ключу (обычный или определяемый пользователем), а также направление сортировки (по возрастанию или по убыванию).
Для быстрой сортировки можно воспользоваться кнопками «Сортировка по возрастанию» и «Сортировка по убыванию» стандартной панели инструментов (в этом случае ключом сортировки является столбец с текущей ячейкой).
Если в результате сортировки вы не добились ожидаемого результата, отмените ее. Для этого щелкните на кнопке «Отменить» стандартной панели инструментов.
Фильтрация данных в списке
Фильтр представляет собой конструкцию, предназначенную для отбора тех строк таблицы, которые удовлетворяют заданному условию, и временного скрытия остальных строк. Основой фильтра является список, который содержит условия отбора строк. Excel позволяет быстро и удобно просматривать требуемые данные из списка с помощью Автофильтра. Более сложные запросы к базе данных можно реализовать с помощью команды Расширенный фильтр.
Автофильтр
С помощью функции Автофильтр можно вывести на экран только определенные записи. Для этого необходимо сначала выделить список обязательно с заголовками полей.
Кнопки, раскрывающие список фильтра, создаются в первой строке диапазона. Если в столбце встречаются незаполненные ячейки, список дополняется категориями «пустые» и «непустые». В любом случае верхняя строка диапазона не рассматривается как объект для фильтрации и в список не включается. Даже если часть строк не попала в выделение, принцип фильтрации будет распространяться и на них, включая итоговую строку. После того, как диапазон выделен выбираем команду Данные/ Фильтр / Автофильтр.
После этой команды Excel помещает раскрывающиеся списки непосредственно в имена столбцов списка. Элемент столбца, который выделен в раскрывающемся списке, называют критерием фильтра. Можно продолжить фильтрацию с помощью критерия из другого столбца.
Чтобы удалить критерий фильтрации для отдельного столбца, надо выбрать параметр «Все» в раскрывающемся списке. Чтобы отобразить все скрытые в списке строки, надо выбрать Данные/ Фильтр/Отобразить все.
Пользователь может сформировать условие отбора строк таблицы для каждого столбца. Пользовательские критерии отбора записей могут состоять из одного или двух логических выражений, соединяемых операторами И (ИЛИ). Использование оператора И предполагает, что условие будут выполнено, если одновременно будут выполнены обе его части. Использование оператора ИЛИ допускает выполнение хотя бы одной части условия.
Чтобы задать пользовательский критерий надо выбрать параметр Условие в раскрывающемся списке, а затем, в появившемся окне «Пользовательский автофильтр» задать необходимые критерии: Показывать только те строки, значения которых… – указать нужные критерии.
Для удаления Автофильтра необходимо повторно выбрать команду Данные/Фильтр/Автофильтр.
Первые 10. Этот метод имеет смысл только для полей с числовыми данными, в том числе и с датами. Чтобы воспользоваться этим методом, нужно выбрать в списке вариант «Первые 10…». В появившемся диалоговом окне следует указать, сколько наибольших или наименьших элементов следует отображать. Например, в БД имеется поле «Оклад» для хранения размера оклада сотрудника. Организация имеет возможность оказать материальную помощь 5 сотрудникам с наименьшим окладом. Для решения этой задачи можно воспользоваться методом отбор «Первые 10…» для поля «Оклад»: задать количество отображаемых записей с наименьшим значением.
Сложная фильтрация (расширенный фильтр)
Для фильтрации списка по сложному критерию, а также для получения части исходного списка по нескольким заданным столбцам используется команда Расширенный фильтр меню Данные. Ее отличие от команды Автофильтр состоит в том, что кроме перечисленных возможностей, отфильтрованные записи можно перенести в другое место рабочего листа Excel, не испортив исходный список. В расширенном фильтре критерий фильтрации создается как таблица, которая может храниться в любом месте рабочей книги, или даже в другом файле. К таблице-критерию применимы все операции обработки данных Excel.
Расширенный фильтр позволяет:
1. сразу копировать отфильтрованные записи в другое место рабочего листа.
2. сохраняет критерий отбора для дальнейшего использования (это полезно, когда в список вносятся новые данные, и нужно периодически извлекать из него информацию в соответствии с критерием).
3. показывать в отфильтрованных записях не все столбцы таблицы, а только указанные
4. объединять оператором ИЛИ условия для разных столбцов
5. для одного столбца объединять операторами И, ИЛИ более двух условий.
6. создавать вычисляемые критерии.
Чтобы использовать команду Расширенный фильтр, надо сначала создать таблицу критериев, которую желательно разместить на том же рабочем листе, что и исходный список, но так, чтобы она не была скрыта во время фильтрации. Для формирования таблицы критериев необходимо для обеспечения идентичности текстовых полей скопировать имена полей списка в ту часть рабочего листа, где будет располагаться таблица критериев. При этом число строк в этой таблице определяется только количеством критериев поиска. Однако включение пустых строк в таблицу критериев недопустимо, поскольку в этом случае будут найдены все записи списка.
Кроме таблицы критериев, для команды Расширенный фильтр надо определить вид выходного документа. Это означает, что следует скопировать в свободное место рабочего листа имена тех полей списка, которые определяют вид выходного документа. Количество строк в выходном документе Excel определит сам.
Таким образом, для выполнения команды Расширенный фильтр следует:
· сформировать в свободном месте рабочего листа таблицу критериев
· сформировать шапку выходного документа
· выделить область списка в исходном документе
В области Обработка надо указать, будет ли фильтрация выполняться на месте или выходные данные будут перенесены в другую область рабочего листа. Если выбран режим по умолчанию «фильтровать список на месте», то Excel скроет все строки исходного списка, не удовлетворяющие заданным критериям. Если установлен переключатель «Только уникальные записи», то повторяющиеся строки исходного списка не будут показаны в области выходных данных.
Задание условий с использованием логической операции ИЛИ:
Чтобы задать условия в диапазоне критериев логической операцией ИЛИ, нужно эти условия расположить на разных строках: например:
1) Отобразить записи о менеджерах с фамилией «Кислов» или о менеджерах, продающих «Хлеб»:
| Менеджер | Товар |
| Кислов | |
| Хлеб |
2) Получить информацию о клиентах, чьи фамилии начинаются на букву В и Т:
| Клиент |
| В |
| Т |
Задание условий с использованием логической операции И:
Чтобы задать условия в диапазоне критериев логической операцией И, нужно эти условия расположить на одной строке: например:
1) Найти информацию о менеджерах с фамилией «Петрова», продавших товар более чем на 50 единиц:
| Менеджер | Количество |
| Петрова | >50 |
2) Найти информацию о товарах, цена которых больше 30 и меньше 80 рублей:
| Цена | Цена |
| >30 | <80 |
Задание условий с одновременным использованием логических операций И, ИЛИ:
Например, выбрать из списка записи о клиентах с фамилией на букву В и с видом сделки «поставка», а также клиентов с фамилией на С с видом сделки «продажа»:
| Клиент | Вид сделки |
| В | Поставка |
| С | Продажа |