kongoking
Goto Top

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

ed0b435da5d56b200f1ba07bdf387f72

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

Content-Key: 193402

Url: https://administrator.de/contentid/193402

Ausgedruckt am: 28.03.2024 um 12:03 Uhr

Mitglied: Arano
Arano 28.10.2012 aktualisiert um 09:29:34 Uhr
Goto Top
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.
Mitglied: wiesi200
wiesi200 28.10.2012 um 09:11:00 Uhr
Goto Top
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
Mitglied: KongoKing
KongoKing 28.10.2012 aktualisiert um 17:34:51 Uhr
Goto Top
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.
Mitglied: Arano
Arano 28.10.2012 um 18:00:34 Uhr
Goto Top
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
Mitglied: KongoKing
KongoKing 28.10.2012 um 19:36:41 Uhr
Goto Top
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
Mitglied: Biber
Biber 28.10.2012, aktualisiert am 30.10.2012 um 09:08:02 Uhr
Goto Top
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
Mitglied: KongoKing
KongoKing 28.10.2012 um 21:40:35 Uhr
Goto Top
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
Mitglied: Biber
Biber 29.10.2012, aktualisiert am 30.10.2012 um 09:10:10 Uhr
Goto Top
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
Mitglied: KongoKing
KongoKing 30.10.2012 aktualisiert um 12:56:58 Uhr
Goto Top
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 face-wink:
=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 ...
Mitglied: bastla
bastla 30.10.2012 um 18:38:53 Uhr
Goto Top
{OT] Hallo Biber!
Diese Formel muss mit Strg-Shift-Enter eingegeben werden
Wie tief bist Du gesunken ... face-wink

Grüße
bastla
[/OT]
Mitglied: Biber
Biber 30.10.2012 um 21:36:23 Uhr
Goto Top
[Re:OT]
Zitat von @bastla:
Wie tief bist Du gesunken ... face-wink
Muss an dem Wochentag ("der, dessen Name niemand nennen darf..") liegen, an dem ich den Kommentar verzapft habe.face-wink

@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]
Mitglied: KongoKing
KongoKing 31.10.2012 um 23:30:25 Uhr
Goto Top
Also lass es as is...
^^....die paar Tage bis zum 21.12. wird es halten.

Grüße
Biber
[/Re:OT]

Hast recht, wir lassen es "as it is" face-smile.
Alles andere wäre zuviel Aufwand für ein lapidares Sheet ...

Ey, hab schon noch vor am 31.12. ein paar Gläser zu leeren ;)

Gruß
KK