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

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

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

Тепловая карта цен в Excel
Тепловая карта цен в Excel

В качестве практического вопроса будем рассматривать визуализацию данных из объявлений на популярных сайтах продажи квартир. Визуальный анализ — основа основ 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 раз быстрее, но это дороже.

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

Фрагмент листа Excel c загруженными парсером объявлениями Авито и ЦИАН
Фрагмент листа Excel c загруженными парсером объявлениями Авито и ЦИАН

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

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

Широта

Долгота

Цена

Общая

Офер

53,2656

34,35292

5030000

64,2

Продам

53,20856

34,46647

2443000

51

Продам

53,26398

34,33171

10000

40

Сдам

53,54983

33,76486

750000

35

Продам

 

 

 

 

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

Подготовка

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

Фильтр Excel
Фильтр Excel

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

Выделение только видимых ячеек
Выделение только видимых ячеек

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

Широта

Долгота

Цена

Общая

53,2656

34,35292

5030000

64,2

53,20856

34,46647

2443000

51

53,54983

33,76486

750000

35

53,31711

34,30244

1450000

62

53,26612

34,33491

2950000

36

 

 

 

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

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

Широта

Долгота

За 1 кв.м.

53,2656

34,35292

78348,91

53,20856

34,46647

47901,96

53,54983

33,76486

21428,57

53,31711

34,30244

23387,1

53,26612

34,33491

81944,44

 

 

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

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

Поместим имеющиеся географические точки на обычную диаграмму 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 знака широтой и долготой. Еще одним столбцом добавляем символьную сумму этих двух последних столбцов:

Формулы округления координат
Формулы округления координат

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

Результат округления координат
Результат округления координат

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

Стандартный инструмент Excel на вкладке Данные → Структура
Стандартный инструмент Excel на вкладке Данные → Структура

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

В строке 5 среднее строк 2 - 4
В строке 5 среднее строк 2 - 4

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

Выделяем только нужные колонки
Выделяем только нужные колонки

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

Так выглядят скопированные только видимые ячейки
Так выглядят скопированные только видимые ячейки

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

Одна точка вместо шести в результате их усреднения
Одна точка вместо шести в результате их усреднения

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

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

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

Гистограмма усредненных данных
Гистограмма усредненных данных

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

Те же данные в боевой раскраске
Те же данные в боевой раскраске

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

Цвет

R

G

B

Код

 Синий

0

171

255

0,171,255

 

0

171

171

0,171,171

 

0

255

171

0,255,171

 Зеленый

0

255

0

0,255,0

 

171

255

0

171,255,0

 

255

85

0

255,85,0

 Красный

255

42

0

255,42,0

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

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

Обработка

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

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

Минимум

4000

Максимум

109253,1

Кол-во карманов

7

Ширина кармана

15036

 И карманы:

1

3999

-

19035

2

19035

-

34071

3

34071

-

49107

4

49107

-

64144

5

64144

-

79180

6

79180

-

94216

7

94216

-

109253

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

Данные разложены по карманам
Данные разложены по карманам

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

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

Карта

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

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

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

Шаблон тепловой карты Excel
Шаблон тепловой карты Excel

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

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

Фрагмент данных первого кармана
Фрагмент данных первого кармана

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

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

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

Пользовательская функция =Ссылка()
Пользовательская функция =Ссылка()

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

Использование функции Ссылка
Использование функции Ссылка

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

Результат функции Ссылка
Результат функции Ссылка

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

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

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

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

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

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

Слишком крупный масштаб карты
Слишком крупный масштаб карты

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

Визуализация данных первого кармана
Визуализация данных первого кармана

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

Шаблон карты с данными двух первых карманов
Шаблон карты с данными двух первых карманов

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

Визуализация данных двух первых карманов
Визуализация данных двух первых карманов

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

Полностью заполненный шаблон карты для всех карманов
Полностью заполненный шаблон карты для всех карманов

На карте:

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

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

Заполненный шаблон метки на карте
Заполненный шаблон метки на карте

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

Метка на карте в Excel
Метка на карте в Excel

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

Комментарии (11)


  1. DS28
    18.10.2022 08:59
    +3

    И всё же это задача решается с программированием, просто на стороне...
    Если парсинг не сработает, если сайт будет другой, если задача будет отличаться - может ничего не получиться... И тогда нужно обратиться к автору, за новой разработкой)

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


  1. Trabant_Vishnya
    18.10.2022 09:41
    +3

    Вот вижу такие статьи порой и не понимаю – зачем мучить людей обычным Excel, если есть практически интуитивно понятный Power Query?


    1. 1001
      18.10.2022 11:50
      +2

      С PQ не всё так просто, особенно если он в виде надстройки над старыми версиями Excel. При большом наборе данных, он начинает дико тормозить и получается не работа, а сплошное разочарование.


  1. qyix7z
    18.10.2022 10:32
    +1

    Вставить ссылку на этот диапазон можно с помощью функции Ссылка(диапазон).
    Пользовательская функция =Ссылка()
    Это либо не эксель — эксель не знает функции =Ссылка(), либо эксель с макросами пользовательскими функциями, что одно и тоже.


    1. Robastik Автор
      18.10.2022 12:34
      +1

      не эксель

      Эксель с надстройкой. Гистограмма, например, тоже требует надстройку. Power Query тут упоминают в комментах, тоже надстройка. Для пользователя установить надстройку != программировать.


      1. qyix7z
        18.10.2022 13:10
        +1

        Это какая-то известная и доступная надстройка? Oh, wait… Вижу: Robastik.xlam
        То есть пользователь (не умеющий программировать) пишет себе надстройку, а потом статью на хабр «как без VBA...»?
        Что вообще делает функция =ссылка()? Возвращает имя книги и листа? Если так, то зачем для этого надстройка? Все, что нужно есть здесь: =ЯЧЕЙКА(«имяфайла»;A1), останется допилить текстовыми функциями до нужного формата. Вот тогда можно смело говорить, что без VBA.

        Гистограмма, например, тоже требует надстройку
        Странно, у меня гистограммы в экселе из коробки работают. Или Вы какую-то конкретную гистограмму имели в виду?


  1. E_BEREZIN
    18.10.2022 14:57
    +1

    Файл по ссылке не открывается толком. Попробуйте в браузере открыть ссылку


    1. Robastik Автор
      18.10.2022 15:53

      Chrome на Андроиде скачал ссылку без проблем.

      Может на почту?


      1. E_BEREZIN
        18.10.2022 16:41

        
при открытии на ПК происходят какие-то вычисления (крутится колесико на вкладке) и зависает
        при открытии на ПК происходят какие-то вычисления (крутится колесико на вкладке) и зависает


        1. qyix7z
          18.10.2022 17:00
          +2

          Я подождал, дождался когда отлипнет, затем Файл — Скачать.


          1. Robastik Автор
            18.10.2022 17:41

            Можно скачивать не открывая.