Giả sử mình có các trường sản phẩm, số lượng, đơn giá theo công thức thông thường bạn sẽ lấy số lượng nhân cho đơn giá để tính thành tiền, sau đó lại dùng hàm SUM để tính tổng tiền của các sản phẩm. Đối với công thức mảng bạn hoàn toàn có thể nhân mảng với nhau bằng cách nhập vào công thức =SUM(B3:B7*C3:C7) sau đó ấn Ctrl + Shift + Enter.

Bình thường chỉ là một ô nhân với nhau còn đối với công thức mảng là một mảng nhân với nhau, mảng ở đây là B3 đến B7 nhân với mảng từ C3 đến C7.

Doanh thu thông thường

Đến với ví dụ 2 mình sẽ có 3 trường dữ liệu gồm công ty, doanh thu 2019 và doanh thu 2020. Bây giờ mình sẽ tính công ty nào tăng trưởng cao nhất và thấp nhất là bao nhiêu, thông thường bạn sẽ tính bằng cách lấy doanh thu 2020 trừ cho doanh thu 2019 sau đó lại dùng hàm MIN, MAX để tìm kiếm công ty tăng trưởng cao nhất và thấp nhất.

Còn đối với công thức mảng bạn sẽ nhập vào như sau =MAX(C2:C8-B2:B8) sau đó ấn Ctrl + Shift + Enter cho hàm tính tăng trưởng cao nhất, tương tự cho tăng trưởng thấp nhất bạn nhập vào =MIN(C2:C8-B2:B8) và ấn Ctrl + Shift + Enter.

Công thức mảng

Sang ví dụ thứ 3, giả sử mình có một vùng giá trị bao gồm các giá trị thông thường và các giá trị bị lỗi trong Excel. Để kiểm tra số lỗi trong vùng giá trị bằng công thức mảng bạn nhập vào công thức sau =SUM(--ISERROR(B3:B9)) sau đó ấn Ctrl + Shift + Enter, hàm ISERROR sẽ kiểm tra các lỗi bao gồm:

N/A,

VALUE!,

REF!,

DIV/0!,

NUM!,

NAME và

NULL!.

Để đếm số ô trống trong vùng giá trị bằng dùng công thức mảng mình sẽ sử dụng hàm ISBLANK cách dùng tương tự như ISERROR, nhập vào =SUM(--ISBLANK(B3:B9)) sau đó ấn Ctrl + Shift + Enter.

Nếu bạn muốn đếm số ô không trống trong vùng dữ liệu bằng công thức mảng thì chỉ cần thêm NOT trước công thức mảng, ví dụ =SUM(NOT(ISBLANK(B3:B9))*1) ấn Ctrl + Shift + Enter khi hoàn tất công thức.

Tính phần trăm hoàn thành dự án

Đến với ví dụ số 4, giả sử mình có một dự án bao gồm các cột nhiệm vụ, ngày hoàn thành, phần trăm hoàn thành và số ngày thực hiện. Để tính tổng phần trăm hoàn thành của dự án này bằng công thức thông thường bạn sẽ ngày hoàn thành nhân cho phần trăm hoàn thành, sau đó lấy tổng số ngày thực hiện chia cho tổng số ngày hoàn thành để có được kết quả.

Còn đối với công thức mảng bạn chỉ cần lấy tổng mảng của ngày, nhân cho mảng của phần trăm hoàn thành sau đó chia cho tổng ngày hoàn thành là có thể tính được tổng phần trăm hoàn thành của dự án, nhập vào như sau =SUM(B2:B6*C2:C6)/SUM(B2:B6) sau đó Ctrl + Shift + Enter.

Tính tổng từ 2 đến 100

Sang ví dụ số 5 mình có số bắt đầu là 2 và số kết thúc là 100, để có thể tính tổng của từ 2 đến 100 bằng công thức mảng bạn nhập vào công thức =SUM(ROW(INDIRECT(A2&":"&B2))) hoàn tất ấn Ctrl + Shift + Enter trong đó A2 là số bắt đầu còn B2 là số kết thúc.

Tương tự đối với tính trung bình từ 2 đến 100 bạn nhập vào công thức sau =AVERAGE(ROW(INDIRECT(A2&":"&B2))) hoàn tất ấn Ctrl + Shift + Enter.

Tính rũi ro

Sang ví dụ số 6 mình có 3 trường là rủi ro và doanh thu với các mức là cao, thấp tương ứng theo đó là các điểm. Để tính điểm tối đa của rủi ro cao và doanh thu thấp của vùng dữ liệu này bạn nhập vào công thức =MAX(IF((A3:A11="Cao")*(B3:I3B11="Thấp"),C3:C11)) hoàn tất ấn Ctrl + Shift + Enter, hàm này sẽ tính điểm cao nhất khi thỏa hai điều kiện trong hàm IF.

Số tương thích

Sang ví dụ số 7, giả sử mình có một vùng dữ liệu bao gồm nhiều số khác nhau, để có thể biết một mảng gồm nhiều số xuất hiện bao nhiêu lần trong vùng dữ liệu bạn nhập vào công thức mảng sau đây: =COUNT(IF(MATCH(A1:D11,B15:B18,0)>0,A1:D11)) hoàn tất ấn Ctrl + Shift + Enter.

Mình sẽ giải thích ngắn gọn hàm trên như sau: đầu tiên sẽ thực thi hàm MATCH tìm các số để tìm các số tương thích với điều kiện của mảng trong vùng dữ liệu, nếu thỏa điều kiện sẽ đếm các số này và cho ra kết qua

Tổng các chữ số

Ví dụ số 8 là giả sử mình có một số và muốn tính tổng các số có trong số đó, bạn nhập vào công thức mảng như sau =SUM(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)*1) hoàn tất ấn Ctrl + Shift + Enter.

Hàm trên đầu tiên sẽ kiểm tra số tại ô B3 là 565 sau đó hàm LEN sẽ kiểm tra độ dài của số là 3, tiếp theo hàm INDIRECT sẽ trả về số từ 1 đến 3 và hàm ROW sẽ chuyển về thành 1, 2, 3. Kế tiếp là hàm MID sẽ tách các số tương ứng với số bạn nhập vào, cuối cùng là tính tổng các số vừa tách. Do kết quả cuối cùng là Text nên bạn cần phải nhân với 1 để chuyển về số.

Lọc tên trùng nhau

Sang ví dụ 9, giả sử mình có cột Tên chứa các dữ liệu có thể trùng nhau hoặc không trùng nhau, để lọc ra được danh sách mỗi dữ liệu chỉ được xuất hiện một lần bằng công thức mảng bạn nhập vào như sau =INDEX($A$3:$A$9,MATCH(0,COUNTIF($C$2:$C2,$A$3:$A$9),0)) hoàn tất ấn Ctrl + Shift + Enter.

Hàm trên khá là phức tạp bạn có thể tìm hiểu thêm nếu muốn nhé, mình sẽ giải thích sơ bộ như sau: đầu tiên hàm COUNTIF sẽ kiểm tra ô C2 tức là ô trước đó để kiểm tra xem có trùng nhau hay chưa sẽ trả về 0 nếu chưa trùng và 1 là đã trùng, hàm MATCH sẽ thực hiện giá trị nào tương thích với 0 tức là chưa trùng và trả về vị trí của nó trên vùng dữ liệu, cuối hàm INDEX sẽ truy xuất đến vị trí vừa tìm được và trả về kết quả là Đậu Xanh.

Công thức mảng nâng cao

Đến với ví dụ cuối cùng là một công thức mảng nâng cao dành cho các bạn muốn tìm hiểu kỹ hơn về phần này, bạn có thể tham khảo trong file Excel.

Như vậy mình đã hoàn thành 10 ví dụ về công thức mảng trong Excel, đây là một kỹ năng vô cùng quan trọng và cần thiết nếu bạn đang muốn học Excel một cách chuyên nghiệp. Nếu muốn học thêm các bài học Excel miễn phí hãy tìm kiếm trên website này nhé.