Discussion :

[chpierro62]

Salut les champions !

Voici un problème de taille.. Cela fait maintenant 3 jours que je me bats avec un SOMMEPROD

Je m'explique : je souhaite effectuer une somme en fonction de deux critères. Jusque là tout va bien, voici la formule utilisée :

=SOMMEPROD((自制计划模板表全!K9:X9=KPI!BY63)*(自制计划模板表全!K1:X1=KPI!CA62)*(自制计划模板表全!K10:BK10))

Problème : mon résultat est toujours égal à 0 alors que je devrais retrouver 20.

J'ai tenté de modifier les formats de cellules, en vain...

Je cherche juste à obtenir le temps total de travail (ligne "Work hour") en fonction de deux critères : la machine (ici "Assembly") et la semaine (ici "1").

Merci d'avance pour votre (précieuse) aide !

Bonne journée,

PA

Ps: ci-joint deux screenshots des feuilles concernées.

[papouclo]

Bonjour,
Les plages intervenant dans les facteurs des produits additionnés à l'aide de la fonction SOMMEPROD doivent contenir le même nombre de cellules.
Or les deux premiers facteurs mettent en oeuvre des plages de 14 cellules ( K9:X9 et K1:X1 ) alors que le troisième facteurs fait appel à une plage de 53 cellules (K10:BK10).
Cordialement
Claude

[chpierro62]

Merci pour ta réponse Claude !

Donc comment puis-je faire ? As-tu une idée s'il te plait ?

Merci !

[chpierro62]

En fait, je pense que je vais devoir passer par une macro VBA. Cependant, ce n'est vraiment pas mon fort... Pouvez-vous m'aider s'il vous plait ?

Explications : je cherche à calculer le total de toutes les lignes intitulées "work hour" seulement pour les cellules contenant "Assembly" et pour chaque semaine.

> C'est donc un calcul par machine (ici "Assembly") pour chaque semaine.

Mon tableau s'étend sur K7:IU1881

Need help, je deviens fou...

MERCI MERCI MERCI !!!!!

[papouclo]

Re,
Je vais appeler feuil1 la feuille 自制计划模板表全 car avec la balise code les caractères ne conservent pas leur aspect et sont remplacés par un code.
De façon à respecter la règle des plages ayant même nombre de cellules, on peut :
- soit "minorer" la dernière plage, ce qui donnerait la formule :

Code :

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

=SOMMEPROD((feuil1!K9:X9=KPI!BY63)*(feuil1!K1:X1=KPI!CA62)*(feuil1!K10:X10))

- soit "majorer" les deux premières plages ce qui donnerait la formule :

Code :

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

=SOMMEPROD((feuil1!K9:BK9=KPI!BY63)*(feuil1!K1:BK1=KPI!CA62)*(feuil1!K10:BK10))

J'aurais plutôt tendance à utiliser la seconde méthode de façon à avoir une formule réutilisable pour un autre numéro de semaine qu'en KPI!CA62.
Au cas où il y aurait des risques que des cellules "rajoutées" introduisent une erreur, on peut approcher au plus près la situation précédente tout en respectant la règle énoncée avec la formule :

Code :

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

=SOMMEPROD((feuil1!K9:BK9=KPI!BY63)*(COLONNE(feuil1!K9:BK9)<25)*(feuil1!K1:BK1=KPI!CA62)*(feuil1!K10:BK10))

ce qui annule dans la somme finale tous les termes qui pourraient correspondre à des colonnes situées après la colonne X.

Mais j'ai bien peur que ces formules proposées renvoient aussi 0; la formule utilisée pourrait s'écrire :

Code :

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

=SOMMEPROD((feuil1!K9:BK9="Assembly")*(feuil1!K1:BK1=1)*(feuil1!K10:BK10))

Or, dans la copie d'écran de la feuille feuil1 , toutes les cellules de la ligne 1 à partir de la cellule E1 semblent vides et ne peuvent dans ce cas pas être égales à 1
(à moins qu'elles soient remplies du n° de la semaine écrit en caractères de couleur blanche). Le résultat nul s'explique parfaitement.

D'autre part, si tu veux totaliser les heures de travail de la machine Assembly durant la semaine 1, pourquoi utliser la plage de cellules K1:X1 et pas seulement la plage K1:Q1 ?
Es-tu sûr que le résultat attendu soit pour la semaine 1 le nombre 20 ? Quelle période appelles-tu semaine 1 et où sont écrits dans la feuille feuil1 les numéros de semaine ?

Au cas où la semaine 1 correspondrait aux colonnes K : Q avec le 1 écrit ligne 3, on peut essayer la formule :

Code :

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

=SOMMEPROD((DECALER(feuil1!A9;;EQUIV(KPI!CA62;feuil1!3:3;0)-1;1;7)=KPI!BY63)*DECALER(feuil1!A10;;EQUIV(KPI!CA62;feuil1!3:3;0)-1;1;7))

mais le résultat devrait être 12 et non pas 20.
Cordialement
Claude

[chpierro62]

Cela fonctionne en effet mais il me renvoie parfois à "valeur", je ne sais vraiment pas pourquoi. De plus, cela ne fonctionne que pour la ligne de "general assembly", or je dispose de plusieurs ensembles qui s'étendent jusqu'à la ligne 1181 (voir PJ).

J'arrive toutefois à l'adapter pour les autres machines .

Claude, tout d'abord merci pour ton aide, c'est fort appréciable. Ci-joint mon fichier, je pense que ça sera beaucoup plus simple, aussi bien pour toi que pour moi. Tu y trouveras la feuille "自制计划模板表全" et la feuille "KPI".

Je cherche en fait à compléter le tableau présent dans la feuille "KPI" (tu le trouveras facilement ). Ce tableau servira à alimenter les indicateurs présents juste à côté, en fonction de la feuille "自制计划模板表全".

MERCI MERCI MERCI !

[chpierro62]

Clairement, c'est exactement ce que je cherchais Claude, sauf que je veux cela pour l'ensemble de mon tableau, et non que pour l'ensemble "General assembly". Jette un coup d'oeil à la pièce-jointe, tu comprendras mieux je pense.

A savoir que les lignes "Machine" et "Work hour" sont toutes décalées de 5 lignes (boucles ?).

Merci !!!!!

[papouclo]

Bonjour,
Les messages d'erreur proviennent des cellules du tableau de la feuille 自制计划模板表全 contenant des points d'interrogation , ce qu'Excel n'aime pas
lorsqu'on lui demande d'effectuer un calcul en utilisant un opérateur (* , + , - , / ).
3 solutions possibles :
- supprimer les causes d'erreur et enlever les points d'interrogation.
- utilser une formule en SI testant si le contenu des cellules de la ligne Work est bien numérique, mais cela complique fortment la formule.
- utiliser une MFC écrivant en caractères blancs le message des cellules contenant une erreur.
La 3ème proposition est la solution employée sur la feuille KPI(2) du classeur joint.

Pour généraliser la formule au tableau, j'ai écrit en colonne CZ des index repérant le rang des machines : 1 en CZ63, 2 en CZ65, 3 en CZ67 , ... , 16 en CZ93.
La formule écrite en CA63 est alors :

Code :

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

=SOMMEPROD((DECALER(feuil1!$A$1;5*$CZ63+3;7*CA$62+3;1;7)='KPI'!$BY63)*DECALER(feuil1!$A$1;5*$CZ63+4;7*CA$62+3;1;7))

à recopier sur la droite jusqu'en CX63
puis à recopier vers le bas en ligne 65 , 67 , 69 ... jusqu'en bas du tableau.

Remarques :
D'une part , cette colonne d'index peut ensuite être masquée ; d'autre part, elle a été placée en colonne CZ pour que l'ensemble du tableau soit visible et compréhensible
mais au cas où le tableau prendrait en compte un plus grand nombre de semaines de l'année, cette colonne peut bien sûr se déplacer. Enfin, cette colonne d'index permet une formule bien plus simple
pour le décalage des zones de calcul de 5 lignes en 5 lignes.

Cordialement
Claude

[papouclo]

Re,
Comme ton fichier était enregistré en version allant jusqu'à 2003, je n'avais pas fait attention au fait que la version utilisée était 2007.
Ce qui permet, pour le problème des messages d'erreur, de remplacer l'ancienne formule par :

Code :

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

=SIERREUR(ancienne formule ; "")

[chpierro62]

Merci Claude pour ton aide ! Cependant, cela ne fonctionne que pour la machine "Assembly" et non pour les autres, le résultat est toujours égal à 0 .

As-tu une solution ?

Ci-joint le fichier à jour, j'ai mis quelques exemples de machines dans la feuille "自制计划模板表全" afin de t'apercevoir que cela ne fonctionne toujours pas.

Nous devrions normalement obtenir ces résultats dans la feuille "KPI (2)" :

• En semaine 1 : 12h d'Assembly.
• En semaine 2 : 8h de Welding, 4h de Vertical CNC Machine, 12h de 2.5 M Vertical Lathe.
• En semaine 3 : 16h + 2h de Welding, 2h d'Assembly.

Or nous n'avons que les heures pour la machine "Assembly", les autres restent à 0..

Une énorme MERCI à toi Claude, c'est vraiment super gentil...

[denandg]

Bonjour,

Pour m'assurer d'une facilité dans SOMMEPROD, j'utilise généralement les champs nommés donc si dans une liste tu as une colonne avec le Nom du Vendeur, le Nom de la ville et finalement le Montant de vente. Tu peux faire comme ceci dans excel Ruban Formule section gestionnaire de noms et tu clique depuis sélection et finalement ligne du haut. Ce qui fait que mes champs nommées débute sur la ligne numéro 2 et se termine à la ligne 53232 par exemplt

Par la suite une formule comme Sommeprod((Vendeur=A3)*(Ville=B3)*(Montant)) te donnera un résultat qui évite des erreur. Tu pourras toujours reviser la liste.

Un autre possibilité d'erreur serait que ton champ de réponse ne soit pas un vrai nombre dans ce cas, je procède avec la formule dans une colonne libre =estnum(C3) et je copie vers le bas pour vérifier si toute les cellules sont numérique

[chpierro62]

Excuse-moi mais je n'ai pas tout compris là...

[papouclo]

Bonjour,
La dernière formule proposée ne risquait pas de servir pour une autre machine que celle nommée Assembly avec l'erreur d'analyse faite.
A ma décharge, dans le premier classeur " plan templet_v4 " envoyé, dans le tableau de Feuil1, il y avait seulement la machine Assembly en ligne 9
et seulement la machine 130 Boring Machini en ligne 14 d'où l'intuition désastreuse d'une ligne par machine.
Il faut se méfier, dans des exemples factices, de la dissimulation involontaire de la complexité du problème par quelques données mal disposées
et craindre l'induction chez une personne extérieure n'ayant qu'une image tronquée de la situation.

J'ai rajouté dans le module Ê9 du classeur joint (plan templet_v4 ter ) une fonction pesonnalisée dont le code est ci-dessous :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Function dureetravail ( machine As Range ,  numsem As Range ) As Long
Dim li As Long, co As Long
    With Sheets(1)
        For li = 9 To 1179 Step 5
            For co = 7 * numsem.Value + 4 To 7 * numsem.Value + 10
                If .Cells(li, co) = machine Then
                    dureetravail = dureetravail + .Cells(li + 1, co).Value
                End If
            Next co
        Next li
    End With
End Function

Avec cette fonction, la formule écrite dans la cellule CA63 de la feuille KPI est :

Code :

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

=SIERREUR(dureetravail($BY63;CA$62);"")

à recopier vers la droite jusqu'en CX63 et vers le bas lignes 65, 67, 69, ...

On retrouve les résultats attendus dans ton message :

• En semaine 1 : 12h d'Assembly.
• En semaine 2 : 8h de Welding, 4h de Vertical CNC Machine, 12h de 2.5 M Vertical Lathe.
• En semaine 3 : 18h de Welding, 2h d'Assembly.

Mais il m'a fallu corriger pour Welding la cellule BY91 (ou plutôt W181) qui contenait une chaine de 9 caractères ( " Welding " avec un espace en début et fin de chaine)
alors que les cellules de la feuille Feuil1 contenaient une chaine de 7 caractères ("Welding").
Pour éviter une nouvelle mésaventure de ce genre, une solution est d'assujettir les cellules des lignes 9 , 14 , 19 , ... , 1179 de la feuille Feuil1 à une validation de données
en utilisant pour liste la plage KPI ! I181 : X181.
Cordialement
Claude

[chpierro62]

Claude, je n'ai qu'un mot à dire : BRAVO !!!!!!

Tu m'as impressionné sur ce coup-là, tout est parfait, aussi bien les explications que la solution !

UN GRAND MERCI !!!!!!!!!!