Sumar la Misma Celda en Varias Hojas en Sheets

Hoy vamos a aprender cómo crear una función personalizada para sumar la misma celda en varias hojas. Este tutorial es especialmente útil ya que Google Sheets no tiene una función integrada para realizar esta operación, a diferencia de Excel.

¿Qué Vamos a Hacer?

Vamos a crear una macro en Google Apps Script que nos permita sumar la misma celda en varias hojas. Para hacerlo, primero veremos cómo funciona en Excel y luego crearemos una función personalizada en Google Sheets para replicar este comportamiento.

Paso 1: Crear las Hojas de Ejemplo

  1. Creamos varias hojas en Google Sheets, por ejemplo: Hoja1, Hoja2, Hoja3, Hoja4.
  2. En cada hoja, introducimos un valor en la celda C3. Por ejemplo:
  • Hoja1!C3: 3
  • Hoja2!C3: 5
  • Hoja3!C3: 7
  • Hoja4!C3: 11

Paso 2: Escribir la Macro en Google Apps Script

  1. Vamos a Extensiones > Apps Script.
  2. Borramos el contenido del archivo Code.gs y lo reemplazamos con el siguiente código:
function sumarCeldaEnHojas(celda, ...hojasExcluidas) {
  var libro = SpreadsheetApp.getActiveSpreadsheet();
  var hojas = libro.getSheets();
  var suma = 0;

  for (var i = 0; i < hojas.length; i++) {
    var hoja = hojas[i];
    if (!hojasExcluidas.includes(hoja.getName())) {
      var valor = hoja.getRange(celda).getValue();
      suma += typeof valor === 'number' ? valor : 0;
    }
  }
  return suma;
}

function onOpen() {
  var libro = SpreadsheetApp.getActiveSpreadsheet();
  var hojas = libro.getSheets();
  var patrones = hojas.map(hoja => hoja.getName()).join("|");

  libro.createTextFinder("=").matchFormulaText(true).replaceAllWith(Utilities.getUuid());
  libro.createTextFinder(Utilities.getUuid()).replaceAllWith("=");
}

Explicación del Código

  • sumarCeldaEnHojas: Esta función personalizada toma como parámetros la celda a sumar (celda) y opcionalmente las hojas que queremos excluir de la suma (hojasExcluidas). La función recorre todas las hojas del libro, suma el valor de la celda especificada en cada hoja y excluye las hojas indicadas.
  • onOpen: Esta función se ejecuta cada vez que se abre el archivo. Su objetivo es forzar la actualización de las fórmulas en Google Sheets, reemplazando temporalmente los signos de igualdad (=) en las fórmulas y volviéndolos a colocar. Esto es necesario porque las fórmulas personalizadas con parámetros de texto en Google Sheets no se recalculan automáticamente.

Paso 3: Usar la Función Personalizada en Google Sheets

  1. Regresamos a Google Sheets.
  2. En una celda de la hoja Suma, introducimos la función personalizada de la siguiente manera:
   =sumarCeldaEnHojas("C3", "Hoja2", "Hoja3")

Esto sumará los valores de la celda C3 en todas las hojas excepto Hoja2 y Hoja3.

Paso 4: Probar la Función

  1. Introducimos diferentes valores en la celda C3 de cada hoja.
  2. Observamos cómo se actualiza el resultado de la función personalizada en la hoja Suma.

Conclusión

Hemos creado una función personalizada en Google Sheets que nos permite sumar la misma celda en varias hojas, similar a la funcionalidad de Excel. Aunque hay ciertas limitaciones debido a cómo Google Sheets maneja las fórmulas con parámetros de texto, hemos implementado una solución que actualiza las fórmulas automáticamente al abrir el archivo.

Espero que este tutorial les haya sido útil. Si tienen alguna sugerencia o mejora, no duden en compartirla.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.

Pin It on Pinterest

Share This