【原】cell+多維引用+n(if(1,))+mmult置換2列數(shù)據(jù)

劉卓學(xué)EXCEL 2021-04-02

展開全文

小伙伴們好。昨天說了mmult置換2列數(shù)值或2列文本的方法，并且在最后給大家留了個思考題，如何置換1列文本和1列數(shù)值呢？今天就來說說這個問題。其實(shí)大體的思路和昨天的差不多，只是在降維處理的方式上有所不同。我們昨天對文本降維用的t函數(shù)，但是一列數(shù)值和一列文本，再用t降維就不行了，會把數(shù)字丟失。當(dāng)然用n函數(shù)也是不行的，會把文本丟失。那就要用到cell這個函數(shù)。

還是先看例子吧，下圖左表A列是出生日期，B列是姓名。現(xiàn)在要置換為右表所示，就是I和J2列。和昨天的方法一樣，把數(shù)據(jù)區(qū)域轉(zhuǎn)換為行列結(jié)合的2列數(shù)字，然后用mmult置換2列數(shù)字，接下來用text轉(zhuǎn)為文本型的單元格地址，再用indirect轉(zhuǎn)為多維引用，最后用cell取得對應(yīng)的內(nèi)容。

mmult的第1參數(shù)ROW(3:10)/1%+COLUMN(A:B)返回的結(jié)果如下圖①所示，用A3:B10這個區(qū)域的行號乘以100再加上列號。第2參數(shù)還是1-munit(2)，如下圖②所示。

=MMULT(ROW(3:10)/1%+COLUMN(A:B),1-MUNIT(2))返回的結(jié)果如下圖④所示，這樣就把2列數(shù)字置換了。接下來就要用text轉(zhuǎn)為文本型單元格地址，也就是R1C1的樣式。

=TEXT(MMULT(ROW(3:10)/1%+COLUMN(A:B),1-MUNIT(2)),"R0C00")這部分就把上一步的數(shù)值轉(zhuǎn)為R1C1樣式的文本型單元格地址，如下圖灰色部分所示。

下圖的公式用indirect返回引用，形成了多維引用；然后用t函數(shù)降維，昨天也是這么做的，但得到的結(jié)果如下圖所示。發(fā)現(xiàn)只有姓名，沒有日期。其實(shí)是t函數(shù)把數(shù)字(日期就是數(shù)字)變?yōu)榭瘴谋玖?，這樣的話就把數(shù)字丟失了。所以這種方法就不行了。

那么我們?yōu)榱私鉀Q這個問題，就要用到cell+多維引用+t/n(if(1,))這個搭檔。t或n(if(1,))把一個數(shù)組在時間和空間上整合在了一起，這話具體什么意思，我現(xiàn)在也不懂，會用就行了。把它看作一個常量數(shù)組或者一個整體就可以了。比如vlookup的第1參數(shù)不支持?jǐn)?shù)組的運(yùn)算，當(dāng)用了t/n(if(1,))結(jié)構(gòu)后就支持了。

=CELL("contents",INDIRECT(TEXT(N(IF(1,MMULT(ROW(3:10)/1%+COLUMN(A:B),1-MUNIT(2)))),"R0C00"),))這部分就得到了置換后的效果，雖然下圖顯示的是錯誤值，但選中公式按F9就可以看到結(jié)果。為什么放在單元格中是錯誤值呢？據(jù)說是易失性函數(shù)和多維引用的結(jié)果不能直接輸出在單元格中。如果想要輸出結(jié)果，外面可以套個index函數(shù)，如下第2圖所示，得到的數(shù)字就是日期。

再來簡單說一說上面的公式，我在mmult的前面加上了n(if(1,))，那么它返回的結(jié)果可以看作一個整體。因?yàn)閙mult返回的結(jié)果是數(shù)字，所以用n+if；如果返回的是文本可以用t+if，比如你可以在text的前面放置t+if。形成一個整體后參與到多維引用中，這樣的方式形成的多維引用和普通的多維引用有所差別，而用cell函數(shù)正好和它結(jié)合使用。現(xiàn)在的我對這方面的理解也很有限，所以也說不清楚，主要還是靠體驗(yàn)，多練習(xí)你就會有自己的理解。

cell是一個信息函數(shù)，它可以返回引用中第1個單元格的一些信息，比如地址，行號，列號，內(nèi)容，格式，文件名等。函數(shù)語法結(jié)構(gòu)為CELL(info_type, [reference]) ，有2個參數(shù)。第1個參數(shù)是個文本值，指定要返回的信息類型；第2參數(shù)是可選參數(shù)，必須是單元格引用，不能是數(shù)組。

下圖是cell第1參數(shù)指定的一些單元格信息類型，常用的有地址，列號，內(nèi)容，文件名，格式，行號，寬度等。

下面看幾個簡單的示例。輸入公式=CELL("contents",A1:C3)，返回的結(jié)果是A。第1參數(shù)"contents"代表內(nèi)容，也就是單元格的值。第2參數(shù)的區(qū)域是A1:C3。值得注意的是：它不是返回A1:C3整個區(qū)域的內(nèi)容，而是返回這個區(qū)域第1個單元格的內(nèi)容，也就是左上角單元格的內(nèi)容。左上角單元格是A1，所以返回它的內(nèi)容A。同樣的，公式=CELL("row",A1:C3)返回A1單元格的行號，當(dāng)然是1；公式=CELL("width",A1:C3)返回A1單元格的寬度8。

總結(jié)一下cell的特征，第2參數(shù)只能是引用，并且只能返回引用區(qū)域左上角單元格的信息。這就是它為什么和多維引用結(jié)合使用的理由，多維引用當(dāng)然是引用，符合第2參數(shù)的特性；但是它也只能返回多維引用的第1個值。而用了t/n(if(1,))后就能返回多維引用的多個值了。你可以把置換那個公式中的n(if(1,))去掉，看看cell返回的結(jié)果是什么。今天的文章需要有多維引用的基礎(chǔ)。

利用這些函數(shù)得到了置換后的數(shù)組，就可以用vlookup逆向查詢了。在M3單元輸入公式

=VLOOKUP(L3,CELL("contents",INDIRECT(TEXT(N(IF(1,MMULT(ROW(3:10)/1%+COLUMN(A:B),1-MUNIT(2)))),"r0c00"),)),2,)，完成。