精华内容
下载资源
问答
  • 利用Excel数组公式实现按日期和客户名查询数据.rar,本例所示的工作表中有一份客户进货记录表,要求根据日期和客户名查询相应的进货记录。
  • 利用Excel数组公式按条件实现总表拆分到分表.rar,本例展示了一份商品销售情况表,其中总表包含了所以单号的销售数据,而与之对于的存在名为“X”和“D”的两个结构相同的工作表,利用介绍的公式将实现总表的数据按...
  • 利用Excel数组公式计算一周中内存采购的平均价格.rar,本例展示了一份某电脑公司一周的内存进货价格明细表,其中每天有可能多次进货,利用介绍的公式可以按每日的最后进货价格来计算出一周的内存平均价格。
  • 带目录带示例,100个工作表,这个文件本身就体现了较高的Excel水平
  • if(含数组) 和 {if(含数组)} 的差别 if(含数组) 就是两者中间每步是把数组转为单个值 {if(含数组)} ,每步都用数组计算 IF(B$4:B$12=E$4,ROW(B$4:B$12),FALSE)

    总结

    • 数组公式 {} 写法的本质在于:每个步骤都会按照 数组去计算
    • 加上{}  就改变了 {}里的函数的计算方式,从值计算变成了数组计算!

     

    1 公式和数组公式分类

    先看一个例子

     

    1.1 不可处理(完全无法处理)数组的函数/公式

    比如字符串类函数
    比如 find()
    错误写法:FIND(1,B5:B9) 会直接返回 #N/A
    正确写法:FIND("天生",F13)
    正确写法:FIND("天生","")


    1.2  多数函数可以处理数组,但是无法 直接 返回数组,但是可以和{}数组公式配合使用的

    这下面又分3类

    1.2.1 部分能处理数组的普通函数,但只会返回数组的第1个数

    • 还要注意一些细节
    • rows()    返回行数,是一定不能用于数组运算的
    • row()     返回行号,可以处理数组,但不能直接返回数组,返回的一定是1个数(不是数组)
    • 也就是说,不配合数组函数,或其他可处理数组内部数的函数,这个基本上无用。
    • {row()}   返回一组行号,可以 结合数组公式返回 数组,把返回的数组存在一个单元格

     

    1.2.2 部分函数可处理数组的整体,可以显示正确结果

    • 比如聚合类的sum等,
    • 比如 :sum(),  large()
    • sum()
    • {sum()}  和sum() 还是有差别,差别在于 中间计算过程是否遵循数组计算


    1.2.3 部分函数可直接处理数组内部的序列

    • 有少部分工作表函数天生能处理数组的函数
    • 部分查找和引用函数,计算函数,是可以的,比如index()  match()
    • 一般字符串函数肯定不行
    • 比如index() 函数非数组函数,但是天生可以处理数组的内部(取到数列里某个)    
    • 还有
    • sum()
    • lookup()
    • vlookup()
    • match()
    • choose()
    • index()
    • offset()
    • 等等


    1.3 在多个单元格输入数组公式(相当于直接返回了整个数组的元素)

    (行列式的计算需要考虑这个)

    数组公式{}有两种返回情况

    • 返回1个数,在多个单元格写意义不大,因为会返回一样的值
    • 返回1个数组,这种情况,如果有需要显示 返回的结果数组,最好在多个单元格里输入数组公式,以期显示返回的数组中的所有元素。

    这么做的好处和坏处

    • 好处在于:输出为数组的情况下,可以显示所有的值
    • 难度在于:如果想知道所有的数组里的值,需要提前计算出数组结果的行列矩阵个数!(行数和列数)


    1.4 在1个单元格输入数组公式

    数组公式{}有两种返回情况

    • 返回1个数,在多个单元格写意义不大,因为会返回一样的值
    • 返回1个数组,这种情况,如果有需要显示 返回的结果数组,最好在多个单元格里输入数组公式,以期显示返回的数组中的所有元素。
    • 如果是返回1个数组的情况,数组公式写在一个单元格,虽然只会显示数组中的第1个数,但实际这个单元格存的是一个数组。

     

    1.5 这种是假数组公式,这么写是没意义的!

     

    1.6 总结: 用数组公式{}  不用数组公式的比较,差别到底是什么?

    • 我总结的差别就是:
    • 如果用了数组公式 {} 写法,每个步骤都会按照 数组去计算
    • 如果没用{}数组公式,则中间过程,函数会直接返回默认的结果(第1个,聚合值,或其他)

     

    2  例子2:比较 数组公式和非数组公式 结果的差别

    • 这种写法其实不是数组公式,我觉得是错的,无意义的
    • =B3:B7+D3:D7
    • =$B$3:$B$7+$D$3:$D$7
    • 还需要往下拉公式
    • 错误原因:因为不是数组,所以large()无法正确取到数组中得某值!              

     

    • 数组公式是对的
    • 数组公式即使只输入到1个单元格,large()取到得还是1个数组,所以能查到值!            

     

    两者差别比较

    • 普通公式,1个单元格里只能存1格数值,就是1格数值                
    • 数组公式,输出有可能是1个数/1个数组,如果输出在1个单元格,那么只显示第1个数,但实际上还是1个数组!                
    • 那么,如何把数组公式如果输出为数组时,把数组元素全部显示呢? 就是选中多个单元格,输入数组公式!计算结果出来前有难度                
    • 也就是说,数组公式可以把1个数组输出到1个单元格,存储在其中,但只显示1个数,有时候第1个甚至是false                
    • 可见,数组公式保证的是,从输入数据如果是数组,那么中间的每步计算都按数组计算                
    • 当然输出结果是否数组得看 每个函数f(x) 是输出数组还是单个聚合数                
       

     

    3 例子3 比较if(含数组)  和 {if(含数组)} 的差别

    3.1 if(含数组)  和 {if(含数组)} 的差别

    • if(含数组)  就是两者中间每步是把数组转为单个值
    • {if(含数组)} ,每步都用数组计算

     

    3.2 if(含数组)  

    • IF(B$4:B$12=E$4,ROW(B$4:B$12),FALSE)
    • 中间那步,ROW(B$4:B$12) 变成了4 而不是 {4,5,6,7,8,9,10,11,12} (有数据行的行数序列)

     

    3.3 {if(含数组)} 数组公式

    • 每个计算步骤里,应该是行列数数组的,都是返回的行列式,数组
    • 比如中间那步,ROW(B$4:B$12)  返回是 {4,5,6,7,8,9,10,11,12} 而不是1个数4!!!

     

     

    展开全文
  • 不用数组公式的情况 一般情况,也可以计算 比如用普通公式,逐个计算,然后再计算 少数公式有类似数组公式的效果,比如sumproduct() 但有些情况下,不用数组公式,想用普通公式很麻烦的算都是很困难的 第1类...

     

    1 不用数组公式的情况

    • 一般情况,也可以计算
    • 比如用普通公式,逐个计算,然后再计算
    • 少数公式有类似数组公式的效果,比如sumproduct()
    • 但有些情况下,不用数组公式,想用普通公式很麻烦的算都是很困难的

     

    2 第1类数组公式:

    (多个单元格绑一起,感觉很少情况下才需要这么用)

    • 选择多个单元格后,在编辑栏,只输入一个公式
    • 需要用 shift ctrl enter联合输入
    • 这种数组公式,不能单独修改其中1个单元格的内容,必须选择全部相关单元格后,一起修改
    •  

     

     

    3 第2类数组公式:

    3.1 在1个单元格里,写一个数组公式(输出在一个单元格内)

     

    数组公式写法

    • 数组公式一般,不需要往下拖,1格就够了
    • 数组公式的返回结果,EXCEL的处理特殊
    •          EXCEL的一个单元格只会显示数组公式,返回的数组的第1个数。
    •           而且这个数就是数组的第1个number,用tpye() if判断都可以发现就是数组的第1个值
    •          所以有时候比如数组是,字符串数组 string array 要注意,即使数组公式的结果显示为false,那也只是第1个数是false而已。
    •          但是数组公式,在这个单元格里存储的还是整个数组,只是只显示为第1个数。

     

     

    4 数组公式的计算:

    4.1 注意数组公式里的存储是number 还是 string

    • 因为数组公式返回值是一个数组,可能是  string 或 number,甚至逻辑值
    • 可能是字符串数组
    • 可能是逻辑值数组
    • 如果是数值数组,可以用 small(), large(),match() 等计算

     

    5 理解数组公式:

    5.1 重点1:公式里有数组,也不一定就必须是数组公式

    • 比如   LOOKUP(AM4,AJ5:AJ24,AK5:AK24)
    • 比如   LOOKUP(1,0/(AJ:AJ=AN4),AK:AK)
    • 所以,我当时图里表上这个说法并不对。。。

     

    5.2 重点2:普通公式里的数据扩展为数组,普通公式就的变为数组公式了吗?并没有,一定要三键才算

    • EXCEL的公式里本身就可以处理数组
    • 但真正的数组公式需要  三键+ 内部用到了数组
    • 比如,数组和数组的计算,像行列式,线性代数的内容
    • 比如 数组计算像矩阵计算等待

     

    5.3 重点3:数组公式是普通公式的扩展,普通公式是基础

    加上{}  就改变了 {}里的函数的计算方式,从值计算变成了数组计算!

    • 另外,普通公式是基础
    • 得先学好普通公式,然后思维扩展到数组
    • 比如先理解
    • IF(C19=E19,ROW(C19))
    • 才能理解
    • {=IF(C19:C23=E19,ROW(C19:C23))}

     

    5.4 重点4:数组公式的计算只是过程,最终目的还是为了得到数组中符合条件的值

    • 我觉得数组公式,只是中间过程
    • 利用数组,矩阵计算这种数组公式
    • 最终的目的还是为了得到数组种符合条件的值,有可能是1个值,或者还是1个数组

    比如,这种并无意义

    而下面这种才有意义

     

     

     

     

     


     

    展开全文
  • 如何使用vlookup+excel数组公式 完成逆向查找?

    本文是黄同学的录屏首秀, 也就是你们所说的第一次,我把第一次都献给你们了。初次录制,会有那么多的不习惯,和结巴,心里想说的话并不能正常用嘴巴表达出来,希望大家谅解。文章末尾有本文的讲解视频,大家一定要耐心观看哦!

    1.本文说明

    今天这个题目是一个真实的数据专员的面试题目,其实完成这个匹配查找能够使用的方法有很多,比如说直接使用Lookup()函数,再比如说使用index()+match()的组合函数。
    在这里插入图片描述
    但是题目中就是说明了:使用数组加分。不要问我为什么使用数组加分,可能人家就是觉得你肯定不会使用数组,人家就会。哈哈,废话不多说,我们来看看这道题应该怎么做吧?

    2.本文思路

    在讲述思路之前,我们先把本文的最终答案发给大家。会的同志可以跳过这篇文章,不会的同学先自己思考一下,再看看我的解体思路。

    =VLOOKUP([@分校],IF({1,0},B:B,A:A),2,0)
    

    1)什么是数组?

    数组在Excel中是用{ }表示的,如{False,9},数组中的每个元素可以是逻辑值、数字、文本、错误值,不能是公式、日期、单元格引用区域。数组可以是一维的或者二维的。

    2)数组中需要清楚的概念

    • ① 逗号分列
    • ② 分号分行

    怎么理解逗号分列和分号分行呢?通俗的说:逗号分列指的是在数组中,如果我们用逗号将数字分隔开,他们最终填充的就是每一列。分号分行指的是在数组中,如果我们用分号将数字分隔开,他们最终填充的就是每一行。文字可能不太好理解,下面我给大家录制了一个gif动画,帮助大家理解。
    在这里插入图片描述

    3)使用数组的步骤

    可能光看上述的gif动画图,你照着都不一定会操作。因为你不知道使用数组的关键步骤,你也就不知道到怎么完成gif动画中的操作了。

    ① 数组的使用步骤
    • Ⅰ 选定填充区域。
    • Ⅱ 输入数组公式。
    • Ⅲ 同时按住组合键:ctrl+ shift +enter。
    ② 以填充区域是1行2列为例 进行文字说明

    Ⅰ首先要选中一个1行2列的区域;

    Ⅱ 输入数组公式,由于是按列填充,我们数字与数字之间使用的是逗号(,)

    Ⅲ 按住组合键ctrl+ shift +enter完成最后的填充操作;

    4)一维数组和二位数组

    • 一维数组:我们填充的方向,仅仅是行或者列,这样的数组代表一维数组;
    • 二维数组:我们填充的方向,既包括行,又包括列,这样的数组就代表二维数组;

    在这里插入图片描述

    5)多维数组的使用

    多维数组:多维数组指的就是,多维数组与多维数组之间的运算。
    在这里插入图片描述
    对于上表:如果说我们想要完成销售额的计算,应该怎么办呢?

    ① 如果不使用数组公式

    在这里插入图片描述

    ② 如果使用数组公式

    在这里插入图片描述

    6)if函数结合数组的使用

    if()函数主要用于逻辑判断,如果是True,返回某个值;如果是False,返回另外一个值。那么if()函数遇见数组又会擦出怎么样子的火花呢?

    ① if()函数的简单使用

    在这里插入图片描述

    ② if()函数结合数组完成列的填充

    在这里插入图片描述

    ③ if()函数结合数组完成行的填充

    在这里插入图片描述

    7)if()函数结合数组完成列的替换

    从下表可以看出,销售量在姓名的左侧,姓名在销售量的右侧。假如我们想要调换这两列的顺序,应该怎么做呢?
    在这里插入图片描述
    操作如下:
    在这里插入图片描述

    8)if()数组结合vlookup完成逆向匹配查找

    从上图可以看出:if()数组操作的结果,就是将姓名和销售量,进行顺序的调换。如果姓名在左侧,销售量在右侧,我们是不是很快可以使用vlookup()函数,完成查找匹配的操作。
    在这里插入图片描述
    操作如下:
    在这里插入图片描述

    展开全文
  • 利用Excel数组公式统计不及格人数.rar,本例展示了一份学员成绩表格,利用介绍的数组公式可以统计表格中有两科以上不及格的人数。
  • EXCEL本身就支持数组,所以,直接用数组相加相乘都是可以的,不需要数组公式也可以生效。 比如 =a3:a8+b3:b8 ;=a3:a8*b3:b8 这个就是基本的线性代数,行列式等的思想吧,虽然已经忘记很久的课程了 (2)公式...

    1 概念:数组与数组公式

    1.1 数组

    •   EXCEL本身就支持数组,所以直接用数组相加相乘都是可以的,不需要数组公式也可以生效。
    •   比如  =a3:a8+b3:b8  ;=a3:a8*b3:b8
    •   这个就是基本的线性代数,行列式等的思想吧

     

    1.2 公式

    •   工作表公式
    •   数组公式:我现在还不太理解,只能理解这2个特点
    1.  数组公式的特点就是可以执行多重计算,它返回的是一组数据结果。
    2.  需要选择一个整体range后,在上面的fx 填整体公式,并且不能单独修改某一个单元格的
    3.  Ctrl + shift +enter

       

    2 区别--比较运算过程

    •      数组在EXCEL里是一个经常出现的事物,本身EXCEL的一般公式/表达式  也支持数组的运算,而且必须是按数组的。
    •      数组的运算,会根据行列规则,某单元格只显示对应行列运算的结果(其实是只显示数组的第1个数),不显示整体的

     

      2.1 数组公式有几种填法,具体比较

                   1 选择某个区域后,整体填一个数组公式,将约束这些range

                    2 在某个单元格填一个公式后,直接输入成数组公式

                       比如 {=sum(a3:a8*b3*b8)}   和 SUM($A$3:$A$8*$B$3:$B$8)

                      我比较了下中间结果

                      (a3:a8*b3*b8) 和 ($A$3:$A$8*$B$3:$B$8) 运算F9都是{1100;1212;1326;1442;1560;1680}

                     那为什么结果不一样?

                    直接用公式求值看计算步骤就发现了

                     {=sum(a3:a8*b3*b8)}  是把这个数组求和了

                    但是SUM($A$3:$A$8*$B$3:$B$8)的计算步骤是分别取了 100 和 11,得到1100

                           第2行又是 101*12得到1212

     

    2.2 上述比较的实例

       选整体填的数组公式,公式只能整体修改不能单独改一个格的数组公式

    数组相乘

    往下拖公式

    两两相乘后求和

    往下拖

    每个单元格都是1个简单工作表公式

    数组相乘后求和普通公式--工作表公式
       {=a3:a8*b3*b8}{=sum(a3:a8*b3*b8)}$A$3:$A$8*$B$3:$B$8SUM($A$3:$A$8*$B$3:$B$8)SUM(A3:A8*B3:B8)SUMPRODUCT(A3:A8,B3:B8)
    10011 110083201100110011008320
    10112 1212 121212121212 
    10213 1326 132613261326 
    10314 1442 144214421442 
    10415 1560 156015601560 
    10516 1680 168016801680 
    展开全文
  • 利用Excel数组公式实现单条件求和.rar,本例展示了一份商场中商品进货明细表,利用统计函数与数组公式两种方法进行数据统计,并观察几个公式应用的主要区别。
  • 利用Excel数组公式统计指定月份的销量汇总.rar,某公司所有产品的年度销售明细表如本例所示,其中B8单元格的数组公式可以统计出指定月份的销售汇总。
  • excel数组公式中的意想不到的坑

    千次阅读 2017-09-06 10:09:06
    在使用EXCEL数组公式中,从以下的学校班级成绩数据中,计算出每个学校和班级中成绩的最大分,最小分,和平均分及总分,结果如图。在计算最大,总分,平均分时,使用数组公式:{=MAX((Sheet1!$A$2:$A$49=$A2)*...
  • 利用Excel数组公式按比赛成绩进行降序排列.rar,本例展示了一份某公司秋季运动会的比赛成绩明细表,利用介绍的公式可实现根据比赛成绩进行部门排名。排名方法1——RANK函数化零为整排序法,排名方法2——SMALL函数和...
  • excel数组公式基础.doc
  • 利用Excel数组公式将中文大写日期转换成日期序列.rar,本例所示的表格中,假设B列区域为中文格式的日期文本,利用介绍的公式将它们转化为真正的日期值。
  • 对于刚接触Excel数组公式的人来说,总是会感觉到它的一份神秘。又Excel的Online Help中只有很少关于它的主题,所以这种神秘感就更强了。不要紧,只要跟着我的思路走,你很快就会看清数组公式的真面目!
  • ctrl+shift+enter输出数组公式 消化下面的这些 公式拆解 首先,它是一个数组公式 COLUMN(A$1) 从F列往右,返回值为1,2,3,4,5,6……序列数,表示匹配到的第几个值 ROW($1:$2000) 返回1:2000 各行的...
  • excel 数组公式

    2012-06-21 19:07:11
    Excel数组公式指南 如果您在 Excel 中使用过公式,想必知道利用公式可以执行某些相当复杂的操作。例如,可以基于给定的年数计算贷款总成本。但是,如果您确实想精通 Excel,还需要掌握如何使用数组公式。因为使用...
  • 利用Excel数组公式标识零件规格号中的非法数据.rar,本例所示的产品规格明细表中,要求所有的规格号只能由字母或数字来组成,利用介绍的公式将对所有不符合要求的规格号进行标识。
  • excel数组公式从入门到精通、数组公式经典教程、山菊花讲数组、菜鸟级excel数组公式入门教程四部.rar
  • 获取符合条件的数组并进行计算 将名称为AA的数量求和 例子: 名称 数量 单价 AA 1 11 BB 2 22 CC 3 33 DD 4 44 AA 6 55 CC 12 0.5 公式为:在单元格输入...
  • Excel数组公式及运用

    2015-07-21 22:13:03
    Excel数组公式及运用,数组是具有某种联系的多个元素的组合。
  • 1 使用数组公式 1.0 感想,这些公式都需要太多小技巧了,很麻烦 公式这么复杂, 用了很多奇怪技巧后对非专职研究EXCEL的人来说失去意义了 一般人能这么做,也就是写成一个工具表后,平时很少改了, 不...
  • Excel数组公式从入门到精通之精通篇

    千次阅读 2015-04-01 00:57:35
    原创作品,允许转载,转载时请... 上次写了篇《Excel数组公式从入门到精通之入门篇》,不觉已十多天过去了,今天补上“精通篇”。当然说“精通”可能有点过了,但是希望大家通过这两篇博文能够真正认识“数组公式”
  • 聚合函数,尽量不用再数组公式组 如果需要用,尽量只用在最外层 如 {sum()} 没问题 用在里层很可能会出错 ,比如 {sum(and())} 一般都是错的 为什么呢,因为 and()这种聚合函数只能返回1个值,破坏了数组公式每步...
  • 数组函数 数组公式 明白这些概念之前需要先搞清楚,函数的对象和输出 例子 函数操作对象 indirect, 操作的对象为1个数,一般是1个单元格 sum,max 等 操作的对象为1组数,一般是1组单元格 sumprouct 操作的...
  • 这个奇特的函数就是数组公式数组公式特点就是可以同时进行多重计算,并返回一种或多种结果的公式。在数组公式中使用两组或多组数据称为数组参数,数组参数可以是一个数据区域,也可以是数组常量。数组公式中的每个...
  • 如图,求不同专题在不同来源渠道下的行号的最大值、行号的最小值。 解决办法,见红框函数。
  • Excel数组公式

    2010-03-15 14:20:00
    写一个以数组为参数的公式,即数组公式,就能通过这个单一的公式,执行多个输入的操作并产生多个结果——每个结果显示在一个单元中 。 数组公式可以看成是有多重数值的公式。与单值公式的不同之处在于它可以...
  • “会啊,但是,你看看,全年级各科成绩都在同一个工作表中,比如,统计二(1)班优秀人数,公式为‘=countif(D2:D69,">=96")’”…… 我扫了一眼,继续敷衍他:“是啊,没错。” “当然没错,只是‘D2:D69’这个...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 8,376
精华内容 3,350
关键字:

excel数组公式