Thep Excel
TOCOLแปลงตาราง 2 มิติให้เป็นคอลัมน์เดียว
TOCOL ช่วย “ตบ” ข้อมูลจากตารางหลายมิติให้มาเรียงต่อกันเป็นคอลัมน์เดียว สามารถเลือกวิธีเรียงลำดับได้ว่าจะอ่านจากซ้ายไปขวา (ทีละแถว) หรือบนลงล่าง (ทีละคอลัมน์) และยังมี Option ให้กรองช่องว่างหรือ Error ทิ้งไปโดยอัตโนมัติ เหมาะสำหรับการเตรียมข้อมูล (Data Preparation)
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | ค่าเริ่มต้น | คำอธิบาย |
|---|---|---|---|
| array | Range/Array | ตารางหรือช่วงข้อมูลที่ต้องการแปลง | |
| [ignore]ไม่บังคับ | Number | 0 | ค่าที่จะให้ข้าม (Ignore): 0=เก็บหมด (default), 1=ข้ามช่องว่าง, 2=ข้าม Error, 3=ข้ามทั้งคู่ |
| [scan_by_column]ไม่บังคับ | Boolean | FALSE | วิธีอ่านข้อมูล: FALSE=อ่านทีละแถว (ซ้ายไปขวา), TRUE=อ่านทีละคอลัมน์ (บนลงล่าง) |
[ ] = อาร์กิวเมนต์ที่ไม่บังคับ
ฟังก์ชัน TOCOL ใน Excel ใช้สำหรับแปลงช่วงข้อมูล (Array) หรือตารางที่มีหลายแถวหลายคอลัมน์ ให้กลายเป็นรายการเดียวในแนวตั้ง (Single Column) พร้อมความสามารถในการข้ามช่องว่างและ Error ได้
How it works
เตรียมข้อมูลสำหรับ PivotTable
แปลงตารางรายงานแบบ Cross-tab (ที่มีหัวตารางเป็นเดือนแนวนอน) ให้เป็น Database แนวตั้ง (Unpivot) อย่างง่าย เพื่อนำไปทำ PivotTable ต่อ
ทำความสะอาดข้อมูล (Clean Data)
ใช้ TOCOL(range, 3) เพื่อดึงเฉพาะข้อมูลที่ดีออกมา (ตัดทั้ง Error และ Blank) จากตารางที่สกปรกหรือมีสูตร Error ปนอยู่
Examples
รวมข้อมูลเป็นคอลัมน์เดียว (แบบปกติ)TOCOL(A2:C4)
นำข้อมูลจากช่วง A2:C4 มาเรียงต่อกัน โดยเริ่มจากแถวแรก (A2, B2, C2) แล้วต่อด้วยแถวที่สอง (A3, B3, C3) ไปเรื่อยๆ จนครบ
รวมข้อมูลแบบอ่านทีละคอลัมน์TOCOL(A2:C4, 0, TRUE)
ตั้งค่า scan_by_column เป็น TRUE: จะอ่านข้อมูลจากคอลัมน์ A จนหมด (A2, A3, A4) แล้วค่อยไปต่อที่คอลัมน์ B และ C
รวมและกำจัดช่องว่างTOCOL(DataRange, 1)
แปลง DataRange เป็นคอลัมน์เดียว โดยตั้งค่า ignore = 1 เพื่อข้ามเซลล์ว่างทั้งหมด ทำให้ได้รายการที่กระชับไม่มีช่องว่างแทรก
สร้างรายชื่อที่ไม่ซ้ำจากหลายคอลัมน์UNIQUE(TOCOL(NameList, 1))
สมมติ NameList เป็นตารางรายชื่อหลายคอลัมน์ ใช้ TOCOL รวมให้เหลือคอลัมน์เดียวและตัดช่องว่างออก (1) จากนั้นใช้ UNIQUE ตัดชื่อซ้ำออกอีกที
รวมยอดขายจากหลายเดือนก่อนหาผลรวมSUM(TOCOL(SalesJanToDec, 3))
ตาราง SalesJanToDec มี 12 คอลัมน์ (แต่ละเดือน) บางช่องว่างเพราะยังไม่มีข้อมูล บางช่อง #N/A เพราะสูตรอ้างอิงผิด ใช้ ignore=3 ยุบทุกอย่างเป็นคอลัมน์เดียวและตัดทั้งช่องว่างและ error ก่อนส่งให้ SUM คำนวณ
FAQs
ต่างจาก TRANSPOSE อย่างไร?+
TRANSPOSE แค่กลับแกน (แถวเป็นคอลัมน์) แต่รักษาโครงสร้างตาราง 2 มิติไว้ ส่วน TOCOL จะ "ยุบ" ทุกอย่างให้เหลือ 1 มิติ (คอลัมน์เดียว) เสมอ
ถ้าต้องการแปลงเป็นแนวนอน (แถวเดียว) ต้องใช้อะไร?+
ใช้ฟังก์ชัน TOROW ซึ่งเป็นคู่หูของ TOCOL โดยจะเรียงข้อมูลออกไปทางขวาเป็นแถวเดียวแทน
ลำดับการอ่านข้อมูลสำคัญอย่างไร?+
สำคัญมากเมื่อข้อมูลมีความหมายตามลำดับ ถ้าข้อมูลเรียงตามเวลาในแนวนอน (เช่น ม.ค., ก.พ., มี.ค.) ควรใช้แบบปกติ (FALSE) แต่ถ้าข้อมูลเรียงลงล่างเป็นกลุ่มๆ ควรใช้แบบ scan_by_column (TRUE)
ใส่ ignore=4 หรือค่าอื่นนอกจาก 0-3 ได้ไหม?+
ไม่ได้ ignore รับได้แค่ 0 (เก็บทุกอย่าง), 1 (ข้ามช่องว่าง), 2 (ข้าม error) หรือ 3 (ข้ามทั้งคู่) เท่านั้น ใส่ตัวเลขอื่นจะได้ #VALUE!
ทำไมใช้ TOCOL แล้วยัง #SPILL! อยู่?+
เพราะพื้นที่ด้านล่างเซลล์สูตรมีข้อมูลขวางอยู่ TOCOL เป็นฟังก์ชัน Dynamic Array ต้องมีที่ว่างพอสำหรับผลลัพธ์ทั้งหมดไหลลงไปได้โดยไม่ชนของเดิม
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ TOCOL จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
HSTACK เป็นฟังก์ชัน Dynamic Array ที่ใช้รวมข้อมูลจากหลายช่วงเข้าด้วยกันโดยนำมาเรียงต่อกันในแนวนอน (ต่อท้ายไปทางขวา) หากช่วงข้อมูลที่นำมารวมมีจำนวนแถวไม่เท่ากัน HSTACK จะเติมค่า #N/A ในส่วนที่ขาดหายไปให้โดยอัตโนมัติ
ฟังก์ชัน RTD ใช้สำหรับดึงข้อมูลแบบ Real-time จากโปรแกรมที่รองรับ COM automation เช่น ราคาหุ้น อัตราแลกเปลี่ยน หรือข้อมูลที่มีการอัพเดทอย่างต่อเนื่อง
.
ที่เจ๋งคือ RTD จะอัพเดทข้อมูลโดยอัตโนมัติเมื่อ Excel อยู่ในโหมดคำนวณอัตโนมัติ ไม่ต้องนั่งกด F9 หรือรีเฟรชด้วยตนเอง ซึ่งเหมาะมากสำหรับการติดตามข้อมูลตลาดการเงิน การวิเคราะห์หุ้น หรือการเชื่อมต่อกับ Data Server ภายนอก
SORT เป็น Dynamic Array Function ที่เรียงลำดับข้อมูลจาก Array แล้ว return เป็น Spill Range ใหม่โดยไม่แก้ไขข้อมูลต้นฉบับ รองรับการเรียงตามคอลัมน์ที่ต้องการ (sort_index) ทั้งจากน้อยไปมาก (1) และมากไปน้อย (-1) รวมถึงเรียงแนวนอน (by_col=TRUE) ต่างจาก SORTBY ที่ใช้คอลัมน์ภายนอกเป็นเกณฑ์
TOROW ช่วยแปลงข้อมูลจากตารางหลายมิติให้มาเรียงต่อกันเป็นแถวเดียว (แนวนอน) สามารถเลือกวิธีเรียงลำดับได้ว่าจะอ่านจากซ้ายไปขวา (ทีละแถว) หรือบนลงล่าง (ทีละคอลัมน์) และเลือกข้ามช่องว่างหรือ Error ได้เหมือน TOCOL
สลับแกนของตารางข้อมูล เปลี่ยนแถวเป็นคอลัมน์และคอลัมน์เป็นแถว ช่วยจัดโครงสร้างข้อมูลให้เหมาะกับการวิเคราะห์และนำเสนอ
ฟังก์ชันใหม่ใน Excel 365 ที่ตัดแถวและคอลัมน์ว่างออกจากขอบของช่วงข้อมูล ช่วยให้การอ้างอิงช่วงกว้าง ๆ หรือทั้งคอลัมน์ปลอดภัยและไม่ดึงข้อมูลว่างเข้ามาปนกับสูตร dynamic array
UNIQUE เป็น Dynamic Array Function ที่คืนค่าที่ไม่ซ้ำจาก Array โดยสามารถตรวจซ้ำตามแถวหรือคอลัมน์ (by_col) และเลือกคืนเฉพาะค่าที่พบครั้งเดียว (exactly_once) ผลลัพธ์เป็น Spill Range ที่อัปเดตอัตโนมัติ ใช้ร่วมกับ SORT FILTER COUNTIF เพื่อสร้างรายงานไดนามิกและ dropdown ที่อัปเดตเอง
VSTACK เป็นฟังก์ชัน Dynamic Array ที่ใช้รวมข้อมูลจากหลายช่วงเข้าด้วยกันโดยนำมาเรียงต่อกันในแนวตั้ง (ต่อท้ายลงไปด้านล่าง) หากช่วงข้อมูลที่นำมารวมมีจำนวนคอลัมน์ไม่เท่ากัน VSTACK จะเติมค่า #N/A ในส่วนที่ขาดหายไปให้โดยอัตโนมัติ
WRAPCOLS ห่อ (wrap) ข้อมูล 1 มิติให้กลายเป็นตาราง 2 มิติ โดยเรียงข้อมูลจากบนลงล่างในแต่ละคอลัมน์ เมื่อครบ wrap_count แถวจะขึ้นคอลัมน์ใหม่ รองรับ padding เมื่อข้อมูลไม่พอดี ใช้คู่กับ WRAPROWS TOCOL TOROW เพื่อ reshape ข้อมูล
WRAPROWS ห่อ (wrap) ข้อมูล 1 มิติให้กลายเป็นตาราง 2 มิติ โดยเรียงข้อมูลจากซ้ายไปขวาในแต่ละแถว เมื่อครบ wrap_count คอลัมน์จะขึ้นแถวใหม่ รองรับ padding เมื่อข้อมูลไม่พอดี ใช้คู่กับ WRAPCOLS TOCOL TOROW เพื่อ reshape ข้อมูล
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่