Mẹo về Công thức mảng trong excel là gì Chi Tiết
Dương Khoa Vũ đang tìm kiếm từ khóa Công thức mảng trong excel là gì được Update vào lúc : 2022-05-20 17:04:03 . Với phương châm chia sẻ Kinh Nghiệm về trong nội dung bài viết một cách Chi Tiết 2022. Nếu sau khi đọc nội dung bài viết vẫn ko hiểu thì hoàn toàn có thể lại phản hồi ở cuối bài để Admin lý giải và hướng dẫn lại nha.
Bạn hoàn toàn có thể tải file thực hành tại đây.
Nội dung chính- Doanh thu thông thườngCông thức mảngTính phần trăm hoàn thành xong dự ánTính tổng từ 2 đến 100Tính rũi roSố tương thíchTổng những chữ sốLọc tên trùng nhauCông thức mảng nâng caoVideo hướng dẫn1. Định nghĩa2. Tại sao tất cả chúng ta phải dùng công thức mảng?3. Các điểm hạn chế của việc dùng công thức mảng4. Chú ý khi sử dụng công thức mảng5. MẢNG HẰNG SỐ5. cách sử dụng hằng mảng trong công thức6. cách đặt tên hằng số mảng7. cách dùng công thức mảng B. Dùng công thức mảng để thống kêCó liên quanVideo liên quan
Giả sử mình có những 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á đựng tính thành tiền, sau đó lại dùng hàm SUM để tính tổng tiền của những sản phẩm. Đối với công thức mảng bạn hoàn toàn hoàn toàn có thể nhân mảng với nhau bằng phương pháp nhập vào công thức =SUM(B3:B7*C3:C7) sau đó ấn Ctrl + Shift + Enter.
Để sử dụng đầy đủ tính năng trong MS Excel thì người tiêu dùng nên phải biết phương pháp sử dụng công thức mảng. Công thức hoàn toàn có thể thực hiện những 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 sử dụng công thức thường bạn chỉ nhấn Enter còn khi sử 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 hoàn toàn 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 hoàn toàn có thể trả về nhiều kết quả hoặc một kết quả duy nhất.
Ví dụ:
Bạn hoàn toà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 hoàn toàn 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 gồm có 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 tất cả chúng ta phải dùng công thức mảng?
Nếu bạn có kinh nghiệm tay nghề dùng công thức trong Excel, bạn biết rằng Ms Excel hoàn toàn có thể thực hiện một số trong những thao tác khá phức tạp. Ví dụ, bạn hoàn toàn có thể tính toán tổng ngân sách cho một khoản vay trong bất kỳ số năm nào đó đã biết. Bạn hoàn toàn có thể dùng công thức mảng để thực hiện những tác vụ phức tạp, ví dụ như:
– Đếm số ký tự chứa trong một phạm vi ô.
– Chỉ tính tổng những số đáp ứng một số trong những đ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 số lượng giới hạn trên và số lượng 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 luôn có thể có một vài hạn chế:
– Đôi khi bạn hoàn toàn có thể quên nhấn Ctrl+Shift+Enter. Điều này hoàn toàn có thể xảy ra thậm chí với những người dân tiêu dùng Excel dày dạn kinh nghiệm. Hãy nhớ nhấn tổ hợp phím này bất kể lúc 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 thao tác của bạn hoàn toà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 lý giải trong bảng tính, vì vậy nếu người khác cần sửa đổi bảng tính thao tá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 dân đó biết rõ mọi công thức mảng trong đó và biết cách thay đổi những 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 hoàn toàn có thể làm chậm quá trình tính toán.
4. Chú ý khi sử dụng công thức mảng
– Sử dụng được tất cả những 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 hiệu suất cao 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ô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 phía 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ềuB1: 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, những gt1, gt2 hoàn toàn 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ử, hoàn toàn có thể sử dụng toán từ khác ví 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 những 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 đã tàng 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ó mứ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ảnVí dụ :
Cho bảng tài 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 đó đó là chiều dài từng chuỗi trong vùng A1:A6, rồi hàm Max lựa 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à loại 2
đến đây hàm gọn=INDEX(A2:A6,2,1) thời điểm hiện nay 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à những điều kiện tính
/+: dấu nhân hoặc dấu cộng, dùng thể hiện quan hệ Một trong những điều kiện, nếu những đ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 những đ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 tài liệu sau
Bảng tài 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ư mang tên Case
=SUM(IF(B3:B38=”Case”,1))
4/ Kiểm tra xem thành tiền lớn số 1 của Vật tư mang 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 tài liệu: Bảng tài 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
[embed]https://www.youtube.com/watch?v=b24MXrZNPH4[/embed]
————————Kết thức bài 5————————
Có liên quan
