Nivel 8 · Desafío final: reproduce el registro de entrada de pedidos con las reglas nuevas

El registro y las reglas

La hoja contiene 36 filas de entrada de pedidos de la hoja de correcciones de soporte, tal como se escribieron: id de entrada, pedido, canal, producto, cantidad y precio unitario. El formulario nuevo de TI aplicará dos reglas, y Jennifer quiere saber qué le habrían hecho a este lote antes de activarlas.

Las entradas están a la derecha: los canales permitidos en K2:K4 y el umbral de “línea grande” en L6. Nada de los datos se ha corregido. Estás encontrando los problemas, no escondiéndolos.

Paso 1: una columna por regla

En Excel, la regla de la cantidad es Datos → Validación de datos → Permitir: Número entero, Datos: mayor que, Mínimo: 0. Escrita como una regla Personalizada que también puedes ejecutar sobre filas antiguas:

G2   =AND(ISNUMBER(E2),E2=INT(E2),E2>0)

Cada parte atrapa una entrada distinta. ISNUMBER hace fallar el "2" de E10, pegado como texto. E2=INT(E2) hace fallar el 1.5 de E21: INT(1.5) es 1. E2>0 hace fallar el 0 de E35 y el -4 de E26. Omite cualquiera de ellas y esa fila se cuela.

La regla del canal es Permitir: Lista, Origen: =$K$2:$K$4, que da a la celda un desplegable. Su forma de fórmula cuenta la entrada en la lista:

H2   =COUNTIF($K$2:$K$4,C2)>0

phone en C18 y website en C26 no están en la lista. C29 parece web pero es "web " con un espacio al final, así que tampoco coincide. En el formulario real, el desplegable detiene los tres. Ten en cuenta que tanto la regla de Lista como COUNTIF ignoran las mayúsculas: Web pasaría. Si las mayúsculas importan, usa SUMPRODUCT(--EXACT($K$2:$K$4,C2))>0.

Paso 2: un motivo por fila

I2   =IFS(NOT(G2),"bad quantity",NOT(H2),"bad channel",TRUE,"ok")
L8   =COUNTIF(I2:I37,"<>ok")

IFS devuelve el primer motivo que se aplica, como un formulario que se detiene en el primer campo que falla. La fila 26 falla ambas reglas y reporta bad quantity. L8 cuenta 6 entradas rechazadas: filas 10, 21, 26 y 35 por la cantidad, 18 y 29 por el canal.

De aquí también salen los mensajes de error. Para cada regla, escribe lo que debe hacer la persona que teclea, y ponlo en la pestaña Mensaje de error de la regla (Estilo: Detener): “La cantidad debe ser un número entero mayor que 0” y “Elige un canal de la lista”. Un Mensaje de entrada (“Unidades enteras, p. ej. 2”) evita la mayoría de estos antes de que ocurran.

Paso 3: la regla de resaltado

Las líneas de más de $500 que pasan reciben una segunda mirada. En Excel, selecciona A2:F37 y luego Formato condicional → Nueva regla → Usar una fórmula:

J2   =AND($I2="ok",$E2*$F2>$L$6)

Las columnas están fijadas para que cada celda de una fila lea el estado, la cantidad y el precio de esa fila; las filas están libres para que cada fila lea la suya. Se iluminan tres líneas: la fila 4 (4 × 149.00 = 596.00), la fila 15 (2 × 349.00 = 698.00) y la fila 34 (999.00). La fila 10 sería una cuarta sin la prueba del estado: el texto "2" por 349.00 es 698.00, porque la aritmética convierte el texto numérico.

Pensar como un analista

Antes de activar la validación, una reproducción como esta responde tres preguntas:

• ¿Son correctas las reglas? Si la mitad del lote falló por un valor legítimo (digamos, una cantidad de 0 en una corrección de precio pura), la regla necesita cambiar, no las personas.
• ¿Qué está ya roto? La validación solo comprueba las entradas nuevas; las seis filas de aquí se quedan como están salvo que alguien las corrija. Rodear con un círculo datos no válidos las muestra en Excel.
• ¿Qué la esquiva? Pegar sustituye la validación, así que una hoja en la que se pega necesita sus reglas respaldadas por una columna de comprobación como estas, o una hoja protegida.

Errores comunes

• E2>0 solo como regla de la cantidad: "2" y 1.5 pasan ambos.
• Una lista sin fijar: COUNTIF(K2:K4,C2) se desliza hasta K3:K5 y empieza a rechazar web.
• Probar el canal primero en IFS, lo que cambia el motivo de la fila 26.
• Una regla de resaltado sin $ antes de las columnas: en Excel cada celda de la fila probaría una columna distinta, y la fila se ilumina a parches.