Microsoft Excel

Herbers Excel/VBA-Archiv

Aggregat-Funktion in Kombi mit Zählenwenns/Verweis

Betrifft: Aggregat-Funktion in Kombi mit Zählenwenns/Verweis von: Tanja
Geschrieben am: 21.08.2014 14:37:54

Hallo Forum,

ich habe ein paar Funktionen die ich so abwandeln will, dass sie sich nur auf den aktiven Filter beziehen.

Die erste Funktion ist eine Verweis-Funktion, die nach 2 Kriterien sucht (in C und in I) und entsprechend das Ergebnis aus Spalte J anzeigt. Ich habe nun die Aggregat-Funktion gefunden und diese würde sich optimal eignen, jedoch bietet sie kein Summewenn, etc.) Habt ihr eine Idee wie ich die Funktion erweitern kann oder wisst ihr wie der Aufbau sein müsste um mit der Aggregat-Funktion nach 2 Kriterien zu suchen?

=WENNFEHLER(VERWEIS(9;1/(C:C=(GANZZAHL(A3))/(I:I=E3));J:J);0)

Das andere wäre eine Zählenwenns-Funktion, die ich nun auch gerne nur auf den gefilterten Bereich beziehen würde:

=ZÄHLENWENNS(C:C;GANZZAHL(A2)I:I;E2)

Ich hoffe es geht ohne Bsp.-Datei. Mir geht es auch eher um den möglichen Aufbau einer Aggregat-Funktion welche ich mit Verweis bzw. Zählenwenn verbinden möchte.

Grüße und Danke

Tanja

  

Betrifft: Nicht anders als sonst, ... von: Luc:-?
Geschrieben am: 21.08.2014 16:21:29

…Tanja,
denn AGGREGAT kannst du im Ggsatz zu ZÄHLENWENN[S] auch ein Datenfeld als Ergebnis eines als 3.Argument notierten Ausdrucks (TeilFml) übergeben. Den Ausdruck fasst du so ab, dass er nur die Daten mit ihrem OriginalWert zurückgibt, die beide Bedingungen erfüllen.
Gruß, Luc :-?


  

Betrifft: AW: Aggregat-Funktion in Kombi mit Zählenwenns/Verweis von: Daniel
Geschrieben am: 21.08.2014 16:26:06

Hi
ich würde eine Hilfsspalte einfügen mit der Formel (als Zellbezug nimmst du eine Zelle in derselben Zeile in einer Spalte, die auf jeden Fall einen Wert enthält):

=Teilergebnis(103;A1) 
das Ergebnis dieser Formel ist 1 wenn die Zeile sichtbar ist und 0, wenn sie ausgeblendet ist.
Diese Spalte kannst du dann in deine SummeWenns/ZählenWenns/Summenprodukte als bedingung mit aufnehmen.

Gruß Daniel


  

Betrifft: AW: Aggregat-Funktion in Kombi mit Zählenwenns/Verweis von: Tanja
Geschrieben am: 21.08.2014 16:37:31

Hallo Daniel,

danke für deine Idee. Leider wird sie nicht so umsetzbar sein.
Ich habe die Spalten Datum, Schicht, Ressource und Dauer Stillstand. Mit den beiden Formeln möchte ich zum einen berechnen wie lange in einer Schicht an einem Tag eine oder mehrere Anlagen stillgestanden haben (je nach ressource ändert sich ja die gesamtstillstandszeit und somit hilft die hilfsspalte nicht weiter). Zum anderen möchte ich die Anzahl der Stillstände ermitteln. Hier genau dasselbe Problem. Die Anzahl ändert sich je nach gesetztem Filter. Somit benötige ich eine Formel die nur die sichtbaren Zeilen berücksichtigt und "rechnet".

@Luc:-?: Sorry, aber ich habe nichts von dem verstanden was du geschrieben hast. Ich bin KEIN Excel-Profi und war eigentlich nur froh die Aggregatfunktion gefunden zu haben, da ich es schon mit Teilergebnis nicht umsetzen konnte...


  

Betrifft: Ja, mit AGGREGAT kann man auch ... von: Luc:-?
Geschrieben am: 21.08.2014 17:51:20

…AusblendProbleme lösen, Tanja,
aber wir kommen wohl nicht ohne eine kleine BspTab, die verdeutlicht, was du willst und welche anderen Bedingungen eingebunden wdn sollen, weiter.
Ansonsten gibt's bekanntlich das Internet und Wikipedia, in dem man unbekannte Fachbegriffe nachschlagen kann. Als (Haupt-)Argu­ment der meisten XlFktt kann ein ZellBereich angegeben wdn, bei den ältesten und allerneuesten XlFktt stattdessen aber oft auch ein Datenfeld, d.h. zusammenhängende Daten, die auf Grund einer Manipulation in Form einer Fml (→Formel) von ihrem ursprünglichen Standort (ZellBereich) abgekoppelt wurden. Das einfachste Bsp für solch eine Manipulation wäre das Folgende:
A2:C9 ist ein ZellBereich → --A2:C9 (bedeutet 1*A2:C9) ist ein Datenfeld (in VBA auch Array genannt, was aber von allzuvielen auch synonym zu Bereich gebraucht wird, was aber eigentlich nicht iO ist). Wenn also aus einem Bereich nur der Wert bestimmter Daten, die eine bzw mehrere Bedingungen erfüllen müssen, ausgewählt wdn soll, schreibt man dafür eine Fml, zB WENN(A2:A9="x";A2:A9;""). Setzt man diese Fml als Argument einer anderen Fkt wie bspw SUMME ein, wdn folglich nur die Werte addiert, die die Wenn-Bedingung erfüllen. Damit das dann auch alle sind (immer für alle Zellen des Bereichs, hier also 8) und nicht nur der erste, muss die Fml als sog MatrixFml gekennzeichnet wdn (durch den besonderen Abschluss mit Tasten [Strg][Umsch][Enter]).
SUMME und damit auch AGGREGAT mit der entsprd Unterfkt ignoriert Texte (wie hier ""; alternativ könnte auch 0 verwendet wdn), so dass letztlich nur die Werte summiert wdn, die die Bedingung erfüllen.
Jetzt verstanden? ;-)
Luc :-?


  

Betrifft: AW: Ja, mit AGGREGAT kann man auch ... von: Daniel
Geschrieben am: 21.08.2014 17:53:24

soweit ich das überblicke, kann aber auch das Aggregat immer noch kein Zählen- und SummeWenn(s)...
Gruß Daniel


  

Betrifft: Deine Bemerkung lässt den Schluss zu, dass ... von: Luc:-?
Geschrieben am: 23.08.2014 00:33:01

…auch du meine Erläuterung nicht verstanden hast, Daniel! :->
Gruß, Luc :-?


  

Betrifft: AW: Deine Bemerkung lässt den Schluss zu, dass ... von: Daniel
Geschrieben am: 23.08.2014 01:11:31

ich habe, ehrlich gesagt, deine Erläuterungen gar nicht gelesen, weil dein Schreibstil mich einfach überfordert...

nein ich will dir nicht vorschreiben, wie du zu schreiben hast.
aber im Gegenzug erwarte ich von dir, dass du mir nicht vorschreibst, was ich lesen muss.


  

Betrifft: Na, dann war deine Bemerkung ja völlig ... von: Luc:-?
Geschrieben am: 23.08.2014 01:27:11

…gegenstandslos! :-]
Warum auch sollte AGGREGAT Fktt benutzen, die seine Möglichkeiten eher einschränken würden, wenn das objektiv gar nicht erforderlich ist?
Luc :-?


  

Betrifft: Bereich versus Matrix/Array von: {Boris}
Geschrieben am: 22.08.2014 07:59:12

Hi Luc,

hier wird tatsächlich oft unsauber bis gar nicht getrennt.
In einem unserer Bücher hatten wir das mit einem Brief und dessen Inhalt (=geschriebene Wörter) veranschaulicht:
Der Brief ist der Bereich (quasi der Container) und die Wörter sind die Matrix / das Array.
Und manche Funktionen verlangen nunmal zwangsweise den Container und nicht bloß dessen Inhalt ;-)

VG Boris


  

Betrifft: Eure Analogie ist sehr plastisch und leider ... von: Luc:-?
Geschrieben am: 22.08.2014 23:50:28

…vom konkreten Faktum her zutreffend, Boris,
denn ähnlich wie beim Brief als Container seines Inhalts ist nicht einzusehen, warum nicht auch der bloße Inhalt ausreichen sollte. Ja, käme es darauf an (um bei eurer Analogie zu bleiben), auch noch die Farbe des Briefpapiers oder die der Tinte zu bestimmen, wäre der Brief als Container alles dessen tatsächlich unverzichtbar. Aber genau darauf beziehen sich ja weder die betroffenen Fktt noch irgend­eine andere xlStandardFkt (ZELLE.ZUORDNEN ist ja kein Standard und auch arg eingeschränkt in ihrer Nutzbarkeit)!
Außerdem ist zwangsweise tatsächlich das angemessene Prädikat, den eigentlich müsste das nicht sein; es ist einzig und allein der engen Sicht ihres Pgmierers auf die Aufgaben dieser Fktt und den weit verbreiteten „Hang“ zu möglichst eindeutiger Datentyp­Fest­le­gung (Zitat: Variant ist böse!) geschuldet.
Was für eine Datenbank zweck­mäßig ist, muss aber für eine in einer ZellFml eines Kalku­lations­blattes einsetz­bare Fkt noch lange nicht sinn­voll sein! Deshalb verwenden von mir pgmierte UDFs den Datentyp Range (außer für simple Tests) idR nur dann, wenn ihre Aufgaben ohnehin das ganze Objekt (quasi den Container) erfordern. Und selbstver­ständlich ist ihr Ergebnis idR auch Variant, sonst könnten sie ja keinen von #WERT! verschiedenen Fehlerwert zurückgeben.
Auch das wird von vielen (Hobby-)Pgmierern leider kaum bedacht!
Gruß, Luc :-?


  

Betrifft: AW: Aggregat-Funktion in Kombi mit Zählenwenns/Verweis von: Tanja
Geschrieben am: 23.08.2014 14:54:44

Ja, Luc, du hast wahrscheinlich Recht und eine Beispieldatei vereinfacht es. Die Datei ist so aufgebaut wie meine aktuelle, nur sind unnötige Inhalte gelöscht. Die Tabelle ist auch um einiges länger im Normalfall.

Nochmal das Problem erklärt:
Ich möchte die Gesamtzeit pro Schicht / Tag errechnen und die Anzahl pro Schicht / Tag zählen. In Tabellenblatt 1 meine bisherige Rechnung, die sich jedoch nicht auf einen Filter anwenden lässt. Die Ergebnisse können in beiden Tabellenblätter angezeigt werden, ich brauche sie später in Tabellenblatt2, aber das kann ich auch noch selbst übertragen.

Ich möchte dass sich die Ergebnisse je nach gesetztem Filter direkt anpassen. Ein Makro müsste man wieder neu starten, daher sehe ich dies eher als schlechte Lösung an.

Habt ihr hierzu eine Idee? Muss auch nicht die Aggregat-Funktion sein. Mit Teilergebnis hatte ich es auch schon versucht, aber nicht wirklich hinbekommen, da die Tabelle variabel lang sein kann. Weiterhin könnte es zu einem Problem werden, dass je nach gesetztem Filter die Tabelle dann nicht immer zwangsläufig in Zeile 10 beginnt...

Danke schonmal für eure bisherige Hilfe.

@Luc: Dein Text vom 21.08. 17:51:20 war für mich leider nicht direkt verständlich. Vielleicht verstehe ich ihn wenn ich mich nochmal stärker einlese.

Hier der Link:
https://www.herber.de/bbs/user/92250.xlsx


  

Betrifft: Ja, AGGREGAT wäre eine Chance, aber ... von: Luc:-?
Geschrieben am: 24.08.2014 21:59:58

…wohl leider keine wirklich gute, Tanja,
denn als 3.Argument kann man zwar auch ein Datenfeld (als Ergebnis eines Ausdrucks, in der Art wie du es versucht hast) angeben, aber ob das dann mit Arg2 für Ignorieren ausgeblendeter Zeilen zusammenpasst, ist fraglich. Außerdem scheint das dann auch mit Summierung nicht zu fktionieren (ggf nur mit Quantilen).
TEILERGEBNIS hingegen kann zwar (im Ggsatz zu bspw ZÄHLENWENNS) auch mit unzusammen­hängenden Bereichen umgehen, aber eben nicht mit Daten­feldern. Nun könnte man zwar trickreich auch einen unzusammen­hängenden Bereich könstruieren, aber das ginge wohl nur mit INDIREKT, was aber damit nichts anfangen kann. INDEX könnte man ggf auch dazu „überreden“, aber das kann solche Bereiche eher bearbeiten als erzeugen — für die Erzeugung müssen immer entsprd viele Zellen ausgewählt wdn, was auf eine MatrixFml und Hilfs­zellen hinaus­laufen würde → ungünstigsten­falls soviele Hilfsspalten wie zu vglnde Zellen verwendet wdn.
Es würde also auf eine VBA-Lösung hinaus­laufen, was ich im Bild mal mit einer (meiner alten, nicht für die Veröffent­lichung geeigneten) UDF demon­striert habe:

Zur Abb: Die UDF ChooseIn wählt direkt die Zellen aus dem Bereich in ihrem Arg1 aus, die mit dem Ergebnis­Datenfeld des Vgls von Arg2 mit Arg3 korrespondieren (Arg4=0 bedeutet Prüfung auf Gleichheit). Auf diese Weise entsteht ein unzusammen­hängender Bereich, auch Mehr­fach­Auswahl genannt.
Allerdings stellt sich hier die Frage, ob das alles überhpt notwendig ist, denn Spalte H zeigt das gleiche Ergebnis wie Spalte L. Die in der BspDatei auf Tabelle1 gezeigten Filter­möglich­keiten haben jeden­falls keinen Einfluss auf die Ergebnisse in H. Alles, was aus den ange­deu­teten weiteren Filte­rungen resul­tieren könnte, kann ich natürlich nicht beurteilen, wundere mich aber darüber, wie­viele vor­gesehen sind. Die Komplexität der dahinter­stehenden Gedanken­gänge kann ich ja eben­falls nicht nach­voll­ziehen.
Mit Tabelle2 habe ich mich nicht weiter befasst, da hier auch Verweise auf im Bsp nicht enthaltene Blätter vorkommen.
Im Übrigen empfehle ich, keine ganzen Spalten in Fmln zu verwenden, weil das der Performance der Berechnungen abträglich sein kann.
Gruß, Luc :-?


  

Betrifft: AW: Aggregat-Funktion in Kombi mit Zählenwenns/Verweis von: Daniel
Geschrieben am: 25.08.2014 11:22:48

Ich bin immer noch der Ansicht, dass du das Problem relativ einfach lösen kannst, indem du in der Quelltabelle (also in der Tabelle, die du auswerten willst) eine Hilfsspalte mit der Formel: =Teilergebnis(3;A2) hinzufügst (A2 ist ein Bezug auf eine Zelle mit Inhalt in der selben Zeile wie die Formel steht.
Diese Formel hat als ergebnnis eine 1, wenn die Zeile eingeblendet ist und 0, wenn sie ausgeblendet ist.
Diese Hilfsspalte kannst du dann in deine Formeln einbauen, um den Filterzustand zu berücksichtigen:
=ZÄHLENWENNS(C:C;GANZZAHL(A2)I:I;E2;X:X;1)
(Spalte X sei die Hilfsspalte mit der Teilergebnisformel)

Gruß Daniel


  

Betrifft: AW: Aggregat-Funktion in Kombi mit Zählenwenns/Verweis von: Tanja
Geschrieben am: 26.08.2014 10:50:05

Hallo Daniel!

Erstmal entschuldige, dass ich dachte deine Idee funktioniert nicht. Ich weiß auch nicht was meine Gedankengänge da veranstaltet haben. Als ich es heute gelesen habe, ist mir direkt eingeleuchtet, dass es gehen muss. Und nach ein paar Minuten testen ob wirklich alle Änderungen berücksichtigt werden, kann ich sagen es funktioniert super!

Der Tag hat schlecht angefangen, aber du hast ihn mir mit dieser Lösung gerettet ;) Vielen vielen Dank!

@Luc: Vielen Dank auch für deine Mithilfe.

Liebe Grüße

Tanja

-------------------------------------------
Lösung gefunden - Thema abgeschlossen :)


  

Betrifft: Ja, das ist wohl die einzige Möglichkeit, die ... von: Luc:-?
Geschrieben am: 26.08.2014 13:28:02

…Anzahl der erforderlichen Hilfsspalten auf eine zu reduzieren, Daniel!
Ganz ohne Hilfsspalten wird es aber keine „orthodoxe“ Lösung (also ohne VBA) geben, Tanja,
da MS hier anscheinend auch mit AGGREGAT nicht die notwendigen Voraussetzungen geschaffen hat, obwohl es möglich gewesen wäre…
Gruß, Luc :-?


  

Betrifft: Das könnte dann bspw so aussehen, ... von: Luc:-?
Geschrieben am: 26.08.2014 16:30:56

…Tanja,
(falls du Umsetzungsprobleme hast und) mal auf eine ergebnis­wirksame fiktive Filterung (SpalteA=WAHR) eingestellt:



Gruß, Luc :-?

Besser informiert mit …