Poznáte šikovný trik, ako vytvoriť odkaz na záhlavie stĺpca v poslednej prázdnej bunke v riadku? Najlepšie: Nepotrebujete žiadne pomocné riadky ani stĺpce. Je to také ľahké:
Skombinujte štyri funkcie IFERROR (), INDEX (), MAX () a IF ()
Generálny riaditeľ pre predaj vám zašle zoznam zmlúv uzatvorených mesačne na produkty, ktoré sa postupne rušia ①. Na zadanie posledného mesiaca predaja pre každý výrobok by ste mali použiť vzorec v stĺpci N - bez pomocných riadkov alebo stĺpcov. Ak už neboli uzatvorené žiadne ďalšie zmluvy, zadajte prázdnu bunku do stĺpca N.
Tento banálny príklad na prvý pohľad sa ukazuje ako ťažký oriešok bez použitia pomocných čiar alebo stĺpcov. ako Excel pre prax-Čítačky lúskajú orech! V bunke na riešenie problémov máme nasledujúci vzorec poľa N2 vytvorené ②:
{= IFERROR (INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; COLUMN (B2: M2) -1; -1))); "")}
Pozrite sa, ako to krok za krokom funguje
Začnite s podmienkou IF () zabudovanou do vzorca poľa: {= IFERROR (INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 "", STĹPEC (B2: M2) -1, -1)));"")}
Podmienka IF () vytvorí vo vzorci poľa fiktívny pomocný riadok a vyhľadá bunky B2 až M2či sú prázdne alebo nie. Ak je bunka prázdna, hodnota -1 sa inak vráti pomocou funkcie COLUMN (), číslo príslušného stĺpca mínus hodnota 1.
Vo vzorci je potrebné odčítať 1, pretože prvý stĺpec v tabuľke neobsahuje názov mesiaca, ale názov produktu. Ďalej sa dozviete, ako používať funkciu INDEX () na zobrazenie názvu príslušného mesiaca, ktorý - ak ste neodpočítali 1 - kvôli použitému dodatočnému stĺpcu A. by bolo nesprávne presne o jeden stĺpec.
Ak sú všetky bunky v rozsahu B2: M2 sú prázdne, hodnota -1 (žiadny predaj produktu) vytvára chybu, ktorú používame na reprezentáciu prázdnej bunky. Aktívnu pomocnú čiaru je možné vidieť na obr. ③ v riadku 3.
V nasledujúcom kroku odčítate najväčšiu hodnotu s funkciou MAX (), v ktorej je vnorená podmienka IF (). Toto je hodnota 12 v riadku 3 (stĺpec 13 mínus 1; pozri pomocný riadok na obrázku ③):
{= IFERROR (INDEX ($ B $ 1: $ M $ 1;MAX (IF (B2: M2 "", STĹPEC (B2: M2) -1, -1)));"")}
Túto MAX hodnotu odovzdáte funkcii INDEX (). Príslušný kalendárny mesiac sa potom odčíta v riadku 1. Dátová oblasť funkcie INDEX () je oblasť $ B $ 1: $ M $ 1. Odovzdaná MAX hodnota - v príklade 12 - znamená, že dvanásta hodnota v zozname, t.j. Dec za december:
{= IFERROR(INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; COLUMN (B2: M2) -1; -1)));"")}
Ak sú všetky bunky v rozsahu mesiacov v tabuľke prázdne, potom je najväčšia hodnota -1 (pozri podmienku IF () na začiatku). Ak je do funkcie INDEX () odovzdaná hodnota -1, bude to mať za následok chybovú hodnotu, pretože oblasť zoznamu funkcie INDEX () obsahuje iba dvanásť záznamov, a preto nemôže nájsť položku -1. Túto chybovú hodnotu zachytíte pomocou funkcie IFERROR () a namiesto toho vrátite prázdny reťazec. V tomto prípade je to prípad produktu C v riadku 4, pretože pre tento produkt nebolo možné uzavrieť žiadnu ďalšiu zmluvu:
{=IFERROR(INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 "", COLUMN (B2: M2) -1, -1)));"")}
Pretože sa jedná o vzorec poľa, doplňte zadanie vzorca pomocou kombinácie klávesov Ctrl + Shift + Enter.