Парсинг данных в эксель и построение розы ветров в полуавтоматическом режиме, часть 1.
В прошлом семестре мне необходимо было сделать интересную, но в то же время довольно рутинную задачу — начертить розу ветров. Так как вручную фильтровать и выписывать данные, а после объединять это все для построения месячных и годового графиков мне совсем не хотелось, я избрал другой путь и решил эту задачу через прекрасный и всемогущий Microsoft Excel. Ниже будет представлено пошаговое решение, от парсинга данных в эксель до построения розы ветров, приятного чтения!
Что такое роза ветров и как ее простроить? По каким исходным данным работать?
Роза ветров — это векторная диаграмма с помощью которой можно определить направления и скорость ветров в точке наблюдений. Для решения поставленной задачи имеются данные с сайта гисметео, из этих данных необходимо получить:
- Определить количество ветреных дней по каждому из направлений (С, СВ, В и тд.) за месяц и за год, вычислить среднюю скорость для каждого из направлений.
- Розы ветров для каждого месяца (рис.1).
- Годовую розу ветров (рис.2).
С основной задачей определились, но что делать теперь, с чего начать? Для облегчения поиска решений я разбил основную задачу на несколько подзадач:
- Парсинг данных в эксель с сайта гисметео, дневник погоды
- Подсчет количества ветреных дней и дней со штилем за месяц, и расчет средней скорости ветра для каждого из направлений
- Выбор наиболее подходящего графика и построение месячной розы ветров
- Создание сводной таблицы по количеству ветреных дней, дней со штилем и скорости этих ветров за год
- Построение годовой розы ветров
1. Парсинг данных в эксель
Для загрузки данных с сайта в эксель необходимо воспользоваться командой «получить данные из интернета». Вкладка «Данные» -> «получить данные из интернета»
Далее появится окно ввода URL-адреса, в которое необходимо вбить адрес сайта с которого необходимо получить выгрузку данных
Далее появится такое окно с готовой таблицей, сортируем данные, удаляя все лишнее. Остаются колонки: число, ветер дневной (вечерний ветер можно не учитывать)
Теперь разделяем столбец с направлением ветра на само направление и скорость ветра, для дальнейшего анализа. Используем команду «Текст по столбцам». Вкладка «Данные» -> «текст по столбцам». В первом шаге указываем пробел как разделитель, вторым шагом разделяем столбец со скоростями ветра, используем символ-разделитель другой: м
С парсингом данных и их подготовкой закончили, теперь можно переходить к подсчетам.
2. Подсчет количества ветреных дней и дней со штилем за месяц, и расчет средней скорости ветра для каждого из направлений
Для начала формируем таблицу из трех столбцов:
- Направление ветра, указываем здесь все направления от северного до северо-западного и штиль
- Количество ветреных дней
- Средняя скорость
Количество ветреных дней из столбца "Колво" подсчитывается по следующей формуле:
=СЧЁТЕСЛИ($C$5:$C$35;G6)
Команда =СЧЁТЕСЛИ() подсчитывает количество ячеек в диапазоне удовлетворяющих условию. В данном примере диапазоном является $C$5:$C$35, а условием ячейка G6. То есть в диапазоне направлений ветров ищется совпадение по северному направлению найденное количество совпадений выводится. После эта формула растягивается на все нижележащие ячейки от H6 до H14.
Следующим шагом считается средняя скорость:
=(СУММЕСЛИ($C$5:$C$35;G6;$D$5:$D$35))/ЕСЛИ(H6>0;H6;1)
Команда =СУММЕСЛИ($C$5:$C$35;G6;$D$5:$D$35) ведет поиски в столбце «Ветер» ячейки: C5:C35, по ячейке G6 (поочередно сравнивая значение в ячейке G6 с ячейками из диапазона C5:C35), если значения из ячеек C5:C35 совпадают со значением ячейки G6 то значения в столбце «Ск-ть» D5:D35 , находящиеся напротив ячеек удовлетворяющих условию суммируются. В рассматриваемом примере в ячейке G6 находится значение «С» (северное направление ветра), если сравнить это значение со значением первой ячейки диапазона C5 «ЮВ», то формула выдаст «ложь» и не будет суммировать эту ячейку, если далее продолжить сравнение то все ячейки до C12 будут выдавать «ложь», ячейка C12 равна G12, следовательно находящаяся правее ячейка D12 будет внесена в сумму, все дальнейшие ячейки не будут удовлетворять заданному условию и не будут внесены в сумму.
ЕСЛИ(H6>0;H6;1) вторая часть формулы выбирает ненулевые значения количества ветреных дней, а нулевые значения заменяет единицей, чтобы не вызвать ошибок и произвести корректный расчет. То есть если значение исследуемой ячейки больше нуля ЕСЛИ(H6>0), то в числителе остается исследуемое число ЕСЛИ(H6>0;H6),иначе оно заменяется единицей ЕСЛИ(H6>0;H6;1)
Ниже приведена схема подсчета средней скорости для северного и западного направлений.
*Так же можно посчитать среднюю скорость не через среднее арифметическое, а используя медиану, такой метод подсчета позволит отсечь аномально высокие/низкие значения и получить более точный результат.
Очень полезная статья: http://mathprofi.ru/moda_mediana_generalnaya_i_vyborochnaya_srednyaya.html#mo
Формула по которой можно посчитать среднюю скорость через
медиану:=ЕСЛИОШИБКА(МЕДИАНА(ЕСЛИ($C$5:$C$35=G6;$D$5:$D$35));0)
3. Выбор наиболее подходящего графика и построение месячной розы ветров
Наиболее подходящей из-за простоты оформления мне показалась лепестковая диаграмма. Диаграммы находятся во вкладке «Вставка» -> «Рекомендуемые диаграммы» -> «Все диаграммы» -> «Лепестковая»
Далее нужно нажать левой кнопкой мыши на появившуюся пустую диаграмму и кликнуть «Выбрать данные»
В появившемся окне «Выбор источника данных» нажать на «Добавить» под надписью «Элементы легенды»(ряды), написать любое имя ряда и в колонке «значения» выбрать столбец количества ветреных дней, все значения кроме количества дней со штилем (Рис.3.2.) Далее нажать ОК и в правом столбце «Подписи горизонтальной оси»(категории) изменить на значения названий направлений(Рис.3.3)
Диаграмма заполнена и визуализирует все данные осталось только настроить внешний вид на свой вкус и повторить вышеприведенные операции для всех оставшихся месяцев
