Jak można określić pierwszą niedzielę w miesiącu w programie Excel

Niedawno zapytano mnie, czy istnieje łatwy sposób na określenie pierwszej niedzieli w miesiącu. Jak można się było spodziewać, ręczne wyznaczanie pierwszej niedzieli każdego miesiąca w arkuszu kalkulacyjnym raportu wydatków może być żmudne. Ale można to zrobić bardziej automatycznie: To prosta sprawa używania funkcji WEEKDAY i CHOOSE razem.

Najpierw omówmy funkcję TYGODNIOWY, która zwraca dzień tygodnia. Funkcja ta ma dwa argumenty:

  • Serial_Numer: Jest to dowolna data, którą program Excel może rozpoznać, albo data wpisana do komórki arkusza, albo wprowadzona za pomocą funkcji DATA.
  • Return_Type: Ten opcjonalny argument określa konwencję numeracji używaną przez program Excel. W przypadku podania 1 lub pominięcia tego argumentu, program Excel zwraca 1 dla niedzieli, 2 dla poniedziałku, itd. W przypadku podania 2 dla tego argumentu, Excel zwraca 1 dla poniedziałku, 2 dla wtorku itd. Inne argumenty pozwalają na zmianę dnia rozpoczęcia tygodnia w zależności od potrzeb, jak pokazano na rysunku 1.

Rysunek 1: Funkcja WEEKDAY zwraca liczbę, która odpowiada 7-dniowemu tygodniowi.

Jak pokazano w komórce B1 na rysunku 2, funkcja WEEKDAY powraca 4 na dzień 31 grudnia 2014 roku. Oznacza to, że ostatnim dniem 2014 roku jest środa lub czwarty dzień tygodnia.

Druga funkcja, której będziemy używać, to funkcja CHOOSE. Funkcja WYBORU ma do 255 argumentów:

  • Index_Num – Jest to liczba, która koreluje z wartością, do której chcesz wrócić.
  • Wartość 1 do wartości 254 – Są to wartości liczbowe lub tekstowe, które chcesz zwrócić w oparciu o Index_Num.

W naszym przypadku CHOOOSE zwróci wartość, która odpowiada danemu dniu tygodnia, więc będziemy mieli 7 wartości, które odpowiadają każdemu dniu tygodnia, w tym przypadku od 7 w dół do 1. Jak pokazano w komórce B2 na rysunku 2, wzór ten zwraca 4:

=CHOOOSE(TYGODNIOWY (A1,1),7,6,5,4,3,2,1))

Rysunek 2: CHOOSE zwraca wartość z listy na podstawie danych wejściowych, w tym przypadku niezależnie od tego, co zwróci WEEKDAY.

To przypadek, że zarówno WEEKDAY jak i CHOOSE wracają 4 w tej sytuacji. Ponieważ środa jest 4 dniem tygodnia, będziemy musieli dodać do niej 4 dni, aby wrócić w niedzielę.

Komora B3 na rysunku 3 łączy w sobie wszystkie elementy:

=A1+CHOOSE(TYGODNIOWY(A1,1),7,6,5,4,3,2,1)

Rysunek 3: Funkcje CHOOSE i WEEKDAY pozwalają nam opracować wzór, który zwraca pierwszą niedzielę miesiąca.

Jeśli w komórce A1 wpiszesz inną datę końca miesiąca, np. 1/31/2015, wówczas komórka B3 powróci 2/1/2015, jak pokazano na rysunku 4. Pierwsza niedziela lutego 2015 roku przypada na pierwszy dzień miesiąca.

Rysunek 4: Wpisanie 1/31/15 w komórce A1 powoduje powrót komórki B3 do pierwszej niedzieli lutego 2015 r.

Jeśli chcesz zwrócić pierwszy poniedziałek na miesiąc, możesz zmienić wartość 1 w funkcji TYGODNIOWY na 2, jak pokazano na rysunku 5:

=A1+CHOOSE(TYGODNIOWY(A1,2),7,6,5,4,3,2,1)

Rysunek 5: Zmień wartość 1 w funkcji TYGODNIOWY na 2, jeśli chcesz wrócić do pierwszego poniedziałku w miesiącu.

Jeśli musisz wrócić do pierwszego powiedzmy czwartku miesiąca, najprawdopodobniej najłatwiej będzie ci zmodyfikować funkcję WYBORU:

=A1+CHOOSE(TYGODNIOWY(A1,1),4,3,2,8,7,6,5)

Ułożenie wartości w ramach funkcji CHOOSE może być mylące, więc łatwym rozwiązaniem jest ustawienie serii liczb, w których można zobaczyć zwracany dzień tygodnia. Wykonaj te kroki, aby odtworzyć to, co widzisz na rysunku 6:

  • Wpisz 12/31/14 w komórce A1.
  • Wpisz 1/31/15 w komórce A2.
  • Wybierz komórki A1 i A2, a następnie przeciągnij uchwyt do napełniania w dół po kilku wierszach, aby utworzyć serię dat końca miesiąca.
  • Wpisz ten wzór w komórce B1:

=A1+CHOOSE(TYGODNIOWY(A1,1),4,3,2,8,7,6,5)

  • Zmień format daty w komórce B1, aby pokazać dzień tygodnia oraz datę:

Naciśnij klawisz Ctrl-1, aby wyświetlić okno dialogowe Format Komórek, wybierz opcję Data, a następnie kliknij dwukrotnie na format numeryczny Środa, 12 marca 2012.

  • Aby skopiować formułę w dół kolumny, kliknij dwukrotnie na Wypełnij uchwyt w komórce B1.

Wszelkie niespójności w wartościach Wyboru (CHOOSE) zostaną natychmiast wyskakujące, dzięki czemu będziesz wiedział, które argumenty wartości należy zmodyfikować w celu obliczenia właściwego dnia tygodnia.

Rysunek 6: Wartości w funkcji CHOOSE można zmienić w celu zwrócenia pierwszego czwartku w miesiącu.

O autorze:

David H. Ringstrom, CPA, szef Accounting Advisors, Inc., firmy doradczej w zakresie oprogramowania i baz danych z siedzibą w Atlancie, świadczącej usługi szkoleniowe i doradcze w całym kraju. Skontaktuj się z Davidem pod adresem [chronione pocztą elektroniczną] lub śledź go na Twitter . David przemawia na konferencjach o programie Microsoft Excel, uczy webcastów dla i pisze niezależne artykuły w programie Excel dla AccountingWEB , et. al.