Jak wyszukać wartości w arkuszu programu Excel?

Skrypciarze odpowiadają na Wasze pytania

Jak wyszukać wartości w arkuszu programu Excel?

Cześć, Skrypciarze! Jak wyszukać wartości w arkuszu programu Excel za pomocą metody Find?

-- SK

Cześć, SK! Dziś to my zaczniemy od pytania, zastanawialiśmy się bowiem w naszym Centrum Skryptów czy do tegorocznej edycji Zimowej Olimpiady krypciarskiej (j.ang.) nie dodać nowych konkurencji dla języka IronPython oraz Perl. W końcu jednak zagłosowaliśmy jednomyślnie, że dodamy tylko Perl.

Uwaga: Co kryje się pod wypowiedzią „zagłosowaliśmy jednomyślnie”? Już wyjaśniam. Oznacza to, że Greg był za tym, żeby dodać zarówno konkurencje związane z językiem IronPython, jak i z językiem Perl; był (i w sumie jest nadal) nawet za tym, żeby przyjmować skrypty w jakimkolwiek języku programowania. Jednak Skrypciarka Jean Ross była przeciwna dodawaniu IronPythona i z rezerwą wypowiadała się na temat dodawania działu Perl. Dlaczego? Ponieważ zawsze się martwi na zapas i zasypywała nas pytaniami typu: „Jak można przetestować tysiące skryptów napisanych w dziesiątakach języków programowania?”, „Nie damy rady!” – to oczywiście autentyczny cytat.

Greg, wiadomo, nie martwi się zbytnio o logistykę, jakoś mu się zwykle wszystko udaje.

Pojawiła się jednak oficjalna prośba o dodanie IronPythona do tegorocznej edycji naszych skrypciarskich zawodów. Pytam Was zatem wszystkich oficjalnie – czy ktokolwiek byłby chętny, żeby wziąć udział w naszych zmaganiach właśnie w IronPythonie (w grupie początkujących lub zaawansowanych), czy też wolelibyście po prostu zobaczyć, co ten język może zdziałać i w jaki sposób działa? Proszę, wypowiedzcie się na ten temat. Już podaję adres: scripter@microsoft.com. Rozważymy wszystkie za i przeciw, i zadecydujemy gdzieś tak w przyszłym tygodniu. Czy takie demokratyczne rozwiązanie Was satysfakcjonuje?

W tegorocznej edycji (15.02–3.03) mamy naprawdę wystrzałowe nagrody, zajrzyjcie tylko na tę stronę (j.ang.).

Teraz do pracy, musimy się zastanowić, jak skorzystać z metody Find programu Microsoft Excel w celu wyszukania określonej wartości w arkuszu. Najpierw omówimy skrypt, który właśnie napisaliśmy na tę okazję, a następnie omówimy trochę inne podejście, konieczne, jeżeli chcemy wyszukać jakiś zakres wartości.

Ale od początku. Następujący skrypt korzysta z metody Find programu Excel i zapisuje wszystkie komórki mające wartość (Value) równą 4 w arkuszu kalkulacyjnym:

Const xlValues = -4163Set objExcel = CreateObject("Excel.Application")objExcel.Visible = TrueSet objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")Set objWorksheet = objWorkbook.Worksheets("Sheet1")Set objRange = objWorksheet.UsedRangeSet objTarget = objRange.Find(4)If Not objTarget Is Nothing Then    Wscript.Echo objTarget.AddressLocal(False,False)    strFirstAddress = objTarget.AddressLocal(False,False)End IfDo Until (objTarget Is Nothing)    Set objTarget = objRange.FindNext(objTarget)    strHolder = objTarget.AddressLocal(False,False)    If strHolder = strFirstAddress Then        Exit Do    End If    Wscript.Echo objTarget.AddressLocal(False,False)Loop

Const xlValues = -4163



Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True



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

Set objWorksheet = objWorkbook.Worksheets("Sheet1")



Set objRange = objWorksheet.UsedRange



Set objTarget = objRange.Find(4)



If Not objTarget Is Nothing Then

    Wscript.Echo objTarget.AddressLocal(False,False)

    strFirstAddress = objTarget.AddressLocal(False,False)

End If



Do Until (objTarget Is Nothing)

    Set objTarget = objRange.FindNext(objTarget)



    strHolder = objTarget.AddressLocal(False,False)

    If strHolder = strFirstAddress Then

        Exit Do

    End If



    Wscript.Echo objTarget.AddressLocal(False,False)

Loop

Zaczynamy od zdefiniowania stałej xlValues i ustawienia jej wartości na -4163. Tworzymy wystąpienie obiektu Excel.Application, ustawiamy właściwość Visible na True, a następnie za pomocą poniższych wierszy kodu otwieramy plik C:\Scripts\Test.xls i łączymy się w pierwszym arkuszem w pliku:

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

Set objWorksheet = objWorkbook.Worksheets("Sheet1")

Metoda Find należy do obiektu Range programu Microsoft Excel, musimy zatem zdefiniować zakres jeszcze zanim ją wywołamy. Z uwagi na to, że chcemy przeszukać cały arkusz, tworzymy zakres, który równy jest właściwości UsedRange naszego arkusza:

Set objRange = objWorksheet.UsedRange

Powyższy wiersz zaznacza zakres komórek, w których są poszukiwane przez nas dane. Jeżeli np. mamy dane w komórce A1 i G11, to właściwość UsedRange składa się z komórki A1, komórki G11 i wszystkich komórek pomiędzy nimi.

Po zdefiniowaniu zakresu przy pomocy poniższego wiersza kodu wyszukujemy pierwsze wystąpienie wartości docelowej (4):

Set objTarget = objRange.Find(4)

W swojej najprostszej formie (dziś nie będziemy mówić o bardziej skomplikowanych formach) metoda Find wymaga od nas tylko jednego parametru, a mianowicie poszukiwanej wartości. Określcie tylko tę wartość, a metoda Find wyszuka wszystkie komórki, które spełniają Wasze kryteria.

Jak sprawdzić, która komórka jest pierwszą komórką spełniającą nasze kryteria? Najprościej będzie to zrobić, sprawdzając czy odwołanie do obiektu objTarget jest równe Nothing:

If Not objTarget Is Nothing Then

Jeśli tak jest (tzn. jeżeli obiekt objTarget nie jest prawidłowym odwołaniem do obiektu), oznacza to, że nasze wyszukiwanie dobiegło końca bez żadnego rezultatu, a skrypt kończy swoją pracę. Jeśli jednak objTarget jest prawidłowym odwołaniem do obiektu, uruchamiamy poniższe dwa wiersze kodu:

Wscript.Echo objTarget.AddressLocal(False,False)

strFirstAddress = objTarget.AddressLocal(False,False)

W pierwszym wierszu przywołujemy echo wartości właściwości AddressLocal. Dlaczego AddressLocal, a nie Address? Właściwość Address zwraca adresy komórek w następujący sposób:

$A$2

$B$2

$C$2

AddressLocal (jeżeli ustawicie parametry rzędów i kolumnie na False, jak w naszym przykładzie) zwraca adresy komórek w następujący sposób:

A2

B2

C2

Ten drugi styl trochę bardziej nam się podobał.

Uwaga. Właściwość AddressLocal pozwala na zwracanie adresów w różnych formach, na przykład: $A$1; $A1; A1; etc. Więcej informacji na ten temat znajdziecie w witrynie MSDN na stronie Excel VBA Language Reference (j.ang.).

Po zwróceniu echa wartości właściwości AddressLocal, nadajemy tę samą wartość zmiennej strFirstAddress:

strFirstAddress = objTarget.AddressLocal(False,False)

Po co? Metoda Find jest trochę takim dziwolągiem, za jednym razem odnajduje tylko jedno wystąpienie danej wartości docelowej, aby odnaleźć inne wystąpienia musicie więc wielokrotnie wywoływać metodę FindNext.

Co jeszcze? Metody Find oraz FindNext nie wiedzą, kiedy zakończyć swoje działanie. Dajmy na to, że umieścimy metodę FindNext w pętli i zażądamy wyszukania wszystkich pozostałych wystąpień wartości docelowej (a to właśnie zamierzamy zrobić). Następnie metoda FindNext wyszuka wszystkie te wartości. Czy zatrzyma się po zakończeniu swojej jakże fascynującej działalności? Nie. Wróci do początku pętli i zacznie wyszukiwanie od początku. I tak w nieskończoność. Jedynym sposobem na przerwanie tej „niekończącej się opowieści” jest przechowanie adresu pierwszej wartości docelowej w zmiennej strFirstAddress. Kiedy metoda FindNext odnajdzie wystąpienie wartości docelowej, sprawdzi, czy nie jest to przypadkiem adres przechowywany w zmiennej strFirstAdrress. Jeżeli tak, będzie to oznaczało, że przeszukaliśmy już cały arkusz i że czas zakończyć całą tę zabawę.

Zapomnieliśmy podać kod służący do umieszczania metody FindNext w pętli. Już nadrabiamy to zaniedbanie:

Do Until (objTarget Is Nothing)

    Set objTarget = objRange.FindNext(objTarget)



    strHolder = objTarget.AddressLocal(False,False)

    If strHolder = strFirstAddress Then

        Exit Do

    End If



    Wscript.Echo objTarget.AddressLocal(False,False)

Loop

Skonfigurowaliśmy tu pętlę Do, która działa, dopóki obiekt objTarget nie przestanie być prawidłowym odwołaniem do obiektu. Jeżeli tak się stanie, będzie to oznaczało, że nie będziemy w stanie odnaleźć już żadnego innego wystąpienia wartości docelowej. Wewnątrz pętli przywołujemy następujący wiersz kodu, który wyszukuje nam kolejną wartość docelową:

Set objTarget = objRange.FindNext(objTarget)

Chociaż wszystko to wygląda dosyć dziwacznie, wywołujemy tu metodę FindNext i nadajemy jej jeden parametr – objTarget, czyli odniesienie do obiektu komórki, którą wyszukaliśmy za pomocą metody Find. Parametr ten instruuje metodę FindNext, gdzie ma ona rozpocząć wyszukiwanie – znalazłszy pierwszą komórkę zawierającą wartość docelową wiemy, że kolejne poszukiwania chcemy rozpocząć właśnie od tego miejsca. Wygląda to tak, jakbyśmy chcieli wyszukać odniesienie do obiektu objTarget za pomocą FindNext. Nie dajcie się jednak zwieść – instruujemy tu tylko FindNext, gdzie ma rozpocząć, nic więcej.

Co się dzieje, kiedy już przywołamy metodę FindNext? Wyszuka nam ona po prostu następną komórkę, która spełnia nasze kryteria. Gdy tak się stanie, pobieramy wartość właściwości AddressLocal i przechowujemy ją w zmiennej strHolder:

strHolder = objTarget.AddressLocal(False,False)

Następnie sprawdzamy, czy wartości strHolder oraz strFirstAddress są sobie równe:

If strHolder = strFirstAddress Then

If strHolder = strFirstAddress Then

Jeżeli są równe, będzie to oznaczać, że wyszukaliśmy już wszystko, co można było wyszukać, korzystamy więc z twierdzenia Exit Do i wychodzimy z pętli i całego skryptu. Jeżeli te zmienne nie są jednak sobie równe, to wywołujemy echo wartości właściwości AddressLocal, a następnie wracamy do początku pętli i próbujemy od nowa.

Jak już wspominaliśmy na początku, wszystko to działa pięknie i szybko, ale z jednym ALE – tylko wtedy, jeżeli poszukujemy pojedynczej wartości, pojedynczego ciągu znakowego, pojedynczej daty, itp. Metoda Find nie radzi sobie bowiem z zakresem wartości, jeżeli więc chcecie wyszukać wszystkie wartości, dajmy na to, mniejsze niż 5, to w ten sposób Wam się nie uda tego zrobić, gwarantujemy. Będziecie musieli użyć takiego skryptu, który od poprzedniego różni się tym, że sprawdza oddzielnie każdą komórkę zakresu:

Const xlValues = -4163



Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True



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

Set objWorksheet = objWorkbook.Worksheets("Sheet1")



Set objRange = objWorksheet.UsedRange



For Each objCell in objRange.Cells

    If objCell.Value < 5 Then

        Wscript.Echo objCell.AddressLocal(False,False)

    End If

Next

Jak widać, po utworzeniu obiektu Range ustawiamy pętlę For Each, która przechodzi przez każdą komórkę zakresu:

For Each objCell in objRange.Cells

Skrypt następnie sprawdza, czy wartość każdej z komórek jest mniejsza niż 5. Jeżeli jest, to wywołuje echo właściwości AddressLocal tej właśnie komórki. Następnie pętla przechodzi do następnej komórki kolekcji i sprawdza to samo. Ta metoda działa trochę wolniej od metody Find, ale jest jedynym sposobem na wyszukanie zakresu wartości.

Mamy nadzieję, SK, że odpowiedzieliśmy w ten sposób na Twoje pytanie i że w zamian Ty odpowiesz na nasze pytanie z początku artykułu. Uważamy, że jest to uczciwa wymiana. Wypowiadaj się oczywiście tylko i wyłącznie w przypadku, gdy nie zgadzasz się ze Skrypciarką Jean Ross.

Do początku strony