Google Sheets đã trở thành công cụ không thể thiếu trong nhiều lĩnh vực, từ quản lý tài chính cá nhân đến phân tích dữ liệu kinh doanh phức tạp. Để tối ưu hóa quy trình làm việc và xử lý dữ liệu hiệu quả hơn, việc nắm vững các hàm mạnh mẽ là điều kiện tiên quyết. Trong số đó, hai hàm CHOOSECOLS và CHOOSEROWS nổi bật như những công cụ đắc lực giúp trích xuất dữ liệu một cách linh hoạt và động trong Google Sheets.
CHOOSECOLS và CHOOSEROWS là các hàm lý tưởng để nhanh chóng trích xuất các cột hoặc hàng cụ thể từ dữ liệu của bạn mà không cần tạo ra những công thức lồng ghép phức tạp. Hơn nữa, với tính năng động của mình, chúng có khả năng tự động thích ứng với các thay đổi trong dữ liệu nguồn, đảm bảo kết quả luôn được cập nhật. Điều này biến chúng thành một giải pháp vượt trội so với việc tham chiếu ô trực tiếp, giúp bạn tiết kiệm thời gian và công sức đáng kể trong việc quản lý và phân tích dữ liệu.
Bài viết này sẽ đi sâu vào cách sử dụng hai hàm này, từ cú pháp cơ bản đến các ví dụ ứng dụng nâng cao, bao gồm việc kết hợp chúng với các hàm khác như VSTACK và SORT, cũng như mẹo sử dụng Xác thực dữ liệu (Data Validation) để làm cho các công thức trở nên thông minh và an toàn hơn. Mục tiêu là trang bị cho bạn kiến thức và kỹ năng cần thiết để làm chủ CHOOSECOLS và CHOOSEROWS, từ đó nâng cao hiệu suất làm việc với Google Sheets.
Tổng Quan về Hàm CHOOSECOLS và CHOOSEROWS trong Google Sheets
Để bắt đầu làm việc với CHOOSECOLS và CHOOSEROWS, điều quan trọng là phải hiểu cú pháp và cách các đối số của chúng hoạt động. Khi bạn đã nắm được một trong hai hàm này, bạn sẽ dễ dàng hiểu cách hoạt động của hàm còn lại, bởi chúng có cấu trúc rất tương đồng.
Cú pháp cho CHOOSECOLS:
=CHOOSECOLS(mảng, chỉ_số_cột_1, [chỉ_số_cột_2], ...)Cú pháp cho CHOOSEROWS:
=CHOOSEROWS(mảng, chỉ_số_hàng_1, [chỉ_số_hàng_2], ...)Dưới đây là bảng giải thích ý nghĩa của từng đối số:
| Đối số | Bắt buộc? | Ý nghĩa | Ghi chú |
|---|---|---|---|
mảng (array) |
Có | Phạm vi chứa các cột (CHOOSECOLS) hoặc hàng (CHOOSEROWS) bạn muốn trích xuất. |
Có thể là một dải ô cụ thể hoặc tên của một bảng đã định dạng. |
chỉ_số_1 (index_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 (cột) hoặc trên (hàng), số âm đếm từ phải (cột) hoặc dưới (hàng). |
chỉ_số_2 (index_2) |
Không | 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 chỉ số phải được phân tách bằng dấu phẩy. |
Nếu bất kỳ số chỉ mục nào là 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 điểm quan trọng cần lưu ý là nếu một chỉ số trích xuất một cột hoặc hàng từ giữa mảng và bạn chèn thêm cột hoặc hàng vào dữ liệu nguồn, thì chỉ số đó có thể sẽ trích xuất một cột hoặc hàng khác so với ban đầu.
Giao diện Google Sheets trên laptop cùng biểu đồ và sách hướng dẫn
Ví Dụ 1: Trích Xuất Cột hoặc Hàng Đầu và Cuối Cùng
Kể từ khi tôi biết cách sử dụng các hàm CHOOSECOLS và CHOOSEROWS, tôi đã áp dụng chúng rất nhiều để trích xuất các cột hoặc hàng đầu 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.
Trích Xuất Cột Đầu và Cuối
Hãy tưởng tượng bạn là một quản trị viên kinh doanh và bạn muốn tạo một báo cáo tóm tắt số lượng sản phẩm đã bán bởi mỗi cửa hàng trong hai quý đầu năm.
Bảng dữ liệu doanh số cửa hàng trong Google Sheets
Để làm điều này, trong một ô trống, bạn hãy nhập công thức sau:
=CHOOSECOLS(Sales[#ALL],1,-1)Trong đó:
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 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”).
Lưu ý: Thay vì gõ mảng theo cách thủ công trong đối số mảng, bạn có thể sử dụng chuột để chọn các ô bạn muốn tham chiếu.
Khi bạn nhấn Enter, đây là kết quả bạn nhận được:
Kết quả hàm CHOOSECOLS trích xuất cột đầu và cuối từ dữ liệu doanh số
Bạn có thể thấy rằng, mặc dù bạn chỉ gõ công thức vào ô J1, kết quả đã “tràn” từ ô đó sang ô K12 để chứa số lượng cột và hàng trong kết quả.
Ngoài ra, 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, kết quả của hàm CHOOSECOLS cũng sẽ thay đổi theo. Điều này cũng đúng nếu có thêm cột hoặc hàng được thêm vào tập dữ liệu.
Kết quả CHOOSECOLS tự động cập nhật khi dữ liệu nguồn thay đổi
Nếu bạn sử dụng các tham chiếu ô trực tiếp trong đối số mảng thay vì tham chiếu dựa trên bảng, công thức sẽ không tự động nhận diện dữ liệu mới trừ khi bạn thay đổi các tham chiếu ô theo cách thủ công. Dù bạn đang sử dụng Google Sheets hay Microsoft Excel, tôi luôn khuyên bạn nên sử dụng bảng có cấu trúc thay vì các ô không có cấu trúc, vì chúng cung cấp các công cụ và khả năng thích ứng tốt hơn.
Bây giờ, bạn có thể sao chép dữ liệu tóm tắt này sang một bảng tính Google Sheets khác (chẳng hạn như một tab dashboard) hoặc một tài liệu Google Docs.
Trích Xuất Hàng Đầu và Cuối
Tương tự, việc trích xuất các hàng đầu và cuối cùng là rất tiện lợi để tạo các bản tóm tắt ngang của dữ liệu.
Sử dụng ví dụ tương tự 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 mỗi tháng.
Trong 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ả hàm CHOOSEROWS trích xuất hàng đầu và cuối của bảng dữ liệu
Ví Dụ 2: Trích Xuất Cột Từ Nhiều Vùng Dữ Liệu (Kết Hợp VSTACK và SORT)
Trong Ví dụ 1, chúng ta đã trích xuất các cột và hàng cụ thể từ từng 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.
Ở đây, bạn có kế hoạch tạo một danh sách tổng điểm của mỗi đội trong League 1 (màu đỏ) và League 2 (màu xanh lá cây).
Bảng điểm số các đội từ hai giải đấu trong Google Sheets
Điều đầu tiên cần lưu ý là bạn không muốn trích xuất các tiêu đề cột, vì bạn muốn xếp chồng dữ liệu từ mỗi bảng lên nhau. 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 đó, trong các ô J1 và K1, bạn hãy gõ các tiêu đề cột theo cách thủ công.
Nhập tiêu đề cột thủ công cho bảng tổng hợp dữ liệu
Sau đó, trong ô J2, bạn hãy nhập:
=CHOOSECOLS(VSTACK(League1,League2),1,-1)Trong đó:
CHOOSECOLScho Google Sheets biết rằng bạn muốn 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 các cột, và việc không bao gồm[#ALL]trong công thức cho Google Sheets biết không bao gồm các 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ả hàm CHOOSECOLS kết hợp VSTACK trích xuất dữ liệu từ nhiều bảng
Bạn có thể tiến thêm một bước nữa và lồng toàn bộ công thức bên trong hàm SORT, để bảng tóm tắt hiển thị các đội theo thứ tự tổng điểm của họ.
Logo Google Sheets nổi bật trên giao diện bảng tính điện tử
Để làm điều này, bạn hãy nhấp đúp vào ô mà bạn đã gõ 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 đó:
SORTlà hàm sắp xếp một phạm vi dựa trên một cột được chỉ định.2cho Google Sheets biết 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 (từ cao xuống thấp).
Kết quả hàm CHOOSECOLS, VSTACK và SORT sắp xếp dữ liệu tổng hợp
Đ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. Ví dụ, việc thay đổi điểm Game 6 của Team H từ 51 lên 89 đã đẩy họ từ vị trí thứ chín lên vị trí thứ tư.
Bảng tổng hợp tự động cập nhật khi dữ liệu gốc trong Google Sheets thay đổi
Ví Dụ 3: Tối Ưu Hàm CHOOSECOLS và CHOOSEROWS Trở Nên Động
Cho đến nay, tôi đã hướng dẫn bạn cách sử dụng CHOOSECOLS và CHOOSEROWS để trích xuất các cột và hàng đầu/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 động bằng cách tham chiếu các ô – thay vì sử dụng các số chỉ mục cố định (hard-coded index numbers) – trong các đối số.
Tôi sẽ hướng dẫn bạn cách sử dụng CHOOSECOLS một cách linh hoạt, mặc dù 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 cộng cho mỗi trận, và mục tiêu của bạn là tạo một bản tóm tắt theo số game bạn chỉ định trong ô B9. Đầu tiên, hãy nhập một số game vào ô này để bạn có thứ gì đó để làm việc khi tạo công thức CHOOSECOLS.
Bảng điểm số đội và ô nhập liệu cho công thức CHOOSECOLS động
Sau đó, trong ô A11, bạn hãy nhập:
=CHOOSECOLS(TeamScores,1,B9+1)Trong đó:
CHOOSECOLScho Google Sheets biết rằng 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 bao gồm+1ở đây là vì các số game 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, chính là game 4.
Đây là những gì bạn nhận được khi nhấn Enter:
Điểm số từ Game 4 được trích xuất động bằng hàm CHOOSECOLS trong Google Sheets
Bây giờ, hãy nhập một số game khác vào ô B9 để xem kết quả cập nhật tương ứng.
Kết quả CHOOSECOLS động cập nhật khi thay đổi số game trong ô tham chiếu
Mẹo Nâng Cao: Thêm Quy Tắc Xác Thực Dữ Liệu (Data Validation)
Mặc dù việc nhập số game vào ô B9 hoạt động chính xác như mong đợi trong việc 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, bạn sẽ đột ngột gặp lỗi #VALUE!. Để ngăn điều này xảy ra, bạn có thể sử dụng xác thực dữ liệu để kiểm soát các thông số đầu vào của ô.
Biểu tượng Google Sheets xuất hiện từ màn hình laptop, minh họa quản lý dữ liệu
Để làm điều này, bạn hãy chọn ô B9, và trong tab Data (Dữ liệu), nhấp vào Data Validation (Xác thực dữ liệu).
Chọn ô B9 và kích hoạt tính năng Xác thực dữ liệu trong Google Sheets
Sau đó, trong bảng Data Validation Rules (Quy tắc xác thực dữ liệu) ở phía bên phải, nhấp vào Add Rule (Thêm quy tắc).
Nhấn "Thêm quy tắc" trong cửa sổ Quy tắc xác thực dữ liệu của Google Sheets
Tiếp theo, trong trường Criteria (Tiêu chí), chọn Dropdown (Danh sách thả xuống), và nhập đầu vào khả thi đầu tiên cho ô này vào trường văn bản. Sau đó, nhấp vào Add Another Item (Thêm mục khác) và lặp lại quá trình này cho đến khi tất cả các đầu vào được liệt kê. Khi tất cả các tùy chọn đã được liệt kê, nhấp vào Done (Xong).
Thiết lập danh sách thả xuống cho quy tắc xác thực dữ liệu trong Google Sheets
Bạn cũng có thể chọn Dropdown From A Range (Danh sách thả xuống từ một phạm vi) trong trường Criteria, nơi bạn có thể chọn các ô chứa các mục 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 các tiêu đề cột, ngay cả khi bạn đặt tên cho phạm vi. Thay vào đó, bạn nên thiết lập một bảng tính riêng biệt nơi bạn có thể lưu giữ tất cả các phạm vi xác thực dữ liệu của mình.
Bây giờ, hãy quay lại ô B9, nhấp vào mũi tên thả xuống và chọn một số game khác để xem kết quả cập nhật tương ứng.
Ô B9 với mũi tên thả xuống sau khi áp dụng xác thực dữ liệu trong Google Sheets
Các hàm CHOOSECOLS và CHOOSEROWS trong Google Sheets tạo ra các mảng động, có nghĩa là kết quả sẽ tràn ra khỏi ô mà bạn đã nhập công thức. Do đó, bạn phải đảm bảo có đủ không gian trong bảng tính của mình trước khi nhập công thức. Nếu không, bạn sẽ thấy lỗi #REF!.
Kết luận
Các hàm CHOOSECOLS và CHOOSEROWS là những công cụ không thể thiếu đối với bất kỳ ai muốn làm việc hiệu quả với dữ liệu trong Google Sheets. Bằng cách tận dụng tính năng động và cú pháp đơn giản của chúng, bạn có thể trích xuất thông tin một cách chính xác, linh hoạt và tự động hóa các báo cáo phức tạp.
Từ việc trích xuất các cột/hàng đầu và cuối, kết hợp dữ liệu từ nhiều nguồn với VSTACK, sắp xếp kết quả với SORT, đến việc tạo ra các công thức động có tham chiếu ô và bảo vệ chúng bằng Data Validation, những hàm này mang lại khả năng tùy chỉnh mạnh mẽ. Nắm vững CHOOSECOLS và CHOOSEROWS sẽ không chỉ giúp bạn giải quyết các tác vụ hàng ngày nhanh hơn mà còn mở ra cánh cửa cho việc phân tích dữ liệu chuyên sâu hơn, biến Google Sheets thành một công cụ thực sự mạnh mẽ trong tay bạn.
Hãy bắt đầu áp dụng những kiến thức này vào công việc của bạn ngay hôm nay để trải nghiệm sự khác biệt mà CHOOSECOLS và CHOOSEROWS mang lại. Nếu bạn có bất kỳ câu hỏi hoặc muốn chia sẻ những ứng dụng sáng tạo khác của các hàm này, đừng ngần ngại để lại bình luận bên dưới!