Plusieurs calculs sur colonnes et lignes en une seule formule ?

Bonjour,

Je crée actuellement un fichier qui permettra à un utilisateur de copier des données dans un onglet, puis de récupérer quelques indicateurs calculés dans un autre onglet. En raison de contraintes techniques et de challenge personnel, je ne veux pas utiliser VBA et tout optimiser au maximum. Dans l'idéal je souhaite calculer chaque indicateur en une seule formule sans devoir faire de modifications de la base de données importée par l'utilisateur.

Pour la faire courte, la base de données importée par l'utilisateur aura un nombre de lignes variable (dont certaines pourront être vides) et des colonnes variables (nom et emplacement), mais les indicateurs calculés seront toujours les mêmes. J'ai géré cela sans problème avec des listes déroulantes dynamiques et formules matricielles pour tous les indicateurs sauf un, dont le calcul me semble infaisable en une seule formule et pour lequel je vous sollicite.

Voici mon besoin/problème, illustré par les données dans le fichier joint.

L'indicateur bloquant est calculé à partir de 4 variables contenant du texte ("Jamais", "Rarement", "Souvent", "Toujours" ou vide). [colonnes A-D du fichier joint]
Pour chaque ligne du fichier, il faut remplacer le texte s'il est non vide par un score (cf tableau ci-dessous) et faire la moyenne des 4 variables transformées. [colonnes F du fichier joint].
Enfin, il faut faire la moyenne de toutes ces valeurs calculées. [cellule G2 du fichier joint]

Pensez-vous qu'il soit possible de faire cela en une seule formule ou vais-je devoir passer par une solution moins sympa et moins propre ? (onglet caché avec copie des 4 variables et formule colonne F prête mais qui alourdirait le fichier, manipulation manuelles sur la base de donnée par l'utilisateur...)

J'espère avoir été assez concis et clair.

Merci d'avance pour vos retours !

Bonjour,

Bien sur, on peut faire cela très facilement avec les fonctions adaptées : RECHERCHEX par exemple. Ci-joint un exemple en utilisant votre tableau de valeurs. On va chercher, pour chaque cellule, sa correspondance dans le tableau de valeurs (avec recherchex), puis on somme/moyenne le résultat. Cela donne la formule :

=MOYENNE(RECHERCHEX(A2:D2;tblScore[Valeur texte];tblScore[Score];0))

NOTA : il s'agit d'une formule matricielle implicite, quand on écrit RECHERCHEX(A2:D2; ...) en fait la fonction RECHERCHEX est appliquée à A2, puis B2... D2. Cela renvoie donc une petite matrice de 4 résultats. On utilise ensuite moyenne pour calculer la moyenne des 4 cellules.

EDIT : la formule de @JFL ci-après permet de calculer de manière dynamique la moyenne des moyennes (pour chaque ligne = la formule ci-dessus, puis moyenne de la colonne obtenue). Le principe de base est le même.

Bonjour à tous !

Une approche type formule unique dynamique avec table de correspondance :

Merci pour vos réponses très rapides et répondant parfaitement à mon besoin.

Bonne journée !

Bonjour à tous de nouveau !

Bien...

Je vous remercie de ce retour.

Bonjour.

Suite à vos réponses d'hier, j'ai essayé de pousser un peu plus loin le sujet et je me heurte à un nouveau souci.

Dans l'exemple fourni hier, les colonnes à traiter étaient contigües. J'ai essayé d'adapter la formule pour le cas où les colonnes seraient séparées. Après de longues recherches, lectures, visionnage de vidéos etc. j'en suis venu à la conclusion que BYROW n'est pas adaptée dans ce cas là mais j'espère me tromper ou pouvoir apprendre une nouvelle formule qui solutionnera le problème.

Vous trouverez un nouveau fichier exemple en pièce jointe. Les colonnes à traiter sont les colonnes Q1, Q4, Q5 et Q7 (A, D, E et G).

J'ai essayé d'utiliser BYROW sur les colonnes A:G, ce qui pourrait fonctionner si la colonne B ne contenait pas les mêmes modalités que les colonnes qui m'intéressent, j'ai essayé de trouver un moyen de créer une table en concaténant les colonnes qui m'intéressent pour les mettre en argument de BYROW mais sans succès.

Avez-vous une solution pour tout faire en une seule formule comme pour l'exemple précédent ou bien faut-il que je me résigne à copier les colonnes qui m'intéressent puis les coller quelque part de façon contigüe afin d'utiliser BYROW ?

Merci d'avance pour vos retours.

PS : je ne savais pas si je devait créer un nouveau sujet ou s'il était OK de prolonger celui-ci.

Bonjour à tous !

Avec l'aide la fonction ASSEMB.H :

=MOYENNE(SIERREUR(BYROW(ASSEMB.H(A2:A8;D2:E8;G2:G8);LAMBDA(x;  MOYENNE(RECHERCHEX(x;tCorr[Texte];tCorr[Valeur];""))));""))

Ou variabilisation de la plage source et choix des colonnes à traiter :

=LET(
p;A2:G8;
MOYENNE(SIERREUR(BYROW(CHOISIRCOLS(p;1;4;5;7);LAMBDA(x;  MOYENNE(RECHERCHEX(x;tCorr[Texte];tCorr[Valeur];""))));""))
)

Bonjour,

Ci-joint une alternative basée sur la proposition de @JFL en utilisant une liste des Qi à trier :

=LET(
    _db; A16:G23;
    _head; PRENDRE(_db; 1);
    _data; EXCLURE(_db; 1);
    _filt; SUPPR.PLAGE(I17:I26);
    _numCol; EQUIVX(_filt; _head);
    MOYENNE(
        SIERREUR(
            BYROW(CHOISIRCOLS(_data; _numCol); LAMBDA(x; MOYENNE(RECHERCHEX(x; tCorr[Texte]; tCorr[Valeur]; ""))));
            ""
        )
    )
)

Bonjour à tous de nouveau !

@saboh12617 : Hello !

Attention.... le fichier posté n'est pas le bon et la fonction BYLIGNE n'est pas francisée.

Merci une nouvelle fois pour vos réponses !!!

Je ne dois pas savoir chercher comme il faut car jamais je n'ai pas vu d'articles ou liens mentionnant ASSEMB.H/V, qui correspond exactement à ce que je cherchais.

Et grâce à vous je découvre également LET qui me sera bien utile.

Bonne continuation !

Bonjour à tous de nouveau !

Bien....

Je vous remercie de ce retour.

Merci pour ton retour @JFL, corrigé

Bonne journée à tous