EXCEl 2007
Part 1
Shift+Home 选中单元格 F2编辑单元格
Ctrl+1 单元格格式
选择性粘贴 转置行列
输入分数 0 1/8否则视为日期
记忆式输入 excel选项-高级选项
自动更正 适用于缩写
Alt+enter 单元格内强制换行，使用于文本 or 自动换行按钮
单元格批注
Ctrl+;输入日期
Ctrl+pgdn/Ctrl+pgup 激活下一或上一工作表
隐藏工作表 工作表标签 右键 开始—单元格—格式
复制区域到另一工作表，Ctrl选中两个工作表，编辑-填充-成组工作表
多个窗口查看工作表 视图-窗口-新建窗口 重排
冻结窗口保持行标题在拖动表格的时候不变 选定单元格左上角为标记
分页符 打印长的报表时有用 页面布局-页面设置-分隔符-分页符
插入水印 页面布局-页眉-图片 插入背景图片
打印标题 页面布局-页面设置
防止打印某些单位，在图标工具—格式—大小
Part2公式和函数
Chp11
粘贴名称 F3 必须先定义名称
Shift+F3 插入公式
输入公式 公式助手出现按TAB
F2 编辑单元格
改变引用模式 F4
引用其他工作表中的单元格 =工作表名称！单元格地址
=A1*’All Depth’!A1 工作表名词中有空格时 要带引号
不同工作簿的单元格 =[工作簿名称]工作表名称!单元格地址
引用表中的特定列 =函数(表名称[列名称])
在表旁边添加直接扩展表格
F9 计算所有工作簿中的公式 Ctrl+Shift+F9强制运算一次
区域命名 公式—名称管理器—定义名称-‘定义名称’
公式名称 绝对引用，常规引用某个单元格的数值 列或者行不变
相对引用，某一列
区域交叉 冒号：设定一个区域
逗号，设定两个区域的联合 连接多个引用形成一个单独的引用
空格 设定两个区域的交叉
生成随机数 粘贴公式数值 选择性粘贴—粘贴值
Chp12 文本函数
数字前加单引号，数字当文本格式
IFTEXT函数查看是否为文本格式
将非数字值转化为数字值，‘选择性粘贴’—‘加’，EXCEL会显示只能标记标记作为文本的数字
合并两个和多个单元格 =A1&” ”&A2 用空格连接两个字符
=A1&” ,”&A2 用逗号连接两个字符
公式返回的是文本字符串非不是数值 eg.连接一个货币格式的单元格只显示字符串，无货币符号
EXCEL数字格式 TEXT函数 格式化显示文本 chp24自定义数字格式
Dollar函数，将货币值显示为文本
REPT（”HO”,3）重复HO3次
用REPT填充数字
符号作为文本时用“”括起来
TRIM 清除文本参数中所有空格，只留下但此间的单空格
CLEAN取出多余字符
LEN 返回文本字符数
UPPER大写 LOWER小写 PROPER合适大小写 创建公式替原始数据 选择性粘贴—粘贴值
提取字符left从开始，right从尾部，MID（A1,2,5）从第2个开始取5个字符
替换字符 substitute（”2006pant”,”2006”,”2007”）替换2006为2007，知道替换的字符但不知道位置
Replace（”字符1”,3,2,”字符2”）从第3个字符换2个字符换为字符2 知道位置不知道具体文本值
通配符 ？任意单个字符
* 任意连续的多个字符
Find（）对大小写敏感 Search（）返回起始位置 对Part-a90字符串 search（“?-?,A1,1”）返回4
特定字符的个数 =LEN(A6)-LEN(SUBSTITUTE(UPPER(A6),"B","")) 大小写都包括
Chp13 时间
Chp14 计数
@通过命名区域，方便计数函数的使用
@自动筛选功能 数据—
@Sum(IF(logic_value,1))
DEVSQ方差和 
AVEDEV
STDEV 样本标准差 STDEVP总体标准差 VAR
样本方差 VARP总体方差
SUMSQ 平方和
COLUMN返回列号 ROW 返回行号
IS函数
@COUNTIF（range，criteria）
Range：包含数值的区域，其中的数值将确定那些单元格将被计算
Criteria 确定被计算的单元格的逻辑条件 逻辑条件表示带” ”比A1单元格中数大的条件 “>”&A1[微软用户1]
E.g COUNTIFS(Grades,">="&D2,Grades,"<="&E2) 单元格引用
@And条件
COUNTIFS(criteria_range1, criteria1, [criteria_range2])
E.g COUNTIFS(Amount,”>100”, Amount,” <200”) 为Amount中100<>200的计数
老版本 COUNTIF(Amount,”>100”)-COUNTIF (Amount, ” <200”)
E.g COUNTIFS(Month,”Jan”,SaleRep,”Brooks”,Amount,”>100”)
满足Month中为Jan，SaleRep中为Brook，Amount中为>100 三个条件的项目的数量
数组公式Ctrl+Shift+Enter且不用输入括号 {=SUM((Amout>20)*(Amout<50))} Amout为定义命名
@SUMPRODUCT将数组间对应的元素相乘，并返回乘积之和。 也可用与和条件
SUMPRODUCT((Month=Jan)*(SaleRep=Brooks)*(Amount>100))
PRODUCT（A1:A4,2）A1*A4*2 PRODUCT（A1:A4）A1到A4连乘
@Or条件
两个COUNTIF相加
特定文本的出现次数用COUNTIF函数匹配，通配符
@频率最高的数 MODE（）只对数值
=MAX(COUNTIF(data,data)) 对文本和数值均适用
@计算唯一值的数目
SUM(1/COUNTIF(Data,Data)) 分类之后的计算有多少类
SUM(IF(COUNTIF(Data,Data)=0,"",1/COUNTIF(Data,Data))) 当包含空格时用
@计算频率分布
对应步长{FREQUENCY(data,G2:G11[微软用户2] )}
FREQUENCY(data,G2:G10)/COUNT(data) 百分比频率
@INDIRECT当需要无论该单元格上方的行是否被删除或单元格是否移动，始终需要公式引用相同的单元格，请使用 INDIRECT 工作表函数，回由文本字符串指定的引用。此函数立即对引用进行计算，并显示其内容。当需要更改公式中单元格的引用，而不更改公式本身
@LARGE(array,k) 返回数列的第k个最大值
@SMALL(array,k) 返回数列的第k个最小值
任何文本条件或任何含有逻辑或数学符号的条件都必须使用双引号 (") 括起来。
如果条件为数字，则无需使用双引号。
条件求和 用条件求和多用数组公式 尤其是或条件
@SUMIF(range，criteria，range1_ criteria1) 红色为实际求和区域（可选）
Range 该区域中包含一些数值，他们确定在求和中是否包含特殊单元格
Criteria 确定在求和中是否包含特殊单元格的表达式
根据不同区域对数值求和 =SUMIF(Diffrence,”<0”,Amount) 由Difference中的值来决定Amount中的值是否加到和中 条件输入时作为文本要” ”
根据文本比较求和 =SUMIFS(Office,"Oregon", Amount) 满足office是Oregon的Amount总额
根据时间比较求和==SUMIF(DateDue,">="&DATE(2007,5,1),Amount)
AND条件 =SUMIFS(Amount,Difference,"<0",Office,"Oregon")
{=SUM((Difference<0)*(Office="Oregon")*Amount)}
OR条件 {=SUM(IF((Difference<0)+(Office="Oregon"),1,0)*Amount)}
AND与OR条件一起使用 {=SUM((Difference<0)*IF((Office="Oregon")+(Office="California"),1)[微软用户3] *Amount)}
Chp15 查找数值函数
VLOCKUP（在表中第一列查找的值，查找目标表，查找区域表中待返回的匹配值的列序号单个数字，逻辑值当false精确值） 必须以升序排列才行
HLOCKUP（在表中第一行查找的值，查找目标表，区域表中待返回的匹配值的行序号单个数字，逻辑值当false精确值）
LOCKUP 需要两个区域引用
MATCH(B1,D2:D21,0) 返回相对位置 必须以升序排列才行，精度-1>=精度 1<=的精度
INDEX返回区域中的单元格内容 INDEX(区域，行号，列号)
IFERROR(VLOOKUP(B1，Emplist,2,false)，”not found”) 当精确查找查找的返回值代替为not found
IF(ISNA(VLOOKUP(B1, Emplist,2,false)),”not found”, VLOOKUP(B1, Emplist,2,false)) 各版本通用的公式
@联合查找，查找多行多列的值INDEX(Averages,MATCH(LookupValue,Players,0)) 通过INDEX和MATCH
在Averages列中，查找由MATCH函数返回的查找值在查找区域中的单元格号
@多个查找表格中进行选择VLOOKUP(C2,IF(B2<3,Table1,Table2),2) 用IF函数
@执行双重查找
@执行双列查找 INDEX(Code, MATCH(Make&Model,Makes&Models,0) 连接起两个变量
返回最接近匹配
Chp16 财务应用公式
@贷款计算 PMT(利率，付款期总数nper，PV，FV，0期末付款1起初付款) —每期付款额
PPMT(利率，特别期，付款期总数，PV，FV，0期末付款1起初付款) —某期的还款本金
CUMPRINC累计还款本金额
IPMT 返回某期的还款利息 CUMIPMT 累计还款利息
RATE 返回利率
NPER 返回还款期数（rate，pmt，fv，type）
使用数据表归纳贷款选项
@单向数据表[微软用户4] 单个输入单元格不同值的多个计算结果
数据—数据分析—假设分析—数据表
需要设定引用单元格 E.g 引用Payment Mount 数据表计算其变化过程
在矩形区域中输入自变量的变化数值
输入引用行的单元格 设定相应于“数据表“列标题行变量的单元格引用
@双向数据表
72规则 用72除以利率 考虑能多长时间使本金翻倍
Chp17-18 数组公式
数组，可以总体或个别操作的一组纪录
数组常量 存储在内存当中 ，用{}括起来，作为常量，不能含有公式，其他数组
@多单元格数组公式 输入时要选择一个区域输入 不能只编辑单个元素，必须以一个区域为编辑对象
@单个单元格数组公式 sum{(B1:B6*C1:C6)} B1:B6存储一个数组，C1:C6存储一个数组，(B1:B6*C1:C6)生成一个数组存储在内存中，SUM公式调用该数组
一维行数组 用{ }括起来，用逗号隔开 再CTRL+SHIFT+ENTER
一维列数组用 { }括起来，用分号隔开
二维数组 每行必须含有相同的数量的元素
@TRANSPOSE 函数只用于数组的行列转换 当做数组公式输入
@生成连续整数 {ROW(1:10)} 数组，必须在选定区域内编辑，作为一个整体
{ROW(INDIRECT[微软用户5] (“1:12”))} 不会因为引用的变化产生变化
单个单元格数组公式
对区域中最小的三个数求和{=sum(small(data,{1,2,3}[微软用户6] ))}
消除中间公式{=MAX(C2:C5-B2:B5)} 直接出结果 无需先算差值在求和
在区域引用中使用数组
对含有错误信息的区域求和 {=SUM(IF(ISERROR(C4:C10)," ",C4:C10))} 用空格替代错误值
{=SUM(IFERROR(C4:C10," "))}
错误信向单元格个数 {=SUM(ISERROR(data)*1)} TRUE*1=1 FALSE*1=0
对区域中最大的N个数求和 {=SUM(LARGE(Data,ROW(INDIRECT("1:"&C17[微软用户7] ))))}
ROW(INDIRECT("1:10:”)使LARGE函数计算十次 1,2,3,4,5,6,7,8,9,10
去除0求平均数 {=AVERAGE(IF(Data<>0,Data))}
计算两个区域中不同值的个数 {=SUM(IF(MyData=YourData,0,1))}
返回区域中最大值的位置
取正值求和
多个单元格数组公式
只返回区域中正值
{=INDEX(Data,SMALL(IF(Data>0,ROW(INDIRECT("1:"&ROWS(Data)[微软用户8] ))),ROW(INDIRECT("1:"&ROWS(Data)))))}
{=IFERROR(INDEX(Data,SMALL(IF(Data>0,ROW(INDIRECT("1:"&ROWS(Data)))),ROW(INDIRECT("1:"&ROWS(Data))))),"")} only07
=IF(ISERR(SMALL(IF(Data>0,ROW(INDIRECT("1:"&ROWS(Data)))),ROW(INDIRECT("1:"&ROWS(Data))))),"",INDEX(Data,SMALL(IF(Data>0,ROW(INDIRECT("1:"&ROWS(Data)))),ROW(INDIRECT("1:"&ROWS(Data))))))
Chp19 图表
图表工作表 F11插入图表
图表工具—位置—移动图表 在多个工作表中移动图表
设置图标元素格式
Chp20 高级图表
Ctrl+1 图表格式设置 同于单元格格式
刻度格式设置——显示单位
对数刻度 在科学计数中使用
类别坐标轴选项 标签间隔 调整标签间隔一水平显示
文本坐标轴 时间类坐标轴
拖拽图表时 按住CTRL键实现图表的复制
@处理随时间变化的数据区域，使用表格作为数据源，当向表格添加行时，图标会自动更新
@处理丢失数据 —选择数据源—隐藏或空单元格设置
@误差线 用于反映不确定因素“加或减”的信息，只适用于面积图，条形图，折线图，XY图，柱形
@趋势线 移动平均 在趋势线格式中设置 ，对于小除很多误差的数据有用 E.g XY散点图
创建组合图表 设置数据系列格式—次坐标轴—改变图表类型
显示数据表 以表格的形式显示图表的数据
创建对比柱形图 一组数据要输入负号
甘特图表 用于项目进度的图表 堆叠带状图 要指定时间和工期为系列，项目名称作为横坐标项目
Chp21 条件格式
@使用图形的条件格式
数据条
色阶
图标集 e.g给满足条件的数字标记 >80 指定图标集之后，条件格式—管理规则—基于各自值设置单元格格式—类型改为数字 THEN 只为包含以下内容的单元格设置格式
@给予公式的规则
必须以最终返回逻辑值‘真’，‘假’的逻辑公式
识别周末=IF(ISBLANK(A1),FALSE,OR(WEEKDAY(A1)=7,WEEKDAY(A1)=1))
棋盘式底纹=MOD[微软用户9] (ROW(),2)=MOD(COLUMN(),2)
交替底纹 =MOD(ROW(),2)=0
对多组行应用底纹 =MOD(INT((ROW()-1)/4)+1,2) 带底纹4行，没带底纹四行
Chp22 利用图片和图形改进工作样式
插入形状，格式化形状
组合对象
对其对象 绘图工具—格式—排列—对齐
Chp24 使用自定义数字格式
数字格式的字符无法查找替换
再输入数字前避免自动格式，需要预先设置数字格式
Chp25 数据有效性
@数据—数据工具—数据有效性—设置
@创建下拉列表—数据—数据工具—数据有效性—设置—序列
数据有效性公式 必须返回逻辑值的公式 或者以1或0返回值
Chp26 工具表的分级显示
创建内容汇总报告，适用于带分类汇总的分层数据
创建分级显示前，要求所有求和公式正确且一致（在同一对应位置）
数据—分级显示—组合—自动
Chp27 连接和合并计算
含连接公式（外部引用公式）依存工作表
@作用 可以用于合并计算不同的文件，将较大的工作簿分为不同的小文件
数据—数据工具—合并计算 或者 剪贴板粘贴 链接
外部引用的公式：[Workbookname]sheetname!CellAddress
包含空格时，用单引号将文本括起来
可以创建指向不存在的文件链接
合并计算 工作表要保持一致
通过数据—数据工具—合并计算 实现
通过位置 适用工作表中按统一格式排列
通过目录 通过行和列标签匹配工作表中的数据（数据在源数据表中不是按不同格式排列）
Chp29 其他应用程序共享数据
在工作表中嵌入对象 插入—文本—对象
Chp30 EXCEL工作组
文件读取权限 防止后续人更新是覆盖前人的数据
Chp31 保护工作成果
OFFICE按钮—准备—检查文档
Chp34 数据透视表
从数据库中产生动态汇总表
要求 长方形数据库格式
数据库包括的字段 ：数据&类别 类别字段可以作为行、列、筛选项
插入—表—数据透视表
报表筛选 在数据透视表中具有分页方向的字段
数据透视表格式设置 选项—活动字段—字段设置—值字段设置
不能在数据表新行添加或列更改人以计算的数值或输入公式，如果想以这样的方式修改，必须先复制，粘贴通过选择性粘贴—粘贴值
e.g 不同类型账户的类型的金额分布
Amount作为行标题，组合—设置步长，设置区间
Amount 计数项 Amount，占同列的百分比
数据透视表大部分数值型数据，对非数值型透视表需要运用计数
组合数据表中的项目
手动组合
自动分组事例 根据日期组合—将字段分组
创建频率分布图 将所选内容分组—设置步长
@创建计算字段和计算项目
创建计算字段根据数据透视表中的字段创建新的字段 必须位于数据表的数值区域
方法，在表格中插入一列，并创建公式
在任意数值区域数据透视表—选项—工具—公式—计算字段
创建计算项目使用一个字段中其他项目[微软用户10] 的内容
方法，在表格中插入一行或多行，并写入使用其他行数值的公式
必须位于行标签、列标签、报表筛选
在行标签区或列标签区域 数据透视表—选项—工具—公式—计算字段
用于创建的公式是不能在单元格中输入的公式，而是在对话框中
分类汇总各项
@引用数据透视表中的单元格 GETPIVOTDATA
@数据透视图
@使用数据透视表生成报表
改变字体但不改变样式 通过样式CTRL+G 定位常量
Chp36 执行电子表格的what-if分析
创建动态模型通过改变输入单元格的数值，观察在其他单元格上的结果的过程
E.g 抵押贷款模型 中首付，利率，购买价格的变化对贷款变化的影响
假设分析
手动假设分析 可以通过公式—公式审核—监视窗口 查看进度
数据表
@创建单输入数据表，在单输入单元格中使用多个值时，单输入数据表显示一或多个公式结果
左列包含 单输入单元格中的多个值
单输入数据表按列垂直排列，“输入引用列的单元格”
如果要输入单元格的值按行排列，“输入引用行的单元格”
顶行对位于工作簿其他位置的公式引用 分别指向含有公式的公式
@创建输入双输入数据表
@方案管理器
可以为任意多的变量存储输入值的不同组合——可变单元格，并为每个组合命名
可以根据名称选择一组值
可以创建汇总报表，在任意数量的结果单元格显示不同的值组合的效果
@创建方案，最好对区域做好命名，以及对公式的应用要清楚
@合并方案 在工作簿下拉列表中选择列表
@生成方案报表
数据—假设分析—方案—方案管理—摘要
Chp37 使用单变量求解和规划求解分析数据
找一个或多个能够在公式单元格中产生预期结果的输入单元格的数值
E.g 知道销售额变化？，总收益变化——假设分析
知道总收益的变化，则销售额需要变化多少——翻转的假设分析
@单变量求解——决定单一输入单元格所需要的数值，这一数值能在独立（公式）单元格中产生所需要的后果
@单一单元格单变量求解
E.g 抵押贷款 单变量求解——目标单元格[LQ11] 、目标值：需要知道在假设分析中的结果的变化
可变单元格:
@规划求解——决定在多重输入单元格中输入的产生所需结果的数值
指定多个可调整单元格，指定其数值约束，
求出特定工作表单元格的解的max&min
对一个问题求多个解
@使用范围 目标单元格依赖其他单元格与公式
目标单元格依赖于一组单元格与公式
必须服从一定的约束与限制
Chp37 规划求解
规划求解结果输出 用规划求解替换原单元格
创建三个报表
保存方案，可以使方案管理器使用
规划求解选项 收敛度 用于求解非线性问题
Chp38 分析工具
@相关系数矩阵 先给数据列命名
用函数=CORREL(INDIRECT($A18[微软用户12] ),INDIRECT(B$17)[微软用户13] ) 内容都一样
@协方差函数=COVAR(INDIRECT($A28),INDIRECT(B$27))
分析工具—别忘了标志位于第一行的选项
@方差分析
@描述性统计
@指数平滑 阻尼系数 E.g 0.7*B3+0.3*C3 中0.3为阻尼系数
Chp39 VBA简介 Alt+F11 vb窗口 Alt+F8 宏
适用范围：
自动执行需经常执行的过程
自动执行重复操作
创建自定义命令
宏的类型VBA子程序 一个命令 能被用户和其他宏执行
以关键字Sub开头，接着宏的名称，加一对括号
End sub 结尾
VBA函数 函数返回单一值 必须用手工创建
F unction End function
@术语 代码 控件 函数 方法
模块 过程 属性 用户窗体
使用录制的宏
@绝对录制 当录制宏时，Excel存储的是准确引用，当执行时，同样的单元格总是被选中
相对录制 使用相对引用控件
通用宏，保存在“个人宏工作簿”
@为按钮制定一个宏
开发工具—控件—插入
@编写
@VBA工作原理
可以通过指定对象在其对象层中的位置来引用这个对象，使用句号作为分隔符
E.g Aplication.workbooks(“Book1.xlsx”).worksheets(“Sheet1”).Range(“A1”)
引用的层级
如果忽略特殊引用，EXCEl会使用活动对象
@对象和集合
对象Ranges、Charts、Autoshape
操纵单元格要用ranges对象
在对象层顶端Application对象，实际excel程序
Application.Activeworkbook Application.ActiveCell
Application.Activeworksheet Application.selection返回Application对象中活动窗口被选中的对象
@方法对对象执行的操作
@控制执行
@IF—then结构赋予应用程序做决定的能力
IF 条件 then语句[else其他语句]
@FOR-Next循环
@With-end with结构处理相同对象的几个属性或方法的捷径
@Select Case结构 做出选择
[微软用户1]作为逻辑条件输入，是文本，A1单元格中的数据，逻辑条件因为运算符号为文本，必须用“”括起来
对比IF(Difference<0,Difference)
[微软用户2]分组区域
[微软用户3]返回满足条件的个数，可以帮助统计
[微软用户4]假设分析：通过更改单元格中的值来查看这些更改对工作表中公式结果的影响的过程
数据表无法容纳两个以上的变量。如果要分析两个以上的变量，则应改用方案
[微软用户5]返回由文本字符串指定的引用。此函数立即对引用进行计算，并显示其内容。当需要更改公式中单元格的引用，而不更改公式本身
如果需要无论该单元格上方的行是否被删除或单元格是否移动，始终需要公式引用相同的单元格，请使用 INDIRECT 工作表函数
[微软用户6]数组常量
[微软用户7]定位目标单元格的值，如果C17中为3，求最大的3个数的和
[微软用户8]ROWS函数 返回需要的行数
[微软用户9]1,0即为逻辑真，假的值
[微软用户10]字段中的一个元素，作为行或列的标题显示
[LQ11]必须含有公式
[微软用户12]行标题
[微软用户13]列标题