Excel-Frage

[Gelöschtes Mitglied 63700]

Gelöst:

Spoiler

Ich bräuchte mal einen Schubs in die richtige Richtung, möglichst ohne Macros

Ich habe hier mehrere Zugangskarten mit Seriennummern, die ich in einer Liste zusammengefasst habe.
In einer zweiten Liste trage ich ein, wann ich welche Karte ausgegeben und wieder zurück bekommen habe.

Ich möchte jetzt in der ersten Liste einen Hinweis zum Status der Karte sehen, also ob diese verfügbar ist.
In der zweiten Liste haben ich schon einen Indikator pro Zeile, da aber hier die selben Karten mehrmals vorkommen, weiß ich nicht, wie ich da am besten rangehe…

Habe aber schon wieder eine neue Frage.

 

[passat3233]

Pro Zugangskarte 2 Spalten:
1. Spalte = Ausgabedatum
2. Spalte = Rückgabedatum
Darüber als Überschrift Seriennummer der Karte

Unter Spalte 1 und 2 machst du eine Summezeile (natürlich mit genug Leezeilen zwischen Überschrift und Summenzeile, damit du da die Dateneintragen kannst).
Darin wird die Anzahl der Ausgabedaten und Rückgabedaten gezählt.
In das Summenfeld trägst du die Funktion: "=ANZAHL(von:bis)" ein (natürlich ohne Leerzeichen, "von" durch die erste Zelle mit Wert (z.B. A3) und "bis" durch die letzte Zelle mit Wert (z.B. A40) ersetzen.
In der ersten Liste machst du neben der Seriennummer ein Feld, in dem du die Summenzeile der 2.Spalte von der Summenzeile der 1. Spalte in Tabelle 2 subtrahierst.
Wenn das Ergebnis 0 ist, ist die Karte verfügbar, wenn das Ergebnis 1 ist, ist sie ausgegeben.
Das kann man auch noch mit bedingter Zellenformatierung ergänzen: Bei 0 Zellenhintergrund grün, bei 1 Zellenhintergrund rot.

 

[Tzk]

Dann mach doch folgendes:
1. Spalte: SN der Karte
2. Spalte: Summenfeld
alle folgenden Spalten: Ausgabe bzw. Rückgabe (wechselnd)

Im Summenfeld zählst du die Aus+Rückgaben. Anzahl grade = Karte da. Anzahl ungrade = Karte unterwegs.
Das Summenfeld kannst du gleichzeitig auch wie vorgeschlagen Rot/Grün einfärben und weisst so immer welche Karte grade da ist.
Damit brauchst du nur eine einzelne Liste und das aktuelle Ausgabedatum findest du immer in der "rechtesten" befüllten Spalte pro Karte.

Was besseres fällt mir für dein Problem grade leider auch nicht ein

 

[passat3233]

Das kann man dann über eine Pivottabelle lösen.
Würde ich aber nicht machen.
Mit deinem Wunsch hast du aber mehr Eingabeaufwand, denn da musst du bei jedem Eintrag auch noch die Seriennummer mit erfassen.
Ich würde so etwas immer so einfach wie möglich lösen, also nach Seriennummern getrennt.

 

[passat3233]

Wie schon geschrieben: Pivottabelle!
Da werden Ergebnisse zusammengefasst.

 

[awehring]

Es gibt die Funktion SUMMEWENN.
Dann trägst du in einer Spalte die Seriennummer ein und jeweils daneben das Datum von Ausgabe und Rückgabe.
Ist allerdings anfälliger für Tippfehler, insbesondere bei der Seriennummer.

https://support.office.com/de-de/article/SUMMEWENN-Funktion-169b8c99-c05c-4483-a712-1697a653039b

 

[awehring]

Dropdown-Liste in Excel erstellen - PCtipp.ch

 

[awehring]

Musst du ausprobieren. Nötigenfalls den Bereich mit jeder neuen Nummer vergrößern.

Excel kann, was es kann. Wenn du darüber hinaus gehen willst, musst du Makros programmieren (Visual Basic).

- - - Updated - - -

P.S: für deine Aufgabenstellung wäre eigentlich eine Datenbank besser geeignet als Excel.

 

[pufaxx]

@work zähle ich z.B. Krankheitstage von Mitarbeitern. KEvon KEbis ... das wird summiert, aber eben je Mitarbeiter. Dann wieviele Tage innerhalb der ersten 28 Vertragstage? Sind 42 Tage insgesamt im Zeitraum 1 Jahr nach Vertragsbeginn überschritten? (Wichtig für eventuellen Anspruch auf Krankengeld) ... Arbeitsunfall dabei, ja nein (zählt extra bei Krankengeld) ... Trallala ... Im Grunde eine ähnliche Aufgabenstellung, bei mir nur noch etwas komplizierter

Arbeite mit mehreren Tabellen. Am besten "Datentabellen", dann werden die Formeln nicht so unhandlich, weil man die Namen von Spalten verwenden kann. Auch werden Formeln automatisch weiter "fortgesetzt", wenn man der Datentabelle Zeilen hinzufügt.

Eine Tabelle mit Deinen ganzen Seriennummern, die jeweils nur einmal vorkommen dürfen - (wie bei mir z.B. die Mitarbeiter) - andere Tabelle beliebiger Länge, wo ich Mitarbeiter per Dropdown auswählen kann - eben auch mehrfach, KEvon KEbis eintragen - und da steht dann die Anzahl der Tage je Mitarbeiter. In Deinem Fall könntest Du eine Hilfsspalte anlegen, die immer, wenn es ein Rückgabedatum gibt, eine 0 ausgibt. Wenn's kein Rückgabedatum gibt, steht dort ne 1. Zweite Hilfsspalte "SUMMEWENN" oder "ZÄHLENWENN" - die zählt alle Zahlen aus der ersten Hilfsspalte zusammen, die der bestimmten Seriennummer zugeordnet sind. Dann wurde Karte 123 vier Mal ausgegeben, vier Mal zurück - hast vier Zeilen mit Nullen. Karte 234 wurde fünf Mal ausgegeben, vier Mal zurück - hast fünf Zeilen mit Einsen.

In der ersten Tabelle mit Deinen Seriennummern machst Du ne Spalte, die in der zweiten Tabelle mit Ausgabe oder Rückgabeterminen nach der Seriennummer und der zweiten Hilfsspalte sucht und den Wert ausgibt. Steht da ne eins, ist die Karte unterwegs. Steht da ne Null, ist sie wieder zurückgekommen. Steht da #NV, ist sie nie ausgegeben worden. Du könntest in der Tabelle mit den Ausgabe und Rückgabeterminen noch eine dritte Spalte machen, die auswertet, wie oft die Karte schon unterwegs gewesen ist.

Ein Dropdown "dynamisch" zu machen ist allerdings schwierig, ohne dann und wann mal Hand anzulegen. Also dass sich die Anzahl der Seriennummern verändert - mit Datentabellen absolut machbar (wobei die "Datenüberprüfung" Spaltennamen leider in Koordinaten umwadelt). Was wohl nicht ohne Weiteres geht - im Dropdown nur die verfügbaren Karten anzuzeigen ...

Wenn ich heute ein Bisschen Luft habe, kann ich meine KE-Tage-Auswertungsdatei umarbeiten. Dürfte nicht allzu schwierig sein.

 

[awehring]

Wenn man mit Excel solche komplizierten Sachen macht wie @pufaxx, sollte man sich ernsthaft überlegen, ob man nicht lieber mit einer Datenbank (z. B. Access) arbeitet.

Mit Excel muss man immer wieder Hand anlegen, wie @pufaxx richtigigerweise schreibt. Nach längerem Gebrauch vergisst man die Details, entdeckt plötzlich einen Fehler und muss sich wieder reinarbeiten.
Schlimm wird es, wenn man sein Excel Blatt an jemand anderen übergibt. Ich habe mehrmals erlebt, dass der dann mit etwas Neuem, eigenem wieder bei Null begann, weil er das vorhandene Excel nicht verstand.

Sich in Access einzuarbeiten ist schon einiger Aufwand. Aber wenn man damit ein Problem gelöst hat, braucht es keine weitere Pflege, sondern funktioniert einfach. Falscheingaben können viel leichter abgefangen werden. Jemand anderer kann es einfach benutzen, ohne daran rumbasteln zu müssen. Das kann auch irgend eine temporäre Hilfskraft sein.

 

[pufaxx]

@awehring: Nee, gemeinnütziger Betrieb - und ich arbeite an einem Rechner mit Atom-Prozessor. Sowas wie Access ist da nicht drin. Aber ich komme auch so prima zurecht. Muss halt viele Hilfsspalten anlegen. Und ich habe mir einige Dinge angewöhnt - beispielsweise reine Eingabe- und reine Auslese-Tabellen zu haben. Oder alles, wo eine Formel hinterlegt ist, hellgrau einzufärben. Und eben viel, viel Kreuz und Quer. Und sinnvolle Namensgebung. Konsistente Namensgebung. Sogar Listen mit eigenen "Namensgebungs-Regeln". Als ich da angefangen habe, hab ich mir ein XAMPP aufgesetzt, um ein ContentManagementSystem für meine Zwecke aufzubohren. Wäre cool gewesen. Aber mit einem Atom? Und die schicken Datums-Funktionen von Excel sind in PHP und MYsql nicht so ganz ohne.

Allerdings könnte jemand anders mit meinem Zeug kaum etwas anfangen. Da gibt's nur eine Kollegin, die wirklich Durchblick hat. Und ihrerseits Sachen für andere Kollegen, die Null Ahnung von überhaupt irgendwelchen Programmen haben, vorbereitet ... und mit Makros Fehleingaben abfängt ... und dies und das. Makros benutze ich z.B. gar nicht.

Whatever. Hab Feierabend und die Datei, die ich heute umgearbeitetet habe, an mich geschickt. Mal schauen, ob man die irgendwie hier veröffentlichen kann.

- - - Updated - - -

@Bob: Ist alles kein Problem. Dazu könntest Du auch meine Datei erweitern.

Schau Dir mal die Formel an ...

=SUMMENPRODUKT((tbVerwaltung[SNr]=tbVerwaltung[[#Diese Zeile];[SNr]])*tbVerwaltung[Tage])

Au, verflixt ... ich hab zu Hause kein Excel, LibreOffice macht was anderes draus. In Excel müsste das so aussehen (glaube ich)

=SUMMENPRODUKT((tbVerwaltung[SNr]=tbVerwaltung[@SNr])*tbVerwaltung[Tage])

Heißt: Suche in der gesamtem Spalte "SNr" nach der "SNr", die die gleiche ist, wie in dieser Zeile "@SNr" und zähle alle Tage zusammen. Du kannst bei Summenprodukt durchaus auch mehrere Suchkriterien definieren, die Spalte muss bloß vorhanden sein. Zum Beispiel könnte da auch ein Teamname drin sein. Und aus SNr könnte PNr (für Personalnummer) werden.

Ansonsten: Excel kennt in dem Sinne keine dynamischen Tabellen, die sich automatisch verlängern (wie eine Art "Ergebnisliste" oder sowas). Es ist also nicht ohne wirklich viel, viel Gefummel drin, ein Dropdown zu definieren, das nur die Seriennummern von Karten findet, die noch unterwegs sind. Vielleicht geht das mit Pivot-Tabellen, die sind dynamisch. Und wahrscheinlich auch sehr geil - hab mich damit bislang bloß noch nicht beschäftigen müssen.

- - - Updated - - -

Kleiner Nachtrag noch: So praktisch Datentabellen auch sind (filtern, sortieren etc.) - es gibt eine Schattenseite.

Leider kann man innerhalb von Datentabellen keine Matrix-Funktionen benutzen. Hat mich oft schon geärgert und ich hab viel rum gesucht. Nix zu machen. Ich hätte (jetzt bezogen auf die hochgeladene Datei) z.B. etwas gebraucht "Suche das neueste Ausgabedatum von SNr 12345" ... Außerhalb der Datentabelle kann ein Abfrage-Feld bastelt, welches das leistet. Aber als Funktion innerhalb einer Spalte einer Datentabelle - No way.

In meinem Fall hätte ich's gebraucht, rechtzeitig ne automatische Warnung zu bekommen, weil der-und-der geförderte Arbeitsplatz längstens drei Monate leer stehen darf, sonst verfällt er. Wäre geil gewesen. "Nimm alle Mitarbeiter mit dem Förderkennzeichen, das links von mir steht, deren Status nicht 'kein Vertrag' oder 'geplant' ist und gib das späteste Vertragsende aus und zähle drei Monate dazu". Grr. Musste ich auf einer Extra-Seite ohne Datentabelle lösen, nicht so elegant und die Formeln sind so unübersichtlich ...

 

[pufaxx]

SUMMENPRODUKT halte ich für besonders ... hm ... ergiebig. Und die Kombination aus INDEX und VERGLEICH ist toll. Allerdings findet die immer nur den ersten Treffer. Ist jedoch meistens nicht schlimm. Auf der Seite "Erfassung" wird das passende Ergebnis ƩTage in der Seite "Verwaltung" gesucht. Würde dort in der Spalte pro Seriennummer nicht immer dieselbe Zahl stehen, wäre das Ergebnis beim Umsortieren der "Verwaltung"-Tabelle immer ein anderes.

Spiel doch mal ein Bisschen damit rum, Du kannst die Tabellen sortieren und filtern, wie Du willst - Die ermittelten Werte ändern sich dadurch nicht.

Datentabellen finde ich super. Bis auf die Einschränkung in Sachen "Matrix"-Funktionen. Bislang bin ich aber noch nie auf Fälle gestoßen, die ich nicht hätte anders lösen können.

Was ich im Übrigen auch oft mache - alle Hilfsspalten sitzen gebündelt auf der rechten Seite. Wenn ich anfange, meine Dateien zu basteln, sind die "interessanten" Dinge bzw. die Felder, in denen ich etwas eingeben kann, auf der linken Seite. Die Hilfsspalten heißen anfangs immer Hilf1 bis Hilf30 (oder so) - und je nachdem, ob und wofür ich sie brauche, werden sie umbenannt. Die Formeln passen sich freundlicherweise an. So wird dann automatisch aus =WENN([@Hilf1]>0;"ja";"nö") beispielsweise =WENN([@MitarbeiterIstDoof]>0;"ja";"nö") ... wobei dann "ja" bzw. "nö" durch eine Verfahrensweise ersetzt werden kann, die in einer anderen Tabelle hinterlegt ist. Mit ner Spalte "MitarbeiterIstDoof", einer Spalte "WasTun" und mit Zeilen "ja" -> "kündigen", "nö" -> "Festanstellung anbieten", "vielleicht" -> "Schulung buchen" ... weiß der Geier.

INDEX, SVERWEIS, SUMMENPRODUKT, VERGLEICH ... damit kannste echt ordentlich was anstellen.