Визуализация геоданных на карте
Если ваша компания имеет филиалы по стране или продает не только в пределах МКАД, то рано или поздно вы столкнетесь с задачей наглядно отобразить числовые данные из Microsoft Excel (продажи, заявки, объемы, клиентов) на географической карте с привязкой к конкретным городам и регионам. Давайте вкратце пробежимся по основным способам визуализации геоданных, которые существуют в Excel.
Способ 1. Быстро и бесплатно — компонент Bing Maps
Начиная с 2013 версии в Excel встроен магазин приложений, т.е. появилась возможность докупать, скачивать и устанавливать дополнительные модули и надстройки с недостающими функциями. Один из таких компонентов как раз и позволяет наглядно отображать числовые данные на карте — он называется Bing Maps и, что особенно приятно, абсолютно бесплатен. Для его установки откройте вкладку Вставка — Магазин (Insert — Office Apps):
После вставки компонента на листе должен появится динамический контейнер с картой. Для визуализации на карте вашей информации нужно выделить диапазон с геоданными и нажать кнопку Показать местоположения (Show Locations):
При необходимости в настройках (иконка с шестеренкой в правом верхнем углу компонента) можно поменять цвета и тип отображаемых диаграмм:
Также возможно быстро отфильтровать города, отобразив только нужные (значок воронки в правом верхнем углу компонента).
Спокойно можно привязываться не только к городам, но и к другим объектам: областям (например, Тульская область), автономным округам (например, Ямало-Ненецкий АО) и республикам (Татарстан) — тогда кругляш диаграммы будет отображен в центре области. Главное, чтобы название в таблице совпадали с подписями на карте.
Итого в плюсах этого способа: легкая бесплатная реализация, автоматическая привязка к карте, два типа диаграмм, удобная фильтрация.
В минусах: нужен Excel 2013 с доступом в интернет, нельзя выделять области и районы.
Способ 2. Гибко и красиво — режим карты в отчетах Power View
Некоторые версии Microsoft Excel 2013 идут в комплекте с мощной надстройкой визуализации отчетов, которая называется Power View и позволяет (помимо всего прочего, а умеет она много!) наглядно отображать данные на карте. Для активации надстройки откройте вкладку Разработчик (Developer) и щелкните по кнопке Надстройки COM (COM Add-ins) . В открывшемся окне поставьте галочку напротив Power View и нажмите ОК. После всех этих манипуляций на вкладке Вставка (Insert) у вас должна появиться кнопка Power View.
Теперь можно выделить диапазон с исходными данными нажать на эту кнопку — в вашей книге будет создан новый лист (больше похожий на слайд из Power Point), где отобразятся выделенные данные в виде таблицы:
Превратить таблицу в географическую карту можно легко с помощью кнопки Карта (Map) на вкладке Конструктор (Design) :
Обратите особое внимание на правую панель Поля Power View — на ней, в отличие от примитивных Bing Maps, перетаскивая мышью названия столбцов (полей) из исходной таблицы и бросая их в разные области, можно очень гибко настраивать получившееся геопредставление:
- В область Местоположения (Locations) нужно закинуть столбец из исходной таблицы содержащий географические названия.
- Если у вас нет столбца с названием, но есть колонки с координатами, то их нужно поместить в области Долгота (Longitude) и Широта (Latitude) , соответственно.
- Если в область Цвет (Color) закинуть товар, то каждый пузырек будет кроме размера (отображающего общую прибыль по городу) детализирован на дольки по товарам.
- Добавление поля в области Вертикальных или Горизонтальных множителей(Dividers) разделит одно карту на несколько по этому полю (в нашем примере — по кварталам).
Если данных очень много, то на вкладке Power View можно включить специальную Область фильтров (Filters) , где с помощью привычных галочек можно выбрать какие именно города или товары нужно показать на карте:
Итого в плюсах: легкость использования и гибкость настройки, возможность дробить одну карту на несколько по категориям.
В минусах: Power View есть не во всех комплектациях Excel 2013, нет других типов диаграмм кроме пузырьковых и круговых.
Способ 3. Дорого и профессионально — надстройка Power Map
Это отдельная COM-надстройка для максимально тяжелых случаев, когда нужна сложная, профессионально выглядящая, анимированная визуализация большого количества данных на любой (даже пользовательской карте), причем с видео динамики процесса во времени. На этапе разработки она имела рабочее название GeoFlow, а позже была переименована в Power Map. К сожалению, в полноценном варианте эта надстройка доступна только покупателям либо полной версии Microsoft Office 2013 Pro, либо подписчикам корпоративных версий Office 365 с тарифным планом Business Intelligence (BI). Однако превьюшку этой надстройки товарищи из Microsoft дают скачать «на поиграться» совершенно бесплатно, за что им спасибо.
После скачивания и установки надстройку нужно подключить на вкладке Разработчик — Надстройки COM (Developer — COM Add-ins) аналогично Power View из предыдущего пункта. После этого на вкладке Вставка должна появиться кнопка Карта (Map) . Если теперь выделить таблицу с исходными данными:
. и нажать кнопку Карта, то мы попадем в отдельное окно надстройки Microsoft Power Map:
Если не вдаваться в детали (которых тут хватит на отдельный тренинг на полдня), то общие принципы работы с картой тут те же, что и в Power View, описанной выше:
- Размер столбиков определяется столбцом исходной таблицы (Выручка), который мы закинем в поле Высота в правой панели. Принцип подсчета, как и в сводных таблицах, можно менять в раскрывающемся списке поля:
- Чтобы детализировать каждый столбик продаж на отдельные товары, нужно закинуть поле Товар в область Категория (Category) .
- Можно использовать различные типы диаграмм (гистограммы, пузыри, тепловую карту, заливку областей) с помощью кнопок на правой панели:
- Если в исходных данных есть столбец с датами продажи, то его можно закинуть в область Время (Time) — тогда внизу появится ось времени, по которой можно перемещаться в прошлое-будущее и посмотреть процесс в динамике.
«Вау-моментом» надстройки Power Map можно, пожалуй, назвать предельную легкость создания анимированных видеообзоров на основе сделанных карт. Достаточно сделать несколько копий текущей сцены с разных углов просмотра и разным масштабом — и надстройка автоматически создаст 3D-анимацию облета вашей карты с акцентированием внимания на выбранных местах. Полученное видео потом легко сохраняется в mp4-формате в виде отдельного файла для вставки, например, на слайд Power Point.
Способ 4. Пузырьковая диаграмма с «доработкой напильником»
Самый «колхозный» способ из всех перечисленных, но зато работающий во всех версиях Excel. Построить пузырьковую диаграмму (Bubble Chart), отключить у нее оси, сетку, легенду. т.е. все, кроме пузырей. Затем вручную подогнать положение пузырей, подложив под диаграмму скачанное заранее изображение нужной карты:
Минусы этого способа очевидны: долго, муторно, много ручной работы. Да еще и вывод подписей к пузырям представляет собой отдельную проблему, когда их много.
Плюсы в том, что этот вариант будет работать в любой версии Excel, в отличие от следующих способов, где обязательно требуется Excel 2013. Да и подключение к интернету не требуется.
Способ 5. Сторонние приложения и надстройки
Раньше существовало несколько надстроек и плагинов для Excel, позволяющих с той или иной степенью удобства и красоты реализовать отображение данных на карте. Сейчас подавляющее большинство из них либо заброшены разработчиками, либо в стадии тихого отмирания — с Power Map конкурировать тяжело 🙂
Создание 3D-карты (Power Map) в MS Excel для визуализации географических данных
Во времена Excel 2013 появилась возможность добавить надстройку Power Map, с помощью которой данные можно изобразить на географической карте. Этот прием не только расширяет возможности визуализации, но также позволяет анализировать данные, осуществляя поиск закономерностей, имеющих региональное происхождение.
В Excel 2016 Power Map вживлена в программу и называется 3D-карта (3D-map). Сразу к делу. Допустим, есть коммерческие данные о деятельности компании в нескольких европейских странах.
Исходные данные лучше хранить в виде Таблицы Excel, а не обычного диапазона. Это позволит автоматически подхватывать новые значения при их добавлении. Активируем любую ячейку и во вкладке Вставка нажимаем 3D-карта.
Открывается окно создания и редактирования 3д-карты.
В верхней части окна находится лента, состоящая из одной вкладки Главная. Здесь находятся команды добавления, удаления, редактирования. По мере разбора примера рассмотрим, как ими пользоваться.
Слева находится панель со сценами карт. Выглядит как Power Point. Оно так и есть. Можно нарисовать несколько карт или их представлений и, как в презентации, включить слайд-шоу с красивыми переходами.
Справа панель управления полями данных, по которым рисуется карта. Здесь, примерно, как в сводной таблице, можно добавлять поля в разные области карты, перетаскивать, настраивать агрегацию, фильтры.
В центре мы видим карту текущей сцены (у нас она пока только одна) со всеми действующим настройками. По умолчанию это глобус. По нему можно перемещаться, как в навигаторе. Для этого используется мышь и/или клавиатура. Справа внизу кнопки навигации.
Стрелками перемещаемся вправо/влево и меняем угол наклон карты (глобуса). Плюс/минус – это приближение/удаление карты.
При наезде появляются детали (дороги, реки). Однако внешний вид лучше настраивать по факту, когда данные уже нанесены на карту.
Добавление данных на 3D-карту
Для добавления данных предусмотрена панель справа.
Пойдем сверху вниз. На карте, как в фотошопе, можно использовать несколько слоев. Каждый слой показывает определенную информацию и настраивается независимо. Слой можно переименовать в более понятное название, чем Слой 1, Слой 2 и т.д. Например, первый слой – это Продажи, второй – Реклама. Слои можно скрывать, редактировать, удалять (см. пиктограммы справа от названия). Если нужно создать еще один слой, нажимаем Добавить слой.
Ниже в разделе Данные выбирается тип карты. Уровни значений могут отображаться в виде столбиков, столбиков с накоплением, столбиков рядом, пузырьков, тепловой и региональной карты.
Затем идут поля для заполнения карты.
Расположение. В этой области указывается поле с географическим признаком (геолокация). Excel всячески пытается нам помочь и сам выбирает столбец, где находится географический признак. Однако угадывает не всегда. Вот и в нашем примере выбран признак региона, хотя нужны страны.
Крестиком справа удаляем ненужное поле. Затем нажимаем Добавить поле и выбираем столбец со странами.
Для Excel важным является тип географической переменной. Это могут быть страны, города, адреса, координаты и др. В нашем простом примере Excel сам присвоил тип Страна/регион. Спасибо экселю за помощь, но иногда тип приходится указывать самостоятельно. Тогда открываем список (рядом с названием) и выбираем подходящий вариант.
Те названия, которые Excel распознал, сразу отмечаются на карте (если сам показатель еще не выбран, то в виде точек), а чуть выше области Расположение показан процент распознанных названий. В идеале должно быть 100% (см. скриншот выше), но может быть и меньше (так часто бывает, если название написано с ошибкой, если это адрес, малый город, деревня). Можно нажать на процент и посмотреть, какие названия эксель не понимает. Если необходимо, исправьте названия и затем нажмите Обновить данные.
Имейте ввиду, что даже при индикаторе 100% эксель не всегда правильно определяет точку на карте. Это связано с дублированием наименований. Например, город Курчатов есть в России и Казахстане. Поэтому следует визуально проконтролировать, чтобы Excel не закинул вас на другой конец карты. Если это произошло, добавьте в исходные данные (и на карту) еще один столбец, который указывает принадлежность города (улицы и т.д.) к более крупному региону (области, стране). Не забудьте затем обновить 3D-карту.
В нашем примере все страны распознались на 100%, что сразу показывается на карте – это столбцы с нулевой высотой.
Итак, на местности сориентировались. Переходим к данным для визуализации.
Высота. В этой области выбирается показатель, который нужно отразить на карте (или несколько). Выберем продажи.
Т.к. по умолчанию в качестве типа выбрана гистограмма (с накоплением), то на карте (глобусе) появятся столбцы.
Выглядит очень круто, но ужасно с точки зрения наглядности. Вообще говоря, любая 3d-гистограмма – это зло, т.к. она искажает реальные пропорции. Тем более, если смотреть сверху. Если вдруг вам нужны именно вертикальные столбики, то хотя бы измените угол обзора.
Ненамного лучше, но пока оставим так.
Исходные данные не всегда представлены в виде компактной (сведенной) таблицы. Источником может быть и база данных, где каждая строка отражает сделку и, соответственно, многие признаки повторяются, в том числе и географический. В этом случае значения нужно агрегировать определенным способом. Как правило, используется суммирование. Задается в выпадающем списке около названия (как в сводной таблице).
Другими словами, все значения выбранного показателя, соответствующие определенной стране, суммируются, а результат показывается на карте. Как видно, можно агрегировать, рассчитывая среднее, минимум, количество и др. Скажем, у вас карта города, где показаны цены на недвижимость. Нужно отразить минимальную цену в некотором районе. Это можно сделать прямо из исходных данных, не прибегая к дополнительным расчетам.
Категория. Довольно часто данные разбиваются на группы. В нашем примере весь рынок сбыта разделен на две части: Восток и Запад и показан в столбце Регион. Выберем данное поле в качестве категории.
Столбики перекрасятся в соответствии с группировочным признаком.
В области Категория можно поместить только одно поле.
Время. Сюда помещают столбец, отражающий время, чтобы показать изменение данных в динамике. В нашем примере их нет.
Фильтры. Используется для фильтрации данных по некоторому полю (см. видеоролик ниже).
Параметры слоя. Здесь меняются настройки отображения. Например, для столбиков можно задать высоту, толщину, цвет, прозрачность. Также показывать ли нули и отрицательные значения. Будьте осторожны с внесением изменений – на 3D-карте нет команды отмены последнего действия, т.е. Ctrl + Z не работает.
Настройки внешнего вида 3D-карты
Итак, с данными определись, однако выглядят они по умолчанию не очень хорошо. Нужно сделать их более наглядными. Универсальных правил здесь, конечно, нет, т.к. наглядность зависит от самих данных и того, что вы хотите в них показать. Однако рассмотрим основные настройки.
Глобус выглядит красиво, но плоская карта все же понятней. Для замены глобуса на карту используйте кнопку Плоская карта.
После ее нажатия, глобус развернется в обычную карту. Немного поправим масштаб и угол обзора.
Следующий момент – это подписи. На ленте есть специальная кнопка.
На карте появляются названия.
К сожалению, регулировать подписи нельзя. Они сами добавляются в соответствии с масштабом. Поэтому следует осторожно пользоваться этой опцией.
Можно еще выбрать тему карты. Например, сделать как будто это снимок со спутника или просто поменять цветовую палитру.
Однако это лишь регулировка внешнего вида карты. Перейдем к типам визуализации. Их несколько.
Как и в обычной гистограмме, для каждой категории (страны) можно отразить несколько показателей. Добавим в область Высота данные о затратах на рекламу. Однако перед этим придется очистить область Категория, т.к. категории можно применить только для одного показателя в области Высота.
Т.к. используется гистограмма с накоплением, то два показателя просто сложились, что нам не подходит. Поэтому выберем гистограмму с группировкой.
Получим следующий вид.
Красиво, но не очень понятно. Как вариант, на карту можно вывести плоскую диаграмму.
В настройках выбирается нужный тип: вертикальная, горизонтальная, с накоплением и рядом.
Диаграмма помещается прямо на карте.
Следующая визуализация – пузырьковая диаграмма. Диаметр кружка соответствует значению показателя.
Но при этом, т.к. у нас два показателя, видно соотношение между продажами и рекламой. Кстати, столбцов больше нет, карту можно повернуть под прямым углом (смотреть строго сверху). Цвета можно поменять в параметрах слоя (внизу в правой панели).
Тепловая карта отражает только один показатель, поэтому реклама исчезает автоматически. Чем больше красного, тем теплее и, соответственно, выше показатель.
Последний вариант – региональная карта. Наверное, самая подходящая для одного показателя по странам (или другим регионам). Здесь все понятно и просто. Чем темнее цвет, тем больше значение.
Цвет можно изменить в параметрах слоя. Когда-то я весь интернет облазил, чтобы нарисовать что-то подобное. Сегодня это делается за 5,6 секунды, возможно, быстрее.
Слои карты
Слои используются для совмещения разнородных типов визуализации. Скажем, нужно одновременно показать столбики по одному показателю и тепловую карту по другому. На одном слое это сделать невозможно. На разных – легко.
Назовем имеющийся слой Продажи и создадим новый Реклама.
В новом слое визуализируем затраты на рекламу, сделав их столбиками.
Теперь интенсивность заливки контура страны показывает продажи, высота столбика – затраты на рекламу. Можно добавить легенду.
Разумным будет акцентировать внимание на нужном регионе с помощью примечания. Правой кнопкой кликаем по элементу и вносим пояснение.
К сожалению, построение 3д-карты происходит в отдельном окне, ее нельзя поместить на лист Excel. Но если вам нужно отобразить карту именно на листе эксель или в power point, сделайте скриншот с помощью вот этого фотоаппарата на ленте.
Естественно, что связки с данными больше нет. Зато есть красивый рисунок. А данные легко обновить и снова вставить картинку.
Создание обзора
В 3D-карте можно создать обзор нескольких карт. Получится что-то похожее на Power Point, где с помощью слайдов (сцен) создается презентация (обзор).
Даже на нашей небольшой карте информации довольно много и без комментариев она не воспринимается. Хорошо бы по очереди показать отдельные показатели по странам. Можно конечно, построить много карт и просто менять картинки в презентации, рассказывая о том, на что обратить внимание, а можно сделать слайдшоу с эффектом полета над картой.
Итак, стоит задача отобразить данные по продажам и рекламе. Сделаем слайд-шоу. Вначале покажем продажи, затем добавим расходы на рекламу. Далее акцентируем внимание на отдельные страны. И в конце снова покажем весь рынок сбыта, но уже под прямым углом, заменив столбики рекламы на пузырьки. Таков полет нашей режиссерской мысли.
Вначале создадим те карты (сцены), которые, собственно, нужны. Первая карта – только продажи. Вторая – добавляем столбики рекламы (с измененным углом обзора карты). Далее перемещение по странам. Последняя – общий обзор, прямой угол обзора, замена столбиков пузырьками.
Чтобы переходы были плавными, сцены следует рисовать не с нуля, а копировать предыдущие (или следующие) и вносить в них изменения. Сразу настроим параметры сцены, чтобы и они также копировались.
Уменьшим длительность одной сцены до 3 секунд, длительность перехода – 2 секунды, и эффект перехода – Перелет. Вам же я рекомендую самостоятельно поэкспериментировать, чтобы добиться требуемого результата.
Теперь создадим новые сцены с помощью копирования.
Внесем изменения, расположив их в нужной последовательности на панели обзора. Кстати, сам обзор и сцены можно также переименовать, ну да ладно. У меня получилось примерно так.
Для того, чтобы посмотреть обзор, как презентацию, нажимаем на кнопку Воспроизвести обзор.
Воспроизведение обзора может несколько подтормаживать. Этот инструмент отнимает много ресурсов. Да и показать можно не на каждом компьютере (не у всех же стоит Excel последней версии). Поэтому данную презентацию можно записать в формате mp4.
В следующем окне выбираем разрешение (берите максимум) и, если надо, накладываем музыку из заранее заготовленного файла.
После обработки получим файл mp4. Теперь этот видеофайл можно залить на Youtube, Яндекс.диск или вставить в презентацию (что получилось, смотрите в видеуроке ниже).
Конечно, чтобы сделать красивую карту и обзор, придется немного постараться. Я лишь показал, как работать с 3D-картами в Excel. Так что дальше дело за вашим пониманием данных, идеей, как их отразить на карте и творческой мыслью при создании обзора.
Чтобы узнать о других возможностях 3D-карты (изображение адресов, динамика показателей и др.), а также изучить множество других инструментов Excel, приглашаю на мой онлайн курс «Продвинутый уровень Excel».
Как использовать функцию географии в Microsoft Excel
При работе с листами Microsoft Excel иногда может потребоваться добавить географические данные на лист. К счастью, Microsoft Excel позволяет сделать это за несколько простых шагов. Все, что вам нужно сделать, это преобразовать ваши данные в тип данных Geography. Это позволит вам связать свои данные с онлайн-источником данных.
В этой статье вы познакомитесь с процессами преобразования ваших данных в типы данных Geography, независимо от того, есть ли у вас бесплатная учетная запись Microsoft или учетная запись Microsoft 365.
Тип данных географии
При вводе географических данных в рабочие листы Microsoft Excel вы хотите, чтобы они были как можно более простыми. Что может быть лучше для этого, чем использовать географическую карту? Благодаря встроенной в Microsoft Excel функции географических данных вам не потребуется профессиональная помощь для разработки сложной картографической диаграммы.
После того, как вы примените типы данных к своим данным, они создают ссылки на информацию в облаке. Связывая данные на листе с облачными данными, вы получаете доступ к неограниченному объему данных, ценных для вашего проекта.
Вещи, которые вам следует знать
Вот несколько важных советов, которые следует учитывать при использовании этого инструмента:
- Перед использованием этих типов данных вам также следует проверить свои языковые предпочтения. Не забудьте добавить в языковые настройки английский, испанский, французский, итальянский или немецкий, чтобы они работали.
- Если вы вводите свои данные, а вместо этого Excel отображает вопросительный знак (?) , Вы должны знать, что он не может сопоставить ваши значения ни с чем в облаке. В этом случае вы можете дважды проверить и убедиться, что вы правильно написали все свои слова, прежде чем продолжить.
- Вы можете щелкнуть значок вопросительного знака (?), Когда Excel не может найти соответствие для ваших данных, чтобы вручную найти соответствующие данные. Вводя ключевые слова во всплывающее окно, вы также можете вручную найти свои совпадения.
- Добавление заголовка или легенды к диаграмме карты поможет вам сделать ваш чат более понятным для пользователей вашего рабочего листа. Excel позволяет форматировать карты диаграмм по своему усмотрению.
- Еще один полезный инструмент – кнопка « Стили диаграмм» , которая позволяет применять к диаграмме различные функции форматирования, делая ее намного более привлекательной.
- Наконец, вы можете изменить размер диаграммы в любое время. Вы также можете переместить его в разные места на листе или даже вставить на другой лист, если собираетесь реплицировать его содержимое.
Как создать ссылки на карту в Excel
Если вам интересно использовать встроенные ссылки на карту в Excel, вот руководство по успешному созданию ссылки на карту.
- Введите текст в соответствующие ячейки. Убедитесь, что ваши данные географические. Это означает, что это должна быть страна, территория, провинция или город в каждой из требуемых ячеек на вашем листе.
- Щелкните ячейки, содержащие данные, чтобы выбрать их.
- Если вы хотите облегчить работу с данными, создайте профессиональную таблицу , которая поможет вам управлять своими данными. Вы можете сделать это, нажав « Вставить» , а затем « Таблица».
- Щелкните вкладку Данные> География .
Как сделать карту цен в Excel без макросов и VBA
Считается, что Data Mining — это магическое снадобье из SQL, Python, Power BI и других волшебных компонент. Мало кто знает, что при правильном подходе с Data Mining может совладать офисный планктон с помощью одного лишь Excel.
Если вы абсолютно далеки от Data Mining, но хотите причаститься его таинств, это руководство в картинках по шагам сделано для вас. Особенно полезно тем, кто никогда бы даже не подумал сделать подобное самостоятельно.
Если вы владеете специальными инструментами для работы с данными, то будет интересно узнать ваше мнение о решениях без «рокет сайнс» (как о явлении в целом, так и о данном кейсе).
Тепловая карта цен в 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®ion=4562&room1=1&room2=1. Раздел квартир здесь будет в offer_type=flat, а регион – в region=4562. Скажем «фу» доске объявлений Ц за не самый удобный порядок формирования ссылок.
Готовые ссылки как есть копируем из адресной строки браузера (нажатием кнопок Ctrl+A и Ctrl+C) и вставляем в парсере нажатием кнопки Добавить ссылку. Для обеих ссылок можно указать один и тот же новый файл Excel, в который будут сохраняться объявления.
Чтобы код для парсинга доски объявлений А загрузился в Excel → в настройках парсера (расположены в Excel на вкладке Надстройки) ставим галочку у парсера доски объявлений А и выключаем галочки у сохранения фотографий из объявлений, у сохранения копии объявлений, у открывания номера телефона и у других ненужных опций. То же самое повторяем с настройками парсера доски объявлений Ц.
Теперь ссылки полностью готовы для загрузки объявлений. Нажимаем в меню парсера кнопку Старт и ждем около 20 секунд до загрузки первого объявления. Да, процесс совсем не быстрый и займет время. Можно уменьшить интервал запросов в настройках парсера до 10 или 5 секунд и иногда это даже прокатывает. Но обычно доски объявлений очень не любят ботов и сразу закрывают доступ к данным (бан). Конечно, эти ограничения можно обойти и загружать данные в 100 раз быстрее, но это дороже.
Загружаемые объявления выглядят примерно так:
Фрагмент листа Excel c загруженными парсером объявлениями Авито и ЦИАН
Таких строк может быть несколько тысяч. В нашем примере это около 5000 объявлений для Брянской области в октябре 2021.
Из множества данных нам понадобятся только широта, долгота, цена, общая площадь и офер: