ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ½Ð°Ý«e½úÃö©óVBA ¤¤¦r¦ê²Î­p°ÝÃD

[µo°Ý] ½Ð°Ý«e½úÃö©óVBA ¤¤¦r¦ê²Î­p°ÝÃD

¦U¦ì«e½ú½Ð°Ý¤@¤U~¦p¤p§Ì¸ê®Æ¤¤"A"Äæ¦ì¦r¦ê¸ê®ÆÅã¥Ü¡B¤p§Ì·Q±N²Å¦X±ø¥ó«á¶i¦æ²Î­p§Y¬° ¦r¦ê(±ø¥ó¶µ¥Ø"-"»Ý²Î­p¥X²{³Ì¦h¦¸¼Æ¥Ø»P¶µ¥Ø)¡ö¶i¦æ¤ñ¸û»P²Î­p¡A
¦]"-"¤§«áªº¶µ¥Ø¬OÀH¾÷¥X²{¡B¤p§Ì¤§«e¦³½Ð±Ð¹L«e½úÃþ¦ü²M³æ¨Ò¥X¶i¦æ²Î­p¡A
¦ý¦¹¦¸¬°²Å¦X¬Y±ø¥ó«á¤~¶i¦æ²Î­p¡A©Ò¥H½Ð±Ð«e½ú­Ì¬O§_¦³¨ä¥¦¤èªk¹³¦p¦¹²Î­p©O¡H


µù¡G1. ¤ñ¸ûdata¨Ó·½ ¡÷ AÄæ¦ì
         2.¤ñ¸û±ø¥ó("G1")
         3.±Nµ²ªG¦C¦Ü("J1" & "J2")¡B

³Â·Ð¦U¦ì«e½úÀ°¦£¤F¡B·PÁ¤j®a¡C
test²Î­p.rar (15.02 KB)

¦^´_ 1# ii31sakura
  1. Sub Test()
  2.   Dim r As Long, sCond As String
  3.   Dim d, sSN As String, sType As String, max, maxType
  4.   
  5.   Set d = CreateObject("scripting.dictionary")
  6.   
  7.   With Sheets("Data")
  8.     sCond = .[G1].Text
  9.     r = .Cells(.Rows.count, "A").End(xlUp).Row - 1
  10.     If r <= 0 Then Exit Sub
  11.     For Each s In .[A2].Resize(r).Value
  12.       sSN = Split(s, "-")(0)
  13.       sType = Split(s, "-")(1)
  14.       If sSN = sCond Then
  15.         d(s) = d(s) + 1
  16.         If d(s) > max Then max = d(s): maxType = sType
  17.       End If
  18.     Next
  19.    
  20.     .[J1].Value = maxType
  21.     .[J2].Value = max
  22.   End With
  23.   
  24. End Sub
½Æ»s¥N½X

TOP

¦^´_ 2# stillfish00
¤j¤j§A¦n:
¹ï©ó dictionary ªº¹B§@, ¤@ª½³£«Ü¦n©_,
¬Ý¤F¤j¤jªº³Ç§@, ¤S¼W¥[¤F´X¤À¤F¸Ñ»P§x´b,
¥ý¦¬¤U¦A¬ã¨s°Õ, ÁÂÁÂ!!

TOP

¦^´_ 2# stillfish00

«Ü·PÁÂstillfish00«e½ú ³o»ò¼ö¤ßÀ°¦£¸Ñ¨M¤p§Ìªº§xÂZ¡B
Åý·Q¤F¦n´X¤Ñªº°ÝÃDÁ`¬O¥i¥H±o¨ìÀ°§U¡A
¦A¤@¦¸¦b¦¹Á¹L«á¤p§Ì¦A¨Ó¦¬¤U¬ã¨s¤F¡C
    ·PÁ¤j¤j~

TOP

¦^´_ 4# ii31sakura


   
²Î­p¥X²{³Ì¦h¦¸¼Æ¥Ø»P¶µ¥Ø
·í¥X²{³Ì¦h¦¸¼Æ¥Ø¤£¥u£¸¶µ®É
  1. Sub Test()
  2.   Dim r As Long, sCond As String
  3.   Dim d As Object, k As Variant
  4.   Set d = CreateObject("scripting.dictionary")
  5.   With Sheets("Data")
  6.     sCond = .[G1].Text
  7.     r = .Cells(.Rows.Count, "A").End(xlUp).Row - 1
  8.     If r <= 0 Then Exit Sub
  9.     For Each s In .[A2].Resize(r).Value
  10.       If sCond = Split(s, "-")(0) Then d(s) = d(s) + 1
  11.     Next
  12.     r = 0
  13.     For Each k In d.keys
  14.         If d(k) = Application.WorksheetFunction.max(d.ITEMS) Then '¥X²{³Ì¦h¦¸¼Æ¥Ø
  15.             With .[J1].Offset(, r)
  16.                .Value = Split(k, "-")(1)
  17.                 .Offset(1).Value = d(k)
  18.             End With
  19.             r = r + 1
  20.         End If
  21.     Next
  22.   End With
  23. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 5# GBKEE

·PÁÂGBKEE«e½ú´£¨Ñ¤p§Ì¥t¤@­Ó¤è¦V¾Þ§@»P«ä¦Ò¡B«ÜÁÂÁÂstillfish00»PGBKEE¨â¦ì«e½úªº¼ö¤ßÀ°¦£~

TOP

        ÀR«ä¦Û¦b : ¡i¥Í©R¦b©I§l¶¡¡j¦òªû»¡¡G¡u¥Í©R¦b©I§l¶¡¡C¡v¤HµLªkºÞ¦í¦Û¤vªº¥Í©R¡A§óµLªk¾×¦í¦º´Á¡AÅý¦Û¤v¥Ã¦í¤H¶¡¡C¬JµM¥Í©R¥h¨Ó³o»òµL±`¡A§Ú­Ì§óÀ³¸Ó¦n¦n¦a·R±¤¥¦¡B§Q¥Î¥¦¡B¥R¹ê¥¦¡AÅý³oµL±`¡BÄ_¶Qªº¥Í©R¡A´²µo¥¦¯uµ½¬üªº¥ú½÷¡A¬M·Ó¥X¥Í©R¯u¥¿ªº»ù­È¡C
ªð¦^¦Cªí ¤W¤@¥DÃD