W tym artykule dowiemy się, jak uzyskać ostatni wpis według miesiąca w programie Excel.
Scenariusz:
Podczas pracy z zestawami danych czasami musimy wyodrębnić niektóre wyniki na podstawie różnych kryteriów. Tutaj kryterium problemu jest wartość musi być ostatnim wpisem odpowiadającym danemu miesiącowi. Problem można interpretować na dwa sposoby.
- Ostatni wpis w kolumnie według miesiąca
- Ostatnia wartość miesiąca w kolumnie
Musisz pomyśleć, jaka jest różnica między tymi dwoma. Różnica polega na tym, że pierwszy problem wyodrębnia ostatni wpis z kolumny, który pasuje do nazwy miesiąca, podczas gdy drugi problem wyodrębnia najbliższą wartość daty z wartości daty końca miesiąca. Obie sytuacje zrozumiemy jedna po drugiej. Najpierw zastanowimy się, jak uzyskać ostatni wpis w kolumnie po miesiącu.
Jak uzyskać ostatni wpis w kolumnie według miesiąca w programie Excel?
W tym celu użyjemy funkcji TEKST i funkcji WYSZUKAJ. Tutaj używamy atrybutu funkcji WYSZUKAJ, wyszukując ostatnią wartość. Funkcja WYSZUKAJ przyjmuje 3 argumenty wartości wyszukiwania, tablicę przeglądową i tablicę wyników. Używamy więc argumentu tablicy wyszukiwania jako tablicy 1/wyszukiwania. Aby uzyskać więcej informacji, zobacz poniższą składnię formuły:
składnia formuły :
| =WYSZUKAJ(2,1/(TEKST(daty;"mmrrrr")=TEKST(miesiąc;"mmrrrr"));wartości) |
date : tablica dat w danych
wartości : tablica wyników w danych
miesiąc : kryteria miesiąca
Przykład :
Wszystko to może być trudne do zrozumienia. Rozumiem, jak używać tej formuły w przykładzie. Tutaj mamy dane z wartościami daty i ceny. W tym celu potrzebujemy najnowszego wpisu miesiąca styczeń 2019, który będzie ostatnim wpisem z datą stycznia 2019. Tutaj dla tablicy dat i tablicy cen użyliśmy nazwanych zakresów.
Użyj wzoru:
| =WYSZUKAJ(2,1/(TEKST(daty;"mmrrrr")=TEKST(F9;"mmrrrr"));Cena) |
Wyjaśnienie:
- TEKST(daty;"mmyyyy") zwróci tablicę wartości w formacie "mmyyyy" po przekonwertowaniu jej na wartości tekstowe, które są
{ "012019"; "012019" ; "012019" ; "012019" ; "022019" ; "022019" ; "022019" ; "022019" ; "032019" ; "032019" ; "032019" ; "032019" ; "032019" }
- TEKST(F9,"mmyyyy") zwróci wartość daty (w komórce F9) w formacie "mmyyyy" po przekonwertowaniu wartości daty wyszukiwania, która jest "012019", a ta wartość zostanie dopasowana do powyższej tablicy.
- Wzięcie dzielenia przez 1 spowoduje konwersję tablicy wartości True na 1s i wartości Fałsz na błąd #DIV/0. Więc otrzymamy zwróconą tablicę jako.
{ 1; 1 ; 1 ; 1 ; #DZIAŁ/0! ; #DZIAŁ/0! ; #DZIAŁ/0! ; #DZIAŁ/0! ; #DZIAŁ/0! ; #DZIAŁ/0! ; #DZIAŁ/0! ; #DZIAŁ/0! ; #DZIAŁ/0! }
- Teraz funkcja WYSZUKAJ znajduje w tablicy wartość 2, która nie zostanie znaleziona. Zwraca więc wpis odpowiadający ostatniej 1 wartości.
Użyte zakresy nazwane
daty : C3:C15
Cena : D3:D15
Ostatnia cena to 78,48 odpowiadająca 31-01-2019. Skopiuj formułę do innych komórek, używając klawiszy Ctrl + D lub przeciągając w dół z prawego dolnego rogu komórki.
Jak widać, formuła zwraca wszystkie wymagane wartości. Funkcja zwraca błąd #N/D, jeśli wartość miesiąca wyszukiwania nie pasuje do żadnego wpisu daty. W powyższym przykładzie wpisy dat są posortowane. Tak więc ostatni wpis miesiąca pasuje do ostatniego wpisu daty w miesiącu. Teraz, jeśli wpisy dat nie są posortowane, najpierw posortujemy wartości dat i użyjemy powyższej formuły.
Jak uzyskać wpis ostatniej daty według miesiąca w kolumnie w programie Excel?
W tym celu użyjemy funkcji WYSZUKAJ.PIONOWO i EOMONTH. Funkcja EOMONTH pobiera wartość daty miesiąca i zwraca ostatnią datę wymaganego miesiąca. Funkcja WYSZUKAJ.PIONOWO wyszukuje ostatni dzień miesiąca lub najbliższą poprzednią datę i zwraca wartość odpowiadającą tej wartości.
Użyj wzoru:
| = WYSZUKAJ.PIONOWO ( EOMIESIĄC (F3,0), tabela, 2) |
Wyjaśnienie :
- EOMONTH (F3,0) zwraca ostatnią datę tego samego miesiąca. 0 argument używany do zwracania daty z tego samego miesiąca.
- Teraz wartość wyszukiwania staje się ostatnią datą danego miesiąca.
- Teraz wartość zostanie przeszukana w pierwszej kolumnie tabeli i poszuka ostatniej daty, jeśli found zwraca wartość odpowiadającą wartości, a jeśli nie zostanie znaleziona, zwróci ostatni pasujący wynik, najbliższy wartości wyszukiwania i zwróci odpowiedni wynik.
- Funkcja zwraca wartość z drugiej kolumny tabeli, ponieważ trzecim argumentem jest 2.
Tutaj formuła zwraca wynik 78,48. Teraz skopiuj formułę do innych komórek, używając skrótu Ctrl + D lub przeciągając w dół z prawego dolnego rogu komórki.
Jak widać formuła działa dobrze. Jest tylko jeden problem. Gdy sprawdzamy ostatnią datę odpowiadającą miesiącowi kwietniowemu, funkcja zwraca wartość odpowiadającą marca, ponieważ w tabeli nie ma wpisu daty kwietniowej. Pamiętaj, aby złapać te wyniki.
Oto wszystkie uwagi obserwacyjne dotyczące stosowania wzoru.
Uwagi:
- Użyj formuły odpowiadającej problemowi podanemu w artykule.
- Funkcja WYSZUKAJ.PIONOWO i WYSZUKAJ są funkcjami, gdy wartością wyszukiwania jest liczba i nie występuje w tablicy wyszukiwania, zwraca wartość odpowiadającą tylko najbliższej liczbie mniejszej niż wartość wyszukiwania.
- Excel przechowuje wartości dat jako liczby.
- Powyższe dwie wymienione funkcje zwracają tylko jedną wartość.
- Użyj czwartego argumentu w funkcji WYSZUKAJ.PIONOWO jako 0, aby uzyskać dokładne dopasowanie wartości wyszukiwania w tabeli.
Mam nadzieję, że ten artykuł o tym, jak uzyskać ostatni wpis według miesiąca w programie Excel, jest objaśniający. Więcej artykułów na temat wyodrębniania wartości z tabeli przy użyciu formuł znajdziesz tutaj. Jeśli podobały Ci się nasze blogi, podziel się nimi ze znajomymi na Facebooku. A także możesz śledzić nas na Twitterze i Facebooku. Chcielibyśmy usłyszeć od Ciebie, daj nam znać, jak możemy ulepszyć, uzupełnić lub unowocześnić naszą pracę i uczynić ją lepszą dla Ciebie. Napisz do nas na stronie e-mail
Znajdź ostatni wiersz z mieszanymi danymi w Excelu : praca z liczbami, tekstem lub pustą komórką w tej samej tablicy. Wyodrębnij ostatni wiersz z niepustą komórką za pomocą funkcji DOPASUJ w programie Excel.
Znalezienie ostatniego dnia danego miesiąca : Funkcja EOMONTH zwraca ostatnią datę miesiąca o podanej wartości daty.
Jak uzyskać ostatnią wartość w kolumnie? : Ostatni wpis w dużej ilości danych, Kombinacja funkcji KOMÓRKA i INDEKS zwraca ostatnią wartość w kolumnie lub liście danych.
Różnica z ostatnią niepustą komórką : przyjmuje alternatywną różnicę od ostatniej wartości na liście z liczbami za pomocą funkcji JEŻELI i WYSZUKAJ w programie Excel.
Znajdź ostatni wiersz danych z wartościami tekstowymi w Excelu : W tablicy wartości tekstowych i pustych komórek zwróć ostatnią wartość w tablicy za pomocą funkcji PODAJ.POWTÓR w programie Excel.
Jak korzystać z funkcji SUMA PRODUKT w programie Excel: Zwraca SUMA po pobraniu iloczynu odpowiednich wartości w wielu tablicach w programie Excel.
Jak używać symboli wieloznacznych w programie Excel : Wyszukaj, policz, suma, średnia i bardziej matematyczne operacje na komórkach pasujących do fraz przy użyciu symboli wieloznacznych w programie Excel.
Popularne artykuły:
Jak korzystać z funkcji IF w programie Excel? : Instrukcja IF w programie Excel sprawdza warunek i zwraca określoną wartość, jeśli warunek jest PRAWDA, lub zwraca inną określoną wartość, jeśli jest FAŁSZ.
Jak korzystać z funkcji WYSZUKAJ.PIONOWO w programie Excel : Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
Jak korzystać z funkcji LICZ.JEŻELI w programie Excel : Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby zliczyć określone wartości. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.
Jak korzystać z funkcji SUMA.JEŻELI w programie Excel : To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.