Thep Excel
EXPANDขยายขนาดตารางพร้อมเติมค่า (Padding)
EXPAND ใช้ขยายขนาดของตารางข้อมูลให้ใหญ่ขึ้นตามจำนวนแถวหรือคอลัมน์ที่ระบุ หากตารางเดิมมีขนาดเล็กกว่า ส่วนที่เพิ่มขึ้นมาจะแสดงค่าเป็น #N/A (ค่าเริ่มต้น) หรือค่าที่เรากำหนดเองได้ (pad_with) มีประโยชน์มากในการปรับขนาดข้อมูลให้เท่ากันก่อนนำไปรวมด้วย VSTACK หรือ HSTACK
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | ค่าเริ่มต้น | คำอธิบาย |
|---|---|---|---|
| array | Range/Array | ตารางหรือช่วงข้อมูลต้นฉบับที่ต้องการขยายขนาด | |
| rows | Number | จำนวนแถวทั้งหมดที่ต้องการ (ต้องมากกว่าหรือเท่ากับจำนวนแถวเดิม) | |
| [columns]ไม่บังคับ | Number | Same as array | จำนวนคอลัมน์ทั้งหมดที่ต้องการ (ต้องมากกว่าหรือเท่ากับจำนวนคอลัมน์เดิม) |
| [pad_with]ไม่บังคับ | Any | #N/A | ค่าที่จะเติมลงในช่องว่างที่เกิดขึ้นใหม่ (ถ้าไม่ระบุจะเป็น #N/A) |
[ ] = อาร์กิวเมนต์ที่ไม่บังคับ
ฟังก์ชัน EXPAND ใน Excel ช่วยขยายขนาดของตารางหรืออาร์เรย์ให้ได้จำนวนแถวและคอลัมน์ตามที่ต้องการ โดยส่วนที่ขยายเพิ่มขึ้นมาจะถูกเติมด้วยค่าที่ระบุ (Pad with) เช่น 0, ขีด (-), หรือข้อความว่าง
How it works
เตรียม Template สำหรับกรอกข้อมูล
สร้างตารางเปล่าขนาดมาตรฐาน (เช่น 100 แถว) รอรับข้อมูล โดยให้แสดงขีด (-) หรือข้อความ "รอข้อมูล" ในบรรทัดที่ยังไม่มีข้อมูลจริง
แก้ปัญหา HSTACK/VSTACK ข้อมูลไม่เท่ากัน
เมื่อต้องรวมตารางที่มีขนาดไม่เท่ากัน EXPAND ช่วยเติมเต็มส่วนที่ขาดให้เท่ากันก่อนรวม เพื่อป้องกัน Error #N/A ที่ควบคุมไม่ได้
Examples
| A | B | C | |
|---|---|---|---|
| 1 | คอลัมน์ 1 | คอลัมน์ 2 | คอลัมน์ 3 |
| 2 | 1 | 2 | 3 |
| 3 | 4 | 5 | 6 |
| 4 | 7 | 8 | 9 |
ตัวเลขเรียงกันเพื่อให้เห็นชัดว่าผลลัพธ์ถูกจัดเรียงใหม่อย่างไร ทุกสูตรด้านล่างอ้างถึงตารางนี้
ขยายตารางให้ครบ 5 แถว (เติมขีด)EXPAND(F2:H4, 5, , "-")
F2:H4 คือตารางตัวเลข 3 แถว 3 คอลัมน์ (1-9) สูตรนี้ขยายให้เป็น 5 แถว โดยคงจำนวนคอลัมน์เท่าเดิมเพราะไม่ได้ระบุอาร์กิวเมนต์ columns
3 แถวแรกยังเป็นตัวเลขเดิม (1,2,3 / 4,5,6 / 7,8,9) ส่วน 2 แถวใหม่ที่เพิ่มมาจะถูกเติมด้วยเครื่องหมาย "-" ทั้งแถว
วิธีนี้ช่วยให้พื้นที่ผลลัพธ์คงที่ 5 แถวเสมอ ไม่ว่าตารางต้นฉบับจะมีกี่แถวจริงก็ตาม
ขยายตารางเป็น 5×5 (เติมเลข 0)EXPAND(F2:H4, 5, 5, 0)
คราวนี้ขยายทั้งสองมิติพร้อมกัน rows=5 columns=5 จากตารางเดิมที่มีแค่ 3×3 ส่วนที่ขยายเพิ่มมาทั้งหมดถูกเติมด้วยเลข 0
3 แถวแรกจะมี 3 คอลัมน์แรกเป็นตัวเลขเดิม (1-9) แล้วตามด้วย 0 อีก 2 ช่องในแต่ละแถว ส่วน 2 แถวสุดท้ายเป็น 0 ล้วนทั้งแถว
เหมาะสำหรับเตรียมข้อมูลก่อนเอาไปคำนวณต่อ เพราะเลข 0 ไม่ทำให้ผลรวมหรือผลคูณผิดเพี้ยนเหมือนใช้ #N/A หรือข้อความ
ขยายเฉพาะคอลัมน์ (เติมข้อความ)EXPAND(F2:H4, 3, 5, "ว่าง")
ตัวอย่างนี้ขยายแค่แนวคอลัมน์ rows ใส่เท่าจำนวนเดิม (3) เพื่อไม่ให้แถวเปลี่ยน แต่ columns ขยายจาก 3 เป็น 5
แต่ละแถวเดิมจะมี 2 ช่องใหม่ต่อท้ายทางขวา เติมด้วยคำว่า "ว่าง" ตามที่ระบุใน pad_with
ข้อความอย่าง "ว่าง" อ่านง่ายกว่า #N/A หรือ 0 เปล่าๆ เวลาต้องการสื่อสารกับคนอ่านไฟล์ว่าช่องนี้ยังไม่มีข้อมูลจริง
ปรับความสูงให้เท่ากันก่อนต่อด้วย HSTACKHSTACK(F2:H4, EXPAND({100;200}, ROWS(F2:H4), , "-"))
{100;200} คือชุดตัวเลข 2 แถวที่พิมพ์ขึ้นมาเองในสูตร ไม่ได้มาจากตาราง แต่ F2:H4 มี 3 แถว ถ้า HSTACK ตรงๆ จำนวนแถวจะไม่เท่ากันแล้วเกิด error
สูตรนี้ใช้ EXPAND ขยาย {100;200} ให้สูงเท่ากับ F2:H4 (ROWS(F2:H4) เท่ากับ 3) ก่อน โดยแถวที่ขาดไปเติมด้วยเครื่องหมาย "-"
พอความสูงเท่ากันแล้ว HSTACK ก็ต่อสองก้อนเข้าด้วยกันตามแนวนอนได้สำเร็จ กลายเป็นตาราง 3 แถว 4 คอลัมน์
จองพื้นที่ผลลัพธ์ FILTER ไว้ล่วงหน้ากัน #SPILL!EXPAND(FILTER(F2:F4, F2:F4>4), 5, , "-")
FILTER(F2:F4, F2:F4>4) กรองเอาเฉพาะตัวเลขในคอลัมน์ F ที่มากกว่า 4 ออกมา คอลัมน์นี้มีแค่ 1, 4, 7 เพราะฉะนั้นตรงเงื่อนไขแค่เลข 7 ตัวเดียว ซึ่งจำนวนแถวจะเปลี่ยนไปเรื่อยๆ ตามข้อมูลต้นทาง
ถ้าใต้สูตรมีเซลล์อื่นวางอยู่ พอผลลัพธ์กรองได้มากขึ้นในอนาคตจะไปชนของอื่นแล้วเกิด error #SPILL! ทันที การห่อด้วย EXPAND บังคับให้พื้นที่คงที่ 5 แถวเสมอ
แถวแรกเป็นผลลัพธ์จริงจาก FILTER ส่วนอีก 4 แถวที่เหลือเติมด้วยเครื่องหมาย "-" ทำให้จัดวางเลย์เอาต์รายงานได้แน่นอนโดยไม่ต้องกังวลว่าพื้นที่จะขยายชนแถวถัดไป
FAQs
ถ้ากำหนด rows/columns น้อยกว่าขนาดเดิม จะเกิดอะไรขึ้น?+
จะเกิด Error #VALUE! ครับ EXPAND ใช้สำหรับ "ขยาย" เท่านั้น ถ้าต้องการลดขนาดหรือตัดข้อมูลออก ต้องใช้ฟังก์ชัน TAKE หรือ DROP แทน
ทำไมค่า Default ถึงเป็น #N/A?+
เพื่อเตือนให้รู้ว่านี่คือข้อมูลที่ไม่มีอยู่จริง (Not Available) ถ้าต้องการให้เป็นค่าอื่น ต้องระบุในอาร์กิวเมนต์ pad_with เสมอ
ใช้กับ Dynamic Array ได้ไหม?+
ได้ดีเยี่ยมครับ EXPAND ออกแบบมาเพื่อจัดการ Dynamic Array โดยเฉพาะ เช่น ห่อผลลัพธ์จาก FILTER หรือ SORT ที่มีขนาดไม่แน่นอนให้กลายเป็นขนาดคงที่ ผลลัพธ์ของ EXPAND เองก็ spill ออกมาเป็นอาเรย์เช่นกัน เอาไปต่อกับฟังก์ชันอาเรย์อื่นได้ทันที
pad_with ใส่เป็นช่วงข้อมูลหรืออาเรย์แทนค่าเดียวได้ไหม?+
ไม่ได้ครับ pad_with รับได้แค่ค่าเดี่ยว (scalar) เดียวเท่านั้น เช่น 0, "-", หรือ "" จะเอาช่วงเซลล์หรืออาเรย์มาใส่เป็น pad_with ไม่ได้ ถ้าต้องการเติมค่าที่ต่างกันไปในแต่ละช่อง ต้องใช้เทคนิคอื่น เช่น IF ร่วมกับ dynamic array แทน
EXPAND ต่างจาก VSTACK/HSTACK อย่างไร ใช้แทนกันได้ไหม?+
ต่างกันคนละหน้าที่ VSTACK/HSTACK ใช้ต่อสองอาเรย์ที่มีอยู่จริงเข้าด้วยกัน (ตามแนวตั้ง/แนวนอน) ส่วน EXPAND ขยายอาเรย์เดียวให้ถึงขนาดที่กำหนดโดยเติมค่าว่างเข้าไป ไม่ใช่การนำสองก้อนข้อมูลมาต่อกัน สองฟังก์ชันนี้มักใช้ร่วมกัน (อย่างตัวอย่างที่ 4) มากกว่าจะใช้แทนกัน
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ EXPAND จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
CHOOSECOLS ใช้ดึงคอลัมน์ที่ต้องการจากตารางหรือ Array โดยระบุเลขลำดับคอลัมน์ สามารถดึงได้หลายคอลัมน์พร้อมกัน จัดลำดับใหม่ หรือทำซ้ำคอลัมน์เดิมได้ รองรับการนับคอลัมน์จากขวาสุดโดยใช้เลขลบ (เช่น -1 คือคอลัมน์ขวาสุด)
CHOOSEROWS ใช้ดึงแถวที่ต้องการจากตารางหรือ Array โดยระบุเลขลำดับแถว สามารถดึงได้หลายแถวพร้อมกัน จัดลำดับใหม่ หรือทำซ้ำแถวเดิมได้ รองรับการนับแถวจากล่างขึ้นบนโดยใช้เลขลบ (เช่น -1 คือแถวสุดท้าย)
DROP จะตัดข้อมูลออกตามจำนวนที่ระบุ ถ้าใส่เลขบวกจะตัดจากจุดเริ่มต้น (บน/ซ้าย) ทิ้งไป ถ้าใส่เลขลบจะตัดจากจุดสิ้นสุด (ล่าง/ขวา) ทิ้งไป ส่วนที่เหลือจะถูกนำมาแสดงผล
FILTER กรองข้อมูลจาก Array ตามเงื่อนไขที่กำหนด แล้ว return เป็น Spill Range ที่ขยายอัตโนมัติ เป็น Dynamic Array Function ที่เปลี่ยนวิธีการทำงานกับข้อมูลใน Excel รองรับเงื่อนไขหลายตัว (AND/OR) และสามารถใช้ร่วมกับ SORT UNIQUE XLOOKUP เพื่อสร้างรายงานที่อัปเดตอัตโนมัติ
HSTACK เป็นฟังก์ชัน Dynamic Array ที่ใช้รวมข้อมูลจากหลายช่วงเข้าด้วยกันโดยนำมาเรียงต่อกันในแนวนอน (ต่อท้ายไปทางขวา) หากช่วงข้อมูลที่นำมารวมมีจำนวนแถวไม่เท่ากัน HSTACK จะเติมค่า #N/A ในส่วนที่ขาดหายไปให้โดยอัตโนมัติ
TAKE ช่วยตัดข้อมูลบางส่วนออกมาใช้งาน โดยระบุจำนวนที่ต้องการ ถ้าใส่เลขบวกจะดึงจากจุดเริ่มต้น (บน/ซ้าย) ถ้าใส่เลขลบจะดึงจากจุดสิ้นสุด (ล่าง/ขวา) คล้ายกับคำสั่ง LIMIT หรือ TOP/BOTTOM ใน Database
สลับแกนของตารางข้อมูล เปลี่ยนแถวเป็นคอลัมน์และคอลัมน์เป็นแถว ช่วยจัดโครงสร้างข้อมูลให้เหมาะกับการวิเคราะห์และนำเสนอ
VSTACK เป็นฟังก์ชัน Dynamic Array ที่ใช้รวมข้อมูลจากหลายช่วงเข้าด้วยกันโดยนำมาเรียงต่อกันในแนวตั้ง (ต่อท้ายลงไปด้านล่าง) หากช่วงข้อมูลที่นำมารวมมีจำนวนคอลัมน์ไม่เท่ากัน VSTACK จะเติมค่า #N/A ในส่วนที่ขาดหายไปให้โดยอัตโนมัติ
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่