|
|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
这段时间学习了一些Excel的课程，我将与我工作有关的部分整理如下，希望自己能记住，日后操作时如果忘记也可以查阅一下。这些资料的整理以自己能明白为准绳，所以文字比较简单，也许对自己才有用呵！
1、智能标记
学会使用智能标记，实现复制、序列等多种操作。
如果要增加智能标记的填充内容（序列规律），工具-->选项-->自定义序列，自行增加。
如果要进行拖动，但列很长，在选中第一个Cell、出现十字标志时，用double click就可以填充全列，并同时在当前页出现智能标记方便下一步操作。
2、数据透视表
如果感到数据透视表中的数据太多，还是不能满足要求（例如需要进一步汇总，将日数据汇总为月、季数据），则可以字段右键选择分组。注意，分组是可以多种分组同时存在的，例如：月与季分组同时存在。
无论是做排序、分类汇总、透视表等等，一定要保留原始的数据，copy一份数据到另一个工作表中再进行处理。
源数据第一行必须包括标题。
源数据每列只能有一种数据类型。
源数据不能有空列、空行，注意不要有最后一行留有数据汇总。
数据透视表创建之前不能有分类汇总或汇总、不能采用合并单元格。
（1）将多个字段多次地放入数据区域，可以通过字段设置，以不同的汇总方式显示（例如：求和、计数等）。（2）还可以通过字段设置中的选项，分别设置“汇总方式”（求和、计数等）和“显示方式”（普通、占总和百分比等）。（3）最后，还可以通过“数据透视表-->公式-->计算字段”显示计算后的数据。注意，只能用字段列表中的字段（数值）参与计算，即使是字段列表中的字段，如果汇总方式为计数，也会以数值参与计算。
想要最直观地体会数据透视表，可以用数据透视图。数据透视图如果想用网页来发布，可以Save为网页，但记住要将“添加交互”点击。
源数据添加一列，数据透视图不能自动地增加一列，必须重新做一个图。
3、条件格式的应用
可以应用条件格式用于查错。如果要快速地使一堆数据中符合某种条件的单元格呈现特定的格式（如字体颜色等），可以先选择要判断的数值范围，再使用“格式-->条件格式-->单元格数值”设置条件。
条件格式的另一种查错方法是对比两列数据是否完全相同，条件格式的“单元格数值”选“不等于”，然后鼠标往下拉，利用智能标记选择仅填充格式。
条件格式的再一种查错方法是对比一列长数据中是否有重复的数值，条件格式的“单元格公式”填：Countif()>=2，然后鼠标往下拉，利用智能标记选择仅填充格式。
要将一个大的表进行条件格式操作，可以“数据-->列表”，很快地将整个表选上，方便操作。
如果要使多行的数据阅读起来更便捷，可以隔行应用不同的格式（如隔行为淡灰色），“格式-->条件格式-->公式”进行设置。
（1）AND(LEN($D10)>0,MOD(ROW(),2)=1)：边输入边变色
（2）或MOD(ROW(),2)=0 ：完成输入后，选定区域后统一变色
（3）MOD函数，是求基偶数，＝0、＝1无所谓
如果要做出一个表是可以根据一定的时限性实时提醒做某事，可以用“单元格格式-->数字-->自定义”：＝B2-TODAY()，实时进行提醒。
4、数据审核的方法
如果数据有一定的规律性，即是数据是介于一定的数值区域内，就可以结合使用“数据-->有效性-->整数”和“视图-->工具栏-->公式审核”去大致控制风险。如果是要预防出错，则要先设有效性，再运用“视图-->工具栏-->公式审核”，一旦输错，马上就会以不同的格式显示。如果是要查错，则在数据范围内设置“有效性”和“视图-->工具栏-->公式审核”，出错的数字就会显示出来。
要注意，如果设置了“有效性”，就算delete了数值，不影响单元格的有效性。除非手动将有效性重新设为“任何值”。
如果数据较少要求核对，可以使用：工具-->语音；输入时也可以用“同步语音”，防疲劳时出错。
检查别人做好的数据、Excel公式：工具-->公式审核-->追踪引用单元格（从视图下的工具栏也可以调出来）
可以应用条件格式用于查错。如果要快速地使一堆数据中符合某种条件的单元格呈现特定的格式（如字体颜色等），可以先选择要判断的数值范围，再使用“格式-->条件格式-->单元格数值”设置条件。
条件格式的另一种查错方法是对比两列数据是否完全相同，条件格式的“单元格数值”选“不等于”，然后鼠标往下拉，利用智能标记选择仅填充格式。
条件格式的再一种查错方法是对比一列长数据中是否有重复的数值，条件格式的“单元格公式”填：Countif()>=2，然后鼠标往下拉，利用智能标记选择仅填充格式。
如果两个表要进行简单的比较，则可以用“窗口-->与XXX并排比较”，可选择“同步滚动”、水平或垂直并排。
如果工作薄中有很多个关联的工作表，最终审核时，一个表的改动令其他表也要改；运用“工具-->公式审核-->监视窗口”，可以将改动过的值始终在出现在各个工作表中，提醒及核对要作改变的项目是否已经全部改过了。
5、单元格有效性的应用
数据审核。结合“视图-->工具栏-->公式审核”去大致控制风险。见上文。
如果要创建下拉菜单，有限选择重复内容填列，例如：是/否/不适用。使用“数据-->有效性-->设置-->序列-->忽略定值&提供下拉菜单箭头都选-->在来源中将选项都选上-->确定”创建下拉菜单，通过制作数据源并设置有效性序列完成。
要注意，工作表中的数据源不能更改不能删除。
6、Excel函数
插入-->函数-->......或者直接按fx按键
要使用到函数的地方：
（1）单元格计算；
（2）条件格式对话框，例如符合偶数行就变格式为黄色；
（3）数据有效性的判断，例如相同发票号就输入无效；
（4）图表的选项，例如增加数列，增加柱形图等。？？
SUMIF：一定范围内符合条件就相加。
LOOKUP：用一个数与一行或一列数据依次进行比较，发现匹配的数值后，将另一组数据中对应的数值提取出来。Lookup只能在一行或一列中查询，而且该行或列只能以升序排列，只能模糊查询。
VLOOKUP：Vlookup用对比数与一个“表”进行对比，而不是Lookup函数的某1列或1行。Vlookup可以在表的第一列中查到需要的数据，再以列为单位定位（数列），模糊查询与精确查询都可以，其中模糊查询必须以升序排列。“V”stand for vertical.
HLOOKUP: HLookup用对比数与一个“表”进行对比，而不是Lookup函数的某1列或1行。Hlookup可以在表的第一行中查到需要的数据，再以行为单位定位（数行），模糊查询与精确查询都可以。“H”stand for horizontal.
Vlookup/hlookup+Match：Vlookup/Hlookup可以在表的第一列/行中查到需要的数据，再以一定的条件来定位，模糊查询与精确查询都可以。
YEAR:自动计算年龄，例如：在E2中写出生年月，IF(E2=0,"",YEAR(TODAY())-YEAR(E2))
Countif：帮助做统计动作，也可以用在条件格式及数据有效性中。
7、Excel图表
“视图-->工具栏-->图表”调出编辑图表
如果在工具栏中找不到一些命令，就可以在“工具-->自定义-->命令”，找出需要的命令拖放到相关工具栏上，找开某个工具栏，就能用到自己常用的命令。
用图表来表达意思，要令读者一眼就能看出信息与含义。
要将Excel的图表应用于Word或者PPT，可以使用copy & 选择性粘贴：
（1）图片：数据都不能改，粘贴位图会比较清楚；
（2）对象：粘贴后双击后进入Excel环境可以更改，这是嵌入文件，但与源文件没有关联；
（3）选粘贴链接及对象，任何一个文件改动，都会引起另一个文件的变动。
柱形图：是几个可比系列数据的对比。
条形图：适合文字标签较长时使用。
堆积柱形图：更适合于显示总额随时间变化。
折线图：绘制连续数据，反映趋势、走向（注意数据要等时间间距）
饼图：个体与总体比例的关系，如占市场份额（注意不能超过6个数据，如果超过6个数据一定要用饼图，则用复合饼图）
散点图：两个变量的关系，这些数据往往与时间相关，要看是否能看出一个结论：除了个别数据，趋势是上升还是下降？
面积图：显示某个时间段中，总数与数据系列的关系，实际上就是折线图与堆积柱形图的结合。
柱形图与折线图的合并图：先绘制两系列柱形图，右键点击图表类型，换为折线图即可。如果希望柱形图与折线图的刻度不同（例如两种刻度相差很悬殊，则其中一个图表趋势不明显），选中系列-->右键-->数据系列格式-->坐标轴-->次坐标轴的数值（Y）轴-->选中次坐标轴，改格式（在刻度里修改最大值等）。
棱锥图/圆柱/圆锥（三维）：比较柱形图个性化。
股价图：可以用其来显示数据的变动过程（walk）数据上升理解为阳线，数据下降理解为阴线，数值分别按“开盘、盘高、盘低、收盘”来排列，只不过盘高、盘低根据不同情况与开盘、收盘分别重合。
要在Excel工作簿中移动图表是很容易的，无论是在原数据工作表中插入或者是重新开一个Tab，单独放一个图表，“点击图表-->右键-->位置-->选择...”
如果系列的名称很长的处理方法：
（1）在数据源单元格中“ALT+回车”将文字分行，
（2）标题倾斜度。
比较直方图：将各个部门赞成及不赞成的数据直观显示出来，可以使用比较直方图。
（1）将系列的重叠比例设为100%；（2）分类轴：刻度线标签在图外；（3）坐标轴：刻度分类次序反转；（注意理解：系列就是不同颜色的柱或饼块所代表的数列）
如果要突出图表中的最大值与最小值，可以创建两列，应用函数：
（1）IF(B2=MAX($B$2:$B$13),B2,NA());
（2）IF(B2=MIN($B$2:$B$13),B2,NA());
（3）结果出来以后，选中两列数据，复制-->点击图表，选择性粘贴到图表上来-->添加单元格为“新建系列”，数值（Y）轴在列（或“行”，做图时看情况，可以作尝试）-->格式化最大值、最小值系列。
如果要做出目标与实际的差距，可以用以下的图表：
第一个图用两个系列100%重叠的方法，没有杯子倒水的感觉；第二个图用次坐标的方法来做（左边的次坐标没有显示），就有杯子倒水的感觉，通过相关系列调整格式来达到。
8、零星总结
如果要将txt文本导入Excel，数据-->导入外部数据-->选中文本文件并导入。
如果有大量的数据要输入，可以使用“数据-->记录单”
注意，如果在记录单中删除一条记录，这个动作不能撤销。
在一堆数据中查询，可以用Find，但Find除了精确查询外，只能使用通配符，不能指定数值的范围；用记录单除了find的功能外，还能查询“<”或“>”的条件。其实做自动筛选也可以达到目的。
如果需要与Internet上的数据实时刷新，可以用“数据-->导入外部数据-->Web查询”，并在导入后右键设置数据区域属性为“允许后台刷新”。
如果要实现对单元格或表格的保护，不让别人修改或者看公式，但又要保留下某些单元格给别人输入。
（a）单元格格式-->保护-->默认/预设为“锁定”，需要保护的话则要撤销打钩（没锁定的话，意思就是open，可以给用户填写）
（b）工具-->保护-->保护工作表-->在“允许用户进行”中只打钩“选定未锁定单元格”
必须先排序，才能做分类汇总（“数据-->分类汇总”）。分类汇总最多只能做一次操作（三个层次），如果想继续做下一个层次，则可以再做一次分类汇总，记住不要选择“替换当前分类汇总”。要删除分类汇总，必须再操作一次“数据-->分类汇总”，选择“全部删除”。
9、单元格的引用：
（1）相对为A1，鼠标拖动时可以改变；
（2）绝对为$A$1；（3）混合为A$1；
（4）三维地址引用：Sheet1!A1
如果计算与显示不同，例如：0.33+0.33+0.33＝1，修改可以用：工具-->自动更正选项-->错误检查-->公式在区域内不一致。另一个解决的办法也很简单，利用Round函数为每个中间值进行四舍五入的操作，格式为：Round(数值，保留的小数位数)，它的作用就是对指定数值进行四舍五入，保留指定小数位数。
除了自动筛选，还可以应用高级筛选功能，筛选完之后自动生成一个表。（1）条件区域必须要包括第一行的表头项目；（2）可以要求同一列的多个条件，也可以是不同列的条件“并且”及“或者”的关系。
单元格中字符前有空格如果一次性地去掉：数据-->数据分列（分隔符号的“空格”）
如果要实现不同的工作表数据合并成一个工作表，相同的项目相加：数据-->合并计算。
注意，除了标题，其他文本是不能合并计算操作的；另外，如果源工作表数据变动，则合并过来的表格要重新再做一遍。
排序、自动筛选可以直接粘贴排序结果，但分类汇总不能直接粘贴汇总结果。先选中要粘贴的数据，点击“选定可见单元格”按钮，再copy & paste到另一个工作表。“选定可见单元格”按钮要通过“工具-->自定义-->命令-->选择”进行设置。 |
评分
-
2
查看全部评分
-
|
[复制链接]
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2011-4-17 19:16
