Jak uporządkować arkusz kalkulacyjny pod względem koloru komórki?

Skrypciarze odpowiadają na Wasze pytania

Witamy w rubryce TechNet, w której Skrypciarze z firmy Microsoft odpowiadają na częste pytania dotyczące używania skryptów w administracji systemu. Jeśli macie jakieś pytania z tej dziedziny, zachęcamy do wysłania e-maila na adres: scripter@microsoft.com. Nie możemy zagwarantować odpowiedzi na każde otrzymane pytanie, ale staramy się jak możemy.

Jak uporządkować arkusz kalkulacyjny pod względem koloru komórki?

Cześć, Skrypciarze! Mam arkusz kalkulacyjny programu Excel, w którym używam różnych kolorów jako sposobu na śledzenie informacji o stanie. Sprawdza się to całkiem dobrze, z wyjątkiem jednej sytuacji: czasem chcę uporządkować arkusz kalkulacyjny pod względem kolorów. O ile wiem, program Excel nie ma funkcji sortowania arkusza na podstawie koloru. Czy można to zrobić za pomocą skryptu?

-- GD

Cześć, GD. Czy można to zrobić za pomocą skryptu? Oczywiście, że można. Za pomocą skryptów można zrobić wszystko. Pamiętacie historię Ernesta Shackletona? W 1915 roku, kiedy jego statek (o nazwie Endurance – wytrwałość) utknął w lodowcu, Shackleton i jego załoga zostali uwięzieni na zmarzniętym pustkowiu Antarktyki. Jak udało im się wyrwać się ze szponów nieuchronnej zagłady? Właśnie: Shackleton napisał skrypt informujący ekipę ratunkową o ich lokalizacji i w ten sposób uratował całą swoją załogę.

Oczywiście dwie rzeczy odróżniają nas od Ernesta Shackletona. Po pierwsze rzadko kiedy musimy przemierzać ocean w łodzi ratunkowej (chociaż wiele osób w firmie Microsoft uważa, że Skrypciarze powinni spróbować). Po drugie, za każdym razem, gdy nam coś nie wychodzi (tak, za każdym razem), możemy rozwiązać wszystkie nasze problemy (tak, wszystkie) za pomocą skryptu:

Const xlAscending = 1

Const xlYes = 1



Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True



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

Set objWorksheet = objWorkbook.Worksheets("Sheet1")



i = 2



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

    intColor = objExcel.Cells(i,2).Interior.ColorIndex 

    Select Case intColor

        Case 3 intSortOrder = 4

        Case 4 intSortOrder = 1

        Case 6 intSortOrder = 2

        Case 41 intSortOrder = 3

    End Select

    objExcel.Cells(i, 3) = intSortOrder

    i = i + 1

Loop



Set objRange = objWorksheet.UsedRange

Set objRange2 = objExcel.Range("C2")

objRange.Sort objRange2, xlAscending, , , , , , xlYes



Set objRange = objRange2.EntireColumn

objRange.Clear

Zanim przejdę do szczegółów dotyczących skryptu i jego działania, trochę informacji wstępnych. Zakładamy, że GD ma arkusz kalkulacyjny wyglądający mniej więcej tak:

Skoro tak uważacie, tak, całkiem ładny. Ale to nie jest istotne. Ważne jest to, że GD chce uporządkować arkusz tak, aby wyglądał następująco:

Jak już wiemy, program Excel nie ma wbudowanej metody sortowania arkusza kalkulacyjnego na podstawie kolorów. Tak samo jest ze skryptami: tu także nie ma bezpośredniej metody zastosowania skryptu do sortowania komórek według kolorów. Co z tym zrobimy? To samo, co zrobił Ernest Shackleton: będziemy ściemniać.

Jak widzimy, nasz arkusz kalkulacyjny zawiera listę nazwisk w kolumnie A oraz kilka kolorów w kolumnie B. Mamy zamiar określić, który kolor pojawia się w kolumnie B, a następnie zapisać wartość liczby całkowitej reprezentującej ten kolor w kolumnie C. Po wykonaniu tej czynności posortujemy arkusz według tych liczb całkowitych, a następnie usuniemy zawartość kolumny C. Co otrzymamy? Wszystkie kolory zostaną zgrupowane razem zupełnie tak, jak gdybyśmy wykonali sortowanie według koloru. (Co przecież zrobiliśmy, tylko trochę okrężną drogą.)

Nie martwcie się. To wszystko nabierze sensu, jak tylko omówimy kod. Zaczynamy od zdefiniowania dwóch stałych, stałej xlAscending (którą zastosujemy do sortowania arkusza w porządku rosnącym) oraz stałą xlYes (którą zastosujemy do poinformowania skryptu, że nasz arkusz ma wiersz nagłówka). Po zdefiniowaniu stałych tworzymy wystąpienie obiektu Excel.Application , a następnie ustawiamy wartość właściwości Visible jako True; w ten sposób otrzymujemy działające wystąpienie programu Excel widoczne na ekranie. Jak tylko to zrobimy, używamy następujących dwóch wierszy kodu w celu otwarcia arkusza kalkulacyjnego C:\Scripts\Test.xls i połączenia się z pierwszym arkuszem w tym pliku:

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

Set objWorksheet = objWorkbook.Worksheets("Sheet1")

Teraz przechodzimy do następującego wiersza kodu:

i = 2

Następnie sprawdzimy wartość kolumny A w każdym wierszu arkusza (lub dokładniej, w każdym wierszu arkusza, który zawiera wartość w kolumnie A). Zastosujemy zmienną licznika i w celu śledzenia bieżącego wiersza w arkuszu. Ponieważ nasze dane zaczynają się dopiero od wiersza 2 (wiersz 1 jest wierszem nagłówka), ustawiamy wartość początkową zmiennej licznika jako 2.

Jak więc sprawdzimy wartość kolumny A w każdym wierszu arkusza (lub dokładniej, w każdym wierszu arkusza, który zawiera wartość w kolumnie A)? To proste. uruchomimy pętlę Do podobną do poniższej:

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

Jak widzimy, zaczynamy od wartości komórki A2 (wiersz 1, kolumna 1). Jeżeli komórka A2 jest pusta, kończymy działanie pętli. Jeżeli komórka A2 nie jest pusta, wykonujemy następujący fragment kodu:

intColor = objExcel.Cells(i,2).Interior.ColorIndex 

Select Case intColor

    Case 3 intSortOrder = 4

    Case 4 intSortOrder = 1

    Case 6 intSortOrder = 2

    Case 41 intSortOrder = 3

End Select

objExcel.Cells(i, 3) = intSortOrder

i = i + 1

W pierwszym wierszu przypisujemy wartość komórki B2 (wiersz 1, kolumna 2) do zmiennej o nazwie intColor. Robimy to pobierając wartość właściwości Interior.ColorIndex komórki. Kolory tła są zachowane jako wartości liczb całkowitych. W naszym przypadku mamy następujące wartości:

Naszym następnym posunięciem jest uruchomienie polecenia Select Case, które zadziała na wartości zmiennej intColor. Jakie to będzie działanie? Przypiszemy porządek sortowania do każdego wiersza na podstawie koloru tła komórki w kolumnie B. Chcemy uporządkować kolory w następującej kolejności:

• Zielony
• Żółty
• Niebieski
• Czerwony

Co to oznacza? Cóż, żółty jest drugim kolorem w porządku sortowania. Załóżmy, że okazuje się, że komórka B2 jest żółta (a jest.) Żółty to kolor nr 2 w porządku sortowania, więc ustawiamy wartość komórki C2 (wiersz 2, kolumna 3) jako 2:

objExcel.Cells(i, 3) = intSortOrder

Ale jak zmienna intSortOrder została ustawiona jako 2? Do tego posłużył nam blok Select Case:

Case 6 intSortOrder = 2

Jak już zauważyłem, kolor żółty ma wartość liczby całkowitej 6. Za pomocą poprzedniego wiersza kodu sprawdzamy, czy kolor tła komórki B2 ma wartość 6. Jeżeli tak jest, przypisujemy wartość 2 (porządek sortowania nr 2) do zmiennej intSortOrder.

Po zwiększeniu wartości zmiennej licznika i o 1 wracamy na początek pętli i powtarzamy proces dla następnego wiersza w arkuszu kalkulacyjnym. Po wykonaniu wszystkich czynności nasz arkusz kalkulacyjny powinien wyglądać następująco:

Czas na posortowanie arkusza według „koloru” (czyli według wartości w kolumnie C). Do tego posłużą nam poniższe trzy wiersze kodu:

Set objRange = objWorksheet.UsedRange

Set objRange2 = objExcel.Range("C2")

objRange.Sort objRange2, xlAscending, , , , , , xlYes

W wierszu pierwszym tworzymy wystąpienie obiektu Range programu Excel, które obejmuje zakres komórek w arkuszu zawierających dane (do tego potrzebna jest nam właściwość UsedRange). W wierszu 2 tworzymy drugi obiekt Range, składający się z jednej komórki C2.

Na koniec w wierszu 3 sortujemy arkusz, używając odniesienia do obiektu do kolumny C (objRange2) jako kolumny sortowania i informujemy program Excel o tym, że: 1) chcemy wykonać sortowanie w porządku rosnącym (xlAscending) oraz 2) mamy wiersz nagłówka (xlYes).

Teraz musimy tylko usunąć wartości, które dodaliśmy do kolumny C. Jak to zrobimy? Cóż, najprostszym sposobem jest utworzenie obiektu Range obejmującego wszystkie komórki w kolumnie C. Ponieważ mamy już obiekt Range wskazujący na komórkę C2, możemy zastosować właściwość EntireColumn w celu pobrania pozostałych komórek z tej kolumny:

Set objRange = objRange2.EntireColumn

Następnie wywołujemy metodę Clear i usuwamy wszystkie wartości w kolumnie C:

objRange.Clear

Wynik? Cóż, sami spróbujcie i zobaczycie, co otrzymacie.

Dokładnie to, co chcieliśmy, GD. Czy to jest tak samo imponujące, jak przemierzenie 1300 km zdradzieckiego oceanu w łodzi ratunkowej? Pewnie nie. Ale jest bardzo blisko.

P.S. Powinienem dodać, że Skrypciarze czują głęboki podziw dla dokonań Ernesta Shackletona. Bardzo imponujące, delikatnie mówiąc. Czujemy także pewną więź z Shackletonem lub przynajmniej z ludźmi, którzy zdecydowali się wyruszyć na jego wyprawę. Wieść niesie, że załoga została skompletowana z ludzi, którzy odpowiedzieli na następujące ogłoszenie:

O dziwo, na takie samo ogłoszenie odpowiedzieli Skrypciarze zatrudniając się w firmie Microsoft.

Do początku strony