0,5 OT - Excelfråga i Happys tjänst

Aron

Aron

Inventarie
0,5 OT - Excelfråga i Happys tjänst
Detta är bara 50% OT eftersom frågan berör ett produkttest för Happy.

Jag har ett antal datafiler med tiotusentals rader i vardera. Inför analyssteget så vill jag kasta större delen av materialet genom att identifiera det högsta värdet i varje sjok om x rader.

Ex: Antag att jag vill identifiera och markera raden med det högsta värdet i raderna från 1 till 60. Därefter identifiera raden med det högsta värdet i raderna från 61 till 120. osv tills programmet tuggat sig igenom alla tiotusentals rader. Kvar får jag då 1/60 av ursprungsmaterialet, som blir betydligt enklare att hantera.

För att tydliggöra min frågeställning ytterligare så kan vi tänka oss att varje rad motsvarar en mätpunkt i ensekundersintervall. Genom ovanstående så skulle jag kunna se vilket som var det högsta värdet under varje minut.

Jag har försökt hitta något sätt att göra detta med MAX, men får inte till något smart sätt att hantera intervallen. Det går tydligen att lösa med VBA, men eftersom jag inte är programmerare så blir det för mekigt för mig att få till anpassningen för mina behov, så jag orkar inte gå den vägen.

Hilfe! Jag hoppas att det finns något enklare sätt än VBA att lösa det jag efterfrågar?

På datorn snurrar den allra senaste versionen av Excel, den som kommer att heta 2016(?).
 
0,5 OT - Excelfråga i Happys tjänst
Min excel advisory quota är redan uppäten av jansson99. Och eftersom jag inte ens förstår frågeställningen ska jag nog undvika att svara.
 
0,5 OT - Excelfråga i Happys tjänst
Jag ser algoritmen framför mig men är för kass på att koda för att kunna skriva nåt :)
Känns väldigt typiskt som en VBA-grej.
 
0,5 OT - Excelfråga i Happys tjänst
Att få ut värdena är enkelt. Först "MAX" för varje rad, och sen kör du "MAX" på kolumnen med rad-"MAX", på var 60:e rad (med ett löpande 60-radsintervall i beräkningen). För att få till var 60:e rad kan du t ex göra en kolumn med "räknare", och sen en "IF"-sats med en modul-60-division som bara sätter ut värdet om resten är 0. Annars sätts värdet "0".

Men det innebär ju inte att raderna markeras och identifieras. Om vi antar att värdena i varje rad är heltal, så skulle du kunna ge varje rad ett unikt radnummer i en separat kolumn, och sen addera rad-"MAX" (heltal) med radnumret (som decimaler). Om värdet för en rad är 678, och det är rad 0.20281 (börja på 0.00001, sen 0.00002, osv), så blir additionskolumnens värde "678.20281", och kan då enkelt identiferas.

Om jag har fattat problembeskrivningen rätt alltså.
 
Senast ändrad:
0,5 OT - Excelfråga i Happys tjänst
geologen skrev:
-------------------------------------------------------
> Att få ut värdena är enkelt. Först "MAX" för
> varje rad, och sen kör du "MAX" på kolumnen med
> rad-"MAX", på var 60:e rad (med ett löpande
> 60-radsintervall i beräkningen). För att få
> till var 60:e rad kan du t ex göra en kolumn med
> "räknare", och sen en "IF"-sats med en
> modul-60-division som bara sätter ut värdet om
> resten är 0. Annars sätts värdet "0".
>
> Men det innebär ju inte att raderna markeras och
> identifieras. Om vi antar att värdena i varje rad
> är heltal, så skulle du kunna ge varje rad ett
> unikt radnummer i en separat kolumn, och sen
> addera rad-"MAX" (heltal) med radnumret (som
> decimaler). Om värdet för en rad är 678, och
> det är rad 0.20281 (börja på 0.00001, sen
> 0.00002, osv), så blir additionskolumnens värde
> "678.20281", och kan då enkelt identiferas.
>
> Om jag har fattat problembeskrivningen rätt
> alltså.


Jag hoppas att du lyfter fram sånt här när du söker jobb geologen? :)
 
0,5 OT - Excelfråga i Happys tjänst
Lulle skrev:
-------------------------------------------------------
> geologen skrev:
> --------------------------------------------------
> -----
> > Att få ut värdena är enkelt. Först "MAX"
> för
> > varje rad, och sen kör du "MAX" på kolumnen
> med
> > rad-"MAX", på var 60:e rad (med ett löpande
> > 60-radsintervall i beräkningen). För att få
> > till var 60:e rad kan du t ex göra en kolumn
> med
> > "räknare", och sen en "IF"-sats med en
> > modul-60-division som bara sätter ut värdet
> om
> > resten är 0. Annars sätts värdet "0".
> >
> > Men det innebär ju inte att raderna markeras
> och
> > identifieras. Om vi antar att värdena i varje
> rad
> > är heltal, så skulle du kunna ge varje rad
> ett
> > unikt radnummer i en separat kolumn, och sen
> > addera rad-"MAX" (heltal) med radnumret (som
> > decimaler). Om värdet för en rad är 678, och
> > det är rad 0.20281 (börja på 0.00001, sen
> > 0.00002, osv), så blir additionskolumnens
> värde
> > "678.20281", och kan då enkelt identiferas.
> >
> > Om jag har fattat problembeskrivningen rätt
> > alltså.
>
>
> Jag hoppas att du lyfter fram sånt här när du
> söker jobb geologen? :)

Det är nog snarare sånt som att kunna VBA som är nåt att hålla fram.
Eller sånt som Mattias A skrev.
 
0,5 OT - Excelfråga i Happys tjänst
geologen skrev:
-------------------------------------------------------
> Lulle skrev:
> --------------------------------------------------
> -----
> > geologen skrev:
> >
> --------------------------------------------------
>
> > -----
> > > Att få ut värdena är enkelt. Först "MAX"
> > för
> > > varje rad, och sen kör du "MAX" på kolumnen
> > med
> > > rad-"MAX", på var 60:e rad (med ett löpande
> > > 60-radsintervall i beräkningen). För att
> få
> > > till var 60:e rad kan du t ex göra en kolumn
> > med
> > > "räknare", och sen en "IF"-sats med en
> > > modul-60-division som bara sätter ut värdet
> > om
> > > resten är 0. Annars sätts värdet "0".
> > >
> > > Men det innebär ju inte att raderna markeras
> > och
> > > identifieras. Om vi antar att värdena i
> varje
> > rad
> > > är heltal, så skulle du kunna ge varje rad
> > ett
> > > unikt radnummer i en separat kolumn, och sen
> > > addera rad-"MAX" (heltal) med radnumret (som
> > > decimaler). Om värdet för en rad är 678,
> och
> > > det är rad 0.20281 (börja på 0.00001, sen
> > > 0.00002, osv), så blir additionskolumnens
> > värde
> > > "678.20281", och kan då enkelt identiferas.
> > >
> > > Om jag har fattat problembeskrivningen rätt
> > > alltså.
> >
> >
> > Jag hoppas att du lyfter fram sånt här när
> du
> > söker jobb geologen? :)
>
> Det är nog snarare sånt som att kunna VBA som
> är nåt att hålla fram.
> Eller sånt som Mattias A skrev.


Ja, iallafall med den attityden.

Edit: Men helt seriöst, det är vanligare att man arbetar med excel än google calc. Visst är det bra att kunna visual basic, men i många fall (typ detta) räcker det säkert lika bra med någon som har hyfsad koll på excel. Hade Aron haft dig tillgänglig på arbetsplatsen hade du sparat honom både tid och frustration, eller hur?

Dessutom är jag helt övertygad om att du själv skulle kunna lära dig grunderna i VBA på bara några kvällar med lämplig "online tutorial". Tänket verkar du ju redan ha.
 
Senast ändrad:
0,5 OT - Excelfråga i Happys tjänst
Mattias A skrev:
-------------------------------------------------------
> I Google Sheets är det bara att köra
>
> =MAX(OFFSET($B$1,(ROW()-1) * 5,0,5))
>
> där 5 är längden på fönstret


Tack Mattias (och övriga)! Den där formeln ser ut att vara vad jag letar efter. Jag får det dock inte att funka som tänkt. När jag kör ditt exempel i Excel så stämmer det. Men när jag stoppar in mina egna siffror blir det konstigt.

Anomaliteten:
På första raden returnerar den resultatet från rad sju i listan nedan. Det borde ju ha varit siffrorna från rad två.

Nu är det läggdags så jag får klura på det i morgon. Men om någon har tråkigt så ser min (översatta) formel ut som så: =MAX(FÖRSKJUTNING($H$2;(RAD()-1) * 5;0;5))

De 20 första raderna i den aktuella kolumnen (H):

Superhemliga mätdata
1,380400792019320
1,520928305436360
1,489490217382410
1,371621801930970
1,247145191049140
1,161793838065990
1,148684701803990
1,111721414533860
0,992338222245342
0,851439546290660
0,840467707205953
0,891585220623773
0,978957556818875
1,058093172023030
1,060692098085860
1,047364721764060
1,029408898491710
1,003423269517730
1,001769303098630

Ovanstående ska för övrigt stuvas in i en villkorsstyrd formatering. Så långt har jag inte kommit, men om någon redan nu ser att jag kommer att stöta på patrull så får ni gärna hojta till.
 
0,5 OT - Excelfråga i Happys tjänst
Jag gissar att du börjar på rad 2 i ditt datablad (eftersom du använder $H$2). Isåfall ska du byta ut "rad()-1" till "rad()-2" eftersom den använder nuvarande rad för att beräkna förskjutningen.


PS. Utan att veta ett dugg om ditt mätdata eller vad du ska göra med det, finns det väl en risk att du verkligen får ut max av varje rad, dvs outliers och i värsta fall bara räknar på outliers, tex om du råkar ha ett mätfel med väldig höga värden i varje 60-intervall kanske det är det som räknas på. Men det kanske precis är det du vill åstadkomma:)
 
0,5 OT - Excelfråga i Happys tjänst
Aron skrev:
-------------------------------------------------------

> Superhemliga mätdata
> 1,380400792019320
> 1,520928305436360
> 1,489490217382410
> 1,371621801930970
> 1,247145191049140
> 1,161793838065990
> 1,148684701803990
> 1,111721414533860
> 0,992338222245342
> 0,851439546290660
> 0,840467707205953
> 0,891585220623773
> 0,978957556818875
> 1,058093172023030
> 1,060692098085860
> 1,047364721764060
> 1,029408898491710
> 1,003423269517730
> 1,001769303098630

HA! Visste väl att jag skulle vara med i datan!
 
0,5 OT - Excelfråga i Happys tjänst
42Hz skrev:
-------------------------------------------------------
> Aron skrev:
> --------------------------------------------------
> -----
>
> > Superhemliga mätdata
>
> HA! Visste väl att jag skulle vara med i datan!

Klart du är!
Det är uträkningar av odds för vaden om när Duden ska landa....
 
0,5 OT - Excelfråga i Happys tjänst
Mattias A skrev:
-------------------------------------------------------
> I Google Sheets är det bara att köra
>
> =MAX(OFFSET($B$1,(ROW()-1) * 5,0,5))
>
> där 5 är längden på fönstret
>
> https://docs.google.com/spreadsheets/d/1HXBa9B1So9
> VmRcAdy6QzZQt9HaYM8nd47iXa9DVl3XI/edit?usp=sharing
>
> Förtydligande: Ovanstående fungerar precis lika
> bra i Excel då sån här enklare funktionalitet
> är identisk.

Detta kändes väldigt smart, men jag får det inte att funka. Var 6e rad räknas inte med. Dvs, om jag sätter ett stort tal på rad 6, 12, 18 osv, så kommer det inte med i max-beräkningen. Det ser ut att funka i google sheets dock, så något glappar nog när jag försökt återskapa i excel.

Hur eller hur, att sitta med några tiotusen rader i excel är standard för mig. För att behandla mätdata brukar jag använda mig att tidskoloumnen, som vanligtvis finns. Vanligt är exempelvis att göra sekundvärden till minutvärden, eller timvärden till dygnsvärden. AVERAGEIF med koppling till tidskolumn brukar jag tillämpa då, och på så vis bygga en ny tidsserie.

Tyvärr finns inte någon MAXIF funktion, men du kan skapa arrayfunktioner (som du stänger med ctrl+shift+enter). Exempelvis =MAX(IF( tid = inom önskad tid)) ... typ :)

Ursäkta lite svamligt (stressigt skrivet).
 
0,5 OT - Excelfråga i Happys tjänst
cyklariskogen skrev:
> PS. Utan att veta ett dugg om ditt mätdata eller
> vad du ska göra med det, finns det väl en risk
> att du verkligen får ut max av varje rad, dvs
> outliers och i värsta fall bara räknar på
> outliers, tex om du råkar ha ett mätfel med
> väldig höga värden i varje 60-intervall kanske
> det är det som räknas på. Men det kanske precis
> är det du vill åstadkomma:)


Precis, kanske bättre att använda en percentil. Eller ta slumpvisa värden rakt över. Eller använda allt data såklart. Men det beror ju helt på data och frågeställning.
 
0,5 OT - Excelfråga i Happys tjänst
VBA-lösning (alt + F11. Välj Insert Module. Klistra in). Resultatet läggs i kolumn B så ha inget viktigt där.

Sub find_max()

Dim rng As Range
Dim maximum As Double
Dim x As Double

x = 1
y = 1

Do
Set rng = Sheet1.Range("A" & x, "A" & x + 58)
maximum = Application.WorksheetFunction.Max(rng)
'Displays largest value
'MsgBox maximum
Range("B" & y).Value = maximum

x = x + 59
y = y + 1

Loop Until Range("A" & x).Value = ""


End Sub
 
0,5 OT - Excelfråga i Happys tjänst
Med cyklariskogens tillägg så har jag fått det att funka.

Nästa steg är att jag vill markera de aktuella raderna, dvs de med högst värden i n rader. Jag har experimenterat med villkorsstyrd formatering, men får inte till det.

Har någon ett tips på hur jag markerar de utvalda raderna?
 
0,5 OT - Excelfråga i Happys tjänst
Aron skrev:
-------------------------------------------------------
> Nästa steg är att jag vill markera de aktuella
> raderna, dvs de med högst värden i n rader. Jag
> har experimenterat med villkorsstyrd formatering,
> men får inte till det.
>
> Har någon ett tips på hur jag markerar de
> utvalda raderna?

Ingen aning hur det "grafiska" fungerar men ett steg som skulle kunna vara åt rätt riktning är att plocka ut referenser till cellerna med högst värde istället för värdet. http://www.excel-easy.com/examples/locate-maximum-value.html

Efter det kanske det finns något smart sätt att formatera celler givet en lista med referenser till cellerna som ska formateras.
 
0,5 OT - Excelfråga i Happys tjänst
1) Markera kolumnen du vill formatera och välj sedan villkorsstyrd formatering.
2) Skapa ny regel. Välj "Classic", sedan "Use a formula..." (vad det nu heter på svenska)
3) Använd formeln "=IF(VLOOKUP(A1; $D$1:$d$4;1;FALSE)=A1;1;0)" där du byter ut A1 mot H2 (du verkar ha din data i kolumn H, och byt ut "$D$1:$d$4" mot den kolumn där du har dina maxvärden. Har inte en aning om vad IF och VLOOKUP heter i svenska excel.

Typ.
 

Senaste inläggen

Köp & Sälj

Ny annons

Artiklar

Tillbaka
Topp