excel工作表制作实例《教学仪器流水帐》 分享电子表格经验技巧（有图有真像）
excel工作表制作实例《教学仪器流水帐》 分享电子表格经验技巧
俗话说的好，干一行爱一行，在管理实验器材总账时，发现纸质账目费力费时，不好管理，于是决定做一个excel电子表格减轻负担，让excel函数担当计算器的重任。没有任何excel基础，一切从0开始，制作表格过程遇到问题就通过网络搜索获得帮助，好记心不如烂笔头，故在此把表格制作过程中收集到的技巧及心得记流水账，一是方便用得着的亲们提供些建议，二也是方便今后自己用得上的时候再次查询。
小技巧分享先睹为快（按Ctrl+单击可快速到达阅读）
小技巧分享：Excel2010如何把行高和列宽单位设置成厘米,精确设置表格？... 4
小技巧分享：如何让excel单元格中数字前面加上0？... 6
小技巧分享：Excel 如何让单元格自动换行显示全部内容？... 7
小技巧分享：Excel如何防止公式被误删和修改？... 11
小技巧分享：excel如何设置数据有效性，使单元格只允许输入规定的整数？... 14
小技巧分享：excel如何设置单元格下拉菜单选项，方便选择输入？... 16
小技巧分享：excel如何保证“数据有效性”在“复制、粘贴”情况下有效？... 16
小技巧分享：excel如何巧用填充柄拖拉法快速将某行的数据复制到下侧的大量行中？... 18
小技巧分享：excel公式函数中如何巧用“$”引用单元格？... 18
小技巧分享：excel中如何隐藏行或列和取消隐藏行或列？... 19
小技巧：怎样在滚动excel工作表时始终显示数据表格的表头？... 20
小技巧分享：在打印Excell表格前，每个页面的表头你是怎么加上去的？是逐页插入表头吗？
小技巧分享：excel如何每行内容上都插入一行（或多行）内容相同的表头（如中考分数条、工资条等）？
小技巧分享：excel如何实现在表头任一位置设置页码？... 25
小技巧分享：excel工作表忘记了保护密码，能否不用破解软件快速的破解密码？... 27
纸质表格
电子表格
基本功能：1、序号能根据公式自动填充；2、年月日能自动检测输入是否正确，如月输入15会提醒输入错误；3、凭证字号及统一编号不会清除数字前面的“0”；4、单价能够自动保留2位小数；5、收入、减少、累计总金额能根据公式自动计算填充。
使用的版本是excel2010，以下按本人制作的先后顺序作相应介绍：
一、页面设置
新建电子簿后第一件事就是按自己的需要设置好纸张大小及页边距。否则制作好表格后打印出来的效果并不一定能如我们所愿，再重新调整是一件很痛苦的事。
小技巧分享：excel如何自定义纸张？
Excel不能像word一样能够自定义纸张，对于需要量体裁衣套打表格可是个头痛的事。网上搜索到的方法：在系统里定义：开始－控制面板—硬件和声音－设备和打印机－打印服务器属性，勾选“创建新表单”，然后在“表单名称”里输入一个名称，这个名称以容易识别为好。然后修改下面的纸张尺寸。注意，尺寸不能超出打印机可打印的范围。确定。以后，就可在EXCEL中象选择其他纸张一样，选择你自己定义的纸张了。可能是人品差，设置好了，但在excel里还是没得我设置的纸张供选择。后来想想如今的办公用纸都是以A4为主，干脆就设置为A4纸算了。
后来我也想了个笨办法，通过调整页过距来实现自定义纸张。比如要设置一张16K（195mm*270mm），上下左右页边距均为2cm，可以在excel页面设置里选择A4（210mm*29.7mm）纸，页边距这样设置：上2cm，下4.7cm（A4比16k长2.7cm），左2cm，右3.5cm（A4比16k宽1.5cm）。如下图所示。
二、设置行高列宽
按自己的需要设置，注意通过打印预览检查不要超出打印范围。Excel中的行高默认是以磅做单位的，列宽的默认单位是“标准字符宽度”。而且，如果我们用拉动表格边框线的方式来设置表格的行高或列宽时，还会同时显示出“xx像素”。如果我们的表格需要套打时，行高列宽经常要求以厘米为单位进行精确设置，咋整？用计算器换算？
小技巧分享：Excel2010如何把行高和列宽单位设置成厘米,精确设置表格？
其实换一个视图就可以喽！在视图标签下，切换到【页面布局】视图，并显示标尺。
三、设置单元格格式
选择需要设置格式的单元格——开始（或直接单击右键，下拉菜单里面有个“设置单元格格式”）——格式——下拉菜单有个“设置单元格格式”，在这里可以设置单元格的“数字”、“对齐”、“字体”、“边框”等格式。多试试即可参透其中奥妙。
小技巧分享：如何让excel单元格中数字前面加上0？
excel中输入数字时默认数字前面的数字不带0，即使数字前面带0了，也会自动消失；如学生学号示例，要让数字前面加上0的方法：打开“设置单元格格式”对话框——数字——分类里选择“文本”——确定，这时数字就会自动靠左，转化为文本类型了。
小技巧分享：excel中如何显示小数点后面的0？
单价需要保留2位小数，可是输入5.00后，一点击下一项，只显示5。正确显示“5.00”的方法：打开“设置单元格格式”对话框——数字——分类里选择“数值”——设置小数位数（默认是2位）——确定，这样我们只要输入整数5并会自动变成“5.00”了。
小技巧分享：Excel 如何让单元格自动换行显示全部内容？
在excel表格编制过程中，一个单元格可能放不下我们要输入的汉字，这个时候可以选择自动换行或强制换行。
自动换行：打开“设置单元格格式”对话框——对齐——文本控制——勾选自动换行——确定。
强制换行：单元格在编辑状态下，只要输入一下快捷键Alt+Enter(回车键）即可实现换行。
小技巧分享：怎样使excel文字居中?
输入文本时，当你换行输入其他的文本以后，这个单元格中的文本就会自动靠左，怎样设置其居中呢？
方法：打开“设置单元格格式”对话框——对齐——水平对齐下拉框选择（居中）——垂直对齐下拉框选择（居中）——确定。
小技巧分享：excel如何设置填空型下划线？
做表头的时候，部分文字需要下划线，设置单元格格式——字体——下划线下拉框选择相应下划线，可是没有字符的地方下划线不显示（想打印出下划线在纸上手写填空用）？即使输入文字，下划线也会与文字距离很近，那么我们又该怎么去调节下划线与文字间的距离呢?
其实何必苦苦追求字体下划线呢？换种思路并可迎刃而解——给单元格设置下边框及调整合适的行高即可实现。
四、设置公式
公式来源于网上分享表格上的公式启发，做了一些改进，把总金额累加即可看到图中红框的金额不同之处——下载的表格总金额出现空行后重新计算，而我设计的表格总金额一直累加。
1.在A6单元格输入公式=IF(OR($H6<>"",$J6<>""),COUNT($A$5:$A5)+1,"")，当H6输入了仪器名称或J6输入了数量，就在A6显示相应序号。
2.在M6输入公式=IF($L6=1,$J6*$K6,0)，当L6输入数字1（表示增加），M6显示增加的金额。
3.在N6输入公式=IF($M6>=10000,MID(RIGHTB($M6*100,7),1,1),IF($M6>=1000,"￥",""))，表示在N6显示增加金额的万位，下面的千、百、十位等漂过。
4.在U6输入公式=IF($L6=2,$J6*$K6,0)，当L6输入数字2（表示减少），U6显示减少的金额。
5.在AC6输入公式=$AC5+$M6-$U6（用填充柄复制后要修改为=$M6-$U6），显示累计金额。
6.在AD6输入公式=IF(AND($M6=0,$U6=0),0,$AC6)，当金额没有增加也没有减少时AD6显示0，也就是说这一行不显示累计总金额，否则显示累计总金额。
小技巧分享：Excel如何防止公式被误删和修改？
因为要设置众多公式进行计算，有时会因为误操作或文件共享被他人修改公式导致数据面目全非。其实，利用Excel中保护工作表的相应功能，就可以避免此类错误。思路如下：将表格中重要的使用公式和函数计算的单元格锁定，不允许修改，留下其他单元格进行基本数据的输入，这样就可以保护重要单元格中数据和公式的安全性和稳定性。
方法步骤：
设定可编辑区域：选中允许编辑的单元格（B6至L6、AN6、AO6），打开“设置单元格格式”对话框——保护——取消“锁定”项的选定（此项默认为选中状态）及 “隐藏”选定——确认。
在这个对话框中我们可以看到相关说明：只有在工作表被保护时，锁定单元格或隐藏公式或数据才有效。将来在进行工作表保护后，因为此区域的锁定状态已被取消，可以自由输入数据、或者对它们进行修改。
设定非编辑区域：选中不允许编辑的单元格（A6、M6至AN6）打开“设置单元格格式”对话框——保护——勾选“锁定”和“隐藏”。选中“隐藏”的目的是让单元格中的数据不在公式栏出现，从而避免其过长而遮掩下面的表格内容。
保护工作表：这一步应该放在整个工作表制作完成后进行，放在这里是为了使这个小技巧得到圆满解决。审阅——保护工作表——勾选“保护工作表及锁定的单元格内容”——输入密码（要记住哦）—— “允许此工作表的所有用户进行”中作如图选择。勾选“设置列格式”目的是L列编辑时要显示及打印时要隐藏。
五、设置数据有效性
在EXCEL中输入数据时，我们进行数据有效性的设置，可以给我们节约很多的时间，也可以让别人在我们制作好的表格中输入数据时提高输入的准确性，我们如何进行excel数据有效性的设置呢？
小技巧分享：excel如何设置数据有效性，使单元格只允许输入规定的整数？
1、年份设置如下图：只能输入4位数的年份
2、月份设置如下图：只允许输入1——12的数字
3、日期设置如下图：只允许输入1——31的数字
4、数量的设置：只允许输入>0的数字
小技巧分享：excel如何设置单元格下拉菜单选项，方便选择输入？
收入或减少的设置如下图：设置数据有效性的条件为序列，可以不用往里面输入数据，我们直接点击选择“1”或者“2”即可输入数据
EXCEL“数据有效性”有一个致命的缺点：用“复制、粘贴”方式录入数据时“数据有效性”就会失效，输入的数据就不受控制了。有人可能会说规定下面的人不准复制粘贴、必须逐个输入就行了。这个办法不是不行，但是不保证百分之百行，下面的人要是都这么听话的话管理工作就简单了。而且，不让复制粘贴会影响工作效率，明明有现成的数据为什么不让人家复制、粘贴偏偏要人家逐个输入呢？只要这些数据符合“数据有效性”就行了。这里跟大家分享一下如何处理这个问题。
小技巧分享：excel如何保证“数据有效性”在“复制、粘贴”情况下有效？
1、右击工作表标签“Sheet1”——选择“查看代码”
代码：
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target AsRange)
Dim rng As Range
For Each rng In Target
If Not rng.Validation.Value Then
Application.Undo
MsgBox prompt:="粘贴数据超出可输入范围！", Title:="输入提示"
Exit For
End If
Next
End Sub
六、复制粘贴第6行的格式及数据(字符或公式)
最笨的办法是一行一行的复制粘贴，量少还是可以马虎应付得了，如果要复制个100行，大家可能会想到用拖拉单元格来完成。
选中需要复制的单元格（A6——AO6）然后将鼠标移至该单元格右下角，成“细十字”细状时(这种状态我们称之为“填充柄”)，按住鼠标左键向下拖拉，即可将公式复制到第6行下面的单元格区域中。
如果要复制填充20000行数据，用拖动鼠标进行填充是不是有一种手要抽筋的感觉，而且一不小心就可能会出现错误。
小技巧分享：excel如何巧用填充柄拖拉法快速将某行的数据复制到下侧的大量行中？
1、冻结第7行（下面第八步冻结表头有方法介绍）。
2、再向下拉动右侧的滚动条，让工作表的最下面一行出现在窗口中（如表格要建到第600行,就让第600行显示在窗口中）。
3、选中A6——AO6单元格，仿照前面的操作，用“填充柄”轻轻拖拉至100行，数据即可快速复制完成。
小技巧分享：excel公式函数中如何巧用“$”引用单元格？
我们在用填充柄复制公式时，公式中引用的单元格地址，在复制过程中会智能化地发生相对变化，而有些单元格地址在复制过程中却不发生变化，这都是$惹的祸。
excel公式中的引用，分绝对引用和相对引用。没有“$”符号是相对引用。有“$”符号是绝对引用。具体情况举例说明：
1、相对引用，复制公式时地址跟着发生变化，如C1单元格有公式：=A1+B1
当将公式复制到C2单元格时变为：=A2+B2
当将公式复制到D1单元格时变为：=B1+C1
2、绝对引用，复制公式时地址不会跟着发生变化，如C1单元格有公式：=$A$1+$B$1
当将公式复制到C2单元格时仍为：=$A$1+$B$1
当将公式复制到D1单元格时仍为：=$A$1+$B$1
3、混合引用，复制公式时地址的部分内容跟着发生变化，如C1单元格有公式：=$A1+B$1
当将公式复制到C2单元格时变为：=$A2+B$1
当将公式复制到D1单元格时变为：=$A1+C$1
在应用时要根据具体情况进行选用：如果不管怎样复制，都不希望被引用的单元格发生改变，就把两个$都加上，即行列全固定；如果希望其中一个应该发生改变的，则把$号加在行或列号前就可以了。
七、隐藏不需要打印的列
为了表格功能的实现，有部分列（如：第L、M、U、AC、AD列）需要其存在不能删除，但又不能把这些列打印出来，肿么办？
小技巧分享：excel中如何隐藏行或列和取消隐藏行或列？
隐藏行或列：选择需要隐藏的行或列，右击选择的行与列，在其右菜单中选择隐藏，就可以隐藏选择的行或列。
显示行或列：选择包含隐藏行与列的区域，右击选择区域，在其右菜单中选择取消隐藏，就可以还原显示被隐藏行或列。
八、锁定表头
进行excel数据表格编辑录入工作，我们希望在往下拉页面的时候表头能够固定不动，对于下面很多页面数据查看或者编辑录入有很大的方便。可当我们往下拉动页面，很明显，表头就会消失在视野，怎么让excel表头置顶不消失呢？
小技巧：怎样在滚动excel工作表时始终显示数据表格的表头？
把鼠标单击将要设置不动的那一行（列）的下一行（列）第一个单元格，比如：我要设置第5行不动，那么就把鼠标单击第6行的第一个单元格，即A6——视图——冻结窗口——冻结拆分窗格。就实现了第5行始终是在单元格的上边了。
九、打印表头
在Excel制表的过程中，只设置一个表头，而打印的时候数据行很多，只有打印出来的第一页有表头，第二页开始就只有数据行而没有表头，这样造成查看数据的时候很费劲，看起来也不是那么美观。有什么方法可以将表头固定起来，在打印之后每页都可以看到？这种结果是我们每个人的初愿吧！
小技巧分享：在打印Excell表格前，每个页面的表头你是怎么加上去的？是逐页插入表头吗？
不必，设置步骤：页面布局——打开“页面设置”窗口——点“工作表”选项卡——打印标题——“顶端标题行”输入行号（$1:$5）——确定，进行打印预览，你将会看到每页上面都加入了表头。
固定表头、设置页码效果图
小技巧分享：excel如何每行内容上都插入一行（或多行）内容相同的表头（如中考分数条、工资条等）？
1、分析分数条的制作效果
每个学生的分数条占4行；
第1、5、9、13等行为表头：除4余数为1;
第2、6、10、14等行为学生分数信息：除4余数为2;
第3、7、11、15等行为空行：除4余数为3；
第4、8、12、16等行为空行：能被4整除，余数为0。
2、建立工作表2个
首先建立“成绩明细表”即Sheet1，用于储存学生成绩表信息，也用于平时编辑和汇总。
其次建立“分数条表”即Sheet2，用于引用成绩信息，同时产生成绩表形式之分数条（下面的操作均在此表进行）。
3、输入公式
将公式=CHOOSE(MOD(ROW(),4)+1,"",Sheet1!A$1,OFFSET(Sheet1!A$1,ROW()/4+1,),"")输入到Sheet2中的A1至I40区域的单元格中,步骤如下：
选定填充区域：可以用鼠标选取，在这里使用如上图方法，可以快速选择想选择的区域（如上万行），首先在名称框输入填充区域左上端及右下端的单元格（本例输入A1:I40）后按回车键。
输入公式：在编辑栏输入公式=CHOOSE(MOD(ROW(),4)+1,"",Sheet1!A$1,OFFSET(Sheet1!A$1,ROW()/4+1,),"")后，同时按Ctrl+回车键。
4、设置边框
选择A1:I4单元格，按上图设置好边框——复制——选择A5:I40单元格——粘贴——选择性粘贴（格式）
5、多行表头：把公式修改为=CHOOSE(MOD(ROW(),5)+1,"",Sheet1!A$1, Sheet1!A$2,OFFSET(Sheet1!A$1,ROW()/5+2,),"")
十、设置页码
小技巧分享：excel如何实现在表头任一位置设置页码？
建议使用页眉/页脚的方式来在打印出的每一页的表头中正确显示页码（效果图如上）。以下是具体步骤。
页面布局——打开“页面设置”窗口——点“页眉/页脚”选项卡——点击“自定义页眉”——点击“右”位置——点击第2个按钮插入“页码”——“右”位置&[页码]后面按需添加字符（如：A）——点击第1个按钮设置字体颜色为打印纸张底色（如白色）——确定——回到“页面设置”主窗口——点击“页边距”选项卡——设置合适的“页眉”边距——确定。进入“打印预览”查看效果后进行适当调整，使页码正好位于你想要放置的位置。页眉的边距与工作表页面的上边距不冲突，可按需设置。
十一、设置保护工作表
上面已介绍，不在累罪。好了，在输入数据时，如果有人想对红色的锁定区域进行修改编辑的话，马上就会弹出一个对话框，提示“只有撤销了工作表保护才能修改”。哈，我的地盘，听我的，如果想发挥，到你的自留地（非锁定区域）去吧！
十二、工作表密码破解
工作表设置保护密码后，是不是会后怕万一密码忘记了造成丢失重要EXCEL文件，这份代码就能帮你找回，仅仅出之这个初衷，如因为这个代码让你感到不安全，那请加强电脑文件的密保，如用在不当的地方，后果自负！
小技巧分享：excel工作表忘记了保护密码，能否不用破解软件快速的破解密码？
可以，破解步骤：
1、新建一个EXCEL文件“工作簿1.xlsx”：视图——宏——录制宏——打开“录制新宏”对话框——“保存在”下拉菜单点选“个人宏工作簿”——确定，如下图。
2、视图——宏——停止录制
4、打开“宏”对话框——“宏名”点选“PERSONAL.XLSB！宏1”——编辑
5、点击“编辑”按钮后，弹出如下图的编辑界面：
找到“VBAProject（PERSONAL.XLSB）－模块－模块1（也可能是模块N-其他数字）”
破解代码：
=========请复制以下内容=============
Public Sub 工作表保护密码破解()
Const DBLSPACE As String = vbNewLine & vbNewLine
Const AUTHORS As String = DBLSPACE & vbNewLine & _
"作者:McCormick
Const HEADER As String = "工作表保护密码破解"
Const VERSION As String = DBLSPACE & "版本 Version 1.1.1"
Const REPBACK As String = DBLSPACE & ""
Const ZHENGLI As String = DBLSPACE &
"
Const ALLCLEAR As String = DBLSPACE & "该工作簿中的工作表密码保护已全部解除!!"
& DBLSPACE & "请记得另保存" _
& DBLSPACE & "注意：不要用在不当地方，要尊重他人的劳动成果！"
Const MSGNOPWORDS1 As String = "该文件工作表中没有加密"
Const MSGNOPWORDS2 As String = "该文件工作表中没有加密2"
Const MSGTAKETIME As String = "解密需花费一定时间,请耐心等候!" & DBLSPACE
& "按确定开始破解!"
Const MSGPWORDFOUND1 As String = "密码重新组合为:" & DBLSPACE &
"$$" & DBLSPACE & _
"如果该文件工作表有不同密码,将搜索下一组密码并修改清除"
Const MSGPWORDFOUND2 As String = "密码重新组合为:" & DBLSPACE &
"$$" & DBLSPACE & _
"如果该文件工作表有不同密码,将搜索下一组密码并解除"
Const MSGONLYONE As String = "确保为唯一的?"
Dim w1 As Worksheet, w2 As Worksheet
Dim i As Integer, j As Integer, k As Integer, l As Integer
Dim m As Integer, n As Integer, i1 As Integer, i2 As Integer
Dim i3 As Integer, i4 As Integer, i5 As Integer, i6 As
Integer
Dim PWord1 As String
Dim ShTag As Boolean, WinTag As Boolean
Application.ScreenUpdating = False
With ActiveWorkbook
WinTag = .ProtectStructure Or .ProtectWindows
End With
ShTag = False
For Each w1 In Worksheets
ShTag = ShTag Or w1.ProtectContents
Next w1
If Not ShTag And Not WinTag Then
MsgBox MSGNOPWORDS1, vbInformation, HEADER
Exit Sub
End If
MsgBox MSGTAKETIME, vbInformation, HEADER
If Not WinTag Then
Else
On Error Resume Next
Do 'dummy do loop
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
With ActiveWorkbook
.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) &
Chr(n)
If .ProtectStructure = False And _
.ProtectWindows = False Then
PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) &
_
Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
MsgBox Application.Substitute(MSGPWORDFOUND1, _
"$$", PWord1), vbInformation, HEADER
Exit Do 'Bypass all for...nexts
End If
End With
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
Loop Until True
On Error GoTo 0
End If
If WinTag And Not ShTag Then
MsgBox MSGONLYONE, vbInformation, HEADER
Exit Sub
End If
On Error Resume Next
For Each w1 In Worksheets
'Attempt clearance with PWord1
w1.Unprotect PWord1
Next w1
On Error GoTo 0
ShTag = False
For Each w1 In Worksheets
'Checks for all clear ShTag triggered to 1 if not.
ShTag = ShTag Or w1.ProtectContents
Next w1
If ShTag Then
For Each w1 In Worksheets
With w1
If .ProtectContents Then
On Error Resume Next
Do 'Dummy do loop
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) &
_
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If Not .ProtectContents Then
PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) &
_
Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
MsgBox Application.Substitute(MSGPWORDFOUND2, _
"$$", PWord1), vbInformation, HEADER
'leverage finding Pword by trying on other sheets
For Each w2 In Worksheets
w2.Unprotect PWord1
Next w2
Exit Do 'Bypass all for...nexts
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
Loop Until True
On Error GoTo 0
End If
End With
Next w1
End If
MsgBox ALLCLEAR & AUTHORS & VERSION & REPBACK &
ZHENGLI, vbInformation, HEADER
End Sub
6、点“保存”若出现下图信息，请退出360等杀软后再保存。
