详细讲解Excel电子表格技巧

这样我们在sheet2的A1单元格中输入公式 “=IF(MOD(ROW(),3)=0,”“,IF(MOD(ROW(),3)=1,Sheet1!A $1,INDEX(Sheet1!$A:$N,INT((ROW()+4)/3),COLUMN())))”。 确认后选择A1单元格，把鼠标放在A1单元格的右下角，鼠标 变成“+”时，向右拖动鼠标自动填充至O列，这样工资条中 的第一行就出来了。选定A1：O1，把鼠标放在O1单元格的 右下角，鼠标再次变成“+”时，向下拖动鼠标自动填充到数 据的最后一行，工资条就全部制作完成了。
公式以等号“=”开头，Excel有很多可利用的公式。如 果想在工作簿中看到的是公式，而不是具体的值，则在 “工具一选项一视图”选项卡中，选中“窗口选项”下的 “公式”复选框。注意，这样在打印的时候打印的也是公 式。
11. 输入相同数据的两种情况
同在多个单元格中输入相同的数据。 方法：选择要输入数据的单元格，如果不连续可以使用Ctrl键 进行选择，然后输入数据，再按下Ctrl+Enter键就可以了。 同时在多张工作表中输入或编辑相同的数据选中多个工作表后 后在相应单元格中输入数据，选择多个工作表的方法也是使用 Ctrl或Shift键。
Word 里：Alt+Shift+D：当前日期 Alt+Shift+T：当前时间
数值的输入
负数输入：直接输入负号和数字，或用圆括号输入。 分数输入：先输入0和一个空格，再输入分数。直接输入分数 则认为是日期。 输入据用自动设置小数点或尾随零的数字。在“工具一选项
一 编辑”选项卡中设置自动小数点
公式的输入
1. 快速填入相同内容
选中多个单元格后，输入字符，然后按Ctrl+Enter组合 键，即可在选中的每个单元格中填入上述相同的字符。
2. 快速复制上一单元格
选中下面的单元格，按Ctrl+ '（'为西文的单引号） 组合键，即可将上一单元格的内容快速复制下来。
3. 求字符串中某字符出现的次数
例如：求A1单元格中字符“a”出现的次数： =LEN(A1)-LEN(SUBSTITUTE(A1,“a” ,“ ”))
18. 绘制斜线表头 在Excel经常需要绘制斜线表头，简单的 两分式的表头可以先把表格绘制好，并在需 要绘制表头的单元格中单击，然后单击格式 菜单下的单元格命令项，在单元格格式对话 框中点开边框选项卡，然后根据绘制的需要 单击外边框和下面的边框线。
19. Excel做成绩表时自动实现同分同名次
绝对地址的形式为：$A$1,$B$1。 有些公式引用单元格是用混合型地址。
日期和时间的输入
10. 数据的输入
当前系统日期的输入：ctrl + ；
当前系统时间的输入：ctrl + shift +；
12小时制输入时间，在时间数字后空一格，并键入
字母a或p，例如，8：00 a
改变日期格式在“格式-单元格”中修改
LEN(text)：返回文本字符串的个数 SUBSTITUTE（text,old_text,new_text,instance_num）: 将字符串中的部分字符串以新字符串替换。
4. 如何在EXCEL中设置单元格编辑权限 （保护部分单元格）
1. 先选定所有单元格，点“格式”→“单元格”→“保护”， 取消“锁定”前面的“√”。
4. 三个条件设定好后，点“确定”即可。
6. EXCEL中如何控制每列数据的长度并避 免重复录入
1. 用数据有效性定义数据长度。
用鼠标选定你要输入的数据范围，点“数据” 一“有效性”一“设置”，“有效性条件” 设成“允许”“文本长度”“等于”“5” （具体条件可根据你的需要改变）。还可以 定义一些提示信息、出错警告信息和是否打 开中文输入法等，定义好后点“确定”。
A1<0时，C1显示黄色
方法如下： 1. 单击C1单元格，点“格式”→“条件格式”，
条件1设为： 公式=A1>1
点“格式” →“字体”→“颜色”，点击红色后点“确定”。
2. 条件2设为：公式=AND（A1>0,A1<1） 点“格式” → “字体”→“颜色”，点击绿色后点“确定”
3. 条件3设为：公式=A1<0 点“格式”→“字体”→“颜色”，点击黄色后点“确 定”。
成绩相同，名次不同
1. 使用排位函数RANK
在L2单元格中输入函数“=RANK(K2,K$2:K$1001,0)”， 回车后L2单元格中会出现名次“1”，然后选定L2，拖动其 填充柄向下填充即可自动实现。
该函数的功能是返回一个数字(上述函数中的K2，此处采 用相对应用，填充时随行的变化而变化)在数字列表(上述函数 中的K$2:K$1001，此处采用绝对引用，填充时不发生变化)中 的排位。
3. 采用IF语句加COUNT函数
该操作与上述两种方法不同，必须先将总分按照降 序排列。然后将第一名的名次“1”输入，再在L3单元格中 输入函数“=IF(K3=K2,L2,COUNT($K$2:K3))”，确定后 向下自动填充也可实现同分同名次的操作。
COUNT函数的功能是返回指定区域中数字单元格 的个数。因为K列全是数字单元格，所以该总分处在第 几位上名次就是几。上述语句的意思就是如果当前总分 和上一个总分相同，则输入上一个总分的名次，否则执 行COUNT语句，统计个数。
7. 去掉默认的表格线
单击“工具”菜单中的“选项”，再单击对话框中的 “视图”，找到“网格线”，使之失效（将左边的
“√”去掉）。
8. 增加工作簿的页数
单击“插入”菜单下的“工作表”，即可在底部看到一个新 名称
的工作表。一个工作簿最多可以有255张工作表。
9. 相对地址、绝对地址、混合地址
相对地址的形式是地址直接用列号行号组成。
2. 用条件格式避免重复。 选定A列，点“格式”一“条件格式”，将条件设成 “公式=COUNTIF($A：$A,$A1)>1”,点“格式”一“字体”一“颜色”，选定红色后点两次“确定”。
COUNTIF(range,criteria)：计算某个区域中满足给定条件的单元 格数目。
这样设定好后，输入数据如果长度不对会有提示，如 果数据重复字体将会变成红色。
2. 再选定你要保护的单元格，点“格式”→“单元 格”→“保护”，在“锁定”前面打上“√”。
3. 点“工具”→“保护”→“保护工作表”，输入两次密码， 点两次“确定”即可。
5. Excel中当某一单元格符合特定条件，如何 在另一单元格显示特定的颜色
比如：
A1>1时，C1显示红色
0<A1<1时，C1显示绿色
Excel的使用技巧
1. 快速填入相同的内容 2. 快速复制上一单元格 3. 求字符串中某字符出现的次数 4. 如何在EXCEL中设置单元格编辑权限（保护部分单元格） 5. Excel中当某一单元格符合特定条件，如何在另一单元格显
示特定的颜色 6. EXCEL中如何控制每列数据的长度并避免重复录入 7. 去掉默认的表格线 8. 增加工作簿的页数
20. 按Shift键 把Excel单元格区域转换为图片
Shift键在Excel里有这样的妙处：在按下Shift键的同 时点击“编辑”菜单，原来的复制和粘贴选项就会变成 “复制图片”和“粘贴图片”。利用这一功能，我们就 能把选定的单元格区域方便地转换为图片。
具体操作方法如下： 首先选中需要复制成图片的单元格区域，然后 按住Shift键，依次选择“编辑→复制图片” 命令，接着弹出“复制图片”窗口，选择 “图片”单选项后点击“确定”按钮。这时 选定的表格区域就已经被复制成图片了。
12. 数据的行列对调
选取要对调的数据范围，复制。 在目的单元格上单击鼠标右键，选择“选择性粘贴”，在对
话 框中选择“转置”，确定。
13. 跨表操作数据
设有名称为sheet1、sheet2和sheet3的三张工作表，现要 用sheet1的D8单元格的内容乘以40%，再加上sheet2的B8单 元格内容乘以60%作为sheet3的A8单元格的内容。则应该在 sheet3的A8单元格输入以下算式：=sheet1！D8*40%+sheet2！ B8*60%。
在用Excel统计成绩时，我们一般会根据成绩高低进行排序， 然后按序列自动填充出名次。这种方法得出的名次与总分没有 关联，即使成绩相同，也会填充出不同的名次。如果数据较少， 我们可以采用手动的方法将成绩相同的人员改成相同的名次， 但数据较多时就很麻烦了。
经过实践，以下三种方法可以自动实现同分同名次的操 作。假设有1000名考生，字段位置如下图所示。
9. 相对地址、绝对地址、混合地址 10. 数据的输入 11. 输入相同数据的两种情况 12. 数据的行列对调 13. 跨表操作数据 14. 两次选定单元格 15. 两列数据巧合并 16. 用快捷键设置单元格文本 17. 自动调整为合适的列宽 18. 绘制斜线表头
19. Excel做成绩表时自动实现同分同名次 20. 按Shift键 把Excel单元格区域转换为图片 21. 制作漂亮的工资条 22. 图表编辑操作
在EXCEL表格中，如果有两列数据需要合并起来，如 果是数字的话，一般情况下可以使用四则运算的方法进行计 算；但是如果有一列是文字，另一列是数字（或者文字）， 那么可以先在这两列后面的一个空列中单击，并输入=号， 然后输入第一列的第一个单元格的相对引用名，输入&号， 然后再输入第二个列单元格的相对引用名，最后按一下回车 键即可。然后，鼠标移动到这个单元格的拖动柄上，向下拖 动知道需要合并的数据最后一行。
16. 用快捷键设置单元格文本
在Excel中，熟练使用快捷键可以帮助我们设置单元格文 本。在选择好要设置格式的单元格后，如果要设置为粗体，可 以按Ctrl + B；如果要设置为斜体，可以按Ctrl + I；如果要设 置为下划线，可以按Ctrl + U。