Encontrar valor en rango con criterio excel

Hay que usar la función estadística MODA()

Entre paréntesis van los datos o el rango de los datos.

Entonce escribe esta fórmula en la celda C16

=MODA(C2:C15)

Y luego copias esa celda y la pegas en D16, E16, F16 y G16

Y eso es todo, espero que te sirva y lo hayas entendido. Si es así no olvides puntuar.

Nono, no es el caso, porqe asi calcularía la MODA de toda la columna, y yo necesito qe sea solo de los qe son verdaderos en la columna LÓGICO (Son variables, los datos son manuales).

Pero yo necesito saber la moda de los valores qe están en amarillo. Es una variable, los datos se ingresan aleatoriamente.

No doy con la forma de hacerlo mediante una fórmula de Excel. El mundo de las funciones de Excel es algo muy oscuro y difícil de manejar. El no poder almacenar valores intermedios va contra todos los principios de la programación y hace muy complejo programarlas.

Si te es lo mismo lo haré con macros. ¿Te parece bien?

Espero la respuesta.

Por cierto, las celdas con color amarillo son siempre las que tienen "VERDADERO", ¿Es eso cierto? Si no es así tendrías que decirme el color exacto usado, tanto por nombre como VbYellow o por el índice de la paleta.

La verdad qe si, yo pensé qe había alguna forma pero veo que no soy el único que intento y no encontró, eso me deja un poco tranquilo ja ja.

Es lo mismo usar macros o no, yo no quería usarlas pero veo que otra solución no hay.

Ahora tengo otra duda, dependiendo de esta tabla:

http://imageshack.us/a/img716/7910/excelm.jpg

A lo qe tengo que llegar es a lo qe esta pintado en verde.

Tengo que conseguir la forma de poder sumar (de la fila 1) todos los valores que sean menores que lo que hay en la fila 9, es decir:

Juan suma 67 porque:

5 no es menor que 5, suma 10

8 no es menor que 8, suma 15

11 es menor que 12, no suma

Se entiende?

Puede que haya alguna forma de hacerlo sin macros y sería lo mejor. Pero yo no llego a tanto. Antes de Excel ya había programado en Fortran, Basic, Pascal, y ensamblador. Y bajo mi punto de vista el sistema de las funciones de Excel es algo tanto o más complicado que el ensamblador, es mucho más sencillo el Visual Basic de las macros. Déjame que ahora haga la macro y después si quiereres mandas la pregunta a otro experto para ver si puede hacerlo sin macros, aquí hay mejores expertos que yo en el uso de funciones Excel.

Y tambíen intentaré hacer lo segundo, veo que se suman los puntos cuando se ha conseguido una nota igual o superior a la moda.

Una preugunta. A veces la moda pueden ser dos notas que se repiten el mismo número de veces y pueden ser muy distintas entre si además. Entonces ¿qué moda tomamos? La más alta, la más baja, la primera que aparece, la última, ¿la media de las dos?

No.. La moda es única ya que son horarios de trabajos estos. Y por la segunda pregunta si, es exactamente eso.

Perdona, es que no se de que van las tablas, yo había pensado que eran puntuaciones de exámenes. Pero de todas formas, sean puntos, sean horarios o lo que sea, son números al fin y al cabo. Y nada impide, salvo que estén preparados, que haya dos números que se repitan el mismo número de veces. En la última columna ha salido el 5, pero si la fila de Pedro hubiera estado con verdadero habría habido un empate entre el 10 y el 5. ¿Y entonces cuál se elegía?

Y otra cosa. ¿Si el resultado que más se repitiera fuera el AUSENTE? ¿O ese no se tiene en cuenta?

Lo que pasa es qe al ser horas trabajadas (No fijas), hay una tendencia a qe el nro mas repetido sea el qe realmente necesito, es decir qe: si de 150 empleados que trabajaron 10 horas.. a lo sumo puede haber 10 o 20 que hayan trabajado menos o estén ausentes, ese numero no tendría qe fallar.

Ahora que lo veo, la imagen tiene unas filas ocultas 10. 11 y 12. Será que tienes algún filtro activado a lo mejor. Entonces cambia lo que yo pensaba hacer.

Porque no me mandas mejor el libro y así lo veo todo y no tengo que seguir haciendo preguntas.

Mi correo es

[email protected]

Pon como asunto el título de la pregunta.

Ya creo que lo tengo.

La macro tiene una función llamada

MaxRep(Columna, Lógico)

Columna es el rango de números entre los que hay que buscar la moda. La primera columna del ejemplo sería el rango C2:C15

Lógico es un rango que tenga columna la columna lógico con poner $B$2 bastaría, ya que solo vamos a utilizar la columna del rango que se pasa.

Entonces en la celda C16 pondremos la fórmula

= MaxRep(C2:C15 ; $B$2)

Y esa celda se puede copiar y pegar en D16, E16, F16 y G16 ya que cambiara el rango de la Columna al adecuado pero dejará el rango de la columna lógico.

Y la macro hay que copiarla en un módulo que creemos y es esta

Public Function MaxRep(ByVal Columna, Logico As Range) As Integer
Dim Tabla(500, 1) As Integer
Dim i, Usadas, LogCol, ColuCol, RepeMas As Integer
LogCol = Logico.Column
ColuCol = Columna.Column
Usadas = 0
For i = Columna.Row To Columna.Row + Columna.Count
     If (Not Rows(i).Hidden) And (UCase(Cells(i, LogCol)) = "VERDADERO") Then
         If (UCase(Cells(i, ColuCol)) <> "AUSENTE") Then
             esta = False
             For j = 0 To Usadas - 1
                 If Cells(i, ColuCol) = Tabla(j, 0) Then
                     Tabla(j, 1) = Tabla(j, 1) + 1
                     esta = True
                     Exit For
                 End If
             Next
             If Not esta Then
                 Tabla(Usadas, 0) = Cells(i, ColuCol)
                 Tabla(Usadas, 1) = 1
                 Usadas = Usadas + 1
             End If
         End If
     End If
Next
RepeMas = 0
For i = 0 To Usadas - 1
     If Tabla(i, 1) > RepeMas Then
         RepeMas = Tabla(i, 1)
         MaxRep = Tabla(i, 0)
     End If
Next
End Function

Lee los valores de la columna que sean válidos, es decir, que no sean filas ocultas, que su lógico se verdaderao y no sea ausente y los va contando en un array de doble entrada, en la primera columna está el número y en la segunda en número de veces que ha salido

Y una vez contados todos mira a ver cuál se ha repetido más veces y la función entrega ese número.

Y eso es todo, espero que te sirva y lo hayas entendido. El otro problema ya tendrá que esperar a mañana.

Lo primero es volver a escribir la macro ya que veo que esta vez no hay columna con Verdadero o Falso, entonces voy a hacerla de moda que admita un dato para indicar la columna donde ver si es verdadero o falso o quien admita que no haya dato, y entonces tendrá en cuenta todas las casillas. Es decir:

Si escribimos MaxRep(rango1, rango2) usa la columna de rango2 para buscar verdadero o falso y contar solo las de verdadero

Si escribimos MaxRep(rango1) contará todas.

Esta es la macro:

Public Function MaxRep(ByVal Columna As Range, Optional ByVal Logico As Variant) As Integer
Dim Tabla(500, 1) As Integer
Dim i, Usadas, LogCol, ColuCol, RepeMas As Integer
Dim NoHayLogico As Boolean
NoHayLogico = IsMissing(Logico)
If NoHayLogico Then LogCol = 1 Else LogCol = Logico.Column
ColuCol = Columna.Column
Usadas = 0
For i = Columna.Row To Columna.Row + Columna.Count
     If (Not Rows(i).Hidden) Then
        If NoHayLogico Or (UCase(Cells(i, LogCol)) = "VERDADERO") Then
             If (UCase(Cells(i, ColuCol)) <> "AUSENTE") Then
                 esta = False
                 For j = 0 To Usadas - 1
                     If Cells(i, ColuCol) = Tabla(j, 0) Then
                         Tabla(j, 1) = Tabla(j, 1) + 1
                         esta = True
                         Exit For
                     End If
                Next
                 If Not esta Then
                     Tabla(Usadas, 0) = Cells(i, ColuCol)
                     Tabla(Usadas, 1) = 1
                     Usadas = Usadas + 1
                 End If
             End If
         End If
     End If
Next
RepeMas = 0
For i = 0 To Usadas - 1
     If Tabla(i, 1) > RepeMas Then
         RepeMas = Tabla(i, 1)
         MaxRep = Tabla(i, 0)
     End If
Next
End Function

En la celda B9 irá la fórmula

= MaxRep(B2:B6)

en C9

= MaxRep(C2:C6)

Etc. Podemos copiar la celda B) en las otras y la fórmula se adapta.

Ahora queda lo de las casillas I2, I3, I4, I5, i6 Para no utilizar macros tendré que usar una función llamada matricial. Pero se me está resistiendo, te mando de momento esto y cuando pueda lo termino.

Ya lo tengo.

La formula a poner en la casilla I2 es

=SUMA(SI(B2:G2>=$B$9:$G$9;$B$1:$G$1))

Pero por tratarse de una fórmula matricial después de escribirla hay que presionar CTRL+SHIFT+ENTER

Entonces aparecerán unos corchetes en la fórmula.

No sirve que los escribas a mano deben generarse pulsando esas tres teclas. La tecla SHIFT es la de mayúsculas, está justo arriba de CTRL.

Y una vez hayas introducido la fórmula de esa forma, copias la celda y la pegas en I3, I4, I5, I6.

Por cierto, en la imagen que me mandaste en la columna G estaba mal calculada la moda, ya que es 10 y pone 11, eso hace que los resultados de la columna I también sean otros.

Y eso es todo, espero que te sirva y lo hayas entendido. Si me mandaras una dirección de correo te mandaría el fichero. Si ya está todo bien no olvides puntuar.

Ante todo perdón por la tardanza, estuve con otros temas y no pude responder.

Las dos soluciones que me diste funcionaron a la perfección.

Ahora las estoy implementando, pero me trabé con algo, quizás sepas del tema:

http://imageshack.us/a/img267/8409/otrom.jpg

El problema está cuando pregunto si es mayor o igual a k2:z2. No es posible comparar esos dos rangos de datos para que solo sume los mayores o iguales? Porque los termina sumando a todos aunque sean 0, menores, etc.

Probé usando

=SUMAR.SI.CONJUNTO(K1:Z1;K3:Z3;"F";K5:Z5;">=K2:Z2")

Y usando

=SUMAR.SI.CONJUNTO(K1:Z1;K3:Z3;"F";K5:Z5;">="&K2:Z2)

Pero eso que haces en la imagen es lo mismo que resolví me parece. Entonces no tienes mas que usar la misma fórmula pero adaptándola a los rangos nuevos. Y lo fundamental es que una vez hayas escrito la fórmula pulses CTRL+SHIFT+ENTER. Si no haces eso no compara casilla con casilla en vertical, compara todas con la primera.

No sé que fila comparas con que fila y cual sumas, pero si comparas la 5 con la 6 y sumas lo de la 1 será

=SUMA(SI(K5:Z5>=$K$6:$Z$6;$K$1:$Z$1))

Y por tratarse de una fórmula matricial, después de escribirla hay que presionar CTRL+SHIFT+ENTER no lo olvides

Eso es todo.

Claro pero lo qe pasa es qe necesito dos condiciones, qe sea mayor o igual o qe las celdas K3:Z3 sean F