Bräkna medeltal/summera hämtade värden för olika villkor/ olika intervall: MEDEL(LETARAD(OM(OCH(intervall))))

[bilobijelo 0]

Hej alla  !

 

Jag är ny på forumet och registrerade mig av en anledning, jag har ett problem som jag inte lyckas lösa. Jag ska försöka förklara det så bra som möjligt.

 

Jag har en ganska stor mängd data loggad under t.ex. varje timme under 1 år. Datan består sedan av olika parametrar. Som exempel så har jag medelvärde för en parameter för varje timme under året. Vilket resulterar i 8760 rader. Varje kollumn består sedan av t.ex. hur många personer som passerade en butiksdörr i olika ställen i Sverige. 

Det jag vill kunna göra är att ta ut olika perioderoch sedan summera/beräkna medeltal m.m. för önskad parameter under denna perioden.

 

EXEMPEL:

 

En butik har dåligt kundunderlag och jag vill se hur den har förändrats under perioden 1 Juni 2013 - 1 November 2013. 

Jag tänker mig då att plotta medeltal av personer som besöker en ICA butik i Skogås per dag för att kunna se förändringar i besökningsfrekvens sedan en konkurent öppnat i närheten.

 

Jag vill då använda mig först en OM(OCH()) sats för att berätta vilken period som ska användas,1 Juni 2013 - 1 November 2013 . Sedan av LETARAD för att hitta önskad period och plocka ut den parameter jag är intresserad av, besökar antal/ timme i butiken i skogås. Sedan MEDEL för att summera alla hämtade parameter värden i intervallet.

 

Min formel blir något så här:

 

=MEDEL(LETARAD(OM(OCH(A1:A8760>1 Juni 2013;A1:A8760<1 November 2013);A1:A8760;"");$A$1:$CL$8760;4;))

 

Där då

A1:A8760 = alla mätvärden för det året

1 Juni 2013 = min. värde för intervallet (skriver självklart rätt dagsnummer tex. 4125)

1 Juni 2013 = max. värde för intervallet (skriver självklart rätt dagsnummer tex. 4256)

$A$1:$CL$8760 = Hela dataområdet

4 = Den önskade ICA butiken i skogås

 

Men jag får fel, min funktion vill ej fungera !

Jag anar att felet ligger i "OCH" delen.

 

Jag har provat med

 

=SUMMA(OM(A24:A54>41275; A24:A54;""))

=SUMMA(OM(A24:A54<41275; A24:A54;""))

 

För att testa funktionerna och det fungerar. Så enkla "större än" och "mindre än" vilkor fungerar. Men inte när jag vill ha intervallet i matris/vektor form.

 

 

 

 

ÄR det någon här som vet vad som blir fel ? eller har en lämplig lösning ?

Jag vill alltså kunna ta ut olika perioder och använda olika funktioner, t.ex. medeltal som besöker butiken varje dag under en period, summering av alla som besökt butiken senaste 5 åren m.m. Man kan även tänka sig att när funktionen fungerar så kan man t.ex titta på butiker i en region och ta ut vilka datum som butikerna sammanlagt hade mer än 5000 besökare/dag för att planera resurser m.m.

 

Med vänliga hälsningar

Bilobijelo

Redigerad 6 november, 2013 av bilobijelo

[Ove Söderlund 307]

Allt går så klart att göra, men vi behöver veta lite grundförutsättningar, så här kommer ett par frågor som jag hoppas du kan besvara:

 

1. Kolumn A, raderna 1 t.o.m. 8760

Hur ser tidsstämpeln ut i den kolumnen? Något i stil med "2013-01-01 01:00" ?

Finns det alltså en rad för varje timme under hela dygnet? Om så är fallet, så bör det vara noll-värden under de timmar som butiken är stängd?

 

2. Om du vill ha medelvärden så behövs det ta hänsyn till butikens öppettider för det blir väl missvisande att beräkna på dygnsmedelvärde (summa besökare/24tim)?

[MH_resurrected 225]

Låter som om du vill använda

Medel.om(),

Eller om du har excel 2007/10

Medel.omf() för flera villkor:

 

Enklaste fallet. Din butik är i B-kolumnen

=SUMMA.OMF($B$1:$B$41271;$A$1:$A$41271;">1 Juni 2012";$A$1:$A$41271;"<1 November 2012")

=MEDEL.OMF($B$1:$B$41271;$A$1:$A$41271;">1 Juni 2012";$A$1:$A$41271;"<1 November 2012")

OSV.

Var det något liknande du tänkt dig??

 

***************

AD

***************

 

Och för att välja butik så väljer du kolumn i första delen av formeln. Dvs den här tittar i din 4:e butik (förmodligen vill  du länka den till en cell där du anger butiksnummer istället)

 

=MEDEL.OMF(FÖRSKJUTNING($A$1:$A$41271;0;4);$A$1:$A$41271;">1 Juni 2012";$A$1:$A$41271;"<1 November 2012")

 

 

 

 

***************

AD 2

***************

Man kan även tänka sig att när funktionen fungerar så kan man t.ex titta på butiker i en region och ta ut vilka datum som butikerna sammanlagt hade mer än 5000 besökare/dag för att planera resurser m.m

1. Om en rad= ett datum: 

Skriv in rubriker på ditt datablad (tid, butik1, butik2 osv)

Gör om din datatabell till en riktig tabell (infoga->tabell i excel 2010. Autofilter i tidigare excel)

Sen kan du plocka fram olika filter genom att klicka på rubrikerna

 

 

 

Om dina data ligger timvis så måste du nästan gå vägen över pivottabell.

 

1 skapa en "dagsvariabel" i databladet. T.ex genom att använda formeln 

=AVKORTA([@Tid])

eller

=AVKORTA(A1)  och kopiera ner

 

Sen skapar du en pivottabell 

Infoga-> pivottabell

Lägg DAGvariabeln som radetikett och Butik X som värden (troligtvis summa du vill se)

Nu kan du klicka på rubriken Radetiketter och välja att filtrera på både datum och data (butik1 >5000) 

 

PS: Normalt sett hade du inte behövt skapa datumkolumnen. Det hade räckt att lägga tidsvariabeln som radetikett och högerklicka på ett datum/tid i radetiketterna och välj gruppera>dagar och år.

Men då fungerar inte filtren som de skall.

 

Redigerad 6 november, 2013 av MH_resurrected

[bilobijelo 0]

Allt går så klart att göra, men vi behöver veta lite grundförutsättningar, så här kommer ett par frågor som jag hoppas du kan besvara:

 

1. Kolumn A, raderna 1 t.o.m. 8760

Hur ser tidsstämpeln ut i den kolumnen? Något i stil med "2013-01-01 01:00" ?

Finns det alltså en rad för varje timme under hela dygnet? Om så är fallet, så bör det vara noll-värden under de timmar som butiken är stängd?

 

2. Om du vill ha medelvärden så behövs det ta hänsyn till butikens öppettider för det blir väl missvisande att beräkna på dygnsmedelvärde (summa besökare/24tim)?

 

1: Egentligen är det inte en butik det handlar om. Utan det var ett exempel  

kanske var lite dum, men jag har loggade processparametrar så oftast är det ett värde i cellerna. men självklart kan anläggning ligga nere vilket ger tomma celler.

 

Tidstämpeln är som du säger per timme. 41275 nästa är 41275 + (1/24), nästa är 41275+(2/24) osv. 

 

Därför tänker jag mig att jag borde kunna använda något i stil med om41275 <A1:A8760<41289 så beräkna medelvärdet av parameter 56.

 

2: Se ovan. Egentligen så blir väl medelvärdet lika representativt om affären har öppet lika många timmar varje dag. Annars hade man väl fått nomralisera medelvärdet på något sätt. men så är som sagt inte fallet så det kan vi släppa 

 

 

 

 

 

 

 

Låter som om du vill använda

Medel.om(),

Eller om du har excel 2007/10

Medel.omf() för flera villkor:

 

Enklaste fallet. Din butik är i B-kolumnen

=SUMMA.OMF($B$1:$B$41271;$A$1:$A$41271;">1 Juni 2012";$A$1:$A$41271;"<1 November 2012")

=MEDEL.OMF($B$1:$B$41271;$A$1:$A$41271;">1 Juni 2012";$A$1:$A$41271;"<1 November 2012")

OSV.

Var det något liknande du tänkt dig??

 

***************

AD

***************

 

Och för att välja butik så väljer du kolumn i första delen av formeln. Dvs den här tittar i din 4:e butik (förmodligen vill  du länka den till en cell där du anger butiksnummer istället)

 

=MEDEL.OMF(FÖRSKJUTNING($A$1:$A$41271;0;4);$A$1:$A$41271;">1 Juni 2012";$A$1:$A$41271;"<1 November 2012")

 

 

 

 

***************

AD 2

***************

Man kan även tänka sig att när funktionen fungerar så kan man t.ex titta på butiker i en region och ta ut vilka datum som butikerna sammanlagt hade mer än 5000 besökare/dag för att planera resurser m.m

1. Om en rad= ett datum: 

Skriv in rubriker på ditt datablad (tid, butik1, butik2 osv)

Gör om din datatabell till en riktig tabell (infoga->tabell i excel 2010. Autofilter i tidigare excel)

Sen kan du plocka fram olika filter genom att klicka på rubrikerna

 

 

 

Om dina data ligger timvis så måste du nästan gå vägen över pivottabell.

 

1 skapa en "dagsvariabel" i databladet. T.ex genom att använda formeln 

=AVKORTA([@Tid])

eller

=AVKORTA(A1)  och kopiera ner

 

Sen skapar du en pivottabell 

Infoga-> pivottabell

Lägg DAGvariabeln som radetikett och Butik X som värden (troligtvis summa du vill se)

Nu kan du klicka på rubriken Radetiketter och välja att filtrera på både datum och data (butik1 >5000) 

 

PS: Normalt sett hade du inte behövt skapa datumkolumnen. Det hade räckt att lägga tidsvariabeln som radetikett och högerklicka på ett datum/tid i radetiketterna och välj gruppera>dagar och år.

Men då fungerar inte filtren som de skall.

 

Jag ska ta och testa dina förslag och sedan återkomma det är ju ganska så svårt att se vad de göra utan att få mixtra lite själv. Problemet är väl att jag främst (dock begränsad kunskap) suttit med Matlab och Java så jag "vet vad jag vill göra" men har svårt att få ihop det i Excel. Men som sagt jag ber om att få återkomma ! 

 

 

 

 

 

 

Tack så mycket båda två ! 

[bilobijelo 0]

För att förtydliga så ungefär så här ser mitt datablad ut (till vänster) Fast jag har upp till ca 400 parametrar (kolumner)

 

Sedan visar jag exempel på vad jag skulle vilja kunna göra. Tanken är att man ska kunna använda denna "funktion/lösning" på lite olika sätt bara.

 

 

Testeli test.xlsx

[bilobijelo 0]

MH_resurrected

 

Dina funktioner verkar lösa det bra. Jag sitter på jobbet med excel 2003 och där finns det så klart itne så flashiga funktioner  

Men testade hemma och det verkar fungera bra. Dock så vill den inte "ta" mina celler med datum..

 

T.ex. i min plot så vill jag sätta att vilkoren ska vara större än eller lika med den dagen som det gäller men mindre än nästa dag och sedan kunna dra ner funktionen så att räkningen blir löpande 

 

=MEDEL.OMF($C$2:$C$8785;$A$2:$A$8785;">=I19";$A$2:$A$8785;"<I20")

 

Men den vill inte "ta cellerna I19 eller I20... hmm. trotts att det är samma format på dem (datum format)

[MH_resurrected 225]

Du är nästan där :-)

Det är adresseringen av datumcellerna som blir fel. Du har satt citationstecken, så den behandlar adressen som ett stycke text. Testa så här istället:

=MEDEL.OMF($C$2:$C$8785;$A$2:$A$8785;">="&I19;$A$2:$A$8785;"<"&I20)

Eller så här om du vill ha det prydligare:

=MEDEL.OMF($C$2:$C$8785;$A$2:$A$8785;SAMMANFOGA(">=";I19);$A$2:$A$8785;SAMMANFOGA("<";I20))

 

Problemet är alltså att datumet i cellen I19 måste slås ihop med villkoret (">="). Excel är smidig, men inte övertydlig i hur den hanterar och blandar datatyper/vilkor osv... 

[MH_resurrected 225]

Excel 2003

Det här är för övrigt en variant som fungerar i Excel 2003 (Obs att du inte behöver slå ihop villkoret och cellvärdet här. Logiskt?)

 

=PRODUKTSUMMA($C$2:$C$8785*($A$2:$A$8785>=I19)*($A$2:$A$8785<I20))/PRODUKTSUMMA(($A$2:$A$8785>=I19)*($A$2:$A$8785<I20))

Det går inte att hämta medel direkt med den här metoden (eftersom de celler som inte uppfyller villkoret =0) utan man kör "summa för de som uppfyller villkoret"/ "antalet som uppfyller villkoret"=Medel

 

Sen har vi en variant som liknar det du började med. Mata in:

=MEDEL(OM(($A$2:$A$8785>=I19)*($A$2:$A$8785<I20)=1;$C$2:$C$8785;""))

 

 

 

**** Om-villkoret returnerar antingen ett tal eller en tom mängd "" så medelvärdet blir rätt direkt)****

 

 

 

Sedan trycker du [Ctrl]+[shift]+[Enter] när du står i cellen Då kommer formeln att behandlas som en matrisformel. Dvs den kör hela villkorssnurran innan den räknar medel på det som returneras. Du ser att den får måsvingar omkring sig när den omvandlas till Matrisformel

{=MEDEL(OM(($A$2:$A$8785>=I19)*($A$2:$A$8785<I20)=1;$C$2:$C$8785;""))}

Du skall alltså inte fylla i måsvingarna, det är bara excel's sätt att bekräfta att du vill använda en matrisformel.  

 Om du vill fylla nedåt så får du dra med musen. Tror inte det fungerar att kopiera. 

 

Jag brukar undvika matrisformler eftersom det smyger sig in fel så lätt (eller, jag gör dom med hjälp av produktsumma), men det är en smaksak. Du verkar ju vara inne på den typen av logik så om du måste få det att fungera i Excel 2003 så är det säkert en bra lösning.

 

 

 

***************Jag är allvarligt uttråkad. Kom på en sak************

 

Om du bara är ute efter ett dygn så kan du faktiskt förenkla dina jämförelser genom att rensa bort tidsdelen av datumen: Typ

AVKORTA($A$2:$A$8785)=I19

Vilket krymper matrisformeln till:

{=MEDEL(OM(AVKORTA($A$2:$A$8785)=I19;$C$2:$C$8785;""))}

Och produktsumman till 

=PRODUKTSUMMA($C$2:$C$8785*(AVKORTA($A$2:$A$8785)=I19))/PRODUKTSUMMA((AVKORTA($A$2:$A$8785)=I19)*1)

Tyvärr går det inte att använda direkt i medel.om osv

Redigerad 6 november, 2013 av MH_resurrected