SOUS.TOTAL et FORMULES MATRICIELLES

[incubus20851]

Bonjour à toutes et à tous !

Je rencontre un problème pour faire cohabiter la fonction SOUS.TOTAL avec des formules matricielles. J'aimerais que le résultat change quand un filtre est activé sur une autre colonne d'où le besoin d'incorporer la fonction SOUS.TOTAL
J'ai regardé sur les forums mais je n'ai rien réussi à adapter...

1ère Formule :
Il s'agit d'une formule matricielle qui compte le nombre de valeurs "Texte" différentes en colonne G. Voici la formule :

=SOMME(SI(ESTTEXTE(G$10:G$3000);1/NB.SI(G$10:G$3000;G$10:G$3000)))

2ème Formule :
Il s'agit d'une formule matricielle qui renvoi la valeur "Texte" qui revient le plus de fois dans la colonne G. Voici la formule :

=INDEX(G$10:G$3000;EQUIV(MAX(NB.SI(G$10:G$3000;G$10:G$3000));NB.SI(G$10:G$3000;G$10:G$3000);0))

Si vous avez une idée ?

Merci beaucoup !

 

[incubus20851]

Re : SOUS.TOTAL et FORMULES MATRICIELLES

Pour la première formule j'ai trouvé ceci qui fonctionne très bien :

=SOMME(--(FREQUENCE(SI(SOUS.TOTAL(3;DECALER(Nafmat;LIGNE(INDIRECT("1:"&LIGNES(Nafmat)))-1;;1));
EQUIV(Nafmat;Nafmat;0));LIGNE(INDIRECT("1:"&LIGNES(Nafmat))))>0))

Mais pour la 2ème formule je sèche, je sais pas trop où intégrer les Sous.TOTAL DECALER etc...

Une idée ?

 

[CISCO]

Re : SOUS.TOTAL et FORMULES MATRICIELLES

Bonjour

Comme d'habitude, avec un petit fichier exemple...

@ plus

 

[incubus20851]

Re : SOUS.TOTAL et FORMULES MATRICIELLES

Voilà ! Voir le fichier exemple en pièce jointe. La formule a modifier se trouve en cellule D7

Merci d'avance !

 

[incubus20851]

Re : SOUS.TOTAL et FORMULES MATRICIELLES

Une petite idée ?

Merci, merci !

 

[david84]

Re : SOUS.TOTAL et FORMULES MATRICIELLES

Bonsoir, salut Cisco,
ta formule est inutilement compliquée :
- tes nombres sont au format texte : les mettre au format nombre
- formule matricielle :

=MAX(SI(SOUS.TOTAL(3;DECALER(G10;LIGNE(G10:G2261)-LIGNE(G10);));G10:G2261))

Mais à mon sens, plus simple de passer par INDIRECT(ADRESSE... pour "reconstituer" la plage de valeurs non filtrée :

=MAX(SI(SOUS.TOTAL(3;INDIRECT(ADRESSE(LIGNE(G10:G2261);COLONNE(G10:G2261))));G10:G2261))

A+

 

[incubus20851]

Re : SOUS.TOTAL et FORMULES MATRICIELLES

Bonjour,

Merci david84, ta formule fonctionne très bien sur les nombres par contre j'ai commis une erreur, dans ma colonne G j'aurai en fait des données au format texte genre "784A" ou "7459B" etc... donc il faudrait une formule qui fonctionne sur du texte.

J'aimerais pouvoir intégrer la fonction SOUS.TOTAL à la formule ci-dessous.

2ème Formule :
Il s'agit d'une formule matricielle qui renvoi la valeur "Texte" qui revient le plus de fois dans la colonne G. Voici la formule :

Code :

=INDEX(G$10:G$3000;EQUIV(MAX(NB.SI(G$10:G$3000;G$10:G$3000));NB.SI(G$10:G$3000;G$10:G$3000);0))

Merci !!

 

[david84]

Re : SOUS.TOTAL et FORMULES MATRICIELLES

Re
tes explications manquent de précision. Le plus simple est de présenter un fichier exemple fidèle à ta demande pour éviter de tourner en rond.
Donc :
- replace un ficher exemple fidèle à ta problématique,
- prends un exemple et dis-nous le résultat attendus en en expliquant le pourquoi du comment (tient-on compte de la lettre ou uniquement des nombres ? Si l'on tient compte de la lettre, quel est son rôle dans le dénombrement de la valeur max non filtrée,...).
A+

 

[incubus20851]

Re : SOUS.TOTAL et FORMULES MATRICIELLES

Ok, désolé pour le manque de précision...

Voir en PJ le nouveau fichier exemple avec le résultat attendu.
Il faut prendre l'ensemble du code chaussure comme un texte. Les chiffres et lettres doivent être pris comme une valeur texte dans son ensemble pas de traitement séparé entre chiffres et lettres.

Merci !

 

[david84]

Re : SOUS.TOTAL et FORMULES MATRICIELLES

Re
Ta plage de données ne comporte que 11 lignes de remplies, et plus de 2200 lignes vides.
Mes questions :
- ton fichier réel comporte-t-il des lignes vides ou toutes les lignes sont-elles renseignées ?
- sur quelle version d'Excel es-tu (est-ce 2002 ou une version plus récente) ?
NB : une formule matricielle sur 3000 lignes, je ne connais pas le temps de recalcul...
A+

 

[incubus20851]

Re : SOUS.TOTAL et FORMULES MATRICIELLES

Pour répondre à tes questions :
- ton fichier réel comporte-t-il des lignes vides ou toutes les lignes sont-elles renseignées ?
Mon fichier réel peux contenir jusqu'à 22 000 lignes, une macro cherche des données d'un onglet à côté et les recopies dans l'onglet que je vous ai donné suivant le choix d'une liste de validation. Donc le nombre de ligne est aléatoire mais j'ai volontairement limité la formule à 3 000 lignes justement à cause du temps de recalcul de la formule matricielle, je l'ai testé, sur 3000 lignes ca reste raisonnable.

- sur quelle version d'Excel es-tu (est-ce 2002 ou une version plus récente) ?
J'utilise Excel 2002 sur windows XP SP3.

Merci

 

[david84]

Re : SOUS.TOTAL et FORMULES MATRICIELLES

Re
comme tu n'as pas répondu à

ton fichier réel comporte-t-il des lignes vides ou toutes les lignes sont-elles renseignées ?

, j'ai prévu avec lignes vides au cas où et étendu la plage de G10 à G3000.
Ci-joint fichier.
L'argument "tableau_données" de la fonction FREQUENCE est nommée, sinon ta version d'Excel ne peut la lire.
A+

 

[incubus20851]

Re : SOUS.TOTAL et FORMULES MATRICIELLES

Merci beaucoup pour ta réponse,

Sur les 3000 lignes parfois y'aura des lignes vides et parfois elles seront toutes remplies tout dépendra du nombre de ligne trouvé suite à la selection dans la liste de validation.

J'ai remarqué un soucis dans ta solution, si je choisit de mettre un filtre en colonne B sur le client "ARMIND" par exemple le résultat reste toujours "4050Z" alors que le code le plus représenté est le "6020B" dans ce cas car c'est le seul.

Autre soucis : Si je selectionne que le code "6020B" dans l'en-tête de colonne G il renvoit la valeur "3010C" et inversement si je sélectionne "3010C" c'est "6020B" qui apparait.

J'ai collé ta formule dans mon fichier réel et il semble aussi que là suivant les filtres que je mets sur les en-têtes de colonne le résultat affiché n'est pas le bon... Ca n'affiche pas le code chaussures qui se trouve le plus de fois dans la liste.

Y'a t-il une solution ?

Merci

 

[david84]

Re : SOUS.TOTAL et FORMULES MATRICIELLES

Re
la formule en elle-même est correcte mais c'est la formule nommée qui s'était décalée d'une ligne. C'est pour cela que j'évite les formules nommées si que je le peux car cela peux jouer des tours et l'on ne s'en aperçoit pas si jamais on n'y pense pas.
Si tu étais sur une version plus récente, tu n'aurais pas eu besoin de nommer une partie de la formule.
Ci-joint fichier modifié.
Pense bien à vérifier que la formule nommée est enregistrée correctement sur ton fichier original (au besoin, teste sur ce fichier en cas de doute si les résultats ramenés sont les mêmes).
A+

 

[incubus20851]

Re : SOUS.TOTAL et FORMULES MATRICIELLES

Merci beaucoup David84 ça marche nickel ! Exactement ce que je voulais !

Merci encore pour le temps que tu as passé, du très beau travail !

A bientôt !