A voir également:
- Somme de plusieurs soustraction d'heures
- Formule somme excel colonne - Guide
- Somme si couleur - Guide
- Soustraction excel anglais - Forum Excel
- Somme si date comprise entre ✓ - Forum Excel
- Colissimo heure de livraison après-midi - Forum Consommation & Internet
6 réponses
Vaucluse
Messages postés
26496
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
1 avril 2022
6 414
25 oct. 2010 à 18:40
25 oct. 2010 à 18:40
Bonjour
le problème doit être que les cellules vides donnent des heures négatives qu'excel traite difficilement. Je crois qu'il y a des options que je ne connais pas, mais vous aurez sans doute d'autres info sur ce fil.
En attendant, si cela n'a pas d'incidence, vous pouvez commencez vos formules de calcul par:
=SI(OU(ESTVIDE(Cell1);ESTVIDE(Cell2));0;Cell1-Cell2))
crdlmnt
le problème doit être que les cellules vides donnent des heures négatives qu'excel traite difficilement. Je crois qu'il y a des options que je ne connais pas, mais vous aurez sans doute d'autres info sur ce fil.
En attendant, si cela n'a pas d'incidence, vous pouvez commencez vos formules de calcul par:
=SI(OU(ESTVIDE(Cell1);ESTVIDE(Cell2));0;Cell1-Cell2))
crdlmnt
Patrice33740
Messages postés
8556
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
2 mars 2023
1 779
Modifié par Patrice33740 le 26/10/2010 à 09:31
Modifié par Patrice33740 le 26/10/2010 à 09:31
Bonjour,
Cette affirmation «Mon problème est que lorsqu'il y a des cellules vides, cela ne fonctionne plus et le résultat indique #VALEUR! » est surprenante, lorsque le cellules sont vides, Excel considère la valeur numérique égale à zéro, il ne devrait pas donner #VALEUR!
Par contre lorsque des cellules contiennent du texte (même une chaine vide : "") Excel renvoi l'erreur #VALEUR!
Pour que les cellules contenant du texte soit considérées comme des zéro, il suffit d'utiliser la fonction N() :
Au lieu de
(D4-C4)+(F4-E4)+ etc ...
Taper
(N(D4)-N(C4))+(N(F4)-N(E4))+ etc ...
Cordialement
Patrice
Nicolas dit toujours : « C'est facile quand on connait la réponse ! »
Cette affirmation «Mon problème est que lorsqu'il y a des cellules vides, cela ne fonctionne plus et le résultat indique #VALEUR! » est surprenante, lorsque le cellules sont vides, Excel considère la valeur numérique égale à zéro, il ne devrait pas donner #VALEUR!
Par contre lorsque des cellules contiennent du texte (même une chaine vide : "") Excel renvoi l'erreur #VALEUR!
Pour que les cellules contenant du texte soit considérées comme des zéro, il suffit d'utiliser la fonction N() :
Au lieu de
(D4-C4)+(F4-E4)+ etc ...
Taper
(N(D4)-N(C4))+(N(F4)-N(E4))+ etc ...
Cordialement
Patrice
Nicolas dit toujours : « C'est facile quand on connait la réponse ! »
Patrice33740
Messages postés
8556
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
2 mars 2023
1 779
26 oct. 2010 à 09:42
26 oct. 2010 à 09:42
Essaie de faire Suppr dans les cases ' vides ' sur la ligne qui provoque l'erreur.
Patrice33740
Messages postés
8556
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
2 mars 2023
1 779
26 oct. 2010 à 12:11
26 oct. 2010 à 12:11
La fonction N() existe depuis 97, et peut-être même avant, elle fonctionne parfaitement. En utilisant cette formule il ne peut y avoir l'erreur #VALEUR! que si une la cellule d'origine contient déjà cette erreur.
Par contre ta formule =SI(C4="";"";"8:20") donne un résultat texte pour obtenir un résultat numérique, remplace la par =SI(C4="";0;TEMPSVAL("8:20"))
Patrice
Par contre ta formule =SI(C4="";"";"8:20") donne un résultat texte pour obtenir un résultat numérique, remplace la par =SI(C4="";0;TEMPSVAL("8:20"))
Patrice
Patrice33740
Messages postés
8556
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
2 mars 2023
1 779
26 oct. 2010 à 13:46
26 oct. 2010 à 13:46
Je ne comprends pas pourquoi tu as encore des soucis, mets un exemple de ton fichier sur http://cijoint.fr/
Vaucluse
Messages postés
26496
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
1 avril 2022
6 414
Modifié par Vaucluse le 26/10/2010 à 15:32
Modifié par Vaucluse le 26/10/2010 à 15:32
Rebonjour
effectivement ce n'est plus à fait la question vu le nombre de données à traiter
mais ce que je ne comprend pas, c'est pourquoi vous utilisez des formules pour placer des constantes,
Si le matin, le temps de présence est de 50 mn et l'après midi de 1h, autant compter par ligne le nombre d'entrée le matin *00:50 et ajouter le nombre d'entrée l'après midi*à1:00
avec cette solution
redécouper vos cellules fusionnées de ligne 2 de façon à n'avoir l'info "Matin" et "Soir "que sur la colonne de gauche dans chaque cas
pour le matin
=SOMMEPROD(($D$2:$C$E2="Matin")*(D4:CE4>0)*"00:50")
pour le soir
=SOMMEPROD(($D$2:$CE$2="Soir"")*(D4:C4>0)*"01:00")
à tirer en plaçant bien les blocages sur la hauteur du champ.
Ces formules ne comptent que les cellules où se trouvent une valeur supérieure à 0 dans les colonnes correspondantes à 3Matin" et "Soir"
non? qu'est ce qui n'est pas encore dit, dans votre question
Maintenant pour être sur que ça marche comme vous le souhaitez , compléter la valeur entre guillemets dans chaque formule par *1
soit par exemple:
("8:20")*1
et remplacez la condition "" par 0
ça devrait garantir un meilleur résultat si vous ne sautez pas de formule
Crdlmnt
effectivement ce n'est plus à fait la question vu le nombre de données à traiter
mais ce que je ne comprend pas, c'est pourquoi vous utilisez des formules pour placer des constantes,
Si le matin, le temps de présence est de 50 mn et l'après midi de 1h, autant compter par ligne le nombre d'entrée le matin *00:50 et ajouter le nombre d'entrée l'après midi*à1:00
avec cette solution
redécouper vos cellules fusionnées de ligne 2 de façon à n'avoir l'info "Matin" et "Soir "que sur la colonne de gauche dans chaque cas
pour le matin
=SOMMEPROD(($D$2:$C$E2="Matin")*(D4:CE4>0)*"00:50")
pour le soir
=SOMMEPROD(($D$2:$CE$2="Soir"")*(D4:C4>0)*"01:00")
à tirer en plaçant bien les blocages sur la hauteur du champ.
Ces formules ne comptent que les cellules où se trouvent une valeur supérieure à 0 dans les colonnes correspondantes à 3Matin" et "Soir"
non? qu'est ce qui n'est pas encore dit, dans votre question
Maintenant pour être sur que ça marche comme vous le souhaitez , compléter la valeur entre guillemets dans chaque formule par *1
soit par exemple:
("8:20")*1
et remplacez la condition "" par 0
ça devrait garantir un meilleur résultat si vous ne sautez pas de formule
Crdlmnt
Patrice33740
Messages postés
8556
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
2 mars 2023
1 779
26 oct. 2010 à 16:20
26 oct. 2010 à 16:20
Bonjour,
Pas besoin de formules compliquées, il suffit d'utiliser un format personnalisé :
http://www.cijoint.fr/cjlink.php?file=cj201010/cijkBIsyWQ.xls
Patrice
Pas besoin de formules compliquées, il suffit d'utiliser un format personnalisé :
http://www.cijoint.fr/cjlink.php?file=cj201010/cijkBIsyWQ.xls
Patrice
Vaucluse
Messages postés
26496
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
1 avril 2022
6 414
26 oct. 2010 à 18:03
26 oct. 2010 à 18:03
Bonsoir Patrice
et merci pour l'info TEMPSVAL que je ne connaissais pas et qui sera bien utile si le quota additionné change.
Ceci dit, je ne vois toujours pas le but de créer une telle formule pour additionner des constantes!
Je reste à l'écoute des explications du demandeur
Bien amicalement
et merci pour l'info TEMPSVAL que je ne connaissais pas et qui sera bien utile si le quota additionné change.
Ceci dit, je ne vois toujours pas le but de créer une telle formule pour additionner des constantes!
Je reste à l'écoute des explications du demandeur
Bien amicalement
Patrice33740
Messages postés
8556
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
2 mars 2023
1 779
Modifié par Patrice33740 le 26/10/2010 à 18:18
Modifié par Patrice33740 le 26/10/2010 à 18:18
Bonsoir Vaucluse
Je pense qu'en général, il ne saisit que l'heure d'arrivée le matin et l'heure de départ le soir, l'heure de départ le matin et d'arrivée le soir étant liées aux horaires de l'école. Cependant, il conserve la possibilité d'entrer manuellement une valeur à la place de la formule pour les cas particuliers.
Amicalement
Patrice
Je pense qu'en général, il ne saisit que l'heure d'arrivée le matin et l'heure de départ le soir, l'heure de départ le matin et d'arrivée le soir étant liées aux horaires de l'école. Cependant, il conserve la possibilité d'entrer manuellement une valeur à la place de la formule pour les cas particuliers.
Amicalement
Patrice
Patrice33740
Messages postés
8556
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
2 mars 2023
1 779
Modifié par Patrice33740 le 26/10/2010 à 18:26
Modifié par Patrice33740 le 26/10/2010 à 18:26
PS : j'aurais pu mettre 8/24+30/1440 à la place de TEMPSVAL("08:30") mais c'est moins « visuel».
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
J'apporterai des précisions à mon problème tout en vous remerciant de vous pencher sur mon cas.
Ce fichier me permet d'enregistrer, chaque jour, les heures d'arrivée et de départ de personne dans la structure dont je suis responsable.
A la fin du mois, il me faut une cellule affichant le total en heures et minutes.
Par exemple :
le 01 janvier, le 02 janvier, etc....................................... Total
Arrivée, Départ, Arrivée, Départ, etc............................... Arrivée, Départ
cell A1 7:30 cell B1 8:20 cell C1 7:30 cell D1 8:30 etc..... (B1-A1)+(D1-C1)+etc...
Dans mon tableau quand toutes les cellules sont remplies, cela fonctionne, dès l'instant où une seule ou plus est vide, cela marque #VALEUR!
Inutile de préciser que je souhaite limiter le temps de saisie et par conséquent, que je ne souhaite par remplir les cellules vides par des 0.
Ce fichier me permet d'enregistrer, chaque jour, les heures d'arrivée et de départ de personne dans la structure dont je suis responsable.
A la fin du mois, il me faut une cellule affichant le total en heures et minutes.
Par exemple :
le 01 janvier, le 02 janvier, etc....................................... Total
Arrivée, Départ, Arrivée, Départ, etc............................... Arrivée, Départ
cell A1 7:30 cell B1 8:20 cell C1 7:30 cell D1 8:30 etc..... (B1-A1)+(D1-C1)+etc...
Dans mon tableau quand toutes les cellules sont remplies, cela fonctionne, dès l'instant où une seule ou plus est vide, cela marque #VALEUR!
Inutile de préciser que je souhaite limiter le temps de saisie et par conséquent, que je ne souhaite par remplir les cellules vides par des 0.
Vaucluse
Messages postés
26496
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
1 avril 2022
6 414
25 oct. 2010 à 20:57
25 oct. 2010 à 20:57
alors ma proposition devrait convenir à ceci près que l'on peut prendre en compte les 4 cellules dans une seule condition
=SI(A1*B1*C1*D1=0;"";(B1-A1)+(D1-C1))
crdlmnt
=SI(A1*B1*C1*D1=0;"";(B1-A1)+(D1-C1))
crdlmnt
Cela fonctionne mais ne répond pas à ma problématique car l'utilisation de la multiplication induit que lorsque une seule cellule affiche rien, c'est à dire 0 alors automatiquement le résultat total est 0 alors que malgré tout les autres cellules affichent bien des heures dont la somme de leur différence doit apparaître dans mes résultats.
Vaucluse
Messages postés
26496
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
1 avril 2022
6 414
26 oct. 2010 à 08:12
26 oct. 2010 à 08:12
Bonjour Errario..
ah?? voyez ici avec les deux options 0 et guillemets
http://www.cijoint.fr/cjlink.php?file=cj201010/cijsY7MkE6.xls
dites moi ce qui ne correspond pas?
crdlmnt
ah?? voyez ici avec les deux options 0 et guillemets
http://www.cijoint.fr/cjlink.php?file=cj201010/cijsY7MkE6.xls
dites moi ce qui ne correspond pas?
crdlmnt
Tout d'abord merci à Patrice33740 qui vient de résoudre mon énigme, sans oublier Vaucluse qui a participé activement à cette résolution.
Dans un deuxième temps, je mets à disposition le fichier résolu pour ceux qui, à l'avenir,pourrait en avoir la nécessité :
http://www.cijoint.fr/cjlink.php?file=cj201010/cijDsf7pQ6.xls
Ensuite, pour répondre aux questions que vous vous êtes posées, en réalisant ce fichier, j'avais les objectifs suivants :
1. Rentabiliser au mieux mon temps de travail en réduisant au maximum le temps de saisie
2. Saisir donc, le moins de données possibles (ce fichier faisant parti d'un groupe de 10 fichiers avec liaisons me permettant la gestion globale des présences de ma structure, tout en répondant à chaque contrainte réglementaire institutionnelle)
Autre questionnement :
Les arrivées du matin et les départs du soir sont variables. Les horaires choisis pour tester ont subi tout bêtement la loi du copier-coller, ce qui, je m'en excuse, pouvait laisser à penser qu'ils étaient constants.
En ce qui concerne vos interrogations sur la formule :
=SI(CB4="";0;TEMPSVAL("8:20")) ou =SI(BS4="";0;TEMPSVAL("16:30")) , il s'agit tout simplement d'esthétique car compte tenu du nombre de cellules je ne voulais pas avoir en permanence 8:20 ou 16:30 dans les cellules et j'imagine, chose encore plus pertinente, que cela fausserait sans doute mes résultats de fin de ligne compte tenu de la formule qui est utilisée (et qui maintenant, j'insiste, fonctionne) ?
En tout état de cause, pour toute autre incompréhension, ce n'est que l'expression caractérisée de ma maîtrise plutôt "juvénile" d'Excel.
Je me répète une nouvelle fois, mais, mille merci encore pour votre aide précieuse.
Dans un deuxième temps, je mets à disposition le fichier résolu pour ceux qui, à l'avenir,pourrait en avoir la nécessité :
http://www.cijoint.fr/cjlink.php?file=cj201010/cijDsf7pQ6.xls
Ensuite, pour répondre aux questions que vous vous êtes posées, en réalisant ce fichier, j'avais les objectifs suivants :
1. Rentabiliser au mieux mon temps de travail en réduisant au maximum le temps de saisie
2. Saisir donc, le moins de données possibles (ce fichier faisant parti d'un groupe de 10 fichiers avec liaisons me permettant la gestion globale des présences de ma structure, tout en répondant à chaque contrainte réglementaire institutionnelle)
Autre questionnement :
Les arrivées du matin et les départs du soir sont variables. Les horaires choisis pour tester ont subi tout bêtement la loi du copier-coller, ce qui, je m'en excuse, pouvait laisser à penser qu'ils étaient constants.
En ce qui concerne vos interrogations sur la formule :
=SI(CB4="";0;TEMPSVAL("8:20")) ou =SI(BS4="";0;TEMPSVAL("16:30")) , il s'agit tout simplement d'esthétique car compte tenu du nombre de cellules je ne voulais pas avoir en permanence 8:20 ou 16:30 dans les cellules et j'imagine, chose encore plus pertinente, que cela fausserait sans doute mes résultats de fin de ligne compte tenu de la formule qui est utilisée (et qui maintenant, j'insiste, fonctionne) ?
En tout état de cause, pour toute autre incompréhension, ce n'est que l'expression caractérisée de ma maîtrise plutôt "juvénile" d'Excel.
Je me répète une nouvelle fois, mais, mille merci encore pour votre aide précieuse.