用查询（）中的月份名称替换月号

发布于 2025-01-27 08:04:08 字数 1451 浏览 2 评论 0原文

我使用月份功能在Google表中按月按月进行分组数据。但是，它返回从0-11起的月号，我更喜欢看到月份的名称。

这是查询

=QUERY(DataAllMonths!A1:B, "SELECT MONTH(A), COUNT(A) WHERE LOWER(B) LIKE '%no empathy%' GROUP BY MONTH(A) LABEL MONTH(A) 'Month', COUNT(A) 'Total'", True)

以下返回以下内容：

我最终写了一个客户功能以从查询中获取结果并替换了数字的月份名称

=returnMonthName(QUERY(DataAllMonths!A1:B, "SELECT MONTH(A), COUNT(A) WHERE LOWER(B) LIKE '%no empathy%' GROUP BY MONTH(A) LABEL MONTH(A) '', COUNT(A) ''", True), 1)

，这显示了以下结果

脚本

function returnMonthName(data, col) {
  // var data = [[1, 100], [2, 200], [3, 300]];
  // var col = 1;
  var months = [ "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" ];
  var retArray = [["Month","Total"]];
  var total = 0;
  // var retArray = [];
  var n = 0;
  var dataLen = data.length;
  for (n = 0; n < dataLen; n++) {
    retArray.push([months[data[n][0]], data[n][1]]);
    total+= data[n][1];
  }
  retArray.push(["Total", total]);

  return retArray;
}

这是我认为可以使用自定义功能的来执行此操作，也许是使用数组公式。有人有解决方案吗？

原文

I am grouping data by month in a Google Sheets QUERY using the MONTH function. However, it returns the month number from 0-11 and I prefer to see the month names.

Here is the QUERY

=QUERY(DataAllMonths!A1:B, "SELECT MONTH(A), COUNT(A) WHERE LOWER(B) LIKE '%no empathy%' GROUP BY MONTH(A) LABEL MONTH(A) 'Month', COUNT(A) 'Total'", True)

This returns something like the following:

I ended up writing a customer function to take the results from the QUERY and substitute the month names for the numbers

=returnMonthName(QUERY(DataAllMonths!A1:B, "SELECT MONTH(A), COUNT(A) WHERE LOWER(B) LIKE '%no empathy%' GROUP BY MONTH(A) LABEL MONTH(A) '', COUNT(A) ''", True), 1)

And this shows the following result

Here is the script

function returnMonthName(data, col) {
  // var data = [[1, 100], [2, 200], [3, 300]];
  // var col = 1;
  var months = [ "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" ];
  var retArray = [["Month","Total"]];
  var total = 0;
  // var retArray = [];
  var n = 0;
  var dataLen = data.length;
  for (n = 0; n < dataLen; n++) {
    retArray.push([months[data[n][0]], data[n][1]]);
    total+= data[n][1];
  }
  retArray.push(["Total", total]);

  return retArray;
}

I believe there is a way to do this without depending on a custom function, perhaps using an array formula. Does anybody have a solution.

Here is a link to a sample sheet.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

还在原地等你 2025-02-03 08:04:08

仅通过公式，尝试

=QUERY({arrayformula(eomonth(DataAllMonths!A1:A,0)),DataAllMonths!B1:B}, "SELECT (Col1), COUNT(Col1) WHERE LOWER(Col2) LIKE '%no empathy%' GROUP BY (Col1) LABEL (Col1) 'Month', COUNT(Col1) 'Total'", True)

并将列的格式更改为mmm

参考

eomonth

By formula only, Try

=QUERY({arrayformula(eomonth(DataAllMonths!A1:A,0)),DataAllMonths!B1:B}, "SELECT (Col1), COUNT(Col1) WHERE LOWER(Col2) LIKE '%no empathy%' GROUP BY (Col1) LABEL (Col1) 'Month', COUNT(Col1) 'Total'", True)

and change format of column to MMM

reference

EOMONTH

回复收藏 0 原文

梦里人 2025-02-03 08:04:08

尝试以下操作：

function lfuncko(m) {
  return [...Array.from(Array(12).keys(), x => Utilities.formatDate(new Date(2022, x, 15), Session.getScriptTimeZone(), "MMM"))][m]
}

Try this:

function lfuncko(m) {
  return [...Array.from(Array(12).keys(), x => Utilities.formatDate(new Date(2022, x, 15), Session.getScriptTimeZone(), "MMM"))][m]
}

回复收藏 0 原文

~没有更多了~