Question to say "I can!"
  • MATCH函数是vlookup他爹小老婆生的

    2017-02-13

    第一次把MATCH函数理解得这么好。

    学习知识一定要触类旁通,今天就讲讲match这个神奇的定位函数。

    以前我用match函数,都是把它当辅助函数来用,也就是将它和index与offset结合起来,对它的内涵一直都是一知半解。

    直到今天,我突然发现它和某个函数的用法非常相似,就是vlookup。

    因为我对vlookup实在是太熟悉了,每天都要用这个函数进行数据查找。

    而match同学大概是vlookup他爹小老婆生的,和VLOOKUP,那个叫像啊……只是比VLOOKUP少一个参数而已。

    不仅如此,连脾气都非常像,查找值不存在或者格式不一致,MATCH也一样会闹脾气甩脸子,同样需要IFERROR函数出面摆平。

    可是MATCH同学又集中了他两个哥哥VLOOKUP和HLOOKUP的优点:竖着找和横着找都包了。

    通常MATCH函数会把找到的位置,留给INDEX和OFFSET这两位老相好。

    举个例子:

    =INDEX(A:A,MATCH(D2,B:B,))
    MATCH(D2,B:B,)部分,用MATCH函数根据D2单元格的姓名,在B列给找到位置5。
    老相好INDEX函数就根据这个位置,在A列找他的地址,返回对应的值。

    作者:admin | Categories:办公能手 | Tags:
  • 如何批量修改文件名

    2016-12-28

    如果我们有一堆的图片要批量命名,命名的规则要对应用一个表中的字段名。我们可以批量修改文件名,下面就和大家一起分享这个经验。

    1.在e盘建立一个文件夹,将所有的图片放置于该文件夹中。

    2.鼠标左键点击,开始->运行->在对话框中输入字母cmd,进入dos模式鼠标。

    3.在dos模式输入命令行“cd e:\pic”然后回车,再输入命令行“dir /b>rename.xls",可将文件夹中的文件名在rename.xls文件中列出。

    (小贴士:有的时候,你的计算机正处于中文输入状态,为确保命令行能够打出来,你可以切换成英文大写状态。)

    4.用xls打开e:\pic文件夹中的rename.xls,你会发现文件名已全部罗列在A列中,你可以在B1列中输入对应的文件文件名(这里你可以用vlookup函数从另一个表中取出对应的字段)。

    5.将A列单元格中没有用的文件名删除。

    6.鼠标选中C1单元格,并在编辑框中输入公式="ren "&A1&" "&B1&".jpg"

    7.继续用鼠标拖动C1单元格右下方的句柄。

    8.然后,鼠标选中C列,鼠标右键复制。

    9.在记事本中,粘贴刚才的命令行。

    10.鼠标点击菜单“文件”-“另存为”,输入文件名ren.bat,保存在e:\pic中,文件的保存类型选择“所有文件”。

    11.打开e盘pic文件夹,鼠标双击ren.bat这个批处理文件,即可将该文件夹下的jpg图片,按照刚才rename.xls中的顺序和文件名批量修改文件名。

    自此,你已经完成了批量修改文件名的操作。我们运用了dos命令技巧、xls技巧以及批处理3个知识点来达到批量修改文件名的效果。

    作者:admin | Categories:办公能手 | Tags:
  • word中如何将上下两行间字、字符、数字分别对齐

    2016-12-27

    在word中对文字、字符进行对齐,通常运用Txt文本文档去除原有格式,然后复制到word中,但是对原word中已经设定某种格式的时候,这种方式有时候并不成功,下面运用word制表位的(Tab键)运用---将上下两行(或多行)中的文字、数字或字符分别对齐。

    示例:将word中以下的5行内容—包含字符串、文字、字符串、数字分别对齐
    human 人类 RADYSKL…… DGDI 1
    mus musculus 小家鼠 RADYSKL……DLGDI 23
    Rattusnorvegicus 褐家鼠 RADYSKL…… DGDI 128
    Domestic dog 家犬 RADYSKL…… DGDI 118
    macaque 猕猴 RADYSKL…… DGDI 68

    1.通常来说,使行与行间文字、字符对齐的方法常用的为:
    1)新建一个文本文档(*.txt格式),并将word中这部分内容复制到该文本文档中,然后对它们进行空格调整,调整前后间隔,使字符串、文字、字符串、数字分别对齐;

    2)将txt中调整好的内容重新复制到word中,OK搞定。但是不幸运,发现不成功!

    2.下面就是制表位发挥作用的时候了:
    (1)将这5行要分别对齐的字符串、文字、字符串、数字之间的空格均删除。

    (2)选择这部分内容,鼠标右键->选择“段落”,弹出“段落”对话框(或通过“开始->段落”)。

    (3)点击该“段落”对话框左下角的“制表位(T)…”,弹出“制表位”对话框:

    ①在“制表位位置(T)”输入一个名字,在此输入了一个“2”;
    ②在“默认制表位(F)”中设置制表位间隔--字符大小,即按Tab键一次跳跃的字符间隔,在此默认为“2 字符”;
    ③在“对齐方式”中可以看到有5种对齐方式,即左对齐(L)、居中(C)、右对齐(R)、小数点对齐(D)和竖线对齐,在此根据需要选择“左对齐”;
    ④在“前导符”中选择“1无(1)”;
    ⑤最后,确定即可。

    (4)在word中,每行需要分开的部分,按制表位键盘Tab键,进行彼此间分离,每次点击便向后跳跃2个字符的间距(对应前一步的”默认制表位(F)”设定值),这样分别点击每行中的带分离部分即可实现将字符串、文字、字符串、数字分别分离且上下行分别对齐了。

    作者:admin | Categories:办公能手 | Tags:
  • 职场人士必会的14个函数公式

    2016-12-21

    今天分享几个模式化的表格公式,大家有类似问题可以直接套用。各位新人朋友们入门学习可以看看,高手请按返回键。

    1
    查找重复内容
    =IF(COUNTIF(A:A,A2)>1,"重复","")

    2
    重复出现两次以上提示
    =IF(COUNTIF(A$2:A2,A2)>1,"重复","")

    3
    根据出生年月计算年龄
    =DATEDIF(A2,TODAY(),"y")

    4
    根据身份证号提取出生年月
    =--TEXT(MID(A2,7,8),"0-00-00")

    5
    根据身份证号码提取性别
    =IF(MOD(MID(A2,15,3),2),"男","女")

    6
    几个常用汇总公式
    A列求和:=SUM(A:A)
    A列最小值:=MIN(A:A)
    A列最大值:=MAX (A:A)
    A列平均值:=AVERAGE(A:A)
    A列数值个数:=COUNT(A:A)

    7
    考核成绩排名
    =RANK(A2,A$2:A$7)

    8
    中国式排名
    相同成绩不占用名次
    =SUMPRODUCT((B$2:B$7>B2)/COUNTIF(B$2:B$7,B$2:B$7))+1

    9
    统计90分以上人数
    =COUNTIF(B1:B7,">90")

    10
    统计各分数段人数
    同时选中E2:E5,输入以下公式,按Shift+Ctrl+Enter
    =FREQUENCY(B2:B7,{70;80;90})

    11
    统计男生平均分
    =AVERAGEIF(B2:B7,"男",C2:C7)

    12
    多条件统计平均值
    =AVERAGEIFS(D2:D7,C2:C7,"男",B2:B7,"销售")

    13
    统计不重复个数
    =SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9))

    14
    提取不重复内容
    C2单元格输入以下数组公式,按Ctrl+ Shift+Enter,向下复制。
    =INDEX(A:A,1+MATCH(,COUNTIF(C$1:C1,A$2:A$10),))&""

    作者:admin | Categories:办公能手 | Tags:
  • 如何得到两个数组的乘积求和?

    2016-12-19

    作者:阿忠凯

    已知每个地市的销售单价和销售数量,需要知道整个表的销售总金额,怎么做???

    普通青年做法:

    普通青年通过加一个辅助列,然后使用Sum函数完美的实现了做法。

    数组狂人做法:

    数组狂人只是将普通青年的做法更近一步,并且还应用了数组。

    逻辑上是将销售单价数组乘以销售数量数组,然后用Sum函数实现,本案例的公式外面有{ },这个标志是数组运算的意思,编辑好Sum函数后
    =SUM(C2:C13*D2:D13)
    同时按住Ctrl+Shift+Enter。

    有人会问,如果编辑好公式后只按Enter会是神马效果呢?请自己尝试,正好弄明白数组函数的意义。

    Excel高手做法:

    看过上两个做法后,肯定有人微微一笑!

    本来一个函数可以搞定的,你们为什么要这么复杂呢!!

    人生,还是简单一点比较好!

    我们选择用SumProduct函数,这个函数嘛意思呢?

    英语好的很好理解:
    Sum 求和
    Product 乘积
    合起来就是SumProduct 乘积后求和,后面接的参数就是N个数组相乘就好!

    加了个餐:
    计算符合地市是广州的销售金额:=sumproduct((B2:B13="广州")*(C2:C13)*(D2:D13))

    作者:admin | Categories:办公能手 | Tags:
  • Excel最全逆向查询(VLOOKUP,INDEX,LOOKUP)

    2016-12-16

    在日常的Excel函数应用中,最常用的就是查询类函数,比如说根据工号查询姓名、根据学生查询成绩、根据男猪脚查询女一号等等。

    说到查询类函数,大家对VLOOKUP一定不会陌生,这可是人见人爱花见花开的函数。

    如下图,查询明星的配偶:

    E2单元格公式为
    =VLOOKUP(D2,A:B,2,0)
    意思就是以D2单元格的姓名作为查询值,以A:B为查找区域,在首列中找到与D2单元格相同的姓名,然后返回这个区域中与之对应的第二列(也就是配偶所在列)的配偶。

    如果我们以配偶作为查找值,需要在这个区域中查找和配偶对应的姓名,该如何使用公式呢?

    因为VLOOKUP函数要求查询值必须处于查询区域的首列,再使用普通方法就无法完成要求了,今天就和大家说说,关于逆向查询的几种方法。

    方法一:使用IF函数重新构建数组。

    在E4中输入
    =VLOOKUP(D4,IF({1,0},$B$2:$B$17,$A$2:$A$17),2,0)

    这个公式的用法就是用IF({1,0},$B$2:$B$17,$A$2:$A$17),返回一个配偶在前,姓名在后的多行两列的内存数组,使其符合VLOOKUP函数的查询值处于查询区域首列的条件,再用VLOOKUP查询即可。

    该函数使用比较复杂,运算效率低。

    装逼指数:★★★★★
    推荐指数:★

    方法二:使用CHOOSE函数重新构建数组。

    E6使用公式为:
    =VLOOKUP(D6,CHOOSE({1,2},B2:B17,A2:A17),2,0)

    这个公式的原理也是重新构建一个内存数组,使其符合VLOOKUP函数的查询值处于查询区域首列的条件。

    该函数与方法一的思路相同,同样是使用复杂,运算效率低。

    装逼指数:★★★★★
    推荐指数:★

    方法三:INDEX+MATCH结合使用。

    E8使用公式为:
    =INDEX(A:A,MATCH(D8,B:B,))

    公式首先使用MATCH函数返回D8单元格姓名在B列单元格中的相对位置,也就是这个区域中所处第几行。

    再以此作为INDEX函数的索引值,从A列单元格区域中返回对应位置的内容。

    这个公式是最常用的查询公式之一,看似繁琐,实际查询应用时,由于其组合灵活,可以完成从左至右、从右到左、从下到上、从上到下等多个方向的查询。

    该函数是嵌套使用,操作灵活,运算方便。

    装逼指数:★★★
    推荐指数:★★★

    方法四:所向披靡的LOOKUP函数。

    E10使用公式为:
    =LOOKUP(1,0/(D10=B2:B17),A2:A17)

    这是非常经典的LOOKUP用法。

    首先用D10=B2:B17得到一组逻辑值,再用0除以这些逻辑值,得到由0和错误值组成的内存数组。再用1作为查询值,在内存数组中进行查询。

    如果 LOOKUP 函数找不到查询值,则它与查询区域中小于或等于查询值的最大值匹配,因此是以最后一个0进行匹配,并返回A2:A17中相同位置的值。

    该函数使用简便,功能强大,公式书写也比较简洁。

    装逼指数:★
    推荐指数:★★★★★

    如果有多条符合条件的结果,前三个公式都是返回首个满足条件的值,而第四个公式则是返回最后一个满足条件的值,这一点大家在使用时还需要特别注意。

    关于多条件查询,可以《一对多查询,神器index+small》。

    作者:admin | Categories:办公能手 | Tags:
  • 如何把身份证升为18位,提取出生年月,性别,年龄

    2016-12-16

    以下假设身份证号码放在A1单元格中。

    1.从身份证号码中提取出生年月日:

    假如身份证号数据在A1单元格,在B1单元格中编辑公式=IF(LEN(A1)=15,"19"&MID(A1,7,2)&MID(A1,9,2)&MID(A1,11,2),MID(A1,7,4)&MID(A1,11,2)&MID(A1,13,2))这样输出格式就都是19821010这种格式了。

    可以通过下述操作,完成形如1978-12-24样式的出生年月日自动提取:假如身份证号数据在C2单元格=IF(LEN(A1)=15,MID(A1,7,2)&"-"&MID(A1,9,2)&"-"&MID(A1,11,2),MID(A1,7,4)&"-"&MID(A1,11,2)&"-"&MID(A1,13,2))。

    2.从身份证号码中提取性别:

    =IF(MOD(MID(A1,15,3),2),"男","女")

    3.从身份证号码中提取年龄:

    =DATEDIF(TEXT(MID(A1,7,LEN(A1)*2/3-4),"0-00-00"),TODAY(),"Y")

    4.将15位身份证升位为18位:

    =IF(LEN(A1)=15,(REPLACE(A1,7,,19))&(INDEX({1,0,"X",9,8,7,6,5,4,3,2},1,(MOD(SUM(MID(REPLACE(A1,7,,19),1,1)*7+MID(REPLACE(A1,7,,19),2,1)*9+MID(REPLACE(A1,7,,19),3,1)*10+MID(REPLACE(A1,7,,19),4,1)*5+MID(REPLACE(A1,7,,19),5,1)*8+MID(REPLACE(A1,7,,19),6,1)*4+MID(REPLACE(A1,7,,19),7,1)*2+MID(REPLACE(A1,7,,19),8,1)*1+MID(REPLACE(A1,7,,19),9,1)*6+MID(REPLACE(A1,7,,19),10,1)*3+MID(REPLACE(A1,7,,19),11,1)*7+MID(REPLACE(A1,7,,19),12,1)*9+MID(REPLACE(A1,7,,19),13,1)*10+MID(REPLACE(A1,7,,19),14,1)*5+MID(REPLACE(A1,7,,19),15,1)*8+MID(REPLACE(A1,7,,19),16,1)*4+MID(REPLACE(A1,7,,19),17,1)*2),11))+1)),A1)

    作者:admin | Categories:办公能手 | Tags: