听故事学Excel转载Word文档格式.docx
- 文档编号:17635650
- 上传时间:2022-12-07
- 格式:DOCX
- 页数:49
- 大小:1.76MB
听故事学Excel转载Word文档格式.docx
《听故事学Excel转载Word文档格式.docx》由会员分享,可在线阅读,更多相关《听故事学Excel转载Word文档格式.docx(49页珍藏版)》请在冰豆网上搜索。
\DOCUME~1\Owner\LOCALS~1\Temp\[検収報告書
后来今朝不知从哪里得知,这是路径,只有跨工作薄才会出现。
知道这一点后,今朝松了一口气,公式现在缩短了一大半。
又经过了几天的摸索,今朝对这个函数的基本用法已经了解。
在茫茫人海中,想查找到一个叫“我是菜鸟”的人的性别,已知人海的第一列为姓名,第二列为性别。
=VLOOKUP(A1,人海,2,0)
VLOOKUP的查找,90%的情况下为精确查找,也就是第4参数为0,如果第4参数为1或者省略即为模糊查找。
思考题:
1、如果设备为空调或者风扇,就可以睡得着,否则睡不着,怎么设置公式?
2、如何根据姓名简称,获取性别?
二、勇敢踏出第一步
今朝虽然学了两个函数,但工作依旧没有任何变化。
但每次制作“出货检查不良报告”的时候,总是无意识地对品名列多看了几眼。
来日企两年了,到现在今朝都没学会日语输写,想想也挺可笑的。
报告每次做完99%,就剩品名让领导输入,时间久了领导也就习以为常。
不过今朝却在想解决之策,怎么将VLOOKUP函数运用到这里。
时间一天天地过去了,但事情依然没进展。
现在难点就是找到一份产品清单,里面列举所有产品返回对应的品名。
今朝向领导打听,部门内是否有我们公司所有产品清单?
领导的答复让今朝的心情一下子陷入低谷,曾经菜头试图找这一份清单,翻遍了各台电脑依旧没有发现,后来得知只有日本总公司才有。
没有清单,会VLOOKUP也没用。
此事就告一段落。
忽然有一天,今朝收到日本的Excel档成绩书,里面有十多款产品。
今朝看到这些成绩书,突然脑袋里闪出这样的念头:
自己制作产品清单。
当然这个清单只是隆成这边而已,全部供应商的产品清单想都不用想,几万款产品。
有想法就得尝试,今朝将隆成的所有产品番号一一罗列出来,将成绩书上有的命名复制黏贴到产品清单里,这样就完成了一小半。
还好今朝想到了自己的老乡会写日语,就麻烦她将剩余的命名输入进去。
经过半天的时间,终于完成了产品清单,太难能可贵了。
不会日语的人真伤不起!
有了这份清单,设置品名查找公式就变得轻而易举。
根据以前的表格,依样画葫芦。
这个公式会将查找不到的值显示成#N/A,在这里本来是没多大影响的。
后来有一天学到了一个新函数IFERROR,有人也许会问,这个是干嘛用的,跟前面又有何关系?
函数语法:
=IFERROR(错误值,显示值)
就是让错误值显示成你想显示的任意值,不是错误值显示本身。
2-4.png
(20.84KB,下载次数:
1)
下载附件
保存到相册
2013-8-519:
59上传
最终,今朝将公式改成:
=IFERROR(VLOOKUP(B10,产品清单!
B:
C,2,0),"
"
虽然在这里进行容错处理意义不大,但起码能将所学第一时间用上。
另一种解释就是显示错误值不好看,显示成空白好看点。
在不良报告设置公式,虽然对今朝而言没什么,但对领导而言却是一种解脱,让领导省去无数次输入品名的麻烦。
后来这个模板在公司内部悄悄地流行起来,今朝也因此受到领导小小的表扬。
三、原来统计并非手工活
今朝虽然学了三两函数,但依然还是菜鸟。
其实,严格来说只能算路上菜,也就是走在通过菜鸟的路上。
Ricell公司每年都有组织十个左右上日本学习,郭小姐也申请去日本。
她一去日本,今朝就得顶替她的工作。
今朝这人最不擅长沟通,所以对于经常需要跟供应商沟通的组长并不感冒,正所谓无官一身轻。
今朝本想推脱,但这一职位今朝是最合适的人选,除了他没人熟悉中山那边的情况。
既来之,则安之。
还是用点心事在交接的事儿上面才是正道,要不然郭小姐一走,今朝就只有哭的份儿。
常用表格交接
只见郭小姐熟练的打开《隆成每天不良记录表》,这是中山隆成的不良记录表,每天都要记录下每一款产品的不良数量,有颜色填充部分是事先设置好的公式,会自动帮你统计。
今朝点击了不良数列的单元格,发现真的存在一条公式。
不良数故名思议就是对每天的不良数量进行汇总,其实叫总不良数更合适,SUM应该就是求和的意思。
其他两个公式为:
不良率
=IF(C5="
C5/$DU$30)
次数
=COUNTA(I5:
DU5)
除了IF外,虽然知道其他两个函数的大概意思,但怎么使用函数不太清楚,就像郭小姐请教。
郭小姐说SUM函数使用很简单,点击C5单元格→“公式”→“自动求和”,最后用鼠标选择求和区域就行。
这个COUNTA函数就藏得深一点,“公式”→“其他函数”→“统计”,浏览到COUNTA,这个函数的用法跟SUM一样。
今朝跟着试了一遍,基本就记住了。
以前一直用计算器,统计数据非常慢,有了这两个函数,统计又上了一个台阶。
郭小姐看今朝明白了,就打开了第二张表《隆成月度统计表》,这里面有填充色的已经设置好公式,还有最小、最大、合计、平均这些都设置好公式,你只要填入一些基础数据就行。
今朝粗略看了下,也没问什么。
郭小姐就继续说了一些工作上注意事项、生活注意事项,说了好久。
不过今朝基本上也没记住她说什么,就如读书的时候:
左耳进,右耳出,管老师说得天花乱坠。
不过对这两张表格,今朝却很感兴趣,利用下班的时候,又看了几次。
无意间点开了“自动求和”的下拉按钮,真是坑爹,居然还有这么多有关的函数。
估计很多人都跟今朝一样,看到这些无限感慨。
以为这里就只有SUM函数而已。
思考题
1、文中郭小姐向今朝介绍用COUNTA统计数字次数,这种统计方法是否正确?
如果不正确,请说出理由。
思考题释疑
OR与AND
逻辑函数的使用频率很高,虽然简单,但必须掌握好。
先看OR,函数语法:
OR(条件1,条件2,条件n):
只要满足其中一个条件就显示TRUE。
=IF(OR(A2="
A2="
空调"
),"
这个相对简单,就不做多余的解释。
跟OR很像的一个函数AND,函数语法:
AND(条件1,条件2,条件n):
只有满足全部条件才显示TRUE。
以现在Excel当选MVP来说明,最基本的要求就是:
精通Excel某方面的内容(如函数与公式)、无私帮助别人,只有这两个条件满足才能拥有成为MVP的最基本条件,只要其中一个不满足永远当不了MVP。
=IF(AND(精通Excel某方面的内容,无私帮助别人),成为MVP的基础条件,什么都不是)
说得通俗点,OR就是或者,AND就是并且。
模糊匹配≠通配符查找
对于这题,有些人用模糊匹配的方法获取性别,但今朝的模糊匹配不一定是笑看今朝,模糊匹配一般都是用在区间的判断上。
根据右边的成绩判定表获取等级
=VLOOKUP(A2,D:
F,3)
当VLOOKUP第四参数省略,也就是模糊查找时,查找不到对应值,返回小于查找值的最大值,如50查找不到对应值,就返回0的对应值;
67返回60的对应值,60就是小于67的最大值。
回到今朝跟笑看今朝上,汉字是按拼音排序的,J(今)>
X(笑),明显的不成立,也就是说用今朝查找笑看今朝将得到错误的答案。
="
今朝"
>
笑看今朝"
,返回FALSE,即不成立。
既然模糊匹配返回不到正确值,应该用什么才可以查找到对应值?
先来了解下Excel中的通配符:
?
代表一个字符,*代表所有字符。
当字符个数确定的时候就用?
,比如知道区域中都是两个字符,第二个字符为朝,就可以用公式:
=VLOOKUP("
?
朝"
{"
;
老乡"
},1,0)
而数据源很明显的字符个数不确定,所以用:
*"
&
D2&
A:
B,2,0)
忠告:
在没弄明白参数的作用时,切忌不可乱用!
COUNT、COUNTA、COUNTBLANK
3、文中郭小姐向今朝介绍用COUNTA统计数字次数,这种统计方法是否正确?
先来看下面三个函数的作用:
COUNT:
统计区域中数字的个数
COUNTA:
统计区域中非空单元格的个数
COUNTBLANK:
统计区域中空单元格的个数
看到这里可以肯定地断定郭小姐的做法是错误的,在数据区域中录入文本可能性很小,但录入空格的可能性很大,一不小心就录入一两个空格。
全面扫盲——四则运算
今朝一想到以后就要负责中山隆成那边,就发愁起来了。
以前需要处理的数据很少,用计算器还勉强能应付过去,但现在需要处理的数据越来越多,不靠Excel根本做不好。
考虑了半天,今朝打算豁出去了,买本《函数手册》来学习。
对于英语水平超级烂的今朝来说,学函数实在是一种挑战。
今朝思索着:
我能学好函数吗?
顾虑归顾虑,但难得第一次买Excel书籍,怎么也要花点心思来学习。
这段时间一直出差,一下班女同事就看电视,今朝又对电视不感兴趣,基本上很少跟她们一起看。
在这边属于断网状态,那时候还没有微博,手机能做的事情实在有限,上会QQ后就不知干嘛。
突然,今朝想起了前段时间购买的《函数手册》,所以掏出来准备学习。
为了不打击自己的自信心,今朝选择了以前接触过的函数,开始看起来。
有很多知识,如果学过后没有重新复习的话,很容易就忘记了,重复看有助于记忆。
就像很多人考试前一样,都会花点心思重新翻开书籍复习,这样以前的知识点又重新被牢记于心。
这样的日子应该持续有两个月吧,今朝每天抽出一点时间翻开书籍,对有兴趣的知识点先了解,不感兴趣的不看,如工程函数。
没想到今朝还学会了很多函数的基本用法,为此今朝兴奋了一小段时间。
还因此得出结论:
不会英语也可以学函数!
学习的目的是什么?
不就是为了能学以致用吗?
为此,今朝开始尝试自己制作模板,虽然很粗糙,但也是一种进步。
经常出差,有时关心的仅仅是每回可以拿多少补助费而已,人有时就是这么现实,不要见笑。
今朝还算有点基础,不一会儿就制作出《出差登记表》,这样一来,每一回出差就知道住宿今天,能获取多少补助。
怎么获取住宿天数呢?
首先来了解下日期知识点,日期其实就是一组序列,1900-1-1就是日期的起点,也就是序列1。
同理1900-1-2,就是序列2。
如果将日期的单元格格式设置为常规,就是日期的本身序列号。
我们知道数字是可以直接进行四则运算的,那么日期也可以。
所以,住宿天数就是结束日期减去开始日期。
默认情况下,相减单元格会自定变成日期格式,当你看到日期格式的时候不要慌,只要明白日期其实也就是普通的数字,设置为常规格式就变回来了。
补助小计的公式为:
=E5*F5
1、
日期直接相减会自动显示成日期格式,如果不设置单元格格式,怎么自动获取两个日期的相差天数,比如第一个就是4?
2、
假如日补助金额格式改变,如图所示,怎么获取补助小计?
比较运算符
以前没有出差等级表的时候,今朝也没去在意补助金额,反正领导给多少就是多少。
现在有了这份表格,就知道领导给得补助知否准确。
话说领导是怎么算补助的:
掰手指算天数,7-26,7-27……8-1,一般持续十天以上的出差很少,所以手指还够用,统计好天数后,比如5天就敲计算器=5×
30,逐个统计。
这样算其实也没什么不好,就是偶尔会出一点点小差错而已,如果能够核对两次以上估计没问题。
领导的做法今朝不敢干预,也没有领导那么有时间,所以能够借助Excel统计的东西,还是尽量借助Excel。
接着一起看Excel怎么表示区间。
作为质检人员,接触最多的是AQL抽样基准表,在某个范围内的出货数量要抽检多少数量。
那区间该怎么表示呢?
在数学中经常用1<
=X<
=500,这种表示方法,但Excel真的表示方法是否一样?
今朝尝试了,却是不成立的。
很多时候,理想跟实现就是存在差距。
在Excel首先执行1<
=30的判断,这个明显成立,返回TRUE。
接着用TRUE<
=500,逻辑值大于数字,得到FALSE。
Excel中数据的排序依据:
错误值>
逻辑值>
文本>
数字。
既然这样不行,正确的做法应该怎么做呢?
出于某个区间也就是大于等于最小值,小于等于最大值,只有满足这两个条件才能成立。
=AND(1<
=30,30<
=500)
前面说过四则运算,其实也可以用在这个地方。
=(1<
=30)*(30<
满足现实TRUE,不满足现实FALSE,然后两者相乘。
在这里TRUE可以看做是1,FALSE可以看做是0,只要都满足就显示1,否则就是0。
TRUE*TRUE=1
TRUE*FALSE=0
FALSE*FALSE=0
相对引用、绝对引用、混合引用
这一天今朝没事在办公室,回头看到覃美女在做报表。
今朝仔细一样,发现覃美女现在懂得用电脑处理数据,而没用计算器,心上暗自夸奖:
不错,有进步。
可是过了几秒中,发生了一件让今朝大跌眼镜的事情,如果今朝有眼镜的话早就碎了一地。
覃美女,输入=,接着用鼠标点击G6,输入/,接着再用鼠标点击F3。
看到这里还算正常,接着直接她一回车,到H7单元格,又重复上一操作。
啊?
这样也行?
今朝半调侃问:
怎么不用计算器算呢?
覃美女倒也回答得爽快:
用Excel算比较准。
今朝:
Excel确实挺适合计算,但你为什么不用绝对引用?
覃美女:
这个以前读书时看过,不过太久没用了,都不知道怎么用。
你输入第一条公式后,别急着回车,用鼠标选择F3,然后按【F4】键。
注意观察编辑栏的变化,自动添加了两美元($),最后向下填充公式。
还真的是这样,这样就快捷多了。
原来是按【F4】键,这回我得记牢它。
知识补充:
通过【F4】键可以不断切换各种引用方式。
相对引用:
就是行列都不给美元,这样公式复制到哪里,哪里就跟着变。
3、
绝对引用:
行列都给美元,不管怎么复制公式,就是不会变。
4、
混合引用:
只给行或者列美元,给行美元,行不变;
给列美元,列不变。
、
凭借Vlookup解疑难
截止到目前,今朝懂得不少函数,但真正熟练的就只有Vlookup。
Vlookup是一个很神奇的函数,有了它查找数据变得异常轻松。
EH的函数版每天的热闹非凡,提问者一个接一个,也正因为如此,才给今朝提供一个一展身手的机会。
其实,准确点应该叫班门弄斧。
在这众多问题中,今朝只关注一类题:
查找。
只要标题包含关键字:
查找,今朝就会打开链接,尝试解读。
一个晚上解答三五个疑难也属于正常的事,随着解读问题的增多,今朝也越来越了解Vlookup。
在10年的某一天,今朝总结了一个帖子《Vlookup函数问题汇集》,里面涉及到成9个方面,常见的基本上都有。
现在挑选其中部分内容进行说明。
VLOOKUP
在表格数组的首列查找指定的值,并由此返回表格数组当前行中其他列的值。
语法:
VLOOKUP(查找值,区域,返回区域第几列,查找模式)
模糊查找与精确查找
模糊查找必须对数据源进行升序,否则出错。
精确查找数据源即使不排序也不会有影响。
查找的结果返回多列对应值
俗称是区域中第2列,正常的话就设置公式为:
=VLOOKUP($A15,$A$2:
$F$11,2,0)
接着是3,4,5,6列的公式,依次修改第三参数为:
$F$11,3,0)
$F$11,4,0)
$F$11,5,0)
$F$11,6,0)
只有5列修改也不是很久的事,但假如是50列呢,直接就晕菜了。
有没有更好的办法呢?
答案是肯定的。
函数中有两个函数,ROW跟COLUMN,一个产生行号,一个产生列号。
这里以COLUMN进行说明。
这个函数有两种用法,一是省略参数,比如在A列输入就返回A的对应值1。
二是输入参数,如果要返回对应值2的话,可以参数写B1。
今朝喜欢使用第二种。
说到这里,公式就可以改善成这样:
$F$11,COLUMN(B1),0)
剩余的暂时不涉及,直接上传附件,有兴趣可以去了解。
温馨提示:
当你觉得很繁琐的时候,请停下来思考,也许Excel本身就有提供相应的解决方案。
Lookup的两分法是个传说
在《VLookup函数问题汇集》这个帖子发出去当天,帖子上升为24人气榜首。
昨天看了,下载量超过1000次。
这对于名人而言,或许没有什么,但对于初出茅庐的今朝而言,却是莫大的鼓舞。
帖子的回复中有一个人叫周义坤,一看资料是老乡,所以留了点心。
今朝又像往常一样回答有关查找的问题,依然使用Vlookup函数解决,而当今朝再次查看帖子的时候,后面总有一个叫周义坤的人用Lookup函数提供另一种答案。
连续好多题都是这样,也许这就是缘分吧。
周义坤首先在论坛加今朝为好友,今朝同意并用QQ添加他为好友。
两人开始在QQ聊起关于查找的问题,他反复强调Lookup好用,让今朝一定要去学习,并推荐一个帖子《Vlookup与Lookup一一过招》。
其实,今朝与周义坤不就是在上演一场真实版的两函数过招吗?
今朝抽空看了这个帖子,很受启发,同时查找了很多有关Lookup函数的资料,开始学习起来。
不过在这些帖子中提到了两分法,哎,这个说法不知道吓怕了多少初学者。
今朝也是其中一员,所以在很长的一段时间,对这个函数依然不太了解,甚至有意逃避它。
至到有一天跟提出这个两分法的神人gouweicao78聊天,才知道这个两分法可以不掌握,这只不过是个传说,即使不会照样可以学好Lookup。
聊天的时候还涉及到Lookup的另一种方法,经典查找模式,这个实在太好用了,有了它,查找就是这么简单。
接上文:
上文里面提到的两分法,其实应该叫二分法。
gouweicao78是谁?
有函神之称(Excel函数之神),跟另一个人山菊花,两人合称花草,EH的两大高手。
gouweicao78的帖子富有逻辑性,山菊花的帖子幽默有趣,各有其优点。
他们两人的精华帖,今朝基本上都用心看了多次。
扯远了,继续Lookup的话题。
gouweicao78就是靠着Lookup函数而一举成名的。
学Lookup看他的帖子最好。
不要迷恋二分法,二分法只是一个传说。
抛开这个传说,一起来见证Vlookup的神奇。
正常的话,每发一个帖子,都有有回复,现在想看最后回复的人员是谁?
=LOOKUP("
座"
B:
B)
帮助提到:
如果
LOOKUP
找不到
lookup_value
的值,它会使用数组中小于或等于
的最大值。
也就是返回最后一个对应值。
要返回最后一个对应值,只有一个办法就是找到一个比所有人员都大的值。
汉字的排序是以首字母进行排序的,如果要选择最大的一定要选择首字母包含Z的汉字。
座是一个很大的汉字,正常情况下的汉字都比它小,当然他并不是最大的。
最大的字是生僻字,说句实话,今朝也写不出来。
以后如果要返回最后一个文本,就用座,宝座的座。
有人看到这里也许会问,那最大的数字是什么?
Excel中最大的数字是9E307,也就是9*10^307,除非搞科研,否则的话,10位数的数字已经够大了。
现在一起来认识Lookup的经典查找模式:
Lookup(1,0/((条件1)*(条件2)*(条件n)),返回值)
Vlookup函数在处理逆向查找跟多条件查找是个难题,既然这样,那就单独以这两个例子来说明Lookup查找之方便。
根据订单号,逆向查找番号
只需套上模式即
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 故事 Excel 转载