Back to: Applying Excel in Product Summary and Invoice
หลังจากหาตำแหน่งรายการได้แล้ว ต่อจากนี้จะใช้สูตร Index เพื่อดึงรายละเอียดรายการของตำแหน่งรายการนั้นมาแสดงประกอบว่าเป็นรายการของวันที่อะไร สินค้าอะไร รับจ่ายเท่ากับเท่าไหร่
แทนที่จะต้องสร้างสูตร Index เพื่อดึงค่าออกมาแบบเซลล์ต่อเซลล์ ซึ่งทำให้ Excel เสียเวลาคำนวณหลายครั้งตามจำนวนเซลล์สูตร ในตัวอย่างนี้จะนำเสนอวิธีสร้างสูตร Index แบบ Array เพื่อทำให้ Excel คำนวณเร็วขึ้นอย่างมาก
เริ่มจากเลือกพื้นที่ K8:N12 แล้วสร้างสูตร =INDEX(MyData, I8:I12, COLUMN(B4:E4)-COLUMN(B4)+1) แล้วกดปุ่ม Ctrl+Shift+Enter ลงไปพร้อมกันทั้งตารางจะเกิดวงเล็บปีกกาปิดหัวท้ายสูตรให้เอง
MyData เป็นพื้นที่ตารางข้อมูล
I8:I12 เป็นเลขที่รายการที่หาได้แบบ Multiple Match
COLUMN(B4:E4)-COLUMN(B4)+1 เป็นสูตรที่จะหาเลขลำดับ 1, 2, 3, 4 (สูตรนี้ช่วยทำให้สามารถย้ายตารางไปที่อื่นได้ด้วย ดีกว่าการใช้ Column(B4)-1 ที่จะตายตัว ห้ามย้ายตารางเด็ดขาด)
Download ตัวอย่างได้จาก
https://drive.google.com/file/d/1bbXurifbze1DboKU0KTs–qA0gc7Yu3g/view?usp=sharing
https://drive.google.com/file/d/14eBWsUARZa0s-YUKWGwRWrexI_q-Wt8P/view?usp=sharing
เลขที่รายการ เลขลำดับที่เรียงจากน้อยไปมาก เริ่มจากเลข 1 หรือจะเริ่มจากเลขที่ใดก็ได้ แทนที่จะมาเสียเวลาพิมพ์เองลงไปทีละเซลล์ หรือจะจับเซลล์ด้านบนมาบวกด้วย 1 เพื่อให้เป็น 1, 1+1=2, 2+1=3, 3+1=4 ไปเรื่อยๆนั้น อย่าใช้สูตรแบบนี้นะครับ
=Row(A1) หรือ =Row()
สูตร =Row(A1) จะได้ค่าออกมาเป็นเลข 1 เพราะสูตรนี้จะหาว่าเซลล์ A1 อยู่ที่ Row ไหน
=Row(B123) จะได้เลข 123 เพราะเซลล์ B123 อยู่ใน Row 123
ส่วนสูตร =Row() จะหาเลขที่ Row ของเซลล์ที่สร้างสูตรนี้ไว้ เช่นถ้าสร้างไว้ในเซลล์ C345 ก็จะได้เลข 345
บางคนอยากได้ตัวเลขมาใช้ก็เลยใช้สูตร Row นี่แหละ ได้เลขตามที่ต้องการมาง่ายมาก
แล้วทำไมจึงบอกว่าอย่าใช้แบบนี้ ?
เพราะถ้ามีการขยับตำแหน่งตารางไปที่อื่น มีการย้ายเซลล์ หรือสั่ง Insert Row ซึ่งทำให้ตำแหน่งเซลล์ที่มีสูตรนี้อยู่หรือเอาไปใช้เป็นตำแหน่งอ้างอิงในสูตรนี้ =Row(A1) ก็จะไม่ใช่ A1 อีกต่อไป ตัวเลข 1 ที่เคยได้ก็จะกลายเป็นเลขอื่นไปแล้ว
ถ้าอยากจะทำให้ได้เลข 1 คงที่แม้จะมีการย้ายตำแหน่งเซลล์ A1 ไปที่อื่นก็ตาม ต้องใช้สูตรแบบนี้ไว้ใน Row 1 ครับแล้ว copy ลากลงมา จะได้เลข 1,2,3,…
=Row()-Row($A$1)+1
แต่สูตรนี้ย้ายเซลล์ A1 ได้แต่ยังมีข้อห้ามไม่ให้ย้ายเซลล์สูตรไปที่อื่น ถ้าอยากจะได้เลขลำดับจากเลข 1,2,3 แบบย้ายที่ได้ ต้องใช้สูตรแบบนี้แทน
=Row()-Row($$Cellแรกของตารางเลขลำดับ)+1
เช่นตามภาพแนบนี้ที่เซลล์ I6 ใช้สูตร
=ROW()-ROW($I$6)+1
ไม่ว่าจะย้ายเซลล์นี้ไปที่ไหนก็จะได้เลข 1 ไปตลอด และเมื่อ copy ลากลงไปก็จะได้เลข 1,2,3,… ไปเรื่อยๆ
นอกจากสูตรนี้แล้ว ยังมีอีกสูตรหนึ่งที่ไม่ได้ขึ้นกับตำแหน่งเซลล์ เพียงแต่ต้องสร้างสูตรพร้อมกันตามแนวตั้งแล้วกดปุ่ม Ctrl+Shift+Enter พร้อมกันเพื่อสร้างแบบ Array จะมีวงเล็บปีกกา { } ปิดหัวท้ายสูตรให้เอง
=Row(Indirect(“1:15”))
จะได้เลขลำดับ 1,2,3,4,….15
ถ้าอยากจะให้เรียงลำดับไปถึงไหนได้ตามขนาดของตารางที่อาจจะใหญ่ขึ้นตามจำนวนรายการของชื่อสินค้า ก็ปรับสูตรใหม่เป็น
=Row(Indirect(“1:”&Rows(ProductRange)))
หรืออยากเริ่มจากเลขอื่นที่ไม่ใช่เลข 1 ก็เพียงเปลี่ยนเลข 1 ไปเป็นเลขอื่น
ชมคลิปวิดีโอได้จาก
https://www.excelexperttraining.com/book/index.php/a-to-z/p-q-r-s-t/r-r-r-r-r/row-indirect-function
ก่อนจะจากกัน จำไว้นะครับว่าสูตรที่ดีต้องสามารถหาค่าตามที่ต้องการได้เสมอ แม้ว่ามีการโยกย้ายตารางหรือย้ายตำแหน่งเซลล์ก็ตาม ดังนั้นพอสร้างสูตรเสร็จแล้วต้องลองย้ายเซลล์ดูว่าสูตรนั้นใช้ได้ตลอดไปจริงไหม