Matriskalkylering III

Välj tips & trick ur denna lista Matriser Matriskonstanter Mest & minst frekventa värde Räkna förekomster i rad och kolumn Unika värden i cellområden Rangordning Rangordna färger Hitta x:e största & minsta värde Matrissummering Månad- och Veckosummeringar Matrismedelvärden Intervallsummering Beräkna vartannat tal... Räkna antal tecken i en lista Flera matrisvillkor Hitta högsta/lägsta intervallvärde Summering av högsta/lägsta värdena Hitta ifyllda/tomma celler Rad & Celladress med maxvärdet? Hitta celladressen! Hitta cellområdet Hitta sista värdet Frekventa värden Diagonal summering Hitta sista raden & kolumnen Hitta största värdet...... Hantera matrisdata Är alla celler ifyllda? Celladresser till de största/minsta värdena Räkna med PERCENTIL Minsta & största värden i mitten Årssummering Hitta rätt data Medelvärdet av två matriser Medelvärdet för unika värden Räkna nummerserier Räkna med textvärden Kontrollera om duplikat vid inmatning Summera värdena i ett tal Identifiera heltal Vilken månad? Vilka har de högsta värdena? Räkna med cellområden i tabeller Radsammanställning Summera texttal Hitta post med största antal Radposition Datum för senast inmatade värdet Antal unika kunder Saknade serievärden Skapa unik lista från två unika listor Hämta värden ur textsträngar Är cellområdet tomt? Skapa ny sammanhängande lista Lista största värdena utan dubbletter Erhålla största & minsta värdet Namnsökning Hitta det numeriska värdet i raden Antal unika poster utifrån villkor Extrahera tal ur text Korrigera värden Raders medelvärde Summera artikelgrupper Sök värde med flera sökkriterium... Ett uppslagsvärde och flera utfallsvärden Närmaste värden Produktsumman av rad- och kolumnvärden

Diagonal summering

Att summera diagonalt är i sig ganska enkelt. Med hjälp av en summaformel, t ex  =SUMMA(A1;B2;C4), får man en diagonal summa. Nackdelen är ju förstås om man har några hundra celler man vill summera.

Här presenteras några tekniker som löser det smidigare. Utgångspunkten är tabellen nedan.

 

För att erhålla summan för intervallet B13:F9 används följande formel:

• {=SUMMA(N(FÖRSKJUTNING(F9;RAD(1:5)-1;-1*RAD(1:5)+1)))}

 

Cellen F9 utgör här startpunkten och RAD(1:5) representerar själva matrisen (5 kolumner och 5 rader i FÖRSKJUTNING-formeln). Som framgår av formeln krävs en viss korrigering (1 / -1).

Andra lösningar för att summera intervallet B13:F9 är:

• {=SUMMA(N(FÖRSKJUTNING(F9;RAD(INDIREKT("1:5"))-1;-1*RAD(INDIREKT("1:5"))+1)))}
• =PRODUKTSUMMA(N(FÖRSKJUTNING(F9;RAD(INDIREKT("1:5"))-1;-1*RAD(INDIREKT("1:5"))+1)))

 

För att erhålla summan för intervallet B9:F13 används följande formel:

• {=SUMMA(N(FÖRSKJUTNING(B9;RAD(1:5)-1;RAD(1:5)-1)))}

 

Andra lösningar för att summera intervallet B9:F13 är:

• {=SUMMA(N(INDIREKT(ADRESS(RAD(9:13);RAD(1:5)+1))))}
• =PRODUKTSUMMA(N(INDIREKT(ADRESS(RAD(9:13);RAD(1:5)+1))))

 

Se PRODUKTSUMMA för mer detaljer om funktionen.

Hitta sista raden & kolumnen

Behöver du ha reda på vilken rad eller kolumn som det sista värdet i en serie ligger i? Här presenteras en relativ kort lösning till denna fråga.

Tabellen nedan visar exemplet och visuellt kan vi avläsa att det är den 7:e raden det sista värdet finns i.

Lösningen består av följande matrisformel:

• {=MAX(RAD(A1:A7)*(A1:A7<>""))}

För att erhålla kolumn istället för rad ersätts RAD-funktionen med KOLUMN-funktionen:

• {=MAX(KOLUMN(A1:C1)*(A1:C1<>""))}

Vill vi istället hitta den första tomma raden i en kolumn kan följande matrisformel användas:

• {=MIN(OM(A1:A7="";RAD(1:7)))}

Eller med formeln:

• {=PASSA(SANT;ÄRTOM(A1:A7);0)}

Vilket ger rad 6 i exemplet. 

Se också Hitta ifyllda / tomma celladresser.

Hantera matrisdata

Här presenteras en teknik för hantering av matrisdata. Exemplet visar hur man kontrollerar kontrollerar rad- och kolumnangivelser.

Nedan visas den ursprungliga tabellen.

 

Nu vill vi ändra på datamängden i det att den ska presenteras på följande sätt:

Hur får vi denna lösning månne? Jo, med hjälp av följande formel, där formeln i cellen C1 kopieras till de intill liggande cellerna:

 

• =INDIREKT("A"&(2*RAD(A1)-(2-KOLUMN(A1))))

Hitta största värdet oavsett negativt/positivt

Antag att du vill ha reda på det största värdet i en större datamängd, oavsett om det är positivt eller negativt. Med dessa förutsättningar så är t ex -100 större än 50!

Tabellen nedan visar förutsättningarna för exemplet samt utfallet.

 

Lösningen ligger företrädesvis i att använda sig av ABS-funktionen, vilken ignorerar huruvida tal är positiva eller negativa. Nästa steg är att nyttja MAX-funktionen. Givet förståelsen för det så kan man därefter använda sig av PASSA- och INDEX-funktionerna. Vi får därmed följande tekniska lösning på problemet:

• {=INDEX(A2:A6;PASSA(MAX(ABS(A2:A6));ABS(A2:A6);0))}

 

Vill vi inte använda oss av en matrisformel kan följande formel vara ett alternativ:

• =OM(ABS(MIN(A1:A5))>MAX(A1:A5);MIN(A1:A5);MAX(A1:A5))

Är alla celler ifyllda?

I en kalkylmall e d kan det vara en god lösning att tvinga användarna att fylla i önskade uppgifter innan slutlig beräkning sker. 

I bilden nedan visas två listor. I lista 1 är alla önskade celler ifyllda varför en beräkning sker. I lista 2 saknas det en uppgift varför den bakomliggande formeln visar felmeddelandet #Saknas!.

 

Lösningen består av en matrisformel, där formeln för Lista 1 är:

• {=OM(ELLER(ÄRTOM(A2:A6));SAKNAS();SUMMA(A2:A6))}

För Lista 2 skiljer sig formeln,  tekniskt sett, från den förstnämnda:

• {=OM(OCH(ÄRTAL(B2:B6));SUMMA(B2:B6);SAKNAS())}