สอบถามการค้นหาข้อมูล แบบเงื่อนไขซับซ้อนครับ

[pijitza]

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

ตอนนี้ผมสามารถรับค่าข้อมูลจากคีย์บอร์ด และบันทึกค่านั้นลงในฟิลด์ B1

จากนั้นนำค่าจากฟิลด์ B1 ไปตรวจสอบใน ฟิลด์ D5 กับฟิลด์ F5 เมื่อตรงตามเงื่อนไข แสดงค่า J5 ได้ตามปกติ

ตามรูปในลิงค์ด้านล่างนะครับ (ขออภัยที่แนบไฟล์ภาพไม่เป็นครับ)

http://www.mx7.com/view2/yFjWgcAK77vJnm5P

ตอนนี้ผมติดอยู่ 2 ปัญหาครับ
1.เนื่องด้วยข้อมูลของผมมีประสาน 100,000 บรรทัดครับ แต่ผมไม่สามารถเขียนโค้ดเป็น Loop และแสดงคำตอบได้ครับ
2. แถวที่ 3 และแถวที่ 4 จะเป็นเลขบัตรที่มีเลข 0 นำหน้า ผมไม่สามารถหาวิธีเชคได้เลยครับ

ยกตัวอย่าง 2 (อ้างอิงตามในไฟล์แนบ Excel)
คอลั่มน์ D3 ------------- คอลั่มน์ F3 ------------- คอลั่มน์ J3
0424100000000000000 0424109999999999000 JPN

ถ้าเราพิมพ์ 0424 เป็น 4 หลักเลขด้านซ้ายตรงกัน ก็จะตอบ JPN
ถ้าเราพิมพ์ 042410 เป็น 6 หลัก เลขด้านซ้ายตรงกัน ก็จะตอบ JPN
ถ้าเราพิมพ์ 04241010 เป็น 8 หลัก เลขอยู่ในช่วงต่ำสุดถึงสูงสุด ก็จะตอบ JPN
ถ้าเราพิมพ์ 042410155564 เป็น 12 หลัก เลขอยู่ในช่วงต่ำสุดถึงสูงสุด ก็จะตอบ JPN
ถ้าเราพิมพ์ 0424109999999999000 ทั้งหมด 19 หลักเลย อยู่ในช่วงต่ำสุดถึงสูงสุด ก็จะตอบ JPN ครับ

รบกวนผู้รู้แนะนำผมด้วยนะครับ เนื่องจากผมไม่มีความรู้ด้าน Excel มาก่อนเลยครับ
ผมจึงใช้การหาข้อมูลจาก Google ประมาณ 1 เดือน แต่สามารถทำได้เพียงแค่นี้ แล้วทำต่อไม่ได้แล้วครับ

ไฟล์แนบเป็น .xlsm นะครับ

[snasui]

ตามคำอธิบายที่ให้มาสามารถใช้สูตรได้ครับ

ตัวอย่างสูตรที่ C1

=LOOKUP(B1+0,LEFT(D3:D200000,LEN(B1))+0,J3:J200000)

Enter

[pijitza]

http://www.mx7.com/view2/yFoV4wrbndv3hCUY

ได้ลองนำสูตรไปใช้ (ตามภาพที่แนบในลิงค์แล้วครับ)

แต่ยังไม่สามารถใช้งานได้ครับ ลองรันระบบแต่คำตอบยังไม่แสดงออกมาครับ

รบกวนแนะนำด้วยครับ

[DhitiBank]

สามารถแนบรูปภาพเข้ามาในฟอรั่มได้เลยครับ แต่ขนาดต้องไม่เกิน 300 kb

ส่วนปัญหาตามรูป สาเหตุเพราะได้มีการตั้งรูปแบบเซลล์ที่เอาสูตรไปวางเป็น text เอาไว้ก่อนครับ เวลาเอาสูตรไปวางโปรแกรมเลยเข้าใจว่าเป็นข้อความ วิธีแก้คือ เลือกเซลล์ C1 --> กด Ctrl+H --> ช่อง Find What: คีย์ = --> ช่อง Replace What: คีย์ = --> Replace

2015-10-17_084556.png

[snasui]

ตามรูปในลิงค์ด้านล่างนะครับ (ขออภัยที่แนบไฟล์ภาพไม่เป็นครับ)

แนบไฟล์มาได้ก็ต้องแนบภาพมาได้ครับ

สำหรับภาพต้องมีมิติไม่เกิน 800*600 และขนาดไฟล์ไม่เกิน 300kb ครับ

[pijitza]

รบกวนสอนถามเพิ่มเติมครับ

ตอนนี้ผมสามารถรับข้อมูลนำไปรันในระบบได้แล้วครับ แต่เมื่อลองป้อนข้อมูลหลากหลายปรากฏว่า

เมื่อป้อนข้อมูลเลข 1 ซึ่งไม่อยู่ในเงื่อนไขใดๆเลย แต่คำตอบออกมาเป็น CHN ครับ (ตามไฟล์ภาพ)

checkbintype1.png

เมื่อป้อนข้อมูลเลข 9 ซึ่งไม่อยู่ในเงื่อนไขใดๆเลย แต่คำตอบออกมาเป็น ARE ครับ (ตามไฟล์ภาพ)

checkbintype9.png

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

ส่วนภาพสุดท้าย เมื่อป้อนข้อมูลเลข 2001999999999999999 ครบทั้ง 19 หลัก ซึ่งไม่อยู่ในเงื่อนไขใดๆเลย

แต่คำตอบออกมาเป็น USA ครับ ซึ่งจริงๆแล้ว คำตอบ USA จะอยู่ถึงแค่ช่วง 2001999999999999000 ครับ

checkbintype2001999999999999999.png

รบกวนแนะนำทีครับ ขอบพระคุณครับ

[snasui]

ที่โพสต์มานั้นคำถามคืออะไรครับ

[pijitza]

คำถามคือเงื่อนไขที่ผมต้องการตรวจสอบครับ

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

ตอนนี้ผมสามารถรับค่าข้อมูลจากคีย์บอร์ด และบันทึกค่านั้นลงในฟิลด์ B1

ตามสูตรที่ให้มายังไม่สามารถทำตามเงื่อนไขที่ไฮไลท์สีแดงได้ครับ ขอบคุณครับ

[snasui]

ที่เขียนมานั้นไม่ใช่คำถามครับ

ตัวอย่างคำถามเช่น

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

หากคำถามเป็นตามที่ผมเขียนมา สามารถปรับสูตรที่ C1 เป็นด้านล่างครับ

=IF(ISNA(MATCH(B1+0,INDEX(LEFT(D3:D200000,LEN(B1))+0,0),0)),"",LOOKUP(B1+0,LEFT(D3:D200000,LEN(B1))+0,J3:J200000))

[pijitza]

ผมได้ทดสอบจากสูตรดังกล่าวแล้วครับ

=IF(ISNA(MATCH(B1+0,INDEX(LEFT(D3:D200000,LEN(B1))+0,0),0)),"",LOOKUP(B1+0,LEFT(D3:D200000,LEN(B1))+0,J3:J200000))

ผลปรากฏว่าค่าข้อมูลในช่อง B1 ที่อยู่ในระหว่างในช่วงของคอลัมน์ D และ F ไม่แสดงคำตอบครับ

เช่น ผมป้อนข้อมูล 2001010000000000000 จะต้องแสดงคำตอบเป็น USA เนื่องจาก

2001000000000000000 >= 2001010000000000000 <= 2001999999999990000

รบกวนขอสูตรที่อยู่ในเงื่อนไขด้วยครับ ขอบพระคุณครับ

[snasui]

ตัวอย่างการปรับสูตรครับ

=IFERROR(INDEX(J3:J200000,MATCH(1,INDEX((B1&"">=LEFT(D3:D200000,LEN(B1)))*(B1&""<=LEFT(F3:F200000,LEN(B1))),0),0)),"")

[pijitza]

เบื้องต้นได้ลองนำสูตรไปใช้งาน สามารถใช้งานตามเงื่อนไขได้แล้วครับ

ถ้ามีอะไรเพิ่มเติม จะขอรบกวนถามอีกครั้งครับ ขอบพระคุณมากครับ

[pijitza]

รบกวนสอบถามเพิ่มเติมครับ ถ้าผมต้องการจะตรวจสอบเลขบัตร แต่จะให้แสดงคำตอบหลายๆคำตอบ ต้องใช้สูตร Excel อย่างไรครับ

ตัวอย่าง เช่น เมื่อกด Check Bins ปุ่มสีเขียวในไฟล์ Excel ช่อง D1 ผมได้ทำให้ตัวเลขที่เราพิมพ์ค่าเข้าไปแสดงในช่อง L1 อัติโนมัติ
ถ้าเราพิมพ์เลข 6 หลัก เช่น "510375" เมื่อเชคจากในไฟล์ คอลั่มน์ D และ F แล้วจะมีเลข 6 หลัก เลข 510375 (ช่อง L1)
อยู่ 3 คำตอบ คือแถว (D15 , D16 , D17) ตามที่ระบายพื้นหลังสีเหลืองไว้ในไฟล์
จึงอยากให้คำตอบไปแสดงในช่อง L , M , N ที่ระบายพื้นหลังสีฟ้าไว้ในไฟล์ครับ

เดิมทีจากสูตรในช่อง C1

=IFERROR(INDEX(J3:J200000,MATCH(1,INDEX((B1&"">=LEFT(D3:D200000,LEN(B1)))*(B1&""<=LEFT(F3:F200000,LEN(B1))),0),0)),"")

สามารถแสดงคำตอบได้ครับ แต่ผมนำมาดัดแปลงไม่ถูก ถ้าต้องการเชค 6 หลักแรก แต่อยากได้หลายๆคำตอบ
เพื่อนำไปดูว่าข้อมูลไหนน่าเชื่อถือมากที่สุด และอยู่ในช่วงเลขบัตรใด (เนื่องจากไฟล์ต้นฉบับมีประมาณสองแสนบรรทัดครับ)

ขอบคุณล่วงหน้าสำหรับคำแนะนำครับ

[snasui]

ทำตามด้านล่างครับ

1. เซลล์ L1 คีย์สูตร
   =LEFT(B1,6)
   Enter หากคีย์แล้วไม่แสดงผลลัพธ์ให้เปลี่ยน Format เป็น General เสียก่อน จากนั้น Activate สูตรใหม่โดยกดแป้น F2 แล้ว Enter
2. เซลล์ M2 คีย์สูตร
   =IFERROR(INDEX($D$3:$D$21,SMALL(IF(LEFT($D$3:$D$21,6)=$L$1,ROW($D$3:$D$21)-ROW($D$3)+1),ROWS(M$2:M2))),"")
   Ctrl+Shift+Enter > Copy ลงด้านล่าง ปรับช่วงที่ระบายไว้ในสูตรเพื่อใช้กับคอลัมน์ที่เหลือ (L, N)

[pijitza]

ขออภัยที่เงียบหายไปครับ เนื่องจากต้องไปธุระที่ต่างจังหวัดครับ

เบื้องต้นได้ลองนำสูตรไปใช้งาน เซลล์ L1 ใช้งานได้ปกติครับ

แต่เซลล์ M2 ใส่สูตรเข้าไปแล้ว ได้ลองใส่ตัวเลขรันดูไม่แสดงค่าใดๆครับ

[parakorn]

ลองปรับเป็นแบบนี้ดูครับ
=IFERROR(INDEX($D$3:$D$21,SMALL(IF(LEFT($D$3:$D$21,6)=$L2,ROW($D$3:$D$21)-ROW($D$3)+1),ROWS(M$2:M2))),"")
Ctrl+Shift+Enter > Copy ลงด้านล่าง

ใช่ที่ต้องการหรือปล่าวครับ

[parakorn]

ลองปรับเป็นแบบนี้ดูครับ
=IFERROR(INDEX($D$3:$D$21,SMALL(IF(LEFT($D$3:$D$21,6)=$L2,ROW($D$3:$D$21)-ROW($D$3)+1),ROWS(M$2:M2))),"")
Ctrl+Shift+Enter > Copy ลงด้านล่าง

ใช่ที่ต้องการหรือปล่าวครับ

ขอโทษทีครับ ลองปรับตามนี้ครับ
=IFERROR(INDEX($D$3:$D$21,SMALL(IF($D$3:$D$21=$L$1,ROW($D$3:$D$21)-ROW($D$3)+1),ROWS(M$2:M2))),"")
Ctrl+Shift+Enter > Copy ลงด้านล่าง

[snasui]

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

[pijitza]

ผมได้แนบไฟล์ไว้ให้แล้วนะครับ

เบื้องต้นได้ลองนำสูตรไปใส่ในช่องเซลล์ M2 ครับ แต่คำตอบไม่แสดงผลครับ

จุดประสงค์อยากให้นำเลข 6 หลัก จากช่องเซลล์ L1 ไปเชคกับคอลัมน์ D 6 หลักแรก เช่น เซลล์ L1 เท่ากับ 5103986

คำตอบจะเป็นบรรทัดที่ระบายพื้นหลังเป็นสีชมพูครับ
- ตั้งแต่ช่องเซลล์ L2 ลงไปตามลำดับ (คำตอบมาจากช่องเซลล์ J18 ถึง J21)
- ตั้งแต่ช่องเซลล์ M2 ลงไปตามลำดับ (คำตอบมาจากช่องเซลล์ D18 ถึง D21)
- ตั้งแต่ช่องเซลล์ N2 ลงไปตามลำดับ (คำตอบมาจากช่องเซลล์ F18 ถึง F21)

ขออภัยครับที่ก่อนหน้านี้ผมอธิบายไม่ชัดเจน

[parakorn]

ที่ cell L1 คีย์
=LEFT($B$1,6)

ที่ cell M2 คีย์
=IFERROR(INDEX($D$3:$D$21,SMALL(IF(LEFT($D$3:$D$21,6)=$L$1,ROW($D$3:$D$21)-ROW($D$3)+1),ROWS(M$1:M1))),"")
Ctrl+Shift+Enter แล้วลากลงครับ

รหัสที่ใช้เช็ค 6 หลัก หรือ 7 หลักครับ ลองปรับช่วงข้อมูลตามตัวเลขที่ไฮไลต์สีแดงดูนะครับ