Microsoft Excel

Herbers Excel/VBA-Archiv

Arrayformel für Texte? | Herbers Excel-Forum


Betrifft: Arrayformel für Texte? von: Jörg
Geschrieben am: 04.12.2009 07:54:07

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

  

Betrifft: AW: Arrayformel für Texte? von: JogyB
Geschrieben am: 04.12.2009 09:04:32

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


  

Betrifft: AW: Arrayformel für Texte? von: Jörg
Geschrieben am: 04.12.2009 09:10:12

Hallo Jogy,

VBA würde ich auch nehmen, hauptsache ich kriege das Problem gelöst.

Ciao Jörg


  

Betrifft: AW: Arrayformel für Texte? von: JogyB
Geschrieben am: 04.12.2009 10:16:15

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


  

Betrifft: AW: Arrayformel für Texte? von: Jörg
Geschrieben am: 04.12.2009 11:30:07

Hallo Jogy,

auch Dir besten Dank!

Ciao Jörg


  

Betrifft: SVERWEIS2() ? von: ransi
Geschrieben am: 04.12.2009 09:51:48

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


  

Betrifft: AW: SVERWEIS2() ? von: Jörg
Geschrieben am: 04.12.2009 10:04:52

Hallo Ransi,

super, genau das, was ich gebraucht habe - vielen Dank!

Ciao Jörg


Beiträge aus den Excel-Beispielen zum Thema "Arrayformel für Texte?"