Alla fyndtips från mellandagsrean
Forum Mjukvara Programmering och digitalt skapande Tråd Inlägg

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

1
Skriv svar
Permalänk
Medlem
●

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!

Redigera
Citera flera Citera
Permalänk
Medlem ★
●

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

Visa signatur

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

Redigera
Citera flera Citera
Permalänk
Lego Master ★
●

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 ,.

Visa signatur

* Vänsterhänt högerskytt med tummen mitt i handen.
* A franchises worst enemies are its biggest fans.
* 🖥️ i5 12600K | Z690 | 32GB | RTX 3070 | Define R6 | 48" 4K OLED | Win11 | 💻 Surface Go 3
* ⌨️ G915 Tactile | ⌨️ G13 | 🖱️ G502 X | 🎧 Pro X | 🎙️ QuadCast | 📹 EOS 550D | 🕹️ X52 Pro | 🎮 Xbox Elite 2
* 📱 Galaxy Fold4 | 🎧 Galaxy Buds Pro | ⌚ Galaxy Watch5 Pro | 📺 65" LG OLED | 🎞️ Nvidia Shield

Redigera
Citera flera Citera
Permalänk
Medlem
●
Skrivet av Json_81:

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

Gå till inlägget

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?

Redigera
Citera flera Citera
Permalänk
Medlem
●

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

Redigera
Citera flera Citera
Permalänk
Medlem ★
●
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?

Gå till inlägget

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

Visa signatur

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

Redigera
Citera flera Citera
Permalänk
Medlem
●
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

Gå till inlägget

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.

Redigera
Citera flera Citera
Permalänk
Medlem ★
●
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.

Gå till inlägget

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.

Visa signatur

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

Redigera
Citera flera Citera
Permalänk
Medlem ★
●

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

Redigera
Citera flera Citera
Permalänk
Medlem
●
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.

Gå till inlägget

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.

Redigera
Citera flera Citera
Permalänk
Medlem
●
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

Gå till inlägget

Låter spännande!

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

Redigera
Citera flera Citera
Permalänk
Medlem ★
●

@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.

Redigera
Citera flera Citera
Permalänk
Medlem ★
●
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.

Gå till inlägget

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.

Visa signatur

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

Redigera
Citera flera Citera
Permalänk
Medlem
●
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.

Gå till inlägget

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

Redigera
Citera flera Citera
Permalänk
Medlem ★
●
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

Gå till inlägget

Testa att ha dem som textfält.

Visa signatur

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

Redigera
Citera flera Citera
Permalänk
Medlem ★
●

@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

Redigera
Citera flera Citera
Permalänk
Medlem ★
●

@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.

Redigera
Citera flera Citera
Permalänk
Medlem
●
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.

Gå till inlägget

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

Redigera
Citera flera Citera
Permalänk
Medlem
●

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).

Redigera
Citera flera Citera
Permalänk
Medlem
●
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)
Gå till inlägget

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.

Redigera
Citera flera Citera
Permalänk
Medlem
●
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).

Gå till inlägget

Tack för ännu ett svar!

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

Redigera
Citera flera Citera
Permalänk
Medlem ★
●
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.

Gå till inlägget

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

Redigera
Citera flera Citera
1
Skriv svar