Excel word ppt officeʹÓü¼ÇÉ´óÈ«(DOC¸ñʽ)-µÚ117ÕÂ

°´¼üÅÌÉÏ·½Ïò¼ü ¡û »ò ¡ú ¿É¿ìËÙÉÏÏ·­Ò³£¬°´¼üÅÌÉ쵀 Enter ¼ü¿É»Øµ½±¾ÊéĿ¼ҳ£¬°´¼üÅÌÉÏ·½Ïò¼ü ¡ü ¿É»Øµ½±¾Ò³¶¥²¿£¡
¡ª¡ª¡ª¡ªÎ´ÔĶÁÍꣿ¼ÓÈëÊéÇ©ÒѱãÏ´μÌÐøÔĶÁ£¡



£»5£»2£©£«MIDB£¨AF4£»7£»2£©/60¡·=17£»MIDB£¨AF4£»5£»2£©£«MIDB£¨AF4£»7£»2£©/60¡­9£»8£©£©¡¡¡¡¡¡

·½·¨¡¡2¡¡£ºnetworkdays¡¡ÐèÒªÄãÌṩһ¸ö½Ú¼ÙÈÕÁбí×÷Ϊ²ÎÊý¡£ÎÒ²ÂÄã²»»áϲ»¶ÊÖ¹¤ÊäÈëÕâÑùÒ»¡¡

¸ö±í¡£ÒÔϹ«Ê½¹¦ÄܸüΪǿ¾¢£¬Ëü¿ÉÒÔËã³öÁ½¸öµ¥Ôª¸ñ£¨A2¡¡ºÍ¡¡C2£©Ëù´¢´æµÄÈÕÆÚÖ®¼äµÄÖÜÄ©¡¡

ÌìÊý£¬³ËÒÔСʱÊý¼´¿É¼ÆËã³öÄãËù˵µÄ½Ú¼ÙÈÕʱÊý¡£µ«ÊÇ£¬ËüÎÞ·¨¼ÆËã¹úÇì½ÚµÈ¹«ÖÚ¼ÙÆÚ¡£¡¡¡¡¡¡

=IF£¨C2¡·A2£»SUM£¨IF£¨WEEKDAY£¨C2¡­ROW£¨INDIRECT£¨¡¨1£º¡¨&¡¡¡¡

C2¡­A2£©£©£»2£©¡·5£»1£»0£©£©£»SUM£¨IF£¨WEEKDAY£¨A2¡­ROW£¨INDIRECT£¨¡¨1£º¡¨&¡¡A2¡­C2£©£©£»2£©¡·5£»1£»0£©£©£©¡£ÕâÊÇ¡¡

Ò»¸öÊý×鹫ʽ£¬ÊäÈëÍê³Éºó°´¡¡CTRL£«SHIFT£«ENTER¡¡½áÊø¡£¡¡¡¡¡¡

·½·¨¡¡3¡¡£ºÓøñʽ¶¨Òå¡¡E4£»G4¡¡ÎªÈÕÆÚʱ¼ä¸ñʽ£º****¡­**¡­**¡¡¡¡**£º**¡£È»ºó£º£¨G4¡­E4£©*24¡¡¡¡¼´¿ÉµÃÁ½¡¡

ʱ¼äÄÚµÄСʱÊý¡£¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡ÈçºÎÔÚµ¥Ôª¸ñ·µ»Ø¹¤×÷±íÃû³Æ¡¡¡¡



´ð£º=RIGHT£¨CELL£¨¡¨filename¡¨£©£»LEN£¨CELL£¨¡¨filename¡¨£©£©¡­FIND£¨¡¨£§¡¨£»CELL£¨¡¨filename¡¨£©£©£©¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡ÈçºÎÔÚÊäÈëÊý×ֵļӼõ³Ë³ý°´¡¡ENTE¡¡ºóÄÜÔÚÁíÒ»µ¥Ôª¸ñ×Ô¶¯³öÏÖ¼ÆËãÊýÖµ¡¡¡¡



1¡¢±àÁ˸öºê£º¡¡¡¡¡¡¡¡

Sub¡¡aa£¨£©¡¡¡¡¡¡¡¡

Cells£¨1£»¡¡1£©¡¡=¡¡Mid£¨ActiveCell¡£Formula£»¡¡2£»¡¡13£©¡¡¡¡¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡379¡¡¡¡


¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­Page¡¡380¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­

¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡



End¡¡Sub¡¡¡¡¡¡¡¡

ÏÈÔÚµ¥Ôª¸ñÀïÊäÈ빫ʽµÃ³ö½á¹û£»Ñ¡¶¨µÃ³ö½á¹ûµÄµ¥Ôª¸ñÔËÐкê¾Í¿ÉÒÔÔÚA1¡¡³öÎı¾¡£¡¡¡¡

¡¡2¡¡¡¢ºê£¨²âÊÔͨ¹ý£©¡¡¡¡

Sub¡¡aa£¨£©¡¡¡¡¡¡¡¡

A¡¡=¡¡2¡¡'ÐÐ'¡¡¡¡¡¡¡¡

B¡¡=¡¡4¡¡'ÁС¡¡¡¡¡¡¡

LINE1£º¡¡¡¡¡¡¡¡

If¡¡Cells£¨A£»¡¡B£©¡¡=¡¡¡¨¡¨¡¡Then¡¡¡¡¡¡¡¡

Exit¡¡Sub¡¡¡¡¡¡¡¡

Else¡¡¡¡¡¡¡¡

Cells£¨A£»¡¡B¡¡£«¡¡1£©¡£Formula¡¡=¡¡¡¨=¡¨¡¡&¡¡Cells£¨A£»¡¡B£©¡¡¡¡¡¡¡¡

A¡¡=¡¡A¡¡£«¡¡1¡¡¡¡¡¡¡¡

GoTo¡¡LINE1£º¡¡¡¡¡¡¡¡

End¡¡If¡¡¡¡¡¡¡¡

End¡¡Sub¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡ÓС¡A1£¬B1£¬C1£¬D1¡¡Ëĸöµ¥Ôª¸ñD1¡¡µÄÖµÒªËæ×Å£Á£±µÄ±ä»¯¶ø±ä»¯¡¡¡¡¡¡



£Á£±ÓÐÈýÖֱ仯£¬Ò»¡¢¶þ¡¢Èý£»µ±£Á£±£½¡®Ò»¡¯Ê±£¬£Ä£±£½¡°¡¡¡¡¡±£»¡¡¡¡£Á£±£½¡®¶þ¡¯Ê±£¬£Ä£±¡¡

£½£¨£Â£±£«£Ã£±£©£¯£³¡¡¡¡£»¡¡¡¡£Á£±£½¡®¶þ¡¯Ê±£¬£Ä£±£½£¨£Â£±£«£Ã£±£©£¯£¶¡¡¡¡¡¡¡£ÒÔÉÏ¿ÉÒÔͨ¹ý£É¡¡

¡¡£ÆÀ´Íê³É¡¡¡¡¡¡¡£¿ÉÎÒÏ£ÍûµÄÊÇ£ºµ±£Â£±»ò£Ã£±Îª¿Õʱ£¬²»ÂÛ£Á£±ÎªÊ²Ã´£Ä£±¶¼Îª¿Õ£¬ÕâÑù×öµÃµ½¡¡

ô£¿¡¡¡¡

½â¡¡´ð¡¡£º¡¡1¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¢¡¡=IF£¨OR£¨B1=¡¨¡¨£»C1=¡¨¡¨£©£»¡¨¡¨£»IF£¨A1=¡¨¡¡¡¡¡¡¡¡¡¡¡¡Ò»¡¡¡¨£»¡¨¡¨£»IF£¨A1=¡¨¡¡¡¡¡¡¡¡¶þ¡¡¡¨£»£¨B1£«C1£©/3£»IF£¨A1=¡¨¡¡¡¡Èý¡¡

¡¨£»£¨B1£«C1£©/6£»¡¨¡¨£©£©£©£©¡¡¡¡

2¡¡¡¢=IF£¨OR£¨B1=¡¨¡¨£»¡¡C1=¡¨¡¨£»¡¡A1=¡¨Ò»¡¨£©£»¡¡¡¨¡¨£»¡¡IF£¨A1¡¡=¡¡¡¨¶þ¡¨£»¡¡£¨B1£«C1£©/3£»¡¡£¨B1£«C1£©/6£©£©¡¡¡¡

18¡¢¡¡¡¡¡¡¡¡¡¡¡¡Èç¹û¿¼ÂÇ¡¡¡¡A1¡¡¡¡Ã»ÓÐÊý¾ÝµÄ»°£º=IF£¨OR£¨B1=¡¨¡¨£»¡¡C1=¡¨¡¨£»¡¡A1=¡¨Ò»¡¨£»A1=¡¨¡¨£©£»¡¡¡¨¡¨£»¡¡IF£¨A1¡¡=¡¡¡¨¶þ¡¨£»¡¡¡¡

¡¡¡¡¡¡¡¡£¨B1£«C1£©/3£»¡¡£¨B1£«C1£©/6£©£©¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¶Ô¡¡A¡¡Áв»Öظ´µÄÊýÖµ¼ÆÊý¡¡¡¡



ÎÒÖ»ÄÜ×öµ½Ð½¨Ò»ÁУ¬B¡¡ÁУ¬È»ºóµÚÒ»¸öµ¥Ôª¸ñ¡¡countif¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡£¨A1£ºA100£»A1£©£¬È»ºóÍ϶¯µ½È«²¿¡¡

ÐÂÁС£×îºóÔÚÐÂÁÐÏÂÃæÓá¡sumif£¨B1£ºB100£»1£©¡¡¡¡Ë­ÓиüºÃµØ·½·¨¡£¡¡¡¡



¡¡¡¡¡¡¡¡¡¡½â´ð£º1¡¢£û=SUM£¨IF£¨A1£ºA100=¡¨¡¨£»¡¨¡¨£»1/£¨COUNTIF£¨A1£ºA100£»A1£ºA100£©£©£©£©£ý£¨ÓÖ¡¡

ÎÊ£º¹«Ê½Öеġ°1/£¨COUNTIF£¨A1£ºA100£»A1£ºA100£©£©¡±ÏñÊÇÒ»¸öµ¹Êý£¬ÔõôÀí½â£¿´ð£º¡¡

Óõ¹ÊýÊÇÕâ¸öÒâ˼£ºÈç¹ûÖ»³öÏÖÒ»´Î£¬Êý×éÖеÄÏàÓ¦Ïîͳ¼ÆΪ¡¡1£¬Æäµ¹ÊýΪ¡¡1£¬Sum¡¡Í³¼Æ¼Æ¡¡1¡£¡¡

Èç¹û³öÏÖ¡¡N¡¡´Î£¬Æäµ¹ÊýΪ¡¡1/N£¬³öÏÖÁËN¡¡´Î£¬ÇóºÍ¾ÍÊÇ¡¡Nx1/N£¬×îºó¡¡Sum¡¡Í³¼Æ¾ÍÖ»¼Æ¡¡1¡££©¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡ÓÀºãµÄÇóºÍ¡¡¡¡



¡¡¡¡¡¡¡¡¡¡1¡¢=SUM£¨OFFSET£¨A1£»£»£»ROW£¨£©¡­ROW£¨A1£©£©£©¿ÉÒÔ¶Ô¡¡A¡¡ÁÐÊýÖµ×Ô¶¯ÇóºÍ¡£¡¡¡¡



¡¡¡¡¡¡¡¡¡¡2¡¢=SUM£¨INDIRECT£¨¡¨R2C£ºR£§¡­1£§C¡¨£»FALSE£©£©¡¡¡¡¡¡



¡¡¡¡¡¡¡¡¡¡3¡¢=SUM£¨INDIRECT£¨¡¨A2£ºA¡¨&ROW£¨£©¡­1£©£©¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡380¡¡¡¡


¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­Page¡¡381¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­

¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡



19¡¢¡¡¡¡¡¡¡¡¡¡¼á³Ö²»Óá¡R1C1¡¡À¸ÃûÁкűíʾ·¨»¹ÓÐÒ»¸ö·½·¨£¬²»¹ýÓÖ¸´ÔÓÁËЩ¡£Ê×Ïȶ¨ÒåÒ»¸ö¹«Ê½£º¡¡

¡¡¡¡¡¡COL=IF£¨COLUMN£¨£©¡·26£»CHAR£¨INT£¨£¨COLUMN£¨£©¡­1£©/26£©£«64£©&CHAR£¨IF£¨MOD£¨COLUMN£¨£©£»¡¡

¡¡¡¡¡¡26£©=0£»26£»MOD£¨COLUMN£¨£©£»26£©£©£«64£©£»CHAR£¨COLUMN£¨£©£«64£©£©¡¡¡£ºóÓÚÓûÇó¼Ó×ÜÖ®´¢´æ¸ñÊäÈ룺¡¡

¡¡¡¡¡¡=SUM£¨INDIRECT£¨COL&¡¨1£º¡¨&COL&ROW£¨£©¡­1£©£©¡£Ôò´Ë¹«Ê½¸´ÖƵ½ÈκÎÒ»ÈνԿÉÓã¬ÓÖ²»Å¡¡

¡¡¡¡¡¡²úÉú´íÎóÖµ¡££¨×¢£ºCOL=IF£¨COLUMN£¨£©¡·26£»CHAR£¨MOD£¨COLUMN£¨£©£»26£©£«64£©&CHAR£¨INT£¨C¡¡

¡¡¡¡¡¡OLUMN£¨£©/26£©£«64£©£»CHAR£¨COLUMN£¨£©£«64£©£©¡¡¡£±©Â¶ÁËÒ»¸öΪÈ˲»ÖªµÄȱµã£¬Èç¹ûÁÐÊýµ½ÁËA¡¡

¡¡¡¡¡¡A¡¡ÁÐÒÔºó¾Í²»ÐÐÁË£¬ËäÈ»¿ÉÓá¡ADDRESS£¨£©½â¾ö£¬±ÈÓá¡CHAR£¨£©ºÃ¶àÁË£¬µ«¹«Ê½»¹ÊÇÌ«³¤£¬¡¡

¡¡¡¡¡¡ÓÃÔÚÒ»¸ö¼Ó·¨ÖÐʵÔÚ²»Öµ£©¡¡¡¡

20¡¡¡¢¡¡¡¡¡¡¡¡ÆäʵÓá¡ADDRESS¡¡¸üºÃ£¬=SUM£¨INDIRECT£¨ADDRESS£¨1£»COLUMN£¨£©£©&¡¨£º¡¨&ADDRES¡¡

¡¡¡¡¡¡S£¨ROW£¨£©¡­1£»COLUMN£¨£©£©£©£©£¬»¹ÊÇÒ»¾äÀÏ»°£¬Îª×öÒ»¸ö¼Ó·¨²»ÊÇÌ«Öµ£¬ÕâÖ»ÄÜÊǼ¼ÊõÉϵÄÌÖ¡¡

¡¡¡¡¡¡ÂÛ¡£¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡ÈçºÎʹÓá¡EXCEL¡¡½øÐж¯Ì¬¿ç±íÈ¡Êý¡¡¡¡



ÓÐÁ½¸öÎļþ£¬µÚÒ»¸öÎļþÓС¡31¡¡ÕÅÈÕ±¨±í£¬Ã¿ÌìÒ»¸ö±íµ¥£»µÚ¶þ¸öÎļþ½öÒ»¸ö±íµ¥£»ÈçºÎÔÚµÚ¡¡

¶þ¸öÎļþÖУ¬ÊäÈë¡¡1¡¡Ê±Óɺ¯Êý¶¯Ì¬È¡³öµÚÒ»ÕÅ±íµ¥Êý£¨Èç¡¡SHEET1£©£¬ÊäÈë2¡¡Ê±È¡£¨SHEET2£©¡¡

Êý£¬ÒÀ´ÎÀàÍÆ¡¨¡¨¡££¬ÈçºÎÉ蹫ʽ¡£ÔÚͬһÎļþÖпÉÒÔÓá¡INDIRECT¡¡ºÍ¡¡ADDRESS¡¡×éºÏ£¬¿ÉÊÇ¡¡

¿ç±íºÃÏó²»ÐУ¬Çë¸÷λָ½ÌÒ»¶þ£¡¡¡¡¡

½â´ð£ºÇ°ÌáÊÇÁ½¸ö¹¤×÷±í¶¼Òª´ò¿ª£º=INDIRECT£¨¡¨£§Book2£§Sheet¡¨&A1&¡¨£¡B1¡¨£©¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡ÈçºÎʹÓÃŒ¦ÕÕ±í¡¡¡¡



ÉèÒ»¶ÔÕÕ±íÈçÏ£ºÈçºÎʹÓú¯Êýµ±ÊäÈë¡£µ±a1=34¡¡¡¡Ôò¡¡a2¡¡ÏÔʾ¡¨ÒÒ¡¨¡¡¡¡b1=68¡¡¡¡Ôò¡¡b2¡¡ÏÔʾ¡¨¶¡¡¨/¡¡¼×¡¡¡¡2¡¡

2¡¡¡¡33¡¡¡¡44¡¡¡¡66¡£¡£¡£¡£/¡¡ÒÒ¡¡¡¡26¡¡¡¡34¡¡¡¡43¡¡¡¡62¡£¡£¡£¡£¡¡¡¡/±û¡¡¡¡28¡¡¡¡39¡¡¡¡41¡¡¡¡67¡£¡£¡£¡£¡¡¡¡/¶¡¡¡¡¡27¡¡¡¡31¡¡¡¡49¡¡¡¡68¡£¡£¡£¡£¡¡¡¡/Îì¡¡¡¡23¡¡¡¡32¡¡¡¡46¡¡¡¡64¡£¡£¡£¡£¡¡

¡¡¡¡/¡¡¼º¡¡¡¡29¡¡¡¡38¡¡¡¡47¡¡¡¡61¡£¡£¡£¡£¡¡¡¡/¸ü¡¡¡¡21¡¡¡¡37¡¡¡¡48¡¡¡¡69¡£¡£¡£¡£¡¡¡¡/ÐÁ¡¡¡¡24¡¡¡¡36¡¡¡¡42¡¡¡¡63¡£¡£¡£¡£¡¡¡¡¡¡¡¡

½â´ð£ºÈç¡¡A¡¡ÁÐÊäÈëµÄÊý×ÖÔÚ±íÖж¼´æÔÚ£¬°ÑÄãµÄÊý¾Ý·ÅÔÚ¡¡D¡¡µ½¡¡G¡¡ÁУ¬¼×ÒÒ±û¶¡Î켺ÐÁ·ÅÔÚ×î¡¡

ºóÒ»ÁУ¬¼´¡¡H¡¡ÁС£ÔÚ¡¡B¡¡ÁÐÊäÈ빫ʽ¡°=IF£¨A1=¡¨¡¨£»¡¨¡¨£»VLOOKUP£¨A1£»INDIRECT£¨ADDRESS£¨1£»IN¡¡

T£¨0¡£1*A1£©£«IF£¨A1¡·60£»1£»2£©£©£©£ºINDIRECT£¨ADDRESS£¨8£»8£©£©£»7¡¡¡­INT£¨0¡£1*A1£©£«IF£¨A1¡·60£»1£»0£©£»FALS¡¡

E£©£©¡¡¡±¡£Èç¼×ÒÒ±û¶¡Î켺ÐÁ·ÅÔÚµÚÒ»ÁУ¬¼´ÁÐÓù«Ê½¡°=IF£¨A1=¡¨¡¨£»¡¨¡¨£»INDEX£¨D£ºD£»MATCH£¨A1£»IN¡¡

DIRECT£¨ADDRESS£¨1£»INT£¨0¡£1*A1£©£«IF£¨A1¡·60£»2£»3£©£©£©£ºINDIRECT£¨ADDRESS£¨8£»INT£¨0¡£1*A1£©£«IF¡¡

£¨A1¡·60£»2£»3£©£©£©£»0£©£©£©¡¡¡±¡¡¡¡

½â´ð¡¡2¡¡£ºÈç¹ûÊý¾ÝÎÞ¹æÂÉ£¬¿ÉÓÃÒÔÏ·½·¨£ºÈçÄãµÄÊý¾Ý±íÔÚD¡¡µ½¡¡H¡¡ÁУ¬ÔÚ¡¡I¡¡ÁÐÊäÈ빫ʽ¡°=IF¡¡

£¨ISERROR£¨MATCH£¨A1£»E1£ºH1£»0£©£©=TRUE£»0£»row£¨£©£©¡¨²¢ÏòÏÂÌî³ä£¬ÔÚ¡¡B1¡¡µ¥Ôª¸ñÊäÈ빫ʽ¡°=IF£¨A¡¡

1=¡¨¡¨£»¡¨¡¨£»INDEX£¨D£ºD£»LARGE£¨I£ºI£»1£©£©£©¡±¡¡¡¡

½â´ð¡¡3¡¡£ºÎÒ½«½â´ð2¡¡¹«Ê½×öÁËСÐ޸ģ¨I¡¡Áй«Ê½É¾³ý£©¡¡£¬B1¡¡µ¥Ôª¸ñÊäÈ빫ʽ£º=IF£¨A1=¡¨¡¨£»¡¨¡¨£»INDE¡¡

X£¨D£ºD£»¡¡¡¡IF£¨ISERROR£¨MATCH£¨A1£»E1£ºH1£»0£©£©=TRUE£»0£»ROW£¨E1£ºH1£©£©£«¡¡¡¡IF£¨ISERROR¡¡

£¨MATCH£¨A1£»E2£ºH2£»0£©£©=TRUE£»0£»ROW£¨E2£ºH2£©£©£«¡¡¡¡IF£¨ISERROR£¨MATCH£¨A1£»E3£ºH¡¡

3£»0£©£©=TRUE£»0£»ROW£¨E3£ºH3£©£©£«¡¡¡¡IF£¨ISERROR£¨MATCH£¨A1£»E4£ºH4£»0£©£©=TRUE£»0£»ROW¡¡

£¨E4£ºH4£©£©£«¡¡¡¡IF£¨ISERROR£¨MATCH£¨A1£»E5£ºH5£»0£©£©=TRUE£»0£»ROW£¨E5£ºH5£©£©£«¡¡¡¡IF£¨ISER¡¡

ROR£¨MATCH£¨A1£»E6£ºH6£»0£©£©=TRUE£»0£»ROW£¨E6£ºH6£©£©£«¡¡¡¡IF£¨ISERROR£¨MATCH£¨A1£»E7£º¡¡

H7£»0£©£©=TRUE£»0£»ROW£¨E7£ºH7£©£©£«¡¡¡¡IF£¨ISERROR£¨MATCH£¨A1£»E8£ºH8£»0£©£©=TRUE£»0£»ROW¡¡

£¨E8£ºH8£©£©£©£©¡¡¡£¡¡¡¡

½â´ð¡¡4¡¡£ºÔÚB1¡¡µ¥Ôª¸ñÊäÈ