Czy znasz fajną sztuczkę tworzenia odwołania do nagłówka kolumny w ostatniej niepustej komórce wiersza? Najlepsze: nie potrzebujesz żadnych linii pomocniczych ani kolumn. To jest takie proste:
Połącz cztery funkcje IFERROR (), INDEX (), MAX () i IF ()
Dyrektor zarządzający ds. sprzedaży prześle Ci listę zawartych co miesiąc umów na produkty, które są wycofywane ①. Należy użyć formuły w kolumnie N, aby określić ostatni miesiąc sprzedaży dla każdego produktu - bez żadnych linii pomocniczych czy kolumn. Jeśli nie zawarto więcej umów, wpisz pustą komórkę w kolumnie N.
Ten banalny na pierwszy rzut oka przykład okazuje się trudnym orzechem do zgryzienia bez użycia pomocniczych linii czy kolumn. jak Excel do praktyki-Czytelnicy wkręcają się w orzechy! W komórce mamy następującą formułę tablicową do rozwiązywania problemów N2 utworzono ②:
{= JEŻELI BŁĄD (INDEKS ($ B $ 1: $ M $ 1; MAKS (JEŻELI (B2: M2 ""; KOLUMNA (B2: M2) -1; -1)); "")}
Zobacz, jak to działa krok po kroku
Zacznij od warunku IF () wbudowanego w formułę tablicową: {= JEŚLI BŁĄD (INDEKS ($B$1:$M$1; MAX (JEŻELI (B2: M2 "", KOLUMNA (B2: M2) -1, -1)));"")}
Warunek IF () tworzy fikcyjną linię pomocniczą w formule tablicowej i sprawdza obecność komórek B2 dopóki M2czy są puste, czy nie. Jeśli komórka jest pusta, wartość -1 jest w przeciwnym razie zwracana przez funkcję COLUMN(), odpowiedni numer kolumny minus wartość 1.
W formule wymagane jest odjęcie 1, ponieważ pierwsza kolumna w tabeli nie zawiera nazwy miesiąca, ale nazwę produktu. Poniżej dowiesz się, jak używać funkcji INDEX () do wyświetlania odpowiedniej nazwy miesiąca, która - jeśli nie odjęto 1 - ze względu na dodatkową kolumnę A. byłby niepoprawny dokładnie o jedną kolumnę.
Jeśli wszystkie komórki w zakresie B2: M2 są puste, wartość -1 (brak sprzedaży produktu) tworzy błąd, którego używamy do reprezentowania pustej komórki. Aktywną linię pomocniczą widać na rys. ③ w linii 3.
W kolejnym kroku za pomocą funkcji MAX() odczytujesz największą wartość, w której zagnieżdżony jest warunek JEŻELI(). Jest to wartość 12 w wierszu 3 (kolumna 13 minus 1; patrz wiersz pomocniczy na rysunku ③):
{= JEŻELI BŁĄD (INDEKS ($B$1:$M$1;MAKS (JEŻELI (B2: M2 "", KOLUMNA (B2: M2) -1, -1)));"")}
Tę wartość MAX przekazujesz do funkcji INDEX (). Odpowiedni miesiąc kalendarzowy jest następnie odczytywany w wierszu 1. Obszar danych funkcji INDEX () to obszar $ B $ 1: $ M $ 1. Przekazana wartość MAX - w przykładzie 12 - oznacza dwunastą wartość na liście, czyli Grudzień za miesiąc grudzień:
{= JEŚLI BŁĄD(INDEKS ($ B $ 1: $ M $ 1; MAX (JEŻELI (B2: M2 ""; KOLUMNA (B2: M2) -1; -1)));"")}
Jeśli wszystkie komórki w zakresie miesięcy tabeli są puste, największą wartością jest -1 (patrz warunek JEŻELI () na początku). Jeśli wartość -1 jest przekazywana do funkcji INDEX (), nieuchronnie prowadzi to do wartości błędu, ponieważ obszar listy funkcji INDEX () zawiera tylko dwanaście wpisów i dlatego nie może znaleźć wpisu -1. Tę wartość błędu można przechwycić za pomocą funkcji IFERROR () i zamiast tego zwrócić pusty ciąg. W przykładzie tak jest w przypadku produktu C w wierszu 4, ponieważ nie można było więcej zawrzeć umowy na ten produkt:
{=JEŻELIBŁĄD(INDEKS ($ B $ 1: $ M $ 1; MAX (JEŻELI (B2: M2 "", KOLUMNA (B2: M2) -1, -1)));"")}
Ponieważ jest to formuła tablicowa, wprowadź formułę za pomocą kombinacji klawiszy Ctrl + Shift + Enter.
