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

[µo°Ý] ·j´M¨S¦³¥X²{ªº¼Æ¦r

[µo°Ý] ·j´M¨S¦³¥X²{ªº¼Æ¦r

´ú¸ÕÀÉ :   ·j´M¨S¦³¥X²{ªº¼Æ¦r.rar (2.93 KB)

³Æµù¡J
E2:S5=1~39¤§¶¡ªº¼Æ¦r(¦³­«½Æ)
E14:J20=1~39¤§¶¡ªº¼Æ¦r(¨S¦³­«½Æ)

»Ý¨D¡J
X2=¨S¦³¥X²{¦bE2:S5ªº1~39¤§¶¡ªº¼Æ¦r¡A¤@¦¡¤U©Ô¡C
X14=¨S¦³¥X²{¦bE14:J20ªº1~39¤§¶¡ªº¼Æ¦r¡A¤@¦¡¤U©Ô¡C

½Ð±Ð¡JX2©MX14ªº2003ª©¨ç¼Æ¤½¦¡¡C
ÁÂÁ¡I

=IF(ROW(A1)>SUM(N(COUNTIF(E$2:S$5,ROW($1:$39))=0)),"",SMALL(IF(COUNTIF(E$2:S$5,ROW($1:$39))=0,ROW($1:$39)),ROW(A1)))

=IF(ROW(A1)>SUM(N(COUNTIF(E$2:S$5,ROW($1:$39))=0)),"",MATCH(,0/ISNA(RANK(ROW($1:$39),(E$2:S$5,X$1:X1))),))

=IF(ROW(A1)>SUM(N(COUNTIF(E$2:S$5,ROW($1:$39))=0)),"",MATCH(,FREQUENCY((E$2:S$5,X$1:X1),ROW($1:$39)),))

¦P¼Ëªº»Ý¨D, ¦P¼Ëªº¤½¦¡¹B¥Î, ÁÙ¬O­n¤@ª½­«Âаݵª???
~~~~~~~~~~~~~~~~

TOP

¥»©«³Ì«á¥Ñ papaya ©ó 2020-7-23 17:32 ½s¿è

¦^´_ 2# ­ã´£³¡ªL
ª©¥D¤j¤j :
«Ü¤£¦n·N«ä¡A¦³±N¤W¦¸±z¸ÑÃDªº"«ü©w°Ï°ì¤º¦³¦P¼Æ­È=3­Ó"ªº¤½¦¡°µ­×§ï=0¡A¤]±N¾î©ì§ï¬°ª½©ì(COLUMN=>ROW)¡A¦ý¤@ª½¶]¤£¥X¥¿½Tªºµª®×¡A
©Ò¥H¤~¤S¤W¨Óµo°Ý

¸U¤À·PÁ±z­@¤ßªº¦A¦¸À°¦£

TOP

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C,½Ð¦U¦ì«e½ú«ü±Ð
°õ¦æ«e:


°õ¦æµ²ªG:


Option Explicit
Sub TEST()
Dim A$(1 To 39, 0), i%, xR As Range, j%
For Each xR In [E2:S12].SpecialCells(2): A(Val(xR), 0) = xR: Next
For i = 1 To 39
   If A(i, 0) = "" Then j = j + 1: A(j, 0) = Format(i, "00")
Next
[X2].Resize(j) = A
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¬°¦Û¤v§äÂǤfªº¤H¥Ã»·¤£·|¶i¨B¡C
ªð¦^¦Cªí ¤W¤@¥DÃD