”工欲善其事,必先利其器。“—孔子《论语.录灵公》
首页 > 编程 > Google Sheets:如何花费数小时构建 SUMIFS

Google Sheets:如何花费数小时构建 SUMIFS

发布于2024-11-08
浏览:596

Google Sheets: How to Build a SUMIFS for Hours

大家好!今天我想分享一个我创建的超级有用的脚本,用于解决日常生活中的常见问题。

如果您曾经尝试在 Google 表格中对“持续时间”求和,您可能已经注意到,SUMIF 和 SUMIFS 公式无法根据特定条件对事件或产品的持续时间求和。根据您需要执行的计算类型,这可能会成为一个障碍。但别担心! Google 表格允许您在 JavaScript 中创建脚本并将其用作自定义公式。

在我的脚本中,我创建了两种变体:第一个接受一个条件,第二个接受最多两个条件。我计划将来改进这一点,使功能更加灵活。

值得一提的是,自定义公式不会返回程序可直接计算的值。要解决此问题,您可以使用 =VALUE() 函数包装结果。然后,只需将相应的格式应用于数据类型 - 在我们的例子中为“持续时间”。准备好查看脚本了吗?

创建用于测试的通用数据

首先,让我们生成数据来测试公式。我为此使用了我们的朋友 GPT。

标题 期间 类别 地位
火星人 01:00:00 电影 已观看
星际穿越 02:49:00 电影 已观看
约翰威克 01:30:00 电影 已观看
复仇者联盟:终局之战 03:00:00 电影 想看
怪奇物语 00:45:00 系列 观看
巫师 01:00:01 系列 观看
曼达洛人 00:40:00 系列 观看
绝命毒师 00:50:00 系列 已观看
金钱抢劫 00:55:00 系列 想看
权力的游戏 01:10:00 系列 想看

脚本

我尝试尽可能最好地记录一切。我决定将其分解为更小的函数,并使用更具声明性的内容来提高代码清晰度。

function allAreArrays(...arrays) {
  return arrays.every(Array.isArray);
}

function allArraysHaveSameLength(...arrays) {
  const lengths = arrays.map((arr) => arr.length);
  return lengths.every((val) => val === lengths[0]);
}

function convertHMSToSeconds(hms) {
  // Breaks the string in HH:MM:SS format into parts
  const parts = String(hms).split(":");

  // Converts the parts into integers
  const [hours, minutes, seconds] = parts;

  // Converts hours and minutes into seconds and adds the seconds
  const totalSeconds =
    Number(hours) * 3600   Number(minutes) * 60   Number(seconds);

  return Number(totalSeconds);
}

function convertSecondsToHMS(seconds) {
  // Calculates the number of hours, minutes, and seconds
  const hours = Math.floor(seconds / 3600);
  const minutes = Math.floor((seconds % 3600) / 60);
  const remainingSeconds = seconds % 60;

  // Adds a leading zero to ensure it always has two digits
  const hourFormat = String(hours).padStart(2, "0");
  const minuteFormat = String(minutes).padStart(2, "0");
  const secondFormat = String(remainingSeconds).padStart(2, "0");

  // Returns the HH:MM:SS format
  return `${hourFormat}:${minuteFormat}:${secondFormat}`;
}

/**
 * Sums hours based on a criterion.
 *
 * @param {string[]} sum_range - Set of time intervals in HH:MM:SS format.
 * @param {number[]} criteria_range - Set of criteria corresponding to the time intervals.
 * @param {number} criterion - The criterion for which hours should be summed.
 * @returns {string} Sum of the passed durations, or an error message.
 */
function sumHoursIf(sum_range, criteria_range, criterion) {
  if (!allAreArrays(sum_range, criteria_range))
    return "Pass the intervals for the calculation!";

  if (!allArraysHaveSameLength(sum_range, criteria_range))
    return "Intervals must be the same size";

  // Filters the time intervals for the specific criterion
  const hoursToSum = sum_range.filter(
    (row, index) =>
      String(criteria_range[index]).trim() == String(criterion).trim()
  );

  // Converts the filtered hours to seconds
  const hoursInSeconds = hoursToSum.map((n) => convertHMSToSeconds(n));

  // Sums all the seconds
  const sumOfSeconds = hoursInSeconds.reduce((accumulator, currentValue) => {
    return accumulator   currentValue;
  }, 0);

  // Converts the total seconds back to HH:MM:SS format
  return convertSecondsToHMS(sumOfSeconds);
}

/**
 * Sums hours based on criteria.
 *
 * @param {string[]} sum_range - Set of time intervals in HH:MM:SS format.
 * @param {number[]} criteria_range1 - First set of criteria corresponding to the time intervals.
 * @param {number} criterion1 - The first criterion for which hours should be summed.
 * @param {string[]} criteria_range2 - Second set of criteria corresponding to the time intervals.
 * @param {string} criterion2 - The second criterion for which hours should be summed.
 * @returns {string} Sum of the passed durations, or an error message.
 */
function sumHoursIf2(
  sum_range,
  criteria_range1,
  criterion1,
  criteria_range2,
  criterion2
) {
  if (
    !allAreArrays(
      sum_range,
      criteria_range1,
      criteria_range2
    )
  )
    return "Pass the intervals for the calculation!";

  if (
    !allArraysHaveSameLength(
      sum_range,
      criteria_range1,
      criteria_range2
    )
  )
    return "Intervals must be the same size";

  // Filters the time intervals for the passed criteria
  const hoursToSum = sum_range.filter(
    (row, index) =>
      String(criteria_range1[index]) == String(criterion1).trim() &&
      String(criteria_range2[index]).trim() === String(criterion2).trim()
  );

  // Converts the filtered hours to seconds
  const hoursInSeconds = hoursToSum.map((n) => convertHMSToSeconds(n));

  // Sums all the seconds
  const sumOfSeconds = hoursInSeconds.reduce((accumulator, currentValue) => {
    return accumulator   currentValue;
  }, 0);

  // Converts the total seconds back to HH:MM:SS format
  return convertSecondsToHMS(sumOfSeconds);
}

如何使用?

条件可以是文本或数字,但小时数必须采用“纯文本”格式。转到应用程序脚本并粘贴脚本,然后按“CTRL S”。完毕。使用它的过程与本机公式相同。

应用公式后,我们可以使用 VALUE 再次将其视为程序可以理解的类型;你的代码应该是这样的:

=VALUE(sumHoursIf2($C$2:$C$11;$D$2:$D$11;C$14;$E$2:$E$11;$B15))

如果一切顺利,这应该是您的结果:

类别 电影 系列
协助 5:19:00 0:50:00
协助 0:00:00 2:25:01
Quero 助手 3:00:00 2:05:00

只是一个提示,希望您喜欢它,如果您有建议,请在评论中留下。干杯。

原始帖子:https://dev.to/casewinter/como-somar-horas-no-google-sheets-usando-criterios-para-filtrar-linhas-364p

版本声明 本文转载于:https://dev.to/casewinter/google-sheets-how-to-build-a-sumifs-for-hours-16ee?1如有侵犯,请联系[email protected]删除
最新教程 更多>
  • 如何处理PHP文件系统功能中的UTF-8文件名?
    如何处理PHP文件系统功能中的UTF-8文件名?
    在PHP的Filesystem functions中处理UTF-8 FileNames 在使用PHP的MKDIR函数中含有UTF-8字符的文件很多flusf-8字符时,您可能会在Windows Explorer中遇到comploreer grounder grounder grounder gro...
    编程 发布于2025-04-02
  • 为什么不````''{margin:0; }`始终删除CSS中的最高边距?
    为什么不````''{margin:0; }`始终删除CSS中的最高边距?
    在CSS 问题:不正确的代码: 全球范围将所有余量重置为零,如提供的代码所建议的,可能会导致意外的副作用。解决特定的保证金问题是更建议的。 例如,在提供的示例中,将以下代码添加到CSS中,将解决余量问题: body H1 { 保证金顶:-40px; } 此方法更精确,避免了由全局保证金重置引...
    编程 发布于2025-04-02
  • 如何使用Python的请求和假用户代理绕过网站块?
    如何使用Python的请求和假用户代理绕过网站块?
    如何使用Python的请求模拟浏览器行为,以及伪造的用户代理提供了一个用户 - 代理标头一个有效方法是提供有效的用户式header,以提供有效的用户 - 设置,该标题可以通过browser和Acterner Systems the equestersystermery和操作系统。通过模仿像Chro...
    编程 发布于2025-04-02
  • 在GO中构造SQL查询时,如何安全地加入文本和值?
    在GO中构造SQL查询时,如何安全地加入文本和值?
    在go中构造文本sql查询时,在go sql queries 中,在使用conting and contement和contement consem per时,尤其是在使用integer per当per当per时,per per per当per. [&​​&&&&&&&&&&&&&&&默元组方法在...
    编程 发布于2025-04-02
  • Python读取CSV文件UnicodeDecodeError终极解决方法
    Python读取CSV文件UnicodeDecodeError终极解决方法
    在试图使用已内置的CSV模块读取Python中时,CSV文件中的Unicode Decode Decode Decode Decode decode Error读取,您可能会遇到错误的错误:无法解码字节 在位置2-3中:截断\ uxxxxxxxx逃脱当CSV文件包含特殊字符或Unicode的路径逃...
    编程 发布于2025-04-02
  • 如何使用node-mysql在单个查询中执行多个SQL语句?
    如何使用node-mysql在单个查询中执行多个SQL语句?
    Multi-Statement Query Support in Node-MySQLIn Node.js, the question arises when executing multiple SQL statements in a single query using the node-mys...
    编程 发布于2025-04-02
  • 为什么我在Silverlight Linq查询中获得“无法找到查询模式的实现”错误?
    为什么我在Silverlight Linq查询中获得“无法找到查询模式的实现”错误?
    查询模式实现缺失:解决“无法找到”错误在银光应用程序中,尝试使用LINQ建立错误的数据库连接的尝试,无法找到以查询模式的实现。”当省略LINQ名称空间或查询类型缺少IEnumerable 实现时,通常会发生此错误。 解决问题来验证该类型的质量是至关重要的。在此特定实例中,tblpersoon可能需...
    编程 发布于2025-04-02
  • 如何从PHP中的数组中提取随机元素?
    如何从PHP中的数组中提取随机元素?
    从阵列中的随机选择,可以轻松从数组中获取随机项目。考虑以下数组:; 从此数组中,使用array_rand( array_rand()函数从数组返回一个随机键。通过将$项目数组索引使用此键,我们可以从数组中访问一个随机元素。这种方法为选择随机项目提供了一种直接且可靠的方法。
    编程 发布于2025-04-02
  • 哪种方法更有效地用于点 - 填点检测:射线跟踪或matplotlib \的路径contains_points?
    哪种方法更有效地用于点 - 填点检测:射线跟踪或matplotlib \的路径contains_points?
    在Python Matplotlib's path.contains_points FunctionMatplotlib's path.contains_points function employs a path object to represent the polygon.它...
    编程 发布于2025-04-02
  • HTML格式标签
    HTML格式标签
    HTML 格式化元素 **HTML Formatting is a process of formatting text for better look and feel. HTML provides us ability to format text without us...
    编程 发布于2025-04-02
  • 如何使用不同数量列的联合数据库表?
    如何使用不同数量列的联合数据库表?
    合并列数不同的表 当尝试合并列数不同的数据库表时,可能会遇到挑战。一种直接的方法是在列数较少的表中,为缺失的列追加空值。 例如,考虑两个表,表 A 和表 B,其中表 A 的列数多于表 B。为了合并这些表,同时处理表 B 中缺失的列,请按照以下步骤操作: 确定表 B 中缺失的列,并将它们添加到表的末...
    编程 发布于2025-04-02
  • 在Java中使用for-to-loop和迭代器进行收集遍历之间是否存在性能差异?
    在Java中使用for-to-loop和迭代器进行收集遍历之间是否存在性能差异?
    For Each Loop vs. Iterator: Efficiency in Collection TraversalIntroductionWhen traversing a collection in Java, the choice arises between using a for-...
    编程 发布于2025-04-02
  • 如何使用Regex在PHP中有效地提取括号内的文本
    如何使用Regex在PHP中有效地提取括号内的文本
    php:在括号内提取文本在处理括号内的文本时,找到最有效的解决方案是必不可少的。一种方法是利用PHP的字符串操作函数,如下所示: 作为替代 $ text ='忽略除此之外的一切(text)'; preg_match('#((。 &&& [Regex使用模式来搜索特...
    编程 发布于2025-04-02
  • 如何将来自三个MySQL表的数据组合到新表中?
    如何将来自三个MySQL表的数据组合到新表中?
    mysql:从三个表和列的新表创建新表 答案:为了实现这一目标,您可以利用一个3-way Join。 选择p。*,d.content作为年龄 来自人为p的人 加入d.person_id = p.id上的d的详细信息 加入T.Id = d.detail_id的分类法 其中t.taxonomy =...
    编程 发布于2025-04-02
  • 如何在Java字符串中有效替换多个子字符串?
    如何在Java字符串中有效替换多个子字符串?
    在java 中有效地替换多个substring,需要在需要替换一个字符串中的多个substring的情况下,很容易求助于重复应用字符串的刺激力量。 However, this can be inefficient for large strings or when working with nu...
    编程 发布于2025-04-02

免责声明: 提供的所有资源部分来自互联网,如果有侵犯您的版权或其他权益,请说明详细缘由并提供版权或权益证明然后发到邮箱:[email protected] 我们会第一时间内为您处理。

Copyright© 2022 湘ICP备2022001581号-3