Google Sheets, một công cụ bảng tính mạnh mẽ từ Google, cung cấp nhiều hàm giúp người dùng xử lý dữ liệu hiệu quả. Trong số đó, các hàm CHOOSECOLS và CHOOSEROWS là những trợ thủ đắc lực cho việc trích xuất các cột hoặc hàng cụ thể từ dữ liệu mà không cần đến các công thức lồng ghép phức tạp, rườm rà. Với khả năng hoạt động linh hoạt và tự động thích ứng với sự thay đổi của dữ liệu gốc, hai hàm này mang lại hiệu suất vượt trội, giúp tối ưu hóa quy trình làm việc và tạo ra các báo cáo động, chính xác.
Bài viết này sẽ đi sâu vào cách sử dụng hàm CHOOSECOLS và CHOOSEROWS trong Google Sheets, từ cú pháp cơ bản đến các ví dụ thực tế phức tạp hơn. Bạn sẽ học cách trích xuất dữ liệu một cách hiệu quả, kết hợp chúng với các hàm khác như VSTACK và SORT để tạo ra các bảng tổng hợp mạnh mẽ, và thậm chí là làm cho chúng trở nên động hoàn toàn bằng cách sử dụng tham chiếu ô và quy tắc xác thực dữ liệu. Mục tiêu là giúp bạn khai thác tối đa tiềm năng của Google Sheets để quản lý và phân tích dữ liệu một cách thông minh và chuyên nghiệp.
Cú Pháp Chuẩn Của Hàm CHOOSECOLS và CHOOSEROWS
Khi bạn đã nắm rõ cách thức hoạt động của một trong hai hàm này trong Google Sheets, bạn sẽ dễ dàng hiểu được cách hàm còn lại hoạt động, bởi chúng có cấu trúc rất tương đồng.
Dưới đây là cú pháp cơ bản của hàm CHOOSECOLS (chọn cột):
=CHOOSECOLS(dải_ô, chỉ_số_cột_1, [chỉ_số_cột_2, ...])Và đây là cú pháp của hàm CHOOSEROWS (chọn hàng):
=CHOOSEROWS(dải_ô, chỉ_số_hàng_1, [chỉ_số_hàng_2, ...])Hãy cùng tìm hiểu ý nghĩa của từng đối số:
| Đối số | Bắt buộc? | Ý nghĩa | Ghi chú |
|---|---|---|---|
dải_ô |
Có | Mảng hoặc phạm vi chứa các cột (CHOOSECOLS) hoặc hàng (CHOOSEROWS) mà bạn muốn trích xuất. | Có thể là một vùng ô cụ thể (ví dụ: A1:G10) hoặc tên của một bảng đã định dạng. |
chỉ_số_cột/hàng_1 |
Có | Số chỉ mục của cột (CHOOSECOLS) hoặc hàng (CHOOSEROWS) đầu tiên cần trích xuất. | Số dương đếm từ trái (CHOOSECOLS) hoặc trên (CHOOSEROWS) của mảng. Số âm đếm từ phải (CHOOSECOLS) hoặc dưới (CHOOSEROWS). |
chỉ_số_cột/hàng_2, ... |
Không | Các số chỉ mục của các cột (CHOOSECOLS) hoặc hàng (CHOOSEROWS) bổ sung cần trích xuất. | Mỗi số chỉ mục phải được phân cách bằng dấu phẩy. |
Nếu bất kỳ số chỉ mục nào là không (0) hoặc vượt quá số lượng cột/hàng trong mảng, Google Sheets sẽ trả về lỗi #VALUE!. Một lưu ý quan trọng là nếu bạn trích xuất một cột hoặc hàng từ giữa mảng, việc chèn thêm các cột hoặc hàng mới vào dữ liệu gốc có thể làm thay đổi cột hoặc hàng được trích xuất nếu bạn sử dụng các chỉ số tuyệt đối.
Ví Dụ 1: Trích Xuất Cột Hoặc Hàng Đầu Tiên và Cuối Cùng
Từ khi biết đến các hàm CHOOSECOLS và CHOOSEROWS, nhiều người dùng đã áp dụng chúng một cách hiệu quả để trích xuất các cột hoặc hàng đầu tiên và cuối cùng từ dữ liệu của mình. Đây là một mẹo đặc biệt hữu ích nếu cột hoặc hàng đầu tiên là tiêu đề và cột hoặc hàng cuối cùng chứa tổng cộng (total).
Trích Xuất Các Cột Đầu Tiên và Cuối Cùng
Hãy tưởng tượng bạn là quản trị viên kinh doanh và muốn tạo một báo cáo tóm tắt số lượng sản phẩm đã bán của từng cửa hàng trong hai quý đầu năm.
Bảng tính Google Sheets hiển thị dữ liệu bán hàng của 10 cửa hàng trong sáu tháng, minh họa dữ liệu nguồn cho hàm CHOOSECOLS.
Để thực hiện điều này, tại một ô trống bất kỳ, bạn hãy nhập công thức sau:
=CHOOSECOLS(Sales[#ALL],1,-1)Trong công thức trên:
CHOOSECOLSlà hàm bạn cần sử dụng để trích xuất dữ liệu từ cột “Shop” và “Tot”.Saleslà tên của bảng chứa mảng dữ liệu, và[#ALL]cho Google Sheets biết rằng bạn muốn bao gồm cả hàng tiêu đề trong kết quả.1chỉ định rằng bạn muốn trích xuất cột đầu tiên (trong trường hợp này là cột “Shop”).-1chỉ định rằng bạn muốn trích xuất cột cuối cùng (trong trường hợp này là cột “Tot”).
Mẹo nhỏ: Thay vì nhập dải ô theo cách thủ công vào đối số dải_ô, bạn có thể dùng chuột để chọn các ô mà bạn muốn tham chiếu, điều này giúp giảm thiểu lỗi.
Đây là kết quả bạn sẽ nhận được khi nhấn Enter:
Kết quả công thức CHOOSECOLS trong Google Sheets trích xuất cột đầu tiên và cột cuối cùng từ tập dữ liệu bán hàng.
Bạn sẽ thấy rằng, mặc dù bạn chỉ nhập công thức vào ô J1, kết quả đã “tràn” (spill) từ ô đó sang ô K12 để chứa toàn bộ số lượng cột và hàng trong kết quả.
Hơn nữa, vì dữ liệu nguồn nằm trong một bảng có cấu trúc, nếu dữ liệu thay đổi (ví dụ: thêm hoặc xóa hàng/cột), kết quả của hàm CHOOSECOLS cũng sẽ tự động cập nhật.
Kết quả hàm CHOOSECOLS tự động cập nhật trong Google Sheets khi dữ liệu nguồn được thêm một hàng mới.
Nếu bạn sử dụng tham chiếu ô trực tiếp trong đối số dải_ô thay vì tham chiếu dựa trên tên bảng, công thức sẽ không tự động cập nhật dữ liệu mới trừ khi bạn thay đổi tham chiếu ô thủ công. Dù là Google Sheets hay Microsoft Excel, việc sử dụng bảng có cấu trúc luôn được khuyến nghị vì chúng cung cấp công cụ tốt hơn và khả năng thích ứng cao hơn.
Giờ đây, bạn có thể dễ dàng sao chép dữ liệu tóm tắt này sang một bảng tính Google Sheets khác (như một tab dashboard) hoặc một tài liệu Google Docs.
Trích Xuất Các Hàng Đầu Tiên và Cuối Cùng
Tương tự, việc trích xuất các hàng đầu tiên và cuối cùng rất hữu ích để tạo các bảng tóm tắt dữ liệu theo chiều ngang.
Sử dụng cùng ví dụ như trên, bao gồm cả “Shop K” được thêm vào, giả sử bạn muốn tóm tắt tổng số sản phẩm bán được mỗi tháng.
Tại một ô trống, bạn hãy nhập:
=CHOOSEROWS(Sales[#ALL],1,-1)và nhấn Enter. Giống như ví dụ trước, việc thêm [#ALL] sau tên bảng cho Google Sheets biết rằng bạn muốn hàng tiêu đề được tính là hàng 1 trong quá trình tra cứu chỉ mục.
Kết quả công thức CHOOSEROWS trong Google Sheets trích xuất hàng đầu tiên và hàng cuối cùng từ tập dữ liệu bán hàng.
Ví Dụ 2: Trích Xuất Cột Từ Nhiều Phạm Vi Với VSTACK và SORT
Trong Ví dụ 1, chúng ta đã trích xuất các cột và hàng cụ thể từ các phạm vi riêng lẻ. Tuy nhiên, bạn có thể kết hợp CHOOSECOLS với hàm VSTACK để kết hợp các trích xuất từ nhiều phạm vi cùng một lúc, tạo ra một bảng dữ liệu hợp nhất.
Trong ví dụ này, bạn muốn tạo một danh sách tổng điểm của từng đội trong League 1 (màu đỏ) và League 2 (màu xanh lá cây).
Bảng tính Google Sheets hiển thị điểm số của 10 đội bóng từ hai giải đấu qua sáu trận, dữ liệu nguồn cho VSTACK và CHOOSECOLS.
Điều đầu tiên cần lưu ý là bạn sẽ không muốn trích xuất các tiêu đề cột từ các bảng dữ liệu gốc, vì bạn muốn xếp chồng dữ liệu từ mỗi bảng lên nhau một cách trực tiếp. Nếu bạn bao gồm các tiêu đề cột, chúng sẽ xuất hiện ở đầu cả hai tập dữ liệu trong kết quả, làm mất đi ý nghĩa của việc bạn đang xem một tập dữ liệu kết hợp.
Do đó, tại các ô J1 và K1, bạn hãy nhập tiêu đề cột theo cách thủ công, ví dụ: “Team” và “Total Score”.
Tiêu đề cột được nhập thủ công vào ô J1 và K1 trong Google Sheets để chuẩn bị cho việc trích xuất dữ liệu.
Sau đó, tại ô J2, bạn nhập công thức sau:
=CHOOSECOLS(VSTACK(League1,League2),1,-1)Trong công thức này:
CHOOSECOLSyêu cầu Google Sheets trích xuất các cột nhất định.VSTACKkết hợp kết quả theo chiều dọc (do đó có chữ “v” trong tên hàm).League1,League2là các mảng chứa dữ liệu; việc không bao gồm[#ALL]trong công thức cho Google Sheets biết không nên bao gồm hàng tiêu đề.1trích xuất cột đầu tiên (“Team”) từ mỗi mảng.-1trích xuất cột cuối cùng (“Tot”) từ mỗi mảng.
Khi bạn nhấn Enter, đây là kết quả bạn nhận được:
Kết quả của hàm CHOOSECOLS kết hợp với VSTACK trong Google Sheets, trích xuất dữ liệu từ nhiều bảng thành một.
Bạn có thể tiến thêm một bước và lồng toàn bộ công thức này vào trong hàm SORT, để bảng tổng hợp hiển thị các đội theo thứ tự tổng điểm của họ.
Để làm điều này, bạn hãy nhấp đúp vào ô nơi bạn đã nhập công thức CHOOSECOLS-VSTACK trước đó, và thêm các đối số SORT sau:
=SORT(CHOOSECOLS(VSTACK(League1,League2),1,-1),2,FALSE)Trong công thức trên:
SORTlà hàm dùng để sắp xếp một phạm vi dựa trên một cột được chỉ định.2yêu cầu Google Sheets sắp xếp phạm vi theo cột thứ hai (cột tổng điểm).FALSEchỉ ra rằng dữ liệu nên được sắp xếp theo thứ tự giảm dần (điểm cao nhất ở trên).
Kết quả của hàm CHOOSECOLS kết hợp với SORT và VSTACK, sắp xếp dữ liệu bảng tổng hợp theo điểm số trong Google Sheets.
Điều ấn tượng là, nếu bất kỳ giá trị nào trong tập dữ liệu gốc thay đổi, bảng tóm tắt sẽ tự động điều chỉnh tương ứng. Ví dụ, nếu thay đổi điểm Game 6 của Team H từ 51 thành 89 sẽ đưa họ từ vị trí thứ chín lên vị trí thứ tư.
Bảng tổng hợp trong Google Sheets tự động cập nhật kết quả khi dữ liệu gốc thay đổi, minh họa tính năng động của công thức.
Ví Dụ 3: Làm Cho Hàm CHOOSECOLS và CHOOSEROWS Trở Nên Động
Cho đến nay, chúng ta đã tìm hiểu cách sử dụng CHOOSECOLS và CHOOSEROWS để trích xuất các cột và hàng đầu tiên và cuối cùng từ một mảng trong Google Sheets. Tuy nhiên, trong ví dụ cuối cùng này, tôi sẽ chỉ cho bạn cách trích xuất các cột hoặc hàng khác một cách linh hoạt bằng cách tham chiếu đến các ô — thay vì sử dụng các số chỉ mục cố định (hard-coded numbers) — trong các đối số.
Chúng ta sẽ tập trung vào việc làm cho hàm CHOOSECOLS trở nên động, nhưng bạn có thể áp dụng nguyên tắc tương tự với hàm CHOOSEROWS.
Bảng Google Sheets này hiển thị điểm số của năm đội qua sáu trận đấu, cũng như tổng điểm kết hợp cho mỗi trận. Mục tiêu của bạn là tạo một bản tóm tắt theo số trận đấu mà bạn chỉ định trong ô B9. Đầu tiên, hãy nhập một số trận đấu vào ô này để có dữ liệu làm việc khi bạn tạo công thức CHOOSECOLS.
Bảng dữ liệu TeamScores và khu vực trích xuất dữ liệu trong Google Sheets, chuẩn bị cho việc sử dụng hàm CHOOSECOLS động.
Sau đó, tại ô A11, nhập công thức sau:
=CHOOSECOLS(TeamScores,1,B9+1)Trong công thức này:
CHOOSECOLScho Google Sheets biết bạn sẽ trích xuất các cột nhất định.TeamScoreslà tên của bảng chứa dữ liệu bạn muốn trích xuất.1là số chỉ mục cho cột “Team”.B9+1cho Google Sheets biết rằng đối số chỉ mục thứ hai được tính bằng cách cộng 1 vào giá trị trong ô B9. Lý do bạn cần thêm+1ở đây là vì các số trận đấu bắt đầu từ cột thứ hai của hàng 1, không phải cột đầu tiên. Do đó, việc nhập 4 vào ô B9 sẽ trích xuất dữ liệu từ cột thứ năm của bảng, đó là game 4.
Đây là những gì bạn nhận được khi nhấn Enter:
Điểm số của Game 4 được trích xuất từ bảng TeamScores trong Google Sheets bằng hàm CHOOSECOLS động.
Bây giờ, hãy thử nhập một số trận đấu khác vào ô B9 để xem kết quả cập nhật tương ứng.
Kết quả hàm CHOOSECOLS tự động cập nhật khi thay đổi số trò chơi được nhập vào ô tham chiếu trong Google Sheets.
Mẹo Chuyên Nghiệp: Thêm Quy Tắc Xác Thực Dữ Liệu
Mặc dù việc nhập số trận đấu vào ô B9 hoạt động chính xác như mong đợi để làm cho hàm CHOOSECOLS trở nên động, nhưng nếu một số không hợp lệ được nhập vào, bạn sẽ ngay lập tức gặp lỗi #VALUE!. Để ngăn điều này xảy ra, bạn có thể sử dụng tính năng xác thực dữ liệu (data validation) để kiểm soát các tham số nhập liệu của ô.
Để thực hiện điều này, hãy chọn ô B9, và trong tab Dữ liệu (Data), nhấp vào “Xác thực dữ liệu” (Data Validation).
Chọn ô B9 và mở tùy chọn "Xác thực dữ liệu" từ tab "Dữ liệu" trong Google Sheets.
Sau đó, trong bảng điều khiển “Quy tắc xác thực dữ liệu” (Data Validation Rules) ở phía bên phải, nhấp vào “Thêm quy tắc” (Add Rule).
Nhấn "Thêm quy tắc" trong bảng điều khiển Quy tắc xác thực dữ liệu của Google Sheets.
Tiếp theo, trong trường “Tiêu chí” (Criteria), chọn “Danh sách thả xuống” (Dropdown), và nhập các giá trị đầu vào có thể có cho ô này vào trường văn bản. Sau đó, nhấp vào “Thêm mục khác” (Add Another Item) và lặp lại quá trình này cho đến khi tất cả các tùy chọn được liệt kê. Khi tất cả các tùy chọn đã được thêm, nhấp vào “Xong” (Done).
Nhập các mục cho danh sách thả xuống trong bảng điều khiển Quy tắc xác thực dữ liệu của Google Sheets.
Bạn cũng có thể chọn “Danh sách thả xuống từ một phạm vi” (Dropdown From A Range) trong trường Tiêu chí, nơi bạn có thể chọn các ô chứa các mục nhập hợp lệ. Tuy nhiên, điều này không phải lúc nào cũng hoạt động như mong đợi với tiêu đề cột, ngay cả khi bạn đặt tên cho phạm vi. Thay vào đó, hãy thiết lập một bảng tính riêng biệt nơi bạn có thể giữ tất cả các phạm vi xác thực dữ liệu của mình.
Bây giờ, quay lại ô B9, nhấp vào mũi tên thả xuống và chọn một số trận đấu khác để xem kết quả cập nhật tương ứng.
Mũi tên thả xuống trong ô B9 của Google Sheets sau khi áp dụng quy tắc xác thực dữ liệu.
Kết Luận
Các hàm CHOOSECOLS và CHOOSEROWS trong Google Sheets là những công cụ không thể thiếu, mang lại khả năng trích xuất dữ liệu một cách linh hoạt và hiệu quả. Chúng cho phép bạn dễ dàng chọn lọc các cột hoặc hàng cụ thể, loại bỏ nhu cầu sử dụng các công thức lồng ghép phức tạp, đồng thời tạo ra các mảng động tự động cập nhật theo dữ liệu nguồn.
Từ việc trích xuất các cột/hàng đầu tiên và cuối cùng, kết hợp dữ liệu từ nhiều bảng với VSTACK và sắp xếp chúng bằng SORT, cho đến việc tạo ra các công thức động hoàn toàn bằng cách tham chiếu ô và sử dụng xác thực dữ liệu, CHOOSECOLS và CHOOSEROWS mở ra cánh cửa cho các giải pháp quản lý và phân tích dữ liệu tiên tiến. Nắm vững các hàm này sẽ giúp bạn tối ưu hóa quy trình làm việc, tạo ra các báo cáo chính xác và dễ hiểu hơn, từ đó đưa ra quyết định kinh doanh hoặc cá nhân hiệu quả hơn.
Một lưu ý quan trọng khi sử dụng các hàm này là kết quả của chúng tạo ra các mảng động, nghĩa là kết quả sẽ “tràn” (spill) ra từ ô mà bạn nhập công thức. Do đó, hãy luôn đảm bảo có đủ không gian trống trong bảng tính của bạn trước khi nhập công thức. Nếu không, bạn có thể gặp lỗi #REF!, báo hiệu rằng không có đủ chỗ để hiển thị toàn bộ kết quả.
Hãy bắt đầu thực hành ngay hôm nay để tận dụng tối đa sức mạnh của Google Sheets và đưa kỹ năng xử lý bảng tính của bạn lên một tầm cao mới! Bạn có ý kiến hay mẹo nào khác khi sử dụng các hàm này không? Hãy chia sẻ ý kiến của bạn trong phần bình luận bên dưới!
