Tóm Tắt
Cách dùng công thức mảng trong Excel [Kèm ví dụ minh hoạ]
SaveSavedRemoved
0
VIẾT NGAY, DỄ LẮM NEK!
MarsURL.com: Lấy URL ảnh trong 3 giây, dung lượng đến 20MB và tồn tại trong 3 NGÀY.
TẢI ẢNH NGAY
MarsReels: Tải Photo/Video/Reels/Stories/IGTV Instagram trong 3 GIÂY.
TRUY CẬP NGAY
MarsPaste.com: Giải pháp nhanh nhất để chia sẻ văn bản/code online.
Xem ngay
Hãy san sẻ thưởng thức về một mẫu sản phẩm hoặc dịch vụ mà bạn đang cảm thấy tốt nhé ! tin tức nay sẽ giúp ích cho rất nhiều người ” đi sau ” đấy. MarsURL. com : Lấy URL ảnh trong 3 giây, dung tích đến 20MB và sống sót trong 3 NGÀY.MarsReels : Tải Photo / Video / Reels / Stories / IGTV Instagram trong 3 GIÂY.MarsPaste.com : Giải pháp nhanh nhất để san sẻ văn bản / code trực tuyến .
Bạn có biết: Trọn bộ 4 khoá học tin học văn phòng 2021 tại Unica (Đang giảm đến 77%)
Mã giảm giá Unica 40 % cho người mua mới
499.000 đ 2.200.000 đ
Lấy MãLấy Mã
Bạn có biết : Trọn bộ 4 khoá học tin học văn phòng 2021 tại Unica ( Đang giảm đến 77 % )
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. Khi nắm rõ bản chất và cách sử dung công thức mảng trong Excel, các bạn sẽ tạo ra được các công thức để thực hiên các phép tính khó cho dù Excel không hổ trợ hoặc các hàm có sẵn trong Excel không thể thực hiện được.
Trong bài viết này, chúng tôi sẽ trình làng với những bạn khái niệm về công thức mảng và những ví dụ đơn cử để những bạn nắm được về tính năng khá can đảm và mạnh mẽ này của Excel .
Xem thêm bài viết về Các hàm cơ bản trong Excel mình đã san sẻ trong thời hạn gần đây .
Cách sử dụng công thức mảng trong Excel
Tìm hiểu công thức mảng
Một mảng là một tập hợp những thành phần 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 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:A5; B2:E2) 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).
Công thức mảng không cần tàng trữ những số liệu trong quy trình giám sát tại những ô mà thực thi giám sát và tàng trữ trong bộ nhớ máy tính. Khi thống kê giám sát xong, Excel sẽ trả về hiệu quả là một ô hoặc một vùng dữ liệu .
Đặc điểm của công thức mảng
– Để 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ụ:
{=SUM(LEN($E$12:$H$20))}
– Bạn không hề chỉnh sửa nội dung của 1 ô trong vùng công thức mảng. Bên cạnh đó, bạn không hề tạo thêm cột, dòng mới xen giữa vùng có công thức mảng .
Excel báo lỗi nếu bạn thêm mới dòng, cột trong vùng dữ liệu chứa công thức mảng. Để chuyển tất cả các ô trong công thức mảng, các bạn chọn cả vùng, nhấn F2 để chỉnh sửa và nhấn tổ hợp phím Ctrl + Enter.
Các thao tác với công thức mảng
a. Chọn vùng chứa công thức mảng
Nếu những bạn đã biết vùng chứa công thức mảng của mình, những bạn hoàn toàn có thể quét và chọn vùng chứa công thức mảng bằng tay .
Ngoài ra, đơn giản hơn, các bạn có thể chọn 1 ô bất kỳ trong công thức mảng sau đó nhấn F5 hoặc Ctrl + G > Special…. > Current Array.
b. Chỉnh sửa một công thức mảng
Để chỉnh sửa một công thức mảng, các bạn cần chọn tất cả các ô trong công thức mảng, sau đó nhấn phím F2 hoặc click vào thanh công thức và chỉnh sửa như thông thường. Sau khi chỉnh sửa xong, các bạn phải nhấn tổ hợp Ctrl + Shift + Enter để kết thúc.
c. Mở rộng hoặc thu hẹp công thức mảng
Để biến hóa vùng vận dụng công thức mảng, những bạn triển khai như sau :
- Chọn vùng chứa công thức mảng
- Nhấn F2 để hiệu chỉnh công thức
- Nhấn Ctrl + Enter để chuyển về dạng công thức thường cho vùng chọn.
- Chọn lại vùng áp dụng công thức mảng (mở rộng hoặc thu hẹp vùng chọn so với ban đầu)
- Nhấn F2 để hiệu chỉnh công thức
- Nhấn Ctrl + Shift + Enter để kế thúc chỉnh sửa
Ví dụ với công thức mảng trả về kết quả tại một ô
a. Đếm số ký tự trong một vùng dữ liệu
Như đã giới thiệu tại bài viết này, nếu chúng ta chỉ sử dụng hàm LEN thì chúng ta phải tính số lý tự cho từng ô trong vùng dữ liệu và tổng cộng lại để ra tổng số ký tự trong vùng
Tuy nhiên, ta dùng công thức mảng kết hợp hàm LEN và hàm SUM. Công thức mảng sử dụng hàm LEN để tạo ra một mảng trong bộ nhớ gồm số ký tự trong mỗi ô của dãy rồi sau đó, tính tổng của dãy.
b. Đếm/Tính tổng theo điều kiện
Trong Excel, chúng ta có thể sử dụng các hàm SUMIF, SUMIFS, COUNTIF, COUNTIFS để tính tổng, đếm theo một điều kiện nào đó.
Trong ví dụ trên, tất cả chúng ta cần đếm xem có bao nhiêu bạn học viên mà tên chứa 4 ký tự .
Tại ô C14, chúng ta sử dụng công thức mảng:
{=SUM((LEN(B3:B12)=4)*1)}
Trong đó:
+ Hàm (LEN(B3:B12)=4) sẽ tạo ra 1 mảng chứa các giá trị True (nếu tên có 4 ký tự) và False (nếu tên có độ dài khác 4 ký tự). Với điều kiện B3:B12, hàm (LEN(B3:B12)=4) sẽ trả về mảng:
{FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
+ Phép tính *1 để nhân với từng giá trị trong mảng trên, nếu True * 1 = 1 và False * 1 = 0. Sau phép tính *1, chúng ta nhận được mảng {0;1;1;1;0;0;0;0;0;0}
+ Hàm Sum có tác dụng cộng các giá trị trong mảng vừa được tạo ra và chúng ta nhận được kết quả là 3 tương ứng với các trường hợp tên là “Bích”, “Bình”, “Bình”.
c. Tính tổng/ trung bình cộng của n giá trị lớn nhất/ nhỏ nhất
Trong ví dụ sau, tất cả chúng ta sẽ tính điểm trung bình của 03 học viên có điểm toán cao nhất. Đó là những học viên có số thứ tự 3, 8, 9 với điểm thi lần lượt là 9.5 ; 9.9 ; 8.8 .
Tại ô C14, các bạn sử dụng công thức mảng:
{=AVERAGE(LARGE(C3:C12;ROW(INDIRECT("1:3"))))}
và nhấn Ctrl + Shift + Enter để kết thúc công thức. Trong đó:
+ Hàm ROW(INDIRECT(“1:3”)) sẽ tạo ra một mảng gồm các số nguyên từ 1-3 như sau {1;2;3} trong bộ nhớ máy.
+ Hàm LARGE sẽ trả về giá trị lớn thứ k trong vùng dữ liệu được chọn. Ở đâ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 C3:C12
+ Hàm AVERAGE sẽ trả về trung bình cộng của 03 điểm toán lớn nhất.
Lưu ý: Trong ví dụ trên, các bạn sử dụng hàm SMALL thay cho LARGE để tính cho các học sinh có điểm toán thấp nhất.
d. Xếp hạng liên tục
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. Ví dụ sau sẽ sắp xếp các học sinh theo thứ tự Điểm tổng kết.
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à 8 và sẽ cùng xếp ở vị trí thứ 3 theo lý thuyết. Tuy nhiên, hàm RANK lại sắp xếp 2 học sinh này ở vị trí thứ 4 mà bỏ qua vị trí thứ 3 (nguyên nhân do vị trí thứ 2 có 2 bạn cùng được xếp).
Để xử lý thực trạng này, tất cả chúng ta sẽ sử dụng công thức mảng như sau :
Tại ô F3, các bạn nhập công thức mảng:
{=SUM(IF(D3<$D$3:$D$7;1/COUNTIF($D$3:$D$7;$D$3:$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 D3<$D$3:$D$7 sẽ tạo nên thứ tự sắp xếp giảm dần dựa vào Điểm tổng kết. 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 D3>$D$3:$D$7.
Ví dụ với công thức mảng trả về kết quả tại vùng dữ liệu
Một trong những vận dụng lớn nhất của công thức mảng là tạo dương lịch cho năm bất kể. Trình tự những bước triển khai như sau :
– Bước 1: Điền chuỗi n[i] (i = 1..a*b) vào bảng Row[1..a] Columns[1..b]
– Bước 2: Điền ngày tháng vào (do ngày tháng có công thức cộng trừ luôn)
– Bước 3: So sánh với tháng hiện tại để hiện thông tin phù hợp
File ví dụ, những bạn hoàn toàn có thể tải về trong những link dưới đây .
Lời kết
Công thức mảng là một trong những nội dung khó và có nhiều vận dụng trong việc làm khi phải giải quyết và xử lý những nhu yếu phức tạp trong Excel. Hi vọng rằng với bài viết này, những bạn hoàn toàn có thể hiểu và thuận tiện sử dụng khi thiết yếu trong việc làm và học tập .
Nếu như có bất kể yếu tố nào khác trong khi sử dụng và thực thi như hướng dẫn trên thì bạn hoàn toàn có thể để lại phản hồi trong phần dưới đây .
Chúc bạn thành công!
4.2 / 5 - ( 4 bầu chọn )
Source: https://final-blade.com
Category: Kiến thức Internet
