步骤1:如下图,选择A2:A7,按F5打开定位对话框,选择"定位条件"--"空值",你会发现空单元格被选中
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/79f0f736afc37931e53eb30fe1c4b74543a91145?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
接着按=A2,按Ctrl+Enter,填满空格。
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/242dd42a2834349b4ab55aa3c3ea15ce36d3be60?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
步骤2:选择这片区域,"插入"--"透视表",照图把字段拖进对应区域。再进行调整,在数据透视表工具里,去掉分类汇总、总计。报表布局选"表格形式",空行选"每个项目后插入空行",如下图:
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/c995d143ad4bd11392e6801450afa40f4bfb05e8?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
步骤3:全选数据透视表,复制,以值的方式粘贴到新工作表中,如图:
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/ca1349540923dd54c300914edb09b3de9c8248a0?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
这时日期是数值形式显示,别担心,更改为日期格式即可。接下来,我编写了一段代码,你确保Excel启用宏,然后按ALT+F11,进入编辑器,选择"插入"--"模块",把代码贴进去
Sub FillGe()
Dim GeRng As Range
Dim DateRng As Range
Dim cell As Range
Dim GeCount As Long
Dim TempString As String
Dim k As Long
k = 1
Set GeRng = Range("A3")
Set DateRng = Range("C2")
Do Until IsEmpty(GeRng)
GeCount = Range(GeRng.Offset(0, 1), GeRng.Offset(0, 1).End(xlDown)).Count
Do Until IsEmpty(DateRng)
For Each cell In DateRng.Offset(GeRng.Row - DateRng.Row, 0).Resize(GeCount, 1)
If cell.Value = 1 Then
TempString = TempString & "," & cell.Offset(0, -k)
End If
Next cell
On Error Resume Next
DateRng.Offset(GeRng.Row - DateRng.Row + GeCount, 0) = Right(TempString, Len(TempString) - 1)
On Error GoTo 0
TempString = ""
Set DateRng = DateRng.Offset(0, 1)
k = k + 1
Loop
Set GeRng = GeRng.Offset(GeCount + 1, 0)
Set DateRng = Range("C2")
k = 1
GeRng.Offset(-1, 0).Value = GeRng.Offset(-GeCount - 1, 0).Value
Loop
End Sub
按F5运行,得到效果如下图:
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/4a36acaf2edda3cc1bde8a060be93901213f92d9?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
步骤4,这时应该比较简单了,对表格筛选,然后B列选择地理位置和空,然后修改日期格式,得到最终结果:
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/9f2f070828381f30e0a1bf8fa3014c086e06f05f?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)