ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 12060|回复: 45

[Excel 函数与公式] [开_153]显示筛选后的唯一属性(开帖待总结)

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2017-11-17 08:41 | 显示全部楼层 |阅读模式
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 delete_007 于 2017-12-20 08:47 编辑

                            显示筛选后的唯一属性


       说明:
               筛选A3:D26单元格区域,当该列唯有一种属性时,在该列的最上单元格显示该属性,否则为空。
       示例:
               示例 一: 当A2 筛选3 时,                B 列唯有 “小星星”, B1显示 “小星星”;
                                                                     C列唯有“女”          C1显示“女”;
                                                                     D列唯有“学习”       D1显示“学习”。

               示例 二: 当A2 筛选1、8 时,        B列唯有“丢丢表哥”,B1显示“丢丢表哥”;
                                                                    C列唯有“男”,         C1显示“男”        ;
                                                                    D列有两种爱好,不是唯一,D1显示空白。

               示例三: 当A2 筛选4、7、11 时,    B列有两会员,不是唯一,B1显示空白;
                                                                     C列只有一种“男”,   C1显示“男”        ;
                                                                     D列有三种爱好,不是唯一,D1显示空白。

        要求:
           纯函数题,谢绝自定义名称、辅助列。
                可使用B1单元格公式向右填充或B1:D1区域数组公式完成。
                除 365 以外 的版本均可。
                答题请使用CODE模式贴出公式,不必上传附件。
        评分:                                                               
               公式长度(含等号)不超过 110 字符,评技术分1分。
               其余正确答案给予适当财富奖励(不超过50财富)。

        竞赛日期:      
             2017年11月17日 至 2017年12月20日                                                         
                                                     
筛选后显示唯一属性.rar (9.58 KB, 下载次数: 234)

题目内容

题目内容

示例

示例







评分

15

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2017-12-20 09:32 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 象山海鲜 于 2017-12-21 10:24 编辑

评分公示
[开_153]显示筛选后的唯一属性-评分.png

各位老师、坛友,如果对评分有疑问的可以提出来,谢谢!

补充其他解法

[开_153]显示筛选后的唯一属性-补充解法.png


个人能力的限制,总结尚有很多欠缺浅薄有待商议之处,敬请各位老师坛友斧正!
谢谢007版帮助,谢谢各位老师、坛友参与!

评分和总结:
筛选后显示唯一属性(总结 评分).rar (39.18 KB, 下载次数: 117)





评分

14

查看全部评分

TA的精华主题

TA的得分主题

发表于 2017-11-17 09:06 | 显示全部楼层
本帖最后由 delete_007 于 2017-11-20 08:44 编辑

先占个位。
B1单元格数组公式(102字符):
  1. =IFNA(OFFSET(B2,MODE(QUARTILE(IF(SUBTOTAL(3,OFFSET(B2,$A3:$A26,)),MATCH(B3:B26,B3:B26,)),{0,4})),),"")
复制代码

B1数组公式(94字符):
  1. =OFFSET(B2,MODE(IF(SUBTOTAL(3,OFFSET(B2,$A3:$A26,)),MATCH(B3:B26,B3:B26,)%^{1,1,0,0}))/1%,)&""
复制代码

评分

11

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2017-11-17 11:42 | 显示全部楼层
预设答案:
  1. =INDEX(B:B,MODE(IF(SUBTOTAL(3,OFFSET(B2,$A3:$A26,)),MATCH(B3:B26,B3:B26,)*{1,1,0,0}+{2,2,99,99})))&""
复制代码

评分

10

查看全部评分

TA的精华主题

TA的得分主题

发表于 2017-11-17 11:48 | 显示全部楼层
本帖最后由 LSL1128xgw 于 2017-11-27 08:39 编辑

=OFFSET(B2,MODE(QUARTILE(IF(SUBTOTAL(3,OFFSET(B2,$A3:$A26,)),MATCH(B3:B26,B3:B26,)),{0,4}),99,99),)&""

筛选后显示唯一属性.zip

10.9 KB, 下载次数: 14

评分

13

查看全部评分

TA的精华主题

TA的得分主题

发表于 2017-11-17 13:47 | 显示全部楼层
本帖最后由 梦幻小丑 于 2017-11-20 08:49 编辑
  1. =OFFSET(B2,MODE(IF(SUBTOTAL(3,OFFSET(A2,$A3:$A26,)),MATCH(B3:B26,B3:B26,)*{0,1}+{99,0}),$A:A),)&""
复制代码

评分

10

查看全部评分

TA的精华主题

TA的得分主题

发表于 2017-11-17 22:29 | 显示全部楼层
本帖最后由 sayhi95 于 2017-12-7 23:22 编辑
  1. =IF(SUM(COUNTIF(OFFSET(B2,IF(SUBTOTAL(3,OFFSET(B2,$A3:$A26,)),$A3:$A26),),INDEX(B3:B26,MAX(IF(SUBTOTAL(3,OFFSET(B2,$A3:$A26,)),$A3:$A26)))))=SUBTOTAL(3,$A3:$A26),INDEX(B3:B26,MAX(IF(SUBTOTAL(3,OFFSET(B2,$A3:$A26,)),$A3:$A26))),"")
复制代码
污污污,小火车开动了,汗~等着学大神们简洁的思路。
  1. =IF(COUNT(0/FREQUENCY(ROW(1:25),IFNA(MATCH(IF(SUBTOTAL(3,OFFSET(B2,$A3:$A26,)),B3:B26),B3:B26,),)))=2,LOOKUP(,0/SUBTOTAL(3,OFFSET(B2,$A3:$A26,)),B3:B26),"")
复制代码
终于。。。
  1. =IFNA(INDEX(B3:B26,MODE(AGGREGATE({14,15},6,MATCH(B3:B26,B3:B26,)/SUBTOTAL(3,OFFSET(B2,$A3:$A26,)),1))),"")
复制代码

OFFSET还能节约两个^_^
  1. =IFNA(OFFSET(B2,MODE(AGGREGATE({14,15},6,MATCH(B3:B26,B3:B26,)/SUBTOTAL(3,OFFSET(B2,$A3:$A26,)),1)),),"")
复制代码

评分

12

查看全部评分

TA的精华主题

TA的得分主题

发表于 2017-11-18 15:17 | 显示全部楼层
本帖最后由 titi012 于 2017-11-27 14:48 编辑
  1. =IF(VARP(IF(SUBTOTAL(3,OFFSET(B2,ROW(1:24),)),MATCH(B3:B26,B3:B26,))),"",OFFSET(B2,SUBTOTAL(4,$A3:$A26),))
复制代码

评分

7

查看全部评分

TA的精华主题

TA的得分主题

发表于 2017-11-18 16:56 | 显示全部楼层
=IF(COUNT(1/(MATCH(IF(SUBTOTAL(3,OFFSET(B2,ROW(1:24),)),B3:B26,1),IF(SUBTOTAL(3,OFFSET(B2,ROW(1:24),)),B3:B26,0),)=$A3:$A26))=1,INDEX(B3:B26,MATCH(1,SUBTOTAL(3,OFFSET(B2,ROW(1:24),)),)),"")

评分

7

查看全部评分

TA的精华主题

TA的得分主题

发表于 2017-11-22 02:25 | 显示全部楼层
本帖最后由 龙城飞将III 于 2017-11-26 15:40 编辑

方差判断唯一属性,应该算是首创吧?


大爷的,发现一个错误,修正下:

  1. =IF(STDEV.P(IF(SUBTOTAL(3,OFFSET(B2,$A3:$A26,)),MATCH(B3:B26,B3:B26,))),"",OFFSET(B2,AGGREGATE(5,5,$A:$A),))
复制代码





到极限了啊,啊,啊,啊,啊,

  1. =IF(STDEV(IF(SUBTOTAL(3,OFFSET(B2,$A3:$A26,)),MATCH(B3:B26,B3:B26,))),"",OFFSET(B2,AGGREGATE(5,5,$A3:$A26),))
复制代码



我还能坚持

  1. =IF(STDEV(IFERROR(MATCH(B3:B26,B3:B26,)/SUBTOTAL(3,OFFSET(B2,$A3:$A26,)),""))=0,LOOKUP("座",IF(SUBTOTAL(3,OFFSET(B2,$A3:$A26,)),B3:B26)),"")
复制代码


继续抢救
  1. =IF(COUNT(0/(MATCH(B3:B26,IF(SUBTOTAL(3,OFFSET(B2,$A3:$A26,)),B3:B26),)=$A3:$A26))=1,VLOOKUP("*",IF(SUBTOTAL(3,OFFSET(B2,$A3:$A26,)),B3:B26),1,),"")
复制代码


我觉得我还能抢救一下
  1. =IF(COUNT(0/(MATCH(B3:B26,IF(SUBTOTAL(3,OFFSET(B2,$A3:$A26,)),B3:B26),)=$A3:$A26))=1,OFFSET(B2,MAX(IF(SUBTOTAL(3,OFFSET(B2,$A3:$A26,)),$A3:$A26)),),"")
复制代码



搞成这样了
  1. =IF(COUNT(0/FREQUENCY(IF(SUBTOTAL(3,OFFSET(B2,$A3:$A26,)),MATCH(B3:B26,B3:B26,)),$A3:$A26))=1,OFFSET(B2,MAX(IF(SUBTOTAL(3,OFFSET(B2,$A3:$A26,)),$A3:$A26)),),"")
复制代码


先这样,不一定能改的动了,我师傅居然还在玩折纸。
  1. =IF(COUNT(0/FREQUENCY(IFERROR(MATCH(IF(SUBTOTAL(3,OFFSET(B2,$A3:$A26,)),B3:B26),B3:B26,),""),ROW(1:24)))=1,OFFSET(B2,MAX(IF(SUBTOTAL(3,OFFSET(B2,$A3:$A26,)),$A3:$A26)),),"")
复制代码

评分

11

查看全部评分

TA的精华主题

TA的得分主题

发表于 2017-11-24 21:14 | 显示全部楼层
本帖最后由 太阳之子 于 2017-12-1 10:47 编辑
  1. =INDEX(B:B,SUBTOTAL(5,$A:$A)+2+VAR(IFERROR({99,99}*MATCH(INDEX(B:B,AGGREGATE(14,1,$A:$A,IF(1,$A:$A+0))+2),B3:B26,),"")))&""
  2. =OFFSET(B2,SUBTOTAL(4,$A:A)+VAR(IF(SUBTOTAL(3,OFFSET(A2,$A3:$A26,)),MATCH(B3:B26,B2:B26,)*{9,9})),)&""
复制代码
公式一122字符,公式二101字符


评分

12

查看全部评分

您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-29 16:08 , Processed in 0.073809 second(s), 12 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表