أهلا بالجميع! اليوم أريد أن أشارك نصًا مفيدًا جدًا قمت بإنشائه لحل مشكلة شائعة في الحياة اليومية.

إذا سبق لك أن حاولت جمع "المدة" في جداول بيانات Google، فربما لاحظت أن صيغ SUMIF وSUMIFS لا تعمل على جمع فترات الأحداث أو المنتجات بناءً على معايير محددة. قد يكون هذا عائقًا اعتمادًا على نوع الحساب الذي يتعين عليك إجراؤه. ولكن لا تقلق! تتيح لك جداول بيانات Google إنشاء نصوص برمجية بلغة JavaScript واستخدامها كصيغ مخصصة.

في البرنامج النصي الخاص بي، قمت بإنشاء شكلين مختلفين: الأول يقبل معيارًا واحدًا والثاني يقبل ما يصل إلى اثنين. أخطط لتحسين هذا في المستقبل لجعل الوظيفة أكثر مرونة.

من الجدير بالذكر أن الصيغ المخصصة لا تُرجع قيمًا قابلة للحساب مباشرة بواسطة البرنامج. للتغلب على هذه المشكلة، يمكنك التفاف النتيجة باستخدام الدالة =VALUE(). بعد ذلك، ما عليك سوى تطبيق التنسيق المقابل على نوع البيانات - في حالتنا، "المدة". هل أنت مستعد للتحقق من النص؟

إنشاء بيانات عامة للاختبار

أولاً، لنقم بإنشاء بيانات لاختبار الصيغة. لقد استخدمت صديقنا GPT لهذا الغرض.

البرنامج النصي

حاولت توثيق كل شيء قدر الإمكان. قررت تقسيمها إلى وظائف أصغر واستخدام شيء أكثر تصريحًا لزيادة وضوح التعليمات البرمجية.

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))

إذا نجح كل شيء، فيجب أن تكون هذه هي النتيجة:

مجرد نصيحة، أتمنى أن تنال إعجابكم، وإذا كان لديكم اقتراحات، اتركوها في التعليقات. هتافات.

المشاركة الأصلية: https://dev.to/casewinter/como-somar-horas-no-google-sheets-usando-criterios-para-filtrar-linhas-364p