La capacité de validation d'Excel est excellente, mais elle ne fait pas une chose importante (sans l'astuce suivante) : ajouter automatiquement nouvel enregistrementà la liste utilisée comme source pour la liste de validation.

Si vous avez déjà travaillé avec la validation, vous savez qu'il s'agit d'une fonctionnalité très intelligente. Le plus impressionnant est peut-être sa capacité à ajouter une liste à n'importe quelle cellule où l'utilisateur peut sélectionner des valeurs. Ce serait formidable si, lors de la saisie d'un nouveau nom dans le champ coché Cellule Excel automatiquement ajouté à la liste. C'est possible grâce à l'astuce suivante.

Soit une liste de noms dans la plage A1:A10 (Fig. 2.24).

Ces noms représentent les employés de l'entreprise. Il n'est pas rare que de nouveaux employés soient ajoutés à une liste comme celle-ci, mais pour le moment, la seule façon de le faire est d'ajouter de nouveaux noms à la fin de la liste, puis de les sélectionner dans la liste dans la cellule cochée. Pour contourner cette limitation, procédez comme suit. Dans la cellule A11, entrez la formule suivante et copiez-la jusqu'à la ligne 20 (Fig. 2.25) - notez la référence relative à la cellule A10 : =IF(OR($D$1="";COUNTIF($A$1:A10;$ D $l));"x";$D$l) , ​​​​en russe Versions Excel: =SI(OU($D$1="";NB.SI($A$1:A10,$D$1)),"x",$D$1) .

Sélectionnez maintenant la commande Formules> Attribuer un nom (Formules> Définir un nom) et dans le champ Nom (Noms dans le classeur), entrez MyNames. Dans le champ Formule (Fait référence à), entrez la formule suivante (Figure 2.26), cliquez sur le bouton Ajouter, puis cliquez sur le bouton OK : =OFFSET(Sheet1!$A$1;0;0;COUNTIF(Sheet1!$A:$ A ;"x");1) , dans la version russe d'Excel : =CMESCH(Sheet1!$A$1;0;0;COUNTIF(Sheet1!$A:$A;"x");1) .

Sélectionnez la cellule D1 et choisissez Données > Validation. Dans le champ Type de données (Autoriser), sélectionnez Liste (Liste) et dans le champ Source (Source), saisissez =MesNoms ; assurez-vous que la zone de liste est cochée valeurs autorisées(En cellule). Cliquez sur l'onglet Alerte d'erreur et décochez la case Afficher l'alerte d'erreur après la saisie de données non valides. Cliquez sur le bouton OK. Vous verrez le résultat comme dans la Fig. 2.27.

Cliquez avec le bouton droit sur l'onglet de la feuille et sélectionnez menu contextuel sélectionnez la commande Afficher le code. Entrez le code indiqué dans le listing 2.5.

// Listing 2.5 Private Sub Worksheet_Calculate() On Error Resume Next Application.EnableEvents = False Range("MyNames") = Range("MyNames").Value Application.EnableEvents = True On Error GoTo 0 End Sub

Pour revenir à Excel et enregistrer le classeur, fermez la fenêtre. Sélectionnez maintenant la cellule D1, tapez un nom ne figurant pas dans la liste et appuyez sur la touche Entrée. Sélectionnez à nouveau la cellule D1 et examinez la liste. Maintenant, la liste devrait contenir un nouveau nom (Fig. 2.28).

Si vous souhaitez ajouter plus de 10 nouveaux noms à la liste, copiez la formule sous la ligne 20.

Pour ceux qui travaillent constamment avec Microsoft Excel, vous devez souvent vous interroger sur la bonne recopie des données du tableau tout en préservant la mise en forme, les formules ou leurs valeurs.

Comment insérer une formule dans un tableau Excel et conserver le format tableau ? Après tout, lors de la résolution de ce problème, vous pouvez gagner du temps en voiture. Pour ce faire, nous utiliserons la fonction "Coller spécial" - c'est un outil simple qui vous permet de résoudre rapidement des problèmes complexes et de maîtriser principes généraux Son usage. L'utilisation de cet outil simple vous permet de formater rapidement toutes les tables dans le même format et d'obtenir le résultat dont vous avez besoin.

Comment copier un tableau avec une largeur de colonne et une hauteur de ligne

Disons que nous avons une telle table, dont le format doit être conservé lors de la copie :

Lors de la copie sur une autre feuille en utilisant la méthode habituelle Ctrl + C - Ctrl + V. Nous obtenons le résultat indésirable :

Par conséquent, vous devez le "développer" manuellement afin de le mettre sous une forme pratique. Si la table est grande, il faudra beaucoup de temps pour "s'amuser". Mais il existe un moyen de réduire considérablement les pertes temporaires.

Méthode 1 : Utiliser un insert spécial

Elle a reçu les paramètres d'origine et semble parfaitement précise.

Méthode 2 : sélectionnez les colonnes avant de copier

Secrète cette méthode en ce que si avant de copier le tableau, sélectionnez ses colonnes avec les en-têtes, alors lorsque vous collez la largeur de chaque colonne sera également copiée.

Pour chaque cas, il est rationnel d'appliquer sa propre méthode. Mais il convient de noter que la deuxième méthode nous permet non seulement de transférer rapidement le tableau avec le format, mais également de copier la hauteur des lignes. Après tout, il n'y a pas d'option "hauteur de ligne" dans le menu spécial de collage. Par conséquent, pour résoudre un tel problème, vous devez agir comme suit :

Insérer des valeurs de formule tout en conservant le format du tableau

Paste Special, bien qu'il ne soit pas parfait, ne doit pas sous-estimer ses capacités. Par exemple, comment insérer une valeur de formule dans une feuille de calcul Excel et conserver le format de cellule.

Pour résoudre un tel problème, vous devez effectuer 2 opérations à l'aide d'une pâte spéciale dans Excel.

Étant donné que la plage copiée est toujours dans le presse-papiers après la copie, nous appelons immédiatement à nouveau le collage spécial où nous sélectionnons l'option «formats». Cliquez sur OK.

Nous avons collé les valeurs de la formule dans le tableau et enregistré les formats de cellule. Comme vous l'avez deviné, vous pouvez effectuer une troisième opération pour copier la largeur des colonnes, comme décrit ci-dessus.

Conseil utile! Afin de ne pas effectuer la deuxième opération, vous pouvez utiliser l'outil Format Painter.

Microsoft Excel offre aux utilisateurs des possibilités presque illimitées pour calculer les fonctions les plus simples et effectuer un certain nombre d'autres procédures. L'utilisation du programme vous permet de définir des formats, d'enregistrer des valeurs de cellule, de travailler avec des formules, de les transférer et de les modifier d'une manière pratique pour les utilisateurs.

Coller ou Ctrl+V est peut-être l'outil le plus efficace à notre disposition. Mais le possédez-vous bien ? Saviez-vous qu'il existe au moins 14 différentes manières insertion de données dans des feuilles Excel ? Surpris? Alors lisez cet article pour devenir un maître du collage.

Ce billet comporte 2 parties :

- Techniques de collage de base

- Insertion via informatique

1. Insérer des valeurs

Si vous souhaitez simplement coller des valeurs à partir de cellules, appuyez successivement sur les touches Z, M et Z tout en maintenant la touche Alt enfoncée, puis appuyez sur la touche Entrée. Cela est nécessaire lorsque vous devez vous débarrasser du formatage et ne travailler qu'avec des données.

À partir d'Excel 2010, la fonctionnalité Coller les valeurs apparaît dans le menu contextuel lorsque vous cliquez avec le bouton droit

2. Formats d'insertion

14. Quelle est votre méthode de collage préférée ?

Il y en a beaucoup d'autres chemins cachés des insertions telles que l'insertion de données XML, d'images, d'objets, de fichiers, etc. Mais je me demande quelles techniques d'insertion intéressantes vous utilisez. Quelle est votre méthode d'insertion préférée ?

Insérez et supprimez des lignes, des colonnes et des cellules pour ajuster au mieux vos données sur la feuille.

Note: Microsoft Excel a les limites de ligne et de colonne suivantes : 16 384 colonnes de large sur 1 048 576 lignes de haut.

Insertion et suppression d'une colonne

Pour insérer une colonne, sélectionnez-la puis sur l'onglet maison appuie sur le bouton Insérer et sélectionnez l'élément Insérer des colonnes sur la feuille.

Pour supprimer une colonne, sélectionnez-la puis sur l'onglet maison appuie sur le bouton Insérer et sélectionnez l'élément Supprimer les colonnes de la feuille.

Vous pouvez également cliquer avec le bouton droit en haut d'une colonne et sélectionner Insérer ou Supprimer.

Insertion et suppression d'une ligne

Pour insérer une ligne, sélectionnez-la puis sur l'onglet maison appuie sur le bouton Insérer et sélectionnez l'élément Insérer des lignes sur une feuille.

Pour supprimer une ligne, sélectionnez-la puis sur l'onglet maison appuie sur le bouton Insérer et sélectionnez l'élément Supprimer les lignes de la feuille.

Vous pouvez également cliquer avec le bouton droit sur la ligne en surbrillance et sélectionner Insérer ou Supprimer.

Insertion d'une cellule

Sélectionnez une ou plusieurs cellules. Faites un clic droit et sélectionnez la commande Insérer.

Dans la fenêtre Insérer sélectionnez la ligne, la colonne ou la cellule à insérer.

Par exemple, pour insérer une nouvelle cellule entre les cellules Summer et Winter :

Cliquez sur la cellule "Hiver".

Sur l'onglet maison Insérer et sélectionnez la commande Coller les cellules (Maj vers le bas).

Une nouvelle cellule est ajoutée au-dessus de la cellule "Hiver":

Insertion de lignes

Pour insérer une ligne: Cliquez avec le bouton droit sur toute la ligne au-dessus de laquelle vous souhaitez en insérer une nouvelle et sélectionnez la commande Insérer des lignes.

Pour insérer plusieurs lignes, procédez comme suit : Mettez en surbrillance le même nombre de lignes au-dessus desquelles vous souhaitez en ajouter de nouvelles. Cliquez avec le bouton droit sur la sélection et sélectionnez la commande Insérer des lignes.

insérer des colonnes

Pour insérer une nouvelle colonne, procédez comme suit : Cliquez avec le bouton droit sur la colonne entière à droite de l'endroit où vous souhaitez ajouter la nouvelle colonne. Par exemple, pour insérer une colonne entre les colonnes B et C, faites un clic droit sur la colonne C et choisissez Insérer des colonnes.

Pour insérer plusieurs colonnes, procédez comme suit : Sélectionnez le même nombre de colonnes à droite desquelles vous souhaitez en ajouter de nouvelles. Cliquez avec le bouton droit sur la sélection et sélectionnez la commande Insérer des colonnes.

Suppression de cellules, lignes et colonnes

Si vous n'avez plus besoin de cellules, de lignes ou de colonnes, procédez comme suit pour les supprimer :

Sélectionnez les cellules, lignes ou colonnes que vous souhaitez supprimer.

Sur l'onglet maison cliquez sur la flèche sous le bouton Supprimer et sélectionnez l'option souhaitée.

Lorsque des lignes ou des colonnes sont supprimées, les lignes et colonnes suivantes sont automatiquement décalées vers le haut ou vers la gauche.

Conseil: Si vous changez d'avis immédiatement après avoir supprimé une cellule, une ligne ou une colonne, appuyez simplement sur CTRL+Z pour la restaurer.

Assez souvent, la question se pose, comment passer à une autre ligne à l'intérieur d'une cellule dans Excel ? Cette question se pose lorsque le texte de la cellule est trop long ou qu'un retour à la ligne est nécessaire pour structurer les données. Dans ce cas, il n'est pas pratique de travailler avec des tables. En règle générale, l'habillage du texte s'effectue à l'aide de la touche Entrée. Par exemple, dans Programme Microsoft Mot de bureau. Mais en Microsoft Office Excel, lorsque vous appuyez sur Entrée, nous arrivons à la cellule inférieure suivante.

Nous devons donc transférer le texte sur une autre ligne. Pour transférer, vous devez appuyer sur une combinaison de touches Alt+Entrer. Après cela, le mot à droite du curseur sera déplacé vers la ligne suivante.

Habillage automatique du texte dans Excel

Dans Excel, sous l'onglet Accueil, dans le groupe Alignement, il y a un bouton Habiller le texte. Si vous sélectionnez une cellule et cliquez sur ce bouton, le texte de la cellule s'ajustera à nouvelle ligne automatiquement en fonction de la largeur de la cellule. Le transfert automatique nécessite une simple pression sur un bouton.

Supprimer un trait d'union avec une fonction et un trait d'union

Afin de supprimer le carry, nous pouvons utiliser la fonction SUBSTITUTE.

La fonction remplace un texte par un autre dans la cellule spécifiée. Dans notre cas, nous remplacerons le caractère espace par un trait d'union.

Syntaxe de la formule :

REMPLACER(texte, ancien_texte, nouveau_texte, [numéro d'entrée])

La forme finale de la formule :

REMPLACER(A1,CAR(10), " ")

• A1 - une cellule contenant du texte avec des tirets,
• CHAR(10) – caractère de saut de ligne,
• " " - espace.

Si, au contraire, nous devons insérer un caractère de saut sur une autre ligne, au lieu d'un espace, nous ferons cette opération vice versa.

REMPLACER(A1; " "; CAR(10))

Pour que la fonction fonctionne correctement dans l'onglet Alignement (Format de cellule), la case "Enchaînement par mots" doit être cochée.

Transfert à l'aide de la formule CONCATENER

Pour résoudre notre problème, nous pouvons utiliser la formule CONCATENER.

Syntaxe de la formule :

CONCATENER (text1,[text2]…)

Nous avons du texte dans les cellules A1 et B1. Introduisons la formule suivante dans B3 :

CONCATENER(A1,CAR(10),B1)

Comme dans l'exemple que j'ai donné ci-dessus, pour que la fonction fonctionne correctement dans les propriétés, vous devez cocher la case "wrap by words".