ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

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

[已解决] 按指定条件、指定序号忽略空格提取对应数据的自定义函数

[复制链接]

TA的精华主题

TA的得分主题

发表于 2020-1-17 12:53 | 显示全部楼层
WYS67 发表于 2020-1-17 12:02
老师:发现17楼最后代码输入=ZDXHTQ($A$5:$A$100000,$J$3,$B$5:$B$100000,"18:1")
=ZDXHTQ($A$5:$A$1000 ...

已经更新附件!!!

评分

2

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2020-1-17 13:22 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2020-1-17 13:22 | 显示全部楼层
lss001 发表于 2020-1-17 12:53
已经更新附件!!!

已测试,这次完全正确!谢谢老师!!!

TA的精华主题

TA的得分主题

发表于 2020-1-20 13:52 | 显示全部楼层
本帖最后由 玉阳山人 于 2020-1-20 13:53 编辑

1.gif 2.gif

按间隔序号为周期多条件计数.zip (398 KB, 下载次数: 3)

老师:经多次验证,发现我先前在附件里所说的最大序号的指定不对--不能是序号区域E5:E5000最大的那个序号【E1931里的4623】,而应该调用G1指定的总表最大序号才对【惟有这样,表二的L664:N664才能显示正确的结果 0,2,3】!

为此,需要:

1.在参数序号区域后面,增添一个新的参数-总表最大序号【此参数可以是数字常量,单元格调用,或公式计算结果】;
2.直接用总表最大序号G1作为满额与不满额周期选择【第五参数】的计算基数,而不再使用序号区域E5:E5000中的最大序号【E1931里的4623】作计算基数。为此,需要对下面代码的红色部分进行修改

3.下面原代码参数的先后顺序是: =XHCOUNTIF(数据区域,指定需要计数的数据,每周期间隔序号,满额与不满额周期选择,序号区域),
添加参数【总表最大序号】后,最好变成这样的顺序:=XHCOUNTIF(数据区域,序号区域,总表最大序号,每周期间隔序号,满额与不满额周期选择,指定需要计数的数据),
    如:选定L5:L5000,输入区域数组公式  { =XHCOUNTIF($G$5:$G$5000,$E$5:$E$5000【可省略】,$G$1,$N$1,$N$2,L$4),看起来清楚明了,一目了然!

原代码如下:

Option Explicit

Public Function XHCOUNTIF(QY As Range, tj, ZQ, Optional x = 0, Optional y As Range, Optional lngStart As Long = 5)
    Application.Volatile
    Dim arr As Variant, brr As Variant, crr As Variant, lngCol As Long
    Dim lngMaxRound As Long, lngMax As Long, lngRow As Long
    Dim lngID As Long, lngVal As Long

    arr = QY
    lngCol = 5 - QY.Column
    If y Is Nothing Then
        crr = QY.Offset(, lngCol)
    Else
        crr = y
    End If

    '满期计算,满期为0,不满返回最后的周期号
    lngMax = Application.WorksheetFunction.Max(crr)
    If (lngMax - lngStart + 1) / ZQ = (lngMax - lngStart) \ ZQ Then
        lngMaxRound = 0
    Else
        lngMaxRound = (lngMax - lngStart) \ ZQ + 1
    End If
    lngMax = (lngMax - lngStart) \ ZQ + 1

    ReDim brr(1 To UBound(crr), 1 To 1) As Variant
    For lngRow = 1 To UBound(brr)
        brr(lngRow, 1) = ""
    Next

    For lngRow = 1 To UBound(arr)
        If lngRow <= lngMax Then brr(lngRow, 1) = Val(brr(lngRow, 1))
        If Trim(arr(lngRow, 1)) <> "" Then
            lngVal = Val(arr(lngRow, 1))
            If lngVal = tj Then
                lngID = ((crr(lngRow, 1) - lngStart) \ ZQ) + 1
                brr(lngID, 1) = Val(brr(lngID, 1)) + 1
            End If
        End If
    Next

    If x = 0 And lngMax <> 0 Then
        brr(lngMax, 1) = ""
    End If

    XHCOUNTIF = brr
End Function


TA的精华主题

TA的得分主题

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

首先预祝老师新春快乐!

恳请老师看看34楼的代码怎么修改?

TA的精华主题

TA的得分主题

发表于 2020-1-20 17:13 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
玉阳山人 发表于 2020-1-20 16:40
首先预祝老师新春快乐!

恳请老师看看34楼的代码怎么修改?

自己的需求,自己忘了?第四参数,设置为1,最后不满期的会显示!
也就是现在公式中的N2单元格

TA的精华主题

TA的得分主题

发表于 2020-1-20 17:28 | 显示全部楼层
本帖最后由 玉阳山人 于 2020-1-20 19:25 编辑
lsdongjh 发表于 2020-1-20 17:13
自己的需求,自己忘了?第四参数,设置为1,最后不满期的会显示!
也就是现在公式中的N2单元格
----------------------------------------

TA的精华主题

TA的得分主题

发表于 2020-1-20 17:40 | 显示全部楼层
本帖最后由 玉阳山人 于 2020-1-20 18:32 编辑

---------------------------------------

TA的精华主题

TA的得分主题

发表于 2020-1-20 17:51 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 玉阳山人 于 2020-1-20 19:27 编辑
lsdongjh 发表于 2020-1-20 17:13
自己的需求,自己忘了?第四参数,设置为1,最后不满期的会显示!
也就是现在公式中的N2单元格
---------------------------------------------



TA的精华主题

TA的得分主题

发表于 2020-1-20 19:19 | 显示全部楼层
本帖最后由 玉阳山人 于 2020-1-20 23:30 编辑
lsdongjh 发表于 2020-1-20 17:13
自己的需求,自己忘了?第四参数,设置为1,最后不满期的会显示!
也就是现在公式中的N2单元格

老师:设新增加的参数总表最大序号的名称为ZDXH, 试着把 lngMax = Application.WorksheetFunction.Max(crr)  置换成lngMax = ZDXH,则原代码修改为:
Public Function XHCOUNTIF(QY As Range, ZDXH, tj, ZQ, Optional x = 0, Optional y As Range, Optional lngStart As Long = 5)
    Application.Volatile
    Dim arr As Variant, brr As Variant, crr As Variant, lngCol As Long
    Dim lngMaxRound As Long, lngMax As Long, lngRow As Long
    Dim lngID As Long, lngVal As Long

    arr = QY
    lngCol = 5 - QY.Column
    If y Is Nothing Then
        crr = QY.Offset(, lngCol)
    Else
        crr = y
    End If

    '满期计算,满期为0,不满返回最后的周期号
    lngMax = ZDXH   
    If (lngMax - lngStart + 1) / ZQ = (lngMax - lngStart) \ ZQ Then
        lngMaxRound = 0
    Else
        lngMaxRound = (lngMax - lngStart) \ ZQ + 1
    End If
    lngMax = (lngMax - lngStart) \ ZQ + 1

    ReDim brr(1 To UBound(crr), 1 To 1) As Variant
    For lngRow = 1 To UBound(brr)
        brr(lngRow, 1) = ""
    Next

    For lngRow = 1 To UBound(arr)
        If lngRow <= lngMax Then brr(lngRow, 1) = Val(brr(lngRow, 1))
        If Trim(arr(lngRow, 1)) <> "" Then
            lngVal = Val(arr(lngRow, 1))
            If lngVal = tj Then
                lngID = ((crr(lngRow, 1) - lngStart) \ ZQ) + 1
                brr(lngID, 1) = Val(brr(lngID, 1)) + 1
            End If
        End If
    Next

    If x = 0 And lngMax <> 0 Then
        brr(lngMax, 1) = ""
    End If

    XHCOUNTIF = brr
End Function

可以显示计算结果。只是如下面截图和附件所示,G1指定的总表最大序号为4624,N1指定的每周期间隔序号为7,MOD(G1-4【表头】,N1)的余数正好为0,代表最后那个周期的数据刚好满额,则不论N2指定为0还是1,L:N列计算结果的最后那个周期的结果,都会显示在(4624-4)/7+4=     L664:N664行上才对,但实际情况却是:指定N2为1时,L664:N664依次显示1,2,3,结果正确;指定N2为0时,由于G1指定的最大序号4624刚好是每周期间隔序号7的整倍数,所以L664:N664也应该依次显示1,2,3才对【而不是显示空白】。

1.gif


按间隔序号为周期多条件计数 - 副本.zip (227.28 KB, 下载次数: 0)

运算规则重要提醒当 MOD(ZDXH-4,N1) =0【即最大序号减去表头4行后,恰好能够被N1指定的序号周期整除】时,无论N2指定0或1,L664:N664都应该显示计算结果(因为最后周期的数据正好满额-这种情况发生的概率是1/N1);而只有在MOD(ZDXH-4,N1) <>0的前提下,当N2指定为0时不显示结果,N2指定为1时显示结果

恳请老师修改和优化代码为盼。
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

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

GMT+8, 2024-3-29 13:52 , Processed in 0.053587 second(s), 9 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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