讓你的Excel表動起來

本周問題，利用名稱管理器完成二維表的匹配返回，讓你的表格動起來。

話本次解決方案相對複雜，能看明白並且自己會用的基本上函數使用沒什麼問題了。

解決方法為個人原創，有更好解決方案歡迎留言打臉——

問題

原表：

需求：想在某個單元格選擇某個部門後，自動將有數量的產品列在下表中！

問題拆分：

一、先解決如果公司固定，只是A部門，如何能將含有數據的產品列在下表。

二、如何結果部門不同時，如何變成另外一個部門的數據

三、如何將某個單元格設置為不同部門可選（三個問題中最簡單的一個）

解決問題一：

如何在確定A部門的情況，將產品列在下表。

思路：先查看B列哪個單元格有數據、查看有數據的單元格的在第幾行，將行數從小到大進行排序，將A列對應行數的單元格拷貝到下面。

bingo！

將每一句話用一個函數實現。

先查看B列哪個單元格有數據->

IF（判斷條件，為真返回什麼，為假返回什麼）函數判斷是否為空

查看有數據的單元格的在第幾行->

Row（單元格）返回單元格的行數

將行數從小到大進行排序->

Small（列表，第幾個最小的值）返回列表中的第幾個最小的值

將A列對應行數的單元格拷貝到下面->

Index（列表，個數）返回列表中第幾個值

口述思路：

先挨個單元格判斷B列是否有數據，如果有返回單元格對應的行數，如果沒有返回值為空。

代碼實現：

IF($B$2:$B$9="","",ROW($B$2:$B$9)）

返回IF函數中最小的那個數字（空不列入排序）

SMALL(IF($B$2:$B$9="","",ROW($B$2:$B$9)),1)

在A列中返回B列有數字行數的值

INDEX($A$1:$A$9,SMALL(IF($B$2:$B$9="","",ROW($B$2:$B$9)),1))

目前有個問題了，我需要依次返回第一個最小的值，第二個最小值，第N個最小值！怎麼快速實現呢。

我們將數字1用Row（A1）替換，這樣自動填充到第二個單元格的時候就會變成Row（A2）

所以目前完整公式為：

=INDEX($A$1:$A$9,SMALL(IF($B$2:$B$9="","",ROW($B$2:$B$9)),ROW(A1)))

由於其中用到了數組函數，輸入公式後要同時按住Ctrl+Shift+Enter

雙擊公式自動填充後效果如下：

如果B列只有三行數據，則返回值前三行是有正確輸出的，但是後面的就會報錯，我們想一個方法規避這種錯誤提醒。

Iferror（函數，如果有錯誤返回值顯示什麼）

所以將原有的公式外面包裹一個Iferror函數，如果有錯誤，顯示空

=IFERROR(INDEX($A$1:$A$9,SMALL(IF($B$2:$B$9="","",ROW($B$2:$B$9)),ROW(A1))),"")

這樣錯誤值就被隱藏掉了。

解決問題二：

如何結果部門不同時，如何變成另外一個部門的數據

原表：

上一個問題的公式：

=IFERROR(INDEX($A$1:$A$9,SMALL(IF($B$2:$B$9="","",ROW($B$2:$B$9)),ROW(A1))),"")

我們希望當選擇B部門的時候，是用C2：C9單元格作判斷；C部門的時候，用D2：D9做判斷。

如何方便快捷的更改選擇的區域呢？且隨著某個單元格的內容變化而變化呢？

介紹兩個小東西：

1、名稱管理器

2、Indirect函數

名稱管理器是啥，就是將某個區域命名為一個名字。

Indirect函數啥意思呢？含義此函數立即對引用進行計算，並顯示其內容。通俗的講。如果將B2：B9命名為部門A，則這個區域作為參數的時候，可以輸入B2:B9，也可以輸入Indirect（部門A）。大概就是就這個意思。

怎麼做呢？

第一步：選中列表區域

第二步：公式-根據所選內容創建

第三步：首行

至此，命名已經完成。

看一下我們之前編輯好的公式

=IFERROR(INDEX($A$1:$A$9,SMALL(IF($B$2:$B$9="","",ROW($B$2:$B$9)),ROW(A1))),"")

我們需要每次選擇不同內容時候，塗紅的區域跟著變化，所以用indirect函數實現。

部門所在的單元格是B12，所以進行引用

=IFERROR(INDEX($A$1:$A$9,SMALL(IF(INDIRECT($B$12)="","",ROW(INDIRECT($B$12))),ROW(A1))),"")

解決問題三

如何將某個單元格設置為不同部門可選

選中塗黃的單元格，設置數據有效性即可

第一步：選中單元格-單擊有效性驗證

第二步：選中序列-選中標題的行

最後就實現了！

推薦閱讀：