Ценовая карта как составить

Время на прочтение
10 мин

Количество просмотров 10K

Считается, что Data Mining — это магическое снадобье из SQL, Python, Power BI и других волшебных компонент. Мало кто знает, что при правильном подходе с Data Mining может совладать офисный планктон с помощью одного лишь Excel.

Если вы абсолютно далеки от Data Mining, но хотите причаститься его таинств, это руководство в картинках по шагам сделано для вас. Особенно полезно тем, кто никогда бы даже не подумал сделать подобное самостоятельно.

Если вы владеете специальными инструментами для работы с данными, то будет интересно узнать ваше мнение о решениях без «рокет сайнс» (как о явлении в целом, так и о данном кейсе).

В качестве практического вопроса будем рассматривать визуализацию данных из объявлений на популярных сайтах продажи квартир. Визуальный анализ — основа основ Data Mining, а при отсутствии специальных знаний — и вовсе единственный способ для понимания смысла, содержащегося в большом количестве данных. Это настолько фундаментальный навык, что ему посвящена целая народная мудрость:

Лучше один раз увидеть, чем сто раз услышать*

*Это все, что нужно знать о достоинствах визуального анализа.

Термины

Тепловая карта (heat map) – обозначение какого-либо показателя цветом:

Как правило, более высокие значения обозначаются красными оттенками, более низкие – синими. Обычная цветовая шкала выглядит так:

Географическая тепловая карта – обозначение показателя цветом на географической карте. Более высокие значения температуры показаны более красными оттенками в привязке к географическим точкам:

Географическая тепловая карта цен – обозначение цветом цен в разных географических местах.

В нашем случае это будут цены на квартиры.

Данные

Цены на квартиры будем брать с общеизвестных досок объявлений А и Ц. Для сбора объявлений без программирования нужно воспользоваться готовым парсером. В данном случае выберем наиболее доступный по причине его бесплатности и наиболее удобный из-за простоты установки в три клика в Excel.

Парсеру надо дать понять какие объявления нужно скачивать. Для этого используется ссылка на доску объявлений.

Для подготовки ссылки для скачивания объявлений с доски объявления А открываем браузер, в браузере открываем сайт доски объявлений, выбираем регион (для примера → Брянск) и раздел → квартиры. В адресном поле браузера получаем ссылку: https://www.avito.ru/bryanskaya_oblast/kvartiry. В последней части ссылки видим раздел → kvartiry, перед ней расположен регион → bryanskaya_oblast. Вместо Брянска можно указать свой регион, а вместо раздела квартир можно указать дома-дачи-коттеджи или земельные-участки. Также можно использовать фильтры (например новостройки или вторичка, количество комнат) и они отобразятся в составе ссылки. Скажем спасибо доске объявлений А за такой понятный порядок формирования ссылок.

Для подготовки ссылки с доски объявлений Ц придется сделать дополнительный шаг: после выбора региона, раздела, фильтров и нажатия кнопки «Найти» нужная ссылка еще не будет готова. Для завершения подготовки ссылки нужно перейти на вторую страницу списка объявлений. После этого ссылка в адресной строке браузера примет вид https:// cian.ru/cat.php?deal_type=sale&engine_version=2&offer_type=flat&p=2&region=4562&room1=1&room2=1. Раздел квартир здесь будет в offer_type=flat, а регион – в region=4562. Скажем «фу» доске объявлений Ц за не самый удобный порядок формирования ссылок.

Готовые ссылки как есть копируем из адресной строки браузера (нажатием кнопок Ctrl+A и Ctrl+C) и вставляем в парсере нажатием кнопки Добавить ссылку. Для обеих ссылок можно указать один и тот же новый файл Excel, в который будут сохраняться объявления.

Чтобы код для парсинга доски объявлений А загрузился в Excel → в настройках парсера (расположены в Excel на вкладке Надстройки) ставим галочку у парсера доски объявлений А и выключаем галочки у сохранения фотографий из объявлений, у сохранения копии объявлений, у открывания номера телефона и у других ненужных опций. То же самое повторяем с настройками парсера доски объявлений Ц.

Теперь ссылки полностью готовы для загрузки объявлений. Нажимаем в меню парсера кнопку Старт и ждем около 20 секунд до загрузки первого объявления. Да, процесс совсем не быстрый и займет время. Можно уменьшить интервал запросов в настройках парсера до 10 или 5 секунд и иногда это даже прокатывает. Но обычно доски объявлений очень не любят ботов и сразу закрывают доступ к данным (бан). Конечно, эти ограничения можно обойти и загружать данные в 100 раз быстрее, но это дороже.

Загружаемые объявления выглядят примерно так:

Таких строк может быть несколько тысяч. В нашем примере это около 5000 объявлений для Брянской области в октябре 2021.

Из множества данных нам понадобятся только широта, долгота, цена, общая площадь и офер:

Это сырые данные, которые требуют подготовки.

Подготовка

Отделим аренду от продажи. Для этого добавим фильтр по полю «офер» и выделим только предложения продажи. Можно и наоборот – оставить только предложения аренды и работать дальше с ними.

Выделим отфильтрованные данные, Ctrl+G → только видимые:

Копируем их Ctrl+C и вставим на новый лист Ctrl+V:

 Если показывать цены на многокомнатные квартиры одним цветом и цены однушек другим цветом, в результате получим карту размещения жилья по числу комнат. Для анализа цен этот показатель слишком сырой. Вместо него используем среднюю цену за квадратный метр.

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

Теперь проведем стандартные процедуры проверки заведомо ошибочных данных.

У нас есть две группы данных: географическое положение и цена. Для проверки обеих групп используем визуальный контроль.

Поместим имеющиеся географические точки на обычную диаграмму Excel:

Посмотрим координаты крайних точек Брянской области. Широта должна быть от 51,5039 до 54,021, долгота от 31,1432 до 35,1917. Некоторые наши точки выходят за эти пределы. Опустим здесь рассмотрение причин появления испорченных данных и возможных путей их восстановления, т.к. это не относится прямо к цели визуализации данных и противоречит принятому ограничению квалификации пользователя. По этой же причине используем грубый, но простой способ избавления от испорченных данных.

Заменим нулями строки, где долгота и широта выходят за границы региона → с помощью простой формулы:

Затем добавим фильтр и уберем отображение строк с нолями:

Выделим все строки отфильтрованных колонок данных, затем Сtrl+G → только видимые:

Копируем их Ctrl+C и вставим в новое место (рядом) Ctrl+V.

Очищенные таким образом долготы и широты точек отправляем на новую диаграмму Excel и видим результат очистки:

Теперь также с помощью визуального анализа очистим данные о ценах.

Для этого построим гистограмму, чтобы посмотреть сколько каких значений цены в нашей выборке.

Почему график именно такой

Практический смысл этой гистограммы: если данные из правой части отметить на карте одним цветом, из средней — вторым и из левой — третьим, то вся карта будет залита одним цветом. Потому что в средней и в правой частях точек почти нет. Аналитического смысла у такой карты не будет.

Чтобы избавиться от упомянутого эффекта нужно отбросить хвост распределения, а заодно и данные из первого левого кармана. В результате получим такую гистограмму:

Теперь количество данных в разных частях более-менее сопоставимо. Количество карманов здесь посчитано Excel автоматически и оно явно избыточно для того, чтобы каждый уровень цены обозначать своим цветом. Поэтому в дальнейшем перестроим гистограмму по количеству карманов в соответствии с количеством цветов, которые будут использованы на карте. В нашем случае будем использовать 7 цветов.

Перед разбивкой данных по карманам рассмотрим еще одно обстоятельство, которое стоит учесть на этапе подготовки данных. Дело в том, что точки на карте могут располагаться слишком тесно. Например, здесь шесть объявлений расположены в одном доме и перекрывают друг друга даже на самом крупном масштабе:

На более мелких масштабах эти метки полностью сольются и станут неразличимы.

Чтобы избавиться от излишней в данном случае детализации данных проведем их усреднение. Для усреднения данных воспользуемся следующим приемом.

Обычная точность указания координат использует 6 знаков после запятой. Например, широта 52,549374 и долгота 31,897056. Четвертый знак после запятой соответствует масштабу придомовой территории. В нашем примере в диапазон долготы от 31,8965 до 31,8974 попадают все объявления, относящиеся к одному строению. Используем это обстоятельство для группировки данных в процессе усреднения.

Добавляем к имеющимся данным столбцы с округленными до 3 знака широтой и долготой. Еще одним столбцом добавляем символьную сумму этих двух последних столбцов:

Что в результате дает:

После чего сортируем все столбцы по колонке с текстом и применяем Промежуточный итог:

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

Для замены групп на точки со средними значениями → сворачиваем все группы, выделяем колонки координат и цены:

Затем выделяем только видимые ячейки Ctrl+G → только видимые, копируем Ctrl+C:

После чего вставляем скопированное на новый лист. Теперь на каждом здании будет не больше одной точки с данными, которая соответствует среднему значению всех относящихся к зданию объявлений:

С помощью такого приема можно провести усреднение цен на уровне группы зданий или по кварталу.

После такого прореживания осталось меньше половины точек. Благодаря этому карта цен будет значительно меньше перегружена данными в самых насыщенных местах.

Получившийся набор данных предстоит разложить по карманам в зависимости от величины цены. Для 7 цветов = 7 карманов гистограмма выглядит так:

Данные из первого левого столбца гистограммы будут синего цвета, из последнего правого — красными, а из расположенных между ними — оттенками зеленого:

Цвет получается смешиванием красного (R), зеленого (G) и синего (B). Интенсивность каждого цвета находится в диапазоне от 0 до 255. Смешивание для получения показанных цветов приведено в следующей таблице.

 Обозначения из столбца Код будут использованы для окрашивания данных на карте.

Полученный результат можно считать подготовленными данными для отображения их на карте.

Обработка

Имеющиеся цены разделим на 7 равных интервалов. (В этой области знаний интервалы синонимы диапазонов, и еще их называют карманами.)

Для определения ширины интервала разницу максимальной и минимальной цен нужно разделить на количество карманов. В нашем случае данные такие:

 И карманы:

Для получения данных первого кармана нужно скопировать данные широты и долготы для цен от 3999 до 19035 и вставить в новое место. Цены копировать не нужно, они использовались только для разбивки данных по карманам и больше не пригодятся. Аналогично для второго кармана копируем широты и долготы для цен от 19035 до 34071 и вставляем их рядом с данными из первого кармана. Повторив семь раз получим в результате:

В каждом кармане две колонки: левая — широта и правая — долгота. Количество строк в каждом кармане разное, как было показано на последней гистограмме.

Теперь данные полностью готовы для их помещения на карту.

Карта

Для построения карты нужно сделать три шага:

Добавить шаблон карты → Заполнить шаблон данными → Показать результат

Шаблон карты добавляется кнопкой Добавить в меню парсера. Если в меню парсера нет кнопок для работы с картой, то в настройках парсера нужно включить опцию Excel → График на карте.

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

Для вставки в шаблон данных о ценах из первого кармана нужно в ячейку A3 вставить ссылку на диапазон данных о широте и долготе, которые указаны в двух колонках первого кармана, вот эти:

Для примера это диапазон Q4:R582 на листе По карманам в файле Брянск 10(октябрь)-21.xlsx.

Вставить ссылку на этот диапазон можно с помощью функции Ссылка(диапазон).

В ячейке А3 шаблона пишем название функции:

В качестве единственного аргумента функции Ссылка указываем диапазон Q4:R582 на листе По карманам:

В результате получаем:

Точки данных первого кармана ранее условились обозначать синим цветом с кодом  0,171,255. Для примера формулы ниже: таблица с кодами цветов находится на листе Палитра. Код синего цвета находится в ячейке Е3:

Для вставки ссылки на ячейку в Excel не требуется использовать специальную функцию, поэтому в ячейке шаблона В3 вставляем ссылку на ячейку кода синего цвета обычным способом:

В результате:

Размер точек определяется из субъективных соображений. Для примера примем размер 10:

На этом шаблон карты полностью готов для отображения данных из первого кармана.

Посмотрим что получилось. Для этого нажимаем кнопку Отобразить в меню парсера, после чего открывается новое окно:

Метки на карте отсутствуют из-за масштаба. Зумим колесом мышки и получаем:

Закрываем окно с картой, добавляем данные из второго кармана:

Данные из второго кармана отображаются поверх данных первого кармана:

После добавления всех оставшихся карманов:

На карте:

Это и есть визуализация цен на географической карте, сделанная в Excel без программирования. Ее можно зумить и двигать как обычную карту в браузере. Для копирования карты в буфер в парсере есть специальная кнопка Копировать.

В завершение отметим на карте какое-нибудь место, например Аграрный университет. Координаты широты и долготы БГАУ возьмем по указанной ссылке и вставим в ячейки J3 и К3. Ссылку на ячейки с координатами вставим в ячейку шаблона Н3:

Увидим БГАУ на карте и оценим его влияние на цену недвижимости:

Файл Excel с примером можно скачать здесь.

Что такое карта маркетинга

Маркетинговая карта – это разработанная и задокументированная стратегия входящего маркетинга, позволяющая привести клиента к вашему бренду, используя конкретный маршрут.

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

Зачем составлять маркетинговую карту

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

Когда поняли что нужно исправить, остается найти что можно улучшить. Задача компании – построить наилучший маршрут для клиента, который быстрее всего и с наименьшими затратами приведет его к покупке. 

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

Как создать маркетинговую карту

Маркетинговая карта позволит узнавать и вести клиента на каждом этапе пути от лидогенерации к конверсии. Но сначала ее нужно составить. Все начинается с целевой аудитории.

Определите, кто ваша ЦА

Первый этап – сегментирование клиентов. У целевой аудитории отличаются потребности и ресурсы.

Ресурсы – это то, чем пользуется клиент, чтобы удовлетворить потребность:

По этим опорным точкам создаем образ покупателя. Когда бренд соответствует потребностям ЦА, и у нее хватает ресурсов, понятно что делать. Но ситуация меняется, когда у клиента нет ресурсов.

Задокументируйте путешествие клиентов

Компании нередко игнорируют последнюю стадию, но именно она становится решающей при трансформации первого клиента в постоянного. Обратимся к статистике, чтобы понять, как сопровождение влияет на повторные продажи: по данным исследования Bain & Company, достаточно удержать на 5% больше клиентов, чтобы увеличить прибыль на 25% – 95%.

Для примера подойдут кредиты: банки улучшают условия кредитования для своих клиентов, особенно, зарплатных. Это побуждает потребителей продолжать пользоваться услугами этого банка. Тут же в счет идут кэшбэки, повышенные ставки по вкладам и другие бонусы.

Когда клиенты проходят маршрут, можно отслеживать, кто, до какой точки добрался и что делал во время путешествия. Это создает картину отличий клиентов, на основе которой можно персонализировать маркетинг.

Чтобы построить наиболее эффективные маршруты, нужно провести оценку и задокументировать статистику со стороны бренда и потенциального покупателя. На прошлом этапе выяснили, какие каналы использует клиент для поиска продукта. Но не менее важно понять, о чем думает клиент: что его волнует больше всего? Чем он заинтересован?

Когда вы описываете путешествие клиента, то лучше понимаете, какие методы использовать для привлечения аудитории и как ее направить. Конвертировать ее в клиентов получится, если найдете точки соприкосновения с лидом на каждом этапе маркетинговой воронки.

Определите, какие способы работают лучше всего

Прежде чем запускать новую рекламную кампанию, нужно узнать, какой канал и контент привлекает аудиторию лучше всего:

Если потребность клиента, например, понять как собрать конструктор, то одним из эффективных способов привлечения будут инструкции в блоге или видеогайды. Для каждого этапа будет работать определенный канал взаимодействия с клиентом. Некоторые из них могут хорошо работать на разных этапах. Например, email-рассылка подходит и для вовлечения, и для сопровождения клиентов после покупки. А публикации в соцсетях работают и для «холодных» клиентов.

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

Другими словами, потребитель проходит все этапы и, если попробовать их перескочить, то есть вероятность, что он «сорвется» в пропасть. Но маркетинговая карта не статична: она будет меняться со временем, поэтому потребуется постоянно оценивать ее эффективность и менять подходы в зависимости от поведения целевой аудитории.

в Excel без программирования.

Считается, что Data Mining — это магическое снадобье из SQL, Python, Power BI и других волшебных компонент. Мало кто знает, что при правильном подходе с Data Mining может совладать офисный планктон с помощью одного лишь Excel.

Если вы абсолютно далеки от Data Mining, но хотите причаститься его таинств, это руководство в картинках по шагам сделано для вас. Особенно полезно тем, кто никогда бы даже не подумал сделать подобное самостоятельно.

В качестве практического вопроса будем рассматривать визуализацию данных из объявлений на популярных сайтах продажи квартир. Визуальный анализ — основа основ Data Mining, а при отсутствии специальных знаний — и вовсе единственный способ для понимания смысла, содержащегося в большом количестве данных. Это настолько фундаментальный навык, что ему посвящена целая народная мудрость:

Термины

Тепловая карта (heat map) – обозначение какого-либо показателя цветом:

Как правило, более высокие значения обозначаются красными оттенками, более низкие – синими. Обычная цветовая шкала выглядит так:

Географическая тепловая карта – обозначение показателя цветом на географической карте. Более высокие значения температуры показаны более красными оттенками в привязке к географическим точкам:

Географическая тепловая карта цен – обозначение цветом цен в разных географических местах.

В нашем случае это будут цены на квартиры.

Данные

Цены на квартиры будем брать с общеизвестных досок объявлений А и Ц. Для сбора объявлений без программирования нужно воспользоваться готовым парсером. В данном случае выберем наиболее доступный по причине его бесплатности и наиболее удобный из-за простоты установки в три клика в Excel.

Парсеру надо дать понять какие объявления нужно скачивать. Для этого используется ссылка на доску объявлений.

Для подготовки ссылки для скачивания объявлений с доски объявления А открываем браузер, в браузере открываем сайт доски объявлений, выбираем регион (для примера → Брянск) и раздел → квартиры. В адресном поле браузера получаем ссылку: https://www.avito.ru/bryanskaya_oblast/kvartiry. В последней части ссылки видим раздел → kvartiry, перед ней расположен регион → bryanskaya_oblast. Вместо Брянска можно указать свой регион, а вместо раздела квартир можно указать дома-дачи-коттеджи или земельные-участки. Также можно использовать фильтры (например новостройки или вторичка, количество комнат) и они отобразятся в составе ссылки. Скажем спасибо доске объявлений А за такой понятный порядок формирования ссылок.

Для подготовки ссылки с доски объявлений Ц придется сделать дополнительный шаг: после выбора региона, раздела, фильтров и нажатия кнопки «Найти» нужная ссылка еще не будет готова. Для завершения подготовки ссылки нужно перейти на вторую страницу списка объявлений. После этого ссылка в адресной строке браузера примет вид https:// cian.ru/cat.php?deal_type=sale&engine_version=2&offer_type=flat&p=2®ion=4562&room1=1&room2=1. Раздел квартир здесь будет в offer_type=flat, а регион – в region=4562. Скажем «фу» доске объявлений Ц за не самый удобный порядок формирования ссылок.

Готовые ссылки как есть копируем из адресной строки браузера (нажатием кнопок Ctrl+A и Ctrl+C) и вставляем в парсере нажатием кнопки Добавить ссылку. Для обеих ссылок можно указать один и тот же новый файл Excel, в который будут сохраняться объявления.

Чтобы код для парсинга доски объявлений А загрузился в Excel → в настройках парсера (расположены в Excel на вкладке Надстройки) ставим галочку у парсера доски объявлений А и выключаем галочки у сохранения фотографий из объявлений, у сохранения копии объявлений, у открывания номера телефона и у других ненужных опций. То же самое повторяем с настройками парсера доски объявлений Ц.

Теперь ссылки полностью готовы для загрузки объявлений. Нажимаем в меню парсера кнопку Старт и ждем около 20 секунд до загрузки первого объявления. Да, процесс совсем не быстрый и займет время. Можно уменьшить интервал запросов в настройках парсера до 10 или 5 секунд и иногда это даже прокатывает. Но обычно доски объявлений очень не любят ботов и сразу закрывают доступ к данным (бан). Конечно, эти ограничения можно обойти и загружать данные в 100 раз быстрее, но это дороже.

Загружаемые объявления выглядят примерно так:

Таких строк может быть несколько тысяч. В нашем примере это около 5000 объявлений для Брянской области в октябре 2021.

Из множества данных нам понадобятся только широта, долгота, цена, общая площадь и офер:

Это сырые данные, которые требуют подготовки.

Подготовка

Отделим аренду от продажи. Для этого добавим фильтр по полю «офер» и выделим только предложения продажи. Можно и наоборот – оставить только предложения аренды и работать дальше с ними.

Выделим отфильтрованные данные, Ctrl+G → только видимые:

Копируем их Ctrl+C:

и вставим на новый лист Ctrl+V:

Если показывать цены на многокомнатные квартиры одним цветом и цены однушек другим цветом, в результате получим карту размещения жилья по числу комнат. Для анализа цен этот показатель слишком сырой. Вместо него используем среднюю цену за квадратный метр.

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

Теперь проведем стандартные процедуры проверки заведомо ошибочных данных.

У нас есть две группы данных: географическое положение и цена. Для проверки обеих групп используем визуальный контроль.

Поместим имеющиеся географические точки на обычную диаграмму Excel:

Посмотрим координаты крайних точек Брянской области. Широта должна быть от 51,5039 до 54,021, долгота от 31,1432 до 35,1917. Некоторые наши точки выходят за эти пределы. Опустим здесь рассмотрение причин появления испорченных данных и возможных путей их восстановления, т.к. это не относится прямо к цели визуализации данных и противоречит принятому ограничению квалификации пользователя. По этой же причине используем грубый, но простой способ избавления от испорченных данных.

Заменим нулями строки, где долгота и широта выходят за границы региона → с помощью простой формулы:

Затем добавим фильтр и уберем отображение строк с нолями:

Выделим все строки отфильтрованных колонок данных, затем Сtrl+G → только видимые:

Копируем их Ctrl+C и вставим в новое место (рядом) Ctrl+V.

Очищенные таким образом долготы и широты точек отправляем на новую диаграмму Excel и видим результат очистки:

Теперь также с помощью визуального анализа очистим данные о ценах.

Для этого построим гистограмму, чтобы посмотреть сколько каких значений цены в нашей выборке.

Практический смысл этой гистограммы: если данные из правой части отметить на карте одним цветом, из средней — вторым и из левой — третьим, то вся карта будет залита одним цветом. Потому что в средней и в правой частях точек почти нет. Аналитического смысла у такой карты не будет.

Чтобы избавиться от упомянутого эффекта нужно отбросить хвост распределения, а заодно и данные из первого левого кармана. В результате получим такую гистограмму:

Теперь количество данных в разных частях более-менее сопоставимо. Количество карманов здесь посчитано Excel автоматически и оно явно избыточно для того, чтобы каждый уровень цены обозначать своим цветом. Поэтому в дальнейшем перестроим гистограмму по количеству карманов в соответствии с количеством цветов, которые будут использованы на карте. В нашем случае будем использовать 7 цветов.

Перед разбивкой данных по карманам рассмотрим еще одно обстоятельство, которое стоит учесть на этапе подготовки данных. Дело в том, что точки на карте могут располагаться слишком тесно. Например, здесь шесть объявлений расположены в одном доме и перекрывают друг друга даже на самом крупном масштабе:

На более мелких масштабах эти метки полностью сольются и станут неразличимы.

Чтобы избавиться от излишней в данном случае детализации данных проведем их усреднение. Для усреднения данных воспользуемся следующим приемом.

Обычная точность указания координат использует 6 знаков после запятой. Например, широта 52,549374 и долгота 31,897056. Четвертый знак после запятой соответствует масштабу придомовой территории. В нашем примере в диапазон долготы от 31,8965 до 31,8974 попадают все объявления, относящиеся к одному строению. Используем это обстоятельство для группировки данных в процессе усреднения.

Добавляем к имеющимся данным столбцы с округленными до 3 знака широтой и долготой. Еще одним столбцом добавляем символьную сумму этих двух последних столбцов:

Что в результате дает:

После чего сортируем все столбцы по колонке с текстом и применяем Промежуточный итог:

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

Для замены групп на точки со средними значениями → сворачиваем все группы, выделяем колонки координат и цены:

Затем выделяем только видимые ячейки Ctrl+G → только видимые, копируем Ctrl+C:

После чего вставляем скопированное на новый лист. Теперь на каждом здании будет не больше одной точки с данными, которая соответствует среднему значению всех относящихся к зданию объявлений:

С помощью такого приема можно провести усреднение цен на уровне группы зданий или по кварталу.

После такого прореживания осталось меньше половины точек. Благодаря этому карта цен будет значительно меньше перегружена данными в самых насыщенных местах.

Получившийся набор данных предстоит разложить по карманам в зависимости от величины цены. Для 7 цветов = 7 карманов гистограмма выглядит так:

Данные из первого левого столбца гистограммы будут синего цвета, из последнего правого — красными, а из расположенных между ними — оттенками зеленого:

Цвет получается смешиванием красного (R), зеленого (G) и синего (B). Интенсивность каждого цвета находится в диапазоне от 0 до 255. Смешивание для получения показанных цветов приведено в следующей таблице.

Обозначения из столбца Код будут использованы для окрашивания данных на карте.

Полученный результат можно считать подготовленными данными для отображения их на карте.

Обработка

Имеющиеся цены разделим на 7 равных интервалов. (В этой области знаний интервалы синонимы диапазонов, и еще их называют карманами.)

Для определения ширины интервала разницу максимальной и минимальной цен нужно разделить на количество карманов. В нашем случае данные такие:

Для получения данных первого кармана нужно скопировать данные широты и долготы для цен от 3999 до 19035 и вставить в новое место. Цены копировать не нужно, они использовались только для разбивки данных по карманам и больше не пригодятся. Аналогично для второго кармана копируем широты и долготы для цен от 19035 до 34071 и вставляем их рядом с данными из первого кармана. Повторив семь раз получим в результате:

В каждом кармане две колонки: левая — широта и правая — долгота. Количество строк в каждом кармане разное, как было показано на последней гистограмме.

Теперь данные полностью готовы для их помещения на карту.

Карта

Для построения карты нужно сделать три шага:

Добавить шаблон карты → Заполнить шаблон данными → Показать результат

Шаблон карты добавляется кнопкой Добавить в меню парсера. Если в меню парсера нет кнопок для работы с картой, то в настройках парсера нужно включить опцию Excel → График на карте.

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

Для вставки в шаблон данных о ценах из первого кармана нужно в ячейку A3 вставить ссылку на диапазон данных о широте и долготе, которые указаны в двух колонках первого кармана, вот эти:

Для примера это диапазон Q4:R582 на листе По карманам в файле Брянск 10(октябрь)-21.xlsx.

Вставить ссылку на этот диапазон можно с помощью функции Ссылка(диапазон).

В ячейке А3 шаблона пишем название функции:

В качестве единственного аргумента функции Ссылка указываем диапазон Q4:R582 на листе По карманам:

В результате получаем:

Точки данных первого кармана ранее условились обозначать синим цветом с кодом 0,171,255. Для примера формулы ниже: таблица с кодами цветов находится на листе Палитра. Код синего цвета находится в ячейке Е3:

Для вставки ссылки на ячейку в Excel не требуется использовать специальную функцию, поэтому в ячейке шаблона В3 вставляем ссылку на ячейку кода синего цвета обычным способом:

В результате:

Размер точек определяется из субъективных соображений. Для примера примем размер 10:

а этом шаблон карты полностью готов для отображения данных из первого кармана.

Посмотрим что получилось. Для этого нажимаем кнопку Отобразить в меню парсера, после чего открывается новое окно:

Метки на карте отсутствуют из-за масштаба. Зумим колесом мышки и получаем:

Закрываем окно с картой, добавляем данные из второго кармана:

Данные из второго кармана отображаются поверх данных первого кармана:

После добавления всех оставшихся карманов:

На карте:

Это и есть визуализация цен на географической карте, сделанная в Excel без программирования. Ее можно зумить и двигать как обычную карту в браузере. Для копирования карты в буфер в парсере есть специальная кнопка Копировать.

В завершение отметим на карте какое-нибудь место, например Аграрный университет. Координаты широты и долготы БГАУ возьмем по указанной ссылке и вставим в ячейки J3 и К3. Ссылку на ячейки с координатами вставим в ячейку шаблона Н3:

Увидим БГАУ на карте и оценим его влияние на цену недвижимости:

Файл Excel с примером можно скачать здесь.

Разобраться в вопросе помогает:

Оксана Зубова, заведующая производством, технолог и бухгалтер-калькулятор в общепите. Опыт в HoReCa — больше 13 лет.

Что такое калькуляционная карта. Это документ для расчёта себестоимости и цены блюда.

Для чего она нужна. Хотя Роспотребнадзор и налоговая инспекция не проверяют эти карточки (в отличии от технологических карт), они необходимая составляющая бизнес-процессов в общепите.

Именно в калькуляционной карте отражается стоимость каждого ингредиента и итоговая себестоимость блюда, указывается наценка и цена продажи. Карта помогает отслеживать и анализировать, насколько каждое блюдо прибыльно для заведения.

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

Рассмотрим, как обычно выглядит калькуляционная карта. Образец для общепита — ОП-1. Но с 2013 года она не обязательна для применения и вы можете использовать произвольную форму.

Форма ОП-1 позволяет определить себестоимость и учесть нюансы по расчёту цены для локальных заведений и для сетей. Поэтому дальше мы рассмотрим заполнение документа по калькуляции блюд именно на её примере.

Откуда брать данные для составления калькуляционной карты

Калькуляционная карта по форме ОП-1 заполняется на основании данных из технологической карты — документа, где содержится рецептура и выход блюда.

Технологическую и калькуляционную карты оформляют на все блюда и полуфабрикаты. Различие в том, что техкарта содержит технологию изготовления блюда без расчёта финансов и используется работниками кухни. Калькуляционная — показывает затраты на приготовление блюда и прибыль, она нужна бухгалтерам и руководству.

Важно! Если в каком-то пункте техкарты вы допустили ошибку, то неверные данные пойдут и в калькуляционную карту, что отразится на всех расчётах и прибыли заведения. Также убедитесь, что номер калькуляционной карты совпадает с номером техкарты.

Как составить калькуляционную карту

Рассмотрим, как по правилам сделать расчёт калькуляционных карт в общепите и что указывается отдельно по каждому разделу:

• «шапка» документа,
• расчёт себестоимости,

расчёт наценки, цены и выхода готового блюда.

1. Название ООО и структурное подразделение. В строке «структурное подразделение» укажите название вашего заведения. Если вы ИП, то по строке «организация» впишите ИП и свои фамилию, имя и отчество.

2. Название блюда по техкарте. Названия блюда должны совпадать в обеих картах — технологической и калькуляционной.

3. Прочие реквизиты организации — код по ОКПО и вид деятельности по ОКДП.

4. Номер блюда по сборнику рецептур, техкарте (ТТК), СТП. Эта графа предусмотрена, чтобы указать номер блюда из справочника «Сборник рецептур блюд и кулинарных изделий для предприятий общественного питания», номера блюда из техкарты и стандартов предприятия. На практике обычно ставится только номер из техкарты.

5. Дата утверждения карты и её номер по порядку (должен совпадать с номером техкарты на это блюдо).

6. Наименование продуктов. В этом столбце приводится весь перечень ингредиентов, которые необходимы для приготовления блюда. В соседнем столбце ставится код продукта в программе автоматизации.

7. Норма продуктов. Указывается количество продукта, необходимое для приготовления блюда, в килограммах. Важно: надо внести в карту не исходный вес сырья, а вес полуфабриката, который используется для приготовления блюда.

8. Цена — стоимость ингредиента за килограмм его веса.

9. Сумма — стоимость ингредиента, необходимого для приготовления блюда. Значение в этой графе получается, если перемножить норму продукта и его цену.

10. Общая стоимость сырьевого набора на 100 блюд. Графа показывает себестоимость ста порций блюда: складываются все значения в столбце «сумма» и результат умножается на 100. Вместо ста порций вы можете посчитать себестоимость на десять порций (т.е. умножить на 10). Подробнее рассмотрим это дальше.

11. Наценка на блюдо. Её устанавливает руководитель заведения.

12. Цена продажи блюда. Указывается цена одной порции блюда в меню. Для её расчёта надо общую стоимость сырьевого набора (пункт 10) разделить на 100 или 10, затем к полученной себестоимости одной порции добавить наценку.

13. Выход одного блюда в готовом виде — вес одной порции готового блюда.

Калькуляционные карты для общепита составляют на:

• Полуфабрикаты — продукты, которые прошли кулинарную обработку. На каждый полуфабрикат заводится отдельная карта, поскольку продукт теряет вес при чистке, тепловой обработке (варке, жарке) и хранении. А из-за потери веса меняется стоимость продукта — его килограмм становится дороже.

В картах на полуфабрикаты мы заносим вес продукта (до обработки) в графу «норма, кг» и его стоимость в графу «цена за кг». В графе «выход одного блюда в готовом виде, кг» указываем вес полученного полуфабриката (после обработки) и в графе «общая стоимость сырьевого набора на 100 блюд» — стоимость 100 порций полуфабриката с учётом пересчёта.

Важно. Чтобы не возникло путаницы, полуфабрикат в калькуляционной карте надо назвать иначе, чем исходный продукт. Например, добавить «п/ф» к названию.

• Готовые блюда. Карту надо заводить на каждое блюдо или напиток. В неё заносятся все ингредиенты блюда и их стоимость (если ингредиент — полуфабрикат, то его стоимость с учётом пересчёта). Только в карты на готовые блюда, в отличие от карт на полуфабрикаты, также будет входить наценка и продажная цена.

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

Почему общая стоимость рассчитывается на 10 или 100 блюд

Хотя в карте мы делаем калькуляцию на одну порцию, общую стоимость надо рассчитать на 10 или 100 порций. Если сделать расчёт только на одну порцию, себестоимость и продажная цена будут некорректными, так как было учтено больше продуктов, чем требуется.

Например, чтобы пожарить одну порцию котлет для гамбургера, вы потратите 50 грамм масла. А на десять порций котлет в этой же сковороде вам потребуется не 500 грамм масла, а меньше.

Также играет роль человеческий фактор: реальный расход ингредиентов будет отличаться от нормы закладки продукции на порцию. К примеру, по рецепту на одну порцию салата идёт 40 грамм соуса. Но повар добавит в одну порцию 42 грамма, а в другую — 39. Эти неточности скорректируются за счёт среднего значения, когда себестоимость рассчитана на 10 или 100 порций.

Рекомендация. Чем крупнее заведение — тем больше неточностей. Если у вас кафе или ресторан, делайте расчёт на 10 порций. Если у вас столовая на крупном предприятии или сеть заведений с общим цехом приготовления — сразу на 100.

Что делать, если изменилась норма или цена ингредиента

Графы «норма, кг» и «цена, руб.коп.» в форме ОП-1 повторяются шесть раз. Эти свободные графы надо использовать, когда меняется цена или количество ингредиента.

Подпись и хранение калькуляционных карт

Калькуляционная карта обязательно должна быть распечатана и подписана бухгалтером-калькулятором (или тем, кто эту карту составил), заведующим производством (если в ресторане или кафе есть такая должность) и руководителем заведения.

Заполненные карты сшиваются в одну папку и хранятся в бухгалтерии или у руководителя. Если карточка уже неактуальна (например, сделана по устаревшей рецептуре или содержит в себе неактуальные цены) выбрасывать её не рекомендуется. Оставьте её в папке — пусть она там и хранится.

Александр Хабаров, начальник отдела технической поддержки Quick Resto

Теперь поговорим про то, как считать калькуляционную карту не вручную, а в программе для общепита. В системе Quick Resto она создаётся автоматически на основании данных заполненной техкарты и соответствует форме ОП-1. Рассмотрим, как это происходит.

1. Данные о количестве ингредиентов

В техкарту вам надо внести все ингредиенты блюда и потери их веса при обработке (если они есть). Таким образом в техкарте появятся данные о точном количестве продуктов, которые нужны для приготовления одной порции блюда (они автоматически рассчитаются в графе «Выход»). Эти данные отразятся в столбце «норма» калькуляционной карты.

2. Автоматизация расчёта себестоимости блюда и складской учёт

Себестоимость каждого продукта подтянется в технологическую и калькуляционную карты автоматически. Поскольку в Quick Resto реализован полный складской учёт, данные о себестоимости есть в системе на основании приходных накладных, актов приготовления блюда и внутренних перемещений с одного склада на другой.

По каждому ингредиенту и блюду вы можете посмотреть текущий остаток и все документы движения (инвентаризацию, приход, перемещение, списание продуктов и т.д.).

3. Расчёт наценки и разные цены для нескольких точек

В систему вносится именно цена продажи, поэтому в калькуляции блюда рассчитывается наценка. Вы можете настроить разные цены на одно блюда для каждой точки (если у вас сеть) и посмотреть прибыль по каждому блюду.

4. Изменение цены или рецепта

Если у блюда изменилась цена или рецептура, прежние данные не пропадут. Вы просто вносите актуальные данные и дату их вступления в силу через «Менеджер версии» и создаёте новую версию техкарты. Калькуляция себестоимости пересчитается автоматически. Это позволяет сравнивать данные и анализировать динамику цен.

5. Печать формы ОП-1

Калькуляционную карточку можно распечать из системы, чтобы её подписали сотрудники, которые отвечают за расчёт себестоимости блюда.