[Résolu] Somme condionnelle multi variable dont couleur

[RIANI]

Bonjour à tous,

A la suite d'un premier post, ma demande a évolué c'est pourquoi j'effectue ce nouveau post.

Je dispose d'un classeur avec les onglets suivant :

• budget par activité : celui ci détail l'ensemble des financements fléchés par activité et par financeur

• Encaissement : celui ci détail l'ensemble des encaissements reçus et à recevoir par financement et financeur

• encaissement par activité : onglet ou j'aimerai obtenir la somme des encaissements par activité en fonction des onglets 1 et 2

L'objectif de ce classeur est de savoir combien chaque activité à généré d'encaissement à aujourd'hui.
La difficulté est que un financeur apporte plusieurs financements, et que un financement peut être lié à une ou plusieurs activités. C'est pourquoi pour les distinguer j'ai mis en place un code couleur qui permet de savoir combien d'activités a un financeur (FIN1, FIN2, FIN3, FIN4...).
L'onglet encaissement est donc généré par financement et par financeur, une ligne peut donc comporter le financement de plusieurs activité (j'ai précisé les activités concernées en colonne C.

Si je résume il faut que je récupère les données encaissées à ce jour (encaissement) puis que je les ventilles à partir de ce qui était prévu (budget par activité) sur les différentes activité.

Par ex :
l'activité 211B pour le financeur "Collectivité - Conseil régional" FIN1 est de 28431,76 sur un financement total de 79244€ (somme du FIN1 sur cette colonne). Dans l'onglet encaissement on constate que ce financeur a versé 55470,8€, le resultat attendu pour le financeur de cette activité serait de (55470,8*(28431,76/79244) = 19902,25€).

J'ai essayé d'être le plus clair possible...

Merci de votre aide.

[jeanmimi]

Bonjour,

L'objectif de ce classeur est de savoir combien chaque activité à généré d'encaissement à aujourd'hui.

Normalement, dans un Tableur, il faut utiliser le Tableau dynamique pour le faire ou tenter de le faire.
Il y a une autre façon de procéder, qui permet plus facilement de faire des Requêtes, c'est de passer par une Base de données, d'autant que, dans l'exemple, les calculs sont simples.

[RIANI]

Bonjour Jeanmimi,
Dans l'exemple que j'ai transmis le tableau croisé dynamique ne peut aucunement répondre à ma demande.

Pour l'aspect base de données et requetes, c'est aussi impossible car l'outil en lui mm est créer sur Calc et qu'il fait parti d'un tout plus important que l'exemple envoyé.

Les calculs sont pas si simple que cela, car comme je l'explique dans mon message la clé d'entré de chaque tableau n'est pas la mm, mais elle n'est pas incompatible.

[jeanmimi]

l'outil en lui mm est créer sur Calc

Ma suggestion était de le créer sur Base pour faciliter ensuite les requêtes.

[RIANI]

Oui j'avais compris jeanmimi, mais ce n'est pas possible car l'outil est plus important que l'exemple que j'ai envoyé qui n'est qu'un extrait de celui ci.

Malgré cela je ne vois pas comment base pourrait mieux gérer que calc mon pb.

C'est sûrement ma connaissance limitée des requêtes sql, mais j'ai plutôt l'impression que je serais plus embêter sur base.
Sauf à tout restructurer et ajouter des champs.

[jeanmimi]

Dans l'ébauche jointe, le Rapport donne une idée du type d'état qui peut être généré.
Pour le suivi des encaissements, la saisie dans la Table dépend de l'ergonomie souhaitée.

[RIANI]

J'ai regardé ton fichier jeanmimi, je suis vraiment pas alaise avec base surtout les formulaires et rapports.
Cependant la ou ça ne va pas c'est qu'un financement d'un financeur (par ex le conseil régional pour 79244€) peut correspondre à plusieurs activités dans le budget, et que ce financement réparti sur plusieurs activités peut évoluer en fonction des ajustements des dépenses par activité.

C'est pourquoi je disais que la variable qui répartirait ces ressources doit se baser sur le taux d'affectation de la table financement par activité.

Enfin, je peux difficilement me séparer du tableur car ces données sont reprises sur le mm outil pour alimenter d'autres onglets qui n'apparaissent pas dans l'exemple.

[jeanmimi]

Trésorier d'une Association, lorsque je recevais un accord de financement pour plusieurs projets, je savais quels étaient les montants pour chaque projet, dont chacun avait sa référence.
Par contre, lors des encaissements, il fallait être plus attentif.
En ce qui concerne le Rapport, il peut être sauvegardé sous forme de feuille Calc.

[RIANI]

Pour moi, quand je rexois l'accord d'un financement je ne sais pas exactement combien je vais mettre par projet ou en tout cas cela peut évoluer au cours de l'année en fonction du suivi budgétaire et des mesures correctrices à appliquer.

En fait j'utilise un système de gestion budgétaire dynamique. Je m'explique, j'ai un budget de structure qui se reparti sur les projets, tous les projets. Ces projets sont composés de charges directes (CD) de MS et fonctionnement, puis de Charges indirectes (CI) de MS et de fonctionnement. Les CI se répartissent sur les projets en fonction d'une clé de répartition dynamique correspondant au temps de travail de chaque collaborateur sur le projet (je simplifie car il y a plusieurs niveaux de CI).

De part ce fait le budget évolue en dépenses et en ressources quand je fais évoluer une CI Ou une CD.

Comme les encaissements par projet son liés au montant prévisionnel affecté j'aimerai pouvoir rendre dynamique cette affectation des encaissements à chaque projet

[jeanmimi]

Si je reprends la problématique initiale

L'objectif de ce classeur est de savoir combien chaque activité à généré d'encaissement à aujourd'hui.

la Requête sur les encaissements, avec l'exemple de deux activités, répond à la question.
La saisie de dates des encaissements permettra de calculer le total de ce qui a été encaissé par activité et par conséquent le reste dû.

[RIANI]

La problématique initiale est plutôt celle du sujet, c'est à dire comment effectuer une somme conditionnelle avec plusieurs variables dont un style de couleur sur Calc.
Dans ton exemple il faut tout saisir manuellement, donc oui infine après avoir tout saisie manuellement il suffit de faire une somme simple.
Mon objectif n'est pas de tout saisir manuellement, mais bien d'automatiser la démarche afin que celle ci fonctionne mm avec 100 projets.

[OOotremer971]

Bonjour,

comment effectuer une somme conditionnelle avec plusieurs variables dont un style de couleur sur Calc.

Uniquement en installant l’extension CountStyle et en se donnant la peine de comprendre comment elle fonctionne.

Le téléchargement de cette extension se fait ici : https://forum.openoffice.org/fr/forum/d ... ?id=130125

Comment installer une extension : https://forum.openoffice.org/fr/forum/v ... 46#p284758

elle permet pour une plage et un style donné de :

• 1 : calculer la somme des cellules
  2 : compter les cellules non vides
  3 : calculer la moyenne
  4 : renvoyer la valeur maximale
  5 : renvoyer la valeur minimale
  6 : compter les valeurs numériques

Un fichier exemple et quelques explications ici : [OXT] Fonction Calc pour compter les styles
Bien entendu, si l'extension n'est pas installée, le fichier exemple ne peut pas fonctionner.

Bon courage

[RIANI]

Bonjour oootremer, j'ai bien countstyle d'installer et je vois comment elle fonctionne.
Dans mon pb countstyle fait partie de ma solution mais n'est pas la solution.

Je n'arrive pas à effectuer une formule intégrant countstyle qui me permettrait de faire ce que je veux (avoir la somme des encaissements par activité en automatique)

Si tu regardes mon fichier exemple tu verras que le pb est plus complexe que de Somme avec countstyle.

[OOotremer971]

Bonjour,

Pas sûr d'avoir tout bien compris à la logique de tes tableaux

Dans ton exemple, tu trouves 68288 euros pour FIN1 et activité 422B :

Riani_1.png

et moi je trouve cette somme pour le FIN2 :

Riani_2.png

Sinon pour les autres lignes tout colle correctement (exemple feuille Budget activité du fichier joint). L'exemple concerne le financeur 1 pour Collectivité – Conseil Régional, je te laisse le soin de le faire pour les autres combinaisons.

Pour les pourcentages colonne B32:B38 :

Code : Tout sélectionner

=INDEX($A$3:$Q$19;EQUIV($A33;$A$3:$A$19;0);EQUIV($B$32;$A$3:$Q$3;0))/C33

Pour total prev :

Code : Tout sélectionner

=INDEX($A$21:$Q$26;EQUIV($A$31;$A$21:$A$26;0);EQUIV($B$32;$A$3:$Q$3;0))

et pour le montant encaissé :

Code : Tout sélectionner

=SOMMEPROD((encaissements.$B$1:$B$41=$B$32);(NON(ESTERR(TROUVE(A33;SUPPRESPACE(encaissements.$C$1:$C$41)))));(encaissements.$S$1:$S$41))*B33

[OOotremer971]

Une variante sans tableau intermédiaire où en sélectionnant un financeur en A22, le tableau s'actualise automatiquement pour ce financeur :

Riani3.png

une formule unique incluant COUNTSTYLE() et qui applique ta règle de calcul. A étirer vers la droite puis vers le bas :

Code : Tout sélectionner

=SIERREUR((SI(COUNTSTYLE(B4;$A$22);INDEX($A$3:$Q$19;EQUIV($A24;$A$3:$A$19;0);EQUIV(B$23;$A$3:$Q$3;0))/SIERREUR(COUNTSTYLE(DECALER($A$4;0;EQUIV(B$23;$B$3:$Q$3;0);16;1);$A$22;1);"");""))*SOMMEPROD((encaissements.$B$1:$B$41=B$23);(NON(ESTERR(TROUVE($A24;SUPPRESPACE(encaissements.$C$1:$C$41)))));(encaissements.$S$1:$S$41));"")

[RIANI]

Bonsoir OoOTremer971,

Dans ton exemple, tu trouves 68288 euros pour FIN1 et activité 422B :
[/code]

effectivement c'est une inversion de nombre.

Je vais regarder le deuxième fichier que tu m'as envoyé.

[RIANI]

Super oootreamer971 on avance...

J'ai regardé ton fichier, le sommeprod doit sommer tous les encaissements à aujourd'hui et non tous les encaissements réalisés et prévu sur l'année. J'ai modifié le sommeprod pour qu'il tienne compte de cette variable (j'ai aussi remplacé les ; par des *) voir PJ.

Par contre pour la fonction trouve j'ai un soucis, celle ci recherche la valeur du code analytique mais confond 322, 322C et 322D. C'est à dire que quand celle ci recherche le code 322, la fonction considère comme vrai la valeur 322C et D alors qu'elle recherche 322 uniquement. Utilisant très peu cette fonction, y aurait il un moyen de lui dire valeur exacte ou de trouver une fonction qui ne ferait pas la confusion entre ces codes analytiques.

[OOotremer971]

Bonjour,

Par contre pour la fonction trouve j'ai un soucis, celle ci recherche la valeur du code analytique mais confond 322, 322C et 322D

C'est un comportement normal.

y aurait il un moyen de lui dire valeur exacte

Théoriquement oui en utilisant les expressions régulières mais je ne parviens pas à le mettre en pratique. L'expression devrait-être : le critère recherché suivit de "\b" genre :

Code : Tout sélectionner

TROUVE($A24&"\b";SUPPRESPACE(encaissements.$C$1:$C$41)) 

cela fonctionne avec l'outil Rechercher & remplacer du menu Édition mais pas dans une formule apparemment.

On pourrait contourner en bricolant un tout petit peu en attendant de trouver mieux, en ajoutant systématiquement une virgule par exemple à tous les codes analytiques concernés par les formules, qu'ils soient seuls dans une cellule ou plusieurs sans omettre le dernier d'une série et ceux qui servent de critère de recherche.

De fait on ne recherchera plus 322 mais 332, et du coup 322D ne sera pas trouvé car il aurait fallu une virgule entre 322 et D (332,D) pour qu'il soit trouvé, ce qui n'est jamais le cas.

A+

[OOotremer971]

Bonjour,

Je viens de regarder ton fichier et je suis interrogatif sur une de tes modifications :

Code : Tout sélectionner

SI(COUNTSTYLE(B4;CELLSTYLE(B4))

ne veux absolument rien dire. Tu demandes à COUNTSTYLE() de te confirmer que le style de B4 est bien le style appliqué à B4 Quelle est la couleur du cheval blanc d'Henri IV ?
Dans l'exemple que je t'ai donné plus haut :

Code : Tout sélectionner

SI(COUNTSTYLE(B4;$A$22)

$A$22 contenait le style FIN1 par exemple et ça permettait donc de ne traiter que les cellules du tableau A4:R19 ayant ce style là (soit FIN1). Bon là j'avoue je suis un peu largué.

Pour ce qui concerne le bricolage pour éviter la confusion des codes analytiques par la fonction TROUVE(), une illustration dans dans le fichier joint en U1:X41 feuille encaissement.

[RIANI]

Bonsoir,

Je viens de regarder ton fichier et je suis interrogatif sur une de tes modifications :

Code : Tout sélectionner

SI(COUNTSTYLE(B4;CELLSTYLE(B4))

Exact j'ai corrigé en supprimant le premier si qui ne servait plus à rien. Dans ton fichier tu avais intégrer un système de liste déroulante qui n'allait pas pour ce que je voulais faire, car il me faut toutes les données dans un seul tableau.

Concernant la fonction trouve couplée à un non et un esterr je t'avouerai que je ne la comprend pas du tout. Je ne vois pas comment elle fonctionne et du coup je n'arrive pas à l'adapter à mon besoin.

Pour la virgule je comprend le principe. Par contre je ne saisi pas vraiment pourquoi les expressions régulières ne fonctionnent pas. Perso je ne connaissais pas mais je trouve la fonctionnalité très interessante.

[OOotremer971]

Concernant la fonction trouve couplée à un non et un esterr je t'avouerai que je ne la comprend pas du tout.

En fait la fonction TROUVE() retourne une erreur (#VALEUR !) lorsque le critère recherché n'est pas trouvé et une valeur numérique correspondant à la position du critère, dans la chaîne de recherche, lorsqu'il est trouvé.
NON(ESTERR()) permet de transformer cette valeur numérique en une valeur booléenne VRAI uniquement lorsque le critère est trouvé, ou FAUX lorsqu'il ne l'est pas, nécessaire pour indiquer à SOMMEPROD() que cette ligne doit, ou ne doit pas, être comptabilisée dans la somme recherchée.

Donc

Code : Tout sélectionner

ESTERR(TROUVE(blablabla))

retourne VRAI si TROUVE() ne trouve pas le critère recherché puisque TROUVE() retourne dans ce cas une erreur.

et

Code : Tout sélectionner

NON(ESTERR(TROUVE(blablabla)))

retourne VRAI si TROUVE() trouve le critère recherché puisque TROUVE() ne retourne pas d'erreur dans ce cas.

A+

[OOotremer971]

Bonjour,

Théoriquement oui en utilisant les expressions régulières mais je ne parviens pas à le mettre en pratique. L'expression devrait-être : le critère recherché suivit de "\b" genre :

Code : Tout sélectionner

TROUVE($A24&"\b";SUPPRESPACE(encaissements.$C$1:$C$41)) 

Je viens de trouver Il ne faut pas utiliser la fonction TROUVE() mais la fonction CHERCHE() à la place :

Code : Tout sélectionner

CHERCHE($A24&"\b";SUBSTITUE(encaissements.$C$1:$C$41;",";" ")) 

Il faut cocher dans les options : Autoriser les expressions régulières dans les formules

RIANI.png

et utiliser la fonction SUBSTITUE() pour remplacer (virtuellement) les virgules par des espaces pour que ça fonctionne correctement.
A+

[RIANI]

Super OOotremer, je regarde ça dès que possible mais à première vu ça a l'air de coller

Merci beaucoup pour ton aide !