Author: Pachara Chatavithee

ใน LAMBDA helper functions ผมคิดว่า REDUCE เป็นฟังก์ชันที่พา Excel ไปสู่ยุคใหม่ของการเขียนสูตร เพราะเราสามารถใช้ REDUCE ในการทำงานแบบวน loop เหมือนที่เราทำในการเขียนโปรแกรมได้แล้ว 😂

ซึ่งการทำ looping มีประโยชน์มากในการทำงาน กล่าวโดยสรุปได้ดังนี้

1. ช่วยให้เราสามารถรันสูตรเดิมซ้ำๆ อย่างอัตโนมัติไม่ต้อง copy ไปวางอีกต่อไป
2. ช่วยให้สูตรของเราสั้นลงเนื่องจาก รันหลายๆรอบ ก็เขียนสูตรแค่รอบเดียว
3. ช่วยเพิ่มความ dynamic ให้กับสูตร ตัวอย่างเช่น จำนวนรอบในการรันข้อมูลขึ้นอยู่กับขนาดของ input data กรณีในอนาคตมีการเพิ่มหรือลดข้อมูล คำตอบจะถูกอัพเดทโดยอัตโนมัติ เป็นต้น

เรามาดูวิธีการใช้งาน REDUCE กันครับ

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

Syntax ของ REDUCE

=REDUCE([initial_value], array, lambda(accumulator, value, body))

• [initial_value] คือ ค่าเริ่มต้น ก่อนที่ REDUCE จะเริ่มทำงาน สามารถใส่เป็นค่าเดี่ยวหรือใส่เป็น array ก็ได้ เนื่องจากเป็น optional argument [..] ถ้าเราว่างไว้  REDUCE จะใช้ value ตัวแรกของ array เป็น initial_value และส่งกลับค่านี้ในรอบที่ 1
• array คือ array ที่เราต้องการทำงานด้วยทีละตัว กรณีเป็น array 1 มิติในแนวตั้ง จำนวน row ของ array ก็คือ จำนวนรอบที่ REDUCE จะทำงาน
• accumulator คือ การตั้งชื่อตัวแปรของ accumulator (สมมุติว่าชื่อ a แต่จริงๆ จะชื่ออะไรก็ได้) โดย REDUCE จะ update ค่าของ a ไปเรื่อยๆ จนครบทุก value
• value คือ การตั้งชื่อตัวแปรของ value (สมมุติว่าชื่อ v แต่จริงๆ จะชื่ออะไรก็ได้) v ก็คือค่าจาก array (parameter ตัวที่สอง) แต่ละตัวนั่นเอง
• body คือ สูตรที่ต้องการให้ REDUCE ส่งกลับค่า

มาเรียนการใช้ฟังก์ชัน REDUCE โดยการลองทำโจทย์ตัวอย่างกันเลยครับ

1. เริ่มด้วยสูตรยอดนิยม : การลบข้อมูลที่ไม่ต้องการออกจากข้อมูล

โจทย์ลักษณะนี้ ถ้าทำด้วยวิธีปกติคือต้องใช้ SUBSTITUTE แทนค่าในข้อความหลายๆ รอบ (เท่ากับจำนวนอักขระที่ต้องการแทน ซึ่งในที่นี้มี 4 ตัว คือ B3:B6) แต่ถ้าเราใช้ REDUCE ในการวน Loop เราก็จะสามารถเขียนสูตรครั้งเดียว โดยจะเปลี่ยนจำนวนที่จะแทนเป็นกี่ค่าก็ได้

=REDUCE(A3:A7,B3:B6,LAMBDA(a,v,SUBSTITUTE(a,v,"")))

ในข้อแรกเราต้องการลบข้อมูลที่ไม่ต้องการ (B3:B6) ออกจาก input data (A3:A7) มีขั้นตอนการทำ ดังนี้

=REDUCE(A3:A7

คือ การกำหนดค่าเริ่มต้นเป็น range A3:B3 โดยจะเห็นว่าในสูตรนี้เรากำหนดค่าเริ่มต้นเป็น range ของข้อมูลตัวอักษรก่อนการ clean data นั่นเอง

=REDUCE(A3:A7,B3:B6

 คือ การกำหนด array ที่เราต้องการทำงานด้วย ซึ่งในข้อนี้ คือ range B3:B6 หรือตัวอักษรที่เราต้องการลบออกจากข้อมูล

=REDUCE(A3:A7,B3:B6,LAMBDA(a,v

คือ การตั้งชื่อ accumulator เป็น a และ value เป็น v หลังจากนี้ เราจะทำงานกับ a และ v

=REDUCE(A3:A7,B3:B6,LAMBDA(a,v,SUBSTITUTE(a,v,"")))

คือ การสร้าง body ของสูตร โดยใช้ SUBSTITUTE(a,v,””) เป็น การค้นหาค่า v ใน a ถ้าเจอ แทนที่ด้วยความว่างเปล่า (“”) หรือลบข้อมูล v ตัวนั้นออกจาก a นั่นเอง

อธิบายขั้นตอนการทำงานของ REDUCE

รอบที่ 1 : REDUCE จะนำค่าเริ่มต้น (A3:A7) มาใช้เป็นค่า a จากนั้นวิ่งไปรับค่า v ตัวแรก ($) และค้นหา v ตัวแรกใน array a ทุกตัว ถ้าเจอ v จะถูกแทนที่ด้วย “” ผลที่ได้จากรอบที่ 1 คือ v ตัวแรก หรือ $ จะถูกลบออกไปจาก a และ a หลังจบรอบที่ 1 จะถูกใช้เป็น a เริ่มต้นของรอบที่ 2  

รอบที่ 2-4 : REDUCE จะวนลูปทำงานเหมือนเดิม คือ ลบ #, !, ? ออกจากข้อมูลทีละตัว โดยจะอัพเดท a ไปเรื่อยๆ (เรามองไม่เห็น) โดยหลังจากวนลูป v ครบทุกตัว เราจะเห็น output สุดท้าย คือ ข้อมูล A3:A7 ที่ถูกลบค่า v ทั้งหมดออกไป ส่งกลับคำตอบเป็น dyanamic range C3#

การวน Loop ด้วย REDUCE แบบนี้มีข้อดีคือทำให้เราไม่จำเป็นต้องเขียน SUBSTITUTE ซ้อนกันหลายๆ รอบเหมือนวิธีปกตินั่นเอง หวังว่าตัวอย่างนี้จะช่วยให้ทุกท่านเข้าใจลักษณะการทำงานของฟังก์ชัน REDUCE มากยิ่งขึ้นนะครับ ^^

2. กรณีว่าง initial value ไว้ REDUCE จะใช้ v ตัวแรกเป็น initial value

=REDUCE(,A3:A6,LAMBDA(a,v,a&v))

จะเห็นว่าสูตรนี้ว่าง initial value ไว้ ดังนั้น ในรอบที่ 1 REDUCE จะส่งกลับค่า v ตัวแรก คือ A3 หรือ =”A” และในรอบถัดไปจะวนลูปนำค่า v มาแปะต่อท้าย a จนครบ v ทุกตัว

3. การประยุกต์ใช้ REDUCE ในการทำ looping แสดงค่า  “Hello, World!” จำนวน n ตัว

=LET(
n,4,
t,"Hello, World!",
REDUCE(t,SEQUENCE(n-1),LAMBDA(c,v,VSTACK(c,t)))
)

ในที่นี้เรามีการใช้ LET มาประกาศตัวแปรด้วย คือ กำหนด

• n = 4
• t = “Hello, World!”

ในส่วน REDUCE เราให้ initial value เท่ากับ t แสดงว่าเริ่มต้น มี “Hello, World!” 1 ตัวแล้วสิ่งที่ต้องการทำ คือ ต้องการเอา t ไป VSTACK หรือเอาไปแปะใต้ c หรือ VSTACK(c,t) อีก n-1 รอบนั่นเอง

เรามักจะกำหนด v ด้วยฟังก์ชัน SEQUENCE(..) เพื่อใช้แทนจำนวนรอบที่เราต้องการวนลูป แต่จะเห็นว่าในส่วนการส่งกลับ ไม่ได้มีส่วนเกี่ยวข้องกับ body เลย (เราใช้ v เพื่อวนลูปเท่านั้น)

4. ประยุกต์ใช้ REDUCE แก้ปัญหา ExcelBI : PQ challenge 187

PQ challenge 187 Download Practice File – https://lnkd.in/dcsdNKgn

c.r. ExcelBI & Excel Super Fan 

จาก input data (A1:C12) ให้สร้างตารางแสดงข้อมูลตามความต้องการของลูกค้า (E1:30)

โจทย์ข้อนี้สามารถประยุกต์ใช้ REDEUCE (แบบ 2 ชั้น) ในการจัดการข้อมูลได้ ดังนี้

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

4.1. จัดการข้อมูล continent โดยตัดตัวที่ซ้ำออกและเรียงจาก A-Z (M2#)

=SORT(UNIQUE(A2:A12))

4.2. ในทำนองเดียวกัน จัดการข้อมูล year (ไม่ซ้ำ + เรียง A-Z) (N3#)

=SORT(UNIQUE(B2:B12))

4.3. ที่ O4 ใช้ continent และ year กรองหาค่า sales โดยทดลองกับ (Asia, 2010)

=FILTER(C2:C12,(A2:A12=M2)*(B2:B12=N3),0)

ความหมายของสูตรนี้คือ การกรองค่าจาก C2:C12 (sales) เมื่อ continent เท่ากับ Asia (M2) และ คูณ (*) year เท่ากับ 2010 (N3) กรณีไม่เจอค่าที่ match ส่งกลับค่า 0

(ซึ่งเป็นความต้องการของโจทย์ ที่ว่าให้ดูจากข้อมูล input พบว่าอาจมีกรณีไม่เจอข้อมูล ซึ่งกำหนดให้ส่งกลับค่า 0)

4.4. ที่ P5 หาค่า sales ของทุก continent (ทดลอง year = 2010)

=REDUCE(0,M2#,LAMBDA(x,v,VSTACK(x,FILTER(C2:C12,(A2:A12=v)*(B2:B12=N3),0))))

สูตรนี้ ผมใส่ initial value เป็น 0 ไว้ก่อนชั่วคราว และกำหนด array เท่ากับ M2# จาก 4.1. ตั้งชื่อ accumulator ว่า x และตั้งชื่อ value ว่า v จากนั้นนำสูตรจาก 4.3. มาสร้างเป็น body แล้วแทน M2 ด้วย v

และเราต้องการให้ REDUCE อัพเดทค่า x โดยนำค่า sales มาเรียงต่อกันในแนวตั้ง เลยต้องใช้ VSTACK(x,… จะได้ค่า sales ของทุก continent ออกมา (ตัวแรกยังเป็น 0 อยู่ ปล่อยไปก่อน)

4.5. ถ้าสังเกตผลลัพธ์ที่ต้องการ พบว่าต้องการข้อมูล 3 columns คือ continent, year, และ sales ตามลำดับ จึงต้องเพิ่ม continent และ year เข้ามา ดังนี้ 

=REDUCE(0,M2#,LAMBDA(x,v,VSTACK(x,HSTACK(v,N3,FILTER(C2:C12,(A2:A12=v)*(B2:B12=N3),0)))))

เราเพิ่ม HSTACK(v,N3,.. เข้ามาเพื่อให้ได้ผลลัพธ์ตามที่ต้องการ

4.6. ใช้ REDUCE ตัวที่ 2 หาค่า sales ของทุก continent ของทุก year

=REDUCE(A1:C1,N3#,LAMBDA(y,w,REDUCE(y,M2#,LAMBDA(x,v,VSTACK(x,HSTACK(v,w,FILTER(C2:C12,(A2:A12=v)*(B2:B12=w),0)))))))

ใน REDUCE ตัวที่ 2 ใช้ A1:C1 หรือชื่อ header จาก input เป็น initial value ใส่ค่า array เป็น year จาก 4.2. (N3#) ตั้งชื่อ accumulator ว่า y และตั้งชื่อ value ว่า w จากนั้นนำสูตรจาก 4.5. มาใช้เป็น body โดยเปลี่ยนค่า initial value จาก 0 เป็น y และแทน N3 ด้วย w REDUCE ตัวที่ 2 จะทำหน้าที่อัพเดทข้อมูลของทุกปี (จนครบทุก w)

4.7. ปรับรูปแบบตารางให้เป็นไปตามที่โจทย์ต้องการ นั่นคือ ในแต่ละปี จะต้องทำการคำนวณ Total sales และมี แถวว่าง 1 แถว คั่นกลางระหว่างแต่ละปี  ดังนี้

=REDUCE(A1:C1,N3#,LAMBDA(y,w,VSTACK(REDUCE(y,M2#,LAMBDA(x,v,VSTACK(x,HSTACK(v,w,FILTER(C2:C12,(A2:A12=v)*(B2:B12=w),0))))),HSTACK("TOTAL",w,SUMIF(B2:B12,w,C2:C12)),{"","",""})))

ผมตั้ง VSTACK หน้า REDUCE ตัวแรก เพื่อก่อนจะขึ้นปีถัดไปเราจะของแปะข้อมูล ได้แก่
1) HSTACK(“TOTAL”,w,SUMIF(B2:B12,w,C2:C12)) และ
2) {“”,””,””}  

4.8. คุณเค้าอยากได้ Grand total sales ด้วย ก็ต้องเพิ่มให้

=VSTACK(REDUCE(A1:C1,N3#,LAMBDA(y,w,VSTACK(REDUCE(y,M2#,LAMBDA(x,v,VSTACK(x,HSTACK(v,w,FILTER(C2:C12,(A2:A12=v)*(B2:B12=w),0))))),HSTACK("TOTAL",w,SUMIF(B2:B12,w,C2:C12)),{"","",""}))),HSTACK("GRAND TOTAL",TAKE(N3#,1)&"-"&TAKE(N3#,-1),SUM(C2:C12)))

ตั้ง VSTACK หน้า REDUCE ตัวที่ 2 (หน้าสุด) และแปะ HSTACK(“GRAND TOTAL”,TAKE(N3#,1)&”-“&TAKE(N3#,-1),SUM(C2:C12)) เข้าไป

4.9. เพื่อความเรียบร้อยสวยงาม array หรือ range ไหนที่ถูกใช้มากกว่า 1 ครั้ง ผมจะใช้ LET เก็บค่า เช่น N3# ถูกใช้ 2 ครั้ง ผมตั้งชื่อว่า z แล้วกัน (N3# คือ dynamic range ที่ถูกสร้างที่ cell N3 สูตรที่ใช้สร้างก็จะอยู่ที่ N3 นั่นแหละ) จะได้ ดังนี้

=LET(z,SORT(UNIQUE(B2:B12)),VSTACK(REDUCE(A1:C1,z,LAMBDA(y,w,VSTACK(REDUCE(y,SORT(UNIQUE(A2:A12)),LAMBDA(x,v,VSTACK(x,HSTACK(v,w,FILTER(C2:C12,(A2:A12=v)*(B2:B12=w),0))))),HSTACK("TOTAL",w,SUMIF(B2:B12,w,C2:C12)),{"","",""}))),HSTACK("GRAND TOTAL",TAKE(z,1)&"-"&TAKE(z,-1),SUM(C2:C12))))

4.10. เก็บรายละเอียด ค่า range A2:A12, B2:B12, C2:C12 ที่ถูกใช้มากกว่า 1 ครั้งให้เรียบร้อยสวยงาม โดยตั้งชื่อเป็น a, b, และ c ตามลำดับ ดังนี้

=LET(a,A2:A12,b,B2:B12,c,C2:C12,z,SORT(UNIQUE(b)),VSTACK(REDUCE(A1:C1,z,LAMBDA(y,w,VSTACK(REDUCE(y,SORT(UNIQUE(a)),LAMBDA(x,v,VSTACK(x,HSTACK(v,w,FILTER(c,(a=v)*(b=w),0))))),HSTACK("TOTAL",w,SUMIF(b,w,c)),{"","",""}))),HSTACK("GRAND TOTAL",@z&"-"&TAKE(z,-1),SUM(c))))

Note: @z ส่งกลับตัวซ้ายบนสุดของ array ให้ผลเหมือนกับ TAKE(z,1) เลือกใช้ตามสะดวก ^^

ลองใช้ Advanced Formula Environment กระจายให้สูตรอ่านง่ายขึ้น

=LET(
    a, A2:A12,
    b, B2:B12,
    c, C2:C12,
    z, SORT(UNIQUE(b)),
    VSTACK(
        REDUCE(
            A1:C1,
            z,
            LAMBDA(y, w,
                VSTACK(
                    REDUCE(
                        y,
                        SORT(UNIQUE(a)),
                        LAMBDA(x, v, VSTACK(x, HSTACK(v, w, FILTER(c, (a = v) * (b = w), 0))))
                    ),
                    HSTACK("TOTAL", w, SUMIF(b, w, c)),
                    {"", "", ""}
                )
            )
        ),
        HSTACK("GRAND TOTAL", @z & "-" & TAKE(z, -1), SUM(c))
    )
)

สรุป

จากตัวอย่างนี้แสดงให้เห็นว่า เราสามารถประยุกต์ใช้  REDUCE เพื่อจัดการข้อมูลได้หลากหลาย โดยประโยชน์ที่แท้จริงที่ต้องเขียนสูตรยาวๆแบบนี้ เนื่องจากต้องการความ dynamic กล่าวคือ ถ้าในอนาคตข้อมูลเปลี่ยน เราแค่กรอก a,b, และ c ใหม่งานเสร็จทันที หรือถ้าสร้าง input data เป็น Table ก็แค่เพิ่มข้อมูลในตาราง input data คำตอบจะถูกอัพเดทโดยอัตโนมัต ซึ่งก็คุ้มกับการที่จะเขียนสูตรยาวๆ เพื่อให้เราทำงานสบายขึ้นในอนาคต 😂