В первой части иллюстрированной инструкции по проведению расчета величины скидки за первый и последний этаж был показан порядок сбора данных с сайтов объявлений силами Excel и первичный анализ собранных данных. Исходные данные разбиты по группам этажности

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

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

В результате проведенной подготовки получены данные с относительно низким уровнем шума.

Файл xlsx со всем процессом подготовки данных доступен для скачивания.

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

1. Предварительные замечания

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

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

Целью очистки является получение однородной выборки данных.

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

Пояснение 1.

В Excel есть неудачно переведенное меню:

Обычно на русском это называется столбчатая или столбиковая диаграмма (график). Гистограммой в действительности называется совершенно другой способ визуализации, хотя и внешне похожий. Настоящая гистограмма в Excel 2021 находится здесь:

В других версиях Excel ищите гистограмму в другом месте.

В целом процесс анализа и очистки заключается в последовательном поиске в выборке и удалении тех данных, которые вносят значительную неоднородность по разным ценообразующим факторам. Примерная последовательность действий будет подробно показана для выборки 4-7.1. Для других выборок – по аналогии.

Пояснение 2.

В первой части инструкции принято обозначать выборки по группам этажности. Например, 4-7.1 обозначает первый территориальный кластер для зданий с этажностью от 4 до 7.

Пояснение 3.

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

=@~~'C:\Users\Den\AppData\Roaming\Microsoft\AddIns\Robastik.xlam'!~~Дистанция([@Широта];[@Долгота];'9.4-7'!$C$8;'9.4-7'!$D$8)<'9.4-7'!$B$9

Должно остаться:

=@Дистанция([@Широта];[@Долгота];'9.4-7'!$C$8;'9.4-7'!$D$8)<'9.4-7'!$B$9

После этого формула с функцией будет работать.

Пояснение 4.

В первой части инструкции приведены скриншоты для Excel 2013 и 2016. Во второй части скриншоты показывают Excel 2021.

2. Перестроение выборки

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

Исходные данные находятся на листе 4. Формулы для фильтрации выборок находятся на листах 7.

Копируем формулу с листа 7.4-7 из первой строки колонки Кластер 2-1 в первую (после строки заголовка) строку листа 4 в первой пустой колонке.

=@Дистанция([@Широта];[@Долгота];'9.4-7'!C$8;'9.4-7'!D$8)<'9.4-7'!B$9

[@Широта] и [@Долгота] должны быть выделены цветом:

Если выделения цветом нет, то вставляем вместо них те же [@Широта] и [@Долгота], но копируя их с листа 4.

Далее в формуле использованы определяющие территориальный кластер данные на листе 9.4-7. Чтобы ссылки не сбились, добавим знак $ в обозначение колонок:

'9.4-7'!$C$8;'9.4-7'!$D$8)<'9.4-7'!$B$9

После вставки формулы колонка автоматически заполнится. Переименуем колонку именем выборки 4-7.1, сначала изменив ее формат на текстовый.

Кликаем Данные → Из таблицы.

Открылась форма для создания запроса. Изменим имя запроса на 4-7/1.

Удалим лишние столбцы: Опубликовано, itemId, Офер, Рынок.

В колонке 4-7.1 снимаем галку FALSE → OK:

В фильтре колонки Этажность нужно оставить галки только у 4-7. Этажность 4 может быть не загружена, необходимо прокрутить ползунок как можно ниже перед открытием фильтра. Этажность 7 в выборку не попала и поэтому галки 7 нет.

Кликаем Добавление столбца → Настраиваемый столбец:

Назовем столбец Метр.

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

Цена м² = Цена квартиры / Общую площадь

В завершение → Закрыть и загрузить в новый лист. Новый лист переименуем в 4-7.1.

Данные → Запросы и подключения. Найдем наш запрос 4-7/1 и сравним количество загруженных строк с количеством строк на листе 7.4-7 при отключенной ЛОЖЬ в фильтре Кластер 2-1. Количество строк одинаковое, т.е. получена выборка тех же точек, но с дополнительными столбцами данных.

Аналогично перестраиваем остальные выборки.

3. Очистка выборки

Построим гистограмму цены квадратного метра:

Гистограмма похожа на гистограмму нормального распределения, но с существенной асимметрией и длинным хвостом.

Посмотрим, как распределена выборка по площади. Для этого построим гистограмму по данным колонки общей площади:

По площади выборка неоднородна. Необходимо оставить в выборке однородную часть и удалить остальное.

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

Однородную часть можно оценить диапазоном 35 – 65 кв.м. общей площади квартиры.

Ограничим нашу выборку этими пределами.

Открыть лист 4-7.1 → Данные → Запросы и подключения → ПКМ 4-7/1 → Изменить → Добавление столбца → Настраиваемый столбец:

Назовем столбец ПлощадьБольше и добавим формулу:

Общая площадь > 35

Аналогично добавим столбец ПлощадьМеньше и укажем 65:

В фильтрах обоих столбцов снимем галку ЛОЖЬ:

В завершение → Закрыть и загрузить.

Гистограмма общей площади приобрела следующий вид:

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

Добавим пару метров в ПлощадьМеньше для демонстрации техники тюнинга параметров отбора.

ПКМ 4-7/1 → Изменить → Примененные шаги → Добавлен пользовательский объект → пикто настройки:

В открывшейся форме Настраиваемый столбец меняем 65 на 67 → ОК → снимаем FALSE в фильтре колонки → Закрыть и загрузить.

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

Гистограмма площади получила новый карман справа:

Поскольку карман слишком мелкий, заключаем ошибочность сделанного изменения и возвращаем обратно 67 на 65.

Гистограмма цены квадратного метра при этом стала немного более симметричной, но все еще имеет большие хвосты:

Обрежем правый хвост начиная с кармана, который полнее предыдущего (на диаграмме Исходный метр): 83235.

ПКМ 4-7/1 → Изменить → Добавление столбца → Настраиваемый столбец:

Снимаем галку FALSE в фильтре колонки ПравыйХвост → Закрыть и загрузить.

Гистограмма стала такой:

Обрежем левый хвост предпоследним значимым карманом: 34935.

ПКМ 4-7/1 → Изменить → Добавление столбца → Настраиваемый столбец:

Снимаем галку FALSE в фильтре колонки ЛевыйХвост → Закрыть и загрузить.

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

Посмотрим, что представляет собой выборка в разрезе количества комнат.

Данные → Из таблицы → выделить колонку Комнат → Группировать по → Базовый → ОК → выделить колонку Комнат → сортировка по возрастанию → Закрыть и загрузить в новый лист. Новый лист назовем 4-7.1.комнаты:

Очевидно, что типичными представителями являются в нашем случае 2-х и 3-х комнатные квартиры, а 1-но, 4-х и 5-ти комнатные явно лишние. Удалим их из выборки.

ПКМ 4-7/1 → Изменить → в фильтре колонки Комнат оставить галки только у 2 и 3:

ОК → Закрыть и загрузить.

Далее аналогично рассмотрим конструктив:

Также аналогично оставим Панель и уберем все остальное:

Результат:

В завершение подчистим последний правый карман, т.к. он слишком мелкий: 80145.

Окончательно выборка имеет следующий вид:

4. Расчет скидки

Посмотрим распределение этажей в выборке:

Лист 4-7.1 → Данные → Из таблицы → выделить колонку Этажность → Группировать по → Базовый:

Вся этажность кроме 5 была вычищена по другим параметрам. Сохранять результат не станем.

Аналогично смотрим распределение по этажам:

Для наглядности то же диаграммой:

Сразу обращает на себя внимание то, что доля первого и последнего этажей заметно больше, чем 2-ых, 3-их и 4-х. На пару они делят примерно 50%, тогда как оставшиеся делят свои 50% на троих.

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

Каков механизм этого перекоса? Давайте поищем ответ позже - в размере скидки.

Посчитаем среднюю цену на каждом этаже:

Лист 4-7.1 → Данные → Из таблицы → выделить колонку Этаж → Группировать по → Подробнее → имя: СредняяЦена, операция: Среднее, столбец: Метр

→ ОК → выделить колонку Этаж → Сортировать → Закрыть и загрузить в новый лист. Новый лист назовем 4-7.1.Скидка.

То же самое диаграммой:

То же таблицей:

Читается таблица следующим образом:

Второй этаж больше пятого на 11,1% и пятый меньше второго на 10,0%.

Знак указывает на отношение: больше пятого на +11,1%, меньше второго на -10,0%.

5. Механизм перекоса

Выше было определено, что рыночные доли первого и последнего этажей составляют 23% - 26%, тогда как доли других этажей 14% - 17% - 20%.

Поскольку во всех пятиэтажках одинаковое количество всех этажей и отсутствует законное обязательство чаще продавать первые и последние этажи, то и рыночные доли всех этажей должны быть около 20%. На выборке в 100+ объектов для каждого этажа погрешность не может составлять 6% в большую (последние этажи) и в меньшую (четвертые этажи) сторону.

Интерес представляет интерпретация разницы этих долей от естественного уровня в 20%.

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

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

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

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

Обратная ситуация с четвертыми этажами. Они на 4,5% дешевле третьего. Разница существенная, хотя, по сути, на практике она заключается только в преодолении одного пролета. Компенсация более чем достаточная и по этой причине четвертый этаж продается заметно легче остальных.

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

6. Где главное?

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

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

На практике для определения скидок и премий используются специальные справочники. На сайте наиболее известного такого справочника приводится следующая информация:

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

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

*Выделено в оригинале.

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

Такая практика считается не просто приемлемой, но фактически воспринимается на официальном уровне как единственно возможная. На это указывает сама практика (с того же сайта):

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

Надеюсь, приведенные объяснения умягчат сердца statistical nazi и вы не насыплете мне минусов в карму ?

7. Второй кластер пятиэтажек

В первой части были сформированы два кластера для этажности от 4 до 7:

Выборка первого кластера в нашем xlsx обозначена 4-7.1 и второго кластера 4-7.2. Два кластера предназначены для того, чтобы можно было сравнить результаты анализа двух выборок, различающихся местом расположения.

В процессе очистки выборок от выбросов по ценообразующим факторам в остались только пятиэтажки.

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

Далее изложение относится к выборке 4-7.2 (на карте кластер 2).

Состав выборки по конструктиву отличается от кластера 1. Если в первом случае легко было пожертвовать 11% кирпичных объектов, то в кластере 2 кирпич занимает 40% выборки:

Поэтому рассмотрим панель и кирпич по отдельности.

Доля крайних этажей в панели также немного недотягивает до половины. Но доля 4-х этажей больше доли последних. Поэтому предположение о недостаточности скидки для продажи пятого этажа не выглядит надежным.

Соотношение средних цен этажей во 2-м кластере хорошо совпадает с соотношением в 1-м кластере:

Небольшое отличие средних цен на этажах в 1 и 2 кластерах может объясняться статистической погрешностью.

Кирпичная часть выборки значительно отличается от соотношения в панельном сегменте.

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

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

Соотношение средних цен на этажах в кирпиче также кардинально отличается:

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

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

На 2 и 3 этажах на ≈5% дороже панель, на 4 и 5 – также на ≈5% дороже кирпич. Первый этаж намного привлекательнее в панели.

Стоить отметить, что полученные различия между сегментами разных конструктивных материалов могут объясняться не самими этажами, а другими признаками, которые проявляются через этажи. Например, в кирпичных домах может быть другое устройство цоколя, из-за чего он хуже проветривается и сырость достает до 3-го этажа. Или схема отопления может не обеспечивать достаточно тепла на верхних этажах панели, но особенно хорошо прогревать первый этаж.

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

8. Этажность 8 – 12

Для этажей с 8 по 12 появляется новый материал – монолит. Однако, объектов в этом сегменте недостаточно для исследования. Поэтому рассмотрим только сегменты панели и кирпича:

В кирпиче этажность представлена в основном 9 и 10:

Поэтому для лучшей однозначности результатов удалим 8, 11 и 12.

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

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

Теперь все 9-е этажи относятся только 10-этажкам, а некоторые 10-е на самом деле последние этажи 9-этажек.

В результате имеем большую долю последних этажей и маленькую долю первых:

То же самое было и в кирпичных 5-этажках.

Цена этажа в кирпиче здесь имеет еще более сложный рисунок:

Последний этаж тут явно в лидерах, а не в аутсайдерах. Та же ситуация и в кирпичных 5-этажках.

Если особую роль 2-го этажа уже видели в 5-этажках, то 7 этаж сложно было заподозрить в претензиях на лидерство. 7-й этаж представлен в выборке 70 объектами (14% выборки), т.е. погрешностью результат точно не является.

Интерес вызывает и отношение рынка к 4 этажу. На этом этаже не только самая маленькая цена, но и самая маленькая доля в выборке – 5%.

Рассмотрим далее панельный сегмент 8-12.

В отличие от кирпича, в панели значительная доля 3-комнатных квартир:

Этажность также представлена в основном 9 и 10 с большим преобладанием 10:

Первый и последний этажи в панели поменялись ролями, а 4 и 7, наоборот, их сохранили:

9. Этажность 13 – 19

У зданий с этажами с 13 по 19 появляется новый конструктивный материал:

Объединим монолитный и монолитно-кирпичный в одну категорию.

Кирпичный сегмент представлен этажностями с 14 по 17:

Здесь не осталось и намека на особую роль 2-го этажа. Роль аутсайдера перешла к 6 этажу (85 точек). Крайние этажи не представляют собой ничего примечательного. Этажей так много, что кратко охарактеризовать соотношение их цен невозможно.

В монолитном сегменте преобладает та же этажность 14 – 17:

Соотношение средней цены на этажах заметно отличается от кирпичного сегмента:

10. Вместо заключения

Эта инструкция по использованию Power Query в Excel задумывалась с целью показать простоту и легкость манипулирования данными. Еще несколько лет назад подобные манипуляции требовали, как минимум, знания языка Power Query М, что требовало от исследователя специальной подготовки.

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

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

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

Файл с данными xlsx можно скачать здесь.

Расчет скидки за первый и последний этаж в Excel (Часть 2)