Doppelte Einträge in einer Spalte überprüfen

Computer: Software & Programmierung Office Software
1

Hallo ich habe eine Exceltabelle für einen Einsatzplan.
Wir haben Dienste nummeriert 1 bis 9.
Diese sind pro Tag in einer Spalte mit verschiedenen Mitarbeitern. z.B Spalte H, ab H7 bis H39
Jetzt möchte ich unterhalb H39 eine Kontrollzeile anfügen in der gemeldet wird, wenn in der Spalte H ein Dienst doppelt aufgeführt ist.
Ich habe bereits eine Kontrollzeile mit der Kontrollsumme 45, wenn diese erscheint sehe ich, dass alle Dienste 1; 2; 3; 4; 5; 6; 7; 8; und 9 vorhanden sind.
Das Problem ist, wenn sich zwei Fehler einschleichen, kann die Kontrollzahl stimmen, aber trotzdem zwei Dienste falsch eingetragen sein.
Wie kann ich die Spalte überprüfen auf doppelte Dienstzahlen. Danke für Eure Hilfe Gruss Karl

2

Nachfrage

Wir haben Dienste nummeriert 1 bis 9.
Diese sind pro Tag in einer Spalte mit verschiedenen
Mitarbeitern. z.B Spalte H, ab H7 bis H39
Jetzt möchte ich unterhalb H39 eine Kontrollzeile anfügen in
der gemeldet wird, wenn in der Spalte H ein Dienst doppelt
aufgeführt ist.

Hallo Karl,

in H7:H39 gibt es immer nur 9 Einträge? Und du willst in H40 überprüfen ob es exakt 9 sind und alle 9 Ziffern vertreten sind?

Gruß
Reinhard

3

Hallo Karl,

in H7:H39 gibt es immer nur 9 Einträge? Und du willst in H40 überprüfen ob es exakt 9 sind und alle 9 Ziffern vertreten sind?
Reinhard

Ich gehe 'mal davon aus, daß Reinhard Deinen Wunsch erraten hat. Dann würde ich es so machen:
in H40 kommt die Formel

=(ZÄHLENWENN(H7:H39;"1")-1)\*1+(ZÄHLENWENN(H7:H39;"2")-1)\*2+(ZÄHLENWENN(H7:H39;"3")-1)\*3+(ZÄHLENWENN(H7:H39;"4")-1)\*4+(ZÄHLENWENN(H7:H39;"5")-1)\*5+(ZÄHLENWENN(H7:H39;"6")-1)\*6+(ZÄHLENWENN(H7:H39;"7")-1)\*7+(ZÄHLENWENN(H7:H39;"8")-1)\*8+(ZÄHLENWENN(H7:H39;"9")-1)\*9

  • in H41 kommt die Formel

    =WENN(ANZAHL(H7:H39)>10;„Mehrere Fehler (“&ANZAHL(H7:H39)-9&" Dst. zuviel)";WENN(UND(H40>0;H40

    Erklärung:

    • Die Formel in H40 besteht aus neun ZÄHLENWENN-Funktionen nach dem Muster

      =(ZÄHLENWENN(H7:H39;„1“)-1)*1

    Die zählt, wieviele Einsen in der Liste sind, und zieht davon eins ab. Das heißt, das Ding wird 0, wenn Dienst1 korrekt nur einmal eingetragen ist. Ist mehr als ein Dienst1 drin, liefert die Klammer mit der ZÄHLENWENN-Funktion eine Zahl größer 0. Die wird mit der 1 multipliziert und zeigt damit an, daß eine 1 zuviel ist.

    • Diese 9 Funktionen werden aufaddiert und ergeben bei korrektem Eintragen 0, bei Fehlern einen Wert > 0.

    • Die Formel in H41 guckt sich die Zahl in H40 an. Ist sie 0, ist alles ok. Ist sie größer 0, stimmt 'was nicht. Dann wird unterschieden:

    • sind mehr als 10 Einträge in der Liste, sind’s auf jeden Fall mehrere Fehler. Dann kommt die Meldung: „Mehrere Fehler (x Dst. zuviel)“

    • sind 10 Einträge in der Liste, kommt die Meldung „Dienst x doppelt?“. Das soll die Suche erleichtern.

    • sonst kommt „o.k.“

    Ich hab ein bißchen herumgespielt und es scheint alles zu funktionieren.

    Wenn aber nicht immer alle Dienste drin stehen müssen, dann geht diese Lösung natürlich nicht.

    HTH.

    Markus
    ____________
    Und Sterne sammel ich noch immer.

4

Grüezi Charly

Wir haben Dienste nummeriert 1 bis 9.
Diese sind pro Tag in einer Spalte mit verschiedenen
Mitarbeitern. z.B Spalte H, ab H7 bis H39
Jetzt möchte ich unterhalb H39 eine Kontrollzeile anfügen in
der gemeldet wird, wenn in der Spalte H ein Dienst doppelt
aufgeführt ist.
Ich habe bereits eine Kontrollzeile mit der Kontrollsumme 45,
wenn diese erscheint sehe ich, dass alle Dienste 1; 2; 3; 4;
5; 6; 7; 8; und 9 vorhanden sind.
Das Problem ist, wenn sich zwei Fehler einschleichen, kann die
Kontrollzahl stimmen, aber trotzdem zwei Dienste falsch
eingetragen sein.
Wie kann ich die Spalte überprüfen auf doppelte Dienstzahlen.

Ich hätte hier die folgende nette Formel anzubieten die die die Anzahl der doppelten Werte ausgibt, also ob 1, 2, 3 oder mehr Dienste mehrfach vergeben wurden. Wenn das Ergebnis der Formel ‚0‘ ist ist jeder Dienst max. einmal eingetragen.

=SUMME(–(HÄUFIGKEIT($H$7:blush:H$39;H7:H39)>1))

und mit der folgenden Variation kannst Du ermitteln wie viele der 9 Dienste eingetragen sind:

=SUMME(HÄUFIGKEIT($H$7:blush:H$39;$H$7:blush:H$39))

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
5

OT Nachfrage

Ich hätte hier die folgende nette Formel anzubieten die die
die Anzahl der doppelten Werte ausgibt, also ob 1, 2, 3 oder
mehr Dienste mehrfach vergeben wurden. Wenn das Ergebnis der
Formel ‚0‘ ist ist jeder Dienst max. einmal eingetragen.
=SUMME(–(HÄUFIGKEIT($H$7:blush:H$39;H7:H39)>1))
und mit der folgenden Variation kannst Du ermitteln wie viele
der 9 Dienste eingetragen sind:
=SUMME(HÄUFIGKEIT($H$7:blush:H$39;$H$7:blush:H$39))

Grüezi Thomas,

kamst du auf Häufigkeit weil du das hier für mich erstaunlicherweise verstanden hast?
Oder durch Austestung von Häufigkeit?
Oder irgendwo gelesen?

Gruß
Reinhard

6

Grüezi Reinhard

Ich hätte hier die folgende nette Formel anzubieten die die
die Anzahl der doppelten Werte ausgibt, also ob 1, 2, 3 oder
mehr Dienste mehrfach vergeben wurden. Wenn das Ergebnis der
Formel ‚0‘ ist ist jeder Dienst max. einmal eingetragen.
=SUMME(–(HÄUFIGKEIT($H$7:blush:H$39;H7:H39)>1))
und mit der folgenden Variation kannst Du ermitteln wie viele
der 9 Dienste eingetragen sind:
=SUMME(HÄUFIGKEIT($H$7:blush:H$39;$H$7:blush:H$39))

kamst du auf Häufigkeit weil du das
hier für mich erstaunlicherweise verstanden hast?

Einerseits ja, andererseits ist diese Funktion für statistische Auswertungen quasi ‚Standard‘, wenn die Klassengrössen erstmal bekannt sind. Im Grunde ist es nichts anderes als eine sehr komfortables ZÄHLENWENN()-Derivat :wink:
Ich kenne sie seit den Anfängen als ich mich mit Excel befasst habe.

Oder durch Austestung von Häufigkeit?

Auch das, ja…

Oder irgendwo gelesen?

…und das natürlich auch… :wink:

(irgendwie muss ich meinen MVP doch herausstreichen… :wink:)

Zugegeben - so wie sie hier eingesetzt wird ist sie etwas quer verwendet, aber sie tut es bestens - vor allem gefällt mir daran, dass es keine Matrix-Formel ist und dass sie sehr kurz und so auch rasch an andere Bereiche anzupassen ist.

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
7

Hallo Reinhard
Ja so ist es. Aber wesentlich ist, wie kann ich gewarnt werden, wenn ein Dienst 2x vorkommt.
Gruss Karl

8

Ja so ist es. Aber wesentlich ist, wie kann ich gewarnt
werden, wenn ein Dienst 2x vorkommt.

Hallo Karl,

=WENN(ISTFEHLER(MODALWERT($H$7:blush:H$39));„Ok“;„Dopp“)
=WENN(ANZAHL($H$7:blush:H$39)9;„Zahlanzahl falsch“;„Ok“)
=WENN(ANZAHL2($H$7:blush:H$39)9;„Zahlanzahl falsch“;„Ok“)

Formel1 prüft auf Doppelte.

Formel2 und Formel3 prüfen b die Anzahl 9 beträgt. Sie unterscheiden sich darin wie sie auf Buchstaben- oder Zahleneingaben in Zellen reagieren.

Kannst auch alles in eine Formel packen:

=WENN(UND(ISTFEHLER(MODALWERT($H$7:blush:H$39));ANZAHL($H$7:blush:H$39)=9;SUMME($H$7:H39)=45);„Ok“;„Fehler“)

Gruß
Reinhard

9

Hallo Reinhard
Du bist super!!!
=WENN(ISTFEHLER(MODALWERT($H$7:blush:H$39));„Ok“;„Dopp“) Diese Formel passt für mein Problem am besten, die $ Zeichen kann ich weg lassen, damit ich die Formel vom 1. bis 31. durch kopieren kann.
Jetzt habe ich noch ein weiteres Problem. Bei den Dienstnummern habe ich 5a und 5b Montag bis Freitag, sowie 15a und 15b Samstags. Diese werden bei dieser Formel aber nicht beachtet, wie ich bemerkt habe.
Für mich ist es richtig dass eigentlich nur Zahlen beachtet werden, weil ich auch F für Ferien und B für Büro, R für Ruhetag in den Spalten habe. Diese würden ja eine Fehler Anzeige generieren.
Wie kann ich also in der Formel diese Ausnahme mit 5a, 5b sowie 15a und 15b mit einschliessen, ohne dass diese F, B,und R oder andere Buchstaben eine Fehleranzeige auslösen?
Mit Deinen Formel Vorschlägen 2 + 3 wäre das der Fall.
Ich weiss, ist etwas viel verlangt, aber ich bin zuversichtlich, dass Du eine Lösung weisst :wink: Herzliche Grüsse und vielen Dank Karl

10

Hallo Charly,

Dienstnummern habe ich 5a und 5b Montag bis Freitag, sowie 15a
und 15b Samstags.

?, verstehe ich nicht.

Für mich ist es richtig dass eigentlich nur Zahlen beachtet
werden, weil ich auch F für Ferien und B für Büro, R für
Ruhetag in den Spalten habe.

5a ist keine Zahl.

Wie kann ich also in der Formel diese Ausnahme mit 5a, 5b
sowie 15a und 15b mit einschliessen, ohne dass diese F, B,und
R oder andere Buchstaben eine Fehleranzeige auslösen?

Wie wäre es mit Vba, das ist für mich einfacher :smile:

Alt+F11, Einfügen—Modul, Code reinkopieren, ggfs. anpassen, Editor schließen.

Anwendung in Excel wie alle Funktionen dort, Einfügen—Funktion—Benutzdefiniert—Pruefe
oder direkt in Zelle schreiben:
=Pruefe(H7:H39)

Das erste IF… prüft ob die Anzahl befüllter Zellen im Bereich 9 ist

Das zweite IF… stellt nur sicher daß nur befüllte Zellen geprüft werden.

Das dritte IF… prüft ob jeder Zellwert exakt in „#1#2#3#4#5#6#7#8#9#5a#5b#15a#15b#“ enthalten ist.
Nach dem Muster kannste ja die „Erlaubten“-Liste beliebig anpassen.

Das vierte IF… prüft ob jeder Zellwert nur einmal im Bereich vorkommt.

Erst wenn alles stimmt wird „Ok“ angezeigt, sonst „Fehler“

Gruß
Reinhard

Option Explicit
'
Function Pruefe(ZellBereich As Range) As String
Dim Zelle As Range
Const Vergl As String = "#1#2#3#4#5#6#7#8#9#5a#5b#15a#15b#"
Pruefe = "Fehler"
If Application.CountA(ZellBereich) 9 Then Exit Function
For Each Zelle In ZellBereich
 If Zelle.Value "" Then
 If InStr(Vergl, "#" & Zelle.Value & "#") 1 Then Exit Function
 End If
Next Zelle
Pruefe = "Ok"
End Function
11

Hallo Richard
Danke für die Vba „Formel“. Ich habe diese modifiziert, damit alle Dienstvariationen die es geben kann als Fehler angezeigt werden.

Dienstkombinationen wie z.B. 1+2, 3, 4+8, 5ab, 6, 7, 9 sind möglich und ändern sich fast täglich. Deshalb habe ich die Zeile
If Application.CountA(ZellBereich) 9 Then Exit Function
entfernt, dadurch wird nicht jedesmal ein Fehler angezeigt.

Jetzt funktioniert alles recht gut.
Zudem habe ich in Zeile 39 diese Formel
=WENN(SUMME(H7:H30)=0;"";pruefe(H7:H30))
eingegeben, damit ist in Zeile 39 kein OK, solange nichts in der Spalte steht.
Nochmals herzlichen Dank und liebe Grüsse
Karl

12

Hallo Fritz *gg*,

Hallo Richard

okay, ich kenn sogar einen Richard, ich leite ihm deinen Beitrag weiter *grins*

Dienstkombinationen wie z.B. 1+2, 3, 4+8, 5ab, 6, 7, 9 sind
möglich und ändern sich fast täglich. Deshalb habe ich die
Zeile
If Application.CountA(ZellBereich) 9 Then Exit Function
entfernt, dadurch wird nicht jedesmal ein Fehler angezeigt.

Vielleicht wäre es für dich günstig, auf einem Hilfsblatt was du ja ausblenden kannst damit es nicht nervt, in einer Spalte alle erlaubten Schichteinträge aufzulisten.
Dann in H Daten–Gültigkeit–Liste.

Und, völlig ungetestet, nur ein spontaner Gedanke, bei Gültigkeit kann man auch eine Formel eingeben, da halt eine Formel basteln die a prüft ob die Eingabe in der Liste steht und gleichzeitig ob nicht schon 9 Werte in H7:H39 stehen.
Nur ne Idee.

Gruß
Reinhard

13

Oh…sorry
Reinhard
Ich hab mich schon zu früh gefreut.
Als ich auf der ganzen Jahresmappe die Änderungen in den jeweiligen Monatsblättern geändert hatte, konnte ich nicht speichern. Das heisst ich musste als . xlsm abspeichern.
Dann nach dem ich neu geöffnet hatte, war in der Kontrollzeile nach dem ersten Eintrag eines Dienstes eine Fehlermeldung mit #Name? also kein OK kein Fehler!!! Was ist geschehen?? Ich bin am verzweifeln!!
Wie es scheint bleiben wir noch länger in Kontakt :smile:

Übrigens sind nicht konstant 9 Einträge pro Tag, den je nach Dienstkombination sind es auch nur 7 oder 8
Ich traue mich fast nicht mehr zu fragen, ob du eine Lösung weisst.
Herzliche Grüsse Karl ähh Fritz :wink: