Консолидация данных по категориям

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

столбца (рис. 4). Excel автоматически переносит эти имена в область назначения.

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

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

 

Рисунок 3 Диалоговое окно Консолидация для задания условий консолидации

 

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

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

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

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

Задание 1

 

Сделать копию таблицы на новый лист, изменить в ней данные. Эта таблица будет отражать, например, показатели во 2 квартале (рис. 4).

Рисунок 4Пример консолидации данных по расположению

 

Выполнить консолидацию данных по расположению:

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

· выполнить команду Данные\ Консолидация;

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

· установить курсор в окне Ссылка и выделить блок ячеек А2:D7

(показатели в 1 квартале);

· нажать кнопку Добавить, в окне Список диапазонов появится ссылка на выделенный диапазон;

· установить курсор в окне Ссылка, удалить прежнюю запись и выделить блок ячеек F2:I7 (показатели во 2 квартале);

· нажать кнопку Добавить, в окне Список диапазонов появится ссылка на выделенный диапазон;

· нажать кнопку ОК и сравнить полученные результаты с рис. 4.

Консолидация по категориям

1 Добавить строку с товаром TV-тюнерс соответствующими числами в первую таблицу "Показатели в 1 квартале"(рис. 6).

2 Вставить новый столбец с именем % реализации и заполнить формулой Продано*100/ Получено. Чтобы выводилось 2 знака после запятой, в меню Формат\Ячейки, вкладка Число выбрать в поле Числовые форматы строку Числовой и установить Число десятичных знаков - 2;

3 Сделать консолидацию данных по категориям:

· установить курсор в первую ячейку области, где будет располагаться консолидиpoванная таблица, например в ячейку A11;

· выполнить команду Данные\ Консолидация;

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

· установить курсор в окне Ссылка и выделить блок ячеек A2:Е8 (показатели в 1 квартале);

· нажать кнопку Добавить, в окне Список диапазонов появится ссылка на выделенный диапазон;

· установить курсор в окне Ссылка, удалить предыдущую запись и выделить блок ячеек G2:J7 (показатели во 2 квартале);

· нажать кнопку Добавить, в окне Список диапазонов появится ссылка на выделенный диапазон;

· нажать кнопку <ОК> и сравнить результаты с рис. 5.

 

Рисунок 5 Пример консолидации данных по категориям

Задание 2

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

1. Создайте в новой книге на отдельных листах таблицы посещаемости по месяцам (сентябрь-декабрь):

2. Заполните таблицы на 4 листах и рассчитайте Пропусков всего.

3. Переименуйте листы как Посещаемость в «месяц».

4. На отдельном листе создайте такую же таблицу, только под названием Посещаемость за семестр (кол-во пропусков).

5. Переименуйте этот лист как Посещаемость за семестр.

6. Сохраните книгу в папку WORK как Сведения о посещаемости.

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

8. На листе Посещаемость за семестр необходимо выделить конечную область (область назначения), т.е. блок ячеек, в который будут помещены консолидированные данные. В нашем случае это диапазон, B4:G10.

9. Выберите Данные►Консолидация.

10. В появившемся диалоговом окне в поле Функция выберите Среднее[1] в поле с раскрывающимся списком. Оставьте флажки в секции Использовать метки неустановленными. Так как мы не собирались устанавливать связи с исходными листами, флажок Создавать связи с исходными данными также оставьте неустановленными.

11. В поле ссылка необходимо ввести ссылки на каждый исходный диапазон:

– установите курсор в поле Ссылка;

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

– выделите диапазон ячеек B4:G10;

– нажмите кнопку Добавить в диалоговом окне Консолидация. Эта ссылка появиться в Списке диапазонов;

– добавьте самостоятельно ссылки на листы посещаемости в октябре, ноябре, декабре.

12. Нажмите ОК.

13. В столбце Н определим студентов попадающих под отчисление:

– в ячейке Н2 введите На отчисление;

– в ячейке Н4 самостоятельно введите формулу для определения отчисляемых студентов (используется функция ЕСЛИ, для отчисляемых студентов должна выводиться надпись «на отчисление»)

14. Используя Автофильтр, отберите только отчисляемых студентов и скопируйте их в текстовый редактор WORD.

15. Сохраните документ под именем Список на отчисление.

Задание 3

Имеются данные о деятельность ОАО «Эльдорадо» за каждый месяц. Необходимо подвести итоги за каждый месяц, квартал и за весь год. Построить диаграмму сравнения продаж по кварталам. Скопировать диаграмму и итоговую таблицу в текстовый редактор WORD.

1. Создайте в новой книге на отдельных листах таблицу Продаж ОАО «Эльдорадо» по кварталам (каждый квартал на отдельном листе) по образцу:

Продажи ОАО "Эльдорадо" за I квартал, руб.
N п/п	Наименование продукции	Месяцы	Итого за I квартал
январь	февраль	март
					?
					?
.					?
					?
Итого	?	?	?	?

 

2. Самостоятельно заполните поле Наименование продукции.

3. Скопируйте эту шапку на 3 других листа.

4. Заполните поля Продажи.

5. Произведите расчеты в ячейках отмеченных вопросительным знаком.

6. Переименуйте Листы как Продажи за? квартал.

7. На отдельном листе создайте таблицу:

 

Продажи ОАО "Эльдорадо" за год
N п/п	Наименование продукции	Кварталы	Итого год, руб	Итого год, $
I	II	III	IV
						?	?
						?	?
.						?	?
						?	?
Итого	?	?	?	?	?	?

8. Для заполнения поля Кварталы этой таблицы использовать команду Консолидация.

9. В отдельной ячейке указать курс валюты.

10. Отформатировать все таблицы.

11. Построить три диаграммы (круговую, гистограмму, график) сравнения продаж по кварталам.

12. Скопировать таблицу Продажи ОАО "Эльдорадо" за год и одну из диаграмм в текстовый редактор Word.

13. После копирования произвести форматирование таблицы и диаграммы.

14. Сохранить книгу и документ под именем Продажи ОАО «Эльдорадо» в папку Work или дискету.

 

Использование функции ЕСЛИ

1. В новой книге на Листе1 создайте таблицу по образцу:

2. Заполните данными ячейки В4:Е10;

3. Для подсчета количества отличников и хорошистов в группе необходимо использовать дополнительно столбцы F и G.

4. В столбцах F и G необходимо ввести формулу с использованием логических функций ЕСЛИ и И. Функция И позволяет создавать сложные логические выражения. Эта функция работает в сочетании с простыми операторами сравнения: =, >, <, >=, <= и, < >. Эта функция может иметь до 30 логических аргументов и имеет следующий синтаксис: =И(логическое_значение 1; логическое_значение 2;…; логическое_значение 30)

5. Ведите формулу в ячейку F4:

· установите курсор в ячейку F4 и выберите Вставка►Функция в меню программы;

· в списке Категория выберите пункт Логические;

· в списке Функция выберите функцию ЕСЛИ и щелкните на кнопке ОК.

· в появившемся диалоговом окне в поле Логическое выражение введите И(СЧЁТ(B4:E4)=4;МИН(B4:E4)=5). В этой формуле проверяются сразу два условия, объединенных функцией И: количество сданных экзаменов – 4 (СЧЁТ(B4:E4)=4) и все экзамены сданы на пять (МИН(B4:E4)=5);

· в поле Значение если истина введите 1 (единицу). Если все 4 экзамена сданы на пять, то выведется 1;

· в поле Значение если ложь введите «» (кавычки). В противном случае – пустая строка.

· нажмите ОК.

6. Скопируйте эту формулу в ячейки F5:F10.

7. Ведите формулу в ячейку G4:

· щелкните на кнопке Вставка функции на стандартной панели инструментов;

· выберите функцию ЕСЛИ;

· в появившемся диалоговом окне в поле Логическое выражение введите И(СЧЁТ(B5:E5)=4;МИН(B5:E5)=4;СУММ(B5:E5)=16) В этой формуле проверяются три условия, объединенных функцией И: количество сданных экзаменов – 4 (СЧЁТ(B4:E4)=4) и все экзамены сданы на четыре (МИН(B5:E5)=4;СУММ(B5:E5)=16);

· в поле Значение если истина введите 1 (единицу). Если все 4 экзамена сданы на пять, то выведется 1;

· в поле Значение если ложь введите «» (кавычки). В противном случае – пустая строка.

· нажмите ОК.

8. Скопируйте эту формулу в ячейки G5:G10.

9. Теперь напротив фамилий отличников и хорошистов установились единицы.

10. Задайте имя ячейкам F4:F10 – отличники.

11. Для ячеек G4:G10 задайте имя – хорошисты.

12. В ячейке В12 подсчитайте количество отличников.

13. В ячейке В13 подсчитайте количество хорошистов.

14. Переименуйте лист как Результаты 1 сессии.

15. Мы можем скрыть столбцы F и G:

· выделите столбцы F и G или ячейки в них;

· выберите Формат►Столбец►Скрыть. EXCEL установит ширину этих столбцов ноль и буквы заголовка столбцов не будут отображаться в верхней части листа. Чтобы снова вывести столбец на экран, сначала выделите столбцы, расположенные слева и справа от скрытых столбцов (столбцы Е и Н), а затем выберите Формат►Столбец►Показать.

16. Сохраните книгу под именем 1 сессия.