Trong kỷ nguyên số, việc quản lý và trích xuất dữ liệu một cách hiệu quả từ các bảng tính khổng lồ là yếu tố then chốt cho mọi hoạt động kinh doanh và cá nhân. Google Sheets, một công cụ bảng tính đám mây mạnh mẽ, liên tục bổ sung các tính năng giúp người dùng tối ưu hóa quy trình làm việc. Trong số đó, các hàm CHOOSECOLS và CHOOSEROWS nổi bật như những công cụ đắc lực, cho phép bạn nhanh chóng trích xuất các cột hoặc hàng cụ thể mà không cần đến những công thức lồng ghép phức tạp. Chúng không chỉ đơn giản hóa việc xử lý dữ liệu mà còn mang lại khả năng tự động thích ứng với những thay đổi trong dữ liệu nguồn, đảm bảo tính chính xác và kịp thời.
Bài viết này sẽ đưa bạn đi sâu vào cách sử dụng hai hàm động này, từ cú pháp cơ bản đến các ví dụ thực tế và mẹo nâng cao, giúp bạn khai thác tối đa tiềm năng của Google Sheets. Để tiện theo dõi và thực hành, bạn có thể tạo một bản sao của template Google Sheets này. Sau khi nhấp vào liên kết, hãy đăng nhập vào tài khoản Google của bạn và chọn “Tạo bản sao” để thêm tệp vào Google Drive của bạn. Sau đó, hãy làm theo các tab ở cuối cửa sổ Google Sheets khi bạn thực hiện các ví dụ dưới đây.
I. Tổng quan về cú pháp CHOOSECOLS và CHOOSEROWS trong Google Sheets
Khi bạn đã nắm rõ cách sử dụng một trong hai hàm này trong Google Sheets, bạn sẽ dễ dàng hiểu được cách hoạt động của hàm còn lại, bởi chúng có cú pháp và logic rất tương đồ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 | Lưu ý |
|---|---|---|---|
| a | Có | Mảng chứa các cột (CHOOSECOLS) hoặc hàng (CHOOSEROWS) bạn muốn trích xuất. |
Đây cũng có thể là tên của một bảng đã định dạng (formatted table). |
| b | Có | Chỉ số của cột (CHOOSECOLS) hoặc hàng (CHOOSEROWS) đầu tiên cần trích xuất. |
Một số dương sẽ đếm từ trái (cột) hoặc trên (hàng) của mảng, trong khi một số âm sẽ đếm từ phải (cột) hoặc dưới (hàng). |
| c | Không | Chỉ số 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ỳ chỉ số nào là số 0 hoặc vượt quá số lượng cột hoặc hàng trong mảng, Google Sheets sẽ trả về lỗi #VALUE!. Điều quan trọng cần lưu ý là nếu một chỉ số kéo 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 vào dữ liệu gốc có thể làm thay đổi cột hoặc hàng được trích xuất. Điều này nhấn mạnh tầm quan trọng của việc hiểu cấu trúc dữ liệu của bạn.
II. Ví dụ 1: Trích xuất cột/hàng đầu tiên và cuối cùng một cách hiệu quả
Từ khi tôi khám phá ra cách hoạt động của các hàm CHOOSECOLS và CHOOSEROWS, tôi đã sử dụng chúng rất nhiều để 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 thủ thuật đặ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ác 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 nhà quản lý 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 của mỗi cửa hàng trong hai quý đầu năm.
Bảng dữ liệu doanh số bán hàng của các cửa hàng trong sáu tháng
Để làm điều này, trong một ô trống, 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 hàng tiêu đề cũng được bao gồm trong kết quả.1cho 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”).-1cho 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õ thủ công mảng vào đối số a, bạn có thể sử dụng chuột để chọn các ô bạn muốn tham chiếu.
Đây là kết quả khi bạn nhấn Enter:
Kết quả trích xuất cột đầu và cuối bằng CHOOSECOLS trong Google Sheets
Hãy chú ý cách mà, mặc dù bạn chỉ gõ công thức vào ô J1, kết quả đã “spill” (tràn) từ ô đó đến ô K12 để phù hợp với 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 (structured table), 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ả công thức CHOOSECOLS tự động cập nhật khi dữ liệu nguồn thay đổi trong Google Sheets
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 diện bất kỳ dữ liệu mới nào trừ khi bạn thay đổi các tham chiếu ô một cách thủ công. Dù bạn đang sử dụng Google Sheets hay Microsoft Excel, tôi luôn khuyến nghị 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 bảng điều khiển) 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 tiện lợi để tạo các bản tóm tắt dữ liệu theo chiều ngang của bạn.
Sử dụng cùng ví dụ như trước, bao gồm cả Shop K đã thêm, hãy nói rằng 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, nhập:
=CHOOSEROWS(Sales[#ALL],1,-1)và nhấn Enter. Tương tự 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 chỉ mục tìm kiếm.
Kết quả trích xuất hàng đầu và cuối bằng CHOOSEROWS trong Google Sheets
III. Ví dụ 2: Kết hợp CHOOSECOLS với VSTACK và SORT để xử lý dữ liệu nâng cao
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.
Ở đây, bạn có kế hoạch tạo một danh sách tổng điểm của mỗi độ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 qua sáu trận đấu
Đ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, một cách trực tiếp lên trên bảng kia. 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ả, từ đó làm mất đi ý nghĩa rằng bạn đang xem một tập dữ liệu kết hợp.
Do đó, trong các ô J1 và K1, hãy nhập thủ công các tiêu đề cột.
Tiêu đề cột được nhập thủ công trong Google Sheets cho dữ liệu sắp trích xuất
Sau đó, trong ô J2, 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 cụ thể.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[#ALL]không được bao gồm 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ả công thức CHOOSECOLS kết hợp với VSTACK để trích xuất dữ liệu từ nhiều bảng trong Google Sheets
Sắp xếp kết quả với hàm SORT
Bạn có thể tiến thêm một bước nữa và lồng toàn bộ công thức vào 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, nhấp đúp vào ô mà bạn đã nhập công thức 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 đó:
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 “Tot”).FALSEcho biết rằng dữ liệu nên được sắp xếp theo thứ tự giảm dần.
Kết quả công thức CHOOSECOLS kết hợp SORT và VSTACK để trích xuất và sắp xếp dữ liệu từ nhiều bảng
Điều đáng kinh ngạc 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. Ở đây, việc thay đổi điểm của Team H trong Game 6 từ 51 lên 89 đã đưa họ từ vị trí thứ chín lên vị trí thứ tư.
Dữ liệu trong bảng tính Google Sheets được thay đổi, và kết quả công thức tự động cập nhật
IV. Ví dụ 3: Làm cho hàm CHOOSECOLS và CHOOSEROWS trở nên động hơn
Cho đến nay, tôi đã chỉ cho bạn 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 động bằng cách tham chiếu các ô—thay vì sử dụng các chỉ số được mã hóa cứng—trong các đối số.
Tôi sẽ chỉ cho bạn 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 trong sáu trận đấu, cũng như tổng điểm kết hợp cho mỗi trận đấu. Mục tiêu của bạn là tạo một bản tóm tắt theo số trận đấu 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 TeamScores trong Google Sheets và bảng trích xuất dữ liệu để hiển thị kết quả công thức
Sau đó, trong ô A11, 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 cụ thể.TeamScoreslà tên của bảng chứa dữ liệu bạn muốn trích xuất.1là chỉ số cho cột “Team”.B9+1cho Google Sheets biết rằng đối số chỉ số 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ố trận đấu bắt đầu ở cột thứ hai của hàng 1, chứ không phải cột đầu tiên. Kết quả là, 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ố từ Trận đấu 4 được trích xuất từ bảng trong Google Sheets bằng CHOOSECOLS
Bây giờ, hãy nhập một số trận đấu khác vào ô B9 để xem kết quả cập nhật tương ứng.
Điểm của mỗi đội từ Trận đấu 2 được trích xuất từ bảng trong Google Sheets bằng CHOOSECOLS
Mẹo chuyên nghiệp: Thêm quy tắc xác thực dữ liệu (Data Validation)
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ẽ đột ngột gặp lỗi #VALUE!. Vì vậy, để 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 (Data Validation) để 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 (Data), nhấp vào “Xác thực dữ liệu” (Data Validation).
Ô B9 được chọn trong Google Sheets, và Quy tắc Xác thực Dữ liệu được chọn từ menu thả xuống tab Dữ liệu
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).
Chọ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 giá trị đầu vào 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ục khác” (Add Another Item), và lặp lại quá trình này cho đến khi tất cả các giá trị đầ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 “Xong” (Done).
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 các 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ờ, hãy 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 một ô của Google Sheets đã được đính kèm các quy tắc xác thực dữ liệu
Các hàm CHOOSECOLS và CHOOSEROWS trong Google Sheets tạo ra các mảng động, 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!.
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 làm việc với Google Sheets, đặc biệt là khi xử lý các tập dữ liệu lớn và phức tạp. Khả năng trích xuất dữ liệu một cách linh hoạt, tự động và hiệu quả mà không cần đến các công thức lồng ghép rắc rối giúp tiết kiệm đáng kể thời gian và công sức. Việc kết hợp chúng với các hàm mạnh mẽ khác như VSTACK để gộp dữ liệu hay SORT để sắp xếp, cùng với khả năng làm cho chúng động hơn thông qua tham chiếu ô và bảo vệ đầu vào bằng Data Validation, mở ra vô số khả năng tùy chỉnh và tự động hóa.
Hy vọng với hướng dẫn chi tiết và các ví dụ cụ thể này, bạn đã nắm vững cách khai thác tối đa sức mạnh của CHOOSECOLS và CHOOSEROWS để tối ưu hóa quy trình quản lý dữ liệu và tạo ra các báo cáo chính xác, kịp thời. Hãy bắt đầu áp dụng những kiến thức này vào công việc hàng ngày của bạn để trải nghiệm sự khác biệt.
Bạn đã áp dụng các hàm này vào công việc của mình như thế nào? Hãy chia sẻ ý kiến của bạn về sản phẩm này và khám phá thêm các bài viết hữu ích khác về Google Sheets và các thủ thuật công nghệ tại trangtincongnghe.net.
