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

[µo°Ý] °Ï¶¡¥[Á`¨ç¼Æ

[µo°Ý] °Ï¶¡¥[Á`¨ç¼Æ

¦U¦ì¥ý¶i¦n,

§Ú·Q¥[Á`1~3ªº¤u§@ªíF:V (¤u§@ªí·|¼W¥[¦Ü31)
¥ÎF3=SUMPRODUCT(('1'!$B$3:$B$10=F$2)*('1'!$F$3:$V$10))
ÁöµM¥i¥H¥[Á`,¦ý¤é´Á¼W¥[®É,´N­n¤£Â_ªº­×§ï¤½¦¡
½Ð°Ý¦³¨S¦³¨º­Ó¨ç¼Æ,¥i¥H¥ÎAÄæ§@¬°¯Á¤Þ,¯à°÷¤@­Ó¤½¦¡¥Î¨ì©³ªº

Ãþ¦ü¹³F3=SUMPRODUCT(($A2&!$B$3:$B$10=F$2)*($A2&!$F$3:$V$10))
³o¥u¬OÁ|¨Ò,§Ú¸Õ¹L¦æ¤£³q¡I
©ÎªÌ¦³¤°»ò¨ä¥L¨ç¼Æ¥i¥H³o¼Ë¥[Á`ªº¡H
°Ï¶¡¥[Á`.rar (15.81 KB)

=SUMPRODUCT((INDIRECT("'"&$A3&"'!B3:B99")=F$2)*INDIRECT("'"&$A3&"'!F3:V99"))

TOP

¦^´_ 2# ­ã´£³¡ªL

±z¦n,

INDIRECT("'"&$A3&"'!F3:V99")
INDIRECTªº¨ç¼Æ,§Ú¤]¸Õ¹L¦ý¬O¨S¦³¥[¬õ¦â³¡¥÷,©Ò¥H¤£¯à­pºâ,·Q½Ð°Ý,¬õ¦âªº·N«ä¬O¤°»ò¡H¬°¤°»ò¥[¤F¥¦´N¥i¥H­pºâ¥X¨Ó¡H

TOP

¦^´_ 2# ­ã´£³¡ªL

¥t¥~ÁÙ¦³¤@­ÓÃþ¥Hªº¥[Á`°ÝÃD,·Q¤@¨Ö½Ð±Ð¡G
ÃöÁä¦r¦bB1:E1
¥[Á`°Ï°ì¬°¥X³f!B:Q
¦³¨S¦³¤@­P©Êªº¨ç¼Æ,¥ÎÃöÁä¦rÃѧO¤è¦¡,¨Ì¤é´Á§O/²£«~§O/°Ï°ì§O ¥[Á` ¥X³f¼Æ­È
¤£­n¥ÎLeft,right,midªº¤è¦¡¨ú¦r,³o¼ËÃöÁä¦r¤@§ïÅÜ,´N­n­«·s­×§ï¨ç¼Æ

°Ï¶¡¥[Á`2.rar (19.57 KB)

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2019-4-28 07:53 ½s¿è

¦^´_ 4# PJChen


    ¥X³f²Î­p
B2 =SUMPRODUCT(($A2=¥X³f!$A$2:$A$9)*($B2=¥X³f!$B$2:$B$9)*ISNUMBER(FIND(C$1,¥X³f!$C$1:$R$1))*¥X³f!$C$2:$R$9)
¤U©Ô¥k©Ô

©Î¬O

B2 =SUMPRODUCT(SUMIFS(OFFSET(¥X³f!$C:$C,,COLUMN($C:$R)-3),¥X³f!$A:$A,$A2,¥X³f!$B:$B,$B2)*ISNUMBER(FIND(C$1,¥X³f!$C$1:$R$1)))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 5# ML089

·PÁ¤j¤j,
«Ü¦n¥Îªº¤½¦¡

½Ð°Ý¨ç¼Æ¦³®É·|¦Û°Ê¥X²{',¨Ò¦p¥H¤U,¥¦¥Nªíªº·N«ä¬O¤°»ò?
=SUMIFS('W:\0_¦Û­qªí³æ\¤é±`ªí®æ 2019.04\[²z³f³æ_All.xlsx]¤ñµá¦h.¥þ¥x'!$Q:$Q,'W:\0_¦Û­qªí³æ\¤é±`ªí®æ 2019.04\[²z³f³æ_All.xlsx]¤ñµá¦h.¥þ¥x'!$T:$T,AT$2,'W:\0_¦Û­qªí³æ\¤é±`ªí®æ 2019.04\[²z³f³æ_All.xlsx]¤ñµá¦h.¥þ¥x'!$L:$L,$D46)

TOP

¦^´_ 6# PJChen

¸ó¤u§@ï®É¡A»Ý­nÀɮצWºÙ¤Î¸ô®|
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 7# ML089

Thank u.

TOP

¦^´_ 5# ML089

M¤j,

½Ð°Ý¤@­ÓÃþ¦üªº¥[Á`°ÝÃD,¥»·Q®M¥Î³o­Ó¤½¦¡,¦ýµ²ªG¬OµL®Ä¡A§Ú·Q°ÝÃD¬O¦b©ó"¦aÂI"¡A¥i§_À°¦£¬Ý¤U­n¦p¦ó­×§ï¤½¦¡¡A¥i¥H¹F¨ì­n¨D¡H

§Ú­n±N"·í¤é¥X³f"¤u§@ªíªº¼Æ¦r±a¨ì"30"¤u§@ªíªºMÄæ       
±ø¥ó¦³¤G:        ®Æ¸¹¤Î¦aÂI
1..        ®Æ¸¹­n¬Û¦P,"·í¤é¥X³f"¤u§@ªí AÄæ="30"¤u§@ªíHÄæ
2..        ¦aÂI:¥u­n"30"¤u§@ªíG1ªº¦r¦³³¡¥÷²Å¦X"·í¤é¥X³f"¤u§@ªíªºB3:G3´N¦¨¥ß
3..        ¦ýG1ªº¤å¦rªø«×³£¤£¤@©w,¦³ªº¤¤¶¡·|¦³"-",¦³¨Ç¨S¦³,°ß¤@ªº³W«h´N¬O,¥¦ªº¬Y¨Ç¤å¦r,¤@©w·|§¹¥þ»P"·í¤é¥X³f"¤u§@ªíªºB3:G3ªº¤@¼Ë
4..        ¥u­n²Å¦X³o¨Ç±ø¥ó,´N§â"·í¤é¥X³f"¤u§@ªíªº¼Æ¦r±a¨ìMÄæ
5..    §Ú¦³«Ü¦hªº¤u§@ªí­n¥Î³o¼Ëªº·Ç«h®M¥Xµª®×,¦aÂI§Ú¥u¦C¤F´X­Ó,»P#3ªº°ß¤@³W«h¬Û¦P""30"¤u§@ªíG1ªº¬Y¨Ç¤å¦r,¤@©w·|§¹¥þ»P"·í¤é¥X³f"¤u§@ªíªºB3:G3ªº¤@¼Ë"

°Ï¶¡¥[Á`2.rar (30.17 KB)

TOP

        ÀR«ä¦Û¦b : ¥Í®ð¡A´N¬O®³§O¤Hªº¹L¿ù¨ÓÃg»@¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD