Tilläggsverktyget MoreFunc


© 2000 - 2007 All rights Laurent Longre

Published by XL-Dennis with permission


Laurent Longre har utvecklat tilläggsverktyget MOREFUNC med många nyttiga och spännande funktioner. Funktionerna kompletterar de i Excel inbyggda funktionerna och i vissa avseenden kan de även ersätta de dessa då de helt enkelt är bättre.

Funktionerna i MoreFunc är lika snabba som de inbyggda då tilläggsverktyget är skrivet i C och är kompilerad till en DLL eller som de vanligtvis kallas för XLL.

Tilläggsverktyget är gratis och finns tillgängligt för hämtning här: http://xcell05.free.fr/

Här presenterar ett större urval av funktionerna, där några bereds större utrymme än andra utifrån den erfarenhet XL-Dennis har från tilläggsverktyget.

LASTROW

Identifierar den sista raden i en eller flera kolumner.

Detta kan även lösas med en matrisformel såsom: {=MAX((A1:A65535<>"")*RAD(1:65535))}

Exemplet i bilden nedan visar hur vi kan erhålla summan för den sista använda raden:

 

Följande formel ger oss lösningen:

 

SETV / GETV

Funktionen SETV tilldelar en temporär variabel ett värde, vilket vi kan utnyttja för att evaluera en beräkning. Funktionen GETV hämtar värdet från den temporära variabeln.

Tillsammans utgör dessa funktioner många gånger ett bättre alternativ än de inbyggda funktionerna. Detta då formlerna i sig blir kortare samt mindre beräkningsintensiva för Excel.

I det första exemplet, som också visas i nedanstående bild, ska det eventuella radnumret som håller uppslagsvärdet identifieras.

 

Så här ser formeln ut med SETV och GETV:

 

Där PASSA(D1;A2:A5;0)) ger det temporära värdet, #Saknas!, som SETV-funktionen håller. Därefter evalueras villkoret ÄRTAL(SETV) och beroende på utfallet (SANT eller FALSKT) returneras 0 eller det faktiska radnumret mha GETV-funktionen.

Med en traditionell formel löser vi det på följande sätt:

 

I det andra exemplet sker ett vanligt leta upp förfarande, se följande bild:

 

Med funktionerna SETV och GETV får vi följande formel:

 

Med en traditionell formel löser vi det på följande sätt:

 

I det tredje och sista exemplet visas en mer komplex uppslagsfunktion och exemplet visas i sin helhet i följande bild:

 

Problemet som ska lösas är att leta upp uppslagsvärdet i Lista 1 och därefter leta upp värdet i Lista 2 med en  rad nedanför, dvs en förskjutning med en rad.
 

Med funktionerna SETV och GETV får vi följande formel:

 

Med en traditionell formel löser vi det på följande sätt:

 

COUNTIF.3D

Denna funktion tillhör definitivt en av XL-Dennis favoriter. Funktionen arbetar över flera arbetsblad och med flera cellområden. Dock begränsas den till ett och samma cellområde i flera arbetsblad men å andra sidan kan vi använda oss av bladnamnsintervall.

För att få liknande funktionalitet med Excels inbyggda funktioner hänvisas till tipset Räkna antal förekomster i flera blad.

Exemplet, som visas i sin helhet i nedanstående bild, sammanställer antalet per intervall samt medelvärdet för  cellområdet B1:B10 i samtliga arbetsbladnamn mellan Jan, Feb och Mar.

 

 

För beräkning av antalet förekomster för det första intervallet används följande formel:


För beräkning av antal förekomster för det andra och tredje intervallet används följande matrisformel:

 

För beräkning av medelvärdet används följande formel:

 

MCONCAT

Denna funktion är ytterligare ett exempel på en av XL-Dennis favoriter. Vid sammanfogning av cellvärden kan vi utan detta tilläggsverktyg antingen använda:

 

Dessa angreppssätt fungerar väl när vi har att göra med ett mindre antal celler.

MCONCAT-funktionen fungerar annorlunda i det att vi kan ange större sammanhängancde cellområden såsom följande exempel visar:

 

För sammanfogning av text används följande lösning:

 

För sammanfogning av tal används följande lösning:

 

Vi kan även sammanfoga icke sammanhängande cellområden enligt följande:

COUNTDIFF & UNIQUEVALUES

Dessa funktioner har det gemensamma att de hanterar unika värden i en lista.

COUNTDIFF räknar förekomsten av värden som förekommer inklusive blanka celler och felmeddelanden. UNIQUEVALUES ger de tal som förekommer i listan.

Exemplet framgår av följande bild:

 

För att erhålla antalet unika värden i listan används följande formel:

 

Där angivande av värdet SANT innebär att även medräkna tomma celler i cellområdet.

För att erhålla en sorterad förteckning över de unika talen i listan matas nedanstående matrisformel in i cellområdet D2:D6 genom att:


För att se formler som baseras på de inbyggda funktionerna se Frekvens.


Rensa serie

I detta exempel används såväl MCONCAT som EVAL-funktionerna. Exemplet visar hur vi kan erhålla en lista vars värden inte har använts i ett cellområde.

Exemplet i sin helhet visas i följande bild:

 

Följande matrisformel ger kvarvarande värden i cellen D2:

 

WMID

Här belyses hur vi kan kasta om textvärden i enstaka celler och i listor. Exemplet belyser hur v kan lösa det med en traditionell formel och med funktionen WMID.

Följande bild visar exemplet med korrekt utfall:

 

 

Korrekta värden i B-kolumnen erhålls med följande traditionella formel:

 

Korrekta värden i C-kolumnen fås med WMID-funktionen:

 

Beräkna skifttider

I detta exempel demonstreras hur vi kan beräkna arbetstid per dag och per vecka där symboler för olika skift används. Formeltekniskt sätt är det EVAL-funktionen som möjliggör denna lösning.

Bilden nedan visar exemplet i sin helhet:

 

Följande matrisformel används i cell C10 och som kopieras i raden:

 

I cellområdet C10:G10 används det anpassade tidsformatet [tt]:mm.

För att beräkning ska kunna ske per kolumn krävs att varje cell i respektive kolumnområde är ifyllt.


Högsta / Lägsta notering

I detta tips visas hur vi bl a kan erhålla högsta respektive lägsta värdet i en cell vars innehåll löpande justeras. Det sker mha funktionen RECALL. Även andra beräkningar kan ske mha av funktionen.
 

Bilden nedan visar exemplet i sin helhet:

 

Följande formler används:

Högsta notering:

Lägsta notering:

Ackumulerad summering: