Arrayformel für Texte?

Forumthread: 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

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Benutzer

AW: Arrayformel für Texte?

04.12.2009 09:04:32

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?

04.12.2009 09:10:12

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?

04.12.2009 10:16:15

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?

04.12.2009 11:30:07

Jörg

Hallo Jogy,
auch Dir besten Dank!
Ciao Jörg

SVERWEIS2() ?

04.12.2009 09:51:48

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

	A	B	C	D	E
1		Produktbereich 1	Produktbereich 2		Produkt A
2	Produkt A	0GH	Q8U		Produktbereich 2
3	Produkt B	J6P	7B6		Q8U, Q8U, TPA, Q8U
4	Produkt A	7ZW	Q8U
5	Produkt B	NA7	PJM
6	Produkt A	KAW	TPA
7	Produkt B	94P	L3Z
8	Produkt A	1QI	Q8U
9	Produkt B	ZF2	LR6
10

Formeln der Tabelle

Zelle	Formel
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() ?

04.12.2009 10:04:52

Jörg

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

Beliebteste Forumthreads (12 Monate)

Forumthread: Arrayformel für Texte?

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Arrayformel für Texte?

Forumthread: Arrayformel für Texte?

6 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread