การใช้ HSTACK และ VSTACK ใน Excel

ภาพรวม

ทั้ง HSTACK และ VSTACK ใช้รวมอาเรย์ (array) หรือรายการของข้อมูล โดยที่ VSTACK จะนำอาเรย์เหล่านั้นมาเรียงต่อกันในแนวตั้งจากบนลงล่าง ส่วน HSTACK เรียงต่อกันในแนวนอนจากซ้ายไปขวา

HSTACK()

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

อย่างเช่น ถ้าเรามีข้อมูลยอดขายแบ่งตามหมวดหมู่สินค้าดังภาพด้านล่างนี้

ทีนี้เราอยากจะนำมาทำเป็นตาราง เพื่อที่จะดูว่าแต่ละหมวดหมู่สินค้าเหล่านั้นแต่ละเดือนขายได้เท่าไหร่ โดยสมมติว่าเราจะใส่หัวตารางใน F1 และเดือนก็เรียงไปตาม G1, H1 ไปเรื่อย ๆ โดยอันนี้เราใส่ข้อมูลเอง

ส่วนใน F2 จะนำข้อมูลยอดขายของแต่ละหมวดของแต่ละเดือนมาใส่ (โดยข้อมูลตัวอย่างเราเรียงมาแล้วนะ) สูตรจะเป็นดังนี้

=HSTACK(C2:C21,D2:D21,D22:D41,D42:D61)

ซึ่งก็คือเราจะเอาชื่อหมวดหมู่มาจากคอลัมภ์ C และยอดขายของแต่ละเดือนมาจากคอลัมภ์ D ซึ่งในตัวอย่างเราทำมาแค่ 3 เดือน เพื่อให้เห็นภาพกาใช้งานได้ชัดหน่อย เพราะถ้าเอามาหมดมันจะดูยาว และเหมือนจะยากไปซะเปล่า 😁

รูปแบบการใช้งาน HSTACK

จากตัวอย่างที่ผ่านมาก็คือ ถ้าเราอยากได้ข้อมูลตรงไหนมาวางต่อ ๆ จากซ้ายไปขวา ก็คือไปเลือกช่วงข้อมูล (range) มาแล้วคั่นด้วยเครื่องหมายคอมม่า (comma) แค่นั้น

ใช่ครับ มันใช้ง่าย ๆ แบบนี้ล่ะ โดย syntax ของ HSTACK คือ

=HSTACK(array1,[array2],[array3], ... )

โดย array1, array2, และ array3 ก็คือ Range ข้อมูลที่เราต้องการนำมาวางต่อกันไปในแนวนอน หรือจะเรียกว่าเป็นการเพิ่มคอลัมภ์ก็ได้

สิ่งที่ควรทราบ

array ใหม่ที่ได้จะมี row เท่ากับจำนวน row ที่มากที่สุด เช่นเดียวกันจำนวนคอลัมภ์ที่ได้ก็จะเท่ากับจำนวนคอลัมภ์ที่มากที่สุด โดยตรงไหนที่ไม่มีข้อมูล HSTACK จะแสดงผลเป็น #N/A

ถ้าไม่อยากให้แสดงผลเป็น #N/A ให้ใช้ร่วมกับฟังก์ชัน IFNA() หรือ IFERROR() ก็ได้ อย่างเช่น

=IFNA(HSTACK(C2:C21,D2:D21,D22:D40,D42:D60),"-")

หากใช้สุตรด้านบนนี้ ส่วนที่เป็น #N/A จะถูกเปลี่ยนเป็น -

สูตรแบบครบ 12 เดือน

ในตัวอย่างด้านบนเราทำแค่ 3 เดือน และสำหรับข้อมูลเต็มไป 2023 สูตรที่เราใช้ใน F2 จะเป็น

=IFNA(HSTACK(C2:C21,D2:D21,D22:D41,D42:D61,D62:D81,D82:D101,D102:D121,D122:D141,D142:D161,D162:D181,D182:D201,D202:D221,D222:D241),"-")

ซึ่งอาจดู งงงง นิดหนึ่ง ดูไม่รู้ว่าเราเอาอะไรมาเรียงต่อกัน และถ้าไม่อยากให้สูตรเป็นแบบนี้ แนะนำให้ทำ Format as Table ในส่วนข้อมูลดิบไว้นะครับ เพราะจะทำให้สูตรเราดูเข้าใจง่ายขึ้นเยอะ

โดยในกรณีนี้ เราสามารถใช้ฟังก์ชัน FILTER() ในการเลือกข้อมูลที่ตรงกับเงื่อนไขที่เราต้องการ แต่สูตรที่ได้จะดูยาวหน่อย แต่อ่านเข้าใจง่ายกว่า (หรือเปล่านะ) 😊

=IFERROR(HSTACK(
 UNIQUE(tblPetShopSales[Category]),
 FILTER(tblPetShopSales[Sales],(tblPetShopSales[Year]=$G$1)*(tblPetShopSales[Month]=G$2),""),
 FILTER(tblPetShopSales[Sales],(tblPetShopSales[Year]=$G$1)*(tblPetShopSales[Month]=H$2),""),
 FILTER(tblPetShopSales[Sales],(tblPetShopSales[Year]=$G$1)*(tblPetShopSales[Month]=I$2),""),
 FILTER(tblPetShopSales[Sales],(tblPetShopSales[Year]=$G$1)*(tblPetShopSales[Month]=J$2),""),
 FILTER(tblPetShopSales[Sales],(tblPetShopSales[Year]=$G$1)*(tblPetShopSales[Month]=K$2),""),
 FILTER(tblPetShopSales[Sales],(tblPetShopSales[Year]=$G$1)*(tblPetShopSales[Month]=L$2),""),
 FILTER(tblPetShopSales[Sales],(tblPetShopSales[Year]=$G$1)*(tblPetShopSales[Month]=M$2),""),
 FILTER(tblPetShopSales[Sales],(tblPetShopSales[Year]=$G$1)*(tblPetShopSales[Month]=N$2),""),
 FILTER(tblPetShopSales[Sales],(tblPetShopSales[Year]=$G$1)*(tblPetShopSales[Month]=O$2),""),
 FILTER(tblPetShopSales[Sales],(tblPetShopSales[Year]=$G$1)*(tblPetShopSales[Month]=P$2),""),
 FILTER(tblPetShopSales[Sales],(tblPetShopSales[Year]=$G$1)*(tblPetShopSales[Month]=Q$2),""),
 FILTER(tblPetShopSales[Sales],(tblPetShopSales[Year]=$G$1)*(tblPetShopSales[Month]=R$2),"")
),"-")

โดยใน G1 เป็นปี ค.ศ. และคอลัมภ์ G2 ถึง R2 จะเป็นชื่อเดือน January จนถึง December

อ่านวิธีการเพิ่มเติมได้ ตามลิงค์นี้

VSTACK()

สำหรับ VSTACK มักใช้สำหรับรวมข้อมูลแบบเดียวกันจาก 2 แหล่งมารวมไว้ในคอลัมภ์เดียวกัน อย่างในภาพตัวอย่าง เรามีข้อมูลพนักงานอยู่ในตาราง 3 ตาราง แยกตามแผนก แต่เราอยากเอามารวมกัน เป็นต้น

โดยในตัวอย่างสูตรของเราที่เซลล์ A1 จะเป็นดังนี้

=VSTACK(tblSupplyChainManagement[#Headers],tblSupplyChainManagement,tblEngineeringMaintenance,tblMarketingSales)

ซึ่งอันนี้ก็จะเป็นข้อดีของการทำ Format as Table แหละ เพราะถ้าเราใช้การระบุ range สูตรก็จะเป็น

=VSTACK(HSTACK(I1:O1),I2:O4,Q2:W7,Y2:AE10)

ถึงสูตรจะดูสั้นกว่า แต่ไม่สื่อความหมายว่าข้อมูลคืออะไรสักเท่าไหร่ อีกอย่างถ้าหากมีการเพิ่มข้อมูลเข้ามาก็ต้องคอยกลับมาแก้ไขสูตรอีก แต่ถ้าทำ Format as Table ข้อมูลในสูตรก็จะขยายเพิ่มมาให้โดยอัตโนมัติ

จากสูตรข้างบน ส่วนหัวตารางเราจะมีการใช้ HSTACK มาช่วยด้วย

รูปแบบการใช้งาน VSTACK

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

=VSTACK(array1,[array2],[array3], ... )

โดย array1, array2, และ array3 ก็คือ range ข้อมูลที่เราต้องการนำมาวางซ้อนกันเป็นหลายแถวขึ้น

💡และก็เช่นเดียวกับ HSTACK ฟังก์ชัน VSTACK จะได้ array ใหม่ที่ขนาดเท่ากับคอลัมภ์และแถวที่มากที่สุดที่ระบุไว้ โดยหากไม่มีข้อมูลจะให้ #N/A มา

ถ้าไม่อยากให้แสดงผลเป็น #N/A ให้ใช้ร่วมกับฟังก์ชัน IFNA() หรือ IFERROR() เช่นกัน

ข้อสังเกตสำหรับการใช้ HSTACK และ VSTACK

ทั้งสองฟังก์ชันนี้จะทำงานได้ดีกับ range หรือ array ที่มีขนาดเท่ากัน ทั้งที่อยู่ใน worksheet หรือผลลัพธ์ที่ได้มาจากสูตรอื่น (อย่าง FILTER ในตัวย่าง HSTACK) ผลลัพธ์จะเปลี่ยนแปลงไปตามข้อมูลต้นฉบับอัตโนมัติ

โดยเฉพาะการทำงานกับข้อมูลที่อยู่ในแบบ table จะทำงานได้ดีเลย เพราะขอบเขตของ table จะขยายขึ้นอัตโนมัติจากข้อมูลที่ผู้ใช้ใส่เพิ่มเข้าไป

⚠️ เช่นเดียวกับ dynamic array function อื่น ๆ HSTACK และ VSTACK จะแสดง error เป็น #SPILL! หากมีข้อมูลอื่นอยู่ในเซลล์ที่จะใส่ข้อมูล

และอีกอย่างคือ 2 ฟังก์ชันนี้อยู่ใน table ไม่ได้จะขึ้น #SPILL! เช่นกัน ถ้าทำไปแล้วก็ให้ไปที่ Table Design > Tools > Convert to range เพื่อแปลง table กับไปเป็น range เหมือนเดิม

สำหรับไฟล์ตัวอย่างของโพสต์นี้ สามารถดูและดาวน์โหลดได้ใน ลิงค์นี้ ได้เลยครับ

หากมีข้อสังสัย หรืออยากให้เพิ่มเติมตรงในสามารถไว้ใน comment ได้เลยนะ