10/13/18

thumbnail

Cách sử dụng công thức mảng Array Formulas trong Excel

Bài viết dưới đây hướng dẫn chi tiết công thức mảng và ví dụ thực tế giúp bạn hiểu rõ hơn và ứng dụng chúng trong công việc một cách hiệu quả nhất. Để sử dụng công thức mảng bạn luôn nhớ mỗi khi nhập xong công thức tính toán hãy nhấn tổ hợp phím Ctrl + Shift + Enter thay cho phím Enter nhé.

1. Công thức mảng để tính tổng theo điều kiện, đếm theo điều kiện

Để tính tổng có điều kiện chúng ta hay sử dụng hàm sumif nhưng để đơn giản hóa công thức hãy thử theo dõi ví dụ tính tổng có điều kiện sử dụng công thức mảng.
Ví dụ: Tính tổng số nhân viên bán được với số lượng lớn hơn 38. Và tính tổng số lượng hàng bán được của những nhân viên đó.
Có bảng số liệu:



1.1. Đếm số nhân viên bán được lượng sản phẩm lớn hơn 30

Trường hợp này bạn có thể sử dụng hàm counif nhưng ở đây mình giới thiệu cách sử dụng công thức mảng Sum. Tại ô cần tính nhậ công thức sau: =SUM(B4:B10>30).
Đếm số nhân viên bán được lượng sản phẩm lớn hơn 30
Nhấn tổ hơp phím Ctrl + Shift + Enter. Kết quả:
Kết quả
Do điều kiện so sánh trả về giá trị True hoặc False mà lại sử dụng hàm Sum nên ở đây bạn thêm vào công thức như sau:
Thêm công thức hàm SUM
Nhấn F9 -> Nhấn tổ hợp phím Ctrl + Shift + Enter được kết quả như hình dưới:
Kết quả 2

1.2. Tính tổng có điều kiện sử dụng công thức mảng

Tính tổng số lượng sản phẩm đã bán được của những người đã bán trên 30 sản phẩm.
Bước 1: Tại ô cần tính nhập công thức.
Tính tổng có điều kiện sử dụng công thức mảng
Nhấn tổ hợp phím Ctrl + Shift + Enter được kết quả:
Tính tổng có điều kiện sử dụng công thức mảng 2
Với một số máy nếu bạn nhập công thức như trên mà không cho kết quả đúng hãy bôi đen tham số trong hàm Sum -> Nhấn F9 kiểm tra lại kết quả -> nhấn Ctrl Shift Enter.

2. Công thức mảng sử dụng trong việc tính chênh lệch lớn nhất của mã số cổ phiếu qua các ngày

Ví dụ: Tính giá chênh lệch lớn nhất và nhỏ nhất của mã cổ phiếu qua các ngày:
Tính giá chênh lệch lớn nhất và nhỏ nhất
Thông thường các bạn sẽ tạo ra một cột phụ tìm giá chênh lệch sau đó sử dụng hàm Min, Max. Ở bài viết này sẽ sử dụng công thức mảng để tính mà không cần tạo ra 1 cột phụ về giá chênh lệch.
Bước 1: Tại ô cần tính giá chênh lệch nhập công thức sau =Max (E5:E10-D5:D10).
Nhập công thức Max
Sau khi nhập xong công thức nhấn Ctrl + Shift Enter được kết quả. Bạn có thể kiểm tra bằng cách bôi đen các tham số trong hàm Max và nhấn F9 để kiểm tra:
Kiểm tra giá trị chênh lệch
Ở đây Max sẽ giá trị trong mảng gồm 6 phần tử.

3. Tính Max, Min theo điều kiện kết hợp công thức mảng

Ví dụ ở đây tính doanh số lớn nhất và nhỏ nhất của các nhân viên đã bán được trong tháng. Tại ô cần tính doanh số lớn nhất nhập công thức: = Max(If($B$5:$B$10=E6,C5:C10)).
Tính Max, Min theo điều kiện kết hợp công thức mảng
Sau khi nhập công thức nhấn tổ hợp phím Ctrl + Shift + Enter được kết quả:
Tính Max, Min theo điều kiện kết hợp công thức mảng (Kết quả)
Tính doanh số nhỏ nhất tương tự như doanh số lớn nhất chỉ thay bằng hàm MIN:
Tính doanh số nhỏ nhất
Tương tự bạn tính cho các nhân viên còn lại chỉ thay đổi điều kiện If được so sánh với mã nhân viên đó.

4. Tìm những mặt hàng được giao cho các đại lý ở 3 miền Bắc, Trung, Nam

Tìm những mặt hàng được giao cho các đại lý ở 3 miền Bắc, Trung, Nam
Sử dụng hàm Index, match kết hợp với công thức mảng.
Tại ô cần tính nhập công thức sau: =IFERROR(INDEX($D$5:$D$12,MATCH($F$6&$G$5,$B$5:$B$12&$C$5:$C$12,0)),"").
Tìm những mặt hàng được giao cho các đại lý ở 3 miền Bắc, Trung, Nam 2
Sau khi nhập xong công thức nhấn tổ hợp phím Ctrl + Shift + Enter được kết quả:
Tìm những mặt hàng được giao cho các đại lý ở 3 miền Bắc, Trung, Nam 3
Tương tự kéo công thức cho các ô còn lại được kết quả:
Tìm những mặt hàng được giao cho các đại lý ở 3 miền Bắc, Trung, Nam 4
Ở đây sử dụng hàm IFERROR để trả về kết quả không bị lỗi khi ở miền Bắc, Trung mặt hàng Máy gọt hoa quả không có thì trả về giá trị rỗng.

5. Tính tổng chi phí

Ví dụ: Tính tổng chi phí của các mặt hàng SP1, SP2, SP3 dựa vào bảng chi phí và số lần xuất hiện mã sp trong bảng sản phẩm đã bán.
Tại ô cần tính nhập công thức: SUM(SUMIFS(H5:H7,G5:G7,C5:C9)).
Tính tổng chi phí
Sau khi nhập công thức nhấn tổ hợp phím Ctrl + Shift + Enter được kết quả:
Tính tổng chi phí 2

6. Tính tổng doanh số của những nhân viên bán được nhiều nhất

Ở đây bạn có thể chọn tính tổng của n nhân viên bán được nhiều nhất với n=2, 3, 4…
Ví dụ tính tổng doanh số bán được của 3 nhân viên bán được nhiều nhất.


Tại ô cần tính nhập công thức: =SUM(LARGE(C6:C12,{1,2,3})) trong đó {1,2,3} là mảng chứa số phần tử cần tính tổng, ở đây là 3.
Tính tổng doanh số của những nhân viên bán được nhiều nhất
Sau khi nhập công thức nhấn tổ hợp phím Ctrl Shift + Enter được kết quả:
Tính tổng doanh số của những nhân viên bán được nhiều nhất 2
Ví dụ: Tính tổng doanh số của 4 nhân viên có doanh số lớn nhất bạn chỉ việc thêm phần tử bằng 4 vào mảng:
Tính tổng doanh số của những nhân viên bán được nhiều nhất 3

7. Sử dụng công thức mảng để đánh số thứ tự đảm bảo thành phần dữ liệu không bị xóa

Bước 1: Bội đen vùng dữ liệu muốn tạo số thứ tự -> quay trở lên trên nhập công thức.
Sử dụng công thức mảng để đánh số thứ tự đảm bảo thành phần dữ liệu không bị xóa
Sau khi nhập công thức nhấn tổ hợp phím Ctrl + Shift Enter được kết quả:
Sử dụng công thức mảng để đánh số thứ tự đảm bảo thành phần dữ liệu không bị xóa 2
Với cách nhập số thứ tự từ công thức mảng việc thay đổi thành phần thì thứ tự cột STT không bị thay đổi. Ví dụ thêm 1 dòng: Bội đen 1 dòng bất kì -> Chuột phải chọn Insert.
Sử dụng công thức mảng để đánh số thứ tự đảm bảo thành phần dữ liệu không bị xóa 3
Số thứ tự dòng 1, 2, 3, 4, 5 bị thay đổi nhưng số thứ tự trong cột thứ tự không bị thay đổi.
Sử dụng công thức mảng để đánh số thứ tự đảm bảo thành phần dữ liệu không bị xóa 4
Trên đây là một số ví dụ về cách sử dụng công thức mảng tùy công việc của bạn lựa chọn cho hợp lý.
Chúc các bạn thành công!



- Bài viết được chia sẽ trên trang www.tuhocexcel.net , Chia sẽ vui lòng ghi rõ nguồn gốc bài đăng.
- Group tham gia Tự Học Excel : https://www.facebook.com/groups/1716543358373810/

- Clip Tự học Excel - Xem tại đây : http://www.tuhocexcel.net/videos
- Mọi thắc mắc, góp ý vui lòng mail về địa chỉ : tuhocexcel2018@gmail.com
hoặc liên hệ qua : http://www.tuhocexcel.net/p/lien-he-tu-hoc-excel.html

No Comment