Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1580to1584
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Formelhilfe/Formelvereinfachung

Formelhilfe/Formelvereinfachung
22.09.2017 14:04:09
Yasso
Hi,
kann mir bite jemand helfen.
Es geht um aktivierungen mit monatsversätzen.
Bsp. von meinen 10 kunden im Jan haben im jan 3% im feb 70% im mrz 9% eine karte aktiviert.
https://www.herber.de/bbs/user/116448.xlsx
Danke und Gruß
Yasso

19
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: nachgefragt ...
22.09.2017 15:04:07
...
Hallo,
... was meinst Du genau mit Formelvereinfachung? Du hast doch eine einfache Formel.
Eine Zusammenfassung dieser zu einer Formel, die Du evtl. von Q6 nach U6 kopieren möchtest, würde die Formel lediglich verkomplizieren. Außerdem würde ich dafür eine etwas andere Datenstruktur Deiner Prozentsätze für günstiger halten (z.B. diese "gespiegelt" um eine Zeile).
Oder willst Du Deine Formel nur für die aktuellen Monate angepasst/aufgestellt wissen, oder...?
Gruß Werner
.. , - ...
AW: nachgefragt ...
22.09.2017 15:33:48
Yasso
Hallo Werner,
ich habe zwar eine Formel...
=SUMME(INDEX($J$6:$N$11;N(WENN(1;SPALTE($A$1:A1)));SPALTE(A1))*INDEX($C$6:$G$6;;N(WENN(1; SPALTE($A$1:A1)))))
Jedoch zieht diese Formel nur den Jan korrekt und ab Feb werden falsche Werte gezogen.
Grüße
Yasso
Anzeige
AW: weiter nachgefragt ...
22.09.2017 15:45:46
...
Hallo Yasso,
... möchtest Du Deine Formel korrigiert haben?
Ich hatte unten stehenden Lösungsansatz für Deine "gespiegelte" Prozentwerte. Hilft Dir diese?
 CDEFGHIJKLMNOPQRSTU
5Mon 1Mon 2Mon 3Mon 4Mon 5  Mon 1Mon 2Mon 3Mon 4Mon 5  Mon 1Mon 2Mon 3Mon 4Mon 5
6510151020 Monat -5       3,57,5111016,5
7      Monat -4    5%       
8      Monat -3   5%5%       
9      Monat -2  10%10%5%       
10      Monat -1 30%30%5%10%       
11      Monat-070%60%50%80%70%       
12                   

Formeln der Tabelle
ZelleFormel
Q6{=SUMME(MTRANS(INDEX(J:J;11-SPALTE(A1)+1):J$11)*$C6:C6)}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Anzeige
AW: weiter nachgefragt ...
22.09.2017 21:19:28
Yasso
Hallo Werner,
das ist schon echt super Vielen lieben Dank.
Kann ich das auch auf meine Ursprungsdatei übertragen?
Da habe ich einmal ein Arbeitsblatt mit den Mengen ein Arbeitsblatt mit den Quoten und im letzten Arbeitsblatt die Ergebnisse.
Aber ich komme da nicht auf die Werte die ich im Ergebnis habe :(
Ich habe die Datei mal hochgeladen.
Danke und schönen Abend :)
Link der datei
https://www.herber.de/bbs/user/116455.xlsx
AW: Deine Frage lässt sich mit JA beantworten ...
23.09.2017 11:36:33
...
Hallo Yasso,
... allerdings gilt dies nur prinzipiell. Zunächst wären noch ein paar Unklarheiten zu klären.
Offen ist momentan jedoch warum Du für Januar keine Ergebniswerte ermittelt hast und wozu die Prozentangaben in "A.Versatz" über das Jahr 2017 angeben sind? Nicht ganz eindeutig ist im weiteren ist, ob die negativen Prozentwerte berücksichtigt werden sollen oder nicht.
Außerdem liegt bei Dir in dieser Tabelle mE Schreibfehler in E143 und evtl. E208 (sollte es da nicht 3.2 lauten?) vor. Oder?
Ich meine Deine komplette Tabelle (inklusive Teilspalten- und Zeilensummen) lässt sich dann mit einer einzigen Formel berechnen.
Ich bin dann aber ab sofort heute offline.
Gruß Werner
.. , - ...
Anzeige
AW: Deine Frage lässt sich mit JA beantworten ...
26.09.2017 10:10:48
Yasso
Hallo Werner,
sorry das ich mich erst jetzt melde.
Die Werte im Januar fehlen...habe ich jetzt angepasst.
Die Prozentwerte in A Versatz sagen mir, in welchem Monat ich wieviel Prozent eine Aktivierung durchführe.
- Bsp.1.1 Obst: Jan: 100 Obst; A.Versatz 53,4 % = 53
Feb: 100 Obst, A.Verstaz 49,6% = 49,6 plus die anteiligen aus dem Jan (also con den 100 kommen noch laut A.Versatz 22,8% im Februar, somit habe ich im Februar 72. und dann wird das so weiter fortgeführt.
Die negativen Prozente habe ich entfernt, danke.
Und den Schreibfehler habe ich auch entfernt. Danke
Die Datei habe ich nochmal mit den Korrekturen hochgeladen.
Ich hoffe da gibt es eile Lösung, damit ich weniger manuellen Aufwand habe.
https://www.herber.de/bbs/user/116526.xlsx
Danke
Anzeige
AW: überprüfe mal Deine Teilergebnisse ...
26.09.2017 11:29:32
...
Hallo Yasso,
... für 2.5 Tierhaltung in den Monaten Juli bis Dez, da ermittelt meine Formel viel höhere Werte. Gibt es für Deine Ergebniswerte da eine Begründung?
Ich melde mich dann am Nachmittag dazu wieder.
Gruß Werner
.. , - ...
AW: überprüfe mal Deine Teilergebnisse ...
26.09.2017 12:45:14
Yasso
Hallo Werner,
danke für den Hinweis.
Da war die Formel falsch... :) passiert halt wenn man manuell Formeln anpasst.
Habe es mal korrigiert und hoffe das es nun passt..
https://www.herber.de/bbs/user/116537.xlsx
Danke und Grüße
Yasso
AW: nun dann ...
26.09.2017 15:22:42
...
Hallo Yasso,
... meine nachfolgende Formellösung zeigt auf, dass das mein Gesamt-Ergebnis für 2017 noch immer etwas höher ist, als was Du ermittelt hast. Das liegt offensichtlich daran, dass meine Formel gegenüber Deinen Formeln sämtliche Prozentwerte in die Berechnung einbezogen hat.
Auch wenn man nachfolgendes normalerweise nicht machen sollte (für einfachere Nachvollziehbarkeit wie auch spätere evtl. erforderliche Anpassungen sind kürzere Formeln sinnvoller) hab ich hier mal mit lediglich einer Zellformel, kombiniert mit einer bedingten Formatierungsformel, die gesamte Tabelle also inklusive sämtlicher Teilsummen und der Monats-/wie Jahressummen und der Überprüfung auf Übereinstimmung der Vergleichswert sowie dem Layout für die Ergebniszellen aufgestellt. Es können auch weitere Zeilen (mit der Formel) eingefügt werden.
Einzig zusätzlich notwendig (für die monatliche Gesamtsumme) zuvor in E51 noch: "Gesamt" schreiben.
Dann die nachfolgende Formel in F5 einkopieren und diese nach rechts bis Spalte R und nach unten bis mindestens Zeile 51 ziehend kopieren.
 F
5216

Formeln der Tabelle
ZelleFormel
F5=WENN(E5="";"";WENN($E5="Gesamt";SUMME(WENN(ISTFEHL(--LINKS($E4:$E$5;3)); F4:F$5)); WENNFEHLER(WENN(LÄNGE(F$4)>4;WENN(ISTZAHL(--LINKS($E5;3)); SUMME(INDEX(Ist!$F:$F;VERGLEICH($E5;Ist!$E:$E;0)):INDEX(Ist!F:F;VERGLEICH($E5;Ist!$E:$E;0))*MTRANS(INDEX(A.Versatz!F:F;VERGLEICH($E5;A.Versatz!$E:$E;0)+1):INDEX(A.Versatz!F:F;VERGLEICH($E5;A.Versatz!$E:$E;0)+SPALTE(A1)))); SUMMEWENN($E6:$E19;LINKS($E5;2)&"*";F6:F19)); SUMME($F5:INDEX(5:5;SPALTE()-1))); "?")))

Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
F51. / Formel ist =(($E5>0)*ISTFEHL(--LINKS($E5;3))+(LÄNGE(F$4)=4))*(F5>0)*(F5<>"")Abc


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Anzeige
AW: nun dann ...
27.09.2017 09:40:49
Yasso
Hallo Werner,
vielen Dank für deine Mühe und Unterstützung.
Aber wenn ich die Formel eingebe, wird nur die der Jan richtig gerechnet und in den restlichen Monaten stehen dann drei Fragezeichen, welche er aus deiner Formel nimmt.
Ich habe die Formel in F5 reinkopiert und nach rechts gezogen bis R5 und dann runter gezogen.
Jan 17 Feb 17 Mrz 17 Apr 17
1.Kanal 215,7272898 0 0 0
1.1.Obst 53,35264901 ? ? ?
1.2.Gemüse 53,94912985 ? ? ?
1.3.Elektro 55,27065527 ? ? ?
1.4.Molke 53,15485564 ? ? ?
VG
Yasso
AW: die Formel ist eine Matrixformel ..
27.09.2017 12:29:35
...
Hallo Yasso,
... und muss entsprechend eingeben werden.
Sorry, da ist mir gestern wohl ein Missgeschick unterlaufen, dass die Formel nicht entsprechend gekennzeichnet war.
Die Formel muss zwingend mit STRG+SHIFT+RETURN abgeschlossen werden. Sie ist danach durch {} gekennzeichnet. Erst danach die Formel nach rechts und unten kopieren.
Hier vorsichtshalber nochmal, wie die Formel in F5 nach korrekter Eingabe auszusehen hat:
 F
5216

Formeln der Tabelle
ZelleFormel
F5{=WENN(E5="";"";WENN($E5="Gesamt";SUMME(WENN(ISTFEHL(--LINKS($E4:$E$5;3)); F4:F$5)); WENNFEHLER(WENN(LÄNGE(F$4)>4;WENN(ISTZAHL(--LINKS($E5;3)); SUMME(INDEX(Ist!$F:$F;VERGLEICH($E5;Ist!$E:$E;0)):INDEX(Ist!F:F;VERGLEICH($E5;Ist!$E:$E;0))*MTRANS(INDEX(A.Versatz!F:F;VERGLEICH($E5;A.Versatz!$E:$E;0)+1):INDEX(A.Versatz!F:F;VERGLEICH($E5;A.Versatz!$E:$E;0)+SPALTE(A1)))); SUMMEWENN($E6:$E19;LINKS($E5;2)&"*";F6:F19)); SUMME($F5:INDEX(5:5;SPALTE()-1))); "?")))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
F51. / Formel ist =(($E5>0)*ISTFEHL(--LINKS($E5;3))+(LÄNGE(F$4)=4))*(F5>0)*(F5<>"")Abc


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Anzeige
AW: die Formel ist eine Matrixformel ..
27.09.2017 17:43:42
Yasso
Hallo Werner,
danke. Super.
Wie kann ich die Formel in meine Ursprungsdatei reinkopieren.
HAbe mal meine eigentliche Datei hochgeladen.
Irgendwie habe ich es nicht hinbekommen :(
Kann ich die Formel nicht einfach kopieren?
Glaube nicht, weil ich in meiner Ursprungsdatei in Spalte T anfange Oder?
Datei
https://www.herber.de/bbs/user/116591.xlsx
Danke und Grüße
Yasso
AW: Formel muss entsprechend angepasst werden ...
27.09.2017 20:35:49
...
Hallo Yasso,
... das schau ich mir dann Morgen an.
Gruß Werner
.. , - ...
AW: dürfte machbar sein ....
27.09.2017 22:05:05
...
Hallo Yasso nochmal,
... nimm Deine bisherige Daten-Beispieldatei ausgefüllt mit meiner Formel (ergibt ein Gesamt-Endergebnis 17.376). Dann füge in Tabellenblatt "Ergebnis" zuoberst soviel Leerzeilen ein, dass "1. Kanal" in Zeile 12 steht.
Nun füge in allen drei Tabellenblättern dieser Datei nach Spalte C soviel Leerspalten ein, dass "Jan.17" in Spalte T steht. Dann ändere lediglich in der Formel in T12:
von =WENN(E12="";"";... zu WENN($E12="";"";...).
Nicht vergessen: wieder als Matrixformel abschließen! Dann diese Formel nach rechts und unten kopieren. und es sollte wieder das gleiche Endergebnis ergeben.
Nun übertrage nur noch die Formel T12 in Dein Original in T12 und kopiere diese dort entsprechend.
Gruß Werner
.. , - ...
Anzeige
AW: dürfte machbar sein ....
28.09.2017 09:11:39
Yasso
Morgen Werner,
Danke, das klappt einwandfrei.
Wo kann ich Dir einen ausgeben? :)
Wirklich KLasse und lieben Dank für die tolle Unterstützung.
VG
Yasso
AW: na, dann ...
28.09.2017 16:12:33
...
Hallo Yasso,
... ich wüsste schon wann und wo :-)
Du kannst mich dazu direkt kontaktieren. Da es hier im Forum keine Möglichkeit gibt sich über PN auszutauschen übermittele ich Dir meine Mailadresse.
Meine Mailadresse erhältst Du, wenn Du in eine leere Standardexcelzelle folgende Formel schreibst: =WECHSELN("neopaCode";"Co";ZEICHEN(64)&"email.")
Solltest Du mir eine Mail senden, dann schreibe in den Betreff bitte: Herbers Excel Forum, thread "Formelhilfe für Yasso" und schreibe hier im thread, wenn Du mir die Mail abgesendet hast, weil diese bei mir erst einmal in meinem "Unbekannt-Ordner" landet. Und diese lösche ich sonst meist ohne große Prüfung, wenn mir der Absender und oder die Betreffzeile nicht bekannt ist.
Gruß Werner
.. , - ...
Anzeige
AW: na, dann ...
29.09.2017 14:27:13
Yasso
Hi Werner,
habe dir eine Mail gesendet.
VG
Yasso
AW: danke, hab sie erhalten ...
29.09.2017 17:25:19
...
Hallo Yasso,
... Antwortmail ist unterwegs.
Gruß Werner
.. , - ...
AW: na, dann ...
02.10.2017 14:04:52
Yasso
Hallo Werner,
eine Frage noch.
Wenn ich die Numerierungen entferne und ohne Numerierungen arbeiten möchte, stehen überall bei Mir 0 (nuller) Werte.
Also in jeder Zelle eine 0.
Ich glaube dass hängt mit deiner Formel zusammen, mit den Links(...) aber ganz genau komme ich nicht dahinter.
Was genau muss ich dann an der Formel ändern damit ich ohne Nummerierungen arbeiten kann?
Danke und Gruß
Yasso

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige