Comment faire un échantillon dans Excel: 4 Fashion de travail

Lorsque vous travaillez avec des tables Excel, il est souvent nécessaire de réaliser la sélection sur un critère spécifique ou dans plusieurs conditions. Le programme peut le faire de différentes manières en utilisant une série d'outils. Découvrons comment faire un échantillon dans Excele en utilisant une variété d'options.

Échantillonnage

L'échantillon de données consiste dans la procédure de sélection à partir de la disposition totale de ces résultats qui répondent aux conditions spécifiées, suivies de la sortie d'eux sur une feuille d'une liste distincte ou dans la plage source.

Méthode 1: Appliquez une coupe automatique étendue

Le moyen le plus simple de faire la sélection est l'utilisation d'une autofiltre étendue. Considérez comment faire cela sur un exemple spécifique.

1. Sélectionnez la zone sur la feuille, parmi les données dont vous souhaitez effectuer un échantillon. Dans l'onglet Home, cliquez sur le bouton "Trier et filtre". Il est placé dans le bloc de paramètres d'édition. Dans la liste qui s'ouvre après cette liste, cliquez sur le bouton "Filtrer".

   

   Il y a une occasion de faire et différemment. Pour ce faire, après avoir sélectionné la zone sur la feuille, nous passons à l'onglet "Données". Cliquez sur le bouton "Filtrer", qui est placé sur la bande dans le groupe de tri et de filtre.



2. Après cette action dans l'en-tête de la table, des pictogrammes semblent commencer à filtrer sous la forme des bords de petits triangles dans le bord droit des cellules. Cliquez sur cette icône dans le titre de cette colonne, selon laquelle nous souhaitons faire un échantillon. Dans le menu du menu exécutant, passez l'élément "Filtres de texte". Ensuite, sélectionnez la position "Filtre personnalisable ...".



3. La fenêtre de filtrage de l'utilisateur est activée. En cela, vous pouvez définir une limite sur laquelle la sélection sera faite. Dans la liste déroulante pour une colonne de la cellule de format numérique, nous utilisons par exemple, vous pouvez choisir l'un des cinq types de conditions:
   • équivaut à;
   • inégal;
   • Suite;
   • plus ou égal;
   • plus petite.

   Définissons la condition comme exemple de manière à sélectionner uniquement les valeurs pour lesquelles le montant des revenus dépasse 10 000 roubles. Nous établissons un passage à la position "plus". Dans le champ de droite, citons la valeur "10 000". Pour effectuer l'action, cliquez sur le bouton "OK".



4. Comme nous le voyons, après filtration, seules les lignes sont restées, dans lesquelles le montant des revenus dépasse 10 000 roubles.



5. Mais dans la même colonne, nous pouvons ajouter la deuxième condition. Pour ce faire, revenez à la fenêtre de filtration personnalisée. Comme nous pouvons le constater, dans sa partie inférieure, il existe une autre condition de commutation et le champ correspondant pour l'entrée. Installons maintenant le bord supérieur de la sélection de 15 000 roubles. Pour ce faire, réglez le commutateur sur la position "Moins" et dans le champ situé à droite, la valeur "15000".

   De plus, il reste encore un commutateur de conditions. Il a deux dispositions "et" et "ou". Par défaut, il est installé dans la première position. Cela signifie que seules les lignes resteront dans l'échantillon qui satisfont les deux restrictions. S'il est réglé sur "ou" position, les valeurs qui conviennent à l'une des deux conditions resteront. Dans notre cas, vous devez définir le commutateur sur la position "et", c'est-à-dire laissant ce paramètre par défaut. Une fois toutes les valeurs entrées, cliquez sur le bouton OK.



6. Maintenant, seules les lignes sont restées dans la table, dans laquelle la quantité de revenus n'est pas inférieure à 10 000 roubles, mais ne dépasse pas 15 000 roubles.



7. De même, vous pouvez configurer des filtres dans d'autres colonnes. Il est possible de maintenir le filtrage et sur les conditions précédentes définies dans les colonnes. Voyons donc comment la sélection est prise en utilisant le filtre pour le format des dates. Cliquez sur l'icône de filtration dans la colonne correspondante. Cohérent en cliquant sur la liste "Filtrer par date" et le filtre.



8. La fenêtre d'exécution automatique personnalisée recommence. Effectuez la sélection des résultats dans la table du 4 au 6 mai 2016 inclus. Dans l'interrupteur de sélection, comme vous pouvez le constater, encore plus d'options que pour un format numérique. Sélectionnez la position "après ou égale". Sur le terrain à droite, définissez la valeur "04.05.2016". Dans le bloc inférieur, nous réglons le commutateur sur la position "UP ou égale". Dans le champ de droite, entrez la valeur "06.05.2016". Condition Compatibility Interrupteur congé dans la position par défaut - "et". Afin d'appliquer le filtrage en action, appuyez sur le bouton "OK".



9. Comme vous pouvez le constater, notre liste a encore diminué. Maintenant, seules les lignes ne sont laissées là-dessus, dans lesquelles la quantité de revenus varie de 10 000 à 15 000 roubles pour la période de 04.05 au 06.05.2016 inclus.



10. Nous pouvons réinitialiser le filtrage dans l'une des colonnes. Faisons-le pour les valeurs de revenus. Cliquez sur l'icône AUTOFILTER dans la colonne correspondante. Dans la liste déroulante, cliquez sur l'élément "Supprimer le filtre".



11. Comme vous pouvez le constater, après ces actions, l'échantillon de la quantité de revenus sera désactivé, mais ne prendra que la sélection par date (du 04/05/2016 au 06.05.2016).



12. Cette table a une autre colonne - "nom". Il contient des données en format texte. Voyons comment former un échantillon à l'aide de filtrage via ces valeurs.

    Cliquez sur l'icône du filtre au nom de la colonne. Allez systématiquement sur les noms de la liste "filtres de texte" et "filtre personnalisable ...".



13. La fenêtre Autofilter de l'utilisateur s'ouvre à nouveau. Faisons un échantillon par les noms de "pommes de terre" et "viande". Dans le premier bloc, les conditions changent de position sur la position "égale". Dans le champ à la droite de cela, c'est le mot "pommes de terre". Le commutateur de l'unité inférieure mettra également la position «égale». Dans le domaine en face de cela, je fais un record - "viande". Et maintenant nous effectuons ce qu'ils n'avaient pas auparavant: définir le commutateur de compatibilité sur la position "ou". Maintenant, la ligne contenant l'une des conditions spécifiées sera affichée à l'écran. Cliquez sur le bouton "OK".



14. Comme vous pouvez le constater, il existe des restrictions à la date du nouvel échantillon (du 04.05.2016 au 06.05.2016) et par nom (pommes de terre et viande). Il n'y a pas de restrictions sur le montant des revenus.



15. Vous pouvez supprimer complètement le filtre par les mêmes méthodes utilisées pour l'installer. Et peu importe quelle méthode a été utilisée. Pour réinitialiser le filtrage, dans l'onglet "Données", cliquez sur le bouton "Filtrer", situé dans le groupe "Trier et filtre".

    

    La deuxième option implique la transition vers l'onglet "Accueil". Nous effectuons un clic sur le ruban sur le bouton "Trier et filtrer" dans l'unité d'édition. Dans la liste activée, cliquez sur le bouton "Filtrer".

Lorsque vous utilisez l'une des deux méthodes ci-dessus, le filtrage sera supprimé et les résultats des échantillons sont nettoyés. C'est-à-dire que la table sera montrée à l'ensemble de la matrice des données qu'elle a.

Leçon: Fonction de filtre automatique dans Excel

Méthode 2: application de la formule de matrice

Faire la sélection peut également appliquer la formule complexe du tableau. Contrairement à la version précédente, cette méthode prévoit la sortie du résultat dans une table séparée.

1. Sur la même feuille, nous créons une table vide avec les mêmes noms des colonnes de l'en-tête que la source.



2. Allouer toutes les cellules vides de la première colonne de la nouvelle table. Installez le curseur dans la chaîne de formule. Juste ici sera inscrit par une formule qui produit un échantillon sur les critères spécifiés. Nous sélectionnerons la ligne, le montant des revenus dans lesquels dépasse 15 000 roubles. Dans notre exemple particulier, la formule introduite sera la suivante:

   = Index (A2: A29; plus petit (si (15000

   Naturellement, dans chaque cas particulier, l'adresse des cellules et des gammes sera la vôtre. Sur cet exemple, vous pouvez comparer la formule avec les coordonnées sur l'illustration et l'adapter à vos besoins.



3. Comme il s'agit d'une formule de matrice, afin de l'appliquer en action, vous devez appuyer sur la touche ENTER, mais la combinaison de touches CTRL + SHIFT + ENTER. Nous le faisons.



4. Après avoir mis en évidence la deuxième colonne avec des dates et l'installation du curseur dans la chaîne de formule, nous introduisons l'expression suivante:

   = Index (B2: B29; plus petit (si (15000

   Cliquez sur la combinaison Ctrl + Shift + Entrée de touche.



5. De même, dans une colonne avec revenus, entrez la formule du contenu suivant:

   = Index (C2: C29; plus petit (si (15000

   Encore une fois, tapez la combinaison CTRL + Shift + Entrée de touche.

   Dans les trois cas, seule la première valeur des coordonnées change et le reste de la formule est complètement identique.



6. Comme nous le voyons, la table est remplie de données, mais son apparence n'est pas entièrement attrayante, de plus, les valeurs des dates sont remplies de manière incorrecte. Il est nécessaire de corriger ces lacunes. L'incorrect de la date est liée au fait que le format des cellules de la colonne correspondante est courant et que nous devons définir le format de date. Nous mettons en valeur la colonne entière, y compris les cellules avec des erreurs, puis cliquez sur la surbrillance sur le bouton droit de la souris. Dans la liste qui apparaît, passez par le "format cellulaire ...".



7. Dans la fenêtre de formatage qui s'ouvre, ouvrez l'onglet "Numéro". Dans le bloc "formats numériques", allouez la valeur "date". Sur le côté droit de la fenêtre, vous pouvez sélectionner le type d'affichage de la date souhaité. Une fois les paramètres exposés, cliquez sur le bouton "OK".



8. La date est maintenant affichée correctement. Mais comme on le voit, tout le fond de la table est rempli de cellules contenant la valeur erronée "# numéro!". En substance, ce sont ces cellules, des données de l'échantillon pour lesquelles ils n'en avaient pas assez. Il serait plus attrayant s'ils étaient affichés du tout vide. À ces fins, nous utilisons un formatage conditionnel. Nous mettons en évidence toutes les cellules du tableau, à l'exception de l'en-tête. Dans l'onglet Home, cliquez sur le bouton "Mise en forme conditionnelle", qui se trouve dans le bloc d'outil "styles". Dans la liste qui apparaît, sélectionnez l'élément "Créer une règle ...".



9. Dans la fenêtre qui s'ouvre, sélectionnez le type de règle "Format uniquement les cellules contenant". Dans le premier champ, sous l'inscription "Format uniquement les cellules pour lesquelles la condition suivante" Sélectionnez la position "Erreur". Ensuite, cliquez sur le bouton "Format ...".



10. Dans la fenêtre de formatage en cours d'exécution, allez à l'onglet "Police" et sélectionnez Blanc dans le champ approprié. Après ces actions, cliquez sur le bouton "OK".



11. Sur le bouton avec exactement le même nom, cliquez sur après avoir revenu à la fenêtre Création de la conduite.

Nous avons maintenant un échantillon fini sur la limite spécifiée dans une table distincte correctement décorée.

Leçon: Formatage conditionnel dans Excel

Méthode 3: Échantillonnage sur plusieurs conditions à l'aide de la formule

Tout comme lors de l'utilisation du filtre, en utilisant la formule, vous pouvez sélectionner plusieurs conditions. Par exemple, prenez la même table source, ainsi qu'une table vide, où les résultats seront émis, avec une mise en forme numérique et conditionnelle déjà effectuée. Nous établirons la première limitation de la frontière inférieure de la sélection par revenus dans 15 000 roubles et la deuxième condition de la bordure supérieure de 20 000 roubles.

1. Entrez dans une colonne séparée, des conditions limites d'échantillonnage.



2. Comme dans la méthode précédente, allouez alternativement les colonnes vides de la nouvelle table et entrez les trois formules correspondantes. Dans la première colonne, nous introduisons l'expression suivante:

   = Index (A2: A29; plus petit (si ($ D $ 2 = C2: C29); ligne (C2: C29); ""); String (C2: C29) -Strkok ($ C $)) - Ligne ($ C $ 1))

   Dans les colonnes suivantes, adaptez exactement les mêmes formules, uniquement en modifiant les coordonnées immédiatement après le nom de l'opérateur, l'index sur les colonnes correspondantes dont nous avons besoin, par analogie avec la méthode précédente.

   Chaque fois, n'oubliez pas de gagner la combinaison de touches CTRL + SHIFT + ENTER.



3. L'avantage de cette méthode avant la précédente est que si nous voulons modifier les limites de l'échantillon, il ne sera pas nécessaire de modifier la formule solide elle-même, ce qui est en soi assez problématique. Il suffit dans la colonne de conditions de la feuille pour modifier les numéros de frontière sur ceux qui sont nécessaires par l'utilisateur. Les résultats de la sélection changeront immédiatement automatiquement.

Méthode 4: Échantillonnage aléatoire

Dans l'exil, avec l'aide d'une formule spéciale, il est également possible d'appliquer une sélection aléatoire. Dans certains cas, il est nécessaire de travailler avec une grande quantité de données lorsque vous devez présenter une image courante sans une analyse complète de toutes les données de la matrice.

1. À gauche de la table, ignorez une colonne. Dans la cellule de la colonne suivante, qui est située en face de la première cellule avec les données de la table, entrez la formule:

   = Adhésif ()

   Cette fonctionnalité affiche un nombre aléatoire. Pour l'activer, cliquez sur le bouton Entrée.



2. Afin de créer une colonne entière de nombres aléatoires, réglez le curseur dans le coin inférieur droit de la cellule, qui contient déjà la formule. Le marqueur de remplissage apparaît. Je l'étends avec le bouton gauche de la souris parallèle à la table avec les données jusqu'à la fin.



3. Maintenant, nous avons une gamme de cellules remplies de nombres aléatoires. Mais, il contient la formule de Calc. Nous devons travailler avec des valeurs propres. Pour ce faire, copiez-la dans une colonne vide à droite. Sélectionnez la plage de cellules avec des nombres aléatoires. Situé dans l'onglet "Home", cliquez sur l'icône "Copier" sur le ruban.



4. Nous mettons en valeur une colonne vide et cliquez sur le bouton droit de la souris sur, appelant le menu contextuel. Dans la barre d'outils "Paramètres d'insertion", sélectionnez la clause "Valeur", décrite comme des pictogrammes avec des chiffres.



5. Après cela, alors que dans l'onglet "Accueil", cliquez sur l'icône déjà familière de l'icône "Trier et filtre". Dans la liste déroulante, arrêtez la sélection à l'élément "Tri personnalisé".



6. La fenêtre de réglage de tri est activée. Assurez-vous d'installer une tick en face du paramètre "Mes données contenant des titres" si la casquette est disponible, mais pas de ticks. Dans le champ "Trier par", spécifiez le nom de cette colonne dans laquelle les valeurs copiées de nombres aléatoires sont contenues. Dans le champ «Trier», laissez les paramètres par défaut. Dans le champ "Commander", vous pouvez choisir le paramètre "ascendant" et "décroissant". Pour un échantillon aléatoire, cette valeur n'a pas. Une fois les paramètres fabriqués, cliquez sur le bouton "OK".



7. Après cela, toutes les valeurs de la table sont construites par ordre croissant ou diminution des nombres aléatoires. Vous pouvez prendre un certain nombre de premières lignes de la table (5, 10, 12, 15, etc.) et elles peuvent être considérées comme le résultat d'un échantillon aléatoire.

Leçon: Tri et filtrage des données pour exceller

Comme vous pouvez le constater, l'échantillon dans la table Excel peut être produit, à la fois à l'aide de la mise au point automatique et de l'application de formules spéciales. Dans le premier cas, le résultat sera affiché dans la table source et dans la seconde dans une zone séparée. Il est possible de produire une sélection, une condition et plusieurs. De plus, vous pouvez créer un échantillon aléatoire à l'aide de la fonction adhésive.