Descargar el fichero: TomaCuentas.xlsm
Normalmente importamos de nuestra base de datos las cuentas contables con sus saldos, y luego manipulamos esa información hasta llegar a los informes que deseamos. Esa manipulación se realiza en Excel, e incluso la presentación del informe final, ya que Excel es una herramienta muy flexible e intuitiva. La base de datos varía con la empresa en la que nos encontremos, puede ser Oracle, u otra base de datos profesional, o bien un sistema integrado de gestión tipo SAP.
Supongamos que importamos de nuestra base de datos y obtenemos una relación de cuentas contables del grupo 6, a 8 dígitos, y con ellas también importamos su saldo actual. Estas cuentas pueden venir sin ordenar de menor a mayor, o bien pudieran venir ordenadas.
En otra tabla tenemos esas mismas cuentas con los saldos. Un sistema para llevarnos las cuentas a esta nueva tabla de Saldos sería copiar y pegar la tabla anterior, pero no lo vamos a hacer así, ya que en base a esta tabla de Saldos tenemos nuestros informes que toman las cuentas contables vinculando con la tabla de Saldos, por lo que si copiamos y pegamos destruimos los vínculos.
Posteriormente importaremos nuevamente nuestras cuentas de SAP o del sistema de que se trate y pueden venir nuevas cuentas que se hayan creado. Lo que necesitamos es que las nuevas cuentas se incorporen a la tabla de Saldos, pero recordemos que no podemos simplemente copiar y pegar ya que los vínculos de los otros informes se destruirían.
Para resolver este caso hemos creado una macro que copia en la tabla de Saldos únicamente las cuentas nuevas. También hemos creado otra macro que copia tanto el color de fondo como el color de la tinta y lo replica en la tabla de Saldos.
Pero veamos todo esto por partes, y con detalle.
BuscarV y otros métodos
La tabla de Saldos dispone de tres columnas denominadas Saldo1, Saldo2 y Saldo3. Son tres formas de obtener el saldo de cada cuenta consultando la primera tabla, la que hemos denominado 'Base de datos importada'.
Veamos Saldo1. La celda F7 es:
=BUSCARV(E7;$B$7:$C$206;2;0)
Veamos Saldo2. La celda G7 es:
=DESREF($A$7;COINCIDIR(E7;$B$7:$B$206;0)-1;2)
Veamos Saldo 3. La celda H7 es:
=INDICE($B$7:$C$206;COINCIDIR(E7;$B$7:$B$206;0);2)
El sistema clásico de búsqueda en una tabla es BuscarV, pero en algunos casos no es el sistema más conveniente, por lo que hemos dado dos alternativas. Para ello, utilizamos las funciones DESREF, o INDICE que son muy similares, y dentro de ellas para determinar la fila utilizamos COINCIDIR.
El rango en el que buscamos es $B$7:$B$206. Si la base de datos fuera mayor tendríamos que extender el rango por exceso hasta la fila que fuera necesaria para cubrir toda la base de datos. Otro método pudiera ser establecer un rango dinámico tal y como se hace en este post: Rango dinámico.
Añadimos nuevas cuentas
Supongamos que una nueva importación de SAP, o el sistema de que se trate, nos proporciona nuevas cuentas contables con su saldo asociado.
Las nuevas cuentas las hemos coloreado dando diferentes colores de fondo y de tinta.
Estamos suponiendo que el proceso de importación de cuentas deja las cuentas nuevas al final de la tabla.
Macro que incorpora cuentas nuevas
Antes de lanzar la macro la situación previa es la siguiente.
Después de lanzar la macro habremos conseguido incorporar las cuentas nuevas.
La macro ha traído también el saldo de las cuentas, pero este es un valor numérico y no una fórmula. Lo mejor será copiar las fórmulas de los métodos 1, 2 y 3 hacia abajo. Por tanto, seleccionamos el rango F26:H26 y lo copiamos hacia abajo, obteniendo la siguiente imagen.
La macro que nos ha permitido hacer la incorporación de las cuentas nuevas es la siguiente.
El 6 que aparece en las expresiones filas2+i+6 obedece a que antes de los datos numéricos de las tablas hay 6 filas, que son las 6 primeras filas de la hoja.
La macro Incorpora1 requiere que las nuevas cuentas se añadan al final de la columna B. Si las nuevas cuentas aparecen intercaladas en la columna B, por ejemplo, por venir ordenadas, la macro Incorpora1 no es válida, y tendremos que utilizar la macro Incorpora2, que es válido vengan en el orden que vengan las nuevas cuentas.
Macro que copia color
Ahora vamos a copiar el color de fondo y el color de la tinta de todas las celdas de la columna B y los vamos a trasladar a la columna E.
La macro utilizada es la siguiente.
Incorporación aunque estén intercaladas
Si las nuevas cuentas que llegan a la columna B no se agrupan al final sino que aparecen intercaladas tendríamos el caso que se recoge en la Hoja2.
En este caso aplicamos la macro Incorpora2 que es válida para cualquier caso, bien vengas las nuevas cuentas intercaladas, o bien vengan todas ellas al final de la columna B.
Finalmente, nos faltaría copiar las fórmulas de BuscarV y similares y dar color con la macro que copia color. El resultado sería el siguiente.
Ahora, si lo deseamos, podemos ordenar la tabla de Saldos. En todo caso, hemos conseguido nuestro objetivo, esto es, al incorporar nuevas cuentas no se estropean los vínculos que teníamos de nuestros informes previos.
Posteriormente importaremos nuevamente nuestras cuentas de SAP o del sistema de que se trate y pueden venir nuevas cuentas que se hayan creado. Lo que necesitamos es que las nuevas cuentas se incorporen a la tabla de Saldos, pero recordemos que no podemos simplemente copiar y pegar ya que los vínculos de los otros informes se destruirían.
Para resolver este caso hemos creado una macro que copia en la tabla de Saldos únicamente las cuentas nuevas. También hemos creado otra macro que copia tanto el color de fondo como el color de la tinta y lo replica en la tabla de Saldos.
Pero veamos todo esto por partes, y con detalle.
BuscarV y otros métodos
La tabla de Saldos dispone de tres columnas denominadas Saldo1, Saldo2 y Saldo3. Son tres formas de obtener el saldo de cada cuenta consultando la primera tabla, la que hemos denominado 'Base de datos importada'.
Veamos Saldo1. La celda F7 es:
=BUSCARV(E7;$B$7:$C$206;2;0)
Veamos Saldo2. La celda G7 es:
=DESREF($A$7;COINCIDIR(E7;$B$7:$B$206;0)-1;2)
Veamos Saldo 3. La celda H7 es:
=INDICE($B$7:$C$206;COINCIDIR(E7;$B$7:$B$206;0);2)
El sistema clásico de búsqueda en una tabla es BuscarV, pero en algunos casos no es el sistema más conveniente, por lo que hemos dado dos alternativas. Para ello, utilizamos las funciones DESREF, o INDICE que son muy similares, y dentro de ellas para determinar la fila utilizamos COINCIDIR.
El rango en el que buscamos es $B$7:$B$206. Si la base de datos fuera mayor tendríamos que extender el rango por exceso hasta la fila que fuera necesaria para cubrir toda la base de datos. Otro método pudiera ser establecer un rango dinámico tal y como se hace en este post: Rango dinámico.
Añadimos nuevas cuentas
Supongamos que una nueva importación de SAP, o el sistema de que se trate, nos proporciona nuevas cuentas contables con su saldo asociado.
Las nuevas cuentas las hemos coloreado dando diferentes colores de fondo y de tinta.
Estamos suponiendo que el proceso de importación de cuentas deja las cuentas nuevas al final de la tabla.
Macro que incorpora cuentas nuevas
Antes de lanzar la macro la situación previa es la siguiente.
Después de lanzar la macro habremos conseguido incorporar las cuentas nuevas.
La macro ha traído también el saldo de las cuentas, pero este es un valor numérico y no una fórmula. Lo mejor será copiar las fórmulas de los métodos 1, 2 y 3 hacia abajo. Por tanto, seleccionamos el rango F26:H26 y lo copiamos hacia abajo, obteniendo la siguiente imagen.
La macro que nos ha permitido hacer la incorporación de las cuentas nuevas es la siguiente.
El 6 que aparece en las expresiones filas2+i+6 obedece a que antes de los datos numéricos de las tablas hay 6 filas, que son las 6 primeras filas de la hoja.
La macro Incorpora1 requiere que las nuevas cuentas se añadan al final de la columna B. Si las nuevas cuentas aparecen intercaladas en la columna B, por ejemplo, por venir ordenadas, la macro Incorpora1 no es válida, y tendremos que utilizar la macro Incorpora2, que es válido vengan en el orden que vengan las nuevas cuentas.
Macro que copia color
Ahora vamos a copiar el color de fondo y el color de la tinta de todas las celdas de la columna B y los vamos a trasladar a la columna E.
La macro utilizada es la siguiente.
Incorporación aunque estén intercaladas
Si las nuevas cuentas que llegan a la columna B no se agrupan al final sino que aparecen intercaladas tendríamos el caso que se recoge en la Hoja2.
En este caso aplicamos la macro Incorpora2 que es válida para cualquier caso, bien vengas las nuevas cuentas intercaladas, o bien vengan todas ellas al final de la columna B.
Finalmente, nos faltaría copiar las fórmulas de BuscarV y similares y dar color con la macro que copia color. El resultado sería el siguiente.
Ahora, si lo deseamos, podemos ordenar la tabla de Saldos. En todo caso, hemos conseguido nuestro objetivo, esto es, al incorporar nuevas cuentas no se estropean los vínculos que teníamos de nuestros informes previos.
Muy útil, gracias por el post.
ResponderEliminarNECESITO UN MACRO QUE ESTE VINCULADO A UN BOTON Y AL EJEDUTARLO ME INTRODUSCA UN VALOR EN UNA DETERMINADA CELDA Y A SU VES LO BLOQUE COSA QUE YA NO SE PUEDA MODIFICAR EJ. INTRODUCIR PARABRA FINALIZAR EN CELDA Q4
ResponderEliminarOJO ANTES DE EJECUTAR EL MACRO LA HOJA ESTA BLOQUEADA