--- title: การใช้ฟังก์ชัน REDUCE ทำงานแบบวน Loop ในสูตร Excel (step by step) url: https://www.thepexcel.com/reduce-step-by-step/ type: post date: 2024-06-12 updated: 2025-12-22 author: Pachara Chatavithee categories: [Excel Advanced Formula, Excel] tags: [LAMBDA, UNIQUE, substitute, LET, FILTER, REDUCE, SORT, HSTACK, SUM, TAKE, SUMIF, VSTACK, loop, SEQUENCE] --- # การใช้ฟังก์ชัน REDUCE ทำงานแบบวน Loop ในสูตร Excel (step by step) ใน [LAMBDA helper functions](https://www.thepexcel.com/lambda-excel-365/) ผมคิดว่า REDUCE เป็นฟังก์ชันที่พา Excel ไปสู่ยุคใหม่ของการเขียนสูตร เพราะเราสามารถใช้ REDUCE ในการทำงานแบบวน loop เหมือนที่เราทำในการเขียนโปรแกรมได้แล้ว 😂 ซึ่งการทำ looping มีประโยชน์มากในการทำงาน กล่าวโดยสรุปได้ดังนี้ 1. ช่วยให้เราสามารถรันสูตรเดิมซ้ำๆ อย่างอัตโนมัติไม่ต้อง copy ไปวางอีกต่อไป 2. ช่วยให้สูตรของเราสั้นลงเนื่องจาก รันหลายๆรอบ ก็เขียนสูตรแค่รอบเดียว 3. ช่วยเพิ่มความ dynamic ให้กับสูตร ตัวอย่างเช่น จำนวนรอบในการรันข้อมูลขึ้นอยู่กับขนาดของ input data กรณีในอนาคตมีการเพิ่มหรือลดข้อมูล คำตอบจะถูกอัพเดทโดยอัตโนมัติ เป็นต้น เรามาดูวิธีการใช้งาน REDUCE กันครับ ***บทความนี้เป็นบทความจาก Content Creator*** *เขียนโดย [พชร ชาตะวิถี](https://www.thepexcel.com/author/pachch/) เจ้าของ [กลุ่ม FB : เรียน Excel ฟรี](https://www.facebook.com/groups/366920072765584)* *บรรณาธิการ ตรวจสอบโดย ศิระ เอกบุตร (เทพเอ็กเซล) * *กลั่นมาจากความคิดทั้งสองคน จึงมั่นใจได้ในความถูกต้องมากขึ้นไปอีกครับ* ## **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. เริ่มด้วยสูตรยอดนิยม : การลบข้อมูลที่ไม่ต้องการออกจากข้อมูล** ![1](https://www.thepexcel.com/wp-content/uploads/2024/06/001-1024x709.png) ***รูปที่ 1** การใช้ REDUCE ลบข้อมูลที่ไม้ต้องการ* โจทย์ลักษณะนี้ ถ้าทำด้วยวิธีปกติคือต้องใช้ 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** ![2](https://www.thepexcel.com/wp-content/uploads/2024/06/002-1024x657.png) ***รูปที่ 2** ตัวอย่างการทำงานของฟังก์ชัน 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](https://www.thepexcel.com/wp-content/uploads/2024/06/003-1024x567.png) ***รูปที่ 3** กรณีไม่กำหนด initial value* ## **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](https://www.thepexcel.com/wp-content/uploads/2024/06/004-1024x491.png) ***รูปที่ 4** การประยุกต์ใช้ REDUCE ทำ looping* ## **4. ประยุกต์ใช้ REDUCE แก้ปัญหา ExcelBI : PQ challenge 187** **PQ challenge 187** Download Practice File – [https://lnkd.in/dcsdNKgn](https://lnkd.in/dcsdNKgn) c.r. ExcelBI & Excel Super Fan** ** จาก input data (A1:C12) ให้สร้างตารางแสดงข้อมูลตามความต้องการของลูกค้า (E1:30) ![5](https://www.thepexcel.com/wp-content/uploads/2024/06/005.png) ***รูปที่ 5** ExcelBI : PQ challenge 187* โจทย์ข้อนี้สามารถประยุกต์ใช้ REDEUCE (แบบ 2 ชั้น) ในการจัดการข้อมูลได้ ดังนี้ ![6](https://www.thepexcel.com/wp-content/uploads/2024/06/006-1024x454.png) ***รูปที่ 6** การประยุกต์ใช้ REDUCE แก้ปัญหา PQ challenge 187* เห็นสูตรยาวๆ อย่าเพิ่งตกใจ สูตรยาวๆนี้ มีวิธีสร้างแบบค่อยเป็นค่อยไปได้ ดังนี้ ![7](https://www.thepexcel.com/wp-content/uploads/2024/06/007-1024x300.png) ***รูปที่ 7** ค่อยเป็นค่อยไป 01 ^^* 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,.. เข้ามาเพื่อให้ได้ผลลัพธ์ตามที่ต้องการ ![8](https://www.thepexcel.com/wp-content/uploads/2024/06/008.png) ***รูปที่ 8** ค่อยเป็นค่อยไป 02 ^^* 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) ![9](https://www.thepexcel.com/wp-content/uploads/2024/06/009.png) ***รูปที่ 9** ค่อยเป็นค่อยไป 03 ^^* 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)) ) ) ``` ![10](https://www.thepexcel.com/wp-content/uploads/2024/06/010-1024x448.png) ***รูปที่ 10** ค่อยเป็นค่อยไป 04 คุณก็ทำได้ เชื่อผม ลองดูครับ ^^* ## สรุป จากตัวอย่างนี้แสดงให้เห็นว่า เราสามารถประยุกต์ใช้ REDUCE เพื่อจัดการข้อมูลได้หลากหลาย โดยประโยชน์ที่แท้จริงที่ต้องเขียนสูตรยาวๆแบบนี้ เนื่องจากต้องการความ dynamic กล่าวคือ ถ้าในอนาคตข้อมูลเปลี่ยน เราแค่กรอก a,b, และ c ใหม่งานเสร็จทันที หรือถ้าสร้าง input data เป็น Table ก็แค่เพิ่มข้อมูลในตาราง input data คำตอบจะถูกอัพเดทโดยอัตโนมัต ซึ่งก็คุ้มกับการที่จะเขียนสูตรยาวๆ เพื่อให้เราทำงานสบายขึ้นในอนาคต 😂 --- _Source: [https://www.thepexcel.com/reduce-step-by-step/](https://www.thepexcel.com/reduce-step-by-step/)_