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

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

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

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]删除
最新教程 更多>
  • Python不会对超范围子串切片报错的原因
    Python不会对超范围子串切片报错的原因
    在python中用索引切片范围:二重性和空序列索引单个元素不同,该元素会引起错误,切片在序列的边界之外没有。这种行为源于索引和切片之间的基本差异。索引一个序列,例如“示例” [3],返回一个项目。但是,切片序列(例如“示例” [3:4])返回项目的子序列。索引不存在的元素时,例如“示例” [9] ...
    编程 发布于2025-05-01
  • 如何有效地转换PHP中的时区?
    如何有效地转换PHP中的时区?
    在PHP 利用dateTime对象和functions DateTime对象及其相应的功能别名为时区转换提供方便的方法。例如: //定义用户的时区 date_default_timezone_set('欧洲/伦敦'); //创建DateTime对象 $ dateTime = ne...
    编程 发布于2025-05-01
  • 如何在其容器中为DIV创建平滑的左右CSS动画?
    如何在其容器中为DIV创建平滑的左右CSS动画?
    通用CSS动画,用于左右运动 ,我们将探索创建一个通用的CSS动画,以向左和右移动DIV,从而到达其容器的边缘。该动画可以应用于具有绝对定位的任何div,无论其未知长度如何。问题:使用左直接导致瞬时消失 更加流畅的解决方案:混合转换和左 [并实现平稳的,线性的运动,我们介绍了线性的转换。这...
    编程 发布于2025-05-01
  • 为什么在我的Linux服务器上安装Archive_Zip后,我找不到“ class \” class \'ziparchive \'错误?
    为什么在我的Linux服务器上安装Archive_Zip后,我找不到“ class \” class \'ziparchive \'错误?
    class'ziparchive'在Linux Server上安装Archive_zip时找不到错误 commant in lin ins in cland ins in lin.11 on a lin.1 in a lin.11错误:致命错误:在... cass中找不到类z...
    编程 发布于2025-05-01
  • 在细胞编辑后,如何维护自定义的JTable细胞渲染?
    在细胞编辑后,如何维护自定义的JTable细胞渲染?
    在JTable中维护jtable单元格渲染后,在JTable中,在JTable中实现自定义单元格渲染和编辑功能可以增强用户体验。但是,至关重要的是要确保即使在编辑操作后也保留所需的格式。在设置用于格式化“价格”列的“价格”列,用户遇到的数字格式丢失的“价格”列的“价格”之后,问题在设置自定义单元格...
    编程 发布于2025-05-01
  • 查找当前执行JavaScript的脚本元素方法
    查找当前执行JavaScript的脚本元素方法
    如何引用当前执行脚本的脚本元素在某些方案中理解问题在某些方案中,开发人员可能需要将其他脚本动态加载其他脚本。但是,如果Head Element尚未完全渲染,则使用document.getElementsbytagname('head')[0] .appendChild(v)的常规方...
    编程 发布于2025-05-01
  • 如何使用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-05-01
  • 如何同步迭代并从PHP中的两个等级阵列打印值?
    如何同步迭代并从PHP中的两个等级阵列打印值?
    同步的迭代和打印值来自相同大小的两个数组使用两个数组相等大小的selectbox时,一个包含country代码的数组,另一个包含乡村代码,另一个包含其相应名称的数组,可能会因不当提供了exply for for for the uncore for the forsion for for ytry...
    编程 发布于2025-05-01
  • \“(1)vs.(;;):编译器优化是否消除了性能差异?\”
    \“(1)vs.(;;):编译器优化是否消除了性能差异?\”
    答案: 在大多数现代编译器中,while(1)和(1)和(;;)之间没有性能差异。编译器: perl: 1 输入 - > 2 2 NextState(Main 2 -E:1)V-> 3 9 Leaveloop VK/2-> A 3 toterloop(next-> 8 last-> 9 ...
    编程 发布于2025-05-01
  • 为什么PYTZ最初显示出意外的时区偏移?
    为什么PYTZ最初显示出意外的时区偏移?
    与pytz 最初从pytz获得特定的偏移。例如,亚洲/hong_kong最初显示一个七个小时37分钟的偏移: 差异源利用本地化将时区分配给日期,使用了适当的时区名称和偏移量。但是,直接使用DateTime构造器分配时区不允许进行正确的调整。 example pytz.timezone(...
    编程 发布于2025-05-01
  • Java是否允许多种返回类型:仔细研究通用方法?
    Java是否允许多种返回类型:仔细研究通用方法?
    在Java中的多个返回类型:一种误解类型:在Java编程中揭示,在Java编程中,Peculiar方法签名可能会出现,可能会出现,使开发人员陷入困境,使开发人员陷入困境。 getResult(string s); ,其中foo是自定义类。该方法声明似乎拥有两种返回类型:列表和E。但这确实是如此吗...
    编程 发布于2025-05-01
  • 如何在Java中正确显示“ DD/MM/YYYY HH:MM:SS.SS”格式的当前日期和时间?
    如何在Java中正确显示“ DD/MM/YYYY HH:MM:SS.SS”格式的当前日期和时间?
    如何在“ dd/mm/yyyy hh:mm:mm:ss.ss”格式“ gormat 解决方案:的,请访问量很大,并应为procectiquiestate的,并在整个代码上正确格式不多: java.text.simpledateformat; 导入java.util.calendar; 导入java...
    编程 发布于2025-05-01
  • 如何从Google API中检索最新的jQuery库?
    如何从Google API中检索最新的jQuery库?
    从Google APIS 问题中提供的jQuery URL是版本1.2.6。对于检索最新版本,以前有一种使用特定版本编号的替代方法,它是使用以下语法:获取最新版本:未压缩)While these legacy URLs still remain in use, it is recommended ...
    编程 发布于2025-05-01
  • 如何在Chrome中居中选择框文本?
    如何在Chrome中居中选择框文本?
    选择框的文本对齐:局部chrome-inly-ly-ly-lyly solument 您可能希望将文本中心集中在选择框中,以获取优化的原因或提高可访问性。但是,在CSS中的选择元素中手动添加一个文本 - 对属性可能无法正常工作。初始尝试 state)</option> < op...
    编程 发布于2025-05-01
  • 哪种方法更有效地用于点 - 填点检测:射线跟踪或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-05-01

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

Copyright© 2022 湘ICP备2022001581号-3