Cómo usar la función de compensación de Excel para fusionar sus datos

Uso de la función de compensación de Excel para fusionar datos

Anteriormente vimos cómo usar la función Sumproduct de Excel para seleccionar datos de una tabla en Excel. Ese artículo fue en respuesta a una pregunta que tuvimos de un delegado en un curso reciente de modelos financieros. Algunos usuarios de Excel no saben que Sumproduct tiene un poder oculto especial que hace que la función sea útil al seleccionar datos de una hoja de cálculo. Puedes encontrar mas aqui.

El papel de Offset en la fusión, por ejemplo, de datos trimestrales

Este es el ejemplo que desarrollamos en ese primer artículo de Sumproduct. Puede hacer clic en él y descargar el ejemplo:

Cómo fusionar datos usando Sumproduct

El ejemplo funciona. Los datos trimestrales se fusionan con éxito utilizando Sumproduct. Los más observadores entre ustedes (algunos dirían ‘quisquilloso’ o ‘un poco quisquilloso’; estamos de acuerdo con eso) habrán notado que hemos roto una de las pequeñas reglas de oro de Excel al armar ese ejemplo.

Buena práctica: configure su modelo financiero para que pueda completar su hoja de cálculo de Excel a la derecha

Si descarga el ejemplo de hoja de cálculo anterior y hace clic en las celdas C11 y C12, notará que hemos ingresado esas entradas de celda manualmente. Lo que deberíamos haber hecho como mínimo es sombrear las celdas con un color diferente (la hoja de cálculo usa azul) para mostrar que son entradas codificadas. Sombrear las celdas de azul es una cosa, pero imagina que la hoja de cálculo de Excel fuera mucho más grande. Imagine que tuviera decenas o incluso centenas de columnas (imagine un modelo financiero mensual o trimestral de 30 años). Sería bastante doloroso escribir esas entradas Y1, Y2, Y3 manualmente para 10 o 100 columnas. Podríamos mejorar aún más la hoja de cálculo si pudiéramos convertir la línea 11 en una fórmula que pudiéramos completar a la derecha fácilmente.

La función de compensación de Excel puede ayudarlo a ‘saltar’ y fusionar columnas

En el ejemplo, la función Offset podría ayudarnos a ‘saltar’ 4 columnas a la vez. En el siguiente ejemplo, Offset, combinado con Sumproduct, proporciona una solución más completa para fusionar datos. Puede hacer clic en la imagen para descargar el ejemplo:

Cómo fusionar columnas usando Offset

Desplazamiento incrustado dentro de Sumproduct

Si quisiéramos dejarnos llevar, podríamos llevar el ejemplo más allá incorporando el trabajo que está haciendo Offset (permitiéndonos ‘saltar’ 4 columnas a la vez) dentro del Sumproduct. Pero, ¿aclararía eso todo el ejemplo? Creemos que no. Probablemente sea mejor tener la línea adicional y los pasos (Offset, Sumproduct) divididos por separado y con un poco más de claridad.

Incrustación de compensación en el interior de Sumproduct

Pautas de buenas prácticas de modelado de Excel

Terminamos con un pequeño ejemplo bastante sofisticado en el que estamos combinando Offset y Sumproduct para fusionar datos de la manera más eficiente posible. Pero al mismo tiempo, hemos estado tratando de observar algunas reglas simples de buenas prácticas de modelado a medida que avanzamos. Aquí hay un resumen de las reglas que hemos estado tratando de seguir:

  • Siempre codifique con colores sus entradas. Aquellas celdas en las que hemos escrito manualmente (‘codificadas’) una entrada son aquellas a las que podríamos querer volver más tarde y cambiar. Esas son nuestras celdas de entrada o suposición (a diferencia de nuestro cálculo y salida). Sería una buena idea resaltar esas celdas de entrada con un color diferente. Esencialmente, estamos tratando de darle al usuario un mensaje claro: “Esta es una celda de entrada. Esto es algo que quizás desee cambiar más adelante”.
  • Configure sus cálculos para que siempre pueda completar correctamente. Pasamos mucho tiempo completando correctamente los modelos, especialmente en aquellos modelos que se ejecutan durante mucho tiempo y tienen muchas columnas (“Ctrl” “R” es un gran método abreviado de teclado para aquellos a los que les gusta completar correctamente). Lo que no queremos hacer es encontrarnos haciendo ‘ajustes’ manuales a las fórmulas a medida que avanzamos hacia la derecha. Lo último que queremos es tener que hacer entradas manuales para extender el modelo a la derecha. Lo que siempre deberíamos estar haciendo es pensar en cómo podemos fabricar un cálculo que se pueda completar en todo el modelo. Lo último que queremos son dos fórmulas una al lado de la otra que sean ligeramente diferentes entre sí. En tal caso, un nuevo usuario podría tomar el modelo y, teniendo el hábito de llenar de izquierda a derecha, llenar la primera fórmula justo sobre la segunda. Tener dos fórmulas diferentes sentadas una al lado de la otra preparará un modelo para el desastre. Siempre intente configurar sus cálculos para que puedan completarse felizmente de izquierda a derecha.
  • Evite anidar o incrustar funciones una dentro de otra. ¿Recuerdas que discutimos un poco sobre si deberíamos poner una función Offset dentro de un Sumproduct? Podría habernos hecho sentir inteligentes y eliminaría una línea en nuestro modelo financiero. Pero adquirir el hábito de poner funciones dentro de funciones tiene un efecto secundario desagradable. Sus funciones se vuelven largas, complejas y difíciles de verificar y comprender para un nuevo usuario (el que no es tan inteligente como usted). En aras de la claridad, diríamos que es mucho mejor pagar el precio de unas pocas líneas adicionales en su modelo, tal vez consignadas a una sección dedicada a ‘funcionamiento’ en la parte inferior. Diríamos que, en aras de la claridad, es mucho mejor guiar a un nuevo usuario a través de cualquier complejidad línea por línea. Eso ayudará a que alguien nuevo use su modelo o verifique o modifique lo que ha hecho.

Excel como una forma de arte

Las reglas suenan muy aburridas y esperamos que las rompas de vez en cuando (la vida no sería divertida si no rompieras alguna regla de vez en cuando, ¿verdad?). Pero incluso el gran arte parece seguir algunas reglas la mayor parte del tiempo, ¿verdad? La pintura generalmente se aplica a un lienzo (excepto, quizás, en el caso de la cama de Tracey Emin). Por lo general, hay un pincel involucrado. Generalmente los colores claros tienden a ir encima de los oscuros. No pretendemos que estés haciendo un gran arte con Excel, pero si puedes llevar tus modelos al punto donde:

  • Puede hacer algo un poco inteligente como emplear solo algunas fórmulas simples para resolver algunos problemas de manipulación de datos bastante importantes y, al mismo tiempo
  • Puede emplear algunos buenos principios de modelado (el lenguaje común que permite que otros usuarios del modelo entiendan lo que ha hecho)

Entonces no nos sorprendería en absoluto si se encontrara sentado y observando detenidamente lo que ha hecho, tal vez encontrando una sonrisa de satisfacción en su rostro. Está bien, puede que no sea la Mona Lisa de Leonardo da Vinci lo que estás mirando, pero sigue siendo tu propia creación privada y algo maravillosa. ¿Quién dice que el modelado financiero de Excel no puede resultar en algo hermoso? ¡Creemos que puede!

Logotipo de la empresa Financial Training Associates

FTA Ltd es un proveedor de cursos de capacitación financiera. El curso de modelado financiero de FTA se lleva a cabo con regularidad e incluye cobertura de útiles consejos y trucos de Excel.

Similar Posts

Leave a Reply

Your email address will not be published.