SOMME en ligne et ensuite en colonnes

Accueil – Le CFO masqué › Forums › Excel › SOMME en ligne et ensuite en colonnes

  • Ce sujet contient 6 réponses, 2 participants et a été mis à jour pour la dernière fois par Evelise Schiavon Mazon, le il y a 3 années et 8 mois.
7 sujets de 1 à 7 (sur un total de 7)
  • Auteur
    Messages
  • 14 juillet 2021 à 10 h 55 min #93005
    Evelise Schiavon Mazon
    Participant

    Bonjour,
    J’ai des données fictives des ventes par trimestres pour 4 ans.
    J’essaie de créer une formule qui me donne les ventes cumulatives, par exemple du Q2-2017 au Q3-2019, ce qui veut dire que je doit avoir les total des ventes suivantes:
    – 2017 : Q2 au Q4
    – 2018 : Q1 au Q4
    – 2019 : Q1 au Q3
    J’ai essayé d’imbriquer des RECHERCHEX (ou de INDEX/EQUIV) dans la fonction SOMME. Mon problème est que je suis capable uniquement de faire la somme des plages pour 2017 et pour 2019, ma formule n’est pas en mesure d’ajouter les données de 2018.
    Est-ce qu’il y a une solution pour cela?
    En pièce-jointe, vous trouverez mon fichier avec les données et les fonctions créées.
    Tout aide est très bienvenue 🙂 Merci beaucoup!

    Attachments:
    You must be logged in to view attached files.
    15 juillet 2021 à 4 h 02 min #93021
    Stéphane Lorin
    Participant

    Bonjour

    Voici une fonction Excel qui répond à votre besoin

    =SOMME(B4:E7*
    (TRANSPOSE(SEQUENCE(4;4))>=INDEX(TRANSPOSE(SEQUENCE(4;4));EQUIV(C11;A4:A7;0);EQUIV(B11;B3:E3;0)))*
    (TRANSPOSE(SEQUENCE(4;4))<=INDEX(TRANSPOSE(SEQUENCE(4;4));EQUIV(D12;A4:A7;0);EQUIV(B12;B3:E3;0))))

    TRANSPOSE(SEQUENCE(4;4)) pour créer une matrice de 1 à 16 avec les périodes dans l’ordre (1, 2, 3, 4 en 1ere colonne; 5, 6, 7, 8 en 2eme…)
    puis un INDEX pour déterminer la position du min
    un autre pour celle du max
    on multiplie la plage de départ par celle des périodes >= mix et par celle <= au max pour obtenir le résultat

    Stéphane

    15 juillet 2021 à 6 h 14 min #93025
    Stéphane Lorin
    Participant

    Re-bonjour
    je complète ma réponse précédente en précisant que votre problématique justifierai un passage par Power Query pour transformer votre tableau en base de données exploitable ensuite par TCD, formule…
    Vous transformez votre plage de données en tableau “structuré”, que vous importez dans Power Query via le menu “Données/Récupérer et transformer”. Vous sélectionnez votre 1ere colonne et dépivotez les autres.
    A voir suivant votre besoin réel et pas uniquement cet exemple.
    Stéphane

    15 juillet 2021 à 6 h 30 min #93026
    Stéphane Lorin
    Participant

    Encore moi avec une formule Excel plus courte et plus simple

    =SOMME(B4:E7*
    (INDEX(B3:E3;SEQUENCE(;4))&INDEX(A4:A7;SEQUENCE(4))>=B11&C11)*
    (INDEX(B3:E3;SEQUENCE(;4))&INDEX(A4:A7;SEQUENCE(4))<=B12&D12))

    INDEX(B3:E3;SEQUENCE(;4))&INDEX(A4:A7;SEQUENCE(4)) crée une matrice
    2017Q1 | 2018Q1 | 2019Q1 …
    2017Q2 | 2018Q2 | 2019Q2 …
    …

    que l’on compare avec le min = 2017Q2 et le max 2019Q3 avant de multiplier l’ensemble par la plage de départ

    Stéphane

    15 juillet 2021 à 10 h 18 min #93028
    Evelise Schiavon Mazon
    Participant

    Bonjour Stéphane,

    WOW! Un très gros merci.

    J’ai bcp aimé ta deuxième suggestion, en effet cette formule est plus simple à lire. Je me suis permis d’apporter une petite modification : à l’intérieure de la formule SEQUENCE, j’ai remplacé le 4 (pour nombre de trimestres et d’années) par la formule NBVAL afin de calculer le nombre de cellules non vides dans la plage. À mon avis, cela m’aidera lors de l’ajout des nouvelles années.

    =SOMME(B4:E7*
    (INDEX(B3:E3;SEQUENCE(;NBVAL(B3:E3)))&INDEX(A4:A7;SEQUENCE(NBVAL(A4:A7)))>=B11&C11)*
    (INDEX(B3:E3;SEQUENCE(;NBVAL(B3:E3)))&INDEX(A4:A7;SEQUENCE(NBVAL(A4:A7)))<=B12&D12))

    Merci aussi pour tes explications, très pertinentes 🙂

    Bonne journée,
    Evelise

    15 juillet 2021 à 11 h 40 min #93032
    Stéphane Lorin
    Participant

    Merci pour le retour
    En effet pour le nombre d’année qui va augmenter
    Par contre, celui du nombre de trimestres dans une année ne devrait pas évoluer. On doit pouvoir considérer sans risque qu’il y aura toujours 4 trimestres dans une année !
    Bonne continuation sur ce projet
    Stéphane

    16 juillet 2021 à 9 h 09 min #93051
    Evelise Schiavon Mazon
    Participant

    Merci Stéphane… très gentil de ta part 🙂

  • Auteur
    Messages
7 sujets de 1 à 7 (sur un total de 7)
  • Vous devez être connecté pour répondre à ce sujet.