Ar žinote puikų triuką, kaip sukurti nuorodą į stulpelio antraštę paskutinėje tuščioje eilutės ląstelėje? Geriausia: jums nereikia jokių pagalbinių eilučių ar stulpelių. Tai taip paprasta:
Sujunkite keturias funkcijas IFERROR (), INDEX (), MAX () ir IF ()
Pardavimų vykdomasis direktorius atsiųs jums per mėnesį sudaromų sutarčių dėl produktų, kurių atsisakoma, sąrašą. Turėtumėte naudoti formulę N stulpelyje, kad nurodytumėte paskutinį kiekvieno produkto pardavimo mėnesį be jokių pagalbinių eilučių ar stulpelių. Jei daugiau sutarčių nesudaryta, įveskite tuščią langelį į N stulpelį.
Šis iš pirmo žvilgsnio banalus pavyzdys pasirodo esąs kietas riešutas, nenaudojant pagalbinių linijų ar stulpelių. kaip „Excel“ praktikai-Skaitytojai laužė riešutą! Ląstelėje turime šią masyvo formulę problemoms spręsti N2 sukurta:
{= IFERROR (INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; COLUMN (B2: M2) -1; -1))); "")}
Žiūrėkite žingsnis po žingsnio, kaip tai veikia
Pradėkite nuo IF () sąlygos, įtrauktos į masyvo formulę: {= IFERROR (INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 "", STulpelis (B2: M2) -1, -1)));"")}
Sąlyga IF () masyvo formulėje sukuria fiktyvią pagalbinę eilutę ir patikrina langelius B2 iki M2ar jie tušti, ar ne. Jei langelis tuščias, reikšmė -1 grąžinama per funkciją COLUMN (), atitinkamo stulpelio numeris atėmus 1.
Formulėje reikia atimti 1, nes pirmame lentelės stulpelyje yra ne mėnesio, o produkto pavadinimas. Žemiau sužinosite, kaip naudoti funkciją INDEX () rodyti atitinkamą mėnesio pavadinimą, kuris - jei neatimėte 1 - dėl papildomo naudojamo stulpelio A. būtų neteisinga tiksliai vienu stulpeliu.
Jei visos diapazono ląstelės B2: M2 yra tuščios, reikšmė -1 (neparduodama produkto) sukuria klaidą, kurią naudojame tuščiam langeliui pavaizduoti. Aktyvią pagalbinę liniją galima pamatyti line pav. 3 eilutėje.
Kitame žingsnyje perskaitysite didžiausią reikšmę su funkcija MAX (), kurioje yra įdėta IF () sąlyga. Tai yra vertė 3 eilutėje (13 stulpelis minus 1; žr. Pagalbinę eilutę Figure paveiksle):
{= IFERROR (INDEX ($ B $ 1: $ M $ 1;MAX (IF (B2: M2 "", COLUMN (B2: M2) -1, -1)));"")}
Šią MAX reikšmę perduodate funkcijai INDEX (). Tada atitinkamas kalendorinis mėnuo skaitomas 1 eilutėje. Funkcijos INDEX () duomenų sritis yra sritis $ B $ 1: $ M $ 1. Praėjusi MAX reikšmė - 12 pavyzdyje - reiškia, kad dvyliktoji sąrašo vertė, t.y. Gruodžio mėn gruodžio mėnesiui:
{= IFERROR(INDEKSAS ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; COLUMN (B2: M2) -1; -1)));"")}
Jei visos lentelės mėnesio diapazono ląstelės yra tuščios, tada didžiausia reikšmė yra -1 (žr. IF () sąlygą pradžioje). Jei reikšmė -1 perduodama funkcijai INDEX (), tai neišvengiamai lemia klaidos reikšmę, nes funkcijos INDEX () sąrašo srityje yra tik dvylika įrašų, todėl negalima rasti įrašo -1. Šią klaidos reikšmę sužinosite naudodami funkciją IFERROR () ir grąžinsite tuščią eilutę. Pavyzdyje taip yra 4 eilutės C produkto atveju, nes daugiau sutarčių dėl šio produkto sudaryti nebuvo galima:
{=IFERROR(INDEKSAS ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 "", COLUMN (B2: M2) -1, -1)));"")}
Kadangi tai yra masyvo formulė, užpildykite formulės įrašą naudodami klavišų kombinaciją Ctrl + Shift + Enter.
