सभी को नमस्कार! आज मैं दैनिक जीवन में एक आम समस्या को हल करने के लिए बनाई गई एक अत्यंत उपयोगी स्क्रिप्ट साझा करना चाहता हूं।

यदि आपने कभी Google शीट में "अवधि" का योग करने का प्रयास किया है, तो आपने देखा होगा कि SUMIF और SUMIFS सूत्र विशिष्ट मानदंडों के आधार पर घटनाओं या उत्पादों की अवधि का योग करने के लिए काम नहीं करते हैं। आपको जिस प्रकार की गणना करने की आवश्यकता है उसके आधार पर यह एक बाधा हो सकती है। लेकिन घबराना नहीं! Google शीट आपको जावास्क्रिप्ट में स्क्रिप्ट बनाने और उन्हें कस्टम फ़ार्मुलों के रूप में उपयोग करने की अनुमति देता है।

अपनी स्क्रिप्ट में, मैंने दो भिन्नताएँ बनाईं: पहला एक मानदंड स्वीकार करता है और दूसरा दो तक स्वीकार करता है। मैं भविष्य में इस फ़ंक्शन को और अधिक लचीला बनाने के लिए इसमें सुधार करने की योजना बना रहा हूं।

यह उल्लेखनीय है कि कस्टम सूत्र प्रोग्राम द्वारा सीधे गणना योग्य मान नहीं लौटाते हैं। इसके आसपास काम करने के लिए, आप परिणाम को =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-शीट्स-usando-criterios-para-filtrar-linhas-364p