Google表格:将两个表连接在一起

问题描述

链接到示例表- https: //docs.google.com/spreadsheets/d/1nKQXHwVO8KjsOy5qvjzh-s-YYRUYlAoH-3aXYbwcKsA/edit?usp=sharing

我有一个从google ads控制台下载的报告，看起来像这样:

I have a report that's downloaded from the google ads console which looks like this:

我从客户那里得到了一个报告，该报告导致了如下所示的google ads广告系列:

I get a report from the client which attributes leads to google ads campaigns like this:

我想将这两个表组合在一起，使它们看起来像这样:

I want to combine both these tables together to make them look like this:

如果您查看表格首位的数据，您会看到客户与我共享的报告具有归因于3月24日的销售线索.我退出google ads控制台的报告中没有该日期.使用sumifs公式不会捕获我从客户报告中获得的所有潜在客户.

If you look at the data in the first to tables, you'll see that the report the client shares with me has leads attributed to 24th March. This date is not present in the report that I got out of the google ads console. Using a sumifs formula will not capture all the leads that I get from the client's report.

本质上，要从输出表中的两个表中获取数据，我需要执行完整的外部联接.

Essentially, to get the data from both tables in the output table, I need to do a full outer join.

我不确定如何在Google表格中执行此操作.您能给我的任何帮助将不胜感激！

I'm not sure how to do that in google sheets. Any help you can give me would be greatly appreciated!

推荐答案

我在您的示例中创建了一个名为MK.Help的新选项卡.

I made a new tab in your sample called MK.Help.

然后我将此公式放在单元格L3中:

I then put this formula in cell L3:

=ARRAYFORMULA(QUERY({A3:G;{H4:I,IFERROR(ROW(H4:H)/{0,0,0,0}),J4:J}},"select Col1,Col2,SUM(Col3),SUM(Col4),SUM(Col5),SUM(Col6),SUM(Col7) where Col1 is not null group by Col1,Col2 label SUM(Col3)'Impressions',SUM(Col4)'Clicks',SUM(Col5)'Cost',SUM(Col6)'Leads',SUM(Col7)'Offline Leads'"))

那是你希望的吗?

这篇关于Google表格:将两个表连接在一起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！