นับจำนวนรายการในตำแหน่งต่างๆ กัน

[Totem]

เรียนอาจารย์และเพื่อนสมาชิก

ขอสูตรนับจำนวนรายการในตำแหน่งต่าง ๆ ที่กำหนด โดยมีปัญหา ดังต่อไปนี้

1.นับตัวอักษรที่เป็น a ในช่อง a1:a10
C3 = 11
2.นับตัวอักษรที่เป็น a ในตำแหน่งที่ 1 และ b ในตำแหน่งที่ 3 คือ a1b3 ในช่อง a1:a10
C5 = 1
3.นับค่าที่เป็น 2 คือ a2,b2,c2,d02,a0002 มีทั้งหมด 9 รายการ ในช่อง a1:a10
C7 = 9
4.นับค่าที่เป็น 3 ในตำแหน่งที่ 2 คือ e03,d03 มีทั้งหมด 2 รายการ ในช่อง a1:a10
C9 = 2
5.นับตัวอักษรที่เป็น a ตั้งแต่ 2 รายการขึ้นไป คือ ช่อง a6,a7,a9 หากมีในแต่ละแถวให้นับแต่ละแถวเป็น 1
รายการ ซึ่งมีทั้งหมด 3 รายการ ในช่อง a1:a10
C11 = 3
6.นับตัวอักษรที่เป็น a ตั้งแต่ 2 รายการขึ้นไป คือ ช่อง a6,a7,a9 มีทั้งหมด 6 รายการ ในช่อง a1:a10
C13 = 6

ขอบคุณครับ

count position.xlsx

[snasui]

จากตัวอย่างคำตอบที่ให้มา เซลล์ C3 นับอย่างไรจึงได้ 9 ครับ

[Totem]

อาจารย์ ครับ

แก้ไขใหม่แล้ว c3 = 11 ครับ

[snasui]

ตัวอย่างสูตร

C3 =SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"a","")))
C5 =SUMPRODUCT(--(MID(A1:A10,1,1)="a"),--(MID(A1:A10,3,1)="b"))
C11 =SUMPRODUCT(--(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"a",""))>=2))

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

[Totem]

ตัวอย่างสูตร

C3 =SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"a","")))
C5 =SUMPRODUCT(--(MID(A1:A10,1,1)="a"),--(MID(A1:A10,3,1)="b"))
C11 =SUMPRODUCT(--(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"a",""))>=2))

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

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

ที่ช่อง c5 = 0 ที่ถูกต้องคือ 1 ค้นหา a ตำแหน่งที่ 1 และ b ตำแหน่งที่ 3 พบ 1 รายการคือ a1b3

ที่ช่อง c9 = 7 ที่ถูกต้องคือ 2 ได้ค่านี้เพราะหาค่า 3 ในตำแหน่งที่3 เริ่มหาจาก e>0>3 , d>0>3 มี 2 รายการ

ที่ช่อง c13 = 3 ที่ถูกต้องคือ 6 ได้ค่านี้เพราะ มีค่า a ตั้งแต่ 2 รายการขึ้นไป คือ

ช่อง a6 มี a1,a1 นับ 2

ช่อง a7 มี a1,a1b3 นับ 2

ช่อง a9 มี a1,a2 นับ 2

ขอบคุณครับ

count position question.xlsx

[snasui]

ค่อย ๆ ถามตอบกันไปครับ

ที่ช่อง c5 = 0 ที่ถูกต้องคือ 1 ค้นหา a ตำแหน่งที่ 1 และ b ตำแหน่งที่ 3 พบ 1 รายการคือ a1b3

จากไฟล์ที่แนบมา ไม่ทราบว่าเซลล์ใดมีค่าเป็น a1b3 ครับ คำว่า "ตำแหน่ง" จะต้องนับทุกอักขระในเซลล์นั้น ๆ ไม่ว่าจะเป็น "คอมม่า" หรือเครื่องหมายอื่น ๆ ครับ

[Totem]

ค่อย ๆ ถามตอบกันไปครับ

ที่ช่อง c5 = 0 ที่ถูกต้องคือ 1 ค้นหา a ตำแหน่งที่ 1 และ b ตำแหน่งที่ 3 พบ 1 รายการคือ a1b3

จากไฟล์ที่แนบมา ไม่ทราบว่าเซลล์ใดมีค่าเป็น a1b3 ครับ คำว่า "ตำแหน่ง" จะต้องนับทุกอักขระในเซลล์นั้น ๆ ไม่ว่าจะเป็น "คอมม่า" หรือเครื่องหมายอื่น ๆ ครับ

คอมม่า , นั้น เป็นการแบ่งแยกรายการแต่ละรายการออกจากกันครับ ผมจึงนับตำแหน่งจากแต่ละคอมม่า
ส่วน a1b3 อยู่ในเซล a6 ครับ

[snasui]

C5 เป็นการหาค่าตามโจทย์

2. นับตัวอักษรที่เป็น a ในตำแหน่งที่ 1 และ b ในตำแหน่งที่ 3 คือ a1b3 ในช่อง a1:a10

สูตรคือ =SUMPRODUCT(--(MID(A1:A10,1,1)="a"),--(MID(A1:A10,3,1)="b"))

จากที่แจ้งมา

ส่วน a1b3 อยู่ในเซล a6 ครับ

ใน A6 คือค่า a1,a1,e03 ไม่ตรงกับที่อธิบายมา แม้จะเอาเครื่องหมายคอมม่าออกให้เหลือเป็น a1a1e03 ก็ตาม

จากค่าใน A6 ด้านบน ช่วยอธิบายมาใหม่ว่า มีกี่ตำแหน่ง แต่ละตำแหน่งคืออะไรบ้าง จะได้เข้าใจตรงกันครับ

[Totem]

C5 เป็นการหาค่าตามโจทย์

2. นับตัวอักษรที่เป็น a ในตำแหน่งที่ 1 และ b ในตำแหน่งที่ 3 คือ a1b3 ในช่อง a1:a10

สูตรคือ =SUMPRODUCT(--(MID(A1:A10,1,1)="a"),--(MID(A1:A10,3,1)="b"))

จากที่แจ้งมา

ส่วน a1b3 อยู่ในเซล a6 ครับ

ใน A6 คือค่า a1,a1,e03 ไม่ตรงกับที่อธิบายมา แม้จะเอาเครื่องหมายคอมม่าออกให้เหลือเป็น a1a1e03 ก็ตาม

จากค่าใน A6 ด้านบน ช่วยอธิบายมาใหม่ว่า มีกี่ตำแหน่ง แต่ละตำแหน่งคืออะไรบ้าง จะได้เข้าใจตรงกันครับ

ขออภัยครับ ที่ถูกต้องคือในเซล a7 ครับ

ส่วน a6 อธิบายว่า มี 3 รายการ a1 , a1 และ e03 ในแต่ละรายการ การนับตำแหน่งให้นับแยกกัน ดังนี้
รายการที่ 1 a1 นับจากซ้ายไปขวา a คือตำแหน่งที่หนึ่ง และ 1 คือตำแหน่งที่สอง
รายการที่ 2 a1 นับจากซ้ายไปขวา a คือตำแหน่งที่หนึ่ง และ 1 คือตำแหน่งที่สอง
รายการที่ 3 e03 นับจากซ้ายไปขวา e คือตำแหน่งที่หนึ่ง , 0 คือตำแหน่งที่สอง และ 3
คือตำแหน่งที่สาม

ขอบคุณครับ

[snasui]

การนับลักษณะนั้นคงต้องพึ่ง VBA ลองเขียนมาดูก่อน ติดตรงไหนค่อยถามกันต่อครับ

[Totem]

การนับลักษณะนั้นคงต้องพึ่ง VBA ลองเขียนมาดูก่อน ติดตรงไหนค่อยถามกันต่อครับ

ครับ จะลองดูครับ

[Totem]

ผมเขียน vba และยังติดปัญหาดังนี้ครับ

Code: Select all

Sub Len_()

    Dim ws1, ws2 As Worksheet
    Dim lastRow As Long
    Dim i As Integer

    Set ws1 = Worksheets("sheet1")
    
    Set ws2 = Worksheets("sheet2")
    
    
 With ws1
 
            .AutoFilterMode = False

            .Range("A1:A1").AutoFilter

            .Range("A:A").AutoFilter Field:=1, Criteria1:="*a?b*"
            
            .Range("A1").CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy
            
With ws2

            If .Range("A1") <> "" Then
            
            .Range("a" & .Rows.Count).End(xlUp).Offset(1, 0) _
            .PasteSpecial Paste:=xlPasteValues
            
        Else
        
            .Range("A1:A1").Value = ws1.Range("A1:A1").Value
            .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0) _
            .PasteSpecial Paste:=xlPasteValues
                
        End If

        Application.CutCopyMode = False
        
        ws1.AutoFilterMode = False
    

    lastRow = ws2.Range("a" & ws2.Rows.Count).End(xlUp).Row
 
            For i = 2 To lastRow
     
        If .Cells(i, "a") <> "" Then

             .Cells(i, 2).FormulaR1C1 = "=LEN(R[0]C[-1])-(LEN(SUBSTITUTE(R[0]C[-1],"","","""")))+1"
               
        End If
     
    Next i
    
            .Range("a" & lastRow + 1) = "Total:"
    
            .Range("b" & lastRow + 1) = Application.WorksheetFunction.Sum(.Range("b1:b" & lastRow))
    
            .Range("b" & lastRow + 1).Font.Bold = True
        
        End With
            
    End With
  
End Sub

ปัญหาตรงสูตร

Code: Select all

.Cells(i, 2).FormulaR1C1 = "=LEN(R[0]C[-1])-(LEN(SUBSTITUTE(R[0]C[-1],"","","""")))+1"

ไม่ได้คำคำตอบทึ่ีถูกต้อง ที่ถูกต้องอยู่ใน เซล c2:c7 ครับ
คือ รายการที่ต้องการนับเฉพาะ "*a?b*"

Len.xlsm

[snasui]

ตัวอย่างการเขียน Function ขึ้นมาใช้เอง ตามด้านล่างครับ

Code: Select all

Function LenAB(r As Range) As Long
    Dim t As Variant
    Dim i As Integer
    Dim icount As Integer
    t = Split(r, ",")
    For i = 0 To UBound(t)
        If t(i) Like "*a?b*" Then
            icount = icount + 1
        End If
    Next
    LenAB = icount
End Function

จากนั้นเซลล์ C2 คีย์สูตร

=LenAB(A2)

Enter > Copy ลงด้านล่าง

[Totem]

ตัวอย่างการเขียน Function ขึ้นมาใช้เอง ตามด้านล่างครับ

Code: Select all

Function LenAB(r As Range) As Long
    Dim t As Variant
    Dim i As Integer
    Dim icount As Integer
    t = Split(r, ",")
    For i = 0 To UBound(t)
        If t(i) Like "*a?b*" Then
            icount = icount + 1
        End If
    Next
    LenAB = icount
End Function

จากนั้นเซลล์ C2 คีย์สูตร

=LenAB(A2)

Enter > Copy ลงด้านล่าง

ขอบคุณครับ ที่สละเวลาตอบปัญหาตอนดึกมาก
จากสูตรที่ให้มาใน Function ที่เขียนบน vba ได้ตามที่ต้องการครับ

[Totem]

เรียนอาจารย์และเพื่อนสมาชิกทุกท่าน

หากต้องการใช้เป็น code vba ที่ไม่ได้เขียนเป็นแบบ Function ผมลองปรับจากที่อาจารย์ให้มาแล้วได้ดังนี้

Code: Select all

Sub len_L()

Dim t As Variant
Dim i As Integer
Dim icount As Integer
Dim r As Range
Dim lastRow As Long

     
Sheets("sheet1").Select
     
            lastRow = Range("a" & Rows.count).End(xlUp).Row
    
    For Each r In Range("a2", Range("a" & Rows.count).End(xlUp))
    
        t = Split(r, ",")
        
         For i = 0 To UBound(t)
         
            If t(i) Like "*a?b*" Then
                
                icount = icount + 1
                
            End If
            
        Next i
        
            r.Offset(, 1).Value = icount
            
    Next r
    
            Range("a" & lastRow + 1) = "Total:"
    
            Range("b" & lastRow + 1) = Application.WorksheetFunction.Sum(Range("b1:b" & lastRow))
    
            Range("b" & lastRow + 1).Font.Bold = True
            
    
End Sub

ติดปัญหาตรงที่

Code: Select all

 icount = icount + 1

และ

Code: Select all

r.Offset(, 1).Value = icount

เมื่อให้แล้วเป็นกันรวมจำนวนแบบสะสม ตั้งแต่ เซล b2:b7 ลงมาด้านล่าง ซึ่งที่ต้องการเป็นการรวมแบบแยกเซล
เลยอย่างให้ช่วยปรับ code ครับ (อยากเขียนแบบไม่ใช้ Function ดูครับ)

ขอบคุณครับ

Len question.xlsm

[snasui]

ตัวอย่างการปรับ Code ตามด้านล่างครับ

Code: Select all

'Other code
t = Split(r, ",")
icount = 0
For i = 0 To UBound(t)
    If t(i) Like "*a?b*" Then
        icount = icount + 1
    End If
Next i
'Other code

[Totem]

ตัวอย่างการปรับ Code ตามด้านล่างครับ

Code: Select all

'Other code
t = Split(r, ",")
icount = 0
For i = 0 To UBound(t)
    If t(i) Like "*a?b*" Then
        icount = icount + 1
    End If
Next i
'Other code

ได้ทั้งความรู้และแก้ไขปัญหาสำเร็จเรียบร้อยแล้วครับ ขอบคุณครับ