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

½Ð±Ð«ö¨â­Ó±ø¥ó³£²Å¦X§ä¨ì·j´M­Èªº¨ç¼Æ¼gªk

¥»©«³Ì«á¥Ñ gong ©ó 2011-3-30 00:00 ½s¿è

¥i§_¦A½Ð±Ð¤@¤U¨ç¼Æ¤¤
=LOOKUP(1,1/($A$2:$A$18&$B$2:$B$18=$F2&$G2),$C$2:$C$18)
²Ä¤@­Ó¤Þ¼Æ1¬O«ü?
²Ä¤G­Ó¤Þ¼Æªº·N«ä¬O?

­n¥ýÁA¸Ñlookup¯S©Ê,
¦³±Æ°£¿ù»~¡B
¨ú1/2¦ì¸m­È¤ñ¸û,¦p¤ñ¸û­È¤ñ¤¤¶¡­È¤j¦A¨ú«á1/2¦ì¸m­È¦A¤ñ¸û,
¦p¤ñ¸û­È¤ñ¤¤¶¡­È¤p«h¨ú«e1/2¦ì¸m­È¦A¤ñ¸û
¦p¦¹´`Àô,¬Glookup¦³»Ý±Æ§Ç¤§¯S©Ê

¦A»¡
1/($A$2:$A$18&$B$2:$B$18=$F2&$G2)
"1"ªº¥Øªº¬O±N"$A$2:$A$18&$B$2:$B$18=$F2&$G2"¥H¤À¼Æ¤è¦¡ªí¥Ü,¸Ó²Õ¼Æ¦p³£¤j©ó1,
¤À¼Æªí¥Ü«áÀ³¸Ó¤£·|>=1

=LOOKUP(1,1/($A$2:$A$18&$B$2:$B$18=$F2&$G2),$C$2:$C$18)
²Ä¤@­Ó"1"«h¬°¯Á¤Þ­È,
¦¹¨Ò {0,0,0,1,0,0,0,...} «h­n¬Ý¹B®ð¬O§_·|§ä¨ì¨º­Ó1¤F,

§ó¥¿
¤£·|²£¥Í{0,0,0,1,0,0,0,...}
¦Ó¬O{#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!}
¸g±Æ°£¿ù»~­È¨ú±o1
ª¾¤§¬°ª¾¤§¡A¤£ª¾¬°¤£ª¾¡A¸Û¹ê¤]¡I

TOP

¥»©«³Ì«á¥Ñ gong ©ó 2011-3-29 23:47 ½s¿è

d12
{=IF(ISNUMBER(MATCH($B12&$C12,$A$2:$A$7&$B$2:$B$7,0)),OFFSET($C$1,MATCH($B12&$C12,$A$2:$A$7&$B$2:$B$7,0),),IF(COUNTIF($A$2:$A$7,$B12),"µL¶Ç²¼¸ê®Æ","µL¸Ó¶µª÷ÃB"))}

e12
{=IF(ISNUMBER($D12),OFFSET($D$1,MATCH($B12&$C12,$A$2:$A$7&$B$2:$B$7,0),),"")}

§ó¥¿d12
{=IF(ISNUMBER(MATCH($B12&$C12,$A$2:$A$7&$B$2:$B$7,0)),OFFSET($C$1,MATCH($B12&$C12,$A$2:$A$7&$B$2:$B$7,0),),IF(COUNTIF($A$2:$A$7,$B12),"µL¸Ó¶µª÷ÃB","µL¶Ç²¼¸ê®Æ"))}

¹Ï¤ÎÀɮצp¦³¤U¸ü½Ð¦Û¦æ§ó¥¿
¥¼©R¦W.JPG

Book1.rar (2.24 KB)

ª¾¤§¬°ª¾¤§¡A¤£ª¾¬°¤£ª¾¡A¸Û¹ê¤]¡I

TOP

¦^´_ 11# gong


·PÁª©¥Dªº»¡©ú¦³¤ñ¸ûÀ´
§Úªº°ò¦¤Ó®t
·|§ó¥[§V¤O¾Ç²ß

¯uªº«Ü·PÁ¤j®aÅý§Úªº¤u§@§Ö³t¦a¶¶§Q§¹¦¨
Ann

TOP

d12
{=IF(ISNUMBER(MATCH($B12&$C12,$A$2A$7&$B$2B$7,0)),OFFSET($C$1,MATCH($B12&$C12,$A$2A$7&$B$ ...
gong µoªí©ó 2011-3-29 23:30


ÁÂÁª©¥D
Åý§Ú¦³ÁŵM¶}®Ôªº·Pı
ÁöµLÅv­­¤U¸ü±z¬°§Ú»¡©úªºÀÉ®×
¦ý¦]¬°¬O¦Û¤vªº¸ê®Æ
ª¾¹D¸ê®Æ¤º®e

§Úªº²z¸Ñ¤£ª¾¹ï¤£¹ï
¥D­n¬O
§Q¥Îmatch±o¨ì°}¦C»P·j´M­È¬Û²Å¦X¤§¬Û¹ï¦ì¸m
¦A¥Îoffset¨Ì¸Ó¦ì¸m¨ú±o·j´M­È
¥t¥~´N¬O¹B¥ÎIF»PISNUMBER¥h§PÂ_<-³o¤ñ¸û®e©ö±q®Ñ»POFFICE»¡©ú²z¸Ñ

¯uªº«Ü·PÁ¤ñ§Ú¨ì®Ñµó½¬ÝN¥»®Ñ
¨Ó±o²M·¡©ú¥Õ
Ann

TOP

¦^´_ 11# gong
·PÁÂ~gong ª©¤jªº¸ÑÄÀ¡A
¬Ý§¹ª©¤jªº¸ÑÄÀ«á¡A¹ïLOOKUPªº¨ç¼Æ¦³¤ñ¸û²`ªº¤F¸Ñ¤F¡A
¤§«e¥uª¾¹D«ç»ò¥Î¡A¦Ó¤£ª¾¹D³o­Ó¨ç¼Æªº²[¸q¡A
¦A¦¸·PÁª©¤j¤£§[«ü¾É~
¨S¨Æ´N¨Ó³}³}

TOP

§Úı±oÁÙ¦n
ÁöµM§Ú¤]¨S¦³Åv­­¤U¸ü
³ßÅw³o­Ó½×¾Â´N¦³¸q°È´L¦u»P°t¦X½×¾Âªº¨î«×
ªÖ¥I¥X´N¦³¦¬Àò

TOP

¥»©«³Ì«á¥Ñ ann ©ó 2011-3-30 11:01 ½s¿è
¼Ó¤W°Q½×¦³´XÂI¤£¿ù
¦³®É¤H®a¦b¤u§@,­n¤Q¸U¤õ«æ§¹¦¨,´Nª½±µ´£¨Ñµª®×¦n¤F
ÁÙ¦³¤U¸üÅv­­¬O¤£¬O¦³ÀË°QªºªÅ¶¡ ...
FAlonso µoªí©ó 2011-3-29 22:58


­º¥ý¯uªºÄ±±o«Ü©¯¹B§ä¨ì³Â»¶®a±Ú°Q½×°Ï
Åý§xÂZ¦Û¤vªº°ÝÃD±o¨ì°ª¤âªº«üÂI
§Ö³t¦a¸Ñ¨M
¤ß¤¤¯uªº°£¤F·PÁ´N¬O¦A·PÁÂ

¥u¬O¥i¯à¤j®aµ¥¯Å³£«Ü°ª¤F
¤£ÁA¸Ñ·s¤â­±¹ïªº§x¹Ò
¦b¯u¤ß³ß·R³o¸Ìªº«e´£¤U
´£¥X¦Û¤vªº±¡§Îµ¹¤j®a°Ñ¦Ò

·s¤â¤£¯à¤U¸üÀÉ®×:<-¤w¸g¸Ñ¨M  «Ü»{¦PÃÙ§U½×¾Âªº²z©À
§Ú¯à²z¸Ñ
¦ý¹ï©ó¦Û¤v´£¥Xªº°ÝÃD
§O¤Hµ¹¤©¸Ñ¨Mªº¦^À³¤]¤£¯à¤U¸ü
¦³®É¸ê®Æ­Y¹L©ó½ÆÂø
¥i¯à¤£®e©ö²z¸Ñ

¦b¬ãŪª©¤W¤w°Q½×¹Lªº®×¨Ò
¦]µL¤U¸üÅv­­
¯uªº«ÜÃø©ú¥Õ(·íµM§Ú¤ñ¸û²Â¤]¦³Ãö«Y)
¤£¬O¬G·N­«Âе¹¤j®a§ä³Â·Ð
ÁÂÁª©¥D»P°ª¤â­Ìªº­@¤ß®e§Ô
§ó·PÁ¤j®aµ¹¤©ªº¨ó§U
Ann

TOP

EXCELªº¨ç¼Æ»¡©ú,ÁÙ¦³«Ü¤j¦¨ªøªºªÅ¶¡,¦³®É»¡ªº¬O¤£²M¤£·¡,¦pªG¦b½×¾Â¤W¦³¬Ý¤£À´ªº¤½¦¡.¬d¬Ý»¡©ú¬O¤@©w­nªº,
¥t¥~¥i¦b½s¿è¦C¤¤¬d¬Ý,¨Ò¦p
{=IF(ISNUMBER($D12) ,OFFSET($D$1,MATCH($B12&$C12,$A$2:$A$7&$B$2:$B$7,0),),"")}
±N¨ä¤¤¤@¬q¤½¦¡¤Ï¥Õ¦A«öF9.¥i±o¨ì¨º¬q¤½¦¡ªº¹Bºâµ²ªG.¥H§Q¤F¸Ñ¸ûªøªº¤½¦¡.¬Ý§¹°O±o«öESCÁä©ñ±ó­×§ï.

TOP

EXCELªº¨ç¼Æ»¡©ú,ÁÙ¦³«Ü¤j¦¨ªøªºªÅ¶¡,¦³®É»¡ªº¬O¤£²M¤£·¡,¦pªG¦b½×¾Â¤W¦³¬Ý¤£À´ªº¤½¦¡.¬d¬Ý»¡©ú¬O¤@©w­nªº, ...
ANGELA µoªí©ó 2011-3-30 10:24

·PÁ±zªº¤À¨É
¹ï·s¤â²z¸Ñ¨ç¼Æ¥Îªk¦Ó¨¥¹ê¦b¤Ó¤è«K¤F
­ì¥»¥u·|¶Ì¶Ì¦a¤@­Ó¨ç¼Æ¤@­Ó¨ç¼ÆºCºC¦a©î¸Ñ
Ann

TOP

¦^´_ 18# ANGELA
¶Ì¶Ìªº¤@­Ó¤@­Ó©î+1...
­ì¨ÓÁÙ¦³³o¼Ë¤è«Kªº¥\¯àªü...
·PÁ«ü¾É~
¨S¨Æ´N¨Ó³}³}

TOP

        ÀR«ä¦Û¦b : ºw¤ô¦¨ªe¡C²É¦Ì¦¨ÅÚ¡A¤Å»´¤vÆF¡A¤Å¥Hµ½¤p¦Ó¤£¬°¡C
ªð¦^¦Cªí ¤W¤@¥DÃD