Formulaires et fonctions Excel Partie 9 : Formulaires de rangement

1. Introduction : Formules de tableau de l'introduction :: à Bienvenueàla section 9. Nous sommes dans le tretch maintenant, et cette section va parler d'une nouvelle race de formule appelée la formule de tableau . Et pour ceux d'entre vous qui n'ont jamais travaillé avec les formules de ray, ils fonctionnent un peu différemment des formules non ary qu'ils ont une syntaxe et une utilisation légèrement inhabituelles qui demandent à s'habituer. Il y a donc un peu de courbe d'apprentissage là-bas. Mais une fois que vous serez à l'aise et apprendrez à les utiliser, vous commencerez à voir qu'ils peuvent ouvrir la porte pour faire des choses que vous ne pouvez vraiment pas faire autrement. Donc, nous allons commencer la section avec les règles des formules de tableau, comment les utiliser et quels sont les avantages et les inconvénients communs des formules de tableau. Nous allons parler des constantes de tableau verticales, horizontales et bidimensionnelles. Et puis comment utiliser ces formules de constantes comment utiliser nommé une augmentation. Ensuite, nous allons aller dans un tas de démos et exceller. Commencez par transposer. Je vais parler de lier les données entre les feuilles. Je vais vous montrer comment utiliser l'approche non Array par rapport à l'approche de tableau. Comparez et contrastez cela à. Nous allons parler de la façon de renvoyer les X plus grandes valeurs de l'arrangement en une seule étape. Nous allons utiliser la fonction Len qui compte les caractères à travers une plage de cellules et dernier but non moindre, créera une fonction max si personnalisée comme d'habitude pour les fichiers à télécharger. Excel pour les analystes, formules de tableau qui pdf et un fichier Excel Section 9 ou formules. Alors allez-y et ouvre-les et allons frapper. 2. Règles de la fonction de la grille: Ok, alors qu'est-ce que tu as raison ? Fonctions ? Fondamentalement, ils sont conçus pour effectuer plusieurs calculs sur un ou plusieurs éléments d'un tableau. Ce qui les rend très différents des fonctions cellulaires normales, c'est qu'un rayon peut prendre la forme d'une formule cellulaire unique, auquel cas elles existent dans une cellule ou une formule multicellule, auquel cas c'est la même formule appliquée sur un certain nombre de cellules. Remarque très importante est que vous devez appuyer sur contrôle, décalage, entrer toe, entrer, modifier ou supprimer toute formule de tableau. Si vous n'appuyez pas sur Ctrl, Maj Entrée. Si vous appuyez simplement sur Entrée comme vous avez l'habitude de le faire, ceux-ci ne fonctionneront pas si la peine de le répéter à nouveau. Appuyez sur la touche de contrôle. Entrez chaque fois que vous entrez, modifiez ou supprimez une formule matricielle. Lorsque vous faites cela, vous verrez que Excel automatiquement entre parenthèses autour de votre formule, commençant avant le signe égal et allant jusqu'à la fin de votre formule, ce qui indique que la fonction s'applique à un tableau. Jetons donc un coup d'oeil à un exemple. Si je devais sélectionner la plage de D à trois d cinq ici et tapez la formule être à travers cinq fois C deux à C cinq et appuyez sur Entrée. La formule ne sera appliquée que pour vendre D à Si je sélectionne cette même plage, tapez exactement la même formule et appuyez sur le changement de contrôle entrer. J' ai été créé formule Honore et elle s'applique à toutes les cellules de la plage que j'avais mise en évidence. Donc, comme vous pouvez le voir, les résultats se peuplent sur les quatre lignes de mon rayon. Donc, les règles des fonctions de tableau malheureusement, il y en a beaucoup sur vous devez obéir à tous afin de les utiliser correctement. Le premier, comme je l'ai mentionné plusieurs fois maintenant, est que vous devez appuyer sur contrôle Shift entrée aussi parfois abrégé que C S E. Certaines personnes l'appellent fonctions de rayon. Fonctions CST pour cette raison même de modifier ou d'entrer la règle de formule Andre 2 Pour les fonctions de tableau à cellules multiples , vous devez sélectionner la plage de cellules avant d'entrer la formule. Donc, l'étape 1 consiste à comprendre à quelle plage de cellules vous voulez que votre formule soit appliquée , puis l'étape 2 consiste à écrire la formule. Règle numéro trois Il s'agit d'une règle importante. Vous ne pouvez pas modifier le contenu d'une cellule individuelle faisant partie d'une formule de tableau. C' est un peu strict et que dès que vous écrivez dans une formule de rayon spécifique à un tableau, vous ne pouvez pas insérer Rose. Vous ne pouvez pas, euh, supprimer des valeurs. Vous ne pouvez pas déplacer les cellules si elles faisaient partie de ce tableau. Règle quatre. Vous pouvez déplacer ou supprimer une formule de tableau entière, mais pas une partie de celle-ci. Tellement souvent. Si vous êtes tableau a besoin de changer, vous devez supprimer votre formule de tableau entière et reconstruire à partir de zéro, puis le dernier numéro mais pas le moins. Vous ne pouvez pas insérer de cellules vides dans ou supprimer des cellules d'une formule de tableau à cellules multiples, sorte que la ligne de fond appuie sur la touche Maj Entrée. Sélectionnez la plage de cellules avant d'entrer la formule. Et assurez-vous que votre tableau ne changera pas une fois que vous aurez écrit la formule. Donc ce sont les règles d'un rayon fonctions. En un mot. 3. Avantages et inconvénients de la fonction de tableau: d' accord, donc vous pouvez déjà commencer. Le sentiment que Ray fonctionne, bien qu'ils puissent certainement être incroyablement puissants, peut également être un buzzkill total avec lequel travailler, d' autant plus que vous vous familiariserez avec eux pour la première fois. Ils ont tendance à être un peu plus rigides et strictes que les fonctions non matricielles, donc la courbe d'apprentissage est un peu plus raide ici. Quoi qu'il en soit, jetons un coup d'oeil à certains des avantages et inconvénients des commentaires du côté pro. abord, ils sont vraiment d'excellents outils pour condenser plusieurs calculs en une seule formule, ce qui a l'avantage supplémentaire de réduire souvent la taille des fichiers. Deuxièmement, il peut exécuter certaines fonctions complexes que les formules non matricielles ne peuvent pas. Je vais vous guider à travers quelques exemples qui démontrent que, et troisièmement, ils ont aidé à réduire le risque d'erreur humaine, comme supprimer accidentellement des parties d'une augmentation ou manquer des formules de frappe sur le côté. Il pourrait être très difficile de modifier ou de supprimer des formules de tableau existantes. Ça pourrait être maladroit de travailler avec. Vous avez une visibilité limitée sur la fonction des formules, en particulier pour les utilisateurs qui ne sont pas familiers avec les rayons. Donc, il fait juste un peu plus difficile, toun moyen que de dépanner et d'identifier comment les combinaisons de fonctions peuvent fonctionner. Troisièmement, il élimine la possibilité de modifier les cellules contenues dans une augmentation. Donc, si vous avez un ensemble de données qui a tendance à changer un peu, sont une fonction peut être très difficile à travailler avec. Et enfin, ils peuvent réellement réduire la vitesse de traitement si plusieurs fonctions de tableau sont utilisées si personnellement dans le travail que je fais, je me retrouve à travailler avec une donnée très dynamique qui change et met à jour constamment, dans ce cas, il y a beaucoup de cas qui sont des fonctions de rayon ne sont pas les bons pour moi. Mais cela dit, il y a certainement des opportunités où j'ai utilisé des fonctions de tableau car il n'y a aucune autre option qui aurait pu accomplir la même chose. Donc, à la fin de la journée, c'est vraiment juste une fonction de ce que vous travaillez avec et de ce que vous essayez accomplir quand il s'agit d'utiliser des fonctions de tableau par rapport à des fonctions non matricielles. Donc, avec cela, parlons des différents types de taux et augmenté nommé, et puis nous allons sauter droit dans Excel 4. Constants de tableau verticaux, Horizontal, et à la 2-dimensions: Ok, Donc, une constante de rayon est une augmentation qui est créée en entrant manuellement une liste d'éléments directement dans la barre de formule et en entourant cette liste de parenthèses. Il y a donc trois types. Il y a horizontal ou une constante, qui créant tableau contenu dans une seule corde, ceux de l'air délimité par des virgules. Donc, ce que vous faites est de sélectionner le tableau horizontal de cellules que vous voulez créer dans ce cas , un 1 31 Si nous voulions que ce tableau soit rempli avec les nombres 123 et quatre, nous étions de type égal. Ouvrez le crochet une virgule, deux virgules, trois virgule quatre. Fermez le support, puis appuyez sur Maj contrôlé Entrée. Il est important de noter que vous devez taper manuellement cet ensemble de crochets, et lorsque vous appuyez sur la touche Maj de contrôle, entrez. Excel ajoutera ensuite une deuxième paire de crochets autour du tout. C' est donc le tableau horizontal ou vertical constant. Constance fonctionne de la même manière que vous sélectionnez simplement votre tableau vertical de cellules, puis vous délimitez vos valeurs par des virgules au lieu de virgules. Donc, si nous devions remplir un tableau d'un à un quatre avec les valeurs 1234, nous étions type égal à crochet ouvert un point-virgule, trois point-virgule quatre. Fermez le support, puis appuyez sur le changement de commande. Entrez Et dernier but, mais non le moindre, vous pouvez combiner des constantes de tableau horizontales et verticales pour créer des constantes de vitesse à deux dimensions . Donc, pour écrire une constante de tableau à deux dimensions pour en créer une à partir de zéro, vous entrez essentiellement vos valeurs de la même manière que vous liriez un livre commençant en haut gauche chaque ligne se déplaçant de gauche à droite. Ainsi, vous listez les valeurs séquentielles des colonnes aide B à C à D, séparées par des virgules, puis utilisez un point-virgule pour indiquer un saut vers le bas à la ligne suivante. Donc, dans ce cas, nous avons les valeurs de un à huit et depuis 123 et quatre off, toutes dans la même rangée. Ces air séparés par des virgules, puis le point-virgule indique que les cinq démarre sur la deuxième rangée en arrière et la colonne un ainsi appuyez à nouveau sur le décalage de contrôle. Entrez et votre tableau sera rempli. Alors rebondissons pour exceller. Je vais juste ouvrir du travail en blanc. Elle s'entraine à travailler avec ça. Donc, nous allons sélectionner un 1 31 aller dans la barre de formule et prendra égale crochet ouvert une virgule , deux virgules trois communs pour fermer le crochet. Et si j'appuie sur Entrée maintenant, vous verrez que la valeur est remplie et en vendrez une. Le numéro un sauf B un C un et d un restent inchangés. Le problème est que je n'ai pas appuyé sur le contrôle Shift, entrez. Et quand je fais cela maintenant, vous pouvez voir qu'il a ajouté la deuxième paire de crochets autour de ma fonction, et il est rempli le tableau que j'avais sélectionné avec les valeurs que j'avais entrées ici. Donc, c'est comment créer un tableau horizontal de base. Jetons un coup d'oeil à la façon d'éditer et de supprimer cette disposition. Donc, le défi est, vous savez, maintenant que j'ai ma fonction de tableau écrit, si je veux changer la Ford de cinq, je ne peux pas juste changer la valeur de la cellule parce que cela fait partie de ce tableau. Alors appuyez sur Annuler. , De même, je ne peux pas aller dans la barre de formule et la changer là pour la même raison. Donc, ce que j'ai besoin de faire, c'est essentiellement sélectionner le tableau entier, aller dans la formule, changer le Ford de cinq, puis appuyez sur le décalage de contrôle Entrée à nouveau pour mettre à jour le tableau quand il s'agit de supprimer le tableau. Une affaire similaire. Je ne peux pas supprimer des cellules individuelles. Ce qu'il faut faire est de sélectionner le tableau entier et de supprimer les valeurs de cette façon, auquel cas les fonctions ont disparu. Ou je vais annuler ça. Il peut sélectionner le tableau, aller dans la barre de formule, supprimer la formule. Et maintenant, voilà le piège. Même s'il n'y a pas de formule écrite par la presse, rien ne se passe. Pour que je puisse continuer à frapper. Entrez encore et encore et encore, et rien ne se passera après appuyer sur le contrôle, déplacer, entrer même lorsque j'ai supprimé la fonction pour effacer complètement le tableau que je viens de créer. Encore une fois, la courbe d' apprentissage est un peu raide. Une fois que vous commencez à jouer avec cela, il commence à devenir un peu plus naturel, mais encore une fois, certainement beaucoup plus strict et rigide que les formules cellulaires traditionnelles. Donc, créons un tableau vertical. Vous sélectionnez une barre 134 Aller à la formule équivaut à un crochet ouvert. Un. Pot-virgule à point-virgule. Trois demi-cône. Quatre. Tu te souviens des demi-deux-points, ou quoi ? Indiquez ou délimitez différentes valeurs qui tombent dans chaque rose. Alors fermez le décalage de commande du support Entrée et voilà. Il est peuplé Mireille dans les cellules un 134 Donc encore une fois pour supprimer cela, supprimer la fonction, appuyez sur le contrôle, Maj, entrer et il est parti. Alors faisons un exemple de plus. Nous allons faire un tableau à deux dimensions à partir de, Ah, un à travers Be pour Donc, dans ce cas, tapez entre crochets égaux. Et rappelez-vous, c'est comme si vous lisiez un livre. Donc, ça va commencer avec la valeur que je veux dans un un suivi de B un à B à un trois B trois un quatre avant. Alors pourquoi ne pas refaire, Ah, Ah, des nombres séquentiels ? Donc, un, puis commentaire m'amène à la prochaine valeur Colin, qui est de demi-deux-points saute vers le bas à la ligne suivante. Trois. Commun pour le point-virgule 56 approcher. L' entrée de changement de commande du support et ce qui est nécessaire ? R. J'avais besoin d'une paire de nombres de plus pour cela pour remplir le tableau complet que j'avais sélectionné, donc c'est un bon exemple. Quand nous revenons juste dans la fonction, ajouter un demi-côlon sept quarts de contrôle commun huit, entrez et là vous allez. C' est le bon tableau. horizontale, Coursehorizontale, verticale et bidimensionnelle 5. Utiliser les constants de tableau dans les formules de l'ordre: donc deux choses importantes à noter sur une constante de taux. Le premier est qu'ils n'ont pas seulement à contenir des valeurs comme je vous ai montré. Ils peuvent également contenir du texte entouré de guillemets ou même des valeurs logiques ou des valeurs d'erreur sur la seconde, plutôt que d'être utilisés simplement pour remplir des cellules là plus souvent utilisées dans le cadre d'une formule de rayon . Donc, en regardant cet exemple ici, comme vous pouvez le dire, il y a une constante de tableau imbriquée dans une formule de rayon plus grande. Et en fait, il se passe quatre choses ici. Nous avons notre fonction un tableau stocké, qui est juste la référence à un à un cinq, l'opérateur de multiplication et enfin, l'opérateur de multiplication et enfin, notre constante de rayon entourée de parenthèses. Et parce que ces nombres sont des valeurs sont délimitées par des virgules. Je sais que c'est une constante de tableau vertical. Donc, tout ce que cette fonction fait est qu'elle prend chaque valeur dans le tableau d'un cinq et la multiplie par rapport à sa valeur correspondante dans la constante de tableau que nous avons imbriquée ici. Donc, fondamentalement, la fonction est juste d'ajouter ou d'agréger tous ces produits, donc cinq fois un huit fois 22 fois 37 fois quatre et trois fois cinq pour arriver dans un total de 70 eso tip ici. Comme je l'ai mentionné, ces parenthèses entourant la constante de taux sont ajoutées manuellement. Les parenthèses externes sont ajoutées une fois que vous appuyez sur contrôle Shift Entrée pour le convertir en une fonction de tableau. Bon, alors on va exceller, on s'entraine un peu avec ça. Tout d'abord, je vais créer un tableau vertical. Constante. Je veux dire, le type est égal au support. 12345 Fermez le décalage de commande du support, Entrée. Et maintenant je vais sélectionner B un à B cinq et je vais créer un problème de multiplication . Ce sera juste les valeurs d'un à cinq. C' est mon histoire. Temps de la baie. Et maintenant je vais imbriquer un nouveau tableau vertical dans cette formule plutôt que d'en créer un séparément et ensuite faire référence aux cellules qu'il est peuplé. Je vais juste le faire dans la formule. Donc 10 point-virgule. 20 30 40 50. Gardez à l'esprit que si je mettais des commentaires ici, cela gâcherait tout car il essaiera de multiplier les valeurs qui descendent, ligne par ligne par les valeurs se déplaçant au-dessus de l'appel de ma colonne et les choses ne s'aligneraient pas comme je le veux . Donc, en ce moment, je crée un tableau avec les mêmes dimensions qu'un à cinq. Je suis pour que je reçoive un genre propre de relation 1 à 1. Fermez le décalage de commande du support, entrez pour terminer cela. Et comme vous pouvez le voir, cela semble fonctionner correctement. Fondamentalement, ce qu'il fait est de dire une fois 10 qui est 10 une deux fois 22 fois vingt, 40 trois fois, trente, 94 fois, quarante, 1 60 enfin, cinq fois 50 est à 50. Donc, comme vous pouvez le voir, j'ai enregistré une étape en insérant simplement cette constante dans la fonction de tableau. Donc, dans la section suivante, nous allons parler de la façon de nommer réellement ces tableaux eso que je n'ai pas besoin d'écrire 10 2030 40 50 à partir de zéro. Si je vais l'utiliser encore et encore, je peux lui donner un nom et ensuite taper le nom dans la formule de la même façon que nous avons utilisé nommé une augmentation dans certaines des sections précédentes. 6. Constants de tableau nommés: accord, donc tout comme les plages de cellules normales, vous pouvez attribuer un nom à une certaine constante de tableau. La seule différence est que dans la zone se réfère à, vous l'écrivez comme vous le feriez si vous essayez de remplir des cellules, donc vous ajoutez les crochets et délimitez soit par des virgules. S' il s'agit d'un tableau vertical, virgules s'il s'agit d'un tableau horizontal ou d'une combinaison des deux. Si c'est un rayon bidimensionnel. Donc, tout ce que vous devez faire est juste d'aller dans des formules, soit nom, manager ou définir le nom. Créez un nom pour votre constante, puis saisissez-le comme vous le feriez normalement dans la zone renvoie à et sachez que vous n'avez pas à appuyer sur contrôle Shift Entrée ici. Il suffit de le taper avec les crochets et d'appuyer sur OK qui stockera la constante du tableau avec nom afin que vous puissiez soit remplir les cellules en tapant simplement le tableau nommé. Ou vous pouvez remplacer la constance du tableau par le tableau nommé. Si elle vit dans une formule. Eso, même quand vous tapez juste le nom Furnari Constant, vous devez toujours appuyer sur contrôle Shift Entrée pour qu'il se remplisse, donc il saute pour exceller et faire un peu de pratique avec ce, euh, euh, encore une fois, Je travaille juste avec le classeur vide pour l'instant. Um, pourquoi ne pas commencer par créer, vous savez, un tableau vertical standard de 1 à 5 égal à crochets. 12345 Il devrait être très familier maintenant. Commande de décalage entrée. C' est donc une façon d'ajouter le tableau. Et comme vous pouvez le voir, il est stocké comme une constante de tableau avec chacune des cinq valeurs. Maintenant, ce que je pourrais faire est d'aller dans des formules pour trouver le nom, lui donner un nom appelé Numbers, puis ici, où il dit se réfère à supprimer tout après le signe égal, tapez le crochet et ensuite exactement la même chose. Un. Poste virgule 2345 Fermez la presse de support. OK, et maintenant, si je sélectionne être gagné par cinq et dans le type de barre de formule est égal aux nombres, vous pouvez voir l'excellence signalée parce qu'il sait qu'il y a un nom à Ray appelé nombres. Je pourrais appuyer sur control shift enter et il appliquera ce tableau à ma plage sélectionnée. Alors faisons la même chose. Sauf qu'au lieu d'utiliser des nombres, créons un tableau nommé avec du texte. Quelqu' un appelle ce texte, et il va se référer à un autre tableau vertical qui va être entre guillemets. Un demi-côlon à un demi-côlon trois demi-côlon. Désolé, vous bloquant là pour des produits chimiques et cinq. Assurez-vous donc juste que votre entourage de chacune de vos valeurs cite que vous entourez le tout avec des parenthèses. Alors appuyez sur OK. Il a sauvé ce tableau nommé en tant que texte, Maintenant en C un à C cinq, Il y a un type égal texte pourrait voir qu'il est reconnu qu'il contrôle. Maj. Entrez et il appliquera le tableau de texte là. Donc maintenant, juste pour utiliser ceux stockés ou nommés raise dans le cadre d'une fonction, cela fonctionne vraiment de la même manière. Donc, disons que nous avons juste cinq nombres aléatoires dans un tableau vertical d'un sept à un 11 . Um, ce que je pourrais faire B septième ou 11 si je veux faire un problème de multiplication similaire que nous avons fait dans la dernière conférence mais utiliser ou faire référence à un tableau sans nom plutôt qu'à un tableau manuel intégré , il pourrait le faire de sorte que équivaut à 73 11 fois. Et là, c'est là que nous vous aurions fait personne de point-virgule 2345 donc c'est comme ça qu' on le faisait. Maintenant, c'est éditer qu'au lieu de mettre le tableau manuel là, nous allons juste les bons nombres. Donc, un sept à 11 fois les nombres sont un décalage de contrôle. Entrez et nous recevrons la même réponse. Il y a donc quelques exemples de la façon d'utiliser des tableaux nommés avec des constantes de vitesse pour vous faciliter la vie. Suivant étaient sur le point de plonger dans Excel et de marcher là-bas un tas de démonstrations différentes. 7. La fonction de transmission de la fonction de transmission: d' accord. Donc la première ou une démo de formule que nous allons faire s'appelle transposer. Et fondamentalement, la fonction transposée vous permet juste de changer l'orientation d'un tableau de données donné. Ainsi, par exemple, si vous avez un tableau qui mesure cinq hauts mais deux colonnes de large, transposant cela le changerait en un tableau de deux lignes de haut à cinq colonnes de large. Une chose à noter qui rend cela un peu difficile est que la plage dans laquelle vous entrez fonction transposée doit être les dimensions exactes des données transposées. Donc, la syntaxe sage ne pouvait pas être plus simple. C' est juste transposé. Et puis la référence à votre tableau d'origine est à nouveau activée. Comme ça va être un changement de contrôle de la fonction de tableau, entrez orteil, verrouillez-le dans une petite pointe. Ici, il y a plusieurs façons de transposer des données, et si vous voulez transposer l'ensemble de données que vous voudrez modifier plus tard, vous pouvez le transposer comme juste des valeurs par opposition à une formule utilisant le paiement spécial transposé, qui est l'autel H v T raccourci que je vous ai montré dans la première section. Alors, sautons pour exceller cette fois. Nous allons ouvrir nos formules de tableau de classeur Excel Section 9. C' est un cahier d'exercices assez simple. Nous avons juste un onglet pour travailler avec les données de vente appelées. Comme vous pouvez le voir, j'ai une colonne d'année ici. Ah, liste des magasins, y compris Boston, New York et Detroit, et puis quelques chiffres hypothétiques pour les revenus dépenser le profit et sont vivants. Et nous allons juste travailler avec cet état quand nous pratiquons des fonctions de résumé. Donc je vais vous montrer deux façons de transposer ces données de la première façon, qui est la simple route A. Si vous avez juste besoin des valeurs à transposer et c'est cela, c'est juste pour saisir le tableau d'un à F 10. Il contrôle C à copier. Je sélectionne n'importe quelle cellule où vous voulez déposer vos données de transposition qui ont frappé Ault H V T. C'est Coller Special transposé. Et comme vous pouvez le voir, il est changé d'un tableau six par 10 en un tableau 10 par six, et il est fondamentalement juste déplacé les valeurs dans une nouvelle orientation Ici. La deuxième façon est d'utiliser une fonction de tableau. Alors rappelez-vous, je dois sélectionner les dimensions parfaites de mes données de transposition avant d'écrire cette fonction et je vais en fait vous montrer la mauvaise façon de le faire d'abord, afin que vous ayez une idée de ce qui se passe si vous ne suivez pas ces règles. Je suis donc je vais juste choisir un tableau aléatoire comme celui-ci et dans le type de barre de formule est égal transposé. Et encore une fois, mon augmentation de l'ensemble de données d'origine. Fermez la parenthèse, et je suis prêt à appuyer sur le contrôle Shift Entrée pour le transposer. Donc, ce que la fonction de transposition fera est qu'elle remplira le tableau que j'ai sélectionné et toute cellule où elle ne peut pas trouver la valeur appropriée à partir du droit d'origine ? Il va juste tomber avec un N A. Donc je vais supprimer cette entrée de changement de contrôle pour m'en débarrasser. Et maintenant je sais que je veux 10 par 6. Donc, il y a 10 colonnes de large 23456 Et recommençons et tapons transposer une fois de plus . Vous soulevez un à f 10 fermer le décalage de contrôle, entrez. Et voilà. Si je veux donner à cela le même formatage, c'est un peu ce qui est sympa à propos de tout th e v t est qu'il a conservé le formatage afin que je puisse juste saisir ce morceau dans l'orteil maison avait presse format peintre cuire le coin supérieur ici et sera appliquer la même mise en forme. Ainsi, comme vous pouvez le voir en ce moment, la même fonction transposée apparaîtra n'importe où. Je clique dans ce. Très bien, euh, donc vous allez à différentes façons de transposer des données. Et encore une fois, ça dépend de ce que vous essayez de faire ici. Pour déterminer quelle approche prendre si vous avez besoin de maintenir un lien vers votre tableau original non transposé que la fonction de rayon est probablement le meilleur pari. Si vous cherchez juste à réorganiser vos données, puis à les modifier ou à les modifier plus tard, alors l'ancien raccourci H V T est probablement votre meilleur pari. De toute façon, c'est la fonction de transposition. 8. Liens de données entre les données entre les feuilles : Comparaison de l'ordre vs ne Réunion pas: d' accord. La démo numéro deux consiste à relier les données entre les feuilles. Revenons donc à notre onglet Données de vente. En fait, je vais juste supprimer les colonnes que nous avions créées à partir de l' exercice de transposition . Maintenant, ce que je veux faire est juste de dupliquer les valeurs dans une référence 10 en les reliant simplement à une nouvelle feuille. Donc, je vais vous montrer deux approches de l'approche non tableau d'abord, suivie de l'approche tableau. Donc je vais créer un nouvel onglet. Appelez ça en double, et je vais faire, c'est écrire une formule auto et vendre 81 juste égale aux données de vente. Un appuyez sur Entrée. Et puisque c'est une référence relative, je peux juste le faire glisser vers f vers le bas à la ligne 10 et il dupliquera, euh, le tableau dans l'onglet données de vente. Comme vous pouvez le voir, chaque cellule a une fonction différente car elle est liée à une cellule différente dans l'onglet Données de vente . Et encore une fois, si je veux être particulier sur mon formatage, il peut également utiliser le peintre de format pour appliquer à beaucoup d'entre les cellules. J' ai donc des données en double sur deux onglets. Créons un onglet 1/3 appelé duplicate, aussi. Et dans ce cas, je vais sélectionner le tableau entier qu'il va remplir et puis appuyer sur égale données de vente un à F 10. La seule différence est que je vais frapper contrôle shift enter en tant que fonction de tableau. Et voilà. Il a encore récupéré toutes les données correctes de la ville de données des ventes. Je vais juste copier le formatage et le coller là. Et donc, vous allez trois versions du même ensemble de données. La différence Maintenant, quand vous regardez ces cellules est que dans la version dupliquée une, j'ai un total de 60 formules ici accordées leurs formules simples. Mais chaque cellule a une formule différente dans la version ray sur duplicate, aussi. Partout où vous cliquez, vous verrez qu'il s'agit d'une seule fonction car elle est appliquée à un tableau entier en une seule étape. n'est donc qu'une démonstration de la façon dont les fonctions Ray peuvent vous aider à consolider des formules dans un cas comme celui-ci. Et donc, vous savez, même si je devais changer les valeurs ici, donc 2015 Boston dépense était en fait 1000 qui va réellement mettre à jour correctement à la fois dans le tableau et ensuite les versions Honore. Voilà donc, reliant les données entre les feuilles à l'aide de fonctions de tableau 9. Rendez les valeurs des « X » dans une plage: ne le sont pas. Maintenant que vous avez une idée du fonctionnement de ces formules d'air, commençons à intégrer certains outils et fonctions que nous avons appris lors des conférences précédentes et voyons comment ceux-ci pourraient être utilisés dans le cadre d'une fonction de rayon. Donc le premier exemple que je veux vous montrer est, ah, disons que je veux retirer les trois premières années de revenus de la colonne C. Donc dans chacune d'elles, je vais juste au top 3 et, ah, la première façon que je pense de le faire. Mais non ou une version que nous avons déjà appris serait utilisée. La grande fonction est donc égale à un grand rayon C deux à C 10. Appuyez sur cela pour corriger cela, puis, pour le numéro un plus grand qui suffit de taper mais un pour la valeur K. Fermez-le et frappez. Entrez. Je pourrais simplement copier cela, coller deux fois de plus et juste changer la valeur K à deux pour le deuxième plus grand et 23 pour le troisième plus grand. Donc, c'est la version non tableau. Je veux les formater de la même façon. Elle est la peintre de format, donc c'est trois formules distinctes pour renvoyer les trois plus grandes valeurs de la colonne C. Um, faisons la même chose ici, Top 3. Cette fois, je vais utiliser une fonction de tableau pour faire la même chose. Alors rappelez-vous, je vais commencer par faire ma sélection des trois cellules que j'ai à travers I pour et le type de barre de formule est égal à gros rayon que je cherche. Les valeurs les plus élevées sont encore C 2 à C 10. La seule différence ici est que pour la valeur k, parce que je cherche les trois plus grandes valeurs. Je ne peux pas mettre un, deux ou trois. Je dois mettre une constante de taux ici. Il va ouvrir le support et faire une constante de tableau vertical de un à trois. Puisque je cherche les trois plus grandes valeurs et le tableau que j'ai sélectionné est un tableau vertical de trois lignes de haut. Alors j'ai juste besoin de fermer cette entrée de contrôle de parenthèses et voilà. Donc même formatage que vous pouvez voir obtenir exactement les mêmes trois premières valeurs. La seule différence est que j'ai trois formules ici dans l'âge de la colonne, juste une formule de tableau unique et une colonne I Maintenant, que faire si je voulais changer cela des trois premiers aux cinq premiers. , Encore une fois, je pourrais juste copier ça. Collé deux fois de plus. Changez le 3 à 4 et un 4 à 5 pour la version de la baie. Ce que je pourrais faire. C' est sélectionner un tableau vertical avec cinq roses dedans. Je pourrais ajouter un demi-côlon quatre, demi-côlon cinq et un décalage de contrôle. Entrez. Et voilà, tu y vas encore. Mettez à jour le formatage ici, changez ceci en, uh, top 5 et encore deux façons d'accomplir la même chose. 10. Compte des personnages de l'ensemble des cellules de l'ensemble: D' accord, donc la prochaine démo que je veux partager avec vous est de compter des personnages sur une gamme de cellules, et ce n'est pas particulièrement difficile. C' est en fait une fonction de tableau assez simple, mais c'est un bon exemple d'une tâche que vous ne pouvez pas accomplir en une seule étape à moins d'utiliser la théorie une formule. Je le suis si notre objectif, pour une raison quelconque, est de compter le nombre de personnages dans toute cette gamme de magasins. B deux à Beat 10. Si je devais prendre l'approche non tableau quand je dois faire, c'est le type de fonctions Siris de Len qui se rapportent à la colonne B et juste l'appliquer vers le bas. Cela me donne donc le nombre de caractères dans chacun de ces noms de ville, y compris les espaces. Et puis j'aurais orteil une fonction de somme assez ici pour me donner le total. Donc 63 caractères sur toute cette gamme. Je peux aussi faire la même chose avec une fonction de tableau en une seule étape, et je pourrais commencer par la fonction de longueur et l'appliquer pour être 23 b 10. Mais le problème est, même si je l'entre à avec le changement de contrôle. Entrez, je n'en reçois que six parce que c'est sur Lee qui calcule la longueur de la première cellule dans la référence. Donc, ce que je dois faire est juste d'insérer une fonction ici aussi. Donc ça va être une partie des longueurs de chacune des cordes de chaque rangée de mon rayon. Et maintenant, une fois que j'ai touché le changement de contrôle, entrez. Comme vous pouvez le voir, j'obtiens le même total en une seule étape, opposition à une douzaine, donc un exemple de la façon dont vous pourriez être en mesure de travailler plus efficacement en adoptant l'approche des rayons par rapport à l'approche traditionnelle des formules cellulaires. 11. Créer une formule de tableau « MAX IF »: Bon, donc pour la démo finale va vous montrer comment construire une fonction max si personnalisée. Ceci est un excellent exemple d'où les fonctions de tableau peuvent entrer en jeu lorsque vous n'êtes pas un tableau ou que les formules de cellules traditionnelles peuvent être insuffisantes. Donc, si vous vous souvenez dans la section de la fonction statistique, nous avons parlé de si, comptez si, si moyennes. Malheureusement, il n'y a pas de formule équivalente si vous voulez calculer la valeur maximale dans une colonne en fonction certains critères. Donc, il n'y a pas de fonction max si hors de la boîte que nous pouvons utiliser. Heureusement, les fonctions de tableau peuvent faire le travail. Donc, ce que je veux faire ici est de retourner la valeur maximale pour le chiffre d'affaires et la colonne C basée sur un nom d'emplacement de magasin spécifique Donc l'un de ces trois noms de ville. Donc la première chose que je veux faire juste pour montrer un exemple. C' est juste nommer cette ville de tableau, et je ne fais pas ah tableau constant. Tout ce que je fais est juste de nommer la plage de B à trois b 10 juste pour que nous nous souvenions que c'est une option pour aider à gagner du temps lors de l'écriture de cette fonction afin que les tableaux maintenant appelés city Ce que je vais faire est d'écrire un des noms de ville ici dans H huit, prenez Boston, puis en h 9. Et c'est là que je vais taper mon max. Si la fonction alors ça va être une fonction relativement simple, nous allons commencer avec égal , Max, ouvrez la princesse si et maintenant le test logique va être ville. Vous voyez, il est en quelque sorte déclenché au tableau parce qu'il l'a reconnu comme un Ray nommé, et la ville est égale à toutes les valeurs en h 8. ce moment, c'est Boston. Et si je change cette valeur, vous tiriez dans le max pour New York ou Detroit, par exemple, alors la valeur de vrai sera juste le tableau de C 2 à C 10 qui est mon champ de revenus. C' est là que j'essaie d'extraire la valeur max de si fermer aux parenthèses et contrôler l'entrée de décalage. Et voilà. Donc, prenez la mise en forme d'apparaître dans l'onglet d'accueil. Boston, 1726$. On pourrait faire une vérification ponctuelle rapide. Confirmez qu'il s'agit de la valeur maximale pour Boston. Et après le changement à New York, par exemple, en 1944. Elle a aussi raison. Et Detroit, 17 43 ans. Voilà, si excellent moyen de créer notre propre fonction personnalisée où nous n'avions pas l'option. En utilisant nos formules statistiques traditionnelles qui complètent la section des formules de tableau. Ce n'est vraiment qu'un petit échantillon des types d'analyses que vous pourriez faire avec la course . Alors je vous encourage. Une fois que vous êtes à l'aise, commencez à explorer comment et où vous pouvez les utiliser dans d'autres domaines. Ensuite, nous avons une dernière section couvrant quelques fonctions bonus ou pour faire des démonstrations vraiment intéressantes, alors restez à l'écoute pour la dernière partie. 12. BONUS : « MAX IF » avec plusieurs critères: Très bien, donc en fait menti. J' ai quelques démos de fonction de tableau supplémentaires que je veux partager avec. Vous les gars qui sont venus avec depuis que le cours a été publié à l'origine ont vraiment aidé à démontrer à quel point les fonctions de tableau peuvent être polyvalentes et puissantes. Donc, dans cette conférence, je veux réellement ramasser la fonction max if que nous venons de terminer et le rendre un peu plus compliqué. Donc, au lieu de simplement baser le max, si sur un critère où la ville est égale à Detroit, Boston ou New York, je veux mettre à jour ce Max. Si la fonction de tableau pour tenir compte de plusieurs critères spécifiquement à la fois l'emplacement ou la ville et l'année afin de sauter de nouveau dans le classeur, la première chose que je vais faire est juste de sélectionner un deux à 8 10 et nommer cette année de plage juste pour faire un un peu plus facile de travailler avec et ici. Et j'ai mangé. Disons qu'un an pourrait être n'importe quoi. Je vais mettre 2014 ici, et je vendrais neuf. C' est là que je veux entrer mon nouveau max. Si la fonction, je vais en fait simplement glisser cela pour me donner un bon point de départ, je peux éditer les références City a toujours besoin d'égaler H huit, et les valeurs cherchaient le max à l'intérieur. Ce n'est pas de D à 3 d 10. Il est toujours voir à la réception 10 le champ de revenus. Donc, si j'appuie sur la touche Contrôle Shift Entrée, vous verrez que j'ai le même point de départ qu'avant. Et maintenant, je pourrais juste ajouter ce deuxième critère ici, donc je veux faire est d'entourer mes premiers critères. Ville est égal à huit H entre parenthèses. Multipliez cela par mon deuxième critère, qui est l'année. Vous pouvez voir qu'il identifie la plage de nom égale que j'ai mangé près de cette parenthèse. J' ai donc deux critères, chacun entouré de parenthèses, séparés par un multiplicateur. Je vais te montrer pourquoi dans une minute. Donc, si je contrôle le décalage, entrez maintenant il renvoie 16 09 qui est la valeur maximale Si la ville est égale à Detroit et l'année est égale 2014. Donc Detroit 2014 16 09 Maintenant, évidemment, c'est la seule valeur qui existe pour Detroit 2014. Mais pour donner un test, nous pouvons changer le 2013 à 2014. Et maintenant, comme vous pouvez le voir, la valeur maximale passe de 16 09 à 17 43. Donc, annulez simplement ce changement à 16 09 Maintenant, nous allons creuser un peu, en essayant de comprendre exactement comment cela fonctionne dans. La meilleure façon de le faire est d'utiliser l'outil d'évaluation de la formule. Donc, avec cette cellule sélectionnée, je peux aller dans les formules, évaluer la formule, et cela me guidera à travers chaque étape de la fonction et comment elle est évaluée. Donc, nous allons commencer par la ville soulignée, et quand nous évaluons la plage nommée ville, elle la traduit en un tableau de neuf rangées de données différentes. Boston, Boston, Boston, New York, New York, New York, Détroit, Détroit, Détroit. Donc, nous cherchons maintenant des cas où les valeurs dans ce tableau sont égales à la valeur à l'âge de huit ans , qui évalue à Detroit et à mesure qu'Excel tourne à travers cela, il va traduire cette déclaration en un tableau de trêve et false est si pour chaque ligne où cette condition est vraie ou la ville est égale à Détroit, il retournera un vrai et pour chaque ligne où ce critère n'est pas vrai, il retournera un faux de sorte que le premier critère ville est égal à huit ans se traduit en faux, faux, faux, faux, faux, faux vrai. True, True, puisque seules les trois dernières lignes de ce tableau répondent à ce critère. Ensuite, on va faire la même chose avec nos critères d'année. Donc, année évalue à un tableau contenant toutes nos dates dans ces neuf lignes et colonnes A. Et nous cherchons des cas où les valeurs dans ce tableau sont égales à la valeur et je huit, ce qui est en ce moment 2014. Donc, cela évaluera d'une manière très similaire, il traduira cette déclaration en une autre. Siris de vrai et faux est dans ce cas, nous avons faux, vrai, faux, faux, , faux vrai, faux, faux, faux, faux, vrai, faux. Et comme vous pouvez le voir, cela a du sens parce que pour tous les trois ans, un seul est 2014. Voici donc maintenant la clé qui permet à cette fonction de renvoyer la valeur correcte que nous avons créée à une augmentation. Sur la base de nos deux critères. Le premier tableau contient des valeurs true et false basées sur des lignes qui correspondent à la ville d'emplacement. Le deuxième tableau contient des valeurs vraies et fausses basées sur la rose qui correspondent à l'année. Donc, lorsque nous multiplions ces deux ensemble, le résultat est un seul tableau de zéros et de ceux qui peuvent être interprétés exactement de la même manière que vous interpréteriez la trêve et False est où true est égal à un faux est égal à zéro. Donc, ce que nous avons ici maintenant est un tableau qui ressemble à 000000010 Donc Onley une valeur dans ce tableau rempli comme vrai. Et c'est parce que tout cas où un critère ou l'autre n'a pas été respecté. Lorsque ces deux valeurs sont multipliées, dont l' une doit être zéro, il en résultera un zéro dans le tableau final. Donc, on indique la ligne dans notre tableau où tous les critères ont été remplis, et dans ce cas c'est la deuxième à la dernière ligne. Et si vous regardez nos données ici, cette deuxième à dernière rangée 9 est la route qui contient à la fois l'année Nous nous soucions de 2014 et la ville que nous nous soucions de Detroit maintenant le dernier bit de cette fonction. L' instruction if affecte essentiellement une valeur comprise dans la plage de C deux à C 10 à toute valeur de notre rayon qui n'est pas égale à zéro. Donc, en d'autres termes, nous allons retirer la valeur de la dernière vente du deuxième orteil dans la gamme de C 2 à C 10 depuis le deuxième orteil. Dernière place dans nos tableaux, la seule valeur non nulle. Et comme vous pouvez le voir, il en résulte 16 09 puisque c'est la deuxième à dernière valeur dans notre tableau de C deux à C 10 et maintenant nous sommes juste avec une simple fonction max. Alors quel est le maximum de faux ou 000000 16 090 C'est 16 09 Et c'est la valeur qui est retournée . Ainsi, comme vous pouvez voir un rayon, les fonctions sont très, très différentes formes, fonctions de cellule normales en ce qu'elles ont en quelque sorte de cycle à travers ces processus tous dans la même formule dans la même cellule, et c'est ce qui les rend si puissants. 13. BONUS : Utiliser le double opérateur unitaire ("--»).: Ok, passons par un autre exemple. Juste pour vous montrer comment les formules de tableau peuvent parfois être utilisées pour faire des choses qui vendent des formules ne peuvent tout simplement pas accomplir. Donc, dans ce cas, restant à notre exemple de données, Disons que quelqu'un vous demande, combien d'années le profit a-t-il dépassé les dépenses dans cet ensemble de données ? Si vous deviez accomplir cela en utilisant des formules de cellules, vous pourriez faire quelque chose comme une instruction conditionnelle qui dit, S'il est supérieur à D, alors retourné un. Sinon zéro et vous pouvez appliquer cela vers le bas et ensuite prendre la partie de cette colonne et là vous pouvez voir ok il y a quatre ans ou quatre lignes dans cet ensemble de données dans lequel le profit a dépassé les dépenses. Mais cela a pris deux pas. J' ai utilisé 10 formules. Les fonctions de tableau total pourraient le faire avec beaucoup plus d'élégance et avec beaucoup moins de puissance de traitement pour le faire, disons simplement, vous savez, profit. C' est plus grand que de passer la ligne de menu à la maison que faire italique Et voici où je veux retourner la même valeur de quatre. Donc, je vais réellement utiliser une fonction de tableau en utilisant certains, donc je vais dire égale certains et maintenant, au lieu de juste un seul nombre ou une seule plage traditionnelle de nombres ici qui peut réellement entrer dans des plages de données ici tout à la fois. Donc, je peux dire E à travers e 10 est supérieur à D à trois d 10 et fermer les parenthèses. Donc, en théorie, ce que cela devrait faire comme une fonction de tableau, c'est cycle à travers chaque ligne dans une augmentation et un tally up un vrai ou un dans tous les cas où le critère est vrai ou où la colonne est grande et la colonne d et un zéro sinon, puis prendre le soleil et le retour de quatre. Donc, si nous contrôlons l'entrée, vous verrez que nous obtenons un zéro, ce qui est évidemment pas que nous voulons. Donc, il va aux formules, évaluer la formule et voir ce qui se passe ici. Donc, dans cette instruction supérieure à est évaluée, cela entraîne le tableau que nous attendrions false, false, true, true, false, true, true, true, true, true, false, false et, comme vous pouvez le voir donc vraies valeurs dans les positions 34 six et sept. Et si vous regardez ces rose spécifique dans notre ligne de données, 1239 68 est génial et dans 7 58 49 quatre-vingt, grand dans les 9 64 6 et sept. Ce sont donc les positions correctes ou les instances correctes de lignes qui correspondent à ce qui est supérieur aux critères. Le problème est que lorsque nous essayons de résumer cette déclaration, il évalue à zéro. Et la raison en est que même si dans de nombreux cas Excel traitera false est à zéros et True comme un avec une certaine instruction, il ne sait pas comment gérer ces valeurs. Donc Excel a en fait un petit outil très utile appelé le W Nery et qui convertit essentiellement les valeurs booléennes non numériques vraies et fausses en valeurs réelles et zéros. Donc si on retourne dans sa fonction ici juste après la première parenthèse qui fera deux tirets, c'est le double toi Nery. Pas besoin d'ouvrir à nouveau la parenthèse et de fermer la seconde à la toute fin. Maintenant, si je contrôle Shift enter, il renvoie la valeur correcte de quatre. Donc, chaque fois que vous avez une fonction de tableau où vous devez convertir un tableau spécifique de booléennes en valeurs et zéros que W Nery devrait faire l'affaire 14. HOMEWORK: HOMEWORK: : Fonctions de communication: d' accord. Il est temps de vous taper sur le dos. Tu viens de finir la section course. Des trucs fous, certainement un peu difficiles à s'habituer et très, très différents des fonctions cellulaires traditionnelles dont nous avons parlé maintenant à nouveau . Comme je l'ai déjà dit, c'est vraiment juste gratter la surface et vous donner quelques exemples sélectionnés de la façon dont nos formules A peuvent être utilisées dans Excel. Cela dit, une fois que vous maîtrisez les principes fondamentaux du fonctionnement des formules Ray, vous pouvez rapidement commencer à les appliquer et des méthodes plus compliquées ou plus avancées. Donc, du temps de devoirs pour toute personne intéressée à creuser un peu plus profondément et à obtenir plus pratique avec ceux-ci dans le fichier d'exercices de devoirs qui est disponible dans le cours. La ressource est que nous avons deux onglets avec des exercices de tableau. Le 1er 1 est une fonction de rayon, et on va s'entraîner à des trucs comme Max. Si Max si avec plusieurs critères en utilisant ce double vous Nery pour convertir les valeurs booléennes en nombres et puis dernier mais pas le moindre, nous avons cet onglet de fonction de tableau bonus, qui est l'endroit où les choses commencent à devenir vraiment poilu. Essentiellement, ce que nous allons essayer de faire ici est d'utiliser la formule Honore pour renvoyer un sous-ensemble de cette plage à partir d'un trois d 14 basé sur des critères spécifiques. Donc nous voulons retourner ce sous-ensemble ici dans I trois à l 14 mais Onley pour Rose qui répondent à la date de début, la date fin et les critères de revenu minimum assignés ici dans la colonne G. Donc, je vais en fait vous donner un petit aperçu de la parce que ce que j'ai fait est exposé une explication étape par étape dans mes propres mots de la façon dont cette chose fonctionne réellement . Et si vous jetez un coup d'oeil à la fonction, vous pouvez voir que c'est une sorte de bête. Cela dit, ce n'est probablement pas la seule façon de le faire. Gardez à l'esprit que c'est l'approche que j'ai choisie pour accomplir cela, Mais il peut très bien y en avoir d'autres. Donc, comme toujours, les messages sur le forum de discussion. Si vous avez besoin d'aide, envoyez un message directement, ou si vous voulez juste dire bonjour, toujours là pour vous aider. Merci, les gars. Bonne chance