Fonctions type base de données PDF

Preview

Summary

cours informatique...

Description

Fonctions type base de données Table des matières 1.Introduction......................................................................................................................................1 2.La fonction BDNB...........................................................................................................................2 3.La fonction BDNBVAL....................................................................................................................3 4.Les fonctions BDSOMME, BDPRODUIT et BDMOYENNE........................................................4 5.Les fonctions BDMIN et BDMAX..................................................................................................5 6.La fonction BDLIRE........................................................................................................................6 7.Les jokers..........................................................................................................................................7

1.Introduction OpenOffice Calc offre un ensemble de fonctions « type base de données ». Ces fonctions servent à faire des requêtes / calculs (comptage, somme, moyenne…) basées sur le contenu d’un tableau, en y posant des critères qui permettent de sélectionner uniquement une partie des lignes du tableau. Attention : les tableurs ne sont aucunement les outils adaptés pour stocker et gérer les bases de données ! Pour de la vraie gestion de bases de données et pour des requêtes puissantes et complexes il faut utiliser un SGBD (Système de Gestion de Bases de Données). Un SGBD très simple est disponible dans la suite OpenOffice ; il s’appelle OpenOffice Base. Les fonctions type base de données offertes par Calc utilisent une matrice de critères très similaire à la matrice de filtre utilisée avec les filtres spéciaux : la même ligne veut dire des conditions reliées par des ET logiques, des lignes différentes sont reliées par des OU logiques, et il faut dupliquer les colonnes de la matrice si on a besoin de poser plusieurs conditions valables en même temps sur une colonne de la table (par exemple, age compris entre 30 et 40…). IMPORTANT : pour ne pas avoir de mauvaises surprises, assurez-vous qu’en allant dans Outils → Options → OpenOffice Calc → Calculer, les options cochées / décochées sont les suivantes :

À retenir aussi : nous pouvons souvent obtenir les mêmes résultats si, plutôt que de faire appel aux fonctions de type bases de données, nous utilisons d’abord un filtre et ensuite les versions « normales » des fonctions (NB plutot que BDNB, SOMME plutôt que BDSOMME…). De plus, si la matrice de critères comprend une seule ligne, nous pouvons souvent utiliser SOMME.SI.ENS et NB.SI.ENS. Enfin : souvent les tableaux croisés dynamiques marchent aussi !

1

2.La fonction BDNB La fonction BDNB compte le nombre de cellules non vides contenant des nombres dans une colonne de la table, pour les lignes qui correspondent à des critères spécifiés par la matrice de critères. Attention : cette fonction va ignorer les cellules vides mais aussi les cellules dont le contenu n’est pas numérique (c’est-à-dire, les cellules contenant du texte). Pour inclure les cellules à contenu de type texte et donc ignorer simplement les cellules vides, il faut utiliser BDNBVAL (voir ci-dessous).

a)

Syntaxe :

BDNB(tableau_base_de_données; indice-colonne; matrice_critères) • • •

b)

tableau_base_de_données est le tableau sur lequel on applique notre fonction indice-colonne est l’indice de la colonne sur laquelle on compte les valeurs matrice_critères est la matrice contenant les critères.

Exemples :

•

BDNB(A1:F8;3;A11:A12) renvoie 2. Cela correspond aux cellules numériques situées dans la colonne 3 dans les lignes 2 et 7 ; la ligne 8 correspond aux critères de ma matrice (nombre d’unités supérieur à 10) mais la cellule sur la colonne 3 (CodeBoutique) est vide, donc pas comptée.

2

•

BDNB(A1:F8;6;A11:A12) renvoie 3. En effet, les trois lignes qui correspondent aux critères contiennent des cellules numériques non vides sur la colonne 6 (PrixTotal).

•

BDNB(A1:F8;4;A11:A12) renvoie 0. En effet, sur les trois lignes qui correspondent aux critères, deux contiennent sur la colonne 4 (CodeProduit) des cellules avec un contenu de type texte, donc pas numérique, et la troisième (ligne 8) contient une cellule vide.

3.La fonction BDNBVAL La fonction BDNBVAL compte le nombre de cellules non vides dans une colonne de la table, pour les lignes qui correspondent à des critères spécifiés par la matrice de critères. Attention : cette fonction va ignorer les cellules vides mais prendre en compte toutes les autres cellules, que leur contenu soit texte ou numérique!. Pour inclure uniquement les cellules à contenu numérique, il faut utiliser BDNB (voir ci-dessus).

a)

Syntaxe :

BDNBVAL(tableau_base_de_données; indice-colonne; matrice_critères) • • •

b)

3

tableau_base_de_données est le tableau sur lequel on applique notre fonction indice-colonne est l’indice de la colonne sur laquelle on compte les valeurs matrice_critères est la matrice contenant les critères.

Exemples :

•

BDNBVAL(A1:F8;3;A11:A12) renvoie 3. En effet, les trois lignes correspondant aux critères (nombre d’unités supérieur à 10), c’est-à-dire les lignes 2, 7 et 8, contiennent des cellules non-vides pour la colonne 3 (CodeBoutique). Remarquez bien le contenu numérique pour les lignes 2 et 7, respectivement texte pour la ligne 8.

•

BDNBVAL(A1:F8;4;A11:A12) renvoie 2. En effet, sur les trois lignes qui correspondent aux critères, deux contiennent sur la colonne 4 (CodeProduit) des cellules avec un contenu de type texte, et la troisième (ligne 8) contient une cellule vide.

4.Les fonctions BDSOMME, BDPRODUIT et BDMOYENNE Renvoient la somme, le produit, respectivement la moyenne des valeurs dans les cellules situées sur une colonne donnée, pour les lignes respectant les critères. Les cellules vides ou avec du contenu de type texte sont simplement ignorées !

a)

Syntaxe :

BDSOMME(tableau_base_de_données; indice-colonne; matrice_critères) BDPRODUIT(tableau_base_de_données; indice-colonne; matrice_critères) BDMOYENNE(tableau_base_de_données; indice-colonne; matrice_critères)

• • •

b)

tableau_base_de_données est le tableau sur lequel on applique notre fonction indice-colonne est l’indice de la colonne sur laquelle on prend les valeurs matrice_critères est la matrice contenant les critères.

Exemples :

•

BDSOMME(A1:F8;6;A11:A12) renvoie 170. Ceci est la somme de la seule et unique valeur 170 qui figure sur la colonne 6 (PrixTotal) et la ligne 4. Les lignes 3 et 5 vérifient elles aussi les critères de la matrice (nombre d’unités inférieur ou égal à 6) mais les cellules correspondantes sur la colonne 6 sont ou bien vides ou bien contiennent du texte (« N/C »).

•

BDMOYENNE(A1:F8;3;A11:A12) renvoie 85. Ceci est la moyenne des valeurs 99 et 71 situées dans la colonne 3 (CodeBoutique) sur les lignes 3 et 5. La ligne 4 vérifie aussi les critères mais la cellule CodeBoutique est vide sur cette ligne, donc ignorée.

•

BDPRODUIT(A1:F8;5;A11:A12) renvoie 90. Cela correspond au produit des valeurs 3, 5 et 6. Commentaire : la fonction BDPRODUIT est en général moins utilisée que BDSOMME et BDMOYENNE.

4

5.Les fonctions BDMIN et BDMAX Renvoient le minimum, respectivement le maximum des valeurs des cellules situées sur une colonne donnée, pour les lignes respectant les critères. Les cellules vides ou avec du contenu de type texte sont simplement ignorées !

a)

Syntaxe :

BDMIN(tableau_base_de_données; indice-colonne; matrice_critères) BDMAX(tableau_base_de_données; indice-colonne; matrice_critères)

• • •

b)

5

tableau_base_de_données est le tableau sur lequel on applique notre fonction indice-colonne est l’indice de la colonne sur laquelle on prend les valeurs matrice_critères est la matrice contenant les critères.

Exemples :

•

BDMIN(A1:F8;6;A11:A12) renvoie 170. Ceci est le minimum de la seule et unique valeur 170 qui figure sur la colonne 6 (PrixTotal) et la ligne 4. Les lignes 3 et 5 vérifient elles aussi les critères de la matrice (nombre d’unités inférieur ou égal à 6) mais les cellules correspondantes sur la colonne 6 sont ou bien vides ou bien contiennent du texte (« N/C ») ; elles sont donc ignorées

•

BDMAX(A1:F8;3;A11:A12) renvoie 99. Ceci est le maximum des valeurs 99 et 71 situées dans la colonne 3 (CodeBoutique) sur les lignes 3 et 5. La ligne 4 vérifie aussi les critères mais la cellule CodeBoutique est vide sur cette ligne, donc ignorée.

•

BDMAX(A1:F8;5;A11:A12) renvoie 6. Cela correspond au maximum des valeurs 3, 5 et 6.

6.La fonction BDLIRE Renvoient la valeur dans la cellule située sur une colonne donnée, pour la ligne respectant les critères dans la matrice. ATTENTION : • Si plusieurs lignes respectent les critères, la fonction retourne une erreur ! Si vous souhaitez afficher toutes les cellules dans les lignes qui respectent les critères, utilisez un filtre et enlevez dans le résultat les colonnes superflues ! • Si aucune ligne ne respecte les critères, la fontion retourne aussi une erreur ! Même strategie que ci-dessus !

a)

Syntaxe :

BDLIRE(tableau_base_de_données;indice-colonne; matrice_critères)

• • •

b)

tableau_base_de_données est le tableau sur lequel on applique notre fonction indice-colonne est l’indice de la colonne sur laquelle on prend les valeurs matrice_critères est la matrice contenant les critères.

Exemples :

•

BDLIRE(A1:F8;5;A11:A12) renvoie 17. Cela correspond au nombre d’unités vendues lors de l’unique vente (ligne 2) pour laquelle le client a été NARTCU.

6

•

BDLIRE(A1:F8;2;A14:B15) renvoie PLEBEN. Cela correspond au nom du client ayant participé à l’unique vente (ligne 7) pour laquelle le code de la boutique est 2 et le code du produit est Prod7.

•

BDLIRE(A1:F8;4;A17:A18) renvoie Prod4. Cela correspond au code du produit vendu lors de l’unique vente (ligne 4) pour laquelle le nombre d’unités est inférieur ou égal à 6 et le prix total strictement supérieur à 100.

7.Les jokers Lorsque nous utilisons des critères de comparaison avec des valeurs de type texte, nous pouvons spécifier un « modèle » avec lequel comparer, plutôt qu’un texte exact. Les « modèles » (ils s ‘appellent expressions régulières) peuvent être très compliqués ; nous ne nous occupons dans ce cours que de modèles très simples, qui comprennent des « jokers » de la forme un astérisque).

.* (point suivi par

Exemples d’utilisation de ces jokers : •

A.* veut dire que le texte doit commencer par A et peut être suivi par n’importe quel(s) caractère(s).

•

.*t veut dire que le texte doit finir par t et peut commencer par n’importe quel(s) caractère(s).

•

a)

.*c.* veut dire que le texte peut commencer et finir par n’importe quel(s) caractère(s), il faut juste que « quelque part dans ce texte » il existe la lettre c.

Exemples

•

BDNBVAL(A1:F8;1;A20:A21) renvoie 3. Cela correspond au nombre de ventes (le numéro de vente n’est jamais laissé vide) pour lesquelles le nom du client commence par la lettre M (lignes 3, 4 et 5).

•

BDSOMME(A1:F8;5;C20:C21) renvoie 17. Cela correspond à la somme (6+11) des nombres d’unités vendues lors des ventes pour lesquelles le nom du client finit par la lettre N (lignes 5 et 7).

7...