Beiträge zum Thread: Sverweis

Hallo,

wenn die Daten immer so vorliegen reicht im Blatt "Ausgabe" in Zelle J3:=cnc!B2:B28
Das funktioniert ab Office 2021.

Gruß Uwe

hallo,

vielen dank für deine antwort.
die daten liegen so nicht immer vor, ich hatte mich hier lediglich auf einen ausschnitt bezogen, um mein problem zu verdeutlichen.
deine formel kann ich leider nicht verwenden, da falsche ergebnisse angezeigt werden.

in der datei anbei ist dargestellt, wie die daten "immer" aussehen. ich habe lediglich in beiden reitern die spalte a sortiert.

evtl. kannst du mir dennoch helfen, eine lösung zu finden?

lg


https://www.herber.de/bbs/user/180779.xlsm

Hallo,

wenn das Suchkriterium mehrfach vorliegt, was soll dann das korrekte Ergebnis sein?
Erster, zweiter, ... letzter Treffer, oder gar alle?

Gruß Sigi

ich habe schon div. hilfsspalte-lösungen versucht, komme nur leider nicht weiter.
dachte, dass evtl. hier jemand eine lösung hat, wie mittels hilfssplate vorgegangen werden kann.

Hallo,

auf meine Frage gehst du gar nicht ein. Beantworte doch meine Frage:
Wenn das Suchkriterium mehrfach vorliegt, was soll dann das korrekte Ergebnis sein?
Erster, zweiter, ... letzter Treffer, oder gar alle?

Gruß Sigi

ich hatte gehofft, dass es eine lösung gibt (mittels hilfsspalte o.ä.), die anhand der anzahl der selben suchkriterien, das ergebnis so anzeigt, dass alle werte gelistet werden, die in der suchmatrix zu dem suchkriterium vorhanden sind

Hi,

alle? Dann s. Mappe.
https://www.herber.de/bbs/user/180780.xlsm

Gruß Sigi

vielen dank für ihre lösung :-)

Hallo mrs.bond,

das geht auch ohne Vba: https://www.herber.de/bbs/user/180782.xlsx

Gruß von Luschi
aus klein-Paris

Hallo Excel-Fan's,

und hier eine 1-Formel-Lösung für das Problem:

=LET(

  k;ausgabe!$A$3:$A$29;

  j;NACHZEILE(k;LAMBDA(jj;LET(

     b;ZÄHLENWENN(INDEX(k;1):jj;jj);

     c;AGGREGAT(15;6;ZEILE(cnc!$A$2:$A$28)/(cnc!$A$2:$A$28=jj);b);

     d;INDEX(cnc!$B$1:$B$28;c);d)));

j)

Gruß von Luschi
aus klein-Paris

Hallo Luschi,

... ich habe mich heute erstmals mit NACHZEILE() und LAMBDA() bekannt gemacht und das ausschließlich über Deine Lösungsformel (die MS-Hilfe hierzu erscheint mir als schlicht zu vergessen).

Schade, dass ich nicht eher die Möglichkeit hatte und wohl auch in der nächste Zeit wieder kaum habe. Es sind ja wirklich faszinierende Möglichkeiten, die mit den (für mich) neuen XL365er Funktionen geboten werden und die sich mir heute wohl nur zu einem kleinen Bruchteil erschlossen haben.

Auf dieser meiner rudimentären Grundlage, würde mein momentaner Vorschlag für den TE wäre folgender:
Den Datenbereich ausgabe!A2:J28 als intelligente Tabelle umwandeln (ich hab diese nachfolgend _AUS benannt). Ebenso cnc!A1:B28 als bei mir: _CNC

Dann folgende Spill-Formel in Zeile3:

=LET(b;INDEX(_AUS;;1);c;INDEX(_CNC;;1);a;NACHZEILE(b;LAMBDA(x;LET(b;ZÄHLENWENN(INDEX(b;1):x;x);d;AGGREGAT(15;6;ZEILE(c)/(c=x);b);e;WENNFEHLER(INDEX(cnc!B:B;d);"");e)));a)

(Achtung, wenn diese unmittelbar in J3 neben der intelligenten Formel eingesetzt wird,wird die Spalte zunächst der _AUS eingegliedert, womit die Spillformel offensichtlich ein Problem hat. Man kann aber einfach verhindern, das die Spill-Formel in _AUS integriert wird)

Meine Formel fängt nicht nur Fehlerwerte ab (wenn es z.B. in _CNC weniger Datenwerte mit der gleichen internen Nr. gibt als in _AUS) sondern sie paßt sich auch automatisch an den jeweiligen Datenbestand in _AUS und _CNC an. Natürlich müßte noch eine kleine Formelanpassung noch vorgenommen werden, wenn in den beiden Datentabellen andere Spalten noch dazukommen.

Deine äußere Formeldarstellungsstruktur würde ich persönlich nicht als notwendig erachten.

Gruß Werner
.. , - ...
aus C the unseen

Hallo Luschi,

... würde ich einfach in J3 folgende verkürzte klassische Formel vorschlagen (denn mE braucht es hierzu kein LET() ), die zudem so bereits ab XL2020 einsetzbar ist und auch Fehlerwerte abfängt:

=WENNFEHLER(INDEX(cnc!B:B;AGGREGAT(15;6;ZEILE(A$2:A99)/(cnc!A$2:A99=A3);ZÄHLENWENN(A$3:A3;A3)));"")

und nach unten kopieren.

Gruß Werner
.. , - ...
aus C the unseen

Hallo Werner,

- diese Lösung: als nach unten zu kopierende Formel...
- versuche ich in E-365 zu vermeiden und verwende lieber die 1-Formel-Lösung für alle Ergebniszeilen
- leider sind Nachzeile() bzw. Nachspalte() ziemlich zickig
- und man muß innerhalt der erforderlichen Lambda-Fkt mit 'Let' doch einige Zwischenberechnungen vollziehen
- 'Let' benutze ich grundsätzlich da, wo ein Zellbereich innerhalb der Formel mehrfach auftritt
- mit der Filter()-Fkt. hat Aggregat() eigentlich seine Daseinsberechtigung verloren

Gruß von Luschi
aus klein-Paris

PS: mit den neuen Array-Funktionen in E-2024 / E-365 muß man sich einfach intensiv beschäftigen, um deren Sinn zu begreifen; nur so ein bißchen nebenbei wird das nix.

Hallo Luschi,

... wenn Du die Daseinsberechtigung von AGGREGAT() in XL365 als verloren betrachtest?
Ich hatte lediglich Deine hier eingestellte Formeln mit AGGREGAT() aus meiner Sicht etwas vereinfacht. Fehlerhaft meinerseits war lediglich, daß ich geschrieben hatte, daß meine erste Formel schon in Excel 2020 einsetzbar wäre. Es sollte da natürlich Excel 2010 geschrieben sein.

Zu Deinem pS: Ich stelle nicht in Abrede, daß ich mich den neuen Array-Funktionen in XL365 noch intensiver beschäftigen muß (was ich bei passenden Gelegenheiten auch noch tun werde) Ich kann aber aktuell nicht erkennen, daß meine Modifikation Deiner zweiten Formel nicht vertretbar sein sollte. Oder?

Gruß Werner
.. , - ...
aus C the unseen

Hallo,

oder passend den Nummernblöcken zugeordent per VBA so:

Sub Zuorden()

    Dim arrEindeutig, fund, tmp, odic As Object, z As Range, arrRoh(), arrErg(), i&, j&, k&

    Set odic = CreateObject("Scripting.Dictionary")

    For Each z In Tabelle1.Range("A8:A" & Tabelle1.Cells(Rows.Count, 1).End(xlUp).Row)

        odic(z.Value) = 1

    Next

    arrEindeutig = odic.keys

    arrRoh = Tabelle2.Range("A4").CurrentRegion.Offset(1)

    ReDim arrErg(1 To UBound(arrRoh), 1 To 1)

    

    For i = LBound(arrEindeutig) To UBound(arrEindeutig)

        fund = Application.Match(arrEindeutig(i), Tabelle2.Columns(1), 0)

        If Not IsError(fund) Then

            tmp = Evaluate("FILTER(CNC!AD4:AD10000, CNC!A4:A10000=""" & arrEindeutig(i) & """)")

            fund = Application.Match(arrEindeutig(i), Tabelle1.Columns(1), 0)

            Tabelle1.Cells(fund, 16).Resize(UBound(tmp), 1) = tmp

        End If

    Next i

End Sub

https://www.herber.de/bbs/user/180781.xlsm

Gruß Uwe

Oder so:

Sub M_snb()

  sn = Tabelle1.Cells(8, 1).CurrentRegion.Resize(, 16)

  sp = Tabelle2.Cells(4, 1).CurrentRegion.Resize(, 30)

    

  With CreateObject("scripting.dictionary")

    For j = 2 To UBound(sp)

       .Item(sp(j, 1)) = .Item(sp(j, 1)) & "|" & sp(j, 30)

    Next

    For j = 3 To UBound(sn)

      st = Split(.Item(sn(j, 1)), "|")

      For jj = 0 To UBound(st)

        If j + jj <= UBound(sn) Then If sn(j + jj, 1) = sn(j, 1) Then sn(j + jj, 16) = st(jj + 1)

      Next

      If UBound(st) > 0 Then j = j + jj - 2

    Next

  End With

    

  Tabelle1.Cells(8, 1).CurrentRegion.Resize(, 16) = sn

End Sub