Les Fonctions ... par l'exemple !

Rubrique débutée le 4/10/2000. Mise à jour au 23/10/01

TEXTE (manipulation de base de données)
La concaténation par &
Nom et prénom dans 2 colonnes à réunir dans une seule :
le &" " insère un espace entre nom et prénom

La dé-concaténation par le menu Outils - Convertir
Nom et prénom dans une colonne à séparer dans 2.
Sélectionner les éléments ou la colonne et appeler le menu Outil-Convertir. l'assistant vous prend en charge.
Étape 1 : Si nom et prénom ne sont pas alignés, choisir l'option Délimité
Étape 2 : Le délimiteur sera l'Espace (il y en a toujours un entre nom et prénom !)

Étape 3 : Attention à la cellule de destination. Si vous laissez A2, ici, vous écrasez vos données d'origine. Vous pouvez remplacer A2 par B2, les noms viendront dans la colonne B et les prénoms dans la colonne C. Néanmoins, méfiez-vous des noms ou prénoms composés avec un espace qui produiront un décalage de colonnes à la fin de la conversion. De même, vérifiez au préalable qu'il n'y a pas de données dans les colonnes qui recevront la conversion (ici, colonne B et C)

RECHERCHES ET MATRICES
La fonction DECALER :Traduction approximative : je fais la moyenne de à partir de B4, je descend de la valeur contenue dans les cellules E3-1 cellules vers le bas (ici 5 cellules)  et je renvoie les E3 cellules en remontant (ici 6) sur lesquelles porte la moyenne.

RECHERCHEV (VRAI)
Traduction approximative : je cherche la valeur contenue dans une cellule (d16) dans le tableau de référence (ici NOTES pour la plage A2:B8) et dès que je l'ai trouvée, je renvoie la valeur située sur la même ligne et contenue dans la Xème colonne (ici la 2ème), et je cherche une valeur proche (argument VRAI) et non une valeur exacte (argument FAUX).
Les recherches V pour vertical cherche une valeur dans la 1er colonne d'un tableau et renvoie le contenu située sur la même ligne à la Xème colonne.
L'argument VRAI s'applique aux valeurs proches, exemple : est-ce que 8.5 est supérieur à 8 (oui), à 10 (non), la valeur renvoyée correspondra à 8. Cependant, la fonction marche uniquement si la base est classée : 8-10-12-...

RECHERCHEV (FAUX)
Traduction approximative : je recherche une information saisie ou récupérée (ici en B18) qui se trouve dans la 1ère colonne d'un tableau (ici,appelé Base), et je renvoie la valeur située à la Xème colonne (ici, 4 ou 5ème) du tableau et située sur la même ligne et je recherche une information strictement identique (argument VRAI) et non,  une valeur proche (argument FAUX). Ici, il n'y a qu'un numéro 111, je ne recherche pas ce qui est supérieur à 111 mais ce qui est strictement égal à 111.
Avec l'argument terminal FAUX, la base de référence n'a pas besoin d'être classée, mais ne doit pas contenir de doublons dans la 1ère colonne.

Toutefois, préférez à la fonction RECHERCHEV (FAUX), la fonction INDEX couplée à un contrôle formulaire (voir plus loin), plus agréable à mettre en œuvre. Toutefois, RECHERCHEV est très utile pour récupérer à partir d'une information obtenue par une fonction INDEX, une autre information dans une autre base

La fonction INDEX liée à une boîte de dialogue
Souvent, l'information à rechercher existe au sein d'une base déjà présente. Et, plutôt que taper un nom, une référence et de faire appel à la fonction RECHERCHEV(faux), on peut choisir la référence de son information grâce à un clic dans une boîte de dialogue et récupérer les autres informations grâce à la fonction INDEX.
Le présent exemple porte sur la récupération d'adresse (pour un bordereau d'envoi, une facture...)
1- Nommer la base : pourquoi ? pour rendre les formules plus lisibles. Cette base de donnée nommée ici BASE est située dans une autre feuille du classeur (les noms sont disponibles directement dans tout le classeur)
2- Création de la boîte de dialogue
On appelle la barre d'outils Formulaire (clic droit) à la fin d'une barre d'outils "standard" et cliquez sur zone de liste modifiable ou zone de liste et dessinez   (élégamment ! ) un rectangle. Puis par un clic droit sur cet objet, on sélectionne Format de contrôle. L'onglet indispensable à compléter est Contrôle (la gestion de la boîte de dialogue s'effectue ici). La plage d'entrée correspond à votre base de référence, sa première colonne apparaîtra dans la boîte de dialogue. La cellule liée (située où vous voulez dans le classeur) reflétera la position sélectionnée dans la base i.e. le numéro de la ligne sélectionnée dans la base par le clic de souris effectué dans la boîte de dialogue (exemple la 1ère, la 6ème ligne de la base...). Nombre de lignes correspond aux nombres d'entrées visibles dans la boîte de dialogue lorsqu'on clique sur le triangle (vers le bas) de la boîte de dialogue. Toutefois, dans l'onglet Propriété de Format de contrôle, pensez (éventuellement) à décocher Imprimez l'objet.

Cliquez en dehors pour désélectionner l'objet, votre boîte de dialogue est opérationnelle. Cliquez sur le triangle et sélectionnez une autre valeur. Il ne se produit rien si ce n'est un changement de la valeur contenue dans la cellule liée. Et c'est cette information que nous allons exploiter.
3- Récupération des informations par la fonction Index
A partir du nom sélectionné dans la boîte de dialogue, nous souhaitons que soit affiché en B3, le nom, en B4, l'adresse et en B5, la ville. Situé en B3, nous appelons l'assistant fonction (catégorie Recherches & matrices) et sélectionnons la fonction Index, puis l'option Tableau, n° lignes, n° colonne.

Le tableau représente l'ensemble de notre base où se trouvent les informations à rechercher i.e. la zone nommée Base,
Le numéro de ligne : à quelle ligne se situe l'information : à la1ère, la 3ème..?, cette information est inscrite dans la cellule liée à la boîte de dialogue (en D1). Un conseil, quand vous avez sélectionnez la cellule D1, appuyer sur la touche F4 (mode relatif et absolu) afin d'atteindre l'affichage absolu $D$1, ainsi la fonction sera recopiable facilement pour l'adresse et la ville.
Le numéro de colonne : où se trouve le nom à récupérer : dans la 1ère colonne. On indique dans cette zone, 1.
La fonction est alors terminée, le nom sélectionné s'affiche alors en B3
4- Récupérer les autres informations : adresse et ville
Avec la poignée de recopie, on descend 2 lignes, s'affiche alors 3 fois le nom. Il suffit alors sur la seconde ligne (en B4) de changer le numéro de colonne de 1 en 2 , l'adresse se trouvant dans la 2ème colonne, et sur la troisième ligne (en B5) de changer le numéro de colonne de 1 en 3.

Vos informations sont à présent disponibles par un simple clic de souris

La fonction Moyenne :
Supposons une base avec des dates de départ et d'arrivée :
Nous ne souhaitons pas avoir de résultats intermédiaires, mais uniquement la moyenne des séjours. Seule, une fonction matricielle (donc s'adressant à une plage de données) est adaptée.

en C1, nous saisirons donc :
=Moyenne(B2:B5-A2:A5)
Mais, nous validerons par la touche Entrée, mais par l'association de touches CTRL+MAJ+ENTREE
la formule apparaît avec des accolades supplémentaires :

{=Moyenne(B2:B5-A2:A5)} indiquant son caractère matriciel. Une limite, toutefois, cette utilisation n'aime pas les données vides (résultat erronnée)

Les Raccourcis clavier

Les associations de touches du clavier les plus fréquemment utilisées :
CTRL ;  (ou contrôle point virgule) : inscrit dans une cellule la valeur de la date actuelle. Equivalent à la fonction AUJOURDHUI mais sans recalcul. C'est la valeur et non la fonction qui est inscrite. Pratique, pour un historique d'un document ou pour remplir une base de donnée en direct.
CTRL  : (ou contrôle double point ) : inscrit dans une cellule la valeur de l'heure courante (sans recalcul). Utile en cas de simulations multiples (type un budget) à différentes heures de la journée.
CTRL * (ou contrôle étoile) : sélectionne la plage rectangulaire contenant des données en lignes et en colonnes, s'arrête lorsque les lignes et colonnes sont vides. Très pratique pour sélectionner rapidement l'ensemble des données d'un tableau : on sélectionne une cellule remplie (ou adjacente à une cellule remplie) et CTRL *. On peut alors appliquer un traitement, une mise en forme sur l'ensemble de la plage.