Thep Excel
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | ค่าเริ่มต้น | คำอธิบาย |
|---|---|---|---|
| Position | number | ตำแหน่งแถวที่ต้องการดึง (1-based integer) ค่าบวก = นับจากแรก (1 = แถวแรก) ค่าลบ = นับจากท้าย (-1 = แถวสุดท้าย) ถ้ากำหนด 0 หรือ BLANK() จะคืนตารางว่าง | |
| [Relation]ไม่บังคับ | table | ALLSELECTED() ของคอลัมน์ OrderBy/PartitionBy | ตารางหรือ Table Expression ที่ต้องการดึงแถว ถ้าไม่ระบุจะใช้ ALLSELECTED() ของคอลัมน์ใน OrderBy และ PartitionBy |
| [OrderBy]ไม่บังคับ | orderby | ไม่ระบุ | นิพจน์ ORDERBY() เพื่อกำหนดลำดับของแถว มีรูปแบบ ORDERBY(Column1, ASC/DESC, Column2, ASC/DESC, …) ถ้าไม่ระบุ Relation จะต้องระบุ OrderBy |
| [PartitionBy]ไม่บังคับ | partitionby | ไม่ระบุ (ถือว่า 1 partition) | นิพจน์ PARTITIONBY() เพื่อแบ่งข้อมูลเป็นกลุ่ม INDEX จะดึงตำแหน่งภายในแต่ละกลุ่ม ถ้าไม่ระบุจะถือว่าข้อมูลทั้งหมดเป็น 1 กลุ่ม |
| [MatchBy]ไม่บังคับ | matchby | ไม่ระบุ | นิพจน์ MATCHBY() สำหรับระบุคอลัมน์ที่ใช้ในการแมตช์เพื่อระบุแถวปัจจุบัน ใช้ในงาน Visual Calculations |
| [Blanks]ไม่บังคับ | text | DEFAULT | วิธีจัดการค่า BLANK() ใน OrderBy: DEFAULT (เริ่มต้น), FIRST (ค่า BLANK มาแรก), LAST (ค่า BLANK มาท้าย) |
| [Reset]ไม่บังคับ | text | ไม่ระบุ | ควบคุมการ Reset ที่ hierarchy level ต่างๆ ใช้ใน Visual Calculations เท่านั้น |
[ ] = อาร์กิวเมนต์ที่ไม่บังคับ
Additional Notes
INDEX เป็นฟังก์ชันที่ใช้ดึง “แถวที่ตำแหน่ง N” (เช่นแถวที่ 1, 2, 3 หรือแม้แต่แถวสุดท้าย) จากตารางหรือชุดข้อมูลที่กำหนด ลักษณะเด่นคือมันคืนตารางแถวเดี่ยว ไม่ใช่ค่าเดี่ยว ดังนั้นต้องใช้ iterator เช่น MINX, MAXX หรือ SELECTCOLUMNS เพื่อดึงค่าออกมา
ที่เจ๋งคือ INDEX ทำให้เราควบคุม “ลำดับ” ได้อย่างละเอียด ผ่าน ORDERBY และ PARTITIONBY ตัวอย่างเช่น อยากเปรียบเทียบยอดขายเดือนนี้กับเดือนที่แล้ว? ใช้ INDEX กับ ORDERBY ดึงช่วงเวลาก่อนหน้า แล้วใช้ CALCULATE เปรียบเทียบได้เลย หรืออยากหาผ่านแต่ละหมวดสินค้า? เพิ่ม PARTITIONBY ให้มันแบ่งกลุ่มก่อน
ส่วนตัวผม INDEX คือ Secret Weapon ของ Window Functions ครับ มันช่วยให้งาน Advanced Analytics เหมือน Ranking, Moving Average, YoY Comparison ทำได้ยุ่งเหยิง แถมเขียนง่ายกว่า Self-Join แบบเก่า ผมเลยชอบใช้ INDEX ตั้งแต่เคยรู้จักมัน 😎
How it works
ดึงวันที่แรกในช่วงที่เลือกด้วยการจัดลำดับ
ใช้ INDEX(1, …) เพื่อดึงแถวแรกจากชุดวันที่ที่เลือก
หาอันดับที่ N ภายในกลุ่ม
ใช้ PARTITIONBY เพื่อดึงแถวตำแหน่ง N ในแต่ละกลุ่ม
Examples
ตัวอย่างที่ 1: ดึงเดือนแรกในช่วงการเลือก (Basic)เดือนแรก = VAR FirstMonth = INDEX( 1, ALLSELECTED(Date[MonthName]), ORDERBY(Date[MonthSequence], ASC) ) RETURN MINX(FirstMonth, Date[MonthName])
INDEX คืนตาราง 1 แถว ใช้ MINX เพื่อดึงค่า Date[MonthName] ออกมาเป็นข้อความ Position=1 หมายถึงแถวแรกตามลำดับ ORDERBY
ตัวอย่างที่ 2: ดึงยอดขายจากเดือนสุดท้าย (Negative Position)ยอดขายเดือนสุดท้าย = VAR LastMonthSales = CALCULATE( [Total Sales], INDEX( -1, ORDERBY(Date[DateKey], DESC) ) ) RETURN LastMonthSales
Position = -1 เลือกแถวสุดท้าย CALCULATE ใช้ Relation ที่ INDEX คืนมา เพื่อคำนวณ [Total Sales] ในบริบทนั้น
ตัวอย่างที่ 3: อันดับสูงสุดต่อหมวดสินค้า (PARTITIONBY)ยอดขายอันดับ 1 ต่อหมวด = VAR Top1PerCategory = INDEX( 1, SUMMARIZECOLUMNS( Product[Category], Product[ProductID], "Sales", [Total Sales] ), ORDERBY([Sales], DES…
PARTITIONBY แบ่งให้ SUMMARIZECOLUMNS เป็นกลุ่มตาม Category INDEX เลือก Position 1 (สูงสุด) ภายในแต่ละกลุ่ม ORDERBY [Sales] DESC เรียงจากมากไปน้อย
ตัวอย่างที่ 4: เปรียบเทียบกับเดือนก่อนหน้า (YoY Pattern)เปรียบเทียบเดือนก่อน = VAR PreviousMonth = CALCULATE( [Total Sales], INDEX( -2, ORDERBY(Date[MonthSequence], DESC) ) ) VAR ThisMonth = [Total Sales] RETURN IF(I…
Position = -2 ดึงแถวที่ 2 จากท้าย (เดือนก่อนหน้า) เมื่อเทียบกับเดือนปัจจุบัน IF ป้องกัน BLANK ในช่วงเริ่มต้น
FAQs
INDEX คืนค่าเป็นตารางหรือค่าเดี่ยว?+
INDEX คืนค่าเป็นตาราง (row table) ที่มี 1 แถว ถ้าต้องการค่าเดี่ยวให้ใช้ iterator เช่น MINX, MAXX, CONCATENATEX หรือ SELECTCOLUMNS เพื่อดึงคอลัมน์ที่ต้องการออกมา การใช้ INDEX ใน CALCULATE โดยตรงจะทำให้ filter context ของตาราง row นั้น ใช้ได้
INDEX กับ OFFSET ต่างกันอย่างไร?+
INDEX ดึง "ตำแหน่งแบบสัมบูรณ์" (เช่นแถวที่ 3 หรือแถวสุดท้าย) ส่วน OFFSET ดึง "ตำแหน่งแบบสัมพัทธ์" (เช่น N แถวหลังจากแถวปัจจุบัน) INDEX ใช้ ORDERBY/PARTITIONBY ส่วน OFFSET ใช้ MATCHBY เพื่อระบุจุดเริ่มต้น นอกจากนี้ INDEX คืนตารางทั้งแถว ส่วน OFFSET อาจคืนค่าเดี่ยวได้
INDEX คืนตารางว่าง (empty table) เมื่อไร?+
เมื่อ Position = 0, Position = BLANK(), หรือ Position เลยนอกช่วง (เช่น Position = 100 แต่มีแถวแค่ 50) INDEX จะคืนตารางว่าง ผมแนะนำให้ใช้ ISBLANK() หรือ COALESCE() เพื่อตรวจสอบและแสดงค่าเริ่มต้นแทน
PARTITIONBY จำเป็นไหม?+
ไม่จำเป็น ถ้าไม่ระบุ PARTITIONBY INDEX จะถือว่าข้อมูลทั้งหมดเป็น 1 partition ใช้ PARTITIONBY เมื่อต้องการดึงตำแหน่งภายในแต่ละกลุ่ม เช่น ผ่านแต่ละหมวดสินค้า/แต่ละอาคม/แต่ละลูกค้า
INDEX จำเป็นต้องใช้ ORDERBY ไหม?+
ไม่จำเป็น ถ้าไม่ระบุ ORDERBY INDEX จะใช้ลำดับเริ่มต้นของตารางทีเลือก แต่ส่วนมากจะระบุ ORDERBY เพื่อควบคุมลำดับให้แน่นอน ผมแนะนำให้ระบุ ORDERBY เสมอเพื่อหลีกเลี่ยงข้อผิดพลาด
INDEX ใช้ได้กับ Calculated Column ไหม?+
ได้ แต่ต้องระวัง INDEX ใน Calculated Column ของตาราง Fact มักจะสูญเสีย Filter Context ที่ Row Level ดังนั้นจึงต้องใช้ ALLSELECTED() หรือ CALCULATETABLE() เพื่อสร้าง Relation ที่ถูกต้อง
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ INDEX จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
MATCHBY ระบุคอลัมน์ที่ใช้จับคู่และระบุแถวปัจจุบันใน window functions เพื่อให้การเลื่อน/เลือกตำแหน่งแถวทำงานถูกต้องเมื่อมีค่า ORDERBY ซ้ำ หรือเมื่อจำเป็นต้องกำหนดเอกลักษณ์ของแถว
ORDERBY ระบุลำดับการเรียงลำดับ (ASC/DESC) และการจัดการค่าว่าง สำหรับ window functions เช่น INDEX, OFFSET, WINDOW เพื่อกำหนดลำดับของแถวภายในกลุ่ม
PARTITIONBY ระบุคอลัมน์ที่ใช้แบ่งกลุ่มสำหรับ window functions เพื่อให้การเลื่อน/เลือกตำแหน่งเกิดขึ้น “ภายในกลุ่ม” ตามที่กำหนด มักใช้ร่วมกับ ORDERBY และ MATCHBY
WINDOW ดึงช่วงแถวภายในพาร์ทิชัน คืนค่าเป็นตาราง เหมาะกับการคำนวณแบบช่วง เช่น moving average, running sum, หรือ lead/lag analysis
FIRST ดึงค่าจากแถวแรก (First Row) บนแกน (Axis) ที่ระบุในการคำนวณภาพ (Visual Calculations) ส่งกลับค่าที่อยู่ตำแหน่งแรกของแกน ใช้เพื่อเปรียบเทียบค่าปัจจุบัน กับค่าต้นตัวของลำดับ หรือดึงมูลค่าเริ่มต้น
LAST ดึงค่าจากแถวสุดท้าย (Last Row) บนแกน (Axis) ที่ระบุในการคำนวณภาพ (Visual Calculations) ส่งกลับค่าที่อยู่ตำแหน่งสุดท้ายของแกน ใช้เพื่อดึงค่าปิด (Closing Value) หรือเปรียบเทียบค่าปัจจุบัน กับค่าสิ้นสุด
คืนตารางที่เป็นผลแบบ semi-join ระหว่าง 2 ตาราง พร้อมแทนคอลัมน์ร่วมด้วยดัชนี 0-based ซึ่งคำนวณจากลำดับแถวของตารางอ้างอิงหลังจัดเรียง
ยังไม่มีบทความที่เกี่ยวข้องกับฟังก์ชันนี้
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่