Top-Themen

AppleEntwicklungHardwareInternetLinuxMicrosoftMultimediaNetzwerkeOff TopicSicherheitSonstige SystemeVirtualisierungWeiterbildungZusammenarbeit

Aktuelle Themen

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit
GELÖST

Text mit Variablen ersetzen und berechnen (Summe)

Frage Microsoft Microsoft Office

Mitglied: KongoKing

KongoKing (Level 1) - Jetzt verbinden

28.10.2012, aktualisiert 30.10.2012, 4199 Aufrufe, 12 Kommentare

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 - Klicke auf das Bild, um es zu vergrößern

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
Mitglied: Arano
28.10.2012, aktualisiert um 09:29 Uhr
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.
Bitte warten ..
Mitglied: wiesi200
28.10.2012 um 09:11 Uhr
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
Bitte warten ..
Mitglied: KongoKing
28.10.2012, aktualisiert um 17:34 Uhr
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.
Bitte warten ..
Mitglied: Arano
28.10.2012 um 18:00 Uhr
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
Bitte warten ..
Mitglied: KongoKing
28.10.2012 um 19:36 Uhr
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
Bitte warten ..
Mitglied: Biber
28.10.2012, aktualisiert 30.10.2012
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:
01.
=ZÄHLENWENN($A1:$AD1;"G1")*12 +ZÄHLENWENN($A1:$AD1;"G2")*9
Grüße
Biber
Bitte warten ..
Mitglied: KongoKing
28.10.2012 um 21:40 Uhr
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
Bitte warten ..
Mitglied: Biber
29.10.2012, aktualisiert 30.10.2012
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
Bitte warten ..
Mitglied: KongoKing
30.10.2012, aktualisiert um 12:56 Uhr
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 ...
Bitte warten ..
Mitglied: bastla
30.10.2012 um 18:38 Uhr
{OT] Hallo Biber!
Diese Formel muss mit Strg-Shift-Enter eingegeben werden
Wie tief bist Du gesunken ...

Grüße
bastla
[/OT]
Bitte warten ..
Mitglied: Biber
30.10.2012 um 21:36 Uhr
[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]
Bitte warten ..
Mitglied: KongoKing
31.10.2012 um 23:30 Uhr
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" .
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
Bitte warten ..
Neuester Wissensbeitrag
Internet

Unbemerkt - Telekom Netzumschaltung!

(3)

Erfahrungsbericht von ashnod zum Thema Internet ...

Ähnliche Inhalte
Batch & Shell
Powershell - In Textdatei suchen und ersetzen (1)

Frage von Raaja89 zum Thema Batch & Shell ...

Windows Netzwerk
Windows Server 2003 SBS Netzwerk durch neuen Server Ersetzen (9)

Frage von MultiStorm zum Thema Windows Netzwerk ...

Microsoft Office
gelöst Wie kann man die Standard-Schriftart bei einfügen von Text in Word 2013 festlegen? (3)

Frage von Rene1976 zum Thema Microsoft Office ...

Batch & Shell
gelöst Suchen und Ersetzen mehrerer Suchbegriffe per Batch (4)

Frage von makroll10 zum Thema Batch & Shell ...

Heiß diskutierte Inhalte
Switche und Hubs
Trunk für 2xCisco Switch. Wo liegt der Fehler? (15)

Frage von JayyyH zum Thema Switche und Hubs ...

DSL, VDSL
DSL-Signal bewerten (13)

Frage von SarekHL zum Thema DSL, VDSL ...