SUMPRODUCT và Công thức mảng - Phép tính có nhiều điều kiện

phihndhsp

Thành viên gạo cội
Tham gia ngày
26 Tháng mười hai 2009
Bài viết
3,320
Được thích
2,414
Điểm
860
Vâng, cảm ơn bác, quá hay ạ. Em lại phải nghiên cứu thêm cái hàm MMULT này mới được.
Nếu bạn học toán tuyến tính thì sẽ hiểu được các quy luật của các phép tính trên ma trận, còn không học thì hơi bị nhứt cái đầu đó
 

echo07

Nguyệt Hà
Tham gia ngày
24 Tháng tám 2007
Bài viết
265
Được thích
193
Điểm
695
Nơi ở
Đối diện Gpe
Nếu bạn học toán tuyến tính thì sẽ hiểu được các quy luật của các phép tính trên ma trận, còn không học thì hơi bị nhứt cái đầu đó
Quá khó ấy ạ. Tạm thời em copy cái công thức này để giải quyết công việc cái đã.
 

hue xoan

Thành viên mới
Tham gia ngày
6 Tháng bảy 2016
Bài viết
13
Được thích
2
Điểm
165
Mình dùng hàm này nhưng không thể kết hợp được 2 điều kiện được. Bạn nào giúp mình với nhe
- Tôi muốn có một công thức thế này: nếu A1:A10 = "X" va B1:b10="Y" thì kq sẽ là : tổng từ c1:C10 thoả mãn 2 đk trên
bạn dùng thử: =sumifs(C1:C10,A1:A10,"X",B1:B10,"Y")
 

hue xoan

Thành viên mới
Tham gia ngày
6 Tháng bảy 2016
Bài viết
13
Được thích
2
Điểm
165
giúp lập công thức

giúp mình lập công thức thống kê tổng số người theo từng loại thâm niên công tác,theo từng bộ phận nữa. Bảng dữ liệu ở sheet1, bảng tổng hợp ở sheet 2 . mình đã ghi chú trong file đính kèm
hepl meeeeeeeeeeeeeee
Xin cảm ơn-=09=+-+-+-+-0-/.
Em dùng countifs, anh xem hữu ích và đơn giản hơn ko:
 

File đính kèm

echo07

Nguyệt Hà
Tham gia ngày
24 Tháng tám 2007
Bài viết
265
Được thích
193
Điểm
695
Nơi ở
Đối diện Gpe
Chào các bạn.
Tôi có vấn đề mới cần hỗ trợ chỗ tính thời gian lũy kế cho các hạng mục khác nhau, đã sử dụng công thức mảng nhưng kết quả không như ý muốn, rất mong anh chị em giúp đỡ. Chi tiêt vấn đề được nêu trong file đính kèm. Cảm ơn và chúc cả nhà cuối tuần vui vẻ.
 

File đính kèm

Tham gia ngày
10 Tháng mười 2017
Bài viết
2,885
Được thích
8,534
Điểm
360
Nơi ở
Sài Gòn
Chào các bạn.
Tôi có vấn đề mới cần hỗ trợ chỗ tính thời gian lũy kế cho các hạng mục khác nhau, đã sử dụng công thức mảng nhưng kết quả không như ý muốn, rất mong anh chị em giúp đỡ. Chi tiêt vấn đề được nêu trong file đính kèm. Cảm ơn và chúc cả nhà cuối tuần vui vẻ.
Vấn đề quá hay! Truy lục ngày trùng theo từng mảng, nhưng với số giờ khác nhau, rồi tổng cộng cho 6 ngày, hoặc tính cho cả quá trình hoạt động.
Công thức tính số giờ của ngày trong tuần của bạn chưa đúng, nên kết quả sai.

Nhưng sáng nay tôi bận rồi, bạn chịu khó chờ nha, sẽ có nhiều anh em hỗ trợ bạn nhiều cách thú vị.

Chúc bạn ngày vui
 

echo07

Nguyệt Hà
Tham gia ngày
24 Tháng tám 2007
Bài viết
265
Được thích
193
Điểm
695
Nơi ở
Đối diện Gpe
Vấn đề quá hay! Truy lục ngày trùng theo từng mảng, nhưng với số giờ khác nhau, rồi tổng cộng cho 6 ngày, hoặc tính cho cả quá trình hoạt động.
Công thức tính số giờ của ngày trong tuần của bạn chưa đúng, nên kết quả sai.

Nhưng sáng nay tôi bận rồi, bạn chịu khó chờ nha, sẽ có nhiều anh em hỗ trợ bạn nhiều cách thú vị.

Chúc bạn ngày vui
Vâng, cảm ơn bác. Vấn đề là nó sai ở chỗ nào đó nên kết quả mới không đúng ạ.
 

leonguyenz

Thành viên mới
Thành viên BQT
Moderator
Tham gia ngày
2 Tháng tám 2010
Bài viết
4,600
Được thích
8,102
Điểm
910
Nơi ở
Bình Dương
Vấn đề quá hay! Truy lục ngày trùng theo từng mảng, nhưng với số giờ khác nhau, rồi tổng cộng cho 6 ngày, hoặc tính cho cả quá trình hoạt động.
Công thức tính số giờ của ngày trong tuần của bạn chưa đúng, nên kết quả sai.

Nhưng sáng nay tôi bận rồi, bạn chịu khó chờ nha, sẽ có nhiều anh em hỗ trợ bạn nhiều cách thú vị.

Chúc bạn ngày vui
Gãi đúng chỗ ngứa của thầy rồi :D
Mà em thấy không ổn ở chỗ thời gian cột B, C so không cố định, tính toán kiểu quy hồi, gần với bài tính thời gian định cư liên tục 5 năm của anh huuthangbd :)
 

echo07

Nguyệt Hà
Tham gia ngày
24 Tháng tám 2007
Bài viết
265
Được thích
193
Điểm
695
Nơi ở
Đối diện Gpe
Gãi đúng chỗ ngứa của thầy rồi :D
Mà em thấy không ổn ở chỗ thời gian cột B, C so không cố định, tính toán kiểu quy hồi, gần với bài tính thời gian định cư liên tục 5 năm của anh huuthangbd :)
Không cố định có thể hiểu là mình có một hợp đồng gồm nhiều gói, mỗi gói có thời gian thực hiện khác nhau và số giờ công cho phép khác nhau nhưng tổng thời gian thực hiện và tổng số giờ được phép sử dụng thì cố định.
 

tam888

Thành viên tích cực
Tham gia ngày
22 Tháng tám 2013
Bài viết
840
Được thích
503
Điểm
560
Gãi đúng chỗ ngứa của thầy rồi :D
Mà em thấy không ổn ở chỗ thời gian cột B, C so không cố định, tính toán kiểu quy hồi, gần với bài tính thời gian định cư liên tục 5 năm của anh huuthangbd :)
Bài này excel công thức thường giải quyết tốt và dễ hiểu, nhưng phải bảng phụ
Khó ở đây là do ta muốn công thức khủng, mà khủng là tự làm khó rồi
Công thức khủng đó chỉ trông ngắn gọn, còn tốc độ tính toán thì chắc sẽ mệt.
Vẫn muốn ngắn gọn thì nên dùng VBA
 

leonguyenz

Thành viên mới
Thành viên BQT
Moderator
Tham gia ngày
2 Tháng tám 2010
Bài viết
4,600
Được thích
8,102
Điểm
910
Nơi ở
Bình Dương
Bài này excel công thức thường giải quyết tốt và dễ hiểu, nhưng phải bảng phụ
Khó ở đây là do ta muốn công thức khủng, mà khủng là tự làm khó rồi
Bài này em nghĩ là dạng khó.
Dữ liệu có trùng ngày, tuần (cột B, C), không biết chọn dòng nào phù hợp (đưa ra 1 tập hợp số, cột E), phân bổ tập hợp số đó thỏa điều kiện thời gian tăng dần (theo tuần) và tổng bằng số cho trước (được tính ra ở F11).
 

tam888

Thành viên tích cực
Tham gia ngày
22 Tháng tám 2013
Bài viết
840
Được thích
503
Điểm
560
Bài này em nghĩ là dạng khó.
Dữ liệu có trùng ngày, tuần (cột B, C), không biết chọn dòng nào phù hợp (đưa ra 1 tập hợp số, cột E), phân bổ tập hợp số đó thỏa điều kiện thời gian tăng dần (theo tuần) và tổng bằng số cho trước (được tính ra ở F11).
Cứ tính từng Hoạt động theo tuần (cộng dồn), thì được bảng các thời gian tuần cho các hoạt động, tổng cột, rồi tổng tất cả là được kết quả mong muốn.

Tính từng hoạt động theo tuần (liệt kê) thì dễ dàng rồi xét khoảng thời gian hiệu theo số ngày trong tuần (min, max ngày) thì bạn tính tốt dễ dàng.

==> cần 1 bảng phụ
 

echo07

Nguyệt Hà
Tham gia ngày
24 Tháng tám 2007
Bài viết
265
Được thích
193
Điểm
695
Nơi ở
Đối diện Gpe
Cứ tính từng Hoạt động theo tuần (cộng dồn), thì được bảng các thời gian tuần cho các hoạt động, tổng cột, rồi tổng tất cả là được kết quả mong muốn.

Tính từng hoạt động theo tuần (liệt kê) thì dễ dàng rồi xét khoảng thời gian hiệu theo số ngày trong tuần (min, max ngày) thì bạn tính tốt dễ dàng.

==> cần 1 bảng phụ
Em nghĩ cũng không quá khó với các bác đâu ạ. Nếu thử kéo dài thời gian kết thúc ra đến ngày 7/2/20 thì sai số theo em tính chỉ là 0.26%. Đấy là phải tính thêm số giờ theo ngày và theo tuần rồi mới cộng lũy kế lại.
Vấn đề em muốn là chỉ cần dựa vào cột F (Số giờ cho phép) và các cột C và D để rải vào các ngày từ lúc bắt đầu đến lúc kết thúc theo dạng lũy kế như ở dòng số 4 thôi và mục tiêu cuối cùng là đường biểu đồ vừa lên đúng với số giờ cho phép.
217951
 

File đính kèm

tam888

Thành viên tích cực
Tham gia ngày
22 Tháng tám 2013
Bài viết
840
Được thích
503
Điểm
560
Em nghĩ cũng không quá khó với các bác đâu ạ. Nếu thử kéo dài thời gian kết thúc ra đến ngày 7/2/20 thì sai số theo em tính chỉ là 0.26%. Đấy là phải tính thêm số giờ theo ngày và theo tuần rồi mới cộng lũy kế lại.
Vấn đề em muốn là chỉ cần dựa vào cột F (Số giờ cho phép) và các cột C và D để rải vào các ngày từ lúc bắt đầu đến lúc kết thúc theo dạng lũy kế như ở dòng số 4 thôi và mục tiêu cuối cùng là đường biểu đồ vừa lên đúng với số giờ cho phép.
View attachment 217951
Xem file kèm
Với File thứ 2 thì bạn tự làm theo, và lưu ý kéo ngày đúng tuần +7 (file 2 phải kéo đến tháng 3)
 

File đính kèm

Tham gia ngày
10 Tháng mười 2017
Bài viết
2,885
Được thích
8,534
Điểm
360
Nơi ở
Sài Gòn
Gãi đúng chỗ ngứa của thầy rồi :D
Mà em thấy không ổn ở chỗ thời gian cột B, C so không cố định, tính toán kiểu quy hồi, gần với bài tính thời gian định cư liên tục 5 năm của anh huuthangbd :)
Sáng nhìn vội, nên cũng hình dung trong đầu y vậy. Nhưng vừa về xem lại thì có lẽ nó nhẹ nhàng hơn tí!
Vấn đề em muốn là chỉ cần dựa vào cột F (Số giờ cho phép) và các cột C và D để rải vào các ngày từ lúc bắt đầu đến lúc kết thúc theo dạng lũy kế như ở dòng số 4 thôi và mục tiêu cuối cùng là đường biểu đồ vừa lên đúng với số giờ cho phép.
Chắc là công thức này:
Mã:
I4=SUMPRODUCT((I1+COLUMN($A:$F)-1>=$B$2:$B$10)*(I1+COLUMN($A:$F)-1<=$C$2:$C$10)*$E$2:$E$10)
Chỉ Enter, rồi fill qua phải.

Thân
 

File đính kèm

HieuCD

Chuyên gia GPE
Tham gia ngày
14 Tháng chín 2010
Bài viết
7,009
Được thích
13,677
Điểm
1,560
Sáng nhìn vội, nên cũng hình dung trong đầu y vậy. Nhưng vừa về xem lại thì có lẽ nó nhẹ nhàng hơn tí!

Chắc là công thức này:
Mã:
I4=SUMPRODUCT((I1+COLUMN($A:$F)-1>=$B$2:$B$10)*(I1+COLUMN($A:$F)-1<=$C$2:$C$10)*$E$2:$E$10)
Chỉ Enter, rồi fill qua phải.

Thân
Chắc không phải rùi, dòng 4 là số lũy kế cộng dồn các tuần trước :)
Chúc bạn 1 tối vui
 
Tham gia ngày
10 Tháng mười 2017
Bài viết
2,885
Được thích
8,534
Điểm
360
Nơi ở
Sài Gòn
Chắc không phải rùi, dòng 4 là số lũy kế cộng dồn các tuần trước :)
Chúc bạn 1 tối vui
Vậy, cộng với ô đằng trước chắc "hợp với dáng em".
Cảm ơn anh @HieuCD.

I4=SUMPRODUCT((I1+COLUMN($A:$F)-1>=$B$2:$B$10)*(I1+COLUMN($A:$F)-1<=$C$2:$C$10)*$E$2:$E$10)+N(H4)
Enter, rồi fill qua phải.

Chúc anh ngày vui
/-*+//-*+//-*+/
 

File đính kèm

echo07

Nguyệt Hà
Tham gia ngày
24 Tháng tám 2007
Bài viết
265
Được thích
193
Điểm
695
Nơi ở
Đối diện Gpe
Vậy, cộng với ô đằng trước chắc "hợp với dáng em".
Cảm ơn anh @HieuCD.

I4=SUMPRODUCT((I1+COLUMN($A:$F)-1>=$B$2:$B$10)*(I1+COLUMN($A:$F)-1<=$C$2:$C$10)*$E$2:$E$10)+N(H4)
Enter, rồi fill qua phải.

Chúc anh ngày vui
/-*+//-*+//-*+/
Cảm ơn các bác nhiều. Tối muộn vẫn hỗ trợ anh em. Chúc các bác một tuần mới nhiều niềm vui và hạnh phúc.
 

echo07

Nguyệt Hà
Tham gia ngày
24 Tháng tám 2007
Bài viết
265
Được thích
193
Điểm
695
Nơi ở
Đối diện Gpe
Vậy, cộng với ô đằng trước chắc "hợp với dáng em".
Cảm ơn anh @HieuCD.

I4=SUMPRODUCT((I1+COLUMN($A:$F)-1>=$B$2:$B$10)*(I1+COLUMN($A:$F)-1<=$C$2:$C$10)*$E$2:$E$10)+N(H4)
Enter, rồi fill qua phải.

Chúc anh ngày vui
/-*+//-*+//-*+/
Hay quá bác ạ. Vấn đề phát sinh là dữ liệu của em có thể thay đổi nên các cột cũng vì thế thay đổi theo. Với "COLUMN($A:$F)" như này em thêm hoặc bớt cột ở trong khoảng đó đều cho kết quả bị sai. Em đã thừ thay thế bằng
=SUMPRODUCT((J1+{1,2,3,4,5,6}-1>=$A$2:$A$10)*(J1+{1,2,3,4,5,6}-1<=$B$2:$B$10)*$F$2:$F$10)+N(I5) thì có thể thay đổi số cột thoải mái. Vậy mình có thể thay thế dạng COLUMN($A:$F) này bằng dạng khác để không bị ảnh hưởng bởi số cột trong đó không ạ.
Một vấn đề nhỏ nữa là trong ví dụ này thì ngày cuối cùng kết thúc là 19/12 nhưng kết quả tính đến ngày 16/12 đã hết khối lượng rồi do đó 3 ngày sau không được tính ạ.

218011
 
Top Bottom