excel 一旦建立格式，就可反复套用

Excel函数 2021-11-17 14:20:13

从材料数据中制作用函数统计的格式是最好的方法。一旦做成这种格式，之后只要将材料数据粘贴到固定位置就能够完成表格。

在表格中，首先在单元格 J6中输入以下汇总公式：

=SUMIF(数据加工!$K:$K,$A6&$B6&J$5&J$4,数据加工!$E:$E)

在单元格 J6中输入=SUMIF(数据加工!$K:$K,$A6&$B6&J$5&J$4,数据加工!$E:$E)

在第二参数中，结合了以下4个单元格的数值。

在单元格 J5中实际上输入的是“2009”这个数值。同样，单元格 K5里也输入了“2001”这个数值。但是，单元格上显示的却是“2009年”，后缀有“年”这个字。在【设置单元格格式】➛【表示格式】的【自定义】中，在【种类】的编辑栏中输入以下内容，就能够在单元格中显示“年”这个字。

0"年"

但是，实际上在单元格中输入的是“2009”、“2010”这样的数值。而前文中的函数的第二参数就变为了“啤酒北海道20091Q”的字符串。

第一参数指定的是“数据加工”工作表中的 K 列，这一列中组合了种类、分公司、年度、季度这4个字符串。也就是说，这个公式在进行这样的处理，

将这个 J6中的公式直接从 J6复制到 K14后，就能统计出各分公司的年度销售额。

将单元格 J6的公式一直复制到 K14

这是将最开始单元格 J6中的公式一直复制粘贴到 J6：K14的范围后，例如，选择单元格 K9，按F2 键后，就会变成下表中的状态。

单元格 K9中含有 SUMIF 函数

第二参数引用了4个单元格的 SUMIF 函数，但 A9、K4这两个单元格看上去引用的是空白单元格。这里其实使用了一个秘诀。

下图是选中 A 列的状态。选中后，各个种类中的“啤酒”、“发泡酒”不仅直接出现在单元格中，同时也以白色文字的状态存在于 A 列空白位置的单元格中。

在 A 列中的文字变为白色

当然，这些文字是作为 SUMIF 的参数输入到单元格中的，但如果所有的单元格中都出现这些文字也会让人无法看懂表格中的内容，所以只保留一个单元格中的文字，将其他单元格中的相同文字的字体颜色设置为白色。在第4行中才采用了相同的处理方法。

接下来，用 SUMIF 函数统计出总和，并在同比率的单元格中加入今年÷去年的除法公式，就可以得出同比率。

这时，在有同比率的表格中事先设置“数值小于100%时单元格内文字变红且加粗”这样的格式，就可以像下图中这样自动判定计算结果并改变文字格式。

通过附带条件格式将同比率的数值小于100%的单元格中的文字设置成红字且加粗

然后，按照以下的操作顺序在其他单元格中填入公式，即可完成表格。

➊ 选中啤酒的第一季度（1Q）范围，按Ctrl +C 复制。

➋ 指定粘贴的范围。

➌ 按Ctrl +V 粘贴。

如此一来，只要在一开始制作自动汇总表的格式，就能多次利用，也就不用再重复相似的统计工作了。通过这个机制，只要在“数据加工”工作表的特定位置粘贴元数据，函数就会自动计算数据，表格也就制作完成了。

这样，1分钟就能完成原本需要2小时才能完成的工作。如果是使用数据透视表的话，即使做到天黑，制作好的资料也可能会有错误，根本无法使用。