Question relative aux sommes d'un tableau
Bonjour, merci encore pour exister
Je crée une feuille de calcul de temps assez complexe pour mes employés, je veux m'assurer du bien de chacune des fonctions applicables dans mon fichier excel!
Je veux avoir un rendu propre et le plus logique possible, des fonctions qui sont souvent faciles pourraient être mieux adaptées...
Voici ma première question, si vous pourriez m'écrire une belle réponse concise car, je connais excel mais, j'ai besoin d'un peu de tutorat! C'est en pratiquant qu'on devient pro
Alors, premièrement, je veux calculer le total des montants d'une semaine dans une cellule à droite de celles-ci (pas forcément la cellule VOISINE), en tenant compte que chaues groupes de semaine peut varier en nombre de lignes.
Je vous partage une capture d'écran au bas du message, mais j'explique.
Colonne A: Date.
B: Jour de la semaine.
C: Montant associé au jour de la semaine (paye fictive).
D1, D7 etc.: Total des montants en C pour une semaine entière (Dimanche au Samedi). J'inscrit ce montant à chaque dimanche, qestion esthétique et logique.
Pour débuter, 7 lignes pour 1 semaine (logique). Par contre, il peut arriver que l'on doive ajouter des lignes au travers des semaines (ici, lignes 11,13 et 14), disons pour ajouter plusieurs montants dans une journée. Je sais que d'inscrire une fonction =SUM(A1:A7) est convenable habituellement, mais y a t'il une meilleure façon de calculer un total des montants de la semaine si on y ajoute des lignes?
Y aurait-il moyen d'ajouter automatiquement une semaine? (7 lignes)
Y aurait-il moyen de n'afficher que les dimanches sans filtres ni option de groupage? Disons VBA? Expandre chaque semaine seulement à notre guise?
Merci beaucoup pour votre temps et votre aide, cela est très apprécié! Je vais sûrement ajouter des questions au fil du temps pour améliorer cette feuille!
Comme je ne peux pas encore inscrire de lien, voici un lien espacé pour vous imager ma feuille de calcul:
imgur . com / 86lcSb2
Bonjour
Je passerais par un tableau structuré avec saisie de la date, le jour est une copie de la date (=A2) affiché autrement, le montant est peut-être récupérable par formule (pas très clair sur ce point ton explication).
On ajoute à la fin et on trie par date.
Pour ne pas voir la répétition des dates, une mise en forme conditionnelle
et pour la formule en D, un SOMME.SI.ENS sur toutes les lignes doit faire l'affaire
Je n'avais pas insisté sur le montant car mon tableau est beaucoup plus complexe à la fin.
J'aimerais y aller pas à pas pour améliorer chaque colonne, mais j'explique:
*À noter, la feuille commence en ligne 5 ici, les lignes 1 è 4 servent d'en-tête incluant en ligne 3 les grands totaux (expliqué à la fin du message).
A: Date
B: WEEKDAY
C: Liste déroulante avec 3 postes différends (CV (valeur de 20,46$), COD (valeur de 23,60$) et FORM (valeur de 20,46$). chaque poste est référencé à un salaire horaire dans une autre feuille.
D: liste déroulante succursale attribuée (ce n'est qu'informatif).
E et F: heures de début et fin de la journée travaillée.
G: Sous total (en décimale) de F-E.
Formule: =(F19-E19)*24
H: Nombres de pauses d'une durée de 15 minutes (mais indiquée en nombres de pauses(0 à 2)) selon les heures travaillées dans la journée.
Formule: =IF(G19<4;0;IF(G19<5,75;1;IF(G19<12;2;0))). Rien ne change selon le nbre de pauses prises.
I: Nombres de repas d'une durée de 1 heure chaque (mais indiquée en nombres de repas (0 à 2)) selon les heures travaillées dans
la journée.
Formule: =IF(G19<7,25;0;IF(G19<10,25;1;IF(G19>=10,5;2;""))). Plus complexe que les pauses, le salaire total de la journée va changer selon le nbre de repas pris.
J: Prime monétaire SI il y à 2 repas pris dans la journée (ne prends pas en compte 0 ou 1 repas).
Formule:=IF(I19=2;12,65;0)
K: Total des heures travaillées dans la journée (en soustrayant le nombre de repas seulement).
Formule: =(G19-I19)
L: Prime salariale de 7,45% sur le salaire total de la journée (qui se retrouvera en N), applicable chaque jour.
Formule: =N5*7,45%
M: Total des primes salariales de la semaine (même fonctionnement que le total monétaire de la semaine expliqué dans le précédent message).
Formule: =L5:L10
N: Total du salaire calculé pour une journée de travail. En fonction de la liste déroulante en C.
Formule: =(IF(C5="CV";Options!$C$2;IF(C5="COS";Options!$C$3;IF(C5="FORM";Options!$C$4;Options!$C$1))))*K5
O: Total des salaires d'une semaine complète.
Formule: =SUM(N5+J5)
P: TOTAL final des heures travaillées dans la semaine en K.
Formule: =SUM(K5:K10)
Q: Total salarial de la semaine, plus la prime indiquée en M.
Formule: =SUM(O5:O10)+M5
Il y aurait aussi deux totals dans des cellules au dessus de la table. Soit:
P3: TOTAL de toutes les heures travaillées de la table (en P).
Formule: =SUM(P11:P48)
Q3: TOTAL complet des heures travaillées (en Q).
Formule: =SUM(Q5:Q48)
Faut savoir, j'ai une deuxième feuille avec les heures et le nombre de pauses et de repas alloués. Je pourrai l'expliquer dans un futur message pour améliorer les colonnes H et I (je pense à un vlookup mais n'y connais rien).
J'ai aussi une feuille PAIE qui exprime en A la date de réception d'une paie, B la semaine comprenant cette paie (2 semaines avant la date en A) et en F le montant de cette paie. Ici aussi je n'y comprends rien, mais on pourra y voir plus tard...
Alors, beaucoup de travail en vue pour améliorer le tout...
Merci encore pour tout votre temps et votre aide!!!
RE
La principes (tableau structuré et MFC) et formules que j'ai transmis sont applicables
Notamment les formule en colonnes
- B qui chez toi est un non sens même si elle fonctionne
- M, O, P et Q qui doivent s'inspirer de la colonne D de mon exemple
P3 et Q3 ne correspondent aux mêmes ligne : est-ce normal ?
Pour P et Q, je voulais simplement formater les résultats sur la même ligne, question d'avoir le total fixe en haut de la page sans ajouter de colonnes, il est possible de dire: Additionner toutes les cellules sous P3 et Q3?
B est changé par votre formule, c'est super.
D, M, N,O,P et Q ne fonctionnent pas si j'ajoute de nouvelles lignes, je dois absolument inscrire la date et le jour de la semaine sur ces lignes ajoutées, ou il y à moyen de ne pas les inscrire question d'alléger la feuille pour les yeux?
Pour ce qui es du tableau des heures, plutôt que d'utiliser les formules inscrites en
H [=IF(G5<4;0;IF(G5<5,75;1;IF(G5<12;2;0)))]
et I [=IF(G5<7,25;0;IF(G5<10,25;1;IF(G5>=10,5;2;"")))],
il y à moyen d'utiliser ce tableau, ou sinon, un VLOOKUP avec le tableau que j'ai créé en AN:AP? Je sens que ce serait plus logique...
Re
Pour P et Q, je voulais simplement formater les résultats sur la même ligne, question d'avoir le total fixe en haut de la page sans ajouter de colonnes, il est possible de dire: Additionner toutes les cellules sous P3 et Q3?
Cest jutsement ce que permet un tableau structuré. Regarde mes formules : basées sur les colonnes, elle suivent automatiquement la taille du tableau
D, M, N,O,P et Q ne fonctionnent pas si j'ajoute de nouvelles lignes, je dois absolument inscrire la date et le jour de la semaine sur ces lignes ajoutées, ou il y à moyen de ne pas les inscrire question d'alléger la feuille pour les yeux?
Oui la date doit être sur chaque ligne, ce qui permet entre autres de classer, filtrer le tableau si nécessaire, mais surtout d'automatiser les calculs portant sur les semaines.
Regarde les formules et les MFC de mon exemple. C'est allégé pour la vue mais présent dans le tableau et on a une formule unique pour la colonne.
Pour ce qui es du tableau des heures, plutôt que d'utiliser les formules inscrites en
H [=IF(G5<4;0;IF(G5<5,75;1;IF(G5<12;2;0)))]
et I [=IF(G5<7,25;0;IF(G5<10,25;1;IF(G5>=10,5;2;"")))],
il y à moyen d'utiliser ce tableau, ou sinon, un VLOOKUP avec le tableau que j'ai créé en AN:AP? Je sens que ce serait plus logique...
Il est toujours plus logique de ne pas mettre de valeurs dans les formules mais des cellules cela facilite grandement la maintenance
Je ne vois rien dans tes feuilles en AN:AP
Je comprends la logique de =IF(WEEKDAY([@DATE];2)<>7;"";SUMIFS([MONTANT];[DATE];">="&[@DATE]-(WEEKDAY([@DATE];1)-1);[DATE];"<="&[@DATE]-(WEEKDAY([@DATE];1)-7)))
Mais je ne sais pas trop comment la traduire pour les autres colonnes, je ne change pas simplement la valeur [MONTANT]?
Pour ma question VLOOKUP,
J'ai maintenant deux listes sur Feuille2,
Colonne A: liste de 00:15 à 12:00 qui signifie le nbre d'heures travaillées
Colonne B: nbre de pauses allouées pour chacun des nbre d'heures de la colonne A.
J'ai fait la même chose pour les repas en C:D
J'essaie de faire un VLOOKUP dans ma page 1 sur les colonnes pauses (H) et repas (I)
=VLOOKUP(H5;Sheet2!A;2;1) ... quelle est donc mon erreur?
salut raphgiroux,
où peut on voir ces colonnes A,B,C et D dans quel fichier?
RE
Je comprends la logique de =IF(WEEKDAY([@DATE];2)<>7;"";SUMIFS([MONTANT];[DATE];">="&[@DATE]-(WEEKDAY([@DATE];1)-1);[DATE];"<="&[@DATE]-(WEEKDAY([@DATE];1)-7)))
Mais je ne sais pas trop comment la traduire pour les autres colonnes, je ne change pas simplement la valeur [MONTANT]?
Oui tu remplaces pas la colonne à "sommer " (mais cela ne marchera pas tant que ce ne sera pas un tableau structuré)
Je crois bien m'en sortir, me manquerait qu le Vlookup dont je vous prlaît il y a peu, je ne réussis pas à faire la bonne fonction
Aussi, je trouve ce problème pratique, mais à chaque fois qu'il y à doublon, la première cellule est voyante, mais les valeurs suivantes sont en texte blanc, ça revient noir quand j change la cellule suivante de valeur...pratique pour les dates et les jours de la semaine (A et B) mais pour le rste du tableau, ça devient nuisible... comment faire pour effacer cette fonction d'une colonne?
Tout se passe comme il le faut, dernière question,
Je place le total de la semaine sur la ligne du dimanche (date ou jour de la semaine, j'aimerais utiliser la valeur la plus logique)
Sur une nouvelle feuille, j'aimerais indiquer cette date (ex: 9 février) et inscrire sur la cellule de droite la valeur inscrite en O sur cette ligne. J'aimerais ensuite ajouter chaque dimanches par la suite sur les lignes suivantes.
exemple:
A:B
9 février: 0,00$
16 février: 643,89$
23 février: 864,77$
Bonjour
J'ai découpé l'un de tes tableaux de référence en 2 tableaux structurés nommés Horaires et Horaires2
J'ai modifié un peu ton tableau Horodateur :
- renommé le tableau (ne pas conserver les noms automatiques types Tableau1)
modifié certains format de cellules pour ne pas afficher les valeurs 0
Sur Synthèse 2 façon de procéder
- soit par formule (sur 100 lignes) : méthode un peu archaïque sur 2016
RE
H: sur la dernière ligne de chaque groupe du mois, j'aimerais y inscrire le total de chaque mois. Y à t'il un moyen de séparer les dates par mois (une ligne vide entre mars et avril, par exemple?
Dans ce cas on exploite la requête PowerQuery par TCD
Je ne comprends pas bien comment le faire...
RE
Tu en es où ?
As-tu regardé la requête déjà, ou pas ?
RE
Une autre solution : ajout d'une colonne à ton tableau source (colonne que tu peux masquer) et TCD directement à partir du Tableau source (sans PowerQuery donc)
C'est une solution qui me convient bien!!
Je pourrai ajouter des calculs à la feuille synthèse seulement en y ajoutant des colonnes? Sinon, quelle méthode utiliser pour appliquer cette façon de rapetisser les mois dans cette feuille sur les groupes de semaines sans mon premier tableau? Pouvoir soit rapetisser les semaines individuellement et/ou n'affichent que les dimanches en un seul clic?
Merci beaucoup, tout le projet prends forme!
RE
Je pourrai ajouter des calculs à la feuille synthèse seulement en y ajoutant des colonnes? Sinon, quelle méthode utiliser pour appliquer cette façon de rapetisser les mois dans cette feuille sur les groupes de semaines sans mon premier tableau? Pouvoir soit rapetisser les semaines individuellement et/ou n'affichent que les dimanches en un seul clic?
Ajouter des colonnes : c'est un tableau croisé dynamique ; on ne peut pas y ajouter d'infos autres que celles existants dans la source
Il faudrait vraiment donner l'objectif final complet car avec des bouts de question on risque de perdre du temps sur des solutions qui n'y correspondent pas.
Pas sûr de comprendre la question à propos de rapetisser.
Le TCD affiche déjà seulement les dimanches
Si tu ne veux voir que les totaux mensuels, cliquer sur un mois, puis dans l'onglet TCD, Analyse, la zone Champ actif, il y a une icône pour plier le détail du mois ou le déplier (icône représentant des lignes avec soit un +, soit un -.
Si c'est un seul mois, se servir des petits + ou - à gauche du nom du mois