Hallo zusammen,
ich hoffe Ihr könnt mir auch diesmal weiterhelfen, bevor ich meinem Rechner durch eine Fallstudie zeigen muss, in welchem Stockwerk ich sitze. :26:
Folgende Aufgabe: Bei der Filterung von Werten (nicht Zeilen) einer Pivottabelle verändern sich die Werte von Teil- und Gesamtergebnis bekanntlich nicht. Daher habe ich über der PT eine Ergebniszeile eingebaut, die mir die jeweiligen Ergebnisse mit der Teilergebnisfunktion anzeigen soll. Für Summe und Durchschnitt habe ich das bereits gelöst bekommen. Bei Min und Max verzweifle ich jedoch an der Herausforderung, dass die Ergebniszeilen nicht mitgerechnet werden sollen.
Die Funktion soll also beispielhaft folgendes tun: Berechne das Teilergebnis Minimum der Zellen O12:O500 ohne die Zeilen A12:A500, deren Inhalt "*ergebnis" oder "*Ergebnis" enthält. Analog für Spalte P dann das Maximum. Aber das würde ich dann sicherlich auch selbst umgeschrieben bekommen
Vielen Dank schon mal für eure Hilfe!
VG Schumi
Und das, was Du bereits hast, soll ich jetzt nachbauen?
Hallöchen,
hier mal das Prinzip. Du bildest den Bereich mit der ersten Datenzelle, hier H2, und definierst die letzte Zeile mit INDEX usw.
| Arbeitsblatt mit dem Namen 'Tabelle1' |
| G | H | I |
| 1 | Zeilenbeschriftungen | Summe von Zahl | 6000 |
| 2 | Erfurt | 3000 | |
| 3 | Gera | 1000 | |
| 4 | Jena | 2000 | |
| 5 | Gesamtergebnis | 6000 | |
| Zelle | Formel |
| I1 | =SUMME(H2:INDEX(H:H;MAX(INDEX((H:H<>"")*ZEILE(H:H);0))-1)) |
| Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016 |
| Diese Tabelle wurde mit Tab2Html (v2.6.2) erstellt. ©Gerd alias Bamberg |
Hallo zusammen,
leider sind die bisherigen Vorschläge nicht erfolgreich. Daher hoffe ich, dass die nun angefügte Beispieldatei etwas Licht ins Fragedunkel bringt.
In Tabelle 2 (Pivot) soll in Zelle D2 das Minimum und in Zelle E2 das Maximum des Teilergebnisses ausgewiesen werden. Dabei sollen jedoch bei der Filterung die Werte nicht berücksichtigt werden, die in Spalte A den Teilstring "ergebnis" oder "Ergebnis" enthalten.
Wenn z.B. das Wertefeld "Anzahl von Anfrage" auf die Werte 2, 3 und 8 gefiltert wird, so dass nur die Ergebniszeilen eingeblendet sind, rechnet meine Formel leider immer noch mit den angezeigten Zeilenwerten. Ich weiß leider nicht, wie ich das unterdrücken kann und hoffe inständig auf Eure Hilfe. Lieben Dank vorab.
VG Schumi
Hallöchen,
seh' ich anders
| Arbeitsblatt mit dem Namen 'Tabelle2' |
| A | B | C | D |
| 1 | Lieferant | Alpha | | 9 |
| 2 | | | | 12,0 |
| 3 | Kunde | Anfrage | Anzahl von Anfrage | Minimum von Min Preis |
| 12 | Kunde 3 | 10 | 1 | 12,00 € |
| 14 | Kunde 3 Ergebnis | | 2 | 12,00 € |
| 15 | Gesamtergebnis | | 8 | 9,00 € |
| Zelle | Formel |
| D1 | {=WENN($A$4:$A$500<>"ergebnis";TEILERGEBNIS(5;$D$4:$D$500);0)} |
| D2 | =WENN(NICHT(ISTZAHL(SUCHEN("ergebnis";$A$4:INDEX(A4:A500;MAX(INDEX((A4:A500<>"")*ZEILE(A1:A497);0))-1))));TEILERGEBNIS(5;$D$4:INDEX(D4:D500;MAX(INDEX((D4:D500<>"")*ZEILE(D1:D497);0))-1));0) |
| Achtung, Matrixformel enthalten! |
| Die geschweiften Klammern{} werden nicht eingegeben. |
| Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine. |
| Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016 |
| Diese Tabelle wurde mit Tab2Html (v2.6.2) erstellt. ©Gerd alias Bamberg |
Hallo schauan,
vielen Dank für Deine Hilfestellung. Ich habe die Formel für D2 bei mir eingebaut, allerdings macht sie leider noch nicht das was sie soll. Hierzu hätte ich noch ein paar Fragen:
In den Indexfunktionen wird nach MAX gesucht, aber bei TEILERGEBNIS nach MIN (Funktion 5). Ist das richtig?
Warum haben Zeilenbezüge ZEILE(A1:A497) und ZEILE(D1:D497) haben eine andere Matrix als der Rest der Formel?
Nach "ergebnis";$A$4 steht ein Doppelpunkt. Müsste da nicht ein Semikolon stehen?
Grundsätzlich sollte bei der Filterung der Spalte C nach den Werten 2, 3 und 8 als Ergebnis eine Null stehen, da nur Ergebniszeilen angezeigt werden, die nicht mitgerechnet werden sollen. Leider verstehe ich die Funktion Deiner Formel nur teilweise, so dass ich hoffe, dass Du mir bei der Anpassung nochmal behilflich sein kannst. Besten Dank schon mal.
VG Schumi
Hallöchen,
Der Max hängt damit zusammen dass die Ergebniszeile ausgenommen wird.
1:497 deswegen, weil die Matrix 4:500 497 Zeilen enthält.
Hallo André,
leider funzt die Formel aber trotzdem nicht, auch wenn ich sie etwas besser verstehe. Wenn die Filterung so gesetzt ist, dass nur die Ergebniszeilen angezeigt werden, müsste dann nicht Null als Min-Wert angezeigt werden?
Ich hoffe, ich nerve nicht zu sehr. Besten Dank!
VG Schumi
Hallöchen,
Eigentlich ist das Minimum vom Ergebnis doch das gewünschte Minimum des Kunden?
Hallo zusammen,
ich versuche nochmal einen Aufruf. Die Formel von schauan funktioniert insoweit leider noch nicht, als dass bei der Betrachtung des Minimums die Ergebniszeilen immer noch mit einbezogen werden. Ich würde mich riesig freuen, wenn Ihr Euch der Herausforderung nochmal annehmen würdet. Lieben Dank vorab!
"Grundsätzlich sollte bei der Filterung der Spalte C nach den Werten 2, 3 und 8 als Ergebnis eine Null stehen, da nur Ergebniszeilen angezeigt werden, die nicht mitgerechnet werden sollen."
VG Schumi