Excel - Formatage conditionnel - Insérer une ligne

À l'aide d'Offset ou Indirecte, dans "s'Applique À" ne semble pas fonctionner. Est-il un autre moyen d'arrêter la mise en forme conditionnelle de la rupture après l'insertion de lignes/s

J'ai un format conditionnel pour une gamme pour, par exemple, $O$19:$O$105. La condition est "si la valeur de la cellule > 10', il est mis en forme avec la couleur rouge.

Le problème est lorsque j'insère une ligne dans excel, la mise en forme de la gamme se divise et je reçois 2 règles de mise en forme. Par exemple les 2 règles avec la gamme de $O$19,$O$21:$O$105 & $O$à 20$, respectivement, si j'insère une nouvelle ligne au 20e rang.

Généralement de condition comme celle-ci, il ne peut pas d'importance, si les règles sont divisées en plusieurs gammes. Mais pour des conditions comme "mettre en évidence le top 10", il provoque des résultats inattendus.

J'ai essayé les sans beaucoup de chance:

  1. Essayé à l'aide indirecte - mais excel semble résoudre la formule et enregistre la règle de mise en forme et donc ne fonctionne pas avec des inserts comme prévu
  2. Essayé d'utiliser le décalage ici encore, excel résout la plage de même que ci-dessus.

Quelqu'un sait comment écrire une mise en forme conditionnelle qui ne rompt pas avec les insertions de ligne?

[EDIT] j'ai réalisé que la ligne d'insertion n'est pas la cause de la scission, de la mise en forme conditionnelle règles. J'ai aussi copier une ligne et de le coller dans la ligne insérée, qui est en train de faire cela. Si j'opte pour le collage spécial et choisir des formules, sa fonctionne bien.

source d'informationauteur ssp

  1. 6

    Je sais que c'est un vieux thread mais voici une autre solution qui est très simple et fonctionne très bien.

    Il suffit d'insérer une nouvelle ligne ou colonne souhaitée. Puis de sélectionner et de copier une ligne/colonne qui a la bonne mise en forme conditionnelle. Passé de Spécial dans la nouvelle ligne/colonne que vous venez de créer et sélectionnez l'option pour "la fusion de Tous les formats conditionnels". Votre mise en forme conditionnelle règles doivent maintenant être mis à jour automatiquement.

    Heureux Excel-ing =)

  2. 5

    C'est un problème général avec les formats conditionnels. Si vous insérez des lignes ou maj choses autour, Excel suppose que vous voulez le format conditionnel décalage avec les cellules, et que vous souhaitez insérer des cellules avec leur format d'origine (ou aucune).

    De sorte qu'il essaie de son mieux pour séparer la mise en forme de la gamme en fonction des modifications apportées. Malheureusement "de son mieux" n'est pas très bonne. Dans le cas, votre mise en forme de règles dupliqué à votre insu; dans le malheureux cas, ils cassent pour certains ou de la totalité de la gamme appliquée.

    C'est surtout un problème si vous travaillez avec ListObjects (un.k.un. "Les tableaux Excel"). Insérez des lignes, de la réorganiser un peu, faire glisser des valeurs autour de la prochaine fois que vous regardez dans votre mise en forme conditionnelle liste, vous avez des dizaines à des centaines de dupliquer les règles. (exemple: http://blog.contextures.com/archives/2012/06/21/excel-2010-conditional-formatting-nightmare/)

    Dans mon expérience, la façon la plus rapide de résoudre le problème est de supprimer toutes les règles et de les recréer (ou pas).

    Certains sidenotes:

    • L'applique à la gamme est toujours absolue. Il n'y a pas moyen de contourner cela.
    • Pour aggraver les choses, les formats conditionnels sont traités comme des formules volatiles, ce qui signifie qu'ils sont recalculées sur un bon nombre de reprises (de l'ouverture d'un autre fichier, le défilement, etc). Si vous ne remarquez pas le split-up, ils peuvent ralentir l'ensemble de l'application de manière significative bout d'un moment.
    • Si vous allez pour VBA, vous voudrez probablement utiliser la Worksheet_Calculate les cas, au moins si vos formules de se référer à d'autres feuilles de calcul (être conscient de noms!)
  3. 2

    J'ai eu ce problème tout en essayant de créer des rapports - une fois qu'ils sont finis, ils n'ont pas besoin de changer, mais pendant que je fais, je continue à ajouter de nouvelles lignes, et chaque nouvelle ligne de fou jusqu'à la mise en forme conditionnelle.

    Ce n'est pas une bonne solution, mais c'était le meilleur que j'ai pu trouver sans avoir recours à VBA - qui était:

    a) Faire la mise en forme conditionnelle règles s'appliquent à l'ensemble de la colonne ou plus à un moment

    par exemple au lieu de définir la mise en forme conditionnelle sur C2 et C17, mettre une colonne supplémentaire, et d'écrire "celui-ci" dans les lignes 2 et 17, puis définir la mise en forme pour l'ensemble de la colonne C de "si la colonne autre dit" il y a une "puis appliquer ce format"

    b) Modifier, s'Applique À la juste $C$1:$C$2.

    c) d'Apporter des modifications et insérer des lignes et des trucs

    d), Puis revenir en arrière et changer la s'Applique À $C:$C

    De cette façon, lorsque vous changer les choses et ajouter des choses, la mise en forme conditionnelle n'est pas là, mais ensuite, vous mettez toutes les revenir plus tard.

    Si, à une date ultérieure, vous devrez ajouter un peu plus de lignes, d'abord changer de $C:$C $C$1:$C$2, puis effectuez les modifications, puis de le remettre à $C:$C. de Cette façon, vous n'avez pas entièrement réécrire toutes les règles de mise en forme à partir de zéro, comme vous le feriez si vous faites ce que j'ai fait auparavant, ce qui était tout de tous les supprimer, malédiction, et recommencer 😉

    Évidemment, si vous avez l'intention de l'insertion de lignes jusqu'en haut à la ligne 1 ou 2, ça ne marchera pas, mais vous pouvez le positionner à quelques autres lignes que vous savez que vous ne le modifiez pas.

  4. 1

    J'ai réalisé que la ligne d'insertion n'est pas la cause de la scission, de la mise en forme conditionnelle règles. J'ai aussi copier une ligne et de le coller dans la ligne insérée, qui est en train de faire cela. Si j'opte pour le collage spécial et choisir des formules, sa fonctionne bien.

    Cependant, je me demande si il n'y aurait jamais être nécessaire d'utiliser "INDIRECTE" ou "OFFSET" dans "s'Applique à" domaine de la mise en forme Conditionnelle. Si oui, ça va être un problème.

  5. 1

    Je me rends compte que c'est un vieux post, mais j'ai été en cours d'exécution dans le même problème et ont depuis trouvé comment ne pas obtenir la scission de mise en forme conditionnelle règles.

    Dans ma feuille de calcul Excel 2010, j'ai entrez des dates dans la colonne B. Quelques fois j'ai de la graisse du doigt la date d'entrée, c'est pourquoi je voulais conditionnellement format. D'abord, j'ai été la sélection d'une plage (B2:B1960), donc ma formule dans la mise en forme Conditionnelle Règle serait "=B2:B1960>AUJOURDHUI()".

    Bien, qui a bien fonctionné jusqu'à ce que je suis allé à insérer de nouvelles lignes entre les lignes existantes. Les règles permettrait de diviser tout comme l'OP décrit. Il m'est arrivé à plusieurs sites web et trouvé un Microsoft Office site qui m'a indiqué la réponse. Il est mentionné pour mettre en surbrillance la plage que vous voulez formater, mais le changement de la formule "=B2>AUJOURDHUI()".

    Puisque la modification de la formule, je peux maintenant insérer de nouvelles lignes entre les lignes et ne pas avoir l'split Règles comme avant. Voici le lien pour cette page web. http://office.microsoft.com/en-us/excel-help/use-a-formula-to-apply-conditional-formatting-HA102809768.aspx

  6. 1

    Ce qui fonctionne pour moi, c'est, lorsque vous insérez une ligne, de ne pas copier la mise en forme à partir d'une autre ligne. Seulement copier-collage spécial les formules. La mise en forme conditionnelle, puis ne pas obtenir les divise.

  7. 1

    Ma solution à ce problème était de compensation de la mise en forme à partir de la source de la copie.
    Étapes:

    1. La source de la copie vers le presse-papiers
    2. Ouvrir nouveau document excel
    3. Collage Spécial, sélectionnez la formule (copies des valeurs et des formules et omet de mise en forme)
    4. Le copier dans le presse-papiers
    5. Maintenant, vous pouvez soit coller à la source de l'avant de l'utiliser, ou vous insérez des lignes dans la feuille avec la mise en forme conditionnelle (note juste en insérant des lignes ne va pas changer la portée de la mise en forme conditionnelle des règles) et de coller le contenu du presse-papiers dans les nouvelles lignes.

    Cela a fonctionné pour moi à l'aide de MS Excel 2016

  8. 1

    Bien que c'est assez vieux sujet, ma des feuilles de calcul Excel ont aussi souffert de reproduire la mise en forme conditionnelle lors de l'insertion d'une nouvelle ligne.

    J'ai été capable de travailler autour d'elle. Permettez-moi de partager avec d'autres, il peut aussi aider.

    Dans mon cas, tous mes de mise en forme conditionnelle règles ont été appliquées à l'ensemble de la table. J'ai réalisé que seulement certaines règles sont dupliqués lors de l'insertion d'une nouvelle ligne. Ces règles sont les préparations à base de la comparaison des valeurs dans des lignes différentes.

    Dans mon cas, j'ai voulu rendre une bordure horizontale lorsque les valeurs de deux lignes adjacentes diffèrent, par exemple:

    =$A2 <> $A1

    Si j'utilise DÉCALAGE de se référer à la ligne précédente, tout est correct, pas dupliqué règles de mise en forme conditionnelle.

    =$A2<>OFFSET($A2; -1; 0)

    J'ai, en fait, ces formules de mise en forme conditionnelle dans une colonne masquée, mais le résultat devrait être le même.

  9. 0

    Voici un fil similaire qui peut être ont-il droit de la piste:

    Comment utiliser le Worksheet_Change événement pour la mise en forme Conditionnelle?

    Il présente une solution de contournement pour R1C1 mise en forme de style qui peut ne pas être touchée par les inserts (non testé) avec le VBA approche que j'ai mentionné dans les commentaires.

  10. 0

    J'ai trouvé un processus simple qui semble fonctionner de manière cohérente pour l'insertion de nouvelles lignes ou de colonnes ET de préserver la continuité de la mise en forme conditionnelle des règles (dans Office 2010, au moins), comme suit:

    1. Faire un simple "Insérer" de votre nombre désiré de nouvelles lignes ou des colonnes ci-dessus, en dessous ou à gauche ou à droite d'une ligne ou d'une colonne contenant la mise en forme conditionnelle pour être conservé.

    NOTE a) Votre mise en forme conditionnelle est appliquée automatiquement à l'insertion de lignes ou de colonnes
    sans que vous n'ayez plus rien à faire. La mise en forme doit avoir été hérité
    de la voisine de rangée ou de la colonne.
    b) Toutes les frontières de la mise en forme doit également avoir été copié dans le nouvellement inséré cellules.

    1. Sélectionnez une ligne, une colonne ou de la plage (en cliquant dessus) qui est adjacente à la nouvellement inséré, et qui contient la mise en forme conditionnelle (et les formules et données le cas échéant) pour être copié.

    2. Passez votre souris sur le coin inférieur gauche ou en bas à droite de la plage sélectionnée jusqu'à ce que vous voyez un plus signe "+" apparaît (à ne pas confondre avec la ligne re-taille gadget qu'elles se ressemblent).

    3. Gauche cliquez et maintenez le clic sur le "+", et faites-le glisser sur le désiré des lignes, des colonnes ou la plage à être mises en forme, puis relâchez-le.

    NOTE: j'ai créer une mise en forme Conditionnelle des règles de référencement que d'une cellule:
    Exemple) dans le champ intitulé "forme aux valeurs pour lesquelles cette formule est vraie:", de créer une règle comme ... =ET($B8="",$C8="",$D8="",$K8<>""), où cette règle s'Applique-à-dire la gamme ... =$B$8:$D$121,$J$8:$M$121.

  11. 0

    Ce que vous avez à faire est de 1) insérer une nouvelle ligne 2) copier la ligne que vous voulez cloner 3) collage spécial "Fusion mise en forme Conditionnelle"

    Pas intuitive et une montagne de la formation des utilisateurs, si vous voulez que les autres à partager les classeurs que vous créez

  12. 0

    En 2013, une Fois que vous trouver vos règles de mise en forme ont été divisés, dupliqué, définir une nouvelle namedrange pour chaque format. Ensuite, réglez l'applique à l' =[Plage]. Excel va remplacer la plage nommée à la plage réelle. Puis supprimer les doublons de formats.

  13. 0

    Je suis en train de construire une solution que les autres avec un mélange de Excel les niveaux de compétences entrent en jeu, donc j'ai besoin de quelque chose de plus simple et plus cohérent que d'avoir à se souvenir de copier et de coller une certaine manière.

    Dans Excel 2016, vous pouvez insérer un tableau à partir d'une sélection qui vous donne l'avantage d'utiliser les références structurées (exemple: tblTOP[Type], les données de référence dans la colonne Type de la table nommée tblTOP).

    J'ai ensuite trouvé cette réponse sur le site de Microsoft, qui montre un moyen efficace pour faire référence à une table dans la formule une partie de la MUCOVISCIDOSE:
    la mise en forme conditionnelle les références structurées

    Donc, cet établis...

    C'est ce que je suis en train de travailler avec:

    tblTop Colonnes

    - Je configurer ma mise en forme conditionnelle, de sorte que lorsque je change la valeur de Type de n'importe où entre de A à E, il va changer la ligne de la couleur correspondante. (Exemple illustré: B tourne la ligne verte)

    Cela a été accompli par l'aide de la formule =INDIRECT("tblTOP[Type]")="B"

    Quand je suis allé à ajouter une ligne cependant, j'ai eu la même mise en forme appliquée à la deuxième rangée :(.

    Cassé FC entre deux lignes

    CF formule qui a travaillé

    Longue histoire courte, la formule suivante est ce que je suis venu avec ma CF règle à cette ligne particulière et affecte pas les lignes ajoutées ou enlevé:

    =INDIRECT("tblTOP[@Type]")="B"

    L'ajout de la '@' devant la référence structurée garde les choses qui se passe pour que la ligne de donnée. Nice.

    Alors maintenant, je peux onglet grâce à ou utiliser le menu contextuel pour ajouter une nouvelle ligne et il attend le type de sélection pour déterminer la couleur pour cette ligne seulement.

    Nouvelle Ligne a été ajoutée propre

    Nouvelle Ligne de travail comme prévu

    Je n'ai pas testé avec un collage de cellules que le but de ce tableau est à l'utilisateur de saisir des données et à ajouter/supprimer des lignes comme nécessaire, donc je ne peux pas dire si cela fonctionne avec coller une ligne.

    Espère que cela aide quelqu'un avec une mise en forme conditionnelle dans une table.

  14. -1

    Je l'ai eu à travailler sur Excel Mac 2011 par les étapes suivantes

    • de l'insertion de la nouvelle ligne
    • la copie de l'un par-dessus (avec la mise en forme conditionnelle déjà appliquée)
    • mettant en évidence la nouvelle ligne et collage SPÉCIAL -> FUSION mise en forme CONDITIONNELLE.

    Le FC règles resté non divisée et mis à jour pour inclure une ligne supplémentaire.