Tạo mảng 2 chiều bị lỗi
- Thread starter loci
- Ngày gửi
khoavu87
Vũ Trần Khoa
- Tham gia
- 5/3/09
- Bài viết
- 1,311
- Được thích
- 1,769
- Nghề nghiệp
- Kỹ Sư Xây dựng cầu đường
bạn đọc cái này thì ok nè
1. Các khái niệm về công thức mả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ác mảng dữ liệu trong công thức. Khi hiểu rõ được khái niệm này sẽ giúp chúng ta tạo ra được các công thức thực hiện các phép tính khó một cách kỳ diệu. Bài viết này sẽ giới thiệu các khái niệm về các mảng số liệu mà bất kỳ ai nếu muốn trở thành chuyên gia sử dụng công thức trong Excel đều phải biết về nó, ngoài ra trong bài cũng trình bày các ví dụ về công thức mảng rất hữu ích.
Nếu bạn đã từng lập trình trên bất kỳ ngôn ngữ lập trình nào thì chắc bạn cũng đã nghe đến khái niệm mảng (array). Một array đơn thuần chỉ là một tập hợp các phần tử có quan hệ hay độc lập với nhau. Trong Excel, một array có thể là array một chiều hoặc array hai chiều. Chiều của array ở đây chính là chỉ các dòng và cột trong array. Ví dụ như array một chiều thì có thể hiểu đó là một vùng (range) số liệu trên bảng tính mà vùng này sẽ có một dòng (khi array nằm ngang) hoặc một cột (array nằm dọc). Một array hai chiều có thể hiểu đó là một vùng số liệu trên bảng tính (có dạng hình chữ nhật) bao gồm nhiều dòng và nhiều cột. Excel không hỗ trợ array 3-chiều (VBA thì hỗ trợ).
Công thức mảng không cần lưu trữ các số liệu trong quá trình tính toán trong các ô (cell), mà Excel sẽ xử lý các mảng số liệu này trong bộ nhớ máy tính. Sau đó các công thức mảng sẽ lấy kết quả tính toán trả về trên bảng tính. Một công thức mảng có thể trả về kết quả là nhiều ô (range) hay chỉ một ô.
Công thức mảng trả kết quả về một vùng nhiều ô
Hình minh hoạ bên dưới trình bay ví dụ đơn giản về việc tính toán cột Total (Tổng doanh thu của một loại sản phầm), thông thường để tính cột Total (cột D) này bạn sẽ nhập vào công thức sau:
Tại ô D2 nhập vào công thức =B2*C2 sau đó chép xuống D3
7
Hình 1: Tính cột [Total] = [Units Sold] * [Unit Price]
Để tính cho cột Total ở đây chúng ta dùng tới 6 công thức. Ngoài cách này chúng ta có thể dùng 1 công thức mảng để tính ra kết quả cho cả cột Total và lưu kết quả trả về tại D27.
Để tạo một công thức mảng tính toán cho trường hợp này hãy làm theo các bước sau:
7 đã có kết quả tính toán và khi vào xem thì sẽ có công thức như sau:
{=B2:B7*C2:C7}
Lưu ý, Excel sẽ đặt cặp ngoặc móc bao quanh công thức để chỉ đây là công thức mảng.
Công thức mảng trên khi tính toán nó sử dụng dữ liệu từ hai mảng đó là mảng chứa số liệu về [Units Sold] được lưu tại vùng địa chỉ B2:B7 và mảng chứa số liệu về [Unit Price] được lưu tại vùng địa chỉ C2:C7 trong bảng tính.
Vì ứng với mỗi mặt hàng sẽ có một kết quả Total khác nhau, nên đó là lý do chúng ta phải chọn trước vùng địa chỉ D27 để lưu trữ kết quả trả về của công thức mảng.
Công thức mảng này trả về kết quả hoàn toàn chính xác cũng giống như kết quả trả về từ 6 công thức riêng lẽ sau:
=B2*C2
=B3*C3
=B4*C4
=B5*C5
=B6*C6
=B7*C7
Sử dụng công thức mảng thay cho công thức đơn có một số ưu điểm như:
Bây giờ chúng ta cần tính Tổng doanh thu của tất cả các sản phẩm, tuy nhiên yêu cầu là tính toán căn cứ vào cột [Units Sold] và [Unit Price] bằng công thức mảng (nghĩa là không tính toán thông qua cột Total). Khi đó chúng ta có thể dùng công thức mảng sau:
Tại ô D8 bạn nhập vào công thức: =SUM(B2:B7*C2:C7) và kết thúc bằng tổ hợp phím Ctrl+Shift+Enter. Khi đó công thức trong ô D8 có dạng sau {=SUM(B2:B7*C2:C7)}
Công thức mảng này trả về kết quả tính toán tổng doanh thu cho tất cả các sản phẩm trong một ô D8. Công thức này cũng tính toán căn cứ vào 2 mảng số liệu được lưu trong vùng B2:B7 và C2:C7, trong quá trình tính toán công thức mảng sẽ tạo ra trong bộ nhớ một mảng số liệu mới chứa kết quả của phép nhân các cặp số liệu, sau đó hàm SUM được dùng để cộng tất cả các phần tử trong mảng mới này và trả về một giá trị duy nhất lưu vào ô D8.
Trong trường hợp này chúng ta cũng có thể dùng công thức thông thường là hàm SUMPRODUCT như sau:
Tại ô D8 nhập vào công thức =SUMPRODUCT(B2:B7,C2:C7) sau đó Enter.
Mảng một chiều
Ta có thể xem mảng một chiều là một hàng (mảng ngang) hay một cột (mảng dọc). Các phần tử trong mảng một chiều (mảng ngang) được cách biệt nhau bằng một dấu phẩy, và trong mảng dọc được cách nhau bằng dấu chấm phẩy.
Ví dụ: {1,2,3,4,5} (mảng ngang) và {10;20;30;40;50} (mảng dọc)
Hay {"Sun","Mon","Tue","Wed","Thu","Fri","Sat"} (phần tử là Text dạng chuỗi)
Để hiển thị một mảng một chiều 5 phần tử theo dạng ngang hay dọc, ta cần 5 ô liên tục trên cùng một hàng hay một cột
Ví dụ: Tạo mảng 1 chiều
Hình 2. Mảng một chiều ngang và dọc
Các phần tử trong mảng sẽ lần lượt được nhập vào 5 ô liên tục theo thứ tự trong mảng. Nếu trong mảng chỉ có 5 phần tử nhưng ta chọn nhiều hơn 5 ô để nhập mảng thì từ ô thứ 6 sẽ cho giá trị #N/A
Hình 3. Mảng lỗi
Mảng hai chiều
Mảng hai chiều là một hình chữ nhật bao gồm nhiều hàng và nhiều cột. Tương tự như mảng một chiều, ta sử dụng các dấu phẩy để ngăn cách các phần tử trong cùng một hàng và dấu chấm phẩy để ngăn cách các phần tử trong cùng một cột.
Ví dụ : Tạo mảng 2 chiều sau {1,2,3,4;5,6,7,8;9,10,11,12}
Để hiển thị mảng này trên bảng tính, ta chọn vùng A13, nhập :
={1,2,3,4;5,6,7,8;9,10,11,12} và nhấn Ctrl+Shift+Enter
Hình 4. Tạo mảng 2 chiều
Cũng vậy, nếu bạn nhập một mảng vào một dãy vốn có các ô nhiều hơn các phần tử mảng, Excel sẽ hiển thị #N/A trong các ô trống còn lại.
Hình 5. Mảng lỗi
Hằng mảng
Bạn có thể tạo một hằng mảng, đặt cho nó một cái tên, sau đó sử dụng mảng được đặt tên này cho công thức. Ví dụ: Ta có hằng mảng như sau: {1,0,1,0,1}. Công thức sau sử dụng hàm SUM với hằng mảng đứng trước là đối số của nó. Công thức trả về tổng của các giá trị trong mảng (ở đây là 3). Công thức này sử dụng mảng là đối số nhưng không phải là công thức mảng: =SUM({1,0,1,0,1}) và công thức này có cùng kết quả với công thức sau =SUM(1,0,1,0,1). Vào thời điểm này, có thể bạn chưa thấy ưu điểm của việc sử dụng hằng mảng.
Công thức sau sử dụng hai hằng mảng: =SUM({1,2,3,4}*{5,6,7,8}). Công thức này sẽ tạo ra một mảng mới trong bộ nhớ máy tính như sau :{5,12,21,32} và sau đó mảng này lại là đối số cho hàm SUM và trả về kết quả là 70. Bạn sẽ thấy công thức trên tương tự như công thức sau : =SUM(1*5,2*6,3*7,4*8) không là công thức mảng.
Một công thức mảng có thể tính tóan trên các mảng số liệu lưu trữ trên bảng tính và hàng mảng. Ví dụ như công thức =SUM((A11*{1,2,3,4})), công thức này tương đương với công thức =SUM(A1*1,B1*2,C1*3,D1*4). (A12 là mảng ngang lưu trữ trên bảng tính).
Một hằng mảng sẽ không chứa các công thức, các hàm, các giá trị có chứa dấu dollar, dấu phẩy, chấm phẩy... Sau đây là một hằng mảng không hợp lệ: {SUM(3,2),$56,12,5%}
1. Các khái niệm về công thức mả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ác mảng dữ liệu trong công thức. Khi hiểu rõ được khái niệm này sẽ giúp chúng ta tạo ra được các công thức thực hiện các phép tính khó một cách kỳ diệu. Bài viết này sẽ giới thiệu các khái niệm về các mảng số liệu mà bất kỳ ai nếu muốn trở thành chuyên gia sử dụng công thức trong Excel đều phải biết về nó, ngoài ra trong bài cũng trình bày các ví dụ về công thức mảng rất hữu ích.
Nếu bạn đã từng lập trình trên bất kỳ ngôn ngữ lập trình nào thì chắc bạn cũng đã nghe đến khái niệm mảng (array). Một array đơn thuần chỉ là một tập hợp các phần tử có quan hệ hay độc lập với nhau. Trong Excel, một array có thể là array một chiều hoặc array hai chiều. Chiều của array ở đây chính là chỉ các dòng và cột trong array. Ví dụ như array một chiều thì có thể hiểu đó là một vùng (range) số liệu trên bảng tính mà vùng này sẽ có một dòng (khi array nằm ngang) hoặc một cột (array nằm dọc). Một array hai chiều có thể hiểu đó là một vùng số liệu trên bảng tính (có dạng hình chữ nhật) bao gồm nhiều dòng và nhiều cột. Excel không hỗ trợ array 3-chiều (VBA thì hỗ trợ).
Công thức mảng không cần lưu trữ các số liệu trong quá trình tính toán trong các ô (cell), mà Excel sẽ xử lý các mảng số liệu này trong bộ nhớ máy tính. Sau đó các công thức mảng sẽ lấy kết quả tính toán trả về trên bảng tính. Một công thức mảng có thể trả về kết quả là nhiều ô (range) hay chỉ một ô.
Công thức mảng trả kết quả về một vùng nhiều ô
Hình minh hoạ bên dưới trình bay ví dụ đơn giản về việc tính toán cột Total (Tổng doanh thu của một loại sản phầm), thông thường để tính cột Total (cột D) này bạn sẽ nhập vào công thức sau:
Tại ô D2 nhập vào công thức =B2*C2 sau đó chép xuống D3
7
Hình 1: Tính cột [Total] = [Units Sold] * [Unit Price]
Để tính cho cột Total ở đây chúng ta dùng tới 6 công thức. Ngoài cách này chúng ta có thể dùng 1 công thức mảng để tính ra kết quả cho cả cột Total và lưu kết quả trả về tại D2
7.Để tạo một công thức mảng tính toán cho trường hợp này hãy làm theo các bước sau:
- <LI dir=ltr>Chọn vùng các ô sẽ lưu kết quả trả về của công thức mảng, trong ví dụ này chọn vùng D27.
<LI dir=ltr>Nhập vào công thức sau =B2:B7*C2:C7 (sau khi chọn vùng thì gõ công thức này vào) - Vì đây là công thức mảng bạn hãy nhấn tổ hợp phím Ctrl+Shift+Enter để nhận kết quả công thức trả về. (Công thức thông thường thì chỉ cần Enter)
7 đã có kết quả tính toán và khi vào xem thì sẽ có công thức như sau:{=B2:B7*C2:C7}
Lưu ý, Excel sẽ đặt cặp ngoặc móc bao quanh công thức để chỉ đây là công thức mảng.
Công thức mảng trên khi tính toán nó sử dụng dữ liệu từ hai mảng đó là mảng chứa số liệu về [Units Sold] được lưu tại vùng địa chỉ B2:B7 và mảng chứa số liệu về [Unit Price] được lưu tại vùng địa chỉ C2:C7 trong bảng tính.
Vì ứng với mỗi mặt hàng sẽ có một kết quả Total khác nhau, nên đó là lý do chúng ta phải chọn trước vùng địa chỉ D2
7 để lưu trữ kết quả trả về của công thức mảng.Công thức mảng này trả về kết quả hoàn toàn chính xác cũng giống như kết quả trả về từ 6 công thức riêng lẽ sau:
=B2*C2
=B3*C3
=B4*C4
=B5*C5
=B6*C6
=B7*C7
Sử dụng công thức mảng thay cho công thức đơn có một số ưu điểm như:
- <LI class=MsoNormal dir=ltr style="LINE-HEIGHT: normal; TEXT-ALIGN: justify">Là cách tính toán đảm bảo sự chính xác về kết quả (tránh trường hợp vô tình sao chép sai công thức do chạy địa chỉ tham chiếu)
<LI class=MsoNormal dir=ltr style="LINE-HEIGHT: normal; TEXT-ALIGN: justify">Dùng công thức mảng tránh được việc vộ tình xoá hay làm thay đổi công thức trong một ô nào đó của vùng công thức mảng. Vì công thức mảng không cho phép xoá, sửa chữa một ô trong vùng công thức mảng. - Dùng công thức mảng sẽ giúp tránh trường hợp người chưa thành thạo Excel làm xáo trộng các công thức của bạn.
Bây giờ chúng ta cần tính Tổng doanh thu của tất cả các sản phẩm, tuy nhiên yêu cầu là tính toán căn cứ vào cột [Units Sold] và [Unit Price] bằng công thức mảng (nghĩa là không tính toán thông qua cột Total). Khi đó chúng ta có thể dùng công thức mảng sau:
Tại ô D8 bạn nhập vào công thức: =SUM(B2:B7*C2:C7) và kết thúc bằng tổ hợp phím Ctrl+Shift+Enter. Khi đó công thức trong ô D8 có dạng sau {=SUM(B2:B7*C2:C7)}
Công thức mảng này trả về kết quả tính toán tổng doanh thu cho tất cả các sản phẩm trong một ô D8. Công thức này cũng tính toán căn cứ vào 2 mảng số liệu được lưu trong vùng B2:B7 và C2:C7, trong quá trình tính toán công thức mảng sẽ tạo ra trong bộ nhớ một mảng số liệu mới chứa kết quả của phép nhân các cặp số liệu, sau đó hàm SUM được dùng để cộng tất cả các phần tử trong mảng mới này và trả về một giá trị duy nhất lưu vào ô D8.
Trong trường hợp này chúng ta cũng có thể dùng công thức thông thường là hàm SUMPRODUCT như sau:
Tại ô D8 nhập vào công thức =SUMPRODUCT(B2:B7,C2:C7) sau đó Enter.
Mảng một chiều
Ta có thể xem mảng một chiều là một hàng (mảng ngang) hay một cột (mảng dọc). Các phần tử trong mảng một chiều (mảng ngang) được cách biệt nhau bằng một dấu phẩy, và trong mảng dọc được cách nhau bằng dấu chấm phẩy.
Ví dụ: {1,2,3,4,5} (mảng ngang) và {10;20;30;40;50} (mảng dọc)
Hay {"Sun","Mon","Tue","Wed","Thu","Fri","Sat"} (phần tử là Text dạng chuỗi)
Để hiển thị một mảng một chiều 5 phần tử theo dạng ngang hay dọc, ta cần 5 ô liên tục trên cùng một hàng hay một cột
Ví dụ: Tạo mảng 1 chiều
- <LI dir=ltr>Ta chọn 5 ô từ B3:F3, nhập: ={1,2,3,4,5} và nhấn Ctrl+Shift+Enter
<LI dir=ltr>Ta chọn 5 ô từ B6:B10, nhập: ={10;20;30;40;50} và nhấn Ctrl+shift+Enter - Ta chọn 7 ô từ B13:H13, nhập ={"Sun","Mon","Tue","Wed","Thu","Fri","Sat"} và nhấn Ctrl+shift+Enter
Hình 2. Mảng một chiều ngang và dọc
Các phần tử trong mảng sẽ lần lượt được nhập vào 5 ô liên tục theo thứ tự trong mảng. Nếu trong mảng chỉ có 5 phần tử nhưng ta chọn nhiều hơn 5 ô để nhập mảng thì từ ô thứ 6 sẽ cho giá trị #N/A
Hình 3. Mảng lỗi
Mảng hai chiều
Mảng hai chiều là một hình chữ nhật bao gồm nhiều hàng và nhiều cột. Tương tự như mảng một chiều, ta sử dụng các dấu phẩy để ngăn cách các phần tử trong cùng một hàng và dấu chấm phẩy để ngăn cách các phần tử trong cùng một cột.
Ví dụ : Tạo mảng 2 chiều sau {1,2,3,4;5,6,7,8;9,10,11,12}
Để hiển thị mảng này trên bảng tính, ta chọn vùng A1
3, nhập :={1,2,3,4;5,6,7,8;9,10,11,12} và nhấn Ctrl+Shift+Enter
Hình 4. Tạo mảng 2 chiều
Cũng vậy, nếu bạn nhập một mảng vào một dãy vốn có các ô nhiều hơn các phần tử mảng, Excel sẽ hiển thị #N/A trong các ô trống còn lại.
Hình 5. Mảng lỗi
Hằng mảng
Bạn có thể tạo một hằng mảng, đặt cho nó một cái tên, sau đó sử dụng mảng được đặt tên này cho công thức. Ví dụ: Ta có hằng mảng như sau: {1,0,1,0,1}. Công thức sau sử dụng hàm SUM với hằng mảng đứng trước là đối số của nó. Công thức trả về tổng của các giá trị trong mảng (ở đây là 3). Công thức này sử dụng mảng là đối số nhưng không phải là công thức mảng: =SUM({1,0,1,0,1}) và công thức này có cùng kết quả với công thức sau =SUM(1,0,1,0,1). Vào thời điểm này, có thể bạn chưa thấy ưu điểm của việc sử dụng hằng mảng.
Công thức sau sử dụng hai hằng mảng: =SUM({1,2,3,4}*{5,6,7,8}). Công thức này sẽ tạo ra một mảng mới trong bộ nhớ máy tính như sau :{5,12,21,32} và sau đó mảng này lại là đối số cho hàm SUM và trả về kết quả là 70. Bạn sẽ thấy công thức trên tương tự như công thức sau : =SUM(1*5,2*6,3*7,4*8) không là công thức mảng.
Một công thức mảng có thể tính tóan trên các mảng số liệu lưu trữ trên bảng tính và hàng mảng. Ví dụ như công thức =SUM((A1
1*{1,2,3,4})), công thức này tương đương với công thức =SUM(A1*1,B1*2,C1*3,D1*4). (A12 là mảng ngang lưu trữ trên bảng tính).Một hằng mảng sẽ không chứa các công thức, các hàm, các giá trị có chứa dấu dollar, dấu phẩy, chấm phẩy... Sau đây là một hằng mảng không hợp lệ: {SUM(3,2),$56,12,5%}
Mong các bạn gỡ giúp mình với . Mình mới tập tìm hiểu về mảng trong excel thì đã gặp rắc rối , trong vùng A1:A4 mình tạo mảng ngang = {1,2,3,4} , nhấn Ctrl+Shift+Enter nhưng sao cứ nhận được kết quả trong từng cell là 1,234 mà không nhận được kết quả trong từng cell là 1 , 2 , 3 ,4 . Như vậy là bị lỗi gì vậy , mong mọi người giúp mình , cảm ơn nhiều .
bebo021999
Thành viên gạo cội
- Tham gia
- 26/1/11
- Bài viết
- 5,950
- Được thích
- 8,742
- Giới tính
- Nam
- Nghề nghiệp
- GPE
Vùng A1:A4 là mảng đứng, phân cách bằng dấu ; nha bạn.
Cảm ơn bạn , vì mình mới tập tành nên bị nhầm , mình tạo mảng đứng thì cho ra kết quả đúng kết quả từ A1 đến A4 là 1 , 2 , 3 , 4 khi dùng dấu ; Còn mình tạo mảng ngang từ A2 đến D2 = {1,2,3,4} , nhấn Ctrl+Shift+Enter vẫn nhận được kết quả trong từng cell là 1,234 mà không nhận được kết quả trong từng cell là 1 , 2 , 3 ,4 bạn ạ . Mình không biết là mình bị lỗi gì bạn ạ . Mong bạn và mọi người chỉ giúp mình , cảm ơn nhiều .
dhn46
Hướng tới tương lai
- Tham gia
- 1/3/11
- Bài viết
- 3,251
- Được thích
- 3,870
Do thiết đặt dấu phân cách giữa mảng ngang và mảng đứng trên mỗi máy khác nhau nên phải kiểm tra xem nó là dấu gì.
- Kiểm tra mảng ngang: gõ =A1:E1 => Ctrl + Shift + Enter > F2>F9. Bạn nhìn dấu phân cách là dấu phân cách ngang
- Kiểm tra mảng dọc: gõ =A1:A5 => Ctrl + Shift + Enter > F2>F9. Bạn nhìn dấu phân cách là dấu phân cách dọc
Sau đó bạn thực hiện lại các bước tạo mảng theo dấu phân cách
Tạo mảng hai chiều bị lỗi
Mình cũng bị gặp trục trặc khi tạo công thức mảng, dù mình đã làm như cách trên để kiểm tra dấu phân cách ngang, dọc thì đúng là ngang là: 1,2,3, dọc là: 1;2;3, nhưng khi áp dụng công thức để tạo mảng hàng ngang thì các cell nó ra là: 123 123 123, còn hàng dọc thì không ra được cái gì, ko biết lỗi này là do máy hay thế nào? Mọi người giúp mình với, mình dùng exel 2007
Mình cũng bị gặp trục trặc khi tạo công thức mảng, dù mình đã làm như cách trên để kiểm tra dấu phân cách ngang, dọc thì đúng là ngang là: 1,2,3, dọc là: 1;2;3, nhưng khi áp dụng công thức để tạo mảng hàng ngang thì các cell nó ra là: 123 123 123, còn hàng dọc thì không ra được cái gì, ko biết lỗi này là do máy hay thế nào? Mọi người giúp mình với, mình dùng exel 2007
- Tham gia
- 5/6/08
- Bài viết
- 30,703
- Được thích
- 53,952
Bạn có thể thí nghiệm:
1> Để biết dấu nào phân cách mảng ngang: Gõ số 1 vào A1, số 2 vào B1, số 3 vào C1. Tại cell D1, bạn gõ công thức =A1:C1 rồi Ctrl + Shift + Enter
Tiếp theo, chọn cell D1, quét chọn công thức của nó trên thanh Formula rồi bấm F9. Đàng sau số 1 là dấu gì thì đấy là dấu phân cách mảng ngang
2> Để biết dấu nào phân cách mảng dọc: Gõ số 1 vào A1, số 2 vào A2, số 3 vào A3. Tại cell A4, bạn gõ công thức =A1:A3 rồi Ctrl + Shift + Enter
Tiếp theo, chọn cell A4, quét chọn công thức của nó trên thanh Formula rồi bấm F9. Đàng sau số 1 là dấu gì thì đấy là dấu phân cách mảng dọc
Bài viết mới nhất
- Question
- Đã giải quyết
