¿Cómo poner pesos relativos y ranking de los mismos para distintos elementos y distintas fechas en un tabla extensa de datos?
La imagen muestra una base de datos bastante simplificada a la que realmente tengo pero debería de operar igual cualquier macro:
Sólo trabajo en la hoja "Ranking".
En la columna "A" están los nombres de los elementos.
En cada nombre aparece la fecha de ese elemento en el extremo derecho (son pocos porque son para cada fin de mes y empezaron el 31 de enero de 2014 y normalmente se repiten para varios fondos) y el fondo del que provienen aparece en el lado izquierdo.
Los que dicen en el intermedio "PEN" indican los montos de cada fondo para cada código ( "i_#", que aparece en la fila "1" pero esos códigos no importan) y para cada fecha (fin de mes). Los que dicen "W" en el intermedio indican el peso de cada uno pero no tiene relevancia porque es un peso con ponderación distinta a la que quiero.
EL OBJETIVO:
Después de cada fecha debe insertarse filas que muestre el peso relativo de cada código (i_#) con respecto a cada fondo por cada fecha. En cada etiqueta debe decir "W_RV" en lugar de "PEN"
Luego de eso, debe insertarse filas que hagan un ranking del peso relativo calculado previamente. En cada etiqueta debe decir "RANK" en lugar de "PEN".
Ejemplo de solución:
Lo sombreado sería las nuevas filas que se insertan entre cada fecha.
Lo de azul es el nuevo peso relativo que sería su monto "PEN" entre la suma de todos los montos "PEN" de esa fila.
Lo de naranja muestra el ranking que hace por fila de los pesos relativos previamente calculados.
Mi base tiene 288 filas útiles (desde enero de 2014 a agosto de 2014 y va creciendo) y 824 columnas útiles ("i_#" y va creciendo). A veces hay celdas vacías porque determinada etiqueta no tenía determinado código para determinada fecha, así que el comando ".End(xlToRight)" no es la mejor opción. Lo mejor sería encontrar la última celda llena por cada fila, hacer un rango que vaya de la primera celda llena a la última celda llena y sume valores.
Alguna ayuda por favor! Siempre pongo la mejor calificación. Tal vez podrían guiarme.
Si la hacen, podrían ponerle comentarios a la programación. Me gusta programar pero no soy tan bueno y quiero aprender.
1 respuesta
Envíame tu archivo con 2 hojas, en la primera hoja pones tus datos originales, y en la segunda hoja me pones los datos resultado, me explicas con comentarios, cómo pasaron los registros del original al resultado.
Te preparo la macro y le pongo comentarios de lo que hace.
Mi correo [email protected]
En el asunto del correo escribe tu nombre de usuario “Kenji Eduardo Moreno” y el título de esta pregunta.
Hola Dante.
Ya te envié el correo. Muchas gracias!
Te anexo la macro
Sub PesosRelativos()
'Por.Dante Amor
Application.ScreenUpdating = False
Set h2 = Sheets("Ranking2")
Sheets("Ranking").Cells.Copy h2.Range("A1")
h2.Select
For i = Range("A" & Rows.Count).End(xlUp).Row To 6 Step -36
Range("A" & i - 35 & ":AES" & i).Copy
Range("A" & i + 1).Insert Shift:=xlDown
Range("A" & i + 1 & ":AES" & i + 36).Replace "_W_", "_RANK_", lookat:=xlPart
Range("A" & i + 1 & ":AES" & i + 36).Replace "_PEN_", "_W_RV_", lookat:=xlPart
Range("B" & i + 1 & ":AES" & i + 18).FormulaR1C1 = "=R[-36]C/SUM(R[-36]C2:R[-36]C825)"
Range("B" & i + 19 & ":AES" & i + 36).FormulaR1C1 = "=RANK(R[-18]C,R[-18]C2:R[-18]C825,0)"
Range("A" & i + 1 & ":AES" & i + 18).Interior.ColorIndex = 4
Range("A" & i + 19 & ":AES" & i + 36).Interior.ColorIndex = 6
Next
MsgBox "Pesos y ranking terminado"
End SubSaludos.Dante Amor
Hola Dante.
Ya he probado tu programación y funciona bien. Muchas gracias.
Sin embargo, mantiene la última columna ("AES") así como el salto de filas ("step -36") fijas cuando éstas pueden cambiar.
Es muy probable que el número de columnas cambie conforme pase el tiempo y no se quede solo en la columna "AES".
Además, puede que las filas que dicen "PEN" por cada fecha sean más que 18 conforme pase el tiempo. Ese cambio no se dará para data histórica, es decir, para las fechas que ya están, el step sí se quedaría en 36; pero en el futuro, si se ingresa nuevas categorías que digan "PEN" para una fecha en específico, el salto de filas debería de aumentar. Es casi imposible que disminuya.
Ejemplo: Si a partir del 31 de marzo de 2015 son 24 las filas que dicen "PEN" y antes que esa fecha sólo eran 18, entonces hará Step de -36 para las fechas anteriores y de -48 a partir del 31/03/2015.
En la programación que yo estaba haciendo, generaba un rango que se iba hasta la última columna así considera columnas nuevas. Además, de alguna manera buscaba trabajar sólo con las filas que digan "PEN" por la posibilidad de que luego aumenten.
Me gustaría que implementes esas cositas porfa y descuida que siempre pongo "Excelente".
Gracias de antemano.
Efectivamente la macro que te envié funciona para el ejemplo que me enviaste, por eso te pedí datos originales
Valora esta respuesta y crea una nueva pregunta para adecuar el código al rango de fechas
