Część 4 – dodanie arkuszy, Karty, Bony, Raty oraz użytkowników błędów
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.
- Naciskamy więc znak plus obok arkuszy na dole,
- klikamy dwa razy na nowo powstałym arkuszu,
- nazywamy go Karty,
- 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ć),
- następnie zaznaczamy obszar A1:C1,
- zmieniamy tło na szare oraz pogrubiamy czcionkę (Ctr+B),
- zaznaczamy obszar A1:C1,
- nie puszczając przycisku myszy zjeżdżamy z zaznaczeniem do wiersza 100,
- naciskamy prawy przycisk myszy,
- 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.
- Prawym przyciskiem myszy klikamy na nazwie arkusza Karty,
- z menu kontekstowego wybieramy Przenieś lub kopiuj…
- w kolejnym oknie zaznaczamy (przenieś na koniec) – krok nie wymagany,
- zaznaczamy okienko Utwórz kopię i klikamy na przycisk OK,,
- dwa razy klikamy na nazwie nowego arkusza Karty(2) i zmieniamy nazwę na Bony,
- powtarzamy krok 1, możemy wskazać arkusz Bony i wybieramy znów (przenieś na koniec),
- Zaznaczamy Utwórz kopię i potwierdzamy kliknięciem w przycisk OK,
- dwa razy klikamy na nazwie nowego arkusza Karty(2) lub Bony(2) i zmieniamy nazwę na Raty,
- zmieniamy kwoty w arkuszu Bony,
- zmieniamy kwoty zakupów ratalnych w arkuszu Raty,
- 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)
- 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.
- 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,
- Zaznaczamy komórkę H13 i naciskamy klawisz F2, jeśli teraz dwa razy klikniemy na słowie Karty to możemy je zmienić,
- Zmieniamy więc słowo Karty w formule i zmieniamy na Bony,
- 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,
- 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:
- Kliknij w lewym górnym roku Plik
- Na dole po lewej Opcje
- W oknie po lewej stronie Dostosowanie wstążki
- 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:
- Na wstążce klikamy na kartę Deweloper,
- z Formantów wybieramy,
- Pole kombi,
- Następnie kursorem odrysowujemy kształt na komórce H8,
- Przechodzimy do zakładki o i w komórce P6 wpisujemy: =Załoga!A1,
- 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.
- Klikamy w komórkę P7
- 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
- W zakładce STAN KASY prawym przyciskiem klikamy na formacie Pola Kombi
- z menu kontekstowego wybieramy Formatuj formant…
- 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).
- 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.
- Przechodzimy do arkusza o i w komórkę Q6 wpisujemy formułę: =LICZ.PUSTE(P7:P10)
- w komórki L9 dopisujemy nowy komunikat o błędzie: Nie wpisano imion pracowników
- 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
- 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,
- 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,
- następnie albo ponawiamy krok 2 dla komórki K9, albo po prostu kopiujemy komórkę poniżej,
- klikamy prawym przyciskiem myszy na komórkę G8,
- wybieramy wiaderko, by zmienić tło komórki,
- wybieramy kolor czerwony,
- wybieramy kolor czcionki,
- zmieniamy na biały,
- przechodzimy do komórki G9 i wpisujemy: =o!G9,
- w komórce H12 zmieniamy kolor na szary jak w komórce G12 oraz wpisujemy: =o!H12,
- ponownie klikamy w H12, przesuwamy kursor w dolny prawy róg komórki, naciskamy lewy przycisk myszy,
- 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.