Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 16:50:59
2 Zellen in 2 Blättern mit unterschiedlichen Bereichen vergl
25.04.2024 15:03:35
Unterschiedliche Dateien miteinander abgleichen lassen
25.04.2024 14:05:48
Makro in aktueller Zelle ausführen mit Doppelklick
25.04.2024 12:56:25
Werte von Kunden und Artikel stehen untereinander
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1584to1588
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1584to1588
1584to1588
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Matrixformel mit zwei Bedingungen

Matrixformel mit zwei Bedingungen
22.10.2017 18:46:37
Frank
Hallo Excel-Spezialisten,
nachdem mein vorheriger Post zeitnah beantwortet wurde (auch wenn ich noch ein wenig basteln muss, bis ich die relativen Bezüge für meine Formeln ermittelt und dynamisiert habe...), habe ich da gleich noch ein Problem in diesem Arbeitsblatt.
Ich möchte in E72 und E73 den Minimum- und Maximumwert der Spalte H ermitteln, allerdings nur wenn in Spalte G der Eintrag "out" enthalten ist. Als Krönung kommt dazu, dass in H teilweise "#NV" steht.
Ich habe eine Matrix-Formel gefunden, die das #NV erfolgreich ignoriert, so dass ich auch ein Ergebnis erhalte:
"MIN(WENN(ISTNV(H2:H64)=FALSCH;H2:H64))"
"MAX(WENN(ISTNV(H2:H64)=FALSCH;H2:H64))".
Hat mich natürlich gefreut, dass dies so auf Anhieb funktioniert hat, leider berücksichtigen sie aber noch alle Werte, weil die notwendige Prüfung auf "out" in Spalte H fehlt. Kann mir jemand bitte sagen, welche Syntax notwendig ist, um dies zu integrieren?
Wenn das nicht funktioniert oder zu kompliziert ist, muss ich vielleicht an der Quelle ansetzen. Das #NV resultiert aus Spalte C, in der Formeln wie diese stehen:
SVERWEIS(A53;'JLL #2'!A:N;6;FALSCH)
Wenn dabei kein Datums-Match mit der referenzierten Tabelle möglich ist, entsteht eben #NV, das in G und H, die sich u.a. darauf beziehen, mitgeschleppt wird.
Danke für Eure Vorschläge.
Viele Grüße
Frank

41
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: mit AGGREGAT() ...
22.10.2017 18:59:40
...
Hallo Frank,
... in einer Matrixfunktion(alität)sformel die keines spez. Formelabschluss wie eine klassische Matrixformel benötigt.
Für das MIN so M=AGGREGAT(15;6;H2:H64/(G2:G64="out");1)
und für das MAX so: =AGGREGAT(14;6;H2:H64/(G2:G64="out");1)
Gruß Werner
.. , - ...
AW: mit AGGREGAT() ...
22.10.2017 20:59:32
Frank
Hallo Werner,
danke für den Lösungsvorschlag.
Ich habe die beiden Formeln direkt mal eingetragen, ehe ich mir näher ansehe, was die Funktion und ihre Parameter leisten. Ich bekomme aber jeweils #ZAHL! zurückgeliefert.
Das "M" vor der ersten war sicher nur ein Schreibfehler, denke ich. Was kann ich falsch gemacht haben?
Stört sich die Formel nicht an den "#NV", oder sorgt einer der Parameter dafür, dass sie ignoriert werden?
Und wenn sie - nach Beseitigung des momentanen Fehlers - laufen, ist der MEDIAN dann auch abfragbar, oder nur MIN und MAX?
Danke!
Viele Grüße
Frank
Anzeige
AW: ein Vorteil von AGGREGAT() ist, ...
23.10.2017 13:24:37
AGGREGAT()
Hallo Frank,
... das über die 6 als zweite Argument von AGGREGAT() der Funktion mitgeteilt werden kann, dass sie Fehlerwerte (egal welcher Fehlerart auch immer) in den auszuwertenden (egal ob nur direkt oder erst ermittelten) Daten ignorieren soll und deswegen keine andere Excelfunktionalität bemüht werden muss. Und das auch, ohne den spez. Matrixformelabschluss vornehmen zu müssen.
Ja, das "M" vor meiner ersten Formel war ein Schreibfehler.
Wenn Du als Ergebnis in Deinem Beispiel mit der AGGREGAT()-Formel den Fehlerwert #ZAHL! erhältst, dann nur dann, wenn Deine "out" in Spalte G immer nur einen #NV und/oder keinem Zahlenwert in der Spalte H zugeordnet sind oder in Spalte G kein "out" vorhanden ist (was ja aber nicht der Fall sein dürfte) Wie auch immer, das ist in mE dann auch richtig. Dieses Ergebnis kann ja mit WENNFEHLER() neutralisiert werden. Die Formel {=MIN(WENN(ISTTEXT(G2:G64);WENN(G2:G64="out";WENN(ISTZAHL(H2:H64);H2:H64))))} ergibt in so einem Fall ein Ergebniswert 0, was aber mE nicht richtig ist.
Mit der Funktion 12 als erstes Argument von AGGREGAT() kann auch ein MEDIAN von Datenwerten ermittelt werden, allerdings dann (leider) nicht in der Matrixversion, wie die dann hier wegen der zusätzlichen Bedingungsabfrage auf "out" in Spalte G notwendig würde.
Gruß Werner
.. , - ...
Anzeige
AW: ein Vorteil von AGGREGAT() ist, ...
23.10.2017 18:47:27
AGGREGAT()
Hallo Werner,
ich habe nun zwar inzwischen an der Quelle, also Spalte C, in der SVERWEIS-Formel durch "WENNFEHLER" die "#NV" ausgeschlossen, ebenso in der Folgespalte H, aber trotzdem habe ich die Aggregatfunktion noch mal probiert.
Mit der Erklärung bzgl. #Zahl! hast Du recht. WENNFEHLER(AGGREGAT(15;6;H2:H64/(G2:G64="out");1);"--") liefert ein Ergebnis, sobald mind. ein "out" in G steht. Die Beispieltabelle hatte tatsächlich keine. Das ist - was Du natürlich nicht wissen kannst - eigentlich sogar der gewünschte Zustand. Je mehr "in" desto besser. Hier war es zufällig ein perfekter Kandidat (für die spätere Nutzung, für den Test eher weniger...). Ich habe daher die Formel in der einen oder anderen Zelle in G überschrieben und "out" eingetragen, dann kam sofort ein Ergebnis für das Minimum heraus.
Ich habe die Formel dann in die beiden nebenliegenden Spalten für Median und Max übertragen.
WENNFEHLER(AGGREGAT(12;6;H2:H64/(G2:G64="out");1);"--")
WENNFEHLER(AGGREGAT(4;6;H2:H64/(G2:G64="out");1);"--")
Sie geht bei Median tatsächlich nicht. Der Grund, warum es bei Min geht, hier aber nicht, erschließt sich mir noch nicht. Zudem wird auch für Max kein Ergebnis ermittelt, wärend die vorherige Matrix-Formel ja für alle drei Zellen funktionierte. 1:3 ist eine schlechte Quote für das Aggregat... :-)
Vielleicht bin ich ja blind, aber in der Hilfe unter https://support.office.com/de-de/article/AGGREGAT-Funktion-43b9278e-6aa7-4f17-92b6-e19993fa26df finde ich keine Erklärung für das "/", das Du verwendet hast. Auch der letzte Parameter, den Du mit "1" angegeben hast, ist dort nur einmalig mit "[k]" angegeben, aber nicht weiter ausgeführt.
Viele Grüße
Frank
Anzeige
AW: dazu folgendes ...
23.10.2017 19:39:41
...
Hallo Frank,
... die MSO-Hilfe ist zu der AGGREGAT()-Funktion wirklich etwas arg zu knapp und insbesondere da zu der Matrixversion. Dort gibt es zum letzteren lediglich folgende Ausführung:
Für Funktionen, die mit einer Matrix arbeiten, ist Bezug1 eine Matrix, eine Matrixformel oder ein Bezug auf einen Zellbereich, für die/den Sie den Aggregatwert berechnen möchten: Funktion
KGRÖSSTE(Matrix,k)
KKLEINSTE(Matrix,k)
QUANTIL.INKL(Matrix,k)
QUARTILE.INKL(Matrix,Quartil)
QUANTIL.EXKL(Matrix,k)
QUARTILE.EXKL(Matrix,Quartil)

Warum die anderen Teilfunktionen (1-13) nicht in der Matrixversion bereitgestellt werden, das ist ein "Geheimnis" von MS (es gibt noch weitere zu der Funktion).
Du hast keinen MAXWert ermitteln können, weil die von Dir verwendete Funktion 4 eben nicht Matrixversions tauglich ist. Anstelle der 4 musst Du wie von mir auch angeben, die 14 nutzen (analog zu MIN/KKLEINSTE, wo ich auch anstelle der 5 die 15 genutzt habe). Die 1 steht somit für den 1. Größten /Kleinsten, welches gleich bedeutend ist mit MAX/MIN.
Die Verwendung von / also der Division der Bedingungsgleichungen in der Formel führt je nachdem intern zur Division durch 1 oder durch 0. Bei letzteren ergibt sich natürlich intern ein Fehler, den die Funktion AGGREGAT() durch die Verwendung der 6 als 2. Argument "herausfiltert". Das genau so, wie es eben explizite Fehlerwerte in auszuwertenden Zellwerten herausfiltert.
Das ist der ganze "Zauber" der Funktion, was diese aber unwahrscheinlich mächtig macht.
Gruß Werner
.. , - ...
Anzeige
statt 5=MIN musst Du 15=KKLEINSTE(…;1) nehmen
23.10.2017 20:01:46
WF
Das kann man niemanden verklickern.
Deshalb war diese Funktion von Anfang an für mich tot (außer als Teilergebnis-Ersatz).
WF
AggregateXk fktt mit allen FktsNrn, ...
23.10.2017 20:08:04
Luc:-?
…Walter,
und auch noch mit 0 und negativen. Aber Du hast ja was gg VBA und damit auch UDFs… ;-]
Gruß, Luc :-?
AW: ich muss nur was ich für richtig halte und ...
24.10.2017 10:03:37
...
Hallo WF,
... "verklickert" habe ich diese Formelart nach meinen Erfahrungen vor allem an weniger erfahrene Exceluser einfacher und schneller, als wenn ich klassischen Matrixformeln angeboten hatte oder anbieten musste (weil noch ältere Excelversionen im Einsatz waren).
Und waren es vor reichlich zwei Jahren noch weniger als ein Handvoll potentieller Helfer in deutschsprachigen Foren die AGGREGAT() - Formeln angeboten haben, so sind es heute schon Dutzende die dies tun. Schau Dich doch nur mal in den Foren etwas mehr um.
Keiner verlangt von Dir, dass Du Dich dem auch anschließen musst, aber unterlasse doch bitte Deine des öfteren weniger sachlichen Argumentationen vor allem meinen entsprechenden Beiträgen gegenüber. Du wirst damit den Trend auch nicht aufhalten aber schadest damit mE mehr Dir und Deiner bisherigen Leistung um und für den Einsatz von Excelformeln.
Abwiegeln werde ich keine sachlich berechtigte Kritik. Gerade aber zur Funktion AGGREGAT() war ich möglicherweise sogar der oder mit der Erste (zumindest in deutschsprachigen Userforen), der auch die Unzulänglichkeiten wie Ungereimtheiten der Funktion aufgezeigt habe. Nichts desto trotz bin und bleibe ich ein Befürworter des Einsatzes gerade auch dieser Funktion.
Gruß Werner
.. , - ...
Anzeige
Eine Lanze für WF ...
25.10.2017 04:36:59
lupo1
... breche ich jedoch bezüglich der Beschränktheit des Einsatzes von AGGREGAT. Meistens stürzt Excel (2010) nämlich bei mehr als 100.000 Anwendungen von AGGREGAT ab. Oft auch schon vorher. Also kann man AGGREGAT nur auf knapp 1/10 ALLER möglichen Datensätze EINMAL einsetzen.
{}-Matrixformeln hatten bis xl2003 eine ähnliche Anwendungsbeschränkung, siehe
http://www.xlam.de/xlimits/arrays.htm
Es gilt, wie schon immer, auch weiterhin: Nicht zu viel auf einmal mit einer einzigen Formel erledigen, da dies mit exponentiellen Laufzeiten bestraft wird! Insbesondere ist es geradezu "kaputt", auf vorherige Sortierungen zu verzichten. Egal, ob bei Originaldaten oder mit Hilfsdaten. Jeder Alt-EDV'ler tippt sich dann mit dem Finger an die Stirn: "Wo bleibt der Fortschritt, wenn ein derartiger Rückschritt in den Köpfen passiert?"
Anzeige
AW: und Dir gegenüber wiederhole ich, ...
25.10.2017 09:14:48
...
Hallo Lupo,
... was ich Dir mindestens auch einmal geschrieben habe. Ich hab nie ein AGGREGAT()-Formel angewendet noch empfohlen, wo diese derartig häufig angewendet werden müsste. Das würde ich auch nicht für jede andere Matrixfunktion(alität)sformel und auch nicht für keine klassische Matrixformel tun.
Dafür sind derartige Formellösungen mE grundsätzlich nicht ausgelegt.
Gruß Werner
.. , - ...
Dem kann ich nur sehr bedingt zustimmen, ...
25.10.2017 19:10:27
Luc:-?
…Lupo,
denn das wird ja wohl hauptsächlich ein Hardware- und Storage-Problem sein. Je größer die virtuell bewegten Daten­mengen wdn, desto größer wird auch der interne Speicher­Bedarf, der dann leicht die Stan­dard­Ein­stel­lungen über­schreiten kann. Wer also Xl für Massen-DV (miss-)brauchen will, muss dem Rech­nung tragen. Das dürfte aber über­wiegend nur bei großen Firmen und Orga­ni­sa­tionen der Fall sein, die idR auch über DBen ver­fügen. Deren Daten kann man auch vor­aggre­giert impor­tieren und somit Xl so benutzen, wie es ursprüng­lich auch vor­gesehen war → für quali­tative Kalku­lationen und Ana­lysen! Die riesige Erwei­terung ab Xl12/2007 kam ja wohl nur auf Kunden­wunsch und aus Kon­kur­renz­Gründen zustande (vgl dazu unten) und hätte gar nicht so über­dimen­sioniert aus­fallen müssen; dürfte eh nur von einer Minder­heit extensiv genutzt wdn. Damit relativiert sich Dein Hpt­Argu­ment doch sehr und meine (und eigent­lich auch Walters) Begrün­dung rückt in den Vor­der­grund.
Massen-DV fällt bei GroßFirmen und -Organisationen regelmäßig an. Dabei wdn DBen genutzt. Daraus erzeu­gen bspw die KVen regel­mäßig rie­sige detail­lierte Listen, die auch so in den Druck gehen. Somit kann jedes Mit­glied sich dort mit seiner Leistung und Ver­gütung wieder­finden. Für Zusammen­fassungen und Ana­lysen wird Xl genutzt, wobei die Daten vor­aggre­giert aus der DB kom­men. In einer Zeit als die Statistiker keinen DB-Zugang hatten, muss­ten sie dort mit deut­lich größeren Daten­mengen umgehen, was damit auch in vglbaren Fällen eher ein Problem der betrieb­lichen Organi­sation nebst Quali­fizie­rung wäre.
Denen, die aber unbedingt auch Massen-DV wg DetailAuswertungen betreiben müssen, sollten andere Mittel und Methoden zV stehen wie bspw QlikView (mit in-memory-Technologie) von QlikTech oder auf dem UN-ECE-Projekt METIS fußende meta­daten-basierte DB-Systeme mit relativ einfach erstell­baren quali­fi­zier­ten Auswertungs­möglich­keiten.
Man sollte also schon abundzu mal über den Rand des selbstgewählten hiesigen „Suppen­tellers“ hin­aus­schauen (und nicht wie GMC im OL-Forum mit MS-Scheu­klappen durch die viel­gestal­tige Soft­ware-Land­scape tappen…)! ;-]
Gruß, Luc :-?
Anzeige
KORR: Meinte natürlich nicht GMC, ...
25.10.2017 19:52:57
Luc:-?
…sondern noch G-C zwischen M und C! ;-]
Übrigens, DESTATIS hatte sich als eines nur weniger europäischer Ämter nicht am METIS-Projekt beteiligt, das letztlich vor Jahr­zehnten mal in Österreich als ISIS begann…
Luc :-?
AGGREGAT ist keine EDV ...
26.10.2017 14:24:49
lupo1
... sondern für EDV sortiert man. Oder indiziert man. Oder pivotiert man.
Kein großes Unternehmen wird mit AGGREGAT arbeiten. Und wenn doch, dann nur, weil es nicht weiß, was es tut.
Große Unternehmen arbeiten ggf nicht mal m.MSO!
26.10.2017 20:57:16
Luc:-?
Gruß, Luc :-?
Das ist aber extrem GGFLLS ...
27.10.2017 11:41:11
lupo1
... bei einer zeitweiligen Verbreitung von 90% MSO.
Nicht zu vergessen: Sogar professionelle Lösungen basieren manchmal auf VB, VBA oder gar Excel.
Anzeige
Es gibt halt sehr viel mehr kleine Firmen! ;-] owT
27.10.2017 12:01:39
Luc:-?
:-?
...was auch ein Grund für langandauernde ...
27.10.2017 12:12:44
Luc:-?
…relative Stagnation gewesen sein könnte, Lupo;
erst, wenn (viele) Großkunden ab(zu)springen (droh[t]en) tu[/a]t sich Wesentliches bei MS…
Gruß, Luc :-?
Ein Problem bei einer solchen Betrachtung ...
27.10.2017 12:56:11
lupo1
... bleibt immer, dass man sich bei 90% eben fast nur verschlechtern kann.
Und 90% ist schon ein unglaublicher Maßstab. Weder hat je ein Federer 90% aller Endspiele seiner Zeit bestritten, noch ein Hamilton bei über 90% aller Rennen auf dem Treppchen gestanden (letzteres aber recht knapp verfehlt mit 17/22).
Von daher sollte man MSO auch noch bei 50% Verbreitung einen gewissen Erfolg nicht absprechen dürfen. Auch wenn es bis dahin fast die Hälfte des maximalen Marktanteils verloren hat.
Kennst Du namentlich Leute, die Experten in OpenO, LibreO, GDocs oder PlanM sind?
Anzeige
Es gibt noch mehr, aber ich hatte auf etwas ...
27.10.2017 16:21:29
Luc:-?
…Anderes verlinkt, Lupo,
im von dort aus erreichbaren Forum findest Du bestimmt ein paar Namen und auf der HptSeite sicher auch Kunden. Ansonsten ist ja bekannt, dass der ÖD vielerorts auf LO/OO umgestiegen ist, was auch unter Linux läuft, wobei es dort noch mehr gibt. Das GgBsp München wäre dabei untypisch, denn das liegt dort allein am neuen OB, der Mio Investitionen für ein passgenaues Limux (auf Linux-Basis) in den Wind geschossen hat, nur um eine MS-Zentrale nach München zu bekommen. Verschwendung von Steuer­geldern, nah an Korruption!
Luc :-?
Kapitalismus ...
27.10.2017 13:24:40
lupo1
... bedeutet ja auch, auf zusätzliche Gewinne verzichten zu können. Wenn die bisherigen ausreichen. Oder wenn die zusätzlichen nicht gesichert erscheinen.
Eben, und die Konkurrenz hat nicht solange ...
27.10.2017 16:25:23
Luc:-?
…geschlafen, sondern von Anfang an etwas Zeitgemäßes geschaffen (und damit meine ich nicht LO/OOcalc!). Apple hätte so etwas vor ca 15 Jahren beinahe das Genick gebrochen… (Auch ein großer Vorlauf kann schnell verschwinden!)
Luc :-?
Es sind Entscheidungen/Nichtentscheidungen ...
28.10.2017 10:26:37
lupo1
... von Marktteilnehmern, die dafür eigenverantwortlich sind.
Moralisch ist dem - bei völliger Handlungsfreiheit (auch, falls unmoralisch), also idealerweise ohne Korruption - nichts hinzuzufügen, denn die Wirtschaftssubjekte befinden mit jeder Entscheidung/Nichtentscheidung über ihr Fortbestehen.
Ein Professor sagte mal in der Vorlesung: "Komisch, dass Unternehmensberater immer nur dann geholt werden, wenn was schief läuft. Es wäre viel wichtiger, sie schon zu holen, um zu wissen, WARUM es gut läuft!" Denn bei dem WARUM kann man einer schweren Fehleinschätzung unterliegen. (Statt Unternehmensberater als Platzhalter kann auch einfach "Analyse" stehen)
Strecke einen Finger in das Terrarium einer Giftschlange. Sie beißt nicht zu.
- Die falsche Folgerung: Sie ist ungefährlich.
- Die vorsichtige Folgerung: Sie wollte gerade nicht beißen; sie ist trotzdem unberechenbar.
- Die richtige Folgerung: Es gibt nur die vorsichtige Folgerung.
Beim LiMux gebe ich Dir soweit recht. Aber nur aus dem Grund, weil im öD eh so langsam gearbeitet wird, dass Produktivitätsvorteile von MSO ggü Linux nicht messbar sind ;)
Was gibt es denn außer OO, LO, PM und GD noch so Bedeutendes?
Folge meinem ganz obigem Link...! owT
28.10.2017 15:50:29
Luc:-?
:-?
AW: richtig, diese Möglichkeit besteht ...
24.10.2017 10:05:28
...
Hallo Luc,
... dann auch in der Matrixversion der Funktion und zwar analog zum Einsatz der Funktionen 14 und 15 für die Funktionen 4 und 5. Leider gibt es ähnliches für die anderen Funktionen wie z.B. für die SUMME so (in der von MS angebotenen Standardfunktion) nicht.
Gruß Werner
.. , - ...
AW: MEDIAN kann durch Quan- bzw Quartile ...
24.10.2017 21:17:45
Frank
Hallo Luc,
oh mein Gott, die tiefgründige Diskussion mit Werner zeigt mir (leider) wie weit weg ich in einigen Bereichen noch von den Möglichkeiten, die Excel bietet, bin, obwohl ich seit vielen Jahren damit arbeite und auch schon etliche größere Tabellen auf die Beine gestellt habe.
Matrix-Formeln gehörten bisher nicht zu meinen Lieblingsthemen muss ich sagen und Dein Ansatz mit eigenen Funktionen fehlende Möglichkeiten von Excel nachzurüsten, ist durchaus beeindruckend. Mal sehen, ob ich es anhand der Beschreibung im Code hinbekomme, sie zu nutzen. Vielleicht kannst Du mir ja helfen, indem Du den Aufruf für zwei, drei Zellen, deren Formeln ich bisher nutze, angibst. Dann kann ich mich von dort weiterarbeiten:
MIN(WENN(ISTTEXT(G1:G63);WENN(G1:G63="out";WENN(ISTZAHL(H1:H63);H1:H63))))
MEDIAN(WENN(ISTTEXT(G1:G63);WENN(G1:G63="out";WENN(ISTZAHL(H1:H63);H1:H63))))
MAX(WENN(ISTTEXT(G1:G63);WENN(G1:G63="out";WENN(ISTZAHL(H1:H63);H1:H63))))
Viele Grüße
Frank
Dir geht's um die UDF AggregateXk, ...
25.10.2017 04:46:05
Luc:-?
…Frank?
Das wäre wahrscheinlich möglich, sowohl als ggf schnellere MatrixFml als auch NormalFml (bei negativem 1.Argument → interne Auswertung). Man könnte aber auch das Original benutzen, dann aber nur mit Fktt ab Nr 14. Ich werde mir das mal unter UDF-Aspekt anschauen…
Übrigens, die UDF ist auch ein Produkt einer Diskussion mit neopa. Hiermit wollte ich ihm zeigen, dass eine einheitliche Lösung für alle integrierten Fktt durchaus möglich gewesen wäre. Allerdings habe ich mich dabei auf nur ein 3. und ggf spezielles 4.Argument beschränkt (deshalb …eXk), weil es eben in 1.Linie eine Demo sein sollte.
Morrn, Luc :-?
Habe deine 3 Fmln mal zur Grundlage von ...
25.10.2017 18:10:33
...
…weiteren 3 Varianten gemacht, Frank,
insgesamt also folgende 12 Fmln → 6 singulare Matrix-, 6 normale Fmln, und anders eingeteilt zusätzlich zu deinen 3 6 Fmln mit der UDF und 3 mit AGGREGAT bei verändertem 3.Argument. Alle Varianten einer der 3 AusgangsFmln haben stets dasselbe Ergebnis:
{=MIN(WENN(ISTTEXT(G1:G63);WENN(G1:G63="out";WENN(ISTZAHL(H1:H63);H1:H63))))}
{=MEDIAN(WENN(ISTTEXT(G1:G63);WENN(G1:G63="out";WENN(ISTZAHL(H1:H63);H1:H63))))}
{=MAX(WENN(ISTTEXT(G1:G63);WENN(G1:G63="out";WENN(ISTZAHL(H1:H63);H1:H63))))}
{=AggregateXk(5;4;WENN(ISTTEXT(G1:G63);WENN(G1:G63="out";WENN(ISTZAHL(H1:H63);H1:H63))))}
{=AggregateXk(12;4;WENN(ISTTEXT(G1:G63);WENN(G1:G63="out";WENN(ISTZAHL(H1:H63);H1:H63))))}
{=AggregateXk(4;4;WENN(ISTTEXT(G1:G63);WENN(G1:G63="out";WENN(ISTZAHL(H1:H63);H1:H63))))}
=AggregateXk(-5;4;WENN(ISTTEXT(G1:G63);WENN(G1:G63="out";WENN(ISTZAHL(H1:H63);H1:H63))))
=AggregateXk(-12;4;WENN(ISTTEXT(G1:G63);WENN(G1:G63="out";WENN(ISTZAHL(H1:H63);H1:H63))))
=AggregateXk(-4;4;WENN(ISTTEXT(G1:G63);WENN(G1:G63="out";WENN(ISTZAHL(H1:H63);H1:H63))))
=AGGREGAT(15;6;H1:H63/(ISTTEXT(G1:G63)*(G1:G63="out")*ISTZAHL(H1:H63));1)
=AGGREGAT(16;6;H1:H63/(ISTTEXT(G1:G63)*(G1:G63="out")*ISTZAHL(H1:H63));0,5)
=AGGREGAT(14;6;H1:H63/(ISTTEXT(G1:G63)*(G1:G63="out")*ISTZAHL(H1:H63));1)
Die AggregateXk-Fmln würden auch mit den Argumenten der AGGREGAT-Fmln (ab dem 2.) analog arbeiten, aber je nach positivem bzw negativem 1.Argument (wie oben ±5/12/4) als singulare Matrix- bzw normale Fml.
Gruß, Luc :-?
AW: Habe deine 3 Fmln mal zur Grundlage von ...
25.10.2017 21:15:19
...
Hallo Luc,
ich habe die ersten drei Formeln von AggregateXk eingesetzt und in der Tat bringen sie das gleiche Ergebnis wie die bisher genutzten. Anhand der Erklärungen in der Funktion kann ich aber nicht wirklich nachvollziehen, was Du da tust, zumal Du in den einleitenden Kommentaren auch noch zahlreiche Abkürzungen nutzt, die das Lesen doch etwas erschweren. Es stellt sich drei Fragen:
- Wann sollte / kann ich diese Funktion nutzen und wann nicht?
- Wann und warum sollte ich welche der drei Gruppen von Formeln verwenden?
- Welche Vor- und welche Nachteile gehen mit ihnen einher?
Hast Du eine Ressource, wo ich das Thema insgesamt mal auf einfache Art und Weise erschließen kann? Ich komme da einfach noch nicht hinter. Ich weiß was ich will - Funktionen wie MIN, MAX etc. auf einen Zellbereich anwenden und dabei mehrere Kriterien vorgeben. Was Excel dann im Hintergrund mit den Daten tut und warum und wann man deshalb eine bestimmte Formel (nicht) nutzen sollte, ist mir unklar. Vielleicht gibt es da ja eine Seite, wo ich mich einlesen kann, die Du empfehlen kannst.
Viele Grüße
Frank
Zu deinen Fragen, ...
26.10.2017 04:08:37
Luc:-?
…Frank;
diese vorangestellten (mitunter zusätzlich auch welche im Pgm) Kurzerläuterungen dienen vorrangig mir als Gedächtnisstütze und als Nukleus einer ggf irgendwann mal zu schaffenden Hilfe (nur bei Aufnahme der UDF in ein AddIn, bei SubProzeduren könnte man das eher individualisieren), die dann auch AnwendungsBspp und Navigationslinks enthalten würde. Ein Hilfe-Kurztext könnte hier dann so aussehen:
AggregateXk: Aggregat nur für ein Datenfeld als Ergebnis eines Ausdrucks (eXpression)
als 3.Argument (Arg3) u. ggf eines solchen auch als Arg4 (k) für Arg1 (FktsNr) > 13; dabei Umwandlung auch einer Bereichsangabe in ein Datenfeld, wodurch dann generell nur noch das Ignorieren von vorhandenen bzw durch Berechnung des 3.Arguments entstandenen Fehlerwerten möglich ist!
&bullet; Arg1: <0 → mit interner Evaluierung (nur 1× pro Formel möglich!), =0 → nur Arg3-Wiedergabe, sonst wie bei Xl-Fkt AGGREGAT
&bullet; Arg2: 0/1/4/5 → ohne Wirkung, 2/3/6/7 → Fehlerwert-Auslassung (→ 0|"") bzw F-Wert-Ersatz durch ""/1 bei Arg2=8/9
&bullet; Arg3: Zellbereichsbezug oder Ausdruck (Teilformel für Auswahl nach Bedingungen u.ä.) → Datenfeld
&bullet; Arg4: entfällt oder Klasse (k) für Funktionsnrn (Arg1) > 13
Achtung! Negatives Arg1 macht die Formel z.Z. ergebnis-instabil (bei Berechnungen auf anderen Blättern) und verhindert eine externe Formeltext-Evaluierung (XLM-Funktion AUSWERTEN in benannter Formel bzw UDF TxEval o.ä.)!
Ich hoffe mal, dass damit deine 1.Frage beantwortet wäre. Falls nicht, folgen hier 3 Links ins Herber-Archiv zu einem Teil meiner Diskussionen mit Werner (neopa) zu diesem Thema:
https://www.herber.de/forum/archiv/1468to1472/1469329_Zum_WE_Kleiner_Exkurs_ueber_ContainerFktt.html#1469329
https://www.herber.de/forum/archiv/1468to1472/1470499_sverweis_verschachtelt.html#1470713
https://www.herber.de/forum/archiv/1468to1472/1471659_Mittelwert__mehrere_Kriterien_in_einer_Spalte.html#1471759
Die UDF AggregateXk kannst du immer dann nutzen, wenn du unbedingt ein Aggregat so nutzen willst wie es neopa oft tut (also nicht für reine ZellBereichsAngaben als Arg3!), es aber für die Fktsnrn 1…13 benötigst und dir keine Alternative einfällt (bedenke dabei, dass das nur als Demo dieses Aspekts gedacht war und deshalb AGGREGAT nicht völlig ersetzen kann!). Falls du unbedingt auf die MatrixFmlForm verzichten willst, anderenfalls könntest du ja auch eine lt Walter (WF) durchschaubarere Fml verwenden, muss das 1.Argument negativ angegeben wdn. Der Ausdruck in Arg3 wird dann zwar 1× normal berechnet, was ohne Matrix­Fml­Form #WERT! als Ergebnis ergäbe, aber dann durch das Ergebnis seiner internen Evaluierung (als FmlText) ersetzt. Dadurch kann die UDF dann alle sich ergebenden Werte verwenden, ohne dass Xl dazu über die MatrixFmlForm angewiesen wdn muss (Xl ver­wendet sonst idR nur den 1.Wert). Allerdings ist das mit pgmtechnischen Klimmzügen verbunden, die das Ergebnis trotz Volatilität instabil machen, wenn auf ein anderes Blatt gewechselt wird und dort Berechnungen erfolgen (beim Rückwechsel muss [F9] gedrückt oder in eine beliebige Zelle geklickt wdn).
Wie gesagt, die Anwendung von AGGREGAT ist umstritten, vor allem, wenn es nur um die Vermeidung der MatrixFmlForm geht, die ja eigentlich ein sehr sinnvolles Instrument ist, mit dem Xl mitgeteilt wdn kann, wie es in einem solchen Fall verfahren soll. Das ist übrigens in anderer Calc-Software ganz ähnlich vor­gesehen, womit ein EntscheidungsProblem auf BlattEbene gelöst wird (Berech­nung u/o Ergebnis­Rückgabe; bei Fmln auf anderen Ebenen wdn stets alle Werte berechnet, die Rückgabe­Entscheidung fällt aber immer auf dem Blatt!). Zu der klassischen Wirkungsweise von AGGREGAT (quasi sein eigentlicher Anwendungszweck) gibt's einen Artikel von Beate Schmitz im Tutorial von PHs Online-Excel-WebSite (Link).
Natürlich haben die Fml-Cracks (wie neopa u.a.) nicht geruht und daraus ein, wenn auch anwendungs­eingeschränktes Universal-Hilfsmittel zur Vermeidung des vorgenannten „Xl-Informieren-Müssens“ gemacht, was in etlichen Fällen auch kürzere Fmln erlaubt, wobei oft die doch sehr zweifelhafte absichtliche Erzeugung von Fehlerwerten in Kauf genommen wird, was im Grunde genommen der Xl-„Philosophie“ widerspricht, die bei Auftreten von Fehlerwerten von inkonsistenten Daten ausgeht und deshalb in nahezu allen anderen Fktt in diesem Fall eine Ergebnis­Bildung verhindert (was natürlich die BedingtFormatierung stören kann — wohl das ursprüngliche HptMotiv für die Schaffung dieser Fkt).
Ich will mal hoffen, dass das erstmal ausreichend war. Ansonsten müsstest du mal selbst in INet und Xl-ForumsArchiven recher­chieren.
Achso, habe übrigens noch einen Link zur Vs1.3 von AggregateXk gefunden:
https://www.herber.de/forum/archiv/1468to1472/1471659_Mittelwert__mehrere_Kriterien_in_einer_Spalte.html#1472137
Viel Erfolg! Morrn, Luc :-?
Ergänzung
27.10.2017 00:23:43
Luc:-?
Nochwas, Frank;
für ab Xl12/2007 neue Xl-Fktt wdn ab dieser Xl-Version standardmäßig verborgene Namen angelegt; auch ein Zeichen dafür, dass sie nicht ganz so wie die alten Xl-Fktt arbeiten. Öffnest du eine Datei mit Fktt in einer Xl-Version, in der es sie noch nicht gab, wdn ihre verborgenen Namen angezeigt, also zB in Xl12 eine AGGREGAT-Fml (Xl14/2010) als _xlfn.AGGREGATE(…)*. Dazu findest du unter dem zuvor genannten Link nichts, weil Beate das offensichtlich nicht wusste (wie die meisten anderen Nutzer auch). Diese neue Technik könnte dann auch dafür mitverantwortlich sein, dass manche {}-lose „MatrixFormelFunktionalitäts“-Fmln (Autor: neopa) mög­lich wdn, die AggregateXk nur mit negativem Arg1 analog nachbilden kann.
Aber es gibt noch einen anderen Weg, die Xl-Fkt AGGREGAT dazu zu veranlassen, stets alle FktsNrn lt Arg1 erfolgreich anzu­wen­den, allerdings bei Vorliegen von AuswahlBedingg idR nur als (singulare) MatrixFml. Es ist dir ja nun bekannt, dass die Fktt 1…13 nur mit BereichsBezügen fktionieren. Wenig bekannt ist hingg (wohl auch Beate nicht), dass das auch diskontinuierliche sein dürfen (wie bei INDEX und etlichen anderen Xl-„Alt“-Fktt). Das kann man ausnutzen, indem man eine UDF verwendet, die solche Bezüge aus einem GesamtBezug erzeugen kann. Ein solcher Bereich kann aber idR nicht direkt auf einen Zell­Bereich abgebildet wdn, weil die ausfallenden Daten ja nicht durch anderes ersetzt wdn. Das kann uU mit INDEX gelingen, zumindest mit einzelnen 4.Argu­men­ten, nicht aber mit INDIREKT (kann, ebenso wie die neueren und neusten ~WENNs-Fktt, keine diskontinuierlichen Bereiche ver­ar­bei­ten!), was hier aber nicht erforderlich wäre. Um die dafür benötigte UDF NoErrRange rationell nutzen zu können, habe ich die AuswahlRegelFml etwas umgestellt, so dass sich folgde 3 Fmln ergeben:
{=AGGREGAT(5;3;NoErrRange(H1:H63;;ISTTEXT(G1:G63)*(G1:G63="out")*ISTZAHL(H1:H63)))}
{=AGGREGAT(12;3;NoErrRange(H1:H63;;ISTTEXT(G1:G63)*(G1:G63="out")*ISTZAHL(H1:H63)))}
{=AGGREGAT(4;3;NoErrRange(H1:H63;;ISTTEXT(G1:G63)*(G1:G63="out")*ISTZAHL(H1:H63)))}
Das hat außerdem den Vorteil, dass auch alle Arg2-Werte benutzt wdn können, wobei ausgeblendete Zeilen (und unter bestimm­ten Bedingg sogar Spalten → dann Volatilität per Arg2 erzeugen!) schon in der UDF (Arg2) berück­sichtigt wdn könnten.
Diese Varianten haben dann selbst­ver­ständ­lich ebenfalls das gleiche Ergebnis wie die bisherigen.
________
* Das kann man testen, indem man eine UDF benutzt, die alle Namen u.ggf ihre Inhalte in ihrer (alfa­betischen) Reihen­folge über eine/ihre lfdNr ausliest. Das kann zwar auch ein spe­ziel­les Xl-Tool machen, bin mir aber nicht sicher, ob es auch ver­borgene Namen (speziell diese!) abbil­det. Außer­dem sind meine diesbzgl UDFs kom­fortabler. Hier aber nur die Light-Version NCont. Deren Einsatz ergibt für meine aktuelle TestDatei ggw bspw folgd 4 verborgene Namen:
1 → Tabelle3!_FilterDatabase (verborgen):=Tabelle3!$B$4:$B$10
2 → _xlfn.AGGREGATE (verborgen): (NV)
3 → _xlfn.IFERROR (verborgen): (NV)
4 → _xlfn.RANK.AVG (verborgen): (NV)
Erst der 5. ist ein von mir angelegter Name, während der 1. von einem Filter ein­ge­rich­tet wurde. Ein solcher Name wird erst dann (bei erneutem DateiÖffnen) entfernt, wenn keine dazugehörige Fkt in der Datei mehr verwendet wird.
Morrn, Luc :-?
{=MIN(WENN(G2:G64="out";H2:H64))}
22.10.2017 19:18:37
WF
und natürlich MAX statt MIN
WF
AW: könnte hier aber auch #NV ergeben owT
22.10.2017 19:26:32
...
Gruß Werner
.. , - ...
ohne out gibt's bei Dir #ZAHL!
22.10.2017 19:47:58
WF
.
AW: #ZAHL! wäre dann ja auch korrekt! owT
23.10.2017 13:17:03
...
Gruß Werner
.. , - ...
AW: {=MIN(WENN(G2:G64="out";H2:H64))}
22.10.2017 21:04:18
Frank
Hallo WF,
ja, die Formel würde sicher funktionieren - wenn da nicht die #NVs in den Spalten G und H wären. So liefert sie ebenfalls #NV. Wenn ich also entgegen meiner Hoffnung nicht zwei Bedingungen verwenden kann, muss ich wohl tatsächlich an der Quelle aktiv werden und dort schon eine WENN-Formel einfügen, um die #NV zu unterdrücken. Dann stehen in G und H auch keine mehr.
Viele Grüße
Frank
das geht schon
22.10.2017 21:35:43
WF
Hi,
dann musst Du die #NV in Spalte G und in Spalte H ausschließen:
{=MIN(WENN(ISTTEXT(G2:G64);WENN(G2:G64="out";WENN(ISTZAHL(H2:H64);H2:H64))))}
Oder Du nimmst die Formel von neopa. Dort müsste allerdings noch die Fehlermeldung unterdrückt werden, wenn kein "out" vorkommt.
WF
AW: das geht schon
22.10.2017 22:10:41
Frank
Hallo WF,
yeah, das funktioniert! Ich danke Dir vielmals. Excel ist schon erstaunlich, insbesondere wenn man die Stolpersteine und ihre Lösungen kennt... :-)
Mit SUMMEWENNS habe ich schon lange Verkettungen aufgesetzt, aber hier war es mir nicht gelungen.
Ich hatte parallel übrigens den Ansatz versucht, die "#NV" an der Quelle, beim SVERWEIS in Spalte C zu unterdrücken, damit sie nicht nach G und H "verschleppt" werden. Dort steht in der ersten Zeile jetzt
"WENN(ISTFEHLER(SVERWEIS(A2;'JLL #2'!A:N;6;FALSCH));"";SVERWEIS(A2;'JLL #2'!A:N;6;FALSCH))"
und liefert auch das erwartete Ergebnis. In den Zeilen, wo sonst "#NV" stand, ist jetzt eine leere Zelle und in denen, wo ein Match gefunden wird, steht der Prozentwert aus der anderen Tabelle.
In Spalte H: stand danach allerdings "#WERT!" als Ergebnis der Formel "C2-ABS(F2)". Also habe ich dort auch eine WENN-Bedingung eingefügt. Weder ISTLEER noch ISTFEHLER haben aber gezogen, es klappte nur mit "WENN(ISTZAHL(C2);C2-ABS(F2);"")". Kannst Du mir sagen, warum ISTFEHLER in C geht, aber nicht in H, das auf C zugreift? Was missverstehe ich da gerade?
Viele Grüße
Frank
ISTFEHLER funktioniert doch + ein Tip
23.10.2017 08:02:20
WF
Hi,
statt:
=WENN(ISTZAHL(C2);C2-ABS(F2);"")
funktioniert auch:
=WENN(ISTFEHLER(C2-ABS(F2));"";C2-ABS(F2))
aber ein Tip:
seit Excel-2007 gibt es die Funktion WENNFEHLER
statt:
=WENN(ISTFEHLER(SVERWEIS(A2;'JLL #2'!A:N;6;FALSCH));"";SVERWEIS(A2;'JLL #2'!A:N;6;FALSCH))
nimmst Du:
=WENNFEHLER(SVERWEIS(A2;'JLL #2'!A:N;6;FALSCH);"")
und dann statt:
=WENN(ISTFEHLER(C2-ABS(F2));"";C2-ABS(F2))
=WENNFEHLER(C2-ABS(F2);"")
WF
AW: ISTFEHLER funktioniert doch + ein Tip
23.10.2017 18:00:25
Frank
Hallo WF,
super, vielen Dank. Das hat sofort funktioniert und sieht tatsächlich deutlich angenehmer aus.
Viele Grüße
Frank
ISTFEHLER funktioniert doch + ein Tip
23.10.2017 08:06:10
WF
Hi,
statt:
=WENN(ISTZAHL(C2);C2-ABS(F2);"")
funktioniert auch:
=WENN(ISTFEHLER(C2-ABS(F2));"";C2-ABS(F2))
aber ein Tip:
seit Excel-2007 gibt es die Funktion WENNFEHLER
statt:
=WENN(ISTFEHLER(SVERWEIS(A2;'JLL #2'!A:N;6;FALSCH));"";SVERWEIS(A2;'JLL #2'!A:N;6;FALSCH))
nimmst Du:
=WENNFEHLER(SVERWEIS(A2;'JLL #2'!A:N;6;FALSCH);"")
und dann statt:
=WENN(ISTFEHLER(C2-ABS(F2));"";C2-ABS(F2))
=WENNFEHLER(C2-ABS(F2);"")
WF

311 Forumthreads zu ähnlichen Themen

Indirekt-Formel mit Wenn-Funktion?
Ingrid

Hallo, hab noch ein Problem...
Gibts auch die Möglichkeit diese Indirekt-Formel (wie schon in "Auto-Ausfüllen: Zelle gleich - anderes Tabellenblatt" besprochen) mit einer Wenn-Funktion zu versehen?
=INDIREKT("'" &ZEILE(D1) &"'!B16")
Wenn B16="" Dann C16
LG
Ingrid
11.09.2008 14:37:28
Thread-ID: 1009130
5 Beiträge
Anzeige
Teilergebnis bei SummeWenn
Stefan

Hallo Zusammen,
ich möchte die folgende Formel noch um ein Teilergebnis ergänzen das nur die sichtbaren Zellen gezählt werden
=SUMMEWENN($J$5:$J$448;"<"&0;$J$5:$J$448)
Hat da einer ne Idee
Danke für eure Hilfe
18.11.2008 12:39:00
Thread-ID: 1025612
2 Beiträge
Teilergebnis(109) mit Wenn
Thorsten

Hallo Excler,
brauche mal wieder eure Hilfe.
Meine Formel derzeit:
SUMMEWENN($G$6:$G$1317;"K1325";$O$6:$O$1317)
Nun brauche ich aber die richtigen Werte nach Filtern.
Kann man die Formel auf TEILERGEBNIS(109) umschreiben?
Für Hilfe schon mal besten Dank
Gruß Thorste...
17.03.2009 11:39:30
Thread-ID: 1061037
3 Beiträge
Anzeige
Teilergebnis mit Summewenn kombinierbar?
u_hoernchen

Ich möchte in einer Tabelle die Summe von gefilterten Werten berechnen, aber immer nur jede 6.Zeile, die eine bestimmte Bezeichnung hat. Intuitiv hätte ich jetzt gerne eine Kombination aus Teilergebnis und Summewenn... geht so was?
Ulrike
07.04.2009 14:29:53
Thread-ID: 1067414
2 Beiträge
Verschachtelungen mit Wenn-Funktionen
Gießbach

Hallo Zusammen,
habe ein Problem:
Und zwar versuche ich gerade eine Berechnung unserer Frachtkosten bei einem Spediteur zu erstellen. Ich habe eine Matrix mit in Spalte A angegebenen Gewichten und in den Spalten B bis I die Kosten für das jeweilige Gewicht von/bis. Dazu sind die Preiss...
11.05.2009 15:50:07
Thread-ID: 1074386
4 Beiträge
Zählewenn mit zwei Bedingungen
DieterL

Hallo Excelfreunde,
ich arbeite mit Excel 2007 und habe in einer Tabellenauswertung folgende Formel die auch funktioniert:
=ZÄHLENWENNS(name;$A4;Art;50)
Dabei sind "name und Art" benannte Bereiche.
Die Formel gibt mir die Anzahl der Datensätze wieder, die mit den beiden Suchkrit...
30.07.2009 10:35:49
Thread-ID: 1092471
9 Beiträge
Dezimal in Bin mit wenn funktion
Matthias

Hallo,
wie kann man Dezimalzahlen in Binärzahlen nur mit einer wenn funktion umwandeln?
viele grüße
Matthias
09.09.2009 17:14:58
Thread-ID: 1101589
7 Beiträge
Zählen mit zwei Bedingungen
Walter

Guten Morgen alle Excel Freunde,
Ich bekomme die Formel nicht hin.
in spalte "A:A" stehen werte von 1 bis 10
in Spalte "B : B" steht leer oder 1
Ich möchte nun auswerten 1. 3 plus leer und 3 plus 1
ZÄHLENWENN($A$1:$A$150;3)*(UND($b$1:$B$150;$AK$1))
DIE FUNKT. NICHT
...
21.09.2009 09:14:42
Thread-ID: 1104336
6 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
4
2
1
2
6
1
3
8
6
5
8
4
4
7
2
2
18
3
3
4
6
2
4
2
3