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

 

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

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

Списки в Excel — это таблицы, строки которых содержат однород­ную информацию. В Excel список называют базой данных (БД), при этом строки таблицы — это записи базы данных, а столбцы — поля записей.

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

При создании списка на рабочем листе Excel необходимо выпол­нить следующие правила.

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

2. Следует отделять список от других данных рабочего стола хотя бы одним свободным столбцом или одной свободной строкой. Это по­может Excel автоматически выделять список при выполнении фильт­рации или при сортировке данных.

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

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

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

 

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

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

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

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

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

Пользовательский порядок сортировки по возрастанию или по убыванию можно задать по заданному ключу. Чтобы применить Пользовательский порядок сортировки, надо выбрать кнопку Параметры. в диалоговом окне Сортировка. Этот режим позволяет установить по­рядок сортировки по первому ключу — обычный или определяемый пользователем, задать учет кодировки строчных и прописных букв (учет регистра символов), а также направление сортировки — по воз­растанию или убыванию.

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

Фильтрация списков в ExcelДля выбора в списке части инфор­мации с некоторым условием необходимо использовать фильтр. Име­ются две разновидности этой команды, задаваемые параметрами Авто­фильтр и Усиленный (Расширенный) фильтр.

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

По команде Данные —> Фильтр —> Автофильтр в строке заголов­ков таблицы появляются кнопки с раскрывающимися списками значе­ний.

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

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

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

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

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

Каждая часть условия включает:

оператор отношения (=, о, <=, >=, <, >);

значение, которое может выбираться из списка или содержать шаб­лонные символы: «*», «?».

Можно задать условия для отбора нескольких столбцов независимо друг от друга, фильтрация выполняется по всем условиям одновременно. Все записи, не прошедшие через фильтр, будут скрыты. Отфильт­рованные записи можно выделить и скопировать в другое место, очи­стить содержимое, удалить и т. д.

Усиленный фильтр.Меню Данные —> Фильтр — > Усиленный фильтр обеспечивает использование двух типов критериев для фильт­рации записей списка:

критерий сравнения; вычисляемый критерий.

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

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

Технология использования усиленного фильтра включает два этапа:

формирование интервала критериев (т. е. создание образца фильт­рации);

фильтрация записей списка.

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

Критерий сравнения формируется при соблюдении следующих тре­бований:

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

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

ниже меток располагаются критерии сравнения типа: точное значе­ние; значение, формируемое с помощью операторов сравнения, и шаб­лон значения, включающий символы * и либо (или).

Правила формирования множественного критерия:

если критерии (условия) указываются в каждом столбце, то они считаются связанными условием «м»;

если условия записаны в нескольких строках, то они считаются связанными условием «или».

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

На втором этапе фильтрации записей списка после подготовки ин­тервала критериев курсор устанавливается в список и выполняется ко­манда Данные —> Фильтр —> Усиленный фильтр. Интервалы списка и критериев включают все строки, в том числе и строку наименования столбцов.

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

Для снятия действия условий фильтрации выполняется команда Данные —> Фильтр —> Показать все.

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

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

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

Создание промежуточных итогов основано на предварительной сортировке записей списка, при этом важен порядок сортировки — со­став и подчиненность ключей сортировки. Если сортировка была вы­полнена по полям: поле 1, поле 2, поле 3, — т. е. поле 1 является са­мым старшим в сортировке, поле 2 определяет порядок сортировки строк списка при одинаковых значениях поля 1, а поле 3 задает поря­док сортировки при одинаковых значениях и поля 1, и поля 2, — то и подведение итогов имеет свой жесткий порядок: поле 1, поле 2, поле 3 или — поле 1, поле 2, или поле 1.

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

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

Команда Итоги в меню Данные позволяет выполнять следующие действия:

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

для одинаковых значений поля группировки можно формировать итоги по одному или нескольким полям списка, при этом вид опера­ции определяет, какие поля могут использоваться для подведения ито­гов. Так, для операций Сумма, Среднее, Максимальное, Минимальное и т. п. могут выбираться поля только числового типа. Для операции Количество значений — поля любого типа (числовые, текстовые, даты).

Консолидация данных. Другим способом получения итоговой ин­формации является консолидация — агрегирование согласно выбран­ной функции обработки данных, представленных в исходных облас­тях-источниках Результат консолидации находится в области назна­чения. Области-источники могут находиться на различных листах или рабочих книгах. В консолидации может участвовать до 255 облас­тей-источников, а сами источники могут быть закрыты во время кон­солидации.

Для консолидации данных курсор устанавливается в область местоназначения. Выполняется команда Данные —> Консолидация, выбирает­ся вариант и задаются условия консолидации. Существуют следующие варианты консолидации:

консолидация по расположению для одинаково организованных источников (фиксированное расположение);

консолидация по категориям для различающихся по расположению данных;

консолидация внешних данных.

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

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

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

Если консолидируются данные по категориям, указывается тип ме­ток в верхней строке и левом столбце.

Переключатель Создавать Связи с Исходными Данными созда­ет при консолидации связи области назначения к областям-источ­никам.

Работа с диаграммами

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

Обычно перед вызовом Мастера диаграмм выделяется интервал ячеек — область данных для построения диаграммы. Мастер диа­грамм осуществляет построение новой диаграммы в интерактивном режиме за несколько шагов:

Шаг 1. Указание блока ячеек с исходными данными для построе­ния диаграммы. Блок ячеек может включать как сами данные, так и дополнительную информацию, которая используется в качестве на­званий исходных данных (легенд), указаний меток по оси X. Блок яче­ек может содержать несмежные ячейки одного рабочего листа (выде­ляются при нажатии клавиши Ctrl).

Шаг 2. Выбор типа диаграмм. Excel позволяет построить диаграм­мы 14 стандартных типов.

Шаг 3. Выбор формата диаграммы указанного типа (можно про­смотреть результаты выбора, нажав клавишу Просмотр результатов).

Шаг 4. Задание параметров диаграммы: расположение данных; способ использования первой строки или столбца (метки осей, текст легенды, название диаграммы).

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

Шаг 5. Добавление легенды, ввод названия диаграммы и подписей к осям диаграммы.

 

Диаграмма может и не содержать легенд, если они не вошли в ин­тервал выделения, но легенду можно добавить при редактировании рядов диаграммы.

Редактирование диаграмм.Созданные диаграммы можно коррек­тировать вплоть до изменения состава и способа представления исходных данных, на основании которых построена диаграмма. Редактирование осуществляется как с помощью Мастера диаграмм, так и с по­мощью команд меню и инструментов панели Диаграмма.

Использование Мастера диаграмм. Данный вид корректировки диаграмм обеспечивает изменение:

исходного интервала ячеек, на основании которого построена диа­грамма;

ориентации рядов;

числа строк и столбцов, отводимых для меток оси X и названия ле­генды в диаграмме.

Предварительно следует выделить объект диаграммы. Далее нажа­тием кнопки «Мастер диаграмм» запускается режим корректировки, состоящий из нескольких шагов:

Шаг 1. Корректировка интервала ячеек для диаграммы.

Шаг 2. Корректировка ориентации в рядах, определение меток оси X и легенд.

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

команда «Тип диаграммы» используется для изменения типа диа­граммы для отдельной последовательности данных, группы или всей диаграммы в целом;

команда «Исходные данные» используется для добавления или изменения выделенного ряда данных или отдельного элемента диа­граммы;

команда «Параметры диаграммы» используется для изменения стандартных параметров выбранного типа диаграмм. Изменения могут затрагивать такие элементы, как сетка, оси, подписи данных и заголо­вок диаграммы;

команда «Размещение» позволяет выбрать расположение выделен­ных объектов на листе;

команда «Добавить данные» позволяет сделать добавление выде­ленного ряда данных или точек на диаграмму;

команда «Линия тренда» (меню Вставка) позволяет добавить или изменить линию тренда на диаграммах различных типов.

Алгоритм изменения диапазона ячеек, используемого для соз­дания диаграммы:

выберите изменяемую диаграмму;

выберите команду Исходные данные в меню Диаграмма, а затем выберите вкладку Диапазон данных;

убедитесь, что выделена полностью вся ссылка в поле Диапазон данных;

на рабочем листе выберите ячейки, содержащие данные, которые должны появиться в диаграмме;

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