范圍 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(VLOOKUP(A2:A&" "&$A$1,{EAR_DATES_RNG&" "&EAR_NAMES_RNG,EAR_AMOUNTS_RNG},2,false),"")), IF(COUNT(A2:A)=0,1,COUNT(A2:A)),1)
我怎么能告訴 VLOOKUP 忽略月份中的哪一天？
或者，當我在公式中創建陣列時，也許有一種方法可以使用 EOMONTH() 技巧以某種方式將用戶輸入日期轉換為月份的第一天，但??是也許我使用命名范圍的事實會給我帶來另一個問題克服。
但是，我已經可以看到一個解決方案，該解決方案需要一個幫助列來轉換 EAR_DATES_RNG 中的所有日期，然后公式使用該轉換后的范圍創建陣列，但我想盡可能避免使用幫助列。
感謝我能得到的任何幫助！
截圖示例：
輸出：
[
輸入：
一月和三月用戶輸入不使用當月的第一天，而二月和四月使用。因此，在第一張圖片中，C 列中的一月和三月的值是空白的，而二月和四月的值卻出現了。
我的 VLOOKUP() 位于第一張影像的 C 列中，并且應該獲取第二張影像中 G 列的相應值。
uj5u.com熱心網友回復：
嘗試：
=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)
uj5u.com熱心網友回復：
這是您想要的功能的腳本版本。
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://developers.google.com/apps-script/reference/utilities/utilities#formatDate（日期，字串，字串）
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/481882.html
上一篇：如何為一組表撰寫通用觸發器？
下一篇：星期數中的Sumif日期