Funkcje zakresu komórek

22.10.2020

Znajdowanie używanego zakresu komórek arkusza

Używany obszar arkusza to najmniejszy zakres komórek, który zawiera wszystkie komórki arkusza kalkulacyjnego z dowolną zawartością (liczby, tekst, formuły) lub z widocznym formatowaniem (obramowanie i kolor tła). Prawą dolną komórkę używanego obszaru możemy łatwo odnaleźć, wybierając z klawiatury Ctrl + End.

Kursor komórek to po prostu zakres komórek, który udostępnia metody przemieszczania się po arkuszu (LINK). Oczywiście tak wykonywane „przemieszczenia” są niewidoczne – aktywne ustawienie nie ulega zmianie.

Omówimy działanie metod kursora komórek oCllCursor występującego w zamieszczonej niżej funkcji UsedArea() (LINK):

• gotoStartOfUsedArea( False ) – redukuje rozmiar kursora komórek do pojedynczej komórki będącej początkiem używanego obszaru
• gotoStartOfUsedArea( True ) – zmienia rozmiar kursora komórek tak, aby jego początek pokrywał się z początkiem używanego obszaru
• gotoEndOfUsedArea( False ) – redukuje rozmiar kursora komórek do pojedynczej komórki będącej końcem używanego obszaru
• gotoEndOfUsedArea( True ) – zmienia rozmiar kursora komórek tak, aby jego koniec pokrywał się z końcem używanego obszaru

Function UsedArea(sFrame as String) as Long
    'w zależności od parametru funkcja zwraca indeks:
    '("L") lewej kolumny, ("U") górnego wiersza, ("R") prawej kolumny, ("B") dolnego wiersza,
    'które wyznaczają granice używanego obszaru arkusza
    Dim oSht as Object, oCllCursor as Object
    oSht = ThisComponent.getCurrentController().ActiveSheet
    oCllCursor = oSht.createCursor() 'tworzy kursor komórek obejmujący cały arkusz
    UsedArea = -1 'ta wartość zostanie zwrócona w przypadku błędnego parametru
    If sFrame = "L" OR sFrame = "U" Then
        oCllCursor.gotoStartOfUsedArea( False ) 'redukuje rozmiar kursora komórek do jednej komórki
        If sFrame = "L" Then
            UsedArea = oCllCursor.getRangeAddress().StartColumn 'indeks lewej kolumny
        Else 'sFrame = "U"
            UsedArea = oCllCursor.getRangeAddress().StartRow 'indeks górnego wiersza
        End If
    End If
    If sFrame = "R" OR sFrame = "B" Then
        oCllCursor.gotoEndOfUsedArea( False ) 'redukuje rozmiar kursora komórek do jednej komórki
        If sFrame = "R" Then
            UsedArea = oCllCursor.getRangeAddress().EndColumn 'indeks prawej kolumny
        Else 'sFrame = "B"
            UsedArea = oCllCursor.getRangeAddress().EndRow 'indeks dolnego wiersza
        End If
    End If
End Function

Sub Zakresy1()
    MsgBox "Indeksy kolumn i wierszy ograniczających używany obszar:" & Chr(10) & _
           "Lewa kolumna – "  & UsedArea("L") & Chr(10) & _
           "Górny wiersz – "  & UsedArea("U") & Chr(10) & _
           "Prawa kolumna – " & UsedArea("R") & Chr(10) & _
           "Dolny wiersz – "  & UsedArea("B")
End Sub

Sub Zakresy2()
    Dim oSht as Object, oCllCursor as Object
    oSht = ThisComponent.getCurrentController().ActiveSheet
    oCllCursor = oSht.createCursor() 'tworzy kursor komórek obejmujący cały arkusz
    oCllCursor.gotoStartOfUsedArea( True ) 'zmienia rozmiar kursora komórek tak, aby jego początek pokrywał się z początkiem używanego obszaru
    MsgBox "Indeksy kolumn i wierszy ograniczających używany obszar:"    & Chr(10) & _
           "Lewa kolumna – "  & oCllCursor.getRangeAddress().StartColumn & Chr(10) & _
           "Górny wiersz – "  & oCllCursor.getRangeAddress().StartRow    & Chr(10) & _
           "Prawa kolumna – " & oCllCursor.getRangeAddress().EndColumn   & Chr(10) & _
           "Dolny wiersz – "  & oCllCursor.getRangeAddress().EndRow
End Sub

Sub Zakresy3()
    Dim oSht as Object, oCllCursor as Object
    oSht = ThisComponent.getCurrentController().ActiveSheet
    oCllCursor = oSht.createCursor() 'tworzy kursor komórek obejmujący cały arkusz
    oCllCursor.gotoEndOfUsedArea( True ) 'zmienia rozmiar kursora komórek tak, aby jego koniec pokrywał się z końcem używanego obszaru
    MsgBox "Indeksy kolumn i wierszy ograniczających używany obszar:"    & Chr(10) & _
           "Lewa kolumna – "  & oCllCursor.getRangeAddress().StartColumn & Chr(10) & _
           "Górny wiersz – "  & oCllCursor.getRangeAddress().StartRow    & Chr(10) & _
           "Prawa kolumna – " & oCllCursor.getRangeAddress().EndColumn   & Chr(10) & _
           "Dolny wiersz – "  & oCllCursor.getRangeAddress().EndRow
End Sub

Sub Zakresy4()
    Dim oSht as Object, oCllCursor as Object
    oSht = ThisComponent.getCurrentController().ActiveSheet
    oCllCursor = oSht.createCursor() 'tworzy kursor komórek obejmujący cały arkusz
    oCllCursor.gotoStartOfUsedArea( False ) 'redukuje rozmiar kursora komórek do jednej komórki
    oCllCursor.gotoEndOfUsedArea( True ) 'rozszerza rozmiar kursora komórek na cały używany obszar
    MsgBox "Używany obszar: " & oCllCursor.AbsoluteName
End Sub

Sumowanie danych zapisanych w zakresie komórek

Przedstawiamy dwie funkcje sumujące dane wpisane do komórek w wybranym zakresie komórek. Pierwsza funkcja wykorzystuje funkcję arkusza SUM(), druga funkcja jawnie sumuje każdą wartość (Value) wpisaną do komórki. Czytelnik łatwo sprawdzi, że obie funkcje zwracają identyczne wyniki.

UWAGA1:

• należy upewnić się, że w sumowanym zakresie są właściwe komórki, bo funkcja SUM() pomija komórki zawierające tekst, ale nie pomija komórek zawierających daty i wartości logiczne

UWAGA2:

• z poziomu arkusza wpis PRAWDA jest interpretowany jako wartość liczbowa 1 i taką też otrzymamy sumę; z poziomu makra wpis True jest interpretowany jako -1 i odpowiednia suma wynosi -1 – zobacz przykładową procedurę Zakresy5(). Nie potrafię wyjaśnić tej różnicy (wersja LibreOffice: 7.0.2.2).

Function mySUM(oRng as Object) as Double
    'dla zadanego zakresu komórek sumuje wartości wpisane do tych komórek
    Dim oFuncAcc as Object
    oFuncAcc = CreateUnoService("com.sun.star.sheet.FunctionAccess")
    mySUM = oFuncAcc.callFunction( "SUM", Array(oRng) )
End Function

Function SumRange(oRng as Object) as Double
    'dla zadanego zakresu komórek sumuje wartości (Value) wpisane do tych komórek
    Dim row as Long, col as Long
    Dim suma as Double
    suma = 0
    For row = 0 To oRng.getRows().getCount() - 1 'odczytujemy liczbę wierszy zakresu oRng
        For col = 0 To oRng.getColumns().getCount() - 1 'odczytujemy liczbę kolumn zakresu oRng
            suma = suma + oRng.getCellByPosition(col, row).Value
        Next col
    Next row
    SumRange = suma
End Function

Sub Zakresy5()
    Dim oSht as Object, oRng as Object
    oSht = ThisComponent.getSheets().getByIndex(0) 'uchwyt do pierwszego arkusza
    ThisComponent.CurrentController.setActiveSheet(oSht) 'aktywacja pierwszego arkusza
    oSht.getColumns().insertByIndex(0, 1) 'dodajemy jedną kolumnę na pozycję pierwszą
    oSht.getCellRangebyName("A1").Value = True ' -1
    oSht.getCellRangebyName("A2").Value = False ' 0
    oRng = oSht.getCellRangeByName("A1:A2")
    oSht.getCellRangebyName("A3").Value = mySUM(oRng)
    oSht.getCellRangebyName("A4").Value = SumRange(oRng)
End Sub

Na zakończenie tej sekcji zauważmy, że zamiast operować na komórkach zadanego zakresu komórek (tak jak to czynimy w funkcji SumRange()) możemy operować na komórkach arkusza. Tak skonstruowana procedura jest mniej elegancka, ale równie skuteczna:

Function SumRange2(oRng as Object) as Double
    'sumuje wartości (Value) z podanego zakresu komórek
    Dim row as Long, col as Long
    Dim suma as Double
    suma = 0
    For row = oRng.getRangeAddress().StartRow To oRng.getRangeAddress().EndRow 'indeksy wierszy arkusza(!)
        For col = oRng.getRangeAddress().StartColumn To oRng.getRangeAddress().EndColumn 'indeksy kolumn arkusza(!)
            suma = suma + oRng.Spreadsheet.getCellByPosition(col, row).Value 'odwołujemy się do komórek arkusza(!)
        Next col
    Next row
    SumRange2 = suma
End Function

Sumowanie danych liczbowych możemy zrealizować wykorzystując metodę getData(). Podamy przykład takiej funkcji, która może być szczególnie przydatna w przypadku, gdy zakres komórek ma ogromne rozmiary:

Function SumRange3(oRng as Object) as Double
    'sumuje wartości (Value) z podanego zakresu komórek
    Dim oVec as Object
    Dim A()
    Dim m as Long, n as Long
    Dim suma as Double
    suma = 0
    oVec = oRng.getData() 'pobrane zostaną jedynie dane liczbowe!
    For m = LBound(oVec) To UBound(oVec) 'przeglądamy tablicę tablic oVec
        A = oVec(m) 'wybrana tablica z tablicy tablic
        For n = LBound(A) To UBound(A)
            suma = suma + A(n)
        Next n
    Next m
    SumRange3 = suma
End Function

Do testowania omówionych wyżej funkcji Czytelnik może wykorzystać procedurę:

Sub Zakresy6()
    Dim oSht as Object, oRng as Object
    oSht = ThisComponent.getSheets.getByIndex(0) 'uchwyt do pierwszego arkusza
    ThisComponent.CurrentController.setActiveSheet(oSht) 'aktywacja pierwszego arkusza
    oSht.getColumns().insertByIndex(0, 1) 'dodajemy jedną kolumnę na pozycję pierwszą
    oSht.getCellByPosition(0, 0).Value = 1
    oSht.getCellByPosition(0, 1).Value = True '-1
    oSht.getCellByPosition(0, 2).Value = DateSerial(2000, 1, 1) '36526
    oSht.getCellByPosition(0, 3).String = "123" '0
    oSht.getCellByPosition(0, 4).Value = Sqr(2) '1,4142...
    oRng = oSht.getCellRangeByPosition(0, 0, 0, 4) 'zakres A1:A5
    oSht.getCellByPosition(0, 6).Value = mySUM(oRng) 'znajdujemy sumę wartości liczbowych z zakresu A1:A5
    oSht.getCellByPosition(0, 7).Value = SumRange(oRng)
    oSht.getCellByPosition(0, 8).Value = SumRange2(oRng)
    oSht.getCellByPosition(0, 9).Value = SumRange3(oRng)
End Sub

LibreOffice Calc – makra                   Strona główna