ความเก่งกาจของฟังก์ชั่น Offset

 ถ้าเราไปยืนที่ใดที่หนึ่งแล้วสามารถจะหันได้รอบทิศ ย่อมเป็นที่พอใจและน่ายินดีมากกว่าจะหันไปได้แค่ ซ้าย ขวาและมองไปข้างหน้าใช่ไหมครับ

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

ฟังก์ชั่น Offset เป็นฟังก์ชั่นสำหรับหาค่า เดี่ยว หรือ ชุดข้อมูล ได้ ขึ้นอยู่กับการกำหนดส่วนประกอบของฟังก์ชั่นครับ ซึ่งฟังก์ชั่น Offset มีส่วนประกอบ 5 ส่วนตามด้านล่าง

ไวยากรณ์

=Offset(Reference,Rows,Cols,[Height],[Width])

หรือ แปลตามแบบของผมเอง

=Offset(เซลล์อ้างอิง, จำนวนแถวที่ห่างจากเซลล์อ้างอิง ,จำนวนคอลัมน์ที่ห่างจากเซลล์อ้างอิง, ความสูงของข้อมูล, ความกว้างของข้อมูล)

ยกตัวอย่างเช่น

=Offset(A1,0,0,1,1)

หมายความว่า

1. ให้หาค่าโดยดูจาก A1 เนื่องจากเซลล์อ้างอิง คือ A1
2. ห่างจาก A1 ไปด้านล่าง 0 แถว คือไม่ไปไหน ยังอยู่ที่ A1 เหมือนเดิม
3. ห่างจาก A1 ไปด้านขวา 0 คอลัมน์ คือไม่ไปไหน ยังอยู่ A1 เหมือนเดิม
4. ความสูงของข้อมูล 1 เซลล์ ก็คือความสูงของ A1
5. ความกว้างของข้อมูล 1 เซลล์ ก็คือความสูงของ A1 เช่นเดิม

สูตรด้านบน สามารถที่จะละส่วนประกอบ 2 ส่วนสุดท้ายไว้ก็ได้ เพราะมีความสูงและความกว้างของข้อมูลแค่ 1 บรรทัดก็จะได้เป็น

=Offset(A1,0,0)

หรือแบบของผู้ที่คิดว่าตัวเองเข้าใจดีแล้วก็จะเหลือ

=Offset(A1,,)

จะได้ค่าเดียวกัน คือ A1 นั่นเอง

ดูตัวอย่างตามภาพด้านล่าง

ยกตัวอย่างใหม่ให้หลากหลายกว่าเดิม เพราะ Offset สามารถไปข้างหน้าและถอยหลังได้ การเริ่มที่เซลล์ A1 ไม่สามารถไปซ้ายและขึ้นบนได้ แต่มีประโยชน์แน่นอน จะกล่าวถึงในตอนท้ายถ้าไม่ลืม

ถ้าเช่นนั้น เริ่มที่ D5 ก็แล้วกัน จะได้เป็น

=Offset(D5,-1,-2,2,2)

โอ้โห…มีติดลบด้วย

ครับ ต้องไม่กระพริบตาเลยครับ

แปลสูตรได้ว่า

1. เริ่มจาก D5
2. ห่างจาก D5 จำนวน -1 แถว (อ้าว…แล้วไปไหน) ไป D4 ครับ ซึ่ง D4 จะกลายเป็นเซลล์อ้างอิงใหม่เพื่อใช้ในข้อ 3
3. ห่างจาก D4 จำนวน -2 คอลัมน์ (อ้าว…แล้วไปไหน) ไป B4 ครับ ซึ่ง B4 จะกลายเป็นเซลล์อ้างอิงใหมเพื่อใช้ในข้อ 4
4. จาก B4 ในข้อ 3 ความสูง 2 แถว ก็แสดงว่าสูงไปถึง B5 ก็จะกลายเป็น B4:B5
5. จากข้อ 4 (คือ B4:B5) กว้าง 2 คอลัมน์ ก็จะกลายเป็น B4:C5

สรุป

=Offset(D5,-1,-2,2,2) คือช่วงเซลล์ B4:C5

ในชีวิตจริงการใช้สูตรนี้อย่างเดียวจะเกิดค่าผิดพลาดเนื่องจากให้ผลลัพธ์เป็นช่วงข้อมูล ซึ่งเราไม่สามารถอ้างถึงช่วงข้อมูลขึ้นมาลอย ๆ ในเซลล์ใด ๆ ได้ เช่นถ้าคีย์ตรง ๆ ในเซลล์ใด ๆ เป็น =B4:C5 จะให้ผลลัพธ์เป็นค่าผิดพลาด ดังนั้น ปกติแล้วจะใช้สูตรอื่นมาครอบอีกทีครับ เช่น

=Sum(Offset(D5,-1,-2,2,2))

หมายถึงการรวมยอดของช่วงเซลล์ B4:C5

ดูตัวอย่างตามภาพด้านล่าง

ความสูงและความกว้างเป็นลบได้ไหม ได้แน่นอนครับ อย่างที่บอกว่าสามารถหันได้รอบทิศ

ถ้าเปลี่ยนสูตรด้านบนเป็น

=Sum(Offset(D5,-1,-2,-2,-2))

จะเป็นการ Sum ช่วงเซลล์ไหนครับ? (เฉลยอยู่ด้านล่าง) ให้ทดลองเล่นดูแล้วจะเข้าใจมากขึ้น อย่าเพิ่งดูเฉลยครับ

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

เช่น

=Offset(A1,-1,0)

ผลลัพธ์จะได้ #Ref!

เนื่องจาก A1 คือเซลล์แรกแล้ว ไม่มีเซลล์อื่นใดมาก่อน A1 อีก

คิดว่าสิ่งที่อธิบายมานี้ทำให้เข้าใจมากขึ้นสำหรับฟังก์ชั่น Offset และใช้งานกันอย่างเพลิดเพลินนะครับ

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

อีกประการที่สำคัญ แม้ฟังก์ชั่น Offset จะมีความสามารถยอดเยี่ยม แต่ก็มีข้อด้อยตรงนี้เป็น Volatile คือ ถูกกระทบให้เปลี่ยนแปลงได้โดยง่ายเพื่อให้สูตรถูกต้องอยู่เสมอ

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

การที่เป็น Volatile นี้เองทำให้เราคำนึงให้มากสำหรับการใช้งาน เพราะถ้าใช้ Offset เป็นจำนวนมาก จะทำให้เครื่องคำนวณช้าลงอย่างมากครับ 

---

เฉลย

=Sum(Offset(D5,-1,-2,-2,-2))

คือ Sum(A3:B4)

Video อธิบายและการใช้งานฟังก์ชั่น Offset

Revised: October 21, 2017 at 19:15