EXCEL操作基本技巧

LOGO EXCEL操作基本技巧 华北区 销售计划组 目 录v 透视表应用v EXCEL部分功能介绍v countif，vlookup函数应用 华北区 销售计划组 目 录v 透视表应用v EXCEL部分功能介绍v countif，vlookup函数应用 华北区 销售计划组 数据透视表透视表位置：数据 数据透视表和数据透视图 华北区 销售计划组 数据透视表具体操作： 华北区 销售计划组 数据透视表行列所需字段 华北区 销售计划组 数据透视表完成行列 数据 华北区 销售计划组 数据透视表-排序方法一：注：将鼠标选定在“行”再单击 右键 华北区 销售计划组 数据透视表-排序 华北区 销售计划组 数据透视表-排序方法二：注：将鼠标选定在“行” 华北区 销售计划组 数据透视表-排序 华北区 销售计划组 数据透视表-用好“透视表”工具栏1，数据透视表建立以后，工具栏会自动出现“数据透视表工具栏”，样式如下：两者功能一样插入字段，可使用公式自动套用格式 可以对透视表的格式设置作一些基本的修改,如”合并标志” 华北区 销售计划组 数据透视表计算字段对数据进行 、 、/ 运算 华北区 销售计划组 数据透视表计算字段起名。如：1-2月零售通过”字段”编辑公 式 华北区 销售计划组 数据透视表计算字段例：双击“字段”里的项，即可自动出现在“公式”里 华北区 销售计划组 数据透视表计算字段 计算的字段出现在数据的最后一列 店效 华北区 销售计划组 数据透视表字段设置 路径：右键字段设置选项 计算占总和的百分比采购规划表可用此命令 华北区 销售计划组 数据透视表图型根据透视表数据，自动显示图表一样 华北区 销售计划组 数据透视表图型 根据透视表的变化，自动更新图形 可对图型形状、颜色等更改 可变项可变项 华北区 销售计划组 数据透视表设置报告格式 表格美观 华北区 销售计划组 数据透视表设置报告格式 华北区 销售计划组 数据透视表动态数据源1、动态数据源介绍 在原数据源区域内，增加行或列，数据透视表的内容随之而改变2、运用的函数：OFFSET和COUNTAv OFFSET：（ref,row,col,height,width）根据条件得到新的引用 Reference 作为偏移量参照系的引用区域。Reference 必须为对单元格或相连单元格区域的引用；否则，函数 OFFSET 返回错误值 #VALUE!。 Rows 相对于偏移量参照系的左上角单元格，上（下）偏移的行数，可为正数（代表在起始引用的下方）或负数（代表在起始引用的上 方）。 Cols 相对于偏移量参照系的左上角单元格，左（右）偏移的列数。可为正数（代表在起始引用的右边）或负数（代表在起始引用的左 边）。 Height 高度，即所要返回的引用区域的行数。Height 必须为正数。 Width 宽度，即所要返回的引用区域的列数。Width 必须为正数v COUNTA：可以统计一个区域内非空单元格的数量，也即对新增加的数据记录能自 动发现，以确定OFFSET函数的height和width参数3、为动态区域建立一个名称，路径是：插入名称定义，假设名称为DATA4、在“引用位置”栏里输入：OFFSET(A1,COUNTA($A:$A),COUNTA($1:$1)5、更改透视表中的数据选定区域，更名为DATA，刷新即可 华北区 销售计划组 目 录v 透视表应用v EXCEL部分功能介绍v countif，vlookup函数应用 华北区 销售计划组 定位一、定位空值，用于填充空值： 1、快捷键：CTRL+G定位，或F5 2、定位条件：空值 3、点击第一个空白单元格，输入 “=“，再点击被引用的单元格； 4、按CTRL+ENTER结束二、定位空值，用于删除空值： 1、2步骤同上 3、点击其中一个空值，右击：选 择删除整行。 华北区 销售计划组 定位三、隔行插入空行（两次定位） 1、最后一列隔行写序号 2、选中整列，第一次定位空值，右键添加整行 3、选中整列，第二次定位量量，右键添加整行 华北区 销售计划组 定位3、选中整列，第二次定位量量，右键添加整行 4、最后将序列删除即可 华北区 销售计划组 条件格式用途：突出重要数值、单元格路径：格式条件格式选择单元格或公式输入选择数据的条件或公式 对格式进行编辑 华北区 销售计划组 条件格式例：条件选择“单元格数值” 注：提前选中需要进行条件格式的区域 华北区 销售计划组 条件格式例：条件选择“公式”隔行：mod(row(),2)隔列：mod(column(),2)交叉：mod(row()+column(),2) 注：提前选中需要进行条件格式的区域 华北区 销售计划组 工作表保护用途：在指定区域填写内容步聚：1、选定编辑区域 华北区 销售计划组 工作表保护标题不能以数字开头 华北区 销售计划组 工作表保护步骤：2、保护工作表 华北区 销售计划组 工作表保护 勾选：1、选定锁定单元格 2、选定未锁定的单元格 3、使用自动筛选系统默认 华北区 销售计划组 选择性粘贴 防止用原始区域的空单元格覆盖粘贴区域的单元格，空单元格将不被复制和粘贴实际等于单元格引用，对于跨表、跨工作簿引用比较有效，可实现同步更新行项与列项互换数值转化为千元为单位时可以使用“除” 华北区 销售计划组 EXCEL与WORD的连接1、从EXCEL中选择相应的表格并粘贴至WORD文档后，点击“粘贴选项”，选择“链接到EXCEL”选项（2个，任选其一） 2、再次打开WORD后，如果不确认此表格中的数据是否为最新，可右击，并选择：更新链接，可实现数据更新方法：点击：更新链接3、反过来，在WORD中右击EXCEL表格，选择：链接的工作表对象，选择“打开”或“编辑”，可以对源文件进行操作并实现同步更新 华北区 销售计划组 EXCEL与PPT超链接v粘贴EXCEL表格后（存为图片格式），点击右键：可与原文件建立超链接，方便找到原文件 店 铺 编 号 简 称 2008年 1月销 售 金 额 2008年 2月销 售 金 额 2008年 3月销 售 金 额 2008年 4月销 售 金 额 2008年 5月销 售 金 额F10A 商 厦 店 157088 302158 120485 134348 147759F11R 东 购 商 场 204545 452941 298740 463244.05 484932.75F11W 青 龙 李 宁 专 卖 店 72459 132443 135789 112727 154433F11Z 古 冶 李 宁 专 卖 店 93643 256956 185701 229532 247168 F136 昌 黎 店 100653 185646 194682 222197 195798F13F 北 购 店 0 0 0 0 0 华北区 销售计划组 照相机1、添加快捷图标：工具自定义命令工具照相机2、拖动鼠标并且选择需要“拍照”的表格内容3、点击工具栏上的“照相机”按钮，于是这个选 定的区域就被“拍”了下来4、打开另一张工作表，在需要显示“照片”的位置 单击鼠标，被“拍摄”的“照片”就立即粘贴过来了5、粘贴后的“照片”可以调整格式，并且当源文件中 的表格内容发生变化时，照片也会自动更新 6、注意：只能在同一个工作簿中使用；7、如果要将EXCEL表格内容直接以图片形式存 放至WORD或PPT，则可以使用另一种方法： 选中数据区域，按住SHIFT，点击“编辑”菜单 中的“复制图片”，可直接复制到WORD、PPT中去 华北区 销售计划组 打印标题及公司LOGO打印标题：用于打印N页表格，每页表格第一行或第一列显示标题行或列 华北区 销售计划组 打印标题及公司LOGO打印公司LOGO用途：每页纸下方显示公司LOGO路径：页面设置 页眉/页脚 自定义页脚 华北区 销售计划组 Excel中双击格式刷的妙用 1，格式刷的主要作用是复制某一单元格（或区域）的格式（字体、字号、行高、列宽等）应用于其他区域，可将上述单元格选中，单击格式刷来取出所在位置或所选内容的文字格式，用这个刷子去刷别的单元格可实现文字格式的复制。 但每次刷完后，格式刷就变成不可用了，如果希望重复复制格式，就要不停地重复上述过程。 2，有一个方法可避免上述的繁琐步骤：通过双击格式刷，可以将选定格式复制到多个位置。若要关闭格式刷，则按下“Esc”键或再次单击格式刷即可（此方法同样适用于Word）。 华北区 销售计划组 快捷键-弹指神功 按住ctrl+shift拖动单元格=复制+插入单元格+粘贴 ctrl +9隐藏一行单元格 ctrl+0隐藏一列单元格 Ctrl+Shift+9取消隐藏行 Ctrl+Shift+0取消隐藏列 快速输入今天的日期：ctrl+; 快速输入当前的时间：ctrl+shift+; 选定从工作表开始到结尾的方法：选中开始单元格（如A21），同时按下ctrl+shift+end. 用Ctrl+右键头和Ctrl+下箭头快速选定区域:如果一张表的第一行和第一列没有空格，一次即可，有几 个空格按几次方向键! 删除单元格/行/列：选中单元格，ctrl+“-”（减号） 插入单元格/行/列：选中单元格，ctrl+shift+“+”（加号） ctrl+1 快速打开格式对话框。 +可以将当前工作表中的公式显示出来,再按+则恢复. F4功能键的妙用:重复上一步操作. 这招在隔行删除、多点复制等方面用起来很爽 快速选定区域按F5键，在“定位”对话框的“引用位置”栏中输入需选中区域，如“a1:w1000”，按“确定”即可。 alt+enter拆分同一单元格内多行文字按ALT+向下键，即可得到本列中原有文本的列表。输入自动求和公式时，不用鼠标点按工具栏上的按钮，只需按“Alt+=”即可。ALT+；选定可见单元格 华北区 销售计划组 线条数据图表类型系列分类最终目的Excel图表应用 华北区 销售计划组 簇状堆积 百分比三维 Excel图表应用-柱形图可以用于店铺、分销商数据的对比 可用于销售产品大类的占比，如服装、鞋、器材 可用于新开、整改店铺分析 华北区 销售计划组 Excel图表应用-折线图 可用于指标完成率、同比增长的分析 华北区 销售计划组不能多于5或6个数据点负数会被转化成正数扇区可脱离中心 Excel图表应用-饼图 可用于库存数据分析 华北区 销售计划组 Excel图表应用-图表设置 可用于销售收入（回款）及指标完成率分析 华北区 销售计划组 目 录v 透视表应用v EXCEL其他功能介绍v countif、vlookup函数应用 华北区 销售计划组 函数countif的应用l 计 算 区 域 中 满 足 给 定 条 件 的 单 元 格 的 个 数 ;语 法 格 式 ： =COUNTIF(range,criteria) 参 数 ： Range 为 计 算 其 中 满 足 条 件 的 单 元 格 数 目 的 单 元 格 区 域 Criteria 为 确 定 哪 些 单 元 格 将 被 计 算 在 内 的 条 件 ， 其 形 式 可 以 为 数 字 、表 达 式 或 文 本 。 例 如 ， 条 件 可 以 表 示 为 0 计算1-5月零售流水大于的零的月份数量 华北区 销售计划组 函数vlookup的应用l 在 表 格 首 列 查 找 指 定 的 数 值 ， 并 由 此 返 回 表 格 当 前 行 中 指 定 列 处 的 数 值 。 语 法 格 式 ： =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 参 数 ： Lookup_value 为 需 要 在 数 组 第 一 列 中 查 找 的 数 值 。 Lookup_value 可 以 为 数 值 、 引 用 或 文 本 字 符 串 。 Table_array 为 需 要 在 其 中 查 找 数 据 的 数 据 表 。 如 果 range_lookup 为 TRUE， 则 table_array 的 第 一 列 中 的 数 值 必 须 按 升 序 排 列 , 否 则 ， 函 数 VLOOKUP 不 能 返 回 正 确 的 数 值 。 如 果 range_lookup 为 FALSE， table_array 不 必 进 行 排 序 Col_index_num 为 table_array 中 待 返 回 的 匹 配 值 的 列 序 号 。 当 为 1时 ， 返 回 table_array 第 一 列 中 的 数 值 ； 为 2时 ， 返 回 table_array 第 二 列 中 的 数 值 ， 以 此 类 推 。 Range_lookup 为 一 逻 辑 值 ， 指 明 函 数 VLOOKUP 返 回 时 是 精 确 匹 配 还 是 近 似 匹 配 。 如 果 为 TRUE 或 省 略 ， 则 返 回 近 似 匹 配 值 ， 也 就 是 说 ， 如 果 找 不 到 精 确 匹 配 值 ， 则 返 回 小 于 lookup_value 的 最 大 数 值 ； 如 果 range_value 为 FALSE， 函 数 VLOOKUP 将 返 回 精 确 匹 配 值 。 如 果 找 不 到 ， 则 返 回 错 误 值 #N/A。 华北区 销售计划组 函数vlookup的应用逐列V 华北区 销售计划组 函数vlookup的应用多列一起V，运用函数columnF4锁定查找列及被查找列，锁定符号$ 华北区 销售计划组 本次培训计划是否能满足您的需求？期待您的回复