Jak zautomatyzować dodatek Excela

W artykule Jak uzyskać więcej korzyści z programu Excel Solver omówiłem techniki ulepszania modelu i usprawniania analizy. Jednym z tematów, na które otrzymałem najwięcej pytań, jest automatyzacja solvera do rozwiązywania wielu wartości.

W poprzednim artykule było krótkie wyjaśnienie, ale nie było nic poświęconego temu, jak wdrożyć go od początku do końca. W tym artykule omówimy szczegółowo pojęcia i metody krok po kroku od początku do końca. Na koniec będziesz mógł zautomatyzować dowolny z własnych modeli osobistych i skalować analizę.

Jak to zrobimy

Wykorzystamy rejestrator makr w programie Excel do obsługi większości rzeczywistego kodu. Narzędzie do nagrywania makr pozwoli Excelowi przekonwertować nasze ręczne kroki na kod VBA.

Następnie zidentyfikujemy kluczowe fragmenty kodu do zmiany. Następnie utworzymy pętlę, aby dynamicznie wybierać i zastępować te wartości, a następnie ponownie uruchomić solver.

Będzie to półtechniczne, ale dzięki temu stylowi programowania możemy skupić się na procesie i koncepcjach zamiast skupiać się na pisaniu kodu.

Konfigurowanie rejestratora makr

Funkcjonalność rekordów makr znajduje się na karcie dewelopera na wstążce. Ta opcja nie jest dostępna domyślnie, ale jest łatwa do wdrożenia. Jeśli przejdziesz do opcji wstążki, zobaczysz pole wyboru dla karty programisty.

Poniższy zrzut ekranu dotyczy komputerów Mac, ale program Excel na komputery PC jest podobny.

Jeśli nie zainstalowałeś dodatku solver, możesz go zainstalować za pomocą menu Dodatki. Witryna pomocy technicznej firmy Microsoft zawiera instrukcje dla wszystkich platform: Załaduj dodatek Solver w programie Excel.

Nagrywanie makra

Teraz, gdy konfiguracja jest zakończona, jesteśmy gotowi do nagrywania! Skonfiguruj odpowiednio swój obszar roboczy ze wszystkimi formułami i odniesieniami. Będziesz chciał ustawić wszystko do punktu, zanim zbudujesz model solwera.

Wróć do karty dewelopera na wstążce. Zobaczysz kartkę z czerwoną kropką. Kliknij ten przycisk, aby otworzyć rejestrator.

Nadaj swojemu makro nazwę i wypełnij opis, jeśli chcesz.

Po naciśnięciu OK rozpocznie się rejestrator.

Przejdź przez kolejne etapy konfiguracji solvera i rozwiąż go. Po zakończeniu wróć do karty programisty i zatrzymaj nagrywanie. Będzie to ta sama lokalizacja, co przycisk użyty do rozpoczęcia nagrywania.

Teraz, gdy mamy kod podstawowy, możemy go wyczyścić i przygotować do następnych kroków.

Wyczyść kod

Teraz możemy edytować kod, usunąć niepotrzebne fragmenty i upewnić się, że używamy tylko niezbędnych fragmentów nagrania makra.

Możesz edytować kod za pomocą edytora VBA programu Excel. Aby uzyskać dostęp do zarejestrowanego kodu, kliknij przycisk Makra.

Przejdź do makra w nowym menu i kliknij edytuj.

Teraz, gdy mamy otwarty edytor kodu, możemy zacząć wprowadzać poprawki.

Usuwanie niepotrzebnego kodu

Jeśli zdarzyło Ci się kliknąć kilka innych rzeczy podczas nagrywania, możesz je teraz usunąć i pozostać z czymś podobnym do poniższego. Jeśli jest dużo dodatkowego kodu i obawiasz się go złamać, zawsze możesz nagrać ponownie.

Konfigurowanie referencji solvera

Ostatni krok, zanim przejdziemy dalej, przejdź do Narzędzia > Referencje i wybierz Solver. Jeśli tego nie zrobisz, pakiet nie załaduje się w makrze i pojawi się błąd.

Możesz teraz zmienić swoje parametry, otwórz menu makr, wybierz swoje makro, naciśnij uruchom, a zaktualizuje wyniki.

Dopasujmy kod do naszego modelu solwera. Zobaczysz, jak się układa. Język jest inny, ale możesz poskładać, co to wszystko znaczy.

Następnie wyizolujemy części, które musimy zmienić i zakończymy naszą automatyzację.

Opłucz i powtórz – budowanie pętli

Teraz, gdy mamy działający solwer. Musimy to powtórzyć wiele razy. Złóżmy przykład i utrwalmy plan.

Zaczęliśmy od budżetu w wysokości 1000 USD w pierwszym modelu, ale chcemy zrobić to samo dla przyrostów o 100 USD, aż do 2500 USD.

Zanurzmy się! Listę wartości mamy w kolumnie B, a wynik w kolumnie C. Gdybyśmy napisali proces, wyglądałoby to tak:

1. Ustaw łączne wydatki na wartość w B10.
2. Rozwiąż model.
3. Umieść wyjście w C10.
4. Wybierz następną wartość B.
5. Ustaw wyjście w wierszu C.
6. Powtórz kroki 2-5 dla wszystkich wartości w B.

Rozpoczęcie pętli

Pętle to konstrukcje kodu, które powtarzają akcję, aż do osiągnięcia logicznego punktu końcowego. Pętla przyda się tutaj, aby powtórzyć ten sam proces, uruchamiając solwer, na każdej wartości, aż dojdziemy do końca wartości.

W tym przykładzie zdefiniujemy punkt końcowy liczbą wierszy. B10:B25 ma 16 wartości.

Rozpoczniemy naszą pętlę i owiniemy ją wokół naszego kodu solvera. Skonfigurujemy zmienną do przechowywania numeru pętli (i) i przejdziemy przez każdą wartość, za każdym razem rozwiązując pętlę.

Resetowanie solvera

Na początku naszego kodu dodamy jeszcze jeden warunek. Ponieważ chcemy uruchamiać solver w każdej iteracji, resetujemy go w każdej pętli. Spowoduje to wyczyszczenie ustawień i uruchomienie nowego modelu.

Możemy to zrobić za pomocą SolverReset .

Aktualizowanie odwołań do komórek

Rozwiązujemy teraz szesnaście razy, ale musimy zaktualizować nasze odwołania do komórek. Chociaż pierwotnie odwoływaliśmy się do B10, musimy przejść o jedną komórkę w dół, aby zaktualizować limity. Możemy to zrobić za pomocą funkcji offset. Możemy zaktualizować „$ B $ 10”, aby odwoływać się do komórki, a następnie przesunąć ją o i wiersze (0 w pierwszym przebiegu, 1 wiersz w drugim itd.). Za każdym razem, gdy pętla się uruchamia, i zwiększa się o jeden.

Zbieranie danych wyjściowych

Dane wyjściowe będą się zmieniać za każdym razem, więc będziemy chcieli je zapisać. Możemy postępować zgodnie z tym samym procesem powyżej, ale odsunąć od C10 i ustawić go na równą wartości rozwiązanego modelu. Następnie pętla zaczyna się od nowa.

Wywołamy C10 przesunięte o odpowiednią liczbę wierszy, a następnie wkleimy wartość naszego wyjścia.

Omijanie powiadomień o solverze

Oszczędźmy sobie bólu głowy i nie uczmy się przez chwilę na przykładach. Pamiętasz, kiedy uruchomiłeś solver i pojawiło się menu z pytaniem, czy chcesz zaakceptować rozwiązanie? Pojawi się to za każdym razem, jeśli go nie wyłączysz. Wyobraź sobie, że jeśli utworzysz pętlę z setkami wartości, za każdym razem będziesz musiał kliknąć akceptuj!

Śmiało i dodaj następujące wiersze po kodzie solvera i dodaj (TRUE) do SolverSolve. Będzie to naśladować akceptację zmian.

Teraz mamy gotowy kod!

Działa i działa

Możemy teraz uruchomić kod, wybierając go z listy makr i uruchamiając. Jeśli chcesz użyć skoroszytu jako szablonu, możesz przypisać makro do przycisku, aby ułatwić korzystanie. Znajdziesz je w zakładce dewelopera.

Możesz także dodać dowolne wykresy porównawcze. W tym przypadku chcemy porównać tempo wzrostu wydatków ze wzrostem liczby konwersji. Ten przykład nie jest interesujący, ale prawdziwe dane mogą pomóc w ujawnieniu punktów przerwania wydajności

Wniosek

Omówiliśmy całkiem sporo! Gratulujemy uruchomienia automatycznego solwera. Omówiony przez nas przykład był zbyt uproszczony, ale możesz zastosować te same koncepcje i zastosować je do dowolnego modelu solwera.

Może to zaoszczędzić ogromną ilość czasu w przypadku modeli, które trzeba wielokrotnie rozwiązywać, i zachęcić użytkowników do eksperymentowania z różnymi scenariuszami.

Pierwotna data publikacji: 30.07.2019