Ver originales (Francés)

Cálculo de deciles en Excel

katiaboutamdja -  
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 Autor
    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
    1. eriiic Colaborador
      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
      1. katiaboutamdja Autor
        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 Colaborador
          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 Autor
          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 Colaborador
          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 Colaborador
          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
      2. 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 Colaborador
          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
      4. katiaboutamdja Autor
        Bueno..
        No sé por qué es diferente, pero lo suelto así.
        De todos modos, muchas gracias.
        1
        1. Bidouilleu_R
          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