Archives de la catégorie : Formules et fonctions

La touche magique F2

de | |
Écrire un commentaire

Si vous avez déjà joué dans les boîtes de dia­logue de mise en forme con­di­tion­nelle ou de val­i­da­tions de don­nées, vous avez déjà remar­qué la dif­fi­culté d’éditer une formule.

Voici la touche magique : 

F2

Cette touche per­met de pass­er du mode édi­tion de for­mule au mode sélec­tion dans la feuille, et donc de vous sim­pli­fi­er la vie !

A bien­tôt.

Gae­tan Mourmant

Idées dans Excel

de | | , , ,
2 commentaires

Une nou­velle fonc­tion­nal­ité est main­tenant disponible dans cer­taines ver­sions d’Ex­cel et va être gradu­elle­ment déployée : Idées. Celle-ci est aus­si disponible dans PowerBI.

Par exem­ple, une pro­gre­sion linéaire est automa­tique­ment sug­gérée avec le com­men­taire “aug­mente au fil du temps”.

Et si on inverse les dates, on obtient l’in­verse avec une sug­ges­tion de diminu­tion au fil du temps.

Si on va un peu plus loin, on a aus­si des sug­ges­tions de valeurs hors normes et de corrélations:

Il y a encore beau­coup de tra­vail, par exem­ple, beau­coup de sug­ges­tions ne sem­blent pas très per­ti­nentes, mais l’outil est encore en test et incor­pore un bou­ton “Est-ce utile?”. 

Finale­ment, on peut imag­in­er encore plus de sug­ges­tions, basées sur les mod­èles sta­tis­tiques plus poussés que l’on pour­rait faire tourn­er automa­tique­ment sur les don­nées du tableau : Equa­tion struc­turelle, Lis­rel, arbres de régres­sion, etc. 

Bonne décou­verte.

Rechercher les liaisons externes liées à une validation

de | |
4 commentaires

Bon­jour à tous,

La présence de liens externes est un prob­lème assez courant et assez très très sioux.

Il faut en général:
— faire une recherche dans l’ensem­ble du classeur pour un “[“ ou un “.xl” (CTRL+F, et choisir de chercher sur tout le classeur)
— véri­fi­er les noms : onglet For­mules, Ges­tion­naire de noms
— véri­fi­er les mis­es en forme con­di­tion­nelle : pour un prochain article.
— véri­fi­er les validations

Voici donc une petite macro qui va per­me­t­tre de par­courir l’ensem­ble des val­i­da­tions de don­nées d’un classeur pour iden­ti­fi­er celles qui pour­raient con­tenir des liaisons externes.

Une fois la macro exé­cutée, une nou­velle feuille est ajoutée au fichier.

Vous noterez que j’u­tilise des vari­ables tableaux pour stock­er le résul­tat. Ça n’est pas néces­saire, mais cela accélère grande­ment l’exé­cu­tion du code, donc c’est une bonne pra­tique au final, surtout si vous avez des dizaines de mil­liers de lignes.

Option Explicit

Sub TrouveLiensExternesValidation()
    'Definition des variables
    Dim rgeCell As Range
    Dim sDvForm As String
    Dim counter As Integer
    Dim wksResult As Worksheet
    Dim wks As Worksheet
    Dim arrNomFeuille As Variant
    Dim arrAdresseCellule As Variant
    Dim arrFormule As Variant
    ReDim arrNomFeuille(1 To 1) As Variant
    ReDim arrAdresseCellule(1 To 1) As Variant
    ReDim arrFormule(1 To 1) As Variant
    Dim arrResult As Variant
    
    Dim lRow As Long
    Dim lRowResultat As Long
    'masquer l'actualisation
    Application.ScreenUpdating = False
    'compteur
    lRow = 1
    'on boucle sur toutes les feuilles
    For Each wks In ActiveWorkbook.Worksheets
        wks.Visible = xlSheetVisible
        'on boucle sur toutes les cellules de la feuille
        For Each rgeCell In wks.UsedRange.Cells
            'On reprend la formule
            On Error Resume Next
            sDvForm = ""
            sDvForm = rgeCell.Validation.Formula1
            On Error GoTo 0
            'il y a plusieurs options. On peut tester la presence d'un "[" ou bien du ".xl"
            If InStr(1, sDvForm, ".xl") > 0 Then 'on trouve un ".xl"
                lRow = lRow + 1
                'on peut faire un Preserve, car une seule dimension dans le tableau
                ReDim Preserve arrNomFeuille(1 To lRow) As Variant
                ReDim Preserve arrAdresseCellule(1 To lRow) As Variant
                ReDim Preserve arrFormule(1 To lRow) As Variant
                'on stocke les donnees
                arrNomFeuille(lRow) = wks.Name
                arrAdresseCellule(lRow) = rgeCell.Address
                arrFormule(lRow) = "'" & sDvForm
            End If
        Next rgeCell
    Next wks
    'on rapatrie les resultats dans la feuille
    'On revient en A1
    If lRow <> 0 Then
        'creer la feuille de resultat
        Set wksResult = ActiveWorkbook.Sheets.Add(before:=ThisWorkbook.Sheets(1))
        'wksResult.Name = "external links"
        ReDim arrResult(1 To lRow, 1 To 3) As Variant
        arrResult(1, 1) = "Nom de la feuille"
        arrResult(1, 2) = "Adresse de la cellule"
        arrResult(1, 3) = "Formule"
        For lRowResultat = 2 To UBound(arrNomFeuille, 1)
            arrResult(lRowResultat, 1) = arrNomFeuille(lRowResultat)
            arrResult(lRowResultat, 2) = arrAdresseCellule(lRowResultat)
            arrResult(lRowResultat, 3) = arrFormule(lRowResultat)
        Next lRowResultat
        wksResult.Range("A1:C" & UBound(arrResult, 1)).Value = arrResult
    End If
    Application.ScreenUpdating = True
End Sub

Source d’in­spi­ra­tion : http://dailydoseofexcel.com/archives/2009/05/04/finding-external-links-in-data-validation/#comment-1045713