Spis treści
Wspominając o bazach danych (DB), pierwszą rzeczą, która przychodzi na myśl, są oczywiście wszelkiego rodzaju modne hasła, takie jak SQL, Oracle, 1C lub przynajmniej Access. Oczywiście są to bardzo wydajne (i w większości drogie) programy, które mogą zautomatyzować pracę dużej i złożonej firmy z dużą ilością danych. Problem polega na tym, że czasami taka moc po prostu nie jest potrzebna. Twoja firma może być mała i ze stosunkowo prostymi procesami biznesowymi, ale chcesz ją również zautomatyzować. I to dla małych firm często jest to kwestia przetrwania.
Na początek sformułujmy TOR. W większości przypadków baza danych do księgowości np. sprzedaż klasyczna powinna być w stanie:
- zachować w tabelach informacje o towarach (cenach), zrealizowanych transakcjach i klientach oraz połącz te tabele ze sobą
- mieć wygodne formularze wejściowe dane (z listami rozwijanymi itp.)
- automatycznie uzupełnij niektóre dane drukowane formularze (płatności, rachunki itp.)
- wydać niezbędne Raporty kontrolować cały proces biznesowy z punktu widzenia menedżera
Microsoft Excel poradzi sobie z tym wszystkim przy odrobinie wysiłku. Spróbujmy to zaimplementować.
Krok 1. Dane początkowe w postaci tabel
Będziemy przechowywać informacje o produktach, sprzedaży i klientach w trzech tabelach (na tej samej kartce lub na różnych – to nie ma znaczenia). Zasadniczo ważne jest, aby przekształcić je w „inteligentne stoły” z automatycznym rozmiarem, aby nie myśleć o tym w przyszłości. Odbywa się to za pomocą polecenia Formatuj jako tabelę Zakładka Strona główna (Strona główna — Formatuj jako tabelę). Na karcie, która się wtedy pojawi Konstruktor (Projekt) nadaj tabelom opisowe nazwy w polu Nazwa tabeli do późniejszego wykorzystania:
W sumie powinniśmy otrzymać trzy „inteligentne stoły”:
Należy pamiętać, że tabele mogą zawierać dodatkowe dane wyjaśniające. Na przykład nasz Cenazawiera dodatkowe informacje o kategorii (grupa produktów, opakowanie, waga itp.) każdego produktu oraz tabelę klientem — miasto i region (adres, NIP, dane bankowe itp.) każdego z nich.
Stół Obroty będzie przez nas później wykorzystywany do wprowadzania do niej zakończonych transakcji.
Krok 2. Utwórz formularz wprowadzania danych
Oczywiście możesz wprowadzić dane sprzedażowe bezpośrednio do zielonej tabeli Obroty, ale nie zawsze jest to wygodne i pociąga za sobą pojawienie się błędów i literówek ze względu na „czynnik ludzki”. Dlatego lepiej byłoby zrobić specjalny formularz do wprowadzania danych na osobnym arkuszu czegoś takiego:
W komórce B3, aby uzyskać zaktualizowaną aktualną datę i godzinę, użyj funkcji TDATA (TERAZ). Jeśli czas nie jest potrzebny, zamiast tego TDATA można zastosować funkcję DZIŚ (DZISIAJ).
W komórce B11 znajdź cenę wybranego produktu w trzeciej kolumnie inteligentnej tabeli Cena korzystanie z funkcji VPR (WYSZUKAJ.PIONOWO). Jeśli wcześniej tego nie spotkałeś, najpierw przeczytaj i obejrzyj wideo tutaj.
W komórce B7 potrzebujemy rozwijanej listy z produktami z cennika. W tym celu możesz użyć polecenia Dane – Walidacja danych (Walidacji danych), określ jako ograniczenie Lista (Lista) a następnie wpisz w polu Źródło (Źródło) link do kolumny Imię z naszego inteligentnego stołu Cena:
Podobnie tworzona jest lista rozwijana z klientami, ale źródło będzie węższe:
= ADR.POŚR("Klienci[Klient]")
Funkcjonować POŚREDNI (POŚREDNI) jest to potrzebne w tym przypadku, ponieważ Excel niestety nie rozumie bezpośrednich linków do inteligentnych tabel w polu Źródło. Ale ten sam link „zawinięty” w funkcję POŚREDNI jednocześnie działa z hukiem (więcej na ten temat w artykule o tworzeniu list rozwijanych z treścią).
Krok 3. Dodanie makra wpisu sprzedaży
Po wypełnieniu formularza należy dodać wpisane do niego dane na końcu tabeli Obroty. Za pomocą prostych linków utworzymy linię do dodania tuż pod formularzem:
Tych. komórka A20 będzie miała łącze =B3, komórka B20 będzie miała łącze =B7 i tak dalej.
Teraz dodajmy 2-wierszowe makro podstawowe, które kopiuje wygenerowany ciąg i dodaje go do tabeli Sales. Aby to zrobić, naciśnij kombinację Alt + F11 lub przycisk Visual Basic Zakładka wywoływacz (Deweloper). Jeśli ta karta nie jest widoczna, włącz ją najpierw w ustawieniach Plik – Opcje – Konfiguracja wstążki (Plik — Opcje — Dostosuj wstążkę). W oknie edytora Visual Basic, które się otworzy, wstaw nowy pusty moduł za pomocą menu Wstaw – moduł i wpisz tam nasz kod makra:
Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Copy 'Skopiuj wiersz danych z formularza n = Worksheets("Sales").Range("A100000").End(xlUp) . Row 'określa numer ostatniego wiersza w tabeli. Sales Worksheets("Sales").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues 'wklej do następnego pustego wiersza Worksheets("Input Form").Range("B5,B7,B9"). ClearContents „wyczyść podformularz końcowy” Teraz możemy dodać przycisk do naszego formularza, aby uruchomić utworzone makro za pomocą listy rozwijanej wstawka Zakładka wywoływacz (Programista — Wstaw — Przycisk):
Po jego narysowaniu, przytrzymując lewy przycisk myszy, Excel zapyta, jakie makro trzeba do niego przypisać – wybierz nasze makro Dodaj_Sprzedaj. Możesz zmienić tekst na przycisku, klikając go prawym przyciskiem myszy i wybierając polecenie Zmień tekst.
Teraz po wypełnieniu formularza wystarczy kliknąć nasz przycisk, a wprowadzone dane zostaną automatycznie dodane do tabeli Obroty, a następnie formularz zostanie wyczyszczony, aby wprowadzić nową transakcję.
Krok 4 Łączenie tabel
Przed zbudowaniem raportu połączmy nasze tabele, aby później móc szybko obliczyć sprzedaż według regionu, klienta lub kategorii. W starszych wersjach programu Excel wymagałoby to użycia kilku funkcji. VPR (WYSZUKAJ.PIONOWO) do podstawienia do tabeli cen, kategorii, klientów, miast itp. Obroty. Wymaga to od nas czasu i wysiłku, a także „zjada” mnóstwo zasobów Excela. Począwszy od programu Excel 2013, wszystko można zaimplementować znacznie prościej, konfigurując relacje między tabelami.
W tym celu w zakładce Dane (Data) kliknij związek (Relacje). W wyświetlonym oknie kliknij przycisk Stwórz (Nowy) i wybierz z list rozwijanych nazwy tabel i kolumn, którymi powinny być powiązane:
Ważny punkt: tabele muszą być określone w tej kolejności, tj. tabela połączona (Cena) nie może zawierać w kolumnie klucza (Imię) zduplikowane produkty, jak to ma miejsce w tabeli Obroty. Innymi słowy, powiązana tabela musi być tą, w której szukałbyś danych za pomocą VPRgdyby był używany.
Oczywiście stół jest połączony w podobny sposób Obroty ze stołem klientem przez wspólną kolumnę Klient:
Po skonfigurowaniu linków okno do zarządzania linkami może zostać zamknięte; nie musisz powtarzać tej procedury.
Krok 5. Budujemy raporty za pomocą podsumowania
Teraz, aby przeanalizować sprzedaż i śledzić dynamikę procesu, stwórzmy na przykład jakiś raport za pomocą tabeli przestawnej. Ustaw aktywną komórkę na tabelę Obroty i wybierz zakładkę na wstążce Wstaw – tabela przestawna (Wstaw — tabela przestawna). W oknie, które się otworzy, Excel zapyta nas o źródło danych (np. tabelę Obroty) oraz miejsce do wgrania raportu (najlepiej na nowej kartce):
Najważniejsze jest to, że konieczne jest włączenie pola wyboru Dodaj te dane do modelu danych (Dodaj dane do modelu danych) na dole okna, aby Excel zrozumiał, że chcemy zbudować raport nie tylko na bieżącej tabeli, ale także wykorzystać wszystkie relacje.
Po kliknięciu OK w prawej połowie okna pojawi się panel Pola tabeli przestawnejgdzie kliknąć link Wszystkie kategorieaby zobaczyć nie tylko aktualną, ale wszystkie „inteligentne tabele”, które znajdują się w książce naraz. A następnie, podobnie jak w klasycznej tabeli przestawnej, możesz po prostu przeciągnąć potrzebne pola z dowolnych powiązanych tabel do obszaru FILTRY, Wydziwianie, Stołbcowa or Wartości – a Excel natychmiast zbuduje na arkuszu dowolny raport, którego potrzebujemy:
Nie zapominaj, że tabela przestawna musi być okresowo aktualizowana (gdy zmienią się dane źródłowe), klikając ją prawym przyciskiem myszy i wybierając polecenie Zaktualizuj i zapisz (Odświeżać), ponieważ nie może tego zrobić automatycznie.
Ponadto, wybierając dowolną komórkę w podsumowaniu i naciskając przycisk Wykres przestawny (wykres przestawny) Zakładka Analiza (Analiza) or parametry (Opcje) możesz szybko zwizualizować obliczone w nim wyniki.
Krok 6. Wypełnij materiały do drukowania
Kolejnym typowym zadaniem każdej bazy danych jest automatyczne wypełnianie różnych drukowanych formularzy i formularzy (faktury, faktury, akty itp.). Napisałem już o jednym ze sposobów na zrobienie tego. Tutaj realizujemy np. wypełnienie formularza po numerze konta:
Zakłada się, że w komórce C2 użytkownik wprowadzi liczbę (numer wiersza w tabeli Obroty, w rzeczywistości), a następnie dane, których potrzebujemy, są pobierane za pomocą znanej już funkcji VPR (WYSZUKAJ.PIONOWO) i funkcje INDEKS (INDEKS).
- Jak używać funkcji WYSZUKAJ.PIONOWO do wyszukiwania i wyszukiwania wartości
- Jak zastąpić funkcje WYSZUKAJ.PIONOWO funkcjami INDEX i PODAJ.POZYCJĘ?
- Automatyczne wypełnianie formularzy i formularzy danymi z tabeli
- Tworzenie raportów za pomocą tabel przestawnych