使用“矩陣法”引用產品的完整型號

孫凡麗

在圖1中，假設A列為空調的完整型號數據，在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”組合鍵（數組公式）。最外層的{}是按下該組合鍵后自動產生的，不能手輸。以后只要在C2單元格中輸入空調型號的部分信息，在D2單元格中就會引用所有與之相關的空調型號。該公式較為復雜，下面詳解公式的計算過程。

一、分割信息

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

二、矩陣轉型

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

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

接著使用“-”減函數再次轉型矩陣，將“FALSE”轉換為“0”、“TRUE”轉換為“-1”。此時可以發現，包含空調部分信息的那一行，轉換后的結果全部為“0”（圖5）。

三、計算數組

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

四、定準“0”位

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

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