Créer une image liée, une autre méthode

Bonjour,

Vous connaissez surement la méthode qui permet de créer une image liée à une cellule :
1/ Copier la cellule
2/ Choisir Collage image liée (le dernier bouton en bas à droite de coller)
3/ vous pouvez ensuite déplacer l'image qui a été positionné directement sur la cellule.

Voici une autre manière de faire suggérée par Claude S.
1/ Créer une zone de texte
2/ Sélectionner une plage de cellules
3/ Sélectionner le bord de la zone de texte (curseur en croix)
4/ Coller
5/ Une zone de texte contenant l'image de la plage a été créée au-dessus de la plage sélectionnée.

Sauf erreur, je pense qu'on arrive au même résultat.
La deuxième solution me semble moins rapide, mais j'ai l'impression qu'il y a des utilisations potentielles non soupçonnées... Des idées ?

A bientôt

Gaetan

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !

Rechercher les liaisons externes liées à une validation

Bonjour à tous,

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

Il faut en général:
- faire une recherche dans l'ensemble du classeur pour un "[" ou un ".xl" (CTRL+F, et choisir de chercher sur tout le classeur)
- vérifier les noms : onglet Formules, Gestionnaire de noms
- vérifier les mises en forme conditionnelle : pour un prochain article.
- vérifier les validations

Voici donc une petite macro qui va permettre de parcourir l'ensemble des validations de données d'un classeur pour identifier celles qui pourraient contenir des liaisons externes.

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

Vous noterez que j'utilise des variables tableaux pour stocker le résultat. Ça n'est pas nécessaire, mais cela accélère grandement l'exécution du code, donc c'est une bonne pratique au final, surtout si vous avez des dizaines de milliers 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'inspiration : http://dailydoseofexcel.com/archives/2009/05/04/finding-external-links-in-data-validation/#comment-1045713

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !

Gestion des formes dans Excel

Bonjour,

Je travaille actuellement sur un livre, et nous sommes en train de faire un diagramme assez complexe avec des centaines de formes.

Excel n'est peut-être pas l'outil idéal, mais comme je le maitrise relativement bien, je l'ai quand même choisi 🙂

Dans cette vidéo, retrouvez une dizaine d'astuces pour gérer ces formes :
- Créer un cadre orthonormé
- Utiliser des connecteurs
- Touches CTRL et ALT
- Volet sélection pour sélectionner en un clic
- Ajouter un curseur pour sélectionner en traçant un cadre autour des formes
- Groupes les formes
- Renommer les formes ou les groupes

Et bien plus...

A bientôt.

Gaëtan

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !

Utilisation de la fonction AGREGAT : équivalent de SOMME.SI.ENS sans formule matricielle

Bonjour,

Agregat est une fonction qui a été introduite avec Excel 2010. Elle permet d'agréger des données d'une liste ou d'un tableau avec toute une série de fonctions disponibles, et aussi la possibilité de gérer les lignes masquées et les valeurs d'erreurs.

Jusqu'à présent, je n'avais pas eu à l'utiliser, mais la nécessité de faire fonctionner un équivalent de MIN.SI.ENS (disponible seulement avec XL 2016) en voulant éviter une formule matricielle m'a forcée à l'utiliser.

L'idée générale est de faire un agrégat en utilisant la fonction Petite valeur (no_fonction = 15) tout en évitant les zéros.
On veut ici retourner la plus petite valeur comprise entre une date de début et une date de fin.

Vous pouvez télécharger le fichier ici.

La formule est donc :
=AGREGAT(15;6;(B2:B14)/(A2:A14< =E4)/(A2:A14>=E3);1)
Si nous décomposons, nous avons donc :
15 : No_Fonction qui est petite.valeur

6 : qui permet d'ignorer les valeurs d'erreur. Comme le fait de diviser par FALSE (donc =0) renvoit une valeur d'erreur, on va donc ignorer toutes les valeurs divisée par zéro, donc toutes les valeurs en dehors des dates.

Le troisième argument reprend le tableau, ici présenté sous forme matricielle, avec les valeurs du tableau, ou un #DIV/0 en cas d'erreur :

On peut améliorer la lisibilité en utilisant les coordonnées sous forme de tableau et en nommant les cellules de référence:
=AGREGAT(15;6;(TabData[Montant])/(TabData[Date]< =ParamDateFin)/(TabData[Date]>=ParamDateDebut);1)

Finalement, le dernier argument nous permet de spécifier quelle plus petite valeur on veut retourner. Ici, on choisir 1 pour la première des plus petites valeurs !
Si on avait choisi 2, on aurait retourné 75, qui est la deuxième plus petite valeur :

A bientôt.

Pour aller plus loin :
Le lien vers le site de microsoft : https://support.office.com/fr-fr/article/agregat-agregat-fonction-43b9278e-6aa7-4f17-92b6-e19993fa26df
Un autre exemple d'utilisation de la fonction Agregat pour Min.Si.Ens (en Anglais) : https://a4accounting.com.au/excel-how-to-create-a-minif-and-a-maxif/

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !

Réorganisation de données (3/3) : via VBA

Résolution manuelle (1/3) - Avec PowerQuery (2/3) - Avec VBA (3/3)
Bonjour à tous,

Troisième volet sur la réorganisation de données, cette fois-ci en utilisant le VBA.

Le code VBA se trouve en dessous de la vidéo

Cliquez ici pour télécharger le fichier.

Option Explicit

Sub subReorganisationDonnees()
    'L'objectif de ce code est de separer une liste de noms et d'emails
    'pour les reorganiser en deux colonnes

    'Definition de variables
    Dim arrDonneesSource As Variant
    Dim arrDonneesCible As Variant
    Dim lRowSource As Long
    Dim lRowCible As Long
    Dim iColonneCible As Integer

    'Charger les donnees
    arrDonneesSource = ActiveSheet.UsedRange.Value

    'Creer l'array qui va accueillir les donnees finales
    ReDim arrDonneesCible(1 To UBound(arrDonneesSource), 1 To 2) As Variant

    'Boucle de remplissage de l'array final
    iColonneCible = 1
    lRowCible = 1
    For lRowSource = LBound(arrDonneesSource, 1) To UBound(arrDonneesSource, 1)
        If arrDonneesSource(lRowSource, 1) <> "" Then
            arrDonneesCible(lRowCible, iColonneCible) = arrDonneesSource(lRowSource, 1)
            If iColonneCible = 1 Then
                iColonneCible = 2
            Else
                iColonneCible = 1
                lRowCible = lRowCible + 1
            End If
        End If
    Next
    
    'Rapatrier les donnees
    Worksheets.Add
    Range("A1").Value = "Nom"
    Range("B1").Value = "Email"
    Range(Cells(2, 1), Cells(UBound(arrDonneesCible, 1) + 1, 2)).Value = _
        arrDonneesCible
End Sub

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !