Постановка задачи Имеется табл 1 данных о поступлении и расходе товаров на предприятии

Постановка задачи
Имеется табл. 1 данных о поступлении и расходе товаров на предприятии.
Таблица 1
Подразделение Наименование товара Остаток на начало года Поступило в течение года Израсходовано на рекламу Израсходовано за год на печать Израсходовано за год на прочие расходы

1. Сформировать табл. 2 «Ведомость о движении товаров».
Таблица 2
Подразделение Наименование товара

Остаток на начало года Поступило в течение года

Израсходовано за год на рекламу

Израсходовано за год на печать

Израсходовано за год на прочие расходы

Общие
расходы за
год

Остаток на конец года

1 2 3 4 5 6 7 8 9

Выходной документ должен содержать 15 — 20 записей (3 — 5 подразделений, в каждом подразделении по 3 — 5 наименований товаров).
Расчет данных в графах 8, 9 в каждой строке табл. 2 осуществляется в соответствии со следующей схемой (в квадратных скобках указаны порядковые номера граф):
[8]=[5]+[6]+[7]
[9]=[3]+[4]- [8].
2. С помощью фильтрации записей получить следующую информацию:
а) наименование товара, который имеет наибольший остаток на начало года;
б) наименование товаров, остаток которых на конец года находится в интервале от 10 до 50 включительно;
в) записи о товарах, которых израсходовали на рекламу больше, чем на печать.
3. Вычислить в таблице 2 итоговые данные по каждому подразделению и общие итоги по предприятию в графах 3, 4, 8, 9.
4. Построить столбиковую диаграмму остатков товаров на начало и конец года по одному подразделению (любому)
5. Построить круговую диаграмму суммарных остатков товаров на конец года по всем подразделениям предприятия.

Исходные данные
Исходные данные приведены в табл. 1.
Таблица 1
Подразделение Наименование товара Остаток на начало года Поступило в течение года Израсходовано на рекламу Израсходовано за год на печать Израсходовано за год на прочие расходы
Магазин Канцтовары Ручка гелевая (черная) 1000,00 2000,00 100,00 100,00 250,00
Магазин Эконом Ручка гелевая (синяя) 1000,00 2000,00 150,00 100,00 250,00
Оптовый склад Набор фломастеров (20 цветов) 500,00 600,00 100,00 100,00 250,00
Магазин Канцтовары Тетрадка (в клетку) 12 листов 1000,00 2000,00 100,00 100,00 250,00
Магазин Эконом Блокнот 500,00 1000,00 100,00 100,00 250,00
Магазин Эконом Карандаш простой 1000,00 2000,00 100,00 100,00 250,00
Магазин Канцтовары Тетрадка (в линейку) 36 листов 1000,00 2000,00 200,00 100,00 250,00
Магазин Канцтовары Тетрадка (в клетку) 36 листов 1000,00 2000,00 100,00 100,00 250,00
Оптовый склад Скотч широкий (прозрачный) 300,00 1000,00 100,00 100,00 250,00
Оптовый склад Альбом для рисования 10 листов 300,00 300,00 100,00 100,00 250,00
Магазин Эконом Краски акварель 10 цветов 200,00 400,00 100,00 210,00 250,00
Магазин Эконом Кисточка для рисования 200,00 500,00 100,00 330,00 250,00
Магазин Канцтовары Тетрадка (в линейку) 12 листов 1000,00 2000,00 100,00 100,00 250,00
Оптовый склад Книжка раскраска для детей 100,00 200,00 60,00 50,00 150,00
Оптовый склад Скотч (двухсторонний желтый) 300,00 1000,00 100,00 100,00 250,00

Исходные данные в отсортированном виде приведены в табл. 1.1.
Таблица 1.1
Подразделение Наименование товара Остаток на начало года Поступило в течение года Израсходовано на рекламу Израсходовано за год на печать Израсходовано за год на прочие расходы
Магазин Канцтовары Ручка гелевая (черная) 1000,00 2000,00 100,00 100,00 250,00
Магазин Канцтовары Тетрадка (в клетку) 12 листов 1000,00 2000,00 100,00 100,00 250,00
Магазин Канцтовары Тетрадка (в линейку) 36 листов 1000,00 2000,00 200,00 100,00 250,00
Магазин Канцтовары Тетрадка (в клетку) 36 листов 1000,00 2000,00 100,00 100,00 250,00
Магазин Канцтовары Тетрадка (в линейку) 12 листов 1000,00 2000,00 100,00 100,00 250,00
Магазин Эконом Ручка гелевая (синяя) 1000,00 2000,00 150,00 100,00 250,00
Магазин Эконом Блокнот 500,00 1000,00 100,00 100,00 250,00
Магазин Эконом Карандаш простой 1000,00 2000,00 100,00 100,00 250,00
Магазин Эконом Краски акварель 10 цветов 200,00 400,00 100,00 210,00 250,00
Магазин Эконом Кисточка для рисования 200,00 500,00 100,00 330,00 250,00
Оптовый склад Набор фломастеров (20 цветов) 500,00 600,00 100,00 100,00 250,00
Оптовый склад Скотч широкий (прозрачный) 300,00 1000,00 100,00 100,00 250,00
Оптовый склад Альбом для рисования 10 листов 300,00 300,00 100,00 100,00 250,00
Оптовый склад Книжка раскраска для детей 100,00 200,00 60,00 50,00 150,00
Оптовый склад Скотч (двухсторонний желтый) 300,00 1000,00 100,00 100,00 250,00

Таблица с расчетами
Таблица с расчетными данными представлена в табл.2.
Таблица 2
Ведомость о движении товаров
Подразделение Наименование товара

Остаток на начало года Поступило в течение года

Израсходовано за год на рекламу

Израсходовано за год на печать

Израсходовано за год на прочие расходы

Общие
расходы за
год

Остаток на конец года

Магазин Канцтовары Ручка гелевая (черная) 1000,00 2000,00 100,00 100,00 250,00 450,00 2550,00
Магазин Канцтовары Тетрадка (в клетку) 12 листов 1000,00 2000,00 100,00 100,00 250,00 450,00 2550,00
Магазин Канцтовары Тетрадка (в линейку) 36 листов 1000,00 2000,00 200,00 100,00 250,00 550,00 2450,00
Магазин Канцтовары Тетрадка (в клетку) 36 листов 1000,00 2000,00 100,00 100,00 250,00 450,00 2550,00
Магазин Канцтовары Тетрадка (в линейку) 12 листов 1000,00 2000,00 100,00 100,00 250,00 450,00 2550,00
Магазин Эконом Ручка гелевая (синяя) 1000,00 2000,00 150,00 100,00 250,00 500,00 2500,00
Магазин Эконом Блокнот 500,00 1000,00 100,00 100,00 250,00 450,00 1050,00
Магазин Эконом Карандаш простой 1000,00 2000,00 100,00 100,00 250,00 450,00 2550,00
Магазин Эконом Краски акварель 10 цветов 200,00 400,00 100,00 210,00 250,00 560,00 40,00
Магазин Эконом Кисточка для рисования 200,00 500,00 100,00 330,00 250,00 680,00 20,00
Оптовый склад Набор фломастеров (20 цветов) 500,00 600,00 100,00 100,00 250,00 450,00 650,00
Оптовый склад Скотч широкий (прозрачный) 300,00 1000,00 100,00 100,00 250,00 450,00 850,00
Оптовый склад Альбом для рисования 10 листов 300,00 300,00 100,00 100,00 250,00 450,00 150,00
Оптовый склад Книжка раскраска для детей 100,00 200,00 60,00 50,00 150,00 260,00 40,00
Оптовый склад Скотч (двухсторонний желтый) 300,00 1000,00 100,00 100,00 250,00 450,00 850,00

Фильтрация записей
Для получения информации с помощью фильтрации записей воспользуемся инструментом Автофильтр.
Для этого выделим таблицу и выполним команду Данные, Фильтр, Автофильтр.
а) наименование товара, который имеет наибольший остаток на начало года;
Для получения наименование товара, который имеет наибольший остаток на начало года, из раскрывающегося списка поля Остаток на конец года выберем Первые 10. В окне диалога Наложение условия по списку установим необходимые параметры:

Выполним щелчок мышью по кнопке ОК. Получим результат:
Подразделение Наименование товара

Остаток на начало года Поступило в течение года

Израсходовано за год на рекламу

Израсходовано за год на печать

Израсходовано за год на прочие расходы

Общие
расходы за
год

Остаток на конец года

Магазин Канцтовары Ручка гелевая (черная) 1000,00 2000,00 100,00 100,00 250,00 450,00 2550,00
Магазин Канцтовары Тетрадка (в клетку) 12 листов 1000,00 2000,00 100,00 100,00 250,00 450,00 2550,00
Магазин Канцтовары Тетрадка (в клетку) 36 листов 1000,00 2000,00 100,00 100,00 250,00 450,00 2550,00
Магазин Канцтовары Тетрадка (в линейку) 12 листов 1000,00 2000,00 100,00 100,00 250,00 450,00 2550,00
Магазин Эконом Карандаш простой 1000,00 2000,00 100,00 100,00 250,00 450,00 2550,00

Для отображения всех записей таблицы из раскрывающегося списка поля Остаток на конец года выберем Все.
б) наименование товаров, остаток которых на конец года находится в интервале от 10 до 50 включительно;
Для получения наименования товаров, остаток которых на конец года находится в интервале от 10 до 50 включительно из раскрывающегося списка поля Остаток на конец года выберем Условие. В окне диалога Пользовательский автофильтр установим необходимые параметры:

Выполним щелчок мышью по кнопке ОК. Получим результат.
Подразделение Наименование товара

Остаток на начало года Поступило в течение года

Израсходовано за год на рекламу

Израсходовано за год на печать

Израсходовано за год на прочие расходы

Общие
расходы за
год

Остаток на конец года

Магазин Эконом Краски акварель 10 цветов 200,00 400,00 100,00 210,00 250,00 560,00 40,00
Магазин Эконом Кисточка для рисования 200,00 500,00 100,00 330,00 250,00 680,00 20,00
Оптовый склад Книжка раскраска для детей 100,00 200,00 60,00 50,00 150,00 260,00 40,00

Для отображения всех записей таблицы из раскрывающегося списка поля Остаток на конец года выберем Все.
в) записи о товарах, которых израсходовали на рекламу больше, чем на печать.
Для получения записей о товарах, которых израсходовали на рекламу больше, чем на печать, создадим правило, согласно которому, будут выделены цветом ячейки, в которых выполняется данное условие. Выделим ячейки Е4:У18, на вкладке Главная выбираем Условное форматирование, Создать правило

Из раскрывающегося списка Израсходовано за год на рекламу выберем условие Фильтры по цвету

Получим результат.
Подразделение Наименование товара

Остаток на начало года Поступило в течение года

Израсходовано за год на рекламу

Израсходовано за год на печать

Израсходовано за год на прочие расходы

Общие
расходы за
год

Остаток на конец года

Магазин Канцтовары Тетрадка (в линейку) 36 листов 1000,00 2000,00 200,00 100,00 250,00 550,00 2450,00
Магазин Эконом Ручка гелевая (синяя) 1000,00 2000,00 150,00 100,00 250,00 500,00 2500,00

Для отображения всех записей таблицы из раскрывающегося списка поля Остаток на конец года выберем Все.

Расчет итогов
Таблица с расчетами итогов представлена в табл. 2.1.
Таблица 2.1
Ведомость о движении товаров
Подразделение Наименование товара Остаток на начало года Поступило в течение года Израсходовано за год на рекламу Израсходовано за год на печать Израсходовано за год на прочие расходы Общие расходы за год Остаток на конец года
Магазин Канцтовары Ручка гелевая (черная) 1000,00 2000,00 100,00 100,00 250,00 450,00 2550,00
Магазин Канцтовары Тетрадка (в клетку) 12 листов 1000,00 2000,00 100,00 100,00 250,00 450,00 2550,00
Магазин Канцтовары Тетрадка (в линейку) 36 листов 1000,00 2000,00 200,00 100,00 250,00 550,00 2450,00
Магазин Канцтовары Тетрадка (в клетку) 36 листов 1000,00 2000,00 100,00 100,00 250,00 450,00 2550,00
Магазин Канцтовары Тетрадка (в линейку) 12 листов 1000,00 2000,00 100,00 100,00 250,00 450,00 2550,00
Магазин Канцтовары Итог 5000,00 10000,00       2350,00 12650,00
Магазин Эконом Ручка гелевая (синяя) 1000,00 2000,00 150,00 100,00 250,00 500,00 2500,00
Магазин Эконом Блокнот 500,00 1000,00 100,00 100,00 250,00 450,00 1050,00
Магазин Эконом Карандаш простой 1000,00 2000,00 100,00 100,00 250,00 450,00 2550,00
Магазин Эконом Краски акварель 10 цветов 200,00 400,00 100,00 210,00 250,00 560,00 40,00
Магазин Эконом Кисточка для рисования 200,00 500,00 100,00 330,00 250,00 680,00 20,00
Магазин Эконом Итог 2900,00 5900,00       2640,00 6160,00
Оптовый склад Набор фломастеров (20 цветов) 500,00 600,00 100,00 100,00 250,00 450,00 650,00
Оптовый склад Скотч широкий (прозрачный) 300,00 1000,00 100,00 100,00 250,00 450,00 850,00
Оптовый склад Альбом для рисования 10 листов 300,00 300,00 100,00 100,00 250,00 450,00 150,00
Оптовый склад Книжка раскраска для детей 100,00 200,00 60,00 50,00 150,00 260,00 40,00
Оптовый склад Скотч (двухсторонний желтый) 300,00 1000,00 100,00 100,00 250,00 450,00 850,00
Оптовый склад Итог
  1500,00 3100,00       2060,00 2540,00

Формульный вариант расчетов представлен в таблице 2.2.
Для отображения формул была выполнена команда Сервис, Параметры, на вкладке Вид в группе Параметры окна установлен флажок формулы.

Ведомость о движении товаров
Подразделение Наименование товара Остаток на начало года Поступило в течение года Израсходовано за год на рекламу Израсходовано за год на печать Израсходовано за год на прочие расходы Общие расходы за год Остаток на конец года
Магазин Канцтовары Ручка гелевая (черная) 1000 2000 100 100 250 =СУММ(E4+F4+G4) =СУММ(C4+D4-H4)
Магазин Канцтовары Тетрадка (в клетку) 12 листов 1000 2000 100 100 250 =СУММ(E5+F5+G5) =СУММ(C5+D5-H5)
Магазин Канцтовары Тетрадка (в линейку) 36 листов 1000 2000 200 100 250 =СУММ(E6+F6+G6) =СУММ(C6+D6-H6)
Магазин Канцтовары Тетрадка (в клетку) 36 листов 1000 2000 100 100 250 =СУММ(E7+F7+G7) =СУММ(C7+D7-H7)
Магазин Канцтовары Тетрадка (в линейку) 12 листов 1000 2000 100 100 250 =СУММ(E8+F8+G8) =СУММ(C8+D8-H8)
Магазин Канцтовары Итог
  =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C4:C8)
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;D4:D8)
      =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;H4:H8)
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;I4:I8)
Магазин Эконом Ручка гелевая (синяя) 1000 2000 150 100 250 =СУММ(E10+F10+G10) =СУММ(C10+D10-H10)
Магазин Эконом Блокнот 500 1000 100 100 250 =СУММ(E11+F11+G11) =СУММ(C11+D11-H11)
Магазин Эконом Карандаш простой 1000 2000 100 100 250 =СУММ(E12+F12+G12) =СУММ(C12+D12-H12)
Магазин Эконом Краски акварель 10 цветов 200 400 100 210 250 =СУММ(E13+F13+G13) =СУММ(C13+D13-H13)
Магазин Эконом Кисточка для рисования 200 500 100 330 250 =СУММ(E14+F14+G14) =СУММ(C14+D14-H14)
Магазин Эконом Итог
  =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C10:C14)
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;D10:D14)
      =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;H10:H14)
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;I10:I14)
Оптовый склад Набор фломастеров (20 цветов) 500 600 100 100 250 =СУММ(E16+F16+G16) =СУММ(C16+D16-H16)
Оптовый склад Скотч широкий (прозрачный) 300 1000 100 100 250 =СУММ(E17+F17+G17) =СУММ(C17+D17-H17)
Оптовый склад Альбом для рисования 10 листов 300 300 100 100 250 =СУММ(E18+F18+G18) =СУММ(C18+D18-H18)
Оптовый склад Книжка раскраска для детей 100 200 60 50 150 =СУММ(E19+F19+G19) =СУММ(C19+D19-H19)
Оптовый склад Скотч (двухсторонний желтый) 300 1000 100 100 250 =СУММ(E20+F20+G20) =СУММ(C20+D20-H20)
Оптовый склад Итог
  =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C16:C20)
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;D16:D20)
      =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;H16:H20)
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;I16:I20)

Диаграммы

Рейтинг
( Пока оценок нет )
Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

один + два =

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.

Adblock detector