В первой части иллюстрированной инструкции по проведению расчета величины скидки за первый и последний этаж был показан порядок сбора данных с сайтов объявлений силами 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 нет.
Кликаем Добавление столбца → Настраиваемый столбец:
Назовем столбец Метр.
Добавим формулу для расчета удельной цены одного квадратного метра:
Цена м2 = Цена квартиры / Общую площадь
В завершение → Закрыть и загрузить в новый лист. Новый лист переименуем в 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. Что дальше
В ближайшее время материал будет дополнен результатами определения скидок для других групп этажности и для пятиэтажек в отдаленном районе того же города (Брянск). Подробнее о подготовленных для этой цели выборках в первой части. Полученные результаты будут сопоставлены и будет сделан главный вывод об универсальности скидки для крайних этажей в пределах одного населенного пункта в конкретном историческом моменте. Финальный файл xlsx также будет предоставлен для скачивания.