La mise en forme conditionnelle avec la Formule à l'aide de Références Relatives

J'ai regardé autour pour une réponse à cela et trouvé un fil similaire, mais il n'a pas tout à fait répondu à ma question donc, ici, va.

J'ai deux colonnes, chacune avec de 262 000 lignes de données. Je suis en train de mettre en surbrillance les données dans l'une des colonnes en fonction de deux conditions. Je crois que j'ai la formule pour la mise en forme conditionnelle, mais le problème est qu'il ne s'applique pas à l'ensemble de la colonne que je veux. Par exemple:

=AND(K6<=1638, L6=0)

C'est la formule que j'ai. Je veux que la mise en forme pour copier et coller tout le chemin vers le bas de la colonne K, l'itération à chaque fois. Par exemple, la cellule du dessous doit de mise en forme qui dit la chose suivante: =AND(K7=1638, L7=0) mais je n'arrive pas à cela. Il ne cesse de regarder les valeurs dans les cellules K6 et L6.

Est le format peintre pas la manière d'obtenir cette mise en forme appliquée à chaque cellule?

  • Vous ne pouvez pas compter sur d'habitude Excel "formule de glisser lorsque vous effectuez la mise en forme conditionnelle. Vous aurez besoin de s'appuyer sur des fonctions telles que ROW().
  • Quelle est la portée de utilisez-vous pour votre s'Applique À:
  • yep, juste comme vous l'avez dit, j'ai réalisé que c'est le problème. Je l'ai essayé à nouveau, cette fois en soulignant l'ensemble de la gamme et à l'aide de la même formule, cette fois ça a fonctionné. Cela ne fait pas vraiment de sens pour moi, parce que même si l'ensemble de la gamme est mise en évidence, la formule encore dit explicitement "K6" et "L6" donc je ne sais pas vraiment comment il sait que l'itération à chaque fois, mais ce que je suppose.
  • Si vous ne faites pas les plages, dans les formules absolu, la mise en forme conditionnelle va changer les références basé sur la cellule en haut à gauche. Si vous avez plus d'une colonne dans votre s'Applique À: vous voulez rendre la colonne partie des références absolues.
  • Donc, vous dites qu'il doit être vraiment: =AND($K$6<=1638, $L$6=0 ?
  • Oh, attendez, non, vous n'êtes pas en disant cela. Je comprends maintenant. La mise en forme conditionnelle ne s'applique qu'à la colonne K. Il est, cependant, de vérifier les valeurs dans deux colonnes différentes.
  • =AND($K6<=1638, $L6=0 la ligne doit être relative.
  • Bizarre. J'ai été en mesure de le faire sans avoir à faire de la famille, comme ça.

InformationsquelleAutor Ryan_C | 2016-07-18
  1. 2

    Avez-vous essayé d'utiliser la mise en forme conditionnelle comme ci-dessous?

    J'ai simplifié la formule dans l'exemple, mais devrait fonctionner pour votre cas.

    Il me semble plus pratique pour les grandes plages de Format de Peintre.

    La mise en forme conditionnelle avec la Formule à l'aide de Références Relatives

    La formule =A2 > 4 a la cellule A2 codé en dur, mais la magie fonctionne lorsqu'il est appliqué à des cellules A6, A7, etc.. Excel utilise cette formule par rapport à la première cellule de la plage, donc si vous voulez mettre en surbrillance les cellules dans la colonne A qui sont supérieures à la colonne B, et votre gamme a commencé en A2, votre formule serait = A2 > B2.

    • Oui, c'est ce que vous avez à faire, il s'avère.
    • La formule =A2 > 4 a la cellule A2 codé en dur, mais la magie fonctionne lorsqu'il est appliqué à des cellules A6, A7, etc.. mais il n'a jamais expliqué comment cela fonctionne. Ce que si je fait voulez pour avoir un point de référence fixe pour A2 au lieu de A{row} - ou vice-versa?
    • Excel utilise cette formule par rapport à la première cellule de la plage, donc si vous voulez mettre en surbrillance les cellules de la colonne A qui sont supérieures à la colonne B, et votre gamme a commencé en A2, votre formule serait = A2 > B2.
    • fixe (aka absolue) les références à utiliser le symbole$. Si $A$1 serait de toujours se référer à exactement A1 .
    InformationsquelleAutor Irek Rybark
  2. 1

    Je suggère de sélectionner la Colonne K et MAISON > Styles de mise en forme Conditionnelle, la Nouvelle Règle..., Utiliser une formule pour déterminer pour quelles cellules le format et forme aux valeurs pour lesquelles cette formule est vraie::

    =AND(K1<=1638,L1=0)

    Format..., sélectionnez choix de mise en forme OK, OK.

    Lorsque la règle est indiqué dans le Gestionnaire des Règles de mise en forme Conditionnelle, il est aussi applicable à la partie supérieure gauche de la cellule de la s'Applique à la gamme. CF en effet l'intention des évaluateurs, la plage de réglage de la cellule automatiquement les références tout comme ils s'adaptent d'ailleurs (c'est à dire lors de la descente de la Colonne K, K1 défile en avant pour K2 etc, alors que K$1 voudrais rester aussi K$1).

    InformationsquelleAutor pnuts
  3. -1

    Vous pouvez faire la mise en forme conditionnelle vous avez besoin en utilisant OFFSET() et ROW() fonctions, de sorte que votre

    =AND(K6<=1638, L6=0)

    devient quelque chose comme:

    =AND(OFFSET(K6,ROW()-6,0)<=1638, OFFSET(L6,ROW()-6,0)=0)

    remplacer -6 avec la ligne de la valeur de décalage dont vous avez besoin.

    Ainsi, par exemple, si vous entrez cette formule dans une cellule dans la ligne 6, il va chercher dans K6 et L6 respectivement parce que ROW()-6 évalue à 0 – donc pas de décalage est appliqué.

    Puis dans la ligne 7 la MÊME formule se regarder dans K7 et L7 parce que ROW()-6 évalue à 1 qui signifie décalage d'une ligne au-dessous K6 et L6.

    • C'est beaucoup plus compliqué que juste de comprendre comment et quand la mise en forme conditionnelle des changements les références relatives.
    • Autant que je sache, la mise en forme conditionnelle ne tient pas compte des références relatives.
    • Oui, il est basé sur la cellule en haut à gauche étant la cellule de base. Puis de toute référence relative va changer en fonction de l'endroit où la cellule est par rapport à la cellule de base. Donc, si vous n' =$A1=100 et l'a Appliqué à B:C chaque fois que la Valeur dans la Colonne A = 100 la Colonne B Et de la colonne C de la rangée retourne true, et de mettre en évidence.
    • Je downvoted pour les raisons @ScottCraner discute. À l'aide d'OFFSET est de mauvais conseils.
    InformationsquelleAutor Taosique