Hallo zusammen

Ich habe eine Excel-Summeprodukt-Formel die ich gerne in Numbers nachbilden möchte. Die in Excel erstellte Formel wird beim öffnen der Excel-Datei in Numbers nicht erkannt. Vermutlich wird diese in Numbers anders formatiert? Kann mir jemand bei der Lösung helfen?


Dies ist ein Screenshot aus Excel. In der Zelle B20 steht folgende Formel:
=SUMMENPRODUKT((A1:A17={"10"\"11"\"12"\"13"\"14"\"15"\"16"\"17"\"18"\"19"})*B1:B17)

Die Formel sucht in A1:A17 die Werte 10, 11, ... 19 und summiert die entsprechenden Werte aus B1:B17.

Genauer:
1*360 + 0*150 + 0*210 + 1*360 + 0*360 + 1*205 + 0*205 etc.
1 wenn der gesuchte Wert in A1:A17 Wahr ist,
0 wenn der gesuchte Wert in A1:A17 Falsch ist.

Ich habe gegoogelt und in der Numbers Hilfe gesucht - jedoch keine sinnvolle Hilfe gefunden.

Tausend Dank für Eure Unterstützung!

eames
Ich weiß zwar nicht was Du damit berechnen willst. Aber wenn ich »Numbers Summenprodukt“ google, dann erhalte ich eine Menge Treffer.

SUMMEWENNS(B1:B17;A1:A17;">9,5";A1:A17;"<19,5")

Den Teil hinter "genauer" habe ich nicht verstanden.

ilig
Das was ich berechnen will hab ich ja oben beschrieben. Mit Excel funktioniert es - Numbers hat jedoch mit der Formel welche in Excel funktioniert ein Problem - sie funktioniert nicht.

marm
mit "Genauer" versuche ich zu erklären was die Summeprodukt-Formel eigentlich genau macht:
Sie sucht in A1:A17 nach dem gesuchten Kriterium. Wenn das Kriterium gefunden wurde beurteilt die Formel das mit "Wahr" bzw. 1. für alle anderen gefundenen Werte sagt die Formel "Falsch" bzw. 0. Da es sich um "SummeProdukt" handelt wird also addiert und multipliziert - so wie ich dann geschrieben habe.

Danke aber auch! Deine Formel funktioniert vom Grundsatz - besser wäre in meinem Fall jedoch wenn nicht nach > bzw. < gesucht wird sondern nach exakt den gewünschten Werten. Das hängt damit zusammen das in meiner Tabelle in A1:A17 auch Werte mit vorgestellter Null vorkommen können was zu Problemen führt wenn Bereiche gesucht werden. Die Excel Formel könnte also auch wie folgt aussehen:

=SUMMENPRODUKT((A1:A17={"00"\"01"\"02"\"03"\"04"\"05"\"06"\"07"\"08"\"09"})*B1:B17)

Mit deiner Formel lässt sich das schon auch abbilden - jedoch kommt dann in der Formel 10x der Zellbereich A1:A17 vor. Wenn dieser sich ändert müsste man den auch 10x anpassen...

Irgendwie hab ich das Gefühl Numbers kommt mit geschweiften Klammern und Backslash nicht klar...

SUMMEWENNS(B1:B17;A1:A17;REGEX("0[1-9]"))

Da muss Du mal mit der passenden Regex-Formel probieren. Das habe ich auch gerade erst dazugelernt

Oha, das klingt ja spannend. Danke das teste ich direkt mal...

mmmh das mit der regex formel bekomm ich nicht hin da kommt ein falsche wert.

ein weg wäre alle zweistelligen werte in A1:A17 zu suchen (unabhängig vom genauen Wert) und die entsprechenden Werte in B1:B17 zu addieren.

Klingt einfach aber ich komme einfach nicht drauf wie das geht.

Welche Formatierung haben die zweistelligen Werte? Zahlen oder Text? In Text kannst Du die Werte in der A-Spalte umwandeln, indem Du einfach &"" an die Formel dranhängst.

Der Ausdruck ist dann
REGEX("[0-9][0-9]")

Die Werte in Spalte A sind als Text gespeichert damit die führende 0 angezeigt wird.

Hier noch die Tabelle in der Variante mit führenden Nullen... die passende Formel in Excel wäre wie oben bereits erwähnt folgende:

=SUMMENPRODUKT((A1:A17={"00"\"01"\"02"\"03"\"04"\"05"\"06"\"07"\"08"\"09"})*B1:B17)



Kann man diese Formel nicht exakt so in Numbers nachbauen?

marm
Mit dem Regex kommt ich irgendwie nicht klar - kannst du mir mal die komplette Formel die es nach deiner Meinung braucht hier posten?

Ich hoffe, die Aufgabe ändert sich nicht nochmal
SUMME(B1:B17)-SUMMEWENNS(B1:B17;A1:A17;REGEX("\d{3}"))
Die Idee dahinter: Alle Werte summieren und die Werte mit drei Ziffern wieder abziehen.

SUMMEWENNS(B1:B17;A1:A17;"<100")

oder besser

SUMMEWENNS(B1:B17;A1:A17;"<100";A1:A17;">=10")

hehe nein die aufgabe ändert sich nicht hihi - ist echt ne knacknuss. ich war damals schon happy als ich für excel die schöne kurze formel entwickelt hatte...

marm
leider kommt mit deiner formel als ergebnis 3160 statt 1580

Roby Toby
und bei dir kommt ne glatte 0 raus. - korrektur - bei dir kommt das richtige raus - aber die formel klappt nicht wenn es vorangestellte nullen gibt...

ich will euch nicht verrückt machen. danke auf alle fälle für eure mühen... falls ihr da lust auf knobeln habt - gerne weitere vorschläge. am liebsten würd ich die funktionierende excel formel einfach im numbers übernehmen. bei numbers scheint mir aber summeprodukt irgendwie anders zu funktionieren als in excel...

also bei mir schon.

Kann nicht sein. Jetzt habe ich tatsächlich deine Tabelle abgetippt und komme mit meiner Formel auf 1580.

marm
stimmt - im excel geht das, im numbers kommt ne fehlermeldung "Der Bereich „B1:B17“ kann nicht als Einzelwert verwendet werden."
wenn du im excel in spalte A eine dreistellige zahl in eine zweistellige änderst rechnet bei mir die formel nicht neu...wenn man einen wert in spalte b ändert auch nicht... komisch

Das Problem ist, dass „eames“ die Vergleichswerte als Text hinterlegt hat, wegen den vorangestellten Nullen.

Deswegen habe ich den Aufwand mit Regex doch gemacht. Getestet habe ich übrigens in Numbers, nicht in Excel.
Bei mir passt sich das Ergebnis mit geänderten Werten auch an.

Roby Toby

marm
da kommt bei mir die fehlermeldung "Der Bereich „B1:B17“ kann nicht als Einzelwert verwendet werden."

Ich dachte auch es geht um Numbers.
Wenn du aus der Zahl 10 einen Text 010 machst dann ist in deiner Formel die auch dreistellig, daher der Unterschied. (Seine erste Tabelle, der Wert in A1)
Meine Formel funktioniert bei mir, egal ob ich die Spalte A als Text oder numerisch oder gemischt formatiere. Und ja, Numbers aktualisiert sofort, bei Excel muß man das einstellen.

So geht das nicht. "001" ist dreistellig, darf daher nicht mitgezählt werden, ist aber offensichtlich kleiner 10.

da gehören ; und nicht , rein.

Da steht größer gleich Zehn!!

Roby Toby
ja ich weiss - hab mich auch gewundert mit den ; bzw ,
mein compi ist aber auf schweizer tastatur eingestellt. numbers gibt mir die , automatisch vor. mit ; funktioniert da keine einzige formel

ist aber nur im numbers so - im excel ist alles "normal"

in roby tobys lösung wird die 001 und die 009 als kleiner 10 gezählt. daher stimmt das ergebnis nicht...

Was willst du eigentlich genau? Ich dachte es geht um zweistellige Zahlen, welche du nur wegen der vorangestellten Null zum Text gemacht hast. Sehe ich das richtig, wenn in A1 „00“ steht soll das auch mitgezählt werden?

Du hast die Klammer falsch eingegeben (nicht nur das Semikolon). Kopier meine Formel
SUMME(B1:B17)-SUMMEWENNS(B1:B17;A1:A17;REGEX("\d{3}"))
einfach rein und nicht abtippen.

Du hast im Screenshot hinten eine Klammer zu viel. Vermutlich ist der summewenns-Teil bei Dir innerhalb der Summe-Klammer.

So muss das aussehen:

Der erste zunehmende Sichelmond vor dem Minus fehlt bei dir.

marm
danke dir. jetzt klappts tatsächlich. numbers macht das mit den klammern optisch für mich echt unübersichtlich.
das semikolon ist bei mir zwingend ein komma - warum weiss ich auch nicht. mit semikolon funktionieren die einfachsten formel im numbers bei mir nicht.

tausend dank. die formel ist klasse!

👍🏻
Ich habe die Formel auch im Texteditor erstellt. In Numbers wird man ja irre, so unübersichtlich ist das.

ja ist bescheuert - auch das reinkopieren führt zu chaos weil numbers die formel wegen den semikolon nicht erkennt. wenn ich die dann auf kommas anpasse geht alles durcheinander.

Roby Toby
Danke auch Dir! Du warst nah dran. Und ja die 00 wäre auch zweistellig.

Gibt es eigentlich irgendwo eine Beschreibung für die Zeichenfolgen zu Regex?

Wiki
Tutorial

Danke 🙏

leute - das mit dem regex ist der wahnsinn.

marm
ich hab deine formel noch vereinfachen können:
SUMMEWENNS(B1:B17,A1:A17,REGEX("\b0[0-9]\b")) für die tabelle mit den führenden nullen

und entsprechend
SUMMEWENNS(E1:E17,D1:D17,REGEX("\b1[0-9]\b")) für die tabelle beginnend mit 10

genial! Danke!!!

REGEX("\b1[0-9]\b"))

Was bedeutet dieser Regex-Term genau? Hat jemand Lust bei seiner zweiten Tasse Kaffee, mir das kurz zu erklären? Regex scheint ja ein mächtiges Werkzeug zu sein.

\b bezeichnet Anfang und Ende des Ausdrucks. Am Beginn des Ausdrucks steht eine 1, dann eine Ziffer zwischen 0 und 9. Gefunden werden also alle Zahlen zwischen 10 und 19.

Ich bin auch absoluter Regex Neuling. So hab ich es verstanden / gegoogelt:

1[0-9] bedeutet das an erster Stelle von links eine 1 und an zweiter Stelle von links eine 0 bis 9 vorkommen darf.

marm

wichtig ist im konkreten fall die stellenanzahl und nicht der tatsächliche wert der ziffern. das verdeutlicht sich wenn man in meiner tabelle mit den führenden nullen sucht:

\b0[0-9]\b findet alle zahlen zwischen 00 und 09 ignoriert aber zb die 001 oder 009 aber auch die 010!

eames
Ja, das war der Kniff, der mir gestern fehlte. Daher habe ich die Werte der dreistelligen Ziffern abgezogen statt die zweistelligen Ziffern direkt zu addieren. Ist so eleganter und weniger fehleranfällig von Dir gelöst.
Schade, dass Excel Regex nur mit VBA kann.

ja eigentlich hattest du die lösung schon im dritten post. es hat nur das \b gefehlt.

da hat also numbers mal einen vorteil gegenüber excel. kaum zu glauben

marm

weisst du ob man im regex string \b0[0-9]\b auch einen zellbezug integrieren kann?

nach dem motto: \b(=D1)[0-9]\b wobei dann in Zelle D1 eine 0 steht.

eames
Es klappt auch direkt ohne Hilfszelle, den Kommentar habe ich gelöscht. Also:

SUMMEWENNS(B1:B17;A$1:A$17;REGEX("\b"&D$1&"[0-9]\b"))

super danke - es geht sogar ohne hilfszeile und noch mehr vereinfacht:

SUMMEWENNS($B,$A,REGEX("\b"&$D1&"[0-9]\b"))
wobei die Formel selbst nicht in Spalte A oder B stehen darf wegen dem Bezug auf sich selbst durch $A bzw. $B... das ist aber kein Problem.

also ich danke Dir wirklich sehr marm! Auf diese Lösung wäre ich im Leben nicht ohne deine Hilfe gekommen. Und durch die Verfeinerungen ist sie nun extrem fehlerresistent und gleichzeitig flexibel nutzbar. ausserdem checkt kaum einer was die formel genau macht - das find ich sympathisch...

…keep it simple…

So, wie ich das verstehe, sind die zweistelligen A-Werte eine Zwischensumme der dreistelligen A-Werte. Wenn das stringent durchgehalten wird, dann gilt für die Formel in B20:

SUMME(B1:17)/2

…ist zwar nicht so „fancy“, tut aber was es soll.

Sicher ne blöde Frage aber ich versuch es mal:
Kann man sich in Numbers kein Zahlenformat erstellen welches alle Zahlen mindestens 3-stellig anzeigt. Dann könnte man sich die Geschichte mit Text sparen und die Felder gleich als Zahl definierten.
Falls das nicht möglich sein sollte, hab ich für solche Aufgaben immer nen Vorschlaghammer bereit liegen und trenne Eingabe und Anzeige in eigene Spalten auf. Hier würde ich mir daher aus der 1. Spalte (Zahlen 3-stellig als Text mit führender 0, erstmal eine Hilfsspalte ableiten die mir den Wert aus der 1. Spalte zuerst in eine Zahl wandelt und dann entweder das Ergebnis oder falls der der Wert außerhalb des gewünschten Bereichs liegt, eine 0 in der Hilfsspalte einträgt. Anschließend unten einfach eine normale Summe der Hilfsspalte bilden und fertig

Gemäß dem, was wir an Zahlen sehen, hast Du recht. Aber selbst, wenn die Regel von Dir generell stimmt, dann hängt es vom Zweck ab. Womöglich will er kontrollieren, ob die Zwischensummen korrekt den Einzelwerten entsprechen. Wir kennen die Hintergründe nicht.

In Spalte A habe ich Kennnummer stehen. Diese Kennnummer werden nach einem ganz bestimmten Prinzip vergeben.

In Spalte B stehen Kostenwerte die abhängig von der Kennnummer summiert werden sollen.

Spalte A dient also ausschliesslich der Analyse welche Werte aus Spalte B summiert werden sollen.

Das klappt so wie ich es benötige nur mit der nun gefundenen Formel. Diese ist in der optimierten Variante die wir zum Schluss gefunden haben sogar so flexibel, das ich mit der innerhalb meiner Tabelle noch viel mehr damit machen kann als ursprünglich gedacht.

MacDino

Hehe bezüglich dem Beispiel hast Du recht - die Summe aus Spalte B durch 2 ergibt leider aber nur zufällig genau den gesuchten Wert...

Noch eine Ergänzung zu meinen Kennzahlen. Diese umfassen alle Zahlen zwischen 0 und 999. Gesucht werden sollen nun z.B.
- alle einstelligen Kennzahlen - die Werte in Spalte B sollen addiert werden
- alle zweistelligen Kennzahlen beginnend mit 0 - alle Werte in Spalte B sollen addiert werden
- alle ... beginnend mit 1 - alle Werte in Spalte B sollen addiert werden
- alle ... beginnend mit 2 etc.
- alle dreistelligen Kennzahlen beginnend mit 00 - ...
- alle dreistelligen Kennzahlen beginnend mit 01 - ...
...
- alle dreistelligen Kennzahlen beginnend mit 12 - ...
usw usw

Dabei kommen in der Tabelle nicht zwingend alle möglichen Kennzahlen vor, manche dafür jedoch mehrfach - das ist projektabhängig. Dadurch, dass sehr grosse Tabellen entstehen können, ist es eine Freude, wenn durch die Formel alle entsprechenden Werte fehlerfrei gefunden werden.