Mini kurs

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

Część 6 – dodajemy nr wersji pliku oraz blokujemy arkusze i ukrywamy formuły

Dotarliśmy do końca całego cyklu – mini kursu. Dziś dodamy informację o wersji pliku, dacie powstania oraz tabeli z informacją jak wyglądał rozwój pliku. Jeśli zastanawiacie się po co to wszystko, to wyobraźcie sobie taką sytuację. Dzwoni do Was kolega z drugiego końca Polski, który wraz z setką innych osób używa naszego pliku. My zrobiliśmy kilka aktualizacji, ale jak pokazuje życie nie każdy zawsze robi lub chce zrobić aktualizację. Skąd będziecie wiedzieć na jakiej wersji on pracuje? Przeżyłem to na swojej skórze, więc wpadłem kiedyś na pomysł wersjonowania. To zawsze pomaga zorientować się gdzie jesteśmy na początku problemu, a takie się pojawiają nawet jak macie tak prosty plik jak ten Stan Kasy, zaczynajmy więc.

Na początku w arkuszu o  wpisujemy:

  • komórka I4 – wersja:
  • komórka J4 04
  • komórka K4z dnia:
  • komórka L4 2019-07-22

Excel - dodanie informacji o wersji pliku i dacie powstania

Teraz stworzymy tabelę z logami, gdybyśmy chcieli w przyszłości rozwijać nasz arkusz. Robimy nagłówki w tabeli:

  • R11 - Data
  • S11 - wersja
  • T11 prace

Następnie wypełniamy tabele przykładowymi danymi i dodajemy krawędzie dla lepszej czytelności, np.

data wersja prace
2019-07-16 1.0 zakończenie prac na wersja 1.0
2019-07-17 1.01  Poprawa błędu w komórce C5
2019-07-16 1.02  Zmiana formatowania
2019-07-20 1.03  …
2019-07-22 1.04  …

Excel - tabela rozwój pliku

Przechodzimy do komórki L4 i skorzystamy z funkcji MAX(), której zadaniem jest wskazać komórkę z największą wartością – wpisujemy = MAX(R12:R37).

Excel - funkcja MAX

W komórce J4 użyjemy funkcji WYSZUKAJ PIONOWO, której zadaniem będzie wpisać nr wersji z tabeli powyżej na podstawie najmłodszej z dat, czyli największej liczby. Wpisujemy więc: =WYSZUKAJ.PIONOWO(L4;R12:S37;2;0)

Funkcja WYSZUKAJ PIONOWO potrzebuje czterech argumentów:

  • L4 - jakich danych szukamy? – w tym wypadku daty z komórki L4
  • Gdzie szukamy powyższej danej – zakres komórek, tabela R12:S37, należy pamiętać, że w pierwszej kolumnie powinny się znaleźć dane, których szukamy
  • Nr kolumny, z której wyniki zostaną zwrócone, w tym wypadku nr wersji
  • Jeśli chcemy w wyniku otrzymać dokładnie dopasowany wynik musimy wpisać FAŁSZ lub 0

Excel - funkcja WYSZUKAJ PIONOWO

Jak widzimy, teraz w wersję i datę mamy ostatnią jaka się pojawiła – pamiętać przy tym należy, że gdyby w przyszłości wersji zaczynało przybywać i informacje w tabeli przekroczyłyby obszar R12:S37 to konieczne będzie poprawianie formuł MAX() oraz WYSZUKAJ.PIONOWO()

To może obsłużmy taki błąd, komunikat powinien być widoczny tylko dla osoby dodającej jakieś funkcjonalnosci w arkuszu, więc wyświetlimy go tylko w arkuszu o. Dodamy więc trzy komunikaty błędów:

  • L12 - Przekroczony obszar logów wersji!
  • L13 - Zmień obszar w komórkach WYSZUKAJ.PIONOWO w J4
  • L14 - Zmień obszar w komórkach MAX w L4

Następnie w komórce J5 dodajemy funkcję: =JEŻELI(CZY.PUSTA(S38)=PRAWDA;"";L12&" "&L13), która za pomocą funkcji JEŻELI  sprawdza czy komórka S38  jest pusta, jeśli tak to nic nie wyświetla, a w przeciwnym wypadku wyświetla dwa komunikaty o błędach z komórek L12 oraz L13 połączonych znakiem łączenia &  oraz spacji - &” ”&.

Excel - komunikat o błędzie, funkcja  CZY PUSTA

Następnie podobny komunikat musimy dodać do komórki L5: =JEŻELI(CZY.PUSTA(R38)=PRAWDA;"";L12&" "&L14)

Różnica tylko w komunikacie błędu zamiast komórki L13  wyświetlamy L14 oraz co oczywiste sprawdzamy inna komórkę czy jest pusta.

Excel - CZY PUSTA w obsłudze błędu

Formatowanie warunkowe

Pozostaje sprawić, by komunikat się  jakoś rzucał w oczy, jeśli się pojawi, dodajmy więc formatowanie warunkowe.

  1. Na początku wyrównajmy do prawej w komórce J5, by komunikat wyświetlał się cały kiedy się pojawi, klikamy więc na komórkę J5 i z menu Narzędzia główne  na wstążce wybieramy odpowiednia ikonę,
  2. zaznaczamy obszar C5:J5,
  3. ze wstążki w Narzędzia główne wybieramy Formatowanie warunkowe,
  4. Nowa reguła…
  5. następnie Użyj formuły do określenia komórek, które należy sformatować,
  6. w polu Formatuj wartości, dla których następująca formuła jest prawdziwa: wpiszmy =JEŻELI(J5<>””;1),
  7. klikamy w przycisk Formatuj,
  8. zakładka Wypełnienie,
  9. wybieramy kolor czerwony,
  10. przechodzimy do zakładki Czcionka,
  11. Styl czcionki: Pogrubiony,
  12. Klikamy w listę rozwijaną Kolor:
  13. Wybieramy kolor biały i OK,
  14. Pozostaje Zastosować lub od razu nacisnąć OK.

Formatowanie warunkowe dla komórki L5

Dla komórki  L5 zaczynamy od punktu 2:

  1. Zaznaczamy zakres: L5:S5,
  2. ze wstążki w Narzędzia główne wybieramy Formatowanie warunkowe,
  3. Nowa reguła…
  4. następnie Użyj formuły do określenia komórek, które należy sformatować,
  5. w polu Formatuj wartości, dla których następująca formuła jest prawdziwa: wpiszmy =JEŻELI(L5<>””;1),
  6. klikamy w przycisk Formatuj,
  7. zakładka Wypełnienie,
  8. wybieramy kolor czerwony,
  9. przechodzimy do zakładki Czcionka,
  10. Styl czcionki: Pogrubiony,
  11. Klikamy w listę rozwijaną Kolor:
  12. Wybieramy kolor biały i OK,
  13. Pozostaje Zastosować lub od razu nacisnąć OK.

Jeśli teraz wpiszemy jakiekolwiek wartości w komórki R38 lub S38 to pojawiają się komunikaty z błędami.

Excel - komunikaty o błędach

Przechodzimy do  arkusza STAN KASY… i  w komórki I4, J4, K4, L4 wpisujemy adresy do arkusza o:

  • I4 wpisujemy =o!I4 – dodatkowo wyrównujemy do prawej
  • J4 wpisujemy =o!J4
  • K4 wpisujemy =o!K4 – dodatkowo wyrównujemy do prawej
  • L4 wpisujemy =o!L4
  • Excel Arkusz STAN KASY

Formatowanie finalne arkuszy

W arkuszu STAN KASY… zaznaczam obszar, w którym mogą się znaleźć formuły, następnie naciskamy prawy przycisk myszy i wybieramy Formatuj komórki…

Formatowanie - Excel

W zakładce Ochrona zaznaczam pole Ukryj – spowoduje to ukrycie formuł po zablokowaniu arkusza. Następnie zaznaczamy obszar, w którym wpisujemy ilości nominałów, obszar D10:D24.Excel - Ukrywanie formuł

 

Excel Formatuj komórki

Klikamy prawym przyciskiem myszy wybieramy kolejny raz Formatuj komórki… tym razem jednak odznaczamy na karcie Ochrona  opcję Zablokuj.

Excel - odblokowanie komórek

To samo wykonujemy dla obszaru K8:K9, to są zakresy, w których użytkownik będzie wpisywać dane, więc powinno być to możliwe.

Zablokowanie arkusza do edycji

Na wstążce klikamy w menu Recenzja  i wybieramy Chroń arkusz, następnie Ok. Dlaczego nie wpisujemy hasła? Ja nie widzę takiej konieczności – sprawa indywidualna. Ja preferuję samo blokowanie arkusza bez haseł, które w Excelu są problemem dla mniej wtajemniczonych.

 

Pozostałe arkusze

  • Załoga – A7:A10 komórki odblokować, następnie Recenzja -> Chroń arkusz
  • Karty A2:C1000 komórki odblokować, Widok odznacz Linie siatki, następnie Recenzja -> Chroń arkusz
  • Bony A2:C1000 komórki odblokować, Widok odznacz Linie siatki, następnie Recenzja -> Chroń arkusz
  • Raty A2:C1000 komórki odblokować, Widok odznacz Linie siatki, następnie Recenzja -> Chroń arkusz
  • O – należy ukryć, klikamy prawym przyciskiem myszy i wybieramy Ukryj; spowoduje to ukrycie arkusza, co prawda można go łatwo pokazać, ale dla większości użytkowników powinno to wystarczyć. Jest inny sposób ukrycia arkuszy, ale to nie tym razem.

Dotarliśmy więc do  końca mini kursu. Jak się okazało, zajęło mi to o jedną część więcej niż przypuszczałem, a także czas przygotowywania pojedynczego wpisu był dość spory. Proszę o komentarze

Kalkulator możecie wykorzystywać w pracy pod warunkiem nie usuwania nagłówka, w innym przypadku proszę o kontakt ze mną.

 

Newsletter

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

Plik do pobrania

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

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

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.