Transcription
1. Introduction: Nous parlerons des fonctions d'index
et de correspondance et de la puissante combinaison
des deux fonctions. Nous allons également apprendre ce qu'
est une fonction matricielle. Imaginez que dans une situation donnée,
vous deviez extraire des données de
manière dynamique à partir d'une énorme base du haut vers le bas, de la gauche vers la
droite, avec une précision de 100 %. Qu'est-ce que tu vas faire ? Index
and Match peut vous aider. Il peut survivre à l'insertion d'une colonne. Si vous insérez une colonne et que continuez à
ajouter des détails
dans les données principales, elle peut toujours survivre. Cela fonctionne parfaitement
avec la fonction array. Maintenant, qu'est-ce qu'une fonction matricielle ? Un tableau est un Excel qui prend en compte plusieurs conditions
en même temps. Il est utilisé pour des calculs plus
complexes. Lorsque nous utilisons un tableau,
Excel arrête de penser cellule par cellule et commence à
penser de manière plus logique Au lieu de me demander quelle est
la valeur de cette cellule, Excel me demande quelle valeur répond à ma condition.
Il s'agit d'une fonction matricielle. Dans ce cours, nous aborderons d'
autres concepts d' Excel, tels que la validation
des données spatiales sur les salaires , le formatage des
conditions, le concept de verrouillage de la cellule et bien plus encore.
On se voit en classe.
2. Fonctions d'index + correspondance: Tout d'abord, nous verrons les principes de base de l'index et des fonctions de correspondance
individuellement et à quoi il sert. Et puis, si nous le combinons, quels résultats étonnants pouvons-nous obtenir. Bien, commençons donc les bases de
l'
index et de la correspondance. Comme je l'ai dit,
l'index renvoie la valeur et le match
renvoie la position. Maintenant, laissez-moi taper la
fonction sous forme d'index, et ce sera
mon tableau complet. Et disons que le deuxième
est le chiffre que je veux. Donc le deuxième numéro que
nous avons est James. Je vais obtenir ces détails. Parlons de match et mon
apparence de valeur est peut-être James, et ce sera l'ensemble de
mon tableau de tables. Et zéro pour une correspondance exacte. Encore une fois, je reçois également le
deuxième numéro en tant que James. Supposons qu'au lieu de cela, si je tape cette cellule, entrons, et si j'en tape deux. Donc ici, à partir de cette cellule,
je peux donner des commandes. Disons trois, non ? Il va donc être
remplacé en conséquence. Il s'agit donc d'une compréhension très
basique de la fonction d'index et de correspondance. Nous allons maintenant voir comment la magie
peut agir en combinaison. Nous avons des détails
ici, disons, euh, les détails de certains
employés sur le côté gauche et les détails de
leur identifiant d'employé respectivement sur le côté droit. De même, nous avons les identifiants des employés sur le côté gauche et nous voulons obtenir des informations sur le côté droit, et ils doivent
correspondre en conséquence. Maintenant, Look up ne peut pas fonctionner ici car il
ne fonctionne que de gauche à droite. Bien, nous allons donc utiliser les fonctions
index et match. Permettez-moi de commencer par index folkson et mon tableau de tables
sera « Je veux les noms ici Je vais taper des noms, sélectionner des noms et verrouiller la cellule.
Pourquoi est-ce que je verrouille ? Parce que je veux faire glisser ça. OK. Alors permettez-moi
d'utiliser la fonction match. Je veux faire correspondre les cellules. Avec toutes ces cellules,
toutes les cellules seront verrouillées et zéro pour une correspondance
exacte Fermez les deux
crochets et entrez. Et laissez-moi suivre cela
pour obtenir toutes les réponses. Et nous voilà avec
toutes les réponses 1024 c'est 1024 c'est Jessica. Et 1028 c'est Michael, non ? Donc, en conséquence,
nous obtenons ceci. Maintenant, ce sont les
erreurs système que nous recevons. Supposons que dans une énorme base de données, nous voulions que
notre propre erreur modifiée et personnalisée apparaisse et non l'erreur système. Nous voulons également que cela soit mis en évidence afin de mettre en évidence les erreurs contenues dans les énormes
données et de déterminer comment nous pouvons le faire. Pour nos erreurs personnelles, ce que nous allons faire, nous ajouterons une fonction supplémentaire ici en cas d'erreur. Et je vais l'adorer. Au lieu d'obtenir une erreur systématique
par défaut dans Excel, je ne suis pas trouvé
en tant qu'erreur personnalisée. Et comment puis-je le souligner ? Nous utiliserons le
formatage conditionnel. Nous allons laisser cette partie
rester sélectionnée, passer au formatage des conditions
et au texte qui le contient. Non trouvé. Ici, nous pouvons également modifier et personnaliser la
couleur dans laquelle nous voulons que les détails soient affichés, nous allons
donc la laisser rester rouge. Et nous y voilà, disons, si nous supprimons ceci, disons que si nous
écrivons un nombre hypothétique, nous allons obtenir l'erreur,
l'erreur personnalisée
que nous avons sélectionnée J'espère que la combinaison d'indice et de
correspondance
que nous pouvons utiliser dans une situation
donnée est
très claire jusqu' combinaison d'indice et de
correspondance
que à présent. Utilisons maintenant quelques
correspondances, comme
un index, avec quelques conditions. Nous avons un autre exemple. Où nous avons les noms
des étudiants écrits ici, leurs matières respectives et leurs
notes respectives en conséquence. Nous voulons maintenant que les
noms des matières apparaissent ici et que le
nom de l'étudiant, si vous le sélectionnez, nous devrions avoir la réponse. Maintenant, pour les noms des sujets, je vais sélectionner le
sujet comme Control C, copier, et je vais faire
le paiement spécial. C'est ce qu'on appelle la transposition. C'est pay spatial.
Il sera transposé de la verticale à l'
horizontale et vice versa. Je peux aussi taper des noms
ici, mais j'ai écrit. Je l'ai créé sous
forme de liste déroulante. Je peux passer à la validation des données. Ici, et à partir de
là, je peux sélectionner la liste et les sources. J'espère donc que c'est
beaucoup plus facile. Commençons maintenant par
index et match. Maintenant, nous voulons connaître les noms. Si vous sélectionnez le nom d'un
étudiant en particulier et le sujet, nous voulons que leurs
notes respectives apparaissent ici. Nous avons deux conditions. Dans l'ensemble de ce tableau, nous voulons rechercher les noms
des étudiants ainsi que leurs
matières, respectivement. Nous utiliserons un index et
quelques correspondances. Disons indexer la table entière.
Je vais le verrouiller. Pourquoi est-ce que je verrouille ? Parce que
je veux le faire glisser. Alors je veux égaler. Maintenant, par quoi vais-je faire correspondre les noms en premier ? Je vais à nouveau l'enregistrer. Où apparaissent les noms
dans la deuxième colonne ? Encore une fois, je vais enregistrer cela,
sélectionner et verrouiller ce contrôle. Alors j'utiliserai une autre allumette. Parce que maintenant j'ai fait
les ajustements pour les noms, mais pas pour les sujets. Je vais sélectionner le sujet, mais je ne le verrouillerai pas car je ne veux le faire glisser que verticalement. n'y a pas de
déplacement horizontal. Faites correspondre les sujets. Maintenant, où sont les sujets
sur la première ligne ? Un 12f. Encore une fois, je bloquerai ce
zéro pour Exact Match, je fermerai les deux
crochets et me voilà. Si nous examinons les notes de l'
histoire de Sara, nous en avons 73. Maintenant que nous l'avons
également verrouillé, nous pouvons le faire glisser. Vous pouvez comparer toutes
les notes données ici. Permettez-moi de changer le nom en Sima et
voici les noms Permettez-moi de changer le nom pour
« Je reçois les noms ici ». Je trouve les
noms ici, non ? Voici donc comment nous pouvons utiliser les quelques
fonctions de ce type. C'est ainsi que nous pouvons compléter, indexer et faire correspondre la fonction. Nous pouvons maintenant
rechercher une fonction matricielle.
3. Fonction de tableau: Nous allons maintenant voir
ce qu'est un tableau ? Ce qu'on appelle une matrice, ce support bouclé, ces bretelles Au lieu de taper Enter, en appuyant sur Entrée après une fonction, nous appuyons sur Control Shift Enter. s'agit donc d'un tableau qui utilise plusieurs conditions avec
plusieurs critères. Jetez-y un coup d'œil.
Regardons maintenant le tableau. Maintenant, on nous donne ce tableau où nous avons les
noms des employés ici, puis leurs
départements respectifs. Et si vous observez, il
existe des noms courants dans
certains départements. Supposons que Peter Gratia travaille
dans le domaine de la procréation assistée et que d'autres travaillent dans SAS Il faut donc en tenir compte, non ? Ensuite, on nous donne le salaire, les
indemnités de performance et le total. Maintenant, que voulons-nous ici ? Nous voulons que les noms
apparaissent ici, et nous voulons également que les
départements apparaissent ici. Donc, si vous sélectionnez
les noms et le département, nous devrions obtenir le salaire et
les indemnités de performance. Maintenant, pour les noms, j'ai
créé une liste déroulante. Et pour le département également, j'ai créé une liste déroulante. Maintenant, pour les noms, comme il est très courant que je passe à la validation
des données, sélectionnez la liste et sélectionnez
la source complète 2-13 Ce sont les noms, non ? Mais que vais-je faire
pour le ministère ? Parce qu'il n'y a aucune source. Ils ont une
bonne réputation. Donc, au lieu de sélectionner une source, je vais écrire personnellement les noms des
départements avec une virgule, afin que cela devienne également une
sorte de liste déroulante, non ? Maintenant, nous avons Alice
Johnson des opérations. Maintenant, nous avons remporté la
performance des ventes. Comme si nous avions obtenu
le détail des ventes ici avec ces
deux critères. Ce que nous allons faire, nous allons commencer par les fonctions d'
index et de correspondance. Maintenant array, ce qu'il me demande
est array. Maintenant, quel sera le
tableau, le tableau devrait être compris entre C un et C 13 parce que c' est
le salaire et nous
voulons le salaire ici. Nous allons maintenant utiliser match. Nous avons maintenant deux correspondances, les noms et le département. Je peux utiliser la concaténation
ici avec la première, deuxième condition, entre crochets, puis je veux
rechercher un tableau de Qu'est-ce que je vais faire ? Pour les noms, je vais sélectionner l'intégralité de cette
recherche dans le tableau comme noms et euh je vais utiliser et
j'utiliserai department. Donc pour b12b 13, ce
sera mon département,
zéro pour une correspondance exacte, zéro pour une correspondance exacte, quelques fermetures de Blackett,
et voici Je veux appuyer sur Ctrl, Shift Enter au lieu de Enter. Control Shift Enter, et je vais
regarder les résultats. Alice Johnson, de l'exploitation,
gagne 500 dollars par salaire. Maintenant, pour les performances également, je ne peux pas faire glisser cette
fonction de tableau de fonctions. Donc, pour les performances également, je vais utiliser la même
méthode, index et match. Mais pour des raisons de performance, le tableau
sera un d12d 13 match, concat net de ces deux
noms Fermez les crochets, les
noms apparaissent 1 à 13 et les départements apparaissent de
B un à B 13, zéro pour une correspondance exacte,
fermez avec les crochets, puis entrez le changement
de commande Ici, je reçois les détails. Voyons d'autres noms. Disons que nous avons Peter, nous utiliserons Peter
et qu'il travaille pour les ressources humaines. Peter des RH
reçoit donc 300 dollars en prime, 30 dollars en allocation ou 300 dollars en
salaire et 30 en allocation Et Peter, du service des ventes,
reçoit un salaire différent. Et voici
Peter, du service des ventes. De même, nous avons
vu comment effectuer la fonction matricielle avec
plusieurs conditions et plusieurs résultats. Et voyez les détails, vous savez,
euh, les documents importants ne
peuvent jamais être téléchargés
vers des outils d'IA, n'est-ce pas ? Nous avons besoin d'une compréhension humaine
et de calculs humains, et j'espère que je
viens de vous aider à
comprendre cette fonction. Accédez au
projet et faites-le.
4. Projet: Maintenant, pour ce qui est
du projet de ce cours, on m'a donné
ce tableau et ce qu'il contient,
il contient des noms d'iPhone, des noms aléatoires
hypothétiques
sans numéro de modèle,
mais il s'agit
en fait de noms de sociétés et des noms Poco et
Opo qui s'y trouvent Poco et
Opo qui s'y répétition, si
vous
observez, Maintenant, nous avons les versions
également indiquées ici, la capacité de stockage, la quantité et le coût. Qu'est-ce que je veux ? Je veux que le numéro
de modèle apparaisse ici sous
forme de liste déroulante, l'iPhone devrait apparaître ici Pim Je veux que les versions apparaissent. Toutes les versions dont ils
disposent devraient apparaître ici, et la capacité de stockage devrait apparaître ici
sous forme de liste déroulante, vous le ferez avec la validation
des données. Qu'est-ce que je veux d'autre ? Je veux que vous écriviez
ici
une fonction matricielle avec une combinaison d'index
et de
correspondance, et
je veux que
lorsque je sélectionne un modèle, lorsque je sélectionne une version, lorsque je sélectionne un stockage
dans la liste déroulante, si les trois conditions
sont remplies, je veux
que les données de
ce tableau soient ici, quelle est la quantité
de ces trois conditions ? Hein ? J'espère donc qu'
il est très clair que vous allez
aborder la
même chose en termes de coûts. Vous devez faire correspondre l'
indice d'utilisation et la correspondance et déterminer quel sera le coût si trois conditions
sont satisfaites. Faites-le donc vous-même
et téléchargez-le dans la galerie de projets.
Rendez-vous au prochain cours.
Aamir Shaikh, Freelancer, Youtuber, Trainer
