HenrykDabrowski o 11:44, 27 paź 2020

2020-10-27T11:44:28Z

Nowa strona

<div style="text-align:right; font-size: 130%; font-style: italic; font-weight: bold;">22.10.2020</div>

__FORCETOC__

== 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 ([https://api.libreoffice.org/docs/idl/ref/servicecom_1_1sun_1_1star_1_1table_1_1CellCursor.html 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() ([https://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/XUsedAreaCursor.html 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<ref name="p1"/> 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 | LibreOffice Calc – makra]]                  [[Henryk Dąbrowski | Strona główna]]

== Przypisy ==
<references>

<ref name="p1">Czytelnik powinien pamiętać, że sumowane będą również dane typu data i wartości logiczne!</ref>

</references>

← poprzednia wersja		Wersja z 16:26, 24 maj 2024
Linia 195:		Linia 195:


	[[LibreOffice Calc – makra \| <b>LibreOffice Calc – makra</b>]]                  [[Henryk Dąbrowski \| <b>Strona główna</b>]]		[[LibreOffice Calc – makra – przykłady \| <b>LibreOffice Calc – makra – przykłady</b>]]                  [[Henryk Dąbrowski \| <b>Strona główna</b>]]

Funkcje zakresu komórek - Historia wersji

HenrykDabrowski o 14:26, 24 maj 2024

HenrykDabrowski: 1 wersja

HenrykDabrowski o 11:44, 27 paź 2020

← poprzednia wersja	Wersja z 22:36, 18 wrz 2022
(Brak różnic)