Niedawno zapytano mnie, czy istnieje prosty sposób na określenie pierwszej niedzieli w miesiącu. Jak można się spodziewać, ręczne określanie pierwszej niedzieli każdego miesiąca w arkuszu kalkulacyjnym raportu wydatków może być żmudne. Można jednak zrobić to w sposób bardziej automatyczny: Wystarczy użyć funkcji TYDZIEŃ i WYBIERZ.
Omówmy najpierw funkcję WEEKDAY, która zwraca dzień tygodnia. Funkcja ta ma dwa argumenty:
- Serial_Number: Jest to dowolna data, którą Excel może rozpoznać - albo data wpisana do komórki arkusza, albo wprowadzona za pomocą funkcji DATE.
- Return_Type: Ten opcjonalny argument określa konwencję numerowania stosowaną przez program Excel. W przypadku podania 1 lub pominięcia tego argumentu program Excel zwraca 1 dla niedzieli, 2 dla poniedziałku itd. Jeśli dla tego argumentu zostanie określona wartość 2, Excel zwraca 1 dla poniedziałku, 2 dla wtorku itd. Inne argumenty pozwalają dowolnie przesuwać początkowy dzień tygodnia, jak pokazano na rysunku 1.
Rysunek 1: Funkcja WEEKDAY zwraca liczbę odpowiadającą 7-dniowemu tygodniowi.
Jak pokazano w komórce B1 na rysunku 2, funkcja WEEKDAY zwraca liczbę 4 dla 31 grudnia 2014 roku. Oznacza to, że ostatnim dniem roku 2014 jest środa, czyli czwarty dzień tygodnia.
Drugą funkcją, której użyjemy, jest funkcja CHOOSE. Funkcja WYBIERZ może mieć do 255 argumentów:
- Index_Num - jest to liczba odpowiadająca wartości, którą chcemy zwrócić.
- Wartości od 1 do 254 - są to wartości liczbowe lub tekstowe, które mają zostać zwrócone na podstawie Index_Num.
W naszym przypadku funkcja CHOOOSE ma zwracać wartość odpowiadającą dniowi tygodnia, więc będziemy mieli 7 wartości odpowiadających poszczególnym dniom tygodnia, w tym przypadku od 7 do 1. Jak pokazano w komórce B2 na rysunku 2, formuła zwraca 4:
=CHOOSE(WEEKDAY(A1,1),7,6,5,4,3,2,1))
Rysunek 2: Formuła CHOOSE zwraca wartość z listy na podstawie danych wejściowych, w tym przypadku wartość zwracaną przez WEEKDAY.
Przypadkowo zarówno WEEKDAY, jak i CHOOSE zwracają w tej sytuacji wartość 4. Ponieważ środa jest czwartym dniem tygodnia, musimy dodać do niej 4 dni, aby zwrócić niedzielę.
Komórka B3 na rysunku 3 łączy wszystkie elementy w jedną całość:
=A1+CHOOSE(WEEKDAY(A1,1),7,6,5,4,3,2,1)
Rysunek 3: Funkcje CHOOSE i WEEKDAY umożliwiają utworzenie formuły zwracającej pierwszą niedzielę w miesiącu.
Jeśli w komórce A1 wpiszemy inną datę zakończenia miesiąca, na przykład 1/31/2015, to komórka B3 zwróci 2/1/2015, jak pokazano na rysunku 4. Tak się składa, że pierwsza niedziela lutego 2015 r. wypada pierwszego dnia miesiąca.
Rysunek 4: Wpisanie 1/31/15 w komórce A1 spowoduje, że komórka B3 zwróci pierwszą niedzielę lutego 2015 roku.
Jeśli chcesz zwrócić pierwszy poniedziałek miesiąca, możesz zmienić liczbę 1 w funkcji WEEKDAY na 2, jak pokazano na rysunku 5:
=A1+CHOOSE(WEEKDAY(A1,2),7,6,5,4,3,2,1)
Rysunek 5: Zmień 1 w funkcji WEEKDAY na 2, jeśli chcesz zwrócić pierwszy poniedziałek w miesiącu.
Jeśli chcesz zwrócić pierwszy czwartek w miesiącu, najłatwiej będzie zmodyfikować funkcję CHOOSE:
=A1+CHOOSE(WEEKDAY(A1,1),4,3,2,8,7,6,5)
Układanie wartości w funkcji WYBIERZ może być mylące, dlatego łatwym rozwiązaniem jest ustawienie serii liczb, w których widać zwracany dzień tygodnia. Wykonaj poniższe kroki, aby odtworzyć to, co widać na rysunku 6:
- W komórce A1 wpisz 12/31/14.
- W komórce A2 wpisz 1/31/15.
- Zaznacz komórki A1 i A2, a następnie przeciągnij uchwyt wypełnienia w dół o kilka wierszy, aby utworzyć serię dat końca miesiąca.
- W komórce B1 wpisz następującą formułę:
=A1+CHOOSE(WEEKDAY(A1,1),4,3,2,8,7,6,5)
- Zmodyfikuj format daty w komórce B1 tak, aby oprócz daty wyświetlany był również dzień tygodnia:
naciśnij klawisz Ctrl-1, aby wyświetlić okno dialogowe Formatowanie komórek, wybierz polecenie Data, a następnie kliknij dwukrotnie format liczbowy Środa, 12 marca 2012 r.
- Kliknij dwukrotnie uchwyt Wypełnij w komórce B1, aby skopiować formułę w dół kolumny.
Natychmiast pojawią się wszelkie niespójności w wartościach CHOOSE, dzięki czemu będziesz wiedział, które argumenty wartości zmodyfikować, aby obliczyć właściwy dzień tygodnia.
Rysunek 6: Wartości w funkcji WYBIERZ można zmienić tak, aby zwracały pierwszy czwartek w miesiącu.
O autorze:
David H. Ringstrom, CPA, kieruje firmą Accounting Advisors, Inc. z siedzibą w Atlancie, zajmującą się doradztwem w zakresie oprogramowania i baz danych, świadczącą usługi szkoleniowe i konsultingowe na terenie całego kraju. Skontaktuj się z Davidem pod adresem [email protected] lub śledź go na Twitterze. David przemawia na konferencjach na temat Microsoft Excel, prowadzi webcasty dla CPE Link i pisze niezależne artykuły na temat Excela dla AccountingWEB, Going Concern, et.al.
