Excel 大数据到汇总表

Excel 大数据到汇总表

附加的 Excel 工作簿包含 2 个工作表,第一个是数据,第二个是结果。

在数据表中,“A”列包含长度(10 米),“B”列包含要使用的杆的类型。

在结果表(即数据表的摘要)中,“A”列应包含起始值,“B”列包含要使用的杆类型。是否有任何公式可以自动生成表格。

https://drive.google.com/open?id=1CRw3Wy2ELyBjl07onnWXPIAprDiraJTC

任何帮助将不胜感激。

答案1

:警告:

由于pole type存在奇数组合(重复组),因此建议的方法不如预期那么好和有效,但最终它是有效的。

在此处输入图片描述

怎么运行的:

  • C41单元格中辅助数据的公式:

    =IF(B41=B41,COUNTIF(B$41:B41,B41),"")

  • 在列中输入杆类型F

注意:

  • 对于极点列表,需要公式来创建唯一列表。但由于组重复,我无法这样做。

  • 如果读者可以做到这一点,请建议我编辑它。

  • 数组(CSE)公式单元格中的公式D41

    {=MIN(IF($B$41:$B$65=$F41,IF($C$41:$C$65>=1,IF($C$41:$C$65<=6,$A$41:$A$65))))}
    
  • 单元格中的数组(CSE)公式E41

    {=MAX(IF($B$41:$B$65=$F41,IF($C$41:$C$65>=1,IF($C$41:$C$65<=6,$A$41:$A$65))))}
    

注意:

  • 完成配方Ctrl+Shift+Enter

  • 抄下来。

对两个公式进行如下所示的修正。

对于第一个 A:IF($C$41:$C$65>=1,IF($C$41:$C$65<=6

对于第一个 B:IF($C$41:$C$65>=1,IF($C$41:$C$65<=5

对于 Cs:IF($C$41:$C$65>=1,IF($C$41:$C$65<=5

对于 Ds:IF($C$41:$C$65>=1,IF($C$41:$C$65<=3

对于第二个 A:IF($C$41:$C$65>=7,IF($C$41:$C$65<=9

对于第二学士学位:IF($C$41:$C$65>=6,IF($C$41:$C$65<=8

  • 为了以后整洁,您可以隐藏辅助列。

:我的建议:

如果允许整个数据按升序排序,那么它将变成简单的组,整个操作变得更加容易,并且可以应用如下所示的有效方法。

在此处输入图片描述

怎么运行的:

  • 单元格中的数组(CSE)公式L41

    {=IFERROR(INDEX($I$41:$I$65,MATCH(0,COUNTIF($L$40:L40,$I$41:$I$65),0)),"")}
    
  • 在单元格中输入数组(CSE)公式J41

    {=MIN(IF($I$41:$I$65=$L41,$H$41:$H$65))}

  • 单元格中的数组(CSE)公式K41

    {=MAX(($I$41:$I$65=$L41)*$H$41:$H$65)}

注意:

  • 使用以下方法完成上面显示的数组公式Ctrl+Shift+Enter,并向下填充。

  • 根据需要调整公式中的单元格引用。

答案2

虽然您没有vba标签,但使用 vba 宏非常简单,并且可以轻松适应您的问题。

要输入此宏(子),alt-F11请打开 Visual Basic 编辑器。确保您的项目在窗口中突出显示Project Explorer。然后,从顶部菜单中,选择Insert/Module下面的代码并将其粘贴到打开的窗口中。

要使用此宏(子),alt-F8请打开宏对话框。按名称选择宏,然后RUN

Option Explicit
Sub Summarize()
    Dim wsSrc As Worksheet, wsRes As Worksheet, rRes As Range
    Dim vSrc As Variant, vRes As Variant
    Dim D As Object
    Dim I As Long, V As Variant

'Set data and results worksheets, range
Set wsSrc = Worksheets("Data")
Set wsRes = Worksheets("Result")
    Set rRes = wsRes.Cells(1, 1)

'read data into vba variant array (faster processing)
With wsSrc
    vSrc = .Range(.Cells(1, 1), .Cells(.Rows.Count, 2).End(xlUp))
End With

'collect Data Groups
Set D = CreateObject("scripting.dictionary")
For I = 2 To UBound(vSrc, 1) 'skip the header row
    If vSrc(I - 1, 2) <> vSrc(I, 2) Then
        D.Add Key:=vSrc(I, 1), Item:=vSrc(I, 2)
    End If
Next I

'create results array
ReDim vRes(0 To D.Count, 1 To 3)

'Header row
vRes(0, 1) = "Start"
vRes(0, 2) = "End"
vRes(0, 3) = "Type"

'Data
I = 0
For Each V In D
    I = I + 1

    'Start Data
    If I = 1 Then
        vRes(I, 1) = 0
    Else
        vRes(I, 1) = V
    End If

    'Type Data
    vRes(I, 3) = D(V)
Next V

'End data
I = 2
Do Until I > UBound(vRes, 1)
    vRes(I - 1, 2) = vRes(I, 1)
    I = I + 1
Loop

'Write to the worksheets
Set rRes = rRes.Resize(rowsize:=UBound(vRes, 1) + 1, columnsize:=UBound(vRes, 2))
With rRes
    .EntireColumn.Clear
    .Value = vRes
    With .Rows(1)
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
    End With
    .EntireColumn.AutoFit
End With

End Sub

数据结果

在此处输入图片描述

相关内容