问题[google-sheets](coding)

我正在尝试创建一个包含学生考试成绩的数据表。有多个表格的考试成绩需要根据某个范围（例如：通过、不通过、警告）进行条件格式化。但是，分数线每年都不一样，我需要手动更新每个公式。

为了解决必须返回并更改每个公式的问题，我想在 Google 表格中创建一个可重复使用的条件格式规则，引用来自单独表格的分数范围，以避免跨多个分数表进行手动更新。

我知道我需要使用 INDIRECT 来引用另一张表，但我不确定应该使用什么公式来使范围起作用而不必列出所有可能的测试分数。

我在这里对我的问题做了一个简化的模型：示例表

这可以在 Google Sheets 中实现吗？

我在 Google 表格中有一个数据集，其中日期出现在A 列中，值（如项目名称）出现在B 列中。

数据如下：

我想将项目名称水平转置到每个日期旁边-
这意味着每个日期的对应值将分布到C、D、E 列等。

预期结果：

我尝试使用FILTER、TRANSPOSE和等公式ARRAYFORMULA，
但只能获得第一次约会的结果或不完整的结果。

我怎样才能像预期结果一样动态填充所有行？

最好使用公式，而不是脚本或手动数据透视表。

我试过了，但这个公式不准确

=IF(A2<>"", TRANSPOSE(FILTER(B$2:B, MMULT(N(ROW(A$2:A$1000)<=ROW(A2))*(A$2:A<>""), SEQUENCE(COLUMNS(B$2:B),1,1,0))=1)), "")

提前致谢！

我尝试突出显示(C) 列和(D)列中任何值重复的整行。本质上，每个代理（D 列）都应该有一个唯一的日期范围（C 列）。C 列和 D 列的组合应该始终唯一，并且应该突出显示/标记。

在条件格式中，我们创建了一个自定义公式来完成此任务。但是，当添加下面的公式时，它会突出显示整个工作表选项卡

=(COUNTIF($A$2:$D$5000, $C2)>1)*(COUNTIF($A$2:$D$5000, $D2)>1)

在下图中，我提供了一个示例，其中 C 列和 D 列中包含重复条目，应该突出显示。

我最近看到了一篇关于这个错误的帖子，因为无法重现该错误，所以帖子被关闭了。我昨晚也遇到了同样的错误，而且很容易重现。我不确定这两者之间有什么联系，但以下是我遇到的情况。

我试图确定可到达的最远行。例如，即使你的工作表只有默认的 1000 行，你也可以写入=ROW(OFFSET(A1,9999,))reach A10000。它会返回 10000。经过反复试验，我发现可以使用嵌套的 来获取最远行OFFSET。

=row(offset(offset(offset($A$1,B1,),C1,),D1,))

最后可到达的行是第 行2,147,483,647。如果我尝试转到2,147,483,649或更远，就会出现OFFSET 计算结果为超出范围的错误。但就在那之前，我发现了一个虫洞。没错，我尝试2,147,483,648使用到达第 行=row(offset(offset(offset($A$1,700000000,),700000000,),747483647,))，结果返回的是-2,147,483,648。您可以在下面看到结果（E 列包含用于启用或禁用公式的复选框）。

它就像虫洞一样，通向纸张宇宙的另一边。我可以在那一行左右移动，甚至可以回到这一边，但我无法在那一边上下移动任何行。确实很奇怪，但没什么危害，我打算报告。

然而，后来当我尝试打开我的个人插件时，出现了以下运行时错误。异常：访问 ID 为 #### 的文档时，服务电子表格失败。[行：77，函数：myGlobalConfig，文件：core/lib/Config。

在费尽心思寻找问题根源之后，我突然冒出个念头，想看看虫洞是不是问题根源。结果也确实如此。我激活那个特定公式时收到了错误提示。禁用那个公式后，问题就消失了。

我甚至在绑定脚本中创建了一个基本函数，用于检索工作表的名称并使用 Toast 显示它。当激活中间那个公式时，出现了相同的错误，得出了负行的值。关闭后，错误就消失了。

这与之前提到的另一篇文章有​​联系吗？有人知道为什么这一行是负数吗？

我有一个column A看起来像：

11

11 11
5 6
5 5
5 6 6 5
11 11 11 11

6 7 8
3
3 2 3

并寻求一种替代的、更智能的方式来输出TRUE/FALSE如下所示：

逻辑是：

• FALSE如果

  • 单元格为空
  • 每个单元格没有重复的数字

• TRUE如果

  • 单元格只有一个数字
  • 单元格中有两个或多个重复的数字，无论顺序如何

更多注释：

• 数字以空格分隔
• 数字范围从 1 到 80
• 每个单元格最多可容纳 40 个以空格分隔的数字
• 每个单元格至少可以容纳 1 个数字或为空

我该如何解决：

我可以对它求和（以各种方式 - LAMBDA，，，...）并用计数除以它。如果结果是整数则是，如果结果有小数则是，QUERY(SUBSTITUTE(但这对不起作用，。SUM(SPLIT(TRUEFALSE3 2 36 7 8

另一种方法是将其拆分，然后使用QUERY和 计算每行的数字，按 row_numbers 分组，按 all_numbers 进行透视，LET然后将其包装到 中IF，并删除小于 的数字2。接下来，将其与TRIM(TOCOL(QUERY(TRANSPOSE(和LEN合并。然后， IF将其拆分>0为 和TRUE，并添加 未涵盖的其余逻辑QUERY。

无论哪种方式，公式都会非常庞大​​，所以我正在寻找一些“更智能/更短”的方法（如果有的话）。也许有一些我不熟悉的神奇正则表达式模式？

我想统计过去 x 天内某个特定值的数量，比如我的情况是 0 的数量。而且这应该动态地进行，因为每天都会添加数据。这就是为什么我认为我必须创建依赖于日期值的公式。

截至目前，我无法提供带有示例的工作表，但示例应该相当简单。A 列包含日期， B 列包含数字，范围在 0-1000000 之间。我想统计从今天开始过去 7 天内的 0 项。

我尝试使用的最后一个是：

=COUNTIFS(B4:B975;A4:A975;"<="&TODAY();A4:A975;">="&TODAY()-7;"0")

我也尝试了一下，但总是报错，提示里面的值要么大小不同，要么必须成对出现。我正在用类似的函数来处理 AVERAGEIFS，它运行完美，所以我想只要稍微调整一下，也可以把它用在 COUNTIFS 上，但我想事情没那么简单。

我目前正在使用

=COUNTIFS($A$4:$A$975;">="&TODAY()-7;$B$4:$B$975;"0")

但这是一个错误的公式，因为它也将未来全部计算为 0，而且我也没有成功地调整这个公式，使它像我所解释的那样工作。

例如，如果我有一张这样的表：

我希望能够查找“Copper”，并获取其对应的两个索引（P2，R2）。我需要能够分别获取它们，所以可能需要一个公式获取“P”，另一个公式获取“R”。据我所知，在 Google Sheets 中获取数字和字母索引，然后从中获取索引“名称”可能是最好的选择。

我试过 vlookup、hlookup、query、match、index 以及其他任何我能在网上找到的、哪怕是略微相关的函数，但都没成功。我发现所有这些函数的问题是，它们一次只能搜索一行或一列，这意味着我最多需要至少 20 个不同的公式才能逐行搜索。

感谢大家的帮助！

我正在尝试创建一个 Google 表格，以便可以安排车间服务日历。

我已按照以下教程操作，但遇到了一些错误，希望有人能够轻松识别错误。

https://www.youtube.com/watch?v=fAfomDR0pe4&list=PL3ooKofg5LEr7yYPz7DyReKrAhhBecfUv

出现这个错误，我尝试查找原因，但一头雾水。我已将开始时间和结束时间格式化为“日期时间”，并尝试删除所有单元格，只保留我输入的内容。我想可能是我输入的范围有误？我不确定……

异常：参数（String、String、null、（class））与 CalendarApp.Calendar.createEvent 的方法签名不匹配。

脚本代码如下：

let calendarId = '[email protected]'; // add your calendar id here

// if your events are coming through but the time is off, click on the gear icon to the left and make sure the time zone matches the time zone setting on your Google Calendar

// on open script menu
function onOpen() {
    let ui = SpreadsheetApp.getUi();
    ui.createMenu("Chur Bro").addItem("Update Calendar", "updateCalendar").addToUi();
}

// main function will irterate through data and determine if we are adding, updating or deleting an event.
function updateCalendar() {
    let ss = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = ss.getSheetByName("Service Register");
    let events = sheet.getRange(9,13,sheet.getLastRow()-1,24).getValues();

    for (i = 0; i < events.length; i++) {
        let event = events[i];
        let returnedData;

        if (event[0] != '') {
            switch (event[0]) {
                case 'Add':
                    returnedData = addEvent(event)
                    break;
                case 'Update':
                    returnedData = updateEvent(event);
                    break;
                case 'Delete':
                    returnedData = deleteEvent(event);
                    break;
            }
            sheet.getRange(i + 9, 21, 9, ).setValues([returnedData]);
        }
    }
    sheet.getRange(9, 13, sheet.getLastRow()-1, 13).clearContent();
}


function addEvent(event) {

  let title = event[17];
  let description = event[18];
  let location = event[19];
  let emails = event[20];
  let startTime = event[23]
  let endTime = event[24]
  let options = {};

  if (description != '') { options['description'] = description; }
  if (location != '') { options['location'] = location; }
  if (emails != '') { options['guests'] = emails; }

  let calendar = CalendarApp.getCalendarById(calendarId);
  let id = calendar.createEvent(title, startTime, endTime, options).getId();
  return ["Added", id];

}




function updateEvent(event) {

    let title = event[17];
    let description = event[18];
    let location = event[19];
    let emails = event[20];
    let eventId = event[22];
    let startTime = event[23];
    let endTime = event[24];

    let calendar = CalendarApp.getCalendarById(calendarId);
    let thisEvent = calendar.getEventById(eventId);
    thisEvent.setTime(startTime, endTime)
        .setTitle(title)
        .setDescription(description)
        .setLocation(location);

    if (emails != '') {
        emails = emails.split(",");
        emails.forEach(x => thisEvent.addGuest(x));
    }
    return ["Modified", eventId];
}




function deleteEvent(event) {

    let eventId = event[22];
    let calendar = CalendarApp.getCalendarById(calendarId);
    let thisEvent = calendar.getEventById(eventId);
    thisEvent.deleteEvent();
    return ['Deleted', ''];

}

Google Sheet 的图片：

Google 表格

谷歌表格图片

如何在 A1、B1、C1 和 D1（分别）处使用数组公式来提供所需的输出列，如图所示。

Google 表格链接

注意： “主标题”（亲戚、朋友、商业朋友）和“姓名”之间的区别在于“主标题”在开始和结束处有 1 个空格字母。

A、B、C 列三者相同，唯一的区别在于“主标题”A 列提供字母，B 列提供数字，C 列提供罗马数字。

序列编号条件：
（1）序列编号完全基于列：NAMES 和 PAX。
（2）如果 PAX 为 0，则输出为空白。

对于 A、B、C 列：
当 PAX 为空白时：
-> 如果它是“主标题”，则输出为字母 OR
-> 如果它是“名称”，则输出为空白。

对于 D 列：
当 PAX 为空白时：
-> 如果它是“主标题”，则输出为空白 OR
-> 如果它是“名称”，则输出为序列号，直到下一个“主标题”，然后再次从 1 重新开始，直到下一个“主标题”等等。

编辑：
列名称和 PAX 是输入，其余的是所需的输出。

数据：

在AI列中有一排复选框，在I列中，复选框要么是“不打印”，要么是空白。空白单元格应该勾选相应的复选框，而“不打印”则应该将其关闭。如果该行为空白，复选框也应该关闭（或移除）。我希望这是一个常规函数，而不是“编辑”函数。我不能使用公式，因为我希望能够在需要时覆盖复选框。

我一直在尝试修改下面的公式，但没有成功。我认为它应该循环遍历第一列的每一行，并返回 true 或 false。

function autoCheck() {
 const sheet = SpreadsheetApp.getActiveSheet();
 const sName = sheet.getName();
 const iRange = sheet.getRange('I2:I101')
// sheet.getRange('A2:A101').removeCheckboxes();

 if (sName === "sheet1") {
const len = sheet.getRange('I2:I101').getValues().filter(row => row[0] != 'Do Not 
Print').length
  const iRange = sheet.getRange(9,1,len,1);
 // sheet.getRange('A2:A101').removeCheckboxes();
 // range.insertCheckboxes().uncheck();
 Logger.log(iRange);
if (iRange.getValues()[0][0] === "") {
  sheet.getRange('A2:A101').uncheck();
  iRange.check();
}
}

}