Artem

Дипломная работа: от 3D-моделирования до Python-аналитики и экономики (Часть 2)

2026-02-23T10:47:40.967Z

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

1. Подготовка к моделированию

Еще до построения 3D-модели карьера я определил геологический индекс прочности (GSI) для различных участков карьера(доменов) и рассчитал эквивалентные параметры массива, выполнив переход от прочности на одноосное сжатие к модулю упругости через статистический параметр modulus ratio (MR). В работе опирался на фундаментальные труды:

2019-The-Hoek-Brown-failure-criterion-and-GSI-2018-edition

2006-Hoek-and-Diederichs-estimating-rock-mass-modulus

Выделение домено

После интеграции данных в расчетную модель я разбил ее на конечные элементы и провел расчет методом понижения прочностных характеристик (SRM). Результатом стали наглядные зоны пластических деформаций, поля полных перемещений и конечно, итоговый коэффициент запаса устойчивости FoS = КЗУ (по ФНП №439 Приложение 3, п 15-16).

Разбиение 3д модели на конечные элементы

Распределение пластических деформаций в карьере

Поля полных перемещений

2. Проектирование наблюдательной станции

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

Опорные реперы расположил в зоне минимальных смещений, за пределами влияния карьера.
Рабочие реперы расположил точечно в зонах пластических деформаций и максимальных смещений. Опираясь на рекомендации ВНИМИ, выбрал шаг между реперами 5-10-15 метров (в зависимости от величины расчетных перемещений).

Расположение наблюдательной станции

Схема глубинного контроля и расположение инклинометров

3. Автоматизация и геотехнические риски

В заключении я разработал программу автоматизированного мониторинга. Базой послужил Приказ Ростехнадзора от 13.11.2020 №439, приложение 8 таблица 1, регламентирующий количество циклов наблюдений. Но чтобы сделать систему гибкой и учесть внешние факторы, я проанализировал статистические данные.

С помощью Python (библиотека pandas) я проанализировал массив метеонаблюдений за 65 лет, которые после фильтрации данных сократились до 45.

Статистические вычисления в Python

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

Частота событий по месяцам

Матрица геотехнических рисков

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

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

1. Не устанавливать реперы там, где в этом нет необходимости, реперы устанавливаются только в зонах повышенной опасности;
2. Повышать точность мониторинга в наиболее опасных зонах;
3.Минимизировать риски внезапных обрушений, которые могут стоить компании человеческих жизней и миллионных убытков.

На последнем графике видно, что операционные затраты на маркшейдерскую службу с учетом трат на мониторинг составляют всего 2,7% от годовых трат предприятия. Это ничтожная цена за безопасность работы всего карьера, для примера траты на ДТ составляют 32,5%.

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

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

От обучения к базе знаний, как промпт-инжиниринг помогает мне в работе с QGIS и Obsidian

2026-01-23T19:18:01.429Z

Мы привыкли использовать большие языковые модели как продвинутый поиск или калькулятор, но это всё равно что забивать микроскопом гвозди. Для меня как для инженера, время очень ценный ресурс. Поэтому я собрал систему, которая превращает LLM в полноценную "кафедру": здесь один агент учит меня сложным программам вроде QGIS, а второй сразу преобразует эти знания в структурированные заметки для Obsidian. В этой статье я поделюсь промптами и логикой системы, которая помогает мне учиться в разы быстрее.

Не так давно я начал экспериментировать с написанием различных промптов и анализировать как написание промпта влияет на итоговый результат. Сейчас в заметках у меня накопились десятки различных вариаций и я пришел к выводу, что самым полезными для меня являются промпты на менторов (агентов) со специализацией на различном ПО, например Civil3d, QGis и тд. В этой статье я напишу свой вариант использования LLM в связке с Obsidian Vault для обучения техническим программам и автоматизированному формированию заметок, что будет очень полезно в контексте организации обучения в связке с созданием большого количества заметок для личной базы знаний.

В статье будет приведено два промпта:

первый — это QGIS-ментор, который принимает задачу, разбивает ее на этапы, утверждает все с пользователем и проходя по каждому из этапов помогает пользователю разобраться в программе, при необходимости углубляясь в необходимые темы. На выходе получается структурированный отчет-инструкция по выполненной работе, который может редактироваться по запросу пользователя, урезаться или наоборот дополняться необходимыми деталями.
второй промпт — технический писатель получает отчет от QGIS-ментора (или любого другого агента-ментора), преобразует его под формат Obsidian Vault и выводит как блок кода markdown. Код вставляется в Obsidian и сразу формируется в красивую заметку.

Я нахожу это очень удобным и полезным по нескольким причинам:

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

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

Статья разбита на следующие части:

Промпт, ментор-QGis(агент)
Промпт, технический писатель(агент)
Интеграция агентов с Obsidian
Выводы
Полезные ссылки

Ниже приведена схема взаимодействия пользователя с агентом-ментором, агентом-техписателем и базой данных Obsidian

Система взаимодействия пользователя с агентами

1. Промпт, ментор-QGis

# РОЛЬ И ЦЕЛЬ (QGIS Mentor)

Ты - **профессиональный картограф и маркшейдер с многолетним опытом преподавания, эксперт в QGIS**.

Твоя задача - быть моим личным ментором, помогая решать задачи в программе QGIS и создавая для меня персональную базу знаний.

**Уровень пользователя:** Новичок (небольшой опыт, знает основы интерфейса).

**Версия QGIS:** По умолчанию 3.34 (пользователь может изменить командой `!version`).

---

## ПЕРЕД НАЧАЛОМ РАБОТЫ (Твоё первое сообщение)

В самом первом сообщении ты должен представиться и обязательно изложить следующие ограничения:

1. **Отсутствие «зрения»:** Напомни, что ты не видишь мой экран или файлы.

2. **БЕЗОПАСНОСТЬ ДАННЫХ:** Настоятельно порекомендуй мне **всегда сохранять проект** перед выполнением сложных операций.

---

## КЛЮЧЕВЫЕ ПРАВИЛА ВЗАИМОДЕЙСТВИЯ

1. **Анализ запроса:** Получив вопрос, проанализируй его. Если он неясен, запроси уточнения. Если некорректен или невыполним, тактично объясни почему и предложи альтернативу.

2. **Оценка сложности:** Перед началом кратко оцени сложность (например, «Это задача средней сложности, примерно 5 шагов»).

3. **Пошаговый план:** Предоставь краткий, пронумерованный план решения. Не начинай объяснение, пока я не напишу «продолжай».

4. **Итеративное объяснение:** Объясняй по **одному шагу за раз**. После каждого шага жди моего ответа.

5. **Проактивная проверка:** В конце каждого шага задавай вопрос: **«Этот шаг получился? Можем переходить к следующему?»**.

6. **Протокол "Я застрял":** Если я отвечаю, что у меня **не получается** или я чего-то не вижу, немедленно переходи в **режим отладки**. Задавай уточняющие вопросы, чтобы найти проблему (например, «Какое сообщение об ошибке вы видите?», «Какие пункты есть в вашем меню?»).

7. **Финальный отчёт и База Знаний:** Когда тема исчерпана, создай полный структурированный отчёт. **Присвой ему уникальный ID** (например, `[QGIS_LESSON_001]`) и в конце добавь ободряющую фразу.

## Финальный отчёт и База Знаний

По завершении задачи ты ОБЯЗАН предложить пользователю создать отчет.

- Сначала представь **черновик** отчета по структуре ниже.

- Если пользователь вводит команду `!refine` [ваш комментарий/запрос], ты должен обсудить детали, изменить глубину изложения или дополнить пункты.

- Как только отчет утвержден (или если пользователь сразу согласен), выдай финальную версию.

## Структура отчета (для Техписателя)

Внутри тегов используй строго следующий формат:

**ID:** `[CAD_TIP_XXX]` (присвой порядковый номер)

**Task:** [Краткое описание задачи]

**Solution:** [ Пошаговое описание решения]

**Best_Practice:** [Как это делать профессионально: стандарты, "легкость" и редактируемость модели]

**Meta:** #cad #mentor_report #workflow

---

## ПРАВИЛА ОТВЕТА И СТИЛЬ

1. **Стиль ментора:** Говори как опытный, терпеливый и поддерживающий преподаватель. Объясняй так, как будто сидишь рядом и смотришь на стандартный интерфейс QGIS.

2. **Точность формулировок:** Всегда используй точные названия инструментов, меню и панелей в обратных кавычках. Пример: «Откройте меню `` `Слой` `` -> `` `Добавить слой` ``».

3. **Система предупреждений:** Используй префиксы:

- **`[💡 СОВЕТ]`**: Для полезных рекомендаций.

- **`[⚠️ ВНИМАНИЕ!]`**: Для необратимых действий или шагов, где легко допустить ошибку.

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

---

## ПРИМЕР ПОЛНОГО ВЗАИМОДЕЙСТВИЯ (Few-Shot Example)

- **Пользователь:** Как мне просто поменять цвет полигона?

- **Ты:** Здравствуйте! Я ваш личный ментор по QGIS. [...] *(полное приветствие)*. Что касается вашего вопроса: это простая задача, займёт 2 шага. **План:** 1. Открыть свойства слоя. 2. Изменить цвет заливки. Если готовы, напишите «продолжай».

- **Пользователь:** продолжай

- **Ты:** **Шаг 1:** На панели `` `Слои` ``, кликните правой кнопкой мыши по вашему слою и выберите `` `Свойства...` ``. У вас получилось?

- **Пользователь:** не получается, у меня нет такого пункта

- **Ты:** Понял. Давайте разбираться. **(Режим отладки)**. Вы точно кликаете правой кнопкой мыши именно на названии слоя? Какие пункты вы видите в самом низу меню?

- **Пользователь:** ой, все, нашел, получилось.

- **Ты:** Отлично! Тогда **Шаг 2:** [...] *(объяснение шага 2)*.

- **Пользователь:** все готово, спасибо!

- **Ты:** Отлично! Рад был помочь. Вот конспект урока. **ID Урока: `[QGIS_LESSON_001]`** [...] *(полный отчет)*. Вы прекрасно справились! Так держать!

---

## УПРАВЛЯЮЩИЕ КОМАНДЫ

- **`!reset`**: Полностью сбрасывает наш диалог.

- **`!status`**: Напоминает, на каком шаге мы находимся, и предлагает следующий шаг.

- **`!help`**: Выводит справку по командам.

- **`!version [номер]`**: Устанавливает вашу версию QGIS (например, `!version 3.22`).

- **`!recall [ID]`**: Напоминает ключевые шаги из урока с указанным ID.

- **`!export_kb`**: Выводит всю вашу базу знаний (список ID и заголовков уроков) в формате Markdown.

- **`!explain [простой/подробный]`**: Устанавливает уровень детализации для следующих объяснений.

---

## НАЧАЛО РАБОТЫ

Подтверди, что ты понял свою роль и все инструкции. Затем выведи своё **первое сообщение** согласно разделу «ПЕРЕД НАЧАЛОМ РАБОТЫ» и будь готов принять мой первый вопрос.

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

Для примера приведу карту, которую я сделал с нуля, настроив все объекты и фактически создал свою подложку, используя методические требования, свои начальные знания QGis и этот промпт, потратив 1 вечер:

Оформленная карта

Укрупненный вид карты

После выполненной задачи и обсуждения, QGis-ментор выдает отчет:

Пример отчета ментора

С этим отчетом иду в другой чат или проект/бота, вставляю его и получаю размеченный документ для Obsidian Vault от универсального технического писателя (он универсальный, потому что принимает любые черновики и преобразует их в заготовки для красивых заметок Obsidian):

2. Промпт, технический писатель

# Role: Senior Technical Writer & Obsidian Architect

## Context

Ты - эксперт по управлению знаниями и технический писатель. Твоя задача: принимать отчеты от Агента-Ментора и преобразовывать их в профессиональные заметки для базы знаний (Obsidian Vault), ничего не сокращая и не меняя смысла.

## Task

Преврати входящий отчет в Markdown-файл, используя продвинутое форматирование Obsidian (YAML, Callouts, Wiki-links).

## Formatting Rules (Strict)

1. **YAML Frontmatter:** В начале заметки обязательно создай блок:

---

id: [ID из отчета]

task_type: cad_solution

created: {{date}}

tags: [все теги из Meta через запятую]

---

2. **Заголовок:** Используй `Task` как основной заголовок H1, но сформулируй его лаконично.

3. **Визуальная структура (Obsidian Callouts):**

- Блок **Solution** оформи в Callout-блок типа `[!success] Решение`.

- Блок **Best Practice** оформи в Callout-блок типа `[!abstract] Лучшие практики`. Используй списки для читаемости.

4. **ID и Навигация:**

- В самом верху текста (под H1) добавь: `> [!info] ID Заметки: #[[ID_из_отчета]]`.

## Input Example (от Ментора)

---REPORT_START---

**ID:** `[CAD_TIP_042]`

**Task:** Создание параметрической резьбы в FreeCAD через Spreadsheet.

**Solution:** Создать таблицу, задать параметры 'Pitch' и 'Diameter', связать их с эскизом профиля через выражения (Expression).

**Best_Practice:** Всегда использовать именованные ячейки. Избегать "Hardcoded" значений в эскизах для легкого ресайзинга.

**Meta:** #cad #mentor_report #workflow #freecad

---REPORT_END---

## Your Output (Example)

# Параметрическая резьба в FreeCAD через Spreadsheet

> [!info] ID Заметки: #[[CAD_TIP_042]]

### Описание

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

> [!success] Решение

> 1. Создать таблицу (Spreadsheet).

> 2. Задать параметры `Pitch` и `Diameter`.

> 3. Связать параметры с эскизом профиля через **Expressions**.

> [!abstract] Best Practices

> * **Именованные ячейки:** Всегда присваивайте имена ячейкам в таблице.

> * **Гибкость:** Избегайте "Hardcoded" (жестко вписанных) значений для обеспечения легкого ресайзинга модели.

#cad

#mentor_report

#workflow

#freecad

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

3. Интеграция с Obsidian

Заметка получена, теперь необходимо немного настроить Obsidian, скачав плагины для более удобного форматирования, я скачал следующие плагины:

Advanced tables. Плагин помогает сделать красивые и легко изменяемые таблицы;
Dataview. Мощный плагин для сортировки данных заметок, если заметок много, то можно будет фильтровать и анализировать данные в них;
Excalidraw. Плагин позволяет делать зарисовки в заметках, иногда удобнее сделать быструю зарисовку/качественный рисунок, чем описывать словами.

Очередность действий для установки плагинов на рисунке ниже:

Все, данные готовы, можно копировать md файл из промпта-техписателя, создать новую заметку командой Ctrl+N и вставить в нее файл, получится красивый документ с расставленными наверху данными и тегами, по которым можно выполнить поиск.

Внешний вид заметки

Можно настроить выноски заметок под себя, заменив соответствующие теги в промпте или в самом документе. За зеленое обрамление решения отвечает выноска [!success] после которой идет нужный заголовок, в данном случае «Решение». Полный список и функционал этих тегов есть на сайте обсидиана ссылка для ознакомления

Оформление выноски

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

Чтобы дать название ссылки на заметку, необходимо в квадратных скобках выбрать документ на которую идет ссылка и после ее названия поставить символ | и ввести необходимое название. В примере [[GGS|net]] отображается в тексте как net — ссылка на заметку GGS.

Выводы

В этой статье я рассмотрел как связать LLM с обучением в ПО и быстро создавать заметки в Obsidian vault. Мне это помогает быстрее и качественнее разбирать функционал современных программ и фиксировать результаты своего обучения в виде персональной базы знаний, к которой можно будет вернуться в случае решения похожей задачи через время и не тратить время на вспоминание алгоритмов решения. Приведенные выше промпты использовались в Gemini 3, Grok 3,4, Qwen 3.

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

Полезные ссылки:

Ссылка на обсидиан: https://obsidian.md/
Оформление карт: https://ggspb.org/normativnaya-baza/files/uslovnye-znaki-dlia-topograficheskikh-kart-masshtabov-25000-50000-100000.pdf
Выноски в обсидиане: https://help.obsidian.md/callouts
Промпт для оценки и улучшения промптов: https://teletype.in/@foxhunterx/S3z_5cw4n4D

#prompt_engineering

#qgis

#промптинжиниринг

#obsidian

#база_знаний

Excel-калькулятор углов: как преобразовать градусы, минуты и секунды в десятичные значения и обратно

2025-03-14T08:25:32.403Z

Хочу рассмотреть задачу перевода углов формата (d°m′s″) в десятичные значение и обратно из десятичного значения в формат градусы° минуты′ секунды″ и математические операции над ними при помощи microsoft excel.

Пусть имеется угол 46°12′44″, необходимо найти синус этого угла или сложить его с углом 23°34′23″. Можно разбить каждый из углов на 3 части - 3 ячейки и , преобразовать их к формату десятичных чисел, помня о том, что в 1 градусе 60 минут, а в 1 минуте 60 секунд. Можно воспользоваться онлайн калькулятором, а можно и написать калькулятор самому, распишу ниже несколько способов:

Простой способ

Рисунок 1 - исходные данные

Формула перевода будет простой и выглядеть следующим образом: =N9+O9/60+P9/3600 (46+12/60+44/3600) = 46,21(2)

Рисунок 2 - перевод угла с разбиением на ячейки

Но можно осуществить переход без разбиения значения угла на 3 ячейки, красиво, но чуть сложнее.

Интересный способ

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

=ЛЕВСИМВ(M9;НАЙТИ("°";M9)-1)+ПСТР(M9;НАЙТИ("°";M9)+1;НАЙТИ("'";M9)-НАЙТИ("°";M9)-1)/60+ПСТР(M9;НАЙТИ("'";M9)+1;НАЙТИ("""";M9)-НАЙТИ("'";M9)-1)/3600

Рисунок 3 - лаконичные преобразования

Рисунок 4 - разбиение формулы

Извлечение градусов

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

=ЛЕВСИМВ(M9;НАЙТИ("°";M9)-1)

ЛЕВСИМВ(текст; количество извлекаемых символов) - эта функция позволяет извлекать необходимое количество символов из строки, начиная слева. Как видно из примера, функция обрабатывает ячейку

Первый аргумент: это текст - ячейка М9;

Второй аргумент: это количество извлекаемых символов, для нахождения воспользовался функцией НАЙТИ("°";M9). Эта функция находит позицию искомого символа, после выполнения будет выведено число 3, так как знак градусов находится на третьей позиции, чтобы вывести только числовые значения нужно от 3 отнять 1 НАЙТИ("°";M9)-1), таким образом получим количество чисел до знака градусов. Если в ЛЕВСИМВ не добавлять второй аргумент - количество извлекаемых символов, то выведется первое слева значение ЛЕВСИМВ(M9) = 3. (рисунок 3)

Результат:

=ЛЕВСИМВ(M9;2)

Извлечение минут и перевод в градусы

ПСТР(M9;НАЙТИ("°";M9)+1;НАЙТИ("'";M9)-НАЙТИ("°";M9)-1)/60

Функция найти разобрана выше, из нового - это функция ПСТР. ПСТР возвращает заданное число знаков из строки, начиная с указанной позиции. ПСТР(текст; начальная позиция; количество возвращаемых символов).

Первый аргумент: М9 - это ячейка(текст) в которой ведется поиск;

Второй аргумент: начальная позиция - НАЙТИ("°";M9)+1, то есть начальной позицией является позиция после знака «°»,единица прибавляется, так как НАЙТИ("°";M9) = позиция знака градусов «°», а необходимо найти следующую за ней;

Третий аргумент: количество возвращаемых символов

НАЙТИ("'";M9)-НАЙТИ("°";M9)-1

Находим позицию знака минут «'» в примере это 6,

Находим позицию знака «°» это 3

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

Результат: 6-3-1 =2, два символа между «°» и «'» будут найдены и использованы в расчетах.

Финальный результат работы формулы:

ПСТР(M9;4;2) выведется 2 символа ячейки M9, начиная с четвертого, далее поделится на 60, так как в 1 градусе 60 минут.

Извлечение секунд и перевод в градусы

ПСТР(M9;НАЙТИ("'";M9)+1;НАЙТИ("""";M9)-НАЙТИ("'";M9)-1)/3600

Формула аналогична формуле для извлечения минут.

Первый аргумент: М9 - это ячейка(текст) в которой ведется поиск;

Второй аргумент: начальная позиция - НАЙТИ("'";M9)+1, то есть начальной позицией является позиция после знака «'»,единица прибавляется, так как НАЙТИ("'";M9) = позиция знака секунд «'», а необходимо найти следующую за ней;

Третий аргумент: количество возвращаемых символов

НАЙТИ("""";M9)-НАЙТИ("'";M9)-1

Находим позицию знака секунд «"» в примере это 9. ВАЖНО! В Excel кавычки (") используются для обозначения текстовых строк. Если нужно указать сам символ кавычки внутри строки, то он должен быть "экранирован" — удвоен.

Находим позицию знака «'» это 6

Результат: 9-6-1 =2, два символа между «'» и «"» будут найдены и использованы в расчетах.

Финальный результат работы формулы:

ПСТР(M9;6;2) выведется 2 символа ячейки M9, начиная с шестого, далее поделится на 3600, так как в 1 градусе 60 минут, а в 1 минуте 60 секунд.

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

Рисунок 5 - преобразованные значения и вычисления с ними

Обратные преобразования из десятичного значения угла в формат (г°м′с″)

Для этого потребуется одна функция, из которой будет составлена формула, это функция - ЦЕЛОЕ, готовая формула:

Рисунок 6 - обратные преобразования

=ЦЕЛОЕ(M14)&"°"&ЦЕЛОЕ((M14-ЦЕЛОЕ(M14))*60)&"'"&((((M14-ЦЕЛОЕ(M14))*60)-ЦЕЛОЕ((M14-ЦЕЛОЕ(M14))*60))*60)&""""

=ЦЕЛОЕ(угол) & "°" & ЦЕЛОЕ((угол - ЦЕЛОЕ(угол)) * 60) & "'" & ОКРУГЛ(((угол - ЦЕЛОЕ(угол)) * 60 - ЦЕЛОЕ((угол - ЦЕЛОЕ(угол)) * 60)) * 60; 2) & """"

Формула облегченная для восприятия

ЦЕЛОЕ Округляет число до ближайшего меньшего целого.

Первая часть формулы: ЦЕЛОЕ(M14)&"°"& выделяет целое число градусов из десятичного обозначения угла, &"°"& прибавляет к числу знак градусов и следующее число. ЦЕЛОЕ(30,3125)&"°"& = 30° ;

Вторая часть формулы: ЦЕЛОЕ((M14-ЦЕЛОЕ(M14))*60)&"'"& из значения ячейки M14 вычитаю целое значение этой ячейки, разность умножаю на 60, чтобы найти количество минут, далее выделяю целое количество минут = ЦЕЛОЕ((30,3125 - 30) * 60)&"'"& = ЦЕЛОЕ(0,3125*60)&"'"& = 18' ;

Третья часть формулы: ((((M14-ЦЕЛОЕ(M14))*60)-ЦЕЛОЕ((M14-ЦЕЛОЕ(M14))*60))*60)&"""" получается матрешка - целое в целом в целом ;) Из значения ячейки M14 вычитаю целое значение этой ячейки (нахожу значение градусов после запятой), разность умножаю на 60(получаю значение уже в минутах), потом проделываю эту операцию еще раз (Из значения ячейки M14 вычитаю целое значение этой ячейки, разность умножаю на 60) нахожу целое количество минут, далее разность минуты- минус целые минуты = дробное значение секунд, умножаю дробное значение секунд на 60 и получаю целое значение: ((((30,3125 - 30)) * 60) - ЦЕЛОЕ((30,3125-30)*60))*60)&"""" = 45".

В статье я использовал обычные кавычки как обозначение минут ' секунд " , но обычно используются спецсимволы минуты ′ секунды ″.

В формуле =ЛЕВСИМВ(M9;НАЙТИ("°";M9)-1)+ПСТР(M9;НАЙТИ("°";M9)+1;НАЙТИ("'";M9)-НАЙТИ("°";M9)-1)/60+ПСТР(M9;НАЙТИ("'";M9)+1;НАЙТИ("""";M9)-НАЙТИ("'";M9)-1)/3600 при использовании спецсимвола секунд ″ не нужно его удваивать:

=ЛЕВСИМВ(M9;НАЙТИ("°";M9)-1)+ПСТР(M9;НАЙТИ("°";M9)+1;НАЙТИ("'";M9)-НАЙТИ("°";M9)-1)/60+ПСТР(M9;НАЙТИ("'";M9)+1;НАЙТИ("″";M9)-НАЙТИ("'";M9)-1)/3600

НАЙТИ("″";M9)-НАЙТИ("'";M9)-1)/3600

И если появляется ошибка #ЗНАЧ! необходимо проверить совпадение спецсимволов в угле и формуле преобразования, приведение их к одному формату исправляет ошибку и делает вас счастливее!

(' " - кавычки )

(′ ″ - спецсимволы)

Резюмирую: в данной статье я рассмотрел способы преобразования углов различными способами, показал как комбинируя функции ms excel можно получить удобный и лаконичный калькулятор пересчета углов, надеюсь было полезно!

Для вышеприведенных преобразований необходимы следующие функции (ЛЕВСИМВ, НАЙТИ, ПСТР, ЦЕЛОЕ)
Для перевода углов формата (d°m′s″) в десятичные значение используется формула: =ЛЕВСИМВ(M9;НАЙТИ("°";M9)-1)+ПСТР(M9;НАЙТИ("°";M9)+1;НАЙТИ("'";M9)-НАЙТИ("°";M9)-1)/60+ПСТР(M9;НАЙТИ("'";M9)+1;НАЙТИ("""";M9)-НАЙТИ("'";M9)-1)/3600
Для обратного преобразования из десятичного значения угла в формат (г°м′с″) используется формула: =ЦЕЛОЕ(M14)&"°"&ЦЕЛОЕ((M14-ЦЕЛОЕ(M14))*60)&"'"&((((M14-ЦЕЛОЕ(M14))*60)-ЦЕЛОЕ((M14-ЦЕЛОЕ(M14))*60))*60)&""""

Источники:

ЛЕВСИМВ, ЛЕВБ (функции ЛЕВСИМВ, ЛЕВБ) - Служба поддержки Майкрософт

Функция НАЙТИ - Служба поддержки Майкрософт

Функция ПСТР - Служба поддержки Майкрософт

Функция ЦЕЛОЕ - Служба поддержки Майкрософт

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

2025-01-13T08:34:14.952Z

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

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

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

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

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

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

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

Рисунок 1 - отображение штилевых дней

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

Рисунок 2 - таблица со штилевыми данными

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

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

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

Рисунок 3 - длины откладываемых отрезков на розе ветров

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

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

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

Рисунок 4 - схема получения розы ветров

Рисунок 5 - общий вид таблицы

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

Для начала предлагаю заполнить ячейки по основным направлениям ветра, так как для их заполнения нужно меньше всего вычислений. Приведу пример на основе северного направления. Точка 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)

Рисунок 6 - луч северного направления ветра

Рисунок 7 - вычисления

Рис. 8 - изменение соотношения длина/ширина

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

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

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

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

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

Рис. 9 - искомые точки

Рис. 10 - вычисления

Для того чтобы найти точку 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))

Рис. 11 - треугольник ABC

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

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

Рис. 12 - вычисления

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

Рис. 13 - уровни иерархии

Рис. 14 - финальный вид таблицы по направлениям ветра и годовой розы ветров

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

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

2024-08-20T12:00:28.076Z

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

Что такое роза ветров и как ее простроить? По каким исходным данным работать?

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

Определить количество ветреных дней по каждому из направлений (С, СВ, В и тд.) за месяц и за год, вычислить среднюю скорость для каждого из направлений.
Розы ветров для каждого месяца (рис.1).
Годовую розу ветров (рис.2).

Рисунок 1 — Роза ветров за март 2023 года

Рисунок 2 - Годовая роза ветров

С основной задачей определились, но что делать теперь, с чего начать? Для облегчения поиска решений я разбил основную задачу на несколько подзадач:

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

1. Парсинг данных в эксель

Для загрузки данных с сайта в эксель необходимо воспользоваться командой «получить данные из интернета». Вкладка «Данные» -> «получить данные из интернета»

Далее появится окно ввода URL-адреса, в которое необходимо вбить адрес сайта с которого необходимо получить выгрузку данных

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

Теперь разделяем столбец с направлением ветра на само направление и скорость ветра, для дальнейшего анализа. Используем команду «Текст по столбцам». Вкладка «Данные» -> «текст по столбцам». В первом шаге указываем пробел как разделитель, вторым шагом разделяем столбец со скоростями ветра, используем символ-разделитель другой: м

Конечныы результатом разделения является 3 столбец

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

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)

Рис.3.1

Рис.3.2

Рис.3.3

Диаграмма заполнена и визуализирует все данные осталось только настроить внешний вид на свой вкус и повторить вышеприведенные операции для всех оставшихся месяцев