Лабораторная работа № 3. Обработка и анализ данных в MS Excel.

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

Цель работы: изучить основные приемы агрегирования данных с помощью формул, консолидации MS Excel.

Материал для работы: файл-заготовка ЛР3-1.xlsx.

Результат работы: файлы ЛР3-1_ФАМ.xlsx, ЛР3-2_ФАМ.xlsx.

Выполнение работы

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

Необходимо получить обобщенные данные по количеству обучаемых в учреждениях образования Республики Беларусь. Исходные данные по областям и учебным годам представлены в файле ЛР3-1.xlsx.

 откройте файл ЛР3-1.xlsx. Обратите внимание на форматирование таблиц на листах. Количественные данные представлены на 10000 человек населения, что указано в примечании ячейки А1 (наличие примечания в ячейке показывает красный треугольник в правой верхней части);

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

на листе Минская в ячейке А5 наберите Итого;

активизируйте последнюю ячейку столбца «2014/2015 учебный год»;

на вкладке Главная в области Редактирование выберите пиктограмму

Сумма. В ячейке появится формула =СУММ(В2:В4), а диапазон суммирования

будет выделен. Для завершения нажмите Enter. Итоговое значение появится в ячейке. Формулу можно написать вручную;

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

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

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

 получите соответствующие показатели и по другим областям.

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

 добавьте новый лист Итоговые данные после листа Минск. Нажмите пиктограмму + (Новый лист) в области ярлыков листов. Двойным щелчком переименуйте лист;

 скопируйте заголовки столбцов с учебными годами (например, с листа Минская). Перейдите на лист консолидации;

 вставьте данные, но уже не по столбцам, а по строкам. Выделите ячейку В2. Выберите пиктограмму Транспонировать из группы Вставить на вкладке Главная. Теперь заголовки будут располагаться в одном столбце по строкам;

 уберите обрамление таблицы. Текст разместите в одну строку. При необходимости двойным щелчком мышью на границе строк уменьшите высоту строки (аналогично ширину столбца);

 в ячейке В1 наберите Учебный год, в С1 – Общее количество обучаемых;

 выделите ячейку С2. Наберите формулу для отображения сумму обучаемых за этот год по всем областям. В формуле будет использоваться ссылка на листы с данными (!). формулу набирайте в адресной строке. Каждый раз выделяйте ячейку В5 на соответствующем листе области:

=Минская!B5+Брестская!B5+Витебская!B5+Могилевская!B5+Гродненская!B5+Минск!B5

 получите итоговые данные и по другим годам.

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

 создайте новый лист Консолидация_образование. Активизируйте ячейку А1. Она будет служить верхней левой ячейкой для блока с результатами консолидации.

 на вкладке Данные выберите Работа с данными/Консолидация. В окне диалога в списке Функция по умолчанию установлено Сумма. Из раскрывающегося списка выберите функцию Среднее.

 щелкните мышью в поле Ссылка. Перейдите к добавлению диапазонов. Щелкните на ярлыке листа Минская, в поле появится имя листа, теперь обведите мышью нужный диапазон. Нажмите кнопку Добавить;

  перейдите на следующий лист и повторите эти операции со всеми последующими листами (рисунок 12).

Рисунок 12 – Окно диалога команды Консолидация.

 установите флажки Подписи верхней строки и Значения левого столбца, это нужно, чтобы информация в таблице идентифицировалась по названиям строк и столбцов. Установите флажок Создавать связи с исходными данными. Это позволит автоматически обновлять данные в консолидированной таблице. Нажмите ОК.

 измените формат представления чисел. Выберите Числовой, число десятичных знаков: 1. При необходимости измените ширину столбцов.

Консолидированный отчет представляет собой структурированную таблицу.

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

 активизируйте любую ячейку с данными. В строке формул отображается, согласно функции Среднее, =СРЗНАЧ(С9:С14);

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

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

Задания для самостоятельной работы

1. Получите данные о прибыли организации (среднее) по трем филиалам с помощью  команды    Консолидация.    Данные    представьте    графически

(тенденции, процентные соотношения и т.п.). Результаты сохраните в файле ЛР3-2.xlsx.

Рекомендуется для упрощения набора данных создать таблицу со всеми вычислениями (шаблон) на одном листе, а затем скопировать ее на другие листы, вводя новые данные.

Выполните следующие предварительные расчеты:

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

Прибыль от продаж – между валовой прибылью и всеми расходами.

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

Текущий налог определяется произведением прибыли до налогообложения и ставки налога на прибыль (20%).

Чистая прибыль – это разница между прибылью до налогообложения и текущим налогом на прибыль.

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

Исходные данные.

Филиал 1                                                     Филиал 2

Филиал 3