IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

  1. #1
    Membre du Club
    Homme Profil pro
    technicien
    Inscrit en
    juillet 2016
    Messages
    51
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Alpes Maritimes (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : technicien
    Secteur : Santé

    Informations forums :
    Inscription : juillet 2016
    Messages : 51
    Points : 41
    Points
    41
    Par défaut Récupérer une date en fonction de la dernière donnée rentrée dans un tableau
    Bonjour a tous,

    J'ai le tableau suivant :

    Nom : excel.jpg
Affichages : 83
Taille : 105,1 Ko

    Je souhaiterais pouvoir connaitre la date (mois et année) correspondant au dernier chiffre du tableau.
    Ici par exemple la dernière cellule remplie et la E9 je souhaiterais donc récupérer la date 04/2021

    Merci d'avance pour votre aide

  2. #2
    Expert confirmé
    Homme Profil pro
    Electrotechnicien
    Inscrit en
    juillet 2016
    Messages
    2 858
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 67
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Electrotechnicien

    Informations forums :
    Inscription : juillet 2016
    Messages : 2 858
    Points : 4 964
    Points
    4 964
    Par défaut
    Bonjour,

    Essayez ceci:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =INDEX($A$1:$M$10;EQUIV(9^9;$B:$B;1);EQUIV(9^9;INDIRECT("B" & P2 &":M" & P2);1)+1)
    Cdlt

    Edit: je me suis trompé, c'est la date que vous vouliez pas la dernière valeur. Je reprends tout ça.

  3. #3
    Membre éprouvé
    Homme Profil pro
    Formateur et développeur bureautique
    Inscrit en
    mars 2007
    Messages
    565
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Formateur et développeur bureautique
    Secteur : Conseil

    Informations forums :
    Inscription : mars 2007
    Messages : 565
    Points : 1 170
    Points
    1 170
    Par défaut
    Bonjour

    Sur le même principe qu'ARTURO83 (plus rapide), mais décomposé.

    Nom : dernier.png
Affichages : 62
Taille : 25,4 Ko


    En C15, on cherche la position de la dernière valeur saisie dans les mois de janvier : =EQUIV(9^9;B2:B13)

    En D15, on trouve l'année en se basant sur la valeur trouvée en C15 : =INDEX(A2:A13;C15)

    En C16, on cherche le nombre de mois remplis sur la ligne correspondant à l'année désirée : =NBVAL(DECALER(A1;C15;1;1;12))

    En D16, on trouve le mois en se basant sur la valeur trouvée en C16 : =INDEX(B1:M1;C16)


    Bien entendu, on peut tout synthétiser en une seule formule.

    En espérant que cela aide aussi.

    Bonne soirée

    Pierre Dumas
    Merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion.

  4. #4
    Expert confirmé
    Homme Profil pro
    Electrotechnicien
    Inscrit en
    juillet 2016
    Messages
    2 858
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 67
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Electrotechnicien

    Informations forums :
    Inscription : juillet 2016
    Messages : 2 858
    Points : 4 964
    Points
    4 964
    Par défaut
    Voilà pour trouver la date:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =INDEX($A$1:$M$1;0;EQUIV(9^9;INDIRECT("B"& EQUIV(9^9;$B:$B;1) &":M"&EQUIV(9^9;$B:$B;1));1)+1)&"-"&INDIRECT("A" &EQUIV(9^9;$B:$B;1))

  5. #5
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    novembre 2003
    Messages
    17 379
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 17 379
    Points : 50 208
    Points
    50 208
    Billets dans le blog
    92
    Par défaut
    Salut.


    Perso, je n'aime pas:
    • Le "truc" du 9^9, quand je peux éviter, j'évite. Pourquoi pas un NBVAL sur la plage B2:B10?
    • le 9^9 sur toute la colonne B de la formule de Arthuro car s'il y a des valeurs numériques en dessous du tableau (par exemple une ligne de total), elles vont être prises en compte dans la formule qui retournera une valeur erronée;
    • Le Indirect("B"... de la formule de Arthuro car si on déplace le tableau, on va devoir modifier la formule (c'est le problème récurrent de INDIRECT);
    • le fait que si on met des données textuelles dans le tableau, les formules avec 9^9 ne fonctionnent plus.



    Nom : 2021-04-29_205618.png
Affichages : 58
Taille : 116,0 Ko


    Nom : 2021-04-29_210120.png
Affichages : 56
Taille : 83,5 Ko
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  6. #6
    Membre éprouvé
    Homme Profil pro
    Formateur et développeur bureautique
    Inscrit en
    mars 2007
    Messages
    565
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Formateur et développeur bureautique
    Secteur : Conseil

    Informations forums :
    Inscription : mars 2007
    Messages : 565
    Points : 1 170
    Points
    1 170
    Par défaut
    Bonsoir
    Citation Envoyé par Pierre Fauconnier Voir le message
    Perso, je n'aime pas:

    Le "truc" du 9^9, quand je peux éviter, j'évite. Pourquoi pas un NBVAL sur la plage B2:B10?
    [...]
    Et tu as tort de ne pas aimer, car tu n'as pas bien regardé les données de tico06 et les miennes
    En effet, les premiers mois peuvent ne pas être remplis pour la première année. Donc un NBVAL tout court ne passe pas.

    J'avais envisagé au départ : =NBVAL(B2:B13)+NB.VIDE(B2) mais ça fait un peu plus long. Surtout quand on imbrique les formules les unes dans les autres.

    Bonne nuit

    Pierre
    Merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion.

  7. #7
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    novembre 2003
    Messages
    17 379
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 17 379
    Points : 50 208
    Points
    50 208
    Billets dans le blog
    92
    Par défaut
    @ Pierre: J'avais travaillé sur un tableau que j'ai construit sans prendre garde à cette spécificité, mais ce "problème" est contournable très rapidement, soit par le comptage du NB.VIDE comme tu le propose, soit en mettant une indication actant que l'on ne dispose pas de la donnée. Perso, je préfère une formule plus longue mais solide à une "astuce" fragile, non expliquée de surcroît et dont il faut connaître les limites.


    Le problème, c'est que ce tableau est à mi-chemin entre un tableau de données (structuré ou pas) et un TCD. J'y reviendrai dans le prochain message.

    Si j'avais à travailler avec un "tableau de données" tel que celui-là et que des données étaient manquantes dans les premiers mois, je mettrais une indication qu'elles sont manquantes, et le NB.VAL passerait sans problème. De plus, l'astuce du 9^9 nous oblige à considérer que l'on ne peut avoir des trous que en début et en fin de tableau, ce que l'on déduit alors que ce n'est pas formulé, mais ça poserait problème si la donnée de janvier 2021 venait à manquer, que ce soit avec 9^9 ou avec NBVAL (sans boucher les trous...), d'ailleurs...


    Avec l'astuce du 9^9, non expliquée au demandeur, ça va fonctionner dans ce cas-ci, mais ça ne peut pas être généralisé...

    Nom : 2021-04-30_062710.png
Affichages : 44
Taille : 184,6 Ko


    Nom : 2021-04-30_063315.png
Affichages : 45
Taille : 183,5 Ko
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  8. #8
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    novembre 2003
    Messages
    17 379
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 17 379
    Points : 50 208
    Points
    50 208
    Billets dans le blog
    92
    Par défaut
    Le problème plus fondamental est que ce tableau se situe entre le tableau de données (structuré ou pas) et le TCD d'analyse. Normalement, c'est un tableau de résultat ou d'analyse, pas un tableau de données. On y mélange donc saisie et présentation de données, ce qui constitue un des 7 péchés capitaux d'Excel. Mes bonnes pratiques voudraient que les données soient stockées dans un tableau structuré, ici à 3 colonnes Année - Mois - Valeur. On peut alors ne pas se tracasser des manquants et utiliser les outils d'Excel pour l'analyse (XXX.SI.ENS, TCD, matricielles)...


    Dans le tableau suivant, les premiers mois de 2014 sont manquants, mais ça n'impacte pas le résultat (j'ai masqué les lignes 11 à 83) et on voit que l'on peut récupérer simplement le dernier mois de saisie, avec une formule très simple si les données sont renseignées par ordre croissant, et avec une formule à peine plus complexe si on craint que les données puissent être dans le désordre:

    Nom : 2021-04-30_065032.png
Affichages : 45
Taille : 13,9 Ko


    Le tableau initial peut alors être reconstitué par TCD, par Power Query ou par formule:

    Nom : 2021-04-30_065929.png
Affichages : 46
Taille : 184,1 Ko



    Si je recevais le tableau initial et que je devais travailler avec, la première chose que je ferais serait de le transformer en tableau structuré bien construit à 3 colonnes comme illustré plus haut, ce qui "coûte" 4 clics de souris en Power Query. Je dispose alors d'un tableau bien formé qui me permet de travailler avec les outils d'Excel sans "astuces" ou formules alambiquées

    Nom : 2021-04-30_070552.png
Affichages : 45
Taille : 23,3 Ko

    Nom : 2021-04-30_070612.png
Affichages : 45
Taille : 29,3 Ko


    Si les gens suivaient ces bonnes pratiques, 80% des questions posées sur le forum n'auraient pas lieu d'être et l'utilisation d'Excel serait simple. La vie avec Excel devient compliquée parce qu'on se la complique, pas parce que l'outil est compliqué...
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  9. #9
    Membre du Club
    Homme Profil pro
    technicien
    Inscrit en
    juillet 2016
    Messages
    51
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Alpes Maritimes (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : technicien
    Secteur : Santé

    Informations forums :
    Inscription : juillet 2016
    Messages : 51
    Points : 41
    Points
    41
    Par défaut
    Merci a tous pour votre aide et vos réponses très instructives qui m'ont permis d'apprendre de nouvelles choses et de répondre a ma demande.
    Par contre j'ai essayé par curiosité de reconstituer la mise en forme du tableau par formule comme indiqué par Mr Fauconnier:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =SIERREUR(INDEX(Tableau2[valeur];EQUIV(1;(Tableau2[Année]=$F14)*(Tableau2[Attribut]=G$13);0));"")
    Malheureusement cela ne fonctionne pas. je n'ai pas d'erreur au niveau de la formule mais rien ne se passe.
    Y'a t'il quelque de particulier a faire ?

  10. #10
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    novembre 2003
    Messages
    17 379
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 17 379
    Points : 50 208
    Points
    50 208
    Billets dans le blog
    92
    Par défaut
    je ne l'ai pas précisé dans ma réponse, mais tu dois valider cette formule en matricielle => SHIFt+CTRL+ENTER. Il ne faut pas saisir les accolades, Excel les ajoutera de part et d'autre de la formule lors de la validation "en matricielle"

    Etant en 365, version pour laquelle ce n'est plus nécessaire, j'ai oublié de le mentionner. Désolé.
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  11. #11
    Membre du Club
    Homme Profil pro
    technicien
    Inscrit en
    juillet 2016
    Messages
    51
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Alpes Maritimes (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : technicien
    Secteur : Santé

    Informations forums :
    Inscription : juillet 2016
    Messages : 51
    Points : 41
    Points
    41
    Par défaut
    J'avais bien cette notion de SHIFt+CTRL+ENTER pour les formes matricielle d'excel
    J'ai bien saisie la formule sans accolade et validé avec SHIFt+CTRL+ENTER. La formule apparait bien avec les accolade.
    Malheureusement rien ne se passe ?

    Nom : excel.jpg
Affichages : 40
Taille : 175,2 Ko

  12. #12
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    novembre 2003
    Messages
    17 379
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 17 379
    Points : 50 208
    Points
    50 208
    Billets dans le blog
    92
    Par défaut
    F14 et G13 sont vides ^^

    J'ai donné cette formule dans le cadre de la reconstitution de ton tableau initial sur base du tableau structuré. Tu dois donc avoir renseigné les années en F14:F22 et les mois en G13:R13
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  13. #13
    Futur Membre du Club
    Homme Profil pro
    ingénieur
    Inscrit en
    mars 2015
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : ingénieur

    Informations forums :
    Inscription : mars 2015
    Messages : 4
    Points : 9
    Points
    9
    Par défaut
    Bonjour
    Je vous propose en version 365
    Code formule : Sélectionner tout - Visualiser dans une fenêtre à part
    =MAX((B2:M10<>0)*CNUM(B1:M1&A2:A10))
    à condition d'ajouter un accent à Février
    sinon
    Code formule : Sélectionner tout - Visualiser dans une fenêtre à part
    =MAX((B2:M10<>0)*DATE(A2:A10;EQUIV(B1:M1;B1:M1;0);1))

    je ne peux pas tester mais ça doit fonctionner en formule matricielle dans les versions antérieures d'Excel.

    Cordialement
    Stéphane

  14. #14
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    novembre 2003
    Messages
    17 379
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 17 379
    Points : 50 208
    Points
    50 208
    Billets dans le blog
    92
    Par défaut
    Citation Envoyé par Raccourcix Voir le message
    [...]
    Joli, et tellement simple

    Perso, je préfèrerais max(B2:M10<>"") car rien ne dit que l'on ne peut pas avoir un mois à 0... Mais avoir pensé à la matricielle, c'est beau!
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  15. #15
    Futur Membre du Club
    Homme Profil pro
    ingénieur
    Inscrit en
    mars 2015
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : ingénieur

    Informations forums :
    Inscription : mars 2015
    Messages : 4
    Points : 9
    Points
    9
    Par défaut
    Merci Pierre pour le retour et en effet, max(B2:M10<>"") est mieux si la dernière valeur est 0.

    Les formules matricielles sont très puissantes mais j'avoue les utiliser de moins en moins bien que je sois maintenant avec 365 (donc formules matricielles plus facile et le # utile dans les références) en plus SEQUENCE, TRI, RECHERCHEX, EQUIVX, UNIQUE ... sont intéressantes et attendues depuis longtemps.

    En effet, des tableaux associés à la puissance de Power Query sont à la base de l'immense majorité de mes fichiers, complété par un peu de Power Pivot avec du DAX et un soupçon de VBA (de moins en moins, le strict nécessaire non géré dans Query).
    Je ne peux que conseiller à tous les utilisateurs d'Excel de se plonger dans Query et Pivot, le champ des possibles est considérablement élargi.
    Pour ma part, je constate un gain de productivité important lors de la création des fichiers et les utilisateurs sont ensuite plus satisfaits qu'avec des fichiers construits "à l'ancienne".

    Cordialement
    Stéphane

Discussions similaires

  1. Réponses: 6
    Dernier message: 28/05/2019, 13h34
  2. Réponses: 5
    Dernier message: 01/06/2006, 11h36
  3. [SQL] Une date en fonction du num de semaine
    Par frederic_s dans le forum Oracle
    Réponses: 1
    Dernier message: 07/03/2006, 11h52
  4. Réponses: 10
    Dernier message: 09/01/2006, 18h39
  5. Réponses: 1
    Dernier message: 07/06/2005, 14h00

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo