ในแฟ้มมีหลายชีทที่มีหน้าตาเดียวกัน จะสร้างสูตรเดียว ที่ใช้งานในทุกชีทได้ยังไง

ถ้าในแฟ้มมีหลายชีทที่มีหน้าตาแบบเดียวกัน จะสร้างสูตรยังไงดีให้ผู้ใช้แฟ้มสามารถเข้าใจได้ง่ายทันทีที่เห็นสูตรว่า หายอดจากตารางอะไรตรงไหนมาให้

ปกติชื่อ Range Name ที่สร้างขึ้น จะถูกกำหนดพื้นที่ตารางที่จำกัดเฉพาะชีทใดชีทหนึ่งเท่านั้น สังเกตุได้จากช่อง Refers to จะมีชื่อชีทกำกับด้านหน้าตำแหน่งเซลล์ไว้เสมอ เช่น ตามภาพตัวอย่างนี้ เมื่อตั้งชื่อพื้นที่ตัวเลขว่า MyData จะพบการอ้างอิงในช่อง Refers to : =Sheet1!$B$2:$B$10

เมื่อมีชื่อชีทกำกับไว้ด้านหน้านี่แหละ ทำให้เวลาดึงข้อมูลโดยใช้สูตร =Sum(MyData) ก็จะได้ยอดรวมเท่ากับ 9 ซึ่งมาจากตารางในชีทชื่อ Sheet1 เสมอ ไม่ว่าจะสร้างสูตร Sum นี้ไว้ในชีทใดก็ตาม

แต่ถ้าอยากจะหายอดรวมจากพื้นที่ $B$2:$B$10 จากชีทอื่นล่ะ วิธีที่มักพบว่าใช้กันก็คือจะตั้งชื่อใหม่ที่ไม่ซ้ำกับชื่อเดิมขึ้นมาอีก ทำให้ในแฟ้มมีหลายชื่อ หรือไม่ก็ใช้วิธีเติมเลขต่อท้ายชื่อ กลายเป็น MyData1, MyData2, MyData3

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

=Sheet1!$B$2:$B$10

ให้ลบส่วนของชื่อชีททิ้งไป เหลือแต่เครื่องหมาย ! แก้เป็น

=!$B$2:$B$10

คราวนี้แหละพอลิงก์ค่าจาก MyData มาใช้ ก็จะดึงข้อมูลจากพื้นที่ $B$2:$B$10 ของแต่ละชีทมาให้ โดยในแฟ้มจะมีชื่อ MyData เพียงชื่อเดียวเท่านั้น

Download ตัวอย่างได้จาก

https://drive.google.com/file/d/1bDlmiNmMc8exeGo1M4o7Dy_OefCXqIyV/view?usp=sharing

ตัวอย่างนี้ใส่ตัวเลขในแต่ละชีทไว้ต่างกัน กรอกเลข 1, 2, 3, 4 สังเกตว่าทุกชีทมีสูตร Sum(MyData) เหมือนกัน แต่ให้คำตอบเป็นยอดรวมของตัวเลขในแต่ละชีท