ใช้ OFFSET เพื่ออ้างอิงไฮเปอร์บล็อก

หัวข้อนี้อธิบายฟังก์ชัน OFFSET ซึ่งหมายถึงรายงานตัวอย่าง RP006 ในแอปพลิเคชันตัวอย่าง

ฟังก์ชัน OFFSET ไม่ได้เจาะจงเฉพาะกับ Application Studio แต่มีประโยชน์อย่างยิ่งเมื่อทำงานกับไฮเปอร์บล็อก เนื่องจากในโหมดมุมมอง ไฮเปอร์บล็อกจะเป็นแบบไดนามิก ในโหมดการออกแบบ ไฮเปอร์บล็อกจะคงที่ นั่นคือมันจะกินพื้นที่ช่วงของเซลล์อย่างเฉพาะเจาะจง แต่ในโหมดมุมมอง ไฮเปอร์บล็อกจะขยายและหดตัวไปตามเนื้อหา ทำให้การอ้างอิงเซลล์เฉพาะไม่มีความหมาย ตัวอย่างเช่น ถ้าคุณมีสูตรนอกไฮเปอร์บล็อกซึ่งต้องอ้างอิงเซลล์ภายในไฮเปอร์บล็อก คุณจำเป็นต้องใช้สูตรแบบไดนามิก

ด้วยการใช้ OFFSET คุณจะระบุเซลล์โดยใช้วิธีระบุตำแหน่งที่สัมพันธ์กับเซลล์อ้างอิงเริ่มต้น คุณระบุตำแหน่งของมันด้วยพิกัดที่แสดงถึงระยะทาง ในแง่ของจำนวนแถวและคอลัมน์นับจากเซลล์อ้างอิงเริ่มต้น

ตัวอย่างนี้อ้างอิงเซลล์ C13:

=OFFSET(F11,2,-3)

นั่นคือ เซลล์ F11 เป็นเซลล์อ้างอิงเริ่มต้น อาร์กิวเมนต์ที่สองคือ 2 ระบุว่าเซลล์ที่อ้างอิงอยู่ด้านล่าง F11 ลงไปสองแถว อาร์กิวเมนต์ที่สามคือ 3 ระบุว่าเซลล์ที่อ้างอิงคือ 3 คอลัมน์ทางด้านซ้ายมือของ F11

ตัวอย่างนี้อ้างอิงเซลล์ C13 เช่นกัน:

=OFFSET(B21,-8,1)

นั่นคือ เซลล์ B21 เป็นเซลล์อ้างอิงเริ่มต้น อาร์กิวเมนต์ที่สอง คือ -8 บ่งชี้ว่าเซลล์ที่อ้างอิงอยู่เหนือ B21 ขึ้นไป 8 แถว อาร์กิวเมนต์ที่สามคือ 1 ระบุว่าเซลล์ที่อ้างอิงอยู่ถัดจาก B21 ไปทางขวามือ 1 คอลัมน์

การใช้งานทั่วไปของ OFFSET ใน Application Studio อยู่ในการคำนวณค่าสะสมสำหรับใช้ในแผนภูมิ ABC Analysis หรือ Pareto ในการคำนวณค่าสะสม คุณต้องเพิ่มค่าของเซลล์ปัจจุบันเข้าไปในค่าของเซลล์ด้านบน แต่ในสภาพแวดล้อมแบบไดนามิกของไฮเปอร์บล็อก เราไม่สามารถระบุเซลล์ด้านบนด้วยการอ้างอิงเซลล์แบบคงที่ได้

รายงาน RP006 มีตัวอย่างวิธีการใช้ OFFSET ในการคำนวณค่าสะสม

รายงาน RP006 มีไฮเปอร์บล็อกซึ่งสร้างขึ้นจากมิติผลิตภัณฑ์ของคิวบ์การวิเคราะห์ของแอปพลิเคชันตัวอย่าง

ไฮเปอร์บล็อกมีสองเซลล์ค่า ในเซลล์ค่าแรก สูตร ROC จะแสดงค่าสำหรับกลุ่มผลิตภัณฑ์แต่ละกลุ่ม ในเซลล์ค่าที่สองเป็นสูตร OFFSET

ในโหมดมุมมอง เซลล์จะทำซ้ำค่าในเซลล์ค่าแรก นั่นเป็นเพราะสูตร OFFSET ไม่ถูกต้อง

สูตรในเซลล์ E5 คือ:

=OFFSET(E5,0,0)+D5

วิธีเปลี่ยนสูตร OFFSET ให้แสดงผลค่าสะสม:

  1. ในโหมดออกแบบ ให้คลิกเซลล์ E5
  2. ในตัวแก้ไขสูตร ให้เปลี่ยนอาร์กิวเมนต์ที่สองของสูตร OFFSET จาก 0 เป็น -1 นั่นคือเปลี่ยนสูตรเป็น: =OFFSET(E5,-1,0)+D5 ในที่นี้ -1 หมายถึง 'แถวด้านบน'

    ในโหมดมุมมอง จะมีการแสดงค่าสะสม และป้ายกำกับสะสมจะปรากฏขึ้น ซึ่งทำได้อีกวิธีโดยใช้ฟังก์ชัน OFFSET ในเซลล์ E3 ซึ่งซ้อนอยู่ภายในคำสั่ง IF

    คำสั่ง IF คือ: =IF(OFFSET(E3,3,0)=OFFSET(E3,3,-1),"","Cumulative")

    เรารู้ว่าค่าในแถวแรกของผลลัพธ์จะเท่ากันเสมอ แต่ถ้าเป็นการแสดงค่าสะสม ค่าในแถวอื่นๆ ทั้งหมดจะไม่เท่ากัน ดังนั้น คำสั่ง IF จะทดสอบว่าค่าในแถวที่ 2 เท่ากันหรือไม่

    คำสั่ง IF มีความหมายดังนี้: หากค่าของเซลล์ซึ่งอยู่ต่ำกว่า E3 ลงไป 3 แถว เท่ากับค่าในเซลล์ที่อยู่ด้านล่างลงไปไป 3 แถวและห่างไปหนึ่งคอลัมน์ทางด้านซ้ายของ E3 จะไม่แสดงอะไรเลย ("") มิฉะนั้น ให้แสดงผลว่า "ยอดสะสม"