Michael a lu l'article Comment compter les doublons et les valeurs uniques dans Excel dans l'espoir d'une solution, mais cela n'a pas tout à fait fonctionné pour lui. Il a des dossiers de devis qui s'étendent sur trois ans. Au cours d'une année donnée, les cotations peuvent se produire plus d'une fois. Il a besoin de savoir combien de citations uniques se produisent chaque année. L'expression que Michael a trouvée dans l'article mentionné ci-dessus compte les valeurs uniques, mais l'expression ne le fait pas de manière conditionnelle. J'ai trouvé une solution super simple, mais ce n'était pas ce dont Michael avait besoin, alors il a trouvé une fonction de tableau qui fonctionnait mieux pour lui. Dans cet article, nous allons examiner les deux solutions.
Vous n'avez peut-être pas un problème aussi complexe, mais il y a beaucoup à apprendre ici. Plus précisément, vous apprendrez à créer une liste unique à l'aide d'un filtre avancé – c'est quelque chose que nous pouvons presque tous utiliser. Vous découvrirez également la fonction NB.SI et verrez une fonction de tableau au travail. Tous ces éléments sont des éléments d'une solution que vous pouvez utiliser d'autres manières.
L'édition du navigateur d'Excel prendra en charge le fichier existant, mais la fonctionnalité de filtre avancé n'est pas disponible. Cette version prendra en charge un filtre existant, mais vous ne pouvez pas l'implémenter dans le navigateur. La fonction de tableau sera mise à jour dans le navigateur.
J'utilise Excel 2016 sur un système Windows 10 64 bits. Les deux solutions devraient fonctionner dans n'importe quelle version du ruban. Vous pouvez travailler avec vos propres données ou télécharger le fichier de démonstration .xlsx.
VOIR : Conseils d'alimentation Windows 10 : raccourcis secrets vers vos paramètres favoris (Tech Pro Research)
Ma méthode facile
La figure A montre un exemple simplifié qui correspond au scénario de Michael. Il est facile de voir qu'il y a trois citations uniques et que 2016 a deux citations uniques et 2017 a trois citations uniques. L'expression de l'article précédemment lié renvoie 3, mais c'est tout.
Figure A
Michael a besoin de savoir combien de citations uniques se produisent chaque année.
Ma solution simple consiste à filtrer un ensemble de données unique par année et à utiliser une valeur d'entrée pour faire le reste ; malheureusement, ce n'est pas dynamique. Comme les données existent, chaque enregistrement est unique car les dates sont uniques. Nous avons besoin d'une colonne d'assistance qui renvoie uniquement l'année. Pour ce faire, insérez une colonne entre les guillemets et les dates comme suit :
- Sélectionnez la colonne C.
- Cliquez avec le bouton droit sur la sélection et choisissez Insérer.
- Dans C3, entrez la fonction suivante pour renvoyer l'année pour ce devis :
=YEAR(D3) - Copiez l'expression dans le jeu de données restant, comme illustré à la figure B .
Figure B
Nous utiliserons l'année pour créer une liste d'enregistrements de devis uniques.
Après avoir créé une colonne d'années, nous pouvons générer une liste d'enregistrements uniques comme suit :
- Cliquez à l'intérieur de l'ensemble de données.
- Cliquez sur l'onglet Données, puis sur Avancé dans le groupe Trier et filtrer.
- Dans la boîte de dialogue résultante, cliquez sur l'option Copier vers un autre emplacement.
- Vérifiez la référence de plage de liste et modifiez-la pour référencer uniquement les colonnes B et C. Si vous incluez les dates complètes dans la colonne D, les résultats contiendront l'ensemble de données complet.
- Entrez une cellule d'ancrage, F2, pour la liste unique dans le contrôle Copier vers.
- Cochez l'option Enregistrements uniques uniquement ( Figure C ).
- Cliquez sur OK. La figure D montre cinq enregistrements uniques.
Figure C
Ces paramètres créeront une liste unique d'enregistrements.
Figure D
Nous filtrerons cette liste unique par années.
À ce stade, vous pouvez appliquer un filtre à l'ensemble de données unique et compter les enregistrements à l'aide de SUBTOTAL. Cependant, vous devez appliquer le filtre chaque fois que vous souhaitez compter les devis uniques par année. Ce n'est pas grave, mais c'est quand même plus de travail que nécessaire. À la place, utilisons une valeur d'entrée pour l'année et une expression qui renverra le nombre d'enregistrements uniques pour l'année d'entrée.
Placer la valeur d'entrée et l'expression sous les données uniques est problématique car elles seront difficiles à repérer et vous devrez les déplacer chaque fois que vous mettrez à jour l'ensemble de données uniques. J'ai donc entré deux lignes au-dessus des deux ensembles de données. Lorsque vous appliquez cela à votre propre travail, tenez compte des autres données de la feuille. Dans ce cas, ajouter deux lignes au-dessus ne fera pas de mal, mais ce ne sera pas toujours le cas. Pour insérer deux lignes, sélectionnez les lignes 1 et 2, cliquez avec le bouton droit sur la sélection et choisissez Insérer dans le sous-menu résultant. Ensuite, entrez ce qui suit :
F1: Year
F2: ="Unique for " & G1
G2: =COUNTIF(G5:G9,G1)
Vous êtes maintenant prêt à voir comment tout cela fonctionne ensemble. Entrez une valeur d'entrée dans G1, la valeur 2016 ou 2017. Comme vous pouvez le voir dans la figure E, l'expression dans G2 est mise à jour en évaluant la valeur d'entrée (l'année) dans G1. L'expression concaténée en F2 sert d'étiquette qui identifie l'année.
Figure E
Entrez une valeur d'entrée pour mettre à jour l'expression dans G2.
VOIR : 30 choses que vous ne devriez jamais faire dans Microsoft Office (PDF gratuit)
L'expression de Michel
Comme je l'ai mentionné, ma solution est facile à mettre en œuvre, mais elle n'est pas dynamique. Si Michel entre une nouvelle citation dans l'ensemble de données, il doit mettre à jour la liste d'enregistrements uniques. Il a trouvé une expression de tableau qui fonctionnait mieux pour lui :
{=SUM(--(FREQUENCY(IF(quote<>"",IF(year=yearinputvalue,MATCH(year,year,0))),ROW(year)-ROW(firstcellinquoterange)+1)>0))}
Comme vous pouvez le voir sur la figure F , cela fonctionne très bien !
Figure F
Une fonction de tableau a donné à Michael les résultats instantanés dont il avait besoin.
Michael a décidé d'utiliser un tableau, afin qu'il puisse avoir une valeur unique pour chaque année, prête à l'emploi. Son tableau n'est cependant pas dynamique; s'il ajoute un enregistrement, le tableau ne l'évaluera pas. Dans ce cas, le correctif est simple : convertissez l'ensemble de données d'origine en un objet Table. Lorsque vous ajoutez un enregistrement, les références dans les expressions (D1:D2) sont automatiquement mises à jour.
Certains d'entre vous se demandent peut-être pourquoi je considère la fonction de tableau plus difficile que le filtre avancé. Ils peuvent certainement être plus efficaces si vous savez ce que vous faites. Cependant, de nombreux utilisateurs n'ont pas les compétences nécessaires pour en écrire un, et encore moins pour l'implémenter et le gérer. Le seul problème à considérer au-delà des compétences est que les tableaux ont tendance à être des porcs en mémoire. Mais avec les systèmes à haute puissance d'aujourd'hui, c'est beaucoup moins un problème qu'auparavant.
Et il y en a un de plus…
Pour être complet, je veux mentionner une autre solution possible : une matrice. Je ne fournirai pas d'instructions détaillées car la solution se trouve dans le fichier de démonstration téléchargeable. La matrice simple illustrée à la figure G est similaire à ma solution de filtrage, mais elle utilise à la place une petite matrice. Si vous avez de nombreuses valeurs conditionnelles à exprimer, vous trouverez peut-être une matrice plus facile à utiliser.
Chiffre G
Cette matrice est similaire à la solution liste unique et valeur d'entrée.
Les formules suivent :
Column G: =COUNTIFS(Table22[Quote],$F3,Table22[Year],G$2)
Column H: =COUNTIFS(Table22[Quote],$F3,Table22[Year],H$2)
Row 6: =COUNTIF(G$3:G$5,"<>"&0)
Dans ce cas, l'ensemble de données d'origine est un objet Table, de sorte que les fonctions font référence aux colonnes de Table. Si vous n'êtes pas familiarisé avec le référencement des tableaux, lisez Utiliser la nomenclature des tableaux d'Excel pour créer rapidement des formules dynamiques. Notez que la matrice n'est pas dynamique.
Envoyez-moi votre question sur Office
Je réponds aux questions des lecteurs quand je peux, mais il n'y a aucune garantie. N'envoyez pas de fichiers à moins que cela ne soit demandé ; les demandes d'aide initiales qui arrivent avec des fichiers joints seront supprimées sans être lues. Vous pouvez envoyer des captures d'écran de vos données pour aider à clarifier votre question. Lorsque vous me contactez, soyez le plus précis possible. Par exemple, “Veuillez dépanner mon classeur et corriger ce qui ne va pas” n'obtiendra probablement pas de réponse, mais “Pouvez-vous me dire pourquoi cette formule ne renvoie pas les résultats attendus ?” pourrait. Veuillez mentionner l'application et la version que vous utilisez. Je ne suis pas remboursé par pour mon temps ou mon expertise lorsque j'aide les lecteurs, et je ne demande pas non plus de frais aux lecteurs que j'aide. Vous pouvez me contacter à [email protected].
A lire aussi…
- Rapport spécial : Transformer les mégadonnées en informations commerciales (ZDNet/ PDF gratuit)
- Office Q&A : trois façons de sélectionner toutes les notes de bas de page dans un document
- Comment utiliser VBA pour additionner les valeurs Excel par couleur de remplissage
- Erreurs Excel : comment la feuille de calcul de Microsoft peut être dangereuse pour votre santé (ZDNet)