Лабораторная работа № 10
"Пример создания структур и консолидации данных"
Порядок выполнения работы:
В качестве примера рассмотримзадачу списания деталей после ремонта судов на судоремонтном заводе. Пусть ремонтная бригада истратила материалы на ремонт четырех судов. Требуется создать консолидирующую таблицу средствами МS Ехсel 2007 для списания деталей за отчетный период для четырех судов, а затем для двух судов.
1. Запустить Мiсrоsоft Ехсеl 2007.
2. Создать чистую рабочую книгу и присвоить ей название "Создание консолидирующей таблицы".
3. Первый лист книги назвать "Бикин" и на нем создать таблицу "Списано по т/х Бикин":
- Для объединения ячеек необходимо выделить ячейки, вызвать контекстное меню правой кнопкой мыши (команда "Формат ячеек"), выбрать в диалоговом окне вкладку "Выравнивание" и включить отображение "Объединение ячеек".
- Нумерацию деталей произвести с помощью автозаполнения по арифметической прогрессии (на ленте вкладка "Главная", диалоговое окно "Редактирование", кнопка "Заполнить", опция "Прогрессия", шаг 1, прогрессия "Арифметическая", заполнение по столбцам)
- Графу "Всего" подсчитать с помощью математической функции СУММ (на ленте вкладка "Формулы", диалоговое окно "Библиотека функций", кнопка "Математические", функция "СУММ").
Пример из строки формул: =СУММ(C2:C9)
| Списано по тх"Бикин" (октябрь) | ||
| Втулка | ||
| Прокладка | ||
| Подшипник | ||
| Зубчатое колесо | ||
| Рейка | ||
| Болт | ||
| Шайба | ||
| Гайка | ||
| Всего |
4. Второй лист книги назвать "Амур" и на нем по аналогии создать таблицу "Списано по т/х Амур".
| Списано по тх "Амур" (октябрь) | ||
| Втулка | ||
| Прокладка | ||
| Подшипник | ||
| Зубчатое колесо | ||
| Рейка | ||
| Болт | ||
| Шайба | ||
| Гайка | ||
| Всего |
5. Третий лист книги назвать "Посьет" и на нем по аналогии создать таблицу "Списано по т/х Посьет".
| Списано по тх"Посьет" (октябрь) | ||
| Втулка | ||
| Прокладка | ||
| Подшипник | ||
| Зубчатое колесо | ||
| Рейка | ||
| Болт | ||
| Шайба | ||
| Гайка | ||
| Всего |
6. Четвертый лист книги назвать "Славянка" и на нем по аналогии создать таблицу "Списано по т/х Славянка".
| Списано по тх"Славянка" (октябрь) | ||
| Втулка | ||
| Прокладка | ||
| Подшипник | ||
| Зубчатое колесо | ||
| Рейка | ||
| Болт | ||
| Шайба | ||
| Гайка | ||
| Всего |
7. Пятый лист рабочей книги назвать "Консолидация".
8. На листе "Консолидация" подготовить макет консолидирующей таблицы "Списано по группе судов". Нумерацию деталей произвести с помощью автозаполнения по арифметической прогрессии (на ленте вкладка "Главная", диалоговое окно "Редактирование", кнопка "Заполнить", опция "Прогрессия", шаг 1, прогрессия "Арифметическая", заполнение по столбцам). Для объединения ячеек необходимо выделить ячейки, вызвать контекстное меню правой кнопкой мыши (команда "Формат ячеек"), выбрать в диалоговом окне вкладку "Выравнивание" и включить отображение "Объединение ячеек".
| Списано по группе судов | ||
9. Активировать верхнюю ячейку первого пустого столбца (там будут названия списываемых деталей), щелкнув на нее.
10. Выбрать на ленте вкладку "Данные", диалоговое окно "Работа с данными", кнопку "Консолидация".
11. В поле "Использовать в качестве имен" диалогового окна "Консолидация" выбрать щелчком мыши "значения левого столбца".
12. Установить курсор в поле "Ссылка".
13. Перейти в лист "Бикин". В таблице "Списано по т/х Бикин"выделить диапазон ячеек с названиями деталей и их количеством, включая графу "Всего".
14. Нажать кнопку Добавить, выбранный диапазон переместится в список диапазонов.
15. Установить курсор в поле "Ссылка" диалогового окна "Консолидация".
16. Перейти в лист "Амур". В таблице "Списано по т/х Амур"выделить диапазон ячеек с названиями деталей и их количеством, включая графу "Всего".
17. Нажать кнопку Добавить, выбранный диапазон переместится в список диапазонов.
18. Установить курсор в поле "Ссылка" диалогового окна "Консолидация".
19. Перейти в лист "Посьет". В таблице "Списано по т/х Посьет"выделить диапазон ячеек с названиями деталей и их количеством, включая графу "Всего".
20. Нажать кнопку Добавить, выбранный диапазон переместится в список диапазонов.
21. Установить курсор в поле "Ссылка" диалогового окна "Консолидация".
22. Перейти в лист "славянка". В таблице "Списано по т/х Славянка" выделить диапазон ячеек с названиями деталей и их количеством, включая графу "Всего".
23. Нажать кнопку Добавить, выбранный диапазон переместится в список диапазонов.
24. Нажать кнопку ОК.
25. Проверить и сохранить результаты работы. После всех манипуляций таблица консолидированных данных должна иметь вид:
| Списано по группе судов | ||
| Втулка | ||
| Прокладка | ||
| Подшипник | ||
| Зубчатое колесо | ||
| Рейка | ||
| Болт | ||
| Шайба | ||
| Гайка | ||
| Всего |
26. На листе "Консолидация" подготовить макет консолидирующей таблицы "Списано по двум судам". Нумерацию деталей произвести с помощью автозаполнения по арифметической прогрессии (на ленте вкладка "Главная", диалоговое окно "Редактирование", кнопка "Заполнить", опция "Прогрессия", шаг 1, прогрессия "Арифметическая", заполнение по столбцам). Для объединения ячеек необходимо выделить ячейки, вызвать контекстное меню правой кнопкой мыши (команда "Формат ячеек"), выбрать в диалоговом окне вкладку "Выравнивание" и включить отображение "Объединение ячеек".
| Списано по двум судам | ||
27. Активировать верхнюю ячейку первого пустого столбца (там будут названия списываемых деталей), щелкнув на нее.
28. Выбрать на ленте вкладку "Данные", диалоговое окно "Работа с данными", кнопку "Консолидация".
29. В списке диапазонов выделить диапазон для т/х "Амур", щелкнув на него, и нажать кнопку Удалить.
30. В списке диапазонов выделить диапазон для т/х "Бикин", щелкнув на него, и нажать кнопку Удалить.
31. Нажать кнопку ОК.
32. Проверить и сохранить результаты работы. После всех манипуляций вторая таблица консолидированных данных по двум оставшимся судам должна иметь вид:
| Списано по двум судам | ||
| Втулка | ||
| Прокладка | ||
| Подшипник | ||
| Зубчатое колесо | ||
| Рейка | ||
| Болт | ||
| Шайба | ||
| Гайка | ||
| Всего |
33. Шестой лист рабочей книги назвать "Структура".
34. На нем создать таблицу "Акт списания". Для объединения ячеек необходимо выделить ячейки, вызвать контекстное меню правой кнопкой мыши (команда "Формат ячеек"), выбрать в диалоговом окне вкладку "Выравнивание" и включить отображение "Объединение ячеек". Графы "Итого" и "Всего" подсчитать с помощью математической функции СУММ (на ленте вкладка "Формулы", диалоговое окно "Библиотека функций", кнопка "Математические", функция "СУММ").
| Акт списания | ||
| Тх | Втулка | |
| "Амур" | Прокладка | |
| "Амур" | Подшипник | |
| "Амур" | Зубчатое колесо | |
| "Амур" | Рейка | |
| Итого | ||
| "Посьет" | Втулка | |
| "Посьет" | Прокладка | |
| "Посьет" | Подшипник | |
| "Посьет" | Зубчатое колесо | |
| "Посьет" | Рейка | |
| Итого | ||
| "Славянка" | Втулка | |
| "Славянка" | Прокладка | |
| "Славянка" | Подшипник | |
| "Славянка" | Зубчатое колесо | |
| "Славянка" | Рейка | |
| Итого | ||
| "Бикин" | Втулка | |
| "Бикин" | Прокладка | |
| "Бикин" | Подшипник | |
| "Бикин" | Зубчатое колесо | |
| "Бикин" | Рейка | |
| Итого | ||
| Всего |
35. Выделить всю таблицу "Акт списания".
36. Выбрать на ленте вкладку "Данные", диалоговое окно "Структура", кнопку "Группировать", опцию "Создание структуры".
37. На листе должна получиться трехуровневая структура:
38. Сохранить работу, просмотреть и проверить результаты.
39. При необходимости удаления структуры необходимо выполнить следующие действия:
- Выбрать на ленте вкладку "Данные", диалоговое окно "Структура" и нажать кнопку "Разгруппировать".
- Из списка опций выбрать "Удалить структуру".
