Kas teate toredat nippi, mille abil luua viide veeru pealkirjale rea viimases tühjas lahtris? Parim: te ei vaja abijooni ega veerge. Nii lihtne see ongi:
Ühendage neli funktsiooni IFERROR (), INDEX (), MAX () ja IF ()
Müügijuht saadab teile nimekirja lepingutest, mis sõlmitakse järk -järgult toodete kohta, mille tootmine lõpetatakse ①. Peaksite kasutama veerus N olevat valemit, et määrata iga toote viimane müügikuu - ilma täiendavate ridade või veergudeta. Kui rohkem lepinguid pole sõlmitud, sisestage veergu N tühi lahter.
See esmapilgul banaalne näide osutub kõvaks pähkliks ilma abijooni või -veerge kasutamata. nagu Excel harjutamiseks-Lugejad murravad pähklit! Lahtris on probleemide lahendamiseks järgmine massiivivalem N2 loodud:
{= IFERROR (INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; COLUMN (B2: M2) -1; -1))); "")}
Vaadake samm -sammult, kuidas see toimib
Alustage massiivivalemisse sisseehitatud tingimusega IF (): {= IFERROR (INDEX ($ B $ 1: $ M $ 1; MAX (KUI (B2: M2 "", VEERG (B2: M2) -1, -1)));"")}
Tingimus IF () loob massiivi valemisse fiktiivse abirea ja kontrollib lahtreid B2 kuni M2kas need on tühjad või mitte. Kui lahter on tühi, tagastatakse väärtus -1 muidu funktsiooni COLUMN () kaudu, vastava veeru number miinus väärtus 1.
Lahutamine 1 on valemis kohustuslik, kuna tabeli esimene veerg ei sisalda kuu nime, vaid toote nime. Allpool saate teada, kuidas kasutada funktsiooni INDEX () vastava kuu nime kuvamiseks, mis - kui te ei lahutanud 1 - kasutatud lisaveeru tõttu A. oleks täpselt ühe veeru võrra vale.
Kui kõik lahtrid vahemikus B2: M2 on tühjad, väärtus -1 (ilma müügita) loob vea, mida kasutame tühja lahtri esitamiseks. Aktiivset abiliini on näha joonisel ③ real 3.
Järgmises etapis loete välja suurima väärtuse funktsiooniga MAX (), milles IF () tingimus on pesastatud. See on väärtus 12 real 3 (veerg 13 miinus 1; vt abirida joonisel ③):
{= IFERROR (INDEX ($ B $ 1: $ M $ 1;MAX (KUI (B2: M2 "", VEERG (B2: M2) -1, -1)));"")}
Selle MAX väärtuse edastate funktsioonile INDEX (). Seejärel loetakse vastav kalendrikuu realt 1 välja. Funktsiooni INDEX () andmeala on ala $ B $ 1: $ M $ 1. Läbitud MAX väärtus - näites 12 - tähendab, et loendis on kaheteistkümnes väärtus, s.t. Dets detsembri kuu kohta:
{= IFERROR(INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; VEERG (B2: M2) -1; -1)));"")}
Kui tabeli kuuvahemiku kõik lahtrid on tühjad, on suurim väärtus -1 (vt IF () tingimust alguses). Kui väärtus -1 edastatakse funktsioonile INDEX (), toob see paratamatult kaasa veaväärtuse, kuna funktsiooni INDEX () loendi ala sisaldab ainult kaksteist kirjet ja seetõttu ei leia kirjet -1. Selle vea väärtuse saate funktsiooni IFERROR () abil ja tagastate selle asemel tühja stringi. Näites kehtib see rea C toote 4 kohta, kuna selle toote kohta ei saanud enam lepingut sõlmida:
{=IFERROR(INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 "", COLUMN (B2: M2) -1, -1)));"")}
Kuna see on massiivivalem, lõpetage valemi sisestamine klahvikombinatsiooniga Ctrl + Shift + Enter.
