你好，我是劉卓。歡迎來(lái)到我的公號(hào)，excel函數(shù)解析。今天通過幾個(gè)簡(jiǎn)單的題目來(lái)分享一下免輔助列的方法。雖然題目很簡(jiǎn)單，你也一定會(huì)做，但是如果增加要求的話，簡(jiǎn)單的題目也會(huì)變得不簡(jiǎn)單，也會(huì)有你未曾觸碰的存在。1.求每一行的和
計(jì)算A2:D7這個(gè)區(qū)域中每一行的和，結(jié)果如E列所示。對(duì)于這個(gè)問題，我相信每個(gè)小伙伴都會(huì)。在E2單元格輸入公式=SUM(A2:D2)，向下填充。現(xiàn)在來(lái)增加一點(diǎn)難度，我們需要將得到的這些和放在一個(gè)內(nèi)存數(shù)組中，方便后續(xù)的運(yùn)算，這樣可以免去輔助列。如果這樣說你不理解，下面舉個(gè)例子。
假如現(xiàn)在有這么一個(gè)要求，先算出每一行的和，然后再?gòu)倪@些和中取出最大值。通常是用下圖所示的方法，先用sum求和，然后用max取最大值。這樣就用到了輔助列，不能一步直接得到結(jié)果。我們希望不用輔助列，一步得到結(jié)果。這時(shí)就需要用到免輔助列的方法，常用的免輔助列方法有mmult和多維引用，當(dāng)然還有其他函數(shù)的數(shù)組用法也可以免輔助列。今天主要分享多維引用和mmult。
如果你的數(shù)據(jù)源是單元格區(qū)域引用，那么可以考慮用多維引用。比如上面的問題可以用下圖的公式直接得到答案。其中subtotal函數(shù)得到的結(jié)果和輔助列E列的結(jié)果是一樣的，只不過存放在內(nèi)存數(shù)組中，這部分是我們學(xué)習(xí)的關(guān)鍵，最后用max取出最大值就ok了。OFFSET(A1:D1,ROW(1:6),)這部分從A1:D1開始，分別向下偏移1,2,3,4,5,6行，得到由A2:D2、A3:D3、···、A7:D7共6行組成的多維引用，注意這6行是相對(duì)獨(dú)立的，它們?cè)诓煌钠矫嫔稀?/p>
SUBTOTAL(9,OFFSET(A1:D1,ROW(1:6),))這部分用subtotal對(duì)多維引用的每一行分別求和，得到6個(gè)值，存放在內(nèi)存數(shù)組中。想要把這6個(gè)值輸出到單元格中，相應(yīng)地也要選中6個(gè)單元格，且方向也要一樣。然后輸入公式，按ctrl+shift+enter。如下圖F2:F7所示。
得到了這個(gè)內(nèi)存數(shù)組，后續(xù)求最大值，還是求平均值就簡(jiǎn)單了。這樣就免去了輔助列。
如果你的數(shù)據(jù)源不是單元格區(qū)域，而是一個(gè)數(shù)組，那么就不能用多維引用了，可以考慮用mmult。假如下圖的A2:D7不是單元格區(qū)域，而是由函數(shù)產(chǎn)生的二維數(shù)組，那么可以用mmult對(duì)每一行求和。公式如下圖所示。mmult的用法不再重復(fù)說明，可以回顧之前的文章《初識(shí)矩陣乘積函數(shù)mmult的原理和基本用法》。
上面舉例的那個(gè)問題，也可以用下圖的公式一步完成，而且不用按三鍵。計(jì)算A11:D16這個(gè)區(qū)域中每一行的最大值，直接在E11單元格輸入公式=MAX(A11:D11)，向下填充。這個(gè)不會(huì)的打屁屁。
用多維引用來(lái)生成內(nèi)存數(shù)組，選中F11:F16，在編輯欄輸入下圖的公式，按ctrl+shift+enter輸出結(jié)果。和第1題的公式基本一樣，只有subtotal的第1參數(shù)變?yōu)?。
用mmult來(lái)完成，嗯？這個(gè)目前還不會(huì)哈。可以用矩陣求最大值法來(lái)完成。選中G11:G16，在編輯欄輸入下圖的公式，按ctrl+shift+enter輸出結(jié)果。這個(gè)方法之前說過一次，今天再來(lái)簡(jiǎn)單說一下。下圖的公式得到灰色區(qū)域的結(jié)果，也就是第①步的結(jié)果。簡(jiǎn)單來(lái)說，就是給A11:D16這個(gè)區(qū)域的第1行加100，第2行加200，···，第6行加600。
第②步的結(jié)果，是從第①步的結(jié)果中取出第4,8,12,16,20,24個(gè)最小值得到的，公式為=SMALL(ROW(1:6)/1%+A11:D16,ROW(1:6)*4)?？梢钥吹終列的每個(gè)結(jié)果剛好是灰色區(qū)域每一行的最大值，這部分你可以好好琢磨一下。
最后把K列的結(jié)果除以100取余數(shù)就得到了我們想要的結(jié)果。
計(jì)算A20:D25這個(gè)區(qū)域每一行的乘積，常規(guī)做法，在E20單元格輸入公式=PRODUCT(A20:D20)，向下填充。用多維引用生成內(nèi)存數(shù)組，選中F20:F25，在編輯欄輸入下圖的公式，按三鍵。subtotal的第1參數(shù)6代表product。用mmult+對(duì)數(shù)函數(shù)log得到內(nèi)存數(shù)組，選中G20:G25，輸入下圖的公式，按三鍵。想要理解這個(gè)公式，需要對(duì)數(shù)的一些知識(shí)。并且這個(gè)公式只能對(duì)正數(shù)求乘積。我在網(wǎng)上找了幾個(gè)對(duì)數(shù)的公式，主要用到第1個(gè)和第3個(gè)。感興趣的小伙伴可以研究下。下圖是一個(gè)運(yùn)算過程圖，方便大家理解。第①步，用log將二維區(qū)域轉(zhuǎn)為對(duì)數(shù)的形式。第②步，用mmult對(duì)第①步結(jié)果的每一行相加，根據(jù)上圖第3個(gè)公式，可以轉(zhuǎn)為相乘。第③步，將第②步的結(jié)果作為10的n次方，根據(jù)上圖第1個(gè)公式，可以得到最后的結(jié)果。還可以用ln+mmult+exp來(lái)生成內(nèi)存數(shù)組，如下圖所示。https://pan.baidu.com/s/1gK4fGQdF00_7TsnKwxOQnA
