Hot News:

Unser Angebot:

  Foren auf CAD.de (alle Foren)
  Excel
  Formel für Suchkriterien in zwei Spalten

Antwort erstellen  Neues Thema erstellen
CAD.de Login | Logout | Profil | Profil bearbeiten | Registrieren | Voreinstellungen | Hilfe | Suchen

Anzeige:

Darstellung des Themas zum Ausdrucken. Bitte dann die Druckfunktion des Browsers verwenden. | Suche nach Beiträgen nächster neuer Beitrag | nächster älterer Beitrag
Autor Thema:  Formel für Suchkriterien in zwei Spalten (2188 mal gelesen)
Fanny CAD
Mitglied



Sehen Sie sich das Profil von Fanny CAD an!   Senden Sie eine Private Message an Fanny CAD  Schreiben Sie einen Gästebucheintrag für Fanny CAD

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 oder löschen Sie diesen Beitrag!  <-- editieren / zitieren -->   Antwort mit Zitat in Fett Antwort mit kursivem Zitat    Unities abgeben: 1 Unity (wenig hilfreich, aber dennoch)2 Unities3 Unities4 Unities5 Unities6 Unities7 Unities8 Unities9 Unities10 Unities


Beispiel.JPG

 
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



Sehen Sie sich das Profil von Bernd P an!   Senden Sie eine Private Message an Bernd P  Schreiben Sie einen Gästebucheintrag für Bernd P

Beiträge: 3358
Registriert: 07.06.2001

erstellt am: 25. Sep. 2014 11:33    Editieren oder löschen Sie diesen Beitrag!  <-- editieren / zitieren -->   Antwort mit Zitat in Fett Antwort mit kursivem Zitat    Unities abgeben: 1 Unity (wenig hilfreich, aber dennoch)2 Unities3 Unities4 Unities5 Unities6 Unities7 Unities8 Unities9 Unities10 Unities Nur für Fanny CAD 10 Unities + Antwort hilfreich

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



Sehen Sie sich das Profil von Fanny CAD an!   Senden Sie eine Private Message an Fanny CAD  Schreiben Sie einen Gästebucheintrag für Fanny CAD

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 oder löschen Sie diesen Beitrag!  <-- editieren / zitieren -->   Antwort mit Zitat in Fett Antwort mit kursivem Zitat    Unities abgeben: 1 Unity (wenig hilfreich, aber dennoch)2 Unities3 Unities4 Unities5 Unities6 Unities7 Unities8 Unities9 Unities10 Unities

Sorry, den Anhang habe ich oben noch ergänzt.
Der genaue Link lautet: http://www.excelformeln.de/formeln.html?welcher=485
Die dort aufgeführte Formel ist etwas komplizierter, weil die Suchkriterien dort beliebig über mehrere Spalten verteilt sind.

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

Beverly
Mitglied
Dipl.-Geologe (Rentner)


Sehen Sie sich das Profil von Beverly an!   Senden Sie eine Private Message an Berverly  Schreiben Sie einen Gästebucheintrag für Berverly

Beiträge: 394
Registriert: 11.08.2007

erstellt am: 25. Sep. 2014 16:40    Editieren oder löschen Sie diesen Beitrag!  <-- editieren / zitieren -->   Antwort mit Zitat in Fett Antwort mit kursivem Zitat    Unities abgeben: 1 Unity (wenig hilfreich, aber dennoch)2 Unities3 Unities4 Unities5 Unities6 Unities7 Unities8 Unities9 Unities10 Unities Nur für Fanny CAD 10 Unities + Antwort hilfreich

Hi,

meinst du vielleicht so:

{=WENNFEHLER(INDEX(E2:E8;VERGLEICH(A2&A3;C2:C8&D2: D8;0));INDEX(E2:E8;VERGLEICH(A2&A3;C2:C8&D2: D8;1)))}

------------------
Bis später,
Karin

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

Fanny CAD
Mitglied



Sehen Sie sich das Profil von Fanny CAD an!   Senden Sie eine Private Message an Fanny CAD  Schreiben Sie einen Gästebucheintrag für Fanny CAD

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 oder löschen Sie diesen Beitrag!  <-- editieren / zitieren -->   Antwort mit Zitat in Fett Antwort mit kursivem Zitat    Unities abgeben: 1 Unity (wenig hilfreich, aber dennoch)2 Unities3 Unities4 Unities5 Unities6 Unities7 Unities8 Unities9 Unities10 Unities

Zitat:
Original erstellt von Beverly:
Hi,

meinst du vielleicht so:

{=WENNFEHLER(INDEX(E2:E8;VERGLEICH(A2&A3;C2:C8&D2: D8;0));INDEX(E2:E8;VERGLEICH(A2&A3;C2:C8&D2: D8;1)))}


Vielen Dank.

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

Fanny CAD
Mitglied



Sehen Sie sich das Profil von Fanny CAD an!   Senden Sie eine Private Message an Fanny CAD  Schreiben Sie einen Gästebucheintrag für Fanny CAD

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 oder löschen Sie diesen Beitrag!  <-- editieren / zitieren -->   Antwort mit Zitat in Fett Antwort mit kursivem Zitat    Unities abgeben: 1 Unity (wenig hilfreich, aber dennoch)2 Unities3 Unities4 Unities5 Unities6 Unities7 Unities8 Unities9 Unities10 Unities

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

Sehen Sie sich das Profil von Datenwürfel an!   Senden Sie eine Private Message an Datenwürfel  Schreiben Sie einen Gästebucheintrag für Datenwürfel

Beiträge: 3
Registriert: 09.01.2015

erstellt am: 09. Jan. 2015 14:52    Editieren oder löschen Sie diesen Beitrag!  <-- editieren / zitieren -->   Antwort mit Zitat in Fett Antwort mit kursivem Zitat    Unities abgeben: 1 Unity (wenig hilfreich, aber dennoch)2 Unities3 Unities4 Unities5 Unities6 Unities7 Unities8 Unities9 Unities10 Unities Nur für Fanny CAD 10 Unities + Antwort hilfreich

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



Sehen Sie sich das Profil von Bernd P an!   Senden Sie eine Private Message an Bernd P  Schreiben Sie einen Gästebucheintrag für Bernd P

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 oder löschen Sie diesen Beitrag!  <-- editieren / zitieren -->   Antwort mit Zitat in Fett Antwort mit kursivem Zitat    Unities abgeben: 1 Unity (wenig hilfreich, aber dennoch)2 Unities3 Unities4 Unities5 Unities6 Unities7 Unities8 Unities9 Unities10 Unities Nur für Fanny CAD 10 Unities + Antwort hilfreich

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



Sehen Sie sich das Profil von Fanny CAD an!   Senden Sie eine Private Message an Fanny CAD  Schreiben Sie einen Gästebucheintrag für Fanny CAD

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 oder löschen Sie diesen Beitrag!  <-- editieren / zitieren -->   Antwort mit Zitat in Fett Antwort mit kursivem Zitat    Unities abgeben: 1 Unity (wenig hilfreich, aber dennoch)2 Unities3 Unities4 Unities5 Unities6 Unities7 Unities8 Unities9 Unities10 Unities

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



Sehen Sie sich das Profil von Fanny CAD an!   Senden Sie eine Private Message an Fanny CAD  Schreiben Sie einen Gästebucheintrag für Fanny CAD

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 oder löschen Sie diesen Beitrag!  <-- editieren / zitieren -->   Antwort mit Zitat in Fett Antwort mit kursivem Zitat    Unities abgeben: 1 Unity (wenig hilfreich, aber dennoch)2 Unities3 Unities4 Unities5 Unities6 Unities7 Unities8 Unities9 Unities10 Unities

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

Sehen Sie sich das Profil von Datenwürfel an!   Senden Sie eine Private Message an Datenwürfel  Schreiben Sie einen Gästebucheintrag für Datenwürfel

Beiträge: 3
Registriert: 09.01.2015

erstellt am: 21. Jan. 2015 15:17    Editieren oder löschen Sie diesen Beitrag!  <-- editieren / zitieren -->   Antwort mit Zitat in Fett Antwort mit kursivem Zitat    Unities abgeben: 1 Unity (wenig hilfreich, aber dennoch)2 Unities3 Unities4 Unities5 Unities6 Unities7 Unities8 Unities9 Unities10 Unities Nur für Fanny CAD 10 Unities + Antwort hilfreich

Hallo,
Deine Kollegen können doch auch den ANKHOR benutzen 
Die Commnunity Edition ist kostenlos und auch kommerziell einsetzbar.
Platipus

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

Bernd P
Ehrenmitglied V.I.P. h.c.
cook-general



Sehen Sie sich das Profil von Bernd P an!   Senden Sie eine Private Message an Bernd P  Schreiben Sie einen Gästebucheintrag für Bernd P

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 oder löschen Sie diesen Beitrag!  <-- editieren / zitieren -->   Antwort mit Zitat in Fett Antwort mit kursivem Zitat    Unities abgeben: 1 Unity (wenig hilfreich, aber dennoch)2 Unities3 Unities4 Unities5 Unities6 Unities7 Unities8 Unities9 Unities10 Unities Nur für Fanny CAD 10 Unities + Antwort hilfreich

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

Sehen Sie sich das Profil von Datenwürfel an!   Senden Sie eine Private Message an Datenwürfel  Schreiben Sie einen Gästebucheintrag für Datenwürfel

Beiträge: 3
Registriert: 09.01.2015

erstellt am: 21. Jan. 2015 17:47    Editieren oder löschen Sie diesen Beitrag!  <-- editieren / zitieren -->   Antwort mit Zitat in Fett Antwort mit kursivem Zitat    Unities abgeben: 1 Unity (wenig hilfreich, aber dennoch)2 Unities3 Unities4 Unities5 Unities6 Unities7 Unities8 Unities9 Unities10 Unities Nur für Fanny CAD 10 Unities + Antwort hilfreich

Man kann die Leute ja nicht zu ihrem Glück zwingen 

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

Fanny CAD
Mitglied



Sehen Sie sich das Profil von Fanny CAD an!   Senden Sie eine Private Message an Fanny CAD  Schreiben Sie einen Gästebucheintrag für Fanny CAD

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 oder löschen Sie diesen Beitrag!  <-- editieren / zitieren -->   Antwort mit Zitat in Fett Antwort mit kursivem Zitat    Unities abgeben: 1 Unity (wenig hilfreich, aber dennoch)2 Unities3 Unities4 Unities5 Unities6 Unities7 Unities8 Unities9 Unities10 Unities


Beispiel.txt

 
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

Anzeige.:

Anzeige: (Infos zum Werbeplatz >>)

Darstellung des Themas zum Ausdrucken. Bitte dann die Druckfunktion des Browsers verwenden. | Suche nach Beiträgen

nächster neuerer Beitrag | nächster älterer Beitrag
Antwort erstellen


Diesen Beitrag mit Lesezeichen versehen ... | Nach anderen Beiträgen suchen | CAD.de-Newsletter

Administrative Optionen: Beitrag schliessen | Archivieren/Bewegen | Beitrag melden!

Fragen und Anregungen: Kritik-Forum | Neues aus der Community: Community-Forum

(c)2023 CAD.de | Impressum | Datenschutz