Känner du till det fiffiga tricket att skapa en referens till en kolumnrubrik i den sista icke-tomma cellen i en rad? Det bästa: Du behöver inga hjälplinjer eller kolumner. Det är så enkelt:
Kombinera de fyra funktionerna IFERROR (), INDEX (), MAX () och IF ()
Verkställande direktören i försäljning skickar dig en lista över de kontrakt som ingås per månad för produkter som fasas ut ①. Du bör använda en formel i kolumn N för att ange den sista månaden för försäljning för varje produkt - utan några extra rader eller kolumner. Om inga fler kontrakt har ingåtts anger du en tom cell i kolumnen N.
Detta exempel, banalt vid första anblicken, visar sig vara en tuff nöt att knäcka utan att använda hjälplinjer eller kolumner. som Excel för övning-Läsare knäcker muttern! Vi har följande matrisformel i cellen för problemlösning N2 skapad ②:
{= IFERROR (INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; COLUMN (B2: M2) -1; -1))) "")}}
Ta en titt på hur det fungerar steg för steg
Börja med villkoret IF () inbyggt i matrisformeln: {= FEL (INDEX ($ B $ 1: $ M $ 1; MAX (OM (B2: M2 "", KOLUMN (B2: M2) -1, -1)));"")}
IF () -villkoret skapar en fiktiv hjälplinje i matrisformeln och söker efter cellerna B2 fram tills M2om dessa är tomma eller inte. Om en cell är tom returneras annars värdet -1 via funktionen KOLONN (), respektive kolumnnummer minus värdet 1.
Subtraktionen av 1 krävs i formeln eftersom den första kolumnen i tabellen inte innehåller månadsnamnet, utan produktnamnet. Nedan lär du dig hur du använder INDEX () -funktionen för att visa motsvarande månadsnamn, vilket - om du inte subtraherade 1 - på grund av den extra kolumn som används A. skulle vara fel med exakt en kolumn.
Om alla celler i intervallet B2: M2 är tomma skapar värdet -1 (ingen produktförsäljning) ett fel som vi använder för att representera en tom cell. Den aktiva hjälplinjen kan ses i fig. ③ i rad 3.
I nästa steg läser du det största värdet med MAX () -funktionen, där IF () -villkoret är inkapslat. Detta är värdet 12 på rad 3 (kolumn 13 minus 1; se hjälplinje i figur ③):
{= FEL (INDEX ($ B $ 1: $ M $ 1;MAX (IF (B2: M2 "", COLUMN (B2: M2) -1, -1)));"")}
Du skickar detta MAX -värde till INDEX () -funktionen. Motsvarande kalendermånad läses sedan upp på rad 1. Dataområdet för INDEX () -funktionen är området $ B $ 1: $ M $ 1. Det godkända MAX -värdet - i exemplet 12 - betyder att det tolfte värdet i listan, dvs. Dec för december månad:
{= FEL(INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; KOLUMN (B2: M2) -1; -1)));"")}
Om alla celler i tabellens månadsintervall är tomma, är det största värdet -1 (se IF () -förhållande i början). Om värdet -1 skickas till INDEX () -funktionen leder detta oundvikligen till ett felvärde, eftersom listområdet för INDEX () -funktionen bara innehåller tolv poster och därför inte kan hitta posten -1. Du fångar upp detta felvärde med funktionen IFERROR () och returnerar istället en tom sträng. I exemplet är detta fallet för produkt C i rad 4, eftersom inget mer kontrakt kunde ingås för denna produkt:
{=IFERROR(INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 "", COLUMN (B2: M2) -1, -1)));"")}
Eftersom detta är en matrisformel, fyll i formulärets inmatning med tangentkombinationen Ctrl + Skift + Retur.
