Récemment, j'ai participé à plusieurs séminaires Excel Power. Lorsque vous recevez 150 comptables dans une pièce pour une matinée remplie de rires de trucs et astuces Excel, j'apprends toujours quelque chose de nouveau. Quelqu'un dans le public peut partager un truc sympa avec le reste de la salle.
Dans l'épisode d'aujourd'hui, j'ai une collection de nouvelles astuces. Ce sont en fait des astuces qui sont meilleures ou différentes de la méthode équivalente discutée dans le livre. Ils seront certainement dans la prochaine révision du livre.
Au fait, j'aimerais beaucoup venir dans votre ville pour faire un séminaire Power Excel. Si vous appartenez à un groupe professionnel tel que la section locale de l'Institut des comptables de gestion, l'Institut des auditeurs internes, l'AICPA, la PME, etc., pourquoi ne pas leur suggérer de me réserver pour l'un de leurs prochains jours CPE? Envoyez votre président de programme de chapitre à cette page pour plus de détails.
Trouvez la différence entre deux dates
Je parle en général sur les méthodes d'utilisation =YEAR(), =MONTH(), =DAY()fonctions, mais il y a un endroit frais ancienne cachette de fonction dans Excel.
La fonction DATEDIF est restée de Lotus. Bien que l'aide d'Excel ne parle pas de cette fonction, c'est un excellent moyen de trouver la différence entre deux dates.
La syntaxe est =DATEDIF(EarlierDate,LaterDate,Code)
Voici les valeurs valides que vous pouvez utiliser pour Code.
- Y - vous indiquera le nombre d'années complètes entre les deux dates.
- YM - vous indiquera le nombre de mois complets, à l'exclusion des années, entre les deux dates.
- MD - vous indiquera le nombre de jours complets, à l'exclusion des mois complets, entre les deux dates.
- M - vous dira le nombre de mois complets. Par exemple, je vis depuis 495 mois
- D - vous dira le nombre de jours. Par exemple, je vis depuis 15 115 jours. C'est une utilisation triviale, car vous pouvez simplement soustraire une date d'une autre et formater sous forme de nombre pour dupliquer ce code.
Les codes utiles sont les trois premiers codes. Lors de l'émission, j'ai démontré cette feuille de travail. Les formules identiques des colonnes D, E et F calculent le DATEDIF en années, mois et jours.
La formule de la colonne G les enchaîne pour créer du texte avec la durée en années, mois et jours.
Vous pouvez combiner cela en une seule formule. Si la cellule A2 contient la date de jonction, utilisez la formule suivante dans B2:
=DATEDIF($A2,TODAY(),"Y")&" years, "&DATEDIF($A2,TODAY(),"YM")&" months & "&DATEDIF($A2,TODAY(),"MD")&" days"
Somme des cellules visibles
Ajoutez une fonction SOMME sous une base de données, puis utilisez le filtre automatique pour filtrer la base de données. Excel inclura de manière ennuyeuse les lignes cachées dans la somme!
Suivez plutôt ces étapes:
- Utilisez Données - Filtre - Filtre automatique pour ajouter les listes déroulantes de filtre automatique.
- Choisissez un filtre pour un champ
- Accédez à la cellule vide sous l'une des colonnes numériques de la base de données.
- Cliquez sur la lettre grecque E (Sigma) dans la barre d'outils standard. Au lieu d'entrer
=SUM(), Excel entrera=SUBTOTAL()et utilisera les codes pour empêcher l'inclusion de lignes masquées.
Touche de raccourci pour répéter la dernière commande
La touche F4 répétera la dernière commande que vous avez effectuée.
Par exemple, sélectionnez une cellule et cliquez sur l'icône B pour mettre la cellule en gras.
Maintenant, sélectionnez une autre cellule et appuyez sur F4. Excel rendra cette cellule en gras.
F4 se souviendra de la dernière commande. Ainsi, vous pouvez créer une cellule en italique, puis utiliser F4 pour rendre de nombreuses cellules en italique.
Pré-sélectionnez la plage de cellules à saisir
Dans le livre, je vous montre comment utiliser Outils - Options - Modifier - Déplacer la sélection après entrer la direction - Droite pour forcer Excel à se déplacer vers la droite lorsque vous appuyez sur la touche Entrée. C'est bien lorsque vous devez saisir des données sur une ligne.
Cela est particulièrement utile si vous saisissez des chiffres sur le clavier numérique. L'astuce vous permet de taper 123 Entrée et de vous retrouver dans la cellule suivante. En gardant vos mains sur le pavé numérique, vous pouvez saisir les chiffres plus rapidement.
Quelqu'un a suggéré une amélioration de cette technique. Pré-sélectionnez la plage dans laquelle vous allez saisir les données. L'avantage est que lorsque vous arrivez à la dernière colonne et appuyez sur Entrée, Excel passe au début de la ligne suivante.
Dans l'image ci-dessous, appuyer sur Entrée vous déplacera vers la cellule B6.
Ctrl + Faites glisser la poignée de remplissage
J'ai montré le truc de la poignée de remplissage à plusieurs reprises dans l'émission. Entrez lundi en A1. Si vous sélectionnez la cellule A1, il y a un point carré dans le coin inférieur droit de la cellule. Ce point est la poignée de remplissage. Cliquez sur la poignée de recopie et faites-la glisser vers le bas ou vers la droite. Excel remplira mardi, mercredi, jeudi, vendredi, samedi, dimanche. Si vous faites glisser pendant plus de 7 cellules, Excel recommencera lundi.
Excel est vraiment bon. Il peut étendre automatiquement toutes ces séries:
- Lundi - mardi, mercredi, jeudi, vendredi, etc.
- Janvier - février, mars, avril, etc.
- Janvier - février, mars, etc.
- Q1 - Q2, Q3, Q4 etc.
- Qtr 1 - Qtr 2, Qtr 3, Qtr 4, Qtr 1, etc.
- 1ère période - 2ème période, 3ème période, 4ème période, etc.
- 23 octobre 2006 - 24 octobre 2006, 25 octobre 2006, etc.
Étant donné qu'Excel peut faire TOUTES ces séries étonnantes, à quoi vous attendriez-vous si vous entrez 1 et faites glisser la poignée de recopie?
Vous pourriez vous attendre à obtenir 1, 2, 3,…
Mais vous obtenez vraiment 1, 1, 1, 1, 1,…
Le livre parle d'une méthode alambiquée. Entrez 1 dans A1. Entrez 2 dans A2. Sélectionnez A1: A2. Faites glisser la poignée de recopie. Il y a un meilleur moyen.
Entrez simplement 1 dans A1. Ctrl + Faites glisser la poignée de recopie. Excel remplira 1, 2, 3. Maintenir la touche Ctrl enfoncée semble remplacer le comportement normal de la poignée de remplissage.
Un participant à un séminaire a déclaré qu'il souhaitait saisir une date, faire glisser la date et demander à Excel de conserver la même date. Si vous maintenez la touche Ctrl enfoncée tout en faisant glisser la poignée de remplissage, Excel remplacera le comportement normal (incrémentation de la date) et vous donnera la même date dans toutes les cellules.
