lcctno

¦^´_ 28# azrael19
·PÁ±zªº´£¨Ñ ¦ýùØ­±¨S¥ô¦ó¨ç¼Æ
µ²ªG·íµM¨S¦³§ïÅÜ

¤U¸ü (220.13 KB)

2015-8-28 15:37

¤U¸ü (162.02 KB)

2015-8-28 15:40

lcctno

¦^´_ 29# ­ã´£³¡ªL

ÁÂÁ±z ¦ý¨º¤£¬O§Ú­nªº §Ú¥Ø«e³£¯à«Ü¥¿±`¥BÀɮפ]Åܪº«Ü¤p ¥u¬O·Q§â¥¦§Ë±o§ó¦³¤H©Ê¤Æ

lcctno

¦^´_ 30# azrael19
¯uªº¬O°ª¤â ­ì¨Ó¬O¥Î¨ç¼Æ¼gªº §ÚÁÙ¥H¬°±zªºvba§Ñ¤F¦s­ìÀɮפº
¦ýµ²ªG¬O¤£¤Ó¤@¼Ë? À³¸Ó¬O¤ñ¹ï¤è¦¡¤£¦P³y¦¨ªº §Ú¦³±N¤ñ¹ï¤§¨ç¼Æ¼g¦bªþµù¤º  ·PÁ±z  ±z¨ºÀÉ°õ¦æ³t«×¶W§Öªº §Æ±æ±z¯à§ä¥X­ì¦]

³o¬O§Úªº¨Ï¥Î²{ª¬

¤U¸ü (299.11 KB)

2015-8-28 16:22

¤w¸g¥[¤Wdata¤§ÀÉ®×
~test% - TT3.rar (154.61 KB)

¤U¸ü¦¸¼Æ: 7

2015-8-28 16:23

lcctno

¦^´_ 30# azrael19

¥ý»¡Án :¹ï¤£°_
°ª¤â±zªºOK ¬O§Ú¨S¦³§¹¥þ°õ¦æ§¹ (¦³§ó§ï¶¡¶Z=±z³]ªº­È) ¦ý§Ñ¤F°µ¾ã®Mªº¤ñ¹ï ©Ò¥H¤~·|¼Æ¾Ú¤£¦P ¤§«e¦^¶Ç©ó±z¤§µ{¦¡ °õ¦æµ²ªG»P§Úªº§¹¥þ¬Û¦P
§Ú¦A¦h¸Õ¤@¨Ç¸ûªø®É¶¡ªº´ú¸Õ §Ú·QÀ³¸Óµ²ªG¤]·|¬Û¦P
¯uªº¨S·Q¨ì¥Î¨ç¼Æ´N¥i¥H§¹¦¨

¤U¸ü (288.02 KB)

2015-8-28 16:55

lcctno

¦^´_ 30# azrael19

¥Ø«e¦³µo²{­Ó«Ü¤pªº°ÝÃD ¥u¬O§Ú¨S¯à¤O­×§ï¨º»ò½ÆÂøªº¨ç¼Æ
¥Ø«e¸g¹L¼ÆºØ¤è¦¡ªº¤ñ¹ï "data"¤º¤§Á`¼Æ »P"µ²ªG"¤º¤§µo¥Í¦¸¼Æ ³£§¹¥þ¤@¼Ë ¦ý²Î­p«á¤§"¥Ø«eªÑ»ù²Ö­pµo¥Í²v"¦³¤p³\ªº®t¶Z ¥i¯à¬Oºâ¦¡¤£¦P©Ò­P
¥i§_³Â·Ð±z±N"¥Ø«eªÑ»ù²Ö­pµo¥Í²v" §ï¦¨¦pªþ¥ó¤W©ñªº¤è¦¡ ¨Æ¹ê¤W§Ú¤]µo²{¤§«e§Ú¥Îªº­pºâ"¥Ø«eªÑ»ù²Ö­pµo¥Í²v"ªº¤è¦¡¤£¤Ó¦n ÁÙ¦n³£¥u¦³¤p¼ÆÂI¥H¤Uªº»~®t

ÁÂÁ±zÀ°¤F§Úªº¤j¦£



    ~test% - TT4.rar (202.79 KB)

¤U¸ü¦¸¼Æ: 7

2015-8-28 18:41

azrael19

¦^´_  azrael19

¥Ø«e¦³µo²{­Ó«Ü¤pªº°ÝÃD ¥u¬O§Ú¨S¯à¤O­×§ï¨º»ò½ÆÂøªº¨ç¼Æ
¥Ø«e¸g¹L¼ÆºØ¤è¦¡ªº¤ñ¹ï "da ...
lcctno µoªí©ó 2015-8-28 18:42

¸Õ¸Õ¬Ý...
~test% - TT5.zip (272.72 KB)

¤U¸ü¦¸¼Æ: 10

2015-8-28 22:02

lcctno

¦^´_ 36# azrael19
·PÁ±z ¥Ø«e¥u´ú¸Õ2­Ó¤£¦P±Ú¸sªºdata §Ú«Ü°ª¿³ µ²ªG"§¹¥þ¥¿½T" ±zªº¨ç¼Æ¯uªº¤Ó±j¤F

­Y¤è«Kªº¸Ü ¥i§_³Â·Ð¨Ï¥Î³Ì¥Õ¸Üªº¤è¦¡ §i¶D§C¯àªº§Ú ±z¦b"µ²ªG"¤À­¶¤º ¨º3­Ó¨ç¼Æªº·N«ä

°Ï¶¡=COUNTA(A:A)-1  ­È¬O¥¿½T ¦ý¬°¦ó¬O-1 ¦Ó¤£¬O-2( ¦]¬°§Ú¬Ý¨ì¦³2­Ó¬O«D¼Æ¦r¦C) ?

¸ê®Æ¦C_³Ì°ª»ù '=OFFSET(INDIRECT(ADDRESS(IF(Data!$I$6="",2,Data!$I$6),3,,,"Data")),,,IF(Data!$J$6="",COUNTA(INDIRECT("Data!$A:$A"))-1,Data!$J$6-IF(Data!$I$6="",2,Data!$I$6)+1),) ³o­Ó§Ú§¹¥þ¤£ª¾¹D¬O¤°»ò

¸ê®Æ¦C_³Ì§C»ù '=OFFSET(¸ê®Æ¦C_³Ì°ª»ù,,1,,) ³o­Ó§Ú¤]§¹¥þ¤£ª¾¹D¬O¤°»ò

ÁÂÁ±z¤F

azrael19

¦^´_  azrael19
·PÁ±z ¥Ø«e¥u´ú¸Õ2­Ó¤£¦P±Ú¸sªºdata §Ú«Ü°ª¿³ µ²ªG"§¹¥þ¥¿½T" ±zªº¨ç¼Æ¯uªº¤Ó±j¤F

­Y ...
lcctno µoªí©ó 2015-8-28 23:14

1.COUNTA¬O­pºâ½d³ò¤¤¤£¬OªÅ¥ÕªºÀx¦s®æ¼Æ¥Ø¡A-1´N¬O´î±¼Àx¦s®æA2¼ÐÃD«ü¼Æ(ªÑ»ù)¡C
2.¨ä¹ê²Õ¦X¤½¦¡¬Ý¤£À´®É§A¥i¥H¥ý¹Á¸ÕµÛ¤À©î¦¨¤@¤p¬q¤è¦¡¨Ã¿é¤JÀx¦s®æ¬Ý¿é¥Xµ²ªG´N«Ü¦n²z¸Ñ¡A¨Ò¦p:
(a)
  ¤½¦¡: =ADDRESS(8,6) ´Nµ¥©ó¦r¦ê "$F$8"¡C
  ¤½¦¡: =ADDRESS(8,6,,,"Data") ´Nµ¥©ó¦r¦ê "Data!$F$8"¡C
(b)·íData!I6 ­È¬O18®É:
  ¤½¦¡: =ADDRESS(Data!$I$6,3,,,"Data") ´Nµ¥©ó¦r¦ê "Data!$C$18"¡C
  ¤½¦¡: =INDIRECT(ADDRESS(Data!$I$6,3,,,"Data")) ´N¬O°Ñ·ÓÀx¦s®æ Data!C18¡C
(c)·íData!I6 ­È¬O7¡AData!J6­È¬O306
  ¤½¦¡: =OFFSET(INDIRECT(ADDRESS(Data!$I$6,3,,,"Data")),,,Data!$J$6-Data!$I$6+1,)
  ´N¬O«ü¦VÀx¦s®æ½d³ò Data!C7:C306¡C
(d)¤½¦¡¤¤¨Ï¥Î¨ìIF¨ç¼Æ³¡¤À´N¬O°w¹ïData!$I$6©ÎData!$J$6¬OªÅ¥Õ®Éªº¨Ò¥~³B²z¤è¦¡¡C
3.¨Ò¦p¦Û©w¦WºÙ¸ê®Æ¦C_³Ì°ª»ù¬O«ü¦VÀx¦s®æ½d³ò Data!C7:C306
  ¤½¦¡: =OFFSET(¸ê®Æ¦C_³Ì°ª»ù,,1,,) ´N¬O«ü¦VÀx¦s®æ½d³ò Data!D7:D306

°Ñ¦Ò¸ê®Æ:
COUNTA
ADDRESS
INDIRECT
OFFSET
µû¦ô­È¤½¦¡
¦Û­q¦WºÙ

lcctno

¦^´_ 36# azrael19
¸g´ú¸ÕªÑ²¼ ¦³µo²{¤u§@­¶"Data"¤ºH2¤§Á`¼Æ¦³»~
¸g·¥¤p¤Æ¨ÓÀ˵ø µo²{±zº|¤F±Nµ¥©ó¦C¤§­È¨ú¼Ë ¦pªþ¥ó ¦³µµ¦âªº¦a¤è½Ð¬Ý¤@¤U
¬Q¤Ñ§Ú¥u´ú¸Õ«ü¼Æ¨Ã¨Sµo²{»P§Úªº²Î­p¦³²§ ¦ý¤µ¤Ñ´ú¸ÕªÑ»ù®Éµo²{¤F³o°ÝÃD
³Â·Ð½Ð±z±N¨ç¼Æ­×§ï¤@¤U ÁÂÁ±z

~test% - TT6.rar (124.83 KB)

¤U¸ü¦¸¼Æ: 5

2015-8-29 09:18

azrael19

¦^´_  azrael19
¸g´ú¸ÕªÑ²¼ ¦³µo²{¤u§@­¶"Data"¤ºH2¤§Á`¼Æ¦³»~
¸g·¥¤p¤Æ¨ÓÀ˵ø µo²{±zº|¤F±Nµ¥©ó¦C¤§­È ...
lcctno µoªí©ó 2015-8-29 09:19

±Nµ²ªG¤u§@ªí A4 ¤½¦¡¥Ñ=A3+Data!$L$4§ï¦¨=ROUND(A3+Data!$L$4,2)¤U©Ô¦Û°Ê¶ñº¡¤½¦¡