28.10.2012, aktualisiert am 30.10.2012

6284

Text mit Variablen ersetzen und berechnen (Summe)

Ich möchte in Excel in einer Tabelle einen bestimmten Zellentext (hier "G1" und "G2") mit einer Variablen ersetzen und diesen dann als Quersumme darstellen (in dem Bespiel sollte die Quersumme 40 sein, der Zellentext "U" und leere Zellen (wie in F1) sollen unberücksichtigt bleiben).

Die Werte der Variable G1 und G2 stehen in B3 und B4.

Kann mir hier wer auf die Sprünge helfen mit der Formel zur Quersummenbildung?

Danke
Carl

Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben

Content-ID: 193402

Url: https://administrator.de/forum/text-mit-variablen-ersetzen-und-berechnen-summe-193402.html

Ausgedruckt am: 08.04.2025 um 23:04 Uhr

12 Kommentare

Neuester Kommentar

Hallo Carl.

Anstelle der Werte für die Summe (=SUMME(wert1;wert1;...)) verwendest du einfach SVERWEIS() mit denen du je das Feld A1 bzw. B1 referenzierst.

	A	B	C	D
1	a	c		=SUMME( SVERWEIS(A1;A3:B5;2;0); SVERWEIS(B1;A3:B5;2;0) )
2
3	a	2
4	b	4
5	c	8

Mit dem erstem SVERWEIS suchen wir dem Wert aus Feld A1 in der Matrix A3:B5 und möchten von dort den Wert aus dem 2.Feld bei einer exakten Übereinstimmung (0).
Mit dem zweiten suchen wir in der selben Matrix nach dem 2.Wert aus Feld B1 unter den gleichen Bedingungen.

~Arano

Edit:
Oh ohh... das mit der Quersumme habe ich total außer Acht gelassen... sorry.

Hallo,

Mal unabhängig vom Problem, die Quersumme ist da doch nicht 40

Die Quersumme von 123 ist z.B 6
Von 10 ist sie 1
Und von 20 die 2

Hallo Arano, wiesl200

Sorry, habe mich wohl mit der Quersumme wohl nicht ganz korrekt ausgedrückt *schäm* ...

Der Text in den Zellen A1, B1, C1, ... (also das G1 oder das G2) soll mit einen definierten Wert der in den Zellen weiter unten angegeben ist esetzt werden um damit eine Summe bilden zu können.

D.h. oben im Bild steht 2 x G1 und 2 x G2 hier wäre die Summe (10+10+20+20) = 40.

Danke schon mal für eure Hilfe
Carl

EDIT: mit Aranos Beispiel (SVERWEIS) dürfte es das Ergebnis bringen, welches ich benötige.

Hi,

ja dann passt es ja !
ABER mein Gehirn und mein Taschenrechner sagen, dass "10+10+20+20" = 60 sind...

Schwerer kann es dann wohl noch mit leeren Felder werden, denn dann versagt mein Vorschlag. Oder wenn ein Wert nicht in der Matrix steht.
U z.B. könnte man ja aber noch mit dem Wert "0" in die Liste mitaufnehmen, mit leeren Feldern... da fällt mir jetzt nur ein das vorher mit einer WENN-Formel zu prüfen und so entweder den Wert aus dem Feld/Matrix zu verwenden oder einfach 0.
Allerdings dürfte das die gesamte Formel ganz schön aufblasen...

~Arano

Zitat von @Arano:

Hi,

ja dann passt es ja !
ABER mein Gehirn und mein Taschenrechner sagen, dass "10+10+20+20" = 60 sind...

*lol* na klar ist es 60 ;) ... bringe schon alles durcheinander ...

Danke, jetzt weiß ich wenigstens, wie ich es angehen muß.
Die Formel benötigen wir in der Firma für einen monatlichen Schichtplan, wo die Kollegen ihre Arbeitszeiten mit G1 (steht für 12 Tagesstunden) und G2 (für 9 Tagesstunden). Das Ganze wird dann auf 30/31 Tage eingetragen und die Summe wird anhand der Formel errechnet. Wie es sich dann mir freien Tagen (leere Zelle) oder Urlaubstagen (U) verhält ... das schaffen wir schon irgendwie ... ;)

LG Carl

Moin KongoKing,

wenn denn nun 30-31 Tage nebeneinander in einer Zeile -z.B. #1- stehen (also grob geschätzr von Spalte A bis Spalte AD), dann sollte sich die Formel (relativ) verkürzen lassen auf

 =ZÄHLENWENN($A1:$AD1;"G1")*SVERWEIS("G1";$A$3:$B$4;2;0) 
+ZÄHLENWENN($A1:$AD1;"G2")*SVERWEIS("G2";$A$3:$B$4;2;0)

-oder, um bei zwei "aufzulösenden Variablen", die sich sicherlich alle achteinhalb Jahre mal ändern, realistisch zu bleiben:

=ZÄHLENWENN($A1:$AD1;"G1")*12 +ZÄHLENWENN($A1:$AD1;"G2")*9  

Grüße
Biber

Leute,
ihr seid echt ein Hit.
Danke euch schon mal für die Hilfe, werde das morgen im Büro zum Umsetzen versuchen.
Werde euch dann das Ergebnis mitteilen.

LG Carl

Moin KongoKing,

auch wenn inzwischen der grüne Haken schon am Beitrag pappt, noch ein kleiner Nachklapp.

Hat mir doch keine Ruhe gelassen, dass eine anderthalbzeilige Formel für so'n bischen pilpul nötig sein sollte - ich hab dein Sheet mal kurz nachgebaut.

Das sieht in meiner Phantasie ungefähr so aus, dass in Zeile #1 die Tage des Monats stehen, von 1 bis maximal 31. Links, in Spalte A stehen die Namen der Plockerer ("Heinz", "Bärbel", etc) ab Zeile 2, danben die einzutippselnden "G1"/"G2"-Schichten, "U"rlaub oder leer - also bei 31 Tagen von Spalte "B" bis Spalte "AF", dahinter in Zelle $AG2 und darunter die Summe der Stunden.

Wenn denn jetzt die Zeile, in der die "G1"-Stunden (=der Wert 12) stehen, noch den Namen "G1Stunden" verpasst bekommt und die Zelle mit dem Wert 9 entsprechend den Namen "G2Stunden", dann lässt sich die Formel in dem Formelfeld AG2 so schreiben:

=SUMME(WENN($B2:$AF2="G1";G1Stunden;WENN($B2:$AF2="G2";G2Stunden;0)))

Diese Formel muss mit Strg-Shift-Enter eingegeben werden ("Matrixformel") und sieht dann in der Anzeige so aus:

{=SUMME(WENN($B2:$AF2="G1";G1Stunden;WENN($B2:$AF2="G2";G2Stunden;0)))}

oder bei Verzicht auf Variablennamen

{=SUMME(WENN($B2:$AF2="G1";12;WENN($B2:$AF2="G2";9;0)))}

Diese Formel kann dann einfach nach unten kopiert werden.

Das Charmante an dieser Lösung ist neben der Lesbarkeit, dass hier ausnahmsweise selbst in Excels grottigen Formeleditor-Fensterchen die von dir im eingangs formulierte "Variablen durch Werte ersetzen"-Anforderung bei der Arbeit zu sehen ist.

Die 31 Tageswerte werden als Array angezeigt
={9 . 9 . 12. 0 . 0 . 12. 0 . .... }
.> Alle "G1"-Zellen wurden durch den Wert 12 ersetzt in der ersten WENN-Prüfung, alle "G2" durch 9 im zweiten WENN-Fall, alles andere durch 0 im SONST-Fall.

Zu beachten ist nur, dass der Dezember in diesem Jahr laut Roland Emmerich und dem Maya-Kalender nur bis Spalte "X" gefüllt werden darf.
Änderungen an der Formel sind aber nicht nötig.

Grüße
Biber

Hi Biber,

wow, deinen Nachklapp muss ich mir mal auf der Zunge zergehen lassen bzw. genauer ansehen ;).

Fakt ist mal, dass es mit ZÄHLEWENN und SVERWEIS tadellos funktioniert. Da wir hier das Excel in Englisch haben geht es mit COUNTIF und VLOOKUP.

Die Formel sieht nun so aus

:
=COUNTIF(B10:AF10;"SN")*VLOOKUP("SN";A28:D42;2;0)+COUNTIF(B10:AF10;"SF")*VLOOKUP("SF";A28:D42;2;0)+COUNTIF(B10:AF10;"ST")*VLOOKUP("ST";A28:D42;2;0)+COUNTIF(B10:AF10;"S1")*VLOOKUP("S1";A28:D42;2;0)+COUNTIF(B10:AF10;"S2")*VLOOKUP("S2";A28:D42;2;0)+COUNTIF(B10:AF10;"S3")*VLOOKUP("S3";A28:D42;2;0)+COUNTIF(B10:AF10;"S4")*VLOOKUP("S4";A28:D42;2;0)+COUNTIF(B10:AF10;"G1")*VLOOKUP("G1";A28:D42;2;0)+COUNTIF(B10:AF10;"G2")*VLOOKUP("G2";A28:D42;2;0)+COUNTIF(B10:AF10;"G3")*VLOOKUP("G3";A28:D42;2;0)+COUNTIF(B10:AF10;"G4")*VLOOKUP("G4";A28:D42;2;0)+COUNTIF(B10:AF10;"INN")*VLOOKUP("INN";A28:D42;2;0)

Kommt daher dass wir mehrere Abfragen (G1,G2,SN,SF, ...) machen müssen.
Hätte gerne das Sheet hier reingestellt zum Ansehen, weiß aber leider nicht wie das genau beim Antworten funktioniert.

LG Carl

EDIT: habe wohl zu früh den grünen Haken angepappt ...

{OT] Hallo Biber!

Diese Formel muss mit Strg-Shift-Enter eingegeben werden

Wie tief bist Du gesunken ...

Grüße
bastla
[/OT]

[Re:OT]

Zitat von @bastla:

Wie tief bist Du gesunken ...

Muss an dem Wochentag ("der, dessen Name niemand nennen darf..") liegen, an dem ich den Kommentar verzapft habe.

@KongoKing:
Wenn es bei euch tatsächlich ein gefühltes Dutzend mögliche aufzulösende "Arbeitszeitart"-Varianten gibt, dann bleib bei deiner COUNTIF/VLOOKUP-Variante.

Die CSE-Variante ist dann auch nicht kürzer und außerdem haben die Redmonder PraktikantInnen nicht mehr als 9 mögliche WENNs zum Ineinanderverschachteln vorgesehen...
-> da müsstest du in der Matrixformel dann rumalbern mit
{{ {=SUM(IF(stdzeile="Fall1"; wert1; IF(stdzeile="Fall2"; wert2; IF(... .. bis "Fall8", wert8; Sum(If(stdzeile="Fall9", wert9;... ;0) }

... und das hat weniger eine Tendenz zum Les- und Wartbaren - mehr eine unaufhaltsame Entwicklung zum Unappetitlichen.

Also lass es as is...
^^....die paar Tage bis zum 21.12. wird es halten.

Grüße
Biber
[/Re:OT]