Discussion :

[C15nantes]

Bonjour à tous et à toutes,

J'ai un tableau correspondant aux cotisations payées par les adhérents d'une association (voir PJ)
J'ai une ligne par adhérent
En colonne, j'ai nom, prénom... une colonne par année de 1983 à 2036
Dans chaque colonne j'ai 0 ou 25, selon que l'adhérent a payé ou pas sa cotisation.

Dans la colonne F, je voudrais avoir la dernière année où l'adhérent a payé sa cotisation
Par exemple pour les 2 premiers de la liste, ce serait 2021, pour le 4 ième, ce serait 2018...

Je pense que la fonction Index/Equiv me donnerait la réponse...mais je me prends les pieds dans le tapis, où alors ce n'est pas la bonne formule.

Merci de votre aide
A+
C15

[Pierre Dumas]

Bonsoir

Si j'ai bien compris, il s'agit de savoir sur chaque ligne quelle est l'année qui correspond à la plus grande colonne qui contient un zéro.
Auquel cas, cette formule peut convenir :
=MAX.SI.ENS($H$1:$BI$1;H2:BI2;"=0")

Ceci étant, je me permets d'indiquer que ce tableau souffre d'un défaut de conception. Cela ne me semble pas une bonne idée d'avoir une colonne par année.
Il faudrait un tableau structuré avec trois colonnes : individu, année, montant de cotisation.
Effectivement, il y aura beaucoup de lignes (quelques centaines). Mais Excel est capable d'en supporter un million.

En espérant que cela aide.

Bonne fin d'après-midi

Pierre Dumas

[C15nantes]

Bonsoir
Merci de cette réponse.
Hélas elle ne renvoie que des 0 et non pas une année

Ce que je voudrais, c'est la valeur de la dernière année où l'on trouve 0, ensuite on trouve 25.
Comme dit dans le post, je voudrais avoir respectivement 2021, 2021, 2020, 2018, 2020 pour les 5 premiers adhérents de la liste.

Pour info, ce fichier est l'exportation d'une requête analyse croisée d'une base Access (le but final est l'exploitation de ce type de donnée dans un site de gestion d’adhérents en ligne.

Merci encore
A+
C15

[Pierre Fauconnier]

Salut.
Sur ton illustration, on voit des triangles verts dans les cellules des dates (le "intitulés") => tes dates sont probablement au format TEXTE. Or MAX.SI.ENS travaille sur des valeurs numériques. Voici une formule qui va corriger cela, à valider en matricielle (SHIFT+CTRL+ENTER) selon les versions:




Perso, en XL2019, j'utiliserais Power Query pour dépivoter et préparer les colonnes et proposer le tableau comme Pierre Dumas le suggère. Ca te permettrait d'utiliser tous les outils d'Excel (TCD, XXX.SI.ENS, ...)

[Pierre Dumas]

Bonsoir

Je confirme que la formule que j'ai donné fonctionne comme demandé. J'ai pris le temps de la tester. Si elle ne fonctionne pas sur le fichier, c'est que, comme l'a indiqué Pierre Fauconnier , les valeurs qui représentent les années sont en texte et non en numérique.

La formule que propose Pierre est parfaitement valide en cas de saisie texte et non numérique. Au détail prés qu'il faut mettre B2:G2=0 et non B2:G2=25 pour l'exemple désiré.

En espérant que cela aide aussi

Bonne soirée

Pierre Dumas

[Pierre Fauconnier]

Salut Pierre, tu vas bien?

[...]
Dans la colonne F, je voudrais avoir la dernière année où l'adhérent a payé sa cotisation[...]

Sur base de cela, j'ai compris qu'il fallait reprendre "le plus récent 25"... Mais le pdf contredit cela. A préciser par le demandeur, donc. Le problème principal provient des années en texte, qui devraient être corrigées en numériques à la source, selon moi.

Belle soirée

[Pierre Dumas]

Oui, je vais bien. Merci

Ceci étant, il est vrai que c'est très bizarre de considérer que les années où l'adhérent à payé sont celles où il y a un zéro. Et non celles avec un 25. C'est peut-être ce qui reste à payer

A+

PIerre

[C15nantes]

Bonjour aux 2 Pierre(s)

Merci de vos réponses.

Quelques observations :
1 Je ne sais pourquoi mes en-têtes de colonnes étaient au format texte (sans doute du fait de leur création sous Access).
Je les ai convertis au format nombre et alors la formule de Pierre Dumas fonctionne parfaitement

2 Ce tableau me permet de savoir les cotisations dues (on a quelques mauvais payeurs), c'est pour cela que quand c'est payé, c'est =0, quand c'est dû, c'est =25 (cotisation de base)

3 Cette discussion vous aura permis à vous deux de vous inquiéter de vos santés respectives

Merci de vos contributions qui ont résolu mon problème

A+
C15

[C15nantes]

Rebonjour,

Même si l'affaire peut être considérée comme résolue, j'ai une question supplémentaire

Qu'entend Pierre Fauconnier par dé"pivoter le tableau avec Power query ?

Merci
A+
C15

[Pierre Dumas]

Bonjour

Qu'entend Pierre Fauconnier par dé"pivoter le tableau avec Power query ?

C'est une opération, dans PowerQuery (installé dans Excel depuis de nombreuses années), qui permet de transformer un tableau à double entrées (type les adhérents en en-tête de lignes, et les années en entêtes de colonnes), en un tableau "à plat" (=une table dans Access).
Tout le contraire d'une requête d'analyse croisée dans Access

Bonne journée

Pierre Dumas

[Pierre Fauconnier]

[...]
Qu'entend Pierre Fauconnier par dé"pivoter le tableau avec Power query ?[...]

Pour compléter la réponse de Pierre Dumas par un exemple, voici une illustration d'un tableau présenté comme le tien (en bleu) que l'on va "dépivoter" avec Power Query pour obtenir une "table de données" (en vert). Cette organisation des données va te permettre d'utiliser tous les outils d'analyse Excel, notamment un tableau croisé dynamique (en ocre). Obtenir le tableau ocre sur base du bleu est impossible et t'obligera à le créer manuellement avec des formules +/- complexes et une mise à jour manuelle +/- ardue, alors qu'ici, grâce à la bonne organisation des données (tableau vert), on l'obtient sans formules grâce à quelques clics et on le met à jour par simple clic droit. Maîtriser au moins les bases des TCD est un atout indéniable.




Bien revoir et maîtriser les 5 piliers d'Excel, et surtout les 4 premiers, au moins dans les grandes lignes

[C15nantes]

Bonjour

Merci de ces infos complémentaires, je vais creuser.

A+
C15

[Pierre Dumas]

Bonjour

[...]
Bien revoir et maîtriser les 5 piliers d'Excel, et surtout les 4 premiers, au moins dans les grandes lignes

Pierre, tu évoques souvent ces 5 piliers (et 7 pêchés aussi je crois). Mais je n'ai trouvé aucune documentation là-dessus sur le Net. Est-ce que c'est quelque chose que tu as créé ? As-tu fais un article de blog, ou autre, sur ce sujet ?

Bonne journée à toi et aux autres lecteurs.

Pierre Dumas

[Pierre Fauconnier]

Pierre,

Oui, ce sont deux "inventions" à moi. Je suis un peu à la bourre pour en parler sur blog ou tuto ou vidéo

[C15nantes]

Bonjour

Indépendamment du fait que je vais creuser cette fonctionnalité de Power Query par laquelle je n'ai pas encore réussi à dépivoter mes colonnes, j'ai tout simplement transformé ma requête analyse croisée en requête sélection avant de l'importer sous Excel.
Je profite ainsi de toute la puissance d'Excel, dont les TCD

A+
C15