[Astuces] Excel

0 – Fonction SI

=SI(Condition; Valeur si condition est remplie; Valeur si condition n’est pas remplie)

Conditions :
est égal à (=)
est différent de (<>)
est supérieur à (>)
est supérieur ou égal à (>=)
est inférieur à (<)
est inférieur ou égal à (<=)

1 – Figer/Geler une colonne

Utiliser le signe « $ » pour « figer » ou « geler » une colonne ou une ligne dans une formule. Quand la formule sera recopiée, les parties gelées ne changeront pas.

A1 : Ni la colonne, ni la ligne sont gelées.

$A1 : La colonne est gelée mais pas la ligne.

A$1 : La ligne est gelée mais pas la colonne.

$A$1 : La ligne et la colonne sont gelées.


2 – Répéter une action


Appuyer sur la touche F4


3 – Couleur différente une ligne sur 2

Sélectionner la zone puis menu mise en forme conditionnelle

Sélectionner ‘La formule est’

=MOD(LIGNE();2)  

4 – Chiffre qui revient le plus souvent

=MODE(B1:B5)

Comment faire disparaître le # N/A

Avec MODE vous avez peut être #N/A,pour l’enlever utiliser cette fonction:

=si(estna(maformule);"";maformule)
Exemple : =si(ESTNA(MODE(B785:F785));"";MODE(B785:F785))

5 – Colorier une cellule si une cellule = une autre cellule

Sélectionner les cellules que vous voulez colorier.

Cliquez sur « Mise en forme conditionnelle »
Puis « Nouvelle règle »
Sélectionner « Appliquer une mise….cellules qui contiennent »

Choisissez « Valeur de la cellule »

Mettre la colonne qui doit correspondre en rajoutant le chiffre 1
Exemple: =$B1


Dans Format choisissez votre mise en forme.Pour finir si vous ne voulez pas que les cellules vides soient coloriés
Sélectionner les colonnes
Appliquer une mise en forme est égale a « 0« Et dans Format > Remplissage,choisir Aucune couleur.


6 – Soustraction Positive

=ABS(A1-B1) 

Renvoie la valeur absolue de la soustraction A1-B1, donc « supprime » le signe –


7 – Supprimer une liaison

Un message indique « Le classeur que vous avez ouvert comporte des liaisons avec un autre classeur. Voulez-vous mettre à jour ce classeur OUI/NON »

Pour l’enlever sous excel 2010,en Haut,Onglet « Données »
Puis dans Groupe « Connexions » cliquez sur « Modifier les liens » et le Supprimer.


8. Supprimer les cellules vides

Sélectionner la première colonne de votre tableau.
Affichez la boîte de dialogue « Atteindre » touche “F5”.
Cliquez sur le bouton « Cellules…« .
Sélectionner l’option « Cellules vides« .


9 – Trouver une valeur dans une plage

=SI(NB.SI(K2:O2;B2);1;0)

10- Afficher ou masquer toutes les valeurs zéro dans une feuille de calcul

  1. Cliquez sur le bouton Microsoft Office , cliquez sur Options Excel, puis sur la catégorie Options avancées.
  1. Sous Afficher les options pour cette feuille de calcul, sélectionnez une feuille de calcul et effectuez l’une des actions suivantes :
    • Pour afficher les valeurs zéro (0) dans les cellules, activez la case à cocher Afficher un zéro dans les cellules qui ont une valeur nulle.
    • Pour afficher les valeurs zéro sous la forme de cellules vides, désactivez la case à cocher Afficher un zéro dans les cellules qui ont une valeur nulle.

11 – Supprimer les lignes vides a la fin

Installer ASAP Utilities
Ouvrir la feuille excel et supprimer les lignes non utilisés en sélectionnant la dernière puis faire :
CTRL + MAJ + BAS
Puis clique-droit, SUPPRIMER.
Ensuite en haut dans ASAP Utilities allez dans Colonnes et Lignes
Cliquez tout en bas sur  » Supprimer les vides inutilisés de fin de lignes »


12 – Ce Classeur comporte une liaison

Pour enlever ce message,il faut rechercher la liaison.
Rechercher avec (Ctrl+F) le caractère « [ » dans tout le classeur.
Supprimer la liaison.


13 – Remplacer #DIV/0! par une case vide 

=SI(ESTERREUR(Formule);"";(Formule))
Exemple:
=SI(ESTERREUR(B16/(C16+D16));"";(B16/(C16+D16)))

14 – Transposer une Plage (Horizontal/Vertical)

Sélectionner la nouvelle plage

Taper =TRANSPOSE(B6:B12)

Valider avec CTRL+MAJ+ENTREE


15 – Lien Hypertexte

F2 contient le numéro de suivi.

=LIEN_HYPERTEXTE("http://www.part.csuivi.courrier.laposte.fr/suivi/index?id="&F2)

=LIEN_HYPERTEXTE("http://www.lesite/" & A1 & "/" & A2 & ".com")

16 – Erreur de calcul

Si Excel fait des erreurs de calcul..
c’est tout simplement que le calcul du classeur est en « Manuel » !

Pour vérifier déjà si cela vient de la faites F9 dans votre feuille,le calcul doit se faire! 🙂

Pour un calcul « Automatique »
Allez dans Fichiers > Options > Formules

Choisir Automatique dans « Calcul du classeur »

Sans titre


17 – Calculer le nombre de fois qu’un mot revient

Ici on cherche combien de fois le mot OK revient.

=NB.SI(B2:B901;"OK")

Pour pouvoir afficher du texte à côté :

="PERFECT : "&NB.SI(B2:B901;"OK")

18 – Calculer le nombre de cellules pleines/vides dans une plage


Nombre de cellules pleines

=NB.SI(AC4:AV4;">0")

Nombre de cellules vides

=NB.SI(AC4:AV4;"=0")

19 – Afficher l’onglet Développeur

Cliquez sur l’onglet Fichier.

Cliquez sur Options.

Cliquez sur Personnaliser le ruban.

Activez la case Développeur.


20 – Ajouter du texte dans une cellule

Ici on va ajouter « kg » a côté du chiffre.

Clic Droit > Format de cellule > Personnalisée.

Ajouter du « texte » à côté du chiffre 0.

Exemple: 0" kg"

kg


21 – Renvoie la valeur de la dernière cellule

Renvoie la dernière valeur numérique de la ligne 1

=RECHERCHE(9^9;1:1)

ou texte :

=RECHERCHE("zz";1:1)

Renvoie la dernière valeur numérique de la colonne A

=RECHERCHE(9^9;A:A)

Exemple: =RECHERCHE(9^9;A:A)-60000

ou texte :

=RECHERCHE("zz";A:A)

22 – Désactiver les commentaires

Dans la boîte de dialogue Options Excel, dans l’onglet Options avancées, dans la section Afficher et choisissez l’option Aucun commentaire :


23 – Supprimer les lignes contenant :

Développeur > Visual Basic
Coller et Exécuter :

Lignes contenant #Nombre! , #Valeur! , #Nom! , #N/A! , #DIV/0!
#Ref! ou #NUL!
Sub test()
Dim Nb As Long, X As Variant, A As Long
On Error Resume Next
With Worksheets("NomFeuille") 'Nom feuille à adapter
With .Range("A1:A99")
Nb = .Rows.Count
For A = Nb To 1 Step -1
X = 0
X = .Rows(A).EntireRow.SpecialCells _
(xlCellTypeFormulas, xlErrors).Cells.Count
If X > 0 Then
.Rows(A).EntireRow.Delete
End If
Next
End With
End With
End Sub

Lignes contenant #N/A!
Sub suppressionLigne_SiErreur_NA()
Dim x As Long
For x = 65536 To 1 Step -1
If WorksheetFunction.IsError(Range("B" & x)) = True Then
If CVErr(xlErrNA) = Range("B" & x) Then _
Rows(x).EntireRow.Delete
End If
Next x
End Sub

Lignes cellules vides ou égale à 0
Sub SupprLigne()
Dim x As Long
Dim y As Long
x = Range("B65536").End(xlUp).Row
For y = x To 1 Step -1
If Cells(y, 2).Value = 0 Then
Rows(y).Delete
End If
Next y
End Sub

24 – CONDITION SI

ET  =SI(ET (Une chose est vraie, Une autre est fausse), Valeur si vrai, Valeur si faux)

OU  =SI(OU (Une chose est vraie, Une autre est fausse), Valeur si vrai, Valeur si faux)

NON  =SI(NON(Une chose est vraie), Valeur si vrai, Valeur si faux)

=SI.CONDITIONS([test_logique1; valeur_si_vrai1; test_logique2; valeur_si_vrai2; test_logique3; valeur_si_vrai3)

25 – Supprimer les lignes vides

Sélectionnez la colonne du tableau puis utilisez la fonctionnalité « Atteindre » 
située dans le menu « Rechercher et sélectionner ». 

Dans la fenêtre qui s’ouvre, cliquez sur le bouton « Cellules » pour accéder à un menu de sélection, où vous sélectionnerez « Cellules vides ». 

Dans l’onglet « Accueil », allez à l’option « Supprimer les cellules… » 
située dans le menu déroulant « Supprimer ».

26 – Les fonctions DROITE, GAUCHE & STXT

La fonction Droite permet d'extraire le nombre de caractères désiré à partir de la droite.

=DROITE(texte_original ; nb_caractères)

La fonction Gauche permet d'extraire le nombre de caractères désiré à partir de la gauche.

=GAUCHE(texte_original ; nb_caractères)

La fonction Stxt permet d'extraire le nombre de caractères désiré à partir d'une position de départ.
(en partant de la gauche !)

=STXT(texte_original ; position_départ ; nb_caractères)

Exemple en concaténant les 2 :
=STXT(DROITE(A1;19);1;7)


27 – Remplacer « . » par « , » avec SUBSTITUE

=SUBSTITUE(DROITE(A8;9);".";",")

28 – Supprimer Les Lignes Cachées

Sub RemoveHiddenRows()
Dim xRow As Range
Dim xRg As Range
Dim xRows As Range
On Error Resume Next
Set xRows = Intersect(ActiveSheet.Range("A:A").EntireRow, ActiveSheet.UsedRange)
If xRows Is Nothing Then Exit Sub
For Each xRow In xRows.Columns(1).Cells
If xRow.EntireRow.Hidden Then
If xRg Is Nothing Then
Set xRg = xRow
Else
Set xRg = Union(xRg, xRow)
End If
End If
Next
If Not xRg Is Nothing Then
MsgBox xRg.Count & " hidden rows have been deleted", , "Kutools for Excel"
xRg.EntireRow.Delete
Else
MsgBox "No hidden rows found", , "Kutools for Excel"
End If
End Sub

29 – Récupérer la 1ère valeur d’une colonne

=INDEX(A1:A65536;EQUIV(VRAI;A1:A65536<>"";0))

Valider la formule avec Ctrl + MAJ + Entrée.


30 – Arrondir un nombre

=ARRONDI.INF(nombre;chiffre après la virgule)

Exemple : =ARRONDI.INF(B8/100;0)


31 – Comptez les nombres « Positifs » OU « Négatifs »

Exemple Négatif : 
NB.SI(F2:F32;"<0")

32 – Moyenne des nombres « Positifs » OU « Négatifs »

Exemple Négatif :
=MOYENNE.SI(F2:F32;"<0")

33 – Copier texte externe sur plusieurs cellules

Private Sub Worksheet_Change(ByVal cell As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False
If Not Intersect(cell, Columns("C")) Is Nothing Then
If cell.Count = 1 Then
If InStr(1, cell.Value, Chr(10)) = 0 Then
res = Split(cell.Value, ",", 3)
If UBound(res) >= 2 Then
cell.Value = Trim(res(0))
cell.Offset(, 1).Value = Trim(res(1)) & Chr(10) & Trim(res(2))

34 – Copier une cellule dans le « Clipboard »

Cliquez sur Visualiser le code

Allez dans Outils > Références
Installer Microsoft Form 2.0 Object Library en faisant « Parcourir » pour prendre le DLL « FM20.DLL »

Ici la cellule de la colonne 7 sera copiée.

 With New DataObject
.SetText (cell.Offset(, 7))
.PutInClipboard
End With

Enjoy !

Votre commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l’aide de votre compte WordPress.com. Déconnexion /  Changer )

Image Twitter

Vous commentez à l’aide de votre compte Twitter. Déconnexion /  Changer )

Photo Facebook

Vous commentez à l’aide de votre compte Facebook. Déconnexion /  Changer )

Connexion à %s

Créez un site ou un blog sur WordPress.com

Retour en haut ↑

%d blogueurs aiment cette page :