Page 1 of 1
หาค่า location code และ location area จาก b_code
Posted: Fri May 27, 2011 7:28 am
by sc201105
เรียนอาจารย์ ที่เคราพ
ผมต้องรบกวนอย่างช่วยผมเกี่ยวกับการหาค่า location code และ location area จาก b_code ตามเอกสารแนบ ผมไม่รู้ว่าจะทำอย่างไร ผมได้อธิบายไว้ใน File แล้ว ประกอบด้วย File ข้อมูลจาก อยู่ที่ sheet source และข้อมูลอ้างอิง location code และ location area จะอยู่ที่ sheet location data ผมไม่รู้ว่าเขียนอย่างไรให้มันเช็ค digit ข้อมูลลดหลั่นตัวเลขของเพื่อให้ได้ข้อมูลตามที่บอกไว้ใน file
รบกวนด้วยครับ ไม่รู้จะทำอย่างไรจริงๆครับที่ให้มันทำงานและได้ผลลัพธ์อย่างที่ต้องการ
ขอบคุณมากๆ ครับผม
Re: หาค่า location code และ location area จาก b_code
Posted: Fri May 27, 2011 10:33 am
by snasui
ดูตามไฟล์แนบครับ
ผมเพิ่มคอลัมน์ G:I มาเพื่อกระจายสูตรออกมาให้ทำความเข้าใจง่ายขึ้น สูตรใน G:H สามารถเขียนในเซลล์เดียวกันเพื่อหา Location_Code ได้
โดยมีขั้นตอนดังนี้
1. ที่เซลล์ G2 คีย์สูตรเพื่อหาค่าที่ต้องการตัดมามีกี่จำนวน
=7-MOD(ROWS(G$2:G2)-1,6)
Enter > Copy ลงด้านล่าง
2. ที่เซลล์ H2 คีย์สูตรเพื่อตัดค่าในคอลัมน์ B เริ่มจากตำแหน่งที่ 3 ของอักขระ มาเท่าจำนวนที่ได้ตามข้อ 1. ด้านบน
=MID(B2,3,G2)+0
Enter > Copy ลงด้านล่าง สังเกตการบวกด้วย 0 ก็เพื่อต้องการทำให้เป็นตัวเลข เนื่องจากค่าจากการตัดข้อความจะเป็นตัวอักษรเสมอ
3. ที่เซลล์ I2 คีย์สูตรเพื่อหาค่า Location_Area
=VLOOKUP(H2,'Location Data'!$A$2:$B$19,2,0)
Enter > Copy ลงด้านล่าง หากไม่พบจะแสดงผลลัพธ์เป็น #N/A
Re: หาค่า location code และ location area จาก b_code
Posted: Fri May 27, 2011 12:01 pm
by sc201105
เรียนท่านอาจารย์
ผมรบกวนอีกครั้งครับ มันไม่ได้อย่างที่ต้องการ เนื่องจากข้อมูลที่เป็นของจริงนั้นมันจะปะปนกันมาไม่เีรียงตามลำดับในการตัดว่า row แรก ต้อง 7 digits row ถัดมาของข้อมูลเป็น 6 digits ครับ ซึ่งผมทดลองนำข้อมูลมาคละกันสร้างเป็น sheets
source(2) และนำcopy สูตรของอาจารย์ไปวางไว้มันจะไม่ได้คำตอบที่ต้องการ
โดยจริงๆ แล้วต้องดูที่ b_code ทุกตัวเป็น 7 digits หมดก่อนแล้ว vlookup เพื่อให้ได้ค่าว่าพบกี่ row แล้ว row ที่ไม่พบนั้นให้นำกลับไปหาใหม่โดยให้ลดจำนวน digit b_code ลงเหลือ 6 digits แล้วทำการ lookup หาข้อมูลอีก แล้วนำตัวที่ไม่เจอนั้นมาหาใหม่ โดยลดจำนวน digit b_code ลงเหลือ 5 digits และ 4 digits ......ไปเลยๆ จนกระทั่งหาไม่พบจาก sheet location_data ซึ่งผมไม่รู้ว่าจะเขียน loop ให้มันแสดงค่าผลลัพธ์ที่ต้องการไว้ใน column เดียวกันได้อย่างไร คือที่ column H และ Column I ที่อาจารย์ช่วยทำขึ้นมาให้ครับผม
รบกวนอาจารย์ช่วยอีกครั้งครับ
ขอบคุณพระคุณครับผม
Re: หาค่า location code และ location area จาก b_code
Posted: Fri May 27, 2011 12:31 pm
by snasui
สามารถใช้สูตร Array ได้ครับแต่สูตรจะยากมาก ลองดูตามไฟล์แนบครับ
ที่เซลล์ E2 คีย์
=INDEX('Location Data'!$A$2:$A$19,SMALL(IF(ISNUMBER(MATCH('Location Data'!$A$2:$A$19,--MID(B2,3,8-ROW(INDIRECT("1:6"))),0)),ROW('Location Data'!$A$2:$A$19)-ROW('Location Data'!$A$2)+1),1))
Ctrl+Shift+Enter > Copy ลงด้านล่าง สำหรับค่าไหนที่ไม่มีในชีท Location Data จะแสดงค่า #Num!
Re: หาค่า location code และ location area จาก b_code
Posted: Fri May 27, 2011 1:04 pm
by sc201105
ขอบคุณครับท่านอาจารย์ ได้แล้วครับ มีคำถามเพิ่มเติมว่า
1. มัน run นานมากหากข้อมูลของผมมีขนาดเป็น หมื่นๆ แถว
ทำอย่างไรให้มันเร็วได้บ้างหรือเปล่าครับ
2. แล้วหาก copy สูตรเลยข้อมูลที่มีอยู่จะให้มันแสดงค่าเป็น
Null จะต้องเพิ่มเติมอย่างไรในสูตรบ้างครับ
3. แล้วหากเขียนเป็น VBA ผมจะเขียน code ตรงนี้ได้อย่างไร
บ้างครับผม
รบกวนอาจารย์เพิ่มอีกครั้งครับผม
ขอบพระคุณอาจารย์มาอีกครั้งครับผม
Re: หาค่า location code และ location area จาก b_code
Posted: Fri May 27, 2011 1:18 pm
by snasui
1. เนื่องจากเป็นสูตร Array ข้อมูลเป็นหมื่นแถวก็จะใช้เวลาคำนวณนานครับ จะให้เร็วเครื่องต้องแรงครับ
2. กรณีไม่ต้องการให้แสดงค่าผิดพลาดก็ให้เขียนสูตร If ครอบเข้าไปอีกชั้น ยกตัวอย่างเช่น
=If(iserr(...)),"",...)
เครื่องหมาย ... คือสูตรเดิมและจะใช้ได้กับ Version 2007 ขึ้นไปเนื่องจากซ้อนสูตรเกิน 7 ชั้นจึงไม่สามารถทำได้ใน 2003
อีกทางเลือกให้เพิ่มคอลัมน์เข้ามาช่วย ยกตัวอย่างจากไฟล์เดิมที่เซลล์ G2 คีย์สูตรเพื่อแปลงค่า Location_Code ที่ผิดพลาดเป็นค่าว่าง
=If(Iserr(E2),"",E2)
Enter
3. กรณีต้องการใช้ VBA ให้เขียนมาก่อนครับ ติดตรงไหนก็มาถามกันต่อครับ
Re: หาค่า location code และ location area จาก b_code
Posted: Fri May 27, 2011 1:56 pm
by sc201105
ขอบคุณครับอาจารย์ สำหรับคำตอบ ส่วนเรื่อง vba นั้นผมไม่รู้จริงๆ เลย เลยไม่รู้จะขึ้นต้นเขียนอย่างไร จะกำหนดตัวแปรอย่างไร เพียงแต่ผมลองนำสูตรที่อาจารย์ทำให้ แล้วทดลอง record marco จะได้สูตรอย่างนี้ แล้วหากผมจะเขียนเป็นตัวแปรพอจะแนะเป็นจุดเริ่มต้นได้หรือเปล่าว่าจะขึ้นอย่างไรก่อนดี
Sub add_location_code()
Range("E2").Select
Selection.FormulaArray = _
"=INDEX('Location Data'!R2C1:R19C1,SMALL(IF(ISNUMBER(MATCH('Location Data'!R2C1:R19C1,--MID(RC[-3],3,8-ROW(INDIRECT(""1:6""))),0)),ROW('Location Data'!R2C1:R19C1)-ROW('Location Data'!R2C1)+1),1))"
Selection.Copy
Range("E3:E20").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'Location Data'!R2C1:R19C2,2,FALSE)"
Selection.Copy
Range("F3:F20").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
ขอบพระคุณครับผม
Re: หาค่า location code และ location area จาก b_code
Posted: Fri May 27, 2011 2:28 pm
by snasui
ดูการประยุกต์ใช้ Code ตามด้านล่างกับสูตร Array ตามไฟล์แนบครับ
Code: Select all
Sub Test0()
Dim r As Range, rs As Range, rt As Range
With Worksheets("Source")
Set rs = .Range("B2", .Range("B" & Rows.Count).End(xlUp))
End With
Set rt = Worksheets("Template").Range("B2")
For Each r In rs
rt = r
r.Offset(0, 3) = rt.Offset(0, 3)
r.Offset(0, 4) = rt.Offset(0, 4)
Next
End Sub
Re: หาค่า location code และ location area จาก b_code
Posted: Fri May 27, 2011 3:01 pm
by sc201105
ขอบคุณครับอาจารย์ ผมขอไปศึกษาก่อนครับ เพื่อประยุกต์ใช้กับงานหน้าต่อไปด้วย หากติดขัดประการใดจะเรียนรบกวนสอบถามเพิ่มเติมอีกครั้งต่อไป
ขอบพระคุณอย่างสูงอีกครั้งครับผม