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

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

Пусть имеется некоторая модельная база данных по сотрудникам мифического магазина, состоящая из 10 полей и 78 строк (1-я строка – имена полей). Поля: "ФИО" – текстовое, "Дата рожд" – дата, "Нач.стажа" – дата, "Пол" – текстовое (1 буква), "С/п" – текстовое (1 буква), "Детей" – число (целое), "Секц" – текстовое, "Образ" – текстовое, "Должность" – текстовое, "Оклад" – число. Оклады указываются в некоторых условных единицах (у.е.).

На рис. 3.1 показано начало списка.

Для выполнения сортировки списка и использования фильтров используются элементы пункта "Данные" главного меню (рис. 3.2)

3.1 Сортировка.

Сначала посмотрим, как в Gnumeric производится сортировка списка. Перед началом операции необходимо выделить весь диапазон ячеек, занимаемый списком, включая строку с именами полей (выделение производится либо "протаскиванием" мыши с нажатой левой кнопкой, либо перемещением указателя активной ячейки с помощью клавиш управления курсором – "стрелок" – при нажатой клавише <SHIFT>). Кнопки сортировки в панели инструментов программы обеспечивают сортировку диапазона по первому столбцу соответственно по возрастанию или по убыванию. Для детального управления условиями сортировки надо использовать вызов диалога "Данные/Сортировка..." из главного меню программы (рис. 3.3).

Выключение режима "Диапазон сортировки имеет заголовок" позволяет вместо номеров столбцов использовать имена полей. Щелчок мышкой по стрелке ($$A \to Z$$) позволяет изменять направление сортировки (вместо сортировки по возрастанию устанавливать порядок сортировки по убыванию). Назначение переключателя "Чувствительно к регистру" очевидно, и он действует для текстовых полей. Кнопки "Вверх" и "Вниз" позволяют изменять порядок критериев сортировки, а кнопки "Удалить" и "Добавить" – соответственно, удалять и добавлять критерии сортировки.

Отсортируем список по следующим критериям – сначала женщины, потом мужчины, и для каждой группы – по убыванию количества детей. В диалоге сортировки уберём лишние поля, а для оставшихся установим нужный порядок сортировки. Тогда условия сортировки будут выглядеть в соответствии с рис. 3.4.

Фрагмент результатов сортировки показан на рис. 3.5.

Далее рассмотрим возможности выбора данных из списка по заданным критериям, то есть применение фильтров.

3.2 Автофильтр

Автофильтр включается выбором команды "Данные/Фильтр/Добавить автофильтр" (рис. 3.6). При этом указатель активной ячейки должен находиться в одной из ячеек диапазона, занимаемого списком, например, в строке с именами полей. После включения Автофильтра в каждой ячейке строки с именами полей появляется значок раскрывающегося списка (рис. 3.7).

При раскрытии списка показываются все варианты значений в выбранном поле, а также варианты (Все), (Верхние 10...) и (Другой...). Список вариантов критериев выбора для поля "Детей" показан на рис. 3.8.

Самый простой вариант использования Автофильтра – выбор данных по точному соответствию значений. При этом критерии могут устанавливаться по нескольким полям одновременно, что соответствует логической операции "И", т.е. все выбранные условия должны одновременно выполняться. Например, выберем мужчин, имеющих среднее специальное (ср/сп) образование. Результат показан на рис. 3.9.

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

Чтобы вернуть список в первоначальный вид, можно выполнить команду "Данные/Фильтр/Удалить автофильтр" из главного меню, а можно для "отфильтрованных" полей установить автофильтр в вариант (Все).

В списке критериев Автофильтра вариант (Другой...) позволяет установить для выбранного поля два условия, связав их логическим выражением "И" или "ИЛИ". Сначала рассмотрим возможности формирования условий поиска для текстовых полей.

Пусть требуется выбрать из общего списка людей, фамилии которых начинаются на "Ми" или "Ни". Тогда диалог настройки автофильтра по полю "ФИО" будет выглядеть следующим образом (рис. 3.10).

В данном случае варианты сравнения выбираются из списка слева. Список вариантов показан на рис. 3.11.

В рассмотренном примере для формирования условий логичным является использование логической функции "ИЛИ", поскольку фамилия не может начинаться одновременно с разных букв. Результаты поиска показаны на рис. 3.12.

Для полей текстового типа можно использовать варианты условий "equals", "does not equal", "begins with", "does not begin with", "ends with", "does not end with", "contains" и "does not contain".

Пусть теперь надо найти людей, фамилии которых состоят из 5-ти или 6-ти букв. Для этого нужно указать, что в поле "ФИО" содержатся пять (или шесть) любых символов, после которых обязательно стоит пробел. Тогда для указания любого одиночного символа используется символ подстановки "?". В этом случае условия поиска по полю "ФИО" будут выглядеть следующим образом (рис. 3.13).

Соответствующий результат показан на рис. 3.14.

Для полей числового типа (в том числе дат) при формировании условий поиска используются варианты "equals", "does not equal", "is less then", "is greater than", "is less than or equal to" и "is greater then or equal to".

Сначала рассмотрим работу с данными числового типа (включая даты) на примере поиска сотрудников, родившихся в 1975 году. Год, как известно, начинается 1 января, а заканчивается 31 декабря. Поэтому для поля "Дата рождения" сформируем условия в соответствии с рис. 3.15.

Результат применения фильтра показан на рис. 3.16.

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

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

Условие для поиска по расчётному полю показано на рис. 3.17, а результат – на рис. 3.18.

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

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

3.3 Расширенный фильтр.

Расширенный фильтр позволяет реализовать подобие запросов QBE (query by example – запрос по образцу), используемых в настоящих базах данных. Для использования расширенного фильтра необходимо для каждого запроса формировать блок критериев. Блок критериев должен состоять минимум из двух ячеек – имени поля и условия поиска по этому полю. Условие должно быть либо числом, либо текстом (аналогично условиям в функциях sumif() и countif(), которые были рассмотрены в предыдущей главе). Блок критериев целесообразно располагать над списком с данными. Обязательно наличие пустой строки перед блоком критериев и после него. Таким образом, в нашем примере перед диапазоном исходных данных нужно вставить несколько строк.

Пусть из списка сотрудников требуется выбрать лиц, начавших трудовую деятельность в 1960, 1983 и в 1990 годах. Здесь потребуется создать расчетное поле "Год", аналогично тому, что делалось при рассмотрении автофильтра. По этому полю нужно удовлетворить одновременно трем условиям, поэтому автофильтр не годится.

Для использования расширенного фильтра в первую очередь необходимо сформировать блок критериев. Он формируется путем копирования строки с именами полей в пустую строку над таблицей данных, а затем под именем поля "Год" в блоке критериев записываются одно под другим три условия (искомые значения годов), как показано на рис. 3.19. Условия, записанные одно под другим, обеспечивают выполнение логической операции "ИЛИ".

После этого выделяется диапазон исходных данных, включая строку с именами полей и вызывается диалог настройки расширенного фильтра "Данные/Фильтр/Расширенный фильтр...". Этот диалог имеет две вкладки. Первая вкладка (Ввод) позволяет определить диапазоны исходных данных и блока критериев (поэтому блок критериев уже должен существовать) (рис. 3.20). В нашем случае данные занимают диапазон $A$9:$K$83, а критерии – диапазон K4:K7 (в блок критериев входит имя поля "Год" и три значения под ним). Вторая вкладка (Вывод) позволяет определить, куда будут записываться результаты работы фильтра (рис. 3.22).

При задании диапазона списка критериев нет возможности выделить эти диапазоны в ЭТ, если диалог настройки расширенного фильтра полностью открыт, как показано на рис. 3.20. Для указания диапазонов путём выделения блоков ЭТ мышью нужно свернуть диалог, нажав на "кнопку" справа от соответствующего поля ввода. Диалог примет вид окна указания диапазона (рис. 3.21), после чего уже можно выделять нужный блок ячеек. По окончании выделения нажатием на ту же "кнопку" следует вернуть диалоговое окно в первоначальный вид.

В данном случае выбран вариант "Фильтровать на месте", однако результаты работы фильтра могут быть скопированы в другой диапазон на том же листе, на другой лист или даже в другой документ. Блок критериев также может быть размещен на другом листе, но это уже дело вкуса и привычки.

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

Из приведенного рисунка видно, что в условиях расширенного фильтра можно использовать как точное соответствие, так и операции сравнения для числовых полей. Расположение условий в одной строке означает одновременное выполнение условий (отношение "И"), а расположение условий друг под другом означает требование выполнения хотя бы одного из условий (отношение "ИЛИ").

В условиях расширенного фильтра можно также использовать результаты работы формул. Например, нужно найти сотрудников с окладом ниже среднего по предприятию. Тогда сначала подсчитываем средний оклад с помощью функции average() по столбцу с окладами (например, в ячейке K6), рядом в какой-то ячейке (например, в K5) записываем знак сравнения "<" (в ячейке, а не в условии, потому что критерий поиска может измениться), а в условии пишем формулу "=concatenate(K5;round(K6;0))". Функция round() используется для округления среднего значения до целого.

Блок критериев, полученный с использованием формулы, показан на рис. 3.25.

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

3.4 Функции базы данных.

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

Некоторые часто используемые функции этой категории приведены в таблице ниже.

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

Рассмотрим пример использования функций базы данных для вычисления количества женщин с различными уровнями образования. Внимательно посмотрев описание функций категории "База данных", можно понять , что для такой задачи потребуется использовать функцию dcounta(), которая подсчитывает количество значений (непустых ячеек) в указанном столбце при указанных условиях. Условия и результаты работы функции показаны на рис. 3.26.

Блок критериев должен состоять как минимум из двух ячеек – имени поля и условия поиска по этому полю. Поскольку все поля текстовые, то условием является полное соответствие текста. В данном случае (для первого результата в ячейке M6) имеем формулу =dcounta($A$7:$J$85;$H$7;L4:M5), где

Результаты подсчета находятся соответственно в ячейках M6, M9, M12 и M15.

Работа со списками не является сильной стороной Gnumeric, однако выполнение часто требуемых операций всё-таки обеспечивается.

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