เร่งความเร็วสูตรขั้นสุดด้วย BMAPλ: เทคนิคใหม่สำหรับการเขียนสูตร Excel ซับซ้อน

ในโลกของการวิเคราะห์ข้อมูล การประมวลผลที่รวดเร็วและมีประสิทธิภาพเป็นสิ่งสำคัญอย่างยิ่ง โดยเฉพาะเมื่อต้องจัดการกับชุดข้อมูล Array ขนาดใหญ่ใน Microsoft Excel บทความนี้จะแนะนำให้คุณรู้จักกับ BMAPλ (Bisected Map Lambda) เทคนิคใหม่ล่าสุดที่พัฒนาโดย Peter Bartholomew ซึ่งจะช่วยเพิ่มประสิทธิภาพในการประมวลผลข้อมูลของคุณอย่างมีนัยสำคัญ (เร็วขึ้นเป็นสิบเท่า!)

บทความนี้เป็นบทความจาก Content Creator
เขียนโดย พชร ชาตะวิถี เจ้าของ กลุ่ม FB : เรียน Excel ฟรี
บรรณาธิการ ตรวจสอบโดย ศิระ เอกบุตร (เทพเอ็กเซล)
กลั่นมาจากความคิดทั้งสองคน จึงมั่นใจได้ในความถูกต้องมากขึ้นไปอีกครับ

BMAPλ คืออะไร?

BMAPλ หรือ Bisected Map Lambda เป็นเทคนิคการเขียนสูตรใน Excel ที่ใช้หลักการของ recursive function ร่วมกับการแบ่งข้อมูลทีละครึ่ง (bisection) เพื่อเพิ่มประสิทธิภาพในการประมวลผล โดยเฉพาะอย่างยิ่งเมื่อต้องทำงานกับข้อมูลจำนวนมาก

ผมรู้จักกับ BMAPλ ครั้งแรกจากการแชร์ของคุณโบ Excel Wizard ในกลุ่ม Excel Super Fan ซึ่งได้มาแนะนำความรู้เรื่อง BMAPλ ให้พวกเราได้รู้จักกัน

หลักการทำงานของ BMAPλ

1. การแบ่งข้อมูล (Bisection): BMAPλ จะแบ่งชุดข้อมูลออกเป็นสองส่วนเท่าๆ กัน
2. การเรียกตัวเองซ้ำ (Recursion): ฟังก์ชันจะเรียกตัวเองซ้ำๆ เพื่อประมวลผลข้อมูลในแต่ละส่วนย่อย โดยประมวลผลทีละแถว แต่สามารถให้ผลลัพธ์ที่มีขนาดไม่เท่ากัน (Ragged Arrays) ได้
3. การรวมผลลัพธ์: เมื่อประมวลผลเสร็จสิ้น BMAPλ จะรวมผลลัพธ์จากทุกส่วนเข้าด้วยกันโดยใช้ VSTACK

ด้วยวิธีนี้ BMAPλ สามารถลดจำนวนรอบการทำงานจาก N (จำนวนแถวข้อมูลทั้งหมด) เหลือเพียง log2N ซึ่งช่วยเพิ่มความเร็วในการประมวลผลอย่างมาก โดยสามารถใช้กับฟังก์ชันใดๆ ที่รับอาร์เรย์หนึ่งแถวเป็นอินพุต และเหมาะสำหรับการทำงานที่ต้องการผลลัพธ์ที่มีขนาดไม่แน่นอนมากเลย

การใช้งาน BMAPλ

ขั้นตอนการสร้างฟังก์ชัน BMAPλ

1. ไปที่ Formulas -> Name Manager (หรือกด Ctrl + F3)
2. คลิก New -> ตั้งชื่อฟังก์ชันเป็น BMAPλ
3. ในช่อง Refers to ให้ใส่สูตรต่อไปนี้:

= LAMBDA(X, Fnλ,
    LET(
        n, ROWS(X),
        Y, IF(
            n > 1,
            LET(
                ℓ, n - QUOTIENT(n, 2),
                X₁, TAKE(X, ℓ),
                X₂, DROP(X, ℓ),
                Y₁, BMAPλ(X₁, Fnλ),
                Y₂, BMAPλ(X₂, Fnλ),
                IFERROR(VSTACK(Y₁, Y₂), "")
            ),
            Fnλ(X)
        ),
        Y
    )
)

4. กด OK เพื่อบันทึก

วิธีการใช้งาน BMAPλ

เมื่อสร้างฟังก์ชัน BMAPλ แล้ว คุณสามารถเรียกใช้งานได้โดยระบุพารามิเตอร์ 2 ตัว:

1. X: ชุดข้อมูลที่ต้องการประมวลผล
2. Fnλ: ฟังก์ชันที่ต้องการใช้กับข้อมูลแต่ละแถว

ในรูปแบบนี้

=BMAPλ( X, Fnλ )

ตัวอย่างการใช้งาน BMAPλ

ตัวอย่างที่ 1: การแยกคำในประโยค

สมมติว่าเรามีข้อมูลประโยคในคอลัมน์ A และต้องการแยกคำในแต่ละประโยค

ในสถานการณ์ปกติ MAP จะไม่สามารถใช้ร่วมกับ TEXTSPLIT ได้ โดยมันจะ Error แบบนี้

=MAP(A2:A7,LAMBDA(x,TEXTSPLIT(x," ")))

แต่ถ้าเปลี่ยนมาจาก BMAPλ แทน มันก็จะสามารถใช้ได้เลยแบบสบายๆ

=BMAPλ(A2:A7,LAMBDA(x,TEXTSPLIT(x," ")))

นอกจากนี้ เราสามารถทำให้ผลลัพธ์มีจำนวนแถวไม่เท่ากันได้ เช่น ผมใช้ TOCOL เพื่อพลิกข้อมูลลงมาให้อยู่คอลัมน์เดียวกัน ก็ยัง work

=BMAPλ(A2:A7,LAMBDA(x,TOCOL(TEXTSPLIT(x," "))))

ตัวอย่างที่ 2: การจัดกลุ่มข้อมูล

สมมติว่าเรามีข้อมูล ID, วันที่, และปริมาณ ต้องการจัดกลุ่มข้อมูลตาม ID

ที่มา:

https://gist.github.com/ncalm/0e72b08272ec14f411e8aaa763c0c0b5?trk=article-ssr-frontend-pulse_little-text-block

1. input data คือ

={"ID","Date","Qty";"ID07",44289,3;"ID07",44322,4;"ID08",45251,7;"ID22",44511,12;"ID22",45159,2;"ID22",45038,5;"ID45",45108,16;"ID45",45116,8;"ID61",45116,32}

ให้ copy ไปวางไว้ที่ cell A1 จะได้ข้อมูล ID ที่ range A2:A10 ข้อมูล date ที่ B2:B10 และ Qty อยู่ที่ C2:C10

2. โจทย์ คือ ต้องการกรองข้อมูล ID เดียวกันมาเรียงให้อยู่ในบรรทัดเดียวกัน

3. ตั้งชื่อข้อมูล column ID ว่า a โดย LET ฟังก์ชัน จากนั้นหา ID ที่ไม่ซ้ำกันโดย UNIQUE ฟังก์ชัน และตั้งชื่อว่า u

=LET(a,A2:A10,u,UNIQUE(a),

3. X คือ u และฟังก์ชันที่เราจะทำงานกับ X คือ

LAMBDA(v,TOROW(FILTER(B2:C10,a=v)))

ฟังก์ชันนี้จะรับค่า v จากนั้นกรองข้อมูล date และ qty (B2:C10) ที่มีค่า ID = v จากนั้นนำมาเรียงอยู่ในบรรทัดเดียวกันโดย TOROW

4. เรียกใช้ BMAPλ ดังนี้ 

=LET(a,A2:A10,u,UNIQUE(a),BMAPλ(u,LAMBDA(v,TOROW(FILTER(B2:C10,a=v))))

5. แปะ ID ไว้หน้าคำตอบเพื่อความเรียบร้อยสวยงาม

=LET(a,A2:A10,u,UNIQUE(a),HSTACK(u,
BMAPλ(u,LAMBDA(v,TOROW(FILTER(B2:C10,a=v))))))

ฟังก์ชันนี้จะจัดกลุ่มข้อมูลตาม ID โดยนำข้อมูลวันที่และปริมาณมาเรียงในแถวเดียวกันได้ตามต้องการ

เทียบความเร็ว BMAPλ vs REDUCE

เพื่อทดสอบประสิทธิภาพ เราจะใช้ข้อมูลสุ่ม 10,000 แถว และเปรียบเทียบเวลาการประมวลผลระหว่าง BMAPλ และ REDUCE

สูตรวัดเวลาการรัน:

สูตรนี้ได้มาจาก Excel Wizard อีกเช่นเคย ขอบคุณมากครับ

=LET(st,NOW(),
rs,
"ใส่สูตรลงตรงนี้ จะส่งกลับบรรทัดแรกเป็นเวลาประมวลผล",
VSTACK(TEXT(NOW()-st,"[s].00 \s"),rs))

ทดลองรันโดย BMAPλ

=LET(st,NOW(),a,A2:A10001,u,UNIQUE(a),
rs,
HSTACK(u,BMAPλ(u,LAMBDA(v,TOROW(FILTER(B2:C10001,a=v))))),
IFNA(VSTACK(TEXT(NOW()-st,"[s].00 \s"),rs),""))

ทดลองรันโดย REDUCE

=LET(st,NOW(),a,A2:A10001,u,UNIQUE(a),
rs,
HSTACK(u,DROP(REDUCE(0,u,LAMBDA(c,v,VSTACK(c,TOROW(FILTER(B2:C10001,a=v))))),1)),
IFNA(VSTACK(TEXT(NOW()-st,"[s].00 \s"),rs),""))

สรุปผลการทดสอบความเร็ว

ข้อดีของ BMAPλ

1. ความเร็ว: เมื่อต้องจัดการarray ขนาดใหญ่ที่ต้องการประมวลผลเป็นแถว ๆ ฟังก์ชัน BMAPλ จะช่วยในการแบ่งข้อมูลออกเป็นส่วนย่อย ๆ (bisection) และประมวลผลแต่ละส่วนอย่างมีประสิทธิภาพ ทำให้การทำงานรวดเร็วขึ้นกว่าการต้องอ่านทั้งหมดซ้ำกันบ่อยๆ จากการทดสอบ BMAPλ ทำงานเร็วกว่า REDUCE ถึง 10 เท่า
2. ความยืดหยุ่น: สามารถจัดการกับข้อมูลที่มีขนาดไม่เท่ากัน (Ragged Arrays) ได้ดีกว่าฟังก์ชัน MAP, REDUCE หรือ SCAN ใน Excel ปัจจุบันซึ่งจะติดข้อจำกัดไม่สามารถทำการคำนวณ Array ของ Array ได้
3. ประสิทธิภาพ: Peter Bartholomew ผู้สร้างฟังก์ชัน BMAPλ อธิบายเหตุผลที่ BMAPλ รันได้เร็วกว่า เนื่องจากจำนวนรอบการรันลดลงจากต้องรัน N รอบ เหลือ  log₂N
4. ทำลายขีดจำกัด : Excel Wizard ได้แนะนำว่า BMAPλ ช่วยให้เราสามารถข้ามข้อจำกัดเรื่องจำนวนรอบที่จำกัดของฟังก์ชัน recursive จากประมาณ 4000 รอบ เป็น 2⁴⁰⁰⁰ รอบ

สรุป

BMAPλ เป็นเทคนิคที่ทรงพลังสำหรับการจัดการข้อมูลขนาดใหญ่ใน Excel โดยเฉพาะอย่างยิ่งเมื่อต้องการความเร็วและความยืดหยุ่นในการประมวลผล แม้ว่าจะมีความซับซ้อนในการเขียนสูตรมากกว่าฟังก์ชันทั่วไป แต่ประโยชน์ที่ได้รับคุ้มค่ากับการเรียนรู้และนำไปใช้ ลองนำ BMAPλ ไปประยุกต์ใช้กับงานของคุณ และคุณจะพบว่า Excel สามารถทำงานได้เร็วขึ้นอย่างที่คุณไม่เคยคาดคิดมาก่อน!

แหล่งข้อมูลเพิ่มเติม

สำหรับผู้ที่สนใจเรียนรู้เพิ่มเติมเกี่ยวกับ BMAPλ สามารถอ่านบทความของ Owen Price ที่จะเล่าประวัติความเป็นมา และอธิบายขั้นตอนการสร้างฟังก์ชันอย่างละเอียดกว่าได้ที่:
Excel LAMBDA Spotlight: Bisected Map (BMAPλ)