การหาค่ามากที่สุดของแต่ละกลุ่มของข้อมูลใน Excel โดย VBA

[Kanok]

ในคอลัมภ์ A จะแสดงชื่อกลุ่มของข้อมูล
คอลัมภ์ B จะแสดงค่าของข้อูลแต่ละตัว
คอลัมภ์ C ให้แสดงค่าที่มากที่สุดของแต่ละกลุ่มข้อมูล
ขั้นตอนการเขียน code
#1) ทำการจัดเรียงข้อมูลโดยใช้การบันทึกมาโคร
#2) กำหนดให้การเปรียบเทียบค่าของ Cells(i, 2).Value และ Cells(i + 1, 2)
ถ้า ค่าCells(i, 2).Value > Cells(i + 1, 2) ให้แสดงค่า ของ Cells(i, 2) ที่ตำแหน่งแถวเดียวกันของคอลัมน์ C
เนื่องจากโดยปกติแล้วค่าที่ได้ในช่วงแรกจะมีค่าน้อย ดังนั้นจึงเอาเงื่อนไขนี้เป็นเกณฑ์ ในการหาค่ามากที่สุดของแต่ละกลุ่ม
แต่ในบางกรณีที่มีกลุ่มข้อมูลที่มีค่ามากผิดปกติ เราไม่สามารถหาค่ามากสุดของกลุ่มข้อมูลนั้นได้ เนื่องจากค่าแรกของกลุ่มข้อมูลนั้นมีค่ามากกว่าค่าที่อยู่ในแถวก่อนหน้านั้น
ตัวอย่าง ค่าของข้อมูลใน cells(11,2) มีค่า เท่ากับ 30 และค่าของข้อมูล cells(12,2) มีค่าเท่ากับ100 ซึ่งในกรณีนี้จะไม่ตรงตามเงื่อนไข ทำให้ไม่สามารถดึงค่ามากที่สุดของกลุ่มข้อมูล SW-16 ได้ ค่ะ
จึงอยากขอคำแนะนำเกี่ยวกับการแก้ไขสูตร หรือฟังก์ชั่นอื่นๆที่ช่วยในการหาค่า Maximun ค่ะ

Find Max Value Y161011.jpg (67.5 KiB) Viewed 190 times

หมายเหตุ : เนื่องจากีข้อมูลเป็นจำนวนมากกว่าหนึ่งพันค่า จึงอยากจะใช้ เขียน vba เพื่อช่วยให้เร็วขึ้นค่ะ

Code: Select all

Sub Findmax1()

'Sort Data

    Range("A1:B15").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A15") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B2:B15") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:B15")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
'Start fine max

Dim i As Long
Dim LabX As Long
Dim MaxT As Long

lastrow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To lastrow

   If Cells(i, 2).Value > Cells(i + 1, 2).Value Then

       Cells(i, 3).Value = Cells(i, 2).Value
   End If
   
Next i

End Sub

[snasui]

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

Code: Select all

Dim i As Long
Dim lastRow As Long
Dim countRow As Long
Dim orderRow As Long

lastRow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lastRow
    orderRow = Application.Match(Cells(i, 1), Range(Cells(2, 1), Cells(lastRow, 1)), 0) + 1
    countRow = Application.CountIf(Range(Cells(2, 1), Cells(lastRow, 1)), Cells(i, 1))
   If Cells(i, 1).Value <> Cells(i + 1, 1).Value Then
        Cells(i, 3).Value = Application.Max(Cells(orderRow, 2).Resize(countRow))
   End If
Next i

[Kanok]

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

Code: Select all

Dim i As Long
Dim lastRow As Long
Dim countRow As Long
Dim orderRow As Long

lastRow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lastRow
    orderRow = Application.Match(Cells(i, 1), Range(Cells(2, 1), Cells(lastRow, 1)), 0) + 1
    countRow = Application.CountIf(Range(Cells(2, 1), Cells(lastRow, 1)), Cells(i, 1))
   If Cells(i, 1).Value <> Cells(i + 1, 1).Value Then
        Cells(i, 3).Value = Application.Max(Cells(orderRow, 2).Resize(countRow))
   End If
Next i

ขอบคุณมากค่ะ สำหรับตัวอย่างในการปรับ Code

อยากจะทำความเข้าใจเพิ่มเติมเกี่ยวกับโค้ดค่ะ อาจจะนำไปปรับใช้กับตารางอื่นๆ ที่คล้ายๆกัน
#1)
" orderRow = Application.Match(Cells(i, 1), Range(Cells(2, 1), Cells(lastRow, 1)), 0) + 1 "
- คำสั่งในบรรทัดนี้จะทำการหาตำแหน่งขอข้อมูลเราแต่ละละตัว ว่าอยู่ที่ แถวไหนของคอลัมภ์ที่ 1 ใช่หรือเปล่าคะ
- ความหมายของ +1 ต่อท้าย มีความหมายว่าอย่างไรคะ
- จากตัวอย่างในรูปภาพ การวนลูปของข้อมูลชุดแรก(FR-04) คำตอบที่ได้จะเป็นค่า 2 หรือว่า 2 ถึง 5 คะ

#2)
" countRow = Application.CountIf(Range(Cells(2, 1), Cells(lastRow, 1)), Cells(i, 1)) "
- คำสั่งในบรรทัดนี้จะทำการนับจำนวนแถวที่มีข้อมูลซ้ำกัน ใช่หรือเปล่าคะ

#3)
" Cells(i, 3).Value = Application.Max(Cells(orderRow, 2).Resize(countRow)) "
- .Resize(countRow) มีความหมายหรือมีหน้าที่อย่าไรคะ

ปล. เพิ่งเริ่มศึกษา Excel VBA ด้วยตนเอง จึงอยากรู้ว่าความเข้าใจของเราถูกต้องหรือไม่ รบกวนช่วยชี้เเนะด้วยค่ะ

[DhitiBank]

ขออนุญาตช่วยอธิบายในส่วนที่ผมพอเข้าใจนะครับ

อยากจะทำความเข้าใจเพิ่มเติมเกี่ยวกับโค้ดค่ะ อาจจะนำไปปรับใช้กับตารางอื่นๆ ที่คล้ายๆกัน
#1)
" orderRow = Application.Match(Cells(i, 1), Range(Cells(2, 1), Cells(lastRow, 1)), 0) + 1 "
- คำสั่งในบรรทัดนี้จะทำการหาตำแหน่งขอข้อมูลเราแต่ละละตัว ว่าอยู่ที่ แถวไหนของคอลัมภ์ที่ 1 ใช่หรือเปล่าคะ
- ความหมายของ +1 ต่อท้าย มีความหมายว่าอย่างไรคะ
- จากตัวอย่างในรูปภาพ การวนลูปของข้อมูลชุดแรก(FR-04) คำตอบที่ได้จะเป็นค่า 2 หรือว่า 2 ถึง 5 คะ

- ใช่ครับ
- ที่ต้อง +1 เพื่อไปรวมผลจากการ Match แล้วนำไปใช้ในโค้ด Application.Max ครับ ในการวนลูปครั้งแรกจะเริ่มจากบรรทัดที่ 2 ดังนั้นการ Match ก็จะเริ่มที่ Cells(2,1) ซึ่งก็คือ FR-04 โดยมองหาในช่วง A2:A... (สุดท้าย) ผลจากการ Match ครั้งแรกนี้จะเท่ากับ 1 แต่เมื่อพูดถึงตำแหน่งในแผ่นงานมันคือบรรทัดที่ 2 ดังนั้นจึง +1 ครับ

#2)
" countRow = Application.CountIf(Range(Cells(2, 1), Cells(lastRow, 1)), Cells(i, 1)) "
- คำสั่งในบรรทัดนี้จะทำการนับจำนวนแถวที่มีข้อมูลซ้ำกัน ใช่หรือเปล่าคะ

- ใช่ครับ กรณี FR-04 คำสั่งนี้จะทำให้ตัวแปร countRow มีค่าเท่ากับ 4

#3)
" Cells(i, 3).Value = Application.Max(Cells(orderRow, 2).Resize(countRow)) "
- .Resize(countRow) มีความหมายหรือมีหน้าที่อย่าไรคะ

- คำสั่ง Resize เป็นการปรับขนาดของ Range ครับ Range ในกรณีนี้คือ Cells(orderRow,2) ถ้าดูจากตัวอย่างนี้ การวนลูปรอบที่ 4 จะทำให้คำสั่ง If Cells(4, 1).Value <> Cells(5, 1).Value Then เป็นจริง
ก็จะได้ว่า
Cells(4,3).value = Application.Max(Cells(2,2).Resize(4)) คือ D4 จะมีค่าเท่ากับค่า Max ในช่วง B2:B5 ครับ

ลองดู resize เพิ่มเติมที่นี่ครับ Resize

[Kanok]

ขอบคุณมากค่ะ