[REF] Wiki 5 de MichelXld (Formules, Audits de formules, Répertoires, Fichiers)

MichelXld

XLDnaute Barbatruc
Les sujets abordés dans cette page
Les formules Excel , Les audits de formules , Les répertoires et les fichiers .​




Les formules Excel ( mise à jour le 25.11.2006 )

  • Généralités
    Les symboles opérateurs​
    Addition +​
    Soustraction -​
    Multiplication *​
    Division /​
    Exposant ^​
    Pourcentage %​
    Egal =​
    Différent <>​
    Supérieur >​
    Supérieur ou égal >=​
    Inférieur <​
    Inférieur ou égal <=​
    La langue d'écriture des formules doit toujours etre identique à la version d'Excel installée sur le PC​
    Nommer les formules​
    Pour valider une formule matricielle :​
    Cliquez dans la barre de formule et appuyez sur les touches Ctrl + Maj + Entrée​
    Ensuite , dans la barre de formule , apparait une accolade à chaque extrémité de la formule: {=maFormule}​
    (Si vous nommez les formules matricielles , les validations ne sont plus nécéssaires)​
    Utiliser une cellule fixe dans une formule :​
    Les références relatives :​
    (informations issues de l'aide en ligne Excel)​
    Une référence de cellule relative, par exemple =A1, est basée sur la position relative de la cellule qui contient la formule et de la cellule à laquelle la référence renvoie.​
    Si la position de la cellule qui contient la formule change, la référence est modifiée. Si vous copiez la formule dans d'autres lignes ou colonnes, la référence est automatiquement adaptée en conséquence.​
    Par exemple, si vous copiez une référence relative =A1 de la cellule B2 dans la cellule B3, elle est automatiquement adaptée en =A2.​
    Les références absolues :​
    Une référence de cellule absolue, par exemple $A$1, correspond à une cellule se trouvant à un endroit spécifique. Si la position de la cellule qui contient la formule change, la référence absolue reste inchangée.​
    Par exemple, si vous copiez une référence absolue de la cellule B2 dans la cellule B3, elle reste la même dans les deux cellules : =$A$1.​
    Par défaut, les formules utilisent des références relatives .Il faut donc les transformer en références absolues (insertion des symboles $ ).​
    Les références mixtes :​
    Une référence mixte comprend soit une colonne absolue et une ligne relative, soit une ligne absolue et une colonne relative. Une référence de colonne absolue s'affiche sous la forme $A1, $B1, etc. Une référence de ligne absolue s'affiche sous la forme A$1, B$1, etc. Si la position de la cellule qui contient la formule change, la référence relative est modifiée, alors que la référence absolue ne l'est pas. Si vous copiez la formule dans des lignes ou dans des colonnes, la référence relative est automatiquement adaptée en conséquence, alors que la référence absolue ne l'est pas. Par exemple, si vous copiez une référence mixte de la cellule A2 dans la cellule B3, elle est adaptée pour passer de =A$1 à =B$1.​
    Les références 3D​
    Si vous voulez analyser des données de la même cellule ou de la même plage de cellules sur plusieurs feuilles de calcul du classeur, utilisez une référence 3D. Celle-ci inclut la référence de cellule ou de plage de cellules précédée d'une plage de noms de feuilles de calcul. Excel utilise alors toutes les feuilles de calcul comprises entre le premier et le dernier noms de la référence.​
    Par exemple, =SOMME(Feuil2:Feuil13!B5) additionne toutes les valeurs contenues dans la cellule B5 de l'ensemble des feuilles de calcul situées entre la Feuille 2 et la Feuille 13, celles-ci incluses.​
    Un utilitaire de traduction des fonctions FR/EN et EN/FR ( merci André )​
    Lien supprimé
    Une démonstration sur la puissance de l'itération dans Excel. ( Un tutoriel de Jean Marie )​
    Lien supprimé
    Les opérateurs conditionnels dans les fonctions SOMMEPROD​
    ET : *​
    OU : +​
    SAUF : -​
    Les opérateurs <, >, <>, <=, >= , = renvoient des valeurs logiques VRAI ou FAUX​
    Ensuite , lorsque que le résultat logique répond à la requète , la somme est établie sur le principe ci dessous :​
    VRAI * VRAI = 1​
    VRAI * FAUX = 0​
    VRAI * 1 = 1​
    VRAI * 0 = 0​

  • Texte
    Compter le nombre de caractères contenus dans la cellule A1( chaque espace compte pour un caractère )​
    =NBCAR(A1)​
    Mettre en majuscule la première lettre de chaque mot , dans la cellule A1​
    =NOMPROPRE(A1)​
    Convertir en majuscule le texte contenu dans la cellule A1​
    =MAJUSCULE(A1)​
    Convertir en minuscule le texte contenu dans la cellule A1​
    =MINUSCULE(A1)​
    La fonction CAR renvoie le caractère ANSI spécifié ( dans l'exemple ci dessous , le résultat = 0 )​
    =CAR(48)​
    d'autres valeurs : CAR(48)=0 , CAR(57)=9 , CAR(65)=A , CAR(90)=Z , CAR(97)=a , CAR(122)=z​
    Supprimer les espaces en trop dans le texte de la cellule A1​
    =SUPPRESPACE(A1)​
    Contrôler si la phrase dans la cellule A1 contient le texte "xld" ( 1=oui , 0=non )​
    =NB.SI(A1;"xld")​
    Séparer le(s) prénom(s) et le nom saisis dans la cellule A1 : Une solution donnée par Phibou​
    Extraire tous les mots d'une phrase (dans la meme cellule ), vers des cellules différentes (Une démo d'André )​
    Lien supprimé
    Remplacer des données dans une chaine de caracteres (exemple "1999" par "2005" )​
    =SUBSTITUE("test 1999 et essai 1999"; "1999"; "2005")​
    Ne remplacer que la deuxieme occurence correspondant aux caracteres cible​
    =SUBSTITUE("test 1999 et essai 1999"; "1999"; "2005";2)​
    Extraire le premier mot d'un texte saisi dans la cellule A1​
    =GAUCHE(A1;CHERCHE(" ";A1;1)-1)​
    Extraire le dernier mot d'un texte saisi dans la cellule A1 : Source Disciplus excelLabo​
    {=DROITE(A1;EQUIV(" ";STXT(A1;NBCAR(A1) - LIGNE(INDIRECT("1:" & NBCAR(A1)));1);0))}​
    Afficher le nombre de caracteres maxi contenu dans une cellule de la plage A1:A10​
    {=MAX(NBCAR(A1:A10))}​
    Afficher une lettre aléatoire entre A et Z ,une solution de Tibo​
    =CAR(ENT(ALEA()*26+1)+64)​
    3 autres solutions d'André pour afficher une lettre aléatoire minuscule ou majuscule​
    =CHOISIR(ARRONDI(ALEA();0)+1;CAR(ENT(ALEA()26+1)+64);CAR(ENT(ALEA()26+1)+96))​
    =CAR(ENT(ALEA()*26+1)+64+CHOISIR(ARRONDI(ALEA();0)+1;0;32))​
    =CAR(ENT(ALEA()*26+1)+CHOISIR(ARRONDI(ALEA();0)+1;64;96))​
    Renvoie OUI si la cellule A1 est du texte​
    =SI(ESTTEXTE(A1);"OUI";"NON")​
    Compter le nombre de "a" dans la cellule A1​
    =NBCAR(A1)-NBCAR(SUBSTITUE(A1;"a";""))​
    Un autre exemple qui compte le nombre de "mimi" contenu dans la cellule A1​
    =(NBCAR(A1)-NBCAR(SUBSTITUE(A1;"mimi";"")))/4​
    Compter le nombre de mots (séparés par un espace) dans la cellule A1​
    =NBCAR(A1)-NBCAR(SUBSTITUE(A1;" ";"")) + 1​
    Compter le nombre de cellules qui contient du texte dans la plage A1:A10​
    =SOMMEPROD(ESTTEXTE(A1:A10)*1)​
    Compter le nombre de cellules qui contient le texte XLD , uniquement quand saisi en majuscules​
    =SOMMEPROD((EXACT(A1:A10;"XLD")*1))​
    Compter le nombre de cellules en majuscules dans la plage A1:A10 ( une solution proposée par Ti)​
    =SOMMEPROD((A1:A10<>"")*(EXACT(A1:A10;MAJUSCULE(A1:A10))))​
    Vérifier si les données sont en majuscule , minuscule , ou les deux , dans la cellule A1​
    =SI(EXACT(A1;MAJUSCULE(A1));"MAJUSCULE";SI(EXACT(A1;MINUSCULE(A1));"MINUSCULE";"MAJUSCULE ET MINUSCULE"))​
    Concaténer sur 2 lignes dans une meme cellule​
    ( le renvoi à la ligne automatique doit etre activé dans la cellule contenant la formule )​
    =A1&CAR(10)&A2​
    Inverser l'ordre des valeurs de la cellule A1 (uniquement pour des valeurs numeriques) : une astuce de Phibou​
    =SOMME(STXT(A1;NBCAR(A1)-LIGNE(INDIRECT("1:" & NBCAR(A1)))+1;1)*10^(NBCAR(A1)-LIGNE(INDIRECT("1:" & NBCAR(A1)))))​
    A valider avec CTRL + MAJ + ENTREE​
    Créer une suite logique de lettres ( de A à Z )​
    Tout d'abord saisissez la formule dans la cellule A1​
    =CAR(64+LIGNE())​
    Ensuite utilisez la poignée de recopie vers le bas​
    Pour obtenir une suite de lettres minuscules vous pouvez utiliser :​
    =MINUSCULE(CAR(64+LIGNE()))​
    Convertir en nombre une chaîne de caractères représentant un nombre​
    =CNUM(A1)​
    Extraire une partie des données saisies dans la cellule A1​
    L'exemple effectue une extraction du 2eme au 5eme caractere​
    =STXT(A1;2;4)​
    Additionner une plage de cellules Contenant des Espaces Redondants​
    =SOMME(CNUM(SUBSTITUE(A1:A10;" ";"")))​
    formule à valider par Ctrl+Maj+Entree​
    L'exemple ne fonctionne pas s'il y a des cellules vides​
    Inverser la position du Nom et du prénom (il ne doit y avoir qu'un espace dans la chaine de caractères)​
    =DROITE(A1;NBCAR(A1)-CHERCHE(" ";A1))&" "&GAUCHE(A1;CHERCHE(" ";A1))​
Statistiques​
Compter le nombre de valeurs numériques das la colonne A​
=NB(A:A)​

Compter le nombre de cellules non vides dans la colonne A​
=NBVAL(A:A)​

Compter les cellules non vides dans une plage qui contient des formules renvoyant ""​
=SOMMEPROD((A1:A10<>"")*1)​

Compter le nombre de cellules contenant la valeur "xld" , dans la colonne A​
=NB.SI(A:A;"xld")​

Compter le nombre de cellules vides dans la plage A1:A10​
=NB.VIDE(A1:A10)​
Remarque de l'aide en ligne Excel :​
Les cellules contenant des formules qui renvoient " " (texte vide) sont également comptées, ce qui n'est pas le cas des cellules contenant la valeur zéro​

Afficher la 2eme valeur la plus élevée , de la plage A1:A10​
=GRANDE.VALEUR(A1:A10;2)​

Afficher la plus petite valeur de la plage A1:A10 , sans tenir compte des 0​
=PETITE.VALEUR(A1:A10;NB.SI(A1:A10;0)+1)​
Une autre solution (valider par Ctrl+Maj+Entree)​
=MIN(SI(A1:A10=0;"";A1:A10))​

Afficher la valeur mini d'une plage sans prendre en compte les 0 et les champs vides (une solution donnée par Jean Marie)​
=MIN(SI(A1:A10>0;A1:A10;""))​
Formule matricielle à valider par Ctrl+Shift+Entrer​

Afficher la plus petite valeur immédiatement superieure à 0 ( une solution donnée par Monique)​
=MIN(SI(A1:D1>0;A1:D1))​
Formule matricielle, à valider par ctrl, maj et entrée​

Renvoie la somme des 6 plus grandes valeurs d'une plage​
(Les doublons éventuels de la 6eme valeur sont comptabilisés)​
=SOMME(A1:A10*(A1:A10>=GRANDE.VALEUR(A1:A10;6)))​
Formule matricielle à valider par Ctrl+Maj+Entree​

Une autre possibilité ( qui ne prend pas en compte les doublons de la 6eme valeur )​
=SOMME(GRANDE.VALEUR(A1:A10;{1.2.3.4.5.6}))​

Afficher la moyenne des 5 plus petites valeurs , de la plage A1:A100​
=SI(NBVAL(A1:A100)>=5;MOYENNE(PETITE.VALEUR(A1:A100;{1.2.3.4.5}));MOYENNE(A1:A100))​

Trouver une valeur proche dans une colonne​

Afficher le classement de la cellule B1 , par rapport à la plage de valeurs B1:B10​
=NB($B$1:$B$10)+1-RANG(B1;$B$1:$B$10;1)​

Afficher le classement de toutes les cellules de la plage A1:A10 , les unes par rapport aux autres .​
(les données numériques sont dans la plage A1:A10)​
=RANG(A1;DECALER($A$1;0;0;NBVAL($A$1:$A$10);1))​
Saisissez la formule dans la cellule B1 et ensuite faites un "Glisser" , jusqu'à la cellule B10​

Afficher la valeur numérique qui apparait le plus souvent dans la plage A1:A10​
=MODE(A1:A10)​

Afficher la donnée (texte ou numérique ) qui apparait le plus souvent dans la plage A1:A10 ( une solution donnée par Jean Marie )​
{=INDEX(A1:A10;EQUIV(MAX(NB.SI(A1:A10;A1:A10));NB.SI(A1:A10;A1:A10);0))}​

D'autres solutions données par Jean Marie et Monique , nottament pour retrouver les 5 références les plus représentées​

Afficher la donnée qui apparaît le plus souvent et au moins 3 fois dans la plage A1:A10( une solution donnée par Jean-Marie)​
=SI(NB.SI(A1:A10;MODE(A1:A10))>2;MODE(A1:A10);"")​

Compter le nombre de valeurs identiques entre deux plages​
=SOMMEPROD(NB.SI(A1:A10;B1:B10))​

Compter le nombre de valeurs identiques (unique) entre deux plages​
=SOMMEPROD((FREQUENCE(A1:A10;B1:B10)>0)*1)-1​

Compter le nombre d'éléments qui répond à plusieurs critères sur différentes colonnes​
= SOMMEPROD ( ( A1:A10 = "Valeur1" ) * ( B1:B10 = "Valeur2" ) * (C1:C10 = "Valeur3" ) )​

Compter le nombre de données différentes dans la plage A1:A10​
=SOMMEPROD(1/NB.SI(A1:A10;A1:A10))​

Compter le nombre de valeurs numériques différentes dans la plage A1:A10​
Remarque : La plage A1:A10 peut contenir du texte et des cellules vides​
= SOMME(SI(FREQUENCE(A1:A10;A1:A10)>0;1))​
Une autre solution proposée par Celeda​
=SOMME(N(FREQUENCE(A1:A10;A1:A10)>0))​
Une autre possiblité à valider par Ctrl + Maj + Entree​
=SOMME(SI(ESTNUM(A1:A10);1/NB.SI(A1:A10;A1:A10)))​
Un autre exemple qui ne prend pas en compte les 0 (à valider par Ctrl + Maj + Entree)​
=SOMME(SI(A1:A10>0;1/NB.SI(A1:A10;A1:A10)))​

Compter le nombre de valeurs comprises entre 50 et 60 , dans la plage A1:A10​
= SOMMEPROD ((A1:A10 >=50 ) * ( A1:A10 <=60 ))​
Une autre solution​
= NB.SI ( A1:A10 ; ">=" &50 ) - NB.SI ( A1:A10 ; ">=" &60 )​

Renvoie "oui" si le contenu de la cellule B1 apparaît dans la plage A1:A10​
=SI(NB.SI(A1:A10;B1);"oui";"non")​

Compter le nombre de valeurs comprises entre 10 et 20 dans la plage A1:A10 ( bornes non compris )​
=FREQUENCE(A1:A10; {20;10} )​

Compter le nombre de lignes dont la date correspond au mois de Décembre​
A1:A10 est la plage contenant les dates au format jj/mm/aaaa​
12 est l'index du mois de Décembre​
=SOMMEPROD((MOIS(A1:A10)=12)*1)​

Compter le nombre de valeurs Impaires dans la plage de cellules B1:B20 ( des solutions données par Monique )​
=SOMMEPROD((MOD(B1:B20;2)=1)*1)​
=SOMMEPROD(MOD(B1:B20;2)*1)​
=SOMMEPROD((B1:B20=IMPAIR(B1:B20))*1)​

Retrouver les données uniques et les doublons dans une plage de cellules​
Les données à controler sont dans la plage A1:A20​
Saisissez la formule ci-dessous dans la cellule B1​
=SI(NB.SI($A$1:$A$20;A1)>1;"Multiple";"Unique")​
Etirez la formule jusqu'en B20​

La variance :​
La variance est la moyenne arithmétique des carrés des écarts par rapport à la moyenne arithmétique de la distribution​
La variance traduit la notion d'incertitude. Plus la variance est faible, moins le résultat de l'expérience aléatoire est incertain. La variance est le carré de l'écart-type.​
La fonction VAR part de l'hypothèse que les arguments ne représentent qu'un échantillon de la population.​
=VAR(nombre1;nombre2;...)​
=VAR(A2:A13)​
nombre1, nombre2, ... représentent de 1 à 30 arguments numériques correspondant à un échantillon de population.​
Si vos données représentent l'ensemble de la population, utilisez la fonction VAR.P pour en calculer la variance.​

L'écart type :​
L'écart type est la racine de la variance.​
=ECARTYPE(A2:A13)​
Si vos données représentent l'ensemble de la population, utilisez la fonction ECARTYPEP pour en calculer l'écart type.​

La Covariance :​
La Covariance mesure la corrélation entre deux ensembles de données. La Covariance est faible si les séries sont indépendantes ou si le lien entre les séries est non linéaire. A l'inverse, les séries sont liées si la Covariance est élevée. La covariance est la moyenne du produit des écarts .​
=COVARIANCE(matrice1;matrice2)​
=COVARIANCE(A2:A10;B2:B10)​
matrice1 représente la première plage de cellules de nombres entiers.​
matrice2 représente la seconde plage de cellules de nombres entiers.​
Les arguments doivent être soit des nombres, soit des noms, des matrices ou des références contenant des nombres​
Si matrice1 et matrice2 contiennent différents nombres d'observations (données), COVARIANCE renvoie la valeur d'erreur #N/A.​
Par exemple, vous souhaitez savoir si la fréquentation de votre site Web est liée aux campagnes publicitaires que vous organisez.​
Saisissez le nombre de connections par mois, dans les cellules B2 à B13​
Saisissez le nombre de séquences publicitaires par mois , dans les cellules D2 à D13​
Saisissez la formule =B3/B2-1 dans la cellule C3​
Copiez la formule dans les cellules C4 à C13 puis de E3 à E13. Les cellules C3:C13 et E3:E13 contiennent maintenant les variations mensuelles des fréquentations et des séquences publicitaires .​
Pour obtenir la Covariance des deux séries de données , saisissez la formule =COVARIANCE(C3:C13;E3:E13) , par exemple dans la cellule A15.​

Math et Trigo

Savoir si la valeur de la cellule est paire(la formule renvoie 1) ou impaire( la formule renvoie 0)​
=(A1=PAIR(A1))*1​
Savoir si la valeur de la cellule est paire(la formule renvoie Vrai) ou impaire( la formule renvoie Faux)​
=(A1=PAIR(A1))​
SOUS.TOTAL Renvoie le sous-total d'une plage de cellules ( somme de la plage A2:A5 dans l'exemple )​
=SOUS.TOTAL(9;A2:A5)​
Un Sous Total ne prend en compte que sur les données visibles résultant du filtrage d'une liste.​
Des sous-totaux déjà existants à l'intérieur de la plage , ne sont pas pris en compte​
Les codes de formules associées :​
1 Moyenne​
2 NB​
3 NBVAL​
4 MAX​
5 MIN​
6 PRODUIT​
7 ECARTYPE​
8 ECARTYPEP​
9 SOMME​
10 VAR​
11 VAR.P​
Un Sous Total peut etre affecté à plusieurs plages de cellules( moyenne des plages A2:A5 et C2:C5 dans l'exemple )​
=SOUS.TOTAL(1;A2:A5;C2:C5)​
Compter le nombre de X dans la plage A2:A10,si la valeur associée dans la colonne B est inférieure à 50​
= SOMMEPROD((A2:A10 = "X")*(B2:B10<50))​
Additionner les valeurs de la colonne B , si la colonne A contient le mot "xld"​
=SOMME.SI(A:A;"xld";B:B)​
Additionner les valeurs d'une plage (A1:A5) , dont certaines cellules contient des erreurs​
{=SOMME(SI(ESTERREUR(A1:A5);"";A1:A5))}​
Renvoyer un nombre aleatoire entre 1 et 3​
=ENT(ALEA()*3+1)​
Insérer un exposant dans une formule( exemple exposant 2 )​
=A1^2​
Remarque : la fonction puissance permet d'obtenir le meme résultat​
=PUISSANCE(A1;2)​
Extraire la racine carré d'un nombre​
=A1^(1/2)​
Extraire la racine cubique d'un nombre​
=A1^(1/3)​
Arrondir la valeur de la cellule A1 à l'entier immédiatement inférieur​
=ENT(A1)​
La différence entre les fonctions TRONQUE et ENT :​
Les deux fonctions renvoient des nombres entiers​
Les fonctions ENT et TRONQUE diffèrent uniquement lorsqu'il s'agit de nombres négatifs.​
TRONQUE supprime la partie décimale d'un nombre ( l'exemple ci-dessous renvoie -10)​
=TRONQUE(-10,5;0)​
ENT arrondit les nombres à l'entier immédiatement inférieur ( l'exemple ci-dessous renvoie -11)​
Arrondir la valeur de la cellue A1 à 2 chiffre apres la virgule​
=ARRONDI(A1;2)​
Arrondit A1 à la valeur entière immédiatement supérieure​
=ARRONDI.SUP(A1;0)​
Arrondit A1 à la valeur supérieure comportant trois décimales​
=ARRONDI.SUP(A1;3)​
Arrondit A1 à gauche du séparateur décimal, à la centaine immédiatement supérieure​
=ARRONDI.SUP(A1;-2)​
Arrondit A1 à la valeur entière immédiatement supérieure​
=ARRONDI.INF(A1;0)​
Arrondit A1 à la valeur inférieure comportant trois décimales​
=ARRONDI.INF(A1;3)​
Arrondit A1 à gauche du séparateur décimal, à la centaine immédiatement inférieure​
=ARRONDI.INF(A1;-2)​
Arrondir la valeur de la cellule A1 au multiple de 5 le plus proche ( par exemple 17,6 renvoie 20 )​
=ARRONDI.AU.MULTIPLE(A1;5)​
Arrondir aux 5 centièmes inférieurs ( des solutions proposées par Monique)​
=A1-MOD(A1;0,05)​
=A1-MOD(A1;5%)​
Calculer des montants de traite identiques en valeurs entieres , la totalité des décimales étant appliquée sur la dernière traite​
La somme totale est en A1 , le nombre de traites est en B1​
Le montant de chaque traite ( hormis la derniere )​
=ARRONDI($A$1/$B$1;0)​
Le montant de la derniere traite​
=A1-(ARRONDI(A1/B1;0)*(B1-1))​
Arrondir à 10 ( 213,05 renvoie 210 ; 216,001 renvoie 220​
=ARRONDI(A1/10;0)*10​
Récuprérer la partie décimale de la cellule A1 ( ne fonctionne que pour les données positives)​
=A1-ENT(A1)​
Additionner toutes les valeurs négatives de la plage A1:A10​
{=SOMME(A1:A10*(A1:A10<0))}​
Compter le nombre de fois ou "xld" dans la plage A1:A10 , est classé "number one" dans la plage B1:B10​
= SOMMEPROD((A1:A10 = "xld")*(B1:B10="number one"))​
Calculer une moyenne sans tenir compte des cellules contenant des zéros​
=SOMME(A1:A10)/NB.SI(A1:A10;">0")​
Calculer la moyenne de la plage A1:A10 , pour les données correspondantes de la plage B1:B10 qui contiennent le mot "oui"​
=SOMMEPROD( ( B1:B10) * (A1:A10 = "oui" ) ) / NB.SI(A1:A10;"oui")​
Calculer une moyenne avec double condition​
Afficher le résultat de la formule (A1+A2) , uniquement quand les deux cellules sont remplies​
=SI(ET(A1<>"";A2<>"");A1+A2;"")​
Afficher le résultat de la formule (A1/B1) , uniquement si le résultat ne renvoie pas une erreur​
=SI(ESTERREUR(A1/B1);"";A1/B1)​
Additionner les cellules A1 de toutes les feuilles : Feuil1 à Feuil4​
=SOMME(Feuil1:Feuil4!A1)​
Additionner les cellules de la plage C1:C10 , si la cellule associée dans la plage A1:A10 est égal à "xld" et si la cellule dans la plage B1:B10 est égal à 50​
= SOMMEPROD((A1:A10 = "xld")(B1:B10=50)(C1:C10))​
Les plages de cellules pour chaque matrice doivent etre identiques​
Le meme exemple que ci-dessus adapté au cas ou il y aurait des valeurs numeriques au format texte dans les plages B1:B10 et C1:C10​
= SOMMEPROD((A1:A10 = "xld")(B1:B101=50);C1:C10*1)​
Additionner les cellules de la plage A1:A10 , alors que chaque premier caractère commençe par une lettre​
{=SOMME(DROITE(A1:A10;(NBCAR(A1:A10)-1))*1)}​
Additionner les cellules de la plage B1:B10 , si les phrases contenues dans la plage A1:A10 contiennent le mot "rose"​
une solution donnée par Monique​
=SOMMEPROD(ESTNUM(CHERCHE("rose";A1:A10))*B1:B10)​
Additionner les cellules de la plage B1:B10 , si les cellules en A1:A10 sont vides​
=SOMMEPROD(ESTVIDE(A1:A10)*(B1:B10))​
une Demo de Monique & Celeda : Partager le bénéfice des entrées à une soirée de la manière suivante :​
De 1 à 50 entrées, 100% vont au propriétaire​
De 51 à 100, 50% pour propriétaire, 50% pour invité​
Dès 101 entrées, 33% pour propriétaire, 66% pour invité​
Lien supprimé
Afficher le sinus d'une valeur définie en degrés​
dans le fonction SIN(nombre) , nombre représente l'angle exprimé en radians .​
il faut dont multiplier la valeur par PI()/180 ou utiliser la fonction RADIANS pour la convertir en radians​
=SIN(RADIANS(30))​
=SIN(30*PI()/180)​
Convertir des degrés décimaux en d mn s , une solution donnée par Phibou​
en A1 : La valeur degré en décimal​
en B1 : =ENT(A1)​
en B2 : =ENT(60*(A1-B1))​
en B3 : =3600(A1-B1-1/60B2)​
Dans la cellule finale : =B1 & " degrés " & B2&" minutes " & B3 &" secondes"​
Afficher la racine Niéme d'un nombre​
Par exemple : si la cellule A1 contient la valeur 27 , la formule ci-dessous renvoie 3​
=A1^(1/3)​
Afficher la Valeur absolue d'un nombre ( nombre sans son signe )​
=ABS(-10)​
Cet exemple renvoie le résultat : 10​
Additionner les valeurs absolues de la plage A1:A5 : ( sans tenir compte du signe des nombres )​
=SOMMEPROD(ABS(A1:A5))​
Retrouver le plus grand diviseur commun d'une plage de cellules​
=PGCD(A1:A5)​
Retrouver le plus petit multiple commun d'une plage de cellules​
=PPCM(A1:A5)​
Renvoie une valeur arrondie au nombre entier IMPAIR le plus proche en s'éloignant de zéro​
=IMPAIR(A1)​
Renvoie une valeur arrondie au nombre entier PAIR le plus proche en s'éloignant de zéro​
=PAIR(A1)​
Additionner toutes les valeurs comprises entre 10 et 50 ( bornes comprises )​
= SOMME.SI($A1:$A100;">="&10)-SOMME.SI($A1:$A100;">"&50)​
Additionner les données de la plage A1:A10 , en plafonnant le résultat maxi à 800​
=MIN(800;SOMME(A1:A10))​
Insérer dans une colonne une suite de nombre croissants qui évolue toutes les 11 lignes​
Placez la formule en A1 puis utilisez la poignée de recopie vers le bas​
=ARRONDI.SUP(LIGNE(A1)/11;0)​
=ENT((LIGNE()-1)/11)+1​
Réduire un nombre de 2 chiffres en faisant l'addition de ses 2 chiffres et le refaire jusqu'à ce que ce nombre n'ait plus qu'un seul chiffre.​
Exemple 84 donne 8+4 =12 qu'il faut encore reduire à 1+2 =3.​
Des solutions données par Phibou​
=SI(MOD(A1;9)=0;9;MOD(A1;9))​
=MOD(A1;9)+9*(MOD(A1;9)=0​
Déterminer la valeur d'abscisse au croisement entre 2 courbes d'un graphique​
=(ORDONNEE.ORIGINE(A1:A2;C1:C2)-ORDONNEE.ORIGINE(B1:B2;C1:C2))/(PENTE(B1:B2;C1:C2)-PENTE(A1:A2;C1:C2))​
Les ordonnées Y sont dans les colonnes A et B​
Les abscisses X sont dans la colonne C​
Vérifier si la valeur de la cellule A1 est un multiple de 5​
=SI(MOD(A1;5)=0;"VRAI";"FAUX")​
Une autre possibilité qui renvoie 1 s'il s'agit d'un multiple et 0 dans le cas contraire​
=(MOD(B2;A2)=0)*1​
Calculer la racine carré de la somme des différences de valeurs au carré ( auteur : Phibou )​
=RACINE(SOMMEPROD((A1:A20-B1:B20)^2))​
Retrouver l'angle (en degrés) à partir de la valeur de la tangeante​
(exemple tangeante=1 renvoie 45 degrés)​
=ATAN(1)*180/PI()​

  • Scientifiques
    Convertir une valeur décimale en binaire (macro complémentaire de l'utilitaire d'analyse)​
    =DECBIN(A1)​
    Tester l'égalité de deux nombres​
    Renvoie 1 si les arguments sont égaux , sinon renvoie 0.​
    =DELTA(A1;B1)​
    Renvoyer le nombre de combinaisons possibles , en fonction de l'argument choisi​
    exemple nombre de combinaisons pour former des groupes de 3 pieces sur un ensemble total de 10 pièces​
    =COMBIN(10;3)​
    Convertir un nombre d'une unité à une autre unité.​
    La synthaxe : CONVERT(nombre;de_unité;à_unité)​
    Par exemple convertir 68 degrés Fahrenheit en degrés Celsius (20)​
    =CONVERT(68; "F"; "C")​
Consultez l'aide en ligne Excel pour visualiser les différentes unité de mesures utilisables​
La macro complémentaire Utilitaire d'analyse doit etre activée :​
Menu Outils​
Macros complémentaires.​
Cochez la case "Utilitaire d'analyse"​
Cliquez sur OK.​


  • Les Dates et les Heures
    Généralité sur les dates dans les formules Excel​
    Pour Excel, 1 = 1 jour = 24 heures​
    Renvoie le jour de la semaine pour une date spécifiiée dans la cellule A1 : Dimanche=1 , Samedi=7​
    =JOURSEM(A1)​
    Renvoie le jour de la semaine pour une date spécifiiée dans la cellule A1 : Lundi=1 , Dimanche=7​
    =JOURSEM(A1;2)​
    Renvoie le jour de la semaine pour une date spécifiiée dans la cellule A1 : Lundi=0 , Dimanche=6​
    =JOURSEM(A1;3)​
    Afficher le numéro du jour ( pour la date d'aujourd'hui )​
    =AUJOURDHUI()-DATE(ANNEE(AUJOURDHUI());1;0)​
    Effectuer la somme des valeurs de la plage B1:B10 , quand la date (plage A1:A10) est comprise entre le 01.01.04 et le 31.12.04​
    {=SOMME((B1:B10)(A1:A10>=DATEVAL("01/01/04"))(A1:A10<=DATEVAL("31/12/04")))}​
    Soustraire des heures qui sont au format "20h15"​
    =CNUM(SUBSTITUE(A2;"h";":"))-CNUM(SUBSTITUE(A1;"h";":"))​
    Afficher le nombre de jours dans un mois , pour une date définie dans la cellule A1​
    =JOUR(DATE(ANNEE(A1);MOIS(A1)+1;0))​
    Afficher la date du jour au format texte​
    =TEXTE(AUJOURDHUI(); "jjjj jj mmmm aaaa")​
    Convertir un numéro de mois (saisi dans la cellule A1) en nom de mois​
    =TEXTE("1/"&A1;"mmmm")​
    Calculer le temps écoulé , pour des heures saisies en A1(début) et A2(fin)​
    =MOD(A2-A1;1)​
Les cellules A1 , A2 et celle contenant la formule doivent etre au format [hh]:mm​

Télécharger la démo Cathy, Monique et Celeda pour avoir de nombreux exemples et commentaires sur les dates et les heures​

Calendrier Jour Partiel : Une démo de Monique et Celeda​
Afficher dans une cellule , le nombre de jours ouvrés d'une année en déduisant les jours fériés ( cas de la fonction JOUR.SOUVRES ) mais en plus les jours de temps partiel d'une personne​
Lien supprimé

Extraire le nombre de journées de 8 heures , pour un total d'heures saisi dans la cellule A1 au format [hh]:mm​
=ENT(A1*24/8)​
Et pour renvoyer le restant d'heures​
=((A124/8)-ENT(A124/8))*8​

Transformer une date qui est sous la forme 1965.05.26 , par 26.05.1965​
=DROITE(A1;2)&"."&STXT(A1;6;2)&"."&GAUCHE(A1;4)​

Afficher Vrai si la date dans la cellule A1 est un jour de Week end , sinon renvoie Faux​
=JOURSEM(A1;2)>5​

Calculer le temps écoulé entre 2 dates ( en années , mois et jours )​
Exemple pour calculer l'age d'une personne dont la date de naissance est saisi dans la cellule A1​
=DATEDIF(A1;AUJOURDHUI();"y")&" ans , "&DATEDIF(A1;AUJOURDHUI();"Ym")&" mois et "&DATEDIF(A1;AUJOURDHUI();"Md")&" jours"​

Vérifier si la date saisie dans la cellule A1 correspond au jour anniversaire (Aujourdui )​
=SI(ET(DATEDIF(A1;AUJOURDHUI();"ym")=0;DATEDIF(A1;AUJOURDHUI();"md")=0);"Oui";"Non")​

Controler si une personne , dont l'age est saisi dans la cellule A1 , est majeure​
=SI(DATEDIF(A1;AUJOURDHUI();"y")>=18;"Majeur";"Mineur")​

Calculer l'age en tenant compte du mois et de l'année de naissance (une solution d'André)​
La date est saisie dans la cellule A1​
=ANNEE(AUJOURDHUI()-A1)-1900&" ans "&MOIS(AUJOURDHUI()+1-A1)-1&" mois "​

Afficher la moyenne du temps passé en mm:ss , avec en A1 le temps total en secondes et en A2 le nb d'occurrences​
=A1/24/60/60/A2​
La cellule contenant la formule doit etre au format [mm]:ss​

Afficher le numéro de semaine pour une date saisie en A1​
=ENT(MOD(ENT((A1-2)/7)+0,6;52+5/28))+1​

Fontion NO.SEMAINE : Gestion des numéros de semaine qui sont décalés en 2005​

Convertir des secondes saisies dans la cellule A1 , en heures ( la cellule contenant la formule au format hh:mm:ss )​
=A1/60/60/24​
Une autre solution ( la cellule contenant la formule toujours au format hh:mm:ss )​
=A1*"0:0:1"​

Convertir en heure une somme de minutes​
=SOMME(A1:A10)/1440​
(Appliquer le format heure à la cellule de résultat )​

Convertir des minutes en heures​

Compter le nombre de dates correspondant au mois de Février(2) dans la plage de cellules A1:A10​
=SOMMEPROD((MOIS(A1:A10) = 2)*1)​
Une autre solution donnée par Monique pour le comptage du mois de Janvier dans une plage pouvant​
contenir des cellules vides , car Pour Excel, =MOIS ( cellule vide ) = 1​
= SOMMEPROD((A$1:A$20<>"")*(MOIS(A$1:A$20) =1))​

Afficher le premier Lundi du mois, pour une date saisie dans la cellule A1​
=A1-JOUR(A1)+9-JOURSEM(A1-JOUR(A1))​
Pensez à adapter le format de la cellule contenant la formule​

Afficher le dernier jour du mois , pour une date saisie dans la cellule A1​
=FIN.MOIS(DATE(TEXTE(A1;"aaaa");TEXTE(A1;"mm");1);0)​

Une autre solution proposée par Monique​
=DATE(ANNEE(A1);MOIS(A1)+1;0)​

Afficher le dernier Lundi du mois, pour une date saisie dans la cellule A1​
=DATE(ANNEE(A1);MOIS(A1)+1;2)-JOURSEM(DATE(ANNEE(A1);MOIS(A1)+1;))​

Afficher le dernier jour du mois en cours​
=FIN.MOIS(AUJOURDHUI();0)​

Compter le nombre de semaines entre 2 dates , plusieurs solutions données par Monique​

Afficher le numero de trimestre , pour une date saisie en A1​
="TRIMESTRE "&ENT((MOIS(A1)+2)/3)​

Vérifier si une date saisie dans la cellule A1 appartient à une année bissextile​
=SI(MOIS(DATE(ANNEE(A1);2;29))=2;"Bissextile";"Non bissextile")​

Créer et gérer un agenda , encore une superbe demo de Monique​
Lien supprimé

Convertir un nombre d'heures spécifique en journée ( 7,4h=1j )​
=(ENT(A1/7,4))/24​
Par exemple : 6,8 renvoie 0 et 7,6 renvoie 1 (appliquer le format ?[ hh] à la cellule contenant la formule )​

Pour multiplier des heures qui sont dans la cellule A1 (au format hh:mm ) par un nombre situé dans la cellule B1​
par exemple 01:30 x 2 = 3​
=A1B124​

Comment retrouver la date du mercredi de la semaine précédente​

Ajouter 30 minutes à une heure saisie en A1​
=A1+TEMPS(0;30;0)​

Convertir des heures décimales en heures minutes​
=A1/24​
La cellule contenant la formule doit etre au format [hh]:mm​
Si par exemple vous saisissez 2,5 dans la Cellule A1 , la cellule contenant la formule renvoie 02:30​

Convertir des heures minutes en décimales.​
Par exemple 10:30 devient 10,5​
=(A1-ENT(A1))*24​

Additionner des jours , des heures et des nombres pour obtenir des heures​

Arrondir à l'heure la plus proche ( 2:45 devient 3:00 )​
=ARRONDI(A1/(1/24);0)*(1/24)​

Mesurer le temps écoulé entre 2 dates au format jj.mm.aa hh:mm (une solution proposée par André )​
A1 : heure de départ​
A2 : heure d'arrivée​
Ces deux cellules au format jj.mm.aa hh:mm​
(ou jj/mm/aa hh:mm, suivant ta configuration)​
=ENT(A2-A1)&" j "&(HEURE(A2)<HEURE(A1))*24+HEURE(A2)-HEURE(A1)&" h"​

Ajouter ou oter un nombre de mois à une date spécifiée​
=MOIS.DECALER(A1;3)​
La date de départ est en A1. 3 correspond au nombre de mois. Une valeur de mois positive donne une date future, tandis qu'une valeur négative donne une date passée.​
La macro complémentaire "Utilitaire d'analyse" doit etre activée​
Pensez à appliquer un format date à la cellule contenant la formule​
Il est aussi possible d'utiliser la version anglaise de la fonction : EDATE .​
=EDATE(E1;3)​

Afficher le 3eme dimanche du mois de juin (Fêtes des Pères) :une solution de Jean Marie​
L'année est saisie en B1​
=("22/6/"&B1)-JOURSEM("1/6/"&B1;2)​

Oter 24 mois sur la date du jour (une solution de Jeannot45 )​
=DATE(ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI())-24;JOUR(AUJOURDHUI()))​

Additionner des valeurs en fonction d'un mois cible​
= SOMMEPROD((MOIS(Totaux!A1:A10)=D1)*(Totaux!B1:B10))​
Dans la feuille "Totaux" , la plage A1:A10 contient les dates "jj/mm/aaaa" et la plage B1:B10 contient les valeurs à additionner​
La Cellule D1 contient le numero du mois qui va servir à filtrer les données additionnées : 1 = Janvier , 2 = Février ...etc …​

Compter le nombre de dates différentes, et qui correspondent à un Dimanche, dans la plage A1:A5​
= SOMME(SI(JOURSEM(A1:A5)= 1;1/NB.SI(A1:A5;A1:A5)))​
Fonction à valider par Ctrl + Maj + Entree . Remarque : la formule accepte des cellules vides dans la Plage A1:A5​

Compter le nombre de dates différentes, et qui correspondent à un Samedi, dans la plage A1:A5​
= SOMME(SI(JOURSEM(A1:A5)= 7;SI(A1:A5>0;1/NB.SI(A1:A5;A1:A5))))​
Fonction à valider par Ctrl + Maj + Entrée​
Cette formule ne compte pas les cellules vides (Une cellule vide correspond au samedi 00-01-1900)​

Additionner les heures qui correspondent à des dimanches et appliquer un coefficient multiplicateur 2 au résultat​
Les jours en A1:A10 , Les heures en B1:B10​
=SOMMEPROD((JOURSEM(A1:A10)=1)B1:B10)2​

Retrouver le mois en fonction du numéro de semaine et de l'index du jour:​
en paramètres :​
L'annee dans la cellule A1​
Le numéro de semaine dans la cellule A2​
L'index du jour dans la cellule A3 : 'Lundi= 0 , Mardi=1 , Mercredi=2 ...etc...​
=TEXTE(DATE(A1;1;3)-JOURSEM(DATE(A1;1;3))-5+(7*A2)+A3;"mmmm")​

Afficher le 1er jour ouvré suivant (une solution donnée par André)​
=SERIE.JOUR.OUVRE(A1;1;ferie)​
ferie est une plage nommée (menu Insertion / Nom / Définir) qui reprend la liste des jours fériés.​
Il faut activer l'Utilitaire d'Analyse sous les Macros complémentaires du menu Outils, sur tous les PC​

Compter le nombre de mois complets entre 2 dates​
=MAX(0;MOD(MOIS(B1)-MOIS(A1);12)-1+(JOUR(A1)=1)+(DATE(ANNEE(B1);MOIS(B1)+1;0)=B1))​
Une solution de Monique​
=(ANNEE(B1)-ANNEE(A1))*12+MOIS(B1)-MOIS(A1)+(JOUR(A1)=1)-(JOUR(B1+1)>1)​
Une autre solution par André​
=MAX(0;(ANNEE(B1+1)-ANNEE(A1-1))*12+MOIS(B1+1)-MOIS(A1-1)-1)​


  • Recherches et matrices
    Renvoyer la dernière valeur numérique d'une colonne contenant des cellules vides: une solution trouvée par André​
Renvoyer la dernière donnée saisie dans la colonne A ( ne fonctionne pas s'il y a des cellules vides )​
=INDIRECT(ADRESSE(NBVAL(A:A);1))​
Une autre solution donnée par Jean Marie , qui fonctionne avec des cellules vides​
( formule matricielle à valider par Ctrl+Maj+Entree )​
=INDEX(A1:A1000;MAX(NON(ESTVIDE(A1:A1000))*LIGNE(A1:A1000));1)​
Deux autres solutions proposées par Monique​
Pour des valeurs numériques​
=RECHERCHE(9^9;A:A)​
Pour des valeurs alphanumériques​
=RECHERCHE("zzz";A:A)​

Renvoyer le numéro de la derniere ligne non vide , dans la colonne A ( valider par Ctrl+Maj+Entree )​
=MAX(NON(ESTVIDE(A1:A100))*LIGNE(A1:A100))​
la formule fonctionne s'il y a des cellules vides dans la plage​

Afficher la position d'une valeur recherchée (exemple : "xld" ) dans la plage cible A1:A10​
=EQUIV("xld";A1:A10;0)​
Si le mot recherché est dans la cellule A3 , le résultat renvoyé sera 3​
La valeur recherchée peut etre numérique ou du texte​
La spécificité des recherches de texte :​
La fonction EQUIV n'est pas sensible aux majuscules ou minuscules .​
Si l'on ne connaît que le début du mot recherché , il est possible d'insérer des valeurs génériques (une astérisque ou un point d'interrogation) . Par exemple :​
=EQUIV("xl*";A1:A10;0)​
L'astérisque est équivalent à une séquence de caractères, le point d'interrogation à un caractère unique.​

Insérer un lien hypertexte dans la cellule pour ouvrir un autre document​
=LIEN_HYPERTEXTE("C:\test.xls";"Cliquez ici !")​
Si vous souhaitez créer un lien hypertexte vers un emplacement précis dans un document Microsoft Word, il faut utiliser un signet pour définir cet emplacement. L'exemple suivant crée un lien hypertexte vers le signet "Signet1" dans le document nommé monFichier.doc​
=LIEN_HYPERTEXTE("C:\monFichier.doc#Signet1";"Cliquez ici")​

Accéder à la cellule D10 dans la Feuil2 du meme classeur​
=LIEN_HYPERTEXTE("[monClasseur]Feuil2!D10";"Mon lien hypertexte")​

3 Remarques :​
Meme si tu restes dans le meme classeur , il faut preciser son nom .​

Si le nom de la feuille contient un espace , le nom doit etre encadré par des apostrophes dans la formule :​
=LIEN_HYPERTEXTE("[monClasseur.xls]'ma feuille'!D10";"Mon lien hypertexte")​

Si le nom du classeur est amené à changer dans le temps tu peux utiliser :​
=LIEN_HYPERTEXTE("["&STXT(CELLULE("nomfichier"); TROUVE("["; CELLULE("nomfichier"))+1;TROUVE("]"; CELLULE("nomfichier"))- TROUVE("["; CELLULE("nomfichier"))-1)&"]'ma feuille'!D10";"Mon lien hypertexte")​

Afficher la lettre de la colonne , pour la cellule contenant cette formule . Une solution donnée par Monique​
=GAUCHE(ADRESSE(1;COLONNE();4);NBCAR(ADRESSE(1;COLONNE();4))-1)​

Chercher la valeur "xld" dans la colonne gauche de la plage A1:D10 , et renvoyer la valeur située dans la même ligne et dans la 4eme colonne de la plage​
=RECHERCHEV("xld";A1:D10;4;0)​

Récuperer la valeur de la cellule A1 d'un classeur fermé , nommé "monFichier.xls"​
='C:\Documents and Settings\[monFichier.xls]Feuil1'!A1​

de la meme manière , Il est aussi possible de récupérer des infos dans un classeur placé sur le Web​
='Ce lien n'existe plus'!$A1​

Faire une RECHERCHEV dans un classeur fermé :​
Rechercher "XLD" dans la colonne A du classeur fermé et afficher la donnée de la colonne B​
=RECHERCHEV("XLD";'C:\[test.xls]Feuil1'!$A:$B;2;FAUX)​

Compter le nombre de caracteres à droite de l'Arobas "@"​
=NBCAR(A1)-TROUVE("@";A1;1)​

Afficher de façon aléatoire une des données se trouvant dans la plage A1:A10​
=INDEX(A1:A10;ENT(ALEA()*10+1);1)​

Afficher de façon aléatoire une des données contenue dans la formule​
=CHOISIR(ENT(ALEA()*6+1);"Valeur1";"Valeur2";"Valeur3";"Valeur4";"Valeur5";"Valeur6")​

Insérer une variable dans une formule​
Dans l'exemple ci dessous , si la cellule B1=5 , la formule effectuera la somme de la plabe A1:A5​
=SOMME(INDIRECT("A1:A"&B1))​

Retrouver un mot de 2 caractères dans plage B1:B10, une astuce de Phibou​
=INDIRECT("B" & SOMME(SI((NBCAR(B1:B10)>0)+(NBCAR(B1:B10)<3)=2;LIGNE(B1:B10);"")))​
Fonction à valider par Ctrl + Maj + Entrée​

Récuperer une donnée sur 2 , dans la colonne A , (une solution de Monique )​
pour commencer la recherche dans la 1ere ligne de la colonne A:​
=DECALER($A$1;(LIGNE()-1)*2;0)​
pour commencer la recherche dans 2eme ligne de la colonne A :​
=DECALER($A$1;LIGNE()*2-1;0)​
Dans les 2 cas , étendre les formules dans la colonne de résultat​

Additionner une colonne sur 2 dans la ligne 1​
en commençant dans la premiere colonne :​
=SOMMEPROD(1:1*MOD(COLONNE($1:$1);2))​
en commençant dans la deuxieme colonne :​
=SOMMEPROD(1:1*MOD(COLONNE($1:$1)-1;2))​

Additionner une cellule sur 10 dans la plage A1:A100​
=SOMMEPROD((MOD(LIGNE(A1:A100);10)=0)*1;A1:A100)​

Extraire les données d'un tableau, en se basant sur les étiquettes automatiques , une Démo d'André​
Lien supprimé

Renvoyer le rang de la cellule A2 dans la plage A1:A10 (équivalent de la position dans la plage apres un tri )​
=RANG(A2;A1:A10;0)​
Ne fonctionne que pour des données numériques​
Remplacer 0 par 1 pour utiliser l'ordre décroissant​

Afficher une série de données dans le sens inverse​
Les données de base sont dans la plage A1:A10 .Saisissez la formule dans chaque cellule de la plage B1:B10​
= DECALER($A$1;10 - LIGNE() ; 0 )​
Une autre solution par André​
= SI(A1="";"";INDIRECT("A"&NBVAL(A:A) - LIGNE() + 1))​

Trouver la valeur maxi (dans la plage C2:C5) attribuée à une donnée "XLD" se trouvant dans la plage B2:B5​
La donnée "XLD" peut apparaitre plusieurs fois parmi d'autres données , et affecté de valeurs différentes​
=MAX ( ( B2:B5 = "XLD" ) * C2:C5)​
valider par Ctrl+Shift+Enter​

Trouver la valeur la plus élevée et qui commence par 99 , dans la plage A1:A10​
=MAX ( ( GAUCHE (A1:A10 ; 2) = "99" ) * A1:A10 )​
à valider par Ctrl+Shift+Enter ( source excelTip )​

Afficher des réponses multiples , lors d'une recherche dans un autre classeur :​
dans un classeur il y a des données: nom prenom​
dans l'autre classeur nommé "interrogation" on fait une recherche pour afficher tous les noms qui ont le meme prénom .​
Lien supprimé

Afficher la dernière donnée texte dans la plage de cellules A1:A10​
= RECHERCHE (2;1 / NON ( ( A1:A10 = "" ) + ESTNUM (A1:A10) ) ; A1:A10 )​

Retrouver les données de la plage A1:A10 qui sont aussi dans la plage B1:B10​
Saisir la formule en C1 par exemple , puis l'étirer vers le bas​
=SI(NB.SI($B$1:$B$10;A1)>0;A1;"")​

Récapituler dans une colonne ,sans vide, les données contenues dans des cellules discontinues de la plage ("A1:E20")​
Lien supprimé
Lien supprimé

Ne prendre en compte que les lignes impaires pour additionner les valeurs de la plage A1:A10 , et si la plage B1:B10 = "x"​
= SOMMEPROD ( ( ( MOD ( LIGNE (1:10) ; 2 ) = 1 ) * A1:A10 ) * ( B1:B10="x" ) )​
et pour les lignes paires​
= SOMMEPROD ( ( ( MOD ( LIGNE (1:10) ; 2 ) = 0 ) * A1:A10 ) * ( B1:B10="x" ) )​

Ne prendre en compte que les lignes impaires pour compter le nombre de valeur >10 dans la plage A1:A10 , et si la plage B1:B10 = "x"​
= SOMMEPROD ( ( ( MOD ( LIGNE (1:10) ; 2 ) = 1 ) * A1:A10 > 10 ) * ( B1:B10="x" ) )​

Conseils pour les recherches à critères multiples : une démo d'Alain Vallon​
Lien supprimé

Compter le nombre de lignes dans un plage de cellules​
=LIGNES($A$1:A5)​

Trouver le Nieme mot dans une phrase​
La phrase est saisie en A1. En B1, saisisssez la position du mot à extraire.​
=SI(B1>NBCAR(A1)-NBCAR(SUBSTITUE(A1;" ";""));DROITE(A1;NBCAR(A1)-TROUVE("^^";SUBSTITUE(A1;" ";"^^";NBCAR(A1)-NBCAR(SUBSTITUE(A1;" ";"")))));SI(B1=1;STXT(A1;1;TROUVE("^^";SUBSTITUE(A1;" ";"^^";1))-1);STXT(A1;TROUVE("^^";SUBSTITUE(A1;" ";"^^";B1-1))+1;TROUVE("^^";SUBSTITUE(A1;" ";"^^";B1))-TROUVE("^^";SUBSTITUE(A1;" ";"^^";B1-1))-1)))​

Transposer une colonne en lignes.​
Si la première valeur est en A1, placez par exemple cette formule en B1​
=INDIRECT("A"&COLONNE()-1)​
Utilisez ensuite les poignées de recopie vers la droite​


  • Informations
    La fonction "INFO" permet d'afficher des informations sur l'environnement d'exploitation​
    Exemple : La version du système d'exploitation utilisé​
    =INFO("versionse")​
    La version d'Excel​
    =INFO("version")​
Généralités sur la fonction CELLULE​
Renvoie des informations sur la mise en forme, la position ou le contenu de la cellule supérieure gauche d'une référence.​
( voir l'aide en ligne Excel pour plus de détails : ci-dessous quelques exemples d'utilisation )​

Afficher le chemin et le nom du classeur​
=CELLULE("filename")​
Remarque : le résultat est vide si le classeur n'est pas sauvegardé​

Afficher le répertoire du classeur​
=GAUCHE(CELLULE("filename");CHERCHE("[";CELLULE("filename");1)-2)​
Remarque : le résultat est vide si le classeur n'est pas sauvegardé​

Créer un lien hypertexte pour ouvrir l'explorateur windows sur le répertoire contenant le classeur​
=LIEN_HYPERTEXTE(GAUCHE(CELLULE("filename");CHERCHE("[";CELLULE("filename");1)-2);"Lien vers le répertoire")​

Afficher le nom du classeur et de la feuille​
=STXT(CELLULE("filename";A1);TROUVE("[";CELLULE("filename";A1));300)​
Remarque : le résultat est vide si le classeur n'est pas sauvegardé​

Afficher le nom du classeur dans une cellule​

Afficher le nom de l'onglet dans une cellule : des solutions données par André et Gérard​

Compter le nombre de feuilles dans le classeur , en utilisant les anciennes macros XL4​
=NBVAL(LIRE.CLASSEUR(1;"Classeur1"))​

Afficher des smileys différents en fonction de la valeur de la cellule A1​
( la police de caractère doit etre de type "Wingdings" , dans la cellule contenant la formule )​
=SI(A1>=0;"J";"L")​

Utiliser les anciennes macro XL4​

Un exemple d'utilisation : Compter le nombre de feuilles dans un classeur​
Sélectionnes la cellule A1​
ensuite​
Menu Insertion​
Nom​
Définir​
Dans le champ "Noms dans le classeur" , saisies TEST1​
Dans le champ "Fait référence à" , saisies la formule​
=NBVAL(LIRE.CLASSEUR(1;"Classeur1"))​
Cliques sur Entrée pour valider​
Ensuite , dans la cellule A1 tu saisies​
=TEST1​

Afficher la lettre d'une colonne pour un numéro de colonne saisi dans la cellule A1​
=GAUCHE(ADRESSE(1;A1;4);NBCAR(ADRESSE(1;A1;4))-1)​

Incrémentation répétitive des références d'une série de cellules avec décalage de colonnes et de lignes​
Une démo d'André​
Lien supprimé


  • Logique
    Utiliser plusieurs conditions dans la fonction Si :​
    Dans cet exemple , la formule renvoie "Faux" si toutes les cellules A1 ,B1 et C1 sont vides et Vrai si au moins une des cellules est non vide​
    = SI(ET ( A1 = "" ; B1="" ;C1 = "" ) ; "Faux";"Vrai")​
Vérifier si la valeur de la cellule A1 est comprise entre 10 et 20 ( renvoie Vrai ou Faux )​
=ET(A1>=10;A1<=20)​

Renvoyer 0 si le résultat d'une formule est négatif​
=SI(SOMME(A1:A10)>0;SOMME(A1:A10);0)​
Une autre solution​
=MAX(0;SOMME(A1:A10))​

Vérifier si la valeur de la cellule A1 est comprise entre 10 et 20 , sinon indiquer la position en dehors de la plage​
=SI(A1<10;"AU DESSOUS";SI(ET(A1>=10;A1<=20);"DANS PLAGE";"AU DESSUS"))​

La fonction OU​
Renvoie VRAI si au moins un argument est VRAI , et FAUX si tous les arguments sont FAUX​
30 conditions peuvent etre testées​
Cet exemple affiche le texte "OK" si la cellule A1=10 ou si la cellule A2=20​
=SI(OU(A1=10;A2=20);"VRAI";"")​

Afficher la valeur 10 si la cellule A1 contient le texte "Option1" , ou la valeur 20 si A1 contient "Option2"​
Si une autre donnée est saisie en A1 , le résultat affiché sera une cellule vide​
= SI(A1= "Option1";10;SI(A1= "Option2";20;""))​
Cet autre exemple affiche 0 si une autre donnée est saisie en A1​
= (A1= "Option1")10+(A1= "Option2")20​

Vérifier si 2 plages de cellules (A1:A10 et B1:B10) sont identiques​
La formule renvoie VRAI si les données des 2 plages sont identiques​
=ET(A1:A10=B1:B10)​

Vérifier si la cellule A1 est vide (pour renvoyer Vrai si c'est le cas)​
=ESTVIDE(A1)​
Vérifier si la cellule A1 est non vide(pour renvoyer Vrai si c'est le cas)​
=NON(ESTVIDE(A1))​


  • Divers
    Pour afficher les formules dans les cellules :​
    Menu Outils​
    Options​
    onglet Affichage​
    cochez l'option Formules​
D'autres solutions pour afficher les formule dans les cellules :​
Ctrl + " ( Ctrl + guillemet )​
Appliquez le meme raccourci clavier pour réafficher les résultats à la place des formules .​
Il est aussi possible de précéder la formule d'une apostrophe (ou d'un espace) pour qu'elle s'affiche dans la cellule :​
'=A1+A2​

La signification des codes d'erreur dans les résultats de formules​



  1. NUL! Survient lorsque vous spécifiez une intersection de deux zones qui, en réalité, ne se coupent pas.
  2. DIV/0! Survient lorsqu'un nombre est divisé par zéro (0).
  3. VALEUR!
  4. REF! Survient lorsque les coordonnées d'une cellule ne sont pas valide.
  5. NOM? Survient lorsque l'application ne reconnaît pas le texte dans une formule.
  6. NOMBRE! Survient lorsqu'une formule ou une fonction contient des valeurs numériques non valides.
  7. N/A Survient lorsqu'une valeur n'est pas disponible pour une fonction ou une formule.
Insérer un commentaire dans la formule​
=A1*A2+N("mon commentaire")​

Remplacer une formule par son resultat :​
Sélectionnez la formule dans la barre de formules puis Cliquez sur la touche F9​

Une autre solution ( donnée par André ) :​
avoir sélectionné la cellule : F2 / F9 / Entrée​

Pour le remplacement lors de la saisie de la formule : F9 / Entrée​

Effacer une plage de cellules mais pas les formules​
Sélectionnez la plage de cellules​
appuyez sur la touche F5 du clavier​
cliquez sur le bouton "Cellules"​
Sélectionnez "Constantes"​
Cochez le ou les types de données à supprimer ( texte , nombre ...)​
cliquez sur "Ok"​
les cellules correspondantes sont sélectionnées​
Vous pouvez alors appuyer sur la touche "Suppr" du clavier​

Afficher la boite de dialogue "Arguments de la fonction" pendant la saisie d'une formule .​
Par exemple, saisissez dans une cellule :​
=NBVAL​
Ensuite appuyez sur la combinaison de touches Ctrl + A​

Afficher les argument d'une fonction dans la barre de formules .​
Par exemple, saisissez dans une cellule :​
=NBVAL​
Ensuite appuyez sur la combinaison de touches Ctrl + Shift + A​

Les audits de formules

Afficher tous les antécédents de formules

Sub afficherTousAntecedents()​

Dim cell As Range​

For Each cell In Sheets("Feuil1").usedRange​

If cell.hasFormula Then cell.showPrecedents​

Next​

End Sub​

Enlever les fleches d'antecedent liées à la cellule F7

Sub enleverUnAntecedent()​

Range("F7").showPrecedents Remove:=True​

End Sub​

Enlever toutes les fleches dans la feuille

Sub enleverTousLesAntecedents()​

activeSheet.clearArrows​

End Sub​


Récupérer la valeur de chaque cellule constituant une formule saisie en A1

Sub afficherValeursElementsFormule()​

Dim X As Range​

For Each X In Range("A1").directPrecedents.Cells​

msgBox "Cellule " & X.Address & " : " & X​

Next X​

End Sub​



Les répertoires et les fichiers

Creer un repertoire

Sub creerUndossier()​

mkDir "c:\Mes Documents\Excel"​

End Sub​


Modifier le répertoire par défaut dans Excel

L'équivalent de Meu Outils / Options / Général / Dossier par défaut​

Application.defaultFilePath = "C:\Documents and Settings\michel\excel"​


Les proprietes d'un repertoire

Sub propriétésRepertoire()​

Dim Cible As Object, Valeur As Object​

Dim Resultat As String​

Set Cible = createObject("Scripting.fileSystemObject")​

Set Valeur = Cible.getfolder("D:\dossier\general\excel") 'adpater le chemin​

Resultat = "Chemin : " & Valeur.parentFolder & Chr(10) & _​

"Date creation : " & Valeur.dateCreated & Chr(10) & _​

"dermiere modification : " & Valeur.dateLastModified & Chr(10) & _​

"taille repertoire : " & Valeur.Size & " octets . "​

msgBox Resultat​

End Sub​

Compter le nombre de fichiers dans un repertoire

Sub nombreFichiers()​

Dim fso As Object, Dossier As Object​

Set fso = createObject("Scripting.fileSystemObject")​

Set Dossier = fso.getFolder("C:\Mes Documents")​

msgBox "Il y a " & Dossier.files.Count & " fichiers dans le dossier . "​

End Sub​

Compter le nombre de classeurs Excel dans un répertoire

Sub nbClasseurs()​

Dim Chemin As String , D as String​

Dim Lig As Integer​

Chemin = "C:\Documents and Settings\michel\dossier\*.xls"​

D = Dir(Chemin)​

While D <> ""​

Lig = Lig + 1​

D = Dir​

Wend​

msgBox "nombre total de classeurs : " & Lig​

End Sub​


Une autre méthode : Compter le nombre de fichiers XLS dans un répertoire​

( à partir de Win2000)​


Sub nombreFichiers_XLS_Repertoire()​

Dim objWMIService As Object, colFiles As Object​

Dim strComputer As String​

Dim Chemin As String, Lettre As String​

Chemin = "C:\Documents and Settings\michel\"​

Lettre = Left(Chemin, 2)​

Chemin = Application.worksheetFunction.Substitute(Chemin, "\", "\\")​

Chemin = Mid(Chemin, 3)​

strComputer = "."​

Set objWMIService = getObject("winmgmts:\\" & strComputer & "\root\cimv2")​

Set colFiles = objWMIService. _​

execQuery("SELECT * FROM CIM_dataFile WHERE Path = '" & Chemin & "' " & _​

"AND Drive = '" & Lettre & "' AND Extension = 'xls'")​

msgBox "nombre de fichiers xls : " & colFiles.Count​

End Sub​

Lister le nom des dosssiers dans un répertoire précis

Sub listeNomsDossiers()​

Dim x As String, Chemin As String​

Chemin = "C:\Documents and Settings\michel\dossier\general\excel\"​

x = Dir(Chemin & ".", vbDirectory Or vbHidden Or vbSystem)​

Do Until x = ""​

If Asc(x) <> 46 Then​

If getAttr(Chemin & x) = vbDirectory Then msgBox Chemin & x​

End If​

x = Dir()​

Loop​

End Sub​

Compter le nombre de sous repertoires dans un dossier


Lister les sous répertoires vides d'un dossier



  • Lister les fichiers d'un répertoire
    Sub recupererListeFichiersDansRepertoire()​
    Dim X As Integer, nbFichiers As Integer​
    Dim Tableau() As String​
    Dim Direction As String​
    Direction = Dir("C:\reperetoire\*.xls") 'adapter chemin​
    'Direction = Dir("C:\repertoire\.") 'pour recuperer tous les types de fichiers​
    Do While Len(Direction) > 0​
    nbFichiers = nbFichiers + 1​
    reDim Preserve Tableau(1 To nbFichiers)​
    Tableau(nbFichiers) = Direction​
    Direction = Dir()​
    Loop​
    If nbFichiers > 0 Then​
    msgBox "il y a " & nbFichiers & " fichiers dans le repertoire . "​
    'pour afficher tous les noms de fichiers​
    For X = 1 To nbFichiers​
    Cells(X, 1) = Tableau(X)​
    Next X​
    End If​
    End Sub​
  • Lister tous les classeurs d'un dossier et de ses sous dossiers : une démo de Didier , myDearFriend
    Lien supprimé
  • Afficher les propriétés de tous les fichiers d'un répertoire, sans les ouvrir
    Sub propriétésFichiers()​
    'Ce lien n'existe plus
    'Necessite d'activer la reference Microsoft Shell Controls and Automation​
    Dim objShell As Shell​
    Dim objFolder As Folder​
    Dim strFileName As folderItem​
    Dim Resultat As String​
    Dim i As Byte​
    Set objShell = createObject("Shell.Application")​
    Set objFolder = objShell.nameSpace("C:\Documents and Settings\michel\dossier\excel") 'repertoire cible​
    For Each strFileName In objFolder.Items 'boucle sur tous les elements du repertoire​
    If strFileName.isFolder = False Then 'pour que les sous dosssiers ne soient pas pris en comptes​
    Resultat = ""​
    For i = 0 To 34​
    Resultat = Resultat & objFolder.getDetailsOf(strFileName, i) & vbLf​
    Next​
    msgBox Resultat​
    End If​
    Next​
    End Sub​
    Un autre exemple​
  • Informations sur les bugs possibles lors de l'utilisation de la méthode fileSearch
  • Rechercher un classeur dans un répertoire et ses sous répertoires , en fonction d'un mot clé
    Utilisation de la fonction createObject("Scripting.fileSystemObject")​
  • Chercher un classeur et recuperer son chemin par getOpenFileName
    Lien supprimé
    Lien supprimé
  • Choisir un répertoire , Lister ses fichiers et créer un lien hypertexte vers chacun d'entre eux
    Lien supprimé
  • Un autre exemple pour lister tous les fichiers d'un répertoire . Une démo de Didier (myDearFriend)
    Lien supprimé
  • Masquer ou démasquer un répertoire
    L'instruction fonctionne aussi directement pour un fichier​
    nota : L'instruction Settattr permet aussi de gérer l'attribut "Lecture seule"​
    Sub dossierMasque()​
    Setattr "C:\Documents and Settings\michel\dossier", 2​
    End Sub​
    Sub dossierNonMasque()​
    Setattr "C:\Documents and Settings\michel\dossier", 0​
    End Sub​
  • Afficher le chemin du classeur actif
    Sub cheminClasseur()​
    msgBox activeWorkbook.Path​
    End Sub​
  • Controler si un répertoire contient des fichiers .Le contrôle de présence des sous dossiers n'est pas pris en compte
    Sub controleRepertoire()​
    Dim Fso As Object​
    Dim Valeur As Long​
    Set Fso = createObject("Scripting.fileSystemObject")​
    Valeur = Fso.getFolder("C:\dossier").Size 'Adapter le chemin​
    If Valeur = 0 Then​
    msgBox "Le répertoire est vide . ", , "Message"​
    Else​
    msgBox "Le répertoire n'est pas vide . ", , "Message"​
    End If​
    End Sub​
    Un autre exemple qui prend en compte les fichiers cachés et les sous dossiers​
    Sub controlerSiUnRepertoireEstVide()​
    'prend en compte les fichiers cachés et les sous repertoires​
    Dim Repertoire As String, Valeur As String, Resultat As String​
    Repertoire = "C:\Documents and Settings\michel\dossier\general\excel\dossier"​
    'controle la présence de fichiers visibles et cachés​
    Resultat = Dir(Repertoire & "\*", vbHidden Or vbSystem)​
    'controle la présence de sous dossiers​
    Valeur = Dir(Repertoire & "\*", vbDirectory)​
    Do While Valeur <> ""​
    If Valeur <> "." And Valeur <> ".." Then​
    If (getAttr(Repertoire & "\" & Valeur) And vbDirectory) = vbDirectory _​
    Then Resultat = Resultat & Valeur​
    End If​
    Valeur = Dir​
    Loop​
    If Resultat <> "" Then​
    msgBox "Le répertoire est non vide ."​
    Else​
    msgBox "Le répertoire est vide ."​
    End If​
    End Sub​
  • Ouvrir l'explorateur Windows dans un répertoire défini
    Sub repertoireExplorateur1()​
    Shell "c:\windows\explorer.exe C:\Documents and Settings\excel", vbMaximizedFocus ' adapter le chemin​
    End Sub​
    Un autre exemple​
    Sub Test()​
    thisWorkbook.followHyperlink "C:\Documents and Settings\michel\dossier"​
    End Sub​
    Un autre exemple​
    Sub repertoireExplorateur2()​
    Dim IE As Object​
    Set IE = createObject("internetExplorer.Application")​
    IE.Navigate "C:\Documents and Settings\excel" ' adapter le chemin​
    IE.Visible = True​
    End Sub​
    Encore un autre exemple​
    Sub ouvrirExplorateurWindows()​
    'necessite d'activer reference Microsoft Shell Controls and Automation​
    Dim objShell As Shell​
    Set objShell = New Shell​
    objShell.Explore ("C:\Documents and Settings\michel\dossier\general\excel")​
    End Sub​
  • Vérifier l'existence d'un dossier
  • Vérifier l'existence d'un fichier
    Sub controleSiFichierExiste()​
    If Dir("C:\monFichier.txt", vbHidden) <> "" Then msgBox "Le fichier existe"​
    'vbHidden pour rechercher aussi les fichiers cachés​
    End Sub​
  • Utiliser la propriété Filedialog pour ouvrir une boite de dialogue , sélectionner un dossier puis en afficher le chemin
    Sub selectionRepertoire_afficherChemin()​
    'à partir d'Excel2002​
    Dim Repertoire As fileDialog​
    Set Repertoire = Application.fileDialog(msoFileDialogFolderPicker)​
    Repertoire.Show​
    msgBox Repertoire.selectedItems(1)​
    End Sub​
  • Retrouver le chemin d'un dossier dont on ne connaît pas l'emplacement sur le disque
  • Copier le contenu d'un dossier et tous les sous dossiers
  • Lister les liens et raccourcis contenus dans le dossier spécial des favoris
    Sub listerLiensDansFavoris()​
    'necessite d'activer la reference Microsoft Shell Controls and Automation​
    Const Cible = &H6 'Favorites​
    Dim objShell As Shell32.Shell​
    Dim objFolder As Shell32.Folder​
    Dim colItems As Shell32.folderItems​
    Dim objItem As Shell32.folderItem​
    Dim i As Integer​
    Set objShell = createObject("Shell.Application")​
    Set objFolder = objShell.nameSpace(Cible)​
    'Pour un autre dossier utilisez par exemple :​
    'Set objFolder = objShell.nameSpace("C:\Documents and Settings\michel\dossier")​
    Set colItems = objFolder.Items​
    For Each objItem In colItems​
    If objItem.isLink Then msgBox objItem.getLink.Path​
    Next​
    End Sub​
  • Retrouver le chemin du répertoire "Mes Documents"
    Sub cheminMesDocuments()​
    'testé avec Excel2002 & winXp​
    Const Cible = &H5 'Mes Documents​
    Dim objShell As Object​
    Dim objFolder As Object, objFolderItem As Object​
    Set objShell = createObject("Shell.Application")​
    Set objFolder = objShell.nameSpace(Cible)​
    Set objFolderItem = objFolder.Self​
    msgBox objFolderItem.Path​
    End Sub​
    La liste des constantes pour afficher le chemin des autres dossiers spéciaux de Windows​
    (Const Cible = &H5 'Mes Documents )​
    &H5 = My Documents​
    &HC = (Virtual) \My Documents\​
    &H27 = \My Documents\My Pictures​
    &H2E = \Documents​
    &HD = \My Documents\My Music​
    &HE = \My Documents\My Video​
    &H0 = Virtual Desktop​
    &H1 = Virtual Internet Explorer (icon on desktop)​
    &H2 = Start Menu\Programs​
    &H3 = Virtual My Computer\Control Panel​
    &H4 = Virtual My Computer\Printers​
    &H6 = \Favorites​
    &H7 = Start Menu\Programs\Startup​
    &H8 = \Recent​
    &H9 = \sendTo​
    &HA = Virtual \Recycle Bin​
    &HB = \Start Menu​
    &H10 = \Desktop​
    &H11 = Virtual My Computer​
    &H12 = Virtual Network Neighborhood​
    &H13 = \nethood (may dupe My Network Places)​
    &H14 = Virtual windows\fonts​
    &H15 = \templates​
    &H16 = \Start Menu​
    &H17 = \Programs​
    &H18 = \Startup​
    &H19 = \Desktop​
    &H1A = \Application Data​
    &H1B = \printHood​
    &H1C = \Local Settings\Application Data (non roaming)​
    &H1D = nonlocalized startup program group​
    &H1E = (NT) nonlocalized Startup group for all NT users​
    &H1F = (NT) all user's favorite items​
    &H20 = temporary Internet files​
    &H21 = (NT) Internet cookies​
    &H22 = (NT) Internet history items​
    &H23 = \Application Data​
    &H24 = Windows directory or SYSROOT​
    &H25 = getSystemDirectory()​
    &H26 = \Program Files​
    &H28 = \​
    &H29 = x86 system directory on RISC​
    &H2A = x86 Program Files folder on RISC​
    &H2B = \Program Files\Common​
    &H2C = x86 Program Files Common folder on RISC​
    &H2D = \Templates​
    &H2F = \Start Menu\Programs\Administrative Tools​
    &H30 = \Start Menu\Programs\Administrative Tools​
    &H31 = Virtual Network and dial-up connections folder​
    &H35 = My Music folder for all users​
    &H36 = My Pictures folder for all users​
    &H37 = My Video folder for all users​
    &H38 = System resource directory​
    &H39 = Localized resource directory​
    &H3A = Links to OEM specific apps for all users​
    &H3B = \Local Settings\Application Data\Microsoft\CD Burning​
    &H3D = Virtual Computers Near Me folder​
  • Récupérer le repertoire parent du classeur contenant cette macro
    Sub afficherRepertoireParent()​
    chDir (thisWorkbook.Path)​
    chDir ".."​
    msgBox curDir​
    End Sub​
  • Remonter 2 repertoires parents par rapport au classeur contenant la macro
    Dim Chemin As String​
    chDir (thisWorkbook.Path)​
    chDir ".."​
    Chemin = Left(curDir, inStrRev(curDir, "\") - 1)​
    msgBox Chemin​
  • Trier les fichiers d'un répertoire par ordre décroissant de création
    Option Base 1​
    Sub triDecroissant_fichiersRepertoire_dateDreation()​
    Dim Fichier As String, Chemin As String​
    'necessite d'activer la reference Microsoft Scripting Run Time​
    Dim Fso As Scripting.fileSystemObject​
    Dim fileItem As Scripting.File​
    Dim Tableau()​
    Dim m As Integer, i As Integer​
    Dim z As Byte, Valeur As Byte​
    Dim Cible As Variant​
    '---lister les fichiers du répertoire ---​
    Chemin = "C:\Documents and Settings\michel\dossier\general\excel"​
    Fichier = Dir(Chemin & "\.")​
    Do​
    m = m + 1​
    reDim Preserve Tableau(2, m)​
    Tableau(1, m) = Fichier​
    Set Fso = createObject("Scripting.fileSystemObject")​
    Set fileItem = Fso.getFile(Chemin & "\" & Fichier)​
    Tableau(2, m) = Left(fileItem.dateCreated, 10)​
    Fichier = Dir​
    Loop Until Fichier = ""​
    '---trier les fichiers par ordre décroissant de création ---​
    Do​
    Valeur = 0​
    For i = 1 To m - 1​
    If CDate(Tableau(2, i)) < CDate(Tableau(2, i + 1)) Then​
    For z = 1 To 2​
    Cible = Tableau(z, i)​
    Tableau(z, i) = Tableau(z, i + 1)​
    Tableau(z, i + 1) = Cible​
    Next z​
    Valeur = 1​
    End If​
    Next i​
    Loop While Valeur = 1​
    '--- transfert des données dans la feuille ---​
    For i = 1 To m​
    Cells(i, 1) = Tableau(1, i)​
    Cells(i, 2) = Tableau(2, i)​
    Next i​
    End Sub​
  • Afficher une boite de dialogue (browseForFloder) pour choisir un repertoire
  • Lister tous les dossiers et sous dossiers d'un répertoire
    Option Explicit​
    Dim i As Integer​
    Dim Cible As Byte​
    Sub listeDossiersEtSousDossiers()​
    Dim Racine As String​
    Application.screenUpdating = False​
    Racine = "C:\Documents and Settings\monRepertoire"​
    Cible = nbSeparateur(Racine)​
    listFilesInFolder Racine, True​
    Application.screenUpdating = True​
    i = 0​
    End Sub​
    Sub listFilesInFolder(sourceFolderName As String, _​
    includeSubfolders As Boolean)​
    ' adapté de Ole P Erlandsen​
    Dim Fso As Object, sourceFolder As Object​
    Dim subFolder As Object​
    On Error goTo Fin​
    Set Fso = createObject("Scripting.fileSystemObject")​
    Set sourceFolder = Fso.getFolder(sourceFolderName)​
    If includeSubfolders Then​
    For Each subFolder In sourceFolder.subfolders​
    i = i + 1​
    'pour recuperer le chemin complet​
    'Cells(i, nbSeparateur(subFolder.Path) - Cible) = subFolder.Path​
    'pour recuperer uniquement le nom du dossier​
    Cells(i, nbSeparateur(subFolder.Path) + 1 - Cible) = subFolder.Name​
    listFilesInFolder subFolder.Path, includeSubfolders​
    Next subFolder​
    End If​
    Fin:​
    End Sub​
    Function nbSeparateur(Chemin As String) As Byte​
    Dim m As Integer​
    Dim Nb As Byte​
    For m = 1 To Len(Chemin)​
    If Mid(Chemin, m, 1) = "\" Then​
    Nb = Nb + 1​
    m = m + 1​
    End If​
    Next​
    nbSeparateur = Nb​
    End Function​
  • La fonction chDrive : pour Changer le lecteur courant
    L'exemple utilise ensuite la fonction chDir pour indiquer le nouveau dossier par défaut , et afficher la boite de dialogue "Ouvrir" sur ce dossier​
    Private Sub commandButton1_Click()​
    chDrive "D"​
    chDir ("D:\monRepertoire")​
    Application.Dialogs(xlDialogOpen).Show​
    End Sub​
  • Lister les autorisations d'un répertoire
  • Lister les autorisations d'un fichier
    Ce lien n'existe plus
  • Gérer les virgules dans les noms des répertoires
    Cet exemple ouvre l'explorateur Windows sur un répertoire nommé "Nouveau , dossier"​
    Sub testOuvertureRepertoire()​
    Dim Chemin As String​
    Chemin = "C:\Documents and Settings\michel\Nouveau"" , ""dossier"​
    Shell "Explorer " & Chemin & "", 3​
    End Sub​
Lister les répertoires en réseau et afficher leur nom UNC

Sub lister_UNC()​

Dim wNwk As Object, Drvs As Object​

Dim i As Integer​

Set wNwk = createObject("WScript.Network")​

Set Drvs = wNwk.enumNetworkDrives​

For i = 0 To Drvs.Count - 1 Step 2​

Debug.Print Drvs.Item(i) & " - " & Drvs.Item(i + 1)​

Next i​

End Sub​


  • Modifier la date de création d'un fichier
  • Rechercher le dernier sous dossier modifié dans un répertoire cible, puis le dernier fichier modifié dans le sous dossier trouvé.
    Ce lien n'existe plus
Si vous constatez des erreurs dans la page n'hesitez pas à m'en faire part .
Toutes vos idees sont les bienvenues .
Michel , Mise à jour le 25 Novembre 2006
 
Dernière modification par un modérateur:

Discussions similaires

Statistiques des forums

Discussions
311 540
Messages
2 080 532
Membres
101 234
dernier inscrit
Layani89