Transcription
1. Introduction - Les formules d'index et de correspondance: Bonjour à tous. Je suis Jeremy, ou comme certains m'appellent Big Dog. Bienvenue dans mon cours
Skillshare sur l'index et les formules de match. Avant de sauter dans le contenu, laissez-moi prendre une seconde
pour me présenter. Je suis dans le monde du
travail depuis près de sept ans et
tout ce temps a été passé dans Excel pendant mon séjour université à mon poste
actuel aujourd'hui, Excel fait partie
de ma boîte à outils quotidienne. J'ai développé une passion pour l' analytique et je m'
intéresse à
trouver les solutions les plus efficaces et à aimer l'automatisation. Mon objectif pour cette
série de vidéos est de faire de vous un expert en matière d'index
et de formules de correspondance. Les
formules d'index et de correspondance, lorsqu'elles sont combinées, vous
fournissent une
fonction Lookup qui surpasse
les recherches
VLookup et H et garantit vos calculs ne
changeront pas fonction de
lignes supplémentaires ou la correspondance d'index peut augmenter
l'efficacité des feuilles de calcul
ad hoc et des modèles de coûts
complexes. Rejoignez-moi dans cette série alors que nous
débloquons le
niveau suivant du
potentiel de votre cellule et laissons les autres
fonctions de recherche dans la poussière. Allons-y après.
2. Les bases de l'index et des correspondances - Partie 1 : Correspondance précise: Bonjour à tous, Bienvenue dans la
première vidéo de la série. Dans les prochaines vidéos, nous couvrirons l'
index et les formules de match. La formule d'index nous permet de définir une plage de cellules et nous
permet de renvoyer la valeur d'une cellule à partir de
cette plage , compte tenu des coordonnées
des cellules. La formule de correspondance demande une valeur de recherche et
la plage définie, ou la valeur de recherche apparaît, la formule de correspondance renvoie
ensuite la colonne ou le numéro de
ligne de la plage définie ou de la recherche la valeur est trouvée. Bien que ces formules puissent
être utilisées seules, le cas d'utilisation le plus courant
est celui où elles sont combinées. Au cours des prochaines vidéos, nous examinerons
quelques exemples
des différentes façons dont ces
deux formules peuvent être utilisées. Plongeons dedans. Dans cet exemple,
nous avons un tableau des factures pour mars qui
manque à la ville client. Nous aimerions extraire
la ville client de la table de choix vers
la table de travail. Nous pouvons utiliser l'indice combiné et les formules de correspondance pour nous aider. La formule d'index
commence par demander le rayon IRB qui contient
les données souhaitées, soit B3 à B6
dans la table de choix. Notez que cela sera défini comme référence
absolue afin que la plage ne change pas lorsque nous
copions la formule dans d'autres cellules. Ensuite, la formule d'index demande
le numéro de ligne dans le tableau que nous venons de définir de la cellule où se trouvent les données
souhaitées. Parce que le numéro de
ligne spécifique de la ville que nous voulons renvoyer
changera en fonction du client. Nous aurons besoin du
numéro de ligne pour être dynamique. Pour cela, nous utiliserons
la formule de correspondance. La formule de correspondance demande
d'abord la valeur de recherche dix. Ensuite, il nous demande d'
entrer le tableau de recherche, qui sera A3 à six. Tout comme le tableau d'index ci-dessus, il sera également défini sur
une référence absolue. Enfin, il
demande le type de match, qui dans ce cas
sera 0 car nous voulons une correspondance exacte
inférieure et supérieure matchs soient
expliqués dans les vidéos ultérieures. Mais sachez que ceux-ci
sont généralement utilisés
uniquement pour faire correspondre les nombres. La formule d'index comporte un numéro de colonne
d'entrée facultatif final que nous n'
utiliserons pas dans cet exemple, mais nous
examinerons plus tard. Ok, nous avons fini
la formule. Nous allons le copier et voir si nous trouvons les
bonnes villes. Fantastique. Maintenant, comme
cette combinaison de formules peut être un peu déroutante, passons rapidement en revue
ce qui s'est passé. abord, nous utilisons la
formule d'index pour obtenir la gamme de cellules où se trouvent les informations de
la ville cliente que nous voulions. Comme nous ne voulions pas
entrer manuellement le numéro de ligne de la ville client pour
chaque ligne de facture, nous utilisons la formule de correspondance pour renvoyer
dynamiquement
le numéro de ligne. La formule de correspondance renvoie le numéro de ligne
du client dans la table de choix en fonction la valeur de recherche
fournie à partir de la table de travail Nous avons pu utiliser
le numéro de ligne que le formule de correspondance fournie en entrée dans la formule
d'index pour ensuite renvoyer la ville du
client. Gardez à l'esprit que les formules d'
index ou les rayons et le tableau
de recherche des formules de correspondance doivent correspondre aux mêmes valeurs de ligne. Dans notre cas, les rangées
trois
à six, afin que les formules
fonctionnent correctement ensemble. Très bien, passons à un autre exemple pour consolider
notre compréhension.
3. Les bases de l'index et des correspondances - Partie 2 : Moins que l'assortiment: Bonjour à tous, Bienvenue dans la
deuxième vidéo de la série. Dans cette vidéo, nous
allons nous appuyer sur la combinaison de
l'index et des
formules de correspondance et
approfondir ce que la formule de match peut
faire. Allons-y. Dans cet exemple,
nous avons une liste de clients et des
ventes annuelles réalisées par chacun. Pour chaque client, nous voulons déterminer quels sont leurs
rabais fonction des
déchirures de rabais associées
aux objectifs de vente annuels
dans la table de choix. Comme dans la vidéo précédente, la formule d'index
commence par le tableau des données souhaitées, B3 à B8. Ensuite, pour que
la formule d'index soit un
numéro de ligne Input Dynamic, nous devons utiliser la formule de
correspondance. Dans l'exemple vidéo précédent, nous utilisons le client comme
valeur de recherche et 0 correspond exactement
comme type de correspondance. Dans cet exemple, nous
allons utiliser un nombre, ventes
annuelles comme valeur de
référence dans
une valeur inférieure à celle du type de correspondance. Passons à travers ça. Le type de correspondance
sera le chiffre d'affaires annuel entre le tableau de recherche de trois à huit, et le type de correspondance sera un. Passons rapidement en revue ce qui
se passe dans la formule de match. Cette fois, le
matelas prend d'abord la valeur de
référence des ventes annuelles et la fait
correspondre aux
ventes annuelles de la gamme de recherche. Puisque nous avons entré
moins que comme type de correspondance, la formule de correspondance
va trouver la valeur la plus proche dans le tableau de recherche
inférieure à notre valeur de recherche. Il renvoie ensuite
le numéro de ligne de la correspondance la plus proche inférieure à la correspondance, et il sera
introduit dans la formule d'index tant que numéro de ligne dynamique. Nous avions déjà donné
la formule d'index, le tableau de données. Et maintenant, avec un
numéro de ligne dynamique de la formule de correspondance, il renvoie le
contenu de la cellule dans la
position des numéros de lignes dynamiques dans le tableau. Nous allons copier la
formule pour voir si nous avons obtenu les
résultats attendus. Génial, tout a l'air bien. Passons
à l'exemple suivant.
4. Les bases de l'index et des correspondances - Partie 3 : Indice Match: Bonjour à tous, Bienvenue dans la
troisième vidéo de la série. Dans cette vidéo,
nous allons ajouter un calque supplémentaire
à la combinaison des
formules d'index et de correspondance et d'une formule de
correspondance supplémentaire. Dans les deux derniers exemples, nous avons cherché à renvoyer
uniquement la ligne numéro deux, la formule d'index pour
obtenir les données souhaitées. Dans cet exemple, nous
allons utiliser pour faire correspondre formules pour renvoyer les numéros de
ligne et de colonne. Cela nous permettra de renvoyer
une plus large gamme de données avec un peu plus de travail
à l'avance. On y va. Dans cet exemple,
nous avons un tableau des factures pour mars qui
manque les clients, la
ville, l'état et le code postal. Nous utiliserons les informations de la table de choix pour
remplir notre table de travail. Contrairement aux
deux autres exemples de vidéos, nous commencerons par un tableau d'
index comprenant des lignes et des colonnes, B3 à D6. Les lignes et
les colonnes sont verrouillées dans le tableau d'index. Une note importante ici,
tout au long de cette formule, il sera très important de
prêter attention aux
types de référence des cellules. Certaines cellules auront les
lignes et les colonnes verrouillées dans d'autres, nous n'aurons que des
lignes ou des colonnes verrouillées. Si le type de
référence de cellule incorrect est utilisé, la formule ne
retournera pas ce que nous voulions. Ensuite, nous utiliserons la première formule de correspondance pour renvoyer
dynamiquement
le numéro de ligne. La première valeur de recherche
des formules de correspondance sera le client à dix. Cette valeur de recherche
aura la colonne verrouillée, mais la ligne dix est ouverte, le tableau de recherche
sera de A3 à A6 avec les lignes
et les colonnes verrouillées. Enfin, le type de correspondance sera 0 car nous voulons
une correspondance exacte. Pour la deuxième formule de correspondance
pour renvoyer le numéro de colonne. La valeur de recherche sera
la ville d'en-tête de colonne. Dans la cellule C9. La ligne neuf de cette cellule sera verrouillée, mais les colonnes C s'ouvrent. Le tableau de recherche
sera B2 à D2, les lignes
et les colonnes étant verrouillées. Enfin, le type de correspondance sera à nouveau 0 car nous
voulons une correspondance exacte. Copions cette formule dans
le reste des cellules de
la colonne ville, ainsi que dans les colonnes d'état et de
code postal. Doux, tout
fonctionne comme prévu. Revenons un peu et parlons du fonctionnement de cette
formule. Nous avons commencé la
formule de correspondance d'index en indiquant la plage de cellules contenant les données que nous voulons
éventuellement renvoyer. Remarque rapide, nous voulons nous assurer
que cette plage de
cellules n'inclut données que nous voulons
renvoyer et n'
inclut pas les valeurs de recherche que
nous utiliserons pour obtenir les données. D'accord ? Maintenant que nous avons
la plage de cellules contenant nos données
renvoyées à trouver, la formule d'index nécessite désormais la
fois le numéro de ligne et le numéro de colonne
où les données sont stockées. Au lieu de donner des
nombres statiques comme la ligne 1 et la deuxième colonne pour renvoyer l'Ohio, nous allons utiliser des formules de
correspondance pour
renvoyer ces valeurs de manière dynamique. Nous n'avons pas besoin de saisir
les coordonnées manuellement. La première
formule de correspondance va renvoyer dynamiquement
le numéro de ligne. Nous donnons la formule,
la valeur de recherche, le client dans
la plage de cellules laquelle la
valeur de recherche client est contenue. Et il renvoie la ligne de
la valeur de recherche client. Étant donné que chaque client de la table de choix possède des informations
correspondantes sur la ville, l'état et le code postal. Les données que nous voulons, renvoyant le
numéro de ligne où
la formule de correspondance a été trouvée. Le client nous donne également le numéro de ligne des données
que nous voulons renvoyer. La deuxième
formule de correspondance va renvoyer dynamiquement
le numéro de colonne. Nous donnons la formule,
la valeur de recherche, l'en-tête de colonne et la plage de cellules auxquelles l'
en-tête de colonne correspond. Et il retournera l'emplacement
de l'en-tête de colonne sous forme nombre compte tenu des deux coordonnées deux valeurs
de correspondance, la formule d'index est maintenant en mesure de
renvoyer la valeur de la
cellule que nous recherchions. C'était un peu
d'informations à absorber, mais vous l'avez
parcouru. Comme il s'agit de la dernière
vidéo de la série, j'aimerais
vous remercier d'avoir regardé. Si vous aimez ce cours, veuillez prendre une minute pour
consulter mes autres cours. Et comme toujours, si
vous vous sentez coincé, n'hésitez pas à vous arrêter
et à poser une question. Merci.
Jeremy Schilling, Microsoft Excel Expert
