Home-theater-designers
Excel jest potężny. Jeśli często go używasz, prawdopodobnie znasz już wiele sztuczek z użyciem formuł lub autoformatowania, ale korzystając z Komórki oraz Zasięg funkcje w VBA, możesz podnieść swoje analizy Excela na zupełnie nowy poziom.
Problem z używaniem funkcji Cells and Range w VBA polega na tym, że na poziomie zaawansowanym większość ludzi ma trudności ze zrozumieniem, jak te funkcje faktycznie działają. Korzystanie z nich może być bardzo mylące. Oto, jak możesz z nich korzystać w sposób, którego prawdopodobnie nigdy nie wyobrażałeś sobie.
Funkcja komórek
Funkcje Komórki i Zakres pozwalają ci powiedzieć twój skrypt VBA dokładnie gdzie w arkuszu chcesz uzyskać lub umieścić dane. Główną różnicą między tymi dwiema komórkami jest to, do czego się odwołują.
Komórki zwykle odwołują się do jednej komórki na raz, podczas gdy Zasięg odwołuje się do grupy komórek naraz. Format tej funkcji to Komórki (wiersz, kolumna) .
Odwołuje się to do każdej komórki w całym arkuszu. To jedyny przykład, w którym funkcja Cells nie odwołuje się do pojedynczej komórki:
Worksheets('Sheet1').CellsOdwołuje się do trzeciej komórki od lewej w górnym wierszu. Komórka C1:
Worksheets('Sheet1').Cells(3)Poniższy kod odwołuje się do komórki D15:
Worksheets('Sheet1').Cells(15,4)Jeśli chcesz, możesz również odwołać się do komórki D15 za pomocą „Cells(15,„D”)” --- możesz użyć litery kolumny.
Istnieje duża elastyczność w możliwości odwoływania się do komórki za pomocą liczby dla kolumny i komórki, szczególnie w przypadku skryptów, które mogą: pętla przez dużą liczbę komórek (i wykonywać na nich obliczenia) bardzo szybko. Zajmiemy się tym bardziej szczegółowo poniżej.
Funkcja zakresu
Pod wieloma względami funkcja Zakres jest znacznie potężniejsza niż używanie komórek, ponieważ pozwala odwoływać się do pojedynczej komórki lub określonego zakresu komórek jednocześnie. Nie będziesz chciał przechodzić przez funkcję Range, ponieważ odwołania do komórek nie są liczbami (chyba że osadzisz w niej funkcję Cells).
Format tej funkcji to Zakres (komórka nr 1, komórka nr 2) . Każda komórka może być oznaczona liczbą literową.
Spójrzmy na kilka przykładów.
dlaczego mój kontroler ps4 nie działa?
Tutaj funkcja zakresu odwołuje się do komórki A5:
Worksheets('Sheet1').Range('A5')W tym przypadku funkcja zakresu odwołuje się do wszystkich komórek między A1 a E20:
Worksheets('Sheet1').Range('A1:E20')Jak wspomniano powyżej, nie musisz używać przypisań komórek numer-litera. W rzeczywistości możesz użyć dwóch funkcji Cells wewnątrz funkcji Range, aby zidentyfikować zakres w arkuszu, na przykład:
With Worksheets('Sheet1')
.Range(.Cells(1, 1), _
.Cells(20, 5))
End With
Powyższy kod odwołuje się do tego samego zakresu, co funkcja Range('A1:E20'). Wartością w jego użyciu jest to, że pozwoliłoby to napisać kod, który dynamicznie działa z zakresami za pomocą pętli .
Teraz, gdy już wiesz, jak formatować funkcje Cells i Range, przyjrzyjmy się, jak twórczo wykorzystać te funkcje w kodzie VBA.
Przetwarzanie danych z funkcją komórek
Funkcja Komórki jest najbardziej przydatna, gdy masz złożoną formułę, którą chcesz wykonać w wielu zakresach komórek. Te zakresy mogą również występować w wielu arkuszach.
Weźmy prosty przykład. Załóżmy, że zarządzasz 11-osobowym zespołem sprzedaży i co miesiąc chcesz przyjrzeć się ich wydajności.
Możesz mieć Arkusz1 który śledzi ich liczbę sprzedaży i ich wielkość.
Na Arkusz2 to miejsce, w którym śledzisz ich ocenę opinii z ostatnich 30 dni od klientów Twojej firmy.
Jeśli chcesz obliczyć premię na pierwszym arkuszu za pomocą wartości z dwóch arkuszy, możesz to zrobić na wiele sposobów. Możesz napisać formułę w pierwszej komórce, która wykonuje obliczenia przy użyciu danych z dwóch arkuszy, i przeciągnąć ją w dół. To zadziała.
Alternatywą do tego jest utworzenie skryptu VBA, który uruchamiasz przy każdym otwarciu arkusza lub uruchamiasz przyciskiem polecenia w arkuszu, dzięki czemu możesz kontrolować, kiedy oblicza. Mimo wszystko możesz użyć skryptu VBA, aby pobrać wszystkie dane sprzedaży z pliku zewnętrznego.
Dlaczego więc nie uruchomić obliczeń dla kolumny premii w tym samym skrypcie w tym czasie?
Komórki działają w działaniu
Jeśli nigdy wcześniej nie pisałeś VBA w programie Excel, musisz włączyć element menu Deweloper. Aby to zrobić, przejdź do Plik > Opcje . Kliknij Dostosuj wstążkę . Na koniec wybierz Deweloper z lewego okienka, Dodać w prawym okienku i upewnij się, że pole wyboru jest zaznaczone.
Teraz, kiedy klikniesz ok i wróć do głównego arkusza, zobaczysz opcję menu Deweloper.
Możesz użyć Wstawić menu, aby wstawić przycisk polecenia, lub po prostu kliknij Zobacz kod zacząć kodować.
W tym przykładzie skrypt będzie uruchamiany przy każdym otwarciu skoroszytu. Aby to zrobić, po prostu kliknij Zobacz kod z menu programisty i wklej następującą nową funkcję w oknie kodu.
Private Sub Workbook_Open()
End SubTwoje okno kodu będzie wyglądać mniej więcej tak.
Teraz możesz napisać kod obsługujący obliczenia. Za pomocą pojedynczej pętli możesz przejść przez wszystkich 11 pracowników, a za pomocą funkcji Cells pobrać trzy zmienne potrzebne do obliczeń.
Pamiętaj, że funkcja Cells ma wiersz i kolumnę jako parametry identyfikujące każdą pojedynczą komórkę. Zrobimy 'x' wiersz, użyjemy liczby, aby zażądać danych z każdej kolumny. Liczba wierszy to liczba pracowników, więc będzie to od 1 do 11. Identyfikator kolumny to 2 dla liczby sprzedaży, 3 dla wolumenu sprzedaży i 2 z arkusza 2 dla wyniku opinii.
Ostateczne obliczenia wykorzystują następujące wartości procentowe, aby dodać do 100 procent całkowitego wyniku premii. Opiera się na idealnej liczbie sprzedaży wynoszącej 50, wielkości sprzedaży wynoszącej 50 000 USD i wyniku opinii wynoszącym 10.
- (liczba sprzedaży/50) x 0,4
- (Wielkość sprzedaży/50,000) x 0,5
- (Wynik opinii/10) x 0,1
To proste podejście daje pracownikom sprzedaży ważoną premię. Za liczbę 50, obrót 50 000 $ i wynik 10 --- otrzymują całą maksymalną premię za miesiąc. Jednak wszystko, co jest niedoskonałe pod jakimkolwiek czynnikiem, zmniejsza premię. Coś lepszego niż ideał zwiększa premię.
Teraz spójrzmy, jak cała ta logika może zostać zrealizowana w bardzo prostym, krótkim skrypcie VBA:
Private Sub Workbook_Open()
For x = 2 To 12
Worksheets('Sheet1').Cells(x, 4) = (Worksheets('Sheet1').Cells(x, 2).Value / 50) * 0.4 _
+ (Worksheets('Sheet1').Cells(x, 3).Value / 50000) * 0.5 _
+ (Worksheets('Sheet2').Cells(x, 2).Value / 10) * 0.1 _
Next x
End Sub
Tak będzie wyglądał wynik tego skryptu.
ilu obserwujących na tiktok, aby rozpocząć transmisję na żywo
Jeśli chcesz, aby kolumna Bonus wyświetlała rzeczywistą premię w dolarach, a nie procent, możesz ją pomnożyć przez maksymalną kwotę premii. Jeszcze lepiej umieść tę kwotę w komórce w innym arkuszu i odnieś się do niej w swoim kodzie. Ułatwiłoby to późniejszą zmianę wartości bez konieczności edytowania kodu.
Piękno funkcji Cells polega na tym, że można zbudować całkiem kreatywną logikę, z której można pobierać dane wiele komórek na wielu różnych arkuszach i wykonać kilka dość skomplikowane obliczenia z nimi.
Korzystając z funkcji Komórki, możesz wykonywać różne czynności na komórkach — takie jak czyszczenie komórek, zmiana formatowania czcionki i wiele innych.
Aby dowiedzieć się więcej, co możesz zrobić, sprawdź Strona Microsoft MSDN dla obiektu Cells.
Formatowanie komórek z funkcją zakresu
Do przeglądania wielu komórek pojedynczo funkcja Cells jest idealna. Ale jeśli chcesz zastosować coś do całego zakresu komórek jednocześnie, funkcja Range jest znacznie bardziej wydajna.
Jednym z przypadków użycia może być sformatowanie zakresu komórek za pomocą skryptu, jeśli spełnione są określone warunki.
Załóżmy na przykład, że jeśli suma całkowitej wielkości sprzedaży wszystkich pracowników sprzedaży przekracza łącznie 400 000 USD, chcesz podświetlić wszystkie komórki w kolumnie premii na zielono, aby zasygnalizować, że zespół zdobył dodatkową premię zespołową.
Rzućmy okiem, jak możesz to zrobić za pomocą oświadczenie IF .
Private Sub Workbook_Open()
If Worksheets('Sheet1').Cells(13, 3).Value > 400000 Then
ActiveSheet.Range('D2:D12').Interior.ColorIndex = 4
End If
End SubPo uruchomieniu, jeśli komórka przekracza cel zespołu, wszystkie komórki w zakresie zostaną wypełnione na zielono.
To tylko jeden prosty przykład z wielu akcji, które możesz wykonać na grupach komórek za pomocą funkcji Zakres. Inne rzeczy, które możesz zrobić, to:
- Zastosuj kontur wokół grupy
- Sprawdź pisownię tekstu w zakresie komórek
- Wyczyść, skopiuj lub wytnij komórki
- Przeszukuj zakres metodą „Znajdź”
- Wiele więcej
Koniecznie przeczytaj Strona Microsoft MSDN dla obiektu Range, aby zobaczyć wszystkie możliwości.
Przenieś Excel na wyższy poziom
Teraz, gdy rozumiesz różnice między funkcjami Cells i Range, nadszedł czas, aby przenieść skrypty VBA na wyższy poziom. Artykuł Danna na temat korzystania z funkcji liczenia i dodawania w programie Excel pozwoli Ci zbudować jeszcze bardziej zaawansowane skrypty, które mogą bardzo szybko gromadzić wartości we wszystkich zestawach danych.
A jeśli dopiero zaczynasz pracę z VBA w Excelu, nie zapomnij, że mamy fantastykę wstępny przewodnik po Excel VBA jak również dla ciebie.
Udział Udział Ćwierkać E-mail Canon kontra Nikon: która marka aparatu jest lepsza?Canon i Nikon to dwie największe marki w branży aparatów fotograficznych. Ale która marka oferuje lepszą gamę aparatów i obiektywów?
Czytaj dalej Powiązane tematy- Wydajność
- Programowanie
- Programowanie Visual Basic
- Microsoft Excel
Ryan posiada tytuł licencjata z elektrotechniki. Pracował 13 lat w inżynierii automatyzacji, 5 lat w IT, a teraz jest inżynierem aplikacji. Były redaktor zarządzający MakeUseOf, przemawiał na krajowych konferencjach poświęconych wizualizacji danych i występował w krajowej telewizji i radiu.
Więcej od Ryana DubeZapisz się do naszego newslettera
Dołącz do naszego newslettera, aby otrzymywać porady techniczne, recenzje, bezpłatne e-booki i ekskluzywne oferty!
Kliknij tutaj, aby zasubskrybować