แนะนำคอลัมน์ เล่นแร่แปรสูตร (Formula Combo)

ยินดีต้อนรับสู่บทความแรกของคอลัมน์ใหม่ ที่ชื่อว่า “เล่นแร่แปรสูตร ” (Formula Combo) ซึ่งมีวัตถุประสงค์ที่จะนำเสนอการทดลองประยุกต์ใช้ Excel โดยการผสมสูตรขึ้นมาใหม่จากส่วนประกอบมากมาย  (ใครอยากรู้ว่า Formula ประกอบด้วยอะไรบ้าง เชิญอ่านได้ที่นี่ ) โดยเฉพาะอย่างยิ่งการนำ Function หลายๆ ตัวมาใช้ร่วมกันจะส่งผลให้เห็นถึงพลังมากมายแค่ไหนนั้น รับรองว่าสนุกแน่ครับ

แน่นอนว่าการจะบรรลุวัตถุประสงค์อย่างหนึ่ง ไม่จำเป็นต้องมีการผสมสูตรเพียงแบบเดียว บางทีเพื่อนๆ อาจเจอหรือค้นพบการผสมสูตรที่มีประสิทธิภาพกว่าผมก็ได้ ถ้าเจอแบบนั้นแล้วอย่าลืมมาแชร์กันบ้างนะครับ!

เอาล่ะ เรามาเริ่มกันที่เนื้อหาแรกกันเลยดีกว่าครับ

เล่นแร่แปรสูตร: สุ่ม item จากรายการที่เราเตรียมไว้

บางครั้งเพื่อนๆ อาจต้องการสุ่มคำบางคำ หรือ เลขบางเลข ออกมาจากรายการที่เราเตรียมไว้ แล้วเราจะทำอย่างนั้นด้วย Excel ได้ยังไง  มาดูกันครับ

แบบที่ 1 เขียนรายการที่จะสุ่มลงไปในสูตรเลย

ส่วนประกอบ

• RANDBETWEEN (bottom,top) = ตัวสุ่มจากช่วงของเลขที่กำหนด
• CHOOSE (index_num,value1,value2,…) = กำหนดว่าจะเอาผลลัพธ์ที่เท่าไหร่มาแสดง

Concept การผสมสูตร

• ถ้าจะใช้วิธีนี้ ก่อนอื่นเราต้องรู้ว่าจะมีรายการที่จะให้สุ่มทั้งหมดกี่รายการ สมมุติว่า 5 อัน
• ใช้ RANDBETWEEN สุ่มเลขระหว่าง 1-5 ออกมาก่อน
• จากนั้นได้ผลลัพธ์อะไร (เลขสุ่มระหว่าง 1-5) ก็เอาไปใส่ใน index_num ของฟังก์ชั่น CHOOSE อีกทีซึ่งเตรียมเขียนรายการที่จะสุ่มทั้ง 5 รายการไว้แล้ว

ขั้นตอนการผสมสูตร

• เขียนสูตรในช่องไหนก็ได้ ว่า

=RANDBETWEEN(1,5) ===> ได้เลขสุ่ม ระหว่าง 1-5
=CHOOSE (เลขสุ่ม,รายการที่1,รายการที่2,รายการที่3,รายการที่4,รายการที่5)
ผสม!! 

 =CHOOSE (RANDBETWEEN(1,5),รายการที่1,รายการที่2,รายการที่3,รายการที่4,รายการที่5)

หมายเหตุ : รายการที่ xxx ถ้าเป็นตัวหนังสือต้องใส่ในเครื่องหมายคำพูด (“”) ด้วย แต่ถ้าเป็นตัวเลข หรือ เป็นสูตรก็ใส่ลงไปตรงๆ ได้เลย

• จากนั้นกด F9 เพื่อ Random ได้เลย

แบบที่ 2 มีรายการที่จะสุ่มอยู่ในตาราง Excel เป็น Range คอลัมน์นึง

ส่วนประกอบ

• RANDBETWEEN (bottom,top) = ตัวสุ่มจากช่วงของเลขที่กำหนด
• INDEX (reference,row_num,[column_num]) = ดึงค่ามาแสดงจากพิกัดที่กำหนด
• COUNTA (value1,value2,…) (Optional) = นับว่ามีกี่ช่องที่ไม่ว่างเปล่า

Concept การผสมสูตร

• ถ้าจะใช้วิธีนี้ ต้องเขียนรายการที่จะสุ่มลงไปในคอลัมน์ซักอันนึงก่อน สมมุติเขียนใน Column B (เพื่อให้ง่าย ให้เขียนโดยไม่ต้องมีหัวตารางนะครับ และให้เขียนต่อกันลงไปเรื่อยๆ อย่าเว้นช่อง)
• วิธีนี้ ถ้าจะกำหนดจำนวนรายการแน่นอนจะง่ายมาก แต่ในที่นี้เราจะทำแบบเผื่อให้มาเพิ่มรายการทีหลังก็ได้ (Dynamic Range) โดยไม่ต้องแก้สูตรใหม่ด้วย
• ใช้ COUNTA เพื่อนับว่าที่เราใส่ลงไปใน Column B มีกี่รายการ (ที่ไม่ใช่ค่าว่างเปล่า) สมมติว่านับได้ bbb รายการ
• ใช้ RANDBETWEEN สุ่มเลขระหว่าง 1 ถึง bbb ออกมาก่อน
• ใช้ INDEX ลากคลุม Column B เอาไว้ เป็น Reference จากนั้นให้เลขสุ่มเป็น row_num

ขั้นตอนการผสมสูตร

• เขียนรายการที่จะสุ่มลงไปในคอลัมน์ B จำนวน bbb รายการ
• เขียนสูตรในช่องไหนก็ได้ ที่ไม่ใช่คอลัมน์ B ว่า

=COUNTA(B:B) => สมมติว่านับได้ bbb รายการ
=RANDBETWEEN(1,เลขที่นับได้)

ผสมครั้งที่ 1 !

= RANDBETWEEN(1,COUNTA(B:B)) => ได้เลขสุ่ม

จากนั้นใช้ = INDEX (reference,row_num) โดยให้ คอลัมน์ B (หรือ B:B) เป็น reference และค่าที่สุ่มได้เป็น row_num

=INDEX(B:B,เลขสุ่ม)

ผสมครั้งที่ 2 !!

=INDEX(B:B,RANDBETWEEN(1,COUNTA(B:B)))

• จากนั้นกด F9 เพื่อ Random ได้เลย