เบื้องต้นของ Array

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

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

{=SUM(1/CountIF(DataRange,DataRange))}

สมมติว่าใน DataRange บันทึกชื่อลูกค้าไว้ตามนี้ aa, bb, aa, cc, bb สูตรนี้จะหาคำตอบเป็นเลข 3 ซึ่งหมายถึงการนับชื่อลูกค้าว่ามีอยู่ 3 คน คือ aa, bb, และ cc แต่กว่าจะหาทางสร้างสูตรให้ทำงานได้ก็ต้องทราบก่อนว่าเครื่องหมายวงเล็บปีกกา { } ที่เห็นอยู่ด้านหน้าสุดและด้านท้ายสุดของสูตรนั้น ไม่ใช่เครื่องหมายวงเล็บปีกกาที่พิมพ์เอง แต่เกิดจากการกดปุ่ม Ctrl+Shift+Enter พร้อมกันและห้ามมีช่องว่างใน DataRange ด้วย

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

• มี aa ซ้ำกันอยู่ 2 ตัว ดังนั้นค่าเฉลี่ยต่อตัวของ aa แต่ละตัวคือ 1/2
• มี bb ซ้ำกันอยู่ 2 ตัว ดังนั้นค่าเฉลี่ยต่อตัวของ bb แต่ละตัวคือ 1/2
• มี cc อยู่ 1 ตัว ดังนั้นค่าเฉลี่ยต่อตัวคือ 1

เมื่อรวมค่าเฉลี่ยต่อตัวเข้าด้วยกัน จาก DataRange aa, bb, aa, cc, bb =1/2+1/2+1/2+1+1/2 จะได้คำตอบเป็นจำนวนของ Unique Items เท่ากับ 3

จวบจนปัจจุบันนี้ยังตามหาคนที่คิดสร้างสูตรยอดเยี่ยมนี้ขึ้นมาเป็นครั้งแรกไม่พบว่าเป็นใคร ยังดีที่มีคำอธิบายประกอบสูตรนี้ไว้ด้วย