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

Liên hệ QC

Người dùng đang xem chủ đề này

Đang trực tuyến: 1 (Thành viên: 0, Khách: 1)
Nếu dữ liệu như mô tả của bạn thì chỉ cần sử dụng hàm SUMIF thôi, bạn xem file đính kèm nhé. Kết quả bạn mong muốn nằm ở vùng bôi màu vàng trong file.
 

File đính kèm

hngiatuong đã viết:
Vâng cảm ơn anh. Đúng rồi, em cứ loay hoay ở chỗ "*"
Nhấp chuột vào đây để mở rộng...
Nếu em muốn thực hành bài này với Sumproduct thì có thể thay chỗ * đó thành hàm Left lấy số ký tự ra và so sánh với điều kiện em cần là OK, vì bài toán em không nói rõ phải dùng Sumproduct nên "anh" dùng SumIF cho nó thân thiện, hehehee
 
hoamattroicoi đã viết:
Nếu em muốn thực hành bài này với Sumproduct thì có thể thay chỗ * đó thành hàm Left lấy số ký tự ra và so sánh với điều kiện em cần là OK, vì bài toán em không nói rõ phải dùng Sumproduct nên "anh" dùng SumIF cho nó thân thiện, hehehee
Nhấp chuột vào đây để mở rộng...
Hehehe, Bé Còi sướng nhé, "SONG HỶ LÂM MÔN", khỏi phải đi Thái Lan & hưởng một luật mới ban hành ......+-+-+-++-+-+-++-+-+-+
 
Lần chỉnh sửa cuối:
hoamattroicoi đã viết:
Nếu em muốn thực hành bài này với Sumproduct thì có thể thay chỗ * đó thành hàm Left lấy số ký tự ra
Nhấp chuột vào đây để mở rộng...

Ban đầu chính là em dùng Hàm Sumproduct này, rồi dùng left lấy vài ký tự đầu thỏa đk thì nhân cột số lượng với đơn giá. Nhưng không biết sai chỗ nào mà loay hoay mãi với cái hàm left ấy. Xin cảm ơn anh chỉ bảo
 
hngiatuong đã viết:
Ban đầu chính là em dùng Hàm Sumproduct này, rồi dùng left lấy vài ký tự đầu thỏa đk thì nhân cột số lượng với đơn giá. Nhưng không biết sai chỗ nào mà loay hoay mãi với cái hàm left ấy. Xin cảm ơn anh chỉ bảo
Nhấp chuột vào đây để mở rộng...
Nếu dùng Sumproduct để nhân số lượng với đơn giá để ra thành tiền với điều kiện như file thì em làm như sau :

Tổng bút (công thức tại E9) : =SUMPRODUCT((LEFT($B$4:$B$8,3)="Bút")*($C$4:$C$8)*($D$4:$D$8))

Tổng hoa quả (công thức tại E10) : =SUMPRODUCT((LEFT($B$4:$B$8,3)<>"Bút")*($C$4:$C$8)*($D$4:$D$8))

Tổng cam ( công thức tại E11) : =SUMPRODUCT((LEFT($B$4:$B$8,2)="Ca")*($C$4:$C$8)*($D$4:$D$8))

Tùy trường hợp em vận dụng nhé, công thức trên "anh" chỉ thao tác theo mô tả dữ liệu trong bài #1 của em thôi.

Chúc năm mới vui vẻ :)
 
Mình sử dụng hàm sumproduct để lọc các mặt hàng mua vào theo từng ngày, nhưng không hiểu sao mỗi lần nhập, file xử lý rất chậm, nhờ các bạn xem và sửa lỗi giúp. Một hóa đơn có nhiều mặt hàng nên có cách nào để không phải nhập dữ liệu ngày tháng mỗi khi xuống hàng nhập mặt hàng khác (Các bạn xem file và giúp mình với). Cảm ơn các bạn rất nhiều.

https://drive.google.com/open?id=0B4ilIIcCxq1SMlVfQ1U1bXFKY2M
 
Xin chào mọi người, mình có bài toán như file đính kèm, rất mong mọi người giúp đỡ.
Đề bài: tính lợi nhuận của 1 danh mục đầu tư tại 1 thời điểm biết danh mục này gồm nhiều khoản khác nhau với tỷ lệ khác nhau. Sau 1 hồi mò mẫm trên mạng thì mình mới chỉ giải quyết được trường hợp tỷ lệ các khoản bằng nhau. Nhờ bà con chỉ giáo giúp.
 

File đính kèm

Nhờ ACE giúp CT sumproduct cho cột có điều kiện (cột động)

Mình cài CT kèm hàm match nhưng không hiểu cách excel tính ntn mà ra số lạ quá

=SUMPRODUCT((MATCH("Actual",$C$2:$N$2,0)*C3:N3))
 

File đính kèm

Em muốn lọc số tiền theo tháng số tiền của từng bộ phận theo ví dụ sau mà em đặt công thức nó ra tổng các tháng luôn. Em k giỏi hàm sumproduct lắm, nhờ các bác sửa giùm em với ạ.
 
sumproduct.jpg
Có thể giải thích giúp mình cái hàm trong bài có nghĩa là gì không vậy? Xem không rõ lắm. Mong thỉnh giáo
 
Quý Anh Chị giúp mình với, mình muốn đếm các số cách nhau bằng dấu phẩy "," như ví dụ bên dưới thì làm sau:

cột A1: 2,3,4
cột A2: 5,6
cột A3: 12,13

kết quả tổng đếm là 7

Anh Chị chỉ giúp công thức với nhé
 
Mã: 
=SUM(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,",",""))+1)

Kết thúc bằng ctrl+shift+enter
 
Mình làm nhưng vẩn không được, Mình gửi file Bạn khuongvietphong xem dùm nhé, cám ơn nhiều
 

File đính kèm

vhlongus đã viết:
Mình làm nhưng vẩn không được,
Nhấp chuột vào đây để mở rộng...

Không được vì trong File có cả dầu . phân cách giữa các ngày chứ không phải chỉ có dấu , như ví dụ bạn đưa ra ban đầu

Bạn sửa công thức thành:

Mã: 
=SUM(LEN(E9:E11)-LEN(SUBSTITUTE(SUBSTITUTE(E9:E11,",",""),".",""))+1)
 
Mình copy roi dán vào file gốc nhưng vẫn báo lỗi
 
Mình bó tay rồi không được, Bạn cho vào file lươn giúp mình nhé, cám ơn nhiều
 

File đính kèm

File đính kèm

Mính làm được rồi, cám ơn Bạn Phong và bạn Hương nhiều nhé, cho mình hỏi thêm là nếu chèn thêm 1 dòng nữa và để trống thì công thức thế nào? hai bạn chỉ giúp mình nhé, cám ơn nhiều
 
vhlongus đã viết:
Mính làm được rồi, cám ơn Bạn Phong và bạn Hương nhiều nhé, cho mình hỏi thêm là nếu chèn thêm 1 dòng nữa và để trống thì công thức thế nào? hai bạn chỉ giúp mình nhé, cám ơn nhiều
Nhấp chuột vào đây để mở rộng...

Nếu chèn thêm 1 dòng trống nữa thì công thức thế này:

Mã: 
=SUM(LEN(E9:E12)-LEN(SUBSTITUTE(SUBSTITUTE(E9:E12,",",""),".",""))+1)-COUNTBLANK($E$9:$E$12)
 
Chào các bạn,
Mình có 1 vấn đề muốn nhờ các cao thủ chỉ giáo
Hiện tại mình đang muốn vlookup trường volume theo tên, do tên "JOHN" có 3 giá trị nên khi vlookup để lấy tất cả các giá trị thì mình dùng hàm mảng (cột G), vấn đề này hiện mình đã giải quyết xong. Tuy nhiên, khi mình muốn thêm điều kiện vlookup với điều kiện ngày, cụ thể là chỉ vlookup mảng thỏa điều kiện cột date chạy từ 03/06/2016 đến 04/07/2016, mình cho thêm 2 điều kiện if (cột F) thì kết quả trả ra false ở dòng John cuối cùng trong khi cột John phía trên lại trả ra giá trị đúng.
Xin cám ơn các bạn trước @$@!^%
 

File đính kèm

Lần chỉnh sửa cuối:
em hay dùng cách này : =SUMPRODUCT(A1:A3*B1:B3*(C1:C3="Cam")) nhấn phím ENTER
 
em có dùm hàm sumproduct để đếm số chuyến xe với điều kiện đúng tên là A và xe là C. Tuy nhiên em có 1 chuyến tên là B và xe là C với số thứ tự chuyến trùng với 1 trong số thứ tự chuyến tên A và xe C. Lúc này kết quả bị sai. Mong các cao nhân chỉ bảo giúp em với.
 

File đính kèm

mai tien hai đã viết:
em có dùm hàm sumproduct để đếm số chuyến xe với điều kiện đúng tên là A và xe là C. Tuy nhiên em có 1 chuyến tên là B và xe là C với số thứ tự chuyến trùng với 1 trong số thứ tự chuyến tên A và xe C. Lúc này kết quả bị sai. Mong các cao nhân chỉ bảo giúp em với.
Nhấp chuột vào đây để mở rộng...
Chỉ cần:
PHP: 
H3=SUMPRODUCT((A3:A6=F3)*(C3:C6=G3))
Hoặc đơn giản
PHP: 
H3=COUNTIFS(A3:A6,F3,C3:C6,G3)

Chúc bạn ngày vui.
 
quocgiacan đã viết:
Chỉ cần:
PHP: 
H3=SUMPRODUCT((A3:A6=F3)*(C3:C6=G3))
Hoặc đơn giản
PHP: 
H3=COUNTIFS(A3:A6,F3,C3:C6,G3)

Chúc bạn ngày vui.
Nhấp chuột vào đây để mở rộng...

Cảm ơn bác đã phản hồi. lệnh chạy tốt ạ.
Tuy nhiên, ngoài thỏa mãn điều kiện Tên và Xe, em muốn kiểm soát thêm 1 diều kiện nữa là đếm số chuyến nhưng loại những chuyến bị trùng ra như trong file em gửi bên dưới. nếu có 2 chuyến bị trùng thì kết quả nó phản ánh ko chính xác ạ.

Mong các bác chỉ giáo giúp em.
 

File đính kèm

mai tien hai đã viết:
Cảm ơn bác đã phản hồi. lệnh chạy tốt ạ.
Tuy nhiên, ngoài thỏa mãn điều kiện Tên và Xe, em muốn kiểm soát thêm 1 diều kiện nữa là đếm số chuyến nhưng loại những chuyến bị trùng ra như trong file em gửi bên dưới. nếu có 2 chuyến bị trùng thì kết quả nó phản ánh ko chính xác ạ.

Mong các bác chỉ giáo giúp em.
Nhấp chuột vào đây để mở rộng...
Vậy thì thử vầy:
PHP: 
H3=SUMPRODUCT((A3:A6=F3)*(C3:C6=G3)/(COUNTIFS(B3:B6,B3:B6,A3:A6,A3:A6,C3:C6,C3:C6)))
Enter

Chúc bạn ngày vui.
 
HG-L1-60*60-60SP006
HG-L1-60*60-60SP306
HG-L1-60*60-60SP306
ASIAN-L1-60*60-6610
ASIAN-L1-60*60-6632
cho em hỏi em muốn lấy caasi chữ cái đầu thì dùng công thức nào a. , VD: HG , ASIAN
 
Bạ nào giúp mình với ạ
 

File đính kèm

Chào anh chị trong diễn đàn, cũng liên quan đến hàm sumproduct, em muốn hỏi một chút nếu trong mảng dữ liệu tính toán của hàm mà bao gồm cả kiểu dữ liệu số và dữ liệu text thì hàm sumproduct sẽ báo lỗi value, vậy có cách nào vẫn tính được dữ liệu mà không bị lỗi được không ạ! Rất mong mọi người chỉ giáo!
Mình đang dùng điện thoại nên khôg up file lên được!
 
nhunghap đã viết:
..........................
Mình đang dùng điện thoại nên khôg up file lên được!
Nhấp chuột vào đây để mở rộng...
Vậy thì khi nào không dùng điện thoại, mà vẫn 'up file' lên được, thì sẽ có kết quả ngay, bạn đừng lo lắng mần chi! không tốn tiền điện thoại đâu! :)

Chúc bạn ngày vui.
 
quocgiacan đã viết:
Vậy thì khi nào không dùng điện thoại, mà vẫn 'up file' lên được, thì sẽ có kết quả ngay, bạn đừng lo lắng mần chi! không tốn tiền điện thoại đâu! :)8

Chúc bạn ngày vui.
Nhấp chuột vào đây để mở rộng...
Rất cám ơn góp ý của bác e sẽ post file ngay khi có thể, chỉ mong ace giúp hướng giải quyết khi trong file có nhiều kiểu dữ liệu! Thân ái và cám ơn mọi người
 
Gửi các anh chị trong diễn đàn mình có bảng công chấm theo tiếng muốn tổng hợp số giờ tăng ca trong tháng mà trong bảng công của mình có cả kiểu dữ liệu số và text nhờ cả nhà xem giúp
 

File đính kèm

nhunghap đã viết:
Gửi các anh chị trong diễn đàn mình có bảng công chấm theo tiếng muốn tổng hợp số giờ tăng ca trong tháng mà trong bảng công của mình có cả kiểu dữ liệu số và text nhờ cả nhà xem giúp
Nhấp chuột vào đây để mở rộng...
Điều chỉnh:
PHP: 
AM7=SUM(ISNUMBER(--G7:AK7)*(G7:AK7>8)*($G$6:$AK$6<>"CN")*(IFERROR(--G7:AK7,0)-8))
Kết thúc bằng Ctrl+Shift+Enter, rồi fill xuống.

Chúc bạn ngày vui.
 
quocgiacan đã viết:
Điều chỉnh:
PHP: 
AM7=SUM(ISNUMBER(--G7:AK7)*(G7:AK7>8)*($G$6:$AK$6<>"CN")*(IFERROR(--G7:AK7,0)-8))
Kết thúc bằng Ctrl+Shift+Enter, rồi fill xuống.

Chúc bạn ngày vui.
Nhấp chuột vào đây để mở rộng...
cám ơn bác đã giúp đỡ, tuy nhiên mình còn một vấn đề nữa là nếu trong tháng có một buổi nào đó mà công nhân chỉ làm một vài tiếng thì số công sẽ không chính xác, nhờ bác giúp đỡ xem số giờ lẻ đó quy ra công thì như thế nào !
Trân trọng
 
nhunghap đã viết:
cám ơn bác đã giúp đỡ, tuy nhiên mình còn một vấn đề nữa là nếu trong tháng có một buổi nào đó mà công nhân chỉ làm một vài tiếng thì số công sẽ không chính xác, nhờ bác giúp đỡ xem số giờ lẻ đó quy ra công thì như thế nào !
Trân trọng
Nhấp chuột vào đây để mở rộng...
Điều chỉnh tiếp:
PHP: 
AL7=COUNTIFS($G$6:$AK$6,"<>CN",$G7:$AK7,">=8")+ROUND(SUMIFS($G7:$AK7,$G$6:$AK$6,"<>CN",$G7:$AK7,"<8")/8,1)
chỉ Enter, rồi fill xuống.

Chúc bạn ngày vui.
 
quocgiacan đã viết:
Điều chỉnh tiếp:
PHP: 
AL7=COUNTIFS($G$6:$AK$6,"<>CN",$G7:$AK7,">=8")+ROUND(SUMIFS($G7:$AK7,$G$6:$AK$6,"<>CN",$G7:$AK7,"<8")/8,1)
chỉ Enter, rồi fill xuống.

Chúc bạn ngày vui.
Nhấp chuột vào đây để mở rộng...
Xin chào bác, em có một vấn đề này muốn nhờ bác giup, trong file chấm công mà em có gửi và nhờ bác chỉ một vài công thức giờ em muốn nhờ bác giúp em tính xem ví dụ công nhân nghỉ nửa ngày ở các cột " nghỉ K", Nghỉ Ro, Nghỉ O trong file bác xem giúp em nhé
 

File đính kèm

nhunghap đã viết:
Xin chào bác, em có một vấn đề này muốn nhờ bác giup, trong file chấm công mà em có gửi và nhờ bác chỉ một vài công thức giờ em muốn nhờ bác giúp em tính xem ví dụ công nhân nghỉ nửa ngày ở các cột " nghỉ K", Nghỉ Ro, Nghỉ O trong file bác xem giúp em nhé
Nhấp chuột vào đây để mở rộng...
Ghi nhận các trường hợp nghỉ nửa ngày bằng: Xx/2
Ví dụ: K/2; Ro/2; Cô/2; Ô/2; P/2.​
Rồi điều chỉnh công thức trong các cột tương ứng:
Ví dụ: =COUNTIF($H7:$AM7,"Ro*")-COUNTIF($H7:$AM7,"Ro/2")/2​
Tương tự cho các cột còn lại.

Chúc bạn ngày vui.
 
quocgiacan đã viết:
Ghi nhận các trường hợp nghỉ nửa ngày bằng: Xx/2
Ví dụ: K/2; Ro/2; Cô/2; Ô/2; P/2.​
Rồi điều chỉnh công thức trong các cột tương ứng:
Ví dụ: =COUNTIF($H7:$AM7,"Ro*")-COUNTIF($H7:$AM7,"Ro/2")/2​
Tương tự cho các cột còn lại.

Chúc bạn ngày vui.
Nhấp chuột vào đây để mở rộng...
Rất cám ơn bác đã nhiệt tình chia sẻ và giúp đỡ, em tính một mà chưa tính tới 2 bác ạ, khi mà tính được các ngày nghỉ thì số công của họ lại bị thay đổi bác ạ, bác làm giúp em công thức tính tổng công, số giờ tăng ca lại khi mà có " 0,5Ro" tham gia vào công thức ạ, Trân trọng
 
nhunghap đã viết:
Rất cám ơn bác đã nhiệt tình chia sẻ và giúp đỡ, em tính một mà chưa tính tới 2 bác ạ, khi mà tính được các ngày nghỉ thì số công của họ lại bị thay đổi bác ạ, bác làm giúp em công thức tính tổng công, số giờ tăng ca lại khi mà có " 0,5Ro" tham gia vào công thức ạ, Trân trọng
Nhấp chuột vào đây để mở rộng...
Thay đổi điều chỉnh các ngày nghỉ theo ký tự, có hoặc không thêm "/2".
PHP: 
AP7=SUM(COUNTIF($H7:$AM7,"Ro"&{"*","/2"})*{1,-0.5})
AQ7=SUM(COUNTIF($H7:$AM7,"K"&{"*","/2"})*{1,-0.5})
AR7=SUM(COUNTIF($H7:$AM7,"P"&{"*","/2"})*{1,-0.5})
AS7=SUM(COUNTIF($H7:$AM7,"Cô"&{"*","/2"})*{1,-0.5})
Enter fill xuống

Ý bạn muốn nửa giờ còn lại tính vào công đi làm!?
PHP: 
AM7=COUNTIFS($H$6:$AL$6,"<>CN",$H7:$AL7,">=8")+ROUND(SUMIFS($H7:$AL7,$H$6:$AL$6,"<>CN",$H7:$AL7,"<8")/8,1)+COUNTIF($H7:$AL7,"*/2")/2
Enter fill xuống

Bạn tham khảo file kèm.

Chúc bạn ngày vui
 

File đính kèm

Lần chỉnh sửa cuối:
quocgiacan đã viết:
Thay đổi điều chỉnh các ngày nghỉ theo ký tự, có hoặc không thêm "/2".
PHP: 
AP7=SUM(COUNTIF($H7:$AM7,"Ro"&{"*","/2"})*{1,-0.5})
AQ7=SUM(COUNTIF($H7:$AM7,"K"&{"*","/2"})*{1,-0.5})
AR7=SUM(COUNTIF($H7:$AM7,"P"&{"*","/2"})*{1,-0.5})
AS7=SUM(COUNTIF($H7:$AM7,"Cô"&{"*","/2"})*{1,-0.5})
Enter fill xuống

Ý bạn muốn nửa giờ còn lại tính vào công đi làm!?
PHP: 
AM7=COUNTIFS($H$6:$AL$6,"<>CN",$H7:$AL7,">=8")+ROUND(SUMIFS($H7:$AL7,$H$6:$AL$6,"<>CN",$H7:$AL7,"<8")/8,1)+COUNTIF($H7:$AL7,"*/2")/2
Enter fill xuống

Bạn tham khảo file kèm.

Chúc bạn ngày vui
Nhấp chuột vào đây để mở rộng...
dạ vâng, rất cám ơn bác đã hỗ trợ, file bác làm chạy rất tốt !
 
nvhung247z đã viết:
Thì cái này không biết gi cả mới cần xem có ví dụ chứ
Nhấp chuột vào đây để mở rộng...
Bạn chịu khó nói cho rõ thêm: bài nào ở phía trên không có ví dụ để đến nỗi bạn "không giám" dùng excel.

Khi muốn đề cập đến bài nào bạn chưa tỏ tường, cứ việc nhấn và "Trả lời" tại mục đó, chứ tự nói bâng quơ, trổng không một mình ên hà, thì bạn có thấy hơi kỳ kỳ không?

Chúc bạn ngày vui.
 
nvhung247z đã viết:
Cái ví dụ đối với mình thì mình cần một ví dụ tường minh hơn. Mình dùng ex chỉ ở mức bt không cần quá nâng cao nên thực sự mình cũng không hiểu được hết những thứ bạn ấy viết :). Mình rất muốn biết thêm thông tin để nâng cao trình độ bản thân :|
Nhấp chuột vào đây để mở rộng...
  • "Mình dùng ex chỉ ở mức bt...":Nếu bạn ở mức bt (là "bình thường" hay "bó tay" vậy bạn?) thì cứ lục lọi trên diễn đàn những bài phù hợp sức mình để tham khảo, chắc chắn một điều: đại đa số bài giải đều dựa trên file hiện hữu đính kèm, cho nên nói bài giải không có ví dụ cụ thể là bạn thiếu quan sát rồi. Giống như bạn đang học lớp 1 đi lạc vào trường đại học thì làm sao hiểu được cái 'bình thường' ở trường đại học, phải vậy không bạn!?
  • "Mình rất muốn biết thêm thông tin để nâng cao trình độ bản thân...": tất cả mọi thành viên ai cũng có nguyện vọng như bạn, nhưng với hành động chủ động hơn, không nói bâng quơ, trỗng không, biết cách đặt câu hỏi và gửi file kèm thắc mắc, để mọi anh em cùng được dịp hỗ trợ giúp nhau. Mọi hành vi khác diễn đàn xem là 'quậy phá' đó nha bạn.
Chúc bạn ngày vui.
 
nvhung247z đã viết:
Cái này cũng gần giống kiểu bạn đang học phép "+" và biết nó. Mà kiến thức của bạn thì toàn phải tính tích phân hay log vậy
Cảm ơn bác ^^.
Nhấp chuột vào đây để mở rộng...

Ok. Tôi coi như bạn là đứa trẻ mới học đọc, học đánh vần. Vậy với bài học công thức mảng mầ bạn chưa biết gì ở đây, bạn hãy đọc và đánh vần, làm theo từng dòng công thức ở bài 1 nhé. Tạm cho mình về zero để học theo cũng là cách học thửa bản đầu.
 
Bài viết đầu tiên tôi viết, sau khi GPE thay đổi giao diện của forum thì hình ảnh bài 1 không còn nữa. Tôi không thể nào sửa lại bài viết để tự chỉnh lại link của hình anh. VẤN ĐỀ KHÔNG SỬA ĐƯỢC BÀI VIẾT GPE VẪN KHÔNG LÀM ĐƯỢC?
 
Các bác giúp em giải bài này với ạ, em đau đầu bao nhiêu ngày nay mà ko thể giải nổi ! Em cảm ơn các bác nhiều !
 

File đính kèm

tuananhthi đã viết:
Bác nào giúp em với :(
Nhấp chuột vào đây để mở rộng...
Chờ lâu không thấy trả lời cũng có lý do đó: bài này thuộc chuyên đề SUMPRODUCT, có quá nhiều bài viết trong đó nên không thu hút sự chú ý. Lần sau nhớ mở topic mới sẽ dễ thu hút hơn.
Dùng công thức này tại E2 rồi copy xuống:

Mã: 
=LOOKUP(D2,{"tb2","tb3","tb4","tb5","tb6"},CHOOSE(LEN(A2)/2-COUNT(SEARCH($F$2:$F$18,A2))+1,{3,8,11,77,111},{0,0,0,3,77},{0,0,0,0,3},{0,0,0,0,0},{0,0,0,0,0}))

Công thức mảng nên kết thúc bằng Ctrl-shift-enter thay vì enter nhé. Thấy có cặp móc nhọn {}bao quanh công thức là OK.
 
ndu96081631 đã viết:
View attachment 187682
Nhấp chuột vào đây để mở rộng...
Sơ ý. Cả
bebo021999 đã viết:
Chờ lâu không thấy trả lời cũng có lý do đó: bài này thuộc chuyên đề SUMPRODUCT, có quá nhiều bài viết trong đó nên không thu hút sự chú ý. Lần sau nhớ mở topic mới sẽ dễ thu hút hơn.
Dùng công thức này tại E2 rồi copy xuống:

Mã: 
=LOOKUP(D2,{"tb2","tb3","tb4","tb5","tb6"},CHOOSE(LEN(A2)/2-COUNT(SEARCH($F$2:$F$18,A2))+1,{3,8,11,77,111},{0,0,0,3,77},{0,0,0,0,3},{0,0,0,0,0},{0,0,0,0,0}))

Công thức mảng nên kết thúc bằng Ctrl-shift-enter thay vì enter nhé. Thấy có cặp móc nhọn {}bao quanh công thức là OK.
Nhấp chuột vào đây để mở rộng...
Đúng là công thức đối với tôi hơi quá tầm, tôi đang nghiên cứu viết code nhưng chưa xong.
 
giaiphap đã viết:
Đúng là công thức đối với tôi hơi quá tầm, tôi đang nghiên cứu viết code nhưng chưa xong.
Nhấp chuột vào đây để mở rộng...
Mình cũng muốn tầm sư học đạo bạn về code.
Để bạn giaiphap ra giải pháp nhanh, mình chia sẽ thuật toán như sau:
LEN(A2)/2-COUNT(SEARCH($F$2:$F$18,A2)) ra 0,1,2,3,4 đếm số lượng KHÔNG tìm thấy (gọi là n). Cộng thêm 1 để dùng CHOOSE
Theo thứ tự:
Nếu n+1 =1,2,3,4,5 trả về mảng tương ứng {3,8,11,77,111},{0,0,0,3,77},{0,0,0,0,3},{0,0,0,0,0},{0,0,0,0,0}
Sau đó dùng =LOOKUP(D2,{"tb2","tb3","tb4","tb5","tb6"},CHOOSE(LEN(A2)/2-COUNT(SEARCH($F$2:$F$18,A2))+1,{3,8,11,77,111},{0,0,0,3,77},{0,0,0,0,3},{0,0,0,0,0},{0,0,0,0,0}))
VD: dòng đầu tiên "th3", n+1=1 (n=0 do tìm thấy 3 cặp trùng), CHOOSE trả về mảng {3,8,11,77,111}, LOOKUP "th3" trả về 3
 
bebo021999 đã viết:
Chờ lâu không thấy trả lời cũng có lý do đó: bài này thuộc chuyên đề SUMPRODUCT, có quá nhiều bài viết trong đó nên không thu hút sự chú ý. Lần sau nhớ mở topic mới sẽ dễ thu hút hơn.
Dùng công thức này tại E2 rồi copy xuống:

Mã: 
=LOOKUP(D2,{"tb2","tb3","tb4","tb5","tb6"},CHOOSE(LEN(A2)/2-COUNT(SEARCH($F$2:$F$18,A2))+1,{3,8,11,77,111},{0,0,0,3,77},{0,0,0,0,3},{0,0,0,0,0},{0,0,0,0,0}))

Công thức mảng nên kết thúc bằng Ctrl-shift-enter thay vì enter nhé. Thấy có cặp móc nhọn {}bao quanh công thức là OK.
Nhấp chuột vào đây để mở rộng...
Em cảm ơn bác lắm lắm ạ !!!!!
 
bebo021999 đã viết:
Chờ lâu không thấy trả lời cũng có lý do đó: bài này thuộc chuyên đề SUMPRODUCT, có quá nhiều bài viết trong đó nên không thu hút sự chú ý. Lần sau nhớ mở topic mới sẽ dễ thu hút hơn.
Dùng công thức này tại E2 rồi copy xuống:

Mã: 
=LOOKUP(D2,{"tb2","tb3","tb4","tb5","tb6"},CHOOSE(LEN(A2)/2-COUNT(SEARCH($F$2:$F$18,A2))+1,{3,8,11,77,111},{0,0,0,3,77},{0,0,0,0,3},{0,0,0,0,0},{0,0,0,0,0}))

Công thức mảng nên kết thúc bằng Ctrl-shift-enter thay vì enter nhé. Thấy có cặp móc nhọn {}bao quanh công thức là OK.
Nhấp chuột vào đây để mở rộng...
Bác cho em hỏi thêm với ạ, Em làm theo hướng dẫn của bác đã được rồi nhưng khi em thay đổi giá trị trong ($F$2:$F$18) thì nó báo lỗi ạ. ví dụ A2 là 1a2b2a mà cột F em có giá trị a2,b2 thì sẽ bị lỗi ạ
 
Lần chỉnh sửa cuối:
tuananhthi đã viết:
Bác cho em hỏi thêm với ạ, Em làm theo hướng dẫn của bác đã được rồi nhưng khi em thay đổi giá trị trong ($F$2:$F$18) thì nó báo lỗi ạ. ví dụ A2 là 1a2b2a mà cột F em có giá trị a2,b2 thì sẽ bị lỗi ạ
Nhấp chuột vào đây để mở rộng...
Lỗi là chắc rồi với dữ liệu không bất nhất. Trong ví dụ chỉ là 1ký số+1 ký tự, (1a,2b) bây giờ lại ngược lại (a1,b2). Bạn đưa hết các trường hợp vào và post lại xem sao
 
bebo021999 đã viết:
Lỗi là chắc rồi với dữ liệu không bất nhất. Trong ví dụ chỉ là 1ký số+1 ký tự, (1a,2b) bây giờ lại ngược lại (a1,b2). Bạn đưa hết các trường hợp vào và post lại xem sao
Nhấp chuột vào đây để mở rộng...
Giá trị cột F là biến số có thể thay đổi bất kỳ và có thể trùng nhau chứ không phải là giá trị xác định như trên bài ạ.
 
em cần tính số tiền phải nộp BHXH tự nguyện cho nhiều năm về sau, tối đa là 5 năm với công thức theo quy định là
aDMReEr.png

vậy có cách nào không cần lập biểu tính từng tháng một rồi tính tổng không ạ? e xin cám ơn
 
phamnamha93 đã viết:
em cần tính số tiền phải nộp BHXH tự nguyện cho nhiều năm về sau, tối đa là 5 năm với công thức theo quy định là
aDMReEr.png

vậy có cách nào không cần lập biểu tính từng tháng một rồi tính tổng không ạ? e xin cám ơn
Nhấp chuột vào đây để mở rộng...
=-PV(0.00628,2*12,3000000*0.22,,1)
 
e tính tổng các cột theo điều kiện bao gồm cả dòng trống dùng hàm sumproduct, bị lỗi, các anh, chị có cách nào hay hơn chỉ giúp e với ạ
 

File đính kèm

queluatb đã viết:
cám ơn bạn đã hỗ trợ, mình thấy trong cell 90, 91 của mình k có dữ liệu gì mà, bạn giải thích giúp mình với
Nhấp chuột vào đây để mở rộng...
2 cell đó không biết bạn thao tác copy dán từ đâu: nó đang là kiểu dữ liệu text chứ không phải Number: nên Sum không được.
( bạn thử dùng Istext() là thấy ak)
:-)
 
kietbui đã viết:
Trong các ứng dụng về công thức mãng mình được biết thì dạng công thức mãng dạng sum nhiều điều kiện là mình tâm đắc nhất vì cú pháp đơn giản, khi kết hợp với những hàm khác thì nó rất linh hoạt , giải quyết rất nhiều tình huống trước đây mình xử lý bằng hàm thông thường rất vất vã, hình như nó tính hơi lâu nhưng kết quả rất ưng ý
Ví dụ
1/ sum nhiều if - thay thế một macro rút trích rồi tính tổng
vd:tính tiền thu được do bán mặt hàng là "sơn" thời gian từ 01/06/07 ( tungay) đến 30/06/07( denngay)
{sum(if(ngay=>tungay,1,0)*if(ngay<=denngay,1,0)*if(mat_hang="son",1,0)*tien)}
2/ Sumproduct nhiều if
vd:tính tổng diện tích các thửa ruộng ấp A, xã B, Huyện C
{sum(if(ap="a",1,0)*if(xa="b",1,0)*if(huyen="c",1,0)*dai*rong)}
3/ count nhiều if
vd: đếm số sv điểm trên trung bình là nam,lớp B, khoa C, Trường D
{sum(if(diem>=5,1,0)*if(gioitinh="nam",1,0)*if(lop="b",1,0)*if(khoa="c",1,0)*if(truong="d",1,0)}
4/max nhiều if
vd:tự động nhảy số hóa đơn thứ mấy xuất cho đơn vị A, chứ không phải là STT hóa đơn, stt hóa đơn =max(stt_hd)
{max(if(dv="a",1,0)*shd_dv)}
5/ Vlookup_if
vd: tìm trong vùng dữ liệu cột 1 mã máy bằng AAA, cột ngày di chuyển=gần nhất, xem nơi đến là nơi nào ( tìm giá trị cột noi_den )
- kết hợp hàm findtwoconditions trên giaiphapexcel và hàm max_if trên
Nhấp chuột vào đây để mở rộng...
Bạn ơi, có thể cho mình xin file exel ví dụ minh họa cho cá công thức trên được ko.
mình làm thử rồi mà ko ra kết quả.
Thanks bạn ^^
 

File đính kèm

em muốn tính tổng có điều kiện cho khoảng 500 đầu mục công việc mà không biết dùng hàm nào cho nhanh. Các anh, chị có cách nào giúp em với ạ
 

File đính kèm

noidoi đã viết:
em muốn tính tổng có điều kiện cho khoảng 500 đầu mục công việc mà không biết dùng hàm nào cho nhanh. Các anh, chị có cách nào giúp em với ạ
Nhấp chuột vào đây để mở rộng...
Dữ liệu của bạn gửi tuy tương đồng kết cấu, nhưng phần chi tiết yêu cầu "cộng phân khoản" không phức tạp như file theo đường link mà tôi đề cập trên.
Do vậy, tôi thực hiện cho bạn cùng giải pháp nhưng đơn giản hơn chút, với một vài lưu ý như sau:
  1. Về chi tiết cộng: bạn chỉ muốn phân nhỏ theo 3 mục (!?): Vật liệu, Nhân công, và Máy nhằm cộng Vùng theo từng tiêu chí đó để ra số tính toán cho "Vật liệu khác" và "Máy khác". Nếu đúng như vậy, thì bạn nên lưu ý chỗ các dòng bạn muốn cộng cho "Máy khác" phải đảm bảo có chữ "Máy" nằm ở cột Nội dung công việc, Vd: ô C25: Cần cẩu xích 10T, bạn phải thêm chữ "(Máy)" để báo cho công thức biết mà phân biệt. Tóm lại, nôm na hễ có "Nhân" và "Máy" thì hiểu là "Nhân công" và "Máy móc", các cái khác còn lại là "Vật liệu". Nếu bạn muốn chính xác hơn nữa thì bạn phải tạo thêm cột phân loại theo ba tiêu chí đó, nhưng hơi rườm rà tí, còn nếu như cách nêu trên là tạm ổn thì cứ việc áp dụng công thức dưới đây.
  2. Cột A số thứ tự (STT) tuy nhìn nó rất đơn giản và bình thường, nhưng chính nhờ nó mà bạn phân Vùng được chính xác. Nên nó là cốt lõi cho công thức cộng tại cột H phía sau. Do bạn chỉ phân biệt 3 chỉ tiêu như trên, nên công thức chỉ đơn giản có "Mẹ" và "Con" thôi không cần "Cháu" :)
  3. Công thức tại cột H tôi dùng SUM() và phân từng đoạn Vùng nhỏ và phù hợp với yêu cầu để không chiếm nhiều bộ nhớ máy. Nên nếu bạn có hơn "500 đầu mục" thì không lo nặng máy. Tôi cũng tính áp dụng Sumif(...,".??",....) như cách bên file hướng dẫn theo link kia, nhưng thấy nó kéo Vùng vào bộ nhớ hơi nhiều (với hơn 500 đầu mục), nên đã chọn giải pháp SUM(OFFSET()).
Công thức áp dụng:
Tính số thứ tự:
Mã: 
A4=IF(COUNTA(B4:G4),IF(B4<>"",INT(IFERROR(--A3,0))+1,INT(--A3)&"."&TEXT(IFERROR(LOOKUP(4,{2,3}/(SEARCH({"Nhân","Máy"},C4)>0)),1),"00")),)
Fill xuống.
Tính Cộng Phân Vùng hoặc Tính Giá trị dòng Chi tiết:
Mã: 
H4=IF(A4<>"",ROUND(IF(B4<>"",SUM(H5:OFFSET(H4,MATCH(1,INDEX(N(A4<>INT(--A4:A1000)),),)-2,)),PRODUCT(E4,F4,G4)*IF(OR(C4={"Vật liệu khác","Máy khác"}),SUM(OFFSET($H$3,MATCH(1,INDEX(N(A4=$A$3:A3),),)-1,):H3)%,1)),),"")
Fill xuống.
Xem file đính kèm.

Chúc bạn học tập vui với GPE.
hihi ^o^
 

File đính kèm

Mộng Thường 2001 đã viết:
Dữ liệu của bạn gửi tuy tương đồng kết cấu, nhưng phần chi tiết yêu cầu "cộng phân khoản" không phức tạp như file theo đường link mà tôi đề cập trên.
Do vậy, tôi thực hiện cho bạn cùng giải pháp nhưng đơn giản hơn chút, với một vài lưu ý như sau:
  1. Về chi tiết cộng: bạn chỉ muốn phân nhỏ theo 3 mục (!?): Vật liệu, Nhân công, và Máy nhằm cộng Vùng theo từng tiêu chí đó để ra số tính toán cho "Vật liệu khác" và "Máy khác". Nếu đúng như vậy, thì bạn nên lưu ý chỗ các dòng bạn muốn cộng cho "Máy khác" phải đảm bảo có chữ "Máy" nằm ở cột Nội dung công việc, Vd: ô C25: Cần cẩu xích 10T, bạn phải thêm chữ "(Máy)" để báo cho công thức biết mà phân biệt. Tóm lại, nôm na hễ có "Nhân" và "Máy" thì hiểu là "Nhân công" và "Máy móc", các cái khác còn lại là "Vật liệu". Nếu bạn muốn chính xác hơn nữa thì bạn phải tạo thêm cột phân loại theo ba tiêu chí đó, nhưng hơi rườm rà tí, còn nếu như cách nêu trên là tạm ổn thì cứ việc áp dụng công thức dưới đây.
  2. Cột A số thứ tự (STT) tuy nhìn nó rất đơn giản và bình thường, nhưng chính nhờ nó mà bạn phân Vùng được chính xác. Nên nó là cốt lõi cho công thức cộng tại cột H phía sau. Do bạn chỉ phân biệt 3 chỉ tiêu như trên, nên công thức chỉ đơn giản có "Mẹ" và "Con" thôi không cần "Cháu" :)
  3. Công thức tại cột H tôi dùng SUM() và phân từng đoạn Vùng nhỏ và phù hợp với yêu cầu để không chiếm nhiều bộ nhớ máy. Nên nếu bạn có hơn "500 đầu mục" thì không lo nặng máy. Tôi cũng tính áp dụng Sumif(...,".??",....) như cách bên file hướng dẫn theo link kia, nhưng thấy nó kéo Vùng vào bộ nhớ hơi nhiều (với hơn 500 đầu mục), nên đã chọn giải pháp SUM(OFFSET()).
Công thức áp dụng:
Tính số thứ tự:
Mã: 
A4=IF(COUNTA(B4:G4),IF(B4<>"",INT(IFERROR(--A3,0))+1,INT(--A3)&"."&TEXT(IFERROR(LOOKUP(4,{2,3}/(SEARCH({"Nhân","Máy"},C4)>0)),1),"00")),)
Fill xuống.
Tính Cộng Phân Vùng hoặc Tính Giá trị dòng Chi tiết:
Mã: 
H4=IF(A4<>"",ROUND(IF(B4<>"",SUM(H5:OFFSET(H4,MATCH(1,INDEX(N(A4<>INT(--A4:A1000)),),)-2,)),PRODUCT(E4,F4,G4)*IF(OR(C4={"Vật liệu khác","Máy khác"}),SUM(OFFSET($H$3,MATCH(1,INDEX(N(A4=$A$3:A3),),)-1,):H3)%,1)),),"")
Fill xuống.
Xem file đính kèm.

Chúc bạn học tập vui với GPE.
hihi ^o^
Nhấp chuột vào đây để mở rộng...
Sao vẫn không được vậy ạ. Em toàn thấy VALUE thôi
 
noidoi đã viết:
Sao vẫn không được vậy ạ. Em toàn thấy VALUE thôi
Nhấp chuột vào đây để mở rộng...
Với code như thế thì bạn phải có thiết lập trong CP giống như người viết code cho bạn. Tức code phụ thuộc vào thiết lập trong CP. Nếu thiết lập của bạn khác đi thì sẽ có VALUE :D
Bạn hãy nhìn kỹ công thức.
Mã: 
A4=IF(COUNTA(B4:G4),IF(B4<>"",INT(IFERROR(--A3,0))+1,INT(--A3)&"."&TEXT(IFERROR(LOOKUP(4,{2,3}/(SEARCH({"Nhân","Máy"},C4)>0)),1),"00")),)
Tức trong cột A giá trị dòng dưới sẽ được tính theo dòng trên. Chắc chắn giá trị trong cột A có nhiều số "lẻ", vd. A5. Và nó có dạng TEXT với quyết định cứng nhắc là có dấu chấm. Vd. A5 = "1.01"

Trên máy người ta thì dấu chấm là dấu thập phân nên với
Mã: 
A6 =IF(COUNTA(B6:G6),IF(B6<>"",INT(IFERROR(--A5,0))+1,INT(--A5)&"."&TEXT(IFERROR(LOOKUP(4,{2\3}/(SEARCH({"Nhân"\"Máy"},C6)>0)),1),"00")),)
thì không có lỗi vì INT(--A5) không gây ra lỗi.

Bây giờ bạn mở tập tin nhưng bạn có dấu phẩy là dấu thập phân nên INT(--A5) = INT(--"1.01") sẽ gây lỗi. Nỗ lực convert chuỗi có dấu chấm thành số trên máy có thiết lập dấu phẩy là dấu thập phân chắc chắn không thành công, chắc chắn gây ra lỗi.

A6 lỗi thì A7 cũng lỗi, mà A7 lỗi thì A8 cũng lỗi, vân vân và phân vân.

Cột A có lỗi thì nhiều giá trị trong cột H cũng sẽ lỗi.
----------------------
Bạn rút kinh nghiệm lần sau. Phải mô tả dữ liệu để biết có cấui trúc thế nào. Không phải ai cũng thích đoán mò.

Tôi hiểu như sau, nếu hiểu không đúng thì hãy đừng đọc tiếp. Vì mọi code tôi viết đều chỉ đúng với giả thiết dữ liệu cụ thể.

Dòng "Vật liệu khác" và "Máy khác" không nhất thiết phải có.

Nếu có "Vật liệu khác" thì ở cột H là tổng các giá trị ở cột H tính từ dòng dưới dòng có số thứ tự đến dòng trên dòng có "Vật liệu khác", được nhân với cột E

Nếu có dòng "Máy khác" thì dòng "Nhân công ..." sẽ có "công" ở cột D, và lúc đó thì ở cột H là tổng các giá trị ở cột H tính từ dòng dưới dòng "công" đến dòng trên dòng có "Máy khác", được nhân với cột E và G

Nếu đúng như trên tôi hiểu thì công thức cho H4
Mã: 
=IF(B4<>"",SUM(H5:INDEX(H$1:H$1000,IFERROR(MATCH("*",B5:B$1000,0)+ROW(B4)-1,LOOKUP("zzz",C5:C$1000,ROW(C5:C$1000))))),IF(C4="Vật liệu khác",E4*SUM(INDEX(H$1:H4,LOOKUP("zzz",B$1:B3,ROW(B$1:B3))+1):H3)/100,IF(C4="Máy khác",E4*G4*SUM(INDEX(H$1:H4,LOOKUP(2,1/(D$1:D4="công"),ROW(D$1:D4))+1):H3)/100,E4*F4*G4)))

copy, kéo công thức xuống dưới.

Tôi giả thiết là có không quá 1000 dòng dữ liệu. Nếu không bao giờ quá vd. 100 thì sửa 1000 trong công thức thành 100.
 

File đính kèm

Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.

Bài viết mới nhất

L
  • Question Question
Nhờ sửa code ChrW () (7 người xem)
Trả lời
1
Đọc
18
ongke0711
ongke0711
8
Theo dõi bán hàng
Trả lời
9
Đọc
215
Maithy46
Maithy46
N
  • Question Question
Hỗ trợ tiếng việt trong macro (1 người xem)
Trả lời
12
Đọc
488
Quang_Hải
Quang_Hải
D
xin hỏi về lỗi mô đun ẩn.
Trả lời
6
Đọc
257
du trung kien
D
haohvnh
Lỗi hàm SUM không hiển thị ra kết quả
Trả lời
19
Đọc
45K
ptm0412
ptm0412
minhduc1o2
Hình ảnh bị lỗi #VALUE (1 người xem)
Trả lời
5
Đọc
142
minhduc1o2
minhduc1o2
ongke0711
  • Question Question
Tra cứu thông tin mã số thuế (cập nhật lại code cũ) (1 người xem)
    • Thích
    • Cảm ơn
    • Yêu thích
Trả lời
5
Đọc
266
ongke0711
ongke0711
befaint
Chia sẻ tâm tư, xả nỗi lòng
    • Thích
6 7 8
Trả lời
761
Đọc
61K
SA_DQ
SA_DQ
S
  • Question Question
Object.close và Set Object = nothing
Trả lời
14
Đọc
1K
nguyendang95
N
H
Lỗi khi sort data xin được hỗ trợ
Trả lời
2
Đọc
106
hunglq48
H
Nguyenkhang2404
Tính định mức sản phẩm
    • Chấp nhận
Trả lời
9
Đọc
338
Nguyenkhang2404
Nguyenkhang2404
ongke0711
  • Question Question
Tải hóa đơn điện tử (https://hoadondientu.gdt.gov.vn/) Excel Vba (1 người xem)
    • Thích
    • Yêu thích
    • Cảm ơn
2 3 4
Trả lời
389
Đọc
46K
THANHHANGPT
T
T
Ngắt kết nối với tệp lệnh đính kèm
Trả lời
2
Đọc
116
TungXauGiai
T
ongke0711
  • Question Question
Trộn dữ liệu từ Excel sang Word mở rộng (phiên bản ongke0711) (1 người xem)
    • Thích
    • Yêu thích
    • Cảm ơn
2 3
Trả lời
277
Đọc
35K
joker102
J
S
tỷ lệ % không khớp khi cộng tổng
Trả lời
2
Đọc
93
snoobcool
S
D
Tên sheet bị che khuất khi thực hiện sửa tên sheet
Trả lời
1
Đọc
257
PhamMaiTB
P
thanhcongdnk
nhờ các cao nhân giúp dùm, xin đa tạ nhiều (1 người xem)
    • Buồn
    • Phẫn nộ
Trả lời
2
Đọc
427
misibi
M
N
  • Question Question
Chia sẻ COM DLL giúp tra cứu mã số thuế từ trang web masothue.com
    • Thích
Trả lời
1
Đọc
452
nguyendang95
N
hoidebiet1710
Doanh thu cho nhiều nhân viên bán hàng
Trả lời
1
Đọc
136
BuiQuangThuan
B
V
  • Question Question
Nhờ mọi người giúp cách xóa các mục đã chọn trong Droplist của một nhóm các ô cùng dạy một môn học.
Trả lời
3
Đọc
217
HUONGHCKT
H
Xem thêm…

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Thời gian đếm ngược.

000
Ngày
00
Giờ
00
phút
00
giây
Thân mời tham dự sự kiện sinh nhật GPE 2025
Back
Top Bottom