ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

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

[Excel 函数与公式] [第134期]数据整理(已总结)

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2022-5-14 17:03 | 显示全部楼层 |阅读模式
本帖最后由 满坛皆为吾师 于 2022-6-8 14:54 编辑

一、题目说明:
      见问题描述及详细说明,若有不解,请参照结果示例。
二、答题要求:
      1、纯函数题目,除录入公式外,不允许其他任何操作。        
      2、禁止使用常量数组或CHOOSE构成的类似常量数组。        
      3、每题仅可用一个公式解答,向下填充或用区域数组。        
      4、仅接受 Microsoft Office 2013 及以下版本答案,代码模式提交。
三、评分规则:
      1、第一题,小于130字符(含等号)得技术2分,小于150得技术分1分。        
      2、第二题,小于130字符(含等号)得技术1分,结果文本或数值均可。        
      3、两题任选,以得分最高的题为准。        
四、竞赛日期:
      2022-5-14至2022-6-5        




单选投票, 共有 46 人参与投票

距结束还有: 9207 天17 小时20 分钟

您所在的用户组没有投票权限

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?免费注册

x

点评

示例,转换B3为时间实为B6  发表于 2022-5-14 19:34

评分

21

查看全部评分

TA的精华主题

TA的得分主题

发表于 2022-5-14 19:28 来自手机 | 显示全部楼层
本帖最后由 dim麻辣君 于 2022-6-5 13:00 编辑

题目一算了先弄个无脑的
  1. =SUBSTITUTE(SUBSTITUTE(TEXT(,SUBSTITUTE(TEXT(,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A6,"+",",+"),"-",",-"),"*",",A"),"/",",C")),",","*")),"A","*"),"C","/")
复制代码


题目二
  1. =SUM(MOD(IMREAL(IMDIV(TEXT(2-COLUMN(A:C),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B6:B8,"*","+"),"/","+"),"+","i;")&"i"),"-1-i"))*2,1))
复制代码


评分

17

查看全部评分

TA的精华主题

TA的得分主题

发表于 2022-5-14 23:00 | 显示全部楼层
本帖最后由 静听溪流 于 2022-5-21 16:29 编辑

第一题:
  1. =TEXT(,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A6,"/","\/\"),"+","\+\"),"-","\-\"),"*","\*\"),"\\","*"))
复制代码
=TEXT(,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A6,"/","\/\"),"+","\+\"),"-","\-\"),"*","\*\"),"\\","*"))

第二题:
  1. =SUM(MOD(-2*IMREAL(IMDIV(MID(TRIM(MID(SUBSTITUTE(".."&ENCODEURL(B6:B8),"%",REPT(" ",99)),{1,2,3}*99-98,99)),3,99)&"i","1+i")),1))
复制代码
  1. =SUM(MOD(-2*IMREAL(IMDIV(MID(TEXT(COLUMN(A:C)-2,""".."&SUBSTITUTE(ENCODEURL(B6:B8),"%","i"";""")&"i"""),3,99),"1+i")),1))
复制代码

评分

15

查看全部评分

TA的精华主题

TA的得分主题

发表于 2022-5-16 10:48 | 显示全部楼层
自定义函数解决
=CONCAT(正则提取(A6,"[-+/.*][.]\d+[\.\d]*|\d+[\.\d]*|[-+/.*]\d+[\.\d]*"))
Function 正则提取(rng, 表达式)
    Dim brr(), arr
    Set regx = CreateObject("vbscript.regexp")
    If TypeName(rng) = "Range" Then
        arr = rng.Value
        If Not IsArray(arr) Then
            ReDim arr(1 To 1, 1 To 1)
            arr(1, 1) = rng.Value
        End If
        k = 0
        With regx
            .Global = True
            .Pattern = 表达式
            For i = 1 To UBound(arr)
                For j = 1 To UBound(arr, 2)
                    x = arr(i, j)
                    Set matches = .Execute(x)
                    For Each aa In matches
                        js = aa.SubMatches.Count
                        If js Then
                            For Each r In aa.SubMatches
                                k = k + 1
                                ReDim Preserve brr(1 To k)
                                brr(k) = r
                            Next
                        Else
                            k = k + 1
                            ReDim Preserve brr(1 To k)
                            brr(k) = aa
                        End If
                    Next
                Next
            Next
        End With
    Else
        k = 0
        With regx
            .Global = True
            .Pattern = 表达式
            arr = rng
            Set matches = .Execute(arr(1))
            For Each aa In matches
                js = aa.SubMatches.Count
                If js Then
                    For Each r In aa.SubMatches
                        k = k + 1
                        ReDim Preserve brr(1 To k)
                        brr(k) = r
                    Next
                Else
                    k = k + 1
                    ReDim Preserve brr(1 To k)
                    brr(k) = aa
                End If
            Next
        End With
    End If
    正则提取 = brr
End Function

评分

4

查看全部评分

TA的精华主题

TA的得分主题

发表于 2022-5-16 10:58 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2022-5-16 15:23 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
  1. =SUM(MOD(-IMREAL(IMDIV(MID(TEXT(COLUMN(A:C)-2,""""&SUBSTITUTE(ENCODEURL(11&B6:B8),"%","i"";""")&"""i"),3,66),"1+i"))*2,1))
复制代码
这是题二的,题一怎么个应用太难了

评分

11

查看全部评分

TA的精华主题

TA的得分主题

发表于 2022-5-17 10:22 | 显示全部楼层
本帖最后由 晨光晞微 于 2022-5-17 15:14 编辑
  1. =TEXT(SUM(MOD(-IMREAL(IMDIV(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B6:B8,"+","*"),"/","*"),"*",REPT(" ",99)),COLUMN(A:C)*99-98,99)&"i","1+i"))*2,1)),"[h]:m:s")
复制代码

评分

5

查看全部评分

TA的精华主题

TA的得分主题

发表于 2022-5-17 13:31 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 Meteor-渣渣 于 2022-5-20 11:57 编辑

厚颜无耻的先占个楼

想了几天,都没想到合适的思路,只能第二题先开个火车
  1. =SUM(MOD(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B6:B8,"+","-"),"*","-"),"/","-"),"-",REPT(" ",99)),COLUMN(A:C)*2*99-98,99))+1-TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B6:B8,"+","-"),"*","-"),"/","-"),"-",REPT(" ",99)),(COLUMN(A:C)*2-1)*99-98,99)),1))
复制代码

灵光一闪,第一题解出来了,虽然还没进150字符
  1. =SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(A6,"-"," "))," ","-"),"+"," "))," ","+"),"*"," "))," ","*"),"/"," "))," ","/")
复制代码




补充内容 (2022-5-26 22:00):
题目一
=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A6,"+"," "),"-"," "))," ","+")," ","-"),"*"," "),"/"," "))," ","*")," ","/")
175字符

补充内容 (2022-5-26 22:00):
题目二
=SUM(MOD(1-IMREAL(IMDIV(TEXT(2-COLUMN(A:C),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B6:B8,"+",";"),"*",";"),"/",";"))&"j","1+j"))*2,1))
127字符

补充内容 (2022-5-26 22:05):
  1. =SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A6,"+"," "),"-"," "))," ","+")," ","-"),"*"," "),"/"," "))," ","*")," ","/")[code]
  2. [b]补充内容 (2022-5-26 22:06):[/b]
  3. 题目1
  4. [code]=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A6,"+"," "),"-"," "))," ","+")," ","-"),"*"," "),"/"," "))," ","*")," ","/")
复制代码

补充内容 (2022-5-26 22:07):
题目1
  1. =SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A6,"+"," "),"-"," "))," ","+")," ","-"),"*"," "),"/"," "))," ","*")," ","/")
复制代码

补充内容 (2022-5-26 22:07):
题目2
  1. =SUM(MOD(1-IMREAL(IMDIV(TEXT(2-COLUMN(A:C),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B6:B8,"+",";"),"*",";"),"/",";"))&"j","1+j"))*2,1))
复制代码

补充内容 (2022-5-26 22:08):
超过7天再编辑真的是太难了

评分

14

查看全部评分

TA的精华主题

TA的得分主题

发表于 2022-5-17 18:05 | 显示全部楼层
第一题答案 c6=CONCAT(IF(MID(A6,ROW($1:$90),1)=MID("?"&A6,ROW($1:$90),1),IF(ISNUMBER(--MID(A6,ROW($1:$90),1)),MID(A6,ROW($1:$90),1),""),MID(A6,ROW($1:$90),1)))  下拉  

评分

3

查看全部评分

TA的精华主题

TA的得分主题

发表于 2022-5-18 10:13 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 swyrlbq 于 2022-5-31 11:11 编辑

问题一
原以为TEXTJOIN是16版函数,CONCAT是13版函数,于是有了公式,
  1. =CONCAT(IF((MID(A6,ROW($1:$35),1)<>MID(A6,ROW($2:$36),1))+(CODE(MID(A6,ROW($1:$35),1))>47),MID(A6,ROW($1:$35),1),""))
复制代码


因公式简单,怀疑CONCAT不是13版函数,经过查询,的确如此。
帖子发了,删除不了。继续探究吧。
想到13版前函数,用常规方法似乎无法实现,于是想到用迭代计算的方法。在365平台下,通过反复探索修改,测试成功。

先设置启用迭代计算,100次,输入公式
  1. =IF(C6="00",LEFT(" "&A6,3),C6&MID(A6,SMALL(IF(MMULT(N(SUBSTITUTE(MID(A6,ROW($1:35)+{0,1},1),".",0)<"0"),{1;1})=2,99,ROW($1:35)),LEN(C6)),1))
复制代码
找个07版验证,不运算,多次寻找原因,似乎07版迭代计算不支持数组运算。
还想找个13版试验,安装卸载太麻烦,只好放弃。

又想探究问题2
搞出的公式,实在离要求太远,再没有思路优化了
  1. =SUM(MOD(MMULT(SMALL(IF(MID(SUBSTITUTE("/"&B6,".",0),ROW($1:40),1)<"0",ROW($1:40)+IFERROR(MID(B6,ROW($1:40),COLUMN(A:I))/1,)),({1;3;5}+{0,1})*9),{-1;1}),1))
复制代码
虽然两个问题都不能及格,但是参与也就是学习,期待大佬的杰作。




评分

10

查看全部评分

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

本版积分规则

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

GMT+8, 2024-7-13 21:34 , Processed in 0.039170 second(s), 12 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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