Excel นำเสนอฟีเจอร์ในตัวที่มีประสิทธิภาพซึ่งช่วยให้คุณสามารถสร้างไฟล์แบบไดนามิกและแบบโต้ตอบได้ เช่น รายงาน แดชบอร์ด และแบบฟอร์ม โดยไม่ต้องใช้ VBA ใดๆ คุณสามารถสร้างรายงานเชิงโต้ตอบที่สามารถอัปเดตข้อมูลได้ทันที
ในบทช่วยสอนนี้ เราจะแสดงวิธีสร้างไฟล์เชิงโต้ตอบเหล่านี้โดยไม่ต้องใช้ VBA
1. เตรียมชุดข้อมูลของคุณ
หากต้องการสร้างไฟล์ Excel แบบโต้ตอบ คุณต้องล้างและจัดโครงสร้างชุดข้อมูลของคุณ ชุดข้อมูลที่มีการจัดรูปแบบและมีโครงสร้างที่ดีเป็นสิ่งสำคัญสำหรับการสร้างพฤติกรรมเชิงโต้ตอบที่มีประสิทธิภาพ
- ล้างชุดข้อมูลของคุณ
- ลบรายการที่ซ้ำกันและการเว้นวรรค
- ปัญหาการจัดรูปแบบและประเภทข้อมูลที่ถูกต้อง
แปลงข้อมูลเป็นตาราง
- เลือกชุดข้อมูล
- ไปที่ แทรก แท็บ>> เลือก ตาราง .
- กาเครื่องหมาย ตารางของฉันมีส่วนหัว .
- คลิก ตกลง .
- เปลี่ยนชื่อตาราง:
- ไปที่การออกแบบตาราง แท็บ>> เลือก ชื่อตาราง และตั้งชื่อที่เกี่ยวข้อง เช่น การขาย .
2. ใช้รายการแบบเลื่อนลงการตรวจสอบข้อมูล
รายการแบบเลื่อนลงเป็นองค์ประกอบสำคัญสำหรับการสร้างไฟล์ Excel แบบโต้ตอบ รายการแบบเลื่อนลงทำให้ผู้ใช้ป้อนข้อมูลได้อย่างรวดเร็ว สม่ำเสมอ และควบคุมได้ หลังจากนั้น คุณสามารถใช้เพื่อกรองข้อมูล แผนภูมิ และ KPI แบบโต้ตอบได้
ขั้นตอน:
สร้างรายการตัวช่วย: สร้างรายการตัวช่วยที่ด้านบนหรือด้านขวาของแผ่นงาน คุณสามารถซ่อนสิ่งเหล่านี้ได้ในภายหลัง
- เลือกเซลล์และป้อนสูตรต่อไปนี้เพื่อแสดงรายการขอบเขตที่ไม่ซ้ำกัน:
=SORT(UNIQUE(Sales[Region]))
- เลือกเซลล์และป้อนสูตรต่อไปนี้เพื่อแสดงชื่อผลิตภัณฑ์:
=SORT(UNIQUE(Sales[Product]))
- ไม่บังคับ: เพิ่ม "ทั้งหมด" เหนือแต่ละรายการก่อนที่จะแทรกสูตรเพื่อทำให้รายการไม่ซ้ำกัน
สร้างรายการแบบเลื่อนลง:
- เลือกเซลล์ที่คุณต้องการให้รายการแบบเลื่อนลง (เช่น B2)
- ไปที่ข้อมูล แท็บ>> เลือก การตรวจสอบข้อมูล .
- ภายใต้ อนุญาต ให้เลือก รายการ .
- ใน แหล่งที่มา ให้เลือกรายการภูมิภาคจากคอลัมน์ตัวช่วย
- คลิก ตกลง .
- ทำตามขั้นตอนที่คล้ายกัน สร้างรายการแบบเลื่อนลงสำหรับผลิตภัณฑ์
- ภายใต้ อนุญาต ให้เลือก รายการ .
- ใน แหล่งที่มา ให้เลือกรายการผลิตภัณฑ์จากคอลัมน์ตัวช่วย
เคล็ดลับ: คุณสามารถสร้างช่วงที่มีชื่อสำหรับรายการแล้วใช้ในการตรวจสอบข้อมูลได้
3. ใช้สูตรไดนามิกแบบเลื่อนลง
การรวมสูตรแบบไดนามิกเข้ากับเมนูแบบเลื่อนลงจะอัปเดต KPI และตัวกรองข้อมูลโดยอัตโนมัติ
สร้าง KPI:
- รายได้ทั้งหมด:
=SUMIFS( Sales[Revenue], Sales[Region], IF($B$2="All","*", $B$2), Sales[Product], IF($B$4="All","*", $B$4))
- ส่วนลดเฉลี่ย:
=AVERAGEIFS( Sales[Discount], Sales[Region], IF($B$2="All","*", $B$2), Sales[Product], IF($B$4="All","*", $B$4))
ใช้รูปแบบเปอร์เซ็นต์เพื่อให้ดูสะอาดตา
- คำสั่งซื้อทั้งหมด:
=COUNTIFS( Sales[Region], IF($B$2="All","*", $B$2), Sales[Product], IF($B$4="All","*", $B$4))
- จำนวนหน่วยที่ขายได้:
=SUMIFS( Sales[Units], Sales[Region], IF($B$2="All","*", $B$2), Sales[Product], IF($B$4="All","*", $B$4))
ทดสอบการโต้ตอบ:
- เลือกภูมิภาคและผลิตภัณฑ์จากรายการแบบเลื่อนลง
- KPI อัปเดตโดยอัตโนมัติ
4. สร้างแผนภูมิแบบไดนามิกด้วยช่วงที่ตั้งชื่อ
สร้างแผนภูมิที่อัปเดตโดยอัตโนมัติตามการเลือกของผู้ใช้
ข้อมูลที่กรอง:
=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")
กำหนดช่วงชื่อแบบไดนามิก:
- ไปที่ สูตร แท็บ>> เลือก ตัวจัดการชื่อ>> เลือก ใหม่ .
- ชื่อ: วันที่กรอง
- หมายถึง:
=INDEX('Interactive Sheet'!$B$7#, ,1)
- ชื่อ: กรองรายได้
- หมายถึง:
=INDEX('Interactive Sheet'!$B$7#, ,2)
สร้างแผนภูมิ:
- ไปที่ แทรก แท็บ>> จาก แผนภูมิ >> เลือก แผนภูมิเส้น .
- คลิกขวาที่แผนภูมิ>> เลือก เลือกข้อมูล .
- สำหรับค่าซีรี่ส์ ให้พิมพ์:
='Interactive Sheet'!FilteredRevenue
- สำหรับป้ายกำกับแกนนอน:
='Interactive Sheet'!FilteredDates
ทดสอบการโต้ตอบ:
- เลือกภูมิภาคตะวันออกและผลิตภัณฑ์ทั้งหมด
- แผนภูมิแสดงข้อมูลที่เกี่ยวข้องทั้งหมด
- เลือกภูมิภาคเฉพาะและผลิตภัณฑ์เฉพาะ
- แผนภูมิจะกรองทันที
- ใช้ได้กับทุกชุดที่ไม่มี VBA
5. ใช้การจัดรูปแบบตามเงื่อนไขสำหรับการตอบสนองด้วยภาพ
เน้นค่าคีย์หรือตัวเลือกของผู้ใช้ทันทีด้วยการจัดรูปแบบตามเงื่อนไข
ขั้นตอน:
- เลือกช่วงข้อมูลของคุณ
- ไปที่หน้าแรก แท็บ>> เลือก การจัดรูปแบบตามเงื่อนไข>> เลือก กฎใหม่ .
- เลือก ใช้สูตรเพื่อกำหนดเซลล์ที่จะจัดรูปแบบ .
- ใส่สูตรต่อไปนี้:
=AND(OR('Interactive Sheet'!$B$2="All", $C2='Interactive Sheet'!$B$2), OR('Interactive Sheet'!$B$4="All", $F2='Interactive Sheet'!$B$4))
- เลือกสีไฮไลต์
- คลิก ตกลง .
เฉพาะแถวที่ตรงกันเท่านั้นที่จะถูกไฮไลต์ ซึ่งช่วยเสริมการเลือกตัวกรองด้วยสายตา
6. PivotTable ตัวแบ่งส่วนข้อมูล และแผนภูมิเชิงโต้ตอบ
PivotTables, PivotChart และ Slicers เป็นฟีเจอร์โต้ตอบหลักใน Excel ที่ทำให้การกรองข้อมูลเป็นเรื่องง่ายอย่างเหลือเชื่อ
สร้าง PivotTable:
- เลือกช่วงข้อมูลของคุณ
- ไปที่ ส่วนแทรก แท็บ>> เลือก PivotTable .
- เลือกสถานที่:ใหม่ หรือ แผ่นงานที่มีอยู่ .
- คลิก ตกลง .
- จาก เขตข้อมูล PivotTable :
- ลาก ผลิตภัณฑ์ ไปที่ แถว พื้นที่
- ลาก รายได้ ไปที่ ค่า พื้นที่
สร้าง PivotChart:
- เลือก PivotTable
- ไปที่การวิเคราะห์ PivotTable แท็บ>> เลือก PivotChart .
- เลือก คอลัมน์แบบคลัสเตอร์ แผนภูมิ
- คลิก ตกลง .
เพิ่มตัวแบ่งส่วนข้อมูลเชิงโต้ตอบ:
- ไปที่การวิเคราะห์ PivotTable แท็บ>> เลือก แทรกตัวแบ่งส่วนข้อมูล .
- เลือกฟิลด์ที่จะกรอง เช่น:
- ภูมิภาค ผลิตภัณฑ์ พนักงานขาย ฯลฯ
- คุณสามารถคลิกปุ่มในตัวแบ่งส่วนข้อมูลเพื่อกรองแผนภูมิและตารางได้ทันที
- หากต้องการเก็บตัวแบ่งส่วนข้อมูลและแผนภูมิไว้ด้วยกัน คุณสามารถจัดกลุ่มได้
- วางตัวแบ่งส่วนข้อมูลไว้ใกล้หรือบนแผนภูมิ เลือกวัตถุทั้งหมด (แผนภูมิและตัวแบ่งส่วนข้อมูล) จากนั้นคลิกขวาและเลือก กลุ่ม .
การเชื่อมต่อ PivotTable หลายรายการกับตัวแบ่งส่วนข้อมูลที่ใช้ร่วมกัน
- สร้างตาราง Pivot หลายรายการจากแหล่งข้อมูลเดียวกัน
- แทรก ตัวแบ่งส่วนข้อมูล .
- คลิกขวาที่ตัวแบ่งส่วนข้อมูล>> เลือก รายงานการเชื่อมต่อ .
- เชื่อมต่อตัวแบ่งส่วนข้อมูลกับ PivotTable หลายรายการ
- ตอนนี้ ให้คัดลอกตัวแบ่งส่วนข้อมูลและแผนภูมิไปยังแผ่นแดชบอร์ดของคุณ
- คุณสามารถซ่อนแผ่นงาน PivotTable ได้
7. ใช้ไฮเปอร์ลิงก์สำหรับการนำทาง
เมื่อต้องการทำให้ไฟล์ Excel ของคุณรู้สึกเหมือนเป็นแอปที่สามารถคลิกได้ คุณสามารถใช้ไฮเปอร์ลิงก์สำหรับการนำทาง ซึ่งจะทำให้ผู้ใช้สามารถข้ามไปยังแผ่นงาน แผนภูมิ หรือตารางต่างๆ ได้
ขั้นตอน:
- ไปที่ ส่วนแทรก แท็บ>> จาก ภาพประกอบ>> เลือก รูปร่าง>> เลือก สี่เหลี่ยมผืนผ้า .
- ตั้งชื่อรูปร่างเป็น “ข้อมูลการขาย”
- คลิกขวาที่รูปร่าง>> เลือก ลิงก์ .
- ในแทรกไฮเปอร์ลิงก์ กล่องโต้ตอบ:
- เลือก วางในเอกสารนี้>> พิมพ์การอ้างอิงเซลล์ A1>> เลือก ยอดขาย แผ่นงาน
- คลิก ตกลง .
- เลือก วางในเอกสารนี้>> พิมพ์การอ้างอิงเซลล์ A3>> เลือก PivotTable แผ่นงาน
- คลิก ตกลง .
- จัดรูปแบบเหมือนปุ่มเพื่อให้ดูสะอาดตา
- ไฮเปอร์ลิงก์การนำทางเช่นนี้ช่วยให้ผู้ใช้สามารถข้ามระหว่างแผ่น "แดชบอร์ด" และแผ่น "รายละเอียด" ต่างๆ ได้อย่างง่ายดาย
ไฟล์ Excel แบบโต้ตอบขั้นสุดท้าย:
- ตัวอย่างด้านล่างแสดงทุกภูมิภาคและผลิตภัณฑ์ “อุปกรณ์เสริม” ที่เลือก
- สามารถเลือกได้จากตัวแบ่งส่วนข้อมูล
- ข้อมูลทั้งหมดอัปเดตโดยอัตโนมัติ
เคล็ดลับ
- เราได้สาธิตทั้งแผนภูมิไดนามิก (โดยใช้ฟังก์ชัน FILTER) และ PivotChart เพื่อแสดงวิธีการโต้ตอบที่แตกต่างกัน คุณสามารถเลือกประเภทใดก็ได้ที่เหมาะกับความต้องการของคุณมากที่สุด
- คุณยังสามารถสำรวจการควบคุมแบบฟอร์มซึ่งมีตัวเลือกเชิงโต้ตอบเพิ่มเติมโดยไม่ต้องใช้ VBA
- หากต้องการสร้างช่องค้นหา คุณสามารถใช้ฟังก์ชัน XLOOKUP แบบไดนามิกได้
ดาวน์โหลดสมุดแบบฝึกหัด
บทสรุป
การใช้ฟีเจอร์และเทคนิคเหล่านี้ ช่วยให้คุณสร้างไฟล์ Excel แบบโต้ตอบที่ซับซ้อนได้โดยไม่ต้องใช้ VBA กุญแจสำคัญคือการรวมเทคนิคหลายอย่างเข้าด้วยกันเพื่อการโต้ตอบสูงสุด อย่าลืมทดสอบองค์ประกอบเชิงโต้ตอบของคุณอย่างละเอียดและให้คำแนะนำที่ชัดเจนแก่ผู้ใช้ ด้วยการฝึกฝน คุณจะสามารถสร้างแดชบอร์ดและเครื่องมือที่น่าประทับใจซึ่งทำให้การวิเคราะห์ข้อมูลมีประสิทธิภาพและใช้งานง่าย โดยไม่ต้องเขียนโค้ด VBAแม้แต่บรรทัดเดียว
รับแบบฝึกหัด Excel ขั้นสูงพร้อมโซลูชันฟรี!