Archives par étiquette : Fonction

Excel – Faire une somme selon plusieurs critères (SOMME.SI.ENS)

Contexte

Vous disposez d’un tableau (par exemple un tableau des scores). Les participants sont des hommes et des femmes rattachés à deux équipes différentes. Vous aimeriez faire la somme des points seulement s’il s’agit des femmes de l’équipe1, puis une somme des points obtenus par les hommes de l’équipe1 et la même chose pour l’équipe2. Bref, il s’agit de faire une somme si deux critères sont remplis (genre et équipe) afin d’obtenir ceci :

SommeSiEns

Solution : utilisez la fonction SOMME.SI.ENS

Pour la formule devant faire la somme des points obtenus par les hommes du groupe CE :

  • Placez-vous dans la cellule devant contenir la formule (dans mon exemple E30)
  • Insérez la fonction =SOMME.SI.ENS()
  • Indiquez quelle est la plage devant être additionnée (D3:D26), tapez un point-virgule
  • Indiquez quelle est la plage contenant le 1er critère (B3:B26), tapez un point-virgule
  • Indiquez entre guillemets quel est le 1er critère, tapez un point-virgule
  • Indiquez quelle est la plage contenant le 2ème critère (C3:C26), tapez un point-virgule
  • Indiquez entre guillemets quel est le 2ème critère et validez la formule, laquelle doit ressembler à ceci :

formuleSommeSiEns

  • Faites la même chose pour les autres scores à additionner (ou copiez la formule et adaptez-la)

Constat suite à cet exercice : il y a trop peu de femmes travaillant au Ci… 🙁

Excel – Utiliser NB.SI dans une mise en forme conditionnelle

Contexte

Vous disposez d’un fichier Excel faisant l’inventaire d’articles. Les libellés de ces articles contiennent des noms (de marques, de fournisseurs…) par exemple « Citron vert » ou « Sucre brun » ou « Rhum ambré ». Vous aimeriez attribuer une mise en forme particulière à une cellule en fonction du contenu des libellés afin de repérer facilement les données, par exemple « Si le libellé en A3 contient le mot « Rhum ambré » alors la cellule B3 des quantités doit apparaître avec une trame brune sinon le texte doit s’écrire de manière standard ». Vous avez essayé la fonction « SI » mais ça ne marche pas…

Solution

  • Cliquez sur la cellule pour laquelle vous souhaitez une mise en forme conditionnelle (dans mon exemple B3)
  • Allez dans l’onglet Accueil > bouton Mise en forme conditionnelle > Nouvelle règle
  • Choisissez un style Classique > Utiliser une formule pour déterminer…
  • Tapez la formule suivante :
    NB.SI($A3;“*sucre brun*“) autrement dit « si la cellule A3 contient (et non pas « est égal ») l’expression « sucre brun » la cellule B3 doit être jaune (remarquez dans la formule que la colonne A est absolue et que la ligne 3 est relative car nous aurons besoin que le numéro de la ligne s’adapte lors de la recopie vers le bas) :

nb.si

  • Faites de même pour les autres conditions, afin d’obtenir ceci :

nb.si2

  • Validez cette boîte de dialogue et utilisez la poignée de recopie de la cellule B3 pour recopier cette mise en forme conditionnelle vers le bas :

nb.si3

Excel – Comparer le contenu de deux plages de cellules

Contexte

Vous devez comparer, dans une feuille Excel, les contenus de deux colonnes (A et B) dans lesquelles ont été saisis par exemple des noms de personnes. Vous devez trouver si des noms ont été saisis dans les deux colonnes, sachant qu’ils n’ont pas été saisis dans le même ordre (par ex. mon nom a été saisi en A4 dans la colonne A et en B2 dans la colonne B). Si Excel trouve une correspondance de noms entre la colonne A et la colonne B, vous aimeriez qu’il vous indique, dans la colonne C, le nom de la personne trouvée à double.

EquivExcel

Solution

  • Entrez la formule suivante dans une cellule vide (dans mon exemple en C1)
    =SI(ESTERREUR(EQUIV(A1;$B$1:$B$42;0)); ″pas trouvé″; A1)
  • A l’aide de la poignée de recopie, recopiez cette formule jusqu’en C42.

Excel va vérifier, dans la plage allant de B1 à B42, si il trouve le même contenu que dans la cellule A1. S’il y a équivalence entre le contenu de la cellule A1 et la plage de cellules B1:B42, Excel va indiquer le contenu (autrement dit le prénom de la personne) qu’il a trouvé en A1. S’il n’a pas trouvé d’équivalence, Excel écrit le texte « pas trouvé ».
Le 0 figurant dans la formule a son importance car il indique à la fonction EQUIV que la recherche doit se faire n’importe où dans la plage allant de B1:B42.
Les dollars $ ont également leur importance car vous allez recopier cette formule jusqu’en C42. Si vous omettez les $, les adresses seront relatives (elles vont donc s’adapter lors de la recopie) alors qu’il est absolument nécessaire que les adresses de la plage soient absolues (car les adresses B1 à B42 doivent rester fixes).

Remarquez qu’Excel ne tient pas compte de la casse dans la recherche d’équivalent.

Remarquez aussi que cette formule fonctionnerait dans le cas où le texte recherché est lui-même le fruit d’une concaténation de prénom et de nom effectuée à l’aide de la formule suivante : =CONCATENER(F1;″ ″;G1).

Outlook 2013 – S’abonner à des flux RSS

Contexte

Vous utilisez Outlook et constatez qu’il existe un dossier Flux RSS (vide) dans la liste des dossiers. Vous vous demandez à quoi peut bien servir ce dossier et comment l’utiliser.

Explication

Certains sites Internet ou blogs (comme celui que vous êtes en train de lire) proposent un service appelé Flux RSS. Grâce à ce flux dynamique de données, le site peut alimenter et mettre à jour des informations comme des flash Info, des news ou toute autre information susceptible de changer tout au long de la journée. Les informations mises à jour à partir du flux RSS sont automatiquement téléchargées sur votre ordinateur. Outlook permet la lecture des contenus de flux RSS comme vous le feriez avec de simples emails.

Solution : ajouter un flux RSS dans Outlook

  • Allez sur une page internet (par exemple http://www.unil.ch/ci/home.html)
  • Cliquez sur le symbole indiquant la présence d’un flux flux
  • Choisissez la commande S’abonner à ce flux (lequel peut porter un nom différent selon la page visitée)
  • Cliquez sur le bouton S’abonner maintenant
  • Cliquez dans la zone d’adresse, sélectionnez l’adresse, par ex. http://rss.unil.ch/ci et copiez-la
  • Dans Outlook, cliquez sur le dossier Flux RSS à l’aide du bouton droit de la souris > Ajouter un nouveau flux RSS
  • Copiez l’adresse et cliquez sur Ajouter puis sur Oui
  • A chaque fois qu’une nouvelle news sera publiée sur cette page Internet, vous recevrez automatiquement un message dans Outlook vous permettant d’afficher l’information complète

Lorsque vous vous abonnez à un flux RSS, vous ne donnez pas votre nom, ni votre adresse de messagerie, ni aucune information personnelle. L’éditeur RSS ne se connecte à aucun moment à votre ordinateur. C’est votre ordinateur qui contacte le serveur de l’éditeur RSS pour solliciter les mises à jour de contenu.

Excel – Aérer les formules de calcul

Contexte

Vous avez ouvert un tableau Excel et certaines formules de calcul vous semblent difficile à déchiffrer (surtout si elles ont été faites par quelqu’un d’autre). Vous aimeriez simplement rendre ces formules plus lisibles en séparant les éléments qui les constituent.

Solution : aérez vos formules

  • Tout d’abord, agrandissez la zone dédiée à la barre de formule (nécessaire seulement sur Mac)
  • Placez votre curseur dans la formule à l’endroit souhaité
  • Appuyez sur les touches Cmd + Ctrl + Enter (Mac) ou sur les touches Alt + Enter (PC); vous obtenez alors un saut de ligne
  • Poursuivez ainsi en insérant des saut de ligne aux endroits souhaités
  • Insérez éventuellement des espaces blancs pour séparer les parenthèses :

La formule :

=SI(SOMME(D23-C23;F23-E23;H23-G23)=0;””;SOMME(D23-C23;F23-E23;H23-G23))

devient :

=SI(
SOMME(D23-C23;F23-E23;H23-G23)
=0;””;
SOMME(D23-C23;F23-E23;H23-G23) )

Les sauts de ligne et les espaces blancs sont sans effets secondaires indésirables sur le résultat de la formule.

Excel – Utiliser la fonction SI(ESTVIDE)

Contexte

Vous disposez d’un fichier Excel contenant par exemple des adresses de contacts internes (facultés, services, unités…). Dans votre tableau, une colonne est prévue pour contenir les URL des contacts (colonne B). Or, pour certains d’entre eux les URL sont inconnues et vous aimeriez, dans ce cas, qu’une URL « générique » soit utilisée (par ex. www.unil.ch). Il faut donc insérer dans une nouvelle colonne (colonne C), une fonction qui complèterait automatiquement les URL inexistantes par l’URL générique (se trouvant en A1).

Solution : utiliser la fonction SI(ESTVIDE)

  • Créez une colonne supplémentaire par ex. URL_complété
  • En C3, saisissez la fonction suivante : =SI(ESTVIDE(B3);T($A$1);T(B3)) autrement dit si B3 est vide, alors insérer le texte se trouvant en A1, sinon insérer le texte se trouvant en B3. Remarquez que j’ai transformé A1 en une adresse absolue (à l’aide des $)
  • Recopiez cette fonction sur les autres cellules de la colonne C

SiEstVide

FileMaker – Entre Si et Cas votre coeur balance…

Contexte

Vous souhaitez créer une rubrique permettant de poser une condition avec deux résultats possibles : 1° le résultat si la condition est vraie et 2° le résultat si la condition est fausse. Habitué aux fonctions d’Excel ou de Word, vous avez immédiatement pensé au fameux « Si…alors…sinon ». Toutefois, vous vous souvenez que cette fonction présentait l’inconvénient de devenir un peu compliquée lorsque plusieurs conditions étaient posées… d’ailleurs rien que le nombre de parenthèses à ouvrir puis à fermer avait le don de vous irriter…

Exemple pratique

Vous disposez, dans votre base d’adresses FileMaker, d’une rubrique Titre dans laquelle ont été saisies les valeurs : « M. », « Mme » et « Mlle »*. Parfois, il arrive que cette rubrique soit laissée vide, car vous ne disposez pas du nom d’une personne pour toutes les fiches. En effet, certaines fiches ne contiennent que le nom de l’entreprise, du service ou de la faculté. Vous souhaitez pouvoir disposer d’une rubrique supplémentaire qui indiquerait « Cher Monsieur » ou « Chère Madame » ou « Chère Mademoiselle » ou de manière plus générique « Chère Madame, cher Monsieur » dans le cas où la rubrique Titre n’est pas renseignée.

*bien entendu sous forme de liste de valeurs, afin d’éviter les fautes de frappe de type MM., Mmes, Melle ou M&M’s 😉

Solution n°1 – Utiliser les SI imbriqués, se concentrer sur les SI et leurs parenthèses

  • Créez une rubrique « Politesse » de type Calcul
  • Construisez votre formule selon la logique suivante :
Si la rubrique Titre est égale à « M. »
alors on affiche le texte « Cher Monsieur », sinon
Si ( Titre= »M. » ; « Cher Monsieur » ;
Si la rubrique Titre est égale à « Mme »
alors on affiche le texte « Chère Madame », sinon
Si ( Titre= »Mme » ; « Chère Madame » ;
Si la rubrique Titre est égale à « Mlle »
alors on affiche le texte « Chère Mademoiselle »,
Si ( Titre= »Mlle »; « Chère Mademoiselle » ;
si aucune des conditions n’est remplie
alors on affiche le texte « Chère Madame, Cher Monsieur »
« Chère Madame, Cher Monsieur » )))

Résultat :
Si ( Titre= »M. » ; « Cher Monsieur » ; Si ( Titre= »Mme » ; « Chère Madame » ; Si ( Titre= »Mlle »; « Chère Mademoiselle » ; « Chère Madame, Cher Monsieur » )))

Solution n°2 – Utiliser la fonction CAS et se détendre

  • Créez une rubrique « Politesse » de type Calcul
  • Construisez votre formule selon la logique suivante :
Dans le cas où la rubrique Titre est égale à « M. »,
on affiche le texte « Cher Monsieur »
Cas ( Titre= »M. » ; « Cher Monsieur » ;
Dans le cas où la rubrique Titre est égale à « Mme »,
on affiche le texte « Chère Madame »
Titre= »Mme » ; « Chère Madame » ;
Dans le cas où la rubrique Titre est égale à « Mlle »,
on affiche le texte « Chère Mademoiselle »
Titre= »Mlle »; « Chère Mademoiselle » ;
Dans le cas où aucune des conditions n’est remplie
on affiche le texte « Chère Madame, Cher Monsieur »
« Chère Madame, Cher Monsieur » )

Résultat :
Cas ( Titre= »M. » ; « Cher Monsieur » ; Titre= »Mme » ; « Chère Madame » ; Titre= »Mlle » ; « Chère Mademoiselle » ; « Chère Madame, Cher Monsieur »)

Avec cet exemple simple, on constate, grâce à la fonction CAS, que nous avons fait l’économie d’une imbrication pouvant être compliquée selon la situation et qu’il n’y a qu’une seule paire de parenthèse.

FileMaker – Extraire des données d’une rubrique

Contexte

Vous disposez d’une base de données dont une rubrique sert à stocker des codes d’articles. Ce code est composé d’un nombre différent de caractères alphanumériques et contient également plusieurs points servant à séparer les données, par ex. XYZ.1.070.9001.50 ou XYZ.1.060.9001.2580.

Vous aimeriez séparer les données de ce code afin d’obtenir 2 rubriques distinctes (début de code et fin de code). Le début du code va jusqu’au 10ème caractère (XYZ.1.060.) et la fin du code démarre au 11ème caractère jusqu’au dernier (sachant que le nombre de caractères composant la fin du code peut compter de 8 à 10 caractères).

Solution

  • Créez 2 rubriques supplémentaires de type Calcul appelée DébutCode et FinCode
  • La rubrique DébutCode aura comme formule : Extrait ( Code ; 1 ; 9 )
  • La rubrique FinCode aura comme formule : Extrait ( Code ; 11; 10 )

La formule Extrait ( Code ; 1 ; 9 ) fonctionne de cette façon : FileMaker extrait de gauche à droite 9 caractères de la rubrique Code en partant du 1er caractère. La formule Extrait (Code ; 11; 10 ) extrait 10 caractères (ou moins) en partant du 11ème caractère du code.

Excel – Calculer le nombre de jours de travail

Contexte

Vous planifiez un projet dans Excel et souhaiteriez calculer le nombre de jours de travail (ouvrés) situés entre deux dates, par exemple entre aujourd’hui (le 17.09.2014) et la fin de l’année (le 31.12.2014). Or, entre ces deux dates se trouvent des jours fériés et des week-ends durant lesquels les ressources ne travaillent pas.

Solution

  • Entrez dans la cellule A1 la date de départ (par exemple la date d’aujourd’hui que vous saisissez manuellement ou à l’aide de la fonction =aujourdhui() … à vous de voir
  • Dans la cellule A2, tapez la 2ème date (par exemple le 31.12.2014)
  • Dans les cellules B1, B2 et B3, tapez les jours fériés jusqu’à la fin de l’année soit les 22.09.2014, 25.12.2014 et 26.12.2014
  • Dans la cellule A3, tapez la formule =NB.JOURS.OUVRES
  • Entre les parenthèses, indiquez la date de départ (A1), tapez un point-virgule puis désignez la date de fin (A2)
  • Dans la formule, après l’indication A2, tapez un point-virgule et sélectionnez les cellules contenant les jours fériés
  • Votre formule doit donc ressembler à ceci : =NB.JOURS.OUVRES(A1;A2;B1:B3)

Résultat obtenu : 73 jours ouvrés (en comptant aujourd’hui)…. allez, courage 😉

Excel – Planifier un projet

Contexte

Vous planifiez un projet dans Excel et avez prévu plusieurs colonnes pour les dates liées aux tâches du projet (date de début de la tâche, date de fin de la tâche et date de suivi). Étant donné que les tâches ne sont pas toutes terminées (certaines cellules « date de fin » sont vides), vous souhaitez pouvoir calculer la différence entre la date de début et la date de suivi mais seulement en l’absence d’une date de fin. Vous avez prévu d’entrer les dates manuellement; seule la différence entre les dates doit être calculée.

Solution

Utilisez la fonction* DATEDIF, sachant que dans mon exemple :

  • Le nom de la tâche est en A2
  • La date de début de la tâche est en B2
  • La date de fin (s’il y en a une) est en C2
  • La date de suivi prévue est en D2
  • La formule de calcul se trouve en E2
    =SI(C2= » »;DATEDIF(B2;D2; »d »);DATEDIF(B2;C2; »d »)) & » jours »

Autrement dit : Si la cellule C2 est vide, Excel doit calculer la différence entre B2 et D2. Cette différence doit être exprimée en jours (argument “d”). Si C2 n’est pas vide, Excel doit calculer la différence entre B2 et C2. Cette différence doit également être exprimée en jours. L’indication « & jours » à la fin de la formule fera apparaître le mot « jours » dans le résultat du calcul afin d’éviter une mauvaise interprétation du résultat :DateDifetSI

* La particularité de cette fonction est qu’elle ne figure pas dans la liste officielle des fonctions d’Excel ;)