excel拓展运用（拓展运用excel）.doc

excel拓展运用（拓展运用excel）.docexcel拓展运用(拓展运用excel)Maybeyou'vedonehundredsoffinancialstatementsinExcel,andmaybeyou'plexoperationswithExcelfunctions,andyoumightthinkthatExcelisjustthat,evenwithoutnewideas・ButthecountlesshandytricksweuseonweekdaysarejustonepercentofalltheskillsofExceLThistopicstartswithsomelittle-,letdifferenttypesofdatadisplayindifferentcolorsInthepayroll,ifyouwantgreaterthanorequaltototalwages2000yuanto"red〃thatisgreaterthanorequaltototalwages1500yuanforthe〃blue〃showsthatlessthan1000yuanintotalwagesofz/Brown〃black〃toothershows,sowecanset・openthepayrollworkbook,selectthecolumnof"payroll",implementthe,zformattoconditionalformat,zcommand,andopenthe^conditionalformatzzdialogbox・Clickthedrop-downbuttonontherightsideofthesecondbox,selectthez,largerorequal/zoption,andenterthevalue〃2000〃inthefollowingbox〃・Clicktheformatbutton,openthecellFormatdialogbox,andchangethecolorofthefonttored〃・pressthe"add〃button,andfollowtheaboveoperationtosetupotherconditions(greaterthanorequalto1500),thefontissetto"blue",lessthan1000,thefontisset"Brown〃・aftersetting,pressthe〃0K〃,establishaclassificationdrop-downlistfillitemWeoftenenterthenameoftheenterpriseintothetable・Inordertomaintaintheconsistencyofthename,weusethedataavailabilityfunctiontobuildaclassificationdrop-downlistfillitem・inSheet2,ordingtocategory(suchas''industrialenterprise",commercialenterprise",''individualenterprise",etc・),andanenterprisenamedatabaseisestablished・selecttheAcolumn(''industrialenterprises"columnname),inthe〃name〃column,enterthe''industrialenterprises"character,press"enter"keytoconfirm・Imitatetheaboveoperation,B,C Theyarenamedbusinessenterpriseandindividualenterprise switchtoSheetl,selectthecolumnthatneedstoentertheenterpriseclass(olumn),mand,andopenthedatavaliditydialogbox・IntheSettingstab,click"allow"ontherightsideofthedrop-downbutton,selectthe"sequence"optioninthe"source"inthebox,enterthe''industrialenterprises",''co