Ver originales (Francés)

Cálculo de deciles en Excel

katiaboutamdja Mensajes publicados 14 Fecha de registro   Estado Miembro Última intervención   -  
katiaboutamdja Mensajes publicados 14 Fecha de registro   Estado Miembro Última intervención   -
Hola,

Hola a todos

Tengo una tabla en la que tengo columnas de números desde la fila 1 hasta la fila 548, y

en lugar de estos números quiero tener D1, D2, D3.....hasta D9 que corresponde a los 9 deciles y esto para cada columna.

Por ejemplo, el primer número está en la fila 17 columna 8 (porque hay celdas donde no hay nada, quiero dejarlas vacías): es 54 (los números van de 1 a 100):
quiero una fórmula que me encuentre a qué decil pertenece este número y si está en el decil 3 por ejemplo, quiero tener D3 y no 17 (D3 es el decil 3 basado en el cálculo de los deciles de la columna en cuestión y esto columna por columna)

Gracias por leerme y por la ayuda que puedan brindarme.

Configuración: Windows XP / Internet Explorer 7.0

6 respuestas

  1.
    katiaboutamdja Mensajes publicados 14 Fecha de registro   Estado Miembro Última intervención   3
     
    Gracias por tu respuesta
    Ya he calculado los deciles para cada columna
    Sí, efectivamente no es fácil
    Podría hacer eso con un macro de VB pero tampoco lo consigo, creo que es aún más difícil:

    Tengo un código que funciona bien pero es solo para una columna porque, por supuesto, los valores de los deciles cambian de una columna a otra:

    Esta es mi macro, si conoces mejor VBA, pero como es solo para los deciles de la primera columna, por supuesto me gustaría generalizarlo para todas las otras columnas.......

    Gracias por tus respuestas.

    Sub Decil()

    Decil1 = 20.9
    Decil2=...........
    hasta Decil9

    Dim w As Worksheet
    For Each w In Worksheets
    Range("AM20:FB619").Select
    For Each Celda In Selection
    If Celda.Value < Decil1 Then Celda.Value = D1
    .......................
    If Celda.Value > Decil9 Then Celda.Value = D10
    Next Celda

    Next w

    End Sub
    1
  2. eriiic Mensajes publicados 24581 Fecha de registro   Estado Colaborador Última intervención   7 281
     
    Hola,

    propuesta en vba:
    seleccionar el rango y ejecutar la macro. 
    Sub decIL() Dim decil(9) As Double, c As Range, i As Long If MsgBox("Reemplazar por los deciles en " & Selection.Address & ". (" & Selection.Cells.Count & " valores)", vbYesNo + vbQuestion, "Confirmación") = vbNo Then Exit Sub For i = 1 To 9 decil(i) = Application.WorksheetFunction.Percentile(Selection, i / 10) Next i For Each c In Selection For i = 9 To 1 Step -1 If c > decil(i) Then Exit For Next i c = "D" & i + 1 Next c End Sub

    Haz algunas pruebas para ver si corresponde a lo que quieres

    eric

    EDIT: añadido un pequeño seguro para evitar errores de manipulación.
    1
  3. katiaboutamdja Mensajes publicados 14 Fecha de registro   Estado Miembro Última intervención   3
     
    Te agradezco mucho por tu ayuda.

    Mis cifras van de AM20 a FB619 con muchas celdas vacías que deben permanecer vacías y con esta macro tengo D10 por todas partes, incluyendo donde tengo celdas vacías.

    De lo contrario, al comparar con una columna que hago en Excel utilizando la función percentil, aparte de los D10 que tengo en lugar de las celdas vacías, tengo pequeñas diferencias para el último decil, como que tengo D10 con esta macro y en mi columna utilizando Excel (la función percentil) tengo D9.

    Y aparte, en vb, no sabes cómo se utilizan los arreglos, es mucho más rápido para tablas de gran tamaño, esta macro se ejecuta en aproximadamente 1 minuto. Pero la velocidad de la macro es secundaria, ya eres muy amable por ayudarme.

    Gracias de nuevo por tu ayuda, es genial.
    1
    1. eriiic Mensajes publicados 24581 Fecha de registro   Estado Colaborador Última intervención   7 281
       
      ok, lo veré esta noche (si no hay problemas...)
      por otro lado, sería interesante que subieras un archivo anonimizado en cijoint.fr y que pegues el enlace aquí.
      Especifica allí donde encuentres diferencias (D9-D10). Yo he utilizado If c > decile(i), ¿quizás debería ser >= ?
      Y especifica también si tienes casos adicionales además de las celdas vacías que no se deben tratar

      eric
      0
    2. katiaboutamdja Mensajes publicados 14 Fecha de registro   Estado Miembro Última intervención   3
       
      No puedo enviar mi archivo, tal vez sea demasiado pesado. Pero puedo enviártelo por correo, el mío es doudou-boutamdja009@hotmail.fr

      Gracias de nuevo, eres muy amable.
      0
    3. eriiic Mensajes publicados 24581 Fecha de registro   Estado Colaborador Última intervención   7 281
       
      Si no pasa por cijoint.fr, pasará aún menos por correo...
      Aligéralo, lo importante es que haya un rango donde se produzca la diferencia. Y si pesa más de 8Mo, puedes comprimirlo
      Especifica también si solo tienes valores en los rangos correspondientes o si puede haber fórmulas que se deban conservar.
      eric

      PD: edita tu mensaje para eliminar la dirección de correo si no quieres recibir más spam
      0
    4. eriiic Mensajes publicados 24581 Fecha de registro   Estado Colaborador Última intervención   7 281
       
      He encontrado el error que seguramente es la causa de las diferencias (i como long en lugar de como double)
      Comprueba con esta versión, el archivo de ejemplo puede que no sea necesario.
      Calculo en memoria, eso debería ser más rápido 
      Sub decile() Dim decile(9) As Double, i As Double Dim datas(), lig As Long, col As Long If MsgBox("Reemplazar por los deciles en " & Selection.Address & ". (" & Selection.Cells.Count & " valores)", vbYesNo + vbQuestion, "Confirmación") = vbNo Then Exit Sub If Selection.Cells.Count = 1 Then Exit Sub datas = Selection For i = 1 To 9 decile(i) = Application.WorksheetFunction.Percentile(datas, i / 10) Next i For lig = 1 To Selection.Rows.Count For col = 1 To Selection.Columns.Count If datas(lig, col) <> "" Then For i = 1 To 9 If datas(lig, col) <= decile(i) Then Exit For Next i datas(lig, col) = "D" & i End If Next col Next lig Selection = datas End Sub

      eric
      0
  4. katiaboutamdja
     
    Muchas gracias por tu ayuda

    si no
    para las celdas vacías está bien, esas celdas permanecen vacías

    Pero con este macro siempre me muestra uno o dos deciles por debajo o por encima en comparación con Excel con la función percentil.

    por ejemplo, en la línea 27 con este macro tenemos D8 D8 D8 D1 D1 D1 D1....

    y con mi función percentil de Excel (en la línea 1027) D9 D9 D9 D3 D3 D3 D3

    Así que no sé por qué

    de todos modos, muchas gracias. Voy a intentar trabajar en ello para entender por qué
    1
    1. eriiic Mensajes publicados 24581 Fecha de registro   Estado Colaborador Última intervención   7 281
       
      Probé en una pequeña tabla aleatoria, los deciles tenían el mismo valor en la hoja y en VBA.
      Además, WorksheetFunction.Percentile llama a la función centile() de las hojas.
      Sin un archivo de ejemplo con un error, es imposible investigar algo...
      Después de haber subido tu archivo a cijoint.fr, debes pegar el enlace proporcionado aquí.
      eric
      0
  6. katiaboutamdja Mensajes publicados 14 Fecha de registro   Estado Miembro Última intervención   3
     
    Bueno..
    No sé por qué es diferente, pero lo suelto así.
    De todos modos, muchas gracias.
    1
  7. Bidouilleu_R Mensajes publicados 1209 Estado Miembro 296
     
    Hola,

    expuesto así no es sencillo.

    Para información, hay que utilizar la función percentil(matriz;0,1): 0,1 representa el decilo.

    La matriz representa la columna A o B o C o cualquier otra área.

    Así que, en resumen, tenemos una matriz (conjunto de celdas) que da un valor.

    Creo que hay que recrear otra tabla, pero sigue siendo confuso.
    ¡Hasta luego!
    0