Kent u de handige truc van het maken van een verwijzing naar een kolomkop in de laatste niet-lege cel van een rij? Het beste: u hebt geen hulplijnen of kolommen nodig. Het is zo makkelijk:
Combineer de vier functies IFERROR (), INDEX (), MAX () en IF ()
De directeur verkoop stuurt je maandelijks een overzicht van de contracten die zijn afgesloten voor producten die worden uitgefaseerd ①. Gebruik een formule in kolom N om de laatste verkoopmaand voor elk product te specificeren - zonder hulplijnen of kolommen. Als er geen contracten meer zijn afgesloten, vul dan een lege cel in de N-kolom in.
Dit op het eerste gezicht banale voorbeeld blijkt een harde noot om te kraken zonder het gebruik van hulplijnen of kolommen. als Excel om te oefenen-Lezers kraken de noot! We hebben de volgende matrixformule in de cel voor het oplossen van problemen: N2 gemaakt :
{= IFERROR (INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; KOLOM (B2: M2) -1; -1))); "")}
Bekijk stap voor stap hoe het werkt
Begin met de voorwaarde IF () die in de matrixformule is ingebouwd: {= IFERROR (INDEX ($ B $ 1: $ M $ 1; MAX (ALS (B2: M2 "", KOLOM (B2: M2) -1, -1)));"")}
De voorwaarde IF () creëert een fictieve hulplijn in de matrixformule en controleert de cellen B2 tot M2of deze nu leeg zijn of niet. Als een cel leeg is, wordt de waarde -1 anders geretourneerd via de functie KOLOM () , het respectieve kolomnummer minus de waarde 1.
De aftrekking van 1 is vereist in de formule omdat de eerste kolom in de tabel niet de maandnaam bevat, maar de productnaam. Hieronder leert u hoe u de functie INDEX () gebruikt om de bijbehorende maandnaam weer te geven, die - als u de 1 niet aftrekt - vanwege de extra gebruikte kolom A. precies één kolom onjuist zou zijn.
Als alle cellen in het bereik B2: M2 leeg zijn, creëert de waarde -1 (geen productverkoop) een fout die we gebruiken om een lege cel weer te geven. De actieve hulplijn is te zien in Fig. ③ in lijn 3.
In de volgende stap lees je de grootste waarde uit met de MAX () functie, waarin de IF () voorwaarde is genest. Dit is de waarde 12 in regel 3 (kolom 13 min 1; zie hulpregel in figuur ③):
{= IFERROR (INDEX ($ B $ 1: $ M $ 1;MAX (ALS (B2: M2 "", KOLOM (B2: M2) -1, -1)));"")}
U geeft deze MAX-waarde door aan de functie INDEX (). In regel 1 wordt dan de bijbehorende kalendermaand uitgelezen. Het gegevensgebied van de functie INDEX () is het gebied $ B $ 1: $ M $ 1. De doorgegeven MAX-waarde - in het voorbeeld 12 - betekent dat de twaalfde waarde in de lijst, d.w.z. december voor de maand december:
{= IFERROR(INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; KOLOM (B2: M2) -1; -1)));"")}
Als alle cellen in het maandbereik van de tabel leeg zijn, is de grootste waarde -1 (zie IF () voorwaarde aan het begin). Als de waarde -1 wordt doorgegeven aan de functie INDEX () leidt dit onvermijdelijk tot een foutwaarde, aangezien het lijstgebied van de functie INDEX () slechts twaalf items bevat en daarom de invoer -1 niet kan vinden. U vangt deze foutwaarde op met de functie IFERROR () en retourneert in plaats daarvan een lege tekenreeks. In het voorbeeld is dit het geval voor product C in regel 4, omdat er voor dit product geen contract meer gesloten kon worden:
{=IFERROR(INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 "", KOLOM (B2: M2) -1, -1)));"")}
Aangezien dit een matrixformule is, voltooit u de invoer van de formule met de toetsencombinatie Ctrl + Shift + Enter.
