第二课
- 一、字段自动组合
- - (一)日期型字段组合
- 按周组合时的注意事项
- - (二)数值字段
- - (三)文本字段
- 二、函数创建辅助列组合
- - (一)按上、中、下旬 组合
- - (二)特殊月份组合
- - (三)不等 步长数值组合(间隔不同时)
- 三、常见组合错误及排查
- - (一)**有空白单元格**
- - (二)**有文本型日期**
- - (三)**数值不能求和**
一、字段自动组合
- (一)日期型字段组合
- 操作:在透视表里单击任意一个日期,右键—>组合—>选择需要的组合,可多选
按周组合时的注意事项
- 对于跨度小的日期,如何组合成周
- 同样右键>>组合>>选择日>>天数7
- 注意:每个月第一天≠每周的第一天,时间起始需要重新设置
- (二)数值字段
数据一般按区间来组合,例:
- 创建透视表>>将数值字段放在行字段>>单击任意数值右键>>组合>>选择合适的起始值,间隔,如下:
- (三)文本字段
- 直接选中所有要组合的文本字段,右键组合,生成新字段之后重命名即可。分布杂乱的先拖到一起。
- 例:将下图的六个产品再分“日常用品”和“户外用品”两大类,如图:
- 操作:
二、函数创建辅助列组合
- (一)按上、中、下旬 组合
如图
- 思路:
- 在日期(A列)右侧新建一个“旬”列,按日期计算出上中下旬,用if函数
- 前10天是上旬;日期里“日”小于11的为上旬。那么剩余的小于21的为中旬;最后剩余的是下旬。
- 所以公式为: =IF(DAY(A2)<11,“上旬”,IF(DAY(A2)<21,“中旬”,“下旬”))
- 得到旬之后再插入数据透视表,自然就会分为上中下旬。
- (二)特殊月份组合
- 假如统计的日期 不是以每月第一天起始,例如1月5号~2月4号 算第一个月,依次类推去组合。
- 那么就将日期调整为正常的,补全差值。这里加个辅助列,公式:=原来的日期(A2)减5;有了新的日期再去插入透视表,右键按月组合
- (三)不等 步长数值组合(间隔不同时)
- 在某些区间需要特别细分时,用IF函数:
- 例:B列是全班语文成绩,卷子简单,高分的太多,为了看看拔尖的有多少。
于是老师按 0-70,71-90,91-95,96-100分档。 - 那么公式为:
- =IF(B2>95,“96-100”,IF(B2>90,“91-95”,IF(B2>70,“71-90”,“0-70”)))
- 或者反过来:=IF(B2<70,“0-70”,IF(B2<90,“71-90”,IF(B2<95,“91-95”,“96-100”)))
- 更多:=IF(B2<50,“无可救药”,IF(B2<80,“还有得救”,“是个人才”))
三、常见组合错误及排查
有以下几种情况是无法组合的:
- (一)有空白单元格
- 有合并的单元格时,只算第一行,剩下的都算空值。
那么需要填充,少量的可以Ctrl拖拽填充。多的时候操作如下:
a. 先选整列,定位条件 >>>空值,这样选中所有空白单元格,取消合并,输入=↑,Ctrl按住回车,就填充好了
b. 最好是,选中整列复制,再右键选择性粘贴 为数值。这样避免顺序变动导致带公式的数据错乱。
- (二)有文本型日期
可以通过分列来强制改变整列的单元格格式:选中整列>>>分列>>>完成(默认会改成常规格式)
- (三)数值不能求和
原因:数值里有文本
a.利用分列转换,直接完成
b.文本型数字一般会有个绿标,没有的话双击就会出现;Ctrl+shift +下箭头 全选,点左边的感叹号>>>>转换为数字