vba Excel指定月份上报数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9425559/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel to specify the month to report the data
提问by user1199080
Using excel, is it possible to specify the month to report in a cell?
使用excel，是否可以在单元格中指定要报告的月份？
For example, excel would need to report the latest reported sales, if its available. If the month of February sales is not available, then it will report January sales. Likewise, if March sales is available, it would report the sales in March.
例如，excel 需要报告最新报告的销售额（如果有）。如果 2 月份的销售额不可用，则它将报告 1 月份的销售额。同样，如果有 3 月份的销售额，它将报告 3 月份的销售额。
How do you write this formulae in a cell in excel? If its not possible to write such a formuale, can we write a vba to do this?
excel中的单元格怎么写这个公式？如果不可能写出这样的公式，我们可以写一个 vba 来做到这一点吗？
采纳答案by user1199080
I found this answer:
我找到了这个答案：
=IF(B12<>0,B12,IF(B11<>0,B11,IF(B10<>0,B10,IF(B9<>0,B9,IF(B8<>0,B8,IF(B7<>0,B7,IF(B6<>0,B6,IF(B5<>0,B5,IF(B4<>0,B4,IF(B3<>0,B3,IF(B2<>0,B2,IF(B1<>0,B1,0))))))))))))
This formulae solves the problem of picking up the latest available data.
这个公式解决了获取最新可用数据的问题。
回答by ABS
If you mean you have a list of sales entries and want the sales for the last month in the data, you could write one formula to get the latest month. If months are in A you could write: =month(max(A1:A99999))
如果您的意思是您有一个销售条目列表并希望数据中包含上个月的销售额，您可以编写一个公式来获取最近一个月的销售额。如果月份在 A 你可以写：=month(max(A1:A99999))
Note if you have multiple years data, you need to pull the year too or get bounding dates. You can then sum sales using an array formula like assuming the result of the first action is in D1 and sales are in B: {=sum(if(month(A1:A999999)=D1,B1:B999999,0))}
请注意，如果您有多年数据，您也需要提取年份或获取边界日期。然后，您可以使用数组公式对销售额求和，例如假设第一个操作的结果在 D1 中，销售额在 B 中：{=sum(if(month(A1:A999999)=D1,B1:B999999,0))}
If you have incremental updates during the month, this will return month to date data. If you need data for the last full month, you can use another array formula to get the maximum month where the date is the last day of the month.
如果您在本月有增量更新，这将返回本月至今的数据。如果您需要最后一个完整月的数据，您可以使用另一个数组公式来获取最大月份，其中日期是该月的最后一天。
回答by barry houdini
If the cells for future data in B1:B12 are blank then you just need to extract the last number in B1:B12 don't you? If so then try
如果 B1:B12 中未来数据的单元格为空，那么您只需要提取 B1:B12 中的最后一个数字，不是吗？如果是这样，那么尝试
=LOOKUP(9.99E+307,B1:B12)
=LOOKUP(9.99E+307,B1:B12)
回答by Robert Mearns
You can use the OFFSETfunction to select specific data based on the month.
您可以使用OFFSET功能来选择基于月份的特定数据。
Insert a formula to calculate the current month (or just type in the month number)
插入一个公式来计算当前月份（或只输入月份数）
=MONTH(TODAY())
The formula to select the current months of sales data
选择当月销售数据的公式
=SUM(OFFSET(B1,MONTH(TODAY()),0,1,1))
Have a look at the help in Excel on the OFFSET function. It can be used to select a range of data. For example, to return data from the start of the year to the current month the formula would be:
查看 Excel 中有关 OFFSET 函数的帮助。它可用于选择数据范围。例如，要返回从年初到当月的数据，公式为：
=SUM(OFFSET(B1,1,0,MONTH(TODAY()),1))
If the current month does not contain data yet, you can test to see if a value is returned and revert to the prior month if there is no data.
如果当月还没有数据，你可以测试看看是否有返回值，如果没有数据则恢复到上个月。
=IF(SUM(OFFSET(B1,B16,0,1,1))=0,SUM(OFFSET(B1,B16-1,0,1,1),SUM(OFFSET(B1,B16,0,1,1))))
