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

¡i°Q½×¡j¦h±ø¥ó¬d§ä¡]¨ç¼Æªk¡^

¡i°Q½×¡j¦h±ø¥ó¬d§ä¡]¨ç¼Æªk¡^

¦p¤U¹Ï¡A ²Ä12¦C¨Ì¡§¦a°Ï¡¨©M¡§³¡ªù¡¨¬d§ä¸ê®Æ¡C

¦³­þ¨Ç¤èªk¡H

C12 = excel ¨ç¼Æ

ss

¥»©«³Ì«á¥Ñ sunnyso ©ó 2013-6-15 22:15 ½s¿è

¦^´_ 1# sunnyso

=SUMIFS(C2:C8,A2:A8,A12,B2:B8,B12)

=SUMPRODUCT((A2:A8=A12)*(B2:B8=B12)*C2:C8)

=LOOKUP(1, 0/(A2:A8=A12)*(B2:B8=B12),C2:C8)

{=SUM((A2:A8=A12)*(B2:B8=B12)*C2:C8)}

=LOOKUP(1,0/(A2:A8&B2:B8=A12&B12),C2:C8)
ss

TOP

¥»©«³Ì«á¥Ñ sunnyso ©ó 2013-6-15 22:36 ½s¿è

{=INDEX(C2:C8,MATCH(A12&B12,A2:A8&B2:B8,0))}

=LOOKUP(1, 1/(((A2:A8=A12)+(B2:B8=B12))=2),C2:C8)

{=SUM(IF(A2:A8=A12,IF(B2:B8=B12,1,0),0)*C2:C8)}
ss

TOP

¥»©«³Ì«á¥Ñ sunnyso ©ó 2013-6-15 22:48 ½s¿è

{=VLOOKUP(A12&B12,CHOOSE({1,2}, A2:A8&B2:B8, C2:C8),2,0)}

{=OFFSET(C1,MATCH(A12&B12,A2:A8&B2:B8, 0),)}

{=MAX((A2:A8=A12)*(B2:B8=B12)*C2:C8)}
ss

TOP

¦A¨Ó¤@­Ó
{=LARGE((A2:A8=A12 )*(B2:B8=B12) *C2:C8,1)}
ss

TOP

=LOOKUP(A12&B12,A2:A8&B2:B8,C2:C8)
³o­Ó¤½¦¡¹ï¥»¨Òµ²ªG爲 #NA¡A °²¦p§âA¡BBÄæ¥Î­^¤å¥N´À¥i¥H±o¨ìµª®×¡C½Ð­þ¦ì¤j¤jÀ°¦£¤ÀªR¤@¤U
ÁÂÁÂ
ss

TOP

¦^´_ 6# sunnyso


    LOOKUP()¥²¶·±Æ§Ç

TOP

¦^´_ 6# sunnyso
> =LOOKUP(A12&B12,A2:A8&B2:B8,C2:C8)
> ³o­Ó¤½¦¡¹ï¥»¨Òµ²ªG爲 #NA¡A °²¦p§âA¡BBÄæ¥Î­^¤å¥N´À¥i¥H±o¨ìµª®×¡C½Ð­þ> ¦ì¤j¤jÀ°¦£¤ÀªR¤@¤U
>ÁÂÁÂ

°Ñ¼Æ2»Ý­n±Æ§Ç¡A¨Ã¤£¬O­^¤å¥i¥H(¥i¯à­è¦n¦³±Æ§Ç)¤¤¤å¤£¦æ¡C

LOOKUP ¬d¸ßªº¼Ð·Ç¦¡´N¬O§A2¼Óªº¦¡¤l¡C
=LOOKUP(1,0/(A2:A8&B2:B8=A12&B12),C2:C8)

¦¹¦¡§Q¥ÎLOOKUP¨ç¼Æ¦³±Æ°£¿ù»~ªº¯S©Ê¡A±N¤£²Å¦X±ø¥ó¥H 0/FLASE = #DIV/0! «¬¦¡¥X²{¡ALOOKUP¦b¬d¸ß¤¤¦Û°Ê©¿²¤¡C
²Å¦X±ø¥ó¥H 0/TRUE = 0 «¬¦¡¥X²{¡ALOOKUP¥H1 ¬d¸ß®É·|§ä¨ì³Ì«á¤@­Ó0ªº¦ì¸m¡A¦^¶Ç©Ò¹ïÀ³²Ä3°Ñ¼Æªº­È¡C
­Y¥u¦³¤@­Ó²Å¦X±ø¥óªº¸ê®Æ©Î²Å¦X¸ê®Æ¤¤¥u­nÀH·N¤@­Ó®É¡A²Ä1°Ñ¼Æ¥i¥H¬Ù²¤(¬d¸ß­È´N¬O0)¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 8# ML089

·PÁ»¡©ú
ss

TOP

Database formula

=DSUM(A1:C8,3,A11:B12)
ss

TOP

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