Snasui Excel Forum

Posted: **Mon Sep 19, 2011 9:59 pm**

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

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

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

ขอบคุณค่ะ

Posted: **Mon Sep 19, 2011 10:14 pm**

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

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

Ctrl+Shift+Enter

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

Posted: **Mon Sep 19, 2011 10:20 pm**

พอเปลี่ยนค่า
=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! ค่ะ

Posted: **Mon Sep 19, 2011 10:26 pm**

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

Posted: **Mon Sep 19, 2011 10:29 pm**

Office 2003 ค่ะ

Posted: **Mon Sep 19, 2011 10:33 pm**

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

Posted: **Mon Sep 19, 2011 10:36 pm**

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

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

ขอบคุณค่ะ

Posted: **Mon Sep 19, 2011 10:42 pm**

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

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

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

Posted: **Mon Sep 19, 2011 11:11 pm**

อาจารย์ค่ะ

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

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

Posted: **Mon Sep 19, 2011 11:50 pm**

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

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

Posted: **Mon Sep 19, 2011 11:54 pm**

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

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

Posted: **Tue Sep 20, 2011 5:56 pm**

กรณีนี้คงต้องหันมาพึ่ง 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

Posted: **Tue Sep 20, 2011 6:14 pm**

อาจารย์ค่ะ

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

Posted: **Tue Sep 20, 2011 6:31 pm**

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

Posted: **Tue Sep 20, 2011 9:26 pm**

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

หาก 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

Posted: **Tue Sep 20, 2011 11:38 pm**

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

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

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

ขอบคุณค่ะ

Posted: **Tue Sep 20, 2011 11:55 pm**

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

Posted: **Wed Sep 21, 2011 12:21 am**

ใช้สูตร
"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
เราต้องแก้ไขตรงไหนค่ะ

Posted: **Wed Sep 21, 2011 12:29 am**

ช่วยแจ้งผลเช่นนี้ทุกครั้งครับ ไม่เช่นนั้นผมก็ไม่ทราบว่าที่แนะนำไปนั้นใช้ได้หรือไม่ได้ เนื่องจากผมใช้งานหลายเครื่อง แต่ละเครื่องก็ไม่ได้มี 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

Posted: **Wed Sep 21, 2011 8:33 am**

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

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

Snasui Excel Forum

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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