Kender du det fede trick ved at oprette en reference til en kolonneoverskrift i den sidste ikke-tomme celle i en række? Det bedste: Du behøver ikke hjælpelinjer eller kolonner. Det er så let:
Kombiner de fire funktioner IFERROR (), INDEX (), MAX () og IF ()
Den administrerende direktør i salg sender dig en liste over de kontrakter, der indgås om måneden for produkter, der udfases ①. Du skal bruge en formel i kolonne N til at angive den sidste måned i salget for hvert produkt - uden hjælpelinjer eller kolonner. Hvis der ikke er indgået flere kontrakter, skal du indtaste en tom celle i kolonnen N.
Dette eksempel, banalt ved første øjekast, viser sig at være en hård møtrik at knække uden brug af hjælpelinjer eller søjler. som Excel til praksis-Læsere knækker møtrikken! Vi har følgende matrixformel i cellen til problemløsning N2 oprettet ②:
{= IFERROR (INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; COLUMN (B2: M2) -1; -1))) "")}}
Tag et kig på, hvordan det fungerer trin for trin
Start med IF () -betingelsen indbygget i matrixformlen: {= FEJL (INDEX ($ B $ 1: $ M $ 1; MAX (HVIS (B2: M2 "", KOLONNE (B2: M2) -1, -1)));"")}
IF () -tilstanden opretter en fiktiv hjælpelinje i matrixformlen og kontrollerer cellerne B2 så længe M2om disse er tomme eller ej. Hvis en celle er tom, returneres værdien -1 ellers via funktionen KOLONNE (), det respektive kolonnenummer minus værdien 1.
Subtraktionen af 1 er påkrævet i formlen, fordi den første kolonne i tabellen ikke indeholder månedsnavnet, men produktnavnet. Nedenfor lærer du, hvordan du bruger INDEX () -funktionen til at vise det tilsvarende månedsnavn, som - hvis du ikke trak 1 - på grund af den ekstra kolonne, der blev brugt EN. ville være forkert med præcis en kolonne.
Hvis alle celler i området B2: M2 er tomme, skaber værdien -1 (intet produktsalg) en fejl, som vi bruger til at repræsentere en tom celle. Den aktive hjælpelinje kan ses i fig. ③ i linje 3.
I det næste trin læser du den største værdi med funktionen MAX (), hvor IF () -tilstanden er indlejret. Dette er værdien 12 i linje 3 (kolonne 13 minus 1; se hjælpelinje i figur ③):
{= FEJL (INDEX ($ B $ 1: $ M $ 1;MAX (IF (B2: M2 "", COLUMN (B2: M2) -1, -1)));"")}
Du sender denne MAX -værdi til INDEX () -funktionen. Den tilsvarende kalendermåned læses derefter op på linje 1. Dataområdet for INDEX () -funktionen er området $ B $ 1: $ M $ 1. Den beståede MAX -værdi - i eksemplet 12 - betyder, at den tolvte værdi i listen, dvs. Dec for december måned:
{= FEJL(INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; COLUMN (B2: M2) -1; -1)));"")}
Hvis alle celler i tabellens månedsinterval er tomme, er den største værdi -1 (se IF () betingelse i begyndelsen). Hvis værdien -1 overføres til INDEX () -funktionen, fører dette uundgåeligt til en fejlværdi, da listeområdet for INDEX () -funktionen kun indeholder tolv poster og derfor ikke kan finde posten -1. Du fanger denne fejlværdi med funktionen IFERROR () og returnerer i stedet en tom streng. I eksemplet er dette tilfældet for produkt C i linje 4, fordi der ikke kunne indgås mere kontrakt om dette produkt:
{=IFERROR(INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 "", COLUMN (B2: M2) -1, -1)));"")}
Da dette er en matrixformel, skal du indtaste formlen med tastekombinationen Ctrl + Shift + Enter.
