Tính tổng theo mã tham chiếu trong vùng (1 người xem)

[baggiotung]

Các bạn giúp mình công thức cho cột tổng công với, dùng sumproduct phải 13if. Dùng VBA cũng đc. Cám ơn

 

[Phuocam]

Các bạn giúp mình công thức cho cột tổng công với, dùng sumproduct phải 13if. Dùng VBA cũng đc. Cám ơn

Bạn cho mình coi công thức sumproduct + 13if với, trong file không có?

 

[baggiotung]

Bạn cho mình coi công thức sumproduct + 13if với, trong file không có?

Hic, mình đang đi hỏi mà

 

[Phuocam]

Hic, mình đang đi hỏi mà

Mình đang muốn học cách kết hợp dùng 13 if với sumproduct của bạn.

 

[Loc Uyen]

Các bạn giúp mình công thức cho cột tổng công với, dùng sumproduct phải 13if. Dùng VBA cũng đc. Cám ơn

Hihi. Em mạo muội viết thử hàm này, Hen^ xui. ,có sumproduct mà ko có được 13 if. .
N6=SUMPRODUCT((D6:M6)*(INDIRECT(ADDRESS(MATCH(B6,$S$5:$S$18,0)+4,20)&":"&ADDRESS(MATCH(B6,$S$5:$S$18,0)+4,29))))

 

[Nguyễn Hồng Quang]

Các bạn giúp mình công thức cho cột tổng công với, dùng sumproduct phải 13if. Dùng VBA cũng đc. Cám ơn

Bạn sửa lại tiêu đề nhé, kẻo bị xóa bài .Có thể Sửa là" Tính tổng theo mã tham chiếu trong vùng"
N6=SUMPRODUCT(D6:M6;OFFSET($R$4;MATCH(B6;$S$4:$S$18;0)-1;2;;10))

 

[Loc Uyen]

Bạn sửa lại tiêu đề nhé, kẻo bị xóa bài .Có thể Sửa là" Tính tổng theo mã tham chiếu trong vùng"
N6=SUMPRODUCT(D6:M6;OFFSET($R$4;MATCH(B6;$S$4:$S$18;0)-1;2;;10))

Offset gọn hơn, lúc nãy em không nghĩ đến. hihi

 

[Nguyễn Hồng Quang]

Offset gọn hơn, lúc nãy em không nghĩ đến. hihi

Uh. Học được Offset của a. PTHiệp là mình áp dùng liền , vậy mới nhớ lâu được

 

[baggiotung]

Bạn sửa lại tiêu đề nhé, kẻo bị xóa bài .Có thể Sửa là" Tính tổng theo mã tham chiếu trong vùng"
N6=SUMPRODUCT(D6:M6;OFFSET($R$4;MATCH(B6;$S$4:$S$18;0)-1;2;;10))

cám ơn bạn nhiều nha.

 

[Nguyễn Hồng Quang]

cám ơn bạn nhiều nha.

Sửa tiêu đề nha đi bạn

 

[Lê Hồng Minh83]

Bạn sửa lại tiêu đề nhé, kẻo bị xóa bài .Có thể Sửa là" Tính tổng theo mã tham chiếu trong vùng"
N6=SUMPRODUCT(D6:M6;OFFSET($R$4;MATCH(B6;$S$4:$S$18;0)-1;2;;10))

Cái này mới phát huy quái chiêu của SUMPRODUCT nè

N6=SUMPRODUCT(D6:M6*($S$5:$S$18=B6)*$T$5:$AC$18)

 

[SA_DQ]

Làm 1 UDF để cùng tham khảo:

Function TraTC(Mã As String, Rng As Range) As Double
Dim BTra As Range, Cls As Range, TraSL As Range
Dim W As Integer

Set BTra = Range("DMuc")           ' Vùng Bang Dinh Múc Duoc Gán Tên '
For Each Cls In BTra(1).Resize(BTra.Rows.Count)
    If Cls.Value = Mã Then
        Set TraSL = Cls.Offset(, 1).Resize(, 10):            Exit For
    End If
Next Cls
For W = 1 To Rng.Columns.Count
    TraTC = TraTC + Rng(W).Value * TraSL(W).Value
Next W
End Function

 

[khi ta 20]

Cái này mới phát huy quái chiêu của SUMPRODUCT nè

N6=SUMPRODUCT(D6:M6*($S$5:$S$18=B6)*$T$5:$AC$18)

Công thức này tuyệt cú mèo

 

[Phan Thế Hiệp]

@Lê Hồng Minh83; @Nguyễn Hồng Quang; @khi ta 20; @Loc Uyen; @saobekhonglac

Nâng cấp bài toán cho vui: Nếu các cột sắp xếp không theo thứ tự thì mình phải xử lý bằng công thức ra sao!?
Xem sheet: 'vidu bai toan(bai2)'

Chúc anh em ngày vui.
Thân

 

[Lê Hồng Minh83]

@Lê Hồng Minh83; @Nguyễn Hồng Quang; @khi ta 20; @Loc Uyen; @saobekhonglac

Nâng cấp bài toán cho vui: Nếu các cột sắp xếp không theo thứ tự thì mình phải xử lý bằng công thức ra sao!?
Xem sheet: 'vidu bai toan(bai2)'

Chúc anh em ngày vui.
Thân

Quan điểm của em là chọn phương pháp đơn giản hơn để làm, không phức tạp hóa vấn đề
Nên vấn đề này chỉ để nghiên cứu và giải trí thì được, áp dụng thực tế là em không chơi ah (áp dụng thực tế chỉ cần sort lại dữ liệu là được thôi mà)

 

[Nguyễn Hồng Quang]

@Lê Hồng Minh83; @Nguyễn Hồng Quang; @khi ta 20; @Loc Uyen; @saobekhonglac

Nâng cấp bài toán cho vui: Nếu các cột sắp xếp không theo thứ tự thì mình phải xử lý bằng công thức ra sao!?
Xem sheet: 'vidu bai toan(bai2)'

Chúc anh em ngày vui.
Thân

Gửi anh ,nghĩ tới nghĩ lui, em tìm ra công thức cơ bản sẽ là. Em đang làm tiếp phần bôi màu đỏ còn lại là xong, cảm ơn anh đã bổ túc thêm kiến thức cho anh em
N6=SUMPRODUCT(SUMIF($T$4:$AC$4;$D$4:$M$4;T5:AC5)*D6:M6)

 

[Phan Thế Hiệp]

Quan điểm của em là chọn phương pháp đơn giản hơn để làm, không phức tạp hóa vấn đề
Nên vấn đề này chỉ để nghiên cứu và giải trí thì được, áp dụng thực tế là em không chơi ah (áp dụng thực tế chỉ cần sort lại dữ liệu là được thôi mà)

Đúng vậy em! nếu đưa về dạng chuẩn thì rất tốt không có vấn đề gì bàn thêm.
Nếu như sort dòng thì dễ, chứ sort cột thì coi bộ phải qua nhiều công đoạn nữa mới tạo thành chuẩn thống nhất của hai bảng.
Trong thực tế, nhiều khi vô tình người nhập dữ liệu vào bảng hay tùy thích chèn thêm cột, thêm dòng, cho nên thứ tự bị xáo trộn.

Vì vậy, anh đưa trường hợp này cũng để cho anh em cái nhìn thoáng hơn và có phương án xử lý nhanh gọn hơn là sort cột.

Kệ! cứ coi như bài nâng cao công thức cho vui.

Chúc anh em ngày vui.

----------------------------------------------------------------------------------------------------------------------------

Gửi anh ,nghĩ tới nghĩ lui, em tìm ra công thức cơ bản sẽ là. Em đang làm tiếp phần bôi màu đỏ còn lại là xong, cảm ơn anh đã bổ túc thêm kiến thức cho anh em
N6=SUMPRODUCT(SUMIF($T$4:$AC$4;$D$4:$M$4;T5:AC5)*D6:M6)

Giỏi! gần xong rồi đó.

Đằng sau, trợ lực bằng Offset()

Chúc em ngày vui.
Thân

 

[Nguyễn Hồng Quang]

Đúng vậy em! nếu đưa về dạng chuẩn thì rất tốt không có vấn đề gì bàn thêm.
Nếu như sort dòng thì dễ, chứ sort cột thì coi bộ phải qua nhiều công đoạn nữa mới tạo thành chuẩn thống nhất của hai bảng.
Trong thực tế, nhiều khi vô tình người nhập dữ liệu vào bảng hay tùy thích chèn thêm cột, thêm dòng, cho nên thứ tự bị xáo trộn.

Vì vậy, anh đưa trường hợp này cũng để cho anh em cái nhìn thoáng hơn và có phương án xử lý nhanh gọn hơn là sort cột.

Kệ! cứ coi như bài nâng cao công thức cho vui.

Chúc anh em ngày vui.

----------------------------------------------------------------------------------------------------------------------------

Giỏi! gần xong rồi đó.

Đằng sau, trợ lực bằng Offset()

Chúc em ngày vui.
Thân

Cảm ơn anh. Làm xong công thức em cũng thấy vui 1 phần vì đã giải được bài toán hay, 1 phần là học thêm được cách dùng sumif

 

[Phan Thế Hiệp]

Cảm ơn anh. Làm xong công thức em cũng thấy vui 1 phần vì đã giải được bài toán hay, 1 phần là học thêm được cách dùng sumif

Chưa xong mà em! giải quyết tiếp T5: AC5 đi em!

Cố chút nữa là tới đích rồi.

Khà khà khà

 

[Nguyễn Hồng Quang]

Chưa xong mà em! giải quyết tiếp T5: AC5 đi em!

Cố chút nữa là tới đích rồi.

Khà khà khà

Vâng ghép Offset vô là ổn.
Thú thật với anh hai là
Lúc đầu em mò mãi ko ra đâu, tắt đi mở lại file mấy lần (cũng hơi nản).
Đến 1 hồi, tự dưng nhớ đến 1 bài anh dùng sumif trong tìm bút toán sai. mò mò tiếp lắp thử cái đoạn SUMIF($T$4:$AC$4;$D$4:$M$4;T5:AC5)thấy kết quả = 55, lúc đó cũng không chắc lắm . Sau rồi ghép tiếp với sumproduct thấy ra kết quả 63.93, lúc đó mới biết mình làm đúng. Hì, đúng là em vẫn còn hên xui nhiều anh à

 

[Phan Thế Hiệp]

Vâng ghép Offset vô là ổn.
Thú thật với anh hai là
Lúc đầu em mò mãi ko ra đâu, tắt đi mở lại file mấy lần (cũng hơi nản).
Đến 1 hồi, tự dưng nhớ đến 1 bài anh dùng sumif trong tìm bút toán sai. mò mò tiếp lắp thử cái đoạn SUMIF($T$4:$AC$4;$D$4:$M$4;T5:AC5)thấy kết quả = 55, lúc đó cũng không chắc lắm . Sau rồi ghép tiếp với sumproduct thấy ra kết quả 63.93, lúc đó mới biết mình làm đúng. Hì, đúng là em vẫn còn hên xui nhiều anh à

Đó là con đường "học" và "tập" đúng đắn.
Vì khi mình thấy người ta làm ra xong, về cũng coi, nhưng rồi để đó mà không nghiền ngẫm lý do tại sao người ta làm vậy! hoặc có công thức nào khác thay thế không!? Nếu không bắt tay vào vọc thì không bao giờ nhớ nỗi nó.

Rồi em sẽ thấy, từ đây về sau em thuộc nằm lòng trường hợp này, vì chính em đã cọ sát qua nó rồi.

Còn một công thức gọi là "quái chiêu" nữa, ngắn hơn và cũng không dùng Ctrl+Shift+Enter.
Gợi ý: thay vì dùng Sumif(), anh sẽ dùng HLOOKUP()
Em hoàn chỉnh công thức tại bài #16 để anh em tham khảo với.

Chúc em ngày vui.
Thân

 

[Nguyễn Hồng Quang]

Đó là con đường "học" và "tập" đúng đắn.

Em hoàn chỉnh công thức tại bài #16 để anh em tham khảo với.

Chúc em ngày vui.
Thân

Vâng em xin trả bài đầy đủ theo file gửi kèm.
N6=SUMPRODUCT(SUMIF($T$4:$AC$4;$D$4:$M$4;OFFSET($T$4;MATCH(B6;$S$5:$S$18;;;10))*$D6:$M6)

Còn một công thức gọi là "quái chiêu" nữa, ngắn hơn và cũng không dùng Ctrl+Shift+Enter.
Gợi ý: thay vì dùng Sumif(), anh sẽ dùng HLOOKUP()

Nhờ anh mở rộng kiến thức cho em; bằng cách dùng HLookup() với nhé.
Chúc anh chiều thứ 6 vui

 

[Phan Thế Hiệp]

Vâng em xin trả bài đầy đủ theo file gửi kèm.
N6=SUMPRODUCT(SUMIF($T$4:$AC$4;$D$4:$M$4;OFFSET($T$4;MATCH(B6;$S$5:$S$18; 0 ) ;;;10))*$D6:$M6)
Nhờ anh mở rộng kiến thức cho em; bằng cách dùng HLookup() với nhé.
Chúc anh chiều thứ 6 vui

Chính xác rồi em!


Về phần công thức với hàm HLOOKUP() lý do anh nói "quái chiêu" là từ xưa đến giờ mình có thể đem mảng vào những đối số để chọn cột hay dòng trong hai hàm V-HLOOKUP(), Ví dụ: Như lối nhảy "Kangaroo" mà anh hay dùng VLOOKUP('Giá trị tìm', 'Vùng so khớp', {2,10,15,18,20}, 0) chẳng hạn, nhưng việc đem mảng vào 'Giá trị tìm' cho hai hàm này rất khó chịu. Chủ đề 'mở rộng' rất phù hợp với HLOOKUP() nên anh 'gã' nó cho "xứng đôi vừa lứa".

=SUMPRODUCT(HLOOKUP(T(IF({1},$D$4:$M$4)),$T$4:$AC$18,MATCH(B6,$S$4:$S$18,),)*D6:M6)

Chỉ Enter, rồi fill xuống.

Chú ý hàm Hlookup() này có giá trị tìm là một mảng dùng IF(): T(IF({1},$D$4:$M$4)), và nếu mảng này không được "mở cửa" bằng hàm T(), thì mình vô phương để hiện nó thành 1 mảng hoàn chỉnh, và lấy toàn bộ đầy đủ 'Giá trị tìm'.

Đó là điều anh muốn chia sẻ cùng anh em.

Thân

 

[Phan Thế Hiệp]

@excel_lv1.5
Em khỏe không!?
Đi đâu mấy nay không vào chơi với anh em!?

 

[excel_lv1.5]

Chính xác rồi em!


Về phần công thức với hàm HLOOKUP() lý do anh nói "quái chiêu" là từ xưa đến giờ mình có thể đem mảng vào những đối số để chọn cột hay dòng trong hai hàm V-HLOOKUP(), Ví dụ: Như lối nhảy "Kangaroo" mà anh hay dùng VLOOKUP('Giá trị tìm', 'Vùng so khớp', {2,10,15,18,20}, 0) chẳng hạn, nhưng việc đem mảng vào 'Giá trị tìm' cho hai hàm này rất khó chịu. Chủ đề 'mở rộng' rất phù hợp với HLOOKUP() nên anh 'gã' nó cho "xứng đôi vừa lứa".

=SUMPRODUCT(HLOOKUP(T(IF({1},$D$4:$M$4)),$T$4:$AC$18,MATCH(B6,$S$4:$S$18,),)*D6:M6)

Chỉ Enter, rồi fill xuống.

Chú ý hàm Hlookup() này có giá trị tìm là một mảng dùng IF(): T(IF({1},$D$4:$M$4)), và nếu mảng này không được "mở cửa" bằng hàm T(), thì mình vô phương để hiện nó thành 1 mảng hoàn chỉnh, và lấy toàn bộ đầy đủ 'Giá trị tìm'.

Đó là điều anh muốn chia sẻ cùng anh em.

Thân

Cũng là lookup_value như macth, lookup mà 2 thằng này đưa vào trực tiếp được, còn v-hlookup thì phải biến thể mới sài được , hài thật, đúng là ông bill gây khó dễ cho người dùng thật, cách này rất hay, thanks.
Dù sao công thức này cũng chưa phải ngắn nhất, dạng lộn xộn này có hàm khác hay hơn.

 

[Phan Thế Hiệp]

Cũng là lookup_value như macth, lookup mà 2 thằng này đưa vào trực tiếp được, còn v-hlookup thì phải biến thể mới sài được , hài thật, đúng là ông bill gây khó dễ cho người dùng thật, cách này rất hay, Cảm ơn.
Dù sao công thức này cũng chưa phải ngắn nhất, dạng lộn xộn này có hàm khác hay hơn.

Lâu rồi, không gặp tác phẩm hay.
Cứ tự nhiên em.

Thân

 

[excel_lv1.5]

@excel_lv1.5
Em khỏe không!?
Đi đâu mấy nay không vào chơi với anh em!?

Dạo này cũng lu bu anh, cũng vào thường mà tìm bài để giải quyết vấn đề riêng thôi.

Lâu rồi, không gặp tác phẩm hay.
Cứ tự nhiên em.

Thân

Cái này mọi người làm hoài, dùng match xét vị trí thôi, dùng vlookup hay offset đều được. Mượn lại hàm vlookup( ,,mảng,) của anh.
=SUM(VLOOKUP(B6,$S$5:$AC$18,MATCH($D$4:$M$4,$S$4:$AC$4,),)*D6:M6)

 

[Phan Thế Hiệp]

Dạo này cũng lu bu anh, cũng vào thường mà tìm bài để giải quyết vấn đề riêng thôi.

Cái này mọi người làm hoài, dùng match xét vị trí thôi, dùng vlookup hay offset đều được. Mượn lại hàm vlookup( ,,mảng,) của anh.
=SUM(VLOOKUP(B6,$S$5:$AC$18,MATCH($D$4:$M$4,$S$4:$AC$4,),)*D6:M6)

Đúng là em!
Độ nhạy bén của em quá cao!

Thật tình anh không nhìn ra, chứ như đề cập ở trên về ghép mảng vào đối số cột cho Vlookup() thì quả là anh em mình đã biết lâu.

Cảm ơn em nhắc nhở.

Chúc em ngày vui.

 

[Minh Ngọc LH]

Dạo này cũng lu bu anh, cũng vào thường mà tìm bài để giải quyết vấn đề riêng thôi.

Cái này mọi người làm hoài, dùng match xét vị trí thôi, dùng vlookup hay offset đều được. Mượn lại hàm vlookup( ,,mảng,) của anh.
=SUM(VLOOKUP(B6,$S$5:$AC$18,MATCH($D$4:$M$4,$S$4:$AC$4,),)*D6:M6)

Em test qua nó ra #VALUE, không biết bị gì ta

 

[Phan Thế Hiệp]

Em test qua nó ra #VALUE, không biết bị gì ta

Kết thúc bằng Ctrl+Shift+Enter

Thân

 

[baggiotung]

cám ơn các bạn nhiều, offset thật lợi hại. Hic. Toàn cao thủ