Introducción a éstas fórmulas de Excel.
En nuestra economía actual, la generación masiva de datos, macrodatos o mejor conocidos por su nombre en inglés “Big data” está acaparando cada vez más la atención de los administradores de las compañías, de sus dueños, directivos, ejecutivos y personal en general.
(Wikipedia, 2018) “datos a gran escala es un concepto que hace referencia a conjuntos de datos tan grandes y complejos como para que aplicaciones informáticas tradicionales de procesamiento de datos puedan tratarlos adecuadamente.” Normalmente, el resultado de éstos análisis son tablas también muy grandes que resumen los datos originales y que le permiten a un usuario menos especializado poder manipularlas para generar los reportes que un Ejecutivo C-Level requiere.
Es ahí donde encaja éste blog, en darte una visión más amplia de una herramienta que ya conoces y que te podría ayudar a mejorar tus resultados, ¿Te interesa? Sigue leyendo.
Fómulas para usar en hojas de cálculo.
En realidad, la herramienta que ocuparé aquí no es Excel de Microsoft. Sino que, ejemplificaré el uso de formulas anidadas, como me lo presentaron alguna vez en concepto, en una herramienta de uso libre llamada LibreOffice, incluida en la distribución de Linux Fedora, la cual uso en mi computadora personal y en la del trabajo, para incrementar mi productividad. Mi experiencia es que es posible grabar éstas hojas de cálculo para poderlas usar indistintamente en LibreOffice o Excel.
Objetivo de Fórmulas de Excel.
Voy a crear un reporte de referencias cruzadas basándome en el catálogo de cuentas que amablemente dispuso (Pérez, 2016) en su sitio de internet. Éste ejemplo, usará una base de datos contable, y generará un reporte contable con su respectiva proyección. Pero, los principios que aquí describo pueden ocuparse para cualquier tipo de base de datos y reporte.
Descripción de los pasos a seguir.
- Descargar el catálogo de cuentas (https://aplicaexcelcontable.com/blog/descarga-ejemplo-catalogo-cuentas)
- Insertar la columna Saldo y capturar la información que aparece en la columna con ese nombre del archivo adjunto.
- Insertar una columna a la izquierda de la columna A y nombrarla Clave.
- Insertar la columna llamada periodo e ingresar el periodo “201807” (como número).
- Insertar la columna llamada suma a escribir la fórmula: =CONCAT(E5,A5), y copiarlas hacia abajo en todos los renglones.
- Crear la hoja reporte como esta en el ejemplo anexo.
- Poner los datos básicos del reporte.
- En la columna A, escribir el título Clave.
- En la columna B, escribir las categorías que contendrán los subtotales esperados para analizar.
- En la columna C, escribir el periodo “201807” (como número).
- En la columna D, escribir la fórmula (he aquí el secreto) =SUMAR.SI(Catalogo.$F$5:$F$150,CONCAT(C$5,$A6),Catalogo.$D$5:$D$150)
Explicación de ésta fórmula.
La formula quiere decir lo siguiente: “En la hoja Catálogo, para cada celda entre F5 y F150 (están bloqueadas para que al copiar no se modifiquen éstos datos) busca la expresión CONCAT (que para efectos es 201807ACT001 en el renglón 1, 201807ACT002, en el dos y así sucesivamente), y cada vez que la encuentres suma lo que contiene ese mismo renglon pero en la columna D, el resultado escríbelo en la celda C6 de la hoja reporte.”
Además ocupo la fórmula Subtotales en las celdas C14, C18, C23, y C28, lo que me permite determinar un total final más fácilmente. Los datos de las hoja Catalogo 2 incluyen los de la hoja Catálogo más otro juego de cuentas, pero ahora clasificados con el periodo “201808”. La hoja reporte_2 incluye dos columnas, la 201807 y la 201808.
Para obtene éste resultado, solamente es necesario insertar los nuevos datos (Catálogo 2) y posteriormente, en la hoja reporte_2 modificar la fórmula a:
=SUMAR.SI(‘Catalogo 2′.$F$5:$F$296,CONCAT(D$4,$A25),’Catalogo 2’.$D$5:$D$296)
Con lo anterior consigues que cada cifra que se encuentre dentro de los rangos, se sume de acuerdo a los clasificadores: Período y Clave, de manera que con pocas modificaciones, puedes agregar columnas como renglones vayas insertando, según los periodos de los mismos.
Conclusión de esta Excel Formula.
Mi objetivo es presentar una breve descripción de otro acercamiento para generar reportes en una hoja de cálculo, compite y tiene sus ventajas y desventajas contra las tablas dinámicas, macros y otras herramientas. Pero en lo personal me sirve para analizar información y realizar proyecciones rápidas de conjuntos de 8 a 10 mil registros de 50 periodos diferentes, dejando una estructura uniforme del reporte y solo variando, cuando se requiere, los valores de cada periodo, en general o uno a uno.
Si deseas que haga un video explicativo a detalle, por favor, solicitamelo a: hola@hugodelao.com
Recursos.
Descarga el archivo aca: https://www.dropbox.com/s/dvkzsipadz0lzr0/excel-formulas.xlsx?dl=0
Referencias.
Pérez, Roger, 2016. Descarga Ejemplo de Catálogo de Cuentas. Aplica Excel Contable. Obtenido de https://aplicaexcelcontable.com/blog/descarga-ejemplo-catalogo-cuentas
Wikipedia, 2018. Macrodatos. Wikipedia La Enciclopedia libre. Obtenido de https://es.wikipedia.org/wiki/Macrodatos
Notas.
Excel es una marca Registrada de Microsoft Coporation.