Discussion :

[hary66]

Bonjour.

Dans une colonne de valeurs, je cherche l'indice de la première ligne où la valeur >= à MA_VALEUR.

C'est une colonne ou j'ai les températures d'un système et je cherche le moment ou le système atteint sa consigne.

J'ai essayé avec la focntion EQUIV

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=EQUIV(Consigne;C4:C133;0)

mais passe à coté de la première ligne si mes valeurs enregistrées passent de consigne-delta à consigne+delta.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=EQUIV(Consigne;C4:C133;1)

mais me donne l'indice de la ligne juste précédente même si la valeur = consigne à été enregistrée !

[Pierre Dumas]

Bonjour

Il faudrait publier une capture d'écran afin d'être un peu plus explicite (avec des données anonymisées bien entendu). Il faut aussi qu'il n'y ait pas de mise en forme pour les nombres.

Sinon, à priori, la fonction EQUIV est bien celle qu'il faut utiliser.

=EQUIV(Consigne;C4:C133;0) implique qu'Excel ne renverra un résultat que s'il trouve STRICTEMENT Consigne dans la plage C4:C133. Sinon, Excel renverra #N/A.
=EQUIV(Consigne;C4:C133;1) implique que les données de la plage C4:C133 DOIVENT être triées par ordre croissant. Avec ce paramètre, Excel renverra un résultat s'il trouve exactement Consigne ou la valeur inférieure la plus proche.

Voici le lien vers la documentation Microsoft de cette fonction.

En espérant que cela aide.

Bonne journée

Pierre Dumas

[Menhir]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=EQUIV(Consigne;C4:C133;1)

mais me donne l'indice de la ligne juste précédente même si la valeur = consigne à été enregistrée !

Dans ce cas, il suffit de faire =EQUIV(Consigne;C4:C133;1)+1.

Cela dit, 1 et -1 en troisième paramètres de EQUIV ne fonctionne que si les données sont triées.

[hary66]

Bon, je reformule donc ma demande avec un fichier .xls épuré mais représentatif.

Sur la page R, colonne C (TREACT), je souhaite récupérer le numéro de ligne où la température de 140°C est atteinte ou dépassée pour la 1ère fois : Attention ! Dans le fichier joint, la valeur 140°C apparait bien, et avec mes premiers essais avec la fonction EQUIV, j'arrive bien à récupérer la ligne 17. Cependant, il n'est pas certain que la valeur 140 soit enregistrée lors d'un prochain cycle de fonctionnement.
Il pourrait y avoir 135°C à la ligne 16 et 141 à la ligne 17 !

Et là, ma fonction EQUIV ne fonctionne plus !

[Pierre Fauconnier]

Ton tableau ne nous aide pas beaucoup plus.

Tu devrais travailler avec un tableau structuré. Tes formules y gagneraient niveau rédaction et lecture.

Pour trouver la ligne où la t° est atteinte ou dépassée, tu devrais trier les lignes par ordre décroissant sur C et utiliser =EQUIV(140;C:C;-1). Ca te permettra de trouver le temps en A (Bien sûr, il faut trier TOUT ton tableau sur la colonne C).

Si tu ne peux pas trier ton tableau parce que tu dois garder ta chronologie ( ce que je peux concevoir dans le cas présent), je ne vois pas bien comment tu vas pouvoir réaliser l'opération sans passer par une fonction perso en VBA. (Edit: Voir mon message suivant)

[Pierre Fauconnier]

Je pense qu'il y a une solution EXCEL et que tu pourrais t'en sortir de la façon suivante:


Principe de fonctionnement:

• On crée une matrice contenant soit le numéro de ligne si la t° est >= limite, soit une chaine vide;
• On récupère la valeur minimum de cette matrice, soit le numéro de ligne de la feuille de la ligne contenant la première valeur >= à la limite;
• On retranche le numéro de la ligne d'entête du tableau et on a le numéro de la ligne du tableau;
• On utilise ce numéro avec INDEX.

Formule : =INDEX(Tableau1[Temps];MIN(SI(Tableau1[T°]>=D1;LIGNE(Tableau1);""))-LIGNE(Tableau1[#En-têtes]))

Pour les versions antérieures à XL365, il faudrait valider en matricielle (SHIFT+CTRL+ENTER)

[hary66]

Ton tableau ne nous aide pas beaucoup plus.
...
Si tu ne peux pas trier ton tableau parce que tu dois garder ta chronologie ( ce que je peux concevoir dans le cas présent), je ne vois pas bien comment tu vas pouvoir réaliser l'opération sans passer par une fonction perso en VBA.

Je ne sais pas s'il est utile que je le précise, mais je suis novice avec Excel.

En fait, au départ j'étais en effet parti à travailler en VBA, mais chez nous (là où je bosse), ils ont apparemment l'habitude de travailler avec des formules. Comme dit mon chef, on peut facilement voir d'où vient le résultat en regardant la formule.

Et puis en faisant mes premiers pas VBA, j'ai vu que c'était en phase d'être remplacé par PowerQuery je crois.
J'ai trouvé assez peu d'info sur PowerQuery ... Je me suis dit que ce truc spécifique windows n'était pas viable dans le temps, et j'avais donc laisser tombé avec l'idée d'essayer de me débrouiller avec Python openpyxl utilisable partout.

C'est vrai que ces formules, c'est à n'y rien comprendre : En plus, ce n'est pas pareil en version Française ou Anglaise, la syntaxe diffère (, . .

Je me replongerais bien dans VBA si j'étais certain que ce soi un truc durable. Ou est il préférable de s'investir plutôt dans PowerQuery ?

[Pierre Fauconnier]

Je t'ai montré une solution Excel, qui s'appuie sur les tableaux structurés (elle pourrait s'appuyer sur des plages classiques mais avec XL365, ce serait dommage de se priver des tables de données )

On pourrait également envisager une solution PowerQuery, mais sur le nombre de lignes que tu donnes, je ne vois pas trop l'intérêt. Le séquençage de la solution serait identique au développement de la formule que je t'ai fournie.

[hary66]

Je t'ai montré une solution Excel, qui s'appuie sur les tableaux structurés (elle pourrait s'appuyer sur des plages classiques mais avec XL365, ce serait dommage de se priver des tables de données )

On pourrait également envisager une solution PowerQuery, mais sur le nombre de lignes que tu donnes, je ne vois pas trop l'intérêt. Le séquençage de la solution serait identique au développement de la formule que je t'ai fournie.

Nos réponses se sont croisées.
tableaux structurés et tables de données ? c'est pareils ?

J'ai suivi le lien indiqué concernant les tableaux structurés, mais je peux faire 1/10ème du tuto uniquement. (jusqu'au Redimensionnement)

Ensuite, je n'arrive pas à retrouver les impressions écran du tuto pour faire les exercices dans mon Excel365 !

Ces tableaux structurés sont utiles pour travailler avec VBA ?

Je ne sais pas par quoi je devrais commencer et quoi utiliser ! VBA, PowerQuery, tableaux structurés ..

[Pierre Fauconnier]

D'abord les tableaux structurés. Ils t'aideront tant en Excel qu'en VBA. Power Query travaille bien mieux avec des tableaux structurés aussi, et restitue les données dans Excel sous forme de tableau structuré... Tu les utiliseras donc tout le temps dans Excel.

Dans le tuto, ne passe pas du temps sur le redimensionnement du tableau, puisque j'explique qu'Excel redimensionne automatiquement en fonction de l'ajout/suppression de lignes/colonnes. Cela simplifie grandement les formules. Il faut "juste" se familiariser avec les références structurées, telles que je les utilise dans la solution que j'ai proposé à ta demande.

As-tu essayé ma solution Excel avec le tableau structuré? Je peux déposer ce soir un fichier exemple si tu le souhaites.

[hary66]

Non, je n'ai pas essayé votre solution. Pour l'instant c'est du Chinois pour moi.

Sinon, oui je veux bien votre exemple. Au moins que je vois un peu ce que ça donne.

Edit : J'ai passé un peu plus de temps sur la solution proposée et comprend un peu mieux comment procéder.

Je vais essayer demain car malheureusement, je suis sous Linux chez moi et ne possède encore moins Excel !

Par contre, j'ai cherché des tuto sur les tableaux structurés. Je ne trouve rien !

[Pierre Fauconnier]

Voici le schéma développé et le fichier. Pour la solution en E1, tu n'as évidemment pas besoin des colonnes et des cellules en vert. Elles sont juste là pour décortiquer le raisonnement de la formule en E1.

[hary66]

Ma parole, vous êtes véloce !

Je viens d'éditer mon message précédant !

[Pierre Fauconnier]

[...]

Par contre, j'ai cherché des tuto sur les tableaux structurés. Je ne trouve rien !

J'espère que que mon tuto est assez complet et progressif sur les tableaux structurés (= tables de données) pour ne pas devoir aller chercher "à la concurrence"

Par contre, je n'ai pas compris ce que tu voulais dire dans ce qui suit:

[...]

J'ai suivi le lien indiqué concernant les tableaux structurés, mais je peux faire 1/10ème du tuto uniquement. (jusqu'au Redimensionnement)

Ensuite, je n'arrive pas à retrouver les impressions écran du tuto pour faire les exercices dans mon Excel365 ![...]

[hary66]

Je voulais dire que dans mon Excel365, je ne trouve pas les mêmes "onglets" à partir du paragraphe IV-B. Ruban spécifique de ton tuto !

[Pierre Fauconnier]

je viens de relire le début de mon tuto et il y manque une info (j'ai noté de l'ajouter dans le tuto): Le ruban spécifique au tableau structuré n'apparaît QUE lorsque la cellule active appartient au tableau.

Pas de ruban car la cellule active n'appartient pas au tableau




Ruban accessible (mais pas forcément déroulé!) car la cellule active appartient au tableau

[papouclo]

Bonjour,
Puisque le travail se fait sur une version XL-365, il faudrait peut être essayer d'utiliser les nouvelles fonctions (en particulier la fonction FILTRE) permettant d'écrire des formules matricielles dynamiques avec une réelle économie d'écriture.

Sur le fichier R.F.xslx envoyé par hary66, en supposant que la valeur à atteindre est dans une cellule nommée consigne, la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=MIN(FILTRE( A:A ; C:C >= consigne))

renvoie pour une valeur de 140 de la consigne une réponse de 45 pour le temps en minutes au bout duquel les 140° sont atteints (la valeur 140 existe dans la colonne C).
Mais si consigne prend une valeur de 150° (non inscrite dans la colonne C), la formule renvoie la bonne réponse de 54 mn temps au bout duquel le système atteint pour la première fois une température (152°) supérieure à la consigne.

La formule

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=FILTRE( A:A ; C:C >= consigne)

inscrite dans une colonne renverrait une plage unicolonne de 81 lignes (pour une consigne de 140), la première ligne étant le titre " Temps (minutes) ", les 80 autres donnant tous les temps pour lesquels la température est supérieure ou égale à 140.

Dans le fichier proposé par Pierre Fauconnier (excellente réponse bien sûr au problème posé), la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=MIN(FILTRE(Tableau1[Temps];Tableau1[T°]>=D1))

donnerait la même réponse que la formule de Pierre.
Outre sa concision, la formule avec FILTRE présente peut-être l'avantage de ne pas être tributaire de la place dans la feuille du tableau de données.
Cordialement,
Claude

[Pierre Fauconnier]

Salut Claude

C'est à force de taper sur le clou qu'on fera entrer ces nouvelles fonctions dans les moeurs.


Perso, je ne suis pas contre le"tout au 365", et FILTRE() est une fonction intéressante, mais si le fichier est utilisé en XL2016 ou antérieur, ça plantera. C'est pourquoi j'hésite à proposer les fonctions 365 (FILTRE, TRIER, UNIQUE, MAX.SI.ENS, MIN.SI.ENS, ...). Mais je devrais peut-être présenter les différentes solutions (XL365 et antérieures)...

[...]
Outre sa concision, la formule avec FILTRE présente peut-être l'avantage de ne pas être tributaire de la place dans la feuille du tableau de données.[...]

Sauf erreur, ma formule n'est pas tributaire de la place du tableau dans le classeur, comme par ailleurs toute formule utilisant les références structurées...

[hary66]

Je suis en train de regarder ce que vous m'avez proposé et voir votre tuto aussi !

IV-A. Accès aux tableaux structurés

Je ne trouve pas le gestionnaire de noms !

[Pierre Fauconnier]

Je suis en train de regarder ce que vous m'avez proposé et voir votre tuto aussi !

IV-A. Accès aux tableaux structurés

Je ne trouve pas le gestionnaire de noms !

Onglet Formules...