怎么使用VBa解决条件引用问题

excel小白1

hfwufanhf2006 发表于 2019-5-27 16:28
我写了一段代码，放在表单“工单”中，会在f列生成对应的订单数量。与你的要求还有区别，因为我还没完全搞 ...

谢谢，不需要每个车号去统计，只需要每条工单去对应计算在一定时间内是否有订单产生，这个代码我先研究下，有啥不会的还得麻烦你指导下

excel小白1

hfwufanhf2006 发表于 2019-5-27 16:28
我写了一段代码，放在表单“工单”中，会在f列生成对应的订单数量。与你的要求还有区别，因为我还没完全搞 ...

转化率=工单总数/调度后在一定时间内产生的订单总数，这个时间段可以是1小时，6小时，12小时，24小时，根据这个公式可以算出这些时间段内的转化率

excel小白1

hfwufanhf2006 发表于 2019-5-27 16:28
我写了一段代码，放在表单“工单”中，会在f列生成对应的订单数量。与你的要求还有区别，因为我还没完全搞 ...

我试了下，大概可以实现目的，只不过中间有一个逻辑错误，1、同一车号在24小时内有两个连续工单之后又有一个订单，那么第一个工单我们应该视为无效工单，也就是转化率为0，2、不需要去计算一个工单24小时内共计产生多少订单，只需要去知道有或者没有产生工单即可，老哥帮忙在看下，怎么解决第一个问题

hfwufanhf2006

excel小白1 发表于 2019-5-28 10:04
我试了下，大概可以实现目的，只不过中间有一个逻辑错误，1、同一车号在24小时内有两个连续工单之后又有 ...

1、对工单产生的订单如果不用计算的话，时间会大幅缩减，大约为原来的一半，1分钟左右就可以运行完成。因此我把原来填写统计数的 F 列改成了文字“有订单”来识别，但我不知道这么修改后，你如何来计算转化率%？2、另一个要求 “同一车辆在24小时内有连续工单，前一个工单作废”，我使用了二次识别的方法。不过从你提供的数据中我还找不到一个这样的例子存在，因此我修改的代码能否正常运行我也没法判断，还要你实际测试；

修改后的代码：
Range(Cells(1, 6), Cells([a10000].End(3).Row, 6)).ClearContents
Cells(1, 6) = "转化率"
Application.ScreenUpdating = False
Dim arr
arr = Worksheets("订单").[a2:c25694]
For i = 2 To [a10000].End(3).Row
    s1 = Cells(i, 2)
    s2 = Cells(i, 4)
    js = 0
    bz = False
    For k = 1 To UBound(arr)
        If (arr(k, 1) = s1) And (DateDiff("n", s2, arr(k, 2)) <= 1440) And (arr(k, 3) <> "#") Then
           js = js + 1
           arr(k, 3) = "#"
           bz = True      '这是新增的，当找到第一个符合条件的订单，就立即终止检索，时间比原来更快的原因就在这里
           Exit For
        End If
    Next k
    If bz Then
       Cells(i, 6) = "有订单"     '只要有一个订单就记录 “有订单”，订单数量会被忽略
    Else
       Cells(i, 6) = ""
    End If
Next i
'下面的代码是做二次识别
Dim arr1
arr1 = Range(Cells(2, 2), Cells([a10000].End(3).Row, 4))
For i = 1 To UBound(arr1)
    s1 = arr1(i, 1)
    s2 = arr1(i, 3)
    For k = i + 1 To UBound(arr1)
        If (arr1(k, 1) = s1) And (DateDiff("n", s2, arr1(k, 3)) <= 1440) Then   '对“工单“做全检索，同一车辆在24小时内有任何重复工单，前一个工单的记录会被更改。比如如果同一车辆有4笔工单，目标是前三笔作废，只保留最后一笔工单
           Cells(i + 1, 8) = IIf(Cells(i + 1, 7) = "有订单", "无效订单", "")   '标签文字“无效订单”会被填在G列，原来记录的 F 列维持不变，因为我不确定这个代码是否能被正确执行。一旦你确认，你可以把这个代码按自己的要求做调整
        End If
    Next k
Next i
Application.ScreenUpdating = True

excel小白1

hfwufanhf2006 发表于 2019-5-28 12:11
1、对工单产生的订单如果不用计算的话，时间会大幅缩减，大约为原来的一半，1分钟左右就可以运行完成。因 ...

老哥，这是程序运行得到结果的其中一部分截图，目标没有实现，G列也没有如期出现，标红部分是同一车号24小时内的连续工单，下面哪一行是这个车号对应日期的订单，按照目标应该第一个工单标记为无效，我对VBA一知半解，现在还停留在函数熟练的阶段，麻烦老哥在看看，万分感谢。

工单编号	车牌号	调度员	完成时间	城市id	转化率
GD2019040903623	渝A005UY	王渠	2019/4/9 9:42	A市	有订单
GD2019040909866	渝A005UY	王渠	2019/4/9 12:43	A市	有订单

渝A005UY

2019/4/9 22:51

A市

excel小白1

hfwufanhf2006 发表于 2019-5-28 12:11
1、对工单产生的订单如果不用计算的话，时间会大幅缩减，大约为原来的一半，1分钟左右就可以运行完成。因 ...

另外，我感觉你可能有点误解了，不是“同一车辆在24小时内有连续工单，前一个工单作废”
如下：

工单编号	车牌号	调度员	完成时间	城市id	转化率
GD2019040903623	渝A005UY	王渠	2019/4/9 9:42	A市	有订单
GD2019040909866	渝A005UY	王渠	2019/4/9 12:43	A市	有订单

订单：

渝A005UY

2019/4/9 22:51

A市

第一个工单应该作废

hfwufanhf2006

excel小白1 发表于 2019-5-28 18:03
另外，我感觉你可能有点误解了，不是“同一车辆在24小时内有连续工单，前一个工单作废”
如下：

1、关键的一行代码把列算错了，你把：
     Cells(i + 1, 8) = IIf(Cells(i + 1, 7) = "有订单", "无效订单", "")

改成：
     Cells(i + 1, 7) = IIf(Cells(i + 1, 6) = "有订单", "无效订单", "")

其中：Cells(i + 1, 7)才是G列，Cells(i + 1, 6)是F列。之前的代码分别是H和G列。A列是数字1，b是2，以此类推，F是6，G是7

2、你先运行一下看看对不对，我运行的结果大约会产生几十个无效订单。至于你说我理解有错误，我还没看懂，如果你觉得不行，举一个具体例子可能我会比较好理解些。

excel小白1

hfwufanhf2006 发表于 2019-5-29 08:37
1、关键的一行代码把列算错了，你把：
     Cells(i + 1, 8) = IIf(Cells(i + 1, 7) = "有订单", "无效 ...

老哥，我试运行了这个程序，发现目前存在两个问题
1、在计算是否在24小时之内有订单时，逻辑上只能时间往后推，程序在计算时发现有向前去计算的，如下：

工单编号	车牌号	调度员	完成时间	城市id	转化率
GD2019040403072	渝A012DR	吴森荐	2019/4/4 8:21	A市	有订单

这个在向下取值24小时之内是没有产生订单的，向上取值24小时之内是有产生订单的，程序应该设置为向上或者向下满足24小时了

车牌号	计费时间 秒	城市名称
渝A012DR	2019/4/3 8:24	A市
渝A012DR	2019/4/5 13:01	A市

这个问题导致整体逻辑错误了
2、假设车号渝A00000在4月5日，11点有一条工单，15点有一条订单，18点又有一条工单，19点又有一条订单，那么根据程序计算，会得出如下结果

工单编号	车牌号	调度员	完成时间	城市id	转化率
GD2019041215603	渝A00000	严云超	2019/4/5 11:40	A市	有订单	无效订单
GD2019042011111	渝A00000	龙安宁	2019/4/5 18:18	A市	有订单

按照逻辑这两条工单都是有效调度工单，都在调度后24小时内产生有效订单，那么判断无效订单这一逻辑需要修改下，如遇到连续工单，先判断连续工单是否在24小时内都有订单，在去判断是否为同一条订单，接下来怎么去写我这就卡壳了，还有，因为附件有大小限制，所以我的附件只有其中一部分，实际数据有3-5倍附件大，这些问题都是在源数据中遇到的

hfwufanhf2006

excel小白1 发表于 2019-5-29 10:29
老哥，我试运行了这个程序，发现目前存在两个问题
1、在计算是否在24小时之内有订单时，逻辑上只能时间 ...

1、我仔细考虑了很久，我倾向于认为你这个问题的记录要素以及判断规则不完整，会导致歧义而无法判断。主要是没有明确的时间起始节点，所谓的“24小时之内”是相对于其他记录来说的，并不是自然的时间节点。所以我建议首先要明确时间节点，就像住宿，中午12点退房就算当天没住，下午2点入住过了6点算半天，过了晚上12点就算一天。没有类似的时间定义是没办法判断彼此关系的；2、在上述假设基础上，技术上可以先把所有的记录用标准的时间定义来分类，这个数据透视就能做，数据透视可以设定任意时间段的分组统计，时间单位可以设定为年、月、日、季度、周、小时、秒或者自定义时间间隔。这个分组统计的前提是要有一个标准的起始时间，也是自定义的；
3、经过分组定义后的记录，比如按同一个时间起点来划分24小时的车辆分组归属，就不会再出现同一车辆工单在订单记录中会有交叉判断的风险，数据透视会把所有24小时之内的订单和工单都合并了，你可以看成是以自然日来做判断了；
4、最后的代码匹配就简单了，因为都是一一对应的（原来交叉匹配的数据都被合并掉了）。所以我建议你先仔细考虑规则，必须是能合理匹配的规则才是容易实现的，你目前的数据我认为做准确判断还不行。

excel小白1

hfwufanhf2006 发表于 2019-5-29 14:01
1、我仔细考虑了很久，我倾向于认为你这个问题的记录要素以及判断规则不完整，会导致歧义而无法判断。主 ...

辛苦了，老哥，我在认真考虑下，不过你的代码还是为我打开了新大门，你说的我试试