Discussion :

[sandydavneo]

Bonjour à tous et à toutes,

je cherche une aide dans une formule que je n'arrive pas à trouver depuis longtemps…
En résumé: je souhaiterai additionner des sommes entres 2 dates mais avec en plus une requête supplémentaire de fournisseur.
J'ai essayé "SOMMEPROD" et "SOMME.SI.ENS" mais rien à faire, toujours ce chiffre de "0"!!!
Le fichier présente une colonne date du 01/01/2016 à aujourd'hui, une colonne avec plusieurs fournisseurs, une colonne avec des montants.
J'ai ensuite des dates de début et de fin.
Je dois trouver ce "fameux" montant entre 2 dates en ne sélectionnant qu'un seul fournisseur.
J'espère avoir été clair!
Je vous remercie par avance de votre aide.
Bien à vous.
David

[Menhir]

En résumé: je souhaiterai additionner des sommes entres 2 dates mais avec en plus une requête supplémentaire de fournisseur.
J'ai essayé "SOMMEPROD" et "SOMME.SI.ENS" mais rien à faire, toujours ce chiffre de "0"!!!

A priori, SOMME.SI.ENS() doit faire l'affaire.

Il faudrait que tu montres quelle formule tu as écrite pour savoir ce qui ne va pas.

Encore faudrait-il être sûr que tes "dates" sont vraiment des dates et non pas du texte ressemblant à des dates.

Le fichier présente une colonne date du 01/01/2016 à aujourd'hui,

Quelle colonne ?
A quelle ligne elle commence ? A quelle ligne elle finit ?

une colonne avec plusieurs fournisseurs,

Quelle colonne ?
A quelle ligne elle commence ? A quelle ligne elle finit ?

une colonne avec des montants.

Quelle colonne ?
A quelle ligne elle commence ? A quelle ligne elle finit ?

J'ai ensuite des dates de début et de fin.

Dans quelles cellules ?

[Alex020181]

Bonjour,

Somme.si.ens suffit à sommer selon les critères que vous définissez. Le tout est d'en trouver la syntaxe.

Je vous ai créé un fichier exemple en PJ.

[Pierre Fauconnier]

Salut.

Sur base de l'idée d'Alex020181, je travaillerais avec des tableaux structurés (voir mon tuto). Pour moi, ce n'est pas une option avec les versions actuelles d'Excel (xlsX ou xlsM)


SOMME.SI.ENS, qui offre une critérisation assez limitée





SOMMEPROD (ou SOMME MATRICIELLE), qui offre une palette beaucoup plus large et quasi infinie de critérisation

[sandydavneo]

Bonjour Pierre Fauconnier, Menhir et Alex020181,

tout d'abord un grand merci pour vos réponses rapides, vous êtes au top les gars !!!

Malheureusement, rien ne marche, toujours ce maudit chiffre "0".
Je vais essayer d'être plus explicite comme le demande Menhir:
- en effet, j'ai bien vérifier que ces dates sont bien des dates et non des chiffres ou nombre…

- J'ai un onglet avec des données de la ligne 2 à 1238 (et qui continuera à grandir),
1 colonne (A)=N° commande, 1 colonne (B)=date, 1 colonne (F)=N° marché et 1 colonne (M)=montants en chiffres monétaires.


Sur un autre onglet figure les dates de début et de fin de marché (donc 5 colonnes avec une date différentes qui elles-aussi sont au format date)


Donc je dois trouver les montants pour l'année 1, l'année 2,etc...

Comme vous le voyez sur la photo ci-après, j'ai trouvé le résultat que je cherche mais en trichant un peu, c'est à dire en allant chercher toutes les factures de la 1ère date à la dernière.


J'ai essayé avec la formule SOMMEPROD (ci-dessous)


Et avec la formule SOMME.SI.ENS


Mais rien y fait !!! aucun résultat comme vous le voyez !!!
J'espère que vous pourrez trouver d'où ça vient, je commence à désespérer.
Merci en tous les cas.
A plus tard et bonne journée.
David

[Pierre Fauconnier]

C'est un problème de références absolues et relatives => voir mon tuto à ce sujet


Ceci dit, ta formule est mal construite car elle ne vaut que pour la première période. Pour les suivantes, le glissement à droite va inclure la date de fin de la période précédente, ce qui est évidemment préjudiciable.

[...]J'ai un onglet avec des données de la ligne 2 à 1238 (et qui continuera à grandir)[...]

===>>> Nécessité d'utiliser les tableaux structurés (voir mon tuto à ce sujet)

Perso, j'utiliserais donc les tableaux et la formule illustrée ci-dessous:




Attention: Lors de la recopie de la formule de J4 vers les colonnes à droite, tu dois utiliser le copier-coller et pas la poignée de recopie. Le copier-coller considère les colonnes du tableau structuré comme absolues, alors que la poignée de recopie va les considérer comme relatives (voir ce point spécifique de mon tuto sur les tableaux structurés)


Comme précisé dans mon tuto, tu peux bien sûr déplacer le tableau dans un autre onglet. Tu remarques que la formule que j'utilise renseigne les colonnes du tableau, sans notion de la feuille où ce tableau se trouve et sans notion des lignes de début et de fin, car le tableau grandira lors de l'ajout de nouvelles lignes, et donc, la formule tiendra automatiquement compte de ces nouvelles lignes sans que tu doives l'adapter.

L'utilisation des tableaux structurés n'est pas une option, mais une obligation pour qui veut construire des classeurs Excel professionnels, fiables, pérennes et évolutifs.

[sandydavneo]

Bonjour Pierre,

non rien à faire, toujours "0".



J'ai regardé ton tuto sur les valeurs relatives et absolues.
Il me semble que mes données soient bonnes.

Je ne comprends pas !
Merci Pierre.

[Pierre Fauconnier]

[...]
J'ai regardé ton tuto sur les valeurs relatives et absolues.
[...]Je ne comprends pas ![...]

Faut croire qu'il est mal expliqué, puisque je montre où il faut mettre des $, et toi, tu n'as mis aucun $ dans ta formule, et en plus, tu utilises la syntaxe de SOMME.SI avec SOMMEPROD...

Donc, quand tu vas la recopier vers le bas, Excel va utiliser M3:M1242 (etc, etc), et quand tu vas la recopier à droite, Excel va utiliser N2:N1241...

Perso, je te conseille SOMMEPROD. SOMME.SI.ENS est contre-intuitif n'est que le critère n'est pas une égalité stricte. Ta formule devrait être

Code formule :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD(Feuil1!$M$2:$M$1241*(Feuil1!$B$2:$B$1241>MOIS.DECALER(O4;-12))*(Feuil1!$B$1241<=O4)*(Feuil1!$F$2:$F$1241=$C7))

Tu devrais vraiment utiliser les tableaux structurés. Dans la formule que tu donnes, si tu ajoutes des lignes en FEuil1, ta formule ne les prendra pas en compte.

Regarde bien, dans la formule que j'ai utilisée, où j'ai mis les $ pour figer l'axe lors de la recopie... On voit bien, entre l'illustration de la formule en J et celle en K, quels sont les blocs qui ont glissés et ceux qui sont restés fixes...

[sandydavneo]

Oui, oui, j'avais bien compris et j'avais essayé les 2 solutions de formules (avec $ et sans $) mais toujours rien (chiffre "0").


Dur, dur !!!

[Pierre Fauconnier]

Sans voir les colonnes que tu as masquées (surtout celles de Feuil1), c'est compliqué de pouvoir t'aider. Il faudrait déjà vérifier que tous les formats sont ok, que les dates sont bien des dates...

Voici un classeur exemple avec SOMMEPROD et, bien entendu, un tableau structuré pour les données sources.


Pourrais-tu mettre le tien (attention aux données sensibles)?

[Menhir]

- en effet, j'ai bien vérifier que ces dates sont bien des dates et non des chiffres ou nombre…

Ce qui serait gênant, c'est plutôt que ce soit des chaine de caractères, si qui produirait exactement l'effet que tu décris.

Comment as-tu procédé pour vérifier qu'il s'agit bien de dates (aussi bien pour les données de la liste que pour les critères) ?

[sandydavneo]

Bonjour Menhir, bonjour Pierre,

après plusieurs essais, toujours le même problème…
ça doit venir probablement de mon tableau de données.

Mehnir, j'ai vérifié mes dates en sélectionnant les données et en allant sur l'onglet "accueil - nombre" et en sélectionnant "date courte".

Merci à vous.
David

[Pierre Fauconnier]

Peut-être as-tu un ou des espaces derrière le nom du fournisseur. C'est une erreur fréquente...

[Menhir]

Mehnir, j'ai vérifié mes dates en sélectionnant les données et en allant sur l'onglet "accueil - nombre" et en sélectionnant "date courte".

Que se passe-t-il si tu sélectionnes toutes les colonnes de date et que tu les passes au format de nombre "Standard" ?

[sandydavneo]

Mehnir,

sur ma base de données, quand je sélectionne toutes mes données et que je change en standard, rien ne change alors que dans mon tableau de date marché, quand je change en standard, les dates deviennent des chiffres… Est-ce un problème ?

Pierre,

non aucun espace sur les fournisseurs.

[Pierre Fauconnier]

Lorsque tu te places sur une date et que tu ouvres la liste déroulante des formats du ruban Accueil, que vois-tu dans ce que j'ai encadré en rouge dans les deux illustrations suivantes? La "date" telle qu'encodée dans la cellule ou bien la date en long comme dans la seconde illustration?

Si c'est "comme dans la cellule", c'est que tes dates sont du texte. Si c'est comme illustré dans la seconde image, ce sont des dates.

[Pierre Dumas]

sur ma base de données, quand je sélectionne toutes mes données et que je change en standard, rien ne change alors que dans mon tableau de date marché, quand je change en standard, les dates deviennent des chiffres… Est-ce un problème ?

Bonjour

Oui, c'est un problème.
Dans un classeur vierge, si on saisie une date (celle du jour par exemple) en cellule A1, Excel l'affiche en format Date courte (ex 06/04/2020).
Sur cette même cellule A1, si on change le format pour prendre "Standard", Excel affiche un nombre (exemple 43927 pour la date du jour par exemple). Et ceci est parfaitement normal. Ce nombre est celui donnant le nombre de jour depuis le 01/01/1900. Il y a donc 43927 jours entre le 01/01/1900 et le 06/04/2020.

Donc, appliqué au cas qui nous préoccupe, si dans la base de données les dates ne deviennent pas des chiffres quand on applique le format Standard, la conclusion est qu'elles ne sont pas des dates (c'est du Canada dry : ont croit que c'en est, mais ce n'en n'est pas ).
Par ailleurs, si dans le tableau marché, les dates se transforment en nombre quand on applique le format Standard, c'est que ce sont bien des dates.

Donc, vous essayez de comparer des "pas date" avec des dates. Donc, cela ne peut pas marcher. Tout au moins directement. Soit il faut changer les données dans la base de données, soit il faut changer les données dans le tableau marché, soit il faut faire des manipulations pour pouvoir comparer les unes et les autres.

En espérant que cela aide.

Bonne journée

Pierre Dumas

[Menhir]

dans mon tableau de date marché, quand je change en standard, les dates deviennent des chiffres…

Ca, c'est normal et c'est bon signe.
Ca veut dire qu'il s'agit effectivement de valeurs Dates.

sur ma base de données, quand je sélectionne toutes mes données et que je change en standard, rien ne change

Ca, c'est un problème.
Ca veut dire qu'il ne s'agit pas de dates mais de chaines de caractères ressemblant à des dates, ce qui est très fréquent quand on charge les données d'une base externe sans avoir précisé la nature de chaque colonne.

Comme nous le soupçonnions depuis le départ, le problème vient de dates qui n'en sont pas.

[sandydavneo]

Messieurs,

vous avez raison, c'est bien un problème de date qui n'est pas considéré comme une date dans Excel…
Vous êtes top !!!!!

Bon maintenant, il va falloir que je me tape 1238 lignes à changer en date… (à moins que vous ayez une combine )
Oui c'est une extraction d'Access que je colle sur Excel.
Pfff, j'en suis démotivé à l'avance.

Merci beaucoup, vous êtes de fins experts.

[Pierre Dumas]

Bon maintenant, il va falloir que je me tape 1238 lignes à changer en date… (à moins que vous ayez une combine )
Oui c'est une extraction d'Access que je colle sur Excel.

Bien sûr qu'il y a des combines
Excel, c'est pour les fainéants : on ne va pas se taper 1238 lignes à refaire à la main

Sinon, Access dans Excel ne devrait pas poser ce genre de problème. A condition que les données soient bien typées (c'est à dire bien indiquées avec le bon type, ici date) dans Access.

Il nous faudrait une copie d'écran des données brutes d'Access (c'est à dire directement sans aucune modification).

Bonne journée

Pierre Dumas