Trong các tab này, bạn sẽ tìm hiểu cách Kết hợp dữ liệu với nhau từ các bảng dữ liệu khác nhau trong Google Trang tính, hiệu quả khá giống với cách sử dụng mệnh đề Tham gia trong SQL
Mục lục cho hướng dẫn này
Thẻ số. Tên1Giới thiệu về Công thức tham gia
23Công thức nối 2 cột *
4Raw1 *5Raw2 *6Raw3 *7Giải thích về công thức nối 2 cột
8Phần thưởng phần 1. chức năng Truy vấn
9Phần thưởng phần 2. Trộn dữ liệu nâng cao
* Bốn Tab này đã được tạo như một ví dụ phức tạp về cách tạo công thức Tham gia 2 cột.
Công thức cụ thể được tìm thấy trong Tab Nr. 3, trong khi Tab Nr. 4, 5 và 6 chứa dữ liệu thô [từ đó công thức lấy dữ liệu].
Trong Tab Nr. 7, bạn sẽ tìm thấy các giải thích chuyên sâu về cách thức hoạt động của công thức Tham gia trong Tab Nr. 3
______________________________________________________________________________________________________________________________________________________________________________________________
Trong tài liệu tab này
Công thức tham gia, chúng là gì và cách sử dụng chúng
Các chức năng phải được hiểu để tạo công thức Tham gia
Hàng ngang. 56-Công thức mảngRow. 58Hàng. 87-VlookupRow. 117-Nguồn để tìm hiểu thêm về các chức năng này
Hàng ngang. 185Công thức tham gia, chúng là gì và cách sử dụng chúng
Với việc sử dụng các công thức Tham gia, bạn có thể tự động Kết hợp các bảng dữ liệu khác nhau lại với nhau. Giả sử bạn đang làm việc với dữ liệu từ Google Analytics và bạn
muốn Kết hợp dữ liệu với nhau từ nhiều báo cáo khác nhau hoặc giả sử bạn muốn kết hợp dữ liệu từ nhiều ngữ cảnh khác nhau, chẳng hạn như. Cơ sở dữ liệu SQL,
Google Analytics, Trình quản lý quảng cáo Facebook, Trình quản lý quảng cáo Snapchat, Google Adwords, v.v. [về cơ bản là bất kỳ dữ liệu nào có thể được nhập vào Google Trang tính bằng
một công cụ chẳng hạn như Super Metrics].
Khi sử dụng công thức Tham gia, bạn phải sử dụng ít nhất một cột làm mẫu số chung có thể tìm thấy trong tất cả các bảng khác nhau mà bạn đang làm việc,
để được .
Cái hay của Công thức nối là tất cả những điều này có thể đạt được bằng cách chỉ viết một công thức cụ thể trong một ô.
Các hàm cần thiết để tạo công thức Tham gia là. Công thức mảng, Dấu ngoặc nhọn và Vlookup. Mặc dù có thể tạo công thức Tham gia bằng cách sử dụng
hàm truy vấn, điều này sẽ không được thảo luận trong hướng dẫn này vì dù sao thì nó cũng kém tin cậy hơn nhiều [so với hàm Vlookup]. Tuy nhiên, có thể sử dụng Hàm truy vấn
cùng với Công thức nối để xác định rõ hơn cách công thức trả về dữ liệu; .
Điều này sẽ được chuyển tải sau trong hướng dẫn.
Các chức năng phải được hiểu để tạo công thức Tham gia
Với việc sử dụng Arrayformula, có thể trả về một phạm vi ô, so với các công thức thông thường chỉ trả về một giá trị duy nhất
Do đó, lợi ích của Arraformulas là bạn có thể lấy các cột dữ liệu chỉ từ một công thức được xác định trong một ô
Trong ví dụ này, chúng ta có thể thấy từ công thức đã xác định trong ô A75, có thể trả về tất cả dữ liệu thô chỉ từ một ô
Dữ liệu thô. ABCABCABCABCCông thức. ABCABCABCABCTuy nhiên, đây là một cách trả về dữ liệu khá tĩnh với việc sử dụng Arrayformula. Nếu chúng ta muốn linh hoạt hơn nhiều
bằng cách sử dụng Arrayformula, nơi có thể cấu trúc lại thứ tự theo cách các cột dữ liệu được trả về, chúng ta cần sử dụng
Dấu ngoặc nhọn .
Dấu ngoặc nhọn hữu ích bất cứ khi nào chúng ta muốn viết một công thức phức tạp hơn bằng cách sử dụng Arrayformula và chúng ta muốn tạo các tham chiếu ô theo cách linh hoạt hơn
, sắp xếp lại thứ tự dữ liệu và cả .
Sử dụng Dấu ngoặc nhọn có nghĩa là linh hoạt hơn rất nhiều và không có Dấu ngoặc nhọn, Arrayformula có xu hướng tĩnh hơn trong chức năng của nó
3 ví dụ về cách Công thức mảng có thể được kết hợp với Dấu ngoặc nhọn và được sử dụng để trả về nhiều cột dữ liệu
Tìm các ô A106, E106 & I106 để tham khảo công thức
Dữ liệu thô. ABC1ABC2ABC3ABC4Công thức. AC1ABC1CA1BAC2ABC2CA2BAC3ABC3CA3BAC4ABC4CA4BCông thức này cho thấy rằng bạn có thể chọn bất kỳ
dữ liệu nào bạn muốn từ dữ liệu thô của mình và trong ví dụ
cụ thể này, cột B .
the formula.
Công thức này cho thấy rằng thông tin từ hai
cột khác nhau trên thực tế có thể được kết hợp thành một
cột dữ liệu, miễn là bạn sử dụng ' .
in an Arrayformula.
Trong công thức này, nó cho thấy Công thức mảng thực sự linh hoạt như thế nào
khi trả về thông tin từ dữ liệu thô của bạn
và trình bày dữ liệu đó dưới dạng .
Hàm Vlookup bao gồm 4 khía cạnh cụ thể được phân tách bằng dấu phẩy trong dấu ngoặc đơn của hàm.
Để biết ví dụ cụ thể về cách sử dụng hàm Vlookup kết hợp với Công thức mảng và Dấu ngoặc nhọn, hãy chuyển đến Hàng 157.
Cú pháp của hàm là như thế này
VLOOKUP[ Search_Key, Range, Index, Is_sorted]
Search_KeyKey_Search là cái mà Vlookup sử dụng làm tham chiếu ô để tìm mẫu số chung trong cột đầu tiên của Phạm vi Vlookup [về cơ bản loại giá trị cần tìm kiếm].
Khi có sự trùng khớp giữa Khóa_tìm kiếm và Phạm vi, dữ liệu có thể được trả về từ các hàng tương ứng theo sự trùng khớp này. Khi Search_Key
được tham chiếu với Arrayformula, toàn bộ cột có thể được khớp thay vì chỉ các giá trị đơn lẻ.
Trong hướng dẫn này, Search_Key sẽ được gọi là "Cột tham gia hỗ trợ" [thêm về điều này trong tab "Công thức và lý thuyết tham gia"]
Phạm viPhạm vi được xác định bởi các tham chiếu ô nhằm mục đích lấy Search_Key để tìm mẫu số chung [và đây là theo mặc định trong cột đầu tiên của phạm vi],
và . Các cột chính xác của Phạm vi nơi các giá trị sẽ
được trả về, được xác định bởi Chỉ mục Vlookup.
Trong hướng dẫn này, cột đầu tiên của Phạm vi Vlookup sẽ được gọi là "Cột tham gia hỗ trợ" và các cột tiếp theo sẽ là dữ liệu mà bạn
thích .
Vì một số lý do, Dấu ngoặc nhọn phải luôn được sử dụng khi xác định Phạm vi
- nó cho phép tạo nhiều tham chiếu ô trong Phạm vi và bạn có thể sử dụng dấu phẩy để phân tách các tham chiếu ô,
trong Dấu ngoặc nhọn cho nỗ lực này.
- có thể tùy chỉnh thứ tự của tất cả các cột mà hàm Vlookup tìm kiếm thông tin
- hàm Vlookup sẽ liên quan đến cách dữ liệu này được cấu trúc trong Mảng với tư cách là nguồn chính của
thông tin [bảng dữ liệu], KHÔNG phải chính dữ liệu thô.
Điều này được trình bày trong ví dụ về cách sử dụng Vlookup, tại Hàng 157.
Chỉ mục được sử dụng để xác định từ cột hoặc cột cụ thể nào mà các giá trị sẽ được trả về từ Phạm vi Vlookup.
Khi xác định Chỉ mục Vlookup, hãy luôn ước tính bạn có bao nhiêu cột trong Phạm vi và vị trí của chúng trong Phạm vi.
Sau đó, bạn có thể xác định cột nào trong số những cột này sẽ trả về giá trị từ trong Chỉ mục Vlookup.
Khi sử dụng Dấu ngoặc nhọn trong Chỉ mục, nó không liên quan gì đến Công thức mảng, nhưng cần thiết để sử dụng bất cứ khi nào các giá trị từ nhiều cột sẽ được
trả về từ Dải ô.
Ví dụ.
{2,3} có nghĩa là bạn trả về các giá trị từ cột thứ hai và thứ ba của Phạm vi.
Một số hướng dẫn chung để xác định Chỉ số
- Lần duy nhất bạn sẽ không sử dụng Dấu ngoặc nhọn trong Chỉ mục là khi bạn muốn trả về các giá trị từ cột thứ hai của Phạm vi và không gì khác,
.
- Không bao giờ đưa cột đầu tiên của Phạm vi vào Chỉ mục [có nghĩa là số '1' sẽ không bao giờ được nhìn thấy trong Chỉ mục], vì điều này sẽ trả về "Cột tham gia hỗ trợ" thứ cấp
in the results of the formula.
Các ví dụ phổ biến về cách chỉ mục thường được xác định.
2
{2,3}
{2,3,4}
{ . .
etc. ...
Khía cạnh này của hàm Vlookup, xác định xem cột đầu tiên của Phạm vi đã chỉ định có được sắp xếp hay không. Nếu được đặt thành Sai, kết quả khớp chính xác sẽ được trả về [đó là điều chúng tôi muốn].
Vì True được đặt làm mặc định, nên bạn nên cố tình đặt Is_sorted thành False [hoặc bạn có thể nhập 0 thay vì False].
Ví dụ về cách sử dụng Vlookup [công thức tại ô A167]
[Search_Key]A1C1A1DA2C2A2DA3C3A3DA4C4A4DC1DC2DC3DC4DNhận xétHãy chú ý đến cách Phạm vi đã được xác định [trong Dấu ngoặc nhọn] và lưu ý rằng cột E đã được đặt ở đầu Phạm vi