409 Aufrufe
Gefragt in Tabellenkalkulation von robbi58 Mitglied (966 Punkte)

Ein herzliches Hallo ans Forum!

Ich bin wieder mal beim Basteln, aber leider führen zwar alle Wege nach Rom, aber noch keiner zu einer Lösung.

Ich habe eine Arbeitsmappe mit aktuell 19 verschiedenen Blättern.

Das Blatt 1 (Name: Lehrer) will ich zur Suche verwenden. Das Suchkriterium ist das KollegInnenkürzel (hier HS).

Gesucht werden soll in allen anderen Blättern der Mappe entsprechend der Abfrage.
In allen Blättern befindet sich das Kürzel in Spalte B (B2:B30 - ist bei allen Blättern aber unterschiedlich).

Ausgegeben im Blatt Lehrer sollen mittels SVerweis die nebenstehenden Daten der Zellen C,D und E.

Wie gesagt, ich brauche nicht alle Wege zum Ziel, ein einziger würde mir (mit oder ohne vba) schon genügen.

Ich bedanke mich im Voraus für eure Hilfe.

LG Robert

11 Antworten

+1 Punkt
Beantwortet von m-o Profi (22.7k Punkte)
Hallo Robert,

du kannst ja mit einer einfachen SVERWEIS-Formel die Daten aus den einzelnen Blättern einlesen. Natürlich musst du für jede Spalte die Formel entsprechend anpassen und für die jeweilige Seite ebenso. Also wo ist das Problem??

Beispiel für die Suche in Tabelle2:

Formel für Fach: =SVERWEIS(B4;Tabelle2!$B$2:$E$30;2;FALSCH)

Formel für Klasse: =SVERWEIS(B4;Tabelle2!$B$2:$E$30;3;FALSCH)

Formel für Stunden: =SVERWEIS(B4;Tabelle2!$B$2:$E$30;4;FALSCH)

Für die Abfrage der Daten in Tabelle3 musst dann nur den Namen ändern:

=SVERWEIS(B4;Tabelle3!$B$2:$E$30;2;FALSCH)

usw.

Gruß

M.O.
0 Punkte
Beantwortet von robbi58 Mitglied (966 Punkte)

Hallo M.O.!

Zunächst recht herzlichen Dank für dein Feedback.

Das mit dem SVerweis war mir schon klar. Vielleicht habe ich mich etwas missverständlich ausgedrückt:
Der Suchbegriff HS kann in jeder Tabelle mehrmals (z. B. B2, B4, B12) vorkommen, in manchen Tabellen nie.
Ich möchte aber mit der Eingabe alle Einträge in allen Tabellen (sie sind nach den Fächern benannt; also z.B. mit D, M, E, BU,...) nach HS absuchen und diese dann in einer Tabelle zusammenfassen. Das Ergebnis könnte dann in etwa so aussehen!

Damit hätte ich dann einen guten Überblick.

Ich werde einfach mal weiterprobieren und versuchen, alle Daten auf ein Tabellenblatt zu bringen und diese dann zu sortieren,...
Das Problem aus meiner Sicht ist nur, dass die Blätter unterschiedlich viele Datensätze beinhalten und diese jederzeit erweitert oder aber auch vermindert werden können.

LG Robert

0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)

Hallo Robert,

das mit dem mehreren Suchergebnissen hatte ich aus deiner Anfrage so nicht herausgelesen.

Wenn du das mit Formeln lösen willst, dann schau dir z.B. das mal an: SVERWEIS mit mehreren Suchergebnissen

Du kannst ja für die Seiten entsprechend viele Zeilen anlegen und die Leerzeilen dann ggf. per Filter herausfiltern.

Gruß

M.O.

0 Punkte
Beantwortet von robbi58 Mitglied (966 Punkte)

Hallo M.O.!

Ich habe deine Anregung aufgenommen und die Lösung über das Filtern gesucht. Danke dafür. Um an mein Ziel zu kommen, habe ich zunächst alle Daten von 18 Tabellenblättern auf eine Seite übertragen. Diese stehen in Spalte A von A2:A350, allerdings befinden sich dazwischen zahlreiche Leerzeilen.

Ich habe den Link des untenstehenden Videos dazu benutzt, um mittels der Aggregat- und Finden-Funktion die „intelligente“ Tabelle namens Tab_Lehrer entsprechend dem Suchkriterium zu filtern.

Aggregat und Finden

https://www.youtube.com/watch?v=L99bbA8W0sU

So weit so gut und man möchte meinen, das Nachbauen sei eine einfache Sache. Das habe ich auch versucht und schon mehrmals kontrolliert, aber ich kann den Fehler einfach nicht finden.
Die Tabelle scheint zwar intelligent zu sein, aber so wie es aussieht nicht der Gestalter.

Die Formel in den Feldern H7:H20 lautet (wurde nach unten kopiert):

=WENNFEHLER(INDEX(Tab_Lehrer[Lehrer];AGGREGAT(15;6;(ZEILE(Tab_Lehrer[Lehrer])-1)/(FINDEN($H$3;Tab_Lehrer[Lehrer];1)>0);ZEILE()-4);1);““)

Die Daten in den Feldern I7:J20 habe ich mittels SVerweis ermittelt.

=WENNFEHLER(SVERWEIS(H7;Tab_Lehrer;2;FALSCH);"")

Folgende Fehler treten auf:

  1. Nicht alle gesuchten Daten werden aufgelistet! Es müssten 8 Daten sein!
    (der Filter zeigt immer 2 Werte weniger an!!!; hier statt 8 nur 6)
  2. Es wird offensichtlich immer dieselbe Zeile dargestellt!
  3. Der Wennfehler tritt in den Spalten H trotzdem auf!

Der Teufel steckt wahrscheinlich in einem kleinen Detail. Wo sind die digitalen Sherlock-Homes, um dieses Detail zu finden??

Danke und lG von Robert

+1 Punkt
Beantwortet von m-o Profi (22.7k Punkte)

Hallo Robert,

ohne die Tabelle kann ich leider nicht viel dazu sagen. Aber wenn immer die selbe Zeile dargestellt wird, dann stimmt irgendetwas mit dem Zeilenverweis in der Formel nicht.

Einfacher wäre es mit einem Makro wink. Du kannst das folgende Makro ja mal eine Kopie deiner Arbeitsmappe in ein allgemeines Modul kopieren. Dabei werden alle Arbeitsblätter der Arbeitsmappe durchlaufen und die gefundenen Daten werden in der Tabelle mit dem Namen "Lehrer" ab Zeile 5 aufgelistet. Daten, die ab Zeile 5 in den Spalten B bis E vorhanden sind, werden beim Start des Makros gelöscht.

Sub Suchen()
Dim i As Integer
Dim d As Integer
Dim s As Integer
Dim arrDaten As Variant
Dim lngLetzte As Long
Dim lngZeile As Long
Dim strLehrer As String

'Einfügezeile festlegen, 1 geringer, da Zeile erhöht wird
lngZeile = 4

With ThisWorkbook.Worksheets("Lehrer")
  'Name des gesuchten Lehrers einlesen
  strLehrer = .Range("B4").Value
  'alle Zeilen ab Zeile 5 löschen, dazu die letzte beschriebene Zeile in Spalte Bermitteln
  lngLetzte = .Cells(Rows.Count, 2).End(xlUp).Row
  'nun ggf. Zeilen löschen
  If lngLetzte > 4 Then .Range("B5:E" & lngLetzte).ClearContents
End With

'Alle Tabellen in der Arbeitsmappe durchlaufen
For i = 1 To ThisWorkbook.Worksheets.Count
   'Daten werden nur aus Tabellenblätter gelesen, die nicht Lehrer heißen
   If Worksheets(i).Name <> "Lehrer" Then
     With ThisWorkbook.Worksheets(i)
       'letzte beschriebene Zeile in Spalte B ermitteln
       lngLetzte = .Cells(Rows.Count, 2).End(xlUp).Row
       'Array für Daten redimensionieren
       ReDim arrDaten(lngLetzte - 1, 4)
       'Daten aus Blatt in Array einlesen
       arrDaten = .Range("B2:E" & lngLetzte)
     End With
     'nun Array durchlaufen
     For d = LBound(arrDaten, 1) To UBound(arrDaten, 1)
       'nun den Lehrer suchen
       If arrDaten(d, 1) = strLehrer Then
          'Einfügezeile erhöhen
          lngZeile = lngZeile + 1
          'Daten in das Blatt Lehrer schreiben
          For s = 1 To 4
            ThisWorkbook.Worksheets("Lehrer").Cells(lngZeile, s + 1) = arrDaten(d, s)
          Next s
       End If
     Next d
    End If
Next i

End Sub

Dadurch sparst du dir die Arbeit alle Daten in ein Arbeitsblatt kopieren zu müssen.

Gruß

M.O.

0 Punkte
Beantwortet von robbi58 Mitglied (966 Punkte)
Bearbeitet von robbi58

Hallo M.O.

Wie immer eine perfekte Lösung für das Problem.

Mit diesem Makro kann ich jetzt endlich die gewünschten Daten herausfiltern, was für die Planung eine enorme Hilfe darstellt.

Recht herzlichen Dank dafür.

Bezüglich der Lösung mit der Formel habe ich inzwischen Problem 1 und 3 lösen können, das Problem 2 allerdings nicht (obwohl ich diesmal die Formel vom Internet herunterkopiert und eingefügt habe).
Rein aus Interesse  lade ich die Dateien mal hoch, um die Fehlerfindung zu erleichtern.

Original

https://supportnet.de/forum/?qa=blob&qa_blobid=8378347889940010666

Meine Version

https://supportnet.de/forum/?qa=blob&qa_blobid=4995155980497945276

Noch einen angenehmen Resttag wünscht Robert

0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)

Hallo Robert,

das Problem bei deiner Tabelle ist, dass das Suchkriterium "AB" bei allen Datensätzen gleich ist, während in der Beispieltabelle immer ein anderes Suchkriterium vorhanden ist, bei dem nur ein Teil gleich ist.

Ersetze mal die "AB"s durch "AB1","AB2" usw. wink.

Gruß

M.O.

0 Punkte
Beantwortet von robbi58 Mitglied (966 Punkte)
Hallo M.O.

Du hast recht. Sobald ich bei den Namen unterschiedliche Ziffern hinzufüge, funktioniert es.
Danke nochmal für deine Hilfe!
LG Robert
0 Punkte
Beantwortet von robbi58 Mitglied (966 Punkte)

Hallo an die Runde!
Mir lässt das Problem keine Ruhe und versuche nun einen Umweg zu nehmen.

Im ersten Schritt habe ich die Zeilennummern unter ("Spalte1") herausgefiltert, in denen der Suchbegriff zu finden ist.

Die Formel dafür:

=AGGREGAT(15;6;ZEILE(A:A)/(FINDEN($G$2;A:A;1)>0);ZEILE()-4)

Diese habe ich dann entsprechend nach unten kopiert.

Mit dem SVerweis hat es ja bisher nicht geklappt.

Ich weiß nun die Zeilennummer und weiß auch, dass die Fächer in Spalte B zu finden sind.
Also muss in die Zelle H5 die Formel =B160 kommen.

Wie führe ich aber, da diese Tabelle ja dynamisch ist und die Zeilennummern - je nach Suchkriterium - sich ständig ändert,  die Zellen E5 (160) mit der Spalte B zusammen?

Habe alles Mögliche probiert, leider bisher ohne Erfolg.
Ich hoffe auf einen Tipp im Forum!
Danke Robert
 

0 Punkte
Beantwortet von m-o Profi (22.7k Punkte)
Hallo Robert,

da du die Zeile ja kennst, gibt es zwei Möglichkeiten:

1. Indirekt

Schreibe in die Zelle H5 die folgende Formel:

=INDIREKT("B"&E5)

oder ohne Hilfsspalte:

=INDIREKT("B"&AGGREGAT(15;6;ZEILE(A:A)/(FINDEN($G$2;A:A;1)>0);ZEILE()-4))

Für die anderen Spalten ersetzt du "B" durch "C" und "D"

Aber wenn du viele INDIREKT-Formeln in einem Buchblatt hast, kann das Berechnen der Formeln etwas länger dauern, da die Formeln mit INDIREKT bei jeder Änderung der Tabelle neu berechnet werden.

2. INDEX

Auch hier die Formel für H5

=INDEX(B:B;E5;1)

oder auch hier ohne Hilfsspalte

=INDEX(B:B;AGGREGAT(15;6;ZEILE(A:A)/(FINDEN($G$2;A:A;1)>0);ZEILE()-4);1)

Wobei B:B die Spalte angibt, die durchsucht werden soll.

Alternativ kannst du die Formel auch so verweden:

für H5: =INDEX(B:D;E5;1)

für I5: =INDEX(B:D;E5;2)

für J5: =INDEX(B:D;E5;3)

Gruß

M.O.
...