澳门赌场

Excel 分类汇总按年、月和周且用数据透视表与公式完成,并把汇总成果提掏出来

亮术网 2018-11-23 本网首创

在 Excel 中,按年、月、日和周分类汇总用数据透视表比拟简略便利,停止简略的设置就能够够前往它们的汇总成果;按年、月和日汇总能够间接显现它们,但按周汇总只能用日期表现周;而用公式汇总能够完成把属于每周的日期转为第几周。

用公式完成按年、月和周分类汇总,首要用 Sum、SumIfs、SumProduct、Value、WeekNum、Date、Row 和 Text 等函数,比方按年和月汇总用前四个函数,而按周汇总庞杂一些,须要用 Sum(或 SumProduct) + Value + WeekNum + Row 完成。

 

一、Excel 分类汇总用数据透视表汇总

(一)按年和月汇总

1、建立数据透视表。选中表格此中一个单位格,挑选“拔出”选项卡,单击屏幕左上角的“数据透视表”,翻开“建立数据透视表”窗口,坚持默许设置,单击“肯定”,则在新任务表建立一个数据透视表;勾选字段“服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net称号、日期和停业额”,已主动按“年”汇总好了每种衣服的停业额;操纵进程步骤,如图1所示:

Excel 分类汇总用数据透视表汇总

图1

2、按月汇总。选中此中一个年份的单位格(如 A5),挑选“阐发”选项卡,单击“组字节”,翻开“组合”窗口,“步长”仅挑选“年和月”(单击“季度”打消挑选),单击“肯定”,则按年和月汇总出了每种服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net的停业额;若是年份要显现到列,把“年”从“行区间”拖到“列区间”;操纵进程步骤,如图2所示:

Excel用数据透视表按月汇总

图2

 

(二)按周汇总

1、以把上例的按年和月分类汇总改成按周为例。选中年份的此中一个单位格(如 B4),挑选“阐发”选项卡,单击“组字节”,翻开“组合”窗口,把“肇端于”由 2018/11/1 改成 2018/10/29,单击“步长”下的“年和月”打消挑选,单击“日”选中它,“天数”由灰色变为可选,把 1 改成 7,单击“肯定”,则每件衣服按“周”汇总;操纵进程步骤,如图3所示:

Excel用数据透视表按周汇总

图3

2、“肇端于”主动填入的日期 2018/11/1 是表格的肇端日期,但不是周一,是以要把它改成“周一”的日期 2018/10/29,如许就能够完成按周汇总。

 

(三)对汇总成果排序

1、对差别服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net的停业额按“周”排序。右键此中一件衣服的停业额,如 B5,在弹出的菜单中,顺次挑选“排序”→ 升序,则每件衣服每周的停业额都按“升序”摆列;操纵进程步骤,如图4所示:

Excel用数据透视表对汇总成果排序

图4

2、对每件衣服的“停业额”按“升序”排序。右键肆意一件衣服的汇总停业额,如 B4,在弹出的菜单中,一样顺次挑选“排序”→ 升序,则每件衣服按“停业额”升序摆列,每件衣服的周停业额也随之排序;操纵进程步骤,如图5所示:

Excel 分类汇总对每件衣服的停业额按升序排序

图5

有关更多数据透视表的操纵,请参考《Excel数据透视表怎样做的15个操纵方式,含非反复计数和百分比统计及四个地域间的用处》、《Excel数据透视表主动天生分页报表和把日报表分解月报表及显现报表挑选页为灰色的处理方式》和《Excel多重归并计较数据地域用于归并多个表格到数据透视表,含把地域和都会的销量与停业额用多页字段归并》。

 

 

二、Excel 分类汇总用公式

(一)按年汇总

1、假设要汇总各类服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net每一年的停业额。双击 F2 单位格,把公式 =SUM(($A$2:$A$25=F$1)*(YEAR($B$2:$B$25)=$E2)*$C$2:$C$25) 复制到F2,按 Ctrl + Shift + 回车,前往“羽绒服”2018 年的停业额 21232;把鼠标移到 F2 右下角的单位格添补柄上,鼠标变为玄色加号后,按住左键,往右拖,一向拖到 I2,则汇总出残剩服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net 2018 年的停业额;再把鼠标移到 I2 右下角的单位格添补柄上,一样方式往下拖,则汇总出 2019 年各类服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net的停业额;操纵进程步骤,如图6所示:

Excel 分类汇总用公式按年汇总

图6

 

2、公式 =SUM(($A$2:$A$25=F$1)*(YEAR($B$2:$B$25)=$E2)*$C$2:$C$25) 申明:

A、$A$2 表现对列和行都相对援用,当往右拖时,A2 不会变为 B2、C2 等;当往下拖时,A2 不会变为 A3、A4 等,别的的有两个 $ 的与$A$2 是一个意义。

B、$A$2:$A$25 以数组情势前往 A2 至 A25 的“服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net称号”,之以是 A2 和 A25 都用相对援用,是要确保往右或往下拖时,一直前往A2:A25 中的“服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net称号”。

C、$A$2:$A$25=F$1 为 Sum 的第一个前提,意义是把 A2:A25 中的每种“服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net称号”与 F1 中的“羽绒服”比拟,若是相称,前往 True,不然前往 False;第一次取 A2(即“羽绒服”),它们相称,前往 True;第二次取 A3(即“风衣”),它们不相称,前往 False;别的的以此类推,最初前往数组 {TRUE;FALSE;FALSE;...;FALSE;FALSE;TRUE}。

D、$B$2:$B$25 以数组情势前往 B2 至 B25 的日期。YEAR($B$2:$B$25) 用于取 B2:B25 日期中的年份;第一次取 B2(即 2018/11/1),YEAR(B2) 前往 2018;第二次取 B3(即 2018/11/2),YEAR(B3) 前往 2018;别的的以此类推,最初前往 {2018;2018;2018;...;2019;2019;2019}。

E、E2 中的值为 2018,YEAR($B$2:$B$25)=$E2 变为 {2018;2018;...;2019}=2018,而后顺次从数组中掏出每一个元素与 2018 比拟,若是相称,前往 True,不然前往 False,最初前往 {TRUE;TRUE;TRUE;...;FALSE;FALSE;FALSE}。

F、则公式变为 =SUM({TRUE;FALSE;FALSE;...;FALSE;FALSE;TRUE}*{TRUE;TRUE;TRUE;...;FALSE;FALSE;FALSE}*$C$2:$C$25),接着,把两个数组中对应元素相乘,相乘时,TRUE被转为 1、FALSE 被转为 0,则公式变为 =SUM({1;0;0;...;0;0;0}*$C$2:$C$25)。

G、$C$2:$C$25 为乞降地域,它以数组情势前往 C2:C25 中的停业额,即前往 {5221;5202;4622;...;5889;5198;5299}。

H、公式进一步变为 =SUM({1;0;0;...;0;0;0}*{5221;5202;4622;...;5889;5198;5299}),接着再把两个数组对应元素相乘,公式变为=SUM({5221;0;0;...;0;0;0}),最初对数组乞降,成果为 21232。

 

提醒:下面的公式也能够用 SumIfs、SumProduct 等函数完成,用这两个函数不需按“Ctrl + Shift + 回车”求值,只要按回车便可,之以是用 Sum 要按“Ctrl + Shift + 回车”,是由于公式为数组公式。把下面的公式改成用 SumProduct 完成,能够如许写: =SUMPRODUCT(($A$2:$A$25=F$1)*(YEAR($B$2:$B$25)=$E2)*$C$2:$C$25),用 SumIfs 完成见下面的按月汇总。

 

(二)按月汇总

1、以汇总每种服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net每一年每一个月的停业额为例。双击 G2 单位格,把公式 =SUM(($A$2:$A$25=G$1)*(YEAR($B$2:$B$25)=$E$2)*(MONTH($B$2:$B$25)=--SUBSTITUTE($F2,"月",""))*$C$2:$C$25) 复制到 G2,按 Ctrl + Shift + 回车,前往“羽绒服”2018 年 11 月的停业额;一样用往右的方式,前往 2018 年 11 月别的服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net的停业额,用往下拖的方式前往每种服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net 2018 年残剩月份的停业额;操纵进程步骤,如图7所示:

Excel 分类汇总用公式按月汇总

图7

 

2、公式 =SUM(($A$2:$A$25=G$1)*(YEAR($B$2:$B$25)=$E$2)*(MONTH($B$2:$B$25)=--SUBSTITUTE($F2,"月",""))*$C$2:$C$25) 申明:

A、公式与下面的“按年汇总”是一个意义,这里只是多加了一个“年份”的前提 YEAR($B$2:$B$25)=$E$2 和用 Substitute 函数代替月份中的“月”字,以获得数字用于计较。

B、--SUBSTITUTE($F2,"月","") 的意义是:用空 "" 代替 F2 中“11月”的“月”字,它前往文本 "11",再用 -- 把 "11" 转为数值 11。

 

3、以上公式也能够用 SumIfs 和 SumProduct 函数完成,它们的写法以下:

=SUMPRODUCT(($A$2:$A$25=G$1)*(YEAR($B$2:$B$25)=$E$2)*(MONTH($B$2:$B$25)=--SUBSTITUTE($F2,"月",""))*$C$2:$C$25)

=SUMIFS($C$2:$C$25,$A$2:$A$25,G$1,$B$2:$B$25,">="&DATE($E$2,SUBSTITUTE($F2,"月",""),0)+1,$B$2:$B$25,"<="&DATE($E$2,SUBSTITUTE($F2,"月","")+1,0))

 

这两个公式间接按回车便可。SumProduct 公式与 Sum 公式是一个意义,不再剖析,下面只剖析 SumIfs 公式:

A、$C$2:$C$25 为乞降地域;$A$2:$A$25,G$1 为第一个前提地域/前提对,$A$2:$A$25 为前提地域,G$1 为前提,意义是:在 A2:A25 中查找 G1 中的“羽绒服”。

 

B、$B$2:$B$25,">="&DATE($E$2,SUBSTITUTE($F2,"月",""),0)+1 为第二个前提地域/前提对,用于在 B2:B25 中查找大于即是 2018/11/1 的一切日期;SUBSTITUTE($F2,"月","") 用于用空 "" 代替 F2 中“11月”的“月”字,它前往文本 "11";

E2 为 2018,则 ">="&DATE($E$2,SUBSTITUTE($F2,"月",""),0)+1 变为 ">="&DATE(2018,"11",0)+1,接着 Date 函数把 "11" 转为数值 11,并前往 2018 年 11 月 0 天表现的序号 43404;

则 ">="&DATE(2018,"11",0)+1 变为 ">="&43404+1,再用 & 把 ">=" 与 43405 毗连起来,即 ">=43405",意义是大于即是 11 月的第一天,由于 2018 年 11 月的第 0 天为 2018 年 10 月 31 日,再加 1,刚好是 2018年 11 月 1 日。

 

C、$B$2:$B$25,"<="&DATE($E$2,SUBSTITUTE($F2,"月","")+1,0) 为第三个前提地域/前提对,与第二个前提地域/前提对是一个意义,用于在 B2:B25 中查找小于即是 2018/11/30 的所日期;

SUBSTITUTE($F2,"月","") 前往 "11",DATE($E$2,SUBSTITUTE($F2,"月","")+1,0) 变为 DATE(2018,"11"+1,0),进一步计较变为 DATE(2018,12,0),意义是 2018 年 12 月第 0 天,即 2018 年 11 月 30 日。

 

D、则公式变为 =SUMIFS($C$2:$C$25,$A$2:$A$25,G$1,$B$2:$B$25,">=2018/11/1",$B$2:$B$25,"<=2018/11/30"),意义是:汇总 A2:A25中为“羽绒服”与“日期”大于即是 2018/11/1 且小于即是 2018/11/30 的一切停业额。

 

 

(三)按周汇总

1、假设要把 11 月各类服装网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net网www.vhao.net的停业额按周汇总。把 11 月第一天的日期 2018/11/1 输出到 E1 单位格,双击 E2,把公式 =TEXT(WEEKNUM(E$1 + 7*(ROW(A1)-1),2)-WEEKNUM(--TEXT(E$1,"e-m"),2)+1,"第[dbnum1]0周") 复制到 E2,按回车,前往 11 月第一周;把鼠标移到 E2 右下角的单位格添补柄上,用往下拖的方式前往 11 月残剩的周数;双击 F2,把公式 =SUM((WEEKNUM(--B$2:B$17,2)=WEEKNUM(E$1+7*(ROW(A1)-1),2))*C$2:C$17) 复制到 F2,按 Ctrl + Shift + 回车,前往 11 月第一周的停业额,一样用往下拖的方式前往残剩周的停业额;操纵进程步骤,如图8所示:

Excel 分类汇总用公式按周汇总

图8

 

2、公式申明:

(1)=TEXT(WEEKNUM(E$1 + 7*(ROW(A1)-1),2)-WEEKNUM(--TEXT(E$1,"e-m"),2)+1,"第[dbnum1]0周")

A、ROW(A1) 用于 前往 A1 的行号 1,7*(ROW(A1)-1 用于完成每往下拖一个单位格,日期累加到下一周;当公式在 E2 时,7*(1-1)=0,当公式在 E3 时,A1 变 A2,ROW(A2) 前往 2,7*(2-1)=7;别的的以此类推。

 

B、WEEKNUM(E$1 + 7*(ROW(A1)-1),2) 用于前往指定日期在一年中是第几周,E$1 + 7*(ROW(A1)-1) 为日期,参数 2 表现以“周一”为每周的第一天;

以公式在 E2 为例:E1 为 2018/11/1,7*(ROW(A1)-1) 前往 0,则 WEEKNUM(E$1 + 7*(ROW(A1)-1),2) 变 WEEKNUM(E$1 + 0,2),它前往44,即 2018/11/1 是一年中的第 44 周。

 

C、e-m 表现以年代显现日期,e 表现年,相称于 yyyy;m 表现月,相称 mm;e 和 m 的地位能够更调;TEXT(E$1,"e-m") 表现以年代前往 E1 中的 2018/11/1,即前往 "2018-11";--"2018-11" 中 -- 表现把文本转为日期,它相称于 Value 函数。

D、则 WEEKNUM(--TEXT(E$1,"e-m"),2) 变为 WEEKNUM(--"2018-11",2),进一步计较前往 44。

E、"第[dbnum1]0周" 用于前往汉字表现的第几周,dbnum1 表现把数字转为汉字,0 表现把 [dbnum1] 显现为数字。别的,dbnum2 表现把数字转为大写汉字。

F、则公式变为 =TEXT(44-44+1,"第[dbnum1]0周"),进一步计较前往“第一周”;当公式在 E3 是,公式变为 =TEXT(45-44+1,"第[dbnum1]0周"),前往“第二周”,别的的以此类推。

 

(2)=SUM((WEEKNUM(--B$2:B$17,2)=WEEKNUM(E$1+7*(ROW(A1)-1),2))*C$2:C$17)

A、WEEKNUM(--B$2:B$17,2) 用于以数组情势前往 B2:B17 的日期在一年中是第几周;第一次掏出 B2(即 2018/11/1),WEEKNUM(--B2,2),前往 44;第二次掏出 B3(即 2018/11/2),WEEKNUM(--B3,2),前往 44;别的的以此类推,最初前往{44;44;44;44;45;45;45;45;45;45;46;46;46;46;46;46}。

B、WEEKNUM(E$1+7*(ROW(A1)-1) 下面已诠释过,当公式在 F2 时,它前往 44;当公式在 F3 时,它前往 45。

C、则公式变为 =SUM(({44;44;44;44;45;45;45;45;45;45;46;46;46;46;46;46}=44)*C$2:C$17),接着把数组中的每一个元素与 44 比拟,若是相称前往 True,不然前往 False。

D、则公式变为 =SUM({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}*C$2:C$17),进一步计较,把 C2:C17 中的每一个数值与数组中的对应元素相乘,相乘时,TRUE 和 FALSE 的取值,下面已诠释。

E、公式进一步变为 =SUM({5221;5202;4622;5140;0;0;0;0;0;0;0;0;0;0;0;0}),最初对数组乞降,最初前往 20185。

F、当公式在 F3 时,公式变为 =SUM(({44;44;44;44;45;45;45;45;45;45;46;46;46;46;46;46}=45)*C$2:C$17),刚好是数组中一切为 45 的前往 True,也便是对第二周的停业额乞降。

别的,Sum 公式能够改成用 SumProduct 完成,公式能够如许写:=SUMPRODUCT((WEEKNUM(--B$2:B$17,2)=WEEKNUM(E$1+7*(ROW(A1)-1),2))*C$2:C$17)。