Cách thiết lập nhanh cho hàm SUMIF (1 người xem)

[AnhThu-1976]

Nhờ các anh chị thiết lập nhanh cho hàm SUMIF như sau:
Tại cột R ở các dòng màu vàng là trống
Hỏi cách thiết lập nhanh (công thức hoặc code) cho các ô màu vàng ở cột R của File đính kèm
Em cảm ơn

 

[khi ta 20]

Nhờ các anh chị thiết lập nhanh cho hàm SUMIF như sau:
Tại cột R ở các dòng màu vàng là trống
Hỏi cách thiết lập nhanh (công thức hoặc code) cho các ô màu vàng ở cột R của File đính kèm
Em cảm ơn

Bỏ các dòng màu vàng đó đi. Dùng Pivot table cũng được mà anh.Trên cùng 1 cột làm sao vừa số vừa công thức được anh ?

 

[AnhThu-1976]

Bỏ các dòng màu vàng đó đi. Dùng Pivot table cũng được mà anh.Trên cùng 1 cột làm sao vừa số vừa công thức được anh ?

Bạn có thể thử = Pivot giùm tôi

 

[hocexcel_1991]

Nhờ các anh chị thiết lập nhanh cho hàm SUMIF như sau:
Tại cột R ở các dòng màu vàng là trống
Hỏi cách thiết lập nhanh (công thức hoặc code) cho các ô màu vàng ở cột R của File đính kèm
Em cảm ơn

Bạn tham khảo link sau bài #20 nhé
https://www.giaiphapexcel.com/diendan/threads/tìm-cách-tính-tổng-nhiều-lần-trong-bảng-tính.133195/#post-842103

 

[nguyenthuy13388]

với dữ liệu đó tạm thời dùng công thức này:
S4 =IF(LEN(M4)<7,"",SUMIFS(R:R,N:N,N4,M:M,">="&ABS(LEFT(M:M,3)),M:M,"<="&ABS(RIGHT(M:M,3))))
sau đó kéo xuống
p/s: nếu số hóa đơn có 4 chữ số thì thay đổi công thức đi 1 chút

 

[AnhThu-1976]

Bạn tham khảo link sau bài #20 nhé
https://www.giaiphapexcel.com/diendan/threads/tìm-cách-tính-tổng-nhiều-lần-trong-bảng-tính.133195/#post-842103

Cảm ơn bạn, bài này tính SUMIF theo điều kiện là "KMVL" chứ không tính SUM thông thường

 

[phulien1902]

Nhờ các anh chị thiết lập nhanh cho hàm SUMIF như sau:
Tại cột R ở các dòng màu vàng là trống
Hỏi cách thiết lập nhanh (công thức hoặc code) cho các ô màu vàng ở cột R của File đính kèm
Em cảm ơn

Thử:

Sub abc()
    Dim c As Range
    For Each c In Columns("R").SpecialCells(2, 1).Areas
        With c(c.Count + 1)
            .Formula = "=sum(" & c.Address & ")"
            .EntireRow.Font.Bold = True
            .EntireRow.Font.ColorIndex = 3
        End With
    Next
End Sub

 

[AnhThu-1976]

Thử:

Sub abc()
    Dim c As Range
    For Each c In Columns("R").SpecialCells(2, 1).Areas
        With c(c.Count + 1)
            .Formula = "=sum(" & c.Address & ")"
            .EntireRow.Font.Bold = True
            .EntireRow.Font.ColorIndex = 3
        End With
    Next
End Sub

Cảm ơn bạn chưa chính xác bạn ạ, vì phải tính là SUMIF do điều kiện Mã là BKVL mà nó còn có các mã khác như B0210, ...

 

[khi ta 20]

Bạn có thể thử = Pivot giùm tôi

Anh nhập số HĐ từ .... đến và chọn mã KH

 

[LamNA]

Bạn có thể thử = Pivot giùm tôi

Anh thử pivot này
Chọn ký hiệu hóa đơn cần xem bấm phím Shift đến hóa đơn cuối cùng

 

[AnhThu-1976]

Cám ơn các bạn, mình thấy cách Pivot nó chưa thuận tiện cho công việc của mình, bởi vì mình muốn nó thể hiện kết quả trên bản tính mình luôn để từ đó mình tiếp tục xử lý
Cho hỏi còn cách nào khác không, ví dụ như công thức hay code

 

[LamNA]

Cám ơn các bạn, mình thấy cách Pivot nó chưa thuận tiện cho công việc của mình, bởi vì mình muốn nó thể hiện kết quả trên bản tính mình luôn để từ đó mình tiếp tục xử lý
Cho hỏi còn cách nào khác không, ví dụ như công thức hay code

Khổ cái là dữ liệu của anh lúc lấy dữ liệu "thu tiền bán hàng" 3 ngày, lúc lấy 4 ngày, ... nên khó làm đó anh

 

[AnhThu-1976]

Khổ cái là dữ liệu của anh lúc lấy dữ liệu "thu tiền bán hàng" 3 ngày, lúc lấy 4 ngày, ... nên khó làm đó anh

Như vậy, nếu lấy thu tiền bán hàng là 4 ngày thì sao, nhưng việc thu tiền phải kết thúc trong tháng
Ví dụ: tháng 6 có 30 ngày, có 2 ngày không bán hàng là 03/06/18 & 12/06/18, như vậy trong tháng 6 chỉ bán hàng 28 ngày => thu 7 đợt , nhưng giả sử tháng 6 bán hàng đầy đủ các ngày trong tháng thì xử lý thế nào? vì thu 7 đợt x 4 ngày/ đợt = 28 ngày => còn 2 ngày lẻ (như vậy đợt 8 phải thu 2 ngày lẻ để kết thúc trong tháng)
Nhờ anh/chị và các bạn giúp giùm. Xin cảm ơn

 

[LamNA]

Như vậy, nếu lấy thu tiền bán hàng là 4 ngày thì sao, nhưng việc thu tiền phải kết thúc trong tháng
Ví dụ: tháng 6 có 30 ngày, có 2 ngày không bán hàng là 03/06/18 & 12/06/18, như vậy trong tháng 6 chỉ bán hàng 28 ngày => thu 7 đợt , nhưng giả sử tháng 6 bán hàng đầy đủ các ngày trong tháng thì xử lý thế nào? vì thu 7 đợt x 4 ngày/ đợt = 28 ngày => còn 2 ngày lẻ (như vậy đợt 8 phải thu 2 ngày lẻ để kết thúc trong tháng)
Nhờ anh/chị và các bạn giúp giùm. Xin cảm ơn

Chắc anh phải đợi anh chị từng xử lý vụ này rồi.
Nếu không cố định như anh nói trên em nghĩ nên làm thủ công cứ xác định dòng cuối phải thu chèn thêm dòng như file của anh.

 

[dazkangel]

Nhờ các anh chị thiết lập nhanh cho hàm SUMIF như sau:
Tại cột R ở các dòng màu vàng là trống
Hỏi cách thiết lập nhanh (công thức hoặc code) cho các ô màu vàng ở cột R của File đính kèm
Em cảm ơn

Thử:

=SUMPRODUCT(OFFSET(R$3,MATCH(--LEFT(M28,3),M$4:M28,0),):R27)

Hoặc:

=SUMIFS(R$4:R27,M$4:M27,">="&LEFT(M28,3),M$4:M27,"<="&RIGHT(M28,3))

 

[AnhThu-1976]

Thử:

=SUMPRODUCT(OFFSET(R$3,MATCH(--LEFT(M28,3),M$4:M28,0),):R27)

Hoặc:

=SUMIFS(R$4:R27,M$4:M27,">="&LEFT(M28,3),M$4:M27,"<="&RIGHT(M28,3))

Sử dụng nó làm sao vậy bạn, mình cóp công thức vào thì chỉ có ô màu vàng đầu tiên cột R (đang dùng hàm sumif) là đúng còn các ô màu vàng còn lại của cột R thì không đúng

 

[dazkangel]

Sử dụng nó làm sao vậy bạn, mình cóp công thức vào thì chỉ có ô màu vàng đầu tiên cột R (đang dùng hàm sumif) là đúng còn các ô màu vàng còn lại của cột R thì không đúng

Bạn xem file, nhớ mở chế độ tính toán automatic:

 

[AnhThu-1976]

Bạn xem file, nhớ mở chế độ tính toán automatic:

1/ Kết quả vẫn chua đúng bạn ơi, cụ thể ô R277 theo công thức của bạn là 209.789.230 nhưng tính đúng theo sumif là 138.653.207 nó chênh lệch vì ở ô N249 và N250 mã của nó là B0210 chứ không phải mã KBVL
2/ Bạn dùng số hóa đơn để dò thì nhiều lúc sẽ sai, vì
a/ có thể số hóa đơn là 1, 2 hay 3 chữ số , tuy nhiên cái này sửa được
b/ Có thể số hóa đơn được in lại và vô tình nó trùng, giả sử trong cột hóa đơn có 2 số hoá đơn cùng số 500, cái này thì chịu!

 

[Phan Thế Hiệp]

1/ Kết quả vẫn chua đúng bạn ơi, cụ thể ô R277 theo công thức của bạn là 209.789.230 nhưng tính đúng theo sumif là 138.653.207 nó chênh lệch vì ở ô N249 và N250 mã của nó là B0210 chứ không phải mã KBVL
2/ Bạn dùng số hóa đơn để dò thì nhiều lúc sẽ sai, vì
a/ có thể số hóa đơn là 1, 2 hay 3 chữ số , tuy nhiên cái này sửa được
b/ Có thể số hóa đơn được in lại và vô tình nó trùng, giả sử trong cột hóa đơn có 2 số hoá đơn cùng số 500, cái này thì chịu!

Ý bạn muốn làm chuẩn 1 dòng tổng cộng, sau đó copy xuống dưới, ngưng dòng nào thì cộng đoạn phía trên!?
Nếu đúng vậy, thì làm công thức chuẩn cho dòng 28:

A28="AAA"
I28=I27
M28=IFERROR(LOOKUP(2,1/("AAA"=$A$3:$A27),M$4:$M28),$M$4)&"-"&M27
N28="BKVL"
O28="Thu tiền bán hàng từ: "&TEXT(IFERROR(LOOKUP(2,1/("AAA"=$A$3:$A27),I$4:$I28),$I$4),"dd/mm")&"→ "&TEXT(I27,"dd/mm")
P28="1111"
Q28="131"
R28=SUMIF(OFFSET(N$1,IFERROR(LOOKUP(2,1/("AAA"=$A$3:$A27),ROW($A$3:$A27)),3),):N27,"BKVL",OFFSET(R$1,IFERROR(LOOKUP(2,1/("AAA"=$A$3:$A27),ROW($A$3:$A27)),3),):R27)

Enter. Rồi copy cho các dòng bạn muốn làm "Tổng cộng theo đoạn"
Xem file kèm.
Thân

 

[batman1]

Công thức cho R28

=SUMIF(INDEX(N$1:N27,LOOKUP("zzz",A$3:A27,ROW(A$3:A27))+1):N27,"BKVL",INDEX(R$1:R27,LOOKUP("zzz",A$3:A27,ROW(A$3:A27))+1):R27)

Kết thúc bằng Enter.
Chọn R28 -> Ctrl+C -> chọn từng ô vàng ở cột R -> Ctrl+V

 

[AnhThu-1976]

Enter. Rồi copy cho các dòng bạn muốn làm "Tổng cộng theo đoạn"

Bạn đã làm thêm các công thức khác, cảm ơn bạn rất nhiều

Chọn R28 -> Ctrl+C -> chọn từng ô vàng ở cột R -> Ctrl+V

Nếu paste cho từng ô màu vàng thì lâu, em filter màu vàng rồi paste công thức hàng loạt, em cảm ơn anh nhiều

 

[khi ta 20]

Bạn đã làm thêm các công thức khác, cảm ơn bạn rất nhiều

Nếu paste cho từng ô màu vàng thì lâu, em filter màu vàng rồi paste công thức hàng loạt, em cảm ơn anh nhiều

Em nghĩ cách này không khả thi. Có cần phải phức hóa quá lên không ?
Sao không để vùng dữ liệu chuẩn thuần túy. Rồi thiết kế vùng tính toán riêng theo tùy biến mã hàng và khách hàng.
Còn dể sao này cho anh sử dụng nữa. Vui vẻ nha anh .!

 

[Phan Thế Hiệp]

Bạn đã làm thêm các công thức khác, cảm ơn bạn rất nhiều

Lưu ý cho bạn sử dụng:
1/ Ý chính trong công thức phân đoạn: là khi dòng nào có ký tự "AAA" trong cột "Nhóm", thì dòng đó vừa làm nhiệm vụ chứa công thức tổng cộng tại cột R, vừa làm 'Mốc' phân định cho 'đoạn sau'. Vì vậy, ô liền kề trên nó chính là 'Mốc cuối đoạn' nó phải cộng, và ô liền kề dưới nó phải là 'Mốc đầu đoạn sau'. Chính vì vậy bạn có thể thay ký tự "AAA" bằng chữ khác như "Subtotal", "Cộng đoạn",..v.v vào ô cột A (công thức A28="ký tự bạn muốn làm Mốc"), thì tự khắc công thức sẽ lấy đó làm 'Mốc' cho bạn, và cái quan trọng nhất là nó "bất chấp" trong các dòng khác của cột A có hay không có dữ liệu, ví dụ: bạn phân thêm nhóm nhỏ "aa1", "ab1", "cc1", hoặc số thứ tự: 1,2,3,4,5.... từ dòng 4,5,6,7,..27; Thậm chí ai đó sử dụng bảng tính này và vô tình (hay cố ý) để rơi rớt đâu đó trong cột A vài khoảng trắng " " mà bằng 'mắt thường' bạn không thấy được, thì cũng chẳng sao, vì nó "phớt lờ tất cả", chỉ biết có "AAA" mà bạn đã quy định (bạn điều khiển được) làm mốc cho các đoạn bạn cần cộng. Bạn nên cẩn trọng vì làm nghề của mình có câu :"Sai con toán, bán con trâu"

2/ Sau khi đã có chuẩn 1 dòng, bạn có thể Filter cột A: chọn "AAA", Copy dòng chuẩn, xong chọn toàn bộ dòng tô màu vàng phía dưới chưa có công thức, nhấn Alt+";", xong Ctrl+V.

Thân.