Comment fusionner deux tables dans Excel qui ont des colonnes identiques?

Dans Excel, j'ai une feuille de calcul qui tire les données d'une base de données SQL vers une table puis génère un rapport basé sur ces données. Malheureusement, les données de cette base de données SQL sont incomplètes et je souhaite inclure des lignes supplémentaires dans le jeu de résultats saisis manuellement dans la feuille de calcul.

Autant que je le souhaite, je ne peux pas simplement insérer manuellement ces lignes supplémentaires dans la table car elles seraient supprimées chaque fois que Excel extrayerait de nouvelles données de la base de données SQL. Donc à la place, je envisage de créer une table séparée avec les mêmes titres de colonne sur une nouvelle feuille et d'entrer les données là-bas, puis de créer une troisième table sur une autre feuille qui combine en quelque sorte les lignes de la table qui tire les données de SQL et la Tableau où je saisis les données manuellement. Comment puis-je accomplir cela? (Ou alternativement, y a-t-il une meilleure façon de faire cela, je suis en train de manquer?)


Exemple:

Table 1 (From Database): | Person | Week Of | Task | Hours | | Bob | 1/6/13 | Foo | 12 | | Mary | 1/6/13 | Foo | 7 | | Mary | 1/6/13 | Bar | 5 | | John | 1/6/13 | Foo | 5 | | John | 1/13/13 | Foo | 13 | 

 Table 2 (Entered Manually): | Person | Week Of | Task | Hours | | Bob | 1/6/13 | Baz | 3 | | Mary | 1/6/13 | Baz | 2 | | John | 1/13/13 | Baz | 5 | 

 Result: | Person | Week Of | Task | Hours | | Bob | 1/6/13 | Foo | 12 | | Mary | 1/6/13 | Foo | 7 | | Mary | 1/6/13 | Bar | 5 | | John | 1/6/13 | Foo | 5 | | John | 1/13/13 | Foo | 13 | | Bob | 1/6/13 | Baz | 3 | | Mary | 1/6/13 | Baz | 2 | | John | 1/13/13 | Baz | 5 | 

Voici une solution Excel simple sans VBA. Il fonctionne en utilisant une fonction INDEX pour décrocher les lignes et à travers les colonnes des données SQL jusqu'à ce que les valeurs soient épuisées et une condition d'erreur résulte. Une fonction IFERROR saisit l'erreur et utilise une deuxième fonction INDEX pour décrocher les lignes et sur les colonnes des données saisies manuellement, jusqu'à ce que ces valeurs soient épuisées et qu'une condition d'erreur résulte. Une seconde fonction IFERROR saisit l'erreur et renvoie un tiret ("-"). (Les données SQL doivent être actualisées via le Ruban pour que les formules produisent un résultat correct.)

Créez une zone dynamique appelée SQLDB pour les données SQL dans Sheet1 en utilisant la formule:

 =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,COUNTA(Sheet1!$1:$1)) 

Créez une deuxième dynamique appelée gamme EXCELRNG pour les données saisies manuellement dans Sheet2 en utilisant la formule:

 =OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A:$A)-1,COUNTA(Sheet2!$1:$1)) 

Ces deux gammes nommées supposent que les noms de variables sont entrés dans la rangée 1 de chacune des deux feuilles.

Entrez les noms de variables dans la ligne 1 de Sheet3 (débutant dans la cellule A1).

Entrez la formule suivante Dans la cellule A2 de Sheet3:

 =IFERROR(INDEX(SQLDB,ROWS(A$2:A2),COLUMN(A2)),IFERROR(INDEX(EXCELRNG,ROWS(A$2:A2)-ROWS(SQLDB),COLUMN(A2)),"-")) 

Copiez la formule sur les colonnes du nom de la variable, puis descendez les lignes jusqu'à ce que les résultats des formules soient tous des tirets ("-").

Il est possible, à l'étape suivante, de créer une table pivot dans une autre feuille pour l'analyse et l'organisation.

Encore une fois, la première étape consisterait à créer une gamme nommée dynamique, disons, RESULTRNG, en insérant la formule suivante dans la zone de saisie du Gestionnaire de noms pour la plage nommée:

 =OFFSET(Sheet3!$A$1,0,0,COUNTA(Sheet1!$A:$A)+COUNTA(Sheet2!$A:$A)-1,COUNTA(Sheet1!$1:$1)) 

Ensuite, créez une table pivot dans une nouvelle feuille, définissant RESULTRNG comme table que vous souhaitez analyser. Cela filtrera les tirets de la table de formule dans Sheet3.

Cela fonctionne parce que la formule RESULTRNG comptabilise le nombre total de lignes dans Sheet1 et Sheet2 (à l'exclusion de l'en-tête Sheet2) et le nombre total de colonnes dans Sheet1 et définit son étendue en fonction de ces comptes, à l'exclusion des tirets dans les lignes de fuite ( Ou des colonnes) dans la table de formule Sheet3.

J'ai trouvé un moyen de le faire. Cette solution est un peu délicate et nécessite que les deux tables aient leurs propres feuilles séparées (avec rien d'autre sur elles), mais à part cela, il fait presque exactement ce que je veux. (Il semble également y avoir beaucoup de potentiel ici pour faire des opérations plus complexes telles que les jointures.)

Accédez à l'onglet Données sur le ruban, cliquez sur «D'autres sources» et «Dans Microsoft Query». Ensuite, cliquez sur Fichiers Excel, sélectionnez le fichier dans lequel vous travaillez et cliquez sur OK. Ensuite, appuyez sur Annuler et lorsque vous êtes invité à continuer de modifier dans Microsoft Query, appuyez sur "Oui". À partir de là, vous pouvez cliquer sur le bouton SQL et écrire une requête SQL personnalisée sur n'importe quelle feuille dans la feuille de calcul. Dans mon cas:

 SELECT * FROM `'Sheet1$'` `'Sheet1$'` UNION ALL SELECT * FROM `'Sheet2$'` `'Sheet2$'` 

Remarque: Pour moi, cette méthode cesse de fonctionner après la fermeture du fichier et l'ouvrir à nouveau. Je le publie ici de toute façon, au cas où il s'agirait simplement d'un problème avec mon ordinateur ou si quelqu'un d'autre peut le faire fonctionner.

Si vous êtes intéressé par une solution VBA, j'ai pu travailler comme suit:

  • Définissez une plage nommée dynamique pour les données que vous tirez de SQL Server. Ouvrez le Gestionnaire de noms, entrez un nouveau nom (par exemple, "SQLDB") et copiez la formule suivante dans la zone de renvoi à la boîte de saisie. J'ai supposé que vos données tirées sont dans Sheet1:

     =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1)) 
  • Définissez une autre gamme nommée pour la plage dans laquelle les données manuelles sont entrées. J'ai utilisé le nom EXCELRNG et j'ai supposé qu'il était dans Sheet2. La gamme nommée commence dans la rangée 2 pour exclure une ligne d'en-tête. La formule ici est identique à la première, à l'exception de la fiche à laquelle elle se réfère:

     =OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A:$A)-1,COUNTA(Sheet2!$1:$1)) 
  • Voici le premier ensemble de paramètres que j'ai utilisé pour la connexion à la table SQL. La boîte de dialogue est accessible en sélectionnant Connexions dans l'onglet Données du ruban. La désactivation du rafraîchissement d'arrière-plan garantit que la macro VBA s'arrête jusqu'à ce qu'un rafraîchissement des données dans la feuille Excel soit terminé. L'actualisation de la connexion lorsque la feuille de calcul s'ouvre peut ne pas être nécessaire, mais je voulais m'assurer que toute authentification serait effectuée avant que la macro ne soit exécutée.

paramètres de connexion

  • Voici le deuxième ensemble de paramètres. Ils se trouvent dans la section Propriétés de l'onglet Données (alors qu'une cellule de la table SQL importée est sélectionnée). Bien que j'ai choisi l'option «Insérer des lignes entières pour les nouvelles données, supprimer des cellules non utilisées», je n'ai pas eu de problèmes avec l'option «Insérer des cellules …».

Propriétés de connexion

  • Enfin, il s'agit du code VBA. Pour l'insérer, choisissez Visual Basic sous l'onglet Développeur. Mettez en surbrillance le nom de la feuille de calcul dans la liste à gauche. Il sera référencé comme "Projet VBA (nom de la feuille). Ensuite, choisissez Insérer un module dans la barre de menus en haut de l'écran et collez le code dans le nouveau module. Notez que j'ai mis le tableau consolidé dans Sheet3. Comme cela a été écrit, La macro ne tri pas la nouvelle table, mais cela ne serait pas difficile à ajouter.

     Sub StackTables() Dim Rng1 As Range, Rng2 As Range Set Rng1 = ThisWorkbook.Names("SQLDB").RefersToRange Set Rng2 = ThisWorkbook.Names("EXCELRNG").RefersToRange ' refresh the SQL table ThisWorkbook.Connections(1).Refresh ' clear the consolidated table range Sheet3.Cells.ClearContents ' copy the SQL data into the consolidation range Rng1.Copy Sheet3.Range("A1").PasteSpecial xlPasteValues 'copy the manually entered data into the consolidate range Rng2.Copy Sheet3.Range("A1").Offset(Rng1.Rows.Count, 0).PasteSpecial xlPasteValues Application.CutCopyMode = False Sheets("Sheet3").Activate ActiveSheet.Range("A1").Select End Sub 

Voici une version de la solution "Pure Excel" de @ chuff conçue spécifiquement pour travailler avec des tableaux. (IE Les deux sources de données que vous souhaitez fusionner sont des tables.)

La principale différence entre cette méthode et le one chuff posté dans sa réponse est que vous n'avez pas besoin de définir des plages nommées pour les deux ensembles de données que vous fusionnez, puisqu'ils sont des tableaux et ont déjà leur propre gamme nommée. Alors, allez-y et nommez votre premier tableau Table1 , et votre deuxième table Table2 .

Maintenant, créez une nouvelle table dans le coin supérieur gauche d'une nouvelle feuille et donnez-lui les mêmes noms de colonnes que les deux autres. Entrez ensuite la formule suivante dans la cellule A2 de la feuille que vous venez de créer:

 =IFERROR(INDEX(Table1,ROWS(A$2:A2),COLUMN(A2)), IFERROR(INDEX(Table2,ROWS(A$2:A2)-ROWS(Table1),COLUMN(A2)), "-")) 

Ensuite, copiez cette formule sur toutes les colonnes du tableau, puis descendez les lignes jusqu'à ce que les résultats des formules soient tous des tirets ("-"). Remarque: Le tri de cette nouvelle table ne fera rien, puisque le contenu de chaque cellule est en fait identique (tous contiennent la même formule).

Si les colonnes dans la table fusionnée affichent 0 lorsqu'elles doivent afficher une cellule vide, vous pouvez appliquer la formule dans cette colonne avec la fonction de remplacement, comme ceci:

 =SUBSTITUTE(<old expression here>, 0, "") 

Si vous souhaitez créer un tableau pivot qui utilise les données de cette nouvelle table, vous devrez créer une gamme nommée. D'abord, nommez le tableau Table3 . Maintenant, passez à l'onglet formules et cliquez sur "Définir le nom". Donnez à la référence un nom, entrez l'équation suivante pour sa valeur ("Renvoie à"):

 =OFFSET(Table3[#All],0,0,ROWS(Table1)+ROWS(Table2)+1) 

Vous pouvez ensuite utiliser cette référence nommée comme la gamme de votre table pivot.

Si vous voulez juste un résultat sur une seule fois, il existe un site Web qui fusionnera deux tables pour vous: https://office-tools.online/table/merge/

Vous collez les tables dans la page Web et sélectionnez les paramètres pertinents. Voici une capture d'écran de l'exemple intégré qui indique comment l'utiliser:

Entrez la description de l'image ici