EXCEL技巧与提升
合集下载
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
实例：从房号中提取室号
3
MID:从字符串的指定字符开始向右返回指定个数的字符
=mid （需提取字符的字符串，指定开始字符，提取个数）
实例：根据身份证提取出生年月
3、基础类操作-常用函数：ROUND&CONCATENATE
常用函数
What?
4
ROUND：按指定的位数对数值进行四舍五入
=round（需要进行四舍五入的数值，指定的位数）
2、基础类操作-查找和替换
查找：台账、明细查找房源
在进行查找、替换操作之前，先选定一个搜索 区域。 单击“查找和替换”对话框中的“选项”按钮， 可以设置查找的格式、范围、方式等；
替换：台账明细替换字段
在进行查找、替换时，如果不能确定完整的搜 索信息，可以使用通配符“？”和“*”来代 替不能确定的部分信息，“？”代表一个字符， “*”代表一个或多个字符；
览等
基础类操作
常用技巧
1、基础类操作-复制、粘贴技巧
简单复制单元格：
方法1：选中所需区域，ctrl+c 复制、ctrl+x剪切，ctrl+v粘贴； 方法2：选中所需区域，工具单开始图标功能—复制、粘贴； 方法3：选中所需区域，右击鼠标键—复制、粘贴；
筛选状态下复制单元格：
适用于从大量数据中筛选所需数据并复制到新表中，此时简单的方法粘贴时经常多出隐藏内容或部分数据复制缺失； 方法：筛选出所需复制的数据—选择复制区域—先按alt+; 键— ctrl+c — ctrl+v；
在Excel2003版本中， Sumifs函数无力进行多条件的求和计 算，所以只能运用Sumproduct函数。
答:=SUMPRODUCT(($I$10:$I$21="3")*($Z$10:$Z $21="一次性")*(K10:K21)) 得出3号楼一次性付款的房源共3套，面积183.55 平方，金额1038265元。
返回错误值 #VALUE!
答： =SUMPRODUCT(($I$10:$I$21=“3")*($Y$10:$Y$21 ="按揭")*(K10:K21))
得出3号楼一次性付款的房源共6套，面积337.10平方， 金额1974003元。
为什么别人做事总比你快？这下明白了吧。如果你也学会 这些神技，放眼望去，你的Excel已经超过90%的同事了。
实例：在日报中大定日期上汇总大定套数，大定总价上汇总大定金额
提升类操作
常用函数
1、提升类操作-VLOOKUP& HLOOKUP函数(查找函数)
实例： 核对销售台账和手工销售明细之间面积和总价
E函数
的差异，如下表中大定面积的差异:
»» VLOOKUP：在表格数组的首列查找指定的值，并由此返回表格数组当 前行中其他列的值 VLOOKUP 中的 V 参数表示垂直方向
图样图森破 左图显示的是美国纽约曼哈顿的24小时人口活跃图。利用 条件格式与小方块，地图便在 Excel 上被视觉化了。每个 GIF 图的框架就是一张 Excel 的萤幕截图。
俗话说“工欲善其事必先利其器”，现在我们已经有了很好的“武器” 那么就让我们来了解下EXCEL的工作界面和常用图标吧。。。。。。
实例：结算书上佣金四舍五入保留两位小数
5
CONCATENATE：将N个字符串合并为一个文本字符串
=concatenate（联接项1，联接项2，联接项3…）
实例：每日固定短信的编辑 注：使用concatenate函数在对文本进行处理编辑时，无需手动添加双引号， 当对较多数据进行编辑时比使用“&”进行连接会更为方便
学习EXCEL 操作技巧
如何让excel在你手中发挥神器作用
目 录
1、前 言 2、基本操作类 3、提升类操作
Excel是Office三大神器当中最神 秘、但也是最能提高效率的工具 了。而我们太多小伙伴，却一直 把它当做是个“电子表格工具”
EXCEL用得 好那是神器， 用不好就是个 电子表格
器 神
轻松复制工作表：
方法1：右击表单标签，移动或复制工作表；可直接移动，或建立副本进行复制 方法2：鼠标点击表头小三角全选，复制整表内容；
选择性粘贴：
适用于改变原数据格式进行复制、改变原数据运算方式、改变原数据展示界面进行复制； 例如数据单位转化时的大批量公式粘贴、或者格式粘贴和纯数值粘贴； 方法：选中所需区域，右击鼠标键—复制—选择性粘贴；
如明细表内我们希望在付款方式列只允许 “商业付款、一次性付款、分期付款”三种方 式，则需在有效性内容框内填写三类内容，点 击确定即可，此时此列只允许输入这三种方式；
设置完有效性规则后，输入非设置内容时， 会提示“输入非法值”；
5、基础类操作-数据分列
»»分列：将某列单元格的内容分割
成数个单独的列
注：空值和文本值会被忽略；
3、提升类操作- Sumifs函数（多条件求和函数）
E函数
实例： 问， 3号楼付款方式为一次性的房源面积、金额共计多少？
»» Sumifs：对多个指定区域满足多个指定条件的区域求和
=Sumifs（求和区域，指定区域，指定条件，指定区域，指定条 件，… ）
注：函数公式中每个指定区域大小和形状必须相同；
快捷访问工具栏 Office按钮
工具栏选项卡
文件名称
编辑栏及名称框
工作表标签
页面显示控制区
常用图标介绍
开始界面：格式刷、自动换行、设置单元格、查找、替换、筛
选、排序、求和、计数、保护工作表
插入界面：形状、符号、超链接 页面布局界面：打印区域设置、打印标题 数据页面：数据有效性、组合 视图界面：冻结窗格 自定义快速访问工具栏：撤销上次操作、快速打印、打印预
4、基础类操作-常用函数：SUM&SUBTOTAL
常用函数
What?
6
SUM:计算所选区域中所有数据的合计
= sum（选定区域）
实例：求销售明细中所有房源大定金额的合计
7
SUBTOTAL:返回列表或数据库的分类汇总
=subtotal（1-11或101-111），汇总区域）
SUBTOTAL函数可谓是全能王,可以对数据进行求平均值、计数、最大最小、 相乘、标准差、求和、方差。
找不到时，可能出现#N/A!等等
E函数
2、基础类操作-常用函数：LEFT&RIGHT&MID
1
LEFT:从字符串的第一个字符开始向右返回指定个数的字符
常用函数
What?
=left（需提取字符的字符串，提取个数）
实例：从房号中提取栋号
2
RIGHT:从字符串的最后一个字符开始向左返回指定个数的字符
= right （需提取字符的字符串，提取个数）
=Vlookup（给定条件，查找范围，查找的值在查找范围的列数，查找精度）
»» HLOOKUP：在表格或数值数组 的首行查找指定的数值，并在表格或 数组中指定行的同一列中返回一个数值 HLOOKUP 中的 H 参数表示“行”
=Hlookup（给定条件，查找范围，查找的值在查找范围的行数，查找精度）
答： =VLOOKUP(H9,'[明细_重庆区域事业二部_蓝光幸 福满庭项目(2).xlsx]明细1'!H$10:L$77,5,FALSE) 找出3-606的大定面积存在差异。 注：如果查找范围中有多个科目符合条件，则输出结果只会显示
——主要用于对楼室号的拆分
——实例：开盘前期根据甲方提供的完 整房号拆分成我们销售系统或者日报明细 需要的楼号、室号等
基础类操作
常用函数
1、基础类操作-常用函数符号
Байду номын сангаас
符号
运算含义
+-*/
为运算符，表示加减乘除
^
表示乘方，如2^3=2*2*2
&
连接符，用于文本连接。如2&3=23（这里的2和3 均视为文本而不是数值）
筛选:明细、客户分析表常用
进行基础数据筛选，“按日期筛选”“按颜 色筛选” 选中表头行，点击“排序和筛选”将整行表 头处于筛选状态。 如图：筛选2011年大定房源 点击”大定日期”筛选框，勾选2011年，即 可弹出2011年此项目所有大定房源信息；
4、基础类操作-数据有效性
在输入数据时，为了防止输入错误的数据， 可以为单元格设置有效的数据范围，限制只能 输入指定范围内的数据；
也可用“Ctrl+F” 组合键唤出
开始选项中的“查 找和替换”按钮
3、基础类操作-排序和筛选
排序：明细、客户分析表常用
选择数据区域，进行“升序”“降序”排序、 按“颜色”排序 自定义排序：选择数据区域，进行多条件排 序，多内容排序 图为：全选整表（可保持整表数据每列不错 乱），先按楼号升序；再按室号升序；
4、提升类操作- Sumproduct函数（多条件求和函数）
实例： 问， 3号楼付款方式为按揭的房源面积、金额是多少？
E函数
»» Sumproduct：在给定的相同维数的几组数组中，将数组间对 应的元素相乘，并返回乘积之和
=Sumproduct（数组1*数组2*... ）
注：数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将
最先查找到值；
2、提升类操作- Countifs函数（多条件计数函数）
E函数
实例： 某项目经历开盘后，对剩余可售房源进行梳理
»» Countifs：对多个指定区域满足多个指定条件的单元格计数 =Countifs（指定区域，指定条件，指定区域，指定条件，…）
答：=COUNTIFS($T$10:$T$77,“”) 计算出剩余可售房源为6套。
<>
表示不等于或者不包含
， （英）
表示函数内一个参数与另一个参数隔开的标志
“ ” 由于函数公式中不能直接解读文本内容，需要用双
（英）
引号来让系统解读
！ 用于sheet与单元格之间，在叹号前为sheet名，叹
（英）
号后为单元格或者区域
出现这个符号的时候，往往是存在错位置，例如引
# 用错误（被引用的地方给删除了）会显示#REF!；查
3
MID:从字符串的指定字符开始向右返回指定个数的字符
=mid （需提取字符的字符串，指定开始字符，提取个数）
实例：根据身份证提取出生年月
3、基础类操作-常用函数：ROUND&CONCATENATE
常用函数
What?
4
ROUND：按指定的位数对数值进行四舍五入
=round（需要进行四舍五入的数值，指定的位数）
2、基础类操作-查找和替换
查找：台账、明细查找房源
在进行查找、替换操作之前，先选定一个搜索 区域。 单击“查找和替换”对话框中的“选项”按钮， 可以设置查找的格式、范围、方式等；
替换：台账明细替换字段
在进行查找、替换时，如果不能确定完整的搜 索信息，可以使用通配符“？”和“*”来代 替不能确定的部分信息，“？”代表一个字符， “*”代表一个或多个字符；
览等
基础类操作
常用技巧
1、基础类操作-复制、粘贴技巧
简单复制单元格：
方法1：选中所需区域，ctrl+c 复制、ctrl+x剪切，ctrl+v粘贴； 方法2：选中所需区域，工具单开始图标功能—复制、粘贴； 方法3：选中所需区域，右击鼠标键—复制、粘贴；
筛选状态下复制单元格：
适用于从大量数据中筛选所需数据并复制到新表中，此时简单的方法粘贴时经常多出隐藏内容或部分数据复制缺失； 方法：筛选出所需复制的数据—选择复制区域—先按alt+; 键— ctrl+c — ctrl+v；
在Excel2003版本中， Sumifs函数无力进行多条件的求和计 算，所以只能运用Sumproduct函数。
答:=SUMPRODUCT(($I$10:$I$21="3")*($Z$10:$Z $21="一次性")*(K10:K21)) 得出3号楼一次性付款的房源共3套，面积183.55 平方，金额1038265元。
返回错误值 #VALUE!
答： =SUMPRODUCT(($I$10:$I$21=“3")*($Y$10:$Y$21 ="按揭")*(K10:K21))
得出3号楼一次性付款的房源共6套，面积337.10平方， 金额1974003元。
为什么别人做事总比你快？这下明白了吧。如果你也学会 这些神技，放眼望去，你的Excel已经超过90%的同事了。
实例：在日报中大定日期上汇总大定套数，大定总价上汇总大定金额
提升类操作
常用函数
1、提升类操作-VLOOKUP& HLOOKUP函数(查找函数)
实例： 核对销售台账和手工销售明细之间面积和总价
E函数
的差异，如下表中大定面积的差异:
»» VLOOKUP：在表格数组的首列查找指定的值，并由此返回表格数组当 前行中其他列的值 VLOOKUP 中的 V 参数表示垂直方向
图样图森破 左图显示的是美国纽约曼哈顿的24小时人口活跃图。利用 条件格式与小方块，地图便在 Excel 上被视觉化了。每个 GIF 图的框架就是一张 Excel 的萤幕截图。
俗话说“工欲善其事必先利其器”，现在我们已经有了很好的“武器” 那么就让我们来了解下EXCEL的工作界面和常用图标吧。。。。。。
实例：结算书上佣金四舍五入保留两位小数
5
CONCATENATE：将N个字符串合并为一个文本字符串
=concatenate（联接项1，联接项2，联接项3…）
实例：每日固定短信的编辑 注：使用concatenate函数在对文本进行处理编辑时，无需手动添加双引号， 当对较多数据进行编辑时比使用“&”进行连接会更为方便
学习EXCEL 操作技巧
如何让excel在你手中发挥神器作用
目 录
1、前 言 2、基本操作类 3、提升类操作
Excel是Office三大神器当中最神 秘、但也是最能提高效率的工具 了。而我们太多小伙伴，却一直 把它当做是个“电子表格工具”
EXCEL用得 好那是神器， 用不好就是个 电子表格
器 神
轻松复制工作表：
方法1：右击表单标签，移动或复制工作表；可直接移动，或建立副本进行复制 方法2：鼠标点击表头小三角全选，复制整表内容；
选择性粘贴：
适用于改变原数据格式进行复制、改变原数据运算方式、改变原数据展示界面进行复制； 例如数据单位转化时的大批量公式粘贴、或者格式粘贴和纯数值粘贴； 方法：选中所需区域，右击鼠标键—复制—选择性粘贴；
如明细表内我们希望在付款方式列只允许 “商业付款、一次性付款、分期付款”三种方 式，则需在有效性内容框内填写三类内容，点 击确定即可，此时此列只允许输入这三种方式；
设置完有效性规则后，输入非设置内容时， 会提示“输入非法值”；
5、基础类操作-数据分列
»»分列：将某列单元格的内容分割
成数个单独的列
注：空值和文本值会被忽略；
3、提升类操作- Sumifs函数（多条件求和函数）
E函数
实例： 问， 3号楼付款方式为一次性的房源面积、金额共计多少？
»» Sumifs：对多个指定区域满足多个指定条件的区域求和
=Sumifs（求和区域，指定区域，指定条件，指定区域，指定条 件，… ）
注：函数公式中每个指定区域大小和形状必须相同；
快捷访问工具栏 Office按钮
工具栏选项卡
文件名称
编辑栏及名称框
工作表标签
页面显示控制区
常用图标介绍
开始界面：格式刷、自动换行、设置单元格、查找、替换、筛
选、排序、求和、计数、保护工作表
插入界面：形状、符号、超链接 页面布局界面：打印区域设置、打印标题 数据页面：数据有效性、组合 视图界面：冻结窗格 自定义快速访问工具栏：撤销上次操作、快速打印、打印预
4、基础类操作-常用函数：SUM&SUBTOTAL
常用函数
What?
6
SUM:计算所选区域中所有数据的合计
= sum（选定区域）
实例：求销售明细中所有房源大定金额的合计
7
SUBTOTAL:返回列表或数据库的分类汇总
=subtotal（1-11或101-111），汇总区域）
SUBTOTAL函数可谓是全能王,可以对数据进行求平均值、计数、最大最小、 相乘、标准差、求和、方差。
找不到时，可能出现#N/A!等等
E函数
2、基础类操作-常用函数：LEFT&RIGHT&MID
1
LEFT:从字符串的第一个字符开始向右返回指定个数的字符
常用函数
What?
=left（需提取字符的字符串，提取个数）
实例：从房号中提取栋号
2
RIGHT:从字符串的最后一个字符开始向左返回指定个数的字符
= right （需提取字符的字符串，提取个数）
=Vlookup（给定条件，查找范围，查找的值在查找范围的列数，查找精度）
»» HLOOKUP：在表格或数值数组 的首行查找指定的数值，并在表格或 数组中指定行的同一列中返回一个数值 HLOOKUP 中的 H 参数表示“行”
=Hlookup（给定条件，查找范围，查找的值在查找范围的行数，查找精度）
答： =VLOOKUP(H9,'[明细_重庆区域事业二部_蓝光幸 福满庭项目(2).xlsx]明细1'!H$10:L$77,5,FALSE) 找出3-606的大定面积存在差异。 注：如果查找范围中有多个科目符合条件，则输出结果只会显示
——主要用于对楼室号的拆分
——实例：开盘前期根据甲方提供的完 整房号拆分成我们销售系统或者日报明细 需要的楼号、室号等
基础类操作
常用函数
1、基础类操作-常用函数符号
Байду номын сангаас
符号
运算含义
+-*/
为运算符，表示加减乘除
^
表示乘方，如2^3=2*2*2
&
连接符，用于文本连接。如2&3=23（这里的2和3 均视为文本而不是数值）
筛选:明细、客户分析表常用
进行基础数据筛选，“按日期筛选”“按颜 色筛选” 选中表头行，点击“排序和筛选”将整行表 头处于筛选状态。 如图：筛选2011年大定房源 点击”大定日期”筛选框，勾选2011年，即 可弹出2011年此项目所有大定房源信息；
4、基础类操作-数据有效性
在输入数据时，为了防止输入错误的数据， 可以为单元格设置有效的数据范围，限制只能 输入指定范围内的数据；
也可用“Ctrl+F” 组合键唤出
开始选项中的“查 找和替换”按钮
3、基础类操作-排序和筛选
排序：明细、客户分析表常用
选择数据区域，进行“升序”“降序”排序、 按“颜色”排序 自定义排序：选择数据区域，进行多条件排 序，多内容排序 图为：全选整表（可保持整表数据每列不错 乱），先按楼号升序；再按室号升序；
4、提升类操作- Sumproduct函数（多条件求和函数）
实例： 问， 3号楼付款方式为按揭的房源面积、金额是多少？
E函数
»» Sumproduct：在给定的相同维数的几组数组中，将数组间对 应的元素相乘，并返回乘积之和
=Sumproduct（数组1*数组2*... ）
注：数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将
最先查找到值；
2、提升类操作- Countifs函数（多条件计数函数）
E函数
实例： 某项目经历开盘后，对剩余可售房源进行梳理
»» Countifs：对多个指定区域满足多个指定条件的单元格计数 =Countifs（指定区域，指定条件，指定区域，指定条件，…）
答：=COUNTIFS($T$10:$T$77,“”) 计算出剩余可售房源为6套。
<>
表示不等于或者不包含
， （英）
表示函数内一个参数与另一个参数隔开的标志
“ ” 由于函数公式中不能直接解读文本内容，需要用双
（英）
引号来让系统解读
！ 用于sheet与单元格之间，在叹号前为sheet名，叹
（英）
号后为单元格或者区域
出现这个符号的时候，往往是存在错位置，例如引
# 用错误（被引用的地方给删除了）会显示#REF!；查