英文:
is there an easier way to write this formula
问题
I have to report daily data, and everyday I have to create a new sheet with today's date.
I have a summary sheet in the end which shows the sum for all the days.
I have this formula in my summary sheet
=SUM(('Shift 1 (May 2)'!AY142:AY145),('Shift 1 (May 3)'!AY142:AY145),('Shift 1 (May 4)'!AY142:AY145),('Shift 1 (May 5)'!AY142:AY145),('Shift 1 (May 6)'!AY142:AY145))
Now, I have to edit this every day because a new sheet is created and it takes me 30-45 mins trying to edit the summary shit per day.
Is there a faster way to do this?
英文:
I have to report daily data, and everyday I have to create a new sheet with today's date.
I have a summary sheet in the end which shows the sum for all the days.
I have this formula in my summary sheet
=SUM(('Shift 1 (May 2)'!AY142:AY145),('Shift 1 (May 3)'!AY142:AY145),('Shift 1 (May 4)'!AY142:AY145),('Shift 1 (May 5)'!AY142:AY145),('Shift 1 (May 6)'!AY142:AY145))
Now, I have to edit this every day because a new sheet is created and it takes me 30-45 mins trying to edit the summary shit per day.
Is there a faster way to do this?
答案1
得分: 4
可能的解决方案可以是创建一个包含所有所需引用的表格，动态生成。将此粘贴到新的工作表中：
| 单词 1 | ID 1 | 日期 | 工作表名称 | 列 1 | 行 1 | 列 2 | 行 2 | 完整地址 | 总和 | 总和总计 |
|---|---|---|---|---|---|---|---|---|---|---|
| 班次 | 1 | 2023/05/01 | =A2&" "&B2&" ("&PROPER(TEXT(C2,"mmm"))&" "&TEXT(C2,"dd")*1&")" | AY | 142 | AY | 145 | ="'"&D2&"'!"&E2&F2&":"&G2&H2 | =SUM(INDIRECT(I2)) | =SUM(J:J) |
| 班次 | 1 | 2023/05/02 | =A3&" "&B3&" ("&PROPER(TEXT(C3,"mmm"))&" "&TEXT(C3,"dd")*1&")" | AY | 142 | AY | 145 | ="'"&D3&"'!"&E3&F3&":"&G3&H3 | =SUM(INDIRECT(I3)) | |
当然，这仍然有点复杂，我怀疑报告本身（甚至源数据）可能以其他方式改进。
英文:
A possbile solution might be creating a table with all the needed reference produced dynamically. Paste this in a new sheet:
| Word 1 | ID 1 | Date | Sheet name | Column 1 | Row 1 | Column 2 | Row 2 | Complete address | Sums | Total of sums |
|---|---|---|---|---|---|---|---|---|---|---|
| Shift | 1 | 2023/05/01 | =A2&" "&B2&" ("&PROPER(TEXT(C2,"mmm"))&" "&TEXT(C2,"dd")*1&")" | AY | 142 | AY | 145 | ="'"&D2&"'!"&E2&F2&":"&G2&H2 | =SUM(INDIRECT(I2)) | =SUM(J:J) |
| Shift | 1 | 2023/05/02 | =A3&" "&B3&" ("&PROPER(TEXT(C3,"mmm"))&" "&TEXT(C3,"dd")*1&")" | AY | 142 | AY | 145 | ="'"&D3&"'!"&E3&F3&":"&G3&H3 | =SUM(INDIRECT(I3)) | |
Of course it's still kind of intricate and i suspect that the report itself (or even the source data) might be improved in other ways.
答案2
得分: 2
使用表格范围来重写公式是一种方法（请注意，并非所有公式都适用于表格范围），而不是使用单独的工作表名称。例如，使用以下公式：'Shift 1(May 2):Shift 1 (May 6)'!AY142:AY145，而不是您的公式。例如，您有Sheet1!A1:B1，Sheet2!A1:B1和Sheet3!A1:B1，您希望在Summary!A1中进行求和：
使用带有表格范围的公式=SUM(Sheet1:Sheet3!A1:B1)：
请记住，如果您添加新的工作表并且不想重写公式，您需要将该工作表添加到Sheet1和Sheet3之间。如果您需要保持这些工作表按顺序排列，您可以使用空的辅助工作表，并将其保留在工作表列表的末尾：
因此，下次您必须插入新的工作表时，请将其放在此示例中的Sheet7之前：
然而，我强烈建议重新考虑如何制作报表工作表，以便您可以将所有数据放在一个工作表中，并使用数据集工作表编写引用该工作表的汇总报表，或者使用数据透视表。
英文:
One of the ways would be to rewrite formulas using sheet range (note that not all formulas work with sheet range) instead of using separate sheet names. For example use 'Shift 1(May 2):Shift 1 (May 6)'!AY142:AY145 instead of your formula. For example you have Sheet1!A1:B1, Sheet2!A1:B1 and Sheet3!A1:B1 you want to SUM in Summary!A1:
Use formula with sheet range =SUM(Sheet1:Sheet3!A1:B1):
Keep in mind, that if you add new Sheet and don't want to rewrite formulas, you need to add that Sheet between Sheet1 and Sheet3. In case you need to keep those sheets to be sorted, you can use empty helper Sheet and keep it at the end of sheet list:
So next time you have to insert new sheet, put it before Sheet7 in this case:
However I strongly recommend to reconsider how report sheets are made so you can have all data in one sheet and create summary report using Pivot table or writing formulas referring to that one dataset sheet.
答案3
得分: 0
我建议首先使用PowerQuery来 consDataPowerQuery 具体来提取汇总信息ksets。 PowerQuery允许您通过使用某种名称模式（比如 'Data-230517'）来动态汇总数据，然后只需要刷新查询，它将自动调整以包括具有相同名称模式的新工作表。
如果您对PowerQuery不熟悉，那么可以使用Leila Gharani的这个优秀教程：
英文:
I suggest using PowerQuery to first consolidate the data, and then extract the summary information from the consolidated data set. PowerQuery allows you to dynamically consolidate data through sheets using a certain name pattern (say 'Data-230517'). You then just need to refresh the query and it will adjust automatically to include new sheets with the the same name pattern.
There's a learning curve if you're not familiar with PowerQuery, but you can use this excellent tutorial from Leila Gharani:
Consolidate & Clean Multiple Excel Sheets in One Pivot Table
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论