Bài giảng Phân tích kinh doanh bằng máy tính

ĐẠI HỌC KINH TẾ VÀ QUẢN TRỊ KINH DOANH PHÒNG THỰC HÀNH KINH DOANH ----------- ----------- BÀI GIẢNG PHÂN TÍCH KINH DOANH BẰNG MÁY TÍNH (Tài liệu lưu hành nội bộ) Trần Công Nghiệp Thái Nguyên 2007 MỤC LỤC MỤC LỤC ..................................................................................................................1 CHƯƠNG I XÂY DỰNG CƠ SỞ DỮ LIỆU ĐỂ QUẢN LÝ THÔNG TIN TRONG KINH DOANH ...........................................................................................3 1. Khái niệm về cơ sở dữ liệu ........................................................................................... 3 1.1 Các bảng dữ liệu như là một cơ sở dữ liệu đơn giản .................................................................3 1.2 Bản ghi và trường trong cơ sở dữ liệu .......................................................................................3 1.3 Cơ sở dữ liệu quan hệ ................................................................................................................3 1.4 Các phép tính trên cơ sở dữ liệu ................................................................................................5 2. Lựa chọn phần mềm máy tính để xây dựng cơ sở dữ liệu .......................................... 7 2.1 Cơ sở dữ liệu trong Visual FoxPro ............................................................................................7 2.2 Cơ sở dữ liệu trong Access........................................................................................................8 2.3 Bảng tính excel như là một cơ sở dữ liệu đơn giản ...................................................................8 3. Xây dựng các cơ sở dữ liệu .......................................................................................... 8 3.1. Khảo sát nhu cầu thực tế để xây dựng ......................................................................................8 3.2. Xây dựng các mô hình thực thể................................................................................................9 CHƯƠNG II - MICROSOFT EXCEL ..................................................................16 1. Giới thiệu .................................................................................................................... 16 1.1. Khởi động và thoát khỏi Excel ...............................................................................................16 1.2. Một số khái niệm cơ bản ........................................................................................................18 1.3. Màn hình làm việc của Excel. ................................................................................................20 1.4. Làm việc với Worksheet.........................................................................................................22 1.5. Nhập và sửa dữ liệu ................................................................................................................24 1.6. Sao chép dữ liệu cho dãy các ô liên tục..................................................................................24 1.7. Tách bảng tính ........................................................................................................................25 1.8. Ẩn hiện bảng tính ...................................................................................................................25 1.9. Tạo bảng tính mới...................................................................................................................26 1.10. Lưu file bảng tính lên đĩa .....................................................................................................26 1.11. Lưu các thay đổi trên một file đã tồn tại...............................................................................27 1.12. Lưu file với tên khác.............................................................................................................27 1.13. Lưu file vào đĩa mềm............................................................................................................27 1.14. Mở một tập tin đã tồn tại trên đĩa. ........................................................................................27 2. Làm việc với bảng tính Excel ..................................................................................... 28 2.1. Xử lý ô, cột, dòng ...................................................................................................................28 2.2. Các dạng dữ liệu trong Excel .................................................................................................32 2.3. Các phép toán trong Excel......................................................................................................34 3. Sử dụng hàm trong Excel........................................................................................... 35 3.1. Khái niệm hàm và cách dùng .................................................................................................35 3.2. Cách dùng một số hàm toán học.............................................................................................36 3.3. Cách dùng một số hàm xử lý ký tự.........................................................................................40 3.4. Cách dùng một số hàm hàm về thời gian ...............................................................................41 3.5. Cách dùng một số hàm logic ..................................................................................................43 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 2 3.6. Cách dùng một số hàm cơ sở dữ liệu......................................................................................43 3.7. Cách dùng một số hàm tìm kiếm ............................................................................................45 4. Cơ sở dữ liệu trong Excel ........................................................................................... 46 4.1. Khái niệm cơ sở dữ liệu..........................................................................................................46 4.2. Thao tác với cơ sở dữ liệu ......................................................................................................46 4.3. Đồ thị trong Excel ..................................................................................................................50 5. In ấn bảng tính ........................................................................................................... 52 1. Thiết kế sổ nhật kí chung ........................................................................................... 55 1.1 Xây dựng danh mục tài khoản:................................................................................................55 1.2 Xây dựng sổ nhật kí chung .....................................................................................................56 2. Thiết kế bảng đối chiếu số phát sinh phát sinh ......................................................... 57 3. Xây dựng bảng cân đối kế toán.................................................................................. 58 4. Xây dựng bảng báo cáo kết quả sản xuất kinh doanh (phần Lỗ lãi) ........................ 62 5. Xây dựng báo cáo lưu chuyển tiền tệ......................................................................... 64 6. Tiến hành phân tích ................................................................................................... 65 6.1 Phương pháp phân tích ............................................................................................................65 6.2 Phân tích các chỉ số chủ yếu ....................................................................................................66 6.3 Phân tích hiệu quả sử dụng vốn ...............................................................................................70 7 Đánh giá hàng tồn ...................................................................................................... 78 7.1. Xây dựng hệ thống bảng biểu trong excel ..............................................................................78 7.2 phân tích theo phương pháp thực tế đích danh ........................................................................79 7.3 Phân tích theo phương pháp đơn giá trung bình......................................................................80 7.4 Phân tích theo phương pháp FIFO...........................................................................................81 CHƯƠNG IV CÁC KĨ THUẬT HOẠCH ĐỊNH ...................................................84 1 Các hàm tài chính trong Excel.................................................................................... 84 1.1 Các hàm khấu hao tài sản cố định ...........................................................................................84 1.2 Các hàm đánh giá và tính toán hiệu quả vốn đầu tư ................................................................85 2 Kĩ thuật dự báo trong Excel........................................................................................ 87 2.1 Dự báo bằng phương pháp trung bình giản đơn. .....................................................................87 2.2 Dự báo bằng phương pháp trung bình động ............................................................................88 2.3 Sử dụng đồ thị để dự báo ........................................................................................................89 2.4 Dự báo bằng hồi qui của Excel................................................................................................89 3 Giải các bài toán qui hoạch tuyến tính trên Excel ..................................................... 94 3.1. Nhắc lại bài toán quy hoạch tuyến tính ..................................................................................94 3.2 Các bước giải bài toán quy hoạch tuyến tính trong excel........................................................96 3.3 Các lựa chọn khi giải bài toán quy hoạch tuyến tính............................................................100 3.4 Mở rộng bài toán ..................................................................................................................102 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 3 CHƯƠNG I XÂY DỰNG CƠ SỞ DỮ LIỆU ĐỂ QUẢN LÝ THÔNG TIN TRONG KINH DOANH 1. Khái niệm về cơ sở dữ liệu 1.1 Các bảng dữ liệu như là một cơ sở dữ liệu đơn giản Mỗi bảng ( table ) như bảng thống kê, kế toán, bảng niêm yết giá hàng, bảng danh sách cán bộ ghi chép dữ liệu về một nhóm phần tử nào đó gọi là thực thể. Thực thể: Là một nhóm người, đồ vật, sự kiện, hiện tượng hay khái niệm bất kỳ với các đặc điểm tính chất cần ghi chép lại. Một số thực thể có vẻ vật chất như vật tư, máy móc, khách hàngcòn một số khái niệm khác chỉ là quan niệm như dự án, tài khoản Mỗi thực thể đều có những đặc điểm gọi là thuộc tính. Mỗi thuộc tính dùng để mô tả thực thể và là những dữ liệu ta cần lưu trữ về thực thể đó. Các bảng dữ liệu được coi như một cơ sở dữ liệu đơn giản. Ví dụ, Công ty Phú Lợi hiện có trong kho những mặt hàng nhập khẩu sau Mã hàng Tên hàng Giá ĐV ( Tr đ) Số lượng Thuế ( % ) Cước vận chuyển ( %) TV02 Ti vi SANYO, 21 inches 5.5 29 4 8 TV01 Ti vi SANYO, 16 inches 4.5 38 4 8 TL01 Tủ lạnh TOSHIBA 3.7 18 6 10 DT03 Điện thoại dây 0.43 265 2 2 Để đơn giản hoá Công ty dùng một bảng gồm 6 cột và 5 dòng để lưu trữ dữ liệu những mặt hàng nhập khẩu có trong kho. 1.2 Bản ghi và trường trong cơ sở dữ liệu - Bản ghi: Mỗi bảng có nhiều dòng. Mỗi dòng được gọi là một bản ghi ( record ) bởi nó ghi chép dữ liệu về một cá thể. Chẳng hạn mỗi dòng của bảng sinh viên dữ liệu về một sinh viên cụ thể như anh Nguyễn Văn A chẳng hạn. - Trường : Mỗi bảng gồm có nhiều cột. Mỗi cột được gọi là một trường ( filed). Giao giữa một dòng và một cột là ô chứa dữ liệu của một thuộc tính của cá thể trên dòng đó. Chẳng hạn, ngoài họ tên, anh Nguyễn Văn A còn có các thuộc tính khác như ngày sinh, quê quán, khoá học 1.3 Cơ sở dữ liệu quan hệ - Cơ sở dữ liệu: là một nhóm gồm một hay nhiều bảng có liên quan với nhau. Chẳng hạn, tất cả các bảng liên quan đến sinh viên như danh sách khoa, danh sách môn học, các mức học phí cùng với bảng sinh viên hợp thành cơ sở dữ liệu sinh viên. Từ những năm 70, mô hình dữ liệu quan hệ do Codd đưa ra với cấu trúc hoàn chỉnh đã tạo cơ sở toán học cho các vấn đề nghiên cứu dữ liệu. Với cấu trúc đơn giản và khả năng hình thức hoá phong phú, CSDL quan hệ dễ dàng mô phỏng các hệ thống thông tin đa dạng trong thực tế. Lưu trữ thông tin tiết kiệm, có tính độc lập dữ liệu cao, dễ sửa đổi, bổ sung cũng như khai thác dữ liệu là những ưu điểm nổi bật của CSDL quan hệ. Việc nghiên cứu của Codd để sáng lập ra mô hình quan hệ vào ba mục tiêu. Mục tiêu thứ nhất là vạch ra một đường gianh giới rõ ràng giữa phương diện logic và vật lý của việc quản trị CSDL. Khi đó các nhà lập trình ứng dụng không cần phải để ý tới cách trình bày dữ liệu trên các phương tiện vật chất nữa. Codd 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 4 gọi mục tiêu này là mục tiêu độc lập dữ liệu. Mục tiêu thứ hai là tạo ra một mô hình đơn giản mà đông đảo các nhà lập trình và những người dùng có thể hiểu được ngay, đây là mục tiêu truyền đạt nhằm tăng hiệu quả của việc trao đổi giữa người dùng và các cán bộ hệ thống thông tin quản lý. Mục tiêu thứ 3 gọi là mục tiêu xử lý tập hợp, nhằm tăng khả năng xử lý “lần lượt từng bản ghi” đến “ đồng thời nhiều bản ghi “. Đạt được những mục tiêu này có nghĩa là chỉ cần viết một số lệnh cho các trình ứng dụng đồng thời những người dùng và những cán bộ quản lý sẽ ít hiểu lầm nhau hơn trong giao tiếp. Mô hình quan hệ có ba thành phần cơ bản: - Các cấu trúc dữ liệu - Các quy tắc vẹn toàn - Các phép toán được dùng để tìm và biến đổi dữ liệu 1.3.1 Cấu trúc dữ liệu Mô hình quan hệ dựa trên các cấu trúc và khái niệm sau đây - Miền: Một miền là một tập hợp giá trị thuộc cùng một kiểu dữ liệu. Ví dụ, miền tên nước là tập hợp các tên nước trên thế giới, miền hệ số lương là tập hợp các hệ số lương có thể có theo quy định về các bậc lương. Một miền cần chứa các giá trị nguyên tố, tức là không chia nhỏ hơn nữa. Giá trị thực tiễn của khái niệm miền là giúp ta xác định những phép so sánh nào có thể thực hiện được. Chỉ những giá trị thuộc tính rút ra cùng một miền mới có thể so sánh được với nhau. So sánh hai giá trị từ hai miền khác nhau là không so sánh được. - Quan hệ: Một quan hệ là một bảng gồm n cột ( hay thuộc tính ) và m dòng (hay bộ). Mỗi cột có tên duy nhất và tất cả các giá trị trong cột được rút ra từ cùng một miền. Mỗi dòng của quan hệ được xác định một cách duy nhất. Trật tự của các cột và dòng không quan trọng. - CSDL quan hệ: Một cơ sở dữ liệu quan hệ là một tuyển tập các quan hệ ( hay các bảng ). Các bảng được kết nối với nhau bởi những cột chung rút ra từ cùng một miền. Hai cột chung ở hai bảng không nhất thiết phải có cùng một tên nhưng các giá trị trong đó phải được rút ra từ cùng một miền để có thể so sánh được với nhau. - Khoá chính: Khoá chính của một quan hệ là yếu tố phân biệt duy nhất của nó. Khoá chính có thể là tổ hợp của một vài cột. - Khoá ngoại lai: Khoá ngoại lai là khái niệm quan trọng trong mô hình quan hệ. Đó là phương tiện để biểu diễn các mối quan hệ và có thể coi như một thứ “ hồ dính “ để dính một số bảng thành một CSDL quan hệ. 1.3.2 Quy tắc vẹn toàn Quy tắc vẹn toàn thực thể: Không một thành phần nào của khoá chính trong một quan hệ có thể là rỗng. Quy tắc vẹn toàn trong quan hệ: Một CSDL không được chứa những giá trị khoá ngoại lai nào mà không thể tìm thấy giá trị khoá chính tương ứng trùng hợp với nó. 1.3.3 Ví dụ về CSDL quan hệ Mô hình CSDL Sinh viên. SINH_VIEN Ma sinh vien Ten sinh vien BANG_DIEM Ma sinh vien Ma mon hoc So trinh Diem MON_HOC Ma mon hoc Ten mon hoc 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 5 Người ta thiết kế CSDL sinh viên trên để quản lý họ tên, ngày tháng năm sinh và điểm của các môn học của sinh viên. 1.4 Các phép tính trên cơ sở dữ liệu Mô hình quan hệ bao hàm một tập hợp phép toán trên quan hệ, gọi là đại số quan hệ. Đại số quan hệ dùng như một tiêu chuẩn để đánh giá các ngôn ngữ tìm kiếm dữ liệu, như SQL chẳng hạn có thể biểu diễn được tất cả các phép toán của đại số quan hệ thì nó được coi là đầy đủ về mặt quan hệ. Sáu phép toán cơ bản trên tập hợp được áp dụng trên tập các bộ giá trị quan hệ, đó là: Hợp ( Union ), Hiệu ( Minus), Giao, Tích đề các, Phép chia và Phép bù. Giả thiết: U = { A1, A2, A3.An} R và S là hai quan hệ được định nghĩa trên U có cùng thứ tự của các thuộc tính. Và ở đây chúng ta luôn luôn giả thiết R và U có số lượng hữu hạn bộ giá trị. 1.4.1. Phép hợp Hợp của hai quan hệ R và S - được ký hiệu là R U – là một quan hệ Q xác định trên tập thuộc tính U, có cùng thứ tự thuộc tính như trong quan hệ R và S, được định nghĩa như sau: Q= R U = { t / t R hoặc t  S} Nói một cách nôm na, hợp của hai quan hệ là một quan hệ có cùng ngôi với hai quan hệ đó với các bộ giá trị bằng gộp các bộ giá trị của cả R và S ; những bộ giá trị trùng nhau chỉ được giữ lại 1 bộ. Ví dụ: Quan hệ Đơn vị A có các bộ giá trị sau Mã số Họ tên Giới tính Chức danh Lương Mã ĐV 100 Nguyễn Văn Nam Nam Giám đốc 2.500.000 10 101 Hoàng Thị Xuân Nữ Kế toán 1.700.000 11 103 Đặng Ngọc Chiến Nữ Thư ký 1.000.000 10 105 Phan Kỳ Nhân Nam Lái xe 700.000 10 Quan hệ Đơn vị B có các bộ giá trị như sau Mã số Họ tên Giới tính Chức danh Lương Mã ĐV 210 Phan Kỳ Nhân Nữ Trưởng Phòng 1.200.000 30 101 Hoàng Thị Xuân Nữ Kế toán 1.700.000 11 221 Nguyễn Hữu Ngọc Nam Phó Phòng 1.000.000 30 223 Hoàng Thao Nam Chuyên viên 1.000.000 30 Hợp của hai quan hệ trên cho kết quả sau: Mã số Họ tên Giới tính Chức danh Lương Mã ĐV 100 Nguyễn Văn Nam Nam Giám đốc 2.000.000 10 101 Hoàng Thị Xuân Nữ Kế toán 1.200.000 11 103 Đặng Ngọc Chiến Nam Lái xe 850.000 10 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 6 105 Phan Kỳ Nhân Nam Bảo vệ 700.000 10 210 Phan Kỳ Nhân Nữ Trưởng Phòng 1.200.000 30 221 Nguyễn Hữu Ngọc Nam Phó Phòng 1.000.000 30 223 Hoàng Thao Nam Chuyên viên 1.000.000 30 1.4.2 Phép trừ hai quan hệ Hiệu của hai quan hệ R và S, được ký hiệu là R – S, là một quan hệ Q xác định trên tập thuộc tính U, có cùng thứ tự thuộc tính như trong quan hệ R và S và được định nghĩa như sau: Q= R – S = { t / t  R và t S } Ví dụ : Với hai quan hệ như trên, hiệu của Đơn vị A và Đơn vị B là Mã số Họ tên Giới tính Chức danh Lương Mã ĐV 100 Nguyễn Văn Nam Nam Giám đốc 2.000.000 10 103 Đặng Ngọc Chiến Nam Lái xe 850.000 10 105 Phan Kỳ Nhân Nam Bảo vệ 700.000 10 1.4.3 Giao của hai quan hệ Giao của hai quan hệ R và S, được ký hiệu R  S, là một quan hệ Q xác định trên tập thuộc tính U, có cùng thứ tự thuộc tính như trong quan hệ R và S, được định nghĩa như sau: Q= R   S = { t / t R và t  S} Nói một cách nôm na, giao của hai quan hệ R và S là một quan hệ có cùng ngôi với quan hệ R và S với các bộ giá trị là các bộ giống nhau của cả hai quan hệ. Ví dụ: Với hai quan hệ như trên, hiệu của A và B là: Mã số Họ tên Giới tính Chức danh Lương Mã ĐV 100 Nguyễn Văn Nam Nam Giám đốc 2.000.000 10 101 Hoàng Thị Xuân Nữ Kế toán 1.200.000 11 1.4.4. Tích Đề – các của hai quan hệ R ( A1,A2, , An} và S ( B1,B2,,Bn} là hai quan hệ có bộ giá trị hữu hạn. Tích đề các của hai quan hệ R và S, được ký hiệu là R x S, là một quan hệ Q xác định trên tập thuộc tính của R và S ( với n + m thuộc tính ) và được định nghĩa như sau: Q= R x S = { t / t có dạng ( a1,a2, , an, b1, b2,,bn) trong đó ( a1,a2, , an) R và (b1, b2,,bn) S} Nói một cách nôm na, tích Đề – các của hai quan hệ R và S là một quan hệ có số ngôi bằng tổng số ngôi của R và S, với các bộ giá trị gồm hai phần : Phần bên trái là một bộ giá trị của R và phần bên phải là một bộ giá trị của S. Như vậy, nếu R có n1 bộ giá giá và S có n2 bộ giá trị thì Q sẽ có n1 x n2 bộ giá trị. Ví dụ: R (A, B, C) R x S = Q (A, B, C, D, E, F) a1, b1 c1 a1 b1 c1 d1 e1 f1 a2, b2 c2 a1 b1 c1 d2 e2 f2 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 7 a3, b3 c3 a2 b2 c2 d1 e1 f1 vaứ S (D, E, F) a2 b2 c2 d2 e2 f2 d1 e1 f1 a3 b3 c3 d1 e1 f1 d2 e2 f2 a3 b3 c3 d2 e2 f2 1.4.5. Phép chia R là quan hệ n ngôi và S là quan hệ m ngôi ( n> m và S   ), có m thuộc tính chung ( thuộc tính có thể so sánh được ) giữa R và S. Phép chia hai quan hệ R và S, ký hiệu R S , là một quan hệ Q có n – m ngôi được định nghĩa như sau: Q= R S = { t/ sc : u S, (t,u)  R} Ví dụ A B A divide B X Y Y X X1 Y1 Y1 X1 X1 Y2 Y2 X1 Y3 X2 Y1 X3 Y3 1.4.6. Phép bù của một quan hệ Cho quan hệ R( A1, A2,,An) với các miền giá trị của thuộc tính A1 là MGT(A1). Phép bù của quan hệ R là quan hệ Q xác định trên tập thuộc tính R+, được định nghĩa như sau: Q=  R = { t ( a1, a2, , an) và a1 MGT(A1) i =1n, t  R } Nghĩa là tập hợp tất cả các bộ giá trị có thể có của tích Đề – các miền giá trị MGT ( A1 ) nhưng chưa có mặt trong thể hiện của quan hệ R. Quan hệ bù của một quan hệ có số lượng bộ giá trị rất lớn, vì vậy trong thực tế rất ít hệ quản trị CSDL cài đặt phép toán này. 2. Lựa chọn phần mềm máy tính để xây dựng cơ sở dữ liệu Để giải quyết tốt tất cả các vấn đề đặt ra cho một CSDL như: Tính chủ quyền, cơ chế bảo mật hay phân quyền khi khai thác CSDL, giải quyết tranh chấp trong quá trình truy nhập dữ liệu và phục hồi dữ liệu khi có sự cố thì phải có một hệ thống các phần mềm chuyên dụng. Hệ thống các phần mềm đó được gọi là hệ quản trị cơ sở dữ liệu. Đó là các công cụ hỗ trợ tích cực cho các nhà phân tích và thiết kế CSDL và những người khai thác CSDL. Cho đến nay có khá nhiều hệ quản trị CSDL mạnh được đưa ra thị trường như: Visual FoxPro, Microsoft Access, SQL-Sever, oracle với chất lượng khác nhau. Mỗi hệ quản trị CSDL đều được cài đặt dựa trên mô hình dữ liệu cụ thể. Hầu hết các hệ quản trị hiện nay dựa trên mô hình quan hệ. 2.1 Cơ sở dữ liệu trong Visual FoxPro Trong Visual FoxPro, cơ sở dữ liệu dùng để tổ chức, thiết lập mối quan hệ giữa các bảng. Cơ sở dữ liệu trong Visual FoxPro cung cấp cấu trúc dùng để lưu trữ dữ liệu và thêm các điều kiện tốt nhất để quản trị cơ sở dữ liệu. Hệ quản trị cơ sở dữ liệu Visual FoxPro mang đến cho bạn những khả năng mở rộng giúp bạn trong nhiều lính vực khi phát triển ứng dụng và cơ sở dữ liệu. Bạn có thể thấy sự tiến bộ trong thực thi, hay sử dụng nguồn tài nguyên hệ thống và môi trường thiết kế. Hệ quản trị cơ sở dữ liệu Visual FoxPro được dùng nhiều trong việc xây dựng các chương trình kế toán cho các doanh nghiệp. 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 8 2.2 Cơ sở dữ liệu trong Access Theo đánh giá của PC Word vào năm 1999 thì hiện nay Microsoft Access đã giành được phần chia lớn trên thị trường. Access là một trong những chương trình quan trọng trong tổ hợp chương trình Microsoft Office Professional do hãng phần mềm Microsoft sản xuất. Access hoạt động trong môi trường Windows. Hệ quản trị cơ sở dữ liệu Microsoft Access có những ưu điểm vượt trội so với nhiều hệ quản trị cơ sở dữ liệu khác, tuy nhiên chỉ thích hợp với những cơ sở dữ liệu vừa và nhỏ. 2.3 Bảng tính excel như là một cơ sở dữ liệu đơn giản Excel cho phép xây dựng, cập nhật, truy suất thông tin từ cơ sở dữ liệu như một hệ quản trị cơ sở dữ liệu. ở đây cơ sở dữ liệu là một tập hợp các dữ liệu có quan hệ với nhau, được tổ chức lưu trữ theo cấu trúc dòng, cột. Người dùng có thể thực hiện truy vấn dữ liệu trong cơ sở dữ liệu Exel và lập những báo cáo dựa trên cơ sở dữ liệu. 2.4 Các hệ quản trị cơ sở dữ liệu khác như Oracle, SQL_Server - Oracle là một bộ phần mềm được cung cấp bởi công ty ORACLE, nó bao gồm một bộ xây dựng các ứng dụng và các sản phẩm cuối cùng cho user (end_uer product). - Oracle cung cấp một hệ quản trị CSDL mềm dẻo nó bao gồm CSDL Oracle, môi trường cho việc thiết kế các cơ sở dữ liệu (Designer 2000) và các công cụ phát triển (Developer 2000).... - Hệ quản trị CSDL có tính an toàn, bảo mật cao, tính nhất quán và toàn vẹn dữ liệu, cho phép các user truy nhập tới CSDL phân tán như một khối thống nhất ... Vì vậy nó được đánh giá là ưu việt nhất hiện nay . 3. Xây dựng các cơ sở dữ liệu 3.1. Khảo sát nhu cầu thực tế để xây dựng 3.1.1 Khảo sát ban đầu Mục đích khảo sát  Cần phải làm sáng tỏ những câu hỏi sau: - Có cần thiết phải tiến hành không? - Cái gì cần tiếp tục? - Thời gian làm? - Chi phí bao nhiêu?  Phương pháp tiến hành Việc khảo sát ban đầu được tiến hành qua các bước sau: - Xác định những điều cần thực hiện để giải quyết vấn đề phù hợp với Công ty. - Xác định phạm vi vấn đề. - Xác định các User là những người trực tiếp chịu sự chi phối bởi sự phát triển của hệ thống. - Viết báo cáo ban đầu về quá trình khảo sát.  Kết quả chính của phương pháp khảo sát ban đầu Sau khi kết thúc giai đoạn này cần có một bản báo cáo các phần sau: - Phát biểu vấn đề - Thông tin rõ ràng về các vấn đề được đề cập và không đề cập. - Xem xét công việc: Xem xét góc độ tổ chức và sự quản lý nó dựa từ kết quả thu được của sự phát triển hệ thống. 3.1.2. Khảo sát chi tiết 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 9 Mục tiêu của giai đoạn này là tìm những giải pháp tối ưu về mặt kỹ thuật tài chính, thời gian ràng buộc, xác định dòng thông tin, đánh giá và lựa chọn giải pháp.  Lĩnh vực khảo sát bao gồm - Chi tiết hoá các mục tiêu - Xác định các nguồn thông tin, yêu cầu thông tin.  Cách tiếp cận - Là quá trình tìm hiểu về hệ thống, dữ liệu, khả năng và cách sử dụng hệ thống. - Xem xét các ứng dụng theo từng bước. - Tập trung trước hết vào các kết quả đạt được từ đó xác định nguồn thông tin tạo ra nó. - Công việc bắt đầu từ người quản lý cấp cao nhất trở xuống. - Bảo đảm rằng mọi người đều biết điều gì xảy ra.  Tổ chức và khảo sát chi tiết - Xem xét lại hồ sơ. - Xem xét lại các nguồn từ hội thảo. - Lập kế hoạch khảo sát. - Giao trách nhiệm cho các thành viên trong nhóm. - Kiểm tra công việc.  Xác định nhu cầu của Công ty - Phải hỗ trợ sao cho Công ty đạt được mục tiêu tăng doanh thu, giảm chi phí và tăng chất lượng phục vụ. - Đối với từng vấn đề cần xác định ảnh hưởng của nó tơi Công ty và các nhu cầu về thông tin vào ra, các thao tác 3.1.3 Một số phương pháp khảo sát Công việc khảo sát được tiến hành sau khi đã đánh giá đúng yêu cầu. Người ta có thể dùng các phương pháp thu thập thông tin khác nhau phục vụ cho mục đích khảo sát như: Phỏng vấn, Nghiên cứu tài liệu, sử dụng phiếu điều tra, quan sát. Phương pháp phỏng vấn và nghiên cứu tài liệu là hai công cụ thu thập thông tin đắc lực nhất và được sử dụng rộng rãi. Phỏng vấn cho phép thu được những xử lý theo cách khác với mô tả trong tài liệu, có thể gặp được người chịu trách nhiệm trên thực tế, số người này có thể không ghi trên văn bản của tổ chức, thu được những nội dung cơ bản khái quát về hệ thống mà nội dung có thể khó nắm bắt được khi tài liệu quá nhiều. Đặc biệt là mục tiêu của tổ chức. Nghiên cứu tài liệu cho phép nghiên cứu kỹ về nhiều khía cạnh của tổ chức. Thông tin trên giấy tờ phản ánh quá khứ, hiện tại và tương lai của tổ chức. Khi chúng ta cần phải lấy thông tin từ một số lượng lớn các đối tượng và trên phạm vi địa lý rộng thì ta phải sử dụng phiếu điều tra. Yêu cầu của các câu hỏi trên phiếu điều tra phải rõ ràng, cùng hiểu như nhau. Phiếu điều tra ghi theo cách dễ tổng hợp. Phương pháp Quan sát được sử dụng khi phân tích viên muốn nhìn thấy những gì không thể hiện trên tài liệu hoặc qua phỏng vấn như tài liệu để ở đâu, đưa cho ai, có sắp xếp hay không, tuy nhiên quan sát gặp khó khăn vì người bị quan sát không thực hiện giỏng ngày thường. 3.2. Xây dựng các mô hình thực thể 3.1.1 Mô hình hoá dữ liệu là gì? Mô hình hoá dữ liệu là quá trình xác định các chi tiết dữ liệu và những mối quan hệ cần được lưu trữ trong một CSDL. Đó cũng là quá trình trao đổi về cách thiết kế CSDL giữa người này với người khác. Mục đích là tạo ra một mô hình dữ liệu phản ánh chính xác nhu cầu dữ liệu và các mối quan hệ dữ liệu trong thế giới thực tại. Những yếu tố cơ bản hợp thành một mô 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 10 hình dữ liệu là: Các thực thể, các thuộc tính của mỗi thực thể, yếu tố phân biệt của mỗi thực thể và những mối quan hệ giữa các thực thể. Việc xây dựng mô hình dữ liệu đòi hỏi một sự phối hợp chặt chẽ giữa khách hàng ( tức là đại diện của những người dùng ) với các nhà thiết kế. Phác hoạ mô hình dữ liệu là một quá trình thử nghiệm, thường phải sửa đi sửa lại nhiều lần. Một mô hình dữ liệu sẽ thay đổi khi bạn hiểu biết nhiều hơn về nhu cầu và lĩnh vực hoạt động của khách hàng. Sản phẩm cuối cùng rất có thể sẽ khác xa với những phiên bản ban đầu của bạn. Chỉ nên dùng bút chì và chuẩn bị sẵn một cục tẩy to. Tốt hơn hết là bạn dùng một phần mềm máy tính để việc vẽ và sửa mô hình dữ liệu được dễ dàng hơn. 3.2.2. Chất lượng của mô hình dữ liệu Khi đánh giá chất lượng của một mô hình dữ liệu. Trước hết ta phải tìm hiểu cặn kẽ bối cảnh của môi trường mà trong đó mô hình được sử dụng, sau đó dựa vào các tính chất của một mô hình dữ liệu hoàn hảo và coi đó là những tiêu chuẩn để đánh giá. Một mô hình dữ liệu hoàn hảo có các tính chất sau đây: - Tuân thủ mọi quy tắc và quy định về xây dựng mô hình dữ liệu đã được khách hàng chấp nhận. - Không có sự nhập nhằng dẫn đến sự hiểu lầm theo nhiều nghĩa khác nhau. ( Chẳng hạn nên dùng cụm từ “Số hiệu hoá đơn “ thay cho cụm từ “ Số hoá đơn “ để tránh hiểu lầm là số lượng hoá đơn. - Các tên thực thể trong một mô hình và các tên thuộc tính của một thực thể không trùng nhau. Các tên đều có nghĩa và gần sát với những tên mà khách hàng thường dùng hàng ngày. - Mỗi thực thể đều có ít nhất một yếu tố phân biệt để tránh tình trạng người dùng tra cứu nhầm cá thể này với cá thể khác. - Tất cả các mối quan hệ đều được ghi nhận bằng các ký hiệu chuẩn xác. Nếu cần thiết thì viết thêm lời mô tả quan hệ để tránh hiểu lầm. - Tất cả các thực thể và thuộc tính của thực thể mà khách hàng quan tâm đều được liệt kê trong mô hình. - Có độ trung thực cao. Những người dùng dữ liệu muốn có một hình ảnh dữ liệu với độ trung thực cao, giống như người say mê âm nhạc đều muốn có một bộ dàn có khả năng tái tạo các bản nhạc gần như bản gốc, ít bị bóp méo. Một mô hình dữ liệu là một hình ảnh với độ trung thực cao khi nó mô tả trung thành thế giới thực tại và cung cấp được thông tin chính xác cho người dùng. Độ trung thực của một mô hình dữ liệu phụ thuộc phần lớn vào sự phản ánh đầy đủ và chính xác các mối quan hệ giữa các thực thể. Chẳng hạn, nếu một mối quan hệ thực tế là m:m thì nó phải được ghi nhận như vậy trong mô hình. - Một mô hình dữ liệu hoàn hảo sẽ dễ hiểu, dễ dùng đồng thời phản ánh được đầy đủ và chính xác thực tế theo đúng yêu cầu của người dùng. 3.2.3 Nâng cao chất lượng của mô hình dữ liệu Để có được một mô hình dữ liệu hoàn hảo thì phải tốn thời gian. Khách hàng phải giải thích vấn đề một cách đầy đủ sao cho người thiết kế có thể hiểu rõ mọi yêu cầu của khách hàng nhằm thiết kế được một mô hình đáp ứng được các yêu cầu đó. Mô hình hoá dữ liệu giống như một quá trình thử nghiệm và cải biến dần: Cán bộ thiết kế dần dần tạo ra một mô hình của cơ sở dữ liệu nhờ sự tiếp xúc và trao đổi thường xuyên với khách hàng. Đôi khi mô hình dữ liệu bộc lộ những thiếu sót nghiêm trọng khiến nhà thiết kế phải thay đổi hàng loạt. Sau đây là một số gợi ý để nâng cao chất lượng của mô hình dữ liệu và giảm bớt sự sửa đổi không đáng phải tiến hành. 3.2.3.1 Mở rộng hay thu hẹp mô hình dữ liệu Mô hình dữ liệu có thể mở rộng và cũng có thể thu hẹp. Mô hình dữ liệu sẽ được mở rộng khi ta tăng phạm vi ứng dụng, bổ sung thực thể, thuộc tính và các mối quan hệ để bao trùm cả các 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 11 trường hợp ngoại lệ. Nói chung, các mô hình dữ liệu thường lớn dần lên, có khi gấp hai ba lần quy mô ban đầu bởi vì nó cần phản ánh độ phức tạp của thực tế mà người thiết kế không dễ gì nhận thức được đầy đủ ngay từ đầu. Đừng cố ý hạn chế sự tăng trưởng của mô hình mà hãy để cho nó lớn lên đến mức cần thiết. Một mô hình dữ liệu có thể thu hẹp lại khi ta tổng quả hoá các thực thể của nó. Mô hình cũng được thu hẹp khi ta có những cải tiến để biểu diễn các mối quan hệ một cách đúng đắn và ít vụng về hơn. Sau đây là một vài ví dụ vế sự cải biến mô hình dữ liệu: Ví dụ 1: Xét trường hợp một Công ty có những phòng ban và những tổ đội. Một phòng ban có nhiều tổ đội, nhưng mỗi tổ đội chỉ thuộc về một phòng ban. Thoạt đầu giả sử Công ty chỉ cho phép mỗi cán bộ công nhân viên làm việc cho một tổ đội duy nhất. Mô hình dữ liệu phản ánh cơ cấu tổ chức theo công việc của Công ty. Trong suốt thời kỳ làm việc cho Công ty, một CBCNV có thể thuyên chuyển từ bộ phận này sang bộ phận khác, thậm chí Công ty có thể thay đổi chủ trương cho phép một người đồng thời kiêm nhiệm ở nhiều bộ phận. Muốn ghi nhận cả lịch sử tức là việc làm trong quá khứ, và thể hiện được chủ trương mới của Công ty thì cần mở rộng mô hình. Vì một tổ có nhiều CBCNV và một CBCNV có thể làm cho nhiều tổ nên ta có mối quan hệ m:m giữa tổ và CBCNV. Cần có một thực thể giao để biểu diễn mối quan hệ này, ta gọi nó là thực thể CHức vụ. Mỗi hiện diện của thực thể, tức là mỗi chức vụ mà một CBCNV nào đó đã đảm nhiệm được xác định duy nhất bởi ngày bắt đầu, kết hợp với mã tổ và mã CBCNV. Bây giờ ta lại muốn theo dõi các khoản tiền công đã trả cho CBCNV. Một người có thể có nhiều phiếu trả lương nhưng mỗi phiếu chỉ thuộc về một người. Một lần nữa mô hình lại được mở rộng. Phòng Mã phòng Tên phòng Tổ Mã tổ Tên tổ Chức vụ Ngày bắt đầu Mã tổ Mã CBCNV CBCNV Mã CB Tên CB Phòng Mã phòng Tên phòng Tổ Mã tổ Tên tổ Chức vụ Ngày bắt đầu Mã tổ Mã CBCNV CBCNV Mã CB Tên CB Phiếu lương Ngày trả 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 12 Ví dụ 2: Một hãng môi giới có thể đứng ra chịu trách nhiệm ký hợp đồng cho một hãng hàng không thuê một chiếc máy bay. Khi hợp đồng thuê một máy bay hết hạn thì hãng mối giới lại cho hãng hàng không khác thuê chiếc máy bay ấy. Trong ngành kinh doanh này ta thấy có ba mối quan hệ nhiều nhiều: - Một máy bay có thể được thuê nhiều lần bởi nhiều hãng hàng không và một hãng hàng không có thể thuê nhiều máy bay. - Một hãng môi giới có thể thuê nhiều máy bay và một máy bay có thể được nhiều hãng môi giới thuê. - Một hãng hàng không có thể ký hợp đồng thuê với nhiều hãng môi giới và ngược lại. Vấn đề vướng mắc khi tạo ra ba thực thể giao tách biệt như trên là không biết nên lưu trữ hợp đồng thuê máy bay ở đâu. Sau khi xem xét ta thấy nên dùng chính hợp đồng thuê máy bay là thực thể giao giữa ba thực thể. Hãng MG * Mã hãng HK_MG MB_MGMáy bay * Mã máy bay MB_HK Hàng HK * Mã hãng không Máy bay * Mã máy bay Hợp đồng * Ngày ký Hãng HK * Mã hãng HK Hãng MG * Mã hãng MG 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 13 3.2.3.2 Yếu tố phân biệt – Thực thể độc lập hay phụ thuộc Nếu không có một yếu tố phân biệt rõ ràng và đơn giản nào thì hãy sáng tạo ra một yếu tố hay nhờ hệ quản trị CSDL tạo ra. Đơn giản nhất là dùng một mã ngắn gọn làm yếu tố phân biệt. Nếu ta tự tạo ra yếu tố phân biệt thì cần đảm bảo nó không có giá trị trùng lặp đối với các cá thể như nhau. Đừng nên chất đầy thông tin vào một yếu tố phân biệt làm cho nó quá tải. Có Công ty chất dẻo đã từng đặt ra mã sản phẩm dài tới 22 ký tự để không những xác định sản phẩm một cách duy nhất mà còn chỉ ra mầu sắc, loại nguyên liệu tạo ra sản phẩm. Mã quá dài sẽ gây ra nạp dữ liệu sai và ít người có thể nhớ được cách giải mã để biết ý nghĩa đầy đủ của nó. Thực thể độc lập hay phục thuộc: Một thực thể phải dựa vào một thực thể khác để tồn tại hay để được xác định một cách duy nhất thì gọi là thực thể phụ thuộc. Tuy nhiên khái niệm độc lập hay phụ thuộc chỉ có tính chất tương đối. 3.2.3.3. Vị trí và trật tự Trong mô hình dữ liệu không có quy định gì về vị trí và trật tự xuất hiện của các thực thể. Nhưng, thực thể nào có nhiều quan hệ với các thực thể khác thì nên đứng giữa để dễ vẽ. Các thuộc tính của một thực thể cũng có thể được liệt kê theo thứ tự tuỳ ý. Tuy nhiên nên sắp xếp liên tiếp các thuộc tính có liên quan với nhau. Chẳng hạn, họ đệm nên đứng ngay trước tên. Các dòng trong bảng cũng không cần được nạp vào theo thứ tự nào cả. 3.2.3.4 Thực thể chỉ có một cá thể Đừng dè dặt khi phải tạo ra một thực thể chỉ có một cá thể. Xét trường hợp mô hình dữ liệu dùng để quản lý dữ liệu tiêu thụ điện năng. ở Việt Nam chỉ có một cơ quan độc quyền cung cấp điện đó là “ Sở điện lực “ Vì vậy thực thể công ty chỉ là một cá thể. 3.2.3.5. Chú ý tới các trường hợp ngoại lệ Luôn thăm dò và phát hiện những trường hợp ngoại lệ để thiết kế lại mô hình nhằm xử lý được cả trường hợp đó. Mô hình càng bao trùm được nhiều trường hợp ngoại lệ thì càng có độ trung thực cao. Để phát hiện những trường hợp ngoại lệ thì phải luôn đặt câu hỏi: - Có phải lúc nào cũng như vậy không? - Liệu có tình huống nào khiến cho mối quan hệ này trở thành nhiều – nhiều hay không? - Trong tương lai vẫn giữ nguyên quy định ấy chứ? 3.2.3.6 Tham khảo các mô hình dữ liệu đã từng được sử dụng Một mô hình đã từng được sử dụng chắc chắn có độ trung thực khá cao vì nó trải qua nhiều lần sàng lọc và sửa đổi. Việc thừa kế có chọn lọc mô hình cũ tốt hơn so với việc thiết kế một mô hình mới hoàn toàn. 3.2.4 Chuẩn hoá 3.2.4.1 Chuẩn hoá là gì? 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 14 Chuẩn hoá là quá trình cải tiến một bản thiết kế CSDL tối sao cho nó khắc phục được những điểm bất thường khi đổi mới dữ liệu và tránh được hiện tượng không nhất quán về dữ liệu. Nếu mô hình hoá dữ liệu một cách hoàn toàn đúng đắn và đã dùng mô hình để thiết kế CSDL với độ trung thực cao thì chẳng cần chuẩn hoá nữa. Tuy nhiên, đôi khi các nhà phân tích hệ thống vẫn lợi dụng các hệ thống tệp dữ liệu cũ của cơ quan rồi tiến hành chuẩn hoá tạo ra CSDL mới. Trong quá trình chuẩn hoá ta áp dụng các quy tắc để dần dần chuyển đổi hệ thống tệp cu sang dạng chuẩn thứ nhất, thứ hai 3.2.4.2. Sự phụ thuộc hàm Sự phụ thuộc hàm là một mối quan hệ giữa các thuộc tính trong thực thể. Nó đơn thuần có nghĩa là một hay một số thuộc tính xác định giá trị của thực thể khác. Chẳng hạn, cho biết mã của một cổ phiếu thì ta có thể biết tên Công ty phát hành ra cổ phiếu ấy, giá cổ phiếu, số lượng, phần trăm lãi suất. Như vậy, tên Công ty, giá cổ phiếu, số lượng, phần trăm lãi suất phụ thuộc vào mã cổ phiếu như một hàm. 3.2.4.3. Dạng chuẩn thứ nhất Một bảng thuộc dạng chuẩn thứ nhất ( 1 NF – First Normal Form ) khi và chỉ khi tất cả các cột của bảng đều chứa những giá trị nguyên tố, nghĩa là tất cả các dòng của bảng đều có cùng số cột và tại mỗi cột trên mỗi dòng chỉ có một giá trị duy nhất. Ví dụ: giả sử hoá đơn ghi nhiều món hàng đã bán. Nếu ta gộp dữ liệu về tất cả các món hàng vào bảng hoá đơn thì bởi vì mỗi hoá đơn chỉ được dành một dòng trong bảng nên sẽ có hai khả năng khiến cho bảng không thuộc dạng chuẩn thứ nhất. - Có một số cột phải chứa nhiều giá trị tương ứng với nhiều món hàng. - Nếu tách các cột sao cho mỗi cột có cùng giá trị thì số cột trên các dòng sẽ không bằng nhau bởi vì các hoá đơn không ghi cùng một số món hàng như nhau. 3.2.4.4. Dạng chuẩn thứ hai Một bảng thuộc dạng chuẩn thứ hai ( 2NF) khi và chỉ khi nó thuộc dạng chuẩn thứ nhất và tất cả các cột không thuộc khoá chính đều phụ thuộc đầy đủ vào khoá chính. Nói cách khác, một bảng không thuộc dạng chuẩn thứ hai khi có một cột không phụ thuộc vào khoá chính mà chỉ phụ thuộc vào phần khoá chính. Ví dụ: Xét một CSDL chỉ có một bảng ghi tất cả các khoản mục hàng hoá do khách hàng đặt trước: HANG_DAT MA_HANG MA_KH SO_LUONG TIN_DUNG 12 57 25 Bình thường Tên bảng “ Hàng đặt” . Các tiêu đề cột tương ứng mã hàng, mã khách hàng, số lượng, tín dụng. Khoá chính của bảng là tổ hợp của mã hàng và mã khách hàng. Vấn đề khiến bảng này không thuộc dạng chuẩn thứ hai là: tình trạng tín dụng của khách hàng chỉ phụ thuộc vào mã khách mà không phụ thuộc vào mã hàng, có nghĩa là nó chỉ phụ thuộc một phần vào khoá chính. Phân tích ta nhận thấy rằng một mặt hàng có thể được nhiều khách hàng đặt và một khách hàng có thể mua nhiều mặt hàng, đây là mối quan hệ m:m vì vậy khi xây dựng mô hình ta phải xây dựng thực thể giao giữa hai thực thể trên. Mô hình dữ liệu cần có ba thực thể: MAT_HANG, HANG_DAT, KHACH_HANG. MAT_HANG * Mã hàng Tên hàng HANG_DAT Số lượng KHACH_HANG * Mã khách Tên khách Tín dụng 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 15 3.2.4.5. Dạng chuẩn thứ ba Một bảng thuộc dạng chuẩn thứ ba ( 3NF) khi và chỉ khi nó thuộc dạng chuẩn thứ hai và không có sự phụ thuộc bắc cầu. Nói cách khác, một bảng không thuộc dạng chuẩn thứ ba khi có một cột không thuộc khoá chính lại phụ thuộc vào một cột khác không thuộc khoá chính. Ví dụ: Xét bảng sau CO_PHIEU MA_CO_PH TEN_NUOC HOI_DOAI Trong bảng này có MA_CO_PHIEU là khoá chính xác định duy nhất tên nước có công ty bán cổ phiếu còn tên nước xác định duy nhất giá hối đoái của nước. Như vậy đã có sự phụ thuộc mang tính chất bắc cầu: Hối đoái phụ thuộc vào tên nước, tên nước phụ thuộc vào mã cổ phiếu. Ta thấy rằng một nước có thể có nhiều cổ phiếu, nhưng mỗi cổ phiếu chỉ thuộc về một nước. Như vậy, muốn có dạng chuẩn thứ ba ta phải xây dựng mô hình quan hệ thực thể gồm hai bảng: NUOC, CO_PHIEU. Ngoài ba dạng chuẩn thông thường vừa nêu trên, còn có các dạng chuẩn khác nữa với những đòi hỏi khắt khe và tinh vi hơn, đó là dạng chuẩn thứ 4 và thứ 5. Tuy nhiên, vì quá khắt khe và tinh vi nên các dạng chuẩn ấy đã vượt quá phạm vi xem xét của các HQTCSDL hiện nay. NUOC * Mã nước Tên nước Hối đoái CO_PHIEU * Mã cổ phiếu Tên Công ty Giá cổ phiếu SL Phần trăm lãi 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 16 CHƯƠNG II - MICROSOFT EXCEL 1. Giới thiệu Microsoft Excel (sau đây gọi là Excel) là một phần mềm bảng tính điện tử nằm trong gói phần mềm MS Office của Microsoft. Excel chạy trên môi trường Windows và được dùng phổ biến trong công tác văn phòng, trong quản lí bởi tính đơn giản, trực quan và dễ sử dụng của nó. Excel được ra đời và phát triển từ cuối năm 1980 cho đến nay qua các phiên bản khác nhau, đối với Việt Nam hiện nay thì phiên bản phổ biến nhất vẫn là Excel 2003 nằm trong gói phần mềm Microsoft Office 2003. Các chức năng cơ bản của Excel có thể tóm tắt như sau: Tổ chức dữ liệu ở dạng bảng tính cho phép tạo, hiệu chỉnh, định dạng in và lưu giữ bảng tính.. Sắp xếp và phân nhóm dữ liệu: Tính năng này cho phép sắp xếp bảng tính chứa dữ liệu theo nhiều tiêu thức khác nhau với trình tự ưu tiên định trước. Chức năng này cũng cho phép tạo nhóm và tiến hành tính toán, tổng hợp theo nhóm. Đặt lọc và kết xuất dữ liệu: Tính năng này cho phép tìm kiếm và kết xuất thông tin theo nhiều tiêu chuẩn khác nhau. Biểu diễn dữ liệu dưới dạng biểu đồ với nhiều kiểu biểu đồ khác nhau từ hai chiều đến ba chiều. Tính năng này làm tăng tính trực quan đối với dữ liệu. Tính toán sử dụng hàm: Excel cung cấp nhiều hàm mẫu thuộc các phạm trù khác nhau như thống kê, ngày tháng, toán học, cơ sở dữ liệu.. Phân tích dữ liệu và tiến hành dự báo: Trong Excel có nhiều công cụ phân tích cho phép người dùng tiến hành các phân tích thống kê hàm lượng hóa các xu thế, các quan hệ giữa các yếu tố kinh tế và trên cơ sở đó tiến hành dự báo. Quản trị cơ sở dữ liệu: Excel cho phép xây dựng cơ sở dữ liệu đơn giản và tiến hành cập nhật, truy xuất thông tin từ cơ sở dữ liệu. Tự động thực hiện bằng công cụ Macro: chức năng này cho phép tạo một macro chứa các thao tác hoặc các công thức. Người sử dụng sau đó có thể gọi lại macro này để tự động thực hiện công việc. 1.1. Khởi động và thoát khỏi Excel a) Khởi động Excel Có rất nhiều cách có thể khởi động được phần mềm Excel. Tuỳ vào mục đích làm việc, sở thích hoặc sự tiện dụng mà người ta có thể chọn một trong các cách sau để khởi động: C1: Vào menu Start | Programs | Microsoft Office 2003 | Microsoft Office Excel 2003. C2: Nháy kép chuột lên biểu tượng của phần mềm Excel trên màn hình nền Desktop Sau khi Excel khởi động xong, chương trình sẽ xuất hiện một màn hình làm việc riêng với một bảng tính trống để cho ta làm việc. 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 17 Excel cho phép mở nhiều bảng tính cùng một lúc, mỗi bảng tính được hiển thị trên một cửa sổ riêng biệt. Cũng giống như các ứng dụng chạy trên Windows, Excel cũng có các nút chức năng để cho phép bạn điều chỉnh độ rộng cửa sổ hiển thị.  Khi tối thiểu hóa các bảng tính bằng nút Minimize , các bảng tính sẽ được thu gọn trên thanh taskbar.  Hiển thị cửa sổ làm việc ở dạng trung bình bằng nút Restore\ Maximize  Đóng cửa sổ làm việc của Excel bằng nút Close . b) Thoát khỏi Excel. Sau khi đã hoàn thành công việc hoặc không muốn làm việc với Excel nữa, để thoát khỏi Excel dùng một trong các cách sau: C1: Bấm vào nút Close ở góc trên bên phải của cửa sổ Excel. C2: Vào menu File | Exit. C3: Dùng tổ hợp phím tắt Alt + F4 (Nhấn phím Alt và gõ phím F4). C4: Dùng tổ hợp phím Ctrl + W C5: Kích phải chuột lên vị trí chương trình Excel nằm trên thanh tác vụ (taskbar) và chọn Close. 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 18 C6: Nháy kép chuột lên biểu tượng Excel ở góc trên bên trái của thanh tiêu đề. 1.2. Một số khái niệm cơ bản File excel là file có phần mở rộng là .xls (vd: bang_luong.xls). Workbook (Book): Một workbook là một tập tin mà trên đó có chứa các phép toán, vẽ đồ thị, và lưu trữ dữ liệu Vì mỗi workbook có thể chứa nhiều sheet (bảng tính), do vậy người dùng có thể tổ chức, lưu trữ nhiều loại thông tin có liên quan với nhau chỉ trong một tập tin (file). Một workbook chứa tối đa 255 worksheet hay chart sheet. Worksheet (Sheet): Là nơi lưu trữ và làm việc với dữ liệu, nó còn được gọi là bảng tính. Một worksheet chứa nhiều ô (cell), các ô được tổ chức thành các cột và các dòng. Worksheet được chứa trong workbook. Một Worksheet chứa được 256 cột đánh số từ A đến IV và 65536 dòng đánh số từ 1 đến 65535. Mỗi Worksheet có một tên riêng, tên mặc định là Sheet1, Sheet2,, ta có thể đặt lại tên cho từng Worksheet. Chart sheet: Là một sheet trong workbook, nó chỉ chứa một đồ thị. Một chart sheet rất hữu ích khi bạn muốn xem riêng lẻ từng đồ thị. Cell: Ứng với một cột và một hàng được gọi là một Cell hay còn gọi là một ô. Mỗi Cell đều có toạ độ (địa chỉ) tương ứng bằng tên cột ghép với tên hàng (VD: A1–hàng 1 cột A) Địa chỉ ô tương đối: Toạ độ ô ghi trong công thức sẽ được thay đổi mỗi khi sao chép đến vị trí mới. Ví dụ: Công thức trong ô C3 là =A3+B3 Khi sao chép đến ô C4 là = A4+B4 Địa chỉ tuyệt đối: Tọa độ ô ghi trong công thức được cố định không thay đổi khi sao chép đến vị trí mới. Ký tự biểu diễn địa chỉ tuyệt đối là dấu đô la ($). Để khẳng định địa chỉ tuyệt đối của 1 ô ta chọn ô đó rồi bấm phím F4 trên bàn phím. VD: Địa chỉ tuyệt đối của ô A1 => $A$1 (tuyệt đối hàng và tuyệt đối cột) C1=$A$1+$B$1 Khi sao chép sang ô D1 =$A$1+$B$1 Khi sao chép sang ô E1 =$A$1+$B$1 Địa chỉ hỗn hợp: Là dạng kết hợp giữa địa chỉ tương đối và địa chỉ tuyệt đối, địa chỉ tuyệt đối sẽ không đổi khi copy còn địa chỉ tương đối sẽ thay đổi khi copy.  Tương đối cột, tuyệt đối dòng: VD ô A1 => A$1 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 19  Tuyệt đối cột, tương đối dòng: VD ô A1 => $A1 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 20 1.3. Màn hình làm việc của Excel. a) Thanh tiêu đề Dòng trên cùng của cửa sổ là thanh tiêu đề, chức năng của nó là hiển thị tên của bảng tính đang soạn thảo và tên của ứng dụng. Trên thanh tiêu đề có 3 nút tính từ bên phải sang là nút thoát, nút phóng to, nút thu nhỏ. b) Hệ thống Menu chính  File: Các chức năng liên quan đến file bảng tính  Edit: Các chức năng chỉnh sửa bảng tính  View: Các chức năng về hiển thị  Insert: Các chức năng chèn các đối tượng như chèn hàng, cột, công thức.  Format: Các chức năng về định dạng bảng tính.  Tools: Các chức năng về công cụ cấu hình cho ứng dụng Excel.  window: Các chức năng về cửa sổ như sắp xếp và chuyển đổi giữa các cửa sổ 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 21  Help: Các chức năng trợ giúp. c) Các thanh công cụ. Các thanh công cụ thường dùng là: Standard, Formatting, Drawing, Formula bar. *) Thanh Standard - Mở một bảng tính mới - Mở file bảng tính đã có trên đĩa - Cất giữ bảng tính đang soạn vào đĩa - In toàn bộ bảng tính ra máy in - Xem bảng tính trước khi in - Kiểm tra chính tả đối với bảng tính tiếng Anh - Cắt bỏ vùng bảng tính đang đánh dấu và lưu nội dung đó vào Clipboard - Sao chép vùng bảng tính đang đánh dấu vào Clipboard - Dán bảng tính vừa copy hoặc cut vào vị trí ô đang chứa con trỏ. - Chổi định dạng ký tự - Xoá bỏ một động tác soạn thảo hoặc định dạng trước đó - Nhắc lại một động tác soạn thảo hoặc định dạng trước đó - Tính tổng các ô có kiểu dữ liệu là số. - Sắp xếp dữ liệu trong bảng tính theo chiều tăng dần hoặc giảm dần. - Chèn biểu đồ - Khởi động chương trình vẽ Microsoft Draw - Thay đổi tỉ lệ hiển thị *) Thanh Formatting - Hộp chọn phông chữ - Font - Hộp chọn cỡ chữ - Size - Tắt bật in đậm - Bold - Tắt bật in nghiêng - Italic - Tắt bật in có gạch chân - Underline - Căn lề bảng tính theo lề trái - Căn lề bảng tính theo giữa 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 22 - Căn lề bảng tính theo lề phải - Căn lề bảng tính theo 2 lề phải, trái - Hoà trộn các ô (cell) - Định dạng kiểu tiền tệ cho ô - Định dạng kiểu dữ liệu dạng phần trăm - Định dạng dữ liệu kiểu số có dấu phẩy ngăn cách phần nghìn, triệu - Tăng giảm số các số 0 sau dấu phân cách phần thập phân. - Di chuyển sang trái 1 Tab vùng bảng tính đã chọn - Di chuyển sang phải 1 Tab vùng bảng tính đã chọn - Tạo viền cho bảng tính *) Thanh công thức (Formula bar) Thanh công thức chứa địa chỉ của ô hiện hành và nội dung dữ liệu chứa trong ô đó. Để lấy thanh công thức vào menu View | Formula bar d) Cách lấy các thanh công cụ trong Excel Trên màn hình giao diện của Excel, người ta thường để thường trực một số thanh công cụ hay sử dụng đến, còn các thanh công cụ khác không dùng đến hoặc rất ít khi dùng đến thì không cho hiển thị ra, khi nào cần dùng đến mới lấy ra, cách lấy các thanh công cụ như sau: Vào menu View | Toolbars, chọn các thanh công cụ muốn sử dụng (các thanh công cụ thường hay dùng là: Standard, Formatting, Drawing). 1.4. Làm việc với Worksheet. a) Thêm một Worksheet mới Dùng một trong các cách sau để chèn thêm một Sheet mới C1: Vào menu Insert | Worksheet. 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 23 C2: Kích phải chuột lên một sheet bất kỳ nào đó trên bảng tính chọn Insert, hộp thoại Insert hiện ra, chọn Worksheet và bấm nút OK để chèn. b) Đổi tên một Sheet Các bước đổi tên một sheet bao gồm: B1: Chọn Sheet muốn đổi tên. B2: Dùng một trong các cách: C2: Vào menu Format | Sheet | Rename, sau đó nhập tên cho Sheet C1: Kích phải chuột lên Sheet đã chọn, chọn Rename, sau đó nhập tên cho Sheet. B3: Nhập tên xong gõ Enter để kết thúc. c) Sắp xếp vị trí các sheet. C1: Nhấp chuột vào tên sheet muốn di chuyển kéo và thả vào vị trí mong muốn. C2: Kích phải chuột lên sheet muốn di chuyển chọn Move or Copy, hộp thoại Move or Copy hiện ra. To book: Vị trí Workbook muốn sao chép tới hoặc di chuyển tới Before sheet: Vị trí đích muốn sheet chuyển tới. d) Xoá bỏ một Sheet Dùng một trong các cách sau để xoá một Sheet C1: Chọn Sheet muốn xoá, vào menu Edit | Delete sheet 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 24 C2: Kích phải chuột vào sheet muốn xoá chọn Delete Lưu ý: Để xoá bỏ nhiều Sheet không liên tục cùng một lúc, kết hợp chuột và giữ phím Ctrl để chọn, sau đó thực hiện các thao tác để xoá. Để xoá các Sheet liên tục, kết hợp dùng phím Shift để chọn. e) Di chuyển qua lại giữa các Sheet Để di chuyển qua lại giữa các Sheet ta dùng tổ hợp phím Ctr + Page Up hoặc Ctrl + Page Down f) Sao chép (copy) một Sheet Giữ phím Ctrl và dùng chuột để kéo thả Sheet muốn copy sang một Sheet mới (nếu không giữ phím Ctrl thì thao tác này là thao tác sắp xếp vị trí các Sheet) 1.5. Nhập và sửa dữ liệu Một ô trong Excel có thể chứa dữ liệu thô được gõ trực tiếp vào từ bàn phím hoặc một công thức tính toán hay một tổ hợp các hàm. Sau đây là cách nhập dữ liệu thô vào một ô. a) Nhập dữ liệu B1: Chọn ô muốn nhập dữ liệu. B2: Gõ trực tiếp từ bàn phím (có thể là số, chữ hoặc ký tự). B3: Gõ phím Enter để kết thúc việc nhập dữ liệu. (có thể dùng phím mũi tên di chuyển chuột để kết thúc nhập) b) Sửa đổi dữ liệu C1: Nháy kép chuột lên ô cần sửa. C2: Chọn ô cần sửa đổi dữ liệu và nhấn phím F2 trên bàn phím. 1.6. Sao chép dữ liệu cho dãy các ô liên tục Để sao chép dữ liệu cho một dãy các ô liên tục, các bước thực hiện: B1: Chọn 1 ô hoặc 2 ô tuỳ kiểu copy được minh hoạ như hình dưới. B2: Đưa con trỏ chuột vào vị trí góc dưới bên phải cho đến khi con trỏ chuột biến thành hình dấu cộng (+) màu đen đậm. Nhấp trái chuột và kéo về phía muốn sao chép dữ liệu đến rồi nhả chuột. 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 25 1.7. Tách bảng tính Excel cho phép tách bảng tính ra thành 2 vùng hoặc 4 vùng để thuận tiện cho mục đích công việc. Các cách thực hiện như sau: C1: Đưa con trỏ chuột vào vị trí cần tách, vào menu Window | Split. Để huỷ bỏ chế độ này, vào menu Window | Remove Split C2: Vào menu Window | Freeze Panes, toàn bộ vùng bảng tính phía trên con trỏ chuột sẽ được giữ nguyên, vùng phía dưới con trỏ có thể dùng thanh trượt (scroll bar) để xem. Để huỷ bỏ chế độ này, vào menu Window | UnFreeze Panes. C3: Trỏ chuột vào thanh tách cho xuất hiện mũi tên 2 chiều, kéo thả nó tại vị trí cần tách. 1.8. Ẩn hiện bảng tính a) Ẩn hiện Workbook Để ẩn một Workbook, vào menu Window | Hide Để hiện một Workbook, ta làm ngược lại, vào menu Window | Unhide b) Ẩn hiện WorkSheet Để ẩn một WorkSheet, vào menu Format | Sheet | Hide Để hiện một WorkSheet, ta làm ngược lại, vào menu Format | Sheet | UnHide c) Ẩn hiện các dòng (rows) Các bước để ẩn các dòng bao gồm 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 26 B1: Lựa chọn (bôi đen) các dòng muốn ẩn. B2: Vào menu Format | Row | Hide Để hiện các dòng, ta làm ngược lại B1: Lựa chọn vùng bao phủ các dòng bị ẩn. B2: Vào menu Format | Row | UnHide d) Ẩn hiện các cột (column) * Các bước để ẩn các cột bao gồm B1: Lựa chọn (bôi đen) các cột muốn ẩn. B2: Vào menu Format | column | Hide * Để hiện các cột, ta làm ngược lại B1: Lựa chọn vùng bao phủ các cột bị ẩn. B2: Vào menu Format | column | UnHide 1.9. Tạo bảng tính mới Đ ể tạo m ộ t tà i liệu m ớ i, sử dụng m ộ t tron g các cách sau : C 1 : V à o M enu File c họn N ew . C 2 : N hấn nú t N ew trên than h côn g cụ Standard . C 3 : N hấn tổ hợp ph ím tắ t C trl + N 1.10. Lưu file bảng tính lên đĩa Tập tin (file) Excel được lưu lên đĩa với phần mở rộng là .XLS. Thông thường thì các tệp Excel được đề nghị cất vào thư mục C:\My Documents trên đĩa cứng. Tuy nhiên, bạn có thể thay đổi vị trí cất giữ tuỳ theo ý muốn của mình. Đề lưu một bảng tính lên đĩa, thực hiện một trong các cách sau: C1: Vào Menu File | Save as C2: N hấn nú t Save trên than h côn g cụ Standard; C 3 : N hấn tổ hợp ph ím tắ t C trl + S Hộp thoại Save as xuất hiện, cần thực hiện các thao tác sau:  Chọn nơi cần lưu ở ô Save in  Đặt tên cho bảng tính cần lưu ở ô File name  Bấm nút Save để lưu. 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 27 1.11. Lưu các thay đổi trên một file đã tồn tại Một file đã tồn tại trên đĩa là một file đã có tên và vị trí lưu xác định, vì vậy với các lần lưu sau, chỉ cần phải lưu các thay đổi về nội dung đã chỉnh sửa mà không cần đặt lại tên và xác định vị trí lưu nữa. Để lưu các thay đổi, dùng một trong các cách sau: C1: Vào Menu File | Save. C2: N hấn nú t Save trên than h côn g cụ Standard; C 3 : N hấn tổ hợp ph ím tắ t C trl + S 1.12. Lưu file với tên khác Vào Menu File | Save as hộp thoại Save as xuất hiện, nhập tên file mới vào ô File name. Lưu ý: Trong bước này, cũng có thể chọn lại vị trí lưu file bảng tính. 1.13. Lưu file vào đĩa mềm B1:Đưa đĩa mềm vào ổ đĩa A:\ B2: Vào menu File | Save As, hộp thoại Save as xuất hiện Tại mục Save in chọn ổ đĩa A:\ (Floppy A) Gõ tên file ở mục file name B3: Chọn nút Save để lưu. 1.14. Mở một tập tin đã tồn tại trên đĩa. Đ ể m ở m ộ t tà i liệu E x ce l đã có trê n đ ĩa , c họn m ộ t tron g các cách sau : C1: Vào Menu File | Open C2: Nhấp chuột vào biểu tượng Open trên thanh Standard C3: Nhấn tổ hợp phím tắt Ctrl + O Hộp thoại Open hiện ra, ta thực hiện các bước sau để mở một file Excel đã có trên đĩa  Chọn nơi chứa văn bản cần mở trong ô Look in 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 28  Chọn file cần mở trong khung hiển thị  Nhấn nút Open hoặc nháy kép chuột vào file Excel cần mở để mở. 2. Làm việc với bảng tính Excel 2.1. Xử lý ô, cột, dòng Để chọn một dòng trong bảng tính, bấm chuột trái vào chữ số ký hiệu dòng đó. Để chọn một cột, bấm chuột trái vào chữ cái ký hiệu cột đó. Để chọn một vùng gồm nhiều ô liên tục, bấm giữ chuột trái và kéo. Cũng có thể sử dụng phím shift kết hợp với phím mũi tên hoặc phím shift kết hợp với bấm chuột trái để chọn một vùng. Để chọn nhiều vùng không liền nhau, nhấn giữ phím Ctrl trong khi bấm chuột trái. Để chọn cả bảng tính, bấm chuột trái vào vị trí góc trên cùng bên trái của bảng tính, nơi giao nhau của tên hàng và tên cột hoặc dùng tổ hợp phím Ctrl + A. Để điều chỉnh độ rộng của dòng bấm giữ và kéo chuột trên đường phân cách giữa hai vị trí tiêu đề tên cột. Để thay đổi độ rộng của dòng, bấm giữ và kéo chuột trên đường phân cách giữa hai dòng tại vị trí tiêu đề dòng. Để thay đổi độ rộng của nhiều cột, chọn vùng có các cột cần thay đổi độ rộng, vào menu Format | Column | Width, hộp thoại column width xuất hiện. Gõ vào độ rộng cột cần thay đổi rồi chọn OK hay nhấn Enter. Làm tương tự cho dòng (row). Lưu ý: Để bỏ chọn, bấm phím Esc (Escape) trên bàn phím. a. Chèn thêm dòng, cột, ô *) Chèn thêm dòng 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 29 B1: Lựa chọn vị trí dòng cần thực hiện chèn B2: Chọn một trong các cách C1: Vào menu Insert | Row C2: Kích phải chuột vào vị trí dòng cần chèn chọn Insert. *) Chèn thêm cột B1: Lựa chọn vị trí cột cần thực hiện chèn B2: Chọn một trong các cách C1: Vào menu Insert | Column C2: Kích phải chuột vào vị trí cột cần chèn chọn Insert. *) Chèn thêm ô B1: Lựa chọn vị trí ô cần thực hiện chèn B2: Chọn một trong các cách C1: Vào menu Insert | Cells C2: Kích phải chuột vào vị trí ô cần chèn chọn Insert, hộp thoại Insert hiện ra.  Shift Cells Right: Đẩy ô hiện hành sang phải khi chèn  Shift Cells Down: Đẩy khối ô hiện hành xuống dưới khi chèn  Entire Row: Chèn thêm các dòng trống phía trên phạm vi lựa chọn  Entire Column: Chèn các cột trống bên trái phạm vi lựa chọn b. Xoá dòng, cột, ô *) Xoá dòng, cột Chọn dòng hoặc cột muốn xoá rồi thực hiện một trong các cách C1: Vào menu Edit | Delete C2: Kích phải chuột vào vùng vừa lựa chọn rồi chọn Delete. *) Xoá ô Chọn ô hoặc tập hợp các ô cần xoá rồi thực hiện một trong các cách C1: Vào menu Edit | Delete C2: Kích phải chuột vào vùng vừa lựa chọn rồi chọn Delete, hộp thoại Delete xuất hiện  Shift Cells Left: Xoá ô hiện hành và chèn ô bên phải sang  Shift Cells Up: Xoá ô hiện hành và chèn ô dưới lên  Entire Row: Xoá dòng hiện hành và chèn dòng dưới lên  Entire Column: Xoá cột hiện hành và chèn cột bên phải sang c) Hoà trộn ô B1: Chọn các ô (vùng dữ liệu) muốn hoà trộn B2: Thực hiện một trong các cách: 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 30 C1: Bấm vào biểu tượng Merge and Center trên thanh công cụ định dạng (Formatting). C2: Vào menu Format | Cells, chọn tab Alignment, tích chọn Merge cells và chọn OK. C3: Kích phải chuột chọn Format Cells và làm tương tự cách 2. d) Huỷ bỏ hoà trộn ô Làm các thao tác ngược lại với hoà trộn ô B1: Chọn ô đã hoà trộn B2: Chọn một trong các cách C1: Bấm vào biểu tượngMerge and Center trên thanh công cụ định dạng (Formatting). C2: Vào menu Format | Cells, chọn tab Alignment, bỏ tích chọn Merge cells và chọn OK. C3: Kích phải chuột chọn Format Cells và làm tương tự cách 2. e) Chức năng Wrap text Chức năng này giúp trình bày dữ liệu trong ô khi độ rộng của cột nhỏ hơn độ rộng của dữ liệu. Các bước thực hiện B1: Chọn ô muốn định dạng B2: Thực hiện một trong các cách: C1: Vào menu Format | Cells, chọn tab Alignment, tích chọn Wrap text và chọn OK. C2: Kích phải chuột chọn Format Cells và làm tương tự cách 1. f) Tạo viền cho ô B1: Chọn các ô muốn tạo viền 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 31 B2: Dùng một trong các cách C1: Bấm chọn biểu tượng Outside Border trên thanh công cụ định dạng (Formatting) và chọn kiểu viền bao. C2: Vào menu Format | Cells (kích phải chuột chọn Format Cells), hộp thoại Format Cells xuất hiện, chọn tab Border. Border: Chọn vị trí đường viền Line: Kiểu đường viền Color: Chọn màu cho đường viền. g) Thiết lập font chữ, kiểu chữ, cỡ chữ B1: Chọn các ô muốn thiết lập font chữ B2: Dùng một trong các cách C1: Chọn font chữ, cỡ chữ, kiểu chữ C2: Vào menu Format | Cells (kích phải chuột chọn Format Cells), hộp thoại Format Cells xuất hiện, chọn tab Font. Font: Chọn font chữ Font style: Chọn kiểu chữ Size: Chọn cỡ chữ. 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 32 h) Chuyển dữ liệu hàng thành dữ liệu cột và ngược lại B1: Chọn (bôi đen) vùng dữ liệu muốn chuyển. B2: Copy vùng dữ liệu đó bằng cách C1: Bấm vào biểu tượng Copy trên thanh Standard C2: Vào menu Edit | Copy C3: Dùng tổ hợp phím tắt Ctrl + C (hoặc Ctrl + Insert) C4: Kích phải chuột chọn Copy B3: Chọn vị trí đích đặt dữ liệu sau khi chuyển B4: Vào menu Edit | Paste Special, hộp thoại Paste Special hiện ra, bấm chọn vào ô Transpose rồi chọn OK để hoàn thành. Lưu ý: Vùng dữ liệu hàng và vùng dữ liệu cột sau khi chuyển đổi không được giao nhau, và ngược lại. 2.2. Các dạng dữ liệu trong Excel Trong Excel có nhiều dạng dữ liệu, vì vậy một ô (cell) có thể chứa một trong các dạng dữ liệu đó. Để định dạng dữ liệu, cách làm như sau: B1: Đánh dấu vùng muốn định dạng B2: Chọn một trong các cách C1: Vào menu Format | Cells 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 33 C2: Dùng tổ hợp phím tắt Ctrl + 1 C3: Kích phải chuột lên vùng đánh dấu chọn Format Cells B3: Hộp thoại Format Cells hiện ra chọn tab Number. General - Dữ liệu tổng quát: Kiểu này do Excel tự động nhận dạng Number - Dữ liệu số: Dữ liệu kiểu số tự động căn thẳng bên phải ô. Các đặc điểm khác:  Theo ngầm định, kiểu số sử dụng dấu chấm (.) để ngăn cách phần thập phân và phần nguyên.  Biểu diễn số âm trong Excel sử dụng 4 cách.  Cách 1: Sử dụng dấu trừ (-) như trong toán học.  Cách 2: Sử dụng dấu ngoặc đơn như trong kế toán.  Cách 3: Sử dụng màu đỏ.  Cách 4: Vừa dùng màu đỏ vừa dùng dấu ngoặc đơn. Các hàm tài chính của Excel sử dụng cách thứ tư.  Để sử dụng dấu phân cách phần nghìn, bấm chọn ô Use 1000 separator (,)  Thay đổi số các số sau dấu phân cách thập phân trong ô Decimal places Currency - Dữ liệu kiểu tiền tệ: Dữ liệu kiểu tiền tệ tự động căn thẳng bên phải ô. Dạng này biểu diễn các đơn vị tiền tệ của các nước trên thế giới. Dữ liệu kiểu tiền tệ biểu diễn số thập phân giống như dữ liệu kiểu số. Accounting - Dữ liệu kiểu kế toán: Dữ liệu kiểu kế toán tự động căn thẳng bên phải ô. Dữ liệu kiểu kế toán cũng biểu diễn các đơn vị tiền tệ tương tự như dữ liệu kiểu currency, tuy nhiên không có cách biểu diễn số âm. Date - Kiểu ngày tháng: Dữ liệu kiểu ngày tháng tự động căn thẳng bên phải ô. Kiểu ngày tháng có thể thực hiện với các phép tính số học. Theo ngầm định, kiểu ngày tháng nhập vào Excel theo dạng MM/DD/YY. 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 34  MM: Biểu diễn tháng.  DD: Biểu diễn ngày.  YY: Biểu diễn năm. Trong cửa sổ type liệt kê các cách hiển thị ngày trong Excel. Time - Kiểu thời gian: Kiểu thời gian tự động căn thẳng bê phải ô. Biểu diễn thời gian trong Excel có dạng HH:MM:SS.  HH: Chỉ giờ.  MM: Chỉ phút  SS: Chỉ giây. Percentage - Kiều phần trăm: Kiểu phần trăm tự động căn thẳng bên phải ô. Kiểu phần trăm đổi một số sang dạng phần trăm bằng cách nhân với 100 và thêm dấu % vào sau số đó. Fraction - Kiểu phân số: Kiểu phân số tự động căn thẳng bên phải ô. Kiểu phân số biểu diễn các số ở dạng phân số. Chọn kiểu hiển thị trong mục Type Text - Kiểu ký tự: Dữ liệu kiểu ký tự tự động căn trái. Sử dụng dữ liệu kiểu ký tự trong hàm hoặc trong các phép toán phải được bao giữa cặp dấu nháy kép (“”). Lưu ý: Cặp dấu nháy kép khác 2 cặp dấu nháy đơn (‘’). Kiểu ký tự bao gồm sự pha trộn của các chữ cái các chữ số và các ký tự đặc biệt. Chú ý: Với các dãy kí tự bắt đầu bằng số 0 vô nghĩa (ví dụ số điện thoại 0913) khi nhập vào bảng tính, Excel sẽ tự động cắt đi số 0 đầu tiên. Để giữ lại số 0 này, sử dụng dấu (‘) trước khi dãy kí tự hoặc định dạng ô kiểu ký tự. Scientific - Kiểu rút gọn: Áp dụng cho kiểu số. Khi biểu diễn các số quá lớn hoặc quá nhỏ, Excel đưa về dạng rút gọn để tiết kiệm không gian. Ví dụ: 123.45 = 1.23E+02; 0.00123 = 1.23E-03 Special - Kiểu đặc biệt: Kiểu này dùng để biểu diễn các dạng đặc biệt như mã số bưu điện, số điện thoại kiểu Mỹ Custom - Kiểu do người dùng định nghĩa: Với kiểu này người dùng có thể định nghĩa cách hiển thị các dạng dữ liệu theo ý muốn. Ví dụ: Khi nhập ngày vào Excel, có thể sử dụng cách ngầm định MM/DD/YY (tháng/ngày/năm). Sau đó sử dụng kiểu custom để định dạng thành ngày kiểu Việt Nam bằng cách nhập vào cửa sổ type DD/MM/YYYY (Ngày/Tháng/Năm). 2.3. Các phép toán trong Excel Phép toán Ý nghĩa Ví dụ + Phép cộng 1+2 = 3 - Phép trừ 5-2 = 3 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 35 * Phép nhân 3*5 = 15 / Phép chia 27/3 = 9 ^ Luỹ thừa 2^3 = 23 = 8 % Phần trăm 5% = 0.05 & Nối chuỗi “Việt” & “Nam” = “Việt Nam” > Lớn hơn 4 > 3 = True < Nhỏ hơn 4 < 3 = False >= Lớn hơn hoặc băng “Aa” >= “aa” = False <= Nhỏ hơn hoặc bằng “Aa” <= “aa” = True = Bằng “B” = “B” = True Khác “a” “A” = True Trong đó mức độ ưu tiên đối với các toán tử tuân theo nguyên tắc toán học thông thường. 3. Sử dụng hàm trong Excel 3.1. Khái niệm hàm và cách dùng a. Khái niệm hàm Hàm (Function) trong Excel là một tổ hợp các công thức đã được xây dựng sẵn nhằm thực hiện các tính toán chuyên biệt nào đó. Hàm được đặc trưng bởi tên hàm, dấu mở đóng ngoặc và danh sách các tham số. VD: Hàm tính tổng =Sum(2,5,3) = 10 b. Dạng chung của các hàm Công thức chung áp dụng cho tất cả các hàm là: =Tên hàm(Danh sách tham số). Mỗi tham số cách nhau một dấu phẩy (,), nếu các đối số ở một vùng liên tục có thể nhập (đối số thứ nhất: đối số thứ n). VD1: Tính tổng mà các tham số được nhập trực tiếp. =SUM(3,5,9) Kết quả: 17 VD2: Cần tính tổng các ô từ A1 đến D1, ta có thể làm như sau: =SUM(A1,A2,A3,A4) hoặc = SUM(A1:A4) 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 36 Chú ý: Có thể sử dụng các hàm lồng nhau, hàm nọ làm đối số cho hàm kia. Excel cho phép các hàm lồng nhau tối đa là 7 cấp. Dấu phân cách giữa các hàm là dấu chấm phẩy (,) khi dấu phân cách giữa phần nguyên và phần thập phân là dấu chấm (.) Dấu phân cách giữa các hàm là dấu chấm phẩy (;) khi dấu phân cách giữa phần nguyên và phần thập phân là dấu phẩy (,) Một vùng dữ liệu liên tục trong Excel được coi là một tham số của hàm. c. Phân loại các hàm trong Excel Các hàm trong Excel được chia làm 9 nhóm: - Financial : Các hàm về tài chính. - Date & Time: Các hàm về thời gian. (Day, Month..) - Math & Trig : Các hàm toán học (Sum, Round, Sin, Cos..) - Statistical : Các hàm thống kê.(Count, Average) - Lookup & Reference : Các hàm tìm kiếm ( Vlookup, Hlookup) - Database : Các hàm về cơ sở dữ liệu ( DSUM, DCOUNT) - Text : Các hàm về xâu ký tự và chuỗi. (LEN, LEFT, Upper) - Logical: Các hàm logic ( AND, OR, IF) - Information (CELL, ISTEXT) d. Cách nhập hàm C1: Nhập trực tiếp lên thanh công thức (Formula) C2: Kích hoạt menu Insert  chọn Function Hộp thoại Insert Function như hình bên: C3: Bấm vào chữ fx trên thanh Formula  Chọn tên hàm bên cửa sổ phải (ví dụ SUM) rồi bấm OK Hộp thoại SUM xuất hiện như sau: Nhập các tham số vào mục Number rồi ấn nút OK 3.2. Cách dùng một số hàm toán học - ABS(number): Trị tuyệt đối của số number 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 37 Ví dụ: =ABS(-5)=5; =ABS(A4) = 14 - INT(number): Lấy phần nguyên của số number VD: =INT(123.45) = 123 VD: =INT(5/2) = 2 - MOD(number,divisor): Lấy phần dư của number chia cho divisor VD: =MOD(100,3) = 1 - SQRT(number): Hàm lấy căn bậc 2 của số number VD: =SQRT(9) = 3 - COUNT(Value1, Value2,, Valuen): Đếm xem trong danh sách các tham số có bao nhiêu tham số có giá trị là số. VD: =COUNT(A1:A5) = 2 - SUM(Number1, Number2,, Number n): Tính tổng các số trong danh sách tham số. VD: =SUM(A1:A3) = 15 Hoặc =SUM(A1,A2,A3) = 15 - AVERAGE(Number1, Number2,, Number n): Tính giá trị trung bình các số trong danh sách tham số. VD: =AVERAGE(A1:A3)= 5 - ROUND(number, num_digits): làm tròn số number tới num_digits chữ số sau dấu phẩy Nếu num_digits >0 : Làm tròn sang phải Nếu num_digits <0: Làm tròn sang trái VD: =ROUND(123.456,1) = 123.5 =ROUND(123.456,-1) = 120 - MAX(Number1, Number2,, Number n): Cho giá trị lớn nhất trong dãy số VD: =MAX(A1:A4) = 11 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 38 - MIN(Number1, Number2,, Number n): Cho giá trị nhỏ nhất trong dãy số VD: =MIN(2,3,1,9,4,5,6,7,8) = 1 - PI(): Cho giá trị của số pi =PI() = 3.14159 - RAND(): Cho một số ngẫu nhiên lớn hơn 0 và nhỏ hơn 1 - EXP(x): Trả về giá trị của hàm mũ ex VD: = EXP(1) = e1 = 2.718282 - LN(x): Lôgarit cơ số e của x (Logarit cơ số tự nhiên) VD: = LN(10) = Loge(10) = 2.302585093 - LOG(number, base) : Lôgarit cơ số base của number VD: = LOG(8,2) = log28 = 3 - LOG10(x): Lôgarit cơ số 10 của x VD: = LOG10(100) = Log10100 = 2 - COUNTIF(vùng cần đếm, điều kiện đếm): Đếm trong vùng cần đếm xem có bao nhiêu giá trị thoả mãn vùng điều kiện đếm. VD: Đếm số SV thi lại = COUNTIF(D2:D6,"<5") - SUMIF(cột chứa giá trị điều kiện, điều kiện, cột cần tính tổng): Tính tổng các giá trị trong cột cần tính tổng mà những giá trị trong cột chứa giá trị điều kiện thoả mãn điều kiện. VD: Tính tổng doanh thu đối với mặt hàng ti vi = SUMIF(B2:B7,"Ti vi",D2:D7) 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 39 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 40 3.3. Cách dùng một số hàm xử lý ký tự - LEN(Text): Cho biết độ dài của xâu Text. VD: =LEN(B7) = 18 - LEFT(Text, n): Trích từ chuỗi Text ra n ký tự từ bên trái sang. VD: =LEFT(B7,LEN(B7)-7)= environment =LEFT(B7,C7-7)= environment - MID(Text, m, n): Lấy từ chuỗi Text ra n ký tự từ vị trí thứ m. VD: =MID(B7, LEN(D7)+2, 3)= org - RIGHT(Text, n): Trích chuỗi Text n ký tự từ bên phải sang. VD: =RIGHT(B7, 2)= au 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 41 - TRIM(Text): Xoá tất cả các ký tự trắng thừa trong xâu Text (các vị trí thừa là: đầu xâu, cuối xâu và có nhiều hơn 1 khoảng trắng - dấu cách giữa 2 từ) - PROPER(Text): Viết hoa ký tự đầu mỗi từ - VALUE(Text): Đổi xâu Text có dạng số thành số 3.4. Cách dùng một số hàm hàm về thời gian - TODAY(): Cho ngày hiện hành của hệ thống. - NOW(): Cho ngày giờ hiện hành của hệ thống. - DAY(ngày): Cho ngày của chuỗi ngày. - MONTH(ngày): Cho tháng của chuỗi ngày - YEAR(ngày): Cho năm của chuỗi ngày - HOUR(giờ): Cho giờ trong chuỗi giờ 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 42 - MINUTE(giờ): Cho phút trong chuỗi giờ - DATE (Năm,Tháng, Ngày): Trả về giá trị là kiểu ngày tháng năm 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 43 3.5. Cách dùng một số hàm logic - IF(Biểu thức điều kiện, Kq khi giả thiết đúng, Kq khi giả thiết sai) Ý nghĩa: Kiểm tra biểu thức điều kiện, nếu biểu thức điều kiện đúng (True) thì kết quả của hàm IF bằng Kq khi giả thiết đúng, còn lại thì kết quả của hàm IF bằng Kq khi giả thiết sai Lưu ý: Hàm IF cho phép lồng nhau tối đa là 7 cấp VD: =IF(H2>=5.0,"Lên lớp","Lưu ban") VD: Dùng công thức điền vào cột xếp loại. Tại ô J2 ta nhập công thức sau = IF(H2>=9,"Giỏi",IF(H2>=8,"Khá",IF(H2>=6.5,"Trung bình khá",IF(H2>=5,"Trung bình",IF(H2>3.5,"Yếu","Kém"))))) - AND (biểu thức lôgic1, biểu thức lôgic2, ,biểu thức lôgic n) Nếu tất cả các biểu thức đều đúng thì trả về giá trị “TRUE” Nếu 1 trong các biểu thức logic sai thì trả về giá trị “FALSE” VD: Điền vào cột điểm ưu tiên với điều kiện là: Nếu ĐTB lớn hơn hoặc bằng 7.00 và là dân tộc ít người thì điểm ưu tiên là 1.5, ngược lại thì không ưu tiên. =IF(AND(H2>=7, C2”Kinh”), 1.5, 0) - OR(biểu thức lôgic1, biểu thức lôgic2, ,biểu thức lôgic n) Nếu 1 trong các biểu thức logic đùng thì trả về giá trị “TRUE” Nếu tất cả các biểu thức đều sai thì trả về giá trị “FALSE ” VD:Xét thưởng đối với những SV là con em gia đình chính sách hoặc thuộc đối tượng là dân tộc ít người. =IF(OR(C2 “Kinh”,D2 “BT”), “Thưởng”, “Không thưởng”) 3.6. Cách dùng một số hàm cơ sở dữ liệu Đặc điểm của các hàm cơ sở dữ liệu là  Có khả năng giải được các bài toán có nhiều hơn 1 điều kiện (ràng buộc).  Làm việc với các hàm cơ sở dữ liệu thường phải lập thêm bảng phụ để chứa các điều kiện. - DCOUNT(vùng dữ liệu, cột cần đếm, vùng điều kiện): Đếm xem trong vùng dữ liệu có bao nhiêu giá trị ở cột cần đếm có dạng số thoả mãn vùng điều kiện. VD: Đếm xem có bao nhiêu cửa hàng trong tháng 1 có doanh thu lớn hơn 100. 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 44 =DCOUNT(A1:C9,A1,A11:B12) - DSUM(vùng dữ liệu, cột cần tính tổng, vùng điều kiện): Tính tổng các giá trị trong cột cần tính tổng nằm trong vùng dữ liệu thoả mãn vùng điều kiện. VD: Tính tổng doanh thu của cửa hàng số 2 =DSUM(A1:C9,C1,A11:A12) - DMAX(vùng dữ liệu, cột cần tìm phần tử lớn nhất, vùng điều kiện): Tìm giá trị lớn nhất ở cột cần tìm phần tử lớn nhất trong vùng dữ liệu sao cho thoả mãn vùng điều kiện. VD: Doanh thu lớn nhất của cửa hàng số 3 là bao nhiêu? =DMAX(A1:C9,C1,A11:A12) - DMIN(vùng dữ liệu, cột cần tìm phần tử nhỏ nhất, vùng điều kiện): Tìm giá trị nhỏ nhất ở cột cần tìm phần tử nhỏ nhất trong vùng dữ liệu sao cho thoả mãn vùng điều kiện 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 45 VD: Doanh thu thấp nhất của cửa hàng số 3 là bao nhiêu? =DMIN(A1:C9,C1,A11:A12) - DAVERAGE(vùng dữ liệu, cột cần tính giá trị trung bình, vùng điều kiện): Tính giá trị trung bình ở cột cần tính giá trị trung bình trong vùng dữ liệu sao cho thoả mãn vùng điều kiện. VD: Tính doanh thu trung bình của cửa hàng số 3 trong 2 tháng đầu năm? = DAVERAGE(A1:C9,C1,A11:A12) 3.7. Cách dùng một số hàm tìm kiếm - VLOOKUP: Tìm kiếm theo chiều dọc Dạng thức: VLOOKUP(X, vùng tham chiếu, cột lấy giá trị, kiểu dò tìm)  X: Giá trị mang ra để tìm kiếm trên cột đầu tiên của vùng tham chiếu  Vùng tham chiếu(bảng phụ): Bảng lấy dữ liệu, cột bên trái nhất được tính là cột 1 (vùng tham chiếu phải dùng địa chỉ tuyệt đối)  Cột lấy giá trị: Cột tham chiếu để lấy giá trị, thứ tự cột này tính theo cột bên trái nhất của vùng tham chiếu (bảng phụ).  Kiểu dò tìm: 0 | 1 o Ngầm định là 1: Cột để lấy X phải sắp xếp tăng. o Nếu chọn 0: Cột để lấy X không cần phải sắp xếp. Ví dụ: Yêu cầu điền dữ liệu vào cột Chức vụ với điều kiện cho trong bảng dưới =VLOOKUP(C2,$A$12:$B$15,2,0)= “Giám đốc” 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 46 - HLOOKUP(X, vùng tham chiếu, cột lấy giá trị, kiểu dò tìm): Dò tìm theo chiều ngang VD: Yêu cầu tính số tiền phụ cấp mà mỗi người được hưởng, mức phụ cấp tương đương được cho trong bảng phụ phía dưới =HLOOKUP(D2,$E$11:$H$12,2,0) = 300000 4. Cơ sở dữ liệu trong Excel 4.1. Khái niệm cơ sở dữ liệu Cơ sở dữ liệu (CSDL) là tập hợp dữ liệu được bố trí trên 1 vùng hình chữ nhật (gồm ít nhất là 2 hàng) của bảng tính. Thông thường, hàng đầu tiên ghi các tiêu đề của vùng dữ liệu, mỗi tiêu đề trên 1 cột, tiêu đề này gọi là tên trường (field name). Mỗi cột gọi là một trường (field). Từ hàng thứ hai trở đi chứa dữ liệu, mỗi hàng gọi là 1 bản ghi (record). 4.2. Thao tác với cơ sở dữ liệu a) Sắp xếp dữ liệu. Điều kiện để sắp xếp là trong vùng dữ liệu không được chứa ô dữ liệu hoà trộn ô (trừ dòng tiêu đề). 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 47 Các bước để sắp xếp dữ liệu bao gồm: B1: Chọn vùng dữ liệu cần sắp xếp. B2: Vào menu Data | Sort, hộp thoại Sort xuất hiện Excel cung cấp 3 ràng buộc (điều kiện) cho việc ưu tiên sắp xếp. Nếu dữ liệu trong mục Sort by giống nhau thì Excel ưu tiên sắp xếp theo điều kiện trong cột Then by thứ nhất, và nếu dữ liệu trong cột Then by thứ nhất lại giống nhau thì sẽ ưu tiên sắp xếp theo điều kiện trong cột Then by thứ 2.  Sort by: Cột ưu tiên thứ nhất trong khoá sắp xếp.  Then by: Cột ưu tiên thứ hai và thứ ba trong khoá sắp xếp  Ascending: Sắp xếp tăng dần  Descending: Sắp xếp giảm dần  Header row: Không chứa dòng tiêu đề trong việc sắp xếp  No header row: Sắp xếp cả dòng tiêu đề . b) Lọc dữ liệu Mục đích là lấy ra những dữ liệu có ích thoả mãn một vài điều kiện nào đó nhằm phục vụ cho việc thống kê, lập báo cáo chi tiết Excel cung cấp 2 dạng lọc là lọc tự động và lọc nâng cao. * Lọc tự động (AutoFilter) Sử dụng phương pháp lọc tự động, những bản ghi thoả mãn điều kiện lọc sẽ được hiển thị, còn những bản ghi không thoả mãn điều kiện sẽ bị ẩn đi. Phương pháp này áp dụng khi điều kiện lọc nằm trên một trường (cột). Các bước thực hiện. B1: Chọn vùng dữ liệu cần lọc. B2: Vào menu Data | Filter | AutoFilter, bên cạnh tên các trường xuất hiện các mũi tên màu đen. 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 48 B3: Chọn mũi tên chứa tên trường ta muốn đặt điều kiện lọc, chọn Custom, hộp thoại Custom AutoFilter hiện ra. Ràng buộc Ý nghĩa equals = does not Equals is greater than > is greater than or equals to >= is less than < is less than or equals to <= end with kết thúc với does not end with không kết thúc với begin with bắt đầu với does not begin with không bắt đầu với contains có chứa does not contain không chứa And: điều kiện và Or: điều kiện hoặc Huỷ lọc bằng cách vào menu Data | Filter | bỏ đánh dấu AutoFilter. * Lọc nâng cao (Advanced Filter) Lọc nâng cao dùng để lọc các bản ghi thoả mãn các điều kiện phức tạp hơn và có nhiều hơn 1 trường điều kiện. Chức năng này ứng với các điều kiện trực tiếp hoặc gián tiếp. Sử dụng chức năng này yêu cầu phải lập vùng điều kiện (bảng phụ).  Điều kiện trực tiếp: Là những điều kiện mà không chứa công thức ở trong. Với một trường (cột) có từ 2 điều kiện trở lên thì: - Điều kiện xảy ra đồng thời AND (và) được thể hiện trên cùng 1 dòng (cùng 1 bản ghi) VD: Lập vùng điều kiện là Điểm trung bình lớn hơn 6.5 và nhỏ hơn 7. 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 49 - Điều kiện xảy ra không đồng thời OR (hoặc) được thể hiện trên các dòng khác nhau. VD: Vùng điều kiện cho những SV thuộc diện gia đình chính sách hoặc là dân tộc ít người.  Điều kiện gián tiếp: Là những điều kiện có chứa công thức bên trong, giá trị của vùng điều kiện này là giá trị logic (TRUE hoặc FALSE) Tiêu đề vùng điều kiện không được trùng với tiêu đề của bất kỳ trường nào. VD: Vùng điều kiện để lọc ra những khách hàng đi trước ngày 15. Các bước thực hiện lọc sau khi đã thiết lập được vùng điều kiện. B1: Chọn vùng dữ liệu muốn lọc. B2: Vào menu Data | Filter | Advanced Filter, hộp thoại Advanced Filter hiện ra. 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 50  Filter the list, in-place: Chỉ hiển thị các bản ghi thoả mãn điều kiện lọc.  Copy to another location: Copy các bản ghi thoả mãn điều kiện lọc ra vị trí mới (*)  List range: Vùng dữ liệu dùng để lọc (phải dùng địa chỉ tuyệt đối).  Criteria range: Vùng điều kiện (dùng địa chỉ tuyệt đối).  Copy to: Vị trí đặt kết quả lọc nếu chọn *  Unique Records only: Đưa ra duy nhất một bản ghi nếu có nhiều hơn 1 bản ghi thoả mãn điều kiện lọc. 4.3. Đồ thị trong Excel Đồ thị là mô hình phản ánh trực quan những số liệu đã được thống kê trong bảng tính, biểu diễn dữ liệu bằng đồ thị sẽ làm tăng thêm sức hấp dẫn và thuyết phục của số liệu. Ví dụ: Có số liệu như bảng sau: a) Thiết lập đồ thị B1: Chọn vùng dữ liệu muốn thành lập đồ thị. (B4:F10) B2: Dùng một trong các cách: 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 51 C1: Vào menu Insert | Chart C2: Bấm vào biểu tượng Chart Wizard trên thanh công cụ chuẩn Standard. B3: Chọn kiểu đồ thị muốn sử dụng trong hộp thoại Chart Wizard – Step 1 of 4 (có thể bấm vào nút Press and Hold to View Sample để xem trước dạng hiển thị của đồ thị). Chọn Next để sang bước tiếp theo. B4: Trong hộp thoại Chart Wizard – Step 2 of 4 Tab Data Range: Data range: Giới hạn vùng dữ liệu cần tạo đồ thị Rows: Dữ liệu trong vùng dữ liệu được coi là đánh khối theo hàng. Columns: Dữ liệu trong vùng dữ liệu được coi là đánh khối theo cột. Tab Series: Category (X) axis labels: Chọn tiêu đề cho trục X. Chọn Next để sang bước tiếp theo. 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 52 B5: Thiết đặt thêm các thông số cho đồ thị Chart title: Tiêu đề của đồ thị Category (X) axis: Tiêu đề cho trục X. Value (Y) axis: Giá trị hiển thị trên cột Y. Chọn Next để sang bước tiếp theo. B6: Lựa chọn vị trí đặt đồ thị As new sheet: Đặt vào một sheet mới As Object in: Đặt đồ thị tại sheet hiện hành Chọn Finish để hoàn thành việc thiết đặt đồ thị Ban đầu, có thể đồ thị hiển thị lên rất xấu, dùng chuột để kéo giãn hiệu chỉnh sao cho thích hợp. b) Hiệu chỉnh đồ thị Để hiệu chỉnh đồ thị, kích phải chuột lên nền đồ thị: Chart type: Chọn lại kiểu đồ thị Source data: Chọn lại vùng dữ liệu cho đồ thị Chart Options: Thiết lập lại các thuộc tính cho đồ thị. Location: Vị trí đặt đồ thị 5. In ấn bảng tính a) Thiết đặt khung trang và khổ giấy. 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 53 Vào menu File | Page setup, hộp thoại Page setup hiện ra. Tab Page: Thiết đặt khổ giấy Portrait: Dùng khổ giấy dọc. Landscape: Dùng khổ giấy ngang. Paper size: Cỡ giấy (thông thường dùng khổ A4). Tab Margin: Thiết đặt khung trang  Top: Lề trên  Bottom: Lề dưới  Left: Lề trái  Right: Lề phải  Horizontally: Căn giữa vùng bảng tính theo chiều ngang  Vertically: Căn giữa vùng bảng tính theo chiều dọc Tab Header/Footer: Đặt tiêu đề đầu và tiêu đề cuối Chon Custom Header để đặt tiêu đề đầu và Custom Footer để đặt tiêu đề cuối. Tab Sheet Print area: Vùng lựa chọn để in. Rows and column headings: In cả dòng tiêu đề trên mỗi trang in nếu bảng biểu trải dài trên nhiều trang. b) In ấn B1:Lựa chọn vùng dữ liệu muốn in và chọn một trong các cách C1: Vào menu File | Print C2: Dùng tổ hợp phím tắt Ctrl + P, hộp thoại Print hiện ra 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 54  All: In toàn bộ workbook.  Pages: In theo số trang chỉ định  From: Trang bắt đầu.  To: Trang kết thúc.  Selection: Chỉ in vùng dữ liệu được chọn lựa  Active Sheet: In toàn bộ Sheet hiện hành.  Number of copies: Số bản in.  Preview: Xem trước khi in. B2: Nhấn OK để bắt đầu in. 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 55 CHƯƠNG 3. ĐƯA CÁC BÁO CÁO TÀI CHÍNH VÀO EXCEL 1. Thiết kế sổ nhật kí chung 1.1 Xây dựng danh mục tài khoản: Mục đích của danh mục tài khoản là dùng làm từ điển trong các sổ sách kế toán. Khi cần sử dụng chỉ cần dùng hàm VLOOKUP để gọi lại, không phải nhập tên các tài khoản. Cấu trúc bảng này gồm 2 cột, cột thứ nhất chứa mã tài khoản và cột thứ hai chứa tên gọi tài khoản như hình sau: vùng từ ô $c$4:$d$83 được đặt tên là DmTk (danh mụctài khoản) bằng cách: Insert -> name-> define -> DmTK Trường hợp cần nhập số dư cho nhanh có thể thêm 2 cột số dư trong danh mục tài khoản để sau này nhập vào các sổ khác bằng hàm VLOOKUP như hình sau: Đổi tên sheet này thành DmTkhoan 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 56 1.2 Xây dựng sổ nhật kí chung Sổ nhật kí chung này chứa các số liệu dùng làm căn cứ để lập các báo cáo sau này. Xây dựng sổ này như sau Khi nhập dữ liệu thì các dòng trên sổ nhật kí không nên để trống và vì vậy khi định khoản kép phải ghi lặp lại. Ví dụ nghiệp vụ trích khấu hao TSCĐ ghi như sau: Nợ 6274 100 000 Nợ 6414 5 000 Nợ 6424 15 000 Có 214 120 000 khi vào sổ nhật kí có dạng sau: 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 57 Đặt tên cho các vùng trong sổ này như sau: Vùng $C$4:$C$33 được đặt tên là NK_Ngay ( nhật kí, cột ngày) Vùng $E$4:$E$33 được đặt tên là NK_Tk_No (cột Tài khoản nợ sổ nhật kí) Vùng $F$4:$F$33 được đặt tên là NK_Tk_Co (cột Tài khoản có sổ nhật kí) Vùng $G$4:$G$33 được đặt tên là NK_PS_No (Cột số phát sinh nợ sổ nhật kí) Vùng $H$4:$H$33 được sđặt tên là NK_PS_Co (Cột số phát sinh Có sổ nhật kí) Đổi tên sheet này thành SoNKi 2. Thiết kế bảng đối chiếu số phát sinh phát sinh Bảng này được cập nhật số liệu từ sổ nhật kí và bảng danh mục Tài khoản như sau: cột tên tài khoản được cập nhật từ bảng danh mục tài khoản (DmTkhoan) bằng công thức sau: tại ô C4 nhập công thức = VLOOKUP(B4,DmTkhoan,2,0) Copy công thức này cho các ô C5:C70 Mục số dư đầu kì có thể nhập trực tiếp tại đây. Cũng có thể cập nhật từ danh mục tài khoản nếu bảng DmTkhoan được xây dựng từ ban đầu có cả cột số dư đầu kì. Nếu Cập nhật từ DmTkhoan thì tại ô D4 nhập công thức: =VLOOKUP(B4,DmTkhoan,3,0) và tại ô E4 nhập công thức =VLOOKUP(B4,DmTkhoan,4,0). Copy công thức này cho các ô từ D5:E70. Các cột phát sinh nợ và phát sinh có của bảng này được cập nhật từ sổ nhật kí bẳng công thức mảng sau: (chú ý cặp dấu { } là do nhấn đồng thời tổ hợp phím Ctrl+SHIFT+ENTER. Ô F4 nhập công thức: {=sum(if(left(NK_Tk_No,len(B4))=left(B4,len(B4)),1,0)*NK_PS_No)} Ô G4 nhập công thức: {=sum(if(left(NK_Tk_Co,len(B4))=left(B4(len(B4)),1,0)*NK_PS_Co)} Trong cột số dư cuối kì nhập công thức sau: Tại Ô H4 nhập công thức: =if((D4-E4+F4-G4)>0,D4-E4+F4-G4,0) 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 58 Tại Ô I4 nhập công thức =if((D4-E4+F4-G4)<0,-(D4-E4+F4-G4),0) Copy các công thức trên cho các ô F5:I70. Bây giờ đặt tên các vùng trong sổ này như sau: Vùng $B$4:$B$70 đặt tên là CD_Ma_TK (cột mã tài khoản trong bảng cân đối phát sinh) Vùng $D$4:$D$70 đặt tên là CD_ DK_No (Số dư Đầu kì bên nợ trong bảng CD PS) Vùng $E$4:$E$70 đặt tên là CD_DK_Co (Số dư đầu kì bên có trong bảng CD PS) Vùng $F$4:$F$70 đặt tên là CD_PS_No (Số phát sinh bên nợ của bảng CD PS) Vùng $G$4:$G$70 đặt tên là CD_PS_Co (Số phát sinh bên có của bảng CD PS) Vùng $H$4:$H$70 đặt tên là CD_CK_No (Số dư cuối kì bên nợ bảng CD PS) Vùng $I$4:$I$70 đặt tên là CD_CK_Co (Số dư cuối kì bên có bảng CD PS) Đổi tên toàn bộ sheet này thành CD_PSinh 3. Xây dựng bảng cân đối kế toán Bảng này được cập nhật dữ liệu chủ yếu từ bảng cân đối phát sinh theo các công thức. Nhìn chung các công thức ở phần tài sản có dạng giống nhau và các công thức ở phần nguồn vốn có dạng giống nhau. Các ô E8 sử dụng công thức mảng sau: {=sum(if(CD_Ma_TK=D8,1,0)*(CD_DK_No-CD_DK_Co))} Copy công thức này cho các ô: E9,10,12,13,14,18,19,20,21,25,26,27,28,29,30,32,34,35,36,38,39,43,44,46,47,49,59,52,53,54 ,55,56,57. Trong ô F8 sử dụng công thức mảng sau: {=sum(if(CD_Ma_TK=D8,1,0)*(CD_CK_No-CD_CK_Co))} Copy công thức này cho các ô: F9,10,12,13,14,18,19,20,21,25,26,27,28,29,30,32,34,35,36,38,39,43,44,46,47,49,59,52,53,54, 55,56,57. Riêng 2 ô E16,17 sử dụng công thức mảng sau: {=sum(if(CD_Ma_TK=D16,1,0)*CD_DK_No)}, và copy công thức này cho ô E17. Hai ô F16 và F17 sử dụng công thức mảng : {=sum(if(CD_Ma_TK=D16,1,0)*CD_CK_No)} và copy công thức này cho ô F17. Sử dụng hàm SUM để tính các chỉ tiêu là tổng của các chỉ tiêu khác.Ví dụ ô E7 nhập công thức =sum(E8:E10). Các ô khác tuỳ theo chỉ tiêu mà nhập công thức thích hợp. Phần nguồn vốn sử dụng các công thức sau để tính toán Trong ô E65 nhập công thức mảng sau: {=sum(if(CD_Ma_TK=D65,1,0)*(CD_DK_Co-CD_DK_No))} Copy công thứ này cho các ô E66,69,70,71, 72,74,75, 77,79,82, 83,84,85,86,87, 88,89,91,92. Trong ô F65 nhập công thức mảng sau: {=sum(if(CD_Ma_TK=D65,1,0)*(CD_CK_Co-CD_CK_No))} Copy công thứ này cho các ô F66,69,70,71, 72,74,75, 77,79,82, 83,84,85,86,87, 88,89,91,92. Riêng trong các ô E67,68,78 sử dụng các công thức mảng sau: {=sum(if(CD_Ma_TK=D67,1,0)*CD_DK_Co)} copy công thức này cho các ô E68,78. Riêng trong các ô F67,68,78 sử dụng các công thức mảng sau: {=sum(if(CD_Ma_TK=D67,1,0)*CD_DK_Co)} copy công thức này cho các ô F68,78. 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 59 Sử dụng hàm SUM để tính các chỉ tiêu là tổng của các chỉ tiêu khác.Ví dụ ô E64 nhập công thức =sum(E65:E72). Các ô khác tuỳ theo chỉ tiêu mà nhập công thức thích hợp. Đổi tên sheet này thành CDoi_KeToan 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 60 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 A B C D E F M∙ sè ChØ tiªu Tªn tµikho¶n Sè ®Çu n¨m Sè cuèi n¨m tµi s¶n 100 A. TSL§ vµ ®Çu t­ ng¾n h¹n. 1124.26 110 I. TiÒn 300 444 111 1. TiÒn mÆt t¹i quü (gåm c¶ ng©n phiÕu) 111 100 94 112 2. TiÒn göi ng©n hµng. 112 200 350 113 3. TiÒn ®ang chuyÓn. 113 0 0 120 II. C¸c kho¶n ®Çu t­ tµi chÝnh ng¾n h¹n. 0 0 121 1. §Çu t­ chøng kho¸n ng¾n h¹n. 121 0 0 128 2. §Çu t­ ng¾n h¹n kh¸c. 128 0 0 129 3. Dù phßng gi¶m gi¸ hµng tån kho(*). 129 0 0 130 III. C¸c kho¶n ph¶i thu. 220 471 131 1. Ph¶i thu cña kh¸ch hµng. 131 150 401 132 2. Tr¶ tr­íc cho ng­êi b¸n. 331 70 70 133 3. ThuÕ GTGT ®­îc khÊu trõ. 133 0 0 134 4. Ph¶i thu néi bé. 136 0 0 138 5. C¸c kho¶n ph¶i thu kh¸c. 138 0 0 139 6. Dù phßng c¸c kho¶n ph¶i thu khã ®ßi (*) 139 0 0 140 IV. Hµng tån kho. 390 209.26 141 1. Hµng mua ®ang ®i trªn ®­êng. 151 0 0 142 2. Nguyªn liÖu, vËt liÖu tån kho. 152 250 35 143 3. C«ng cô dông cô trong kho. 153 0 0 144 4. Chi phÝ s¶n xuÊt kinh doanh dë dang. 154 80 94.26 145 5. Thµnh phÈm tån kho. 155 0 80 146 6. Hµng ho¸ tån kho. 156 0 0 147 7. Hµng göi ®i b¸n. 157 60 0 149 8. Dù phßng gi¶m gi¸ hµng tån kho. 159 0 0 150 V. Tµi s¶n l­u ®éng kh¸c. 0 0 151 1. T¹m øng. 141 0 0 152 2. Chi phÝ tr¶ tr­íc. 1421 0 0 153 3. Chi phÝ chê kÕt chuyÓn. 1422 0 0 154 4. Tµi s¶n thiÕu chë xö lý. 1381 0 0 155 5. C¸c kho¶n ký c­îc, ký quü ng¾n h¹n 144 0 0 160 VI. Chi sù nghiÖp. 161 3223 0 161 1. Chi sù nghiÖp n¨m tr­íc. 1611 0 0 162 2. Chi sù nghiÖp n¨m nay. 1612 0 0 200 B. TSC§ ®Çu t­ dµi h¹n 1760 3470 210 I. Tµi s¶n cè ®Þnh. 1760 3470 211 1. Tµi s¶n cè ®Þnh h÷u h×nh. 1760 1735 B¶ng c©n ®èi kÕ to¸n 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 61 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 A B C D E F 212 - Nguyªn gi¸ 211 1760 1760 213 - Gi¸ trÞ hao mân luü kÕ(*) 214 0 -25 214 2. Tµi s¶n cè ®Þnh thuÕ tµi chÝnh. 0 0 215 - Nguyªn gi¸ 212 0 0 216 - Gi¸ trÞ hao mân luü kÕ(*) 2142 0 0 217 3. Tµi s¶n cè ®Þnh v« h×nh. 0 0 218 - Nguyªn gi¸ 213 0 0 219 - Gi¸ trÞ hao mân luü kÕ(*) 2143 0 0 220 II. C¸c kho¶n ®Çu t­ tµi chÝnh dµi h¹n. 0 0 221 1. §Çu t­ chøng kho¸n dµi h¹n. 221 0 0 222 2. Gãp vèn liªn doanh 222 0 0 228 3. §Çu t­ dµi h¹n kh¸c 228 0 0 229 4. Dù phßng gi¶m gi¸ ®Çu t­ dµi h¹n(*) 229 0 0 230 III. Chi phÝ x©y dùng c¬ b¶n dë dang. 241 0 0 240 IV. C¸c kho¶n ký c­îc, ký quü dµi h¹n 244 0 0 250 Tæng céng tµi s¶n 4594.26 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 62 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 B C D E F nguån vèn M∙ sè ChØ tiªu Tªn tµikho¶n Sè ®Çu n¨m Sè cuèi n¨m 300 A. Nî ph¶i tr¶. 310 322.4 310 I. Nî ng¾n h¹n. 310 322.4 311 1. Vay ng¾n h¹n. 311 100 0 312 2. Nî dµi h¹n ®Õn h¹n tr¶. 315 0 0 313 3. Ph¶i tr¶ cho ng­êi b¸n. 331 150 150 314 4. Ng­êi mua tr¶ tiÒn tr­íc. 131 0 0 315 5. ThuÕ vµ c¸c kho¶n ph¶i nép Nhµ n­íc 333 0 41 316 6. Ph¶i tr¶ c«ng nh©n viªn 334 60 116.4 317 7. Ph¶i tr¶ cho c¸c ®¬n vÞ néi bé. 336 0 0 318 8. C¸c kho¶n ph¶i tr¶ ph¶i nép kh¸c. 338 0 15 320 II. Nî dµi h¹n. 0 0 321 1. Vay dµi h¹n. 341 0 0 322 2. Nî dµi h¹n 342 0 0 330 III. Nî kh¸c. 0 0 331 1. Chi phÝ ph¶i tr¶. 335 0 0 332 2. Tµi s¶n thõa chê xö lý. 138 0 0 333 3. NhËn ký quü ký c­îc dµi h¹n. 344 0 0 400 B. Nguån vèn chñ së h÷u. 2360 2436.86 410 I. Nguån vèn quü. 2360 2436.86 411 1. Nguån vèn kinh doanh. 411 2200 2200 412 2. Chªnh lÖch ®¸nh gi¸ l¹i tµi s¶n. 412 0 0 413 3. Chªnh lÖch tû gi¸ 413 0 0 414 4. Quü ®Çu t­ ph¸t triÓn. 414 60 60 415 5. Quü dù phßng tµi chÝnh. 415 0 0 416 6. Lîi nhuËn ch­a ph©n phèi. 421 100 176.86 417 7. Quü khen th­ëng phóc lîi 431 0 0 418 8. Nguån vèn ®Çu t­ x©y dùng c¬ b¶n. 441 0 0 420 II. Nguån kinh phÝ, quü kh¸c. 0 0 421 3. Quü qu¶n lý cña cÊp trªn 451 0 0 422 4. Nguån kinh phÝ sù nghiÖp. 461 0 0 430 Tæng céng nguån vèn 2759.26 4. Xây dựng bảng báo cáo kết quả sản xuất kinh doanh (phần Lỗ lãi) Số liệu trong bảng này được cập nhật từ bảng đối chiếu số phát sinh theo các công thức sau: Ô F4 sử dụng công thức mảng sau: ={sum(if(left(CD_Ma_TK,3)=”511”,1,0)*CD_PS_Co)+sum(ifleft(CD_Ma_TK,3)=”512”,1,0 )*CD_PS_Co)} Ô F5 sử dụng công thức mảng sau {=SUM(IF(LEFT(NK_TK_NO,4)="5111",1,0)*NK_PS_NO)} 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 63 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 A B C D E F G STT ChØ tiªu M∙ sè Tµi kho¶n K× tr­íck× nµy lòy kÕ 1 Doanh thu 01 511,512 360 360 2 Donanh thu tõ ho¹t ®éng xuÊt khÈu 02 5111 0 0 3 C¸c kho¶n gi¶m trõ 03 41 41 4 ChiÕt khÊu th­¬ng m¹i 04 521 0 0 5 Gi¶m gi¸ hµng b¸n 05 532 0 0 6 Hµng b¸n bÞ tr¶ l¹i 06 531 0 0 7 ThuÕ phÈi nép 07 3331;3332 41 41 8 Doanh thu thuÇn 10 319 319 9 Gi¸ vèn hµng b¸n 11 632 260 260 10 Lîi nhuËn ThuÇn 20 59 59 11 Doanh thu tõ ho¹t ®éng tµi chÝnh 21 515 0 0 12 Chi phÝ tõ ho¹t ®éng tµi chÝnh 22 635 0 0 13 Chi phÝ b¸n hµng 24 641 6 6 14 Chi phÝ qu¶n lý DN 25 642 17.14 17.14 15 Lîi nhuËn thuÇn tõ ho¹t ®éng kinh doanh30 35.86 35.86 16 Doanh thu kh¸c 31 711 0 0 17 Chi phÝ k¸c 32 811 0 0 18 Lîi nhuËn tõ ho¹t ®éng kh¸c 40 0 0 19 Tæng lîi nhuËn tr­íc thuÕ 50 35.86 35.86 20 ThÕ ph¶i nép 51 3334 0 0 21 Lîi nhuËn sau thuÕ 60 35.86 35.86 PhÇn I L∙i _Lç b¸o c¸o kÕt qu¶ kinh doanh Các ô F8,9,10 sử dụng công thức mảng sau: {=sum(if(D8=CD_Ma_TK,1,0)*CD_PS_No)}. Copy công thức này cho các ô E9,10. Ô E11 nhập công thức mảng sau: {=SUM(IF(LEFT(NK_TK_CO,4)="3331",1,0)*NK_PS_Co)+SUM(IF(LEFT(NK_TK_Co,4) ="3332",1,0)*NK_PS_Co)} Ô F3 nhập công thức =sum(F8:F11) Ô F12 nhập công thức =F5-F7 Ô F13 nhập công thức mảng sau: {=sum(if(CD_Ma_TK=D13,1,0)*CD_PS_No)} Ô F14 nhập công thức =F12-F13 Từ ô F15 đến ô F18 nhập công thức sau {=sum(if(CD_Ma_TK=D15,1,0)*CD_PS_No)}. Copy công thức sang các ô F16,17,18. Ô F19 nhập công thức = F14+F15-F16-F17-F18 Ô F20 nhập công thức mảng sau: {=sum(if(NK_TK_No=D20,1,0)*NK_PS_No)} Ô F21 nhập công thức mảng sau: {=sum(if(NK_TK_Co=D21,1,0)*NK_PS_Co)} Ô F22 nhập công thức =F20-F21 Ô F23 nhập công thức = F19+F22 Ô F24 nhập công thức mảng sau: {=sum(if(left(NK_TK_Co,4)=”3334”,1,0)*NK_PS_Co)} 3/1/2008Bài giảng phân tích kinh doanh bằng máy tính Trần Công Nghiệp – Phòng Thực hành Kinh doanh 64 Ô F25 nhập công thức =F23-F24 Đổi tên sheet này thành BCKQSXKD_LoLai 5. Xây dựng báo cáo lưu chuyển tiền tệ Số liệu trong bảng này lấy từ sổ nhật kí 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 A B C D E ChØ tiªu M∙ sè MTK Kú tr­íc Kú nµy I.L­u chuyÓn tiÒn tÖ tõ ho¹t ®éng kinh doanh 20 1.TiÒn thu b¸n hµng 01 511;512;3331 0 2. TiÒn thu tõ c¸c kho¶n nî ph¶i thu 02 131;138;136 0 3. TiÒn ®∙ tr¶ cho ng­êi b¸n 04 331 0 4. §∙ tr¶ c«ng nh©n viªn 05 334 3.6 5. §∙ nép thuÕ vµ c¸c kho¶n kh¸c cho nhµ n­íc 06 333 0 6. §∙ tr¶ cho c¸c kho¶n nî ph¶i tr¶ 07 338 0 II. L­u chuyÓn tiÒn tÖ tõ ho¹t ®éng ®Çu t­ 30 1. Thu håi tõ c¸c kho¶n ®Çu t­ vµo ®¬n vÞ kh¸c 21 121;128;221 0 2. Thu do b¸n TSC§ 23 711 0 3. §Çu t­ vµo c¸c ®¬n vÞ kh¸c 24 121;128;221 0 4. Mua TSC§ 25 211;213 0 III. L­u chuûen tiÒn tõ ho¹t ®éng tµi chÝnh 40 1. TiÒn thu do ®i vay 31 311 0 2. TiÒn thu do c¸c chñ së h÷u gãp vèn 32 411 0 3. TiÒn thu tõ l∙i tiÒn göi 33 515 0 4. TiÒn ®∙ tr¶ nî vay 34 311 0 5. TiÒn ®∙ hoµn vèn cho c¸c chñ së h÷u 35 411 0 6. L∙i ®∙ tr¶ cho c¸c nhµ ®Çu t­ doanh nghiÖp 36 414;431 0 L­u chuyÓn tiÒn thuÇn 50 B¸o c¸o l­u chuyÓn tiÒn tÖ Bảng này sử dụng các công thức sau: Ô E6 nhập công thức mảng sau: {=SUM(IF(LEFT(NK_TK_Co,3)="511",1,0)*IF(LEFT(NK_TK_No,3)="111",1,0)*NK_PS_ Co)+SUM(IF(LEFT(NK_TK_Co,3)="512",1,0)*IF(LEFT(NK_TK_No,3)="111",1,0)*NK_P S_Co)+SUM(IF(LEFT(NK_TK_Co,4)="3331",1,0)*IF(LEFT(NK_TK_No,3)="111",1,0)*N K_PS_Co)} Ô E7 nhập công thức mảng sau: {=SUM(IF(LEFT(NK_TK_Co,3)="131",1,0)*IF(LEFT(NK_TK_No,3)="111",1,0)*NK_PS_ Co)+SUM(IF(LEFT(NK_TK_Co,3)="138",1,0)*IF(LEFT(NK_TK_No,3)="111",1,0)*NK_P S_Co)+SUM(IF(LEFT(NK_TK_Co,3)="136",1,0)*IF(LEFT(NK_TK_No,3)="111",1,0)*NK _PS_Co)} Từ ô E9 đến ô E11 nhập công thức mảng sau: {=SUM(IF(LEFT(NK_TK_No,3)=LEFT(C9,3),1,0)*NK_PS_No)}. Copy công thức này s