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