Использование WorksheetFunction.LinEst

Автор Гришин Илья, 20 сентября 2022, 14:55

0 Пользователи и 1 гость просматривают эту тему.

Гришин Илья

Добрый день.
Ходят слухи, что мою контору переводят на LibreOffice, в связи с чем начинаю готовится с переписыванию десятков тысяч макросов (не преувеличиваю). Столкнулся с проблемой невозможности прямого перевода использования функций МСО в макросах, к чему я привык уже...
Есть подпрограмма определения коэффициентов полинома аппроксимирующей функции (в просторечье линии тренда):
' Проведение интерполяции с использованием функционала Excel
' На выходе - коэффициенты полинома. Число точек должно быть минимум на одну больше, чем степень полинома.
' Данные берутся из программы
[code]Public Sub Linia_trenda(ByRef Y() As Double, ByRef X() As Double, ByVal PolyStep As Integer, ByRef c() As Double, Optional ByRef r2 As Double)
    Dim stepen As Long
    ' Ввожу проверку не превышения степени массива
    If (UBound(Y) - LBound(Y) - 1) < PolyStep Then
        stepen = UBound(Y) - LBound(Y)
    Else
        stepen = PolyStep
    End If
    ' Объявляю переменные, создаю матрицы под размер данных и степень полинома.
    Dim X1() As Double, Y1() As Double
    ReDim X1(LBound(Y) To UBound(Y), 1 To stepen) As Double
    ReDim Y1(LBound(Y) To UBound(Y), 1 To 1) As Double
    ReDim c(1 To stepen + 1) As Double
    ' Заполню массив Х в соответствии со степенью уравнения.
    For i = LBound(X) To UBound(X)
        Y1(i, 1) = Y(i)
        X1(i, 1) = X(i)
        For n = 2 To stepen
            X1(i, n) = X1(i, 1) ^ n
        Next n
    Next i
    ' Нахожу уравнение.
    Dim Coefs As Variant
        Coefs = WorksheetFunction.LinEst(Y1, X1, True, True)
    ' Вытаскиваю коэффициенты полинома.
    For i = 1 To stepen + 1
        c(i) = Coefs(1, i)
    Next i
    ' Вытаскиваю величину достоверности аппроксимации.
    r2 = Coefs(3, 1)
End Sub

Соответственно в неё передаются вектора Х и Y, желаемая степень и на выходе получаем вектор коэффициентов уравнения полинома.

Гугл к сожалению не помог с решением проблемы...
Как понял надо делать типа так, но видимо понял не до конца
Dim svc As Object
svc = createUnoService("com.sun.star.sheet.FunctionAccess")

Coefs = svc.callFunction("LinEst",Y1, X1, True, True)

Буду рад помощи

Ну и использование данной подпрограммы у меня было так:
' Аппроксимация для всего массива исходных данных полиномом
' В подпрограмму передаются все заданные точки и аппроксимация ведётся по всем точкам!
' Xish - массив данных по оси Х
' Yish - массив данных по оси Y
' Xisk - заданное значение Х, при котором требуется поиск соответствующего Y
' stepen - степень аппроксимирующего полинома. Если апроксимация не полиномом - любое число либо пропуск
Public Function polinom(Xish As Variant, Yish As Variant, _
                        ByVal Xisk As Double, _
                        Optional ByVal stepen As Integer = 2) As Variant
Dim i As Integer
Dim X() As Double
Dim Y() As Double

Dim cd() As Double
Подготовка_данных Xish, Yish, X, Y

' Ввожу проверку соответствия переданных точек и заданной степени полинома.
' при несоответствии правлю до максимально допустимой
If stepen > 7 Then stepen = 7
If (UBound(Y) - LBound(Y)) < stepen Then
    stepen = UBound(Y) - LBound(Y)
End If
polinom = 0#
Linia_trenda Y, X, stepen, cd
For i = 1 To stepen + 1
    polinom = polinom + cd(i) * Xisk ^ (stepen - i + 1)
Next i
End Function
Очень удобно быстро найти данные между заданными значениями.

sokol92

Метод callFunction имеет два параметра.
Попробуйте так (по существу не разбирался):
Coefs = svc.callFunction("LinEst",Array(Y1, X1, True, True))
Владимир.

Гришин Илья

sokol92
Не работает. Теперь ругается на диапазон.
Вообще я опирался на код вот отсюда
Sub regressao

       svc = createUnoService( "com.sun.star.sheet.FunctionAccess" )
       
    dim rend ( 1 to 3 ) as double
    dim y ( 1 to 3 ) as double
    dim coef as double
   
       oDoc = thisComponent
      oForm2 = oDoc.DrawPage.Forms.getByName("MainForm").getbyname("SubForm").getbyname("gridace")
      rend(1) = oForm2.RowSet.Columns.getbyname("Rend50").value
      rend(2) = oForm2.RowSet.Columns.getbyname("Rend75").value
      rend(3) = oForm2.RowSet.Columns.getbyname("Rend100").value
     
      y(1) = 0.5
      y(2) = 0.75
      y(3) = 1
       
    result = svc.callFunction( "LINEST", y, rend )

End Sub

но не совсем его понял...

oForm2.RowSet.Columns.getbyname("Rend50").value  - вот это что такое?

sokol92

#3
Выгрузите, пожалуйста, работающий пример в Excel (по возможности, короткий) с вызовом вышеуказанной функции листа, а мы его попробуем перенести в Calc.
Владимир.


sokol92

Спасибо за интересный пример.
Разработчики LibreOffice проделали огромную работу по эмуляции свойств и методов объектов Excel в LO.
Ваш пример является тому подтверждением. Если "хвост" функции Linia_trenda записать так:
    ' Вытаскиваю коэффициенты полинома.
    If LBound(Coefs, 1) = 1 Then  ' Excel - двумерный массив, индексы от 1
      For i = 1 To stepen + 1
          c(i) = Coefs(1, i)
      Next i
      ' Вытаскиваю величину достоверности апроксимации.
      r2 = Coefs(3, 1)
    Else                          ' Calc - массив массивов, индексы от 0
      For i = 1 To stepen + 1
          c(i) = Coefs(0)(i - 1)
      Next i
      ' Вытаскиваю величину достоверности апроксимации.
      r2 = Coefs(2)(0)
    End If
End Sub

то UDF - функция polinom будет работать как в Excel, так и в Calc.

Разумеется, сказанное не означает, что не следует изучать объектную модель LibreOffice.
Владимир.

Гришин Илья

Цитата: sokol92 от 20 сентября 2022, 20:32Разумеется, сказанное не означает, что не следует изучать объектную модель LibreOffice.
Огромное спасибо. Буду пытаться разбираться дальше...
А не посоветуете что почитать про программирование в LO под бэйсиком? А то в Перевод Calc Guide 6.2 можно сказать по этой теме ничего и нет...

kompilainenn

Цитата: Гришин Илья от 20 сентября 2022, 21:04А не посоветуете что почитать про программирование в LO под бэйсиком?
Книгу Питоньяка https://wiki.documentfoundation.org/images/a/a3/%D0%9F%D0%B8%D1%82%D0%BE%D0%BD%D1%8C%D1%8F%D0%BA._%D0%9C%D0%B0%D0%BA%D1%80%D0%BE%D1%81%D1%8B_%D0%B2_%D0%9E%D0%BE%D0%BE.pdf

Цитата: Гришин Илья от 20 сентября 2022, 21:04А то в Перевод Calc Guide 6.2 можно сказать по этой теме ничего и нет...
и быть не могло, макросы - это огромная отдельная тема =(
Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут

Гришин Илья

#8
Продолжение вопроса.
Если макросы в модуле прицепленном к файлу - всё работает.
А если макрос перенести в "Мои макросы и диалоги", то выдаёт ошибку на строку Coefs = WorksheetFunction.LinEst(Y1, X1, True, True)
"Ошибка выполнения Basic. '91' Объектная переменная не установлена"
Это можно побороть?

Public Sub Linia_trenda(ByRef Y() As Double, ByRef X() As Double, ByVal PolyStep As Integer, ByRef c() As Double, Optional ByRef r2 As Double)
    svc = createUnoService( "com.sun.star.sheet.FunctionAccess" )
   
    Dim stepen As Long
    ' Ввожу проверку не превышения степени массива
    If (UBound(Y) - LBound(Y) - 1) < PolyStep Then
        stepen = UBound(Y) - LBound(Y)
    Else
        stepen = PolyStep
    End If
    ' Объявляю переменные, создаю матрицы под размер данных и степень полинома.
    Dim X1() As Double, Y1() As Double
    ReDim X1(LBound(Y) To UBound(Y), 1 To stepen) As Double
    ReDim Y1(LBound(Y) To UBound(Y), 1 To 1) As Double
    ReDim c(1 To stepen + 1) As Double
    ' Заполню массив Х в соответствии со степенью уравнения.
    For i = LBound(X) To UBound(X)
        Y1(i, 1) = Y(i)
        X1(i, 1) = X(i)
        For n = 2 To stepen
            X1(i, n) = X1(i, 1) ^ n
        Next n
    Next i
    Dim Coefs As Variant
        Coefs =svc.callFunction( "LINEST", Array(Y1, X1, True, True))
    ' Вытаскиваю коэффициенты полинома.
    If LBound(Coefs, 1) = 1 Then  ' Excel - двумерный массив, индексы от 1
      For i = 1 To stepen + 1
          c(i) = Coefs(1, i)
      Next i
      ' Вытаскиваю величину достоверности апроксимации.
      r2 = Coefs(3, 1)
    Else                          ' Calc - массив массивов, индексы от 0
      For i = 1 To stepen + 1
          c(i) = Coefs(0)(i - 1)
      Next i
      ' Вытаскиваю величину достоверности апроксимации.
      r2 = Coefs(2)(0)
    End If
End Sub
Ругается на  r2 = Coefs(2)(0)
"Ошибка выполнения Basic. '9' Индекс за пределами диапазона"


УПД.Вот так надо обращаться, тогда работает
Coefs =svc.callFunction("LinEst", Array(Y1, X1, 1, 1))

sokol92

#9
Эти вопросы на данной стадии обучения задавать рановато. :)
Режим
Option VBASupport 1имеет ряд особенностей и изначально рассчитан на то, что модуль Basic находится в библиотеке документа. При переносе функции в библиотеку из My Macros... следует иметь в виду, что LO работает с документами разных типов и не обязан "знать", что в Вашем случае речь идет о функции рабочего листа документа Calc.

Можно ли перенести Вашу функцию в библиотеку из My Macros - можно (но пояснение деталей выведет нас далеко за пределы темы).
Перенесите функцию Linia_trenda в любой модуль библиотеки Standard из My Macros... (Мои макросы... в интерфейсе на русском языке). Замените строку с присвоением Coefs на
  Dim xlApp
  xlApp=CreateUnoService("ooo.vba.excel.Application")
  Coefs = xlApp.WorksheetFunction.LinEst(Y1, X1, True, True)
Должно "взлететь".

Разумеется, использование "родных" методов всегда лучше.
Владимир.

Гришин Илья

Цитата: sokol92 от 21 сентября 2022, 13:26Эти вопросы на данной стадии обучения задавать рановато. :)
К сожалению приходится не учиться, а работать и в авральном порядке переделывать отлаженный мат.механизм...
Повторюсь, вот так работает
svc = createUnoService("com.sun.star.sheet.FunctionAccess")
Coefs =svc.callFunction("LinEst", Array(Y1, X1, 1, 1))


Спасибо за отзывчивость.