Задание 1. Консолидация данных.
Создайте таблицу по образцу на листе Исходный: Фирма «Юпитер»
2. Создайте два новых рабочих листа и присвойте им имена Итоги и Вычисления.
3. Скопируйте рабочий лист Исходный на лист Итоги и выполните вычисления в столбце Объем продаж.
4. На листе Итоги требуется вычислить суммарное количество программ, реализованных каждым продавцом, объем продаж для каждого продавца и подвести итоги в целом по фирме.
5. Определите эффективность работы каждого продавца, его вклад в общий объем продаж. Для этого в итоговую таблицу на листе Вычисления добавьте заголовок нового столбца Н – Доля, % и введите формулу для расчета процентной доли каждого продавца в общем объеме продаж.
6. На основе таблицы с итоговыми данными постройте объемную столбчатую диаграмму, используя столбцы «Продавец» и «Доля, % ».
7. Создайте три новых рабочих листа и присвойте им имена Юпитер, Сатурн и Консолидация.
1. Скопируйте рабочий лист Исходный на лист Юпитер и выполните вычисления в столбце Объем продаж.
2. Скопируйте данные рабочего листа Юпитер на лист Сатурн. Откорректируйте данные на рабочем листе Сатурн:
измените название фирмы;
так как на фирме "Сатурн" реализацией программных продуктов занимается только один человек, удалите в таблице строки, относящиеся к одному из продавцов, измените фамилию продавца и некоторые данные в столбце Количество.
3. Используя консолидацию, вычислить суммарный объем продаж и количество программ от различных фирм-производителей, реализованных всеми продавцами обеих фирм ("Юпитер" и "Сатурн"). Таблицу с итоговыми (консолидированными) данными разместите на рабочем листе Консолидация.
4. Откорректируйте в таблице с консолидированными данными ширину столбцов так, чтобы был виден текст всех заголовков столбцов.
5. Удалите столбец с меткой Цена, $.
6. Используя кнопку Автосумма, вычислите общий объем продаж и количество программ, реализованных на обеих фирмах – "Юпитер" и "Сатурн".
7. Проанализируйте полученные результаты.
8. Отформатируйте таблицу с консолидированными данными рамками и заливкой, выделите метки столбцов и строк полужирным шрифтом.
9. Постройте диаграмму на основе итоговой таблицы.
Задание 2. Сводные таблицы.
1. Наберите в табличном процессоре Excel исходный список (таблицу) объемов продаж фирмы «Гигант».
Задайте первому рабочему листу имя «Исходная».
При создании списка сначала наберите заголовки столбцов, затем задайте форматы ячеек: для столбцов Покупатель, Товар, Ед. изм., – общий, Количество – числовой целый, Цена за ед., Сумма, Сумма с НДС – денежный с двумя десятичными знаками, НДС, % – процентный, Дата сделки – Дата.
Введите первую запись и задайте формулы для вычисляемых ячеек (столбцы Сумма и Сумма с НДС).
Создайте форму и введите остальные записи таблицы. В поле НДС формы вводите значение 0,25, Excel сам переведет это значение в %.
2. Подсчитайте итоговые суммы по столбцам Сумма и Сумма с НДС.
3. Создайте заголовок таблицы Объемы продаж фирмы «Гигант», отформатируйте заголовок.
4. Скопируйте таблицу с заголовком и без итоговых цифр по столбцам Сумма и Сумма с НДС на второй рабочий лист. Выполните сортировку данных по возрастанию по столбцам Покупатель и Товар.
5. Подсчитайте промежуточные итоги и общий итог продаж по покупателям.
6. Задайте имя Покупатели рабочему листу. Создайте заголовок таблицы «Данные по покупателям», отформатируйте заголовок.
7. Выполните фильтрацию данных по товару Конфеты и дате сделки с 02.11.07 по 08.11.07 .
8. На основе исходной таблицы создайте сводную таблицу вида:
9. Задайте рабочему листу имя Покупатели. Создайте заголовок таблицы Данные по покупателям, отформатируйте заголовок.
10. Составьте сводную таблицу для получения объемов продаж с 01.11.2007 по 02.11.07, 03.11.2007 по 04.11.07, с 05.11.2007 по 06.11.07, 06.11.2007 по 08.11.07.
В результате должна получиться следующая сводная таблица:
11. Проведите группировку данных по периодам.
Создайте заголовок таблицы Итоги по периодам, отформатируйте его.
12. Постройте сводную таблицу, в которой указан процент количества каждого типа проданного товара от общего числа.
Задание 3. Анализ эффективности рекламной компании с помощью «Таблицы данных».
В рабочем листе вычисляется чистая прибыль после продажи рекламируемого товара как функция двух входных параметров: количества разосланных рекламных материалов и предполагаемого процента ответов.
1. Введите текст в ячейку А1 – «Анализ эффективности почтовой рекламы», в А3 – «Входные данные» и объедините ячейки А1:В1 и А3:В3.
2. Введите текст в ячейки в соответствии с таблицей
3. Задайте имена ячеек В4, В5, В7, В8, В9, В10, В11, В12, В13 в соответствии с текстом, находящимся в соседних ячейках столбца А (Ре- жим Формулы Создать из выделенного фрагмента).
4. Введите необходимые числовые значения и расчетные формулы:
a. Стоимость печатных материалов (изменяется в зависимости от количества): 0,30 руб. – если количество экземпляров не более 200000; 0,20 руб. – от 200001 до 300000 экземпляров; 0,10 руб. – если больше 300000.
b. Почтовые расходы – их стоимость фиксирована и составляет 1,2 руб. за одно почтовое отправление.
c. Число заказов, т.е. количество ответов, которые предполагается получить, определяется в зависимости от процента предполагаемых ответов и от количества разосланных материалов по формуле: = Процент ответов * Число заказов. (в формулах нужно использовать присвоенные ячейкам имена).
d. Доход на один заказ – число фиксированное, т.е. компании известно, что за каждый заказ она получит прибыль 55 рублей.
e. Суммарный доход вычисляется по формуле: = Доход на один заказ * Число заказов.
f. Суммарные расходы состоят из стоимости печатных материалов и почтовых услуг: = Разослано материалов * (Стоимость печатных материалов + почтовые расходы)
g. Чистая прибыль определяется как разница суммарных доходов и суммарных расходов.
5. В диапазон ячеек В15:Н15 введите значения для переменной Процент_ответов: от 2,00% до 3,25% с шагом 0,25%.
6. В диапазон ячеек А16:А25 введите значения для переменной Разослано_материалов от 100 000 до 325 000 с шагом 25 000.
7. В ячейку А15 введите формулу для вычисления чистой прибыли.
8. Создайте таблицу подстановки. Для этого выделите диапазон А15:Н25 и выполните команду Данные – Анализ «Что-Если»–таблица данных. В диалоговом окне в поле Подставлять значения по столбцам введите имя Процент_ответов, а в поле Подставлять значения по строкам – Разослано_материалов.
9. По полученным данным постройте трехмерные гистограммы, представляющие эффективность компании.
10. Измените значения в ячейках Доход_на_один_заказ и Почтовые_расходы. Может оказаться, что все значения в таблице подстановки при этом станут отрицательными. Увеличьте предполагаемый процент ответов, количество разосланных материалов, либо снизить расходы на печать. Поэкспериментируйте