Как нормировать данные в excel

4

Формула нормализации — Калькулятор (Примеры с шаблоном Excel)

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

Формула для нормализации

• X: это набор наблюдаемых значений, присутствующих в X.
• X_min : это минимальные значения в X
• X _max : это максимальные значения в X

Примеры формул нормализации (с шаблоном Excel)

Давайте рассмотрим пример, чтобы лучше понять расчет нормализации.
Вы можете скачать этот шаблон формулы нормализации Excel здесь — Шаблон формулы нормализации Excel

Формула нормализации — пример № 1

Рассчитать нормализацию для следующего набора данных.

Максимальное значение в наборе данных рассчитывается как

Таким образом, 75 является максимальным значением в данном наборе данных.

Минимальное значение в наборе данных рассчитывается как

20 — минимальное значение в данном наборе данных.

Нормализация рассчитывается по формуле, приведенной ниже

Точно так же мы рассчитали нормализацию для всех значений данных.

Формула нормализации — пример № 2

Рассчитать нормализацию для следующего набора данных.

Максимальное значение в наборе данных рассчитывается как

Таким образом, 164 является максимальным значением в данном наборе данных.

Минимальное значение в наборе данных рассчитывается как

101 — минимальное значение в данном наборе данных.

Нормализация рассчитывается по формуле, приведенной ниже

Точно так же мы рассчитали нормализацию для всех значений данных.

Формула нормализации — пример № 3

Рассчитать нормализацию для следующего набора данных.

Максимальное значение в наборе данных рассчитывается как

Таким образом, 197 является максимальным значением в данном наборе данных.

Минимальное значение в наборе данных рассчитывается как

121 — минимальное значение в данном наборе данных.

Нормализация рассчитывается по формуле, приведенной ниже

Точно так же мы рассчитали нормализацию для всех значений данных.

объяснение

Формула нормализации может быть объяснена в следующих шагах:

Шаг 1 : Из данных пользователю необходимо найти максимальное и минимальное значение, чтобы определить контуры набора данных.

Шаг 2: Затем пользователь должен найти разницу между максимальным и минимальным значением в наборе данных.

Шаг 3: Значение — Мин необходимо определить по каждой точке данных в наборе.

Шаг 4 : После определения всех значений в наборе данных значение необходимо поместить в формулу, т. Е. X _new = (X — X _min) / (X _max — X _min)

Актуальность и использование формулы нормализации

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

Калькулятор формулы нормализации

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

Рекомендуемые статьи

Это было руководство к формуле нормализации. Здесь мы обсудим, как рассчитать нормализацию вместе с практическими примерами. Мы также предоставляем калькулятор нормализации с загружаемым шаблоном Excel. Вы также можете посмотреть следующие статьи, чтобы узнать больше —

Нормализация значений в ячейках Excel ⁠ ⁠

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

Итак, проблема.

Бывает такое странное, что в ячейке стоит значение, а она не форматируется, как тебе надо. Всегда остается неизменной. Даже, какие-то вычисления с ней можно произвести, а формат не меняется, пока в ячейку не зайдешь и не нажмешь ввод. Такое случается при выгрузке в формате эксель из какой-нибудь другой программы. 1С, например, будь он проклят. Или, что будет описано ниже, из MS Project. Далее описание примера с картинками.

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

Вот так это работает. Я внес в ячейку G12 просто 1, ячейка O12 сигнализирует, что она отличается.

Теперь по формату. Вот я пытаюсь применить к диапазону дат в столбцах I:J другой формат даты, не с 4х-значным, а двухзначным годом.

Формат применен, однако в ячейке по прежнему год 2020

Но, если дважды кликнуть по ячейке, а затем нажать Enter (как бы ввести значение в ячейку), то формат становится тем, какой применен ранее. Вот тут заливка желтым сигнализирует, что ячейки B3 и J3 не равны.

Теперь по формулам в таблицах. Они идентичны.

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

Синим выделил расчет в начальном варианте. 23.09.20 считается от окончания, хотя оно раньше 12.10.20 в предыдущей строке. А должно быть наоборот, как в таблице справа.

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

Как водится, в экселе, таких строк обычно тысячи и каждую ячейку вручную не исправишь.

К сожалению, никаких средств в самом экселе для решения такой проблемы нет.

Для себя же, методом тыка, нашел команду в VBA и вставил простенький макрос:

!Эта команда работает только для ячеек, выделенных вертикально. При выборе диапазона или всего столбца, если где-то в нем попадутся объединенные ячейки, VBA выдаст ошибку.

!Еще бывает, если выделять очень длинный диапазон, то случалось вместо 12.05.2021 получить 12.05.01. Почему и как оно так вышло, не понял.

На самом деле, метод Parse предназначен не для этого, конечно.

Но, оно работает, а большего мне и не надо.

Как вставить себе в эксель макрос и повесить его на кнопку, много где описано, поэтому не стану.

635 постов 14.5K подписчиков

Правила сообщества

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

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

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

Важно — сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.

Утверждения вроде «пост — отстой», это оскорбление автора и будет наказываться баном.

Помощь с макросом⁠ ⁠

Дамы и господа, добрый день.

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

Весёлые маркеры графиков⁠ ⁠

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

Установка рисунков в качестве маркеров позволяет разнообразить внешний вид документации, сделав её нагляднее. Установка смайлов (© http://www.kolobok.us/ ) сделана в качестве примера (помните про Aiwan то? Или забыли. ).

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

Заменить маркеры на рисунки, в данном случае они представлены смайлами, можно при помощи не сложного макроса

Sub Markers_Smiles()

ActiveSheet.ChartObjects(«Диаграмма 1»).Activate

For Each icell In [C2:C102]

ActiveChart.FullSeriesCollection(1).Points(icell.Row — 1).Select

‘ Убираю рамки вокруг маркеров

Selection.MarkerForegroundColorIndex = xlNone

‘ Установка типа маркера «Рисунок»

Selection.MarkerStyle = -4147

Selection.Format.Fill.UserPicture «D:\4.gif»

If icell.Value = 0 Then Selection.Format.Fill.UserPicture «D:\1.gif»

If icell.Value = 1 Then Selection.Format.Fill.UserPicture «D:\2.gif»

If icell.Value = 2 Then Selection.Format.Fill.UserPicture «D:\3.gif»

[C2:C102] — столбец с признаками маркера. Число элементов равно числу данных (Х или Y). Может как заполняться вручную, так и быть расчётным (см.рисунок ниже).

D:\1.gif . D:\4.gif — пути к рисункам.

Аналогично производится заполнение рисунками нескольких графиков на диаграмме

Sub Прореживание_маркеров()

‘ Активируем диаграмму

ActiveSheet.ChartObjects(«Диаграмма 1»).Activate

‘ Перебор по всем графикам диаграммы

For k = 1 To ActiveChart.FullSeriesCollection.Count

‘ Удаляем все маркеры на линии

For i = 1 To ActiveChart.SeriesCollection(k).Points.Count

ActiveChart.FullSeriesCollection(k).Points(i).Select

Selection.MarkerStyle = -4142

‘ Выставляем маркеры с требуемым шагом.

For i = 1 To ActiveChart.SeriesCollection(k).Points.Count Step 4

ActiveChart.FullSeriesCollection(k).Points(i).Select

With Selection

.MarkerStyle = 8

.MarkerSize = 15

Public Sub color_graph()

ActiveSheet.ChartObjects(«Диаграмма 1»).Activate

For k = 1 To ActiveChart.FullSeriesCollection.Count ‘ Перебор по всем графикам

For Each icell In [C2:C102]

ActiveChart.FullSeriesCollection(k).Points(icell.Row — 1).Select

Selection.MarkerStyle = -4147

Selection.Format.Fill.UserPicture «D:\4.gif»

If icell.Value = 0 Then Selection.Format.Fill.UserPicture «D:\1.gif»

If icell.Value = 1 Then Selection.Format.Fill.UserPicture «D:\2.gif»

If icell.Value = 2 Then Selection.Format.Fill.UserPicture «D:\3.gif»

Аналогично разным графикам одной диаграммы можно присвоить уникальные маркеры

Sub Markers()

ActiveSheet.ChartObjects(«Диаграмма 1»).Activate

For i = 1 To ActiveChart.FullSeriesCollection.Count ‘ Перебор по всем графикам

ActiveChart.FullSeriesCollection(i).Select

Selection.MarkerForegroundColorIndex = xlNone

Selection.MarkerStyle = -4147

If i = 1 Then Selection.Format.Fill.UserPicture «D:\1.gif»

If i = 2 Then Selection.Format.Fill.UserPicture «D:\2.gif»

If i = 3 Then Selection.Format.Fill.UserPicture «D:\3.gif»

If i = 4 Then Selection.Format.Fill.UserPicture «D:\4.gif»

If i = 5 Then Selection.Format.Fill.UserPicture «D:\5.gif»

Ну или просто разными штатными маркерами разные графики. Но в автоматическом режиме — очень сокращает время подготовки документации. Полезно при подготовке к печати в чёрно-белом варианте.

Sub Установка_разных_маркеров()

ActiveSheet.ChartObjects(«Диаграмма 1»).Activate

For i = 1 To ActiveChart.FullSeriesCollection.Count ‘ Перебор по всем графикам

ActiveChart.FullSeriesCollection(i).Select

Selection.Format.Line.ForeColor.RGB = RGB(0, 0, 0) ‘ Цвета линий и маркера

Selection.Format.Line.Weight = 0.75 ‘ Установка толщины линии

Selection.MarkerStyle = i ‘ Установка типа маркера

Selection.MarkerSize = 4 ‘ Установка размера маркера

Selection.Format.Fill.ForeColor.RGB = RGB(255, 255, 255) ‘ Установка заливки маркера

Можно ли это сделать без макросов? Несомненно. Долго и нудно кликать кнопочки.

Но как по мне — проще скопировать и немного поправить код простого макроса. А в остальном — ваш выбор.

Excel триальный⁠ ⁠

Немного отвлечённый пост о защите своей работы.

Итак, общеизвестны способы закрытия информации в Excel, а именно:

1. Защита листа/книги

Выбираем разрешения/допуски, вводим пароль, сохраняем файл.

Дополнительно для каждой ячейки можно указать защищается ли она или нет. По умолчанию — защищается.

2. Защита кода

Точно так же вводим пароль (с повторением), ок, сохраняем.

Но все эти способы не более чем игрушки, и вскрываются совершенно не сложно при наличии некоторых минимальных навыков и особенно при сохранении в *.xlsm (файл с поддержкой макросов). Сохраняйте в *.xlsb (двоичный код), если хотите хоть немного защитить свою работу.

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

Каким же образом можно ещё затруднить использование вашей работы, кроме как не давать её?

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

Этим можно воспользоваться выполнив передачу результатов расчёта в виде статических таблиц. Да, можно распечатать/сохранить в pdf, или банальным Ctrl+А / Ctrl+C / Ctrl+V /только значения/. А можно просто воспользоваться простым макросом:

‘ Замена всех формул на листе в значения

Sub Form_2_Dan()

Dim a As Integer

‘ Запрашиваем подтверждение

a = MsgBox(«Внимание!» & _

Chr(10) & «Вы точно хотите заменить все формулы на листе на значения?» & _

Chr(10) & «Это необратимо!», _

52, «Замена формул на значения.»)

‘ Если OK, то замену производим

If a = 6 Then

ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value

Расположение макроса — модуль.

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

Внимание! Действие макроса необратимо!

Можно сделать «триальным» расчёт разместив в модулях листов вот такого вида макрос.

Private Sub Worksheet_Activate()

Application.ScreenUpdating = False

If Date >= #10/6/2022# Then ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value

Application.ScreenUpdating = True

Т.е. после 10.06.2022 все расчёты с листа исчезнут. А цифры останутся.

Можно заменить проверку на заполнение ячейки, например проверить что в определённой ячейке записан автор труда «Вася Пупкин». 🙂 При смене которого всё превратится в набор цифр..

Естественно доступ к макросам должен быть закрыт/запаролен.

Ещё вариант — ввод пароля на саму книгу:

Private Sub Workbook_Open()

Dim i&, n&, P As Variant

Application.ScreenUpdating = False

If Date >= #1/2/2022# Then

For i = 1 To Sheets.Count

Sheets(i).Activate

Sheets(i).Protect «1234»

P = InputBox(«Время использования книги истекло, для продолжения введите пароль», «ВВОД ПАРОЛЯ»)

If P = «°0176» Then

For i = 1 To Sheets.Count

Sheets(i).Activate

Sheets(i).Unprotect «1234»

If n = 0 Then

Application.DisplayAlerts = False

ThisWorkbook.Close

Application.DisplayAlerts = True

MsgBox «Пароль не верный, у вас еще » & n & » попытки»

Application.ScreenUpdating = True

Расположение макроса — «Эта книга».

#1/2/2022# — дата с которой будет запрашиваться пароль

«°0176» – правильный пароль

И при открытии файл будет встречать весёлым окошком:

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

И да, это всё игрушки — серьёзные дяденьки с тётеньками при необходимости поломают сие поделия, и узнают как вы определяли дискриминант. (0_о). Даже если Вы применили обфускацию кода или перенос кода в dll.

Построение графиков⁠ ⁠

Сегодня разберём задачку, которая вставала перед каждым пользователем Excel — необходимость построить график функции.

Любой, кто решал эту задачку — действовал следующим способом:

1. Создаётся столбец Х;

2. Создаётся столбец Y, котором происходит расчёт согласно заданной функции;

3. Выделяются два созданных столбца и вставляется график.

Но это просто и скучно. Есть другой способ. Построить график непосредственно из макроса.

Начнём с простого — у нас есть набор точек соответствия X и Y.

Sub Построй_график_по_точкам()

Dim MyChart As Chart

Set MyChart = ActiveSheet.Shapes.AddChart2.Chart

With MyChart

.SeriesCollection.NewSeries

.SeriesCollection(1).Name = «xlXYScatterSmoothNoMarkers»

.SeriesCollection(1).XValues = Array(0#, 0.5, 1#, 1.5, 2#, 2.5, 3#, 3.5, 4#, 4.5, 5#)

.SeriesCollection(1).Values = Array(0#, 0.4794, 0.8415, 0.9975, 0.9093, 0.5985, 0.1411, -0.3508, -0.7568, -0.9775, -0.9589)

.ChartType = xlXYScatterLines ‘ Соединение точек прямыми

.SetElement msoElementLegendNone

Другие варианты отображения линии графика:

.ChartType = xlXYScatterLinesNoMarkers ‘ Соединение точек прямыми без маркеров

.ChartType = xlXYScatterSmoothNoMarkers ‘ Сглаженная линия

Более подробно о типах — тут

Сборка Array(. ) может быть выполнена с использованием программы, которую я выкладывал в 7-й части темы про оцифровку, ну или заполнить руками.

Как не трудно догадаться — вовсе не обязательно иметь готовый набор данных.

Рассмотрим ситуацию, когда требуется построить два графика на одной диаграмме.

Для упрощения восприятия использую две простые функции линий y1 = x — 20, y2 = x + 20.

Sub Создать_диаграмму()

Dim MyChart As Chart

Dim i As Integer, Xmin As Single, dX As Single, Xmax As Single, _

Ymin As Single, Ymax As Single, dY As Single

Dim X() As Single

Dim Y() As Single

Dim Yp() As Single

Xmin = 0: Xmax = 300: dX = 20 ‘ Сие больше нужно для осей и оформления

Ymin = 0: Ymax = 160: dY = 20

ReDim X(0 To Xmax — Xmin): ReDim Y(0 To Xmax — Xmin, 1 To 2)

ReDim Yp(0 To Xmax — Xmin)

For i = 0 To Xmax — Xmin Step 1

X(i) = Xmin + i

‘ Заполнение данных первого графика

Y(i, 1) = X(i) — 20

‘ Заполнение данных второго графика

Y(i, 2) = X(i) + 20

‘ создадим новую диаграмму и зададим ей габаириты

Set MyChart = ActiveSheet.Shapes.AddChart2(, , , , 300, 200).Chart

For i = 1 To 2

For j = 0 To Xmax — Xmin Step 1

With MyChart

.SeriesCollection.NewSeries

.SeriesCollection(i).XValues = X

.SeriesCollection(i).Values = Yp

.ChartType = xlXYScatterSmoothNoMarkers

При задании новой диаграммы можно задать в том числе и положение диаграммы на листе

AddChart2(Стиль,XlChartType,слева,сверху,ширина,высота,NewLayout)

В итоге получим вот такую диаграмму:

В дальнейшем можно обработать её как обычную — задать цвета, толщины и т.д. Но можно это сразу поручить нашему макросу:

Sub Создать_диаграмму()

Dim MyChart As Chart

Dim i As Integer, Xmin As Single, dX As Single, Xmax As Single, _

Ymin As Single, Ymax As Single, dY As Single

Dim X() As Single

Dim Y() As Single

Dim Yp() As Single

Xmin = 0: Xmax = 300: dX = 20 ‘ Сие больше нужно для осей и оформления

Ymin = 0: Ymax = 340: dY = 20

ReDim X(0 To Xmax — Xmin): ReDim Y(0 To Xmax — Xmin, 1 To 2)

ReDim Yp(0 To Xmax — Xmin)

For i = 0 To Xmax — Xmin Step 1

X(i) = Xmin + i

Y(i, 1) = X(i) — 20

Y(i, 2) = X(i) + 20

Set MyChart = ActiveSheet.Shapes.AddChart2(, , 0, 0, 400, 230).Chart

For i = 1 To 2

For j = 0 To Xmax — Xmin Step 1

With MyChart

.SeriesCollection.NewSeries

.SeriesCollection(i).XValues = X

.SeriesCollection(i).Values = Yp

.ChartType = xlXYScatterSmoothNoMarkers

With MyChart

.SetElement (msoElementPrimaryCategoryGridLinesMajor)

‘ Включаю отображение названия осей

.Axes(xlCategory, xlPrimary).HasTitle = True

.Axes(xlValue, xlPrimary).HasTitle = True

.Axes(xlCategory, xlPrimary).AxisTitle.Text = «Расход Go т/ч»

.Axes(xlValue, xlPrimary).AxisTitle.Text = «Давление кгс/кв.см.»

‘ Выключаю отображение легенды

.SetElement (msoElementLegendNone)

‘ Выключаю отображения заголовка диаграммы

.SetElement (msoElementChartTitleNone)

‘ Выставляем параметры осей

.Axes(xlCategory).MinimumScale = Xmin

.Axes(xlCategory).MaximumScale = Xmax

.Axes(xlCategory).MajorUnit = dX

.Axes(xlValue).MinimumScale = Ymin

.Axes(xlValue).MaximumScale = Ymax

.Axes(xlValue).MajorUnit = dY

‘ Оформление гризонтальной оси

MyChart.Axes(xlCategory).Select

With Selection.Format.Line

.Visible = msoTrue

.ForeColor.RGB = RGB(0, 0, 0)

.ForeColor.TintAndShade = 0

.ForeColor.Brightness = 0

.Transparency = 0

.Visible = msoTrue

.Weight = 1.25

‘ Оформление вертикальной оси

MyChart.Axes(xlValue).Select

With Selection.Format.Line

.Visible = msoTrue

.ForeColor.RGB = RGB(0, 0, 0)

.ForeColor.TintAndShade = 0

.ForeColor.Brightness = 0

.Transparency = 0

.Visible = msoTrue

.Weight = 1.25

‘ Оформление горизонтальной сетки

MyChart.Axes(xlValue).MajorGridlines.Select

With Selection.Format.Line

.Visible = msoTrue

.DashStyle = msoLineDash

.Visible = msoTrue

.ForeColor.RGB = RGB(0, 176, 240)

.Transparency = 0

‘ Оформление вертикальной сетки

MyChart.Axes(xlCategory).MajorGridlines.Select

With Selection.Format.Line

.Visible = msoTrue

.DashStyle = msoLineDash

.Visible = msoTrue

.ForeColor.RGB = RGB(0, 176, 240)

.Transparency = 0

По итогу диаграмма будет выглядеть так:

Как не трудно понять, данных, по которым построена диаграмма, на листе нет. И после удаления макроса останется только итоговый результат.

Кому то это покажется слишком сложным, однако открою маленький секрет — очень редкие люди пишут макрос с нуля. В 90% достаточно иметь готовый макрос (см листинг выше), заменить в нём пару строк (сменить функции, изменить диапазоны. ) и всё. По итогу построение занимает меньше времени чем построение классическим способом.

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

Ну и всегда есть вариант удивить преподавателя (0_о).

Excel. Долгая дорога оцифровки. Часть 9. Оформление графиков, или отображение поиска решения⁠ ⁠

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

Итак, по сути потребуется решить два вопроса:

1. Построить ход поиска с помощью стрелки/стрелок.

2. Совместить построенный график с изначальным рисунком.

Т.е. получить что то похожее на вот это:

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

Часть 1. Построение поиска решения.

Итак, у нас есть заданные аргументы (G2, t1в) и результат расчёта Р2. На графике сие будет выглядеть как одна точка с координатами X = G2 = 200 (в нашем примере) и Y = Р2 = 0,065

Существуют минимум три метода построения стрелки поиска:

Вариант 1. Для вертикальной и горизонтальной части строим независимые линии.

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

Для вертикальной линии второй точкой указывается точка с равным значением по Х и минимумом по бумажному графику Y.

Для горизонтальной линии второй точкой указывается точка с равным значением по Y и минимумом по бумажному графику X.

Минимумы и максимумы диаграммы выставляются равными минимумам и максимумам бумажного рисунка.

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

Вариант 2. Единая линия поиска.

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

Вариант 3. Использование погрешностей для указания поиска решения.

Если точка одна, то для отображения линий погрешности необходимо перейти в настройки предела погрешности по Х и по Y поочерёдно и.

— величина погрешности «пользовательская».

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

Если есть желание получить стрелку направленную к оси Y, а ось Х начинается не с 0 (в нашем случае с 2-ти), то потребуется сделать ячейку рассчитывающую смещение относительно 0.

В нашем примере сделаем такое и для X и Y:

ось Х сдвинута на 20. Соответственно имеем ячейку Хзаданное — Хсмещения = 200 — 20

ось Y сдвинута на 0,02 Соответственно имеем ячейку Yзаданное — Yсмещения

Это значения не статичны, т.е. они пересчитаются при изменении исходных данных.

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

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

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

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

Однако можно сделать выноску для той самой, единственной точки.

Результаты всех трёх способов не сильно отличаются:

Часть 2. Совмещение построенного графика с изначальным рисунком.

И опять есть минимум три варианта.

Вариант 1. Использование рисунка в качестве подложки под областью построения (то, что расположено внутри границ осей). Для этого рисунок сначала подготавливается (обрезается по размерам построения, при этом подписи осей оказываются обрезанными), а затем вставляется по пути: Формат области построения – Заливка – Рисунки и текстура – Файл / из буфера обмена;

Вариант 2. Использование рисунка в качестве подложки области диаграммы (вкладка Формат области диаграммы – Заливка – Рисунки и текстура — Файл) вставляется рисунок графика (предварительно подготовленный и очищенный. Необходимо также учитывать, что потребуется некоторая ширина полей для выставления подписей). Совмещаются границы графика Excel с границами графика рисунка перетягиванием за маркеры границы графика (перемещение указал стрелками).

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

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

при необходимости можно построить дополнительную линию. В качестве примера построена дополнительная кривая при 40°С при помощи созданной пользовательской функции при заданной температуре 40°С и переменной влажности. Аналогично построена дополнительная линия на первом рисунке

Вариант 3. При третьем варианте рисунок вставляется на лист Excel, построенный график/ подготовленная диаграмма размещается над рисунком, при этом заливка поля построения и самой диаграммы «отсутствует» или «прозрачная». После совмещения изображение и диаграмма фиксируются между собой как это было указано в посте «Нестандартные заголовки диаграмм».

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

Отдельно стоят диаграммы состоящие из расположенных рядом двух и более диаграмм.

Их оформление, опять же, может быть реализовано тремя способами.

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

Способ 2 — применение первого варианта, наложение графика на область построения (описано выше). Т.е. строим два независимых графика для левой и правой части, накладываем области построения и размещаем взаимно друг другу до совпадения минимума и максимума.

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

Рассмотрим один из вариантов построения стрелки на диаграмме, состоящей из двух диаграмм, при этом ширина клеток и величина шага для правого и левого графика разная.

Для наглядности оси были ярко выражены и отодвинуты относительно области построения, а графики разнесены по цветам.

Шаг 1. Построение левого графика (синий график, синяя ось, синие данные).

1. Построить точечный график по исходным данным, причём заложить небольшой перехлёст по Х (установлено 80 вместо 70-ти по рисунку);

2. Сделать подложку под диаграмму (используется весь рисунок, без обрезок или разделения на две части);

3. Растянуть область построения на рисунок;

4. Задать значения оси (диапазон) Y в соответствии с оцифровкой;

5. Задать значения оси (диапазон) Х таким образом, чтобы Хмин было равно минимальному значению на рисунке (30), а Хмакс подобрать таким образом, чтобы совпали значения рисок (40=40, 50=50, 60=60, 70=70).

Шаг 2. Построение правого графика (красный график, красный ось, красный данные).

1. Построить точечный график по исходным данным, причём минимум Х заложить равным минимуму по второй оси (0);

2. Указать построение по вспомогательным осям;

3. Задать значения вспомогательной оси (диапазон) Y в соответствии с оцифровкой;

4. Задать значения оси (диапазон) Х таким образом, чтобы Хмакс было равно максимальному значению по второй оси рисунка, а Хмин подбрать таким образом, чтобы начало второго графика легло на минимум второй оси Х рисунка.

Шаг 3. Убрать отображение подписей осей, сетки и т.д. Настроить цвета линий.

Для кого то это покажется элементарным, но я на своей практике не один раз ломал голову как выполнить графическое оформление поиска решения. Базовыми знаниями поделился. Всё дальнейшее зависит от вас. Будут вопросы — помогу по мере сил.

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

9. Отображение поиска решения (данный пост).

Excel. Долгая дорога оцифровки. Часть 7. Автоматическое создание макроса функции с использованием кусочной интерполяции⁠ ⁠

По аналогии с Excel. Долгая дорога оцифровки. Часть 4. Макрос по созданию макросов апроксимации простых графиков полиномом и Excel. Долгая дорога оцифровки. Часть 6. Кусочная интерполяция не сложно выполняется макрос по созданию макросов оцифровки простых графиков с использованием кусочной интерполяции.

Описание вводимых данных аналогично ранее изложенному.

Если ещё немного развить тему, то и макрос создания макросов функции с двумя аргументами не проблема:

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

Основное нововведение — определение количества графиков. Если вспомните ещё в Excel. Долгая дорога оцифровки. Часть 2. Забираем данные с листа я писал, что что «снятие точек производить от меньшего Х к большему. При наличии диаграммы зависимости от двух аргументов типаY(X1, X2) начиная с графика меньшего Х2. С обязательным условием — каждая следующая линия должна начинаться с Х меньшего, чем закончилась предыдущая.«. И теперь можно этим воспользоваться — определить количество переходов на новую линию по уменьшению Х по сравнению с предыдущим.

For i = 2 To xVal.Count

If i = xVal.Count Then

Nkon(Ndiap) = i

If xVal.Rows(i) < xVal.Rows(i — 1) Then

Nkon(Ndiap) = i — 1

Ndiap = Ndiap + 1

Nna4(Ndiap) = i

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

Результирующий макрос будет иметь вид:

‘ Поправки Сербия Панчево Страница 34 из 77 Нижний рисунок

Public Function ТЭХ_ПТ80_Рис3(ByRef Go As Single, ByRef CkH As Single) As Single

Dim krit_kriv As Variant

krit_kriv = array(2.96,3.06,3.15)

Dim kriv As Variant

kriv = Array(-0.00271242 * Go + 0.100817, _

-0.00252906 * Go + 0.230858, _

0.000276671 * Go ^ 2 -0.203078 * Go + 31.5862)

ТЭХ_ПТ80_Рис3= kus_interp(krit_kriv, kriv, CkH, 2)

End Function

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

Давайте так, чтобы не утомлять читателя выкопировкой текстовок макросов — выкладываю сие для свободной скачки/использования/модернизации

Если возникнут вопросы как сие работает, распишу. Если у кого то что то не заработает — обращайтесь, посмотрю.

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

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

Например диаграмма с несколькими независимыми графиками типа такой. Можно либо сделать 3 независимых макроса, либо один с выбором графика.

С помощью автоматического создания макросов

Позволит получить (текстовка от графика отличного от представленного выше рисунка)

‘ ТЭХ ПТ80 Рис.3 Давление в отборах при конденсационном режиме [МПа]

Public Function ТЭХ_ПТ80_Рис3(ByRef Go As Single, ByRef Название_графика As Variant) As Variant

Dim krit_graph As Variant

krit_graph = array(1,2,3)

Select Case Название_графика

Case krit_graph(0)

ТЭХ_ПТ80_Рис3 = -0.0027124 * Go ^ 1 + 0.10082

Case krit_graph(1)

ТЭХ_ПТ80_Рис3 = -0.0025397 * Go ^ 1 + 0.23509

Case krit_graph(2)

ТЭХ_ПТ80_Рис3 = -0.0026659 * Go ^ 1 + 0.4529

ТЭХ_ПТ80_Рис3 = 999999999999999

End Function

При желании указывать название графика правится krit_graph = array(«Go»,»Qo»,»qt»).

Ну и гораздо более сложноподчинённые, например что реализовано у меня:

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

Диаграммы режимов ПТ типа ПТ-80

Диаграммы режимов типа Т-250

Нормативной температуры сетевого подогревателя.

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

Из того на что стоит обратить внимание, или маленькие лайфхаки:

1. Не всегда есть разметка осей. Например на диаграмме на последнем скрине вертикальная ось не размечена. Но она в данном случае не нужно. Важно иметь одинаковое значение для левого и правого графиков. Как правило я принимаю в качестве минимального значения оси — 0, в качестве максимального — число клеток (например 12-ть).

2. Внимание! Ось Х не обязательно горизонтальная при «снятии точек»! Например на диаграмме на последнем скрине для правого графика удобно взять в качестве оси Х вертикальную ось а в качестве Y — горизонтальную. Тогда результат обработки левой номограммы будет сразу выступать в качестве аргумента для правой номограммы.

3. Есть варианты оцифровки, когда лучше привязываться не к значениям осей, а к клеточкам 🙂 Да, звучит дико, но иногда проще внести пересчёт внутри макроса, чем реализовать оцифровку по данным осей. Например диаграмма ниже — обратите внимание, что вертикальная ось не обозначена, зато горизонтальная в левой диаграмме разбита на 3 участка с разным масштабом.

Упд. Вспомнил ещё про важную часть — обратные функции. Т.е. есть макрос (готовый!), который по известным Х1, Х2. находит Y. Иногда требуется с использованием данного макроса и известных Y и X1 найти X2. Но об этом в следующий раз. А то и так пост разросся.

Функция «нормализация»

Эта функция возвращает нормализованное значение Z величины x, на основании которого затем вычисляется искомая вероятность p (E ≤ x). Она реализует соотношение (2). Функция требует задания трех аргументов:

х – нормализуемое значение;

среднее – математическое ожидание случайной величины Е;

станд_откл – стандартное отклонение.

Полученное значение Z является аргументом для следующей функции – НОРМСТРАСП ().

Функция «нормстрасп»

Эта функция возвращает стандартное нормальное распределение, т.е. вероятность того, что случайная нормализованная величина Е будет меньше или равна х. Она имеет всего один аргумент – Z, вычисляемый функцией НОРМАЛИЗАЦИЯ ().

Нетрудно заметить, что эти функции следует использовать в тандеме. При этом наиболее эффективным и компактным способом их задания является указание функции НОРМАЛИЗАЦИЯ () в качестве аргумента функции – НОРМСТРАСП (), т.е.:

=НОРМСТРАСП (НОРМАЛИЗАЦИЯ (x; среднее; станд_откл)).

С целью повышения наглядности, в проектируемом шаблоне функции заданы раздельно (ячейки Е18 и F18).

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

Ввести значения постоянных переменных (табл. 2.) в ячейки В2, В4 и D2, D4 листа «Результаты анализа».

Ввести значения диапазонов изменений ключевых переменных (табл. 1.) в ячейки В3, С5 листа «Имитация».

Задать в ячейке В7 требуемое число экспериментов.

Установить курсор в ячейку А11 и вставить необходимое число строк в шаблон (номер последней строки будет вычислен в Е7).

Скопировать формулы блока А10, Е10 требуемое количество раз.

Перейти к листу «Результаты анализа» и проанализировать полученные результаты.

Рассмотрим реализацию выделенных шагов более подробно. Введем значения постоянных переменных в ячейки В2, В4 листа «Результаты анализа». Введем значения диапазонов изменений ключевых переменных в ячейки В3, С5 листа «Имитация». Укажем в ячейке В7 число проводимых экспериментов, например – 20. Установим табличный курсор в ячейку А11.

На следующем шаге необходимо вставить в шаблон нужное количество строк (18).

Теперь необходимо заполнить вставленные строки формулами блока ячеек А10. Е10.

Рис. 4. Результаты имитации

Рис. 5. Результаты анализа

Сумма всех отрицательных значений NPV в полученной генеральной совокупности (ячейка F14) может быть интерпретирована как чистая стоимость неопределенности для инвестора в случае принятия проекта. Аналогично сумма всех положительных значений NPV (ячейка F15) может трактоваться как чистая стоимость неопределенности для инвестора в случае отклонения проекта. Несмотря на всю условность этих показателей, в целом они представляют собой индикаторы целесообразности проведения дальнейшего анализа.

На практике одним из важнейших этапов анализа результатов имитационного эксперимента является исследование зависимостей между ключевыми параметрами. Количественная оценка вариации напрямую зависит от степени корреляции между случайными величинами. На рис. 6. приведен график распределения значений ключевых параметров V, P и Q, построенный на основании 20 имитаций.

Нетрудно заметить, что в целом, вариация значений всех трех параметров носит случайный характер, что подтверждает принятую ранее гипотезу о их независимости. Для сравнения ниже приведен график распределений потока платежей NCF и величины NPV (рис. 7).

Рис. 6. Распределение значений параметров V, P и Q

Рис. 7. Зависимость между NCF и NPV

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

Функция СТАНДАРТИЗАЦИЯ в Excel

Функция СТАНДАРТИЗАЦИЯ в Excel возвращает нормализованное значение (z-оценку) на основе среднего значения и стандартного отклонения.

Возвращаемое значение

Синтаксис

Аргументы

• x — значение для нормализации.
• среднее — Среднее арифметическое распределения.
• standard_dev — стандартное отклонение распределения.

Версия

Примечания по использованию

Функция СТАНДАРТИЗАЦИЯ в Excel возвращает нормализованное значение (z-оценку) на основе среднего значения и стандартного отклонения. Чтобы использовать функцию СТАНДАРТИЗАЦИЯ, рассчитайте среднее значение с помощью функции СРЕДНЕЕ, а стандартное отклонение — с помощью функции СТАНДАРТ.П (см. Ниже).

В показанном примере формула в D5:

О z-показателях / стандартных показателях

Z-оценка или стандартная оценка — это способ стандартизации оценок по той же шкале путем деления отклонения оценки на стандартное отклонение в наборе данных. Результатом является стандартная оценка или z-оценка. Он измеряет количество стандартных отклонений данной точки данных от среднего.

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

Чтобы вычислить z-оценку, необходимо вычислить среднее значение и стандартное отклонение. Формулы в G4 и G5, соответственно:

Где «точки» — именованный диапазон C5: C12.

Если вы хотите узнать больше о z-показателях и статистическом анализе в Excel, я рекомендую книгу Джозефа Шмуллера «Статистический анализ в Excel для чайников».