3.3. Фильтрация и помощью вычисляемого критерия

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

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

Критерий может быть составлен с помощью логических функций И(), ИЛИ(), НЕ(). Аргументами этих функций являются логические выражения.

Задайте вычисляемый критерий, записав в ячейку K38 формулу =ИЛИ(J3>25000;B3=”Мясной”) (см. рис.16), и выполните фильтрацию.

Рис.16. Вычисляемый расширенный фильтр

3.4. Поиск по критерию с помощью форм данных

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

1. Выделите исходную таблицу с нижней строкой заголовка.

2. Выберите команду Данные, Форма и в появившейся форме нажмите кнопку Критерии.

3. Введите критерий в нужном поле. Перейдите к следующему полю, если оно участвует в определении критерия.

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

На рис. 17 приведен пример критерия, который определяет записи, одновременно удовлетворяющие трем условиям: в поле Отдел должно быть название отдела, начинающееся с буквы Б, значение поля Цена при поступлении должно быть больше 30, и значение поля Количество отпущенного товара должно быть меньше 30. Выполните следующее задание: введите данный критерий и найдите все записи, удовлетворяющие ему с помощью формы данных. При выполнении упражнения помните, что в форме данных условия в критерии соединяются логической операцией И, нельзя использовать ИЛИ, а также составлять критерии, требующие вычислений.

Рис.17. Форма для поиска

3.5. Скрытие ненужных столбцов

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

Вместо команды контекстного меню можно воспользоваться командой горизонтального меню Формат, Столбец, Скрыть.

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

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

Диаграммы можно использовать для наглядного представления результатов расчетов и их анализа.

Постановка задачи.

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

Рис.18. Платежная ведомость

Этапы решения задачи:

1. На новом листе Платежная ведомость постройте таблицу, приведенную на рис.18. Самостоятельно определите, какие ячейки должны содержать исходные данные, а какие – формулы для расчетов. Обратите внимание на формулу в строке формул. Введите исходные данные и формулы.

2. Для построения диаграммы, показывающей начисления сотрудников, требуется выделить два столбца таблицы: Фамилия И. О. и Итого к получению. Эти столбцы не расположены рядом (не смежные), и традиционным способом их нельзя выделить. Однако, если удерживать нажатой клавишу Ctrl, то можно одновременно выделять ячейки в разных местах таблицы. Выделите заполненные данными ячейками таблицы, относящиеся к столбцам Фамилия И. О. и Итого к получению вместе с заголовками столбцов.

3. Запустите Мастер диаграмм одним из способов: либо выбрав кнопку панели инструментов, либо команду меню Вставка – Диаграмма. Появится первое диалоговое окно Мастера диаграмм. Передвигаясь по шагам с Мастером диаграмм, выберите тип диаграммы, для этого щелкните по нужному типу диаграммы: Объёмная круговая, а затем щелкните кнопку Далее.

4. Появится второе диалоговое окно мастера диаграмм. В этом окне проверьте диапазон данных, для которых создается диаграмма и нажмите кнопку Далее.

5. В третьем диалоговом окне в поле Название диаграммы введите текст Выплата по кассе и снова нажмите кнопку Далее.

6. Для создания диаграммы на отдельном листе выберите в четвертом диалоговом окне Мастера диаграмм переключатель Отдельном и нажмите кнопку Готово. В результате вы должны получить вот такую диаграмму (рис. 19).

7. Для того чтобы проверить, какая связь существует между таблицей начисления и диаграммой, перейдите на лист Платежная ведомость, в середину таблицы вставьте новую строку. Распространите на новую строку формулы, заполните данные на нового сотрудника. Теперь перейдите на лист Диаграмма и проверьте, как новые данные отразились на диаграмме – новый сотрудник сразу же внесён в диаграмму.

Рис.19. Диаграмма

Выводы:

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

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

Вопросы для самоконтроля:

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

2. Что такое Маркер автозаполнения и как им воспользоваться?

3. Абсолютная и относительная адресация: особенности и условия применения.

4. Какие типы диаграмм предоставляет программа пользователю?

5. Что означает запись в строке формул на рис.18?