作者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/cn.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