Hjälp med excel formel

[Mattias Svensson]

Grymt! Nu är allting ordnat och uppställt som jag vill ha det. Har gjort stickprover och kollat allt.. Nu börjar själva räknandet av modellen i att skapa lokala arbetsmarknader. Ska dela in alla områden i 5 kategorier beroende på om de är ett arbetscentrum, eller om de tillhör någon av de 4 leden under som ska bilda dessa kluster. Lär bli lite formler, men ska testa. Skriver om ja fasttnar. Den första formeln har jag ju nu i alla fall, TACK

[Monshi]

jag säger igen, Pivot... första formeln behövs egentligen inte, pivot löser ju faktiskt allt... men kör på och hör av dig om det blir problem

[Mattias Svensson]

jag har en tabellfråga nu faktiskt och det gäller sortering. Det är samma tabell som jag bifogat innan. i kolumn A är det boende område, i kolumn B det område de pendlar till och i kolumn C andelen som pendlar från varje områdeskod till ett annat område.

jag vill kunna först sortera i kolumn A så att bara ett områdesnummer av varje visas. I kolumn C vill jag inte se summan av andelen pendlare till den kommunen som jag får fram nu i pivot som ju är 1 för alla områdeskoder naturligtvis. Utan jag vill i Kolumn C att excel visar det högsta värdet inom varje områdeskod. I kolumn B vill jag att det visas den områdeskod som tillhör det högsta värdet i kolumn C. Är du med? Det är lite samma sak vi gjorde innan för att summera alla nollor för varje områdeskod, men nu vill jag se det hösta andelsvärdet som pendlar ut för varje kommun och vilket område som det andelsvärdet tillhör.

Är du med?

[Monshi]

Tre alternativ

 

1. Pivot, åter igen. Sätt boendekommun som radetikett, antal pendlare som värden och sätt värdefältsegenskaperna till att visa Max istället för summa.
2. Delsumma. ta bort tabellformateringen och applicera verktyget delsumma, egenskap max på boendekommun
3. Formler, kopiera ut unika kommunnummer till en ny tabell och en matrisformel som dock kommer slöa ned ditt ark troligen.

Rekommendationen är Pivot fast vill du får fram vilket området det hör till också...fasiken, då spricker Pivot och Delsumma då blir det formel, matrisformel...

Något i stil med

=PRODUKTSUMMA((MAX((F2=Tabell5[boendekommun])*(Tabell5[antalArbetare]))=(F2=Tabell5[boendekommun])*(Tabell5[antalArbetare]))*RAD(Tabell5[boendekommun]))

Där det då i F2 står det kommunnummer du vill få fram raden för dessa maxvärde.

Okej, det går att lösa på annat vis, med lite formler utmed tabellen. Kanske enklare om tabellen är sorterad...

 

Eller kanske om man tar hjälp av Pivot för att ta ut värdena... men nej, testa det ovan och se hur seg din bok blir.

 

Formeln returnerar alltså den rad som max-värdet står på, sedan får du använda INDEX för att hämta värdet och kommunen. Tänk bara på att det är radnumret, inte något relativt index som returnerats.

[Mattias Svensson]

tusen tack!

[Mattias Svensson]

Förresten,vad är knepet när man ska kopiera mellan tabeller varvin den ena är filtrerad men inte den andra. Jag har filtrerat en tabell, kopierat den till ett nytt blad. Nu vill jag kopiera tillbaka till orginalbladet. Jag vill klistra in svaren i den fortfarande filtrerade tabellen. Men då kopierar den in på radnumrena vilket gör att det blir helt fel. Hur kopierar man in i en filtrerad tabell så att den kopierar in som cellerna ligger i tabellen och inte utefter radnumret. När man filtrerat har ju tabellen kvar samma radnummer bara att den döljer de radnummer som filtrerats bort.. hoppas du förstår

[Monshi]

Tänk vad en runda i skogen kan ge, nya krafter, nya tankar.

Nu ska då få en bättre lösning än den jag gav i mitt förra inlägg.

 

Lägg till en ny kolumn till din tabell, kalla den Max exempelvis. I denna skriv

=OM(ANTAL.OMF([boendekommun];[@Boendekommun];[antalArbetare];">" &[@antalArbetare])=0;"Max";"")

(men byt ut @ mot denna rad om Excel 2007)

Då kommer det stå Max i de rader där du har max för en kommun.

Sedan tar du och skapar dig en Pivot och tar fram alla kommuner som har ordet Max i denna kolumn.

Enda notering, om två poster har för en kommun delar maxvärde kommer våda på max.

 

Åter är vi där, en tabell, en enkel formel utmed den och en Pivot som fixar rapporten.

 

 

Du vill kopiera in värden in i en filtrerad tabell? Det tror jag ärligt talat inte går, du får finna annan väg runt detta problem. Om du klistrar in över område med rader dolda av en filtrering skrivs värdena in över de rader som är dolda som du märker. Så är det bra.

 

En lösning kan vara att skriva in ett sorteringsindex på varje rad, sortera allt lika och klistra in det. Fast nej, fungera kanske inte så bra.

Eller använd formler för att hämta data, typ PASSA-INDEX om möjligt.

[Mattias Svensson]

Mycket bra!

En sak jag undrar i pivot bara. jag har bokommunerna som radettikett.. jag har bredvid andelen pendlare inställd på max. Men jag skulle vilja ha en till kolumn bredvid där man ser en till värde från samma rad som andelen pendlare man f¨r som resultat men från en annan kolumn så jag t ex kan se vilken kommun det är individerna pendlar till som maxvärdet visar, går inte det? Sen kan man lägga in rapportfilter och filtrera bort de som pendlar utanför mitt område och även få bort de som jobbar inom samma kommun. men skulle just vilja kunna få fram en kolumn till för att få mer infå om maxvärdet den visar.. ett resultatvärde säger lixom inget vad det värdet gäller..

[Monshi]

Inte helt lätt att se framför mig vad du menar men kanske är det något som detta:

[Mattias Svensson]

Hur får du till fler kolumner i pivot så där?

[Monshi]

Se bifogad fil

KommunExempel.xlsx

[Mattias Svensson]

jag får det bara tll att bli underrubriker, inte kolumner brevid varandra som dig..

[Monshi]

Testa de olika formaten som en Pivot kan ha. Den där Pivottabellen är i tabellformat.

 

Tror inte det har ändrats emellan Excel 2007 och 2010.

[Mattias Svensson]

Tack! Nu har jag fått ut alla kriterier och värden till min pendlingsmodell och nu börjar processen att dela in alla områden i 4 kategorier utefter hur deras utpendling ser ut. För detta kan jag inte använda pivot utan måste använda formler så jag kan köra detta som skript.

Modellen jag skapat ser helt enkelt ut som såhär: först bestäms områden som blir varje arbetsmarknads centrum. För att bli det måste området uppfylla 2 kriterier, pendlingen inom sitt område måste vara minst 20% och utpendlingen till ett annat specifikt område får inte överstiga 9%.

När dessa centrum bestämts och som ska ha "typ11" skrivet i en kolumn så ska övriga områden kopplas i en länk till dessa centrum. Först har vi typ20-områden som har sin största utpendling till typ11-områden. Sedan har vi typ 30-områden som är de områden som har sin största utpendling till en typ20kommun och den 4:e kategorin är typ50-områden som då har sin största utpendling till ett typ30område.

På detta vis får jag ett antal fristående arbetsmarknader som innehåller områden som länkas samman i och med deras pendling.

 

Jag vill göra ett skript så att jag först kan bestämma vilka områden som är centrum. De ska alltså uppfylla dels villkoren om att pendlingen inom sitt område måste vara minst 20% och utpendlingen till ett annat specifikt område får inte överstiga 9%. Det första villkoret hittar man bland 0-värdena i Kolumn D vilket innebär att boende och arbetsområde är samma, är den andelen pendling över 20% är första villkoret uppfyllt. Det andra villkoret att största utpendling inte får överstiga 9% hittar vi bland de områden som både har värdet 1 i C-komumnen och 1 i D-kolumnen. Värdet noll i C-kolumnen innebär att det de raderna gäller utpendling till områden utanför mitt undersökningsområde. Värdet 0 i D-kolumnen innebär att bo och arbetsområde är samma, så de ska i detta kriterium heller inte vara med. Nu ska vi alltså ha bestämt vilka områden som är centrum och det vill jag ska visa sig genom el kolumn där det står "typ11" i de rader som uppfyller dessa krav.

 

Steg två är att koppla resterande områden till dessa centrum på sättet jag beskrev i början. rader med 0-värden i kolumn C ska inte räknas med då jag inte undersöker pendling utanför mitt undersökningsområde, inte heller de områden som redan utsetts till ett typ11-område får vara med här då de redan fått en värdekategori. De områden som har störst utpendling till typ11-områden ska visas i en kolumn där det i raden står "typ20".

När typ20-områdena är bestämda ska typ30-områdena bestämmas på samma sätt. har området störst utpendling till ett typ20-område ska det i kolumnen alltså för den raden stå "typ30".

Och samma sätt bestäms typ50-områden, de har sin största utpendling till typ30-områden.

 

Denna process sker alltså i 4 steg men där steg 2-4 i princip går till på samma vis. Förutom att det i en kolumn ska stå vilket typ av område varje rad/område är är så vill jag för typ20,30 och 50-områdena att det i en kolumn ska stå vilket område de har sin största utpendling till, har de störst utpendling till ett typ20-område vill jag att det står vad det området har för nummer, detta för att jag ska kunna länka ihop dem så de bildar en arbetsmarknad. Typ11-områdena har inte något sådant värde då de är centrum och inte länkas av utpendling till andra områden.

 

Hoppas du kan hjälpa mig med dessa skript. Efter det är mitt excel-jobb färdigt och jag ska visualisera det i GIS sen med kartor och börja analysarbetet av mina kartor.

 

Jag bifogar ett excel-blad som visar lite hur det ser ut.

SkripsModellExempel.xlsx

[Monshi]

Du borde nog ha dina data i en databas och inte i Excel. Det borde jag kanske sagt redan efter första inlägget. Några SQL-satser skulle nog lösa detta på nolltid. Okej, nu är jag ingen fena på SQL ska sägas men jämförelser av denna art är en barnlek där.

 

Det vore lite enklare att förstå vad du vill om du kortade ned texten lite, lite mer explicita exempel.

Som jag tolkat det

Typ11

Pendling inom område (samma kommun) > 20%

Pendling till annat område <9%

 

Typ20

Största enskilda utpendling ska vara till typ11

 

Typ30

Största enskilda utpendling till typ20

 

Typ50

Största enskilda utpendling till typ30

 

Vad jag kan se ur de data du gav finns där inge typ11-områden och därmed inga andra heller.

Typ11 kan jag få ut via

=OM([@BoendeSAMS]=[@PendlarTillSAMS];OM(OCH([@AndelPendlareTotalt]>0,2;PRODUKTSUMMA(MAX(([@BoendeSAMS]=[boendeSAMS])*([@BoendeSAMS]<>[PendlarTillSAMS])*[AndelPendlareTotalt]))<=0,09);"ja";"nej");"")

 

fast sedan blir det nog lite för komplexa formler faktiskt för att gå den vägen.

Som sagt, en databas.

 

men resultatet, vill du ha alla hittade typ-kommuner i en egen tabell?

[Mattias Svensson]

Du har förståt helt rätt. Och angående att du inte hittade några typ 11-områden med de kriterierna så kan det vara för att i exemplet har du bara data för typ 5-6 områden av de totalt 333 som jag har, så kan bero på det. Du kan testa att ändra värdena så någon uppfyller detta kriterierna

 

 

När alla typ11-områden är bestämda så ska typ20 väljas. De ska ha villkoret att inte räkna bland de områden som redan valts till typ11 och de ska heller inte leta bland områden utanför mitt område, det vill säga 0-värden i C-kolumnen, alternativt att man tar bort de raderna helt då de inte kommer vara med i denna tabell. För att bli en typ20 ska området ha sin största utpendling till ett typ11-område. Sedan likadant för uträkningen typ 30 och 50-område.

Resultaten vill jag när allt är klart ha i 2 kolumner. en kolumn där det står vilken typ varje område det är och en kolumn där det står vilket omrpdesnummer det är det området har den största utpendlingen till. är området ett typ20-område ska det stå typ20 i ena kolumnen och numret på den typ11-område de har sin största utpendling till.

 

SQL kan jag nada om, sorry..

[Mattias Svensson]

Jag har nu kollat lite närmare. Område 13830003 i exemplet borde bli ett typ11-område 21.42% pendling inom sitt område och uppfyller första kriteriet. Det har sin största utpendling till annat specifikt område på 7.14% och uppfyller även här kriteriet. Observera att raderna med 0 i C-kolumnen ej ska vara med i uträkningen, de med kod 999 "pendlartillSAMS"-kolumnen. För de raderna är den totala utpendlingen utanför mitt område och ska inte räknas med. Den raden har för detta område värde 14.28% och gör att svaret blir nej om det räknas med. Tror felet ligger där i din formel, förslag på ändring?

[Monshi]

Okej, 999-områdena funderade jag över, det förklarar.

 

Dina 0-1 kolumner hjälper egentligen inte, man måste hålla koll på områdesnumren och alla data kan man väl få ur dessa?

Måste bara tänka rätt och det kommer blir ganska massiva formler kan jag varna.

[Monshi]

Okej, so far so good...

 

titta på detta

Kopia av SkripsModellExempel.xlsx

 

Fasiken, det blir komplexa formler men jag hoppas du begriper dem.

 

Produktsumma är en matrisformel, alla likheter evalueras rad för rad, multipliceras rad för rad för att till sist summera över den resulterande vektorn.

Kontrollera att det blivit rätt det jag gjort, nästa steg är i nästan samma formler igen.

 

ja, som sagt, matrisformler är beräkningsintensiva. Hoppas du har en snabb dator som kan beräkna detta över alla 33 000 rader du har.

[Mattias Svensson]

Ett stort tack, ska kolla på detta. Men sedan vi grupperade alla utpendlingsområden till en grupp för varje område är jag nu nere i 16000 rader

[Mattias Svensson]

Första formeln verkar funka strålande av det jag sett hittills när jag applicerade den på hela mitt jobb..

 

Formeln för att bestämma typ 20 områden verkar inte riktigt korrekt dock. Den formeln ska leta upp enbart det högsta värdet av utpendling bland varje område i A-kolumnen och bestämma om den raden har sin största utpendling till ett typ 11-område eller ej. Så denna formel ska kolla på alla det högsta värdet för varje område från kolumn A förutom de som har kod 999 i B-kolumnen och den får heller inte leta bland områden som redan utnämnts som typ 11-område, samt inte heller till raderna där BoendeSams och PendlarTillSams är lika, för pendlar individerna inom området kan jag inte koppla det till ett annat område.

Jag ändrade svaret i första formeln för att bestämma typ11-kommuner från "ja" till "Typ 11".

 

I den formeln du nu gjort för att bestämma vilka som är typ 20-områden har formeln valt ut 2 rader i ett och samma område och då inte det högsta utpendlingsvärdet alla gånger i det området.

T ex för det första området 1383002 ser vi att det inte är ett typ 11-område vilket kan kvalificera detta område tiull att bli ett typ20-område om största utpendlingen visar sig vara till ett typ 11-område. Då ska formeln kolla efter raden med den högsta upendlingsvärdet, som är 0.08 på rad 3 vad jag kan se. Denna blir störst då raderna med 999-områdena inte ska räknas med, heller inte områdena där BoendeSams och PendlarTillSams är lika, för pendlar individerna inom området kan jag inte koppla det till ett annat område.

istället för att se ett värde i olumnen "målområde typ11?" skulle jag vilja se numret på det området största utpendlingen är till om det är kvalificerat som typ20-område. I den andra kolumnen som du skapade "typ20" kan det vara precis som nu att det står "ja" för att visa att det är ett typ20 område som har sin största utpendling till ett typ11-område, men att man i kolumnen före som sagt ser i denna rad vilket typ 11 område(områdesnumret) detta typ 20-område har största utpendling till.

 

Det är nära nu! Tusen tack för att du tar dig tid!

[Monshi]

Typ20, ger den formeln inte rätt svar?

Den går i två steg

1: tar ut pendlingsvärdet för de poster som har pendlare till ett typ11-område.

2: Kontrollerar om detta är max-värdet... ahha nu ser jag, det ska vara maxvärdet av alla pendlingar ut från området, inte bara de som är typ20-kanditater från steg 1.

 

Uppdatera steg två formeln till

=OM([@[Målområde typ11?]]>0;

OM(PRODUKTSUMMA(MAX(([@BoendeSAMS]=[boendeSAMS])*([PendlarTillSAMS]<>999)*([@BoendeSAMS]<>[PendlarTillSAMS])*[AndelPendlareTotalt]))=[@[Målområde typ11?]];"ja";"");"")

 

se

Kopia av SkripsModellExempel.xlsx

då hittar jag en post som stämmer in på rad 188.

Dock ser man att det kanske inte är helt sant, se på rad 189..

 

detta vore mycket enklare med fiktiva data, dvs en begränsad mängd data där jag vet vilket utfallet ska vara.

Då kan jag enkelt se om formeln ger rätt svar.

[Mattias Svensson]

Rent spontant känns det som det börjar bli rätt nu, ska kontrollera lite mer bara.. med att bestämma typ 30 och 50 blir samma formen som för typ 20 men att man ändrar på några kolumnvärden i formeln?

[Monshi]

Nja, nästan. I samma stil men måste tänka igenom villkoren.

 

Kontrollera att denna formel gör rätt först.

För Typ30 stämmer detta:

Största utpendling ska ske till typ20. Största av alla pendlingar som inte går till egna området, typ11 område samt själv inte redan är typ20 eller typ11.

[Mattias Svensson]

Av allt jag gått igenom verkar detta fungera och vara riktigt.. Formlerna blir så långa nu så svårt att hänga med.

 

Hur ser formeln för typ 30 respektive typ 50-områden ut? Jag känner att jag behöver jämföra alla formler och verkligen lära mig hur de är uppbyggda och fungerar. Sen vill jag kontrollera så att alla områden får ett värde så att allt verkar stämma och bli rätt hela vägen..

 

Förklara gärna lite kort hur alla formlerna är uppbyggda och vad som skiljer dem med så är du riktigt schysst, det är du ändå, grym hjälp och jag lär mig massor på vägen!