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

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

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

´ú¸ÕÀÉ : ¨ú¤T°Ïªº­«½Æ­È.rar (2.14 KB)
±N$B7:$K8©M$B10:$K11©M$B13:$K14¤T­Ó°Ï°ìªº­«½Æ­È¡A
¥Ñ¤p¦Ó¤j±qB17©¹¥k¶ñ¤J¡C

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

ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

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

TOP

¦^´_ 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^^

TOP

=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)))

TOP

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

TOP

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð
°õ¦æ«e:
20231214_1.jpg
2023-12-14 09:30


°õ¦æµ²ªG:
20231214_2.jpg
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
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

(¿é¤J½s¸¹12138) googleºô§}:https://hcm19522.blogspot.com/
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

        ÀR«ä¦Û¦b : ¤Hªº¤ß¦a¬O¤@²¥¥Ð¡A¤g¦a¨S¦³¼½¤U¦nºØ¤l¡A¤]ªø¤£¥X¦nªºªG¹ê¡C -
ªð¦^¦Cªí ¤W¤@¥DÃD