Nouveau défi ! Les 75% premiers !

Voici mon nouveau défi !

Je pense qu'il devrait en intéresser plus d'un et il y a un prix à gagner !

Soit donc la table suivante :

Produit
Quantité vendues
Produit 1
39
Produit 2
48
Produit 3
80
Produit 1
95
Produit 2
19
Produit 3
45
Produit 1
97
Produit 2
38
Produit 3
43
Produit 1
69
Produit 2
86
Produit 3
43
Produit 1
66
Produit 2
93
Produit 3
79
Produit 1
30
Produit 2
14
Produit 3
68
Produit 1
39
Produit 2
49
Produit 3
23
Produit 1
98

Critères
Produit : Produit 2

Objectif :
Quelle est la formule permettant de sommer les quantités
correspondant aux 75 % meilleures ventes pour le produit sélectionné.

On ne peut pas utiliser de colonnes supplémentaires, ni de fonctions
personnalisées et la formule doit tenir dans une seule cellule. Le tableau n'est pas et ne doit pas être trié.
Au cas où l'on trouve des décimales pour les nombres à sélectionner, on arrondira au plus proche.

Ex :
Pour le produit 1, nous avons donc les valeurs suivantes, triées par ordre croissant :

Produit 1
30
Produit 1
39
Produit 1
39
Produit 1
66
Produit 1
69
Produit 1
95
Produit 1
97
Produit 1
98

Nous avons 8 valeurs, donc 75 % de 8 vaut 6
La réponse est donc 464.
Notez que 39 est présent 2 fois, mais n'est sélectionné qu'une fois.

A vous de jouer ! Réponses dans les commentaires !

Le gagnant le plus rapide se verra offrir un de mes produits (disponible sur www.polykromy.com) au choix !

Gaetan Mourmant

TCD sur plusieurs feuilles

Bonjour,

Cet article fait suite à la lecture du site de Jean-Marie Lambert où il présente une facon de changer la source des données d'un tableau croisé dynamique (TCD) en utilisant la fonction INDIRECT dans un nom dynamique pour renseigner la feuille contenant les données.

Ceci bien sur à condition que les données aient la même structure.

Le but est de réaliser ceci :

Vous pouvez retrouver mon fichier exemple ici

Je ne vais pas réécrire l'article, qui est très bien détaillé et pédagogique, mais l'idée principale est celle-ci .

On ajoute une cellule (ici A2 de la feuille TCD) contenant le nom de la feuille de la base de données concernée. Cette cellule contient une validation (Données - Validation) afin d'éviter les fautes de frappe.

La fonction Indirect permet de renvoyer les références suivantes :
- Liste1!$A$1 pour INDIRECT(TCD!$A$2&"!$A$1")
- Liste1!$A:$A pour INDIRECT(TCD!$A$2&"!$A:$A")
- Liste1!$1:$1 pour INDIRECT(TCD!$A$2&"!$1:$1")

La définition d'un nom dynamique (Insertion - Nom - Définir) pour un TCD prend la forme générale suivante :
=DECALER(Liste!$A$1;;;NBVAL(Liste1!$A:$A);NBVAL(Liste1!$1:$1))
En considérant que:
- la feuille "Liste1" contienne la base de données,
- la première ligne ne contenant que les en-têtes et
- la première colonne ne devant pas avoir de cellules vides.

D'où la combinaison finale suivante pour le nom dynamique :
=DECALER(INDIRECT(TCD!$A$2&"!$A$1");;;
NBVAL(INDIRECT(TCD!$A$2&"!$A:$A"));NBVAL(INDIRECT(TCD!$A$2&"!$1:$1")))

On crée ensuite le TCD en se basant sur ce nom dynamique comme source des données.

Finalement, en ajout à l'article de Jean-Marie Lambert, on peut créer une procédure événementielle captant le changement de valeur de la cellule A2 et lancant automatiquement l'actualisation du tableau :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotCache.Refresh
End If

End Sub

Bon amusement

Gaetan Mourmant
www.polykromy.com