Dùng bảng tính excel để tính doanh thu bán hàng của một doanh nghiệp thương mại

Mục lục Giới thiệu chung về Excel 3 Các hàm thông dụng trong Excel 5 Áp dụng lập bảng tính doanh thu bán hàng của một doanh nghiệp 9 thương mại Tài liệu tham khảo 12 I.GIỚI THIỆU CHUNG VỀ EXCEL 1. Giới thiệu chung a, Nguồn gốc: Microsoft Excel (Microsof Office Excel) là chương trình xử lý bảng tính nằm trong bộ Microsoft Office của hãng phần mềm Microsoft. Phiên bản đầu tiên được phát hành trên máy MAC vào năm 1985 và trên Windows vào tháng 11.1987, trung bình cứ 2 năm Microsoft lại cho ra mắt một phiên bản Excel mới một lần. Phiên bản hiện thời là Excel 12 được chạy trên Windows 2007, ra đời ngày 11/12/2007. b, Tính năng: Excel hiện nay là phần mềm chuyên dụng cho công tác kế toán, văn phòng, có thể thực hiện được nhiều phép tính từ đơn giản đến phức tạp. Excel tổ chức và lưu giữ thông tin dưới dạng bảng như bảng lương, bảng kết toán, bảng thống kê, bảng dự toán, và khi có sự thay đổi dữ liệu, bảng tính tự động tính toán lại theo số liệu mới. Thao tác trên bảng tính có thể tạo ra báo cáo tổng hợp hoặc phân tích có kềm theo các biểu đồ, hình vẽ minh hoạ. Excel có một số tính năng đặc trưng cơ bản sau: Auditing: Có thể theo dõi một cách dễ dàng sự phụ thuộc của các ô chứa dữ liệu trong công thức. Auto Fill: Thực hiện việc điền một cách tự động một chuỗi số tuần tự vào phạm vi đã được chỉ định trên bảng tính. Auto Select: Thực hiện một cách tự động việc chọn nhanh phạm vi một khối ô của bảng tính. Chart Wizard,Function Wizard và Top Wizard: Giúp và chỉ dẫn thực hiện việc tạo lập các đồ thị và xem trước kết quả của hàm trong hộp thoại. Data Filter: Lọc ra một cách nhanh chóng các mẩu tin của cơ sở dữ liệu theo điều kiện đã chỉ định. Format Painter: Thực hiện sao chép nhanh dạng thức của dữ liệu trong bảng tính. Group,Consiladate: Nhập và liên kết dữ liệu với nhiều bảng tính cùng một lúc vào màn hình bảng tính. Subtotals: Phân loại và tổng kết các nhóm dữ liệu trong bảng tính. Print Preview: Xem trước trang in trên màn hình và trong khi xem có thể điều chỉnh các lề,chiều rộng,chiều dài của trang in mà không cần phải trở lại bảng tính. Shortcut Menus: là các menu tắt chứa những lệnh thường dùng ở bên cạnh khu vực làm việc. Spell Checking: Kiểm tra lỗi chính tả của các dữ liệu trong bảng tính. Tooltips: Hiển thị công cụ của các nút lệnh trên thanh công cụ. Workbook: Tập hợp các loại bảng tính có quan hệ với nhau vào trong một tập tin. Sử dụng và khai thác dữ liệu của các trình ứng dụng khác. Một ví dụ về tính năng Chart Wizard 2,Khởi đông Excel Khởi đông Excel: Dùng lệnh Start/ Programs/ Microsoft Excel Kích đúp chuột vào biểu tương trên Destop Màn hình làm việc của Excel có dạng như sau: Thanh địa chỉ Thanh công thức Vùng nhập dữ liệu Thanh công thức Thoát khỏi Excel: Dùng lệnh File/ Exit Nhấn nút Close Nhấn tổ hợp phím Alt+F4 II. GIỚI THIỆU MỘT SỐ HÀM THÔNG DỤNG TRONG EXCEL 1, Khái niệm Hàm(Function) là những công thức phức tạp đã được soạn thảo sẵn nhằm thực hiện những yêu cầu tính toán cho vùng giá trị xác định, được cài đặt chung với phần mềm và coi như là tiện ích cho phần mềm đó Hàm sẽ cho kết quả là một giá trị, một chuỗi hoặc một thông báo lỗi trên ô thực hiện Excel có hơn 300 hàm và được chia thành 10 nhóm Hàm toán học(Abs, Average, Sum…) Hàm thống kê (Countif, Rank, Counta…) Hàm tài chính (Rate, Db…) Hàm tra cứu, tham chiếu (Hlookup, Vlookup…) Hàm văn bản (Left, Right, Mid…) Hàm thời gian (Day, Month, Now,…) Hàm logic (If, And, Or, Not…) Hàm cơ sở dữ liệu (Dmax, Daverage, Dmin…) Hàm thông tin (Type, Info, Cell…) Hàm kỹ thuật 2, Cú pháp Cú pháp tổng quát của hàm có dạng sau: =(Danh sách đối số)8 Tên hàm: là tên hàm mẫu do Excel quy định Đối số của hàm có thể là: Các giá trị số. VD: Sum(1,8) Địa chỉ ô, địa chỉ vùng. VD: Average(E12,E13) Một chuỗi ký tự. VD: Left(DHTM,1) Một biểu thức logic. VD: If(A12>=5.0, “Đạt”,”Không đạt”) Tên một vùng. VD: A5*Tiền_công… Lưu ý:Hàm phải được bắt đầu bằng dấu (=) Tên hàm không phân biệt chữ thường và chữ hoa Đối số phải đặt trong dấu ngoặc đơn, giữa các đối số phân cách nhau bởi dấu phẩy 3, Cách nhập hàm Cách 1, Chọn ô cần nhập hàm Gõ dấu = Nhập tên hàm và các đối số theo đúng cú pháp Gõ phím Enter Cách 2, Chọn ô cần nhập hàm Click nút Insert Function trên thanh Standard, hoặc chọn Insert/ Function hoặc gõ tổ hợp phím Shift+F3 Chọn nhóm hàm trong Select a category Chọn hàm cần sử dụng trong Select a function Nhập các dữ liệu hàm Nhấn OK Hộp thoai Insert Function 4, Một số hàm thông dụng trong Excel a, Hàm Sum: tính tổng các giá trị có trong danh sách =Sum( danh sách các trị)8 VD: =Sum(1,2,3)8 cho kết quả là 6 b, Hàm Average : Tính trung bình cộng các giá trị có trong danh sách =Average(danh sách các trị)8 VD : =Average(C12 :C18)8 cho giá trị trung bình cộng trong phạm vi các ô từ C12 đến C18 c, Hàm Max (Min): Cho giá trị lớn nhất (nhỏ nhất) trong các trị số =Max(danh sách các trị)8 VD: Max(10,27,29,45)8 cho kết quả là 45 Min(A2 :A15)8 cho kết quá là giá trị nhỏ nhất trong các ô từ A2 đến A15. d,Hàm Count : Đếm số ô chứa giá trị là số trong một phạm vi =Count(danh sách các trị)8 VD: Count(a,s,f,2,g,5)8 cho kết quả là 2 e, Hàm Round: Làm tròn =Round(biểu _thức_số,n)8 Nếu n>0 làm tròn về bên phải cột thập phân Nếu n<0 làm tròn về bên trái cột thập phân VD: =Round(123.239587,2)8 cho kết quả là 123,24 e, Hàm If: thực hiện biểu thức đúng nếu thoả mãn điều kiện, nếu không sẽ thực hiên biểu thức sai If(Biểu thức điều kiện, Biểu thức 1, Biểu thức 2)8 Biểu thức điều kiện: Biểu thức logic có giá trị đúng hoặc sai, dùng làm điều kiện để kiểm tra Biểu thức 1: Biểu thức được sử dụng khi thoả mãn điều kiện Biểu thức 2: Biểu thức được sử dung khi không thoả mãn điều kiện (Hai biểu thức này có thể là những giá trị số, biểu thức(chuỗi, số, logic) và cũng có thể là một hàm if khác) VD:=If(A2<=5,”kém”, If(A2<=6.5,”TB”, If(A2<=8,”Khá”,”Giỏi”)))8 cho kết quả là Kém nếu A2<5 TB nếu 5≤A2<6.5 Khá nếu 6,5≤A2<8 Giỏi nếu A2≥8 Ngoài một số hàm thông dung trên, để thực hiên tính toán với các số liệu trên các bảng khác nhau người ta còn sử dụng thêm 2 hàm khá thông dụng khác mà nhóm chúng tôi xin giới thiệu sau đây: 1,Hàm Vlookup: Thực hiện việc tìm kiếm giá trị X trên cột đầu tiên của khối khai thác và cho kết quả tương ứng trong những cột khai thác theo chỉ định (Nếu không tìm thấy cho kết quả là VALUE!) Vlookup(X,bảng,cột tham chiếu,cách dò)8 X: có thể là một chuỗi, số, biểu thức... Bảng: Phạm vi khối dữ liệu cần khai thác Cột đầu tiên là cột chứa dữ liệu cần để so sánh với X Các cột kế tiếp bên phải là những cột chứa dữ liệu cần khai thác Cột tham chiếu: Số thứ tự cột(tính từ bên trái bảng, với cột đầu tiên của bảng là cột 1) Cách dò: Là 1 thì danh sách ở cột bên trái của bảng phải xếp theo thứ tự tăng dần. Là 0 thì danh sách ở cột bên trái của bảng không cần xếp theo thứ tự VD: Tính tiền công =Số công*số tiền cho 1 công theo loại A B C D 1 A 150000 2 B 100000 3 C 50000 4 5 Họ tên Loại Số công Tiền 6 An C 20 1000000 7 Bình B 24 2400000 8 Tuấn A 30 4500000 Nhập vào ô D4 công thức: =Vlookup(B6,$A$1:$B$3,2,0)8 ta được kết quả như trên Lưu ý: trong công thức ta dùng địa chỉ tuyệt đối $A$1:$B$3 để đảm bảo địa chỉ này không bị thay đổi trong quá trình sao chép công thức. 2, Hàm Hlookup: Tương tự như hàm Vlooup chỉ khác là hàm Vlookup dò tìm ở cột bên trái, tham chiếu số liệu ở cột bên phải, còn hàm Hlookup dò tìm ở hàng trên cùng, tham chiếu số liệu ở hàng phía dưới. Hlookup(X,Bảng,Hàng tham chiếu,Cách dò)8  III. ỨNG DỤNG LẬP BẢNG TÍNH EXCEL TÍNH DOANH THU BÁN HÀNG CỦA MỘT DOANH NGHIỆP THƯƠNG MẠI A.Bài toán: A B C D E F G H I J K L 2 STT Tên hàng Mã hàng Ngày nhập Ngày bán Nhận xét Đơn giá Số lượng Thành tiền Thuế Thưởng Cộng 3 1 ? ? ? ? 4 2 ? ? ? ? 5 3 ? ? ? ? 6 4 ? ? ? ? 7 5 ? ? ? ? 8 6 ? ? ? ? 9 7 ? ? ? ? 10 8 ? ? ? ? 11 9 ? ? ? ? 12 10 ? ? ? ? 13 Tổng cộng ? ? ? ? ? 14 15 Mã Hàng Thuể 16 17 18 19 20 21 22 Yêu cầu: 1,Nếu thời gian lưu kho 30 ngày và 90 ngày ghi nhận xét bán chậm 2, Tính thành tiền=Số lượng*đơn giá 3,Tính Thuế= thành tiền*thuế 4,Tính thưởng cho nhân viên bán hàng theo thời gian lưu kho như sau:=30 ngày<90 ngày thưởng 1% thànhtiền, các trường hợp khác không được thưởng 5,Tính cộng=Thành tiền-thuế-thưởng 6,Tính tổng cộng các cột số lượng, thuế, thành tiền,thưởng,cộng 7, Trích ra các mặt hàng bán chậm, bán nhanh. B.Các bước thực hiện: 1, Thu thập thông tin điiền vào các cột STT, tên hàng, mã hàng, ngày nhập, ngày xuất, số lượng, đơn giá. 2,Tính thành tiền: Chọn ô I3 Gõ công thức: =G3*H38 Sao chép công thức:: Đặt trỏ chuột tại góc dưới bên phải ô I3 sao cho trỏ chuột biến thành dấu +, giữ và rê chuột theo cột từ ô I3 đến ô I12 3,Ghi nhận xét: Chọn ô F3 Gõ công thức: =If(E3-D3<=30,”Bán nhanh”, If(E3-D3<=90,”Bán được”,”Bán chậm”))8 Sao chép công thức: Đặt trỏ chuột tại g óc dưới bên phải ô F3 sao cho trỏ chuột biến thành dấu +, giữ và rê chuột theo cột từ ô F3 đến ô F12 4, Tính thuế Chọn ô J3 Gõ công thức: =Vlookup(left(C3,1),$A$16:$B$22,2,0)8 Sao chép công thức: Đặt trỏ chuột tại góc dưới bên phải ô J3 sao cho trỏ chuột biến thành dấu +, giữ và rê chuột theo cột từ ô J3 đến ô J12 5,Tính thưởng Chọn ô K3 Gõ công thức: =If(E3-D3<=30,I3*2%,If(E3-D3<=90,I3*1%,0))8 Sao chép công thức Đặt trỏ chuột tại g óc dưới bên phải ô K3 sao cho trỏ chuột biến thành dấu +, giữ và rê chuột theo cột từ ô K3 đến ô K12 6, Tính cộng Chọn ô L3 Gõ công thức: =I3-J3-K38 Sao chép công thức: Đặt trỏ chuột tại g óc dưới bên phải ô L3 sao cho trỏ chuột biến thành dấu +, giữ và rê chuột theo cột từ ô L3 đến ô L12 7, Tính tổng cộng Chọn ô H13 Gõ công thức: Sum(H3:H12)8 Sao chép công thức Đặt trỏ chuột tại g óc dưới bên phải ô H13 sao cho trỏ chuột biến thành dấu +, giữ và rê chuột theo dòng từ ô H13 đến ô L13 8, Trích ra các mặt hàng bán chậm,bán nhanh a,Trích ra các mặt hàng bán nhanh Tạo vùng tiêu chuẩn có vị trí B24:B25 B 24 Nhận xét 25 Bán nhanh Chọn Data/ Filter/ Advance Filter Kích chuột chọn Copy to another location Tại list range gõ địa chỉ vùng cơ sở dữ liệu $A$3:$L$13 Tại Criteria range gõ địa chỉ vùng tiêu chuẩn $B$24:$B$25 Tại Copy to gõ địa chỉ ô đầu của vị trí ghi kết quả. Vd: $B$26 Nhấn OK Làm tương tự với các mặt hàng bán chậm Lưu ý: Cần định dạng kiểu thời gian trước khi nhập hàm ghi nhân xét, kiểu số khi tính thuế, thưởng nếu không Excel sẽ trả về kết quả VALLUE! Sử dụng địa chỉ tuyệt đối khi làm phần 8 IV, TÀI LIỆU THAM KHẢO 1,Giáo trình Tin học Đại cương. NXB Giao thông vận tải 2006, Bùi Thế Tâm 2,Microsoft Office 2000 toàn tập, NXB Thống kê 1999, Trần Văn Tư, Nguyễn Ngọc Tuấn, Hoàng Phương 3,Giáo trình Tin học Đại cương, NXB Thống kê 2004, Hàn Viết Thuận 4,Tin học van phòng, NXB ĐHQG HCM 2004, Lê Thanh Dũng 5,Microsoft Office 2000 thông qua hình ảnh, NXB Thống kê 1999, Nhóm tác giả Elicom 6, www.google.com.vn