{"id":10548,"date":"2022-11-27T18:36:02","date_gmt":"2022-11-27T17:36:02","guid":{"rendered":"https:\/\/www.auditsi.eu\/?p=10548"},"modified":"2022-11-27T18:36:02","modified_gmt":"2022-11-27T17:36:02","slug":"excel-fiabiliser-la-saisie-de-donnees-avec-la-validation-de-donnees","status":"publish","type":"post","link":"https:\/\/www.auditsi.eu\/?p=10548","title":{"rendered":"Excel : fiabiliser la saisie de donn\u00e9es avec la validation de donn\u00e9es"},"content":{"rendered":"<p style=\"text-align: justify;\">La <strong>validation de donn\u00e9es fiabilise la saisie des donn\u00e9es<\/strong> en les soumettant \u00e0 des <strong>crit\u00e8res pr\u00e9d\u00e9finis<\/strong>. Les saisies qui ne correspondent pas aux crit\u00e8res sont emp\u00each\u00e9es.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-10625\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Options.png\" alt=\"VALIDATION DONNEES Options\" width=\"515\" height=\"375\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Options.png 515w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Options-300x218.png 300w\" sizes=\"auto, (max-width: 515px) 100vw, 515px\" \/><\/p>\n<p style=\"text-align: justify;\">A titre d&#8217;illustration, cet article s&#8217;appuiera sur exemple. L&#8217;utilisateur sera invit\u00e9 \u00e0 saisir un montant et Excel listera tous les comptes de la balance pour lesquels le solde comptable est sup\u00e9rieur.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-10631\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Balance.png\" alt=\"VALIDATION DONNEES Balance\" width=\"459\" height=\"419\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Balance.png 459w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Balance-300x274.png 300w\" sizes=\"auto, (max-width: 459px) 100vw, 459px\" \/><\/p>\n<p style=\"text-align: justify;\">Mode op\u00e9ratoire :<\/p>\n<ul>\n<li style=\"text-align: justify;\">S\u00e9lectionner la cellule (ou plage de cellules) \u00e0 soumettre \u00e0 validation<\/li>\n<li style=\"text-align: justify;\">Menu <strong>Donn\u00e9es \/ Validation des donn\u00e9es<\/strong> :<img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-10626\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Bouton.png\" alt=\"VALIDATION DONNEES Bouton\" width=\"334\" height=\"45\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Bouton.png 334w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Bouton-300x40.png 300w\" sizes=\"auto, (max-width: 334px) 100vw, 334px\" \/><\/li>\n<\/ul>\n<p style=\"text-align: justify;\">Puis renseigner tour \u00e0 tour les diff\u00e9rents param\u00e8tres requis :<\/p>\n<p style=\"text-align: justify;\"><span style=\"text-decoration: underline;\"><em>Onglet Options<\/em><\/span> : Crit\u00e8res de validation<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-10627\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Options-Donnees.png\" alt=\"VALIDATION DONNEES Options Donn\u00e9es\" width=\"416\" height=\"337\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Options-Donnees.png 416w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Options-Donnees-300x243.png 300w\" sizes=\"auto, (max-width: 416px) 100vw, 416px\" \/><\/p>\n<ul>\n<li>Tout (par d\u00e9faut) : aucune restriction<\/li>\n<li>Nombre entier : d\u00e9finir les montants mini et maxi\u2026<\/li>\n<li>D\u00e9cimal : d\u00e9finir les montants mini et maxi\u2026<\/li>\n<li>Liste : items \u00e0 saisir (ou plage de cellules) avec \u00e9ventuellement liste d\u00e9roulante<\/li>\n<li>Date<\/li>\n<li>Heure<\/li>\n<li>Longueur du texte : \u00e0 d\u00e9finir<\/li>\n<li>Personnalis\u00e9 : selon formule de calcul \u00e0 saisir<\/li>\n<\/ul>\n<p style=\"text-align: justify;\">Dans cet exemple, l&#8217;utilisateur sera contraint de <strong>saisir un montant compris entre<\/strong> le solde le plus faible de la balance (<strong>fonction MIN<\/strong>) et le montant le plus \u00e9lev\u00e9 (<strong>fonction MAX<\/strong>).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-10632\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Options-exemple.png\" alt=\"VALIDATION DONNEES Options exemple\" width=\"417\" height=\"339\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Options-exemple.png 417w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Options-exemple-300x244.png 300w\" sizes=\"auto, (max-width: 417px) 100vw, 417px\" \/><\/p>\n<p style=\"text-align: justify;\"><span style=\"text-decoration: underline;\"><em>Onglet Message de saisie<\/em><\/span> : instruction sur les restrictions de saisie \u00e0 l\u2019attention de l\u2019utilisateur<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-10633\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Message-de-saisie-exemple.png\" alt=\"VALIDATION DONNEES Message de saisie exemple\" width=\"415\" height=\"341\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Message-de-saisie-exemple.png 415w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Message-de-saisie-exemple-300x247.png 300w\" sizes=\"auto, (max-width: 415px) 100vw, 415px\" \/><\/p>\n<p style=\"text-align: justify;\"><span style=\"text-decoration: underline;\"><em>Onglet alerte d\u2019erreur<\/em><\/span> : d\u00e9finition du message d\u2019erreur en cas de saisie erron\u00e9e<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-10634\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Alerte-derreur-exemple.png\" alt=\"VALIDATION DONNEES Alerte d'erreur exemple\" width=\"415\" height=\"336\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Alerte-derreur-exemple.png 415w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Alerte-derreur-exemple-300x243.png 300w\" sizes=\"auto, (max-width: 415px) 100vw, 415px\" \/><\/p>\n<p style=\"text-align: justify;\">Dans cet exemple, si l&#8217;utilisateur ne saisit pas un montant compris dans l&#8217;intervalle d\u00e9fini, le message suivant appara\u00eet :<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-10635\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Message-derreur-exemple.png\" alt=\"VALIDATION DONNEES Message d'erreur exemple\" width=\"534\" height=\"151\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Message-derreur-exemple.png 534w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Message-derreur-exemple-300x85.png 300w\" sizes=\"auto, (max-width: 534px) 100vw, 534px\" \/><\/p>\n<p>Dans le cas contraire, le r\u00e9sultat se met \u00e0 jour (usage de la <a href=\"https:\/\/www.auditsi.eu\/?p=10516\">fonction FILTRE<\/a>) :<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-10636\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-MAJ.png\" alt=\"VALIDATION DONNEES MAJ\" width=\"534\" height=\"324\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-MAJ.png 534w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-MAJ-300x182.png 300w\" sizes=\"auto, (max-width: 534px) 100vw, 534px\" \/><\/p>\n<p style=\"text-align: justify;\">Le fichier exemple est t\u00e9l\u00e9chargeable ci-apr\u00e8s.<\/p>\n<p style=\"text-align: justify;\">Les validation de donn\u00e9es sont <strong>inop\u00e9rantes sur les copier\/coller<\/strong>. Pour mettre en \u00e9vidence des donn\u00e9es introduites par coller\/coller qui ne correspondent pas aux crit\u00e8res, se rendre dans le menu Donn\u00e9es \/ Validation des donn\u00e9es puis cliquer sur <strong>Entourer les donn\u00e9es non valides<\/strong> :<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-10637\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Entourer.png\" alt=\"VALIDATION DONNEES Entourer\" width=\"341\" height=\"194\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Entourer.png 341w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Entourer-300x171.png 300w\" sizes=\"auto, (max-width: 341px) 100vw, 341px\" \/><\/p>\n<p style=\"text-align: justify;\">Les donn\u00e9es non valides sont entour\u00e9es :<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-10638\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Cercles-validation.png\" alt=\"VALIDATION DONNEES Cercles validation\" width=\"751\" height=\"393\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Cercles-validation.png 751w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Cercles-validation-300x157.png 300w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/VALIDATION-DONNEES-Cercles-validation-730x382.png 730w\" sizes=\"auto, (max-width: 751px) 100vw, 751px\" \/><\/p>\n<p style=\"text-align: justify;\">Pour effacer les cercles de validation : cliquer sur <strong>Effacer les cercles de validation<\/strong>.<\/p>\n<p style=\"text-align: justify;\">___<\/p>\n<p style=\"text-align: justify;\">Approfondir le sujet :&nbsp;<a href=\"https:\/\/www.auditsi.eu\/?cat=2765\">Formation Analyse de donn\u00e9es et automatisation avec Excel et Access<\/a>&nbsp;\/&nbsp;<a href=\"https:\/\/www.auditsi.eu\/?glossary=excel\">Tout savoir sur Excel<\/a>&nbsp;\/&nbsp;<a title=\"S\u00e9rie d'articles Ma\u00eetriser Excel\" href=\"https:\/\/www.auditsi.eu\/?p=4475\">D\u00e9couvrir la s\u00e9rie d\u2019articles Ma\u00eetriser Excel<\/a><\/p>\n<div id=\"sconnect-is-installed\" style=\"display: none; text-align: justify;\">2.13.0.0<\/div>\n<div id=\"sconnect-is-installed\" style=\"display: none;\">2.13.0.0<\/div>\n<div id=\"sconnect-is-installed\" style=\"display: none;\">2.13.0.0<\/div>\n<div id=\"sconnect-is-installed\" style=\"display: none;\">2.13.0.0<\/div>\n<div id=\"sconnect-is-installed\" style=\"display: none;\">2.13.0.0<\/div>\n<div id=\"sconnect-is-installed\" style=\"display: none;\">2.13.0.0<\/div>\n<div style=\"padding-bottom:20px; padding-top:10px;\" class=\"hupso-share-buttons\"><!-- Hupso Share Buttons - https:\/\/www.hupso.com\/share\/ --><a class=\"hupso_counters\" href=\"https:\/\/www.hupso.com\/share\/\"><img decoding=\"async\" src=\"https:\/\/static.hupso.com\/share\/buttons\/lang\/fr\/share-small.png\" style=\"border:0px; padding-top:2px; float:left;\" alt=\"Share Button\"\/><\/a><script type=\"text\/javascript\">var hupso_services_c=new Array(\"twitter\",\"facebook_like\",\"facebook_send\",\"email\",\"print\",\"linkedin\");var hupso_counters_lang = \"fr_FR\";var hupso_image_folder_url = \"\";var hupso_twitter_via=\"BenoitRiviere14\";var hupso_url_c=\"\";var hupso_title_c=\"Excel%20%3A%20fiabiliser%20la%20saisie%20de%20donn%C3%A9es%20avec%20la%20validation%20de%20donn%C3%A9es\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"excerpt":{"rendered":"<p>La validation de donn\u00e9es fiabilise la saisie des donn\u00e9es en les soumettant \u00e0 des crit\u00e8res pr\u00e9d\u00e9finis. Les saisies qui ne correspondent pas aux crit\u00e8res sont emp\u00each\u00e9es. A titre d&#8217;illustration, cet article s&#8217;appuiera sur exemple. L&#8217;utilisateur sera invit\u00e9 \u00e0 saisir un montant et Excel listera tous les comptes de la balance pour lesquels le solde comptable &#8230;<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?p=10548\" class=\"more-link\">Continue reading &lsquo;Excel : fiabiliser la saisie de donn\u00e9es avec la validation de donn\u00e9es&rsquo; &raquo;<\/a><\/p>\n<div style=\"padding-bottom:20px; padding-top:10px;\" class=\"hupso-share-buttons\"><!-- Hupso Share Buttons - https:\/\/www.hupso.com\/share\/ --><a class=\"hupso_counters\" href=\"https:\/\/www.hupso.com\/share\/\"><img src=\"https:\/\/static.hupso.com\/share\/buttons\/lang\/fr\/share-small.png\" style=\"border:0px; padding-top:2px; float:left;\" alt=\"Share Button\"\/><\/a><script type=\"text\/javascript\">var hupso_services_c=new Array(\"twitter\",\"facebook_like\",\"facebook_send\",\"email\",\"print\",\"linkedin\");var hupso_counters_lang = \"fr_FR\";var hupso_image_folder_url = \"\";var hupso_twitter_via=\"BenoitRiviere14\";var hupso_url_c=\"\";var hupso_title_c=\"Excel%20%3A%20fiabiliser%20la%20saisie%20de%20donn%C3%A9es%20avec%20la%20validation%20de%20donn%C3%A9es\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"ngg_post_thumbnail":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","_links_to":"","_links_to_target":""},"categories":[48,1326],"tags":[2808,2798,1751,1750,2809],"class_list":["post-10548","post","type-post","status-publish","format-standard","hentry","category-excel","category-maitriser-excel","tag-cercle-de-validation","tag-filtre","tag-max","tag-min","tag-validation-de-donnees"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_likes_enabled":false,"_links":{"self":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/10548","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=10548"}],"version-history":[{"count":7,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/10548\/revisions"}],"predecessor-version":[{"id":10643,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/10548\/revisions\/10643"}],"wp:attachment":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=10548"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=10548"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=10548"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}