文章来自对这个问题的回答
excel筛选后怎么自动生成表头标题?
其实实现的方法是反过来的,给定一个表头标题,然后根据表头标题动态筛选表格。
Dynamic Excel Filter Search Box (Extract data as you Type)trumpexcel.com
上面这个链接是一个不错的教程,完美的回答了这个需求。
大体上说一下实现方法,需要用到这么几个东西
- iferror公式,这个公式评估第一个参数是否返回错误值,如果返回错误值就显示第二个参数,可以让我们的结果看起来干净一些
- search公式,我们给定一个字符,excel在目标单元格里找这个字符是否存在
- small公式,得到一组单元格第k个非空值
- 不需要编程,不需要vba编程,重复,不需要vba编程
这个教程的实现方法整个还是挺精妙的
原始数据
首先肯定是把所有的客户选中,复制到一个新的工作表,然后数据>删除重复项
非重复的客户列表
教程原本需要添加一个combox box 的activeX控件,但是如果你只想用下拉菜单选一下的话,用excel自带的数据>数据验证也可以弄一个下拉框出来:
刚才的非重复列表放在了aux工作表a1~a6这个区域
在原来的数据右边加2列,现在长成这样:(因为题主你这个表已经有id列:序号
了,不需要额外弄一个id列出来)。
首先是helper1:=IF(ISNUMBER(SEARCH($N$1,[@客户名称])),ROW()-ROW(表1[[#标题],[helper1]]),"")
其中$N$1是做了数据验证的单元格的位置,从那里读一下我们需要什么客户,[@客户名称]表示从这一行客户名称这一列(比如现在是“金平”)去搜索N1这个单元格里的数据在不在(比如现在N1单元格选择的是“金马”)。
- 如果不在,用“金马”搜“金平”当然是报错,这个时候返回空值
- 如果在(‘金平’搜‘金平’),那么会返回一个数值,那接下来就返回这一行行数减去表头的行数
接下来是helper2:=IFERROR(SMALL([helper1],ROW()-ROW(表1[[#标题],[helper2]])),"")
small相当于是公式版的筛选,会返回一组数据里面前k个最小值。由于前面helper1这一列的返回空值不是数值,所以不会被拿来排序,自然就被排除了。为了方便观察,我给表格对金额做了升序排列
好,接下来可以用index这个公式去提取符合条件的数据了=IFERROR(INDEX(表1,表1[@helper2],COLUMN()-COLUMN(表4[[#标题],[序号]])+1),"")
整个一行都要用这个公式,不要使用自动填充