✔ In Excel Tabellen einer Arbeitsmappe durchsuchen

[otmar2812]

habe folgendes problem:
habe 20 tabellen Tabelle 1.....Tabelle 20 und ein summenblatt Sum. in allen tabellen gibt es auf position A1 einen kenncode (z.b. MC oder LT2). möchte jetzt auf dem summenblatt z.b. auf A5 einen dieser kenncodes eintragen und auf B5 den wert des feldes C10 von der tabelle übernehmen, die den gleichen kenncode hat. das müsste irgendetwas mit SVERWEIS sein? kann mir irgendjemand einen tip geben? muss noch dazusagen dass ich mit office für mac 2004 arbeite.
danke im voraus!

 

[tombe]

Schau mal hier. Da wird genau so ein Beispiel gezeigt.

 

[otmar2812]

danke für deine schnelle antwort! das beispiel hab ich schon gesehen, funktioniert aber nur für nebeneinanderliegende felder, oder? und angeblich nur für zahlen (das problem könnte ich umgehen). macht mich auch nervös, wenn ich die formeln nicht ganz kapier.

 

[tombe]

Wäre eine selbst erstelle Funktion für dich eine mögliche Lösung? Mit ein paar Zeile Code lässt sich das eigentlich schnell lösen. Hierzu in ein Modul folgenden Code eingeben:

Function tabverweis(wert As String)

For a% = 1 To Sheets.Count - 1
    If ActiveWorkbook.Worksheets(a%).Range("A1") = wert Then
        tabverweis = ActiveWorkbook.Worksheets(a%).Range("C10")
    End If
Next a%

End Function

Du gibst nun in die Zelle "B5" folgendes ein:

=tabverweis($A$5)

und in die Zelle "A5" gibst du dann den Kenncode ein den du suchst.

NACHTRAG: Diese Funktion darf nur in der letzten Tabelle stehen (deshalb auch Sheets.Count - 1)!! Sonst wird sie beim Durchlauf selber auch berücksichtigt. Zur Not kann man es aber auch so ändern das die Funktion im ersten Tabellenblatt verwendet werden kann.

 

[otmar2812]

bin offenbar zu blöd: wenn ich das so mache, dann kommt: fehler beim kompilieren, sub oder funktion nicht definiert. mach ich da was grundsätzlich falsch?
ps. wenns dir zu blöd wird mit mir, sags bitte!

 

[tombe]

Hi,

irgendwas macht du wohl falsch. Die Fehlermeldung sagt mir aber nicht was es ist.

Ich hänge hier mal eine Beispieldatei dran. Es gibt Tabelle1 bis Tabelle4 in diesen steht immer in der Zelle "A1" die Kennziffer und daneben in der Zelle "B1" der dazugehörende Wert.
In der Tabelle5 kannst du dann ebenfalls in der Zelle "A1" eine beliebige Kennung eingeben und es wird in Zelle "B1" der Wert angezeigt der zu dieser Kennung gehört.

Hoffe mal du kommst damit klar und verstehst wie es aufgebaut ist. Zum Betrachten des Makros einfach ALT + F11 drücken und dort das Modul1 auswählen. Da steht dann die Funktion drin.

Gruß Thomas

P.S. Außerdem warum soll es mir zu blöd sein dir zu helfen?

 

[tombe]

So und hier eine Lösung mit Excel eigenen Mitteln:

=SVERWEIS(A1;INDIREKT(WAHL(1*(ZÄHLENWENN(Tabelle1!A:A;A1)>0)+2*(ZÄHLENWENN(Tabelle2!A:A;A1)>0)+3*(ZÄHLENWENN(Tabelle3!A:A;A1)>0)+4*(ZÄHLENWENN(Tabelle4!A:A;A1)>0);"Tabelle1";"Tabelle2";"Tabelle3";"Tabelle4")&"!A:B");2;0)

SVERWEIS dürfte von der Funktion her ja klar sein. Hier wird der Inhalt der Zelle "A1" als Vorgabewert genommen.

INDIREKT wandelt einen als Text übergenenen Wert in eine Referenz innerhalb Excel um.

WAHL wählt einen Wert aus einer Liste aus.

ZÄHLENWENN zählt die Werte zusammen die einer bestimmten Vorgabe entsprechen.

In dieser Formel wird nun durch ZÄHLENWENN ermittelt ob die in Zelle "A1" eigegebene Kennziffer in Tabelle1!A:A oder Tabelle2!A:A usw. enthalten ist. Wird ein Wert gefunden so wird dieser mit 1, 2, 3, 4 multipliziert um so zu ermitteln welchen Eintrag WAHL aus der Liste "Tabelle1"; "Tabelle2"; "Tabelle3"; "Tabelle4" nehmen soll.

Der so ermittelte Wert (Text) wird dann durch INDIREKT in eine gültige Excel Referenz umgewandelt und um die Matrix ("!A:B") für den SVERWEIS erweitert. Die Zahl 2 am Schluss sagt das aus der Matrix der Wert der 2. Spalte angezeigt werden soll.

So ich hoffe ich habe es jetzt auch wirklich so erklärt wie es ist .

Du musst damit es bei dir geht alle "A1" in der Formel so ändern das es die Zelle enthält in die du die Kennziffer eingibst.
Dann musst du den ZÄHLENWENN Bereich für jede Tabelle erweitern die es bei dir gibt und auch die Zahl ergänzen die addiert wird (immer fortlaufend).
Als letztes musst du dann die Liste "Tabelle1"; "Tabelle2"; usw. noch erweitern das alles Tabellenblättern enthalten sind die es bei dir gibt (die Blattnamen sind natürlich entsprechend anzupassen).

Der Vorteil dieser Lösung ist das Excel nicht immer frägt ob das Ausführen von Makros erlaubt sein soll oder nicht.

Der Nachteil je mehr Blätter desto länger und unübersichtlicher wird die Formel.

Gruß Thomas