Hoe kan ik twee werkbladen in Excel samenvoegen zoals ik dat zou doen in SQL?

Voor 2007+ gebruik je Data> From Other Sources> From Microsoft Query:

1. kies Excel File en selecteer je 1e uitmuntende
2. kies kolommen (als je geen lijst met kolommen ziet, controleer dan Options> System Tables)
3. ga naar Data > Connections > [kies de zojuist gemaakte verbinding] > Properties > Definition > Command text > Command text

Je kunt deze &007 nu bewerken als SQL. Ik weet niet zeker welke syntaxis wordt ondersteund, maar ik probeerde impliciete verbindingen, “inner join”, “left join” en verbindingen die allemaal werken. Hier is een voorbeeldvraag:

SELECT *
FROM `C:\Users\Peter\Documents\Excel-to-excel\Source_1.xlsx`.`Sheet1$` a
LEFT JOIN `C:\Users\Peter\Documents\Excel-to-excel\Source_2.xlsx`.`Sheet1$` b
ON a.col2 = b.col2

Steun het geaccepteerde antwoord. Ik wil gewoon de nadruk leggen op “kies kolommen (als je geen lijst met kolommen ziet, controleer dan Opties > Systeem Tabellen)”

Als je eenmaal het Excel-bestand hebt geselecteerd, zul je waarschijnlijk this data source contains no visible tables prompt zien, en de beschikbare tabs en kolommen zijn geen. Microsoft geeft toe dat dit een bug is dat de tabs in de Excel-bestanden worden behandeld als “Systeemtabellen”, en de optie voor “Systeemtabellen” is niet standaard geselecteerd. Dus raak niet in paniek bij deze stap, u hoeft alleen maar op “optie” te klikken en “Systeemtabellen” aan te vinken, dan ziet u de beschikbare kolommen.

VLOOKUP en HLOOKUP kunnen worden gebruikt om te zoeken naar bijpassende primaire toetsen (verticaal of horizontaal opgeslagen) en om waarden te retourneren uit ‘attribuut’-kolommen/-pijlen.

U kunt gebruik maken van Microsoft Power Query, beschikbaar voor nieuwere versies van Excel (vergelijkbaar met het geaccepteerde antwoord, maar veel eenvoudiger en gemakkelijker). Power Query calls voegt zich bij ‘merges’.

De eenvoudigste manier is om uw 2 Excel-sheets als Excel-tabellen te hebben. Ga dan in Excel naar het Power Query-lint tabblad, en klik op de knop ‘Van Excel’. Zodra je beide tabellen hebt geïmporteerd in Power Query, selecteer je er één en klik je op ‘Samenvoegen’.

Hoewel ik het antwoord van Aprillion met behulp van Microsoft Query uitstekend vind, inspireerde het mij om Microsoft Access te gebruiken voor de datasheets die ik veel gemakkelijker vond.

Je moet natuurlijk MS Access geïnstalleerd hebben.

Stappen:

• Maak een nieuwe Access database aan (of gebruik een scratch DB).
• Gebruik Get External Data om je Excel gegevens te importeren als nieuwe tabellen.
• Gebruik Relationships om te laten zien hoe je tabellen zijn samengevoegd.
• Stel het Relatietype in dat overeenkomt met wat je wilt (representatief voor linker join etc.)
• Maak een nieuwe query die je tabellen samenvoegt.
• Gebruik External Data->Export to Excel om je resultaten te genereren.

Dat had ik echt niet kunnen doen zonder Aprillion’s geweldige antwoord.

Bij XLTools.net hebben we een goed alternatief voor MS Query gecreëerd om vooral met SQL Queries tegen Excel-tabellen te werken. Het heet XLTools SQL Queries . Het is een stuk gemakkelijker te gebruiken dan MS Query en werkt heel goed als u alleen maar SQL hoeft te maken en uit te voeren - geen VBA, geen complexe manipulaties met MS Query…

Met deze tool kunt u elke SQL query tegen tabellen in Excel-werkmap(pen) maken met behulp van ingesloten SQL-editor en deze direct uitvoeren met de optie om het resultaat op een nieuw of een bestaand werkblad te zetten.

U kunt bijna elk type verbinding gebruiken, inclusief LEFT OUTER JOIN (alleen RIGHT OUTER JOIN en FULL OUTER JOIN worden niet ondersteund).

Hier is een voorbeeld:

U kunt de SQL-stijl niet preformulieren op Excel-tabellen vanuit Excel. Dat gezegd hebbende, er zijn meerdere manieren om te bereiken wat je probeert te doen.

In Excel, zoals Reuben zegt, zijn de formules die waarschijnlijk het beste zullen werken VLOOKUP en HLOOKUP. In beide gevallen kom je overeen op een unieke rij en het geeft de waarde van de gegeven kolom terug van het gevonden id.

Als je alleen een paar extra velden aan de tweede lijst wilt toevoegen, voeg dan de formules toe aan de tweede lijst. Als je een “outer join” stijl tabel wilt, voeg dan de VLOOKUP formule toe aan de eerste lijst met ISNA om te testen of de lookup is gevonden. Als de Help van Excel u niet genoeg details geeft over hoe u deze in uw specifieke geval moet gebruiken, laat het ons dan weten.

Als u liever SQL gebruikt, koppel de gegevens dan aan uw databaseprogramma, maak uw query aan en exporteer de resultaten terug naar Excel. (In Access kunt u Excel Worksheets of Named Ranges importeren als een gekoppelde tabel).

Voor Excel 2007 Gebruiker: Gegevens uit andere bronnen, blader naar het Excel-bestand

. Volgens dit artikel kan het opvragen van gegevens uit XLS-versie 2003 leiden tot een fout “Deze gegevensbron bevat geen zichtbare tabellen”, omdat uw werkbladen worden behandeld als SYSTEEMtabel. Dus controleer de “Systeemtabellen” in de “Query Wizard – Kies Kolommen” dialoogvenster de opties wanneer u de query maakt zal werken rond.

Om uw verbinding te definiëren: Microsoft Query dialoogvenster > Tabelmenu > Verbindingen…

Om gegevens terug te sturen naar uw originele Excel-blad, kies “Gegevens terugsturen naar Excel-blad” uit het Microsoft Query dialoogvenster > Bestandsmenu.

Op zoek naar hetzelfde probleem kwam ik RDBMerge tegen, wat volgens mij een gebruiksvriendelijke manier is om gegevens uit meerdere Excel-werkboeken, csv- en xml-bestanden samen te voegen tot een samenvattend werkboek.

Als u bekend bent met databases, kunt u SQL Server gebruiken om beide werkbladen als Linked Servers te koppelen en vervolgens T-SQL gebruiken om uw back-end data werk te doen. Sluit dan af door Excel terug te koppelen naar SQL en de gegevens in een tabel te trekken (regulier of pivot). U kunt ook overwegen om Powerpivot te gebruiken; dit maakt het mogelijk om verbindingen te maken tussen willekeurige databasebronnen, inclusief Excel dat wordt gebruikt als platte databases.