Теперь расскажу про программу интерпретации КВД. Кто не из нефтянки - КВД это не кожвендиспансер, а кривая восстановления давления.
Автоматизируй_это_2
В двух словах что такое КВД: на забой (т.е. на дно скважины (простите меня, профессионалы)) остановленной скважины спускают манометр. Он находится там некоторое время, записывает значения давления, потом его поднимают и записанные данные интерпретируют.
На вопрос "как интерпретируют?" - написаны десятки книг, это целая наука. Здесь пойдет рассказ об интерпретации КВД по методу Хорнера. Метод старый, имеет ряд недостатков, но блог о программировании, так что не надо тут ля-ля.
Имеем следующее. От подрядчика нам прилетает два здоровенных текстовых файла, содержащих очень много строк с данными. Очень много. Доходило до 3 млн, но чаще - не более 100 тысяч.
Эти данные нужно вставить в 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).