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

½Ð±Ð¡G¦Û°Ê±Æ¯ZEXCEL¦p¦ó³B²z

½Ð±Ð¡G¦Û°Ê±Æ¯ZEXCEL¦p¦ó³B²z

¥»©«³Ì«á¥Ñ gaishutsusuru ©ó 2016-3-25 22:27 ½s¿è

·Q°µ­Óexcel ±Æ¯Z¥\¯à¡A»¡©ú¦p¤U¡G

(¹Ï¤@)
1.JPG
2016-3-25 22:25

¤W¯Z¶}©l¤é¡B¤W¯Zµ²§ô¤é¡B¶}©l¯Z§O¡G³o¤T­Ó¥Ñ½Ð°²¤è¶ñ¼g
¶À¦â³¡¥÷¡G§Ú¤è¶ñ¼g­n¦w±Æªº¤H­û
µù¡G¶}©l¯Z§O¡G¨Ì1¡÷2¡÷3¡÷1¡÷2¡÷3 ......    ³o¤TºØ¼Ò¦¡´`Àô¥h½ü

¤@¥¹¶À¦â³¡¥÷¶ñ¤W«á¡A·|¦Û°Ê¥X²{¦p¹Ï¤Gªº¸ê®Æ

(¹Ï¤G)
2.JPG
2016-3-25 22:19

½Ð±Ð¡A«D²ÊÅé¦rªº³¡¥÷¡A¸Ó¦p¦ó¼g¤½¦¡¡A¤~¯à¦³³oºØ¥\¯à©O?

§Ú·Q¤F¦n¤[¡AÁÙ¬O¤£À´¡C©Ò¥H½Ð±Ð¦U¦ìexcel¤j¤jªº·Qªk¡AÁÂÁ¡C

¥»©«³Ì«á¥Ñ luhpro ©ó 2016-3-26 01:22 ½s¿è

¦^´_ 1# gaishutsusuru
³o¥i¥Î Dictionary ¨ç¼Æ¥ý«Ø¥ß¬ÛÃö¦ì¸m¯Á¤Þ«á·|¸û®e©ö°µ.

¯à¹ê°µ.jpg
2016-3-26 01:17
  1. Private Sub cbClr_Click()
  2.   Range([H2], [J11]).Clear
  3. End Sub

  4. Private Sub cbGet_Click()
  5.   Dim iCol%, iNo%
  6.   Dim lRow&
  7.   Dim dDate As Date
  8.   Dim vDd, vDp
  9.   
  10.   Set vDd = CreateObject("Scripting.Dictionary")
  11.   Set vDp = CreateObject("Scripting.Dictionary")
  12.   
  13.   lRow = 2
  14.   While Cells(lRow, 7) <> ""
  15.     vDd(CStr(Cells(lRow, 7))) = lRow
  16.     lRow = lRow + 1
  17.   Wend
  18.   
  19.   iCol = 8
  20.   While Cells(1, iCol) <> ""
  21.     vDp(CStr(Cells(1, iCol))) = iCol
  22.     iCol = iCol + 1
  23.   Wend
  24.   
  25.   lRow = 2
  26.   While Cells(lRow, 2) <> ""
  27.     dDate = Cells(lRow, 2)
  28.     iNo = Cells(lRow, 4)
  29.     While dDate <= Cells(lRow, 3)
  30.       Cells(vDd(CStr(dDate)), vDp(CStr(Cells(lRow, 5)))) = iNo
  31.       iNo = iNo + (iNo = 3) * 3 + 1
  32.       dDate = dDate + 1
  33.     Wend
  34.     lRow = lRow + 1
  35.   Wend
  36. End Sub
½Æ»s¥N½X
¯à¹ê°µ.zip (28.82 KB)

TOP

http://blog.xuite.net/hcm19522/twblog/395556234
G2:I11{=IFERROR(MOD(LOOKUP(1,0/(($F2>=$A$2:$A$7)*($F2<=$B$2:$B$7)*($D$2:$D$7=G$1)),$C$2:$C$7)-2+ROW(A1),3)+1,"")

TOP

¦^´_ 1# gaishutsusuru

­É2¼ÓÀɮ׮榡

H2°}¦C¤½¦¡
{=TEXT(MIN(IF((H$1=$E$2:$E$7)*($B$2:$B$7+COLUMN($A:$Z)-1=$G2)*($G2<=$C$2:$C$7),MOD($D$2:$D$7+COLUMN($A:$Z)+1,3)+1)),"[>];")}

»Ý¥Î¤TÁä(SHIFT+CTRL+ENTER)»ô«ö¿é¤J¤½¦¡
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

«D±`ÁÂÁ¦U¦ìªº·N¨£·Qªk¡A¯uªº«Ü¦³À°§U¡C
ps. ¦]¬°­Ó¤HÅv­­¤£¨¬(¥u¬O¤p¾Ç¥Í)¡A©Ò¥H¤£¯à¤U¸ü¡u¯à¹ê°µ.zip (28.82 KB)¡v¡A¤£ª¾¦³¨S¦³¨ä¥L¤è¦¡¥i¥H¤U¸ü? ÁÂÁÂ

TOP

¦^´_ 5# gaishutsusuru

http://www.FunP.Net/889679
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

«D±`ÁÂÁ¦U¦ìªº·N¨£·Qªk¡A¯uªº«Ü¦³À°§U¡C
ps. ¦]¬°­Ó¤HÅv­­¤£¨¬(¥u¬O¤p¾Ç¥Í)¡A©Ò¥H¤£¯à¤U¸ü¡u¯à¹ê°µ.zip ( ...
gaishutsusuru µoªí©ó 2016-3-26 22:06


¯à¹ê°µ.zip (28.82 KB)

TOP

¢Ö¢±¡D°}¦C¤½¦¡¡G
=SUBSTITUTE(MAX((MOD($G2-$B$2:$B$7+$D$2:$D$7-1,3)+1)*($B$2:$B$7<=$G2)*($C$2:$C$7>=$G2)*($E$2:$E$7=H$1)),0,)
©Î¡G
=TEXT(MAX((MOD($G2-$B$2:$B$7+$D$2:$D$7-1,3)+1)*($B$2:$B$7<=$G2)*($C$2:$C$7>=$G2)*($E$2:$E$7=H$1)),"[>];")

TOP

        ÀR«ä¦Û¦b : ª¾ÃÑ­n¥Î¤ßÅé·|¡A¤~¯àÅܦ¨¦Û¤vªº´¼¼z¡C
ªð¦^¦Cªí ¤W¤@¥DÃD