英文:
Excel formula, count occurrences in the first week
问题
我需要找到一种方法，在数据透视表中显示特定产品在其首周出现的次数。以下是表格模型。这并不是那么简单，但是是一个有效的示例，我大约有20万行数据（我不能使用SQL或任何其他软件）。
| 收到日期 | 周 | 年 | 产品 | 工厂 | 制造日期 | 过期日期 |
|---|---|---|---|---|---|---|
| 2022/01/12 | 48 | 2022 | Y | A | 2022/10/10 | 2023/10/10 |
| 2023/02/06 | 22 | 2023 | X | A | 2022/10/10 | 2023/10/10 |
| 2023/03/06 | 22 | 2023 | X | A | 2022/10/10 | 2023/10/10 |
| 2023/10/06 | 23 | 2023 | X | A | 2022/10/10 | 2023/10/10 |
| 2023/10/06 | 23 | 2023 | X | A | 2022/10/10 | 2023/10/10 |
| 2023/06/22 | 25 | 2023 | Y | A | 2022/10/10 | 2023/10/10 |
在透视表中，应该是这样的，当我点击数字2或1时，它应该列出所有出现的情况：
| 制造 | 产品 | 制造日期 | 首周出现次数 |
|---|---|---|---|
| A | X | 2022/10/10 | 2 |
| A | Y | 2022/10/10 | 1 |
我知道如何找到第一次出现的情况；它会是这样的：
在特定列中，使用以下公式进行连接：
=CONCATENATE('产品' & '工厂' & '制造日期' & '过期日期')
然后在另一个单元格中，使用以下公式来计算连接的公式，并检查是否是第一次出现：
=IF( COUNT.IF( '连接列这里'：'连接列这里' ), { '连接列这里' ) > 1 ), { "多于1次出现" ), { '第一次出现' )
我真的在尝试找到一种方法，只计算特定产品的最早周数，但我找不到方法。
英文:
I need to find a way to display in a pivot table how many occurrences of specific product that happened in his first week of appearance. Below is the table's model. It's not that simple, but a valid example, I have something around 200k rows (I can't use SQL or any other software).
| Date Received | Week | Year | Product | Factory | Manufactury Date | Expiration Date |
|---|---|---|---|---|---|---|
| 01/12/2022 | 48 | 2022 | Y | A | 10/10/2022 | 10/10/2023 |
| 02/06/2023 | 22 | 2023 | X | A | 10/10/2022 | 10/10/2023 |
| 03/06/2023 | 22 | 2023 | X | A | 10/10/2022 | 10/10/2023 |
| 10/06/2023 | 23 | 2023 | X | A | 10/10/2022 | 10/10/2023 |
| 10/06/2023 | 23 | 2023 | X | A | 10/10/2022 | 10/10/2023 |
| 22/06/2023 | 25 | 2023 | Y | A | 10/10/2022 | 10/10/2023 |
It should be something like this on pivot table, and when I click on number 2 or 1 it should list all occurrences:
| Manufactury | Product | Manufactury Date | occurrences first week |
|---|---|---|---|
| A | X | 10/10/2022 | 2 |
| A | Y | 10/10/2022 | 1 |
I know how to find the first occurrences; it would be something like this:
In a specific column a formula for concatenate:
=CONCATENATE('Product' & 'Factory' & 'Manufactury Date' & 'Expiration Date')
And in another cell a formula to count the concatenate formula and check if it is the first occurrences:
=IF(
COUNT.IF(
'CONCATENATECOLUMNHERE': 'Z LINE OF CONCATENATECOLUMNHERE'
),
{ 'Z LINE OF CONCATENATECOLUMNHERE'
) > 1
),
{ "MORE THAN 1 OCCURRENCE"
),
{ 'FIRST OCCURRENCE'
)
I'm really trying to find a way to count only the oldest week for a specific product, but I can't find a way.
答案1
得分: 4
以下是翻译好的部分：
在数据透视表中，没有直接计算这个的方法。
您可以在源数据中添加一个字段来检查该行是否在产品的前7天内：
我使用了一个数组公式创建了字段 test，但我相当肯定在 Excel 365 上更容易实现：
=IF(AND(A2>=MIN(IF($D$2:$D$7=D2;$A$2:$A$7));A2<=(MIN(IF($D$2:$D$7=D2;$A$2:$A$7))+6));1;0)
因为这是一个数组公式，必须按 CTRL+ENTER+SHIFT 输入，否则它将无法工作！
有了这个新字段，然后在数据透视表中将其用作筛选器：
更新： 当 OP 说“首次出现的第一周”时，意味着日期的自然周的第一周，而不是连续的7天。所以更新后的数组公式如下：
=IF(YEAR(A2)&WEEKNUM(A2;2)=YEAR(MIN(IF($D$2:$D$7=D2;$A$2:$A$7)))&WEEKNUM(MIN(IF($D$2:$D$7=D2;$A$2:$A$7));2);1;0)
这个公式将比较每个“收到日期”的年份和周数与每个产品的最小“收到日期”的年份和周数。然后按照之前的方法制作数据透视表。
用 03/06/2023 替换 05/06/2023 进行测试（这两个日期在不同的周）：
请注意，我使用 WEEKNUM 将星期一设置为每周的第一天，但您可以根据需要进行更改。
此外，检查年份值是强制性的，因为不同的年份可以具有相同周数的相同日期，但它们实际上不是相同的周。
英文:
There is no way you can calculate that directly in the Pivot table itself.
You could add a field on your source data to check if that row would be inside the first 7 days of a product:
I did field test using an array formula but I'm pretty sure it's easier on Excel365:
=IF(AND(A2>=MIN(IF($D$2:$D$7=D2;$A$2:$A$7));A2<=(MIN(IF($D$2:$D$7=D2;$A$2:$A$7))+6));1;0)
Because it's an array formula it must be entered pressing <kbd>CTRL</kbd>+<kbd>ENTER</kbd>+<kbd>SHIFT</kbd> or it won't work!
With this new field then use it as a filter in your pivot table:
UPDATE: When OP said first week of appearance meaned the first natural week of the date, not a period of 7 days. so the updated array formula is:
=IF(YEAR(A2)&WEEKNUM(A2;2)=YEAR(MIN(IF($D$2:$D$7=D2;$A$2:$A$7)))&WEEKNUM(MIN(IF($D$2:$D$7=D2;$A$2:$A$7));2);1;0)
This formula will compare the year and week number of each Date received versus the year and week number of the minimun Date received for each product. Then make the pivot table as explained before.
Test replacing 03/06/2023 with 05/06/2023 (both dates are on different weeks):
Notice I'm using WEEKNUM setting Monday as first day of week, but you can change this based on your needs.
Also, it's mandatory to check the year value because different years can have same date in same week number, but it wouldn't be really the same week.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论