你的老板給你這樣格式的excel: 然后讓你填充這樣的匯總: 怎么辦？ 怎么辦？ 怎么辦？ 那么，如何在區(qū)域范圍內(nèi)達(dá)到類似“vlookup”的功能呢？ 我們給出一種思路，因?yàn)镮D（Apple, Orange…）和其屬性的相對(duì)位置都是一樣的，那么只要找到ID所在單元格，然后作相應(yīng)的位置偏移，即可得到對(duì)應(yīng)結(jié)果的值。 那么我們只需要做2件事就可以了： 1、 找到ID的位置 2、 作相應(yīng)偏移 Step 1: 如何根據(jù)內(nèi)容找到單元格地址？（這是一句非常標(biāo)準(zhǔn)的，可以在各大搜索引擎得到答案的搜索用問句。下面給出一種但不是唯一的解決方案） 關(guān)鍵詞：Address，數(shù)組組合鍵(Ctrl + Shift + Enter) 公式： {=ADDRESS(MIN(IF(查詢范圍=目標(biāo)單元格,ROW(查詢范圍))),MIN(IF(查詢范圍=目標(biāo)單元格,COLUMN(查詢范圍))))} 我們先來解讀一下這個(gè)公式的邏輯， 1、 現(xiàn)在區(qū)域范圍內(nèi)找到與我們要查找的內(nèi)容相等的單元格，并且取得該單元格的行號(hào)和列號(hào) IF(查詢范圍=目標(biāo)單元格, ROW(查詢范圍)) IF(查詢范圍=目標(biāo)單元格, COLUMN(查詢范圍)) 2、 當(dāng)區(qū)域內(nèi)可能存在多處重復(fù)值時(shí)，取最先出現(xiàn)的結(jié)果（最小目標(biāo)行號(hào)/列號(hào)）。這個(gè)操作只是為了防止多結(jié)果情況下隨機(jī)取到了行列不對(duì)應(yīng)的情況，所以選MAX值也可以。 MIN(IF(查詢范圍=目標(biāo)單元格,ROW(查詢范圍))) 3、 輸入其它參數(shù)（引用類型，結(jié)果樣式….） 不知道大家有沒有注意到上面給出的公式被一個(gè)大的花括號(hào)括住了呢？ 這個(gè){}其實(shí)并不是輸入的，而是在輸入完公式后把常用的回車（Enter鍵）改成了數(shù)組組合鍵(Ctrl + Shift + Enter)。那么到底什么是數(shù)組組合鍵以及它強(qiáng)大的適用范圍我們下次再講！ 現(xiàn)在大家先留一個(gè)印象，涉及到多個(gè)數(shù)值項(xiàng)的運(yùn)算（比如此例中涉及到區(qū)域內(nèi)多個(gè)單元格的等值運(yùn)算）大多數(shù)情況下需要用到數(shù)組組合鍵。 其它還存在運(yùn)算結(jié)果為數(shù)組等等不同的情況也需要用到的，請(qǐng)大家關(guān)注后續(xù)內(nèi)容！ 下面我們來看一下這個(gè)公式和對(duì)應(yīng)參數(shù)。 ADDRESS(rownum, columnnum, absnum, a1, [sheettext]) rownum：表示要在單元格引用中使用的行號(hào)。 columnnum：表示要在單元格引用中使用的列號(hào)。 absnum：表示要返回的引用類型。（1或省略 - 絕對(duì)引用，2/3 - 混合引用， 4 - 相對(duì)引用） a1：表示返回的單元格地址的引用樣式。（0 - B2, 1 - R[2]C[2]，可省略） sheettext：表示指定要用外部引用的工作表的名稱。（可省略） 就其本質(zhì)而且ADDRESS函數(shù)就是一個(gè)取得地址的函數(shù)，至于到底取到什么值的地址，這個(gè)值如何查找，可以結(jié)合其它函數(shù)變化出無盡的組合。 Step 2: 如何進(jìn)行位置偏移并且得到值？關(guān)鍵詞：OFFSET，INDIRECT 公式： =OFFSET(INDIRECT(單元格地址), 行偏移值, 列偏移值) Offset函數(shù)以指定單元格為參照系，通過偏移量來得到新的單元格引用。 同樣的我們來看一下這個(gè)函數(shù)的參數(shù)： OFFSET(reference, rows, cols, [height], [width]) reference：表示指定單元格或者單元格區(qū)域的引用。 rows：上（下）偏移的行數(shù)。 cols：左（右）偏移的列數(shù)。 height：表示所要返回的引用區(qū)域的行數(shù)。（可省略） width：表示所要返回的引用區(qū)域的列數(shù)。（可省略） 可見OFFSET函數(shù)就能滿足行列偏移的需求。然而我們通過ADDRESS函數(shù)取到的實(shí)際上是一個(gè)文本。而OFFSET函數(shù)第一個(gè)參數(shù)需要的是一個(gè)引用，所以此處引出一個(gè)新的函數(shù)INDIRECT。 這個(gè)函數(shù)的基本功能非常簡(jiǎn)潔，Office官方的描述就是根據(jù)文本值返回其引用。(Returns the reference specified by a text string.) 就這個(gè)案例而言，品名對(duì)應(yīng)ID，在水平方向偏移了1，垂直方向不變，因此offset(address,0,1)即可。 最終，我們可以根據(jù)兩步的運(yùn)算，得到我們想要的結(jié)果。 小提示！當(dāng)公式復(fù)雜或者引用較多的時(shí)候，如下圖 在公式編輯時(shí)選中對(duì)應(yīng)引用，按下F9可以直觀的看到對(duì)應(yīng)的值或計(jì)算結(jié)果： 不選中任何引用/參數(shù)對(duì)于整個(gè)公式F9的話，則可以直接看到該公式的結(jié)果： |
|
|
