Dashboard for Everyone, Not Dashboard for Only OneFast & Clear Dashboard: Ask First, Show Later
(Copilot ตั้งชื่อให้ครับ)
Power Query รึจะสู้ Filtered QueryExcel ถาม : อยากได้ข้อมูลอะไรบ้างครับ เชิญคลิกเลือกได้ตามสบายในตารางด้านซ้ายมือตรงส่วนของ 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 ตามชื่อเมืองที่ต้องการ
แปลว่า ให้ดูว่ามีชื่อเมื่องอะไรบ้างให้หาเมืองตามนั้น หรือ ถ้าไม่ได้ใส่ชื่อเมืองให้หาทั้งหมดมาให้
ผมใช้หลักช่วยจำว่า
ถ้าเงื่อนไขเป็นเรื่องเดียวกัน ให้จับมาบวกกัน
แต่ถ้าต่างเรื่องกันให้ใช้คูณ
================================
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 แบบกรอกค่าจากเซลล์
- สร้าง Named Range สำหรับเซลล์ที่ผู้ใช้กรอก เช่น
SelectedRegion, SelectedCity ฯลฯ - โหลด Named Range เข้า Power Query ผ่าน
Data > Get Data > From Workbook > Named Range - ใช้ค่าเหล่านั้นเป็น Parameter ในการกรองข้อมูลหลักใน Power Query
- รวม 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:
🛡️ ข้อดีของโค้ดนี้:
- ไม่พังเมื่อเซลล์ว่าง
- ไม่ต้องเขียนหลาย 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
