ArrayFormula de Sheet à Excel

Bonjour à tous,

Je suis en train de migrer doucement de Google Sheet vers Excel, mais dans tous les cas je suis plutôt au début de la longue route de l'apprentissage...
Bref, J'ai téléchargé au format XLSX l'une de mes Google Sheet, et au moment d'ouvrir le fichier excel m'a indiqué des erreurs, et les a "réparées". En fait il a juste supprimé les formules qui ne lui plaisaient pas et les a simplement remplacées par le résultat, pas très pratique...

En gros toutes mes "ArrayFormula" ont disparues, elles fonctionnaient à merveille, mais il doit y avoir quelque chose qui cloche pour qu'Excel ne les comprennent pas. Voici un exemple simple :

=ArrayFormula(SOMME.SI.ENS(BDD!$E:$E;MOIS(BDD!$A:$A);MOIS(C29&1);BDD!$M:$M;"Gabriel"))

Je veux la somme de la colonne E si il y a "Gabriel" dans la colonne M et si le mois dans la colonne A est janvier. Il est inscrit "janvier" dans la cellule C29, et la date est au format jj/mm/aa dans la colonne A.
Je n'arrive pas à comprendre ce qui cloche dans la formule, même si j'ai noté que "arrayformula n'a pas l'air d'être reconnu comme tel dans Excel.

Est-ce que quelqu'un peut m'aider? Merci d'avance!

Gabriel.

Bonjour,

Faute de fichier, voici un essai incertain sans matricielle :

=SOMMEPROD(BDD!$E:$E*(MOIS(BDD!$A:$A)=MOIS(1&C29))*(BDD!$M:$M="Gabriel"))

Ici, on utilise SOMMEPROD à cause du MOIS(A:A) qui n'est a priori pas utilisable dans SOMME.SI.ENS (car les argument plages nécessitent des plages et ne prennent pas les matrices).

Cdlt,

Bonjour 3GB,

J'ai essayé votre formule, ça ne fonctionne pas (#VALEUR!). :(
J'ai donc fait un petit fichier d'exemple, en espérant que ça aide!

Gabriel

Bonjour,

Il faut mettre C4 à la place de C29 et pour espérer qu'elle marche, il faut enlever les valeurs textuelles de la colonne à sommer (soit la colonne E ici). Il y a des "-", des "x" qu'il faut enlever.

Une alternative cependant avec un tableau structuré :

=SOMMEPROD((MOIS(BDD[Colonne1])=MOIS(1&$C$4))*(BDD[Colonne13]=$B7)*SIERREUR(BDD[Colonne5]*1;0))

Cdlt,

Bonjour,

Une autre proposition.

Cdlt.

=SOMMEPROD((BDD!$M$1:$M$23=$B8)*(MOIS(BDD!$A$1:$A$23=1&$C$4));BDD!E$1:E$23)

Bonjour, et merci pour vos réponses.

J'ai malheureusement toujours du mal à comprendre tout cela (oui, mon niveau est très bas, désolé).

La première formule de 3GB me donne #VALEUR! (même en modifiant le C29 pour C4).
La deuxième formule de 3GB me donne 402 (qui ne correspond à rien que je puisse définir au vu des données).
La formule de Jean-Eric me donne 684,57 (qui est le total de la colonne E en prenant en compte le critère de la colonne M, mais sans tenir compte de la date (colonne A).

En théorie le résultat que je devrais obtenir est 388,9 (La somme de la colonne E si "Gabriel" dans la colonne M et 'Janvier' dans la colonne A).
J'ai réussi à atteindre ce résultat avec cette formule plutôt simpliste :

=SOMME.SI.ENS(BDD!$E:$E;BDD!$A:$A;">=01/01/19";BDD!$A:$A;"<=31/01/19";BDD!$M:$M;"Gabriel")

Cela dit je ne suis pas satisfait, car avant en faisant référence à une case spécifique pour le mois, il me suffisait de changer le nom du mois dans cette case pour avoir les résultats correspondants dans toutes les formules, je n'avais donc qu'à dupliquer la feuille de janvier et changer le nom dans une case pour février, et ainsi de suite pour chaque mois, ce qui m'évitait de devoir refaire les formules à chaque fois.

J'entend donc qu'il faut passer par une formule "matricielle" (c'est bien comme ça qu'on dit en français pour "ArrayFormula"?), et là je commence à vraiment être perdu... Sur Google il faut commencer par écrire "ArrayFormula" en début de formule, mais ce n'est apparemment pas le cas sur Excel où vous me conseillez d'utiliser "SOMMEPROD" car dans "SOMME.SI(.ENS)" on ne peut pas y utiliser "MOIS(*:*)".
Aussi sachez que mon fichier Excel comprend des dizaines de "ArrayFormula" qui ne fonctionnent pas en passant sur Excel, et que mon but ultime est de pouvoir tout transférer sur Excel, mais évidemment sans passer des heures à refaire toutes mes formules du tout au tout. Donc si je pouvais avoir une solution qui me permette de tout changer facilement avec un petit Ctrl+F et remplacer ceci par cela dans les formules, ça m'arrangerait grandement.
Je vous ai mis dans le fichier quelques exemples de ces formules, pas sûr que ça vous aide en quoi que ce soit, mais ça vous donnera peut-être une idée du labeur qui m'attend...

J'ai bien sûr conscience que ce n'est pas gagné d'avance et que je risque de devoir passer un peu de temps à modifier les formules de manière plus complexe, mais bon, je garde espoir!

Bonjour,

En mélangeant ma formule à celle de Jean-Eric, on trouve bien 388,90 en JANVIER pour Gabriel :

=SOMMEPROD((MOIS(BDD[Colonne1])=MOIS(1&$C$4))*(BDD[Colonne13]=$B6);BDD[Colonne5])

D'ailleurs, cette formule n'est utile que sur une seule année car si on se retrouve avec des données sur plusieurs années, elle fausse les résultats donc la vôtre semble plus indiquée (si possible, il faut garder le tableau structuré et adapter la formule) :

=SOMME.SI.ENS(BDD!$E:$E;BDD!$A:$A;">=" & DATE(2019;MOIS(1&$C$4);1);BDD!$A:$A;"<=" & FIN.MOIS(DATE(2019;MOIS(1&$C$4);1);0);BDD!$M:$M;$B6)

En mettant l'année dans une cellule et en y faisant référence, ce serait encore mieux.

Cdlt,

Bonjour 3GB,

Merci pour ce retour, ça me donne pas mal à réfléchir...
Après réflexion, je pense ma formule est, comme vous dites, probablement la plus adaptée, et sera certainement celle qui me permettra d'aller le plus vite dans la modification de toutes mes formules. Cela dit, votre dernière proposition avec "SOMME.SI.ENS" m'intéresse beaucoup aussi, mais à mon niveau il y a beaucoup de choses que je ne suis pas sûr de comprendre, donc pardonnez-moi pour ces questions qui vous paraîtront peut-être idiotes :

• Quelle est la différence entre l'utilisation de "BDD[Colonne1]" et "BDD!$A:$A" ? Et pourquoi y a-t-il un "!" dans la deuxième version et non dans la première ?
  Sur la dernière formule "SOMMEPROD", en remplaçant le premier par le deuxième (dans toute la formule bien sûr), ça ne fonctionne plus (#VALEUR!).

• Même sur ma première formule, je n'ai jamais compris ce qu'est ce "1&" dans le critère de mois ("&1" dans ma version Google d'origine) et comment il fonctionne?

• Dans la dernière formule vous écrivez "DATE(2019;MOIS(1&$C$4);1)", puis "FIN.MOIS(DATE(2019;MOIS(1&$C$4);1);0)". À quoi correspondent les "1" et "0" ?

Je pense que ça m'aiderait beaucoup de comprendre les pourquoi du comment et fonctionnements de ces détails, merci beaucoup!

Gabriel.

Bonjour,

Non ce n'est pas idiot du tout et c'est mieux qu'utiliser des fonctions sans les comprendre...

"BDD!A:A" est la colonne A de la feuille BDD alors que BDD[colonne1] est la colonne nommée colonne1 (je ne l'ai pas renommée) du tableau structuré nommé BDD. Les tableaux structurés sont très utiles et importants. Regardez mon fichier (au moins le premier que j'ai joint).

Dans la deuxième version, je ne me suis peut-être pas embarrassé où j'ai peut-être fait un copier-coller mais à votre place j'utiliserais le tableau en renommant les colonnes préalablement.

Si la formule marche avec les références au tableau, elle devrait marcher avec les références sur la feuille tant que les dimensions des colonnes sont identiques. Il faut bien adapter ("BDD!A:A" > BDD[Colonne1]; E:E > col5; M:M > col13).

Le 1&Mois dans la fonction MOIS() est une astuce pour convertir directement le mois textuel en mois numérique. C'est une découverte récente (merci à André13) que je ne saurais pas expliquer avec certitude mais je dirais que de cette manière, on force Excel à évaluer un mois en texte comme une date formatée spécialement...

DATE(2019;MOIS;1) renvoie la date au jour 1 du mois MOIS de l'année 2019. Le 1 est donc le premier jour, un choix plus ou moins arbitraire, afin d'obtenir un jour commun à tous les mois.

FIN.MOIS(Datedepart;0) renvoie la date du dernier jour du mois de la date de départ. L'argument 0 indique qu'on ne décale pas, qu'on demande le mois courant. Par exemple, si datedépart est le 1/1/2019 :

Fin.MOIS(datedépart;0) donne le 31/1/2019, FIN.MOIS(datedépart;2) donne le 31/3/2019 et FIN.MOIS(datedépart;-2) donne le 30/11/2018.

Enfin, FIN.MOIS(DATE(2019;mois(1&C4);1);0) est l'équivalent de DATE(2019;mois(1&C4)+1;1)-1 où on décale d'un mois directement dans la fonction DATE avant de soustraire un jour à la date obtenue (1/2/2019-1).

J'espère que ces explications vous permettront d'y voir plus clair.

Cdlt,

Bonjour, et merci pour ces explications!

J'y vois effectivement un peu plus clair, et je pense que je vais pouvoir me débrouiller maintenant pour faire mes modifications. Malheureusement, pas de solution miracle pour adapter les formules Google à Excel, mais avec des Ctrl+F et des remplacements bien ciblés dans les formules, j'espère ne pas passer trop de temps à modifier tout ça. Si je trouve quelque chose de vraiment pratique et efficace, je reviendrai poster ma solution "presque-miracle".

Merci encore!

Gabriel.