总和单元格范围被合并值过滤掉了吗? [英] Sum cell range filtered by merged value?

问题描述

我有一个常用格式的保龄球结果表，其中每个系列都包含一行实际得分，而低于该行的累计得分(向右递增).对于每次保龄球，我都有保龄球馆的日期和名称，对于每个系列，都有序列号.显示两个保龄球比赛的示例(由于我很懒，所以得分相同):

  |   A   |   B   |   C   | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z |
 1| Date  | Hall  |Series |   1   |   2   |   3   |   4   |   5   |   6   |   7   |   8   |   9   |  10   |Extra  |Sum|
 2|       |       |   1   | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 |       |120|
 3|       |       |       |   9   |  14   |  36   |  53   |  60   |  69   |  74   |  96   |  113  |  120  |       |   |
 4| 140113| CBH   |   2   | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 |       |120|
 5|       |       |       |   9   |  14   |  36   |  53   |  60   |  69   |  74   |  96   |  113  |  120  |       |   |
 6|       |       |   3   | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 |       |120|
 7|       |       |       |   9   |  14   |  36   |  53   |  60   |  69   |  74   |  96   |  113  |  120  |       |   |
 8|       |       |   1   | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 |       |120|
 9|       |       |       |   9   |  14   |  36   |  53   |  60   |  69   |  74   |  96   |  113  |  120  |       |   |
10| 140425| Bowly |   2   | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 |       |120|
11|       |       |       |   9   |  14   |  36   |  53   |  60   |  69   |  74   |  96   |  113  |  120  |       |   |
12|       |       |   3   | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 |       |120|
13|       |       |       |   9   |  14   |  36   |  53   |  60   |  69   |  74   |  96   |  113  |  120  |       |   |

现在，我想使用公式对日期为140425(YYMMDD)的保龄球比赛所有系列的分数求和.

我该怎么做?

请注意，日期放置在覆盖该保龄球时段所有行的合并单元格中，即，对于第一个会话，合并单元格为A2:A7，对于第二个会话为A8:A13.我知道我需要引用该范围内的第一个单元格以获取该值，但是我将如何在SUM.IF(...)公式中做到这一点，或者为每个分数线找到要引用的正确单元格呢?

每个会话的系列数可以从一个到最多.

解决方案

尽管未在OP中显示，但似乎A2:A7和A8:A13已合并.在SE的某个地方，有人按照创建魔鬼来尝试超越我们的忍耐力"的方式描述了合并的单元格-我尊重的所有建议都说，最好避免充满活力地进行合并.上面是IMO很好的例子，说明合并的单元格可能导致不成比例的问题.

• 取消合并每组合并的单元格

选择合并范围，然后依次选择"HOME">"Alignment"，-Merge&中心.
应该在合并的每个范围的顶部单元格中显示所需的值.

• 填空

选择ColumnA，HOME，编辑，-Find&选择转到特殊"，选中空白(仅)"，然后单击确定.
=，Up， Ctrl + Enter .

• 选择字体颜色

选择工作表，数据>排序并添加筛选器-筛选器.
对于ColumnB，选择(Blanks)(仅).
选择ColumnA并应用字体颜色以匹配背景.

• 继续使用SUMIF公式.

I have a bowling result sheet in usual format, where each series contains one row for the actual scores and below that the accumulated score so far, incrementing to the right. For each bowling session I have the date and the name of the bowling hall, and for each series the series number. Example showing two bowling sessions (with identical scores because I'm lazy):

  |   A   |   B   |   C   | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z |
 1| Date  | Hall  |Series |   1   |   2   |   3   |   4   |   5   |   6   |   7   |   8   |   9   |  10   |Extra  |Sum|
 2|       |       |   1   | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 |       |120|
 3|       |       |       |   9   |  14   |  36   |  53   |  60   |  69   |  74   |  96   |  113  |  120  |       |   |
 4| 140113| CBH   |   2   | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 |       |120|
 5|       |       |       |   9   |  14   |  36   |  53   |  60   |  69   |  74   |  96   |  113  |  120  |       |   |
 6|       |       |   3   | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 |       |120|
 7|       |       |       |   9   |  14   |  36   |  53   |  60   |  69   |  74   |  96   |  113  |  120  |       |   |
 8|       |       |   1   | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 |       |120|
 9|       |       |       |   9   |  14   |  36   |  53   |  60   |  69   |  74   |  96   |  113  |  120  |       |   |
10| 140425| Bowly |   2   | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 |       |120|
11|       |       |       |   9   |  14   |  36   |  53   |  60   |  69   |  74   |  96   |  113  |  120  |       |   |
12|       |       |   3   | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 | 5 | 4 | 0 | 5 |10 |   |10 |   | 2 | 5 |       |120|
13|       |       |       |   9   |  14   |  36   |  53   |  60   |  69   |  74   |  96   |  113  |  120  |       |   |

Now, I'd like to sum the scores for all series from the bowling session on date 140425 (YYMMDD), using a formula.

How would I do that?

Please note that the date is placed in a merged cell covering all rows for that bowling session, i.e. for the first session the merged cells are A2:A7 and for the second session A8:A13. I know I need to reference the first cell in the range to get the value, but how would I do that in a SUM.IF(...) formula, or otherwise find the right cell to reference for each score line?

The number of series per session can vary from one and up.

解决方案

Although not shown as such in the OP, it would appear that A2:A7 and A8:A13 have been merged. Somewhere on SE someone described merged cells along the lines of "a creation of the Devil sent to try us beyond endurance" - all advice I respect says merging is best avoided with vigour. The above is IMO a good example of the disproportionate problems merged cells can cause.

• Unmerge each set of merged cells

Select merged range, HOME > Alignment, -Merge & Center.
Should show required values in the top cell of each range that was merged.

• Fill blanks

Select ColumnA, HOME, Editing, -Find & Select, Go To Special, check Blanks (only), OK.
=, Up, Ctrl+Enter.

• Select for font colour

Select sheet, DATA > Sort & Filter, -Filter.
For ColumnB select (Blanks) (only).
Select ColumnA and apply font colour to match background.

• Proceed with SUMIF formula.

这篇关于总和单元格范围被合并值过滤掉了吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！