錦子老師

這是一位網友提出的，我有一個跨工作表二層下拉式選單的問題卡住了，想請教有什麼方法可以解決？

登錄工作表

客戶資料表

想要將客戶資料表中在登錄資料表A欄選擇地區，然後在B欄可以選取該地區的醫院。

這個問題首先需要將資料依地區分欄到一張新的工作表中。

依地區分類

1、在轉換工作表(這是錦子老師新增命名的)A1:E1儲存格依續輸入各個地區名稱。

2、在A2儲存格輸入公式：

=Iferror(offset(客戶!$a$1,Sumproduct(Large((客戶!$a$1:$a$30=轉換!A$1)*(Row(客戶!$a$1:$a$30)),Row()-1))-1,1,1,1),"")

再複製到A2:E10儲存格。

【公式說明】

Row(客戶!$A$1:$A$30) 傳回客戶工作表A1:A30的列號。

(客戶!$A$1:$A$30=轉換!A$1) 傳回客戶工作表A1:A30儲存格內容與轉換工作表A1儲存格內容一樣的儲存格位址。

Large((客戶!$A$1:$A$30=轉換!A$1)*(Row(客戶!$A$1:$A$30)),Row()-1) 將(客戶!$A$1:$A$30=轉換!A$1)傳回的儲存格位址抓取第N(Row()-1目前列號減一)大那筆記錄。

Sumproduct(Large((客戶!$A$1:$A$30=轉換!A$1)*(Row(客戶!$A$1:$A$30)),Row()-1))-1 傳回Large((客戶!$A$1:$A$30=轉換!A$1)*(Row(客戶!$A$1:$A$30)),Row()-1)的值。

Offset(客戶!$A$1,Sumproduct(Large((客戶!$A$1:$A$30=轉換!A$1)*(Row(客戶!$A$1:$A$30)),Row()-1))-1,1,1,1) 從客戶工作表A1儲存格移動Sumproduct(Large((客戶!$A$1:$A$30=轉換!A$1)*(Row(客戶!$A$1:$A$30)),Row()-1))-1傳回的列數，移動1欄，框選一列一欄資料。

Iferror(Offset(客戶!$A$1,Sumproduct(Large((客戶!$A$1:$A$30=轉換!A$1)*(Row(客戶!$A$1:$A$30)),Row()-1))-1,1,1,1),"")如果Offset(客戶!$A$1,Sumproduct(Large((客戶!$A$1:$A$30=轉換!A$1)*(Row(客戶!$A$1:$A$30)),Row()-1))-1,1,1,1) 傳回錯誤值則不填入資料。

設定範圍名稱

1、將A11:A8框選起來，再點取「公式 > 從選取範圍建立」圖示

2、在【以選取範圍建立名稱】對話方塊，點取「頂端列」核取方塊，使其打勾，其他空白。

3、點取「確定」鈕。

4、依續框選「B1:B6」、「C1:C4」、「D1:D7」、「E1:E7」儲存格範圍，重複2、與3、步驟。

設定資料驗證

當我們建立好轉換表後，接下來是將轉換表內容變成下拉式選單。

1、點取「登錄」工作表，再框選要設定地區下拉清單的儲存格範圍(錦子老師是框選A2:A20)，再點取「資料 > 資料驗證」圖示。

2、在【資料驗證】對話方塊，點取「儲存格內允許下方▼鈕」選擇「清單」項目。

3、點取「來源」欄位輸入「轉換!A1:E1」字串。

4、點取「確定」鈕。

5、框選要設定醫院下拉清單的儲存格範圍(錦子老師是框選B2:B20)，再點取「資料 > 資料驗證」圖示。

6、在【資料驗證】對話方塊，點取「儲存格內允許下方▼鈕」選擇「清單」項目。

7、點取「來源」欄位輸入「=Indirect(A2)」字串。

8、點取「確定」鈕。

這時即可以在A2:A20儲存格右邊出現下拉清單供選擇地區，B2:B20儲存格右邊出現下拉清單供選擇對應A2儲存格地區的醫院。

2233Excel日曆加上日誌(四)日期條件格式化

2230Excel日曆加上日誌(三)重要節日及詳細事項

2230Excel日曆加上日誌(二)活動日曆製作

2230Excel日曆加上日誌(一)日曆製作-DATE、MATCH、TEXT、COLUMN函數

807Excel中使用「監看視窗」功能實現監視公式的方法