EXCEL数组例题运算逻辑

losxunuo

求助，本题给出了很多解题思路，但是小白我有一点不明白：就是  解法2（qlz110、yeatol简化）中我自己通过数组输入方式输入（以A部门为例）=SUM((B$7:B$15=G21)*VLOOKUP(C$7:E$15,G$10:H$13,2,)*C$6:E$6)形成数组以后，得到的是24，而答案却是21.4，经过我的研究，我发现答案框左边多了一列答案（A38），就是无背景颜色的那个，我发现原来原题解答的作者是将A38和B38同时选中创建的数组公式。
我想问：为什么要这么操作才能得到正确答案？这个逻辑思路和运算过程是怎么样的？


excel_优秀练习题(总结).zip (7.06 KB, 下载次数: 331)

2014-3-17 09:10 上传

点击文件名下载附件

losxunuo

没有人来解答吗？那只好自己顶了。

Bodhidharma

這個看起來是某個題目的總結帖，建議樓主直接給出該帖連結，並在該帖底下提問
翻了一下找到了原帖：
[Excel 函数与公式] [开_95]考评综合评分（已总结）
基本上vlookup的第一參數不接受數組參數，因此在單一單元格直接輸入

1. =SUM((B$7:B$15=G21)*VLOOKUP(C$7:E$15,G$10:H$13,2,)*C$6:E$6)

复制代码

會得出

1. =SUM((B$7:B$15=G21)*VLOOKUP(C$7,G$10:H$13,2,)*C$6:E$6)

复制代码

的結果。這個問題目前在論壇中已有解決方式：

1. =SUM((B$7:B$15=G21)*VLOOKUP(T(IF({1},C$7:E$15)),G$10:H$13,2,)*C$6:E$6)

复制代码

至於以多單元格數組公式輸入，可得出正確結果的原因有點複雜…主要是SUM函數在處理這種「不接受數組參數的函數」的時候，會將所有結果整合在一起

losxunuo

Bodhidharma 发表于 2014-3-17 16:46
這個看起來是某個題目的總結帖，建議樓主直接給出該帖連結，並在該帖底下提問
翻了一下找到了原帖：
[Exc ...

太感谢楼上了！我想问问，能否进一步解释一下这个公式的逻辑吗？主要是Vlookup函数内部的嵌套不太理解。
1、IF（{1},XX：XX）这个代表什么意思呢？主要是{1}在IF中代表什么意思？
2、T函数在这里代表什么？它不是返回文本吗？

Bodhidharma

losxunuo 发表于 2014-3-18 12:44
太感谢楼上了！我想问问，能否进一步解释一下这个公式的逻辑吗？主要是Vlookup函数内部的嵌套不太理解。
...

這個問題涉及的知識點有點多，而且有點難度，不過你既然都有興趣研究競賽題，相信你應該有能力理解以下內容…

如我前面所講，vlookup的第一參數不支援數組形式，因此像vlookup({1,2},引用範圍,...)在內存中，返回的結果會等同於vlookup(1,引用範圍...)
但是piny大師後來發現，像vlookup和index這些不支援數組形式的函數，如果加了N+if(1...)或T+if(1...)的話，就會變成可以支援，不過原理不明
因此vlookup(N(if(1,{1,2},引用範圍,...)就可以返回正確的內存數組

相關的討論可見：
[分享] 为piny发现的INDEX+N+IF用法推波助澜

不過有一點要注意，就是N、T函數內的參數，如果是「引用」的話，只會返回引用最左上角的值
相關說明可見：
http://club.excelhome.net/forum.php?mod=redirect&goto=findpost&ptid=882343&pid=7189125

因此vlookup(T(if(1,{"良","中","优"...})),...)可以返回正確的內存數組
但是vlookup(T(if(1,C$7:E$15)),...)在T的部分，只會回傳C7，因此還是無法回傳正確的內存數組

因此必需要將C$7:E$15這個「引用」，變成由文字所組成的「數組」
if(1,C$7:E$15)會直接返回C$7:E$15這個「引用」，但是if({1},C$7:E$15)，因為第一參數是數組形式
因此會將C$7:E$15轉為{"良","中","优"...}這個數組，避開T函數只取最左上引用的問題
相關討論可見：
[讨论] IF({1} 与 IF(1 的区别


以上三個連結的內容都有一定難度，請小心服用
希望我以上說明講的夠清楚，如果有其它問題歡迎繼續討論~