Овчаренко_6
.pdfВ поле Исходный диапазон (рис. 5.9) автоматически отобразится диапазон ячеек исходного списка. В поле Диапазон условий надо ввести ссылку на ячейки области критериев. Это выделение можно сделать с помощью мыши (рис. 5.8). Щелчок на кнопке ОК скроет в исходном списке строки, не удовлетворяющие условиям критерия.
Результат фильтрации списка можно скопировать в другую область рабочего листа, на котором находится исходный список, или на отдельный лист текущей книги (этот вариант будет рассмотрен далее). Для этого в окне Расширенный фильтр следует включить переключатель Скопировать результат в другое место, а в поле Поместить результат в диапазон ввести ссылку на крайнюю верхнюю ячейку нового места расположения результата. С копией отфильтрованной таблицы можно выполнять новые действия, не испортив исходный список. Результат выполнения условий фильтра (рис. 5.9) для прайс-листа (рис. 5.8) приведен на рис. 5.10.
Если результат фильтрации списка необходимо разместить в другом месте, например на другом рабочем листе открытой книги или даже в другой по отношению к исходному списку книге, i о необходимо выполнить следующие правила:
Тип |
Cache |
ОЗУ |
HDD |
Видео |
Примечание |
Цена |
|
(USD) |
|||||||
Pentium 166 ММХ |
512 |
16 |
2100 |
2 Mb, 15" |
Intel 430TX, |
674 |
|
Quantum |
|||||||
|
|
|
|
|
|
||
Pentium 200 ММХ |
512 |
16 |
2100 |
2 Mb, 15" |
Intel 430TX, |
730 |
|
Quantum |
|||||||
|
|
|
|
|
|
||
Pentium 233 ММХ |
512 |
16 |
2100 |
2 Mb, 15" |
Intel 430TX, |
760 |
|
Quantum |
|||||||
|
|
|
|
|
|
Рис. 5.10. Результат фильтрации прайс-листа компьютерных фирм.
•Строка заголовков для результата фильтрации должна быть скопирована на тот же рабочий лист, что и диапазон условий.
•Начинать фильтрацию списка надо с рабочего листа, на котором расположен диапазон условий; это значит, что перед вызовом команды Расширенный фильтр надо активизировать любую ячейку на том рабочем листе EXCEL, где расположен диапазон условий. На рис. 5.11 приведен пример заполнения окна команды Расширенный фильтр для прайс-листа компьютерных фирм, когда результат фильтрации выносится на отдельный лист EXCEL, на котором расположен диапазон условий. Условия и результаты фильтрации для этого случая приведены на рис. 5.12.
Рис. 5.11. Заполнение окна команды Расширенный фильтр для случая, когда результат фильтрации должен быть расположен на отдельном от исходного списка листе EXCEL.
Рис. 5.12. Условия и результаты фильтрации прайс-листа компьютерных фирм, расположенные на отдельном рабочем листе
EXCEL.
5.1.2.3. Применение вычисляемых критериев в задачах бизнес-анализа
В диапазон условий команды Расширенный фильтр можно ввести вычисляемые критерии. При этом следует придерживаться следующих правил:
• заголовок над вычисляемым критерием не должен совпадать ни с одним заголовком списка, он может быть даже пустой ячейкой;
• ссылки на ячейки списка в левой части формулы вычисляемого критерия должны быть относительными, а ссылки на ячейки списка в правой части формулы критерия и ссылки на ячейки вне списка должны быть
абсолютными.
Поясним это на примере. Выберем в прайс-листе (рис. 5.8) все компьютеры, цена которых выше средней для данного списка. Зададим условия фильтрации двумя способами.
Сначала вычислим среднее значение цены в какой-либо ячейке вне списка, например в A3 (рис. 5.8). Введем в ячейку A3 формулу вычисления среднего значения цены по столбцу G:
=CP3HA4(G6:G15), которая для данного примера дает значение средней цены 649,9. Если столбцу цен (G6:G15) присвоить имя блока Цена, то формула вычисляемого критерия получит следующий вид: =СРЗНАЧ(Цена). Использование в формулах имен блоков вместо координат ячеек облегчает их применение. Теперь зададим вычисляемый критерий. Для этого в ячейку А1 введем заголовок Цена выше средней, а в ячейку А2 введем следующую формулу: =G6>$A$3, где G6 — это первая ячейка столбца G, содержащего цены компьютеров. Формула позволяет отобрать только те строки списка, в которых значение цены исходного списка больше значения в ячейке A3, т.е. больше средней цены в прайс-листе данного примера. В диалоговом окне Расширенный фильтр (рис. 5.9) в поле Диапазон условий надо ввести ссылку на ячейки $А$1:$А$2, содержащие критерий отбора записей списка. После выполнения команды Расширенный фильтр список будет содержать строки с ценой выше средней, как показано на рис. 5.13.
Тип |
Cache |
ОЗУ |
HDD |
Видео |
Примечание |
Цена |
|
|
|
|
|
|
(USD) |
Pentium |
512 |
16 |
2100 |
2 Mb, 15" |
Intel 430TX, |
730 |
200 ММХ |
|
|
|
|
Quantum |
|
Pentium |
512 |
16 |
2100 |
2 Mb, 15" |
Intel 430TX, |
760 |
233 ММХ |
|
|
|
|
Quantum |
|
Pentium |
512 |
32 |
2100 |
4 Mb, 15" |
Intel 430LX, |
980 |
II 233 |
|
|
|
|
VAAGP |
|
|
|
|
|
|
Quantum |
|
Pentium |
512 |
32 |
2100 |
4Mb, 15" |
Intel 430LX, |
1220 |
II 300 |
|
|
|
|
VA AGP |
|
|
|
|
|
|
Quantum |
|
Рис. 5.13. Список компьютеров, цена которых выше средней цены в прайс-листе.
Другой способ задания вычисляемого критерия не требует предварительного вычисления средней цены в отдельной ячейке прайс-листа. В ячейке А1 (рис. 5.8) удалим заголовок, сделав ее пустой. В ячейку А2 введем формулу: =G6>CP3HA4($G$6:$G$15), которая отфильтрует ячейки столбца G со значениями больше средней цены в прайс-листе. Если столбцу цен (G6:G15) присвоить имя блока Цена, то формула вычисляемого критерия получит следующий вид: =С6>СРЗНАЧ(Цена). Результат выполнения команды Расширенный фильтр для критерия, заданного таким образом, совпадает с предыдущим.
Для копирования результата фильтрации списка в другую область рабочего листа EXCEL в окне Расширенный фильтр (рис. 5.9) надо включить значение переключателя Скопировать результат в другое место,
а в поле Поместить результат в диапазон ввести ссылку на начало нового расположения отфильтрованного списка, например $A$17:$G$17 (рис. 5.8).
5.1.3. Структурирование и группировка данных для формирования итогов
После того как список отсортирован, можно применить команду Итоги из меню Данные для создания промежуточных и общих итогов в списке. По этой команде открывается диалоговое окно Промежуточные итоги (рис. 5.14). В нем задаются поле, при каждом изменении значения
которого будут вычисляться итоговые значения, и операция, которая будет применяться к значениям полей, отмеченных в списке Добавить итоги по. Для прайс-листа компьютерных фирм (рис. 5.4) определим средние цены компьютеров с одинаковыми объемами ОЗУ с помощью команды Итоги. После выделения списка (рис. 5.4) и запуска команды Итоги следует заполнить диалоговое окно Промежуточные итоги так, как это показано на рис. 5.14.
Щелчок по кнопке ОК вставляет итоговые строки и структурирует список.
Рис. 5.14. Диалоговое окно Промежуточные итоги.
Для прайс-листа компьютерных фирм (рис. 5.8) результат вычисления средней цены компьютеров в группах с одинаковым объемом ОЗУ по команде Итоги, настроенной в соответствии с рис. 5.14, приведен на рис. 5.15.
Создание промежуточных итогов основано на предварительной сортировке записей списка, при этом важен порядок сортировки — состав и подчиненность ключей сортировки.
Если сортировка была выполнена по полям: поле1, поле2, полеЗ, — т.е. поле1 являлось самым старшим в сортировке, поле2 определяло порядок сортировки строк списка при одинаковых значениях поля1, а полеЗ задавало порядок сортировки при одинаковых значениях и поля1, и поля2, — то и подведение итогов имеет свой жесткий порядок: поле1, поле2, полеЗ или —
поле1, поле2, или поле1.
Если таблица уже содержит итоговые строки, в нее можно добавить новые итоговые значения, рассчитанные с помощью другой функции. Для этого в окне Промежуточные итоги следует снять флажок Заменить текущие итоги и задать нужное поле и функцию (операцию). Два других флажка (рис. 5.14) позволяют размещать итоги под или над строками данных и выводить каждую группу значений данных на отдельном листе.
Если промежуточные итоги больше не нужны, то для приведения списка в исходное состояние достаточно щелкнуть на кнопке Отмена (рис. 5.14). Однако отмена сработает лишь в том случае, если после
формирования итогов не было других изменений списка, в противном случае следует щелкнуть по кнопке Убрать все, которая возвращает список в исходное состояние.
|
|
Фрагмент базы данных прайс-листов компьютерных фирм |
|||||
|
|
|
|
|
|
|
|
Тип |
|
Cache |
ОЗУ |
HDD |
Видео |
Примечание |
Цена |
|
|
|
|
|
|
|
(USD) |
Pentium 133 |
460 |
8 |
1080 |
1 Mb, 14" |
Intel Triton, Quantum |
460 |
|
|
|
|
|
|
|
8 среднее |
460 |
Pentium 133 |
256 |
16 |
1080 |
1 Mb, 14" |
Intel Triton, Quantum |
480 |
|
Pentium 166 |
256 |
16 |
1080 |
1 Mb, 14" |
Intel Triton, Quantum |
520 |
|
Pentium |
166 |
256 |
16 |
1080 |
1 Mb, 14" |
Intel Triton, Quantum |
545 |
ММХ |
|
|
|
|
|
|
|
Pentium |
166 |
512 |
16 |
2100 |
2 Mb, 15" |
Intel 430TX, Quantum |
674 |
ММХ |
|
|
|
|
|
|
|
Pentium |
200 |
256 |
16 |
1080 |
1 Mb, 14" |
Intel Triton, Quantum |
580 |
ММХ |
|
|
|
|
|
|
|
Pentium |
200 |
512 |
16 |
2100 |
2 Mb, 15" |
Intel 430TX, Quantum |
730 |
ММХ |
|
|
|
|
|
|
|
Pentium |
233 |
. 512 |
16 |
2100 |
1Mb, 15" |
Intel 430TX, Quantum |
760 |
ММХ |
|
|
|
|
|
|
|
|
|
|
|
|
|
16 среднее |
612,71 |
Pentium |
11 |
512 |
32 |
2100 |
4 Mb, 15" |
Intel 430LX, VAAGP |
980 |
233 |
|
|
|
|
|
Quantum |
|
Pentium |
II |
512 |
32 |
2100 |
4 Mb, 15" |
Intel 430LX, VAAGP |
1220 |
300 |
|
|
|
|
|
Quantum |
|
|
|
|
|
|
|
32 среднее |
1100 |
|
|
|
|
|
|
Общее среднее |
649,9 |
Рис. 5.15. Вычисление средней цены компьютеров с одинаковым объемом ОЗУ по команде Итоги.
Таким образом, команда Итоги в меню Данные позволяет выполнять следующие действия:
•По отдельному полю списка, используемому в качестве поля группировки, можно осуществлять формирование итогов различных видов операций (функций).
•Для одинаковых значений поля группировки можно формировать итоги по одному или нескольким полям списка, при этом вид операции
(функции) определяет, какие поля могут использоваться для подведения итогов. Так, для операций Сумма, Среднее, Максимальное, Минимальное
и т.п. могут выбираться поля только числового типа. Для операции
Количество значений — поля любого типа (числовые, текстовые, даты).
5.1.4. Формирование итогов в списках по заданным условиям
Команда Итоги производит автоматический расчет итогов по всем строкам списка для одинаковых значений поля группировки. Чтобы получить итоги только для тех строк или ячеек таблицы, которые удовлетворяют определенным условиям, следует использовать специальные функции EXCEL, а также функции баз данных. Первую группу функций применяют при формировании итогов по одному критерию, заданному в виде константы, а вторую — при расчете итогов по комплексному критерию фильтрации строк списка.
5.1.4.1. Специальные функции EXCEL для формирования итогов, удовлетворяющих одному условию
Решение подобных задач осуществляется с помощью следующих функций EXCEL:
=СУММЕСЛИ ( ) и =СЧЕТЕСЛИ ( ). Функция СУММЕСЛИ ( )
имеет следующий формат:
=СУММЕСЛИ(диапазон;условие;диапазон_суммирования),
где:
диапазон — фрагмент списка, который оценивается на критерии; условие — задается в виде числового выражения с использованием
следующих логических операторов: >, <, =, >=, <=, о или в виде текстовой константы;
диапазон_суммирования — задает интервал ячеек списка для формирования итогов.
Функция СЧЕТЕСЛИ() имеет следующий формат:
=СЧЕТЕСЛИ(диапазон;условие),
где аргументы аналогичны функции СУММЕСЛИ( ).
Рассмотрим использование этих функций на примере прайс-листа компьютерных фирм (рис. 5.4). Пусть необходимо рассчитать количество компьютеров в прайс-листе, цена которых не превышает $700. Для этого в ячейку А1 (рис. 5.8) введем следующую формулу:
=СЧЕТЕСЛИ(С6:С15,"<700") или =СЧЕТЕСЛИ(Цена,"<700"), при
выполнении которой получим число б, что соответствует значениям прайслиста (рис. 5.8).
5.1.4.2. Функции баз данных для формирования итогов по комплексным критериям
Эта группа функций позволяет выполнять расчеты итогов по комплексным критериям фильтрации строк списка. В этом случае используется категория функций EXCEL, которая называется функции баз данных. К ним относятся следующие функции:
БДСУММ, БДДИСЩ ), БДДИСПЩ ), БСЧЕТ( ), БСЧЕТА( ), БИЗВЛЕЧЬ( ), БДПРОИЗВЕД( ), ДМАКС( ), ДМИН(), ДСРЗНАЧ(), ДСТАНДОТКЛ(), ДСТАНДОТКЛЩ ).
Каждая из функций баз данных, кроме БДИЗВЛЕЧЬ( ), является аналогом обычной статистической функции. Например, функция БДСУММ() — это аналог функции СУММ(), а функция ДСТАНДОТКЛ( )
— аналог СТАНДОТКЛОЩ ). Различие между функциями баз данных и их аналогами состоит в том, что первые оперируют только теми данными списка, которые удовлетворяют заданным условиям. Формат функций баз данных следующий:
Функция БД(Фрагмент БД,Поле,Критерий),
где Фрагмент БД — это адрес области списка или весь список, для которого выполняется расчет;
Поле — имя столбца или адрес столбца, по которому ведется расчет;
Критерий — адрес области критериев, где хранятся условия фильтрации списка. Эта область может быть частью списка, или она должна быть предварительно построена, подобно Диапазону условий в команде
Расширенный фильтр.
Например, чтобы рассчитать среднюю цену компьютеров с размером Cache-памяти в 512 Kb по прайс-листу, приведенному на рис. 5.8, можно воспользоваться функцией ДСРЗНАЧ( ). Для использования этой функции надо предварительно построить область критериев с заголовком Cache и
значением 512. Если область критериев размещается по адресу В1:В2 и ей присвоено имя блока Критерий, а фрагмент исходного списка занимает ячейки блока B5:G15 (рис. 5.8), которому присвоено имя Фрагмент, то аргументы функции ДСРЗНАЧ( ) должны быть заданы следующим образом: ДСРЗНАЧ(Фрагмент;С5;Критерий). При задании аргументов функции через координаты ячеек обращение к ней получает менее удобный вид:
ДСРЗНАЧ(В5:С15;С5;В1:В2). В результате расчета в месте расположения функции получим значение 872,8.
Для расчета максимальной (минимальной) цены компьютеров типа Pentium 200 ММХ по прайс-листу на рис. 5.8 можно использовать функцию ДМАКС( ), предварительно разместив в ячейке С1 заголовок области критериев Тип, а в ячейке С2 — значение Pentium 200 ММХ. Если блоку ячеек С1:С2 присвоить имя Критерий2, а блоку ячеек исходного списка A5:G15 присвоить имя БД, то обращение к функции ДМАКС() получит следующий вид: ДМАКС(БД;С5;Критерий2). Ответ — 730.
Необходимо отметить, что область критериев может содержать несколько условий, связанных логической функцией И, если они расположены в одной строке. Если условия расположены в разных строках области критериев, то они связаны логической функцией ИЛИ аналогично команде Расширенный фильтр.
Функция БИЗВЛЕЧЬ() возвращает значение ячейки в столбце, которое удовлетворяет нескольким условиям, заданным в области критериев. Если такой ячейки не обнаружится, то функция возвращает ошибочное значение #ЗНАЧ!. Если заданным условиям удовлетворяет несколько ячеек, то функция возвращает ошибочное значение #ЧИСЛО!.
Например, необходимо определить цену компьютера (рис. 5.8) со следующими характеристиками, заданными в ячейках блока С1:Е2, которому присвоено имя Критерий3:
Тип |
HDD |
Cache |
Pentium 166 |
>1000 |
>256 |
Функция БИЗВЛЕЧЬ(БД;С5;КритерийЗ) дает следующий, результат — 674.
Практика решения бизнес-задач показывает, что применение имен блоков ячеек в качестве аргументов функций EXCEL существенно упрощает технологию использования любых функций EXCEL.
5.1.5. Решение задач бизнес-анализа средствами аппарата сводных таблиц
5.1.5.1. Технология создания сводной таблицы
Сводная таблица — это еще один инструмент обработки больших списков (баз данных). Поскольку в этом случае сразу подводятся итоги, выполняется сортировка и фильтрация списков, то сводная таблица является
более мощным инструментом обработки данных, который в EXCEL
называется мастер сводных таблиц.
Перед построением сводной таблицы на основе списка следует убрать из него промежуточные итоги и наложенные фильтры. Сводные таблицы сами обеспечивают подведение итогов и фильтрацию данных, но построить сводную таблицу по списку с уже имеющимися промежуточными итогами
невозможно.
Рассмотрим построение сводной таблицы на примере формирования
Ведомости зарплаты за полугодие на основе Ведомостей зарплаты сотрудников за 1-й и 2-й кварталы. Прежде всего необходимо разместить исходные ведомости на одном рабочем листе EXCEL, как это сделано на рис. 5.16. Следует обратить внимание, что при копировании Ведомости зарплаты сотрудников за 1-й квартал (аналогично за 2-й квартал) на общий лист
EXCEL необходимо воспользоваться режимом Специальная вставка в меню Правка и вкладкой Связать по ссылке, чтобы сохранить связь с соответствующими формулами расчета на других рабочих листах EXCEL.
Ф.И.0. |
Всего |
Всего |
Сумма к |
Отдел |
Дата |
|
начислено |
удержано |
выдаче |
|
|
Иванов И.А. |
4500 |
487,80 |
4 012,20 |
1 |
1 KB 1998 |
Малаев В.П. |
3900 |
441,12 |
3 458,88 |
2 |
1 KB 1998 |
Фёдоров Е. |
4400 |
505,52 |
3 894,48 |
2 |
1 KB 1998 |
Климов Ф.Ф. |
4000 |
484,60 |
3 515,40 |
1 |
1 KB 1998 |
Осипов Ю.Я. |
4650 |
537,72 |
4 112,28 |
1 |
1 KB 1998 |
Иванов И.А. |
7700 |
899,96 |
6800,04 |
1 |
2 KB 1998 |
Малаев В.П. |
6 100 |
724,48 |
5 375,52 |
2 |
2 KB 1998 |
Фёдоров Е. |
5800 |
685,84 |
5 114,16 |
2 |
2 KB 1998 |
Климов Ф.Ф. |
4800 |
587,64 |
4 212,36 |
1 |
2 KB 1998 |
Осипов Ю.Я. |
5600 |
660,08 |
4 939,92 |
1 |
2 KB 1998 |
Рис. 5.16. Подготовка списка для создания сводной ведомости за полугодие.
Напомним, что для выполнения команды Сводная таблица не требуется предварительная сортировка списка, поскольку команда Сводная таблица включает сортировку, фильтрацию и формирование итогов.
В меню Данные следует выбрать команду Сводная таблица, по которой на экран выводится окно Мастер сводных таблиц для
задания области обрабатываемых данных, в нашем примере — это весь список.