แกะสูตร Excel สุดซับซ้อน ให้เข้าใจง่ายขึ้นด้วย AI Chatbot : ThepExcel GPT 1

แกะสูตร Excel สุดซับซ้อน ให้เข้าใจง่ายขึ้นด้วย AI Chatbot : ThepExcel GPT

หลายครั้งที่เราพบเจอสูตร Excel ที่ดูยุ่งเหยิงซับซ้อนเกินกว่าจะทำความเข้าใจได้ง่ายๆ ไม่รู้ว่ามันคืออะไร ทำงานยังไง แล้วจะต้องแก้ไขมันอย่างไร ??

วันนี้ผมมีวิธีแก้ปัญหานี้มาแนะนำ นั่นก็คือการใช้ AI Chatbot ที่ชื่อว่า ThepExcel GPT เข้ามาช่วยในการแกะสูตรเหล่านั้นนั่นเอง โดยตอนนี้เปิดให้ทุกคนสามารถใช้ได้ฟรีแล้วครับ

แนะนำ ThepExcel GPT

แกะสูตร Excel สุดซับซ้อน ให้เข้าใจง่ายขึ้นด้วย AI Chatbot : ThepExcel GPT 2

AI Chatbot อย่าง “ThepExcel GPT” สามารถช่วยแกะและอธิบายสูตร Excel ที่ซับซ้อนเหล่านั้นได้ เพียงแค่เราส่งสูตรหรือแนบรูปภาพไป มันก็จะอธิบายออกมาให้เราเข้าใจได้ง่ายๆ พร้อมตอบข้อสงสัยให้เราได้เรื่อยๆ เหมือนการสอบถามผู้รู้จริงๆ

ThepExcel GPT นี้ผมพัฒนาขึ้นเพื่อตอบปัญหาเกี่ยวกับ Excel โดยเฉพาะ เพื่อช่วยให้การแก้ปัญหาเรื่องสูตรที่ยากๆ เป็นเรื่องง่ายขึ้น

อย่างไรก็ตาม หลายคนอาจจะรู้สึกยังไม่มั่นใจกับการถาม AI ในบทความนี้ผมเลยจะมาทดสอบความสามารถให้ดูว่า ถ้าเจอสูตร Excel ยากๆ มันจะตอบได้ไหม? และนอกจากเรื่องถาม AI แล้ว ผมจะขอบอกเทคนิคเพิ่มเติมที่เราจะแกะสูตร Excel ยากๆ ได้ดีขึ้นกันด้วยครับ

Feedback การใช้งาน

หากคุณลองใช้  ThepExcel GPT  AI Chatbot แล้วคิดเห็นเป็นยังไง ฝากบอกผมด้วย ที่นี่ https://forms.gle/Ku7xn9jrkwhjBWBRA

ที่มาที่ไป

เล่าที่มาที่ไปให้ฟังนิดนึงว่าทำไมผมถึงทำ AI Chatbot นั่นก็คือ ผมเองติดปัญหาเรื่องการตอบ Message หรือคำถามของแฟนเพจมานานแล้ว เพราะผมไม่ค่อยมีเวลาที่จะเข้าไปตอบให้ 😭 ก็เลยคิดว่าน่าจะมี AI Chatbot ที่ตอบคำถาม Excel เก่งๆ มาช่วยตอบคำถามเพื่อนๆ นะ ซึ่งเมื่อซักปลายปี 2023 ผมได้ทำ ThepExcel GPTs Chatbot ขึ้นมาเพื่อตอบปัญหา Excel แทนผม แต่ติดปัญหาสำคัญอันนึงคือ ต้องเป็นคนใช้ ChatGPT Plus แบบเสียตังเท่านั้นถึงจะใช้งานได้ ณ ตอนนั้นหลายคนก็เลยไม่สามารถใช้ AI ผมได้ T_T

แต่เมื่อต้น พค. 67 ที่ผ่านมา Open AI ได้เปิดตัว GPT-4o ไป พร้อมบอกว่าจะอนุญาตให้คนใช้ ChatGPT ฟรี สามารถใช้ GPTs ได้

แกะสูตร Excel สุดซับซ้อน ให้เข้าใจง่ายขึ้นด้วย AI Chatbot : ThepExcel GPT 3

ซึ่งล่าสุด ตั้งแต่ 30 พค. 67 ที่ผ่านมา ตอนนี้คนใช้ ChatGPT ฟรี สามารถใช้ GPTs ได้ ผมก็เลยได้มีการแจก Link ให้ใช้กันฟรีๆ (แต่แป๊ปเดียวก็ครบ limit ของการใช้แบบฟรี 55)

ป.ล. ถ้า ThepExcel GPT ตัวนี้มันเจอคำถามเฉพาะทางยากๆ เช่น DAX หรือ Python มันจะ refer ไปให้ถาม Bot ตัวเฉพาะทางที่ผมทำขึ้นมาได้ด้วยนะ

ทีนี้มาดูการทดสอบกัน ว่าถ้าเจอสูตรยากๆ มันจะตอบได้ไหม อธิบายได้ไหม?

ทดสอบการแกะสูตร Case 1

เช่น สมมติว่าเราต้องการจะ List ค่าในคอลัมน์ออกมาแบบไม่ซ้ำกัน จากค่าใน B2:B10

แกะสูตร Excel สุดซับซ้อน ให้เข้าใจง่ายขึ้นด้วย AI Chatbot : ThepExcel GPT 4

ถ้าเรามี Excel Version ใหม่ (หรือ Google Sheets) ที่รองรับสูตรแนว Dynamic Array แล้วก็อาจเขียนสูตรใน Cell C2 ง่ายๆ แค่นี้จบเลย

=UNIQUE(B2:B10)

ผลลัพธ์ Spill ออกมา C2:C5 ให้เองด้วย

แกะสูตร Excel สุดซับซ้อน ให้เข้าใจง่ายขึ้นด้วย AI Chatbot : ThepExcel GPT 5

แต่ถ้าเรามี Excel Version เก่าที่ยังไม่มีฟังก์ชันแบบ UNIQUE ให้ใช้ ชีวิตก็จะเศร้าทันที 😭

หากลองไปถามสูตรตาม Internet สูตรนึงที่เราอาจจะเจอ อาจจะประมาณนี้ ซึ่งต้องเขียนในช่อง C2 โดยต้องกด Ctrl+Shift+Enter ก่อน เพราะเป็นสูตรแบบ Array Formula แล้วค่อย Copy ยาวลงมา

=IFERROR(INDEX($B$2:$B$10, MATCH(0, COUNTIFS($C$1:C1, $B$2:$B$10), 0)), "")
แกะสูตร Excel สุดซับซ้อน ให้เข้าใจง่ายขึ้นด้วย AI Chatbot : ThepExcel GPT 6

วิธีแกะสูตรดูทีละ Steps

วิธีแกะสูตร ถ้าหากว่าเรามี Excel Version เก่า อาจต้องใช้ Formula -> Evaluate Formula แล้วให้มันแสดงการทำงานทีละ Step เอา แต่วิธีนี้อาจไม่ยืดหยุ่นเท่าไหร่ เพราะเลือกจุดที่จะตรวจสอบเองไม่ได้ ต้องทำทีละก้อนไปเลยตาม Step ของสูตร

แกะสูตร Excel สุดซับซ้อน ให้เข้าใจง่ายขึ้นด้วย AI Chatbot : ThepExcel GPT 7

อีกวิธีนึงคือการ Highlight บางส่วนของสูตร แล้วกด F9 เพื่อแปลงข้อมูลเป็น Value เพื่อทำความเข้าใจ แบบนี้จะยืดหยุ่นกว่า เพราะเลือกส่วนที่ตัวเองสนใจได้ (อย่าลืมกด Undo กลับ)

แกะสูตร Excel สุดซับซ้อน ให้เข้าใจง่ายขึ้นด้วย AI Chatbot : ThepExcel GPT 8

แต่ถ้าใครมี Excel Version 2016 ขึ้นไป ก็จะสามารถใช้ Add-ins ที่ชื่อว่า Excel Labs ซึ่งจะมีเครื่องมือ Advanced Formula Environment อยู่ ซึ่งเจ๋งกว่า Evaluate Formula มาก

อันนี้จะค่อยๆ อธิบายสูตรทีละ Step ได้ดีและเห็นภาพง่าย เพราะสามารถเอา Mouse ไป Hover แล้วจะเห็นผลเป็น Array ได้เลย

แกะสูตร Excel สุดซับซ้อน ให้เข้าใจง่ายขึ้นด้วย AI Chatbot : ThepExcel GPT 9

ไม่ว่าจะใช้วิธีไหน ถ้าเราไม่รู้จักหน้าที่และวิธีการทำงานฟังก์ชันที่อยู่ในสูตร เราก็จะแกะสูตรต่อไม่ได้อยู่ดี ในยุคนี้วิธีที่ดีมากๆ อันนึงคือการถาม AI Chatbot

วิธีแกะสูตรโดยถาม AI Chatbot

ผมลองถาม ThepExcel GPT (พร้อมแนบรูปประกอบ) ว่า

AI Prompt:

ผมมีข้อมูลแบบนี้

ได้สูตรเพื่อ List รายการจาก B2:B10 ออกมาแบบไม่ซ้ำกัน แต่ไม่เข้าใจสูตร ช่วยอธิบายการทำงานที
สูตรที่เขียนใน C3 คือ
=IFERROR(INDEX($B$2:$B$10, MATCH(0, COUNTIFS($C$1:C2, $B$2:$B$10), 0)), "")
แกะสูตร Excel สุดซับซ้อน ให้เข้าใจง่ายขึ้นด้วย AI Chatbot : ThepExcel GPT 10

มันตอบอธิบายมาประมาณนี้ ซึ่งผม Copy Text ออกมาให้ละ

แกะสูตร Excel สุดซับซ้อน ให้เข้าใจง่ายขึ้นด้วย AI Chatbot : ThepExcel GPT 11

สูตรที่ใช้ในเซลล์ C3 คือ:

=IFERROR(INDEX($B$2:$B$10, MATCH(0, COUNTIFS($C$1:C2, $B$2:$B$10), 0)), "")

เรามาอธิบายการทำงานของสูตรนี้กันครับ:

  1. COUNTIFS($C$1:C2, $B$2:$B$10):
    • ฟังก์ชัน COUNTIFS ใช้เพื่อทำการนับจำนวนครั้งที่เงื่อนไขหลายๆ ข้อเป็นจริงพร้อมกัน โดยในที่นี้ COUNTIFS($C$1:C2, $B$2:$B$10) จะนับจำนวนครั้งที่ค่าในช่วง $B$2:$B$10 ปรากฏในช่วง $C$1:C2 (คือค่าที่อยู่ในคอลัมน์ C ด้านบนของเซลล์ปัจจุบัน)
    • ค่าที่ได้จะเป็นอาร์เรย์ เช่น {0, 1, 0, 0, 1, 0, 0, 0, 0} โดยแต่ละค่าบ่งบอกถึงจำนวนครั้งที่ค่าจากคอลัมน์ B ปรากฏในช่วง $C$1:C2
  2. MATCH(0, COUNTIFS($C$1:C2, $B$2:$B$10), 0):
    • ฟังก์ชัน MATCH ใช้เพื่อหาตำแหน่งของค่า 0 ในอาร์เรย์ที่ได้จาก COUNTIFS ซึ่งหมายถึงตำแหน่งที่ค่าจากคอลัมน์ B ยังไม่ปรากฏในคอลัมน์ C ด้านบนของเซลล์ปัจจุบัน
    • ผลลัพธ์จะเป็นตำแหน่งในช่วง $B$2:$B$10 ที่มีค่าเป็น 0 ในอาร์เรย์จาก COUNTIFS
  3. INDEX($B$2:$B$10, MATCH(0, COUNTIFS($C$1:C2, $B$2:$B$10), 0)):
    • ฟังก์ชัน INDEX จะใช้ตำแหน่งที่ได้จาก MATCH เพื่อดึงค่าจากช่วง $B$2:$B$10
    • ค่าที่ได้จะเป็นค่าจากคอลัมน์ B ที่ยังไม่ปรากฏในคอลัมน์ C
  4. IFERROR(INDEX($B$2:$B$10, MATCH(0, COUNTIFS($C$1:C2, $B$2:$B$10), 0)), “”):
    • ฟังก์ชัน IFERROR ใช้เพื่อจัดการกับข้อผิดพลาดที่อาจเกิดขึ้นจากการใช้ MATCH ซึ่งหากไม่มีค่าที่ตรงกับเงื่อนไขเลย (เช่น ทุกค่าจากคอลัมน์ B ปรากฏในคอลัมน์ C แล้ว) ฟังก์ชัน MATCH จะคืนค่า #N/A ซึ่งจะทำให้ INDEX เกิดข้อผิดพลาด
    • หากเกิดข้อผิดพลาด IFERROR จะคืนค่าเป็นข้อความว่าง (“”)

สรุป: สูตรนี้จะช่วยให้เราสร้างรายการที่ไม่ซ้ำกันจากคอลัมน์ B ในคอลัมน์ C โดยเริ่มจาก C2 และเรียงต่อกันลงมาในคอลัมน์ C โดยใช้ INDEX และ MATCH ร่วมกับ COUNTIFS เพื่อค้นหาค่าที่ไม่ซ้ำกันครับ

Confidence: 100%

ถาม AI เพิ่มเติม เพื่อให้ได้สิ่งที่อยากรู้

เอาล่ะ สิ่งที่ AI ตอบมา มันคือการทำงานของแต่ละฟังก์ชันอ่ะนะ แต่มันอาจยังไม่ได้อธิบายหลักการหรือแนวคิดว่าทำไมถึงใช้ฟังก์ชันเหล่านั้น? ซึ่งบางทีมันยังไม่ Perfect หรือเราอาจยังไม่เข้าใจ

ถ้าอยากรู้เราควรถามมันต่อนะ

AI Prompt:
อยากเข้าใจหลักการคิด ทำไมถึงเลือกใช้ฟังก์ชันเหล่านี้ 

ThepExcel GPT จะตอบว่า

แน่นอนครับ การเลือกใช้ฟังก์ชันเหล่านี้มีเหตุผลที่ชัดเจนในการสร้างรายการที่ไม่ซ้ำกันจากคอลัมน์ B ในคอลัมน์ C โดยใช้ฟังก์ชันต่างๆ เพื่อจัดการกับการตรวจสอบและดึงค่าที่ไม่ซ้ำกันมาเรียงต่อกัน เรามาอธิบายหลักการคิดในการเลือกใช้ฟังก์ชันเหล่านี้กันครับ:

1. COUNTIFS

ฟังก์ชัน COUNTIFS ใช้เพื่อตรวจสอบว่าแต่ละค่าจากช่วง $B$2:$B$10 ปรากฏในช่วง $C$1:C2 กี่ครั้ง ซึ่งจะทำให้เราสามารถทราบได้ว่าค่าใดที่ยังไม่ปรากฏในคอลัมน์ C ด้านบนของเซลล์ปัจจุบัน

2. MATCH

ฟังก์ชัน MATCH ใช้เพื่อตรวจหาตำแหน่งของค่าแรกที่มีค่าตรงกับ 0 ในอาร์เรย์ที่ได้จาก COUNTIFS ซึ่งตำแหน่งนี้จะเป็นตำแหน่งของค่าจากคอลัมน์ B ที่ยังไม่เคยปรากฏในคอลัมน์ C การใช้ MATCH(0, COUNTIFS(...), 0) จึงทำให้เราสามารถหาได้ว่าค่าใดเป็นค่าถัดไปที่ต้องดึงมา

3. INDEX

ฟังก์ชัน INDEX ใช้เพื่อนำค่าที่อยู่ในตำแหน่งที่ได้จาก MATCH ออกมา โดยใช้ช่วง $B$2:$B$10 เป็นแหล่งข้อมูลและตำแหน่งจาก MATCH เป็นตัวชี้ไปยังค่าในช่วงนี้

4. IFERROR

ฟังก์ชัน IFERROR ใช้เพื่อตรวจสอบและจัดการกับข้อผิดพลาดที่อาจเกิดขึ้นจาก MATCH โดยหาก MATCH ไม่พบค่า 0 ในอาร์เรย์จาก COUNTIFS จะเกิดข้อผิดพลาด #N/A ขึ้น ซึ่ง IFERROR จะช่วยให้คืนค่าเป็นข้อความว่าง (“”) แทนการแสดงข้อผิดพลาดนั้น

การคิดอย่างเป็นขั้นตอน:

  1. ตรวจสอบการมีอยู่ของค่าในช่วงที่ต้องการ:
    • ใช้ COUNTIFS เพื่อตรวจสอบว่าแต่ละค่าในคอลัมน์ B ปรากฏในคอลัมน์ C ด้านบนของเซลล์ปัจจุบันกี่ครั้ง โดยมองหาค่าที่นับได้เป็น 0 (แสดงว่ายังไม่ปรากฏ)
  2. ค้นหาตำแหน่งของค่าที่ไม่ซ้ำกัน:
    • ใช้ MATCH เพื่อหาตำแหน่งแรกที่มีค่า 0 ในอาร์เรย์จาก COUNTIFS ซึ่งบอกเราว่าค่าที่ไม่ซ้ำกันอยู่ที่ตำแหน่งใด
  3. ดึงค่าที่ไม่ซ้ำกันมาแสดง:
    • ใช้ INDEX เพื่อดึงค่าจากคอลัมน์ B ตามตำแหน่งที่ได้จาก MATCH
  4. จัดการกับข้อผิดพลาด:
    • ใช้ IFERROR เพื่อคืนค่าเป็นข้อความว่างในกรณีที่ไม่พบค่าที่ตรงกับเงื่อนไข

ตัวอย่างนี้จึงทำให้สามารถสร้างรายการที่ไม่ซ้ำกันในคอลัมน์ C จากข้อมูลในคอลัมน์ B ได้อย่างมีประสิทธิภาพครับ

Confidence: 100%

ขออธิบายเพิ่มเติมโดยผมเอง

สูตรนี้จริงๆ แล้วความต้องการคือจะหาว่าตัวแรกของแต่ละคำนั้นอยู่ลำดับไหนใน Range? แล้วเอา INDEX ถึงค่าพิกัดนั้นออกมา ถ้าดึงลำดับเลขพวกนี้มาได้ก็จะหาคำตอบได้

แกะสูตร Excel สุดซับซ้อน ให้เข้าใจง่ายขึ้นด้วย AI Chatbot : ThepExcel GPT 12

แล้วเลขลำดับพวกนี้จะหาได้จากไหนล่ะ??

มันก็อาจหาได้จากหลายแนวทาง แต่สูตรที่เราได้มา ใช้แนวคิดคือ สร้างลำดับเลข 0,1 ออกมาด้วย COUNTIFS จากนั้นใช้ MATCH หาว่าเลข 0 ตัวแรกอยู่ที่ลำดับที่เท่าไหร่?

ไอเดียคือ ถ้าอยากได้อันไหนให้สร้างเลข 0 ถ้าไม่อยากได้ให้สร้างเลข 1 (เช่น เราไม่อยากได้ตัวที่ซ้ำกับคำตอบที่ List ไปแล้ว) จากนั้นให้หา 0 ตัวแรกให้เจอ

ถ้าจะให้เข้าใจง่ายหน่อย เราจะพิจารณาสูตรที่ช่อง C3 ที่ควรจะได้ค่าคำว่า “มะละกอ” ซึ่งอยู่ลำดับที่ 3 ของ Range

=IFERROR(INDEX($B$2:$B$10, MATCH(0, COUNTIFS($C$1:C2, $B$2:$B$10), 0)), "")

ซึ่งส่วนตรง COUNTIFS นั้นเป็นแบบนี้

COUNTIFS(

{0;"แอปเปิ้ล"}, 

{"แอปเปิ้ล";"แอปเปิ้ล";"มะละกอ";"กล้วย";"กล้วย";"แอปเปิ้ล";"กล้วย";"ส้ม";"ส้ม"}

)

แปลว่าเอาคำแต่ละคำใน Range ยาวๆ ไปไล่หาใน Range คำตอบที่ทำไปก่อนหน้า
ซึ่งคือ {0;”แอปเปิ้ล”}

จะได้แบบนี้

{1;1;0;0;0;1;0;0;0}

แปลว่า
1 = เจอ (แบบนี้เราไม่อยากได้ เพราะจะซ้ำ เช่น ในเคสนี้อันไหนเป็น แอปเปิ้ลก็จะเจอ)
0= ไม่เจอ (แบบนี้เราอยากได้ แสดงว่าเป็นตัวที่ยังไม่มีในคำตอบ)

ทีนี้เราจะ เอาตัวแรกสุดที่ยังไม่มีในคำตอบ เราเลยใช้ MATCH หาเลข 0 แบบ Exact Match (จะได้ตัวแรกเสมอ) ก็จะได้คำตอบเป็น 3 นั่นเอง

ทำไมสูตรยุ่งยากจัง?

ตอบง่ายๆ เลย เป็นเพราะว่าใน Excel Version เก่า ฟังก์ชันยังไม่ค่อยเก่ง อารมณ์ประมาณว่า เหมือนกับใน Excel เก่าเรามีแค่มีด แต่ดันให้ไปตัดต้นไม้ ก็จะยากกว่าการมีเครื่องมือดีๆ แบบเลื่อยไฟฟ้า เหมือน UNIQUE ใน Excel ใหม่ๆ

หรืออย่างเคสที่คนชอบถามว่า ถ้าอยาก VLOOKUP แบบได้ทุกคำตอบที่เจอ อันนี้ถ้าเราใช้ Excel Version เก่า มันก็จะเป็นสูตรยาวๆ อีก แต่ถ้าเรามีฟังก์ชัน FILTER ให้ใช้นี่ทีเดียวจบง่ายๆ เลย

ดังนั้น บางทีที่คำตอบมันยากก็เพราะ Version ของ Excel ด้วยนะ แต่ผมจะแนะนำว่าถ้าใครยังไม่มีฟังก์ชันแบบ UNIQUE/FILTER แต่ไม่อยากเขียนสูตรยากๆ แบบนี้ ยังมีอีกทางเลือกที่ดีมาก คือให้ย้ายไปใช้ Power Query ซะ ใช้ง่ายและดีมากๆ แค่มี Excel 2016 ขึ้นไปก็ใช้ได้แล้ว

ทดสอบการแกะสูตร Case 2

ถ้าใครได้มีโอกาสไปเจอสูตรของคนเก่งๆ เทพๆ ในกลุ่ม Excel Super Fan แฟนพันธุ์แท้เอ็กเซล ใน Facebook ซึ่งเป็นพื้นที่ที่จะมีคนเก่งๆ มาตอบคำถามมากมาย ก็อาจจะพบขาประจำที่มาช่วยตอบคำถาม หรือมาฝึกแก้ปริศนาบ่อยๆ เช่น Excel Wizard, เมษ พงษ์อำไพ, محمد حلمي, Kwang Pachara, Wittaya Chainim และอีกมากมายหลายท่าน

ตัวอย่างเช่น โจทย์อันนี้

แกะสูตร Excel สุดซับซ้อน ให้เข้าใจง่ายขึ้นด้วย AI Chatbot : ThepExcel GPT 13

Download Practice File – https://lnkd.in/gcn62ByP

ถ้าไปไล่ดูคำตอบ อาจจะเจอสูตรประมาณนี้ ที่จะทำให้หลายคนช๊อคกับสูตรที่หลายท่านตอบมา…

แกะสูตร Excel สุดซับซ้อน ให้เข้าใจง่ายขึ้นด้วย AI Chatbot : ThepExcel GPT 14

ป.ล. ตัวอย่างนี้ที่ผมคัดเลือกมาให้ดูนี่คือสั้นแล้วนะ บางคำถามคำตอบยาวและซับซ้อนกว่านี้มากๆ และแต่ละท่านที่มาตอบก็อาจมีแนวคิดไม่เหมือนกัน สูตรที่ใช้ก็จะไม่เหมือนกัน

ทำไมมันถึงซับซ้อน?

โจทย์แบบนี้ ขนาดว่าคำตอบที่ได้มา เป็นการใช้สูตร Excel Version ล่าสุดแล้วนะ มีฟังก์ชันช่วยเหลือมากมาย ที่มันควรจะ Simple กว่า Excel Version เก่ามากแล้ว

แต่ที่มันซับซ้อนเพราะว่านอกจากการที่โจทย์มันยากแล้ว ที่สำคัญ คนตอบพยายามตอบโดยใช้สูตรแบบ Array แบบที่ว่าเขียนทีเดียวได้คำตอบออกมาทั้งตาราง โดยไม่ต้องทดสูตรไว้ที่อื่นเลย

ซึ่งตรงนี้อาจมีหลายเหตุผลนะ คือ อาจเป็นเพราะถ้ามันต้องทดออกมาไว้ที่อื่นก่อน จะอธิบายได้ลำบากพอสมควร (เพราะอาจต้องมีภาพประกอบ) สู้การตอบทีเดียวจบเลยไม่ได้ ถ้าใคร Copy สูตรไปลองใช้งานก็จะเห็นคำตอบเลยว่าใช้ได้หรือไม่

รวมถึงอีกเหตุผลที่อาจสำคัญมากกว่าคือ การตอบด้วยสูตรเดียวมันได้ฝึกสมองมากกว่า (อย่าลืมว่าพวกเขาเข้ามาตอบคำถามในกลุ่มเพื่อฝึกฝนฝีมือตัวเองด้วย)

และใน Excel Version ใหม่ๆ เราสามารถประกาศตัวแปรในสูตรได้แล้วด้วยฟังก์ชัน LET จึงไม่จำเป็นต้องไปทดไว้ที่อื่นอีกต่อไป แถมยังมีการใช้ LAMBDA เพื่อสร้างฟังก์ชันหรือวิธีการคำนวณขึ้นมาเองได้ด้วย

สูตรของ Excel สมัยใหม่นั้นมีหลายฟังก์ชันที่คนทั่วไปอาจไม่รู้จัก แต่จริงๆ แล้วมันดีมากๆ ผมจะแนะนำคร่าวๆ เผื่อใครอยากศึกษาต่อ

  • HSTACK = เอา Array มาต่อกันในแนวนอน (ข้างๆ)
  • VSTACK = เอา Array มาต่อกันในแนวตั้ง (บนล่าง)
  • TAKE = คัดเลือก Array มาบางส่วน
  • DROP = ทิ้ง Array ไปบางส่วน
  • TOCOL = เปลี่ยนโครงสร้างให้กลายเป็น Array 1 คอลัมน์
  • TOROW = เปลี่ยนโครงสร้างให้กลายเป็น Array 1 แถว
  • LET = ประกาศตัวแปรภายในสูตร (คล้ายๆ VAR ใน DAX หรือ let…in.. ของ MCode)
  • LAMBDA = สร้างฟังก์ชันขึ้นมาใช้เอง มักใช้ร่วมกับ LAMBDA Helper Function เช่น
    • MAP = โยนค่าจาก Range เข้าไปให้ LAMBDA ทีละค่า
    • SCAN = ส่ง Array เข้าไปคำนวณสะสม แสดงผลสะสมระหว่างทางออกมาเป็น Array
    • REDUCE = ส่ง Array เข้าไปคำนวณสะสม แสดงผลสะสมสุดท้ายออกมาอันเดียว

วิธีแกะสูตร

สมมติเราต้องการแกะสูตรของคุณโบ Excel Wizard ซึ่งเค้าตอบมาแบบนี้

=LET(c,A2:A30,d,C2:C7,IFNA(HSTACK(c,INDEX(C2:D7,
IFNA(XMATCH(c,d),(XMATCH(c,TOCOL(d+{-1,1}))+1)/2),{1,2})),""))

ถ้าลองเอาไปแปะดูก็จะได้คำตอบที่ถูกต้องเลยทันทีแบบนี้

แกะสูตร Excel สุดซับซ้อน ให้เข้าใจง่ายขึ้นด้วย AI Chatbot : ThepExcel GPT 15

ถ้าจะแกะสูตร Style นี้ด้วยตัวเอง ผมแนะนำให้ใช้ Excel Labs : Advanced Formula Environment เพื่อให้มันจัดระเบียบสูตรให้อ่านง่ายขึ้นก่อน เช่น

แกะสูตร Excel สุดซับซ้อน ให้เข้าใจง่ายขึ้นด้วย AI Chatbot : ThepExcel GPT 16

ด้วยการจัดแบบนี้เราจะเห็นชัดขึ้นว่า c, d คือการประกาศตัวแปรแต่ละพื้นที่ จากนั้นใช้ HSTACK เพื่อตั้ง List วันที่เดิมไว้ก่อน แล้วต่อด้วยการใช้ INDEX เพื่อดึงตัวที่เหมาะสมออกมาจาก C2:D7

ซึ่ง Logic ที่จะต้องลงลึกคือ Part ดึง ตัวที่เหมาะสมออกมาจาก C2:D7 นี่แหละ

ซึ่งคุณ Excel Wizard ใช้ XMATCH(c, d) เพื่อให้หาแบบ Exact Match ก่อน ถ้าเจอก็ใช้เลย แต่ถ้าไม่เจอ ก็ใช้ IFNA สั่งให้เป็นอีกค่าแทน ซึ่งคือ (XMATCH(c, TOCOL(d + {-1, 1})) + 1) / 2 ที่อ่านตอนแรกอาจดูไม่ออกว่าเอาไว้ทำอะไร?

เดี๋ยวลองเอาสูตรไปถาม ThepExcel GPT ดูดีกว่า ว่ามันจะตอบได้ไหม?

AI Prompt:

อธิบายสูตร excel นี้ได้ไหม

=LET(c,A2:A30,d,C2:C7,IFNA(HSTACK(c,INDEX(C2:D7,IFNA(XMATCH(c,d),(XMATCH(c,TOCOL(d+{-1,1}))+1)/2),{1,2})),""))
แกะสูตร Excel สุดซับซ้อน ให้เข้าใจง่ายขึ้นด้วย AI Chatbot : ThepExcel GPT 17

แน่นอนครับ มาดูที่สูตรกันนะครับ สูตรนี้เป็นการใช้ฟังก์ชัน LET เพื่อทำการจัดการข้อมูลในตารางโดยใช้ขั้นตอนต่างๆ ดังนี้:

มาดูกันทีละส่วนครับ:

  1. LET(c, A2, d, C2, …):
    • ฟังก์ชัน LET จะช่วยให้เราประกาศตัวแปรภายในสูตรได้ ในที่นี้ c คือช่วงของวันที่ในคอลัมน์ A (A2:A30) และ d คือช่วงของวันที่ในคอลัมน์ C (C2:C7).
  2. IFNA(HSTACK(c, …), “”):
    • ฟังก์ชัน IFNA ใช้เพื่อคืนค่าที่กำหนดหากฟังก์ชันที่อยู่ใน IFNA เกิดข้อผิดพลาด ในที่นี้จะคืนค่าเป็นช่องว่างถ้ามีข้อผิดพลาด
    • HSTACK ใช้เพื่อรวมข้อมูลในแนวนอน ในที่นี้คือการรวมช่วงของวันที่ใน c กับผลลัพธ์จาก INDEX.
  3. INDEX(C2, IFNA(XMATCH(c, d), (XMATCH(c, TOCOL(d + {-1, 1})) + 1) / 2), {1, 2}):
    • XMATCH(c, d) จะค้นหาค่าจาก c ในช่วง d และคืนค่าเป็นตำแหน่ง
    • IFNA ใช้เพื่อจัดการกับข้อผิดพลาด ถ้า XMATCH ไม่พบค่าใน d จะใช้การคำนวณเพิ่มเติมเพื่อค้นหาวันที่ใกล้เคียงในช่วง d โดยใช้ XMATCH กับ TOCOL(d + {-1, 1})
    • INDEX ใช้เพื่อดึงข้อมูลจากช่วง C2โดยใช้ตำแหน่งที่ได้จาก XMATCH, ในที่นี้ {1, 2} จะดึงค่าจากทั้งคอลัมน์ C และ D

เมื่อทำงานทั้งหมดเสร็จสิ้น สูตรนี้จะคืนค่าตารางที่มีวันที่ในคอลัมน์ A และคอลัมน์ที่ตรงกับวันที่ใกล้เคียงในช่วง C2และคอลัมน์ Vendor ที่เกี่ยวข้อง

AI มันอธิบายก้อนที่ 3 ใหญ่เกินไป ดูไม่รู้เรื่อง สามารถให้มันลองอธิบายเพิ่มได้

AI Prompt:

เน้นอธิบายส่วนนี้ (XMATCH(c, TOCOL(d + {-1, 1})) + 1) / 2

ได้ครับ มาดูส่วนนี้อย่างละเอียด:

(XMATCH(c, TOCOL(d + {-1, 1})) + 1) / 2

ส่วนนี้ของสูตรมีการทำงานเพื่อค้นหาวันที่ใกล้เคียงที่สุดในช่วง d (Delivery Date) และใช้ค่าเฉลี่ยของตำแหน่งที่ใกล้เคียงที่สุดสองตำแหน่งเพื่อหาแถวที่ตรงกัน มาดูกันทีละขั้นตอน:

  1. TOCOL(d + {-1, 1}):
    • ฟังก์ชัน TOCOL จะเปลี่ยนช่วง d ให้เป็นคอลัมน์
    • การบวก {-1, 1} เป็นการสร้างช่วงของวันที่ที่เพิ่มและลดหนึ่งวันจากวันที่ในช่วง d
    • ผลลัพธ์จะเป็นคอลัมน์ของวันที่ที่ขยายออกมาจากช่วง d
    ตัวอย่าง:
    • ถ้า d คือ C2:C7 (มีวันที่ 01/05/2024 ถึง 26/05/2024)
    • TOCOL(d + {-1, 1}) จะเป็นคอลัมน์ที่มีวันที่ก่อนและหลังช่วงนี้ เช่น 30/04/2024, 02/05/2024, 07/05/2024, เป็นต้น
  2. XMATCH(c, TOCOL(d + {-1, 1})):
    • ฟังก์ชัน XMATCH ค้นหาค่าจากช่วง c (Calendar Date) ในช่วงของวันที่ที่ปรับเปลี่ยน (เพิ่มหรือลดหนึ่งวัน) ซึ่งได้จาก TOCOL(d + {-1, 1})
    • คืนค่าตำแหน่งของวันที่ในช่วงที่ขยายนี้ซึ่งใกล้เคียงกับค่าจากช่วง c
  3. (XMATCH(c, TOCOL(d + {-1, 1})) + 1) / 2:
    • เมื่อได้ตำแหน่งของวันที่ใกล้เคียงจาก XMATCH แล้ว จะทำการคำนวณค่าเฉลี่ยของตำแหน่งนี้
    • + 1 และ / 2 ใช้เพื่อปรับตำแหน่งให้ได้ค่ากลางระหว่างตำแหน่งสองตำแหน่งที่ใกล้เคียงที่สุด
    ตัวอย่าง:
    • ถ้า XMATCH คืนค่าตำแหน่งเป็น 4
    • (4 + 1) / 2 จะเท่ากับ 2.5
    • ค่านี้จะใช้เป็นตำแหน่งเฉลี่ยระหว่างสองตำแหน่งเพื่อหาแถวที่ใกล้เคียงที่สุด

สรุป:

ส่วนนี้ของสูตรจะค้นหาค่าตำแหน่งที่ใกล้เคียงที่สุดในช่วงวันที่ที่ขยายออกมาจากช่วง d โดยใช้ฟังก์ชัน XMATCH แล้วคำนวณค่าเฉลี่ยของตำแหน่งสองตำแหน่งที่ใกล้เคียงที่สุด ด้วยการบวกหนึ่งและหารสองเพื่อลดความเบี่ยงเบนและหาค่าที่ใกล้เคียงที่สุดในวันที่จัดส่ง.

[Confidence 95%]

อธิบายเพิ่มเติม

จากที่ AI อธิบายมา มีบางส่วนที่อาจยังไม่ถูกต้องนัก โดยเฉพาะเรื่องขอววัตถุประสงค์ในการเขียนสูตรแล้วมี (…+1) /2 ในช่วงท้าย เดี๋ยวเรามาทำความเข้าใจกัน

ถ้าลองเขียนสูตรแค่ ตรง d + {-1, 1} มันจะได้ผลแบบนี้

แกะสูตร Excel สุดซับซ้อน ให้เข้าใจง่ายขึ้นด้วย AI Chatbot : ThepExcel GPT 18

พอทำเป็น TOCOL ก็จะได้เป็นคอลัมน์เดียวแบบนี้

แกะสูตร Excel สุดซับซ้อน ให้เข้าใจง่ายขึ้นด้วย AI Chatbot : ThepExcel GPT 19

ซึ่ง OK แหละ แบบนี้ช่วงวันที่มันก็จะเยอะึ้นมา ทำให้สามารถหาเจอได้ตามเกณฑ์ที่กำหนด อย่างไรก็ตาม อย่าลืมว่าสิ่งที่ต้องการคือตำแหน่งที่จะเอาไปใช้อ้างอิงใน Delivery Date + Vendor อันเดิม ดังนั้น เนื่องจากสมมติเดิม มี 6 วัน พอแตกแบบใหม่ ก็จะเป็น 6*2 = 12 วัน

ดังนั้นสมมติว่าใน List เจอเป็นตัวที่ 3 หรือ 4 ของจริงก็ถือว่าเป็นลำดับ 2

แบบนี้เราก็เลยต้องคำนวณย้อนกลับ โดยเอาไปหาร 2 แต่ทีนี้มันมี Trick พิเศษนิดหน่อย คือ คุณ Excel Wizard เค้ารู้อยู่แล้วว่า INDEX มันเอาลำดับที่เป็นจำนวนเต็มเท่านั้น ถ้าใส่ลำดับ 2.5 ก็จะเทียบเท่ากับ 2 ได้เลย

แกะสูตร Excel สุดซับซ้อน ให้เข้าใจง่ายขึ้นด้วย AI Chatbot : ThepExcel GPT 20

ดังนั้นเค้าเลยใช้วิธี +1 เข้าไปก่อนที่จะหาร 2 เพื่อให้ลำดับ Perfect นั่นเอง (ซึ่งจริงๆ ไม่เกี่ยวกับการหาค่าเฉลี่ยตามที่ AI อธิบาย แต่ว่าอย่างน้อยมันก็ทำให้เราพอคลำทางถูก)

แกะสูตร Excel สุดซับซ้อน ให้เข้าใจง่ายขึ้นด้วย AI Chatbot : ThepExcel GPT 21

แล้วพอได้ลำดับที่ถูกต้อง ก็ใช้ INDEX ดึงค่าออกมา ซึ่ง Excel Wizard มีการใส่ {1,2} แปลว่าให้ดึงผลทั้งคอลัมน์ 1 และ 2 ออกมาทั้งคู่เลย

สุดท้ายก็ได้แบบนี้ ซึ่งได้ผลลัพธ์ทั้งหมดออกมาจากสูตรเดียว

แกะสูตร Excel สุดซับซ้อน ให้เข้าใจง่ายขึ้นด้วย AI Chatbot : ThepExcel GPT 22

ข้อจำกัดของ AI ในการอธิบายสูตรและการแก้ไข

แม้ว่า AI Chatbot อย่าง ThepExcel GPT จะสามารถช่วยอธิบายสูตร Excel ที่ซับซ้อนได้ดี แต่สำหรับสูตรที่ซับซ้อนมากๆ หรือใช้ฟังก์ชันที่ค่อนข้างใหม่ บางครั้ง AI ก็อาจจะอธิบายได้ไม่ครบถ้วนทั้งหมด หรือไม่เข้าใจวัตถุประสงค์ของสูตรได้อย่างถ่องแท้ ดังนั้นเราจึงควรใช้วิจารณญาณในการพิจารณาคำอธิบายจาก AI ควบคู่ไปกับการทำความเข้าใจด้วยตัวเองด้วย และหากสงสัยเราก็สามารถซักถาม AI เพิ่มเติมได้เรื่อยๆ จนกว่าจะเข้าใจครับ

อย่างไรก็ตาม ในชีวิตจริงสูตรที่เราเจอส่วนใหญ่แล้วจะไม่ได้ซับซ้อนขนาดที่ผมเอามาให้ดูหรอกครับ ดังนั้นถาม AI ได้เลย เอารูปประกอบให้มันดูด้วยนะ มันตอบได้ เชื่อผม

สรุป

การใช้ AI Chatbot อย่าง ThepExcel GPT สามารถช่วยให้เราเข้าใจสูตร Excel ที่ซับซ้อนได้ง่ายขึ้นอย่างมาก เพียงแค่ส่งสูตรหรือรูปภาพไป มันก็จะช่วยแกะและอธิบายให้เราเข้าใจได้อย่างเป็นขั้นตอน และยังสามารถตอบคำถามข้อสงสัยเพิ่มเติมได้อีกด้วย

อย่างไรก็ตาม เราควรใช้ AI เป็นตัวช่วยเสริมในการทำความเข้าใจ ควบคู่ไปกับการพิจารณาและตีความผลลัพธ์อย่างรอบคอบด้วยตัวเอง เพื่อให้สามารถนำความรู้ที่ได้ไปประยุกต์ใช้และต่อยอดได้อย่างเต็มประสิทธิภาพครับ

หากสนใจลองใช้ ThepExcel GPT ในการแกะสูตร Excel สามารถเข้าไปที่ลิงก์นี้ได้เลยครับ: https://chatgpt.com/g/g-GQjMXWNEy-thepexcel-gpt

หวังว่าบทความนี้จะเป็นประโยชน์กับทุกท่านนะครับ สู้ๆ กับการเรียนรู้ไปด้วยกันครับ!

Power BI Workshop 2024 ตุลาคมนี้
อบรม In-House Training

Feedback การใช้งาน AI Chatbot