เปลี่ยนสูตรยากให้เป็นสูตรกล้วยๆ ด้วย Dynamic Array ใน Excel 365

ผมเชื่อว่าหลายคนที่ใช้ Excel นั้นย่อมเคยต้องการให้ Excel ทำงานจัดการข้อมูลในลักษณะนี้ เช่น

• คัดกรองข้อมูลให้เหลือสิ่งที่ต้องการเท่านั้น (Filter)
• จัดเรียงข้องมูลใหม่ตามต้องการ (Sort)
• ตัดข้อมูลซ้ำออกให้เหลือเฉพาะที่ไม่ซ้ำ (Remove Duplicates)

ตั้งแต่อดีตที่ผ่านมา จริงๆ Excel ก็มีเครื่องมือที่พร้อมที่จะทำงานเรื่องเหล่านี้อยู่แล้ว ใน Ribbon เครื่องมือ Data เช่น Filter, Sort, Remove Duplicates และผมก็เชื่อว่ามันเป็นเครื่องมือที่หลายๆ คนน่าจะพอใช้กันเป็นอยู่แล้วล่ะ

อย่างไรก็ตามเครื่องมือเหล่านั้นแม้จะใช้ง่าย แต่ก็มีจุดอ่อนสำคัญคือ มันเป็นเครื่องมือที่ต้องให้เรากดปุ่มสั่งใหม่ทุกครั้ง เช่น จะ Filter แบบไหน Sort ยังไง รวมถึง Remove Duplicates ข้อมูลชุดไหน แปลว่ามันยังต้องมี Step ที่ต้องการมนุษย์มา “ทำงาน Manual” บางอย่างอยู่

ใน Excel ยุคใหม่ขึ้นมาหน่อย ก็ได้มีเครื่องมือ Power Query เข้ามาช่วยแก้ปัญหานี้ เพราะมันก็ Filter ข้อมูลได้ Sort ได้ Remove Duplicates ได้ (และทำอย่างอื่นได้อีกเยอะแยะ) และที่สำคัญคือ มันกด Refresh เพื่อทำซ้ำ Step ทุกอย่างที่กำหนดไว้ได้! อย่างไรก็ตาม มันก็ยังต้องมี Step การกด Refresh อีกทีนึงอยู่ดี (ยกเว้นใช้ VBA ช่วย)

ดังนั้นถ้าหากเราต้องการความอัตโนมัติขั้นสุดยอด วิธีที่ทำได้ก็คงหนีไม่พ้น “การเขียนสูตร” ซึ่งบอกเลยว่าการเขียนสูตรเพื่อ Filter/Sort/Remove Duplicates นั้นเคยเป็นเรื่องที่ยากมากกกกกกกกกกๆๆๆๆๆ มาโดยตลอด (คิดดูว่าถ้าจะ Filter ข้อมูลจริงๆ มันก็คล้ายๆ จะต้อง VLOOKUP หรือ INDEX+MATCH นั่นแหละ และปกติสูตรพวกนี้ก็จะเจอแต่ตัวแรกอีก ยากจะตายกว่าจะเอามาครบทุกตัวได้)

จนกระทั่งมีสูตรชุดใหม่เกิดขึ้นมา เป็นสูตรในกลุ่ม Dynamic Array ซึ่งเป็นความสามารถใหม่ของ Excel 365 ที่จริงๆ มีหลายตัว แต่ผมจะขอแนะนำให้รู้จักแค่ 3 ตัวก่อน นั่นคือ FILTER, SORT และ UNIQUE ครับ

** ใครใช้ Excel 365 แล้วยังไม่มี ฟังก์ชัน FILTER, SORT และ UNIQUE ให้ใช้ ลองเช็คดูนะครับว่าอัปเดทเป็น version ล่าสุดรึยัง? วิธีอัปเดทดูได้ที่นี่

เรามาดูข้อมูลตัวอย่างกัน

โหลดข้อมูลตัวอย่างได้ที่นี่

มาดูการทำงานทีละฟังก์ชันก่อนนะ

ก่อนอื่นมาดูตัวที่เข้าใจง่ายก่อน นั่นคือ UNIQUE

UNIQUE เอาไว้คัดมาเฉพาะตัวที่ไม่ซ้ำกัน

วิธีใช้งานแบบง่ายที่สุดคือ

=UNIQUE(rangeข้อมูล)

ซึ่งง่ายมากๆๆๆๆ

ถ้าไม่มีฟังก์ชัน UNIQUE เราต้องเขียนสูตรผสมฟังก์ชันต่างๆ ออกมายาวเป็นกิโล ที่ทั้งยาวและยาก คนทั่วไปอ่านไม่เข้าใจแน่นอน เช่น ในรูปนี้

=IFERROR(INDEX($B$2:$B$15,MATCH(0,COUNTIFS($I$2:I2,$B$2:$B$15),0)),"")

แต่พอมี UNIQUE แล้ว เหลือแค่นี้ =UNIQUE(B2:B15) !!

ผลลัพธ์จะได้ข้อมูลแบบไม่ซ้ำกันเลย แบบนี้ และผลลัพธ์จะงอกออกมาเท่ากับจำนวนข้อมูลด้วย

ถ้ามีสินค้าเพิ่มขึ้นในข้อมูลดิบ เช่น ผมเพิ่มเจลล้างมือลงไป ผลลัพธ์ของ UNIQUE ก็จะเพิ่มมาทันที!!

Dynamic Array กับการงอกผลลัพธ์แบบ Spill

การที่ผลลัพธ์งอกอัตโนมัติแบบนี้ มีศัพท์เทคนิคเรียกว่า Spill นะครับ ซึ่งเป็น Concept ใหม่ของ Array Formula ใน Excel 365 ที่เรียกว่า “Dynamic Array” โดยที่สูตรจะอยู่ช่องซ้ายบนของพื้นที่ที่ Spill เท่านั้น (คล้ายๆ Merge Cell) ถ้าคลิ๊กที่ช่องอื่นสูตรจะเป็นสีเทา

=UNIQUE(E2:E15)

โดยถ้าพื้นที่ที่จะงอกมีข้อมูลอื่นขวางอยู่มันจะขึ้น Spill Error ว่า #SPILL! แบบนี้

กลับมาดูเรื่อง UNIQUE ต่อกัน

การใช้งานแบบเต็มความสามารถของ UNIQUE คือ

=UNIQUE(array, [by_col] , [exactly_once] )

• array คือ range ข้อมูลต้นฉบับ
• [by_col] ถ้าเป็น TRUE คือจะคิด Unique by column คือจะกำจัดตัวซ้ำที่อยู่ในแถวเดียวกันออก (ซึ่งปกติค่านี้จะเป็น FALSE คือ ให้เอาที่ซ้ำกันในคอลัมน์เดียวกันออก) ซึ่งผมว่าแทบไม่ค่อยได้ใช้หรอก
• ส่วน [exactly_once] ถ้าใส่เป็น TRUE คือจะคัดให้เหลือเฉพาะข้อมูลที่โผล่มาครั้งเดียวใน Data Source เท่านั้น อันนี้เดี๋ยวลองกับวันที่ให้ดู (ปกติอันนี้จะเป็น FALSE)

=UNIQUE(B2:B15,FALSE,TRUE)

ผลลัพธ์ออกมาเป็น 4 หมื่นกว่าๆ เพราะยังไม่ได้ปรับ Number Format ดังนั้นให้ลากครอบแล้วปรับ Number Format ให้เป็น Date ซะ (เลือกพื้นที่เผื่อเอาลงมาเยอะๆก็ได้)

สังเกตว่าไม่มีวันที่ 1/2/2020 และ 7/2/2020 เพราะมันไม่ได้มีแค่ตัวเดียว

SORT เอาไว้เรียงข้อมูลตามที่ต้องการ

แบบง่ายสุดก็คือ

=SORT(range ข้อมูล)

เช่น

แต่การใช้งานแบบเต็มความสามารถ คือ

=SORT(array, [sort_index], [sort_order], [by_col])

• array คือ range ข้อมูลต้นฉบับ
• [sort_index] คือ จะเรียงตามคอลัมน์ที่เท่าไหร่ (ปกติเรียงตามคอลัมน์แรก)
• [sort_order] ถ้าเป็น 1 คือ น้อยไปมาก ส่วน -1 คือ มากไปน้อย (ปกติจะเป็น 1)
• [by_col] คือ ถ้าเป็น TRUE คือจะเรียงตามแนวนอน (ปกติเป็น FALSE) อันนี้ไม่ค่อยได้ใช้อีกนั่นแหละ

ตัวอย่างเช่น

ถ้าผม Sort ทั้งพื้นที่เหลือง แต่ให้เรียงตามคอลัมน์ที่ 3 ของพื้นที่เหลือง (ก็คือ ลูกค้า) ก็จะได้ดังนี้

=SORT(A2:D15,3,1)

FILTER เอาไว้คัดเลือกให้เหลือเฉพาะสิ่งที่ต้องการ

=FILTER(array,include,[if_empty])

• array คือ range ข้อมูลต้นฉบับที่จะนำมา Filter
• include คือ range ของเงื่อนไขโดยจะเอาเฉพาะค่าที่เป็น TRUE มาแสดง (สามารถใส่ condition ใน Range ที่ไม่อยู่ใน array ได้)
• [if_empty] คือ ถ้าไม่มีผลลัพธ์เหลือเลยจะให้ขึ้นว่าอะไร

ตัวอย่างเช่น

=FILTER(C2:E15,D2:D15="sales ค","ไม่มีข้อมูล")

array คือพื้นที่สีเหลือง

include เขียนว่า D2:D15=”sales ค” ซึ่งจริงๆ ถ้า Highlight แล้วกด F9 ดูจะได้เป็นค่า TRUE/FALSE ดังนี้

เจ้าฟังก์ชัน FILTER นี่จะเอาเฉพาะรายการที่เป็น TRUE มาเท่านั้น ก็เลยได้แค่ item อันที่ 2 กับ 11 นั่นเอง

ถ้าจะ Filter หลายเงื่อนไข เราต้องรู้จักว่า การเอาเงื่อนไขคูณกัน จะเป็นเงื่อนไขเป็น AND (TRUE * TRUE ได้ 1 นอกนั้นเป็น 0)

เช่น ถ้าอยากได้เฉพาะรายการของ sales ก และ อาหาร เท่านั้น ก็เขียนได้ว่า

=FILTER(C2:E15,(D2:D15="sales ก")*(E2:E15="อาหาร"))

ใช้ฟังก์ชันผสมผสานกัน

ความโดดเด่นของวิธีการเขียนสูตรใน Excel เมื่อเทียบกับวิธีอื่นๆ คือเราสามารถใช้ฟังก์ชันหลายอันผสมกันได้ และฟังก์ชันขี้โกงเหล่านี้ก็ใช้ผสมกันได้แน่นอน

ตัวอย่างเช่น ถ้าผมใช้ UNIQUE กับ SORT ซ้อนกันจะได้แบบนี้

การเขียนฟังก์ชันซ้อนกันในสูตรเดียว

การเขียน

=SORT(UNIQUE(D2:D15))

เป็นการเขียนฟังก์ชันซ้อนกันในสูตรเดียว เหมาะกับคนที่เขียนสูตรคล่องๆ แล้ว

การเขียนฟังก์ชันแยก Cell กัน แต่อ้างอิงข้อมูลที่ Spill

อย่างไรก็ตามถ้าเราต้องการเขียน UNIQUE ก่อน แล้วค่อย SORT แยกออกมาต่างหาก ก็สามารถทำได้ โดยการอ้างอิงไปที่ช่องซ้ายบนของพื้นที่ Spill แล้วตามด้วยเครื่องหมาย# เพื่อให้ Excel เข้าใจว่าเป็นการอ้างอิงตัวที่ Spill ทั้งชุด ไม่ได้อ้างอิงแค่ Cell เดียว เช่น

เดี๋ยวลองผสมกัน 3 ตัวเลย

ใส่ Filter เลือก sales ก ก่อน

ที่ K8 =FILTER(E2:F15,D2:D15=K3)

จากนั้นใส่ Sort ให้เรียงจากน้อยไปมาก ตามชื่อสินค้า

ที่ K8 =SORT(FILTER(E2:F15,D2:D15=K3))

Tips : ถ้ารู้สึกยังไม่ถูกใจ เพราะราคายังไม่เรียงด้วย แปลว่าต้อง Sort หลาย Step ซึ่งจริงๆใช้ฟังก์ชัน SORTBY จะง่ายกว่า แต่ผมยังไม่ได้สอน 555 ไม่เป็นไร ใช้ SORT ก็ทำได้แต่ต้องใส่ซ้อนกัน เช่น SORT index 2 ก่อน แล้วค่อย Sort index 1 ครอบทีหลังดังนี้

ที่ K8 =SORT(SORT(FILTER(E2:F15,D2:D15=K3),2),1)

จากนั้นใส่ UNIQUE เพื่อกำจัดตัวซ้ำ

ที่ K8 =UNIQUE(SORT(SORT(FILTER(E2:F15,D2:D15=K3),2),1))

ลองเปลี่ยน sales ก เป็นคนอื่นบ้าง เช่น sales ค

ตัวอย่างการพลิกแพลงสูตร

ซึ่งหากเรามีความรู้เรื่องฟังก์ชันอื่นๆ ด้วย เราก็สามารถทำอะไรที่พลิกแพลงได้กว่านี้มาก

ตัวอย่างการสร้าง Dropdown List ที่มี Item เพิ่มตามข้อมูลที่เปลี่ยนไป

ให้เราเขียน Dynamic Array ทิ้งไว้ แล้วค่อยอ้างอิงเข้าไปใน Data Validation

หากอยากให้สามารถเห็นข้อมูลใหม่ที่อาจเพิ่มในอนาคตได้

ก็ทำให้ตารางเป็น Table ซะสิ เพราะความสามารถที่เด่นที่สุดของ Table คือการขยายอาณาเขตตัวเองได้เวลามีข้อมูลเพิ่มด้านขวาหรือด้านล่าง

ตัวอย่าง เลียนแบบ Pivot Table แบบมีทั้ง Row และ Column

เช่นสามารถได้ตารางที่ใกล้เคียง Pivot Table เลย โดยในนี้เพิ่มฟังก์ชัน TRANSPOSE ที่เอาไว้พลิกสลับแนวนอนเป็นแนวตั้ง และมีการใช้ SUMIFS มาช่วย โดยระบุ Criteria แบบ Array เป็นตัวที่ Spill

ที่ M5 : =SORT(UNIQUE(C2:C15))
ที่ N4 : =TRANSPOSE(SORT(UNIQUE(E2:E15)))
ที่ N5 : =SUMIFS(H2:H15,C2:C15,M5#,E2:E15,N4#,D2:D15,M1)

ตัวอย่าง เลียนแบบ Pivot Table แบบมี row 2 ชั้น

โดยเลือกมาบางคอลัมน์ด้วย CHOOSE ผสม Array Constant และหาผลสรุปด้วย SUMIFS ผสมกับเลือก Criteria บางส่วนของที่ Spill ด้วย INDEX

ที่ K5 : =SORT(FILTER(CHOOSE({1,2},C2:C15,E2:E15,H2:H15),D2:D15=K1))
ที่ M5 : =SUMIFS(H2:H15,C2:C15,INDEX(K5#,0,1),E2:E15,INDEX(K5#,0,2),D2:D15,K1)

สรุป

เป็นอย่างไรบ้างกับฟังก์ชันใหม่ของ Excel 365 ? ถึงมันจะดูยากไปบ้างเมื่อเทียบกับการใช้ Pivot Table แต่ว่าสิ่งที่ได้กลับมาคือความอัตโนมัติแบบสุดๆ นั่นเอง

และถ้าย้อนกลับไปตอนไม่มีฟังก์ชันพวกนี้ให้ใช้ สูตร Array Formula จะยากกว่านี้หลายสิบเท่า เผลอๆ ต้องเขียนสูตรยาวหลายบรรทัด กว่าจะได้คำตอบที่เทียบเท่ากับสูตร Dynamic Array บรรทัดเดียว

ดังนั้นลองเลือกดว่าจะลองหัดใช้สูตรใหม่นี้หรือไม่ และถ้าจะใช้ ประเด็นที่ต้องพิจารณาคือ หากสิ่งไฟล์ไปให้คนอื่นที่มี Excel Version เก่า ก็จะเปิดไฟล์มาแล้ว Error นะ ดังนั้นระวังให้ดีด้วยนะครับ *o*