Excel - summa.omf Hur ska jag skriva i formeln för att identifera ett ord i en cell?

Trädvy Permalänk
Medlem
Registrerad
Maj 2013

Excel - summa.omf Hur ska jag skriva i formeln för att identifera ett ord i en cell?

Hej!

Mitt bekymmer är att jag vill kunna identifier olika ord, datum eller siffror i en cell för att den ska utifrån de villkoren summera antalet.

Jag ska försöka beskriva med ett exempel:

Jag använder just nu =summa.omf() för att koppla ihop olika blad. Syftet är att i en matris summera utifrån ett antal ställda villkor, så långt går allt bra. Problemet blir när jag ska formulera villkoret att alla rader med t.ex. datumet 2016-03-01 ska summeras. Kolumnen innehåller datum och tid d.v.s. 2016-03-01 08:00.
Jag har testat lite olika med asterisker o.s.v. men får det inte att fungera.
Det kan se ut som följer:
A B
1 2016-03-01 08:00 2000
2 2016-03-02 06:00 1000
3 2016-03-01 08:00 2000
4 2016-03-02 06:00 1000

Jag vill då kunna få summan av 2016-03-01 till 4000 och 2016-03-02 2000.

Sulle då se ut såhär =summa.omf(B1:B4;A1:A4;HÄR JAG inte får till det, testat olika: "*2016-03-01*", "2016-03-01*, A1 etc.

Skulle vara tacksam för all hjälp!

Trädvy Permalänk
Medlem
Plats
Romelanda
Registrerad
Nov 2009

=left(A1,10) Borde plocka ut bara datumet.
Kan heta =vänster på svenska kanske?

"When I get sad, I stop being sad and be awsome instead, true story."

Trädvy Permalänk
Medlem
Plats
Pangea
Registrerad
Jun 2007

Ta en titt här: http://www.techonthenet.com/excel/formulas/sumif.php
det är =summa.omf() som är svenska motsvarigheten. Tänk på att amerikanska Excel använder komma istället för semikolon för att separera argument, du får skriva ; där guiden använder ,.

Antec Eleven Hundred | Be Quiet! 580W Modular | i5 3570k@4.2 GHz | Corsair H60 v2 | ASUS Sabertooth Z77 | ASUS Strix GTX 970 | 16GB Corsair Vengeanance | 250GBx2 SSD, 5TB HDD | 2x BenQ RL2450HT | Logitech G710 & G700 & G13 & G533
* De gav mig en present. Jag gav dem en present. Vet du inte skillnaden, använd "dom".
* Fraktfritt på Inet för SweC-medlemmar

Trädvy Permalänk
Medlem
Registrerad
Maj 2013
Skrivet av Json_81:

=left(A1,10) Borde plocka ut bara datumet.
Kan heta =vänster på svenska kanske?

Okej, det är jag med på, men det jag vill uppnå är att summera alla volymer 2016-03-01 så behöver inte plocka upp datumet utan endast selektera på det då det är villkoret. Eller missförstår jag hur du menade nu?

Trädvy Permalänk
Medlem
Plats
Stockholm
Registrerad
Maj 2007

Jag skulle tro att det enklaste sättet att göra summeringen är att dela upp kolumn B i 3 kolumner: datum, tid och värde.

Därefter gör du en pivottabell på datan och där kan du enkelt göra summeringen du är ute efter.

Lycka till

Trädvy Permalänk
Medlem
Plats
Romelanda
Registrerad
Nov 2009
Skrivet av sirWayne:

Okej, det är jag med på, men det jag vill uppnå är att summera alla volymer 2016-03-01 så behöver inte plocka upp datumet utan endast selektera på det då det är villkoret. Eller missförstår jag hur du menade nu?

Om jag förstår vad du vill uppnå rätt använder du funktionerna ihop.
=sumif(left(A:A)=datum osv.

"When I get sad, I stop being sad and be awsome instead, true story."

Trädvy Permalänk
Medlem
Registrerad
Maj 2013
Skrivet av Flippson69:

Jag skulle tro att det enklaste sättet att göra summeringen är att dela upp kolumn B i 3 kolumner: datum, tid och värde.

Därefter gör du en pivottabell på datan och där kan du enkelt göra summeringen du är ute efter.

Lycka till

Ja, det var min första tanke också, men det är inte jättesmidigt särskilt som det kan röra sig om 35 000-40 000 rader och proceduren ska upprepas med återkommande interval typ en gang varannan vecka. Har dessutom lite andra former där jag också skulle behöva en lösning på detta och slippa separera celler.

Trädvy Permalänk
Medlem
Plats
Romelanda
Registrerad
Nov 2009
Skrivet av sirWayne:

Ja, det var min första tanke också, men det är inte jättesmidigt särskilt som det kan röra sig om 35 000-40 000 rader och proceduren ska upprepas med återkommande interval typ en gang varannan vecka. Har dessutom lite andra former där jag också skulle behöva en lösning på detta och slippa separera celler.

Uppdelning kan du göra med text-to-columns och ska det göras ofta är det lätt att spela in ett macro för det.

"When I get sad, I stop being sad and be awsome instead, true story."

Trädvy Permalänk
Medlem
Registrerad
Feb 2016

Sitter inte vid datorn så jag kan inte ge dig ett exempel, men du kan extrahera års- månads- och dagsvärdet ur din kolumn med funktioner som heter YEAR MONTH och DAY (har jag för mig) på engelska. Dessa borde du sen kunna bygga ihop till en sträng att jämföra med "etiketten" för den cell där summeringen ska hamna (dvs oftast cellen till vänster om cellen där summan ska va).
Du borde också kunna stapla villkoren på varandra i SUMIFS dvs YEAR(A1)=YEAR(etikett);MONTH(A1)= etc. Osäker på vilket alternativ som skulle va bäst ur prestandasynpunkt med den datamängden dock.
Tror problemet med din kod är att du förökar jämföra ett datum med en textsträng och även om de till synes är samma så är de inte det för excel.
Ska försöka peta ihop nåt när jag kommer hem till datorn.

Skickades från m.sweclockers.com

Trädvy Permalänk
Medlem
Registrerad
Maj 2013
Skrivet av Json_81:

Uppdelning kan du göra med text-to-columns och ska det göras ofta är det lätt att spela in ett macro för det.

Gjorde som du beskriver nu och det fungerar, det trodde jag att det skulle från början med men vill hitta en annan lösning kanske lite tjurskalligt.

Men kommer vilja få en lösning där jag slipper det på sikt.

Trädvy Permalänk
Medlem
Registrerad
Maj 2013
Skrivet av Napoleongl:

Sitter inte vid datorn så jag kan inte ge dig ett exempel, men du kan extrahera års- månads- och dagsvärdet ur din kolumn med funktioner som heter YEAR MONTH och DAY (har jag för mig) på engelska. Dessa borde du sen kunna bygga ihop till en sträng att jämföra med "etiketten" för den cell där summeringen ska hamna (dvs oftast cellen till vänster om cellen där summan ska va).
Du borde också kunna stapla villkoren på varandra i SUMIFS dvs YEAR(A1)=YEAR(etikett);MONTH(A1)= etc. Osäker på vilket alternativ som skulle va bäst ur prestandasynpunkt med den datamängden dock.
Tror problemet med din kod är att du förökar jämföra ett datum med en textsträng och även om de till synes är samma så är de inte det för excel.
Ska försöka peta ihop nåt när jag kommer hem till datorn.

Skickades från m.sweclockers.com

Låter spännande!

Tacksam för alla som kommer med input, tack!

Trädvy Permalänk
Medlem
Registrerad
Feb 2016

@sirWayne:
Han bara leka lite snabbt nu, ska kolla mer ikvälk, men det verkar tyvärr inte riktigt lika lätt som jag trodde. Mer specifikt så verkar Excel freaka när den ska utvärdera en hel kolumn via en annan funktion.

Trädvy Permalänk
Medlem
Plats
Romelanda
Registrerad
Nov 2009
Skrivet av sirWayne:

Gjorde som du beskriver nu och det fungerar, det trodde jag att det skulle från början med men vill hitta en annan lösning kanske lite tjurskalligt.

Men kommer vilja få en lösning där jag slipper det på sikt.

Fungerade det inte att använda left som jag skrev först då? Det borde ge precis samma resultat utan att behöva text-to-columns.

"When I get sad, I stop being sad and be awsome instead, true story."

Trädvy Permalänk
Medlem
Registrerad
Maj 2013
Skrivet av Json_81:

Fungerade det inte att använda left som jag skrev först då? Det borde ge precis samma resultat utan att behöva text-to-columns.

Nej, det fungerade inte testade ändå en stund även med heltalsfunktionen och några andra. Fick även hjälp av en IT-utvecklare på jobbet men fortfarande ingen lösning utan att dela upp kolumnen. Datum verkar generellt lite bökigt att hantera i excel.

Skickades från m.sweclockers.com

Trädvy Permalänk
Medlem
Plats
Romelanda
Registrerad
Nov 2009
Skrivet av sirWayne:

Nej, det fungerade inte testade ändå en stund även med heltalsfunktionen och några andra. Fick även hjälp av en IT-utvecklare på jobbet men fortfarande ingen lösning utan att dela upp kolumnen. Datum verkar generellt lite bökigt att hantera i excel.

Skickades från m.sweclockers.com

Testa att ha dem som textfält.

"When I get sad, I stop being sad and be awsome instead, true story."

Trädvy Permalänk
Medlem
Registrerad
Feb 2016

@sirWayne
Vad är det egentligen för format på datumfältet? (Tryck ctrl+1 i en av cellerna)
Och vad är det för format på fältet du vill jämföra med och kan (får) du styra formatet på det sistnämnda?

Skickades från m.sweclockers.com

Trädvy Permalänk
Medlem
Registrerad
Feb 2016

@sirWayne
Såja, lite mat och tobak så kan man ju tänka klart igen...
Enligt nedan med följande villkor
A2-A17 är cellerna med datum och tid, tex "2016-04-18 13:40:48"
B2-B17 är cellerna med värdena du vill summera tex "2400"
Cell E2 är ett datum i textformat, tex " '2016-04-18" (' framför gör det till text)

=SUMIFS($B$2:$B$17;$A$2:$A$17;">="&DATEVALUE(E2);$A$2:$A$17;"<"&DATEVALUE(E2)+1)

Om Du kan sätta jämförelsefältet till datumformat istället för text fungerar följande
Cell H2 är ett datum i just datumformat, tx "2016-04-18"

=SUMIFS($B$2:$B$17;$A$2:$A$17;">="&H2;$A$2:$A$17;"<"&H2+1)

Med det sagt vetetusan hur lång tid det här kommer ta på 35k rader, ett VBAscript är sannolikt betydligt snabbare men jag är för ringrostigt på VBA för att lyckas med det.

Trädvy Permalänk
Medlem
Registrerad
Maj 2013
Skrivet av Napoleongl:

@sirWayne
Såja, lite mat och tobak så kan man ju tänka klart igen...
Enligt nedan med följande villkor
A2-A17 är cellerna med datum och tid, tex "2016-04-18 13:40:48"
B2-B17 är cellerna med värdena du vill summera tex "2400"
Cell E2 är ett datum i textformat, tex " '2016-04-18" (' framför gör det till text)

=SUMIFS($B$2:$B$17;$A$2:$A$17;">="&DATEVALUE(E2);$A$2:$A$17;"<"&DATEVALUE(E2)+1)

Om Du kan sätta jämförelsefältet till datumformat istället för text fungerar följande
Cell H2 är ett datum i just datumformat, tx "2016-04-18"

=SUMIFS($B$2:$B$17;$A$2:$A$17;">="&H2;$A$2:$A$17;"<"&H2+1)

Med det sagt vetetusan hur lång tid det här kommer ta på 35k rader, ett VBAscript är sannolikt betydligt snabbare men jag är för ringrostigt på VBA för att lyckas med det.

Toppen! Ska testa imorrn när jag är tillbaka på jobbet, återkommer med hur det gick. Tobak och käka brukar göra susen!:-)

Skickades från m.sweclockers.com

Trädvy Permalänk
Medlem
Registrerad
Apr 2016

Hej,

Jag skulle vilja rekommendera en matrisformel. Dessa är väldigt kraftfulla när du vill behandla större datamängder. En fördel är att man kan utföra samma operationer som om det bara gällde en cell. Cell F2 i nedanstående formeln är det datum för vilket du vill summera.

=SUMMA(OM((DAG(F2)=DAG(A2:A5))*(MÅNAD(F2)=MÅNAD(A2:A5))*(ÅR(F2)=ÅR(A2:A5));B2:B5;0))

Formeln jämför dag, månad och år i cellen F2 med A2:A5 och returnerar innehållet i B2:B5 där villkoren uppfylls. Resultatet för respektive rad hamnar i en matris. Därav "SUMMA" för att summera hela resultatmatrisen. Du behöver inte konvertera datumet till text.

Avsluta formeln med CTRL-SHIFT-ENTER när markören är i formelfältet. Då förstår excel att det är en matrisformel (och du får måsvingar runt hela ekvationen).

Trädvy Permalänk
Medlem
Registrerad
Maj 2013
Skrivet av Napoleongl:

@sirWayne

Om Du kan sätta jämförelsefältet till datumformat istället för text fungerar följande
Cell H2 är ett datum i just datumformat, tx "2016-04-18"

=SUMIFS($B$2:$B$17;$A$2:$A$17;">="&H2;$A$2:$A$17;"<"&H2+1)

Tack fungerade jättebra!

Går det att förklara varför det måste göras till en olikhet? Nyfiken på hur det fungerar.

Trädvy Permalänk
Medlem
Registrerad
Maj 2013
Skrivet av Boomhauzen:

Hej,

Jag skulle vilja rekommendera en matrisformel. Dessa är väldigt kraftfulla när du vill behandla större datamängder. En fördel är att man kan utföra samma operationer som om det bara gällde en cell. Cell F2 i nedanstående formeln är det datum för vilket du vill summera.

=SUMMA(OM((DAG(F2)=DAG(A2:A5))*(MÅNAD(F2)=MÅNAD(A2:A5))*(ÅR(F2)=ÅR(A2:A5));B2:B5;0))

Formeln jämför dag, månad och år i cellen F2 med A2:A5 och returnerar innehållet i B2:B5 där villkoren uppfylls. Resultatet för respektive rad hamnar i en matris. Därav "SUMMA" för att summera hela resultatmatrisen. Du behöver inte konvertera datumet till text.

Avsluta formeln med CTRL-SHIFT-ENTER när markören är i formelfältet. Då förstår excel att det är en matrisformel (och du får måsvingar runt hela ekvationen).

Tack för ännu ett svar!

Fick andra metoden att fungera bra, men ska ha denna i åtanke också.

Trädvy Permalänk
Medlem
Registrerad
Feb 2016
Skrivet av sirWayne:

Tack fungerade jättebra!

Går det att förklara varför det måste göras till en olikhet? Nyfiken på hur det fungerar.

Till att börja med skulle jag utan att vara helt insatt i hur de fungerar också rekommendera @Boomhauzen s lösning, matriser (arrays på engelska) är generellt gjorda med stora datamängder som grundpelare oavsett program.
Med det sagt; olikheten behövs för att du vill ha med alla poster i spannet från jämförelsecellen (som har timestamp 00:00:00) och fram tills nästa datum börjar. Därav >= på första jmf och endast < på den andra.
Den här olikheten gör att du också kan dela upp summorna i förmiddags och eftermiddags summor om du skulle vilja eller timmar för den delen. >=H2+0,5 är tex alla värden efter 12:00.

Skickades från m.sweclockers.com