Funkcija ISOMITTED u Microsoft Excelu: Potpuni vodič

Ako radite sa Napredne lambda funkcije u ExceluPrije ili kasnije, susrećete se s klasičnim problemom: kako utvrditi je li argument zaista poslan ili je ostavljen prazan? Do nedavno nije postojao čist način za to, pa ste morali pribjeći ne baš elegantnim trikovima. Dolaskom ISOMITTED u Microsoft ExceluOva glavobolja se rješava na direktan, moćan i vrlo jednostavan način integriranja u vaše prilagođene formule.

U ovom članku ćemo detaljno pogledati Šta ISOMITTED radi i kako se koristi unutar LAMBDA-e?Naučit ćete koje su njegove praktične nijanse (uključujući razliku između preskočenog argumenta i prazne ćelije) i vidjeti nekoliko primjera iz stvarnog svijeta usmjerenih na poslovanje, automatizaciju i složene modele podataka. Također ćete vidjeti kako se ova funkcija uklapa u ekosistem LAMBDA informacije i funkcije pomoći koje Excel ima i kako alati poput ChatGPT za Excel Mogu se integrirati, tako da možete početi tretirati svoje proračunske tablice gotovo kao mali programski jezik.

Šta je funkcija ISOMITTED u programu Microsoft Excel?

Funkcija IZOMITTED Dio je Excelove grupe informacijskih funkcija i posebno je dizajniran za rad unutar LAMBDA izraziNjegov cilj je vrlo specifičan: provjeriti da li je parametar LAMBDA funkcije izostavljen prilikom pozivanja funkcije i vraćanja rezultata. REAL o FAKE po potrebi.

Jednostavno rečeno, ISOMITTED odgovara na pitanje: "Je li korisnik popunio ovaj argument ili ga je preskočio?"Ovo vam omogućava da kreirate prilagođene funkcije sa opcionim argumentima koji se automatski prilagođavaju, sa zadanim vrijednostima kada nedostaju informacije ili sa alternativnim ponašanjem kada se podaci dostave.

Sintaksa i osnovno ponašanje ISOMITTED-a

Sintaksa funkcije je izuzetno jednostavna, iako ima jednu ključnu nijansu: Radi samo unutar LAMBDA i primijenjeno na parametre te LAMBDA funkcije.

=ISOMITTED(argument)

U ovoj sintaksi, element argument predstavlja naziv parametra LAMBDA funkcije koju želite provjeriti. Rezultat će biti:

• REAL ako je argument izostavljen prilikom pozivanja LAMBDA funkcije.
• FAKE ako je argument naveden (čak i ako pokazuje na praznu ćeliju).

Važno je da to shvatite ISOMITTED ne provjerava sadržaj ćelijeali da li je sam argument proslijeđen ili ne. Na primjer, ako pozovete LAMBDA funkciju kao =MojaFunkcija(A1), parametar se računa kao "prisutan" iako A1 je prazanStoga će ISOMITTED vratiti FALSE. Da bi se uzeli u obzir scenariji praznih ćelija, preporučljivo je kombinirati ga s dodatnom provjerom tipa argument="" unutar a OR.

Opcionalni argumenti u LAMBDA i ISOMITTED

Da bi ISOMITTED zaista zablistao, bitno je iskoristiti mogućnost definiranja Opcionalni parametri u LAMBDAExcel vam omogućava da označite argument kao opcionalan tako što ćete ga uključiti u uglaste zagrade u definiciji LAMBDA funkcije, što dovodi do mnogo fleksibilnijih dizajna.

Na primjer, prilikom kreiranja prilagođene uloge u Upravitelju imena, možete napisati nešto poput:

=LAMBDA(x, , …izračunavanja…)

U ovom slučaju, y To je opcionalni parametar. Zahvaljujući tome, ISOMITTED može ispravno procijeniti da li ga je korisnik odabrao specificirati ili ga je izostavio prilikom pozivanja funkcije. Ovakav tip dizajna omogućava da jedna LAMBDA funkcija pokrije nekoliko scenarija bez narušavanja koherentnosti formula u kolonama.

Osnovni primjer: otkrivanje nedostajućeg argumenta i vraćanje teksta

Vrlo jednostavna upotreba ISOMITTED-a je provjera da li parametar nedostaje u LAMBDA signalu i, ako jeste, vrati opisni niz koji obavještava korisnika.

Zamislite LAMBDA funkciju sa dva argumenta, gdje se drugi može izostaviti. Vrlo tipična struktura bi bila:

=LAMBDA(x, y, AKO(ISOMITTED(y), "Nedostaje drugi argument", x + y))(1,)

U ovom pozivu, drugi argument je eksplicitno ostavljen prazan, tako da ISOMITTED vraća REAL a rezultat formule je tekstualna poruka „Nedostaje drugi argument“. Ako je uključen u sljedeći poziv y, zbir x+y Radi normalno.

Ovaj obrazac se može lako generalizirati: kada pomoću ISOMITTED-a otkrijete da nedostaje ključni dio podataka, možete vratiti jasno i čitljivo obavještenje umjesto obmanjujućeg broja ili netačnog izračuna, što znatno olakšava održavanje i reviziju tabele.

Funkcije ISOMITTED i LAMBDA u praksi

Funkcija ISOMITTED namijenjena je za korištenje kao integralni dio LAMBDA i njen ekosistemNema smisla koristiti je kao izoliranu formulu u ćeliji, jer je njena prava svrha kontrola internog ponašanja prilagođenih funkcija koje definirate u Upravitelju imena ili koje direktno ugnijezdite u formule.

U poslovnom okruženju, ovo je posebno korisno za kreiranje funkcije za višekratnu upotrebu s opcionalnim parametrimaBez potrebe za ponovljenim repliciranjem iste logike s minimalnim varijacijama, jedna dobro dizajnirana LAMBDA funkcija, s ISOMITTED provjerom koji argumenti stižu u svakom pozivu, može zamijeniti nekoliko kolona tradicionalnih formula.

Kombiniranje ISOMITTED-a s provjerama praznih ćelija

Jedan detalj koji često iznenađuje je da korisnik mnogo puta zapravo ne izostavi argument, već ga popuni referencom na prazna ćelijaIz Excelove perspektive, to znači da je argument naveden, tako da će ISOMITTED vratiti FALSE čak i ako je sadržaj "ništa".

Da bi se pokrili oba slučaja (izostavljeni parametar i prazna referenca), preporučuje se kombinovanje ISOMITTED-a sa direktnim poređenjem sa praznim tekstom, na primjer:

AKO(ILI(ISOMITTED(naziv), naziv=""), ...zadana vrijednost..., ...standardni izračun...)

Ovaj obrazac osigurava da se vaša LAMBDA funkcija ponaša konzistentno bez obzira da li korisnik piše funkciju bez argumenata u zagradama ili pokazuje na polje tabele koje je prazno u tom određenom redu.

Primjer „Zdravo svijete“: Prilagođena funkcija GreetUser

Da biste vidjeli vrlo jednostavan i vizualan primjer, zamislite tabelu u kojoj kolona A sadrži imena ljudi, a kolona B prikazuje pozdrav poput "Zdravo, Marta". Ako u redu nema imena, želite da se prikaže "Zdravo, gost".

Možete kreirati LAMBDA funkciju pod nazivom, na primjer, PozdraviKorisnika, u Upravitelju imena, s prvom verzijom poput ove:

=LAMBDA(, AKO(ISOMITTED(ime), "Zdravo, Goste", "Zdravo, " i ime))

Ako jednostavno stavite u ćeliju =PozdravKorisniku()Funkcija će detektovati nedostajući argument i vratiti generički pozdrav. Međutim, ako koristite nešto ovakvo unutar strukturirane tabele =PozdravKorisniku() i u određenom redu ćelija s imenom je prazna, ISOMITTED(ime) Ispostavit će se da je NETAČNO jer je argument usvojen, iako referenca ukazuje na prazninu.

Da bi se osiguralo očekivano ponašanje u svim slučajevima, robusnije je definirati LAMBDA na sljedeći način:

=LAMBDA(, AKO(ILI(ISOMITTED(ime), ime=""), "Zdravo, Goste", "Zdravo, " i ime))

Na ovaj način, ista funkcija pokriva i scenario pozivanja bez argumenata i scenario referenciranja prazne ćelije, održavajući jedinstvena, centralizovana logika.

Izbjegavajte ručne "zakrpe" i održavajte konzistentne formule

U velikim proračunskim tablicama to je vrlo primamljivo ispraviti red ručno Kada postoji poseban slučaj: odete na problematičnu ćeliju, uredite samo tu formulu i gotovi ste. Problem je što ove lokalizovane "zakrpe" postaju zamke koje je teško otkriti kako radna sveska raste.

Svaki put kada promijenite cjelokupnu logiku u koloni, morate zapamtiti koji redovi sadrže različite formule, što je praktično nemoguće kada imate posla sa stotinama zapisa. Nadalje, jednostavno prevlačenje prema dolje iz standardnog reda može uništi te izuzetke a da toga niste ni svjesni, što stvara neslaganja koja je teško pregledati.

Filozofija ISOMITTED-a i LAMBDA-e ide upravo u suprotnom smjeru: umjesto kršenja formule u određenim slučajevima, Učiš formulu da se prilagodi do tih slučajeva putem opcionalnih parametara i dobro definiranih internih logičkih grana.

Primjer 1 iz stvarnog svijeta: postavljanje zadanih vrijednosti u numeričkim proračunima

Jedna od najzanimljivijih upotreba ISOMITTED-a je kada imate proračun koji, po zadanim postavkama, slijedi standardno pravilo (na primjer, vrstu fiksni porez), ali u nekim slučajevima potrebno je da korisnik bude u mogućnosti da navede alternativnu vrijednost.

Zamislite tabelu s proizvodima gdje je većina oporezovana 8% PDV-aosim nekih specifičnih stavki koje imaju drugačiji tip. Možete dizajnirati LAMBDA pod nazivom, na primjer, Dodaj porezsa ovom shemom:

=LAMBDA(cijena; ; AKO(ILI(ISOMITTED(stopa); stopa=""); cijena * 1,08; cijena * (1 + stopa)))

U vašoj tabeli biste imali kolonu sa Cijena i još jedan opcionalni Poništavanje poreza (za alternativni tip poreza). Formula za kolonu ukupnog iznosa može biti:

=DodajPorez(, )

Kada je polje TaxOverride prazno, OR će vratiti TRUE jer će ISOMITTED detektovati da je argument izostavljen ili zato što će referenca biti prazan niz, a formula će primijeniti standardni multiplikator 1,08Ako unesete, na primjer, 0,1 u TaxOverride u određenom redu, logika će se automatski prebaciti na korištenje cijena * (1 + stopa) sa tih konkretnih 10%.

Na ovaj način, kapsulirate svo fiskalno ponašanje u jednu LAMBDA funkciju, izbjegavajući popunjavanje ćelija sa ugniježđene ponavljajuće IF naredbe i značajno smanjujući rizik ljudske greške.

Slučaj upotrebe iz stvarnog svijeta 2: potpuno promijeniti ponašanje ovisno o tome ima li podataka ili ne.

ISOMITTED ne služi samo za odabir zadane vrijednosti; može i aktivirati radikalno različiti blokovi logike ovisno o tome postoji li opcionalni argument ili ne. Ovo je vrlo uobičajeno pri radu s datumima procesa ili rokovima isporuke.

Zamislite tabelu narudžbi u kojoj imate kolonu Datum završetka s datumom završetka narudžbe i još jednom opcionalnom kolonom Nadjačavanje međuspremnika s mogućnošću dodavanja dodatnih dana za složenije narudžbe.

Možete definirati LAMBDA funkciju pod nazivom, na primjer, GetShipDate sa ovom strukturom:

=LAMBDA(f_fin; , AKO(ILI(ISOMITTED(bafer); bafer=»»), f_fin, f_fin + bafer))

U koloni Datum isporuke (ShipDate) jednostavno trebate napisati:

=UzmiDatumIsporuke(, )

U normalnim narudžbama, gdje je BufferOverride prazan, funkcija će vratiti jednostavno krajnji datumMeđutim, u onim zapisima gdje unesete određeni broj dana margine, funkcija će promijeniti svoje ponašanje i dodati tu vrijednost osnovnom datumu.

Zanimljivo je da, ako se vaša poslovna pravila kasnije promijene (na primjer, želite uzeti u obzir samo radne dane umjesto kalendarskih), morat ćete samo izmijeniti internu logiku funkcije LAMBDA, a Excel će u skladu s tim ažurirati ponašanje. svi redovi automatski.

ISOMITTED unutar ekosistema informacionih funkcija

ISOMITTED se integrira s ostatkom funkcija grupe Informacije Excel funkcije, od kojih mnoge vraćaju i TRUE ili FALSE na osnovu različitih vrsta provjera. Neke od najsrodnijih su:

• BIJELA: označava da li je ćelija prazna.
• ESERR: provjerava da li je vrijednost greška, osim #N/A.
• GREŠKA IZDAVAČA: detektuje bilo koju vrijednost greške.
• BROJ, ESTEKST, LOGIČNO JE: oni provjeravaju tip podataka.
• ESREF: označava da li je vrijednost validna referenca.
• VRSTA GREŠKE: vraća numerički kod prema vrsti greške.
• MOMAK: označava opći tip podataka (broj, tekst, itd.).

Dok ove funkcije rade na ćelije i vrijednostiISOMITTED se fokusira isključivo na svijet LAMBDA parametriNjihovo kombinovanje otvara vrata veoma ekspresivnim formulama: možete otkriti da li je argument izostavljen, da li je ulazna vrednost numerička, da li postoje greške, da li je ćelija prazna itd., i prilagoditi logiku sa nivoom kontrole veoma sličnim onome u tradicionalnom programskom jeziku.

Komplementarne funkcije za maksimalno iskorištavanje LAMBDA algoritma

Excel se nije zaustavio samo na LAMBDA i ISOMITTED; u novijim verzijama pojavio se niz drugih funkcija. LAMBDA-ine "prateće" funkcije koji se vrlo dobro integriraju jedni s drugima i omogućavaju vam da direktno na listu izgradite složena rješenja:

• BYROW y BYCOL: prolazi kroz matricu po redovima ili kolonama primjenjujući LAMBDA funkciju na svaku od njih i vraćajući matricu s rezultatima.
• NAPRAVITE EARRAY: generira dinamički dimenzionirane izračunate matrice iz LAMBDA izraza.
• MAPIRANJE (MAP): projektuje jednu ili više matrica u novu primjenom LAMBDA funkcije na svaku kombinaciju elemenata.
• SMANJITE: akumulira jedan rezultat smanjenjem niza pomoću LAMBDA funkcije koja kombinuje akumulator sa svakom vrijednošću.
• SCANSlično kao REDUCE, ali vraća sve međuvrijednosti akumulacije kao matrice.

U ovom kontekstu, ISOMITTED postaje ključan prilikom dizajniranja LAMBDA senzori za višekratnu upotrebu s opcionalnim parametrima koje zatim koristite unutar BYROW, BYCOL ili MAKEARRAY. Mogućnost razlikovanja unutar LAMBDA krive koji su argumenti proslijeđeni, a koji nisu, čini razliku između rigidnog i fleksibilnog rješenja.

Odnos ISOMITTED-a prema drugim funkcionalnim kategorijama

Iako je ISOMITTED visoko specijalizirana funkcija, ona se uklapa u ogroman katalog Excel kategorija koje se često kombiniraju u stvarnim projektima: logika, tekst, matematika, finansije, statistika, pretraživanje i reference, iz baza podataka, datuma i vremena, inženjerstva, weba ili čak povezanih s tržišnim podacima i automatizacijom.

Mnoge od ovih kategorija uključuju funkcije koje također vraćaju logičke ili kontrolne vrijednosti i mogu biti dio iste formule u kojoj koristite LAMBDA i ISOMITTED. Neki relevantni primjeri su:

• Logičke funkcije kao što su Y, O, NE, SI, DA.GREŠKA, DA. POSTAVIšto vam omogućava da granate logiku kada otkrijete da je argument izostavljen.
• Funkcije pretraživanja kao što su SEARCHX, MATCHX, INDEX, OFFSETkoji se često koriste unutar LAMBDA za kreiranje prilagođenih upita za raspone ili tabele.
• Tekstualne funkcije kao što su LIJEVO, DESNO, EKSTRAKT, CONCAT, UNICHAINS, korisno kada vaša LAMBDA funkcija manipuliše stringovima i, u zavisnosti od izostavljenog argumenta, birate jedan ili drugi format.
• Matrične i sortirne funkcije kao što su FILTER, NARUČI, SORTIRAJ PO, UNICOSkoji se odlično kombinuju sa BYROW, BYCOL i LAMBDA.

Opšta ideja je da postepeno usvojite način rada u kojem vaše formule prelaze iz statičkih blokova u male parametrizabilne funkcije, pri čemu ISOMITTED djeluje kao čuvar tih opcionalnih parametara.

Kada koristiti ISOMITTED u Excel modelima

Postoje određeni obrasci korištenja gdje ISOMITTED savršeno pristaje, i vrijedi imati ovo na umu:

• Kada prilagođena funkcija ima razumne zadane vrijednosti (na primjer, standardne stope, uobičajeni uslovi, zadani formati teksta), ali želite dozvoliti prilagođavanja od slučaja do slučaja.
• Kada trebate održavati jedna homogena formula za cijelu kolonu, iako postoje linije sa posebnim zahtjevima.
• Kada želite izbjeći nečitljive formule pune Ugniježđeni IF izrazi i ponovljene provjere u mnogim ćelijama.
• Kada počnete tretirati Excel kao lagano programsko okruženjeponovna upotreba LAMBDA funkcija na nekoliko listova ili čak u nekoliko knjiga.

U svim ovim scenarijima, ISOMITTED djeluje kao nedostajući dio koji jasno razlikuje kada korisnik pruža dodatne informacije, a kada ne, bez potrebe za pribjegavanjem trikovima sa skrivenim ćelijama ili međukolonama.

Uobičajene greške i kako otkloniti greške u LAMBDA-i pomoću ISOMITTED-a

Iako je ISOMITTED jednostavan, postoje neke uobičajene greške koje se mogu napraviti na početku. Korisno ih je identificirati kako biste izbjegli gubljenje vremena na otklanjanje grešaka:

• Koristite ga izvan LAMBDA-eAko pokušate direktno upisati ISOMITTED u ćeliju bez da ste unutar LAMBDA izraza, to vam neće donijeti nikakvu korist, jer nema LAMBDA parametar na koji bi se moglo reagovati.
• Zbunjujući izostavljeni argument s praznom ćelijomKao što smo vidjeli, prosljeđivanje reference na praznu ćeliju nije isto što i ostavljanje argumenta neodređenim. Ako želite pokriti obje situacije, morat ćete dodati dodatnu provjeru. argument="".
• Zaboravljanje označavanja parametra kao opcionalnogAko argument ne stavite u uglaste zagrade u definiciji LAMBDA funkcije, Excel će ga tretirati kao obavezan i ponašanje funkcije ISOMITTED možda neće biti onako kako očekujete.
• Ne testirajte granične slučajevePrilikom dizajniranja složene LAMBDA funkcije, korisno je testirati pozive sa svim argumentima, s nekim izostavljenim, s praznim referencama i s različitim tipovima podataka, kombinirajući ISOMITTED s funkcijama kao što su ISNUMBER ili ISERROR.

Odvajanje nekoliko minuta za pokretanje ovih testova s ​​nekoliko uzoraka podataka uštedjet će vam mnogo glavobolja kasnije kada vaša funkcija počne biti korištena u opsežne tabele ili kritični modeli za posao.

Zahvaljujući ISOMITTED-u, Excel dobija vrijedan sloj inteligencije prilikom kreiranja prilagođenih funkcija: možete precizno razlikovati prisutne od izostavljenih argumenata, ponuditi razumne zadane vrijednosti, logiku grananja na osnovu dostupnih informacija i održavati konzistentne i lako upravljive formule čak i u velikim radnim sveskama. U kombinaciji s LAMBDA, pomoćnim funkcijama poput BYROW, REDUCE i MAP, te drugim informacijskim funkcijama, ovaj alat transformira vaše proračunske tablice u mnogo fleksibilnija, moćnija rješenja, bliža zaista prilagođenoj aplikaciji, a sve unutar okruženja koje već koristite svaki dan.