Определение множества условий

Создание диапазона условий

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

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

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

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

Вставьте перед таблицей 4 пустые строки и сформируйте диапазон условий для выборки записей, содержащих данные о продажах менеджера Иванова. Имена полей лучше копировать из таблицы.

 

Менеджер

Иванов

Определение множества условий

Достаточно часто у пользователя возникает необходимость выбора записи, учитывающей сразу несколько условий, которые основаны на соответствии более чем одному полю списка или соответствии множества значений одному и тому же полю. Такие условия используют логические операторы И или ИЛИ.

Чтобы объединить условия с помощью оператора И, в диапазоне условий должно исполь­зоваться множество столбцов.

Сформируйте диапазон условий для выборки записей, содержащих данные о продажах менеджера Иванова клиенту «Дельта».

 

Менеджер	Клиент
Иванов	Дельта

 

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

Сформируйте диапазон условий для выборки записей, содержащих данные о продажах менеджеров Иванова и Петрова.

 

Менеджер

Иванов

Петров

 

 

Оперативная сводка (выборка из базы данных)

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

Предположим, что нам нужна информация об объемах продаж, осуще­ствленных каждым менеджером, чтобы иметь возможность сравнивать результаты их работы. Создадим выборку таких данных из базы на но­вом листе.

 

Перейдите на лист Список1 книги Учет продаж.

Здесь создан список менеджеров.

 

Продажи менеджеров за январь
Менеджер	Менеджер	Менеджер
Иванов	Петров	Сергеев

 

Под каждой фамилией отобразим сумму продаж.

Воспользуемся функцией БДСУММ() из категории Работа с базой данных:

• В поле База_данных следует ввести диапазон ячеек, содер­жащих базу данных, на основании которой будет вычисляться функция. Это диапазон A3:E1000 на листе Отчет менеджеров.

• В поле диалога Поле следует указать заголовок столбца, из которого функ­ция будет извлекать данные для вычисления. Это - столбец Сумма.

• В поле Критерий необходимо указать диапазон ячеек, задаю­щих условия для выборки из базы данных. В качестве критерия может использоваться любой интервал, который содержит по крайней мере одно название столбца и одну ячейку под на­званием с условием.

В нашем примере в качестве критерия мы используем диапазон ячеек A3:А4 на листе Список1. Этот критерий имеет смысл: Менеджер = Иванов.

Функция БДСУММ “просматривает” поле Сум­ма указанной базы данных, выбирает из нее все значения, соответст­вующие критерию Менеджер = Иванов, и суммирует их.

Теперь введем аналогичные формулы в ячейки В4, С4, где будут вычислять­ся суммы продаж менеджеров Петрова и Сергеева. Для этого скопируем формулу из ячейки В4. Это можно сделать с помощью маркера заполнения.

Если не получили правильный ответ, проанализируйте формулы в ячейках В4 и С4. Какие адреса ячеек указаны в формулах? Что происходит с такими адресами при копировании функции вправо?

Итак, для решения проблемы надо задать абсолютные или смешанные адреса. Это можно сделать с помощью функциональной клавиши F4.

В окончательном виде формула должна иметь вид:

=БДСУММ('Отчет менеджеров'!$A$3:E8;'Отчет менеджеров'!$E$3;A3:A4)

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

Перейдите на лист Списки2 и решите аналогичную задачу.

Построение круговой диаграммы

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

Рассмотрим способы построения диа­грамм, которые наглядно представят объемы продаж каждого менедже­ра.Все построения выполняются Мастером диаграмм. Перед началом построения Мастеру нужно указать диапазон ячеек, со­держащих данные, которые должны быть отражены на диаграмме. Если необходимо, чтобы на диаграмме выводились и названия строк или столбцов, то следует выделить также содержащие их ячейки.

· Перейдитена лист Списки2.

· Выделите диапазон ячеек А1:В6. Этот диапазон включает все необ­ходимые для построения диаграммы данные, в том числе и название.

· Нажмите кнопку Мастера диаграмм на панели инструментов Стандартная. На экране появится диалог Мастер диаграмм (шаг 1 из 4): тип диа­граммы, в котором предлагается выбрать тип диаграммы для построения.

Каждый тип диаграммы имеет свою область применения. В зависимо­сти от того, что именно вы хотите продемонстрировать вашей диа­граммой, следует подобрать наиболее подходящий для этого тип.

В поле списка Тип выберите Круговая.

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

· В поле Вид выберите один из предлагаемых для по­строения вариантов диаграммы.

· Нажмите кнопку Просмотр результата, чтобы увидеть, как будут отображаться данные при выборе этого ви­да диаграммы.

· Нажмите кнопку Далее. На экране появится второй диалог Мастер диаграмм (шаг 2 из 4): источник данных диаграммы с открытой вкладкой Диапазон данных.

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

Как правило, на диаграмме отображается последовательность значений какого-либо параметра взависимости от значений аргументов. После­довательность значений параметров в Microsoft Excel называется рядом данных, а последовательность значений аргументов - категорией. Ряды данных могут располагаться в колонках или в строках. В выделенном нами для построения диаграммы диапазоне ячеек имеется один ряд данных, расположенных в столбце Сумма и три категории: Иванов, Петров, Сергеев. Мастер диаграмм правильно определил, что ряды данных расположены в столбцах диапазона, установив пере­ключатель Ряды в: столбцах.

· Щелкните мышью на ярлычке вкладки Ряд, чтобы перейти на эту вкладку.

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

· Нажмите кнопку Далее. На экране появится следующий диа­лог Мастер диаграмм (шаг 3 из 4): Параметры диаграммы с открытой вкладкой Заголовки.

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

· Щелкните мышью на ярлычке вкладки Легенда, чтобы пе­рейти на эту вкладку.

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

· Убедитесь, что установлен флажок Добавить легенду.

· Установите переключатель Внизу, чтобы легенда выводи­лась под диаграммой.

· Щелкните мышью на ярлычке вкладки Подписи данных. На экране отобразятся элементы управления этой вкладки.

Вкладка Подписи данных позволяет изменить подписи:

нет - подписи отсутствуют;

значение - отображаются числовые значения для всех то­чек данных;

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

категория - отображаются подписи категорий для всех то­чек данных;

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

размеры пузырьков - на пузырьковой диаграмме при­водятся размеры пузырьков для всех точек данных.

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

· Установите флажок Ключ легенды. При этом рядом с подписями данных на диаграмме будет размещаться ключ легенды.

· Нажмите кнопку Далее. На экране появится последний диалог Мастер диаграмм (шаг 4 из 4) размещение диаграммы, в котором необходимо указать место размещения созданной диаграммы.

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

· Выберите лист Отчет менеджеров.

· Нажмите кнопку Готово. Окно диалога закроется. На новом листе Отчет менеджеров отобразится построенная диаграмма.

Вопросы:

1. Какие функции используются в Excel для вычисления итогов (сумм)?

2. Чем отличается работа этих функций?

3. Какие Вы знаете адреса ячеек в Excel?

4. Что происходит с адресами ячеек при копировании формулы вправо (вниз)?

5. Какой функциональной клавишей можно воспользоваться, чтобы задать абсолютную или смешанную ссылку?

6. Как задаются в Excel критерии отбора?

 

Задания для самостоятельной работы:

1 С помощью Автофильтра сделайте выборку записей о продажах менеджеров с 12.01.06 по 14.01.06 включительно.

2 С помощью расширенного фильтра сделайте выборки записей:

a) о продажах менеджеров с 12.01.06 по 14.01.06 включительно;

b) о продажах менеджера Петрова с 12.01.06 по 14.01.06 включительно;

c) о продажах менеджеров Иванова и Петрова с 12.01.06 по 14.01.06 включительно.

3 Проанализируйте формулу для подсчета объёмов продаж менеджеров на листе Списки2 и запишите ее так, чтобы она корректно копировалась из ячейки В5 вниз.

4 Создайте оперативный отчет по объёму продаж клиентам.

5 Решите задачу, предложенную на листе Списки2, с помощью других функций Excel (кроме БДСУММ()).