Jak przypisać kolor tła komórkom arkusza, a następnie zsumować te komórki?

Skrypciarze odpowiadają na Wasze pytania

Jak przypisać kolor tła komórkom arkusza, a następnie zsumować te komórki?

Cześć, Skrypciarze! W jaki sposób przypisać kolor tła (odpowiadający podanej wartości) komórkom w arkuszu, a następnie zsumować te komórki?

-- JC

Cześć, JC. Dzięki za pytanie. To bardzo dobre pytanie i Skrypciarz piszący te słowa cieszy się, że ono padło – w końcu dobrze jest raz na jakiś czas umieć odpowiedzieć na zadane pytanie:

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True



Set objWorkbook = objExcel.Workbooks.Add()

Set objWorksheet = objWorkbook.Worksheets(1)



objExcel.Cells(1, 1).Value = "A"

objExcel.Cells(2, 1).Value = "B"

objExcel.Cells(3, 1).Value = "C"

objExcel.Cells(4, 1).Value = "D"

objExcel.Cells(5, 1).Value = "E"

objExcel.Cells(6, 1).Value = "F"

objExcel.Cells(7, 1).Value = "G"

objExcel.Cells(8, 1).Value = "H"



objExcel.Cells(1, 1).Interior.ColorIndex = 7

objExcel.Cells(2, 1).Interior.ColorIndex = 8

objExcel.Cells(3, 1).Interior.ColorIndex = 9

objExcel.Cells(4, 1).Interior.ColorIndex = 10

objExcel.Cells(5, 1).Interior.ColorIndex = 7

objExcel.Cells(6, 1).Interior.ColorIndex = 7

objExcel.Cells(7, 1).Interior.ColorIndex = 8

objExcel.Cells(8, 1).Interior.ColorIndex = 10



i = 1



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

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



    Select Case intColor

        Case 7 intSum = intSum + 5

        Case 8 intSum = intSum + 10

        Case 9 intSum = intSum + 15

        Case 10 intSum = intSum + 20

    End Select



    i = i + 1

Loop



Wscript.Echo intSum

W kilku miejscach pozwoliliśmy sobie na pewną dowolność. Opisany przez JC skrypt wpisywał określoną wartość w wybrane komórki, a następnie zmieniał ich kolor tła w zależności od tego, jaka wartość została wpisana. Nie tworzyliśmy tak wyrafinowanego systemu – po prostu wpisaliśmy jakieś dane do komórek i w arbitralny sposób zmieniliśmy ich kolory. Ale nie martwcie się – zmiana koloru tła komórki jest bardzo prosta, podobnie jak „odczytywanie” tych kolorów, więc nie będzie problemu z dostosowaniem skryptu do własnych potrzeb.

Zaczynamy – co za zaskoczenie – od utworzenia wystąpienia obiektu Excel.Application i ustawienia jego właściwości Visible na True; uzyskujemy w ten sposób uruchomione i widoczne na ekranie wystąpienie programu Excel. Potem używamy poniższych dwóch wierszy kodu do utworzenia nowego zeszytu i połączenia się z jego pierwszym arkuszem:

Set objWorkbook = objExcel.Workbooks.Add()

Set objWorksheet = objWorkbook.Worksheets(1)

Następnie mamy kilka wierszy kodu, w których wstawiamy wartości do komórek od A1 do A8. Poniższy wiersz na przykład wpisuje w komórkę A1 (wiersz 1, kolumna 1) literę A:

objExcel.Cells(1, 1).Value = "A"

Po wpisaniu wartości, możemy użyć kodu takiego jak poniżej (to ponownie arbitralny wybór), by zmienić kolor tła komórek:

objExcel.Cells(1, 1).Interior.ColorIndex = 7

Jak widać, zmiana koloru jest bardzo prosta – wystarczy przypisać liczbę odpowiadającą pożądanej barwie do właściwości Interior.ColorIndex. Jedyny problem w tym, że trzeba wiedzieć, jakie liczby odpowiadają jakim kolorom. Na szczęście wszystko to można znaleźć w odpowiednim artykule Office Space (j.ang.).

Co zatem uzyskujemy? Otóż uzyskujemy arkusz wyglądający tak:

Teraz chcemy zsumować wartości poszczególnych komórek. Nie zrobimy tego przez dodawanie – A plus B plus C itd. nie ma większego sensu. Sprawdzimy więc kolor tła każdej z komórek, każdemu kolorowi przypiszemy jakąś wartość, a następnie zsumujemy te wartości. Nie martwcie się, zaraz wyjaśnimy dokładniej, o co chodzi.

Zanim zaczniemy odczytywać wartości poszczególnych komórek, zastosujemy wiersz kodu przypisujący wartość 1 zmiennej licznika o nazwie i. Użyjemy jej do śledzenia bieżącej pozycji (wiersza) w arkuszu. Następnie uruchamiamy pętlę Do, która będzie działać, dopóki nie dojdzie do pustej komórki w kolumnie A – zakładamy, że to będzie koniec zawartości arkusza:

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

Działanie wewnątrz pętli zaczynamy od określenia koloru tła komórki i przypisania liczby całkowitej do zmiennej o nazwie intColor:

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

Znając kolor tła komórki, możemy się pobawić w matematykę:

Select Case intColor

    Case 7 intSum = intSum + 5

    Case 8 intSum = intSum + 10

    Case 9 intSum = intSum + 15

    Case 10 intSum = intSum + 20

End Select

Uruchomiliśmy tutaj polecenie Select Case oparte na wartości intColor. Ponadto przypisaliśmy wartości numeryczne poszczególnym kolorom użytym w arkuszu. I tak, kolor 7 (coś na kształt różu zmieszanego z fioletem) otrzymał wartość 5; kolor 8 (to się chyba nazywa cyjan) ma wartość 10 itd. W poleceniu Select Case zwiększamy wartość kolejnej zmiennej (intSum). O ile? No cóż, to zależy od koloru komórki. Weźmy np. komórkę różowofioletową. Jej wartość intColor będzie równa 7, a skrypt wykona poniższy wiersz kodu, zwiększając wartość intSum o 5:

Case 7 intSum = intSum + 5

Znowu nie jest to szczególnie skomplikowane. Jeśli wartość intColor jest równa 7, przypisujemy nową wartość zmiennej intSum, równą dotychczasowej wartości plus 5. Przy pierwszym przejściu pętli, wartość intSum jest równa 0; dlatego też, kiedy napotkamy różowofioletową komórkę w pierwszym wierszu pierwszej komórki, równanie będzie wyglądać tak: 0 + 5 = 5. W wyniku tego, wartość intSum będzie równa 5.

Po zakończeniu działania polecenia Select Case, zwiększamy wartość licznika o 1, a następnie powtarzamy proces dla drugiego wiersza pierwszej kolumny. Ostateczny wynik będzie wyglądać tak:

90

Mamy nadzieję, że w ten sposób masz kolejny problem z głowy, JC.

Do początku strony