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 !

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

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

Voici le deuxième épisode de la réorganisation des données.

Cette fois, on va utiliser PowerQuery.

Cliquez ici pour télécharger le fichier.

Comme le processus est un peu long, je donne juste les principales étapes :
importer les données dans PowerQuery
supprimer les Null
copier-coller le résultat
supprimer une ligne sur deux dans les deux tables
ajouter un index pour chaque table
joindre la deuxième table à la première via l'index

Le processus est plus long que la première solution, mais une fois la requête effectuée, l'actualisation pour de nouvelles données se fait en un clic via l'actualisation du tableau final.

Et en vidéo, ca nous donne :

Source :
https://community.powerbi.com/t5/Desktop/Every-other-row-to-new-column/td-p/205313

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

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

Résolution manuelle (1/3) - Avec PowerQuery (2/3) - Avec VBA (3/3)
Dans cette vidéo, on va voir comment passer de ceci à cela en utilisant les fonctionnalités d'excel :

Cliquez ici pour télécharger le fichier.

Les grandes étapes sont :
sélectionner les cellules vides (CTRL+A,touche F5, Cellules, Cellules vides)
supprimer les lignes (CTRL + -)
ajouter la référence appropriée (en B2, la formule est =A1)
copier B1:B2 vers le bas
copier coller en valeur la colonne B
et finalement supprimer les lignes avec une cellule vide en colonne B (touche F5, Cellules, Cellules vides, CTRL + -)

Ca parait long comme ca, mais ca se fait en moins de deux minutes :

A bientôt.

Gaetan

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

Compléter les lignes vides

Bonjour à tous,

Voici deux petites vidéos récapitulatives pour compléter une table ayant des lignes vides.

On va voir 2 méthodes (il y en a certainement plus, notamment par VBA) :
par PowerQuery :
1/ vous devez disposer d'une version récente d'Excel
2/ importez les données dans powerquery (Données, A partir d'un tableau ou d'une plage)
3/ finalement, transformer les données en recopiant vers le bas (voir l'image ci-contre).

par touche de raccourci - déjà discutée ici, mais une piqure de rappel ne peut que faire du bien !
1/ Sélectionnez la plage contenant toutes les cellules vides (y compris des cellules remplies).
Vous pouvez utiliser les combinaisons suivantes : se placer en haut du tableau (CTRL+Début), puis CTRL + MAJ + Fin et enfin CTRL + MAJ + Flèche gauche (plusieurs fois si nécessaire).
2/ Une fois la plage sélectionnée, on va uniquement sélectionner les cellules vides. Appuyez sur la touche F5, cliquez sur Cellules... et double-cliquez sur "cellules vides"
3/ Enfin, tapez "=" et appuyez sur la flèche haut
4/ Validez par CTRL + Entrée pour recopier la formule sur toute la plage de données.
5/ Finalement, sélectionnez la plage au complet (par exemple en cliquant sur la colonne A) et copier collez en valeur (CTRL + C, puis Collage spécial en valeur).

Voici donc la méthode avec PowerQuery

Voici la méthode en utilisant les touches de raccourci

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