阳光小院 发表于 2021-12-20 21:44:27

PQ解法参考一下

清溪流萍 发表于 2021-12-30 19:30:14

Sub test()
Dim i%, j%, k%, arr, d
Set d = CreateObject("Scripting.Dictionary")
arr = Sheet1..CurrentRegion
For i = 2 To UBound(arr)
For j = Val(Split(arr(i, 2), "-")(0)) To Val(Split(arr(i, 2), "-")(1))
    For k = Val(Split(arr(i, 4), "-")(0)) To Val(Split(arr(i, 4), "-")(1))
      d(arr(i, 1) & "," & j & "," & arr(i, 3) & "," & Format(CStr(k), "00")) = arr(i, 5)
    Next
Next
Next
arr = Sheet1..CurrentRegion
For i = 2 To UBound(arr)
arr(i, 5) = d(arr(i, 1) & "," & arr(i, 2) & "," & arr(i, 3) & "," & arr(i, 4))
Next
Sheet1..CurrentRegion = arr
End Sub

来不及许愿的流星 发表于 2022-1-1 22:56:10

太复杂了，不适合我这种初学者啊

李元芳 发表于 2022-1-3 14:56:49

=VLOOKUP(H2&I2&J2,IF({1,0},RIGHT($B$2:$B$9,2)&$C$2:$C$9&RIGHT($D$2:$D$9,2),$E$2:$E$9),2,0)
三键下拉另外你的数据源中没有满足的条件

坦荡仁生 发表于 2022-1-3 15:11:36


=IFERROR(LOOKUP(1,0/((RIGHT($B$2:$B$9,2)=H2&"")*($C$2:$C$9=I2)*(LEFT($D$2:$D$9,2)=J2)),$E$2:$E$9),"")

雷艺教育科技有限公司 发表于 2022-1-15 12:25:05

有点长。。。
=LOOKUP(1,0/(A$2:A$9=G2)/(H2>=--SUBSTITUTE(LEFT(B$2:B$9,2),"-",))/(C$2:C$9=I2)/(J2>=--SUBSTITUTE(LEFT(D$2:D$9,2),"-",)),E$2:E$9)

兴辉陶瓷六哥 发表于 2022-1-15 20:30:43

楼主装了个陷阱
=LOOKUP(1,0/(A$2:A$9=G2)/(H2>=--SUBSTITUTE(LEFT(B$2:B$9,2),"-",))/(C$2:C$9=I2)/(-J2<=-SUBSTITUTE(LEFT(D$2:D$9,2),"-",)),E$2:E$9)

乐在旅途 发表于 2022-1-23 13:03:18

按照原数据显示全部房号，在用公式，原数据有变动，刷新数据连接

小格影像 发表于 2022-1-26 14:27:26

谢谢回复。我的表格数据比较多，列出所有房号的方法不适用，不过还是非常感谢

淡淡静海 发表于 2022-1-31 04:46:27

=VLOOKUP(H2&I2&J2,IF({1,0},RIGHT($B$2:$B$9,2)&$C$2:$C$9&RIGHT($D$2:$D$9,2),$E$2:$E$9),2,0)
三键下拉另外你的数据源中没有满足的条件
页: 1 [2] 3
查看完整版本: 请大神帮忙，多个条件匹配问题