Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1720to1724
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
Inhaltsverzeichnis

Index-Vergleich / 2te Bedíngung in Vergleich

Index-Vergleich / 2te Bedíngung in Vergleich
02.11.2019 23:14:53
Jürgen
Moin Gemeinde !
Ich brech mir gleich die Finger wegen einer vermutlich einfachen Lösung.
Tabelle mit Freizeitname, Datum von, Datum bis, Teilnehmer und Kosten.
Die Tabelle geht über mehrere Jahre und der Freizeitname und die Kosten können über die Jahre mehrfach gleich sein. Der einzige Unterschied ist das Datum.
Ich möchte jetzt über Index-Vergleich unter Berücksichtigung der Kosten (könnte auch eine andere Variable sein) und des Jahres den Freizeitnamen auslesen. Krieg´s aber nicht hin.
Mit Summenprodukt die Teilnehmerzahl oder die Kosten ist kein Problem, aber wie bekomme ich den Text ausgelesen ?
Ich probiere mit Ansätzen wie diesem :
"{=INDEX(B6:F15;VERGLEICH(Kosten;F6:F15;0);vergleich(Jahreszahl(wenn(jahr(D6:D15)=Jahreszahl));B6:F6))}"
Bei Index(B6:F15;Vergleich(Kosten;F6:F15;0);1) wird der erste gefundene Datensatz gelesen, bei dem die Kostenvariable gefunden wird. Wie bringe ich die Formel jetzt hin, dass nur die Kosten im angegebenen Jahr als Kriterium herangezogen werden ?
Könnte mir bitte mal jemand auf die Sprünge helfen ?
Wäre wirklich super nett !
Hab wohl auch grad ne mathematische und logische Blockade.
Danke im Voraus !
https://www.herber.de/bbs/user/132917.xlsx

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: nicht mit 2 Vergleichen sondern mit &
03.11.2019 06:59:41
Luschi
Hallo Jürgen,
oder so:

=INDEX($B$7:$B$20;AGGREGAT(15;6;ZEILE($D$7:$D$20)/($F$7:$F$20=$H$2)/(JAHR($D$7:$D$20)=$H$4);ZEILE(A1) )-6;) 

Diese Formel verrät Sir auch, wenn es mehrere Angebote im gleichen Jahr zum gleichen Preis gibt.
Gruß von Luschi
aus klein-Paris
AW: hierzu ...
03.11.2019 17:47:39
neopa
Hallo Luschi,
... das was Du anstrebst, wäre etwas einfacher zu erzielen, und zwar so:
=INDEX(B:B;AGGREGAT(15;6;ZEILE(D$7:D$99)/(F$7:F$99=H$2)/(JAHR(D$7:D$99)=H$4);ZEILE(A1)))
Doch wenn Du diese Formel nach unten ziehst, würde diese keine Veranstaltung finden, die über das Jahresende angeboten wird und anstelle dessen eine Fehlermeldung erzeugen. Letzteres lässt sich durch eine Formelklammerung mit WENNFEHLER() zwar vermeiden, aber für das Finden der Jahresendübergreifenden bedarf einer Formelerweiterung. Z.B. so:
=WENNFEHLER(INDEX(B:B;AGGREGAT(15;6;ZEILE(D$7:D$99)/(F$7:F$99=H$2)/(MMULT((JAHR(C$7:D$99)=H$4)+0;{1;1})&gt0);ZEILE(A1)));"")
Gruß Werner
.. , - ...
Anzeige
AW: nicht mit 2 Vergleichen sondern mit &
03.11.2019 09:32:50
Jürgen
Super, vielen Dank !
War mir nicht bewusst, dass man innerhalb des Vergleichs mit Verketten arbeiten kann.
Aber so tastet man sich über Jahre von einer Forml zur nächsten :-(
Ich muss leider auch zugeben, dass sich mir die Formel mit Aggregat überhaupt nicht erschließt.
Jetzt wird mal versucht, ob ich mein Vorhaben damit erreiche.
Vermutlich wird´s nicht lange dauern, bis ich wieder hier aufschlage.
Beste Grüße an euch !
AW: besser noch, die Datentab. formatieren ...
03.11.2019 18:35:08
neopa
Hallo Jürgen,
... und zwar mit der Funktion: "Als Tabelle formatieren" (mit der Option: "...hat Überschriften".
Dadurch wird eine "intelligente" Tabelle erzeugt, die für eine entsprechende Formelauswertung den Vorteil bietet, dass sich die Formel automatisch an evtl. Datenerweiterungen anpasst und auch beliebig innerhalb der Arbeitsmappe verschoben werden kann.
Nachfolgend hab ich den mit dieser Funktion durch Excel automatisch vergebenen Namen "Tabelle1" belassen. kann aber im Namensmanager auch nachträglich noch ändern.
Damit Du das Formelergebnis (Formel H8 ziehend nach unten kopieren) besser nachvollziehen kannst, hab ich ein paar Datenänderungen/-erweiterungen vorgenommen.
Arbeitsblatt mit dem Namen 'Statistic_2'
 ABCDEFGH
1       Kosten
2       280
3       Jahr
4       2017
5        
6 FreizeitvonbisTeilnehmerKosten  
7 Fahrradtour6. Jul. 201618. Jul. 201614280 Freizeit
8 Jakobsweg6. Jul. 201620. Jul. 201621300 Nordsee
9 Sommer auf Malle15. Aug. 201612. Sep. 201624600 Motorradtour
10 Nordsee24. Jun. 20174. Jul. 201717280 Skiwanderung
11 Kanutour Schweden3. Jul. 201714. Jul. 201712140  
12 Motorradtour7. Jul. 201721. Jul. 201713280  
13 Sommer auf Ibiza7. Jul. 20177. Aug. 201727780  
14 Skiwanderung27. Dez. 20173. Jan. 201812280  
15 Wanderung5. Sep. 201819. Sep. 201815320  
16 Surfen16. Jun. 20181. Aug. 201819280  
17        

ZelleFormel
H8=WENNFEHLER(INDEX(Tabelle1[Freizeit];AGGREGAT(15;6;ZEILE(Tabelle1)/(Tabelle1[Kosten]=H$2)/(MMULT((JAHR(Tabelle1[[von]:[bis]])=$H$4)+0;{1;1})0);ZEILE()-ZEILE(H$7))-ZEILE(Tabelle1[#Kopfzeilen]));"")
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...
Anzeige
es gibt eine Levelangabe !
03.11.2019 22:11:01
WF
der Fragesteller ist Anfänger, der sich freut, wenn er Basisfunktionen richtig anwenden kann.
WF
AW: sicher, doch ...
04.11.2019 11:26:04
neopa
Hallo WF,
... die Funktionalität des Erstellens und Nutzens der Funktion "Als Tabellen formatieren" gehört nun mal seit Excel 2007 (in Anfängen auch schon seit Excel 2003) zu den Excel-Basisfunktionalitäten. Diese Funktionalität ist auch wesentlicher einfacher zu erlernen und anzuwenden, als das Aufstellen von Matrixformeln und bringt darüber hinaus schnell Nutzen für viele einfache Einsatzmöglichkeiten und bietet die besser geeignete und teils sogar notwendige Voraussetzung z.B. für für Pivot- und PowerQuery-Auswertungen aber auch für viele Formel-Lösungen (auch VBA-Lösungen, mit welchen ich mich zwar nicht beschäftige aber auch nicht ablehne) an.
Am schwierigsten ist für ExcelUser mit Basiswissen meisten, eine für die Auswertung gut geeignete Datenstruktur zu gestalten. Dies solchen Usern zu vermitteln ist noch schwieriger. Dabei kann eine "formatierte Tabelle" etwas unterstützend sein. Jürgen jedenfalls hat für seine Aufgabe schon eine vorbildliche Datenstruktur, die lediglich noch ein paar Klicks braucht, um sie in eine "intelligente" Tabelle zu wandeln.
Ich hielt es hier darüber hinaus auch für notwendig, Jürgen darauf aufmerksam zu machen, dass es bei seiner Thematik auch Ausnahmefälle geben kann, die z.B. mit den zuvor im thread aufgezeigten Formeln nicht abgedeckt waren. Er muss meine aufgezeigte Lösungsformel ja nicht gleich verstehen und nachvollziehen können, aber sie versetzt ihn zumindest in die Lage darüber nachzudenken. Jürgen hat ja auch zum Ausdruck gebracht, dass er nicht nur eine Lösung sucht, sondern sich auch mit einer solchen auseinandersetzen will.
Außerdem hatte ich ja im thread in meinem Betrag an Luschi Aussagen zu seinem AGGREGAT()-Formelvorschlag getroffen.
Ich kann Jürgen nur noch den Rat geben, dass es in Excel mehr und oft auch effektivere Lösungsmöglichkeiten gibt als nur der Einsatz von der von Dir so "geliebten" klassischen Matrixformeln. Seien es z.B. AGGREGAT-, Pivot- oder PowerQuery-Lösungen, alles Lösungsmethoden die auch oder gerade für Exceluser mit Basiswissen mit entsprechenden Willen sich einfach(er) aneignen lassen.
Gruß Werner
.. , - ...
Anzeige
unter "Varianten" stehen 5 Lösungen ohne { }
04.11.2019 12:03:20
WF
.
AW: darum ging es nicht owT
04.11.2019 12:22:48
neopa
Gruß Werner
.. , - ...
AW: übrigens ...
04.11.2019 14:58:40
neopa
Hallo WF,
... ich hatte schon mehrfach darauf aufmerksam gemacht, das man eine Lösungsformel mit SUMMENPRODUKT() für die Ermittlung einer Zeilennummer, vom Grundsatz her vermeiden sollte, weil man da nie sicher sein kann, dass der jeweilige Suchwert oder die Suchwertkombination nicht doppelt oder mehrfach vorkommen kann.
Und gerade eben stelle ich fest, dass genau dies in der von Dir verlinkten Seite noch immer als eine derartige Lösungsvariante angeboten wird, obwohl die Suchkombination im dortigen Beispiel auch nicht einmalig ist.
Also am besten lösche die SUMMENPRODUKT() -Formel auf dieser Seite.
Gruß Werner
.. , - ...
Anzeige

126 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige