Ali poznate odličen trik pri ustvarjanju sklica na naslov stolpca v zadnji neprazni celici vrstice? Najboljše: ne potrebujete pomožnih vrstic ali stolpcev. Tako enostavno je:
Združite štiri funkcije IFERROR (), INDEX (), MAX () in IF ()
Direktor prodaje vam bo poslal seznam pogodb, sklenjenih na mesec za izdelke, ki se postopno ukinjajo ①. Za določitev zadnjega meseca prodaje za vsak izdelek - brez pomožnih vrstic ali stolpcev - uporabite formulo. Če ni sklenjenih več pogodb, v stolpec N vnesite prazno celico.
Ta primer, na prvi pogled banalen, se izkaže za trd oreh brez uporabe pomožnih črt ali stebrov. kot Excel za vadbo-Bralci pokajo oreh! Za reševanje problemov imamo v celici naslednjo formulo matrike N2 ustvarjeno ②:
{= IFERROR (INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; STOLPEC (B2: M2) -1; -1))); "")}}
Korak za korakom si oglejte, kako deluje
Začnite s pogojem IF (), vgrajenim v formulo matrike: {= IFERROR (INDEKS ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 "", STOLPEC (B2: M2) -1, -1)));"")}
Pogoj IF () ustvari izmišljeno pomožno vrstico v formuli matrike in preveri celice B2 dokler M2ali so te prazne ali ne. Če je celica prazna, se vrednost -1 sicer vrne s funkcijo COLUMN (), številka ustreznega stolpca minus vrednost 1.
Odštevanje 1 je potrebno v formuli, ker prvi stolpec v tabeli ne vsebuje imena meseca, ampak ime izdelka. Spodaj boste izvedeli, kako s funkcijo INDEX () prikazati ustrezno ime meseca, ki - če niste odšteli 1 - zaradi uporabljenega dodatnega stolpca A. bi bil napačen v točno enem stolpcu.
Če so vse celice v razponu B2: M2 so prazne, vrednost -1 (brez prodaje izdelkov) ustvari napako, ki jo uporabljamo za predstavitev prazne celice. Aktivno pomožno črto lahko vidite na sliki ③ v vrstici 3.
V naslednjem koraku preberete največjo vrednost s funkcijo MAX (), v kateri je pogoj IF () ugnezden. To je vrednost 12 v vrstici 3 (stolpec 13 minus 1; glej pomožno vrstico na sliki ③):
{= IFERROR (INDEKS ($ B $ 1: $ M $ 1;MAX (ČE (B2: M2 "", STOLPEC (B2: M2) -1, -1)));"")}
To vrednost MAX posredujete funkciji INDEX (). Ustrezni koledarski mesec se nato odčita v prvi vrstici. Področje podatkov funkcije INDEX () je območje $ B $ 1: $ M $ 1. Prenesena vrednost MAX - v primeru 12 - pomeni, da dvanajsta vrednost na seznamu, tj. Dec za mesec december:
{= NAPAKA(INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; STOLPEC (B2: M2) -1; -1)));"")}
Če so vse celice v mesečnem obsegu tabele prazne, je največja vrednost -1 (glej pogoj IF () na začetku). Če se vrednost -1 posreduje funkciji INDEX (), to neizogibno vodi do napake, saj območje seznama funkcije INDEX () vsebuje le dvanajst vnosov in zato ne more najti vnosa -1. To vrednost napake ujamete s funkcijo IFERROR () in namesto tega vrnete prazen niz. V primeru to velja za izdelek C v vrstici 4, ker za ta izdelek ni več mogoče skleniti pogodbe:
{=NAPAKA(INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 "", STOLPEC (B2: M2) -1, -1)));"")}
Ker je to matrična formula, dokončajte vnos formule s kombinacijo tipk Ctrl + Shift + Enter.
