VBA Excel - Excel | Combine data using formulas in Excel without using Power Query

วิธีรวมข้อมูลทุก Sheet โดยใช้สูตรใน Excel ไม่ต้องใช้ Power Query

Combine data using formulas in Excel without using Power Query

Excel สำหรับ Version Office 365

ออก functions ใหม่ ที่ทำให้รวมข้อมูลจากหลายๆ sheet

โดยไม่ต้องใช้ Power Query

ตามนี้

TRIMRANGE- > ดึงมา เฉพาะบรรทัด ที่มีข้อมูล

VSTACK -> เอา ข้อมูล มา ต่อกัน

DROP -> ตัดบรรทัด ของ ข้อมูล ออก

ตัวอย่างข้อมูล

ในไฟล์มี 3 Sheet ที่ต้องการจะรวม

ชื่อ

Central1,Central2,Central3

และ รวมสรุปใน Sheet ชื่อ CombineData

1.ใช้ TRIMRANGE

สร้างสูตร TRIMRANGE ที่ Sheet ComineData

โดยไปลากทั้ง Column ตั้ง A->E ใน Sheet Central1

ผลลัพธ์ที่ได้ จะเอามาทุกบรรทัดที่มี Data

บรรทัดด้านล่างที่เป็นช่องว่างไม่เอามาด้วย

แต่สังเกตว่า ผลลัพธ์ที่ได้ มีหัวตาราง ของ Sheet Central1 ติดมาด้วย

2.ดังนั้นเพื่อที่จะตัด บรรทัดแรก ออก

ใช้ฟังก์ชั่น DROP ช่วย

DROP เป็น Function ใช้ตัด ข้อมูล

โดย

DROP ( ก้อนข้อมุล , จำนวนบรรทัดที่ตัดออก )

=DROP( TRIMRANGE(Central1!A:E) , 1 )

ดังนั้นผลลัพธ์ที่ได้

คือการดึง ตารางข้อมูล ที่ถูก TRIMRANGE
แล้ว ตัดบรรทัด บนสุดออก
ซึ่งคือการตัด บรรทัดที่เป็นตารางออก

สังเกตจากรูป จะไม่มีหัวตารางแล้ว

3.หลังจากที่

ตัดบรรทัดว่างด้วย TRIMRANGE

ตัดบรรทัดแรกดวย DROP

ขั้นตอนถัดไปคือเอามาต่อกัน

ด้วย VSTACK

VSTACK เป็น Function ที่เอาข้อมูลมา ต่อกัน แนวตั้ง

ดังนั้นจากสูตร

คือ เอาข้อมูล 3 ตาราง ที่มีการ

เรียบร้อยแล้ว

ผลลัพธ์ที่ได้คือ ข้อมูลมาต่อกันเรียบร้อย

และ ถ้าในแต่ละ sheet

Central1,Central2,Central3

มีการเพิ่มบรรทัด

ข้อมูลที่ Sheet CombineData

จะ update ใหม่ด้วย