> Tous les forums > Forum Bureautique
 Extraire juste les nouvelles entrées dans BDD ExcelSujet résolu
Ajouter un message à la discussion
Pages : [1] 2 3 ... Fin
Page 1 sur 3 [Fin]
goofyto8
  Posté le 04/03/2015 @ 16:54 
Aller en bas de la page 
Petit astucien

bonjour,

je voudrais savoir comment procéder en présence de deux bases de données sous Excel . L'ancienne contenant X entrées (classées par ordre alphabetique) et la nouvelle contenant Y entrées ( les éléments de l'ancienne base + des nouvelles entrées ) également classées par ordre alphabétique.

Pour ressortir uniquement les nouvelles entrées (soit Y-X).

merci.

Publicité
ferrand
 Posté le 04/03/2015 à 19:27 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Bonsoir,

Il faut un modèle d'organisation de tes bases, en particulier sur quel champ doit se faire la détermination des enregistrements de chacune (clé), et sous quelle forme tu souhaites matérialiser la détection.

Les enregistrements que tu cherches sont ceux qui dans la base Y n'ont pas de correspondances dans X. Appliqué à un champ (colonne dans Excel) définissant une clé unique pour chaque enregistrement, la fonction NB.SI appliqué au champ de X pour chaque élément du champ de Y donnera 1 pour les enregistrements communs à X et Y et 0 pour les enregistrements figurant uniquement dans Y. Voilà qui te donne la solution si tu penses pouvoir la mettre en oeuvre immédiatement.

goofyto8
 Posté le 04/03/2015 à 22:11 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

bonsoir,

Pour plus de précisions

Je cherche à appliquer une fonction EXCEL à une colonne intitulée NOM

sachant que sur l'ancienne table dans la colonne NOM j'ai

DUPONT

DURAND

DUTOUR

et avec la nouvelle table

DELEPINE

DUPONT

DURAND

DUTOUR

DUVAL

Le but c'est de générer une troisième table avec dans la colonne NOM

DELEPINE

DUVAL

ferrand
 Posté le 05/03/2015 à 00:05 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Je t'ai fourni une réponse... dans des termes comparables à tes "précisions".

Sa matérialisation exige que tu fasses ta part de boulot et fournisse un classeur à cette fin. Le modèle doit être de structure identique à ton classeur de travail, pour que la réponse soit parfaitement adaptée.

goofyto8
 Posté le 05/03/2015 à 17:34 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien
ferrand a écrit :

Sa matérialisation exige que tu fasses ta part de boulot et fournisse un classeur à cette fin.

bonjour,

Voici mon problème j'ai un mailing à envoyer à environ 200 nouvelles personnes, mais je ne sais pas qui sont ces 200 personnes car on m'a fourni un fichier de BDD Excel de 1500 personnes où les anciens et les nouveaux sont mélangés.

J'ai donc un classeur avec en feuille1 1300 personnes (les anciens) et en feuille2 1500 personnes(anciens + nouveaux) .Je voudrai en feuille3 générer une table avec uniquement les 200 nouvelles personnes en comparant la table de la feuille1 et la table de la feuille2.

Sachant que chaque personne est affecté d' un numéro de carte d'adherent UNIQUE. C'est donc sur la colonne contenant le numero de carte qu'il faut que je travaille.

Mais quelles fonctions utiliser ?



Modifié par goofyto8 le 05/03/2015 17:36
Magnan
 Posté le 05/03/2015 à 18:05 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

Bonjour,

Si tu as une date (d'inscription, achat, appel, ...) tu peux trier dessus. Un copier coller suffit ensuite.

A+

ferrand
 Posté le 05/03/2015 à 19:40 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Bonsoir,

J'ai donc un classeur avec en feuille1 1300 personnes (les anciens) et en feuille2 1500 personnes(anciens + nouveaux) .Je voudrai en feuille3 générer une table avec uniquement les 200 nouvelles personnes en comparant la table de la feuille1 et la table de la feuille2.

On avance un peu ! On sait déjà qu'il y a une base par feuille, il reste plus qu'à savoir comment elles sont réparties... Les formules ne se mettent pas en l'air mais dans des feuilles, en utilisant des références dans lesdites feuilles. C'est d'ailleurs pareil pour les macros, il faut qu'elles s'appliquent à quelque chose... Avec un modèle reprenant la même structure de ton classeur de travail, avec un échantillon de données (30 à 50 pour tester significativement), fictives ou non mais de même type que tes données réelles, tu aurais déjà fait ton extraction.

Sachant que chaque personne est affecté d' un numéro de carte d'adherent UNIQUE. C'est donc sur la colonne contenant le numero de carte qu'il faut que je travaille.

Excellente initiative ! Parce qu'effectivement le Nom n'est jamais assimilable à une clé primaire. Cela aurait pu compliquer.

Mais quelles fonctions utiliser ?

Formule matricielle (bien sûr ), qui utilise des fonctions classiques comme NB.SI (pour la détection), PETITE.VALEUR (pour une récupération ordonnée, ou GRANDE.VALEUR), INDEX (pour l'extraction)... J'aurais deux variantes en fait, selon ce qui s'avèrera le plus commode en situation.

On peut aussi opérer avec une macro. De toutes façons c'est une manoeuvre temporaire.

A suivre...

ferrand
 Posté le 06/03/2015 à 19:57 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Il semble que tu n'entends pas donner suite à ta demande. Bonne continuation.

goofyto8
 Posté le 07/03/2015 à 12:50 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

Je n'ai toujours pas compris comment mettre en œuvre ta methode.

Publicité
ferrand
 Posté le 07/03/2015 à 12:58 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Il n'y a pourtant rien à comprendre :

Avec un modèle reprenant la même structure de ton classeur de travail, avec un échantillon de données (30 à 50 pour tester significativement), fictives ou non mais de même type que tes données réelles,

Une fois que tu l'as établi et communiqué par cjoint, je pourrai matérialiser la solution.

goofyto8
 Posté le 07/03/2015 à 16:55 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

bonjour,

Comment fonctionne le cjoint pour mettre un fichier à disposition des forumeurs ?

ferrand
 Posté le 07/03/2015 à 17:01 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

http://www.cjoint.com

Tu cliques sur Parcourir pour aller chercher ton fichier

Tu cliques ensuite sur Créer le lien Cjoint

Une fois le lien créé, tu le copies, puis tu le colles dans un post sur le forum.

goofyto8
 Posté le 07/03/2015 à 17:37 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

bonsoir,

Voici le lien vers mon fichier BDD Excel

http://cjoint.com/?0ChrQGjXUT5

A partir de la totalité des clients 2013 (feuille1) et de la totalité des clients 2014 (feuille2) , je voudrai avoir en feuille3 (nouveaux) uniquement les nouveaux clients entrés en 2014.

Pour eviter le problème des doublons sur les noms , les formules doivent porter sur la colonne qui contient le numero de client.

ferrand
 Posté le 07/03/2015 à 17:46 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Vu, Je te fais ça après manger.

Le numéro de carte est unique, ta base ne comporte que 4 champs.

A+

goofyto8
 Posté le 07/03/2015 à 17:49 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

merci.

oui le numero de client est unique et il y a 4 champs.

Si tu peux ensuite m'expliquer, un peu, le principe des formules matricielles.

ferrand
 Posté le 07/03/2015 à 20:23 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Re,

1re étape : Nommer les bases ancienne et nouvelle, et nommer les colonnes contenant le numéro de carte. Ce n'est pas absolument indispensable mais cela facilite l'écriture des formules...

J'ai nommé : BasAnc, BasNou, et NumBA, NumBN. Pas besoin d'indiquer à quoi chaque nom se réfère. Les noms prennent en compte les plages de données, sans la ligne d'en-tête.

J'ai mis des noms dynamiques (peuvent s'adapter aux variations de plages, utilisent la fonction DECALER pour définir la référence de la plage nommée). Ce n'était pas non plus nécessaire, dans la mesure où l'opération répond à un besoin temporaire, mais la méthode peut toujours être réutilisée.

J'ai nommé les bases de la façon habituelle pour affecter des noms dynamiques, et nommé les colonnes en décalant la définition des bases. L'inverse était également possible. Souci inconscient de symétrie dans cette opération, car BasAnc n'est pas utilisé dans les formules.

2e étape : Formulation. J'avais évoqué 2 variantes, la première consistait à extraire directement le numéro dans la feuille nouveaux clients, s'il avait été situé en première colonne. Les autres éléments étant ensuite extraits en utilisant ce numéro avec RECHERCHEV.

Le numéro étant situé en 3e colonne, on ne va donc pas l'extraire directement mais calculer l'index de ligne dans une autre colonne, l'ensemble des données étant alors extrait en utilisant la fonction INDEX.

3 formules sont utilisées :

1) Calcul du nombre d'enregistrement de la base nouvelle ne figurant pas dans l'ancienne :

=SOMMEPROD(--(NB.SI(NumBA;NumBN)=0))

La fonction NB.SI va calculer pour chaque élément de NumBN le nombre de fois où il figure dans NumBA. Ce ne peut être que 1 (éléments anciens) ou 0 (éléments nouveaux ne figurant pas parmi les anciens). Elle va donc renvoyer une suite de 0 et de 1. En la mettant sous condition (=0), les 0 seront transformés en VRAI et les 1 en FAUX. Le -- figurant devant cette expression mise entre parenthèses permet de transformer les VRAI en 1 et les FAUX en 0. SOMMEPROD peut alors en faire l'évaluation en additionnant les 1 (qui correspondent aux éléments nouveaux).

SOMMEPROD étant une fonction matricielle dans son utilisation normale, cela dispense d'une validation matricielle malgré l'utilisation d'une matrice (au lieu d'une valeur unique) en 2e argument de NB.SI.

J'ai placé cette formule en F1. Elle peut être placée où on veut. Ce calcul n'est pas obsolument indispensable, il permet de mettre les autres formules sous conditions pour éviter l'affichage de valeurs d'erreurs. Si on veut s'en dispenser, il suffit de stopper la recopie par tirage des formules à l'apparition de valeur d'erreur (et d'effacer la ligne en erreur (c'est qu'on aura dépassé le nombre de nouveaux éléments à extraire).

Si l'on est sûr de ses bases, on pouvait également obtenir le nombre d'éléments à extraire par la différence du nombre de lignes entre la nouvelle base et l'ancienne.

.../...

ferrand
 Posté le 07/03/2015 à 20:57 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

2) Calcul de l'index de ligne des éléments nouveaux à extraire :

J'ai mis cette formule en colonne F à partir de F2. La colonne est indifférente, mais on doit partir de la première ligne de données de la base extraite, donc la 2.

En F2 : {=SI(LIGNE(1:1)<=$F$1;PETITE.VALEUR(SI(NB.SI(NumBA;NumBN);9^9;LIGNE(NumBN)-1);LIGNE(1:1));"")}

Cette formule est matricielle et doit donc être validée par la combinaison de touches Ctrl+Maj+Entrée, ce qui a pour effet de la faire encadrer par des accolades.

Comme précédemment, la fonction NB.SI va nous renvoyer une matrice de 0 (élément nouveaux) et de 1. Mise en argument conditionnel d'une fonction SI, les 0 seront des FAUX et les 1 des VRAI (ce qui dispense de mettre "=1" ou ">0" pour obtenir ces VRAI ou FAUX). Si VRAI, on renvoie une valeur asseez grande pour être supérieure au nombre de ligne de la base (avec 9^9, on a de la marge !), si FAUX on renvoie le numéro de ligne de l'élément (on retranche 1, pour avoir l'index de ligne dans la base, les données commençant ligne 2).

On a donc une matrice dans laquelle les index de ligne qu'on recherche sont mélangés à des valeurs nettement supérieures à l'index le plus élevé. Il faut donc en quelque sorte le filtrer pour extraire les index à la suite. La fonction PETITE.VALEUR appliquée à cette matrice va nous permettre de mettre ses valeurs dans l'ordre à partir de la plus petite. L'argument 'rang' de la fonction est indiquée par : LIGNE(1:1), qui va renvoyer 1, mais qui deviendra LIGNE(2:2) lors de la recopie ligne suivante (renvoyant donc 2), et ainsi de suite. En tirant la formule pour la recopier, on va donc incrémenter l'argument de la fonction et extraire tous les index recherchés à la suite.

La mise sous condition de cette formule aboutit à ce qu'elle n'affiche plus rien lorsqu'on dépasse le nombre d'éléments à extraire que l'on a précédemment calculé.

3) Extraction des données :

Formule en A2 : =SI($F2<>"";INDEX(BasNou;$F2;COLONNE());"")

Utilisation classique de la fonction INDEX, ligne étant fourni par la formule précédente en F et la colonne étant la même...

On tire latéralement sur la ligne cette formule, puis on tire l'ensemble vers le bas, elle sera ainsi rapidement recopiée sur l'ensemble de la base à extraire.

Voilà pour la méthode formules : http://cjoint.com/?EChviXln3SY

Publicité
ferrand
 Posté le 07/03/2015 à 21:04 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

A noter que la présence de formule aboutit à rendre ton extraction dépendante des modifications affectant les bases nouvelle et ancienne auxquelles se réfèrent les formules (si tu fais des tris sur ces bases par exemple). Si tu souhaites éviter des modifications en touchant aux bases de référence, il faut la rendre autonome en la copiant et collant en valeurs sur elle-même, ce qui supprimera les formules.

La méthode macro évite cet aspect en produisant d'emblée des valeurs (pas de formules).

Mais pour l'instant, pause et je regarde un film.

goofyto8
 Posté le 07/03/2015 à 21:48 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

bonsoir,

merci, j'ai vu que ta methode fonctionne très bien.

Malheureusement je ne comprend rien et franchement ça m'ennuie car pour l'adapter à une BDD de plus de 1500 noms je ne sais pas si je vais y arriver.

Je ne vois pas du tout pourquoi tu fais intervenir 9 ^9 , par exemple (??)

N'y-a-t-il pas plus simple ? En écrivant un module en VBA, peut-être ?

ferrand
 Posté le 07/03/2015 à 23:53 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Je crois avoir fourni des explications particulièrement détaillées sur les formules pour permettre de comprendre la façon dont elles fonctionnent. A toi d'étudier la chose pour progresser. En outre, l'utilisation de noms de plages te permet de les utiliser par simple copie sans aucune adaptation (si tu affectes les mêmes noms aux plages considérées) ou en n'ayant que les noms à modifier (si tu en choisis d'autres), en les collant aux emplacements correspondants de ton fichier.

9^9 (sans espace !) est un nombre (9 puissance 9). Tu tapes =9^9 dans une cellule, tu auras le résultat. On a coutume de l'utiliser lorsqu'on a besoin d'un grand nombre, supérieur à tous les nombres susceptibles d'être utilisés dans les données qu'on traite. Une feuille Excel dépassant un peu 1 million de lignes, on est sûr de se situer au-dessus ! Cette valeur étant affectée aux éléments qu'on ne recherche pas, en listant les valeurs de la matrice dans l'ordre croissant, on aura tous les éléments recherchés avant les autres...

Une macro a l'avantage de fournir une nouvelle base indépendante de celles dont elle procède, et donc immédiatement utilisable, et sans les ajouts sur la feuille destinés à la construire avec des formules. Comme adaptation, elle réclamera les mêmes que la méthode formules : nommer les plages.

J'ai ajouté un nom de plage (par rapport à la version formules) : NvB qui représente la cellule A2 de la base Nouveaux éléments. Il fallait une cible pour affecter les résultats trouvés par la macro.

Ce que fait la macro :

- Elle trie les bases ancienne et nouvelle sur le numéro client.

- Elle dimensionne un tableau sur le nombre de colonnes des bases et la différence de lignes entre nouvelle et ancienne (pour accueillir les données à extraire).

- Elle parcourt les numéros de la nouvelle base et lorsque ce dernier ne figure pas dans l'ancienne, elle inscrit les données de la ligne dans le tableau.

- Elle transfère les données du tableau sur l'emplacement des nouveaux éléments.

- Elle retrie les bases et les nouveaux éléments sur le nom.

Mécanisme simpliste plus facile à appréhender que le fonctionnement des formules matricielles, mais c'est souvent que les macros apparaissent plus simples que l'équivalent formules pour obtenir le même résultat.

Sub ExtracNouvo()
Dim i%, j%, h%, k%, n%, p%, Nv()
[BasAnc].Sort key1:=[NumBa], order1:=xlAscending, Header:=xlNo
[BasNou].Sort key1:=[NumBN], order1:=xlAscending, Header:=xlNo
k = [BasNou].Columns.Count
n = [BasNou].Rows.Count
p = [BasAnc].Rows.Count
ReDim Nv(k - 1, n - p - 1)
With [BasNou]
For i = 1 To n
If [NumBN].Cells(i, 1).Value <> [NumBa].Cells(i - h, 1).Value Then
h = h + 1
For j = 1 To k
Nv(j - 1, h - 1) = [BasNou].Cells(i, j).Value
Next j
End If
Next i
End With
With [NvB]
For i = 1 To n - p
For j = 1 To k
.Cells(i, j).Value = Nv(j - 1, i - 1)
Next j
Next i
End With
[BasAnc].Sort key1:=[BasAnc].Cells(1, 1), order1:=xlAscending, Header:=xlNo
[BasNou].Sort key1:=[BasNou].Cells(1, 1), order1:=xlAscending, Header:=xlNo
Range([NvB].Cells(1, 1), [NvB].Cells(n - p, k)).Sort key1:=[NvB].Cells(1, 1), _
order1:=xlAscending, Header:=xlNo
End Sub

Plus qu'à tester : http://cjoint.com/?ECiacEZpJQO

goofyto8
 Posté le 08/03/2015 à 14:31 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

bonjour,

merci pour la version utilisant VBA.

Mais test pas possible car avec le lien de telechargement que tu me donnes dans ton dernier message, tu ne m'as pas envoyé les bases de données avec la macro..... mais juste mon fichier Excel d'origine.

En ce qui me concerne lire des lignes de programme en VBA (dérivé du Basic) est nettement plus compréhensible que les formules Excel !

Où je bloque, ce n'est pas tant sur ce que fait une fonction dans une formule , (il suffit de lire la définition); mais c'est lorsqu'il y a plusieurs fonctions imbriquées les unes dans les autres avec des multiples parenthèses que je n'arrive plus à suivre.

salutations cordiales



Modifié par goofyto8 le 08/03/2015 14:44
ferrand
 Posté le 08/03/2015 à 16:02 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Tu as mal regardé ! Le fichier n'est plus .xlsx mais .xlsm (parce qu'il contient une macro). A l'ouverture, Excel te demande si tu veux activer les macros. Si tu les actives, le fichier est prêt à tester : onglet Développeur > Macros > Tu exécutes la macro ExtracNouvo et tu observes le résultat...

goofyto8
 Posté le 08/03/2015 à 17:09 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

Au temps pour moi.

j'avais beau appuyer sur Alt F11 , je ne voyais aucune macro apparaitre ,mais c'est parce qu'Excel bloquait l'execution des macros.

goofyto8
 Posté le 08/03/2015 à 18:29 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

Bonjour,

Pourquoi commences-tu à trier chacune des deux bases selon le numero de client ?

ça ne me parait pas indispensable pour rechercher avec une boucle si un numero est present ou pas dans l'ancienne base .

D'autre part pour comprendre ta routine VBA pour ce tri je me suis documenté sur

https://msdn.microsoft.com/fr-fr/library/office/ff840646.aspx

Mais n'y a -til pas une formule plus simple ?

ferrand
 Posté le 08/03/2015 à 20:13 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Pourquoi commences-tu à trier chacune des deux bases selon le numero de client ?

La méthode utilisée exige que les deux bases soient identiquement ordonnées, ce qui ne peut être garanti qu'en les triant sur la clé primaire.

ça ne me parait pas indispensable pour rechercher avec une boucle si un numero est present ou pas dans l'ancienne base .

Certes ! Mais ce n'est pas ce que je fais.

D'autre part pour comprendre ta routine VBA pour ce tri je me suis documenté sur

https://msdn.microsoft.com/fr-fr/library/office/ff840646.aspx

Ce n'est pas ma routine, je me contente de l'utiliser comme méthode d'un objet Excel sans avoir accès à son implémentation, et elle appartient à la bibliothèque Excel, non à VBA.

Tu aurais trouvé la même chose dans l'aide, sans avoir à chercher si loin.

Mais n'y a -til pas une formule plus simple ?

Je ne comprends pas ce que tu veux dire par là. S'il s'agit du tri, il ne prend qu'une ligne de code... Si tu parles de l'ensemble de la procédure, on peut faire plus long (avec une boucle pour rechercher si chaque numéro de la nouvelle base est présent dans l'ancienne, par exemple ), et les autres variantes que je peux imaginer ne me paraissent ni plus rapides, ni plus simples à écrire. Mais je demeure ouvert à toute suggestion si tu as une idée...

NB- Le tri final n'a rien d'indispensable pour répondre au problème posé initialement. Il est d'ailleurs incomplet, réalisé sur le nom seulement (au lieu du nom et prénom habituels en la matière)...



Modifié par ferrand le 08/03/2015 20:15
goofyto8
 Posté le 08/03/2015 à 20:57 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

N'y a -t-il pas moyen avec VBA d'ecrire une procédure avec des boucles classiques, qui prend le premier n° de client de la première ligne dans la nouvelle base (clients2014) et de voir si ce numero de client est présent dans l'ancienne base (clients2013) en parcourant toutes les lignes de l'ancienne base.

- si il est présent, on passe à la ligne suivante (deuxième numero de client de la nouvelle base ) et on recommence

-s'il n'est pas présent , on transfère l'ensemble de la ligne dans la feuille3 (nouveauxx clients)

une fois qu'on a parcouru toutes les lignes de la feuille clients2014 on arrête .On aura bien nos nouveaux clients dans la feuille3



Modifié par goofyto8 le 08/03/2015 20:59
Publicité
Pages : [1] 2 3 ... Fin
Page 1 sur 3 [Fin]

Vous devez être connecté pour poster des messages. Cliquez ici pour vous identifier.

Vous n'avez pas de compte ? Créez-en un gratuitement !


Sujets relatifs
EXTRAIRE DES DONNEES DANS EXCEL
VBA EXCEL mettre procédures dans un seul module
2 pages par feuille dans macro excel
Changement dans Excel 2010 ?
case à cocher dans cellule Excel 2010
Excel 2007 Copier/Coller d’une feuille à l’autre Dans un même classeur.
Creation d' une boucle macro dans fichier EXCEL pour impression
Relier des choix à des cellules dans excel
Largeur Hauteur colonnes en mm dans Excel
listing et somme valeurs unique dans colonne excel
Plus de sujets relatifs à Extraire juste les nouvelles entrées dans BDD Excel
 > Tous les forums > Forum Bureautique