Овчаренко_6

5. Информационная технология бизнес-анализа в EXCEL

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

EXCEL.

5.1. Методы обработки и анализа экономической информации в

EXCEL

В данном разделе рассматриваются инструменты EXCEL для работы со списками, которые включают такие средства обработки данных, как

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

Списки в EXCEL — это таблицы, строки которых содержат однородную информацию. В терминологии EXCEL список называют также

базой данных (БД), при этом строки таблицы — это записи базы данных, а столбцы — поля записей.

Чтобы превратить таблицу EXCEL в список, или базу данных, необходимо присвоить столбцам однострочные имена, которые будут использоваться как имена полей записей базы данных. Следует иметь в виду, что строка имен полей может состоять из нескольких строк заголовков, размещенных в одной строке таблицы EXCEL, как это показано на рис. 5.1.

Кредитор	Сумма кредита	Годовая ставка	Число дней, на
	(тыс. руб.)	процента (%)	которые выдан
			кредит
Ветров А.Р.	400000	31%	120
Захаров Л.Д.	78000	32%	60
Иванов В.А.	10000	28%	60
Каримов Р.А.	80000	32%	120
Морозов К.Н.	65000	29,50%	90

Петров А.А.	50 000	30%	90
Сидоров П.Р.	350000	28,50%	60
Черкасов К. Г.	99000	30%	120
ИТОГО:	1 132 000

Рис. 5.1. Пример списка, имена столбцов которого занимают несколько строк заголовков.

При создании списка на рабочем листе EXCEL необходимо соблюдать следующие правила:

•На одном рабочем листе не следует помещать более одного списка, поскольку некоторые операции, например фильтрация, работают в определенный момент только с одним списком.

•Следует отделять список от других данных рабочего листа хотя бы

одним свободным столбцом или одной свободной строкой. Это поможет

EXCEL автоматически выделить список при выполнении фильтрации или при сортировке данных.

•Список может занимать весь рабочий лист: 65536 строк, 256 столбцов.

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

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

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

•Каждый столбец списка должен содержать во всех строках

однотипные данные.

•Не следует вводить дополнительные пробелы в начале ячеек данных, поскольку они влияют на сортировку и поиск.

Замечание.

Если имя поля шире его данных, то можно перенести текст имени по строкам. Для этого надо выбрать в меню Формат команду Ячейки, а затем выбрать вкладку Выравнивание и установить флажок Переносить по

словам.

5.1.1. Сортировка списков

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

Команда Сортировка открывает одноименное диалоговое окно (рис. 5.2), в котором можно задать до трех ключей с указанием порядка сортировки. Сортировка выполняется сначала по первому ключу, затем в строках с совпадающим значением первого ключа — по второму ключу, наконец, в строках с одинаковыми значениями первого и второго ключа — по третьему ключу. Например, для примера на рис. 5.1 сортировка списка по возрастанию Числа дней, на которые выдан кредит, а при одинаковом числе дней — по возрастанию Годовой ставки процента, требует задания параметров сортировки, как показано на рис 5.2. Результат выполнения такой сортировки списка приведен на рис. 5.3.

Рис. 5.2. Задание параметров сортировки.

Кредитор	Сумма кредита	Годовая ставка	Число дней, на
	(тыс. руб.)	процента (%)	которые выдан
			кредит
Иванов В.А.	10000	28%	60
Сидоров П.Р.	350000	28,50%	60
Захаров Л.Д.	78000	32%	60
Морозов К Н.	65000	29,50%	90
Петров А.А.	50000	30%	90
Черкасов К Г.	99000	30%	120
Ветров А Р.	400000	31%	120
Каримов Р.А	80000	32%	120
ИТОГО:	1 132 000

Рис. 5.3. Результаты сортировки списка по двум ключам.

Замечание.

Скрытые строки или столбцы не перемещаются во время сортировки, хотя они и являются частью списка.

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

•Числа: от наименьшего отрицательного до наибольшею положительного.

•Даты и время: от наиболее ранней до самой поздней.

•Текст: сначала числа, введённые в виде текста, затем обычный

текст.

•Логические значения: сначала ЛОЖЬ, затем ИСТИНА.

Сортировка по убыванию выполняется в порядке, o6paтном порядку по возрастанию, кроме пустых ячеек, которые всегда сортируются

последними.

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

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

Чтобы применить пользовательский порядок сортировки, надо выбрать кнопку Параметры... в диалоговом окне Сортировка (рис. 5.2).

Этот режим позволяет установить порядок сортировки по первому ключу —

обычный или определяемый пользователем, задать учёт кодировки

строчных и прописных букв (учёт регистра символов), а также направление сортировки — по возрастанию или по убыванию.

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

Задать пользовательский порядок сортировки можно также по вкладке Списки диалогового окна Параметры, которое открывается по команде

Параметры в меню Сервис.

Замечания:

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

•Для быстрой сортировки на панели инструментов Стандартная находятся две кнопки: (сортировать по возрастанию) и (сортировать по убыванию).

Ключом сортировки в этом случае является столбец с текущей ячейкой.

5.1.2. Фильтрация списков

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

5.1.2.1. Выборка данных средствами Автофильтра

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

По команде Данные, Фильтр, Автофильтр в строке заголовков таблицы появляются кнопки с раскрывающимися списками значений. Рассмотрим применение автофильтра на примере прайс-листа компьютерных фирм, который приведен на рис. 5.4

Автофильтр предполагает использование критериев поиска типа сравнение. Существует два типа сравнений:

•по точному или шаблонному значению;

•по условию отбора.

	Тип	Cache	OЗУ	HDD	Видео	Примечание	Цена
							(USD)
	Pentium 133	256	8	1080	1 Mb, 14"	Intel Triton,	460
						Quantum
	Pentium 133	256	16	1080	1 Mb, 14"	Intel Triton,	480
						Quantum
	Pentium 166	256	16	1080	1 Mb, 14"	Intel Triton,	520
						Quantum
	Pentium 166 ММХ	256	16	1080	1 Mb, 14"	Intel Triton,	545
						Quantum
	Pentium 166 ММХ	512	16	2100	2 Mb, 15"	Intel 430TX,	674
						Quantum
	Pentium 200 ММХ	256	16	1080	1 Mb, 14"	Intel Triton,	580
						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
	Pentium II 233	512	32	2100	4Mb, 15"	Intel 430LX,	980
						VA AGP
						Quantum
	Pentium II 300	512	32	2100	4 Mb, 15"	Intel 430LX,	1220
						VA AGP
						Quantum

Рис. 5.4. Фрагмент прайс-листа компьютерных фирм.

Точное значение для сравнения выбирается из раскрывающегося списка для указанного поля. EXCEL в Microsoft Office 97 формирует при выполнении команды Данные, Фильтр, Автофильтр списки значений полей, в которых может находиться до 999 элементов (в отличие от EXCEL 7.0, когда список содержал до 250 элементов).

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

Иили ИЛИ. Каждое предложение имеет стандартную структуру:

<реляционный оператор > < значение >.

Тип реляционного оператора выбирается из предлагаемого перечня

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

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

Для задания сложного условия для значении элементов одного из столбцов в команде Автофильтр выбирается строка (Условие...), по которой открывается диалоговое окно Пользовательский автофильтр (рис. 5.5). В нем можно ввести два условия для одною столбца и объединить их логическими функциями И/ИЛИ. Задание условий пользовательского автофильтра для прайс-листа компьютерных фирм, приведенного на рис. 5.4, показано на рис. 5.5, а результаты выполнения такого фильтра приведены на рис. 5.6.

Рис. 5.5. Задание условий Пользовательского автофильтра.

	Тип	Cache	ОЗУ	HDD	Видео	Примечание	Цена
							(USD)
	Pentium 166 ММХ	256	16	1080	1 Mb, 14"	Intel Triton,	545
						Quantum
	Pentium 166 ММХ	512	16	2100	2 Mb, 15"	Intel 430TX,	674
						Quantum
	Pentium 200 ММХ	256	16	1080	1 Mb, 14"	Intel Triton,	580
						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.6. Результаты выполнения Пользовательского автофильтра.

Другой возможностью задания сложного условия в команде Автофильтр является выбор строки (Первые 10...), вызывающей окно Наложение условия по списку, в котором задается вывод определенного числа строк, содержащих максимальные (минимальные) значения данного столбца или заданный % строк (рис. 5.7).

Рис. 5.7. Дополнительное окно команды Автофильтр Наложение условия по списку.

Замечание.

• Чтобы быстро переместиться к нужному элементу в длинном списке, надо выделить стрелку и ввести первые буквы элемента.

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

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

5.1.2.2. Технология использования расширенного фильтра EXCEL

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

Данные.

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

Значения условий фильтрации, размещенные в одной строке диапазона, или области, критериев, объединяются логической функцией И, а значения условий, заданные в разных строках области критериев, связываются функцией ИЛИ. Диапазон условий должен отделяться от исходного списка по крайней мере одной пустой строкой. Для примера прайс-листа компьютерных фирм (рис. 5.4) на рис. 5.8. показано формирование диапазона условий фильтрации, размещенных на одном листе с исходным списком.

	А	В	С	D	Е	F		G
	Тип	Cache	ОЗУ	HDD	Видео	Примечание		Цена
								(USD)
				>1200				<900
			Фрагмент базы данных прайс-листа
				компьютерных фирм
	Тип	Cache	ОЗУ	HDD	Видео	Примечание		Цена
								(USD)
	Pentium 133	256	8	1080	1 Mb, 14"	Intel Triton,		460
						Quantum
	Pentium 133	256	16	1080	1 Mb, 14"	Intel Triton,		480
						Quantum

	Pentium 166	256	16	1080	1 Mb, 14"	Intel Triton,	520
						Quantum
	Pentium 166 ММХ	256	16	1080	1 Mb, 14"	Intel Triton,	545
						Quantum
0	Pentium 166 ММХ	512	16	2100	2 Mb, 15"	Intel 430TX,	674
						Quantum
1	Pentium 200 ММХ	256	16	1080	1 Mb, 14"	Intel Triton,	580
						Quantum
2	Pentium 200 ММХ	512	16	2100	2 Mb, 15"	Intel 430TX,	730
						Quantum
3	Pentium 233 ММХ	512	16	2100	2 Mb, 15"	Intel 430TX,	760
						Quantum
	Pentium 11-233	512	32	2100	4Mb, 15"	Intel 430LX,	980
4						VAAGP
						Quantum
	Pentium 11-300	512	32	2100	4 Mb. 15"	Intel 430LX,	1220
5						VAAGP
						Quantum

Рис. 5.8. Прайс-лист компьютерных фирм с диапазоном критериев, расположенным вверху списка на одном рабочем листе, для команды Расширенный фильтр.

После ввода условий в область критериев маркер выделения следует поместить в одну из ячеек списка и вызвать окно команды Расширенный фильтр (рис. 5.9).

Рис. 5.9. Окно команды Расширенный фильтр.