ฟังก์ชัน OFFSET และการตั้งชื่อช่วงข้อมูลแบบ Dynamic

สำหรับ การตั้งชื่อช่วงข้อมูลแบบคงที่ (Static range name) ที่ผมเคยเขียนไว้ก่อนหน้านั้น มีข้อจำกัดอย่างหนึ่งคือเมื่อมีข้อมูลเพิ่มเข้ามาใหม่ ต่อจากช่วงเซลล์ที่เราตั้งชื่อไปแล้วนั้น การเรียกใช้ชื่อดังกล่าว จะไม่ครอบคลุมไปถึงข้อมูลที่เพิ่มเข้ามาใหม่ เราต้องแก้ไขตำแหน่งอ้างอิงให้กับชื่อที่ตั้งไปแล้วใหม่ทุกครั้ง ซึ่งการนำไปใช้งานจริง จะไม่สะดวกต่อการปรับปรุงข้อมูล โดยเฉพาะในงานฐานข้อมูล (Database) หรือการทำรายการเลือก (List) เพื่อใช้เติมข้อมูล ดังนั้น จึงต้องใช้วิธีการให้ชื่อที่เราตั้งนั้น มีความแสนรู้มากขึ้น สามารถอัพเดตช่วงข้อมูลของตัวเองได้ทันที เมื่อมีข้อมูลใหม่เพิ่มเข้ามา เราเรียกวิธีการตั้งชื่อแบบนี้ว่าDynamic Range Name


ทำความรู้จักกับฟังก์ชัน OFFSET

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

OFFSET( reference, rows, cols ) สำหรับกรณีระบุตำแหน่งเซลล์เป้าหมาย หรือ
OFFSET( reference, rows, cols, height, width ) สำหรับกรณีระบุช่วงเซลล์เป้าหมาย

• Reference : ตำแหน่งเซลล์อ้างอิง เพื่อระบุระยะทางไปยังเซลล์ หรือช่วงเซลล์เป้าหมาย
• rows : จำนวนแถวที่ต้องการให้นับขึ้นหรือลง จากตำแหน่งเซลล์อ้างอิง ซึ่งค่า rows อาจเป็นค่าบวก (นับลงมาใต้ตำแหน่งเซลล์อ้างอิง) หรือเป็นค่าลบ (นับขึ้นไปเหนือตำแหน่งเซลล์อ้างอิง) ก็ได้
• cols : จำนวนคอลัมน์ที่ต้องการให้นับไปทางซ้ายหรือขวา จากตำแหน่งเซลล์อ้างอิง ซึ่งค่า cols อาจเป็นค่าบวก (นับไปทางขวาของเซลล์อ้างอิง) หรือเป็นค่าลบ (นับไปทางซ้ายของเซลล์อ้างอิง) ก็ได้
• height : จำนวนแถว ที่ระบุให้เป็นช่วงข้อมูล
• width : จำนวนคอลัมน์ ที่ระบุให้เป็นช่วงข้อมูล

รูปที่ 1 แสดงการอ้างอิงไปยังช่วงเซลล์ที่กำหนด โดยใช้ฟังก์ชัน OFFSET

จากรูปที่ 1 ถ้าให้ A1 เป็น Reference หรือตำแหน่งเซลล์อ้างอิง เพื่อระบุช่วงเซลล์ผลลัพธ์ C2:D8 (ในกรอบสีแดง) พิกัดของการระบุ OFFSET ในกรณีนี้ คือ OFFSET($A$1, 1, 2, 7, 2) โดยที่ $A$1 คือตำแหน่งที่ใช้อ้างอิง

1, 2 คือระยะจาก $A$1 นับลงมา 1 แถว และนับไปทางขวา 2 คอลัมน์ ซึ่งก็คือตำแหน่ง C2 ซึ่งเป็นตำแหน่งเริ่มต้นของช่วงเซลล์ผลลัพธ์ที่ต้องการนั่นเอง

7, 2 คือจำนวนแถวและจำนวนคอลัมน์ ของช่วงเซลล์ผลลัพธ์ที่ต้องการอ้างถึง (C2:D8 ในกรอบสีแดง)



ฟังก์ชัน COUNTA นับจำนวนเซลล์ที่ไม่ว่าง

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

COUNTA( value )

• value คือ ช่วงเซลล์ที่ต้องการนับ ในกรณีนี้ value คือ ข้อมูลชนิดใดๆ ซึ่งรวมถึงข้อความว่าง ("") แต่ไม่รวมเซลล์ว่าง (Blank) เราอาจใช้เป็นช่วงข้อมูลก็ได้ เช่น =COUNTA( $A:$A ) หมายถึง นับทุกเซลล์ในคอลัมน์ A เป็นต้น

จากรูปที่ 1 ข้างต้น ลองพิมพ์สูตรที่เซลล์ใดๆ =COUNTA($A:$A) จะได้ผลลัพธ์ = 8 เพราะ Excel จะดูว่าในคอลัมน์ A มีจำนวนเซลล์ที่ไม่ใช่เซลล์ว่างทั้งหมดกี่เซลล์ และถ้าหากเราลองพิมพ์ข้อความหรือตัวเลขใดๆ ลงใน A9 ผลลัพธ์จากสูตร COUNTA($A:$A) ก็จะเปลี่ยนเป็น 9 หรือหากเราลบข้อมูลในเซลล์ใดเซลล์หนึ่ง ซึ่งอยู่ในคอลัมน์ A ออกไป ผลลัพธ์ที่ได้จากสูตร ก็จะมีค่าลดลงด้วยเช่นกัน

ทำนองเดียวกัน หากเราพิมพ์สูตรที่เซลล์ใดๆ =COUNTA($1:$1) จะได้ผลลัพธ์ = 4 เพราะ Excel จะดูว่าในแถวที่ 1 มีจำนวนเซลล์ที่ไม่ใช่เซลล์ว่างทั้งหมดกี่เซลล์

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


ตัวอย่างการนำไปใช้งาน


รูปที่ 2

จากตัวอย่างในรูปที่ 2 ถ้าเราต้องการหาผลรวมในช่วง B1:B12 หรือหาผลรวมทั้งหมดนั้น แน่นอนว่า เราคุ้นเคยที่จะใช้สูตร=SUM(B1:B12) ซึ่งได้ผลลัพธ์เท่ากับ 1,200 ดังในเซลล์ D1 ในรูปที่ 2



รูปที่ 3

แต่ในกรณีที่เราต้องการหาผลรวมเฉพาะบางส่วนของข้อมูล เช่น หาผลรวมเฉพาะ 3 เดือนแรก เราก็สามารถที่จะใช้สูตร=SUM(B1:B3) หรือ =SUM(OFFSET(B1,0,0,3,1)) ดังในเซลล์ D4 ในรูปที่ 3



รูปที่ 4

ในทำนองเดียวกัน ถ้าเราต้องการหาผลรวมเฉพาะ 6 เดือนแรก เราก็จะเปลี่ยนสูตรเป็น =SUM(B1:B6) หรือ=SUM(OFFSET(B1,0,0,6,1)) ดังในเซลล์ D7 ในรูปที่ 4

มาถึงตรงนี้ บางท่านอาจจะยังมองไม่ออกว่า แล้วทั้งสองสูตรที่ยกมาในสองกรณีข้างต้นนั้น มันเหมือนหรือต่างกันอย่างไร เพราะในเมื่อเราใช้ SUM แบบปกติได้อยู่แล้ว ทำไมต้องมี OFFSET เข้ามาเกี่ยวข้องด้วย

การใช้ SUM แบบเดี่ยวๆ ตามที่ยกตัวอย่างมานั้น เราต้องระบุลงไปเองในสูตร ว่าต้องการรวมตัวเลขตั้งแต่ ม.ค. ไปเป็นจำนวนกี่เดือน เช่น 3 เดือน, 6 เดือน หรือ 9 เดือน เป็นต้น ซึ่งเมื่อเปลี่ยนเงื่อนไขครั้งใด ก็ต้องมาแก้ไขสูตรทุกครั้งไป



รูปที่ 5

แต่กรณีที่เราใช้ SUM ร่วมกับ OFFSET เราสามารถที่จะทำให้สูตรนั้น เป็นอัตโนมัติได้ ดังตัวอย่างในรูปที่ 5 ให้ใส่จำนวนเดือนที่ต้องการรวมตัวเลข ลงในเซลล์ F9 ในที่นี้ผมลองใส่เลข 6

ที่ D10 พิมพ์สูตร =SUM(OFFSET(B1,0,0,F9,1)) จะได้ผลลัพธ์ 600

ลองเปลี่ยนตัวเลขที่ F9 แล้วสังเกตผลลัพธ์ที่เกิดขึ้น จะเห็นได้ว่า เมื่อเงื่อนไขเปลี่ยน ผลลัพธ์ก็เปลี่ยนอัตโนมัติ โดยที่เราไม่ต้องมาแก้ไขใดๆ ในสูตรเลย



รูปที่ 6

ลองมาดูกันอีกสักตัวอย่างหนึ่ง เช่น ในรูปที่ 6 สมมติโจทย์บอกว่า ให้รวมตัวเลขในคอลัมน์ B เฉพาะช่วงที่มีชื่อเดือนในคอลัมน์ A เท่านั้น ซึ่งเมื่อดูจากรูปจะเห็นว่า มีชื่อเดือนอยู่ 8 เดือน ก็หมายถึงให้รวมตัวเลขช่วง B1:B8 นั่นเอง

จากรูป ที่เซลล์ D13 พิมพ์สูตร =SUM(OFFSET(B1,0,0,COUNTA(A:A),1)) จะได้ผลลัพธ์เท่ากับ 800 ทั้งนี้เพราะ ฟังก์ชัน COUNTA ทำการนับเซลล์ในคอลัมน์ A พบว่ามีเซลล์ที่ไม่ใช่เซลล์ว่างอยู่ 8 เซลล์ นั่นคือชื่อเดือนจำนวน 8 เดือน นั่นเอง

จากนั้นลองใส่ชื่อเดือนเพิ่มลงไปทีละเดือน หรือในทางกลับกัน ลองลบชื่อเดือนออกทีละเดือน แล้วสังเกตผลลัพธ์ที่เปลี่ยนแปลงไปในเซลล์ D13


การตั้งชื่อช่วงเซลล์แบบ Dynamic ด้วยฟังก์ชัน Offset

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

จากตัวอย่างในรูปที่ 6 หากเราตั้งชื่อให้กับช่วงตัวเลขในคอลัมน์ B เฉพาะข้อมูลที่ตรงกับจำนวนเดือนในคอลัมน์ A (สมมติให้ชื่อว่า Amount) เราก็สามารถใช้ชื่อดังกล่าว แทนลงในสูตรได้ เช่น
จากสูตรเดิม =SUM(OFFSET(B1,0,0,COUNTA(A:A),1))เขียนใหม่ได้เป็น =SUM(Amount)

การตั้งชื่อ Amount ให้เป็นแบบ Dynamic มีขั้นตอนดังนี้

สำหรับผู้ใช้ Excel 2003 หรือเวอร์ชันก่อนหน้านั้น ให้เข้าไปที่เมนู Insert (แทรก) > Name (ชื่อ) > Define... (กำหนด...) จะปรากฏหน้าดังรูปที่ 7


รูปที่ 7 แสดงการตั้งชื่อช่วงเซลล์แบบ Dynamic สำหรับ Excel 2003

1. พิมพ์ชื่อ Amount ลงในช่องด้านบน (ถ้ามีข้อความใดๆ ก็ลบออก และพิมพ์ Amount ทับลงไป)
2. ในช่องด้านล่าง (ถ้ามีข้อความใดๆ ก็ลบออกก่อน) พิมพ์สูตร =OFFSET($B$1,0,0,COUNTA($A:$A),1)
3. คลิกปุ่ม Add แล้วคลิก OK (หรือจะคลิก OK เลยก็ได้)
4. หลังจากนั้น ลองพิมพ์สูตร ณ เซลล์ใดๆ =SUM(Amount) แล้วสังเกตผลลัพธ์เทียบกับ D13
5. ลองใส่ชื่อเดือนเพิ่มลงไปทีละเดือน หรือในทางกลับกัน ลองลบชื่อเดือนออกทีละเดือน แล้วสังเกตผลลัพธ์ที่เปลี่ยนแปลง

สำหรับผู้ใช้ Excel 2007 หรือเวอร์ชันใหม่กว่า ให้เข้าไปที่แถบ Formulas (สูตร) > Define Name (กำหนดชื่อ) จะปรากฏหน้าดังรูปที่ 8


รูปที่ 8 แสดงการตั้งชื่อช่วงเซลล์แบบ Dynamic สำหรับ Excel 2007 หรือใหม่กว่า

1. พิมพ์ชื่อ Amount ลงในช่อง Name
2. ในช่อง Refers to ด้านล่าง (ถ้ามีข้อความใดๆ ก็ลบออกก่อน) พิมพ์สูตร =OFFSET($B$1,0,0,COUNTA($A:$A),1)
3. คลิกปุ่ม OK
4. หลังจากนั้น ลองพิมพ์สูตร ณ เซลล์ใดๆ =SUM(Amount) แล้วสังเกตผลลัพธ์เทียบกับ D13
5. ลองใส่ชื่อเดือนเพิ่มลงไปทีละเดือน หรือในทางกลับกัน ลองลบชื่อเดือนออกทีละเดือน แล้วสังเกตผลลัพธ์ที่เปลี่ยนแปลง