Applies ToExcel for Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016 Excel 2013

ส่วนนี้จะอธิบายวิธีการสร้างตัวกรองภายในสูตร Data Analysis Expressions (DAX) คุณสามารถสร้างตัวกรองภายในสูตร เพื่อจํากัดค่าจากแหล่งข้อมูลที่ใช้ในการคํานวณ คุณทําเช่นนี้โดยการระบุตารางเป็นข้อมูลที่ป้อนลงในสูตร แล้วกําหนดนิพจน์ตัวกรอง นิพจน์ตัวกรองที่คุณระบุจะใช้ในการสอบถามข้อมูลและส่งกลับเฉพาะชุดย่อยของข้อมูลต้นฉบับ ตัวกรองจะถูกนําไปใช้แบบไดนามิกในแต่ละครั้งที่คุณอัปเดตผลลัพธ์ของสูตร โดยขึ้นอยู่กับบริบทปัจจุบันของข้อมูลของคุณ

ในบทความนี้

การสร้างตัวกรองในตารางที่ใช้ในสูตร

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

ตัวอย่างเช่น สมมติว่าคุณมีตารางข้อมูลที่มีข้อมูลการสั่งซื้อเกี่ยวกับผู้จําหน่าย และคุณต้องการคํานวณจํานวนตัวแทนจําหน่ายแต่ละรายที่ขายได้ อย่างไรก็ตาม คุณต้องการแสดงยอดขายสําหรับผู้จําหน่ายที่ขายผลิตภัณฑ์ที่มีมูลค่าสูงกว่าหลายหน่วย สูตรต่อไปนี้ซึ่งยึดตามเวิร์กบุ๊กตัวอย่าง DAX จะแสดงตัวอย่างหนึ่งของวิธีที่คุณสามารถสร้างการคํานวณนี้โดยใช้ตัวกรอง

=SUMX(      FILTER ('ResellerSales_USD', 'ResellerSales_USD'[ปริมาณ] > 5 &&      'ResellerSales_USD'[ProductStandardCost_USD] > 100),      'ResellerSales_USD'[SalesAmt]      )

  • ส่วนแรกของสูตรจะระบุหนึ่งใน Power Pivot ฟังก์ชันการรวม ซึ่งใช้ตารางเป็นอาร์กิวเมนต์ SUMX จะคํานวณผลรวมของตาราง

  • ส่วนที่สองของสูตร FILTER(table, expression),จะบอก SUMX ว่าจะใช้ข้อมูลใด SUMX ต้องใช้ตารางหรือนิพจน์ที่ให้ผลลัพธ์ในตาราง ที่นี่ แทนที่จะใช้ข้อมูลทั้งหมดในตาราง ให้คุณใช้ฟังก์ชัน FILTER เพื่อระบุแถวที่จะใช้จากตาราง

    นิพจน์ตัวกรองมีสองส่วน: ส่วนแรกจะตั้งชื่อตารางที่ตัวกรองนําไปใช้ ส่วนที่สองกําหนดนิพจน์ที่จะใช้เป็นเงื่อนไขตัวกรอง ในกรณีนี้คุณกําลังกรองผู้จําหน่ายที่ขายสินค้ามากกว่า 5 หน่วยและผลิตภัณฑ์ที่มีราคามากกว่า $100 ตัวดําเนินการ && เป็นตัวดําเนินการ AND เชิงตรรกะ ซึ่งบ่งชี้ว่าทั้งสองส่วนของเงื่อนไขต้องเป็นจริงสําหรับแถวที่จะเป็นสมาชิกของชุดย่อยที่ถูกกรอง

  • ส่วนที่สามของสูตรจะบอกฟังก์ชัน SUMX ว่าค่าใดที่ควรนํามารวม ในกรณีนี้ คุณกําลังใช้เฉพาะยอดขายเท่านั้น

    โปรดสังเกตว่าฟังก์ชัน เช่น FILTER ซึ่งส่งกลับตาราง จะไม่ส่งกลับตารางหรือแถวโดยตรง แต่จะฝังอยู่ในฟังก์ชันอื่นเสมอ สําหรับข้อมูลเพิ่มเติมเกี่ยวกับ FILTER และฟังก์ชันอื่นๆ ที่ใช้สําหรับการกรอง รวมถึงตัวอย่างเพิ่มเติม ให้ดูที่ ฟังก์ชันตัวกรอง (DAX)

    หมายเหตุ: นิพจน์ตัวกรองจะได้รับผลกระทบจากบริบทที่ใช้ ตัวอย่างเช่น ถ้าคุณใช้ตัวกรองในการวัด และใช้การวัดใน PivotTable หรือ PivotChart ชุดย่อยของข้อมูลที่ส่งกลับอาจได้รับผลกระทบจากตัวกรองหรือตัวแบ่งส่วนข้อมูลที่ผู้ใช้นําไปใช้ใน PivotTable เพิ่มเติม สําหรับข้อมูลเพิ่มเติมเกี่ยวกับบริบท ให้ดูที่ บริบทในสูตร DAX

ตัวกรองที่เอารายการที่ซ้ํากันออก

นอกจากการกรองค่าที่ระบุแล้ว คุณสามารถส่งกลับชุดค่าที่ไม่ซ้ํากันจากตารางหรือคอลัมน์อื่นได้ ซึ่งจะเป็นประโยชน์เมื่อคุณต้องการนับจํานวนค่าที่ไม่ซ้ํากันในคอลัมน์ หรือใช้รายการของค่าที่ไม่ซ้ํากันสําหรับการดําเนินการอื่นๆ DAX มีสองฟังก์ชันสําหรับการส่งกลับค่าที่แตกต่างกัน ได้แก่ ฟังก์ชัน DISTINCT และฟังก์ชัน VALUES

  • ฟังก์ชัน DISTINCT จะตรวจสอบคอลัมน์เดียวที่คุณระบุเป็นอาร์กิวเมนต์ของฟังก์ชัน และส่งกลับคอลัมน์ใหม่ที่มีเฉพาะค่าที่แตกต่างกัน

  • ฟังก์ชัน VALUES ยังส่งกลับรายการของค่าที่ไม่ซ้ํากัน แต่ยังส่งกลับสมาชิกที่ไม่รู้จัก ซึ่งจะเป็นประโยชน์เมื่อคุณใช้ค่าจากสองตารางที่มีความสัมพันธ์รวมกัน และค่าหายไปจากหนึ่งตารางและแสดงในอีกตารางหนึ่ง สําหรับข้อมูลเพิ่มเติมเกี่ยวกับสมาชิกที่ไม่รู้จัก ให้ดู บริบทในสูตร DAX

ฟังก์ชันทั้งสองฟังก์ชันเหล่านี้จะส่งกลับทั้งคอลัมน์ของค่า แต่ฟังก์ชันทั้งสองอย่างจะส่งกลับค่าที่ต่างกัน ดังนั้น คุณจะใช้ฟังก์ชันเพื่อรับรายการของค่าที่ถูกส่งผ่านไปยังฟังก์ชันอื่น ตัวอย่างเช่น คุณสามารถใช้สูตรต่อไปนี้เพื่อรับรายการผลิตภัณฑ์ที่ไม่ซ้ํากันที่ขายโดยผู้จําหน่ายเฉพาะ โดยใช้คีย์ผลิตภัณฑ์ที่ไม่ซ้ํากัน แล้วนับผลิตภัณฑ์ในรายการนั้นโดยใช้ฟังก์ชัน COUNTROWS:

=COUNTROWS(DISTINCT('ResellerSales_USD'[ProductKey]))

ด้านบนของหน้า

บริบทมีผลต่อตัวกรองอย่างไร

เมื่อคุณเพิ่มสูตร DAX ลงใน PivotTable หรือ PivotChart ผลลัพธ์ของสูตรอาจได้รับผลกระทบจากบริบท ถ้าคุณกําลังทํางานในตาราง Power Pivot บริบทจะเป็นแถวปัจจุบันและค่าของแถวนั้น ถ้าคุณกําลังทํางานใน PivotTable หรือ PivotChart บริบทหมายถึงชุดหรือชุดย่อยของข้อมูลที่กําหนดโดยการดําเนินการ เช่น การแบ่งส่วน หรือการกรอง การออกแบบ PivotTable หรือ PivotChart ยังกําหนดบริบทของตนเองอีกด้วย ตัวอย่างเช่น ถ้าคุณสร้าง PivotTable ที่จัดกลุ่มยอดขายตามภูมิภาคและปี เฉพาะข้อมูลที่นําไปใช้กับภูมิภาคและปีเหล่านั้นเท่านั้นที่จะปรากฏใน PivotTable ดังนั้น การวัดใดๆ ที่คุณเพิ่มลงใน PivotTable จะถูกคํานวณในบริบทของส่วนหัวของคอลัมน์และแถวและตัวกรองใดๆ ในสูตรการวัด

สําหรับข้อมูลเพิ่มเติม ให้ดูที่ บริบทในสูตร DAX

ด้านบนของหน้า

การเอาตัวกรองออก

เมื่อทํางานกับสูตรที่ซับซ้อน คุณอาจต้องการทราบว่าตัวกรองปัจจุบันคืออะไร หรือคุณอาจต้องการปรับเปลี่ยนส่วนตัวกรองของสูตร DAX มีฟังก์ชันหลายฟังก์ชันที่ช่วยให้คุณสามารถเอาตัวกรองออก และควบคุมว่าจะเก็บคอลัมน์ใดไว้เป็นส่วนหนึ่งของบริบทตัวกรองปัจจุบัน ส่วนนี้จะให้ภาพรวมของผลกระทบของฟังก์ชันเหล่านี้ต่อผลลัพธ์ในสูตร

การแทนที่ตัวกรองทั้งหมดด้วยฟังก์ชัน ALL

คุณสามารถใช้ฟังก์ชัน ALL เพื่อแทนที่ตัวกรองใดๆ ที่นําไปใช้ก่อนหน้านี้ และส่งกลับแถวทั้งหมดในตารางไปยังฟังก์ชันที่กําลังดําเนินการรวมหรือการดําเนินการอื่นๆ ถ้าคุณใช้คอลัมน์อย่างน้อยหนึ่งคอลัมน์ แทนที่จะใช้เป็นตาราง เพื่อเป็นอาร์กิวเมนต์ใน ALLฟังก์ชัน ALL จะส่งกลับแถวทั้งหมด โดยไม่สนใจตัวกรองบริบทใดๆ

หมายเหตุ: ถ้าคุณคุ้นเคยกับศัพท์ของฐานข้อมูลเชิงสัมพันธ์ คุณสามารถนึกถึง ALL ว่าเป็นการสร้างการรวมภายนอกด้านซ้ายอย่างเป็นธรรมชาติของตารางทั้งหมด

ตัวอย่างเช่น สมมติว่าคุณมีตาราง ยอดขายและผลิตภัณฑ์ และคุณต้องการสร้างสูตรที่จะคํานวณผลรวมของยอดขายสําหรับผลิตภัณฑ์ปัจจุบันที่หารด้วยยอดขายสําหรับผลิตภัณฑ์ทั้งหมด คุณต้องคํานึงถึงข้อเท็จจริงที่ว่า ถ้าใช้สูตรในการวัด ผู้ใช้ PivotTable อาจใช้ตัวแบ่งส่วนข้อมูลเพื่อกรองผลิตภัณฑ์เฉพาะด้วยชื่อผลิตภัณฑ์บนแถว ดังนั้น เมื่อต้องการรับค่าที่แท้จริงของตัวส่วนโดยไม่คํานึงถึงตัวกรองหรือตัวแบ่งส่วนข้อมูล คุณต้องเพิ่มฟังก์ชัน ALL เพื่อแทนที่ตัวกรองใดๆ สูตรต่อไปนี้เป็นตัวอย่างหนึ่งของวิธีใช้ ALL เพื่อแทนที่ผลกระทบของตัวกรองก่อนหน้า:

=SUM (Sales[Amount])/SUMX(Sales[Amount], FILTER(Sales, ALL(Products)))

  • ส่วนแรกของสูตร SUM (Sales[Amount]) จะคํานวณตัวเศษ

  • ผลรวมจะคํานึงถึงบริบทปัจจุบัน ซึ่งหมายความว่าถ้าคุณเพิ่มสูตรลงในคอลัมน์จากการคํานวณ บริบทของแถวจะถูกนําไปใช้ และถ้าคุณเพิ่มสูตรลงใน PivotTable เป็นการวัด ตัวกรองใดๆ ที่นําไปใช้ใน PivotTable (บริบทตัวกรอง) จะถูกนําไปใช้

  • ส่วนที่สองของสูตรจะคํานวณตัวหาร ฟังก์ชัน ALL จะแทนที่ตัวกรองใดๆ ที่อาจนําไปใช้กับตาราง Products

สําหรับข้อมูลเพิ่มเติม รวมถึงตัวอย่างโดยละเอียด ให้ดูที่ ฟังก์ชัน ALL

การแทนที่ตัวกรองเฉพาะด้วยฟังก์ชัน ALLEXCEPT

ฟังก์ชัน ALLEXCEPT ยังแทนที่ตัวกรองที่มีอยู่ด้วย แต่คุณสามารถระบุได้ว่าควรเก็บตัวกรองที่มีอยู่บางตัวไว้ คอลัมน์ที่คุณตั้งชื่อเป็นอาร์กิวเมนต์ของฟังก์ชัน ALLEXCEPT จะระบุว่าคอลัมน์ใดที่จะถูกกรองต่อไป ถ้าคุณต้องการแทนที่ตัวกรองจากคอลัมน์ส่วนใหญ่ แต่ไม่ใช่ทั้งหมด ALLEXCEPT จะสะดวกกว่า ALL ฟังก์ชัน ALLEXCEPT จะมีประโยชน์เป็นพิเศษเมื่อคุณสร้าง PivotTable ที่อาจถูกกรองในหลายคอลัมน์ และคุณต้องการควบคุมค่าที่ใช้ในสูตร สําหรับข้อมูลเพิ่มเติม รวมถึงตัวอย่างโดยละเอียดของวิธีใช้ ALLEXCEPT ใน PivotTable ให้ดูที่ ฟังก์ชัน ALLEXCEPT

ด้านบนของหน้า

ต้องการความช่วยเหลือเพิ่มเติมหรือไม่

ต้องการตัวเลือกเพิ่มเติมหรือไม่

สํารวจสิทธิประโยชน์ของการสมัครใช้งาน เรียกดูหลักสูตรการฝึกอบรม เรียนรู้วิธีการรักษาความปลอดภัยอุปกรณ์ของคุณ และอื่นๆ

ชุมชนช่วยให้คุณถามและตอบคําถาม ให้คําติชม และรับฟังจากผู้เชี่ยวชาญที่มีความรู้มากมาย