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
Utilisations
des fonctions sous forme matricielle
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.