FR/Documentation/Calc: Fonctions base de données
Liste des fonctions de 'Base de données' Calc
BDNB | BDNB compte les entrées numériques dans une colonne de table de base de données, qui sont dans les lignes qui correspondent aux critères. |
BDNBVAL | BDNBVAL compte les cellules qui ne sont pas vides dans un colonne de table de base de données, qui sont dans les lignes qui correspondent aux critères. |
BDLIRE | BDLIRE renvoie le contenu d'une cellule dans un colonne d'une table de base de données, dans la ligne unique qui correspond aux critères. |
BDMAX | BDMAX renvoie la plus grande valeur dans un colonne de table de base de données, dans les lignes qui correspondent aux critères. |
BDMIN | BDMIN renvoie la plus petite valeur dans une colonne de table de base de données, dans les lignes qui correspondent aux critères. |
BDMOYENNE | BDMOYENNE renvoie la moyenne des entrées dans une colonne de table de base de données, dans les lignes qui correspondent aux critères. |
BDPRODUIT | BDPRODUIT renvoie le produit des entrées dans une colonne de table de base de données, dans les lignes qui correspondent aux critères. |
BDECARTYPE | BDECARTYPE calcul la déviation standard des entrées dans une colonne de la table de base de données, dans les lignes qui correspondent aux critères. Les enregistrements sont traités comme un échantillon, pas comme une population entière. |
BDECARTYPEP | BDECARTYPEP calcule la déviation standard des entrées dans une colonne de la table de base de données, dans les lignes qui correspondent aux critères. Les enregistrements sont traités comme une population entière et pas comme un échantillon d'une population. |
BDSOMME | BDSOMME renvoie le total des entrées dans une colonne de table de base de données, dans les lignes qui correspondent aux critères. |
BDVAR | BDVAR renvoie la variance des entrées dans une colonne de table de base de données, dans les lignes qui correspondent aux critères. Les enregistrements sont traités comme un échantillon et non comme une population entière. |
BDVARP | BDVARP calcule la variance des entrées dans une colonne de table de base de données, dans les lignes qui correspondent aux critères. Les enregistrements sont traités comme une population entière et non comme un échantillon de population. |
Aperçu
Dans une feuille de calcul OpenOffice.org 'Calc', une 'base de données' est simplement une table de valeurs et n'a rien à voir avec une base de données complexe OpenOffice.org 'Base'. Les fonctions de 'base de données' Calc (listées ic) sont assez simples à utiliser et vous permettent de choisir et d'exécuter des données dans une table.
Une table de 'base de données' Calc ressemble à ceci :
La première ligne de la table de 'base de données' contient des en-têtes (Nom, Niveau, Âge ... ), et chaque ligne suivante contient les valeurs de données.
Les critères, qui sont utilisés pour sélectionne les lignes de la 'base de données' sont saisis dans une autre table :
La première ligne de cette table contient des en-têtes. Les lignes suivantes spécifient les critères : par exemple, en utilisant les critères de la table ci-dessus, on trouvera les enfants dont la distance du domicile par rapport à celui de l'école est supérieure à 600
Les fonctions de 'base de données' de Calc ont toutes une forme similaire. Pour un premier exemple simple utilisant les tables ci-dessus :
BDNB(A1:E10; 0; A13:E14)
- renvoie 5, le nombre d'enfants dont “Distance de l'école” est supérieur à 600.
- A1:E10 est la table de base de données A13:E14 est la table de critère.
Il y a d'autres fonctions de 'base de données' qui renvoient une somme, une moyenne une déviation standard, etc.
Critères
La table de critères peut être placée n'importe où dans la feuille de calcul, mais c'est souvent plus pratique de l'avoir à côté de la table de base de données.
Les en-têtes de la table de critères doivent correspondrent exactement aux en-têtes de la table de base de données, mais elles peuvent apparaître dans n'importe quel ordre et peuvent également appraître plus d'une fois :
Tous les critères dans une ligne doivent être satisfaits pour que la ligne soit satifaite, ainsi, dans l'exemple ci-dessous, nous trouvons les lignes qui ont :
- “Distance de l'école” supérieur à 600 ET
- “Âge” supérieur à 8 ET
- “Âge” inférieur ou égal à 10
en d'autres termes 'les enfants âgés de 9 et 10 ans qui se déplacent à plus de 600 m de l'école'.
Si la table des critères a plus d'une ligne de critères, chaque ligne satisfaite signifie que l'ensembles des critères de la table est satisfait :
Dans cet exemple, le test est :
- (“Distance de l'école” supérieure à 600 ET
- “Âge” supérieur à 8 ET
- “Âge” inférieur ou égal à 10)
- OU
- ( “Âge” inférieur ou égal à 8)
En d'autres termes, nous trouvons les lignes où les enfants ont aussi bien 9 ou 10 avec 600 ou plus de distance par rapport à l'école', ou 'âgés de 8 ans ou moins'.
La condition qui est saisi dans une cellule de la table des critères (ex. >4 ) est simplement du texte et a la forme suivante :
- comparateur valeur où
- comparateur est un de ceux-ci >, <, >=, <=, =, <> (si comparateur est omis = est considéré );
- valeur est la valeur (nombre ou texte) à comparer.
Par exemple :
- la condition “>4” teste si le contenu des cellules est supérieur à 4.
- la condition “<lampe” teste si le contenu des cellules vient alphabétiquement avant lampe (so ladre et ant correspond à la condition, mais late and zebra ne correspondent pas).
- la condition “lampe” teste si le contenu des cellue est lampe - mais Calc a un certain nombre de paramètres qui définissent le comportement exact, veuillez lire la section :
Settings for text criteria
When matching a simple text condition, (such as “<lamp”) it can be important to check the settings on the Tools menu→Options→OpenOffice.org Calc→Calculate dialog:
Case sensitive
- this check box has no effect on Calc database functions. Case is ignored - so that “lamp” will always match lamp, Lamp and LAMP, etc.
Search criteria = and <> must apply to whole cells
- if this check box is ticked, “lamp” will only match a cell containing just lamp. If this check box is not ticked, “lamp” will match lamp, clamp, lampoon, etc. - in other words it will match if lamp is found anywhere in the cell's text.
Enable regular expressions in formulas
- if this check box is ticked, then the condition is treated as a regular expression. For example the condition “l?amp” would match lamp and amp (in regular expressions “l?” means the “l” is optional).Regular expressions are similar to wildcards, but more powerful; they are described in the HowTo: Regular Expressions in Calc. (Note that the ' Search criteria = and <> must apply to whole cells ' setting above also works when regular expressions are selected.)
The functions results can depend on these settings - but unfortunately they are rather hidden away so that a user may be unaware if the settings are wrong.
There are two ways to combat this:
Firstly you can design the spreadsheet to work irrespective of the settings
- most easily, never do a text comparison - or if you do, always look for a whole cell match, and make sure that none of the cells that you check is a submatch of any other cell (eg if you search for 'apple' as the sole contents of a cell, make sure no other cell can contain 'crabapple'), AND
- do not use regular expressions AND
- do not use regular expressions special characters such as ., *, + [, { in criteria
Secondly you can include on your spreadsheet a warning if the settings are wrong.
- in cell A3 enter the text:
- Check:
- Now note that
- COUNTIF(A3;".*") returns 1 if regular expressions are turned on.
- COUNTIF(A3;"<>e") returns 1 if whole cell matching is turned on.
- and construct an appropriate formula in cell A4 - for example:
- To check for both regular expressions and whole cell matching:
- =IF( AND(COUNTIF(A3;".*"); COUNTIF(A3;"<>e")); "OK"; "Error: " & IF(COUNTIF(A3;".*")=0; "Turn on regular expressions. "; "") & IF(COUNTIF(A3;"<>e")=0; "Turn on whole cell matching."; "") )
- To check for regular expressions but no whole cell matching:
- =IF( AND(COUNTIF(A3;".*"); COUNTIF(A3;"<>e")=0); "OK"; "Error: " & IF(COUNTIF(A3;".*")=0; "Turn on regular expressions. "; "") & IF(COUNTIF(A3;"<>e"); "Turn off whole cell matching."; "") )
- To check for whole cell matching but no regular expressions:
- =IF( AND(COUNTIF(A3;".*")=0; COUNTIF(A3;"<>e")); "OK"; "Error: " & IF(COUNTIF(A3;".*"); "Turn off regular expressions. "; "") & IF(COUNTIF(A3;"<>e")=0; "Turn on whole cell matching."; "") )
- To check for neither regular expressions nor whole cell matching:
- =IF( AND(COUNTIF(A3;".*")=0; COUNTIF(A3;"<>e")=0); "OK"; "Error: " & IF(COUNTIF(A3;".*"); "Turn off regular expressions. "; "") & IF(COUNTIF(A3;"<>e"); "Turn off whole cell matching."; "") )
Tips and Tricks
- To make sure that the criteria table headings exactly match the database table headings, you can either copy and paste, or use '='. For example, in the table above =D1 will give 'Distance to School'
- If the criteria do not seem to work, make sure that any apparently empty cells really are empty by selecting them and deleting.