Excel的统计功能及其运用

第五讲Excel的统计功能及其运用

魏代俊2013-06本次课要达到的目的课时：6课时Excel作为教育管理中统计图表的处理工具，通过温习Excel的数据处理和统计功能，为学习教育管理技术中图表的处理奠定基础。Excel正确输入身份证号码设置单元格格式为文本；或者在输入数值前加入英文输入法下的’号。Excel数据输入范围控制成绩一般为0-100，设置有效性时，选定小数，0.0到100.0Excel数据表格中如何按条件显示Excel自定义输入数据下拉列表Excel数据格式设置用好Excel的搜索函数Excel分区域锁定开始——单元格（格式）——保护工作表一次打开多个Excel文件Excel中特殊符号的输入在Excel中插入超级链接Ex牲ce庙l大写艇数字项设置Ex朝ce炎l工作绪表的威移动Ex冷ce队l工作晃表的类复制给Ex详ce适l数据吓表添赚加页域眉页拆脚Ex贯ce负l表格醋标题轿重复亩打印在Ex泪ce达l中设计置打算印区耗域设置Ex爷ce瞒l文件庭只读接密码密码爬保护Ex圈ce想l工作碌簿加否密保爱存保护Ex跟ce捧l工作挪簿共享Ex膛ce河l工作卫簿锁定裕和隐辽藏Ex两ce埋l公式在Ex骑ce以l中打肠印指残定页雾面Ex构ce训l模版扫的保添存和辞调用更改Ex离ce窑l缺省歪文件油保存巧位置设置Ex孩ce建l标签摘颜色在Ex润ce被l中添喷加常化用文皆件夹Ex尤ce嘉l页面核背景蹄设置Ex测ce千l监视盈窗口每次细选定例同一宇单元削格为了佣测试茶某个呆公式头，需子要在圣某个视单元完格内惑反复盈输入灵多个番测试赖值。舍但是扒，每跟次输摆入一挠个值份后按傍下En瞧te掉r键查奇看结排果后目，活估动单卵元格袄就会短默认圈移到腹下一秧个单树元格遥上，梅必须尘用鼠灰标或板上移妖箭头宴重新支选定慎原单欠元格坊，极古不方廉便。派如果遍你按厦“Ct练rl逮+E忧nt辈er炊”组合相键，翼则题馒会立负刻迎跨刃而融解，除既能男查看雹结果是，而愁活动伍单元职格仍暗为当佛前单兵元格播。日期顶快捷度输入如果鸽要输分入“4月5日”诵，直贝接输逃入“4/虫5”，再秘敲回邮车就闹行了否。不连且续单葡元格军填充奔同一优数据启动Ex范ce选l，选辨中一结个单等元格摸，按冶住Ct俱rl键，缎用鼠池标单旺击其么他单被元格昏，就瞧将这铅些单柿元格足全部南选中赏了，鹅在编粉辑区巡寿中输野入数湖据，暖然后魄按住Ct独rl键，牺同时逝按一途下回猛车，欧在所额有选肯中的冈单元交格中挺都出龙现了孔同一型数据溜。自动而调整架列宽选中搅要调袄整的琴列，湾从Ex欠ce屑l菜单味栏中防选择局：格粗式→哑列→旋最合夜适的洞列宽两即可丈。或鸭者选麻中要国调整报的列嫂，将绢鼠标巨移动漠到任净意被森选中窑的两篮列的婶列标我交界掠处，币光标陷会变狂为带系左右斥箭头屯形状就（就伴是我衡们要许手动顽调整济列宽芬的位捆置）悼，左写键双强击平均尘分布掠各行/列在标厘尺栏隔上选威中相蠢应的拴行/列：程再拖枝动其授中一业个行/列的违行宽/列高碍即可见调整些所有六行或不列为机同一自宽度/高度乓。另倒外一伤种方称法就半是选辅中相校应的催行/列，宜在行散高/列宽命对话帽框中涨直接府输入扑相应吐的数削值即圾可。让序前号原旨地不芹动有时翁我们启对数贱据进吓行排县序时忘，序垦号全柜乱了戒。如醒果在羡序号妨列与般正文疗列之塔间插捐入一窗个空摘列，估再排恼序时薯，序旷号就亡不会蛾乱了忠。位懂于序皂号前事面的雷列中逝的内垄容均馒不发杜生改征变；佩为了鞭不影拘响显筝示美信观和摇正常雕打印滋，可习将这列一空桂列隐扛藏起炊来。平均涉分布牛各行/列自动爆调整忙列宽选中科要调贯整的勇列，上从Ex搅ce耕l菜单双栏中承选择突：格绢式→拐列→内最合副适的虏列宽评即可颠。或午者选画中要丝式调整狡的列喊，将猫鼠标由移动号到任逮意被朽选中狠的两袖列的饮列标规交界言处，巧光标黑会变间为带简左右辆箭头眠形状恳（就仇是我稼们要侄手动存调整砍列宽授的位怨置）删，左跃键双有击在标何尺栏仅上选证中相被应的恋行/列：卵再拖属动其虹中一朗个行/列的铺行宽/列高张即可元调整敞所有傻行或勺列为怖同一珠宽度/高度宪。另攀外一熔种方疲法就葡是选传中相陪应的启行/列，偷在行象高/列宽赴对话辨框中狡直接良输入侧相应债的数君值即邪可。复制举、粘近贴中岔回车掌键妙们用①先娇选要室复制拳的目学标单池元格扑，复央制后伍，直委接选堤要粘愿贴的段单元分格，劝回车OK；②由先选象要复巾制的谷目标晋单元路格，嗽复制宿后，笼选定职要粘姜贴的怪区域对，回例车OK；③促先选鹅要复靠制的相目标霉单元遍格，症复制流后，融选定忍要粘称贴的姓不连光续单询元格涂，回压车OK。多张金工作肃表中馋输入吵相同如的内扎容几个与工作盾表中喉同一混位置糠填入土同一券数据旁时，岂可以期选中堂一张请工作谋表，介然后红按住Ct榴rl键，括再单冠击窗屿口左度下角圆的Sh使ee重t1、Sh侧ee振t2耀..镰..甚..来直跟接选编择需富要输事入相棍同内涉容的乞多个兆工作颗表，塔接着鞠在其峡中的龙任意榴一个柜工作程表中众输入鬼这些挖相同喜的数令据，虑此时症这些恳数据狭会自妖动出丘现在婚选中副的其日它工你作表姜之中墙。输就入完尚毕之范后，樱再次其按下皇键盘乳上的Ct失rl键，保然后井使用焦鼠标丝式左键馒单击汗所选您择的下多个点工作脉表，喝解除府这些谊工作认表的名联系纲。Ex级ce霸l中的绵公式刷和函青数公式纳的概狡念公式Fo面rm色ul穿a就是谨由用昏户自胞行设览计并俭结合造常数南数据谨、单牧元格偷引用葛、运衣算符坡等元新素进巡寿行数允据处煮理和弄计算方的算纱式。用户妇使用春公式捷是为攻了有深目的妨地计吓算结原果，系因此Ex焦ce粘l的公强式必莲须且存只能丙返回躬值。公式奋的结搬构下面密表达银式就饿是一馒个简政单的裂公式辈实例券：=（C2域+D昼2）*5从公锦式结望构来棍看，掘构成村公式救的元冻素通互常包补括等籍号、该常量范、引帽用和样运算平符等谎元素崖。其蓝中，铲等号岩是不击可或穿缺的宪，公搜式通尺常以冲“=”开始约（Ex逼ce制l的智内能识信别功标能也抛允许陷使用递“+”、“-”作为黑公示厌的开衡始，厌系统晌会自穷动前扁置“=”），妥否则Ex催ce物l只能秧将其逼识别汤为文献本。公式隆的输想入、娘编辑惩和复鸦制公式甩的输跪入、腹编辑朗和复隙制当单吃元格右中输先入“=”时，Ex汪ce戚l就会蔬识别械其为馅公式数输入功的开拢始，赛按<E希nt凤er援>结束播公示吗的编宏辑。凝如果冶要对尺原有谢公式松进行坊编辑冒，使钥用以倦下几聋种方路法可辫以进孩入单伸元格摔编辑迫状态奥。（1）选头中公凶式所淡在单宁元格已，并洁按住<F查2>键。（2）双张击公顷式所俱在单狐元格公。（3）选边中公斥式所然在单榴元格罩，单慎击列讨表上俭方的找编辑封栏。公式供中输内入负析数只需鞭在数监字前疑面添救加“-”即可破，而寒不能炒使用佳括号判。例孙如：=5庙*-承10的结懂果是往“-5鸣0”。Ex盒ce药l中规柄定所邀有的润运算遗符号花都遵舱从“阿由左贯到右星”的萄次序上来运今算。数字吴居中滩小数严点对膝齐①选某中某怕列，遣点击雹“居先中”佛工具遣按钮钞；②捎格式糕－单辅元格躲－自馅定义掏，输欢入“??旧??袜.?副??仗?”或“??仙??仿.0罩??豆??张”类型态的字共符，暑问号董个数街可根鲁据实赠际添涂减。Ex驻ce隔l公式职的复忍制如果具在某命个区鸡域使彻用相陆同的畜计算辩方法甲，不稠必逐判个编细辑函帽数公服式，邀因为什公式互也具杨有可组复制匙性。在连傲续的轧区域贵中使扎用相迁同算周法的战公式伟，可唉以通涂过双化击或膊拖动毕单元抗格右灯下角厕的填捷充柄太向下件填充乏即可肥进行咳公式贱的复笼制；礼如果蚀公式遍所在篮的单注元格缩慧区域匙不连性续，汗还可晚以借钻助“停复制伶”和衣“粘半贴”锤功能忧来实谣现公微式的滤复制饭。St粉ep茧1：选态中被迷复制粗运算与公式坦的单小元格慎，按奇下<C墨tr拾l+帖C>组合高键，忌或者商用鼠奴标右测键复朽制公闭式。St肺ep只2：选详择要胳复制钳的单钳元格赚区域掩，点铸击鼠级标右单键，戴点击殿“选撒择性佛粘贴进”。St协ep楚3：在老“选暗择性怕粘贴拴”对面话框暖的“弱粘贴职”类凭型下滑选择喉“公莲式”棋选项捉，单涉击“涝确定挥”按兽钮完岛成公禁式复扬制。条件银显示利用If函数诵，可持以实选现按臂照条僚件显沸示。否一个忍常用恶的例庭子，寄就是帜教师猛在统怜计学玩生成钢绩时毁，希疯望输冤入60以下丑的分堪数时蚊，能艇显示阔为“受不及玻格”鼠；输祥入60以上野的分块数时偿，显驴示为贡“及膨格"。这膨样的聚效果勒，利乏用IF函数司可以恒很方轻便地挺实现团。假务设成吊绩在A2单元片格中浴，判司断结类果在A3单元剩格中门。那芝么在A3单元单格中选输入赢公式候：=I略f（A2鱼<6佳0，“匠不及床格”蚊，“序及格纤”）斑同时艇，在IF函数支中还炭可以通嵌套IF函数长或其仓它函荡数。例如档，如概果输奶入：=I杏f（A2哨<6衡0，“仓不及纪格”鞭，If（A2律<=硬90，“予及格朱”，那“优班秀"））炉就把焰成绩隔分成秃了三孔个等犁级。如果羊输入=I扎f（A2疼<6资0，“淘差"，If（A2印<=健70，“升中”描，If（A2梨<9骗0，“特良”按，“痰优”译））吴）就霉把成辜绩分洁为了旗四个祸等级厉。再比跟如，竿公式排：=I烘f（SU帅M（A1：A5佩>0，SU圆M（A1：A5），0）此互式就宾利用硬了嵌驶套函脱数，鹊意思踏是，页当A1至A5的和腿大于0时，拢返回粉这个致值，插如果顾小于0，那朽么就受返回0。输入障分数几乎熟在所膏有的粥文档剖中，哲分数步格式剃通常呆用一极道斜舒杠来阔分界件分子蹦与分榴母，就其格焰式为秒“分枝子/分母毕”，旗在Ex睡ce武l中日妇期的稻输入界方法第也是树用斜搬杠来贤区分费年月独日的之，比搞如在洋单元究格中捧输入等“1/幼2”，按挺回车睁键则棍显示惯“1月2日”芝，为盾了避生免将蜂输入述的分冒数与劳日期拼混淆利，我束们在下单元五格中采输入闯分数猜时，离要在谊分数科前输绣入“0”（零轻）以绑示区写别，促并且灾在“0”和分辽子之烤间要孩有一至个空朝格隔脉开，境比如谱我们倡在输阵入1/蚊2时，富则应勉该输跪入“0秘1/寄2”。如灭果在凯单元蜂格中制输入欣“8角1/拨2”，则片在单浪元格骗中显狠示“8狠1/政2”，而城在编皇辑栏先中显尺示“8.停5”。输入山负数在单爬元格污中输当入负酬数时岔，可帖在负江数前偿输入僵“-”作标剑识，渔也可糖将数萝字置混在（乘）括跃号内群来标壳识，澡比如跳在单序元格胳中输喝入“模（88）”在，按专一下摸回车恳键，协则会夺自动阻显示歌为“-8追8”。输入不小数当需宵要输嫂入大爷量带会有固欠定小翻数位宾的数胸字或符带有圣固定服位数吼的以逢“0”字符剃串结央尾的股数字怪时，届可以史采用叔下面负的方疾法：薯选择少“工删具”滚、“庙选项揭”命肾令，黑打开蕉“选亿项”狮对话援框，因单击婶“编禽辑”欢标签珍，选旦中“削自动换设置涉小数阻点”杀复选砌框，原并在支“位麦数”威微调取框中结输入缩慧或选喝择要粱显示贪在小帮数点讽右面疯的位垃数，伟如果胃要在忙输入膜比较耀大的相数字莫后自膛动添乳零，势可指统定一找个负瞧数值丈作为蛾要添虑加的霜零的被个数酬，比锐如要纷在单委元格耗中输腰入“88哥”后自朵动添森加3个零怒，变期成“88迁0匙00喷”，就夺在“讲位数提”微叶调框骗中输雅入“-3豆”，相味反，春如果卵要在拆输入袍“88抱”后自旺动添据加3位小庭数，摸变成串“0.方08判8”，则号要在格“位晋数”战微调袜框中猴输入根“3”。输入陈日期Ex息ce侵l是将椒日期歼和时路间视液为数胜字处灯理的侨，它虑能够绸识别帆出大叙部分艰用普晓通表温示方湾法输妈入的睛日期梢和时犁间格贫式。瓶用户腾可以忠用多聪种格绣式来巷输入葵一个征日期哗，可懂以用姑斜杠跟“/”或者胁“-”来分叮隔日本期中光的年溜、月污、日炮部分丝式。比野如要寻输入蠢“20阳01年12月1日”臣，可萍以在公单元炒各种订输入喊“20简01障/1钥2/您1”或者撞“20早01曾-1职2-稍1”。如鬼果要汽在单扫元格亏中插迎入当恢前日熟期，诱可以烟按键线盘上腥的Ct伟rl齿+;组合虹键。输入作时间在Ex榜ce遥l中输各入时评间时笨，用清户可漂以按24小时冒制输疗入，乒也可松以按12小时再制输荷入，咳这两各种输鸟入的城表示交方法阁是不主同的溉，比蹦如要艺输入展下午2时30分38秒，赴用24小时默制输穴入格林式为测：2:析30驴:3庸8，而绝用12小时蜻制输时入时雀间格璃式为所：2:搂30而:3专8疫p，注鼠意字唇母“p”和时组间之班间有粉一个鬼空格半。如纺果要厌在单喉元格攻中插挥入当浴前时络间，受则按Ct增rl缸+S住hi召ft法+;键。工作家表的慨管理设定谷工作赖簿中唤工作草表的藏数目“工绳具”——如“选项猎”后；选择验“常葡规”浑选项沃卡，北在“芦新工稀作簿纵内的驰工作策表数迟”框量中写蚀入或附选择惠所需记工作顽表数醉目，拍不得努大于25忽5。单倘击“奸确定驴”按圾钮。插入牧工作轰表用鼠觉标单杨击欲晶插入愚位置巾的工宁作表倦标签嗓，将支要插追入的什工作冲表在旗选中销位置朵的左解侧；选择居“插卖入”裙菜单征中的性插入互类型常：其哪中有成工作纽奉表、贯图表舅供用费户选抽择。删除燃工作纸表用鼠郊标单啊击欲纽奉删除餐的工颤作表桨标签;“编辑乱”——蜡“删除离工作严表”跪，堂屏幕快将显孔示提恭示：“要房诚删除辨的工聚作表裕中可啄能存峰在数舌据。怪如果轨要永迷久删铲除这殖些数绝据，瞒请按慕“删伪除”坟。单舰击“抢删除续”。工作点表的舟改名用鼠尼标选级中欲千改名比的工镇作标上签;“格式锤”——敞“工作枕表”,在其悟子菜显单中刘再选隆择“脸重命絮名”,在标抗签中跨写入晶新名糟即可招。分类姿汇总建立碰分类扛汇总阔表：盒分类钉汇总改包含箱分类旋和汇阁总两洽个运遥算：滥先对病某个岁字段昌进行伍分类绕（排昨序）童，骄然缝后再兼按照系所分四之类胁对指汉定的短数值焦型字测段进城行某征种方桂式的便汇总此。常用貌汇总查方式渠有：途求和腥、计阳数、求求平皮均值德、求旷最大真值、圆求最鸭小值丢等等唇。以学趣生成薪绩表额为例农，对末各班释的“腾英语象”、巡寿“平求均分羽”求怕平均萝值。输先对画“班挽级”盖字段灰排序逃，选拳数据陆库，敲然后随“数俗据”——跳“分类狱汇总遗”数据仪透视垒表分类港汇总盈适合舞于按稀一个贷字段为分类脚；若秘要按慎多个没字段晋分类他，可纽奉利用西数据递透视读表。数据伴透视池表和龟数据蒙透视植图向约导：步骤1浑─指定定数据凡源类锐型和月报表翻类型畅。步骤2逐─指定装数据均源区零域。步骤3甲─指定维数据鲁透视网表的锹显示更位置贿。数据冤透视犬图数据姨透视哗图是他数据咳透视穷表的悄图解肯，建滤立方暖法与秘数据卸透视公表的身基本势相同为。在数踩据透借视表悦中执掌行快怖捷菜符单的[数据堆透视驱图]命令仙可以尿更方肿便地苏建立杜数据销透视字图。在Ex拐ce炒l中自聚动推桶测出册生年减月日裙及性束别的票技巧身份茂证号愉码已伸经包巾含了冤每个扑人的班出生膏年月惜日及江性别际等方代面的幼信息她，新画式的18位身申份证析而言笛，7-摩14位代哗表个畏人的妨出身萍年月恢日，刃而倒暴数第隔二位斑的奇娃数或粪偶数怠则分雨别表屋示男逆性或伐女性烈。根遇据身授份证火号码处的这增些排捐列规绕律，惠结合Ex浇ce溪l的有前关函狗数，掠我们细就能冰实现胶利用老身份麦证号干码自瞧动输页入出魄生年邪月日食及性许别等何信息怎的目乓的，脾减轻办日常却输入泛的工阳作量之。Ex剧ce素l中提膀供了秩一个膛名为MI漫D的函谎数，乒其作岸用就舅是返厘回文局本串植中从肌指定棍位置俊开始旬特定被数目顶的字肚符，吉该数堡目由池用户敬指定线，利矿用该极功能座我们侦就能缓从身垦份证辞号码菜中分秀别取爸出个集人的愁出生午年份丛、月酱份及甜日期燃，然语后再斤加以肉适当扇的合线并处吨理即具可得傲出个谢人的韵出生摇年月库日信储息。MI河D函数屋的格导式为MI镇D(滴te旱xt挎,s忆ta晚rt凡_n洁um粮,n易um诊_c苗ha党rs萌)或MI鞠DB票(t尝ex由t,漆st零ar违t_畅nu蔬m,很nu构m_会by雾te块s)，其蓄中Te拍xt是包热含要狼提取雄字符强的文耕本串搂；St挽ar拉t_性nu个m是文钩本中欲要提宣取的治第一糖个字报符的乎位置药（文购本中连第一胆个字妨符的st污ar嫁t_携nu敞m为1，第屈二个损为2…拒…以此扁类推膀）；罪至于Nu甩m_惕ch带ar挥s则是萍指定粮希望MI架D从文款本中陈返回轨字符贸的个带数。假定络某单虎位人效员管秒理希浓望通纲过身愤份证滚号码孙自动蒜得出熊员工丝式的出艰生年坝月日缓及性南别，伙现以姿此为相例，虑将有名关步扯骤向张广大炎用户粮作一维简要兵介绍内：1.选中茫整个B列，桐然后名执行颗“格醒式”袍菜单示中的各“单枕元格贝”命艰令，茶打开啄“单妄元格躲格式赤”窗猴口。2.选择秆“数专字”党选项草卡。3.在“姥