HERBERS Excel-Forum - das Archiv
Bewerten Sie hier bitte das Excel-Portal
Arrayformel für Texte?
Jörg

Hallo Forum
ich habe folgende Tabelle:
Produktbereich1 Produktbereich2
ProduktA test1 test2
ProduktB test3 test4
Wenn statt dem Text "test.." eine Zahl stehen würde, könnte ich Problemlos mit einer Arrayformel arbeiten
=SUMME((A2:A3="ProduktA")*(B1:C1="Produktbereich2")*B2:C3).
1. Frage:
Wie muss ich vorgehen, wenn ich als Ergebnis ProduktA/Produktbereich2 das Wort "test2" sehen möchte?
2. Frage:
Eine weitere Zeile enthält erneut ProduktA mit "test5" und "test6"
Falls in der Zeile4 erneut das ProduktA auftaucht, kann man dann auch beide Begriffe im Sinne von : test2 &" "& test6 ausweisen?
Ciao Jörg

AW: Arrayformel für Texte?
JogyB

Hi.
Nur mal vorneweg: Die Frage ist wirklich schelcht formuliert, da mußte ich zig mal überlegen, was Du eigentlich willst.
Zur 1. Frage: Klassischer Fall für SVERWEIS - und das darf mit "Excel gut" eigentlich kein Thema sein
Als Beispiel: In G2 und H2 stehen die beiden Suchwerte, in G2 das "Produkt A" und in H2 der Produktbereich. Wertebereich ist A2:C4
=SVERWEIS(G2;A2:C4;INDEX({2.3};VERGLEICH(H2;B1:C1;0)))
Zur 2. Frage: Da sehe ich jetzt nur eine VBA-Lösung, kommt die auch in Frage?
Gruss, Jogy
AW: Arrayformel für Texte?
Jörg

Hallo Jogy,
VBA würde ich auch nehmen, hauptsache ich kriege das Problem gelöst.
Ciao Jörg
AW: Arrayformel für Texte?
JogyB

Hi.
Da es jetzt eh schon fertig ist, nun meine Version (ist nur durch die Fehlerprüfungen deutlich länger):
' Hinweise ' prodDaten ist der Bereich, in dem Prosukt A, Produkt B etc steht (hier A2:A4) ' bereichDaten ist der Bereich, in dem das test1, test2 etc. steht INKL. Überschrift! ' (hier B1:C4) ' gesProd ist eine Zelle, in dem das Produkt steht, das gesucht wird (hier G2) ' gesBereich ist eine Zelle, in der gesetuchte Produktbereucg stehr (hier H2) Public Function suchVerkett(prodDaten As Range, _ bereichDaten As Range, gesProd As Range, gesBereich As Range) As Variant Dim ergSpalte As Range Dim founD As Range Dim firstAddress As String ' Ein paar Überprüfungen ' darf nicht mehr als eine Spalte sein If prodDaten.Columns.Count > 1 Then suchVerkett = CVErr(2015) Exit Function ' Muss genau eine Zeile mehr als der Suchbereich sein (eine ist die Überschrift) ElseIf bereichDaten.Rows.Count <> prodDaten.Rows.Count + 1 Then suchVerkett = CVErr(2015) Exit Function ' Bereich zusammenhängen ElseIf bereichDaten.Areas.Count <> 1 Or prodDaten.Areas.Count <> 1 Then suchVerkett = CVErr(2015) Exit Function ' darf nur eine Zelle sein ElseIf gesProd.Cells.Count > 1 Or gesBereich.Cells.Count > 1 Then suchVerkett = CVErr(2015) Exit Function End If ' Gibt es den Suchbereich überhaupt? Set ergSpalte = bereichDaten.Rows(1).Find(gesBereich.Value) If ergSpalte Is Nothing Then suchVerkett = CVErr(xlErrNA) Exit Function End If ' suchspalte definiert festlegen (nur die Spalte mit dem Suchbegriff, ' nur die Zeilen von gesbereich ohne Überschrift Set ergSpalte = Intersect(ergSpalte.EntireColumn, _ bereichDaten.Offset(1).Resize(bereichDaten.Rows.Count - 1)) With prodDaten ' Fängt nach der letzten Zelle an zu suchen, da er sonst die erste u.U. ' nicht als erstes findet Set founD = .Find(gesProd.Value, .Cells(.Cells.Count)) ' wenn nichts gefunden, dann raus If founD Is Nothing Then suchVerkett = CVErr(xlErrNA) Exit Function End If firstAddress = founD.Address Do ' Ergebnisstring aufbauen suchVerkett = suchVerkett & IIf(suchVerkett = "", "", " ") & _ ergSpalte.Cells(founD.Row - .Row + 1, 1).Value Set founD = .Find(gesProd.Value, founD) ' Abfrage nach found is nothing unnötig, er findet ja sicher was Loop Until founD.Address = firstAddress End With End Function
Gruss, Jogy
AW: Arrayformel für Texte?
Jörg

Hallo Jogy,
auch Dir besten Dank!
Ciao Jörg
SVERWEIS2() ?
ransi

Hallo Jörg
Pack diesen Code mal in Modul:
' **********************************************************************
' Modul: Sverweis_anders Typ: Allgemeines Modul
' **********************************************************************

Option Explicit

Public Function SVERWEIS2(Kriterium As String, _
    Bereich As Range, _
    SuchSpalte As Integer, _
    ErgebnissSpalte As Integer, _
    Optional Unikate As Boolean = True, _
    Optional Trenner As String = ", ") As String
Dim arrTmp
Dim L As Long
Dim Mydic As Object
arrTmp = Bereich
Set Mydic = CreateObject("Scripting.Dictionary")
If Unikate = True Then
    For L = 1 To UBound(arrTmp)
        If arrTmp(L, SuchSpalte) = Kriterium Then Mydic(arrTmp(L, ErgebnissSpalte)) = 0
    Next
    SVERWEIS2 = Join(Mydic.keys, Trenner)
    Else:
    For L = 1 To UBound(arrTmp)
        If arrTmp(L, SuchSpalte) = Kriterium Then Mydic(L) = arrTmp(L, ErgebnissSpalte)
    Next
    SVERWEIS2 = Join(Mydic.items, Trenner)
End If
End Function


Der Aufruf geht dann so:
Tabelle2

 ABCDE
1 Produktbereich 1Produktbereich 2 Produkt A
2Produkt A0GHQ8U Produktbereich 2
3Produkt BJ6P7B6 Q8U, Q8U, TPA, Q8U
4Produkt A7ZWQ8U  
5Produkt BNA7PJM  
6Produkt AKAWTPA  
7Produkt B94PL3Z  
8Produkt A1QIQ8U  
9Produkt BZF2LR6  
10     

Formeln der Tabelle
ZelleFormel
E3=sverweis2(E1;A1:C20;1;VERGLEICH(E2;A1:C1;0); FALSCH)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Erklärt wirds hier:
http://www.j-hennekes.de/1729416.htm
ransi
AW: SVERWEIS2() ?
Jörg

Hallo Ransi,
super, genau das, was ich gebraucht habe - vielen Dank!
Ciao Jörg