Clever-Excel-Forum

Seiten: 1 2

Hallo zusammen,

in Excel kann man sehr bequem Dubletten finden und markieren. Mittlerweile benötigt man dafür nicht mal mehr Formeln, S-Verweise o.ä., sondern kann einfach die Bordmittel verwenden, z.B.:
Start > Bedingte Formatierung > Regeln zum Hervorheben von Zellen > Doppelte Werte.

Mein Problem ist jetzt allerdings etwas kniffliger. Der Hintergrund: die IT-Berechtigungen meiner Kollegen sind Kraut und Rüben. Ich will jetzt rausfinden, wer Rechte hat, die die anderen nicht haben. Das Ziel: ich erkenne z.B.: A hat ein Recht, das B fehlt. Da alle die gleichen Rechte haben sollen, möchte ich jetzt die Lücken finden. Aber wie erkenne ich die Unterschiede in den Rechte-Profilen?

Meine Excel-Abzug aus der Datenbank sieht wie folgt aus:
Drei Spalten, jeweils eine für Mitarbeiter A, B und C. Darunter stehen in den Zellen jeder Spalte die eindeutigen Bezeichnungen des User-Rechtes. Was ich rausfinden will: wer hat Rechte, die die anderen nicht haben?

Einfach nur die Dubletten suchen, wäre hier nicht zielführend - weil ich dann zwar erkenne, dass A und B dieselben Rechte haben, ich erkenne aber nicht, dass sie C fehlen.

Mein Zielbild: markiere eine Zelle dann, wenn der Wert im ganzen Arbeitsblatt nur einmal oder zweimal vorkommt. Denn dann gibt es mindestens einen Kollegen, dem eine Berechtigung fehlt.

Oder umgekehrt: markiere einen Wert dann, wenn er drei Mal im Arbeitsblatt vorkommt (dann enthält die Restmenge die Werte, die entweder nur einer oder zwei haben)

Ich hoffe, ich habe mich halbwegs verständlich ausgedrückt...

Beispiel:

Kollege A Kollege B Kolle C
Rechte X Rechte X Recht X
Recht Y Recht Y
Recht Z Recht Z

Die erste Zeile ist OK: hier haben alle dieselben Rechte. In der zweiten Zeile sehe ich: Kollege C fehlt Recht Y und in der dritten Zeile sehe ich: Kollege A fehlt Recht Z.

Alternativ könnte man die Werte auch aphabetisch sortieren und dann bräuchte ich eine Funktion, die immer dann eine leere Zelle erzeugt, wenn links oder rechts der identische Wert fehlt.

Hat jemand eine Idee?

Huh

Hallo,

füge eine Hilfsspalte (D) ein mit

Code:
=und(a2; b2;c2)

mfg

Den Vorschlag verstehe ich ehrlich gesagt nicht - kannst Du die Zielsetzung erklären?

(Habe es einfach mal stumpf ausprobiert - Fehlermeldung sagt: #Wert!)

Die obige Darstellung ist vereinfacht, um die Zielrichtung zu illustrieren. Näher am Ist-Zusand ist:

Kollege A Kollege B Kollege C
Recht u Recht v Recht x
Recht v Recht x Recht z
Recht x
Recht z

D.h. zeilenweise vorgehen bringt mich nicht weiter, da gleichartige Werte nicht in derselben Zeile stehen.

Du hast Recht.

Aber um einen Text-Vergleich durchführen zu können, müssen die echten String bekannt sein.

Vielleicht reicht auch schon eine Zähl-Funktion, die die Datensätze markiert, die drei Mal vorhanden sind - den Rest könnte ich mir dann manuell ansehen, das würde schon mal etwas helfen.

Das scheint zu gehen mit:
Startvorlagen / Formatvorlagen / Bedingte Formatierung
dort: "Neue Regel - Formel zur Ermittlung der zu formatierenden Zellen"

Die Formel müsste dann ungefähr so aussehen:

=ZÄHLENWENN( ... )=3

Dann die Funktion Formatieren / Ausfüllen auswählen u. z.B. rot als Farbe auswählen

Nach langem Zögern hier doch noch ein VBA-Code:

Code:
Sub Nicht_vorhanden()

Ar = Sheets(2).UsedRange

For j = 1 To 3 ' Spalte = User

    For r = 2 To UBound(Ar)

        Set Rng = Columns(j).Find(Ar(r, 1), , xlValues, xlWhole)

        If Rng Is Nothing Then

            lr = Cells(Rows.Count, j + 4).End(xlUp).Row + 1

            Cells(lr, j + 4) = Ar(r, 1)

        End If

    Next r

Next j

End Sub

Die Daten sind im Workbook gezeigt.

Hier eine Lösung mit Power Query. Dabei ist die Anzahl der User, deren Berechtigungen geprüft werden sollen beliebig.

Die Lösung habe ich in 2 Varianten erstellt.

Zum einen mittels direktem lesen aus dem Tabellenblatt "Ausgangsdaten". Dabei wurde der Input bei dem Ersten Import automatisch in eine formatierte Tabelle umgewandelt.

Beim Einfügen anderer Ausgangsdaten (mehr/weniger Userspalten) kann dies evtl. Probleme verursachen.

Deshalb wird bei der 2. Variante aus einer Mappe, vom Tabellenblatt "Ausgangsdaten_2", gelesen. Wobei es sich dabei auch um die aktuelle Mappe handeln kann. Bevor dann jedoch eine Aktualisierung der PQ-Abfrage, auch geänderte Daten berücksichtigt, muss die Mappe vorher gesichert werden.

Weiterhin muss im Tabellenblatt "Pfad_&_Datei" der Pfad und Dateiname angepasst werden, damit die 2. Abfrage Ergebnisse liefern kann.f

Super, danke Euch für die Mühe!

Ich bin leider noch nicht zum Testen gekommen, gebe aber auf jeden Fall noch eine Rückmeldung!

Hallo celdummy,

anbei eine etwas umständliche Lösung mit Zählenwenn. Hierbei muss jedoch dreimal umgestellt werden um alle Möglichkeiten bei 3 Mitarbeitern abzudecken/auch wirklich alle Rechte die Fehlen zu finden. Ich hoffe ist selbsterklärend.

-> Ich bin Euch noch Feedback schuldig, hier ein kurzer Zwischenstand:

Beim Makro habe ich eine Fehlermeldung bekommen – wahrscheinlich keine große Sache. Da ich mit Markos aber noch nie zu tun hatte, war ich erst mal aufgeschmissen. Ich habe aber einen Kollegen, der sich damit auskennt und werde den ansprechen, sobald er aus dem Urlaub zurück ist. Das ist vermutlich einfacher als Fehlersuche per Ferndiagnose.

Power Query kannte ich bis dato noch gar nicht und diese Funktionalität finde ich in Excel auch nicht. Nach dem, was ich per Google zusammengesucht habe, scheint es so zu sein, dass das ein Feature ist, dass in Excel nicht serienmäßig vorhanden ist u. separat installiert werden muss – dazu fehlen mir die Userrechte.

Ich habe mir jetzt zwischenzeitlich erst Mal mit Bordmitteln weitergeholfen, um die fraglichen Datensätze weiter einzugrenzen:

- Tabelle mit drei Spalten gebaut:
1) Name Person
2) Name Berechtigung
und die vorhandenen Daten reinkopiert
3) Counter eingefügt, der zählt, wie oft das Recht vergeben wurde: =ZÄHLENWENN(B:B;B2)
(Beispiel für die zweite Zeile in der Tabelle)

Damit wird mir in der letzten Spalte ausgeworfen, wenn ein Recht 1x, 2x oder 3x in der Gesamtmenge vorhanden ist und ich kann schon mal alles rausfiltern, was unkritisch ist (weil 3x vorhanden).

Nachteil dieser Methode: in der Restmenge muss man Recht für Recht durchsehen, wem das Recht fehlt. Evtl. finde ich dafür auch noch eine Lösung.

Danke auf jeden Fall noch mal für Eure Ideen!

Seiten: 1 2

celdummy

Fennek

celdummy

Fennek

celdummy

Fennek

wisch

celdummy

Stoffo

celdummy