uocmo_kchodoi
Moderator
- Xu
- 132
Trong bài viết hôm nay mình sẽ giới thiệu đến các bạn kiến thức về công thức mảng - công cụ vô cùng hữu ích nhưng cũng rất trừu tượng, do đó để giải quyết những bài toán phức tạp nhiều khi ta cần phải sử dụng đến nó. Tuy nhiên các bạn chỉ nên sử dụng công thức mạng khi đã có kiến thức khá vững về Excel. Trong hướng dẫn mình sẽ đưa ra một số ví dụ và phân tích thật tỉ mỉ để các bạn có thể hiểu được bản chất của công thức mảng.
Công thức mảng là công thức xử lý dữ liệu trên các mảng, tuy nhiên 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 ô. Sau khi nhập xong công thức mảng, thay vì bấm Enter để ra giá trị thì chúng ta phải sử dụng tổ hợp phím Ctrl+Shift+Enter nhé. Nói xong khái niệm, chắc các bạn thấy khá trừu tượng nhỉ. Để làm rõ, mình bắt đầu đi vào ví dụ 1 như sau
Giả sử mình có bảng giá vật tư như hình dưới. Các bạn có thể tải file ví dụ này tại đây
Bình thường, các bảng giá vật tư như trên sẽ có cột Thành tiền, mỗi mặt hàng sẽ có 1 giá trị thành tiền tương ứng chính bằng số lượng nhân với đơn giá. Tuy nhiên, với ví dụ trong hình, mình không có cột thành tiền nhưng mình yêu cầu tính Tổng thành tiền vào ô D16. Giá trị của ô này chính là tổng thành tiền của tất cả các mặt hàng trong bảng. Phải làm thế nào nhỉ, nếu tư duy theo cách thông thường, tại ô D16 chắc có bạn sẽ nghĩ đến công thức sau
=C3*D3+C4*D4+C5*D5+C6*D6+C7*D7+C8*D8+C9*D9+C10*D10+C11*D11+C12*D12+C13*D13+C14*D14+C15*D15
Hoặc
=SUM(C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11,C12*D12,C13*D13,C14*D14,C15*D15)
Cả 2 trường hợp trên đều cho ra 1 kết quả là 7,127,021 như 2 hình dưới
Ví dụ trên chỉ là 13 dòng dữ liệu mà công thức đã dài lê thê đúng không các bạn. Giờ mình sẽ sử dụng công thức mảng, tại D16 mình nhập vào công thức
=SUM(C3:C15*D3
15)
Kết thúc mình bấm tổ hợp phím Ctrl+Shift+Enter, lúc này công thức sẽ được nằm trong cặp dấu ngoặc nhọn
={SUM(C3:C15*D315)}
Nhưng khi click vào ô để sửa công thức các bạn sẽ không thấy ngoặc nhọn đó đâu cả. Và đây cũng chính là dấu hiệu để nhận biết một công thức là công thức mảng hay công thức thường. Công thức trên vẫn cho ra kết quả giống với 2 công thức mà mình đã làm trước đó
So với 2 công thức trước đó, các bạn có thấy công thức này ngắn gọn hơn nhiều đúng không. Nhưng, công thức trên hoạt động như thế nào và tại sao nó lại cho kết quả đúng như 2 công thức trước đó? Mình cùng phân tích công thức này nhé
Mình có 2 mảng thực hiện nhân với nhau C3:C15*D315, 2 mảng này có độ dài bằng nhau và có 13 phần tử (13 ô – cells). Phép nhân này sẽ tạo ra 1 mảng cũng có đúng 13 phần tử nhưng được lưu trong bộ nhớ của máy tính. Mỗi phần tử của mảng trong bộ nhớ máy tính này chính là tích của 2 phần tử tương ứng của 2 mảng ban đầu. Để hiểu rõ hơn nữa, tại một cột bất kỳ trong sheet đang ví dụ, các bạn chọn (bôi đen) 1 mảng cũng có 13 phần tử, rồi nhập vào công thức =C3:C15*D315 sau đó bấm tổ hợp phímCtrl+Shift+Enter (mình lấy ô từ F3 đến F15 gần đó cho dễ quan sát). Kết quả như hình dưới
Vậy =C3:C15*D315 chính là công thức mảng cho ra mảng khác chính là kết quả của phép nhân số lượng nhân đơn giá tương ứng của từng vật tư. À, vậy để tính tổng thành tiền mình chỉ cần tính tổng của mảng này là được, hay chính là mình sử dụng công thức mảng mà mình đã sử dụng =SUM(C3:C15*D315).
Giờ mình chuyển sang ví dụ khác phức tạp hơn như hình dưới, các bạn có thể tải file ví dụ này tại đây
Trong ví dụ này dữ liệu chỉ mang tính chất ví dụ nên mình đưa vào ít dữ liệu của 2 tháng 5 và tháng 6. Nhu cầu là mình nhập vào Tháng cần tính vào ô G23 thì Tổng tiền tại ô G24 tương ứng của tháng đó sẽ được tính
Bình thường chúng ta sẽ tạo ra 1 cột phụ để có được tháng tương ứng của từng dòng sau đó sử dụng hàm SUMIF để tính tổng theo điều kiện cột phụ chứa tháng mới tạo ra. Cách này quá quen thuộc rồi nên mình không giới thiệu nữa. Giả sử cần tính tổng của tháng 5, ô G23 mình nhập vào số 5. Mình sử dụng công thức mảng sau cho ô G24, lưu ý sau khi nhập công thức xong bấm tổ hợp phím Ctrl+Shift+Enter và đây là công thức mảng
=SUM(--(MONTH(B4:B19)=G23)*F4:F19)
Kết quả như hình dưới
Cũng tương tự như ở ví dụ 1, công thức mảng ở ví dụ 2 sẽ tạo ra 1 mảng, tính toán trong bộ nhớ và sau đó hàm SUM sẽ tính tổng của mảng trong bộ nhớ này đưa kết quả ra ô G24. Tuy nhiên ở ví dụ 2 này mình ứng dụng linh hoạt trong công thức hơn. Cụ thể mình cùng đi phân tích công thức trên nhé
Tại một ô bất kỳ, mình chọn 1 vùng dữ liệu bằng với số dòng cần tính toán trong ví dụ (mình chọn cột J cho dễ nhìn), sau đó mình nhập vào công thức mảng, kết thúc bằng Ctrl+Shift+Enter
=MONTH(B4:B19)
Như vậy, công thức mảng ở trên tạo ra 1 mảng với các phần tử là tháng của dòng tương ứng. Mình tiếp tục nhập vào công thức mảng sau và kết thúc bởi Ctrl+Shift+Enter
MONTH(B4:B19)=G23
Các bạn thấy không, công thức mảng vừa rồi sẽ tạo ra 1 mảng mới đúng bằng 2 mảng mà mình thực hiện ở công thức trên, mỗi phần tử trả về giá trị TRUE hay FALSE (đúng hay sai) tương ứng với phép so sánh tháng của từng ô trong cột Ngày có bằng giá trị của ô G23 không. Lúc này mình cần chuyển các giá trị của mảng vừa tạo ra về 1 và không tương ứng với TRUE và FALSE. Mình nghĩ ngay đến phương án –TRUE=1, –FALSE=0 nên mình sử dụng công thức mảng
=--(MONTH(B4:B19)=G23)
Không quên kết thúc bấm tổ hợp phím Ctrl+Shift+Enter cho công thức mảng. Công thức trên sẽ đổi TRUE, FALSE thành 1 và 0 tương ứng như hình dưới
Vậy, lấy từng giá trị của mảng trên nhân với giá trị tương ứng ở cột Tổng thì đương nhiên giá trị của dòng nào không đúng với tháng mình cần tính sẽ ra giá trị bằng 0 (vì 0 nhân với số nào cũng bằng 0). Mình lấy SUM của các giá trị còn lại thì sẽ ra thứ mình cần. Do đó mình sử dụng công thức mảng
=SUM(--(MONTH(B4:B19)=G23)*F4:F19)
Trên đây là 2 ví dụ rất cơ bản về công thức mảng trong Excel. Mong rằng những kiến thức cơ bản này sẽ giúp các bạn hiểu về mảng và từ đó sẽ phát triển hơn để giải quyết được nhiều bài toán phức tạp.
Nguồn: thuthuattinhoc.org
GIỚI THIỆU VÀ HƯỚNG DẪN SỬ DỤNG CÔNG THỨC MẢNG
Công thức mảng là công thức xử lý dữ liệu trên các mảng, tuy nhiên 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 ô. Sau khi nhập xong công thức mảng, thay vì bấm Enter để ra giá trị thì chúng ta phải sử dụng tổ hợp phím Ctrl+Shift+Enter nhé. Nói xong khái niệm, chắc các bạn thấy khá trừu tượng nhỉ. Để làm rõ, mình bắt đầu đi vào ví dụ 1 như sau
Giả sử mình có bảng giá vật tư như hình dưới. Các bạn có thể tải file ví dụ này tại đây
Bình thường, các bảng giá vật tư như trên sẽ có cột Thành tiền, mỗi mặt hàng sẽ có 1 giá trị thành tiền tương ứng chính bằng số lượng nhân với đơn giá. Tuy nhiên, với ví dụ trong hình, mình không có cột thành tiền nhưng mình yêu cầu tính Tổng thành tiền vào ô D16. Giá trị của ô này chính là tổng thành tiền của tất cả các mặt hàng trong bảng. Phải làm thế nào nhỉ, nếu tư duy theo cách thông thường, tại ô D16 chắc có bạn sẽ nghĩ đến công thức sau
=C3*D3+C4*D4+C5*D5+C6*D6+C7*D7+C8*D8+C9*D9+C10*D10+C11*D11+C12*D12+C13*D13+C14*D14+C15*D15
Hoặc
=SUM(C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11,C12*D12,C13*D13,C14*D14,C15*D15)
Cả 2 trường hợp trên đều cho ra 1 kết quả là 7,127,021 như 2 hình dưới
Ví dụ trên chỉ là 13 dòng dữ liệu mà công thức đã dài lê thê đúng không các bạn. Giờ mình sẽ sử dụng công thức mảng, tại D16 mình nhập vào công thức
=SUM(C3:C15*D3
15)Kết thúc mình bấm tổ hợp phím Ctrl+Shift+Enter, lúc này công thức sẽ được nằm trong cặp dấu ngoặc nhọn
={SUM(C3:C15*D3
15)}Nhưng khi click vào ô để sửa công thức các bạn sẽ không thấy ngoặc nhọn đó đâu cả. Và đây cũng chính là dấu hiệu để nhận biết một công thức là công thức mảng hay công thức thường. Công thức trên vẫn cho ra kết quả giống với 2 công thức mà mình đã làm trước đó
So với 2 công thức trước đó, các bạn có thấy công thức này ngắn gọn hơn nhiều đúng không. Nhưng, công thức trên hoạt động như thế nào và tại sao nó lại cho kết quả đúng như 2 công thức trước đó? Mình cùng phân tích công thức này nhé
Mình có 2 mảng thực hiện nhân với nhau C3:C15*D3
15, 2 mảng này có độ dài bằng nhau và có 13 phần tử (13 ô – cells). Phép nhân này sẽ tạo ra 1 mảng cũng có đúng 13 phần tử nhưng được lưu trong bộ nhớ của máy tính. Mỗi phần tử của mảng trong bộ nhớ máy tính này chính là tích của 2 phần tử tương ứng của 2 mảng ban đầu. Để hiểu rõ hơn nữa, tại một cột bất kỳ trong sheet đang ví dụ, các bạn chọn (bôi đen) 1 mảng cũng có 13 phần tử, rồi nhập vào công thức =C3:C15*D315 sau đó bấm tổ hợp phímCtrl+Shift+Enter (mình lấy ô từ F3 đến F15 gần đó cho dễ quan sát). Kết quả như hình dưới
Vậy =C3:C15*D3
15 chính là công thức mảng cho ra mảng khác chính là kết quả của phép nhân số lượng nhân đơn giá tương ứng của từng vật tư. À, vậy để tính tổng thành tiền mình chỉ cần tính tổng của mảng này là được, hay chính là mình sử dụng công thức mảng mà mình đã sử dụng =SUM(C3:C15*D315).Giờ mình chuyển sang ví dụ khác phức tạp hơn như hình dưới, các bạn có thể tải file ví dụ này tại đây
Trong ví dụ này dữ liệu chỉ mang tính chất ví dụ nên mình đưa vào ít dữ liệu của 2 tháng 5 và tháng 6. Nhu cầu là mình nhập vào Tháng cần tính vào ô G23 thì Tổng tiền tại ô G24 tương ứng của tháng đó sẽ được tính
Bình thường chúng ta sẽ tạo ra 1 cột phụ để có được tháng tương ứng của từng dòng sau đó sử dụng hàm SUMIF để tính tổng theo điều kiện cột phụ chứa tháng mới tạo ra. Cách này quá quen thuộc rồi nên mình không giới thiệu nữa. Giả sử cần tính tổng của tháng 5, ô G23 mình nhập vào số 5. Mình sử dụng công thức mảng sau cho ô G24, lưu ý sau khi nhập công thức xong bấm tổ hợp phím Ctrl+Shift+Enter và đây là công thức mảng
=SUM(--(MONTH(B4:B19)=G23)*F4:F19)
Kết quả như hình dưới
Cũng tương tự như ở ví dụ 1, công thức mảng ở ví dụ 2 sẽ tạo ra 1 mảng, tính toán trong bộ nhớ và sau đó hàm SUM sẽ tính tổng của mảng trong bộ nhớ này đưa kết quả ra ô G24. Tuy nhiên ở ví dụ 2 này mình ứng dụng linh hoạt trong công thức hơn. Cụ thể mình cùng đi phân tích công thức trên nhé
Tại một ô bất kỳ, mình chọn 1 vùng dữ liệu bằng với số dòng cần tính toán trong ví dụ (mình chọn cột J cho dễ nhìn), sau đó mình nhập vào công thức mảng, kết thúc bằng Ctrl+Shift+Enter
=MONTH(B4:B19)
Như vậy, công thức mảng ở trên tạo ra 1 mảng với các phần tử là tháng của dòng tương ứng. Mình tiếp tục nhập vào công thức mảng sau và kết thúc bởi Ctrl+Shift+Enter
MONTH(B4:B19)=G23
Các bạn thấy không, công thức mảng vừa rồi sẽ tạo ra 1 mảng mới đúng bằng 2 mảng mà mình thực hiện ở công thức trên, mỗi phần tử trả về giá trị TRUE hay FALSE (đúng hay sai) tương ứng với phép so sánh tháng của từng ô trong cột Ngày có bằng giá trị của ô G23 không. Lúc này mình cần chuyển các giá trị của mảng vừa tạo ra về 1 và không tương ứng với TRUE và FALSE. Mình nghĩ ngay đến phương án –TRUE=1, –FALSE=0 nên mình sử dụng công thức mảng
=--(MONTH(B4:B19)=G23)
Không quên kết thúc bấm tổ hợp phím Ctrl+Shift+Enter cho công thức mảng. Công thức trên sẽ đổi TRUE, FALSE thành 1 và 0 tương ứng như hình dưới
Vậy, lấy từng giá trị của mảng trên nhân với giá trị tương ứng ở cột Tổng thì đương nhiên giá trị của dòng nào không đúng với tháng mình cần tính sẽ ra giá trị bằng 0 (vì 0 nhân với số nào cũng bằng 0). Mình lấy SUM của các giá trị còn lại thì sẽ ra thứ mình cần. Do đó mình sử dụng công thức mảng
=SUM(--(MONTH(B4:B19)=G23)*F4:F19)
Trên đây là 2 ví dụ rất cơ bản về công thức mảng trong Excel. Mong rằng những kiến thức cơ bản này sẽ giúp các bạn hiểu về mảng và từ đó sẽ phát triển hơn để giải quyết được nhiều bài toán phức tạp.
Nguồn: thuthuattinhoc.org
