Controle qualité - échantillon de 15%par utilisateur d'apres base de données excel

pierb · Février 22, 2018, 5:52

Bonjour a tous,

N’étant pas un expert avec Excel je viens vous demander de l’aide pour une formule.

Tout les jours je recois une base de donnee avec: 1- nom des utilisateurs, 2- un numero de cas qui a ete traite (ainsi que d’autres informations). Apres reception de cette base de donnee nous devons prelever un echantillon aleatoire de 15% des cas traitees par utilisateurs pour le transmettre au service qualite. Le nombre de cas traites d’un utilisateur a l’autre differe selon la productivite journaliere de celui-ci.

agents	numero du cas
david	1
alan	2
david	3
anna	4
anna	5
paul	6
david	7
alan	8
david	9
anna	10
anna	11
paul	12
fred	13
fred	14
fred	15
david	16
alan	17
david	18
anna	19
anna	20

Ainsi je voudrais avoir un moyen de creer une deuxieme feuille recréant cet échantillon grace a l’utilisation d’une formule pour eviter le travail manuel Auriez-vous des pistes?

Merci pour votre temps.

cdlt
pierb

DocteurExcel · Février 23, 2018, 6:33

Bonjour

controle qualite.xlsx (10,3 Ko)

En plusieurs étapes:

Tirage aléatoire pour chaque cas avec la fonction ALEA
Filtrer uniquement les top 15% avec la formule
=SI(C21>CENTILE($C$2:$C$21;0,85);C21;"")
Si le tirage n’est pas dans les 15%, on affiche du vide dans cette colonne, sinon la valeur du tirage.
Eventuellement on pourrait calculer ailleurs le nombre de valeurs à tirer pour ne pas alourdir avec la fonction CENTILE dans chaque ligne
Maintenant on retraite la liste des tirages pour n’obtenir que la plus grande valeur, puis la 2e plus grande, puis la 3e plus grande etc on utilise pour ça GRANDE.VALEUR.
Pour avoir la série 1,2, 3 dans chaque ligne on peut utiliser la fonction LIGNES, ou insérer les numéros dans chaque ligne, en dur
On insère çaa dans INDEX + EQUIV pour renvoyer non pas le numéro aléatoire, mais l’agent et le cas correspondant:

=SIERREUR(INDEX(A:A;EQUIV(GRANDE.VALEUR(D:D;LIGNES($D$1:D1));D:D;0));"")
=SIERREUR(INDEX(B:B;EQUIV(GRANDE.VALEUR(D:D;LIGNES($D$1:D3));D:D;0));"")

pierb · Février 24, 2018, 9:20

Bonjour docteur excel,

Merci infiniment pour ta réponse! Cela m’a fait apprendre la fonction centile. Maintenant je bloque un peu avec la compréhenson de la deuxieme formule car je ne travaille pas souvent avec des fonctions imbriquees. Je comprendsl’utilisation de l’INDEX MATCH pour l’avoir utilise régulièrement ainsi que GRANDE.VALEUR mais je n’arrive pas a:

1- Comprendre l’avantage de rajouter la fonction LIGNES dans la formule.Pourais tu me l’éxpliquer?
2- La formule **=SIERREUR(INDEX(A:A;EQUIV(GRANDE.VALEUR(D:D;LIGNES($D$1:D1));D:D;0));"") fonctionne parfaitement.
En revanche pour la deuxieme partie me donnant les numeros de cas (=SIERREUR(INDEX(B:B;EQUIV(GRANDE.VALEUR(D:D;LIGNES($D$1:D3));D:D;0));"") je parviens a avoir un résultat correct uniquement dans la première ligne. Quel paramètre dois-je changer?

ps: l’excel que tu m’as fournis en piece-jointe n’affichais aucun resultat en F2:F5/G2/G5. Seulement les formules et des cases vides.Ainsi j’ai vu:

=SIERREUR(INDEX(B:B;EQUIV(GRANDE.VALEUR(D:D;LIGNES($D$1:D1));D:D;0));"")
=SIERREUR(INDEX(B:B;EQUIV(GRANDE.VALEUR(D:D;LIGNES($D$1:D2));D:D;0));"")
=SIERREUR(INDEX(B:B;EQUIV(GRANDE.VALEUR(D:D;LIGNES($D$1:D3));D:D;0));"")

Bonne journee!

PIerb

mdo100 · Février 24, 2018, 11:14

Bonjour @pierb, @DocteurExcel,

Explications et modifications de la réponse du @DocteurExcel.

Dans la formule:

=SIERREUR(INDEX(A:A;EQUIV(GRANDE.VALEUR(D:D;LIGNES($D$1:D1));D:D;0));"")

GRANDE.VALEUR(D:D;LIGNES($D$1:D1))
Cette partie de la formule: Recherche la première grande valeur de la colonne « D » a partir de LIGNES($D$1:D1) donc =1 « première grande valeur ».

En tirant la formule vers le bas, ça devient donc:
GRANDE.VALEUR(D:D;LIGNES($D$1:D2))
Cette partie de la formule: Recherche la deuxième grande valeur de la colonne « D » a partir de LIGNES($D$1:D2) donc =2 « deuxième grande valeur ».

Et ainsi de suite, l’ensemble de la formule permet donc de ramener l’INDEX des colonnes « A & B » les uns sous les autres sans cellule vides dans les colonnes « F & G » sinon les valeurs recherchées seraient en face de la colonne « D ».

Est-ce plus clair pour toi ?

Maintenant, je te propose d’améliorer le fichier afin que tu puisses l’allonger sans problème en conservant les formules uniques pour les colonnes « F & G ».

En nommant les colonnes « A, B, D » dans le « Gestionnaire de noms ».

Avec ses formules:

ColA	=DECALER(Feuil1!$A$2;;;NBVAL(Feuil1!$A:$A)-1)
ColB	=DECALER(ColA;;1;)
ColD	=DECALER(ColA;;3;)

Puis dans les colonnes « F & G »

En « F2 »

=SIERREUR(INDEX(ColA;EQUIV(GRANDE.VALEUR(ColD;LIGNES($D$2:D2));ColD;0));"")

En « G2 »

=SIERREUR(INDEX(ColB;EQUIV(GRANDE.VALEUR(ColD;LIGNES($D$2:E2));ColD;0));"")

Tirer ses 2 formules vers le bas autant que nécessaire.

Et bien à moi aussi, cela m’a permis de voir son utilisation

Ton fichier en retour ICI==> controle qualite V1.xlsx (13,0 Ko)

Cordialement.

pierb · Février 26, 2018, 6:06

@mdo100 @autres,

Woaw super ta réponse, merci beaucoup! Je dois apprendre a utiliser DECALER plus regulierement ca simplifie la vie sur excel.

1- Y’a t’il un moyen d’améliorer encore un peu la formule en ajoutant une autre condition (en colonne A, la date de creation du cas) pour avoir uniquement les donnes pour la derniere entree (hier)?

2 - Dans la colonne D (Liste des cas tirés), avoir 15% des cas de chaque agent au lieu de 15% du nombre total de cas?

Cordialement,

pierb

mdo100 · Février 26, 2018, 6:56

Bonsoir @pierb,

Tu nous poses 2 nouvelles questions, mais depuis le départ, @DocteurExcel et moi nous t’avons fourni un fichier avec des réponses claires et détaillées.

Je te demanderais donc de faire l’effort de nous fournir un fichier à ton tour accompagné de la nouvelle demande avec les données dans chaque colonnes en nous indiquant ou tu souhaites avoir les résultats.

Pour joindre un fichier, suivre cette procédure:

Joindre un fichier

Cordialement.

pierb · Février 26, 2018, 8:01

@mdo100,

Excuses moi le but n’était absolument pas de ne pas fournir l’excel ou de ne pas faire d’efforts, comme tu peux le voir des le premier message je n’en avais pas mis car je n’avais pas vu dans la barre d’outils le bouton upload (en général sur les forums il est en dehors du cadre de texte)

Je joins le fichier.

Mon unique question (Q1) est si tu sais comment rajouter une condition correspondant à la date en colonne A pour ne prendre en compte que les données d’une journée?

Merci,

pierb

controle qualite V2.xlsx (12,3 Ko)

mdo100 · Février 27, 2018, 12:05

Bonjour @pierb,

Sur le fichier que tu as joins, tes dates sont aux formats 25.02.2018, ça pose un problème, si tu n’as pas le choix de colonne, il faut les convertir au format date 25/02/2018 etc…

Pour ce faire, tu peux insérer une colonne “B” puis:

Sélectionner la colonne “A”.
Aller sur l’onglet “Données”.
Cliquer sur “Convertir”.
Dans la boîte de dialogue “Assistant Conversion - Étape 1 sur 3” sélectionner “Largeur fixe”.
Puis cliquer 2 fois sur “Suivant”.

Là tu arrive sur la boîte de dialogue “Assistant Conversion - Étape 3 sur 3”.

Sélectionner “Date”.
Destination: $B$1
Cliquer sur “Terminer”.

Et tu obtiens les dates formatées dans la colonne “B”.

Capture2

Tu peux bien entendu ne pas ajouter la colonne “B” et convertir directement sur la colonne “A”, c’est le même principe sauf l’étape.

Destination: $A$1

Je te mets les 2 fichiers:

Concernant ce fichier ICI==>: controle qualite V2 Bis.xlsx (14,1 Ko)

J’ai renommé les colonnes dans le “Gestionnaire de noms”.

agents	=DECALER(Feuil1!$C$2;;;NBVAL(Feuil1!$C:$C)-1)
cas	=DECALER(agents;;1;)
Liste_cas	=DECALER(agents;;3;)
Tirage	=DECALER(agents;;2;)

Puis j’ai modifié la formule dans la colonne “E” pour ne prendre en compte que le jour d’hier.

En “E2”

=SI(B2=AUJOURDHUI()-1;ALEA();"")

Puis:

En “F2”

=SI(E2>CENTILE(Tirage;0,85);E2;"")

En “H2”

=SIERREUR(INDEX(agents;EQUIV(GRANDE.VALEUR(Liste_cas;LIGNES($F$2:F2));Liste_cas;0));"")

En “I2”

=SIERREUR(INDEX(cas;EQUIV(GRANDE.VALEUR(Liste_cas;LIGNES($F$2:G2));Liste_cas;0));"")

Dans le 2ème fichier sans colonne ajoutée le principe est le même.

2ème fichier ICI==> controle qualite V2 Bis V1.xlsx (13,8 Ko)

Cordialement.

pierb · Février 27, 2018, 5:08

Un très très grand merci pour ta réponse mdo100, super claire et permet d’éclaircir les mystères d’Excel a un débutant comme moi. La route est longue ,mais intéressante, afin de maitriser ces formules.Mais y être confronté sur un cas pratique est la meilleure route pour l’apprentissage.
J’ai découvert l’utilisation de Aujourd’hui - 1 pour chercher les données d’hier ainsi que l’utilisation du gestionnaire de noms + DECALE pour les listes dans une formule.

Chapeau bas pour ta connaissance et ton engagement dans la communauté!

MERCI!

pierb