Excel 技巧篇
让不同类型数据用不同颜色显示
大于等于2000元的工资总额以“红色”显示，大于等于1500元的工资总额以“蓝色”显示，低于1000元的工资总额以“棕色”显示，其它以“黑色”显示：格式――条件格式，按要求添加所需格式
建立常用文档菜单栏：
1、 工具栏――自定义――命令――新菜单，拖动子菜单项到当前菜单栏
2、 将插入菜单中的“超链接”拖到新建的子菜单中，根据需要进行命名，如“工资表”
3、 编辑超链接，使其链接到所需的工资表，以后单击其则可打开，可以多添加一些
用“视图管理器”保存多个打印页面：
1、 将不需要的行列隐藏，设置好面页面
2、 视图――视图管理器――添加
3、 使用时打开视图管理器――选择――显示即可
自定义排序，让数据按所需要求排序：
1、 工具――选项――自定义序列――输入所需序列
2、 使用：排序――选项――在下拉框中选择输入的序列即可
隐藏数据：
1、 选定区域――格式――数字――自定义：“；；；”
2、 切换到“保护”标签下，勾选“隐藏”
3、 保护工作表，设置密码
让中英文输入法智能出现：
选定区域――数据有效性――输入法――模式，选择所需的输入法即可
用自动更正输入统一文本
1、 自动更正――替换，输入“pcw”，在替换为框中输入“电脑报”，添加――确定
2、 以后输入pcw，则可以显示“电脑报”
使用监视窗口，同时查看多个多个单元格中的内容
1、 视图――工具栏――监视窗口
2、 添加需监视的单元格，可以是其它表中的
小数点自动定位：
选项—编辑—自动设置小数点（两位），输入5则为0.05
将基数字转换为序数词
=A2&IF(VALUE(RIGHT(A2,2))={11,12,13},"th",IF(VALUE(RIGHT(A2))={1,2,3},CHOOSE(VALUE(RIGHT(A2)),"st","nd","rd"),"th"))
快速录入文本文件中的内容 ： 数据—获取外部数据—导入文本文件
特殊的双击功能
1、在工具栏右侧的空白处双击，可打开“自定义”对话框。
2、在单元格中双击，单元格进入编辑状态。
3、在“格式刷”按钮上双击，格式刷可以反复多次使用。单击“格式刷”或者按Esc键可以取消
4、在标题栏上双击，Excel窗口由最大化（原始状态）还原到原始状态（最大化）大小。
5、在窗口左上角Excel标志上双击，则退出Excel（如果当前文档没有保存，系统会提示保存）。
6、在滚动条上端/左端与编辑区交界处双击，可拆分窗口。
7、在菜单上双击，即可将菜单中所有的菜单项（包括不常的菜单项）全部展开。
8、某行/列有多个连续的空白或数据单元格时，在某个单元格边上双击（十字形）可快速定位（最下/右必须有数据）
9、双击数据透视表中的数据, 可在新的工作表中列出该数据的明细
快速处理多个工作表
按住“Ctrl"或“Shift" 键选定工作表，然后批量处理，如设置相同的列宽，字体等
F1---F12 功能键的功用
F2:进入编辑状态
F4:改变单元格引用方式（相对，绝对）
F5:定位
F6:在同一表格的不同分拆栏里切换
F7:拼写检查
F8:区域扩展
F9:活动工作表重算
F10: 同Alt，菜单选择
F11:自动生成图表
F12:另存为
Application.OnKey "{F11}", "ccc" 禁用F11
批量替换批注：
修订－－批注—替换
把加载宏内置到Excel文件里：
Private Sub Workbook_Open()
Application.RegisterXLL Filename:= Application.Path & "\Library\Analysis\ANALYS32.XLL"
End Sub
提取单元格中的文字：
=LEFT(A1,(SEARCHB("?",A1)-1)/2)，查找第一个半角字符出现的位置
=RIGHT(A1,LENB(A1)-LEN(A1))
关于宏和程序
问：编了一个较完整的程序，能够给源程序加密码，实现"工程不可见"，但在vba里还能看到大部分宏，虽然不能编辑，但能运行，如何隐藏起
答：不用模块函数,重写成类或放到workbook中,或在程序中直接将菜单宏隐藏。
问：已经屏蔽alt+F11 键，虽然不能看到宏程序，但依然可以运行宏，如何隐藏宏。
答：在宏的声明前加Private。
禁止输入空格： 有效性公式。=COUNTIF(A1,"* *")=0
固定数据输入时的焦点： “Ctrl”键的同时单击选择该单元格。按“Enter”键光标不会移动
利用公式求值检查错误： 在自定义中将“公式求值”拉到菜单栏上，单步执行公式
在工具按钮之间设置分隔线： 按住Alt，单击并稍稍往右拖动即可，取消时向左即可。
同时打开相关联的所有工作簿： 将所有相关的表打开，然后选择“菜单”—文件—保存工作区即可
避免数据显示误差： [工具]→[菜单]→[选项]→[重新计算]--“以显示值为准”
数组：用｛｝括起来，如果是多维，则多维之间用“;”分隔
1、数组中不能逐个列出引用，但可以使用区域，｛A1,B1｝错，｛A1:B1｝正确
2、数组单元格不能单独编辑，需要“定位”—“当前数组”才能编辑
3、删除数组，选定要删除的数组，按[Ctrl]+[Delete]或选择编辑菜单中的“清除”命令
√ 输入： 按住ALT键输入41420后放开ALT键
HYPERLINK("#表1！L2","轻客支撑")，#加在表名前指当前工作簿，加在单元格前是指当前工作表
把Excel 表格转换为图片
按住 Shift 键点击“文件”菜单，原来的“关闭”菜单项就会变成“全部关闭”
按下 Shift 键的同时点击“编辑”菜单，原来的复制和粘贴就会变成“复制图片”和“粘贴图片”
在B1中同步显示A列中最后一行的内容
最后一行为文本： =offset($b$1,MATCH(CHAR(65535),b:b)-1,)
最后一行为数字： =offset($b$1,MATCH(9.9999E+307,b:b)-1,)
或者：=lookup(2,1/(b1:b1000<>""),b1:b1000)
怎样很简单的判断最后一位是字母
right(a1)*1 出错的是字母
=IF(ISNUMBER(--RIGHT(A1,1)),"数字","字母")
=IF(ISERR(RIGHT(A1)*1),"字母","数字")
工具——选项——
有无打勾？去掉
原函数=a1+a4+a7+a10+a13+a16+a19+a22... ，数组公式：｛=SUM(N(OFFSET(A1,(ROW(1:10)-1)*3,)))｝
奇数行求和 =SUMPRODUCT((A1:A1000)*MOD(ROW(A1:A1000),2))
偶数行求和 =SUMPRODUCT((A1:A1000)*NOT(MOD(ROW(A1:A1000),2)))
对日期进行上中下旬区分：=LOOKUP(DAY(A1),{0,11,21,31},{"上旬","中旬","下旬","下旬"})
=--A1>50000中的--代表什么意思，转变为数值.与+0,*1,是一样的效果。
打印行号列标： 文件菜单-----页面设置---工作表----在打印选项中的行号列标前打勾。
打印不连续区域： 按CTRL键不松，选取区域，再点文件菜单中的打印区域--设置打印区域。
打印时自动隐去被0除的错误提示值： 页面设置—工作表，错误值打印为空白
求A1:B10中A列等于1的对应B列中的最小值 ｛=min(if(a1:a10=1,b1:b10)) ｝
定义名称的妙处：
引用位置：可以是工作表中的任意单元格，可以是公式，也可以是文本。
妙处1：减少输入量 DATA = “I LOVE YOU， EXCEL！”， “=DATA”显示“I LOVE YOU， EXCEL！”
妙处2：出现多次的字段： 例如 =IF(ISERROR(IF(A1>B1,A1/B1,A1)),””, IF(A1>B1,A1/B1,A1))
将IF(A1>B1,A1/B1,A1)定义“A_B”，公式便简化为=IF(ISERROR(A_B),””,A_B)
妙处3：解决公式嵌套限制和单元格字符数限制
妙处4：某些函数只能在名称中使用， A1=1+2+3，定义名称 RESULT = EVALUATE(Sheet1!$A1)
在B1输入=RESULT，B1就会显示6了。
妙处5：图片的自动更新，例如你想要在一周内每天有不同的图片出现在你的文档中，具体做法是：
找7张图片分别放在A1至A7单元格中，调整单元格和图片大小，使之恰好合适
定义名称MYPIC = OFFSET(SHEET1!$A$1,WEEKDAY(TODAY(),1)-1,0,1,1)
控件工具箱--文字框，在编辑栏中将EMBED("Forms.TextBox.1","")改成MYPIC 就大功告成了。
此外，名称和其他，例如数据有效性的联合使用，会有更多意想不到的结果。
假如A栏里有任一单元格有"$"字符串，则等于1，否则等于0
=IF(COUNTIF(A:A,"*$*")>0,1,0) 或者 ＝(countif((A:A,*$*)>0)+0
在查找中使用通配符： ？和 * ，各代表一个字符和一串字符，可用于任何查找，如Vlookup中
图片批注 ：编辑批注--边框上右击--设置批注格式--颜色与线条--颜色--填充效果--图片--选择图片.
if函数的另类用法 =IF(OR(VALUE(RIGHT(A1,2))={11,12,13}),"包含","不包含")
算术运算符 | 含义 | 示例 |
+ | 加法运算 | 6+1 |
— | 减法运算 | 8-3 |
* | 负号 | 3*7 |
/ | 乘法运算 | 4/5 |
% | 百分比 | 50% |
^ | 乘幂 | 5^4 |
引用运算符 | 含义 | 示例 |
： | 区域运算符，对两个引用之间，包括两个引用在内的所有单元格进行引用 | A1:B2 |
， | 联合运算符，将多个引用合并为一个引用 | SUM(A1:A7,B1:B7) |
（空格） | 产生两个交叉的引用 | SUM(A1:A7B1:B7) |
运算符的优先级：
运算符 | 说明 |
： | 引用运算符 |
， | 引用运算符 |
（空格） | 引用运算符 |
— | 负号 |
% | 百分比 |
^ | 乘幂 |
*和/ | 加减法 |
& | 文本连接运算符 |
=,>,<,>=,<=,<> | 比较运算符 |
三维引用：在同一文件的多个工作表上的相同单元格或单元区域中的数据使用
示例：在工作表Sheet4单元格B2中统计Sheet1——Sheet3中单元格C3值之和，操作步骤如下：
1、打开工作表，选定Sheet4中的B2单元格，键入“＝sum()”
2、然后单击Sheet1的标签，这时系统提示出错，单击确定 ，再次单击工作表Sheet1的标签，然后按住 Shift，单击工作表Sheet3的标签，然后单击C3单元格。完成上述操作后按 Eeter，则在 B2单元格中出现“＝Sheet1：Sheet3！C3”
同一文件中引用其它表的单元格，其格式为： 工作表名称！（空格）单元格名。
引用其它文件中的单元格，格式为：路径名\【文件名】工作表名！单元格和单元格区域的地址
Excel 快捷键
常用快捷键：
Shift + F11 插入新工作表，或者使用Alt+Shift+F1
Ctrl + PageDown 移动到工作簿中的下一张工作表。
Ctrl + PageUp 移动到工作簿中的上一张工作表。
Shift + Ctrl + PageDown 选择多张工作表。取消选择多按 Ctrl PageDown，
Shift + Ctrl + PageUp 选择当前工作表和上一张工作表。
Alt+OHR 对当前工作表重命名（“格式”菜单的“工作表”子菜单上的“重命名”命令）。
Alt+EM 移动或复制当前工作表（“编辑”菜单上的“移动或复制工作表”命令）。
Alt+EL 删除当前工作表（“编辑”菜单上的“删除工作表”命令）。
Shift + Tab 向左移动一个单元格。
Shift + Enter 向上移动一个单元格。
Ctrl + → 移动到当前数据区域的边缘。
Alt + PageDown 向右移动一屏。
Alt + PageUp 向左移动一屏。
F6 切换到已拆分（“窗口”菜单上的“拆分”命令）的工作表中的下一个窗格。
Shift + F6 切换到已拆分的工作表中的上一个窗格。
Ctrl + Backspace 滚动以显示活动单元格。
F5 显示“定位”对话框。
Shift + F5 显示“查找”对话框。
Shift + F4 重复上一次“查找”操作（等同于“查找下一个”）。
Ctrl + 句号 按顺时针方向移动到选定区域的下一个角。
Ctrl + Alt + → 在不相邻的选定区域中，向右切换到下一个选定区域。
Ctrl + Alt + ← 向左切换到下一个不相邻的选定区域。
单元格及对象操作快捷键：
Ctrl + 减号 删除选定的单元格。
Ctrl + 加号 插入空白单元格。
Ctrl + 空格键 选择整列。
Shift + 空格键 选择整行。
Ctrl + A 选中当前数据区域（四周为空），再次按 Ctrl + A 可选中整个工作表。
Shift + Backspace 在选择了多个单元格的情况下，只选择活动单元格。
Ctrl + Shift + 空格键 作用同Ctrl+A ，但它同时会选中表中的对象
Ctrl + 6 在隐藏对象、显示对象和显示对象占位符之间切换。
Ctrl + Shift + * 选择活动单元格周围的数据区域（四周为空）
Ctrl + / 选择包含活动单元格的数组
Ctrl + Shift + O（字母） 选择含有批注的所有单元格
Ctrl + \ 在选定的行中，选择与活动单元格中的值不匹配的单元格。
Ctrl + Shift + | 在选定的列中，选择与活动单元格中的值不匹配的单元格。
Ctrl + [ 选择由选定区域中的公式直接引用的所有单元格。
Ctrl + Shift + { 选择由选定区域中的公式直接或间接引用的所有单元格。
Ctrl + ] 选择包含直接引用活动单元格的公式的单元格。
Ctrl + Shift + } 选择包含直接或间接引用活动单元格的公式的单元格。
Alt + ; 选择当前选定区域中的可见单元格。
F8 打开或关闭扩展模式。在扩展模式中，箭头键可扩展选定区域。
Shift + F8 将其他区域的单元格添加到选定区域中，或使用箭头键移动到所要添加的区域的起始处，然后按 F8 和箭头键以选择下一个区域。
Shift + 箭头键 将选定区域扩展一个单元格。
Ctrl + Shift + 箭头键 将选定区域扩展到与活动单元格在同一列或同一行的最后一个非空单元格。
Shift + Home 将选定区域扩展到行首。
Ctrl + Shift + Home 将选定区域扩展到工作表的开始处。
Ctrl + Shift + End 将选定区域扩展到工作表上最后一个使用的单元格（右下角）。
Shift + Page Down 将选定区域向下扩展一屏。
Shift + Page Up 将选定区域向上扩展一屏。
End + Shift + 箭头键 将选定区域扩展到与活动单元格在同一列或同一行的最后一个非空单元格。
End + Shift + Home 将选定区域扩展到工作表的最后一个使用的单元格（右下角）
End + Shift + Enter 将选定区域扩展到当前行中的最后一个单元格。在 “Lotus 1-2-3 常用键”不起作用
ScrollLock + Shift + Home 将选定区域扩展到窗口左上角的单元格。
ScrollLock + Shift + End 将选定区域扩展到窗口右下角的单元格。
数据操作快捷键：
Alt + Enter 在单元格中换行
Ctrl + Enter 用当前输入项填充选定的单元格区域
F4 或 Ctrl + Y 重复上一次操作
Ctrl + Shift + F3 由行列标志创建名称
Ctrl + D 向下填充
Ctrl + R 向右填充
Ctrl + F3 定义名称
Ctrl + K 插入超链接
Ctrl + ;（分号） 输入日期
Ctrl + Shift + :（冒号） 输入时间
Alt + 向下键 显示区域当前列中的数值下拉列表。
Ctrl + Z 撤消上一次操作
Ctrl + Delete 删除插入点到行末的文本
F2 编辑当前单元格
Shift + F2 编辑单元格批注
输入特殊字符：
Alt + 0162 输入分币字符 ￠。
Alt + 0163 输入英镑字符 ￡。
Alt + 0165 输入日圆符号 ￥。
Alt + 0128 输入欧元符号 €。
公式操作快捷键：
"=(等号)" 键入公式。
Ctrl + Shift + Enter 将公式作为数组公式
Shift + F3 在公式中，显示“插入函数”对话框。
Ctrl + A 当插入点位于公式中公式名称的右侧时，显示“函数参数”对话框。
Ctrl + Shift + A 当插入点位于公式中函数名称的右侧时，插入参数名和括号。
F3 将定义的名称粘贴到公式中。
Alt + =（等号） 插入自动求和函数
Ctrl + "（双引号） 将活动单元格上方单元格中的数值复制到当前单元格或编辑栏。（也可再加Shift键）
Ctrl + '（撇号） 将活动单元格上方单元格中的公式复制到当前单元格或编辑栏。
Ctrl + `（左单引号） 在显示单元格值和显示公式之间切换。
F9 重新计算打开工作薄中的所有工作表，如果选择了一部分公式，则计算选定部分。
按 Enter 或 Ctrl + Shift + Enter（数组公式）后用计算出的值替换选定部分
Shift + F9 计算活动工作表。
Ctrl + Alt + F9 计算所有打开的工作簿中的所有工作表，无论其在上次计算之后是否进行了更改。
Ctrl + Alt + Shift + F9 重新检查从属公式，然后计算所有打开的工作簿中的所有单元格
设置数据的格式：（以下数字为键盘区数字，不是小键盘数字）
Alt + '（撇号） 显示“样式”对话框
Ctrl + 1 显示“单元格格式”对话框
Ctrl + Shift + ~ “常规”格式
Ctrl + Shift + 1 使用千位分隔符且不带小数的格式（-515，151）
Ctrl + Shift + 2 应用含小时和分钟并标明上午或下午的“时间”格式。
Ctrl + Shift + 3 日期格式（2011-2-21）
Ctrl + Shift + 4 两位小数的“贷币”格式（负数在括号中）
Ctrl + Shift + 5 不带小数的“百分比”格式
Ctrl + Shift + 6 两位小数的“科学记数”格式
Ctrl + Shift + 7 对选定单元格应用外边框
Ctrl + ( 隐藏当前单元格所在行
Ctrl + ) 隐藏当前单元格所在列
Ctrl + Shift + ( 取消所有隐藏行
Ctrl + Shift + ) 取消所有隐藏列
Ctrl + Shift + _ 取消选定单元格的外边框
Ctrl + B 加粗
Ctrl + I 倾斜
Ctrl + U 下划线
Ctrl + 5 删除线
自动筛选中的快捷键：
Alt +↓ 在包含下拉箭头的单元格中，显示当前列的“自动筛选”列表。
Alt +↑ 关闭当前列的“自动筛选”列表。
Home 选择“自动筛选”列表中的第一项（“（全部）”）。
End 选择“自动筛选”列表中的最后一项。
Enter 根据“自动筛选”列表中的选项筛选区域。
分级显示数据中的快捷键：
Alt + Shift + → 对行或列分组。
Alt + Shift + ← 取消行或列分组。
Ctrl + 8 显示或隐藏分级显示符号。
图形对象操作快捷键：
Ctrl + Shift + C 复制对象属性
Ctrl + Shift + V 粘贴对象属性到本对象中
联系客服
