美观又实用 打造有数据才显示的图表

比如下图是某公司不同区域的銷售月报表，由于每个区域上报数据的时间并不同步，现在希望只显示有数据的月份的条形图，并在添加新的月份数据后自动增加对应的条形图（图1）。操作如下：

比如东南区目前只有1～5月份的销售数据，那么就只显示这5个月份的条形图。

1. 制作销售区域下拉选择列表

定位到G1单元格，依次点击“数据→数据验证→设置→序列”，选择B1：E1数据区域作为验证列表，这样在G1单元格中可以通过下拉列表选择不同的销售区域（图2）。

2. 使用HLOOKUP函数引用对应的销售数据

在G1单元格中选择不同的销售区域后，我们需要在G列引用对应的销售数据。在G2单元格中输入公式“=IF（HLOOKUP（$G$1，$B$1：$E$13，ROW（A2），0）=""，""，HLOOKUP（$G$1，$B$1：$E$13，ROW（A2），0））”，下拉填充到G13单元格（图3）。

公式解释：

先使用HLOOKUP函数引用数据，其中“$G$1”（绝对引用）作为要查找的值，查找的区域是“$B$1：$E$13”，查找范围的行号是“ROW（A2）”（表示第2行，下拉公式后会依次变为第3行、第4行……），参数“0”表示精确匹配。然后将查找结果作为IF函数的判断依据，如果未找到结果（即销售数据为0）就显示为空，反之则显示找到的销售数据。

3. 建立自定义公式

对于有销售数据区域的引用需要借助自定义公式来完成。依次点击“公式→定义名称→定义名称”，在打开的对话框中，在“名称”处输入“区域”、“引用位置”处输入“=OFFSET（Sheet1！$G$2，0，0，COUNT（Sheet1！$G：$G））”，点击“确定”按钮即可完成公式的建立（图4）。

公式解释：

先使用COUNT函数对G列中有销售数据的单元格计数，接着将结果作为OFFSET函数引用的行号，即G列中有几个月份的销售数据，那就引用几行。

“Sheet1！$G$2”（即G2单元格）是OFFSE T函数的引用基准。“0，0”表示向下偏移0行，向右偏移0列，引用COUNT统计的行数。如果G列中没有销售数据，那么就不再引用，从而实现只引用有销售数据的单元格。

操作同上，继续新建一个名为“月份”的自定义公式，在“引用位置”处输入“=OFFSET（Sheet1！$A$2，0，0，COUNT（Sheet1！$G：$G））”。公式的含义同上，即只在A列引用G列中有销售数据的月份。

4. 插入图表

按住C t r l 键并分别选中A1： A13、G1：G13数据区域，依次点击“插入→ 图表→ 条形图”，按提示插入一个条形图，可以看到，没有销售数据的月份会以空白的形式显示在条形图的上方（图5）。

5. 应用自定义公式

在上述条形图的任意空白处右击并选择“选择数据”，在打开的窗口中点击“图例项（系列）”下的“编辑”按钮，然后在“系列值”处输入“=Sheet 1！ 区域”，这里的“区域”就是图4中建立的自定义公式的名称（图6）。

继续在图6所示的窗口中点击“水平（分类）轴标签”下的“编辑”按钮，然后在“轴标签”处输入“=Sheet 1！ 月份”。最后依次点击“确定”按钮保存退出，这样有销售数据的月份才会显示条形图。

6. 为图表添加标题

定位到I1单元格并输入公式“=G1&"区1-"&COUNT（Sheet1！ $G：$G）&"月销售展示"”，接着点击图表的标题区域，在公式栏中输入“=Sheet1！$I$1”，即引用I1单元格中的内容作为图表的标题（图7）。

7. 美化图表

最后对图表美化，如取消网格线的显示、勾选坐标轴的“逆序类别”等即可。以后在原始数据区域中添加新的月份数据后，会自动增加新的条形图，图表的标题也会随之同步发生变化（图8）。