奇数项求和公式_Excel公式求和奇数或偶数周

最新推荐文章于 2024-04-18 08:42:51 发布

culiao2169

最新推荐文章于 2024-04-18 08:42:51 发布

阅读量3.6k

点赞数

文章标签： excel python java 数据分析大数据

原文链接：https://contexturesblog.com/archives/2020/04/30/excel-formula-to-sum-for-odd-or-even-weeks/

版权

奇数项求和公式

Here's an Excel formula challenge for you, based on an email question that someone sent to me. They wanted help with an Excel formula to sum for odd and even weeks. See my solution, and let me know how you'd solve the problem.

根据某人发送给我的电子邮件问题，这是您面临的Excel公式挑战。他们希望获得有关Excel公式的帮助，以求奇数和偶数周的总和。查看我的解决方案，让我知道您将如何解决问题。

为什么总和是奇数，甚至是几周？ (Why Sum Odd or Even Weeks?)

Have you ever needed to add numbers, based on odd and even numbers in another column?

您是否曾经需要根据另一列中的奇数和偶数来添加数字？

In this example, the person worked in a factory, where there are 2 work crews – Crew A and Crew B. They need to compare the production quantities for the 2 work crews.

在此示例中，此人在一家工厂工作，那里有2名工作人员- 机组A和机组B。 他们需要比较两个工作人员的生产数量。

Here's a simplified version of the production data, which we'll use for this challenge. As you can see, the crew name is not in the data.

这是生产数据的简化版本，我们将用它来应对这一挑战。如您所见，数据中没有机组人员名称。

This is the morning data from the first 4 weeks of the year.

这是一年前4周的早晨数据。

识别机组人员 (Identify the Crews)

Even though the crew name is not listed, we can use the week numbers to identify which crew was working.

即使没有列出机组人员的姓名，我们也可以使用周号来标识正在工作的机组人员。

Crew A works mornings on odd-numbered weeks
机组A在奇数周的早晨工作
Crew B works mornings on even-numbered weeks
机组B在偶数周的早晨工作

The production data doesn't show the crew name, but we can total the odd or even week data, to get each crew's total quantity.

生产数据没有显示机组人员的姓名，但是我们可以合计奇数或偶数周数据，以获得每个机组人员的总数。

方程式挑战赛 (Formula Challenge)

Your challenge is to create a formula to calculate the total quantities for odd and even weeks

您面临的挑战是创建一个公式来计算奇数周和偶数周的总量

You can type the data in a blank workbook, or download my sample file with the challenge data and all the solutions that are shown below.

您可以在空白工作簿中键入数据，或下载包含挑战数据和下面显示的所有解决方案的示例文件。

您有什么解决方案？ (What's Your Solution?)

You can see my solution in the next section, and below that, you'll see solutions from my weekly Excel newsletter readers.

您可以在下一节中看到我的解决方案，在其下，您将看到每周的Excel新闻快讯读者的解决方案。

What pros and cons can you see in the other people's solutions?

您在其他人的解决方案中可以看到哪些利弊？

If you found a different solution than the ones shown here, let me know in the comment section.

如果您找到的解决方案与此处显示的解决方案不同，请在评论部分告知我。

我的解决方案 (My Solution)

Here's my formula to sum for odd or even weeks. I used the SUMPRODUCT function, combined with ISODD and ISEVEN.

这是我求和奇数或偶数周的公式。我将SUMPRODUCT函数与ISODD和ISEVEN结合使用。

I put this formula in cell E2, to total the odd weeks:

我将此公式放在单元格E2中，以得出奇数周总数：

=SUMPRODUCT((ISODD(--($B$2:$B$11))) *($A$2:$A$11))
= SUMPRODUCT((ISODD(-($ B $ 2：$ B $ 11)))*($ A $ 2：$ A $ 11))

And here's the formula in cell E3, to total the even weeks:

这是单元格E3中的公式，总共为偶数周：

=SUMPRODUCT((ISEVEN(--($B$2:$B$11))) *($A$2:$A$11))
= SUMPRODUCT((ISEVEN(-($ B $ 2：$ B $ 11)))*($ A $ 2：$ A $ 11))

这个怎么运作 (How It Works)

Here's how my formula works:

这是我的公式的工作原理：

The ISODD and ISEVEN functions return TRUE or FALSE
ISODD和ISEVEN函数返回TRUE或FALSE
The two minus signs (double unary) convert those T/F values to numbers (-1 or 0)
两个减号(双一进制)将这些T / F值转换为数字(-1或0)
Those results are multiplied by the Qty amounts
这些结果乘以数量
The SUMPRODUCT function returns the total of all those multiplications.
SUMPRODUCT函数返回所有这些乘法的总和。

There are more Sumproduct examples on my Contextures website.

我的Contextures网站上有更多Sumproduct示例 。

其他解决方案 (Other Solutions)

Next, here are some of the formulas that newsletter readers sent to me. You can get the data, and see all of the solutions in the Formula Challenge sample file.

接下来，这是时事通讯读者发送给我的一些公式。您可以获取数据，并在Formula Challenge示例文件中查看所有解决方案。

MOD功能 (MOD Function)

Many of the solutions used the MOD function, to check if the week numbers were odd or even.

许多解决方案使用MOD功能来检查星期数是奇数还是偶数。

The MOD function returns the remainder when you divide the first number (week number) by the second number (2)
当您将第一个数字(周数)除以第二个数字(2)时，MOD函数将返回余数
If you divide an even number by 2, the remainder will be zero
如果将偶数除以2，则余数将为零
If you divide an odd number by 2 the remainder will be 1
如果将奇数除以2，则余数将为1

For example, to find the total for odd weeks:

例如，要查找奇数周的总数：

=SUMPRODUCT((MOD($B$2:$B$11,2)=1)*$A$2:$A$11)

= SUMPRODUCT(( MOD($ B $ 2：$ B $ 11,2)= 1 )* $ A $ 2：$ A $ 11)

SUM或SUMPRODUCT (SUM or SUMPRODUCT)

All the solutions used SUM or SUMPRODUCT to calculate the grand total.

所有解决方案都使用SUM或SUMPRODUCT来计算总计。

For example, to find the total for even weeks:

例如，要查找偶数周的总数：

=SUM(MOD($B$2:$B$11-1,2)*$A$2:$A$11)

= SUM(MOD($ B $ 2：$ B $ 11-1,2)* $ A $ 2：$ A $ 11)

NOTE: Array Formulas

注意：数组公式

In older versions of Excel, you'll need to array-enter the SUM formulas, with Ctrl+Shift+Enter.
在旧版Excel中，您需要使用Ctrl + Shift + Enter 数组输入SUM公式 。
If your version of Excel has Dynamic Array formulas, you won't need to do that -- just press Enter.
如果您的Excel版本具有动态数组公式，则无需这样做-只需按Enter。

过滤功能 (FILTER Function)

Two of the solutions use the new FILTER function. It filters a set of numbers, based on a rule.

其中两个解决方案使用新的FILTER函数。它根据规则过滤一组数字。

=SUM(FILTER($A$2:$A$11, MOD($B$2:$B$11,2)))

= SUM( FILTER ($ A $ 2：$ A $ 11，MOD($ B $ 2：$ B $ 11,2)))

In these solutions, the quantities were returned, based on using the MOD function in the week number column.

在这些解决方案中，基于周数列中的MOD函数返回了数量。

You can use these solutions if your version of Excel has Dynamic Array formulas.

如果您的Excel版本具有动态数组公式，则可以使用这些解决方案。

硬编码值 (Hard-Coded Values)

In some solutions, including my original solution, there were hard-coded values. For example, the Odd week formulas used a 1, and the Even week formulas used a zero.

在某些解决方案中，包括我的原始解决方案，都有硬编码的值。例如，奇数周公式使用1，而偶数周公式使用0。

=SUMPRODUCT((MOD($B$2:$B$11,2)=1) *$A$2:$A$11)
= SUMPRODUCT((MOD($ B $ 2：$ B $ 11,2) = 1 )* $ A $ 2：$ A $ 11)
=SUMPRODUCT((MOD($B$2:$B$11,2)=0) *$A$2:$A$11)
= SUMPRODUCT((MOD($ B $ 2：$ B $ 11,2) = 0 )* $ A $ 2：$ A $ 11)

You could put those values in adjacent cells, and refer to those cells in the formula.

您可以将这些值放在相邻的单元格中，然后在公式中引用这些单元格。

=SUMPRODUCT((MOD($B$2:$B$11,2)=D2)*$A$2:$A$11)

= SUMPRODUCT((MOD($ B $ 2：$ B $ 11,2) = D2 )* $ A $ 2：$ A $ 11)

Then, drag down, to use the same formula in both calculations. I like to use consistent formulas wherever possible!

然后，向下拖动以在两个计算中使用相同的公式。我喜欢尽可能使用一致的公式！

Here's how I should have written my original solutions, to make it the same in both cells.

这是我应该编写原始解决方案的方式，以使其在两个单元中都相同。

In cell D2, type TRUE, and in D3, type FALSE
在单元格D2中，键入TRUE，在D3中，键入FALSE
In cell E2, enter this formula, then copy it down to cell E3
在单元格E2中，输入此公式，然后将其复制到单元格E3中

=SUMPRODUCT((ISODD(--($B$2:$B$11))=D2) * ($A$2:$A$11))

= SUMPRODUCT((ISODD(-($ B $ 2：$ B $ 11))= D2)*($ A $ 2：$ A $ 11))

硬编码阵列 (Hard-Coded Array)

One solution used hard-coded arrays of odd and even numbers.

一种解决方案是使用奇数和偶数的硬编码数组。

=SUM(SUMIFS($A$2:$A$11,$B$2:$B$11,{1,3}))
= SUM(SUMIFS($ A $ 2：$ A $ 11，$ B $ 2：$ B $ 11 ， {1,3} ))
=SUM(SUMIFS($A$2:$A$11,$B$2:$B$11,{2,4}))
= SUM(SUMIFS($ A $ 2：$ A $ 11，$ B $ 2：$ B $ 11， {2,4} ))

That worked alright in this small sample, with only 4 work weeks, but you'd need a flexible solution for larger samples.

在这个只有4个工作周的小样本中，这种方法可以正常工作，但是您需要一个灵活的解决方案来处理较大的样本。

助手列 (Helper Columns)

A few people used one or two helper columns in their solutions. You can see those in the sample file, and I've colour coded the columns, to match the solutions in which they're used.

少数人在解决方案中使用了一两个辅助列。您可以在示例文件中看到这些内容，并且已经对这些列进行了颜色编码，以匹配使用它们的解决方案。

Helper cells or columns are useful in some situations, allowing you to break a complex formula into smaller sections that are easier to understand or troubleshoot.

辅助单元格或列在某些情况下很有用，可让您将复杂的公式分解为较小的部分，以便于理解或排除故障。

But in these solutions, you'd need formulas in every data row, instead of a just 2 formulas at the top of the worksheet.

但是在这些解决方案中，您需要在每个数据行中使用公式，而不是在工作表顶部仅使用2个公式。

That's not too bad in a small file like this one, but could really slow things down if you're working with thousands of rows of data.

在像这样的小文件中，这还不错，但是如果您要处理成千上万的数据行，则确实可能使速度变慢。