Formuła ogólna
=VLOOKUP(time,bucket_table,column,TRUE)
Podsumowanie
Aby pogrupować godziny w zasobniki, które są nierówne (np. 12:00 - 7:00, 7:00 - 12:00 itd.), Możesz użyć funkcji WYSZUKAJ. W przedstawionym przykładzie formuła n E5 to:
=VLOOKUP(D5,buckets,2,1)
Wyjaśnienie
Jeśli chcesz pogrupować godziny w zasobniki o różnych rozmiarach (np. 12: 00–7: 00, 7: 00–12: 00 itd.), Możesz użyć funkcji WYSZUKAJ.PIONOWO w trybie przybliżonego dopasowania.
Problem
Istnieje kilka sposobów grupowania czasów w programie Excel. Jeśli potrzebujesz tylko pogrupować czasy według godzin, tabela przestawna jest bardzo szybka i łatwa. Jeśli potrzebujesz pogrupować czasy w inne równe przedziały po wiele godzin (np. 3 godziny, 4 godziny itp.), Dobrym rozwiązaniem jest użycie funkcji PODŁOGA. Jeśli jednak chcesz pogrupować czasy w nierówne segmenty, musisz przyjąć bardziej niestandardowe podejście. WYSZUKAJ.PIONOWO, w trybie przybliżonego dopasowania, umożliwia grupowanie czasów w niestandardowe interwały o dowolnej wielkości.
Rozwiązanie
Rozwiązaniem jest zbudowanie tabeli przeglądowej, która za każdym razem „mapuje” do odpowiedniego zasobnika. W pierwszej kolumnie wprowadź czas rozpoczęcia zasobnika. W drugiej kolumnie wprowadź nazwę zasobnika, którego chcesz użyć. Tabelę należy posortować według czasu rozpoczęcia, od najmniejszego do największego. Na koniec skonfiguruj funkcję WYSZUKAJ.PIONOWO, aby za każdym razem wyszukiwała w tabeli zasobników z przybliżonym dopasowaniem.
W przedstawionym przykładzie formuła n E5 to:
=VLOOKUP(D5,buckets,2,1)
D5 to wartość wyszukiwania, „segmenty” to nazwany zakres dla G5: H8, 2 to indeks kolumny, a 1 to flaga umożliwiająca przybliżone dopasowanie. (Możesz również użyć TRUE). Zobacz tę stronę, aby uzyskać pełne wyjaśnienie.
Gdy funkcja WYSZUKAJ.PIONOWO jest w trybie przybliżonego dopasowania, dopasowuje najbliższą wartość, która jest mniejsza lub równa wartości wyszukiwania. W ten sposób możesz myśleć o nadchodzącym czasie wyszukiwania jako o „zaokrągleniu w dół” do odpowiedniego segmentu.
Ta formuła jest doskonałym przykładem tego, jak możesz użyć funkcji WYSZUKAJ.PIONOWO do grupowania danych w całkowicie niestandardowy sposób. Nauczyłem się tego od Jona Acampory z Excel Campus, w jego artykule na temat trzech sposobów grupowania.
