Excel过滤合并的单元格 [英] Excel filtering for merged cells
问题描述
我正在计算每个员工在其从事的每个项目上的工作时间.但我不知道该如何选择,因为包含员工姓名的单元格已如图中所示合并.如果我想查看项目编号. 3是从事工作的员工,因此Excel筛选不能使用名称"John".仅对应于项目1.更清楚地说,我需要知道如何对单元格A3和A4进行过滤. 如果我不合并这些单元,则John只会在A2单元中工作,实际上,他还参与了项目2和项目2. 3.
I'm trying to make a calculation of hours worked for each employee on each project that he worked on. But i don't know how to select because the cells that are containing the name of the employee are merged like in the picture. And if i want to see on project no. 3 which are the employees that worked on, the Excel Filtering can't take the name "John" which corresponds only to project no.1. To be more clear, I need to know how the filtering will be made for cells A3 and A4. If i will unmerge the cells, John will be only on cell A2, and in fact he worked also on projects 2 & 3.
谢谢!
推荐答案
如果您有合并的单元格,并尝试对其进行过滤,则只会得到第一行:
If you have a Merged Cell, and you attempt to Filter for it, you will only get the first row:
要解决此问题,首先需要在其他位置创建合并的单元格,取消合并过滤器单元格,然后将值填充到所有单元格中:
To fix this, you first need to start by creating your Merged Cells somewhere else, unmerge your filter-cells, and fill the values into all cells:
然后,您可以复制合并的单元格,并在要合并的单元格上粘贴特殊">格式化":
Then, you can Copy the merged cells, and Paste Special > Formats over the cells you want to merge:
您现在可以删除临时合并的单元格,进行过滤时,您将获得合并单元格的所有行:
You can now delete your temporary merged cells, and when you filter you will get all rows for the merged cell:
{EDIT} 这是一个宏,它将自动将上述更改应用于指定范围:
{EDIT} Here is a macro that will automatically apply the changes above to a specified range:
Public Sub FilterableMergedCells()
Dim WorkingRange As Range
SelectRange:
Set WorkingRange = Nothing
On Error Resume Next
Set WorkingRange = Application.InputBox("Select a range", "Get Range", Type:=8)
On Error GoTo 0
'If you click Cancel
If WorkingRange Is Nothing Then Exit Sub
'If you select multiple Ranges
If WorkingRange.Areas.Count > 1 Then
MsgBox "Please select 1 continuous range only", vbCritical
GoTo SelectRange
End If
Dim ScreenUpdating As Boolean, DisplayAlerts As Boolean, Calculation As XlCalculation
ScreenUpdating = Application.ScreenUpdating
DisplayAlerts = Application.DisplayAlerts
Calculation = Application.Calculation
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Dim WorkingCell As Range, MergeCell As Range, MergeRange As Range, OffsetX As Long, OffsetY As Long
OffsetX = WorkingRange.Cells(1, 1).Column - 1
OffsetY = WorkingRange.Cells(1, 1).Row - 1
'Create temporary sheet to work with
With Worksheets.Add
WorkingRange.Copy .Cells(1, 1)
'Loop through cells in Range
For Each WorkingCell In WorkingRange.Cells
'If is a merged cell
If WorkingCell.MergeCells Then
'If is the top/left merged cell in a range
If Not Intersect(WorkingCell, WorkingCell.MergeArea.Cells(1, 1)) Is Nothing Then
Set MergeRange = WorkingCell.MergeArea
'Unmerge cells
MergeRange.MergeCells = False
'Replicate value to all cells in formerly merged area
For Each MergeCell In MergeRange.Cells
If WorkingCell.FormulaArray = vbNull Then
MergeCell.Formula = WorkingCell.Formula
Else
MergeCell.FormulaArray = WorkingCell.FormulaArray
End If
Next MergeCell
'Copy merge-formatting over old Merged area
.Cells(WorkingCell.Row - OffsetY, WorkingCell.Column - OffsetX).MergeArea.Copy
WorkingCell.PasteSpecial xlPasteFormats
End If
End If
Next WorkingCell
.Delete
End With
Set MergeRange = Nothing
Set WorkingRange = Nothing
Application.ScreenUpdating = ScreenUpdating
Application.DisplayAlerts = DisplayAlerts
Application.Calculation = Calculation
End Sub
这篇关于Excel过滤合并的单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!