在Excel中数组公式.docx
- 文档编号:1983794
- 上传时间:2022-10-25
- 格式:DOCX
- 页数:13
- 大小:26.26KB
在Excel中数组公式.docx
《在Excel中数组公式.docx》由会员分享,可在线阅读,更多相关《在Excel中数组公式.docx(13页珍藏版)》请在冰豆网上搜索。
在Excel中数组公式
在Excel中数组公式
在Excel中数组公式可用于线性albegra,矩阵运算和操作,数据分析,有条件的算术和更。
其中,我最流行的Excel模板使用数组公式,在一些这样或那样的。
这是一个数组公式的技巧和实例多年来,我一直在编译的。
在此页面上的链接数组公式的例子
∙替代COUNTIF
∙数组公式中的IF语句
∙Squential数字阵列(1,2,3,...)
∙查找的类别,一个独特的价值
∙线性回归
∙替代XNPV
∙的向量和,ONES矩阵(1,1,1,...)
∙单位矩阵
∙元素相乘
∙重复的行,列
∙矩阵诊断
∙归一化向量
输入数组公式
∙提示#1:
使用数组公式时,你按下 Ctrl +Shift+Enter键 ,而不是只输入后,输入或编辑公式。
∙提示#2:
数组公式将显示周围的公式,这样的公式栏的花括号或大括号 {...}
∙提示#3:
数组常量(数组“硬编码”入公式),括在大括号({}),并使用逗号来分隔列和分号分开这2个(行)×3(列)阵列,像一列列:
1,1,1,2,2,2}
在公式中使用硬编码的阵列
在我的年历模板,我用了很多的公式之一的返回平日缩写,对于一个给定的日期:
INDEX({“苏”,“M”,“土”,“W”,“TH”,“F”,“萨”,WEEKDAY(theDate1))
(意思是使用CTRL+SHIFT+ENTER)作为数组公式,这个公式并不需要输入。
关于这个公式是很好的事情,我可以选择是否显示单个字符或两个字符。
另一种可能是使用= LEFT(TEXT(theDate,“滴滴滴”),1)只显示第一个字符,如果我不关心区分周日/周六和周二/周四。
替代COUNTIF
比方说,你要计算的值的范围内出现的次数。
您可以使用COUNTIF函数不是作为数组公式输入。
但是,阵列功能如下所示实际上是速度稍快,使得它的范围是非常大的,有用的,如果:
COUNTIF(范围,“=”&lookup_value的)
-或-
SUM(1*(范围=lookup_value的))
这个问题的技巧来实现这个范围内lookup_value的布尔值(TRUE或FALSE)返回一个数组,和1的乘积布尔数组转换成0为假,1为TRUE的布尔值。
然后使用SUM函数指望所有的“1”。
数组公式中的IF语句
芯片皮尔逊提供了一些很好的例子,如何使用数组公式中的IF语句内的SUM和AVERAGE函数的错误,而忽略零值。
见芯片Pearson的文章 。
顺序编号阵列
对于一些公式和操作,你可能需要使用一个向量的连续数字,如{1,2,3,... N}。
您可以使用这个简单的数组公式创建一个顺序从1到n的向量:
ROW(OFFSET($A$1,0,0,N,1))
-或-
ROW(1:
N)
重要提示 :
虽然没有什么问题包含在单元格A1中,如果你删除的单元格(通过删除第1行或列A为例),插入行的上方或左边的一列单元格A1,或剪切和粘贴单元格A1到不同的位置,你的数组公式将被搞砸了。
要避免此问题,请使用INDIRECT函数引用单元格A1:
ROW(OFFSET(INDIRECT(“A1”),0,0,N,1))
-或-
ROW(INDIRECT(“1:
”&N))
变异#1:
创建一个整数的顺序从 i 到 j
如果你想硬编码的值代入公式,数组公式,ROW(I:
J)i和 j是你所需要的。
如果你想i和 j是单元格引用,
ROW(INDIRECT(I&“:
”&J))
变异#2:
创建一个 N×1向量整数的S 开始
要创建的数组{S,S+1,S+2; S+N-1}
小号+ROW(OFFSET(INDIRECT(“A1”),0,0,N,1))-1
-或-
ROW(INDIRECT(S&“:
”&S+N-1))
变异#3:
创建一个 N×1的向量序列的权力10
要创建数组{1;10;100;1000;... 10^(N-1)}使用
10^(ROW(OFFSET(INDIRECT(“A1”),0,0,N,1))-1)
-或-
10^(ROW(INDIRECT(“1:
”&N))-1)
创建的载体和ONES矩阵
线性代数中使用的是一个共同的载体的载体:
J ={111}。
这 n×1的1阵列可以使用下面的数组公式的创建。
(1+0*ROW(OFFSET(INDIRECT(“A1”),0,0,N,1)))
-或-
(1+0*ROW(INDIRECT(“1:
”&N)))
ONES1的一个n×n矩阵, 矩阵 J,可以创建从ONES向量乘以j的j 的移调。
在下面的公式中的 j代替替代上述式:
MMULT(TRANSPOSEJ,(十))
-或-
MMULT(1+0*ROW(INDIRECT(“1:
”&N)),TRANSPOSE(1+0*ROW(INDIRECT(“1:
”&N))))
一个更普遍的公式创建一个N×K矩阵ONES
IF(ISERROR(OFFSET(INDIRECT(“A1”),0,0,N,K)),1,1)
使用的矢量求和的行或列
事实证明,在ONES向量统计中是非常重要的,用于执行一个非常简单的矩阵运算-求和的行或列。
比方说,你有一个范围的大小为n(行)X k变换(列)。
SUM( 范围 )功能,在Excel中返回的所有值之和的范围内。
你既可以使用SUM函数分别为每行或每列,或者你可以使用下面的矩阵公式:
列萨姆 :
总和的值的矩阵的每个柱内,并返回作为一个1×n的阵列(或行向量)的款项,使用
TRANSPOSE(MMULT(TRANSPOSE(范围),(1+0*ROW(INDIRECT(“1:
”&ROWS())))))
行萨姆 :
总和每个ROW的矩阵内的值,并返回作为一个 k×1阵列的款项(或列向量),使用
MMULT(范围,(1+0*ROW(INDIRECT(“1:
”&COLUMNS()))))
创建的IDENTITY矩阵
要使用公式创建单位矩阵,而不是通过手动输入,您可以使用下面的数组函数值 =1:
IF(ROW(OFFSET(INDIRECT(“A1”),0,0,N,N))=COLUMN(OFFSET(INDIRECT(“A1”),0,0,N,N))值,0)
如果你想要把一个 n×1向量沿对角线的值,然后替换的范围包含矢量的参考价值。
2矩阵的元素相乘
您可以执行2矩阵的元素相乘的简单地将两个范围和输入的函数作为数组公式。
例如,式={1,2,3,4}*为{a,B,C,D}将返回该数组{1*,2*B;3*c中,4*深}。
如果一个矩阵中有更多的比其他的列或行,这些值将被截断,从结果。
创建一个对角矩阵
元素相乘的矩阵可以被用来建立一个对角线矩阵 。
一个对角矩阵是一种特殊的矩阵中,所有的非对角项是零。
要创建对角矩阵,乘以由单位矩阵的大小相同的矩阵:
范围*身份
-或-
范围*(IF(ROW(OFFSET(INDIRECT(“A1”),0,0,ROWS(范围),行(范围)))=
COLUMN(OFFSET(INDIRECT(“A1”),0,0,COLUMNS(范围),COLUMNS())),1,0))
许多程序(而不是Excel)中包括的功能,如诊断 ( 矩阵 )返回一个 n×1向量包含一个n×n矩阵对角线项。
为了表示对角矩阵作为列向量,你可以使用行和操作诊断(M)=MI:
MMULT(范围的身份,(1+0*ROW(INDIRECT(“1:
”&COLUMNS()))))
FindinTRACE方阵
的正方形矩阵的轨迹就是的对角线元素的总和。
因此,该公式计算的跟踪:
SUM(范围标识)
-或-
SUM(对角线)
重复的行或列
有时候,你可能需要重复的行或列的数值形成一个矩阵。
使用矩阵乘法和ONES矢量I定义如上, 其中 j是一个 n×1向量,这是可以做到。
如果你想创建一个矩阵重复R ={1,2,3,...} n行,使用数组公式:
MMULT(J,R)。
如果你想创建一个矩阵,n列的重复R ={1,2,3,...},使用数组公式中:
MMULT(TRANSPOSE R,(j)条)。
实施例:
下面的公式将创建一个n×k矩阵与n行,其中每行是序列1,2,3,...,K。
MMULT(1+0*ROW(INDIRECT(“1:
”&N)),TRANSPOSE(ROW(INDIRECT(“1:
”&R))))
查找类别表中的一个独特的价值
我最近找到一种方法来看待一个独特的表中的值,并返回包含该值对应的列标签。
我可能是错的,但不能用于此目的的VLOOKUP,INDEX/MATCH没有一堆嵌套IF语句(出列的值是在第一个数字)。
这可以通过使用大型数组公式 。
首先,让我们来分析下面的公式返回的列索引的范围内命名的数组 ,包含了独特的lookup_value的 。
SUMPRODUCT((lookup_value的阵列)*1,
MMULT(1+0*ROW(INDIRECT(“1:
”&ROWS(数组))),
TRANSPOSE(ROW(INDIRECT(“1”:
COLUMNS(阵列))))))
现在,为了得到Lookup_value是在列的标签,我们可以使用INDEX函数,如下图所示,上述公式替代col_index。
if语句是必要的,因为如果没有在数组中找到该项目,col_index将为零。
IF(col_index=0,NA(),INDEX(label_range,col_index))
矩阵诊断
线性代数,知道一个矩阵的某些特性,它是有帮助的。
幸运的是,Excel提供的函数计算行列式的矩阵,MDETERM(),它可以被用来帮助识别等特点。
∙一个矩阵的秩是线性无关的行数。
线性独立的行的数量是线性独立的列的数目相等。
如果一个矩阵,矩阵是 nX P被说成是满秩的秩等于n和 p的小。
∙如果一个方形矩阵是满秩的 ,则它被称为一个非奇异矩阵,并且它有一个独特的逆 。
如果一个方阵小于满秩的,则一个唯一的逆不存在,它被称为一个奇异矩阵。
您可以使用MINVERSE()函数在Excel中找到的倒数。
一个非奇异矩阵有一个非零的决定因素。
∙对称矩阵是正定的, 如果x'Ax> 0时为所有可能的向量 x x = 0的除外。
简单来说,一个正定矩阵可以分解成一种“方的根”(不是真的,但它的样的类似于一个平方根)如甲 = 乙 , 乙 乙是一个Ňxp矩阵的秩 P 正定矩阵的对角线元素是正的。 ∙一个奇异矩阵的行列式为零。 一个非奇异矩阵的行列式为零。 正定矩阵的行列式是积极的。 您可以使用的MDETERM()函数在Excel中的决定因素。 MDETERM()函数返回一个常量,不要求你输入公式作为数组公式。 ∙a和 b的大小相同的两个向量的是正交的,如果一个“b =0时,或MMULT(TRANSPOSE (一),二)=0 ∙的载体 的所述以被归一如果'= 1,或MMULT(TRANSPOSE (一),)=1。 归一化的向量也被称为一个单位矢量,因为归一化的向量的大小或长度是等于1。 ∙的正方形矩阵的每一列是正交的,如果是归一化的向量和列是互相正交的。 正交矩阵中使用的机器人,运动学和统计的轴进行旋转。 ∙的正方形矩阵的本征值的总和等于该矩阵中的跟踪 。 这是一个很好的仔细检
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 数组 公式