Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: Indirekt mit Index(Vergleich()) vernküpfen

Indirekt mit Index(Vergleich()) vernküpfen
07.02.2019 09:42:30
Stefan
Hallo zusammen
Ich bin eine Rezeptesammlung für die Lagerküche am schreiben.
Jedes Rezept steht in einem eigenen Tabellenblatt. In einem weiteren Blatt steht eine Liste mit den einzelnen Rezepten.
Für die Einkaufsliste möchte ich gerne die Zutatenmengen aus den einzelnen Rezepten auslesen und addieren.
Auslesen möchte ich mit INDEX und VERGLEICH und dabei anhand des Inhaltsverzeichnis mit INDIREKT die einzelnen Blätter durchlaufen. Das ganze müsste anschliessen aufsummiert werden...
Die Aufgaben einzeln kriege ich hin, also zum Beispiel:

{=SUMME(SUMMEWENN(INDIREKT("'"&Inhaltsverzeichnis!B$3:B$4&"'!B8");">0"}))

Die B8-Zellen der Rezepte werden sauber addiert, jedoch kann ich da nicht nach Zutat suchen.
So weit bin ich gekommen (ohne Umbrüche):

{=SUMME(SUMMEWENN(INDEX(INDIREKT("'"&Inhaltsverzeichnis!B$3:B$4&"'!B$8:B$37");
VERGLEICH(A8;   INDIREKT("'"&Inhaltsverzeichnis!B$3:B$4&"'!D$8:D$37");0));">0"))}

Dabei wird mir jedoch nur die Zutat des ersten Rezepts zurückgegeben und nichts addiert.
Hat jemand eine Idee?
Herzlichen Dank und Grüsse aus der Schweiz
Stefan
Anzeige

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Dein INDEX-Frmteil übergibt keinen Bereich ...
07.02.2019 10:25:37
neopa
Hallo Stefan,
... sondern lediglich die erste Zelladresse. Um es Dir korrekt aufzuzeigen solltest Du mal zumindest einen Auszug aus Deiner Arbeitsmappe als xlsx-Datei hier einstellen.
Gruß Werner
.. , - ...
AW: Dein INDEX-Frmteil übergibt keinen Bereich ...
07.02.2019 10:54:21
Stefan
Ein Auszug der Datei mit zwei Rezepten ist unter https://www.herber.de/bbs/user/127476.xlsx
verfügbar.
Anzeige
AW: nachgefragt ...
07.02.2019 11:18:29
neopa
Hallo Stefan,
... stehen Deine "Waren" wie in der Beispieldatei immer in der gleichen Zeile des jeweiligen Menüs? Wenn noch nicht, lässt sich das sicher organisieren. Evtl. Waren die für ein Menü nicht benötigt werden könnten ja dort einfach mit dem Autofilter ausgefiltert werden.
Wie viele Menüs (bei Dir verschiedene Tabellenblätter) sind denn ca. max vorhanden?
Gruß Werner
.. , - ...
Anzeige
AW: nachgefragt ...
07.02.2019 11:31:21
Stefan
Im Moment sind rund 70 Rezepte vorgesehen, mit der Möglichkeit zur Erweiterung, deshalb möchte ich das ganze automatisch übers Inhaltsverzeichnis lösen, dass ich nicht immer händisch jedes Tabellenblatt neu in die Einkaufsliste übertragen muss.
In der aktuellen Version werden es gut 140 Zutaten, wenn es geht, möchte ich die lieber nicht immer an derselben Stelle schreiben, weshalb ich den Weg über INDEX und VERGLEICH gewählt habe. Könnte aber eine "Notlösung" sein, welche jedoch das Dokument unnötig aufbläst.
Anzeige
AW: das wäre mE weder eine "Notlösung" ...
07.02.2019 13:39:01
neopa
Hallo Stefan,
... noch eine "Aufblähung" wieso soll Deiner Meinung nach die Datei aufgebläht werden, wenn diese gleiche Informationen an gleicher Stelle anordnet. Du kannst ja an den Stellen wo in den Rezepten bestimmte Zutaten nicht erforderlich sind, einfach leere Zellen lassen und dies Zeilen im jeweiligen Tabellenblatt der besseren Übersicht halber wie bereits geschrieben ausfiltern.
Eine solche gleiche Datenstruktur je Menü vereinfacht insbesondere die Auswertung wie Du sie anstrebst, weil dann eine einfache 3-D-Summenbildung je Zutatenmenge möglich ist. Und wenn Du jeweils ein Dummytabellenblatt als erstes und letztes anlegst, kannst Du zwischen diesen beiden beliebig Tabellenblätter einfügen, ändern und löschen die 3-D-Summenformel wertet dann immer auch alle korrekt aus und ist dabei weder auf das volatile INDIREKT() noch auf Matrixformeln angewiesen.
Gruß Werner
.. , - ...
Anzeige
AW: das wäre mE weder eine "Notlösung" ...
07.02.2019 14:09:27
Stefan
Hallo Werner
Ach so, du meinst, dass ich die unbenötigten Zutaten leer lasse und nicht einfach Menge 0 eingebe... ja, dann ist es keine Aufblähung, jedenfalls nicht bezüglich der Dateigrösse.
Ich habe es schnell ausprobiert, das wäre ein gangbarer Weg, allerdings wollen mir die vielen ausgefilterten Zeilen nicht gefallen und dann gibt es Rezepte, bei welchen eine bestimmte Zutatenreihenfolge wichtig ist und da wäre es natürlich schön, wenn die Zutaten auch jeweils in der Reihenfolge aufgeschrieben werden könnten...
Gruss Stefan
Anzeige
AW: das wäre mE weder eine "Notlösung" ...
07.02.2019 14:20:41
Stefan
habe die Checkbox, dass die Frage noch nicht ganz beantwortet wurde, vergessen.
AW: letzteres ist ein Argument, jedoch ...
07.02.2019 14:58:39
neopa
Hallo Stefan,
... welches man aber leicht "entschärfen" kann. Dann allerdings zu Lasten eines "Aufblähens" der Datei, welches ich aber momentan trotzdem noch vorziehen der von Dir angedachten Auswertung vorziehen würde.
Erfasse Deine Zutatenreihenfolge wie notwendig und erfasse in einem in allen Tabellenblättern freien Datenbereich wieder eine fixe komplexe Zutatenreihenfolge. Diesen weist Du über SVERWEIS() die Daten aus Deiner vorgegebenen Reihenfolge zu, wobei Du diese Formeln mit WENNFEHLER()SVERWEIS(....);0) klammerst.
Diese nun wieder fixe Zutatenreihenfolge kannst Du in jedem Blatt auch ausblenden (geht auch gruppiert, wie Du auch zuvor alle Formelzuweisung in den Tabellenblättern bei gruppierten Blättern vornehmen kannst) und dann wie vorhin vorgeschlagen über eine einfache 3D-Summenauswertung vornehmen. Zuvor jedoch nicht vergessen, die Gruppierung wieder aufzuheben.
Gruß Werner
.. , - ...
Anzeige
AW: Problem gelöst.
07.02.2019 15:07:13
Stefan
Hallo Werner
Etwas in der Richtung werde ich dann wohl umsetzen, herzlichen Dank für deine Hilfe!
Gruss Stefan
;

Forumthreads zu verwandten Themen

Anzeige
Anzeige
Anzeige
Anzeige
Entdecke relevante Threads

Schau dir verwandte Threads basierend auf dem aktuellen Thema an

Alle relevanten Threads mit Inhaltsvorschau entdecken
Anzeige
Anzeige

Infobox / Tutorial

Indirekt mit INDEX und VERGLEICH verknüpfen in Excel


Schritt-für-Schritt-Anleitung

  1. Inhaltsverzeichnis erstellen: Lege ein neues Blatt in Deiner Arbeitsmappe an, das als Inhaltsverzeichnis dient. Trage hier die Namen der einzelnen Rezeptblätter ein.

  2. Zutaten auslesen: Verwende die Funktion INDIREKT, um die Zutaten aus den Rezepten zu extrahieren. Hier ein Beispiel, wie Du es für eine Zutat umsetzen kannst:

    =INDIREKT("'"&Inhaltsverzeichnis!B3&"'!B8")
  3. INDEX und VERGLEICH kombinieren: Um die spezifischen Zutaten aus den verschiedenen Rezepten zu addieren, kannst Du die Formeln INDEX und VERGLEICH wie folgt miteinander kombinieren:

    =SUMME(SUMMEWENN(INDEX(INDIREKT("'"&Inhaltsverzeichnis!B$3:B$4&"'!B$8:B$37"), VERGLEICH(A8, INDIREKT("'"&Inhaltsverzeichnis!B$3:B$4&"'!D$8:D$37"), 0), ">0")))
  4. Zusammenfassen: Stelle sicher, dass Du für jede Zutat und jedes Rezept die entsprechenden Zellbereiche korrekt angibst.


Häufige Fehler und Lösungen

  • Fehler: Es wird nur die Zutat des ersten Rezepts zurückgegeben.

    • Lösung: Überprüfe, dass die Zellbereiche in der INDEX- und VERGLEICH-Formel korrekt definiert sind. Möglicherweise verweist Deine VERGLEICH-Funktion nicht auf die richtige Zelle.
  • Fehler: Die INDIREKT-Funktion gibt einen Fehler zurück.

    • Lösung: Stelle sicher, dass die Zellreferenzen im Inhaltsverzeichnis korrekt sind und die Namen der Tabellenblätter exakt übereinstimmen.

Alternative Methoden

Falls Du Schwierigkeiten mit der Kombination von INDEX und VERGLEICH hast, kannst Du auch die SVERWEIS-Funktion nutzen. Diese ermöglicht es, Werte basierend auf einer Suchbedingung zu finden. Zum Beispiel:

=SVERWEIS(A8,INDIREKT("'"&Inhaltsverzeichnis!B3&"'!D8:E37"),2,FALSCH)

Diese Methode ist einfacher, wenn alle Daten in einer einheitlichen Struktur vorliegen.


Praktische Beispiele

Angenommen, Du hast ein Rezeptblatt mit verschiedenen Zutaten und Mengen. Hier ist ein einfaches Beispiel, wie Du eine Einkaufsliste in Excel erstellen kannst:

  1. Rezeptblatt: Zutat Menge
    Zucker 100
    Salz 50
  2. Inhaltsverzeichnis: Rezeptname
    Rezept1
    Rezept2
  3. Einkaufsliste-Formel:

    =SUMME(SUMMEWENN(INDIREKT("'"&Inhaltsverzeichnis!B$3:B$4&"'!B8:B37"),">0"))

Dieses Beispiel zeigt, wie Du Zutatenmengen für die einkaufsliste excel automatisiert zusammenstellen kannst.


Tipps für Profis

  • Datenstruktur: Halte Deine Rezeptdaten in einer einheitlichen Struktur, um die Verwendung von Formeln zu erleichtern. Leere Zellen anstelle von Nullen zu verwenden, kann die Lesbarkeit verbessern.

  • 3D-Referenzen: Überlege, Dummy-Tabellenblätter zu verwenden, um eine bessere Struktur und Übersichtlichkeit zu erreichen. Das erleichtert die Anwendung von 3D-Summen und vereinfacht die Auswertung.


FAQ: Häufige Fragen

1. Wie kann ich die Einkaufsliste automatisch aktualisieren?
Wenn Du die Formeln in der Einkaufsliste korrekt einstellst, werden Änderungen in den Rezeptblättern automatisch in die Einkaufsliste übernommen.

2. Welche Excel-Version benötige ich für diese Funktionen?
Die beschriebenen Funktionen sind in den meisten modernen Excel-Versionen verfügbar, einschließlich Excel 2010 und neuer.

3. Kann ich auch mehrere Zutaten in einer Formel kombinieren?
Ja, Du kannst mehrere SUMMEWENN- oder SVERWEIS-Funktionen in einer Formel kombinieren, um verschiedene Zutaten gleichzeitig zu summieren.

4. Was ist der Unterschied zwischen INDEX und SVERWEIS?
INDEX gibt den Wert einer Zelle in einem bestimmten Bereich zurück, während SVERWEIS einen Wert in der ersten Spalte eines Bereichs sucht und den zugehörigen Wert aus einer anderen Spalte zurückgibt.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige