Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema CheckBox
BildScreenshot zu CheckBox CheckBox-Seite mit Beispielarbeitsmappe aufrufen

Index/Match mit 2 Kriterien

Betrifft: Index/Match mit 2 Kriterien von: VBA_Tester
Geschrieben am: 27.10.2014 16:34:21

Hallo liebe Forumsmitglieder,

ich habe ein kleines Problem und bekomme die Lösung einfach nicht hin.

Und zwar habe ich in Tabelle 1: Mitarbeiter (Spalte A), ihre Kunden, mit denen sie im betrachteten Zeitraum Kontakt hatten (Spalte B) und in Spalte C die Anzahl der Kontakte mit diesen Kunden.

In Tabelle 2 habe ich ebenfalls in Spalte A die Namen der Mitarbeiter in Spalte B alle Kunden dieser Mitarbeiter und in Spalte C soll die Anzahl der Kontakte mit den entsprechenden Kunden erscheinen. Bei Kunden, die in Tabelle 1 bei einem Mitarbeiter nicht auftauchen, aber in Tabelle 2 hat im betrachteten Zeitraum demnach kein Treffen stattgefunden. Ich würde gerne eine Formel bzw. gerne auch VBA-Code erstellen, die/der mir die Anzahl der Kontakte eines bestimmten Mitarbeiters auf einem bestimmten Account in Tabelle 2 überträgt.

VBA habe ich erstmal außen vorgelassen, da ich hier noch blutiger Anfänger bin. Wäre aber natürlich die schickere Lösung und soll auch der zukünftige Weg sein.

Ich habe es jetzt erstmal mit einer Excel Formel versucht.
Ich glaube auch schon die richtige gefunden zu haben undzwar "Index(Match)Kombo".
Der Match Befehl gibt mir ja immer eine Zeile zurück.
Für die Index Funktion brauche ich allerdings eine Zeilen und eine Spalten Angabe.
Die Spalte könnte man ja eigentlich auch manuell eintragen (hier Spalte 3)
Aber irgendwie bekomme ich die Syntax mit der ich die beiden Kriterien Kunde und Mitarbeiter in dieser Formel verknüpfe einfach nicht hin.

Wäre super, wenn mir jemand aus dem Forum helfen könnte.
Bei den Themen, die hier sonst so diskutiert werden, bin ich recht zuversichtlich, dass mein "Kindergartenproblem ;-)" keine Herausforderung darstellt.

Danke im Voraus

VG

https://www.herber.de/bbs/user/93401.xlsx

https://www.herber.de/bbs/user/93402.xlsx

  

Betrifft: Ja, du hast recht, schwer ist das nicht, ... von: Luc:-?
Geschrieben am: 28.10.2014 01:03:06

…„vielgrüßender (hier mal nicht VBA-)Tester“, ;-)
nur mag das „VBA“ die Nur-Formel-Freaks abgeschreckt haben und die VBA-Freaks waren enttäuscht oder allen war's tatsächlich zu einfach… ;-]
Die erforderliche einzellige MatrixFml in Datei2 lautet:
C2[:C11]: {=WENNFEHLER(INDEX([93401.xlsx]Sheet1!$C$2:$C$8;VERGLEICH(A2&B2;[93401.xlsx]Sheet1!$A$2:$A$8&[93401.xlsx]Sheet1!$B$2:$B$8;0));0)}
Dabei sollte es egal sein, ob die Kunden flfd oder pro MA durchnummeriert wdn oder ein Kunden­Name bei verschiedenen MAn auftaucht, nur fehlerfrei-eindeutig muss er sein, also ohne irgendwelche Leerzeichen davor oder dahinter (das würde die Fml verlängern!).
Morrn, Luc :-?


  

Betrifft: kommt auf den "Standpunkt" an ... von: neopa C (paneo)
Geschrieben am: 28.10.2014 08:08:14

Hallo VG,

... alternativ zur INDEX()-Matrixformel von Luc würde z.B. auch eine VERWEIS()-Formelvariante möglich sein.

In C2: =WENNFEHLER(VERWEIS(9;1/([93401.xlsx]Sheet1!A$1:A$9=A2)/([93401.xlsx]Sheet1!B$1:B$9=B2);[93401.xlsx]Sheet1!C:C);"") und nach unten kopieren.

Diese ist zwar für viele schwerer nachvollziehbar als die INDEX()-Variante, kommt aber dafür ohne den spez. Formeleingabeabschluß aus, der zu der {}-Anzeige führt und sie somit sofort erkennbar als eine spez. Matrixformel ausweist. Die VERWEIS()-Formel bedarf dem nicht (ist aber trotzdem auch eine).

Es gibt nach andere Lösungsformeln für Deine Problemstellung, allerdings kann ich Dich beruhigen, alle derartige Formeln fließen auch nicht allen Helfer hier leicht in die Tastatur. Sie sind also nicht als "Kindergartenproblem" zu bezeichnen, wie Du meintest.

Gruß Werner
.. , - ...


  

Betrifft: AW: kommt auf den "Standpunkt" an ... von: VBA_Tester
Geschrieben am: 28.10.2014 09:04:08

Super schonmal vielen Dank euch beiden!!
Da das ganze eine wöchentlich wiederkehrende Aufgabe ist wäre es ideal, wenn es noch eine VBA Lösung gäbe. Bitte liebe VBA-Tüftler please help!!


  

Betrifft: MxFml kommt auch auf d."Standpunkt" an, ... von: Luc:-?
Geschrieben am: 28.10.2014 17:03:36

…Werner,
nämlich, ob man mit MatrixFml alle eine Matrix verarbeitenden Fktt oder nur die einer besonderen Kenn­zeichnung bedürftigen meint. IdR ist Letzteres gemeint, dem auch andere Calc-Pgmm wie bspw LO/OO folgen, indem hier eine CheckBox im dortigen FmlAssi angehakt wdn muss, sonst ist es stets eine einfache Fml, egal ob matrix­verarbei­tend oder nicht.
Es geht hierbei auch vornehmlich nicht um Zell­Bereiche, sondern um als Argument übergebene Daten­felder, die aus der Berechnung von Ausdrücken (als Argument einer über­geordneten Fkt) resultieren. Ohne die MxFml­Kennung würde nur das 1.Element eines solchen DFeldes verwendet wdn, nicht das ganze, wie man auch sehr schön bei SUMMENPRODUKT erkennen kann.
Gruß, Luc :-?

Besser informiert mit …


  

Betrifft: AW: Index/Match mit 2 Kriterien von: Daniel
Geschrieben am: 28.10.2014 09:39:51

Hi
da es sich bei den Ausgewerteten Daten um Zahlen handelt (Anzahl der Gespräche) könntest du hier auch einfach mit SummeWenns arbeiten.

dazu folgende Formel in Tabelle2 Zelle C2 (und dann nach unten kopieren)

=SummeWenns(Tabelle1!C:C;Tabelle1!A:A;A2;Tabelle1!B:B;B2)
als VBA-Lösung würde ich erstmal nur ein Makro schreiben, welches die Formel einträgt und durch ihren Wert ersetzt.
VBA-Fortgeschrittene setzen hier das Dictionary-Objekt ein, um erstmal die Werte aus der Tabelle1 zu lesen und dann in die Tabelle2 einzutragen.

anbei mal für beide Varianten die Makrolösung, passend für deine beiden Bespieldateien:

eintragen der Formel
Sub AnzahlGesprächeFormel()
With Workbooks("93402.xlsx").Sheets(1)
    With .Range(.Cells(2, 3), .Cells(1, 1).End(xlDown).Offset(0, 2))
        .FormulaR1C1 = _
           "=SUMIFS([93401.xlsx]Sheet1!C3,[93401.xlsx]Sheet1!C1,RC1,[93401.xlsx]Sheet1!C2,RC2)"
        .Formula = .Value
    End With
End With
End Sub

Auswertung über das Dictionary
Sub AnzahlGesprächeDictionay()
Dim arr1
Dim arr2
Dim z As Long
Dim dicNameAccount As Object
Set dicNameAccount = CreateObject("Scripting.Dictionary")

arr1 = Workbooks("93401.xlsx").Sheets(1).Cells(1, 1).CurrentRegion.Value
arr2 = Workbooks("93402.xlsx").Sheets(1).Cells(1, 1).CurrentRegion.Value

For z = 2 To UBound(arr1, 1)
    dicNameAccount(arr1(z, 1) & "-" & arr1(z, 2)) = _
        dicNameAccount(arr1(z, 1) & "-" & arr1(z, 2)) + arr1(z, 3)
Next

For z = 2 To UBound(arr2, 1)
    arr2(z, 3) = dicNameAccount(arr2(z, 1) & "-" & arr2(z, 2))
Next

 Workbooks("93402.xlsx").Sheets(1).Cells(1, 1).CurrentRegion.Value = arr2
End Sub

Gruß Daniel


  

Betrifft: AW: Index/Match mit 2 Kriterien von: VBA_Tester
Geschrieben am: 28.10.2014 11:17:12

Hi Daniel, vielen Dank für deine Antwort.

Habe jetzt erstmal deine Formel ausprobiert. Sie klappt auch mit den hochgeladenen Beispieldateien.
Wenn ich sie dann allerdings auf meine richtigen Dateien anwenden möchte bekomme ich nur 0 Werte angezeigt obwohl welche vorhanden sind für die Kombination Kunde(Account) und Mitarbeiter(Name).

Ich benutze eine englishe Version desshalb leicht abgeänderte Formel aber wie gesagt sie funktioniert wenn ich sie in die Beispieldatei eintrage. Kann das was damit zu tun haben, dass ich die Daten aus eine Pivot stammen? Hier mal die Formel, wie ich sie für meine echten dateien eingetragen habe:

=SUMIFS('[Tabelle1.xlsx]Sheet1 View-Pivot'!G:G,'[Tabelle1.xlsx]Sheet1 View-Pivot'!A:A,A9,'[Tabelle1.xlsx]Sheet1 View-Pivot'!B:B,B9)

Danke für die Hilfe!!!


  

Betrifft: AW: Index/Match mit 2 Kriterien von: Daniel
Geschrieben am: 28.10.2014 11:55:27

Hi
kann sein.
wenn du mir Beispieldateien zeigst, dann erstelle ich die Lösung grundsätzlich für diese Beispieldateien.
Wenn deine Originaldatei von der Beispieldatei abweicht und deswegen meine Lösung nicht funktioniert, dann ist das DEIN Problem, denn ich kenne deine Originaldatei ja nicht und kann mich nur an dem orientieren, was du mir zeigst.

Gruß Daniel


  

Betrifft: AW: Index/Match mit 2 Kriterien von: VBA_Tester
Geschrieben am: 28.10.2014 12:45:11

Hi Daniel vielleicht ist das falsch angekommen.... sollte auf gar keinen Fall Kritik sein deine Lösung ist super!! Das ganze war ja auch schon vorher MEIN PROBLEM und ich finde es uneingeschränkt klasse, dass es Leute wie dich gibt, die einem Einsteiger ihre kompetente Hilfe anbieten. Ich habe auch nochmal rumgegoogelt aber keinen Anhaltspunkt gefunden, warum deine Formel nicht funktioniert, wenn ich die Daten aus einer Pivot rausziehe. Hat vielleicht sonst jemand eine Idee woran das liegen könnte oder habe ich vielleicht doch einen Syntaktischen Fehler in meiner Formel?

=SUMIFS('[Tabelle1.xlsx]Sheet1 View-Pivot'!G:G,'[Tabelle1.xlsx]Sheet1 View-Pivot'!A:A,A9,'[Tabelle1.xlsx]Sheet1 View-Pivot'!B:B,B9)

Danke für eure Hilfe!


  

Betrifft: AW: Index/Match mit 2 Kriterien von: Daniel
Geschrieben am: 28.10.2014 13:09:30

Hi
ich hab das auch nicht als Kritik verstanden.
Es muss dir nur klar sein, dass ich eine Lösung immer nur für die Datei schreiben kann, die du mir zeigst und ich dir keine Antworten zu Dateien geben kann, die ich nicht kenne.
mit den Auswertungen von Pivottabellen kenne ich mich auch nicht aus.
Warum machst du die Auswertung nicht direkt mit den Ausgangsdaten für die Pivottabelle?

Gruß Daniel


  

Betrifft: AW: Index/Match mit 2 Kriterien von: VBA_Tester
Geschrieben am: 28.10.2014 13:06:42

Habs war mein Fehler!!
Nochmal Danke an alle und sorry für meinen Verpeiler. Ich hatte gedacht, dass sich A2 und B2 auch auf Tabelle 1 beziehen (A:A,A2 und B:B,B2)


  

Betrifft: AW: Index/Match mit 2 Kriterien von: VBA_Tester
Geschrieben am: 28.10.2014 14:30:03

Hallo Daniel,
kannst du mir vielleicht noch ein Paar Kommentare dranschreiben, damit ich die Formel meiner Datei anpassen kann? Ich will ja auch nicht immer so dumm bleiben und das auch mal selbst hin bekommen, damit ich euch nicht mehr fragen muss oder auch selbst anderen Antworten geben kann.
VG

Hier meine Formel aus der Original Datei: Falls die Erklärung zu aufwendig ist wäre es die Krönung deiner heutigen Heldentat, (nicht veräppelnd gemeint für mich wirklich eine Heldentat :-))wenn du mir den Code noch anpassen könntest.

=SUMIFS
('[Tabelle1.xlsx]Sheet1 View-Pivot'!G:G,
'[Tabelle1.xlsx]Sheet1 View-Pivot'!A:A,H2, (H2 logischerweise aus Tabelle2)
'[Tabelle1.xlsx]Sheet1 View-Pivot'!B:B,J2) (J2 auch Tabelle2)

Sub AnzahlGesprächeFormel()'Logischer Weise eigenen Namen wählen    
With Workbooks("93402.xlsx").Sheets(1)'Meine Datei statt93492.xlsx nehmen und wenn für alle  _
Sheets gültig .Sheets (1) weglassen        
With .Range(.Cells(2, 3), .Cells(1, 1).End(xlDown).Offset(0, 2))'(.Cells(2,3)) heißt  _
wahrscheinlich Zeile 2 Spalte 3 (die Stelle, an der die Formel einfügt werden soll.) Den Rest der Zeile kann ich nicht nachvollziehen            
.FormulaR1C1 = _ 'was bewirkt das?
               "=SUMIFS([93401.xlsx]Sheet1!C3,[93401.xlsx]Sheet1!C1,RC1,[93401.xlsx]Sheet1!C2, _
RC2)" 'Wie kann ich die logic dieser Formel nachvollziehen? Hat keine Ähnlichkeit mehr mit der Summewenns Formel aus Excel
            .Formula = .Value 'was sagt das aus?
        End With
    End With
    End Sub



  

Betrifft: Literaturempfehlung VBA von: VBA_Tester
Geschrieben am: 28.10.2014 14:45:20

Hallo Experten,
wie es aussieht, werde ich um eine ausgiebige Theorie Sitzung nicht drumrum kommen.
Könnt ihr mir gute Bücher oder WebSeiten empfehlen um mir VBA beizubringen. Ich weis nicht, ob ich alle Funktionen auswendig wissen muss...vielleicht könnt ihr mir ja sagen wie und wo ihr Sachen nachschlagt. Im Studium geht es immer mehr darum, zu wissen wo man eine Information findet...
Neben den Funktionen von VBA ist für mich insbesondere der Aufbau und die Strukturierung meines VBA- bzw. Makrogesamtgerüsts wichtig. Also best Practise Vorgehen mit Konventionen, Speicherstruktur, Einsatz von Variablen, Konstanten USW.

Danke für die eure Hilfe


  

Betrifft: AW: Literaturempfehlung VBA von: Mullit
Geschrieben am: 28.10.2014 20:11:37

Hallo,

null problemo...

Gruß,


  

Betrifft: AW: Literaturempfehlung VBA von: VBA_Tester
Geschrieben am: 29.10.2014 07:49:16

Hi ist meine Frage unangebracht oder ist es generell nicht gewünscht, dass sich die Fragesteller weiterbilden? Bei meiner anderen offenen Frage geht es ja auch darum zu verstehen. Falls es nur an der Formulierung meiner Frage liegt sagt mir bitte, was ich besser machen kann.
Gruß Tobi


  

Betrifft: Quatsch! Das war ein Link! Klick drauf! orT von: Luc:-?
Geschrieben am: 29.10.2014 12:09:41

Gruß, Luc :-?


  

Betrifft: AW: Literaturempfehlung VBA von: VBA_Tester
Geschrieben am: 29.10.2014 17:56:50

Oh man das war jetzt peinlich :-)


  

Betrifft: Na, denn iss ja jut...! ;-] owT von: Luc:-?
Geschrieben am: 29.10.2014 20:00:02

:-?


 

Beiträge aus den Excel-Beispielen zum Thema "Index/Match mit 2 Kriterien"