home

หน้าแรก
 Computer >> บทช่วยสอนคอมพิวเตอร์ >  >> ซอฟต์แวร์ >> Office

สร้าง Excel Workbook โต้ตอบโดยไม่ใช้ VBA: คู่มือขั้นตอน

Excel มีฟีเจอร์ built-in ที่ทรงพลังซึ่งช่วยให้คุณสร้างไฟล์แบบไดนามิกและโต้ตอบ—อย่างรายงาน แดชบอร์ด และฟอร์ม—โดยไม่ใช้ VBA เลย คุณสามารถสร้างรายงานโต้ตอบที่อัปเดตข้อมูลทันที

ในบทช่วยสอนนี้ เราจะแสดงวิธีสร้างไฟล์โต้ตอบเหล่านี้โดยไม่มี VBA

1. เตรียมชุดข้อมูลของคุณ

เพื่อทำไฟล์ Excel โต้ตอบ คุณต้องทำความสะอาดและจัดโครงสร้างชุดข้อมูล ชุดข้อมูลที่จัดรูปแบบและมีโครงสร้างดีเป็นสิ่งสำคัญสำหรับการสร้างพฤติกรรมโต้ตอบที่มีประสิทธิภาพ

  • ทำความสะอาดชุดข้อมูลของคุณ
  • ลบ duplicates และ spaces
  • แก้ไขปัญหาการจัดรูปแบบและ data type

แปลงข้อมูลเป็นตาราง

  • เลือกชุดข้อมูล
  • ไปที่แท็บ Insert >> เลือก Table
  • ติ๊ก My table has headers
  • คลิก OK
  • เปลี่ยนชื่อตาราง:
    • ไปที่แท็บ Table Design >> เลือก Table Name และตั้งชื่อที่เกี่ยวข้อง เช่น Sales

2. ใช้ Data Validation Drop-Down Lists

Drop-down lists เป็นองค์ประกอบสำคัญสำหรับการสร้างไฟล์ Excel โต้ตอบ Drop-down list เปิดใช้งานการป้อนข้อมูลของผู้ใช้ที่รวดเร็ว สม่ำเสมอ และควบคุมได้ ต่อไป คุณสามารถใช้มันกรองข้อมูล ชาร์ต และ KPIs แบบโต้ตอบ

ขั้นตอน:

สร้าง Helper Lists: สร้าง helper lists ด้านบนหรือขวาของชีท คุณสามารถซ่อนพวกมันภายหลัง

  • เลือกเซลล์และใส่สูตรต่อไปนี้เพื่อรายการ region ที่ไม่ซ้ำ:
=SORT(UNIQUE(Sales[Region]))
  • เลือกเซลล์และใส่สูตรต่อไปนี้เพื่อรายการชื่อสินค้า:
=SORT(UNIQUE(Sales[Product]))
  • Optional: เพิ่ม "All" ด้านบนแต่ละรายการก่อนใส่สูตรเพื่อ spill รายการ unique

สร้าง Drop-Down List:

  • เลือกเซลล์ที่คุณต้องการ drop-down (เช่น B2)
  • ไปที่แท็บ Data >> เลือก Data Validation
  • ภายใต้ Allow เลือก List
  • ใน Source เลือกรายการ region จากคอลัมน์ helper
  • คลิก OK
  • ทำขั้นตอนคล้ายกัน สร้าง drop-down list สำหรับ Product
  • ภายใต้ Allow เลือก List
  • ใน Source เลือกรายการ product จากคอลัมน์ helper

Tip: คุณสามารถสร้าง named range สำหรับรายการและใช้ใน data validation

3. ใช้ Dynamic Formulas กับ Drop-Down

การรวม dynamic formulas กับ drop-down อัปเดต KPIs และกรองข้อมูลอัตโนมัติ

สร้าง KPIs:

  • Total Revenue:
=SUMIFS(
    Sales[Revenue],
    Sales[Region], IF($B$2="All","*", $B$2),
    Sales[Product], IF($B$4="All","*", $B$4)
)
  • Average Discount:
=AVERAGEIFS(
    Sales[Discount],
    Sales[Region], IF($B$2="All","*", $B$2),
    Sales[Product], IF($B$4="All","*", $B$4)
)

ใช้ percentage format เพื่อลุックスะอาด

  • Total Orders:
=COUNTIFS(
    Sales[Region], IF($B$2="All","*", $B$2),
    Sales[Product], IF($B$4="All","*", $B$4)
)
  • Total Units Sold:
=SUMIFS(
    Sales[Units],
    Sales[Region], IF($B$2="All","*", $B$2),
    Sales[Product], IF($B$4="All","*", $B$4)
)

ทดสอบโต้ตอบ:

  • เลือก Region และ Product จาก drop-down lists
  • KPIs อัปเดตอัตโนมัติ

4. สร้าง Dynamic Charts ด้วย Named Ranges

ทำชาร์ตที่อัปเดตอัตโนมัติตามการเลือกของผู้ใช้

Filtered Data:

=FILTER(
    CHOOSE({1,2}, Sales[Date], Sales[Revenue]),
    IF($B$2="All", Sales[Region]<>"", Sales[Region]=$B$2) *
    IF($B$4="All", Sales[Product]<>"", Sales[Product]=$B$4),
    "No rows"
)

กำหนด Dynamic Named Range:

  • ไปที่แท็บ Formulas >> เลือก Name Manager >> เลือก New
  • Name: FilteredDates
  • Refers to:
=INDEX('Interactive Sheet'!$B$7#, ,1)
  • Name: FilteredRevenue
  • Refers to:
=INDEX('Interactive Sheet'!$B$7#, ,2)

สร้าง Chart:

  • ไปที่แท็บ Insert >> จาก Charts >> เลือก Line Chart
  • คลิกขวา chart >> เลือก Select Data
  • สำหรับ Series Values พิมพ์:
='Interactive Sheet'!FilteredRevenue
  • สำหรับ Horizontal Axis Labels:
='Interactive Sheet'!FilteredDates

ทดสอบโต้ตอบ:

  • เลือก East region และ all products
  • Chart แสดงข้อมูลที่เกี่ยวข้องทั้งหมด
  • เลือก Region และ Product เฉพาะเจาะจง
  • Chart กรองทันที
  • สิ่งนี้ทำงานได้สำหรับการจับคู่ใดๆ โดยไม่ต้อง VBA

5. ใช้ Conditional Formatting สำหรับ Visual Feedback

ไฮไลท์ค่าสำคัญหรือการเลือกของผู้ใช้ทันทีด้วย Conditional Formatting

ขั้นตอน:

  • เลือกช่วงข้อมูลของคุณ
  • ไปที่แท็บ Home >> เลือก Conditional Formatting >> เลือก New Rule
  • เลือก Use a formula to determine which cells to format
  • ใส่สูตรต่อไปนี้:
=AND(OR('Interactive Sheet'!$B$2="All", $C2='Interactive Sheet'!$B$2), OR('Interactive Sheet'!$B$4="All", $F2='Interactive Sheet'!$B$4))
  • เลือกสีไฮไลท์
  • คลิก OK

เฉพาะแถวที่ตรงกับการเลือกจะถูกไฮไลท์ เสริมการเลือกฟิลเตอร์แบบภาพ

6. PivotTables, Slicers, และ Interactive Charts

PivotTables, PivotCharts, และ Slicers เป็นฟีเจอร์โต้ตอบหลักของ Excel ที่ทำให้การกรองข้อมูลง่ายอย่างไร้ที่ติ

สร้าง PivotTable:

  • เลือกช่วงข้อมูลของคุณ
  • ไปที่แท็บ Insert >> เลือก PivotTable
  • เลือกตำแหน่ง: New หรือ Existing Worksheet
  • คลิก OK
  • จาก PivotTable Fields:
    • ลาก Product ไป Rows
    • ลาก Revenue ไป Values

สร้าง PivotCharts:

  • เลือก PivotTable
  • ไปที่แท็บ PivotTable Analyze >> เลือก PivotChart
  • เลือก Clustered Column chart
  • คลิก OK

เพิ่ม Interactive Slicers:

  • ไปที่แท็บ PivotTable Analyze >> เลือก Insert Slicer
  • เลือกฟิลด์ที่จะกรอง เช่น:
    • Region, Product, Salesperson, ฯลฯ
  • คุณสามารถคลิกปุ่มใน slicer เพื่อกรองชาร์ตและตารางทันที
  • เพื่อให้ slicers และ chart อยู่ร่วมกัน คุณสามารถ group พวกมัน
  • วาง slicers ใกล้หรือบน chart เลือก object ทั้งหมด (chart และ slicers) แล้วคลิกขวาและเลือก Group

การเชื่อม Multiple PivotTables ด้วย Shared Slicers

  • สร้าง pivot tables หลายตัวจากแหล่งข้อมูลเดียวกัน
  • แทรก Slicers
  • คลิกขวา slicer >> เลือก Report Connections
  • เชื่อม slicer กับ PivotTables หลายตัว
  • ตอนนี้ copy slicers และ charts ไปที่ dashboard sheet
  • คุณสามารถซ่อน PivotTable sheet

7. ใช้ Hyperlinks สำหรับการนำทาง

เพื่อให้ไฟล์ Excel ของคุณรู้สึกเหมือนแอปที่คลิกได้ คุณสามารถใช้ hyperlinks สำหรับการนำทาง นี่ช่วยให้ผู้ใช้กระโดดไปชีท ชาร์ต หรือตารางต่างๆ ได้

ขั้นตอน:

  • ไปที่แท็บ Insert >> จาก Illustrations >> เลือก Shapes >> เลือก Rectangle
  • ตั้งชื่อ shape ว่า "Sales Data"
  • คลิกขวา shape >> เลือก Link
  • ในกล่องโต้ตอบ Insert Hyperlink:
  • เลือก Place in This Document >> พิมพ์ cell reference A1 >> เลือกชีท Sales
  • คลิก OK
  • เลือก Place in This Document >> พิมพ์ cell reference A3 >> เลือกชีท PivotTable
  • คลิก OK
  • จัดรูปแบบให้ดูเหมือนปุ่มเพื่อลุคที่สะอาด
  • Navigation hyperlinks เหล่านี้ช่วยให้ผู้ใช้กระโดดระหว่าง "Dashboard" และ "Details" sheets ต่างๆ ได้ง่าย

Final Interactive Excel File:

  • ตัวอย่างด้านล่างแสดงทุก region และ product "Accessory" ที่เลือก
  • การเลือกยังสามารถทำจาก slicer ได้
  • ข้อมูลทั้งหมดอัปเดตอัตโนมัติ

เคล็ดลับ

  • เราสาธิตทั้ง dynamic chart (ใช้ FILTER function) และ PivotChart เพื่อแสดงวิธีโต้ตอบที่แตกต่าง คุณสามารถเลือกแบบใดก็ได้ที่เหมาะกับความต้องการ
  • คุณยังสามารถสำรวจ Form Controls ซึ่งมีตัวเลือกโต้ตอบมากกว่าโดยไม่ต้อง VBA
  • เพื่อสร้าง search box คุณสามารถใช้ฟังก์ชัน XLOOKUP แบบไดนามิก

ดาวน์โหลด Practice Workbook

สรุป

ด้วยฟีเจอร์และเทคนิคเหล่านี้ คุณสามารถสร้างไฟล์ Excel ที่ซับซ้อน โต้ตอบ ได้โดยไม่มี VBA เลย กุญแจคือการรวมหลายเทคนิคเพื่อ interactivity สูงสุด จำไว้ว่าต้องทดสอบ interactive elements อย่างละเอียดและให้คำแนะนำชัดเจนสำหรับผู้ใช้ ด้วยการฝึก คุณจะสร้างแดชบอร์ดและเครื่องมือที่น่าประทับใจซึ่งทำให้การวิเคราะห์ข้อมูลทรงพลังและใช้งานง่าย ทั้งหมดโดยไม่เขียนโค้ด VBA บรรทัดเดียว