Abfrage in Tabelle nach zwei Werten

Hallo zusammen,
Ich komme nicht mehr weiter, bzw stehe auf dem Schlauch und sehe vor lauter Formeln keinen geraden Weg mehr.
Projekt Ausleihliste:
Um es vereinfacht zu schreiben, möchte ich eine Art Liste führen, über Gegenstände, die ich ausleihen kann.
Ich habe ein Deckblatt wo ich alle möglichen Daten eintragen kann. Per Makro überträgt er diese Daten dann in eine Liste. In der Liste habe ich beispielsweise ganz am Ende zwei Spalten. Spalte M lautet Rückgabe am und Spalte N lautet Rückgabe durch.
Diese zwei Spalten sind die einzigen, die händisch beschreibbar sind und nicht geschützt sind.
Bekomme ich also einen Gegenstand zurück, trage ich alles hier ein. Dann schwenken die Zeilen auch von rot auf grün um.
Kurzum, hier die Beschreibung der Blätter :
- Deckblatt: Felder, die ich befülle und per Makro an dir Tabelle in Liste ubergebe
Dort schreibt er fortlaufend ein Tagebuch quasi.
- Liste: Tagebuch über die aisgeliehenen Gegenstände. Am Ende zwei Spalten für die Rückgabe. Fehlt ein Gegenstand sind die Felder leer und rot. Sind sie zurück gegeben sind die Felder befüllt und grün.
Aufgabe:
Ich möchte nun zusätzlich das Tabellenblatt Übersicht anlegen. Auf dieser Übersicht sind die ganzen Gegenstände (überschaubar) untereinander geschrieben. Daneben sind einige Daten noch und am Ende der Tabelle soll in Spalte F eine Meldung stehen. Die Meldung kann entweder Ausgeliehen oder Verfügbar heißen.
Ich habe eine Hilfstabelle in Spalte G angelegt, die dann später ausgeblendet werden soll.
Die Schwierigkeit die ich sehe, ist natürlich, dass mehrere gleiche Gegenstände in der Liste auftauchen werden. Natürlich mit anderen Personen und anderen Daten bei der Rückgabe. Da wird es mit dem Sverweis schwierig.
Ich möchte alles ohne Vba lösen, sofern möglich.
Excel soll mir jetzt quasi für jeden Gegenstand anzeigen, ob er verfügbar ist, oder ausgeliehen ist.
Also soll er beispielsweise Bei Gegenstand 1 "Buch" (Suchkriterium) auf dem Tabellenblatt Liste suchen, wo sich das "Buch" befindet und zusätzlich gleichzeitig prüfen soll, ob dann die Spalte M und N leer sind. Ist dies der Fall, dann gilt das "Buch" als ausgeliehen. Findet er nur Einträge mit "Buch" und zusätzlich alle Rückgabe Spalten beschrieben, dann gilt das "Buch" als Verfügbar.
Bei meinem. Sverweis hört er beim ersten Finden des "Buch" auf und gibt mir natürlich den Wert "verfügbar" zurück, obwohl das Buch etwas später noch ausgeliehen wurde und noch nicht zurück ist.
Da ich die Hilfstabelle ausgegeben habe, kann ich ja bei einem Treffer die 0 dort 1 ausgeben lassen.
Kann mir jemand weiterhelfen und weiß einen Ansatz?
Vielen Dank im Voraus!!
LG Marco

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Hallo Marco,
am Ende ist nur die Leihliste relevant.
Da Du diese beliebig sortieren kannst, hast Du schnell die gerade ausgeliehene Gegenstände am Ende der Liste. Eine Tabelle (auch intelligente Tabelle bennant) muss Du nicht im voraus Platzhalter-Zeilen reservieren: diese ergänzen sich automatisch nach unter (samt Formeln), wenn ein neuer Eintrag hinzugefügt wird.
Schwieriger wäre die Liste der verfügbare Gegenstände. 2 Möglichkeiten: Formeln oder Power Query.
Formeln:
da musst Du eine Markierung auf die Gegenstände legen, die zurzeit ausgeliehen sind. Ich schlage eine Hilfsspalte1 mit
= [@Gegenstand]&[@[Rückgabe am]]
Diese Spalte ist nicht schön, man kann sie anschliessend ausblenden.
Element, die gerade ausgeliehen sind, erscheinen in dieser Spalte ohne Zusatz. Dann kann man daraus testen: ist diese Gegenstand in
diese Spalte mit einem SVerweis ausfindbar, weil es keine Zusatz hat, dann ist es ausgeliehen. Genauer gesagt umgekehrt: wenn der SVerweis eine Fehler zurückgibt, ist es verfügbar:
=WENN(ISTFEHLER(SVERWEIS([@Gegenstand];[Hilfsspalte1];1;0));"Verfügbar";"Ausgeliehen")
Dann kann man mit bedingte Formatierung die Zustände besser sichtbar machen. Jede Gegenständ kann mehrmals vorkommen. Ein "Duplikate entfernen" geht hier nicht ohne Daten zu venrichten.
Alternativ, anstatt diese beide Formeln, ist mit Power Query (PQ) am einfachste
(es sieht zuerst nicht so aus, aber es ist sehr einfach):
_ irgendeine Zelle von der Tabelle "Leihliste" auswählen,
_ im Register "Daten", in Gruppe "Abrufen und transformieren", "Aus Tabelle" auswählen
Es öffnet sich den Power Query Editor (tut nicht weh. Ist nur beim erstem Mal beeindrückend) und der Inhalt der Tabelle ist sichtbar.
Hier werden wir Transformationsschritte definieren, die jederzeit wieder eingesetzt werden können. Es ist ein sogenannte "Low Code/No Code" Programmier-Umgebung (Outsch! Das Wort Programmieren wollten wir nicht sagen. Nennen wir es "Rezept-herstellen")
Folgende Schritte:
_ im Reiter "Spalte hinzufügen", "Bedingte Spalte" auswählen,
_ eine Name "Zurückgegeben" eingeben, Wenn Spalte "Rückgabe am" "entspricht nicht" "null", Dann "0", Andernfalls "1" (Ja: es ist nichts anderes als eine Excel-Formel WENN).
-> es fügt sich eine neue "Schritt" in der Liste rechts. Das ist das "Rezept".
_ im Reiter "Transformieren", "Gruppieren nach" auswählen
_ "weitere" anklicken, und zweimal auf "Gruppierung hinzufügen" klicken,
_ Gruppieren nach Spalten "Gegenstand", "Typ", "Nummer"
_ als neuer Spaltenname "Ausgeliehen" eintragen, Vorgang "Max", Spalte "Zurückgegen" auswählen
Das ist eine Art von Duplikat entfernen und gleichzeitig von jeden eindeutigen Kombinationen das Maximum der Spalte "Zurückgegeben" behalten. Das Schwierigste haben wir damit hinter uns.
Jetzt wären wir sogar fertig. Aber 0 und 1 sind nicht schön.
_ in "Spalte hinzufügen", "Bedingte Spalte" auswählen,
_ Spaltenname "Stand", Wenn "Zurückgegeben" "ist gleich" "1" dann "Ausgeliehen" Anderfalls "Verfügbar"
_ Spaltenüberschrift von "Zurückgegegen" rechtklicken, und "entfernen"
_ Spalten "Ausgeliehen" aufsteigend sortieren
_ in "Datei", "Schliessen und laden" auswählen: das Ergebnis unsere Rezept wird in einer neue Tabelle ausgegeben, deren Blattname beliebigt geändert werden kann, wie z.B. Gegenstandsliste
Und jetzt: ändert sich irgendwas in der "Leihliste", braucht man nur im Reiter "Daten" "Alle Aktualisieren" zu klicken (Alternativ Strg+Alt+F5) und schon wird unsere Rezept neu angewendet und die Liste aktualisiert.
Das gesamte ist dann auf diese 2 Blätter beschränkt: will man etwas ausleihen, geht man in Gegenstandsliste, prüft die Verfügbarkeit, kopiert Gegenstand-Typ-Nummer und fügt es am Ende der Leihliste, Datum fügt man mit Strg+. (Punkt). Name und Vorname muss eingetippt werden. Na ja, ein Bischen Arbeit muss wohl übrig bleiben. Anschliesssen auf Gegenstandsliste wechseln und Strg+Alt+F5 drücken.
Also entweder Hilfsspalte, aber dann kommen Gegenstände mehrmals vor, oder PQ.
Ich mag PQ, weil es zwingt eine klare Struktur zu haben (die Du schon hast :-) und eine klare Struktur ist immer der Basis einer guten Lösung.
Und weil PQ robust ist: da bastelt keiner aus versehen an den Formeln und ich muss nicht prüfen, dass überall die richtige Formel eingesetzt sind.
Eine dritte Möglichkeit wäre VBA. Wenn es einfach und stabil ohne (VBA-) Programmierung geht, dann lieber ohne Programmierung. VBA-Programmierung ist wie Formeln in noch chaotischer. Und dann packt man immer was drauf und aus dem quick&dirty wird nur noch dirty.
Jetzt hast Du die Wahl.
Viel Erfolg.
VG
Yal
ach ja: die Abfrageliste kann man in "Daten", "Abfragen anzeigen" ein- und ausblenden.

Beliebteste Forumthreads (12 Monate)

A	B	C	D	E	F
Verfügbarkeit

	Gegenstände	Typ	Nummer	Verfügbarkeit
	Buch 1	Duden	234CD33	AUSGELIEHEN
	XBOX	One	lkjkdjbakg	AUSGELIEHEN
	PS4	PS4	lkndvöjabf	Verfügbar
	COD2	PS4 Spiel	KVNEWGO	Verfügbar
	Uhr	Rolex	OLELEEM	AUSGELIEHEN
	Buch 2	Atlas	ÜÜÜÜ	AUSGELIEHEN
	TV	samsung	LVNWLW	AUSGELIEHEN

Zelle	Formel
B5	=WENNFEHLER(INDEX(Tabelle1[Gegenstand];AGGREGAT(15;6;(ZEILE(Tabelle1)-ZEILE(Tabelle1[#Kopfzeilen]))/(ZÄHLENWENN($D$4:$D4;Tabelle1[[Nummer]:[Nummer]])=0);1))&"";"")
E5	=WAHL(1+(VERWEIS(9;1/(Tabelle1[Nummer]=[@Nummer]);Tabelle1[Rückgabe am])&gt0)+([@Nummer]="")*2;"AUSGELIEHEN";"Verfügbar";"")

Abfrage in Tabelle nach zwei Werten

8
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

300 Forumthreads zu ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

Abfrage in Tabelle nach zwei Werten

8 Beiträge zum Forumthread Beiträge zu diesem Forumthread

300 Forumthreads zu ähnlichen Themen

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

8
Beiträge zum Forumthread

Beiträge zu diesem Forumthread