¨ú¤T°Ïªº­«½Æ­È

papaya

´ú¸ÕÀÉ : ¨ú¤T°Ïªº­«½Æ­È.rar (2.14 KB)

¤U¸ü¦¸¼Æ: 8

2020-3-23 17:14

±N$B7:$K8©M$B10:$K11©M$B13:$K14¤T­Ó°Ï°ìªº­«½Æ­È¡A
¥Ñ¤p¦Ó¤j±qB17©¹¥k¶ñ¤J¡C

½Ð°Ý : B17ªº2003ª©¨ç¼Æ¤½¦¡­n«ç»ò½s¼g ?
ÁÂÁÂ !

hcm19522

https://blog.xuite.net/hcm19522/twblog/589003673

­ã´£³¡ªL

­Y¦U°Ï¦Û¤v¨S¦³­«ÂЭÈ,
°}¦C¤½¦¡:
=TEXT(SMALL(--TEXT(SUMIF($B13:$K14,SUMIF($B7:$K8,$B10:$K11)),"0;;99"),COLUMN(A1)),"[<99]0;;")
¥k©Ô~~

papaya

¦^´_ 2# hcm19522
¦^´_ 3# ­ã´£³¡ªL
·PÁ¤G¦ì¤j¤jªºÀ°¦£©M¸Ñµª!
¤£ª¾¯à§_¤£¥ÎTEXT¡A¦]¬°¤å¦r§ÎºA¡AµLªk®æ¦¡³]©w¡C
--TEXT(.........)¡A¬O¥i¥H¦¨¬°¼Æ­Èªº§ÎºA¡A¦ý¦h©ÔªºÀx¦s®æ(¦]¬°²Å¦X±ø¥óªº¼Æ­È­Ó¼Æ¤£©w)·|Åã¥Ü"#VALUE"
ÁÂÁ¤G¦ì¤j¤j^^

­ã´£³¡ªL

=IF(COLUMN(A1)>SUM(N(COUNTIF($B7:$K14,ROW(1:39))=3)),"",SMALL(IF(COUNTIF($B7:$K14,ROW(1:39))=3,ROW(1:39)),COLUMN(A1)))

papaya

¦^´_ 5# ­ã´£³¡ªL
ª©¥D¤j¤j : ±z¦n!
ÁÂÁ±zªº­@¤ßÀ°¦£!
OK¤F~·P®¦^^

Andy2483

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð
°õ¦æ«e:

¤U¸ü (80.36 KB)

2023-12-14 09:30

°õ¦æµ²ªG:

¤U¸ü (82.67 KB)

2023-12-14 09:30

Option Explicit
Sub TEST()
Dim Brr, Crr(1 To 100), V&, Z, A, i&, N%, xA As Range
'¡ô«Å§iÅܼÆ,CrrÅܼƬO¤@ºû°}¦C,¯Á¤Þ¸¹1~100
Set Z = CreateObject("Scripting.Dictionary")
'¡ô¥OZÅܼƬO ¦r¨å
Set xA = [B7:K8]
'¡ô¥OxAÅܼƬOª«¥óÀx¦s®æ [B7:K8]
For i = 0 To 2
'¡ô³]¶¶°j°é!¥Oi±q0 ¨ì2
   Brr = xA.Offset(i * 3)
   '¡ô¥OBrrÅܼƬO ±a¤J°j°é°Ï°ìÀx¦s®æ­Èªº¤Gºû°}¦C
   For Each A In Brr
   '¡ô³]³v¶µ°j°é!¥OAÅܼƬOBrr°}¦C¸Ìªº°}¦C­È
      If Trim(A) = "" Then GoTo A01 Else V = Val(A)
      '¡ô¦pªGAÅܼƥh°£ÀY§ÀªÅ¥Õ¦r¤¸«á¬O ªÅ¦r¤¸!´N¸õ¨ì¼Ð¥ÜA01¦ì¸mÄ~Äò°õ¦æ,
      '§_«h´N¥OVÅܼƬO AÅܼÆÂনªº¼Æ­È
      If Z(V) = i Then Z(V) = i + 1
      '¡ô¦pªG¥HVÅܼƬdZ¦r¨å¦^¶Çitem­È¬O¦P iÅܼÆ,´N¥O¨äitem­È+1
      If Z(V) = 3 Then N = N + 1: Crr(N) = V: Z(V) = 0
      '¡ô¦pªG¥HVÅܼƬdZ¦r¨å¦^¶Çitem­È¬O3 ,´N¥ONÅܼƲ֥[1,
      '¥O²ÄNÅܼƭÓCrr°}¦C­È¬O ÅܼÆ,¥OZ¦r¨å¸ÌVÅܼÆkey¹ïÀ³ªºitem­È¬O0
A01: Next
Next
With [B18].Resize(, N)
'¡ô¥H¤U¬OÃö©ó[B18]Àx¦s®æÂX®i¦V¥kNÅܼƭÓÀx¦s®æ°Ï°ìªºµ{§Ç
   .Value = Crr
   '¡ô¥O¸Ó°Ï°ìÀx¦s®æ­È¥HCrr°}¦C­È±a¤J
   .Sort KEY1:=.Item(1), Order1:=1, Header:=2, Orientation:=2
   '¡ô¥O¸Ó°Ï°ì°µµL¼ÐÃDªº¾î¦V¶¶±Æ§Ç
End With
End Sub

hcm19522

(¿é¤J½s¸¹12138) googleºô§}:https://hcm19522.blogspot.com/