Dienstag, 1. Dezember 2020
22:45 Uhr
22:45 Uhr
Zufallswerte in Excel Zellen per Zufallszahlszahl und Zufallsbereich aber auch in Access je Datenzeile einer Access Abfrage generieren
Ausgangslage:
Manchmal kann es, sei es für Beispiele hier im Blog oder für eine Anonymisierung einer Statistik, erforderlich sein Daten zu anonymisieren oder zumindest zu pseudonymisieren.
Hier gibt es unterschiedliche Formeln in Office je nachdem ob diese in Excel oder Access genutzt werden sollen.
Dabei generiert Excel diese Zahlen basierend auf den Mersenne Twister-Algorithmus (MT19937 siehe "Eintrag auf Wikipedia") wodurch Pseudozufallszahlen die als zufällig erscheinen generiert werden.
In einer Datentabelle mit zehn Einträgen (von ID 1 bis 10) und jeweils einen Wert von 100 werden dazu je folgende Zufallszahlen generiert.
Spalte Zufallszahl
Durch die Formel =RUNDEN(ZUFALLSZAHL()*100;2) wird durch ZUFALLSZAHL() eine Zahl größer oder gleich 0 aber kleiner als 1 generiert und diese mit 100 multipliziert und auf 2 Nachkommastellen gerundet.
Spalte Zufallsbereich
Der Zufallsbereich wird durch die Formel =ZUFALLSBEREICH(1;100) definiert als ganzzahlige Zahl (ohne Nachkommastellen) zwischen 1 und 100.
So sind in Excel relativ schnell entsprechende "Zufallszahlen" können dann als Werte für Beispiele herangezogen werden.
Sinnvollerweise werden diese dann kopiert und als Inhalte Einfügen als Werte wieder eingefügt, andernfalls aktualisieren sich diese Zahlen jedes Mal aufs Neue sobald eine andere Berechnung im Tabellenblatt erfolgt.
Ein Grund warum Controller Excel mögen, dürfte wohl auch daran liegen, dass hier schnell gute Ergebnisse erreicht werden können.
Zur identischen Datenzeile ID 1 bis 10 und jeweils die Werte 100 je Datenzeile möchte ich nun aber ebenfalls Zufallszahlen erhalten.
Dazu gibt es in Access die an der Formel RND in VBA angelehnte Formel ZZG() die in Abfragen verwendet werden kann.
Wird diese Formel ohne Parameter (also nur als ZZG() ) verwendet, so wird als Basis zur Berechnung von Zufallswerten der Systemzeitgeber als Startwert zur Berechnung eines Zufallswertes verwendet. Allerdings wird für jeden Zufallswert der gleiche Startwert verwendet, so dass in der Abfrage die Zufallswerte wie folgt in der Spalte ZZG ausgegeben werden:
Hier sind also für jede Datenzeile der gleiche Zufallswert ausgegeben, was nun zwar eine Anonymisierung aber nicht das gewünschte Ergebnis von unterschiedlichen Zufallswerten je Datenzeilen verursacht. Die Zahlen ändern sich zwar bei jeder erneuten Abfrage, bleiben aber für jeden Eintrag identisch.
Daher habe ich nun versucht mit zwei Schritten die Abfrage etwas anzupassen um letzten Endes doch noch Zufallszahlen je Eintrag zu erhalten.
Insgesamt habe ich hier zwei weitere Felder eingefügt auf deren Ergebnis ich nun eingehen mag.
Spalte ZZG Festwert
ZZG Festwert: RUNDEN ( ZZG(1) * 100 );2 )
Hier gebe ich der Funktion ZZG den Startwert 1 und multipliziere dieses mit 100 um dann die Zufallszahl auf 2 Nachkommastellen zu runden.
Ausgeführt liefert die Abfrage folgende Ausgabe:
Im Ergebnis erhalte ich nun gut lesbare Zahlen pro Eintrag, aber da hier nun statt des Systemzeitgeber eine feste Zahl verwendet worden ist, ist das Ergebnis leider erentu identisch. Beim erneuten Ausführen der Abfrage erhalte ich wiederum neue Werte, die aber ebenfalls fix sind.
Spalte ZZG Bezug
ZZG Bezug: RUNDEN ( ZZG( [Zahl] ) * 100 );2 )
Hier lese ich nun den Wert in der Spalte Zahl aus um diese als Startwert zu nehmen. Auch wenn die Zahlen jeweils identisch sind, wird hier doch für jede Zeile eine neue Zufallszahl berechnet, so dass die Werte in der Spalte ZZG Bezug nun endgültig unterschiedlich sind.
Im Beispiel soll hier "Bestanden", "Nicht bestanden" oder "Neuer Versuch per Zufall auf die einzelnen Einträge verteilt werden.
Dazu habe ich folgende Spalten in die Abfrage eingefügt:
Natürlich können hier auch größere Bandbreiten durch Erhöhung der Abstände ZZG Bezug bis 100 oder auch andere Bestandteile für die Wenn Abfrage genommen werden.
Immerhin sind solche Fragen, wie auch die Konverttierung einer "als Tabelle formatierten" Datengrundlage in einen Bereich wie im Artikel "Daten in Excel intelligenter formatieren 📊 als intelligente Tabellen bzw. als Tabelle formatieren" beschrieben immer mal wieder Grundlage für spannende Artikel.
Hinweis: Aktuelle Buchempfehlungen besonders SAP Fachbücher sind unter Buchempfehlungen inklusive ausführlicher Rezenssionen und Bestellmöglichkeit zu finden.
Manchmal kann es, sei es für Beispiele hier im Blog oder für eine Anonymisierung einer Statistik, erforderlich sein Daten zu anonymisieren oder zumindest zu pseudonymisieren.
Hier gibt es unterschiedliche Formeln in Office je nachdem ob diese in Excel oder Access genutzt werden sollen.
Zufallszahlen in Excel
Für meine Beispielzahlen in Excel verwende ich meistens entweder die Formel ZUFALLSZAHL() oder ZUFALLSBEREICH(Untere_Zahl;Obere_Zahl) um hier entsprechende Zahlen automatisch errechnen zu lassen.Dabei generiert Excel diese Zahlen basierend auf den Mersenne Twister-Algorithmus (MT19937 siehe "Eintrag auf Wikipedia") wodurch Pseudozufallszahlen die als zufällig erscheinen generiert werden.
In einer Datentabelle mit zehn Einträgen (von ID 1 bis 10) und jeweils einen Wert von 100 werden dazu je folgende Zufallszahlen generiert.
Spalte Zufallszahl
Durch die Formel =RUNDEN(ZUFALLSZAHL()*100;2) wird durch ZUFALLSZAHL() eine Zahl größer oder gleich 0 aber kleiner als 1 generiert und diese mit 100 multipliziert und auf 2 Nachkommastellen gerundet.
Spalte Zufallsbereich
Der Zufallsbereich wird durch die Formel =ZUFALLSBEREICH(1;100) definiert als ganzzahlige Zahl (ohne Nachkommastellen) zwischen 1 und 100.
So sind in Excel relativ schnell entsprechende "Zufallszahlen" können dann als Werte für Beispiele herangezogen werden.
Sinnvollerweise werden diese dann kopiert und als Inhalte Einfügen als Werte wieder eingefügt, andernfalls aktualisieren sich diese Zahlen jedes Mal aufs Neue sobald eine andere Berechnung im Tabellenblatt erfolgt.
Ein Grund warum Controller Excel mögen, dürfte wohl auch daran liegen, dass hier schnell gute Ergebnisse erreicht werden können.
Zufallszahlen in Access
Zur identischen Datenzeile ID 1 bis 10 und jeweils die Werte 100 je Datenzeile möchte ich nun aber ebenfalls Zufallszahlen erhalten.
Dazu gibt es in Access die an der Formel RND in VBA angelehnte Formel ZZG() die in Abfragen verwendet werden kann.
Wird diese Formel ohne Parameter (also nur als ZZG() ) verwendet, so wird als Basis zur Berechnung von Zufallswerten der Systemzeitgeber als Startwert zur Berechnung eines Zufallswertes verwendet. Allerdings wird für jeden Zufallswert der gleiche Startwert verwendet, so dass in der Abfrage die Zufallswerte wie folgt in der Spalte ZZG ausgegeben werden:
Hier sind also für jede Datenzeile der gleiche Zufallswert ausgegeben, was nun zwar eine Anonymisierung aber nicht das gewünschte Ergebnis von unterschiedlichen Zufallswerten je Datenzeilen verursacht. Die Zahlen ändern sich zwar bei jeder erneuten Abfrage, bleiben aber für jeden Eintrag identisch.
Daher habe ich nun versucht mit zwei Schritten die Abfrage etwas anzupassen um letzten Endes doch noch Zufallszahlen je Eintrag zu erhalten.
Insgesamt habe ich hier zwei weitere Felder eingefügt auf deren Ergebnis ich nun eingehen mag.
Spalte ZZG Festwert
ZZG Festwert: RUNDEN ( ZZG(1) * 100 );2 )
Hier gebe ich der Funktion ZZG den Startwert 1 und multipliziere dieses mit 100 um dann die Zufallszahl auf 2 Nachkommastellen zu runden.
Ausgeführt liefert die Abfrage folgende Ausgabe:
Im Ergebnis erhalte ich nun gut lesbare Zahlen pro Eintrag, aber da hier nun statt des Systemzeitgeber eine feste Zahl verwendet worden ist, ist das Ergebnis leider erentu identisch. Beim erneuten Ausführen der Abfrage erhalte ich wiederum neue Werte, die aber ebenfalls fix sind.
Spalte ZZG Bezug
ZZG Bezug: RUNDEN ( ZZG( [Zahl] ) * 100 );2 )
Hier lese ich nun den Wert in der Spalte Zahl aus um diese als Startwert zu nehmen. Auch wenn die Zahlen jeweils identisch sind, wird hier doch für jede Zeile eine neue Zufallszahl berechnet, so dass die Werte in der Spalte ZZG Bezug nun endgültig unterschiedlich sind.
Verschachtele WENN Dann Funktion mit Zufallszahlen
Die erhaltenen Zufallszahlen können dann auch für weitere Berechnungen oder zufällige Wertausgaben genutzt werden. Dieses kann zum Beispiel für "Ja", "Nein" aber auch für komplexere Ausgaben wie die Auswahl aus drei Optionen "Tor A", "Tor B" oder "Tor C" genutzt werden.Im Beispiel soll hier "Bestanden", "Nicht bestanden" oder "Neuer Versuch per Zufall auf die einzelnen Einträge verteilt werden.
Dazu habe ich folgende Spalten in die Abfrage eingefügt:
- ZZG Bezug: Runden(ZZG([Zahl])*10;2)
Damit wird für ZZG Bezug ein Wert zwischen 0 und 10 ausgegeben.
- Ergebnis: Wenn([ZZG Bezug]<3;"Nichtbestanden";Wenn([ZZG Bezug]<7;"Neuer Versuch";"Bestanden"))
Als Ergebnis wird hier bis 3 "Nichtbestanden", bis 7 "Neuer Versuch" und ansonsten "Bestanden" ausgegeben.
Natürlich können hier auch größere Bandbreiten durch Erhöhung der Abstände ZZG Bezug bis 100 oder auch andere Bestandteile für die Wenn Abfrage genommen werden.
Fazit
Auch wenn in Access die Berechnung des Zufalls etwas umständlicher ist, können so doch auch pseudozufällige Verteilungen der Ausprägung eines Merkmals generiert werden und damit etwaige Anonymisierungen von Statistiken erfolgen.Immerhin sind solche Fragen, wie auch die Konverttierung einer "als Tabelle formatierten" Datengrundlage in einen Bereich wie im Artikel "Daten in Excel intelligenter formatieren 📊 als intelligente Tabellen bzw. als Tabelle formatieren" beschrieben immer mal wieder Grundlage für spannende Artikel.
ein Angebot von Espresso Tutorials
unkelbach.link/et.books/
unkelbach.link/et.reportpainter/
unkelbach.link/et.migrationscockpit/
Diesen Artikel zitieren:
Unkelbach, Andreas: »Zufallswerte in Excel Zellen per Zufallszahlszahl und Zufallsbereich aber auch in Access je Datenzeile einer Access Abfrage generieren« in Andreas Unkelbach Blog (ISSN: 2701-6242) vom 1.12.2020, Online-Publikation: https://www.andreas-unkelbach.de/blog/?go=show&id=1158 (Abgerufen am 14.5.2024)
Keine Kommentare - Permalink - Office
Artikel datenschutzfreundlich teilen
🌎 Facebook 🌎 Twitter 🌎 LinkedIn