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

[µo°Ý] ¨Ì¨C¤ë³Ì«áªº¤é´Á ¥[Á`

[µo°Ý] ¨Ì¨C¤ë³Ì«áªº¤é´Á ¥[Á`

Dear,
½Ð°Ý¥i¥H«D°}¦Cªº¤@­P©Ê¤½¦¡,²Î­p¤T­Ó«È¤áªº­Ó§O¼Æ¶q¶Ü?
¨ÌAÄæ¨C¤ë³Ì«áªº¤é´Á(¤£¤@©w¬O¤ë©³,¥u§ì¨C¤ë³Ì«á¤@­Ó¤é´Á)
¤À§O²Î­pCÄæ ¤T­Ó«È¤á¼Æ¶q
1) ¤j
2) Ÿ
3) ¨Î
CÄæ­Y¦³¨ä¥L¦r,«h

¨ÌAÄæ¨C¤ë³Ì«áªº¤é´Á_¤À§O²Î­p¼Æ¶q.rar (11.62 KB)

¦^´_ 1# PJChen
³o¼Ëªºµ²ªG¥i¥H¶Ü?
½Ð°Ñ¦Ò!

ref.zip (12.89 KB)

TOP

¦^´_ 1# PJChen
­×¥¿¡I
2019/12/31»P2020/1/31¦³«D³æµ§«È¤á
½Ð°Ñ¦Ò¡I

ref_­×¥¿.zip (12.79 KB)

TOP

D2 =IF(SUMPRODUCT((TEXT(A2,"yyyymm")=TEXT(A3:A33,"yyyymm"))*(C2=C3:C33))=0,
SUMPRODUCT(B$2:B2,(TEXT(A2,"yyyymm")=TEXT(A$2:A2,"yyyymm"))*(C2=C$2:C2)),"")

¤U©Ô½Æ»s
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 4# ML089


    ±zªº¸ÑªkÅý§Ú¤j¶}²´¬É¡A¨ØªA!!

TOP

D2{=IF(MAX((MONTH(A$2:A$146)=MONTH(A2))*(C$2:C$146=C2)*A$2:A$146)=A2,SUM((MONTH(A$2:A$146)=MONTH(A2))*(C$2:C$146=C2)*B$2:B$146),"")
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¦^´_ 4# ML089
¦^´_ 6# hcm19522

·PÁÂ...³o¨â­Ó¸Ñªk³£¥i¥H,
¯à§_½Ð±Ð,§Ú¨ä¥LÀɮפ]¦³¤£¤Ö¬O¥Î°}¦C¤½¦¡ªº,¦pªG­n¹³³o¼Ëª½±µ¥´¦bVBA¤¤,°}¦C¤½¦¡­n«ç»ò§ï¤~¯à¥Î??
        xRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
            Range("D2:D" & xRow).Formula = "=IF(MAX((MONTH(A$2:A$146)=MONTH(A2))*(C$2:C$146=C2)*A$2:A$146)=A2,SUM((MONTH(A$2:A$146)=MONTH(A2))*(C$2:C$146=C2)*B$2:B$146),"")"
            Range("D2:D" & xRow).Value = Range("D2:D" & xRow).Value

TOP

¦^´_ 7# PJChen

§Ú·|«Øij²Ä¤@¦C¨Ï¥Î¤½¦¡

VBA ¦b±N²Ä¤@¦C¤½¦¡½Æ»s¦Ü¤U¤è°Ï°ì¡A¦A±N¤½¦¡Âà­È

³o¼Ë´N¤£¥Î·Ð´o¤½¦¡Âà¼g¦ÜVBA¸Ì
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 8# ML089
³o­Ó¤½¦¡«Ü¯S§O,½Ð±ÐTEXT(A3:A33....¬°¤°»ò¬O33 ??
D2 =IF(SUMPRODUCT((TEXT(A2,"yyyymm")=TEXT(A3:A33,"yyyymm"))*(C2=C3:C33))=0,
SUMPRODUCT(B$2:B2,(TEXT(A2,"yyyymm")=TEXT(A$2:A2,"yyyymm"))*(C2=C$2:C2)),"")

TOP

¦^´_  ML089
³o­Ó¤½¦¡«Ü¯S§O,½Ð±ÐTEXT(A3:A33....¬°¤°»ò¬O33 ??
D2 =IF(SUMPRODUCT((TEXT(A2,"yyyymm")= ...
PJChen µoªí©ó 2020-3-11 19:29



    A3:A33 ¦@¦³31®æ¡Aªí¥Ü¤@­Ó¤ë³Ì¦h31¤Ñ¡AÅý¤½¦¡¤ñ¸ûÀx¦s®æ­pºâ¥i¥H¤Ö¤@ÂI¡C
­Y¸ê®Æ«Ü¦h®É´N¥i¥H¬Ý¥X¤½¦¡®Ä²v
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¤£­n¤p¬Ý¦Û¤v¡A¦]¬°¤H¦³µL­­ªº¥i¯à¡C
ªð¦^¦Cªí ¤W¤@¥DÃD