vlookup โชว์คำตอบมากกว่า 1 ค่า ค่ะ

[su019]

เรียนอาจารย์

มีเรื่องสอบถามค่ะ คือ ใช้สูตร Vlookup แล้วต้องการโชว์คำตอบมากกว่า 1 ค่า
(ลองใช้สูตร SUBSTITUTE(MCONCAT(IF(A2:A18=D3,B2:B17)),FALSE,"") แต่ตัวข้อมูลมีมากกว่า 7000 Record เลยไม่ทำงาน)
ไมาทราบว่ามีสูตรอื่นแนะนำบ้างไหมค่ะ

ได้แนบไฟล์ตัวอย่างมาด้วยค่ะ

ขอบคุณค่ะ

[snasui]

การคั่นด้วย , สามารถปรับสูตรเป็น

=Substitute(Substitute(SUBSTITUTE(MCONCAT(IF($A$2:$A$18=D3,$B$2:$B$18),","),"FALSE,",""),",FALSE",""),"FALSE","")

Ctrl+Shift+Enter

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

[su019]

พอเปลี่ยนค่า
=Substitute(Substitute(SUBSTITUTE(MCONCAT(IF($A$2:$A$18=D3,$B$2:$B$18),","),"FALSE,",""),",FALSE",""),"FALSE","")

เป็น
=Substitute(Substitute(SUBSTITUTE(MCONCAT(IF($A$2:$A$7000=D3,$B$2:$B$7000),","),"FALSE,",""),",FALSE",""),"FALSE","")
ผลที่ได้คือ #VALUE! ค่ะ

[snasui]

ไม่ทราบว่าใช้ Excel Version ใดครับ

[su019]

Office 2003 ค่ะ

[snasui]

ให้ใช้ 2007 แทนครับ หากไม่มีคงต้องวานให้เพื่อน ๆ ทำให้แล้ว Save เป็น Value กลับมาให้ เนื่องจากเป็นข้อจำกัดของ Excel 2003 ที่ไม่สามารถใช้ Mconcat กับข้อมูลปริมาณมาก ๆ ได้ครับ

[su019]

แล้ว Office 2007 รองรับฐานข้อมูลได้มากแค่ไหนค่ะ ( ลองทำใน 2007 แล้วก็ได้ฐานข้อมูลไม่มากกว่า 2003 ไปสักเท่าไหร่ )

แล้วไม่มีสูตรอื่นที่ผลลัทธ์เหมือนกันหรือค่ะ


ขอบคุณค่ะ

[snasui]

คุณ su019 คงนำไฟล์ Excel ที่มีนามสกุล .xls ไปทำงานบน Excel 2007 ถึงเห็นว่าไม่มากไปกว่า 2003

Excel 2007 มีจำนวน 1048576 บรรทัด ในขณะที่ 2003 มีแค่ 65536 บรรทัด
Excel 2007 มีจำนวน 16384 คอลัมน์ ในขณะที่ 2003 มีแค่ 256 คอลัมน์

ลองคำนวณดูครับว่าเป็นกี่เท่า

[su019]

อาจารย์ค่ะ

นี่คือไฟล์ที่ทำงาน save เป็น Office 2007 แล้วค่ะ
ก็ยังไม่ได้เหมือนเดิม

ตามไฟล์ เดิมจะใช้สูตร Vlookup ตามพื้นที่สีเหลือง
ซึ่งอยากจะเปลี่ยนเป็น MCONCAT ตามพื้นที่สีเทาค่ะ

[snasui]

เนื่องจากตอนนี้ผมไม่ได้ใช้เครื่องที่มี Excel 2007 จึงไม่สามารถทดสอบให้ได้

ท่านใดมี Excel 2007 และติดตั้ง Add-ins Morefunc แล้วช่วยทดสอบให้ด้วยนะครับ

[su019]

ไม่เป็นไรค่ะอาจารย์ เดี่ยวจะลองดูใหม่แล้วกันนะค่ะ

ขอบคุณมากค่ะ สำหรับคำแนะนำค่ะ

[snasui]

กรณีนี้คงต้องหันมาพึ่ง VBA ครับ

ลอง Run Code ตามด้านล่างดูครับ

Code: Select all

Sub MconCat()
Dim rs  As Range, rsAll As Range
Dim rt As Range, rtAll As Range
Dim t As String
Application.Calculation = xlCalculationManual
With Worksheets("Run")
    Set rsAll = .Range("A2", .Range("A2").End(xlDown))
End With
With Sheets("Form2")
    Set rtAll = .Range("A7", .Range("A7").End(xlDown))
End With
On Error Resume Next
For Each rt In rtAll
    t = ""
    For Each rs In rsAll
        If rs = rt & "Time0" Then
            t = t & rs.Offset(0, 5) & ","
        End If
    Next rs
    rt.Offset(0, 4) = Left(t, Len(t) - 1)
Next rt
Application.Calculation = xlCalculationAutomatic
End Sub

[su019]

อาจารย์ค่ะ

มันรันตอนไหนค่ะ หรือแทนที่ Code Vlookup ใน Sheet Form2 ค่ะ

[snasui]

ก่อนจะ Run Code ลอง Clear ค่าผิดพลาดในคอลัมน์ A ของชีท Run เพี่อดูผลลัพธ์จากสูตรเดิมก่อนครับ

[snasui]

เพิ่มเติมครับ

หาก Clear ค่าผิดพลาดจากสูตรแล้วในชีท Run แล้ว สูตร Mconcat ที่ใช้ในชีท Form2 น่าจะแสดงผลได้ หากแสดงผลเป็น Error เหมือนเดิมค่อยมา Run Code ที่ให้ไปครับ

การ Run Code ให้ทำดังนี้ครับ

1. กดแป้น Alt+F11
2. เข้าเมนู Insert > Module โปรแกรมจะสร้าง Module ใหม่มาให้
3. Copy Code ที่ให้ไปมาวางใน Module
4. กดแป้น Alt+Q เพื่อกลับไปยังหน้าจอ Excel
5. กดแป้น Alt+F8 > เลือก Mconcat > Run

[su019]

สอบถามเพิ่มเติมค่ะ

จาก Code ที่อาจารย์ให้มาหลังจากรันแล้วค่าที่ได้จะเป็น Value แทนสูตรเลยใช่ไหมค่ะ

และหากต้องการที่จะทำกับสูตร Coumn E ซึ่งคือ Time0 หากต้องการทำกับ Column F:AB ซึ่งจะเป็นค่า Time1 ไปจนถึง Time 23
เราต้องแก้ไขตรงไหนค่ะ

ขอบคุณค่ะ

[snasui]

สรุปว่าได้ลบค่า Error ออกแล้วยังครับ ถ้าลบออกแล้วได้ผลหรือไม่อย่างไร ตอบด้วยครับ

[su019]

ใช้สูตร
"SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MCONCAT(IF(Run!$A$1:$A$7000=Form_Code!$A7&"Time0",Run!$F$1:$F$7000),","),"FALSE,",""),",FALSE",""),"FALSE","")"

ผลที่ได้คือ #VALUE!

แล้วรัน Code Mconcat ของอาจารย์ ผลลัทธ์ที่ได้คือ ใน Column E (ซึ่งจะเป็น Time0) จะโชว์คำตอบที่ถูกต้อง ทดสอบแล้วปรากฎว่าสำหรับค่าที่มีมากกว่า 1 ค่าก็ได้คำตอบที่ถูกต้อง แต่สำหรับช่องที่ไม่มีข้อมูลก็ยังคงเป็น #VALUE! อยู่ค่ะ คำตอบที่โชว์จะเป็น Value ไม่ใช่สูตรค่ะ


และหากต้องการที่จะทำกับสูตร Coumn E ซึ่งคือ Time0 หากต้องการทำกับ Column F:AB ซึ่งจะเป็นค่า Time1 ไปจนถึง Time 23
เราต้องแก้ไขตรงไหนค่ะ

[snasui]

ช่วยแจ้งผลเช่นนี้ทุกครั้งครับ ไม่เช่นนั้นผมก็ไม่ทราบว่าที่แนะนำไปนั้นใช้ได้หรือไม่ได้ เนื่องจากผมใช้งานหลายเครื่อง แต่ละเครื่องก็ไม่ได้มี Excel ครบทุก Version

กรณีต้องการใช้ Code ให้ลบสูตรทั้งหมดทิ้งไปเลยครับ แล้วนำ Code ด้านล่างไปใช้แทน ผมปรับมาให้ดูเป็นตัวอย่าง ลองสังเกตครับว่ามีการปรับตรงส่วนไหน

Code: Select all

Sub MconCat()
Dim rs  As Range, rsAll As Range
Dim rt As Range, rtAll As Range
Dim t As String, i As Integer
Application.Calculation = xlCalculationManual
With Worksheets("Run")
    Set rsAll = .Range("A2", .Range("A2").End(xlDown))
End With
With Sheets("Form2")
    Set rtAll = .Range("A7", .Range("A7").End(xlDown))
End With
On Error Resume Next
For i = 0 To 23
    For Each rt In rtAll
        t = ""
        For Each rs In rsAll
            If rs = rt & "Time" & i Then
                t = t & rs.Offset(0, 5) & ","
            End If
        Next rs
        rt.Offset(0, 4 + i) = Left(t, Len(t) - 1)
    Next rt
Next i
Application.Calculation = xlCalculationAutomatic
End Sub

[su019]

ได้แล้วค่ะ ขอบคุณมากๆๆค่ะ สำหรับความช่วยเหลือ

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