Hjälp med excel formel

[Mattias Svensson]

Hej! Jag håller på att lär mig lite mer avancerad excel-teknik men är bara nybörjare ännu. Jag ska undersöka pendling mellan olika kommuner. I kolumn A har jag kommunnummer för den kommun de aktuella individen bor i. I kolumn B har jag kommunnummer på den kommunen individerna I A jobbar i. I kolumn C har jag antalet individer det gäller.

Så pendlar individerna i en kommun till 5 olika andra kommuner blir det alltså 5 (eller 6 rader om personen bor och jobbar i samma kommun, då blir kommunnumret samma i kolumn A och B + de andra 5 kommunerna) rader i kolumn A, varje rad med en ny arbetskommun i kolumn B. Antalet individer i kolumn C kan vara allt från 1 person till oändligt.

Efter de 5 eller 6 raderna i Kolumn A behandlas nästa kommun. Den kommunen kanske individer pendlar till 3 andra kommuner och även inom sin egen kommun. Då blir det 4 rader men det kommunnumret i kolumn A, medan det i kolumn B blir 4 olika kommunnummer till de kommunerna individerna i den kommunen pendlar till. Och i kolumn C visas alltså hur många som pendlar till varje kommun.

 

Jag ska räkna ut andelen i varje kommun som pendlar till de andra kommunerna och ha det i kolumn D. Hur gör jag en formen/script för detta? Jag vill alltså högst upp i kolumn D kunna gör en formel som jag bara kan dra ner hela vägen och få allt uträknat. Vet dock ej om det går. Först måste formeln alltså summera alla som bor i kommunen i kolumn A (antalet personer finns naturligt vis i kolumn C), alltså de rader i kolumn A som är identiska, de raderna i kolumn C ska summeras. Antalet personer i kolumn C ska sedan för varje kommun som de pendlar till ska (Kommunnumren i kolumn var för sig sedan divideras på den summerade summan som summerade det totala antalet som pendlade från kolumn A.

 

Hoppas ni hänger med. Så för varje gång raderna i kolumn A får ett nytt kommunnummer så måste ju formeln ändras till att bara gälla de raderna med den kommunen som utbendlingskommun. De olika kommunerna i kolumn B som pendlar från en och samma kommun från kolumn A måste på något sätt höra samman. Men när ett nytt kommunnummer i kolumn A dyker upp så måste kommunerna, de rader i kolumn B som hör till samma kommunnummer varifrån individerna pendlar i kolumn A, så måste excel förstå den förändringen och bara gälla de raderna med den nya kommunen som det pendlas ut ifrån i kolumn A.

 

Finns funktionen så måste det ju vara någon sorts smart låsningsfunktion till specifika kommunnummer i kolumn A som raderna i kolumn B och C rättar sig efter.

 

Snälla hjälp. Jag kan läsa mig till olika funktioner och formler som summa.om och om osv men just denna smarta funktion vet jag inte hur jag ska hitta. Jag vet man kan låsa formeln till en visst cell med dollartecken, men inte hur den låsningen ska ändras till att gälla nästa kommunnummer i kolumn A sedan.

 

Bifogar ett excel-exempel på hur det skulle kunna se ut med mitt exempel jag förklarat.

 

Tack på förhand!

 

MVH

Mattias Svensson

Exempel.xlsx

[Monshi]

Wall of text....

Ibland behövs det inte så många ord, det räcker med det matematiska problemet.

 

Om jag sammanfattar vad du skriver:

Du vill räkna ut andelen som en post gör av summan över ett nyckelvärde i A i D-kolumnen.

 

Det var väl allt?

=C2/SUMMA.OM($A$2:$A$11;A2;$C$2:$C$11)

i cell D2 och kopiera nedåt.

[Mattias Svensson]

Det är ju första delgrejen i formeln jag vill ha, det grundläggande.Om du kollar i filen jag bifogade. Det är koderna i Kolumn A som ska bestämma området för den uträkningen du precis gjorde. Utefter den bifogade filen skulle jag skriva denna formen:=C2/SUMMA.OM($A$2:$A$7;A2;$C$2:$C$7)

Detta då A2:A7 har identiska kommunnummer och jag vill just ha denna formel för dem. men sedan A8:A11 så kommer det 4 nya rader med identiska kommunkoder i kolumn A. Då vill jag fortsätta kunna dra ned den ursprungliga formeln med autofyll och excel ska då på ett logiskt sätt kunna inse att nu gäller området A8:A11 för att det nu är rader med en ny kommunkod i just de cellerna. Hur kan man ordna detta? Jag har 31.398 rader uppdelat på 333 kommunkoder nämligen, vill ej göra dessa uträkningar manuellt som du kanske förstår

[Monshi]

Du menar att när tabellen växer så vill du ha med även den nya värdena i formeln?

 

behövs väl inge ny formel... se magin när jag slår på tabellformatet i Excel och uppdaterar formeln.

 

Fast ärligt, en modell av Pivottabellen jag lade in är kanske mer intressent? Se flödena mellan kommunerna?

ja, du kan skippa summa-formeln och lösa allt med Pivot om du vill.

 

KommunExempel.xlsx

[Mattias Svensson]

Du menar att när tabellen växer så vill du ha med även den nya värdena i formeln?

 

behövs väl inge ny formel... se magin när jag slår på tabellformatet i Excel och uppdaterar formeln.

 

Fast ärligt, en modell av Pivottabellen jag lade in är kanske mer intressent? Se flödena mellan kommunerna?

ja, du kan skippa summa-formeln och lösa allt med Pivot om du vill.

 

KommunExempel.xlsx

 

Hur gjorde du när du slog på tabellformatet(vad är det), äre det du menar med att du uppdaterat formeln? För nu ser den helt annorlunda ut.

[Monshi]

Se bild...

Formeln är i sig densamma men den nyttjar Excels tabellformattering. Helt enkel de kolumnnamn som finns i tabellen. En nyhet i Excel 2007 som är mycket trevlig.

 

Testa och se hur det fungerar, lägg till fler rader med data och se vad som händer.

[Mattias Svensson]

Se bild...

Formeln är i sig densamma men den nyttjar Excels tabellformattering. Helt enkel de kolumnnamn som finns i tabellen. En nyhet i Excel 2007 som är mycket trevlig.

 

Testa och se hur det fungerar, lägg till fler rader med data och se vad som händer.

 

Tackar, ser lovande ut. Försöker få detta att fungera i ett nytt excelark. Jag formaterar om kolumnerna så det blir en tabell, dock ändras inte formeln att se ut som den gjorde i ditt exempel. Hur får jag excel att ändra på den ursprungliga formeln till att se ut som din?

 

Skulle du också vilja förklara den andra formeln så jag verkligen förstår den. Den första formeln förstår jag nog: =C2/SUMMA.OM($A$2:$A$7;A2;$C$2:$C$7), Den hr ett fixerat område och summaområde och villkoret ligger i cell A2. Därför fungerar denna inte autofylla ned på resterande kommunområden nedanför det valda området och summaområdet. Skulle man kunna skriva om denna formel att fungera i detta formatet lika som den nya formeln fungerar?

 

 

Den nya formeln: =[@antalArbetare]/SUMMA.OM([boendekommun];[@Boendekommun];[antalArbetare]) är ju liknande, den harboendekommun som område och boendekommun som villkor och antalarbetare är summaområdet. Vad jag inte förstår är vad @ betyder i formeln och vad som gör att det nu helt plötsligt fungerar, nu gälller ju formeln precis som jag ville för de rader som har gemensam boendekommun i raderna. Helt grymt, men jag vill förstå vad som händer och varför. Tusen tack förresten, du är grym!

[Monshi]

Okej, här kommer en förklaring.

 

när du formaterar om ett område till tabell uppdateras inte per automatik formlerna till att använda sig av tabellens egenskaper, det får du själv se till att ordna.

 

testa detta.

1. Lägg till en ny, tom kolumn till din tabell. (bara att skriva in en till rubrik i överkant
2. Skriv = i första cellen och klicka sedan på en cell i samma rad inom tabellen. Se vad resultatet blir.
3. Radera detta och skriv åter = men markera en hel kolumn bortsett från rubriken (okej, blir felvärde men det är formelns utseende som är intressant).

Helt enkelt

 

[boendeKommun refererar till hela kolumnen utom rubrikraden. Referensen utvidgas med tabellen.

[@BoendeKommun] refererar till cell i kolumnen BoendeKommun på samma rad som formeln står på.

 

Ta även en titt på Pivottabellen i arket jag bifogade ovan, egentligen behövs inte dessa formler, Pivottabellen fixar det.

[Mattias Svensson]

Okej, här kommer en förklaring.

 

när du formaterar om ett område till tabell uppdateras inte per automatik formlerna till att använda sig av tabellens egenskaper, det får du själv se till att ordna.

 

testa detta.

1. Lägg till en ny, tom kolumn till din tabell. (bara att skriva in en till rubrik i överkant
2. Skriv = i första cellen och klicka sedan på en cell i samma rad inom tabellen. Se vad resultatet blir.
3. Radera detta och skriv åter = men markera en hel kolumn bortsett från rubriken (okej, blir felvärde men det är formelns utseende som är intressant).

Helt enkelt

 

[boendeKommun refererar till hela kolumnen utom rubrikraden. Referensen utvidgas med tabellen.

[@BoendeKommun] refererar till cell i kolumnen BoendeKommun på samma rad som formeln står på.

 

Ta även en titt på Pivottabellen i arket jag bifogade ovan, egentligen behövs inte dessa formler, Pivottabellen fixar det.

 

 

Kanon, tack! Nu är ett problem löst. Jag ska göra en rätt avancerad modell i flera steg så pivottabellen kommer nog inte funka är ja rädd. För övrigt fungerade formeln =C2/SUMMA.OM(A:A;A2;C:C) lika bra ja

 

 

Nu tar vi nästa steg. jag undersöker ett antal kommuner i Sverige som mitt område, arbetskommunen är i många fall utanför detta område.

Nu vill jag sortera ut alla de arbetskommuner i kolumn B2 som inte tillhör mitt område och summera ihop de områdenas resultaten vi fick i D-kolumnen (AndelPendlaretill) som mitt summaområde, fortfarande ska vi utgå precis som i förra formeln att det är boendekommunen i A-kolumnen som bestämmer omfattningen av vilka kommuner som räknas ihop, men att det är kommuner som inte har en specifik kod i kolumn B (kommunen de pendlar till) som väljs ihop och räknas ihop, så de får ett ihopslaget värde för hur många i boendekommunen i kolumn A som pendlar utanför mitt studerade område.

Det bästa är ju i första steget att kanske döpa om de utomliggande kommunerna till t ex 999 för att lätt se att de ligger utanför mitt undersökta kommunområde. De områden jag har är 8:a siffriga, de 4 första siffrorna är den kommunens unika kommunnummer. är områdets nummer 13831234 så är 1383 det unika kommunnumret. Jag har 9 sådana nummer. Så utsorteringen ska vara alla nummer som inte börjar på 1383, 1499 osv..

Har du någon smart formel för detta? Jag har för kul testat en om-funktion för att bara bestämma vilka rader som tillhör mitt område eller ligger utanför. Då hade jag först 1383* som villkor bara för att testa utifrån ett villkor. Men det ville excel inte köpa. Betyder inte 1383* att den ska välja alla nummer som börjar på 1383.

 

kul med utmaningar, men så svårt när man är nybörjare. Är till en C-uppsats detta. Jag ska beräkna pendlingen i ett område och först bestämma lokala arbetscentrum som är områden som har lite utpendling utefter en %-sats. Sedan ska jag koppla på andra områden till det området beroende på till vilket område de har stört utpendling till. på det sättet får jag kluster, eller några sammanhängande arbetsmarknader utefter pendling.

[Monshi]

Om du vill undersöka en delmängd av värdena skulle jag lägga upp det lite annorlunda.

 

Jag skulle skapa en sekundär tabell i vilken de kommuner som ska ingå i undersökningen ska ingå.

Sedan kan man antingen använda dessa data för att i din stora tabell markera dessa kommuner eller så kan man hämta data från din stora tabell till denna för att göra uträkningarna där.

Om vi ska markera, en enkel PASSA-sats i stil med

=OM(ÄRFEL(PASSA([@Boendekommun];tbGranska[AttGranska];0));"Nej";"Ja")

där då det i kolumnen AttGranska i tabellen tbGranska står de kommunnummer som ska granskas.

 

Sedan kan du använda resultatet för att filtrerar, summera i Pivot eller summera via formler. Om formler, då har du nu två villkor och får använda SUMMA.OMF(..) men hur den ska se ut får du klura ut själva.

 

Visst, =C2/SUMMA.OM(A:A;A2;C:C) fungerar lika bra MEN den tittar på alla celler i kolumn A och det är väl onödigt?

Elegantare och säkrare att begränsa en formels omfång om möjligt.

 

mmh, kanske jag missar målet lite? Du vill sortera ut på några inledande siffror?

Om vi vet att du ska söka på fyra första, att du i tbGranska bara skriver in fyra siffror:

=OM(ÄRFEL(PASSA(VÄNSTER([@Boendekommun];4);tbGranska[AttGranska];0));"Nej";"Ja")

[Tune]

Lånar tråden lite:

 

[@BoendeKommun] refererar till cell i kolumnen BoendeKommun på samma rad som formeln står på.

 

Mina formler ser ut så här "=Kurslista_FT[[#Denna rad];[sPP Obligationsfond Säljkurs]]*Kurslista_FT[[#Denna rad];[Fondandelar2]]". Ser det olika ut i excel 2007 och 2010 eller är det någon annan skillnad. Verkar som "@" är utbytt mod "Denna rad".

[Monshi]

Mmh, Tune, jag funderade på det också när jag skrev det för ärligt, @-symbolen har jag inte sett tidigare...det brukar står [#Denna rad] när jag tänker efter.

 

Hur ser det ut för dig om du laddar ned filen jag bifogade för några inlägg sedan?

Ja, jag har Excel 2010 på denna dator.

[Tune]

Hur ser det ut för dig om du laddar ned filen jag bifogade för några inlägg sedan?

Ja, jag har Excel 2010 på denna dator.

 

Det är "Denna rad" överallt. Om jag lägger på tabell i Mattias bifogade fil så blir det också "Denna rad". Jag har excel 2007 så det verkat inte bero på vilken av 2007/2010 man använder.

[Mattias Svensson]

jag är bara lite fundersam på hur jag ska få en formel att på samma sätt som den första räkna med Boendekommun som bestämmer omfånget, jag borde kunna lägga in några fler villkor i den ursprungliga formeln för att gallra ut kommuner med specifika egenskaper. Men hur lägger man ihop dessa resultat till en cellpost för varje boendekommun och sedan tar bort överflödiga arbetskommuner som bara blir dublettrader?

Jag har nu skapat en till kolumn som visar 1 om både bokommunen, och arbetskommunen är densamma. är arbetskommunen en annan så är värdet noll Alla boendekommuner tillhör mitt område då de filtrerade direkt i mitt urval av datan.

På något sätt måste man ju kunna använda denna 1 och noll kolumn till att bestämma den cell horisontellt som ska uträknas och kolumn 1 som är boendekommun på samma sätt som i första formeln sätta den att bestämma vertikala efter varje kommun ändra till att gälla nästa osv. Är det möjligt detta? Sitta manuellt blir det inte tal om när det är 32000 rader

[Monshi]

Två saker

 

1: ta en titt på Pivot. Den kan summera/filtrerar över din nya kolumn. Jag tror, som sagt, att Pivot kan lösa många av de problem du målar upp.

2: Alternativt, använda SUMMA.OMF som kan ta flera villkor.

 

Tune: Jag undrar om det inte är en uppdatering av Excel 2010 som fixat detta för vad jag vill minnas har jag haft Denna Rad i Excel 2010 tidigare.

[Mattias Svensson]

Borde det inte räcka med filterfunktionen i varje kolumn. i filter kan man ju välja "inte lika med" och sedan följer en textruta. Kan man sedan lägga in krav om att nummer som börjar på vissa siffror ska sorteras bort och inte visas i kolumnen. Hur gör man det i så fall. För då kan jag ju köra samma formel som den första sen på de rader som blir kvar efter filtreringen. Sen kanske det finns någon funktion att ta bort alla dubletter genom att skrivs in några villkor?

[Mattias Svensson]

jag tror att får jag bara alla arbetskommuner som inte ska vara med i min studie att summeras till en rad så kommer jag lösa mycket själv sen. Nu har jag filtrerat bort alla kommuner som pendlar inom mitt undersökta område. har alltså bara de som pendlar utåt som syns i i mitt arbetsblad nu.

Nu vill jag på något sätt summera alla arbetskommuner i Kolumn B som hör till samma kommun i kolumn A. Liknande uträkning som i första formeln förutom att ingen division ska ske utan bara summering. Dock är det som gör mig förvirrad vart det svaret ska synas. skulle vilja få en ny rad som ersatte d summerade helst, att kommunnamnet i kolumn A är samma så man vet vilket svaret tillhör.Antingen att det skapas en ny rad efter varje kommunområde utefter kolumn A, för då kan man filtrera bort efter tomma celler om svaret skulle visas i en ny kolumn. Detta som förbryllar mig, sen ska jag nog kunna köra lite villkorsformer och skapa mina pendlingskluster. Hur skulle du lösa detta på enklast vis. I pivot,tabellen kan man ju filtrera å visa saker, men jag kommer ändå till just detta problem..

[Monshi]

Många olika fall du vill åt...

Inte riktigt med på vad du vill få ut, enklast vore om du tar en liten mängd data och gör ett praktiskt exempel av vad du har och vad du vill ha.

 

Ladda upp ett ark som det så tar jag mig en titt.

[Mattias Svensson]

Många olika fall du vill åt...

Inte riktigt med på vad du vill få ut, enklast vore om du tar en liten mängd data och gör ett praktiskt exempel av vad du har och vad du vill ha.

 

Ladda upp ett ark som det så tar jag mig en titt.

 

Tusen tack!

I filen jag bifogade här syns 3st boendeplats-områden i kolumn A (BoendeSAMS): 13830002, 13830003 och 13830004.

I kolumn B (PendlarTillSAMS) är de arbetsområden de pendlar till.

I kolumn C hur många antal arbetare det rör sig om.

i Kolumn D hur stor andelen är som pendlar från just det boendeområdet till just det arbetsområdet.

Och sist i kolumn E är kontrollsiffran jag fixat. Har en rad en nolla betyder det att det gäller en pendling till en plats utanför mitt undersökta område. Står det en etta betyder det att personerna pendlar inom mitt undersökta område.

 

Det jag vill är helt enkelt att summera alla rader i kolumn D "AndelPendlare" som har en nolla på sin rad för var och ett av de tre boendeområdena 13830002, 13830003 och 13830004 som finns i kolumn A.

Det jag önskar är att alla 0-rader i kolumnen E "kontrollsiffra" ska försvinna och ersättas med en rad för varje BoendeSAMS -område.

Alltså en rad där det för första kommunen står 13830002 som BoendeSAMS-område i kolumn A

I kolumn B är det lite skitsamma, t ex 0 som indikerar att det är den totala andelen som pendlar till utanför mitt undersökta område. I kolumn hur många personer det rör sig om som pendlar utanför mitt undersökta område(inte helt viktigt just nu men nice om det följer med).

I kolumn D alltså summeringen av alla 0-rader, alltså hur stor den samlade utpendlingen är utanför mitt område.

Kolumn E är skitsamma, den tas bort sen, bara med för kontroll nu.

 

Hoppas du hittar något bra sätt att göra detta på. Vill du ta bort kolumn D som jag räknade fram med din formel igår och göra en formel för allt detta från grunden, om det skulle vara lättare så får du absolut göra det

Testexempel.xlsx

[Monshi]

Ah, okej, nu tror jag att jag förstår.

 

enklast är att du sätter upp en Pivottabell (igen), lägger din kontrolsiffra som Rapportfilter, BoendeSAMS som Radetiketter och AntalArbetare som data du summerar över.

Sätt filtret till att visa enbart nollvärden. Kopiera ut resultatet till tomt cellområde, enbart värdena.

Gå sedan till din tabell, applicera filter på Kontrollsiffra, visa enbart nollvärdema och ta bort de rader som är synliga. Alternativ sortera tabellen med avseende på kontrollsiffran och ta bort dessa rader.

Läggs sedan in de data du hämtat från Pivot.

Tips: Lägg din Pivot på eget blad, låt din tabell stå ensam på sitt blad. Då är det ovan enklare att göra.

 

 

Går även, givetvis, att fixa genom formler men nog är detta enklare om än det krävs några handgrepp?

 

Lek runt med Pivot, tror som sagt du har en hel del att hämta där.

[Mattias Svensson]

Tackar, ska testa detta!

[Mattias Svensson]

Jag har nu skapat en pivot tabell. Sorterat efter nollvärdet och har nu resultatet för hur många som pendlar till 0-kommuner, alltså kommuner utanför mitt område för varje enskild kommun som är med i mitt undersökta område. Fantastiskt bra. Jag har gått in i min tabell och filtrerat att enbart visa 0-rader. Jag har raderat dessa.

 

Nu är frågan hur jag enklast lägger till mitt resultat från pivot-resultatlistan i min tabell som egna rader. Jag vill att varje resultat for boendekommunen i pivoten ska lägga sig som en egen rad i min tabell där de hör hemma, alltså som en rad i anslutning till de identiska boendekommunerna i min tabell. blir det manuellt arbete eller går de att föra in på det sättet jag vill?

 

Jag har inte kopierat resultatet av pivot-tabellen i en tom cell som du skrev, förstod inte vad det var bra för, men det kanske behövs för att detta ska funka?

[Monshi]

bara att kopiera in dina data i slutet av tabellen, helst bara värden och inga format. Då utökas tabellen per automatik. Därefter får du sortera tabellen på dina kommuner varvid de kommer att grupperas som du vill.

 

Sedan, varför inte, uppdatera din Pivot och skapa en fin rapport där

 

Anledning till att kopiera ut, tja som försäkran mot att du skulle råka uppdatera Pivot och därmed att dina data skulle försvinna.

[Mattias Svensson]

Allt fixat nu. Bara sorteringen kvar för att få allt på rätt plats. Ska sortera efter boendekommun, men visst äre så att man kan sortera på olika vis i excel. Sorterar jag bara efter boendekommun stigande, visst ändrar excel bara den kolumnen då och inte alla rader, eller hur var det? Jag vill ju att alla rader ska vara intakta..

[Monshi]

Hur menar du?

men visst, har du en tabell som

1 1

2 1

1 2

2 2

och sorterar på första kolumnen blir resultatet

1 1

1 2

2 1

2 2

 

dvs samma sortering där som tidigare.