Formule extraction Mois, année et compter

Bonjour chers membres,

Pouvez-vous m'aider à trouver la bonne formule s'il vous plait ? Je joins un fichier test sous le lien en fin de message.

Je dois reporter dans le 1er onglet, le nombre de personnes formées pour le mois donné. Le format mois utilisé dans le 1er onglet est "31/01/20) par exemple pour janvier 2020. Les personnes formées et leur date de formation sont mentionnées dans le 2ème onglet sachant qu'il y a des données 2019 et 2020.

Le fichier d'origine est sous Google Sheet, mais je vous le mets en format Excel car je pense que c'est plus facile.

Venez à mon secours !

Merci par avance.

Bonjour,

exemple pour les dates "Excel"

=SOMMEPROD((Sheet2!$B$2:$B$10=A2)*(ANNEE(Sheet2!$C$2:$C$10)=ANNEE(B2))*(MOIS(Sheet2!$C$2:$C$10)=MOIS(B2)))

Bonjour à tous,

Je suis malheureusement toujours à la recherche d'une solution sous Google Sheet. Cela fait une semaine que je suis dessus. J'avais trouvé une solution Excel, mais qui n'est pas reconnue sous Google Sheet. Si vous avez une solution (même en passant par une autre formule), je suis preneuse. Je joins mon fichier test (format Excel car je n'arrive pas à le laisser en Google Sheet pour l'envoyer, mais le format de travail est Google Sheet).

Pour rappel, je cherche à comptabiliser via une formule, sur le premier onglet, le nombre total de personnes formées uniquement en 2020 par mois et par entité .

Sur le 2ème onglet, j'ai mes données. Les dates sont 1 colonne sur 2 à partir de la colonne F (alternance colonne date de certification et colonne nom de la certification).

Merci à vous.

Bonjour,

1° lecture de ton excel, c'est un sujet "avancé" !

Il y a de belles formules, mais c'est transposable dans gSheets sans souci.

Je peux faire rapidement.

Ma principale question est :

Qu'est-ce qui définit qu'une personne est formée ?

une date dans les colonnes date ? (comme dans la solution excel)

mais est-ce qu'il ne faut pas aussi tenir compte de :

un N° de certif dans les colonnes # ?

un status Ok ?

une Entity pas vide ? (pour l'instant, ces lignes ne sont pas comptées)

Merci d'avance pour ces éclairages...

Et si tu veux être constructif : initialise un classeur Sheets, et de le partager en public.

Comme ça j'intervient directement, c'est tellement plus sheets-pratique !

A bientôt

EMX

Bonjour Exacel,

La date de formation définit que la personne est formée. Je n'ai pas toujours le n° de certification.

Pour les Entity vides il faut laisser tel quel. Ne pas les comptabiliser.

Mon problème est que la formule dans le fichier Google me renvoit 0 alors que c'est faux. On m'avait conseillé de nommer des plages dans l'onglet Certifications ("Entity", pour les entités et "Tableau" pour les données mais je n'arrive pas à créer ces plages dans Google.

Entity : =DECALER(Certifications!$C$4;;;NBVAL(Certifications!$A:$A))

Tableau : =DECALER(Certifications!$F$4;;;NBVAL(Certifications!$A:$A);68)

Je joins le lien menant à un fichier test. Celui -ci se rapproche plus de mon fichier de réel

Merci à toi (et aux autres qui voudront bien collaborer).

Bjr Del & Nel,

C'était bien un sujet avancé !

Vous avez juste buté sur les plages nommées de Sheets qui ne sont pas dynamiques (formule impossible).

J'ai contourné en utilisant DECALER directement dans les calculs.

Pour cela, j'ai rajouté 2 compteurs (lignes et colonnes) de la plage utile des données,

pour ne pas les recalculer à chaque fois, et rendre les formules plus digestes.

Ensuite, il suffit de dérouler la superposition des conditions :

• Entity = Entity
• Colonne (date) est paire
• Mois = Mois
• Colonne (#) pas vide (un O, un OK, un N°...) <- J'ai rajouté cette condition,

Tu peux la supprimer si elle est trop restrictive.

Si tout ca est vrai, on somme 1 et le total donne le nombre voulu.

NB : C'est bien le nombre de certifs, et pas le nombre de personnes

=ArrayFormula(SOMME(1*
(DECALER(Certifications!$C$3,1,0,NbRows,1)=$A5)*
(EST.PAIR(COLONNE(DECALER(Certifications!$F$3,0,0,1,NbCols))))*(TEXTE(DECALER(Certifications!$F$3,1,0,NbRows,NbCols),"YYYYMM")=TEXTE($B5,"YYYYMM"))*
(DECALER(Certifications!$F$3,1,1,NbRows,NbCols)<>"")
))

Il y a une subtilité ds la formule :

le test des colonnes "date" & "#" nécessite de décaler d'une colonne la plage de test des #

(DECALER(Certifications!$F$3,1,1,NbRows,NbCols)

Sur la lancée, j'ai aussi fait un récap matriciel, plus condensé, plus parlant.

Et un TCD, si tu veux t'amuser.

C'est toujours par ici, dans ton partage.

Avec les formules vues dans votre classeur, je pense que vous pourrez facilement vous réapproprier le sujet.

A vous de jouer maintenant...

Au plaisir

EMX

Bonjour,

Merci beaucoup, beaucoup.

Cela fonctionne à merveille, j'ai même droit à une exploration supplémentaire. C'est fantastique !

J'ai un autre challenge à relever. Je travaille dessus et si ça coince je sais où m'adresser

Bonjour,

Je reviens avec une demande supplémentaire. Il me faut maintenant isoler :

1) intégrer dans le calcul de janvier les certifications des années antérieures (uniquement sur janvier)

2) Calculer séparément les certifications qui correspondent aux colonnes AP, AR, AT, AX, AZ, BB, BF, BH, BJ, BL, B, BP, BR, BT. Certifications qu'on nommera "Micro Certifications et de ce fait faire en sorte qu'elle ne soient pas prises en compte dans la formule de Exacel. La formule relative aux micro certifications doit être portée en colonne G de l'onglet Recap du fichier joint.

J'avoue que j'en est mal au crâne et évidemment je dois rendre cela rapidement. Du coup je me dis que plusieurs cerveaux valent mieux que mon seul cerveau embué.

Donc Exacel et les autres à l'aide à nouveau.

Merci pour votre aide précieuse.

Bonjour,

Quand on aime, on ne compte plus !

Il est quasiment nécessaire de pouvoir différencier les micro des autres,

et de baser la sélection sur un critère.

Par exemple, d'utiliser la ligne 1 pour y mettre cette info.

D'ailleurs tu as commencé dans l'idée en les marquant en noir.

Ok, la couleur c'est bien (c'est pas toi qui dira le contraire !),

mais avoir une valeur c'est mieux pour faire un test Oui/Non.

Est-ce qu"on peut oser se permettre d'envisager espérer pouvoir tenter cela,

comme je l'ai proposé dans ton fichier partagé ?

J'espère avoir compris ta demande pour les cumuls sur Janvier,.

Je propose plutôt de faire un cumul des années précédentes,

que tu ajoutes ensuite à Janvier courant, si besoin,

Ou même, soyons fous, c'est pas cher, un cumul pour chaque année civile révolue,

puis par mois de l'année courante.

Je vous ai aussi proposé précédemment un calendrier glissant sur 12 mois, mais ça n'a pas percuté.

Vous avez une habitude de travailler par exercice fiscal. Soit !

Mais c'est franchement souhaitable de garder cette distinction :

2018 / 2019 / Jan 2020 / Fev 2020 / etc

Alors, on est bien ou... on est mal, patron ?

A+

EMX

Merci encore. J'ai regardé rapidement car je suis sollicitée sur 300 choses en même temps, mais j'ai l'impression que la colonne certifications intègre les micro-certifications alors qu'elles devraient en être soustraites. L'objectif final étant que j'ai une colonne pour les certifications (hors micro), une pour les micro uniquement, et une autre qui représentera le total des certifications (certifications + micro).

Pour ajouter, en janvier 2020, les certifications historiques (celles passées sur les autres années) j'aurai préféré l'automatiser en l'ajoutant à la formule de janvier cela éviterait une manipulation (étant donné que je ne suis pas seule à travailler sur le fichier). Mais si trop compliqué...

C'est déjà beaucoup d'avancée grâce à vous.

Bjr Team,

Préambule

Note bien que je n'ai pas mission de sortir le classeur complet et opérationnel (en tous cas pas pour l'instant).

Via ce forum, nous essayons d'aider chacun à passer un point dur en proposant des pistes de solutions.

Après, il revient à chacun de "déployer" dans son propre contexte, ce qu'il aura appris ici.

Q1/R1. Micro or not ?

Oui, je n'ai fait que rajouter le comptage "micro", comme exemple de ce qu"on peut faire.

Il suffit de réappliquer le principe sur le comptage général.

C'est tellement peanuts, que je l'ai fait sur les premières lignes.

Q2/R2. "Smart Dates"

Pour ce faire, est-ce qu'on peut ajouter 2 ou 3 colonnes dans Recap,

pour se faciliter la vie, comme proposé dans le fichier partagé ?

J'ai retenu la séparation par année civiles, parce que c'est conceptuellement aberrant de mélanger ces données dans le Janvier courant. Tu pourras adapter sans trop de difficulté si tu y tiens vraiment.

Merci du retour, a prochaine.

Et n'oublie pas la doctrine : "Dévouement et Dévotion pour la Team !"

EMX

Merci Exacel,

Désolée, je ne voulais pas te donner l'impression (et aux autres) d'attendre un dossier opérationnel. C'est juste que je voulais donner un retour rapide, même si je suis super chargée en ce moment.

Effectivement, j'avais pensé ajouter des colonnes à l'onglet Recap. Je vais examiner cela très rapidement.

Je te remercie énormément de prendre du temps et d'apporter un support aussi performant et...je retiens la doctrine.