Excel - formulas para estadisticas

 
Vista:
sin imagen de perfil
Val: 8
Ha disminuido su posición en 2 puestos en Excel (en relación al último mes)
Gráfica de Excel

formulas para estadisticas

Publicado por Marco (4 intervenciones) el 12/07/2017 19:30:06
Buenas Noches.

Agradecido estaré de aquella persona que por favor me ayude con esta problemática la cual me urge. Resulta que necesito una FORMULA ó MACRO en la cual pueda saber el valor (sea texto o numero) que más se repite en un rango de celdas y que la misma no tome en cuenta las celdas que se encuentren vacías, ya que la mayoría que he usado tales como MODA.UNO , COINCIDIR y INDICE al tener una celda sin información dentro del rango a consultar me arrojan el error #N/A o sencillamente deja la celda de resultado en blanco hasta no estar completas todas las celdas del rango.

TOMAR COMO REFERENCIA EL SIGUIENTE EJEMPLO.


Imagen2

Tal como se muestra en el ejemplo que coloco para dejar en claro lo que busco conseguir, en el rango comprendido entre A1:A8 existen una serie de elementos del cual en la casilla B1 se determina como factor más repetitivo "barcelona" y en la casilla B2 la cantidad de veces que este factor se repite. Es apropiado tomar en cuenta que la casilla A8 se encuentra en blanco por falta de datos..

Espero haber sido un poco más explícito en mi requerimiento, de antemano estoy completamente agradecido por su apoyo y aporte..

Saludos
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
1
Responder
Imágen de perfil de Rafael
Val: 73
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

formulas para estadisticas

Publicado por Rafael (29 intervenciones) el 12/07/2017 20:39:40
Hola Marco,

Tienes un buen enigma por delante, por un lado indicarte que la fórmula MODA sólo funciona con valores, por lo que hay que modificarla un poco para que puedas utilizar con texto, aquí te dejo la solución:

=INDICE(A1:A8;MODA(COINCIDIR(A1:A8;A1:A8;0)))

Eso sí, esto tiene una pega y es las celdas vacías, para solucionarlo o bien, las eliminamos o bien las reemplazamos por un espacio, pero esto tendrá otra pega, y es que si se repite muchas veces (la que mas) el espacio será este el texto encontrado. Al menos es una posibilidad.

Yo te propongo otra y es utilizar una macro.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Sub MasRep()
'Definir cuantos datos hay desde una celda usando CONTARA
    PASEO = Range("E1").Value
    Range("D2").Activate 'Posicionamiento inicial
    'Bucle para realizar la formula de contar y eliminar de la cuenta los vacios
    For i = 1 To PASEO Step 1
 
         ActiveCell.FormulaR1C1 = "=IF(RC[-2]="""",0,COUNTIF(C[-2],RC[-2]))"
         ActiveCell.Offset(1, 0).Activate
 
    Next
    'Nuevo posicionamiento inicial
    Range("D2").Activate
    MAXCANT = ActiveCell.Value 'Tomamos como maximo repetitivo el primer valor
    CELDA = ActiveCell.Row 'Detección de la posicion que tenemos
    CIUDAD = Range("B" & CELDA).Text 'Asignamos como primera ciudad el primer valor
 
    'Comenzamos con el bucle nuevamente para pasar por todos los conteos
    'Al mismo tiempo vamos reasignando la ciudad si encontramos un valor mayor
    For i = 1 To PASEO Step 1
 
        ActiveCell.Offset(1, 0).Activate
        If ActiveCell.Value >= MAXCANT Then
 
            CELDA = ActiveCell.Row
            MAXCANT = ActiveCell.Value
            CIUDAD = Range("B" & CELDA).Text
 
        End If
 
    Next
 
    'Limpiamos el estropicio
    Range("D:D").ClearContents
    'Mostramos resultado
    MsgBox "La ciudad más repetida es " & CIUDAD & " con " & MAXCANT & " veces repetida"
 
End Sub

Con esto puedes tener el resultado independientemente de si tienes celdas vacias o no. Ten en cuenta la posibilidad de que tengas sólo espacios y tambien tendriamos que adaptar la formula de contar, para que los obvie. Y en lugar de mostrar el resultado también podemos volcarlo a una celda. Esto ya a gusto del consumidor.

Espero te haya sido útil.

Un saludo.

Rafa
http://officepostit.es
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
1
Comentar
sin imagen de perfil
Val: 8
Ha disminuido su posición en 2 puestos en Excel (en relación al último mes)
Gráfica de Excel

formulas para estadisticas

Publicado por Marco (4 intervenciones) el 12/07/2017 20:58:06
Hola gracias por tu respuesta aunque aún no me funciono para lo que plante o necesito.

Saludos
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
1
Comentar
Imágen de perfil de Rafael
Val: 73
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

formulas para estadisticas

Publicado por Rafael (29 intervenciones) el 12/07/2017 22:22:51
Buenas de nuevo Marco

En principio la macro debería funcionar perfectamente ya que las prueba que he realizado no me da ningún problema. Hay otras formas como realizar una función y utilizar esta directamente a modo de fórmula, aunque básicamente viene a ser lo mismo que realizar la macro. ¿Has tenido en cuenta las celdas de referencia en modificarlas?, piensa que yo he utilizado unas e igual tu tienes los campos en otras celdas diferentes. Ten esto en cuenta y si lo deseas adjunta el archivo y le echo un ojo para aplicarte la macro directamente sobre tu archivo.

Te doy otra opción que es lo que más te puede interesar y más fácil y rápido te será. Utiliza una tabla dinámica y ordena el resultado de Contar las ciudades de mayor a menor, con esto ya tienes en primera linea la ciudad más repetida y ademas puedes utilizar esta tabla para montar incluso gráficos.

Te dejo un tutorial de tablas dinámicas que puede interesarte para hacer esta tarea.

http://officepostit.es/tutoriales/excel-tutoriales/tabla-dinamica-excel/

Si aún así no consigo ayudarte, dame más información al respecto para poder seguir ayudándote a solucionar tu problema.

Un saludo.

Rafa
http://officepostit.es
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de wordexperto.com
Val: 6.181
Oro
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

formulas para estadisticas

Publicado por wordexperto.com (2129 intervenciones) el 12/07/2017 21:00:30
Hola Marco:
No he conseguido solucionarlo sin rango auxiliar. Pon en un rango auxiliar, que puedes ocultar, en mi ejemplo es c1:c9, esta fórmula matricial (se introduce con Ctrl+Mayús+Intro) =CONTAR.SI(A1:A9;A1:A9)
Ahora en b2: =MAX(C1:C9)
Y en b1: =INDICE(A1:A9;COINCIDIR(B2;C1:C9;0))
https://wordexperto.com/blog/
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

formulas para estadisticas

Publicado por JuanC (1237 intervenciones) el 12/07/2017 23:33:13
puede hacerse de mil maneras... (lo hice de una +/-)

https://www.sendspace.com/file/y7ue5z
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de wordexperto.com
Val: 6.181
Oro
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

formulas para estadisticas

Publicado por wordexperto.com (2129 intervenciones) el 13/07/2017 08:55:48
Efectivamente, como dice Rafael, una Tabla dinámica, como para casi todo, es la mejor opción.
https://wordexperto.com/blog/

Captura
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
sin imagen de perfil
Val: 20
Ha disminuido su posición en 7 puestos en Excel (en relación al último mes)
Gráfica de Excel

formulas para estadisticas

Publicado por Juan Carlos (12 intervenciones) el 16/07/2017 21:05:31
Hola Marco, entendí lo que necesitas. Pero tengo una duda ¿Qué hay que hacer si se encuentran dos o mas elementos que aparecen
el mismo y el mayor número de veces?
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar