BÀI 5: CÔNG THỨC MẢNG MS EXCEL

Để sử dụng đầy đủ tính năng trong MS Excel thì người dùng cần phải biết cách sử dụng công thức mảng. Công thức có thể thực hiện các phép tính mà bạn không thể làm nếu không sử dụng công thức mảng. Công thức mảng thường được gọi là công thức CSE (Ctrl+Shift+Enter), bởi khi dùng công thức thường bạn chỉ nhấn Enter còn khi dùng công thức mảng bạn cần phải nhấn Ctrl+Shift+Enter để hoàn tất công thức.

1. Định nghĩa

– Công thức mảng là công thức có thể thực hiện nhiều phép tính đối với một hoặc nhiều mục trong mảng.

– Mảng là một hàng giá trị, một cột giá trị hoặc là tập hợp gồm nhiều hàng và cột giá trị.

– Công thức mảng có thể trả về nhiều kết quả hoặc một kết quả duy nhất.

Ví dụ:

Bạn có thể tạo một công thức mảng trong phạm vi một ô và dùng công thức mảng đó để tính toán cột hay hàng. Bạn cũng có thể đặt công thức mảng vào một ô đơn lẻ, rồi tính toán một con số đơn lẻ. Công thức mảng bao gồm nhiều ô được gọi là công thức đa ô, công thức mảng trong một ô duy nhất được gọi là công thức đơn ô.

2. Tại sao chúng ta phải dùng công thức mảng?

Nếu bạn có kinh nghiệm dùng công thức trong Excel, bạn biết rằng Ms Excel có thể thực hiện một số thao tác khá phức tạp. Ví dụ, bạn có thể tính toán tổng chi phí cho một khoản vay trong bất kỳ số năm nào đó đã biết. Bạn có thể dùng công thức mảng để thực hiện các tác vụ phức tạp, chẳng hạn như:

– Đếm số ký tự chứa trong một phạm vi ô.

– Chỉ tính tổng các số đáp ứng một số điều kiện nhất định.

Ví dụ: Tính các giá trị thấp nhất trong một phạm vi hoặc các số nằm giữa một giới hạn trên và giới hạn dưới nào đó.

– Tính tổng mọi giá trị thứ n trong phạm vi giá trị.

3. Các điểm hạn chế của việc dùng công thức mảng

Công thức mảng rất tuyệt vời nhưng chúng cũng có một vài hạn chế:

– Đôi khi bạn có thể quên nhấn Ctrl+Shift+Enter. Điều này có thể xảy ra thậm chí với những người dùng Excel dày dạn kinh nghiệm. Hãy nhớ nhấn tổ hợp phím này bất cứ khi nào bạn nhập hay sửa công thức mảng.

– Những người khác dùng bảng tính làm việc của bạn có thể họ không hiểu công thức của bạn. Trên thực tế, công thức mảng nhìn chung không được giải thích trong bảng tính, vì vậy nếu người khác cần sửa đổi bảng tính làm việc của bạn, thì bạn nên tránh dùng công thức mảng hoặc hãy bảo đảm rằng những người đó biết rõ mọi công thức mảng trong đó và biết cách thay đổi các công thức đó nếu họ cần.

– Tùy thuộc vào tốc độ xử lý và bộ nhớ của máy tính, công thức mảng lớn có thể làm chậm quá trình tính toán.

4. Chú ý khi dùng công thức mảng

– Sử dụng được tất cả các hàm cơ bản để thực hiện công thức mảng.

– Công thức mảng cũng tương tự như công thức thường nhưng khi thực hiện công thức thay vì chọn tường ô thì chọn cả vùng.

– Sau khi viết công thức tính toán xong thay vì nhấn ENTER thì công thức mảng phải nhấn CTRL+SHIFT+ENTER

– Công thức mảng không thực hiện cho những ô đã thực hiện chức năng Merge cell( gộp ô).

Ví dụ: cho bảng dữ liệu sau, tính cột thành tiền

Nếu dùng công thức thường tại ô D2 ta viết công thức =B2*C2, rồi sao chép công thức xuống bên dưới

Nếu dùng công thức mảng: Chọn vùng thành tiền D2:D10 ta nhập công thức =B2:B10*C2:C10, rồi nhấn tổ hợp phím CTRL+SHIFT+ENTER

5. MẢNG HẰNG SỐ

A. nhập Mảng hằng số 1 chiều( ngang / dọc )

B1: Chọn vùng cần nhập

B2: Viết công thức ={gt1, gt2,..} mảng ngang hay còn gọi là hàng, nếu mảng dọc hay còn gọi là cột={gt1; gt2;…}

B3: Nhấn tổ hợp phím CRTL+SHIFT+ENTER

Ví dụ:

Chọn vùng A1:E1, rồi viêt ={1,2,3,4,5}, nhấn Ctrl+shift+enter

Nhập mảng 1 chiều theo dòng/hàng

Chọn vùng C1:C5, rồi viêt ={1;2;3;4;5}, nhấn Ctrl+shift+enter

Nhập mảng 1 chiều theo cột / dọc

b. nhập Mảng hằng số 2 chiều

B1: Chọn vùng cần nhập

B2: Viết công thức ={gt1, gt2,..;gt1′, gt1”,…;…} ( dấu “,” nhập giá trị theo hàng, dấu “;” nhập giá trị theo cột

B3: Nhấn tổ hợp phím CRTL+SHIFT+ENTER

Ví dụ: nhập giá trị cho vùng C1:F3

5. cách sử dụng hằng mảng trong công thức

=TÊN HÀM(VÙNG GIÁ TRỊ*{GT1, GT2, GT3,..})

Giải thích:

– Vùng giá trị: là một cột, dòng

– {gt1, gt2,…}: là hằng mảng, các gt1, gt2 có thể cách nhau dấu “,” hoặc dấu “;”. Trong hằng mảng không được sử dụng hàm, một mảng khác mà chỉ là số, chuỗi mà thôi.

– * : là toán tử, có thể sử dụng toán từ khác như: +, -, /,…

Ví dụ: {1, 2, A1:A5}, hoặc  {1, 2, sum(A1:A5)}  sẽ bị lỗi.

Ví dụ: cho vùng A1:E1 các giá trị lần lượt là: 2,3,4,5,6

A4 ta viết công thức sau =Sum(A1:E1*{1,2,3,4,5}) nhấn ctrl+shift+enter kết quả 70

Giải thích:

 

 

 

1. Hàm

2. Mảng đã lưu trữ

3. Toán tử

4. Hằng số mảng

Tương đương công thức sau: =SUM(A11,B12,C13,D14,E1*5)

6. cách đặt tên hằng số mảng

B1: Vào Ribbon(Menu) / FORMULAS / Name Manager / hộp thoại / New / hộp thoại

Tại Name: nhập tên hằng mảng

Tại Refers to: ={gt1, gt2,…}/ OK

B2: Cách gọi Tên hằng mảng

+ Chọn vùng cần nhập tên hằng mảng

+ =Tên hằng mảng, rồi nhấn Ctrl+Shift+enter

Ví dụ 1: Tạo tên hằng là tháng có giá tri tháng 1, tháng 2, tháng 3 sau đó gọi chuỗi 3 tháng này ra

Ta tạo như hình sau:

Chọn vùng gồm 3 ô còn trong trên hàng =thang rồi nhấn Ctrl+shift+enter

7. cách dùng công thức mảng

a. Dùng công thức mảng cơ bản

Ví dụ : 

Cho bảng dữ liệu sau

1/ Tính tổng các ký tự trong vùng A2:A6

A7 =SUM(LEN(A2:A6))

Giải thích:

– Trước hết hàm Len(A2:A6) trả về mảng chiều dài của từng chuỗi trong vùng A2:A6 là {9, 19, 13, 9, 16}, sau đó hàm sum sẽ tính tổng mảng này.

2/ Tìm nội dung của ô có chuỗi dài nhất.

A8 =INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1) rồi nhấn ctrl+shift+enter kết quả ” bunch of cells that”

Giải thích:

– MAX(LEN(A2:A6)): Len(A2:A6) trả về mảng hằng số {9, 19, 13, 9, 16} chính là chiều dài từng chuỗi trong vùng A1:A6, rồi hàm Max chọn ra giá trị lơn nhất 19, kết quả MAX(LEN(A2:A6))=19

– MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0): hàm này trả về dòng có chuỗi dài nhất trong vùng A2:A6 là dòng 2

đến đây hàm gọn=INDEX(A2:A6,2,1) lúc này hàm Index sẽ lấy chuỗi trong vùng A2:A6 tại dòng 2, cột 1

=> kết quả: ” Lunch of cells that”

 B. Dùng công thức mảng để thống kê

Công thức tổng quát

=HÀM(IF(ĐIỀU KIỆN, GIÁ TRỊ/VÙNG GIÁ TRỊ))

1/Công thức tính tổng

CP1 =SUM(IF(ĐIỀU KIỆN, VÙNG TÍNH)) hoặc =SUM(IF(ĐIỀU KIỆN, VÙNG TÍNH, 0)) Tính tổng giá trị của Vùng Tính thỏa Điều Kiện.

CP2 

=SUM(IF((ĐK1 /+ ĐK2 /+ ĐK3 …), VÙNG TÍNH)) hoặc =SUM(IF((ĐK1 /+ ĐK2 /+ ĐK3 …), VÙNG TÍNH, 0)) Tính tổng giá trị của Vùng Tính thỏa nhiều Điều Kiện.

Giải thích:

ĐK: là các điều kiện tính

/+: dấu nhân hoặc dấu cộng, dùng thể hiện mối quan hệ giữa các điều kiện, nếu các điều kiện có quan hệ “Và” thay vì sử dụng hàm AND ở đây ta sử dụng toán tử ““, nếu các điều kiện quan hệ “Hay/Hoặc” thay vì dùng hàm OR ta dùng toán tử “+“

2/Công thức đếm

CP1 =SUM(IF(ĐIỀU KIỆN, 1)) hoặc =COUNT(IF(ĐIỀU KIỆN, 1, 0)): Đếm số phần tử thỏa Điều Kiện

CP2

=SUM(IF((ĐK1 /+ ĐK2 /+ ĐK3 …), 1)) hoặc =COUNT(IF((ĐK1 /+ ĐK2 /+ ĐK3 …), 1, 0)): Đếm số phần tử thỏa  nhiều Điều Kiện

Ví dụ 1: Cho bảng dữ liệu sau

Bảng dữ liệu 1

1/ Tính tổng thành tiền của tên vật tư là Màn hình

=SUM(IF(B3:B38=”Màn hình”,F3:F38))

2/ Tính tổng thành tiền của tên vật tư là Màn hình và Mouse

=SUM(IF((B3:B38=”Màn hình”)+(B3:B38=”Mouse”),F3:F38))

3/ Đếm xem có bao nhiêu Vật tư có tên Case

=SUM(IF(B3:B38=”Case”,1))

4/ Kiểm tra xem thành tiền lớn nhất của Vật tư có tên Case

=MAX(IF(B3:B38=”Case”,F3:F38))

Ví dụ 2: Kết hợp công thức mảng với Data Table để lập bảng thống kê

Lấy lại bảng dữ liệu: Bảng dữ liệu 1

Hãy lập bảng thống kê theo mẫu sau:

Xem Video Hướng dẫn:

B1: Chọn 2 ô còn trống lầm ô mẫu đại diện cho Tên Vật tư, và Tháng bán

B2: Tại ô mầu vàng của bảng thống kê mẫu viết hàm sau

=SUM(IF((B3:B38=ô mẫu tên vật tư)*(MONTH(C3:C38)=ô mẫu tháng bán),F3:F38))

B3: Chọn bảng thống kê vào DATA / WHAT IF ANALYSIS / DATA TABLE / HỘP THOẠI

Tại Row Input cell: chọn ô mẫu tháng bán

Tại Column input cell: chọn ô mẫu Tên Vật tư

=>OK

————————Kết thức bài 5————————

Dữ liệu thực hành phần nâng cao: Link download