วิธีใช้ Text กำหนดขอบเขตตารางแบบยืดหยุ่น

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

image025

จากภาพนี้สมมุติว่าต้องการหายอดรวมของตัวเลขในพื้นที่ตารางจาก B2:F15 ซึ่งตามปกติสามารถใช้สูตร =SUM(B2:F15) ได้อยู่แล้ว เมื่อต้องการเพิ่มลดขนาดตาราง B2:F15 ให้ต่างไปจากเดิมก็ต้องแก้ไขตำแหน่งอ้างอิงในสูตร หรือสั่ง Insert/Delete Row หรือ Column ซึ่งย่อมทำให้ตำแหน่งอ้างอิงในสูตร Sum ปรับตาม แต่ผลจากการ Insert/Delete Row หรือ Column อาจกระทบกับพื้นที่ตารางใกล้เคียงที่อยู่ในแนวเดียวกันและทำให้ต้อง Copy สูตรที่สร้างไว้เดิมมีลำดับเซลล์ที่อ้างอิงไว้ผิดเพี้ยนไปจากที่ควรก็ได้

เริ่มต้นจากใช้สูตรในเซลล์ I2 และ I3 =Cell(“Address”,cell) หาตำแหน่งอ้างอิงของเซลล์หัวมุมซ้ายบนสุด(First)และเซลล์หัวมุมขวาล่างสุด(Last) จะได้ตำแหน่งอ้างอิง $B$2 และ $F$15 ตามลำดับ

นำตำแหน่งอ้างอิงที่ได้มาเชื่อมต่อกันในเซลล์ I4 ด้วยสูตร =FirstCell&":"&LastCell ทำให้ได้ตำแหน่งอ้างอิง $B$2:$F$15 ซึ่งมีลักษณะเป็น Text ที่บอกตำแหน่ง พร้อมที่จะนำไปใช้กับสูตร Indirect

เมื่อต้องการหายอดรวม ให้ใช้สูตรในเซลล์ I6 =SUM(INDIRECT(I4)) หรือจะนำสูตร =INDIRECT($I$4) ไปตั้งชื่อเป็น Formula Name เพื่อเพิ่มความสะดวกในการนำไปใช้ร่วมกับสูตรอื่นใดก็ได้ โดยไม่จำกัดเฉพาะสูตร Sum

หากต้องการปรับเปลี่ยนขนาดตาราง ให้ย้ายเซลล์ First หรือ Last ไปวางลงในตำแหน่งที่ต้องการ

image027

จากภาพนี้เมื่อย้ายเซลล์ First กับ Last ไปวางไว้ที่เซลล์ C5 และ E9 ตามลำดับ จะทำให้สูตร Sum หายอดรวมในขอบเขตตาราง C5:E9 ตามไปด้วย

ประเด็นสำคัญมิได้ขึ้นกับการสั่งย้ายเซลล์ไปตำแหน่งอื่น เพราะการสั่งย้ายเซลล์ย่อมต้องเกิดจากฝีมือของมนุษย์ แต่ขึ้นกับการใช้สูตร =Cell(“Address”,cell) ต่างหาก เพราะสูตรนี้จะคืนค่าเป็นตำแหน่งของเซลล์ที่กำหนดไว้ภายในวงเล็บของสูตร เมื่อใช้สูตร IF, Choose, Index, หรือ Offset ซ้อนเข้าไปก็จะได้ตำแหน่งเซลล์แปรเปลี่ยนตามให้ทันที เช่น

=CELL("Address",INDEX(MyNum,FirstRow))

image029

เมื่อซ้อนสูตร INDEX(MyNum,FirstRow) เข้าไปในสูตร Cell จะเปลี่ยนหน้าที่จากการหาค่าที่ 2 คือเลข 22 จากพื้นที่ตารางชื่อ MyNum มาเป็นการบอกตำแหน่งเซลล์ B3

Related Articles

© Copyright 1999

สงวนลิขสิทธิ์ตามกฎหมาย

ห้ามนำข้อความหรือส่วนหนึ่งส่วนใดของบทความหรือวิดีโอหรือรูปภาพไปใช้เพื่อการค้าขาย หรือเพื่อประโยชน์ส่วนตัว

อนญาตให้นำไปใช้เพื่อสาธารณประโยชน์โดยขอให้ระบุที่มาและชื่อผู้เขียนกำกับไว้ด้วยเสมอ

ลิงก์เว็บ Excel Expert Training

เว็บสำหรับ เรียนออนไลน์

เว็บสำหรับ เรียนแบบกลุ่ม-ส่วนตัว

ติดตามข่าวสารได้จาก facebook

ถามปัญหาได้ที่ กลุ่มคนรัก Excel

และไลน์กลุ่ม Excel Expert Group

ที่อยู่และการติดต่อ

สมเกียรติ ฟุ้งเกียรติ 7/1 รามคำแหง ซอย 35 หัวหมาก บางกะปิ กทม 10240 โทร 097-140-5555, 02-718-9331

Excel@ExcelExpertTraining.com

sfk234x234