Jak zastosować pusty wiersz do oddzielenia danych w arkuszach kalkulacyjnych programu Excel?

Skrypciarze odpowiadają na Wasze pytania

Jak zastosować pusty wiersz do oddzielenia danych w arkuszach kalkulacyjnych programu Excel?

Cześć, Skrypciarze! Jak zastosować pusty wiersz do oddzielenia danych w arkuszach kalkulacyjnych programu Excel?

-- AR

Cześć, AR. Panuje taka opinia, że wszyscy Skrypciarze myślą w podobny sposób i że zawsze się we wszystkim ze sobą zgadzają. Nie jest to do końca prawdą, jak w każdym zespole, wśród Skrypciarzy także pojawiają się różnice zdań. Weźmy na przykład takie proste pytanie: Jaki jest najlepszy sport na świecie. Odpowiedzi pada mnóstwo: piłka nożna, hokej, skoki narciarskie, koszykówka, itp. Te odpowiedzi się często powtarzają, ale jest tylko jeden Skrypciarz, który uparcie twierdzi (i nikt nie jest w stanie go przekonać, iż jest inaczej), że najlepszym sportem na świecie jest minigolf. Zapewne wynika to z faktu, że jednym z dwóch jego życiowych osiągnięć jest puchar w rozgrywkach minigolfa. Drugim życiowym osiągnięciem tego Skrypciarza jest stworzenie skryptu, dzięki któremu można oddzielać wiesze danych w arkuszach kalkulacyjnych programu Excel za pomocą pustego wiersza:

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



strStartValue = Left(objExcel.Cells(i, 1), 1)



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

    strValue = Left(objExcel.Cells(i, 1), 1)

    If strValue <> strStartValue Then

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

        objRange.Activate

        objRange.Insert xlShiftDown

        strStartValue = Left(objExcel.Cells(i + 1, 1), 1)

    End If

    i = i + 1

Loop

Zanim jednak zaczniemy omawiać ten skrypt, wyjaśnijmy jak wygląda arkusz kalkulacyjny, na którym będziemy pracować. A wygląda on następująco:

Jak widać, w arkuszu mamy pięciu różnych użytkowników (wymienionych w kolumnie A):

• A Person
• B Person
• C Person
• D Person
• E Person

Ten arkusz zawiera co najmniej jeden wiersz z danymi dla każdego z tych użytkowników, na przykład dane użytkownika „A Person” są umieszczone w pierwszych trzech wierszach. Teraz chcemy wstawić pusty wiersz pomiędzy dane użytkownika „A Person” a dane użytkownika „B Person”, dzięki czemu otrzymamy arkusz wyglądający tak:

Ale jak mamy to zrobić? Oto jest pytanie.

Po pierwsze, tworzymy stałą o nazwie xlShiftDown i nadajemy jej wartość -4121; zastosujemy ją, aby wydać polecenie przesunięcia komórek w dół za każdym razem, kiedy wstawimy nowy, pusty wiersz. (Gdybyśmy pracowali z kolumnami, moglibyśmy zastosować stałą o wartości -4161, aby przesuwać komórki w prawo.) Następnie używamy poniższego fragmentu kodu, aby utworzyć działające wystąpienie programu Excel, które możemy wyświetlić na ekranie i otwieramy plik C:\Scripts\Test.xls:

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

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

Set objWorksheet = objWorkbook.Worksheets(1)

Teraz będzie zabawnie. Na początek, przypisujemy wartość 1 zmiennej licznika o nazwie „i”; następnie zastosujemy tę zmienną do śledzenia naszego położenia w arkuszu. Teraz, za pomocą poniższego wiersza kodu, uzyskujemy pierwszy znak z komórki 1,1 i zachowujemy go w zmiennej o nazwie strStartValue:

strStartValue = Left(objExcel.Cells(i, 1), 1)

Rzeczą, która wymaga trochę wysiłku intelektualnego, jest określenie miejsca, gdzie należy wstawić pusty wiersz; chodzi nam przecież o to, aby wstawić go pomiędzy dane jednego użytkownika, a dane innego użytkownika. (Oczywiście, nie trzeba dodawać, że skrypt nie będzie działał prawidłowo, jeżeli użytkownicy w arkuszu nie będą uporządkowani według nazwy. Informacje dotyczące programistycznego sposobu sortowania tego typu danych zawarte są w niniejszym artykule Office Space (j.ang.).) Jak więc mamy rozpoznać, kiedy kończą się dane naszego użytkownika „A Person”? No cóż, zachowamy dane użytkownika z pierwszego wiersza w zmiennej strStartValue, a następnie przejdziemy do kolejnego wiersza i sprawdzimy, czy dane z tego wiersza do nich pasują. Jeżeli tak, będzie dla nas jasne, że nadal mamy do czynienia z użytkownikiem „A Person”. Jeżeli nie, będzie to oznaczać, że dane należą do następnego użytkownika.

Nie martwmy się, zrozumiemy, o co w tym dokładnie chodzi, kiedy już omówimy skrypt.

Po pierwsze, tworzymy pętlę Do, która będzie działać dopóki nie dojdzie do pustej komórki w kolumnie A:

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

Wewnątrz tej pętli, pobieramy początkowy znak z bieżącego wiersza i zachowujemy go w zmiennej o nazwie strValue:

If strValue <> strStartValue Then

Przy pierwszym przejściu pętli wartości nie są różne; obie zmienne – strStartValue oraz strValue są równe A. Dlatego też opuszczamy zdanie If Then, podwyższamy wartość i o 1, następnie uruchamiamy pętlę i powtarzamy cały proces dla drugiego wiersza w arkuszu kalkulacyjnym. Ważne jest, że nie zmieniliśmy wartości zmiennej strStartValue. W tej zmiennej nadal jest zachowana litera A oraz pierwszy znak z komórki A1. Ponieważ jednak podwyższyliśmy wartość i o 1, zmienna strValue będzie teraz równa pierwszemu znakowi z komórki A2.

Prawda, że nie jest to takie skomplikowane? Przejdźmy zatem dalej.

Prędzej czy później, odnajdziemy rozbieżność pomiędzy zmienną strStartValue a zmienną strValue; zauważmy, że pierwszy znak w komórce A4 to B. Co zatem się dzieje, kiedy wartości zmiennych strStartValue oraz strValue się nie zgadzają? Dzieje się tak:

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

objRange.Activate

objRange.Insert xlShiftDown

strStartValue = Left(objExcel.Cells(i + 1, 1), 1)

Za pomocą pierwszego wiersza kodu tworzymy obiekt Range:, który obejmuje cały wiersz 4, poprzez określenie komórki A4 (objExcel.Cells(i, 1), a potem wywołujemy właściwość EntireRow. Następnie stosujemy metodę Activate w celu uaktywnienia całego wiersza w arkuszu kalkulacyjnym:

objRange.Activate

W zasadzie to przesunęliśmy kursor w dół do komórki A4, po czym zaznaczyliśmy cały wiersz. Po zaznaczeniu wiersza, stosujemy metodę Insert, aby wstawić pusty wiersz, używając parametru xlShiftDown do przesunięcia istniejących komórek w dół. Cóż to oznacza? Oznacza to, że dane znajdujące się obecnie w wierszu 4 zostaną przesunięte do wiersza 5, a wiersz 4 będzie teraz wierszem pustym.

Właśnie to chcieliśmy osiągnąć.

Następnie używamy poniższego wiersza kodu w celu pobrania pierwszego znaku z wiersza 5 (i + 1) i zachowujemy te dane w zmiennej strStartValue:

strStartValue = Left(objExcel.Cells(i + 1, 1), 1)

Dzięki temu zmienna strStartValue jest równa B.

Następnie podwyższamy wartość i o 1, uruchamiamy pętlę i powtarzamy cały proces od początku. To trochę skomplikowane, ale działa i na pewno sobie z tym poradzicie.

Do początku strony