ในบทความนี้ เราจะมาดูที่พื้นฐานการสร้างสูตรการคํานวณสําหรับทั้ง คอลัมน์จากการคํานวณ และ การวัด ใน Power Pivot ถ้าคุณเพิ่งเริ่มใช้ DAX อย่าลืมดูการเริ่มต้นใช้งานด่วน: เรียนรู้ข้อมูลพื้นฐานของ DAX ใน 30 นาที
ข้อมูลพื้นฐานเกี่ยวกับสูตร
Power Pivot มี Data Analysis Expressions (DAX) สําหรับการสร้างการคํานวณแบบกําหนดเองในตาราง Power Pivot และใน PivotTable ของ Excel DAX มีฟังก์ชันบางอย่างที่ใช้ในสูตร Excel และฟังก์ชันเพิ่มเติมที่ออกแบบมาเพื่อทํางานกับข้อมูลที่สัมพันธ์กันและดําเนินการรวมแบบไดนามิก
ต่อไปนี้คือสูตรพื้นฐานบางสูตรที่สามารถใช้ในคอลัมน์จากการคํานวณได้
| สูตร | คำอธิบาย |
|---|---|
| =TODAY() | แทรกวันที่ของวันนี้ในทุกแถวของคอลัมน์ |
| =3 | แทรกค่า 3 ในทุกแถวของคอลัมน์ |
| =[คอลัมน์ 1] + [คอลัมน์ 2] | เพิ่มค่าในแถวเดียวกันของ [คอลัมน์ 1] และ [คอลัมน์ 2] และใส่ผลลัพธ์ในแถวเดียวกันของคอลัมน์จากการคํานวณ |
คุณสามารถสร้างสูตร Power Pivot สําหรับคอลัมน์จากการคํานวณได้มากเท่ากับที่คุณสร้างสูตรใน Microsoft Excel
ใช้ขั้นตอนต่อไปนี้เมื่อคุณสร้างสูตร:
- แต่ละสูตรต้องเริ่มต้นด้วยเครื่องหมายเท่ากับ
- คุณสามารถพิมพ์หรือเลือกชื่อฟังก์ชัน หรือพิมพ์นิพจน์
- เริ่มพิมพ์ตัวอักษรสองสามตัวแรกของฟังก์ชันหรือชื่อที่คุณต้องการ และการทําให้สมบูรณ์อัตโนมัติจะแสดงรายการฟังก์ชัน ตาราง และคอลัมน์ที่พร้อมใช้งาน กด TAB เพื่อเพิ่มรายการจากรายการ การทําให้สมบูรณ์อัตโนมัติ ลงในสูตร
- คลิกปุ่ม Fx เพื่อแสดงรายการฟังก์ชันที่พร้อมใช้งาน เมื่อต้องการเลือกฟังก์ชันจากรายการดรอปดาวน์ ให้ใช้แป้นลูกศรเพื่อเน้นรายการ แล้วคลิก ตกลง เพื่อเพิ่มฟังก์ชันลงในสูตร
- ใส่อาร์กิวเมนต์ลงในฟังก์ชันโดยการเลือกจากรายการดรอปดาวน์ของตารางและคอลัมน์ที่เป็นไปได้ หรือโดยการพิมพ์ค่าหรือฟังก์ชันอื่น
- ตรวจสอบข้อผิดพลาดทางไวยากรณ์: ตรวจสอบให้แน่ใจว่าวงเล็บทั้งหมดถูกปิด และคอลัมน์ ตาราง และค่าถูกอ้างอิงอย่างถูกต้อง
- กด ENTER เพื่อยอมรับสูตร
หมายเหตุ
ในคอลัมน์จากการคํานวณ ทันทีที่คุณยอมรับสูตร คอลัมน์จะถูกเติมด้วยค่า ในการวัด การกด ENTER จะบันทึกข้อกําหนดการวัด
สร้างสูตรอย่างง่าย
| เมื่อต้องการสร้างคอลัมน์จากการคํานวณด้วยสูตรอย่างง่าย วันที่ขายSubcategoryProductSalesQuantity1/5/2009AccessoriesCarrying Case254995681/5/2009AccessoriesMini ที่ชาร์จแบตเตอรี่ 1099.56441/5/2009DigitalSlim Digital65 12441/6/2009AccessoriesTelephoto Conversion Lens1662.5181/6/2009AccessoriesTripod938.34181/6/2009 สาย AccessoriesUSB1230.2526
|
|---|
เคล็ดลับสําหรับการใช้การทําให้สมบูรณ์อัตโนมัติ
- คุณสามารถใช้ การทําให้สูตรสมบูรณ์อัตโนมัติ ที่อยู่ตรงกลางของสูตรที่มีอยู่กับฟังก์ชันที่ซ้อนกันได้ ข้อความที่อยู่ก่อนจุดแทรกจะถูกใช้เพื่อแสดงค่าในรายการดรอปดาวน์ และข้อความทั้งหมดหลังจากจุดแทรกจะยังคงไม่เปลี่ยนแปลง
- Power Pivot จะไม่เพิ่มวงเล็บปิดของฟังก์ชันหรือจับคู่วงเล็บโดยอัตโนมัติ คุณต้องตรวจสอบให้แน่ใจว่าแต่ละฟังก์ชันถูกต้องตามหลักไวยากรณ์ หรือคุณไม่สามารถบันทึกหรือใช้สูตรได้ Power Pivot จะเน้นวงเล็บ ซึ่งทําให้ง่ายต่อการตรวจสอบว่าวงเล็บปิดอยู่หรือไม่
การทํางานกับตารางและคอลัมน์
ตาราง Power Pivot มีลักษณะคล้ายกับตาราง Excel แต่แตกต่างกันในแบบทํางานกับข้อมูลและสูตร:
- สูตรใน Power Pivot จะทํางานกับตารางและคอลัมน์เท่านั้น ไม่ใช่กับแต่ละเซลล์ การอ้างอิงช่วง หรืออาร์เรย์
- สูตรสามารถใช้ความสัมพันธ์เพื่อรับค่าจากตารางที่เกี่ยวข้อง ค่าที่ดึงมาจะสัมพันธ์กับค่าแถวปัจจุบันเสมอ
- คุณไม่สามารถวางสูตร Power Pivot ลงในเวิร์กชีต Excel และในทางกลับกันได้
- คุณไม่สามารถมีข้อมูลที่ไม่สม่ําเสมอหรือ "ไม่สม่ําเสมอ" ได้ เหมือนกับที่คุณทําในเวิร์กชีต Excel แต่ละแถวในตารางต้องมีจํานวนคอลัมน์เท่ากัน อย่างไรก็ตาม คุณสามารถมีค่าว่างในบางคอลัมน์ได้ ตารางข้อมูล Excel และตารางข้อมูล Power Pivot ไม่สามารถสลับกันได้ แต่คุณสามารถลิงก์ไปยังตาราง Excel จาก Power Pivot และวางข้อมูล Excel ลงใน Power Pivot ได้ สําหรับข้อมูลเพิ่มเติม ให้ดู เพิ่มข้อมูลเวิร์กชีตลงในตัวแบบข้อมูลโดยใช้ตารางที่ลิงก์ และ คัดลอกและวางแถวลงในตัวแบบข้อมูลใน Power Pivot
การอ้างถึงตารางและคอลัมน์ในสูตรและนิพจน์
คุณสามารถอ้างอิงถึงตารางและคอลัมน์ใดๆ โดยใช้ชื่อของตารางและคอลัมน์ ตัวอย่างเช่น สูตรต่อไปนี้แสดงวิธีการอ้างอิงไปยังคอลัมน์จากสองตารางโดยใช้ชื่อแบบเต็ม:
=SUM('New Sales'[Amount]) + SUM('Past Sales'[Amount])
เมื่อสูตรถูกประเมินแล้ว Power Pivot จะตรวจสอบไวยากรณ์ทั่วไปก่อน แล้วจึงตรวจสอบชื่อของคอลัมน์และตารางที่คุณระบุในคอลัมน์และตารางที่เป็นไปได้ในบริบทปัจจุบัน ถ้าชื่อไม่ชัดเจน หรือถ้าไม่พบคอลัมน์หรือตาราง คุณจะได้รับข้อผิดพลาดในสูตรของคุณ (สตริง #ERROR แทนที่จะเป็นค่าข้อมูลในเซลล์ที่มีข้อผิดพลาดเกิดขึ้น) สําหรับข้อมูลเพิ่มเติมเกี่ยวกับข้อกําหนดในการตั้งชื่อสําหรับตาราง คอลัมน์ และวัตถุอื่นๆ ให้ดูที่ "ข้อกําหนดการตั้งชื่อในข้อกําหนดไวยากรณ์ DAX สําหรับ Power Pivot
หมายเหตุ
บริบทเป็นฟีเจอร์ที่สําคัญของตัวแบบข้อมูล Power Pivot ที่ช่วยให้คุณสร้างสูตรแบบไดนามิกได้ บริบทจะถูกกําหนดโดยตารางในตัวแบบข้อมูล ความสัมพันธ์ระหว่างตาราง และตัวกรองใดๆ ที่ถูกนําไปใช้ สําหรับข้อมูลเพิ่มเติม ให้ดูที่ บริบทในสูตร DAX
ความสัมพันธ์ของตาราง
ตารางสามารถสัมพันธ์กับตารางอื่นได้ ด้วยการสร้างความสัมพันธ์ คุณจะได้รับความสามารถในการค้นหาข้อมูลในตารางอื่นและใช้ค่าที่เกี่ยวข้องเพื่อทําการคํานวณที่ซับซ้อน ตัวอย่างเช่น คุณสามารถใช้คอลัมน์จากการคํานวณเพื่อค้นหาเรกคอร์ดการจัดส่งทั้งหมดที่เกี่ยวข้องกับผู้จําหน่ายปัจจุบัน แล้วรวมต้นทุนการจัดส่งสําหรับแต่ละรายการ เอฟเฟ็กต์จะเหมือนกับคิวรีที่มีพารามิเตอร์ คุณสามารถคํานวณผลรวมที่แตกต่างกันสําหรับแต่ละแถวในตารางปัจจุบันได้
ฟังก์ชัน DAX จํานวนมากจําเป็นต้องมีความสัมพันธ์อยู่ระหว่างตาราง หรือระหว่างหลายตาราง เพื่อระบุตําแหน่งคอลัมน์ที่คุณอ้างอิงและส่งกลับผลลัพธ์ที่เหมาะสม ฟังก์ชันอื่นๆ จะพยายามระบุความสัมพันธ์ อย่างไรก็ตาม เพื่อผลลัพธ์ที่ดีที่สุด คุณควรสร้างความสัมพันธ์ที่เป็นไปได้เสมอ
เมื่อคุณทํางานกับ PivotTable เป็นสิ่งสําคัญอย่างยิ่งที่คุณต้องเชื่อมต่อตารางทั้งหมดที่ใช้ใน PivotTable เพื่อให้สามารถคํานวณข้อมูลสรุปได้อย่างถูกต้อง สําหรับข้อมูลเพิ่มเติม ให้ดูที่ ทํางานกับความสัมพันธ์ใน PivotTable
การแก้ไขปัญหาข้อผิดพลาดในสูตร
ถ้าคุณได้รับข้อผิดพลาดเมื่อคุณกําลังกําหนดคอลัมน์จากการคํานวณ สูตรอาจมีข้อผิดพลาดทางไวยากรณ์หรือข้อผิดพลาดทางความหมาย
ข้อผิดพลาดทางไวยากรณ์เป็นวิธีที่ง่ายที่สุดในการแก้ไข โดยปกติแล้วจะมีวงเล็บหรือเครื่องหมายจุลภาคหายไป สําหรับความช่วยเหลือเกี่ยวกับไวยากรณ์ของแต่ละฟังก์ชัน ให้ดูที่ การอ้างอิงฟังก์ชัน DAX
ข้อผิดพลาดชนิดอื่นจะเกิดขึ้นเมื่อไวยากรณ์ถูกต้อง แต่ค่าหรือคอลัมน์ที่อ้างอิงไม่เหมาะสมในบริบทของสูตร ข้อผิดพลาดทางความหมายดังกล่าวอาจเกิดจากปัญหาใดๆ ต่อไปนี้:
- สูตรอ้างอิงไปยังคอลัมน์ ตาราง หรือฟังก์ชันที่ไม่มีอยู่
- สูตรดูเหมือนถูกต้อง แต่เมื่อ Power Pivot ดึงข้อมูลพบชนิดที่ไม่ตรงกัน และทําให้เกิดข้อผิดพลาด
- สูตรส่งผ่านตัวเลขหรือชนิดของพารามิเตอร์ที่ไม่ถูกต้องไปยังฟังก์ชัน
- สูตรอ้างอิงไปยังคอลัมน์อื่นที่มีข้อผิดพลาด ดังนั้นค่าของคอลัมน์จึงไม่ถูกต้อง
- สูตรอ้างอิงไปยังคอลัมน์ที่ยังไม่ถูกประมวลผล ซึ่งอาจเกิดขึ้นได้ถ้าคุณเปลี่ยนเวิร์กบุ๊กเป็นโหมดด้วยตนเอง ทําการเปลี่ยนแปลง แล้วไม่รีเฟรชข้อมูลหรืออัปเดตการคํานวณ
ในสี่กรณีแรก DAX จะตั้งค่าสถานะทั้งคอลัมน์ที่มีสูตรที่ไม่ถูกต้อง ในกรณีสุดท้าย DAX จะเป็นสีเทาออกจากคอลัมน์เพื่อระบุว่าคอลัมน์อยู่ในสถานะยังไม่ได้ประมวลผล