☰

Excel: Zufällige Auswahl innerhalb einer Tabelle, die zwei Kriterien erfüllt

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function microsoft-excel-2007
Excel: Zufällige Auswahl innerhalb einer Tabelle, die zwei Kriterien erfüllt

Hier ist ein Beispiel meiner Daten:

Name:        Office:   Years:

John Smith   Canada      1

Bob Smith    Canada      1

Jake Smith   Canada      1

Sarah Smith  Canada      1

Auf einem anderen Blatt möchte ich einen zufälligen Namen basierend auf Amt und Jahren ausgeben. Wenn ich die Formel nach unten ziehe, soll sie jedes Mal einen anderen Namen ausgeben (hier stecke ich fest). Ich habe mehrere Formeln ausprobiert und diese kommt dem am nächsten.

=INDEX($B$2:$B$436,MATCH(SMALL(IF($D$2:$D$436&$F$2:$F$436=$H$3&$I$3,$G$2:$G$436),1),$G$2:$G$436,0))

Spalte B = Name Spalte D = Büro Spalte F = Jahre dann sind H3 und I3 meine Nachschlagewerte

Spalte G ist eine Menge von Zufallszahlen = rand()

Antwort1

Ich werde die Zufallsverteilung in die Formel selbst eintragen. Sie können dies jederzeit in eine andere Spalte eintragen, wenn Sie möchten.

Spalte A ist der Name,
Spalte B ist das Büro,
Spalte C ist das Jahr

Zelle G2 ist das Büro, für das Sie sich interessieren
Zelle G3 ist das Jahr, für das Sie sich interessieren

=INDIRECT("A"&LARGE(IF($B$2:$B$7=G2,IF($C$2:$C$7=G3,ROW($A$2:$A$7),0),0), RANDBETWEEN(1,COUNTIFS(B:B,G2,C:C,G3))))

Dies ist eine Array-Formel und muss mit Ctrl+ Shift+ eingegeben werden Enter.

Es funktioniert, indem ein Array erstellt wird, das die Zeilennummern enthält (wenn die Kriterien erfüllt sind) oder Null, wenn dies nicht der Fall ist. Dann nimmt es dieNgrößter Wert, wobeiNist eine Zufallszahl zwischen 1 und der Anzahl der Zeilen, die den Kriterien entsprechen.

Antwort2

Angenommen, Sie möchten Namen in A2 nach unten zurückgeben (wobei A1 eine Überschrift oder leer ist), dann verwenden Sie diese Array-Formel in A2

=INDEX($B$2:$B$436,SMALL(IF(($D$2:$D$436=$H$3)*($F$2:$F$436=$I$3)*(COUNTIF(A$1:A1,$B$2:$B$436)=0),ROW($B$2:$B$436)-ROW($B$2)+1),RANDBETWEEN(1,SUM(($D$2:$D$436=$H$3)*($F$2:$F$436=$I$3)*(COUNTIF(A$1:A1,$B$2:$B$436)=0)))))

mit CTRL+ SHIFT+ bestätigen CTRLund nach unten kopieren - den A$1:A1Bereich in den COUNTIFFunktionen so ändern, dass er mit der Zelle über Ihrer Startzelle übereinstimmt.

Hinweis: - Sie benötigen bei dieser Methode keine Spalte mit Zufallszahlen

verwandte Informationen