Question Formule Excel permettant d'obtenir la première et la dernière valeur non vide dans une ligne et de retourner l'en-tête de colonne


Disons que je dispose d'une feuille de calcul Excel regroupant les votes dans certains sondages sur une période donnée, comme dans l'exemple ci-dessous.

Comment trouver le min et max valeur les personnes ont voté un jour donné (le en-tête de colonne)?

         A        B   C   D   E   F   G    H     I   
   --------------------------------------------------
1  | Answers:   | 0 | 1 | 2 | 3 | 4 | 5 | Min | Max |
   --------------------------------------------------
2  | 2014-12-01 |   | 2 | 4 | 1 |   |   |  1  |  3  |
3  | 2015-01-01 |   |   | 1 | 4 | 2 |   |  2  |  4  |
                          ^       ^        ^-----^- Min: 2, Max: 4
                          |       |        
                          |       |_ 2 people voted "4"
                          |
                          |_ 1 person voted "2" on 2015-01-01

3
2018-01-29 16:47


origine




Réponses:


Pour trouver le min valeur les personnes ont voté dans le sondage (l'en-tête de colonne de la première cellule non vide), j'ai utilisé la formule suivante (dans la cellule H2):

=INDEX(B$1:G$1, MATCH(TRUE, INDEX(B2:G2<>"", 0), 0))

Pour trouver le max valeur (en cellule I2):

=LOOKUP(2, 1/(B2:G2<>""),B$1:G$1)

Sous le capot

La fonction de recherche a la signature suivante: LOOKUP( value, lookup_range, [result_range] )

Ce que fait la deuxième formule est le suivant:

  1. Diviser 1 par un tableau de valeurs booléennes (true / false) (B2:G2<>""); c'est le 1/(B2:G2<>"") partie.
  2. Ci-dessus repose sur le fait que Excel convertit booléen TRUE à 1 et FALSE à 0, le résultat de cette étape est donc une liste de:
    • 1's, où CELL_VALUE<>"" revenu TRUE, et 1/TRUE => 1)
    • et #DIV/0! erreurs, où CELL_VALUE<>"" revenu FALSE et 1/FALSE => 1/0 => #DIV/0!
  3. Maintenant, parce que j'ai utilisé 2 (vous pouvez utiliser un nombre commençant à 1) comme valeur de recherche, la formule correspond à la dernière valeur numérique de la plage (comme vous vous en souvenez, la plage est quelque chose dans le sens de [DIV/0!, 1, 1, 1, DIV/0!, DIV/0!], c'est pourquoi la recherche correspondra à la Quatrième article). BTW: Notez que si la formule ne correspond à rien, vous obtenez toujours une erreur # N / A.
  4. A ce stade, nous savons à quelle colonne nous nous intéressons (Quatrième dans le périmètre). Le dernier bit manquant indique donc à Excel que la plage de résultats est la première ligne de la feuille de calcul: B$1:G$1, et c'est là que Excel cherchera le dit Quatrième article.

Vous pouvez en savoir plus sur les formules de recherche Excel ici: http://blogs.office.com/2012/04/26/using-multiple-criteria-in-excel-lookup-formulas/

J'espère que cela t'aides!

Jan


3
2018-01-29 16:58



Agréable. L'ajout de l'exemple de la façon dont il peut être utilisé est génial. Je vois que vous avez déjà obtenu des votes positifs (j'ajoute le mien). Deux réflexions: 1) Vous n'avez pas besoin de l'avertissement, juste la réponse. 2) Il n'est pas évident pourquoi ou comment fonctionne la formule max. Pour rendre la réponse encore meilleure (surtout lorsque vous utilisez une approche novatrice), ajoutez une brève explication de son fonctionnement. 3) Je n'ai pas compris comment capturer le lien direct vers un commentaire avant de voir comment vous l'avez fait. Tu m'apprends toutes sortes de bonnes choses. - fixer1234
Tu as raison. Le bit de formule LOOKUP n'était pas évident et j'ai édité ma réponse pour mieux l'expliquer. J'espère que quelqu'un le trouve utile :) - Jan Molak