Microsoft Excel

Herbers Excel/VBA-Archiv

Verkettungsfunktion über Kriterium

Betrifft: Verkettungsfunktion über Kriterium von: jens
Geschrieben am: 17.03.2016 15:38:47

Hallo,

ich habe hier eine Verkettungsfunktion.

Function Kette2(Vorgabe As Range, Ketten As Range)
 
 Dim Zelle As Range
 Dim T As String
 Application.Volatile
 For Each Zelle In Vorgabe
     T = Zelle.Value
     Select Case T
         Case "leer"
             Kette2 = Kette2 & " "
         Case ""
             Kette2 = Kette2 & ""
         Case Else
             If T Like "[A-Z]" Or T Like "[A-Z][A-Z]" Then
                 Kette2 = Kette2 & Ketten.Worksheet.Cells(Ketten.Row, T).Text
             Else
                 Kette2 = Kette2 & T
         End If
     End Select
 Next
 
 End Function

Mit dieser kann ich Spalten verketten unter Vorgabe der Spalte in Zellen.

Funktioniert auch super.

Aber wie baue ich hier noch eine Vorgabe ein.

Sprich wenn Vorgabe1 dann soll diese Vorschrift1 genommen werden.
Wenn Vorgabe2 dann soll die Vorschrift 2 genommen werden.

  

Betrifft: AW: Verkettungsfunktion über Kriterium von: jens
Geschrieben am: 17.03.2016 16:03:43

Dazu noch eine BSP Datei mit der umgesetzten Funktion in der Zelle Q3.
https://www.herber.de/bbs/user/104424.xlsm

Aber wie schaffe ich die Abhängigkeit entsprechende der Vorgabe in Zelle E3???


  

Betrifft: Warum die ganze Mühe mit einer für eine ... von: Luc:-?
Geschrieben am: 17.03.2016 18:24:34

…Anwendung von Ausdrücken als Argument ungeeigneten UDF*, Jens,
wenn doch schon eine normale und wesentlich flexiblere Fml reicht?
Q3[:…]:=INDIREKT(INDEX(Tabelle2!A1:B14;VERGLEICH(E3;Tabelle2!A1:A14;0)+1;1)&ZEILE())&INDIREKT(INDEX(Tabelle2!A1:B14;VERGLEICH(E3;Tabelle2!A1:A14;0)+1;2)&ZEILE())
Ansonsten kannst du ja auch mal nach VJoin im Archiv RECHERCHiEren! ;-)
* Die UDF-Argumente sind datentyp-beschränkt deklariert, ein häufig zu findender Fehler, speziell bei Access u.Ä. gewohnten Pgmmierern. Dadurch kann eine solche UDF nicht wie das Gros der Xl-StandardFktt, sondern eher nur wie ~WENNs & Co benutzt wdn!
Feedback nicht unerwünscht! Gruß, Luc :-?

Besser informiert mit …


  

Betrifft: AW: Verkettungsfunktion über Kriterium von: Michael
Geschrieben am: 17.03.2016 17:28:53

Hallo Jens,

ich habe zwei Änderungen an Deiner Datei vorgenommen:
a) Tabelle 2 umsortiert, damit die Spaltenbuchstaben mit SVerweis verfügbar gemacht werden und
b) eine Function Kette3 angelegt, die als Parameter die beiden Spaltenbuchstaben "frißt":

Function Kette3(SpLi As String, SpRe As String, Ketten As Range)
 
 Dim Zelle As Range
 Dim T(1 To 2) As String
 Dim i As Long
 'Application.Volatile
   T(1) = SpLi: T(2) = SpRe
   
   For i = 1 To 2
     Select Case T(i)
         Case "leer"
             Kette3 = Kette3 & " "
         Case ""
             Kette3 = Kette3 & ""
         Case Else
             If T(i) Like "[A-Z]" Or T(i) Like "[A-Z][A-Z]" Then
                 Kette3 = Kette3 & Ketten.Worksheet.Cells(Ketten.Row, T(i)).Text
             Else
                 Kette3 = Kette3 & T(i)
         End If
     End Select
   Next
 
End Function
Aufgerufen dann in Zeile 5 etwa mit:
=Kette3(SVERWEIS(E5;Tabelle2!$D$1:$F$5;2);SVERWEIS(E5;Tabelle2!$D$1:$F$5;3);A5:P5)
Das funktioniert solange gut, wie die beiden Spaltenbuchstaben nebeneinander liegen (das tun sie ja in allen Beispielen). Hier eingesetzt in Zeile 5...

Eigentlich wäre es noch viel einfacher, nur EINE Angabe zu verwenden, das skizziere ich noch schnell:
Function Kette4(Spalten As String, Ketten As Range)
 
 Dim Zelle As Range
 Dim T As Variant
 Dim i As Long
 'Application.Volatile
   T = Split(Spalten, ",")
   
   For i = LBound(T) To UBound(T)
     Select Case T(i)
         Case "leer"
             Kette4 = Kette4 & " "
         Case ""
             Kette4 = Kette4 & ""
         Case Else
             If T(i) Like "[A-Z]" Or T(i) Like "[A-Z][A-Z]" Then
                 Kette4 = Kette4 & Ketten.Worksheet.Cells(Ketten.Row, T(i)).Text
             Else
                 Kette4 = Kette4 & T(i)
         End If
     End Select
   Next
 
End Function

Aufruf mit =Kette4(SVERWEIS(E6;Tabelle2!$H$1:$I$5;2);A6:P6)
Im Beispiel (Zeile 6) steht "Vorgabe 3" und SVerweis gibt den String "K,F,D" zurück - so kannst Du beliebig viele Spalten verwenden. Ein "von-bis" könnte man auch programmieren, aber das scheint ja erst mal nicht gefragt zu sein.

Die Datei: https://www.herber.de/bbs/user/104427.xlsm

Ach, ob das app.volatile sein muß... ich hab's auskommentiert.

Schöne Grüße,

Michael


  

Betrifft: AW: Verkettungsfunktion über Kriterium von: Jens
Geschrieben am: 18.03.2016 07:27:41

@ Michael.

Super Funkioniert zum Großteil schon sehr gut.

Eine Anmerkung hätte ich noch.

Bei Kette 3 gefällt mir sehr gut, dass wenn ich z.b in Vorgabe 1 schreibe dass Spalte A verketten werden soll und z.b danach ein Punk "." dann wird dies auch gemacht.

So Wäre es optimal. Aber es müsste erweiterbar sein. Sodass eine beliebige Anzahl an Spalten verknüft werden kann.

Ebenso sollte die Vorgabe der Spalten und auch Sonderzeichen oder Wörter immer jeweils durch eine Zelle erfolgen und nicht wie in deiner Kette4 über die Angabe des Spaltenbuchstaben in einer Zelle.

Ist diese Anpassung noch möglich?
(Siehe hierzu den Link)
https://www.herber.de/bbs/user/104437.xlsm


  

Betrifft: AW: Verkettungsfunktion über Kriterium von: Michael
Geschrieben am: 18.03.2016 11:31:46

Hallo Jens,

die Variante 4 gefällt Dir zwar auf Anhieb nicht, aber ich habe sie etwas erweitert auf Version 5:

Function Kette5(Spalten As String, Ketten As Range, SplitChar As String)
 
 Dim Zelle As Range
 Dim T As Variant
 Dim i As Long
 'Application.Volatile
   T = Split(Spalten, SplitChar)
   
   For i = LBound(T) To UBound(T)
     Select Case T(i)
         Case "leer"
             Kette5 = Kette5 & " "
         Case ""
             Kette5 = Kette5 & ""
         Case Else
             If T(i) Like "[A-Z]" Or T(i) Like "[A-Z][A-Z]" Then
                 Kette5 = Kette5 & Ketten.Worksheet.Cells(Ketten.Row, T(i)).Text
             Else
                 Kette5 = Kette5 & T(i)
         End If
     End Select
   Next
 
End Function
Aufgerufen wird sie z.B. mit =Kette5(SVERWEIS(E7;Tabelle2!$H$1:$I$5;2);A7:P7;"|")

Als letzter Parameter wird das Trennzeichen eingegeben, in dem Fall der senkrechte Strich (altGr+Taste mit größer/kleiner-Zeichen).

Bei Eingabe von A|, |B|C|D|. als Vorgabe kommt der Text heraus: Hallo, hier ist User.

So IST das beliebig erweiterbar, und Du kannst auch mehrere Zeichen zwischen den Trennzeichen verwenden, wie Komma+Leerzeichen zwischen A und B.

Mit "Excel gut" sollte es Dir ein Leichtes sein, die gewünschten "variablen Spalten" selbst zu einem String mit dieser Syntax zu verketten - das geht mit Excel-Mitteln, ohne das Makro zu ändern.

Schöne Grüße,

Michael


  

Betrifft: AW: Verkettungsfunktion über Kriterium von: jens
Geschrieben am: 18.03.2016 11:47:29

Hallo,

danke für die Hilfe.

Mein Problem ist blos, das die kette2 mit den Spalten arbeitet und ich nun zusätzlich noch eine andere Art einbauen muss. Bzw. Es werden an der Datei Kollegen auch arbeiten und das muss ich zusätzlich noch eine Vorschrift schulen. Von daher wir es für andere noch schwieriger.

Verstehe mich nicht falsch ich finde die Lösung super. Aber der Einfachheithalber für die Kollegen, wäre es mir lieber wenn ich nur eine Variante hätte.
Ist es daher irgendwie möglich, dass Ganze auf Spalten aufzuteilen?
Also so wie ich das schon angesprochen habe?


Gruß


  

Betrifft: Schulen? Warum dann nicht mit StandardFktt ... von: Luc:-?
Geschrieben am: 18.03.2016 13:57:50

…statt UDF! Schönen Dank für dein Desinteresse! :-[
Luc :-?


  

Betrifft: Du tust Dir schon schwer, oder? von: Michael
Geschrieben am: 19.03.2016 14:16:45

Hi Jens,

Du brauchst ja nur EINE zu verweden, nämlich Kette5, und zwar so:
https://www.herber.de/bbs/user/104468.xlsm

Kette5 funktioniert sowieso mit beliebig vielen Parametern, und die Formel in Tabelle2 verarbeitet vorläufig bis zu 10 Eingabespalten.

Wozu Schulung? Kette5 brauchst Du nur überall in Spalte Q zu verwenden...

Übrigens funktioniert Luc:-?s Formel für die ursprüngliche Frage ausgezeichnet - wie zu erwarten.

Schöne Grüße,

Michael


 

Beiträge aus den Excel-Beispielen zum Thema "Verkettungsfunktion über Kriterium"