- ?
ISBLANK函数和ISLOGICAL函数
Rosine
展开
最近今天小编也在准备年会和年底各项业绩数据统计,忙的是不可开交啊。这两天都没有更新啦。同时在这里也祝福各位粉丝们年会都能抽中大奖,年终奖数收到手软哈。
今天小编要给大家分享的是 ISBLANK和ISLOGICAL这两个函数。
ISBLANK:判断单元格内容是否为空白
ISLOGICAL:判断是否为逻辑值
小编同样为大家准备了详细的案例来说明函数:
案例一:表格信息如下,统计出勤率
表格说明:在是否签到列中 如果单元格没有打√代表缺勤
缺勤率=缺勤的人数/总的人数
具体操作如下图:
ISBLANK函数方法:在B12单元格中输入公式:=SUM(--ISBLANK(B3:B11))/9
解释:ISBLANK(B3:B11) 这个函数是找出 B3:B11 为空的单元格,如果单元格为空,就返回true。
备注:这个案例中中还有一个小技巧,在excel中
输入√这个符号的方法是:alt+4120数字 即可;
输入×这个符号的方法是:alt+41409数字 即可 。
这个小技巧考勤的时候也很实用哦
案例二:表格信息如下 , 标记打卡异常
说明: 上班和下班都是需要打卡的,如果上班打卡列和下班打卡列中有一个为空白,则为异常。异常情况在备注列显示“异常”
具体操作如下图:
ISBLANK函数方式:在D15单元格中输入公式:=IF(OR(ISBLANK(B15:C15)),"异常","")
思路:B15:C15在这些单元格中,如果有一个为空,就显示异常。
案例三:表格信息如下,统计每个科目不及格的人数 (这里使用ISLOGICAL函数来实现)
具体操作如下图:
ISLOGICAL函数在B38 单元格中输入公式:=SUM(--ISLOGICAL(IF(B27:B37<60,TRUE,0)))
解释:IF(B27:B37<60,TRUE,0) 这个函数中得到的是 0和true组成的数组,其中true就是逻辑值。
在外层加上ISLOGICAL函数 就统计出数组中的逻辑值个数啦。
在这里为了使用ISLOGICAL函数把简单的例子反而复杂花了,大家也可以实用之前的办法统计哦
如果觉得有对您有帮助,请点个赞,也欢迎评论区提问
点击头像关注我,了解更多excel
你的转发 我的10W+
我是爱excel的小编。
excel函数公式 - ?
一个财务新手的Excel学习笔记
Opal
展开
一、行政管理
1、 员工所属的快捷、选择性输入
数据→数据有效性→数据有效性(excel2016版为数据验证)→允许(序列)→来源(财务部,人事部,销售部……)注意:中间用英文逗号隔开
2、 设置身份证号码的长度
数据有效性→允许(文本长度)→数据(等于)→长度(5)
3、 格式的更改
选中单元格→打开单元格下拉格式菜单→常用的格式都在这里了。
4、 根据员工的出生日期计算年龄
选择年龄单元格,输入公式(G3为对应的出生日期单元格):
=DATEDIF(G3,TODAY(),"Y")
计算工龄的方法同上,只不过是以入职时间为准
表示使用DATEDIF函数返回从G3单元格中的日期到当前日期的年数。
5、 统计公司中所有男员工或女员工,或者各部门、各职位的员工数
使用COUNTIF函数,其语法为:COUNTIF(range,”criteria”)
Range表示要计数的单元格的范围,Criteria表示确定哪些单元格将被计算在内的条件
如:计算C3到C20单元格内女员工的人数,则公式应写为:
=COUNTIF(C3:C20,”女”)
6、 筛选出4月过生日的员工
筛选→按日期筛选→期间所有日期→二月
7、 插入smartArt图形的应用
工作表在插入SmartArt图形后会功能区中有“设计”和“格式”两个选项卡可以对其填充、样式的那个进行设计。
动图
1
二、工资管理
1、为各部门、各职位的员工设定基本工资
假设开发部为1500,基础部为1200,人事部为1000
输入公式:
=IF($C3=”开发部”,1500,IF($C3=”基础部”,1200,IF($C3=”人事部”,1000)))
或:
=LOOKUP($C3,{"开发部",1500;"开发部",1500;"开发部",1500})
2、计算加班费
加班费的计算方法为:基本工资/30/8×1.5×加班时间=加班工资
即加班费为每小时基本工资的1.5倍
输入公式为:=ROUND($E3/30/8,0)*1.5*$F3
0表示保留0位小数点
3、出勤扣款
事假扣款:=ROUND($E3/30,0)* $F3 每天扣一天的基本工资
病假扣款:=ROUND($E3/30,0)*0.5* $F3 每天扣一天基本工资的一半
迟到扣款:=$J3*50 每迟到一次扣50元
4、计算个人所得税
应纳税所得额=应发工资合计—应扣“三险一金”合计—费用扣除额(3500元)即不超过3500的不用交税
应纳个人所得税税额=应纳税所得额×适用税率—速算扣除数
假如A2中是应税工资,则计算个税公式为:
=5*MAX(A2*{0.6,2,4,5,6,7,9}%-{21,91,251,376,761,1346,3016},)
三、其他
1、复制工作表
将鼠标指针移动到要复制的工作表的标签上,按住鼠标左键,同时按住“Ctrl”键向右拖拽,即可复制该工作表
2、分类汇总的使用
先将数据按升序排列(为了使数据分类排列),选择“数据”—“分级显示”—“分类汇总”,在弹出的对话框中选择要汇总的依据和汇总后要输出的数量和金额,“确定”,即可将其分类汇总并分为不同的级别(在表格的左侧显示),可将数据展开或收缩。
例:如下图所示的工资表,要求按公司和部门进行汇总。
操作步骤:
第1步对源表按公司和部门进行排序。这是分类汇总的前提条件。排序后如下图所示。
第2步选取全部数据表,数据 - 分类汇总。在打开的分类汇总窗口中进行如下设置。
第3步再次打开 数据 - 分类汇总,设置分类字段为“部门”并去掉“替换当前分类汇总”
进行以上操作后,分类汇总已完成。
删除分类汇总的方法: 选取汇总后的表格,在分类汇总窗口上点击“全部删除”按钮。
3、创建数据透视表
先选择单元格区域,然后选择“插入”—“数据透视表”,在弹出的对话框中选择区域后,即可得到一个空的数据透视表,添加行、列、数据标签后就可以对数据进行数据汇总。
4、统计一组数据中的最大值、最小值、平均值
最大值:=MAX(E3:E37)
最小值:=MIN(E3:E37)
第二大值:LARGE(A1:A10,2)
第二小值:SMALL(A1:A10,2)
平均值:=AVERAGE(E3:E37)
兰色说:这个笔记的来源已无可知,对于很多老手来说有些简单。但对于新手来说,每学会一个excel小知识点,都会对工作有很多的帮助。就如兰色当年初学SUMIF函数后,就解决了供货商货款汇总难题。
- ?
Excel批量提取指定数据——函数篇
封怜阳
展开
上一节课中我们分享了利用数据分列功能,批量提取QQ号,那么今天我们将分享如何利用函数对指定数据进行批量提取;
同样通过观察发现,每行数据里每个QQ号码都由一对括号包括,如果左括号前面的字符数量固定,那么我们直接用mid()函数和len()函数组合,即可提取数据,但是左括号前面的字符数是不固定的……
整体思路:我们先想办法确定每行的左括号在本行中的位置,也就是第几个字符,然后利用mid()函数从左括号位置+1位开始截取,截取多长呢?
由于QQ号码的位数也不固定,那我们就利用len()函数算出每行字符的总长度,然后利用这个总长度减去利用find()函数算出来的位置数(也就是左括号以左的字符长度),然后再减去1,这个减1是减掉右括号。
好,既然整体思路有了,那我们就开始下面的吧:
1、B列新建辅助列,并命名“步骤1”,在B2中输入公式=FIND("(",A2),双击向下填充,得到如下结果;
函数注释(百度百科解释):
FIND(find_text,within_text,start_num)
Find_text 是要查找的字符串。
Within_text 是包含要查找关键字的单元格。就是说要在这个单元格内查找关键字
Start_num 指定开始进行查找的字符数。比如Start_num为1,则从单元格内第一个字符开始查找关键字。如果忽略 start_num,则假设其为 1。
2、C列新建辅助列,并命名“步骤2”,在C2单元格中输入公式=LEN(A2),双击向下填充,得到如下结果;
函数注释(百度百科解释):
Len( text)
Len(string| varname)
Len 函数的语法有下面这些部分:
string 任何有效的字符串表达式。如果 string 包含 Null,会返回 Null;
Varnam 任何有效的变量名称。如果 varname 包含 Null,会返回 Null。如果 varname 是 Variant,Len 会视其为 String 并且总是返回其包含的字符数。
3、D列新建辅助列,并命名“步骤3”,在D2单元格中输入公式=MID(A2,B2+1,C2-B2-1),双击向下填充,得到如下结果;
什么?为什么mid()函数第二个参数里多加了一个1啊,这是因为我们B列是查找的左括号在位置,所以要从这个位置的下一个字符开始取了,而第三个参数最后再减1,也是为了不取最后那个个右括号,大家可以改变一下这里的参数,看看返回的数值就可以了。
函数注释(百度百科解释):
MID(text, start_num, num_chars)
text 必选。(字符串)表达式,要被截取的字符。如果该参数为Null,则函数返回Null;
start_num 必选。数值表达式,从左起第几位开始截取;
num_chars 必选。(数值)表达式,从Start参数指定的位置开始,要向右截取的长度。如果省略,将指定为从Start参数位置开始向右到字符串结尾的所有字符数。
好了,今天的课程就分享到这里了,喜欢的朋友们关注并分享,下节课再见。
- ?
Excel vlookup函数的多条件操作实例及if{1,0}数组组合剖析
姬尔
展开
Excel 中的 vlookup函数可结合 if 多条件查找,并且可用数组作为 if 的条件,这样可以同时查找多个字段,例如查找服装销量表中分类为衬衫且价格为85元的服装。文章先列举了四个vlookup函数的多条件操作实例,然后对它们逐一剖析,主要剖析if{1,0}数组条件;四个操作实例分别为:实例1:条件用 IF{1,0}、实例2:条件用 IF{0,1}、实例3:两列连接查找,条件用 IF{1,0}与用 & 连接查找区域和实例4:两列连接查找,条件用 IF{0,1}与用 & 连接查找区域。实例中操作所用版本均为 Excel 2016。
一、Excel vlookup函数的多条件操作实例
(一)实例1:条件用 IF{1,0}
1、假如要从服装销量表中找出价格为85元的服装名称。在 A12 单元格中输入要查找的价格,例如 85,把公式 =VLOOKUP(A12,IF({1,0},C2:C9,B2:B9),2,0) 复制到 B12 单元格,按回车,则返回“粉红短袖衬衫”,这件衬衫的价格恰好是 85 元,说明结果正确;操作过程步骤,如图1所示:
图1提示:若表格中有多件85元的服装,则vlookup函数只返回第一件符合条件的服装。
2、公式简析(详细剖析见下文)
公式中查找区域为 IF 数组条件,数组由 1 和 0 组成,1 表示 True(真),0 表示 False(假);执行公式时,先从数组中取 1,由于 1 为真,所以从 C2:C9 中返回一个值;然后再从数组中取 0,由于 0 为假,所以从 B2:B9 中返回一个值;如此反复,直到遍历完 C2 到 C9 与 B2 到 B9。
(二)实例2:条件用 IF{0,1}
1、同样要从服装销量表中找出价格为85元的服装名称。在 A13 单元格输入 85,把公式 =VLOOKUP(A13,IF({0,1},B2:B9,C2:C9),2,0) 复制到 B13 单元格,按回车,则同样返回“粉红短袖衬衫”,与实例1的结果一样,操作过程步骤,如图2所示:
图22、公式简析
公式与实例1相比,IF 的数组条件与只是把 1 和 0 交换了次序,由 {1,0} 变为 {0,1};公式执行时,先从数组中取出 0,由于 0 为假,所以从 C2 到 C9 中返回一个值;然后再从数组中取 1,由于 1 为真,所以从 B2 到 B9 中返回一个值;如此反复,直到遍历完 B2 到 B9 与 C2 到 C9。从执行过程来看,取出值的顺序与实例1完全一样,因此返回同一个结果。
(三)实例3:两列连接查找,条件用 IF{1,0}与用 & 连接查找区域
1、假如要从服装销量表中找出“小类”为“衬衫”、“价格”为85元的服装名称。在 A12 输入“衬衫”,B12 输入 85,把公式 =VLOOKUP(A12&B12,IF({1,0},D2:D9&E2:E9,B2:B9),2,0) 复制到 C12,按 Ctrl + Shift + 回车,则返回服装名称同样为“粉红短袖衬衫”,操作过程步骤,如图3所示:
2、公式说明:
A12&B12 是要查找的值,A12 与 B12 用连接符号 & 连接起来,结果为“衬衫85”;查找区域的条件同样用 {1,0} 数组条件;公式执行时,先取 1,由于 1 为真,所以从 D2:D9&E2:E9 中返回一个连结值(例如 D2&E2);然后取 0,由于 0 是假,所以从 B2:B9 中取一个值取来;如此反复,直到遍历完 D2&E2 到 D9&E9 与 B2 到 B9。
(四)实例4:两列连接查找,条件用 IF{0,1}与用 & 连接查找区域
1、同样要从服装销量表中找出“小类”为“衬衫”、“价格”为85元的服装名称。在 A13 输入“衬衫”,B13 输入 85,如图4所示:
图42、把公式 =VLOOKUP(A13&B13,IF({0,1},B2:B9,D2:D9&E2:E9),2,0) 复制到 C13 单元格,如图5所示:
图53、按 Ctrl + Shift + 回车,同样返回与实例3一样的服装“粉红短袖衬衫”,如图6所示:
图64、公式说明:
与实例3相比,实例4只是 IF 的数组条件由 {1,0} 变为 {0,1},IF 条件的真假返回值相互调换,即 D2:D9&E2:E9 与 B2:B9 的位置相互调换。经此调换,公式执行所返回的值与实例3一样,所以能查到与实例3一样的结果。
二、Excel vlookup函数与if{1,0}数组组合剖析
(一)实例1剖析
公式为:=VLOOKUP(A12,IF({1,0},C2:C9,B2:B9),2,0)
1、Excel 横向数组与纵向数组的区别。横向数组是占一行两列,元素之间用“,”分隔,例如 {1,0} 为横向数组;纵向数组占一列两行,元素之间用“;”分隔,例如 {1;0} 为纵向数组。
2、if 条件分解
由于 {1,0} 为横向数组,C2:C9 与 B2:B9 之间共有八行,因此 IF 的三个参数要分为八组,即:
参数1分解为:{1,0; 1,0; 1,0; 1,0; 1,0; 1,0; 1,0; 1,0;}
参数2分解为:{C2,C2; C3,C3; C4,C4; C5,C5; C6,C6; C7,C7; C8,C8; C9,C9;}
参数3分解为:{B2,B2; B3,B3; B4,B4; B5,B5; B6,B6; B7,B7; B8,B8; B9,B9; }
3、公式执行时,if 条件组合
A、第一次执行,分别从三个参数中取第一个元素(即从参数1中取 1,从参数2中取 C2,从参数3中取 B2),组成 IF(1,C2,B2),由于 1 为真,所以取 C2。
B、第二次执行,分别从三个参数中取第二个元素,组成 IF(0,C2,B2),由于 0 为假,所以取 B2。
C、第三次执行,分别从三个参数中取第三个元素,组成 IF(1,C3,B3),由于 1 为真,所以取 C3。
D、以此类推,直到遍历完 C2 到 C9 和 B2 到 B9。
(二)实例2剖析
公式为:=VLOOKUP(A13,IF({0,1},B2:B9,C2:C9),2,0)
实例2 与实例1 只是数组元素 1 和 0及真假条件调换了位置,剖析方法与实例1一样。
1、if 条件分解
由于 {0,1} 同样为横向数组,B2:B9 与 C2:C9 之间共有八行,因此 IF 的三个参数要分为八组,即:
参数1分解为:{0,1; 0,1; 0,1; 0,1; 0,1; 0,1; 0,1; 0,1;}
参数2分解为:{B2,B2; B3,B3; B4,B4; B5,B5; B6,B6; B7,B7; B8,B8; B9,B9; }
参数3分解为:{C2,C2; C3,C3; C4,C4; C5,C5; C6,C6; C7,C7; C8,C8; C9,C9;}
2、公式执行时,if 条件组合
A、第一次执行,分别从三个参数中取第一个元素,组成 IF(0,B2,C2),由于 0 为假,所以取 C2。
B、第二次执行,分别从三个参数中取第二个元素,组成 IF(1,B2,C2),由于 1 为真,所以取 B2。
C、第三次执行,分别从三个参数中取第三个元素,组成 IF(0,B3,C3),由于 0 为假,所以取 C3。
D、以此类推,直到遍历完 B2 到 B9 和 C2 到 C9。从执行的结果来看,实例2与实例1每次执行返回的结果一样。
(三)实例3剖析
公式为:=VLOOKUP(A12&B12,IF({1,0},D2:D9&E2:E9,B2:B9),2,0)
实例3 与实例1 的原理是一样的,只是实例3的查找值和查找范围用连接符号 & 把两列连接成一列,剖析方法与实例1也一样。
1、if 条件分解
由于 {1,0} 为横向数组,D2&E2 到 D9&E9 与 B2 到 B9 之间共有八行,因此 IF 的三个参数要分为八组,即:
参数1分解为:{1,0; 1,0; 1,0; 1,0; 1,0; 1,0; 1,0; 1,0;}
参数2分解为:{D2&E2,D2&E2; D3&E3,D3&E3; D4&E4,D4&E4; D5&E5,D5&E5; D6&E6,D6&E6; D7&E7,D7&E7; D8&E8,D8&E8; D9&E9,D9&E9;}
参数3分解为:{B2,B2; B3,B3; B4,B4; B5,B5; B6,B6; B7,B7; B8,B8; B9,B9; }
2、公式执行时,if 条件组合
A、第一次执行,分别从三个参数中取第一个元素,组成 IF(1,D2&E2,B2),由于 1 为真,所以取 D2&E2。
B、第二次执行,分别从三个参数中取第二个元素,组成 IF(0,D2&E2,B2),由于 0 为假,所以取 B2。
C、第三次执行,分别从三个参数中取第三个元素,组成 IF(1,D3&E3,B3),由于 1 为真,所以取 D3&E3。
D、以此类推,直到遍历完 D2&E2 到 D9&E9 与 B2 到 B9。
实例4与实例2和实例3类似,剖析方法也相同,可以自己尝试剖析以加深理解。
- ?
Excel实用技巧分享:学会这两种方法,再也不为制作斜线表头发愁
清溪蝶
展开
在日常制作表格的过程中,难免会遇到需要制作斜线表头的时候,但excel本身并没有现成的斜线表头,这时就需要我们自己动手进行绘制,如何才能制作出美观的斜线表头呢?我们使用最多的斜线表头也就两种:两栏斜线表头和三栏斜线表头。下面我就与大家一一分享它们的制作过程。
一、两栏斜线表头
两栏斜线表头的制作方法有两种:(1)利用单元格设置来制作;(2)利用“直线”工具绘制。
1、利用单元格设置制作斜线表头的步骤如下:
(1)将A1单元格调整到合适的宽度和高度后输入“姓名”,按“ALT+ENTER”组合键换行后,再输入“班级”,然后将对齐方式设置为“左对齐”。
(2)选中A1单元格,按“CTRL+1”打开“设置单元格格式”对话框,选“边框”选项卡,点“右斜线”。
(3)利用空格键把“班级”和“姓名”调整到合适位置即可。
利用“直线”工具绘制(1)将A1单元格调整到合适的宽度和高度后输入“姓名”,按“ALT+ENTER”组合键换行后,再输入“班级”,然后将对齐方式设置为“左对齐”。
(2)依次单击【插入】→【形状】→【线条组中的直线】后,鼠标变为“+”,将“+”放在A1单元格的左上角,按住鼠标左键后拖动到A1单元格的右下角后松手(为了更加精确的对准单元格左上角,可以按住“ALT”键的同时拖动鼠标)。
(3)将光标放到“姓名”前,利用空格键进行调整即可。
二、三栏斜线表头
三栏斜线表头的制作方法只有一种,那就是利用“直线”工具自己动手绘制,具体步骤如下:
(1)在A1单元格中输入三个标题名称,按“ALT+ENTER”组合键换行,并添加空格将三个标题调整到下图的位置。
(2)依次单击【插入】→【形状】→【线条组中的直线】后,鼠标变为“+”,将“+”放在A1单元格的左上角,按住鼠标左键后拖动到合适的位置后松手。
(3)重复第(2)步。
以上就是两栏斜线表头和三栏斜线表头的制作方法,但是这种具有中国特色的功能,在实际制表过程中还是要少用,因为在表格中进行了单元格合并或者是添加了斜线表头后,对后续的“汇总”会有一定的影响。
如果今天分享的内容对你的工作有所帮助的话就点个赞鼓励一下吧,也可以分享出去给其他需要的朋友。如果你在excel使用过程中遇到了什么难题的话,也可以在评论区留言,大家一起讨论,相互帮忙。
- ?
「每天1分钟」Word插入Excel的2种方法
若雁
展开
一、利用复制粘贴插入Excel内容
第1步:在Excel中,利用鼠标选取需要插入的数据范围并复制,如图下图。
第2步:在Word中,选取要插入Excel内容的位置,右键单击,在弹出的快捷菜单中选取要粘贴选项的格式即可。
二、直接插入Excel表格
第1步:打开Word文档,将鼠标光标移至需要插入Excel表格的文档位置。切换至“插入”选项卡,在“文本”选项组中单击“对象”,对象类型选择“Microsoft Excel工作表”,然后单击“确定”按钮,如下图。
第2步:Word中会插入Excel界面,并且有空白Excel表格。
- ?
「每天1分钟」Excel中使用频率最高的函数(2)
醉山
展开
OUNT函数
主要功能:计算含有数字的单元格的个数。
使用格式:COUNT (number:number) number代表需要求绝对值的数值或引用的单元格,参数不超过30个。
应用举例:在F15单元格中输入公式:COUNT (A1:E15),则能算出所选区域含数字的单元格个数;在F15单元格中输入公式:COUNT (A1:A15,E1:E15),则能算出所选A列和E列含数字的单元格个数。
MAX函数和MIN函数
主要功能:单元格区域中找到最大和最小的数值。
使用格式:MAX (number:number) MIN (number:number)
应用举例:在F15单元格中输入公式:MAX (A1:E15)或MIN (A1:E15)则能找到所选区域的最大值或最小值。
- ?
数据分析技巧:如何将Excel二维表转一维表?
Gan
展开
一维表,二维表分别是什么?
“维”是指物质在时空中的衡量参数。一维是线,二维是面。
然而Excel表哥的维度,与它们根本一点亲戚关系都没有。那什么是一维表,二维表呢?相信许多表亲们现在都还搞不清楚吧!
举个栗子,如下图哪个是一维表,哪个是二维表呢?
判断数据表是一维表还是二维表最简单的一个方法就是看列的内容,看每一列是否是一个独立的参数。如果每一列都是独立的参数那就是一维表,如果有两列及以上都是同类参数那就是二维表。
在数据关系上来说,二维表有一个很明显的特征,就是列字段中有一部分是相同性质的字段。而一维表没有任何两个字段是相同性质的。
通过上面的判断依据,很显然:图1左侧的表是一维表,它的类别、产地是两个不同的列参数;而右侧的是二维表,它的1月、2月、3月属于一组性质相同的“月份”参数。
二维转一维有什么用呢?
在数据统计分析中,要求所有的Table数据都必须是一维,以便于数据能够方便地进行管理。原则上,一维表的数据再加工比二维表的要简单得多,所以我们提倡在做数据管理的时候,基础数据要采用一维表。
我们举一个简单例子: 从图2可以看出它是个二维统计表,因为表哥录入数据的时候把年份放在列上了,最近老板要求他重新对2008年的各类茶叶的产量进行综合统计,必须一行要一个分类一个年份一个产量,如图3所示,以便后续能够快速进行数据跟踪。
怎么实现呢?许多表亲们会说,很简单啊,手动复制就可以。但你仔细想想,图2数据较少是比较容易,可如果数据有几千条、几万条呢?手动复制不得累死。因此,我们需要将二维表转为一维表格。
如果是一维数据表,只需通过单击表头右侧的下拉按钮,在弹出的对话框中的筛选出选中想要的数据,这里选择中2008选项,即可统计出数据。是不是就非常方便了?
通过上面的例子可以看出,我们只有把二维表转化为一维表的形式,才能以它为数据源快速地得到其他数据。那么,二维表转化一维表步骤是怎样的呢?下面请看具体操作方法:
EXCEL二维表如何转一维表?
表哥向我诉苦,说是老板要求真多,“好好的一个表格,非要搞成什么一维表,不是明摆着为难人嘛”。其实二维表转一维表并不难。下面,我就来教教各表亲们如何在Excel 2016中玩转数据改造,将二维表转为一维表。
我们先来看看二维表改造前的样子。
改造成一维表后,变成如下图所示的效果,怎么实现呢?
那么我们就要对表格的形象进行改造,具体操作方法如下:
第一步:按快捷键ALT+D→P(此处先按下Alt+D键,然后放掉,再按Alt+P键),在弹出的对话框中按下图进行设置,并单击下一步。
第二步:此时,页字段数目选择“创建单页字段”,别犹豫,我们继续下一步改造。
第三步:单击
,然后在表格中拖动鼠标选定数据目标区域,再继续下一步。
第四步:选择你改造后表格的位置,“新建工作表”或者“现有工作表”都可以,看您心情。这里我们选择 “新建工作表”。
第五步:此时,表格已有很大改变,如图8。接下来,见证奇迹的时刻,在表格右侧,在“数据透视表字段”窗格中,一般会自动勾选,如果没勾选,选中“行”“列”“值”三项。
第六步:OK!Excel会自动创建一个新的工作表,基于原二维表形成的一维表。
复合表格怎么办?
然而,并不是所有二维表都这么好对付。这不,表哥又遇到了麻烦。问小编我下图10中这类“产地”比较复杂的远房表格,即常见的混合二维表(又称复合表格),应该怎么将其转换为一维表呢?
像这类复合表格,就不得不用出Excel 2016的大招,表格专用“查询编辑器”,下面来看看具体操作方法:
第一步:首先,单击表格的任意位置或者选中整个表格,再选择“数据”-“从表格”,如下所示。
Excel将自动选择表格区域。然后选中“表包含标题”复选框,单击“确定”按钮,如下所示。
第二步:打开“表11-查询编辑器”。此时,混合表中的数据将自动上载到编辑器中,如下所示。
第三步:选中表格中多重数据的区域,如,“产地”列,单击“拆分列”按钮,如下图所示。
第四步:因为多重数据中是用顿号进行间隔的,所以这里选择“按分隔符”,如下所示。如果每个标签字符相同,则可以选择“按字符数”。
第五步:弹出“按分隔符拆分列”对话框,在下拉列表中选择“自定义”选项,在输入框中输入“顿号”,再单击确定。
第六步:此时,将变成如下图所示的表格。这里选中混合表中的产地区域。
第七步:选择“转换”选项卡,单击“逆透视其他列”按钮。
第八步:表格变成如下所示的效果。再选择“属性”列,单击鼠标右键,在弹出的菜单中选择“删除”命令,删除该列。
第九步:选择“开始”选项卡,单击“关闭并上载”按钮,关闭表格查询编辑器。
此时,看看数据,复合二维表已经转换成如下所示的一维表,大功告成。
技巧拓展:
工作中,表格数据需要改造的种类非常多。那么,根据实际情况表亲们可选择不同的改造的方法和技巧。譬如,对于简单的一维表或二维表数据改造,除了前面介绍的方法之外。表亲们还可以使用行、列互转的方法来快速实现。
首先选中要转置的源数据区域并复制,鼠标停留在一个空白单元格区域。然后右键选择黏贴——选择性粘贴——转置。或者复制并选择空白单元格之后,直接按Ctrl+Alt+V键,在弹出菜单中勾选“转置”复选框,确定即可完成转置。|内容来源:中国统计网
百家号-【袁帅数据分析运营】运营者:袁帅,会展业信息化、数字化领域专家。新社汇平台联合创始人,微会动平台创始人。永洪数据科学研究院MVP。认证数据分析师、网络营销师、SEM搜索引擎营销师、SEO工程师、中国电子商务职业经理人。畅销书《互联网销售宝典》联合出品人。
- ?
如何在1分钟内完成2份Excel数据核对
彼岸花
展开
相信很多人有过这样体会,要进行2份Excel数据的核对。
看着密密麻麻的数据,人都不好了,其实核对数据完成可以用点巧劲。
今天就来分享一下数据核对的技巧。
如下面两张数据表,分别是李二狗和王翠花对货物的检测结果。现在需要快速找出检测结果的不同之处。
第1步:如下图所示,选中李二狗的检测结果数据,按下Ctrl+C键进行复制。
第2步:切换到王翠花检测员的数据表中,选中相同的数据区域,即需要进行数据核对的区域,选择【粘贴】菜单中的【选择性粘贴】选项。
第3步:在【选择性粘贴】对话框中,选择【减】选项,表示在粘贴数据的同时进行减法运算,最后单击【确定】按钮。
第4步:查看结果。此时在王翠花检测员的检测结果中,效果如下图所示,不为0的数据就表示这是与李二狗检测员数据不相符的地方。例如E11单元格为-4.5,表示这个数据的检测结果是,李二狗的数据比王翠花的数据大4.5mm。
总结:在复制粘贴数据的同时,使用减法运算,可以快速让一份数据减去另一份数据,如果相减结果不为0,就表示数据不相同。此方法只适合用来核对数据区域相同的报表数据。并且只对数值型数据有效,对检查文字是否相同无效。
- ?
Excel插入3000名员工一一对应的照片到单元格,原来只要2分钟?
支向真
展开
应用场景:HR在进行员工档案管理时,需要插入与员工一一对应的照片,怎样能够快速的导入3000名员工照片到工作表的单元格中,并且照片与员工姓名一一对应,并实现下图的效果呢?
批量导入图片到单元格具体操作方法(案例用时2分钟,3000员工用时差不多)
1.先将图片裁剪成相同大小,一般员工照片我们可以按照1寸照片的尺寸裁剪(宽2.5厘米,高3.5厘米)。
2.为每张图片命名,图片名称要与员工名称完全一致。(如需批量重命名可看往期教程《EXCEL重命名3000多个文件,只用了2分钟,原来他是用了这个方法!》)
3. 在H2单元格录入:
5. 在K2单元格写入公式:=H2&I2&B2&J2
6. 向下填充所有公式,将K列公式复制粘贴到文本文件中。
7. 在G2单元格中插入一张图片,方法是:【插入】→【图片】→选择要插入的图片→【确定】
8. 根据插入的图片调整所有单元格的行高和列宽,要求与照片大致匹配。
9. 删除插入的图片(图片只是调整行高和列宽的参照)
10. 复制刚刚粘贴到文本文件的内容,回到Excel中,右击【选择性粘贴】,选择【Unicode文本】
11. 右击【设置图片格式】→【大小与属性】→【重设】→勾选【锁定纵横比】,属性选择【大小固定,位置随单元格而变】
扩展应用:此案例还可用于仓库入库清单,设备清单、产品清单等。
如果我的分享对您有帮助,请欢迎点赞、收藏、评论、转发,更多的EXCEL技能,可以关注百家号“EXCEL学习微课堂”。如需导入图片到批注VBA代码文件,请关注、转发后私信联系我。
我们尽快联系您