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

[µo°Ý] ½Ö¯àÀ°³o´X¥G­«½Æ°Ê§@ªºVBA½G¨­ ÁÂÁÂ

¦^´_ 40# azrael19

¬Ý°_¨Ó«Ü§´·í¤F
§Ú¬Ý±z­n¨D§ïªº³¡¤À ¬O4±Ë5¤J ¬Ý°_¨Ó¨º¨Ç®æ¤º­È¨Ã¨S¦³§ïÅÜ ¦ý¬°¦ó¤ñ¹ïµ²ªG´N¥¿½T¤F???
¥t¥~ ¬°¦ó§Ú¥u­n¶}±Ò±zªºÀÉ®× §Ú¥¼°µ¥ô¦ó°Ê§@«KÃö¤W ¥¦´N·|´£¥Ü¸ê®Æ¤w§ó§ï °Ý§Ú­n¤£­n¦sÀÉ???
¬°¦ó±zªºÀɮ׶W¯Å¤p ¥B°õ¦æ°_¨Ó¤]«Ü§Ö(¸ò§Ú¼gªº¤ñ) ?
±z¯uªº¬O°ª¤â¤¤ªº°ª¤â

ÁÂÁ±z¤F

TOP

¦^´_  azrael19

¬Ý°_¨Ó«Ü§´·í¤F
§Ú¬Ý±z­n¨D§ïªº³¡¤À ¬O4±Ë5¤J ¬Ý°_¨Ó¨º¨Ç®æ¤º­È¨Ã¨S¦³§ïÅÜ ¦ý¬°¦ó¤ñ¹ï ...
lcctno µoªí©ó 2015-8-29 11:58


1.¯BÂI¼Æ»~®t¡A½Ð°Ñ¦Ò https://support.microsoft.com/zh-tw/kb/214118
  ©Ò¥H¤]¥i¥H±N A4¤½¦¡§ï¦¨: =TRUNC(A3+Data!$L$4,2)¡A³o¬O±z­ì©lÀɩҨϥΪº¤è¦¡¡C
2.¨ä¹ê±z©Ò´£¨Ñªº-test% -.zip´N·|¤F¡A³o­Ó§Ú¤]¤£¤Óª¾¹D...
  (¥i¯à¥´¶}Àɮ׮ɪº¹Bºâ¶W¹L¤@©w¼Æ¶q½d³ò©Î¬O¨Ï¥Î¨ì¯S©w¥\¯à´N·|³o¼Ë¡A¨Ò¦p±zªº­ì©lÀɧڥu­n±NWebQuery²¾°£´N¤£·|°Ý¤F...)
3.¦]¬°¨Ï¥ÎªºÀx¦s®æ¤Ö¦ÛµM´N¤p¡A¹Bºâ¤Ö¦ÛµMµ¥«Ý®É¶¡´Nµu¡C

§Ú¤£¬O°ª¤â¡A¦pªG¦³±`¦b½×¾Âª¦¤å¡A¦P®É±Nª©¥D­Ìªº¦^Âп˦۰ʤⰵ¹L¤@¹M´N¥i¥H«Ü§Ö¾Ç¨ì³o¨Ç§Þ¥©¡A¥u¬O¥Î¬Ýªº¤j·§«Ü§Ö´N§Ñ¥ú¤F...
ÁÙ¦³§AÀ³¸Ó­n·PÁ¬O«e­±¦^Âбz°ÝÃDªº«e½ú¡A¦pªG¤@¶}©l´N¥ý±N¤½¦¡Â²¤Æ¦A¨Ó°Ý¦p¦ó¥ÎVBA½G¨­¡A¨º­Ó¹Bºâ³t«×·|¤ñ§Úµ¹§Aªº§Ö¦h¤F

TOP

¦^´_ 42# azrael19
·PÁ±zªº¥Î¤ß §Ú±q¨S¥¿¦¡ªº¤W¹LPC¬ÛÃöªº½Ò ¥u¬°¤F¦w¤ßªº§ë¸êªÑ²¼
©Ò¥H¤~¤gªk·Ò¿û ¥Î³Ì²³æ°ò¥»ªº¨ç¼Æ¼g¤F¼Æ­Ó"¤j¥¨³J"
¨º¨Ç§Ú¼gªº"¤j¥¨³J" ©~µM¦³³æ¤@ÀÉ´N¶W¹L700mb °õ¦æ°_¨Ó ÁÙ¯uºC ©Ò¥H¤]¬O¬°¤F±NÀÉ®×½G¨­¤~¨Ó³Â»¶®a±Ú¨D±Ï
¨Ó¨ì³Â»¶®a±ÚÁÙºâ¯uªº«Ü¨ü¯q ÁÂÁ±z¤j¤OªºÀ°§U§Ú ¯u¤ßªº·PÁ±z
±z»¡¥ÎVBA·|§ó§Ö? ±z§¹¦¨ªº¤w¸g«Ü§Ö¤F §ÚÁÙ¯u·Q¬Ý¬Ývba¯à§Ö¨ì¤°»òµ{«×
±z»¡¦pªG§Ú¦A¥Î±zÀ°§Ú§¹¦¨ªºÀɮצAµo¤@¦¸©« ·|¤£·|³Q¤H·í¦¨Äé¤ôµo©«?

TOP

¦^´_  azrael19
·PÁ±zªº¥Î¤ß §Ú±q¨S¥¿¦¡ªº¤W¹LPC¬ÛÃöªº½Ò ¥u¬°¤F¦w¤ßªº§ë¸êªÑ²¼
©Ò¥H¤~¤gªk·Ò¿û ¥Î³Ì² ...
lcctno µoªí©ó 2015-8-29 14:52


ÁÙ¬O¤£­n¦n¤F¡A¥ý»¡§Ú¨S¦³´c·N±z¬Ý¤F¤d¸U§O»~·|
³o¸Ì¬OÅý¤j®a¾Ç²ß³¨³½ªº§Þ¥©¤Î¤À¨É¾Ç²ß¸gÅç¡A·í¤j®aµo²{§A¥u¬O·Q½Ð¤HÀ°§A³¨³½¡A«Ü§Ö§Aªº°ÝÃD´N±o¤£¨ì¥ô¦ó¦^ÂÐ...
°ò¥»¤Wµ¥§A¦³¯à¤O¬ÝÀ´«e­±¦^Âбz°ÝÃD«e½úªºµ{¦¡½X¡A¦A´£¥X°ÝÃD·|¤ñ¸û¾A·í¡C
ÁÙ¦³»P¥DÃDµLÃöªº¦^ÂдNºâÄé¤ô¡A§Ú²{¦bªº¦^ÂдNºâ¤F¡A©Ò¥H±z¤d¸U§O¦A¦^ÂЧÚ

TOP

¦^´_ 43# lcctno

data!J2 ¥i¥Hª½±µ±a¨ç¼Æ¤ñ¸û²³æ¤@¨Ç
=FORECAST(I2,OFFSET(µ²ªG!D1,MATCH(I2,µ²ªG!A:A,1),,-2),OFFSET(µ²ªG!A1,MATCH(I2,µ²ªG!A:A,1),,-2))


FORECAST(x,known_y's,known_x's)
X    ¬O±z­n¹w´ú¤@­Ó¼Æ­Èªº¸ê®ÆÂI¡C
Known_y's    ¬O¦]Åܼư}¦C©Î¸ê®Æ½d³ò¡C
Known_x's    ¬O¦ÛÅܼư}¦C©Î¸ê®Æ½d³ò¡C



­ì data!J2 ¤½¦¡
=IF(ISERROR(LOOKUP(I2,OFFSET(µ²ªG!$A$3,,,µ²ªG!$E$2,),ROW(OFFSET(µ²ªG!$A$3,,,µ²ªG!$E$2,)))),"--",IF(LOOKUP(I2,OFFSET(µ²ªG!$A$3,,,µ²ªG!$E$2,),ROW(OFFSET(µ²ªG!$A$3,,,µ²ªG!$E$2,)))>=µ²ªG!$E$2+2,1,VLOOKUP(I2,IF({1,0},OFFSET(µ²ªG!$A$3,,,µ²ªG!$E$2,),OFFSET(µ²ªG!$D$3,,,µ²ªG!$E$2,)),2)+(I2-LOOKUP(I2,OFFSET(µ²ªG!$A$3,,,µ²ªG!$E$2,)))*LINEST(OFFSET(INDIRECT("µ²ªG!D"&LOOKUP(I2,OFFSET(µ²ªG!$A$3,,,µ²ªG!$E$2,),ROW(OFFSET(µ²ªG!$A$3,,,µ²ªG!$E$2,))),1),,,2,),OFFSET(INDIRECT("µ²ªG!A"&LOOKUP(I2,OFFSET(µ²ªG!$A$3,,,µ²ªG!$E$2,),ROW(OFFSET(µ²ªG!$A$3,,,µ²ªG!$E$2,))),1),,,2,))))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 43# lcctno

SUMPRODUCT¨ç¼Æ«ÜºC¡A»Ý´î¤Ö¨Ï¥Î
A3:A2562 ¤½¦¡½d³ò¤Ó¤j¨Ï¥Î­pºâÅܱo«ÜºC¡A¹ê»Ú¤~¨Ï¥Î¨ìA743

«Øij§ï¬°

µ²ªG!
A3 =IF(Data!N$2+Data!O$2*(ROW()-4) > Data!K$2, "", Data!N$2+Data!O$2*(ROW()-3))
B3 =IF(A3="",0,SUMPRODUCT((A3<=¸ê®Æ¦C_³Ì°ª»ù)*(A3>=¸ê®Æ¦C_³Ì§C»ù)))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¬°¦ó§Ú¥u­n¶}±Ò±zªºÀÉ®× §Ú¥¼°µ¥ô¦ó°Ê§@«KÃö¤W ¥¦´N·|´£¥Ü¸ê®Æ¤w§ó§ï °Ý§Ú­n¤£­n¦sÀÉ???
OFFSET,INDIRECT,NOW,TODAY....µ¥µ¥¨ç¼Æ, ³£·|³y¦¨³o­Ó´£¥Ü!

SUMPRODUCT­pºâ¤W¸Uµ§¸ê®Æ, ­YPCµ¥¯Å¤£°ª, ¤j³¡¥÷¥ú¶}±ÒÀÉ®×´N­nµ¥«Ü¤[,
¤S, ­Y¬O¬°[±`¾n]¤½¦¡, ²Î­p¨Ó·½ªí¥ô¤@Àx¦s®æ¨C¤@¦¸ÅÜ°Ê, §Y·|¶]¥X¡e¦Û°Ê­«ºâ¡f(«UºÙ:¥dÀÉ), «D±`¤£¤è«K,

¨Ï¥ÎVBA¥i¥H§ïµ½¥H¤W±¡ªp, ¯ÊÂI¬O¡G¼Æ¾ÚÅÜ°Ê«á, ¥²¶·¦A°õ¦æµ{¦¡, µLªkÀH®ÉÅã¥Ü°ÊºAµ²ªG!

TOP

¦^´_ 47# ­ã´£³¡ªL
§Æ±æ±z¦³¿ìªkÀ°§Ú ­Y¥i¥Hªº¸Ü ªþ¤W»¡©ú §Ú¤]«Ü·Q¾Ç

ªþ¤W¶Q¤H­Ìªº«ü¾É«á §¹¦¨ªºÀÉ®× §Æ±æ¯à¦h´£¨Ñ§ïµ½ªº¦a¤è ÁÂÁ±z


¤j½L2011-12-19°_.rar (135.89 KB)

TOP

¥Î¤W¦¸VBA­×§ï¦p¤U¡]¥u¦C¥Xµ²ªGªí¢ÐÄ榸¼Æ¡A¨ä¥L¤£³B²z¡^¡G
TEST02.rar (134.34 KB)

Sub ¤ÀªR()
Dim Arr, Brr, Cunt&, Crr%(), R&, X&, Y&, TM, j%, k&, SU%
TM = Timer
R = [data!C65536].End(xlUp).Row
Arr = [Data!C1:D1].Resize(R)

X = Val([Data!I6]): If X < 2 Then X = 2
Y = Val([Data!J6]): If Y > R Then Y = R
If Y <= X Then Exit Sub

Cunt = 2560
ReDim Crr(1 To Cunt, 0)
Brr = [µ²ªG!A3].Resize(Cunt)
For j = 1 To Cunt
¡@¡@For k = X To Y
¡@¡@¡@¡@'If Brr(j, 1) >= Arr(k, 2) And Brr(j, 1) <= Arr(k, 1) Then SU = SU + 1 '§PÂ_¨â¦¸,³t«×¸ûºC
¡@¡@¡@¡@If Brr(j, 1) >= Arr(k, 2) Then If Brr(j, 1) <= Arr(k, 1) Then SU = SU + 1 '²Ä¤@±ø¥ó¦¨¥ß¦A§PÂ_²Ä¤G±ø¥ó,¸û§Ö
¡@¡@Next k
¡@¡@Crr(j, 0) = SU: SU = 0
Next j
[µ²ªG!B3].Resize(Cunt).Value = Crr
Application.Goto [µ²ªG!B3]
MsgBox Timer - TM
End Sub

TOP

¥»©«³Ì«á¥Ñ lcctno ©ó 2015-8-29 21:47 ½s¿è

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

[attach]21867[/attach]

¥i¯à¬O§PŪªºµ{¦¡½X¤£¦P©Ò¦Ü ½Ð¬Ýªþ¥ó¤ºµµ¦âªº³¡¤À ¥Ñ©ó³o°õ¦æÀɬO¯à­nÅýªÑ²¼¤Î«ü¼Æ¨Ó¨Ï¥Î ÁٽжO¤ß¤F

ÁÂÁ±zªº¨¯³Ò


¦pªG°ò·Ç­È(¤ñ¸ûÂI) >= ¦C¤§³Ì§C»ù ¦P®É <= ¦C¤§³Ì°ª»ù ´Nµ¹ 1   
¤]´N¬O»¡¥u­n°ò·Ç­È(¤ñ¸ûÂI)¦³¦b°Ï¶¡¤º´Nµ¹1  
=IF(AND(°ò·Ç­È>=Data!D3,°ò·Ç­È<=Data!C3),1,"")

TEST03.rar (170.23 KB)

TOP

        ÀR«ä¦Û¦b : ¤H¨ÆªºÁ}Ãø»PµZ¿i¡A´N¬O¤@ºØ¦ÒÅç¡C
ªð¦^¦Cªí ¤W¤@¥DÃD