1. Hàm CHOOSECOLS trong Excel

Hàm CHOOSECOLS trong Excel được thiết kế để trả về các cột đã chỉ định từ một mảng hoặc dải ô.

Cú pháp bao gồm các đối số sau:

=CHOOSECOLS(array, col_num1, [col_num2], …)

array (bắt buộc) - mảng hoặc phạm vi nguồn.

Col_num1 (bắt buộc) - một số nguyên chỉ định cột đầu tiên trả về.

Col_num2, … (tùy chọn) - số chỉ mục của các cột bổ sung để trả về.

Và dưới đây là cách mình ứng dụng hàm CHOOSECOLS trong thực tế.

2. Tính khả dụng của hàm CHOOSECOLS

Hiện tại, hàm CHOOSECOLS sẵn dùng trong Excel for Microsoft 365 (Windows và Mac) và Excel dành cho web.

3. Cách sử dụng hàm CHOOSECOLS trong Excel

CHOOSECOLS là một hàm mảng động, do đó, nó xử lý các mảng một cách tự nhiên. Công thức chỉ được nhập vào một ô - ô phía trên bên trái của dải ô đích - và công thức sẽ tự động tràn vào bao nhiêu cột như đã chỉ định cho nó đối số của nó và bao nhiêu hàng có trong mảng ban đầu. Kết quả là một mảng động duy nhất, được gọi là phạm vi tràn (spill range)

Để tạo công thức CHOOSECOLS trong Excel, bạn cần làm như sau:

- Đối với mảng, hãy cung cấp một dải ô hoặc một mảng giá trị.

- Đối với col_num, hãy cung cấp một số nguyên dương hoặc âm cho biết cột nào sẽ trả về. Một số dương kéo một cột tương ứng từ phía bên trái của mảng, một số âm - từ phía bên phải của mảng. Để nhận nhiều cột, bạn có thể xác định số của chúng trong các đối số riêng biệt hoặc trong một đối số ở dạng hằng số mảng.

Ví dụ: Giờ bạn cần lấy ra cột 1, 3, 4 thuộc vùng A2:E17 thì làm thế nào.

Ngoài ra, bạn có thể sử dụng hằng số mảng ngang chẳng hạn như {1,3,4} hoặc hằng số mảng dọc chẳng hạn như {1;3;4} để chỉ định số cột.

Tất cả ba công thức trên sẽ mang lại kết quả như nhau.

Trong một số trường hợp, bạn có thể thấy thuận tiện hơn khi nhập số cột vào một số ô, rồi tham chiếu các ô đó riêng lẻ hoặc cung cấp một tham chiếu phạm vi duy nhất. Ví dụ:

Cách tiếp cận này giúp bạn linh hoạt hơn - để trích xuất bất kỳ cột nào khác, bạn chỉ cần nhập các số khác nhau vào các ô được xác định trước mà không phải tự sửa đổi công thức.

Bây giờ bạn đã biết những điều cơ bản, hãy đi sâu vào các tính năng bổ sung và khám phá công thức CHOOSECOLS phức tạp hơn một chút để xử lý các tình huống cụ thể.

4. Nhận các cột cuối cùng từ phạm vi (range)

Để trả về một hoặc nhiều cột từ cuối phạm vi, hãy cung cấp số âm cho đối số col_num. Điều này sẽ làm cho hàm bắt đầu đếm các cột từ phía bên phải của mảng.

Chẳng hạn, để lấy cột cuối cùng trong phạm vi A2:F17, hãy sử dụng công thức sau:

Phía trên là ví dụ mình sử dụng hàm để lấy ra cột đầu tiên và 2 cột cuối cùng.

Bạn có thể hiểu đơn giản đó là nếu col_num > 0 thì bạn sẽ lấy các cột theo chiều từ trái sang phải.

Nếu col_num < 0 thì bạn sẽ lấy các cột theo chiều từ phải sang trái.

5. Nhận mọi cột khác trong Excel

Để trích xuất mọi cột khác từ một phạm vi nhất định, bạn có thể sử dụng CHOOSECOLS cùng với một số hàm khác. Dưới đây là hai phiên bản của công thức trích xuất các cột chẵn và lẻ.

Để lấy các cột lẻ (chẳng hạn như 1, 3, 5, v.v.), công thức là:

Để trả về các cột chẵn (chẳng hạn như 2, 4, 6, v.v.), công thức có dạng sau:

Cách thức hoạt động của công thức này:

Giải thích ngắn gọn: Hàm CHOOSECOLS trả về mọi cột khác dựa trên một mảng các số chẵn hoặc lẻ liên tiếp do hàm SEQUENCE tạo ra.

Phân tích công thức chi tiết:

Bước đầu tiên là tính xem cần trả về bao nhiêu cột. Đối với điều này, chúng tôi sử dụng một trong các công thức sau:

ROUNDUP(COLUMNS(A1:F1)/2,0)

hoặc

ROUNDDOWN(COLUMNS(A1:F1)/2,0)

COLUMNS đếm tổng số cột trong phạm vi nguồn. Bạn chia số đó cho 2, sau đó, tùy thuộc vào việc bạn đang trích xuất các cột chẵn hay lẻ, hãy làm tròn thương số lên hoặc xuống thành số nguyên với sự trợ giúp của ROUNDUP hoặc ROUNDDOWN. Làm tròn là cần thiết trong trường hợp phạm vi nguồn chứa số cột lẻ, sẽ để lại phần dư khi chia cho 2.

Phạm vi nguồn của chúng tôi có 6 cột. Vì vậy, đối với các cột lẻ ROUNDUP(6/2, 0) trả về 3, trong khi đối với các cột chẵn ROUNDDOWN(6/2, 0) trả về 3.

Số trả về được cung cấp cho đối số (row) đầu tiên của hàm SEQUENCE.

Đối với các cột lẻ, chúng tôi nhận được:

SEQUENCE(3, 1, 1, 2)

Công thức SEQUENCE này tạo ra một mảng số bao gồm 3 hàng và 1 cột, bắt đầu từ 1 và tăng thêm 2, là {1;3;5}.

Đối với các cột chẵn, ta có:

SEQUENCE(2, 1, 2, 2)

Trong trường hợp này, SEQUENCE tạo ra một mảng số bao gồm 2 hàng và 1 cột, bắt đầu từ 2 và tăng thêm 2, là {2;4}.

Mảng trên chuyển đến đối số col_num1 của CHOOSECOLS và bạn nhận được kết quả mong muốn.

6. Dò tìm và lấy ra các cột theo tên cột bạn muốn

Ở các bài trước bạn đang lấy cứng ra các cột ở vị trí 2, 3, 4.

Vậy giờ nếu bạn có tên các cột khớp với Data gốc nhưng muốn sử dụng các tên này để lấy ra các cột dữ liệu tương ứng thì bạn sẽ làm thế nào?

Để giải quyết bài toán này bạn có thể sử dụng hàm MATCH để tìm ra vị trí của các cột Student, Average, Total trong vùng A1:F1.

Sau đó bạn hãy sử dụng hàm CHOOSECOLS để lấy ra các cột tương ứng từ tên các cột bạn muốn.

Tuy nhiên trong trường hợp này nếu bạn chưa có hàm CHOOSECOLS thì bạn cũng có thể thay thế bằng hàm FILTER như sau.

Đây là 1 phiên bản được sử dụng để thay thế trong trường hợp bạn không có hàm CHOOSECOLS nhưng tin mình đi logic của hàm CHOOSECOLS dễ hiểu và cũng dễ sử dụng hơn rất nhiều.

7. Lấy ra tất cả các cột trừ các cột?

Thêm 1 ví dụ khá hay ho và cũng hơi khó nhằn như sau.

Giả sử ta có 1 tình huống như sau.

Bạn muốn lấy ra các cột không phải cột 2 và cột 3 (Thực tế ta đang có 6 cột)

Bạn hoàn toàn có thể viết là {1, 4, 5, 6}.

Nhưng nếu số cột của vùng dữ liệu gốc tăng lên thì bạn sẽ phải tiếp tục thêm các cột được tăng thêm vào. Theo mình nghĩ đó không phải là 1 bài toán tối ưu.

Vì thế ta sẽ làm như sau.

Cách trên cũng sẽ có hạn chế đó là khi bạn muốn thêm các cột khác vào danh sách thì bạn sẽ phải thêm tay cụm (COLUMN(A1:F1)=cột bạn muốn)

Vì thế mình gợi ý các bạn thêm 1 cách nữa cũng khá hay như sau.

Đây là công thức mình sử dụng ở bảng trên để các bạn tham khảo.

=FILTER(A1:F17,NOT(ISNUMBER(MATCH(COLUMN(A1:F1),FILTER(M:M,ISNUMBER(M:M)),0))))

8. Lật một mảng theo chiều ngang trong Excel

Để đảo ngược thứ tự các cột trong một mảng từ trái sang phải, bạn có thể sử dụng đồng thời các hàm CHOOSECOLS, COLUMN theo cách sau:

Kết quả là, phạm vi ban đầu được lật theo chiều ngang như trong hình bên dưới:

9. Trích xuất các cột dựa trên chuỗi có số

Trong trường hợp khi số chỉ mục của các cột mục tiêu được cung cấp ở dạng chuỗi văn bản, bạn có thể sử dụng hàm TEXTSPLIT để tách chuỗi bằng một dấu phân cách đã cho, sau đó chuyển mảng kết quả gồm các số cho CHOOSECOLS.

Nếu bạn chưa hiểu rõ về hàm TEXTSPLIT thì bạn có thể tham khảo bài viết này của ERX Việt Nam nhé.

Link bài viết

10. Trích xuất các cột từ nhiều vùng

Để nhận các cột cụ thể từ một số phạm vi không liền kề, trước tiên, bạn hợp nhất tất cả các phạm vi thành một với sự trợ giúp của hàm VSTACK, sau đó xử lý phạm vi đã hợp nhất bằng CHOOSECOLS.

Ví dụ: để trả về cột 1 và 3 từ các phạm vi A2:D7, A11:D14 và A18:D20, công thức là:

11. Hàm CHOOSECOLS không hoạt động

Nếu công thức CHOOSECOLS gây ra lỗi, rất có thể đó là một trong những lỗi sau.

- Lỗi #VALUE!

Xảy ra nếu giá trị tuyệt đối của bất kỳ đối số col_num nào bằng 0 hoặc lớn hơn tổng số cột trong mảng được tham chiếu.

- Lỗi #NAME?

Xảy ra nếu tên hàm sai chính tả hoặc hàm không có sẵn trong phiên bản Excel của bạn. Hiện tại, CHOOSECOLS chỉ được hỗ trợ trong Excel 365 và Excel dành cho web.

- Lỗi #SPILL!

Xảy ra khi một cái gì đó ngăn công thức tràn kết quả vào các ô lân cận. Để khắc phục, chỉ cần xóa các ô cản trở.

Đó là cách sử dụng hàm CHOOSECOLS trong Excel để trả về các cột cụ thể từ một dải ô hoặc mảng.

Link download File đính kèm bài viết: