NB SI ENS & SOMME SI ENS - pb de méthode

[bloublou]

Bonjour à tous,

Je revenais vers vous concernant un pb de conception de formule ou vba, je n'arrive pas à trouver la bonne formule adaptée à mon pb ou alors je n'ai pas d'assez bonnes bases en excel

J'ai un tableau où sont répartis des "choix" disponibles sur 9 colonnes différentes avec leurs prix correspondants.

Contraintes :
Les choix ne sont jamais à la même place dans les colonnes
les choix peuvent avoir des prix différents
Le numéro de facture est unique (1 ligne = 1 facture)
Le nombre de lignes correspondant à un numéro d'identification est variable
C'est un exemple très simplifié mon fichier fait plus de 50 000 lignes

Exemple : Pour remplir 2 tableaux (compter et additionner en €) :J'ai 4 lignes de factures, je voudrais qu'Excel me compte qu'1 choix premium selectionné mais qu'il additionne les 4 lignes pour un total de 201.08€

Numéro de facture Groupe Numéro identification Choix 1 Prix Choix 1
555-3567233 A ABCD56-345 premium 50,27
555-3567234 A ABCD56-345 premium 50,27
555-3567235 A ABCD56-345 premium 50,27
555-3567235 A ABCD56-345 premium 50,27

Si j'utilise nbsi ensemble il va me compter 4 lignes...
Comme le choix premium peut se trouver sur différentes colonnes je ne sais pas quelle formule utilisée...

J'ai mis un exemple concret pour illustrer :
1ere feuille = base
2ieme feuille = resultat attendu (je ne pas fait tous les calculs)
3ieme feuille = essai avec nb si ens et somme si ens

Si un ou plusieurs kadors d'Excel pouvaient m'aider je suis preneur

Merci

BlouBlou

 

[gosselien]

Re : NB SI ENS & SOMME SI ENS - pb de méthode

Hello,

personnellement je pencherais pour un tableau croisé, ça semble une solution + facile et permet de trouver des erreurs d'encodage dans la feuille de base...

 

[bloublou]

Re : NB SI ENS & SOMME SI ENS - pb de méthode

Bonsoir le forum, bonsoir Gosselien,

Merci pour ta réponse mais j'avais déjà essayé le tcd mais je n'arrive pas au résultat voulu...
Le pb vient du fait que j'ai les données qui ne sont jamais au même endroit et répartit sur 9 colonnes et que je peux avoir plusieurs lignes pour un même numéro d'indentification

Là dans ton exemple tu ne prends pas les colonnes P & Q pour le groupe A et le choix premium
On devrait arriver à plus de 3,6 millions €

Merci quand même de m'avoir lu

BlouBlou

 

[R@chid]

Re : NB SI ENS & SOMME SI ENS - pb de méthode

Bonsoir,
Voir PJ si j'ai bien compris..
@ + +

 

[bloublou]

Construction d'une Formule : nb si somme si ens ou vba ?

Bonjour à tous, Bonjour R@chid, Gosselien,

Merci pour m'avoir lu et de ta réponse, mais ce n'est pas exactement le résultat voulu ;o)
Par contre j'ai bien aimé le fait que l'on pouvait tirer la formule de tous les côtés, je n'ai pas tout compris (si tu veux me donner des pistes sur ta méthode) mais je pense que ca peut résoudre une partie du pb à la fin

Je sais que c'est pas évident à comprendre

Dans la feuille nb si ens, j'ai essayé de passer par cette formule mais je ne suis pas sur que c'est cette solution qui est la bonne

Je vais essayer de clarifier ma demande :

1) Je voudrais dans un premier temps compter le nombre lignes en supprimer les doublons à partir du num d'identification pour tous les groupes et tous les choix disponibles avec 2 contraintes :
Les choix sont répartis sur plusieurs colonnes et ne sont jamais à la même place.

Je vais prendre un exemple pour le groupe A, je devrais arriver au résultat de 3, ca serait comme faire un sous total avec comme critère le groupe A, le num d'identification et le choix.

2) Dans un deuxieme temps je voudrais à partir de ces contraintes arriver faire la somme des montants mais là la différence est notable, il faudrait ajouter toutes les lignes :
Je reprends mon exemple pour le groupe A je devrais arriver à 3 625 201,08€ (cellules en rouge et blanc)

J'ai remis une nouvelle PJ

Merci d'avance pour toute aide

BlouBlou

 

[Jocelyn]

Re : NB SI ENS & SOMME SI ENS - pb de méthode

Bonjour le Forum,
Bonjour bloublou, gosselien, R@chid ,

un essai en fichier joint le premier tableau contient une formule Matricielle, il faut valider la formule en appuyant simultanément sur CTRL, Maj (au dessus de CTRL) et entrée, si ta formule est bien validée excel va mettre de lui même le signe { devant le égal et le signe } en fin de formule.

ensuite dans les 2 tableaux un format de cellule personnalisé [>0]Standard;[Blanc]Standard pour masquer les 0

Regardes et dis nous

 

[bloublou]

Re : NB SI ENS & SOMME SI ENS - pb de méthode

Bonjour le forum,
Bonjour Jocelyn, R@chid, Gosselien,

Effectivement tu arrives bien au résultat !!! Whaaaaaaaaaaaaouhhhhhh

Par contre je ne comprends pas du tout la formule avec fréquence indirect ligne etc... peux tu m'expliquer ta formule et la construction de ta méthode ?
Idem la sommeprod avec Decaler...

Il faut que j'essaye avec mon véritable cas que je puisse la transposer ...

Par contre est-ce qu'avec la sommeprod, est-ce que le calcul va pas être trop long ?
Dans mon exemple concret j'ai plus de 50 000 lignes

Merci en tout cas !!!

BlouBlou

 

[Jocelyn]

Re : NB SI ENS & SOMME SI ENS - pb de méthode

re,

Hum hum, que se soit avec le sommeprod ou la formule matricielle sur 50 000 ligne cela risque d'être très longalors la solution serait peut être de passer par du VBA mais malheureusement je ne pourrais pas t'aider je suis une bille à ce jeu la

la formule fréquence permet de compter dans une plage le nombre de fois qu'apparait une donnée en divisant par le nombre de fois qu'apparait une donnée distincte suivant les critère précisés après le si du fréquence pour chaque données différente le résultat sera 1 par exemple pour

groupe A identifiant ABCD56-345 on a 4 apparition donc 1/4 soit 0.25 se qui donne au final 1

après dans le sommeprod() on décale la plage nommée zone de une colonne pour dire a ecxel tu regarde dans toutes les colonnes de la plage zone et si tu trouve par exemple "prémiun" tu te décales d'une colonne pour faire l'addition

voila j'espère que c'est clair

 

[Dugenou]

Re : NB SI ENS & SOMME SI ENS - pb de méthode

Bonjour,
Un essai en pj : fonctionne bien pour la somme des prix, résultats moins conforme pour le nb de lignes mais les données sont ainsi : par ex pou A ABCD56-345 rose : une seule ligne sur 4
pour simplifier la formule sur le nb de lignes j'ai créé une colonne supplémentaire. On devrait pouvoir faire sans si la solution te convient

Cordialement

Edit : bon, et bien j'arrive comme la cavalerie on dirait ! Je ne vois pas ta pj Jocelyn !!

 

[Jocelyn]

Re : NB SI ENS & SOMME SI ENS - pb de méthode

RE,
Bonjour l'ami Dugenou,

Pourtant je la vois moi la pièce jointe

tu t'es compliqué la vie avec le sommeprod

 

[Dugenou]

Re : NB SI ENS & SOMME SI ENS - pb de méthode

Jocelyn,
ça y est je la vois (certainement un pb avec mon IE) !
en effet il y avait plus simple ! j'ai encore du mal avec les matrices 2, 3 dans le sommeprod
quand au comptage je me doutais bien que frequence interviendrait, mais je ne sais pas m'en servir !
Bravo !

 

[bloublou]

Construction d'une macro - nb si ens - somme si

Bonjour le forum, dugenou, re jocelyn

Merci pour ta solution ca marche nickel egalement
Si je veux rajouter un critere d'année par exemple je peux le rajouter dans la fonction SI ?

Par contre j'ai essayé de la transposer, mais vu le nombre de lignes élevé, Excel a rendu l'âme !

Au vu de vos réponses il y aurait qu'une solution en VBA pour traiter mon pb ?

Parce que si on rajoute des données vers la 100 000 ieme ligne juste pour faire le test ca plante aussi chez vous ?

Merci

BlouBlou