PTT數位生活區 / Office

[算表] 大量資料的多層下拉式選單

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