หาวิธีจับคู่เซลล์พร้อมสลับตำแหน่ง เซลล์ แทนที่กันแบบต่อเนื่องจนครบทุกตำแหน่ง

[bbking]

เนื่องจากว่า ผมไม่สามารถหาสูตรหรือฟังก์ชั่นใดๆใน excel ทำได้ ผมพยายามเลี่ยง VBA เนื่องจากผมยังไม่คล่อง (มีความรู้พื้นฐานแค่ทำ if else ธรรมดาๆ) พร้อมกับทางบริษัทต้องการวิธีธรรมดาที่สามารถอธิบายในที่ประชุมให้คนอื่นเข้าใจได้ง่ายๆ แต่ถ้าเลี่ยงไม่ได้ก็ต้องจำเป็นต้องใช้ครับ
ปัญหาคือ ผมมีตารางข้อมูล สามตาราง(ตารางจำลองล็อคว่างสินค้า) ตารางแรกคือ ตารางปัจจุบันที่สินค้าวางอยู่ ตารางที่สองคือตารางที่ผมต้องการนำสินค้าไปวางตามที่ออกแบบไว้ และตารางสุดท้ายคือตารางแสดงการย้าย ผมแต้มสีเหลืองไว้คือตำแหน่งที่สลับโดยใช้ฟังก์ชั่น ตัวเลขข้างในแถบสีเหลืองคือคำตอบที่ผมต้องการให้เป็นครับ การย้ายจะเป็นการย้ายจากที่หนึ่งไปอีกที่หนึ่ง แทนที่กัน ปัญหาคือคลังมีขนาดใหญ่ ผมไม่สามารถเขียนมือทำย้ายทีละตำแหน่งได้ครับ

[menem]

ถ้าเงื่อนไขการวางสินค้า กำหนดไว้ชัดเจนว่า สินค้าที่เป็นชนิดเดียวกัน
ต้องอยู่ต่อเนื่องกันทั้งหมด สามารถใช้ Function MATCH , CountIF
และ Index มาช่วยได้ครับ

[otlup]

ไม่ทราบว่าผมเข้าใจเช่นนี้ถูกต้องไหมครับ หากผิดพลาดขออภัยด้วยครับ

• 1) ตารางหัวข้อปัจจุบัน คือที่อยู่ที่สินค้าอยู่ในปัจจุบัน
  2) ตารางหัวข้อออกแบบไว้ คือที่ที่จะย้ายสินค้าไป
  3) ตารางขั้นตอน คือตารางที่จะแสดงว่า สินค้าแต่ละตัว ถูกย้ายจากล็อคจัดเก็บใดไปล็อคจัดเก็บใด (ซึ่งก็คือค่าล็อคจัดเก็บในตารางหัวข้อ ปัจจุบัน และ ล็อคจัดเก็บในตารางหัวข้อออกแบบไว้ของสินค้าแต่ละตัว)

ถ้าหากผมเข้าใจถูกต้อง
แล้วในตารางขั้นตอนต้องจำเป็นต้องมีการเรียงลำดับตามข้อมูลในไฟล์แนบหรือเปล่าครับ
เช่น
จากข้อมูลเดิมที่อยู่ในตารางขั้นตอน คือ
(บรรทัดที่ 7)A ย้ายจาก 113 ไป 101
(บรรทัดที่ 15)A ย้ายจาก 106 ไป 102
(บรรทัดที่ 18)A ย้ายจาก 114 ไป 100

แต่ถ้าใช้สูตรจะลำดับเป็น
(บรรทัดที่ 7)A ย้ายจาก 106 ไป 100
(บรรทัดที่ 15)A ย้ายจาก 113 ไป 101
(บรรทัดที่ 18)A ย้ายจาก 114 ไป 102

โดยผลลัพธ์การย้ายจะออกมาเหมือนกับ ตาราง ออกแบบไว้ คือ
A ย้ายไปล็อค 100
A ย้ายไปล็อค 101
A ย้ายไปล็อค 102

หากสามารถทำตามนี้ได้ ผมแนะนำสูตรตามนี้ครับ

สูตรใน Cell K5
{=INDEX($C:$C,SMALL(IF($B$5:$B$19=J5,ROW($B$5:$B$19),""),COUNTIF($J$5:$J5,J5)),0)}

สูตรใน Cell L5
{=INDEX($G:$G,SMALL(IF($F$5:$F$19=J5,ROW($F$5:$F$19),""),COUNTIF($J$5:$J5,J5)),0)}

จากนั้น Copy ลงมา

**เครื่องหมาย { } คือสัญลักษณ์ของ Array Formula เกิดจากการพิมพ์สูตรปกติจบแล้ว กด Ctrl + Shift + Enter ห้ามพิมพ์เองเด็ดขาด

!

วิธีพิมพ์สูตร Array Formula 1. พิมพ์สูตรลงใน Cell ตามปกติ เช่น พิมพ์ =INDEX($C:$C,SMALL(IF($B$5:$B$19=J5,ROW($B$5:$B$19),""),COUNTIF($J$5:$J5,J5)),0) 2. กด Ctrl + Shift + Enter (สูตรจะถูกเปลี่ยนเป็น Array Formula และเพิ่มเครื่องหมาย { } โดยอัตโนมัติ) 3. ทุกครั้งที่แก้ไขสูตร หรือคลิกเข้าไปใน Cell จะต้องกด Ctrl + Shift + Enter ทุกครั้ง

ตัวอย่างตามไฟล์แนบ (ตัวอย่างอยู่ใน Sheet2)

ปล. ผมไม่เห็นส่วนที่ทำสีเหลืองไว้ในไฟล์แนบนะครับ

[menem]

และเมื่อได้ตำแหน่งที่แน่นอนแล้ว จึงค่อย ๆ คำนวณลำดับการย้าย
ตามไฟล์แนบนี้อีกทีครับ



... น่าจะยังไม่ถูกครับ , ขอตรวจสอบอีกที >_<

[otlup]

ผมไม่ได้คิดถึงเรื่องลำดับการย้ายเลยครับ ได้เรียนรู้เลยครับ ขอบคุณคุณ menem มากครับ

ผมลองเพิ่มเติมสูตรแบบปรับลำดับ โดยต้องแก้ไขดังนี้ครับ

1. เพิ่มคำว่า "ว่าง" ในช่องเดิมที่ว่าง

2. สูตรของบรรทัดแรก แก้เป็น

J5 {=INDEX(F5:F19,MATCH(VLOOKUP("ว่าง",B5:C19,2,FALSE),G5:G19,0))}
K5 {=INDEX($C:$C,SMALL(IF($B$5:$B$19=J5,ROW($B$5:$B$19),""),COUNTIF($J$5:$J5,J5)),0)}
L5 =VLOOKUP("ว่าง",B5:C19,2,FALSE)

3. สูตรในบรรทัดถัดมา แก้เป็น
J6 =INDEX($F$5:$F$19,MATCH(K5,$G$5:$G$19,0))
K6 {=INDEX($C:$C,SMALL(IF($B$5:$B$19=J6,ROW($B$5:$B$19),""),COUNTIF($J$5:$J6,J6)),0)}
L6 =K5

จากนั้น Copy ลงมาด้านล่าง

สำหรับเรื่องเครื่องหมาย { } ขอย้ำอีกรอบว่า คือ Arrray Formula ห้ามพิมพ์เอง

!

วิธีพิมพ์สูตร Array Formula 1. พิมพ์สูตรลงใน Cell ตามปกติ เช่น พิมพ์ =INDEX(F5:F19,MATCH(VLOOKUP("ว่าง",B5:C19,2,FALSE),G5:G19,0)) 2. กด Ctrl + Shift + Enter (สูตรจะถูกเปลี่ยนเป็น Array Formula และเพิ่มเครื่องหมาย { } โดยอัตโนมัติ) 3. ทุกครั้งที่แก้ไขสูตร หรือคลิกเข้าไปใน Cell จะต้องกด Ctrl + Shift + Enter ทุกครั้ง

ตัวอย่างตามไฟล์แนบ (Sheet2 - ตารางปรับลำดับแล้ว)

**แก้ไข
1. แก้ไขไฟล์ - ตัดตารางที่ไม่ได้ปรับลำดับออกเพื่อกันความสับสน
2. แก้ไขสูตรที่อ้างอิง Range ผิดพลาด

[menem]

น่าจะได้แล้วครับ ^_^

[bbking]

ขอบคุณทุกคนครับ ทั้งคุณ menem และคุณ otlup เกี่ยวกับ excel นอกจากจะเป็นเรื่องความรู้แล้ว ต้องมีจิตนาการในการแก้ปัญหาด้วย เกี่ยวกับ vlookup หรือ match index ผมลองถูไปถูมาอยู่นานพอสมควร แต่ก็คิดไม่ออก ยังไงก็ขอขอบคุณมากๆนะครับ