Microsoft Excel ma różne funkcje, a jedną z nich jest automatyczne generowanie raportów. Możesz tworzyć interaktywne arkusze kalkulacyjne, aby uprościć proces wprowadzania danych innych osób do skoroszytu, a także zautomatyzować generowanie raportów. Obie te funkcje wymagają wystarczającej znajomości języka Visual Basic. Poniżej opisano kroki do wykonania obu zadań.
Krok
Metoda 1 z 2: Tworzenie interaktywnego arkusza kalkulacyjnego
Krok 1. Określ układ arkusza kalkulacyjnego
Układ arkusza kalkulacyjnego musi być wykonany tak, aby inni mogli znaleźć pola potrzebne do wprowadzania danych.
Układy arkuszy kalkulacyjnych można układać poziomo lub pionowo. Większości użytkowników łatwiej jest pracować z układem pionowym, zwłaszcza jeśli arkusz kalkulacyjny zostanie wydrukowany
Krok 2. Utwórz etykiety tekstowe dla arkusza kalkulacyjnego
Napisz etykietę u góry każdej kolumny i po lewej stronie każdej komórki w kolumnie, której zamierzasz użyć jako wpisu danych.
Krok 3. Naciśnij jednocześnie alt=""Obraz" i F11.</h4" />
Ta kombinacja klawiszy otworzy edytor Microsoft Visual Basic.
Krok 4. Kliknij dwukrotnie „Ten skoroszyt” w okienku „Project-VBA Project” w lewym górnym rogu
W głównej sekcji edytora pojawi się okno do pisania kodu.
Krok 5. Wybierz „Procedura” z menu Wstaw
Pojawi się okno dialogowe Dodaj procedurę.
Krok 6. Wprowadź nazwę procedury w polu Nazwa
Wprowadź opisową nazwę procedury, na przykład „SumExpenses”, jeśli arkusz kalkulacyjny będzie używany do zgłaszania kosztów podróży. Kliknij OK, aby zamknąć okno dialogowe.
- Nazwy procedur nie mogą zawierać spacji, ale mogą używać podkreślenia (_) w celu zastąpienia spacji.
- Po zamknięciu okna dialogowego Dodaj procedurę pojawi się wiersz oznaczony „Public Sub”, a następnie nazwa procedury. Poniżej tej linii znajduje się spacja i słowa „End Sub”.
Krok 7. Wprowadź kod dla każdego pola wejściowego w arkuszu kalkulacyjnym
Do każdego wpisu napiszesz dwie linijki kodu.
- Pierwsza linia kodu ma postać „Range("nazwa komórki"). Wybierz", "nazwa komórki" reprezentuje komórkę, w której wprowadzane są dane wejściowe. Wpisz nazwę komórki, która znajduje się bezpośrednio po prawej stronie etykiety tekstowej. Jeśli etykieta tekstowa znajduje się w komórce A2, pole do wprowadzania danych to komórka B2 (Zakres("B2"). Wybierz). Znaki cudzysłowu przed i po nazwie komórki są nadal zapisywane na tym etapie, ale nie są już uwzględniane w instrukcji pełnego kodu.
- Kod w drugim wierszu to "ActiveCell. Value = InputBox("InputPrompt")". „InputPrompt” reprezentuje tekst, który pojawi się, aby powiadomić użytkownika o typie danych wprowadzanych do komórki wejściowej. Na przykład, jeśli komórka wejściowa będzie wypełniona wydatkami na jedzenie, zamień „InputPrompt” na „Wprowadź całkowite wydatki na posiłki, w tym napiwki”. (Znaki cudzysłowu dla tekstu instrukcji wejściowej są nadal uwzględniane, podczas gdy znaki cudzysłowu przed i po poleceniu nie muszą być wpisywane.)
Krok 8. Wprowadź kod dla każdego pola obliczeniowego
Ponownie użyte dwie linie są takie same jak poprzednio, ale tym razem ActiveCell. Value jest obliczeniem funkcji numerycznej, na przykład SUMA, gdzie funkcja InputBox służy do wyświetlania instrukcji wejściowych.
Krok 9. Dodaj wiersz kodu, aby zapisać interaktywny arkusz kalkulacyjny
Format to „ActiveWorkbook. SaveAs Nazwa pliku:="Nazwa pliku.xls". „Nazwa pliku" to nazwa interaktywnego arkusza kalkulacyjnego. (Cydzysłowy przed i po „Nazwa pliku.xls” pozostają zapisane, podczas gdy cudzysłowy dla wszystkich poleceń są nie wymagane.)
Jeśli używasz programu Microsoft Excel w wersji 2007 lub nowszej, użyj „.xlsx” zamiast „.xls”. Jeśli jednak istnieją użytkownicy interaktywnego arkusza kalkulacyjnego w wersji Excel 2003 lub starszej, nie będą mogli korzystać z arkusza kalkulacyjnego bez wtyczki czytnika
Krok 10. Naciśnij jednocześnie alt=""Obraz" i Q.</h4" />
Edytor Visual Basic zostanie zamknięty.
Krok 11. Naciśnij jednocześnie klawisze alt=""Obraz" i F8.</h4" />
Pojawi się okno dialogowe Makro.
Krok 12. Kliknij nazwę procedury na liście Makra
Jeśli utworzona procedura jest jedyną na liście, zostanie wybrana automatycznie.
Krok 13. Kliknij przycisk Opcje
Zostaniesz poproszony o wprowadzenie znaku klawiatury, który będzie używany jako skrót za pomocą klawisza Ctrl. Wybierz zrozumiałą literę, która nie była używana jako znak skrótu, na przykład „e” dla „wpisu”.
Krok 14. Kliknij „OK”, aby zamknąć okno dialogowe Opcje makr
Teraz możesz udostępniać użytkownikom interaktywne arkusze kalkulacyjne. Po jego otwarciu użytkownik może użyć klawiszy skrótów do wprowadzania wpisów i postępować zgodnie z utworzonymi instrukcjami, aby wypełnić dane.
Metoda 2 z 2: Automatyzacja generowania raportów
Krok 1. Utwórz raport w tabeli przestawnej
Tabele przestawne służą do podsumowywania danych, dzięki czemu można porównywać liczby i identyfikować trendy. Tabela przestawna musi odnosić się do danych znajdujących się w jednym z procesorów danych lub importowanych z określonej bazy danych.
Krok 2. Napisz skrypt w języku Visual Basic, aby otworzyć i zamknąć raport
Skrypt powinien być w stanie wykonywać różne funkcje wymienione poniżej. Każda funkcja zostanie opisana, a następnie kod podany w nawiasach, aby ją zaimplementować. Pisząc rzeczywisty kod, napisz go w jednym bloku, zastępując nazwę w przykładzie własną i nie umieszczaj nawiasów na początku i na końcu przykładowego kodu.
- Otwórz arkusz kalkulacyjny w trybie tylko do odczytu. [DIM XLAppSet XLApp = CreateObject("Excel. App")xlapp.visible=falsexlapp.workbooks.open \excelloc\filename.xls, 3,]
- Załaduj ponownie dane i zapisz raport, w tym przykładzie jest on zapisany jako plik PDF z podpisem daty. [Truexlapp.activeworkbook. RefreshAllxlapp.activeworkbook. ExportAsFixedFormat xlTypePDF, \pdfloc\raportname_ & DatePart("rrrr, Now()) & "-" & Right("0" & DatePart("m", Now()), 2) & "-" Right("0" & DatePart("d", Now()), 2) & ".pdf"] Jeśli format dokumentu wyjściowego jest inny, zamień format ".pdf" na odpowiednie rozszerzenie zgodnie z potrzebami.
- Zamknij arkusz kalkulacyjny bez zapisywania go, a następnie zamknij program Excel. [xlQualityStandardxlapp.activeworkbook.close Falsexlapp.quit]
- Użyj „.xlsx” zamiast „.xls” na końcu arkusza kalkulacyjnego, jeśli arkusz kalkulacyjny jest zapisany w programie Excel 2007 i nowszym formacie opartym na XML.
Krok 3. Napisz skrypt wsadowy, aby uruchomić skrypt Visual Basic
Celem napisania skryptu jest umożliwienie automatycznego uruchamiania skryptów Visual Basic. Bez skryptu wsadowego skrypty VB muszą być uruchamiane ręcznie.
Skrypt jest napisany w następującym formacie, zastępując nazwę podaną w tym przykładzie własną nazwą folderu i nazwą pliku: [cscript /nologo \fileloc\script.vbs]
Krok 4. Napisz skrypt wsadowy, aby upewnić się, że utworzony plik wyjściowy istnieje
Twój skrypt powinien wykonywać poniższe funkcje. Po każdej funkcji nastąpi kod podany w nawiasach, aby ją zaimplementować. Pisząc rzeczywisty kod, napisz go w jednym bloku, zastępując nazwę w przykładzie nazwą, której potrzebujesz, i nie umieszczaj nawiasów na początku i na końcu przykładowego kodu.
- Sprawdź, czy istnieje plik wyjściowy. [Dla /f "tokens=2-4 delims=/" %%a w ('data /t') ustaw rreport=raportname_%%c-%%a-%%b.pdf)] Jeśli format pliku wyjściowego nie jest PDF, zastąp „.pdf” odpowiednim rozszerzeniem.
- Jeśli plik/raport wyjściowy istnieje, wyślij go jako e-mail do osoby, która go potrzebuje. [Jeśli istnieje \pdfloc\%rreport% (sendmail -f [email protected] -t [email protected] -u Scheduled Report -m Załączony raport %%report%. -a \pdfloc\%rreport% -s twójserwer:port -xu nazwa użytkownika -xp hasło)]
- Jeśli plik/raport wyjściowy nie istnieje w określonej lokalizacji, utwórz procedurę umożliwiającą wysłanie komunikatu, że dostarczenie nie powiodło się. [W innym przypadku (sendmail -f [email protected] -t [email protected] -u Raport nie został uruchomiony -m plik %rreport% nie istnieje w \pdfloc\ -s twójserwer:port -xu nazwa użytkownika -xp hasło)]
Krok 5. Upewnij się, że folder „Pulpit” znajduje się na komputerze
Musisz zweryfikować istnienie folderu Pulpit zarówno dla systemów 32-bitowych, jak i 64-bitowych. W przeciwnym razie Excel i arkusz kalkulacyjny należy otworzyć ręcznie.
- 32-bitowa lokalizacja systemu: c:\windows\system32\config\systemprofile
- 64-bitowa lokalizacja systemu: c:\windows\syswow64\config\systemprofile
Krok 6. Zaplanuj zadania, aby uruchamiać skrypty zgodnie z potrzebami
Skrypty wsadowe muszą być wykonywane sekwencyjnie i nieprzerwanie, nawet jeśli nikt nie korzysta z komputera. Uprawnienia muszą być ustawione na najwyższy możliwy poziom.
