ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 效率神器,一键搞定繁琐工作
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
让更多数据处理,一键完成 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
楼主: hjj0451

[Excel 函数与公式] [开_129]字母重复区域排序取数

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2012-1-13 12:33 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册

请验证,78字符

=CHAR(MID(SMALL((CODE(A2:D4)&{1,2,3,4}&CODE(A1:D1))+10^11,ROW(1:12)),{8,2},5))
多区域数组

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2012-1-13 13:15 | 显示全部楼层
本帖最后由 tyxh0916 于 2012-1-13 13:17 编辑

F2
=INDIRECT(TEXT(MOD(SMALL(--(CODE($A$2:$D$4)&{2;3;4}^(COLUMN()-6)&{1,2,3,4}),ROW(A1)),100),"r0c0"),)
右拉下拉填充

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2012-1-13 16:00 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 xgg2001 于 2012-1-13 16:01 编辑

无绝对引用,内存数组86个字符,不知是否算投机?加上$后98个字符
  1. =CHAR(MID(SMALL(--((1000+CODE(A2:D4)+COLUMN(A:D)%)&CODE(A1:D1)&1),ROW(1:12)),{8,2},5))
复制代码

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2012-1-13 20:32 | 显示全部楼层
字母重复区域排序取数-hjj0451.rar (3.46 KB, 下载次数: 9)

TA的精华主题

TA的得分主题

发表于 2012-1-14 08:13 | 显示全部楼层
本帖最后由 swyrlbq 于 2012-1-16 09:24 编辑

F2:G13区域数组,99字符

F2:G13=CHAR(MID(TEXT(SMALL(--(CODE($A$2:$D$4)&{1,2,3,4}&CODE($A$1:$D$1)),ROW(1:12)),REPT(0,12)),{8,1},6))

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2012-1-14 12:08 | 显示全部楼层
完成了,不容易啊不容易,数组函数组合,挺烦人的。

字母重复区域排序取数-hjj0451.zip

5.43 KB, 下载次数: 6

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2012-1-15 17:01 | 显示全部楼层
  1. =MID("甲乙丙丁abcdefghijklmnopqrstuvwxyz",MID(SMALL(CODE(A2:D4)+8+FIND(A1:D1,"甲乙丙丁")%,ROW()-1),{5,2},2),1)
复制代码
区域数组,102{:soso_e130:}

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2012-1-16 12:36 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
楼主辛苦了

=IF({1,0},INDEX(1:1,,RIGHT(SMALL(CODE(A2:D4)+{1,2,3,4}%,ROW()-1))),CHAR(SMALL(CODE(A2:D4),ROW()-1)))

评分

1

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2012-1-16 23:01 | 显示全部楼层
本帖最后由 hjj0451 于 2012-1-16 23:25 编辑

考察点:
  1.数据信息先糅合再提取再分离的技巧与方法,思路1、思路2都采用;        
  2.字符排序与字符代码code的关系(请谨慎使用);                                
  3.公式精简的一些小技巧。
               
基本原理:
   示意图.jpg   
  见上图,同平时常用的数字与其行列号以不同的数量级杂揉后再排序取出的原理一样,思路1揉在一起的是:"a-z字符代码"+"甲-丁位号"+"甲-丁字符代码",为缩短字符而没有采用"X*10^6+Y*10^5+Z"或"X+Y/10+Z%%%"这种平时常用的乘数量级技巧,而改用&直接连接。
  几种思路的最高位和次高位的糅合思想相同,这是排序的第一关键字和第二关键字,不同的是最低位,思路1是直接揉进甲-丁的字符代码(用char-mid直接取出),思路2是揉进行列号组合(用indirect-text间接取出)

说明:        
  a-z的顺序与其对应的CODE值大小顺序是一致的,它是排序的第一权重因素,因此可以直接把其CODE值作为最高的几位,用SMALL来取数时,取得的CODE值大小顺序也是按a-z的排序顺序。        
  甲-丁的顺序与其对应的CODE值大小顺序是不一致的,它是排序的第二权重因素,不可直接把其CODE值作为次高的几位。应把其排在原始数据中的位号作为次高位,再把其CODE值放在位号后面,在取其CODE值时,当有重复的a-z时,上图的绿色区域值相同,用SMALL取数时先取到的是位号较小的数字。        
  因为甲-丁的字符代码是5位数, a-z的字符代码是2-3位数,因此在a-z的字符代码位前最少要补三个零(加上10^11),才可以用MID统一取5位得到a-z和甲-丁的字符代码。如果加上10^9,只能mid(数字,{6,2},{5,3}),效率降低,hopeson2010的+10^3&…的方法与此类似。                        
  位号的取得方法一是FIND(A1:D1,"甲乙丙丁")法,二是MOD(CODE(A1:D1),29)法,后一种很独特,有创新。
                                                
心得体会:                                                
  1.补零的补位取数技巧:加上合适的大数10^n,n的大小取决于处理的数据的最小位数        
  2.信息杂糅的技巧,①乘以不同的数量级;②直接&连接                                
  3.减负与取负要灵活使用,不必要的减负不如取负有效。
                        
题目扩展:        
  方案1:A1:D1为十天干之中的任意一个与十二地支中的任意一个的组合(无重,排序按逐字排序的原则,如甲在甲子前,乙卯在丙午前),A2:D4为"a-z、aa-az、ba-bz、…、za-zz"中的任意一个(有重),排序按原体要求,字符排序逐个比较并排序(如a在ba前、aa在ac前,cd在da前)                        
  方案2:在原题的基础上A2:D4里搀杂进逻辑值与数字(数值型),数字小于字母,字母小于逻辑值,逻辑值false<true。


字母重复区域排序取数(附答案与总结)-hjj0451.rar (17.98 KB, 下载次数: 59)                                              


点评

点评比出题更不容易啊。  发表于 2012-1-17 11:02

评分

3

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2012-1-16 23:02 | 显示全部楼层
本帖最后由 hjj0451 于 2012-1-16 22:43 编辑

点评:
5楼:考虑A1:D1顺序,并可简化;
9-1216-1721-22楼:大致相同,零补位MID取数不同;
14楼:把A2:D4的字符代码变成3位,补位取数方法稍有不同。
17楼:MOD(CODE(A1:D1),29)取得位号的方法有创新,补位取数方法稍有不同
20楼:分别取组合的1-2位,左列加上行号1,右列不变。美中不足的是没有考虑A1:D1乱序,并可简化。
21-22楼:思路很优秀,与9-12楼一样,但没有考虑A1:D1乱序,以原始的{1,2,3,4}考虑,ROW(1:12)可简化。
23楼:没有考虑A1:D1乱序,可继续精简字符。
24楼:乙的字符代码为53970,在算术运算的过程中会丢掉末尾的零,因此采用了&1,在考虑A1:D1顺序的基础上仍可简化。
25-26楼:
28楼:加8a-z的取数位置从5开始,其余的穷举法的思路充分利用题意。
29楼:羽希发给我的答案:
=T(INDIRECT(TEXT(RIGHT(SMALL(CODE(A2:D4)+({2;3;4}&{1,2,3,4})%,ROW()-1),{1,2}),"r"&{1,0}&"c0"),))除了没考虑A1:D1乱序外,思路很不错,类似于20楼,不同的是在TEXT2参数里做手脚。
谢谢你们的答案,如果你没有答对,我说声抱歉,也许与你的理解和题意的表达都有关系,不同的人理解的深度不同。




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

本版积分规则

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

GMT+8, 2024-12-23 14:10 , Processed in 0.044011 second(s), 11 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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