Отчёт о заказах, содержащих конкретное блюдо

Девушка построила отчет о заказах с блюдом при помощи iiko

Раздел Практика / Автор / Апрель 3, 2017 / Комментариев 0

Нас часто просят построить отчёт в iiko, в котором будут все заказы с их полным содержимым, но только те заказы, в которых была какая-то конкретная позиция. Соответственно заказы, в которых этой позиции не было, не нужно отображать в отчёте. Решили поделиться с Вами и этим know-how, т.к. оказалось, что мало кто знает как это сделать. Нам понадобятся степлер, пластиковая бутылка и канцелярский нож, только iiko и MS Excel. В этом рецепте без Excel’я пока не обойтись.

Недавний пример. Как известно, с 31 марта 2017 г. продажа пива без применения ККТ стала вдруг запрещена. Некоторые наши заказчики в связи с этим решили узнать а что же они потеряют, если вообще пиво перестать продавать вместо того, чтобы купить ККТ. Есть ли смысл от подобной экономии? Наш ответ такой: Вы потеряете какую-то часть заказов, в которых пиво было основным блюдом, а остальные блюда сопутствующие (закуска, по-русски говоря). Но сколько же это в деньгах? Давайте посчитаем.
Далее пошаговый рецепт (и небольшая выдержка из урока Excel для рестораторов):

OLAP Отчёт по продажам в iiko

1. Строим OLAP Отчет по продажам за прошедшие полгода с интересующими полями и выгружаем в Excel в таком виде:


На что обратить внимание: Колонка Номер смены добавлена потому, что в этом предприятии в каждой кассовой смене нумерация заказов начинается с 1. А это значит, что если мы будем фильтровать только по номеру заказа, то существует большая вероятность, что в итоговый отчёт попадут и заказы без искомого блюда, но с точно таким же номером. Если в Вашем заведении нумерация заказов сквозная, то никакого другого признака кроме номера заказа Вам в отчёте не нужно.

Спойлер курса Excel в ресторане для начинающих

2. Для нашего усложнённого примера мы начнём с того, что каждому заказу средствами Excel присвоим уникальный номер, так называемый идентификатор, на основе пары номер смены + номер заказа. Открываем сохранённый OLAP отчет в Excel. И добавляем три столбца:


3. В первом столбце напротив первой ячейки с данными добавляем формулу:
Формула
=ЕСЛИ(ЕЧИСЛО(D6);D6;A5)


4. Эту же ячейку копируем в соседнюю, протягиванием за правый нижний угол.

Или просто во втором столбце добавляем похожую формулу:

5. Также предлагаю привести к удобному формату готовые значения, чтобы длина будущего идентификатора всегда была одинаковой. Для этого формулы в ячейках нужно немного видоизменить:
Формулы
=ТЕКСТ(ЕСЛИ(ЕЧИСЛО(D6);D6;A5);"0000")
=ТЕКСТ((ЕСЛИ(ЕЧИСЛО(E6);E6;B5));"00000")

Это приведёт к тому, что новое значение номера смены всегда будет четырёхзначным, а новое значение номера заказа всегда пятизначным, с лидирующими нулями в начале.

Эти действия – чистое украшательство. Просто если уж делать удобочитаемый отчёт, то делать надо сразу красиво. Напоминаю, что если у Вас нумерация заказов сквозная, то Вам достаточно одного нового столбца и формулу как на четвёртом шаге.

6. В третьем столбце и создаём уникальный идентификатор заказа, вставив формулу:
Формулы
=СЦЕПИТЬ(A6;B6)


7. Закрепим области, выделив ячейку под заголовком Блюдо:

8. Теперь нужно скопировать формулы в трёх первых столбцах до конца, для этого выделяем три ячейки с формулами и делаем двойной щелчок правой кнопкой мыши в правом нижем углу выделенного диапазона:

Ячейки заполнялся формулами до конца значений.

9. Устанавливаем фильтры. Для этого выделяем заголовки столбцов и нажимаем Shift+Ctrl+L или соответствующий пункт в меню:

Выдержка из курса Excel для продвинутых

10. Это был урок работы с функциями и интерфейсом Excel, а теперь мы наконец-то будем делать отчёт. Фильтруем блюда, оставляя только те, которые «Пиво»:


11. Теперь нужно применить фильтр к идентификаторам заказа (если у Вас сквозная нумерация заказов, то достаточно к новому столбцу «Чек»). И тут снова маленький экскурс в работу Excel. Обратите внимание на то, что сейчас, несмотря на то, что в фильтре вроде бы перечислены лишь искомые чеки, на самом деле фильтр применён со значением «(Выделить все)», что не позволяет нам как-то его использовать в таком виде дальше. Поэтому применим хитрость. Снимаем галку со значения первого чека (в этом примере 087312082), а сам этот номер запоминаем.

Получаем вот такую картину, с двумя применёнными фильтрами: все чеки (кроме 087312082) и все блюда, которые Пиво.

12. Теперь, отменяем фильтр для блюд:

13. Это даёт нам отчёт обо всех заказах (кроме 087312082) с их полным содержимым, в которых есть любое Пиво:

14. Вернём в список заказ 087312082:

Теперь в отчёте точно только те заказы с их полным содержимым, в которых есть любое Пиво:

15. Если Ваша цель просто вывести отчёт, в котором перечислены все заказы с их полным содержимым, в которых есть конкретное блюдо, то на этом всё. Но давайте вернёмся к рассмотрению нашей задачи: узнать сумму выручки и наценки по таким заказам. Присмотревшись к структуре OLAP Отчета можно увидеть, что сумма по строкам, например, заказов 12082 или 31 в столбце «Сумма со скидкой» будет просуммирована дважды: итоги по каждой строке и сумма из строки «всего», а значит надо избавиться от строк со значением «всего». Снова фильтры нам в помощь. Фильтруем по столбцам «Номер чекам» и «Номер смены»:

Исключаем все значения, в которых содержится слово «всего», т.е. оставляем все те, которые слово «всего» не содержат:

Эту операцию проделываем и для столбца «Номер чекам» и для «Номер смены».

Отчет о заказах с блюдом

16. Уже сейчас выделив столбец «Сумма со скидкой» итоговую сумму отфильтрованных заказов можно увидеть на нижней панели Excel:


17. Но если Вам нужно распечатать этот отчёт, то итоги можно просчитать, вставив под столбцом «Сумма со скидкой» формулу, например:
Формула
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;I1:I45212)


18. Вот такой отчёт получил наш заказчик: