(教程)引用函數(shù)產(chǎn)生的多維引用及其應(yīng)用xls.xls

淺談在引用函數(shù)中使用數(shù)組參數(shù)產(chǎn)生的多維引用及其應(yīng)用 第一部分 通常我們所說(shuō)的三維引用都是指Excel幫助中定義的跨多表的相同位置區(qū)域的引用 而此文重點(diǎn)要說(shuō)明的是另一種由引用函數(shù)產(chǎn)生的三維以上 含 的引用 本文分為三部分 1 認(rèn)識(shí)引用和區(qū)域及其維數(shù)2 引用函數(shù)產(chǎn)生的多維引用3 認(rèn)識(shí)引用和區(qū)域及其維數(shù) 下表為一張成績(jī)表 在下面的舉例中會(huì)多次用到 姓名語(yǔ)文數(shù)學(xué)英語(yǔ) 張三857280 李四996490 王五956897 引用的類(lèi)型 引用是對(duì)工作表上單元格或單元格區(qū)域的標(biāo)識(shí) 從引用的范圍看一般有 單個(gè)單元格引用 多個(gè)連續(xù)單元格的區(qū)域引用和連續(xù)多表三維引用 從引用產(chǎn)生的方式上看 有直接輸入標(biāo)識(shí)的引用和引用函數(shù)產(chǎn)生的引用 另外還有交叉引用 以及由引用構(gòu)成的合并區(qū)域等形式 其中交叉引用不是我們要說(shuō)明的重點(diǎn) 單個(gè)單元格引用 是指對(duì)工作表中某個(gè)單元格的引用 如 姓名 C8 區(qū)域引用 是指對(duì)一個(gè)連續(xù)單元格區(qū)域的引用 可以是一行多列的單元格區(qū)域 或多行一列的單元格區(qū)域 還可以是多行多列的單元格區(qū)域 單元格區(qū)域引用的結(jié)果會(huì)產(chǎn)生一個(gè)單元格值組成的數(shù)組 其中一行多列或多行一列的單元格區(qū)域引用產(chǎn)生的是一維數(shù)組 而多行多列的單元格區(qū)域引用產(chǎn)生的是二維數(shù)組 所以我們需要以數(shù)組公式的形式輸入才能讓其在單元格中正確顯示 一行多列多行一列 姓名語(yǔ)文數(shù)學(xué)姓名 C8 E8張三 C8 C11 李四 王五 合并區(qū)域 在介紹三維引用前 我們有必要先解釋一個(gè)多區(qū)域合并的概念 其和區(qū)域引用一起合稱(chēng)為區(qū)域 將多個(gè)單元格或區(qū)域引用 用逗號(hào)隔開(kāi)并用括號(hào) 合并起來(lái)表示 就是合并區(qū)域 合并區(qū)域是雖是平面二維的 但無(wú)法在一個(gè)連續(xù)的單元格區(qū)域中顯示 也不能形成一個(gè)按行列整齊排列的二維的數(shù)組 和我們要說(shuō)的連續(xù)多表三維引用和引用函數(shù)產(chǎn)生的多維引用不同 它只是分散在同一個(gè)工作表中的幾個(gè)區(qū)域的集合 一個(gè)合并區(qū)域的例子 VALUE D9 D10 D10 F10 E10 F11 F9 F10 D11 D9 E9 E9 F9 合并區(qū)域在單元格中無(wú)法正確顯示 不論其實(shí)際合并后所代表的區(qū)域是否連續(xù) 但并影響我們將其作為一個(gè)參數(shù)用于可使用區(qū)域參數(shù)的函數(shù)中參與計(jì)算 返回引用區(qū)域的地址 D 9 D 10 F 10 E 10 F 11 F 9 F 10 D 11 D 9 E 9 E 9 F 9 CELL address D9 D99 D10 F10 E10 F11 F9 F10 D11 D9 E9 E9 F9 注意 合并區(qū)域中的第一個(gè)區(qū)域只有第一個(gè)單元格的地址出現(xiàn)在Cell address 的返回結(jié)果中 其他區(qū)域則是完整的 測(cè)試合并區(qū)域中的區(qū)域數(shù) 7 AREAS D9 D10 D10 F10 E10 F11 F9 F10 D11 D9 E9 E9 F9 Areas 函數(shù)可用來(lái)返回一個(gè)區(qū)域中包含幾個(gè)單元格或區(qū)域引用 上例中的合并區(qū)域?qū)嶋H上是7個(gè)區(qū)域組成的 其中有兩個(gè)區(qū)域是做為一個(gè)合并區(qū)輸入的 他們分別是 ref1ref2ref3 85 D9 D1099649064 99 D10 F1068 ref4ref5ref6 80 F9 F10958572 90 D11 D9 E9 合并區(qū)域如何參與計(jì)算 求和1330 SUM D9 D10 D10 F10 E10 F11 F9 F10 D11 D9 E9 E9 F9 1330 SUBTOTAL 9 D9 D10 D10 F10 E10 F11 F9 F10 D11 D9 E9 E9 F9 計(jì)數(shù)16 COUNT D9 D10 D10 F10 E10 F11 F9 F10 D11 D9 E9 E9 F9 16 SUBTOTAL 3 D9 D10 D10 F10 E10 F11 F9 F10 D11 D9 E9 E9 F9 最大值99 MAX D9 D10 D10 F10 E10 F11 F9 F10 D11 D9 E9 E9 F9 99 SUBTOTAL 4 D9 D10 D10 F10 E10 F11 F9 F10 D11 D9 E9 E9 F9 求85在區(qū)域中的排名 8 RANK 85 D9 D10 D10 F10 E10 F11 F9 F10 D11 D9 E9 E9 F9 使用Index返回合并區(qū)域中的第3個(gè)區(qū)域引用 6490 INDEX D9 D10 D10 F10 E10 F11 F9 F10 D11 D9 E9 E9 F9 3 6897 使用Index返回合并區(qū)域中的第3個(gè)區(qū)域引用 然后求和 319 SUM INDEX D9 D10 D10 F10 E10 F11 F9 F10 D11 D9 E9 E9 F9 3 使用Index返回合并區(qū)域中的第3個(gè)區(qū)域 第2行的值 6897 INDEX D9 D10 D10 F10 E10 F11 F9 F10 D11 D9 E9 E9 F9 2 3 使用Index返回合并區(qū)域中的第3個(gè)區(qū)域 第2行的值 第2列的值 97 INDEX D9 D10 D10 F10 E10 F11 F9 F10 D11 D9 E9 E9 F9 2 2 3 合并區(qū)域不是引用 它不能作為參數(shù)類(lèi)型只為Range的函數(shù)的參數(shù) 我們不能在函數(shù)的range參數(shù)中輸入合并區(qū)域 例如下例就出現(xiàn)參數(shù)類(lèi)型錯(cuò)誤 VALUE COUNTIF D9 D10 D10 F10 E10 F11 F9 F10 D11 D9 E9 E9 F9 90 合并區(qū)域中不能同時(shí)存在于有兩張工作表以上的引用或區(qū)域或連續(xù)多表三維引用 VALUE SUM D9 D10 D10 F10 E10 F11 F9 F10 D11 D9 E9 E9 F9 成績(jī)表1 B3 C4 連續(xù)多表三維引用 連續(xù)多表三維引用是引用多張連續(xù)排列的工作表中相同行列位置的單元格或連續(xù)單元格區(qū)域的表示方法 連續(xù)多表三維引用的例子 REF 成績(jī)表1 成績(jī)表3 B3 D5 由于多表三維引用是一個(gè)在表 行 列三個(gè)方向上的引用所以我們無(wú)法直接將在一張表的單元格區(qū)域中直接展示出來(lái) 但是大家可以透過(guò)下圖去理解 把每張表的區(qū)域看是一個(gè)平面 那么多表三維引用就是在多個(gè)平面上的不同區(qū)域引用組成的 而且每個(gè)平面區(qū)域引用的尺寸是一樣的 857280 996490 956897 957582 926888 897098 919981 876299 937293 測(cè)試是否為引用 結(jié)果為否 FALSE ISREF 成績(jī)表1 成績(jī)表3 B3 D5 連續(xù)多表三維引用 不是真正的引用 它無(wú)法應(yīng)用于Range參數(shù)類(lèi)型的函數(shù) 例如 VALUE COUNTIF 成績(jī)表1 成績(jī)表3 B3 D5 80 下表為幫助中列出的支持連續(xù)多表三維引用的函數(shù)列表 此外Rank函數(shù)也支持連續(xù)多表三維引用 SUM 將數(shù)值相加 AVERAGE 計(jì)算數(shù)值的平均值 數(shù)學(xué)方法 AVERAGEA 計(jì)算數(shù)值 包括字符串和邏輯值 的平均值 數(shù)學(xué)方法 COUNT 計(jì)算包含數(shù)字的單元格個(gè)數(shù) COUNTA 計(jì)算非空白單元格個(gè)數(shù) MAX 查找一組數(shù)值中的最大值 MAXA 查找一組數(shù)值中的最大值 包括字符串和邏輯值 MIN 查找一組數(shù)值中的最小值 MINA 查找一組數(shù)值中的最小值 包括文本和邏輯值 PRODUCT 將數(shù)字相乘 STDEV 估算基于給定樣本的標(biāo)準(zhǔn)偏差 STDEVA 估算基于給定樣本 包括字符串和邏輯值 的標(biāo)準(zhǔn)偏差 STDEVP 計(jì)算基于給定的樣本的總體的標(biāo)準(zhǔn)偏差 STDEVPA 計(jì)算樣本 包括文本和邏輯值 總體的標(biāo)準(zhǔn)偏差 VAR 估計(jì)樣本的方差 VARA 估算給定樣本 包括文本和邏輯值 的方差 VARP 計(jì)算基于給定的樣本的總體的方差 VARPA 計(jì)算樣本 包括文本和邏輯值 總體方差 連續(xù)多表三維引用的輸入方式 單擊要輸入函數(shù)的單元格 鍵入 等號(hào) 再輸入函數(shù)名稱(chēng) 然后鍵入左圓括號(hào) 單擊需要引用的第一個(gè)工作表標(biāo)簽 按住 Shift 單擊需要引用的最后一個(gè)工作表的標(biāo)簽 選定需要引用的單元格或單元格區(qū)域 完成公式 再按 Enter 連續(xù)多表三維引用如何參與計(jì)算 例 求97在三張表中的名次 因?yàn)槿龔埍碇杏?個(gè)99和1個(gè)98 所以97名列第5名 5 RANK 97 成績(jī)表1 成績(jī)表3 B3 D5 例 求三個(gè)學(xué)期所有成績(jī)的平均值 85 AVERAGE 成績(jī)表1 成績(jī)表3 B3 D5 例 求三個(gè)學(xué)期所有成績(jī)的最大值 99 MAX 成績(jī)表1 成績(jī)表3 B3 D5 注意 1連續(xù)多表三維引用 雖然稱(chēng)作引用但是其不能用于引用類(lèi)型Range為參數(shù)的函數(shù) 如Sumif Countif 等 2對(duì)于大多數(shù)以reference或ref為參數(shù)的函數(shù) 也不能使用連續(xù)多表三維引用作為參數(shù) 但有一個(gè)例外 就是Rank函數(shù) 3Areas 函數(shù)雖然是求區(qū)域中區(qū)域引用的個(gè)數(shù)的 但其只適用于同一個(gè)工作表中的區(qū)域 即同一個(gè)平面上的區(qū)域 因此Areas不能用來(lái)統(tǒng)計(jì)三維引用區(qū)域的個(gè)數(shù) 通過(guò)引用函數(shù)產(chǎn)生的單個(gè)單元格 單元格區(qū)域引用 如果不直接輸入引用的標(biāo)識(shí) 我們還可以通過(guò)Index Offsett Indirect 函數(shù)來(lái)產(chǎn)生對(duì)單元格和單元格區(qū)域的引用 這里主要是要說(shuō)明三個(gè)函數(shù)是如何返回單個(gè)單元格引用和多個(gè)單元格區(qū)域引用 所以對(duì)三個(gè)函數(shù)的用法就不作更詳細(xì)地介紹了 Index產(chǎn)生的單個(gè)單元格 單元格區(qū)域引用 Index的第一參數(shù)為不連續(xù)區(qū)域時(shí) 可指定返回其中一個(gè)區(qū)域或區(qū)域中某一行列或某一單元格的引用 通過(guò)之前合并區(qū)域中的例子 我們已經(jīng)了解了 當(dāng)?shù)谝粎?shù)為連續(xù)區(qū)域時(shí) 除了不用使用第4個(gè)參數(shù)外 其他都是一樣的 這里不再重復(fù) 要強(qiáng)調(diào)的是 Index第一參數(shù)為區(qū)域時(shí) 其返回的值的類(lèi)型為單元格引用 857280 996490 956897 957582 926888 897098 919981 876299 937293 我們可以通過(guò)兩個(gè)例子自來(lái)作進(jìn)一步的說(shuō)明 例 使用Isref 函數(shù)測(cè)試是否為引用 Index對(duì)區(qū)域中單個(gè)單元格的引用的返回值類(lèi)型的判斷Index對(duì)區(qū)域中某一行的引用 TRUE ISREF INDEX C 8 F 11 1 1 姓名語(yǔ)文 Index引用區(qū)域中某一行的引用的返回值類(lèi)型的判斷 INDEX C 8 F 11 1 TRUE ISREF INDEX C 8 F 11 1 判斷結(jié)果是引用 有點(diǎn)遺憾 對(duì)于一個(gè)連續(xù)區(qū)域 Index最多只能返回其中的一行或是一列 而對(duì)于不連續(xù)區(qū)域Index則可以返回其中的一整個(gè)連續(xù)區(qū)域 但也不能返回這個(gè)連續(xù)區(qū)域的多行或多列 Index返回的引用如何參與計(jì)算 例 將Index函數(shù)返回的引用用于區(qū)域引用中 483 SUM D9 INDEX D9 F11 3 2 例 將Index函數(shù)返回的引用用于Index的一個(gè)參數(shù) 再讓其返回一個(gè)引用 姓名 INDEX INDEX C 8 F 11 1 1 Offest產(chǎn)生的單個(gè)單元格 單元格區(qū)域引用 通過(guò)指定一個(gè)單元格或區(qū)域引用 行列偏移量 區(qū)域的高度和寬度等參數(shù) offset 可以產(chǎn)生對(duì)另一個(gè)單元格或單元格區(qū)域的引用 例 引用成績(jī)表格中第3行第2列的單元格例 引用成績(jī)表3的第3行第2列單元格 99 OFFSET C 8 2 1 87 OFFSET 成績(jī)表3 A 2 2 1 例 引用成績(jī)表格中第3行第2列的單元格起 高為2 寬為2的單元格區(qū)域 這是多個(gè)單元格區(qū)域引用需要以數(shù)組公式的方式返回 9964 OFFSET C 8 2 1 2 2 9568 注意 offset的第一個(gè)參數(shù)不可以是合并區(qū)域 Indirect產(chǎn)生的單個(gè)單元格 單元格區(qū)域引用 例 引用成績(jī)表格中第3行第2列的單元格例 引用成績(jī)表3的第3行第2列單元格 李四 INDIRECT c10 87 INDIRECT 成績(jī)表3 b4 例 引用成績(jī)表格局部區(qū)域 9964 INDIRECT d10 e12 9568 以上三個(gè)引用函數(shù)中都不帶數(shù)組參數(shù) 因此只能返回單個(gè)單元格引用或一個(gè)二維以?xún)?nèi)的區(qū)域引用 并且都能在單元格中直接顯示出來(lái) 在合并區(qū)域中使用引用函數(shù)產(chǎn)生的單元格或區(qū)域引用 對(duì)引用函數(shù)產(chǎn)生的二維以?xún)?nèi)的引用進(jìn)行區(qū)域合并 也同樣能得到同在一個(gè)平面的二維區(qū)域引用的集合 但其也不是三維引用 VALUE OFFSET C 8 2 1 INDIRECT c10 d12 INDEX C 8 F 11 1 上述公式的結(jié)果無(wú)法正確顯示 但實(shí)際上是返回了下面的各區(qū)域或引用的合并區(qū)域 999964 D10 E12姓名 D109568張三 李四 王五 測(cè)試此合并區(qū)域的區(qū)域引用數(shù) 3 AREAS OFFSET C 8 2 1 INDIRECT D10 E12 INDEX C 8 F 11 1 求區(qū)域中的最大值 注意區(qū)域中的文本會(huì)被忽略 99 MAX OFFSET C 8 2 1 INDIRECT d10 e12 INDEX C 8 F 11 1 求區(qū)域中的數(shù)值和 注意區(qū)域中的文本會(huì)被忽略 425 SUM OFFSET C 8 2 1 INDIRECT d10 e12 INDEX C 8 F 11 1 小結(jié) 從上述內(nèi)容 我們可以了解單個(gè)單元格引用 一維區(qū)域引用 二維區(qū)域引用 合并區(qū)域和連續(xù)多表三維引用的特點(diǎn)及用法 以及如何用函數(shù)來(lái)返回二維以?xún)?nèi)的單元格和區(qū)域引用 引用函數(shù)產(chǎn)生的多維引用的應(yīng)用實(shí)例 多行多列 姓名語(yǔ)文 張三85 C8 D11 李四99 王五95 未經(jīng)許可 請(qǐng)勿抄載 Apolloh 黃朝陽(yáng) 友情提示 本工作簿中使用了宏表函數(shù)來(lái)顯示 公式文本 為了使公式文本能被正確地顯 示出來(lái) 請(qǐng)確認(rèn)您的Excel中的宏安全性 是否允許執(zhí)行宏 淺談在引用函數(shù)中使用數(shù)組參數(shù)產(chǎn)生的多維引用及其應(yīng)用 第一部分 通常我們所說(shuō)的三維引用都是指Excel幫助中定義的跨多表的相同位置區(qū)域的引用 而此文重點(diǎn)要說(shuō)明的是另一種由引用函數(shù)產(chǎn)生的三維以上 含 的引用 是指對(duì)一個(gè)連續(xù)單元格區(qū)域的引用 可以是一行多列的單元格區(qū)域 或多行一列的單元格區(qū)域 還可以是多行多列的單元格區(qū)域 單元格區(qū)域引用的結(jié)果會(huì)產(chǎn)生一個(gè)單元格值組成的數(shù)組 其中一行多列或多行一列的單元格區(qū)域引用產(chǎn)生的是一維數(shù)組 而多行多列的單元格區(qū)域引用產(chǎn)生的是二維數(shù)組 將多個(gè)單元格或區(qū)域引用 用逗號(hào)隔開(kāi)并用括號(hào) 合并起來(lái)表示 就是合并區(qū)域 合并區(qū)域是雖是平面二維的 但無(wú)法在一個(gè)連續(xù)的單元格區(qū)域中顯示 也不能形成一個(gè)按行列整齊排列的二維的數(shù)組 和我們要說(shuō)的連續(xù)多表三維引用和引用函數(shù)產(chǎn)生的多維引用不同 它只是分散在同一個(gè)工作表中的幾個(gè)區(qū)域的集合 合并區(qū)域在單元格中無(wú)法正確顯示 不論其實(shí)際合并后所代表的區(qū)域是否連續(xù) 但并影響我們將其作為一個(gè)參數(shù)用于可使用區(qū)域參數(shù)的函數(shù)中參與計(jì)算 注意 合并區(qū)域中的第一個(gè)區(qū)域只有第一個(gè)單元格的地址出現(xiàn)在Cell address 的返回結(jié)果中 其他區(qū)域則是完整的 90 E10 F11 97 ref7 7280 E9 F9 上例中的合并區(qū)域?qū)嶋H上是7個(gè)區(qū)域組成的 其中有兩個(gè)區(qū)域是做為一個(gè)合并區(qū)輸入的 他們分別是 SUBTOTAL 9 D9 D10 D10 F10 E10 F11 F9 F10 D11 D9 E9 E9 F9 SUBTOTAL 3 D9 D10 D10 F10 E10 F11 F9 F10 D11 D9 E9 E9 F9 SUBTOTAL 4 D9 D10 D10 F10 E10 F11 F9 F10 D11 D9 E9 E9 F9 RANK 85 D9 D10 D10 F10 E10 F11 F9 F10 D11 D9 E9 E9 F9 INDEX D9 D10 D10 F10 E10 F11 F9 F10 D11 D9 E9 E9 F9 3 INDEX D9 D10 D10 F10 E10 F11 F9 F10 D11 D9 E9 E9 F9 2 3 合并區(qū)域不是引用 它不能作為參數(shù)類(lèi)型只為Range的函數(shù)的參數(shù) 我們不能在函數(shù)的range參數(shù)中輸入合并區(qū)域 例如下例就出現(xiàn)參數(shù)類(lèi)型錯(cuò)誤 SUM D9 D10 D10 F10 E10 F11 F9 F10 D11 D9 E9 E9 F9 成績(jī)表1 B3 C4 連續(xù)多表三維引用是引用多張連續(xù)排列的工作表中相同行列位置的單元格或連續(xù)單元格區(qū)域的表示方法 由于多表三維引用是一個(gè)在表 行 列三個(gè)方向上的引用所以我們無(wú)法直接將在一張表的單元格區(qū)域中直接展示出來(lái) 但是大家可以透過(guò)下圖去理解 把每張表的區(qū)域看是一個(gè)平面 那么多表三維引用就是在多個(gè)平面上的不同區(qū)域引用組成的 而且每個(gè)平面區(qū)域引用的尺寸是一樣的 857280 996490 956897 957582 926888 897098 919981 876299 937293 857280 996490 956897 957582 926888 897098 919981 876299 937293 單擊要輸入函數(shù)的單元格 鍵入 等號(hào) 再輸入函數(shù)名稱(chēng) 然后鍵入左圓括號(hào) 單擊需要引用的第一個(gè)工作表標(biāo)簽 按住 Shift 單擊需要引用的最后一個(gè)工作表的標(biāo)簽 選定需要引用的單元格或單元格區(qū)域 完成公式 再按 Enter 連續(xù)多表三維引用 雖然稱(chēng)作引用但是其不能用于引用類(lèi)型Range為參數(shù)的函數(shù) 如Sumif Countif 等 對(duì)于大多數(shù)以reference或ref為參數(shù)的函數(shù) 也不能使用連續(xù)多表三維引用作為參數(shù) 但有一個(gè)例外 就是Rank函數(shù) Areas 函數(shù)雖然是求區(qū)域中區(qū)域引用的個(gè)數(shù)的 但其只適用于同一個(gè)工作表中的區(qū)域 即同一個(gè)平面上的區(qū)域 因此Areas不能用來(lái)統(tǒng)計(jì)三維引用區(qū)域的個(gè)數(shù) 如果不直接輸入引用的標(biāo)識(shí) 我們還可以通過(guò)Index Offsett Indirect 函數(shù)來(lái)產(chǎn)生對(duì)單元格和單元格區(qū)域的引用 這里主要是要說(shuō)明三個(gè)函數(shù)是如何返回單個(gè)單元格引用和多個(gè)單元格區(qū)域引用 所以對(duì)三個(gè)函數(shù)的用法就不作更詳細(xì)地介紹了 Index的第一參數(shù)為不連續(xù)區(qū)域時(shí) 可指定返回其中一個(gè)區(qū)域或區(qū)域中某一行列或某一單元格的引用 通過(guò)之前合并區(qū)域中的例子 我們已經(jīng)了解了 數(shù)學(xué)英語(yǔ) Index對(duì)區(qū)域中某一列的引用 姓名 張三 INDEX C 8 F 11 1 李四 王五 C8 C11 Index對(duì)區(qū)域中某一行的引用 而對(duì)于不連續(xù)區(qū)域Index則可以返回其中的一整個(gè)連續(xù)區(qū)域 但也不能返回這個(gè)連續(xù)區(qū)域的多行或多列 通過(guò)指定一個(gè)單元格或區(qū)域引用 行列偏移量 區(qū)域的高度和寬度等參數(shù) offset 可以產(chǎn)生對(duì)另一個(gè)單元格或單元格區(qū)域的引用 OFFSET 成績(jī)表3 A 2 2 1 例 引用成績(jī)表格中第3行第2列的單元格起 高為2 寬為2的單元格區(qū)域 這是多個(gè)單元格區(qū)域引用需要以數(shù)組公式的方式返回 以上三個(gè)引用函數(shù)中都不帶數(shù)組參數(shù) 因此只能返回單個(gè)單元格引用或一個(gè)二維以?xún)?nèi)的區(qū)域引用 并且都能在單元格中直接顯示出來(lái) 對(duì)引用函數(shù)產(chǎn)生的二維以?xún)?nèi)的引用進(jìn)行區(qū)域合并 也同樣能得到同在一個(gè)平面的二維區(qū)域引用的集合 但其也不是三維引用 從上述內(nèi)容 我們可以了解單個(gè)單元格引用 一維區(qū)域引用 二維區(qū)域引用 合并區(qū)域和連續(xù)多表三維引用的特點(diǎn)及用法 以及如何用函數(shù)來(lái)返回二維以?xún)?nèi)的單元格和區(qū)域引用 將多個(gè)單元格或區(qū)域引用 用逗號(hào)隔開(kāi)并用括號(hào) 合并起來(lái)表示 就是合并區(qū)域 合并區(qū)域是雖是平面二維的 但無(wú)法在一個(gè)連續(xù)的單元格區(qū)域中顯示 也不能形成一個(gè)按行列整齊排列的二維的數(shù)組 淺談在引用函數(shù)中使用數(shù)組參數(shù)產(chǎn)生的多維引用及其應(yīng)用 第二部分 本文分為三部分 1 認(rèn)識(shí)引用和區(qū)域及其維數(shù)2 引用函數(shù)產(chǎn)生的多維引用3 用OFFSET INDIRECT來(lái)對(duì)單元格和區(qū)域進(jìn)行引用時(shí) 如果部分或全部參數(shù)使用數(shù)組 就會(huì)產(chǎn)生一個(gè)三維甚至三維以上的引用 下面我們將兩個(gè)函數(shù)產(chǎn)生多維引用的情況進(jìn)行解析 下表在下面的舉例中會(huì)多次用到 111111111111111111111 222222222222222222222 333333333333333333333 444444444444444444444 555555555555555555555 引用函數(shù)產(chǎn)生的多維引用 什么是多維的引用 單個(gè)引用和區(qū)域引用及合并區(qū)域都是在一個(gè)平面上 其中合并區(qū)域 是使得平面上同時(shí)放置了多個(gè)獨(dú)立的單元格或區(qū)域引用 而 維引用實(shí)際上是將各個(gè)引用區(qū)域放到了不同的平面的 其中每個(gè)平面只有一個(gè)單元格或區(qū)域引用 不同平面的引用形成一個(gè)空間 維引用實(shí)際上就是有 個(gè)以上象 維引用這樣的空間 形成一個(gè)外套的空間 維 維 維 維 以此類(lèi)推就是空間外再套空間 首先我們先來(lái)做幾個(gè)推斷 之后通過(guò)一些實(shí)例大家可以一起來(lái)證明這些推斷是否正確 推斷 函數(shù)產(chǎn)生的多維引用的基本元素是存放于每個(gè)平面上的單個(gè)單元格或區(qū)域引用 只有先將這些區(qū)域轉(zhuǎn)換成常數(shù) 多維引用才能被應(yīng)用于數(shù)組運(yùn)算 推斷 Offset函數(shù)的各參數(shù)及Indierect的第一參數(shù)都可以使用數(shù)組 但每個(gè)數(shù)組參數(shù)的維數(shù)最多不能超過(guò)2維 因?yàn)镋xcel函數(shù)不能正確處理3維以上的數(shù)組 推斷 在Offset函數(shù)中任何一個(gè)參數(shù)中增加數(shù)組的維數(shù) 是否會(huì)對(duì)offset產(chǎn)生引用的維數(shù)產(chǎn)生影響 具體還需要看每個(gè)數(shù)組參數(shù)的維度方向是否一致 推斷 在已經(jīng)有其他參數(shù)是數(shù)組的情況下 增加另一個(gè)一元數(shù)組參數(shù) 不會(huì)對(duì)引用的維數(shù)產(chǎn)生影響 推斷 一些參數(shù) 如offset的height和width中所使用的數(shù)組的行列方向是否一致會(huì)影響引用產(chǎn)生的各區(qū)域的尺寸大小 推斷 Excel函數(shù)能處理的引用最多只有4維 可同過(guò)函數(shù)將其中2維平面先轉(zhuǎn)換計(jì)算成常量 然后形成一個(gè)2維以?xún)?nèi)的數(shù)組參與數(shù)組運(yùn)算 推斷 超過(guò)3維的引用無(wú)法在單元格區(qū)域中展開(kāi)直接顯示出來(lái) 引用函數(shù)產(chǎn)生的多維引用的維數(shù)變化 單個(gè)引用 區(qū)域引用和合并區(qū)域都是在一個(gè)平面上的3維引用是一個(gè)空間包含一個(gè)以上的平面 每個(gè)平面上都是一個(gè)連續(xù)的區(qū)域引用 上圖是下列公式的圖解 其實(shí)質(zhì)是Row參數(shù)變化產(chǎn)生了多個(gè)平面 VALUE 按F9可以看到公式返回 VALUE VALUE VALUE 由于三維數(shù)組無(wú)法顯示出來(lái)所以返回了一個(gè)按行方向排列的一維3 1的區(qū)域數(shù)組 每個(gè) VALUE 都代表一個(gè)區(qū)域 區(qū)域數(shù)組 把一個(gè)區(qū)域看成是一個(gè)元素 那么多個(gè)區(qū)域按一定排列順序組成的數(shù)組叫區(qū)域數(shù)組 注 把參數(shù)改為一元數(shù)組如 1 這樣的數(shù)組 結(jié)果也是3維引用 雖然其只產(chǎn)生一個(gè)平面 但仍然是一個(gè)三維空間 4維引用實(shí)際上是 多個(gè)三維引用構(gòu)成的外套空間 單個(gè)引用 1 區(qū)域引用 22222 33333 合并區(qū)域 122222 33333 111 222 222 333 333 444 111 222 222 333 333 444 11111 22222 22222 33333 33333 44444 Row參 數(shù)變化 產(chǎn)生多 個(gè)平面 Col參數(shù)變化產(chǎn)生的多個(gè)空間 只在一個(gè)參數(shù)中使用二維數(shù)組產(chǎn)生的4維引用 Offet第一參數(shù)為二維數(shù)組產(chǎn)生的4維引用 5維的引用 VALUE OFFSET B 7 0 1 1 2 2 3 0 1 2 2 按F9可以看到公式返回 VALUE VALUE VALUE VALUE VALUE VALUE 其返回的區(qū)域維數(shù) 和 VAlUE 所代表的區(qū)域的數(shù)目都是和4位引用一樣的 這是為什么呢 因?yàn)镋xcel最多只能把4維引用處理成2維數(shù)組 再增加維數(shù) Excel就無(wú)法進(jìn)一步處理了 所以只能返回其中的第一個(gè)4維引用結(jié)果 由于函數(shù)的參數(shù)有多個(gè) 每個(gè)參數(shù)都支持二維數(shù)組 一一列舉其多維引用的變化將是一項(xiàng)很龐大的工作 而且不是每種情況都有實(shí)用價(jià)值 所以維數(shù)變化就說(shuō)這么多 大家可以從實(shí)例中去體會(huì) 引用函數(shù)產(chǎn)生的多維引用中每一區(qū)域的尺寸變化 引用區(qū)域高度和寬度產(chǎn)生的變化 右圖是由Height參數(shù)使用一維數(shù)組產(chǎn)生的各區(qū)域引用的高度的變化 1 OFFSET B 7 1 2 3 4 5 右圖是由Height和width參數(shù)使用一維數(shù)組產(chǎn)生的各區(qū)域引用的高度和寬度同時(shí)變化 1 OFFSET B 7 1 2 3 1 2 3 按F9可以看到公式返回 1 VALUE VALUE 這里的高度和寬度的值一對(duì)一對(duì)應(yīng) 產(chǎn)生了 1 3個(gè)區(qū)域 下面的公式 寬度參數(shù)中的數(shù)組在維度方向和上式不同 高度和寬度的值多對(duì)多對(duì)應(yīng)后 形成了一個(gè)3 3的區(qū)域數(shù)組 1 OFFSET B 7 1 2 3 1 2 3 請(qǐng)注意這里 和 號(hào)的使用 按F9可以看到公式返回 1 VALUE VALUE VALUE VALUE VALUE VALUE VALUE VALUE 一共9個(gè)區(qū)域 注 由于第一個(gè)區(qū)域只有一個(gè)單元格 所以上面的三個(gè)公式都能正確顯示該單元格的值 上述Offset產(chǎn)生的多維引用中的每個(gè)區(qū)域在尺寸大小的變化是由height和width決定的 但都遵循了一定的運(yùn)算規(guī)則 是有規(guī)律可循的 而下面要講的Indirect的多維引用則不同 其每個(gè)區(qū)域的尺寸大小是可以任意變化的 可以無(wú)規(guī)律性 Indirect函數(shù)第一個(gè)參數(shù)為二維數(shù)組產(chǎn)生的4維引用 222 333 111 222 222 333 333 444 333 444 444 555 Row參 數(shù)行方 向變化 產(chǎn)生多 個(gè)平面 Row參數(shù)列方向變化產(chǎn)生的多個(gè)空間 1 1 2 1 2 3 1 2 3 4 1 2 3 4 5 1 111 222 111111 222222 333333 Reference參數(shù) 數(shù)組中數(shù)行方向 變化產(chǎn)生多個(gè)平 Reference參數(shù)列方向變化產(chǎn)生的多個(gè)空間 22222 33333 44444 55555 222222222 333333333 444444444 555555555 1111113333333 4444444 3333333333 4444444444 5 Ref text參數(shù)數(shù)組 中數(shù)行方向變化 產(chǎn)生多個(gè)平面 Col參數(shù)變化產(chǎn)生的多個(gè)空間 引用函數(shù)產(chǎn)生的跨多表多維引用 Indirect的Ref text參數(shù)使用二維數(shù)組產(chǎn)生的跨多表的4維引用 學(xué)年上的變化 這是跨多表的多維引用 其中Indirect的Reft text參數(shù)使用了二維數(shù)組產(chǎn)生了一個(gè)2 2的二維區(qū)域數(shù)組 VALUE INDIRECT 成績(jī)表1 成績(jī)表2 成績(jī)表3 成績(jī)表4 VALUE VALUE 一共2 2 4個(gè)區(qū)域 Offset與Indirect結(jié)合的跨多表引用 右圖中Indirect產(chǎn)生一個(gè)引用維數(shù)的變化 offset的height參數(shù)產(chǎn)生了一個(gè)區(qū)域尺寸 高度 的變化 姓名 注意 Indirect中的Ref text參數(shù)同offset中的height參數(shù)是一一對(duì)應(yīng)的 連續(xù)多表三維引用和引用函數(shù)產(chǎn)生的多維引用的區(qū)別 1連續(xù)多表三維引用是將整個(gè)引用作為一個(gè)結(jié)果返回給Excel 而引用函數(shù)產(chǎn)生的三維引用以及多維引用是將不同空間不同平面上的區(qū)域引用作為多個(gè)結(jié)果返回給Excel 2其支持的函數(shù)也大不相同 連續(xù)多表三維引用做為參數(shù)支持的函數(shù)只對(duì)該參數(shù)返回一個(gè)結(jié)果 而引用產(chǎn)生的多維引用做為參數(shù)的函數(shù)對(duì)其引用中的每個(gè)區(qū)域分別計(jì)算后返回多個(gè)結(jié)果 3連續(xù)多表三維引用是其三維結(jié)構(gòu)是物理存在的 而引用函數(shù)產(chǎn)生的多維引用其結(jié)構(gòu)是虛擬獲得的 4連續(xù)多表三維引用每個(gè)平面區(qū)域的大小和行列位置時(shí)相同的 而引用函數(shù)產(chǎn)生的多維引用每個(gè)區(qū)域引用的大小和行列位置都可以不同 小結(jié) 1從上面的分析 我們可以了解引用函數(shù)產(chǎn)生的多維引用是如何形成的 數(shù)組參數(shù)的維數(shù) 維度 行列方向 值的變化會(huì)對(duì)引用結(jié)果的維數(shù) 引用區(qū)域的位置和尺寸產(chǎn)生影響 Ref text參數(shù)列方向變化產(chǎn)生的多個(gè)空間 Ref text 參數(shù)數(shù)組 中數(shù)行方 向變化產(chǎn) 生多個(gè)平 面 Ref text參數(shù)列方向變化產(chǎn)生的多個(gè)空間 第一學(xué)期成績(jī)表 姓名語(yǔ)文數(shù)學(xué)英語(yǔ) 張三857280 李四996490 王五956897 第二學(xué)期成績(jī)表 姓名語(yǔ)文數(shù)學(xué)英語(yǔ) 張三957582 李四926888 王五897098 第三學(xué)期成績(jī)表 姓名語(yǔ)文數(shù)學(xué)英語(yǔ) 張三919981 李四876299 王五937293 第四學(xué)期成績(jī)表 姓名語(yǔ)文數(shù)學(xué)英語(yǔ) 張三919981 李四876299 王五937293 英語(yǔ) 81 99 93 Indirect的 Ref text 參數(shù)數(shù)組 中數(shù)行方 向變化產(chǎn) 生多個(gè)平 面 Ref text參數(shù)列方向變化產(chǎn)生的多個(gè)空間 姓名語(yǔ)文 95 數(shù)學(xué) 99 62 Offset的height參數(shù)產(chǎn)生一個(gè)區(qū)域高度的變化 1111113333333 4444444 3333333333 4444444444 5 Ref text參數(shù)數(shù)組 中數(shù)行方向變化 產(chǎn)生多個(gè)平面 24維以下的多維引用可以返回一個(gè)二維以?xún)?nèi)的 區(qū)域數(shù)組 我們只要將 區(qū)域數(shù)組 中的每個(gè)區(qū)域用函數(shù)分別同步求值 就可以獲得一個(gè)二維以?xún)?nèi)的數(shù)組 引用函數(shù)產(chǎn)生的多維引用的應(yīng)用實(shí)例 OFFSET B 7 0 1 2 2 2 右圖是由兩個(gè)參數(shù)使用一維數(shù)組產(chǎn)生的不同方向上的變化 VALUE OFFSET B 7 0 1 2 0 1 2 2 注意這里 和 號(hào)的區(qū)別 按F9可以看到公式返回 VALUE VALUE VALUE VALUE VALUE VALUE 由于4維數(shù)組無(wú)法顯示出來(lái)所以返回了一個(gè)按3 2的二維區(qū)域數(shù)組 每個(gè) VALUE 都代表一個(gè)區(qū)域 Row參 數(shù)變化 產(chǎn)生多 個(gè)平面 未經(jīng)許可 請(qǐng)勿抄載 Apolloh 黃朝陽(yáng) 友情提示 本工作簿中使用了宏表函數(shù)來(lái)顯示 公式文本 為了使公式文本能被正確地顯 示出來(lái) 請(qǐng)確認(rèn)您的Excel中的宏安全性 是否允許執(zhí)行宏 淺談在引用函數(shù)中使用數(shù)組參數(shù)產(chǎn)生的多維引用及其應(yīng)用 第二部分 用OFFSET INDIRECT來(lái)對(duì)單元格和區(qū)域進(jìn)行引用時(shí) 如果部分或全部參數(shù)使用數(shù)組 就會(huì)產(chǎn)生一個(gè)三維甚至三維以上的引用 下面我們將兩個(gè)函數(shù)產(chǎn)生多維引用的情況進(jìn)行解析 單個(gè)引用和區(qū)域引用及合并區(qū)域都是在一個(gè)平面上 其中合并區(qū)域 是使得平面上同時(shí)放置了多個(gè)獨(dú)立的單元格或區(qū)域引用 而 維引用實(shí)際上是將各個(gè)引用區(qū)域放到了不同的平面的 其中每個(gè)平面只有一個(gè)單元格或區(qū)域引用 不同平面的引用形成一個(gè)空間 函數(shù)產(chǎn)生的多維引用的基本元素是存放于每個(gè)平面上的單個(gè)單元格或區(qū)域引用 只有先將這些區(qū)域轉(zhuǎn)換成常數(shù) 多維引用才能被應(yīng)用于數(shù)組運(yùn)算 Offset函數(shù)的各參數(shù)及Indierect的第一參數(shù)都可以使用數(shù)組 但每個(gè)數(shù)組參數(shù)的維數(shù)最多不能超過(guò)2維 因?yàn)镋xcel函數(shù)不能正確處理3維以上的數(shù)組 在Offset函數(shù)中任何一個(gè)參數(shù)中增加數(shù)組的維數(shù) 是否會(huì)對(duì)offset產(chǎn)生引用的維數(shù)產(chǎn)生影響 具體還需要看每個(gè)數(shù)組參數(shù)的維度方向是否一致 在已經(jīng)有其他參數(shù)是數(shù)組的情況下 增加另一個(gè)一元數(shù)組參數(shù) 不會(huì)對(duì)引用的維數(shù)產(chǎn)生影響 一些參數(shù) 如offset的height和width中所使用的數(shù)組的行列方向是否一致會(huì)影響引用產(chǎn)生的各區(qū)域的尺寸大小 Excel函數(shù)能處理的引用最多只有4維 可同過(guò)函數(shù)將其中2維平面先轉(zhuǎn)換計(jì)算成常量 然后形成一個(gè)2維以?xún)?nèi)的數(shù)組參與數(shù)組運(yùn)算 3維引用是一個(gè)空間包含一個(gè)以上的平面 每個(gè)平面上都是一個(gè)連續(xù)的區(qū)域引用 上圖是下列公式的圖解 其實(shí)質(zhì)是Row參數(shù)變化產(chǎn)生了多個(gè)平面 按F9可以看到公式返回 VALUE VALUE VALUE 由于三維數(shù)組無(wú)法顯示出來(lái)所以返回了一個(gè)按行方向排列的一維3 1的區(qū)域數(shù)組 每個(gè) VALUE 都代表一個(gè)區(qū)域 區(qū)域數(shù)組 把一個(gè)區(qū)域看成是一個(gè)元素 那么多個(gè)區(qū)域按一定排列順序組成的數(shù)組叫區(qū)域數(shù)組 注 把參數(shù)改為一元數(shù)組如 1 這樣的數(shù)組 結(jié)果也是3維引用 雖然其只產(chǎn)生一個(gè)平面 但仍然是一個(gè)三維空間 111 222 222 333 333 444 11111 22222 22222 33333 33333 44444 右圖是由一個(gè)參數(shù)使用二維數(shù)組產(chǎn)生的不同方向上的變化 VALUE 按F9可以看到公式返回 VALUE VALUE VALUE VALUE VALUE VALUE 由于4維數(shù)組無(wú)法顯示出來(lái)所以返回了一個(gè)按3行2列方向排列的二維區(qū)域數(shù)組 每個(gè) VALUE 都代表一個(gè)區(qū)域 右圖是由Reference參數(shù)使用二維數(shù)組產(chǎn)生的引用區(qū)域起點(diǎn)不同的變化 VALUE OFFSET OFFSET B 7 1 3 1 3 2 2 按F9可以看到公式返回 VALUE VALUE VALUE VALUE 一共2 2 4個(gè)區(qū)域 右圖是由Ref text參數(shù)使用二維數(shù)組產(chǎn)生的各區(qū)域尺寸大小的不規(guī)則變化 右圖是由Ref text參數(shù)使用二維數(shù)組產(chǎn)生的各區(qū)域尺寸大小的不規(guī)則變化 因?yàn)镋xcel最多只能把4維引用處理成2維數(shù)組 再增加維數(shù) Excel就無(wú)法進(jìn)一步處理了 所以只能返回其中的第一個(gè)4維引用結(jié)果 由于函數(shù)的參數(shù)有多個(gè) 每個(gè)參數(shù)都支持二維數(shù)組 一一列舉其多維引用的變化將是一項(xiàng)很龐大的工作 而且不是每種情況都有實(shí)用價(jià)值 所以維數(shù)變化就說(shuō)這么多 大家可以從實(shí)例中去體會(huì) 右圖是由Height參數(shù)使用一維數(shù)組產(chǎn)生的各區(qū)域引用的高度的變化 OFFSET B 7 1 2 3 4 5 右圖是由Height和width參數(shù)使用一維數(shù)組產(chǎn)生的各區(qū)域引用的高度和寬度同時(shí)變化 OFFSET B 7 1 2 3 1 2 3 按F9可以看到公式返回 1 VALUE VALUE 這里的高度和寬度的值一對(duì)一對(duì)應(yīng) 產(chǎn)生了 1 3個(gè)區(qū)域 下面的公式 寬度參數(shù)中的數(shù)組在維度方向和上式不同 高度和寬度的值多對(duì)多對(duì)應(yīng)后 形成了一個(gè)3 3的區(qū)域數(shù)組 按F9可以看到公式返回 1 VALUE VALUE VALUE VALUE VALUE VALUE VALUE VALUE 一共9個(gè)區(qū)域 上述Offset產(chǎn)生的多維引用中的每個(gè)區(qū)域在尺寸大小的變化是由height和width決定的 但都遵循了一定的運(yùn)算規(guī)則 是有規(guī)律可循的 而下面要講的Indirect的多維引用則不同 其每個(gè)區(qū)域的尺寸大小是可以任意變化的 可以無(wú)規(guī)律性 222 333 111 222 222 333 333 444 333 444 444 555 22222 33333 44444 55555 222222222 333333333 444444444 555555555 1111113333333 4444444 3333333333 4444444444 5 VALUE INDIRECT B5 D5 D7 E8 B7 E8 B9 按F9可以看到公式返回 VALUE VALUE VALUE VALUE 一共2 2 4個(gè)區(qū)域 上下半學(xué)期的變化 OFFSET INDIRECT 成績(jī)表1 A2 成績(jī)表2 B2 成績(jī)表3 C2 成績(jī)表4 D2 1 2 3 4 1111113333333 4444444 3333333333 4444444444 5 這是跨多表的多維引用 其中Indirect的Reft text參數(shù)使用了二維數(shù)組產(chǎn)生了一個(gè)2 2的二維區(qū)域數(shù)組 右圖中Indirect產(chǎn)生一個(gè)引用維數(shù)的變化 offset的height參數(shù)產(chǎn)生了一個(gè)區(qū)域尺寸 高度 的變化 注意 Indirect中的Ref text參數(shù)同offset中的height參數(shù)是一一對(duì)應(yīng)的 連續(xù)多表三維引用是將整個(gè)引用作為一個(gè)結(jié)果返回給Excel 而引用函數(shù)產(chǎn)生的三維引用以及多維引用是將不同空間不同平面上的區(qū)域引用作為多個(gè)結(jié)果返回給Excel 其支持的函數(shù)也大不相同 連續(xù)多表三維引用做為參數(shù)支持的函數(shù)只對(duì)該參數(shù)返回一個(gè)結(jié)果 而引用產(chǎn)生的多維引用做為參數(shù)的函數(shù)對(duì)其引用中的每個(gè)區(qū)域分別計(jì)算后返回多個(gè)結(jié)果 連續(xù)多表三維引用是其三維結(jié)構(gòu)是物理存在的 而引用函數(shù)產(chǎn)生的多維引用其結(jié)構(gòu)是虛擬獲得的 連續(xù)多表三維引用每個(gè)平面區(qū)域的大小和行列位置時(shí)相同的 而引用函數(shù)產(chǎn)生的多維引用每個(gè)區(qū)域引用的大小和行列位置都可以不同 從上面的分析 我們可以了解引用函數(shù)產(chǎn)生的多維引用是如何形成的 數(shù)組參數(shù)的維數(shù) 維度 行列方向 值的變化會(huì)對(duì)引用結(jié)果的維數(shù) 引用區(qū)域的位置和尺寸產(chǎn)生影響 第一學(xué)期成績(jī)表 姓名語(yǔ)文數(shù)學(xué)英語(yǔ) 張三857280 李四996490 王五956897 第二學(xué)期成績(jī)表 姓名語(yǔ)文數(shù)學(xué)英語(yǔ) 張三957582 李四926888 王五897098 第三學(xué)期成績(jī)表 姓名語(yǔ)文數(shù)學(xué)英語(yǔ) 張三919981 李四876299 王五937293 第四學(xué)期成績(jī)表 姓名語(yǔ)文數(shù)學(xué)英語(yǔ) 張三919981 李四876299 王五937293 Offset的height參數(shù)產(chǎn)生一個(gè)區(qū)域高度的變化 4維以下的多維引用可以返回一個(gè)二維以?xún)?nèi)的 區(qū)域數(shù)組 我們只要將 區(qū)域數(shù)組 中的每個(gè)區(qū)域用函數(shù)分別同步求值 就可以獲得一個(gè)二維以?xún)?nèi)的數(shù)組 由于三維數(shù)組無(wú)法顯示出來(lái)所以返回了一個(gè)按行方向排列的一維3 1的區(qū)域數(shù)組 每個(gè) VALUE 都代表一個(gè)區(qū)域 注 把參數(shù)改為一元數(shù)組如 1 這樣的數(shù)組 結(jié)果也是3維引用 雖然其只產(chǎn)生一個(gè)平面 但仍然是一個(gè)三維空間 按F9可以看到公式返回 VALUE VALUE VALUE VALUE VALUE VALUE 由于4維數(shù)組無(wú)法顯示出來(lái)所以返回了一個(gè)按3 2的二維區(qū)域數(shù)組 每個(gè) VALUE 都代表一個(gè)區(qū)域 按F9可以看到公式返回 VALUE VALUE VALUE VALUE VALUE VALUE 由于4維數(shù)組無(wú)法顯示出來(lái)所以返回了一個(gè)按3行2列方向排列的二維區(qū)域數(shù)組 每個(gè) VALUE 都代表一個(gè)區(qū)域 按F9可以看到公式返回 VALUE VALUE VALUE VALUE 一共2 2 4個(gè)區(qū)域 右圖是由Ref text參數(shù)使用二維數(shù)組產(chǎn)生的各區(qū)域尺寸大小的不規(guī)則變化 其支持的函數(shù)也大不相同 連續(xù)多表三維引用做為參數(shù)支持的函數(shù)只對(duì)該參數(shù)返回一個(gè)結(jié)果 而引用產(chǎn)生的多維引用做為參數(shù)的函數(shù)對(duì)其引用中的每個(gè)區(qū)域分別計(jì)算后返回多個(gè)結(jié)果 按F9可以看到公式返回 VALUE VALUE VALUE VALUE 一共2 2 4個(gè)區(qū)域 OFFSET INDIRECT 成績(jī)表1 A2 成績(jī)表2 B2 成績(jī)表3 C2 成績(jī)表4 D2 1 2 3 4 淺談在引用函數(shù)中使用數(shù)組參數(shù)產(chǎn)生的多維引用及其應(yīng)用 第三部分 本文分為三部分 1 認(rèn)識(shí)引用和區(qū)域及其維數(shù)2 引用函數(shù)產(chǎn)生的多維引用3 引用函數(shù)產(chǎn)生的多維引用的應(yīng)用實(shí)例 可以使用引用函數(shù)產(chǎn)生的多維引用的作為參數(shù)的函數(shù) 1通過(guò)對(duì)引用函數(shù)產(chǎn)生的多維引用的討論 我們已經(jīng)得知 多維引用在沒(méi)有使用函數(shù)將其各區(qū)域計(jì)算處理成常量前是無(wú)法顯示 也不能作為內(nèi)存數(shù)組直接參與計(jì)算 2目前已知的可以支持引用函數(shù)產(chǎn)生的多維引用 并將引用中的各區(qū)域計(jì)算后作為內(nèi)存數(shù)組返回的函數(shù)有 COUNTBLANK COUNTIF D數(shù)據(jù)庫(kù)函數(shù) RANK SUBTOTAL SUMIF 等 3引用函數(shù)產(chǎn)生的多維引用經(jīng)過(guò)上述函數(shù)計(jì)算后的結(jié)果值是一個(gè)二維以?xún)?nèi)的數(shù)組 其元素的個(gè)數(shù)和多維引用所能返回的區(qū)域個(gè)數(shù)相同 4其中 和 函數(shù)比較特殊 他們只能返回每個(gè)區(qū)域的第一個(gè)值 并將其轉(zhuǎn)和為數(shù)值或文本 所以當(dāng)多維引用的每個(gè)區(qū)域都是一個(gè)單元格時(shí) 使用這兩個(gè)函數(shù)比較合適 5除了Rank函數(shù)和一些 數(shù)據(jù)庫(kù)函數(shù)外其他函數(shù)的多維引用計(jì)算都有很高的實(shí)用價(jià)值 下面將以幾種典型的多維引用方式來(lái)說(shuō)明這些函數(shù)是如何進(jìn)行多維引用計(jì)算 區(qū)域?yàn)閱涡袉瘟械牡亩嗑S引用 學(xué)生成績(jī)表 語(yǔ)文數(shù)學(xué)英語(yǔ) 孫二836278 張三669857 李四976153 王五657665 趙六557064 錢(qián)七897773 例1 求表一中每個(gè)學(xué)生的總成績(jī) 返回內(nèi)存數(shù)組 例2 求表一中每個(gè)學(xué)生的最高單科成績(jī)和平均成績(jī) Subtotal 9 SumifSubtotal 4 subtotal 1 孫二223223孫二8374 張三221221張三9874 李四211211李四9770 王五206206王五7669 趙六189189趙六7063 錢(qián)七239239錢(qián)七8980 例4 求表一中各科的最高成績(jī)例5 求表一中各科的最低成績(jī) Subtotal 4 列方向 Subtotal 5 語(yǔ)文數(shù)學(xué)英語(yǔ)語(yǔ)文數(shù)學(xué)英語(yǔ) 979878556153 水果上市的銷(xiāo)售數(shù)量水果單價(jià) 5號(hào)6號(hào)7號(hào)單價(jià) 蘋(píng)果2933蘋(píng)果1 5 1101AVERAGE 2102COUNT 3103COUNTA 4104MAX 5105MIN 6106PRODUCT 7107STDEV 8108STDEVP 9109SUM 10110VAR 11111VARP Subtal參數(shù)對(duì)照表 香蕉3521香蕉1 6 李子1430李子1 7 栗子153216栗子2 梨子29梨子2 5 荔枝143237荔枝2 3 例6 求表二中各種水果有銷(xiāo)售的天數(shù)例7 用表二 表三的數(shù)據(jù)求三日銷(xiāo)售額最高的水果 Subtotal 2 Subtotal 3 Countblank CountifSubtotal 9 蘋(píng)果2222荔枝 香蕉2222 李子2222例8 求1天以上沒(méi)有銷(xiāo)售的水果品種個(gè)數(shù) 栗子3333subtotal 2 梨子11114 荔枝3333 用餐及餐費(fèi)記錄 日期張三李四王五趙六錢(qián)七餐費(fèi) 1 1中餐11125 00 1 1晚餐11119 00 1 2中餐1111127 00 1 2晚餐113 00 1 3中餐112 00 1 3晚餐1118 00 1 4中餐111122 00 1 4晚餐1115 00 這是我曾出過(guò)的一道測(cè)試題 也有不用多維引用的解法 其主要目的是要說(shuō)明內(nèi)存數(shù)組的概念的 具體見(jiàn)貼子鏈接 區(qū)域的尺寸遞增或遞減的多維引用 紙箱規(guī)格 厘米 例10 求表五中紙箱的體積 立方厘米 長(zhǎng)框高Subtotal 6 L001803649L001141 120 L002923559L002189 980 L003653253L003110 240 L004672739L00470 551 L005673455L005125 290 L006932932L00686 304 水果清單例12 求表六中不重復(fù)的水果清單現(xiàn)金收入支出表 蘋(píng)果 VALUE 收入 香蕉 VALUE 2005 4 15 000 李子 VALUE 2005 4 21 000 栗子 VALUE 2005 4 34 000 李子 VALUE 2005 4 4 栗子 VALUE 2005 4 56 000 蘋(píng)果 VALUE 2005 4 6 T OFFSET B 90 SMALL IF COUNTIF OFFSET B 91 ROW B 91 B 97 ROW B 90 B 91 B 97 1 ROW B 91 B 97 ROW B 90 ROW B 91 B 97 ROW D 90 T函數(shù)是對(duì)一個(gè)每個(gè)區(qū)域只有一個(gè)單元格的多維引用計(jì)算 Countif函數(shù)對(duì)一個(gè)區(qū)域高度遞增的三維引用計(jì)算 環(huán)比成長(zhǎng)率表例13 用表八數(shù)據(jù)求每年較第一年的定比成長(zhǎng)率 要求為內(nèi)存數(shù)組 年度環(huán)比成長(zhǎng)率Subtotal 6 0 2001100 00 2001100 00 SUBTOTAL 6 OFFSET C 104 ROW C 104 C 108 ROW C 104 1 2002110 00 2002110 00 2003125 00 2003137 50 2004130 00 2004178 75 2005150 00 2005268 13 表九 項(xiàng)目投資與收益表例14 求表九中各項(xiàng)目的最大投資和 項(xiàng)目投資收益43 000Dmax 第一參數(shù)為區(qū)域尺寸遞減的多維引用 第三個(gè)參數(shù)返回每2行為區(qū)域的多維引用 A4 0002 000 SUM IF MATCH B 114 B 126 B 114 B 126 0 ROW B 114 B 126 ROW B 113 DMAX OFFSET B 113 ROW B 114 B 126 ROW B 113 1 ROWS B 114 B 126 ROW B 114 B 126 ROW B 113 2 2 2 OFFSET B 113 ROW B 114 B 126 ROW B 113 1 2 B5 0003 000這是我曾出過(guò)的一道測(cè)試題 chenjun版主給的用Dmax多維引用計(jì)算來(lái)實(shí)現(xiàn)的