Ordenar automáticamente Rango o Tabla al calcular fórmula.
publicado
Buenas tardes,
Primero de todo desear que, este desastre que estamos viviendo a nivel mundial, este os haya afectado lo menos posible, pediros precaución y responsabilidad.
Hasta hoy, no he necesitado de vuestra ayuda para mis peleas con Excel, por lo que, desde este preciso instante, os doy las gracias de forma anticipada por vuestra comprensión y ayuda al respecto de mis inquietudes. Os explico el problema, os pido disculpas por la extensión del tema, pero considero que cuanta más información mejor para que me podáis echar un cable:
Tengo un dashboard, no estándar, pero sí "armonizado" con el que mido una serie de indicadores sobre varias instalaciones en diferentes plantas productivas. La base de datos es bastante pesada por lo que estoy empezando a utilizar consultas para poder aligerarla un poco, estoy empezando, y seguro que podría optimizarla mucho más, pero bueno, paso a paso. Por el momento parece que no tiene mala pinta y no trabajo el tema consultas desde que utilizaba Access hace más de 20 años y he me he perdido completamente las evoluciones.
En el libro, tengo una serie de macros que me llaman a un archivo (base de datos) en un servidor determinado, me configura y descarga la nueva información en las tablas definidas en el dashboard, recalcula y ordena los rangos para los gráficos según unos criterios de ordenación (en algunos libros hasta 40 gráficos) y vuelve a recalcular nuevamente todo. Como he comentado anteriormente, esta base de datos es muy pesada (estamos hablando que, por semana y para una sola instalación son: ± 35000 registros en tres hojas, 1600 filas en una de las hojas de más datos) Esto ocasiona que, semana a semana se vayan ralentizando los cálculos y, sobre todo, una vez que la macro ordena según los diferentes criterios, pues los ocho procesadores de mi máquina se pueden tirar, en algún caso, hasta cuarenta minutos calculando. Sé que, para este volumen de datos, Excel no es la mejor opción, pero aún estoy muy pez con Power Bi y no encuentro el tiempo para avanzar, además de la dificultad añadida de no poder configurar tablas en columnas, si no en filas. Con las consultas le he conseguido quitar unas 19 columnas con fórmulas que multiplicas por las miles de filas, optimizo bastante el cálculo, pero no es suficiente.
En la antigua macro, lo que iba haciendo era: seleccionar cada uno de los rangos a ordenar (C13:K122) y (C128:K237) hay más rangos, pero en el ejemplo solo he incluido estos dos, y ordenar según un criterio (Mayor a Menor), ahora quería que esto se hiciera de forma automática cuando, al introducir nuevos registros de base de datos y se recalcule la fórmula incluida en la columna de ordenación (TOTAL PERIODO) se ordenen de mayor a menor dichos rangos. Con el procedimiento “Change” he conseguido que, al introducir algún valor de forma manual en la columna TOTAL PERIODO, el rango se ordene y chuta bien, pero el objetivo es el indicado anteriormente, que se ordene nuevamente el rango al variar el resultado de la fórmula.
Lo he probado cambiando el procedimiento a “Calculate” pero me da un error de compilación por lo que la variable definida seguro que no es la correcta. El código que, incluyo directamente en la hoja de trabajo, es el siguiente:
Private Sub Worksheet_Change(ByVal Target As Range)
Ya de paso, si me podéis aconsejar sobre cómo agilizar los cálculos o como estructurar las consultas, sería capaz de haceros la ola… Desactivo el cálculo automático al inicio de la macro, desactivo los filtros que pudiera haber activos, el refresco de pantalla en “False” hasta el final de la macro, delimito el espacio de trabajo de las hojas, delimito el rango de los gráficos de evolución a un máximo de diez modos de incidencia y solo recalculo en dos ocasiones en todo el proceso.
Reitero mi agradecimiento y aprovecho para felicitar a todos los integrantes de esta extraordinaria comunidad, que es la biblia de los sufridores de Excel, y de la que me gustaría ser parte activa para lo que podáis necesitar, por lo que quedo a vuestra entera disposición.
Recibid un cordial saludo.
P.D. En el fichero de ejemplo os he incluido solo algunas columnas de la base de datos y un par de rangos a ordenar, si necesitáis el archivo completo no dudéis en solicitármelo. El código está en la Hoja "Rango prueba 1"
Buenas tardes,
Primero de todo desear que, este desastre que estamos viviendo a nivel mundial, este os haya afectado lo menos posible, pediros precaución y responsabilidad.
Hasta hoy, no he necesitado de vuestra ayuda para mis peleas con Excel, por lo que, desde este preciso instante, os doy las gracias de forma anticipada por vuestra comprensión y ayuda al respecto de mis inquietudes. Os explico el problema, os pido disculpas por la extensión del tema, pero considero que cuanta más información mejor para que me podáis echar un cable:
Tengo un dashboard, no estándar, pero sí "armonizado" con el que mido una serie de indicadores sobre varias instalaciones en diferentes plantas productivas. La base de datos es bastante pesada por lo que estoy empezando a utilizar consultas para poder aligerarla un poco, estoy empezando, y seguro que podría optimizarla mucho más, pero bueno, paso a paso. Por el momento parece que no tiene mala pinta y no trabajo el tema consultas desde que utilizaba Access hace más de 20 años y he me he perdido completamente las evoluciones.
En el libro, tengo una serie de macros que me llaman a un archivo (base de datos) en un servidor determinado, me configura y descarga la nueva información en las tablas definidas en el dashboard, recalcula y ordena los rangos para los gráficos según unos criterios de ordenación (en algunos libros hasta 40 gráficos) y vuelve a recalcular nuevamente todo. Como he comentado anteriormente, esta base de datos es muy pesada (estamos hablando que, por semana y para una sola instalación son: ± 35000 registros en tres hojas, 1600 filas en una de las hojas de más datos) Esto ocasiona que, semana a semana se vayan ralentizando los cálculos y, sobre todo, una vez que la macro ordena según los diferentes criterios, pues los ocho procesadores de mi máquina se pueden tirar, en algún caso, hasta cuarenta minutos calculando. Sé que, para este volumen de datos, Excel no es la mejor opción, pero aún estoy muy pez con Power Bi y no encuentro el tiempo para avanzar, además de la dificultad añadida de no poder configurar tablas en columnas, si no en filas. Con las consultas le he conseguido quitar unas 19 columnas con fórmulas que multiplicas por las miles de filas, optimizo bastante el cálculo, pero no es suficiente.
En la antigua macro, lo que iba haciendo era: seleccionar cada uno de los rangos a ordenar (C13:K122) y (C128:K237) hay más rangos, pero en el ejemplo solo he incluido estos dos, y ordenar según un criterio (Mayor a Menor), ahora quería que esto se hiciera de forma automática cuando, al introducir nuevos registros de base de datos y se recalcule la fórmula incluida en la columna de ordenación (TOTAL PERIODO) se ordenen de mayor a menor dichos rangos. Con el procedimiento “Change” he conseguido que, al introducir algún valor de forma manual en la columna TOTAL PERIODO, el rango se ordene y chuta bien, pero el objetivo es el indicado anteriormente, que se ordene nuevamente el rango al variar el resultado de la fórmula.
Lo he probado cambiando el procedimiento a “Calculate” pero me da un error de compilación por lo que la variable definida seguro que no es la correcta. El código que, incluyo directamente en la hoja de trabajo, es el siguiente:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 11 Then
With Range("C12:K122")
.Sort key1:=.Cells(1, 9), order1:=xlDescending, Header:=xlYes
End With
End If
If Target.Column = 11 Then
With Range("C127:K237")
.Sort key1:=.Cells(1, 9), order1:=xlDescending, Header:=xlYes
End With
End If
End Sub
Ya de paso, si me podéis aconsejar sobre cómo agilizar los cálculos o como estructurar las consultas, sería capaz de haceros la ola… Desactivo el cálculo automático al inicio de la macro, desactivo los filtros que pudiera haber activos, el refresco de pantalla en “False” hasta el final de la macro, delimito el espacio de trabajo de las hojas, delimito el rango de los gráficos de evolución a un máximo de diez modos de incidencia y solo recalculo en dos ocasiones en todo el proceso.
Reitero mi agradecimiento y aprovecho para felicitar a todos los integrantes de esta extraordinaria comunidad, que es la biblia de los sufridores de Excel, y de la que me gustaría ser parte activa para lo que podáis necesitar, por lo que quedo a vuestra entera disposición.
Recibid un cordial saludo.
P.D. En el fichero de ejemplo os he incluido solo algunas columnas de la base de datos y un par de rangos a ordenar, si necesitáis el archivo completo no dudéis en solicitármelo. El código está en la Hoja "Rango prueba 1"
ORDEN AUTOMATICO RANGO BETA v01.xlsm