Autor
|
Thema: Formel für Suchkriterien in zwei Spalten (2188 mal gelesen)
|
Fanny CAD Mitglied
Beiträge: 338 Registriert: 22.01.2010 AutoCAD 2013 AutoCAD Mechanical 2013 WIN 7, MS Office 2010
|
erstellt am: 25. Sep. 2014 11:29 <-- editieren / zitieren --> Unities abgeben:
Hallo, auf der Webseite www.excelformeln.de habe ich eine Formel gefunden, mit der ich Werte ermitteln kann, bei denen zwei Suchkriterien zutreffen müssen. Ein Beispiel habe ich angehängt. In den Zellen A2 und A3 stehen die gewünschten Suchkriterien, in der Tabelle C1:E8 sind die Kriterien und die Zielwerte aufgelistet. Die Formel lautet: ={INDEX(E2:E8;VERGLEICH(A2&A3;C2:C8&D2: D8;0))}. Das funktioniert alles prima, solange ich bei den Suchkriterien auch tatsächlich vorhandene Werte eingebe. Ich benötige nun aber eine Formel, die beim 2. Suchkriterium entweder den vorhandenen Wert oder den nächstgrößeren sucht. Bei den Kriterien "Wert 1" und "4" wäre das Ergebnis dann 9, bei den Kriterien "Wert 1" und "3" ebenfalls 9. Die Werte für "Kriterium 1" und "Kriterium 2" sind alphabetisch bzw. aufsteigend sortiert. Ich habe schon an der Formel herumgebastelt, aber außer Fehlermeldungen kommt dabei nichts heraus. Das dritte Argument bei VERGLEICH auf "-1" zu setzen bringt ebenfalls eine Fehlermeldung #NV. Kann mir jemand weiterhelfen? Vielen Dank im voraus. [Diese Nachricht wurde von Fanny CAD am 25. Sep. 2014 editiert.] Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
Bernd P Ehrenmitglied V.I.P. h.c. cook-general
Beiträge: 3358 Registriert: 07.06.2001
|
erstellt am: 25. Sep. 2014 11:33 <-- editieren / zitieren --> Unities abgeben: Nur für Fanny CAD
Servus, link zur Formel wäre hilfreich und der Anhang fehlt. ------------------ <----- Bitte Systeminfo eintragen, warum siehst du hier. "Warum Einfach es geht auch kompliziert". Schöne Grüsse aus der Steiermark Bernd P. Sport ist Mord Rekorde: Scalelist>11727, Fehler>34365, Layerfilter>XXXX Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
Fanny CAD Mitglied
Beiträge: 338 Registriert: 22.01.2010 AutoCAD 2013 AutoCAD Mechanical 2013 WIN 7, MS Office 2010
|
erstellt am: 25. Sep. 2014 11:42 <-- editieren / zitieren --> Unities abgeben:
|
Beverly Mitglied Dipl.-Geologe (Rentner)
Beiträge: 394 Registriert: 11.08.2007
|
erstellt am: 25. Sep. 2014 16:40 <-- editieren / zitieren --> Unities abgeben: Nur für Fanny CAD
|
Fanny CAD Mitglied
Beiträge: 338 Registriert: 22.01.2010 AutoCAD 2013 AutoCAD Mechanical 2013 WIN 7, MS Office 2010
|
erstellt am: 17. Okt. 2014 16:05 <-- editieren / zitieren --> Unities abgeben:
|
Fanny CAD Mitglied
Beiträge: 338 Registriert: 22.01.2010 AutoCAD 2013 AutoCAD Mechanical 2013 WIN 7, MS Office 2010
|
erstellt am: 08. Jan. 2015 13:16 <-- editieren / zitieren --> Unities abgeben:
Zitat: Original erstellt von Beverly: {=WENNFEHLER(INDEX(E2:E8;VERGLEICH(A2&A3;C2:C8&D2: D8;0));INDEX(E2:E8;VERGLEICH(A2&A3;C2:C8&D2: D8;1)))}
Hallo, ich muss jetzt doch nochmal das Problem aufrollen. Ich habe die o.g. Formel jetzt vereinfacht zu {=INDEX(E2:E8;VERGLEICH(A2&A3;C2:C8&D2: D8;1))} Das funktioniert auch. Allerdings liefert die Formel mir nur den Wert für das nächstkleinere Suchkriterium (siehe Argument "1" in der Formel VERGLEICH), ich brauche aber das nächstgrößere. Das Argument "1" durch "-1" zu ersetzen funktioniert nicht, da ich dann die Werte in der Suchtabelle anders sortieren müsste, was aber nicht möglich ist. Die Werte sind in beiden Suchspalten aufsteigend sortiert. Gibt es noch eine andere Möglichkeit?
[Diese Nachricht wurde von Fanny CAD am 21. Jan. 2015 editiert.] Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
Datenwürfel Mitglied Datenwürfler
Beiträge: 3 Registriert: 09.01.2015
|
erstellt am: 09. Jan. 2015 14:52 <-- editieren / zitieren --> Unities abgeben: Nur für Fanny CAD
Hallo, ich würde es mal mit einem ANKHOR FlowSheet versuchen. Ziemlich beeindruckend, was man damit alles anstellen kann ( wenn man sich mal eingearbeitet hat ). Hier wären passende Videos auf YouTube Beispiel 1 Beispiel 2 die in etwa auf dein Problem passen könnten. Die Tabellen können z.B. einfach von Excel in ein FlowSheet per Copy&Paste übertragen werden. Mittlerweile bin ich ganz fit damit, Excel benutze ich (fast) gar nicht mehr. Bei Fragen dazu, einfach eine PM senden. Grüße Datenwürfel [Diese Nachricht wurde von Datenwürfel am 21. Jan. 2015 editiert.] Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
Bernd P Ehrenmitglied V.I.P. h.c. cook-general
Beiträge: 3358 Registriert: 07.06.2001 W10-64bit, AMD Ryzen 7 3700X,32GB RAM, Sapphire Pulse Radeon RX 570 8G G5, Canon TX-3000 MFP, Maus Cherry MW4500, Sub:Infrastructure Design Suite, Office 365
|
erstellt am: 10. Jan. 2015 14:06 <-- editieren / zitieren --> Unities abgeben: Nur für Fanny CAD
Servus gut versteckt aber hier -1 Die Suchmatrix muss in absteigender Reihenfolge angeben werden.Man könnte sich in =C2&D2 eine Hilfspalte machen um zu sortieren und dann braucht man auch keine Arrayformel mehr sondern ein =INDEX(E2:E10;VERGLEICH(A2&A3;B2:B10;-1)) reichen. ------------------ <----- Bitte Systeminfo eintragen, warum siehst du hier. "Ist bestimmt nicht das schönste Projekt auf Erden, aber läuft vielleicht.". Schöne Grüsse aus der Steiermark Bernd P. Sport ist Mord Rekorde: Scalelist>11727, Fehler>34365, Layerfilter>XXXX Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
Fanny CAD Mitglied
Beiträge: 338 Registriert: 22.01.2010 AutoCAD 2013 AutoCAD Mechanical 2013 WIN 7, MS Office 2010
|
erstellt am: 13. Jan. 2015 10:37 <-- editieren / zitieren --> Unities abgeben:
Zitat: Original erstellt von Datenwürfel: ich würde es mal mit einem ANKHOR FlowSheet versuchen.
Danke für den Tipp, aber dann können meine Kollegen wieder nicht damit arbeiten. Zitat: Original erstellt von Bernd P: Man könnte sich in =C2&D2 eine Hilfspalte machen um zu sortieren ...
Hm, wieder eine Spalte mehr... Ich will ja nicht rummäkeln, aber die Datei ist jetzt schon durch diverse Array-Formeln lahm. Aber Ausprobieren werde ich es mal. Danke. Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
Fanny CAD Mitglied
Beiträge: 338 Registriert: 22.01.2010 AutoCAD 2013 AutoCAD Mechanical 2013 WIN 7, MS Office 2010
|
erstellt am: 21. Jan. 2015 12:33 <-- editieren / zitieren --> Unities abgeben:
Zitat: Original erstellt von Bernd P: Man könnte sich in =C2&D2 eine Hilfspalte machen um zu sortieren und dann braucht man auch keine Arrayformel mehr sondern ein =INDEX(E2:E10;VERGLEICH(A2&A3;B2:B10;-1)) reichen.
Wenn ich mir eine Hilfsspalte bastle, dann kann ich nur nach exakten Werten suchen und gleich SVERWEIS nehmen. Ich brauche aber in der zweiten Spalte einen Vergleich größer/gleich. ******************* Ich habe es mal mit dieser Formel versucht: =INDEX(Tabelle_Wert;SUMMENPRODUKT((Tabelle_Kriterium1=A2)*(Tabelle_Kriterium2>=A3)*(ZEILE(2:8)-ZEILE(1:1)))) (Herkunft: www.excelformeln.de/formeln.html?welcher=30) aber die Ergebnisse sind mir komplett schleierhaft. Mal funktioniert es, mal nicht. Kann mir da jemand weiterhelfen? [Diese Nachricht wurde von Fanny CAD am 21. Jan. 2015 editiert.] Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
Datenwürfel Mitglied Datenwürfler
Beiträge: 3 Registriert: 09.01.2015
|
erstellt am: 21. Jan. 2015 15:17 <-- editieren / zitieren --> Unities abgeben: Nur für Fanny CAD
|
Bernd P Ehrenmitglied V.I.P. h.c. cook-general
Beiträge: 3358 Registriert: 07.06.2001 W10-64bit, AMD Ryzen 7 3700X,32GB RAM, Sapphire Pulse Radeon RX 570 8G G5, Canon TX-3000 MFP, Maus Cherry MW4500, Sub:Infrastructure Design Suite, Office 365
|
erstellt am: 21. Jan. 2015 16:51 <-- editieren / zitieren --> Unities abgeben: Nur für Fanny CAD
Servus.... Die Werte müssen beim Vergleich mit -1 absteigend sortiert sein Die Suchmatrix muss in absteigender Reihenfolge angeben werden. Vergleich liefert #NV weil deine Liste aufsteigend sortiert ist. sortiere richtig, verwende vergleich dann funkts auch.... @Datenwürfel ich denke nicht das Fannys Kollegen Lizenzprobleme hat ps.: lade doch mal ein Beispiel.xls hoch da kann man die Formel leicht einbauen und muss nichts selber basteln. ------------------ <----- Bitte Systeminfo eintragen, warum siehst du hier. "Ist bestimmt nicht das schönste Projekt auf Erden, aber läuft vielleicht.". Schöne Grüsse aus der Steiermark Bernd P. Sport ist Mord Rekorde: Scalelist>11727, Fehler>34365, Layerfilter>XXXX Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
Datenwürfel Mitglied Datenwürfler
Beiträge: 3 Registriert: 09.01.2015
|
erstellt am: 21. Jan. 2015 17:47 <-- editieren / zitieren --> Unities abgeben: Nur für Fanny CAD
|
Fanny CAD Mitglied
Beiträge: 338 Registriert: 22.01.2010 AutoCAD 2013 AutoCAD Mechanical 2013 WIN 7, MS Office 2010
|
erstellt am: 21. Jan. 2015 22:52 <-- editieren / zitieren --> Unities abgeben:
Zitat: Original erstellt von Datenwürfel: Man kann die Leute ja nicht zu ihrem Glück zwingen
Seufz, ja. ****************** Aber ich glaube, ich habe jetzt die Lösung: =SVERWEIS(A3;BEREICH.VERSCHIEBEN(C1;VERGLEICH(A2;C2:C8;0);1;ZÄHLENWENN(C2:C8;A2);2);2;1) bzw. =INDEX(BEREICH.VERSCHIEBEN(C1;VERGLEICH(A2;C2:C8;0);2;ZÄHLENWENN(C2:C8;A2);1);VERGLEICH(A3;BEREICH.VERSCHIEBEN(C1;VERGLEICH(A2;C2:C8;0);1;ZÄHLENWENN(C2:C8;A2);1);1)) liefern zuverlässige Ergebnisse für Kriterium 1 = Suchwert aus Zelle A2, Kriterium 2 >= Suchwert aus Zelle A3. Wenn Kriterium 2 <= Suchwert aus Zelle A3 sein soll, wird es etwas komplizierter, aber es klappt auch. Ich habe zwar das dumme Gefühl, dass ich einen Hut mit einem Kran aufsetze, aber es klappt. Die Formeln habe ich mal in eine Beispieldatei gepackt (Endung TXT durch XLSX ersetzen). [Diese Nachricht wurde von Fanny CAD am 21. Jan. 2015 editiert.] Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |