(Excel) in allen Tabellen Blätter suchen?


23.03.2020, 12:19

kurzer Überblick:

Rechts neben dem Feld "Produkt" kann der Inhalt geändert werden. In der Liste dadrunter sollen dann die Treffer ausgegeben werden. Der Aufbau der Seiten von den Kunden ist immer gleich. Es wird somit angezeigt bei welchem Kunden (also welches Tabellenblatt) und welches Quartal in der Reihe steht, wo der treffe ist.

Funktioniert mit der Formel aber bisher nur bei "Henkel"

3 Antworten

Hallo

Um dir das alles verständlich zu machen, auch die eigentlich "überflüssigen" schritte, will ich dir das ganze kurz erklären:

Aufbau deiner Tabelle:

An sich ist deine Tabelle komplett gleich geblieben, sie hat nur 3 extra Spalten bekommen, die zur Berechnung genutzt werden.

Die Spalten sind in diesem Fall, G H und I

1. Formel in Spalte G (G4):



Die Funktion macht nichts anderes als, zu überprüfen ob das Suchfeld leer ist, wenn ja bleibt das Feld leer, wenn nicht wird die SUCHEN()-Funktion auf die in der Zeile liegenden Produkt Zelle angewendet. Wenn die Funktion einen Treffer hat, wird in das Feld eine "1" eingetragen. Wenn es einen Fehler gibt, wird dieser durch die Funktion WENNFEHLER() Abgefangen und ein leeres Feld wird ausgegeben.

2. Formel in Spalte H (H4):



Gleich wie bei der vorherigen Formel, wird hier per WENN()-Abfrage überprüft, ob das Feld einen Inhalt hat oder nicht.

Solange die SUCHE() hier also keinen Treffer hat (heißt, das Feld in Spalte G ist leer = kein Suchtreffer) wird auch nichts weiteres getan, als ein leeres Feld auszugeben, wenn jedoch eine 1 in diesem Feld stehen sollte, zählt die Funktion ZÄHLENWENN() die Anzahl der 1. von der obersten Zelle, bis zur aktiven Zelle.

Dort summiert sich jetzt also die Zahl auf, bei 2 Treffern kommt eine 1 und bei 2 Treffern bekommt Treffer Nummer zwei eine 2 ... usw.

Jetzt kommen wir zu Spalte I, diese kann man sich sparen, macht das arbeiten m.M.n aber einfacher, weil man sonst mit der INDEX() Variante, einen SVERWEIS() erstellen müsste, der von rechts nach links und nicht von links nach rechts liest.

In Spalte I steht nur der gleiche Wert wie in Spalte A:



Wenn die Zelle A4 leer ist, nichts ausgeben, ansonsten den Wert aus Zelle A4 übernehmen.

Mit der SVERWEIS()-Funktion, kannst du jetzt ganz easy Pö a Pö alle Zahlen abfragen.

DU nimmst also folgende Formel:

und fügst sie in J4 ein und ziehst diese dann nach unten.

Die Funktion ZEILE()-3 bewirkt, einen Automatischen Suchbegriff zu erstellen, welcher sich beim Herunterkopieren der Formel Dynamisch hochzählt.

So kriegst du bei einem Fehler (sprich, einem Produkt das z.B. nur 5x gefunden wurde, aber auf einen 6. geprüft wurde) einfach eine leere Zelle zurück :)

Vielleicht hilft das ja dem ein oder anderen weiter!

Liebe Grüße,

iMPerFekTioN

Woher ich das weiß:Beruf – Eigenständiges lernen während und nach meiner Ausbildung

Ich habe es gerade mal getestet: Prinzipiell sähe das so aus:

{=WENNFEHLER(INDEX(Henkel:Ende!$A$4:$A$314;KGRÖSSTE((Henkel:Ende!$C$4:$C$314=$B$1)*(ZEILE(Henkel:Ende!$C$4:$C$314)-3);ZÄHLENWENN(Henkel:Ende!$C$4:$C$314;$B$1)+1-ZEILE(E1)));"")}

Aber, wie Du schon festgestellt hast und ich auch befürchtet hätte, "frisst" er das so nicht in einer Matrixformel.

Ich sehe da nur die Möglichkeit das in Hilfsspalten für jedes Blatt einzeln zu berechnen und dann die Ergebnisse in einer Hauptspalte zusammenzuführen.

Aber vielleicht hat ja ein Anderer eine Idee. Ich drücke Dir die Daumen.

P.S.: Ich verstehe übrigens nicht, was Du mit dem k-Teil Deiner Formel bezweckst (funktionieren tut sie). MEINE Standard-Formel für so eine Aufgabenstellung ist einfach:

=WENNFEHLER(INDEX(Henkel!$A$4:$A$314;KKLEINSTE(WENN(Henkel!$C$4:$C$314=$B$1;(ZEILE(Henkel!$C$4:$C$314)-3));ZEILE(A1)));"")

Woher ich das weiß:Beruf – IT-Administrator (i.R.)

P.P.S: Ich hatte das abgeschickt ohne Deine Ergänzung zu sehen.

1

Okay vielen vielen Dank für deine Hilfe.

2

Hast du evtl eine Beispieldatei? Nur mit einer von dir erserstellten Formel, ist es schwierig nachzuvollziehen was du tun willst und wo der Fehler liegt:)

Woher ich das weiß:Beruf – Eigenständiges lernen während und nach meiner Ausbildung

habe ein Bild hinzugefügt. Wie kann ich eine Datei hochladen?

1
@captlaie

Das kannst Du hier in GF nicht direkt.
Lade sie bei einem Uploas-Service hoch und stelle den Link hier ein.

1

Was möchtest Du wissen?