Excel word ppt officeʹÓü¼ÇÉ´óÈ«(DOC¸ñʽ)-µÚ64²¿·Ö

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



2¡¢rank¡¡º¯Êý£¨=RANK£¨A2£ºA11£»A2£ºA11£»0£©£¨¼ÙÉèÊý¾ÝÔÚ¡¡A2£ºA11¡¡µ¥Ôª¸ñ£¬ÏÂͬ£©¡¡¡¡

3¡¢Ê¹Óá¡contif¡¡º¯Êý½øÐÐÅÅÁС°=countif£¨a2£ºa11£»¡¨¡·¡¨&a2£©£«1¡¨¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡ÓÐÎÞ°ì·¨Èá¡B2¡¡ËùÔÚÐж¼³ÊºìÉ«×ÖÌå¡¡¡¡



½â´ð£º¼ÙÉèÄãÓÐÒ»¸ö¡¡B¡¡ÁкÍÒ»¸ö¡¡A1¡¡µÄÖµ£¬ÄãµÄÄ¿µÄÊÇ£¬Èç¹ûB2£½A1¡¡µÄ»°£¬Õû¸öB¡¡Áж¼ÎªºìÉ«¡¡

ÏÔʾ£¡¡¡¡¡

ÉèÖÃÈçÏ£ºÏÈÑ¡¶¨Õû¸ö¡¡b¡¡ÁУ¬Ò²¾ÍÊÇÔÚ¡¡B¡¡ÁÐÁб괦µ¥»÷£¨·Ï»°¡«^_^£©£¬Ñ¡Ôñ¸ñʽ£­Ìõ¼þ¸ñʽ¡¡¡¡

³öÏÖÌõ¼þ¸ñʽ¶Ô»°¿ò£¬µ¥»÷×ó±ßµÄÏÂÀ­ÁÐ±í£¬ÀïÃæÖ»ÓÐÁ½Ïµ¥Ôª¸ñÊýÖµºÍ¹«Ê½£¬Ñ¡Öй«Ê½£¬¡¡

Óұ߾ͿÉÒÔÊäÈëÈκοÉÒÔ·µ»ØÂß¼­ÖµµÄ¹«Ê½ÁË¡£ÊäÈëÕâ¸ö¹«Ê½=£¨B2=A1£©¡£Ç§Íò×¢ÒâÒªÓþø¡¡

¶ÔÒýÓã¬ÒòΪÈç¹ûÊÇÏà¶ÔµÄ£¬excel¡¡ÓÖ×Ô×÷Ö÷ÕŵÄÒ»¸öÒ»¸öÅжÏÁË£¬¾ÍûÓÐ×÷ÓÃÁË¡£¡¡£¨¾ø¶ÔÕý¡¡

È·²¢ÇÒºÃÓã©¡¡¡¡



¡¡¡¡¡¡¡¡¡¡ÏÖÓС¡12¡¡¸ö¹¤×÷±í£»ÊÇ¡¡12¡¡ÕÅ·¢Æ±£»½¨Á¢Ò»¸ö»ã×Ü±í£»½«·¢Æ±ºÅºÍ½ð¶î»ã×ÜÏÔʾÔÚÒ»ÕűíÀï¡¡¡¡



£¨·¢Æ±ºÅºÍ½ð¶îÔÚÿÕűíµÄÏàͬλÖã©¡£¡¡¡¡

½â´ð£ºÔÚ¡¡A1¡¡ÊäÈë¡¡¡¡=INDIRECT£¨¡¨sheet¡¨&ROW£¨£©&¡¨£¡d3¡¨£©¡¡¡¡

ÔÚ¡¡B1¡¡ÊäÈë¡¡¡¡=INDIRECT£¨¡¨sheet¡¨&ROW£¨£©&¡¨£¡d10¡¨£©¡¡¡¡

ÔÙÑ¡Ôñ¡¡A1£ºB1¡¡Íùϸ´ÖƵ½µÚ¡¡12¡¡ÐС£¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¾­Ñé¼¼ÇÉ¡¡¡¡



°´¡¡¡°Ctrl£«~¡±¿ÉÒÔÒ»´ÎÏÔʾËùÓй«Ê½£¨¶ø²»ÊǼÆËã½á¹û£©¡£ÔÙ°´Ò»´Î»Øµ½¼ÆËã½á¹û¡££¨³ÌÏãÖ棩¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡ÔÚÒ»¸ö²»¶Ô³ÆµÄÇøÓòÖÐÈ磨b1£ºG7£©ÖÐÕÒµ½A¡¡ÐÐÒ»×éÊý¾ÝÖеÄij¸öÊý²¢×Ô¶¯±äºì¡¡¡¡



½â´ð£ºÆäʵҲºÜ¼òµ¥£»ÄãֻҪѡ¶¨ÄãµÄ¡¡b1£ºg7£¬ÉèÖÃËüµÄÌõ¼þ¸ñʽΪ=£¨COUNTIF£¨A1£ºA7£»b1£©£©¡¡¡¡

×¢Ò⣻b1¡¡ÎªÏà¶ÔÒýÓã»ÕâÀïÊäÈëËùÑ¡ÇøÓòµÄµÚÒ»¸öÈ¡Öµ£¬ÄÇÑùÄãµÄËùÑ¡ÇøÓò»á×Ô¶¯Ìî³ä¡£´ïµ½Äã¡¡

ÒªµÄЧ¹û¡£¡¡£¨ºÃ£©¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡²»½èÖúµÚÈýÁжøÖ±½ÓÓú¯Êý»ò¹«Ê½Ò»²½µÃµ½¡¡sum£¨a2/b2£»a3/b3£»¡¨£©µÄ½á¹û¡¡¡¡



½â´ð£ºÊäÈë=sum£¨a1£ºa100/b1£ºb100£©£¬°´¡¡ctrl£«shift£«Enter¡£¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡ÇëÎÊÒªÈçºÎËã³öÿ¸öÔÂÓм¸¸öÐÇÆÚÒ»¡¢¶þ¡¢Èý¡¨¡£ÈÕ¡¡¡¡



½â´ð£ºÎª¼òµ¥Æð¼û£»±í¸ñÐè×÷һϵ÷Õû£»½«¡¡¡¨ÐÇÆÚÈÕ¡¨¡¡ÒƵ½¡¡C1£»ÆäºóÒÀ´Î£»ÕâÒ²·ûºÏ¹æÔò¡¡£¨Çë²ÎÔÄ¡¡

º¯¡¡¡¡¡¡¡¡Êý¡¡¡¡¡¡£º¡¡¡¡¡¡¡¡WEEKDAY£¨£©£©¡£¡¡¡¡¡£¡¡¡¡¡¡¡¡ÔÚ¡¡¡¡¡¡¡¡¡¡¡¡¡¡C2¡¡¡¡¡¡¼ü¡¡¡¡¡¡Èë¡¡¡¡¡¡¡¡Êý¡¡¡¡¡¡×é¡¡¡¡¡¡¡¡¹«¡¡¡¡¡¡Ê½¡¡¡¡¡¡¡¡£º¡¡¡¡

£û=SUM£¨IF£¨WEEKDAY£¨DATE£¨A2£»B2£»ROW£¨INDIRECT£¨¡¨A1£ºA¡¨¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡&¡¡¡¡

DAY£¨DATE£¨A2£»B2£«1£»1£©¡­1£©£©£©£©£©=COLUMN£¨£©¡­2£»1£©£©£ý¡¡£¬ÏòÓÒ¸´ÖÆ¡¢Ïòϸ´ÖÆ¡£¹«Ê½½âÊÍÒ»¡¡

µã£ºROW£¨INDIRECT£¨¡¨A1£ºA¡¨¡¡&¡¡DAY£¨DATE£¨A2£»B2£«1£»1£©¡­1£©£©£©Êµ¼ÊÉÏÊÇ´Ó¡¡1¡¡ºÅ²âÊÔµ½±¾Ôµġ¡

×îºóÒ»Ìì¡£ÈçÐèÒª£»¹«Ê½¿ÉÔÙ×÷¾«¼ò¡£¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡351¡¡¡¡


¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­Page¡¡352¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­

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



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡ÈÃÒþ²ØµÄÁлòÐв»²ÎÔ¤¼ÆËã¡¡¡¡



½â´ð£ºÊ¹Óá¡subtotal¡¡º¯Êý£»ÏêϸÓ÷¨²Î¼û°ïÖú¡£¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡Ò»´ÎɾÍê¡¡Excel¡¡ÀïÃæ¶à³öºÜ¶àµÄ¿Õ°×ÐС¡¡¡



½â´ð£º1¡¢Ó÷ÖÃæÔ¤ÀÀ¿´¿´¡¡¡¡

2¡¢ÓÃ×Ô¶¯É¸Ñ¡È»ºóɾ³ý¡¡¡¡

3¡¢ÓÃ×Ô¶¯É¸Ñ¡£¬Ñ¡ÔñÒ»ÁÐÓ÷ǿհף¬¿Õ°×ÐоͿ´²»µ½ÁË£¬´òÓ¡Ò²²»»á´ò³öÀ´¡£µ«ÊÇʵ¼ÊÉÏ»¹¡¡

ÊÇÔڵģ¬²»Ëãɾ³ý¡£»òÕßÓÃ×Ô¶¯É¸Ñ¡Ñ¡Ôñ¿Õ°×½«¿Õ°×ÐÐÈ«ÏÔ³öÀ´Ò»´ÎɾÍêÒ²¿ÉÒÔ¡£¡¡¡¡

4¡¢ÏȲåÈëÒ»ÁУ¬ÔÚÕâÒ»ÁÐÖÐÊäÈë×ÔÈ»ÊýÐòÁУ¬È»ºóÒÔÈÎÒ»ÁÐÅÅÐò£¬ÅÅÐòÍêºóɾ³ýÊý¾ÝºóÃæµÄ¡¡

¿ÕÐУ¬ÔÙÒÔ¸Õ²ÅÊäÈëµÄÒ»ÁÐÅÅÐò£¬ÅÅÐòºóɾ³ý¸Õ²Å²åÈëµÄÒ»ÁС£¡¡¡¡



¡¡¡¡¡¡¡¡±í¡¡1¡¢±í¡¡2¡¡·Ö±ðÓС¡20¡¡¸öÈ˵Ļù±¾Çé¿öºÍÆäÖС¡10¡¡¸öÈ˵ÄÃû×Ö£¬ÈÃ±í¡¡1¡¡µÄÊý¾Ý×Ô¶¯Ìî³äµ½±í¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡2¡¡¡¡



´ð£º1¡¢Óá¡lookup¡¡º¯Êý¼´¿É¡£Òª±£Ö¤¡¡20¡¡È˲»ÖØÃû£»¡¡¡¡

2¡¢¼ÙÉè±í¡¡1¡¡µÄD¡¡ÁжÔÓ¦±í¡¡2¡¡µÄE¡¡ÁС£E2¡¡µÄ¹«Ê½£º=VLOOKUP£¨B2£»Sheet1£¡B£ºD£»3£»FALSE£©¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡Ê¹Óá¡vlookup¡¡º¯Êý·µ»Ø#N/A¡¡·ûºÅʱ½«´Ë·ûºÅÓá¡0¡¡»ò¿Õ¸ñÀ´´úÌæ¡¡¡¡



´ð£ºÕâÑù´¦Àí£º¡¡=IF£¨ISNA£¨VLOOKUP£¨C13£»A1£ºB10£»2£©£©£»0£»VLOOKUP£¨C13£»A1£ºB10£»2£©£©¡¡¡¡

»ò£ºIF£¨ISERROR£¨vlookup£¨a1£»e1£ºg10£»2£»0£©£©£»0£»vlookup£¨a1£»e1£ºg10£»2£»0£©£©¡£¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡Í¨¹ýÌõ¼þ¸ñʽ½«Ð¡¼ÆºÍ×ܼƵÄÐÐÉèΪ²»Í¬µÄÑÕÉ«¡¡¡¡



´ð£ºÊäÈë=RIGHT£¨RC£»1£©=¡¨¼Æ¡¨£»É趨×ÖÌå¡¢±ß¿ò¡¢Í¼°¸£»È·¶¨¡£¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¸´ÖÆÒþ²ØºóµÄ±í¸ñµ½Ò»¸öбí¸ñÖÐʹ±»Òþ²ØµÄÄÚÈݲ»ÏÔʾ¡¡¡¡



´ð£ºcrtl£«g£­Ñ¡¿É¼ûµ¥Î»¸ñ£­¸´ÖÆ£­Õ³Ìù¡£¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡ÈçºÎ½«Ò»¸ö¹¤×÷²¾ÖеÄÒ»¸ö¡¡Sheet¡¡Òþ²Ø¡¡¡¡



´ð£º1¡¢Ñ¡¡°¸ñʽ¡±¡­¡­¡°¹¤×÷±í¡±¡­¡­¡­¡°Òþ²Ø¡±¡¡¡¡

2¡¢Ê¹ÓÃVBA¡¡ÕâÑùÒþ²ØºóÔÚʹÓù¤×÷±í±£»¤¡£¡¡¡¡

Alt£«F11¡­¡­¡­¡­Ctrl£«G¡­¡­¡­¡­³öÏÖÁ¢¼´Ö´Ðд°¿Ú£¬ÔÚ´Ë´°¿ÚÄÚÖ´ÐС¡¡¡

Sheet1¡£Visible¡¡=¡¡xlSheetVeryHidden¡¡¡¡

ÕâÑùÒþ²Øºó¡¡sheet¡¡ÔÚ¸ñʽ¡­¡­¡­¹¤×÷±í¡­¡­¡­¡­È¡ÏûÒþ²ØÊÇ¿´²»¼ûµÄ¡£¡¡¡¡

ÎÊ£º·½·¨2¡¡¸üºÃŶ£¬ÈçºÎ»Ö¸´ÄØ£¿¡¡¡¡

´ð£ºsheet1¡£Visible¡¡=xlSheetVisible¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¹¤¾ß²Ëµ¥ÓëÊÓͼÖеŤ¾ßÀ¸²»Í¬¡¡¡¡



ÆÁ±Î¹¤¾ß²Ëµ¥ºê¡¡¡¡

sub¡¡notool£¨£©¡¡¡¡

MenuBars£¨xlWorksheet£©¡£Menus£¨¡¨¹¤¾ß¡¨£©¡£Delete¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡352¡¡¡¡


¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­Page¡¡353¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­

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



end¡¡sub¡¡¡¡

½â³ýÆÁ±Î¡¡¡¡

sub¡¡yestool£¨£©¡¡¡¡

MenuBars£¨xlWorksheet£©¡£reset¡¡¡¡

end¡¡sub¡¡¡¡¡¡

Alt£«F11¡¡½øÈë¡¡VBA¡¡±à¼­¡¢²åÈëÄ£¿é¡¢½«ÉÏÃæºê¸´ÖƵ½Ä£¿é¡¢ÔËÐкꡣOK¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡²éÕÒ²¢Ìîд·ûºÏÌõ¼þµÄµ¥Ôª¸ñÄÚÈÝ¡¡¡¡



ÎÒÔÚ¹¤×÷ÖÐÐè¿ìËÙ¸´ÖÆÿÐжà¸öÊý¾Ý£¨µ¥Ôª¸ñ£©ÖÐ×îСֵËù¶ÔÓ¦µÄ¡°±êÌâÃû¡±£¬Èç¡¡E6¡¡ÊÇ¡¡C6£º¡¡

Y6¡¡ÖеÄ×îСֵ£¬Ëù¶ÔÓ¦µÄ±êÌâÊÇE5¡¡µ¥Ôª¸ñ¡°Ä³Ä³¹«Ë¾¡±£¬Òª½«Æ䣨ijij¹«Ë¾£©¸´ÖƵ½¡¡B6¡¡µ¥Ôª¡¡

¸ñÖУ¬ÒÔ´ËÀàÍƵĸ´ÖƺܶàºÜ¶àÐеÄÄÚÈÝ¡£Èç¹ûÊÇÊÖ¹¤Ò»¸öÒ»¸ö²éÕÒÓ븴ÖÆ£¬ÊµÔÚÊÇÌ«ÂýÌ«±¿¡¡

ÁË£¬ÄÜ·ñʹÓÃÒ»¸ö¼òµ¥µÄ¹«Ê½¼ÆËãÄØ£¿¡¡¡¡

´ð£ºB6¡¡µ¥Ôª¸ñ¡¨=INDEX£¨C5£ºY5£»MATCH£¨MIN£¨C6£ºY6£©£»C6£ºY6£»0£©£©¡¨¡¡¡¡¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡Ìîд¿Õ°×ÐС¡¡¡



ÎÒÓиöͬÊÂÔÚÒ»ÕÅ¿Õ°×±íÒÀ´ÎÊäÈëÊý¾Ý£¬ÎªÁËÊ¡ÊÂËý°ÑºÍÉÏÒ»¸ñÄÚÈÝÏàͬµÄµÄÊ¡ÂÔ²»Ê䣬ÊäÁË¡¡

½ü¡¡200¡¡ÐС£ºóÀ´ÓÖ¾õµÃ²»¹»Õýʽ£¬Ïë°Ñ¿Õ°×µÄµØ·½²¹ÉÏ¡£ËýÀ´ÎÊÎÒÔõô°ìºÃ¡£µ±È»ÒÀ´ÎÌî³äÒ²¡¡

ÐУ¬µ«ÎÒ¾õµÃ·³£¨Èç¹ûÓС¡2000¡¡ÐÐÔõô°ìºÇºÇ£©ÎÒÏëÁËÒ»¸ö²»ÊÇ°ì·¨µÄ°ì·¨£ºÔÚ¡¡A¡¡Áк͡¡B¡¡ÁÐÅÔ¡¡

¸÷²åÈëÒ»ÁУ¬ÏÖÔÚ¾ÍÓС¡ABCDE¡¡ÁУ¬ÎÒÔÚ¡¡B2¡¡Öи´ÖÆÁË¡¡A2¡¡ÖеÄÄÚÈÝ£¬È»ºóÔÚ¡¡B3¡¡ÖÐÊäÈ빫ʽ£º¡¡

IF£¨A3=0£»B2£»A3£©£»È»ºóÍùÉÏÍùϸ´Öƹ«Ê½¡£ÕâÑù¾ÍÌîÂúÁË¡£Èç·¨ÅÚÖÆ¡¡D¡¡ÁкóÒþ²Ø¡¡AB¡¡Áиоõ¾Í¿É¡¡

ÒÔÁË¡£¿ÉÊÇÒ²·³°¡£¬Ë­ÓиüºÃµÄ°ì·¨£¿¡¡¡¡

´ð£º1¡¢Sub¡¡feifjeifjeifjeifjeifjiefjiejfiejf£¨£©¡¡¡¡

For¡¡i¡¡=¡¡2¡¡To¡¡ActiveSheet¡£Range£¨¡¨a1¡¨£©¡£CurrentRegion¡£Rows¡£Count¡¡¡¡

If¡¡IsEmpty£¨Cells£¨i£»¡¡1£©£©¡¡Then¡¡¡¡

Cells£¨i£»¡¡1£©¡£FormulaR1C1¡¡=¡¡Cells£¨i¡¡1£»¡¡1£©¡£Value¡¡¡¡

End¡¡If¡¡¡¡¡¡

Next¡¡¡¡

End¡¡Sub¡¡¡¡

2¡¢É¸Ñ¡³ö¿Õ°×ÐУ¬ÊäÈ빫ʽ=INDIRECT£¨¡¨a¡¨&ROW£¨£©¡­1£©£¬Ìî³ä¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡Öƶ©ÏÂÔ¼ƻ®²¢ÏÔʾΪÖÐÎÄ¡¡¡¡



ÎÒÔÚÎåÔ·Ý×öÁùÔ·ݵļƻ®£¬Îª¼õÉÙ¹¤×÷Á¿ºÍ¸ü¸ÄµÄÂé·³£¬ÎÒ×öÄ£°å²¢Ê¹ÓÃÁ˹«Ê½=¡¨¼Æ»®ÆÚ£º¡¡

¡¨&YEAR£¨NOW£¨£©£©&¡¨Äꡨ&£¨MONTH£¨NOW£¨£©£©£«1£©&¡¨Ô¡¨£¬½á¹ûÈçA1¡¡Ëùʾ¡¡¼Æ»®ÆÚ£º2002¡¡Äê¡¡6¡¡Ô¡¡¡¡£¨ÏÖÔÚ¡¡

µÄϵͳÈÕÆÚÊÇ¡¡2002¡¡Äê¡¡5¡¡Ô£©¡£¡¡Èç¹ûÎÒÏë×Ô¶¯µÃµ½Èç¡¡A2¡¡ÖеĽá¹û¡¡¼Æ»®ÆÚ£º¶þ¡ð¡ðÄêÁùÔ¡¡£¬Çë¡¡

ÎÊÒªÈçºÎ×ö²ÅÐУ¬ÎÒÉèÖÃÁ˵¥Ôª¸ñµÄÈÕÆÚ¸ñʽ»¹ÊDz»ÐС£¡¡¡¡

½â´ð£º¡¡1¡¢ÏÈÉèÖõ¥Ôª¸ñ¸ñʽΪ¡¨¶þ¡ð¡ð¶þÄêÁùÔ¡¡¡¨ÄÇÖÖÀàÐÍ£¬È»ºóÓÃÈçϹ«Ê½£º¡¡

=DATE£¨YEAR£¨NOW£¨£©£©£»£¨MONTH£¨NOW¡¡£¨£©£©£«1£©£»20£©¾Í¿ÉÒÔÁË¡£¡¡¡¡

2¡¢Ê¹ÓÃÕâ¸öº¯Êý°É£¡¡¡=EDATE£¨NOW£¨£©£»1£©¡£µ¥Ôª¸ñ¸ñʽӦÉèÖÃΪ£ºÈÕÆÚ¡­¡­¡­¡­Ò»¾Å¾ÅÆßÄêÈýÔ¡£¡¡¡¡

3¡¢ÉèÖõ¥Ôª¸ñ¸ñʽΪ£º£§DBNum1£§¡¨¼Æ»®ÆÚ£º¡¨yyyy¡¨Äꡨm¡¨Ô¡¨¡¡£¬È»ºóÖ±½ÓÊäÈëÈÕÆÚÖµ£¨Èç¡¡2002/11£©¡¡

¼´¿É¡£¡¡¡¡

ÊäÈ빫ʽҲ¿ÉÒÔ¡£Èç=today£¨£©£«30£¬¿ÉÒԵõ½Ï¸öÔµÄÔ·ݡ£¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡353¡¡¡¡


¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­Page¡¡354¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­

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



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡&µÄÓ÷¨¡¡¡¡



ÓС¡E44¡¡µ¥Ôª¸ñ£¬ÎÒÏ£Íû¡¡×ܼƣº=SUM£¨E45£ºE49£©¡¡¾ÍÊÇÏëÈÃËü¾­¹ý×Ô¶¯ÇóºÍºóÔÚÒ»¸öµ¥Ôª¸ñÄÚÏÔ¡¡

ʾ¡¡×ܼƣº120¡¡¡£¡¡¡¡

½â´ð£ºÓжàÖÖ·½·¨ÊµÏÖ£¬ÏêϸÈçÏ£º¡¡¡¡

1¡¢=¡¨×ܼƣº¡¨&sum£¨e45£ºe49£©¡¡¡¡

2¡¢°Ñ¡¡E44¡¡¸ñʽÉèΪ¡¨×ܼƣº¡¨#0¡£00£»¡¨×ܼƣº¡¨¡­#0¡£00£»¡¨×ܼƣº¡¨0¡£00£»@¡¡¡¡

3¡¢½«¡¡E44¡¡µ¥Ôª¸ñ¸ñʽ×Ô¶¨ÒåΪ¡¡¡¨×ܼƣº¡¨0¡£000¡¡¼´¿É£»·½±ã¶Ô¡¡E44¡¡µÄÒýÓüÆËã¡¡¡¡

4¡¢=CONCATENATE£¨¡¨ºÏ¼Æ£º¡¨£»SUM£¨e45£ºe49£©£©¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡ÓС¡5¡¡ÐÐÊý¾ÝÔÚÿÐÐÉÏÃæ¸ö²åÈë¡¡1¡¡ÐС¡¡¡



¡¡¡¡¡¡¡¡½â´ð£º1¡¢ÔÚ×î×ó±ß²åÈëÒ»ÁУ¬È»ºóÊäÈë¡¡1¡¢2¡¢3¡¢4¡¢5¡¢1¡¢2¡¢3¡¢4¡¢5£¬²¢ÒÔ´ËÁнøÐС¡

ÅÅÐò£¬ÔÚµÚÒ»ÐÐÉÏÃæÔÙ²åÈëÒ»ÐУ¬É¾³ý¸Õ¸Õ²åÈëµÄÁС£¡¡¡¡

¡¡¡¡¡¡¡¡2¡¢Ê¹Óá¡Ctrl£«Êó±êÒ»ÐÐÒ»ÐÐÑ¡¶¨£¬È»ºó²åÈëÐС£¡¡¡¡

¡¡¡¡¡¡¡¡3¡¢sub¡¡²åÈëÐС¡¡¡

for¡¡i=1¡¡to¡¡6¡¡¡¡

if¡¡cells£¨i£»1£©¡£value¡¡¡¡Cells£¨i¡¡£«¡¡1£»¡¡1£©¡¡And¡¡Cells£¨i£»¡¡1£©¡¡¡¡¡¨¡¨£©¡¡Then¡¡¡¡

Rows£¨i¡¡£«¡¡1£©¡£Insert¡¡¡¡

end¡¡if¡¡¡¡

next¡¡i¡¡¡¡

end¡¡sub¡¡¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¿ÉÒÔ¼ì²éÒ»ÕűíÀïÊÇ·ñÓЩÖصÄÊý×ÖÂð¡¡¡¡



´ð£ºÂ©Öµ£º¡¡£û=IF£¨SUM£¨£¨R1C1£ºR10C4=¡¨¡¨£©*1£©¡·0£»¡¨ÓЩֵ¡¨£»¡¨ÎÞ©ֵ¡¨£©£ý¡¡¡¡

Öظ´Öµ£º¡¡£û=SUM£¨SUM£¨£¨Â©Öµ£¡R1C1£ºR10C4=©ֵ£¡RC£©*1£©£©£ý¡¡¡¡

£û=IF£¨MAX£¨R1C1£ºR10C4£©¡·1£»¡¨ÓÐÖظ´Öµ¡¨£»¡¨ÎÞÖظ´Öµ¡¨£©£ý¡¡¡¡

ʹÓÃÏÂÃ湫ʽ¸ü·½±ã£º¡¡¡¡

ÕÒÖظ´Öµ¡­¡­¡­¡­¡­¡­£û=IF£¨SUM£¨£¨COUNTIF£¨R1C1£ºR10C4£»R1C1£ºR10C4£©¡·1£©*1£©¡·1£»¡¨ÓÐÖظ´Öµ¡¨£»¡¨ÎÞÖØ¡¡

¸´Öµ¡¨£©£ý¡¡¡¡

ÕÒ¡¡Â©¡¡Öµ¡­¡­¡­¡­¡­¡­£û=IF£¨SUM£¨£¨R1C1£ºR10C4=¡¨¡¨£©*1£©¡·0£»¡¨ÓЩֵ¡¨£»¡¨ÎÞ©ֵ¡¨£©£ý¡¡¡¡

×¢Ò⣺ÕâÁ½¸ö¹«Ê½¾ùΪÊý×飬ÊäÈëʱӦͬʱ°´¡¡Ctrl£«Shift£«Enter¡£¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡ÔõÑù½«µ¥Ôª¸ñÖеĹ«Ê½×ª»»ÎªÊýÖµ¡¡¡¡



½â´ð£ºÑ¡Öй«Ê½µÄÒ»²¿·Ö£¬°´¡¡F9¡¡¼ü¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡Ìõ¼þÇóºÍ¡¡¡¡



ÓÐÕâÑùÒ»¸ö±í¸ñ¡¡¡¡

A¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡B¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡C¡¡¡¡¡¡¡¡

1¡¡2¡¡3¡¡/2¡¡2¡¡3¡¡/3¡¡2¡¡3¡¡/4¡¡2¡¡3¡¡/5¡¡2¡¡3/6¡¡2¡¡3¡¡/ÈçºÎ²ÅÄÜÇó³öÂú×ã¡¡A¡¡ÁÐÖдóÓÚ¡¡2¡¡ÇÒСÓÚ¡¡5¡¡µÄB¡¡Áк͡¡C¡¡ÁÐÊý¡¡

ÖµµÄºÍ£¬ÒªÇó¡¡B¡¡Áк͡¡C¡¡ÁеÄÖµÏà¼Ó¡£Óá¡sumif¡¡º¯ÊýËƺõÌõ¼þÖÐÖ»ÄÜÉ趨Ϊ£¾2¡¡£¬¶ø²»ÄÜͬʱÉè¡¡

¶¨£¼5£¬¶øÇÒÔÚÇóºÍʱֻÄÜ¡¡B¡¡ÁÐÏà¼Ó£¬²»ÄÜ°Ñ¡¡B¡¡Áк͡¡C¡¡ÁÐÂú×ãÌõ¼þµÄÖµ¼ÓÆðÀ´¡£Õâ¸öÎÊÌâÄÜ¡¡

²»ÄÜÖ»Óú¯Êý£¬²»ÓÃÊý×鹫ʽ½â¾ö¡£Çë¸÷λָ½Ì¡£¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡354¡¡¡¡


¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­Page¡¡355¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­

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



½â´ð£º1¡¡Óù«Ê½£º=SUM£¨IF£¨£¨A1£ºA6¡·2£©*£¨A1£ºA62£»IF£¨A2£ºA72£»IF£¨A2£ºA¡¡

73000£»¡¡Q7¡·0¡£5£©£»¡¡¡¨¡¨£»¡¡P7£©¡¡¡¡



Òâ˼ÊÇ£ºÈç¹û¡¡R7¡¡µ¥Ôª¸ñÖеÄÊýÖµ´óÓÚ¡¡3000£¬²¢ÇÒ¡¡Q7¡¡µ¥Ôª¸ñÖеÄÊýÖµ´óÓÚ¡¡0¡£5£¬ÔòÔÚK7¡¡µ¥Ôª¡¡

¸ñÖÐÏÔʾ¿Õ°×£¬·ñÔòÏÔʾ³ö¡¡P7¡¡µ¥Ôª¸ñÖеÄÊý¾Ý¡£¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡Í³¼ÆÊý¾ÝÎÊÌâÒ»Àý¡¡¡¡



¸÷λÅóÓÑ£¬Èç¹ûÎÒÏëͳ¼Æ¡¡50¡¡¸öÊý¾ÝÖдóÓÚij¸öÖµµÄÊý¾Ý¸öÊý£¬£¨Õâ¸öÖµÊÇÔÚʹÓÃʱ²ÅÊäÈëij¸ö¡¡

µ¥Ôª¸ñµÄ£©£¬ÇëÎÊÓÃʲôº¯Êý£¬ÈçºÎʵÏÖ£¬Ð»Ð»¡£¡¡¡¡¡¡¡¡

ÈçÊý¾Ýµ¥Ôª¸ñΪ¡¡A1¡¡£ºE10¡¡£¬ÖµµÄµ¥Ôª¸ñΪA11¡¡¡£¡¡¡¡

´ð£º1¡¢Ê¹ÓÃÏÂÃæµÄÊý×鹫ʽ£º¡¡¡¡¡¡£û=SUM£¨IF£¨A1£ºE10¡·A11£»1£©£©£ý¡¡¡¡

2¡¡¡¢ÊäÈëÒÔϺ¯Êý£º¡¡¡¡=COUNTIF£¨A1¡¡£ºE10£»¡¨¡·¡¨&A11£©¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¹ØÓÚÌõ¼þÇóºÍÎÊÌ⣡¡¡¡¡



ÓС¡A¡¡£¬B¡¡£¬C£¬ÈýÁÐÊý¾Ý£¬Èç¹ûA¡¡ÁзûºÏÒªÇó£¬Çó¡¡B1*C1£«B2*C2£«¡£¡£¡£¡£¡£¡££¿¡¡¡¡

´ð£ºÊ¹ÓÃÊý×鹫ʽ£º¡¡¡¡¡¡£û=SUM£¨£¨R2C1£ºR13C1=¡¨ab¡¨£©*£¨R2C2£ºR13C2£©*£¨R2C3£ºR13C3£©£©£ý¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡Çë½Ì¹ØÓÚÌõ¼þ¹Ô»ýµÄÇóºÍÎÊÌâ¡¡¡¡



A¡¡ÁÐΪ²¿ÃÅÃû³Æ£¬B¡¡ÁÐΪÐÕÃû£¬C¡¡ÁÐΪÈÕ¹¤×ʶÈç¡¡20¡£00£©¡¡£¬D¡¡ÁÐΪÔ³öÇÚÌìÊý£¬ÎÒÏëÔÚÁíÒ»»ã¡¡

×ܱíÖлã×ܳö¸÷²¿ÃÅÔ±¹¤Ô¹¤×Ê×ܶ¼´£ºÏàÓ¦²¿ÃŶÔÓ¦µÄ¡¡C*D¡¡Ö®ºÍ£©¡¡¡£ÇëÎÊÈçºÎ½â¾ö£¿¡¡¡¡

½â´ð£º1¡¢=SUM£¨£¨A4£ºA10=¡¨¼×²¿ÃÅ¡¨£©*£¨C4£ºC13£©*£¨D4£ºD13£©£©¡¡¡¡

¼ÙÉèÄãµÄ¡¡a¡¡Áдæ·Å²¿ÃÅÃû³Æ£¬ÄãµÄ¡¡b¡¡Áдæ·ÅÔ