สูตรยาวๆแบบนี้ มีวิธีสร้างกันยังไง =INDEX(Sheet1!C3:G6,MATCH(B4,Sheet1!B3:B6,0),MATCH(B5,Sheet1!C2:G2,0))

ย้ายงานใหม่ เจอสูตรเก่าสร้างไว้แบบนี้ จะทำความเข้าใจว่าเขาสร้างมาได้ยังไง

=INDEX(Sheet1!C3:G6,MATCH(B4,Sheet1!B3:B6,0),MATCH(B5,Sheet1!C2:G2,0)) 

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

ตัวอย่างนี้สูตรที่ใช้หาค่า 333 คือ Index ซึ่งหาจากตำแหน่งที่ตัดกันระหว่างรหัส a003 กับ D หรือ Row 3 ตัดกับ Column 4 ในตารางพื้นสีส้ม

ให้ลองสร้างสูตรง่ายๆ เพื่อลองดูก่อนว่า concept แบบนี้ใช้ได้ไหม 

=Index( C3:G6, 3, 4) พบว่าหา 333 ออกมาได้ก็ใช้ได้แล้วสูตรนี้

จากนั้นจึงหาทางสร้างสูตร Match ที่จะหาว่ารหัส a003 อยู่ใน Row 3 กับ D มาจาก Column 4

เซลล์ C11 =Match( B11, B3:B6, 0 ) หาเลข 3 ได้
เซลล์ C12 =Match( B12, C2:G2, 0 หาเลข 4 ได้

พอได้สูตรหาตำแหน่งตัดกันระหว่าง Row 3 กับ Column 4 ได้แล้ว จึงลิงก์ค่าจาก C11 กับ C12 ไปใช้ต่อในสูตรเดิม =Index( C3:G6, 3, 4) ให้กลายเป็น

=Index( C3:G6, C11, C12)

จากนั้นจึงลอกตัวสูตร Match จากเซลล์ C11 กับ C12 ไปใส่ในสูตร Index แทน จะได้สูตรซ้อนกันตามที่เคยสร้างไว้ตามนี้

=INDEX( C3:G6, MATCH( B11, B3:B6, 0 ), MATCH( B12, C2:G2, 0)) 

พอ Cut สูตรไปวางที่ชีทอื่น สูตรจะมีชื่อชีทมาใส่เพิ่มให้เอง กลายเป็น

=INDEX( Sheet1!C3:G6, MATCH( B4, Sheet1!B3:B6, 0 ), MATCH( B5, Sheet1!C2:G2, 0))

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

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

ผมเพิ่มอีกสูตร =SUMPRODUCT((Top=B12)*(Left=B11)*MyData) ใช้ได้กรณีที่เป็นตัวเลขครับ ดูในตัวอย่าง

   

Download ตัวอย่างจาก

https://drive.google.com/file/d/1c4-Zd8aUbii5Lidk49mQBam_SZCmAAvK/view?usp=sharing