Le principe de dichotomie de la fonction RECHERCHE dans l'apprentissage des fonctions Excel

Dans l'article précédent "Apprentissage de la fonction Excel : 5 façons d'utiliser la fonction RECHERCHE", nous avons découvert les 5 façons d'utiliser la fonction RECHERCHE. Je suppose que beaucoup d'amis ne l'ont pas compris. Aujourd'hui, je vais vous expliquer les deux parties. de LOOKUP en détail.Après avoir compris les principes de la méthode, revenez en arrière et lisez le tutoriel d'hier. Je pense que vous aurez une compréhension différente de LOOKUP.

Dans l'article précédent, nous avons appris diverses routines de la fonction LOOKUP, et avons également mentionné à plusieurs reprises que la recherche de la fonction LOOKUP est basée sur la méthode de dichotomie. Alors, quelle est la méthode de dichotomie ? Cette question.

Utilisons encore l'exemple d'hier : recherchez les résultats par numéro de série, les numéros de série sont par ordre croissant, et le résultat de la formule =LOOKUP(J2,A2:D19) est correct.

1. Le principe de la recherche binaire

La recherche binaire consiste à diviser les données dans la plage de recherche en deux en fonction du nombre pour trouver une donnée en position médiane, la valeur médiane, puis à utiliser notre recherche Comparez la valeur à la valeur moyenne. Lorsque la valeur médiane est égale à la valeur de recherche, le résultat est obtenu directement ; lorsque la valeur médiane est inférieure à la valeur de recherche, la comparaison de recherche binaire se poursuit vers le bas (c'est-à-dire que la recherche binaire se poursuit dans la moitié inférieure de la valeur de recherche). données excluant la valeur moyenne) ; lorsque la valeur intermédiaire est supérieure à la valeur de recherche, la comparaison de recherche binaire continue vers le haut (c'est-à-dire que la recherche binaire continue dans la moitié supérieure des données excluant la valeur intermédiaire). Si aucune donnée égale à la valeur de recherche n'est trouvée après la dichotomisation jusqu'à la dernière donnée : si la dernière donnée est inférieure à la valeur de recherche, utilisez la position de la dernière donnée pour obtenir la valeur du résultat si la dernière donnée est supérieure à la valeur de recherche ; valeur de recherche, puis recherche vers le haut. Les données dont la position est la plus proche des dernières données sont inférieures ou égales à la valeur de recherche, puis le résultat est obtenu en fonction de la position de ces données.

Ce n'est qu'une explication et je pense que beaucoup d'amis seront confus. Combinons les exemples ci-dessus pour voir en détail comment trouver le score chinois de 79 jusqu'au numéro de série 5.

Première comparaison : il y a 18 données dans la plage de recherche A2~A19, et la position médiane est 18÷2=9, c'est-à-dire que la valeur médiane est 9 dans la cellule A10. Évidemment, la valeur de recherche 5 est inférieure à 9, alors continuez à rechercher vers le haut dans A2~A9

Astuce : si le nombre de données dans la plage de recherche est un nombre impair, la position médiane est (nombre + 1) ÷ 2 ; Par exemple, s'il y a 11 lignes, la position médiane est (11+1)÷2=6 ; si le nombre de données est un nombre pair, la position médiane est (nombre)÷2 ;

Deuxième comparaison : il n'y a que 8 données, la position médiane est 8÷2=4, la valeur médiane est 4 dans la cellule A5, la valeur de recherche 5 est supérieure à 4, alors continuez à chercher dans A6~ A9. Notez qu'il n'y a que quatre chiffres ci-dessous pour le moment et que les données inférieures à 9 sont directement exclues de la première recherche.

La troisième comparaison : 4 données, la valeur moyenne est 6 de A7, la valeur de recherche 5 est inférieure à 6, donc recherchez vers le haut. À ce stade, il ne reste qu'une seule donnée, 5 dans la cellule A6, qui est cohérente avec la valeur de recherche, donc les données 79 dans la colonne D correspondant à 5 sont obtenues.

Il est très difficile de comprendre la dichotomie à travers un tel exemple. Regardons un autre exemple. Classez les données dans le tableau ci-dessus par ordre décroissant des scores ou recherchez les scores chinois par numéro de série 5. La formule n'a pas besoin d'être modifiée. Étant donné que l'ordre de la colonne du numéro de série n'est pas ordonné et n'est pas classé par ordre croissant, une erreur se produit. Le nombre réel est 79 et la formule donne 94. Que se passe-t-il ? Regardons cela à travers la dichotomie.

La première recherche : la valeur du milieu (la 9ème donnée) est 18, la valeur de recherche 5 est inférieure à 18, donc recherchez vers le haut dans A2~A9

La deuxième recherche : les 8 données ci-dessus, le milieu ; La valeur (la 4ème donnée) est 8, la valeur de recherche 5 est inférieure à 8, continuez à chercher vers le haut dans A2~A4

La troisième recherche : les 3 données ci-dessus, la valeur médiane est 1, la recherche la valeur 5 est supérieure à 1. Recherche vers le bas :

La quatrième recherche : il n'y a maintenant qu'une seule donnée 7 dans la cellule A4, et la valeur de recherche 5 est inférieure à 7, utilisez donc 7 comme référence, trouvez une position la plus proche de 7 et la valeur est inférieure à 5 ou Les données égales à 5 sont 1 dans la cellule A3, et la valeur chinoise correspondante obtenue est 94.

A travers ces deux exemples, je pense que chacun devrait avoir une certaine compréhension de la dichotomie Concernant ce principe, il n'y a qu'une seule phrase dans la description de la fonction :

.

Dans les applications pratiques, nous n'avons pas à nous soucier de la dichotomie, de la position médiane, de savoir si regarder vers le bas ou vers le haut, c'est tout le travail de la fonction, nous devons seulement nous rappeler une chose : le les données doivent être organisées par ordre croissant. Si elles ne peuvent pas être organisées par ordre croissant, suivez la routine de recherche précise de LOOKUP pour concevoir la formule.

2. LOOKUP implémente l'arrondi des données

C'est toute l'introduction au principe de dichotomie. Ensuite, nous devons résoudre les deux problèmes restants auparavant.

Dans l'article du 12 mai, nous avons utilisé LOOKUP pour résoudre un problème d'arrondi. Du coup, tout le monde a laissé des messages demandant une explication :

Alors, quelle est cette formule qui a suscité de vives discussions chez tout le monde ? Regardez l'image ci-dessous :

Il s'avère que cette formule utilise la fonction LOOKUP pour arrondir tous les nombres en dessous de la centième place, obtenant ainsi un arrondi centile.

Après avoir compris le principe de dichotomie, il est temps pour LOOKUP de l'expliquer à tout le monde. Tout d’abord, expliquez la partie ROW (A:A)*100. En fait, il s’agit simplement d’un ensemble de chiffres. Afin que tout le monde comprenne, réduisons la plage de A:A, nous utilisons =ROW(A1:A31)*100 comme explication :

Bien que vous ne puissiez voir qu'un seul 100 dans la cellule, il y a en fait 31 nombres, nous pouvons utiliser la touche de fonction f9 pour voir le contenu spécifique :

La fonction ROW est utilisée pour obtenir le numéro de ligne de la cellule ROW(A1:A31)*100 est multiplié par les numéros de ligne de. cellules A1 à A31. En utilisant 100, nous obtenons un ensemble de données arrondies aux chiffres des centaines {100;200;300;...3000;3100}.

Puis LOOKUP est entré sur scène. Il recherche A1 dans le tableau obtenu ci-dessus qui a été arrondi à des centaines de chiffres. Étant donné que le tableau est classé par ordre croissant, l’essence de la recherche de A1 est de trouver la valeur maximale inférieure ou égale à A1 dans le tableau.

En prenant 2517,32 comme exemple, seulement 2500 est la valeur maximale inférieure à elle, donc le résultat est 2500. Les amis intéressés peuvent l’essayer eux-mêmes en utilisant le principe de dichotomie pour voir si c’est correct.

Il en va de même pour trouver d’autres numéros. La raison pour laquelle cette formule est astucieuse est qu'elle transforme un problème d'arrondi en un problème de recherche de références. C'est vraiment merveilleux !

3. LOOKUP pour l'extraction de données

Nous avons de nouveau utilisé LOOKUP pour l'extraction de données, nous avons donc eu un rendez-vous de 5000 mots :

Quel est le problème cette fois ? La question originale extrait le numéro d'étudiant comme indiqué dans l'image :

Utilisons les premières données de l'image ci-dessus pour expliquer. Entrez « 10 Zhang Yong a » dans la cellule N1, puis entrez la formule =-LOOKUP(1,-LEFT(N1,ROW(1:9))) dans la cellule O1 pour extraire le numéro d'étudiant.

LOOKUP n'est-il pas une fonction de référence ? Comment peut-elle extraire à nouveau des nombres ?

Le deuxième paramètre de la fonction GAUCHE utilise un tableau, ROW(1:9) est équivalent à {1;2;3;4;5;6;7;8;9}. LEFT extrait les données spécifiées par le premier paramètre à partir de la gauche et la longueur de l'extraction est déterminée par le deuxième paramètre. LEFT extrait selon le tableau {1;2;3;4;5;6;7;8;9} et obtient 9 résultats :

, c'est-à-dire extraire le chiffre 1 de la gauche, le chiffre 2.. .jusqu'à 9 bits. Étant donné que les résultats extraits par LEFT sont tous des types de texte, l'ajout d'un signe négatif devant LEFT peut convertir les numéros de texte en valeurs numériques, et le texte devient une valeur d'erreur :

La valeur d'erreur est ignorée par LOOKUP, et maintenant cela devient Il devient trouver 1 dans {-1; -10}. Selon le principe de la recherche binaire, après la bissection, nous ne pouvons rechercher que vers le bas jusqu'à ce que le dernier chiffre soit inférieur à 1. Par conséquent, nous pouvons simplement comprendre que lorsque la valeur de recherche est supérieure à toutes les données de la plage de recherche (qu'elle soit ou non par ordre croissant), l'essence de LOOKUP est de trouver les dernières données.

En fait, il est également possible de changer 1 dans la formule en 0, car 0 est aussi plus grand que tous les nombres négatifs :

Le dernier nombre actuel est -10, on ajoute donc un signe négatif devant RECHERCHE et cela devient Cela devient 10, qui est le nombre que nous devons extraire.

Pour les débutants, l'utilisation de LOOKUP dans les deux cas ci-dessus est trop avancée. Même à travers ces introductions, on estime qu'ils n'ont qu'une compréhension partielle. En fait, l'apprentissage des fonctions est un processus de compréhension en compréhension. la maîtrise, ici Cela demande beaucoup de pratique et de réflexion. Tant que chacun maintient une attitude positive et optimiste et peut expérimenter le plaisir des fonctions d'apprentissage, le succès n'est pas loin.

Recommandations d'apprentissage associées : Tutoriel Excel

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!