知乎office技能总结_office使用技巧总结

知乎office技能总结由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“office使用技巧总结”。

===============排版篇==================

 给他人发送excel前，请尽量将光标定位在需要他人首先阅览的位置，例如Home位置（A1），例如结论sheet，长表尽量将位置定位到最顶端    有必要的时候请冻结首行，没必要但可追究的内容，可以隐藏处理 行标题、列标题加粗，适当处理文字颜色、填充颜色，利人利己

占用空间比较小的表格，可以放置在左上角，但留空A列和1行，并给表格加上合适的框线，观感很不错哦~   同类型数据的行高、列宽、字体、字号，求你尽量一致，非要逼死强迫症吗！

定义好比较标准的格式，例如百分比预留几位小数，手机号的列宽设置足够，时间显示尽量本土化... 不要设置其他电脑没有的字体，除非这个表格就在这一台电脑使用...===============操作篇==================

     Alt+Enter在表格内换行，楼上有提到

Ctrl+Shift+上/下，选择该列所有数据，当然加上左右可选择多列 Ctrl+上/下，跳至表格最下方

Ctrl+C/V，不仅仅复制表格内容，也可以复制格式和公式！

Ctrl + Shift + 直接调整字体大小，不要上去一个个尝试啦~（02/11纠正：此快捷键为word上使用，excel还真没有快捷键调整字体大小...好奇怪...） Ctrl+F/H的查找、替换，点击“选项”，可以替换某种格式等等，另一片天地有木有！

 F4，对，你没看错，就是F4！重复上一步操作，比如，插入行、设置格式等等频繁的操作，F4简直逆天！ „（分号后面那个）比如输入网址的时候，一般输入完会自动变为超链接，在网址前输入‟就解决咯

 复制，选择性粘贴里面有几个非常好用的——仅值，转置（个人推荐用transpose公式）



===============公式篇==================    if、countif、sumif、countifs、sumifs，这几个一起学，必学算是~ 定位类型的函数：MID、SEARCH、LEN、LEFT、RIGHT一起学吧，简单但异常实用 四舍五入个人偏好用round函数，举个简单例子，一列数据，2.04、2.03并求和，显示保留1位小数，你会在界面上看到2.0、2.0，求和却是4.1，表格打印出来会比较让人难理解。    Vlookup函数，这个不多说了，神器

text，例如19880110 text（A1，“0-00-00”)，转为1988-01-10，用法很多 weekday，让你做时间计划表什么的时候，把日期转为“星期X”

column（目标单元格），返回目标单元格所在列数，有时候真的很好用...还有 @黄老邪推荐的columns~   transpose（目标区域），神奇的转置，把行变成列，把列变成行...&，可在目标单元格后面增加某些字符，偶尔用（我这种强迫患者用的是concatenate公式，我特么有病！）公式里面切换绝对引用，直接点选目标，按F4轮流切换，例如A1，$A$1，$A1，A$1

  数组，虽然复杂，但是有的公式配上数组简直爽爆

多百度，例如曾经碰到一个难题，把X分X秒，转为X秒，例如172分52秒，百度半天得到的公式：=IF(IFERROR(FIND(“分”, $E2), 0)> 0, LEFT($E2, FIND(“分”, $E2)FIND(“分”, $E2)1)* 1）度娘很厉害的

===============图表篇================

 不同的场景请用不同的图，转个非常精髓的图：

 数据透析表、数据透析图，嗯嗯，推荐的人太多了... 图表设计——布局，灵活运用好多类数据时的“次坐标轴”

 选择数据——右键——更改图标类型，灵活在一张表上结合起来柱状图和折线图



===============技巧篇================= SmartArt也是一大神器，我终于不用在Ai或者PPT上作图再粘过来了... 数据——分列，将列内的数据拆分成多列，比如“XXX省XXX市”，拆成省、市两列，“XX小时XX分钟”拆成时、分两列，可以按照宽度、文本、标点等作为界定进行拆分，非常多的场景会使用到，请优先学会... 如果你不是靠excel吃饭，请不用那么geek，而是学会excel的逻辑——配合简单的公式、排序、替换、if等全局操作能得出的结果，不一定非要用一个长公式然后下拉，举例： 如何将无规律的一列上下翻转？

——创建一列，标上1、2、3……，下拉，以该列为主排序，改升序为降序，扩展目标列，得到结果，之后可以删掉创建的辅助排序列

如何目标区域的每一行数据下面插入一条空行？

——创建一列，标上1、2、3……，下拉，下面空白行标上1.5、2.5、3.5……下拉，同理排序~Tada~   条件格式——突出显示单元格规则，里面的“重复值”，在实时录入和检查标记时很实用 在条件允许的情况下，升级到office 2013吧，excel 2013比2010好到爆啊！比如新增的sumifs、averageifs等多条件if，比如选择一个区域，右下角小标“快速分析”自动生成数据条、色阶、柱形图、汇总图、透视表、折线图等等啊，秒中出啊有木有！（诶，好像哪里不对的样子）

==================其它篇====================

不会写宏没关系，要懂得怎么使用别人的宏（自行百度“excel宏大全”吧~），怎么保存xlsm，怎么录制宏。当你把机械化的一套操作通过录制宏实现，并用xlsm配合auto_open自动操作，眼看表格自动化操作，在两秒内给你返回原来每天固定要做十几分钟的数据分析结果时，那个鸡皮疙瘩（蔡健雅腔调...）  

✎✎✎✎✎✎✎✎✎ 出题的分割线必须华丽✎✎✎✎✎✎✎✎✎

有时间推荐泡泡excel的论坛，excelhome什么的，神人太多了....非常推荐给初学者、中阶以及“中介”的视频：文库课程，哪怕前几个视频，看标题简单到爆，但中间真的有很多操作上的亮点不为普通人自学所知~ excel满足不了你，又懂编程，想秀逼格的，请右转百度 SPSS ==================其它篇==================== 定义名称：

在公式引用的时候 A1 法是最常引用的，形如：

=sum(A1:A10)

公式下拉的时候某些变量不用更改相对索引，但是 Excel 会自动的更改相对索引，这时候使用 F4 可以固定维度

=$A$2+B2 这样即使下拉，也只有B2会变化。PS：连续按 F4 会切换锁定的维度 还有一种更便捷的方法就是定义变量 在单元格上右键选择自定义名称，如图：

Excel 会智能的识别出目标单元格的名称，当然，如果冲突还需要更改，可以写入备注

当你自定义名称之后，再次引用这个单元格的时候只需要输入名称就可以了，在你输入名称的时候你所添加的备注也会实时出现，甚至没有拼写完你都可以使用 Tab 自动补齐：

数组也可以定义为名称，感觉应该和 Range 对象一样

VBA 有时需要引用表格内容，不论是 A1 法还是用 Cells 引用，一旦单元格出现增删行列，VBA引用的目标就会发生变化。这使得后期维护难度增大，使用名称可以避免这个问题，增删行列的时候 Excel 会自动更改。

需要注意的是在VBA中使用名称需要用中括号括起来。这时候自定义名称相当于一个全局变量(真正地全局……对整个 workbook 全局)，可以引用，甚至可以赋值，对比之下 A1 法和 Cells 则相当不美观也不利于阅读。

随手拷贝了几行，一个简单的例子。[PowerLevel] 和 [BULevel] 是数组，你们感受一下：

RowNum = WorksheetFunction.Match(P, [PowerLevel],-1)+ 4 ColumnNum = WorksheetFunction.Match([BU], [BULevel], 1)+ 2 [KME] = PthAvg(T,Dt)我定义的名称，你们感受一下：

函数以及自定义函数：

Excel 的函数真的很牛x，第一的答案写的很详尽了，不表。输入的时候即时提醒做的很赞~ 有时候需要一些特定的计算，如果全部用公式的话略显繁琐，并且公式会很长很长很长很长…… 我曾经就写过很长很长很长很长很长的公式…(插一句，Excel 公式输入的地方是可以拉来的，结合 Alt + Enter 可以实现换行和缩进等等，我来上个图……）

自定义函数方便维护，花样繁多，玩大的还可以去直接导入其他语言开发的库 直接上列子……

Function Rho(P1 As Double, P0 As Double)As Double '根据功率变化计算反应性

Rho = [beta_eff] / [lambda] / 60 * Log(P1 / P0)End Function

然后你就可以愉快的使用了

唯一的遗憾是无法在 Excel 界面中即时提示……在 VBE 里面可以即时提示……顺便打车求解决方法……好像说注册 DLL 欺骗 Excel 可以做到……求少折腾的方法 VBA：

VBA我就不多说了，能干的事情太多了，虽然速度有点慢，但是合理的优化，关闭刷新，处理日常的工作还是可以的。

不会写代码刚开始学可以直接录，录完了好好读一读，删掉无用的就好。经常看看自带的帮助文档，干货很多。不会了就慢慢地 google，百度……

界面：

严格说起来界面应该属于 VBA，这部分和 VB 类似，不过貌似不支持控件数组，当然旁门左道也能实现控件数组。在 Workbook_Open 事件里面写上：

Application.Visible = False xxx窗体.show 让 Excel 伪装成一个程序……完全看不到 Excel 的界面~ PS：请不要看不起我们这些拖控件的……

引用与库函数：

这里的引用特质 VBA 的工程引用，这时候可以绑定一些封装好的库文件，个人偏爱 MatrixVB，最早是我用来增强 VB 的运算的，后来玩 Excel 发现也能在 VBA 里跑，不过参考文档很屎，老古董了。

大型计算忍不了 VBA 的渣速度的也可以用其他语言做库然后让Excel来用，不过我用的不是很好，尝试过用 FORTRAN 写，只局限于实验阶段……

自定义界面：

如果自己用，直接在选项里面添加就可以了，如果是给别人用，可以做成含有 Ribbon 界面的加载项，一般后缀是 xla 和 xlam。

Ribbon 可以指定快捷键，指定 Screen Tip 和 Super Tip，XML 语言就好，喜欢折腾的不要错过。

半途而废的东西：在 Excel 中打开其他软件……用的是 Shell……做不下去了，因为完全没需求，从来都单手盲开任何常用软件网站……要问我为何这么屌，因为我有 AutoHotKey 呀

还有高大上的就不跟你们秀了…………

其他：

下面这些东西就有点奇葩了，FileDialog，Shell，API 这就属于瞎折腾了，不表~

另外两个：

Excel 绘制 李萨茹图

http://v.youku.com/

Excel 随机游动小球

http://v.youku.com/

==================技巧篇====================

1、vlookup，尤其是不同工作表之间的查询；

2、ctrl+D 选中连续单元格后填充第一行的内容；

3、要插入n行空白列/行，先在表格空白处选中n行/列，复制，之后在要插入的地方右键，选择插入复制的行/列；

4、选中某行，ctrl+shift+↓，选中某行以下所有有数据的单元格。

5、忘了就点F1，哪里不会点哪里。

【 自定义序列 】工具——选项——自定义序列。

一、如果是简单的自定义序列

如果要使用序列第一组、第二组、第三组、第三组、第四组、第五组、第六组、第七组、第八组、第九组、第十组，就打开“自定义序列”选项卡，选择“自定义序列”中的“新序列”，然后在右侧输入第一组、第二组……第九组、第十组，然后点击右侧“导入”，最后确定。

二、比较复杂的自定义序列

如果要频繁使用A—1到A—100、B — 1到B — 100、C — 1到C — 100等编号，怎么办？ 第一步，做好自定义序列的准备。（以A—1到A—100为例）

1、A列前100行全部填充A，B列前100行全部填充 —，C列前100行填充1到100（使用“以序列方式填充”）。

2、使用运算符“&”（数字7的上面那个），把D列前100行自动填充为A—1到A—100。方法是，在D1单元格内填入”=A1&B1&C1“(引号不输入)，然后敲回车，D1单元格就显示” A—1 “。然后选择拖动D1单元格，把D列分别填充为” A—1到A—100 “。

3、去除D列的公式，使之成为纯文本。方法是，复制D列，然后选择“选择性粘贴“，“粘贴”中选择“数值”，“运算”中选择“无”，然后确定。

第二步，导入自定义序列。

打开工具——选项——自定义序列（下面第一图），点击“导入”前面的按钮（下图中红框），在弹出来的框中（下面第二图）选择D列，然后关闭该选择框，然后点击下面第一图右下角“导入”，结果如下

面

第三

图，然

后

确

定。

第三步，试验效果。

自定义序列完成，A—1到A—100就可以用”自动填充选项“填充了。

一.基本功能

1.查看文件路径:点最左上角圈圈→准备→属性→看右上角.2.简体与繁体转换:审阅→中文简繁体转换.3.加密文档: 点最左上角圈圈→加密文档

4.快速定位单元格: Ctrl+Home返回A1单元格,Ctrl+End返回最右下角单元格Ctrl+→表格最右单元格（其他方向同上）.5.快速填充空白单元格: 选中表格→按F5定位→在定位条件中选择空值→在输入函数栏输入0→按下ctrl+Enter，这样空值就全变成0了~ 6.重复插入列与行：右键第一行的数字1点插入→移到任意一行点F4就可以快速插入一行。（插入列同上）

7.对单元格区域设置密码: 审阅→点最右的允许用户编辑区域→点新建→输入标题名称，要加密的单元格区域，加密密码→确认→再次输入密码。再点审阅→保护工作表→

在这几条前面打钩→输入取消工作表保护时使用的密码（可以不同于单元格保护的密码），确认密码。这样可以实现必须有单元格密码才能改单元格数据了。

8.删除所有空行: 选中区域

→点数据中的筛选 →点1月的下拉箭头选择空白，对2月和3月进行相同的操作，出现如图

接着选中A4：C8区域右键删除行，再点筛选空行就没有了~~~ 9.快速选择最合适的列宽：如图，先选中AB列，再双击

中间那条白线，接着系统自动生成最合适列宽10.冻结窗口：视图→冻结首行或首列或单元格。

11.关闭超链接转换功能：→Excel选项→校对→自动更正选项→键入时自动套用格式→把前面的勾去掉。12.做汇总表: 让表2中A1的内容体现在表1中B2的位置。很简单，只需在B2处输入等号，再切换到表2点击A1单元格就行了~~ 二 数据分析: 排序

1.按特定的规则排序: 如对图中

按董事长,总经理,秘书,助理依次排序.首先

再点Excel选项,在常用里点编辑自定义序列,点添加.接着选中A列,点数据再点排序出现如图

在次序里选择自定义序列,找到我们刚才做的那个序列,点确定.输出结果如图2.按颜色排序: 对表中按绿色,红色,黄色排序.输入所示序列,.首先,选中A列,点数据再点排序,在排序依据里面点按颜色排序,再依次添加条件,如图所示

最后结果如图3.数字和字母混合排序

.如图，Excel的默认排序是先按A-Z排序，再按后面的数值大小排序，但实际上我们想要的结果往往是A7排在首位，所以我们需要处理格式。在B1单元格中输入公式

点回车拖到B5，如图所示行排序就可以了~ 4.数值核对。对2表进行数值核对

再对B列进如图所示是否一致。首先点数据，再点合并运算，接着选中单元格区域如图

核对A与B列数值

点确定，出现如图格如输入=K2=L2 并下拉，出现

再在M2单元

就可以知道数值不同处了。

5.对数组公式的应用。利用数组公式对中A1到A5>0的数据求和。

在A6单元格输入=sum((A1:A5>0)*A1:A5)，再按Ctrl+Shift+Enter,即可得出结果5。

本公式的内涵（A1：A5>0）输出的结果是（FALSE,FALSE,TURE,TURE,FALSE）A1;A5输出的是（-1,0,2,3-5），FALSE=0 ture=1，所以这2组数相乘得到的结果是（00,2,3,0）再对（0,0,2,3,0）求和得到5.三 函数篇: 1.对英文进行大小写装换： 大写=UPPER（）小写=LOWER（）首字母大写=proper（）

2.生成可换行文本: 工作中我们会遇到要将

放在同一个单元格内，上面是张三，下面是邮箱地址。首先在C1单元格内输入=A2&CHAR(10)&B2 再点开始，点自动换行，可以生成码。

所示样式。公式中CHAR(10)为换行符的代3.替换单元格内容：这里我们可以用2个函数Substitute和replace函数。例子1：将A1单元格内容变为B1内容

可以用函数=SUBSTITUTE(A1,1,“一”)或者=REPLACE(A1,1,1,“一”)。例子2.这2个函数还有别的用法。如substitute将

A1变为B1可输入公式=SUBSTITUTE(A1,1,“一”,1)。用REPLACE函数可实现下图A1到B1的转换，在C1单元格输入公式=REPLACE(A1,5,0,“第一名”)即可实现。4.计算字符出现的次数：

如计算Excel master中”e”的字符数。可输入公式=Len(A1)-len(substitute(A1,”e”,””))可以得到结果3 计算包含某字符的单元格数：如计算在A9中输入=countif(A1:A8,”*助理*”)即可得到结果3。

表中助理的个数。

5.利用文本格式对数据进行格式转换。例子：将0.35转为35%。在A1中输入0.35，在B1中输入公式=TEXT(A1,“0%”)即可完成转换。6.如何计算星期几.单元格输入==TEXT(A1,“aaaa”)即可.7.计算非空单元格,和空白单元格.分别用函数counta和countblank即可实现。

在B1

UltraEdit。

举个例子，利用UltraEdit的列编辑模式，可以同时向N行数据相同位置输入相同的内容，配合Excel的分列操作，可以随心所欲地同时控制每行数据相同位置“应该出现什么，不应该出现什么”