缓解工作压力有什么办法?Excel会计应学35招秘技

 huanjiegongzuoyaliyoushenmebanfa?weigongzuobujiashangdeqingkuang，shichenzaiquanbugongzuobuxiabiande，nengfouzhichenzaibiaotouxiabianni?huanjieyalidefangfashishenme?

 1.shixing“wenjiangeshi→gongzuobu→qingkuang”zhiling，kaiqi“gongzuobuqingkuang”tishikuang，xuandingbixuzuoweiqingkuangdezhaopianhou，anzhu“chajin”anjian，jiangzhaopianchenyuquanbugongzuobuxiabian。

 2.zaianzhectrljiandelingwai，yongdiannaoshubiaozaibuyongchenzhaopiandedanyuange(diqu)zhongtuotuolala，lingwaixuandingzheizhongdanyuange(diqu)。

 3.an“wenjiangeshi”caidanlanshangde“tianchongyanse”youbiandewangxialaanjian，zaijiezhechuxiande“diaosepan”zhong，xuanding“rubaise”。lijingneiyangdeshedingzhihou，liuyoudedanyuangexiabianchendaolezhaopian，ersuoshuxuandingdedanyuange(diqu)xiabianjiumeiyouchenzhaopianle(shijishang，shizhaopianbei“rubaise”zhezhule)。

tixingchenzaidanyuangexiamiandezhaopianshibujianrongfuyinde。

用连字符“&”来合拼文字

 ruguowomenxiangjiangliangliedeneironghepindaoyiliezhong，buyongyunyonghanshu，yigexiaoxiaolianzifu“&”jiunengjiangtanaxia(zheilijiashejiangb、c、dliehepindaoyiliezhong)。

 1.zaidliehoumiancharu2gekonglie(e、flie)，suihouzaid1danyuangezhongjianrugongshijisuan：=b1&c1&d1。

 2.zaiduxuandingd1danyuange，yong“tianchongbing”jiangsuoshugongshijisuankaobeidaodliexiabiandedanyuangezhong，b、c、dliedeneirongjibeihepindaoeliexiangpipeidedanyuangezhong。

 3.xuandingelie，shixing“kaobei”shijicaozuo，suihouxuandingflie，shixing“bianxie→xuanzexingzhantie”zhiling，kaiqi“xuanzexingzhantie”tishikuang，xuandingzaiqizhongde“biaozhi”xuanzexiang，anzhu“mingque”anjian，eliedeneirong(bingbushigongshijisuan)jibeikaobeidaoflieshang。

4.jiangb、c、d、elieshandiao，jinxinghepingongzuozhong。

 tixing：jinxingdiyi、2budeshijicaozuo，hepinshijixiaoguozaoyiwancheng，danzheishijiarushandiaob、c、dlie，gongshijisuanhuichuxianbuzhengque。guxukaizhandisanbushijicaozuo，jianggongshijisuanbianhuanweibuhuigaibiande“zhi”。

迅速复印学生成绩条

 jingchangchuxianpenyouwen“zenmedayinkaoshichengjitiao”neiyangdenanti，youxuduo rencaiyongluzhihonghuovbadefangshilaiwancheng，zheizhenduixinshoueryanyouyidingnanduxishu。chuziyucileikaolvdao，wozaizheidechuyizhongyonghanshuwanchengdejianyifangshi。

 zheilijiashexueshengchengjichucunzaisheet1gongzuobudea1zhig64danyuangediquzhong，zaiqizhongdiyigerenxingweitimu，diergerenxingweikechengmingzi。

 1.zhuanhuandaosheet2gongzuobuzhong，xuandinga1danyuange，jianrugongshijisuan：=if(mod(row()，3)=0，″″，if(0modrow()，3(=1，sheet1!a$2，index(sheet1!$a：$g，int(((row()+4)/3)+1)，column())))。

 2.zaiduxuandinga1danyuange，yong“tianchongbing”jiangsuoshugongshijisuankaobeidaob1zhig1danyuangezhong;suihou，zailingwaixuandinga1zhig1danyuangediqu，yong“tianchongbing”jiangsuoshugongshijisuankaobeidaoa2zhig185danyuangezhong。

daoci，kaoshichengjitiaojichuchengxing，xiabianjianyizhuangshiyixia。

 3.diaojiehaoxinggaoheliekuanhou，lingwaixuandinga1zhig2danyuangediqu(diyiweixueyuandekaoshichengjitiaodiqu)，an“wenjiangeshi”caidanlan“waikuang”youbiandewangxialaanjian，zaijiezhechuxiandewaikuangmuluzhong，xuanding“quanbubianxian”xuanzexiang，weixuandingdediqujiashangwaikuang(jiarubuyongwaikuang，keyibukaizhancibujixiabiandeshijicaozuo)。

 4.lingwaixuandinga1zhig3danyuangediqu，dianyixia“changjian”caidanlanshangde“geshishua”anjian，suihouanzheshubiaozuojian，zia4tuotuolalazhig186danyuangediqu，weiquanbudekaoshichengjitiaojiashangwaikuang。

an“fuyin”anjian，jiukeyijiangkaoshichengjitiaofuyinchuqu。

Excel帮你选涵数

 zaiyonghanshujiejueshujuxinxishi，jingchangbuqingchuyingyongnaxiehanshujiaoweishihe。excelde“jiansuohanshu”zuoyongnenggoubangnibianxiaofanchou，shaichushihedehanshu。

 shixing“chajin→hanshu”zhiling，kaiqi“chajinhanshu”tishikuang，zai“jiansuohanshu”xiabiandekuangjiazhongjianruguiding(ru“jishu”)，suihoudianji“zhuandao”anjian，xitongruanjianlikejiangyu“jishu”xiangguandehanshuxuanzechuqu，bingxianshixinxizai“xuanzehanshu”xiabiandeliebiaokuangzhong。zaironghechaxunyouguandebangzhuwenjian，jiukeyixunsumingquesuobixudehanshu。

另外查询不一样工作簿中好几个单元格内的数据信息

 youshihou，dajiabianxiemouyigongzuobu(sheet1)shi，bixuchaxunqitagongzuobuzhong(sheet2、sheet3……)mouyidanyuangedeneirong，nenggouyunyongexcelde“jiankongduihuakuang”zuoyonglaiwancheng。

 shixing“zhushitu→caidanlan→jiankongduihuakuang”zhiling，kaiqi“jiankongduihuakuang”，dianjizaiqizhongde“jiashangjiankong”anjian，jinxing“jiashangjiankongdian”tishikuang，yongdiannaoshubiaoxuandingbixuchaxundedanyuangehou，zaidianji“jiashang”anjian。fanfushangshuqingkuangshijicaozuo，jiashangqita“jiankongdian”。

 zhihou，buguanzainagegongzuobuzhong，yaoshikaiqi“jiankongduihuakuang”，jiukeyichaxunquanbubeijiankongdiandanyuangeneideshujuxinxihejibenxinxi。

为单元格迅速画边框

 zaiexcel2002zhiqiandebanbenhaozhong，weidanyuangediqujiashangwaikuangdeshijicaozuojiaoweibubian，excel2002duiyucishizuoyongkaizhanlequanxinshengjidekuozhan。

 dianji“wenjiangeshi”caidanlanshang“waikuang”youbiandewangxialaanjian，zaijiezhedanchulaidexialakuangzhong，xuan“zhituwaikuang”xuanzexiang，huoshishixing“zhushitu→caidanlan→waikuang”zhiling，jinxing“waikuang”caidanlan。

 dianjicaidanlanzuizuobiandewangxialaanjian，xuandingyizhongwaikuangkuanshi，suihouzaibixujiashangwaikuangdedanyuangediquzhongtuotuolala，jiukeyiweixiangduidedanyuangediquxunsuhuashangwaikuang。

 tixing：①jiaruhuabuduiwaikuang，meishier，xuandingcaidanlanshangde“cadiaowaikuang”anjian，suihouzaibuzhengquedewaikuangshangtuotuolalayixia，jiukeyixiaochudiaobuzhengquedewaikuang。②jiarubixuhuizhibuyiyangsediaodewaikuang，nenggouxianancaidanlanyoubiande“xiankuangsediao”anjian，zaijiezhedanchulaidediaosepanzhongxuandingbixudesediaohou，zaihuabiankuangjiukeyi。③zheiyizuoyonghainenggouzaidanyuangezhonghuashangdingjiaodexiegang。

操纵特殊单元格键入文字的长短

 nikeyixiangxiangruguonizaigaijianrusiweishudedanyuangezhongquetianxieleyigeliangweishu，huoshizaigaishuruwenzidedanyuangezhongniquejianruleshujudeqingkuangxia，exceljiunengquanzidongfenbian、jishipouxibingdanchulaijingshi，neigaikehaole!yaowanchengzheiyizuoyong，duiexceleryan，yebingbuhuitainan。

 birudajiajiangshubiaoguangbiaojingzhundingweidaoyigebeian“niandai”dedanyuangezhong，yibianjianrudetongyihecesuandebianjie，dajiaqidai“niandai”douyongyigesiweishulaibiaoming。yinci ，womenkeyidianji“shujuxinxi”laidande“shixiaoxing”xuanzexiang。zai“sheding”kapai“shixiaoxingbiaozhun”de“rongxu”xialaliebiaozhongtiaoxuan“wenzizhangduan”。suihouzai“shujuxinxi”xialaliebiaozhongtiaoxuan“xiangdangyu”，qie“zhangduan”wei“4”。lingwai，dajiazaigandao“cuowujingshi”kapaizhong，jiang“jianrushixiaoshujuxinxishixianshixinxidecuowujingshi”shewei“zhongzhi”，bingzai“timu”he“cuowubaogao”lanzhonggezitianxie“jianruwenzibufa!”he“shuruninsiweishuniandai。”ziyan。

 henxianeryijian，dangjiaruyouxierenzaigaidanyuangezhongjianrudebingbushiyigesiweishushi，excelbianhuidantigongdejingshitishikuang，duinishuocuowuyuangu，bingzhizhinijianruleqiadang“kuanshi”debiaozhihoucaikezaicirulu。qimiaoba?shijishang，zaiexcelde“shujuyouxiaoxing”fenbianzhong，yeyouhenduodutezhongleideshujuleixingkexuan，liru“wenzizhonglei”a，“bianmaxuliechicun”a，“shijianjinyuan”a，rujinixingquaihao，weihebushanzuozhuzhang，zishenshejifanganyizhongceshibiaozhun，jinideexcelzhanxianchubutongxunchangdefengcaini。

组合添充多个报表的固定单元格

 womenzhidaomeiyicikaiqiexcel，shoujiruanjianyizhimorenshezhikaiqiduogegongzuobu。congerjiukekandechuexcelchukaiyouzheqiangjingdeyizhangbaobiaodejiejuegongzuonengli，gengheshizaiduogehuxiangguanxidebaobiaozhongxianghupeihe。yaorongqiaguanxi，ziranzuixianjiubixutongbujianru。yiner，zaixuduozhuangkuangxia，doushibixulingwaizaiduogebaobiaodetongyangdanyuangezhongjianruyiyangdeneirong。

 neimazenyangduibaobiaokaizhanzuhebianxieni?zuixiandajiadianjidiyigegongzuobudebiaoshiming“sheet1”，suihouanzheshiftjian，dianjizuizhongyizhangbaobiaodebiaoshiming“sheet3”(ruguowomenxiangguanxidebaobiaobuzaiyiqi，nenggouanzhectrljiankaizhanxuanzhong)。zheishi，dajiajiandaoexceldebiaotilanshangdemingzichuxianle“xiezuozu”ziyan，dajiajiukeyikaizhanduixiezuozudebianxiegongzuozhongle。zaibixuyicijianruduogebaobiaoneirongdedanyuangezhongsuiyixiedianshenme，dajiafajue，“xiezuozu”zhongquanbubaobiaodetongyibuweidouxianshixinxichuxiangduineirongle。

 keshi，zhishitongbujianrushihaibugoude。liru，dajiabixujiangduogebaobiaozhongtongyangbuweideshujuxinxitongyigenggaiwenjiangeshiyinggaizenmebanni?zuixian，dajiadegenggaidiyizhangbaobiaodeshujuleixing，zaidianji“bianxie”laidande“tianchong”xuanzexiang，suihouzaitadeerzicaidanzhongxuanze“zhiyigezugongzuobu”。zheishihou，excelhuidanchulai“tianchongzuhegongzuobu”detishikuang，zaizheierdajiatiaoxuan“wenjiangeshi”yixiang，dian“mingque”hou，yigezuzhongquanbubaobiaogaibuweideshujuleixingdougenggaile。

更改文字的英文大小写

 zaiexcelzhong，weibaobiaojiejueheshujuxinxijisuanchushizuiqiangjingshiyongdebingbushigongshijisuan，yebushishujukuchaxun，zhishihanshu。bubirenweiexcelzhongdehanshujinjinduiyushuju，shijishangyaoshishixiejinbaobiaozhongdeneirong，exceldoushiyouduitabianxiedeteshuhanshu。birugenggaiwenzideyingwendaxiaoxie。huanjieyalidefangfashishenme?

 zaiexcel2002zhong，zuishaochushilesanzhongxiangguanwenzidaxiaoxiezhuanhuandehanshu。tamenfenbieshi：“=upper(yuanshujuxinxige)”，jiangwenzisuoyoubianhuanweiyingwendaxie;“=lower(yuanshujuxinxige)”，jiangwenzisuoyouzhuanhuachengxiaoxiezimu;“=proper(yuanshujuxinxige)”，jiangwenzizhuanhuacheng“shidu”deyingwendaxiaoxie，rurangmeiyigeyingyudancideshouxieweiyingwendaxiedeng。biru，womenzaiyizhangbaobiaodea1danyuangezhongjianruxiaoxiezimude“excel”，suihouzaizongtimubiaodanyuangezhongjianru“=upper(a1)”，huichejianhouhuodedejieguokenengshi“excel”。yiyang，ruguowomenzaia3danyuangezhongjianru“mr.weiwei”，suihouwomenzaizongtimubiaodanyuangezhongjianru“=proper(a3)”，neimadajiahuodedejieguojiujiangshi“mr.weiwei”le。

获取字符串数组中的特殊标识符

 chukailijijianruwai，congyicunyoudedanyuangeneirongzhonghuoquteshubiaoshifujianru，kendingshiyizhongshengshiyoufangbiandefangshi，feichangshiduiyixiekuanshileitongdexinxineironggengshiruci，liruyuangongmingdan、hujisuozaididengxinxineirong。

 ruguowomenxiangxunsuconga4danyuangezhonghuoquchenghudehua，shiyingyong“=right(yuanshujuxinxige，huoqudezifushu)”hanshu，tabiaoming“conga4danyuangezuiyoubiandebiaoshifugangkaishihuoqulianggebiaoshifu”jianrudaocibuwei。ziran，jiaruniyaohuoqumingzidehua，zeyaoyingyong“=left(yuanshujuxinxige，huoqudezifushu)”hanshule。yeyouyizhongzhuangkuang，womenbucongzuoyouliangduangangkaishi，zhishilijicongshujuxinxizhengzhongjianhuoquhaoduogebiaoshifu。liruwomenyaoxiangconga5danyuangezhonghuoqu“wuhanshi”liangzishi，jiuzhixuyaozaizongtimubiaodanyuangezhongjianru“=mid(a5,4,2)”jiukeyile。yisishi：zaia5danyuangezhonghuoqudisizifuhoude2gebiaoshifu，yejiushidisihediwuliangzi。huanjiegongzuoyaliyoushenmebanfa?