Discussion :

[anthonybe]

Bonjour à tous,

j'ai fait une recherche sur le forum sans trouver de post aidant à résoudre mon problème.

J'ai une application java qui génère un fichier excel pour du reporting.
Le fichier excel est composé de différente lignes:
- Une ligne d'en tête contenant le nom de la colonne: Date, Destination , Nb Chaise, Nb Table, Nb Boisson, ...
- D'autre lignes (insérée dynamiquement lors de la génération du fichier) contenant les infos en rapport avec le nom de la colonne: 2008-01-01, Belgique, 1, 2, 3, ...
- une ligne de total utilisant la fonction SUM pour les colonnes Chaise, Table, Boisson.

Aucun problème pour générer ce fichier et utiliser SUM. La ligne total comprend bien la somme.
Mais mon problème est que certaine de ces lignes ne doivent pas être incluses dans la somme. Je ne sais pas comment faire.

Les lignes à ne pas inclure ont une couleur de fond rouge. Puis-je utiliser celà? Dois-je ajouter un "flag" dans une colonne pour "marqué" les lignes à exclure?

PS: Pas de VBA, uniquement une formule à mettre dans les cellules de la ligne de total.

[cafeine]

Hello,

pas de fonction native d'Excel pour les sommes en fonction d'une couleur. En revanche, en faisant une recherche dans la section VBA d'Excel tu trouveras quelques fonctions VBA ...
Si tu as la possibilité de "flagger" tes lignes, regarde l'aide sur la fonction SOMME.SI()

[alsimbad]

une idée en l'air. je ne pense pas qu'il y ait de chiffre négatif dans des expeditions de chaises, donc si tu marque en négatif tes lignes a exclure, tu va pouvoir utiliser une formule genre

Code :

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

1
2
 
=SOMMEPROD((A1:A4>0)*(A1:A4))

[anthonybe]

Hello,

pas de fonction native d'Excel pour les sommes en fonction d'une couleur. En revanche, en faisant une recherche dans la section VBA d'Excel tu trouveras quelques fonctions VBA ...
Si tu as la possibilité de "flagger" tes lignes, regarde l'aide sur la fonction SOMME.SI()

Pas de VBA.
Je me suis renseignée sur SUMIF, celle-ci pourrais faire l'affaire mais est-il possible de cumuler plusieurs critères (voir ma solution plus bas pour comprendre ce que j'entends pas critère)? J'ai lu sur le net que non. Si oui, comment puis-je faire?

une idée en l'air. je ne pense pas qu'il y ait de chiffre négatif dans des expeditions de chaises, donc si tu marque en négatif tes lignes a exclure, tu va pouvoir utiliser une formule genre

Les noms de colonnes sont à titre d'exemple ;-)
Je ne peux malheureusement pas "modifier" les données pour garder leur intégrité.

Entre temps, j'ai trouvé une solution.
A coté de la dernière colonne, je met un flag. Par exemple, la lettre "D".
Dans la cellule contenant la formule, je mets:

Code :

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

=SUM(IF((Q2:Q17<>"D");H2:H17;0))

suivi d'un CTRL+SHIFT+ENTER étant donné que c'est une "Array formula".

Dans la même optique, je peux cumuler plusieurs type de flag en utilisant la formule:

Code :

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

=SUM(IF((Q2:Q17<>"D")*(Q2:Q17<>"G");H2:H17;0))

où les flags sont les lettres "D" et "G". Les lignes flaggées avec ces lettres ne seront pas ajoutées à la somme.

Malheureusement, cette formule est une "Array formula" et le package java que j'utilise pour générer le fichier Excel ne supporte pas les "Array Formula". De ce fait, l'utilisateur qui ouvre le fichier doit s'amuser à faire CTRL+SHIFT+ENTER sur chaque cellule contenant la formule pour afficher le résultat.
Question: Est-il possible de ne pas devoir obliger l'utilisateur à faire les CTRL+ALT+DELETE sur toute les formules?

[anthonybe]

J'ai trouvé également une autre solution: SUMPRODUCT (Désolé, mon excel est en anglais).

Code :

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

=SUMPRODUCT((Q2:Q17<>"D")*H2:H17)

Cette formule me donne la somme de H2:H17 de toutes les lignes n'ayant pas le flag "D" dans la colonne Q.
Cette formule n'étant pas une "Array formula", pas besoin de faire CTRL+SHIFT+ENTER. Par contre, quand j'ouvre le fichier après génération, toute les colonnes avec la formule ont comme résultat: #VALUE!
Je suis obligé de faire F2 (éditer) + Enter, et la valeur s'affiche sans erreur
Si j'évalue la formule, l'erreur se produit à: "Q2:Q17". Je ne comprend pas.

N'y a-t-il pas moyen d'évaluer les formules sans devoir faire F2+Enter ? Ou ma formule est-elle incorrecte/incomplète (Manque de parenthèse ou autre)?

[cafeine]

Question basique : as-tu doublé les "" quand tu fais écrire la formule ?

[anthonybe]

Question basique : as-tu doublé les "" quand tu fais écrire la formule ?

Non je n'ai pas doublé. La formule est tel quel.

Le problème semble venir du package java que j'utilise. Les relations entre l'open source et les formats propriétaire de MS ne sont pas des meilleurs .

Chose bizar:

• Sous OpenOffice Calc, aucun problème. La formule est bien évaluée.
• Sous MS Office, #VALUE! est affiché. Suis obligé de faire F2 + Enter.

La formule est quand même correcte sinon j'aurais une erreur dans les deux tableur, non ?

[anthonybe]

Je me permets de résumé mon problème ainsi que la solution qui a été trouvée. (Ceux qui auraient le même problème apprécieraient).

Exemple de fichier excel (si il y a un moyen de faire un tableau, merci d'éditer mon post )

1. Name -- Value -- Flag
2. Jon -- 1 --
3. Bob -- 2 --
4. Rob -- 3 -- D
5. Mik -- 4 --
6. Sum -- 7

Je considère que:

• "Name" = colonne "A"
• "Value" = colonne "B"
• "Flag" = colonne "C"

La formule à utiliser pour avoir la somme de "Value" de toute les lignes n'étant pas flaggée (càd n'ayant pas la lettre "D" dans "Flag") est la suivante:

Code :

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

=SUMPRODUCT((C2:C5<>"D")*(B2:B5))

• Code :

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

  (C2:C5<>"D")

  définit un critère. Vous pouvez combiner autant de critère que vous souhaitez.

• Code :

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

  (B2:B5)

  définit la zone à additionner
• le signe '*' équivaut à un ET logic. Pour un OU logic, on peut utiliser le signe '+'.

En espérant que cela puisse aider d'autres personnes ou leurs donner une piste vers leur solution.

PS: Voici un des sites où j'ai trouvé de l'info: http://www.ozgrid.com/Excel/sum-if.htm
Il y en à eu d'autre mais je n'arrive pas remettre la main dessus.