贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

網友問到:在 Excel 的工作表中(如下圖),如果有二個下拉式選單,希望第二選單不要重現第一個選單已選取的項目,該如何處理?

例如,在選單A中已選取「五月」,則選單B中沒有列出「五月」供選取。

Excel-第二個選單不出現第一個選單已被選的內容(OFFSET,ROW)

【公式設計與解析】

要製作選單效果可以透過「資料驗證」功能,例如選單A設定:

儲存格內允許:清單

來源:=$D$2:$D$13

image

如果要做到選單B不能包含選單A中已被選取的項目,則必須建立另一個選單的內容。

選取儲存格D1:D13,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:選單A。

儲存格E2:

{=OFFSET($D$1,SMALL(IF(選單A<>$A$2,ROW(選單A),""),ROW(1:1))-1,0)}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動輸入「{}」。

(1) IF(選單A<>$A$2,ROW(選單A),"")

在陣列公式中判斷儲存格A2內容是否和選單A陣列相同,若是則傳回儲存格列號,否則傳回空字串。

(2) SMALL(第(1)式,ROW(1:1))

利用 SMALL 函數由小至大依序取出列號。

(3) OFFSET($D$1,第(2)式,ROW(1:1))-1,0)

將第(2)式傳回的列號代入 OFFSET 函數取得對應的儲存格內容。

複製儲存格E2,貼至儲存格E2:E12。

在儲存格B2中以儲存格E2:E12,建立下拉式清單。

 

 

文章標籤
創作者介紹
創作者 vincent 的頭像
vincent

學不完.教不停.用不盡

vincent 發表在 痞客邦 留言(2) 人氣()


留言列表 (2)

發表留言
  • 訪客
  • 你好
    這一段"選取儲存格D2:D"要怎麼操作? 有後續?
  • 已修改,請再行檢視。

    vincent 於 2018/12/12 23:17 回覆

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼