SOLVED

Tri dynamique dans tableau

%3CLINGO-SUB%20id%3D%22lingo-sub-2368079%22%20slang%3D%22fr-FR%22%3EDynamic%20sorting%20in%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2368079%22%20slang%3D%22fr-FR%22%3E%3CP%3EHello%20everyone%2C%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20I%20try%20to%20make%20the%20sorting%20of%20my%20table%20automatic%20as%20soon%20as%20a%20data%20is%20changed.%20With%20the%20help%20of%20Excel%20support%2C%20I%20tried%20to%20do%20this%20via%20dynamic%20sorting%20and%20TRIERPAR%20but%20when%20I%20enter%20the%20formula%2C%20I%20feel%20like%20I%20don't%20make%20a%20mistake%20and%20yet%20it%20only%20puts%20me%20%23VALEURS%20everywhere.%3CBR%20%2F%3E%3CBR%20%2F%3ECould%20someone%20please%20explain%20my%20mistake%20to%20me%3F%3CBR%20%2F%3E%3CBR%20%2F%3EI%20would%20like%20to%20sort%20first%20by%20the%20PTS%20column%20and%20then%20by%20the%20column%20and%20then%20by%20the%20BP%20column%2C%20each%20column%20sorted%20in%20descending%20order%20please.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2368079%22%20slang%3D%22fr-FR%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2368979%22%20slang%3D%22en-US%22%3ERe%3A%20Tri%20dynamique%20dans%20tableau%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2368979%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1056784%22%20target%3D%22_blank%22%3E%40othonagra%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20since%20you%20use%20arrays%20of%20different%20size%20-%20entire%20table%20with%20headers%20sorted%20by%20columns%20without%20headers.%20Actually%20you%20need%203%20spills%20here%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20542px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F281877iCCF1C4F81B55E1A1%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eformulas%20are%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3Efor%20headers%0A%3DTableau3%5B%23Headers%5D%0A%0Afor%20class%0A%3DTableau3%5BClass%5D%0A%0Afor%20data%0A%3DSORTBY(Tableau3%5B%5BEquipes%5D%3A%5B%2B%2F-%5D%5D%2CTableau3%5BPTS%5D%2C-1%2CTableau3%5B%2B%2F-%5D%2C-1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EIf%20you%20open%20attached%20workbook%20formulas%20will%20be%20shown%20in%20your%20locale.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2377548%22%20slang%3D%22fr-FR%22%3ERe%3A%20Dynamic%20sorting%20in%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2377548%22%20slang%3D%22fr-FR%22%3EIt's%20perfect%2C%20it's%20exactly%20what%20I%20needed%20thank%20you%20very%20much%20to%20you!%3C%2FLINGO-BODY%3E
Occasional Contributor

Bonjour à tous, 

Je cherche à rendre le tri de mon tableau automatique dès qu'une donnée est modifiée. Avec l'aide de l'assistance Excel, j'ai tenté de faire cela via le tri dynamique et le TRIERPAR mais quand je rentre la formule, j'ai l'impression de ne pas faire d'erreur et pourtant ça ne me met que des #VALEURS partout.

Quelqu'un pourrait-il m'expliquer mon erreur s'il vous plait ?

Je voudrais trier en premier par la colonne PTS puis par la colonne +/- puis par la colonne BP, chaque colonne triée dans l'ordre décroissant svp.

2 Replies
best response confirmed by othonagra (Occasional Contributor)
Solution

@othonagra 

That's since you use arrays of different size - entire table with headers sorted by columns without headers. Actually you need 3 spills here

image.png

formulas are

for headers
=Tableau3[#Headers]

for class
=Tableau3[Class]

for data
=SORTBY(Tableau3[[Equipes]:[+/-]],Tableau3[PTS],-1,Tableau3[+/-],-1)

If you open attached workbook formulas will be shown in your locale.

C'est parfait, c'est exactement ce qu'il me fallait merci beaucoup à vous !