Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Formel "verschönern"
#1
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 Wink

Vielen Dank im Voraus.


Angehängte Dateien Thumbnail(s)
   

.xlsx   Formel-Verschoenerung.xlsx (Größe: 18,79 KB / Downloads: 15)
Herzliche Grüße aus dem Rheinland
Jörg

[Windows 10, Microsoft 365]
Antwortento top
#2
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'
ABCDEFGH
2Zum Testen z.B. nach "Alter = 22" filtern ...
3
4NameVornameAlterStatusHSS2
5MüllerMax22S1
8FischerFranz22N0
11BreitnerKatrin22S1
12HobelMica22V0

NameBezug
Status=Tabelle1!$D$5:$D$15
status_1=Tabelle1!$E$5:$E$15

ZelleFormel
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
Gruß Jörg

"Wer immer tut, was er schon kann, bleibt immer das, was er schon ist." - Henry Ford
[-] Folgende(r) 1 Benutzer sagt Danke an Jockel für diesen Beitrag:
  • LuckyJoe
Antwortento top
#3
Hallo Lucky Joe,

Zitat: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
[-] Folgende(r) 1 Benutzer sagt Danke an Fred11 für diesen Beitrag:
  • LuckyJoe
Antwortento top
#4
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'
AB
11
225
33
44
NameBezug
Status=Tabelle1!$A$1:$A$4
ZelleFormel
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
   \\\|///      Hoffe, geholfen zu haben.
   ( ô ô )      Grüße, André aus G in T  
 ooO-(_)-Ooo    (Excel 97-2016)
[-] Folgende(r) 1 Benutzer sagt Danke an schauan für diesen Beitrag:
  • LuckyJoe
Antwortento top
#5
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.
Herzliche Grüße aus dem Rheinland
Jörg

[Windows 10, Microsoft 365]
Antwortento top
#6
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...
Gruß Jörg

"Wer immer tut, was er schon kann, bleibt immer das, was er schon ist." - Henry Ford
[-] Folgende(r) 1 Benutzer sagt Danke an Jockel für diesen Beitrag:
  • LuckyJoe
Antwortento top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste