Технология подготовки отчета

Лабораторная работа № 8 В (часть 3)

                Тема: Технология структурирования и группировки экономической информации для бизнес–анализа.

Цель работы: Приобретение простейших практических навыков в обработке, фильтрации и консолидации табличных данных в Excel.

Порядок выполнения работы

1. Ознакомиться с методическими указаниями.

2. Включить компьютер. Загрузить систему Windows. Запустить редактор MS Excel.

3. Выполнить задание.

4. Сдать работу преподавателю.

Методические указания

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

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

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

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

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

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

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

Сортировка записей базы данных осуществляется при выборе пункта меню Данные/Сортировка. Эта команда открывает диалоговое окно, в котором можно задать до трех ключей порядка сортировки. Чтобы отсортировать список по четырем и более ключам, сортировку надо повторить. Можно выполнять не алфавитный, а пользовательский порядок сортировки (например, по месяцам).

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

Если в списке записей базы данных необходимо выбрать часть информации, то следует использовать так называемый «фильтр». Для простых условий достаточно применение автоматического фильтра (команда Данные/Фильтр/Автофильтр). Для фильтрации списка или базы данных по сложным критериям, включая вычисляемый критерий с использованием любой функции Excel, а также для получения результатов в другом месте рабочего листа используется расширенный фильтр (команда Данные/Фильтр/Расширенный фильтр).

Задание:

На основании ранее подготовленных листов Информация о сотрудникахи Ведомостьподготовить отчет, который должен содержать:

1. Список сотрудников, фамилии которых располагаются по алфавиту, в порядке убывания окладов сотрудников.

2. Список сотрудников, отработавших меньше месяца (меньше 24 дней).

3. Список сотрудников, заработная плата которых меньше средней заработной платы фирмы, а оклад больше среднего значения оклада.

4. Выдать сводную информацию о начислении денежных средств сотрудникам за два месяца — февраль и март.

Технология подготовки отчета

Порядок подготовки отчета может быть следующим:

1) Запустить программу Excel. Открыть книгу Информационные технологии.

2) Получить список сотрудников, фамилии которых располагаются по алфавиту, в порядке убывания окладов сотрудников. Для этого нужно:

· Перейти на лист Ведомость.

· Выделить таблицу (можно просто установить курсор на таблице, и она выделится автоматически).

· Вызвать команду Данные/Сортировка. Эта команда открывает диалоговое окно, показанное на рис. 1.

Рис. 1. Диалоговое окно Сортировка диапазона

· В поле ввода Сортировать пос помощью кнопки ▼ открыть список выбора, в котором остановиться на значении ‘Оклад’. Выбрать режим сортировки, например, по убыванию.

· В поле Затем поаналогичным образом выбрать ‘Фамилия’. Выбрать режим сортировки по возрастанию. Выполнить команду <Ok>.

3) Можно убедиться, что данные таблицы переупорядочились заданным образом.

4) Подготовить новый лист книги с именем Итоги.

5) Получить список сотрудников, отработавших меньше месяца (меньше 24 дней). Для этого следует проделать шаги:

· Выделить всю таблицу, включая заголовки.

· Выбрать команду Данные/Фильтр/Автофильтр. На экране появятся кнопки со стрелочками, как это показано на рис. 2.

Рис. 2. Вид окна после выполнения команды Данные/Фильтр/Автофильтр

· Нажать кнопку ▼ нужного поля («Число проработанных дней»). В открывшемся окне из набора появившихся значений (а это все варианты содержимого поля) выбрать значение Условие.В результате откроется окно Пользовательский автофильтрв виде, как это показано на рис. 3.

Рис. 3. Окно Пользовательский автофильтрс условием Число проработанных дней <24

· В поле ввода Число проработанных днейс помощью кнопки ▼ открыть список выбора, в котором остановиться на значении Меньше, и, справа от него расположенном окне, с помощью кнопки ▼ открыть список выбора, в котором остановиться на значении 24. Выполнить команду <Ok>.

· Скопировать полученную выборку в отдельную область на рабочем листе Итоги. Это можно сделать с помощью кнопок меню.

· Чтобы отменить фильтрацию, следует нажать кнопку со стрелкой ▼ и выбрать в предлагаемом списке элемент Все. А чтобы убрать стрелки команды Автофильтр,нужно выполнить команду Данные/Фильтр/Автофильтр.

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

· На свободном месте данной страницы сформировать критерий поиска в виде:

где Krit — имя критерия (может быть любая строка текста). В строке 18 (ячейки H18 и I18) записаны соответственно 2 условия, над которыми выполняется действие “и”.

Если требуется над условиями выполнить действие “или”, то критерий поиска следует сформировать в виде:

Замечание. Клавиша F4 меняет выделенный относительный адрес на абсолютный.

· В записи критерия в первой формуле L7 — соответствует первой ячейке столбца, содержащего значения Основная зар/плат.

· В записи критерия во второй формуле J7 — соответствует первой ячейке столбца, содержащего значения списка ‘Оклад’. В этой формуле используется функция СРЗНАЧ.

· Выделить исходную таблицу или установить на ней курсор.

· Выбрать команду Данные/Фильтр/Расширенный фильтр. На экране появятся диалоговое окно, как это показано на рис. 4.

Рис. 4. Окно расширенного фильтра

· Это окно следует заполнить и выполнить <Ok>.

· Заполнение окна:

Исходный диапазон: $D$6:$O$10

Диапазон условий:  $H$17:$I$18

Поместить результат в диапазон: $D$21:$O$30
— свободное место листа, а можно и на исходное место таблицы. Диапазон результата можно задать одним именем угловой ячейки диапазона, т. е. $D$21

· Следует убедиться в том, что фильтрация значений в сформированной таблице произведена правильно.

7) Получить сводную информацию о начислении им денежных средств за два месяца — февраль и март. Для этого нужно:

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

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

Рис. 5. Окно команды Правка/Специальная вставка

· Выделите сводную таблицу или установить курсор на таблице.

· Выбрать команду Данные/Сводная таблица. Появится окно Мастера сводных таблиц, предлагающее выполнить 3 шага. Окно для шага 2 представлено на рис. 6.

Рис. 6. Окно для шага 2 Мастера сводных таблиц

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

· Перейти к следующему шагу с помощью кнопки Далее.

· Откроется диалоговое окно 3-го шага, показывающее, куда поместить сводную таблицу. Поместите ее в новый лист книги.

· Нажмите кнопку Готовоили Макет.

Рис. 7. Окно для шага 3 Мастера сводных таблиц

• После перетаскивания мышкой полей (в соответствии с заданием) из правой части в схему таблицы или в макет, он примет вид, как это показано на рис. 8.

Рис. 8. Окно для шага 3 Мастера сводных таблицпосле перетаскивания полей

В результате получили сводную таблицу. Ее следует проанализировать и убедиться в правильности ее создания.

Новые термины

База данных, записи базы данных, сортировка, фильтр, мастер сводных таблиц.

Контрольные вопросы

1. Что собой представляет понятие спискаили базы данныхв Excel?

2. Что собой представляют записи базы данных? Поля записей?

3. Какие правила необходимо соблюдать при создании списка?

4. В каких случаях необходимы элементы сортировки и фильтрации данных? Приведите примеры.

5. Как подготовить сводный отчет?