Discussion :

[JPierreM]

Bonjour à tous

Je souhaite récupérer, dans une cellule, les cinq dernières valeurs d’une liste dans la colonne A : A.
J’ai essayé

Code formule :

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

=DECALER(A$1;NBVAL(A:A)-{0;1;2;3;4};0)

Si je valide la formule d’une façon classique par la touche Entrée, j’obtiens uniquement la dernière valeur. Par contre si je valide la formule par la touche F9 j’obtiens une nouvelle formule ={55;44;33;22;11} où les cinq valeurs sont effectivement les cinq dernières valeurs de la liste. En validant cette nouvelle formule de façon classique elle n’affiche que 55 soit la dernière valeur, si je la valide en matriciel sur 5 cellules j’obtiens bien les cinq dernières valeurs mais sur 5 cellules. Ne peut-on avoir une formule qui me donnerai les cinq dernières valeurs dans la même cellule.
Merci de vos conseils

JP

[riton00]

Bonsoir,

Peut-être essayer

Code formule :

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

=DECALER(A1;EQUIV(9^9;A:A;1)-5;;1;1)

Slts

[JPierreM]

Bonsoir
La formule proposée renvoie la cinquième valeur depuis la fin et non les cinq dernières valeurs
Merci pour le retour
JP

[Raccourcix]

Bonjour
Et en ajoutant un JOINDRE.TEXTE à votre formule ?
Stéphane

[JPierreM]

Bonjour,
Malheureusement sur Excel 2016 on ne possède pas la fonction JOINDRE.TEXTE
Malgré tout merci, si quelqu'un avait une autre proposition.
A vous lire
JP

[Raccourcix]

Si pas de JOINDRE.TEXTE il faut concaténer les 5 derniers éléments avec des &.
Perso je préfère des INDEX que des DECALER qui est une fonction volatile.
Stephane

[78chris]

Bonjour à tous
Pour illustrer

Code formule :

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

=INDEX(A:A;EQUIV(9^9;A:A;1)-4)&", "&INDEX(A:A;EQUIV(9^9;A:A;1)-3)&", "&INDEX(A:A;EQUIV(9^9;A:A;1)-2)&", "&INDEX(A:A;EQUIV(9^9;A:A;1)-1)&", "&INDEX(A:A;EQUIV(9^9;A:A;1))

[jurassic pork]

Hello,
quand on a pas les dernières fonctions d'Excel ou que l'on arrive pas à trouver la formule pour effectuer un traitement, on peut penser aux fonctions personnalisées en VBA. Cela n'est pas compliqué à mettre en oeuvre, c'est souvent plus facile à décoder qu'une formule compliquée et les performances sont satisfaisantes. Le seul souci, c'est qu'il faut pratiquer le VBA.
Voici une fonction personnalisée en VBA qui retourne les n derniers éléments d'une colonne séparés par un séparateur :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Public Function LastElements(Col As Range, NbElems As Integer, Sep As String) As String
' Fonction qui va chercher les NbElems derniers éléments d'une colonne col
' le séparateur est sep
Dim LastLine As Long, CptLine As Long
LastLine = Col.Cells(Col.Rows.Count, 1).End(xlUp).row
If NbElems < LastLine Then
   For CptLine = LastLine - NbElems + 1 To LastLine
     LastElements = LastElements & Sep & CStr(Col.Cells(CptLine, 1).Value)
   Next CptLine
   LastElements = Mid(LastElements, Len(Sep) + 1)
Else
   LastElements = "NbElem >= lastLine"
End If
End Function

Performance pour une formule de 5 éléments : 12 microsecondes sur mon ordinateur Windows 10 avec Excel 2016 32 bits

Ami calmant, J.P

[JPierreM]

Bonjour,

Que j’utilise INDEX ou DECALER, ne change rien à ma demande. L’utilisation de CONCATENER fonctionne quel que soit la formule utilisée (DECALER, INDEX, INDEX avec EQUIV) mais cela est très peu pratique pour des utilisations ultérieures.
Dans la pièce jointe j’ai saisi les différentes formules qui me renvoient le résultat mais je pensais vraiment que l’on pouvait optimiser cela. Autant faire une concaténation directement en sélectionnant les valeurs une à une.

En reprenant mon idée initiale
Si je valide la fonction

Code :

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

=DECALER(A$1;NBVAL(A:A)-{0;1;2;3;4};0)

par la touche F9, Excel modifie cette formule et la transforme en

Code :

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

={55;44;33;22;11}

Pour décomposer la formule je crée une formule à partir de la fonction macro XL4 LIRE.CELLULE avec pour argument 41
Nom de la formule : LireCellule qui fait référence à :

Code :

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

=LIRE.CELLULE(41;!D8)

Je saisi ensuite la formule

Code :

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

=DROITE(GAUCHE(LireCellule;NBCAR(LireCellule)-1);NBCAR(GAUCHE(LireCellule;NBCAR(LireCellule)-1))-2)

J’obtiens le résultat voulu. Reste à solutionner la validation de la première formule par F9 ou obtenir cette formule d’une autre façon mais là je cale. Je joins un fichier reprenant le détail ci-dessus et les différentes solutions proposées.

Pour la solution par macro cela fonctionne très bien, mais mon souhait était justement de les éviter.
Merci de vos suggestions

JP

[78chris]

RE

Pour la solution par macro cela fonctionne très bien, mais mon souhait était justement de les éviter.

une fonction Excel4 est considérée comme du VBA par Excel avec obligation de sauvegarder en xlsm... Je ne vois pas trop l’intérêt par rapport à la fonction VBA...

Le traitement du texte est ce qui avance le moins vite chez MS : quand on voit tout ce qu'on fait simplement avec PowerQuery, on attend de pied ferme les nouvelles fonctions prévues mais 365 oblige...

EDIT : j'ai parlé trop vite : on vient d'avoir des fonctions de matrices parfaites pour ce cas

Code :

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

=EXCLURE(A1:A50;NBVAL(A1:A50)-5)

Plus dynamiques si on utilise des tableaux structurés...

[jurassic pork]

Hello,

Le traitement du texte est ce qui avance le moins vite chez MS : quand on voit tout ce qu'on fait simplement avec PowerQuery

Voici un exemple de code en Power Query M qui fait le traitement demandé dans cette discussion.
Dans le classeur de test il y a 3 tableaux :
Le tableau source TabSource avec deux colonnes Nom et Prénom où l'on cherche les derniers éléments
Le tableau de paramètres TabParams où l'on indique la colonne à utiliser, le nombre d'éléments et la chaîne qui sert de séparateur.
Le tableau de résultat Résultat où l'on affiche les n derniers éléments.
Voici le code de la requête en Power Query M :

Code :

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

1
2
3
4
5
6
7
let
    Params = Excel.CurrentWorkbook(){[Name="TabParams"]}[Content]{0},
    Source = Excel.CurrentWorkbook(){[Name="TabSource"]}[Content],
    Nom =  Table.Column(Source,Params[Col]),
    Result = Text.Combine(List.LastN(Nom, Params[NbElems]), Params[Séparateur])
in
    Result

A noter que pour rafraîchir la requête lorsque l'on change un des paramètres il y a le code VBA suivant :

Code :

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

1
2
3
4
5
6
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("TabParams")) Is Nothing Then
  'Debug.Print Target.Address
  ActiveWorkbook.Connections(1).Refresh
End If
End Sub

Ami calmant, J.P

[Raccourcix]

Si je valide la fonction

Code :

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

=DECALER(A$1;NBVAL(A:A)-{0;1;2;3;4};0)

par la touche F9, Excel modifie cette formule et la transforme en

Code :

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

={55;44;33;22;11}

Bonjour

Avec 365 vous auriez utilisé la fonction TABLEAU.EN.TEXTE

Code :

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

1
2
=TABLEAU.EN.TEXTE(DECALER(A$1;NBVAL(A:A)-{0;1;2;3;4};0);1)
renvoie {55;44;33;22;11}

mais évidemment pas dispo avec 2016.

Je ne suis pas utilisateur des fonctions personnalisées (je préfère faire au maximum sans macro) mais avant d'avoir JOINDRE.TEXTE j'avais une fonction perso qui permettait de le faire car cela me manquait vraiment.

La solution par Power Query est aisée à mettre en oeuvre, mis à part que je préfère nommer la requête plutôt que mettre un n°

Code :

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

ActiveWorkbook.Connections("Requête*-*xxx").Refresh

Sinon, @78chris il existe désormais la fonction PRENDRE (TAKE en VO) pour récupérer directement les dernières lignes avec le second argument négatif

Code :

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

=PRENDRE(plage;-5)

encore plus simple que le EXCLURE

Stéphane

[jurassic pork]

La solution par Power Query est aisée à mettre en oeuvre, mis à part que je préfère nommer la requête plutôt que mettre un n°

Code :

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

ActiveWorkbook.Connections("Requête*-*xxx").Refresh

En fait j'avais mis un n° parce que je n'arrive pas à faire fonctionner le code avec la la chaîne "Requête - xxxx". Ce sont les espaces au lieu d'étoiles qui posaient problème ?

[Raccourcix]

En fait j'avais mis un n° parce que je n'arrive pas à faire fonctionner le code avec la la chaîne "Requête - xxxx". Ce sont les espaces au lieu d'étoiles qui posaient problème ?

ce sont bien des espaces et non des étoiles, je ne sais pas pourquoi le code ne s'affiche pas comme je l'ai mis

Pour actualiser une requête nommée "Tableau1"

Code :

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

    ActiveWorkbook.Connections("Requête - Tableau1").Refresh

Stéphane

[78chris]

Bonjour à tous

@78chris il existe désormais la fonction PRENDRE (TAKE en VO) pour récupérer directement les dernières lignes avec le second argument négatif

Code :

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

=PRENDRE(plage;-5)

Bon, il faut que je regarde la liste des nouveautés... Merci beaucoup

[jurassic pork]

ce sont bien des espaces et non des étoiles, je ne sais pas pourquoi le code ne s'affiche pas comme je l'ai mis
Pour actualiser une requête nommée "Tableau1"

Code :

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

    ActiveWorkbook.Connections("Requête - Tableau1").Refresh

J'ai cette erreur là :


sur ce code :

Code :

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

ActiveWorkbook.Connections("Requête - Résultat").Refresh

Alors que le nom de la requête à l'air bon :



Cela se produit sous Excel 2016.

[Raccourcix]

sur ce code :

Code :

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

ActiveWorkbook.Connections("Requête - Résultat").Refresh

Cela se produit sous Excel 2016.

Aucun problème chez moi avec 365.
quel est le code généré via l'enregistreur de macro lorsque tu actualises la requête depuis la liste des requêtes ?
Stéphane

[jurassic pork]

Bon j'ai trouvé d'où venez le problème : en fait dans le nom de la connexion ce ne sont pas des espaces (20 en hexa) qui sont utilisés dans "Requête - " mais des caractères "Non breaking space" (c2 a0 en hexa) :



En utilisant ces caractères dans mon code , je n'ai plus de problème

ça explique aussi peut-être le pourquoi de : ce sont bien des espaces et non des étoiles, je ne sais pas pourquoi le code ne s'affiche pas comme je l'ai mis ​de Raccourcix.

[JPierreM]

Bonsoir,
Je pense que l’on s’éloigne de ma demande.
Je suis passé courant janvier 2022 de Excel 2007 à 2016, donc Power Query m’est totalement inconnu. Hé oui je ne me précipite pas à chaque sortie d'une nouveauté. J’ai bien essayé de voir sur Internet à quoi ça pouvait servir mais cela demande apparemment un apprentissage. J'y passerai plus tard quand j'aurai plus de disponibilité.

Je continue les recherches de mon côté sur quelque chose de plus basique.
Merci pout tout
JP

[78chris]

Bonjour à tous

Tu as eu une formule qui répondait à ta demande le 24 et une fonction personnalisée le 25

Tu n'as voulu ne de l'une, ni de l'autre, et tu es reparti sur une solution Excel4 (donc considéré comme du VBA par Excel) semi-manuelle

Que tu ne te précipites pas à chaque sortie d'une nouveauté, on peut comprendre mais 15 ans c'est énorme en informatique et passer de 2007 à 2016 qui a 6 ans et n'est déjà plus supporté par Microsof est un choix curieux d'autant qu'Excel est qui a plus évolué dans le pack Office et dans le bon sens.

Ton fil nous a permis d'échanger sur nombre de possibilités récentes : dommage que tu en prennes ombrage car c'est aussi le rôle des forums d'échange.