Mini kurs

Kalkulator gotówki w Excelu – Stan Kasy – część 5

Część 5 – integracja z programem sprzedażowym

W tym wpisie zaprezentuję jak  zintegrować nasz kalkulator w Excelu z  programem sprzedażowym. Może nie do końca będzie to integracja, gdyż w zasadzie będziemy pobierali dane z programu i wykorzystamy w Excelu. Ja pracowałem na dwóch takich programach i z powodzeniem wykorzystywałem takie rozwiązanie do  automatyzacji pracy.

Co będzie potrzebne? Przede wszystkim nasze oprogramowanie do sprzedaży powinno mieć możliwość eksportu danych sprzedaży najlepiej do pliku xls lub xlsx, czyli standardowych plików Excela. W tej części zaprezentuję taki właśnie przypadek. A co jeśli Wasze oprogramowanie nie ma takiej opcji? Nie wszystko stracone, jednak na potrzeby dzisiejsze nie rozpatrujemy innej możliwości. Jeśli macie takie potrzeby to piszcie albo w komentarzach, albo bezpośrednio do mnie na maila, zobaczymy jak to ugryźć.

Zaczynajmy

Na początek dodajmy nowy arkusz i nazwijmy go, np. PS od Program Sprzedażowy.

Tworzenie nowego arkusza - PS

Teraz pozostaje wyeksportować dane z Waszego programu sprzedażowego i te dane wkleić do arkusza PS. Ja na potrzeby tego wpisy wygenerowałem plik z przykładowymi danymi, u Was najczęściej będzie więcej kolumn, nazwy będą inne, itd. Co ważne, to Wy musicie wiedzieć w jakiej kolumnie jakie są dane. Do naszego przykładu będzie potrzebna wiedza, która kolumna to data sprzedaży, kwota brutto, a także rodzaje płatności. Może się zdarzyć, że Wasz system nie posiada informacji o rodzaju płatności, w takim razie wykorzystacie tylko takie informację, które będziecie w stanie.

D=Excel - dane wyeksportowane z programu sprzedażowego

Więc na początek przejdźmy do arkusza o.

Liczymy utarg

Do liczenia utargu wykorzystamy znaną już nam funkcje SUMA JEŻELI. W komórce H10 wpisujemy: =SUMA.JEŻELI(PS!$B$7:$B$1000;$K$8;PS!$H$7:$H$1000)

Funkcji SUMA JEŻELI wskazujemy, by przeszukała w arkuszu PS zakres $B$7:$B$1000 sprawdziła, czy w komórkach znajduje się wartość z komórki $K$8 czyli data. Jeśli w wierszu wartości się zgadzają to do sumowania dodawana jest wartość z kolumny H  - zakres $H$1000.

Skąd wziął się wiersz nr 1000, np. $B$1000? No cóż, musimy przyjąć jakieś założenia i możemy albo wskazywać, by Excel obliczał wartości dla całej kolumny poprzez wpisanie wartość B:B, albo założyć ograniczenia, np. jak w przykładzie B7:B1000. Po co się ograniczać? Jeśli nasz arkusz nie jest duży i nie obsługuje bardzo dużej ilości wierszy możemy się pokusić o wskazywanie całych kolumn, ale jeśli arkusz jest duży, a danych przybywa to całość będzie działała coraz wolniej, gdyż za każdym razem Excel będzie sprawdzał wartości w całej kolumnie. Dlatego ja jestem zwolennikiem wytyczania granic, które zawsze możemy zwiększyć w razie potrzeb.

Excel - SUMA JEŻELI - liczymy utarg z arkusza

Jak policzymy karty, bony i raty?

Ja bardzo lubię, kiedy tworzone przez mnie narzędzia są bardzo elastyczne i chciałbym, by każdy kto podejdzie do tego przykładu mógł go wykorzystać nawet wtedy kiedy np. nie ma w danych eksportowanych ze systemu sprzedażowego u siebie. Dodamy w takim razie kilka opcji ekstra.

Przechodzimy więc do arkusza STAN KASY…

  1. Na wstążce klikamy zakładkę Deweloper
  2. następnie Formanty wybieramy Wstaw,
  3. Pole wyboru (popularnie nazywane polem CheckBox),
  4. nowy formant przenosimy do komórki G12,
  5. następnie zmieniamy jego nazwę na PS,
  6. klikamy prawym przyciskiem myszy,
  7. z menu kontekstowego wybieramy polecenie Kopiuj,
  8. zaznaczamy komórkę niżej i naciskamy skrót klawiaturowy (Ctrl+V), by wkleić formant,
  9. to samo wykonujemy raz jeszcze wiersz niżej,
  10. na formant obok płatności kartami klikamy prawym przyciskiem myszy,
  11. wybieramy polecenie Formatuj formant…
  12. wpisujemy w pole Łącze komórki: o!$I$12
  13. klikamy na formant obok płatności bonami prawym przyciskiem myszy,
  14. wybieramy polecenie Formatuj formant…
  15. wpisujemy w pole Łącze komórki: o!$I$13 (kroki 13 do 15 powtarzamy jeszcze dla Rat, w adresie podajemy o!$I$14,
  16. kliknijcie w każde pole wyboru ze dwa razy
  17. jeśli wszystko wykonaliście prawidłowo to na arkuszu o powinny pojawić się w komórkach od I12 do I14 wpisy: PRAWDA – jeśli formant jest zaznaczony i FAŁSZ, jeśli nie.

Excel - formant Pole wyboru - CheckBox

Teraz należy zmienić formuły, by liczyły z odpowiednich zakładki płatności. W zakładce z obliczeniami, czyli o wpisujemy w komórkę H12 następującą formułę: =JEŻELI(I12=PRAWDA;SUMA.JEŻELI(PS!$B$7:$B$1000;o!$K$8;PS!$K$7:$K$1000);SUMA.JEŻELI(Karty!$A$2:$A$100;o!$K$8;Karty!$B$2:$B$100))

Jest to funkcja JEŻELI, która sprawdza, czy w komórce I12 znajduje się słowo PRAWDA, jeśli tak to uruchamia formułę LICZ JEŻELI, która sumuje wartości sprzedaży kartami z zakładki PS, jeśli natomiast komórka I12 nie zawiera słowa PRAWDA to kolejną formułą SUMA JEŻELI sumuje wartości płatności kartami, ale z arkusza Karty.

Formułę tą możemy skopiować do dwóch poniższych wierszy i należy tylko zmienić adres komórki, która ma być sprawdzana pod katem występowania słowa PRAWDA, czyli I13 i I14, następnie zmienić adresowanie w pierwszej funkcji SUMA JEŻELI oraz nazwy arkuszy w drugiej funkcji SUMA JEŻELI.

=JEŻELI(I13=PRAWDA;SUMA.JEŻELI(PS!$B$7:$B$1000;o!$K$8;PS!$L$7:$L$1000);SUMA.JEŻELI(Bony!$A$2:$A$100;o!$K$8;Bony!$B$2:$B$100))

=JEŻELI(I14=PRAWDA;SUMA.JEŻELI(PS!$B$7:$B$1000;o!$K$8;PS!$M$7:$M$1000);SUMA.JEŻELI(Raty!$A$2:$A$100;o!$K$8;Raty!$B$2:$B$100))

Excel - funkcja JEŻELI sprawdza warunki za pomocą SUMA JEŻELI

Przechodzimy do arkusza STAN KASY… tutaj nie dajemy wyboru użytkownikowi, jeśli zaczyna korzystać z oprogramowania sprzedażowego to zmieniamy mu możliwość edycji komórki z utargiem, który będzie pobierany z arkusza o.

  1. w komórce H10 dodajemy adres =o!H10,
  2. klikamy prawym przyciskiem myszy na komórce H10 i zmieniamy tło komórki na szare.

Excel - zmiana koloru tła komórki

Obsługa błędów

Dobrze byłoby się zabezpieczyć przed błędami użytkowników, którzy mając czasem do wyboru kilka możliwych raportów z programu sprzedażowego, pomylą się u wyeksportują nie ten, który jest potrzebny. Idealnie, gdyby można było nazwę takiego raportu sprawdzić, ale ja nigdy nie miałem takiego szczęścia i musiałem sam wybierać dane, po których będę w stanie określić, czy to ten raport, którego potrzebuję.

Założenia na potrzeby tego kalkulatora:

  • w moim programie sprzedażowym można wygenerować kilka różnych raportów,
  • każdy różni się ilością eksportowanych kolumn i ich umieszczeniem

Więc jeśli tylko mamy tak prosto to super, jeśli nie, a chcemy obsłużyć błąd to musimy się zastanowić jak to zrobić.

Czy warto? Uważa, że tak, ja miałem często telefony, że coś nie działa w raporcie do póki nie zaimplementowałem odpowiedniego komunikatu. Zdarzały się przypadki, kiedy ktoś dzwonił i mówił, że na pewno ma odpowiedni raport, a jak krok po kroku pytałem co wybiera, na końcu okazywało się, ze jednak nie ten co miał. Mając odpowiedni sygnał dla użytkownika, że cos robi nie tak, może sam spróbuje raz jeszcze.

Zgodnie z moimi założeniami raporty generowane przez program sprzedażowy generują różną ilość kolumn i ich nazwy. Spójrzmy zatem na raport w arkuszu PS. Proponuje więc sprawdzić nazwy tych kolumn.

Excel zaznaczenie komórek wiersza

Na początek dodajmy dwa komunikaty o błędzie w arkuszu o:

  • komórka L10 – Zły plik Raportu, wgraj plik o nazwie:
  • komórka L11 – Wpiszcie nazwę raportu jaki powinien być wgrany, ja wpiszę: NAZWA_RAPORTU_Z_SYSTEMU_SPRZEDAŻOWEGO

Excel - obsługa błędów

W arkuszu PS zaznaczamy wiersz 6 od kolumny A  do ostatniego wpisu, w moim przypadku kolumna N, kopiujemy to dane, np. poprzez skrót (Ctrl+C), przechodzimy do arkusza o i wklejamy w komórkę R6 (Ctrl+V). Poniżej w wierszu 7 sprawdzimy za pomocą funkcji JEŻELI,  czy R6  jest równe A6 w arkuszu PS, jeśli tak to wpisujemy 1, jeśli nie to 0:

=JEŻELI(R6=PS!A6;0;1)

Klikamy na komórkę R7 i przeciągamy do kolumny AE.

Przechodzimy do komórki E7  i za pomocą funkcji JEŻELI, sprawdzimy czy suma z komórek R6:AE6 jest większa od zera, jeśli tak to znaczy, że w arkuszu PS nie są takie dane jakich oczekiwaliśmy i wyświetlimy komunikat błędu z dwóch komórek oddzielonych znakiem spacji L10&” „&L12.

=JEŻELI(SUMA(R7:AE7)>0;L10&" "&L11;"")

Excel- funkcja JEŻELI sprawdza czy błąd

Przechodzimy do arkusza STAN KASY…  i w komórce E6 wpisujemy =o!E6, następnie celowo zmieniamy jedną z nazw kolumn w arkuszu PS, by sprawdzić czy pojawia się komunikat. Możemy teraz kliknąć na komórkę E6 i zmienić kolor czcionki na czerwony oraz ją pogrubić (Ctrl+B).

Excel - komunikat z błędem - błędny raport

W ten sposób dotarliśmy prawie do końca cyklu, pozostała jeszcze jeszcze jedna część, która pojawi się na stronie  niebawem. Oczywiście standardowo zapraszam do zapisywanie się do mojego Newslettera.

Cykl Kalkulator gotówki w Excelu

  1. Kalkulator gotówki - Stan Kasy - część 1
  2. Kalkulator gotówki - Stan Kasy - część 2
  3. Kalkulator gotówki - Stan Kasy - część 3
  4. Kalkulator gotówki - Stan Kasy - część 4

Newsletter

Zapisz się do Neslettera i bądź na bieżąco

Plik do pobrania

Plik do przykładu 5 części cyklu - kalkulator gotówki - STAN KASY.

Plik do Kalkulator gotówki w Excelu część 5

Przykładowe pliki do ćwiczeń spakowane do zip. W jednym jest całość gotowa, a w drugim pliku arkusz do wykonania ćwiczenia.

Zobacz pozostałe wpisy z serii

Kalkulator gotówki w Excelu

No comments yet! You be the first to comment.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *

This site uses Akismet to reduce spam. Learn how your comment data is processed.