una - formato condicional excel ejemplos
Excel-Formato condicional-Insertar fila (16)
Aquí hay un hilo similar que puede ponerlo en el camino correcto:
¿Cómo usar el evento Worksheet_Change para el formato condicional?
Describe una solución al formato de estilo R1C1 que puede no verse afectado por las inserciones (sin probar) junto con el enfoque VBA que mencioné en los comentarios.
El uso de Offset o Indirecto en ''Se aplica a'' no parece funcionar. ¿Hay alguna otra manera de evitar que el formato condicional se rompa después de insertar la fila / s
Tengo un formato condicional para un rango de, por ejemplo, $ O $ 19: $ O $ 105. La condición es ''si el valor de celda es> 10'', se formatea con color rojo.
El problema es que cuando inserto una fila en Excel, este rango de formato se divide y obtengo 2 reglas de formato. Por ejemplo, las 2 reglas con un rango de $ O $ 19, $ O $ 21: $ O $ 105 y $ O $ 20 respectivamente, si inserto una nueva fila en la fila 20.
Normalmente, para una condición como la anterior, puede que no importe, si las reglas se dividen en varios rangos. Pero para condiciones como ''resaltar top 10'', causa resultados no deseados.
Intenté lo siguiente sin mucha suerte:
- Se intentó utilizar indirectamente, pero Excel parece resolver la fórmula y guarda la regla de formato y, por lo tanto, no funciona con las inserciones como se esperaba.
- Intentado usando offset, aquí nuevamente Excel resuelve el rango igual al anterior.
¿Alguien sabe cómo escribir un formato condicional que no se rompa con las inserciones de fila?
[EDITAR] Me di cuenta de que la fila de inserción no está causando la división de las reglas de formato condicional. También copio una fila y pego en la fila insertada que está haciendo esto. Si opto por la pasta especial y elijo solo las fórmulas, está funcionando bien.
Aunque este es un tema bastante antiguo, mis hojas de Excel también sufrían la duplicación del formato condicional al insertar una nueva fila.
Pude trabajar alrededor de él Déjame compartirlo con otros, también podría ayudar.
En mi caso, todas mis reglas de formato condicional se aplicaron a toda la tabla. Me di cuenta de que solo ciertas reglas se duplican al insertar una nueva fila. Estas reglas se basan en fórmulas que comparan valores en diferentes filas.
En mi caso, quería representar un borde horizontal cuando los valores de dos filas adyacentes difieren, por ejemplo:
=$A2 <> $A1
Si utilizo OFFSET para referirme a la fila anterior, todo es correcto, no hay reglas de formato condicional duplicadas.
=$A2<>OFFSET($A2; -1; 0)
De hecho, puse estas fórmulas de formato condicional en una columna oculta, pero el resultado debería ser el mismo.
En 2013, una vez que encuentre que sus reglas de formato se han dividido / duplicado, defina un nuevo rango de nombre para cada formato. A continuación, establezca que se aplica a = [Rango con nombre]. Excel reemplazará el rango nombrado con el rango real. Luego borre los formatos duplicados.
Estaba teniendo este problema al intentar crear informes: una vez que están terminados, no necesitan cambiarlos, pero mientras los estoy creando, sigo agregando nuevas líneas y cada nueva línea destruye el formato condicional.
De ninguna manera es una buena solución, pero fue lo mejor que pude encontrar sin recurrir a VBA, que era:
a) Haga que las reglas de formato condicional se apliquen a una columna completa o más a la vez
por ejemplo, en lugar de establecer el formato condicional en C2 y C17, coloque una columna adicional y escriba "éste" en las filas 2 y 17, y luego configure el formato para que toda la columna C sea "si la otra columna dice ''esto uno ''entonces aplique este formato "
b) Cambie los Aplica a ser $ C $ 1: $ C $ 2.
c) Hacer cambios e insertar filas y cosas
d) Luego regresa y cambia el Aplica a ser $ C: $ C
De esa manera, mientras cambias las cosas y las añades, el formato condicional no está allí, pero luego lo devuelves todo.
Si, en una fecha posterior, necesita agregar algunas filas más, primero cámbielo de $ C: $ C a $ C $ 1: $ C $ 2, luego realice los cambios y luego vuelva a colocarlo en $ C: $ C . De esa manera, no tiene que volver a escribir completamente todas las reglas de formato desde cero como lo haría si hiciera lo que he hecho anteriormente, que fue simplemente eliminarlas todas, maldecir y comenzar de nuevo;)
Obviamente, si planea insertar filas hacia arriba en la parte superior de la fila 1 o 2, eso no funcionará, pero siempre puede establecerlo en otras filas que sepa que no cambiará.
Este es un problema general con los formatos condicionales. Si inserta filas o cambia las cosas, Excel asume que desea que el formato condicional cambie con las celdas, y que desea insertar celdas con su formato original (o ninguna).
Así que hace todo lo posible para dividir el rango formateado de acuerdo con los cambios. Desafortunadamente "lo mejor" no es muy bueno. En los casos afortunados, sus reglas de formato se duplican sin que usted se dé cuenta; en casos de mala suerte, se rompen por parte o por todo el rango aplicado.
Esto es especialmente un problema si trabaja con ListObjects (también conocido como "tablas de Excel"). Inserte algunas filas, reordénelas un poco, arrastre algunos valores y la próxima vez que busque en su lista de formato condicional, tendrá docenas de cientos de reglas duplicadas. (Ejemplo: http://blog.contextures.com/archives/2012/06/21/excel-2010-conditional-formatting-nightmare/ )
En mi experiencia, la forma más rápida de solucionar el problema es eliminar todas las reglas y recrearlas (o no).
Algunas notas al margen:
- El rango de aplicación es siempre absoluto. No hay manera de evitar eso.
- Para empeorar las cosas, los formatos condicionales se tratan como fórmulas volátiles, lo que significa que se recalculan en muchas ocasiones (abrir otro archivo, desplazarse, etc.). Si no te das cuenta de las divisiones, pueden demorar considerablemente toda la aplicación después de un tiempo.
- Si elige VBA, probablemente desee usar el evento Worksheet_Calculate, al menos si sus fórmulas se refieren a otras hojas de trabajo (¡tenga en cuenta los nombres!)
Esto funcionó bastante bien para mí ...
Sub ConditionalFormattingRefresh()
''
'' ConditionalFormattingRefresh Macro
''
''Generales
Dim sh As Worksheet
Dim tbl As ListObject
Dim selectedCell As Range
Set sh = ActiveSheet
Set tbl = Range("Plan").ListObject
Set selectedCell = ActiveCell
''Rango a copiar
Dim copyRow As Range
Set copyRow = tbl.ListRows(1).Range
''Rango a restaurar
Dim startCell As Range
Dim finalCell As Range
Dim refreshRange As Range
Set startCell = tbl.DataBodyRange.Cells(2, 1)
Set finalCell = tbl.DataBodyRange.Cells(tbl.ListRows.Count, tbl.ListColumns.Count)
Set refreshRange = Range(startCell.Address, finalCell)
''Ocultar procesamiento
Application.ScreenUpdating = False
Application.EnableEvents = False
''Borrar formato corrupto
refreshRange.FormatConditions.Delete
''Copiar
copyRow.Copy
''Pegar formato
tbl.DataBodyRange.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
''Retornar a la normalidad
selectedCell.Select
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Estoy creando una solución en la que otros jugadores con niveles de habilidades de Excel mixtos entrarán en juego, por lo que necesitaba algo más fácil y más consistente que tener que recordar copiar y pegar de una determinada manera.
En Excel 2016, puede insertar una tabla de un rango seleccionado que luego le brinda la ventaja de usar referencias estructuradas (ejemplo: tblTOP [Tipo], para hacer referencia a los datos en la columna Tipo de la tabla llamada tblTOP).
Luego encontré esta respuesta en el sitio de Microsoft que muestra una forma efectiva de hacer referencia a una tabla en la parte de la fórmula de CF: formato condicional, referencias estructuradas
Entonces, con eso establecido ...
Esto es con lo que estoy trabajando:
Configuré mi formato condicional para que cuando cambie el valor de Tipo entre A y E, cambie esa fila a un color correspondiente. (Ejemplo mostrado: B convierte la fila en verde)
Esto se logró utilizando la fórmula = INDIRECTO ("tblTOP [Tipo]") = "B"
Sin embargo, cuando fui a agregar una fila, obtuve el mismo formato aplicado a esa segunda fila :(.
Se rompió el CF entre dos filas
Fórmula CF que funcionó
En pocas palabras, la siguiente fórmula es la que se me ocurrió para aplicar mi regla de FQ a esa fila en particular y no afecta a ninguna fila que se agregue o elimine:
= INDIRECTO ("tblTOP [@Type]") = "B"
La adición de la ''@'' delante de la referencia estructurada mantiene las cosas sucediendo solo para esa fila dada. Bonito.
Así que ahora puedo desplazarme o usar el menú contextual para agregar una nueva fila y espera la selección de tipo para determinar el color solo para esa fila.
Nueva fila trabajando como se esperaba
No lo he probado con celdas de pegado, ya que el propósito de esta tabla es que el usuario final ingrese datos y agregue / elimine filas según sea necesario, por lo que no puedo decir si esto funcionará con pegar una fila.
Espero que esto ayude a alguien con formato condicional en una tabla.
Estoy de acuerdo con lo que se ha publicado anteriormente; Copiar y pegar valores (o pegar fórmulas) funcionará totalmente para no dividir el formato condicional.
Soy un poco flojo para eso. Y no quiero que aquellos que usan mis hojas de cálculo tengan que hacer eso. Tampoco estoy seguro de que recuerden copiar y pegar valores. :(
No sé si esta solución funcionará para sus necesidades, pero recurrí a eliminar todo el formato condicional y volver a aplicar el formato condicional correcto cada vez que se abre el libro.
Debido a que esta macro se ejecuta cada vez que se abre el libro, el usuario no necesita cambiar la forma en que se copia y pega. No necesitan saber que la macro está incluso allí. No necesitan ejecutar manualmente la macro; es automatico Siento que esto crea una mejor experiencia de usuario.
Tenga en cuenta que este código debe copiarse y pegarse en el módulo "Este libro de trabajo"; No es un módulo regular.
Private Sub Workbook_Open()
''This will delete all conditional formatting and reapply the conditional formatting properly.
''After copying and pasting the conditional formatting get split into two or more conditional formattings. After a few
''weeks there are so many conditional formattings that Excel crashes and has to recover.
Dim ws As Worksheet, starting_ws As Worksheet
Set starting_ws = ActiveSheet ''remember which worksheet is active in the beginning
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "InvErr" Then
ws.Activate
Cells.FormatConditions.Delete
''''Every Other Row Tan
Range("A4:M203").FormatConditions.Add Type:=xlExpression, Formula1:="=ISODD(ROW(A4))"
Range("A4:M203").FormatConditions(Range("A4:M203").FormatConditions.Count).SetFirstPriority
Range("A4:M203").FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
Range("A4:M203").FormatConditions(1).Interior.ThemeColor = xlThemeColorDark2
Range("A4:M203").FormatConditions(1).Interior.TintAndShade = 0
Range("A4:M203").FormatConditions(1).StopIfTrue = False
''''Highlight Duplicates Red
Columns("B").FormatConditions.AddUniqueValues
Columns("B").FormatConditions(Columns("B").FormatConditions.Count).SetFirstPriority
Columns("B").FormatConditions(1).DupeUnique = xlDuplicate
Columns("B").FormatConditions(1).Font.Color = -16383844
Columns("B").FormatConditions(1).Font.TintAndShade = 0
Columns("B").FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
Columns("B").FormatConditions(1).Interior.Color = 13551615
Columns("B").FormatConditions(1).Interior.TintAndShade = 0
Columns("B").FormatConditions(1).StopIfTrue = False
End If
Next
starting_ws.Activate ''activate the worksheet that was originally active
Application.ScreenUpdating = True
End Sub
He encontrado un proceso simple que parece funcionar de manera consistente para insertar nuevas filas o columnas Y preserva la continuidad de las reglas de formato condicional (al menos en Office 2010), de la siguiente manera:
- Haga un simple "Insertar" de su número deseado de nuevas filas o columnas arriba, abajo o a la izquierda o derecha de una fila o columna que contenga el formato condicional a preservar.
NOTA a) Su formato condicional se aplica automáticamente a las filas o columnas insertadas sin que tenga que hacer nada más. El formato debería haber sido heredado de la fila o columna vecina. b) Cualquier formato de bordes también debería haberse copiado a las celdas recién insertadas.
Seleccione una fila, columna o rango (haciendo clic en él) que sea adyacente a los nuevos que se insertaron, y que contenga el formato condicional (y las fórmulas y los datos, si corresponde) para copiar.
Mueva el mouse sobre la esquina inferior izquierda o inferior derecha del rango seleccionado hasta que vea que aparece un signo más "+" (no lo confunda con el gadget de tamaño de la fila, ya que se ven similares).
Haga clic y mantenga presionado el botón izquierdo en "+", y arrastre las filas, columnas o rango que desee formatear, luego suelte.
NOTA: creo reglas de Formato condicional que solo hacen referencia a una celda: Ejemplo) en el campo titulado "Valores de formato donde esta fórmula es verdadera:", cree una regla como ... = AND ($ B8 = "", $ C8 = " ", $ D8 =" ", $ K8 <>" "), donde esta regla se aplica al rango ... = $ B $ 8: $ D $ 121, $ J $ 8: $ M $ 121.
Lo hice funcionar en Excel Mac 2011 siguiendo los siguientes pasos
- insertando la nueva fila
- copiando el que está arriba (con el formato condicional ya aplicado)
- resaltando la nueva fila y PEGAR ESPECIAL -> COMBINAR EL FORMATO CONDICIONAL .
Las reglas de CF permanecieron sin dividir y actualizadas para incluir una fila adicional.
Lo que debe hacer es 1) insertar una nueva fila 2) copiar la fila que desea clonar 3) pegar especial "Fusionar formato condicional"
No intuitivo y una montaña de capacitación para usuarios si desea que otros compartan los libros de trabajo que cree.
Lo que me funciona es que, cuando inserta una fila, no copie el formato de otra fila. Sólo copia-especial pega las fórmulas. El formato condicional entonces no se divide.
Me di cuenta de que la fila de inserción no está causando la división de las reglas de formato condicional. También copio una fila y pego en la fila insertada que está haciendo esto. Si opto por la pasta especial y elijo solo las fórmulas, está funcionando bien.
Sin embargo, me pregunto si alguna vez sería necesario usar ''INDIRECTO'' o ''DESPLAZAMIENTO'' en el campo ''Se aplica a'' del Formato condicional. Si es así, va a ser un problema.
Me doy cuenta de que esta es una publicación antigua, pero me encontraba con el mismo problema y desde entonces he descubierto cómo no obtener las reglas de formato condicional divididas.
En mi hoja de cálculo de Excel 2010, introduzco las fechas en la columna B. Algunas veces selecciono la entrada con la fecha, por lo que quería darles un formato condicional. Inicialmente, estaba seleccionando un rango (B2: B1960), por lo que mi fórmula en la Regla de formato condicional sería "= B2: B1960> HOY ()".
Bueno, eso funcionó bien hasta que fui a insertar nuevas filas entre las filas existentes. Las reglas se dividirían tal como lo describió el OP. Encontré varios sitios web diferentes y encontré un sitio de Microsoft Office que me indicó la respuesta. Se menciona para resaltar el rango que desea formatear, pero cambie la fórmula a "= B2> HOY ()".
Desde que cambié la fórmula, ahora puedo insertar nuevas filas entre filas existentes y no obtener las Reglas divididas como antes. Aquí está el enlace a esa página web. http://office.microsoft.com/en-us/excel-help/use-a-formula-to-apply-conditional-formatting-HA102809768.aspx
Mi solución a este problema exacto fue borrar el formato de la copia de origen. Pasos:
- Copia la fuente en el portapapeles
- Abrir nuevo documento de Excel
- Pegado especial, seleccionar fórmula (copia valores y fórmulas y omite el formato)
- Copia esto al portapapeles
- Ahora puede volver a pegar en el origen antes de usarlo o insertar filas en blanco en la hoja con el formato condicional (tenga en cuenta que solo insertando filas no cambiará el rango en las reglas de formato condicional) y pegue el contenido del portapapeles en las nuevas filas.
Esto me funcionó usando MS Excel 2016
Sé que este es un tema antiguo, pero aquí hay otra solución que es muy simple y funciona muy bien.
Simplemente inserte una nueva fila o columna como desee. Luego, seleccione y copie una fila / columna que tenga el formato condicional correcto. Pasado especial en la nueva fila / columna que acaba de crear y seleccione la opción "Todos los formatos condicionales de fusión". Las reglas de formato condicional ahora deben actualizarse automáticamente.
Happy Excel-ing =)