Formel "verschönern"

[LuckyJoe]

Hallo,

ich habe eine Frage an die "Formel-Spezialisten", bei der es weniger um das Inhaltliche als vielmehr um die "Gestaltung" der Formel selbst geht:

In meiner Beispiel-Tabelle habe ich eine Spalte "Status", in der ich einen Bereich auch so benannt habe. Ich möchte nun in einer Zelle (in meinem Beispiel G20) angezeigt bekommen, wie oft ein bestimmter Status vorkommt. Das läßt sich einfach mit der Formel

Code:

=ZÄHLENWENNS(Status;"=S")

abfragen. Diese Formel berücksichtigt allerdings keine Filterung der Tabelle, so dass es zu einem "unerwünschten" Ergebnis kommt, wenn ich meine Tabelle beispielsweise nach "Alter" filter.

Mittlerweile bin ich soweit gekommen, dass ich eine Formel gefunden habe, bei der ich den Bereichsnamen ("Status") verwenden kann, also keine absoluten Bezüge verwenden muss, was mir das Lesen von Formeln ungemein erleichtert:

Code:

=SUMMENPRODUKT(TEILERGEBNIS(3;INDIREKT("D" & ZEILE(Status)))*(Status="S"))

Das "I-Tüpfelchen" wäre es für mich nun noch, wenn ich das "D" in der Formel ersetzen könnte, um die Formel noch etwas "dynamischer" gestalten zu können. Meine Versuche mit "Spalte(Status)" oder Ähnlichem sind aber alle gescheitert. Daher meine Frage: Kann man die Formel noch so weit "verschönern", dass sie ohne den Bezug zu Spalte "D" auskommt? Oder gibt es gar eine viel einfachere Formel, die zum gewünschten Ergebnis führt?

Anbei die Beispiel-Tabelle und ein Bild, das die Fragestellung deutlich macht - hoffe ich

Vielen Dank im Voraus.

[Jockel]

Hallo, ich weiß nicht. ob ich das richtig verstanden habe (das "S" könnte man hier flexibel tun...) ... die Hilfsspalte könnte man ja ausblenden...  

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G	H
2	Zum Testen z.B. nach "Alter = 22" filtern ...
3
4	Name	Vorname	Alter	Status	HS		S	2
5	Müller	Max	22	S	1
8	Fischer	Franz	22	N	0
11	Breitner	Katrin	22	S	1
12	Hobel	Mica	22	V	0

Name	Bezug
Status	=Tabelle1!$D$5:$D$15
status_1	=Tabelle1!$E$5:$E$15

Zelle	Formel
H4	=AGGREGAT(9;7;status_1)
E5	=(D5=$G$4)*1
E8	=(D8=$G$4)*1
E11	=(D11=$G$4)*1
E12	=(D12=$G$4)*1

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

[Fred11]

Hallo Lucky Joe,

Kann man die Formel noch so weit "verschönern", dass sie ohne den Bezug zu Spalte "D" auskommt?

Die Funktion INDIREKT hat einen zweiten Parameter, mit dem man angeben kann, ob der Zellbezug in der A1- (=> Standard) oder in der Z1S1-Schreibweise angegeben wird.

=INDIREKT(Bezug;[A1])

Wenn man bei INDIREKT die Z1S1-Schreibweise verwendet (2. Parameter =>" FALSCH"), dann kriegt man in deiner Formel den Spaltenbuchstabe "D" weg.
Dann funktioniert das mit SPALTE(Status).

=SUMMENPRODUKT(TEILERGEBNIS(3;INDIREKT("Z"& ZEILE(Status)&"S"&SPALTE(Status);FALSCH))*(Status="S"))

(Aber so richtig "schöner" wird die Formel dadurch jetzt nicht direkt…)


Wo man noch eine Kleinigkeit "verschönern" könnte, ist bei deiner ZÄHLENWENNS-Formel:
Das = vor dem S wird hier nicht benötigt....

Gruß
Fred

[schauan]

Hallöchen,

mal ganz allgemein, verwende statt INDIREKT (und BEREICH.VERSCHIEBEN) besser INDEX, da hast Du Zeile und Spalte, kannst auch Bereichsnamen verwenden und Bereiche bilden und hast keine Volatilität …

Arbeitsblatt mit dem Namen 'Tabelle1' A B 1 1 2 2 5 3 3 4 4
Name Bezug Status =Tabelle1!$A$1:$A$4	Zelle Formel B2 =SUMME(INDEX(Status;2):INDEX(Status;3))

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.2) erstellt. ©Gerd alias Bamberg

[LuckyJoe]

Hallo zusammen,

vielen lieben Dank für eure Kommentare und Tipps; es lohnt sich wohl doch, das etwas verstaubte Formelwissen etwas aufzufrischen.

@Jörg: die Aggregat-Formel ist schön kurz und knackig, aber leider muss ich dafür eine zusätzliche Hilfsspalte und eine zusätzliche Zelle (G4 in deinem Beispiel) verwenden. Mit den Formeln in Spalte E wieder dazugelernt, insbesondere, wenn man statt direkter Bezüge den benannten Bereich "Status" verwendet und die Hilfsspalte damit automatisch aufgefüllt wird.

@Fred: Danke für die Info mit dem zusätzlichen Parameter (hätte ich sehen müssen). Schön, dass es damit ohne direkten Spaltenbezug funktioniert, aber du hast Recht: schöner wird die Formel damit nicht.

@André: Danke für den Tipp mit INDEX, allerdings habe ich es nicht geschafft, das mit in mein Vorhaben einzubauen, vor allem, weil INDEX wohl keine Rücksicht auf eine Filterung nimmt.

[Jockel]

Hallo Jörg musst du nicht, ich hatte eine andere Idee und zwar das Kriterium flexibel zu machen also auch verschieden Spalten je nach Kriterium zu zählen. Du kannst freilich auch "S" hart in die Formel schreiben - Und dann brauchst du G4 nicht. Hilfsspalten dienen in aller Regel der Übersichtlichkeit  und hier erleichtert sie das Vorhaben ungemein. Man könnte auch mehrere Sachen in der Spalte abfragen und filtern, was sonst schwierig ist...