Hỏi nhanh - đáp nhanh về công thức (phần 3)

huuthang_bd · 18/9/17

tuananhthi đã viết:
Mong các anh giúp đỡ, rút ngắn công thức này hộ em với, nó dài quá, vào excel cứ bị đơ
'''

Em cảm ơn các anh !

Rút gọn thì bạn có thể làm như sau.

Mã:

=SUM($B$2:B2000*($C$2:C2000=F16)*($D$2:D2000="L")*MMULT(--($A$2:A2000=TRANSPOSE($M16:M$42)),ROW($M16:M$42)^0))

Nếu chỉ vài ô dùng công thức thì có thể dùng được chứ công thức áp dụng cho cả cột thì không có công thức nào nhanh đâu, bạn nên tìm cách khác (VBA, Pivot Table)

dazkangel · 18/9/17

huuthang_bd đã viết:
Rút gọn thì bạn có thể làm như sau.

Mã:

=SUM($B$2:B2000*($C$2:C2000=F16)*($D$2:D2000="L")*MMULT(--($A$2:A2000=TRANSPOSE($M16:M$42)),ROW($M16:M$42)^0))

Nếu chỉ vài ô dùng công thức thì có thể dùng được chứ công thức áp dụng cho cả cột thì không có công thức nào nhanh đâu, bạn nên tìm cách khác (VBA, Pivot Table)

Nếu có file mẫu chắc sẽ có hướng hay hơn, ví dụ các ô M16:M42 có cái gì điểm chung chẳng hạn ^^, lúc đó có thể kết hợp thêm 1 cột phụ

quocgiacan · 18/9/17

huuthang_bd đã viết:
Rút gọn thì bạn có thể làm như sau.

Mã:

=SUM($B$2:B2000*($C$2:C2000=F16)*($D$2:D2000="L")*MMULT(--($A$2:A2000=TRANSPOSE($M16:M$42)),ROW($M16:M$42)^0))

Nếu chỉ vài ô dùng công thức thì có thể dùng được chứ công thức áp dụng cho cả cột thì không có công thức nào nhanh đâu, bạn nên tìm cách khác (VBA, Pivot Table)

Hình như chủ thớt muốn gom vầy, anh xem thử: (Do SUMIFS(...) không thể để 'đông' $B$2 mà cho B2000 động rồi kéo qua cột khác thành $B$2: C2000!!?)
=SUMPRODUCT(SUMIFS($B$2:$B2000,$C$2:$C2000,F16,$D$2: $D2000,"L",$A$2:$A2000,$M$16:$M$42))
Không có file kiểm chứng nên đoán mò, như bao lần anh em mình làm 'thầy bói' vậy.

Chúc anh em ngày thiệt vui.

tuananhthi · 18/9/17

quocgiacan đã viết:
Hình như chủ thớt muốn gom vầy, anh xem thử: (Do SUMIFS(...) không thể để 'đông' $B$2 mà cho B2000 động rồi kéo qua cột khác thành $B$2: C2000!!?)
=SUMPRODUCT(SUMIFS($B$2:$B2000,$C$2:$C2000,F16,$D$2: $D2000,"L",$A$2:$A2000,$M$16:$M$42))
Không có file kiểm chứng nên đoán mò, như bao lần anh em mình làm 'thầy bói' vậy.

Chúc anh em ngày thiệt vui.

Em có các giá trị ở cột A2:A2000 và giá trị cột M16:M42, một một giá trị ở cột A sẽ tương ứng với 1 số lượng ở cột B ( chính xác ở đây là từ B2:B2000 đó ạ)
Em muốn làm 1 công thức so sánh để tính tổng khối lượng ở cột B với 2 điều kiện:
1. Giá trị của cột A thuộc tập hợp các giá trị của cột M16:M42, nếu có 1 giá trị của cột A là "abc" chẳng hạn, mà cột M16:M42 có 2 giá trị "abc" thì số lượng tương ứng cũng phải gấp lên 2 lần, nhiều lần hơn cũng thế ạ.
2. Giá trị cột C ở đây thuộc 1 giá trị cố định em định sẵn ở F16. (Tên cửa hàng chẳng hạn)
Nếu được thì mong các anh giúp em code thì tốt lắm lắm ạ, em cảm ơn các anh !

quocgiacan · 18/9/17

tuananhthi đã viết:
Em có các giá trị ở cột A2:A2000 và giá trị cột M16:M42, một một giá trị ở cột A sẽ tương ứng với 1 số lượng ở cột B ( chính xác ở đây là từ B2:B2000 đó ạ)
Em muốn làm 1 công thức so sánh để tính tổng khối lượng ở cột B với 2 điều kiện:
1. Giá trị của cột A thuộc tập hợp các giá trị của cột M16:M42, nếu có 1 giá trị của cột A là "abc" chẳng hạn, mà cột M16:M42 có 2 giá trị "abc" thì số lượng tương ứng cũng phải gấp lên 2 lần, nhiều lần hơn cũng thế ạ.
2. Giá trị cột C ở đây thuộc 1 giá trị cố định em định sẵn ở F16. (Tên cửa hàng chẳng hạn)
Nếu được thì mong các anh giúp em code thì tốt lắm lắm ạ, em cảm ơn các anh !

Bạn đưa yêu cầu, nhưng bạn không chịu nhìn kỹ yêu cầu của người giải bài!

dazkangel đã viết:
Nếu có file mẫu chắc sẽ có hướng hay hơn

quocgiacan đã viết:
Không có file kiểm chứng nên đoán mò,

Chúc bạn ngày vui.

huuthang_bd · 18/9/17

quocgiacan đã viết:
Hình như chủ thớt muốn gom vầy, anh xem thử: (Do SUMIFS(...) không thể để 'đông' $B$2 mà cho B2000 động rồi kéo qua cột khác thành $B$2: C2000!!?)
=SUMPRODUCT(SUMIFS($B$2:$B2000,$C$2:$C2000,F16,$D$2: $D2000,"L",$A$2:$A2000,$M$16:$M$42))
Không có file kiểm chứng nên đoán mò, như bao lần anh em mình làm 'thầy bói' vậy.

Chúc anh em ngày thiệt vui.

Kiểu tham chiếu (Tuyệt đối, tương đối, hỗn hợp) là theo công thức của người hỏi, tôi để nguyên và không sửa gì cả. Nếu nhu cầu của người tả là chỉ copy công thức theo chiều dọc thì $B$2:B2000 hay $B$2:$B2000 hay B$2:B2000 đều như nhau cả mà thôi.

Tôi dùng hàm MMULT mục đích là để tăng tốc độ tính toán của công thức (mặc dùng cũng không biết có hiệu quả hay không. Tôi chỉ nghĩ đơn giản là A * B * C * (x + y + z) thì tính sẽ nhanh hơn là A * B * C * x + A * B * C * y + A * B * C * z

tuananhthi · 18/9/17

Vấn đề này nó hơi tế nhị nhưng đúng là không biết phải làm thế nào do trình độ em kém quá, mà cái này bạn em nó nhờ nên nghiệp vụ em chỉ biết hạn chế nên em viết cả trong file, mong các anh giúp đỡ thêm và chỉnh sửa vba hộ em với ạ.

quocgiacan · 18/9/17

tuananhthi đã viết:
Vấn đề này nó hơi tế nhị nhưng đúng là không biết phải làm thế nào do trình độ em kém quá, mà cái này bạn em nó nhờ nên nghiệp vụ em chỉ biết hạn chế nên em viết cả trong file, mong các anh giúp đỡ thêm và chỉnh sửa vba hộ em với ạ.

Theo 1 trong các gợi ý trên, bạn có thể dùng:

PHP:

J16=SUMPRODUCT(SUMIFS($B$2:$B$983,$C$2:$C$983,$F16,$D$2:$D$983,"L",$A$2:$A$983,$M$16:$M$42))
Hoặc
J16=SUMPRODUCT(SUMIFS($B$2:$B$983,$C$2:$C$983,$F16,$D$2:$D$983,RIGHT(TRIM(J$15)),$A$2:$A$983,$M$16:$M$42))

Enter fill xuống.

Chúc bạn ngày vui.
p/s: và chỉ có vậy thôi nha bạn!

dazkangel · 18/9/17

tuananhthi đã viết:
Vấn đề này nó hơi tế nhị nhưng đúng là không biết phải làm thế nào do trình độ em kém quá, mà cái này bạn em nó nhờ nên nghiệp vụ em chỉ biết hạn chế nên em viết cả trong file, mong các anh giúp đỡ thêm và chỉnh sửa vba hộ em với ạ.

Thử cách sau:

PHP:

=SUMPRODUCT(COUNTIF($M$16:$M$42,$A$2:$A$189)*($C$2:$C$189=$F16)*($D$2:$D$189="L")*$B$2:$B$189)

tuananhthi · 18/9/17

Em cảm ơn các anh

dinh quang dai · 24/9/17

Nếu mình Có text như sau : "Dây dù D500/250/52 dăng mùng". : "Dây dù D20/250/52 dăng mùng". trong chuỗi có 4 chữ d. Mình muốn

Thứ nhất : vị trí chữ D mà phía sau nó là số (ở đây là D5, D2,3,4....), không phải D phía sau là text "Dây, dù, dăng";
Thứ 2 là lấy toàn bộ số nằm sau chữ D và dấu /: ở đây là số 500 liền sau chữ D mà không phải lấy toàn bộ số phía sau chữ D (500/250/52);
thứ 3: và sau đó lấy toàn bộ số sau chữ D (500/250/52)

Vấn đề 2: mình có số trong excel phân cách hàng ngàn, đơn vị bằng dấu .,: ví dụ: A1= 100.000.000,25
Khi mình lập hàm kết nối với nối với A1: ví dụ A2="giá x "&A1 thì kết quả là : gia x 1000000,25.
Tuy nhiên, trong số không có dấu phân cách hàng ngàn. mình muốn nó thể hiện thì làm sao: gia x 1.000.000,25.

Cảm ơn

frpx123 · 8/12/17

mình có 1 đoạn sau :

= CONCATENATE(B6, "*", B7,"*",B8,"*",B23,"*",B27)

mình muốn excel hiểu là : nếu giá trị của b27 > 1 thì không ghi đoạn "*",B27 nghĩa là nó sẽ có dạng : = CONCATENATE(B6, "*", B7,"*",B8,"*",B23)

còn nếu giá trị của b27 < 1 thì ghi đoạn "*",B27 nghĩa là nó sẽ có dạng : = CONCATENATE(B6, "*", B7,"*",B8,"*",B23,"*",B27)

Cho giá trị A1 và A2 , cho A1 giá trị x , A1 giá trị y

A1 : x
A2: y

cho B3 : = A1 * B1

Nếu B3 => 1 thì viết hàm = CONCATENATE(B6, "*", B7,"*",B8)

còn nếu B3 < 1 thì không viết gì cả ?

ai giúp với ạ

bebo021999 · 8/12/17

Nếu B3<1 thì trả về trống, còn không thì nối?
=IF(B3<1,"",CONCATENATE(B6,"*",B7,"*",B8))

Hay nếu B3<1 thì không nối "*B7" vào chuỗi?
=CONCATENATE(B6,IF(B3<1,"","*B7"),"*",B8)

thuhuonglee · 8/12/17

huuthang_bd đã viết:
Rút gọn thì bạn có thể làm như sau.

Mã:

=SUM($B$2:B2000*($C$2:C2000=F16)*($D$2:D2000="L")*MMULT(--($A$2:A2000=TRANSPOSE($M16:M$42)),ROW($M16:M$42)^0))

Nếu chỉ vài ô dùng công thức thì có thể dùng được chứ công thức áp dụng cho cả cột thì không có công thức nào nhanh đâu, bạn nên tìm cách khác (VBA, Pivot Table)

Công thức hay và lạ quá .Cám ơn Anh nhiều

KhanhChi29 · 9/12/17

Anh chị em trong diễn đàn giúp em rút gọn hàm này với (Mục đích thay chữ ngàn thành chữ nghìn và xóa chữ chẵn trong ô G59
Nhìn 2 hàm SUBSTITUTE nồng vào nhau kỳ kỳ
SUBSTITUTE(SUBSTITUTE(G59;"ngàn";"nghìn");" chẵn";"")
với G59=Một tỷ không trăm mười chín triệu bảy trăm chín mươi chín ngàn ba trăm năm mươi tám đồng chẵn.

tanluc1991 · 11/12/17

Cao thủ giúp em với. em gặp vấn đề thế này.
em muốn kéo công thức theo hàng dọc nhưng giá trí tính ở trong chạy theo hàng ngang.
VD: em count(B1:B50) nếu kéo xuống thì nó chạy count(B2:B51). Nhưng em lại cần là kéo xuống mà nó chạy thành count(C1:C50) ạk
Cao nhân giúp em với.

bebo021999 · 12/12/17

Dùng cái này tại N3 rồi kéo xuống:
=COUNT(OFFSET($A$2:$A$50,,ROWS($1:1),))

tanluc1991 · 12/12/17

bebo021999 đã viết:
Dùng cái này tại N3 rồi kéo xuống:
=COUNT(OFFSET($A$2:$A$50,,ROWS($1:1),))

em cảm ơn anh nhiều ạk

frpx123 · 13/12/17

= CONCATENATE("XXX: ", B6, "*", B7,"*",B8,"*",B9)

với công thức trên nó ra dạng :

XXX : 6*1,9*2,6*8

có cách nào nó ra kết quả luôn ko ? nó hiện luôn kết quả 23712

XXX : 6*1,9*2,6*8 = 23712

MT2Love · 15/12/17

Ở cột B2 của mình là random các số cho sẵn tùy chọn trong cột A
Có bác nào có cách để cột C2, C3, C4 của mình khi thay đổi thì nó sẽ random ở trong khoảng đó được không.
Ví dụ hiện B2 đang random số từ A2:A15, B3 là random từ A17:30. khi thay đổi C2 thì nó thay đổi tương ứng.
Xin cảm ơn

Hỏi nhanh - đáp nhanh về công thức (phần 3)

Chuyên gia GPE

<New Horizons>

-⍟-

Thành viên mới

-⍟-

Chuyên gia GPE

Thành viên mới

-⍟-

<New Horizons>

Thành viên mới

Thành viên mới

Thành viên mới

Thành viên gạo cội

Thành viên hoạt động

Thành viên chính thức

Thành viên mới

File đính kèm

Thành viên gạo cội

Thành viên mới

Thành viên mới

Thành viên mới

File đính kèm

Facebook

Group