excel33

题目

2.将一串数字改为“yyyy-mm-dd”的日期格式

2.①将日期转换为“yyyy-mm-dd”形式的日期格式

（注意不能直接改数字格式，会导致无效❗）

选中日期（不要标题）→数据→分列→3个下一步，再将“常规”改为“日期”

然后20160421变成2016/4/21

右键修改格式，yyyy/m/d修改为yyyy-mm-dd

②在“收支分类”列，限制F5:F92单元格区域只能输入“收支分类”序列中的内容。在该单元格的空白单元格输入“费用”

第一小问：选中F5:F92区域→数据→数据有效性→允许“任何值”改为“序列”；来源：=收支分类

第二小问：选中区域→开始→查找和选择→定位条件→勾选：空值→在第一个空值单元格，填上“费用”，再按ctrl+enter

③在D列“账户余额”中通过公式“本行余额=上行余额+本行收入-本行支出”计算每行的余额（除第四行外）

选中B列“支出”B5:B92→数据→分列

再选中C列“收入”C5:C92→数据→分列

再账户余额D5输入    =d4+c5-b5  ,再双击填充整列

④设置数字格式

⑤套表格格式

⑥在最下方添加汇总行，分别计算本年的收入和支出总额，其他列不进行任何计算

添加汇总行---------套用表格格式完后→设计→勾选：汇总行→支出、收入单元格下拉列表选择求和；将收支分类删除

3.在2017，2018年，按照下列要求对数据表进行完善

示例：2017年的做法，2018年同样如此。

①将日期设置为yyyy-mm-dd

不知道为什么这个可以直接更改格式，不是像前面那个还需要数据分类再更改格式。

②将上年度的余额填入D4单元格中，分别计算每行的账户余额填入D列

选中2016年的账户余额即D92复制，将其粘贴【右键，数值粘贴】在2017年的账户余额D4中

选中B列“支出”B4:B350→数据→分列

选中C列“收入”C4:C350→数据→分列

再账户余额D5输入  =d4+c5-b5  ,再双击填充整列

（❗注意在D5输入之前，一定要将左边两列分列，不然D5输入完，数据无效）

③套用表格格式（❗注意要和之前的不一样）

④添加汇总行-----方法同上

⑤分别将数据区域以外的行列隐藏

选中G列，"ctrl+shift+→ "再右键隐藏

选中352行，"ctrl+shift+↓ "再右键隐藏

⑥分别将B4单元格上方及左侧行列锁定，令其总是可见

选中B4单元格→视图→冻结窗口→冻结拆分窗口

4.以2016，2017，2018年三张工作表为数据源，在工作表“数据分类”中完成以下各项统计工作

示例：2016年

①不显示工作表”分类统计“的网格线

点击“分类统计”→视图→勾选网格线即取消

②按照”收支分类“列中的分类，统计更年的员工费用、产品销售收入及采购成本-----sumifs函数

计算2016年的工资及奖金：运用sumifs函数

第一行：（支出列）2016年的B5:B92

第二行：（收支分类列）2016年的F5:F92

第三行：C4

=SUMIFS('2016年'!B5:B92,'2016年'!F5:F92,C4)

B5:B92还有F5:F92进行绝对引用

=SUMIFS('2016年'!$B$5:$B$92,'2016年'!$F$5:$F$92,C4)

C5填充完右拉至F5

因此示例

2016：=SUMIFS('2016年'!$B$5:$B$92,'2016年'!$F$5:$F$92,C4)

2017：=SUMIFS('2017年'!$B$5:$B$350,'2017年'!$F$5:$F$350,C4)

2018：=SUMIFS('2018年'!$B$5:$B$219,'2018年'!$F$5:$F$219,C4)

③sum函数

④按照公式“毛利润=销售收入-采购成本”计算各年的毛利润

先得计算出销售成本，即类同②的做法（不同点：收入改为支出）

销售收入：运用sumifs函数

第一行：（收入列）2016年的C5:C92

第二行：（收支分类列）2016年的F5:F92

第三行：H4

再绝对引用

=SUMIFS('2016年'!$C$5:$C$92,'2016年'!$F$5:$F$92,H4)

采购成本：运用sumifs函数

第一行：（收入列）2016年的B5:B92

第二行：（收支分类列）2016年的F5:F92

第三行：I4

示例：

2016年：=SUMIFS('2016年'!$B$5:$B$92,'2016年'!$F$5:$F$92,I4)

2017年：=SUMIFS('2017年'!$B$5:$B$350,'2017年'!$F$5:$F$350,I4)

2018年：=SUMIFS('2018年'!$C$5:$C$219,'2018年'!$F$5:$F$219,H4)

⑤插入迷你图，插入线条颜色为红色，标记为绿色的折线图

❗注意要在插入迷你图的单元格点击去插入，不能点击数据再点插入图，反了

第一问：插入迷你图的单元格点击→插入→迷你图→折线图→选中数据一列的区域（不要包括总和）

第二问：设计→迷你图颜色，标记颜色（再点标记）

5.以2016，2017，2018年3张工作表为数据源，参考文档的示例，自新工作表A3单元格开始生成数据透视表。

新建工作表，命名为“透视分析”

然后在A1单元格用快捷键alt+d+p调出透视表向导→选中“多重合并计算数据区域”→自定义字段

→选定区域：2016表A3:F92→添加；页字段数目：1；字段1：2016年

→再选中区域→选定区域：2017表A3:F350→添加；页字段数目：1；字段1：2017年

→再选中区域→选定区域：2017表A3:F219→添加；页字段数目：1；字段1：2018年

→现有工作表A3

（注意：直接按alt+d+p是调不出的
网传：按住Fn+alt不放,然后点一下D,松开D,再点一下P,松开P ,最后松开Fn+Alt

实操另外方便的方法：按住Alt不放，在按D，松开两个按钮，单独按P）

①要求统计各年每个年度的收支总和，只有列合计

设计→总计→仅对列启用

→在B3单元格→下拉按钮→取消全选，只勾选“收入”和“支出”

→在B5单元格→右键→值汇总依据→求和

→在A4单元格→下拉列表→日期筛选→介于→输入2016/1/1    2018/12/31

→在A7单元格→右键创建组→取消月，选择季度和年→确定

→在A7单元格→右键→数据透视表选项→布局和格式→勾选”合并且居中“

                                                               →显示→取消”显示“，勾选”经典透视“

设计→分类汇总→在组的底部显示

根据图例修改单格内容

将A1修改为年度，A3改为收支统计，A4改为年份，B4改为季度，A8改为“2016年  求和”

选择C5:D19→右键设置格式（数值2位数，千位符）

选中当前透视表→插入柱状图→点击收入→右键→设置数据系列格式→次坐标轴

→点击收入→右键→更改系列图表类型→选择带有标记的折线图（折线图第4个）

分析→字段→按钮→全部隐藏

布局→网格线→主要横网格线→无

选择左侧的数据→右键→设置坐标轴格式→主要刻度类型：无【右侧的数据也是这样更改的】

布局→图例→其他图例选项→图例选项改为右上

设计→更改样式，更改为与样例一致的（选择6行第2个，即样式42）

②行列标题应与示例相同，不显示展开/折叠按钮，筛选项要包含3个年度

③设置收入和支出的数字格式（保留二位小数，千字符），适当改变透视表样式

④生成透视图，图例位于右上角，要求图表类型、图表样式与示例相同，图表中不显示字段按钮

完

-2020.1.17