Thep Excel
TRIMRANGEฟังก์ชันตัดแถว/คอลัมน์ว่างออกจากขอบ
ฟังก์ชันใหม่ใน Excel 365 ที่ตัดแถวและคอลัมน์ว่างออกจากขอบของช่วงข้อมูล ช่วยให้การอ้างอิงช่วงกว้าง ๆ หรือทั้งคอลัมน์ปลอดภัยและไม่ดึงข้อมูลว่างเข้ามาปนกับสูตร dynamic array
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | คำอธิบาย |
|---|---|---|
| range | Range/Array | ช่วงเซลล์หรืออาร์เรย์ที่ต้องการตัดแถว/คอลัมน์ว่างออกจากขอบ รองรับการอ้างอิงทั้งคอลัมน์หรือทั้งแถว เช่น A:A |
| [trim_rows]ไม่บังคับ | Number (0-3) | กำหนดวิธีตัดแถวว่าง: 0 = ไม่ตัด, 1 = ตัดเฉพาะแถวว่างด้านบน (leading), 2 = ตัดเฉพาะแถวว่างด้านล่าง (trailing), 3 = ตัดทั้งบนและล่าง (ค่า default ถ้าไม่ใส่) |
| [trim_cols]ไม่บังคับ | Number (0-3) | กำหนดวิธีตัดคอลัมน์ว่าง: 0 = ไม่ตัด, 1 = ตัดเฉพาะคอลัมน์ว่างด้านซ้าย (leading), 2 = ตัดเฉพาะคอลัมน์ว่างด้านขวา (trailing), 3 = ตัดทั้งซ้ายและขวา (ค่า default ถ้าไม่ใส่) |
[ ] = อาร์กิวเมนต์ที่ไม่บังคับ
Additional Notes
TRIMRANGE(range, [trim_rows], [trim_cols]) จะสแกนจากขอบของช่วงเข้ามาด้านใน จนกว่าจะเจอเซลล์ที่ไม่ว่าง แล้วตัดแถวหรือคอลัมน์ว่างที่อยู่รอบนอกทิ้งไป โดยไม่ยุ่งกับเซลล์ว่างที่อยู่ตรงกลางของข้อมูล ส่วน trim_rows และ trim_cols เป็นตัวเลข 0-3 ที่บอกว่าจะตัดฝั่งไหน (0 = ไม่ตัด, 1 = ตัดเฉพาะขอบบน/ซ้าย, 2 = ตัดเฉพาะขอบล่าง/ขวา, 3 = ตัดทั้งสองฝั่ง ซึ่งเป็นค่า default)
ที่เจ๋งคือ Microsoft ทำ shorthand ให้ด้วย เรียกว่า trim-reference operator ใช้จุด (.) แทรกในการอ้างอิงช่วงได้เลยโดยไม่ต้องพิมพ์สูตร เช่น A1.:E10 (ตัดขอบบน/ซ้าย) A1:.E10 (ตัดขอบล่าง/ขวา) หรือ A1.:.E10 (ตัดทุกขอบ) ซึ่งเทียบเท่ากับ TRIMRANGE(A1:E10,1,1) TRIMRANGE(A1:E10,2,2) และ TRIMRANGE(A1:E10,3,3) ตามลำดับ และที่สำคัญมากคือใช้กับการอ้างอิงทั้งคอลัมน์แบบ A:A ได้ด้วย เช่น A.:.A จะได้เฉพาะแถวที่มีข้อมูลจริง ไม่ต้องกลัวสูตร dynamic array ล้นจอหรือคำนวณช้าเพราะแบกเซลล์ว่างเป็นล้านแถวอีกต่อไป
ส่วนตัวผมมองว่านี่คือฟังก์ชันที่แก้ pain point เก่าแก่ของ Excel เลย สมัยก่อนอ้างอิง A:A ทั้งคอลัมน์กับสูตร dynamic array มันเสี่ยงมาก ทั้งช้าทั้งดึงแถวว่างมาปนจน UNIQUE หรือ SORT เพี้ยน ตอนนี้แค่เติมจุดสองจุดก็จบ ผมเริ่มเปลี่ยนนิสัยมาใช้ .:. แทน A:A ในสูตรที่ต้องอ้างอิงทั้งคอลัมน์แล้ว 😎
Examples
ตัดทุกขอบด้วยค่า defaultTRIMRANGE(A1:E20)
ถ้าข้อมูลจริงอยู่แค่ B3:D15 แต่ในช่วง A1:E20 มีแถวและคอลัมน์ว่างล้อมรอบ TRIMRANGE จะตัดส่วนว่างนอกขอบทิ้งให้อัตโนมัติ เหลือแค่ B3:D15
ตัดเฉพาะขอบบนกับซ้าย (leading)TRIMRANGE(A1:E20,1,1)
เหมาะกับกรณีที่รู้ว่าข้อมูลจะขยายลงล่างหรือขวาเพิ่มเรื่อย ๆ อยากเก็บพื้นที่ขอบล่าง/ขวาไว้ไม่ให้สูตรพัง
อ้างอิงทั้งคอลัมน์ให้ปลอดภัยด้วย trim-reference operatorSORT(A.:.A)
จุดสองจุด .:. ที่แทรกในการอ้างอิง A:A คือ shorthand ของ TRIMRANGE ทำให้อ้างอิงทั้งคอลัมน์ได้โดยไม่ต้องกลัวประสิทธิภาพหรือแถวว่างกวนสูตร dynamic array
ทำลิสต์ไม่ซ้ำจากทั้งคอลัมน์โดยไม่มีช่องว่างต่อท้ายSORT(UNIQUE(B.:.B))
ถ้าเขียน =SORT(UNIQUE(B:B)) ตรง ๆ จะได้ค่าว่างติดมาด้วย เพราะ B:B ลากเซลล์ว่างทั้งคอลัมน์เข้ามาคำนวณ พอเปลี่ยนเป็น B.:.B ซึ่งเป็น shorthand ของ TRIMRANGE(B:B,3,3) ก็เหลือเฉพาะแถวที่มีข้อมูลจริง เหมาะมากกับการทำแหล่งข้อมูลให้ dropdown ที่ต้องยืดหดตามข้อมูลเอง
FAQs
TRIMRANGE ต่างจาก TRIM ยังไง+
คนละงานเลยครับ TRIM ใช้ตัดช่องว่าง (space) ภายในข้อความของเซลล์เดียว ส่วน TRIMRANGE ทำงานกับทั้งช่วง ตัดแถวหรือคอลัมน์ที่ว่างทั้งแถว/คอลัมน์ออกจากขอบนอกของช่วงนั้น ไม่เกี่ยวกับข้อความในเซลล์เลย
ใช้ trim-reference operator (.:. หรือ .: หรือ :.) แทน TRIMRANGE ได้เลยไหม ต่างกันตรงไหน+
ใช้แทนกันได้ครับ ผลลัพธ์เหมือนกันทุกประการ ต่างแค่วิธีเขียน operator แบบจุดจะกระชับกว่าเวลาพิมพ์สด ๆ ในเซลล์ ส่วน TRIMRANGE เหมาะกับตอนที่ต้องส่งช่วงเป็น argument ให้ฟังก์ชันอื่นต่อ หรืออยากให้สูตรอ่านง่ายเวลาคนอื่นมาดูทีหลัง ผมเลือกใช้ operator เวลาพิมพ์เร็ว ๆ และสลับมาเขียน TRIMRANGE เต็มรูปเวลาสูตรซับซ้อนหลายชั้น
ถ้าในช่วงข้อมูลมีแถวว่างอยู่ตรงกลาง TRIMRANGE จะตัดออกด้วยไหม+
ไม่ครับ TRIMRANGE สแกนจากขอบเข้ามาเท่านั้น เจอเซลล์ไม่ว่างเมื่อไหร่ก็หยุดตัดทันที แถวหรือคอลัมน์ว่างที่แทรกอยู่กลางบล็อกข้อมูลจะยังอยู่เหมือนเดิม ถ้าอยากเอาแถวว่างกลางตารางออกด้วยต้องไปใช้ FILTER ร่วมด้วย
ทำไม TRIMRANGE ถึงสำคัญกับสูตร dynamic array อย่าง UNIQUE, SORT, FILTER+
เพราะฟังก์ชันพวกนี้ประมวลผลทุกเซลล์ในช่วงที่อ้างอิง ถ้าอ้างอิงทั้งคอลัมน์แบบ A:A ตรง ๆ มันจะแบกเซลล์ว่างเป็นล้านแถวไปด้วย ทำให้ผลลัพธ์มีค่าว่างปนหรือช้าเวลาไฟล์ใหญ่ ผมเจอปัญหานี้บ่อยตอนทำ dropdown list ที่ต้องอัปเดตอัตโนมัติ พอมี TRIMRANGE หรือ .:. ก็จบปัญหานี้ไปเลย
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ TRIMRANGE จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
FILTER กรองข้อมูลจาก Array ตามเงื่อนไขที่กำหนด แล้ว return เป็น Spill Range ที่ขยายอัตโนมัติ เป็น Dynamic Array Function ที่เปลี่ยนวิธีการทำงานกับข้อมูลใน Excel รองรับเงื่อนไขหลายตัว (AND/OR) และสามารถใช้ร่วมกับ SORT UNIQUE XLOOKUP เพื่อสร้างรายงานที่อัปเดตอัตโนมัติ
อ้างอิงช่วงข้อมูลที่เลื่อนจากตำแหน่งเริ่มต้น สร้าง dynamic range ได้
SORT เป็น Dynamic Array Function ที่เรียงลำดับข้อมูลจาก Array แล้ว return เป็น Spill Range ใหม่โดยไม่แก้ไขข้อมูลต้นฉบับ รองรับการเรียงตามคอลัมน์ที่ต้องการ (sort_index) ทั้งจากน้อยไปมาก (1) และมากไปน้อย (-1) รวมถึงเรียงแนวนอน (by_col=TRUE) ต่างจาก SORTBY ที่ใช้คอลัมน์ภายนอกเป็นเกณฑ์
TOCOL ช่วย "ตบ" ข้อมูลจากตารางหลายมิติให้มาเรียงต่อกันเป็นคอลัมน์เดียว สามารถเลือกวิธีเรียงลำดับได้ว่าจะอ่านจากซ้ายไปขวา (ทีละแถว) หรือบนลงล่าง (ทีละคอลัมน์) และยังมี Option ให้กรองช่องว่างหรือ Error ทิ้งไปโดยอัตโนมัติ เหมาะสำหรับการเตรียมข้อมูล (Data Preparation)
UNIQUE เป็น Dynamic Array Function ที่คืนค่าที่ไม่ซ้ำจาก Array โดยสามารถตรวจซ้ำตามแถวหรือคอลัมน์ (by_col) และเลือกคืนเฉพาะค่าที่พบครั้งเดียว (exactly_once) ผลลัพธ์เป็น Spill Range ที่อัปเดตอัตโนมัติ ใช้ร่วมกับ SORT FILTER COUNTIF เพื่อสร้างรายงานไดนามิกและ dropdown ที่อัปเดตเอง
ยังไม่มีบทความที่เกี่ยวข้องกับฟังก์ชันนี้
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่