求助：如何在Google Sheets中实现两张表格的全外连接？

Got it, let's fix this issue where SUMIFS misses rows that only exist in one of your two tables (like that March 24th lead in your client's report). A full outer join is exactly what you need—it keeps every record from both your Google Ads report and the client's attribution report, even when there's no matching data between them. Here are two straightforward methods to pull this off:

Method 1: Use Google Sheets' Built-in Merge Sheets Feature (Easiest) #

Google Sheets has a native tool that handles full outer joins without needing complex formulas:

1. Open your spreadsheet, then go to the top menu and click Data > Merge sheets.
2. In the pop-up window:
   • Pick your Google Ads report as the Primary table
   • Select your client's attribution report as the Secondary table
   • Choose the matching columns (likely Date and Campaign Name—make sure you select the corresponding columns from both tables)
   • Under Join type, select Full outer join (this ensures every row from both tables is kept, even if there's no match)
3. Hit Merge, and Google Sheets will automatically create a new tab with your combined data. Any missing values (like Ads data for that March 24th lead) will show up as blank cells, which you can fill or leave as-is.

Method 2: Use a QUERY Formula (For More Control) #

If you prefer using formulas to keep everything in one tab, you can combine the two tables and aggregate the data to get a full outer join result. Let's assume:

• Your Google Ads data is in a tab named AdsReport with columns: Date, Campaign, Clicks, Cost
• Your client's attribution data is in a tab named ClientReport with columns: Date, Campaign, Leads

Paste this formula into a blank cell in a new tab:

=QUERY({
  AdsReport!A:D, IFERROR(AdsReport!A:A/0, "");  // Add empty column for Leads
  ClientReport!A:C, IFERROR(ClientReport!A:A/0, ""), IFERROR(ClientReport!A:A/0, "")  // Add empty columns for Clicks & Cost
}, "SELECT Col1, Col2, MAX(Col3), MAX(Col4), MAX(Col5) WHERE Col1 IS NOT NULL GROUP BY Col1, Col2 LABEL Col1 'Date', Col2 'Campaign', MAX(Col3) 'Clicks', MAX(Col4) 'Cost', MAX(Col5) 'Leads'", 1)

How this works: #

• We first stack both tables together, adding empty columns to make their structures match.
• Then we use QUERY to group by Date and Campaign, and take the MAX of each metric. This works because for rows that only exist in one table, the other table's values will be blank—MAX ignores blanks and keeps the valid value.
• The LABEL clause cleans up the header names to match your original tables.

Why SUMIFS Didn't Work #

SUMIFS relies on finding matching rows in both tables to calculate totals. When a row exists only in your client's report (like that March 24th lead), there's no corresponding row in the Ads report for SUMIFS to reference—so it can't capture that lead. A full outer join solves this by preserving all rows from both datasets, regardless of matches.

内容的提问来源于stack exchange，提问作者Abhay