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

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

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

แนะนำ ThepExcel GPT

ThepExcel GPT นี้ ผมตั้งใจพัฒนาขึ้นมาเพื่อตอบปัญหาเกี่ยวกับ Excel ให้การแก้ไขสูตรคลุมเครือเป็นเรื่องกล้วยๆ สำหรับทุกคน

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

หากเพื่อนๆ ลองใช้ ThepExcel GPT AI Chatbot แล้วรู้สึกอย่างไร อย่าลืมบอกผมด้วยที่นี่ https://forms.gle/Ku7xn9jrkwhjBWBRA

Feedback การใช้งาน

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

ที่มาที่ไป

แต่ข่าวดีคือ เมื่อต้นเดือนพฤษภาคม 2567 OpenAI ได้ปล่อยตัว GPT-4o และเปิดให้ใช้ GPTs ได้ฟรี!

ล่าสุด วันที่ 30 พฤษภาคม 2567 นี้เอง คนที่ใช้ ChatGPT ฟรีก็ใช้ GPTs ได้แล้วครับ ผมก็เลยแจก Link ให้ใช้กันฟรีๆ (แต่ก็แน่นอนแหละ คนใช้เยอะ limit ก็เต็มไว 555)

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

ทีนี้มาทดสอบกันดูนะ ว่า AI จะช่วยเราไขความลับของสูตร Excel ยากๆ อย่างไร?

ตัวอย่างโจทย์ เช่น เราต้องการแสดงค่าที่ไม่ซ้ำจากคอลัมน์ B2:B10

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

ถ้าเพื่อนๆ มี Excel เวอร์ชั่นใหม่ (หรือ Google Sheets) ที่รองรับ Dynamic Array ก็สามารถเขียนสูตรลงที่เซลล์ C2 ได้ง่ายๆ เลย

ผลลัพธ์ก็จะมีโชว์ในเซลล์ C2:C5 โดยอัตโนมัติ

=UNIQUE(B2:B10)

แต่ถ้าเพื่อนๆ ใช้ Excel รุ่นเก่าที่ไม่มีฟังก์ชัน UNIQUE ก็อาจจะเศร้าเล็กน้อย 😭

ถ้าเราลองไปหาสูตรในอินเทอร์เน็ต เราอาจเจอสูตรหนึ่งที่ต้องเขียนในเซลล์ C2 โดยก่อนอื่นต้องกด Ctrl+Shift+Enter เพราะมันเป็นสูตรแบบ Array Formula ก่อนที่จะทำการ Copy ลงมาในเซลล์ล่างๆ

วิธีแกะสูตรนี้ถ้าเรายังใช้ Excel เก่า ก็อาจต้องใช้เครื่องมือ Formula -> Evaluate Formula ซึ่งจะแสดงขั้นตอนการทำงานทีละขั้นตอน ซึ่งอาจไม่สะดวกนักเพราะเราไม่สามารถตรวจเช็คแต่ละส่วนได้ตามใจ

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

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

แต่ถ้าเรามี Excel เวอร์ชั่น 2016 ขึ้นไป เราจะมี Add-ins ชื่อว่า Excel Labs ที่เจ๋งมาก มีเครื่องมือ Advanced Formula Environment ที่ทรงพลังกว่า

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

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

ลองถาม ThepExcel GPT (พร้อมแนบรูป) มันสามารถจะอธิบายได้อย่างดี

ได้รับคำอธิบายประมาณนี้ ผมจะขออธิบายการทำงานของสูตรในเซลล์ C3 นะครับ:

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

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

AI Prompt:

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

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

Confidence: 100%

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

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

ฟังก์ชัน MATCH ใช้เพื่อตรวจสอบตำแหน่งแรกที่ค่าตรงกับ 0 ในอาร์เรย์จาก COUNTIFS ที่แปลว่าค่าในคอลัมน์ B ที่ยังไม่ถูกใช้งาน

3. INDEX

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

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

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

ในกลุ่มExcel Super Fan แฟนพันธุ์แท้เอ็กเซลใน Facebook เป็นที่ที่มีคนเก่งๆ มาตอบคำถาม เช่น Excel Wizard, เมษ พงษ์อำไพ และอีกหลายท่าน

อย่างโจทย์นี้

ดาวน์โหลดไฟล์ตัวอย่าง – https://lnkd.in/gcn62ByP

ดูคำตอบแล้วอาจจะช๊อคกับสูตรของหลายคน…

ปล. ตัวอย่างนี้คือย่อแล้ว จริงๆ ยังมีที่ซับซ้อนกว่านี้ และคำตอบอาจต่างกันไปตามการคิดของแต่ละคน

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

แม้ว่าคำตอบมาจากการใช้สูตรใน Excel เวอร์ชันล่าสุด แต่ก็ยังซับซ้อนเพราะเป็นการใช้สูตร Array แบบเขียนทีเดียวได้ผลลัพธ์ออกมาทั้งตาราง

COUNTIFS(

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

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

)

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

และบอกเลยว่าในเวอร์ชั่นใหม่ๆ เราสามารถประกาศตัวแปรในสูตรได้ด้วย LET และสร้างฟังก์ชันเองด้วย LAMBDA

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

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

ลองแกะสูตรที่คุณโบ Excel Wizard ตอบแบบนี้ดูสามารถทำง่ายๆ

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

ใช้แป๊ะที่ตารางแล้วเห็นผลเลย

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

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

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

ที่ต้องพิจารณาให้ชัดเจนคือ ส่วนที่ดึงค่าจาก C2:D7

ซึ่งในนี้ใช้ XMATCH(c, d) หาแบบ Exact Match ถ้าเจอใช้ได้เลย ไม่เจอก็ใช้ IFNA ใส่เป็นค่าอื่นแทนคือ (XMATCH(c, TOCOL(d + {-1, 1})) + 1) / 2

ลองถาม AI ดูว่า ThepExcel GPT ว่ามันจะช่วยเราได้ไหม?

มาเลยเพื่อนๆ ดูที่สูตรนี้ AI บอกว่าฟังก์ชัน LET มีขั้นตอนในการจัดการข้อมูลในตาราง

มาดูแต่ละส่วนในสูตรทีละหน่อย

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

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

บางที AI เจอบางส่วนที่เข้าใจยาก ให้ AI ลองอธิบายเพิ่มดูดีไหม?

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

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

สรุป:

ส่วนนี้ของสูตรทำให้หาตำแหน่งที่ใกล้ที่สุดในวันที่จากช่วง d โดยดูค่าเฉลี่ยตำแหน่งสองจุดที่ใกล้เคียงที่สุด ลดความเบี่ยงเบนและหาได้อย่างง่ายดายตามวันที่จัดส่ง

• 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 เข้าไปคำนวณสะสม แสดงผลสะสมสุดท้ายออกมาอันเดียว

วิธีแกะสูตร

[Confidence 95%]

=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})),""))

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

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

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

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

ถ้าพบลำดับ 3 หรือ 4 ที่แท้จริงจะถือว่าเป็นลำดับ 2

ดังนั้นต้องคำนวณด้วยการหาร 2 ซึ่งเป็นเคล็ดไม่ลับ โดยรู้ว่า INDEX จะใช้ค่านับเต็มแบบ +1 แล้วหาร 2 ให้ได้ลำดับที่กลมกลืน

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})),""))

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

AI Prompt:

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

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

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

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

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

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

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

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

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

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

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

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

ข้อจำกัดของ 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

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