Jak zastąpić wartości numeryczne w arkuszu kalkulacyjnym programu Excel?

Skrypciarze odpowiadają na Wasze pytania

Jak zastąpić wartości numeryczne w arkuszu kalkulacyjnym programu Excel?

Cześć, Skrypciarze! Mam arkusz kalkulacyjny, do którego różni ludzie wprowadzają dane. W celu zapewnienia, że te dane będą mogły zostać wykorzystane w innej aplikacji, żadna z wartości nie może przekroczyć 999; jeżeli mamy wartość, np. 1234, powinna ona zostać wprowadzona jako 999, czyli maksymalna dozwolona wartość. Jednakże ludzie mają tendencje do wpisywania wartości jako 1234, co oznacza, że nie możemy importować arkusza kalkulacyjnego do innej aplikacji, dopóki nie znajdziemy wszystkich niedozwolonych wartości i nie zamienimy ich na 999. Jak napisać skrypt, który zrobi to za mnie?

-- LK

Cześć, LK. Wiesz, oprócz złożenia przysięgi skromnego życia, złożyłem także przysięgę obligującą mnie do tego, że będę pomagał każdemu, kto mnie o to poprosi. (No dobra, technicznie rzecz biorąc nie składałem przysięgi skromnego życia, ale zostało mi ono niejako narzucone. Tak się dzieje, jak się ma 17-letniego syna, zwłaszcza takiego, który gra w koszykówkę.) Z powodu tej drugiej przysięgi, jestem zobowiązany, żeby odpowiedzieć na zadane pytanie. Boję się tylko, że będę musiał pomóc każdemu, kto przyjdzie do mnie z prośbą, żebym coś „za niego zrobił”. Zrobić coś za kogoś. Oczywiście nie wiedzieliście ostatnio mojego domu, prawda?

W każdym razie robienie czegoś za kogoś to nie jest to, co ja robię najlepiej. Ale to przecież nie moja wina. Może chodzi o to, że tworzenie skryptu zmieniającego wartości komórek większych niż 999 na maksymalną dozwoloną wartość 999 wymaga dużo mniej nakładu pracy niż odsłonięcie mojego domu:

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True



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

Set objWorksheet = objWorkbook.Worksheets(1)



Set objRange = objWorksheet.UsedRange



For Each objCell in objRange

    If IsNumeric(objCell.Value) Then

        If objCell.Value > 999 Then

            objCell.Value = 999

        End If

    End If

Next

Jak widać, naprawdę nie jest to skomplikowany skrypt. Jak się okazuje, zaskakująco proste jest przejście przez arkusz kalkulacyjny, odnalezienie wartości większych niż 999 i ich zamiana na 999. Na początek, tworzymy wystąpienie obiektu Excel.Application i nadajemy właściwości Visible wartość True; to nam da działające wystąpienie programu Excel widoczne na ekranie. Następnie stosujemy poniższe dwa wiersze kodu w celu otwarcia pliku C:\Scripts\Test.xls i połączenia się z pierwszym arkuszem tego pliku:

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

Set objWorksheet = objWorkbook.Worksheets(1)

Jak widać, w żadnej z tych czynności nie ma nic skomplikowanego.

Wiem, co sobie teraz myślicie: „Pewnie, nie ma w tym nic skomplikowanego. Ale przecież musimy jakoś przeprowadzić tę operację wyszukaj-i-zastąp, dzięki której znajdziemy, a następnie zastąpimy wszystkie wartości większe niż 999. Z tym może już być problem.”

Prawdę mówiąc, takie rozwiązanie mogłoby być trochę problematyczne, ale trudno powiedzieć, bo go nie zastosujemy. Nie wykonamy typowej operacji wyszukiwania i zastępowania (czyli nie zastosujemy metody Replace programu Excel). Być może moglibyśmy zastosować tę metodę jednak wybraliśmy o wiele łatwiejsze podejście: po prostu pobierzemy kolekcję wszystkich komórek w arkuszu, sprawdzimy wartość każdej z nich osobno, a następnie w razie potrzeby zastąpimy tę wartość przez 999. Jak sami zobaczycie, nie ma w tym nic strasznie skomplikowanego. Po co to marudzenie…

Że niby co? Nie wierzycie mi? Dalej sądzicie, że wyszukanie i zastąpienie będzie trudne? Pozwólcie mi rozwiać wszelkie wątpliwości – przedstawię dokładne wyjaśnienia dotyczące całej tej czynności.

Na początek stosujemy poniższy wiersz kodu oraz właściwość UsedRange w celu pobrania kolekcji wszystkich komórek znajdujących się w arkuszu. Nic skomplikowanego, naprawdę:

Set objRange = objWorksheet.UsedRange

Tworząc wystąpienie obiektu Range programu Excel (co, tak się składa, dzieje się przy okazji odnoszenia się do właściwości UsedRange) otrzymujemy kolekcję wszystkich komórek z tego zakresu. To z kolei oznacza, że mamy dostęp do poszczególnych komórek z zakresu, po prostu uruchamiając pętlę For Each, która przejdzie przez wszystkie elementy (komórki) w kolekcji. I… niespodzianka! To właśnie nasz następny krok:

For Each objCell in objRange

Wewnątrz pętli For Each robimy kilka rzeczy. Po pierwsze, określamy, czy wartość znajdująca się w danej komórce jest wartością numeryczną; jeżeli nie, napotkamy pewne problemy (takie, które spowodują awarię skryptu) podczas określania, czy dana wartość jest większa niż 999. Dlatego też, w celu ich uniknięcia, stosujemy funkcję IsNumeric skryptu VBScript, która określi, czy wartość w pierwszej komórce to liczba:

If IsNumeric(objCell.Value) Then

Jeżeli funkcja IsNumeric da nam False, oznacza to, że mamy do czynienia z ciągiem, datą lub inną, nienumeryczną informacją; w takim przypadku wracamy na początek pętli i powtarzamy proces dla następnej komórki w kolekcji. Załóżmy jednakże, że funkcja IsNumeric zwraca True; oznacza to, że mamy do czynienia z wartością numeryczną. W tym przypadku, stosujemy poniższy wiersz kodu w celu określenia, czy ta wartość jest większa niż 999:

If objCell.Value > 999 Then

A co, jeżeli ta wartość będzie większa niż 999? Żaden problem. Po prostu przypisujemy komórce nową wartość (999):

objCell.Value = 999

W tym momencie wracamy na początek pętli i próbujemy jeszcze raz z następną komórką w kolekcji. Kiedy już przejdziemy przez całą kolekcję komórek, wszystkie wartości większe niż 999 zostaną zastąpione wartością 999.

To jest przecież dokładnie to, czym chcieliśmy je zastąpić...

To powinno wystarczyć. Jeżeli jednak ten skrypt nie załatwi sprawy, dajcie znać, a z wielką chęcią napiszę nowy. Jak tamten nie zadziała, napiszę jeszcze jeden. Mogę dla każdego napisać nowy skrypt, jeżeli tylko będzie to oznaczało, że nie muszę się użerać z dżunglą wokół mojego domu.

Do początku strony