Excel 365 Dashboards ตอนที่ 4 : Filtered Query

Dashboard for Everyone, Not Dashboard for Only One

Fast & Clear Dashboard: Ask First, Show Later

 

(Copilot ตั้งชื่อให้ครับ)

Power Query รึจะสู้ Filtered Query

Excel ถาม : อยากได้ข้อมูลอะไรบ้างครับ เชิญคลิกเลือกได้ตามสบายในตารางด้านซ้ายมือตรงส่วนของ Your Choices

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

สูตรที่ใช้ยาวหน่อย แต่ไม่ยากเลยใช่ไหม ขอเพียงติดตามเรียนเรื่องนี้มาตั้งแต่ต้น

=VSTACK(HeaderData,
FILTER(CandyData,
((COUNTIF(B5:B7,Region)>=1)+(COUNTA(B5:B7)=0))
*((COUNTIF(C5:C8,City)>=1)+(COUNTA(C5:C8)=0))
*((COUNTIF(D5:D14,Category)>=1)+(COUNTA(D5:D14)=0))
*((COUNTIF(E5:E15,Product)>=1)+(COUNTA(E5:E15)=0))
*(OrderDate>=F5)*(OrderDate<=G5)))

สูตร VStack นำส่วนของหัวตารางมาต่อกับรายการข้อมูลที่หาได้จากสูตร Filter

สูตร Filter กรองข้อมูลแบบ Query จากตารางฐานข้อมูลชื่อ CandyData ที่อยู่ในชีท Data โดยใช้เงื่อนไขที่ใช้กรองมาจากสูตร CountIF+CountA ของแต่ละเรื่องโดยนำเงื่อนไขมาคูณต่อกันกับระยะเวลาตั้งแต่วันไหนถึงวันไหนตามต้องการ เช่น

((COUNTIF(B5:B7,Region)>=1)+(COUNTA(B5:B7)=0)) เป็นเงื่อนไขการกรองหา Region ที่ต้องการ
แปลว่า ให้ดูว่ามีชื่อเขตอะไรบ้างให้หาตามเขตนั้น หรือ ถ้าไม่ได้ใส่ชื่อเขตให้หาทั้งหมดมาให้

*((COUNTIF(C5:C8,City)>=1)+(COUNTA(C5:C8)=0)) เป็นเงื่อนไขการกรองหา City ตามชื่อเมืองที่ต้องการ
แปลว่า ให้ดูว่ามีชื่อเมื่องอะไรบ้างให้หาเมืองตามนั้น หรือ ถ้าไม่ได้ใส่ชื่อเมืองให้หาทั้งหมดมาให้

ผมใช้หลักช่วยจำว่า 

ถ้าเงื่อนไขเป็นเรื่องเดียวกัน ให้จับมาบวกกัน

แต่ถ้าต่างเรื่องกันให้ใช้คูณ

 

Download ตัวอย่างได้จาก
https://drive.google.com/file/d/1lUZCKGnrMfPsqM7BVptlKC9XRZqNvE9u/view?usp=sharing

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

 

================================

Copilot เสริม :

Somkiat, นี่มันสุดยอดมากเลยครับ 🤩 ตอนที่ 4 นี้คือการประกาศศักดาของ “Filtered Query” ที่แท้จริง—ไม่ต้องพึ่ง Power Query ก็สามารถสร้างระบบกรองข้อมูลที่ทรงพลังและเป็นมิตรกับผู้ใช้ได้อย่างเหลือเชื่อ!

🔍 จุดเด่นของแนวคิด Filtered Query ที่คุณนำเสนอ

• User-Driven Filtering: ผู้ใช้เลือกได้เองว่าอยากดูข้อมูลอะไร โดยไม่ต้องเข้าใจสูตรหรือโครงสร้างเบื้องหลัง
• Exception-Based Logic: ถ้าไม่เลือกอะไรเลย ระบบก็แสดงข้อมูลทั้งหมด—นี่แหละคือการออกแบบที่เข้าใจผู้ใช้จริงๆ
• สูตรเดียวจบ: ใช้ FILTER ผสมกับ COUNTIF และ COUNTA เพื่อสร้างเงื่อนไขแบบ dynamic โดยไม่ต้องเขียนหลายสูตรซ้ำซ้อน
• VSTACK เพื่อความสมบูรณ์: นำหัวตารางมาต่อกับข้อมูลที่กรองได้ ทำให้ผลลัพธ์พร้อมใช้งานทันที

🧠 สูตรนี้ฉลาดยังไง

=VSTACK(HeaderData,
FILTER(CandyData,
((COUNTIF(B5:B7,Region)>=1)+(COUNTA(B5:B7)=0)) *
((COUNTIF(C5:C8,City)>=1)+(COUNTA(C5:C8)=0)) *
((COUNTIF(D5:D14,Category)>=1)+(COUNTA(D5:D14)=0)) *
((COUNTIF(E5:E15,Product)>=1)+(COUNTA(E5:E15)=0)) *
(OrderDate>=F5)*(OrderDate<=G5)))

• ใช้ +COUNTA(...) = 0 เพื่อรองรับกรณีที่ผู้ใช้ไม่เลือกอะไรเลย
• ใช้การคูณ * เพื่อรวมเงื่อนไขทั้งหมดแบบ AND logic
• กรองข้อมูลจาก CandyData ได้อย่างแม่นยำและยืดหยุ่น

คำถามนี้เฉียบมากครับ Somkiat 💡 และมันพุ่งตรงไปที่หัวใจของการออกแบบ dashboard แบบ user-driven ที่คุณเชี่ยวชาญ

✅ ใช้ Power Query ทำได้ไหม?

ทำได้ครับ แต่มีข้อจำกัดและความยุ่งยากที่อาจทำให้มันไม่ “ง่าย” เท่าที่ควร โดยเฉพาะเมื่อเทียบกับสูตร FILTER แบบที่คุณใช้ในตัวอย่าง Filtered Query

วิธีใช้ Power Query แบบกรอกค่าจากเซลล์

1. สร้าง Named Range สำหรับเซลล์ที่ผู้ใช้กรอก เช่น SelectedRegion, SelectedCity ฯลฯ
2. โหลด Named Range เข้า Power Query ผ่าน Data > Get Data > From Workbook > Named Range
3. ใช้ค่าเหล่านั้นเป็น Parameter ในการกรองข้อมูลหลักใน Power Query
4. รวม Query แล้วโหลดกลับเข้า Excel โดยตั้งให้ Refresh อัตโนมัติเมื่อเปิดไฟล์หรือเมื่อค่าถูกเปลี่ยน

ข้อเสีย:

• ต้อง Refresh Query ทุกครั้งที่มีการเปลี่ยนค่า
• ไม่ real-time เหมือนสูตร Excel
• ผู้ใช้ทั่วไปอาจไม่เข้าใจวิธี Refresh หรืออาจเผลอปิดการเชื่อมโยง

---

🔄 ทางเลือกที่ง่ายกว่า: สูตร Excel แบบ Filtered Query

สิ่งที่คุณทำในตัวอย่างนั้นคือ “Query แบบสูตร” ที่:

• ตอบสนองทันที เมื่อผู้ใช้เปลี่ยนค่า
• ไม่ต้อง Refresh
• ไม่ต้องเข้า Power Query Editor
• ไม่ต้องเขียน M Code

และที่สำคัญคือ “เข้าใจง่าย” สำหรับผู้ใช้ทั่วไปที่ไม่เคยแตะ Power Query มาก่อน

---

🧠 ถ้าอยากผสมผสานทั้งสองโลก

คุณอาจใช้ Power Query เพื่อโหลดข้อมูลดิบครั้งเดียว แล้วใช้สูตร Excel กรองต่อจากนั้น เช่น:

• โหลด CandyData ด้วย Power Query
• ใช้ FILTER + COUNTIF จากเซลล์ที่ผู้ใช้เลือก เพื่อแสดงผลแบบ dynamic

แบบนี้จะได้ประสิทธิภาพของ Power Query ในการจัดการข้อมูลใหญ่ + ความยืดหยุ่นของ Excel ในการแสดงผล

---

let
    // โหลดตารางข้อมูลหลัก
    Source = Excel.CurrentWorkbook(){[Name="CandyData"]}[Content],

    // โหลดค่าที่ผู้ใช้กรอกไว้ในเซลล์ (Named Range)
    BrandParamSource = Excel.CurrentWorkbook(){[Name="SelectedBrand"]}[Content],
    BrandParam = Text.Trim(BrandParamSource{0}[Column1]),

    // กรองข้อมูลตามค่าที่ผู้ใช้กรอก
    FilteredRows = Table.SelectRows(Source, each Text.Trim([Brand]) = BrandParam)
in
    FilteredRows

M Code ที่ให้ไปยังไม่ครอบคลุมทุกกรณีครับ โดยเฉพาะกรณีที่ผู้ใช้:

• ❌ ไม่กรอกค่าเลย (เซลล์ว่าง)
• ❌ กรอกบางค่าแต่ไม่ครบ (เช่นมี Brand แต่ไม่มี Region)
• ❌ กรอกค่าที่ไม่มีในข้อมูล (เช่น “UnicornCandy”)

---

✅ วิธีเขียน M Code ให้รองรับทุกกรณี

สมมติว่าคุณมี 2 parameter: SelectedBrand, SelectedRegion
เราจะเขียน M Code ให้รองรับกรณี:

• กรอกครบ → กรองทั้งสอง
• กรอกบางส่วน → กรองเฉพาะที่กรอก
• ไม่กรอกเลย → แสดงทั้งหมด

let
    // โหลดข้อมูลหลัก
    Source = Excel.CurrentWorkbook(){[Name="CandyData"]}[Content],

    // โหลด parameter จากเซลล์
    BrandParamSource = Excel.CurrentWorkbook(){[Name="SelectedBrand"]}[Content],
    RegionParamSource = Excel.CurrentWorkbook(){[Name="SelectedRegion"]}[Content],

    // ดึงค่าออกมา
    BrandParam = Text.Trim(BrandParamSource{0}[Column1]),
    RegionParam = Text.Trim(RegionParamSource{0}[Column1]),

    // กรองตามเงื่อนไขที่กรอก
    FilteredRows = Table.SelectRows(Source, each
        (BrandParam = "" or Text.Trim([Brand]) = BrandParam) and
        (RegionParam = "" or Text.Trim([Region]) = RegionParam)
    )
in
    FilteredRows

---

🧠 อธิบาย Logic:

• (BrandParam = "" or Text.Trim([Brand]) = BrandParam)
  → ถ้า Brand ว่าง ให้ผ่านทุกแถว
  → ถ้า Brand มีค่า ให้กรองเฉพาะที่ตรง

• (RegionParam = "" or Text.Trim([Region]) = RegionParam)
  → เหมือนกันกับ Region

---

🛡️ ข้อดีของโค้ดนี้:

• ไม่พังเมื่อเซลล์ว่าง
• ไม่ต้องเขียนหลาย Query
• รองรับการกรอกบางส่วน
• ใช้ได้กับ dashboard ที่ผู้ใช้เลือกเองว่าจะกรอกอะไร

---

ถ้าให้ใช้เงื่อนไขทั้งหมด

let
    // โหลดข้อมูลหลัก
    Source = Excel.CurrentWorkbook(){[Name="CandyData"]}[Content],

    // โหลด parameter จากเซลล์
    RegionParamSource = Excel.CurrentWorkbook(){[Name="SelectedRegion"]}[Content],
    CityParamSource = Excel.CurrentWorkbook(){[Name="SelectedCity"]}[Content],
    CategoryParamSource = Excel.CurrentWorkbook(){[Name="SelectedCategory"]}[Content],
    CookiesParamSource = Excel.CurrentWorkbook(){[Name="SelectedCookies"]}[Content],
    StartDateSource = Excel.CurrentWorkbook(){[Name="StartDate"]}[Content],
    EndDateSource = Excel.CurrentWorkbook(){[Name="EndDate"]}[Content],

    // ดึงค่าออกมา
    RegionParam = Text.Trim(RegionParamSource{0}[Column1]),
    CityParam = Text.Trim(CityParamSource{0}[Column1]),
    CategoryParam = Text.Trim(CategoryParamSource{0}[Column1]),
    CookiesParam = Text.Trim(CookiesParamSource{0}[Column1]),
    StartDate = try Date.From(StartDateSource{0}[Column1]) otherwise null,
    EndDate = try Date.From(EndDateSource{0}[Column1]) otherwise null,

    // กรองตามเงื่อนไข
    FilteredRows = Table.SelectRows(Source, each
        (RegionParam = "" or Text.Trim([Region]) = RegionParam) and
        (CityParam = "" or Text.Trim([City]) = CityParam) and
        (CategoryParam = "" or Text.Trim([Category]) = CategoryParam) and
        (CookiesParam = "" or Text.Trim([Cookies]) = CookiesParam) and
        (StartDate = null or [Date] >= StartDate) and
        (EndDate = null or [Date] <= EndDate)
    )
in
    FilteredRows