Zrozumienie komórek Excela a funkcji zakresu w VBA

Excel jest potężny. Jeśli często go używasz, prawdopodobnie znasz już wiele sztuczek przy użyciu formuł lub automatycznego formatowania, ale korzystając z Komórki i Zasięg funkcje w VBA, możesz podnieść swoją analitykę Excel na zupełnie nowy poziom.

Problem z używaniem funkcji komórek i zakresu 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órek i zakresu pozwalają ci powiedzieć skryptowi VBA 4 błędy, których możesz uniknąć podczas programowania makr programu Excel za pomocą VBA 4 błędy, których możesz uniknąć, programując makra programu Excel za pomocą VBA Prosty kod i makra są kluczami do supermoce programu Microsoft Excel. Nawet osoby niebędące programistami mogą łatwo dodać imponującą funkcjonalność do swoich arkuszy kalkulacyjnych dzięki Virtual Basics for Applications (VBA). Po prostu unikaj błędów w programowaniu dla początkujących! gdzie dokładnie w arkuszu chcesz uzyskać lub umieścić dane. Główną różnicą między dwiema komórkami jest to, do czego się odnoszą.

Komórki zwykle odnoszą się do pojedynczej komórki na raz, podczas gdy Zasięg odwołuje się do grupy komórek jednocześnie. Format tej funkcji to Komórki (wiersz, kolumna).

Odnosi się to do każdej pojedynczej komórki w całym arkuszu. Jest to jeden przykład, w którym funkcja Komórki nie odwołuje się do pojedynczej komórki:

Arkusze („Arkusz 1”). Komórki

Odnosi się to do trzeciej komórki od lewej, górnego rzędu. Komórka C1:

Arkusze („Arkusz 1”). Komórki (3)

Poniższy kod odwołuje się do komórki D15:

Arkusze („Arkusz 1”). Komórki (15,4)

Jeśli chcesz, możesz również odwołać się do komórki D15 “Komórki (15,”re”)”-możesz użyć litery kolumny.

Istnieje duża elastyczność w odniesieniu do komórki za pomocą numeru kolumny i komórki, szczególnie w przypadku skryptów, które potrafią zapętlić duża liczba komórek (i wykonywać na nich obliczenia) bardzo szybko. Przejdziemy do tego bardziej szczegółowo poniżej.

Funkcja Range

Pod wieloma względami funkcja Range jest o wiele bardziej wydajna niż używanie komórek, ponieważ umożliwia odwołanie 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 Zasięg (komórka nr 1, komórka nr 2). Każda komórka może być oznaczona literą.

Spójrzmy na kilka przykładów.

Tutaj funkcja zakresu odnosi się do komórki A5:

Arkusze („Arkusz 1”). Zakres („A5”)

Tutaj funkcja zakresu odnosi się do wszystkich komórek od A1 do E20:

Arkusze („Arkusz 1”). Zakres („A1: E20”)

Jak wspomniano powyżej, nie musisz używać przypisań komórek z literami. W rzeczywistości można użyć dwóch funkcji komórek wewnątrz funkcji zakresu, aby zidentyfikować zakres na arkuszu:

 Z arkuszami („Arkusz 1”). Zakres (. Komórki (1, 1), _. Komórki (20, 5)) Zakończ z 

Powyższy kod odwołuje się do tego samego zakresu co Range (“A1: E20”) działa. Wartością korzystania z niego jest to, że pozwala on pisać kod, który dynamicznie działa z zakresami przy użyciu pętli. Jak pętle „do-while” pracują w programowaniu komputerowym. Jak pętle „do-while” pracują w programowaniu komputerowym. Pętle są jednym z pierwszych typów kontroli, które „ Nauczę się programowania. Prawdopodobnie wiesz o pętlach while i for, ale o tym, co osiąga pętla do-while? .

Teraz, gdy rozumiesz, jak sformatować funkcje komórek i zakresu, przyjrzyjmy się, w jaki sposób możesz kreatywnie wykorzystać te funkcje w kodzie VBA.

Przetwarzanie danych z funkcją komórek

Funkcja Komórki jest najbardziej użyteczna, gdy masz złożoną formułę, którą chcesz wykonać w wielu zakresach komórek. Te zakresy mogą również istnieć na wielu arkuszach.

Weźmy prosty przykład. Załóżmy, że zarządzasz zespołem sprzedaży złożonym z 11 osób i co miesiąc chcesz sprawdzić ich wydajność.

Możesz mieć Arkusz 1 który śledzi ich sprzedaż i wielkość sprzedaży.

Na Arkusz 2 to miejsce, w którym śledzisz ich opinie z ostatnich 30 dni od klientów Twojej firmy.

Jeśli chcesz obliczyć premię na pierwszym arkuszu przy użyciu 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 na podstawie danych z dwóch arkuszy i przeciągnąć ją w dół. To zadziała.

Alternatywą jest tworzenie skryptu VBA, który uruchamia się po każdym otwarciu arkusza lub uruchamia się przyciskiem polecenia na arkuszu, abyś mógł kontrolować jego obliczenia. Możesz użyć skryptu VBA, aby pobrać wszystkie dane sprzedaży z zewnętrznego pliku.

Dlaczego więc nie wyzwolić obliczeń dla kolumny premii w tym samym skrypcie w tym czasie?

Funkcja komórek w akcji

Jeśli nigdy wcześniej nie pisałeś VBA w Excelu, musisz włączyć element menu Deweloper. Aby to zrobić, przejdź do Plik > Opcje. Kliknij Dostosuj wstążkę. Na koniec wybierz Deweloper z lewego panelu, Dodaj w prawym okienku i upewnij się, że pole wyboru jest zaznaczone.

Teraz, kiedy klikniesz dobrze i wróć do głównego arkusza, zobaczysz opcję menu Deweloper.

Możesz użyć Wstawić menu, aby wstawić przycisk polecenia lub po prostu kliknij Wyświetl kod aby rozpocząć kodowanie.

W tym przykładzie uruchamiamy skrypt przy każdym otwarciu skoroszytu. Aby to zrobić, po prostu kliknij Wyświetl kod z menu programisty i wklej następującą nową funkcję do okna kodu.

Sub prywatny skoroszyt_Open () End Sub

Twoje okno kodu będzie wyglądać mniej więcej tak.

Teraz możesz napisać kod do obsługi obliczeń. Za pomocą pojedynczej pętli możesz przejść przez wszystkich 11 pracowników, a dzięki funkcji Komórki pobrać trzy zmienne potrzebne do obliczeń.

Pamiętaj, że funkcja Komórki ma wiersz i kolumnę jako parametry identyfikujące każdą komórkę. Zrobimy “x” w wierszu użyj liczby, aby zażądać danych w każdej kolumnie. Liczba wierszy to liczba pracowników, więc będzie to od 1 do 11. Identyfikator kolumny będzie wynosił 2 dla liczby sprzedaży, 3 dla wielkości sprzedaży i 2 z arkusza 2 dla oceny opinii.

Ostateczne obliczenia wykorzystują następujące wartości procentowe, aby dodać do 100 procent całkowitego wyniku premiowego. Opiera się na idealnej wartości sprzedaży wynoszącej 50, wolumenie sprzedaży wynoszącej 50 000 USD i wyniku oceny 10.

• (Liczba sprzedaży / 50) x 0,4
• (Wielkość sprzedaży / 50 000) x 0,5
• (Wynik oceny / 10) x 0,1

To proste podejście daje pracownikom handlowym premię ważoną. Za 50, objętość 50 000 $ i wynik 10-dostają całą maksymalną premię za miesiąc. Jednak wszystko, co pod jakimkolwiek względem jest niedoskonałe, zmniejsza premię. Wszystko lepsze niż idealne zwiększa premię.

Spójrzmy teraz, w jaki sposób można wyciągnąć całą tę logikę w bardzo prostym, krótkim skrypcie VBA:

Private Sub Workbook_Open () For x = 2 to 12 Arkusze („Arkusz 1”). Komórki (x, 4) = (Arkusze („Arkusz 1”). Komórki (x, 2). Wartość / 50) * 0,4 _ + (Arkusze („Arkusz1”). Komórki (x, 3) .Wartość / 50000) * 0,5 _ + (Arkusze robocze („Arkusz2”). Komórki (x, 2) .Wartość / 10) * 0,1 _ Dalej x Koniec Sub 

Tak będzie wyglądać wyjście tego skryptu.

Jeśli chcesz, aby kolumna Bonus pokazywał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 na innym arkuszu i odwołaj się do niej w kodzie. Ułatwi 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 wykonuj z nimi dość złożone obliczenia.

Możesz wykonywać wszelkiego rodzaju działania na komórkach za pomocą funkcji Komórki - rzeczy takie jak czyszczenie komórek, zmiana formatowania czcionek i wiele więcej.

Aby zbadać wszystko, co możesz zrobić dalej, sprawdź stronę Microsoft MSDN dla obiektu Cells.

Formatowanie komórek z funkcją zakresu

Do zapętlania wielu komórek pojedynczo funkcja Komórki jest idealna. Ale jeśli chcesz zastosować coś do całego zakresu komórek jednocześnie, funkcja Range jest znacznie wydajniejsza.

Jednym z takich przypadków może być sformatowanie zakresu komórek za pomocą skryptu, jeśli zostaną spełnione określone warunki.

Na przykład, powiedzmy, że suma całej wielkości sprzedaży wszystkich pracowników sprzedaży przekracza ogółem 400 000 USD, chcesz podświetlić wszystkie komórki w kolumnie premii na zielono, aby wskazać, że zespół zdobył dodatkową premię dla zespołu.

Przyjrzyjmy się, jak możesz to zrobić za pomocą instrukcji IF Jak używać instrukcji IF w Microsoft Excel Jak korzystać z instrukcji IF w Microsoft Excel Niezależnie od tego, czy jesteś doświadczonym ekspertem czy początkującym arkuszem kalkulacyjnym, powinieneś to sprawdzić przewodnik po instrukcjach IF w Excelu. .

Sub prywatny skoroszyt_Open () Jeśli arkusze („Arkusz 1”). Komórki (13, 3). Wartość> 400000 Następnie ActiveSheet.Range („D2: D12”). Interior.ColorIndex = 4 End If End Sub

Gdy to się uruchomi, jeśli komórka przekroczy cel drużyny, wszystkie komórki w zakresie zostaną wypełnione na zielono.

To tylko jeden prosty przykład wielu działań, które można wykonać na grupach komórek za pomocą funkcji Range. 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
• Przeszukaj zakres za pomocą “Odnaleźć” metoda
• Wiele więcej

Przeczytaj stronę MSDN Microsoft dla obiektu Range, aby zobaczyć wszystkie możliwości.

Przenieś program Excel na wyższy poziom

Teraz, gdy rozumiesz różnice między funkcjami Cells i Range, czas przenieść skrypty VBA na wyższy poziom. Artykuł Danna na temat korzystania z funkcji liczenia i dodawania w programie Excel pozwoli Ci budować jeszcze bardziej zaawansowane skrypty, które mogą bardzo szybko gromadzić wartości we wszystkich zestawach danych.

A jeśli dopiero zaczynasz korzystać z VBA w Excelu, nie zapomnij, że mamy fantastyczny przewodnik wprowadzający do Excela VBA Samouczek programowania Excel VBA dla początkujących Samouczek programowania Excel VBA dla początkujących VBA to narzędzie Microsoft Office. Możesz go użyć do automatyzacji zadań za pomocą makr, ustawiania wyzwalaczy i wielu innych. Zapoznamy Cię z podstawowym programowaniem w programie Excel za pomocą prostego projektu. jak również dla ciebie.