难倒90%的人,根据筛选内容LOOKUP对应值,居然如此简单!

学员的问题,有无公式可以实现,将筛选状态下的B列的名称,连接合并到B1个单元格,并随着筛选状态的变化而变化?

对筛选的内容求和、计数、添加序号都是用SUBTOTAL函数,这里也照样用这个函数。

先跟卢子复习一下,SUBTOTAL函数在筛选情况下获得连续序号的方法。103是计数,最后面*1是为了防止最后一行被认为总计从而导致筛选出错。

=SUBTOTAL(103,A$3:A3)*1

各数字对应表,用得最多的就是103计数,109求和。

筛选以后,序号就都是连续的。

根据这个特点,现在将辅助列的公式略作改动,原来是区域,现在变成单元格。现在可见单元格都是1。

=SUBTOTAL(103,A3)*1

最终公式就出来了,这是数组公式,需要按Ctrl+Shift+Enter三键结束。

=TEXTJOIN(",",1,IF(C3:C12=1,B3:B12,""))

IF函数部分的作用,让C列为1的返回B列的对应值,否则显示空白。TEXTJOIN函数就将结果用分隔符号合并起来。

有了这个套路,现在要根据筛选内容查找对应值,就不是问题了。只需增加一个条件,判断类别是否一样就行。

=TEXTJOIN(",",1,IF(($C$3:$C$12=1)*($A$3:$A$12=A22),$B$3:$B$12,""))

语法总结,需要按Ctrl+Shift+Enter三键结束。

=TEXTJOIN("分隔符号",1,IF((辅助列=1)*(查找区域=查找值),返回区域,""))

如果你的电脑没有这个函数,没关系,送你一个。详见下面文章:

遭了,你的Excel找不到这个TEXTJOIN函数!

平常我比较喜欢用辅助列,这样会使问题变得更加简单。

推荐:筛选情况下条件计数和条件求和,你会吗?

上篇:无意间想到的多条件求和公式,实在太好用了。。。

如果是你,遇到这种筛选状态查找对应值,你会怎么做?

作者:卢子,清华畅销书作者,《Excel效率手册 早做完,不加班》系列丛书创始人,个人公众号:Excel不加班(ID:Excelbujiaban)

(0)

相关推荐

  • Excel中那些护发公式!(上篇)

    废话就不唠了,直接上案例开搞! 案例01 | 提取唯一值(去重) 这个需求很常见,我们讲过的方法也很多,先来看看传统的函数处理方法吧! >> 传统方法: ▼大部分同学已经放弃 =IFERR ...

  • SUBTOTAL函数,这个函数不简单?

    行云里讲堂(ID:xingyunli2022) 践行终身学习,专注个人提升. 整理编辑:枏北 SUBTOTAL函数,常用在分类汇总中.创建分类汇总后,可以通过编辑SUBTOTAL函数对该列表进行修改. ...

  • EXCEL好朋友最新发布的十个简单好用技巧,必须收藏!

    各位朋友好,上一节讲到日期的几种常用计算方法.那么在这节,我们来说说EXCEL的常用技巧,在平日工作中说不定你会遇到. 1. 在指定位置冻结行和列 一张表格数据太多,行和列都很多的情况下,滚动数据的时 ...

  • Subtotal函数的使用方法

    在Excel中subtotal函数既能求和,求平均值,还能计数,求最值等多种功能.可以说是非常实用的一个函数. 一.subtotal函数的使用 作用:返回一个数据列表或数据库的分类汇总. 语法:=Su ...

  • 太难了!这个工时计算问题难倒90%的人

    你好,我是刘卓.欢迎来到我的公号,excel函数解析.今天来分享一个计算实际工时的方法.主要用到前两篇文章的知识,一个是text的分段求和,一个是计算在某段时间内的时长. 1.计算实际工时 下图左表是 ...

  • 能让人健康长寿的五件事居然这么简单,可第一个很多人就做不到!

    你是否有这样的感觉?二十岁的你加班加点熬夜赶工,那是小事一桩:三十岁的你日以继夜忙前忙后,不觉得有任何问题:四十岁的你忙忙碌碌几周下来,就会有些许疲惫:五十岁的你连轴转一周工作完,可能就得休息休息. ...

  • Excel筛选后的难题,难倒了很多人....

    你好,我是世杰老师,很高兴在这里和你遇见. 前几天有同学问世杰老师,带有序号的表格筛选之后就不会自动排序了,可不可以筛选之后序号会根据筛选的内容自动排序,这当然是可以做到的,今天世杰老师给同学们分享关 ...

  • 10个动画轻松掌握90%的人都不会用的Excel高级筛选功能

    跟李锐学Excel, 高效工作,快乐生活. 10个动画轻松掌握90%的人都不会用的Excel高级筛选功能 Excel中的自动筛选很多人都会用,但经常遇到多条件或复杂条件筛选时卡壳的窘境,其实强大的Ex ...

  • 美国90万人死于新冠?美研究所称全球疫情死亡人数是官方数据两倍

    东方网·纵相新闻记者 谢斯河 据俄媒RT5月6日报道,美国华盛顿大学健康指标与评估研究所(IHME)发表声明称,全球新冠疫情的真正死亡人数远高于官方数据,更接近于他们预估的死亡人数.据悉,IHME是最 ...

  • 90%的人猝死都跟肩颈有关?

    中医之声 医学资讯 公众号 90%的人猝死,都跟这个部位堵住有关! 这个地方就是肩颈.为什么肩颈堵了90%的人猝死呢?看完下面的介绍,你就明白了. 肩颈是大脑的总开关 也是毒素最容易堆积的地方 ▼ 从 ...

  • 浙江私藏的安逸小城!不是杭州不是宁波,风光旖旎,却被90%的人遗忘!

    把浙江地理版图对折,在正中心的位置上有这样一个地方,被称为"浙江之心". 它隐蔽于森林间,是踏春赏花的好去处,溪水缓缓,风吹山林绿意起伏,让你心灵受到震撼. 这里远离城市的喧闹嘈杂 ...

  • 早饭怎么吃,决定你的寿命和生什么病!但90%的人都吃错了!

    心梗和脑梗,是人尽皆知的,威胁着我们健康的两种疾病.但是,很多人不知道,这两种疾病的发生,竟和早饭有关系. 这顿饭就是早饭! 不吃早餐,后果有多严重? 01 心梗.脑梗概率成倍增加! 不是在吓你,这两 ...

  • 似是而非的英语句子,90%的人都理解错了,你会吗?

    英文中有一种地道的表达方法--"似是而非".我们若通过一般语法句法分析则无法解码出句子的真正含义,需要了解一些惯用的句式表达,并且结合上下文,才能理解其真正意图. 1This is ...