為什麼這篇excel下拉式選單太多鄉民發文收入到精華區：因為在excel下拉式選單太多這個討論話題中，有許多相關的文章在討論，這篇最有參考價值！作者thesonofevil (四非亞心)看板Office標題[算表] 大量資料的多層下拉式選單時間...
軟體:EXCEL
各位好，想請問各位關於EXCEL的下拉式選單的問題
我們是賣隱形眼鏡的公司，以規格來分的話，至少要分
品牌、產品名稱、花色、度數
有上網找過多層下拉式選單的方式，但那似乎都僅限於較少數量的資料
而我的資料，品牌可能10幾個還好
但產品就幾百，每個產品又再劃分3.4個花色，每個花色又再劃分20個左右的度數
導致我不知道怎麼樣去列一個資料基底去給資料驗證抓
甚至我也不知道資料驗證是不是能抓這麼多層資料
想請問有沒有方法可以整理大量資料的下拉式選單？
另外最好能去除重複值，因為以度數為最終區分的話
商品大概破萬，品牌跟產品、花色都會有重複
如果有不清楚的話再告知，我再補充說明，感謝QQ
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 220.133.71.126 (臺灣)
※ 文章網址: https://www.ptt.cc/bbs/Office/M.1608539660.A.EB4.html
→ soyoso: 這要先看內文寫到的資料如何呈現，提供檔案會比較清楚 12/21 16:50
https://reurl.cc/6l1XyV如上
主要是給客戶下單用的
所以希望能讓客戶用類似工作表2那樣的方式一個一個篩選
※ 編輯: thesonofevil (220.133.71.126 臺灣), 12/21/2020 17:04:17
※ 編輯: thesonofevil (220.133.71.126 臺灣), 12/21/2020 17:21:13
→ soyoso: 工作表1先排序品牌(是否有要新增排序的欄位，再自行調整) 12/21 18:40
→ soyoso: 接著有要品牌的唯一值，這個移除重覆項，品牌下面有產品唯 12/21 18:40
→ soyoso: 一值，這一樣除除重覆項 12/21 18:40
→ soyoso: 第二列起產品 12/21 18:40
→ soyoso: 接著花色有二個資料，1.match 產品於工作表1a欄列號 2. 12/21 18:40
→ soyoso: countif 產品於工作表1a欄筆數 12/21 18:40
→ soyoso: offset來回傳不重覆花色的值，有幾筆不重覆花色用count 12/21 18:40
→ soyoso: 接著數量，match 產品&花色於工作表1輔助欄i欄的部分， 12/21 18:40
→ soyoso: countif 產品&花色，欄位一樣 12/21 18:40
→ soyoso: 就配合用於offset用的列偏移和高度(列數) 12/21 18:40
→ soyoso: 以這個方式來做，做出來會是 12/21 18:40
→ soyoso: 表格要如何設計和放置於其他儲存格或工作表就自行調整 12/21 18:40
→ soyoso: 12/21 18:40
感覺跳過太多步驟了 前面不懂 後面更是完全看不懂QQ...請問有檔案可以直接載來研究嗎...謝謝
※ 編輯: thesonofevil (220.133.71.126 臺灣), 12/21/2020 18:47:48
※ 編輯: thesonofevil (220.133.71.126 臺灣), 12/22/2020 09:58:53
※ 編輯: thesonofevil (220.133.71.126 臺灣), 12/22/2020 10:06:52
→ soyoso: 可查一下excel動態範圍，這方面12/21 18:40的回文 12/22 11:02
→ soyoso: 列偏移的定位就是函數match，高度列數就是函數countif(這 12/22 11:02
→ soyoso: 方面連結公式沒寫到，是寫在ai欄[match]和aj欄[countif]) 12/22 11:02
→ soyoso: 如花色以"產品"(b欄)當搜尋或條件，在哪個範圍或欄位：工 12/22 11:02
→ soyoso: 作表1的a欄 12/22 11:02
→ soyoso: 度數(回文寫數量，更正一下)以"產品&花色"當搜尋或條件， 12/22 11:02
→ soyoso: 在哪個範圍或欄位：工作表1輔助欄i欄 12/22 11:02
→ soyoso: 有無法回傳正確資料的地方可再提出 12/22 11:02
請問第一張圖到第二張圖是如何轉變的難道是一個一個複製貼上再用移除重複值的功能嗎?
因為我看你品牌似乎根據產品個數在同一格重複輸入
所以應該是有什麼方法可以快速得出第二張圖?
※ 編輯: thesonofevil (220.133.71.126 臺灣), 12/22/2020 11:23:20
真的麻煩您了 看到您的成品真的完全符合我的需求
但我真的太多看不懂了......
※ 編輯: thesonofevil (220.133.71.126 臺灣), 12/22/2020 11:29:21
→ soyoso: 品牌移除重複值，轉置於第一列(24筆) 12/22 11:48
→ soyoso: 1.一個一個複製貼上再用移除重複值也是可以，因為品牌筆數 12/22 11:48
→ soyoso: 不多，篩選，複製貼上再移除重複值 12/22 11:48
→ soyoso: 2.公式，index、offset或indirect 定位match，列偏移row， 12/22 11:48
→ soyoso: 限縮方面加上判斷countif的筆數(寫法類似儲存格ak2)，選擇 12/22 11:48
→ soyoso: 性貼上值，再移除重複值(可迴圈協助range. 12/22 11:48
→ soyoso: removeduplicates) 12/22 11:48
→ soyoso: 3.陣列公式small(if配合match或countif也可以，如果產品有 12/22 11:48
→ soyoso: 常更新的話 12/22 11:48
呃...抱歉 真的看不懂後面您所說的一堆公式代表什麼我本身只會一些常用的EXCEL公式，層數也頂多兩三層，而且還需要上網查詢
抱歉真的看不懂你後面的.... 請問方便提供檔案成品，我再研究嗎?
雖然這樣真的很伸手牌....但還是想麻煩您了.....
※ 編輯: thesonofevil (220.133.71.126 臺灣), 12/22/2020 11:53:30
→ soyoso: 不太了解這些函數如何用在公式也沒有關係，就用1的方式， 12/22 11:56
→ soyoso: 就先要有這個表格出來 12/22 11:56
品牌第一列 產品第二列其實是我最初碰到的困難 我還有想過用樞紐去複製貼上但最大的問題還是品牌跟產品無法一對一得對上
品牌只有2X個 產品有很多個 就算同樣用複製貼上也不會剛好品牌下面就是對應產品
所以煩惱很久 不知道該怎麼匹配....
不知道是否是我哪裡理解錯誤....感覺您講的好像很簡單 但我就是不知道怎弄QQ
還是你是說品牌下的產品其實是用公式弄得?
※ 編輯: thesonofevil (220.133.71.126 臺灣), 12/22/2020 12:02:24
※ 編輯: thesonofevil (220.133.71.126 臺灣), 12/22/2020 12:03:14
→ soyoso: 品牌跟產品無法一對一得對上的方面，篩選品牌，那篩選後的 12/22 12:05
→ soyoso: 產品就應該是對應該品牌吧，這就可以對上了 12/22 12:05
→ soyoso: 因為我表格沒有更新，所以沒有用12/22 11:48回文的陣列公 12/22 12:09
→ soyoso: 式，而是用2達成的，選擇性貼上值後，儲存格內也就沒有公 12/22 12:09
→ soyoso: 式了 12/22 12:09
阿阿阿阿阿 剛剛終於搞懂了 原來我連最基本的範圍移除重複都沒理解....想說個別移除不就失去一對一的性質了...
※ 編輯: thesonofevil (220.133.71.126 臺灣), 12/22/2020 12:18:19
→ soyoso: 樞紐分析表也是可行的方式，我回文是以第一列，樞紐分析表 12/22 12:14
→ soyoso: 則是第一欄，重覆項目標籤(勾選) 12/22 12:14
→ soyoso: 補充一下，用樞紐分析表的話，1.品牌的唯一值，2.品牌和貨 12/22 12:55
→ soyoso: 品名稱的唯一值，3.貨品名稱(品牌之間有相同的貨品名稱的 12/22 12:55
→ soyoso: 話再加上品牌)和簡稱的唯一值，4.貨品名稱、簡稱和度數， 12/22 12:55
→ soyoso: 動態範圍，用於產品、花色和度數，一樣定位用match和計數 12/22 12:55
→ soyoso: 用countif(3或4有二個條件時，可用輔助欄品牌&貨品名稱或 12/22 12:55
→ soyoso: 貨品名稱&簡稱，如不用match以條件1*條件2的方式，countif 12/22 12:55
→ soyoso: 則改以countifs)；度數排序規則上如不同於工作表1的話，自 12/22 12:55
→ soyoso: 訂清單 12/22 12:55
最後還是用手動自己貼了請問一下上圖只有看到AK2的公式，反黃部份是跟AK2一樣直接拉滿嗎?
AH、AI、AJ、BD、BE、BF又是什麼公式....還
有全弄完後的下拉式選單一樣用驗證...?
※ 編輯: thesonofevil (220.133.71.126 臺灣), 12/22/2020 15:23:56
→ soyoso: 黃色區域是以儲存格ak2拖曳的；一樣用資料驗證 12/22 15:39
那其他欄位呢....悟性不足.....QQ※ 編輯: thesonofevil (220.133.71.126 臺灣), 12/22/2020 15:48:32
→ soyoso: ai和aj 回文12/22 11:02有寫，be和bf一樣，ah計數count， 12/22 15:54
→ soyoso: 花色的筆數，用工作表1的欄位 12/22 15:54
→ soyoso: 上述寫的工作表1欄位是指輔助欄；不以工作表1欄位而要判斷 12/22 16:04
→ soyoso: ak:bc的話，sumproduct加總非空字串，邏輯值要運算或value 12/22 16:04
→ soyoso: 轉換 12/22 16:04
還是聽不懂.... 不好意思 因為您很多說明都是建立在我懂那些公式的前提下但我其實不太熟那些公式 您的說明我很難理解....
我可能還是只能一個一個列出來用下拉式了....
※ 編輯: thesonofevil (220.133.71.126 臺灣), 12/22/2020 16:10:35
→ soyoso: 是啊 12/22 16:14
→ soyoso: 所以原po對offset這個函數都不懂，所提供match或是countif 12/22 16:15
→ soyoso: 所回傳的資料就是用於offset內的引數 12/22 16:16
→ soyoso: offset的height帶的就是儲存格d2 12/22 16:18
OFFEST是真的不熟... MATCH比較少用 COUNTIF比較熟一點但重點是整個混在一起我就腦筋打結了 抱歉......
※ 編輯: thesonofevil (220.133.71.126 臺灣), 12/22/2020 16:44:53
→ soyoso: 圍有沒有就已經給原po了，有的，12/21 18:40回文 https:// 12/22 17:21
→ soyoso: i.imgur.com/iKKvrl1.jpeg 12/22 17:21
→ soyoso: 12/22 11:02回文，在ai欄[match]和aj欄[countif]以"產品"( 12/22 17:21
→ soyoso: b欄)當搜尋或條件，在哪個範圍或欄位：工作表1的a欄 12/22 17:21
→ soyoso: 度數看12/22 11:02的回文以"產品&花色"當搜尋或條件，在哪 12/22 17:21
→ soyoso: 個範圍或欄位：工作表1輔助欄i欄 12/22 17:21
感謝 按照您的公式 重新用了後可以了 非常感謝~※ 編輯: thesonofevil (220.133.71.126 臺灣), 12/24/2020 18:12:52