John walkenbach’s favorite excel tips & tricks

第 1 部分 excel基本操作 1

技巧1 了解excel的各种版本 3

技巧2 使菜单的效能最大化 5

保证excel始终显示完整的菜单 5

尽量使用键盘 5

学会使用菜单快捷键 6

使用右键快捷菜单 6

技巧3 有效地选取单元格 7

使用shift键和箭头键选择区域 7

选择当前区域 7

用按shift键并单击的方法来选定区域 7

选定非连续区域 7

选定整行 8

选定整列 8

选取多个工作表的多个区域 8

技巧4 选择“特殊”区域 10

技巧5 撤销、恢复和重复 12

撤销 12

恢复 12

重复 12

.技巧6 改变撤销的步数 14

技巧7 一些实用的快捷键 15

技巧8 在一个工作簿的各工作表间浏览定位 16

技巧9 重置工作表中的已使用范围 17

技巧10 工作簿与窗口的区别 18

技巧11 使用“excel 2003帮助”时避免出现任务窗格 19

技巧12 自定义默认工作簿 20

技巧13 改变工作表标签外观 22

改变工作表标签的颜色 22

改变工作表标签文字的字号 22

技巧14 隐藏用户界面元素 24

隐藏工具栏 24

隐藏用户界面元素 24

技巧15 隐藏列或行 26

技巧16 隐藏单元格内容 27

技巧17 进行模糊搜索 28

技巧18 替换格式 30

技巧19 增加行列数量 32

技巧20 限定工作表中的可用范围 33

设置scrollarea属性 33

使用工作表保护 34

技巧21 插入单元格批注的另一种方法 36

技巧22 改变excel帮助窗口中文字的大小 37

技巧23 使工作表“完全隐藏” 38

技巧24 常见设置问题解答 39

问题1：excel在启动时崩溃 39

问题2：启动时自动打开了很多文档 39

问题3：excel的菜单混乱不堪 39

问题4：菜单中的命令不见了 40

问题5：excel显示了多余的菜单命令 40

问题6：双击一个excel文件后没有反应 40

问题7：excel启动时显示报错信息——“隐含模块中的编译错误” 41

问题8：不存在宏时显示了宏警告信息 41

问题9：错误地出现了“文件正在由……编辑”信息 41

问题10：输入数字时小数位出现错误 41

问题11：同一工作簿出现在多个窗口中 42

问题12：列标题中出现了数字而不是字母 42

问题13：excel工作异常 42

第 2 部分 数据输入 43

技巧25 了解数据的类型 45

输入数值 45

输入日期和时间 45

输入文本 47

输入公式 47

技巧26 在输入数据后移动单元格指针 48

技巧27 在输入数据前选择要输入的单元格区域 49

技巧28 使用“记忆式键入”功能自动完成数据输入 50

技巧29 使用“冻结窗格”功能来保持标题可见 51

技巧30 用序列自动填充区域 52

技巧31 遇到分数时的解决方法 54

技巧32 利用声音验证数据 56

技巧33 控制自动超链接 57

取消自动超链接 57

关闭自动超链接 57

取消现有的超链接 57

取消多个超链接 58

使用vba来取消超链接 58

技巧34 输入信用卡号码 59

技巧35 使用excel内置的数据输入记录单 60

技巧36 自定义和共享自动更正输入 62

技巧37 限制光标在输入单元格内的移动 63

技巧38 控制office剪贴板 64

技巧39 在单元格里创建下拉列表框 66

第 3 部分 格式设置 69

技巧40 快速设置数字格式 71

技巧41 使用“分离式”工具栏 72

技巧42 创建自定义数字格式 73

数字格式字符串的组成部分 73

自定义数字格式代码 74

技巧43 使用自定义数字格式来缩放数值 76

技巧44 使用自定义日期格式 78

技巧45 一些有用的自定义数字格式 79

隐藏零值 79

显示前导零 79

百分比格式 79

显示分数 80

重复文本 80

在右边显示负号 80

按单元格的数值设置格式 80

颜色值 81

隐藏某些类型的内容 81

技巧46 在一个单元格里显示文本和数值 83

使用串联 83

使用text函数 83

使用自定义数字格式 83

技巧47 合并单元格 84

技巧48 对单元格里的个别字符进行格式设置 85

技巧49 显示超过24小时的时间 86

技巧50 修改非数值型数字 87

技巧51 使用“自动套用格式” 88

技巧52 用网格线、边框、下划线进行格式处理 90

技巧53 创建3d格式效果 92

技巧54 单元格里的文字换行 94

技巧55 查看一种字体中的所有字符 95

技巧56 输入特殊字符 97

技巧57 使用命名样式 98

使用“样式”对话框 98

创建新样式 99

技巧58 了解excel如何处理颜色 100

改变颜色 100

56色调色板的例外 101

技巧59 设置间隔式行底纹 102

技巧60 给工作表添加背景图像 104

第 4 部分 基本的公式和函数 105

技巧61 何时使用绝对引用 107

技巧62 何时使用混合引用 109

技巧63 改变单元格引用类型 110

技巧64 “自动求和”技巧 111

技巧65 使用状态栏的选区统计功能 112

技巧66 将公式转化为数值 113

技巧67 在不使用公式的情况下对数据进行变形 114

技巧68 使用公式对数据进行变形 115

技巧69 在保留公式的情况下删除数值 117

技巧70 处理函数参数 118

技巧71 在不使用批注的情况下对公式进行注释 120

技巧72 精确复制一定区域内的公式 121

技巧73 从任意位置监控公式单元格 122

技巧74 显示及打印公式 123

技巧75 避免公式中的错误显示 124

技巧76 使用“单变量求解”工具 125

技巧77 了解有关名称的秘密 127

技巧78 使用命名常量 128

技巧79 在名称中使用函数 130

技巧80 编辑名称引用 131

技巧81 使用动态名称 132

技巧82 创建工作表级的名称 134

技巧83 处理1900年以前的日期 135

技巧84 处理负时间值 136

第 5 部分 实用公式举例 139

技巧85 计算节假日 141

元旦 141

马丁·路德·金纪念日 141

总统日 141

复活节 141

阵亡将士纪念日 141

独立日 142

劳工节 142

哥伦布发现美洲纪念日 142

退伍军人节 142

感恩节 142

圣诞节 142

技巧86 计算加权平均值 143

技巧87 计算年龄 144

方法1 144

方法2 144

方法3 144

技巧88 利用数组公式进行数值排位 145

技巧89 对单元格中的字符进行计数 146

对一个单元格中的特定字符进行计数 146

计算一个单元格中某个子字符串出现的次数 146

技巧90 将数字转换为序数 147

技巧91 从字符串中抽取单词 148

抽取字符串中的第一个单词 148

抽取字符串中的最后一个单词 148

抽取字符串中除第一个单词外的所有单词 148

技巧92 分解姓名 149

技巧93 从姓名中删除称谓 150

技巧94 生成日期序列 151

使用自动填充 151

使用公式 151

技巧95 判断特定日期 153

确定某日是一年中的第几天 153

判断某日是星期几 153

判断最近星期日的日期 153

判断某个日期后的第一个星期几的日期 154

判断当月第几个星期数的日期 154

判断一个月的最后一天 154

判断一个日期所在的季度 154

技巧96 在单元格区域中显示日历 155

技巧97 数字舍入的几种方法 156

舍入到最接近的倍数 156

舍入货币值 156

使用int和trunc函数 157

舍入为n位有效数字 157

技巧98 舍入时间值 158

技巧99 返回某列或某行中的最后一个非空单元格 159

技巧100 使用countif函数 160

技巧101 对符合多个筛选条件的单元格进行计数 161

使用筛选条件“与” 161

使用筛选条件“或” 162

结合使用筛选条件“与”和“或” 163

技巧102 对一定区域中的不同项进行计数 164

技巧103 使用单一条件求条件和 165

仅对负值求和 165

基于另一个区域进行数值求和 165

基于文本对比进行数值求和 165

基于日期对比进行数值求和 166

技巧104 使用多个条件求条件和 167

使用“与”条件 167

使用“或”条件 167

结合使用“与”和“或”条件 168

技巧105 查找特定数值 169

技巧106 进行双向查找 171

使用公式 171

使用固有交叉点 172

技巧107 进行双列查找 173

技巧108 使用数组进行查找 174

使用查找表 174

使用数组 174

技巧109 使用indirect函数 175

技巧110 创建超大型公式 177

第 6 部分 图表与图形 179

技巧111 直接在区域中创建文本图表 181

技巧112 图表的注释 183

技巧113 创建可自动扩展的图表 184

针对excel 2003 184

针对excel 2003以前的版本 184

技巧114 创建组合图表 186

技巧115 解决折线图中缺失数据的问题 188

技巧116 创建甘特图 190

技巧117 创建温度计式图表 192

设置百分比公式 192

创建图表 192

修饰温度计 193

技巧118 创建带有图片的图表 195

技巧119 标绘单变量数学函数 197

技巧120 标绘双变量数学函数 198

技巧121 创建半透明的图表系列 199

技巧122 将图表保存为图形文件 201

方法1：将图表粘贴到图形程序中 201

方法2：保存为html文件 201

方法3：使用vba宏 201

技巧123 使各图表大小一致 203

技巧124 在一个图表工作表上显示多个图表 205

技巧125 冻结图表 207

将图表转换为图片 207

将区域引用转换为数组 207

技巧126 给工作表添加水印 209

技巧127 改变单元格批注的形状 210

技巧128 在单元格批注中插入图形 211

第 7 部分 数据分析及列表 213

技巧129 使用excel 2003中的列表功能 215

创建指定列表 215

在指定列表中添加行或列 215

在指定列表中添加汇总公式 216

技巧130 对超过3列的数据排序 218

技巧131 使用带有自动筛选的自定义视图 219

创建命名视图 219

在工具栏上添加“视图管理器”控件 219

技巧132 把高级筛选的结果放到另一个工作表上 221

技巧133 利用条件格式比较两个区域 222

技巧134 使列表随机排列 224

技巧135 填补报表中的空隙 226

技巧136 从汇总表创建列表 228

技巧137 使用条件格式找到重复内容 230

技巧138 防止在一个区域内插入行或列 232

excel 2002及更高版本 232

excel 2000及更低版本 232

技巧139 快速创建频度表 233

技巧140 控制数据透视表内对单元格的引用 235

技巧141 将数据透视表内的项按日期进行组合 236

技巧142 隐藏数据透视图内的字段按钮 238

第 8 部分 与文件有关的操作 241

技巧143 将文本文件导入到工作表区域中 243

技巧144 从网页上获取数据 245

粘贴静态信息 245

粘贴可刷新信息 245

直接打开网页 247

技巧145 显示工作簿的完整路径 248

技巧146 保存工作簿的预览图片 249

技巧147 使用文档属性 251

技巧148 了解是谁最后打开文件的 253

技巧149 关闭文件时“全否”按钮在哪里 255

技巧150 获取文件名列表 256

技巧151 了解excel的密码 258

技巧152 使用工作区文件 259

技巧153 减小工作簿的大小 260

第 9 部分 打印 261

技巧154 控制需要打印的内容 263

打印所有工作表 263

打印特定工作表 263

设置工作表的打印区域 263

打印特定页面 263

技巧155 在打印输出里显示重复的行或列 264

技巧156 在一页上打印不连续的区域 265

准备好“照相机”工具 265

用照相机拍照 265

技巧157 防止打印不必要的对象 267

技巧158 编制页码的技巧 268

基本的页码编制 268

改变起始页码 268

技巧159 预览分页符 270

技巧160 添加及删除分页符 272

根据个人需要手工加入分页符 272

取消手工添加的分页符 272

技巧161 打印成pdf文档 273

技巧162 避免打印特定行 274

技巧163 让打印输出适合一页的大小 276

技巧164 打印公式 278

在“公式视图”模式下打印 278

用宏来打印一个公式列表 278

技巧165 跨工作表复制页面设置 280

技巧166 使用针对打印的自定义视图 281

第 10 部分 自定义工具栏和菜单 283

技巧167 查找多功能工具栏按钮 285

技巧168 查找隐藏的菜单命令 286

技巧169 自定义菜单和工具栏 287

技巧170 创建自定义工具栏 288

添加第一个按钮 288

添加更多按钮 288

保存自定义工具栏 289

技巧171 控制弹出工具栏 290

技巧172 把工具栏附加到工作表里 291

技巧173 备份自定义工具栏和菜单 292

找到*.xlb文件 292

备份*.xlb文件 292

删除工具栏和菜单自定义设置 292

第 11 部分 错误的查找、修正及预防 293

技巧174 使用excel的错误检查功能 295

技巧175 识别公式单元格 297

使用“定位条件” 297

使用“条件格式” 297

技巧176 处理浮点数问题 299

技巧177 创建单元格及区域名称表 300

技巧178 以图形方式查看名称 301

技巧179 找到虚链接 302

技巧180 了解显示值与实际值的区别 303

技巧181 追踪单元格之间的关系 304

识别引用单元格 304

识别从属单元格 305

第 12 部分 vba和宏的基础知识 307

技巧182 了解宏和vba 309

什么是宏 309

宏能做什么 309

技巧183 录制宏 310

创建宏 310

检查宏 310

测试宏 311

技巧184 了解与宏有关的安全性问题 312

技巧185 使用个人宏工作簿 314

技巧186 了解函数过程及子过程 315

vba子过程 315

vba函数过程 315

技巧187 显示弹出信息 317

显示时间 317

提出问题 318

合并按钮和图标 318

技巧188 从用户处获得信息 319

vba的inputbox函数 319

inputbox方法 319

技巧189 打开工作簿时执行宏 321

通过名字问候用户 321

记录工作簿打开的次数 322

创建使用日志 322

技巧190 创建简单的工作表函数 323

返回用户名 323

单元格里有公式吗 323

返回工作表名称 323

返回工作簿名称 323

将字符串反转 324

抽取一个字符串中的第n个部分 324

技巧191 让excel“开口说话” 325

技巧192 了解自定义函数的局限性 326

技巧193 通过宏执行菜单项 327

技巧194 将自定义函数存储在加载宏中 328

技巧195 显示弹出式链接日历 329

技巧196 使用加载宏 331

excel内置的加载宏 331

安装加载宏 331

寻找加载宏 331

第 13 部分 单位换算和数学运算 333

技巧197 度量单位之间的换算 335

使用convert函数 335

在不使用convert函数的情况下转换单位 335

技巧198 温度单位换算 339

技巧199 处理直角三角形的相关问题 340

技巧200 计算面积、表面积、周长和体积 342

计算正方形的面积和周长 342

计算矩形的面积和周长 342

计算圆的面积和周长 342

计算梯形的面积 342

计算三角形的面积 343

计算球体的表面积和体积 343

计算正立方体的表面积和体积 343

计算圆锥体的表面积和体积 343

计算圆柱体的体积 343

计算棱锥体的体积 344

技巧201 联立方程求解 345

技巧202 生成惟一的随机整数 347

技巧203 生成随机数 349

技巧204 计算根和余数 350

计算根 350

计算余数 350

技巧205 计算条件平均值 351

第 14 部分 excel信息资源 353

技巧206 使用excel的帮助系统 355

技巧207 通过互联网搜索帮助信息 356

技巧208 使用excel新闻组 357

技巧209 浏览与excel有关的网站 358

allen wyatt的excel tips网站 358

david mcritchie的excel pages网站 358

jon peltier的excel page网站 358

pearson software consulting网站 358

the spreadsheet page网站 358

stephen bullen的excel page网站 358