ใน LAMBDA helper functions ผมคิดว่า REDUCE เป็นฟังก์ชันที่พา Excel ไปสู่ยุคใหม่ของการเขียนสูตร เพราะเราสามารถใช้ REDUCE ในการทำงานแบบวน loop เหมือนที่เราทำในการเขียนโปรแกรมได้แล้ว 😂
ซึ่งการทำ looping มีประโยชน์มากในการทำงาน กล่าวโดยสรุปได้ดังนี้
- ช่วยให้เราสามารถรันสูตรเดิมซ้ำๆ อย่างอัตโนมัติไม่ต้อง copy ไปวางอีกต่อไป
- ช่วยให้สูตรของเราสั้นลงเนื่องจาก รันหลายๆรอบ ก็เขียนสูตรแค่รอบเดียว
- ช่วยเพิ่มความ 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 คำตอบจะถูกอัพเดทโดยอัตโนมัต ซึ่งก็คุ้มกับการที่จะเขียนสูตรยาวๆ เพื่อให้เราทำงานสบายขึ้นในอนาคต 😂
