使用“矩陣法”引用產(chǎn)品的完整型號

孫凡麗

在圖1中，假設A列為空調(diào)的完整型號數(shù)據(jù)，在D2單元格中輸入公式“ ={IFERROR（INDEX（$A$1：$A$10，SMALL（IF（MMULT（-ISERR（FIND（MID（$C$2，COLUMN（A：Z），1），$A$1：$A$10）），ROW（$A$1：$A$26）^0）=0，ROW（$A$1：$A$10），COUNTA（A：A）+1），ROW（A1））），"/"）}”。注意，輸入完公式后需要按下“Ctrl+Shift+Enter”組合鍵（數(shù)組公式）。最外層的{}是按下該組合鍵后自動產(chǎn)生的，不能手輸。以后只要在C2單元格中輸入空調(diào)型號的部分信息，在D2單元格中就會引用所有與之相關的空調(diào)型號。該公式較為復雜，下面詳解公式的計算過程。

一、分割信息

通過MID和COLUMN函數(shù)將C2單元格中的信息分割為x個單個字符，x根據(jù)A列中空調(diào)型號的最長字符數(shù)確定，并將其對應為列字母。在本公式中，假設A列中的最長字符數(shù)為26個，對應的列字母為Z，即將C2單元格中的信息分割為26個字符，沒有字符的部分使用空值湊齊（圖2）。

二、矩陣轉(zhuǎn)型

使用FI N D函數(shù)將2 6個字符與A1： A10數(shù)據(jù)區(qū)域中的內(nèi)容逐個比對，形成一個10 行2 6 列的矩陣。若字符存在于某個單元格中，就顯示其在該單元格字符中的位置序號，不存在就顯示“#VALUE！”，空值部分對比后顯示“1”（圖3）。

然后借助ISERR函數(shù)將上述矩陣中的數(shù)據(jù)轉(zhuǎn)型，將位置序號和“1”轉(zhuǎn)換為“FALSE”、“#VALUE！”轉(zhuǎn)換為“TRUE”（圖4）。

接著使用“-”減函數(shù)再次轉(zhuǎn)型矩陣，將“FALSE”轉(zhuǎn)換為“0”、“TRUE”轉(zhuǎn)換為“-1”。此時可以發(fā)現(xiàn)，包含空調(diào)部分信息的那一行，轉(zhuǎn)換后的結(jié)果全部為“0”（圖5）。

三、計算數(shù)組

使用MMULT 矩陣函數(shù)計算數(shù)組。數(shù)組1為圖5 所示的10 行2 6 列的矩陣，數(shù)組2 為ROW（$A$1：$A$26）^0，即26行1列中所有值為“1”的矩陣。計算結(jié)果是兩個數(shù)組的乘積之和，為10行1列的數(shù)組（圖6）。

四、定準“0”位

使用I NDEX 、IF和SMALL函數(shù)將上述的計算結(jié)果數(shù)組按ROW（A$1：A$10）轉(zhuǎn)換為10行1列的位置行號數(shù)組，結(jié)果為“0”就顯示位置行號，非“0”則顯示“11”。并在該數(shù)組中找出第k個最小位置行號，D2單元格中k為ROW（A1），值為1，即選出第1個最小的位置行號5。然后從A1：A10數(shù)據(jù)區(qū)域中根據(jù)位置行號5引用第1個包含空調(diào)部分信息的完整型號。

下拉D 2 單元格中的公式到D 3～D1 0 單元格，R OW（ A1）會依次變?yōu)镽 O W （ A 2 ） 、ROW（A3）……，這樣就會引用第2個、第3個……的完整型號了。最后會使用IFERROR函數(shù)將不符合的結(jié)果標記為“/ ”。