Trong thế giới làm việc với dữ liệu ngày nay, Google Sheets đã trở thành một công cụ không thể thiếu đối với nhiều cá nhân và doanh nghiệp. Để tối ưu hóa việc quản lý và trích xuất thông tin, hai hàm CHOOSECOLS và CHOOSEROWS nổi lên như những “người hùng thầm lặng”. Chúng cho phép bạn nhanh chóng trích xuất các cột hoặc hàng cụ thể từ tập dữ liệu khổng lồ mà không cần tạo ra những công thức lồng ghép phức tạp, rắc rối. Điều đặc biệt là, với tư cách là các hàm động, CHOOSECOLS và CHOOSEROWS sẽ tự động điều chỉnh kết quả khi dữ liệu nguồn thay đổi, mang lại sự linh hoạt và hiệu quả vượt trội. Website xalocongnghe.com sẽ hướng dẫn bạn chi tiết cách tận dụng tối đa sức mạnh của hai hàm này để quản lý bảng tính Google Sheets của mình một cách chuyên nghiệp nhất.
Cú pháp và Đối số của CHOOSECOLS và CHOOSEROWS
Khi bạn đã nắm vững cách sử dụng một trong hai hàm CHOOSECOLS hoặc CHOOSEROWS trong Google Sheets, việc hiểu và áp dụng hàm còn lại sẽ trở nên rất dễ dàng bởi cú pháp tương đồng của chúng.
Đây là cú pháp cho hàm CHOOSECOLS:
=CHOOSECOLS(a,b,c)Và đây là cú pháp cho hàm CHOOSEROWS:
=CHOOSEROWS(a,b,c)Dưới đây là ý nghĩa của từng đối số:
| Đối số | Bắt buộc? | Ý nghĩa | Ghi chú |
|---|---|---|---|
| a | Có | Mảng dữ liệu 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 dải ô hoặc tên của một bảng đã định dạng. |
| b | 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 từ trên xuống (CHOOSEROWS) của mảng, trong khi số âm đếm từ phải (CHOOSECOLS) hoặc từ dưới lên (CHOOSEROWS). |
| c | Không | Các số chỉ mục của bất kỳ cột (CHOOSECOLS) hoặc hàng (CHOOSEROWS) bổ sung nào cần trích xuất. | Mỗi số chỉ mục 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à số 0 hoặc vượt quá số lượng cột/hàng trong mảng, Google Sheets sẽ trả về lỗi #VALUE!. Ngoài ra, nếu số chỉ mục lấy một cột hoặc hàng từ giữa mảng, việc chèn thêm cột hoặc hàng mới có thể làm thay đổi cột hoặc hàng được trích xuất. Do đó, cần lưu ý khi cấu trúc dữ liệu của bạn có thể thay đổi.
Ví dụ 1: Trích Xuất Cột/Hàng Đầu Tiên và Cuối Cùng
Kể từ khi biết cách các hàm CHOOSECOLS và CHOOSEROWS hoạt động, tôi đã sử dụng chúng rất nhiều để trích xuất 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 tiện lợi 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 Tiên và Cuối Cùng
Trong ví dụ này, hãy tưởng tượng bạn là một 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 chứa dữ liệu bán hàng của mười cửa hàng trong sáu tháng.
Để làm điều này, trong một ô trống, bạn hãy nhập:
=CHOOSECOLS(Sales[#ALL],1,-1)Trong đó:
- CHOOSECOLS là hàm bạn cần sử dụng để trích xuất dữ liệu từ cột “Shop” và “Tot” (Tổng cộng).
- Sales là 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ả.
- 1 cho biết bạn muốn trích xuất cột đầu tiên (trong trường hợp này là cột “Shop”).
- -1 cho biết 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”).
Thay vì gõ mảng dữ liệu thủ công vào đối số a, bạn có thể dùng chuột để chọn các ô mà bạn muốn tham chiếu.
Đây là kết quả khi bạn nhấn Enter:
Kết quả của công thức CHOOSECOLS trong Google Sheets trả về cột đầu tiên và cuối cùng của một tập dữ liệu.
Bạn có thể thấy, mặc dù bạn chỉ gõ công thức vào ô J1, kết quả đã tự động tràn sang ô K12 để phù hợp với 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ẽ tự động cập nhật. Đ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ả của công thức CHOOSECOLS trong Google Sheets đã được cập nhật để bao gồm một hàng dữ liệu bổ sung trong dữ liệu nguồn.
Nếu bạn sử dụng tham chiếu ô trực tiếp trong đối số a thay vì tham chiếu dựa trên bảng, công thức sẽ không tự động nhận dữ liệu mới trừ khi bạn thay đổi tham chiếu ô thủ công. Dù bạn đang sử dụng Google Sheets hay Microsoft Excel, việc sử dụng các bảng có cấu trúc luôn được khuyến nghị hơn so với 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. Giờ đây, 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 Tiên và Cuối Cùng
Tương tự như vậy, việc trích xuất hàng đầu tiên và cuối cùng rất tiện lợi để tạo ra các bản tóm tắt ngang của dữ liệu.
Sử dụng cùng ví dụ như trước, bao gồm cả “Shop K” đã được thêm vào, giả sử bây giờ 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 nhập:
=CHOOSEROWS(Sales[#ALL],1,-1)và nhấn Enter. Cũng như trong 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ủa công thức CHOOSEROWS trong Google Sheets trả về hàng đầu tiên và cuối cùng của một tập dữ liệu.
Ví dụ 2: Trích Xuất Cột Từ Nhiều Dải Ô Kết Hợp VSTACK và SẮP XẾP
Ở Ví dụ 1, chúng ta đã trích xuất các cột và hàng cụ thể từ từng dải ô riêng lẻ. Tuy nhiên, bạn có thể kết hợp CHOOSECOLS với hàm VSTACK để gộp kết quả trích xuất từ nhiều dải ô khác nhau 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 từng đội trong Giải đấu 1 (màu đỏ) và Giải đấu 2 (màu xanh lá cây).
Bảng tính Google Sheets chứa điểm số của 10 đội thuộc hai giải đấu trong sáu trận.
Đ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 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ủa cả hai tập dữ liệu trong kết quả, làm mất đi cảm giác rằng bạn đang xem một tập dữ liệu kết hợp.
Kết quả là, trong các ô J1 và K1, bạn sẽ gõ thủ công các tiêu đề cột.
Tiêu đề của hai cột, nơi dữ liệu sẽ được trích xuất, được gõ vào ô J1 và K1 trong Google Sheets.
Sau đó, trong ô J2, bạn nhập:
=CHOOSECOLS(VSTACK(League1,League2),1,-1)Trong đó:
- CHOOSECOLS cho Google Sheets biết rằng bạn muốn trích xuất các cột nhất định.
- VSTACK kết hợp kết quả theo chiều dọc (do đó có chữ “v” trong tên hàm, viết tắt của “vertical”).
- League1,League2 là các mảng chứa các cột; 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 đề.
- 1 trích xuất cột đầu tiên (“Team”) từ mỗi mảng.
- -1 trí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 công thức CHOOSECOLS trong Google Sheets, kết hợp với hàm VSTACK, để trích xuất dữ liệu từ nhiều bảng khác nhau.
Bạn có thể tiến thêm một bước nữa và lồng toàn bộ công thức này vào 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ọ.
Để làm điều này, bạn nhấp đúp vào ô mà bạn đã nhập kết hợp CHOOSECOLS-VSTACK trước đó và thêm các đối số SORT sau:
=SORT(CHOOSECOLS(VSTACK(League1,League2),1,-1),2,FALSE)Trong đó:
- SORT là hàm dùng để sắp xếp một dải ô dựa trên một cột được chỉ định.
- 2 cho Google Sheets biết để sắp xếp dải ô theo cột thứ hai.
- FALSE chỉ ra rằng dữ liệu nên được sắp xếp theo thứ tự giảm dần.
Kết quả của công thức CHOOSECOLS trong Google Sheets, kết hợp với các hàm SORT và VSTACK, để trích xuất dữ liệu từ các bảng khác nhau và sắp xếp theo tổng điểm.
Đ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 theo. Ở đây, việc thay đổi điểm trận 6 của “Team H” từ 51 thành 89 đã đưa họ từ vị trí thứ chín lên vị trí thứ tư. Sự linh hoạt này giúp bạn dễ dàng theo dõi và cập nhật thông tin mà không cần can thiệp thủ công.
Một số dữ liệu được thay đổi trong bảng tính Google Sheets và kết quả của công thức được cập nhật tương ứng.
Ví dụ 3: Làm Cho Hàm CHOOSECOLS và CHOOSEROWS Động
Cho đến nay, tôi đã chỉ cho bạn cách sử dụng CHOOSECOLS và CHOOSEROWS để trích xuất 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 các ô—thay vì sử dụng các số chỉ mục cố định—trong các đối số. Tôi sẽ minh họa cách sử dụng CHOOSECOLS một cách động, 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ố 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 để bạn có dữ liệu để làm việc khi tạo công thức CHOOSECOLS của mình.
Bảng dữ liệu trong Google Sheets có tên TeamScores, và một bảng trích xuất dữ liệu nơi kết quả của công thức sẽ được thực thi.
Sau đó, trong ô A11, bạn nhập:
=CHOOSECOLS(TeamScores,1,B9+1)Trong đó:
- CHOOSECOLS cho Google Sheets biết rằng bạn sẽ trích xuất các cột nhất định.
- TeamScores là tên của bảng chứa dữ liệu bạn muốn trích xuất.
- 1 là số chỉ mục cho cột “Team”.
- B9+1 cho 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ì 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. Kết quả là, việc gõ 4 vào ô B9 sẽ trích xuất dữ liệu từ cột thứ năm của bảng, đó là trận 4.
Đây là kết quả bạn nhận được khi nhấn Enter:
Điểm số từ Trận 4 được trích xuất từ một bảng trong Google Sheets bằng cách sử dụng CHOOSECOLS.
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. Sự linh hoạt này giúp bạn dễ dàng chuyển đổi giữa các trận đấu hoặc dữ liệu khác nhau mà không cần sửa đổi công thức.
Điểm số của mỗi đội từ Trận 2 được trích xuất từ một bảng trong Google Sheets bằng CHOOSECOLS, cho thấy tính năng động của hàm.
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 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ẽ gặp ngay lỗi #VALUE!. Để ngăn chặ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 tham số đầu vào của ô.
Để làm điều này, chọn ô B9, và trong tab “Dữ liệu”, nhấp vào “Xác thực dữ liệu”.
Ô B9 được chọn trong Google Sheets, và Xác thực dữ liệu được chọn từ menu thả xuống của tab Dữ liệu.
Sau đó, trong bảng “Quy tắc xác thực dữ liệu” ở phía bên phải, nhấp vào “Thêm quy tắc”.
Thêm Quy tắc được chọn trong bảng Quy tắc xác thực dữ liệu của Google Sheets.
Tiếp theo, trong trường “Tiêu chí”, chọn “Danh sách thả xuống”, và nhập mục nhập có thể có đầu tiên cho ô này vào trường văn bản. Sau đó, nhấp vào “Thêm một mục khác”, và lặp lại quá trình này cho đến khi tất cả các mục nhập được liệt kê. Khi tất cả các tùy chọn đã được liệt kê, nhấp vào “Xong”.
Các mục đang được nhập vào bảng Quy tắc xác thực dữ liệu cho một danh sách thả xuống trong Google Sheets.
Bạn cũng có thể chọn “Danh sách thả xuống từ một dải ô” trong trường “Tiêu chí”, 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 tiêu đề cột, ngay cả khi bạn đặt tên cho dải ô. Thay vào đó, hãy thiết lập một bảng tính riêng nơi bạn có thể lưu giữ tất cả các dải ô 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ẹo này không chỉ giúp bạn tránh lỗi mà còn cải thiện đáng kể trải nghiệm người dùng khi tương tác với bảng tính của mình.
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 từ ô 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!. Với những kiến thức và ví dụ trên, hy vọng bạn có thể khai thác tối đa tiềm năng của hai hàm này để biến Google Sheets thành một công cụ phân tích và quản lý dữ liệu mạnh mẽ hơn nữa cho công việc của mình.
