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

[µo°Ý] ¨C¤é¦¬¤ä¾ã²z

[µo°Ý] ¨C¤é¦¬¤ä¾ã²z

ªþ¥óÁ`¦@¦³8­Ó¤À­¶¡A¨ä¤¤6­¶¬O¦p¤U¹Ï¤§raw data
·Q¦b¿é¤J®É«K¤]¥i¬Ý¨ì²Ö­p¸ê®Æ
¬G¿é¤J¦¹¤½¦¡¡A·Pı¼gªk«Ü²Â¡A¤£ª¾¦³¤°»ò¦n°µªk¥i¥H´£¨Ñ°Ñ¦Ò¡AÁÂÁÂ!


¥»¦¸¥D­n·Q°Ýªº¬O­n¦p¦ó¾ã²z¦¨summary¡Bsummary2³oºØµ²ªG
¥Ø«e§Ú¥ý¨Ï¥Î¤H¤uªº¤è¦¡§â¸ê®Æ¥[Á`°_¨ÓÅý¤j®aª¾¹D§Ú·Q±o¨ì¤°»òµ²ªG
§Æ±æ¥i¥H§ï¦¨¦Û°Ê§PÂ_«á¥[Á`
¦Ó³o2­Ó¤À­¶³£¥u¥[Á`¡u¦¬¤J¡v¬°µ²ªG
¥»¨Ó·Q¨Ï¥Îsumproduct°µ§PÂ_¡A¦ýsumproductµLªk¨Ï¥Î¸U¥Î¦r¤¸
§ïÅÜraw data¿é¤J¤è¦¡§ï¥Îsumif¥çµLªk¨Ï¥Î¦~«×¡B¤ë¥÷·í³¡¥÷¦r¤¸§PÂ_¥[Á`ªº¶µ¥Ø
³Â·Ð¤j®a´£¨Ñ¨ó§U¡AÁÂÁÂ!!
APP¦¬¤ä°O¿ý.rar (22.62 KB)

¦^´_ 1# vvcvc
¥ý´£¨ÑRaw Data¤»­Ó¤À­¶ªº
1¡BÀx¦s®æD2¡uµ²¾l¡v¤½¦¡
=SUM(IF(B2="¦¬¤J",1,-1)*C2,D1)
¦V¤U½Æ»s
2¡BÀx¦s®æF1¤½¦¡
=OFFSET($D$1,COUNT(D:D),)
½Ð°Ñ¦Ò¡I

TOP

¦^´_ 1# vvcvc

¥H¨C­¶¸ê®Æ¨ì2000¦C¬°¨Ò
summary2ªºD5¤½¦¡
=SUMPRODUCT((INDIRECT("'"&LOOKUP("9999",$D$3:D$3)&" "&D$4&"'!B2:B2000")="¦¬¤J")*(TEXT(INDIRECT("'"&LOOKUP("9999",$D$3:D$3)&" "&D$4&"'!A2:A2000"),"yyyym")=$B5&$C5)*INDIRECT("'"&LOOKUP("9999",$D$3:D$3)&" "&D$4&"'!C2:C2000"))
summaryªºC5¤½¦¡
=SUMPRODUCT((INDIRECT("'"&$B5&"'!B2:B2000")="¦¬¤J")*INDIRECT("'"&$B5&"'!C2:C2000")*(TEXT(INDIRECT("'"&$B5&"'!A2:A2000"),"yyyym")=C$3&C$4))
¸Õ¸Õªþ¥ó
APP¦¬¤ä°O¿ý.zip (56.3 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

Hsiehª©¥Dªº¤½¦¡¸ûªø¸û½ÆÂø
©ú¤Ñ¦A¨Ó¬ã¨s¬ã¨s

p212§â¤½¦¡§Ëªº¦nºë²
F1ªº¤½¦¡Åý§Ú¾Ç¨ì«ç»ò§PÂ_³Ì«á¤@®æ

·PÁ¤G¦ìªº¤À¨É

TOP

        ÀR«ä¦Û¦b : ¦h°µ¦h±o¡C¤Ö°µ¦h¥¢¡C
ªð¦^¦Cªí ¤W¤@¥DÃD