Question d'aujourd'hui du séminaire Atlanta Power Excel.
J'ai des données formatées avec une ligne sur deux en gris. Lorsque j'insère deux nouvelles lignes au milieu des données, les deux nouvelles lignes deviennent grises au lieu de suivre le modèle.
Jetez un œil au classeur dans l'image ci-dessous. Les lignes paires ont un remplissage gris.
Lorsque vous sélectionnez A5: A6 et Insérer des lignes, vous obtenez deux nouvelles lignes vides. Mais les deux lignes sont grises.
J'ai une solution à cela, mais cela nécessite un retour en mathématiques de 3e année.
En 2e ou 3e année, lorsque vous étiez en première division d'apprentissage, vous vous souvenez peut-être de ceci:
- 4 passe à 9: 2 avec un reste de 1 ou 2R1
- 5 entre en 17: 3 avec un reste de 2 ou 3R2
À l'école primaire, vous avez appelé cela un reste. Je trouve que beaucoup de gens s'en souviennent.
Mais plus tard, au lycée, alors que la plupart d'entre nous avaient cessé de prêter attention aux mathématiques, ils ont introduit le concept appelé Modulo.
17 Modulo 5 vaut 2.
Modulo est dérivé du mot latin Modulus.
En anglais, cela dit: "Divisez 5 en 17 et le reste est 2"
Dans Excel, vous pouvez calculer un Modulo en utilisant =MOD(17,5). Voici quelques résultats MOD:
Cet article est censé porter sur l'ombrage des lignes en gris. Pourquoi toute la théorie mathématique? Parce que MOD résout ce problème!
Consultez la colonne A ci-dessous. La =ROW()fonction vous indique dans quelle ligne vous vous trouvez.
Et la colonne C divise le nombre 2 en numéro de ligne et donne le reste. Pour les lignes paires, le reste est égal à 0. Pour les lignes impaires, le reste est égal à 1.
Configurez la mise en forme conditionnelle pour vérifier si MOD(ROW(),2)=0:
- Sélectionnez toutes les cellules de vos données.
- Accueil, mise en forme conditionnelle, nouvelle règle.
- Utilisez une formule pour déterminer les cellules à mettre en forme.
- La formule est
=MOD(ROW(),2)=0 - Cliquez sur Format… et appliquez un remplissage gris.
- Cliquez sur OK.
Chaque autre ligne est formatée en gris. Mais - le bel avantage - lorsque vous insérez de nouvelles rangées, elles conservent l'alternance de couleur gris / blanc. (Regardez la vidéo ci-dessous pour voir cela en action.)
Vous pouvez étendre ce concept. Supposons que vous vouliez deux rangées de vert suivies de deux rangées de blanc. Calculez =MOD(ROW(),4). Les quatre réponses possibles sont 0, 1, 2 et 3. Configurez une règle pour tester si le résultat est <2 et formatez ces lignes en vert.
Regarder la vidéo
Transcription vidéo
Apprenez Excel à partir de l'épisode 2209 du podcast: insertion de lignes alternées.
Salut, bienvenue à nouveau sur le netcast MrExcel, je suis Bill Jelen. Celui-ci est un classique - j'étais à Atlanta et quelqu'un a décrit qu'il avait une feuille de calcul comme celle-ci. Maintenant, hé, je ne veux pas entendre que vous ne devriez pas garder votre feuille de calcul comme ça; ils avaient une feuille de calcul comme celle-ci, et ils ont dit: "Voici notre problème. Nous avons 2 lignes physiques pour chaque donnée, et si nous devrions le faire ou non, lorsque j'insère 2 lignes, Alt + I + R et insère 2 lignes, les 3 rangées deviennent grises! " J'ai dit: "Ah, ouais, j'ai une solution pour ça."
Mais, avant d'arriver à cette solution, nous devons revenir, par exemple, à la troisième année / deuxième année, lorsque vous apprenez à diviser pour la première fois. Tu te souviens de ça? 4 va dans 9: 9/4, et la réponse serait 2 avec un reste de 1. 4 va dans 9 2 fois; 4x2 = 8; il en reste 1. Nous écririons donc 2R1. 5 passe en 17 3 fois avec un reste de 2; 3R2. Vous vous souvenez des restes, non? C'est parce que nous faisions tous attention en troisième année. Ce truc était difficile en troisième année. Au lycée, personne ne faisait plus attention aux mathématiques - nous faisions attention à la jolie fille assise devant nous - et donc vous ne vous souvenez plus quand votre professeur de mathématiques vous a appris quelque chose qui s'appelle Modulo.
Modulo est comme les restes. 17 Modulo 5 est 2 parce que 2 est le reste lorsque vous divisez 5 en 17. Vous obtenez 3 avec un reste de 2. Donc, si nous avons demandé = MOD (17,5), la réponse sera 2. MOD est valable pour Modulo, qui est vraiment juste un nom de fantaisie pour le reste. Voilà le reste. D'accord, pourquoi tout ce discours sur Modulo?
Donc, je vais utiliser une fonction ici = ROW, et = ROW me dit simplement dans quelle ligne il se trouve (3, 4, 5, 6, 7, 8, 9, 10, 11, 12…) et ensuite je ' Je vais prendre le = MOD de cette ligne, divisé par 2. D'accord, donc 2 passe à 3 1 fois avec un reste de 1. 2 passe à 4 deux fois avec un reste de 0. Cette formule ici va donner moi seulement 2 réponses possibles: soit 0 ou 1, 0 ou 1. Et la belle chose est, quand j'insère une nouvelle ligne, ces zéros ou uns - ils vont s'ajuster. Donc, fondamentalement, un 1 va me donner toutes les lignes impaires, et un 0 va me donner toutes les lignes paires. On dirait qu'ici je veux mettre en évidence les lignes paires. Donc, je vais juste choisir toutes ces données, et je vais passer au formatage conditionnel. Accueil; Mise en forme conditionnelle; Nouvelle règle; Format de cellule; Utilisez une formule pour déterminer les cellules à mettre en forme; et moi'je vais demander = MOD (ROW (), 2) = 0 et, si c'est le cas, alors ce que je veux faire, c'est que je veux formater cela avec un fond gris - remplissage de gris; cliquez sur OK; cliquez sur OK; et bam - toutes les autres lignes sont grises, tout comme nous avons commencé à Atlanta avant d'insérer ces deux lignes et de tout foutre en l'air. Mais ce qui est beau à ce sujet, c'est lorsque je décide que j'ai besoin de plus de données et que j'insère 2 lignes - Alt + I + R - les lignes sont formatées en blanc et gris, comme je le voulais. Et je pourrais même insérer 2 lignes ici - Alt + I + R - et cela continue à se formater en blanc et gris comme je le voulais. Modulo - c'est un reste.tout comme nous avons commencé à Atlanta avant d'insérer ces deux rangées et de tout foirer. Mais ce qui est beau à ce sujet, c'est lorsque je décide que j'ai besoin de plus de données et que j'insère 2 lignes - Alt + I + R - les lignes sont formatées en blanc et gris, comme je le voulais. Et je pourrais même insérer 2 lignes ici - Alt + I + R - et cela continue à se formater en blanc et gris comme je le voulais. Modulo - c'est un reste.tout comme nous avons commencé à Atlanta avant d'insérer ces deux rangées et de tout foirer. Mais ce qui est beau à ce sujet, c'est lorsque je décide que j'ai besoin de plus de données et que j'insère 2 lignes - Alt + I + R - les lignes sont formatées en blanc et gris, comme je le voulais. Et je pourrais même insérer 2 lignes ici - Alt + I + R - et cela continue à se formater en blanc et gris comme je le voulais. Modulo - c'est un reste.
Très bien, maintenant que nous avons celui-là, faisons 2 rangées de haut. À l'époque, lorsque nous imprimions nos rapports Barre verte, nos rapports Barre verte - ils n'étaient pas 1 ligne, 1 ligne, 1 ligne, mais 2 lignes, 2 lignes ou peut-être même 4 lignes, 4 lignes. Bien? Donc, si je veux faire 2 lignes de haut - voici notre fonction = ROW, puis je prends le = MOD de ce numéro de ligne et le divise par 4, et nous allons faire glisser les deux vers le bas et voir ce que nous obtenons. Nous allons maintenant avoir 4 réponses possibles - 0, 1, 2 ou 3. Donc, toute la question est, est-ce que = MOD (ROW, 4) = 0 ou 1, 2 ou 3? Votre choix. Choisissez toutes ces données ici; Alt + O + D pour le formatage conditionnel; Nouvelle règle; "Utilisez une formule"; alors nous disons = MOD (ROW (), 4) = <2 (donc, les choix sont 0, 1, 2, 3. Je veux mettre en évidence tous les autres, donc = <2; cela signifie est-ce 0 ou 1? ); et si c'est le cas, je 'je vais choisir le même vert que nous avions dans les imprimantes IBM dans les années 1980 (oui, je suis si vieux); cliquez sur OK; cliquez sur OK; et BAM! nous avons maintenant que chaque rangée mesure 2 de haut, puis 2 de haut, puis 2 de haut, 2 de haut, et encore une fois, cela fonctionne à merveille si j'insère tout un tas de rangées supplémentaires, je garde ce format de barre verte tout en bas.
J'adore quand je reçois une excellente question sur Excel dont je connais la réponse, et la réponse implique une sorte de maths bizarres et geek parce que maintenant c'est Excel geek et des maths geek. Le mot Modulo - Je ne sais pas si c'est du latin ou du grec, ou qui sait ce que c'est, mais ça résout ce problème.
MrExcel LIVe: Les 54 meilleurs conseils Excel de tous les temps. Cliquez sur ce "I" là-haut dans le coin supérieur droit pour en savoir plus sur ce livre.
Récapitulation de cet épisode du Gulf South Council au séminaire IMA d'Atlanta - hé, j'ai des données formatées avec chaque autre ligne de gris; Lorsque j'insère deux lignes, les deux lignes sont grises. J'ai une excellente solution avec le formatage conditionnel mais nécessite des mathématiques de troisième année. Divisez le numéro de ligne par 2. Quel est le reste? Cela va être 0 ou 1. Alors, configurez le formatage conditionnel, vérifiez si = MOD (ROW (), 2) = 1 et si c'est le cas, remplissez cette ligne de gris. Cela fonctionne à merveille.
N'hésitez pas à télécharger le classeur - l'URL est là-bas dans la description de Youtube - et à configurer une fonction = MOD pour mettre en évidence 3 lignes en orange et 1 ligne en bleu sarcelle, et c'est vrai. Vous pouvez également créer des classeurs hideux, en utilisant simplement la fonction = MOD. Je tiens à vous remercier d'être passé. Je vous verrai la prochaine fois pour une autre diffusion sur Internet.
Télécharger le fichier Excel
Pour télécharger le fichier Excel: inserting-alternating-rows.xlsx
Excellente pensée du jour
J'ai demandé à mes amis Excel Master leurs conseils sur Excel. Pensée d'aujourd'hui à méditer:
"Deux feuilles de calcul valent mieux qu'aucune."
Jordan Goldmeier
