Как вытащить цифры из текста в excel

Извлечение чисел из текста в Excel ⁠ ⁠

Извлечь числа из строки текста в Excel, естественно можно с помощью формул. Например, в этом может помочь следующая формула массива:

Тем не менее, у использованной выше формулы есть определенные минусы:

• Во-первых, все числа, например, из текста «Задача 5 от 19 Ноября» выдаются не разделёнными, образую таким образом одно слитное число, тогда же как информация о том, что числа на самом деле в оригинальном тексте разделены другими словами потенциально может быть важной.

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

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

Function extractDelimitedNumbers(ByVal strOriginalText As String) As String

Dim strExtractedNumbers As String

Dim lngTextLength As Long

Dim lngPositionCounter As Long

‘Проверка, указано ли название файла

If strOriginalText <> «» Then

‘Проверка каждой позиции названия

For lngPositionCounter = 1 To lngTextLength

If IsNumeric(Mid(strOriginalText, lngPositionCounter, 1)) = True Then

‘. то сохраняем в переменную

strExtractedNumbers = strExtractedNumbers & Mid(strOriginalText, lngPositionCounter, 1)

‘Разделение отдельно стоящих в названии чисел с помощью «_»

If lngPositionCounter + 1 <= lngTextLength Then

If IsNumeric(Mid(strOriginalText, lngPositionCounter + 1, 1)) = False Then

strExtractedNumbers = strExtractedNumbers & «_»

‘Удаляем по итогу лишний нижний пробел, если таковой имеется

If Right(strExtractedNumbers, 1) = «_» Then

strExtractedNumbers = Left(strExtractedNumbers, Len(strExtractedNumbers) — 1)

Как использовать этот код:

1. Открыть файл Excel, в котором нужно применить функцию (лучше его копию)

2. Открыть редактор VBA с помощью комбинации клавиш Alt+F11

3. В верхнем левом углу нажать на «Insert» и затем «Module».

4. Скопировать текст функции и вставить в открывшееся окно в центре редактора VBA

5. Сохранить файл в формате xlsm (формат xlsx не сохраняет макросы!). Для этого открываем окно сохранить как при помощи клавиши F12 либо File -> Save as -> Browse. По открытии окна сохранения файла в поле «Тип файла» выбираем «Книга Excel с поддержкой макросов»

6. Подтверждаем сохранение. Теперь функция может использоваться как самая обычная функция на рабочем листе Excel. То есть ставим знак равно, и прописываем название нашей пользовательской функции «extractDelimitedNumbers». В скобках указываем текст, из которого должны быть извлечены числовые значения:

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

Конечно, для этого видео нужно уже владеть определенными знаниями VBA. Если ты еще совсем новичок в области VBA, то также могу предложить посмотреть вот этот курс VBA:

661 пост 14.8K подписчиков

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

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

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

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

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

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

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

Excel в regexp не умеет?

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

Читать ещё на Пикабу

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

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

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

Помогите поправить надстройку Exel⁠ ⁠

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

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

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

Установка рисунков в качестве маркеров позволяет разнообразить внешний вид документации, сделав её нагляднее. Установка смайлов (© 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. Но об этом в следующий раз. А то и так пост разросся.