Office: (Office 365) ZÄHLENWENNS - Formelteile aus Zelle übernehmen

Hallo zusammen,

ich erstelle derzeit in einer Excel-Datei ein Reporting, das unterschiedliche Tabellenblätter umfasst. Hierfür zähle ich Zellen, die diverse Kriterien erfüllen mittels ZÄHLENWENNS. Nun ist es so, dass ich etwa Dreiviertel der Kriterien wiederholt in mehreren der Formeln benutzen möchte, wohingegen des übrige Viertel der Kriterien jeweils variiert. Deshalb war meine Idee, den gleichbleibenden Teil der Formel als Text in eine gesonderte Zelle auszulagern und dann innerhalb meiner ZÄHLENWENNS-Formeln darauf zuzugreifen. Leider funktioniert das nicht so, wie ich es mir vorstelle.

Beispiel, wie das bisher manuell aussieht:
=ZÄHLENWENNS('Tabelle 1'!$T:$T;"Ja";'Tabelle 1'!$L:$L;"<>"&$B$10;'Tabelle 1'!$L:$L;"<>"&$B$16;'Tabelle 1'!$L:$L;"<>"&$B$22;'Tabelle 1'!$L:$L;"<>"&$B$28;'Tabelle 1'!$L:$L;">"&$B$2-1;'Tabelle 1'!$L:$L;"<"&$C$2-1)

Die rot markierten Teile möchte ich auslagern. Bislang habe ich das ohne Erfolg so probiert:

• =ZÄHLENWENNS('Tabelle 1'!$T:$T;"Ja";'Tabelle 1'!$L:$L;"<>"&$B$10;$I36)
• =ZÄHLENWENNS('Tabelle 1'!$T:$T;"Ja";'Tabelle 1'!$L:$L;"<>"&$B$10;"$I36")
• =ZÄHLENWENNS('Tabelle 1'!$T:$T;"Ja";'Tabelle 1'!$L:$L;"<>"&$B$10;TEXT($I36;))

Vielleicht kann mir jemand auf die Sprünge helfen und erklären, ob und wie ich meinen Pöanm umsetzen kann.

Vielen Dank im Voraus und

beste Grüße
Sebastian

 

Hola,
mit Formeln wird das mMn nicht funktionieren. Das naheliegendste wäre INDIREKT(), aber du schreibst das in einen Bereich wo Zählenwenns() einen Bereich erwartet. Du möchtest aber Bereich, Suchkriterium, Bereich, Suchkriterium.... da rein schreiben.
Evtl ginge das über VBA, das kann ich aber nicht beurteilen.
Gruß,
steve1da

 

Hallo,

mich hat die Herausforderung gereizt, die Funktion ZÄHLENWENNS(Bezug1;Ausdr1;Bezug2;Ausdr2;...) mit den Mitteln von Excel365 nachzubauen. Zu diesem Zweck habe ich 3 benutzerdef. LAMBDA-Funktionen in den Namensmanager eingefügt zusammen mit einem kurzen Kommentar.
2 Funktionen sind als Hilfsfunktionen gedacht und werden von der eigentlichen LAMBDA-Funktion
=ZählenWennsSpez(KritAnfg; [KritSpei])
aufgerufen. Der Einsatz des 1.Parameters "KritAnfg" ist Pflicht, während die Übergabe des 2.Parameters "KritSpei" optional ist. Sind beide vorhanden, werden sie funktionsintern einfach mit ";" verkettet.

Es dürfen beliebig viele Bezug/Ausdr-Paare in den beiden Parametern übergeben werden, was sich natürlich auf die Rechenzeit niederschlägt.

Die Kriterienbereiche "Bezug1", "Bezug2", ... müssen wie bei der Originalfunktion gleich groß sein. Werden hier ganze Spalten angegeben (wie in deinen Beispielen), wirkt sich das natürlich auf die Rechenzeit aus.

Die Kriterienausdrücke "Ausdr1", "Ausdr2",... sind nicht so leistungsfähig wie bei der Originalfunktion, aber man kann
optional einen Vergleichsoperator (zB. <> oder <=), gefolgt von einem "&"-Zeichen,
gefolgt von einer Zellangabe (zB. $B$10) oder einer Konstanten (zB. "ja"),
optional gefolgt einer Additions/Subtraktions-Konstanten (zB. +12 oder -1)
eingeben.
Als Beispiele: "<>"&$B10-1 oder "ja" oder $B$16

Die Auswertung von Zelladress-Angaben erfolgt über die INDIREKT-Funktion. Diese ist, wie allgemein bekannt, eine volatile Funktion.

In den gelb eingefärbten Zellen im Arbeitsblatt "Tabelle2" habe ich dir 4 Musteraufrufe der Funktion eingefügt.

Datei liegt bei.

 

Moin Sebastian,

steve verweist auf VBA, wo ich sehr gerne unterwegs bin. Und das bedingte Zusammenbauen von Formeln ist dort tatsächlich relativ einfach. Jetzt kommt der Haken: Ich bin mit Formeln tatsächlich so bescheiden unterwegs, dass ich Deine nicht verstehe (hüstel). Mein Vorschlag:

Zeige doch einfach mal eins, zwei, drei Zeilen (muss keine Mappe sein) Ausgangsmaterial (Daten) und dann das jeweilige Wunschergebnis.

Dann lässt sich sicher etwas VBAsteln

Grüße

 

Mal als Schnellschuss:

Der folgenden Code in ein Arbeitsblatt ....

Code:

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim wsf As WorksheetFunction
Dim sFormula As String, sPart As String
Dim i As Integer, r As Integer 'sic!

Set wsf = WorksheetFunction
Cancel = True
  
r = Target.Row
    For i = 1 To 3
        sPart = Cells(r, i + 1)
        sFormula = sFormula & sPart
    Next i
Cells(r, 1) = sFormula
End Sub

... macht dies: Du trägst in die Spalten B,C und D die Teile Deines Codes ein. Ein Doppelklick in die Zeile kombiniert diese Parts dann in Spalte A.

Bisher habe ich das nur mit Text getestet. Wenn Dich der Weg interessiert, schick die Beispielzeilen. Als kleiner Hinweis hinterher: Sehr viele Formeln in XL lassen sich in VBA nachbilden und sind dann erheblich flexibler einzusetzen. Im Code habe ich bereits die Nutzung von Arbeitsblattfunktionen vorbereitet, die bei konsequenter VBA-Programmierung aber nicht gebraucht wird.

Grüße!

 

Hi,

mit aktuellem Excel geht das auch per Formel!
Allerdings kannst du den roten Teil nur in eine Zelle auslagern, die genügen Platz nach unten hat, da man Spillen muss. Wenn du wie hier ganze Spalten als Kriterium verwendest, kann die "Hilfszelle" also nur in Zeile 1 stehen. Aus Performancegründen würde ich allerdings den Kriterienbereich so weit wie möglich einschränken.

Ich habe als Hilfzelle H1 verwendet. Dort hinein kommt die Formel:
H1: =('Tabelle 1'!$L:$L<>$B$16)*('Tabelle 1'!$L:$L<>$B$22)*('Tabelle 1'!$L:$L<>$B$28)*('Tabelle 1'!$L:$L>$B$2-1)*('Tabelle 1'!$L:$L<$C$2-1)
Also im Grunde genommen die Kriterien nur als Logik-Formel formuliert. Damit spillst du eine komplette Spalte und erhältst 0en öder 1en, je nachdem, ob die Bedingungen für die Zeile erfüllt sind oder nicht.
In die Zelle, in der bisher dein ZÄHLENWENNS steht, kommt jetzt die Formel
=ZEILEN(FILTER('Tabelle 1'!L:L;('Tabelle 1'!T:T="Ja")*('Tabelle 1'!$L:$L<>$B$10)*H1#))
Ohne das ZEILEN() würden dir die Werte der Spalte L angezeigt werden, für die die Bedingungen zutreffen. Aber du willst ja die Anzahl haben. Daher noch das ZEILEN drum rum. ANZAHL (wenn es nur Zahlen sind) oder auch ANZAHL2 (wenn auch Texte vorkommen können) würde auch funktionieren.

@d'r Bastler: dem sic! in deinem Code stimme ich sofort zu, wenn du da an der korrekten Stelle noch ein k einfügst!

 

@Helmut: dann ergänze hier Dep doch bitte noch um ein p ...

Was eine dümmliche Debatte ....

 

Danke für die Beleidigung. Das p hast du wohl eher selbst verdient.

Mach doch mal z.B. in Zeile 40.000 einen Doppelklick, während dein Code im Blatt steht. DU weist genau wieso das passiert. Andere wissen es vielleicht nicht. Denen dann bewußt solchen Code ohne entsprechenden Hinweis zu empfehlen ist - nein, ich sage es besser nicht.

Also Warnung für alle: Integer als Variable für eine Zeile ist in Excel ungeeignet, da es zu einem Fehler kommt, wenn man eine Zeile jenseits von Zeile 32.768 damit ansprechen will. Abhilfe: Statt einer Integer-Variablen eine Long-Variable verwenden. Man handelt sich dadurch keinerlei Nachteile, aber einige Vorteile ein. Neben dem größeren Zahlenbereich und weniger Tipparbeit beim Deklarieren, läuft ein Programm damit sogar minimal schneller. Unser Bastler wurde von mir schon oft auf diesen Umstand hingewiesen. Sein einziger Kommentar: Habe ich schon immer so gemacht und werde ich nicht ändern. sic!

 

Was eine dümmliche Debatte ....

 

Guten Morgen Bastler. Du hast recht. Die Debatte ist dümmlich und überflüssig. Selbst in der von dir verlinkten Diskussion kommst du selbst zum Ergebnis, dass

• dein Code unter Umständen den Abflug macht
• Integer keinerlei Vorteil zu Long bietet
• du es nur aus Gewohnheit machst (und ev. um zu provozieren)

Daher lasse ich es jetzt gut sein. Ich lasse mir dennoch nicht nehmen, immer wieder andere auf diesen offensichtlichen Fehler hinzuweisen.

 

Was'n dumm Zeuch! Lern lesen!

wenn Du echt nicht Besseres zu tun hast....

Interessanterweise hast in allen Deppendiskussionsbeiträgen darauf verzichtet, den jeweils für den Fragesteller hilfreichen Vorschlag auch nur zu erwähnen.

Kämpfe weiter gegen Deine Windmühlen ...