MicrosoftExcelFortwodays

1、instructor:董瑞营mail:msn:dry123_公式与格式、函数工作表及单元格的安全设置数据的提取(数据筛选)数据分析(数据透视表)数据图表excel数据与ppt、word的引用公式的输入：单元格的引用：相对引用：a1 在行中复制时，行不动，列在动，在列中复制时，列不动，行在动.绝对引用：$a$1 f4不能动的要加$混合引用：$a1 , a$1,适合向两个方向上复制.分别进行讨论，只讨论默认在动的，不讨论默认不动的。名称引用：定位功能（f5），用作公式中的参数(f3),跨工作表操作。方法：选择要命名的区域，单击名称框输入名字：不能以数字命名不能以地址命名名字中间不能有空格名字写完后

2、要enter确认删除：insertnamedefinedelete函数=sum(a1,b5) =sum(a1:b5)=average() =averagea()=max()=min()=count()统计数字单元格的个数=counta()统计数字及文本的个数=countblank()统计空单元格的个数=if(logical_test,value_if_true,value_if_false)=round(number,num_digits)if嵌套（一个if最多可以嵌套7个if）共8个if有几段条件，用几段条件减一个if条件判断时从一端判断到另一端，不能从中间开始=and(条件1，条件2)=o

3、r(条件1，条件2)=not(条件)=sumif(range, criteria,data_range)range:条件所在的区域criteria:条件，表达式用” “=10” “=“&a1 “=“ & average(f:f)data_range：求和的区域。=countif(range,criteria)判断是否重复：datavalidationsettingscustomtoolsformula auditingshow formula auditingcircle invalid data=vlookup(lookup_value,table_array,col_ind

4、ex_num,range_lookup)lookup_value:查找的依据，它一定在数据源的第一列，且值唯一。table_array：数据源，命名数据源。col_index_num：返回被找信息所在的列数。range_lookup:是否精确匹配：true:-1相似匹配。被找依据在被找表中要升序排放返回小于或等于查找值的最接近值常用于数值范围的查找false:0 精确匹配。vlookup的嵌套=if($d4=,vlookup($d4,data,5,0)=trim():去除多余的空格。=vlookup(trim(a2),data,1,0)=iserror():判断单元格中是否存在错误true:单

5、元格中存在错误false:单元格中不在错误 =if(iserror(vlookup(trim($b22),产品!$b:$j,match(d$21,产品!$b$1:$j$1,0),0)=true,vlookup(trim($b22),产品!$b:$j,match(d$21,产品!$b$1:$j$1,0),0)=match(lookup_value,table_array,type)lookup_value:查找依据table_array:一般要绝对引用，只能为一行数据或一列数据type:1:返回小于或等于lookup_value的最接近的值，且table_array中一定要升序排放0:精确匹配：

6、 table_array可以没有顺序-1:返回大于或等于lookup_value的最接近的值，且table_array中一定要降序排放=vlookup($c4&$d4,订单与产品!$a:$k,match(e$3,订单与产品!$a$1:$k$1,0),0)=right(text,num)=left(text,num)=mid(text,start,num)=len():计算单元格中字符个数。=searchsearch(find_textfind_text,within_textwithin_text,start_num)= findfind(find_textfind_text,with

7、in_textwithin_text,start_num) =date(year,month,day)=year(),=month()=text(number,format)有效性 数值的唯一性：=countif($a$4:$a$18,a4)=1下拉列表的制作：内容来源于其他表格:选择要制作下拉列表的内容区域，命名选择要使用的区域，datavalidationallowlistsource,输入名字内容自定义：选择要使用的区域，datavalidationallowlistsource,输入内容，任意两内容之间英文半角的逗号隔开数值的效性：=sum($h$4:$h$25)=5000日期的有效性

8、：yyyy-mm-dd yy-mmm-d mm/dd/yyyy分列：datatext to columnfixed width=date(year,month,day)条件格式 自身值决定自已的格式。由公式来决定格式。and(条件1，条件2，条件3)or(条件1，条件2，条件3)自动套用格式 格式化数字 单元格安全设置单元格级的安全性：只给出允许操作的区域：选择允许别人操作的区域formatcellsprotection取消locked选择有公式的且不用修改的区域formatcellsprotection选中locked hiddentoolsprotectionprotect sheet输入

9、密码，取消select locked cells复制框光标定位到原始数据表，formatsheethidetoolsprotectionprotect workbookstructure加密。只保护指定的区域：选择所有单元格formatcellsprotection取消locked hidden选择要保护的单元格区域formatcellsprotection选中locked hidden单击toolsprotectionallow users to edit ranges单击”new”按钮，输入区域标题，设定区域的范围，输入区域password单击”protect sheet”按钮，输入pas

10、sword文件级的安全：toolsoptionssecuritypassword to open and password to modifymodule 3module 3：数据查询：数据查询 筛选：(按条件显示记录) 自动筛选：datafilterauto filter指定内容筛选:可以跨列操作取极端数据top 10：（只对数值性字段有效,不可以跨列操作）自定义：（最多可以定义两个条件，and or）*:表示多个字符？：只表示一个字符module 3module 3：数据查询：数据查询高级筛选：创建条件区域：将条件中用到的列的标题复制到原表格以外的某处在复制出的标题下方写条件。如果条件写在

11、同一行中为and如果条件写在同一列中为 ordatafilteradvanced filter去除重复记录：module 3module 3：数据查询：数据查询去除重复数据：光标定位到空的单元格中datafilteradvanced filteraction: copy to another locationlist range:选择要去除重复的数据criteria range:空copy to:指定一个空的单元格module 3module 3：数据查询：数据查询subtotal(founction_num,ref) 与筛选一起使用.1:average2:count3:counta4:max

12、5:min9.:sum数据库函数：=dsum(database,field,criteria)=daverage()=dmax()=dmin()=dcount()=dcounta()数据前提：列中数据为字段，每个字段只有一个字段名行中数据为记录表格中不能合并单元格表格中不能有空白列，可以有空白单元格。数据透视表（数据分析）制作透视有的方法给原始区域命名。按向导操作：透视表的布局：分析出需求中要求计算的字段（data）分析出需求中要求显示字段（除去计算的都是要显示）显示指定项page显示多项或所有项row如果row中有多项字段，可将一项放到column中形成交叉表page ,row,column

13、中可以为空，但不能放重复的内容.data可以重复的，但不能为空.向透视表中添加及删除字段：添加：回到向导中添加(右击pivottable wizard)删除：右击字段名选择hide透视表中字段的组合：日期型：yyyy-mm-dd mm/dd/yyyy自动组合被组合的字段中每个值均为日期型被组合的字段中不能有空单元格将要组合的字段放到行中。=weeknum(date)手动组合先将行中只保留要组合的字段,其他字放到页中选择要组合的内容，右击组合。再将页字段中的内容放回到应在位置数值型：自动组合被组合的字段中每个值均为数值型被组合的字段中不能有空单元格将要组合的字段放到行中。手动组合被组合的字段中每

14、个值均为数值型先将行中只保留要组合的字段选择要组合的内容，右击组合。向透视表中添加新的计算字段。向透视表中添加计算项透视表中不能有组合字段光标要定位在计算项的字段中进行计算。更改行中的汇总方式：双击行中除最后一个字段以外的字段名。如果双击内容，展开及折叠。如果要展开或折叠所有项：右击字段名group and show detailhide detail显示极端数据：双击行中需求中为主语的字段（为定语的字段要放到主语的前方，或页中）advancedtop 10 autoshow 透视表中百分比的显示：光标定位在data中要百分比显的字段中，右击field settings% of total如果

15、透视表中行中的一个字段占另一个行中字段百分比时，这两个字段要分别放到行中及列中。% of row% of columnrunning total in明细数据的产生：双击data中的值：多维（四维）数据透视表的制作：每张原始表格都要有交叉表的结构要划分出表格的维数。数据透视图的操作（f11）x轴（分类轴）：透视表中行中的内容。y轴（数值轴）：透视表中data中的内容。图例：透视表中column中的内容。页：透视表中的页。柱形图看比较：（3种，图表图表类型）折线图看趋势变化：饼形图看份额比较（适合数据量较少的表格）。双轴图：（大数据与小数据并存）双轴折线双轴柱线图堆积图：对图表y轴的修改（双击y

16、轴）显示单位：scaledisplay units修改主刻度：scalemajor unit显示标签：numbergeneralcustom general”m”修改y轴与x轴的交汇点：scale:对图表x轴的修改（双击x轴）字体大小：font文字方向：alignment修改与y轴的交汇点：scale对数据系列格式的修改修改数据系列间距：双击任意一个数据系列，optionsoverlap修改数据系列的坐标轴：双击任意一个数据系列，axissecondary axis修改数据系列的类型：双击一数据系列，chartchart type透视表的更新：数值的修改：修改原始表格，回到透视表中右击refresh data数据的插入：在原表格数据的中间插入，回到透视表中右击refresh data数据的append:insertnamedefine将原数据表名子删除ctrl+shift+8全选原表数据，以相同的名子命名。回到透视表中右击refresh d