Znáte ten šikovný trik, jak vytvořit odkaz na záhlaví sloupce v poslední prázdné buňce řádku? Nejlepší: Nepotřebujete žádné pomocné řádky ani sloupce. Je to tak snadné:
Zkombinujte čtyři funkce IFERROR (), INDEX (), MAX () a IF ()
Generální ředitel pro prodej vám zašle seznam smluv uzavřených měsíčně na produkty, které se postupně vyřazují ①. K určení posledního měsíce prodeje pro každý produkt byste měli použít vzorec ve sloupci N - bez pomocných řádků nebo sloupců. Pokud nebyly uzavřeny žádné další smlouvy, zadejte prázdnou buňku do sloupce N.
Tento příklad, na první pohled banální, se ukazuje jako těžký oříšek bez použití pomocných čar nebo sloupců. tak jako Excel pro praxi-Čtenáři rozluští matici! V buňce pro řešení problémů máme následující vzorec pole N2 vytvořeno ②:
{= IFERROR (INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; COLUMN (B2: M2) -1; -1))); "")}
Podívejte se, jak to krok za krokem funguje
Začněte s podmínkou IF () zabudovanou do vzorce pole: {= IFERROR (INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 "", SLOUPEC (B2: M2) -1, -1)));"")}
Podmínka IF () vytvoří ve vzorci pole fiktivní pomocnou čáru a zkontroluje buňky B2 dokud M2zda jsou prázdné nebo ne. Pokud je buňka prázdná, hodnota -1 se jinak vrátí pomocí funkce COLUMN (), číslo příslušného sloupce minus hodnota 1.
Ve vzorci je vyžadováno odečtení 1, protože první sloupec v tabulce neobsahuje název měsíce, ale název produktu. Níže se dozvíte, jak pomocí funkce INDEX () zobrazit název příslušného měsíce, který - pokud jste neodečetli 1 - kvůli použitému dalšímu sloupci A. by bylo nesprávné přesně o jeden sloupec.
Pokud jsou všechny buňky v rozsahu B2: M2 jsou prázdné, hodnota -1 (žádné prodeje produktů) vytvoří chybu, kterou používáme k reprezentaci prázdné buňky. Aktivní pomocnou čáru lze vidět na obr. ③ v řádku 3.
V dalším kroku načtete největší hodnotu s funkcí MAX (), ve které je vnořena podmínka IF (). Toto je hodnota 12 v řádku 3 (sloupec 13 minus 1; viz pomocný řádek na obrázku ③):
{= IFERROR (INDEX ($ B $ 1: $ M $ 1;MAX (IF (B2: M2 "", SLOUPEC (B2: M2) -1, -1)));"")}
Tuto MAX hodnotu předáte funkci INDEX (). V řádku 1 se poté načte odpovídající kalendářní měsíc. Oblast dat funkce INDEX () je oblast $ B $ 1: $ M $ 1. Předaná hodnota MAX - v příkladu 12 - znamená, že dvanáctá hodnota v seznamu, tj. Prosince za měsíc prosinec:
{= IFERROR(INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; COLUMN (B2: M2) -1; -1)));"")}
Pokud jsou všechny buňky v rozsahu měsíců v tabulce prázdné, pak je největší hodnota -1 (viz podmínka IF () na začátku). Pokud je do funkce INDEX () předána hodnota -1, nevyhnutelně to vede k chybové hodnotě, protože oblast seznamu funkce INDEX () obsahuje pouze dvanáct položek, a proto nemůže najít položku -1. Tuto chybovou hodnotu zachytíte pomocí funkce IFERROR () a místo toho vrátíte prázdný řetězec. V příkladu je to případ produktu C v řádku 4, protože pro tento produkt již nelze uzavřít žádnou smlouvu:
{=IFERROR(INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 "", COLUMN (B2: M2) -1, -1)));"")}
Protože se jedná o maticový vzorec, doplňte zadání vzorce pomocí kombinace kláves Ctrl + Shift + Enter.
