Методы обработки и анализа экономической информации в EXCEL
Цель работы
Получить представление о задачах анализа экономической информации и изучить основные информационные технологии решения задач бизнес-анализа; овладеть методами обработки и анализа экономической информации с помощью инструментов электронного процессора MS Excel.
Теоретическое введение
Задачи бизнес-анализа требуют применения информационной технологии выполнения сортировки, фильтрации и консолидации данных в электронных таблицах, формирования разнообразных итогов и сводов, а также выполнения экономических расчетов в таблицах для данных, удовлетворяющих заданным условиям. Например, обработка всевозможных прайс-листов компьютерных фирм является типичной задачей этого класса.
Для решения подобных задач в популярном электронном процессоре EXCEL необходимо представить электронную таблицу в виде списка, или базы данных. При этом списками в EXCEL называются таблицы, строки которых содержат однородную информацию. В терминологии EXCEL список называют также базой данных (БД), при этом строки таблицы — это записи базы данных, а столбцы — поля записей.
Чтобы превратить таблицу EXCEL в список, или базу данных, необходимо присвоить столбцам однострочные имена, которые будут использоваться как имена полей записей базы данных. Следует иметь в виду, что строка имен полей может состоять из нескольких строк заголовков, размещенных в одной строке таблицы EXCEL, как это показано в таблице 9.
Таблица 9 – Исходная таблица списочных данных
| Кредитор | Сумма кредита, тыс. руб. | Годовая ставка процента, % | Число дней, на которые выдан кредит |
| Ветров А.Р. | 400 000 | ||
| Захаров Л.Д. | 78 000 | ||
| Иванов В.А. | 10 000 | ||
| Каримов Р.А | 80 000 | ||
| Морозов К.Н. | 65 000 | 29,50 | |
| Петров А.А. | 50 000 | ||
| Сидоров П.Р. | 350 000 | 28,50 | |
| Черкасов К.Г. | 99 000 | ||
| ИТОГО: | 1 132 000 |
При создании списка на рабочем листе EXCEL необходимо соблюдать следующие правила:
• на одном рабочем листе не следует помещать более одного списка, поскольку некоторые операции, работают в определенный момент только с одним списком;
• следует отделять список от других данных рабочего листа хотя бы одним свободным столбцом или одной свободной строкой;
• имена полей списка должны располагаться в первой строке таблицы;
• для имён полей следует использовать форматирование заголовков столбцов, отличное от того, которое использовалось для данных списка;
• чтобы отделить имена полей от данных, следует поместить рамку по нижнему краю ячеек строки с именами столбцов. Нельзя использовать пустые строки или пунктирные линии;
• каждый столбец списка должен содержать во всех строках однотипные данные;
• не следует вводить дополнительные пробелы в начале ячеек данных, поскольку они влияют на сортировку и поиск.
Сортировка списков
Сортировку данных в заданном порядке в EXCEL можно выполнить с помощью команды Сортировка (меню Данные), предварительно выделив весь список с заголовками столбцов (кроме итоговых строк таблицы). Включение заголовков столбцов в область выделения позволяет использовать их в качестве ключей сортировки.
 |
Команда Сортировка открывает одноименное диалоговое окно, в котором можно задать до трех ключей с указанием порядка сортировки. Сортировка выполняется сначала по первому ключу, затем в строках с совпадающим значением первого ключа — по второму ключу, наконец, в строках с одинаковыми значениями первого и второго ключа — по третьему ключу. Например, для примера в таблице 1 сортировка списка производится по возрастанию Числа дней, на которые выдан кредит, а при одинаковом числе дней — по возрастанию Годовой ставки процента, требует задания параметров сортировки, как показано на рисунке 31.
Рисунок 31 - Образец задания параметров сортировки в MS Excel
Результат выполнения такой сортировки списка приведен в таблице 10.
Таблица 10 – Результат сортировки исходных списочных данных в MS Excel
| Кредитор | Сумма кредита, тыс. руб. | Годовая ставка процента, % | Число дней, на которые выдан кредит |
| Иванов В.А. | 10 000 | ||
| Сидоров П.Р. | 350 000 | 28,50 | |
| Захаров Л.Д. | 78 000 | ||
| Морозов К.Н. | 65 000 | 29,50 | |
| Петров А.А. | 50 000 | ||
| Черкасов К.Г. | 99 000 | ||
| Каримов Р.А | 80 000 | ||
| Ветров А.Р. | 400 000 | ||
| ИТОГО: | 1 132 000 |
Следует иметь в виду, что последовательность стандартной сортировки определяется содержанием и типом данных (например, от наименьшего отрицательного к наибольшему положительному числу или от наиболее ранней даты и времени к поздней), поэтому в случае необходимости другого порядка сортировки, определяемого пользователем, используют кнопку Параметры... в диалоговом окне Сортировка (в этом случае можно, например, отсортировать список по месяцам календаря: январь, февраль, март и т. д.).
Фильтрация списков
Если в списке необходимо выбрать часть информации в соответствии с некоторым условием, то следует использовать фильтр.
Для простых условий используют автофильтр, для использования которого сначала выделяют область списка или весь список, причем обязательно с заголовками столбцов (при этом имена столбцов списка EXCEL преобразует в имена полей записей базы данных). Затем используют команду Данные, Фильтр, Автофильтр, которая в строке заголовков таблицы отображает кнопки с раскрывающимися списками значений.
Рассмотрим применение автофильтра на примере прайс-листа компьютерных фирм, который приведен в таблице 11.
Таблица 11 – Фрагмент прайс-листа компьютерных фирм
| Тип | Cache, kb | ОЗУ | HDD | Видео | Примечание | Цена, USD |
| Pentium 133 | 1 Mb, 14" | Intel Triton, Quantum | ||||
| Pentium 133 | 1 Mb, 14" | Intel Triton, Quantum | ||||
| Pentium 166 | 1 Mb, 14" | Intel Triton, Quantum | ||||
| Pentium 166 ММХ | 1 Mb, 14" | Intel Triton, Quantum | ||||
| Pentium 166 ММХ | 2 Mb, 15" | Intel 430TX, Quantum | ||||
| Pentium 200 ММХ | 1 Mb, 14" | Intel Triton, Quantum | ||||
| Pentium 200 ММХ | 2Mb, 15" | Intel 430TX, Quantum | ||||
| Pentium 233 ММХ | 2 Mb, 15" | Intel 430TX, Quantum | ||||
| Pentium II 233 | 4 Mb, 15" | Intel 430LX, VA AGP Quantum | ||||
| Pentium II 300 | 4 Mb, 15" | Intel 430LX, VA AGP Quantum |
Автофильтр предполагает использование критериев поиска типа сравнение двух типов: по точному или шаблонному значению и по условию oтбopa.
Точное значение для сравнения выбирается из раскрывающегося списка для указанного поля, формируемого EXCEL при выполнении команды Данные, Фильтр, Автофильтр.
При выборе сравнения по условию задается критерий oтбopa, состоящий из двух предложений, связанных между собой логической связкой И либо ИЛИ. При этом каждое предложение имеет стандартную структуру:
< реляционный оператор > < значение >.
Тип реляционного оператора выбирается из предлагаемого перечня (больше, больше или равно, равно, содержит, начинается с и т.д.).
Выбор конкретного значения условия в одном из столбцов приводит к скрытию всех строк таблицы с другими значениями этого столбца. Последовательное применение этого механизма позволяет создать условия, объединенные логическим умножением (И).
Для задания сложного условия для значений элементов одного из столбцов в команде Автофильтр выбирается строка (Условие...), в которой можно ввести два условия для одного столбца и обьединить их логическими функциями И/ИЛИ. Задание условий Пользовательского автофильтра для прайс-листа компьютерных фирм, приведенного в таблице 11, показано на рисунке 32, а результаты выполнения такого фильтра приведены в таблице 12.
Рисунок 32 - Образец задания условий Пользовательского автофильтра в MS Excel
Таблица 12 – Результаты выполнения Пользовательского автофильтра в MS Excel
| Тип | Cache | ОЗУ | HDD | Видео | Примечание | Цена, USD |
| Pentium 166 ММХ | 1 Mb, 14" | Intel Triton, Quantum | ||||
| Pentium 166 ММХ | 2 Mb, 15" | Intel 430TX, Quantum | ||||
| Pentium 200 ММХ | 1 Mb, 14" | Intel Triton, Quantum | ||||
| Pentium 200 ММХ | 2Mb, 15" | Intel 430TX, Quantum | ||||
| Pentium 233 ММХ | 2 Mb, 15" | Intel 430TX, Quantum |
Автофильтр не позволяет вводить условия для разных столбцов, объединенных логическим сложением (ИЛИ), а также более сложные логические условия. Для фильтрации списка или базы данных по сложным критериям, включая вычисляемый критерии с использованием любой функции EXCEL, а также для получения результатов фильтрации в другом месте рабочего листа, вне исходного списка, применяется команда Расширенный фильтр из меню Данные.
Прежде чем выполнять вызов диалогового окна Расширенный фильтр, необходимо сформировать диапазон условий, который для удобства доступа целесообразно разместить перед исходным списком. Диапазон условий должен включать заголовки строк исходного списка и хотя бы одну строку с критериями отбора записей списка (таблица 13).
Таблица 13 - Образец обработки прайс-листа компьютерных фирм с диапазоном критериев для команды Расширенный фильтр в MS Excel
| A | B | C | D | E | F | G | |
| Тип | Cache | ОЗУ | HDD | Видео | Примечание | Цена, USD | |
| >1200 | <900 | ||||||
| Фрагмент базы данных прайс-листа компьютерных фирм | |||||||
| Тип | Cache | ОЗУ | HDD | Видео | Примечание | Цена USD | |
| Pentium 133 | 1 Mb, 14" | Intel Triton, Quantum | |||||
| Pentium 133 | 1 Mb, 14" | Intel Triton, Quantum | |||||
| Pentium 166 | 1 Mb, 14" | Intel Triton, Quantum | |||||
| Pentium 166 ММХ | 1 Mb, 14" | Intel Triton, Quantum | |||||
| Pentium 166 ММХ | 2 Mb, 15" | Intel 430TX, Quantum | |||||
| Pentium 200 ММХ | 1 Mb, 14" | Intel Triton, Quantum | |||||
| Pentium 200 ММХ | 2Mb, 15" | Intel 430TX,Quantum | |||||
| Pentium 233 ММХ | 2 Mb, 15" | Intel 430TX, Quantum | |||||
| Pentium II 233 | 4 Mb, 15" | Intel 430LX, VA AGP Quantum | |||||
| Pentium II 300 | 4 Mb, 15" | Intel 430LX, VA AGP Quantum |
Значения условий фильтрации, размещенные в одной строке диапазона, или области, критериев, объединяются логической функцией И, а значения условий в разных строках связываются функцией ИЛИ. Диапазон условий должен отделяться от исходного списка по крайней мере одной пустой строкой. Для примера прайс-листа компьютерных фирм на рисунке 33 показано формирование диапазона условий фильтрации, размещенных на одном листе с исходным списком. При этом диапазон критериев расположен на рисунке вверху списка на одном рабочем листе с исходным списком фильтрации.
 |
После ввода условий в область критериев маркер выделения следует пометить в одну из ячеек списка и вызвать окно команды Расширенный фильтр (рисунок 33).
Рисунок 33 - Образец окна команды Расширенный фильтр в MS Excel
Результат выполнения условий фильтра для прайс-листа (таблица 11) приведен в таблице 14.
Таблица 14 – Результаты фильтрации прайс-листа компьютерных фирм с помощью Расширенного фильтра в MS Excel
| Тип | Cache | ОЗУ | HDD | Видео | Примечание | Цена, USD |
| Pentium 166 ММХ | 2 Mb, 15" | Intel 430TX, Quantum | ||||
| Pentium 200 ММХ | 2Mb, 15" | Intel 430TX, Quantum | ||||
| Pentium 233 ММХ | 2 Mb, 15" | Intel 430TX, Quantum |
В диапазон условий команды Расширенный фильтр можно ввести вычисляемые критерии. При этом следует придерживаться следующих правил:
• заголовок над вычисляемым критерием не должен совпадать ни с одним заголовком списка, он может быть даже пустой ячейкой;
• ссылки на ячейки списка в левой части формулы вычисляемого критерия должны быть относительными, а ссылки на ячейки списка в правой части формулы критерия и ссылки на ячейки вне списки должны быть абсолютными.
В качестве примера выберем в компьютерном прайс-листе (таблица 3) все компьютеры, цена которых выше средней для данного списка. Зададим условия фильтрации двумя способами.
Сначала вычислим среднее значение цены в какой-либо ячейке вне списка, например в A3 (рисунок 3). Введем в ячейку A3 формулу вычисления среднего значения цены по столбцу G: =CP3HAЧ(G6:G15), которая для данного примера дает значение средней цены 649,9. Если столбцу цен (G6:G15) присвоить имя блока Цена, то формула вычисляемого критерия получит следующий вид: =СРЗНАЧ(Цена).[3] Для задания вычисляемого критерия в ячейку А1 введем заголовок Цена выше средней, а в ячейку А2 введем следующую формулу: =G6>$A$3, где G6 - первая ячейка столбца G, содержащего цены компьютеров. После выполнения команды Расширенный фильтр (при этом в поле Диапазон условий следует ввести ссылку на ячейки $А$1:$А$2, содержащие критерии oт6oрa записей списка) список будет содержать строки с ценой выше средней, как показано в таблице 15.
Таблица 15 – Список компьютеров, цена которых выше средней в прайс-листе исходного списка
| Тип | Cache | ОЗУ | HDD | Видео | Примечание | Цена (USD) |
| Pentium 200 ММХ | 2Mb, 15" | Intel 430TX, Quantum | ||||
| Pentium 233 ММХ | 2 Mb, 15" | Intel 430TX, Quantum | ||||
| Pentium II 233 | 4 Mb, 15" | Intel 430LX, VA AGP Quantum | ||||
| Pentium II 300 | 4 Mb, 15" | Intel 430LX, VA AGP Quantum |
Другой способ задания вычисляемого критерия не требует предварительного вычисления средней цены в отдельной ячейке прайс-листа. В ячейке А1 (рисунок 3) удалим заголовок, сделав ее пустой, а в ячейку А2 введем формулу: =G6>CP3HAЧ($G$6:$G$15), которая отфильтрует ячейки столбца G со значениями больше средней цены в прайс-листе.