อยากเก่งสูตร Array ต้องสร้างไปแกะไป

จากคำอธิบายที่เขียนไว้เกี่ยวกับที่ไปที่มาของสูตรคำนวณหาจำนวน Unique Items ว่า เป็นการหายอดรวมของค่าเฉลี่ยต่อตัวของแต่ละรายการใน DataRange ย่อมทำให้คนส่วนใหญ่ที่เพิ่งรู้จักสูตร Array เป็นครั้งแรกคงยังไม่เข้าใจลำดับในการคำนวณที่ใช้ในสูตรนี้ชัดเจนนัก ถ้าอยากจะเข้าใจอย่างถ่องแท้ว่าสูตร Array ทำงานได้อย่างไร ต้องเรียนรู้จากการปฏิบัติเท่านั้น โดยใช้วิธีทดลองสร้างสูตรไปแกะไปซึ่งมีอยู่ 2 วิธี

1. สร้างสูตร Array ลงไปในเซลล์เดียวแล้วแกะค่าในแต่ละส่วนของสูตร โดยการกดปุ่ม F9 เพื่อดูในช่อง Formula Bar หรือ
2. สร้างสูตร Array ลงไปในตารางพร้อมกันทีเดียวหลายๆเซลล์หรือใช้ตารางแยกคำนวณทีละขั้น เพื่อดูผลลัพธ์กระจายออกมาให้เห็นในตาราง

โดยทั่วไปจะใช้วิธีแรกสำหรับแกะสูตรที่ไม่ซับซ้อนหรือไม่ยาวนัก พอแกะสูตรโดยการกดปุ่ม F9 แล้วต้องอย่าลืมกดปุ่ม Esc เพื่อย้อนหลังกลับคืนสู่สภาพเดิมก่อนแกะ (ถ้าแกะสูตรแล้วเผลอกด Enter ลงไปจะทำลายสูตรให้กลายเป็นค่าคงที่แทน)

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

วิธีแกะสูตร Array วิธีที่ 1

DataRange คือ ตารางช่วง B3:B7

เซลล์ D3 มีสูตร Array {=SUM(1/COUNTIF(DataRange,DataRange))} โดยเครื่องหมายวงเล็บปีกกาเกิดจากการกดปุ่ม Ctrl+Shift+Enter พร้อมกัน (ไม่ได้พิมพ์ {} เอง)

1. เริ่มจากคลิกลงไปในช่อง Formula Bar คลิกลากทับส่วนของสูตร COUNTIF(DataRange,DataRange)
2. กดปุ่ม F9 จะพบว่าสูตร CountIF นับจำนวนซ้ำของค่าแต่ละตัว ทำให้สูตรทั้งหมดเปลี่ยนเป็นสูตร =SUM(1/{2;2;2;1;2})
3. ตัวเลข  {2;2;2;1;2} หมายถึง จำนวนซ้ำของแต่ละค่า กล่าวคือ aa มี 2 ค่า, bb มี 2 ค่า, aa มี 2 ค่า, cc มี 1 ค่า, และ bb มี 2 ค่า
4. คลิกลากทับส่วนของสูตร 1/{2;2;2;1;2}
5. กดปุ่ม F9 จะพบว่าสูตร 1/{2;2;2;1;2} หาค่าเฉลี่ยจากจำนวนซ้ำของค่าแต่ละตัว ทำให้สูตรทั้งหมดเปลี่ยนเป็นสูตร =SUM({0.5;0.5;0.5;1;0.5})
6. ตัวเลข {0.5;0.5;0.5;1;0.5} เกิดจากส่วนของหารหาค่าเฉลี่ยโดย 1/2 ทำให้เกิดเลข 0.5 และ 1/1 ทำให้เกิดเลข 1
7. คลิกลากทับสูตร SUM({0.5;0.5;0.5;1;0.5}) แล้วกดปุ่ม F9 จะได้คำตอบเป็นสูตร =3
8. กดปุ่ม Esc เพื่อทำให้สูตรแปลงกลับไปเป็นสูตรแรกเริ่มตามเดิม

หมายเหตุ การคลิกแล้วลากทับส่วนของสูตรที่แสดงในช่อง Formula Bar นี้ ต้องคลิกแล้วลากทับส่วนของสูตรที่ครบและพร้อมจะคำนวณได้ เช่น COUNTIF(DataRange,DataRange) แต่ถ้าลากทับขาดหรือเกิน เช่น ลากทับ /COUNTIF(DataRange,DataRange) ซึ่งมีเครื่องหมายหารเกินเข้ามาด้วย จะทำให้เมื่อกดปุ่ม F9 จะพบว่าไม่สามารถแกะสูตรส่วนนั้นได้

วิธีแกะสูตร Array วิธีที่ 2

วิธีนี้ใช้ตารางเป็นตัวช่วยหาผลลัพธ์ของการคำนวณทีละขั้นแสดงให้เห็นได้ทันทีว่า หลังจากคำนวณขั้นแรกเสร็จแล้ว ส่งผลไปคำนวณต่อในการคำนวณขั้นถัดไปอย่างไรและเกิดผลลัพธ์อย่างไร ซึ่งพอคำนวณต่อกันไปจนเกิดผลลัพธ์ได้ถูกต้องตามที่ต้องการแล้ว จึงนำสูตรแต่ละขั้นมาซ้อนกันเป็นสูตร Array เพียงสูตรเดียว

ให้เริ่มจากแยกสูตร =SUM(1/COUNTIF(DataRange,DataRange)) ออกเป็นส่วนๆตามลำดับการคำนวณ โดยไล่จากสูตรที่อยู่ภายในวงเล็บในสุดออกมา

1. Step1 เซลล์ D3 สร้างสูตร =COUNTIF(DataRange,B3) แล้ว copy ลงมาตลอดแนวจนถึง เซลล์ D7
2. ตัวเลขที่ได้จากเซลล์ D3:D7 เป็นตัวเลขการนับว่าข้อมูลแต่ละตัวมีบันทึกไว้ซ้ำกันกี่ครั้งภายใน DataRange
3. Step2 เซลล์ E3 สร้างสูตร =1/D3 แล้ว copy ลงมาตลอดแนวจนถึง เซลล์ E7
4. ตัวเลขที่ได้จากเซลล์ E3:E7 เป็นตัวเลขค่าเฉลี่ยต่อตัวจากการนับว่าข้อมูลแต่ละตัวมีบันทึกไว้ซ้ำกันกี่ครั้งภายใน DataRange
5. Step3 เซลล์ F3 สร้างสูตร =SUM(E3:E7) เพื่อหายอดรวมของค่าเฉลี่ย ได้คำตอบเป็นจำนวนของ Unique Items

หมายเหตุ ใน Step1 และ Step2 แทนที่จะสร้างสูตรลงไปในเซลล์ D3 และ E3 เพียงเซลล์เดียว ให้ทดลองสร้างสูตรคำนวณแบบ Array ลงไปในตารางหลายเซลล์พร้อมกันแทนก็ได้

• Step1 ให้เลือกเซลล์ D3:D7 แล้วพิมพ์สูตร =COUNTIF(DataRange,DataRange) แล้วกดปุ่ม Ctrl+Shift+Enter เพื่อสร้างสูตรนี้ลงไปพร้อมกันทีเดียว จะพบว่าได้คำตอบเป็นการนับค่าซ้ำเช่นเดียวกัน เนื่องจากสูตร CountIF จะใช้แต่ละค่าใน DataRange ตรวจสอบจำนวนการบันทึกซ้ำจากพื้นที่ DataRange ทั้งหมดเช่นเดียวกับสูตรที่นับแต่ละตัว
• Step2 ให้เลือกเซลล์ E3:E7 แล้วพิมพ์สูตร =1/D3:D7 แล้วกดปุ่ม Ctrl+Shift+Enter เพื่อสร้างสูตรนี้ลงไปพร้อมกันทีเดียว จะพบว่าได้คำตอบเป็นค่าเฉลี่ยเช่นเดียวกัน