Komplexe Vergleich/Sverweis funktion in Excel?
Hallo, Community.
Ich habe eine recht umfassende Tabelle für die Ausleihe von Geräten auf Arbeit.
- In dieser Tabelle gibt es eine Spalte mit Gerätetyp (TV, Computer, Maus, etc) in welcher nur festgelegte Werte durch Datenüberprüfung ausgewählt werden können.
- Zusätzlich gibt es eine Spalte mit Ausleihbeginn und Ausleihende (FEST!) Ich plane die Ausleihen also und es wird davon ausgegangen, dass z.b. eine Maus am 01.01.17 abgeholt und am 14.07.17 wiedergebracht wird.
- noch dazu werden Ausleihen weit vorher angemeldet und in der Liste (unten angefügt) vermerkt welche nicht nach irgendwelchen Kriterien Sortiert ist oder werden soll
Nun möchte ich eine "Auflistung" machen, Wie viele Geräte des jeweiligen Typs ich besitze (fester schon bekannter Wert) und wie viele AKTUELL ausgeliehen bzw schon verplant sind.
Problem dabei ist nur, dass wenn ich ZÄHLENWENN() in der Spalte des Typs anwende, auch die Geräte mitgezählt werden, die noch nicht ausgeliehen wurden, oder schon zurückgegeben wurden (liste geht also in Vergangenheit und Zukunft)
Ich muss also davon Trennen, dass
TYP START ENDE
Maus 01.01.17 14.07.17
Maus 16.07.17 24.08.17
Maus 01.09.17 12.12.17Nur ein Gerät ist und nicht (wie aktuell) 3, weil ZählenWENN 3x Maus liest
Wie stelle ich das an? habt ihr da eine Formel für mich?
Typ befindet sich in der Spalte F(ab 4) und Start in Spalte H(ab 4) und Ende in Spalte I(ab 4)
Zählerstand kann in A50:A60 ausgegeben werden (10 verschiedene ausleihbare Typen vorhanden)
VIELEN DANK!
4 Antworten
Der tatsächlich gangbare Weg liegt nicht (nur) in der Komplexität der Formel sondern (auch) in der Vergabe eindeutiger Inventarnummern.
Sind diese in einer "intelligenten" Form angelegt/kodiert, ermöglichen sie die eineindeutige Zählung unter Berücksichtigung des Typs etc.
Plumpes Bsp:
Mäuse bekommen ein "Ma" und eine lfd. Nummer, TV ein "TV" + lfd Nr, ...
Zählen der Einträge mit "Ma" am Anfang unter Berücksichtigung der lfd Nr (welche von 1 bis zur bekannten Anzahl läuft) ergibt wohl das gewünschte Ergebnis. Entspr. die anderen Geräte.
hmm, lange gegrübelt über diese Frage. In eine einzige schlaue Formel packen kann ich es nicht. Vielleicht geht ja folgendes.
Folgende Annahmen zum Aufbau der Tabelle, s. Bild:
Spalte A = Gerätetyp
Spalte B = Start
Spalte C = Ende
In Zeile 1 (G1 - NG1) die Tage des relevanten Zeitraums. Spalte NG unter der Annahme, dass 365 Tage betrachtet werden. Nach Belieben zu ändern.
Spalte E die Namen der Geräte (bei 10 Gerätetypen E2:E11)
Spalte F = Max(G2:NG2) -> Ermittelt den Maximalwert an ausgeliehenen Geräten, Formel runterkopieren für alle Gerätetypen
G2:NG11 =SUMMENPRODUKT(($A$1:$A$100=$E2)*($B$1:$B$100<=G$1)*($C$1:$C$100>=G$1)*1)
wenn es mehr als 100 Ausleihvorgänge sind, entsprechend an den drei Stellen erhöhen.
Für jeden Tag wird pro Gerätetyp geprüft, wie viele jeweils ausgeliehen sind. Der Maximalwert müsste von Dir vorgehalten werden, damit die Ausleih-Wünsche bedient werden können.
Nabend.
Ich habe die Funktionsnamen, etc. nicht alle im Kopf und auch gerade kein Excel zur Hand um damit herum zu probieren. Aber einfach mal so als Idee, würde ich wohl versuchen mir eine Funktion zu bauen, in der folgende Parameter berücksichtigt werden:
Zählenwenn xx mit Bedingungen bezogen auf das aktuelle Datum. Z.B. sollte Start > aktuelles Datum alles ausschließen, was vor dem heutigen Datum ausgeliehen wurde. Dann müsste man die noch nicht zurückgegebenen Sachen noch einfließen lassen.
Ansonsten solltest Du Dir evtl. eine neue Spalte "Ausgeliehen (Ja/Nein)" anlegen, bei der sich "Ja" über Start kleiner/gleich Aktuelles Datum UND Ende größer/gleich Aktuelles Datum erzeugen ließe.
Mit dieser zusätzlichen Spalte wäre für Deine Zählfunktion dann Zählenwenn xx mit der Bedingung Ausgeliehen = Nein.
Hoffe Du konntest meinen Gedanken halbwegs folgen.
ASRvw de André
Du kannst dafür Summenprodukt verwenden, z.B.
=SUMMENPRODUKT(($F$4:$F$49="Maus")*($H$4:$H$49<HEUTE())*($I$4:$I$49>HEUTE()))
Diese Formel liefert die Anzahl der Einträge, bei denen der Typ "Maus" ist und das Startdatum vor und das Enddatum nach Heute liegen.
Statt "Maus" kannst du auch einen Zellbezug verwenden. Je nachdem ob bei der Auswertung der Heutige Tag mitgerechnet werden soll, musst du z.B <= statt < verwenden.
Den Punkt kann ich in der Logik aber nicht nachvollziehen. Warum ergibt sich aus getrennten Zeiträumen automatisch, dass es eine Maus ist? Es könnten doch auch 2 sein oder 3?
Das Ermitteln von überschneidenden Zeiträumen erscheint mir (gerade wenn tatsächlich mehr als 1 Gerät vom gleichen Typ im Einsatz ist) deutlich aufwändiger als eine Inventur manueller Art.
Vielen dank schonmal. aber das problem ist ja nicht der heutige tag sondern, wie in dem Beispiel
dass dieses EINE gerät hintereinander ausgeliehen wird. es würde aber als 3 Geräte gezählt werden.Eisgentlich müsste es aber ja nur eins sein, weil verschiedene Ausleihzeiträume, welche sich nciht überschneiden