使用日期作为搜索键（但忽略一天）的vlookup

Question

A2范围：A包含每个月使用本月第一天的几个月。输入数据时，允许用户在该月内的任何一天输入。用户输入日期的范围由命名范围“ ear_dates_rng”定义。每个月都有相应的该月收益摘要。

因此，这里有一个可能的示例：

User Input:
1/11/2017 - $100
2/9/2017 - $250
3/21/2017 - $500

Summary:
1/1/2017 - $100
2/1/2017 - $250
3/1/2017 - $500

以前，我有一张注释，迫使用户也可以使用本月的第一天进行输入，但是我想释放它。但是，这导致我的vlookup（）停止工作，因为它以前使用了摘要日期来查找相应的用户输入：

= array_constrain（arrayformula（ifError（ifeREROR（vlookup（a2：a＆amp;“”＆amp; $ a $ a $ 1 $ 1 ，{ear_dates_rng＆amp;“”＆amp; ear_names_rng，ear_amounts_rng}，2，false），“”）），if（count（a2：a）= 0,1，count（a2：a2：a），1），1），

我可以告诉Vlookup忽略本月的一天？

或者，当我在公式中创建数组时，有一种方法可以以某种方式使用eomonth（）技巧将用户输入日期转换为本月的第一个，但是我使用命名范围的事实可能会给我带来另一个问题克服。

但是，我可以看到已经需要一个辅助列的解决方案，该解决方案可以转换ear_dates_rng中的所有日期，然后使用该公式使用该转换范围来创建数组，但是如果可能的话，我想避免使用助手列。

感谢我能得到的任何帮助！

屏幕截图示例：

输出：

[

一月和三月的用户输入不使用本月的第一天，而二月和四月则不使用。因此，在第一张图像中，一月和三月的值在C列中为空白，而2月和4月则出现。

我的vlookup（）在第一个图像的C列中，应该在第二张图像中抓住列的相应值。

Answer 1

尝试：

=ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(VLOOKUP(TEXT(A2:A, "m/1/e")*1&" "&$A$1, 
 {EAR_DATES_RNG&" "&EAR_NAMES_RNG, EAR_AMOUNTS_RNG}, 2, 0))), 
 IF(COUNT(A2:A)=0, 1, COUNT(A2:A)), 1)

或：

=ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(VLOOKUP(A2:A&" "&$A$1, 
 {TEXT(EAR_DATES_RNG, "m/1/e")*1&" "&EAR_NAMES_RNG, EAR_AMOUNTS_RNG}, 2, 0))), 
 IF(COUNT(A2:A)=0, 1, COUNT(A2:A)), 1)

Answer 2

这是您想要的函数的脚本版本。

function firstOfTheMonth() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  //Gets the values of column A until the last row with data
  var range = sheet.getRange(2,1, sheet.getLastRow() - 1, 1);

  //Changes the date values of column A into MM-YYYY-1
  // You need to define your TimeZone on Utilities.formatDate(new Date(y), "YOUR TIMEZONE HERE", "MM-1-yyyy")
  var val = range.getValues();
  var newval = val.map(x => x.map(y => Utilities.formatDate(new Date(y), "GMT+8", "MM-1-yyyy")));

  console.log(newval);
  range.setValues(newval); 
}

您可以在OneDit触发器上手动添加该功能，以便每当在电子表格上进行编辑时，该功能就会自动运行。

之前：

​：

https：// https：// https：// developers.google.com/apps-script/reference/utilities/utilities#formatdate(Date,String，String）