เปิดดูทีละหน้า-วิธีแก้ข้อมูลล้นแล้วเจอ Error Spill

#SPILL! เป็น Error ที่พบบ่อยมากใน Excel 365 หรือรุ่นตั้งแต่ 2021 เป็นต้นมา ที่เริ่มต้นความสามารถพิเศษที่เรียกว่า Dynamic Array โดยสาเหตุหนึ่งที่คำเตือนนี้จะเกิดขึ้น คือ ต่อเมื่อสูตรที่สร้างขึ้นนั้นไม่สามารถกระจายค่าที่หาได้ออกไปจนครบทั้งตาราง

Spill แปลว่า ล้น หรือ หก เช่น เวลาเทน้ำจนเต็มแก้วแล้วล้น หรือมีอะไรมาขวางทางน้ำที่ไหล น้ำก็จะล้น

วิธีแก้ไขแบบง่ายๆ ให้ใช้สูตร CountA ตรวจสอบก่อนว่าพื้นที่ด้านล่างที่ติดกับสูตรที่จะกระจายค่าออกไปนั้นเป็นช่องว่างที่ไม่มีอะไรกรอกไว้ไหม โดยใช้สูตรแบบนี้

=IF( CountA(Range) = 0, สูตรที่ต้องการใช้, 0)

ถ้านับแล้วพบว่าในพื้นที่นั้นสูตร CountA = 0  แสดงว่าไม่มีอะไรมาขวางก็ให้ใช้สูตรนั้นได้ แต่ถ้าไม่ใช่ให้แสดงค่า 0 แทน

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

=CHOOSEROWS( MyData, SEQUENCE( Transactions, 1, (Transactions*(PageNum-1))+1) )

พอกรอกเลขหน้า PageNum ที่อยากจะดูลงไปในเซลล์สีส้ม K2 สูตรนี้จะเลือกแสดงข้อมูลออกมาให้เห็นทีละหน้า

MyData เป็นพื้นที่ข้อมูลทั้งหมด ซึ่งมีจำนวน 380 รายการ หาได้จากสูตร Rows(MyData) ในเซลล์ C2

SEQUENCE( Transactions, 1, (Transactions*(PageNum-1))+1) ) ทำหน้าที่ดึงข้อมูลแต่ละหน้า โดย

เซลล์สีเขียว F2 ตั้งชื่อว่า Transactions เป็นจำนวนรายการต่อหน้า ตอนนี้สมมติว่าอยากให้แสดงทีละ 20 รายการต่อหน้า

(Transactions*(PageNum-1))+1) เป็นสูตรหาเลขที่ลำดับรายการเริ่มต้นของแต่ละหน้า

หน้าที่ 1 หาเลขที่รายการเริ่มต้นมาจาก 20*(1-1)+1 ก็จะเริ่มต้นที่รายการที่ 1 

หน้าที่ 2 หาเลขที่รายการเริ่มต้นมาจาก 20*(2-1)+1 ก็จะเริ่มต้นที่รายการที่ 21

หน้าที่ 3 หาเลขที่รายการเริ่มต้นมาจาก 20*(3-1)+1 ก็จะเริ่มต้นที่รายการที่ 41

หน้าที่ 9 หาเลขที่รายการเริ่มต้นมาจาก 20*(9-1)+1 ก็จะเริ่มต้นที่รายการที่ 161

หน้าที่ 15 หาเลขที่รายการเริ่มต้นมาจาก 20*(15-1)+1 ก็จะเริ่มต้นที่รายการที่ 281 

Download ตัวอย่างได้จาก 

https://drive.google.com/file/d/1EJaRC4PNHa3gArrYkpai0TmEmzA2qJYA/view?usp=sharing

วิธีนี้นอกจากใช้ช่วยแก้ Spill แล้ว ยังช่วยทำให้สามารถเลือกดูรายการไหนก็ได้โดยไม่ต้องเสียเวลาไปเลื่อนหน้าจอ หรือให้นำข้อมูลที่เลือกมาแต่ละหน้านี้ไปใช้คำนวณต่อ Excel จะใช้เวลาคำนวณเหลือนิดเดียว