Znate li sjajan trik stvaranja reference na naslov stupca u zadnjoj ćeliji reda koja nije prazna? Najbolje: Ne trebaju vam nikakvi pomoćni redovi ili stupci. Tako je jednostavno:
Kombinirajte četiri funkcije IFERROR (), INDEX (), MAX () i IF ()
Generalni direktor prodaje će vam poslati popis ugovora sklopljenih mjesečno za proizvode koji se ukidaju ①. Trebali biste koristiti formulu u stupcu N za navođenje posljednjeg mjeseca prodaje za svaki proizvod - bez ikakvih pomoćnih redaka ili stupaca. Ako nije sklopljen više ugovor, unesite praznu ćeliju u stupac N.
Ovaj primjer, na prvi pogled banalan, ispada kao tvrd orah bez upotrebe pomoćnih linija ili stupova. kao Excel za vježbu-Čitatelji popucaju maticu! Za rješavanje problema u ćeliji imamo sljedeću formulu niza N2 stvoreno ②:
{= IFERROR (INDEKS ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; COLUMN (B2: M2) -1; -1))); "")}
Korak po korak pogledajte kako to funkcionira
Počnite s IF () uvjetom ugrađenim u formulu niza: {= IFERROR (INDEKS ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 "", KOLONA (B2: M2) -1, -1)));"")}
Uvjet IF () stvara izmišljeni pomoćni redak u formuli niza i provjerava ima li ćelija B2 do M2bili oni prazni ili ne. Ako je ćelija prazna, vrijednost -1 se inače vraća putem funkcije COLUMN (), odgovarajući broj stupca minus vrijednost 1.
Oduzimanje 1 je potrebno u formuli jer prvi stupac u tablici ne sadrži naziv mjeseca, već naziv proizvoda. U nastavku ćete naučiti kako koristiti funkciju INDEX () za prikaz odgovarajućeg naziva mjeseca, koji - ako niste oduzeli 1 - zbog korištenja dodatnog stupca A. bilo bi netočno za točno jedan stupac.
Ako sve ćelije u rasponu B2: M2 su prazne, vrijednost -1 (nema prodaje proizvoda) stvara pogrešku koju koristimo za predstavljanje prazne ćelije. Aktivna pomoćna linija može se vidjeti na slici ③ u retku 3.
U sljedećem koraku očitavate najveću vrijednost pomoću funkcije MAX () u kojoj je ugniježđen uvjet IF (). Ovo je vrijednost 12 u retku 3 (stupac 13 minus 1; pogledajte pomoćni redak na slici ③):
{= IFERROR (INDEKS ($ B $ 1: $ M $ 1;MAX (IF (B2: M2 "", COLUMN (B2: M2) -1, -1)));"")}
Ovu vrijednost MAX prosljeđujete funkciji INDEX (). Odgovarajući kalendarski mjesec tada se čita u prvom retku. Područje podataka funkcije INDEX () je područje $ B $ 1: $ M $ 1. Proslijeđena MAX vrijednost - u primjeru 12 - znači da dvanaesta vrijednost na popisu, tj. Prosinca za mjesec prosinac:
{= POGREŠKA(INDEKS ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; KOLONA (B2: M2) -1; -1)));"")}
Ako su sve ćelije u mjesečnom rasponu tablice prazne, tada je najveća vrijednost -1 (vidi uvjet IF () na početku). Ako se vrijednost -1 prenese u funkciju INDEX (), to neizbježno dovodi do vrijednosti pogreške, budući da područje popisa funkcije INDEX () sadrži samo dvanaest unosa i stoga ne može pronaći unos -1. Tu vrijednost pogreške uhvatite pomoću funkcije IFERROR () i umjesto toga vratite prazan niz. U primjeru je to slučaj s proizvodom C u retku 4, jer se za ovaj proizvod više ne može zaključiti ugovor:
{=POGREŠAK(INDEKS ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 "", KOLONA (B2: M2) -1, -1)));"")}
Budući da se radi o formuli niza, dovršite unos formule kombinacijom tipki Ctrl + Shift + Enter.
