:D snasui.com ยินดีต้อนรับ :D
ยินดีต้อนรับสู่กระดานถามตอบ Excel and VBA และอื่น ๆ ที่เป็นมิตรกับทุกท่าน มีไฟล์แนบมหาศาล ช่วยให้ท่านค้นหาและติดตามศึกษาได้โดยง่าย :thup: สมาชิกท่านใดที่ยังไม่ได้ระบุ Version ของ Excel ที่ใช้งานจริง สามารถทำตาม Link นี้เพื่อจะได้รับคำตอบที่ตรงกับ Version ของท่านครับ :arrow: ระบุ Version ของ Excel
:!: โปรดทราบ :!:
  1. กรุณาอ่านกฎการใช้บอร์ด (Forum rules) ในตำแหน่งด้านบนของแต่ละบอร์ด
  2. การสมัครสมาชิก การ Login การกู้คืนรหัสผ่าน
    1. สมัครสมาชิกดูขั้นตอนได้ที่ :arrow: สมัครสมาชิก
    2. Login เข้าระบบโดยคลิกปุ่ม Login ตรงมุมขวาบนของหน้านี้ :roll:
    3. การ Login ผ่าน Facebook ดูวิธีที่ :arrow: Login ผ่าน Facebook
    4. ลืมรหัสผ่านสามารถรับรหัสใหม่ได้ที่ :arrow: Reset รหัสผ่าน
  3. มีปัญหาการใช้งาน แจ้งผู้ดูแลระบบได้ที่ :arrow: ติดต่อผู้ดูแลระบบ
  4. กำหนดการตั้งค่าส่วนตัว เช่นตั้งค่าภาษาเป็นไทยหรืออังกฤษดูได้ที่ :arrow: ตั้งค่าส่วนตัว
  5. การตั้งและตอบกระทู้ดูได้ที่ :arrow: วิธีการตั้งและตอบกระทู้
  6. การจัดรูปแบบตัวอักษรด้วย bbcode ในช่องแสดงความคิดเห็นดูได้ที่ :arrow: จัดรูปแบบตัวอักษร
  7. กำหนดขนาดตัวอักษรใน Browser ดูได้ที่ :arrow: กำหนดขนาดตัวอักษรใน Browser

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

ฟอรัมถาม-ตอบปัญหาการใช้งาน MS Excel and VBA
Forum rules
  1. ไม่อนุญาตให้ใช้ภาษาแชทในการถามและตอบปัญหา ไม่ใช้คำว่า "คับ" หรือ "อ่ะครับ" แทนคำว่า "ครับ" ไม่ใช้คำว่า "เด๋ว" แทนคำว่า "เดี๋ยว" เป็นต้น เนื่องจากเมื่อแปลเป็นภาษาต่างประเทศแล้วจะให้ความหมายผิดไปจากที่ควรจะเป็น
  2. ห้ามถามโดยระบุชื่อผู้ตอบและต้องตั้งชื่อกระทู้ให้สื่อถึงปัญหาที่จะถาม ไม่ตั้งชื่อว่า ช่วยด้วยครับ, มีปัญหามาปรึกษาครับ เป็นต้น
  3. กรุณาอธิบายปัญหาและระบุคำตอบที่ต้องการมาในกระทู้ด้วยเสมอถึงแม้จะอธิบายไว้ในไฟล์แนบแล้วก็ตาม ทั้งนี้เพื่ออำนวยความสะดวกแก่เพื่อนสมาชิกในการค้นหาข้อมูล
  4. กรุณาแนบไฟล์ตัวอย่างพร้อมแสดงคำตอบที่ถูกต้องมาในไฟล์ด้วยเพื่อให้ง่ายต่อการทำความเข้าใจและสะดวกต่อการตอบคำถาม (ขนาดไฟล์ไม่เกิน 500Kb ขนาดภาพไม่เกิน 800*600 Pixel) ไม่แนบเป็น Link มาจากแหล่งอื่นที่อาจจะถูกลบทิ้งไปโดยต้นทางในภายหลัง นอกจากนี้ไม่ควรแนบไฟล์ที่มีข้อมูลสำคัญอันก่อให้เกิดความเสียหายกับตนเองและผู้อื่น
  5. กรณีเป็นคำถามเกี่ยวกับ Programming เช่น VBA, VB.Net, C#, SQL ฯลฯ ต้องลองเขียนมาเองก่อนเสมอ ถามเฉพาะที่ติดปัญหา ระบุ Module, Procedure ที่ติดปัญหาให้ชัดเจน กรุณาโพสต์ Code ให้แสดงเป็น Code คือเปิดด้วย [code] และปิดด้วย [/code] ตัวอย่างเช่น [code]dim r as range[/code] เพื่อให้แตกต่างจากข้อความทั่วไป สะดวกในการอ่านและทดสอบ (คลิกเพื่อดูตัวอย่าง)
  6. กรุณาแจ้งผลการใช้งานเมื่อได้รับคำตอบว่าตรงกับความต้องการหรือไม่
bbking
Member
Member
Posts: 11
Joined: Wed Nov 30, 2016 1:20 pm

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

#1

Post by bbking »

เนื่องจากว่า ผมไม่สามารถหาสูตรหรือฟังก์ชั่นใดๆใน excel ทำได้ ผมพยายามเลี่ยง VBA เนื่องจากผมยังไม่คล่อง (มีความรู้พื้นฐานแค่ทำ if else ธรรมดาๆ) พร้อมกับทางบริษัทต้องการวิธีธรรมดาที่สามารถอธิบายในที่ประชุมให้คนอื่นเข้าใจได้ง่ายๆ แต่ถ้าเลี่ยงไม่ได้ก็ต้องจำเป็นต้องใช้ครับ
ปัญหาคือ ผมมีตารางข้อมูล สามตาราง(ตารางจำลองล็อคว่างสินค้า) ตารางแรกคือ ตารางปัจจุบันที่สินค้าวางอยู่ ตารางที่สองคือตารางที่ผมต้องการนำสินค้าไปวางตามที่ออกแบบไว้ และตารางสุดท้ายคือตารางแสดงการย้าย ผมแต้มสีเหลืองไว้คือตำแหน่งที่สลับโดยใช้ฟังก์ชั่น ตัวเลขข้างในแถบสีเหลืองคือคำตอบที่ผมต้องการให้เป็นครับ การย้ายจะเป็นการย้ายจากที่หนึ่งไปอีกที่หนึ่ง แทนที่กัน ปัญหาคือคลังมีขนาดใหญ่ ผมไม่สามารถเขียนมือทำย้ายทีละตำแหน่งได้ครับ
You do not have the required permissions to view the files attached to this post.
menem
Silver
Silver
Posts: 549
Joined: Mon Jan 26, 2015 11:02 am

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

#2

Post by menem »

ถ้าเงื่อนไขการวางสินค้า กำหนดไว้ชัดเจนว่า สินค้าที่เป็นชนิดเดียวกัน
ต้องอยู่ต่อเนื่องกันทั้งหมด สามารถใช้ Function MATCH , CountIF
และ Index มาช่วยได้ครับ
You do not have the required permissions to view the files attached to this post.
User avatar
otlup
Member
Member
Posts: 11
Joined: Tue Nov 29, 2016 11:19 am

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

#3

Post by 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)

ปล. ผมไม่เห็นส่วนที่ทำสีเหลืองไว้ในไฟล์แนบนะครับ
You do not have the required permissions to view the files attached to this post.
menem
Silver
Silver
Posts: 549
Joined: Mon Jan 26, 2015 11:02 am

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

#4

Post by menem »

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



... น่าจะยังไม่ถูกครับ , ขอตรวจสอบอีกที >_<
You do not have the required permissions to view the files attached to this post.
User avatar
otlup
Member
Member
Posts: 11
Joined: Tue Nov 29, 2016 11:19 am

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

#5

Post by 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 ผิดพลาด
You do not have the required permissions to view the files attached to this post.
Last edited by otlup on Wed Nov 30, 2016 7:42 pm, edited 1 time in total.
menem
Silver
Silver
Posts: 549
Joined: Mon Jan 26, 2015 11:02 am

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

#6

Post by menem »

น่าจะได้แล้วครับ ^_^
You do not have the required permissions to view the files attached to this post.
bbking
Member
Member
Posts: 11
Joined: Wed Nov 30, 2016 1:20 pm

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

#7

Post by bbking »

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