创建 多条件查询的自定义函数

WYS67

lss001 发表于 2018-11-17 22:40
已经有自下而上，自上而下了！
第四参数为0→自上而下！
第四参数为1→自下而上！

老师：31楼里指定次序为单值的用途也很广。最好保留指定次序为单值【不是区域】时，求B:G列各项计算结果这一功能。

WYS67

lss001 发表于 2018-11-17 20:14
请重新下载附件！

老师：今早下载后，经过反复测试，发现计算结果不正确。原因是:如《总表》O5：O27所示，公式提取的指定号码566最后27期的遗漏期数不正确，其正确的计算结果应该是《总表》K:M里L列所显示的。看来是自定义函数DTJCX原代码计算功能不适合拓展遗漏分区和剩余期数的缘故。
   经过多次验证，发现用自定义函数WZTJTQ的代码拓展遗漏分区和剩余期数最合适不过！《WZTJTQ代码》里有详细的说明、语法和实例，《WZTJTQ功能拓展》的B:C列是指定号码144的顺排和定位遗漏期数，《总表》K:M列分别是指定号码566的遗漏期数、遗漏分区和剩余期数。

《WZTJTQ代码》里G39:G126里有自定义函数WZTJTQ的代码。
恳请老师按照WZTJTQ代码的计算功能拓展代码。如果您觉得WZTJTQ代码不符合您的编写习惯，也可以重新编写，只需要全部保留WZTJTQ代码原有的计算功能，并且拓展遗漏分区和剩余期数就行！

             WZTJTQ计算功能拓展.zip (1.69 MB, 下载次数: 16)

2018-11-18 13:16 上传

点击文件名下载附件

老师：您也可以继续使用33楼附件，按《总表》N列的指定次序，自下而上地提取指定号码的遗漏期数【指定号码566的提取结果应该与《总表》K5:531K5:K31完全相同才对】！
问题就出现在指定号码的遗漏期数没有按指定次序提取这一关键所在上！
41楼前两个工作表的B:C列里有求指定次序遗漏期数的通用的内置函数数组公式！
{ =IFERROR(IF(B$4=0,INDEX(总表!$J:$J,SMALL(IF((总表!$I$5:$I$35104&""=$C$1&"")*(总表!$J$5:$J$35104<>""),ROW($5:$35104)),$A26)),INDEX(总表!$J:$J,LARGE(IF((总表!$I$5:$I$35104&""=$C$1&"")*(总表!$J$5:$J$35104<>""),ROW($5:$35104)),$A26))),"")

WYS67

lss001 发表于 2018-11-17 20:14
请重新下载附件！

老师：40楼的自上而下和自下而上提取遗漏期数，在下面附件中有详细的函数公式和计算结果，看了《正序提取数据》A:C列的计算结果和《反序提取数据》A:C列的计算结果，您就知道33楼代码DTJCX代码的错误就出在指定号码的遗漏期数没有按指定次序提取这一关键所在上！

正序、反序.zip (1.6 MB, 下载次数: 6)

2018-11-18 15:48 上传

点击文件名下载附件

只要按照《正序提取数据》和《反序提取数据》B5:C1000的运算规则进行编码就行了！
另外，切记！当=DTJCX(总表!$I$5:$I$35104,$C$1,总表!$J$5:$J$35104,B$4,$A5)  最后指定次序不是区域，而是单元格时，B:G列的计算结果需要修改为：求指定次序$A5单元格的单值计算。

WYS67

lss001 发表于 2018-11-17 20:14
请重新下载附件！

恳请老师：按41楼附件《正序提取数据》和《反序提取数据》B:C列的运算规则提取遗漏期数。

WYS67

lss001 发表于 2018-11-17 20:14
请重新下载附件！

老师：41楼前两个工作表的B:C列里有求指定次序遗漏期数的通用的内置函数数组公式！
{ =IFERROR(IF(B$4=0,INDEX(总表!$J:$J,SMALL(IF((总表!$I$5:$I$35104&""=$C$1&"")*(总表!$J$5:$J$35104<>""),ROW($5:$35104)),$A26)),INDEX(总表!$J:$J,LARGE(IF((总表!$I$5:$I$35104&""=$C$1&"")*(总表!$J$5:$J$35104<>""),ROW($5:$35104)),$A26))),"")


用这个公式可以正确显示《正序提取数据》和《反序提取数据》B:C列的所有计算结果！

WYS67

lss001 发表于 2018-11-17 20:14
请重新下载附件！

下载后经过反复测试验证，无论是→0自上而下，→1自下而上，还是《总表》K5:K31指定正序、反序排列，都能显示正确的计算结果！谢谢老师了！


还有一个指定次序为单值查询的问题：如下面截图所示：以B5:G1000公式为例：当第五参数的指定次序不是区域$A$5:$A$1000,而是指定单元格$A$1004的“25”时，怎样才能在B1004:G1004中自动显示指定次序为“25”时单值计算结果？


33楼.zip (1.54 MB, 下载次数: 2)

2018-11-18 19:57 上传

点击文件名下载附件

指定次序为次序区域 $A$5:$A$1000 适用于日清月结，大批量的查询统计计算，而指定次序为单值$A1004则适用于随时随地地即时查询和统计计算，平时要经常用到。所以，代码最好能够在第五参数为单值时也能显示正确的计算结果，就如B1004:G1004显示的那样。

WYS67

恳请帮忙解决45楼提出的问题。

WYS67

lss001 发表于 2018-11-17 20:14
请重新下载附件！

《总表》K3正确答案应该是127才对，而不是61，还有下边截图里A1004为指定次序，B1004:G1004是代码计算结果，与正确答案B1006:G1006没有一个能对上啊？！


33楼.zip (1.6 MB, 下载次数: 0)

2018-11-18 21:53 上传

点击文件名下载附件

老师：41楼前两个工作表的B:C列里有求指定次序遗漏期数的通用的内置函数数组公式！用到B1004:C1004的单值计算上，则是：
{ =IFERROR(IF(B$4=0,INDEX(总表!$J:$J,SMALL(IF((总表!$I$5:$I$35104&""=$C$1&"")*(总表!$J$5:$J$35104<>""),ROW($5:$35104)),$A1004)),INDEX(总表!$J:$J,LARGE(IF((总表!$I$5:$I$35104&""=$C$1&"")*(总表!$J$5:$J$35104<>""),ROW($5:$35104)),$A1004))),"")  我不知道VBA怎么写，但这个函数公式复制粘贴到B1004:C1004后，就能得出正确的结果。