Neue dynamische Beispieldatei verfügbar

Hallo und ein sehr spätes Frohes Neues!

Es wurde Zeit für eine neue Datei mit Namen, Geschlecht, Alter, Abteilung etc.
Ich braue immer wieder mal Daten, um diese zur Anonymisierung in bestehende Listen einzufügen oder um Excel-Techniken an Daten zu demonstrieren.

Ich habe nur das Geschlecht und den Namen vorgegeben, die übrigen Felder werden bei einer Aktualisierung durch F9, insofern die Berechnung auf manuell eingestellt ist, aktualisiert.

namen01_thehos

Erläuterung der Spalten

PNr – Die Personalnummer sollte mindestens 8 Stellen betragen. Die ersten sechs Stellen bestehen aus dem unformatierten Einstellungsdatum. Die weiteren Stellen sind zufällig vergeben. Doppelte Werte werden rot hervorgehoben.
Formel: =LINKS(TEXT([@[angestellt seit]];0)&ZUFALLSBEREICH(1000000000;99999999999);rngPNr)
Name – fix vorgegeben
Gebdatum – ein zufälliges Datum in den Grenzen der beiden benannten Zellen rngDatumFrüh und rngDatumSpät. Mit rngFaktor kann das Alter zu jung oder alt verschoben werden.
Formel: =RUNDEN(ZUFALLSBEREICH(rngDatumFrüh;rngDatumSpät)*rngFaktor;0)
Geschlecht – fix auf m oder w
Alter
Nachname – fix
Vorname – fix
Titel – Ein Titel rngTitel wird nur zufällig vergeben. Dafür muss ein bestimmtes Alter rngMindestalter erreicht werden und ein Zufallswert bis zur Zahl rngTitelGrenze muss eine bestimmte Größe rngTitelGrenze haben.
Formel: =WENN(UND([@Alter]>=rngMindestalter;ZUFALLSBEREICH(1;rngWerteBis)>=rngTitelGrenze);rngTitel;““)
Zufallszahl – Eine Zufallszahl zwischen 1 und 20. Dient z.B. zur Ermittlung der Abteilung.
Formel: =ZUFALLSBEREICH(1;20)
Abteilung – Zufällig Zuweisung der Abteilung. Dadurch auch Basiswert für Gehalt.
Formel: =SVERWEIS([@Zufallszahl];tabAbteilung;2;FALSCH)
Gehalt – Gehalt wird zufällig um Basisgehalt über Parameter rngGehaltMin und rngGehaltMax gestreut und dann noch mit Alters- (rngEuroLebensjahr) und Geschlechtsfaktor (rngGehaltF für Frauen und rngGehaltM für Männer) multipliziert. Somit lässt sich Diskriminierung bzw. Lohnungerechtigkeit simulieren. Gehalt wird auf zwei Stellen hinter dem Komma gerundet.
Formel: =RUNDEN(SVERWEIS([@Zufallszahl];tabAbteilung;3;FALSCH)*ZUFALLSBEREICH(rngGehaltMin*100;rngGehaltMax*100)/100+[@Alter]*rngEuroLebensjahr*WENN([@Geschlecht]=“w“;rngGehaltF;rngGehaltM);2)
angestellt seit – Über rngBZ1 und rngBZ2 wird gesteuert, ob die Mitarbeiter eher lang- oder kurzfristig angestellt sind. Es wird ein jeweils Monatserster ermittelt. Für die maximale Betriebszugehörigkeit wird der Wert 18 rngBZAlter abgezogen.
Formel: =ABRUNDEN(MONATSENDE(HEUTE()-([@Alter]-rngBZAlter)*ZUFALLSBEREICH(rngBZ1;10)*ZUFALLSBEREICH(rngBZ2;10)/100*365;0)+1;0)
BZ in Jahren – Umrechnung der Betriebszugehörigkeit in Jahren.
Formel: =RUNDEN(BRTEILJAHRE([@[angestellt seit]];HEUTE());1)
Filter – Sollen nicht alle Daten in der Pivot-Tabelle berücksichtigt werden, können dort nur die 1er-Werte gefiltert werden. Die ersten Werte der Liste bis zur Zeile rngMenge werden dann mit 1 versehen.
Formel: =WENN(ZEILE()-5<=rngMenge;1;0)

Die aktuellen Standardeinstellungen führen zu folgender Verteilung bei Alter, Lohn und Betriebszugehörigkeit.

namen02_thehos
Bild 2: Verteilung von Alter und Geschlecht in Kohorten von jeweils 10 Jahren

namen03_thehos
Bild 3: Durchschnittseinkommen für Männer und Frauen pro Alterskohorte
namen04_thehos
Bild 4: Durchschnittliche, minimale und maximale Betriebszugehörigkeit bei Männern und Frauen pro Alterskohorte

Über die Veränderung der Parameter kann die Verteilung von z.B. Alter und Lohn verändert werden. Nach einer Modifikation mit F9 die Formeln aktualisieren und anschließend einen Rechtsklick auf eine der Pivot-Tabellen zur Aktualisierung der Daten.
namen05_thehos
Bild 5: Tabellenblatt Parameter mit Basiseinstellungen

Und wie soll ich die Datei jetzt verwenden?

  1. Aktualisiere zunächst die Tabelle und dann die Pivot-Tabellen.
  2. Prüfe, ob es doppelte Werte gibt und ob dir die Diagramme zusagen.
  3. Dann markiere die Daten vom ersten Tabellenblatt komplett mit 2x STRG + A
  4. und kopiere diese als Werte in einen neuen Bereich.
    Somit sind die kopierten Daten frei von Formeln und können ohne permanente Änderungen genutzt werden.

Und hier gibt es die Excel-Datei im Format XLSX: Beispielnamen.xlsx

Kunden des USB-Sticks finden die Datei auch immer in aktualisierter und eventuell erweiterter Fassung im Verzeichnis zusätzliche Excel-Dateien

Die Datei wird sicher noch in einem späteren Video beschrieben werden.
Die Datei und die Daten können in unveränderter Form frei für Lehrzwecke verwendet werden.

Um die Daten in Serienbriefen etc. nutzen zu können wäre sicher noch die Ergänzung um Straße, PLZ, Ort, Land und E-Mail-Anschrift denkbar…

Freue mich auf Feedback
Beste Grüße

Andreas

P.S.: Am 24. März 2017 findet in Bochum seit langer Zeit mein erstes öffentliches Seminar zum Thema „Excel – Tabellen und Tabellenfunktionen“ statt. Am 07. April 2017 folgt das Seminar „Excel – Pivot-Tabellen“.
Mehr Informationen folgen in Kürze in diesem Blog und auf meiner Facebook-Seite https://www.facebook.com/excelthehos/

Als Tagungsort habe ich einen Raum des DJH in der Bochumer Innenstadt (Bermuda-Dreieck) inklusive Bewirtung gebucht.
Wir starten pünktlich um 9 Uhr und beschließen die Veranstaltung gegen 17:30 Uhr.
Wichtig: Die Teilnehmer bringen bitte ihre eigenen Notebooks bzw. Firmennotebooks mit Excel 2010, 2013 oder 2016 (PC-Version) mit. Somit arbeiten alle in vertrauter Umgebung.

Die Teilnahmegebühr beträgt pro Person/Tag EUR 300,00 zzgl. 19% Mehrwertsteuer (EUR 57,00) -> Bruttopreis EUR 357,00. Der Seminarpreis beinhaltet: Gebühr für das Seminar, Skript zur Schulung, Pausengetränke,
Seminarteilnehmer können zusätzlich meinen USB-Stick mit mehr als 700 Excel-Videos zum Preis von EUR 50 zzgl. 19% Mehrwertsteuer (EUR 9,50) -> Bruttopreis 59,50 erwerben. Der reguläre Bruttopreis für den Stick beträgt aktuell EUR 109,00.

Bei Interesse an den Seminaren freue ich mich über eine E-Mail an thehos@at-training.de. Bitte den Betreff: Interesse Seminar verwenden.

Im Anschluss steht der Raum noch für einen etwaigen Excel-Stammtisch zur Verfügung. Hierzu werde ich gesondert einladen.

Ein Kommentar

  1. Artur

    Hallo Andreas,
    mal wieder was neues gelernt, bisher noch nie mit Zufallsbereich gearbeitet, sondern immer aus dem Bauch heraus getippt 🙂
    Freue mich schon auf das kommende Video hierzu.

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s