W dzisiejszym poście będziemy kontynuować temat sprawdzania poprawności danych. Nauczymy się tak ustalać sprawdzanie poprawności (walidację) danych, aby było możliwe wpisanie daty, która przypada na dzień pracujący, pomija weekendy oraz święta. Zadanie to wykonamy na podstawie przykładowych danych z rysunku nr 1, gdzie dodatkowo zostały wypisane dni świąteczne wypadające w roku 2019.
W Excelu wystarczy nam do tego zadania funkcja DNI.ROBOCZE, która zwraca dni robocze pomiędzy dwiema datami. Pierwszym argumentem funkcji jest data_pocz, czyli data początkowa. Drugi argument funkcji to data_końc, czyli data końcowa. Trzecim argumentem (opcjonalnym) są święta, czyli dni świąteczne, które chcemy odliczyć z dni roboczych. Nasuwa się tutaj pytanie, co się stanie, kiedy w zapisie funkcji jako dzień początkowy i końcowy podamy tę samą datę. Zapis funkcji powinien wyglądać następująco:
=DNI.ROBOCZE(B3;B3)
W takiej sytuacji funkcja DNI.ROBOCZE zwróci nam wartość 1, kiedy ten dzień będzie dniem pracującym lub wartość 0, kiedy data będzie dniem wolnym od pracy (weekend lub święta) – rys. nr 2.
Taki zapis wystarczy nam do określenia warunku poprawności danych, ponieważ wartość 0 zostanie zinterpretowane przez Excela jako FAŁSZ, czyli Excel odrzuci nam taką datę. Natomiast po wpisaniu poprawnej daty otrzymamy wartość 1, odpowiadającą wartości logicznej PRAWDA. Do poprzedniego zapisu funkcji dołożymy sobie tylko opcjonalny trzeci argument funkcji, czyli święta określone w osobnej tabeli. Zakres ten blokujemy bezwzględnie za pomocą klawisza F4. Zapis funkcji powinien wyglądać następująco:
=DNI.ROBOCZE(B3;B3;$I$2:$I$14)
W trybie edycji komórki kopiujemy powyższą formułę za pomocą skrótu klawiszowego Ctrl+C, następnie użyjemy jej do sprawdzenia poprawności danych dla naszych przykładowych dat zakupów z kolumny Data.
Zaznaczamy komórki, na których chcemy użyć sprawdzania poprawności danych (B3:B6) i wybieramy ikonę polecenia Poprawność danych z karty Dane (rys. nr 4).
Otworzy nam się okno Sprawdzania poprawności danych, gdzie w karcie Ustawienia, w polu Dozwolone wybieramy typ Niestandardowe. Następnie w polu Formuła wklejamy wcześniej skopiowaną formułę za pomocą skrótu klawiszowego Ctrl+V. Tak ustawione parametry sprawdzania poprawności danych zatwierdzamy przyciskiem OK (rys. nr 5).
Po zatwierdzeniu sprawdzania poprawności danych możemy sprawdzić jego działanie, tzn. wpisujemy dowolną datę. Jeśli wpisana data będzie wypadała w weekend lub w dni świąteczne to pojawi się komunikat przedstawiony na rys. nr 6.
Możemy dodatkowo wykonać sprawdzanie poprawności danych dla dostaw do klienta tylko w dniach od poniedziałku do czwartku, czyli z wyłączeniem piątku oprócz weekendów i świąt. Aby widzieć na jaki dzień tygodnia wypada wpisana przez nas data, otwieramy okno Formatowania komórek za pomocą skrótu klawiszowego Ctrl+1, następnie przechodzimy do kategorii Niestandardowe i w polu Typ wpisujemy skrót ddd. Zapis powinien wyglądać następująco: rrrr-mm-dd, ddd. Ustawienie formatowania zatwierdzamy przyciskiem OK (rys., nr 7).
Otrzymamy datę z oznaczeniem dnia tygodnia przedstawioną na rys. nr 8.
Z tym zadaniem możemy poradzić sobie wykorzystując niejako funkcję rozwijającą możliwości standardowej funkcji DNI.ROBOCZE. Użyjemy tutaj funkcji DNI.ROBOCZE.NIESTAND, która zwraca dni robocze między dwoma datami z niestandardowymi parametrami dotyczącymi weekendów. Dwa pierwsze argumenty są takie same jak dla standardowej funkcji. Trzeci argument funkcji to weekend, czyli dni tygodnia jakie chcemy przyjąć za weekend. Mamy tutaj listę możliwości przedstawioną na rys. nr 9.
Trzeci argument możemy również ustalić jako ciąg cyfr 0 i 1, gdzie wartość 0 odpowiada dniom pracującym a wartość 1 to dni wolne od pracy. Zatem zapis formuły powinien wyglądać następująco:
=DNI.ROBOCZE.NIESTAND(F3;F3;"0000111")
Mamy określony trzeci argument funkcji, czyli dni jakie chcemy traktować jako weekend. Pozostaje nam określić dni świąteczne, czyli czwarty parametr funkcji – święta. Tak jak dla poprzedniego przykładu temu parametrowi odpowiada zakres z tabeli święta (I2:I14), zablokowany bezwzględnie za pomocą klawisza F4. Zapis całej funkcji powinien wyglądać następująco:
=DNI.ROBOCZE.NIESTAND(F3;F3;"0000111";$I$2:$I$14)
Wpisaną formułę kopiujemy w trybie edycji komórki za pomocą skrótu klawiszowego Ctrl+C. Zaznaczamy obszar na jaki chcemy nałożyć sprawdzanie poprawności danych, następnie wybieramy ikonę polecenia Poprawność danych z karty Dane (tak jak na rys. nr 4). Otworzy nam się okno Sprawdzania poprawności danych, gdzie karcie Ustawienia, w polu Dozwolone wybieramy typ Niestandardowe. Następnie w polu Formuła wklejamy wcześniej skopiowaną formułę za pomocą skrótu klawiszowego Ctrl+V. Tak ustawione parametry sprawdzania poprawności danych zatwierdzamy przyciskiem OK (rys. nr 10).
Dzięki sprawdzaniu poprawności danych Excel pozwoli nam wpisać datę, odpowiadającą tylko dniom od poniedziałku do czwartku. Jeśli spróbujemy wpisać datę z weekendu lub święto wyświetli nam się komunikat przedstawiony na rys. nr 11.
Podsumowując nasza formuła działa prawidłowo, mianowicie sprawdza czy nasza data jest dniem wolnym od pracy (założenie weekend i piątek)oraz czy nie wypada w święta.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY
Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY
Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY
Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY
VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY
Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY
