Obtenir des valeurs uniques dans Excel en utilisant uniquement des formules

Question

Connaissez-vous un moyen dans Excel de "calculer" par formule une liste de valeurs uniques?
Exemple: une plage contient les valeurs "rouge", "bleu", "rouge", "vert", "bleu", "noir"
et je veux avoir comme résultat "rouge," bleu "," vert "," noir "+ éventuellement 2 autres cellules vides.
J'ai déjà trouvé un moyen d'obtenir une liste triée calculée en utilisant SMALL ou LARGE combiné avec INDEX, mais j'aimerais également avoir ce tri calculé, SANS UTILISER VBA.

Answer 1

Ok, j'ai deux idées pour vous. Nous espérons que l'un d'entre eux va vous rendre où vous devez aller. Notez que le premier ignore la demande de faire cela comme une formule puisque cette solution n'est pas assez. J'ai pensé que je assurez-vous que la voie de la facilité ne serait vraiment pas de travail pour vous ;^).

Utiliser la commande Filtre Avancé

1. Sélectionnez la liste (ou placez votre sélection n'importe où à l'intérieur de la liste et cliquez sur ok lorsque la boîte de dialogue vient se plaindre que Excel ne sais pas si votre liste contient des en-têtes ou pas)
2. Choisir De Données/Filtre Avancé
3. Choisissez soit "Filtrer la liste sur place" ou "Copier vers un autre emplacement"
4. Cliquez sur "dossiers Uniques seulement"
5. Cliquez sur ok
6. Vous êtes fait. Une liste unique est créé soit en place ou à un nouvel emplacement. Notez que vous pouvez enregistrer cette action afin de créer une ligne de script VBA pour ce faire, ce qui pourrait alors être généralisée à travailler dans d'autres situations pour vous (par exemple, sans le manuel étapes répertoriées ci-dessus).

À l'aide de Formules (notez que je suis en train de construire sur Locksfree solution pour mettre fin à une liste sans trous)

Cette solution fonctionne avec les caractéristiques suivantes:

La liste doit être triée (ascendant ou descendant n'a pas d'importance). En fait c'est tout à fait exact que l'exigence est vraiment que tous les éléments de même nature doivent être contigus, mais le tri est le moyen le plus facile pour atteindre cet état. Trois nouvelles colonnes sont requis (deux nouvelles colonnes pour les calculs et une nouvelle colonne pour la nouvelle liste). Les deuxième et troisième colonnes pourraient être combinés, mais je vais laisser ça comme un exercice pour le lecteur.

Voici le résumé de la solution:

1. Pour chaque élément de la liste, calculer le nombre de doublons au-dessus d'elle.
2. Pour chaque endroit de la liste unique, le calcul de l'indice de la prochaine pièce unique.
3. Enfin, utilisez l'index pour créer une nouvelle liste avec uniquement des objets uniques.

Et voici un exemple étape par étape:

1. Ouvrez une nouvelle feuille de calcul
2. En a1:a6 entrer l'exemple donné dans la question d'origine ("rouge", "bleu", "rouge", "vert", "bleu", "noir")
3. Le tri de la liste: mettre la sélection dans la liste et choisissez la commande de tri.
4. Dans la colonne B, calculer les doublons:
   1. En B1, entrez "=SI(NB.SI($A$1:A1,A1) = 1,0,NB.SI(A1:$A$6,A1))". Notez que le "$" dans les références des cellules sont très importante car elle va passer à l'étape suivante (à peupler le reste de la colonne) beaucoup plus facile. Le "$" indique une référence absolue de sorte que, lorsque le contenu de la cellule est de copier/coller la référence ne sera pas mise à jour (par opposition à une référence relative, qui sera mise à jour).
   2. Utiliser smart copie pour remplir le reste de la colonne B: Sélectionnez B1. Déplacez votre souris sur le carré noir dans le coin inférieur droit de la sélection. Cliquez et faites glisser vers le bas de la liste (B6). Lorsque vous relâchez, la formule sera copié dans B2:B6 avec les références relatives à jour.
   3. La valeur de B1:B6 devrait maintenant être "0,0,1,0,0,1". Notez que le "1" entrées indiquent les doublons.

5. Dans la Colonne C, de créer un index des objets uniques:
   1. En C1, saisissez "=Ligne()". Vraiment vous voulez juste C1 = 1, mais en utilisant la Ligne() signifie que cette solution fonctionne même si la liste ne démarre pas dans la ligne 1.
   2. En C2, saisissez "=SI(C1+1<=LIGNE($B$6), C1+1+INDEX($B$1:$B$6,C1+1),C1+1)". Le "si" est utilisé pour arrêter un #REF d'être produite lorsque l'indice a atteint la fin de la liste.
   3. Utiliser smart copie pour remplir C3:C6.
   4. La valeur de C1:C6 doit être "1,2,4,5,7,8"

6. Dans la colonne D, créer la nouvelle liste unique:
   1. En D1, saisissez "=SI(C1<=LIGNE($A$6), INDEX($A$1:$A$6,C1), "")". Et, le "si" est utilisé pour arrêter le #RÉF cas lorsque l'index va au-delà de la fin de la liste.
   2. Utiliser smart copie pour remplir D2:D6.
   3. Les valeurs de D1:D6 devrait maintenant être "noir","bleu","vert","rouge","","".

Espérons que cela aide....

Answer 2

Solution

J'ai créé une fonction en VBA pour vous, alors vous pouvez le faire maintenant dans un moyen facile.
Créer un code VBA module (macro) comme vous pouvez le voir dans ce tutoriel.

1. Appuyez Sur Alt+F11
2. Cliquez pour Module en Insert.
3. Coller le code.
4. Si Excel dit que votre format de fichier n'est pas macro convivial que de l'enregistrer en tant que Excel Macro-Enabled en Save As.

Le code Source

Function listUnique(rng As Range) As Variant
    Dim row As Range
    Dim elements() As String
    Dim elementSize As Integer
    Dim newElement As Boolean
    Dim i As Integer
    Dim distance As Integer
    Dim result As String

    elementSize = 0
    newElement = True

    For Each row In rng.Rows
        If row.Value <> "" Then
            newElement = True
            For i = 1 To elementSize Step 1
                If elements(i - 1) = row.Value Then
                    newElement = False
                End If
            Next i
            If newElement Then
                elementSize = elementSize + 1
                ReDim Preserve elements(elementSize - 1)
                elements(elementSize - 1) = row.Value
            End If
        End If
    Next

    distance = Range(Application.Caller.Address).row - rng.row

    If distance < elementSize Then
        result = elements(distance)
        listUnique = result
    Else
        listUnique = ""
    End If
End Function

L'utilisation de la

Il suffit d'entrer =listUnique(range) pour une cellule. Le seul paramètre est range qui est une plage Excel. Par exemple: A$1:A$28 ou H$8:H$30.

Conditions

• L' range doit être une colonne.
• La première cellule où vous appelez la fonction doit être dans la même ligne où l' range commence.

Exemple

Régulière cas

1. Saisir des données et de la fonction d'appel.
   
2. Cultiver.
   
3. Voilà.
   

Cellule vide de cas

Il fonctionne dans les colonnes qui ont des cellules vides. Aussi la fonction des sorties rien (pas d'erreurs) si vous overwind les cellules (l'appel de la fonction) dans des lieux où doit pas être de sortie, comme je l'ai fait dans l'exemple précédent "2. Grandir".

Answer 3

Un moyen détourné consiste à charger votre feuille de calcul Excel dans une feuille de calcul Google, à l'aide de la fonction UNIQUE (étendue) de Google (qui fait exactement ce que vous voulez), puis à enregistrer la feuille de calcul Google au format Excel.

J'admets que ce n'est pas une solution viable pour les utilisateurs d'Excel, mais cette approche est utile à quiconque souhaite utiliser cette fonctionnalité et est capable d'utiliser un tableur Google.

Answer 4

Ce site décrit comment obtenir une liste triée par formule sans devoir trier la liste d'origine http://www.get-digital-help.com/2009/04/14/create-a-unique-alphabetical-sorted-list- extrait de la colonne /

Answer 5

Vous pouvez utiliser COUNTIF pour obtenir le nombre d'occurrences de la valeur dans la plage. Donc, si la valeur est en A3, la plage est A1: A6, puis dans la colonne suivante, utilisez un IF (EXACT (COUNTIF (A3: $ A $ 6, A3), 1), A3, ""). Pour le A4, ce serait IF (EXACT (COUNTIF (A4: 6 USD, A3), 1), A4, "")

Cela vous donnerait une colonne où toutes les valeurs uniques sont sans doublon