在Excel中数组公式Word文件下载.docx

在Excel中数组公式Word文件下载.docx

《在Excel中数组公式Word文件下载.docx》由会员分享，可在线阅读，更多相关《在Excel中数组公式Word文件下载.docx（13页珍藏版）》请在冰豆网上搜索。

∙提示＃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：

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）

如果你想要把一个 

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）中包括的功能，如诊断 

（ 

）返回一个 

1向量包含一个n×

n矩阵对角线项。

为了表示对角矩阵作为列向量，你可以使用行和操作诊断（M）=MI：

MMULT（范围的身份，（1+0*ROW（INDIRECT（“1：

FindinTRACE方阵

的正方形矩阵的轨迹就是的对角线元素的总和。

因此，该公式计算的跟踪：

SUM（范围标识）

SUM（对角线）

重复的行或列

有时候，你可能需要重复的行或列的数值形成一个矩阵。

使用矩阵乘法和ONES矢量I定义如上， 

其中 

j是一个 

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。

”＆N）），TRANSPOSE（ROW（INDIRECT（“1：

”＆R））））

查找类别表中的一个独特的价值

我最近找到一种方法来看待一个独特的表中的值，并返回包含该值对应的列标签。

我可能是错的，但不能用于此目的的VLOOKUP，INDEX/MATCH没有一堆嵌套IF语句（出列的值是在第一个数字）。

这可以通过使用大型数组公式 

首先，让我们来分析下面的公式返回的列索引的范围内命名的数组 

，包含了独特的lookup_value的 

SUMPRODUCT（（lookup_value的阵列）*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 

= 

0的除外。

简单来说，一个正定矩阵可以分解成一种“方的根”（不是真的，但它的样的类似于一个平方根）如甲 

乙 

， 

乙是一个Ňxp矩阵的秩 

P 

<

N。

正定矩阵的对角线元素是正的。

∙一个奇异矩阵的行列式为零。

一个非奇异矩阵的行列式为零。

正定矩阵的行列式是积极的。

您可以使用的MDETERM（）函数在Excel中的决定因素。

MDETERM（）函数返回一个常量，不要求你输入公式作为数组公式。

∙a和 

b的大小相同的两个向量的是正交的，如果一个“b 

=0时，或MMULT（TRANSPOSE

（一），二）=0

∙的载体 

的所述以被归一如果'

1，或MMULT（TRANSPOSE

（一），）=1。

归一化的向量也被称为一个单位矢量，因为归一化的向量的大小或长度是等于1。

∙的正方形矩阵的每一列是正交的，如果是归一化的向量和列是互相正交的。

正交矩阵中使用的机器人，运动学和统计的轴进行旋转。

∙的正方形矩阵的本征值的总和等于该矩阵中的跟踪 

这是一个很好的仔细检