Phan Thế Hiệp
-⍟-
- Tham gia
- 10/10/17
- Bài viết
- 3,374
- Được thích
- 11,214
- Giới tính
- Nam
Mấy nay, đi đâu mà mất tích biền biệt vậy em gái!
Chào mừng trở lại với anh em.
SUMPRODUCT và Công thức mảng - Phép tính có nhiều điều kiện
- Thread starter Nguyễn Duy Tuân
- Ngày gửi
Chào mừng trở lại với anh em.
bebanhbeo
Thành viên thường trực
- Tham gia
- 9/10/17
- Bài viết
- 391
- Được thích
- 338
- Giới tính
- Nữ
Người không ra đi mà như trở lạiMấy nay, đi đâu mà mất tích biền biệt vậy em gái!
Chào mừng trở lại với anh em.
Cảm ơn bạn thuhuonglee, nhưng mình cần tính số ở các ô như: E2, G2, I2, K2.
Số máy ngưng hoạt động theo từng ngày.
Phan Thế Hiệp
-⍟-
- Tham gia
- 10/10/17
- Bài viết
- 3,374
- Được thích
- 11,214
- Giới tính
- Nam
Bạn thử thêm cột O chứa kết quả tổng kết "Số máy thiếu vật tư":
Mã:
O7=SUMPRODUCT(($N7<=SUMIF(OFFSET($F$5,,,,{1,3,5,7}),"SL",OFFSET($F7,,,,{1,3,5,7})))*N(OFFSET($E7,,{0,2,4,6})))Muốn theo dõi số lượng máy thiếu theo từng ngày trả kết quả tại các ô: E2, G2, I2, K2, bạn làm như sau:
Mã:
E2=SUMPRODUCT(INDEX($N$7:$N$13<=SUMIF(OFFSET($F$5,,,,{1,3,5,7}),"SL",OFFSET($F$7,ROW(1:7)-1,,,{1,3,5,7})),,COUNTA($E$4:E$4))*E$7:E$13)Xem file kèm.
Thân.
Lần chỉnh sửa cuối:
Thanks Phan Thế Hiệp,
Đúng thứ mình cần rồi bạn, nhưng vẫn không hiểu cấu trúc của hàm OFFSET cho lắm, trước giờ mình chưa áp dụng hàm này.
Phan Thế Hiệp
-⍟-
- Tham gia
- 10/10/17
- Bài viết
- 3,374
- Được thích
- 11,214
- Giới tính
- Nam
Công thức:
SUMIF( OFFSET($F$5,,,,{1,3,5,7}), "SL" , OFFSET($F$7,ROW(1:7)-1,,,{1,3,5,7}) )
giống Sumif( Vùng so, Tiêu chí so, Vùng cộng) bình thường, nhưng nhờ Mảng {1,3,5,7} mà hàm Offset() sẽ nhảy qua cách cột, để làm nhiệm vụ cộng dồn số liệu, ví dụ:
- OFFSET($F$5,,,,{1,3,5,7}): chỉ lấy các gía trị tại các ô: bắt đầu từ F5, nhảy qua 2 cột (3-1=2) lấy đến ô H5, cứ vậy lấy giá trị đến các ô J5 và L5 để so sánh với tiêu chí "SL". Bạn có thể hình dung cách thức nó hoạt động như sau: cột đầu tiên F là Sumif(F5:F5,"SL".....), cột H là Sumif(F5:H5,"SL".....), cột J là Sumif(F5:J5,"SL".....), cột L là Sumif(F5:L5,"SL".....)
- OFFSET($F$7,ROW(1:7)-1,,,{1,3,5,7}): Tương tự với cách thức trên, nó đứng từ cột F7, với giá trị đầu tiên là 1, thì nó lấy giá trị tại chỗ là =12, nhảy qua 2 cột nữa (1+2=3) tức lấy giá trị ô H7 (vì H5="SL") là =4, nó cộng dồn 12+4=16, rồi tiếp tục 5 (1+2+2=5) tức qua cột J7 là =6, nó lại cộng dồn 16+6=22, sau cùng là nhảy đến ô L7 là =8, cộng dồn 22+8=30
- Kế tiếp nó lấy vật tư tồn tại cột N7 = 14 đem so với mảng mà nó vừa cộng dồn là {12,16,22,30}, tức 14<={12,16,22,30} <=> trả về Mảng {False, True, True, True} hay {0,1,1,1} (True=1; False=0)
- Hàm OFFSET($F$7,ROW(1:7)-1,,,{1,3,5,7}) không những lấy cách cột mà nó còn chạy xuống cho hết 7 hàng như dữ liệu của bạn có. Nó sẽ tạo ra "Mảng hai chiều" gồm: hàng x cột: chứa số cộng dồn theo từng hàng dữ liệu và có 4 cột. Gom chung lại cả công thức bạn có thể hình dung nó hoạt động theo hướng sau:
- Dòng 7: Sumif(F5:F5,"SL",F7:F7), Sumif(F5:H5,"SL",F7:H7), Sumif(F5:J5,"SL",F7:J7), Sumif(F5:L5,"SL",F7:L7)
- Dòng 8: Sumif(F5:F5,"SL",F8:F8), Sumif(F5:H5,"SL",F8:H8), Sumif(F5:J5,"SL",F8:J8), Sumif(F5:L5,"SL",F8:L8)
- Dòng 9: Sumif(F5:F5,"SL",F9:F9), Sumif(F5:H5,"SL",F9:H9), Sumif(F5:J5,"SL",F9:J9), Sumif(F5:L5,"SL",F9:L9)
- ..........Đến dòng 13:....
- Tức kết quả sẽ ra 1 Mảng như sau:
- Sau đó nó đem Mảng này ra so với cột Tồn vật tư N để tạo ra Mảng hai chiều chứa giá trị thỏa hay không cột nào bị thiếu vật tư.
- Hàm Index(Mảng, ,'Thứ tự cột cần lấy') sẽ lấy ra Mảng cột, Vd: hình dạng cột F là {0;0;0;0;1;0;0} tức dòng 5 có giá trị 1 báo dòng đó bị thiếu vật tư. Tùy theo Thứ tự cột cần lấy, Ví dụ đứng tại F là 1, qua cột H là 2....., mà lấy ra Mảng điều kiện phù hợp (xem các cột 0,1 như hình trên).
- Cuối cùng, nó nhân 2 Mảng: 'Mảng điều kiện vừa tìm ra trên' x 'Cột số máy tương ứng', Ví dụ: cột F gồm hai Mảng: {0;0;0;0;1;0;0}*{6;2;5;4;5;2;6} --> kết quả cuối ={0;0;0;0;5;0;0}
- Sumproduct() làm nhiệm vụ cộng lại các số trong Mảng kết quả trên, tức = 5.
Thân
Lần chỉnh sửa cuối:
Phan Thế Hiệp
-⍟-
- Tham gia
- 10/10/17
- Bài viết
- 3,374
- Được thích
- 11,214
- Giới tính
- Nam
Thử:
Mã:
D4=SUMIFS(Data!$F$2:$F$13,Data!$B$2:$B$13,$B4,Data!$E$2:$E$13,"17Q"&COLUMN(A$1))Nếu muốn linh động về niên độ tính thuế thì cập nhật tại ô nào đó Vd: A1=01/01/2017, và thực hiện công thức trong file kèm.
Thân.
p/s: Bạn chắc đang làm công tác quản lý thuế tại Chi Cục à!?
Lasderybi
Thành viên hoạt động
- Tham gia
- 9/12/11
- Bài viết
- 191
- Được thích
- 20
Rất hay và cũng rất...khó hehe, nhưng thôi giải quyết được việc của mình là ok rồi, cám ơn anh nhiều nhé, vâng em làm thuế ở Chi cục ạ. Cho em hỏi thêm tẹo, theo anh thì cách nào hay hơn và muốn tìm hiểu thì cách nào dễ hiểu hơn ạ?
Phan Thế Hiệp
-⍟-
- Tham gia
- 10/10/17
- Bài viết
- 3,374
- Được thích
- 11,214
- Giới tính
- Nam
Tùy theo bạn thấy cái nào tiện cho công việc của bạn:
D4=SUMIFS(Data!$F$2:$F$13,Data!$B$2:$B$13,$B4,Data!$E$2:$E$13,"17Q"&COLUMN(A$1))
- Công thức này gọn dễ hiểu hơn, nhưng khi qua năm mới (Vd: 2018), bạn phải sửa lại công thức tại "17Q" thành "18Q". Tương tự cho các niên độ sau nữa.
- Công thức này bạn chỉ cần điều chỉnh niên độ mới tại A1 (Vd: gõ 01/01/2018, tương tự cho các niên độ sau) thì không cần chỉnh công thức.
- TEXT($A$1,"yy"): Ra kết quả là 2 số đuôi của năm do định dạng "yy" (Vd: 01/01/2017 ra 17)
- RIGHT(D$3): Lấy bên phải của ô D3 ra 1 ký tự, tức số của các Quý: 1,2,3,4
- TEXT($A$1,"yy")&"Q"&RIGHT(D$3) ghép các kết quả chung với chữ "Q" thành các chuỗi: Vd: "17Q1" hay "17Q2" "17Q3" "17Q4"
Arsenelupin
Thành viên mới
- Tham gia
- 16/8/10
- Bài viết
- 10
- Được thích
- 1
Chào các bạn,
Mình cần tìm tổng số lượng theo từng size của các đơn hàng. Chi tiết đơn hàng ở Sheet1, giá trị cần tính ở cột Total sheet2.
Hiện tại, mình đang dùng hàm như sau:
Nhưng hàm này quá dài và chỉ những máy sử dụng Office 365 mới dùng được. Các bạn giúp mình thay hàm trên bằng 1 hàm khác đơn giản hơn.
Xin cảm ơn!
Mình cần tìm tổng số lượng theo từng size của các đơn hàng. Chi tiết đơn hàng ở Sheet1, giá trị cần tính ở cột Total sheet2.
Hiện tại, mình đang dùng hàm như sau:
Mã:
=SUMPRODUCT(SWITCH([@Size],"36",PO[36],"38",PO[38],"40",PO[40],"42",PO[42],"44",PO[44],"46",PO[46],"48",PO[48],"XS",PO[XS],"S",PO[S],"M",PO[M],"L",PO[L],"XL",PO[XL],"2XL",PO[2XL],"3XL",PO[3XL])*(PO[MODEL]=[@Model]))Xin cảm ơn!
Phan Thế Hiệp
-⍟-
- Tham gia
- 10/10/17
- Bài viết
- 3,374
- Được thích
- 11,214
- Giới tính
- Nam
Thử:
Mã:
C2=SUMIF(Sheet1!$B$1:$B$1000,$A2,OFFSET(Sheet1!$D$1:$D$1000,,MATCH($B2,Sheet1!$E$1:$Z$1,)))hocexcel_1991
Bắt đầu học Excel
- Tham gia
- 9/7/18
- Bài viết
- 734
- Được thích
- 1,266
Thêm cách nữa
Mã:
=SUMPRODUCT((Sheet1!$B$2:$B$1000=$A2)*(Sheet1!$E$1:$T$1=$B2),Sheet1!$E$2:$T$1000)Arsenelupin
Thành viên mới
- Tham gia
- 16/8/10
- Bài viết
- 10
- Được thích
- 1
Cảm ơn bạn! Mình vẫn chưa hiểu hàm OFFSET trong hàm. Bạn có thể giải thích giúp mình được không?
Phan Thế Hiệp
-⍟-
- Tham gia
- 10/10/17
- Bài viết
- 3,374
- Được thích
- 11,214
- Giới tính
- Nam
OFFSET(Sheet1!$D$1:$D$1000,,MATCH($B2,Sheet1!$E$1:$Z$1,))
Sẽ lấy cột size thích hợp với B2 (Vd: là size "XS"), thì Offset() sẽ trả về Vùng dữ liệu với địa chỉ là L1: L1000, để cung cấp Vùng cộng cho SUMIF(), tức có thể hiểu lúc sau cùng công thức có dạng là:
=SUMIF(Sheet1!$B$1:$B$1000,$A2,Sheet1!$L$1:$L$1000)
Vận hành của Offset() như sau: đứng tại mốc Sheet1!$D$1:$D$1000, sử dụng Macth() để tìm size "XS" có cột 'thứ mấy' trong Vùng Sheet1!$E$1:$Z$1, khi tìm được nó trả về stt cột, tức cách D1 bao nhiêu cột nhằm cung cấp thông số 'cột' cho Offset() để nhảy đến lấy cả vùng đó.
Cũng có thể thay thế bằng =OFFSET(Sheet1!$D$1,,MATCH($B2,Sheet1!$E$1:$Z$1,1000)) cũng cùng ý nghĩa.
Dùng hàm này là phù hợp với 'chủ đề' ở đây, tuy nhiên tôi muốn lưu ý với bạn dùng Sumproduct() là tập họp 1 mảng 2 chiều, do đó nó chiếm khá nhiều bộ nhớ. Nếu dữ liệu càng lớn, thì bạn sẽ thấy nó làm trì trệ tốc độ xử lý của máy tính.
Bạn thử hình dung như sau với công thức của bạn có khoảng 50 dòng x 14 cột = 700 ô dữ liệu, nếu dòng dữ liệu khoảng 1000 (như công thức Sumif() tôi đưa Vùng vào) thì nó vào khoảng 14.000 ô dữ liệu chiếm trong bộ nhớ.
Với Sumif() do dữ liệu được ghi nhận bởi Vùng (vùng so khớp, và vùng cộng đã ghi nhận vào ô hiện hữu trên bảng tính, ước lượng cao lắm cũng thao tác trong khoảng 2000 ô mà thôi), sau khi xác định rõ Vùng thì máy tính xử lý theo dòng dữ liệu nên nó ít chiếm bộ nhớ của máy tính, vì vậy tốc độ xử lý sẽ nhanh hơn.
Cũng là một dịp chia sẻ để biết cái lợi và hại của Sumproduct().
Thân.
Tôi nghĩ không nên bắt chước ai cả. Nếu không dùng các hàm volatile mà công thức không phức tạp hơn thì không nên dùng các hàm volatile. Chỉ dùng khi hết cách - tức khi cách khác thì phức tạp hơn nhiều.
Dùng volatile thì các công thức luôn được tính lại mặc dù tham chiếu không đổi.
Đây là tôi nói về nguyên tắc, về tập cho mình một thói quen
Dùng volatile thì các công thức luôn được tính lại mặc dù tham chiếu không đổi.
Đây là tôi nói về nguyên tắc, về tập cho mình một thói quen
Mã:
=SUMIF(Sheet1!$B$1:$B$1000,$A2,INDEX(Sheet1!$E$1:$Z$1000,,MATCH($B2,Sheet1!$E$1:$Z$1,0)))hocexcel_1991
Bắt đầu học Excel
- Tham gia
- 9/7/18
- Bài viết
- 734
- Được thích
- 1,266
Cám ơn anh đã chia sẽ những thông tin hữu ích
Chúc anh vui vẻ
honey_552911
Thành viên mới
- Tham gia
- 12/4/12
- Bài viết
- 24
- Được thích
- 1
Mọi người giúp mình file này với ạ.
Mình muốn cộng số lượng, theo điều kiện loại giày (thấp cổ, cao cổ, cổ lỡ, đế cao) và theo kích thước hộp giày, nhóm size.
Chi tiết trong File đính kèm.
Cảm ơn cả nhà
Mình muốn cộng số lượng, theo điều kiện loại giày (thấp cổ, cao cổ, cổ lỡ, đế cao) và theo kích thước hộp giày, nhóm size.
Chi tiết trong File đính kèm.
Cảm ơn cả nhà
Phan Thế Hiệp
-⍟-
- Tham gia
- 10/10/17
- Bài viết
- 3,374
- Được thích
- 11,214
- Giới tính
- Nam
Sheet "THĐH":
- Cột H: không hiểu bạn lấy kết quả từ đâu ra nên không làm.
- Từ Cột O đến AE:
Enter, fill sang phải đến AE, rồi fill cả hàng xuống.Mã:
O4=IF($E4=LOOKUP("zzz",$O$1:O$1),SUMPRODUCT((MATCH(ĐH!$K$2:$AQ$2*1,IFERROR(MID(O$2,{0,1,4},2)+{0,0,1},))=2)*OFFSET(ĐH!$K$2:$AQ$2,LOOKUP(2,1/($F4=ĐH!$D$3:$D$500)/($G4=ĐH!$E$3:$E$500),ROW($1:$500)),)),) Hoặc O4=IF($E4=LOOKUP("zzz",$O$1:O$1),SUMIFS(OFFSET(ĐH!$K$2:$AQ$2,LOOKUP(2,1/($F4=ĐH!$D$3:$D$500)/($G4=ĐH!$E$3:$E$500),ROW($1:$500)),),ĐH!$K$2:$AQ$2,">="&LEFT(O$2,2),ĐH!$K$2:$AQ$2,"<="&RIGHT(O$2,2)),)
Lần chỉnh sửa cuối:
yeskukumalu
Thành viên mới
- Tham gia
- 24/3/15
- Bài viết
- 1
- Được thích
- 0
Mình dùng hàm sumproduct, sau đó dùng hàm large(cột kết quả của hàm sumproduct, 1), rồi large(cột kết quả của hàm sumproduct, 3) thì kết quả của hàm large ra như nhau, cho dù thay đổi tham số thứ hai của hàm large thì kết quả vẫn như khi large(cột kết quả của hàm sumproduct, 1). Mình không hiểu tại sao, mong nhận được ý kiến của mọi người ạ. Mình cảm ơn !
