0973.981.661

dịch vụ thành lập doanh nghiệp

16 bài tập excel 2016 cơ bản đến nâng cao có đáp án

Rất mới dành cho bạn nào quan tâm đến 16 bài tập excel 2016 cơ bản đến nâng cao có đáp án mới nhất của chúng tôi

Bài 1:

  1. Điền dữ liệu cho cột “NGÀNH THI” dựa vào ký tự thứ 1 của “MÃ SỐ” được dò trong bảng “BẢNG TRA ĐIỂM CHUẨN”.
  2. Điền dữ liệu cho cột “KHU VỰC” là ký tự thứ 2 của “MÃ SỐ” và được chuyển về kiểu dữ liệu số.
  3. Điền dữ liệu cho cột  “TỔNG ĐIỂM” là tổng của “ĐIỂM TOÁN”, “ĐIỂM LÝ” và “ĐIỂM HÓA”.
  4. Sắp xếp bảng tính theo “MÃ SỐ” tăng dần.
  5. Điền dữ liệu cho “ĐIỂM CHUẨN” dựa vào ký tự đầu của “MÃ SỐ” được dò trong bảng “BẢNG TRA ĐIỂM CHUẨN” nhưng nếu “KHU VỰC” là 1 thì lấy “ĐIỂM CHUẨN 1” còn nếu “KHU VỰC” là 2 thì lấy “ĐIỂM CHUẨN 2”.
  6. Điền dữ liệu cho “KẾT QUẢ” nếu tổng điểm lớn hơn hoặc bằng “ĐIỂM CHUẨN” thì điền “đậu”, ngược lại điền “rớt”.
  7. Thống kê tổng số thí sinh ngành “Kế Toán” có kết quả đậu.

THÍ SINH CÓ KẾT QUẢ ĐẬU

Kế Toán

 

 
  1. Dùng Advance Filter rút trích ra tất cả các thí sinh có kết quả “đậu”.
  2. Thêm cột “LỚP TÀI NĂNG” bên phải cột “KẾT QUẢ” và điền dữ liệu như sau: Nếu tổng điểm thi lớn hơn hoặc bằng 18 và không có môn nào nhỏ hơn 5 thì điền là “lớp tài năng”, còn ngược lại thì điền là “lớp đại trà”.

Bài 2:

 

Lập công thức để điền dữ liệu vào cột TÊN HÀNG dựa vào hai ký tự đầu của MÃ HÀNG và BẢNG 1.

2. Lập công thức điền vào cột TÊN TỈNH dựa vào ký tự thứ ba của mã hàng và BẢNG 2.

3. Lập công thức điền vào cột SỐ LƯỢNG là ba ký tự cuối của MÃ HÀNG và được chuyển thành dữ liệu kiểu số.

4. Lập công thức điền vào cột ĐƠN GIÁ, nếu sản phẩn được mua trước tháng 5 thì lấy giá 1, còn lại lấy giá 2.

5. Lập công thức điền vào cột THÀNH TIỀN (VND): =SỐ LƯỢNG * ĐƠN GIÁ. Định dạng đơn vị VND.

6. Lập công thức điền vào cột THÀNH TIỀN (USD):

= THÀNH TIỀN (VND) /19000. Nếu sản phẩm được mua trước tháng 7

=THÀNH TIỀN (VND)/20000. Nếu sản phẩm được mua sau tháng 7

Định dạng đơn vị USD.

7. Sắp xếp bảng tính theo thứ tự tăng dần dựa vào cột THÀNH TIỀN (VND)

8. Dùng Advance Filter lọc ra các dòng có TÊN HÀNG là FUTURE.

9. Thực hiện bảng thống kê sau:

Tên hàng

Tổng số lượng

Tổng thành tiền VND)

FUTURE

 

 

10. Tạo Header là Họ và Tên SV ở vị trí trung tâm (center)

Bài 3:

Yêu cầu:

  1. Thiết lập điều kiện cho ba cột Toán, Lý, Hóa: điểm nhập >=0 và <=10.
  2. Chèn thêm hai cột Ngành thi và Khu vực vào bên trái cột Toán; Hai cột Tổng điểm và Điểm Chuẩn vào bên trái cột Kết Quả; Cột Học bổng vào bên phải cột Kết Quả.
  3. Lập công thức cho biết Khu vực và Ngành thi cho từng thí sinh. Biết rằng kí tự thứ 2 của SBD cho biết khu vực; kí tự thứ nhất của SBD cho biết ngành thi.
  4. Tính Tổng điểm là tổng cộng của 3 môn thi.
  5. Lập công thức cho biết điểm chuẩn dựa vào ngành thi và bảng 2, nếu thí sinh ở khu vực 1 thì điểm chuẩn là Điểm chuẩn 1, ngược lại là Điểm chuẩn 2.
  6.  Lập công thức cho cột kết quả, nếu tổng điểm lớn hơn hay bằng điểm chuẩn của ngành dự thi thì kết quả là “Đậu”, ngược lại là “Rớt”.
  7. Lập công thức cho cột học bổng, nếu tổng điểm lớn hơn hay bằng điểm học bổng của ngành dự thi thì học bổng là “Có”, ngược lại để trống.
  8. Thực hiện bảng thống kê như trên.
  9. Trích những thí sinh thi Đậu của khối thi A.
  10. Vẽ biểu đồ (PIE) so sánh thí sinh Đậu và Rớt.

Bài 4:

BÀI 4

STT


Dự Án

Khu Vực

Loại

Dự Án

Chủ Đầu Tư

Vốn
Đầu Tư

Năm
Bắt đầu

Năm
Kết thúc

Mức
 ưu tiên

Tiềm
Năng

1

XD1MNIDC

       

2011

2018

   

2

TH2MBFPT

       

2011

2015

   

3

XD1MTIDC

       

2011

2013

   

4

TH1MBFPT

       

2010

2013

   

5

DL3MBSGT

       

2010

2020

   

6

TH1MNFPT

       

2011

2016

   

7

DL3MTMLI

       

2009

2014

   

8

XD1MNIDC

       

2007

2013

   

9

DL2MTMLI

       

2011

2018

   

 

Bảng Chủ đầu tư & Vốn đầu tư

Chủ đầu tư

MN

MB

MT

IDC

Cty XD IDICO

100  tỉ

200  tỉ

500  tỉ

FPT

Tập đoàn FPT

800  tỉ

1000  tỉ

750  tỉ

SGT

Sài Gòn Tourist

500  tỉ

350  tỉ

200  tỉ

MLI

Tập đoàn Mai Linh

1000  tỉ

1500  tỉ

800  tỉ

 

 

 

 

 

 

 

 

 

 

Bảng loại dự án

Loại Dự Án

XD

Dự án xây dựng

TH

Dự án tin học

DL

Dự án du lịch

 

 

 

 

 

 

 

 

1)Định dạng bảng tính như trên, tạo Header là Họ Và Tên của học viên.

2)Mã khu vực là ký tự thứ 4 và thứ 5 của cột mã dự án

3)Loại dự án dựa vào 2 ký tự đầu của Mã Dự Án và bảng loại Dự Án

4)Chủ đầu tư dựa vào 3 ký tự sau của Mã Dự Án và bảng Chủ đầu tư & Vốn đầu tư

5)Vốn đầu tư dựa vào mã dự án và mã khu vực, tra trong bảng chủ đầu tư & vốn đầu tư

6)Mức ưu tiên dựa vào ký tự thứ 3 của mã dự án

7)Nếu số năm hoạt động <5 & Mức ưu tiên =1 thì ghi "Dự Án Tiềm Năng",  Ngược lại ghi "Không tiềm năng".

8)Trích ra danh sách các dự án thực hiện vào năm 2011

9)       Làm bảng thống kê sau

10) Vẽ đồ thị cho bảng thông kê ở câu 9

       

Tổng Vốn Đầu Tư

Sài Gòn Tourist

 

Tập đoàn FPT

 

 

 

 

 

 

Bài 5:

KẾT QUẢ TUYỂN SINH NĂM HỌC

???

Mã Số

Họ  Và Tên

Ngành Thi

Khu Vực

Toán

Hóa

Kết Quả

H101X

Thị

Nhung

 

 

5.0

8.0

7.0

 

H102S

Hồng

Nguyệt

 

 

6.0

5.0

5.0

 

C203X

Văn

Thao

 

 

2.0

6.0

3.0

 

D240X

Quang

Đạo

 

 

9.0

9.0

7.0

 

M205S

Thu

Thảo

 

 

5.0

5.0

4.0

 

C106X

Thị

Huyền

 

 

10.0

8.0

8.0

 

D107S

Hồng

Nhung

 

 

8.0

6.0

5.0

 

M208X

văn

Thắng

 

 

4.0

3.0

5.0

 

Bảng 1:

Mã Ngành

M

D

C

H

 Ngành thi

Máy tính

Điện Tử

Cơ Khí

Hóa Dầu

Điểm chuần 1

19

16

15

14

Điểm chuần 2

20

18

16

15

Bảng 2:

Mã ngành

Điểm Học Bổng

M

25

D

23

C

21

H

19

 

 

 

 

 

 

 

 

 

  1. Chèn vào trước cột kết quả ba côt: Tổng Cộng, Điểm ChuẩnHọc Bổng.
  2. Dùng hàm lấy ra năm hiện hành "KẾT QUẢ TUYỂN SINH NĂM ???"  .
  3. Lập công thức điền dữ liệu cho cột Khu Vực, khu vực là ký tự thứ 2 của Mã số.
  4. ngành Thi: Dựa vào ký tự đầu của Mã Số (Mã ngành) và Bảng 1.
  5. Điểm Chuẩn: Dựa vào ký tự đầu của Mã số (Mã ngành), Khu vực và Bảng 1. Trong đó, nếu thí sinh thuộc khu vực 1 thì lấy Điểm chuẩn1, ngược lại lấy Điểm chuẩn2.
  6. Tính Tổng cộng là tổng điểm của 3 môn.
  7. Hãy lập công thức điền Kết quả như sau: Nếu thí sinh có điểm Tổng cộng >= Điểm chuẩn của ngành mình dự thi thì sẽ có kết quả là "Đậu", ngược lại là "Rớt".
  8. Cột Học bổng lập công thức điền vào đó là "Có" nếu điểm Tổng cộng của thí sinh >= Điểm học bổng, trường hợp ngược lại để trống.
  9. Sắp xếp lại danh sách Kết quả tuyển sinh theo thứ tự tăng dần của cột điểm Tổng Cộng.
  10. Rút trích: thông tin của các thí sinh thuộc ngành thi Máy Tính .

Bài 6: Nhà sách XYZ có hệ thống 2 chi nhánh

Yêu cầu:

  1. Tạo một Sheet Tổng kết cuối năm để tínhTổng số lượng sách đã bán. ( Dùng chức năng Consolidate ).
  2. Sử dụng chức năng SparkLine vẽ  đồ thị cho bảng thống kê của 2 chi nhánh.
  3. Vẽ biểu đồ cột cho bảng tổng kết của cửa hàng.
  4. Thực hiện chức năng Conditional Formatting, để đánh dấu số lượng cao nhất của từng loại sách

Bài 7: 

BẢNG DOANH SỐ BÁN HÀNG

STT

Mã hàng

Tên hàng

Ngày
mua

Số lượng
mua

Đơn giá
mua

Ngày
bán

Số lượng
bán

Đơn giá
bán

Tiền
chênh lệch

Tiền
hoa hồng

1

S100X

 

01/01/2011

 

 

01/01/2011

 100

 

 

 

2

S150Y

 

02/01/2011

 

 

05/02/2011

 120

 

 

 

3

M300X

 

01/01/2011

 

 

03/01/2011

 250

 

 

 

4

N310X

 

04/01/2011

 

 

02/03/2011

 300

 

 

 

5

D120Y

 

05/01/2011

 

 

05/01/2011

 125

 

 

 

6

N250X

 

01/01/2011

 

 

01/01/2011

 200

 

 

 

7

M120Y

 

01/02/2011

 

 

07/02/2011

 100

 

 

 

8

D100X

 

01/01/2011

 

 

01/01/2011

 95

 

 

 

9

S200X

 

09/01/2011

 

 

09/01/2011

 170

 

 

 

10

S120X

 

01/02/2011

 

 

10/02/2011

 120

 

 

 

Bảng tra 1

 

Bảng tra 2

 

Mã hàng

Tên hàng

Đơn giá mua

 

Mã hàng

S

M

D

N

 

S

Sữa

300,000

 

Giá 1

350,000

200,000

300,000

100,000

 

M

Muối

150,000

 

Giá 2

320,000

190,000

270,000

90,000

 

D

Đường

250,000

           

 

N

Nước khoáng

75,000

           

 

                                       
  1. Định dạng bảng tính như trên, tạo header là họ và tên của học viên.
  2. Tên hàng dựa vào ký tự đầu tiên của mã hàng và bảng tra 1 .
  3. Số lượng mua dựa vào 3 ký tự giữa của mã hàng .
  4. Đơn giá mua dựa vào tên hàng và bảng tra 1 .
  5. Đơn giá bán dựa vào ký tự đầu của mã hàng và bảng tra 2. Chú ý rằng nếu ký tự cuối của mã hàng là X thì chọn đơn giá 1, nếu ký tự cuối của mã hàng là Y thì chọn đơn giá 2.
  6. Tiền chênh lệch = Tiền bán – Tiền mua .
  7. Cột tiền hoa hồng được tính như sau: Nếu như số ngày tồn kho <3 và các mặt hàng phải được bán hết, thì tiền hoa hồng bằng 10% tiền chênh lệch .

Tên hàng

Sữa

Muối

Đường

Nước khoáng

Tổng tiền chênh lệch

 

 

   
  1. Dùng Advanced Filter trích ra những mặt hàng có số lượng bán >=200 .
  2. Thực hiện  bảng thống kê bên dưới .
  3. Vẽ đồ thị cho bảng thống kê ở câu 9 .

Bài 8:

CƯỚC PHÍ BƯU ĐIỆN

STT

Mã bưu kiện

Nơi đến

Phương tiện

Hình thức

Trọng lượng (gram)

Giá cước

Thành tiền

1

MBUSN

 

 

 

500

 

 

2

TLUSE

 

 

 

200

 

 

3

TTAUE

 

 

 

50

 

 

4

TLSIE

 

 

 

250

 

 

5

MBUSN

 

 

 

150

 

 

6

TTSIN

 

 

 

800

 

 

7

TLAUN

 

 

 

250

 

 

8

MBAUE

 

 

 

600

 

 

 

 

GIÁ VẬN CHUYỂN

 

LOẠI PHƯƠNG TIỆN

Tên nước

Giá 1

Giá 2

Giá 3

 

Mã phương tiện

MB

TT

TL

US

USA

19000

18000

17000

 

Tên phương tiện

Máy bay

Tàu thủy

Tàu lửa

FR

France

17000

16000

14500

         

AU

Australia

14000

12000

11200

         

SI

Singapore

12000

10500

10000

         
 

1.        Tạo Header là “họ và tên sinh viên” ở vị trí trung tâm (center).

2.                Nơi đến: Dựa vào hai ký tự thứ 3 và thứ 4 của Mã bưu kiện và dò tìm trong bảng GIÁ VẬN CHUYỂN.

3.                Phương tiện: Dựa vào 2 ký tự đầu trong Mã bưu kiện và dò tìm trong bảng LOẠI PHƯƠNG TIỆN.

4.Sắp xếp bảng tính theo Trọng lượng tăng dần.

5.                Hình thức: Nếu ký tự cuối của Mã bưu kiện là N thì ghi là “Bình thường”, ngược lại ghi  “Nhanh”.

6.       Giá cước: Dựa vào hai ký tụ 3 và 4 của Mã bưu kiện và dò tìm trong bảng GIÁ VẬN CHUYỂN.

ü  Nếu hai ký tự đầu của Mã bưu kiện là “MB” thì lấy giá 1

ü  Nếu hai ký tự đầu của Mã bưu kiện là “TT” thì lấy giá 2

ü  Nếu hai ký tự đầu của Mã bưu kiện là “TL” thì lấy giá 3

7.Thành tiền: = Trọng lượng * Giá cước, nếu hình thức gởi là “Nhanh” thì tăng 10% thành tiền. Định dạng đơn vị tiền tệ là “VNĐ”.

8.                     Thực hiện bảng thống kê theo mẫu:

9.Trích lọc danh sách các bưu kiện đi USA và có trọng lượng > 300 (gram).

Đếm tổng số bưu kiện được giao hàng bằng “Máy bay” và có trọng lượng >300 (gram). 

DOANH THU CÁC HÌNH THỨC GỬI

Phương tiện

Bình thường

Nhanh

Máy bay

?

?

Tàu thủy

 

 

Tàu lửa

 

 

 

 

 

 

 

 

 

 

 

 

Bài 9: 

STT

HỌ

TÊN

MÃ SỐ ĐƠN VỊ

SỐ LƯƠNG

TÊN SẢN PHẨM

ĐƠN GIÁ

GHI CHÚ

THƯỞNG

MỨC LƯƠNG

1

TRẦN

MINH

APX1

24

 

 

 

 

 

2

NAM

BPX2

22

 

 

 

 

 

3

NGUYỄN

HOA

APX1

31

 

 

 

 

 

4

LAN

CPX1

37

 

 

 

 

 

5

AN

CPX2

43

 

 

 

 

 

6

NGUYỄN

BPX1

20

 

 

 

 

 

7

PHAN

MAI

APX2

24

 

 

 

 

 

8

TRẦN

HÙNG

CPX2

19

 

 

 

 

 

 

BẢNG 2

 

A

B

C

Sản phẩm

Nón

Túi Xách

Dây nịt

Đơn giá

15,000

20,000

5,000

BẢNG 3

Số lượng

Thưởng

15

150,000

20

200,000

30

300,000

 

 

 

 

 

 

 

 

Yêu cầu

  1. Nhập liệu và trình bày các bảng được cho như trên
  2. Điền cột TÊN SẢN PHẨM: Dựa vào 1 kí tự thứ nhất của MÃ SỐ ĐƠN VỊ BẢNG 2
  3.  Điền cột GHI CHÚ: nếu 1 kí tự cuối của MÃ ĐƠN VỊ là 1 thì ghi “Phân xưởng 1”, ngược lại  ghi “ Phân xưởng 2
  4. Dựa vào BẢNG 3 tính cột THƯỞNG
  5. Tính tóan cho cột MỨC LƯƠNG= SỐ LƯƠNG * ĐƠN GIÁ + THƯỞNG
  6. Chèn thêm cột PHỤ CẤP, biết nếu MỨC LƯƠNG thấp hơn 600,000 thì sẽ phụ cấp thêm cho đủ 600,000 .
  7. Chèn cột THỰC LÃNH = MỨC LƯƠNG + PHỤ CẤP
  8. Thực hiện bảng thống kê sau:

 

Sản phẩm

Nón

Túi xách

Dây nịt

Tổng số luợng

 

 

 

 

 

 

 

 

  1. Trích  danh sách những nhân viên thuộc đơn vị A
  2. Tạo Header (canh trái) có nội dung là Họ Tên Học Viên; Footer (canh phải) là số trang.

Bài 10:

Mã hàng

Phân loại

Xuất xứ

Số lượng
(m3)

Giá nhập

Giá bán

Doanh thu

Thuế

okal1m

 

 

20

 

 

 

 

okal2m

 

 

23

 

 

 

 

vnia1i

 

 

34

 

 

 

 

okal1i

 

 

21

 

 

 

 

vnia2i

 

 

41

 

 

 

 

vnia1m

 

 

22

 

 

 

 

okal2i

 

 

15

 

 

 

 

vnia2m

 

 

35

 

 

 

 

 

 

 

 

     

 

 

Bảng tra

     
 

okal1

okal2

vnia1

vnia2

     

m

100

80

200

180

     

i

130

90

195

180

     
               

 

 

 

 

 

 

 

 

     

Tổng lợi nhuận từng mặt hàng

 

 

     

 

okal1

okal2

vnia1

vnia2

     

m

       
     

i

       

 

  1. Dựa vào ký tự thứ 5 của mã hàng điền vào cột Phân loại
  2. Dựa ký tự cuối của mã hàng điền cột xuất xứ: nếu là "m" ghi là "malaysia", còn lại ghi "indonesia"
  3. Điền giá nhập dựa vào bảng tra
  4. Điền cột giá bán theo quy định sau:
     - Đối với mặt hàng của "malaysia" : nếu hàng loại 1 thi giá bán > giá nhập 12%,
     hàng loại 2 thi giá bán hơn giá nhập 8%
    - Đối với mặt hàng của indonesia: giá bán hơn giá nhập 15%
  5. Cột doanh thu được tính như sau: (giá bán - giá nhập).   số lượng
  6. Cột thuế được xác định: nếu hàng loại 1: 5%doanh thu, hàng loại 2: 3% doanh thu
  7. Cột lợi nhuận = doanh thu - thuế
  8. Thống kê theo bảng "Tổng lợi nhuận các mặt hàng"
  9. Trích các mặt hàng malaysia loại 1 có lợi nhuận >324
  10. Vẽ biểu đồ bảng thống kê

Xem thêm: thành lập công ty tnhh tại long biên  &&  thu hồi nợ xấu là gì?

Chúc bạn thành công !


Tags: Chua co du lieu

Các bài viết mới

Các tin cũ hơn