12-13 วิธีใช้สูตร Index แบบ Array

หลังจากหาตำแหน่งรายการได้แล้ว ต่อจากนี้จะใช้สูตร 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

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

FREE Online Excel Expert Training @ XLSiam
Scroll to Top