การใช้ฟังก์ชัน Recursive ในสูตร Excel (step by step)

Recursive function คืออะไร?

Recursive function คือ ฟังก์ชันที่ทำงานโดยมีการอ้างอิงตัวเองไปเรื่อยๆ ทำงานวนลูป โดยใช้ผลการรันในรอบก่อนหน้า นำไปรันต่อในรอบถัดไป รันไปเรื่อยๆ จนถึงจุดสิ้นสุดที่กำหนด (Stopping criteria) คล้ายกับการใช้ Do While loop ในการเขียนโปรแกรม ต่างกันตรงที่เมื่อมีการวนลูป ฟังก์ชัน recursive จะมีการอ้างอิงตัวมันเองไปเรื่อยๆ

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

ใน Excel ผมมักจะใช้ฟังก์ชัน Recursive LAMBDA ในงานที่ไม่ทราบจำนวนรอบการรันที่แน่นอน

เพื่อให้การวนลูปหยุดทันทีเมื่อถึงจุดสิ้นสุดที่กำหนด (หากทราบจำนวนรอบการรันที่แน่นอน ผมมักจะใช้ฟังก์ชัน REDUCE)

ตัวอย่างปัญหาที่มีจำนวนรอบการรันไม่แน่นอน มีดังนี้ 

1. การจำลองสถานการณ์การเล่นเกม XO ซึ่งจำนวนรอบการรันจะเท่ากับ 9 รอบ คือเมือ input ค่า X และ O ครบ 9 ช่องแล้วไม่มีผู้ชนะ หรือเกมจะจบทันทีเมื่อมีผู้ชนะ (X หรือ O เรียงกันได้ 3 ช่อง) ก่อนจะใส่ค่าครบ 9 ช่องก็ได้
2. การสุ่มเปิดตัวเลขในเกม BINGO ซึ่งจำนวนรอบการสุ่มเปิดตัวเลขจะหยุดเมื่อมีคนได้ BINGO เป็นต้น
3. ใช้ในอัลกอริธึมในการหาตำตอบรูปแบบต่างๆ เช่น Genetic algorithm (GA) หรือ Differential evolution (DE)  ที่มีการกำหนด stopping criteria เป็นต้น

มาทดลองประยุกต์ใช้ฟังก์ชัน recursive กันเลย lol

1. ฟังก์ชัน recursive ในการหาคา MOD

เริ่มจากตัวอย่างง่ายๆ ก่อน คือ กรณีมีส้ม 38 ลูก ต้องการแบ่งใสถุง ถุงละ 8 ลูก จะเหลือเศษกีลูก นันคือ 

=MOD(38,8) 

หรือ 

=(((38-8)-8)-8)-8

เราสามารถประยุกต์ใช้ฟังก์ชัน recursive ในการหาคา mod ได้ ดังนี้

ฟังก์ชันของเราจะรับ 2 ค่า คือ รับคา n เริมต้นเท่ากับ 38 และลบออกทีละ d (divisor) เท่ากับ 8 ดังนั้นในแต่ละรอบจะมีการอัพเดท n โดย n-d ส่วนค่า d ไม่มีการอัพเดท stopping criteria คือ เมื่อส้มเหลือน้อยกว่า 8 ลูก คือ n<8 หรือ n<d ให้ส่งกลับค่าจำนวนส้มที่เหลือ

ฟังก์ชัน recursive ของเราจะสร้างใน cell เพื่อให้คุณมองเห็นภาพรวมของฟังก์ชัน ดังนี้ 

1.1. เริ่มโดย ฟังก์ชันของเราจะรับ 3 ค่าตัวแปร คือ fx, n, และ d คือ การอ้างอิงตัวเอง (fx) ค่าจำนวนส้มที่เหลือในแต่ละรอบ (n) และจำนวนการแบ่งส้ม (d)

fx(fx,n,d)

1.2. ในแต่ละรอบเราจะอัพเดทค่า n แต่ไม่อัพเดทค่า d ดังนี้ 

fx(fx,n-d,d)

1.3. ใช้ LET สร้างฟังก์ชัน fx ด้วยฟังก์ชัน LAMBDA รับค่า 3 ค่า คือ fx, n, และ d ดังนี้ 

=LET(fx,LAMBDA(fx,n,d,

1.4. ระบุ stopping criteria และค่าที่ต้องการส่งกลับ ดังนี้

=LET(fx,LAMBDA(fx,n,d,IF(n<d,n

1.5. ถ้ายังไม่ถึง stopping criteria ให้รันฟังก์ชัน recursive ของเราไปเรื่อยๆ

=LET(fx,LAMBDA(fx,n,d,IF(n<d,n,fx(fx,n-d,d)

1.6. เรียกใช้ฟังก์ชัน recursive ของเรากับค่า n = 38 และ d = 8 ดังนี้

=LET(fx,LAMBDA(fx,n,d,IF(n<d,n,fx(fx,n-d,d))),fx(fx,38,8))

สรุป

=LET(
    fx, LAMBDA(fx, n, d,
        IF(n < d, n, fx(fx, n - d, d))
    ),
    fx(fx, 38, 8)
)

2. ฟังก์ชัน recursive ในการหาคา FACT

การหาค่า 5! หรือ 5*4*3*2*1 สามารถหาค่าได้จากสูตร ดังนี้

=FACT(5)

หรือ

=5*(5-1)*((5-1)-1)*(((5-1)-1)-1)*((((5-1)-1)-1)-1)

รับค่า n เริ่มต้นเท่ากับ 5 ในรอบแรก ค่าคำตอบจะถูกอัพเดทเท่ากับ 5*(5-1) หรือ n*(n-1) และเราจะวนลูปไปเรื่อยๆ จนกระทั่ง n เท่ากับ 1

เราสามารถประยุกต์ใช้ฟังก์ชัน recursive ในการหาคา factorial ได้ดังนี้

2.1. ฟังก์ชันของเรารับ 2 ค่า คือ fx และ n

fx(fx,n) 

2.2. อัพเดทค่า n โดย n*(n-1)…

n*fx(fx,n-1) 

2.3.ใช้ LET สร้างฟังก์ชัน fx ด้วย LAMBDA รับค่า 2 ค่า คือ fx และ n ดังนี้

=LET(fx,LAMBDA(fx,n

2.4. ในตัวอย่างนี้ลองเปลี่ยนจาก stopping criteria มาระบุ running criteria และค่าที่ต้องการส่งกลับ ดังนี้

=LET(fx,LAMBDA(fx,n,IF(n>1,n*fx(fx,n-1),1)

2.5. เรียกใช้ฟังก์ชัน recursive ของเรากับค่า n = 5 ดังนี้

=LET(fx,LAMBDA(fx,n,IF(n>1,n*fx(fx,n-1),1)),fx(fx,5))

สรุป

=LET(
    fx, LAMBDA(fx, n,
        IF(n > 1, n * fx(fx, n - 1), 1)
    ),
    fx(fx, 5)
)

3. ประยุกต์ใช้ฟังก์ชัน recursive ในการลบข้อมูลที่ไม่ต้องการออกจากข้อมูลตัวอักษร

ในตัวอย่างนี้เราต้องการ clean ข้อมูลจาก A2 โดยการลบข้อมูลใน B2 ออก

เราสามารถ clean ข้อมูลนี้ด้วยฟังก์ชัน REDUCE ได้ดังนี้

=REDUCE(
    A2,
    MID(B2, SEQUENCE(LEN(B2)), 1),
    LAMBDA(a, v, SUBSTITUTE(a, v, ""))
)

ฟังก์ชัน recursive ในการ clean data ในตัวอย่างนี้สามารถสร้างได้ ดังนี้

3.1. ฟังก์ชันของเรารับ 3 ค่าตัวแปร คือ fx, symbol (s), และ text (t) ดังนี้

fx(fx,s,t) 

3.2. อัพเดทค่า s โดย 

MID(s,2,LEN(s)-1)

3.3. อัพเดทค่า t โดย 

SUBSTITUTE(t,LEFT(s),"")

3.4. recursive ฟังก์ชันของเราจะอัพเดท s และ t ดังนี้

fx(fx,MID(s,2,LEN(s)-1),SUBSTITUTE(t,LEFT(s),"")) 

3.5.ใช้ LET สร้างฟังก์ชัน fx ด้วย LAMBDA รับค่า 3 ค่า คือ fx, s, และ t ดังนี้

=LET(fx,LAMBDA(fx,s,t

3.6. ระบุ stopping criteria และค่าที่ต้องการส่งกลับ ดังนี้

=LET(fx,LAMBDA(fx,s,t,IF(s="",t,
fx(fx,MID(s,2,LEN(s)-1),SUBSTITUTE(t,LEFT(s),""))

3.7. เรียกใช้ฟังก์ชัน recursive ของเรากับค่า s = B2 และ t = A2 ดังนี้

=LET(fx,LAMBDA(fx,s,t,IF(s="",t,
fx(fx,MID(s,2,9),SUBSTITUTE(t,LEFT(s),"")))),
fx(fx,B2,A2))

สรุป

=LET(
    fx, LAMBDA(fx, s, t,
        IF(
            s = "",
            t,
            fx(
                fx,
                MID(s, 2, 9),
                SUBSTITUTE(t, LEFT(s), "")
            )
        )
    ),
    fx(fx, B2, A2)
)

ข้อจำกัดของฟังก์ชัน recursive ใน Excel

ขณะที่ผมเขียนบทความนี้ ฟังก์ชัน recursive ใน Excel จะสามารถรันได้มากที่สุด 5,460 รอบ และถ้าในฟังก์ชันมีการใช้ฟังก์ชัน LET จะมีจำนวนรอบสุงสุดลดลงเหลือ 4,095 รอบ

ฝากการบ้านให้ไปลองแกะสูตรดูครับ ^^

ผมลองประยุกต์ใช้ฟังก์ชัน recursive ในการจำลองสถานการณ์การเล่นเกม XO เผื่อมีท่านใด สนใจในเชิงลึก อยากลองแกะสูตรเล่นดู โดยตัวเกมจะเริ่มจากตารางว่างๆ 9 ช่อง และจะมีการสุ่ม input ค่า X และ O สลับกันไปเรื่อยๆ และหยุดรันทันทีเมื่อมีผู้ชนะหรือใส่ค่าครบทุกช่อง และผลยังไม่มีผู้ชนะ สูตรมีดังนี้ครับ

=LET(
    s, SEQUENCE(3, 3),
    r, SORTBY(TOCOL(s), RANDARRAY(9)),
    c, LAMBDA(v, CONCAT(v)),
    fx, LAMBDA(fx, t, n,
        IF(
            AND(
                OR(t = ""),
                ISERR(
                    FIND(
                        {"XXX", "OOO"},
                        TEXTJOIN(
                            1,
                            ,
                            BYROW(t, c),
                            BYCOL(t, c),
                            c(REPT(t, s = {1, 5, 9})),
                            c(REPT(t, s = {7, 5, 3}))
                        )
                    )
                )
            ),
            fx(fx, IFS(t > "", t, s = INDEX(r, n + 1), IF(MOD(n + 1, 2), "X", "O"), 1, ""), n + 1),
            t
        )
    ),
    fx(fx, IF(s, ""), )
)

สรุป

จากตัวอย่างที่กล่าวมาในข้างต้น คุณจะเห็นขั้นตอนในการสร้างฟังก์ชัน recursive ของผมอย่างละเอียด ผมจะค่อยๆสร้างทีละขั้นตอน สิ่งสำคัญที่เราต้องมองให้ออก คือ 

1. ตัวแปรที่ฟังก์ชันของเราต้องการมีอะไรบ้าง 
2. แต่ละตัวแปรถูกอัพเดทอย่างไรในแต่ละรอบการรัน
3. จุดสิ้นสุดหรือ stoppping criteria หรือบางเคสเป็น running criteria
4. สร้างฟังก์ชันตามโครงสร้างของ recursive LAMBDA ดังนี้

=LET(fx,LAMBDA(fx,ตัวแปร1,ตัวแปร2,...,
IF(เงื่อนไขการหยุด,ส่งกลับ,recursive function)),
fx(fx,input1,input2,...))

โดยส่วนตัวคิดว่าการใช้ฟังก์ชัน recursive จะใช้จำนวนรอบการรันที่น้อยกว่า ตัวอย่างเช่นใน เกม XO ถ้าเราใช้ฟังก์ชัน REDUCE เราต้องรัน 9 รอบ โดยเมื่อพบผู้ชนะกำหนดให้ input ค่าที่เหลือเป็น “” ก็ทำได้ ดังนั้นผมขอสรุปว่าคุณสามารถเลือกทำได้ทั้ง 2 วิธี ตราบใดที่ยังไม่กระทบต่อเวลาประมวลผลที่ยอมรับได้ โดยใน REDUCE คณจะต้องมั่นใจว่ากำหนดจำนวนรอบในการรันที่เพียงพอที่จะทำให้ได้คำตอบ เพราะถ้าจำนวนรอบน้อยเกินไป อาจจะส่งผลให้คำตอบผิด อย่างไรก็ตามในการทำงานจริงฟังก์ชัน REDUCE จะถูกใช้แทนฟังก์ชัน recursive หากติดปัญหาเรื่องข้อจำกัดด้านจำนวนรอบการรันในปัญหาขนาดใหญ่