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

[µo°Ý] ¨â¶¥¬q±ø¥ó²Å¦X«á¦A¶i¦æ­pºâ

[µo°Ý] ¨â¶¥¬q±ø¥ó²Å¦X«á¦A¶i¦æ­pºâ

·Ð½Ð¦U¦ì°ª¤â~
¦b¤u§@ªí1

»P¤u§@ªí¤G


²Ä¤@¨BÆJ¡Q
¡@¡@¥ý¦b¤u§@ªí¤@ªº¢ÓÄæ¡]ªíÀY sys.date¡^¤¤©M¤u§@ªí¤Gªº¢ÓÄæ¡]ªíÀY Sdate¡^¤ñ¹ï
         µo²{¤é´Á¬Û¦Pªº®É­Ô¡A¶i¦æ²Ä¤G¨BÆJ
²Ä¤G¨BÆJ¡Q
         ¦A¤ñ¹ï¤u§@ªí¤@ªºAÄæ¡]ªíÀY NO.¡^¤¤©M¤u§@ªí¤GªºAÄæ¡]ªíÀY number¡^
         µo²{³æ¸¹¬Û¦Pªº®É­Ô¡A¶i¦æ²Ä¤T¨BÆJ
²Ä¤T¨BÆJ¡Q
        §â¤u§@ªí¤@ªºDÄæ¡]ªíÀY cash¡^¤¤»P¤u§@ªí¤GªºDÄæ¡]ªíÀY fee¡^ªº­È¬Û´î
        
        ¤]´N¬O»¡²Ä¤@¨BÆJ©M²Ä¤G¨BÆJ³£²Å¦X±ø¥ó®É¤~¶i¦æ²Ä¤T¨BÆJ
        ¨Ò¦p¡Q
                     1. ¥ý¦b¤u§@ªí¤@ªº¢Ó2¡]®É¶¡2016/8/1¡^¤¤©M¤u§@ªí¤Gªº¢ÓÄæ¡]2016/8/1¡^¤ñ¹ï¡Aµo²{¦³¨âµ§¸ê®Æ
                     2. ¦A¤ñ¹ï¤u§@ªí¤@ªºA2¡]³æ¸¹10001¡^¤¤©M¤u§@ªí¤GªºAÄæ¡]µo²{¦³10001¡^
                     3. ³Ì«á¤u§@ªí¤@ªºD2¡]1200¡^»P¤u§@ªí¤GªºDÄæ¡]200¡^ªº­È¬Û´î   SHOW¥X1000

Àu¥ý§Æ±æ¯àª½±µ¦b¤u§@ªí¤@ªºDÄæ¤W­pºâ
­Y¤£¦æ¡A²Ä¤G¦Ò¼{¦A¼W¥[¤@Äæ¦ì¥h­pºâ
       ¦³¸ÕµÛ¼g¨ç¼Æªº¤è¦¡¥h°µ¡A¦ý¼g¤F¨â¤ÑÁÙ¬O¨S¦³¦¨¥\@@
      §Ú¼g=IF(AND(AND(E2,¤u§@ªí2!E:E),AND(¤u§@ªí1!A2,¤u§@ªí2!A:A)),¤u§@ªí1!D2-¤u§@ªí2!D:D,"")
      ±o¥Xªºµ²ªG¬O¿ùªº


~~­Y¯à¥ÎVBA¸Ñ¨M¤]¦æ

³Â·Ð¦U¦ì°ª¤â¦Ñ®v¤F!!

ªþ¥ó==> ¬¡­¶Ã¯1_0914.zip (382.58 KB)

dear  sir *-
1.¤u§@ªí¤G ªÅ¥Õ FÄæ¥[¤J =A2&E2«á©¹¤U©Ô (­YFÄæ«DªÅ¥Õ.¦Û¦æ¨úªÅ¥ÕÄæ)
2.¤u§@ªí¤@ ªÅ¥Õ HÄæ¥[¤J =IF(ISERROR(INDIRECT("Sheet2!D"&MATCH(A2&E2,Sheet2!F:F,0))),"",D2-INDIRECT("Sheet2!D"&MATCH(A2&E2,Sheet2!F:F,0)))«á¤U©Ô§Y¥i.
ù

TOP

¦^´_ 2# rouber590324

ÁÂÁ±z~~~
³o¼Ëkey­ÈÅܬ°A2&E2¤F
¦nÁo©úªº¤è¦¡
  
  ­Y§ï¬°vba±z·|¶Ü?

TOP

H2 =D2-SUMIFS(¤u§@ªí2!D:D,¤u§@ªí2!A:A,A2,¤u§@ªí2!E:E,E2)
¤U©Ô
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¤£½T©w¹ï¤£¹ï¡A½Ð´¡¤J¼Ò²Õ«á´ú¸Õ¤@¤U

Dim LostRow As Integer
LostRow=sheets("¤u§@ªí1").cells(rows.count,1).end(xlup).row
For i = 2 to LostRow
If sheets("¤u§@ªí1").cells(i,5)=sheets("¤u§@ªí2").cells(i,5) and sheets("¤u§@ªí1").cells(i,1)=sheets("¤u§@ªí2").cells(i,1) then
sheets("¤u§@ªí1").cells(i,7)=sheets("¤u§@ªí1").cells(i,4)-sheets("¤u§@ªí2").cells(i,4)
end if
next
EXCEL VBA·s¤â¡A½Ð«e½ú¦h¦h«ü±Ð

TOP

¦^´_ 5# ketrddem


¤j¤j§A³o­Ó¤èªk¨S¦³¶]¥Xµ²ªG

§Ú¥ý¼È®É¥Î¨ç¼Æ¸Ñ¨M¤F~ÁÂÁ±z

TOP

¦^´_ 4# ML089


  ±z³o¤èªkº¡¦n¥Îªº
  
  ·Q¦A½Ð±Ð¤@¤U
  ­Y¦b¤u§@ªí¤@­pºâ®É¥u¿ï¤u§@ªí¤G©_¼Æ¦C°µ­pºâ
  ¦Ó°¸¼Æ¦C©¿²¤¥¦¤£­n­pºâ
  ³o¼Ë¦³¨ç¼Æ¥i¥H¨Ï¥Î¶Ü?

TOP

¦^´_ 7# Changbanana
­Y¦b¤u§@ªí¤@­pºâ®É¥u¿ï¤u§@ªí¤G©_¼Æ¦C°µ­pºâ¡A¦Ó°¸¼Æ¦C©¿²¤¥¦¤£­n­pºâ¡C

1.
¤u§@ªí¤G¡A¼W¥[»²§UÄ椽¦¡¡A©_¼Æ¦C=1¡A°¸¼Æ¦C=0¡C
F2 =MOD(ROW(),2)
¤U©Ô

2.
H2 =D2-SUMIFS(¤u§@ªí2!D:D,¤u§@ªí2!A:A,A2,¤u§@ªí2!E:E,E2)
§ï¬° (³Ì«á¤@¼Æ¡A©_¼Æ¦C=1¡A°¸¼Æ¦C=0)
H2 =D2-SUMIFS(¤u§@ªí2!D:D,¤u§@ªí2!A:A,A2,¤u§@ªí2!E:E,E2,¤u§@ªí2!F:F,1)
¤U©Ô
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 8# ML089


    ÁÂÁ§A~¦¨¥\¹F°}^^

TOP

        ÀR«ä¦Û¦b : ¬°¦Û¤v§äÂǤfªº¤H¥Ã»·¤£·|¶i¨B¡C
ªð¦^¦Cªí ¤W¤@¥DÃD