When you begin to create formulas in Calc, you soon realize that the wrong kind of data in a cell referenced by your formula can throw the results off. Calc does its best to interpret the data entered according to the formatting style of the cell. If you format the cell as a date type, Calc does its best to translate the input in that cell as a date. At times it is successful and other times not. Fortunately, Calc comes with validity options built in to help you design your spreadsheet in such a way as to give the user help with the input or even force them to enter the data type needed for the cell. In this article, we will look at the validity options and its capabilities. Sheet Setup Before we get to the validity options of Calc, we need to set up a spreadsheet where we will use the options. The spreadsheet is a time sheet for showing the hours worked during a week. The time sheet works well for our example because on a given day, the next time input must have a greater value than the previous entry.

Lorsque vous commencez à créer des formules dans Calc, vous réalisez vite qu'un mauvais type de données dans une cellule référencée par votre formule peut invalider les résultats. Calc fait de son mieux pour interpréter les données saisies selon le style de mise en forme de la cellule. Si vous formatez la cellule comme une date, Calc fait de son mieux pour traduire le contenu de cette cellule en date. Parfois, il réussit, parfois non. Heureusement, Calc est livré avec des options de validité intégrées pour vous aider à concevoir votre feuille de calcul de manière à donner aux utilisateurs de l'aide à la saisie, ou même les forcer à saisir le type de données nécessaire pour la cellule. Dans cet article, nous allons examiner les options de validité et leurs capacités.

Configuration de la feuille

Avant de passer aux options de validité de Calc, nous avons besoin de mettre en place une feuille de calcul où nous allons utiliser ces options. La feuille de calcul est une feuille de temps pour montrer les heures travaillées au cours d'une semaine. Cette feuille fonctionne bien pour notre exemple, car pour un jour donné, l'entrée de temps suivante doit avoir une valeur supérieure à l'entrée précédente.

We will start by creating our title and column headers. In cell A1, enter “Time Sheet”. On row 2, fill in columns A-F with the following titles: DAY, IN, OUT, IN, OUT, TOTALS. Now for the row headers. In cell A3, type in “Sunday”. Making sure that cell A3 is highlighted, click on the small square in the lower right corner of the highlighted cell. The cursor will become a crosshair. Drag down to A9. When you release mouse button, the rest of the weekday names are filled in. This happens because the days of the week are one of the default sort lists. You can see all the lists and add your own at Tools > Options > LibreOffice Calc > Sort Lists. Now let's format the cells. Select B3:E9, right-click, and select Format Cells. Click the Numbers tab. From the Category list box, select Time. If you normally use a 12-hour clock, select the “01:37 PM” option under format. However, for a 24-hour clock, select “13:37.” Click OK to save the options. Column F is the totals. We will need to select a time format, at least for the last one, that can go beyond 24 hours. Select cells F3:F10, right-click, select Format Cells. Again, on the Numbers tab, select the Time category, but for the format, select “876613:37.” This will allow for totals that are greater than 24 hours. Click OK to save the settings. If you haven't yet, you might want to save your sheet.

Nous allons commencer par créer le titre et les en-têtes de colonne. Dans la cellule A1, indiquez « Feuille de temps ». Sur la ligne 2, remplissez les colonnes A à F avec les titres suivants : JOUR, DÉBUT, FIN, DÉBUT, FIN, TOTAL. Maintenant, les en-têtes de ligne. Dans la cellule A3, indiquez « lundi ». Sélectionnez la cellule A3 et cliquez sur le petit carré dans le coin inférieur droit de la cellule en surbrillance. Le curseur devient une croix. Faites glisser vers le bas jusqu'à A9. Lorsque vous relâchez le bouton de la souris, les autres noms de jours sont remplis. Cela fonctionne car les jours de la semaine sont l'une des listes de tri par défaut. Vous pouvez voir toutes les listes et ajouter les vôtres dans Outils > Options > LibreOffice Calc > Listes de tri.

Maintenant, formatons les cellules. Sélectionnez B3:E9, faites un clic droit et sélectionnez Formater les cellules. Cliquez sur l'onglet Nombres. Dans la liste Catégorie, sélectionnez Heure. Si vous utilisez habituellement une horloge sur 12 heures, sélectionnez l'option « 01:37 PM » comme format. Pour une horloge sur 24 heures, sélectionnez « 13:37 ». Cliquez sur OK pour enregistrer les options. La colonne F contient les totaux. Nous aurons besoin de choisir un format d'heure, au moins pour la dernière, qui peut aller au-delà de 24 heures. Sélectionnez les cellules F3:F10, faites un clic droit, sélectionnez Formater les cellules. À nouveau, sur l'onglet Nombres, sélectionnez la catégorie Heure, mais pour le format, sélectionnez « 876613:37 ». Cela permettra d'avoir des totaux dépassant 24 heures. Cliquez sur OK pour enregistrer les paramètres.

Si vous ne l'avez pas encore fait, vous pouvez enregistrer votre feuille de calcul.

Setup Validation We will need to check the validity of the time inputs. We will want them in a Calc-acceptable time format. This is the only requirement for valid data in the B column. For the other columns (C-E), we will need to also make sure the input is greater than the input before it. Select cell B3, then Data > Validity. The Validity dialog will appear. The Criteria tab (above) is where we control what constitutes valid data. The Allow drop-down list defines the type of data that is acceptable. The default is All Values. The other types are Whole Numbers, Decimal, Date, Time, Cell Range, List, and Text Length. The choices for the different types are basically the same except for Cell Range and List. With the cell Range, you define a cell range and the cell must contain a value that is in one of those cells. The defined range must consist of only one column or one row. With the List option, you define a list of acceptable values. With both the List and Cell Range options, you get a drop-down list box with the acceptable values. For our purposes, we need Allow set to Time.

Validation de la configuration

Nous aurons besoin de vérifier la validité des saisies d'heures. Nous les voulons dans un format acceptable pour Calc. C'est la seule exigence pour des données valides dans la colonne B. Pour les autres colonnes (C à E), nous aurons également besoin de vérifier que la saisie est supérieure à la saisie précédente.

Sélectionnez la cellule B3, puis Données > Validité. La boîte de dialogue de validité apparaît.

L'onglet Critères (ci-dessus) est l'endroit où nous contrôlons ce qui constitue des données valides. La liste déroulante Autoriser définit le type de données qui est acceptable. La valeur par défaut est « Toutes les valeurs ». Les autres types sont Nombres entiers, Décimal, Date, Heure, Plage de cellules, Liste et Longueur du texte. Les choix pour les différents types sont fondamentalement les mêmes, sauf pour Plage de cellules et Liste. Pour Plage de cellules, vous définissez une plage de cellules et la cellule doit contenir une valeur qui est dans une de ces cellules. La plage définie doit être composée d'une seule colonne ou une ligne. Avec l'option Liste, vous définissez une liste de valeurs acceptables. Avec les options Liste et Plage de cellules, vous obtenez une zone de liste déroulante avec les valeurs acceptables. Pour nos besoins, nous devons régler Autoriser sur Heure.

Check the box for “Allow empty cells” to allow cells to remain empty. This will allow us to start with a blank time sheet. The Data drop-down list allows you to select the comparative operator to use for this validation. The choices are equal, less than, greater than, less than or equal to, greater than or equal to, not equal, valid range, and invalid range. Each one asks for a minimum, maximum, or value to compare, except for the two range choices. The range choices require a minimum and maximum. For our purpose, we need the greater than operator. In the minimum text box, enter 12:00 AM or 00:00. Click on the Input Help tab (below). The settings on this tab are optional. The idea is to provide the user with information about what to enter into the cell as a tip box that pops up when the cell is selected. To activate, click the checkbox for “Show input help when cell is selected.” Create a title for the tip box by typing it in the Title text box. For us, we could use the title “Start Time”. The Input Help text box is where you put the actual help tip like “Enter the start time: ex. 1:00 PM or 13:00”.

Cochez la case « Autoriser les cellules vides » pour permettre aux cellules de rester vides. Cela nous permettra de commencer avec une feuille de temps vide.

La liste déroulante Données vous permet de sélectionner l'opérateur de comparaison à utiliser pour cette validation. Les choix sont égal à, inférieur à, supérieur à, inférieur ou égal à, supérieur ou égal à, différent, plage correcte et plage incorrecte. Chacun demande un minimum, un maximum, ou une valeur à comparer, à l'exception des deux choix de plages. Les choix de plages nécessitent un minimum et un maximum. Pour nos fins, il nous faut l'opérateur supérieur à. Dans la zone de texte minimum, saisissez 12:00 AM ou 00:00.

Cliquez sur l'onglet Aide à la saisie (ci-dessous). Les paramètres de cet onglet sont facultatifs. L'idée est de fournir à l'utilisateur des informations sur ce qu'il faut saisir dans la cellule sous forme de bulle qui apparaît lorsque la cellule est sélectionnée. Pour l'activer, cliquez sur la case à cocher « Afficher l'aide à la saisie lors de la sélection d'une cellule ». Créez un titre pour la bulle d'aide en le plaçant dans la zone de texte Titre. Dans notre cas, nous pourrions utiliser le titre « Heure de début ». Dans la boîte de texte Aide à la saisie, vous mettez l'aide proprement dite, comme « Saisissez l'heure de début : ex. 1:00 PM or 13:00 ».

Switch to the Error Alert tab (above). Here we can set the validation to display a message when invalid data is entered. To activate the setting, check the checkbox for “Show error message when invalid values are entered”. There are four choices for the Action drop-down list, Stop, Information, Warning, and Macro. Stop, the one we will use, prompts with a dialog and rejects the input when invalid data is entered. For Information and Warning, they prompt with a dialog containing OK and Cancel buttons when invalid data is entered. If the user clicks OK, the input is left as it is. If the user clicks Cancel, the input is rejected. Macro will allow you to select a macro to run when invalid data is entered. The Title and Error Message is the title and message for the dialog box. We could set them to “Invalid Time” and “Enter in time format: 13:00 or 1:00 PM”. Once you have everything set, click OK to save the Validity dialog settings.

Passez à l'onglet Message d'erreur (ci-dessus). Ici, nous pouvons définir la validation pour afficher un message lorsque des données incorrectes sont saisies. Pour activer la fonction, cochez la case « Afficher un message d'erreur en cas de saisie de valeurs incorrectes ». Il y a quatre choix pour la liste déroulante Action, Arrêter, Information, Avertissement et Macro. Arrêter, celle que nous allons utiliser, affiche une boîte de dialogue et rejette l'entrée lorsque des données incorrectes sont saisies. Pour Information et Avertissement, ils affichent une boîte de dialogue avec les boutons OK et Annuler lorsque des données incorrectes sont saisies. Si l'utilisateur clique sur OK, la saisie est conservée. Si l'utilisateur clique sur Annuler, la saisie est rejetée. Macro vous permet de sélectionner une macro à exécuter lorsque des données incorrectes sont saisies. Titre et Message d'erreur sont le titre et le message de la boîte de dialogue. Nous pourrions les régler à « Heure incorrecte » et « Saisissez avec le format d'heure : 13:00 or 1:00 PM ».

Une fois que vous avez tout rempli, cliquez sur OK pour enregistrer les paramètres du dialogue de validité.

We will copy cell B3 to cells B4:B9. Right-click cell B3 and select Copy. Select cells B4:B9, right-click, and select Paste. This will copy the validity settings to the cells. The whole column will have the same requirements for input. For columns C, D, and E, we will do almost the same thing. We will go through the steps on C, and you can repeat for D and E. Select cell C3, Data > Validity. On the Criteria tab of the Validity dialog, select Time from the Allow drop-down list. Check to allow empty cells. Set Data to greater than, and set the minimum value to B3. Notice that we use a relative cell reference here. This will allow us to copy it to the other cells and have the reference translate. These criteria tell Calc that the cell requires a value greater than the value in cell B3. Do what you want with the help and error tabs. I do recommend using at least Information or Warning messages for invalid data. The messages could mention needing a greater value (or a later time). Right-click cell C3 and copy the cell, then paste it into cells C4:C9. You can repeat for columns D and E. You can also just copy C3 to D3:D9 and E3:E9, but you will need to adjust the text in the dialogs for help and error.

Nous allons recopier la cellule B3 dans les cellules B4:B9. Un clic droit sur la cellule B3 et sélectionnez Copier. Sélectionnez les cellules B4:B9, faites un clic droit et sélectionnez Coller. Cela va copier les paramètres de validité dans les cellules. La colonne entière aura les mêmes exigences pour la saisie.

Pour les colonnes C, D et E, nous allons faire presque la même chose. Nous allons dérouler les étapes pour C, et vous pourrez les répéter pour D et E. Sélectionnez la cellule C3, Données > Validité. Dans l'onglet Critères de la boîte de dialogue de validité, sélectionnez Heure dans la liste déroulante Autoriser. Cochez pour autoriser les cellules vides. Réglez Données sur « supérieur à », et définissez la valeur minimale à B3. Remarquez que nous utilisons ici une référence de cellule relative. Cela nous permettra de la copier sur les autres cellules et la référence sera translatée. Ces critères indiquent à Calc que la cellule nécessite une valeur supérieure à la valeur dans la cellule B3. Faites ce que vous voulez avec les onglets Aide et Erreur. Je recommande vraiment d'utiliser au moins des message d'information ou d'avertissement pour les données incorrectes. Les messages pourraient mentionner qu'on a besoin d'une valeur plus grande (ou postérieure). Faites un clic droit sur la cellule C3 et copiez la cellule, puis collez-la dans les cellules C4:C9. Vous pouvez répéter pour les colonnes D et E. Vous pouvez aussi recopier C3 sur D3:D9 et E3:E9, mais vous aurez besoin d'ajuster le texte dans les boîtes de dialogue d'aide et d'erreur.

Total Formulas Now that we have our validation set up, we need to create the formulas to give us our totals. We will start with the daily totals. Select cell F3. Enter the formula =(C3-B3)+(E3-D3) which will give us the total for Sunday's times. Right-click F3 and Copy. Select cells F4:F9, right-click, and Paste. For the weekly total in F10, we will just sum the daily totals. Here is the formula: =SUM(F3:F9)

Formules pour les totaux

Maintenant que notre validation est en place, nous devons créer les formules pour obtenir les totaux. Nous allons commencer avec les totaux quotidiens. Sélectionnez la cellule F3. Saisissez la formule :

=(C3-B3)+(E3-D3)

ce qui nous donnera le total des heures du lundi. Faites un clic droit sur F3 et Copier. Sélectionnez les cellules F4:F9, clic droit, et Coller. Pour le total hebdomadaire en F10, nous allons simplement additionner les totaux quotidiens. Voici la formule :

=SOMME(F3:F9)

Protect Cells Once you have a sheet set up the way you like, you may want to protect the parts of the sheet you don't want changed like the labels and formulas. First, you need to tell Calc whether the cell is protected or not. Then you add protection to the sheet or document once everything is finished. In the case of our time sheet, we need to protect everything except for the cells where the user enters their times, B3:E9. By default, the Protect option is turned on for all cells. To change the protection setting for the input cells, select B3:B9, Format > Cells, and the Format Cells dialog appears. Click on the Cell Protection tab and uncheck Protected.

Protection des cellules

Une fois que vous avez mis en place une feuille comme vous le souhaitez, vous pouvez protéger les parties de la feuille que vous voulez rendre non modifiables, comme les étiquettes et les formules. Tout d'abord, vous devez dire à Calc si la cellule est protégée ou non. Ensuite, vous ajoutez la protection de la feuille ou du document une fois que tout est terminé. Dans le cas de notre feuille de temps, nous devons protéger tout, sauf pour les cellules où l'utilisateur entre ses heures, B3:E9.

Par défaut, l'option Protéger est activée pour toutes les cellules. Pour changer le réglage de protection pour les cellules de saisie, sélectionnez B3:B9, Format > Cellules, et le dialogue de formatage des cellules apparaît. Cliquez sur l'onglet Protection de cellule et décochez Protégé.

At this point, you can still edit any cell because the sheet or document is not protected. To turn on protection for the sheet or document, Tools > Protect Document > Sheet (or Document). The Protect Sheet or Protect Document dialog will appear depending on which option you selected. Enter and confirm a password in the dialog. The options will allow you to check whether the user can select protected or unprotected cells. If you do not enter a password, then no password is required to remove protection from the sheet or document. To remove protection from a sheet or document, Tools > Protect Document > Sheet (or Document). If a password was used when protection was set, enter the password used to protect the sheet or document.

À ce stade, vous pouvez toujours modifier n'importe quelle cellule, car la feuille et le document ne sont pas protégés. Pour activer la protection de la feuille ou du document, Outils > Protéger le document > Feuille (ou Document). Le dialogue Protéger la feuille ou Protéger le document apparaîtra en fonction de l'option sélectionnée. Saisissez et confirmez le mot de passe dans la boîte de dialogue. Les options vous permettront de vérifier si l'utilisateur peut sélectionner des cellules protégées ou non protégées. Si vous ne saisissez pas de mot de passe, aucun mot de passe n'est nécessaire pour supprimer la protection de la feuille ou du document.

Pour supprimer la protection d'une feuille ou d'un document, Outils > Protéger le document > Feuille (ou Document). Si un mot de passe a été utilisé lorsque la protection a été mise, saisissez le mot de passe utilisé pour protéger la feuille ou le document.

Mark Invalid Data with Detective If you selected Warning or Information from the Action drop-down list in the Validity dialog, a user could enter invalid data and click OK to keep the invalid input. This may generate strange results in your totals or an error. Tools > Detective > Mark Invalid Data will mark the invalid data. Once the user corrects the data, Tools > Detective > Mark Invalid Data should clear the marks, unless the data is still invalid. Notice from my sample that invalid data can cause other cells data to show as invalid. The Calc Validity options are a great way to set up sheets for use by other people, or to keep you from entering invalid data on a complicated sheet. You can set it up to reject the invalid data outright, or for the user to make a choice about keeping the input. Once you have the sheet set the way you want it, you can protect the sheet from unwanted changes. This is a good way to protect formulas and labels. Finally, the Detective tool allows the user to mark invalid data they entered.

Marquer les données incorrectes avec Audit

Si vous avez sélectionné Avertissement ou Information dans la liste déroulante d'action dans le dialogue de validité, un utilisateur peut entrer des données incorrectes et cliquer sur OK pour conserver la saisie incorrecte. Cela peut générer des résultats étranges dans vos totaux ou une erreur. Outils > Audit > Marquer les données incorrectes marquera les données incorrectes. Une fois que l'utilisateur corrige les données, Outils > Audit > Marquer les données incorrectes devrait effacer les marques, à moins que les données soient toujours incorrectes. Remarquez sur mon exemple que des données non valides peuvent entraîner que d'autres cellules soient marquées comme incorrectes.

Les options de validité de Calc sont un excellent moyen de mettre en place des feuilles pour une utilisation par d'autres personnes, ou pour vous empêcher d'entrer des données incorrectes sur une feuille compliquée. Vous pouvez les configurer pour rejeter les données invalides purement et simplement, ou pour que l'utilisateur puisse faire le choix de garder la saisie. Une fois que vous avez préparé la feuille comme vous le souhaitez, vous pouvez protéger la feuille contre les modifications indésirables. C'est une bonne façon de protéger les formules et les étiquettes. Enfin, l'outil Audit permet à l'utilisateur de marquer des données incorrectes qui ont été saisies.