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

¡i½Ð±Ð¡j¦p¦ó¥[Á`¤£¦P¬¡­¶Ã¯©Ò¹ïÀ³¸ê®Æ

¡i½Ð±Ð¡j¦p¦ó¥[Á`¤£¦P¬¡­¶Ã¯©Ò¹ïÀ³¸ê®Æ

¤j®a¦n,
§Æ±æ±q¹ï·Óªí¤¤¥[Á`¨C³æ¦ì¤£¦P¶O¥Î¤ä¥X¤§ª÷ÃB¡A©ósummary¬¡­¶Ã¯¤¤¶À¦â©³§e²{
¦]¸Õ¹Lvlookup()»Psumif()¦ý­n¹ïÀ³ªº¥Ø¼Ð­È¦³Äæ»P¦C¼ÐÃD¡A¤£ª¾¬O§_¦³¨ä¥L§ó¦n¤èªk¥i¦Û°Ê±a¤J??

¥H¤W
«D±`·PÁÂ~~^^

¤£¦P³øªí¹ï·Ó.rar (37.13 KB)

mmap

¥»©«³Ì«á¥Ñ p212 ©ó 2014-9-12 17:09 ½s¿è

¦^´_ 1# mmap
1¡B©ó¡u¹ï·Óªí¡v¤u§@ªí¿ï¨úA1:F871¡A«öCtrl+Shift+F3¡A¤Ä¿ï¥H¡u³»ºÝ¦C¡v¬°¦WºÙ¡C
2.©ó¡usummary¡v¤u§@ªí¤§
Àx¦s®æB2¿é¤J=SUMPRODUCT((³æ¦ì¥N¸¹=$A2)*ºë¯«¸ÉÀv¶O)
Àx¦s®æC2¿é¤J=SUMPRODUCT((³æ¦ì¥N¸¹=$A2)*¯S§÷¶O)
Àx¦s®æD2¿é¤J=SUMPRODUCT((³æ¦ì¥N¸¹=$A2)*¿¯­¹¶O)
Àx¦s®æE2¿é¤J=SUMPRODUCT((³æ¦ì¥N¸¹=$A2)*Á`¶O¥Î)
Àx¦s®æF2¿é¤J=SUMPRODUCT((³æ¦ì¥N¸¹=$A2)*³¡¥÷­t¾á)
¤Ï¥Õ¡usummary¡v¤u§@ªí¤§B2:F3¡A¦V¤U½Æ»s¤½¦¡¡C
½Ð°Ñ¦Ò¡I

TOP

½Ð°Ý¡G¬O§_¦³¾÷·|¤£»Ý­n¿é¤J¶O¥Î¦WºÙ©O?
¦p¹³³æ¦ì¥N¸¹¤@¼Ë¥i¥H=B¢C1¤§Ãþªº¤èªk
¦]¬°¶O¥Î¦WºÙ¬Û·í¦h ==
mmap

TOP

B2
=SUMPRODUCT(($A2=¹ï·Óªí!$A$2:$A$871)*(¹ï·Óªí!$B$1:$F$1=B$1)*¹ï·Óªí!$B$2:$F$871)

TOP

«D±`·PÁÂ~~
¥i¥H¬Ù¤U«Ü¦h®É¶¡~~
®Ä²v§ó´£¤É¤F~~^^
mmap

TOP

¦^´_ 1# mmap

¨Ï¥ÎSUMIF¨ç¼Æ§@ªk

B2
=SUMIF(¹ï·Óªí!$A:$A,$A2,INDEX(¹ï·Óªí!$A:$F,,MATCH(B$1,¹ï·Óªí!$1:$1,)))
¥k©Ô

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

TOP

        ÀR«ä¦Û¦b : ¤ß¤¤±`¦sµ½¸Ñ¡B¥]®e¡B·P«ä¡Bª¾¨¬¡B±¤ºÖ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD