giai 12 de excel

<span class='text_page_counter'>(1)</span>Giải 12 đề Đề 1 Câu 1. Loại: dựa vào 2 kí tự đầu của mã sách và tra trong bảng 1 =VLOOKUP(LEFT(B3,2),$A$18:$B$21,2,0) Câu 2. Năm XB: là 4 kí tự 3,4,5 và 6 của mã sách Cách 1=VALUE(MID(B3,3,4)) Cách 2 =MID(B3,3,4)*1 Câu 3. Đề nghị: nếu năm XB từ 2005 trở về sau và số lượng <=5 thì đề nghị mua thêm còn lại bỏ trống =IF(AND(F3>=2005,E3<=5),"mua thêm","") Câu 4. Sắp xếp bảng theo năm XB, trùng năm XB thì sắp theo mã sách Bước 1: Quét hết bảng Bước 2: DataSort. Câu 5. Lập bảng thống kê như mẫu Loại sách Số lượng TK Cách1=SUMIF($B$3:$B$14,D18&"*",$E$3:$E$14) Cách 2=SUMIF($B$3:$B$14,"TK*",$E$3:$E$14) Cách 3=SUMIF($C$3:$C$14,"Tham Khảo",$E$3:$E$14) BT Tương tự TN Tương tự XH Tương tự Lưu ý: Nếu dùng cách 1 chỉ cần làm 1 lần rồi kéo xuống Câu 6. Cách 1. Tạo vùng đk, sau đó vào Data Filter Advanced Filter HS tự làm MÃ SÁCH BT* XH* Cách 2. Tạo vùng đk HS tự làm Loại Bài tập Xã hội Cách 3. Lọc tự động Quét hết bảng DataFilterAuto Filer Tại ô loại click mũi tên xuống chọn Custom Sau đó ok Sau đó chuột phải copychọn nơi đến chuột phải Paste Cuối cùng vào DataFilterAuto Filer để bỏ lọc. Đáp sô 80 14 174 78.

<span class='text_page_counter'>(2)</span> Đề 2 Câu 1. Ngành học dựa vào kí tự đầu của mã số SV theo bảng 1 Cách 1=VLOOKUP(LEFT(D3,1),$A$14:$B$18,2,0) Cách 2=VLOOKUP(LEFT(D3),$A$14:$B$18,2,0) Câu 2. Điểm cc theo số buổi có mặt:3 buổi:1 đ, 2 buổi:0.5đ, còn lại:0 đ =IF(COUNTA(F3:H3)=3,1,IF(COUNTA(F3:H3)=2,0.5,0)) Câu 3. Điểm TC=Điểm thi +Điểm CC =J3+I3 Câu 4. Kết quả: Nếu điểm TC>=5 ghi đậu, ngược lại Rớt =IF(K3>=5,"đậu","rớt") Câu 5. Lập bảng thống kê như mẫu Điểm cao nhất =MAX($K$3:$K$9) Điểm thấp nhất =MIN($K$3:$K$9) Tổng số SV đậu =COUNTIF($L$3:$L$9,"đậu") Tổng số SV rớt =COUNTIF($L$3:$L$9,"rớt") Câu 6. Trích Danh sách sinh viên ngành Anh văn hay Toán – Tin có kết quả đậu Cách 1. Tạo vùng điều kiện NGÀNH HỌC KẾT QUẢ anh văn đậu toán - tin đậu sau đó vào Data Filter Advanced Filter Tiếp HS tự làm Cách 2. Tạo vùng điều kiện MÃ SỐ SV KẾT QUẢ A* đậu T* đậu Tiếp HS tự làm. Cách 3. Lọc tự động Quét hết bảng DataFilterAuto Filer Tại NGÀNH HỌC chọn mũi tên xuống Tại Kết Quả chọn mũi tên xuống chọn đậu Sau đó chuột phải copychọn nơi đến chuột phải Paste Cuối cùng vào DataFilterAuto Filer để bỏ lọc.

<span class='text_page_counter'>(3)</span> Đề 3 Câu 1. Đối tượng:dựa vào kí tự cuối của mã số TS và theo bảng 1 để điền vào Cách 1=HLOOKUP(VALUE(RIGHT(B3,1)),$D$16:$G$17,2,0) Cách 2=HLOOKUP(VALUE(RIGHT(B3)),$D$16:$G$17,2,0) Cách 3=HLOOKUP(RIGHT(B3,1)*1,$D$16:$G$17,2,0) Cách 4=HLOOKUP(RIGHT(B3)*1,$D$16:$G$17,2,0) Lưu ý: =Hlookup(Giá trị dò, bảng dò bấm F4 để cố định, dòng lấy, kiểu dò (0 hoặc 1)) Kiểu dò =0 là dò chính xác Kiểu dò =1 là dò gần đúng Câu 2. Điểm TB: là TB của 3 môn thi làm tròn 2 số lẻ, nếu môn thi nào bỏ thi coi như 0 Cách 1=IF(OR(E3="",F3="",G3=""),0,ROUND(AVERAGE(E3:G3),2)) Cách 2=IF(COUNT(E3:G3)<3,0,ROUND(AVERAGE(E3:G3),2)) Câu 3. Kết quả: nếu điểm TB>=5 ghi ĐẬU, ngược lại RỚT =IF(H3>=5,"đậu","rớt") Câu 4. Xếp loại: Giỏi nếu điểm TB>=8 và không môn nào dưới 5, còn lại bỏ trống =IF(AND(H3>=8,E3>=5,F3>=5,G3>=5),"giỏi","khá") Câu 5. Lập bảng thống kê như mẫu Điểm TB cao nhất =MAX($H$3:$H$13) Điểm TB thấp nhất =MIN($H$3:$H$13) Tổng số TS đậu =COUNTIF($I$3:$I$13,"đậu") Tổng số TS rớt =COUNTIF($I$3:$I$13,"rớt") Câu 6. Trích Danh sách thí sinh là sinh viên hay học sinh có kết quả đậu Cách 1 tạo vùng đk Đối tượng Kết quả SINH VIÊN đậu HỌC SINH đậu DataFilterAdvanced Filter HS tự làm. Cách 2. Lọc tự động Quét hết bảng DataFilterAuto Filer Tại Đối tượng chọn mũi tên xuống Tại Kết Quả chọn mũi tên xuống chọn đậu Sau đó chuột phải copychọn nơi đến chuột phải Paste Cuối cùng vào DataFilterAuto Filer để bỏ lọc.

<span class='text_page_counter'>(4)</span> Đề 4 Câu 1. Khu vực là kí tự thứ 5 và 6 của SBD cho biết khu vực =MID(C3,5,2) Câu 2. Ngành thi dựa vào kí tự cuối và tra trong bảng 1 Cách 1=VLOOKUP(RIGHT(C3,1),$A$18:$B$20,2,0) Cách 2=VLOOKUP(RIGHT(C3),$A$18:$B$20,2,0) Câu 3. Điểm TC=điểm thi+Điểm khu vực =SUM(F3:H3)+VLOOKUP(D3,$D$18:$E$20,2,0) Câu 4. Kết Quả:nếu điểm TC/3 làm tròn 1 số lẻ >=5 ghi ĐẬU, ngược lại RỚT =IF(ROUND(I3/3,1)>=5,"đậu","rớt") Câu 5. Lập bảng thống kê như mẫu Điểm cao nhất =MAX($I$3:$I$13) Điểm thấp nhất =MIN($I$3:$I$13) Tổng số SV đậu =COUNTIF($J$3:$J$13,"đậu") Tổng số SV rớt =COUNTIF($J$3:$J$13,"rớt") Câu 6. Trích Danh sách sinh viên ngành Anh văn hay Toán – Tin có kết quả đậu Đề sai.

<span class='text_page_counter'>(5)</span> Đề 5 Câu 1. Ngành học là kí tự thứ 1 của SBD và tra trong bảng 1 Cách 1=VLOOKUP(LEFT(B4,1),$A$16:$B$19,2,0) Cách 2=VLOOKUP(LEFT(B4),$A$16:$B$19,2,0) Câu 2. Tổng điểm= điểm 3 môn thi với điểm ngành thi nhân 2, ngành Tin học lấy điểm Toán. Ví dụ: thí sinh số 10-CC010 -Phan ánh Tuyết-có tổng điểm là 6.5*2+4+4.5=21.5 Cách 1 =IF(OR(D3="toán",D3="tin học"),E3*2+F3+G3,IF(D3="Hoá học",E3+F3+G3*2,IF(D3="vật lý",E3+F3*2+G3))) Cách 2 =IF(OR(D3=$B$17,D3=$B$16),E3*2+F3+G3,IF(D3=$B$19,E3+F3+G3*2,IF(D3=$B$18,E3+F3*2+G3))) Lưu ý: khi làm cách 1 đánh sai dấu sẽ ra kết qủa sai Vd: Hoá học và Hóa học là khác nhau Câu 3. Kết Quả: nếu tổng điểm>=điểm chuẩn ngành thì ghi ĐẬU, ngược lại RỚT Cách 1=IF(H3>=VLOOKUP(LEFT(D3,1),$A$16:$C$19,3,0),"đậu","rớt") Cách 2=IF(H3>=VLOOKUP(LEFT(D3),$A$16:$C$19,3,0),"đậu","rớt") Cách 3=IF(H3>=VLOOKUP(D3,$B$16:$C$19,2,0),"đậu","rớt") Câu 4. Sắp xếp theo điểm Toán giảm dần, cùng điểm Toán sắp theo điểm Lý giảm dần DataSort. Câu 5. Lập bảng thống kê như mẫu Điểm Toán cao nhất =MAX(E3:E12) Điểm Lý cao nhất =MAX(F3:F12) Điểm Hoá cao nhất =MAX(G3:G12) Tổng số SV đậu =COUNTIF(I3:I12,"đậu") Câu 6. Trích Danh sách sinh viên ngành Toán hay Tin có kết quả đậu Tạo vùng điều kiện Ngành học Kết quả Toán đậu Tin học đậu HS tự làm.

<span class='text_page_counter'>(6)</span> Đề 6 Câu 1. Nhà XB: dựa vào kí tự cuối của mã sách và tra trong bảng 1 Cách 1=VLOOKUP(VALUE(RIGHT(B3,1)),$A$17:$B$21,2,0) Cách 2=VLOOKUP(VALUE(RIGHT(B3)),$A$17:$B$21,2,0) Cách 3=VLOOKUP(RIGHT(B3,1)*1,$A$17:$B$21,2,0) Cách 4=VLOOKUP(RIGHT(B3)*1,$A$17:$B$21,2,0) Câu 2. Năm XB: là 4 kí tự 3,4,5 và 6 của mã sách Cách 1=VALUE(MID(B3,3,4)) Cách 2=MID(B3,3,4)*1 Câu 3. Đề nghị: nếu năm XB từ 2005 trở về sau và số lượng <=5 thì đề nghị mua thêm còn lại bỏ trống =IF(AND(F3>=2005,E3<=5),"mua thêm","") Câu 4. Sắp xếp theo nhà XB, trùng nhà XB thì sắp theo nam XB giảm dần DataSort. Câu 5. Lập bảng thống kê như mẫu Số lượng Nhà XB Cách 1 =SUMIF($C$3:$C$14,D18,$E$3:$E$14) Giáo Dục Cách 2 =SUMIF($C$3:$C$14,"Giáo Dục",$E$3:$E$14) Khoa học Kỹ thuật Văn Học Kỹ Thuật Câu 6. Trích Danh sách các sách thể loại giáo dục hay văn học Cách 1. Tạo vùng đk NXB Giáo Dục Văn Học DataFilterAdvanced Filter HS tự làm. Đáp số 14 147 9 80.

<span class='text_page_counter'>(7)</span> Đề 7 Câu 1. Hiệu đĩa: dò tìm dựa vào 2 kí tự đầu của Mã HD và dò ở Bảng 1 =VLOOKUP(LEFT(A3,2),$A$16:$B$18,2,0) Câu 2. Dung lượng: là 3 kí tự thứ 3,4 và 5 từ trái qua của mã HD và thêm chuỗi "MB" =MID(A3,3,3)&"MB" Câu 3. Thành tiền: =Đơn giá*Số lượng và có giảm giá cho các trường hợp sau nếu số lượng >=200 giảm 7% nếu số lượng >=100 giảm 5% Các trường hợp khác không giảm Cách 1 =E3*D3*IF(D3>=200,93%,IF(D3>=100,95%,100%)) Cách 2=IF(D3>=200,93%*E3*D3,IF(D3>=100,95%*E3*D3,E3*D3)) Câu 4. Sắp xếp danh sách trên theo hiệu đĩa, nếu trùng hiệu đĩa thì sắp theo số lượng giảm dần Data  Sort. Câu 6. Lập bảng 2-Bảng thống kê Tổng số lượng đĩa Philips Số lượng HĐ có thành tiền trên 200.000. Cách 1=SUMIF($B$3:$B$12,"Philips",$D$3:$D$12) Cách 2=SUMIF($B$3:$B$12,B16,$D$3:$D$12) =SUMIF($G$3:$G$12,">=200000",$D$3:$D$12). ĐS: 35 ĐS: 650. Câu 5. Dùng Advanced Filter để trích ra các hóa đơn mua đĩa hiệu Bách Việt hay số lượng nhỏ hơn 10 và chép kết quả vào ô A25. Lưu ý không xóa vùng điều kiện Tạo vùng đk Hiệu đĩa. Số lượng. Bách Việt <10 DataFilterAdvanced Filter HS tự làm.

<span class='text_page_counter'>(8)</span> Đề 8 Câu 1. Tên hàng:dựa vào Mã hàng và tra ở Bảng 1 =VLOOKUP(B4,$A$18:$B$24,2,0) Câu 2. Đơn vị tính: Nếu 2 kí tự đầu của Mã hàng là NS hay VT thì ghi "TẤN" còn không ghi "THÙNG" =IF(OR(LEFT(B4,2)="TP",LEFT(B4,2)="VT"),"tấn","thùng") Câu 3. Thành tiền: =Giá đơn vị USD*số lượng*Tỉ giá USD/VNĐ =H4*F4*$C$2 Sau đó định dạng VNĐ Chuột phảiFormat CellsCustom tại ô type gõ #,##0 "VNĐ"ok Câu 4. Sắp xếp danh sách trên theo mã hàng, nếu trùng mã hàng thì sắp theo số lượng giảm dần DataSort. Câu 5. Dùng Advanced Filter để trích ra các mẫu tin có là thực phẩm hay có xuất xứ Asean chép kết quả vào vị trí qui ước. Lưu ý không xóa vùng dữ liệu Tạo vùng đk Xuất xứ Mã hàng Asean TP* DataFilterAdvanced Filter HS tự làm Câu 6. Lập bảng Thống kê như trong đề Tổng số lượng nông sản có xuất xứ từ Asean =SUMIF($D$4:$D$13,"Asean",$F$4:$F$13).

<span class='text_page_counter'>(9)</span> Đề 9 1. Loại hàng: nếu 2 kí tự đầu của mã hàng là TP thì loại hàng là Thực phẩm, nếu là VT loại là vật tư, còn nếu là NS thì loại là nông sản =IF(LEFT(B4,2)="TP","thực phẩm",IF(LEFT(B4,2)="VT","Vật tư","Nông sản")) 2. Đơn giá: Dựa vào 5 kí tự đầu của mã hàng và dò trong bảng 1 đọc ở cột đơn giá =VLOOKUP(LEFT(B4,5),$A$18:$B$24,2,0) 3. Thành Tiền: Số lượng*Đơn giá và có giảm giá 10% nếu số lượng >=100 Cách 1= =G4*F4*IF(F4>=100,90%,100%) Cách 2=IF(F4>=100,90%*G4*F4,G4*F4) 4. Sắp xếp danh sách theo số lượng tù nhỏ đến lớn, nếu trùng sắp theo mã hàng Data  Sort. 5. Dùng Advanced Filter để trích ra danh sách có xuất xứ Asean và số lượng lớn hơn hay bằng 100 và chép kết quả vào ô A25. Lưu ý không xóa vùng dữ liệu Tạo vùng đk Số Xuất xứ lượng Asean >=100 DataFilterAdvanced Filter HS tự làm 6. Lập bảng Thống kê như trong đề Cách 1 =SUMIF($E$4:$E$13,"Nông sản",$F$4:$F$13) Tổng số lượng nông sản Cách 2=SUMIF($B$4:$B$13,"NS*",$F$4:$F$13) ĐS:2078 Tổng số lượng vật tư Tổng số lượng thực phẩm. Tương tự Tương tự. ĐS:62155 ĐS:8957.

<span class='text_page_counter'>(10)</span> Đề 10 1. Loại: dựa vào 2 kí tự đầu của mã sách và tra bảng 1 để điền vào =VLOOKUP(LEFT(B3,2),$A$17:$B$20,2,0) 2. Năm XB: là 4 kí tự 3,4,5 và 6 của mã sách và đổi sang kiểu số Cách 1=VALUE(MID(B3,3,4)) Cách 2=MID(B3,3,4)*1 3. Đề nghị: nếu năm XB từ 2005 trở về sau và số lượng <=5 thì đề nghị mua thêm còn lại bỏ trống =IF(AND(F3>=2005,E3<=5),"mua thêm","") 4. Sắp xếp bảng theo năm XB, trùng năm XB thì sắp theo mã sách Data  Sort. 5. Lập bảng thống kê như mẫu Đề thiếu SỐ Loại LƯỢNG Xã hội >=5 Bài tập >=5 6. Trích danh sách các sách loại xã hội hay bài tập có số lượng <=10 Tạo vùng đk SỐ Loại LƯỢNG Xã hội <=10 Bài tập <=10 DataFilterAdvanced Filter HS tự làm.

<span class='text_page_counter'>(11)</span> Đề 11 1. Đối tượng: dựa vào kí tự cuối của mã số TS và theo bảng 1 để điền vào Cách 1=HLOOKUP(VALUE(RIGHT(B3,1)),$B$16:$E$17,2,0) Cách 2=HLOOKUP(VALUE(RIGHT(B3)),$B$16:$E$17,2,0) Cách 3=HLOOKUP(RIGHT(B3,1)*1,$B$16:$E$17,2,0) Cách 4=HLOOKUP(RIGHT(B3)*1,$B$16:$E$17,2,0) 2. Điểm TB: là TB của 3 môn thi làm tròn 0.5, nếu môn thi nào bỏ thi coi như 0 Cách 1=IF(OR(E3="",F3="",G3=""),0,ROUND(AVERAGE(E3:G3)*2,0)/2) Cách 2=IF(COUNT(E3:G3)<3,0,ROUND(AVERAGE(E3:G3)*2,0)/2) 3. KẾT QuẢ: nếu điểm TB>=5 thì "Đạt" ngược lại "Không đạt" =IF(H3>=5,"đạt","không đạt") 4. Xếp hạng: dựa trên điểm TB với điểm cao nhất là hạng 1 =RANK(H3,$H$3:$H$13,0) 5. Lập bảng thông kê như mẫu Bước 1: Tạo cột phụ tại cột K Tại ô K3 gõ công thức=RIGHT(B3,1)&I3 Sau đó kéo xuống Đối tượng 1 2 3 4 Tương Tương Tương Tổng số TS đậu ==COUNTIF($K$3:$K$13,"1đạt") tự tự tự Lưu ý: nếu dùng cách 1 thì kéo sang phải là xong 6. Trích Danh sách thí sinh đối tượng là học sinh hoặc sinh viên có kết quả đạt Tạo vùng đk Đối tượng Kết quả HỌC SINH đạt SINH VIÊN đạt DataFilterAdvanced Filter HS tự làm.

<span class='text_page_counter'>(12)</span> Đề 12 1. Ngành học là kí tự thứ 1 của SBD và tra trong bảng 1 Cách 1=VLOOKUP(LEFT(B3,1),$A$16:$B$19,2,0) Cách 2=VLOOKUP(LEFT(B3),$A$16:$B$19,2,0) 2. Tổng điểm= điểm 3 môn thi =SUM(E3:G3) 3. Kết Quả: nếu tổng điểm>=15 và không có môn nào dưới 5 thì ghi ĐẬU, ngược lại RỚT =IF(AND(H3>=15,E3>=5,F3>=5,G3>=5),"đậu","rớt") 4. Sắp xếp theo điểm Toán giảm dần, cùng điểm Toán sắp theo điểm Lý giảm dần. Lưu ý: Asceding: tăng dần Descending: giảm dần 5. Lập bảng thống kê như mẫu Điểm Toán cao nhất =MAX(E3:E12) Điểm Lý cao nhất =MAX(F3:F12) Điểm Hoá cao nhất =MAX(G3:G12) Tổng số SV đậu =COUNTIF($I$3:$I$12,"đậu") 6. Trích Danh sách sinh viên ngành Toán hay Tin có điểm toán >=7 Tạo vùng đk Ngành Điểm học toán Toán >=7 Tin học DataFilterAdvanced Filter HS tự làm.

<span class='text_page_counter'>(13)</span>