Đang thực hiện

Top banner

Một số ứng dựng công thức mảng trong Excel

Đăng ngày: 05/04/2019

(ĐHVH HN) - Như chúng ta đã biết Excel được xem là công cụ hỗ trợ đắc lực trong công việc tính toán, xử lý dữ liệu một cách nhanh chóng, chính xác, giúp người dùng giảm thiểu được những thao tác thủ công. Một trong những tính năng độc đáo và mạnh mẽ nhất của Excel chính là khả năng tính toán với công thức mảng. Công thức mảng giúp chúng ta có thể giải quyết được rất nhiều bài toán mà sử công thức thông thường sẽ rất khó hoặc phải áp dụng những công thức rất phức tạp mới xử lý được.

Một công thức mảng đơn có thể thay thế hàng trăm công thức thông thường. Công thức mảng có thể sử dụng cho các công việc như: tính tổng các số thỏa điều kiện đã cho, tính trung bình cộng các số theo điều kiện, đếm theo điều kiện, tìm giá trị lớn nhất, nhỏ nhất theo điều kiện, xếp hạng liên tục… Bài viết này giúp các bạn ứng dụng công thức mảng hiệu quả vào công việc thông qua các ví dụ cụ thể.

 
  1. Tìm hiểu công thức mảng
Trước khi chúng ta bắt đầu tìm hiểu về hàm và các công thức mảng trong Excel, thì chúng ta hãy tìm hiểu rằng thế nào là thuật ngữ “mảng”. Một mảng là một tập hợp các phần tử có quan hệ hay độc lập với nhau. Ví dụ, {1; 2; “ba”; “four”}.
Trong Excel, một mảng có một chiều hoặc hai chiều (chính là các dòng, các cột trong Excel). Ví dụ, một vùng dữ liệu nằm trên một dòng hoặc một cột (A1:A3; B2:C2) là các mảng một chiều; nếu một vùng dữ liệu nằm trên ít nhất 2 dòng, 2 cột là mảng 2 chiều (A1:D4).
Để kết thúc một công thức mảng, các bạn cần sử dụng tổ hợp phím Ctrl + Shift + Enter (thay vì Enter). Khi đó, cặp ngoặc {} bao quanh công thức để thể hiện đó là công thức mảng. Ví dụ: {=MAX(IF($F$9:$F$19="LÝ",$G$9:$G$19))}.
  1. Các ví dụ
    1. Ví dụ 1: Ví dụ đơn giản của công thức mảng
Yêu cầu: tính tổng doanh thu tất cả các mặt hàng:
 

Thông thường ta sẽ thêm 1 cột TỔNG TIỀN bên cạnh cột GIÁ để tính tổng tiền cho từng mặt hàng, sau đó sẽ cộng tất cả tiền của các mặt hàng lại thì được tổng doanh thu:
 

 
Tuy nhiên, nếu sử dụng công thức mảng ta không cần tính tiền cho từng loại hàng mà có thể tính luôn được tổng doanh thu qua công thức sau:
 

 
Công thức sẽ nhân giá trị trong mỗi hàng riêng của mảng được chỉ định (các ô từ D3 đến E8), có thêm các tổng từng mặt hàng và kết quả cuối cùng là tổng doanh thu.

Ví dụ đơn giản trên cho thấy sức mạnh của công thức mảng trong Excel. Khi làm việc với hàng trăm, hàng ngàn hàng chứa dữ liệu thì bạn có thể cân nhắc nó tiết kiệm bao nhiêu thời gian với một công thức mảng trong mỗi ô.
  1. Ví dụ 2: Tính tổng, trung bình cộng theo điều kiện
Yêu cầu: tính tổng doanh số, trung bình cộng doanh số của 3 nhân viên bán được nhiều nhất.
  • Tính tổng doanh số 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(D3:D13,{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. Sau khi nhập công thức nhấn tổ hợp phím Ctrl Shift + Enter được kết quả:

 
  • Tính trung bình cộng doanh số 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: Hàm ROW(INDIRECT("1:3")) sẽ tạo ra một mảng gồm các số nguyên từ 1đến 3 như sau {1,2,3} trong bộ nhớ máy. Hàm LARGE sẽ trả về giá trị lớn thứ 1, 2 và 3 (tương ứng với các giá trị trong mảng số nguyên vừa được tạo ra) trong vùng D3:D13. Hàm AVERAGE sẽ trả về trung bình cộng doanh số của 3 nhân viên bán 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ả:


 
  1. Ví dụ 3: Đếm theo điều kiện
Yêu cầu: Đếm xem có bao nhiêu mặt hàng, biết rằng Tên hàng giống nhau chỉ tính 1 mặt hàng.
 

Với cách làm cơ bản ta có thể thêm 1 cột phụ (là cột H) và nhập hàm =COUNTIF($C$3:C3,C3) tại ô G3 sau đó sao chép cho các ô còn lại rồi sử dụng hàm COUNTIF để đếm số lượng các mặt hàng:

Đầu tiên hàm COUNTIF sẽ đếm số lần xuất hiện của mặt hàng tại mỗi dòng trong vùng tính từ dòng đầu tiên đến dòng đang đếm. Điều này rất quan trọng bởi khi tính theo cách này thì giá trị COUNTIF đếm được lần lượt sẽ là 1,2,3…tăng dần theo số lần xuất hiện của điều kiện (criteria) đang xét. Khác với việc chọn Range là cả vùng dữ liệu từ C3:C12, ở đây chỉ xét C3 tăng dần theo các dòng, giá trị điểm đầu C3 trong vùng Range được cố định.

Sau khi dùng hàm COUNTIF chúng ta sẽ được kết quả tại cột G từ G3:G12. Mỗi một giá trị số 1 thể hiện là mặt hàng tương ứng xuất hiện lần thứ 1, nghĩa là chưa bị trùng, các giá trị lớn hơn 1 nghĩa là bị trùng. Khi đó kết quả đếm giá trị không trùng chỉ cần dùng hàm COUNTIF trong cột G với điều kiện là số 1 được kết quả thu được là 4 mặt hàng.
Nếu sử dụng công thức mảng ta không cần phải thêm 1 cột để đếm lần xuất hiện từng mặt hàng mà có thể tính ngay được số lượng mặt hàng với công thức sau:

 
 
 
Hàm COUNTIF để đếm ra số lần xuất hiện các mặt hàng, kết quả của hàm COUNTIF là mảng sau: {1;3;3;3;3;3;3;3;3;3}. Nếu mặt hàng xuất hiện 3 lần trong danh sách nó sẽ tạo ra 3 mục trong mảng với giá trị là 0.3333333(1/3). Kết quả của hàm 1/COUNTIF là mảng ={1;0.3(3);0.3(3);0.3(3); 0.3(3);0.3(3);0.3(3); 0.3(3);0.3(3);0.3(3)}.Hàm SUMPRODUCT sẽ cộng các giá trị trong mảng với điều kiện chỉ tính những ô khác rỗng, tổng của tất cả các số phân số cho mỗi mục riêng lẻ luôn là 1, cho dù có bao nhiêu lần xuất hiện của mục đó trong danh sách.  Kết quả trả về tổng số mặt hàng là 4.
  1. Ví dụ 4: Tính giá trị lớn nhất, nhỏ nhất theo điều kiện
Yêu cầu: Tìm điểm cao nhất, thấp nhất theo từng ngành học
 
Theo suy nghĩ thông thường, ta sẽ tiến hành trích lọc ra theo từng ngành học rồi dùng hàm MIN hoặc MAX để tìm ra điểm thấp nhất, cao nhất hoặc sử dụng hàm DMAX. Nếu sử dụng hàm DMAX chúng ta phải thêm ô tiêu đề cột (cột mà chứa điều kiện) trên các ô chứa điều kiện rồi nhập công thức như sau:
 

Nếu sử dụng công thức mảng ta không cần phải thêm ô tiêu đề cột trên các ô chứa điều kiện (chỉ cần nhập công thức như bên dưới sau đó nhấn tổ hợp phím Ctrl Shift + Enter), sẽ nhanh chóng có được kết quả như hình sau:
 
 
  1. Ví dụ 5: Xếp hạng liên tục
Yêu cầu: xếp hạng các học sinh theo ĐTB
 

Trong Excel, chúng ta sử dụng hàm RANK để tính thứ tự xếp hạng của một phần tử trong dãy. Tuy nhiên, hàm RANK không tạo ra một thứ hạng liên tục. Nếu các bạn để ý sẽ thấy, học sinh thứ 1 và thứ 4 có cùng điểm tổng kết là 9 và sẽ cùng xếp ở vị trí thứ 1, học sinh thứ 2 được 7 theo lý thuyết xếp thứ 2. Tuy nhiên, hàm RANK lại sắp xếp học sinh này ở vị trí thứ 3 mà bỏ qua vị trí thứ 2 (nguyên nhân do vị trí thứ 1 có 2 bạn cùng được xếp). Để giải quyết tình trạng này, chúng ta sẽ sử dụng công thức mảng như sau:
 

Tại ô E4, nhập công thức mảng:
{=SUM(IF(D4<$D$4:$D$7,1/COUNTIF($D$4:$D$7,$D$4:$D$7)))+1} và sao chép cho các ô còn lại. Trong công thức này, điều kiện D4<$D$4:$D$7 sẽ tạo nên thứ tự sắp xếp giảm dần dựa vào ĐTB. Trong trường hợp muốn sắp xếp tăng dần, các bạn sử dụng điều kiện D4>$D$4:$D$7.


--
Tác giả: ThS. Nguyễn Thị Yên (Khoa Lý luận chính trị & Khoa học cơ bản)
Lượt xem: 2591
Xem nhiều
Right Tuyen sinh DH
TSNK
Right - tuyen sinh SDH
Right - tuyen sinh VLVH
Right - NCVH
Right - Thu vien so
Right - middle
Right - NCVH
Right - Thu vien so
Right - middle
Thống kê truy cập
Đang truy cập: 11
Lượt truy cập: 1.742.096