Live-Forum - Die aktuellen Beiträge
Datum
Titel
23.04.2024 14:47:39
Formel SVERWEIS
23.04.2024 14:27:08
Excel - Zellen verketten anhand anderer Spalte
23.04.2024 14:23:45
Gehe zu Tabellenblatt
23.04.2024 14:07:03
Alternative Summewenn für Verlinkung closed Workbook
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1688to1692
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
1688to1692
1688to1692
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Verweis mit 2 Kriterien, immer erster Treffer

Verweis mit 2 Kriterien, immer erster Treffer
23.04.2019 13:36:42
Chris94
Hallo!
ich brauche mal wieder einen Denkanstoß, da ich gerade einfach nicht auf die Lösung komme, wie ich immer den ersten Treffer geliefert bekomme anhand von 2 Kriterien (u.a. ISTZAHL) in einer Matrix.
In der Datei ist derzeit VERWEIS hinterlegt, was mir den letzten Treffer liefert. Ich brauche den Erste; Wunschergebnis ist grün markiert.
Hier die Beispieldatei:
https://www.herber.de/bbs/user/129327.xlsx
Vielen Dank im Voraus!
Gruß,
Chris

28
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Verweis mit 2 Kriterien, immer erster Treffer
23.04.2019 13:46:54
SF
Hola,
z.B.:
=INDEX(C2:C11;AGGREGAT(15;6;ZEILE(A2:A11)-1/((ISTZAHL(A2:A11))*(B2:B11=H1));1))
Gruß,
steve1da
Danke SF, klappt :-) owT
23.04.2019 14:28:08
Chris94
.
einfacher per Index / Vergleich
23.04.2019 15:19:40
WF
Hi,
folgende Arrayformel:
{=INDEX(C:C;VERGLEICH(H1;WENN(ISTZAHL(A1:A99);B1:B99);0))}
WF
AW: ausreichend wäre schon ...
23.04.2019 15:42:40
neopa
Hallo Chris,
... ganz simpel: =AGGREGAT(15;6;C2:C99/(B2:B99=H1);1)
Sollte in A3 jedoch ein Textwert stehen, dann eben so:
=AGGREGAT(15;6;C2:C99/(B2:B99=H1)/ISTZAHL(A2:A99);1)
Gruß Werner
.. , - ...
dann kill noch ISTZAHL
23.04.2019 16:00:25
WF
Hi,
(A2:A99<"") ist dasselbe wie ISTZAHL(A2:A99)
WF
AW: ja das wäre eine Option owT
23.04.2019 16:06:46
neopa
Gruß Werner
.. , - ...
Anzeige
Diese Aussage ist definitiv falsch, ...
24.04.2019 03:47:24
Luc:-?
…Walter,
und das weißt Du auch! Richtig ist, dass das ausreicht, wenn es nur darum geht, Leerzellen, nicht jegliche Nicht-Zahlen abzufangen.
Morhn, Luc :-?
AW: nein, sie ist zutreffend owT
24.04.2019 07:30:08
neopa
Gruß Werner
.. , - ...
Dann probier das mal mit Texten, wenn ...
24.04.2019 13:59:38
Luc:-?
…die ebenfalls nicht zulässig sein sollen, Werner;
außerdem ist dann nicht nur ISTZAHL, sondern bei AGGREGAT-Verwendung der ganze Vgl überflüssig, was ich demonstriert hatte.
Außerdem hatte Walter im von Dir nicht (aber mir) kommentierten Beitrag so ganz nebenbei das GrundGerüst für die bisher kürzeste Fml geliefert… ;-)
Gruß, Luc :-?
AW: ich hatte es sogar 2x getan; Du auch? Denn ...
24.04.2019 16:41:55
neopa
Hallo Luc,
... Deine SVERWEIS()-Matrix-Formel und Deine AGGREGAT()-Formel von 4:37 liefern jedenfalls keine korrekten Ergebnisse. Meine entsprechenden Formeln schon, auch die mit dem Vergleich &lt"".
Gruß Werner
.. , - ...
Anzeige
Bei meinen TestDaten war's genau umgekehrt, ...
24.04.2019 19:30:11
Luc:-?
…Werner,
aber Du hast natürlich teilweise recht und mit geringfügigen (zT von Dir vorgeschlagenen) Erweiterungen sollten diese Fmln nun auch mit den OriginalDaten wie gewünscht fktionieren…
Trotzdem halte ich Deine Verkürzung für riskant, es sei denn, die DatenVorsortierung ist stets gewährleistet.
Kleiner als ein LeerText sind nur echte Zahlen. Ich hatte hier an <>"" gedacht (ggf verguckt).
Luc :-?
mittlerweile hast Du Aggregatklappen auf
23.04.2019 16:35:11
WF
Hi,
Deine erste Formel:
=AGGREGAT(15;6;C2:C99/(B2:B99=H1);1)
ist nichts anderes als diese aus den Excel-Gründungszeiten:
=SVERWEIS(H1;B2:C99;2;0)
WF
Anzeige
Das ist ebenfalls falsch, ...
24.04.2019 04:21:09
Luc:-?
…Walter,
denn der SVERWEIS liefert hier zwar das verlangte 1.Auftreten, wenn man die singulare MatrixFml so ergänzt, …
{=SVERWEIS(H1;B2:C99*ISTZAHL(A2:A99);2;0)}
…nicht aber Werners Fml, die von allen Auftreten nicht das 1., sondern das mit dem kleinsten fehlerfreien Divisionsergebnis wählt. Das mag im Bsp gleich sein, muss es aber prinzipiell nicht. Das wäre auch mit folgd Fml nicht anders, sondern ggf nur ein anderer Wert:
=AGGREGAT(15;6;C2:C99/(A2:A99*(B2:B99=H1));1)
Gruß, Luc :-?
Nein, ...
24.04.2019 04:37:09
Luc:-?
…Werner,
damit erhältst Du nur zufällig den C-Wert zum 1. gültigen Auftreten von H1 in B! An Steves AGGREGAT- und Walters 1.Fml führt folglich prinzipiell nichts vorbei, außer meiner SVERWEIS-Variante. Was AGGREGAT betrifft kann man Steves Fml etwas kürzen (s.ob) oder sie auch so fassen:
=INDEX(C2:C11;AGGREGAT(15;6;ZEILE(1:10)/A2:A11/(B2:B11=H1);1))
Mal ganz abgesehen von möglichen Varianten mit UDFs… ;-)
Morhn, Luc :-?
Anzeige
AW: im Gegenteil, denn Du irrst Dich hier ...
24.04.2019 07:31:08
neopa
Hallo Luc,
... was mE ganz einfach nachzuvollziehen ist.
Lösche in der Beispieldatei 1. einfach in A3 den Wert oder 2. schreibe einen Text in A3. Meine für diese Optionen angegebene (2.) Formel ermittelt dann den richtigen Wert, was auf Deine Formeln nicht zutrifft.
Natürlich gehe ich davon aus, dass die Werte in Spalte B aufwärts sortiert vorliegen, was ja aus dem Beispiel eindeutig entnehmbar ist. Andernfalls hätte ich natürlich auch INDEX() von AGGREGAT() genutzt allerdings
so: =INDEX(C:C;AGGREGAT(15;6;ZEILE(C2:C99)/(B2:B99=H1)/ISTZAHL(A2:A99);1)) oder
mit dem Vorschlag von WF so : =INDEX(C:C;AGGREGAT(15;6;ZEILE(C2:C99)/(B2:B99=H1)/(A2:A99&lt"");1))
Gruß Werner
.. , - ...
Anzeige
Du hattest übersehen, ...
24.04.2019 13:51:56
Luc:-?
…Werner,
dass ich prinzipiell (zufällig) geschrieben und mich nicht auf irgendeine BspDatei bezogen hatte. Die hatte ich mir selbst erstellt, um sol­che Zufälle zu vermeiden, denn Du bist ja ein Anhänger weitergehender Überlegungen (auch in Fmln), die auch DatenEventualitäten aus­schließen wollen… ;-)
2 Fmln, die prinzipiell unterschiedliche Ansätze verfolgen, können einfach nicht in allen Fällen gleiche Ergebnisse liefern! Es ist nunmal ein Unterschied, ob Positions- oder DatenWerte aufsteigend sortiert wdn, egal, ob das mal zufällig passt!
Gruß, Luc :-?
AW: nein ...
24.04.2019 16:38:23
neopa
Hallo Luc,
... mir ist natürlich klar,dass "Es ist nun mal ein Unterschied, ob Positions- oder DatenWerte aufsteigend sortiert wdn, egal, ob das mal zufällig passt!"
Hier im Beispiel waren und sind für mich die Datenwerte aufwärts sortiert. Sollte nach Kriterium 2 und 1 sortiert werden, dann natürlich kommt für mich nur einen Definition mit INDEX() in Frage.
Gruß Werner
.. , - ...
Anzeige
Ich habe von den gezeigten Fmln auf die Daten ...
24.04.2019 19:08:59
den
…rückgeschlossen, Werner;
wenn die Daten in B schon aufsteigend sortiert vorliegen, muss man sie ja nun nicht nochmals sortieren, nur um eine längere (matrixfml-formfreie) AGGREGAT-Lösung verwenden zu können, zumal SVERWEIS deutlich durchschaubarer ist (Bedingung: H1>0):
{=SVERWEIS(H1;B2:C99*ISTZAHL(A2:A99);2;0)}
Mit Deinem INDEX-Trick könnte man dann auch auf die MatrixFml-Form verzichten:
=SVERWEIS(H1;INDEX(B2:C99*ISTZAHL(A2:A99);0);2;0)
Das entspräche übrigens in etwa dem, was bei Google-Docs gemacht wird (im Prinzip ist das auch das, was AGGREGAT im BasisTeil der DatenVorbereitung leistet).
Luc :-?
Anzeige
AW: Dein Rückschluss ist aber nicht zutreffend ...
25.04.2019 10:50:41
neopa
Hallo Luc,
... in H1 und in Spalte B stehen Textwerte. Demzufolge können Deine hier aufgezeigten SVERWEIS()-Formeln nicht funktionieren.
Gruß Werner
.. , - ...
Tja, dann habe ich wohl Pech gehabt, ...
25.04.2019 14:03:58
Luc:-?
…mich auf Walters Einwurf zu verlassen, Werner,
und deshalb auf Zahlen in H1 und B zu schließen, was eigentlich eher nicht so naheliegend gewesen wäre…
Damit wäre Walters Vgl mit SVERWEIS nun sogar doppelt unpassend, mathematisch und von den Kriterien her. Eine AGGREGAT-KKLEINSTE-Konstruktion kann nicht mit einem SVERWEIS gleich gesetzt wdn, wenn erstere gar keine Position ermittelt*. Wenn doch, wird zusätzlich stets INDEX benötigt, um einen Vgl mit SVERWEIS mathematisch zu rechtfertigen. Ein SVERWEIS müsste dann, falls überhaupt möglich, anders aussehen.
* Das hatte mich stutzig gemacht.
Gruß, Luc :-?
Anzeige
Habe inzwischen eine für Texte passende Form ...
25.04.2019 20:06:19
Luc:-?
…von SVERWEIS unter Einschluss aller Kriterien gefunden, Werner,
allerdings nur als singulare MatrixFml:
{=SVERWEIS(H1;WENN(A2:A11<"";B2:C11;{0.0});2;0)}
Das wäre dann wohl die rationellste Fml, die auch nicht versagt, wenn Spalte C unsortiert ist.
Gruß, Luc :-?
AW: ja, so ist es möglich, aber eben mit {} ;-)owT
26.04.2019 10:17:59
neopa
Gruß Werner
.. , - ...
Das ist ja gerade das Geniale am ursprüngl Xl, ...
28.04.2019 14:48:23
Luc:-?
…Werner,
dass es so konstruiert wurde, dass es im TabBlatt eine positionsorientierte HauptAbarbeitungs­vari­ante und eine durch simple Mitteilung an Xl einstellbare zusammenfassende gibt! Das wird leider oft verkannt und die MatrixVariante missverstanden, obwohl sie praktisch in jeder Calc-Software vorkommt, auch in Google-Docs, nur wird das in unterschiedlicher Form mitgeteilt. Wahrscheinlich war sie auch schon in Lotus 1-2-3, dem Marktführer der 90er des vorigen Jhdts, vorhanden.
Wäre es nicht so, hätte jede Fkt (wie ggf eine UDF) so pgmmiert wdn müssen, dass jedes Argument auch ein (Ko-)Vektor oder eine Matrix sein dürfte, wenn das sinnvoll vorkommen kann. So konnte man sich aber auf die Fktt beschränken, bei denen es durch ihre Auf­gabe ohnehin idR nur so sinnvoll ist. Der Rest variiert die skalaren Argumente einfach nur über das/den angegebene/n Inter­vall/Be­reich. Das kann man mit VBA für eine UDF kaum simulieren, da das vom FmlText-Interpreter generell für alle Standard-Fktt gemacht wird (die darauf eingerichtet sind). Mit UDFs klappt das nicht, da diese extra für so etwas eingerichtet wdn müssten — nur wie…‽
Wenn nun neuerdings (zumindest in einfachen Fällen) auch in ZellFmln auf diese Mitteilung an Xl verzichtet wdn kann, steckt dahinter sicher eine weitergehende Beurteilung, die dann automatisch die am wahrscheinlichsten beabsichtigte sinnvolle Berechnungsvariante wählt.
Gruß + schöSo, Luc :-?
Anzeige
AW: sehe ich auch etwas anders ...
24.04.2019 16:58:38
neopa
Hallo Luc,
... obwohl es an sich grundsätzlich schon längst nicht mehr um "die kürzeste Formel" geht bzw. gehen sollte.
Aber wenn ich INDEX() einsetze und dazu in der Formel keinen Vergleich anwenden möchte, dann würde ich im konkreten Beispiel meine Formel so definieren:
=INDEX(C:C;AGGREGAT(15;6;ZEILE(C2:C99)/(B2:B99=H1)/(A2:A99)^0;1))
Gruß Werner
.. , - ...
Ja, ...
24.04.2019 18:28:21
Luc:-?
…Werner,
wenn die Zahlen in A ≠1 sind, kann passieren, dass keine vernünftige Position rauskommt.
Gruß, Luc :-?
AW: dies ist so nicht eindeutig, denn ...
25.04.2019 10:48:30
neopa
Hallo Luc,
... es ist unklar auf was Deine Aussage sich hier bezieht. Meiner Formel jedenfalls ist es egal,
ob in Spalte A Nichts, Textwerte oder Zahlenwerte stehen und letztere gleich, größer oder kleiner als 1 sind. Ausgewertet werden nur die Datensätze mit einem Zahlenwert.
Gruß Werner
.. , - ...
Ich hatte einfach immer nur 1, leer oder x für A …
25.04.2019 13:45:00
Luc:-?
…angenommen, Werner,
wobei ein anderer Zahlenwert als 1 ja Auswirkungen auf die Positionsermittlung hätte, was durch Deinen Zusatz ^0 vermieden wird.
Luc :-?

305 Forumthreads zu ähnlichen Themen

Ausgabe zweier Zellen mit SVERWEIS
Andreas

Hallo
Ich möchte mittels SVERWEIS Werte aus Spalte 2 oder wenn sich in dieser Spalte keine Werte befinden aus Spalte 3 ausgeben...
Kann mir da jemand weiterhelfen?
Gruß
Andreas
14.08.2008 11:46:00
Thread-ID: 1001793
4 Beiträge
Anzeige
Summe mit 2 Bedingungen
TobiasM

Hallo Excel-Experten,
ich würde gerne eine Summe mit zwei Bedingungen berechnen:
Wenn Wert in Spalte H gleich "EUR" und Wert in Spalte I gleich ""2005", dann Summe der entsprechenden Werte in Spalte J.
Könnte mir jmd. bitte einen Hinweis zur Formel geben?
Vielen Dank!!!
Tobi...
28.08.2008 10:11:30
Thread-ID: 1005310
3 Beiträge
Daten sortiert aus anderem Tabellenblatt auslesen
Robert

Hallo,
bräuchte eure Hilfe bitte.
Aus einem Tabellenblatt ("FEHLZEITEN") sollen Daten ausgelesen und in einem anderen Tabellenblatt ("EINSATZPLANUNG") ab Zeile 100 eingefügt und bei Änderungen in "FEHLZEITEN" aktualisiert werden.
Habe da mal eine Beispieldatei zur besseren Veransch...
28.08.2008 11:32:00
Thread-ID: 1005356
3 Beiträge
Anzeige
ZÄHLENWENN mit 2 Bedingungen
oblivion

Hallo, hab mal eine Frage zur Funktion ZÄHLENWENN.
Die Funktion lautet ja allgemein so =ZÄHLENWENN(A1:A15;"=19").
Ich würde aber gerne diese Funktion so erweitern, dass er mir alle Werte zählt, die kleiner als 19 sind, aber größer als 0.
Ich bekomme es nicht hin. Gibt es da eine Lö...
04.09.2008 18:07:33
Thread-ID: 1007473
3 Beiträge
Sverweis und mehrere Dateien durchsuchen
Benjamin

Hallo,
ist es möglich mit sverweis oder einer anderen Funktion mehere gleich aufgebaute Dateien zu durchsuchen und ein bestimmten wert (z.b in der zweiten Spalte) auszugeben. Also keine sheets sondern, dass das Makro die dateien eigenständig öffnet z.b. all dateien in diesem Odner öfnnet un...
26.09.2008 02:00:36
Thread-ID: 1012468
2 Beiträge
Zwei Kriterien für Sverweis
sascha76er

Hallo,
ich habe folgende Liste:
https://www.herber.de/bbs/user/55778.xls
Hier möchte ich gerne anhand von zwei Kriterien einen SVERWEIS erstellen für die Spalte P , die Kriterien sollen aus den Werten der Spalten E und M gebildet werden. Der entsprechende Wert muss im Tabellenblatt...
01.10.2008 16:04:31
Thread-ID: 1013672
3 Beiträge
PIVOTDATENZUORDNEN mit zwei Kriterien
Andi

Hallo Experten,
Ich versuch zurzeit eine Auswertung aus einer Pivot Tabelle zu erstellen, komm jedoch nicht weiter da ich zwei Kriterien in der X-achse und eins in der Y-achse berücksichtigen muss. Das heisst ich kann nicht einfach X-Achse und Y-achse mit der Pivot-tabelle abgleichen, sonde...
14.10.2008 12:16:00
Thread-ID: 1016295
5 Beiträge
SVerweis mit 2 Kriterien über Index??
Bernd

Hallo Freaks,
ich habe es wirklich versucht....und auch ein einfaches Bsp. aufgebaut. Nur die Indexformel funktioniert nicht...
Ich bitte Euch um Hilfe.
https://www.herber.de/bbs/user/56079.xls
Danke, Bernd
17.10.2008 15:26:00
Thread-ID: 1017185
4 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
3
3
2
2
5
2
1
2
4
3
6
11
3
5
11
6
2
2
2
5
1
3
3
3
6