gpt4 book ai didi

excel - (Excel) 在 Excel 中报告街道号码

转载 作者:行者123 更新时间:2023-12-04 20:37:48 30 4
gpt4 key购买 nike

有谁知道是否可以在 Excel 中生成缺少地址门牌号的报告?

例如,我们在 Apple St (no.5, 9, 11) 有三个地址记录,是否可以生成一个报告:

列出工作簿中每条街道的所有记录街道编号,以及

还生成每条街道缺失的街道编号列表,使用 1 作为最小变量,最大记录的街道编号作为范围内的最大变量?

       **Sample Data**
Street Number: Street Name:
5 Apple St.
9 Apple St.
11 Apple St.
6 Lemon Rd.
3 Watermelon Cl.

因此,从这个示例数据中,理想情况下我想生成一个报告,如:
Street Name:    Recorded Street Numbers:
Apple St. 5, 9, 11
Lemon Rd. 6
Watermelon Cl. 3

Street Name: Missing Street Numbers:
Apple St. 1, 2, 3, 4, 6, 7, 8, 10
Lemon Rd. 1, 2, 3, 4, 5
Watermelon Cl. 1, 2

我已经使用 INDEX 和 MATCH 制作了一个不同的街道名称列表,但是关于如何将街道编号列为多个变量的字符串让我感到困惑。

我开始认为我可能需要使用 VBA 来获得我想要的东西,但我还需要尽可能简单,以便使用此报告的人可以更新和修改它。

任何帮助或指向正确方向的指针将不胜感激。谢谢。

最佳答案

这将为您完成:

Sub Check_Street_Number()
Dim i As Long, iStreet As Long, Street As String, Cel As Range

ActiveSheet.Name = "Inputs"
If ActiveWorkbook.Sheets.Count = 1 Then Sheets.Add After:=Sheets("Inputs")
Sheets(2).Name = "Output"

Sheets("Inputs").Activate
Range("B2").Select
Range("B2:B" & Range("B2").End(xlDown).Row).Copy
Sheets("Output").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Range("A2:A" & Range("A2").End(xlDown).Row).RemoveDuplicates Columns:=1, Header:=xlNo
ActiveSheet.Range("A2:A" & Range("A2").End(xlDown).Row).Copy
Range("A" & Range("A2").End(xlDown).Row + 3).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A" & Range("A2").End(xlDown).Row + 2).Value = "Street Name:"
Range("B" & Range("A2").End(xlDown).Row + 2).Value = "Missing Street Numbers:"
Range("A1").Value = "Street Name:"
Range("B1").Value = "Recorded Street Numbers:"
Range("A1:B1").Font.Bold = True
Range("A" & Range("A2").End(xlDown).Row + 2 & ":B" & Range("A2").End(xlDown).Row + 2).Font.Bold = True

Do Until iStreet = ActiveSheet.Range("A2:A" & Range("A2").End(xlDown).Row).Cells.Count
i = 1
iStreet = iStreet + 1
Street = Cells(1 + iStreet, 1).Value
Do Until i > WorksheetFunction.Max(Sheets("Inputs").Range("A2:A" & Range("A2").End(xlDown).Row))
Range("E1").FormulaR1C1 = "=COUNTIFS(Inputs!R[1]C[-3]:R[5]C[-3],""" & Street & """,Inputs!R[1]C[-4]:R[5]C[-4]," & i & ")"
If Range("E1").Value = 0 Then
If Range("B" & Range("A2").End(xlDown).Row + 2 + iStreet).Value = "" Then
Range("B" & Range("A2").End(xlDown).Row + 2 + iStreet).Value = i
Else
Range("B" & Range("A2").End(xlDown).Row + 2 + iStreet).Value = Range("B" & Range("A2").End(xlDown).Row + 2 + iStreet).Value & ", " & i
End If
Else
If Range("B" & 1 + iStreet).Value = "" Then
Range("B" & 1 + iStreet).Value = i
Else
Range("B" & 1 + iStreet).Value = Range("B" & 1 + iStreet).Value & ", " & i
End If
End If
i = i + 1
Loop
Range("B" & Range("A2").End(xlDown).Row + 2 + iStreet).HorizontalAlignment = xlRight
Range("B" & 1 + iStreet).HorizontalAlignment = xlRight
Loop

Range("E1").Value = ""
Columns("A:B").EntireColumn.AutoFit

End Sub

关于excel - (Excel) 在 Excel 中报告街道号码,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/39407202/

30 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com