Bài Tập Công Thức Mảng Trong Excel

Để sử dụng không thiếu tính năng vào MS Excel thì tín đồ dùng cần 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 bí quyết mảng. Cách làm mảng hay được call là bí quyết CSE (Ctrl+Shift+Enter), bởi khi dùng công thức thường các 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.

Bạn đang xem: Bài tập công thức mảng trong excel

1. Định nghĩa

– phương pháp mảng là công thức có thể thực hiện các phép tính so với một hoặc những mục vào mảng.

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

– Công thức mảng hoàn toàn có thể trả về nhiều tác dụng hoặc một hiệu quả duy nhất.

Ví dụ:

Bạn có thể tạo một bí quyết mảng trong phạm vi một ô và dùng phương pháp mảng đó để thống kê giám sát cột tốt hàng. Chúng ta cũng có thể đặt công thức mảng vào một ô đối chọi lẻ, rồi giám sát một bé số đơn lẻ. Phương pháp mảng bao hàm nhiều ô được gọi là công thức đa ô, phương pháp mảng trong một ô duy nhất được điện thoại tư vấn là công thức solo ô.

2. Tại sao bọn họ phải dùng cách làm mảng?

Nếu bạn có tay nghề dùng bí quyết trong Excel, chúng ta biết rằng Ms Excel hoàn toàn có thể thực hiện tại một số thao tác làm việc khá phức tạp. Ví dụ, chúng ta cũng có thể tính toán tổng giá cả cho một khoản vay trong ngẫu nhiên số năm nào đó đã biết. Bạn có thể dùng bí quyết mảng để thực hiện các tác vụ phức tạp, ví dụ điển hình như:

– Đếm số cam kết tự cất trong một phạm vi ô.

– Chỉ tính tổng các số thỏa mãn nhu cầu một số đk 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 trong lòng một số lượng giới hạn trên và giới hạn dưới như thế nào đó.

– Tính tổng đầy đủ giá trị thứ n trong phạm vi giá bán trị.

3. Những điểm hạn chế của việc dùng phương pháp mảng

Công thức mảng rất hoàn hảo và tuyệt vời nhất nhưng chúng cũng có thể 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í là với những người dùng Excel dày dạn kinh nghiệm. Hãy lưu giữ nhấn tổ hợp phím này bất cứ lúc nào bạn nhập hay sửa bí quyết mảng.

– Những bạn khác cần sử dụng bảng tính thao tác của bạn có thể họ không hiểu biết nhiều công thức của bạn. Trên thực tế, công thức mảng nhìn bao quát không được phân tích và lý giải trong bảng tính, vày vậy nếu tín đồ khác đề xuất sửa đổi bảng tính làm việc của bạn, thì bạn nên tránh dùng phương pháp mảng hoặc hãy bảo đảm rằng những người dân đó biết rõ mọi bí quyết mảng vào đó với biết cách thay đổi các phương pháp đó nếu như họ cần.

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

Xem thêm:

4. Chăm chú khi dùng cách làm mảng

– sử dụng được tất cả các hàm cơ bạn dạng để triển khai công thức mảng.

– cách làm mảng cũng tương tự như cách làm thường tuy vậy khi thực hiện công thức thay bởi vì chọn tường ô thì lựa chọn cả vùng.

– sau khi viết bí quyết tính toán ngừng thay do nhấn ENTER thì bí quyết mảng yêu cầu nhấn CTRL+SHIFT+ENTER

– bí quyết mảng không tiến hành cho rất nhiều ô vẫn thực hiện tính năng Merge cell( gộp ô).

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

*

Nếu dùng cách làm 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 mặt dưới

Nếu dùng bí quyết mảng: chọn vùng thành chi phí D2:D10 ta nhập phương pháp =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ó cách gọi khác là hàng, nếu mảng dọc hay nói một cách khác là cột=gt1; gt2;…

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

Ví dụ:

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

*

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

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

*

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

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

B1: chọn vùng buộc phải nhập

B2: Viết phương pháp =gt1, gt2,..;gt1′, gt1”,…;… ( dấu “,” nhập quý hiếm theo hàng, vệt “;” nhập quý hiếm theo cột

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

Ví dụ: nhập giá chỉ trị mang đến vùng C1:F3

*

5. Cách áp dụng hằng mảng vào công thức

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

Giải thích:

– Vùng giá chỉ trị: là 1 trong những cột, dòng

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

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

Ví dụ: 1, 2, A1:A5, hoặc 1, 2, sum(A1:A5) sẽ ảnh hưởng lỗi.

Ví dụ: mang lại 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 vẫn 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. Phương pháp đặt thương hiệu hằng số mảng

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

*

Tại Name: nhập thương hiệu hằng mảng

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

B2: phương pháp gọi thương hiệu hằng mảng

+ chọn vùng bắt buộc nhập thương hiệu hằng mảng

+ =Tên hằng mảng, rồi thừa nhận Ctrl+Shift+enter

Ví dụ 1: sinh sản tên hằng là tháng có giá tri tháng 1, tháng 2, mon 3 tiếp đến gọi chuỗi 3 tháng này ra

Ta sinh sản như hình sau:

*

Chọn vùng bao gồm 3 ô còn vào trên sản phẩm =thang rồi nhận Ctrl+shift+enter

*

7. Giải pháp dùng bí quyết mảng

a. Dùng phương pháp mảng cơ bản

Ví dụ : 

Cho bảng tài liệu sau

*

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

A7 =SUM(LEN(A2:A6))

Giải thích:

– thứ 1 hàm Len(A2:A6) trả về mảng chiều nhiều năm của từng chuỗi vào vùng A2:A6 là 9, 19, 13, 9, 16, tiếp nối hàm sum và tính tổng mảng này.

2/ Tìm câu chữ của ô tất cả chuỗi nhiều năm nhất.

A8 =INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1) rồi dìm 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 lâu năm từng chuỗi vào vùng A1:A6, rồi hàm Max chọn ra giá trị lơn duy 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 tất cả chuỗi lâu năm nhất vào vùng A2:A6 được coi là dòng 2

đến phía trên hàm gọn=INDEX(A2:A6,2,1) lúc này hàm Index đang 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 mức vốn 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 mức vốn của Vùng Tính thỏa nhiều Điều Kiện.

Giải thích:

ĐK: là những điều khiếu nại tính

/+: lốt nhân hoặc giấu cộng, sử dụng thể hiện quan hệ giữa những điều kiện, nếu những điều kiện gồm quan hệ “Và” ráng vì thực hiện hàm AND tại chỗ này ta áp dụng toán tử ““, nếu các điều kiện quan hệ nam nữ “Hay/Hoặc” thay do 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ố thành phần 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 tài liệu 1

1/ Tính tổng thành tiền tài 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 bốn là screen và Mouse

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

3/ Đếm xem gồm bao nhiêu vật dụng tư có tên Case

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

4/ kiểm soát xem thành tiền lớn số 1 của trang bị tư mang tên Case

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

Ví dụ 2: Kết hợp bí quyết mảng cù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 mã sau:

*

Xem clip Hướng dẫn:

B1: chọn 2 ô còn trống lầm ô mẫu thay mặt cho Tên vật tư, với Tháng bán

B2: trên ô mầu xoàn của bảng thống kê mẫu mã viết hàm sau

=SUM(IF((B3:B38=ô mẫu mã tên thiết bị tư)*(MONTH(C3:C38)=ô chủng loại tháng bán),F3:F38))

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

Tại Row đầu vào cell: chọn ô mẫu mã 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 bác 5————————