Chuyên đề giải đáp các bài tập

[BNTT]

nhờ mọi người xem hộ mình bài này với:

Công thức tính thưởng:

[F7]: =HLOOKUP(D7, $C$23:$E$26, IF(C7<10, 2, IF(C7<19, 3, 4)), 0)​

Công thức đếm số người theo A, B, C:

[H9]: =COUNTIF($D$7:$D$21, G9)​

Công thức tính tổng số tiền thưởng theo A, B, C:

[I9]: =SUMIF($D$7:$D$21, G9, $F$7)​

(copy xuống cho những ô còn lại)

 

[hittho]

Giúp mình bài tập xếp học lực này nhé.

Mình có một bài tập về xếp học lực nhưng làm đến câu cuối cùng thì bí mò mãi không ra.
Điều kiện xếp học lực dựa vào điểm trung bình và điểm tối thiểu của các môn. Mình giửi file lên nhờ các bạn lập cho mình một công thức mẩu là mình sẽ hiểu. Cảm ơn các bạn trước nhé. Đang tự mò excel nên không có thầy hỏi nhờ các bạn vậy.
File đính kèm ở đây: http://s18.galaxyz.net/data/4f7e7e777c767d7d/BaiTapExcel.rar.glx

 

[ptlong04x1]

Bạn xem file đính kèm.

Bạn xem lại xem đúng ý chưa nhé, trong này mình có dùng 1 số NAME để đơn giản bớt công thức, bạn bấm Ctrl F3 để xem nhé.

 

[BNTT]

Mình có một bài tập về xếp học lực nhưng làm đến câu cuối cùng thì bí mò mãi không ra.
Điều kiện xếp học lực dựa vào điểm trung bình và điểm tối thiểu của các môn. Mình giửi file lên nhờ các bạn lập cho mình một công thức mẩu là mình sẽ hiểu. Cảm ơn các bạn trước nhé. Đang tự mò excel nên không có thầy hỏi nhờ các bạn vậy.

Để dễ nói, tôi để luôn cái bảng tính của bạn ở đây nhé:

Để tách số điểm ở MÃ ĐIỂM LƯU thành 5 cột cho 5 môn, có thể làm như sau:
Chọn hết từ F5 cho đến J17 (tất cả những ô sẽ chứa điểm), nhập công thức sau:

=--MID($E5, COLUMN()-5, 1)​

Xong nhấn 2 phím Ctrl+Enter, số điểm sẽ được chia hết vào trong các ô.
Chúng ta sẽ sử dụng 1 công thức cho tất cả, không cần đến 5 công thức như bạn đã làm.

Cái dấu -- ở trước công thức là để chuyển luôn kết quả thành dạng số để tính trung bình.
Hàm COLUMN() lấy con số của cột chứa công thức (ví dụ, cột Toán (cột F) là cột thứ 6), rồi trừ bớt đi 5, ra số 1, dùng số 1 này làm tham số cho hàm MID...

Để tính trung bình, chỉ cần dùng hàm này: AVERAGE
Công thức ở K5:K17 (chọn hết các ô từ K5 đến K17, nhập công thức xong nhấn Ctrl+Enter):

=AVERAGE(F5:J5)​

Công thức để Xếp loại, dùng hàm MIN để xét con số nhỏ nhất trong dãy ô chứa điểm, buộc hàm MIN này phải lớn hơn hoặc bằng 1 con số, để cho "không có môn nào dưới...". Rồi kết hợp với hàm AND, rồi lồng vào trong nhiều hàm IF.

Theo đề bài (xét trường hợp đầu tiên, Nguyễn Trúc A):

• loại Giỏi: =AND(K5>=8, MIN(F5:J5)>=6.5)
• loại Khá: =AND(K5>=6.5, MIN(F5:J5)>=5)
• loại TB: =AND(K5>=5, MIN(F5:J5)>=3.5)
• còn lại là Yếu

Dùng hàm IF lồng những điều kiện trên đây lại (bạn cũng làm như trên tôi đã nói, chọn hết từ L5 đến L17, nhập công thức, rồi nhấn Ctrl+Enter):

=IF(AND(K5>=8,MIN(F5:J5)>=6.5), "Giỏi", IF(AND(K5>=6.5,MIN(F5:J5)>=5), "Khá", IF(AND(K5>=5,MIN(F5:J5)>=3), "Trung bình", "Yếu")))​

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

Bạn ptlong04x1 có kiểm tra lại kết quả không vậy?
Tôi chưa xem công thức của bạn, nhưng nhìn vào trường hợp 1 (Nguyễn Trúc A), anh chàng này có số điểm lần lượt là 1, 2, 3, 4, 5. Riêng con số 1 ở môn đầu tiên thôi, đủ để xếp anh ta loại Yếu, vậy mà bạn xếp anh ta loại TB?

 

[ptlong04x1]

Cảm ơn anh đã nhắc nhở, em kiểm tra lại rồi, em đặt 2 NAME là Tbinh và TB nên khi viết công thức thì bị nhầm 2 NAME này với nhau. Em đã đưa file lên lại bài trước rồi, nếu anh có thời gian xem qua giùm em 1 chút.

 

[thaibinh1212]

Câu hỏi như sau:
_Từ cột "Mã NV", Bảng 1, Bảng 2. Điền CVỤ (T-KD00==>TrPhòng Kinh Doanh)
Thanks

 

[salam]

Câu hỏi như sau:
_Từ cột "Mã NV", Bảng 1, Bảng 2. Điền CVỤ (T-KD00==>TrPhòng Kinh Doanh)
Thanks

Bạn dùng hàm vlookup và hlookup nhé.

=VLOOKUP(LEFT(A3,1),$A$19:$C$21,2,0)&" "&HLOOKUP(MID(A3,3,2),$D$18:$G$19,2,0)

 

[cop_kh]

Câu hỏi như sau:
_Từ cột "Mã NV", Bảng 1, Bảng 2. Điền CVỤ (T-KD00==>TrPhòng Kinh Doanh)
Thanks

Bạn dùng công thức sau nhé:

C3=VLOOKUP(LEFT(A3;1);$A$19:$B$21;2;0)&" "&HLOOKUP(MID(A3;3;2);$D$18:$G$19;2;0)

 

[hittho]

Cảm ơn 2 bạn rất nhiều..!. BNTT có cách hướng dẫn rất hay. Mình học hỏi được nhiều thứ nữa. Bây giờ thì mình hiểu tại sao ở cột trung bình minh không dùng hàm AVERAGE được rồi đơn giản vì các ô điểm Toán, Lý.....sinh không phải dạng dữ liệu số phải không bạn. Lại học được cách chuyễn về dữ liệu dạng số khi dùng hảm Mid.. hi hi.. (--)... Quy tắc lòng hàm ..!!!.
Mình thấy để học một hàm đơn lẽ thì rất dễ. Nhưng để kết hợp nhiều hàm trong một công thức thì hơi khó thông thường sách lại ích viết hoặc cho nhiều các ví dụ theo dạng này. Nếu các cao thủ excel ở diễn đàn ta mở một topic chia sẽ nhưng kinh nghiệm này thì tuyệt cú mèo.
pm ptlong04x1 cảm ơn bạn đã làm hộ mình. Cách của bạn cũng rất hay khi dùng Defile name để gán giá trị vùng. Nhưng cách này không biết đi thi lấy bằng giám khảo có chịu không nhỉ. Vì thông thường người ta cho đề bài dạng này thì yêu cầu chính là phải dùng hàm căn bản trực tiếp (trình độ A) mà... hì hì..! Cảm ơn bạn rất nhiều..!
Mong còn được các bạn giúp đở nhiều mổi khi "bí lù".. Thân mến!!!

 

[hittho]

Mình có thêm một câu hỏi nữa trong bài tập số 17 nhờ các bạn giúp đở. Liên quan đến tính ngày tháng
Mình gửi kèm file các bạn trả lời giùm mình nhé!!!!

 

[BNTT]

Mình có thêm một câu hỏi nữa trong bài tập số 17 nhờ các bạn giúp đở. Liên quan đến tính ngày tháng
Mình gửi kèm file các bạn trả lời giùm mình nhé!!!!

Hì hì, ở trên bày bạn hàm MID, xong bạn đem áp dụng vào bài này, thấy vui quá.

Không ai dùng cái này: MID(B6,1,1) đâu, mà người ta dùng hàm LEFT (lấy ký tự đầu tiên bên trái), cụ thể, bạn có thể thay cái MID(B6,1,1) bằng LEFT(B6).

Công thức để dò ra tên vật tư đúng rồi, nhưng nên thay như sau cho nó ngắn hơn:

=VLOOKUP(LEFT(B6), $B$19:$C$21, 2, 0)​

Tương tự, ở đơn giá cũng vậy:

=VLOOKUP(LEFT(B6), $B$19:$D$21, 3, 0)​

Còn cái chuyện: Nếu thanh toán trước 15 ngày kể từ "Ngày nhận" hàng thì được giảm giá 10% so với "Đơn giá", bạn dùng hàm IF đi (tại có chữ Nếu đó), thanh toán trước 15 ngày kể từ ngày nhân hàng (lẽ ra phải dùng chữ thanh toán trong vòng 15 ngày thì hay hơn), nghĩa là lấy ngày trả tiền trừ đi ngày nhân hàng, mà nó nhỏ hơn 15 ngày, thì giảm cho nó 10%.

[H6]: =F6 * G6 * (1-IF(E6-D6<15, 10%, 0))​

Hoặc là vầy, ngắn hơn tí:

[H6]: =F6 * G6 * (1-((E6-D6)<15)*10%)​

Mà nói cho vui thôi, chớ cái bài toán của bạn, chả có ma nào thanh toán sau 15 ngày kể từ ngày nhận hàng hết trơn! Nghĩa là ai cũng được giảm 10% hết!

 

[hittho]

Hì hì, ở trên bày bạn hàm MID, xong bạn đem áp dụng vào bài này, thấy vui quá.

Không ai dùng cái này: MID(B6,1,1) đâu, mà người ta dùng hàm LEFT (lấy ký tự đầu tiên bên trái), cụ thể, bạn có thể thay cái MID(B6,1,1) bằng LEFT(B6).

Công thức để dò ra tên vật tư đúng rồi, nhưng nên thay như sau cho nó ngắn hơn:

=VLOOKUP(LEFT(B6), $B$19:$C$21, 2, 0)​

Tương tự, ở đơn giá cũng vậy:

=VLOOKUP(LEFT(B6), $B$19:$D$21, 3, 0)​

Còn cái chuyện: Nếu thanh toán trước 15 ngày kể từ "Ngày nhận" hàng thì được giảm giá 10% so với "Đơn giá", bạn dùng hàm IF đi (tại có chữ Nếu đó), thanh toán trước 15 ngày kể từ ngày nhân hàng (lẽ ra phải dùng chữ thanh toán trong vòng 15 ngày thì hay hơn), nghĩa là lấy ngày trả tiền trừ đi ngày nhân hàng, mà nó nhỏ hơn 15 ngày, thì giảm cho nó 10%.

[H6]: =F6 * G6 * (1-IF(E6-D6<15, 10%, 0))​

Hoặc là vầy, ngắn hơn tí:

[H6]: =F6 * G6 * (1-((E6-D6)<15)*10%)​

Mà nói cho vui thôi, chớ cái bài toán của bạn, chả có ma nào thanh toán sau 15 ngày kể từ ngày nhận hàng hết trơn! Nghĩa là ai cũng được giảm 10% hết!

Cảm ơn bạn rất nhiều.! Với những chĩ dẫn này thì mình hoàn toàn không lo không có sư phụ nữa..!! hi.hi. Bây giờ thì mình đã hiểu khi nào nên áp dụng hàm LEFT(trích biên trái), MID(trích khoãng giữa), RIGHT(trích biên phải). Có đúng thế không.?. À mà bạn cho mình một vài ví dụ khi nào sử dụng hàm DATE(yyyy,mm,dd) được không ?
pm: Hỏi nhiều quá ...hihi đang máu học cel mà.. ..! Còn phải thọ giáo dài dài ...Nhận học trò nhé..!!!

 

[cop_kh]

À mà bạn cho mình một vài ví dụ khi nào sử dụng hàm DATE(yyyy,mm,dd) được không ?

Mục đích của hàm DATE(yyyy,mm,dd) là dùng để lấy ngày tháng năm chuẩn cho Excel mà không cần quan tâm đến máy đang dùng sử dụng kiểu ngày tháng năm theo định dạng nào cả (mm/dd/yyyy; mm/dd/yyyy...).
VD như thế này cho dễ hiểu bạn nhé: ở ô A1 bạn có ô dữ liệu là 10042009, tức là người dùng có dữ liệu như vậy, cho kiểu ngày tháng năm là ngày 10 tháng 4 năm 2009.
Ở ô B1 có công thức: DATE(RIGHT(A1;4);MID(A1;3;2);LEFT(A1;2)), KQ sẽ là:

• 10/04/2009 nếu máy tính định dạng theo kiểu dd/mm/yyyy.
• 04/10/2009 nếu máy tính định dạng theo kiểu mm/dd/yyyy.
• 2009/04/10 nếu máy tính định dạng theo kiểu yyyy/mm/dd.

Sơ sơ như vậy được chưa bạn nhỉ?

 

[tonggiap]

Mình có 1 số khó khăn mong các bạn giúp đỡ mình đang pải trả bài sớm!
Mình gửi kèm File bài tập nhé
2,Tên hàng : Dựa vào 2 ký tự đầu của Mã hàng tra trong Bảng tên hàng và 1 ký tự cuối của Mã hàng tra trong Bảng loại hàng
3. Thôngs kế số liệu vào bảng Thống Kê
Cám ơn các bạn rất nhiều!!

 

[cop_kh]

Gởi lại đáp án cho bạn luôn nè:
C4=VLOOKUP(LEFT(B4;2);$A$20:$B$22;2;0)&" "&HLOOKUP(RIGHT(B4;1);$A$25:$C$26;2;0)
K20=SUMIF($B$4:$B$15;A20&"*";$K$4:$K$15)

Góp ý thêm bạn ở ô E4 có thể sửa lại như sau cho tốt hơn nhé:
E4==INDEX($F$20:$H$22;MATCH(RIGHT(B4;1);$E$20:$E$22;0);MATCH(LEFT(B4;2);$F$19:$H$19;0))

 

[tonggiap]

Gởi lại đáp án cho bạn luôn nè:
C4=VLOOKUP(LEFT(B4;2);$A$20:$B$22;2;0)&" "&HLOOKUP(RIGHT(B4;1);$A$25:$C$26;2;0)
K20=SUMIF($B$4:$B$15;A20&"*";$K$4:$K$15)

Góp ý thêm bạn ở ô E4 có thể sửa lại như sau cho tốt hơn nhé:
E4==INDEX($F$20:$H$22;MATCH(RIGHT(B4;1);$E$20:$E$22;0);MATCH(LEFT(B4;2);$F$19:$H$19;0))

ban ah sao ở chỗ "; mình nhập dữ liệu vào lại báo lỗi nhỉ?
&" "& bạn có thể giải thích hộ mình các đó có tác dụng gì ko? mình hời nhiều chuyện nhưng mình muốn hiểu vì mình thấy hơi là!

 

[volga]

ban ah sao ở chỗ "; mình nhập dữ liệu vào lại báo lỗi nhỉ?
&" "& bạn có thể giải thích hộ mình các đó có tác dụng gì ko? mình hời nhiều chuyện nhưng mình muốn hiểu vì mình thấy hơi là!

Bạn nhìn lại công thức , chia ra làm 2 vế rỏ gàng đúng không? (vlookup & hlookup)
& Mang nghĩa là và thôi ,củng như ghép A và B
( A&B) = AB Đúng không ? công thức trên củng vậy .
&" " & .Có nghĩa nom na là . Kết quả của hàm vlookup + khoảng trắng + kết quả của hlookup .

Bạn thay dấu ; thành dấu , thử xem
Thân.

 

[lamphibang]

chỉ giúp tôi 1 công thức nữa

Tôi cần xử lý dữ liệu trên cột A, vd như ô A1 có dữ liệu là KS000003 , ô A2 trống, ô A3 trống, đến ô A4 thì dữ liệu là KS000006, cứ tiếp tục như thế, 1 ô có dữ liệu rồi vài ô kô có dữ liệu,....
tôi cần lập hàm cho cột B sao cho, nếu ô tương ứng bên A có dữ liệu rồi thì hiện dữ liệu ấy ra, nếu ô A tương ứng không có dữ liệu thì hiện dữ liệu của ô A phía trên của nó mà có chứ dữ liệu....
vd: lúc đầu A1 = KS0000003
A2 = blank
A3 = Blank
A4 = KS0000006
A5 = blank
bây giờ tôi muốn B1 = KS000003
B2 = KS000003 (giống B1=A1)
B3 = KS000003 ( tương tự)
B4 = KS000006 (=A4)
B5 = KS000006 (=B4=A4)
help

 

[cop_kh]

tôi cần xử lý dữ liệu trên cột a, vd như ô a1 có dữ liệu là ks000003 , ô a2 trống, ô a3 trống, đến ô a4 thì dữ liệu là ks000006, cứ tiếp tục như thế, 1 ô có dữ liệu rồi vài ô kô có dữ liệu,....
Tôi cần lập hàm cho cột b sao cho, nếu ô tương ứng bên a có dữ liệu rồi thì hiện dữ liệu ấy ra, nếu ô a tương ứng không có dữ liệu thì hiện dữ liệu của ô a phía trên của nó mà có chứ dữ liệu....
Vd: Lúc đầu a1 = ks0000003
a2 = blank
a3 = blank
a4 = ks0000006
a5 = blank
bây giờ tôi muốn b1 = ks000003
b2 = ks000003 (giống b1=a1)
b3 = ks000003 ( tương tự)
b4 = ks000006 (=a4)
b5 = ks000006 (=b4=a4)
help

bạn có thể dùng công thức đơn giản như sau:
ô b1=a1
ô b2=if(a2<>"";a2;b1)

 

[salam]

Tôi cần xử lý dữ liệu trên cột A, vd như ô A1 có dữ liệu là KS000003 , ô A2 trống, ô A3 trống, đến ô A4 thì dữ liệu là KS000006, cứ tiếp tục như thế, 1 ô có dữ liệu rồi vài ô kô có dữ liệu,....
tôi cần lập hàm cho cột B sao cho, nếu ô tương ứng bên A có dữ liệu rồi thì hiện dữ liệu ấy ra, nếu ô A tương ứng không có dữ liệu thì hiện dữ liệu của ô A phía trên của nó mà có chứ dữ liệu....
vd: lúc đầu A1 = KS0000003
A2 = blank
A3 = Blank
A4 = KS0000006
A5 = blank
bây giờ tôi muốn B1 = KS000003
B2 = KS000003 (giống B1=A1)
B3 = KS000003 ( tương tự)
B4 = KS000006 (=A4)
B5 = KS000006 (=B4=A4)
help

Bạn dùng ct dưới xem nhé.

=IF(A1="",LOOKUP(2,1/(1-ISBLANK($A$1:A1)),$A$1:A1),A1)