Top-Themen

Aktuelle Themen (A bis Z)

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, 4622 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 ..
Ähnliche Inhalte
Basic
Variablen Text einfügen
gelöst Frage von TheFalconBasic14 Kommentare

Hallo zusammen, ich hab ein Makro, welches automatisch eine E-MAil verschickt nachdem man in einer Message Box einen Text ...

Microsoft Office
Text in mehreren .docx suchen und ersetzen
gelöst Frage von DerWoWussteMicrosoft Office14 Kommentare

Moin Kollegen. Ich hoffe auf Antworten von Leuten, die genau das per Tool schon mal gemacht haben: Selektiere mehrere ...

Batch & Shell
Text Zeilenweise auslesen und variable überprüfen
Frage von BruendlBatch & Shell11 Kommentare

Moin Moin, ich habe eine Textdatei mit folgendem Inhalt: ich möchte jetzt gerne die beiden Werte, die von einem ...

Batch & Shell
In Batch eine Text Variable und ein Array vergleichen
Frage von DrMarksmanBatch & Shell5 Kommentare

Hallo Leute! Ich habe mich wieder zu meinen Ursprüngen des Programmierens zurück begeben und bin dabei ein kleines Programm ...

Neue Wissensbeiträge
Windows 10

Autsch: Microsoft bündelt Windows 10 mit unsicherer Passwort-Manager-App

Tipp von kgborn vor 1 TagWindows 106 Kommentare

Unter Microsofts Windows 10 haben Endbenutzer keine Kontrolle mehr, was Microsoft an Apps auf dem Betriebssystem installiert (die Windows ...

Sicherheits-Tools

Achtung: Sicherheitslücke im FortiClient VPN-Client

Tipp von kgborn vor 1 TagSicherheits-Tools

Ich weiß nicht, wie häufig die NextGeneration Endpoint Protection-Lösung von Fortinet in deutschen Unternehmen eingesetzt wird. An dieser Stelle ...

Internet

USA: Die FCC schaff die Netzneutralität ab

Information von Frank vor 2 TagenInternet5 Kommentare

Jetzt beschädigt US-Präsident Donald Trump auch noch das Internet. Der neu eingesetzte FCC-Chef Ajit Pai ist bekannter Gegner einer ...

DSL, VDSL

ALL-BM200VDSL2V - Neues VDSL-Modem mit Vectoring von Allnet

Information von Lochkartenstanzer vor 2 TagenDSL, VDSL2 Kommentare

Moin, Falls jemand eine Alternative zu dem draytek sucht: Gruß lks

Heiß diskutierte Inhalte
Batch & Shell
Kann man mit einer .txt Datei eine .bat Datei öffnen?
gelöst Frage von HelloWorldBatch & Shell20 Kommentare

Wie schon im Titel beschrieben würde ich gerne durch einfaches klicken auf eine Text oder Word Datei eine Batch ...

Router & Routing
OpenWRT bzw. L.E.D.E auf Buffalo WZR-HP-AG300H - update
gelöst Frage von EpigeneseRouter & Routing11 Kommentare

Guten Tag, ich habe auf einem Buffalo WZR-HP-AG300H die alternative Firmware vom L.E.D.E Projekt geflasht. Ich bin es von ...

LAN, WAN, Wireless
WLAN Reichweite erhöhen mit neuer Antenne
gelöst Frage von gdconsultLAN, WAN, Wireless8 Kommentare

Hallo, ich besitze einen TL-WN722N USB-WLAN Dongle mit einer richtigen Antenne. Ich frage mich jetzt ob man die Reichweite ...

Router & Routing
Fritzbox Gastnetz - exposed Host - zur Sophos IPTV
Frage von medikopterRouter & Routing7 Kommentare

Hallo zusammen, ich habe eine Frage bezüglich des Fritz box Gastzugangs an einer Sophos UTM Home. An liebsten wäre ...