Найти тему
SimplyVBA

Автоматизация интерпретации КВД

Оглавление

Теперь расскажу про программу интерпретации КВД. Кто не из нефтянки - КВД это не кожвендиспансер, а кривая восстановления давления.

Автоматизируй_это_2

В двух словах что такое КВД: на забой (т.е. на дно скважины (простите меня, профессионалы)) остановленной скважины спускают манометр. Он находится там некоторое время, записывает значения давления, потом его поднимают и записанные данные интерпретируют.

На вопрос "как интерпретируют?" - написаны десятки книг, это целая наука. Здесь пойдет рассказ об интерпретации КВД по методу Хорнера. Метод старый, имеет ряд недостатков, но блог о программировании, так что не надо тут ля-ля.

Имеем следующее. От подрядчика нам прилетает два здоровенных текстовых файла, содержащих очень много строк с данными. Очень много. Доходило до 3 млн, но чаще - не более 100 тысяч.

Обрабатываемые файлы. 1 столбец - время, 2 - давление, 3 - температура. В 1 файле ~15к строк, во втором ~28к строк
Обрабатываемые файлы. 1 столбец - время, 2 - давление, 3 - температура. В 1 файле ~15к строк, во втором ~28к строк

Эти данные нужно вставить в MS Excel, сделать несколько магических движений - и получить величину пластового давления в этой скважине (ради чего КВД и затевается, собственно говоря).

Поехали.

Разобьем задачу на под-задачи:

1) Импортировать данные

2) Удалить лишние данные

3) Еще удалить лишние данные

4) Построить линию тренда по выбранным данным и пойти пить чай

Решаем.

1) Загружаем это чудо надо в Excel:

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

FileAdress = Application.InputBox(prompt:="Введите адрес файла", Default:=ThisWorkbook.Path & "\")

If FileAdress = "False" Then Exit Sub

FileName = Application.InputBox(prompt:="Введите адрес имя нужного файла", Default:="замер 1.las")

If FileName = "False" Then Exit Sub

Start_Row = Application.InputBox(prompt:="Введите номер строки, с которой следует импортировать данные", Default:="28")

Workbooks.OpenText FileName:=FileAdress + FileName, Origin:=866, StartRow:=Start_Row, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _

Space:=True, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True

Windows(FileName).Activate

Worksheets(1).Range("A1:C" & ActiveCell.SpecialCells(xlLastCell).Row).COPY

ThisWorkbook.Activate

Range("E5").Select

ActiveSheet.Paste

Application.CutCopyMode = False

Windows(FileName).Close

Вы можете заметить, что тут я использую простое copy-paste. Это не православно. Кошернее было бы через массив или еще как-то - но это я понимаю спустя 6 лет после этой разработки, а тогда даже это было тяжело. Впрочем, код работает.

Также здесь применен отличный от предыдущего поста метод определения последней строки: ActiveCell.SpecialCells(xlLastCell).Row

Эта конструкция меня несколько раз подводила, поэтому в 99.9 % случаев пользуюсь LastRow = Cells(.Rows.Count, 1).End(xlUp).Row

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

2) Чистка

Не все йогурты одинаково полезные и не все данные из загруженных - нужны. Спросите, почему нельзя было выкинуть до импорта? Отвечает Александр Друзь - импортируемый файл представляет собой запись данных с прибора, которая ведется начиная от включения прибора и до его выключения. А интересующие нас события происходят где-то между. Где именно - прибору не ведомо.

Что нужно сделать - отрезать начало и конец записи, пока прибор спускался/поднимался и оставить только тот участок, на котором прибор стоял.

Как сказано в подписи к рисунку выше - файл имеет столбец "время", куда записывается число секунд после включения прибора. Время включения прибора фиксируется (тут +- т.к. это промысел, а не лаборатория). Фиксируется время завершения спуска прибора на забой и время начала подъема (нас интересует запись прибора между этими событиями). И тут такой же +- т.к. мы по-прежнему в лаборатории.

Для точного определения нужного нам фрагмента записи нужно понять, где началась и завершилась стоянка прибора. Сделать это можно, опираясь на значение давления и температуры. На стоянке они почти постоянны (правда, давление слегка увеличивается, это же КВД), в динамике - сильно меняются.

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

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

Ниже - блок кнопок навигации.

Блок кнопок навигации
Блок кнопок навигации

В него заносим те самые +- известные даты. И происходит магия:

А магия работает весьма просто:

Private Sub End_KVD_1_Click()

date1 = Worksheets("Исх. данные").Range("begin_time1")

date2 = Range("End_KVD1_Date").Text + " " + Range("End_KVD1_Time").Text

seconds_diff = DateDiff("s", date1, date2)

Time_Step = Range("second_time1").Value - Range("first_time1").Value

desired_row = seconds_diff / Time_Step

Range("D" & desired_row + 5).Activate

End Sub

Берем с листа значение двух ячеек - в одной дата начала записи, в другой - дата конца. К дате конца добавляем еще и время завершения записи, чтобы получилось точнее. Вычисляем количество секунд между этими датами с помощью встроенной функции DateDiff. Затем, узнав шаг записи прибора (Time_Step) можем легко определить, куда нам нужно переместиться.

Строго говоря, строки кода выше - пример, как делать не надо. Ужасное происходит в переменной date2. Она получается путем сложения (конкатенации) текста из двух ячеек. И это очень плохо. Если мы используем .Text то при перемене формата наш код может перестать работать. Стоит пользователю поменять формат даты с ДД.ММ.ГГГГ на ДД-ММ и привет.

Сейчас эту задачу я бы решил так:

date2 = CDate(CDate(Range("End_KVD1_Date")) & " " & TimeValue(Range("End_KVD1_Time").Text))

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

date2 = DateAdd("h", Hour(Range("End_KVD1_Time")), CDate(Range("End_KVD1_Date")))

date2 = DateAdd("n", Minute(Range("End_KVD1_Time")), date2)

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

3) Еще чистка

Мы пока лишь посмотрели границы интересующего нас массива данных, чтобы его укоротить. Но зачем? В то время (Excel 2007, a long time ago in a galaxy far far away...) на график можно было строить только для 32 000 значений. А для интерпретации КВД по Хорнеру нужен график! Поэтому иногда приходилось сокращать число чисел (т.е. количество значений). Но нельзя удалить начало или конец данных. Середину вырезать - тоже нельзя. Что остается? Только проредить. И вот как:

Private Sub Change_step2_Click()

CurrentStep = Range("second_time2").Value - Range("first_time2").Value

New_Time_Step = Application.InputBox(prompt:="Введите новое значение шага. Текущий шаг = " & CurrentStep & " с.", Default:=CurrentStep * 2)

If New_Time_Step = 0 Then GoTo EndMakro

Begin_Point = Range("Left_2").Value

End_Point = Range("Right_2").Value

Divisor = New_Time_Step / CurrentStep

ArrayBound = Int((End_Point - Begin_Point) / Divisor) + 1

ReDim ar(1 To ArrayBound)

j = 1

For i = Begin_Point To Begin_Point + ArrayBound - 1

ar(j ) = Cells(i + (Divisor - 1) * j, 13).Value

j = j + 1

Next i

Worksheets("Данные для расчета") .Range("N8").Resize(ArrayBound + 8).Value = WorksheetFunction.Transpose(ar)

EndMakro:

End Sub

В начале процедуры спрашиваем новый временной шаг у пользователя. Здесь - прекрасная возможность отстрелить себе ногу. Дело в том, что значение нового временного шага используется в переменной Divisor, которая должна быть целой и положительной. Т.е. если пользователь введет -1 или 7 (при текущем шаге 5), то словим исключение. Посему - всегда делайте проверку вводимых данных. Данный код использовался только при текущем шаге 1, поэтому на дробную часть я проверку не реализовывал, а про отрицательные случае сообразил только сейчас - моим коллегам не приходило в голову так издеваться надо мной и программой.

Проверку на целое число я делаю так:

If a - Fix(a) <> 0 Then ' a - дробное, иначе - a целое

На этом этапе у нас уже точно меньше 32 000 чисел и можно переходить к самому интересному.

4) График!

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

Делаем!

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

Worksheets("Расчет Рпл").ChartObjects.Select

With ActiveChart

.SeriesCollection.NewSeries

.SeriesCollection(1).XValues = "='Данные для расчета'!$M$" & BeginKVD & ":$M$" & LastRow + 6

.SeriesCollection(1).Values = "='Данные для расчета'!$O$" & BeginKVD & ":$O$" & LastRow + 6

End With

Чем он плох? Вот чем:

Первое - использован метод Select. Старайтесь его избегать. Лишнее замедление кода. Нужен какой-то объект? Обратитесь к нему по его номеру или по имени. В данном случае можно было:

With Worksheets("Расчет Рпл").ChartObjects(1).Chart

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

Третье - использована ссылка на объект с помощью ActiveChart. Это проистекает из первого - мы выбрали объект, теперь он активен. Лечиться, опять же, строкой кода выше.

Построенный график выглядит так
Построенный график выглядит так

Здесь видны кнопки "Изменить границы...", посмотрим, что они делают:

Работают они несложно - считываем значения из измененных пользователем ячеек и передаем их на график.

LeftBorderKVD = Range("LeftBorderKVD").Value

RightBorderKVD = Range("RightBorderKVD").Value

ChartObjects.Select

With ActiveChart

.SeriesCollection(1).XValues = "='Данные для расчета'!$M$" & LeftBorderKVD & ":$M$" & RightBorderKVD

.SeriesCollection(1).Values = "='Данные для расчета'!$O$" & LeftBorderKVD & ":$O$" & RightBorderKVD

End With

Эти кнопки нужны, чтобы интерпретатор выбрал наиболее представительный участок на представленном графике и по нему была построена линия тренда. Цель почти достигнута - нам нужно уравнение этой линии тренда. Как его получал я во время разработки этой программы я вам говорить не буду, но покажу, как это нужно сделать c помощью функции Linest:

ArrTrendline = WorksheetFunction.LinEst(y, x, 1, 1)

k = ArrTrendline(1, 1)

b = ArrTrendline(1, 2)

r2 = ArrTrendline(3, 1)

Как видно - эта функция из класса WorksheetFunction, т.е. ее можно вызвать с рабочего листа. В русскоязычном Excel она называется ЛИНЕЙН. Попробуйте в пустую ячейку вписать "=Линейн(" и посмотрите синтаксис функции.

В коде я передаю в качестве аргументов в эту функцию массивы значений y, x и получаю коэффициенты уравнения линейного тренда

(у = k * x + b)

r2 - та самая величина R^2, используемая для оценки корректности аппроксимации этого графика этой линии тренда. Чем ближе к 1 - тем лучше. Но не всегда этой характеристике следует слепо доверять.

Если нам нужно получить линию тренда какого-либо графика, то мы передаем вместо y, x значения, по которым построен график:

With ActiveChart

ArrTrendline = WorksheetFunction.LinEst(.SeriesCollection(1).Values, .SeriesCollection(1).XValues, 1, 1)

End with

Для вычисления значения давления нам нужен коэффициент b линии тренда. Делаем с ним кое-какую арифметику и величина пластового давления найдена.

На этом все. Задача решена. Можно браться за новую :)

Вопросы? В комментарии!

P.S. Функция LinEst позволяет вычислить коэффициенты любой линии тренда без построения самого графика. Если вам нужно найти зависимость между 2 наборами значений - смело используйте. Но коэффициенты именного линейного тренда быстрее будет найти с помощью функций =ОТРЕЗОК(), =НАКЛОН(). Их названия говорят сами за себя - первая возвращает величину b, а вторая - угол наклона линии тренда к оси Х (k).