Aide pour des conditions SI imbriquées

[do958]

Bonjour à tous,

je suis en stage et mon responsable me demande de trouver un formule qui me permet de calculer un budget. pourriez vous m'aider? j'ai quelques bases en excel mais là je ne trouve pas du tout la solution...
Je vous mets le fichier en pièce jointe.

Explications des différentes feuilles:
-Feuille "données" = Données entrée manuellement. Le nom de l'entreprise, le statut et le nombre de serveur installé dans chaque entreprise (+) et le nombre de serveur déinstallé (-) répartis par serveur virtuel ou physique et par OS (windows, linux...) et la date du projet.

-Feuille "tarif": Données entrée manuellement. Permet de connaitre le coût d'un serveur lorsque l'entreprise achète et le coût lorsqu'on l'enlève. Coût variable selon l'année (2014 ou 2015)

-Feuille "budget" : permet grâce à une formule de connaitre le montant total de serveur acheter ou enlever par mois ( faire une différence entre les serveurs entrant et les serveurs sortant) par entreprise et si le serveur est physique ou virtuel.

Je ne peux pas modifier la forme de la feuille "données" mais pour les autre pages oui.
Je dois donc créer la formule.

Je pensais à faire une formule mais impossible de la faire je vous la détaille (formule entrée dans la case B4 de la feuille "budget):

Si dans la colonne A de la feuille "données" = "Entreprise - 1",
Si dans la colonne B de la page "données" = "7" ou "8" alors on ne prend pas en compte,
sinon on continue la formule
Si la date qui se trouve dans la colonne AQ de la page "données" est entre le 1/01/2014 et 31/01/2014
alors on fait un calcule
on fait la somme de la colonne c5:c11 et la somme de cette colonne w5:11 multiplier par la case B6 qui se trouve dans la feuille"tarif" - la somme de la colonne D511 et la somme de la colonne x5:x11 multiplier par la case C qui se trouve dans la feuille "

Normalement le résultat trouvé doit être 82€
car :
- la date choisit est entre le 1 et le 31 janvier
- On ne choisit que l'entreprise "Entreprise - 1"
- On prend tous les statuts sauf le "7- Archived" et le "8-cancelled"
- On ne prend que les windows virtuels

Merci de m'aider...

 

[titiborregan5]

Re : Aide pour des conditions SI imbriquées

Bonjour Do,
si tu ne peux pas changer la forme de ta feuille données je ne vois pas...
Par contre, tu peux créer une autre feuille qui va aller impacter la feuille données mais qui sera plus exploitable pour tes données.

SInon la formule somme.si.ens ou nombre.si.ens ne répond pas à tes attentes?

Bon courage

Tibo

 

[Dugenou]

Re : Aide pour des conditions SI imbriquées

Bonjour,
Un essai avec une formule indigeste (6 lignes) mais qui donne le résultat attendu et qui se recopie.
Il faudra modifier pour les serveurs physiques.
à base de somme.si.ens pour les quantités et index+ 2equiv pour le tarif.

on pourrait simplifier si on mettait les chiffres 7 et 8 dans une autre colonne
et si le tarif pour enlever était toujours celui pour ajouter mais en négatif : ce qui semble le cas pour ta table ... (ah mais non si on enlève pas la même année qu'on installe ça marche plus)

J'ai détaillé le calcul dans des cellules en dessous du tableau : à ta disposition pour expliquer si besoin

Cordialement
Edit : Titi tu avais raison comme toujours !

 

[do958]

Re : Aide pour des conditions SI imbriquées

Bonjour,

Merci pour vos réponses !!

@Dugenou : je suis en train de regarder ton fichier, je t'avoue que je ne comprends pas grand chose...
Peux tu m'expliquer à quoi sert la formule "Somme.Si.ENS" et "INDEX" stp

merci

 

[Dugenou]

Re : Aide pour des conditions SI imbriquées

Bonjour,
Somme.si.ens permet de calculer une somme avec plusieurs conditions. La syntaxe est :
somme.si.ens(plage sur la quelle on fait la somme; plage de la première condition, condition1; plage de la 2eme condition; condition 2etc...)
donc dans ton cas j'ai commencé avec : somme.si.ens (C5:C11 de la feuille données; A5:A11 de la feuille données; "entreprise-1"; B5:B11 de la feuille données; différent de 7 archived; même plage; différent de 8 cancelled) pour trouver les + puis même chose mais en faisant la somme sur la plage D511 pour trouver les -

ensuite avec la formule index(plage; N° de ligne; N° de colonne) on recherche le tarif
Le N° de ligne est obtenu en recherchant l'année dans la colonne des années avec la fonction equiv(valeur cherchée, zone de recherche;correspondance extacte)
Le N° de colonne en recherchant le type de serveur dans la ligne 4 du tarif avec la même fonction Equiv
Regardes l'aide sur chacune de ces fonctions et essayes les une à une.
Si ça ne va pas je te ferai le détail pas à pas dans le fichier

 

[do958]

Re : Aide pour des conditions SI imbriquées

Bonjour,

Merci pour les explications.
Dans le fichier que je vous m'aviez envoyé, j'ai pris chaque formule et je les ai soit additionné ou soustrait pour me donner le bon résultat. Ca m'a permis de comprendre les formules.

Je crois qu'il manque un bout de formule dans la case "b19" dans la page "budget".
Vous ne prenez pas en compte la deuxième colonne des servers virtuels windows. (colonne w de la page "données").
Il faut additioner la colonne C et la colonne w.

Je n'arrive pas a insérer dans votre formule la condition pour la colonne W...

 

[Dugenou]

Re : Aide pour des conditions SI imbriquées

Bonjour,
En effet je n'ai fait le calcul que pour les serveurs virtuels du datacenter1
s'il faut ajouter le datacenter2 il faut écrire la formule une seconde fois et là ça devient vraiment illisible.
Dans la PJ je te propose une autre disposition des données de base (puisque tu nous dis que ces données sont entrées à la main) qui rend les calculs bien plus aisés à écrire.
On utilise un Tableau où on ne saisit que les données utiles. J'ai installé des listes déroulantes pour faciliter la saisie.
Pour entrer une nouvelle ligne : se placer sur la dernière cellule remplie en bas à droite et appuyer sur la touche tab : on retrouve alors les validations. Les formules (ainsi que le tcd) tiennent compte des nouvelles données.
La déclaration en Tableau permet d'utiliser les noms de colonnes dans les formules : cela devient compréhensible et facilement vérifiable (attention cependant : les formules ne se recopient pas à droite : les noms de champs changent aussi : il faut à chaque fois coller la formule et corriger les cellules de référence de date : ce que j'ai fait dans le tableau budget).
Par un TCD on pourra réafficher les données comme dans le fichier d'origine : voir feuille TCD avec un peu de mise en forme : à chaque ajout de données il faudra actualiser le TCD (clic droit dans le TCD et actualiser).
Cordialement

 

[Dugenou]

Re : Aide pour des conditions SI imbriquées

re,
Et en continuant dans cette voie on arrive alors quelque chose de beaucoup plus simple : on calcule le cout pour chaque ligne de donnée et on fait un TCD pour remplacer la feuille budget.

A approfondir si ça te convient

 

[do958]

Re : Aide pour des conditions SI imbriquées

re,

Merci pour votre réponse, mais le fichier des données de base ne peut pas être modifié même la disposition n'est pas envisageable...
Le fichier est utilisé par un autre service, je n'ai pas l'autorisation de le modifier...

Par contre, j'ai quand même essayer d'utiliser vos formules. J'ai modifié la formule pour qu'elle soit cohérente avec le fichier mais le résultat n'est pas correct.

La formule est dans la page "Feuil1" case "B40"

Je vous mets en pièce joint le fichier.

Merci

 

[Dugenou]

Re : Aide pour des conditions SI imbriquées

Bonjour,
le decaler doit concerner la première colonne à gauche des premières données nb+ donc :
=SOMME.SI.ENS(DECALER(Cockpit!$F$16:$F$300;;EQUIV(Feuil1!$A40;Cockpit!$G$14:$AI$14;0))
vous faisiez des calculs en prenant les dates au lieu de prendre les nb
en ligne 2 les dates ne sont pas le 1er de chaque mois : j'ai corrigé
le statut 7 n'est archived mais achieved : 7-Achived
voir en pj si on a bien les éléments pour le datacenter1
j'ai adapté les formules pour les deux premiers clients 501 et 502, physique et virtuel

Cordialement

 

[do958]

Re : Aide pour des conditions SI imbriquées

Merci ! ça fonctionne !
Donc pour ajouter le second datacenter, je copie la formule et je change avec les données du second datacenter?

 

[Dugenou]

Re : Aide pour des conditions SI imbriquées

Oui, il faut changer la colonne du décaler et celle des equiv
je ne pourrai pas reprendre avant jeudi

à l'arrache formule pas vérifiée mais qui doit fonctionner en pj

(somme.si(datacenter1)+somme.si(datacenter2))*(equiv.... tarif+)+(somme.si(datacenter1)+somme.si(datacenter2))*(equiv.... tarif-)

 

[do958]

Re : Aide pour des conditions SI imbriquées

Bonjour,

J'ai reussi à copier la formule pour insérer le dexuième datacenter =).

Par contre pour les "databases" j'ai pas du tout réussi, ça me met "NA"

=(SOMME.SI.ENS(DECALER(Cockpit!$BR$16:$BR$300;;EQUIV(differentiel!$A54;Cockpit!$G$14:$AI$14;0));Cockpit!$A$16:$A$300;differentiel!$A$38;Cockpit!$BS$16:$BS$300;">="&differentiel!B$2;Cockpit!$BS$16:$BS$300;"<"&differentiel!D$2;Cockpit!$C$16:$C$300;"<>"&"8-Cancelled";Cockpit!$C$16:$C$300;"<>"&"7-Achived")+SOMME.SI.ENS(DECALER(Cockpit!$AH$16:$AH$300;;EQUIV(differentiel!$A54;Cockpit!$AI$14:$AR$14;0));Cockpit!$A$16:$A$300;differentiel!$A$38;Cockpit!$BS$16:$BS$300;">="&differentiel!B$2;Cockpit!$BS$16:$BS$300;"<"&differentiel!D$2;Cockpit!$C$16:$C$300;"<>"&"8-Cancelled";Cockpit!$C$16:$C$300;"<>"&"7-Achived"))*INDEX(tarif!$B$6:$L$7;EQUIV(ANNEE(differentiel!B$2);tarif!$A$6:$A$7;0);EQUIV(differentiel!$A54;tarif!$B$4:$L$4;0))+(SOMME.SI.ENS(DECALER(Cockpit!$F$16:$F$300;;EQUIV(differentiel!$A54;Cockpit!$G$14:$AI$14;0)+1);Cockpit!$A$16:$A$300;differentiel!$A$38;Cockpit!$BS$16:$BS$300;">="&differentiel!B$2;Cockpit!$BS$16:$BS$300;"<"&differentiel!D$2;Cockpit!$C$16:$C$300;"<>"&"8-Cancelled";Cockpit!$C$16:$C$300;"<>"&"7-Feuil1!Achived")+SOMME.SI.ENS(DECALER(Cockpit!$AH$16:$AH$300;;EQUIV(differentiel!$A54;Cockpit!$AI$14:$AR$14;0)+1);Cockpit!$A$16:$A$300;differentiel!$A$38;Cockpit!$BS$16:$BS$300;">="&differentiel!B$2;Cockpit!$BS$16:$BS$300;"<"&differentiel!D$2;Cockpit!$C$16:$C$300;"<>"&"8-Cancelled";Cockpit!$C$16:$C$300;"<>"&"7-Achived"))*INDEX(tarif!$B$6:$L$7;EQUIV(ANNEE(differentiel!B$2);tarif!$A$6:$A$7;0);EQUIV(differentiel!$A54;tarif!$B$4:$L$4;0)+1)

 

[titiborregan5]

Re : Aide pour des conditions SI imbriquées

C'est pas humain de faire des formules comme ça!!!

 

[do958]

Re : Aide pour des conditions SI imbriquées

C'est vrai :/