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

Это продолжение статьи с описанием построения розы ветров в Ms excel, осталось самое сложное, но самое интересное.

5. Построение годовой розы ветров

Создание сводной таблицы

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

1. Количество ветреных дней определяется простым сложение по каждому листу, пример для северного направления ветра: =СУММ(Январь!H7+Февраль!H7+Март!H7+Апрель!H7+Май!H7+Июнь!H7+Июль!H7+Август!H7+Сентябрь!H7+Октябрь!H7+Ноябрь!H7+Декабрь!H7) Для ссылки на лист используется формула !имя листа
2. Среднюю скорость ветра определил через медиану по формуле: =ЕСЛИОШИБКА(МЕДИАНА(Январь!I7;Февраль!I7;Март!I7;Апрель!I7;Май!I7;Июнь!I7;Июль!I7;Август!I7;Сентябрь!I7;Октябрь!I7;Ноябрь!I7;Декабрь!I7);0), ЕСЛИОШИБКА добавил для отображения 0 в строке штиль.

Отображение штиля на графике

Если в году были дни со штилем, их необходимо отобразить на графике в виде квадрата с половиной диагонали n, равной количеству штилевых дней (рис.1)

Для получения штилевого квадрата необходимо подготовить таблицу и в процессе ее заполнения решить небольшую геометрическую задачу (рис. 2).

Квадрат штиля состоит из 8 треугольников, у каждого треугольника известна 1 координата, это точка 0, необходимо найти оставшиеся координаты треугольников и занести данные в таблицу. Наверху таблицы и слева указаны направления ветра, с-св(север, северо-восток) это значит что треугольник лежит в пределах северного и северо-восточного направлений, а колонка слева указывает отрезок какой длины должен быть проложен в выбранном направлении.

Разберу на примере 1 колонки: выбранный треугольник лежит в пределах северного и северо-восточного направлений, длина отрезка, откладываемого в северном направлении равна 23 единицам- «n» это полудиагональ квадрата, длина отрезка откладываемого в направлении северо-востока равна n*sin45°=$H$14*SIN(РАДИАНЫ(45))(рис.3)

Для построения была использована лепестковая диаграмма, но можно использовать точечную (x,y) диаграмму, в чем то это будет проще, но решение изменится.

Таким образом заполняется вся таблица «штиль»

Создание таблицы направлений ветра

Рядом создается аналогичная таблица для направлений ветра. Чтобы правильно отобразить направления ветра, необходимо по каждой стороне света от границы квадрата штиля отложить отрезки длиной, равной количеству ветреных дней в выбранном направлении (0l=0n+nl). Каждый луч розы ветров состоит из двух треугольников(рис.4).

Заполнение таблицы

Для начала предлагаю заполнить ячейки по основным направлениям ветра, так как для их заполнения нужно меньше всего вычислений. Приведу пример на основе северного направления. Точка 1 находится на расстоянии n*sin45°=$H$14*SIN(РАДИАНЫ(45)) аналогично выше вычисляются длины отрезков откладываемых в промежуточных направлениях (св, юв, юз, сз). Точка 2 вычисляется путем сложения количества ветреных дней в северном направлении и количества штилевых дней (рис.6), но эти вычисления справедливы при ситуации когда количество дней со штилем > 0. Если в году каждый день будут дуть ветра в разных направлениях, то точка 2 будет откладываться от нуля на расстояние равное количеству ветреных дней, а не от крайних точек квадрата штиля, а точка 1 будет вычисляться по такой же формуле, но от произвольного числа. Пример формул:

=ЕСЛИ($H$14>0;$H$14*SIN(РАДИАНЫ(45));L9*SIN(РАДИАНЫ(45))) вычисление точки 1

=ЕСЛИ($H$14>0;$H$6+$H$14;$H$6) вычисление точки 2 (рис. 7)

Цифра в ячейке L9 позволяет регулировать отношение длины луча розы ветров к ширине при количестве штилевых дней = 0. (рис. 8)

Далее заполняются все ячейки по основным направлениям.

Заполнение ячеек промежуточных направлений

Точка 1 вычисляется путем сложения количества ветреных дней в северо-восточном направлении и количества штилевых дней, аналогично вышеприведенным вычислениям здесь присутствует условие количества штилевых дней.

=ЕСЛИ(H14>0;($H$7+$L$5);H7)

0,5 ст. кв=$H$14*SIN(РАДИАНЫ(45))

Для того чтобы найти точку 2 необходимо построить треугольник ABC и найти точку пересечения стороны AC с диагональю квадрата штиля. Получаем прямоугольный треугольник с биссектрисой BD. Длина биссектрисы BD (x) является искомой величиной.

Биссектриса делит треугольник ABC на два треугольника BCD и ABD, стороны AB (a) и BC (b) известны (рис.11).

Представлю площадь треугольника ABC как сумму треугольников BCD и ABD

S = ½*ab = ½ax*sin45° + ½bx*sin45°

½*ab = ½ax*(√2/2) + ½bx*(√2/2)

ab = (x*√2*(a+b))/2

x = 2ab/(√2*(a+b))

DB (x) = 2ab/(√2*(a+b))

Следовательно формула в экселе с учетом условия будет выглядеть следующим образом:

=ЕСЛИ(H14>0;(2*$L$5*P22)/(($L$5+P22)*КОРЕНЬ(2));(2*$L$9*P22)/(($L$9+P22)*КОРЕНЬ(2)))

Теперь можно окончательно заполнить таблицу «направление ветра» одна колонка - один треугольник на графике. Для правильного и красивого отображения годовой розы ветров важно соблюдать иерархию рядов в данных диаграммы, вышележащий ряд будет перекрывать нижележащий, хорошо подойдет аналогия со слоями в фотошопе. Для изменения иерархии рядов данных необходимо нажать правой кнопкой мыши на диаграмму -> «Выбрать данные» -> выбрать необходимый ряд и при помощи стрелок передвинуть его на нужный уровень иерархии(рис.13). После настройки иерархии диаграмма будет полностью готова.

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