Bài tập Excel - Bài 2

Chú ý: Học viên tạo một tập tin với tên là tenhocvien_số của bài thực hành Bài thực hành số 1 Công thức và Hàm            A  B  A+ B  A-B  A*B  A/B  A lũy thừa B  A&B   1  10         3  8         5  6         7  4         9  2         A  B  A2 + B  (A + B)2  (A - B)*A  (A+B)*A - B  (A+B)*(A-B)  ((A+B)/(A-B))4   16  4         5  3         Yêu cầu: học viên hoàn thành các công thức trên Bài thực hành số 2 BẢNG LƯƠNG NHÂN VIÊN THÁNG …../2005   STT  HỌ VÀ TÊN  CHỨC VỤ  LƯƠNG CƠ BẢN  NGÀY  LƯƠNG  TẠM ỨNG  CÒN LẠI   1  Nguyễn Văn A  TP  350000  25            2  Trần Văn B  NV  330000  24            3  Lương Sơn Bá  PP  340000  26            4  Chúc Anh Đài  NV  330000  26            5  Nguyễn Y Chang  KT  335000  20            6  Vũ Như Cẩn  BV  340000  28            7  Đổng Như Khôi  TQ  335000  26            8  Sang Song Luôn  PP  350000  24            9  Nguyễn Trùng Dương  NV  330000  27            10  Lục Vân Tiên  BV  335000  29            Tổng cho các cột Lương, Tạm Ứng, Còn lại            Giá trị cao nhất cho các cột Lương, Tạm Ứng, Còn lại            Giá trị thấp nhất cho các cột Lương, Tạm Ứng, Còn lại            Giá trị trung bình cho các cột Lương, Tạm Ứng, Còn lại                          Yêu Cầu:          1. Trình bày bảng tính trên    2. Tính lương tháng:=Lương Cơ Bản * Ngày Công    3. Tạm Ứng =50%*Lương Tháng    4. Còn Lại:=Lương Tháng - Tạm Ứng    5. Tính Tổng, Cao nhất, Thấp nhất, Trung bình cho các cột Lương, Tạm Ứng, Còn Lại    6. Định dạng các cột: Lương, Tạm Ứng, Còn Lại ở dạng "VNĐ"            Bài thực hành số 3 BẢNG KÊ HÀNG NHẬP THÁNG …./2004 TẠI KHO QUẢNG NGÃI STT  CHỨNG TỪ  DIỄN GIẢI  SỐ LƯỢNG  ĐƠN GIÁ  THUẾ  CHUYÊN CHỞ  CỘNG   1  B50  Bàn gỗ  40  1000000      2  B51  Bàn sắt  45  1500000      3  B52  Bàn Formica  85  550000      4  B53  Ghế dựa  42  360000      5  B54  Ghế xếp  12  450000      6  B55  Ghế đẩu  36  120000      7  B56  Tủ gổ  52  2500000      8  B57  Tủ sắc  54  3700000                        Yêu Cầu:         1. Trình bày bảng tính trên     2. Chèn thêm cột trị giá vào sau cột đơn giá rồi tính Trị Giá:=Số Lượng*Đơn Giá   3. Tính Thuế:=Trị Giá*3.75% làm tròn đến hàng trăm.    4. Cước chuyên chở:=Số Lượng*0.75% Trị giá, làm tròn đến hàng nghìn.   5. Tính Tổng cộng:=Trị giá+Thuế+Chuyên chở   6. Định dạng cột Đơn giá, Thuế, Trị giá, Chuyên chở, Cộng có dấu phân cách phần nghìn.   Bài thực hành số 4 DOANH SỐ BÁN HÀNG CỦA TT THƯƠNG MẠI VÀ PHÁT TRIỂN CÔNG NGHỆ ĐẠI VIỆT   TỪ 1- 5/7/2001  Bảng 1   NGÀY  MÃ HÀNG  TÊN HÀNG  ĐƠN VỊ TÍNH  SỐ LƯỢNG  ĐƠN GIÁ  THUẾ  THÀNH TIỀN   1  MT    3      1  C    2      1  MI    1      2  BP    2      2  MT    2      2  UPS    1      3  HDD    4      3  HDD    2      3  MT    1      3  MH    2      4  MT    2      4  C    10      4  MI    3      5  ODC    6      5  CS    4      5  FDD    8      5  MT    3      Bảng 2              MÃ HÀNG  TÊN HÀNG  ĐƠN VỊ TÍNH  ĐƠN GIÁ     MT  Máy tính  Bộ  5200000     C  Chuột  Con  35000     MI  Máy in  Cái  4200000     BP  Bàn phím  Cái  60000     UPS  Tích điện  Cái  850000     HDD  Ổ đĩa cứng  Ổ  1000000     CDR  Ổ đĩa CDROM  Ổ  450000     MH  Màn hình  Cái  1420000     CS  Case  Cái  170000     FDD  Đĩa mềm  Hộp  65000     Yêu cầu:         Câu 1: Dựa vào số liệu đã cho ở Bảng 2, dùng công thức để lấy Tên hàng, Đơn vị tính, Đơn giá cho Bảng 1 trên.   Câu 2: Tính tiền thuế (cột Thuế), biết rằng: Nếu là Máy tính hoặc Máy in thì Thuế = 5% đơn giá.   Còn những thiết bị khác thì Thuế = 10% đơn giá.   Câu 3: Tính số tiền (cột Tiền) cho các mặt hàng được bán ra trong Bảng 1.   Câu 4: Chèn vào cuối Bảng 1 3 dòng để hiển thị các kết quả sau:   Lần lượt tính tổng số tiền bán Máy tính, Máy in, Màn hình, ....   Tính số lần bán mặt hàng Máy tính, Máy in, Màn hình, ....   Bài thực hành số 5 Tạo bảng tính với số liệu thô như sau: KẾT QUẢ TUYỂN SINH NĂM 2006 - ĐẠI HỌC ĐÀ NẴNG   STT  SBD  HỌ TÊN  ĐTCS  TOÁN  LÝ  HÓA  ĐUT  TỔNG  KẾT QUẢ   1  BK001  Tô Linh Kha  LS   ?  ?   ?  ?  ?  ?   2  BK002  Trúc Lan Mai  TB                     3  BK003  Cổ Gia Trưởng  BB                     4  BK004  Chúc Anh Đài  KH                     5  KT001  Hồ Mỹ Nhân  TB                     6  KT002  Đoàn Tự Cổ  KH                     7  SP001  Cao Bất Hứa  LS                     8  SP002  Vi Nhân Gian  TB                     BẢNG CHẤM   BẢNG ĐIỂM ƯU TIÊN    SBD  MÔN 1  MÔN 2  MÔN 3   ĐTCS  LS  TB  BB    BK001  6  7  6   ĐIỂM UT  2  1  0.5    KT001  9  9  5         BK002  3  7  5         SP001  9  7  6         BK003  8  8  7         KT002  7  6  9         SP002  7  4  5         BK004  4  3  4         Trong đó: SBD: Số báo danh dự thi của thí sinh ĐTCS: Mã đối tượng chính sách, TB (thương binh), LS (liệt sỹ); BB (bệnh binh); KH(không) ĐUT: Điểm ưu tiên, tuỳ thuộc vào ĐTCS cho trong Bảng điểm ưu tiên, nếu thí sinh nào có ĐTCS là “KH” thì Điểm ưu tiên (ĐUT) bằng không TỔNG: Tổng số điểm bao gồm điểm 3 môn thi cộng với điểm ưu tiên KẾT QUẢ: Kết quả thi (ĐẬU hay KHÔNG), biết rằng chỉ tiêu của Nhà trường là tuyển vào 5 thí sinh có tổng điểm thi cao nhất HƯỚNG DẪN Các công thức sử dụng: IF, VLOOKUP, HLOOKUP, RANK kết hợp với địa chỉ tuyệt đối Tính điểm Toán (môn 1): =VLOOKUP() Tính điểm Lý (môn 2): =VLOOKUP() Tính điểm Hoá (môn 3): =VLOOKUP() Tính Điểm ưu tiên: =IF(HLOOKUP()) Tính Tổng điểm: = Toán + Lý + Hóa + Điểm Ưu Tiên. Tính Kết quả:=IF(RANK("ĐẬU", "KHÔNG") Bài thực hành số 6 Bảng Giá Thanh Toán Tiền Điện Tháng 12/2005   CHỦ HỘ  LOẠI  SÔCŨ  SỐMỚI  SỐKW  ĐM  TĐM  NĐM  T.TĐM  T.NĐM  CỘNG   MỘT  CQ-A  200  250   ?  ?   ?  ?  ?  ?  ?   HAI  NN-D  240  280                        BA  CB-A  340  450                        BỐN  CQ-A  430  540                        NĂM  CB-B  230  450                        SÁU  CB-C  120  540                        BẢY  CN-D  210  350                        TÁM  CN-C  260  410                        CHÍN  CQ-B  290  450                        MƯỜI  CQ-D  320  540                        NHẤT  NN-A  110  230                        NHÌ  KD-C  230  410                              Chú thích:      Bảng số KW định mức   SỐKW: Số KW tiêu thụ       A  B  C  D   ĐM: Số KW định mức    CQ  50  60  70  75   TĐM: Số KW tiêu thụ trong định mức   CB  40  42  55  65   NĐM: Số KW tiêu thụ ngoài định mức   CN  20  50  70  95   T.TĐM: Số tiền trong định mức   KD  60  80  95  120   T.NĐM: Số tiền ngoài định mức   NN  30  60  75  85   CỘNG: Tổng số tiền phải trả    Bảng đơn giá (trong và ngoài định mức)           CB  CQ  CN  KD  NN   Chú thích:      TĐM  1200  2300  5400  5500  3000   TĐM: Đơn giá trong định mức   NĐM  1000  1800  4500  4000  2500   NĐM: Đơn giá ngoài định mức   Bài thực hành số 7 KẾT QUẢ KỲ THI TIN HỌC KHOÁ NGÀY ....../....../200....     STT  HỌ TÊN  NĂM SINH  QUÊ QUÁN  ĐLT  ĐTH  ĐTBKT  T.BÌNH  VỊ THỨ  GHI CHÚ   1  Nguyễn Văn A  1985  Đà Nẵng  3  6  5            2  Trần Văn B  1968  Quảng Nam  7  2  7            3  Lương Sơn Bá  1974  Quảng Ngãi  5  7  5            4  Chúc Anh Đài  1972  Huế  3  6  7            5  Vũ Như Cẩn  1977  Bình Định  4  4  5            6  Nguyễn Y Vân  1958  Quảng Ngãi  7  2  3            7  Wright York  1971  Tobaco&Trinida  4  8  7            8  Trần Bình Trọng  1968  Quảng Ngãi  8  9  7            9  Zidine Zidane  1972  France  6  9  4            10  David Beckham  1973  England  7  9  4            Yêu cầu:           1. Trình bày bảng tính trên.   2. Tính điểm trung bình cho các học viên: T.BÌNH=(ĐLT+ĐTH+ĐTBKT)/3, làm tròn số với 2 số lẻ ở phần thập phân.   3. Xếp hạng cho lớp theo dạng [Vị thứ] / [Tổng số]. Ví dụ : 1/10, 5/10.   4. Nếu các học viên có cùng vị thứ với nhau thì cột GHI CHÚ phải ghi chú là Đồng hạn.   5. Sắp xếp danh sách học viên theo thứ tự tăng dần của Năm sinh, nếu trùng thì theo thứ tự giảm dần của cột T.BÌNH.   6. Dùng Data\Filter\Advanced Filter để lọc trích ra danh sách những học viên ở Quảng Ngãi.   Bảng kết quả sau khi thực hiện các yêu cầu trên   STT  HỌ TÊN  NĂM SINH  QUÊ QUÁN  ĐLT  ĐTH  ĐTBKT  T.BÌNH  VỊ THỨ  GHI CHÚ   1  Nguyễn Văn A  1985  Đà Nẵng  3  6  5  4.67  8/10    2  Trần Văn B  1968  Quảng Nam  7  2  7  5.33  6/10  Đồng hạn   3  Lương Sơn Bá  1974  Quảng Ngãi  5  7  5  5.67  5/10    4  Chúc Anh Đài  1972  Huế  3  6  7  5.33  6/10  Đồng hạn   5  Vũ Như Cẩn  1977  Bình Định  4  4  5  4.33  9/10    6  Nguyễn Y Vân  1958  Quảng Ngãi  7  2  3  4.00  10/10    7  Wright York  1971  Tobaco&Trinida  4  8  7  6.33  3/10  Đồng hạn   8  Trần Bình Trọng  1968  Quảng Ngãi  8  9  7  8.00  1/10    9  Zidine Zidane  1972  France  6  9  4  6.33  3/10  Đồng hạn   10  David Beckham  1973  England  7  9  4  6.67  2/10                  QUÊ QUÁN   Đây là vùng điều kiện để lọc trích(Criteria)      Quảng Ngãi         Kết quả sau khi lọc trích           STT  HỌ TÊN  NĂM SINH  QUÊ QUÁN  ĐLT  ĐTH  ĐTBKT  T.BÌNH  VỊ THỨ  GHI CHÚ   3  Lương Sơn Bá  1974  Quảng Ngãi  5  7  5  5.67  5/10      6  Nguyễn Y Vân  1958  Quảng Ngãi  7  2  3  4.00  10/10      8  Trần Bình Trọng  1968  Quảng Ngãi  8  9  7  8.00  1/10      Bài thực hành số 8 BÁO CÁO KINH DOANH KHO VẬT TƯ               STT  HỌ & TÊN  CHỨNG TỪ  TÊN VẬT TƯ  SỐ LƯỢNG  ĐƠN GIÁ  THÀNH TIỀN   1  A  X01C     50         2  B  D02K     1050         3  C  N03C     150         4  D  X04C     2550         5  E  D05K     250         6  F  G06C     320         7  G  D08K     120         8  H  G08C     240         9  I  X01C     300         10  J  N09K     125         TỔNG CỘNG            TRUNG BÌNH             BẢNG PHỤ 1     MÃ CT  D  G  N  X     TÊN VẬT TƯ  DẦU LỬA  GASOL  NHỚT  XĂNG     ĐƠN GIÁ  120  140  130  100              BẢNG PHỤ 2        SCT  THUẾ        D  70%        G  80%        N  90%        X  95%               Lưu bảng tính trên với tên Kinhdoanhvattu    1. Xác định Tên vật tư, Đơn Giá thông qua bảng phụ 1    2. Tính cột Thành Tiên:=Số lượng*Đơn giá*Thuế    3. Tính Tổng Cộng và Trung Bình cho các cột.    4. Sắp xếp bảng tính theo thứ tự tăng dần của cột thành tiền    5. Vẽ đồ thị dạng 3D Pie cho cột số lượng (theo cột họ tên)    6. Tạo bảng dữ liệu(Lọc) gồm các mẫu tin có tên vật tư là Dầu lửa có số lượng bán ra lớn hơn 200 7. Lập bảng thống Kê:   Tổng số tiền bán được của các mặt hàng   Dầu lửa  Xăng  Nhớt       Bài thực hành số 9 BẢNG BÁO CÁO KINH DOANH XĂNG   TRAM XĂNG DẦU 1/5         KD  CC        XUẤT  3500  2500        NHẬP  3300  1200   STT  K-HÀNG  SỐ-CT  SỐ-LG  TIỀN CHI  TIỀN THU  T-NHẬP  T-XUẤT   1  A  N100C                  2  B  X2000K                  3  C  X120K                  4  D  N230C                  5  E  X1200C                  6  F  N2230K                  7  G  N1000C                  8  H  X200K                  9  I  X300K                  10  J  N1200K                  11  K  X2000K                  12  L  X120K                  13  M  N2000C                  14  N  N300K                  15  O  X1220K                  TỔNG SỐ                  Diễn Giải:         - Ký tự đầu tiên của số chứng từ cho biết hàng xuất hay nhập:   + "X" là xuất, "N" là nhập       - Ký tự cuối cùng cho biết hàng xuất(nhập) theo giá cung cấp hay kinh doanh   + "K":kinh doanh, "C": cung cấp      - Các ký tự còn lại cho biết số lượng hàng xuất hay nhập   Yêu cầu:   1. Tính(a): Số lượng hàng xuất, nhập   2. Tính tiền chi và tiền thu: Số lượng * Đơn giá   + Tiền chi là tiền nhập hàng hóa và tiền thu là tiền xuất hàng hóa   + Ký tự cuối cùng cho biết tính theo giá kinh doanh hay cung cấp   3. Tính thuế nhập và thuế xuất biết:   + Nếu số lượng Nhập theo giá Cung cấp lớn hơn 800 thì phải nộp thuế là 7% Tiền chi, ngược lại 10% Tiền chi   + Nếu số lượng Xuất theo giá Kinh doanh lớn hơn 800 thì phải nộp thuế là 5% Tiền thu, ngược lại 10% Tiền thu   4. Tính tổng của các cột        5. Lưu bảng tính với tên tenhocvien_9      6. Định dạng cột tiền có dấu phần cách phần nghìn và theo đơn vị tiền tệ "Đồng"   Bài thực hành số 10 TÍNH TIỀN Ở KHÁCH SẠN   STT  HỌ VÀ TÊN  MÃ SỐ  NGÀY ĐẾN  NGÀY ĐI  SNGAY  TUẦN  NGÀY  T.NGÀY  T.TUẦN   1  Nguyễn Văn Hay  A1  4/12/2001  25/04/2001     100000      2  Trần Tự Lực  B2  5/23/2001  12/06/2001     120000      3  Nguyễn Thị Mai Lê  A1  3/14/2001  16/04/2001     100000      4  Phan Thanh Thùy Linh  C2  6/16/2001  20/06/2001     160000      5  Lê Thị Tuyết Mai  A2  5/1/2001  30/05/2001     100000      6  Trấn Vân Quỳnh  B1  5/2/2001  14/05/2001     120000      7  Dương Cao Anh Dương  C2  6/12/2001  26/06/2001     160000      8  Trần Minh Hảo  C1  6/10/2001  17/06/2001     160000      9  Lê Văn Phận  B3  6/16/2001  24/06/2001     120000      Tổng cộng             BẢNG 1:TIỀN NGÀY    BẢNG 2:TIỀN TUẦN       Phòng đơn  Phòng đôi      Phòng đơn  Phòng đôi    A  100000  150000   A  600000  900000    B  120000  170000   B  720000  1020000    C  160000  180000   C  960000  1080000                       Yêu cầu:           1. Nhập và trình bày bảng tính       2. Tính cột số ngày: Ngày đi - Ngày đến        3. Dùng hàm INT() Tính số tuần ở khác sạn của khách cho cột Tuần (13 ngày = 1 tuần + 6 ngày)   4. Dùng hàm MOD() Tính số ngày lẻ ở khách sạn của khách cho cột Ngày   5. Tính cột T.Ngày (Tiền ngày) dựa vào Mã số và Bảng 1, số 1 bên phải chỉ phòng đơn Số 2 chỉ phòng đôi   6. Tính tiền tuần dựa vào mã số và Bảng 2   7. Cột Tổng tiền := Tiền Tuần + Tiền Ngày   Bài thực hành số 11    SỬ DỤNG HÀM MATCH VÀ INDEX                    HÃY NHẬP BẢNG TÍNH SAU    Ngày  Nơi Đi  Nơi Đến  Cự Ly  Đơn Giá  Thành Tiền    05/03/2003  Đà Nẵng  Tam Kỳ             06/05/2003  Tam kỳ  Huế             12/05/2003  Sài Gòn  Đà Nẳng             17/06/2003  Đà Nẵng  Đà Nẳng             20/06/2003  Quảng Ngãi  Đà Nẳng             25/06/2003  Hội An  Huế              Bảng Cự Ly    Nơ Đi/Nơi Đến  Đà Nẵng  Tam Kỳ  Sài Gòn  Hội An  Huế    Đà Nẵng  10  74  1000  30  100    Tam Kỳ  74  10  960  44  144    Sài Gòn  1000  960  30  970  1100    Hội An  30  44  970  5  130    Huế  100  144  1100  130  15    Quảng Ngãi  135  60  900  104  204           Yêu Cầu         1. Điền dữ liệu cho cột cự ly   2. Tính đơn giá theo yêu cầu sau   - Cự ly <100 Km: Đơn giá là 3.000 đồng   - 100 <= cự ly < 500 Km: Đơn giá là 2.500 đồng   - Cự ly >=100 Kmlà 2.200 đồng   3. Tính thành tiền =Cự ly * Đơn giá   4. Tính Tổng cộng cho cột thành tiền   5. Hãy chèn thêm một cột tháng vào sau cột ngày. Sau đó, dựa vào số liệu cột ngày để điền dữ liệu cho cột tháng.   6. Tính thống kê Tổng tiền của từng tháng   7. Lọc danh sách cho những chuyến có Nơi đi hoặc Nơi đến là Đà Nẵng