Контрольный пример

КОНСОЛИДАЦИЯ РАБОЧИХ ЛИСТОВ В EXCEL

1. ЦЕЛЬ И ЗАДАЧИ ПРОВЕДЕНИЯ ЛАБОРАТОРНОЙ РАБОТЫ

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

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

КОНТРОЛЬНЫЙ ПРИМЕР

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

1. Создать рабочую книгу, состоящую из четырех листов. Переименовать рабочие листы в листы с названиями Январь, Февраль, Март, Квартал с помощью контекстного меню (либо дважды щелкнуть мышкой по ярлычку листа).

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

3. В групповом режиме составить ведомость движения товаров за январь (таб. 1, таб. 2).

Таблица 1.

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

5. Скорректировать содержимое листа Февраль в соответствии с таблицами 3, 4.

6. Скорректировать содержимое листа Март в соответствии с таблицами 5, 6.

7. Скорректировать содержимое листа Квартал в соответствии с таблицами 7, 8, 9.

8. Выполнить консолидацию рабочих листов. Для этого вставить новый лист и переименовать его в лист Консолидация 1. Установить курсор в ячейку А1. Вызвать команду Данные/Консолидация. В диалоговом окне Консолидация в поле Функция выбрать функцию Сумма.

9. Установить курсор в поле Ссылка, затем выделить диапазон А2:G12 листа Январь и щелкнуть кнопку Добавить. Эта область автоматически перенесется в поле Список диапазонов. Затем клавишей Delete очистить поле Ссылка. Выделить поочередно диапазон А2:G12 листов Февраль и Март и добавить их в поле Список диапазонов.

Таблица 2.

10. Щелкнуть мышкой по флажку Создавать связи с исходными данными, при этом при изменении исходных данных в рабочих листах Январь, Февраль, Март изменяться и консолидированные данные.

Таблица 3.

11. Щелкнуть по флажку В верхней строке, при этом названия из диапазона A2:G2 будут использованы при консолидации.

12. Щелкнуть по флажку В левом столбце. При этом при консолидации будут использованы значения ячеек диапазона А2:А12. В заключении нажмем кнопку ОК. Получим консолидированные данные по таблицам, расположенным на рабочих листах Январь, Февраль, Март (таб. 10- 15).

Таблица 4.

Таблица 5.

13. Выполнить второй вариант консолидации. Для этого вставить новый лист и переименовать его в лист Консолидация 2. Установить курсор в ячейку А1. Вызвать команду Данные/Консолидация. В диалоговом окне Консолидация в поле Функция выбрать функцию Сумма.

Таблица 6.

Таблица 7.

14. Очистить содержимое поля Список диапазонов кнопкой Удалить.

15. Установить курсор в поле Ссылка, выделить диапазон В2:G13 листа Январь и щелкнуть кнопку Добавить. Затем поочередно диапазон В2:G13 листов Февраль и Март добавить в поле Список диапазонов.

16. Щелкнуть мышкой по флажку Создавать связи с исходными данными.

17. Щелкнуть по флажку В верхней строке, по флажку В левом столбце, ОК (таб. 16-21).

18. Консолидированные данные представлены в структурированной форме, что упрощает визуальный контроль в больших таблицах. Отдельные строки таблицы скрыты. Щелкая мышкой по кнопкам «+» и «-», расположенным слева вдоль бордюра, можно скрывать и открывать элементы структуры таблицы.

19. Сравнить консолидированные данные, отраженные на листах Консолидация 1 и Консолидация 2.

20. Удалить структуру можно командой Данные/Структура и группы/Удалить структуру.

Таблица 8.

Таблица 9.

Таблица 10.

Таблица 11.

Таблица 12.

Таблица 13.

Таблица 14.

Таблица 15.

Таблица 16.

Таблица 17.

Таблица 18.

Таблица 19.

Таблица 20.

Таблица 21.

ВАРИАНТЫ ЗАДАНИЙ

1. Акт поставки продовольственных товаров:

№ п/п	Наименование товаров	Единица измерения	Количество единиц	Цена за единицу	Итого
	Молоко
	Масло сливочное
	Маргарин
	Яйцо
	Рыба
	Мясо

Составить акты поставки товаров в магазины «Береке», «Айдос» и «Южный» на трех разных листах, на четвертом листе подсчитать итоговые данные по поставке товаров в три магазина. На пятом листе выполнить консолидацию данных.

1. Ведомость начислений и удержаний:

Табельный номер	Ф.И.О.	Должность	Сумма начислений	Сумма удержаний	Итого к выдаче
	Иванов И.И.
	Антонов А.А.
	Петров П.П.
	Козлов К.К.
	Девятов Д.Д.
	Баранов Б.Б.
	Легков Л.Л.

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

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

№ п/п	Наименование товара	Количество	Цена	Количество бракованных изделий	Сумма выручки
	Сапоги муж.
	Сапоги жен.
	Ботинки дет.
	Тапочки
	Туфли дет.

Составить ведомости реализации товаров за 1, 2, 3 и 4 квартал на четырех разных листах, на пятом листе подсчитать итоговые данные за год. На шестом листе выполнить консолидацию данных.

1. Ведомость заработной платы:

№ п/п	Ф.И.О.	Должность	Тарифная ставка	Количество отработанных дней	Отчисления в ПФ	Сумма к выплате

Составить ведомость з/платы за январь, февраль, март, апрель на четырех разных листах, на пятом листе подсчитать сумму з/платы за 1 квартал. На шестом листе выполнить консолидацию данных.

1. Ведомость реализации хлебобулочных изделий:

№ п/п	Наименование изделий	Количество Произведенной продукции	Реализовано (за вычетом спонсорской помощи)	Цена	Сумма выручки

1/5 часть произведенной продукции разного вида отправляется в качестве спонсорской помощи в детские дома.

Составить ведомость реализации хлебобулочных изделий в магазинах №1, 2, 3 на трех разных листах, на четвертом листе подсчитать общую сумму реализованной продукции трех магазинов. На пятом листе выполнить консолидацию данных.

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

Дата добавления: 2015-07-20