excel公式技巧：在方形區域內填充不重復的隨機整數

excel公式技巧：在方形區域內填充不重復的隨機整數

本文分享一個基于公式生成n×n隨機整數的解決方案，并且每個整數都是唯一的。例如，下圖1顯示了生成10行10列的不重復隨機整數。

圖1

解決方案

在單元格A1中輸入數組公式：

=SMALL(IF(FREQUENCY(($A2:$J$11,B1:$K1),ROW(INDIRECT(“1:99”))-1)=0,ROW(INDIRECT(“1:100”))-1),RANDBETWEEN(1,100-COUNTA($A2:$J$11,B1:$K1)))

向右向下拖拉至單元格J10。

通常，將此矩陣放置在工作表中的某位置，對于輸出結果的最左上角單元格的公式，引用的兩個單元格區域包括：

1）10×10的單元格區域從最左上角的單元格正下方的單元格開始，向下并向右延伸。

2）最左上角單元格右側的1×10單行單元格數組

這里都是相對/絕對混合引用。

工作原理

考慮使用FREQUENCY函數，不僅可以生成通常使用COUNTIF函數能夠獲得的結果，而且還可以操作由多個單元格區域組成的引用。

讓我們從示例中隨便選擇一個公式，看看其是如何工作的。例如，在單元格C8中的公式：

=SMALL(IF(FREQUENCY(($A9:$J$11,D8:$K8),ROW(INDIRECT(“1:99”))-1)=0,ROW(INDIRECT(“1:100”))-1),RANDBETWEEN(1,100-COUNTA($A9:$J$11,D8:$K8)))

可以看到，公式引用的兩個單元格區域是：D8:$K8和$A9:$J$11，如下圖2所示。

圖2

公式中的：

FREQUENCY(($A9:$J$11,D8:$K8),ROW(INDIRECT(“1:99”))-1)

是這種情況下COUNTIF函數有用的替代，它可以用于返回一個由單元格區域內某些值個數組成的數組，而且執行這些計數的單元格區域不是單個連續的區域，而是兩個這樣的區域。這里需要注意的是FREQUENCY函數的一個特點，即返回的數組比傳遞給它的元素數量多。因此，上面的結構解析為：

{0;1;0;0;0;1;0;0;0;1;0;1;0;0;0;0;0;0;1;0;1;0;1;1;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;1;1;0;0;0;1;0;0;0;1;0;0;0;0;0;0;0;0;1;0;1;0;0;1;1;1;0;1;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;1;1;1;0;0;0;1;0;1;0;0;1;0}

顯然，我們對該數組中的零感興趣，因此在IF函數中將以上內容設置等于為零，其中IF函函數的參數value_if_true的值是一個從0到99的整數數組，因此：

IF(FREQUENCY(($A9:$J$11,D8:$K8),ROW(INDIRECT(“1:99”))-1)=0,ROW(INDIRECT(“1:100”))-1)

轉換為：

IF({0;0;0;0;0;1;0;1;0;0;0;1;0;1;0;0;0;0;0;0;1;0;1;1;0;0;1;0;0;0;0;0;0;0;0;0;1;1;1;1;0;0;0;0;1;1;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;1;0;0;1;0;0;0;0;0;1;1;0;0;0;0;0;1;0;0;0;0;0;0;0;0;1;0;1;1;0;0;0;1;1;1;0;0;1}=0,ROW(INDIRECT(“1:100”))-1)

轉換為：

IF({0;0;0;0;0;1;0;1;0;0;0;1;0;1;0;0;0;0;0;0;1;0;1;1;0;0;1;0;0;0;0;0;0;0;0;0;1;1;1;1;0;0;0;0;1;1;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;1;0;0;1;0;0;0;0;0;1;1;0;0;0;0;0;1;0;0;0;0;0;0;0;0;1;0;1;1;0;0;0;1;1;1;0;0;1}=0,{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61;62;63;64;65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80;81;82;83;84;85;86;87;88;89;90;91;92;93;94;95;96;97;98;99})

轉換為：

IF({TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE},{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61;62;63;64;65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80;81;82;83;84;85;86;87;88;89;90;91;92;93;94;95;96;97;98;99})

結果為：

{0;1;2;3;4;FALSE;6;FALSE;8;9;10;FALSE;12;FALSE;14;15;16;17;18;19;FALSE;21;FALSE;FALSE;24;25;FALSE;27;28;29;30;31;32;33;34;35;FALSE;FALSE;FALSE;FALSE;40;41;42;43;FALSE;FALSE;46;47;48;49;50;51;52;53;54;55;56;57;FALSE;59;60;61;FALSE;63;64;FALSE;66;67;68;69;70;FALSE;FALSE;73;74;75;76;77;FALSE;79;80;81;82;83;84;85;86;FALSE;88;FALSE;FALSE;91;92;93;FALSE;FALSE;FALSE;97;98;FALSE}

現在，成功地創建了一個不在公式單元格下面的行或右邊的單元格中的所有值組成的數組，剩下的就是從此數組中隨機選擇一個數值。

實現這一目標的一種方法是將上述數組傳遞給SMALL函數，并指定參數k的值為合適的隨機數。由于數組中的數字元素數等于100減去所引用的區域的元素數，因此可以將其用于RANDBETWEEN函數的top參數：

100-COUNTA($A9:$J$11,D8:$K8)

使用了COUNTA函數，可用于處理多個單元格區域。因此：

RANDBETWEEN(1,100-COUNTA($A9:$J$11,D8:$K8))

轉換為：

RANDBETWEEN(1,100-27)

其中的27等于單元格區域$A9:$J$11中的20個非空元素加上D8:$K8中的7個非空元素。（注意，將A1:J10區域周邊的無關單元格有意地留為空白單元格非常重要）

綜上，公式轉換為：

=SMALL({0;1;2;3;4;FALSE;6;FALSE;8;9;10;FALSE;12;FALSE;14;15;16;17;18;19;FALSE;21;FALSE;FALSE;24;25;FALSE;27;28;29;30;31;32;33;34;35;FALSE;FALSE;FALSE;FALSE;40;41;42;43;FALSE;FALSE;46;47;48;49;50;51;52;53;54;55;56;57;FALSE;59;60;61;FALSE;63;64;FALSE;66;67;68;69;70;FALSE;FALSE;73;74;75;76;77;FALSE;79;80;81;82;83;84;85;86;FALSE;88;FALSE;FALSE;91;92;93;FALSE;FALSE;FALSE;97;98;FALSE},RANDBETWEEN(1,73))

得到所需的結果。

小結

FREQUENCY函數、COUNTA函數可以操作多個單元格區域。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。