Discussion :

[lyeshbc]

Bonjour,

Après de nombreuses recherches sur le forum je n'ai pas réussi à trouver une solution à ma problématique et je me permets donc de poster ce message.

Mon but serait donc de créer à partir d'un tableau de valeurs initial un tableau où chaque cellule est la moyenne d'une sélection dont la taille serait fixée et modifiable.

Pour clarifier mon problème je vais donner un exemple illustratif:

Sur un tableau de 10 lignes et 10 colonnes je souhaiterai donc realiser un tableau de 10 lignes et 10 colonnes où la selection pour le moyennage aurait un rayon de 3 cellules ( dans les quatres directions quand c'est possible). L'idée serait donc d'avoir une gestion automatisé de cette selection en tenant compte des bords du tableau initial tout en excluant la valeur du centre dans le calcul. Pour le calcul de la moyenne j'aimerais aussi pouvoir exclure les valeurs inférieur à une valeur seuil qu'on pourrait fixer au préalable.

Je ne sais pas si ma demande est réalisable sous excel et je suis ouvert à toute forme d'aide la plus détaillée possible.

Si ce message manque de clarté ou si vous avez besoins de plus d'explications n'hésitez pas à me les demander.

Je vous remercie de l'aide que vous pourrez m'apporter.

[Jean-Philippe André]

Salut,

pour bien comprendre, si on a par exemple un carré de 5 par 5
1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
16 17 18 19 20
21 22 23 24 25

Si on prend le centre 13, et ton rayon de 2, est-on sensé raisonner en distance manhattan ?

[lyeshbc]

Salut,

pour bien comprendre, si on a par exemple un carré de 5 par 5
1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
16 17 18 19 20
21 22 23 24 25

Si on prend le centre 13, et ton rayon de 2, est-on sensé raisonner en distance manhattan ?

Merci pour ce retour.

Oui il s'agit bien de la distance manhattan si toutefois on parle du même concept.
Si on reprend ton tableau pour la valeur centrale de 13 on considère seulement l'envionnement "circulaire" où l'on prendrait en compte chaque cellule qui se situe à 2 cases de mon centre soit les valeurs 3 8 7 9 11 12 14 15 17 18 19 23.

Et pour anticiper si je choisis d'appliquer un seuil a 15 je ne prendrais en compte que les valeurs de 15 a 23 pour mon calcul de ma valeur moyenne.

Cordialement.

[Jean-Philippe André]

Ok,

en VBA c'est faisable, l'idée ici est de partir d'une fonction qui retourne la plage de cellules qui sont à une distance manhattan <=N de ta cellule d'origine .
Ensuite tu fais un for each du retour de cette fonction, et tu testes la valeur pour voir si elle est incluse ou non dans ton intervalle autorisé
En bout de ligne tu fais la moyenne des valeurs éligibles.

[Raccourcix]

Bonjour

Voici un exemple avec Power Query

le principe est de
1 : décroiser la table d'origine
2 : créer une table avec distance et les décalages associés
3 : fusionner les tables puis filtrer suivant le seuil, regrouper les lignes et les colonnes en faisant la moyenne
4 : pivoter les colonnes et charger l'ensemble dans un tableau

Voir le fichier joint
2021_06_23 moyenne distance Manhattan avec seuil.xlsx
Stéphane

[Pierre Fauconnier]

Salut.

Par formule (et il y en a peut-être une plus simple)... XL2016 => validation matricielle (SHIFT+CTRL+ENTER)

Code excel :

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

=MOYENNE(SI((((ABS(LIGNE(INDIRECT(Cellule))-LIGNE(Zone))=Distance)*(COLONNE(INDIRECT(Cellule))-COLONNE(Zone)=0))+((ABS(COLONNE(INDIRECT(Cellule))-COLONNE(Zone))=Distance)*(LIGNE(INDIRECT(Cellule))-LIGNE(Zone)=0))+(ABS(LIGNE(INDIRECT(Cellule))-LIGNE(Zone))<Distance)*(ABS(COLONNE(INDIRECT(Cellule))-COLONNE(Zone))<Distance))*(ADRESSE(LIGNE(Zone);COLONNE(Zone))<>CELLULE("adresse";INDIRECT(Cellule)));Zone;""))

J'ai nommé la plage de données Zone, la cellule centrale de la "zone des moyennes" Cellule et la cellule qui spécifie la distance Distance



On peut ainsi faire varier le centre de la zone des moyennes et la distance pour la prise en compte des cellules. Dans le fichier joint, j'ai mis une MFC pour visualiser les cellules qui sont prises en compte.

[Jean-Philippe André]

J'adore la solution, même si le "+ simple" est tout relatif Pierre

Bravo pour cette solution !

[Pierre Fauconnier]

Salut JP

Par plus simple, je pensais à une optimisation de la table de vérité, mais je ne suis pas certain qu'il y ait plus concis...

Cela dit, il y a une erreur dans ma formule. Mea culpa. Dans l'illustration qui suit, les cellules entourées sont à une distance de 4 alors qu'on demande une distance de 3. Je la corrigerai ce weekend...

[Raccourcix]

Bonjour

Pour le plaisir de retrouver un peu le MPFE d'il y a bien longtemps et la proposition de la formule la plus compliquée possible

Je vous propose une solution par formule (Excel 365, j'abuse du SEQUENCE remplaçable sûrement par LIGNE INDIRECT et TRANSPOSE dans les versions précédentes d'Excel)

mon objectif était de généraliser le calcul à toutes les cellules en même temps !

Chaque cellule de la zone est référencée par un n° :
1 à n la première ligne,
n+1 à 2n la 2e ligne...
jusqu'à n*m
avec n le nombre de colonnes et m le nombre de lignes


puis je calcule la distance de chaque cellule avec toutes les autres avec un tableau de n*m lignes et n*m colonne
avec des MOD ou des ENT j'obtiens le nombre de lignes et de colonnes d'écart que je teste par rapport à l'écart

et ainsi une matrice de n*m lignes/colonnes avec des 0 et de 1 (cellules < distance)

puis un PRODUITMAT pour calculer la somme de chaque cellule (en mettant ma plage de départ en une seule colonne avec un index)

la même chose pour calculer le nombre de cellules proches (ce coup ci le PRODUIT MAT est avec une seule colonne de 1 obtenu avec un SEQUENCE(m*n;;1;0) )

il ne me reste plus qu'à diviser les deux parties la formule et à remettre le tout en ligne/colonne de départ avec un INDEX et un SEQUENCE(m;n)


avec en B1 la hauteur de la plage (nb de lignes)
en B2 la largeur de la plage (nb de colonnes)
en B3 la distance
en A6 un tableau d'entiers aléatoires entre 1 et 20 avec la formule TABLEAU.ALEA(B1;B2;1;20;VRAI)

le résultat est obtenu en une seule formule :

Code formule :

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

1
2
3
4
5
6
7
8
9
10
=INDEX(PRODUITMAT(N((ABS(ENT((SEQUENCE(SEQUENCE(B1*B2);SEQUENCE(;B1*B2);SEQUENCE(;B1*B2))-1)/B2)-ENT((SEQUENCE(SEQUENCE(B1*B2);SEQUENCE(;B1*B2);SEQUENCE(B1*B2))-1)/B2))
+ABS(MOD(SEQUENCE(SEQUENCE(B1*B2);SEQUENCE(;B1*B2);SEQUENCE(;B1*B2))-1;B2)-MOD(SEQUENCE(SEQUENCE(B1*B2);SEQUENCE(;B1*B2);SEQUENCE(B1*B2))-1;B2)))<=B3)
*((ABS(ENT((SEQUENCE(SEQUENCE(B1*B2);SEQUENCE(;B1*B2);SEQUENCE(;B1*B2))-1)/B2)-ENT((SEQUENCE(SEQUENCE(B1*B2);SEQUENCE(;B1*B2);SEQUENCE(B1*B2))-1)/B2))
+ABS(MOD(SEQUENCE(SEQUENCE(B1*B2);SEQUENCE(;B1*B2);SEQUENCE(;B1*B2))-1;B2)-MOD(SEQUENCE(SEQUENCE(B1*B2);SEQUENCE(;B1*B2);SEQUENCE(B1*B2))-1;B2)))>0);
INDEX(A6#;ENT((SEQUENCE(B1*B2)-1)/B2)+1;MOD(SEQUENCE(B1*B2)-1;B2)+1))
/PRODUITMAT(N((ABS(ENT((SEQUENCE(SEQUENCE(B1*B2);SEQUENCE(;B1*B2);SEQUENCE(;B1*B2))-1)/B2)-ENT((SEQUENCE(SEQUENCE(B1*B2);SEQUENCE(;B1*B2);SEQUENCE(B1*B2))-1)/B2))
+ABS(MOD(SEQUENCE(SEQUENCE(B1*B2);SEQUENCE(;B1*B2);SEQUENCE(;B1*B2))-1;B2)-MOD(SEQUENCE(SEQUENCE(B1*B2);SEQUENCE(;B1*B2);SEQUENCE(B1*B2))-1;B2)))<=B3)
*((ABS(ENT((SEQUENCE(SEQUENCE(B1*B2);SEQUENCE(;B1*B2);SEQUENCE(;B1*B2))-1)/B2)-ENT((SEQUENCE(SEQUENCE(B1*B2);SEQUENCE(;B1*B2);SEQUENCE(B1*B2))-1)/B2))
+ABS(MOD(SEQUENCE(SEQUENCE(B1*B2);SEQUENCE(;B1*B2);SEQUENCE(;B1*B2))-1;B2)-MOD(SEQUENCE(SEQUENCE(B1*B2);SEQUENCE(;B1*B2);SEQUENCE(B1*B2))-1;B2)))>0);
SEQUENCE(B1*B2;;1;0));SEQUENCE(B1;B2))

simplifiable avec LET !

le fichier avec un onglet de calcul détaillé pour ceux qui souhaite comprendre le fonctionnement


Bon, c'est sûrement plus simple et rapide pour les grands tableaux avec VBA ou Power Query mais j'ai appris beaucoup de techniques sur SEQUENCE avec cet exemple.
Stéphane

[Pierre Fauconnier]

Le MPFE? J'avais pas capté... On s'y est forcément croisé, du coup!! Curieux je suis donc de savoir qui se cache derrière Stéphane Raccourcix...

Pour le plaisir, je corrigerai ma formule en y ajoutant aussi les imites inférieure et supérieure dont il a été question à un moment donné...