Fonction NB.SI.ENS de valeurs uniques
Bonjour à tous,
J'ai l'habitude de me débrouiller tout seul d'habitude, mais là je sèche depuis un moment.
Mon soucis c'est que j'ai une fonction NB.SI.ENS qui calcule le nombre de factures pour une direction régionale selon plusieurs critères. Toutefois, plusieurs factures peuvent être rattachées à un seul et même dossier. J'aimerais donc avoir non pas le nombre de factures, mais plutôt le nombre de dossier.
En PJ, le fichier Excel en question, avec la base de donnée extraite du CRM et l'onglet qui récapitule les statistiques par DR.
Pourriez-vous m'aider à trouver une formule qui permette de calculer le nombre de valeurs uniques en colonne D de la base de donnée (00), selon les différents critères sélectionnés pour chacune des directions régionales.
J'ai vu sur les différents forum les propositions de fonctions SOMMEPROD & FREQUENCE mais j'ai beaucoup de mal à les appliquer à mon cas de figure.
Merci par avance pour votre aide.
Bonjour,
A tester :
=SOMME(--(FREQUENCE(SI('00'!L:L=C9;'00'!D:D);'00'!D:D)>0))Formule matricielle (à valider avec Ctrl + Maj + Entrée)
Cdlt.
Bonjour,
D'après l'excellent site de Jacques Boisgontier formule à valider en matricielle par CTRL + SHIFT + ENTER en X9 par exemple :
=NB(1/FREQUENCE(SI(('00'!L7:L5000=C9)*('00'!D7:D5000<>"");EQUIV('00'!D7:D5000;'00'!D7:D5000;0));LIGNE(INDIRECT("1:"&LIGNES('00'!D7:D5000)))))Obtenable par POWER QUERY également.
Cdlt,
Re,
@Ergotamine,
J'ai modifié le IF en SI !
Pour Excel 365, la formule équivalente (non matricielle) :
=NB(UNIQUE(FILTRE('00'!D:D;'00'!L:L=C9)))Plus de SI !...
Merci à tous les deux pour vos réponses, cela fonctionne pour obtenir le nombre de dossiers unique qui répond à un critère (la direction régionale), mais j'ai deux autres critères (l'année et le YTD) qui doivent entrer en compte dans la formule.
Sauriez-vous comment je peux appliquer ces conditions à vos formules ?
Bonjour Djidji,
J'aimerais éviter d'avoir à passer par un TCD.
Et oui c'est en fonction de Do.
Jean-Eric,
La fonction =NB(UNIQUE(FILTRE('00'!D:D;'00'!L:L=C9))) ne fonctionne pas. Excel mentionne "cette fonction est incorrecte" et surligne la fonction FILTRE.
Bonjour,
Il va falloir penser à passer par des TCD/POWER QUERY voir POWERBI pour faire ce genre d'analyse si vous souhaitez continuer à pouvoir ouvrir ce fichier. Cependant la formule demandée :
=NB(1/FREQUENCE(SI(('00'!L:L=$C$9)*('00'!U:U=$A$5)*('00'!F:F=$A$4)*('00'!D:D<>"");EQUIV('00'!D:D;'00'!D:D;0));LIGNE(INDIRECT("1:"&LIGNES('00'!D:D)))))Cdlt,
Edit : Comme l'a dit Jean-Eric, FILTRE et UNIQUE ne sont disponible qu'à partir d'Office 365.
Re,
La mise à jour des formules.
Versions antérieures à Excel 365 ; formule matricielle (Ctrl + Maj +Entrée)
=SOMME(--(FREQUENCE(SI(('00'!L:L=C9)*('00'!F:F=2020)*('00'!U:U=1);'00'!D:D);'00'!D:D)>0))Excel 365 :
=NB(UNIQUE(FILTRE('00'!D:D;('00'!F:F=2020)*('00'!U:U=1)*('00'!L:L='Im. DR An'!C9))))Jean-Eric, votre formule matricielle a l'air de fonctionner très bien, merci d'avoir pris le temps de m'aider.
Pouvez-vous m'indiquer quel est l'inconvénient d'avoir des formules matricielles au sein d'un fichier s'il vous plaît ?
Ergotamine, votre formule indique "0", ça n'a pas l'air de fonctionner. Si tu sais d'où vient le problème sur ta formule et que tu te sens de la corriger, je serais curieux de voir une solution alternative à celle de Jean-Eric.
Autrement, pourquoi est-ce que tu dis que le fichier risque de ne plus s'ouvrir ? Il n'est pas lourd jusqu'ici, si ? Il n'est pas amené à être davantage enrichit en plus.
Merci sincèrement pour votre aide à tous les deux.
Bonjour,
La formule comme celle de Jean Eric est à valider via CTRL + SHIFT + ENTER.
Les formules matricielle sont puissantes mais consomment beaucoup de ressources car elles crééent des matrices en arrière-plans pour chaque condition. Ma formule, vous le constaterez mets plus d'une seconde à afficher un résultat. C'est pourquoi il faut en limiter l'usage autant que se peu. Par exemple limiter la plage d'application peut-être bénéfique. En effet vous indiquez pour toutes les formules, des colonnes entières, ainsi pour chaque formule, 1 million de données sont testées a chaque fois. Ce phénomène a pour effet de demander énormément de ressources, et le cas échéant, ralentir le fichier.
Quand je dis lourd ce n'est pas en terme de poids brut, mais en terme de puissance de calcul et ressources consommées, qui, s'il est trop important, peut empêcher l'utilisation d'un fichier.
Cdlt,
D'accord, merci pour les infos Ergotamine.
Et sans passer par une formule matricielle, n'y a t-il pas une formule "classique" capable de répondre à ma demande (via Sommeprod peut-être ?).
Bonjour,
Si je ne dit pas de bêtises, même si SOMMEPROD n'est pas a valider en matriciel, de part son fonctionnement, c'est une formule matricielle, tout comme SOMME.SI.ENS ou NB.SI.ENS qui testent toutes les conditions, gardent ces résultats conditionnels sous forme de VRAI/FAUX ou 0/1 en mémoire (donc plusieurs milliers de valeurs si plusieurs milliers de lignes testées) avant de réaliser l'opération finale (la somme, le comptage, etc ...).
C'est pourquoi pour des traitements de grands volumes d'informations, il est préférable d'utiliser les TCD ou POWER QUERY qui sont des outils adaptés à de l'analyse de données en grande quantité.
Cdlt,