Kjenner du det fiffige trikset med å lage en referanse til en kolonneoverskrift i den siste ikke-tomme cellen i en rad? Det beste: Du trenger ingen hjelpelinjer eller kolonner. Det er så enkelt:
Kombiner de fire funksjonene IFERROR (), INDEX (), MAX () og IF ()
Administrerende direktør i salg vil sende deg en liste over kontraktene som inngås per måned for produkter som skal fases ut ①. Du bør bruke en formel i kolonne N for å angi den siste måneden med salg for hvert produkt - uten hjelpelinjer eller kolonner. Hvis det ikke er inngått flere kontrakter, skriver du inn en tom celle i N -kolonnen.
Dette eksemplet, banalt ved første øyekast, viser seg å være en tøff mutter å knekke uten bruk av hjelpelinjer eller søyler. som Excel for praksis-Lesere knekker mutteren! Vi har følgende matriseformel i cellen for problemløsning N2 opprettet ②:
{= FEIL (INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; COLUMN (B2: M2) -1; -1))) "")}}
Ta en titt på hvordan det fungerer trinn for trinn
Start med IF () -tilstanden innebygd i matriseformelen: {= FEIL (INDEKS ($ B $ 1: $ M $ 1; MAX (HVIS (B2: M2 "", KOLONN (B2: M2) -1, -1)));"")}
IF () -tilstanden oppretter en fiktiv hjelpelinje i matriseformelen og sjekker etter cellene B2 før M2om disse er tomme eller ikke. Hvis en celle er tom, returneres verdien -1 ellers via funksjonen KOLONNE (), det respektive kolonnetallet minus verdien 1.
Subtraksjonen av 1 kreves i formelen fordi den første kolonnen i tabellen ikke inneholder månedsnavnet, men produktnavnet. Nedenfor lærer du hvordan du bruker INDEX () -funksjonen til å vise det tilsvarende månedsnavnet, som - hvis du ikke trekker fra 1 - på grunn av den ekstra kolonnen som brukes EN. ville være feil med nøyaktig en kolonne.
Hvis alle cellene i området B2: M2 er tomme, oppretter verdien -1 (ingen produktsalg) en feil som vi bruker til å representere en tom celle. Den aktive hjelpelinjen kan sees på figur ③ på linje 3.
I neste trinn leser du ut den største verdien med MAX () -funksjonen, der IF () -tilstanden er nestet. Dette er verdien 12 på linje 3 (kolonne 13 minus 1; se tilleggslinjen i figur ③):
{= FEIL (INDEKS ($ B $ 1: $ M $ 1;MAX (IF (B2: M2 "", COLUMN (B2: M2) -1, -1)));"")}
Du sender denne MAX -verdien til INDEX () -funksjonen. Den tilsvarende kalendermåneden blir deretter lest opp på linje 1. Dataområdet til INDEX () -funksjonen er området $ B $ 1: $ M $ 1. Den bestått MAX -verdien - i eksempel 12 - betyr at den tolvte verdien i listen, dvs. Des for desember måned:
{= FEIL(INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; COLUMN (B2: M2) -1; -1)));"")}
Hvis alle cellene i månedsområdet i tabellen er tomme, er den største verdien -1 (se IF () -tilstand i begynnelsen). Hvis verdien -1 sendes til INDEX () -funksjonen, fører dette uunngåelig til en feilverdi, siden listeområdet til INDEX () -funksjonen bare inneholder tolv oppføringer og derfor ikke kan finne oppføringen -1. Du fanger opp denne feilverdien med IFERROR () -funksjonen og returnerer i stedet en tom streng. I eksemplet er dette tilfellet for produkt C på linje 4, fordi det ikke kunne inngås mer kontrakt for dette produktet:
{=IFERROR(INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 "", COLUMN (B2: M2) -1, -1)));"")}
Siden dette er en matriseformel, fullfører du oppføringen av formelen med tastekombinasjonen Ctrl + Shift + Enter.
