Excel

[deathdigger]

Ben c'est pour ça que je préconise plutôt un bête IF : dans l'énoncé, il n'est nullement mentionné que ROUGE est forcément égal à 1965 (par exemple).

Bref, encore une analyse fonctionnelle bâclée, et quand on pourra pas livrer dans les temps parce qu'il faut tout refaire, sur qui on va taper ? Le développeur ! :vismaviededev'enSSII:

[Wobak]

Bah si, il dit que pour un type de produit y'a une année et qu'il a une table de correspondance ailleurs.

De plus tes IF imbriqués ne changent rien si y'a plusieurs années possibles pour Rouge.

[SuperLowl]

Puis le IF, c'est très joli quand on a deux valeurs. Quand y'en a 50, tu te retrouves avec un beau bordel. D'ailleurs je crois qu'on ne peut pas imbriquer plus d'un certain nombre de IF dans une formule. Ou alors c'était une ancienne limite qui n'a plus cours aujourd'hui.

[Sharn]

Puis le IF, c'est très joli quand on a deux valeurs. Quand y'en a 50, tu te retrouves avec un beau bordel. D'ailleurs je crois qu'on ne peut pas imbriquer plus d'un certain nombre de IF dans une formule. Ou alors c'était une ancienne limite qui n'a plus cours aujourd'hui.

Demandez à perverpepere. Il nous a pondu une espèce de formule dégueulasse ces derniers jours avec un nombre hallucinant de si/if... Sinon tu peux le faire en vba.

[Phenixy]

Hello,

je cherche à réaliser une mise en forme conditionnelle assez bête mais qui me bloque (je suis sous Excel 2007). J'ai une colonne avec des dates (de diffusion d'un programme), et je cherche à colorer les cases ou le texte une fois la date passée. Dans les options avec la date, Excel me propose que des contexte genre "hier, "demain", "mois dernier", "mois suivant", bref de l'amplitude finie autour de la date du jour, mais rien d'infini. Idéalement, je ferai bien un truc genre une couleur "à venir", une "diffusion dans moins de 7 jours" et une "diffusion passée"... Une idée?

[Wobak]

Avec une formule du type : If (date - today <7)

[Jaycie]

Bon petit déterrage pour un petit problème sur un de mes fichiers.

C'est un fichier de suivi d'avancement (pour de l'approbation de docs). J'ai 6 colonnes : 3 pour les dates prévues (protocole, exécution, rapport) et 3 pour rentrer les dates réelles.

Vu que je dois faire un peu de reporting, je veux sortir des courbes sur ces dates, qui me donne combien de documents aurais dû être approuvés à la date de Mai ou Juillet 2015 par exemple.

J'ai réussi à trouver un moyen de faire ces données cumulées pour les dates prévues (passage de la date en chiffre via la fonction MONTH, puis faire un COUNTIF+le nombre du dessus pour faire le cumulé)

Par contre, pour l'instant, je ne peux pas faire la même chose pour les dates réelles : les cases sont vides (les documents sont pas encore sortis) et si je passe par la fonction month ça me sort un "1".
Si je fais comme pour les dates prévues ça va donc me dire que j'ai tout mes documents faits en Janvier...

Est-ce qu'il existe une fonction qui me renverrait zéro si la case est vide et le mois si la case est remplie ?

Au passage si vous connaissez aussi un moyen plus élégant pour compter le nombre d'occurences d'un mois dans une colonne je suis preneur (les dates sont rentrées en format date, obviously)

[Wobak]

Salut,

Si tu pouvais nous mettre un exemple de fichier sur un truc d'upload pour que je puisse manipuler un peu et te dire comment faire ça, ça serait top

[deathdigger]

Bon petit déterrage pour un petit problème sur un de mes fichiers.

C'est un fichier de suivi d'avancement (pour de l'approbation de docs). J'ai 6 colonnes : 3 pour les dates prévues (protocole, exécution, rapport) et 3 pour rentrer les dates réelles.

Vu que je dois faire un peu de reporting, je veux sortir des courbes sur ces dates, qui me donne combien de documents aurais dû être approuvés à la date de Mai ou Juillet 2015 par exemple.

J'ai réussi à trouver un moyen de faire ces données cumulées pour les dates prévues (passage de la date en chiffre via la fonction MONTH, puis faire un COUNTIF+le nombre du dessus pour faire le cumulé)

Par contre, pour l'instant, je ne peux pas faire la même chose pour les dates réelles : les cases sont vides (les documents sont pas encore sortis) et si je passe par la fonction month ça me sort un "1".
Si je fais comme pour les dates prévues ça va donc me dire que j'ai tout mes documents faits en Janvier...

Est-ce qu'il existe une fonction qui me renverrait zéro si la case est vide et le mois si la case est remplie ?

Au passage si vous connaissez aussi un moyen plus élégant pour compter le nombre d'occurences d'un mois dans une colonne je suis preneur (les dates sont rentrées en format date, obviously)

Rajouter un Si ="" ne suffirait pas ?

[Jaycie]

Voici le fichier (expurgé de toutes les infos "sensibles" bien entendu ) : https://drive.google.com/file/d/0B_c...ew?usp=sharing

[Wobak]

Alors pour avoir la liste des mois sans passer par une liste intermédiaire :
=SUMPRODUCT(--((C7:C2000)<>""),--(MONTH(C7:C2000)=1)) pour Janvier
=SUMPRODUCT(--((C7:C2000)<>""),--(MONTH(C7:C2000)=2)) pour Février, etc...

Tu remplaces le C par la lettre de chaque colonne et t'as ta liste qui va bien si je ne dis pas de bétise.

[Jaycie]

Alors pour avoir la liste des mois sans passer par une liste intermédiaire :
=SUMPRODUCT(--((C7:C2000)<>""),--(MONTH(C7:C2000)=1)) pour Janvier
=SUMPRODUCT(--((C7:C2000)<>""),--(MONTH(C7:C2000)=2)) pour Février, etc...

Tu remplaces le C par la lettre de chaque colonne et t'as ta liste qui va bien si je ne dis pas de bétise.

Merci ça marche du tonnerre .

Juste pour mon info ça sert à quoi les "--" ? (j'ai compris pour <> )

[Wobak]

Les -- c'est pour transformer les booléens en chiffres pour la multiplication dans le sumproduct

[Jaycie]

Les -- c'est pour transformer les booléens en chiffres pour la multiplication dans le sumproduct

OK Merci

[Tien 12]

Je reposte ici, n'ayant pas vu qu'il y avait un topic dédié:

Salut les canards. J'ai besoin de votre aide pour établir une fonction sous excel.
Je vais essayer d'être clair, ça risque de ne pas être facile.

Je souhaite créer un classeur me permettant de calculer des horaires de travail. C'est pour de la restauration, donc les horaires et jours ne sont pas fixes et compris du lundi au dimanche avec des horaires à cheval sur 2 jours.
Voici, rapidement, les principales caractéristiques :
- Le classeur est simplement composé de 12 feuilles pour les 12 mois.
- Les dates se calculent automatiquement sur toutes les feuilles après avoir indiqué celle du 1er Janvier. L'idée est d'avoir une quelque chose d'automatique et "perpétuel", hormis pour les année bissextile (salut 2016)
- Les n° de semaines sont calculé automatiquement et ajouté pour chaque dimanche et dernier jour du mois.

J'arrive à peut près à ce que je souhaite, sauf pour le calcul hebdomadaire que je souhaite afficher seulement quand le n° de la semaine est indiqué (les dimanche et dernier jour du mois). Pour ça, je pars d'une somme des 7 derniers jours à partir du calcul
Sauf qu'en début de mois, j'ai une valeur #REF! et en fin de mois, il me compte les 7 derniers jours donc ceux de la semaine précédente.

Pour être plus clair, une petite image :


J'ai bien essayé de faire une condition sur le fait que les valeurs de la colonne "Total" à additionner soit sur la même semaine au vu de la conne "Date", mais je n'y arrive pas.

Bon, je ne suis pas certain d'être clair, donc n'hésitez pas à demander des éclaircissements. Voici le fichier.

Edit : Du coups, étant sur un topic dédié, j'apporte plus de détails.

Ma formule actuelle pour calculer le temps hebdomadaire est le suivant :
=SI(CELLULE("type";J33)="v";SOMME(G27:G33);"")

Il faudrait que j'agisse sur la partie "SOMME". Mais je voudrait ensuite rajouter, pour le premier dimanche de chaque mois, dans le cas où la semaine n'est pas complète, la récupération du dernier calcul hebdo du mois précédent. Ce sera l'étape suivante (sachant que pour la première semaine de Janvier, ce sera un cas particulier).

[Grosnours]

Je te réponds ici aussi !
Une solution simple, rapide et un peu sale :
- tu rajoutes une colonne "numéro du jour dans la semaine" (colonne L)
- tu rajoutes une autre colonne "numéro du jour dans le mois" (colonne M)
- tu rajoutes une dernière colonne pour ton résultat, avec la formule suivante : =SUM(OFFSET($G$3;M3-L3;0;L3;1)) pour la première cellule N3. Avec un Excel français cela donne =SOMME(DECALER($G$3;M3-L3;0;L3;1))

Le résultat :


L'idée derrière est de calculer la somme des heures pour une plage de cellules dynamique, obtenue en fonction du premier jour du mois. Je suis sur qu'il devrait y avoir moyen de se passer de mes deux colonnes supplémentaires (au pire tu les caches) mais comme je le disais c'est du simple, rapide et un peu crade.

[Tien 12]

Oui, j'y avais pensé, faire une colonne du n° de la semaine, mais j'en ai déjà une et je ne souhaite pas qu'elle soit indiqué pour chaque jour. Ça rendrais encore plus lourd un tableau qui l'est déjà.

Non, je souhaiterais juste une formule (aussi complexe qu'elle soit), pour justement éviter d'être "crade".

[Grosnours]

Le problème d'Excel si on ne veut pas toucher au VBA est que l'algorithmique y est un tant soit peu "différente" et qu'il faut bien souvent utiliser des cellules auxiliaires si on veut rester un tant soit peu lisible.

Dans ton cas, il faut simplement recalculer pour chaque fin de semaine la fenêtre des cellules à additionner. Il faut donc que tu aies une variable avec la position de la cellule pour chacune des cellules totale. Bête comme chou en VBA ou n'importe quel langage de prog, plus complexe en utilisant des formules Excel. D'ou mon utilisation de colonnes auxiliaires. Colonnes qu'on peut soigneusement cacher d'ailleurs (données > plan > grouper), le résultat visuel final d'une feuille de calcul étant hautement personnalisable.

Mais je comprends ton souci de compacité et je vais donc essayer de te trouver une formule un poil plus propre, j'ai comme hier quelques heures de trajet à tuer ce matin.


EDIT : Ok, avec une seule colonne (Q) en intermédiaire :
On remplace la colonne L par =ROW()-2 et la colonne M par =IF(ISNUMBER(Q2);IF(ISNUMBER(J2);1;Q2+1);ROW()-2), ce qui te donne comme formule :
=SUM(OFFSET($G$3;ROW()-2-IF(ISNUMBER(Q2);IF(ISNUMBER(J2);1;Q2+1);ROW()-2);0;IF(ISNUMBER(Q2);IF(ISNUMBER(J2);1;Q2+1);ROW()-2);1))
Ça fonctionne et il n'y a besoin que d'une colonne intermédiaire.
Maintenant je vais essayer de trouver une formule sans aucun intermédiaire, cela promet d’être mirifiquement crade...

[Tien 12]

Merci.

Je suis au taf, donc je ne pourrais pas tester avant ce soir.

[Grosnours]

Ok, voilà la formule, sans aucune valeur intermédiaire :
=SUM(OFFSET($G$3;ROW()-2-IF(ROW()-2<=MATCH(1;$J$3:$J$33;0);ROW()-2;IF(MOD(ROW()-2-MATCH(1;$J$3:$J$33;0);7)=0;7;MOD(ROW()-2-MATCH(1;$J$3:$J$33;0);7)));0;IF(ROW()-2<=MATCH(1;$J$3:$J$33;0);ROW()-2;IF(MOD(ROW()-2-MATCH(1;$J$3:$J$33;0);7)=0;7;MOD(ROW()-2-MATCH(1;$J$3:$J$33;0);7)));1))

En Excel français, cela te donne :
=SOMME(DECALER($G$3;LIGNE()-2-SI(LIGNE()-2<=EQUIV(1;$J$3:$J$33;0);LIGNE()-2;SI(MOD(LIGNE()-2-EQUIV(1;$J$3:$J$33;0);7)=0;7;MOD(LIGNE()-2-EQUIV(1;$J$3:$J$33;0);7)));0;SI(LIGNE()-2<=EQUIV(1;$J$3:$J$33;0);LIGNE()-2;SI(MOD(LIGNE()-2-EQUIV(1;$J$3:$J$33;0);7)=0;7;MOD(LIGNE()-2-EQUIV(1;$J$3:$J$33;0);7)));1))


Tant que tu respectes les conventions de ta première feuille de calcul (colonne G=total, colonne J=semaine, les valeurs commencent à la ligne 3) tout devrait fonctionner sans problèmes et être portable.


Perso je préfère largement avoir une formule plus simple avec des valeurs intermédiaires pour une simple raison de maintenance. Dans un mois, il n'y aucune chance que tu te souviennes pourquoi cette formule si complexe est écrite ainsi et donc la modifier pour une raison ou une autre sera assez difficile. Mais c'est chacun son truc, c'est une question de style.

[Tien 12]

C'est beau... Merci. Je teste ça ce soir.

Ce serait trop demander de me détailler un peu les étapes, je comprend une partie, mais pas tout (version fr) ?

[Grosnours]

Pour chaque cellule :
- on calcule numéro du jour correspondant dans le mois (LIGNE()-2)
- on calcule le numéro du jour correspondant dans le mois (la grosse formule SI(LIGNE()-2<=EQUIV(1;$J$3:$J$33;0);LIGNE()-2;SI(MOD(LIGNE()-2-EQUIV(1;$J$3:$J$33;0);7)=0;7;MOD(LIGNE()-2-EQUIV(1;$J$3:$J$33;0);7))). Là l'algo est un poil plus compliqué : si on est pas dans la première semaine, on cherche la fin de la première semaine et on calcule avec un modulo 7 le numéro du jour de la semaine. Comme obtenir un jour 0 ne nous intéresse pas, on remplace les 0 obtenus par des 7.
- ces deux infos nous permettent de définir la taille du bloc de cellule dont on veut calculer la somme. Par exemple si tu es dans la cellule qui correspond au 3me mercredi du mois, tu ne voudras obtenir comme résultat que la somme du lundi au mercredi
- le cœur de l'algo est la formule SOMME(DECALER($G$3; n° du jour dans le mois - n° jour dans la semaine; 0; n° jour dans la semaine; 1)) qui te calcule la somme des heures travaillées dans la semaine actuelle. En bref, toute l'astuce consiste à avoir une grille de cellule de taille dynamique (le nombre de jours dans une semaine variant pour les débuts et fin de mois) et qui démarre et finit au bon endroit.

J'oubliais : si tu ne veux pas afficher le résultat de cette formule pour chaque jour mais seulement pour les fins de semaine, il faut bien sur rajouter un petit truc au début, ce qui donne (pour la cellule ligne 3) :
=IF(ISNUMBER(J3);SUM(OFFSET($G$3;ROW()-2-IF(ROW()-2<=MATCH(1;$J$3:$J$33;0);ROW()-2;IF(MOD(ROW()-2-MATCH(1;$J$3:$J$33;0);7)=0;7;MOD(ROW()-2-MATCH(1;$J$3:$J$33;0);7)));0;IF(ROW()-2<=MATCH(1;$J$3:$J$33;0);ROW()-2;IF(MOD(ROW()-2-MATCH(1;$J$3:$J$33;0);7)=0;7;MOD(ROW()-2-MATCH(1;$J$3:$J$33;0);7)));1));"")

En français :
=SI(ESTNUM(J3);SOMME(DECALER($G$3;LIGNE()-2-SI(LIGNE()-2<=EQUIV(1;$J$3:$J$33;0);LIGNE()-2;SI(MOD(LIGNE()-2-EQUIV(1;$J$3:$J$33;0);7)=0;7;MOD(LIGNE()-2-EQUIV(1;$J$3:$J$33;0);7)));0;SI(LIGNE()-2<=EQUIV(1;$J$3:$J$33;0);LIGNE()-2;SI(MOD(LIGNE()-2-EQUIV(1;$J$3:$J$33;0);7)=0;7;MOD(LIGNE()-2-EQUIV(1;$J$3:$J$33;0);7)));1));"")

[Tien 12]

OK, merci pour les détails.

[Tien 12]

Alors, ça fonctionne pour Janvier, mais j'ai #N/A pour tous les autres mois.

Et si je souhaiterais ajouter au total de la 1ère semaine incomplète de chaque mois, le total de la dernière semaine incomplète du mois précédent (hors janvier) ? Il faudrait rajouter quoi ?

Je me dis que la mise en place de colonnes intermédiaires masquée serait finalement une solution plus simple...

[Grosnours]

T'as des données pour février ? Parce que dans la feuille de calcul que tu avais donné ici il n'y en avait pas trop => la colonne J des semaines déconnait => c'est normal que ma formule ne donne rien.

Sinon pour répondre à ta question, il faudrait alors (et c'est faisable à la main parce que tu n'as à le faire que 11 ou 12 fois) procéder ainsi :
- te placer dans le mois suivant
- copier-coller ma formule
- uniquement pour la cellule indiquant la fin de la première semaine de ce mois, rajouter +Janvier!K33, en remplaçant bien sur Janvier par le bon mois et K33 par le bon numéro de cellule, celui contenant le total de la dernière semaine du mois d'avant.

[Tien 12]

Je comprend pas ta question "T'as des données pour février ?". Le tableau est vierge à la base, mais basé sur le même schéma (j'ai mis le fichier a dispo un peu plus haut, mais je le remets ici à jour et des horaires en exemple).

J'ai copié ta formule dans la case "K3" de chaque feuille du classeur et l'ai reporté/glissé sur l'ensemble du mois. Ça fonctionne impeccable pour Janvier, mais pas les autres.

Perso, j'étais simplement parti sur =SI(CELLULE("type";J17)="v";SOMME(G11:G17);"") par exemple, mais j'obtiens logiquement un #REF! pour la première semaine si incomplète et un dépassement sur la dernière du mois si incomplète aussi.
Ce que j'avais essayé de faire, c'est de vérifier dans ma partie "SOMME" si chaque ligne où je souhaite additionner la valeur dans "G" est bien de la même semaine que la case où je fais mon calcul.
Ce que je n'ai pas réussi à faire, c'est conditionner cette somme avec les arguments voulu.
Ce que je ne sais pas faire c'est comment faire une vérification d'une case par rapport à une autre de la même ligne. J'avais essayé plusieurs chose, sans succès.

J'ai le sentiment que j'en veux p-e trop. Tu ferais comment en ajoutant les colonnes que tu juges nécessaire ?

Je continue à chercher de mon coté (a mon rythme... )

[Grosnours]

Ok, j'ai compris le problème. Si tu regardes la formule, je fais un EQUIV avec 1, puisque "1" est la première semaine du mois. J'avais complètement oublié que tu allais numéroter tes semaines de 1 à 52 et non pas recommencer à chaque mois. My bad !
Bref, pour que cela fonctionne pour Février, il suffit de remplacer dans la formule tous les EQUIV(1;$J$3:$J$33 par des EQUIV(5;$J$3:$J$33.
Puis pour Mars ce sera EQUIV(10;$J$3:$J$33, etc... avec à chaque fois le numéro de la première semaine du mois.

[Tien 12]

OK, ça fonctionne. Seulement, c'est une valeur en dur. Si, une année, il y a un décalage du n° de la première semaine d'un mois, ça fausse la suite. Il y aurait moyen d'avoir une valeur relevée du tableau, par exemple un NO.SEMAINE.ISO(B*) ?

Ou alors une feuille "Récap" qui reprendrait les valeurs souhaités de tous les mois, afin de faire les calculs ailleurs ou tous les jours seraient à la suite (sur 365/366 lignes).

[Grosnours]

La réponse 2 serait la plus simple.
Imagine que tu crée une nouvelle feuille "Mois" dans laquelle tu as un tableau du genre Janvier 12345 Février 6789 Mars 101112.....
Avec Janvier en A1, Février en B1, etc...
Avec 1 en A2, 2 en A3, 6 en B2, 10 en C2 etc...

Il te suffirait alors de remplacer le "1" dans la formule du mois de Janvier par la valeur Mois!A2, celle de Février par Mois!B2, celle de Mars par Mois!C2, etc...
On en est pas encore à l'automatisation absolue, mais cela devient plus élégant.

[Wobak]

Vous cherchez une formule qui donne le numéro de la première semaine d'un mois en fonction de quelle valeur ?