Les fonctions d'index et de recherche dans Excel sont la meilleure alternative pour vpr. Fonctions d'indexation et de recherche dans Excel - la meilleure alternative au programme vpr pour trouver des modèles dans le code

Supposons que votre rapport contienne un tableau contenant de nombreuses données dans de nombreuses colonnes. L'analyse visuelle de tels tableaux est extrêmement difficile. Et l'une des tâches pour travailler avec le rapport est l'analyse des données concernant les en-têtes de lignes et de colonnes relatives à un mois particulier. À première vue, il s'agit d'une tâche très simple, mais elle ne peut pas être résolue à l'aide d'une fonction standard. Oui, bien sûr, vous pouvez utiliser l'outil : "ACCUEIL" - "Édition" - "Rechercher" CTRL + F pour ouvrir la fenêtre de recherche de valeurs sur la feuille Excel. Ou créez une règle de mise en forme conditionnelle pour le tableau. Mais alors, il sera impossible d'effectuer d'autres calculs avec les résultats obtenus. Par conséquent, il est nécessaire de créer et d'appliquer correctement la formule appropriée.

Trouver une valeur dans un tableau Excel

Le schéma de résolution de problèmes ressemble à ceci :

• dans la cellule B1, nous entrerons les données qui nous intéressent ;
• la cellule B2 affichera l'en-tête de colonne qui contient la valeur de la cellule B1
• la cellule B3 affichera le nom de la ligne qui contient la valeur de la cellule B1.

En fait, vous devez rechercher des coordonnées dans Excel. Pourquoi est-ce? Très souvent, nous devons obtenir les coordonnées d'une table par valeur. Un peu comme l'analyse matricielle inverse. Un exemple spécifique en un mot ressemble à ceci. L'objectif fixé en chiffres est la valeur initiale, il faut déterminer qui et quand est le plus proche de cet objectif. À titre d'exemple, utilisons une matrice de données simple indiquant les trois quarts des articles vendus, comme indiqué dans la figure ci-dessous. Il est important que tous les numéros correspondent. Si vous ne souhaitez pas créer et remplir manuellement une feuille de calcul Excel à partir de zéro, vous pouvez télécharger un exemple prêt à l'emploi à la fin de l'article.

Nous examinerons séquentiellement des solutions de complexité variable et, à la fin de l'article, le résultat final.

Trouver une valeur dans une colonne Excel

Tout d'abord, apprenons à obtenir les en-têtes de colonne de tableau par valeur. Pour le faire, suivez ces étapes:

1. Dans la cellule B1, entrez la valeur tirée de la table 5277 et surlignez son arrière-plan en bleu pour la lisibilité du champ de saisie (nous entrerons ensuite d'autres nombres dans la cellule B1 pour expérimenter de nouvelles valeurs).
2. Dans la cellule C2, entrez la formule pour obtenir l'en-tête de la colonne du tableau qui contient cette valeur :
3. Après avoir entré la formule pour confirmer, appuyez sur la combinaison de touches de raccourci CTRL + MAJ + Entrée, car la formule doit être exécutée dans un tableau. Si cela est fait correctement, des accolades ( ) apparaîtront autour des bords de la barre de formule.

Recherche d'une valeur dans une ligne Excel

Nous obtenons maintenant le numéro de ligne pour la même valeur (5277). Pour cela, dans la cellule C3, saisissez la formule suivante :

Après avoir entré la formule pour confirmer, appuyez à nouveau sur la combinaison de touches CTRL + SHIFT + Enter et obtenez le résultat :

La formule a renvoyé le nombre 9 - elle a trouvé le titre de la ligne de feuille par la valeur de table correspondante. En conséquence, nous avons l'adresse complète de la valeur D9.

Comment obtenir l'en-tête de colonne et le titre de ligne d'un tableau

Apprenons maintenant à obtenir par valeur les coordonnées non pas de la feuille entière, mais de la table actuelle. En un mot, il faut trouver par valeur 5277 au lieu de D9 pour obtenir les en-têtes :

• pour la colonne du tableau - mars ;
• pour la ligne - Item4.

Pour résoudre ce problème, nous allons utiliser la formule avec les valeurs déjà obtenues dans les cellules C2 et C3. Pour ce faire, nous faisons ceci :

En conséquence, les coordonnées internes de la table sont obtenues par valeur - mars ; Point 4 :

Retrouver les mêmes valeurs dans une plage Excel

Pour contrôler la présence de doublons parmi les valeurs du tableau, nous allons créer une formule qui peut nous informer de la présence de doublons et compter leur nombre. Pour cela, dans la cellule E2, saisissez la formule :

De plus, pour la plage de la section tabulaire, nous allons créer une règle de mise en forme conditionnelle :

Comme vous pouvez le voir, s'il y a des doublons, la formule d'en-tête prend l'en-tête du premier doublon horizontalement (de gauche à droite). Et la formule pour obtenir le nom (numéro) de la ligne prend le numéro du premier doublon verticalement (de haut en bas). Il existe 2 façons de résoudre cette solution :

Dans ce cas, on change les formules soit l'une soit l'autre, mais pas les deux à la fois. Il convient de rappeler que l'ancienne formule doit rester dans la cellule C3 :

Ici, les coordonnées verticales du premier doublon (de haut en bas) sont correctement affichées - I7 pour la feuille et August ; Product2 pour la table. Laissons cette option pour le prochain exemple final.

Recherche de la valeur la plus proche dans une plage Excel

Ce tableau n'est toujours pas parfait. Après tout, lors de l'analyse, vous devez connaître exactement toutes ses valeurs. Si le nombre saisi dans la cellule B1 n'est pas trouvé dans le tableau, une erreur est renvoyée - #VALEUR ! L'idéal serait que la formule, en l'absence d'un nombre initial dans le tableau, sélectionne elle-même la valeur la plus proche que contient le tableau. Pour créer un tel programme d'analyse de table, dans la cellule F1, saisissez une nouvelle formule :

Après quoi suit dans toutes les autres formules, changer la référence au lieu de B1 devrait être F1 ! Vous devez également modifier le lien dans la mise en forme conditionnelle. Sélectionnez : "ACCUEIL" - "Styles" - "Mise en forme conditionnelle" - "Gérer les règles" - "Modifier la règle". Et ici, dans les paramètres, spécifiez F1 au lieu de B1 . Pour tester le programme, entrez dans la cellule B1 un nombre qui n'est pas dans le tableau, par exemple : 8000. Cela conduira au résultat final :

Vous pouvez maintenant entrer n'importe quelle valeur initiale et le programme lui-même sélectionnera le nombre le plus proche contenu dans le tableau. Affiche ensuite l'en-tête de colonne et le nom de la ligne pour la valeur actuelle. Par exemple, si vous saisissez le nombre 5000, nous obtenons un nouveau résultat :

Notre programme dans Excel a trouvé la valeur la plus proche de 4965 pour l'original - 5000. Un tel programme peut être utile pour résoudre automatiquement divers problèmes analytiques dans la planification d'entreprise, la définition d'objectifs, la recherche d'une solution rationnelle, etc. Et les lignes et les colonnes qui en résultent vous permettent d'étendre davantage les capacités de calcul de ce type de rapports à l'aide de nouvelles formules Excel.

Sur la programmation fonctionnelle, qui se tient sous les auspices du Fonds d'appui à la programmation fonctionnelle FP (FP). Traditionnellement, je voudrais résumer les résultats du concours et parler de la résolution du problème de concurrence en utilisant le langage de programmation Haskell. J'invite donc toutes les personnes intéressées à lire cette petite note.

En tant que tâche pour le concours, une tâche a été proposée pour rechercher des modèles dans un certain nombre de manifestations d'un événement apparemment «aléatoire». Mais comme tout le reste dans ce monde, apparemment, les résultats de la mesure des états quantiques sont purement aléatoires, de sorte que dans tout le reste, vous pouvez trouver des modèles. Alors ici. Une liste de dates a été donnée lorsqu'un événement s'est produit, et il a été proposé de répondre à deux questions :

1. Quelle est la période minimale pendant laquelle la probabilité de fréquence d'occurrence d'un événement sur au moins un jour de la période est égale ou supérieure à 50 % ?
2. Il était nécessaire de donner une prévision de la manifestation de l'événement à partir de la date de la compétition jusqu'à la fin de l'année en cours.

Seuls deux candidats ont pu apporter des solutions. Cependant, les deux se sont trompés, puisque la bonne réponse à la première question est le nombre 24. Mais la deuxième tâche sera traitée à la fin de l'année, lorsque les statistiques sur les manifestations des événements seront révélées. Ainsi, le prix de la première question n'a pas été tiré au sort et le prix de la deuxième question sera attribué au candidat dont la prédiction marquera le plus de points l'année prochaine.

Eh bien, il reste ici à considérer la solution de ces problèmes dans le langage de programmation Haskell.

Recherche de période

Dans un premier temps, une liste de manifestations événementielles a été définie :

Dates ::dates = ["27/09/2013", "06/10/2013", "23/10/2013", "06/11/2013", "26/11/2013", "27/11 /2013", "21/12/2013", "30/12/2013", "06/01/2014", "16/01/2014", "17/01/2014", "21/01/2014" ", "25/01/2014", "26/01/2014", "05/02/2014", "11/02/2014", "21/02/2014", "02/03/2014", "07/03/2014", "30/03/2014", "08/04/2014", "18/04/2014", "23/04/2014", "27/04/2014", "05 /02/2014", "15/05/2014", "17/05/2014", "18/05/2014", "19/05/2014", "20/05/2014", "25/05" /2014", "26/05/2014", "28/05.2014"]

Même un coup d'œil rapide montrera qu'il n'y a pas de modèles évidents dans cette liste. Mais le méditer plusieurs jours de suite peut révéler quelque chose. Afin de ne pas se livrer à une perte de temps insensée sur la méditation, il a simplement été décidé d'écrire un programme pour rechercher des modèles dans des périodes de différentes longueurs. Commençons par écrire ce programme, comme c'est généralement le cas en développement dans les langages de programmation fonctionnels, de haut en bas. Voici la fonction principale du programme :

Main :: IO () main = do putStrLn (" ++ "modèles trouvés dans la séquence de dates dans la période minimale de longueur " ++ show (length findMinimalPeriod) ++ " jours.") Revequences findMinimalPeriod

Il y a deux appels de fonction ici, que nous verrons ensuite. Le premier, findMinimalPeriod , renvoie la période de longueur minimale pour un seuil de probabilité donné. Le lecteur attentif remarquera qu'aucun argument ne lui est transmis, de sorte que le seuil de probabilité est défini quelque part comme une constante. En général, c'est une mauvaise pratique pour le paradigme de la programmation fonctionnelle, mais parfois à des fins de recherche, cela aide à obtenir rapidement des résultats. Voici donc la définition :

Signification::Int signification = 5 lowProbability::Float lowProbability = 0.5 findMinimalPeriod::[(Int, Float)] findMinimalPeriod = head $filter(l -> maximum(map snd l) >= lowProbability) $map process

La constante de signification détermine la "hauteur" minimale du cylindre sur lequel l'échelle de temps est enroulée (après tout, pour trouver des périodes, vous pouvez marquer les dates de l'événement sur une longue bande, que vous enroulez ensuite avec une spirale autour un cylindre avec une circonférence donnée, qui détermine la période ; en conséquence, les motifs ressembleront à des lignes verticales). Eh bien, la constante lowProbability définit le seuil de probabilité minimum pour l'occurrence d'un événement. La fonction findMinimalPeriod elle-même prend la tête de la liste obtenue après filtrage de la liste pour la présence d'une probabilité non inférieure à un seuil donné, qui (la liste) est obtenue en traitant (la fonction de processus) des nombres de 1 à une limite supérieure.

La borne supérieure est déterminée à l'aide de la fonction d'intervalle, dont la définition est la suivante :

Interval::Int interval = finish - start where start = stringToDayOfYear $head dates finish = 365 + stringToDayOfYear(last dates)

Comme vous pouvez le voir, nous considérons ici la durée de l'intervalle dans lequel les dates des manifestations de l'événement sont fixées. On soustrait la première date de la dernière date (peut-être faudrait-il ajouter 1). Cette fonction n'est pas très bonne, car elle porte le numéro 365, ce qui signifie qu'elle n'est pas universelle. En tous cas. De plus, la fonction précédente (findMinimalPeriod) est généralement mal écrite et peut générer une erreur d'exécution en raison de l'absence de vérification du vide de la liste transmise à la fonction head.

Passons maintenant à la définition de la fonction process :

Process::Fractional a => Int -> [(Int, a)] process p = map (l -> (fst $head l, ((/) `on` fromInteger) (sum $map snd l) (toEnum $ longueur l))) $ groupBy ((==) `on` fst) $ sortBy (comparant fst) $ map (first (`mod` p) . i -> if i `elem` ds3 then (i, 1) else (i, 0)) où ds1 = map stringToDayOfYear dates ds2 = uncurry (++) $second (map (+365)) $span (>= head ds1) ds1 ds3 = map (subtract (head ds2)) ds2

La fonction reçoit la longueur de la période en entrée et renvoie une liste de paires (histogramme), dans laquelle le premier élément est le numéro du jour de la période et le second est la probabilité de fréquence que l'événement se produise ce jour-là. . En utilisant les définitions locales ds1 , ds2 et ds3 , une liste de jours consécutifs d'occurrence de l'événement est construite, en commençant par le premier jour dans la liste des dates. Cette liste est ensuite soumise à la procédure suivante. Pour tous les nombres de 1 au nombre de la dernière date d'occurrence de l'événement, le reste de la division par la longueur de la période est recherché. Pour tous ces résidus, l'indicateur 0 est activé s'il n'y a pas eu d'événement le jour correspondant, et l'indicateur 1 s'il y en a eu. Ensuite, la liste des résidus avec des drapeaux est regroupée par résidus, après quoi les groupes se regroupent en paires de la forme (numéro du jour dans la période, probabilité d'occurrence de l'événement). Tout.

Ici, nous devons considérer deux autres fonctions de service :

Double :: a -> (a, a) double x = (x, x) stringToDayOfYear :: String -> Int stringToDayOfYear = uncurry (monthAndDayToDayOfYear False) . (lire . prendre 2 . laisser tomber 3 *** lire . prendre 2) . double

Il n'y a rien à dire sur le premier (seulement il est étrange que sa définition ne soit pas dans le module standard Prelude ; bien que cela soit compréhensible, car il est si trivial). La deuxième fonction convertit la date de la représentation sous forme de chaîne "JJ.MM.AAAA" en la représentation numérique acceptée dans le module Data.Time.Calendar.MonthDay, qui est utilisé pour traiter les dates.

Enfin, définissons la fonction RevealSequences :

RevealSequences::[(Int, Float)] -> IO() RevealSequences ps = do let l = longueur ps(d1, p1) = maximumBy(comparant snd) ps(d2, p2) = maximumBy(comparant snd) $delete( d1, p1) ps putStrLn("La manifestation maximale de l'événement (probabilité : " ++ show p1 ++ ") se produit le " ++ show (d1 + 1) ++ "ième jour du " ++ show l + + "-période de jours.") putStrLn ("Le deuxième plus haut (probabilité : " ++ show p2 ++ ") se produit le " ++ show (d2 + 1) ++ "ième jour.")

Comme vous pouvez le voir, il trouve et affiche simplement deux pics de la manifestation de l'événement dans la liste donnée (histogramme). Cette fonction est mise en œuvre uniquement pour la commodité de la recherche et n'est plus liée à la solution de la tâche définie dans le concours.

Prévision

Passons maintenant à la deuxième question du concours - prévoir les dates de manifestation de l'événement avant la fin de l'année. Il convient de noter qu'en général la tâche de prévision est une tâche ingrate. Une question est quand nous avons un modèle clair (même s'il est probabiliste), et nous pouvons plus ou moins appliquer une formule déterministe. Il en va tout autrement lorsqu'il existe un certain (très petit) nombre de valeurs, à partir desquelles il faut construire un avenir possible. Il existe une myriade de points de bifurcation, donc même les tolérances et l'arithmétique d'intervalle n'aideront pas. Cependant, nous pouvons proposer une méthode et essayer d'évaluer son applicabilité déjà avec le recul, lorsqu'un certain laps de temps s'est écoulé, et nous pouvons comparer les valeurs brutes avec les valeurs réelles.

Faisons-le de cette façon ici. La méthode suivante peut être suggérée. Considérons toutes les périodes possibles, en commençant par le minimum trouvé à l'étape précédente (les plus petites, franchement, n'inspirent pas confiance en termes de plausibilité statistique) et en terminant par la durée de la période d'observation connue divisée par la norme de tolérance ("épaisseur", comme nous l'avons appelé plus tôt). Pour chacune de ces périodes, nous obtiendrons les probabilités d'occurrence des événements à chaque jour de la période, comme nous avons déjà appris à le faire à l'aide de la fonction process. Ensuite, on trouve simplement la probabilité moyenne pour chaque jour sur toutes les probabilités trouvées pour chaque période considérée.

C'est cette méthode qui est implémentée par la fonction suivante :

Forecast::FilePath -> String -> String -> IO () prévision fp sd fd = do let (b, e) = (longueur findMinimalPeriod, signification de l'intervalle `div`) writeFile fp $unlines $map (((n, q ) -> let (m, d) = dayOfYearToMonthAndDay False (n - 365) in joliShowInt d ++ "." ++ joliShowInt m ++ ".2014 : " ++ joliShowFloat q).second(/toEnum(e - b + 1))) $ foldr1 (zipWith ((d, q1) (_, q2) -> (d, q1 + q2))) $ map getProbabilities where getProbabilities p = let ds = stringToDayOfYear $ head dates fs = 365 + stringToDayOfYear (dernières dates) d1 = 365 + stringToDayOfYear sd d2 = 365 + stringToDayOfYear fd in drop (intervalle + (d1 - fs)) $ zipWith (x (_, q) -> (x, q)) $ cycle $ process p leadZero :: Chaîne -> Chaîne menantZéro [c] = "0" : [c] menantZéro c = c joliShowInt i = menantZéro $ show i joliShowFloat f = let (d, r) = span (/= ".") $ show ( f*100) en têteZero d++ prendre 5 (cycle r++ "0")

Sa définition semble un peu monstrueuse, mais le cœur des calculs est la définition locale de getProbabilities (il devrait être clair d'après le nom à quelle étape de la méthode elle correspond). Le reste n'est qu'une liaison pour sortir les valeurs obtenues dans un fichier au format spécifié par les conditions du concours.

En général, tout. Il ne reste plus qu'à attendre la fin de l'année et à comparer les prévisions avec les faits.

Pour lutter contre l'explosion combinatoire, un « scrap combinatoire » est nécessaire. Il existe deux outils qui permettent en pratique de résoudre des problèmes combinatoires complexes. La première est la parallélisation massive des calculs. Et ici, il est important non seulement d'avoir un grand nombre de processeurs parallèles, mais aussi de choisir un algorithme qui permet de paralléliser la tâche et de charger toute la puissance de calcul disponible.

Le deuxième outil est le principe de limitation. La principale méthode utilisant le principe de limitation est la méthode des "sous-espaces aléatoires". Parfois, les problèmes combinatoires permettent une forte restriction des conditions initiales et conservent en même temps l'espoir que même après ces restrictions, il restera suffisamment d'informations dans les données pour que la solution requise puisse être trouvée. Il peut y avoir de nombreuses options pour limiter les conditions initiales. Tous ne réussiront peut-être pas. Mais si, néanmoins, il existe une probabilité qu'il existe des variantes réussies de restrictions, alors un problème complexe peut être divisé en un grand nombre de problèmes limités, dont chacun est résolu beaucoup plus simplement que l'original.

En combinant ces deux principes, nous pouvons construire une solution à notre problème.

espace combinatoire

Quel est le sens de cet espace ? Nous supposons que le signal d'entrée n'est pas aléatoire, mais contient certains modèles. Les motifs peuvent être de deux types principaux. Quelque chose dans la description de l'entrée peut apparaître légèrement plus souvent qu'un autre. Par exemple, dans notre cas, les lettres individuelles apparaissent plus souvent que leurs combinaisons. Dans le codage binaire, cela signifie que certaines combinaisons de bits se produisent plus souvent que d'autres.

Un autre type de régularité est lorsque, en plus du signal d'entrée, il y a un signal d'apprentissage qui l'accompagne, et quelque chose contenu dans le signal d'entrée est associé à quelque chose contenu dans le signal d'apprentissage. Dans notre cas, les bits de sortie actifs sont une réponse à une combinaison de certains bits d'entrée.

Si vous recherchez des motifs "sur le front", c'est-à-dire en regardant l'ensemble des vecteurs d'entrée et de sortie, il n'est pas très clair quoi faire et où se déplacer. Si vous commencez à construire des hypothèses sur ce qui peut dépendre de quoi, alors une explosion combinatoire se produit immédiatement. Le nombre d'hypothèses possibles est monstrueux.

Une méthode classique largement utilisée dans les réseaux de neurones est la descente de gradient. Il est important pour lui de comprendre dans quelle direction aller. C'est généralement facile lorsqu'il n'y a qu'une seule cible de sortie. Par exemple, si nous voulons entraîner un réseau de neurones à écrire des nombres, nous lui montrons des images de nombres et indiquons quel type de nombre il voit. Le réseau comprend « comment et où descendre ». Si nous montrons des images avec plusieurs numéros à la fois et appelons tous ces numéros en même temps, sans indiquer où tout se trouve, alors la situation devient beaucoup plus compliquée.

Lorsque des points d'un espace combinatoire sont créés avec une "vue" très limitée (sous-espaces aléatoires), il s'avère que certains points peuvent être chanceux et ils verront un motif, sinon complètement pur, du moins sous une forme significativement raffinée. Une vue aussi limitée permettra, par exemple, d'effectuer des descentes en pente et d'obtenir une pure régularité. La probabilité qu'un seul point tombe sur un modèle n'est peut-être pas très élevée, mais vous pouvez toujours ramasser un tel nombre de points pour vous garantir que tout modèle "apparaîtra quelque part".

Bien sûr, si la taille des points est rendue trop étroite, c'est-à-dire que le nombre de bits dans les points est choisi approximativement égal au nombre de bits attendus dans le motif, alors les dimensions de l'espace combinatoire commenceront à tendre vers le nombre d'options pour l'énumération exhaustive des hypothèses possibles, ce qui nous ramène à l'explosion combinatoire. Mais, heureusement, il est possible d'augmenter la vue des points, en réduisant leur nombre total. Cette réduction n'est pas donnée gratuitement, la combinatoire est "transférée en points", mais jusqu'à un certain point elle n'est pas fatale.

Créons un vecteur de sortie. Nous réduisons simplement plusieurs points de l'espace combinatoire à chaque bit de la sortie. Nous choisissons au hasard quels seront ces points. Le nombre de points qui tombent dans un bit correspondra au nombre de fois que nous voulons réduire l'espace combinatoire. Un tel vecteur de sortie sera une fonction de hachage pour le vecteur d'état de l'espace combinatoire. Nous parlerons de la façon dont cette condition est considérée un peu plus tard.

En général, par exemple, comme le montre la figure ci-dessus, la taille de l'entrée et de la sortie peut être différente. Dans notre exemple de transcodage de chaîne, ces tailles sont les mêmes.

Amas de récepteurs

Nous appellerons l'enregistrement du nombre d'un bit un récepteur pour ce bit. Cela implique que le récepteur surveille l'état du bit correspondant du vecteur d'entrée et réagit lorsqu'un un y apparaît.

L'ensemble de récepteurs sera appelé un cluster de récepteurs ou un cluster réceptif. Lorsqu'un vecteur d'entrée est présenté, les récepteurs du cluster répondent s'il y en a dans les positions correspondantes du vecteur. Pour un cluster, le nombre de récepteurs déclenchés peut être calculé.

Étant donné que nos informations ne sont pas codées par des bits individuels, mais par un code, la précision avec laquelle nous formulons l'hypothèse dépend du nombre de bits que nous prenons dans le cluster. L'article est accompagné du texte du programme qui résout le problème de conversion de chaîne. Par défaut, le programme a les paramètres suivants :

• longueur du vecteur d'entrée - 256 bits ;
• longueur du vecteur de sortie - 256 bits ;
• une seule lettre est codée sur 8 bits ;
• longueur de chaîne - 5 caractères ;
• le nombre de contextes de déplacement est de 10 ;
• la taille de l'espace combinatoire est 60000 ;
• le nombre de bits se croisant en un point est de 32 ;
• seuil de création de cluster - 6 ;
• le seuil d'activation partielle du cluster est de 4.

Avec ces paramètres, presque chaque bit contenu dans le code d'une lettre est répété dans le code d'une autre lettre, et même dans les codes de plusieurs lettres. Par conséquent, un seul récepteur ne peut pas indiquer de manière fiable un motif. Deux récepteurs pointent beaucoup mieux vers une lettre, mais ils peuvent aussi indiquer une combinaison de lettres complètement différentes. Vous pouvez introduire un certain seuil de longueur, à partir duquel vous pouvez juger de manière assez fiable si le cluster contient le fragment de code dont nous avons besoin.

Introduisons le seuil minimum du nombre de récepteurs nécessaires pour former une hypothèse (dans l'exemple il est égal à 6). Commençons à apprendre. Nous soumettrons le code source et le code que nous voulons obtenir en sortie. Pour le code source, il est facile de calculer combien de bits actifs tombent dans chacun des points de l'espace combinatoire. Nous sélectionnons uniquement les points qui sont connectés aux bits actifs du code de sortie et pour lesquels le nombre de bits actifs du code d'entrée qui y tombent n'est pas inférieur au seuil de création d'un cluster. À ces points, nous créerons des grappes de récepteurs avec les ensembles de bits correspondants. Sauvons ces clusters exactement aux points où ils ont été créés. Afin de ne pas créer de doublons, on vérifie d'abord que ces clusters sont uniques pour ces points et que les points ne contiennent pas encore exactement les mêmes clusters.

Disons la même chose en d'autres termes. A partir du vecteur de sortie, nous savons quels bits doivent être actifs. En conséquence, nous pouvons choisir les points de l'espace combinatoire qui leur sont associés. Pour chacun de ces points, nous pouvons formuler une hypothèse selon laquelle ce qu'elle voit maintenant sur le vecteur d'entrée est le motif responsable de l'activité du bit auquel ce point est connecté. Nous ne pouvons pas dire à partir d'un exemple si cette hypothèse est vraie ou non, mais personne ne nous empêche de faire une hypothèse.

Éducation. Consolidation de la mémoire

Il existe différentes approches pour résoudre le problème. Je vais en décrire une, sans prétendre que ce soit la meilleure. J'ai parcouru de nombreuses options, celle-ci m'a soudoyé par la qualité du travail et la simplicité, mais cela ne veut pas dire qu'elle ne peut pas être améliorée.

Il est commode de percevoir les clusters comme des ordinateurs autonomes. Si chaque cluster peut tester sa propre hypothèse et prendre des décisions indépendamment des autres, alors c'est très bon pour la parallélisation potentielle des calculs. Chaque groupe de récepteurs après la création commence une vie indépendante. Il surveille les signaux entrants, acquiert de l'expérience, se change et, si nécessaire, décide de s'autodétruire.

Un cluster est un ensemble de bits, à propos duquel nous avons supposé qu'il y avait un motif à l'intérieur de celui-ci associé à l'opération du bit de sortie auquel le point contenant ce cluster est connecté. S'il existe un motif, il n'affecte très probablement qu'une partie des bits, et nous ne savons pas à l'avance lequel. Par conséquent, nous enregistrerons tous les instants où un nombre significatif de récepteurs sont déclenchés dans le cluster (au moins 4 dans l'exemple). Il est possible qu'à ces moments la régularité, s'il y en a, se manifeste. Lorsque certaines statistiques s'accumulent, nous pouvons essayer de déterminer s'il y a quelque chose de naturel dans de tels déclenchements partiels de cluster ou non.

Un exemple de statistiques est présenté dans la figure ci-dessous. Le signe plus au début de la ligne indique que le bit de sortie était également actif lorsque le cluster a été partiellement activé. Les bits de cluster sont formés à partir des bits correspondants du vecteur d'entrée.

A quoi doit-on s'intéresser dans ces statistiques ? Nous nous soucions de savoir quels éléments fonctionnent ensemble plus souvent que d'autres. Ne confondez pas cela avec les battements les plus fréquents. Si nous calculons pour chaque bit la fréquence de son apparition et prenons les bits les plus courants, alors ce sera une moyenne, ce qui n'est pas du tout ce dont nous avons besoin. Si plusieurs modèles stables ont convergé en un point, la moyenne se traduira par le « non-modèle » moyen entre eux. Dans notre exemple, on peut voir que les lignes 1, 2 et 4 sont similaires entre elles, les lignes 3, 4 et 6 sont également similaires. Nous devons choisir l'un de ces modèles, de préférence le plus fort, et le supprimer des bits supplémentaires.

La combinaison la plus courante qui apparaît lorsque certains bits se déclenchent ensemble concerne cette statistique. Pour calculer la composante principale, vous pouvez utiliser le filtre Hebb. Pour ce faire, vous pouvez spécifier un vecteur avec des poids initiaux unitaires. Ensuite, obtenez l'activité du cluster en multipliant le vecteur de poids par l'état actuel du cluster. Et puis déplacez les poids vers l'état actuel le plus fort, plus cette activité est élevée. Pour que les poids n'augmentent pas de manière incontrôlable, après avoir modifié les poids, ils doivent être normalisés, par exemple, à la valeur maximale du vecteur de poids.

Cette procédure est répétée pour tous les exemples disponibles. De ce fait, le vecteur poids se rapproche de plus en plus de la composante principale. Si les exemples existants ne suffisent pas à converger, alors vous pouvez répéter le processus plusieurs fois sur les mêmes exemples, en réduisant progressivement le taux d'apprentissage.

L'idée principale est qu'à mesure qu'il s'approche du composant principal, le cluster commence à répondre de plus en plus aux échantillons qui lui ressemblent et de moins en moins au reste, de ce fait, apprendre dans la bonne direction va plus vite que les "mauvais" exemples essaient pour le gâcher. Le résultat de cet algorithme après plusieurs itérations est présenté ci-dessous.

Si nous coupons maintenant le cluster, c'est-à-dire que nous ne laissons que les récepteurs qui ont des poids élevés (par exemple, supérieurs à 0,75), nous obtiendrons un modèle nettoyé des bits de bruit supplémentaires. Cette procédure peut être répétée plusieurs fois au fur et à mesure que les statistiques s'accumulent. En conséquence, vous pouvez comprendre s'il existe un modèle dans le cluster ou si nous avons collecté un ensemble aléatoire de bits ensemble. S'il n'y a pas de motif, à la suite du recadrage du cluster, un fragment trop court restera. Dans ce cas, un tel cluster peut être supprimé en tant qu'hypothèse échouée.

En plus de couper le cluster, il est nécessaire de s'assurer que le motif souhaité est capturé exactement. Des codes de plusieurs lettres sont mélangés dans la chaîne originale, chacun d'eux est une régularité. N'importe lequel de ces codes peut être « intercepté » par le cluster. Mais nous ne nous intéressons qu'au code de la lettre qui affecte la formation du bit de sortie. Pour cette raison, la plupart des hypothèses seront fausses et doivent être rejetées. Cela peut être fait selon les critères selon lesquels le fonctionnement partiel ou même complet du cluster ne coïncidera trop souvent pas avec l'activité du bit de sortie souhaité. Ces grappes sont susceptibles d'être supprimées. Le processus de contrôle et de suppression des clusters redondants, ainsi que leur "élagage", peut être appelé consolidation de la mémoire.

Le processus d'accumulation de nouveaux clusters est assez rapide, chaque nouvelle expérience forme plusieurs milliers de nouveaux clusters-hypothèses. Il est conseillé de mener une formation par étapes avec une pause pour "sommeil". Lorsqu'il existe un nombre critique de clusters, il est nécessaire de passer en mode de fonctionnement « inactif ». Dans ce mode, l'expérience précédemment mémorisée défile. Mais en même temps, de nouvelles hypothèses ne sont pas créées, mais seules les anciennes sont testées. À la suite du "sommeil", il est possible de supprimer un pourcentage énorme de fausses hypothèses et de ne laisser que les hypothèses qui ont réussi le test. Après le "sommeil", l'espace combinatoire est non seulement dégagé et prêt à recevoir de nouvelles informations, mais aussi orienté avec beaucoup plus de confiance dans ce qui a été appris "hier".

Sortie de l'espace combinatoire

De plus, à partir de cette activité, nous formerons la sortie sous la forme d'un hachage de l'espace combinatoire. En même temps, nous tiendrons compte du fait que plus le cluster est long, plus il y a de chances que nous ayons attrapé le modèle. Pour les clusters courts, il est possible que la combinaison de bits soit apparue par hasard en tant que combinaison d'autres concepts. Pour améliorer l'immunité au bruit, nous utiliserons l'idée de booster, c'est-à-dire que nous exigerons que pour les clusters courts, l'activation du bit de sortie ne se produise que lorsqu'il y a plusieurs opérations de ce type. Dans le cas de clusters longs, on supposera qu'une seule opération suffit. Cela peut être représenté par le potentiel qui survient lorsque les clusters sont déclenchés. Ce potentiel est d'autant plus élevé que le cluster est long. Les potentiels des points connectés à un bit de sortie sont additionnés. Si le potentiel résultant dépasse un certain seuil, alors le bit est activé.

Après un certain entraînement, la sortie commence à jouer le rôle qui correspond à ce que nous voulons obtenir (figure ci-dessous).

Progressivement, la sortie de l'espace combinatoire commence à reproduire de mieux en mieux le code de sortie requis. Après plusieurs milliers d'étapes d'apprentissage, la sortie est reproduite avec une précision assez élevée (figure ci-dessous).

Pour visualiser comment tout cela fonctionne, j'ai enregistré une vidéo avec le processus d'apprentissage. De plus, peut-être que mes explications permettront de mieux comprendre toute cette cuisine.

Renforcement des règles

Il est facile de trouver des règles pour tester les hypothèses inhibitrices et commencer à consolider les clusters récepteurs inhibiteurs.

Les clusters de freinage étant créés à des points spécifiques, ils n'affectent pas le blocage du bit de sortie en général, mais bloquent son fonctionnement à partir des règles détectées à ce point particulier. Il est possible de compliquer l'architecture des connexions et d'introduire des règles inhibitrices communes à un groupe de points ou à tous les points connectés à un bit de sortie. Il semble que vous puissiez trouver beaucoup plus de choses intéressantes, mais pour l'instant, concentrons-nous sur le modèle simple décrit.

forêt aléatoire

Cette méthode part de l'idée de "sous-espaces aléatoires". S'il y a trop de variables dans les données d'origine et que ces variables sont faiblement mais corrélées, il devient alors difficile d'isoler des modèles individuels sur la quantité totale de données. Dans ce cas, vous pouvez créer des sous-espaces qui seront limités à la fois aux variables utilisées et aux exemples d'apprentissage. Autrement dit, chaque sous-espace ne contiendra qu'une partie des données d'entrée, et ces données ne seront pas représentées par toutes les variables, mais par leur ensemble limité aléatoire. Pour certains de ces sous-espaces, les chances de trouver un motif difficile à voir dans l'ensemble de données complet sont grandement améliorées.

Ensuite, dans chaque sous-espace, un arbre de décision est formé sur un ensemble limité de variables et d'exemples de formation. Un arbre de décision est une structure arborescente (figure ci-dessous), dans les nœuds de laquelle les variables d'entrée (attributs) sont vérifiées. Sur la base des résultats de la vérification des conditions aux nœuds, le chemin du sommet au nœud terminal est déterminé, ce qui est généralement appelé une feuille de l'arbre. La feuille de l'arbre contient le résultat, qui peut être la valeur d'une quantité ou un numéro de classe.

Pour les arbres de décision, il existe différents algorithmes d'apprentissage qui permettent de construire un arbre avec des attributs plus ou moins optimaux à ses nœuds.

Au stade final, l'idée de booster est appliquée. Les arbres de décision forment un comité de vote. Sur la base de l'opinion collective, la réponse la plus plausible est créée. Le principal avantage du boosting est la possibilité de combiner de nombreux "mauvais" algorithmes (dont le résultat n'est que légèrement meilleur qu'aléatoire) pour obtenir un résultat final arbitrairement "bon".

Notre algorithme, qui exploite l'espace combinatoire et les clusters de récepteurs, utilise les mêmes idées fondamentales que dans la méthode de la forêt aléatoire. Il n'est donc pas surprenant que notre algorithme fonctionne et donne un bon résultat.

Biologie de l'apprentissage

Il existe de nombreux récepteurs différents sur la membrane neuronale. La plupart de ces récepteurs sont "flottant librement". La membrane crée un environnement pour les récepteurs dans lequel ils peuvent se déplacer librement, changeant facilement leur position à la surface du neurone (Sheng, M., Nakagawa, T., 2002) (Tovar K. R., Westbrook G. L., 2002).

Dans l'approche classique, les raisons d'une telle « liberté » des récepteurs ne sont généralement pas soulignées. Lorsque la synapse augmente sa sensibilité, cela s'accompagne du mouvement des récepteurs de l'espace extrasynaptique vers la fente synaptique (Malenka R.C., Nicoll R.A., 1999). Ce fait est tacitement perçu comme une justification de la mobilité des récepteurs.

Dans notre modèle, nous pouvons supposer que la principale raison de la mobilité des récepteurs est la nécessité d'en former des grappes à la volée. Autrement dit, l'image ressemble à ceci. Une variété de récepteurs sensibles à divers neurotransmetteurs dérivent librement à travers la membrane. Le signal d'information généré dans la mini-colonne provoque la libération de neurotransmetteurs par les terminaisons axonales des neurones et des astrocytes. Dans chaque synapse où les neurotransmetteurs sont émis, en plus du neurotransmetteur principal, il existe un additif unique qui identifie cette synapse particulière. Les neurotransmetteurs se déversent des fentes synaptiques dans l'espace environnant, grâce à quoi un cocktail spécifique de neurotransmetteurs apparaît à chaque endroit de la dendrite (points de l'espace combinatoire) (les ingrédients du cocktail indiquent les morceaux qui tombent dans le point). Ces récepteurs librement errants qui à ce moment trouvent leur neurotransmetteur dans ce cocktail (récepteurs de bits spécifiques du signal d'entrée) entrent dans un nouvel état - l'état de recherche. Dans cet état, ils disposent d'un court laps de temps (jusqu'au prochain cycle) pendant lequel ils peuvent rencontrer d'autres récepteurs "actifs" et créer un cluster commun (un cluster de récepteurs sensibles à une certaine combinaison de bits).

Les récepteurs métabotropes, et nous parlons d'eux, ont une forme assez complexe (figure ci-dessous). Ils sont constitués de sept domaines transmembranaires reliés par des boucles. De plus, ils ont deux extrémités libres. En raison de charges électrostatiques de signes différents, les extrémités libres peuvent « coller » les unes aux autres à travers la membrane. En raison de ces composés, les récepteurs sont combinés en grappes.

Après l'association, la vie conjointe des récepteurs du cluster commence. On peut supposer que la position des récepteurs les uns par rapport aux autres peut varier sur une large plage et que le cluster peut prendre des formes bizarres. Si nous supposons que les récepteurs qui travaillent ensemble auront tendance à se rapprocher les uns des autres, par exemple en raison de forces électrostatiques, une conséquence intéressante se produira. Plus ces récepteurs « conjoints » sont proches, plus leur attraction conjointe sera forte. Au fur et à mesure qu'ils se rapprochent, ils commencent à renforcer l'influence de l'autre. Ce comportement imite le comportement du filtre Hebb, qui extrait la première composante principale. Plus le filtre est réglé avec précision sur le composant principal, plus sa réponse est forte lorsqu'il apparaît dans l'exemple. Ainsi, si, après une série d'itérations, les récepteurs coopérants se retrouvent ensemble au "centre" conditionnel du cluster, et que les récepteurs "supplémentaires" sont à distance, sur ses bords, alors, en principe, ces récepteurs "supplémentaires" les récepteurs peuvent s'autodétruire à un moment donné, puis il n'y a plus qu'à se détacher du cluster. Et puis nous obtiendrons le comportement du cluster, similaire à ce qui est décrit ci-dessus dans notre modèle de calcul.

Les clusters qui ont subi une consolidation peuvent se déplacer quelque part "vers un refuge sûr", par exemple, vers la fente synaptique. Il y a un compactage post-synaptique, derrière lequel des grappes de récepteurs peuvent s'ancrer, perdant la mobilité dont ils n'ont plus besoin. Près d'eux se trouveront des canaux ioniques qu'ils pourront contrôler grâce aux protéines G. Maintenant, ces récepteurs vont commencer à influencer la formation du potentiel post-synaptique local (potentiel ponctuel).

Le potentiel local consiste en l'influence conjointe des récepteurs activateurs et inhibiteurs adjacents. Dans notre approche, les activateurs sont responsables de la reconnaissance des modèles qui demandent l'activation d'un bit de sortie, tandis que les inhibiteurs sont responsables de l'identification des modèles qui bloquent l'action des règles locales.

Les synapses (points) sont situées sur un arbre dendritique. Si quelque part sur cet arbre il y a un endroit où plusieurs récepteurs activateurs fonctionnent à la fois dans une petite zone et que cela n'est pas bloqué par des récepteurs inhibiteurs, alors un pic dendritique se produit, qui se propage au corps du neurone et, atteignant la butte axonale, provoque un pic dans le neurone lui-même. Un arbre dendritique combine de nombreuses synapses, les fermant à un neurone, ce qui est très similaire à la formation du bit de sortie d'un espace combinatoire.

Combiner des signaux provenant de différentes synapses du même arbre dendritique n'est peut-être pas un simple ajout logique, mais cela peut être plus compliqué et mettre en œuvre une sorte d'algorithme de renforcement délicat.

Permettez-moi de vous rappeler que l'élément de base du cortex est la minicolonne corticale. Dans une mini-colonne, une centaine de neurones sont situés les uns sous les autres. En même temps, ils sont étroitement enveloppés dans des obligations, qui sont beaucoup plus abondantes à l'intérieur de la minicolonne que les obligations allant aux minicolonnes voisines. L'ensemble du cortex cérébral est l'espace de telles mini-colonnes. Un neurone d'une minicolonne peut correspondre à un bit de sortie, tous les neurones d'une minicolonne corticale peuvent être un analogue du vecteur binaire de sortie.

Les groupes de récepteurs décrits dans ce chapitre créent la mémoire responsable de la recherche de modèles. Plus tôt, nous avons décrit comment créer une mémoire d'événements holographique à l'aide de clusters de récepteurs. Ce sont deux types de mémoire différents qui remplissent des fonctions différentes, bien que basés sur des mécanismes communs.

Rêver

On peut supposer que le travail principal d'élimination des grappes de récepteurs qui se sont accumulés pendant la journée a lieu pendant le sommeil. Dans le modèle informatique, nous avons décrit la procédure d'apprentissage "inactif". L'ancienne expérience est présentée au cerveau sans provoquer la formation de nouveaux clusters. Le but est de tester les hypothèses existantes. Cette vérification comporte deux étapes. La première consiste à calculer la composante principale du motif et à vérifier que le nombre de bits qui en est responsable est suffisant pour une identification claire. La seconde consiste à tester la validité de l'hypothèse, c'est-à-dire que le motif était au bon point associé au bit de sortie souhaité. On peut supposer qu'une partie des étapes du sommeil nocturne est associée à de telles procédures.

Tous les processus associés aux changements dans les cellules s'accompagnent de l'expression de certaines protéines et facteurs de transcription. Il existe des protéines et des facteurs qui se sont révélés être impliqués dans la formation de nouvelles expériences. Ainsi, il s'avère que leur nombre augmente fortement pendant l'éveil et diminue fortement pendant le sommeil.

Vous pouvez voir et évaluer la concentration de protéines en colorant une section de tissu cérébral avec un colorant qui réagit sélectivement à la protéine souhaitée. Des observations similaires ont montré que les changements les plus importants dans les protéines liées à la mémoire se produisent pendant le sommeil (Chiara Cirelli, Giulio Tononi, 1998) (Cirelli, 2002) (figures ci-dessous).

Un tel raisonnement sur le rôle du sommeil correspond bien à la caractéristique connue de tous - "le matin est plus sage que le soir". Le matin nous sommes bien mieux orientés dans ce qui n'était pas très clair hier. Tout devient plus clair et plus évident. Il est possible que nous devions cela à la compensation à grande échelle des grappes de récepteurs qui se sont produites pendant le sommeil. Les hypothèses fausses et douteuses sont supprimées, les hypothèses fiables sont consolidées et commencent à participer plus activement aux processus d'information.

Lors de la modélisation, on a vu que le nombre de fausses hypothèses est plusieurs milliers de fois supérieur au nombre de vraies. Comme l'un ne peut être distingué de l'autre que par le temps et l'expérience, le cerveau n'a d'autre choix que d'accumuler tout ce minerai d'information dans l'espoir d'y trouver des grammes de radium au fil du temps. Lorsque de nouvelles expériences sont acquises, le nombre de clusters dont les hypothèses doivent être testées ne cesse de croître. Le nombre de grappes qui se forment chaque jour et qui contiennent du minerai qui n'a pas encore été traité peut dépasser le nombre de grappes chargées de coder l'expérience éprouvée accumulée au cours d'une vie antérieure. Les ressources du cerveau pour stocker les hypothèses brutes qui doivent être testées devraient être limitées. Il semble que pendant les 16 heures d'éveil diurne, tout l'espace disponible est presque complètement obstrué par des amas de récepteurs. Lorsque ce moment arrive, le cerveau commence à nous forcer à passer en mode veille pour lui permettre de se consolider et de dégager de l'espace. Apparemment, le processus de nettoyage complet prend environ 8 heures. Si nous nous réveillons plus tôt, certains des clusters resteront non traités. D'où le phénomène que la fatigue s'accumule. Si vous ne dormez pas suffisamment pendant quelques jours, vous devrez rattraper le sommeil perdu. Sinon, le cerveau commence à supprimer "d'urgence" les clusters, ce qui ne mène à rien de bon, car cela nous prive de la possibilité de tirer des connaissances de l'expérience acquise. La mémoire des événements est susceptible d'être préservée, mais les modèles resteront non détectés.
Au fait, mon conseil personnel : ne négligez pas un sommeil de qualité, surtout si vous étudiez. N'essayez pas d'économiser sur le sommeil pour en faire plus. Le sommeil n'est pas moins important dans l'apprentissage que d'assister à des cours magistraux et de répéter du matériel dans des cours pratiques. Ce n'est pas pour rien que les enfants dans ces périodes de développement, où l'accumulation et la généralisation des informations sont les plus actives, passent la plupart de leur temps dans un rêve.

vitesse du cerveau

Nous pouvons maintenant supposer que chaque groupe de récepteurs peut fonctionner comme un élément informatique autonome, effectuant tout le cycle de calculs pour tester son hypothèse. Il peut y avoir des centaines de millions de tels clusters dans une seule colonne corticale. Cela signifie que bien que les fréquences auxquelles le cerveau fonctionne soient éloignées des fréquences auxquelles fonctionnent les ordinateurs modernes, il n'y a pas lieu de s'inquiéter de la vitesse du cerveau. Des centaines de millions de clusters de récepteurs fonctionnant en parallèle dans chaque minicolonne du cortex permettent de résoudre avec succès des problèmes complexes qui sont au bord d'une explosion combinatoire. Les miracles ne se produisent pas. Mais vous pouvez apprendre à marcher sur le bord.

Considérez l'une des options utiles offertes par Microsoft Excel. Au fait, vous pouvez acheter une version sous licence de ce programme dans notre boutique en ligne à prix réduit. Les prix et les versions peuvent être consultés.

Aujourd'hui, nous allons parler du formatage conditionnel. Il est conçu pour mettre en évidence les cellules de tableau qui ont des caractéristiques communes. Il peut s'agir d'une police, de valeurs, d'un arrière-plan identiques, etc. Cette opération prévoit différentes configurations : la sévérité du contrôle, le contenu des correspondances, leur identité et leur variabilité.

1. Commençons par lancer le programme Microsoft Excel contenant le tableau dont nous avons besoin. Ensuite, sélectionnez la plage de cellules qui doivent être traitées. Nous parlons de la similitude des colonnes et des cellules qui font partie du tableau, ou de plusieurs zones non liées du tableau.
2. Ensuite, nous devons suivre le chemin suivant :

1. Le programme dispose d'un large éventail d'options, en particulier : vous pouvez choisir la surbrillance des cellules qui sont dans la sélection, il y a une option pour sélectionner le remplissage d'arrière-plan (le programme fournit 6 solutions de couleur), des variations de polices et des cadres de tableau . Vous pouvez sélectionner "FORMAT PERSONNALISÉ", qui vous permet de créer votre propre version de la conception des cellules. Pour désélectionner les cellules correspondantes, cliquez sur OK.

Utilisation de la fonction EQUALS

Si les cellules que vous devez sélectionner ont une signification très précise, utilisez l'élément "ÉGAL" dans la liste "FORMATAGE CONDITIONNEL", située dans la section "RÈGLES DE SÉLECTION DE CELLULES". Dans la boîte de dialogue qui s'ouvre, marquez les cellules qui vous intéressent qui nécessitent une détection des doublons, et leur adresse apparaîtra dans la boîte de dialogue adjacente. En maîtrisant ces compétences simples, vous pouvez réduire considérablement le temps de traitement des données tabulaires et de regroupement des valeurs communes.

Vidéo : Rechercher des correspondances dans Excel

Ce tutoriel présente les principaux avantages des fonctions INDICE et CORRESPONDRE dans Excel, ce qui les rend plus attractifs par rapport à RECHERCHEV. Vous verrez plusieurs exemples de formules qui vous aideront à gérer facilement de nombreuses tâches complexes auxquelles la fonction est confrontée. RECHERCHEV impuissant.

Dans plusieurs articles récents, nous nous sommes efforcés d'expliquer les bases de la fonction aux utilisateurs novices. RECHERCHEV et montrer des exemples de formules plus complexes pour les utilisateurs avancés. Maintenant nous allons essayer, sinon de vous dissuader d'utiliser RECHERCHEV, puis montrez au moins d'autres moyens d'implémenter la recherche verticale dans Excel.

Pourquoi avons nous besoin de ça? - tu demandes. Oui parce que RECHERCHEV n'est pas la seule fonction de recherche dans Excel, et ses nombreuses limitations peuvent vous empêcher d'obtenir le résultat souhaité dans de nombreuses situations. D'autre part, les fonctions INDICE et CORRESPONDRE– plus flexibles et dotés d'un certain nombre de fonctionnalités qui les rendent plus attractifs que RECHERCHEV.

Informations de base sur INDEX et MATCH

Puisque le but de ce tutoriel est de montrer les capacités des fonctions INDICE et CORRESPONDRE pour implémenter la recherche verticale dans Excel, nous ne nous attarderons pas sur leur syntaxe et leur application.

Nous donnons ici le minimum nécessaire pour comprendre l'essentiel, puis nous analyserons en détail des exemples de formules qui montrent les avantages d'utiliser INDICE et CORRESPONDREà la place de RECHERCHEV.

INDEX - syntaxe et application de la fonction

Une fonction INDICE(INDEX) dans Excel renvoie une valeur d'un tableau aux numéros de ligne et de colonne donnés. La fonction a la syntaxe suivante :

Chaque argument a une explication très simple :

• déployer(tableau) est la plage de cellules à partir de laquelle extraire la valeur.
• num_ligne(rownumber) est le numéro de ligne dans le tableau à partir duquel extraire la valeur. S'il n'est pas spécifié, un argument est requis num_colonne(numéro_colonne).
• num_colonne(column_number) est le numéro de la colonne dans le tableau à partir de laquelle extraire la valeur. S'il n'est pas spécifié, un argument est requis num_ligne(numéro de ligne)

Si les deux arguments sont donnés, alors la fonction INDICE renvoie la valeur de la cellule à l'intersection de la ligne et de la colonne spécifiées.

Voici un exemple de fonction simple INDICE(INDICE):

INDEX(A1:C10,2,3)
=INDICE(A1:C10,2,3)

La formule recherche dans une plage A1:C10 et renvoie la valeur de la cellule dans 2e ligne et 3m colonne, c'est-à-dire à partir d'une cellule C2.

Très simple, non ? Cependant, en pratique, vous ne savez pas toujours de quelle ligne et de quelle colonne vous avez besoin, et vous avez donc besoin de l'aide de la fonction CORRESPONDRE.

MATCH - syntaxe et application de la fonction

Une fonction CORRESPONDRE(MATCH) dans Excel recherche une valeur spécifiée dans une plage de cellules et renvoie la position relative de cette valeur dans la plage.

Par exemple, si dans la plage B1:B3 contient les valeurs New-York, Paris, Londres, alors la formule suivante renverra un chiffre 3 , car "Londres" est le troisième élément de la liste.

EQUIV("Londres",B1:B3,0)
=EQUIV("Londres",B1:B3,0)

Une fonction CORRESPONDRE(MATCH) a la syntaxe suivante :

MATCH(lookup_value,lookup_array,)
MATCH(lookup_value, lookup_array, [match_type])

• lookup_value(lookup_value) est le nombre ou le texte que vous recherchez. L'argument peut être une valeur, y compris un booléen, ou une référence de cellule.
• lookup_array(lookup_array) est la plage de cellules dans laquelle rechercher.
• Type de match(match_type) - Cet argument indique à la fonction CORRESPONDRE si vous voulez trouver une correspondance exacte ou une correspondance approximative :
  • 1 ou non spécifié– trouve la valeur maximale inférieure ou égale à celle souhaitée. Le tableau recherché doit être trié par ordre croissant, c'est-à-dire du plus petit au plus grand.
  • 0 - trouve la première valeur égale à celle désirée. Pour combinaison INDICE/CORRESPONDRE vous voulez toujours une correspondance exacte, donc le troisième argument de la fonction CORRESPONDRE devrait être égal 0 .
  • -1 - trouve la plus petite valeur supérieure ou égale à la valeur souhaitée. Le tableau affiché doit être trié par ordre décroissant, c'est-à-dire du plus grand au plus petit.

À première vue, les avantages de la fonction CORRESPONDRE soulève des doutes. Qui a besoin de connaître la position d'un élément dans une plage ? Nous voulons connaître la valeur de cet élément !

Permettez-moi de vous rappeler que la position relative de la valeur que vous recherchez (c'est-à-dire le numéro de ligne et/ou de colonne) est exactement ce que nous devons spécifier pour les arguments num_ligne(numéro_ligne) et/ou num_colonne(numéro_colonne) fonctions INDICE(INDICE). Comme vous vous en souvenez, la fonction INDICE peut renvoyer la valeur à l'intersection de la ligne et de la colonne données, mais il ne peut pas déterminer la ligne et la colonne qui nous intéressent.

Comment utiliser INDEX et MATCH dans Excel

Maintenant que vous connaissez les informations de base sur ces deux fonctions, je pense qu'il devient déjà clair comment les fonctions CORRESPONDRE et INDICE peuvent travailler ensemble. CORRESPONDRE définit la position relative de la valeur recherchée dans la plage de cellules donnée, et INDICE utilise ce nombre (ou ces nombres) et renvoie le résultat de la cellule correspondante.

Toujours pas tout à fait clair ? Représenter les fonctionnalités INDICE et CORRESPONDRE sous cette forme :

INDEX(,(CONCORDER ( Valeur souhaitée,la colonne que nous recherchons,0))
=INDICE( colonne à partir de laquelle extraire;(CORRESPONDRE( Valeur souhaitée;la colonne que nous recherchons;0))

Je pense que ce sera encore plus facile à comprendre avec un exemple. Supposons que vous ayez la liste suivante de capitales d'État :

Trouvons la population de l'une des capitales, disons le Japon, en utilisant la formule suivante :

INDEX($D$2:$D$10,EQUIV("Japon",$B$2:$B$10,0))
=INDEX($D$2:$D$10;EQUIV("Japon";$B$2:$B$10;0))

Maintenant, décomposons ce que fait chaque élément de cette formule :

• Une fonction CORRESPONDRE(MATCH) recherche la valeur "Japon" dans une colonne B, et plus précisément dans les cellules B2:B10, et renvoie un nombre 3 , puisque « Japon » occupe la troisième place de la liste.
• Une fonction INDICE(INDEX) utilise 3 pour argumenter num_ligne(line_number), qui spécifie à partir de quelle ligne la valeur doit être renvoyée. Celles. une formule simple est obtenue :

  INDEX($D$2:$D$10.3)
  =INDICE($D$2:$D$10,3)

  La formule dit quelque chose comme ceci : regardez dans les cellules de D2 avant de D10 et extraire la valeur de la troisième ligne, c'est-à-dire de la cellule D4, puisque le comptage commence à partir de la deuxième ligne.

Voici le résultat sous Excel :

Important! Le nombre de lignes et de colonnes dans le tableau que la fonction utilise INDICE(INDEX), doit correspondre aux valeurs des arguments num_ligne(numéro_ligne) et num_colonne(numéro_colonne) fonctions CORRESPONDRE(CORRESPONDRE). Sinon, le résultat de la formule sera erroné.

Arrêtez, arrêtez... pourquoi ne pouvons-nous pas simplement utiliser la fonction RECHERCHEV(RECHERCHEV) ? Y a-t-il une raison de perdre du temps à essayer de comprendre les labyrinthes CORRESPONDRE et INDICE?

RECHERCHEV("Japon",$B$2:$D$2,3)
=RECHERCHEV("Japon",$B$2:$D$2,3)

Dans ce cas, cela n'a aucun sens ! Le but de cet exemple est purement illustratif, afin que vous puissiez comprendre comment les fonctions CORRESPONDRE et INDICE travailler en équipe de deux. Les exemples suivants vous montreront la véritable puissance du bundle. INDICE et CORRESPONDRE, qui fait facilement face à de nombreuses situations difficiles lorsque RECHERCHEV se coince.

Pourquoi INDEX/MATCH est meilleur que VLOOKUP ?

Au moment de décider quelle formule utiliser pour la recherche verticale, la plupart des gourous d'Excel pensent que INDICE/CORRESPONDRE beaucoup mieux que RECHERCHEV. Cependant, de nombreux utilisateurs d'Excel ont encore recours à l'utilisation RECHERCHEV, car cette fonction est beaucoup plus facile. C'est parce que très peu de gens comprennent pleinement tous les avantages de passer de RECHERCHEV sur un tas INDICE et CORRESPONDRE, et personne ne veut passer du temps à étudier une formule plus complexe.

Les 4 principaux avantages de l'utilisation de MATCH/INDEX dans Excel :

1. Recherche de droite à gauche. Comme tout utilisateur Excel averti le sait, RECHERCHEV ne peut pas regarder vers la gauche, ce qui signifie que la valeur recherchée doit nécessairement se trouver dans la colonne la plus à gauche de la plage étudiée. En cas de CORRESPONDRE/INDICE, la colonne de recherche peut se trouver à gauche ou à droite de la plage de recherche. Exemple : montrera cette fonctionnalité en action.

2. Ajout ou retrait de colonnes en toute sécurité. Formules avec une fonction RECHERCHEV cesser de fonctionner ou renvoyer des valeurs erronées si vous supprimez ou ajoutez une colonne à la table de recherche. Pour la fonction RECHERCHEV toute colonne insérée ou supprimée modifiera le résultat de la formule car la syntaxe RECHERCHEV vous oblige à spécifier la plage entière et le numéro de colonne spécifique à partir de laquelle vous souhaitez extraire les données.

Par exemple, si vous avez une table A1:C10, et vous souhaitez extraire des données d'une colonne B, alors vous devez définir la valeur 2 pour argumenter col_index_num(numéro_colonne) fonctions RECHERCHEV, comme ça:

RECHERCHEV("valeur recherchée",A1:C10,2)
=RECHERCHEV("valeur de recherche";A1:C10;2)

Si vous insérez ultérieurement une nouvelle colonne entre les colonnes UNE et B, alors la valeur de l'argument devra être changée de 2 sur le 3 , sinon la formule renverra le résultat de la colonne que vous venez d'insérer.

Utilisant CORRESPONDRE/INDICE, Vous pouvez supprimer ou ajouter des colonnes à la plage examinée sans déformer le résultat, car la colonne contenant la valeur souhaitée est directement définie. En effet, c'est un grand avantage, surtout lorsque vous devez travailler avec de grandes quantités de données. Vous pouvez ajouter et supprimer des colonnes sans vous soucier d'avoir à corriger chaque fonctionnalité que vous utilisez RECHERCHEV.

3. Il n'y a pas de limite à la taille de la valeur recherchée. Utilisant RECHERCHEV, soyez conscient de la limite de 255 caractères sur la longueur de la valeur recherchée, sinon vous risquez d'obtenir une erreur #VALEUR!(#VALEUR!). Donc, si la table contient de longues lignes, la seule solution viable est d'utiliser INDICE/CORRESPONDRE.

Disons que vous utilisez la formule suivante : RECHERCHEV, qui recherche dans les cellules de B5 avant de D10 la valeur indiquée dans la cellule A2:

RECHERCHEV(A2,B5:D10,3,FAUX)
=RECHERCHEV(A2,B5:D10,3,FAUX)

La formule ne fonctionnera pas si la valeur est dans la cellule A2 plus de 255 caractères. Au lieu de cela, vous devez utiliser une formule similaire INDICE/CORRESPONDRE:

INDEX(D5:D10,EQUIV(VRAI,INDICE(B5:B10=A2,0),0))
=INDEX(D5:D10,EQUIV(VRAI,INDICE(B5:B10=A2,0),0))

4. Vitesse de fonctionnement plus élevée. Si vous travaillez avec de petites feuilles de calcul, la différence de performances d'Excel ne sera probablement pas perceptible, en particulier dans les versions récentes. Si vous travaillez avec de grandes tables contenant des milliers de lignes et des centaines de formules de recherche, Excel s'exécute beaucoup plus rapidement lorsque vous utilisez CORRESPONDRE et INDICEà la place de RECHERCHEV. En général, un tel remplacement augmente la vitesse d'Excel de 13% .

Influence RECHERCHEV Les performances d'Excel sont particulièrement visibles si le classeur contient des centaines de formules matricielles complexes, telles que RECHERCHEV+SOMME. Le fait est que la vérification de chaque valeur du tableau nécessite un appel de fonction séparé RECHERCHEV. Par conséquent, plus un tableau contient de valeurs et plus votre tableau contient de formules matricielles, plus Excel est lent.

D'autre part, une formule avec des fonctions CORRESPONDRE et INDICE effectue simplement une recherche et renvoie le résultat, faisant le même travail sensiblement plus rapidement.

INDEX et MATCH - exemples de formules

Maintenant que vous comprenez les raisons pour lesquelles vous devriez apprendre les fonctions CORRESPONDRE et INDICE, passons au plus intéressant et voyons comment vous pouvez appliquer les connaissances théoriques dans la pratique.

Comment rechercher sur le côté gauche en utilisant MATCH et INDEX

N'importe quel manuel RECHERCHEV dit que cette fonction ne peut pas regarder vers la gauche. Celles. si la colonne recherchée n'est pas la colonne la plus à gauche dans la plage de recherche, il n'y a aucune chance d'obtenir de RECHERCHEV résultat désiré.

Les fonctions CORRESPONDRE et INDICE Excel est beaucoup plus flexible et ne se soucie pas de l'emplacement de la colonne contenant la valeur à extraire. Par exemple, revenons au tableau avec les capitales et la population des États. Cette fois, nous écrivons la formule CORRESPONDRE/INDICE, qui montrera quelle place occupe la capitale de la Russie (Moscou) en termes de population.

Comme vous pouvez le voir dans la figure ci-dessous, la formule fait un excellent travail dans cette tâche :

INDEX($A$2:$A$10,EQUIV("Russie",$B$2:$B$10,0))

Maintenant, vous ne devriez avoir aucun problème à comprendre le fonctionnement de cette formule :

• D'abord, utilisons la fonction CORRESPONDRE(MATCH) qui trouve la position "Russie" dans la liste :

  EQUIV("Russie",$B$2:$B$10.0))
  =EQUIV("Russie",$B$2:$B$10,0))

• Ensuite, définissez la plage de la fonction INDICE(INDEX) pour récupérer la valeur. Dans notre cas, cela A2: A10.
• Ensuite, nous connectons les deux parties et obtenons la formule :

  INDEX($A$2:$A$10;EQUIV("Russie";$B$2:$B$10;0))
  =INDEX($A$2:$A$10,EQUIV("Russie",$B$2:$B$10,0))

Indice: La bonne solution consiste à toujours utiliser des références absolues pour INDICE et CORRESPONDRE afin que les plages de recherche ne soient pas perdues lors de la copie de la formule dans d'autres cellules.

Calculs utilisant INDEX et MATCH dans Excel (AVERAGE, MAX, MIN)

Vous pouvez imbriquer d'autres fonctions Excel dans INDICE et CORRESPONDRE, par exemple, pour trouver la valeur minimale, maximale ou la plus proche de la valeur moyenne. Voici quelques options pour les formules, appliquées à une table à partir de :

1. MAX(MAX). La formule trouve le maximum dans une colonne ré C la même ligne :

INDEX($C$2:$C$10,EQUIV(MAX($D$2:I$10),$D$2:D$10,0))
=INDICE($C$2:$C$10,EQUIV(MAX($D$2:I$10),$D$2:D$10,0))

Résultat : Pékin

2. MIN(MIN). La formule trouve le minimum dans une colonne ré et renvoie la valeur de la colonne C la même ligne :

INDEX($C$2:$C$10,EQUIV(MIN($D$2:I$10),$D$2:D$10,0))
=INDEX($C$2:$C$10,EQUIV(MIN($D$2:I$10),$D$2:D$10,0))

Résultat : Lima

3. MOYENNE(MOYENNE). La formule calcule la moyenne dans la plage D2:D10, puis recherche celui qui lui est le plus proche et renvoie la valeur de la colonne C la même ligne :

INDEX($C$2:$C$10,CONCORDANCE(MOYENNE($D$2:D$10),$D$2:D$10,1))
=INDICE($C$2:$C$10,CONCORDANCE(MOYENNE($D$2:D$10),$D$2:D$10,1))

Résultat : Moscou

Points à retenir lors de l'utilisation de la fonction AVERAGE avec INDEX et MATCH

Utilisation de la fonction MOYENNE en combinaison avec INDICE et CORRESPONDRE, comme troisième argument de la fonction CORRESPONDRE le plus souvent, vous devrez spécifier 1 ou -1 au cas où vous n'êtes pas sûr que la plage visualisée contient une valeur égale à la moyenne. Si vous êtes sûr qu'il existe une telle valeur, mettez 0 pour trouver une correspondance exacte.

• Si vous spécifiez 1 , les valeurs de la colonne de recherche doivent être triées par ordre croissant et la formule renverra la valeur maximale inférieure ou égale à la moyenne.
• Si vous spécifiez -1 , les valeurs de la colonne de recherche doivent être triées par ordre décroissant et la valeur minimale supérieure ou égale à la moyenne sera renvoyée.

Dans notre exemple, les valeurs de la colonne ré sont triés par ordre croissant, nous utilisons donc le type de correspondance 1 . Formule INDICE/RECHERCHEPOO renvoie "Moscou" car la population de Moscou est la valeur inférieure la plus proche de la moyenne (12 269 006).

Comment utiliser INDEX et MATCH pour effectuer une recherche sur une ligne et une colonne connues

Cette formule équivaut à une recherche 2D RECHERCHEV et vous permet de trouver la valeur à l'intersection d'une certaine ligne et colonne.

Dans cet exemple, la formule INDICE/CORRESPONDRE sera très similaire aux formules dont nous avons déjà parlé dans cette leçon, avec une seule différence. Devinez quoi?

Comme vous vous en souvenez, la syntaxe de la fonction INDICE(INDEX) autorise trois arguments :

INDEX(tableau,num_ligne,)
INDEX(tableau, numéro_ligne, [numéro_colonne])

Et félicitations à ceux d'entre vous qui l'ont compris !

Commençons par écrire le modèle de formule. Pour ce faire, nous prenons la formule déjà familière INDICE/CORRESPONDRE et lui ajouter une autre fonction CORRESPONDRE, qui renverra le numéro de colonne.

INDEX(Votre table ,(EQUIV(, colonne à rechercher,0)),(CONCORDANCE(, chaîne à rechercher,0))
=INDEX(Votre tableau ,(MATCH( valeur pour la recherche verticale,colonne à rechercher,0)),(CONCORDANCE( valeur pour la recherche horizontale,chaîne à rechercher,0))

Veuillez noter que pour une recherche en deux dimensions, vous devez spécifier la table entière dans l'argument déployer(tableau) fonctions INDICE(INDICE).

Mettons maintenant ce modèle à l'épreuve. Vous trouverez ci-dessous une liste des pays les plus peuplés du monde. Supposons que notre tâche consiste à connaître la population des États-Unis en 2015.

Bon, écrivons la formule. Lorsque j'ai besoin de créer une formule complexe dans Excel avec des fonctions imbriquées, j'écris d'abord chacune d'entre elles séparément.

Commençons donc par deux fonctions CORRESPONDRE, qui renverra les numéros de ligne et de colonne pour la fonction INDICE:

• MATCH pour la colonne- on regarde dans une colonne B, pour être plus précis, dans la gamme B2:B11, la valeur spécifiée dans la cellule H2(ETATS-UNIS). La fonction ressemblera à ceci :

  CORRESPONDANCE($H$2,$B$1:$B$11.0)
  =EQUIV($H$2,$B$1:$B$11,0)

  4 , puisque "USA" est le 4ème élément de la liste dans la colonne B(y compris le titre).

• MATCH pour la chaîne- on cherche la valeur de la cellule H3(2015) en ligne 1 , c'est-à-dire dans les cellules A1:E1:

  CORRESPONDANCE($H$3,$A$1:$E$1,0)
  =EQUIV($H$3,$A$1:$E$1,0)

  Le résultat de cette formule sera 5 car "2015" est dans la 5ème colonne.

Maintenant, nous insérons ces formules dans la fonction INDICE et voilà :

INDEX($A$1:$E$11,EQUIV($H$2,$B$1:$B$11.0),EQUIV($H$3,$A$1:$E$1.0))
=INDEX($A$1:$E$11,EQUIV($H$2,$B$1:$B$11,0),EQUIV($H$3,$A$1:$E$1,0))

Si on remplace les fonctions CORRESPONDRE sur les valeurs qu'ils renvoient, la formule deviendra simple et compréhensible :

INDEX($A$1:$E$11,4,5))
=INDICE($A$1:$E$11,4,5))

Cette formule renvoie la valeur à l'intersection 4ème lignes et 5ème colonnes dans la plage A1:E11, c'est-à-dire la valeur de la cellule E4. Juste? Oui!

Recherche par critères multiples avec INDEX et MATCH

Dans le manuel RECHERCHEV nous avons montré un exemple de formule avec une fonction RECHERCHEV pour effectuer une recherche par plusieurs critères. Cependant, une limitation importante de cette solution était la nécessité d'ajouter une colonne auxiliaire. La bonne nouvelle : la formule INDICE/CORRESPONDRE peut rechercher par valeurs dans deux colonnes, sans avoir besoin de créer une colonne auxiliaire !

Supposons que nous ayons une liste de commandes et que nous voulions trouver la somme par deux critères - nom de l'acheteur(Client) et produit(Produit). La question est compliquée par le fait qu'un acheteur peut acheter plusieurs produits différents à la fois, et les noms des acheteurs dans le tableau sur la feuille table de recherche disposés dans un ordre aléatoire.

Voici la formule INDICE/CORRESPONDRE résout le problème:

(=INDEX("Table de recherche" !$A$2:$C$13,MATCH(1,(A2="Table de recherche"!$A$2:$A$13)*
(B2="Table de recherche" !$B$2:$B$13),0),3))
(=INDEX("Table de consultation"!$A$2:$C$13;MATCH(1;(A2="Table de consultation"!$A$2:$A$13)*
(B2="Table de consultation" !$B$2:$B$13 );0);3))

Cette formule est plus compliquée que les autres dont nous avons discuté plus tôt, mais armé de la connaissance des fonctions INDICE et CORRESPONDRE Vous la vaincrez. Le plus dur c'est la fonction CORRESPONDRE Je pense qu'il faut d'abord l'expliquer.

MATCH(1,(A2="Table de consultation" !$A$2 : $A$13),0)*(B2="Table de consultation" !$B$2 :$B$13)
EQUIV(1;(A2="Table de consultation"!$A$2:$A$13);0)*(B2="Table de consultation"!$B$2:$B$13)

Dans la formule ci-dessus, la valeur que vous recherchez est 1 , et le tableau de recherche est le résultat de la multiplication. D'accord, alors que devrions-nous multiplier et pourquoi ? Prenons tout dans l'ordre :

• Obtenir la première valeur de la colonne UNE(Client) par feuille tableau principal et comparez-le avec tous les noms d'acheteurs dans le tableau sur la feuille table de recherche(A2:A13).
• Si une correspondance est trouvée, l'équation renvoie 1 (VRAI), et sinon - 0 (MENSONGE).
• Ensuite, nous faisons la même chose pour les valeurs des colonnes B(Produit).
• Ensuite, nous multiplions les résultats obtenus (1 et 0). Ce n'est que si des correspondances sont trouvées dans les deux colonnes (c'est-à-dire que les deux critères sont vrais) que vous obtiendrez 1 . Si les deux critères sont faux, ou si un seul d'entre eux est rempli, vous obtiendrez 0 .

Maintenant vous comprenez pourquoi nous avons demandé 1 comme valeur souhaitée ? C'est vrai que la fonction CORRESPONDRE n'a renvoyé le poste que lorsque les deux critères sont remplis.

Noter: Dans ce cas, vous devez utiliser le troisième argument de fonction facultatif INDICE. C'est nécessaire, car dans le premier argument, nous spécifions la table entière et devons dire à la fonction de quelle colonne extraire la valeur. Dans notre cas, il s'agit de la colonne C(Somme) et nous avons donc introduit 3 .

Et enfin, parce que nous devons vérifier chaque cellule du tableau, cette formule doit être une formule matricielle. Vous pouvez le voir par les accolades dans lesquelles il est enfermé. Ainsi, lorsque vous avez terminé d'entrer la formule, n'oubliez pas d'appuyer sur Ctrl+Maj+Entrée.

Si tout est fait correctement, vous obtiendrez le résultat comme dans la figure ci-dessous :

INDEX et MATCH combinés avec IFERROR dans Excel

Comme vous l'avez probablement déjà remarqué (et plus d'une fois), si vous entrez une valeur incorrecte, par exemple, qui n'est pas dans le tableau en cours de visualisation, la formule INDICE/CORRESPONDRE signale une erreur #N / A(#N/A) ou #VALEUR!(#VALEUR!). Si vous souhaitez remplacer un tel message par quelque chose de plus compréhensible, vous pouvez insérer une formule avec INDICE et CORRESPONDRE dans une fonction ESLIOSHIBKA.

Syntaxe de la fonction ESLIOSHIBKA très simple:

SIERREUR(valeur,valeur_si_erreur)
SIERREUR(valeur, valeur_si_erreur)

Où est la dispute valeur(valeur) est la valeur vérifiée pour une erreur (dans notre cas, le résultat de la formule INDICE/CORRESPONDRE); et la polémique value_if_error(value_if_error) est la valeur à renvoyer si la formule génère une erreur.

Par exemple, vous pouvez mettre une fonction ESLIOSHIBKA comme ça:

SIERREUR(INDEX($A$1:$E$11,EQUIV($G$2,$B$1:$B$11.0),EQUIV($G$3,$A$1:$E$1.0)),
"Aucune correspondance trouvée. Veuillez réessayer!") =IFERREUR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1 :$E$1;0));
"Aucune correspondance trouvée. Veuillez réessayer !")

Et maintenant, si quelqu'un entre une valeur erronée, la formule donnera le résultat suivant :

Si vous préférez laisser la cellule vide en cas d'erreur, vous pouvez utiliser des guillemets ("") comme valeur du deuxième argument de la fonction ESLIOSHIBKA. Comme ça:

SIERREUR(INDEX(tableau,MATCH(valeur_recherche, tableau_recherche,0),"")
SIERREUR(INDEX(tableau,MATCH(valeur_recherche,tableau_recherche,0),"")

J'espère qu'au moins une des formules décrites dans ce tutoriel vous a semblé utile. Si vous avez rencontré d'autres tâches de recherche pour lesquelles vous n'avez pas trouvé de solution appropriée parmi les informations de cette leçon, n'hésitez pas à décrire votre problème dans les commentaires, et nous essaierons tous de le résoudre ensemble.