สูตรที่หาค่าได้ ก็ใช้บอกตำแหน่งได้

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

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

ที่พิเศษไปกว่านี้ หากคุณตัดกาในช่อง Allow edit directly in cells ทิ้งไป (ใน Excel Options > Advanced หรือใน Excel 2003 ใช้เมนู Tools > Options > Edit) จะพบว่าเมื่อดับเบิลคลิกที่เซลล์สูตร =A1 จะย้ายตำแหน่ง cursor ไปที่เซลล์ต้นทาง A1 ให้ทันที

สิ่งที่อธิบายข้างต้นนี้เคยอธิบายมาหลายต่อหลายครั้งในการอบรมและในเว็บ ExcelExpertTraining.com ซึ่งพฤติกรรมแบบนี้แหละที่นำไปสู่เรื่องแปลกที่คาดไม่ถึง จากเดิมที่เขียนว่า "สูตรที่หาค่าได้ ก็ใช้บอกตำแหน่งได้" ขอเขียนใหม่ให้ละเอียดชัดเจนกว่าเดิมว่า

ยกตัวอย่างเช่น สูตร =INDEX(A1:B10,5,2) เป็นสูตรหาค่าจากตารางข้อมูลช่วง A1:B10 ในตำแหน่ง Row 5 ตัดกับ Column 2 จะได้ค่าจากเซลล์ B5

ถ้าอยากจะตามไปดูว่าเซลล์ B5 อยู่ที่ไหนโดยไม่ต้องนั่งแกะสูตรว่า Row 5 ตัดกับ Column 2 คือเซลล์ใด ให้หยิบเมาส์คลิกลากทับสูตร =INDEX(A1:B10,5,2) ที่แสดงบน Formula Bar จากนั้นกดปุ่ม F5 แล้วกดปุ่ม Enter จะพบว่าสูตร =INDEX(A1:B10,5,2) เปลี่ยนไปเป็น =B5 ให้ทันที พอเห็นดังนี้แล้วให้กดปุ่ม Esc เพื่อทำให้สูตรคืนสู่สภาพเดิม

ยิ่งกว่านั้น ถ้า Copy สูตร =INDEX($A$1:$B$10,5,2) ไว้ก่อนแล้วกดปุ่ม F5 แล้วกดปุ่ม Ctrl+v เพื่อ Paste สูตรนี้ลงไปในช่อง Reference แล้วกดปุ่ม Enter จะพบว่า Excel พาคุณย้ายตำแหน่งไปที่เซลล์ B5 ให้เลย (สังเกตว่าวิธีนี้ต้องกำหนดตำแหน่งอ้างอิงเป็น Absolute คือ $A$1:$B$10 ก่อนด้วย)

ยิ่งกว่านี้ของยิ่งกว่านั้นอีก ถ้านำสูตร INDEX(A1:B10,5,2) ไปซ้อนในสูตรอื่น เช่น สูตร Offset จะทำให้สูตร INDEX(A1:B10,5,2) ทำหน้าที่ชี้ตำแหน่งต่อให้กับ Offset

ขอให้สร้างสูตร =OFFSET(INDEX(A1:B10,5,2),2,3) แล้วลองใช้วิธีข้างต้นค้นหาว่าหมายถึงเซลล์ใด จะพบว่าเป็นเซลล์ E7 ใช่ไหม เพราะเซลล์ E7 เป็นเซลล์ที่อยู่ถัดลงมาจากเซลล์ B5 จำนวน 2 row และถัดไปด้านขวาจำนวน 3 column เทียบเท่ากับการใช้สูตร =OFFSET(B5,2,3) นั่นเอง

แต่ถ้าสร้างสูตร =INDEX(A1:B10,5,2) ไว้ในเซลล์ D12 แล้วใช้สูตร Offset อ้างถึง D12 ในแบบ =OFFSET(D12,2,3) จะพบว่า D12 ไม่ได้ช่วยบอกตำแหน่งต่อให้กับสูตร Offset เพราะสูตร Index ในเซลล์ D12 ทำหน้าที่หาค่าจากเซลล์ B5 มาแสดงเสร็จก็จบหน้าที่ไปแล้ว

สูตรที่สามารถหาค่าที่เก็บไว้จากตารางได้โดยตรง ซึ่งทำงานได้แบบที่อธิบายนี้ ได้แก่ If, Choose, Index, Offset, Indirect (แต่ไม่รวมสูตร VLookup เพราะมันไม่ได้หาค่าได้โดยตรง) ซึ่งไม่จำเป็นต้องคืนค่าเพียงค่าเดียวเป็นตำแหน่งเซลล์เดียว โดยสามารถคืนค่าเป็นตารางเพื่อใช้บอกตำแหน่งตารางทั้งตารางก็ยังได้