Как вытащить число или часть текста из текстовой строки в Excel
Сегодня мы с вами рассмотрим весьма распространённую ситуацию, возникающую в работе экономиста связанную с анализом данных.
Как правило, экономисту поручают проведение всевозможных видов анализа на основании бухгалтерских данных, группировку их специальным образом, получение дополнительных срезов, отличающихся от имеющихся бухгалтерских аналитик и т.д.
Речь здесь уже идет о преобразовании данных бухгалтерского учета в данные управленческого учета. Мы не будем говорить о необходимости сближения бухгалтерского и управленческого учета, или, по крайней мере, получения нужных срезов и аналитик в имеющихся учетных программах в автоматическом режиме. К сожалению, зачастую экономисту приходиться «перелопачивать» огромные объемы информации вручную.
И здесь, очень многое зависит, насколько эффективно организована работа, насколько экономист владеет своим основным прикладным инструментом – программой Excel, знает ее возможности и эффективные приемы обработки информации. Ведь одну и туже задачу можно решать разными способами, затрачивая разное количество времени и усилий.
Рассмотрим конкретную ситуацию. Вам нужно подготовить отчёт в разрезе, который нельзя получить в бухгалтерской программе. Вы выгрузили в Excel отчет по проводкам (оборотно-сальдовую ведомость, карточку счета и т.д. – не суть важно) и видите, что для нормальной фильтрации данных или создания сводной таблицы для анализа данных у вас не хватает одного признака (аналитики, разреза, субконто и т.д.).
Критически взглянув на таблицу, вы видите, что необходимый вам признак операции находиться тут же в таблице, но не в отдельной ячейке, а внутри текста. Например, код филиала в наименовании документа. А вам как раз надо подготовить отчет по поставщикам в разрезе филиалов, т.е. по двум признакам, один из которых отсутствует в приемлемом для дальнейшей обработки информации виде.
Если в таблице находиться десять операций, то проще проставить признак вручную в соседнем столбце, однако если записей несколько тысяч, то это уже проблематично.
Вся трудность, в том чтобы извлечь код из текстовой строки.
Возможна ситуация, когда этот код находиться всегда в начале текстовой строки или всегда в конце.
В этом случае, мы можем извлекать код или часть текста при помощи функций ЛЕВСИМВ и ПРАВСИМВ, которые возвращают заданное количество знаков соответственно с начала строки или с конца строки.
Текст – обязательный аргумент. Текстовая строка, содержащая символы, которые требуется извлечь.
Количество_знаков — необязательный аргумент. Количество символов, извлекаемых функцией ЛЕВСИМВ (ПРАВСИМВ).
«Количество_знаков» должно быть больше нуля или равно ему. Если «количество_знаков» превышает длину текста, функция ЛЕВСИМВ (ПРАВСИМВ) возвращает весь текст. Если значение «количество_знаков» опущено, оно считается равным 1.
Зная количество знаков, которые содержит код, мы легко извлечем необходимые символы.
Сложнее если нужные нам символы находятся в середине текста.
Извлечь число, текст, код и т.д. из середины текстовой строки может функция ПСТР, возвращает заданное число знаков из строки текста, начиная с указанной позиции.
=ПСТР(текст; начальная_позиция; количество_знаков)
Текст – обязательный аргумент. Текстовая строка, содержащая символы, которые требуется извлечь.
Начальная_позиция – обязательный аргумент. Позиция первого знака, извлекаемого из текста. Первый знак в тексте имеет начальную позицию 1 и так далее.
Количество_знаков – обязательный аргумент. Указывает, сколько знаков должна вернуть функция ПСТР.
Самый простой случай – если код находиться на одном и том же месте от начала строки. Например, у нас наименование документа начинается всегда одинаково «Поступление товаров и услуг ХХ ….»
Наш признак «ХХ» — код филиала начинается с 29 знака и имеет 2 знака в своем составе.
В нашем случае формула будет иметь вид:
Однако не всегда все так безоблачно. Предположим, мы не можем со 100% уверенностью сказать, что наименование документа у нас во всех строках будет начинаться одинаково, но мы точно знаем, что признак филиала закодирован в номере документа следующим образом:
Первый символ – первая буква в наименовании филиала, второй символ – это буква Ф (филиал) и далее следует пять нулей «00000». Причем меняется только первый символ — первая буква наименования филиала.
Обладая такими существенными знаниями, мы можем смело использовать функцию ПОИСК, которая находит нужный нам текст в текстовой строке и возвращают начальную позицию нужного нам текста внутри всей текстовой строки.
=ПОИСК(искомый_текст; текст_для_поиска; [нач_позиция])
Искомый_текст – обязательный аргумент. Текст, который требуется найти.
Просматриваемый_текст – обязательный аргумент. Текст, в котором нужно найти значение аргумента искомый_текст.
Нач_позиция – необязательный аргумент. Номер знака в аргументе просматриваемый_текст, с которого следует начать поиск.
Функция ПОИСК не учитывает регистр. Если требуется учитывать регистр, используйте функцию НАЙТИ.
В аргументе искомый_текст можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак или звездочку, введите перед ним тильду (
Обозначив меняющийся первый символ знаком вопроса (?), мы можем записать итоговую формулу для выделения кода филиала в таком виде:
Эта формула определяет начальную позицию кода филиала в наименовании документа, а затем возвращает два знака кода, начиная с найденной позиции.
В результате, мы получим в отдельном столбце код филиала, который сможем использовать как признак для фильтрации, сортировки или создания сводной таблицы.
Как вытащить из строки только цифры в Excel
Недавно мы рассмотрели как можно вернуть из строки только буквы, исключив цифры. Теперь же мы рассмотрим обратную ситуацию, когда из строки нам необходимо вернуть только цифры, исключив текстовую информацию.
Задача все та же — у нас есть столбец с данными (и текс и цифры) и нам требуется разбить отдельно текст и отдельно цифры. Как мы писали выше с текстом мы уже разобрались, осталось вытащить цифры.
Встроенной функции для этих целей в Excel так же нет, поэтому мы будем писать пользовательскую.
Как пользоваться?
Открываем редактор VBA в Excel (Alt+F11), или правой кнопкой по листу и выбираем пункт «Исходный текст».
Создаем новый модуль → Insert → Module
Переключаемся на российскую раскладку клавиатуры, копируем код, указанный выше и вставляем в модуль
Далее в нужной ячейке, где необходимо вывести только буквы, прописываем формулу:
Как из текста вытащить цифры в excel
Вы когда-нибудь хотели извлекать числа только из списка строк в Excel? Здесь я познакомлю вас с некоторыми способами быстрого и легкого извлечения только чисел в Excel.
Метод 1. Извлечь число только из текстовых строк с формулой
Следующая длинная формула может помочь вам извлечь только числа из текстовых строк, пожалуйста, сделайте следующее:
Выберите пустую ячейку, в которую вы хотите вывести извлеченное число, затем введите эту формулу: = СУММПРОИЗВ (СРЕДНЯЯ (0 & A5; НАИБОЛЬШИЙ (ИНДЕКС (- СРЕДНЕЕ (A5; СТРОКА (КОСВЕННАЯ («1:» & LEN (A5))); 1)) * СТРОКА (КОСВЕННАЯ («1:» & LEN (A5) )), 0), СТРОКА (КОСВЕННАЯ («1:» & LEN (A5)))) + 1, 1) * 10 ^ ROW (INDIRECT («1:» & LEN (A5))) / 10) , а затем перетащите маркер заполнения, чтобы заполнить диапазон, необходимый для применения этой формулы. Смотрите скриншот:
Ноты:
- 1. A5 стоит первые данные, из которых вы хотите извлечь числа только из списка.
- 2. Если в строке нет чисел, результат будет показан как 0.
Извлекать числа только из текстовых строк:
Работы С Нами Kutools for ExcelАвтора ВЫДЕРЖКИ функция, вы можете быстро извлекать только числа из ячеек текстовой строки. Нажмите, чтобы загрузить Kutools for Excel!
Метод 2: извлекать число только из текстовых строк с кодом VBA
Вот код VBA, который также может оказать вам услугу, пожалуйста, сделайте следующее:
1. Удерживайте Alt + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии вставьте следующий код в Модули Окно.
Код VBA: извлекать номер только из текстовой строки:
3. А затем нажмите F5 нажмите клавишу для запуска этого кода, и появится окно подсказки, напоминающее о выборе текстового диапазона, который вы хотите использовать, см. снимок экрана:
4, Затем нажмите OK, следующее окно подсказки, выберите ячейку для вывода результата, см. снимок экрана:
5, Наконец, нажмите OK кнопку, и все числа в выбранных ячейках были извлечены сразу.
Метод 3: извлечь номер только из текстовой строки с помощью Kutools for Excel
Kutools for Excel также имеет мощную функцию, которая называется ВЫДЕРЖКИ, с помощью этой функции вы можете быстро извлечь только числа из исходных текстовых строк.
После установки Kutools for Excel, пожалуйста, сделайте следующее:
1. Щелкните ячейку помимо текстовой строки, в которую вы поместите результат, см. Снимок экрана:
2. Затем нажмите Кутулс > Функции Kutools > Текст > ВЫДЕРЖКИ, см. снимок экрана:
3. В Аргументы функций диалоговом окне выберите ячейку, в которой вы хотите извлечь числа из Текст текстовое поле, а затем введите правда or ложный в N текстовое поле, см. снимок экрана:
Внимание: Аргумент N является необязательным элементом, если вы введете правда, он вернет числа как числовые, если вы введете ложный, он вернет числа в текстовом формате, значение по умолчанию — false, поэтому вы можете оставить его пустым.
4, Затем нажмите OK, числа были извлечены из выбранной ячейки, затем перетащите маркер заполнения вниз к ячейкам, которые вы хотите применить к этой функции, вы получите следующий результат:
Method 4:Split text string into text and number columns individually with Kutools for Excel
If you want to split the text string into separated text and number columns, Kutools for Excel’s Split Cells also can help you to solve this task.
After installing Kutools for Excel , please do as follows:
1. Select the text string that you want to split, and then click Kutools > Text > Split Cells, see screenshot:
2. In the Split Cells dialog box, select Split to Columns under the Type section, and then check Text and number from the Split by section, see screenshot:
3. And then click Ok button, select a cell to put the result in the popped out dialog box, see screenshot:
4. Then click OK button, and the text strings have been split into separated text and number columns as following screenshot shown:
Метод 4: извлечь десятичное число только из текстовой строки с формулой
Если текстовые строки содержат некоторые десятичные числа на вашем листе, как вы могли бы извлечь из текстовых строк только десятичные числа?
Приведенная ниже формула может помочь вам быстро и легко извлечь десятичные числа из текстовых строк.
Введите эту формулу ： =LOOKUP(9.9E+307,—LEFT(MID(A5,MIN(FIND(<1,2,3,4,5,6,7,8,9,0>, $A5&»1023456789″)),999),ROW(INDIRECT(«1:999»)))) ，, А затем заполните дескриптор до ячеек, которые вы хотите содержать эту формулу, все десятичные числа были извлечены из текстовых строк, см. Снимок экрана:
Извлечение чисел из текста в 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:
635 постов 14.5K подписчиков
Правила сообщества
2. Публиковать посты соответствующие тематике сообщества
3. Проявлять уважение к пользователям
4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.
По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях
Важно — сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.
Утверждения вроде «пост — отстой», это оскорбление автора и будет наказываться баном.
Excel в regexp не умеет?
какие есть способы запуска формулы массива кроме комбинации клавиш на клавиатуре?
Помощь с макросом
Дамы и господа, добрый день.
Есть макрос который берет выделенные ячейки и сохраняет их в папку с файлом в виде картинки 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. Но об этом в следующий раз. А то и так пост разросся.