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

§PÂ_¤é´Á¤§«áªº¸ê®Æ¨Ã­pºâ¼Æ¶q

§PÂ_¤é´Á¤§«áªº¸ê®Æ¨Ã­pºâ¼Æ¶q

½Ð°Ý¦p¦ó¨Ï¥ÎVBA¡A§PÂ_¤é´Á¤§«áªº¸ê®Æ¨Ã­pºâ¼Æ¶q¡AÁÂÁÂ!
¬¡­¶Ã¯1.tar (148.5 KB)

¨Ì·Ó©ÒKEY INªº¤é´Á(¤u§@ªí2-EÄæ)  §PÂ_¤é´Á ¤§«áªº¸ê®Æ(¤u§@ªí1 -GÄæ) ¨Ã­pºâ°Ï°ì(¤u§@ªí1 -GÄæ)ªº¼Æ¶q
1.jpg
2017-5-21 23:21

2.jpg
2017-5-21 23:21

3.jpg
2017-5-21 23:21

¥i¥H¨Ï¥ÎCOUNTIFS ¨ç¼Æ¥i±N·Ç«h®M¥Î¦Ü¸ó¦h­Ó½d³òªºÀx¦s®æ¡A¨Ã­pºâ©Ò¦³·Ç«h§¡²Å¦Xªº­Ó¼Æ¡C
=COUNTIFS(¤é´Á½d³ò,">"&§PÂ_°ò·Çªº¤é´Á,¸¹½X1½d³ò,§PÂ_°ò·Çªº¸¹½X1,°Ï°ì½d³ò,§PÂ_°ò·Çªº°Ï°ì)
¤é´ÁNAªº³¡¤À
=if(§PÂ_°ò·Çªº¤é´Á="NA",¥h±¼¤é´Á·Ç«hªºCOUNTIFS,§t¤é´Á·Ç«hªºCOUNTIFS)

TOP

­pºâµ²ªG»P§A´£¨Ñªº¤£¦P, ¦ýÀ³¸Ó¬O§A­nªº
  1. Option Explicit
  2. Sub Cal_N()
  3.     Dim I%, J%, DD$, S1 As Worksheet, S2 As Worksheet
  4.    
  5.     Set S1 = ActiveWorkbook.Sheets("¤u§@ªí1")
  6.     Set S2 = ActiveWorkbook.Sheets("¤u§@ªí2")
  7.    
  8.     For I = 3 To 7
  9.         For J = 6 To 16
  10.             DD = S2.Cells(I, 5)
  11.             If DD = "NA" Then
  12.                 S2.Cells(I, J) = Application.WorksheetFunction.CountIfs(S1.Columns(5), _
  13.                 S2.Cells(I, 3), S1.Columns(7), S2.Cells(2, J))
  14.             Else
  15.                 S2.Cells(I, J) = Application.WorksheetFunction.CountIfs(S1.Columns(5), _
  16.                 S2.Cells(I, 3), S1.Columns(7), S2.Cells(2, J), S1.Columns(6), ">" & S2.Cells(I, 5))
  17.             End If
  18.         Next J
  19.     Next I
  20. End Sub
½Æ»s¥N½X

TOP

¦^´_ 3# prin.huang


    ½Ð°Ýprin.huang
°²¦p¦³¤@¨â¸U¦Cªº¸ê®Æ¡A¨Ï¥Îcountifs¨Ó¶]¡A®É¶]ªº®É¶¡¹L¤[¡A½Ð°Ýµ{¦¡«ç»ò­×¤~¥i¥H§ïµ½³t«×

TOP

°Ñ¦Ò¯¸¤ºor¨¦ºq¸ê®Æ¡A§ï¼g¦¨°}¦C¸Õ¸Õ
À³¸Ó·|§Ö«Ü¦h

TOP

°Ñ¦Ò¯¸¤ºor¨¦ºq¸ê®Æ¡A§ï¼g¦¨°}¦C¸Õ¸Õ
À³¸Ó·|§Ö«Ü¦h
prin.huang µoªí©ó 2017-6-1 09:19


­è¤~¸Õ§@¤F¤@¤U¡Aexcel¨ç¼ÆsumifsµLªk±µ¨ü°}¦C¡A¥u¯à±µ¨ürange¡A¸Õ¹Bºâ1¸U¦hµ§³t«×ÁÙ¬OºC¡A
¬Ý¨Ó¥u¯à§â"¸ê®ÆŪ¦¨°}¦C+¦Û¤v¼g§PÂ_¥y"¤~¯à¥[§Ö³t«×¤F

TOP

¦^´_ 6# prin.huang

prin.huang

ÁÂÁ¡A§Ú¦A¸Õ¸Õ¬Ý

TOP

¦^´_ 4# chiang0320
§ï¥Î°}¦C¨Ã¼ÒÀÀ2¸U5¤dµ§¡A¤j¬ù1.3¬í§¹¦¨¡A½Ð´ú¸Õ¡C
¬¡­¶Ã¯1.rar (138.42 KB)

TOP

¦^´_ 8# Kubi

½Æ¥» ½Æ¥» ¬¡­¶Ã¯1.tar (147 KB)

½Ð°ÝKubi

¦pªGÅܧó­ì©lÀɮתºÃöÁäÄæ¦ì¡Aµ{¦¡½X¤S·|­×§ï¦¨«ç»ò¼Ë¤l?

¥i¥HÅý§Ú¹ï·Ó¤@¤U¤§«e»P¤§«á¡AÁÂÁÂÀ°¦£!

TOP

¦^´_ 9# chiang0320
½Ð°Ñ¦Ò
¬¡­¶Ã¯2.rar (131.26 KB)

TOP

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