สร้าง Multi-Level Hierarchies ใน Excel: 2 วิธีง่ายๆ

ขณะทำงานใน Excel เรามักต้อง สร้าง Multi-Level Hierarchy (ลำดับชั้นหลายระดับ) โดยใช้ Multi-Level Hierarchy เราสามารถนำเสนอข้อมูลในรูปแบบที่เรียบง่ายและกระชับได้ Microsoft Excel เป็นซอฟต์แวร์ที่ทรงพลังและหลากหลาย ใช้ Excel สร้าง Multi-Level Hierarchy ได้โดยทำตามขั้นตอนง่ายๆ บทความนี้จะแนะนำ 2 วิธีง่ายในการ สร้าง Multi-Level Hierarchy ใน Excel

2 วิธีง่ายในการสร้าง Multi-Level Hierarchy ใน Excel

ใน 2 วิธี วิธีแรกจะใช้ฟีเจอร์ Data Validation (ตรวจสอบข้อมูล) ของ Excel วิธีที่สองจะใช้ Power Pivot Add-in ของ Excel

ขอระบุว่าใช้ Microsoft Excel 365 สำหรับบทความนี้ คุณสามารถใช้เวอร์ชันอื่นตามสะดวกได้

1. ใช้ฟีเจอร์ Data Validation สร้าง Multi-Level Hierarchy

การใช้ฟีเจอร์ Data Validation เป็นหนึ่งในวิธีที่ง่ายที่สุดในการ สร้าง Multi-Level Hierarchy ใน Excel ในชุดข้อมูลต่อไปนี้ มี Movie Name (ชื่อหนัง), Show Schedule (รอบฉาย), และ Seat Type (ประเภทที่นั่ง) สำหรับหนังที่กำลังฉายของโรงหนัง เป้าหมายคือ สร้าง Multi-Level Hierarchy จากข้อมูลนี้ มาทำตามขั้นตอนด้านล่างกัน

ขั้นตอนที่ 01: ตั้งค่า Data Validation สำหรับคอลัมน์ Movie Name

ในคอลัมน์ Movie Name ต้องสร้าง Drop-down list เพื่อเลือกหนังที่ชอบ ทำได้ง่ายมาก เพียงทำตามขั้นตอน

• ขั้นแรก เลือกเซลล์ B5 ภายใต้คอลัมน์ Movie Name
• ไปที่แท็บ Data จาก Ribbon
• คลิก Data Validation จากกลุ่ม Data Tools

กล่องโต้ตอบ Data Validation จะเปิดขึ้น

• คลิกไอคอน Drop-down ตามภาพ
• เลือก List จาก Drop-down

• คลิกที่กล่อง Source และใส่สูตรต่อไปนี้

ที่นี่ $B$5:$B$10 คือช่วงเซลล์ภายใต้คอลัมน์ Movie Name ของชุดข้อมูลที่ให้มา

หมายเหตุ: ใช้ Absolute Cell Reference (อ้างอิงเซลล์แบบสัมบูรณ์) เพราะในแต่ละเซลล์ต้องแสดงชื่อหนังทั้งหมดเพื่อให้เลือกได้

• คลิก OK

• จะมีไอคอน Drop-down ข้างเซลล์ B5 คลิกที่นั่น

ผลลัพธ์: ใน Drop-down list สามารถเลือกหนังที่ชอบได้

ขั้นตอนที่ 02: คัดลอก Validation ไปยังเซลล์อื่นในคอลัมน์ Movie Name

ใช้ฟีเจอร์ Paste Special ของ Excel เพื่อคัดลอก Data Validation ของเซลล์ B5 ไปยังเซลล์อื่นในคอลัมน์ Movie Name

• เลือกเซลล์ B5 กด CTRL + C เพื่อคัดลอก

• เลือกเซลล์ที่เหลือของคอลัมน์ Movie Name
• ไปแท็บ Home จาก Ribbon
• คลิก Paste แล้วเลือก Paste Special

• ในกล่อง Paste Special เลือก Validation แล้วคลิก OK

Data Validation จะถูกคัดลอกไปยังเซลล์ที่เลือก สามารถเลือก Movie Names จาก Drop-down ได้

ขั้นตอนที่ 03: ตั้งค่า Data Validation สำหรับคอลัมน์ Show Schedule

ตอนนี้ต้องสร้าง Drop-down list ตามการเลือกในคอลัมน์ Movie Name เช่น หนัง Into the Deep มี 5 รอบฉาย แต่น Bullet Train มีแค่ 3 รอบ ตัวเลือกในคอลัมน์ Show Schedule ขึ้นอยู่กับหนังที่เลือกใน Movie Name จะใช้ฟังก์ชัน OFFSET และ MATCH มาทำตามขั้นตอน

• เลือกเซลล์ C5 และทำตาม ขั้นตอนที่ 01 เพื่อได้ผลลัพธ์

• ใส่สูตรต่อไปนี้ในกล่อง Source

ที่นี่ เซลล์ $B$14 คือ Header ของ Show Schedule ของ Dataset 1, เซลล์ $B5 คือหนังที่เลือกในคอลัมน์ Movie Name และช่วง $B$14:$G$14 คืออาร์เรย์ชื่อหนังในชีต Dataset 1

Formula Breakdown

• MATCH($B5,'Dataset 1 '!$B$14:$G$14,0) → คืนตำแหน่งของค่าค้นหาในแถว/คอลัมน์/ตาราง
  • $B5 → lookup_value
  • 'Dataset 1 '!$B$14:$G$14 → lookup_array
  • 0 → match_type
  • Output → 1
• OFFSET('Dataset 1 '!$B$14,1,MATCH($B5,'Dataset 1 '!$B$14:$G$14,0)-1,5,1) → กลายเป็น OFFSET('Dataset 1 '!$B$14,1,1-1,5,1)
  • 'Dataset 1 '!$B$14 → reference
  • 1 → rows
  • 1-1 → cols
  • 5 → height
  • 1 → width
  • Output → {11:00 AM, 3:30 PM, 6:00 PM, 9:30 PM, 11:00 PM}

• คลิก OK

• จะมีไอคอน Drop-down ข้างเซลล์ C5 คลิกที่นั่น

จะเห็น Show Schedule ที่มีสำหรับหนัง Into the Deep

ขั้นตอนที่ 04: คัดลอก Validation ไปยังเซลล์อื่นในคอลัมน์ Show Schedule

คัดลอกคุณสมบัติ Data Validation ของเซลล์ C5 ไปเซลล์อื่นด้วย Paste Special

• เลือกเซลล์ C5 ทำตาม ขั้นตอนที่ 02
• เลือกหนัง Bullet Train จะเห็นตัวเลือก Show Schedule เพียง 3 ตัว ตามชุดข้อมูล

• เลือก Show Schedule สำหรับหนังอื่น ได้ผลลัพธ์ตามภาพ

ขั้นตอนที่ 05: ตั้งค่า Data Validation สำหรับคอลัมน์ Seat Type

สร้าง Drop-down list ในคอลัมน์ Seat Type ตามการเลือก Show Schedule ในชุดข้อมูล Show Schedule ทุกตัวไม่มี Seat Type เหมือนกัน เช่น รอบ 11:00 AM มี 3 ประเภท แต่รอบ 6:00 PM มีแค่ Regular และ VIP ใช้ OFFSET และ MATCH อีกครั้ง

• เลือกเซลล์ D5 ทำตาม ขั้นตอนที่ 01

• ใส่สูตรใน Source Box ของกล่อง Data Validation

ที่นี่ เซลล์ $B$23 คือ Header ของ Seat Type ใน Dataset 1, เซลล์ $C5 คือ Show Schedule ที่เลือก และช่วง $B$23:$F$23 คืออาร์เรย์ Show Schedule ในชีต Dataset 1

Formula Breakdown

• MATCH($C5,'Dataset 1 '!$B$23:$F$23,0) → คืนตำแหน่งค่าค้นหา
  • $C5 → lookup_value
  • 'Dataset 1 '!$B$23:$F$23 → lookup_array
  • 0 → match_type
  • Output → 3
• OFFSET('Dataset 1 '!$B$23,1,MATCH($C5,'Dataset 1 '!$B$23:$F$23,0)-1,3,1) → กลายเป็น OFFSET('Dataset 1 '!$B$23,1,3-1,3,1)
  • 'Dataset 1 '!$B$23 → reference
  • 1 → rows
  • 3-1 → cols
  • 3 → height
  • 1 → width
  • Output → {Regular, VIP}

• คลิก OK

จะมีไอคอน Drop-down ข้างเซลล์ D5 เนื่องจาก Show Schedule ที่เลือกคือ 6:00 PM ตัวเลือกคือ Regular และ VIP

ขั้นตอนที่ 06: คัดลอก Validation ไปยังเซลล์อื่นในคอลัมน์ Seat Type

คัดลอก Data Validation ของเซลล์ D5 ไปเซลล์อื่นในคอลัมน์ Seat Type

• เลือกเซลล์ D5 ทำตาม ขั้นตอนที่ 02
• คลิก Drop-down ข้างเซลล์ C6 เนื่องจาก Show Schedule เป็น 11:00 AM มี Seat Types ครบ 3 ประเภท

• เลือก Seat Type สำหรับเซลล์อื่น ได้ผลลัพธ์ตามภาพ

อ่านเพิ่มเติม: วิธีสร้าง Hierarchy ใน Excel (3 วิธีง่าย)

2. ใช้ Power Pivot สร้าง Multi-Level Hierarchy ใน Excel

การใช้ฟีเจอร์ Power Pivot เป็นอีกวิธีที่มีประสิทธิภาพในการ สร้าง Multi-Level Hierarchy ใน Excel ในชุดข้อมูลต่อไป มีตารางข้อมูล Sales (ยอดขาย) จาก Cities (เมือง) ต่างๆ ใน States (รัฐ) ต่างๆ ของ USA จะ สร้าง Multi-Level Hierarchy จากตารางนี้

ขั้นตอนที่ 01: เพิ่มตารางเข้า Power Pivot Window

หากยังไม่ได้เปิด Power Pivot Add-in ทำตามขั้นตอนที่นี่ เพื่อเปิดใช้งาน หลังเปิดแล้ว ทำตามขั้นตอน

• เลือกตารางทั้งหมด กด CTRL + C คัดลอก

• ไปแท็บ Power Pivot จาก Ribbon
• คลิก Manage

จะเปิด Power Pivot Window ว่างเปล่า

• ใน Power Pivot Window คลิก Clipboard แล้วเลือก Paste

กล่อง Paste Preview จะปรากฏ

• คลิก OK

ตารางจะถูกเพิ่มเข้า Power Pivot Window

ขั้นตอนที่ 02: สร้าง Hierarchy ใน Diagram View

สร้าง Hierarchy ที่อธิบาย Cities ภายใต้ States และ States ภายใต้ Country

• คลิก Diagram View จากแท็บ View ของ Power Pivot Window

Diagram View จะแสดง

• คลิกส่วนที่ทำเครื่องหมายในภาพ

จะสร้าง Hierarchy ขึ้นมา

• ลากคอลัมน์ Country วางใน Hierarchy ใหม่

• ลากคอลัมน์อื่นๆ ตามลำดับ ได้ผลลัพธ์ตามภาพ

ขั้นตอนที่ 03: สร้าง Pivot Table

• คลิก Pivot Table

• กล่อง Create Pivot Table จะปรากฏ คลิก OK

ชีตใหม่จะถูกสร้าง

• จาก Pivot Table Fields ลาก Hierarchy1 ไป Rows และ Sales ไป Sum of Values

จะได้ Pivot Table พร้อม Hierarchy

• คลิกเครื่องหมาย + ตามภาพ

จะเห็น States และ Sum of Sales ของแต่ละ State

• คลิก + ข้างชื่อ State Arizona

จะแสดง Cities ใน State Arizona พร้อม Sales

• คลิก + ข้าง States อื่น ได้ผลลัพธ์ตามภาพ

อ่านเพิ่มเติม: วิธีสร้าง Hierarchy ใน Excel Pivot Table (ขั้นตอนง่ายๆ)

Practice Section

ใน Excel Workbook มี Practice Section ด้านขวาของแต่ละชีต โปรดฝึกฝนด้วยตัวเอง

สรุป

นี่คือสิ่งทั้งหมดสำหรับวันนี้ เชื่อว่าบทความนี้แนะนำการ สร้าง Multi-Level Hierarchy ใน Excel ได้ดี โปรดแสดงความคิดเห็นหากมีข้อสงสัยหรือข้อเสนอแนะ หากต้องการเรียนรู้ Excel เพิ่มเติม เยี่ยมชมเว็บไซต์ ExcelDemy Happy Learning!

บทความเกี่ยวข้อง

• วิธีสร้าง Hierarchy Chart ใน Excel (3 วิธีง่าย)
• สร้าง Date Hierarchy ใน Excel Pivot Table (ขั้นตอนง่ายๆ)
• วิธีใช้ SmartArt Hierarchy ใน Excel (พร้อมขั้นตอน)
• เพิ่ม Row Hierarchy ใน Excel (2 วิธีง่าย)