Discussion :

[madrunner]

Bonjour à tous

La question est toute bête et me parait tellement simple que je suis étonné de ne rien trouver sur ce sujet...
Je désire faire une somme par rapport aux cellules de la colonne sachant que ces dernières peuvent être sous forme de nombre ou de date, mais je ne veux pas que les dates soient comptabilisées.

A1 = 10
A2 = 15-mars
A3 = 15
A4 = somme (25 et non 41738)

Auriez vous une solution?

Merci par avance

Madrunner

[Marc-L]

Bonjour,

bonne problématique car les dates sont en fait un format d'affichage et stockées sous forme de nombres !

Je sèche : je ne vois pas s'il y a moyen par formule de détecter si une cellule est une date ou pas
mais les cadors des formules Excel ne devraient pas tarder à donner leur avis !
L'astuce pourrait consister à additionner les nombres inférieurs à la valeur numérique d'une date plancher;
ou encore mettre un repère dans une colonne libre pour n'additionner que ceux qui ont ou n'ont pas ce repère …
Par contre par code, c'est réalisable sans souci …

[OCJuls]

Bonsoir,

Le problème pourrait se régler facilement avec la formule CELLULE à mettre dans une colonne adjacente. Il est possible de sortir le format, celui qui nous intéresse est "D1", il suffit ensuite de faire une SOMME avec en condition ce D1.
En B1 tu met

Code :

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

=CELLULE("format";A1)

Copie vers le bas de la formule
et enfin en C1

Code :

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

=SOMME((A1:A3)*(B1:B3<>"D1"))

à valider avec Maj+Ctrl+Entrée
Il ce peut que ce ne soit pas D1 en fonction du format de date mais la formule est modifiable en fonction

[madrunner]

Bonsoir OCJuls et Marc-L

Tout d'abord un grand merci, c'est ma première expérience sur un forum et je suis agréablement surpris

OCJuls; ta solution "cellule" est intéressante est remplie mes contraintes, toutefois je suis dans ce cas obligé d'ajouter 2 colonnes pour renseigner la condition "somme.si"...
Si je n'ai pas d'autre solution j'opterai pour celle-ci en cachant les 2 colonnes supplémentaires afin de ne pas gêner la lisibilité du tableau qui comportent bien sûr beaucoup plus de colonnes que la simplification que j'ai donné.

Merci Encore.

Je ne clos pas la discussion au cas ou...

[Menhir]

toutefois je suis dans ce cas obligé d'ajouter 2 colonnes pour renseigner la condition "somme.si"...

Malheureusement, la fonction cellule n'est pas vraiment compatible avec les fonctions matricielles (j'ai testé) ce qui empêche de l'inclure directement dans la somme pour tout mettre dans une seule cellule.
Mais je ne comprends pas pourquoi tu dis 2 colonnes ? La solution de OCJuls n'en ajouter qu'une...

[madrunner]

Malheureusement, la fonction cellule n'est pas vraiment compatible avec les fonctions matricielles (j'ai testé) ce qui empêche de l'inclure directement dans la somme pour tout mettre dans une seule cellule.
Mais je ne comprends pas pourquoi tu dis 2 colonnes ? La solution de OCJuls n'en ajouter qu'une...

Bonjour Menhir

A moins que je n'ai pas mis correctement en application la solution d' OCJuls, moi je me me retrouve avec :
Colonne A1 = data
Colonne B1 =CELLULE("format";A1)
Colonne C1 =SOMME((A1:A4)*(B1:B4<>"D1"))
Ligne 6 = somme automatique
A B C
1 200.00 P2 1200
30-nov-13 D1 0
31-mars-14 D1 0
1 500.00 P2 1500

86 037.00 2700

(désolé je n'ai pas su mettre sous forme de tableau...)

Par contre je t'accorde que je n'ai pas comprise en C1 l’intérêt de mettre A1:A4....
J'ai bien tenté de mettre le formule en A5 mais j'avais comme résultat #Valeurs!#

[Menhir]

A moins que je n'ai pas mis correctement en application la solution d' OCJuls, moi je me me retrouve avec :
[...]
Colonne C1 =SOMME((A1:A4)*(B1:B4<>"D1"))
[...]
Par contre je t'accorde que je n'ai pas comprise en C1 l’intérêt de mettre A1:A4....

Effectivement, il y a quelque chose qui t'a échappé dans l'explication de OCJuls : la somme est à mettre dans une seule cellule et non pas dans toutes les cellules de la colonne C.
Et elle doit être validée en tant que formule matricielle (d'où le A1:A4), avec un Ctrl + Shift + Entrée et non pas avec un simple Entrée comme une formule classique.

Cela dit, dans ce cas, un SOMME.SI aurait aussi bien fait l'affaire :

Code :

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

=SOMME.SI(B1:B4;"<>D1";A1:A4)

Autre solution : mettre la condition dans les formules de la colonne B.
Ecrire en B1 :

Code :

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

=SI(CELLULE("format";A1)="D1";"";A1)

La recopier dans toutes les cellules de la colonne B.
Ensuite, il suffit d'une SOMME classique pour obtenir ton résultat.

[madrunner]

2 réponses en 1... bravo Menhir

J'ai compris la formule matricielle et la solution somme.si est tout aussi efficace.
Du coup, ça m’oriente vers une autre idée qui serait parfaite, en tenant compte que la cellule adjacente est pleine ou vide.
Je m'explique; en réalité, à un moment, sur une même ligne j'ai une cellule avec un nombre ensuite une cellule avec une date de validation, sur la ligne suivante idem mais sans date de validation.

Du coup ma somme globale pourrait indiquer
=SOMME.SI(B1:B4;"nonvide";A1:A4)

Bien sûr le "nonvide" est une vue d'esprit

Ma question est donc : comment gérer ce "nonvide" dans une formule et ainsi ne pas avoir à ajouter le format ?
Je suis conscient que le postulat de base a évolué et que vous n'aviez pas toutes les indications pour m'orienter vers cette solution.
La simplification d'un problème peut engendrer un rétrécissement du champs des possibilités....

[Philippe Tulliez]

Bonjour,
Il faut tout de même être très prudent quant au résultat renvoyé par la formule Cellule avec comme argument format et surtout avec les dates.
En effet si la date est formatée avec jjj jjjj ou mmm mmmm le résultat renvoyé est S ce qui pourrait sembler logique puisqu'il évalue un format.
Soit dit en passant, l'organisation de la liste de données est très douteuse pour mélanger dans une même colonne des dates et des nombres. Mais peut-être est-ce un tableau de synthèse ?

[Menhir]

Je ne vois pas trop l'intérêt de ce que tu envisages de faire. Il faudrait que tu décrives un peu mieux ce que tu comptes mettre dans la colonne B.

[Winnt]

Bonjour,

Ton souci pourrais assez facilement se régler en créant une fonction vba que tu pourrais appeler dans ta feuille excel comme une fonction native d'excel.
Du coup, tu disposerais de la puissance de vba et de la "simplicité" d'utilisation des formules.

Voici un code fonctionnel pour des dates en français

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Public Function somme_sans_date(Donnees As Range) As Double
'
Dim total, cellule As Range
total = 0
For Each cellule In Donnees.Cells
     cellule.Select
     If cellule.NumberFormat <> "m/d/yyyy" Then
          total = total + cellule.Value
     End If
Next cellule
somme_sans_date = total
'
End Function

et dans ta cellule de somme tu saisie : =somme_sans_date(<la plage voulue>)

[madrunner]

Philippe ; en effet c'est un tableau de synthèse avec des sommations intermédiaires.

Pour être plus claire

	20-févr-14	1 500	25/02
	31-mars-14	500	04/05
Toto	2000
	20-févr-14	2 000	28/02
	31-mars-14	500	08/05
	20-avril-14	2 000
Titi	4500
		6500	4500
		facturé	encaissé

J'ai reconstruis le tableau en fonction de l'idée de Menhir de somme.si
Car si il y a une date de validation alors le nombre doit être pris en compte.

Winnt, je te remercie pour ta solution en VBA, mais comme tu peux le constater je ne maitrise déjà pas des fonctions de base alors le VBA

[Marc-L]

Pour du VBA, ne pas oublier la fonction IsDate ‼ Select inutile …

[Menhir]

Pour du VBA, ne pas oublier la fonction IsDate ‼ Select inutile …

Vu la diversité des formats de date, le IsDate est effectivement plus fiable.
Il y a aussi la variable total qui est inutile. Il suffit de mettre somme_sans_date à la place et de supprimer la ligne 11.

Ce qui donne :

Code :

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

1
2
3
4
5
6
7
8
9
Public Function somme_sans_date(Donnees As Range) As Double
 
Dim cellule As Range
somme_sans_date = 0
For Each cellule In Donnees.Cells
     If not IsDate(cellule) Then somme_sans_date = somme_sans_date + cellule.Value
Next cellule
'
End Function

[Philippe Tulliez]

Bonjour,

Philippe ; en effet c'est un tableau de synthèse avec des sommations intermédiaires.

En regardant ton message, je me demande pourquoi tu as trois colonnes avec la même date mais deux formats différents
Ce tableau de synthèse provient d'où ?
Si c'est d'un tableau Excel, il suffirait peut-être de le retravailler, si cela vient d'une exportation de demander au service qui l'a produit de modifier l'exportation ou la requête afin de pouvoir travailler avec les outils natifs d'excel (Sous-total ou Tableau Croisés Dynamiques).

[madrunner]

Bonjour,

En regardant ton message, je me demande pourquoi tu as trois colonnes avec la même date mais deux formats différents
Ce tableau de synthèse provient d'où ?
Si c'est d'un tableau Excel, il suffirait peut-être de le retravailler, si cela vient d'une exportation de demander au service qui l'a produit de modifier l'exportation ou la requête afin de pouvoir travailler avec les outils natifs d'excel (Sous-total ou Tableau Croisés Dynamiques).

Bonjour Philippe; en fait j'ai repris les données de mon tableau excel (j'avoue, j'ai mis un format différent pour orienter la solution vers somme.si "nonvide" plutôt que format). Je peux en effet me replonger dans les outils natifs d'excel (Sous-total ou Tableau Croisés Dynamiques) ou VBA mais la question me paraissait si bête que je pensais pouvoir passer par une fonction plus basique.

[Philippe Tulliez]

Bonjour,
Une liste de données bien organisée doit avoir des colonnes avec de valeurs de même nature. On ne mélange pas dans une même colonne des dates et des nombres ou des nombres et du texte.

[madrunner]

Oui mais, tout le monde n'est pas formateur et pourtant il faut avancer... Il faut seulement apprendre de ses erreurs pour ne pas les refaire, mais là je dois faire avec.

[Philippe Tulliez]

Bonjour,

Oui mais, tout le monde n'est pas formateur et pourtant il faut avancer... Il faut seulement apprendre de ses erreurs pour ne pas les refaire, mais là je dois faire avec.

C'est bien pour cela que je te le signale.
Il vaut dès fois mieux repenser le concept que de construire des formules boiteuses ou des usines à gaz.