Jak wstawić puste wiersze (i formuły) do arkusza programu Excel?

Skrypciarze odpowiadają na Wasze pytania

Jak wstawić puste wiersze (i formuły) do arkusza programu Excel?

Cześć, Skrypciarze! Mam trochę bardzo wielkich arkuszy programu Excel, do których muszę wstawić pusty wiersz co 240 wierszy; następnie chcę umieścić w takim wierszu formułę, obliczającą średnią wartość z poprzednich 240 wierszy. Jak się domyślasz, to strasznie żmudna praca, a arkuszy jest naprawdę mnóstwo. Jak napisać skrypt wstawiający te wiersze i formuły?

-- BS

Cześć, BS. Wiesz, wiele osób się zastanawia, co Skrypciarze robią w wolnym czasie. Nie wiem jak inni Skrypciarze, ale ja mam niezwykle fascynujące hobby. Jest nim, rzecz jasna, pisanie skryptów obsługujących program Excel, dziękuję Ci więc za danie mi szansy połączenia pracy zawodowej z prywatnymi zainteresowaniami. Oto gotowy skrypt:

Const xlShiftDown = -4121



Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True



Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")

Set objWorksheet = objWorkbook.Worksheets(1)



i = 1

j = 1



Do Until objWorkSheet.Cells (i, 1) = ""

    If j = 241 Then

        Set objRange = objWorksheet.Cells(i, 1).EntireRow

        objRange.Insert(xlShiftDown)



        strFormula = "=AVERAGE(A" & i - 1 & ":A" & i - 240 & ")"

        objWorksheet.Cells(i, 1) = strFormula

        objWorksheet.Cells(i, 1).Interior.ColorIndex = 44



        j = 0

    End If

    j = j + 1

    i = i + 1

Loop



If j <> 0 Then

    Set objRange = objWorksheet.Cells(i, 1).EntireRow

    objRange.Insert(xlShiftDown)



    strFormula = "=AVERAGE(A" & i - 1 & ":A" & i - j + 1 & ")"

    objWorksheet.Cells(i, 1) = strFormula

    objWorksheet.Cells(i, 1).Interior.ColorIndex = 44

End If

Jak działa skrypt? Szkoda, że to pytanie padło – w końcu moje hobby to pisanie skryptów, a nie ich wyjaśnianie. Trudno; przysiądźmy fałdów i spróbujmy rozwikłać tę zagadkę.

Jak widać, zaczynamy od zdefiniowania stałej o nazwie xlShiftDown, której wartość ustawiamy na -4121; użyjemy jej do poinstruowania programu Excel, w którą stronę przesunąć istniejące komórki po wstawieniu nowego wiersza.

Po zdefiniowaniu stałej, tworzymy wystąpienie obiektu Excel.Application, którego właściwość Visible ustawiamy na True; to daje nam uruchomione i widoczne na ekranie wystąpienie programu Excel. Następnie za pomocą poniższych dwu wierszy kodu otwieramy plik C:\Scripts\Test.xls i łączymy się z pierwszym znajdującym się w nim arkuszem:

Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")

Set objWorksheet = objWorkbook.Worksheets(1)

Teraz przypisujemy wartość 1 do pary zmiennych licznika - i oraz j. Zmienna i posłuży nam do śledzenia bieżącej lokalizacji w arkuszu, a j – do śledzenia miejsc, gdzie należy wstawić wiersz.

Dla celów edukacyjnych zakładamy, że nasz arkusz jest bardzo prosty i zawiera liczby w kolumnie A oraz żadnych pustych wierszy. Uruchamiamy zatem pętlę Do Until, która będzie działać, dopóki nie napotkamy w kolumnie A pustej komórki; jeśli tak się stanie, zakładamy, że doszliśmy do końca arkusza. Jak mówiliśmy, za pomocą zmiennej licznika i śledzimy naszą bieżącą lokalizację:

Do Until objWorkSheet.Cells (i, 1) = ""

Ponieważ zmienna i ma wartość 1, oznacza to, że przy pierwszym przejściu pętli będziemy sprawdzać wartość komórki w wierszu 1 i kolumnie 1. Dochodzimy do następującego wiersza kodu:

If j = 241 Then

Co nam to daje? No cóż, BS chce wstawić nowy wiersz co 240 wierszy. Za pomocą zmiennej licznika j śledzimy numer wiersza, przechodząc w dół arkusza. Jeśli numer ten jest inny niż 241, oznacza to, że jeszcze nie przeszliśmy 240 wierszy. Dlatego też po prostu pomijamy fragment If-Then, zwiększamy wartości obu zmiennych licznika o 1 i powtarzamy proces z kolejnym wierszem w arkuszu.

Załóżmy jednak, że zmienna j ma wartość 241. W takim wypadku wiemy, że właśnie minęliśmy wiersz numer 240; możemy więc wstawić pusty wiersz:

Set objRange = objWorksheet.Cells(i, 1).EntireRow

objRange.Insert(xlShiftDown)

Tworzymy tu wystąpienie obiektu Range programu Excel, który obejmuje bieżący wiersz (skąd wiemy, że właśnie bieżący wiersz? otóż właściwość objWorksheet.Cells(i, 1) oznacza bieżącą komórkę w kolumnie A, natomiast właściwość EntireRow oznacza wszystkie pozostałe komórki w tym wierszu.) Następnie wywołujemy metodę Insert, za której pomocą wstawiamy nowy wiersz, używając stałej xlShiftDown, by poinstruować skrypt, że chcemy wstawić nowy wiersz i przesunąć resztę wierszy w dół.

Gdybyśmy chcieli tylko wstawić puste wiersze, moglibyśmy już zakończyć i zająć się tym, co lubimy najbardziej. Mam tu nawet skrypt, który na tym poprzestaje:

Const xlShiftDown = -4121



Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True



Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")

Set objWorksheet = objWorkbook.Worksheets(1)



i = 1

j = 1



Do Until objWorkSheet.Cells (i, 1) = ""

    If j = 241 Then

        Set objRange = objWorksheet.Cells(i, 1).EntireRow

        objRange.Insert(xlShiftDown)

        j = 0

    End If

    j = j + 1

    i = i + 1

Loop

Nasze ambicje sięgają jednak dalej; chcemy wstawić tam jeszcze formułę obliczającą średnią wartość. Robimy to w ten sposób:

strFormula = "=AVERAGE(A" & i - 1 & ":A" & i - 240 & ")"

objWorksheet.Cells(i, 1) = strFormula

Wstawiając formułę do komórki, należy użyć składni takiej jak poniżej:

=AVERAGE(A240:A1)

Może trudno w to uwierzyć, ale to wystarczy, aby utworzyć naszą formułę. Łączymy trzy elementy:

• Wartość ciągu =AVERAGE(A.
• Numer bieżącego wiersza minus 1 (i – 1). Skąd się wzięło „i minus 1”? No cóż, jesteśmy teraz w wierszu 241, a chcemy, by formuła obliczyła średnią z wierszy od 1 do 240. Musimy więc odjąć 1 od bieżącego numeru wiersza. Jeśli tego nie zrobimy, będziemy mieć całe mnóstwo błędów odwołania cyklicznego. Co to takiego? Nie wiem, bo nigdy się na nie tak naprawdę nie natknąłem. Zawsze zapobiegam ich powstaniu. Jeśli ktoś mówi, że tak nie jest, to rozsiewa złowrogie plotki. Serio.
• Wartość ciągu :A
• Początkowy wiersz zestawu 240 wierszy. Jak to obliczyć? Odejmując 240 od obecnej wartości zmiennej licznika i. Jeśli odejmiemy 240 od 241, uzyskamy 1. I bardzo dobrze, bo to właśnie pierwszy wiersz, który nas interesuje.
• Wartość ciągu ).

Po ich dodaniu uzyskujemy fragment:

=AVERAGE(A240:A1)

Nie da się ukryć, że komórki są tam podane od tyłu. Ja też wolałbym zacząć od A1, a skończyć na A240, o tak:

=AVERAGE(A1:A240)

Prawdę mówiąc, takiej kolejności też można użyć. Po prostu chciałem się popisać.

Po wpisaniu formuły do bieżącej komórki, zmieniamy wartość zmiennej j na 0. Dlaczego? Ponieważ zaczynamy teraz odliczanie kolejnego zestawu 240 komórek. Zwiększamy więc wartości obu zmienny o 1, po czym uruchamiamy ponownie pętlę i przechodzimy do kolejnego wiersza w arkuszu:

j = j + 1

i = i + 1

Tak się składa, że można bez większego trudu zmodyfikować skrypt tak, aby wstawiał puste wiersze nie co 240, ale np. co 25 wierszy. W tym celu zmieniamy instrukcję If Then w taki sposób, aby sprawdzać, czy zmienna j ma wartość 26 (o jeden więcej niż pożądana częstotliwość wstawiania wierszy):

If j = 26 Then

Następnie odejmujemy wartość odpowiadającą pożądanej częstotliwości (25) podczas składania naszej formuły:

strFormula = "=AVERAGE(A" & i - 1 & ":A" & i - 25 & ")"

Proste, nie?

Aha, jeszcze jedno:

objWorksheet.Cells(i, 1).Interior.ColorIndex = 44

Ten wiersz kodu jest opcjonalny, ale ułatwia odróżnienie komórek z formułą od pozostałych. Zmieniamy tu ich kolor tła na pomarańczowy.

Po zamknięciu pętli sprawdzamy, czy zmienna j jest równa 0. Jeśli tak jest, oznacza to, że obliczyliśmy średnią wszystkich wierszy z poprzedniej serii. Jeśli j jest różna od 0 (np. ma wartość 113), mamy jeszcze parę wierszy, których średniej nie obliczyliśmy. Zajmie się tym poniższy fragment kodu:

Set objRange = objWorksheet.Cells(i, 1).EntireRow

objRange.Insert(xlShiftDown)



strFormula = "=AVERAGE(A" & i - 1 & ":A" & i - j + 1 & ")"

objWorksheet.Cells(i, 1) = strFormula

objWorksheet.Cells(i, 1).Interior.ColorIndex = 44

I już.

Do początku strony