una - buscar varios valores en excel

Excel: busca valores mínimos/máximos en una columna entre los que coinciden desde otra columna

Question

Tengo una tabla con dos columnas, digamos A: B. Tengo una lista separada (en la columna D) de todos los valores diferentes en la columna A. Para cada valor objetivo en la columna D, quiero encontrar, entre todas las filas cuya columna A coincide con el objetivo, los valores mínimo y máximo en la columna B. Por ejemplo, si los datos son como se muestra,

    col A   col B        col D
 1  7.5     1.00         7.5      1.00   1.20
 2  7.5     1.04         8        1.08   1.45
 3  7.5     1.08         8.5      1.17   1.83
 4  7.5     1.15
 5  7.5     1.20
 6  8       1.08
 7  8       1.13
 8  8       1.20
 9  8       1.29
10  8       1.38
11  8       1.43
12  8       1.45
13  8.5     1.17
14  8.5     1.22
15  8.5     1.26
16  8.5     1.35
17  8.5     1.42
18  8.5     1.51
19  8.5     1.58
20  8.5     1.64
21  8.5     1.69
22  8.5     1.74
23  8.5     1.79
24  8.5     1.83

Quiero tener fórmulas que devuelvan las dos últimas columnas (mínimo y máximo).

Notas:

1. Sería conveniente tener algo que funcione incluso cuando se refiere a rangos que van más allá de la última fila (por ejemplo, usando $A$8:$A$50 en fórmulas, no necesariamente $A$8:$A$24 ), para que los nuevos datos puedan ser agregado en la parte inferior de las columnas A, B y todo se actualiza automáticamente.

2. Las columnas A, B en realidad contendrán otros datos, encabezados, etc., así que supongo que algunas fórmulas pueden no funcionar con referencias a columnas enteras como $A:$A

EDITAR : Acabo de encontrar algunas publicaciones similares / relacionadas

Encuentre la fecha MIN / MAX en un rango si coincide con los criterios de otras columnas

Condicional mínimo y máximo en Excel 2010

seleccione el valor mínimo en la columna B para los mismos valores en A columnas de Excel?

Dado el valor en la columna A, encuentre min / max en la columna B en EXCEL

encontrar max o min en excel, con condiciones

Answer 1

Puede hacer que las referencias se calculen ellas mismas, suponiendo que no haya lagunas en los datos, utilizando rangos con nombre.
p.ej

ARange = OFFSET ($ A $ 2,0,0, COUNT ($ A: $ A))
BRange = OFFSET ($ B $ 2,0,0, COUNT ($ A: $ A))

(Utilizo el mismo COUNT en ambos para asegurar que las áreas sean del mismo tamaño)

Ahora puedo usar una fórmula de matriz =MAX((ARange=D2)*(BRange)) para obtener el máximo (y lo mismo para min).
Las fórmulas de matriz se ingresan con CTRL + MAYÚS + Entrar
Ver la respuesta de @Simoco para la fórmula correcta

Answer 2

Puede usar fórmulas de matriz para darle las respuestas que necesita.

Por el minuto, puedes usar la fórmula en la celda E1:

{=MIN(IF($A:$A=D1,$B:$B))}

y el máximo de la fórmula para la celda F1 es:

{=MAX(IF($A:$A=D1,$B:$B))}

Para ingresar una fórmula de matriz, debe ingresar todo excepto las llaves (los corchetes) y luego presionar las teclas Ctrl y Shift cuando presiona la tecla Intro ... esto agregará las llaves y la fórmula se considerará una fórmula de matriz.

Una vez ingresado, puede copiar la fórmula para los otros valores coincidentes

Las fórmulas de matriz funcionan calculando cada combinación. Se calculará si el valor en A1 es el mismo que D1, y si lo es, dará el valor de B1, entonces, si el valor de A2 es igual que D1, dará el valor de B2, y así sucesivamente. Esto le dará una lista (o matriz) de valores de la columna B donde el valor en A es una coincidencia. El MIN / MAX se calcula como normal.

Answer 3

La función INDEX puede ayudarlo a evitar el CSE al construir una fórmula estándar usando algunas matemáticas para poner a cero o hacer astronómico cualquier valor que no coincida dependiendo de si está buscando un resultado MAX o MIN .

La fórmula pseudo-MAXIF es un poco más fácil así que comenzaré allí.

=MAX(INDEX(B:B*(A:A=D1), , ))

Excel trata cualquier instrucción booleana TRUE como 1 y cualquier FALSE como 0 cuando se usa matemáticamente. Multiplicar un valor en la columna B por 1 deja el valor sin cambios; multiplicar por 0 dará como resultado cero. La función INDEX pasa una matriz de valores sin modificar y ceros en la función MAX dependiendo de si coincide con los criterios o no. El resultado será el valor máximo de la columna B donde la columna A es igual a los criterios.

La fórmula pseudo-MINIF esencialmente invierte el proceso al excluir matemáticamente cualquier valor que no coincida, dejando solo valores coincidentes entre los cuales elegir un MIN .

=MIN(INDEX(B:B+(A:A<>D1)*1E+99, , ))

Nuevamente, TRUE es 1 y FALSE es 0, pero esta vez lo estamos usando para agregar 1E + 99 (un 1 seguido de 99 ceros que no va a ser el MIN de nada) a ningún valor que no coincida. Los valores coincidentes tendrán 0 × 1E + 99 agregado a ellos, lo que equivale a cero y no cambiará su valor.

Las referencias de rango de celda de columna completa que he usado no afectan negativamente el retraso de cálculo más de lo que lo haría una fórmula de matriz similar.

Answer 4

Esto funciona para mi.

por min:

=MIN(IF(($A$1:$A$50=D1),($B$1:$B$50)))

para max:

=MAX(IF(($A$1:$A$50=D1),($B$1:$B$50)))

Tenga en cuenta que se trata de una matriz de fórmulas, por lo que debe presionar CTRL + MAYÚS + INTRO