6Microsoft Office Excel 2010El formato condicional para los grados v objetivos

Asi que, en mi otra vida en realidad soy un profesor de ciencias, y ya que por lo general se pone redonda que soy “bueno con las computadoras” Por lo general preguntan a echar un vistazo a algunas cosas. Dos veces en los últimos meses me han preguntado si me gustaría configurar el formato condicional en Excel para colorear resultados de las pruebas de los estudiantes de código en comparación con sus grados de destino. Aunque no es exactamente relacionado con el contenido básico de este sitio, es todavía DIY ayuda técnica por lo que decidió publicar un conjunto completo de instrucciones aquí. Disfrutar…

Microsoft Excel Conditional Formatting: Lookup TablesLo primero que necesita una hoja de búsqueda

  • Crear una nueva hoja del libro.
  • Llenar 2 - columnas de la izquierda con los grados (1c, 1si, 1una, 2c etc.) y el derecho con un valor de "puntuación" (puede ser sólo 1, 2, 3, 4, 5 etc.)
  • Llenar 2 columnas adicionales con una “búsqueda inversa”, que es la misma que antes, pero con la 2 columnas invertidas
  • Resaltar la tabla izquierda completa que ha creado, haga clic derecho sobre el mismo, y seleccione "definir el nombre". Dale un nombre, e.g. “Búsqueda”
  • Resalte la segunda tabla que ha creado, haga clic derecho y definir otro nombre. Dale un nombre, e.g. "Reverse_lookup"

Microsoft Excel Conditional Formatting: Define NameLa creación de las hojas de trabajo

  • Ahora crear una hoja de trabajo para cada clase, grupo o año (como trajes).  Incluir una columna para los grados de destino y varias columnas de resultados de la prueba o evaluación (e.g. como por debajo)
  • Obsérvese en este ejemplo que se dirigen a los datos está en C3 - C5. Los resultados están en D3 - F5

Microsoft Excel Conditional Formatting: Example Markbook

Construyendo el formato condicional básica

  • Seleccione la celda D3 - la parte superior izquierda de las células resultados de los datos. Asegúrese de que está en la pestaña “Inicio” y haga clic en “formato condicional” y seleccione “nueva norma”
  • Seleccionar “utilizar una fórmula para determinar qué células a formato”
  • Lo que queremos hacer es comparar la celda actual al objetivo, pero no es tan simple como eso, debido a la forma en que Excel compara los valores - por lo que tenemos que utilizar las tablas de búsqueda.
  • También necesitamos 5 reglas - 2 subniveles o más por debajo, 1 abajo, en el blanco, 1 arriba y 2 o más por encima. Puede crear más reglas si quieres más detalle o menos si lo que desea a continuación, en y por encima del objetivo.
  • La fórmula para eventual 2 subniveles o más abajo se =VLOOKUP($C3,Lookup,2,FALSE)-1>VLOOKUP(D3,Lookup,2,FALSE) 

    Microsoft Excel Conditional Formatting: New Rule Formula

  • El C3 en la primera parte se refiere a la célula diana superior, y la D3 se refiere a la célula resultado real superior. La “búsqueda” se refiere a la gama de tabla de búsqueda que hemos llamado anteriormente.
  • Descomponerlo - estamos mirando hacia arriba valores numéricos, tanto para el objetivo y el resultado real de la celda actual. Porque queremos 2 subniveles o más por debajo tomamos 1 lejos del objetivo y dicen que todavía debe ser mayor que la puntuación. los $ antes de la C de C3 asegura que cuando usamos la misma fórmula para otras columnas que todavía serán comparados a la columna de objetivos.
  • Ahora haga clic en formato y aplicar el formato que desee. Yo uso un color de fondo de la luz roja
  • Ahora hemos creado 1 de 5 reglas para una sola célula.

Microsoft Excel Conditional Formatting: Format CellsLa ampliación del formato condicional para múltiples normas

  • A continuación, repita el proceso para crear 4 más reglas.
  • Cuando todos hayan terminado mi 5 ejemplos mirarían como a continuación. Sólo la comparación y la posible adición o cambio resta entre ellos. También tenga en cuenta el orden (el cual llegamos a la siguiente)
= BUSCAR($C3, búsqueda,2,FALSO)-1>BUSCAR(D3, búsqueda,2,FALSO)
= BUSCAR($C3, búsqueda,2,FALSO)>BUSCAR(D3, búsqueda,2,FALSO)
= BUSCAR($C3, búsqueda,2,FALSO)= BUSCAR(D3, búsqueda,2,FALSO)
= BUSCAR($C3, búsqueda,2,FALSO)+1<BUSCAR(D3, búsqueda,2,FALSO)
= BUSCAR($C3, búsqueda,2,FALSO)<BUSCAR(D3, búsqueda,2,FALSO)
  • Yo uso roja mediana, luz roja, amarillo, verde medio y de color verde claro (en ese orden) de colores de fondo

Clasificación de la orden de las reglas

  • Finalmente, tenemos que asegurarnos de que las reglas están en el orden correcto, y aplicarlos a todas las células resultados, no sólo la celda de resultado único D3.
  • Haga clic en el botón de formato condicional de nuevo y elija “gestionar las reglas ...”
  • Asegúrese de que las reglas están en el orden correcto - que el anterior (rojo mediano, luz roja, amarillo, verde medio, verde claro)Microsoft Excel Conditional Formatting: Rules Manager
  • Haga clic en la sección “Se aplica a” casilla e introduzca toda la gama de células que contendrán los resultados. En mi ejemplo, esto es =$D$3:$F$5.  Pon esto en todo 5 "Se aplica a" cajas
  • Esta hoja debería funcionar plenamente con formato condicional

La expansión fácilmente a hojas adicionales

  • Para aplicar a otras hojas de copia simple y pegar una sola célula que ya ha aplicado formato condicional a ella, en la nueva hoja de.
  • A continuación, haga clic en el botón de formato condicional, seleccione Administrar reglas, y cambiar el 5 “Se aplica a” cajas para que apunte a las células de la nueva hoja que necesitan formato condicional

Qué piensas? envíanos un comentario más abajo! Si desea suscribirse por favor utilice el enlace de suscripción en el menú en la parte superior derecha. También puede compartir esto con tus amigos mediante el uso de los enlaces sociales inferiores. Aclamaciones.

Deja una respuesta

6 comentarios

gravatarNikki

Me encantaría volver a publicar este artículo en mi propio sitio web, habría que estar bien?

Respuesta
gravatarGary Zawadzki

Hola,
Gran tutorial - gracias. Tengo 3 diferentes colores funcionando perfectamente, pero el 2 arriba y 2 a continuación no son el formato a sus colores correctos. Están cambiando a rojo y verde, pero no las sombras que necesitan para cambiar a. ¿Alguna sugerencia por favor?

Gracias,

Gary

Respuesta
gravatarJon Scaife

Lo siento por el largo retraso en la respuesta Gary - He estado ocupado migrar el sitio para nuestro nuevo VPS. ¿Has conseguido que resolvieran esto? Si usted no entonces por todos los medios me zap por encima de su archivo de Excel a echar un vistazo a. Voy vuelva a revisar esta guía en los próximos días 2 para asegurarse de que no son engañosas o partes de cualquier error tipográfico, se ha sabido que suceda 😉

Respuesta
gravatarEmma

He seguido todas sus instrucciones anteriores, pero ninguno de mis células se han ido de color?? ¿Alguna sugerencia dónde voy mal??

Respuesta
gravatarJon Scaife

Supongo que los has rellenado con los datos, y la columna de destino también se rellena con datos?
Por todos los medios me zap el archivo una y voy a echar un vistazo rápido - Te he enviado por correo electrónico

Respuesta