สูตรซึ่งทำงานเกี่ยวข้องกับพื้นที่ตารางส่วนใหญ่ มีข้อจำกัดตรงที่สร้างไปแล้วก็แล้วกัน ต้องกำหนดขนาดพื้นที่แน่นอนตายตัวลงไป ต่อมาหากข้อมูลมีขนาดเปลี่ยนแปลงไป ไม่ว่าจะใหญ่ขึ้นหรือเล็กลง เราต้องช่วยแก้ไขสูตร หรือขนาดพื้นที่ตารางกันใหม่อีก
ถ้าใช้ชื่อ Range Name ช่วยกำกับขนาดตาราง จะแก้ไขง่ายขึ้นหน่อย เพราะเพียงแค่เปลี่ยน reference ของ Range Name ทำให้สูตรรู้จักพื้นที่ตารางขนาดใหม่ได้แล้ว
โดยทั่วไป วิธีง่ายที่สุดที่ใช้กัน คือ กำหนดขนาดตารางใน Range Name ให้มีขนาดใหญ่เกินพอไว้ก่อน
แต่ถ้าต้องการกำหนดขนาดตาราง ให้มีพื้นที่พอดีกับขนาดข้อมูล ตามจำนวนเซลล์ที่ใช้ล่ะก้อ Excel Expert Training ขอเสนอสูตร Offset ครับ
Offset มีหลายรูปแบบที่ใช้กัน เช่น
=Offset(เซลล์อ้างอิง, ตำแหน่ง Row ใหม่, ตำแหน่ง Column ใหม่)
ตัวอย่างที่ 1 ใช้สูตร = Offset (A1, 2, 3)
สูตรรูปแบบนี้ ใช้กำหนดตำแหน่งอ้างอิงให้เลื่อนไปจากเดิม โดยนับตำแหน่งเซลล์อ้างอิง เป็นตำแหน่ง 0,0 เราจะกำหนดเซลล์ใดที่ต้องการเป็นเซลล์อ้างอิงก็ได้ เช่น ตามตัวอย่าง ใช้ A1 เป็นเซลล์อ้างอิง
หากต้องการเลื่อนตำแหน่งไปเซลล์ข้างบนเหนือเซลล์อ้างอิง ให้กำหนดตำแหน่ง Row ใหม่ เป็นตัวเลขนับเป็นเลขจำนวนเต็มลบ หากต้องการเลื่อนตำแหน่งไปเซลล์ข้างล่างใต้เซลล์อ้างอิง ให้กำหนดตำแหน่ง Row ใหม่ เป็นตัวเลขนับเป็นเลขจำนวนเต็มบวก
หากต้องการเลื่อนตำแหน่งไปเซลล์ข้างซ้ายของเซลล์อ้างอิง ให้กำหนดตำแหน่ง Column ใหม่ เป็นตัวเลขนับเป็นเลขจำนวนเต็มลบ หากต้องการเลื่อนตำแหน่งไปเซลล์ข้างขวาของเซลล์อ้างอิง ใหกำหนดตำแหน่ง Column ใหม่ เป็นตัวเลขนับเป็นเลขจำนวนเต็มบวก
| -3 | ||||||
| -2 | ||||||
| -1 | ||||||
| -3 | -2 | -1 | 0,0 | 1 | 2 | 3 |
| 1 | ||||||
| 2 | ||||||
| 3 |
ดังนั้นสูตร =Offset(A1, 2, 3) จึงเคลื่อนย้ายตำแหน่งเซลล์อ้างอิง จากเดิมที่ A1 ไปใต้เซลล์อ้างอิงเท่ากับอีก 2 row และย้ายไปทางขวาอีก 3 column ไปตกที่ตำแหน่งเซลล์ D3
| A | B | C | D | |
| 1 | เดิม 0,0 | 1 | 2 | 3 |
| 2 | 1 | |||
| 3 | 2 | ใหม่ |
สูตรแบบนี้จะใช้กรณี ต้องการหาค่าซึ่งอยู่ในเซลล์ถัดไปจากตำแหน่งเดิม โดยเรารู้ตำแหน่งเดิมว่าอยู่ที่ไหน และรู้ด้วยว่าตำแหน่งเซลล์ที่ต้องการนั้น ห่างจากเซลล์เดิมเท่าใด
แต่ความน่าสนุกของ Offset ยังไม่จบแค่นี้ครับ เราสามารถกำหนดขนาดพื้นที่ตารางใหม่ นับจากจุดอ้างอิงใหม่ได้ด้วย ซึ่งตอนนี้เราได้สูตรหาตำแหน่งอ้างอิงใหม่ได้แล้ว ขาดแต่วิธีกำหนดขนาด ซึ่งต้องใช้สูตร offset แบบเต็ม ดังนี้
=Offset(เซลล์อ้างอิง, ตำแหน่ง Row ใหม่, ตำแหน่ง Column ใหม่, ขนาด row, ขนาด Column)
เพื่อให้ง่ายขึ้น ขอใช้ตัวอย่างซึ่งยังไม่ได้ย้ายตำแหน่งอ้างอิง แต่มีขนาด row / column เท่ากับ 2 row กับ 3 column ดังนี้
ตัวอย่างที่ 2 ใช้สูตร = Offset (A1, 0, 0, 2, 3) หมายถึง range ตั้งแต่ A1:C2
| A | B | C | D | |
| 1 | 1 | 2 | 3 | |
| 2 | 2 | |||
| 3 |
ตัวอย่างที่ 3 ใช้สูตร = Offset (A1, 2, 3, 2, 3) หมายถึง range ตั้งแต่ D3:F4
| A | B | C | D | E | F | |
| 1 | เดิม | |||||
| 2 | ||||||
| 3 | ใหม่ | |||||
| 4 |
สูตรตามตัวอย่างที่ 2-3 นี้ ต้องใช้แบบ array formula หรือกำหนดไว้บน formula name หากสร้างแบบธรรมดาหรือใช้แบบธรรมดาในเซลล์เดียว จะ error ครับ
เนื่องจากภายในสูตร Offset ใช้ตัวเลขเป็นตัวชี้ตำแหน่ง ดังนั้นเราสามารถใช้สูตรอื่น ซึ่งคืนค่าเป็นตัวเลข นำมาซ้อนภายในสูตร Offset อีกทีหนึ่ง เช่น ใช้สูตร Count หรือ Counta ช่วยนับจำนวนเซลล์ที่ใช้งาน ทำให้สูตร Offset ปรับขนาดได้เองตามจำนวนเซลล์ที่ใช้ เช่น
= Offset (A1, 0, 0, Counta(A:A), Counta(1:1))
สูตรข้างบนนี้ จะมีขนาด row ปรับตามจำนวนเซลล์ที่ใช้ใน column A และมีขนาด column ตามจำนวนเซลล์ที่ใช้ใน row 1
| 21/12/2544 |
http://xls.i.am |