Snygga till det med PROPER

För en tid sedan fann jag mig i ett statistiskt problem. Mitt mål var att räkna ut vilket område som till hade mest frekvensta kunder. Jag fann mig ha en lista på några tuden entries där källan är vad kunden själv har knappat in i databasen. 

Som synes är en del entries i versaler, andra i gemener medan de flesta är hel ok som jag vill ha dem. Dvs. begynnelsebokstav som versal, resten som gemener. Jag skulle kunna lösa det genom att skapa en ny tabell där jag letar up de felaktiga värdena och returnerar de korrekta med VLOOKUP men det är en tabell jag behöver underhålla. Det är ett levande dokument och jag vill ju såklart att alla framtida tillkomster ska behandlas på samma sätt. 

Svaret är lika enkelt som stilligt. PROPER gör det åt oss. Vi fixar en ny kolumn som vi använder till att räkna och plocka resultatet ifrån. I den nya kolumnen skriver man =PROPER(N1722) där N1722 är celllen vi vill fixa. Alla ord kommer nu att få en versal som begynnelsebokstav och med efter följande gemener. Jämför skillnaden. Vilken tycker du ser stilrenast ut?

Motsvarande formler kan användas för enbart versaler (UPPER) eller gemener (LOWER). Riktigt smidigt!

Använda SEARCH för att dela upp i categorier

I det här exemplet har jag skapat ett antal felkoder. Inte sällan finns det en sträng som är tämligen lika medan det finns en avvikande kombination av tecken någonstans. I exemplet jag har skapat är den gemensamma strängen omgiven av lite slumpmässiga siffror. Det ser inte helt olikt ut från verkligheten faktiskt.

Nu vill jag kunna pivotera det där. Men eftersom det är tilldynes en enda röra måste jag ordna reda ur kaoset. Jag brukar göra så här.

- Först tar jag reda på de statiska strängerna som finns i mitt data

·       - Sedan ordnar jag det med att skriva en och en av dessa stränger överst i kolumnerna till höger.

Det blir ungefär så här:

Nästa steg blir att skapa en formel som tala om vilka rader som ska ingå i varje kolumn. Vi vill alltås indikera alla rader som innhåller ”Fel Siffror” i kolumn B etc. Enbart en IF formel hjälper oss inte eftersom den då kommer att leta exakt efter ”Fel Siffor” och inte få en enda träff. Formeln =IF(A2=B1;TRUE;FALSE) där vi jämför A2 med B1 måste därför utökas och förändras lite.

Först lägger vi till SEARCH i formeln. Det är en funktion som leter inne i texten efter en textsträng vi säger åt den att leta efter. I det här fallet innehållet i B1.

=IF(SEARCH(B1;A2);TRUE;FALSE) ger oss värdet True om värdet i B1 finns i A2. Men för att kunna fylla formeln till höger och nedåt måste vi lägga in lite $ i formeln. Dollartecken låser relativa formler till absoluta. Vi sätter markören mitt i vår cell referens och trycker på F4. Nu kommer vi att bläddra genom tre val: $B$1, B$1 och $B1. Beroende på hur vi vill låsa cellen som väljer vi ett av dessa. $B$1 motsvarar den exakta cellen hur vi än fyller formeln i de andra fälten. B$1 behåller formeln på första raden och $B1 i första kolumnen. 

I det här fallet vill vi behålla oss på första raden och första kolumen när vi fyller ut formeln. Det blir alltså =IF(SEARCH(B$1;$A2);TRUE;FALSE). Vi får ett resultat som ser ut i den här stilen. Det vill vi förstås snygga till och se till att vi kan räkna på värdena.

Istället för TRUE och FALSE ändrar vi till wn 1a för true. =IF(SEARCH(B$1;$A2);1;FALSE). FALSE skulle vi egentligen kunna plocka bort men eftersom jag gillar att ha alla valen kvar I exemplet later vi det stanna. Dock lägger vi till IFERROR till vår formel. Vi vill bestämma vad vi ska visa om vi får ett fel. Då vill vi inte visa någonting, eller en Nolla berdene på vad vi vill göra med siffrorna i nästa steg. Jag väljer att visa en 0a.

=IFERROR(IF(SEARCH(B$1;$A2);1;FALSE);)

 Det är nu möjligt att skapa en illustrerade pivottabell.

Jag gillar också att summera och jämföra direkt i tabellen. Summan delat med antalet i varje kolumn:

=SUM(B3:B236)/COUNTA(B3:B236).

Radbrytning med CONCAT

Visst är det frustrerande när man försöker klistra in info i excel och man får resultatet i flera celler när man egentligen behöver det i en? Låt oss säga att vi vill lista olika skivsläpp av olika artister. Vi vill då ha artisten i ett fält och alla skivorna i motsvarande fält.

Utan att göra någonting ser det ut så här om vi klistrar in direkt från Wikipedia.

Vi vill alltså samla skivorna från Metallica i B1 och Anthrax i B2. För detta kan vi använda CONCAT. Infoga en ny kolumn och använd formeln på följande sätt. =CONCAT(C1:C12)

Det betyder dock att vi får allt på en enda rad och det är varken snyggt eller överskådligt. Hur löser vi nu detta? Jo, Lösningen ligger i funktionen CHAR! CHAR är en funktion som infogar tecken i excel. Följande =CHAR(142) ger oss till exempel tecknet Ž. Med denna funktion kan man således infoga ett tecken motsvarande radbrytning =CHAR(10).

Jag lägger denna funktion i varje motsvarande fällt i kolumn D. 

Gör CONCAT igen, denna gång på kolumn C och D.

Klicka på Wrap text och kopiera kolumn B och klistra in som värden.

Nu kan den ursprungliga datan raderas och vi får vårt önskade resultat.

Användbart om man vill lista variabler på samma ämne.

Jämföra två listor i excel

Det finns många sätt att jämföra data i två tabeller med varandra. Här är en metod som jag ofta brukar använda mig av.

Låt oss säga att vi har en lista med filmtitlar som vi vill jämföra med en annan. I det här fallet har jag tagit John Carpenters filmografi som regissör. Inga konstigheter, källan är wikipedia. Jag lägger den i en egen flik. Specifika flikar för olika information är oftast att föredra. Därifrån länkar man samman alltig i en ”operativ flik” på så sätt kan man ändra i sin masterdata utan att behöva skriva om en massa formler. Vi kallar denna flik för Filmography.

I en annan flik skriver vi vilka av dessa titlar som vi äger på DVD, blu-ray eller annat förmat. Just nu är vi bara intresserade av att jämföra vad vi äger med vad karln har regissörat så format struntar vi i. Låt oss säga att vi äger dessa. Vi kallar fliken för Owned.

Det är viktigt att komma ihåg att formatet måste vara exakt lika som tabellen vi ska jämföra med. Ett mellanslag mer gör att formeln spricker.

Det vi skulle kunna göra är att skriva en enkel lookupformel. Det skulle i det här fallet se ut så här: =VLOOKUP(A1;Owned!A:A;1;FALSE). Det betyder att vi letar upp värdet i cellen A1 i kolumnen A i fliken Owned. Hittar vi en träff kommer innehållet att skrivas till cellen där vi har vår formel.

Ganska enkelt men med ett par nackdelar. För det första blir det lätt rörigt med två kolumner som egentligen säger samma sak. För det andra. Är det rätt fullt med #N/A errors bland datan. För att komma runt det första problemet brukar jag jag bygga till ett vilkorsargument. Jag vill helt enkelt att de titlar jag äger ska stå som ”owned” i kolumn B och inte som titeln. Det jag ber excel göra är helt enkelt att skriva ”owned” om resultatet i vår andra tabell, den med titlarna vi äger, stämmer överens med texten i kolumn A.

=IF(VLOOKUP(A1;Owned!A:A;1;FALSE)=A1;"Owned") Här talar jag om att om resultatet av lookupformeln är samma som den text vi letar efter så ska texten ”Owned” visas. Stämmer det inte överrens ska cellen vara tom. Det ger oss fölande resultat.

Fortfarande är de fula #N/A felen kvar men de ska vi ta bort i nästa steg. Vi instruerar helt enkelt excel att ta bort dem om det visar sig att det blir ett fel. Vi vill kanske inte ta bort alla fel som kan uppstå utan vara dem av #N/A karaktär. Följande tillägg fixar det år oss.

=IFNA(IF(VLOOKUP(A1;Owned!A:A;1;FALSE)=A1;"Owned");"")

Voila!

VLOOKUP med multipla kriterier

Visst har vi alla råkat ut för situationer där vi behöver ha flera vilkor sanna för att VLOOKUP ska vara rätt operation. Det finns ett ganska enkelt sätt att kringå den formen av problematik.

Låt oss säga att vi har följande information i en tabell.

Vi vill plocka priset här ifrån men vi vill samtidigt att alla kriterierna i kolumn A-D stämmer med målet. Antagligen skulle vi kunna skapa en massa krångliga AND och IF formeler för att få det att funka. Eller kanske ännu heller INDEX och MATCH. Men det bli en massa kod som vi egentligen inte behöver. Lösningen ligger i att skapa en ny column och göra en enda formel i denna. Den vi ska använda os av heter CONCAT

Det är en busenkel formel som trycker ihop all info i de fält vi väljer till ett enda. Med =CONCAT(B2:E2) Får vi följande resultat.

Fyll formeln i hela kolumnen som ska användas och gör samma sak i den mottagande tabellen. Vi får följande struktur.

Prislista

Lager

Det enda vi behöver göra nu är en vanligt VLOOKUP mot A kolumnen för att få det resultat vi vill ha. =VLOOKUP(A2;Prislista!A:F;6;FALSE)

Självklart kan vi snygga till det genom att formatera cellerna som valuta och plocka bort #N/A med ett enkelt tillägg I formeln. =IFNA(VLOOKUP(A2;Prislista!A:F;6;FALSE);"")