หน้าเว็บ

วันพุธที่ 9 ธันวาคม พ.ศ. 2552

การใช้สูตรสุ่มข้อมูลเป็นชุด ๆ โดยกำหนดได้ว่าให้ไม่ซ้ำกันภายในชุด

เหตุการณ์ของการสุ่มอย่างที่ว่านี้คือ สุ่มออกมาเป็นชุด ๆ โดยให้ข้อมูลไม่ซ้ำกันภายในชุดนั้นใช้ในกรณีไหนบ้าง ตัวอย่างที่เห็นได้ชัดก็เรื่องของการจัดกลุ่มแข่งขันกีฬาสีครับ

เป็นที่น่าดีใจว่า Excel เองสามารถที่จะสุ่มเช่นนี้ด้วยสูตรได้เหมือนกัน โดยไม่ต้องพึ่งพา VBA แต่สูตรยาวพอควร เข้าใจค่อนข้างยาก แต่นั่นไม่ใช่ปัญหาสำหรับเราใช่ไหมครับ

ภาพตัวอย่างการ Random ด้วยสูตรตามด้านล่างครับ

RandomInAdvanced 

จากภาพด้านบนเราสมมุติว่าข้อมูลมีชุดละ 2 บรรทัด และโดยชุดแรกคือ C2:C3, ชุดที่สองคือ C4:C5 เป็นต้น และจะทำการสุ่มโดยนำข้อมูลสีที่ E3:E4 เข้าแสดงแต่ต้องไม่ซ้ำสีกันภายในชุดเดียวกัน

เซลล์ E3:E4  สามารถกรอกค่าใด ๆ ก็ได้ไม่จำเป็นต้องเป็นต้วหนังสือ และจะกรอกกี่ค่าก็ได้ แต่ถ้ากรอกมากกว่า 2 ค่าตามที่ยกตัวอย่างมา จะต้องเข้าไปแก้สูตรใหม่ โดยเปลี่ยนเลข 2 ที่ไม่เกี่ยวกับช่วงเซลล์ ให้เป็นเลขใด ๆ ตามจำนวนค่าจริงที่ต้องการสุ่ม และแก้ไขช่วงเซลล์ที่ต้องการสุ่ม

สำหรับเลข 2 ด้านบน สามารถใช้ฟังก์ชั่นนับค่าที่ต้องการสุ่มไว้ในเซลล์ใด ๆ แล้วค่อยอ้างอิงไปใช้ในสูตร จะสะดวกกว่ามากหากมีการแก้ไขในภายหลัง

ช่วงเซลล์ที่ต้องการสุ่มในฟังก์ชั่น Index ก็เช่นกัน สามารถทำให้ Dynamic โดยการให้ชื่อกับช่วงเซลล์แบบ Dynamic ได้

ตามรูป ที่ C2 คีย์

=Index($E$3:$E$4,Small(If(Countif(If(Mod(Rows($C$2:C2)-1,2)=0,$C$1:$C$1,Offset(C1,0,0,-Mod(Rows($C$2:C2)-1,2),1):C1),$E$3:$E$4)=0,Row($E$3:$E$4)-Row($E$3)+1),Int((2-Mod(Rows($C$2:C2)-1,2))*Rand())+1))

การกดแป้นให้รับสูตรต้องกด 3 แป้นคือ Ctrl+Shift+Enter ถ้ากดแป้นถูกต้องจะเห็นปีกกาคร่อมสูตร จากนั้น Copy ลงด้านล่าง

3 ความคิดเห็น:

Jeab กล่าวว่า...

รบกวนหน่อยค่ะ ถ้า Data Random มี 4 ชุด ต้องเขียนคำสั่งอย่างไงค่ะ เพื่อที่ข้อมูลที่ได้ เท่ากัน มีตัวอย่างค่ะ
Name Random
aa 2
bb 1
cc 3
dd 4
ee 4
ff 1
gg 2
hh 3
รวมแล้ว Random 1 = 2 รายการ
Random 2 = 2 รายการ
Random 3 = 2 รายการ
Random 4 = 2 รายการ
ขอบคุณค่ะ

snasui กล่าวว่า...

ใช้สูตรเดิมครับ จากรูปข้างบนที่ E5:E6 ให้เติมตัวเลข 3 และ 4 เข้าไปตามลำดับ จากนั้นที่เซลล์ C2 คีย์

=Index($E$3:$E$6,Small(If(Countif(If(Mod(Rows($C$2:C2)-1,4)=0,$C$1:$C$1,Offset(C1,0,0,-Mod(Rows($C$2:C2)-1,4),1):C1),$E$3:$E$6)=0,Row($E$3:$E$6)-Row($E$3)+1),Int((4-Mod(Rows($C$2:C2)-1,4))*Rand())+1))

Ctrl+Shift+Enter > Copy ลงด้านล่าง

หากยังไม่ได้คำตอบส่งคำถามไปที่ http://www.snasui.com/ จะสะดวกในการตอบมากกว่าครับ

Jeab กล่าวว่า...

ทำได้แล้วค่ะ ขอบคุณมากค่ะ