Tạo công thức mảng trong excel – với nhiều điều kiện chỉ bằng click chuột (1 người xem)

[AnhPhong411]

Chào các bạn,
Công thức mảng trong Excel là một kỹ thuật nâng cao trong làm công thức Excel, nó cho phép thực hiện nhiều điều kiện, xử lý các bài toán rất khó với nhiều điều kiện ràng buộc nhau. Để giải quyết các bài toán về công thức mảng, kết hợp với nhiều điều kiên, các bạn thường phải dùng 1 mảng để so sánh với một giá trị hoặc một mảng khác, và bạn cần phải có kiên thức cơ bản về công thức mảng. Trong quá trình làm bạn gõ công thức sẽ không tránh nhiều vấn đề sai sót và kết quả sai hoặc một số vấn đề liên quan trong quá trình gõ công thức.
Vậy hôm nay, mình sẽ hướng dẫn cho những bạn chưa có thời gian tìm hiểu về công thức mảng, và đang có nhu cầu muốn tìm hiểu. Mình sẽ hướng dẫn các bạn lập công thức mảng chỉ bằng click chuột, công thức mảng sẽ được tạo rất nhanh, chính xác, các bạn có thể chỉnh sữa công thức cho đúng với yêu cầu bài toán của mình. Khi tạo xong các bạn không cần cài đặt A-Tools Free vẫn có thể sử dụng như các công thức Excel thông thường khác.
Ví dụ để giải bài toán tính tổng của một mặt hàng theo điều kiên loại phiếu. Công thức chúng ta cần là sẽ là: =SUM(IF($I$5:$I$19=$E30,IF($L$5:$L$19="X",IF(MONTH($D$5:$D$19)=8,$M$5:$M$19,0),0),0))
Nhưng ở đây mình cùng công cụ A-Tools chỉ cần click chuột để tạo ra được ạ. Dưới đây mình có kèm theo video hướng dẫn và bài mình đã làm trên Excel.
video:

[video=youtube;MKXZeo9Pdsw]https://www.youtube.com/watch?v=MKXZeo9Pdsw&feature=youtu.be[/video]

Hình ảnh cụ thể:

 

[huuthang_bd]

A-Tools hay thật. Bạn hướng dẫn tôi lập công thức tìm mã hàng có doanh thu cao nhất trong tháng 8 với. Tôi làm không được

 

[langtuchungtinh360]

A-Tools hay thật. Bạn hướng dẫn tôi lập công thức tìm mã hàng có doanh thu cao nhất trong tháng 8 với. Tôi làm không được

em thì cài thử vô. mở excel không thấy Atools đâu mà thêm addins vô là Restar office luôn. @@

 

[AnhPhong411]

em thì cài thử vô. mở excel không thấy Atools đâu mà thêm addins vô là Restar office luôn. @@

Chào anh,
Anh có thể xem video sau để hướng dẫn khắc phục vấn đề không có menu A-Tools ạ. Link video

 

[huuthang_bd]

Chào anh,
Em có làm video hướng dẫn cụ thể đó anh ạ.Cảm ơn anh nhiều ạ,

Trong đó đâu có công thức mình cần đâu???

 

[AnhPhong411]

Trong đó đâu có công thức mình cần đâu???

Vậy anh phải liên hệ em. Và nêu yêu cầu bài toán anh cần.Em mới có thể hỗ trợ dc ạ.Chứ giờ em k biết yêu cầu bài toán anh thì làm sao hỗ trợ dc ạ.

 

[AnhPhong411]

Ah ra thế ah? là phải như vầy nè: "Vậy anh phải liên hệ lên bên em. " Thế thì từ đầu phải nói rõ có đỡ mất công tới #10 mới biết được không.

Đây là em bảo nếu anh cần bên em sẽ hỗ trợ tư vấn cho anh trong phạm vi có thể. Còn bài toán của anh thế nào thì đó là bên anh thì nó có đáp ứng được hay không vẫn là vấn đề ở anh. Và em hỗ trợ ngoài giờ k liên quan vấn đề thương mại đâu.

 

[Nguyễn Duy Tuân]

Ah ra thế ah? là phải như vầy nè: "Vậy anh phải liên hệ lên bên em. " Thế thì từ đầu phải nói rõ có đỡ mất công tới #10 mới biết được không.

Bạn hpkhuong. Công cụ A-Tools Free này còn có chức năng tạo công thức mảng mà chỉ cần dùng chuột, khi đã tạo được công thức này thì người dùng không cần A-Tools vẫn chạy. Bạn Phong đã tự quay video hướng dẫn cách làm để cho nhiều người dễ làm và vận dụng, tự học cùng công cụ này. Còn bài toán cụ thể của bạn thì cứ đưa lên đây, bạn Phong hay bạn nào đó có kiến thức, có thời gian thì người ta giúp đỡ bạn mang tính cá nhân. Việc giúp đỡ này không liên quan gì bên nào cả và chắc chắn là không mất tiền. Sẽ có nhiều người cảm ơn bạn Phong.

Kiến thức về công thức mảng tôi đã chia sẻ cộng đồng Webketoan, Giaiphapexcel cách đây gần 10 năm tại bài "SUMPRODUCT và Công thức mảng - Phép tính có nhiều điều kiện". Bạn muốn học có kiến thức tốt để có thể tự được làm trường hợp của bạn thì váo đó đọc nhé.

A-Tools là công cụ có nhiều chức năng hỗ trợ tốt cho người dùng thuận tiện cho việc học nâng cao và ứng dụng tiện ích. Nó giống như các Add-in ở nước ngoài có 2 phiên bản Free và Pro. Người có nhu cầu ứng dụng tính năng cao cấp thì mua còn không thì được dùng miễn phí với các tính năng trong phiên bản miễn phí. Đây là sự hỗ trợ từ tác giả Nguyễn Duy Tuân nói riêng và Bluesofts nói chung.

 

[AnhPhong411]

A-Tools hay thật. Bạn hướng dẫn tôi lập công thức tìm mã hàng có doanh thu cao nhất trong tháng 8 với. Tôi làm không được

Chào bạn,
Đây là bài toán mà bạn yêu cầu, mình đã giải kèm theo hình ảnh chứa công thức và file excel cho bạn đây.Bạn tải về xem nhé.
Công thức :=INDEX($C$5:$M$19,MATCH(MAX(IF(MONTH($D$5:$D$19)=$O$24,J5:J19)),J5:J19,0),7)
và hình ảnh cụ thể ạ:

 

[ndu96081631]

Chào bạn,
Đây là bài toán mà bạn yêu cầu, mình đã giải kèm theo hình ảnh chứa công thức và file excel cho bạn đây.Bạn tải về xem nhé.

View attachment 147637

Ủa gì kỳ vậy ta? Đây chỉ là công thức mảng bình thường mà ai trên diễn đàn này cũng biết! Nó liên quan gì đến A-Tools nhỉ?
Tóm lại là... chưa hiểu

 

[doveandrose]

Chào bạn,
Đây là bài toán mà bạn yêu cầu, mình đã giải kèm theo hình ảnh chứa công thức và file excel cho bạn đây.Bạn tải về xem nhé.
Công thức :=INDEX($C$5:$M$19,MATCH(MAX(IF(MONTH($D$5:$D$19)=$O$24,J5:J19)),J5:J19,0),7)
và hình ảnh cụ thể ạ:

View attachment 147637

trong câu nói của Huuthang có 2 dấu chấm . nghĩa là 3 câu . bạn nên liên kết 3 câu đó lại với nhau để hiểu ý người khác nói . đó cũng là kỹ năng cơ bản bạn cần nắm trước khi muốn đi hỗ trợ ai đó . nếu chỉ dùng công thức excel thông thường thì bạn nghĩ 1 thành viên có vài ngàn bài viết và cũng hàng ngàn lượt được cảm ơn có thể không viết được công thức excel cho bài toán đó hay sao ??? . bạn nghĩ xem người ta muốn gì ?

 

[AnhPhong411]

Ủa gì kỳ vậy ta? Đây chỉ là công thức mảng bình thường mà ai trên diễn đàn này cũng biết! Nó liên quan gì đến A-Tools nhỉ?
Tóm lại là... chưa hiểu

Em chào anh,
Vâng đây chính là công thức mảng trong Excel anh ạ. A-Tools chỉ là công cụ tạo ra một phần công thức mảng, hỗ trợ cho bài toán mà mình đang cần ạ. Trên A-Tools anh sẽ lấy ra số lượng lớn nhất với điều kiện ngày chứng từ là tháng 8 một cách nhanh nhất và đơn giản nhất. Khi tìm được số lượng lớn nhất MAX(IF(MONTH($D$5:$D$19)=$O$24,J5:J19)), bây giờ chúng ta có thể chỉnh sữa công thức theo yêu cầu bài toán đặt ra. Vậy nên em chỉnh sữa và thêm hàm Index, Match để tìm tới mã mặt hàng như yêu cầu của bài toán =INDEX($C$5:$M$19,MATCH(MAX(IF(MONTH($D$5:$D$19)=$O$24,J5:J19)),J5:J19,0),7).

 

[huuthang_bd]

Em chào anh,
Vâng đây chính là công thức mảng trong Excel anh ạ. A-Tools chỉ là công cụ tạo ra một phần công thức mảng, hỗ trợ cho bài toán mà mình đang cần ạ. Trên A-Tools anh sẽ lấy ra số lượng lớn nhất với điều kiện ngày chứng từ là tháng 8 một cách nhanh nhất và đơn giản nhất. Khi tìm được số lượng lớn nhất MAX(IF(MONTH($D$5:$D$19)=$O$24,J5:J19)), bây giờ chúng ta có thể chỉnh sữa công thức theo yêu cầu bài toán đặt ra. Vậy nên em chỉnh sữa và thêm hàm Index, Match để tìm tới mã mặt hàng như yêu cầu của bài toán =INDEX($C$5:$M$19,MATCH(MAX(IF(MONTH($D$5:$D$19)=$O$24,J5:J19)),J5:J19,0),7).

Tôi là người hỏi nên tôi có một số ý kiến:

1. Đầu tiên là về A-Tools. Bạn nói chỉ tạo ra một phần, phần còn lại tự làm. Nếu đã nói rõ như thế thì không có gì phải bàn cãi nữa nhưng nên chăng sửa tiêu đề topic này lại thành Tạo công thức mảng đơn giản trong excel – với nhiều điều kiện chỉ bằng click chuột

2. Thứ hai là công thức mà bạn đã xây dựng:
- Bài toán là tìm mã hàng có doanh thu cao nhất trong tháng 8. Tức là nếu làm thủ công thì tính tổng doanh thu của tất cả các mặt hàng trong tháng 8 rồi so sánh với nhau coi cái nào lớn nhất. Nhưng công thức của bạn hình như đang tính số lượng lớn nhất của 1 lần giao dịch (chả thấy điều kiện gì đề cập đến chuyện mua hay bán cả) trong tháng 8. Không ăn nhập gì với đầu bài cả.
- Sau khi tìm được Max mà dùng hàm MATCH để dò tìm là hoàn toàn tào lao. Ai biết trong các tháng trước không có phát sinh số liệu y như vậy mà dùng hàm MATCH (thử sửa dữ liệu ô J6 thành 64 là thấy ngay).

 

[AnhPhong411]

Cách 1: Dùng công thức Excel

Tôi là người hỏi nên tôi có một số ý kiến:

1. Đầu tiên là về A-Tools. Bạn nói chỉ tạo ra một phần, phần còn lại tự làm. Nếu đã nói rõ như thế thì không có gì phải bàn cãi nữa nhưng nên chăng sửa tiêu đề topic này lại thành Tạo công thức mảng đơn giản trong excel – với nhiều điều kiện chỉ bằng click chuột

2. Thứ hai là công thức mà bạn đã xây dựng:
- Bài toán là tìm mã hàng có doanh thu cao nhất trong tháng 8. Tức là nếu làm thủ công thì tính tổng doanh thu của tất cả các mặt hàng trong tháng 8 rồi so sánh với nhau coi cái nào lớn nhất. Nhưng công thức của bạn hình như đang tính số lượng lớn nhất của 1 lần giao dịch (chả thấy điều kiện gì đề cập đến chuyện mua hay bán cả) trong tháng 8. Không ăn nhập gì với đầu bài cả.
- Sau khi tìm được Max mà dùng hàm MATCH để dò tìm là hoàn toàn tào lao. Ai biết trong các tháng trước không có phát sinh số liệu y như vậy mà dùng hàm MATCH (thử sửa dữ liệu ô J6 thành 64 là thấy ngay).

Em chào anh,
Để giải quyết yêu cầu bài toán của anh, em đã làm một file chi tiết theo đường dẫn dưới ạ. Trong file này em phải tạo một bảng phụ lấy ra danh sách mã hàng và tổng doanh thu theo từng mặt hàng ạ. Nhưng ở đây em dùng tính năng tự đông tạo công thức mảng trên A-tools để tạo ra ạ.
Dưới đây là link dowload file excel em đã làm ạ: link dowload

Cách 2: Dùng SQL

Cái này thì hơi.. bậy nha đồng chí
Bởi người ta là nhà tài trợ, người ta đăng bài quảng cáo vô box của người ta là hoàn toàn đúng luật (đăng chỗ khác mới sai luật)
Còn chuyện mình có thích hay không là chuyện cá nhân, thiết nghĩ không nên nói ở đây
---------------------------
Mình thì chỉ quan tâm A-Tools làm được thứ gì lạ lạ thôi, nếu không thì hổng có gì để nói cả

Em chào anh,
Hiện tại trong top píc này, phần tạo công thức mảng chỉ một tính năng mà A-tools có, tính năng này với những người thành thạo công thức mảng thì lại là bình thường như anh nói ạ. Nhưng với nhiều người chưa tìm hiểu sâu thì đây là 1 cách hiệu quả để học cách tạo công thức mảng anh ạ.
Các tính năng cao cấp khác đã được giới thiếu bên trang chủ của công ty BLUESOFTS rồi ạ, em không nêu ở đây vì nêu lại nghĩ là quảng cáo ạ.
Về công thức mảng anh Tuân có link tới bài hướng dẫn ở #16 rồi
Còn về bài toán #5 thì em có thể dùng SQL Builder trong A-Tools để làm ạ, ở đây em cần tạo bảng phụ mà dùng một hàm BS_sql để giải bài toán ạ.
Câu lệnh đó là :
SELECT TOP 1 MA_VLSPHH FROM (SELECT MA_VLSPHH,SUM(THANH_TIEN) AS TONGTT
FROM DATA
WHERE MONTH(NGAY_CT) =8 AND LOAI_PHIEU='X'
GROUP BY MA_VLSPHH) D ORDER BY TONGTT DESC

Với câu lệnh này, anh/chị không cần tạo bảng phụ, các anh/chị lập trình VBA, dùng đối tượng ADO thì thực thi câu lệnh trên là được ạ.

 

[HungQuoc49]

Cách 1ùng công thức Excel


Em chào anh,
Để giải quyết yêu cầu bài toán của anh, em đã làm một file chi tiết theo đường dẫn dưới ạ. Trong file này em phải tạo một bảng phụ lấy ra danh sách mã hàng và tổng doanh thu theo từng mặt hàng ạ. Bảng phụ này em có thể dùng một công thức BS SQl để tạo ra. Nhưng ở đây em dùng tính năng tự đông tạo công thức trên A-tools để tạo ra ạ.
Dưới đây là link dowload file excel em đã làm ạ: link dowload

Cách 2: Dùng SQL


Em chào anh,
Hiện tại trong top píc này, phần tạo công thức mảng chỉ một tính năng mà A-tools có, tính năng này với những người thành thạo công thức mảng thì lại là bình thường như anh nói ạ. Nhưng với nhiều người chưa tìm hiểu sâu thì đây là 1 cách hiệu quả để học cách tạo công thức mảng anh ạ.
Các tính năng cao cấp khác đã được giới thiếu bên trang chủ của công ty BLUESOFTS rồi ạ, em không nêu ở đây vì nêu lại nghĩ là quảng cáo ạ.
Về công thức mảng anh Tuân có link tới bài hướng dẫn ở #16 rồi
Còn về bài toán #5 thì em có thể dùng SQL Builder trong A-Tools để làm ạ, ở đây em cần tạo bảng phụ mà dùng một hàm BS_sql để giải bài toán ạ.
Câu lệnh đó là :
SELECT TOP 1 MA_VLSPHH FROM (SELECT MA_VLSPHH,SUM(THANH_TIEN) AS TONGTT
FROM DATA
WHERE MONTH(NGAY_CT) =8 AND LOAI_PHIEU='X'
GROUP BY MA_VLSPHH) D ORDER BY TONGTT DESC

Với câu lệnh này, anh/chị không cần tạo bảng phụ, các anh/chị lập trình VBA, dùng đối tượng ADO thì thực thi câu lệnh trên là được ạ.

SQL liên quan như thế nào với tiêu đề: Tạo công thức mảng trong excel – với nhiều điều kiện chỉ bằng click chuột

 

[AnhPhong411]

SQL liên quan như thế nào với tiêu đề: Tạo công thức mảng trong excel – với nhiều điều kiện chỉ bằng click chuột

Em chào anh,

Ở Cách 1 là em tạo công thức mảng trên Excel đơn giản bằng công cụ A-Tools để giải quyết yêu cầu #5, ở cách này không liên quan gì tới câu lệnh SQL đâu ạ.
Cách 2 là mở rộng cho những người dùng ADO hoăc các công cụng khác có thể tham khảo thêm để giải quyết bài #5. Đây là ý của em muốn mở rộng cách làm mà thôi

Mình có xem rồi.
Thấy cũng mạnh. Nhưng các câu lệnh khó nhớ. .

Em chào anh,
Những câu lệnh SQL là một trong các ngôn ngữ chuẩn về truy vấn dữ liệu được thế giới công nhận và sử dụng rất rộng rãi ạ. Trong Excel vào menu DATA -> CONNECTIONS cho phép sử dụng câu lệnh SQL. Mới đây trên mạng cũng có xuất hiên thêm các công cụ mạnh như Add-in Power Query, bảng tính trên Google docs cũng mới đây cho sử dụng SQL. Tóm lại, SQL là ngôn ngữ rất mạnh để trích lọc dữ liệu không phải bàn thêm ở diễn đàn này, chỉ là phải học như thế nào để nắm bắt, ứng dụng được nhanh và hiệu quả nhất. Trên Youtube có rất nhiều bài học trực tuyến mà chỉ cần tập trung vào 6 từ khóa chính là đã ra được các sổ sách với khả năng trích lọc nhiều điều kiện rất mạnh (Nếu làm Excel thì thực sự rất vất vả).

Mình có xem rồi.
Mà ko biết bản của bác hoàn thiện chưa.
Chứ cài vào lỗi tùm lum nản lắm.

Theo như em biết thì sản phầm này có từ năm 2007, được công ty Bluesofts đưa ra công bố vào năm 2010 và được nhiều công ty, ngân hàng, doanh nghiệp, cá nhân đã và đang ứng dụng trong công việc rất tốt ạ. Vì do môi trường máy tính đa dạng, nên một số máy tính không cài được là chuyện bình thường anh ạ. Nếu máy tính anh chưa cài được thì em có thể hỗ trợ anh ạ.

 

[HungQuoc49]

Em chào anh,

Ở Cách 1 là em tạo công thức mảng trên Excel đơn giản bằng công cụ A-Tools để giải quyết yêu cầu #5, ở cách này không liên quan gì tới câu lệnh SQL đâu ạ.
Cách 2 là mở rộng cho những người dùng ADO hoăc các công cụng khác có thể tham khảo thêm để giải quyết bài #5. Đây là ý của em muốn mở rộng cách làm mà thôi

sheet data H37 dùng index, match, max bình thường, không cần đến công thức mảng
dùng công cụ gì để có được danh sách tại G32:G35 ?

Với bài này cho excel đơn giản thì dùng pivottable tổng hợp rồi tìm kiếm sau

 

[AnhPhong411]

sheet data H37 dùng index, match, max bình thường, không cần đến công thức mảng
dùng công cụ gì để có được danh sách tại G32:G35 ?

Với bài này cho excel đơn giản thì dùng pivottable tổng hợp rồi tìm kiếm sau

Em chào anh,
Để trả lời yêu cầu của anh em có làm một video cụ thể sau ạ. Ở đây em đặt vùng dữ liệu từ G24:G27 ạ, anh/chị chon chế độ HD để xem cho rõ ạ.

[video=youtube;8Bv2iYYFGvM]https://www.youtube.com/watch?v=8Bv2iYYFGvM&feature=youtu.be[/video]

 

[HungQuoc49]

Em chào anh,
Để trả lời yêu cầu của anh em có làm một video cụ thể sau ạ. Ở đây em đặt vùng dữ liệu từ G24:G27 ạ, anh/chị chon chế độ HD để xem cho rõ ạ.

tức là danh sách này được lập bằng tay?
[TABLE="width: 96"]
[TR]
[TD]HH001[/TD]
[/TR]
[TR]
[TD]HH002[/TD]
[/TR]
[TR]
[TD]HH003[/TD]
[/TR]
[TR]
[TD]HH004[/TD]
[/TR]
[/TABLE]
---
Nhờ giải thích hộ vì sao phải dùng công thức mảng tại H37 trong file của bài 41 trong khi dùng công thức thường với nội dung y nguyên vẫn được

sheet data H37 dùng index, match, max bình thường, không cần đến công thức mảng