ผมนั่งปั่นงานตั้งแต่สามทุ่มถึงตีสอง ง่วงมาก ตากำลังจะปิด เหลือเพียงกดเอ็นเทอร์ Excel ไฟล์นี้ก็เสร็จ
พอกดปุ๊ป มันขึ้น #SPILL!
เฮ้ย! ผีหลอก

SPILL คืออะไร?

SPILL คือคุณสมบัติใหม่ของ Microsoft 365 (Office 365) แปลไทยง่าย ๆ ว่า ‘ทะลัก’

นั่นคือ สูตรที่เขียนได้ผลลัพธ์มากกว่า 1 ค่า มันก็เลย ‘ทะลัก’ ออกมา

ยังไง?

เช่น เดิมทีมีข้อมูลบันทึกการขายแบบนี้

ลองเขียนสูตรที่เซลล์ E2 เป็น

=C2:C9*10

แล้วกด Enter จะได้ผลลัพธ์เป็น

นั่นคือเขียนสูตรที่ E2 ที่เดียว แต่ ‘ทะลัก’ ได้ผลลัพธ์ 8 ค่า! (E2-E9)

แต่ละค่าคือการนำคอลัมน์ C ไปคูณ 10

สังเกตว่าเซลล์ E2-E9 มีกรอบล้อมรอบ กรอบนี้คือขอบเขตการ SPILL

ถ้าคลิกเซลล์อื่นที่อยู่ในกรอบ เช่น E3 จะพบว่าสูตรเป็นสีเทา (ใน formula bar) ไม่สามารถลบหรือแก้ไขได้

ถ้าคลิกที่ E4, E5, E6 ก็ได้ผลลัพธ์เช่นเดียวกัน

ทำไม?

เพราะสูตรที่แท้จริงอยู่ในเซลล์ E2 สิ่งที่แสดงในเซลล์ E3-E9 เป็นเพียงการ SPILL

ถ้าต้องการลบสูตรต้องทำยังไง?

ให้ลบเซลล์ที่เขียนสูตร เช่น ในที่นี้คือเซลล์ E2
นั่นคือ ถ้าลบ E2 เพียงเซลล์เดียว เซลล์ E3-E9 ที่เป็นการ SPILL ก็จะหายไปด้วย

Note1: SPILL เป็นคุณสมบัติที่มีเฉพาะใน Microsoft 365 (Office 365) เวอร์ชันล่าสุด ถ้าใครใช้ Microsoft 365 แต่เขียนสูตรนี้แล้วไม่เจอการ SPILL แปลว่าต้องอัพเดตเวอร์ชัน

Note2: SPILL เป็นคุณสมบัติที่มาพร้อมกับ Dynamic Array Formula ทำให้การเขียนสูตรง่ายขึ้น และไม่ต้องกด Ctrl+Shift+Enter

Note3: SPILL และ Dynamic Array Formula ไม่มีใน Excel 2010/ 2013/ 2016/ 2019

ขอบเขตของการ SPILL ไม่จำเป็นต้องเท่ากับจำนวนบรรทัดของข้อมูลต้นทาง แต่ขึ้นกับว่าผลลัพธ์นั้นมีกี่ค่า
เช่น ถ้าข้อมูลต้นทางคือ B2:B9 ไม่จำเป็นต้อง SPILL ได้ 8 ค่า

ลองเปลี่ยนสูตรที่เซลล์ E2 เป็น
=UNIQUE(B2:B9)

จะได้ผลลัพธ์เป็น Ann, Frank, John, Sue กรอบของ SPILL แสดงเพียง 4 ค่าเท่านั้น

ทำไมได้ 4 ค่า?

เพราะเคสนี้ใช้ฟังก์ชัน UNIQUE ซึ่งเป็นหนึ่งในฟังก์ชันใหม่ของ Dynamic Array Formula

ฟังก์ชัน UNIQUE คืออะไร?

ฟังก์ชัน UNIQUE มีความหมายตามชื่อเลย นั่นคือแสดงเฉพาะค่าที่เป็น unique หรือค่าที่ไม่ซ้ำ

จากชื่อลูกค้าใน B2:B9 พบว่าบางชื่อซ้ำ เช่น Ann 3 ค่า, Frank 2 ค่า, John 2 ค่า

พอเขียนสูตรเป็น =UNIQUE(B2:B9) แปลว่าให้แสดงเฉพาะชื่อที่ไม่ซ้ำ พอเกิดการ SPILL ก็ SPILL ตามจำนวนค่าที่ได้ นั่นคือ 4 ค่า (ไม่ใช่ 8 ค่า)

“แล้วถ้าต้องการอ้างอิงสูตรจากช่วงที่ SPILL ล่ะ?”
“ยังไง?”
“เช่น อยากนับว่ามีลูกค้าทั้งหมดกี่คน?”
“ถ้าอยากนับข้อมูลที่ SPILL ให้ระบุเซลล์เริ่มต้นของ SPILL แล้วตามด้วยเครื่องหมาย # ได้เลย”

เช่น เขียนสูตรเป็น

=COUNTA(E2#)

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

=COUNTA(E2:E5)
สูตรจะแสดงเป็น

=COUNTA(E2#)

เพราะโปรแกรม “เดา” ว่าต้องการเลือกข้อมูลทั้งหมดที่ SPILL
แต่เขียนเป็น =COUNTA(E2#) น่าจะสั้นและง่ายกว่า แถมไม่ต้องลากครอบพื้นที่ด้วย ^__^

แล้ว #SPILL! คืออะไร?

#SPILL! เป็นความผิดพลาดที่บ่งบอกว่าสูตรนี้ SPILL ไม่ได้ หรือแปลเป็นไทยว่า ทะลักแล้ว ‘โดนขวางทาง’

งง?

เช่น พิมพ์คำว่า “จำนวนลูกค้าทั้งหมด” ไว้ที่เซลล์ E5 แบบนี้

เจตนาคือต้องการเขียนสูตร UNIQUE ที่เซลล์ E2 แล้วค่อยนับจำนวนลูกค้าที่เซลล์ F5

แต่พอเขียนสูตร UNIQUE(B2:B9) ที่เซลล์ E2 จะได้ผลลัพธ์ 4 ค่า ซึ่งจะแสดงในเซลล์ E2:E5 แต่เซลล์ E5 ดั๊นมีคำว่า “จำนวนลูกค้าทั้งหมด” มาขวางทางไว้ก่อน ผลลัพธ์คือ

#SPILL!

นั่นคือ ‘ทะลัก’ ไม่ได้ เพราะโดน “จำนวนลูกค้าทั้งหมด” ขวางทาง (สังเกตว่าเซลล์ E5 อยู่ในกรอบของการ SPILL)

แล้วจะแก้ #SPILL! ยังไง?

แก้ไขได้อย่างน้อย 4 วิธีคือ

1. กำจัดข้อมูลที่ขวางทาง

วิธีนี้ตรงไปตรงมา นั่นคือ กำจัดข้อมูลที่ขวางทางซะ

เช่น เลื่อนคำว่า “จำนวนลูกค้าทั้งหมด” ไปไว้ที่เซลล์ E7 ซึ่งอยู่เลยขอบเขตการ SPILL จบ!

วิธีนี้ง่ายและดีที่สุด ^__^

2. ใส่เครื่องหมาย @ นำหน้า

ใส่เครื่องหมาย @ นำหน้าช่วงข้อมูล
เช่น จากเดิมที่ระบุช่วงข้อมูลเป็น C2:C9*10 ก็ระบุเป็น

=@C2:C9*10

จะพบว่าสูตรนี้ไม่เกิดการ SPILL และได้ผลลัพธ์เพียงค่าเดียว
อย่างไรก็ตาม สูตรนี้ไม่สามารถก็อปปี้ได้ เพราะการอ้างอิงช่วงเซลล์จะเลื่อนทั้งหมด เช่น กลายเป็น @C4:C11*10 (ทั้งที่ควรเป็น @C2:C9*10 )

ทางแก้ง่าย ๆ คือ ให้ล็อคช่วงข้อมูลด้วย $ หรือเขียนสูตรเป็น

=@$C$2:$C$9*10

แต่โดยส่วนตัวแล้ว ไม่แนะนำวิธีนี้เลย

“ทำไม?”
เพราะใช้ได้ผลกับข้อมูลที่บรรทัดตรงกันเท่านั้น

นั่นคือเคสนี้ใช้ได้ผลกับเซลล์ในบรรทัด 2-9 ถ้านำสูตรนี้ไปใช้กับเซลล์บรรทัดอื่น จะได้ผลลัพธ์เป็น #VALUE!

การอ้างอิงแบบ @ (เช่น @$C$2:$C$9) ศัพท์เทคนิคเรียกว่า Implicit Intersection ใช้ได้ผลแค่บรรทัดเดียวกันเท่านั้น ถ้าเลี่ยงได้ให้เลี่ยง

ยกเว้นข้อมูลถูกจัดฟอร์แมตเป็น Table อนุโลมให้ใช้ได้ โดยการอ้างอิงจะเป็น

@TableName[ColumnName]

เช่น ลองทำข้อมูลนี้ให้เป็น Table (กด Ctrl+T) โปรแกรมจะตั้งชื่อเป็น Table1 (ชื่อดีฟอลต์ สามารถเปลี่ยนได้) ถ้าต้องการนำคอลัมน์ C ไปคูณ 10 ก็เขียนสูตรเป็น

=Table1[@Volume]*10

จะพบว่าไม่เกิดการ SPILL แถมสามารถก็อปปี้สูตรมาด้านล่างได้ด้วย

เมื่อปรับฟอร์แมตข้อมูลเป็น Table มักเขียนสูตรอ้างอิงในบรรทัดเดียวกันเสมอ นี่คือรูปแบบที่พบได้บ่อย

แต่ถ้าเขียนสูตรไม่ตรงกับบรรทัด แล้วปรับฟอร์แมตเป็น Table ก็เจอผลลัพธ์ #VALUE! เช่นกัน

ดังนั้น ถ้าจะใช้ @ นำหน้า ควรใช้ในกรณีปรับฟอร์แมตข้อมูลเป็น Table เท่านั้น

3. กด Ctrl+Shift+Enter

อันที่จริงแล้วการเขียนสูตรในรูปแบบ C2:C9*10 คือการเขียนสูตรแบบอาร์เรย์ (Array Formula) เดิมทีถ้าจะเขียนสูตรแบบนี้ต้องกด Ctrl+Shift+Enter (แทนการกด Enter)

ด้วยคุณสมบัติ Dynamic Array Formula การกด Ctrl+Shift+Enter ไม่จำเป็นแล้ว สามารถกด Enter อย่างเดียวได้เลย

แต่ไม่ได้แปลว่าจะกด Ctrl+Shift+Enter ไม่ได้ ตรงกันข้าม ยังคงกด Ctrl+Shift+Enter ได้เหมือนเดิม แต่สูตรจะไม่ SPILL

เช่น เขียนสูตรเป็น =C2:C9*10 พอกด Ctrl+Shift+Enter จะมีวงเล็บปีกกาครอบหน้าหลัง แต่ไม่เกิดการ SPILL

{=C2:C9*10}

แต่ถ้าต้องการเขียนสูตรแล้วแสดงผลลัพธ์หลายค่า ให้เลือกพื้นที่ไว้ล่วงหน้าแล้วเขียนสูตร เช่น เลือกพื้นที่เซลล์ C11:C13 แล้วพิมพ์สูตร

=C2:C9*10

จากนั้นกด Ctrl+Shift+Enter
จะพบว่าสูตรที่ได้คือ

{=C2:C9*10}

มีวงเล็บปีกกาครอบหัว-ท้าย ได้ผลลัพธ์ 3 ค่า และไม่เกิดการ SPILL

วิธีนี้ไม่แสดงผลลัพธ์เป็น #VALUE! เพราะไม่ใช่การอ้างอิงแบบ Implicit Intersection ( @ ) แต่โดยส่วนตัวแล้วไม่ค่อยแนะนำ

“อ้าว! ทำไมล่ะ?”

เพราะถ้าต้องการแสดงผลหลายค่าต้องเลือกพื้นที่เผื่อไว้ก่อน

เช่น อยากแสดงชื่อลูกค้าที่ไม่ซ้ำ แต่ไม่รู้ว่ามีลูกค้ากี่คน ก็ต้องเลือกพื้นที่เผื่อไว้ล่วงหน้า เช่น เลือกพื้นที่ E2:E4 แล้วเขียนสูตร

=UNIQUE(B2:B9)

จากนั้น กด Ctrl+Shift+Enter สูตรจะกลายเป็น

{=UNIQUE(B2:B9)}

จะพบว่าสูตรนี้ไม่เกิดการ SPILL แต่ได้ข้อมูลไม่ครบ (ไม่แสดงชื่อ Sue)

ถ้าเผื่อพื้นที่มากไป เช่น เลือกพื้นที่ E2:E6 แล้วเขียนสูตรเดิม

จะพบว่ามี #N/A เพราะเผื่อพื้นที่เยอะเกินไป

อีกทั้งการกด Ctrl+Shift+Enter คล้ายกับให้โปรแกรมใช้รูปแบบการคำนวณแบบเก่า ทำให้การคำนวณของไฟล์ ‘หนักขึ้น’ โดยไม่จำเป็น

4. ใช้ร่วมกับฟังก์ชันอื่น

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

ทางแก้คือใช้ร่วมกับฟังก์ชันอื่น ซึ่งฟังก์ชันพวกนี้มักเป็นฟังก์ชันเกี่ยวกับการสรุป หรือ aggregate เช่น

• SUM
• COUNT
• COUNTA
• AVERAGE
• MAX
• MIN
• LARGE
• SMALL
• AGGREGATE
• TEXTJOIN

เช่น อยากรวมผลลัพธ์จาก C2:C9*10 ก็นำ SUM ไปครอบ หรือเขียนสูตรเป็น

=SUM(C2:C9*10)

ได้ผลลัพธ์เป็น 3,600

ต้องการนับว่ามีลูกค้าที่ไม่ซ้ำกี่คน ก็นำฟังก์ชัน COUNTA ไปครอบ

=COUNTA(UNIQUE(B2:B9))

ได้ผลลัพธ์เป็น 4

ต้องการแสดงชื่อลูกค้าทั้งหมดที่ไม่ซ้ำ และคั่นด้วย , ก็นำฟังก์ชัน TEXTJOIN ไปครอบ

=TEXTJOIN( ” , “, TRUE, UNIQUE(B2:B9) )

ได้ผลลัพธ์เป็น Ann , Frank , John , Sue (อ่านวิธีใช้ฟังก์ชัน TEXTJOIN ได้จาก บทความนี้ )

สรุปคือ การแก้ #SPILL! ที่ง่ายและดีที่สุดคือวิธีที่ 1 (กำจัดข้อมูลที่ขวางทาง) แต่ถ้าต้องการให้แสดงผลลัพธ์เพียงเซลล์เดียว ให้ทำแบบวิธีที่ 4

สำหรับใครที่สนใจไฟล์ตัวอย่างของบทความนี้ สามารถดาวน์โหลดเพื่อศึกษาเพิ่มเติมได้เลย

เป็นยังไงกันบ้างกับ SPILL และ #SPILL!

โดยส่วนตัวแล้วผมชอบ SPILL มาก จากนี้ไปไม่ต้องเขียนสูตรแบบเผื่อบรรทัดแล้ว ถ้าได้ผลลัพธ์หลายค่าเดี๋ยวมัน ‘ทะลัก’ เอง

สูตร ‘ทะลัก’ ได้ แต่งานห้าม ‘ทะลัก’ นะ ^^

.
หากคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่โดยคลิก Like เฟซบุ๊กแฟนเพจวิศวกรรีพอร์ต

อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^__^

.
Credit
https://exceljet.net/formula/how-to-fix-the-spill-error
https://www.spreadsheetweb.com/excel-spill/