ค่าใช้จ่ายแอบแฝงจากการอ้างอิงทั้งคอลัมน์ใน Excel: เรียนรู้ 3 ทางเลือกเพื่อเพิ่มประสิทธิภาพเวิร์กบุ๊กของคุณ

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

ต้นทุนแฝงของภาษีล้านแถว

การอ้างอิงทั้งคอลัมน์ (A:A) อาจบังคับให้ Excel จัดการกับ 1,048,576 แถว โดยเฉพาะอย่างยิ่งกับการคำนวณแบบอาเรย์หรือฟังก์ชันที่ประเมินแต่ละเซลล์ ลองใช้ชุดข้อมูล 50 แถวนี้เป็นตัวอย่าง ในเซลล์ D1 คุณต้องการใช้ UNIQUE เพื่อแสดงรายชื่อทั้งหมดของบุคคลที่ระบุในคอลัมน์ B

เพื่อให้สูตรสามารถดึงข้อมูลใหม่ที่เพิ่มเข้ามาในแถวที่ 51, 52 และต่อไปได้ คุณอาจใช้การอ้างอิงทั้งคอลัมน์:

=UNIQUE(B:B)

สูตรนี้จะตรวจสอบ ทุก เซลล์ในคอลัมน์ B — รวมถึงแถวว่างใต้ช่วงที่ใช้งานอยู่ ซึ่งแทนด้วยศูนย์ที่ไม่ตรงกัน

การทดสอบศูนย์: การพิสูจน์ภาระ

หากคุณต้องการดูว่า Excel ต้องรับมือกับอะไรบ้างเมื่อคุณป้อนการอ้างอิงทั้งคอลัมน์ ให้พิมพ์ในเซลล์ D1:

=B:B

เมื่อคุณเลื่อนลงมาด้านล่าง คุณจะเห็นศูนย์ต่อท้ายมากกว่าล้านตัว ซึ่งแทนเซลล์ว่างในคอลัมน์ B ตั้งแต่แถว 51 ลงไป

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

Excel เวอร์ชันใหม่ใช้ "การคัดกรองช่วง" เพื่อข้ามแถวว่าง แต่ก็ขึ้นอยู่กับว่าช่วงที่ใช้งานสะอาดหรือไม่ หากแถวที่ 10,000 มีช่องว่างเล็ก ๆ Excel จะถือว่าพื้นที่ว่างทั้งหมดเป็นข้อมูลที่ใช้งานอยู่ นอกจากนี้ สูตรยังต้องทำงานร่วมกับช่วงทั้งหมดเพื่อตรวจสอบขอบเขตด้วย

นี่คือสามเครื่องมือที่มีมาในตัวที่คุณสามารถใช้เพื่อป้องกันพฤติกรรมนี้

วิธีแก้ไขที่ 1: แปลงข้อมูลของคุณเป็นตาราง Excel

วิธีที่มั่นคงที่สุดในการหลีกเลี่ยงภาษีล้านแถวคือการใช้ตาราง Excel—วัตถุแบบไดนามิกที่เปลี่ยนวิธีการคำนวณข้อมูลใน Excel ของคุณอย่างสิ้นเชิง

ตารางจะมาแทนที่การอ้างอิงคอลัมน์ทั้งหมดขนาดใหญ่ด้วยขอบเขตที่ชัดเจน หากข้อมูลของคุณมี 50 แถว วัตถุตารางจะมีความยาว เท่ากับ 50 แถวพอดี ดังนั้นเมื่อสูตรอ้างอิงถึงตาราง เครื่องยนต์การคำนวณของ Excel จะจำกัดการทำงานให้อยู่ภายในตารางนั้นเท่านั้น

ก่อนอื่น ให้ตรวจสอบให้แน่ใจว่าชุดข้อมูลของคุณมีหัวข้อคอลัมน์ ต่อมา เลือกเซลล์ใดก็ได้ในช่วงนั้น กด Ctrl+T และในกล่องโต้ตอบสร้างตาราง ให้ตรวจสอบว่าได้เลือก "ตารางของฉันมีหัวข้อ" แล้ว จากนั้นคลิก "ตกลง"

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

=UNIQUE(Table1[Manager])

สังเกตว่าศูนย์ที่อยู่ด้านล่างของผลลัพธ์หายไป เพราะสูตรอ้างอิงเพียงข้อมูลที่อยู่ภายในขอบเขตของตารางเท่านั้น

หากต้องการเปลี่ยนชื่อของตารางของคุณ (จาก Table1 เป็นชื่ออย่างเช่น T_Managers) ให้เลือกเซลล์ใดก็ได้ในช่วงนั้น เปิดแท็บ "Table Design" แล้วพิมพ์ชื่อใหม่ในช่อง Table Name นอกจากประโยชน์อื่น ๆ อีกมากมาย วิธีนี้ยังทำให้สูตรง่ายต่อการอ่านมากขึ้นด้วย

วิธีนี้ดีกว่าการอ้างอิงทั้งคอลัมน์ด้วยสองเหตุผล:

การคำนวณแบบไม่สร้างขยะ: เพราะตารางมีจุดสิ้นสุดที่กำหนดไว้ Excel จึงไม่จำเป็นต้องประมวลผลพื้นที่ว่างด้านล่างของข้อมูล
ช่วงที่จัดการด้วยตนเอง: เมื่อคุณเพิ่มข้อมูลไปที่แถว 51 ขอบเขตของตารางจะขยายเพิ่มขึ้นหนึ่งแถว และสูตรของคุณจะยังคงครอบคลุมช่วงนั้นโดยไม่ต้องปรับด้วยตนเอง

วิธีแก้ไขที่ 2: ใช้ฟังก์ชัน TRIMRANGE

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

ฟังก์ชัน TRIMRANGE ใช้ได้เฉพาะผู้ที่ใช้ Excel สำหรับ Microsoft 365, Excel สำหรับเว็บ และแอป Excel บนมือถือและแท็บเล็ตเท่านั้น

TRIMRANGE มีอาร์กิวเมนต์สามตัว:

=TRIMRANGE(range,[trim_rows],[trim_cols])

ที่ไหน :

range (จำเป็น) คือช่วงที่ต้องการตัดแต่ง
trim_rows (ไม่บังคับ) ใช้บอก Excel ว่าจะตัดแถวว่างที่อยู่ด้านหน้าหรือไม่ (1), แถวว่างที่อยู่ด้านท้าย (2), หรือทั้งสองอย่าง (3)
trim_cols (ไม่บังคับ) ใช้บอก Excel ว่าจะตัดคอลัมน์ว่างด้านหน้า (1), ด้านหลัง (2), หรือทั้งสองด้าน (3)

ในการใช้ตรรกะนี้กับชุดข้อมูลของคุณ ให้พิมพ์:

=UNIQUE(TRIMRANGE(B:B,2))

ที่นี่ ตัวเลข "2" จะบังคับให้สูตรตัดเซลล์ว่างที่ต่อท้ายออก เนื่องจากในสูตรอ้างอิงเพียงคอลัมน์เดียว จึงไม่จำเป็นต้องใส่อาร์กิวเมนต์ trim_cols

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

นี่คือเหตุผลว่าทำไมวิธีนี้ถึงเป็นวิธีแก้ปัญหาที่ดีสำหรับการอ้างอิงทั้งคอลัมน์:

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

วิธีแก้ 3: ใช้ตัวดำเนินการอ้างอิง trim

ตัวดำเนินการ trim ref ของ Excel ให้ประโยชน์เหมือนกับฟังก์ชัน TRIMRANGE แต่พิมพ์น้อยกว่า

ตัวดำเนินการอ้างอิง trim มีความพร้อมใช้งานเดียวกับฟังก์ชัน TRIMRANGE: Excel สำหรับ Microsoft 365, Excel บนเว็บ และแอป Excel บนมือถือและแท็บเล็ต

โดยการเพิ่มจุด (.) ทางด้านใดด้านหนึ่งของเครื่องหมายทวิภาคในช่วงของคุณ คุณจะบอก Excel ให้ตัดเซลล์ว่างจากทิศทางนั้น:

ใส่จุดที่ไหน	ช่องว่างใดที่ถูกตัด
หลังเครื่องหมายทวิภาค	ช่องว่างที่อยู่ท้าย
ก่อนเครื่องหมายทวิภาค	ช่องว่างนำหน้า
ทั้งสองด้านของลำไส้ใหญ่	ช่องว่างต้นและท้าย

ดังนั้น ให้ใช้รายการเดิมเหมือนก่อนหน้านี้ พิมพ์ว่า:

=UNIQUE(B:.B)

ช่วงเวลาหลังเครื่องหมายทวิภาคบอก Excel ให้หยุดที่แถวสุดท้ายที่มีข้อมูล—ในกรณีนี้คือแถวที่ 50

นี่เป็นวิธีที่ดีเพราะว่า:

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

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