Excel

Guia de validació de dades d'Excel

Excel Data Validation Guide

Fórmules de validació | Llistes desplegables dependents | Fórmules generals

Introducció

La validació de dades és una característica d'Excel que s'utilitza per controlar què pot introduir un usuari en una cel·la. Per exemple, podeu utilitzar la validació de dades per assegurar-vos que un valor sigui un número entre 1 i 6, assegurar-vos que es produeixi una data en els propers 30 dies o assegurar-vos que una entrada de text tingui menys de 25 caràcters.



La validació de dades només pot mostrar un missatge a un usuari que li indica el que es permet, tal com es mostra a continuació:

Exemple de missatge de validació de dades que es mostra quan es selecciona la cel·la





La validació de dades també pot aturar l’entrada d’usuaris no vàlids. Per exemple, si un codi de producte falla la validació, podeu mostrar un missatge com aquest:

Alerta d'error de validació de dades Exemple de codi de producte no vàlid



A més, la validació de dades es pot utilitzar per presentar a l’usuari una opció predefinida en un menú desplegable:

Exemple de menú desplegable de validació de dades

Aquesta pot ser una manera convenient de proporcionar a l'usuari exactament els valors que compleixen els requisits.

Controls de validació de dades

La validació de dades s’implementa mitjançant regles definides a la interfície d’usuari d’Excel a la pestanya Dades de la cinta.

com establir una constant a Excel

Controls de validació de dades a la pestanya de dades de la cinta

Limitació important

És important entendre que la validació de dades es pot derrotar fàcilment. Si un usuari copia dades d'una cel·la sense validació a una cel·la amb validació de dades, la validació es destrueix (o se substitueix). La validació de dades és una bona manera de fer saber als usuaris què es permet o què s’espera, però no és una manera infal·lible de garantir l’entrada.

Definició de regles de validació de dades

La validació de dades es defineix en una finestra amb 3 pestanyes: Configuració, missatge d'entrada i alerta d'error:

La finestra de validació de dades té tres pestanyes principals

La pestanya de configuració és on introduïu els criteris de validació. Hi ha una sèrie de regles de validació integrades amb diverses opcions, o podeu seleccionar Personalitzat i utilitzar la vostra pròpia fórmula per validar l'entrada tal com es mostra a continuació:

Exemple de pestanya de configuració de validació de dades

La pestanya Missatge d'entrada defineix un missatge que es mostrarà quan se selecciona una cel·la amb regles de validació. Aquest missatge d’entrada és completament opcional. Si no s'estableix cap missatge d'entrada, no apareix cap missatge quan un usuari selecciona una cel·la amb la validació de dades aplicada. El missatge d’entrada no té cap efecte sobre el que l’usuari pot introduir: simplement mostra un missatge per fer-li saber què es permet o què s’espera.

Pestanya Paràmetres de validació de dades

La pestanya Alerta d'errors controla com s'aplica la validació. Per exemple, quan l'estil s'estableix a 'Atura', les dades no vàlides activen una finestra amb un missatge i no es permet l'entrada.

Pestanya d'alerta d'error de validació de dades

L'usuari veu un missatge com aquest:

Exemple de missatge d'alerta d'error de validació de dades

Quan l'estil s'estableix en Informació o advertiment, es mostra una icona diferent amb un missatge personalitzat, però l'usuari pot ignorar-lo i introduir valors que no passin la validació. La taula següent resumeix el comportament de cada opció d'alerta d'error.

Estil d'alerta Comportament
Atura Evita que els usuaris introdueixin dades no vàlides en una cel·la. Els usuaris poden tornar-ho a provar, però han d'introduir un valor que passi la validació de dades. La finestra Atura l'alerta té dues opcions: Torna-ho a provar i Cancel·la.
Advertiment Avisa els usuaris que les dades no són vàlides. L’advertència no fa res per aturar les dades no vàlides. La finestra d'alerta d'advertència té tres opcions: Sí (per acceptar dades no vàlides), No (per editar dades no vàlides) i Cancel·lar (per eliminar les dades no vàlides).
Informació Informa els usuaris que les dades no són vàlides. Aquest missatge no fa res per aturar les dades no vàlides. La finestra d'alerta d'informació té 2 opcions: D'acord per acceptar dades no vàlides i Cancel·lar per eliminar-les.

Opcions de validació de dades

Quan es crea una regla de validació de dades, hi ha vuit opcions disponibles per validar l'entrada de l'usuari:

Qualsevol valor - no es realitza cap validació. Nota: si prèviament s’aplicava la validació de dades amb un missatge d’entrada definit, el missatge encara es mostrarà quan la cel·la estigui seleccionada, fins i tot quan se selecciona Qualsevol valor.

Nombre enter - només es permeten números sencers. Un cop seleccionada l'opció de número sencer, hi ha altres opcions disponibles per limitar encara més l'entrada. Per exemple, podeu requerir un nombre enter entre 1 i 10.

Decimal - funciona com l'opció de nombre sencer, però permet valors decimals. Per exemple, amb l'opció Decimal configurada per permetre valors entre 0 i 3, es permeten valors com ara .5, 2.5 i 3.1.

Llista - només es permeten els valors d'una llista predefinida. Els valors es presenten a l'usuari com a control de menú desplegable. Els valors permesos es poden codificar durament directament a la pestanya Configuració o especificar-los com a un interval al full de treball.

Data - només es permeten les dates. Per exemple, podeu sol·licitar una data entre l'1 de gener de 2018 i el 31 de desembre de 2021 o una data posterior a l'1 de juny de 2018.

Temps - només es permeten les vegades. Per exemple, podeu requerir una hora entre les 9:00 i les 17:00 o només permetre temps després de les 12:00.

Longitud del text - Valida l'entrada basada en el nombre de caràcters o dígits. Per exemple, podeu requerir un codi que contingui cinc dígits.

Personalitzat - Valida l'entrada de l'usuari mitjançant una fórmula personalitzada. En altres paraules, podeu escriure la vostra pròpia fórmula per validar l'entrada. Les fórmules personalitzades amplien molt les opcions de validació de dades. Per exemple, podeu utilitzar una fórmula per assegurar-vos que un valor sigui majúscula, que un valor contingui 'xyz' o que una data sigui un dia feiner durant els propers 45 dies.

La pestanya de configuració també inclou dues caselles de selecció:

Ignora el blanc - indica a Excel que no validi les cel·les que no contenen cap valor. A la pràctica, aquest paràmetre sembla que només afecta l'ordre 'encerclar dades no vàlides'. Quan s’activa, les cel·les en blanc no es circumscriuen fins i tot si fallen la validació.

Apliqueu aquests canvis a altres cel·les amb la mateixa configuració - aquest paràmetre actualitzarà la validació aplicada a altres cel·les quan coincideixi amb la validació (original) de les cel·les que s'estan editant.

convertint hores i minuts en decimals en excel

Nota: També podeu seleccionar manualment totes les cel·les amb la validació de dades aplicada mitjançant Anar a + Especial, tal com s’explica a continuació.

Menú desplegable senzill

Podeu proporcionar un menú desplegable d’opcions codificant valors al quadre de configuració o seleccionant un interval al full de treball. Per exemple, per restringir les entrades a les accions 'COMPRAR', 'HOLD' o 'VENDRE' podeu introduir aquests valors separats per comes, tal com es mostra a continuació:

Menú desplegable de validació de dades amb valors codificats

Quan s’aplica a una cel·la del full de treball, el menú desplegable funciona així:

Valors codificats en format de menú desplegable de validació de dades

Una altra manera d’aportar valors a un menú desplegable és fer servir una referència de full de càlcul. Per exemple, amb mides (és a dir, petites, mitjanes, etc.) a l'interval F3: F6, podeu subministrar aquest interval directament a la finestra de configuració de la validació de dades:

Valors del menú desplegable de validació de dades amb referència al full de càlcul

Tingueu en compte que l'interval s'introdueix com a adreça absoluta per evitar que canviï a mesura que s'aplica la validació de dades a altres cel·les.

Consell: feu clic a la icona de fletxa petita a l'extrem dret del camp d'origen per fer una selecció directament al full de càlcul, de manera que no hàgiu d'introduir l'interval manualment.

També podeu utilitzar intervals amb nom per especificar valors. Per exemple, amb l'interval anomenat 'mides' per a F3: F7, podeu introduir el nom directament a la finestra, començant per un signe igual:

Valors del menú desplegable de validació de dades amb un interval anomenat

Intervals amb nom són automàticament absolutes, de manera que no canviaran ja que la validació de dades s'aplica a diferents cel·les. Si els vostres intervals amb nom són nous, aquesta pàgina ofereix una bona visió general i diversos consells relacionats .

Tu pots també creeu llistes desplegables dependents amb una fórmula personalitzada.

Consell: si utilitzeu una taula per obtenir valors desplegables, Excel continuarà expandint o contractant la taula automàticament quan s'afegeixin o s'eliminin els valors desplegables. Dit d’una altra manera, Excel mantindrà automàticament el menú desplegable sincronitzat amb els valors de la taula a mesura que es canvien, s’afegeixen o s’eliminen. Si no coneixeu les taules d'Excel, podeu veure un fitxer demostració en aquest vídeo a les dreceres de taula.

Validació de dades amb una fórmula personalitzada

Les fórmules de validació de dades han de ser fórmules lògiques que retornin TRUE quan l’entrada és vàlida i FALSE quan l’entrada no és vàlida. Per exemple, per permetre qualsevol número com a entrada a la cel·la A1, podeu utilitzar la funció ISNUMBER en una fórmula com aquesta:

 
= ISNUMBER (A1)

Si un usuari introdueix un valor com 10 a A1, ISNUMBER retorna TRUE i la validació de dades té èxit. Si introdueixen un valor com 'poma' a A1, ISNUMBER retorna FALS i la validació de dades falla.

Per activar la validació de dades amb una fórmula, seleccioneu 'Personalitzat' a la pestanya Configuració i, a continuació, introduïu una fórmula a la barra de fórmules que comenci amb un signe igual (=) com de costum.

Fórmules de resolució de problemes

Excel ignora les fórmules de validació de dades que retornen errors. Si una fórmula no funciona i no podeu esbrinar-ne el motiu, configureu fórmules fictícies per assegurar-vos que la fórmula tingui el rendiment que espereu. Les fórmules fictícies són simplement fórmules de validació de dades introduïdes directament al full de treball perquè pugueu veure el que retornen fàcilment. La pantalla següent mostra un exemple:

Prova de validació de dades amb fórmules fictícies

Quan hàgiu aconseguit que la fórmula fictícia funcioni com vulgueu, simplement copieu-la i enganxeu-la a l'àrea de la fórmula de validació de dades.

Si aquesta idea de fórmula fictícia us confon, mireu aquest vídeo , que mostra com utilitzar fórmules fictícies per perfeccionar fórmules de format condicional. El concepte és exactament el mateix.

Exemples de fórmules de validació de dades

Les possibilitats de fórmules personalitzades de validació de dades són pràcticament il·limitades. Aquests són alguns exemples per inspirar-vos:

Per permetre només 5 valors de caràcters que comencin per 'z', podeu utilitzar:

 
= AND ( LEFT (A1)='z', LEN (A1)=5)

Aquesta fórmula només retorna TRUE quan un codi té 5 dígits i comença amb 'z'. Els dos valors encerclats retornen FALS amb aquesta fórmula.

Per permetre només una data dins dels 30 dies següents:

 
= AND (A1> TODAY (),A1<=( TODAY ()+30))

Per permetre només valors únics:

 
= COUNTIF (range,A1)<2

Per permetre només una adreça de correu electrònic

 
=ISUMBER( FIND ('@',A1)
Feu clic per obtenir més exemples de fórmules i explicacions detallades

Validació de dades per encerclar entrades no vàlides

Un cop aplicada la validació de dades, podeu demanar a Excel que encercli els valors no vàlids introduïts prèviament. A la pestanya Dades de la cinta, feu clic a Validació de dades i seleccioneu 'Cercle de dades no vàlides':

Encerclar valors no vàlids amb validació de dades: menú

Per exemple, a la pantalla següent es mostren els valors encerclats que no validen amb aquesta fórmula personalitzada:

 
= AND ( LEFT (A1)='z', LEN (A1)=5)

Valors no vàlids de validació de dades encerclats al full de càlcul

Cerqueu cel·les amb validació de dades

Per trobar cel·les amb la validació de dades aplicada, utilitzeu el diàleg Vés a> Especial. Escriviu la drecera Control + G i feu clic al botó Especial. Quan aparegui el diàleg, seleccioneu 'Validació de dades':

Ves al botó especial

com es crea una macro a Excel

Seleccioneu la validació de dades amb anar al quadre de diàleg especial

Copieu la validació de dades d'una cel·la a una altra

Per copiar la validació d'una cel·la a altres cel·les. Copieu les cel·les normalment que contenen la validació de dades que vulgueu i, a continuació, utilitzeu Enganxa especial + Validació. Un cop aparegui el diàleg, escriviu 'n' per seleccionar la validació o feu clic a validació amb el ratolí.

Utilitzeu pasta especial per copiar la validació de dades

Nota: podeu utilitzar la drecera de teclat Control + Alt + V per invocar Enganxa especial sense el ratolí.

Esborreu totes les validacions de dades

Per esborrar totes les validacions de dades d'un interval de cel·les, feu la selecció i feu clic al botó Validació de dades a la pestanya Dades de la cinta. A continuació, feu clic al botó 'Esborra-ho tot':

Utilitzeu el botó Esborra-ho tot per eliminar la validació de dadesimg / excel / 59 / excel-data-validation-guide-16.png

Per esborrar totes les validacions de dades d'un full de càlcul, seleccioneu tot el full de treball i, a continuació, seguiu els mateixos passos anteriors.

Autor Dave Bruns


^