Бухгалтерия, работа в excel

Исходные данные (задание вариант 50)

1. На листе Справочник создать таблицы. Значения транзитной нормы и тарифа в отдельных именованных ячейках.
Тарифное расстояние, км Процент тарифа Транзитная норма (объем, л) 300
0 0% Тариф 500,00 ₽
50 25%
100 50%
200 75%
300 100%

2. На листе Грузооборот оформить таблицу. Доставка и Цена задать денежный формат.
Подготовить именованные блоки на листе Справочники для ввода данных на лист Грузооборот.
Заполнить поле Тарифное расстояние в виде списка.
Цена продукции с учетом расходов транспортировки
Себестоимость продукции 15,40 ₽

Грузополучатель Тарифное расстояние, км Потребность (объем продукции), л Грузооборот Количество перевозок Доставка, руб. Цена, руб.
П1 0 2100
П2 300 2000
П3 300 2250
П4 300 2000
П5 100 2100
П6 100 2400
П7 50 2400
П8 100 1500
П9 50 2000
П10 0 2000

3. В таблице выполнить вычисления:
 Грузооборот = Тарифное расстояние * Потребность.
 Кол-во перевозок = Потребность / Транзитная норма. В формуле применить функцию
 Доставка =Процент тарифа * Тариф * Кол-во перевозок;
Процент тарифа, соответствующий тарифному расстоянию, получить с помощью функции ВПР.
 Цена = Себестоимость + Доставка / Потребность.
4. На листе Итоги получить по тарифным расстояниям общую потребность в продукции и общую сумму доставки.
5. По результатам итогов построить круговую диаграмму, отражающую потребность в продукции по тарифным расстояниям, включить в подписи имена рядов и значения.
6. На листе Фильтр с помощью расширенного фильтра вывести строки, в которых тарифное рас-стояние не ноль и потребность больше, чем Кол-во перевозок * Транзитная норма (потребность в продукции не обеспечена).
7. Построить Сводную таблицу, вывести по тарифному расстоянию суммарный грузооборот и количество грузополучателей.
8. Сохранить книгу под именем Грузооборот.
9. Средствами Word по результатам фильтрации подготовить и оформить рассылку информации грузополучателям о необходимости корректировки заказа.
 
Пошаговое выполнение задания

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

Рис. 1. Заполненные таблицы с исходными данными

2. На листе Грузооборот оформить таблицу. Доставка и Цена задать денежный формат. Подготовить именованные блоки на листе Справочники для ввода данных на лист Грузооборот.
Заполнить поле Тарифное расстояние в виде списка.
Для того чтобы заполнить поле в виде списка, в меню «Данные» выбирается строка Проверка данных, в закладке «параметры» выделяется тип данных «список», ниже в «Источник» вводится диапазон, например выделяется одноименный столбец с листа «Справочник» (рис. 2).

Рис. 2. Заполнение поля в виде списка

В итоге оформленная таблица представлена на рис. 3.

Рис. 3. Таблица в MS Excel

3. В таблице выполнить вычисления:
 Грузооборот = Тарифное расстояние * Потребность.
В данном случае, D5 =C5*B5
Растянем формулу вниз.
 Кол-во перевозок = Потребность / Транзитная норма. В формуле применить функцию
ОКРУГЛВНИЗ для округления частного до целого.
В данном случае, Е5 =ОКРУГЛВНИЗ(C5/Справочник!$E$1;0)
Растянем формулу вниз.
 Доставка =Процент тарифа * Тариф * Кол-во перевозок;
Процент тарифа, соответствующий тарифному расстоянию, получить с помощью функции ВПР.
В данном случае,
F5 =ВПР(B5;Справочник!$A$2:$B$6;2;ЛОЖЬ)*Справочник!$E$2* E5
Растянем формулу вниз.
 Цена = Себестоимость + Доставка / Потребность.
В данном случае, G5 =$C$2+F5/C5
Растянем формулу вниз.
В итоге получим таблицу как на рис. 4.

Рис. 4. Заполненная таблица в MS Excel
4. На листе Итоги получить по тарифным расстояниям общую потребность в продукции и общую сумму доставки.
Оформим таблицу следующего вида как на рис. 5.

Рис. 5. Таблица для заполнения на листе «Итоги»

В данном случае, воспользуемся функцией СУММЕСЛИ()
Ячейка В2
=СУММЕСЛИ(Грузооборот!$B$5:$B$14; A2;Грузооборот!$C$5:$C$14)
Ячейка С2
=СУММЕСЛИ(Грузооборот!$B$5:$B$14; A2;Грузооборот!$F$5:$F$14)
В итоге получим таблицу как на рис. 6.

Рис. 6. Заполненная таблица на листе «Итоги»
5. По результатам итогов построить круговую диаграмму, отражающую потребность в продукции по тарифным расстояниям, включить в подписи имена рядов и значения.
Для построения диаграммы в меню «Вставка» выберем пункт «круговая диаграмма» и выберем такую диаграмму, как на рис. 7.

Рис. 7. Диаграмма потребности в продукции по тарифным расстояниям

6. На листе Фильтр с помощью расширенного фильтра вывести строки, в которых тарифное расстояние не ноль и потребность больше, чем Кол-во перевозок * Транзитная норма (потребность в продукции не обеспечена).
Создадим таблицу с условиями отбора. Для этого копируем заголовки исходного списка и вставляем выше. В табличке с критериями для фильтрации оставляем достаточное количество строк плюс пустая строка, отделяющая от исходной таблицы.
Настроим параметры фильтрации для отбора строк.
В2 «>0»
С3 =C5>E5*300
Результаты настроек отображены на рис. 8.