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

Rozbudujemy plik Excela o dodatkowe arkusze, by móc można było z niego łatwiej korzystać, dodamy także arkusz z użytkownikami.

Na początek stwórzmy arkusz Załoga, kliknijmy na plus obok nazw arkuszy, następnie dwa razy klikamy nazwie arkusza, który się pojawi i zmieńmy mu nazwę na Załoga.

Następnie w arkuszu Załoga w komórce A1 wpisuję Załoga, a poniżej cztery imiona, np. Darek, Jarek, Jola, Tola. Zaznaczam całość i zaznaczam wszystkie krawędzie, dodatkowo w komórce A1 dodaje tło i zmieniam format czcionki na pogrubioną.

Potrzebne teraz będą trzy arkusze: Karty, Bony i Raty.

1. Naciskamy więc znak plus obok arkuszy na dole,
2. klikamy dwa razy na nowo powstałym arkuszu,
3. nazywamy go Karty,
4. wpisujemy w komórkach:

• A1 - Data,
• B1 – Kwota
• C1 – Nr paragonu (tak naprawdę ta kolumna nie jest konieczna, możliwe, że po prostu do czegoś Wam może się przydać),

5. następnie zaznaczamy obszar A1:C1,
6. zmieniamy tło na szare oraz pogrubiamy czcionkę (Ctr+B),
7. zaznaczamy obszar A1:C1,
8. nie puszczając przycisku myszy zjeżdżamy z zaznaczeniem do wiersza 100,
9. naciskamy prawy przycisk myszy,
10. wybieramy z menu kontekstowego Wszystkie krawędzie.

Możemy teraz przesunąć kursor pomiędzy kolumnę C, a  D i po zmianie kursora trochę ją rozszerzyć. Potrzebujemy teraz dwa dodatkowe arkusze, by zaoszczędzić pracy skorzystamy z gotowego arkusza Karty.

1. Prawym przyciskiem myszy klikamy na nazwie arkusza Karty,
2. z menu kontekstowego wybieramy Przenieś lub kopiuj…
3. w kolejnym oknie zaznaczamy (przenieś na koniec) – krok nie wymagany,
4. zaznaczamy okienko Utwórz kopię i klikamy na przycisk OK,,
5. dwa razy klikamy na nazwie nowego arkusza Karty(2) i zmieniamy nazwę na Bony,
6. powtarzamy krok 1, możemy wskazać arkusz Bony i wybieramy znów (przenieś na koniec),
7. Zaznaczamy Utwórz kopię i potwierdzamy kliknięciem w przycisk OK,
8. dwa razy klikamy na nazwie nowego arkusza Karty(2) lub Bony(2) i zmieniamy nazwę na Raty,
9. zmieniamy kwoty w arkuszu Bony,
10. zmieniamy kwoty zakupów ratalnych w arkuszu Raty,
11. zmieniamy kwoty płatności kartami w arkuszu.

W arkuszu o w komórce J8 wpisujemy Data: a w komórce obok dzisiejszą datę, np. 11-07-2019 –  lub skorzystajcie z fajnego skrótu do wpisywania dzisiejszej daty (Ctrl+;) W komórce J9 wpiszmy Godzina: a obok np. 14:20, zaznaczmy ten obszar i zaznaczmy krawędzie.

To skoro już mamy wpisana datę sprawdzenia gotówki to możemy wpisać formuły, które nam podliczą dane z arkuszy: Karty, Bony i Raty. Wykorzystamy w tym celu formułę SUMA.JEŻELI(), która by zadziałać potrzebuje 3 argumentów:

SUMA.JEŻELI(przeszukiwany zakres; czego szukamy ; zakres, który sumujemy)

1. Klikamy wiec na komórkę H12 i wpisujemy =SUMA.JEŻELI(Karty!$A$2:$A$100;$K$8;Karty!$B$2:$B$100)

Funkcja Suma Jeżeli sprawdza czy w arkuszu Karty i obszarze A2 do A100 występuję data wskazana w arkuszu o w komórce K8, następnie jeśli data się zgadza to sumuje wartości z komórek B arkusza Karty.

2. Klikamy na komórkę H12, przesuwamy kursor w prawy dolny róg komórki i kiedy zmieni się kursor w czarny krzyżyk to przeciągamy go w dół do komórki H14 i zwalniamy go,
3. Zaznaczamy komórkę H13 i naciskamy klawisz F2, jeśli teraz dwa razy klikniemy na słowie Karty to możemy je zmienić,
4. Zmieniamy więc słowo Karty w formule i zmieniamy na Bony,
5. Zaznaczamy komórkę H14 i naciskamy klawisz F2, następnie dwa razy klikamy na każdym słowie Karty w formule i zmieniamy na Raty,
6. Teraz w komórkach H12:H14 powinny być dobrze wyliczone dane.

Dodanie listy rozwijanej użytkowników

Do tego zadania będziemy musieli mieć aktywną zakładkę Deweloper na wstążce, jeśli nie mamy wykonaj następujące:

1. Kliknij w lewym górnym roku Plik
2. Na dole po lewej Opcje
3. W oknie po lewej stronie Dostosowanie wstążki
4. Z prawego okienka zaznaczyć polecenie Deweloper oraz potwierdzić przyciskiem OK.

Możemy usunąć z komórki H8 imię Darek, przechodzimy do arkusza STAN KASY, gdzie także usuwamy imię, następnie w tym samym arkuszu:

1. Na wstążce klikamy na kartę Deweloper,
2. z Formantów wybieramy,
3. Pole kombi,
4. Następnie kursorem odrysowujemy kształt na komórce H8,
5. Przechodzimy do zakładki o i w komórce P6 wpisujemy: =Załoga!A1,
6. W komórce P7 wpisujemy następującą formułę: JEŻELI(Załoga!A2="";"";LITERY.WIELKIE(Załoga!A2))

Która sprawdza czy komórka A2 w arkuszu Załoga jest pusta, jeśli tak to nic nie wyświetla, a jeśli nie jest pusta wyświetla zawartość tej komórki dużymi literami.

7. Klikamy w komórkę P7
8. Przesuwamy kursor w prawy dolny róg komórki, aż zmieni się kursor w czarny krzyżyk i dwa razy klikamy, formuły powinny się skopiować same
9. W zakładce STAN KASY prawym przyciskiem klikamy na formacie Pola Kombi
10. z menu kontekstowego wybieramy Formatuj formant…
11. w oknie Formatowanie formantu wpisujemy:
    • Zakres wejściowy: o!$P$7:$P$10 – zakres z którego będzie zaczytywać dane do listy rozwijanej
    • Łącze komórki: o!$O$6 – w zależności od wyboru pozycji na liście rozwijanej, w tej komórce pojawi się liczba, w naszym przypadku od 1 do 4
    • Linie zrzutu: 4 – jeśli nie chcemy, by lista miała puste pola, warto wpisać tu taka liczbę ile będziemy mieć rekordów (wpisów w tabeli, z której pobierane są dane).
12. Jeśli wszystko dobrze zrobiliśmy to po kliknięciu w listę rozwijana powinny pojawić się imiona.

Obsługa błędów

Teraz dodamy obsługę błędu - na wypadek, gdyby nie został zapisany żaden pracownik w arkuszu Załoga.

1. Przechodzimy do arkusza o i w komórkę Q6 wpisujemy formułę: =LICZ.PUSTE(P7:P10)
2. w komórki L9 dopisujemy nowy komunikat o błędzie: Nie wpisano imion pracowników
3. następnie w komórkę G9 wpisujemy formułę, która sprawdzi czy jest co najmniej jeden pracownik wpisany na liście z Załoga, jeśli nie to pojawi się komunikat o błędzie, formuła: =JEŻELI(Q6=4;L9;””)

Formuła sprawdza komórkę Q6 czy równa się 4. W komórce Q6 jest formuła licząca puste komórki z tabeli z załogą, jeśli więc nie ma żadnych danych, to formuła tam wyliczy 4, a tym samym warunek będzie prawdziwy i zostanie wstawiony komunikat o błędzie z komórki L9, w przeciwnym wypadki nic nie wypisze.

Arkusz z warstwą prezentacyjną

Przechodzimy do arkusza STAN KASY  i pozostaje dodać nowe funkcjonalności

1. Zaznaczamy obszar J8:K9 – do komórki J8 wpisujemy =o!J8, następnie kopiujemy formułę do komórki poniżej. Cały obszar formatujemy: dodajemy jasny kolor szary do lewej kolumny, a prawa zostawiamy jak jest. Wpisujemy w prawą kolumnę dzisiejszą datę, np. 2019-07-11, a do poniższej godzinę, np. 14:20,
2. przechodzimy do arkusza o i w komórkę K8 wpisujemy znak równości, klikamy na zakładkę STAN KASY… i wskazujemy komórkę K8,
3. następnie albo ponawiamy krok 2 dla komórki K9, albo po prostu kopiujemy komórkę poniżej,
4. klikamy prawym przyciskiem myszy na komórkę G8,
5. wybieramy wiaderko, by zmienić tło komórki,
6. wybieramy kolor czerwony,
7. wybieramy kolor czcionki,
8. zmieniamy na biały,
9. przechodzimy do komórki G9 i wpisujemy: =o!G9,
10. w komórce H12 zmieniamy kolor na szary jak w komórce G12 oraz wpisujemy: =o!H12,
11. ponownie klikamy w H12, przesuwamy kursor w dolny prawy róg komórki, naciskamy lewy przycisk myszy,
12. przeciągamy o dwa wiersze w dół.

Formatowanie warunkowe

Pozostało dodać formatowanie warunkowe do komórki G8, by w przypadku kiedy jest wpisany chociaż jedne pracownik komórka miała kolor szary.

Klikamy więc na komórkę G8,  następnie na wstążce z narzędzi głównych  wybieramy Formatowanie warunkowe i dalej Nowa reguła…

Z okna  Nowa formuła formatowania wybieramy Użyj formuły do określenia komórek, które należy sformatować  a poniżej wpisz formułę: JEŻELI($G$9=””;1) przycisk Formatuj:

• zakładka Wypełnienie - wybierz kolor szary
• zakładka Czcionka – wybierz kolor czarny oraz Styl czcionki Pogrubiony

Zatwierdź przyciskiem OK  i jeszcze raz OK.

Sprawdzenie

Proponuje teraz sprawdzić czy wszystko działa jak należy, zmieńmy kwoty w arkuszach karty i sprawdźmy czy się zgadza, to samo dla pozostałych arkuszy. Usuńmy wszystkich pracowników i zobaczmy czy pojawi się błąd i zmieni formatowanie w arkuszu STNA KASY…

U mnie wszystko działa. Dziękuje za uwagę i standardowo zapraszam do zapisywania się na mój newsletter, by być na bieżąco i zapraszam do komentowania.

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