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

·Q°Ý°Ý¦³¦ó¤½¦¡¥i¤À§O­pºâ¤£¦P¤ÀÃþªºÁ`©M

·Q°Ý°Ý¦³¦ó¤½¦¡¥i¤À§O­pºâ¤£¦P¤ÀÃþªºÁ`©M

¥»©«³Ì«á¥Ñ K_Wing ©ó 2015-1-3 11:54 ½s¿è


A
B
C
D
E
F
G
H
I
1
½s¸¹
¤é´Á
®É¬q
·|ij«ÇÃþ§O
¨C¤p®É¦¬¶O
§é¦©
Á`­p


2
OUT-001
01/12/2014
12:00-14:00
·|ij«ÇA
$300.00
10%
$540.00


3
OUT-002
02/12/2014
18:00-22:00
·|ij«ÇB
$200.00
0%
$800.00


4
OUT-003
03/12/2014
13:00-15:00
·|ij«ÇA
$300.00
10%
$540.00


5
OUT-004
03/12/2014
18:00-21:00
·|ij«ÇB
$200.00
0%
$600.00


6
OUT-005
04/12/2014
19:00-22:00
·|ij«ÇC
$100.00
0%
$300.00


7
OUT-006
05/12/2014
16:00-22:00
·|ij«ÇC
$100.00
0%
$400.00



A
B
C
D
10
·|ij«ÇAÁ`¦¬¤J¡G$1080.00
11 ·|ij«ÇBÁ`¦¬¤J¡G$1400.00
12 ·|ij«ÇCÁ`¦¬¤J¡G$700.00
13

·Q°Ý°Ý¦U¦ì¥S§Ì
¦bB10,B11©MB12¬O¤W¹Ï¤£¦P·|ij«ÇÁ`­pªºÁ`©M
¦p·Q¨Ï¥Î¤½¦¡
¥i¥H«ç¼Ë¦C¥X
ÁÂÁÂÀ°¦£

¦^´_ 1# K_Wing

¥Îsumif¨ç¼Æ¡A¦bB10Àx¦s®æÁä¤J"=SUMIF($D$2:$D$7,LEFT(A10,4),$G$2:$G$7)"¡A¦V¤U½Æ»s¤½¦¡¡A¥i¥H­pºâ¥X¤@¼Ëªºµ²ªG¡C

TOP

¥»©«³Ì«á¥Ñ K_Wing ©ó 2015-1-4 11:20 ½s¿è
¦^´_  K_Wing

¥Îsumif¨ç¼Æ¡A¦bB10Àx¦s®æÁä¤J"=SUMIF($D$2:$D$7,LEFT(A10,4),$G$2:$G$7)"¡A¦V¤U½Æ»s¤½¦¡¡A¥i¥H­pºâ¥X¤@¼Ëªºµ²ªG¡C
tku0216 µoªí©ó 2015-1-3 22:03

^^~·PÁÂ
¦¨¥\¤F

·Q¦A°Ý°Ý
¦pªG¦b¤½¦¡¤¤¥[¦h¤@¶µ¡y½s¸¹¡z¤À¶}­pºâªº­n¨D
§Y¡G©Ò¦³½s¸¹OUTªº·|ij«ÇAªºÁ`¼Æ
¤S¥i·|¦³¬ÛÃö¤½¦¡¶Ü?
¥H¹Ï¤@ªº¸ê®Æ
¥i¦b¹Ï¤G¤¤¦p¦ó³]¸m¤½¦¡©O?
¹Ï¤@
A
B
C
D
E
F
G
1
½s¸¹
¤é´Á
®É¬q·|ij«ÇÃþ§O
¨C¤p®É¦¬¶O
§é¦©
Á`­p¦¬¶O
2OUT-001 11/12/201413:00-15:00·|ij«ÇA
$300.00
0%$600.00
3
OUT-002 12/12/2014 17:00-22:00
·|ij«ÇB$200.00
0%
$1000.00
4
OUT-003 13/12/2014 18:00-21:00·|ij«ÇA
$300.00
0%
$600.00
5
IN-001 13/12/2014 18:00-22:00·|ij«ÇA$150.00
0%
$600.00
6
IN-002 14/12/2014 09:00-12:00·|ij«ÇB
$100.00
0%
$300.00
7
IN-003 14/12/2014 09:00-11:00·|ij«ÇC
$  50.00
0%
$100.00

¹Ï¤GAB
C
D
10

·|ij«ÇA


11
·|ij«ÇB


12
·|ij«ÇC

13



TOP

¦^´_ 3# K_Wing
1¡B¦P®É¿ï¨úA1:A7¡BD1:D7»PG1:G7¤T­Ó½d³ò¡A«ö¡uCtrl+Shift+F3¡v¡A¥H¡u³»ºÝ¦C¡v¬°¦WºÙ¶i¦æ©w¸q¦WºÙ¡C
2¡BÀx¦s®æA10¥H¸ê®ÆÅçÃÒ»s§@²M³æ
3¡BÀx¦s®æC10¿é¤J¤½¦¡
=SUMPRODUCT((LEFT(½s¸¹,FIND("-",½s¸¹)-1)=$A$10)*(·|ij«ÇÃþ§O=$B10)*Á`­p¦¬¶O)
¦V¤U½Æ»s
½Ð°Ñ¦Ò¡I

fig.jpg (83.72 KB)

fig.jpg

TEST.zip (8.5 KB)

TOP

¦^´_  K_Wing
1¡B¦P®É¿ï¨úA1:A7¡BD17»PG1:G7¤T­Ó½d³ò¡A«ö¡uCtrl+Shift+F3¡v¡A¥H¡u³»ºÝ¦C¡v¬°¦WºÙ¶i¦æ©w ...
p212 µoªí©ó 2015-1-5 08:30

^^~¦¨¥\¤F
«D±`·PÁÂ

¦ý¥t¤@­Ó°ÝÃD¤S¥X²{¤F
¦]¬°¨Ï¥Î¨ä¥L½s¸¹®É
¨Ò¦p¡G"AB100-1415"©Î"BW"
Óý¤£¯à±N"Á`­p¦¬¶O"­pºâ¥X¨Ó
¬Ý¨Ó¬O"-"³o­Ó²Å¸¹ªº¦ì¸m²¾¦ì«K¥¢®Ä¤F
·Q°Ý°Ý·í¤£¨Ï¥Î"-"®É
¤S©Î½s¸¹§ï¦¨"AB100-1415"®É
¥i¥H¦p¦ó­×§ï¸Ó¤½¦¡©O?

TOP

¦^´_ 5# K_Wing

´£¨Ñ§Úªº¤èªk¡A§Ú¬O¨Ï¥Î¿ëÃÑÃöÁä¦r¥[Á`ªº¤è¦¡¡A©óA10Àx¦s®æ¿é¤J¥H¤U¤½¦¡¡A¤§«á©¹¤U½Æ»s¡G
  =SUMIFS($G$2:$G$7,$A$2:$A$7,"OUT"&"*",$D$2:$D$7,B10)

¤¤¶¡ªº"OUT"&"*"¡A·|´M§ä"OUT"¶}ÀYªºÃöÁä¦r¶i¦æ¥[Á`¡A±z¥i¥H¦Û¦æ¹Á¸Õ§ó´«³o­Ó¦a¤è...

TOP

¦^´_  K_Wing

´£¨Ñ§Úªº¤èªk¡A§Ú¬O¨Ï¥Î¿ëÃÑÃöÁä¦r¥[Á`ªº¤è¦¡¡A©óA10Àx¦s®æ¿é¤J¥H¤U¤½¦¡¡A¤§«á©¹¤U½Æ»s¡G
  =SUMIFS($G$2:$G$7,$A$2:$A$7,"OUT"&"*",$D$2:$D$7,B10)

¤¤¶¡ªº"OUT"&"*"¡A·|´M§ä"OUT"¶}ÀYªºÃöÁä¦r¶i¦æ¥[Á`¡A±z¥i¥H¦Û¦æ¹Á¸Õ§ó´«³o­Ó¦a¤è...
tku0216 µoªí©ó 2015-1-5 18:44

^^~·PÁÂ¥S§Ì´£¨Ñ¤èªk
¥i±¤§Ú«ä¦Ò¿ð¶w
¤´¥¼¯à­ÉÃDµo´§
­pºâ¥X·Q­nªºµ²ªG

®Ú¾Ú¼Ó¼h¤Tªº¹Ïªí
¥¼¯à±N¤£¦P½s¸¹("OUT"©M"IN")¤¤ªº¤£¦P·|ij«Ç(A¡BB¡BC)
¿W¥ß­pºâ¥X¨Ó
·Q¤F¸Ñ¦h¤@¨Çªº¬O
§Ú¸Ó¦p¦ó³]¸m"OUT"©M"IN"ªº½s¸¹
¥h¤À§O­pºâ·|ij«ÇA¡BB¡BCªº¦U¦Û"Á`­p¦¬¶O"?
ÁÂÁÂ

TOP

¦^´_ 7# K_Wing

¤£½T©w³o¬O¤£¬O§A»Ý­nªº¡H§Ú§â¨â­Ó·Ç«h(·|ij«ÇA~C¡BOUT~IN)«Ø¦¨¤@±iªí®æ¡A­pºâ¥X¥²¶·²Å¦X¨â­Ó·Ç«h³£¦³¥æ¶°ªº¶O¥Î¡Aµ²ªG¦pB15~D16Àx¦s®æ¤º®e¡C
    ¬¡­¶Ã¯1.rar (7.93 KB)

TOP

¦^´_  K_Wing

¤£½T©w³o¬O¤£¬O§A»Ý­nªº¡H§Ú§â¨â­Ó·Ç«h(·|ij«ÇA~C¡BOUT~IN)«Ø¦¨¤@±iªí®æ¡A­pºâ¥X¥²¶·²Å¦X ...
tku0216 µoªí©ó 2015-1-5 22:35

·PÁÂÀ°¦£~^^
¬Q±ß¦^¨ì®a¤¤¤U¸ü¬d¬Ý¨Ã¨Ï¥Î
§¹¥þ²Å¦X§Úªº»Ý­n
¥u¥i±¤¤@®É¶}¤ß±o¤Ó§Ñ§Î¤F
¨S¦³±N¶}±Ò¦nªºÀÉ®×±a¦^¤½¥q

¤µ¤Ñ¤@¦­¦^¨ì¤½¥q
¦A¦¸¤U¸ü¦³ÃöÀÉ®×
¥i«ë§Ú¤½¥q¥u¯à¶}±ÒzipÀÉ
¶}¤£¨ìrarÀÉ
°ß¦³¦^®a±N¶}±Ò«áªºÀÉ®×
©ú¤Ñ±a¦^¤½¥q¦A§@¹Á¸Õ
¦A¦¸·PÁÂ¥S§Ìªº¼ö¤ßÀ°¦£
ÁÂÁÂ

TOP

        ÀR«ä¦Û¦b : Ä@­n¤j¡B§Ó­n°í¡B®ð­n¬X¡B¤ß­n²Ó¡C
ªð¦^¦Cªí ¤W¤@¥DÃD