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

[cop_kh]

cám ơn các bác quan tâm em có 6 cột là a1, c1, o1,r1,u1, y1 và muốn viết công thức là cột z1, các bác lưu ý là giữa các cột này có cột khác cũng có giá trị nhưng không cho vào, giả sử giữa côt a1 và c1 có cột b1 với b1 = 97, nhưng em không muốn cột đó khôngtham gia vào công thức. em cám ơn các bác nhé ( file can anh gửi cho em là 6 cột liền nhau)

Ý bạn muốn che cột B đi phải không? Nếu vậy bạn Click phải chuột lên cột B (ngay chữ B luôn bạn nhé), chọn Hide là che được cột B rồi, các cột khác không cần, bạn tô luôn 1 lần cho các cột liền nhau, làm tuơng tự như trên.

 

[saker]

Cám ơn các bác nhiều lắm, sáng nay vừa làm việc vừa tranh thủ vào diễn đàn em không ngờ câu hỏi của mình lại được các bác trả lời tận tình như thế, lòng nhiệt tình của các bác làm em có cảm giác rất vui giống như được nhận món quà rất quí vậy.
- Theo như bác ndu96081631 ứng dụng VB cho kết quả nhanh như điện xẹt vậy , nhưng em xin thỉnh giáo các bác nếu ta chỉ dùng 3 hàm IF, OFFSET, MATCH thì có thể cho kết quả như trên được ko ạ?
Thanks các bác nhiều nhiều,

 

[ndu96081631]

Cám ơn các bác nhiều lắm, sáng nay vừa làm việc vừa tranh thủ vào diễn đàn em không ngờ câu hỏi của mình lại được các bác trả lời tận tình như thế, lòng nhiệt tình của các bác làm em có cảm giác rất vui giống như được nhận món quà rất quí vậy.
- Theo như bác ndu96081631 ứng dụng VB cho kết quả nhanh như điện xẹt vậy , nhưng em xin thỉnh giáo các bác nếu ta chỉ dùng 3 hàm IF, OFFSET, MATCH thì có thể cho kết quả như trên được ko ạ?
Thanks các bác nhiều nhiều,

Xin nói thêm 1 chút cho bạn rõ:
- Dùng mấy hàm mà bạn nói đương nhiên là cho kết quả rồi
- Ở đây chỉ phân tích cho bạn hiểu rằng: Với dử liệu lớn cở vài ngàn dòng thì công thức bộc lộ rất nhiều nhược điểm, mà cụ thể là TỐC ĐỘ
- VBA giãi quyết được nhược điểm trên ---> Hơn nữa với code tôi viết cũng chẳng qua là Record lại quá trình làm bằng tay mà thôi (Tức tự động hóa quá trình làm bằng tay) ---> Rất dể hiểu

 

[saker]

Cám ơn bác ndu96081631 đã giải thích, câu trả lời của bác rất rõ ràng rồi tuy nhiên do em mít đặc VB nên cứ muốn học hỏi cách sử dụng các hàm trong excel, hẳn bác là một trong những cao thủ trong diễn đàn vậy phiền bác cùng các bác trong diễn đàn có thể bớt chút thời gian giúp em chỉnh sửa, áp dụng thêm hàm OFFSET trong câu lệnh sử dụng 3 hàm chính: IF, MATCH, OFFSET.

câu lệnh này sử dụng ngoài bảng chứa dữ liệu kết quả, trong bảng em sẽ dùng hàm khác để lấy dữ liệu từ địa chỉ của câu lệnh sau:

= IF(OR(LEFT(NKChungTu!M6,3)="Máy",LEFT(NKChungTu!M6,8)="Cầu trục",LEFT(NKChungTu!M6,9)="Cổng trục"),MATCH(NKChungTu!$M6,NKChungTu!$M$6:$M$1625,0),"")

Em chịu không biết dùng OFFSET chỗ nào trong câu lệnh trên để cho kết quả như lúc đầu em hỏi các bác.Xin nói rõ thêm là em lấy dữ liệu từ một sheet nguồn khác có tên là NKChungTu

Thanks các bác lắm lắm.

 

[tintin239]

cột b vẫn hiện để em nhập số liệu và có lúc nó cũng bỏ trống nưa. nhung nó không tham gia vào
công thức. cám ơn anh đã quan tâm mong anh giúp em vói nhé

 

[cadafi]

cột b vẫn hiện để em nhập số liệu và có lúc nó cũng bỏ trống nưa. nhung nó không tham gia vào công thức. cám ơn anh đã quan tâm mong anh giúp em vói nhé

Vậy cụ thể yêu cầu của bạn là gì? Nhìn vào file của bạn, mình không biết bạn muốn gì nữa!?

 

[ndu96081631]

cột b vẫn hiện để em nhập số liệu và có lúc nó cũng bỏ trống nưa. nhung nó không tham gia vào
công thức. cám ơn anh đã quan tâm mong anh giúp em vói nhé

Cột Z bạn đang có công thức:
=W5+T5+Q5+N5+K5
Vậy trong công thức này cột B đâu có tham gia vào công thức ---> Hỏng hiểu bạn muốn gì?
Lúc đầu tôi đoán cái bạn cần là SUMIF, công tất cả dử liệu có tiêu để là SỐ BAO (giờ xem ra chắc không phải)

 

[tieuthubuongbinh]

Cần biết 1 giá trị nằm ở đâu trong file

1/ Mình muốn dò tìm giá trị A ở hàng nào, cột nào thì có được không?
Vd: trong bảng tính, giá trị A đang nằm ở R100 chẳng hạn, thì tại ô công thức sẽ cho giá trị la R100

2/ Giả sử mình có các ô như sau:
1 2 3
A 100
B 100
C =A1+B1 =A2+B2 =A3+B3
D 50
E =C1+D1 =C2+D2 =C3+D3
F
Mình muốn hide C1 và E1 là ~ ô có công thức không đổi trong toàn bộ bảng tính, để khi copy giá trị cho ~ ô còn lại, mình không cần copy từng khúc mà 1 lần copy A1:F3 để nó fill các giá trị vào, vì bảng tính của mình dài mà phải copy từng ô từng ô thì mất thời gian quá.

 

[cop_kh]

1/ Mình muốn dò tìm giá trị A ở hàng nào, cột nào thì có được không?
Vd: trong bảng tính, giá trị A đang nằm ở R100 chẳng hạn, thì tại ô công thức sẽ cho giá trị la R100

2/ Giả sử mình có các ô như sau:
1 2 3
A 100
B 100
C =A1+B1 =A2+B2 =A3+B3
D 50
E =C1+D1 =C2+D2 =C3+D3
F
Mình muốn hide C1 và E1 là ~ ô có công thức không đổi trong toàn bộ bảng tính, để khi copy giá trị cho ~ ô còn lại, mình không cần copy từng khúc mà 1 lần copy A1:F3 để nó fill các giá trị vào, vì bảng tính của mình dài mà phải copy từng ô từng ô thì mất thời gian quá.

Câu 1 bạn có thể tham khảo ở đây.
Câu 2: chưa hiểu ý bạn lắm.

 

[ararechan]

Chỉ cách giải bài tập này với

em đang làm bài tập down từ diễn đàn.
đến câu 3 thì em chịu..bó tay.
Nhờ các bác chỉ em cách giải quyết với ạ!

 

[BNTT]

em đang làm bài tập down từ diễn đàn.
đến câu 3 thì em chịu..bó tay.
Nhờ các bác chỉ em cách giải quyết với ạ!

Góp ý với bạn 2 câu này:

Câu 1 (ô I5):
Bạn làm: =(C5*2+D5*2+E5*2+F5+G5+H5)/9
Tại sao bạn không gom 3 môn chính và 3 môn phụ lại với nhau bằng hàm SUM?

=(SUM(C5:E5)*2+SUM(F5:H5))/9​

May là có 3 môn chính, 3 môn phụ, chứ chừng mười mấy môn mà bạn cũng làm vầy chắc gõ dấu + mỏi tay...


Câu 2: Hàm RANK, nếu bình thường xếp hạng từ điểm cao nhất đến điểm thấp nhất, bạn không cần phải nhập tham số 0 vào. Chỉ cần vầy thôi:

=RANK(I5, $I$5:$I$14)​

Câu 3: Xếp loại. Bạn xem lại đề bài nhé:

- Giỏi : ĐTB >=9 và không có môn nào điểm nhỏ hơn 8
- Khá : ĐTB >=7 và không có môn nào điểm nhỏ hơn 6
- TB : ĐTB >=5 và không có môn nào điểm nhỏ hơn 4
- Yếu : Các trường hợp còn lại

• Để làm cái này, chúng ta sẽ dùng hàm IF. Xếp cho ba loại Giỏi, Khá, TB, cần có 3 hàm IF lồng nhau.
• Để làm cái chuyện "và", dùng hàm AND
• Để tìm số điểm nhỏ nhất trong các môn, dùng hàm MIN (minimum), MIN(6 môn)>=8 có nghĩa là trong 6 môn không có điểm nào nhỏ hơn 8.

Làm nhé, làm tại ô K5. Tôi sẽ hướng dẫn bạn theo cách "sư phạm" nhất, dễ hiểu nhất. Ta có ĐTB là ô I5, điểm của các môn nằm từ ô C5 đến H5.

Cái thứ nhất: Nếu ĐTB >=9 và không có môn nào điểm nhỏ hơn 8 thì xếp loại Giỏi

IF(AND(I5>=9, MIN(C5:H5)>=8), "Giỏi", IF...)​

Cái thứ hai: Nếu ĐTB >=7 và không có môn nào điểm nhỏ hơn 6 thì xếp loại Khá

IF(AND(I5>=7, MIN(C5:H5)>=6), "Khá", IF...)​

Cái thứ ba: Nếu ĐTB >=5 và không có môn nào điểm nhỏ hơn 4 thì xếp loại TB, còn lại thì xếp loại Yếu

IF(AND(I5>=5, MIN(C5:H5)>=4), "TB", "Yếu)​

Rồi. Bây giờ bạn lần lượt lồng mấy cái IF lại với nhau, cái thứ 3 vào cái thứ 2, rồi tất cả vào trong cái thứ nhất. Vì có 3 cái IF nên phải nhớ đóng công thức bằng 3 cái ngoặc đơn:

IF(AND(I5>=9, MIN(C5:H5)>=8), "Giỏi", IF(AND(I5>=7, MIN(C5:H5)>=6), "Khá", IF(AND(I5>=5, MIN(C5:H5)>=4), "TB", "Yếu)))​

Bạn hiểu chứ?

 

[tieuthubuongbinh]

[2/ Giả sử mình cần tính như sau:
Cột A là tên sp
Cột B,C,D là tương ứng tháng 1, 2 ,3
Tại B2, C2, D2: sp A cần vlookup từ 1 sheet khác
Tại B3, C3, D3: sp B cần vlookup từ sheet khác
Tại dong 4 cần total 2 sp trên: ô b4 =B2+B3 , ô c4 =C2+C3 , ô d4 =D2+D3
Tại B5, C5, D5: Sp C cần vlookup từ sheet khác
Tại dong 6 cần Grand Total cho sp A,B,C : ô b6 = B4+B5, ô c6 =C4+C5, ô d6 =D4+D5

vì mình phải vlookup từ sheet khác, nhưng vì bảng tính dài nên mình muốn kéo công thức từ b2:C5 luôn, nhưng như vậy thì công thức ở các ô Total sẽ bị copy thành vlookup luôn, nên có cách nào cố định các ô total (luôn không đổi công thức sum) để khi mình kéo như vậy, các ô sp sẽ sẽ thành vlookup, các ô total vẫn giữ nguyên công thức sum
Vì hiện giờ, mình phải copy từng ô thành vlookup nên phải copy đến 9 lần cho 9 ô (nếu có bảng tính như trên)
Hoặc là nếu kéo công thức thì phải sửa các ô total thành công thức sum thì cũng mất thời gian.
Hi vọng bạn hiểu ý mình

 

[Po_Pikachu]

Thật sự mình đọc bài của bạn mà không thể nào hiểu được bạn đang muốn làm gì cả?
Bạn có thể giải thích rõ hơn và có file đính kèm làm ví dụ không?
Thân.

 

[cadafi]

@tieuthubuongbinh: Với yêu cầu của bạn tại bài #820, tốt nhất bạn nên gửi file đính kèm và ghi rõ kết quả mong muốn vào đó cho dễ hiểu, kể cả cái hàm Vlookup và subtotal luôn. Mình nghĩ biết đâu sẽ có giải pháp hay hơn!

 

[ptm0412]

Có gì đâu mà khó hiểu ha cadafi:
Cách vài dòng dữ liệu lại có 1 dòng subtotal: hiểu hông?
sub total của cột có chứa công thức: hiểu hông?
công thức thì muốn kéo cái rẹt từ trên xuống chạy suốt qua các dòng subtotal lun: hiểu hông?

Câu trả lời là không thể.

 

[cadafi]

Có gì đâu mà khó hiểu ha cadafi:
Cách vài dòng dữ liệu lại có 1 dòng subtotal: hiểu hông?
sub total của cột có chứa công thức: hiểu hông?
công thức thì muốn kéo cái rẹt từ trên xuống chạy suốt qua các dòng subtotal lun: hiểu hông?

Câu trả lời là không thể.

Dạ! Anh ơi, cho nên em mới nói

tốt nhất bạn nên gửi file đính kèm và ghi rõ kết quả mong muốn vào đó cho dễ hiểu, kể cả cái hàm Vlookup và subtotal luôn. Mình nghĩ biết đâu sẽ có giải pháp hay hơn!

Gửi kèm file để biết cấu trúc dữ liệu có cái gì, bao nhiêu sản phẩm, dữ liệu từ các sheet kia là gì có cần thiết phải vlookup hay không? Chứ còn chỉ có mỗi ba sản phẩm A,B,C thì cách làm cho yêu cầu này quá dễ.

(Với lại từ chiều đến giờ vào hết 1 két ken, đọc bài 820 nhìn rối quá, hiểu được mới lạ)

@tieuthubuongbinh: Tốt nhất bạn nên gửi file lên. tôi làm theo yêu cầu của bạn, nhưng chỉ giới hạn cho 3 sản phẩm, tôi gõ công thức tại một ô, sau đó bạn tự copy down và copy right nhé!

|

A​

|

B​

|

C​

|

D​

1 |

Tên SP​

|

Tháng 1​

|

Tháng 2​

|

Tháng 3​

2 |

A​

|=CHOOSE(1+SUMPRODUCT(--($A2={"A","B","C"})),SUM(INDIRECT(ADDRESS(ROW()-2,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN()))),VLOOKUP($A2,ThamChieu!$A$1:$D$4,B$1+1,0))||
3 |

B​

|||
4 |

Total A+B​

|||
5 |

C​

|||
6 |

Total A+B+C​

|||

 

[tieuthubuongbinh]

=CHOOSE(1+SUMPRODUCT(--($A2={"A","B","C"})),SUM(INDIRECT(ADDRESS(ROW()-2,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN()))),VLOOKUP($A2,ThamChieu!$A$1:$D$4,B$1+1,0))

=> 1/ mình đã dùng được công thức này nhưng vế vlookup thì không dùng được. Theo như mình hiểu thì phần col_index, cafi dùng công thức động để không phải đếm thứ tự cột phải không?
Thay vì: VLOOKUP($A2,ThamChieu!$A$1:$D$4,2,0) thì 2 thay bằng B$1+1
Nhưng sao mình làm lại thành ra #VALUE
2/ đúng là nếu ít sp thì dùng công thức này được, nhưng nếu n` sp hơn thì cũng không tối ưu lắm, mình sẽ cố gắng upfile lên để mọi người dễ mường tượng hơn nhé

 

[tintin239]

tính tổng

các bác tỉnh tổng cho em cột Tổng nhé

 

[Po_Pikachu]

các bác tỉnh tổng cho em cột Tổng nhé

Thì bạn dùng hàm IF để xem thằng nào rỗng là được rồi.
[L5] =IF(J5<>"",J5,IF(H5<>"",H5,IF(F5<>"",F5,D5)))-B5
Hoặc viết như vầy cũng được:
[L5] =MAX(D5,F5,H5,J5)-B5
Thân.

 

[cadafi]

các bác tỉnh tổng cho em cột Tổng nhé

Bạn hãy thử công thức này xem:

=INDIRECT(CHOOSE(SUMPRODUCT(--(ISNUMBER($B$4:$K$4)),--(ISNUMBER($B5:$K5))),"B","D","F","H","J")&ROW())-$B5*(SUMPRODUCT(--ISNUMBER($B5:$K5))>1)

@Po_Pikachu: bạn xem lại công thức này xem sao, hình như không đúng đâu

MAX(D5,F5,H5,J5)-B5

Với lại, công thức này:

IF(J5<>"",J5,IF(H5<>"",H5,IF(F5<>"",F5,D5)))-B5

- nếu chỉ cân 1 lần thì chẳng lẽ = 0