Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Abfrage in Tabelle nach zwei Werten

Abfrage in Tabelle nach zwei Werten
14.07.2021 17:06:57
Marco
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

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

Betreff
Datum
Anwender
Anzeige
AW: (D)eine Beispieldatei wäre hilfreich(er) owT
14.07.2021 17:13:26
neopa
Gruß Werner
.. , - ...
AW: (D)eine Beispieldatei wäre hilfreich(er) owT
14.07.2021 18:00:07
Marco
Hier die Beispieldatei:
https://www.herber.de/bbs/user/147117.xlsx
Die Datei ist jetzt sehr vereinfacht, ohne Formeln, ohne Makro. Nur zur Veranschaulichung.
Lg Marco
AW: nicht eindeutig nachvollziehbar, denn ...
14.07.2021 20:03:47
neopa
Hallo Marco,
... aus der Datei ist für mich so nicht ersichtlich, wo Du was als fixe Daten hast und wo was genau ermittelt werden soll. Kannst Du das an Hand Deine eingestellten Datei kurz beschreiben. Dann sehe ich es mir morgen an.
Gruß Werner
.. , - ...
AW: nicht eindeutig nachvollziehbar, denn ...
14.07.2021 20:15:27
Marco
Hallo, ja natürlich.
In der Datei siehst du 3 Tabellenblätter.
1. Das Deckblatt
Hier werden Daten in die Tabelle eingegeben. Die Daten werden im Anschluss per Vba fortlaufend in die Liste übertragen.
Hier sind also alle Daten in den Feldern variabel. Außer natürlich die Beschreibung. Sowohl der Gegenstand, als auch der Typ und die Nummer sind fest. Die werden aus einer Liste gespeist und dann per Dropdown und Sverweis ausgegeben. Frei Eintragbar sind das Datum, der Nachname und der Vorname.
2. Die Liste
Hier werden alle Vorgänge des Ausleihens der Gegenstände aufgereiht. Und zwar fortlaufend. Es stellt quasi eine Art Historie dar, wann ich welchen Gegenstand wem ausgegeben habe.
Da diese Liste mittels Vba aus der Ausfüllhilfe bzw das Deckblatt übertragen werden, ist diese Liste fest belegt. Einzig die letzten zwei Spalten "Rückgabe am" und "Rückgabe durch" sind manuell beschreibbar. Alle anderen Zeilen sind gesperrt.
3. Die Übersicht/Verfügbarkeit
Die Übersicht ist fest geschrieben. Hier sind alle Gegenstände die ich habe untereinander aufgelistet. Also auch die Typen und Nummern bleiben fest.
Die Spalte "ausgegeben" und "im Bestand" werden als Wenn Formel deklariert und gesteuert. Sollte also die letzte Spalte grün und verfügbar sein, so erscheint ein x in der Spalte im Bestand. Andersrum natürlich genauso.
Die Frage, die ich gestellt habe, bezieht sich auf die rot und grün hinterlegten Zeilen in der letzten Spalte.
Sprich Excel soll folgende Frage für jeden einzelnen aufgelisteten Gegenstand beantworten:
Ist der Gegenstand gerade ausgegeben oder ist er verfügbar? Prüfe also, ob der Gegenstand in der Liste in der Ausleihhistorie aufgeführt ist und falls ja, prüfe, ob der Gegenstand bereits zurück gegeben wurde oder nicht.
Es kann daher ja sein, dass Gegenstand (zB Buch 1) bereits mehrfach ausgeliehen wurde, also auch mehrfach in der Liste steht, jedoch das letzte Mal noch nicht zurück gegeben wurde.
Sofern ein Gegenstand noch nicht zurückgegeben wurde, soll im Tabellenblatt Übersicht/Verfügbarkeit dann bei dem jeweiligen Gegenstand in der letzten Spalte entweder verfügbar oder ausgegeben erscheinen.
Ich hoffe, dass das einigermaßen plausibel erklärt wurde...
LG Marco und vielen Dank!!
Anzeige
AW: nicht eindeutig nachvollziehbar, denn ...
14.07.2021 22:44:36
Yal
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.
Anzeige
AW: (m)eine Formellösung hierzu ...
15.07.2021 10:25:22
neopa
Hallo Marco,
... würde ich im neuen Tabellenblatt Uebersicht wie folgt vornehmen. Diese ist so definiert, dass sie jegliche Änderungen in Tabelle 1 automatisch dynamisch verarbeitet und sowohl die Listentabelle als auch die neue Übersichtsliste (fast) beliebig nachträglich verschoben werden kann, ohne eine Änderung an den Formeln vonrnehmen zu müssen.
Dazu nachfolgende Formel B5 einfach zwei Spalten nach rechts kopieren und weit genug nach unten und die Formel in E5 entsprechend nach unten kopieren.
ABCDEF
Verfügbarkeit
      
 GegenständeTypNummerVerfügbarkeit 
 Buch 1Duden234CD33AUSGELIEHEN 
 XBOXOnelkjkdjbakgAUSGELIEHEN 
 PS4PS4lkndvöjabfVerfügbar 
 COD2PS4 SpielKVNEWGOVerfügbar 
 UhrRolexOLELEEMAUSGELIEHEN 
 Buch 2AtlasÜÜÜÜAUSGELIEHEN 
 TVsamsungLVNWLWAUSGELIEHEN 
      

ZelleFormel
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";"")
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...
Anzeige
AW: (m)eine Formellösung hierzu ...
15.07.2021 10:29:46
Marco
Sehr geil, funktioniert perfekt! Vielen herzlichen Dank dafür :)
LG Marco
AW: bitteschön owT
15.07.2021 10:37:13
neopa
Gruß Werner
.. , - ...

301 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige