З адание 2. Сводные т аблицы.

 

1. Наберите в табличном процессоре Excel исходный список (таблицу)

 

объемов продаж фирмы «Гигант».

 

Задайте первому рабочему листу имя «Исходная».

 

При создании списка сначала наберите заголовки столбцов, затем за-

 

дайте форматы ячеек: для столбцов Покупатель, Товар, Ед. изм., –

 

общий, Количество – числовой целый, Цена за ед., Сумма, Сумма с

 

НДС – денежный с двумя десятичными знаками, НДС, % – процент-

 

ный, Дата сделки – Дата.

 

Введите первую запись и задайте формулы для вычисляемых ячеек

 

(столбцы Сумма и Сумма с НДС).

 

Создайте форму и введите остальные записи таблицы. В п о л е НДС

 

формы вводите значение 0,25, Excel сам переведет это значение в %.

 

Объемы продаж фирмы «Гигант»

Покупатель Товар

 

Атлант       Бананы

Е д.     Кол- Цена з а Сумма, НДС измер.   во      ед.,руб.   руб.        %

кг       25         12                 25

С умма Дата

с НДС сделки 02.11.07

 

Реверс        Бананы      кг       30        12 Атлант         Консервы  банка      20       15,5 Метеор         Консервы  банка      50         8,3 Атлант         Консервы  банка      20         7,5 Реверс          Консервы  банка      60         8,7 Метеор         Конфеты     шт.        30          45 Реверс          Конфеты      кг         25       10,5 Метеор         Конфеты      кг         40       12,5 Реверс          Яблоки        кг         50          10 Метеор         Яблоки        кг         50            8 Атлант         Яблоки        кг         10       11,5 Атлант         Конфеты      кг         25          40 Реверс          Бананы        кг         30          12 Атлант         Конфеты      кг         20          30 Метеор         Консервы  банка      20         8,3 Атлант         Консервы  банка      40          12 Реверс          Консервы  банка      30         8,7 Метеор         Конфеты     шт.        10          45 Реверс          Консервы  банка      25            5 Метеор         Бананы        кг         40       12,5 Реверс          Конфеты      кг         50          25 Метеор         Яблоки        кг         50            8 Атлант         Яблоки        кг       100       11,5

25                02.11.07 25                   01.11.07 25                   02.11.07 25                   05.11.07 25                   05.11.07 25                   01.11.07 25                   01.11.07 25                   05.11.07 25                   01.11.07 25                   04.11.07 25                   07.11.07 25                   08.11.07 25                   08.11.07 25                   07.11.07 25                   09.11.07 25                   08.11.07 25                   05.11.07 25                   08.11.07 25                   08.11.07 25                   07.11.07 25                   09.11.07 25                   08.11.07 25                   05.11.07

 

 

122

 

 

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.

 

В результате должна получиться следующая сводная таблица:

 

123

 

 

11. Проведите группировку данных по периодам.

 

 

Создайте заголовок таблицы Итоги п о п ериодам, отформатируйте

 

его.

 

12. Постройте сводную таблицу, в которой указан процент количества каж-

 

дого типа проданного товара от общего числа.

 

З адание 3. Анализ эффективности рекламной компании с помощью

 

«Таблицы данных».

 

В рабочем листе вычисляется чистая прибыль после продажи рек-

 

ламируемого товара как функция двух входных параметров: количества

 

разосланных рекламных материалов и предполагаемого процента ответов.

 

1. Введите текст в ячейку А1 – «Анализ эффективности почтовой рек-

 

ламы», в А3 – «Входные данные» и объедините ячейки А1:В1 и

 

А3:В3.

 

2. Введите текст в ячейки в соответствии с таблицей 124

 

Ячейка         Что вводить А4        Разослано материалов А5        Процент ответов

Ячейка           Что вводить А10      Доход на один заказ А11      Суммарный доход

 

А7      Стоимость печатных ма- А12    Суммарные расходы А8        териалов                             А13      Чистая прибыль

А9      Почтовые расходы Число заказов

 

 

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. Суммарные расходы состоят из стоимости печатных материа-

 

лов и почтовых услуг: = Разослано материалов * (Стоимость

 

печатных материалов + почтовые расходы)

 

125

 

 

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.Измените значения в ячейках Доход_на_один_заказ и Почто-

 

вые_расходы. Может оказаться, что все значения в таблице подста-

 

новки при этом станут отрицательными. Увеличьте предполагаемый

 

процент ответов, количество разосланных материалов, либо снизить

 

расходы           на          печать.           Поэкспериментируйте.

 

 

1