Excel: Trouvez des valeurs min/max dans une colonne parmi celles qui correspondent à partir d'une autre colonne

J'ai un tableau avec deux colonnes, dire:B.
J'ai une liste séparée (dans la colonne D), de toutes les différentes valeurs dans la colonne A.
Pour chaque valeur de la cible dans la colonne D, je veux trouver, parmi toutes les lignes dont le col Un correspond à la cible, le minimum et le maximum des valeurs dans la colonne B.
Par exemple, si des données est indiquée,

    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

Je veux avoir des formules qui renvoient les deux dernières colonnes (min et max).

Notes:

  1. Il serait agréable d'avoir quelque chose qui fonctionne même en se référant à des plages allant au-delà de la dernière ligne (p. ex., à l'aide de $A$8:$A$50 dans les formules, pas nécessairement $A$8:$A$24), de sorte que de nouvelles données peuvent être ajoutées au bas des colonnes A,B et tout est mis à jour automatiquement.
  2. Les colonnes A,B contiendra en fait d'autres données, en-têtes, etc., donc je suppose que certaines formules ne peuvent pas travailler avec des références à des colonnes entières comme $A:$A.

MODIFIER: je viens de trouver un peu similaire ou connexe postes

Trouver MIN/MAX de la date dans une gamme si elle correspond à des critères des autres colonnes

Conditionnel Min et Max dans Excel 2010

sélectionnez min de la valeur dans la colonne B pour les mêmes valeurs dans Une colonne excel?

La valeur donnée dans la colonne A, trouver des min/max de la colonne B dans EXCEL

trouver max ou min dans excel, avec des conditions de

Comment fonctionne un tableau croisé dynamique sembler à vous?
C'est une option. Inconvénients: 1) il n'obtient pas automatiquement mis à jour lors de l'ajout/changement de source de données, 2) (peut-être mineur, mais fait toujours un travail mal à l'aise), il semble ne pas fonctionner avec les en-têtes spécifiques que j'ai (cellules fusionnées).
Qui peut être considéré comme un avantage ou un inconvénient trop! Si les résultats reçois des mises à jour, vous perdrez beaucoup de temps chaque fois que des données sont insérés les calculs sont en cours de mise à jour, encore et encore, alors qu'avec un tableau croisé dynamique, vous obtenez la mise à jour une fois que les données sont mises à jour avec une simple pression sur un bouton. Pour le second même si, oui, Tableaux croisés dynamiques ne fonctionnent pas trop bien avec des cellules fusionnées, mais j'ai tendance à penser que l'on nécessite rarement des cellules fusionnées.
pour le Centre de Sélection. Mais il n'est pas disponible pour le centrage vertical (et je me souviens d'une discussion à ce sujet).

OriginalL'auteur sancho.s | 2014-01-03

  1. 6

    Cela fonctionne pour moi.

    pour min:

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

    pour max:

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

    Remarque, que c'est un ensemble de formules, de sorte que vous devez appuyer sur CTRL+MAJ+ENTRÉE

    Elle fonctionne. Quelle est la différence (même mineur) avec la réponse par des user3157280? Si oui, je l'ai raté.
    Il n'y a pas de différence
    ma réponse a été posté en premier
    la principale que l'OP a la réponse à sa question:)
    Je suis d'accord à 100%: le point principal est d'avoir les bonnes réponses, l'accent est mis là. Il est, en outre, bon de préciser le point de vue d'autres.

    OriginalL'auteur Dmitry Pavliv

  2. 2

    Vous pouvez utiliser les formules de tableau pour vous donner les réponses dont vous avez besoin.

    Pour min vous pouvez utiliser la formule dans la cellule E1: 

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

    et le max de la formule pour la cellule F1 est :

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

    Pour entrer une formule de tableau, vous devez entrer tout sauf les accolades (accolades), puis appuyez sur les touches Ctrl et Maj lorsque vous appuyez sur la touche entrée... cela va ajouter des accolades et la formule sera considéré comme une formule de matrice.

    Une fois inscrit, vous pouvez copier la formule vers le bas pour les autres appariés valeurs

    Tableau des formules de travail par le calcul de chaque combinaison. Il va calculer si la valeur de A1 est le même que D1, et si c'est elle donnera la valeur de B1, puis si la valeur de A2 est le même que D1 il donnera la valeur de B2, et ainsi de suite. Cela vous donnera une liste (ou un tableau) des valeurs de la colonne B, où la valeur de A est un match. Le MIN/MAX est ensuite calculé comme la normale.

    Je l'ai testé, il fonctionne. Je pense que j'ai essayé ce un parmi d'autres, j'ai été absent du CST.
    Est-il une raison pour poster la même réponse qu'une autre déjà posté? Est-il pour le supplément d'explication?
    Il a fallu un certain temps pour taper - quand j'ai commencé cette réponse là où pas de réponses.

    OriginalL'auteur Paranoid Android

  3. 2

    La INDEX fonction peut vous aider à éviter des CST par la construction d'une formule standard à l'aide de maths à zéro ou astronomiques non-correspondance des valeurs, selon si vous êtes à la recherche d'un MAX ou MIN résultat.

    La pseudo-MAXIF formule est un peu plus facile donc je vais commencer par là.

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

    Excel traite tout boolean TRUE déclaration 1 et tout FAUX, comme 0 lorsque utilisé mathématiquement. La multiplication d'une valeur dans la colonne B par 1 feuilles de la valeur inchangée; en multipliant par 0 résultat sera zéro. Le INDEX fonction passe un tableau de valeurs inchangées et zéros dans le MAX fonction selon qu'qui correspondent aux critères ou non. Le résultat sera la valeur maximale de la colonne B colonne où A est égal à l'critères.

    La pseudo-MINIF formule essentiellement inverse le processus par mathématiquement à l'exclusion de toute non-valeur correspondante, ne laissant que des valeurs correspondantes à partir de laquelle choisir un MIN.

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

    Encore une fois, le VRAI est 1 et le FAUX est 0 mais cette fois, nous l'utilisons pour ajouter 1E+99 (un 1 suivie par 99 zéros qui ne va pas être la MIN de quoi que ce soit) à une non-correspondance des valeurs. Des valeurs correspondantes auront 0 × 1E+99 ajouté à eux, ce qui équivaut à zéro et ne changent pas de valeur.

                 Excel: Trouvez des valeurs min/max dans une colonne parmi celles qui correspondent à partir d'une autre colonne

    L'ensemble de la colonne références de plage de cellules que j'ai utilisés n'aient pas d'impact négatif calcul lag plus qu'un semblable formule de tableau serait.

    OriginalL'auteur

  4. -1

    Vous pouvez avoir les références de calculer eux-mêmes, s'il n'y a pas de lacunes dans les données, à l'aide de plages nommées.

    par exemple,

    ARange =DECALER($A$2,0,0,COUNT($A:$A))

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

    (J'utilise le même COMPTER à la fois pour assurer les domaines sont de la même taille)

    Maintenant, je peux utiliser une formule matricielle =MAX((ARange=D2)*(BRange)) pour obtenir le max (et même pour min).

    Les Formules de matrice sont saisies avec les touches CTRL+MAJ+Enter

    Voir @Simoco réponse à la formule correcte

    il doesen " de travail. Elle renvoie 0 pour min (si toutes les valeurs sont positives) et de 0 à max (si toutes les valeurs sont négatives)
    oui, vous avez raison. Je vais laisser dans l'info sur les plages nommées, mais votre réponse fonctionne dans tous les cas.

    OriginalL'auteur SeanC