☰

按类别对报告进行排序，包括小计和相应的详细信息，小计金额从大到小排列，然后按供应商名称排序

tag-icon • tag-icon microsoft-excel
按类别对报告进行排序，包括小计和相应的详细信息，小计金额从大到小排列，然后按供应商名称排序

我首先下载完整的数据，然后按类别和供应商名称添加小计。我需要的最终结果是一份首先按类别排序的报告，然后按类别内供应商名称按金额从大到小排序。我一直丢失的重要部分是构成小计总和的详细数据。

参见以下示例：

    tech subtotal   $14,308,342
GENERAL SERVICES    nor $20,314,000
GENERAL SERVICES    nor $3,200,000
    nor subtotal    $23,514,000
GENERAL SERVICES    le  $20,314,000
GENERAL SERVICES    le  $3,200,000
    le subtototal   $23,514,000
GENERAL SERVICES    ce  $22,500,000
GENERAL SERVICES    ce  $1,000,000
    ce subtotal $23,500,000
GENERAL SERVICES    po  $22,500,000
    po subtotal $22,500,000
GENERAL SERVICES    te  $20,314,000
    te subtotal $20,314,000
PROFESSIONAL SERVICESOFESSIONAL SERVICES    b1  $3,000,000
PROFESSIONAL SERVICES   b2  $495,000
PROFESSIONAL SERVICES   b3  $450,000
PROFESSIONAL SERVICES   b4  $400,000
PROFESSIONAL SERVICES   b5  $250,000
PROFESSIONAL SERVICES   b6  $230,000
PROFESSIONAL SERVICES   b7  $230,000
PROFESSIONAL SERVICES   b8  $180,000
PROFESSIONAL SERVICES   b9  $110,000
PROFESSIONAL SERVICES   b10 $75,000
PROFESSIONAL SERVICES   b11 $75,000
PROFESSIONAL SERVICES   b12 $75,000
    b1-12 subtota   $5,570,000

答案1

这正是数据透视表的用途。

选择所有数据，转到Insert>PivotTable并根据需要将字段拖到行、列、过滤器和值字段之间。

相关内容