Требуется вычислить суммарный объем продаж и количество программ от различных фирм-производителей, реализованных всеми продавцами обеих фирм ("Юпитер" и "Сатурн").
Для решения этой задачи используйте консолидацию данных по категориям. Таблицу с итоговыми (консолидированными) данными разместите на рабочем листе Консолидация.
Для консолидации данных, находящихся на рабочих листах Юпитер и Сатурн, выполните следующую последовательность действий:
1. Сделайте активным рабочий лист Консолидация и укажите ячейку А1 - левый верхний угол области вставки консолидированных данных.
2. Введите команду меню ДАННЫЕÞКонсолидация.
3. В диалоговом окне Консолидация из раскрывающегося списка Функция выберите функцию Сумма.
4. Щелкните мышью в поле Ссылка, перейдите на рабочий лист Юпитер и укажите диапазон ячеек D4:G22 - первый диапазон, данные из которого должны быть консолидированы. При выделении диапазонов заголовки столбцов и строк (метки) должны быть включены в области-источники.
Для того чтобы диалоговое окно не мешало выделению нужных областей, его можно переместить или нажать кнопку со стрелкой в правой части поля Ссылка. Для восстановления исходных размеров диалогового окна нужно снова нажать кнопку со стрелкой в поле Ссылка.
5. Щелкните по кнопке Добавить для включения выбранного диапазона в поле Список диапазонов.
6. Щелкните мышью в поле Ссылка, перейдите на рабочий лист Сатурн и укажите второй диапазон консолидируемых данных, включающий метки столбцов и названия фирм-производителей - D4:G13.
7. Щелкните по кнопке Добавить.
8. Установите переключатели:
• Использовать в качестве имен: подписи верхней строки и значения левого столбца;
• Создавать связи с исходными данными.
Установка переключателя Использовать в качестве имен значения левого столбца позволяет просуммировать значения в строках с одинаковыми метками - названиями фирм-производителей программных продуктов, даже если эти метки расположены в несмежных областях.
9. Щелкните по кнопке ОК для выполнения консолидации данных из областей, указанных в поле Список диапазонов. Откорректируйте в таблице с консолидированными данными ширину столбцов так, чтобы был виден текст всех заголовков столбцов.
Удалите столбец с меткой Цена, $.
Обратите внимание на структуру таблицы, появившуюся на листе Консолидация. Откройте все уровни структуры. Сверните структуру документа щелчком по номеру уровня 1.
Используя кнопку Автосумма, вычислите общий объем продаж и количество программ, реализованных на обеих фирмах -"Юпитер" и "Сатурн".
Проанализируйте полученные результаты.
Сохраните работу.
Отформатируйте таблицу с консолидированными данными рамками и заливкой, выделите метки столбцов и строк полужирным шрифтом.
Измените какие-либо исходные данные и убедитесь в том, что эти изменения нашли отражение в итоговой таблице.
Постройте диаграмму на основе таблицы с консолидированными данными.
Откройте в таблице одну из групп детальных данных и обратите внимание на изменение диаграммы.
Сохраните работу.
Задание 7. Создание и форматирование прайс-листа
В рабочей книге Itogy.хls создайте еще один рабочий лист с именем Цены.
Скопируйте на лист Цены таблицу с листа Сатурн и создайте прайс-лист фирмы "Сатурн" по приведенному ниже образцу (рисунок).
Сегодняшняя дата Курс у. е.
Программа | Фирма | Цена, у. е. | Цена, руб. | |
1 | ||||
2 |
Прейскурант фирмы "Сатурн"
Выполните необходимые расчеты и отформатируйте таблицу.
Создайте новый документ Word и сохраните его под именем Прайс-лист, doc.
Выведите на экран одновременно оба документа: Itogy.xls и Прайс-лист.doc и упорядочите их расположение на экране сверху вниз.
На рабочем листе Цены выделите необходимые данные и перетащите их с помощью мыши в окно документа Word, удерживая одновременно нажатую клавишу Ctrl.
Сохраните и закройте оба документа.
Дополнительное задание. Создание сводной таблицы на основе базы данных Excel
В рабочей книге Itogy.хls создайте новый рабочий лист с именем Сводные и скопируйте на него рабочий лист Итоги.
Удалите структуру таблицы с помощью команды ДАННЫЕÞГруппа и структураÞУдалить структуру.
Удалите все строки с итоговыми данными.
Определите с помощью сводной таблицы эффективность работы каждого продавца. Для этого установите курсор внутри таблицы, введите команду ДАННЫЕÞСводная таблица и используйте инструкции Мастера сводных таблиц.
На третьем шаге Мастера сводных таблиц для создания макета сводной таблицы перетащите с помощью мыши в область построения Строка кнопку Продавец, а в область Данные - кнопки полей, по которым будут производиться вычисления, - кнопку Количество и две кнопки Объем продаж. Вторая кнопка Объем продаж нужна для выполнения дополнительных вычислений.
Для выполнения дополнительных вычислений в третьем окне (шаге) Мастера сводных таблиц сделайте двойной щелчок по кнопке Сумма по полю Объем продаж2, в диалоговом окне Вычисление поля сводной таблицы щелкните по кнопке Дополнительно>>, а затем в списке Дополнительные вычисления выберите строку Доля от суммы по столбцу.
В четвертом окне Мастера сводных таблиц установите переключатель Новый лист и щелкните по кнопке Готово.
Сводная таблица должна иметь вид, приведенный на рисунке.
Продавец | Данные | Всего |
| Сумма по полю Количество | 108 |
Сумма по полю Объем продаж | 34665 | |
Сумма по полю Объем продаж2 | 44% | |
| Сумма по полю Количество | 140 |
Сумма по полю Объем продаж | 44214 | |
Сумма по полю Объем продаж2 | 56% | |
Итог Сумма по полю Количество | 248 | |
Итог сумма по полю Объем продаж | 78879 | |
Итог Сумма по полю Объем продаж2 | 100% |
Сводная таблица
|
Из за большого объема этот материал размещен на нескольких страницах:
1 2 3 4 5 6 |
