Știți trucul ingenios de a crea o referință la un titlu de coloană din ultima celulă ne-goală a unui rând? Cel mai bun: nu aveți nevoie de linii sau coloane auxiliare. Este atât de ușor:
Combinați cele patru funcții IFERROR (), INDEX (), MAX () și IF ()
Directorul general în vânzări vă va trimite o listă a contractelor încheiate lunar pentru produsele care sunt eliminate treptat ①. Ar trebui să utilizați o formulă în coloana N pentru a specifica ultima lună de vânzări pentru fiecare produs - fără linii sau coloane auxiliare. Dacă nu s-au mai încheiat contracte, introduceți o celulă goală în coloana N.
Acest exemplu, banal la prima vedere, se dovedește a fi o piuliță dură de spart fără a folosi linii sau coloane auxiliare. la fel de Excel pentru practică-Cititorii sparge nuca! Avem următoarea formulă matrice în celulă pentru rezolvarea problemelor N2 creat ②:
{= IFERROR (INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; COLUMN (B2: M2) -1; -1))); "")}
Aruncați o privire la modul în care funcționează pas cu pas
Începeți cu condiția IF () încorporată în formula matricei: {= IFERROR (INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 "", COLUMN (B2: M2) -1, -1)));"")}
Condiția IF () creează o linie auxiliară fictivă în formula matricei și verifică celulele B2 pana cand M2dacă acestea sunt goale sau nu. Dacă o celulă este goală, valoarea -1 este returnată altfel prin funcția COLUMN (), numărul coloanei respective minus valoarea 1.
Scăderea lui 1 este necesară în formulă, deoarece prima coloană din tabel nu conține numele lunii, ci numele produsului. Mai jos veți afla cum să utilizați funcția INDEX () pentru a afișa numele lunii corespunzătoare, care - dacă nu ați scăzut 1 - din cauza coloanei suplimentare utilizate A. ar fi incorect cu exact o coloană.
Dacă toate celulele din interval B2: M2 sunt goale, valoarea -1 (fără vânzări de produse) creează o eroare pe care o folosim pentru a reprezenta o celulă goală. Linia auxiliară activă poate fi văzută în Fig. ③ în linia 3.
În pasul următor citiți cea mai mare valoare cu funcția MAX (), în care este imbricată condiția IF (). Aceasta este valoarea 12 din linia 3 (coloana 13 minus 1; vezi linia auxiliară din figura ③):
{= IFERROR (INDEX ($ B $ 1: $ M $ 1;MAX (IF (B2: M2 "", COLUMN (B2: M2) -1, -1)));"")}
Treceți această valoare MAX funcției INDEX (). Luna calendaristică corespunzătoare este apoi citită în rândul 1. Zona de date a funcției INDEX () este zona $ B $ 1: $ M $ 1. Valoarea MAX trecută - în exemplul 12 - înseamnă că a douăsprezecea valoare din listă, adică Dec pentru luna decembrie:
{= IFERROR(INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; COLUMN (B2: M2) -1; -1)));"")}
Dacă toate celulele din intervalul lunar al tabelului sunt goale, atunci cea mai mare valoare este -1 (vezi condiția IF () la început). Dacă valoarea -1 este trecută la funcția INDEX (), aceasta conduce inevitabil la o valoare de eroare, deoarece zona de listă a funcției INDEX () conține doar doisprezece intrări și, prin urmare, nu poate găsi intrarea -1. Prindeți această valoare de eroare cu funcția IFERROR () și în schimb returnați un șir gol. În exemplu, acesta este cazul pentru produsul C din linia 4, deoarece nu s-a mai putut încheia un contract pentru acest produs:
{=IFERROR(INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 "", COLUMN (B2: M2) -1, -1)));"")}
Deoarece aceasta este o formulă matrice, completați intrarea formulei cu combinația de taste Ctrl + Shift + Enter.
