Bài giảng Tin học quản lý - ThS. Dương Trần Đức

173 trang phuongnguyen 2190

Download

Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Tin học quản lý - ThS. Dương Trần Đức", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

bai_giang_tin_hoc_quan_ly_ths_duong_tran_duc.pdf

Nội dung text: Bài giảng Tin học quản lý - ThS. Dương Trần Đức

HỌC VIỆN CÔNG NGHỆ BƯU CHÍNH VIỄN THÔNG - - - - - - - - - - - - - - BÀI GIẢNG TIN HỌC QUẢN LÝ Biên soạn : Ths. DƯƠNG TRẦN ĐỨC Ks. PHẠM HÙNG THẾ Lưu hành nội bộ HÀ NỘI - 2006
LỜI NÓI ĐẦU Ngày nay, tin học đang được ứng dụng vào hầu hết các lĩnh vực của cuộc sống, trong đó công tác quản lý là một trong những lĩnh vực được thừa hưởng thành quả của sự phát triển CNTT nhiều nhất. Hầu hết các công việc trước đây được thực hiện bằng tay, thực hiện trên giấy, được tính toán thủ công, thì nay đã có thể được thực hiện dễ dàng, tiện lợi và chính xác hơn nhờ các ứng dụng tin học. Đối với các hệ thống quản lý phức tạp thì cần phải có các chương trình ứng dụng riêng biệt, được xây dựng dành riêng cho các hệ thống này, ví dụ như các chương trình kế toán, hệ thống quản lý đào tạo của 1 trường đại học, quản lý ngân hàng, kho bạc v.v. Đối với các hệ thống không quá phức tạp (ví dụ bảng lương, chấm công .v.v), người ta thường sử dụng các chương trình ứng dụng được xây dựng sẵn để quản lý, như Word, Excel, Foxpro, Access, v.v. Cuốn giáo trình “Tin học quản lý” giới thiệu với bạn đọc các chức năng nâng cao của hai ứng dụng tin học được dùng phổ biến và hiệu quả nhất hiện nay cho công tác quản lý, đó là ứng dụng bảng tính Microsoft Excel và hệ quản trị cơ sở dữ liệu Microsoft Access. Đây là hai ứng dụng cốt lõi và được ưu chuộng của bộ chương trình ứng dụng tin học văn phòng (Office) do Microsoft phát triển. Trải quan nhiều phiên bản (hiện nay là phiên bản chạy trên Windows XP), Excel và Access đã có rất nhiều cải tiến, được tăng cường thêm các chức năng, và ngày càng tiện lợi, hiệu quả, được cài đặt trên hầu hết các máy sử dụng hệ điều hành Windows. Phần 1 trình bày về Excel nâng cao, bao gồm việc sử dụng các chức năng nâng cao, các hàm nâng cao, các công cụ phân tích dữ liệu v.v. Excel tương đối dễ sử dụng, các ứng dụng của Excel cũng rất rộng rãi, từ đơn giản đến phức tạp, tuy nhiên không đáp ứng được đối với các bài toán cần phải quản lý nhiều dữ liệu và cần các tính toán linh hoạt. Trong khi lớp các ứng dụng của Excel rộng và không quản lý nhiều dữ liệu thì Access có tầm ứng dụng hẹp hơn, nhưng có thể đáp ứng cho các ứng dụng phức tạp hơn, quản lý dữ liệu nhiều hơn và tính toán linh hoạt hơn. Thực chất, Access là một hệ quản trị CSDL quy mô vừa, vì vậy việc sử dụng Access thiên về lập trình và yêu cầu người sử dụng phải có một số kỹ năng cơ bản cũng như các khái niệm về lập trình và CSDL. Phần 2 nhắc lại các khái niệm cơ bản của Access và trình bày về phương pháp xây dựng một số ứng điển hình trên Access. Cuối mỗi phần là các bài tập và gợi ý hoặc hướng dẫn giải. Mặc dù đã rất cố gắng trong quá trình biên soạn, song không tránh khỏi các thiểu sót, nhóm tác giả rất mong nhận được ý kiến đóng góp của bạn đọc để tài liệu được hoàn thiện hơn. Cuối cùng xin gửi lời cảm ơn tới các đồng nghiệp đã giúp đỡ nhóm biên soạn hoàn thành cuốn tài liệu này. Nhóm tác giả
Phần 1: Sử dụng Excel trong quản lý PHẦN 1 SỬ DỤNG EXCEL TRONG QUẢN LÝ GIỚI THIỆU Như đã biết, Excel là ứng dụng bảng tính rất thông dụng chạy trên môi trường Windows. Rất nhiều người đang sử dụng Excel, nhưng hầu hết chỉ sử dụng các tính năng cơ bản như tạo các bảng tính đơn giản có trình bày theo hàng, cột, với các phép tính cơ sở như cộng tổng, tính trừ, nhân, chia, trung bình v.v. Tuy nhiên, Excel là một ứng dụng rất đa năng, không những dễ dàng cho phép người dùng thể hiện các bài toán đơn giản 1 cách nhanh chóng mà còn cho phép người dùng sử dụng các chức năng tính toán và phân tích rất mạnh nhằm quản lý các hệ thống tính toán tương đối phức tạp. Phần này giới thiệu cho bạn đọc những chức năng nâng cao của Excel như tạo tệp mẫu, macro, bảo mật v.v., các hàm nâng cao dùng trong các bài toán phức tạp như hàm if, hàm countif, lookup v.v., các hàm lồng nhau, và cách sử dụng các công cụ phân tích dữ liệu trong Excel như Goal seek, Solver v.v. Việc sử dụng các hàm nâng cao cho phép thực hiện các phép tính phức tạp, tạo các lệnh điều kiện, rẽ nhánh, tham chiếu dữ liệu v.v., tạo ra các kết quả dưới các định dạng mong muốn. Đặc biệt, việc cho phép sử dụng các hàm lồng nhau (kết quả của hàm này là tham số cho hàm kia) tạo cho Excel một khả năng lập trình khá mạnh và tiện lợi. Tuy nhiên, dù được hỗ trợ khả năng lập trình và các hàm khá mạnh, Excel vẫn chỉ là 1 chương trình ứng dụng, do vậy khả năng quản lý dữ liệu rất hạn chế, không quản lý được các hệ thống có lượng dữ liệu lớn và cần đến khả năng lập trình mạnh. Phần 2 sẽ giới thiệu với bạn đọc 1 công cụ quản trị dữ liệu mạnh hơn nhiều, chuyên sử dụng cho các ứng dụng quản lý dữ liệu, đó là Microsoft Access. Mục tiêu Kết thúc phần này, sinh viên cần: - Nắm được cách sử dụng các chức năng nâng cao của Excel như tạo tệp mẫu, tạo và sử dụng macro, tạo chú thích, bảo mật v.v. Áp dụng các chức năng này trong các công việc thích hợp. - Hiểu và sử dụng được các hàm nâng cao trong Excel, sử dụng được các hàm thao tác trên các kiểu dữ liệu đặc biệt, có nhiều tham số, các kiểu tham số đặc biệt, các hàm lồng nhau v.v. để giải quyết các bài toán quản lý phức tạp. - Có thể sử dụng các công cụ phân tích dữ liệu như Data tables, Solver, Pivot tables v.v. để phục vụ cho các công việc như lựa chọn phương án, rút trích dữ liệu có ích từ các dữ liệu ban đầu v.v. Cách trình bày Phần này được trình bày theo dạng phân tích cú pháp, cách sử dụng các chức năng, các hàm, các công cụ, kèm theo là các ví dụ đặc trưng được diễn giải cụ thể. Cách trình bày này giúp cho 5
Phần 1: Sử dụng Excel trong quản lý bạn đọc có thể nhanh chóng nắm bắt được các kiến thức trong phần này và có thể áp dụng cho các bài tập tương tự. Cuối phần có các bài tập tổng hợp cùng hướng dẫn giải, nhằm hệ thống lại các kiến thức trong phần, và tạo các ví dụ minh họa cho phần trình bày ở trên. Các bài tập được chọn lọc sát với nội dung kiến thức của phần, đồng thời cũng giúp sinh viên mở rộng thêm kiến thức. Phần cuối là tóm tắt nội dung và các bài tập ôn tập dưới dạng câu hỏi trắc nghiệm hoặc bài tập tự giải. Phương pháp học Để học phần này một cách hiệu quả, sinh viên nên học theo cách sau: - Đọc kỹ và hiểu cách giải thích của phần lý thuyết hoặc hướng dẫn. Tìm hiểu ý nghĩa của phần giải thích lý thuyết này. Cố gắng nắm được các ví dụ minh họa. - Suy nghĩ, tìm cách mở rộng các ví dụ minh họa. Nghĩ ra các ví dụ tương tự để thực hiện. - Đối với các bài tập tổng hợp, đọc kỹ yêu cầu bài toán, hiểu được yêu cầu, cố gắng tự tìm ra cách giải tốt nhất. Đọc hướng dẫn giải, so sánh với cách làm của mình (nếu tự nghĩ ra). - Đọc phần tóm tắt để tổng hợp kiến thức. Làm các bài tập ôn tập, đọc lại, nghiên cứu lại những phần kiến thức chưa nắm vững. Yêu cầu kiến thức trước khi học phần này Để hiểu được các kiến thức trong phần này, bạn đọc cần có các kiến thức cơ sở về tin học, nắm được cách sử dụng Excel và các hàm cơ bản trong Excel. Ngoài ra, cần có các kiến thức cơ sở về lập trình và quản lý dữ liệu, có kỹ năng phân tích bài toán. 6
Phần 1: Sử dụng Excel trong quản lý CHƯƠNG 1: SỬ DỤNG CÁC CHỨC NĂNG NÂNG CAO 1.1 TẠO VÀ SỬ DỤNG CÁC TỆP MẪU (TEMPLATE) Trong công việc hàng ngày, nhiều khi người dùng có nhu cầu sử dụng một định dạng file Excel theo một “mẫu” nào đó có định dạng và các cách trình bày dữ liệu biết trước, ví dụ như các hóa đơn gửi khách hàng, các báo cáo tài chính v.v. Nếu mỗi lần người dùng tạo một bảng tính mới, họ lại phải làm lại mọi thao tác định dạng và trình bày lại dữ liệu thì sẽ mất rất nhiều thời gian và chưa chắc định dạng đó đã giống như định dạng mà họ mong muốn hoặc định dạng của file đã làm lần trước. Chính vì vậy, để tiết kiệm thời gian và công sức cho người sử dụng, Microsoft đã cung cấp cho người dùng Excel một công cụ gọi là “tệp mẫu”. Tệp mẫu trong Excel là một file có phần mở rộng là “.xlt”. Đây là một file chứa các thông tin về định dạng, các cách trình bày, bố trí dữ liệu trong một bảng tính theo một khuôn dạng mà người sử dụng mong muốn. Các thông tin trên thường bao gồm, kiểu chữ, cỡ chữ, màu chữ, các header, footer, các công thức v.v. và các tham số định dạng khác. 1.1.1 Tạo 1 tệp mẫu Để tạo một tệp mẫu, thực hiện các bước như sau: - Tạo một file Excel mới. Thực hiện các thao tác định dạng và trình bày dữ liệu mà ta mong muốn tệp mẫu sẽ lưu giữ. - Sau khi đã thực hiện xong toàn bộ các thao tác định dạng, ghi lại tệp mẫu này bằng cách chọn Menu File > Save As. - Trong hộp thoại Save As, đặt tên cho tệp mẫu vào ô “File Name”. Trong ô “Save as type”, click chuột vào hộp chọn và chọn kiểu file muốn ghi là “Template”. Ví dụ: Tạo một tệp mẫu đơn giản với các định dạng mong muốn như sau: - Kiểu chữ: Font chữ là Arial, cỡ chữ là 13. - Kiểu số: Có 2 chữ số phần thập phân, ngăn cách hàng nghìn là dấu phẩy “,”. Số âm sẽ có dạng dấu trừ “– “ đứng trước số. Các bước tạo tệp mẫu trên như sau: - Khởi động Excel. Khi đó Excel sẽ tự động tạo ra cho ta một bảng tính mới ngầm định là Book1. - Từ menu của chương trình, chọn Format > Style. - Khi đó Excel sẽ mở ra một cửa sổ, cho phép định nghĩa lại định dạng cho từng kiểu dữ liệu mà Excel hỗ trợ: 7
Phần 1: Sử dụng Excel trong quản lý - Click chuột vào nút lệnh Modify để sửa đổi định dạng cho kiểu dữ liệu mong muốn: - Ở ví dụ này, chúng ta mong muốn thiết lập định dạng cho Font chữ và kiểu số. Vì vậy ở hộp chọn “Category”, lựa chọn lần lượt các mục là General và Number để thiết lập các định dạng mong muốn cho từng mục này. Sau khi đã thực hiện xong mọi thao tác, bấm nút lệnh OK để ghi lại các thiết lập và thoát khỏi hộp thoại này. - Ở cửa sổ style, bấm OK để kết thúc việc thiết lập định dạng cho tệp mẫu. Như vậy, đến đây chúng ta đã tạo được các định dạng mong muốn. Việc cuối cùng và rất quan trọng là phải “nhờ” Excel ghi lại các định dạng đó thành 1 tệp mẫu để lần sau có thể sử dụng lại chúng. Để làm được việc này, từ menu File, chọn Save As. Khi đó, Excel sẽ mở một cửa sổ “Save as” để chúng ta đặt tên và ghi lại tệp mẫu vừa tạo. - Trong mục “File name”, đặt tên tùy ý cho tệp mẫu. Trong mục “Save as type”, click chuột vào hộp chọn để chọn kiểu file muốn ghi lại là Template. Sau đó, click chuột vào nút lệnh “Save” để ghi lại tệp mẫu. 8
Phần 1: Sử dụng Excel trong quản lý Lưu ý: Một cách ngầm định, Excel sẽ ghi tệp mẫu vào thư mục “Templates” trong thư mục mà Excel được khởi động. Ta có thể tùy ý lựa chọn thư mục mong muốn lưu bằng cách click chuột vào mục “Save in” ở phía trên cùng của cửa sổ “Save as”. Sau đó có thể chọn thư mục mong muốn thay vì thư mục ngầm định của Excel. Như vậy ta đã có một tệp mẫu chứa tất cả các định dạng và cách trình bày, bố trí dữ liệu v.v., và có thể sử dụng tệp này cho lần sau. 1.1.2 Sử dụng tệp mẫu Bây giờ chúng ta sẽ tìm hiểu cách tạo một file Excel mới áp dụng mẫu đã định nghĩa trước. Để làm được điều này, sau khi đã khởi động Excel, từ menu chính của chương trình, chọn File > New hoặc bấm vào biểu tượng “New” phía dưới menu “File”. Khi đó, Excel sẽ mở một cửa sổ có tên “New workbook” như sau: Cửa sổ “New workbook” có hai vùng cho ta lựa chọn. Vùng thứ nhất là “New”. Vùng này cho phép ta có thể tạo một trang tính mới hoàn toàn với lựa chọn “Blank workbook” hoặc có thể lựa chọn “From existing workbook” để tạo bảng tính mới từ một file Excel đã có trước. 9
Phần 1: Sử dụng Excel trong quản lý Vùng lựa chọn thứ 2 chính là vùng mà chúng ta đang muốn sử dụng, đó là “Templates”. Như đã nói ở trên, chúng ta đã có sẵn một mẫu được định nghĩa và được lưu trữ đâu đó trong máy tính của mình và chúng ta muốn áp dụng những gì đã làm trong tệp mẫu vào tệp mới mà chúng ta đang muốn tạo ra. Do đó, chọn lựa chọn “On my computer” để Excel giúp ta tìm đến tệp mẫu đã có sẵn. Nếu ta sử dụng lựa chọn này, Excel sẽ mở cửa sổ “Templates” để ta tìm đến tệp mẫu đã tạo: Như đã trình bày ở trên, khi lưu trữ tệp mẫu thì Excel ngầm định sẽ lưu trữ nó trong thư mục “Templates” mà bộ MS Office được cài đặt. Do đó, khi cửa sổ “Templates” hiện ra, ta có thể dễ dàng chọn được mẫu muốn áp dụng cho bảng tính mới của mình. Sau khi chọn xong tệp mẫu bằng cách click chuột lên tên tệp của mình (giả sử chúng ta có tệp mẫu tên “My Template”), bấm OK để Excel mở ra một trang làm việc mới. Khi đó trang làm việc này sẽ được áp dụng các định nghĩa về dữ liệu, cách trình bày dữ liệu cũng như khuôn dạng dữ liệu đã được thực hiện trên tệp mẫu “My Template”. Khi hoàn thành việc xây dựng tệp mới và ghi lại tệp này lên ổ đĩa của máy tính, Excel sẽ tự động bật chế độ “Save as” cho phép ghi ra một file mới để mọi thay đổi không ảnh hưởng tới tệp mẫu ban đầu. Lựa chọn tên mong muốn cho tệp vừa được tạo ra và chọn Save để ghi lại. 1.2 MACRO 1.2.1 Khái niệm macro Một Macro trong Excel là một tập hợp các lệnh cho phép người dùng yêu cầu Excel thực hiện một số lệnh hay một số hành động theo nhu cầu. Macro thực sự là một công cụ hữu ích cho 10
Phần 1: Sử dụng Excel trong quản lý người dùng mỗi khi phải thực hiện lặp đi lặp lại một vài thao tác phức tạp nhờ khả năng ghi lại các thao tác và lần sau, mỗi khi cần làm lại những thao tác đó chỉ cần gọi đến Macro. Xây dựng Macro có nhiều mức, từ đơn giản đến phức tạp. Các Macro phức tạp cho phép thực hiện các tác vụ khó, yêu cầu người dùng phải có kiến thức lập trình nhất định (Visual Basic Macro). Các Macro đơn giản có thể được thiết lập khá dễ dàng nhờ công cụ được Excel cung cấp, và không cần đến kiến thức về lập trình. Với các Macro loại này, có một cách đơn giản nhất để tạo một Macro mới là yêu cầu Excel ghi lại những hành động mà người dùng thực hiện và lưu trữ nó dưới dạng một Macro. Những hành động mà người dùng cần ghi lại có thể là 1 hoặc nhiều lệnh hoặc là sự kết hợp của các lệnh mà Excel có thể thực thi được. Ngoài tính năng có thể tái sử dụng, Macro còn tỏ ra rất mềm dẻo trong sử dụng vì chúng ta hoàn toàn có thể sửa đổi mỗi khi cần sử dụng một vài thao tác mới mà lần sử dụng trước chưa có. 1.2.2 Tạo macro Có nhiều cách để tạo một Macro trong MS Excel, tuy nhiên mục tiêu của môn học chỉ giới thiệu cách tạo và sử dụng Macro một cách căn bản. Do vậy, phần này chỉ giới thiệu một cách đơn giản nhất để người dùng ở mọi trình độ đều có thể làm việc được với Macro. Như đã nói ở trên, cách đơn giản nhất để tạo một Macro trong MS Excel đó là yêu cầu Excel ghi lại các thao tác mà người dùng có nhu cầu sử dụng nhiều lần dưới dạng một Macro. Các bước để thực hiện công việc này như sau: - Chọn menu Tools > Macro > Record New Macro để hiển thị hộp thoại tạo Macro như sau: - Trên vùng “Macro name”, đặt tên cho Macro mong muốn tạo ra. - Trong vùng “Description”, Excel cung cấp một mô tả ngầm định cho mọi Macro. Nếu muốn thay đổi, ta có thể thay đổi mô tả cho Macro của mình. - Để bắt đầu ghi, click chuột vào nút lệnh OK và bắt đầu thực hiện các lệnh mong muốn (Ví dụ thực hiện thay đổi định dạng dữ liệu, ghi file, mở file mới v.v.). Excel sẽ tự động ghi lại các lệnh đó cho đến khi kết thúc ghi bằng cách chọn menu Tools > Macro > Stop Recording hoặc click chuột vào “Stop Recording” trên thanh công cụ “Stop Recording” 11
Phần 1: Sử dụng Excel trong quản lý 1.2.3 Sử dụng macro Như vậy, một Macro đã được tạo ra và lưu giữ toàn bộ những thao tác vừa thực hiện. Mỗi khi cần lặp lại những thao tác này, thay vì phải thực hiện lại toàn bộ các thao tác đó, ta chỉ việc gọi Macro trên. Để gọi Macro, đầu tiên chọn menu Tools > Macro > Macros để mở hộp chọn các Macro được lưu trữ bởi Excel. Lựa chọn Macro mong muốn sử dụng và click chuột vào nút lệnh “Run” để thực thi Macro. Ngoài cách thực thi Macro từ menu, Excel còn hỗ trợ một phương pháp gọi Macro “nhanh” bằng cách gán cho mỗi Macro một “phím nóng” (shortcut key) hay tạo một nút lệnh để mỗi khi cần thực thi Macro, người dùng chỉ việc bấm vào nút lệnh đó. 1.2.3.1 Thực thi Macro với phím nóng Các bước để gán 1 phím nóng cho một Macro được thực hiện như sau: - Chọn menu Tools > Macro > Macros. - Trên hộp thoại Macro, lựa chọn Macro mong muốn gán phím nóng và click chuột vào nút lệnh “Options”. Khi đó cửa sổ tùy chọn của Macro sẽ xuất hiện. - Lựa chọn phím muốn sử dụng để gọi nhanh Macro của mình. Giả sử ta chọn phím “m”. Khi đó trong bảng tính Excel đang sử dụng, mỗi khi ta cần gọi Macro thì thay vì chọn từ Menu, ta chỉ cần bấm tổ hợp phím “Ctrl + m”. - Click OK để xác nhận sự thay đổi và đóng cửa sổ tùy chọn của Macro. 12
Phần 1: Sử dụng Excel trong quản lý 1.2.3.2 Thực thi Macro với nút lệnh Để thực thi một Macro bằng cách bấm một nút lệnh, trước hết chúng ta cần phải tạo ra một nút lệnh và gắn cho nó với Macro. Các bước để thực hiện công việc này như sau: - Từ menu chính của Excel, chọn menu View > Toolbars > Forms để hiển thị thanh công cụ Forms. - Click chuột vào biểu tượng của nút lệnh “Button” sau đó click tiếp chuột vào nơi muốn đặt nút lệnh trên trang Excel. - Khi nhả chuột ra, hộp thoại “Assign Macro” sẽ xuất hiện cho phép gắn nút lệnh vừa tạo với một Macro được tạo ra từ trước. - Click chuột để chọn Macro muốn gọi (ví dụ Macro1) khi nút lệnh được bấm rồi chọn “OK” để kết thúc. Lưu ý: Ta cũng có thể tạo mới 1 Macro và gắn cho nút lệnh thay vì chọn một Macro có sẵn bằng cách chọn nút “Record” để Excel ghi tạo một Macro mới. Việc thực hiện tạo mới một Macro đã trình bày ở trên. Như vậy, mỗi khi cần gọi Macro, ta chỉ việc click chuột vào nút lệnh vừa tạo ra và Macro sẽ được gọi thực thi. Để giúp người dùng tạo ra một nút lệnh thân thiện, Excel cho phép người dùng thay đổi tên của nút lệnh. Điều này đặc biệt hữu ích vì mỗi khi nhìn vào tên của nút lệnh chúng ta có thể biết nội dung mà nó sẽ thực hiện. Các bước tiến hành thay đổi tên của nút lệnh như sau: - Click chuột phải lên nút lệnh muốn đổi tên, chọn “Text Edit” từ menu đẩy xuống. 13
Phần 1: Sử dụng Excel trong quản lý - Thay đổi tên nút lệnh thành tên mong muốn. - Click chuột ra khỏi phạm vi của nút lệnh để thoát khỏi chế độ thay đổi tên cho nút lệnh. Ngoài ra, Excel còn cho phép chúng ta thiết lập định dạng cho nút lệnh. Để thiết lập định dạng cho nút lệnh, tiến hành các bước sau: - Click chuột phải lên nút lệnh muốn thiết lập định dạng, chọn menu “Format Control” từ menu đẩy xuống để mở hộp thoại “Format Control”: - Lựa chọn tab tương ứng với mong muốn thiết lập định dạng và thiết lập các giá trị mong muốn rồi bấm OK để ghi lại các thay đổi (ví dụ có thể thiết lập phông chữ, kích thước, màu, v.v. cho nút lệnh). 1.3 CÁC CHỨC NĂNG BẢO MẬT VÀ KIỂM SOÁT 1.3.1 Sử dụng chú thích cho dữ liệu trong Excel Chức năng tạo chú thích (comments) của Excel cho phép người dùng đưa vào các chú thích cho dữ liệu trong bảng tính. Các chú thích được sử dụng khi người dùng muốn đưa vào những giải thích thêm cho 1 ô dữ liệu mà không muốn đưa trực tiếp vào bảng tính dưới dạng dữ liệu. Việc sử dụng chú thích cũng làm cho bảng dữ liệu “gọn ghẽ” hơn, vì thông thường các chú thích này sẽ được ẩn đi và chỉ khi người dùng chọn thì các chú thích mới hiện ra. Các chú thích này rất hữu ích trong trường hợp bảng tính có nhiều người sử dụng. Khi đó, người tạo dữ liệu có thể thêm các chú thích để giải thích thêm cho những người đọc khác. Chú thích cũng rất cần thiết khi người dùng muốn tự mình lưu lại các giải thích cho các dữ liệu của mình tránh trường hợp bị quên khi làm việc với quá nhiều dữ liệu. 1.3.1.1 Tạo các chú thích Để tạo chú thích cho 1 ô dữ liệu, thực hiện các bước sau: - Chọn ô cần thêm chú thích sau đó click chuột phải để hiện ra menu đẩy xuống. Chọn Insert Comment từ menu đẩy xuống (hoặc chọn menu Insert > Comments). 14
Phần 1: Sử dụng Excel trong quản lý - Cửa sổ thêm chú thích hiện ra, cho phép người dùng đưa vào chú thích bằng cách gõ trực tiếp từ bàn phím. - Sau khi gõ xong chú thích, click chuột vào chỗ bất kỳ bên ngoài vùng chú thích, cửa sổ chú thích sẽ ẩn đi, chỉ hiện ra 1 dấu hiệu là 1 tam giác màu đỏ ở góc trên phải của ô, cho biết ô dữ liệu đó có chú thích. 1.3.1.2 Xem chú thích Để xem chú thích, di chuột vào vùng tam giác màu đỏ ở góc trên phải của ô. Dừng chuột ở đó vài giây, chú thích sẽ hiện ra. 1.3.1.3 Xóa hoặc sửa chú thích Để xóa hoặc sửa chú thích của 1 ô dữ liệu, chọn ô dữ liệu đó và click chuột phải để hiện ra menu đẩy xuống và chọn Edit Comments hoặc Delete Comments. 1.3.2 Thẩm định dữ liệu nhập Chức năng thẩm định dữ liệu nhập (data validation) của Excel cho phép người dùng đưa vào các phép kiểm tra dữ liệu nhập xem có thỏa mãn ràng buộc mong muốn không, ví dụ kiểm tra xem dữ liệu có thuộc loại dữ liệu định trước không, hoặc kiểm tra xem dữ liệu số có nằm trong khoảng quy định không, hoặc độ dài của dữ liệu xâu ký tự có vượt quá độ dài cho phép không v.v. Chức năng thẩm định dữ liệu nhập rất hữu ích trong trường hợp người dùng phải làm việc với số lượng nhiều dữ liệu, khó có thể kiểm tra dữ liệu một cách thủ công bằng mắt thường, hoặc với dữ liệu quan trọng, cần có độ chính xác cao, mà sự nhầm lẫn có thể gây ra những hậu quả đáng tiếc, ví dụ như bảng dữ liệu bán hàng, bảng lương, bảng chấm công v.v. 1.3.2.1 Thiết lập các phép thẩm định dữ liệu cho 1 ô Để thiết lập quy tắc thẩm định dữ liệu cho 1 ô, click chọn ô đó, sau đó chọn menu Data > Validation. Cửa sổ Data Validation hiện ra cho phép người dùng đưa vào các quy tắc thẩm định dữ liệu. 15
Phần 1: Sử dụng Excel trong quản lý Trong cửa sổ này, trên tab Settings, hộp chọn thả xuống Allow cho phép ta thiết lập loại quy tắc thẩm định. Có các loại quy tắc sau: - Any value: Là giá trị mặc định của ô, cho phép đưa vào giá trị bất kỳ - Whole number và Decimal: Chỉ cho phép nhập các giá trị số. Khi chọn lựa chọn này, Excel hiện thêm các ràng buộc cho giá trị số như sau: Hộp lựa chọn thả xuống Data cho phép ta lựa chọn ràng buộc cho dữ liệu. Ví dụ ta có thể lựa chọn các toán tử between (nằm giữa 2 giá trị max và min), not between (không nằm giữa), equal to (bằng), not equal to (không bằng), greater than (lớn hơn), less than (nhỏ hơn), greater than or equal to (lớn hơn hoặc bằng), less than or equal to (nhỏ hơn hoặc bằng). Để đưa vào các ràng buộc mong muốn, chọn toán tử tương ứng và đưa tham số tương ứng vào các ô trên cửa sổ này. - List: Dữ liệu nhập phải nằm trong một danh sách các dữ liệu cho trước nào đó. Khi chọn lựa chọn này, Excel hiện thêm ra ô văn bản cho phép người dùng đưa vào danh sách dữ liệu. 16
Phần 1: Sử dụng Excel trong quản lý - Date: Dữ liệu nhập phải là kiểu ngày. Khi chọn lựa chọn này, Excel hiện thêm các ràng buộc tương tự như với giá trị số. - Time: Dữ liệu nhập phải là kiểu thời gian. Khi chọn lựa chọn này, Excel hiện thêm các ràng buộc tương tự như với giá trị số. - Text length: Kiểm tra độ dài của xâu ký tự nhập. Khi chọn lựa chọn này, Excel hiện thêm các ràng buộc tương tự như với giá trị số. - Custom: Do người dùng tự định nghĩa. Có thể định nghĩa ràng buộc bằng 1 công thức nào đó. Sau khi thiết lập xong các quy tắc thẩm định dữ liệu, chọn OK để quay về trang bảng tính. Khi đó, nếu người dùng nhập dữ liệu sai với các quy tắc đã thiết lập, Excel sẽ báo lỗi và hiện ra thông báo nhắc nhở. 1.3.2.2 Thiết lập thông báo giới hạn dữ liệu nhập Thông báo giới hạn nhập dữ liệu là thông báo sẽ được hiển thị khi người dùng click chọn vào ô có thiết lập giới hạn nhập. Mục đích của việc thiết lập thông báo này là giúp cho người dùng có thể biết được các quy tắc thẩm định dữ liệu đã được thiết lập cho Excel, giúp cho người dùng có thể nhập dữ liệu chính xác ngay từ đầu. Để thiết lập thông báo này, chọn tab Input Message trên cửa sổ Data Validation. Trong ô Title đưa vào tiêu đề của thông báo và trong ô Input Message đưa vào nội dung thông báo. 17
Phần 1: Sử dụng Excel trong quản lý Sau khi thiết lập xong, click OK. Như vậy, khi người dùng click chọn ô, thông báo như sau sẽ được hiển thị: 1.3.2.3 Tự định nghĩa thông báo lỗi khi nhập sai dữ liệu Thông thường, khi người dùng nhập dữ liệu sai so với các quy tắc đã được thiết lập, Excel sẽ hiển thị thông báo lỗi mặc định. Tuy nhiên, người dùng có thể tự định nghĩa thông báo lỗi nhằm hiển thị rõ hơn thông tin về lỗi cho người nhập dữ liệu, hoặc thay các thông báo lỗi bằng tiếng Việt thay vì tiếng Anh như mặc định. Để làm việc này, trên cửa sổ Data Validation, chọn tab Error Alert. Trong ô Title đưa vào tiêu đề của thông báo và trong ô Error Message đưa vào nội dung thông báo lỗi. Ngoài ra, Excel còn cho phép thiết lập phương án xử lý tình huống khi xảy ra lỗi trong hộp chọn Style. Có 3 cách xử lý, đó là Stop (dừng), Warning (cảnh báo), và Information (thông tin). Chọn cách xử lý thích hợp. Sau khi thiết lập xong, click OK. Như vậy, khi người dùng nhập sai dữ liệu, thông báo lỗi có nội dung như sau sẽ được hiển thị: 1.3.3 Bảo mật trong Excel Chức năng bảo mật trong Excel cho phép bảo vệ các dữ liệu trong bảng tính, chống các truy nhập trái phép, đồng thời cho phép người dùng truy cập dữ liệu theo các mức bảo mật khác nhau. 18
Phần 1: Sử dụng Excel trong quản lý Ngoài ra các chức năng bảo mật đa dạng của Excel còn cho phép bảo vệ các phần khác nhau của bảng tính với các mức độ khác nhau. Ví dụ, các vùng dữ liệu quan trọng có thể được bảo vệ cẩn mật, trong khi các vùng dữ liệu khác có thể không cần phải bảo vệ. Các lưu ý về bảo mật dữ liệu trong Excel - Bảo vệ các dữ liệu công thức và các dữ liệu quan trọng, không được thay đổi - Bảo vệ hoặc ẩn đi các dữ liệu nhạy cảm trong bảng tính - Thêm vào các chú thích cho các dữ liệu tổng hợp Các mức bảo mật trong Excel - Bảo vệ bằng mật khẩu - Bảo vệ bảng tính - Bảo vệ các sheet - Bảo vệ ô dữ liệu hoặc đồ thị 1.3.3.1 Bảo vệ bằng mật khẩu Để bảo vệ bảng tính khỏi các truy cập trái phép, ta có thể đặt mật khẩu cho bảng tính Excel. Có 2 mức bảo vệ bằng mật khẩu cho bảng tính: Mật khẩu đọc bảng tính và mật khẩu sửa bảng tính. Mật khẩu đọc bảng tính là mật khẩu được yêu cầu khi người dùng mở 1 bảng tính hoặc tham chiếu đến 1 ô dữ liệu trong bảng tính. Nếu người dùng không có mật khẩu này sẽ không mở được bảng tính. Mật khẩu sửa bảng tính là mật khẩu sẽ được yêu cầu khi người dùng sửa dữ liệu trong bảng tính và ghi lại bảng tính. Khác với mật khẩu mở bảng tính, nếu không có mật khẩu này, người dùng vẫn có thể mở bảng tính, xem dữ liệu, tuy nhiên không được chỉnh sửa và ghi lại dữ liệu (người dùng có thể chỉnh sửa và ghi lại với 1 tên khác). Các bước thiết lập mật khẩu cho file Excel: - Chọn menu File > Save As. Cửa sổ Save As hiện ra cho phép ghi lại file với 1 tên khác. 19
Phần 1: Sử dụng Excel trong quản lý - Trên cửa sổ này, click vào biểu tượng Tools ở góc trên bên phải. Trong menu thả xuống, chọn General Options. Cửa sổ thiết lập mật khẩu hiện ra. - Trong ô Password to open, đưa vào mật khẩu đọc bảng tính. Ô Password to modify là ô chứa mật khẩu sửa bảng tính. - Click OK sau khi đưa vào mật khẩu. Excel sẽ yêu cầu xác nhận mật khẩu thêm 1 lần trước khi kết thúc. 1.3.3.2 Bảo vệ bảng tính Chức năng bảo vệ bảng tính cho phép thiết lập các bảo vệ về cấu trúc (Structure) và kích thước cửa sổ (Window). Bảo vệ về cấu trúc sẽ ngăn cấm việc thay đổi cấu trúc bảng tính như thêm, xóa, sửa các sheet. Bảo vệ cửa sổ sẽ ngăn cấm việc thay đổi kích thước, phóng to, thu nhỏ v.v. cửa sổ Excel. Để thiết lập chức năng này, chọn menu Tools > Protection > Protect Workbook. Cửa sổ bảo vệ bảng tính hiện ra. Để bảo vệ cấu trúc hoặc cửa sổ, click chọn lựa chọn Structure hoặc Windows tương ứng. Đưa vào mật khẩu ở ô Password, sau đó bấm OK. Excel sẽ yêu cầu gõ xác nhận mật khẩu. Để bỏ chức năng bảo vệ bảng tính, chọn menu Tools > Protection > Unprotect Workbook. Excel sẽ yêu cầu cung cấp mật khẩu đã thiết lập trước đó. Nếu mật khẩu được cung cấp là đúng, Excel sẽ mở khóa bảo mật bảng tính. 1.3.3.3 Bảo vệ các sheet Chức năng bảo vệ sheet cho phép thiết lập các bảo vệ cho từng sheet trong bảng tính cùng với nội dung của tất cả các ô trong sheet này. Để thiết lập chức năng này, chọn menu Tools > Protection > Protect Sheet. Cửa sổ bảo vệ sheet hiện ra. 20
Phần 1: Sử dụng Excel trong quản lý Trên cửa sổ này, Excel cho phép người dùng đưa vào mật khẩu để mở khóa bảo vệ sheet. Tiếp theo, người dùng có thể chọn các nội dung cần bảo vệ ở danh sách lựa chọn bên dưới. Mặc định Excel chỉ cho phép chọn (bôi đen) ô dữ liệu, ngoài ra các chức năng khác như định dạng ô dữ liệu, định dạng hàng, cột, thêm cột, hàng v.v. đều bị cấm. Để cho phép các chức năng này, click chọn các lựa chọn tương ứng. 1.3.3.4 Bảo vệ các ô dữ liệu Chức năng bảo vệ ô dữ liệu cho phép khóa các ô dữ liệu hoặc ẩn đi, không cho phép hiển thị trên bảng tính. Để thực hiện bảo vệ ô dữ liệu, click chuột phải vào ô dữ liệu tương ứng và chọn Format cells Tiếp theo, trên cửa sổ hiện ra chọn Protection. 21
Phần 1: Sử dụng Excel trong quản lý Lựa chọn Locked cho phép khóa nội dung của ô dữ liệu, trong khi lựa chọn Hidden sẽ cho ẩn nội dung của ô. Chú ý rằng các lựa chọn này chỉ có tác dụng khi ta thực hiện việc bảo vệ Sheet (như đã trình bày ở trên). Do vậy, ta thấy các ô dữ liệu của Excel đều có lựa chọn mặc định là Locked, tuy nhiên nội dung của ô vẫn có thể thay đổi vì các sheet chưa được bảo vệ. Ẩn một hàng, một cột, hoặc 1 sheet trong bảng tính Để ẩn 1 hàng, 1 cột, hoặc 1 sheet trong bảng tính, lựa chọn hàng, cột, hoặc sheet tương ứng rồi chọn menu Format > Row (Column, hoặc Sheet) rồi chọn Hide. Để cho hiện ra các hàng, cột, hoặc sheet đã ẩn trước đó, chọn Unhide từ menu tương ứng. 22
Phần 1: Sử dụng Excel trong quản lý CHƯƠNG 2: SỬ DỤNG CÁC HÀM NÂNG CAO 2.1 CÁC HÀM LOGIC VÀ THỐNG KÊ THÔNG DỤNG 2.1.1 Sử dụng hàm IF Trong thực tế, khi tính toán số liệu cũng như xử lý các bài toán trong công việc, người dùng cần giải quyết một vấn đề có dạng: “Nếu dữ liệu đầu vào như thế này, thì tôi muốn đầu ra như thế kia. Ngược lại, tôi muốn có một đầu ra khác.” Với những bài toán có dạng như trên, Excel cung cấp một hàm rất hữu ích giúp người dùng xử lý một cách chính xác và hiệu quả. Đó là hàm IF mà chúng ta sẽ xem xét ngay sau đây. Để tiện cho bạn đọc nắm được cách sử dụng hàm, chúng ta xem xét một ví dụ như sau: Ví dụ: Ta có một bảng dữ liệu cuộc gọi (điện thoại) của khách hàng. Công việc cần thực hiện là tính cước cho các cuộc gọi dựa vào độ dài (tính theo giây) và đơn giá cước của cuộc gọi (tính theo hướng gọi). Yêu cầu nghiệp vụ của bài toán là: những cuộc gọi được coi là bình thường nếu nó dài hơn 6 giây, khi đó cước sẽ là độ dài cuộc gọi nhân với giá. Ngược lại, nếu cuộc gọi nhỏ hơn 6 giây thì được coi là cuộc gọi “lỗi” và khách hàng chỉ bị tính cước bằng ½ cước bình thường. Khi đó ta có một bảng tính như sau: Với bài toán như trên, có thể sử dụng hàm IF của Excel để tính toán giá trị của cột “Thành tiền”. Cú pháp của hàm IF như sau: IF( , , ). Lưu ý: Khi áp dụng vào Excel, không sử dụng các dấu >, , = (lớn hơn hoặc bằng), <= (nhỏ hơn hoặc bằng). - Giá trị trả về nếu đúng: Là giá trị mà người lập biểu thức muốn hàm IF tính toán và trả về nếu biểu thức logic ở trên là đúng. - Giá trị trả về nếu sai: Là giá trị mà hàm IF sẽ trả về nếu biểu thức logic ở trên cho kết quả sai. 23
Phần 1: Sử dụng Excel trong quản lý Quay trở lại với bài toán trên. Tiến hành áp dụng hàm IF theo cú pháp trên cho từng ô trong cột “Thành tiền”, ta sẽ có cách tính như sau: IF Thời gian liên lạc >= 6, Thành tiền = Thời gian liên lạc * Giá cước , Thành tiền = Thời gian liên lạc * Giá cước/2 Điều này được hiểu như sau: Nếu thời gian liên lạc lớn hơn hay bằng 6s thì “Thành tiền” được tính bằng thời gian liên lạc nhân với giá cước. Ngược lại, nghĩa là thời gian liên lạc nhỏ hơn 6s, thì “Thành tiền” được tính bằng thời gian liên lạc nhân với cước và chia đôi. Từ đó, ta có công thức cho các ô ở cột “Thành tiền” sử dụng hàm IF như sau: Sau khi viết hàm IF cho ô đầu tiên của cột thành tiền (Ở đây là ô F:2), áp dụng công thức tương tự cho toàn bộ các ô khác từ F:3 đến F:6 bằng cách sau: - Chọn ô F:2 (click chuột vào ô này) và di chuyển chuột đến góc dưới bên phải của ô F:2 cho đến khi con trỏ chuột chuyển thành dấu hình chữ thập thì nhấp đúp chuột. Khi đó toàn bộ các ô trong cột “Thành tiền” sẽ được áp dụng công thức giống như ô đầu tiên: Như vậy, Excel đã giúp chúng ta tính toán một cách chính xác theo đúng yêu cầu nghiệp vụ của bài toán được đặt ra. Nhìn vào bảng trên, ta có thể thấy, ở cuộc gọi đến Vinaphone (cuộc gọi thứ 2), mặc dù giá cước cho mỗi giây là 50Đ và thời gian là 4s nhưng thành tiền chỉ có 100Đ vì cuộc gọi này chỉ bị tính một nửa so với giá bình thường. 2.1.2 Hàm IF lồng nhau. Như đã nói ở trên, hàm IF là một công cụ rất hữu ích khi phải xử lý các bài toán mà dữ liệu đầu vào được “rẽ nhánh” làm 2 nhánh (nghĩa là dữ liệu chỉ có thể ở một trong hai trạng thái, cụ thể ở ví dụ trên là lớn hơn 6s hay nhỏ hơn 6s). Tuy nhiên, trong thực tế nhiều khi chúng ta lại gặp phải các bài toán mà dữ liệu đầu vào có thể được “rẽ nhánh” làm nhiều nhánh khác nhau. Một ví dụ đơn giản và phổ thông nhất là bài toán về xếp hạng học sinh. Giả sử có các mức xếp hạng học sinh dựa vào điểm trung bình chung các môn học như sau: - Nếu điểm trung bình nhỏ hơn 4 thì học sinh bị xếp hạng Yếu. - Nếu điểm trung trình từ 4.0 đến 5.0 thì xếp hạng Kém. - Nếu điểm trung bình từ 5.0 đến nhỏ hơn 6.5 thì xếp hạng Trung bình. 24
Phần 1: Sử dụng Excel trong quản lý - Từ 6.5 đến nhỏ hơn 8.0 thì xếp hạng Khá. - Từ 8.0 đến nhỏ hơn 9.0 thì xếp hạng Giỏi - Từ 9.0 thì xếp hạng Xuất sắc. Với bài toán như vậy, việc sử dụng hàm IF theo cách ở trên là không thể thực hiện được. Tuy nhiên, Excel cung cấp cho chúng ta một cách dùng các hàm IF lồng nhau để có thể thực hiện được bài toán này. Cụ thể, Excel cho phép chúng ta sử dụng một hay nhiều hàm IF khác trong một hàm IF ban đầu. Để đơn giản nhưng không mất tính tổng quát, chúng ta trở lại với ví dụ về tính cước cho khách hàng sử dụng điện thoại ở trên. Với nghiệp vụ yêu cầu tương tự như cũ, nhưng ta thêm vào một chính sách khuyến mãi. Theo đó, những khách hàng gọi nhiều (cuộc gọi có độ dài lớn hơn 30 phút = 1800s) sẽ được giảm 5% giá cước trên tổng số cước họ phải trả tính theo thời gian gọi và đơn giá cước theo số bị gọi. Khi đó có thể áp dụng việc sử dụng hàm IF lồng nhau như sau: “Nếu thời gian liên lạc nhỏ hơn 6s, cước sẽ là Thời gian liên lạc*Giá cước/2. Ngược lại, nếu thời gian liên lạc nhỏ hơn 1800s (và tất nhiên lớn hơn hoặc bằng 6s) thì cước sẽ là Thời gian liên lạc * giá cước. Cuối cùng, nếu thời gian liên lạc lớn hơn 1800s thì cước sẽ là Thời gian liên lạc * giá cước * 95% (Giảm giá 5%).” Như vậy công thức để tính cước sẽ như sau: IF(Thời gian liên lạc <6, Thành tiền = Thời gian liên lạc * Cước/2, IF(Thời gian liên lạc <1800,Thành tiền = Thời gian liên lạc* Giá cước,Thành tiền = Thời gian liên lạc* Giá cước * 95%) ) Áp dụng vào bảng dữ liệu như sau: Công thức cho ô đầu tiên của cột thành tiền sẽ là: Ở đây, chúng ta để ý đến cú pháp của câu lệnh IF. Thay vì đưa vào một công thức tính cụ thể khi giá trị kiểm tra là sai (đối số thứ 3 của hàm IF), chúng ta đưa thêm vào một hàm IF nữa. Hàm này sẽ kiểm tra giá trị của ô thời gian liên lạc để quyết định sẽ áp dung cách tính nào (tính bình thường cho cuộc lớn hơn 6s và nhỏ hơn 1800s hay tính theo cách khuyến mại cho cuộc dài hơn 1800s). 25
Phần 1: Sử dụng Excel trong quản lý Excel cho phép chúng ta có thể đưa vào số lượng tùy ý các lệnh IF lồng nhau. Như là một bài tập, các bạn hãy lập một bảng xếp hạng học sinh theo qui ước về xếp hạng dựa trên điểm trung bình như đã trình bày ở trên. 2.1.3 Hàm COUNTIF Trong thực tế công việc, người dùng rất hay gặp phải những bài toán cần sự thống kê đơn giản kiểu như: Có bao nhiêu bộ dữ liệu trong bảng tính phù hợp với một yêu cầu đặt ra?. Để giải bài toán này, Excel cung cấp cho người dùng một hàm gọi là hàm COUNTIF. Hàm này có cú pháp như sau: COUNTIF( , ) Ý nghĩa của hàm COUNTIF có thể được diễn tả như sau: Hãy đếm số lượng các ô trong “Vùng tìm kiếm” mà có giá trị giống với “Điều kiện” được đưa ra. Ở đây, “Vùng tìm kiếm” có thể là một dòng, một cột, một vùng dữ liệu gồm nhiều dòng, nhiều cột hay một vùng dữ liệu được đặt tên. “Điều kiện” có thể là giá trị số, chữ hay bất kỳ một kiểu giá trị nào mà Excel hiểu được. Ví dụ: Giả sử có một bảng điểm của một lớp như hình dưới đây. - Yêu cầu 1: Tìm số lượng người đạt điểm 10 môn Toán, Lý và Hóa học. - Yêu cầu 2: Tìm tổng số điểm 9 trong cả 3 môn học mà các học sinh đạt được. Để giải được bài toán này, chúng ta thêm vào các dòng dữ liệu như sau: - Dòng số điểm 10 môn Toán - Dòng số điểm 10 môn Vật lý - Dòng số điểm 10 môn Hóa học - Dòng số điểm 9 của cả 3 môn học. Khi đó ta sẽ có bảng dữ liệu mới như sau: 26
Phần 1: Sử dụng Excel trong quản lý Như đã trình bày ở trên, ta có thể sử dụng hàm COUNTIF để tính toán các giá trị như sau: - Với số điểm 10 môn Toán, hàm sẽ thực hiện tìm kiếm trên cột “Toán” (tức là các ô từ C2 đến C9). Điều kiện so sánh là điểm 10 tức là giá trị 10. Khi đó hàm COUNTIF để tính số điểm 10 môn toán sẽ như sau: COUNTIF(C2:C9,10). Đưa hàm này vào ô C11 của bảng tính để tính số điểm 10 cho môn Toán. - Tương tự như vậy, với môn Vật lý và Hóa học, chúng ta chỉ việc thay giá trị vùng tìm kiếm vào (Từ D2 đến D9 và từ E2 đến E9). Giá trị điều kiện vẫn giữ như cũ là 10 (điểm). - Với phép tính tổng số điểm 9, vùng tìm kiếm sẽ là cả 3 cột Toán, Vật lý và Hóa học. Do đó vùng tìm kiếm sẽ là các ô từ C2 đến E9. Sau khi áp dụng các công thức, Excel sẽ tính cho chúng ta kết quả như sau: 2.1.4 Hàm MAX Trong Excel, hàm MAX là một hàm khá đơn giản. Hàm MAX được sử dụng khi người dùng cần tính một giá trị có kiểu số lớn nhất trong một vùng dữ liệu cần tìm kiếm. Kết quả trả về của hàm MAX là chính giá trị lớn nhất trong vùng dữ liệu đó. Cú pháp của hàm MAX như sau: MAX(Number_1, Number_2, ,Number_n) 27
Phần 1: Sử dụng Excel trong quản lý Trong đó Number_1, Number_2, Number_n là các giá trị số mà chúng ta muốn tìm giá trị lớn nhất trong n số. Khi sử dụng trong Excel, các giá trị số này có thể được thay bởi một vùng các ô. Ví dụ: Ta có một bảng tính chứa điểm các môn học và điểm trung bình của các học sinh như sau: Yêu cầu của bài toán là tìm ra điểm Trung bình cao nhất. Thực tế, ta cũng có thể dùng phương pháp thủ công để làm việc này. Tuy nhiên, nếu danh sách có số lượng học sinh lớn thì cách thủ công là không phù hợp. Khi đó, chúng ta có thể sử dụng hàm MAX. Đầu tiên, ta thêm vào 1 ô ghi lại điểm cao nhất trong số các điểm trung bình trên như sau: Ở đây chúng ta thấy, vùng cần tìm kiếm giá trị điểm trung bình lớn nhất là cột F (Trung bình), từ hàng thứ 2 đến hàng thứ 8. Do đó vùng tìm kiếm sẽ là: F2:F8. Ngoài ra, một cách tương tự, chúng ta cũng có thể sử dụng hàm MAX để tìm điểm cao nhât của từng học sinh theo hàng. Ví dụ, học sinh Nguyễn Văn Đông muốn tìm xem điểm cao nhất của mình là bao nhiêu, khi đó anh ta có thể sử dụng hàm MAX để tính toán. Các điểm của Nguyễn Văn Đông được đặt trên các ô từ C4 đến E4, do đó thay vùng tìm kiếm C4:E4 vào hàm MAX ta được: 28
Phần 1: Sử dụng Excel trong quản lý 2.1.5 Hàm MIN Hàm MIN có ý nghĩa và cách sử dụng tương tự như hàm MAX. Tuy nhiên giá trị trả về của hàm MIN là giá trị nhỏ nhất trong vùng tìm kiếm. Cách sử dụng và cú pháp của hàm MIN hoàn toàn giống với hàm MAX. 2.1.6 Hàm AVARAGE AVARAGE là hàm tính giá trị trung bình của một vùng dữ liệu theo cách cộng dồn tất cả các giá trị của các ô rồi chia cho tổng số lượng các ô được cộng. Hàm AVARAGE có cú pháp tương tự như hàm MAX và MIN. Đối số của hàm AVARAGE chỉ là một vùng dữ liệu cần tính trung bình. Giá trị trả về là giá trị trung bình của vùng dữ liệu cần tính. Trở lại ví dụ trên, có một danh sách học sinh và điểm của 3 môn học. Chúng ta cần tính điểm trung bình cho từng học sinh. Công thức tính sẽ là: Trung bình = (Toán + Lý + Hóa)/3. Như vậy, thay vì dùng công thức cộng rồi chia, chúng ta có thể sử dụng hàm AVARAGE như sau: Sau khi đã tính được điểm trung bình của từng học sinh theo hàng, chúng ta có thể áp dụng hàm AVARAGE theo cột để tính điểm trung bình của từng môn học và trung bình của các điểm trung bình. Cụ thể ta có thể tính điểm trung bình của môn Toán như sau: 2.2 CÁC HÀM TÌM KIẾM VÀ THAM CHIẾU DỮ LIỆU THÔNG DỤNG 2.2.1 Hàm VLOOKUP VLOOKUP là một hàm tìm kiếm và tham chiếu dữ liệu rất quan trọng trong Excel và được sử dụng trong tính toán ở nhiều trường hợp. Để cho bạn đọc có thể dễ dàng nắm bắt được ý nghĩa và tình huống sử dụng của hàm này, chúng ta xem xét ví dụ sau trước khi xem giải thích chi tiết về hàm. Ví dụ: Giả sử cuối học kỳ, chúng ta có một danh sách điểm tổng kết của toàn bộ học sinh của lớp học và công việc của chúng ta là cần tính xem những học sinh được học bổng sẽ nhận được mức học bổng như thế nào. 29
Phần 1: Sử dụng Excel trong quản lý Khi đó chúng ta có bảng dữ liệu như sau: Trong bài toán này, có 3 mức học bổng như sau: - Mức khá: 180.000 đ. - Mức giỏi: 240.000 đ. - Mức xuất sắc: 360.000 đ. Trong bảng dữ liệu này, với mỗi học sinh, ta có thể tính ra được xem học sinh đó được xếp loại gì, dựa vào điểm trung bình của học sinh đó. Từ xếp loại của học sinh, dựa vào bảng quy định mức học bổng (cũng được trình bày trong bảng tính), ta có thể tính ra được học sinh đó được học bổng bao nhiêu hoặc có được học bổng hay không. Các đơn giản nhất để tính ra mức học bổng cho từng học sinh là xem xếp loại, sau đó nhìn xuống bảng mức học bổng, và gõ mức học bổng tương ứng với xếp loại vào ô học bổng. Tuy nhiên, cách làm này có thể gọi là cách làm thủ công, không hiệu quả, đặc biệt là đối với 1 danh sách dài các học sinh, đồng thời dễ gây ra nhầm lẫn. Để giải quyết tình huống này, Excel cung cấp cho chúng ta 1 hàm tham chiếu dữ liệu rất hiệu quả, đó là hàm VLOOKUP. Nhiệm vụ của hàm này là với mỗi giá trị của ô xếp loại, tham chiếu xuống bảng các mức học bổng để tìm xem mức học bổng tương ứng với xếp loại đó là bao nhiêu. Ví dụ, ô xếp loại là Giỏi, hàm VLOOKUP sẽ tham chiếu xuống bảng Các mức học bổng để tìm xem với xếp loại là Giỏi, thì mức học bổng sẽ là bao nhiêu. Kết quả trả về chính là kết quả của hàm VLOOKUP. Cú pháp của hàm VLOOKUP như sau: VLOOKUP( , , , Trong đó, ý nghĩa của các tham số như sau: - : Là giá trị mà chúng ta dùng để tìm kiếm trong . Đây có thể là một giá trị cụ thể, một công thức, .v.v, nhưng thông thường nó là một ô trong bảng tính. Giá trị này sẽ được so sánh với cột đầu tiên trong . Trong ví dụ trên, chính là giá trị của 1 ô trong cột “Xếp hạng” mà chúng ta dùng để tìm kiếm trong 30
Phần 1: Sử dụng Excel trong quản lý bảng “Các mức học bổng”. Ở bảng “Các mức học bổng” thì cột “Xếp hạng” của bảng này được đặt là cột đầu tiên. - : Là một danh sách các ô trong một bảng tính. Ở ví dụ trên, chính là bảng “Các mức học bổng” mà chúng ta thực hiện việc tìm kiếm mức học bổng cho từng học sinh. Lưu ý: Khi định nghĩa bảng tìm kiếm, chúng ta cần chỉ ra tọa độ tuyệt đối của các ô. Để làm được điều này, chúng ta có thể thực hiện như sau: +) Gõ trực tiếp vào địa chỉ của ô: ví dụ như $B$12 là địa chỉ tuyệt đối của ô B12. +) Dùng chuột lựa chọn vùng mà bạn định nghĩa bảng, ở ví dụ trên là vùng B12:C14. Sau đó bấm phím F4. Excel sẽ giúp ta định nghĩa địa chỉ tuyệt đối của B12:C14 thành $B$12:$C$14. - : Là một số xác định vị trí của cột trong mà chúng ta cần lấy ra. Vị trí này được tính là thứ tự của cột cần lấy so với thứ tự của cột so sánh giá trị (thường là cột đầu tiên). Hàm VLOOKUP sẽ so sánh với các giá trị trong cột đầu tiên của bảng tìm kiếm. Nếu khớp ở vị trí nào, giá trị của cột thứ trong bảng tìm kiếm tại vị trí đó sẽ được lấy ra. Ở ví dụ trên, chúng ta thấy cột so sánh giá trị trong bảng “Các mức học bổng là cột số 1. Cột cần lấy giá trị là cột mức học bổng (số 2), do đó = 2. - : Excel định nghĩa 2 cách tìm kiếm. Ngầm định (nếu người dùng không đưa vào) thì cách tìm sẽ là 1. Giá trị của có thể là 0 hoặc 1. Trong đó ý nghĩa của từng giá trị như sau: +) Nếu cách tìm là 1: Các giá trị trong phải được sắp xếp theo thứ tự tăng dần của cột so sánh (thường là cột đầu tiên). Nếu giá trị tìm kiếm nhỏ hơn phần tử đầu tiên trong thì giá trị trả về sẽ là #N/A. Nếu giá trị tìm kiếm lớn hơn phần tử cuối cùng thì giá trị trả về coi như là giá trị cuối cùng trong danh sách. Ngược lại, nếu giá trị tìm kiếm được tìm thấy ở một phần tử trong danh sách thì giá trị trả về sẽ là giá trị của ô tìm kiếm nằm cùng hàng với phần tử tìm kiếm. +) Nếu cách tìm là 0: Các giá trị trong sẽ không cần phải sắp xếp. Khi đó, nếu giá trị tìm kiếm không đúng với bất kỳ giá trị nào trong bảng thì Excel sẽ trả về giá trị lỗi là #N/A. Lưu ý: Cũng giống như các hàm khác, hàm này không phân biệt chữ hoa, chữ thường khi so sánh các giá trị có kiểu chữ. Ví dụ “Giỏi” cũng được coi là bằng với “giỏi” và “GIỎI”. Quay lại ví dụ trên, để tính được học bổng cho từng học sinh, ở cột “Học bổng”, chúng ta tiến hành lập công thức cho từng ô như sau: Xét ô đầu tiên của cột học bổng (E2). Lập công thức cho ô này như sau: =VLOOKUP(D2,$B$12:$C$14,2,0). Ý nghĩa của công thức như sau: Với giá trị ô D2, hãy tìm kiếm trong bảng tìm kiếm được giới hạn bởi vùng từ ô B12 đến ô C14 (kí hiệu bằng $B$12:$C$14), và so sánh giá trị ô D2 với các giá trị trong cột thứ nhất của bảng tìm kiếm, và lấy ra giá trị của cột thứ 2 (cột mức học bổng) của bảng ở hàng tìm được. 31
Phần 1: Sử dụng Excel trong quản lý Sau khi lập được công thức này cho ô E1, chúng ta sẽ nhận được giá trị 180,000. Bởi vì giá trị ô D2 là “Khá”, Excel sẽ tìm kiếm trong cột thứ nhất của bảng mức học bổng, thấy hàng thứ nhất có giá trị là “Khá”, bằng với giá trị ô D2. Khi đó, giá trị của cột thứ 2 trong hàng thứ nhất sẽ được lấy làm giá trị trả về cho hàm VLOOKUP, và đó là giá trị 180,000 đ. Sao chép công thức này cho các ô còn lại từ E3 đến E8, chúng ta được bảng kết quả như sau: Trong bảng dữ liệu này, chúng ta thấy kết quả học bổng của những học sinh có xếp hạng không nằm trong các mức học bổng định nghĩa ở bảng phía dưới sẽ là: #N/A. Để tránh gặp phải những giá trị này, chúng ta có thể kết hợp với hàm IF để làm cho kết quả “đẹp” hơn bằng cách trả về giá trị 0 (không đồng) cho những học sinh không đạt được học bổng mà không phải đưa thêm vào danh sách các mức học bổng giá trị 0 cho các học sinh không đạt học bổng. Cụ thể chúng ta sửa lại công thức như sau: =IF(ISERROR((VLOOKUP(D2,$B$12:$C$14,2,0))),0,(VLOOKUP(D2,$B$12:$C$14,2,0))) Ở đây chúng ta để ý một hàm là ISERROR( ). Hàm này sẽ trả về giá trị “đúng” nếu là “lỗi” và ngược lại. Như vậy, công thức trên có thể được hiểu như sau: “Nếu việc thực hiện hàm VLOOKUP mà lỗi (trả về giá trị #N/A) thì tôi thay bằng giá trị 0. Ngược lại, tức là VLOOKUP thực hiện đúng thì tôi lấy đúng giá trị VLOOKUP.” Khi đó, chúng ta có một bảng dữ liệu mới “đẹp” hơn bảng ban đầu mà không cần phải thêm dữ liệu vào bảng tìm kiếm “Các mức học bổng” như sau: 32
Phần 1: Sử dụng Excel trong quản lý Trên đây là một ví dụ đơn giản về hàm VLOOKUP. Trong thực tế, có rất nhiều bài toán quản lý liên quan đến việc phải tham chiếu, tìm kiếm dữu liệu trên nhiều bảng khác nhau, ví dụ như các bài toán về tính cước điện thoại theo tỉnh, bài toán tính giá tiền của mặt hàng bán ra v.v. Người dùng hoàn toàn có thể sử dụng hàm VLOOKUP để giải được các bài toán này. Các ví dụ này chúng ta sẽ xem xét ở phần bài tập tổng hợp. 2.2.2 Hàm HLOOKUP. Tương tự như hàm VLOOKUP, hàm HLOOKUP cũng thực hiện chức năng so sánh và tìm kiếm trên một và trả về giá trị nếu tìm thấy. Tuy nhiên, khác với hàm VLOOKUP thực hiện tìm kiếm theo cột, HLOOKUP thực hiện việc tìm kiếm theo hàng. Cú pháp của hàm HLOOKUP như sau: HLOOKUP( , , , Ý nghĩa của các tham số trong HLOOKUP tương tự như trong VLOOKUP. Chỉ có điều ở đây chúng ta tìm kiếm theo hàng nên ở VLOOKUP được thay bởi trong HLOOKUP. là một số xác định vị trí của dòng cần lấy ra giá trị so với dòng chứa giá trị so sánh (thường là dòng đầu tiên). Trở lại ví dụ tính học bổng cho học sinh ở trên, chúng ta cũng có một bảng qui định về mức học bổng, tuy nhiên chúng ta bố trí dữ liệu theo hàng như sau: Rõ ràng, chúng ta không thể sử dụng VLOOKUP để tìm kiếm giá trị mức học bổng dựa vào cách bố trí dữ liệu như thế này. Do vậy, hàm HLOOKUP được sử dụng để tính toán như sau: Tính cho ô đầu tiên của cột “Học bổng”, ô này được lập công thức như sau: = HLOOKUP(D2,$B$12:$D$13,2,0) Khi đó giá trị của ô E2 sẽ là 120000 vì mức học bổng dành cho học sinh “Khá” là 120000. Sao chép công thức trên cho tất cả các ô còn lại, chúng ta có bảng kết quả cần tính như sau: 33
Phần 1: Sử dụng Excel trong quản lý Giống như ví dụ ở trên, bạn đọc tự đưa chỉnh sửa lại công thức tính để có một kết quả “đẹp” hơn. 2.3 CÁC HÀM LÀM VIỆC VỚI XÂU KÝ TỰ 2.3.1 Hàm LEFT Cú pháp của hàm LEFT như sau: LEFT( , ) Kết quả của hàm LEFT là một chuỗi ký tự được lấy ra từ và có độ dài là tính từ bên trái sang. Trong trường hợp lớn hơn độ dài của thì toàn bộ sẽ được lấy ra. Ví dụ: LEFT("Nguyễn Văn Nam",10) sẽ cho giá trị trả về là “Nguyễn Văn”. LEFT(“Nguyễn Văn Nam”, 20) sẽ cho giá trị trả về là “Nguyễn Văn Nam”. 2.3.2 Hàm RIGHT Cú pháp: RIGHT( , ). Ngược lại với hàm LEFT, hàm RIGHT cũng trả về 1 biểu thức ký tự là “con” của đầu vào, được lấy ra nhưng tính từ bên phải sang. Cũng tương tự, nếu lớn hơn độ dài của thì toàn bộ được lấy ra. Ví dụ: RIGHT("Nguyễn Văn Nam",10) sẽ cho giá trị trả về là “ễn Văn Nam” RIGHT(“Nguyễn Văn Nam”, 20) sẽ cho giá trị trả về là “Nguyễn Văn Nam” 2.3.3 Hàm MID Cú pháp: MID( , , ). 34
Phần 1: Sử dụng Excel trong quản lý Hàm MID sẽ trả về một biểu thức ký tự được lấy ra từ , tính từ vị trí và lấy ra . Lưu ý: Trong 3 hàm LEFT, RIGHT và MID, tất cả các đối số có giá trị là số như , đều phải là các số nguyên lớn hơn 0. Ví dụ: MID(“Nguyễn Văn Nam”,8,3) = “Văn” 2.3.4 Hàm LEN Cú pháp: LEN( ) Hàm LEN trả về độ dài của Ví dụ: LEN(“Nguyễn Văn Nam”) = 14. 2.3.5 Hàm LOWER Cú pháp: LOWER( ). Hàm LOWER sẽ thực hiện việc chuyển đổi thành một chuỗi ký tự mà tất cả các chữ cái trong đều ở dạng chữ thường. Ví dụ: LOWER(“Nguyễn Văn Nam”) = “nguyễn văn nam” LOWER(“NGUYỄN VĂN NAM”) = “nguyễn văn nam” 2.3.6 Hàm UPPER Cú pháp: UPPER( ). Hàm UPPER có ý nghĩa ngược lại với hàm LOWER. Hàm này sẽ trả về một xâu ký tự mà tất cả các chữ cái trong đều ở dạng chữ hoa. Ví dụ: UPPER(“nguyễn văn nam”) = “NGUYỄN VĂN NAM” Lưu ý: Với hai hàm UPPER và LOWER, có thể có một số trường hợp ký tự trả về không phải là chữ hoa thực sự do sự không đồng bộ của bộ font tiếng Việt. 2.3.7 Hàm REPLACE Cú pháp: REPLACE( , , , ). Hàm REPLACE sẽ thực hiện việc thay thế , tính từ , với số lượng ký tự cần thay đổi là bằng xâu mới là . Ví dụ: Chúng ta có 1 biểu thức ký tự là: “Nguyễn Văn Nam” Giờ chúng ta muốn thay thế chữ “Văn” trong xâu trên thành chữ “Hùng”, khi đó ta làm như sau: REPLACE(“Nguyễn Văn Nam”,8,3,“Hùng”) = “Nguyễn Hùng Nam”. 35
Phần 1: Sử dụng Excel trong quản lý Ở đây: - 8 là vị trí bắt đầu của chữ “Văn” - 3 là số lượng các chữ cái cần thay thế, bắt đầu từ vị trí 8. - “Hùng” là biểu xâu mới cần thay thế vào xâu cũ. 2.3.8 Hàm REPT Cú pháp: REPT( , ) Hàm REPT thực hiện việc in ra một chuỗi ký tự được lặp lại của . Ví dụ: REPT(“Nguyễn”,3) = “NguyễnNguyễnNguyễn”. 2.3.9 Hàm SEARCH Cú pháp: SEARCH( , ,[ ]). Hàm SEARCH có một đối số tùy chọn là [ ]. Nếu người dùng không đưa vào tham số này, hàm SEARCH sẽ thực hiện tìm trong tính từ vị trí đầu tiên của (từ trái sang) và trả về vị trí mà xuất hiện trong nếu thấy. Ngược lại, hàm SEARCH sẽ trả về giá trị lỗi (#VALUE). Nếu người dùng đưa vào , khi đó Excel sẽ thực hiện tìm kiếm từ vị trí đó đến hết xâu và trả về vị trí xuất hiện của trong nếu tìm thấy. Ngược lại, giá trị lỗi sẽ được trả về (#VALUE) Ví dụ: Ta có một biểu thức ký tự như sau: “Đây là một biểu thức ký tự. Biểu thức này được tạo để thử hàm Search”. Giờ ta muốn tìm xâu “biểu thức” trong xâu trên. Nếu ta sử dụng hàm SEARCH như sau: SEARCH(“biểu thức”, “Đây là một biểu thức ký tự. Biểu thức này được tạo để thử hàm Search”) thì giá trị trả về sẽ là 12 vì xâu “biểu thức” xuất hiện ở vị trí thứ 12 trong xâu lớn cần tìm. Tuy nhiên, nếu chúng ta sử dụng hàm SEARCH theo cách khác: SEARCH(“biểu thức”, “Đây là một biểu thức ký tự. Biểu thức này được tạo để thử hàm Search”,20) thì giá trị trả về sẽ là 29 vì Excel tiến hành tìm từ vị trí thứ 20 của xâu trở đi. Khi đó nó sẽ gặp từ “Biểu thức” ở vị trí 29. Lưu ý: Trong khi tìm kiếm, Excel không quan tâm đến ký tự chữ thường hay chữ hoa. Do đó “biểu thức” và “Biểu thức” được hiểu là giống nhau. 36
Phần 1: Sử dụng Excel trong quản lý 2.3.10 Hàm SUBSTITUTE Cú pháp: SUBSTITUE ( , , ) Hàm SUBSTITUTE có ý nghĩa gần giống hàm REPLACE. Hàm này thực hiện việc thay thế 1 xâu ký tự trong bằng một xâu khác. Cụ thể, nếu xuất hiện trong thì sẽ được thay thế bằng . Ngược lại (tức là không xuất hiện trong ) thì sẽ vẫn giữ giá trị như cũ. Ví dụ: Có biểu thức ký tự: “Nguyễn Văn Nam” Ta muốn thay thế tên đệm “Văn” thành “Hùng”, khi đó sử dụng hàm SUBSTITUE như sau: SUBSTITUE(“Nguyễn Văn Nam”,“Văn”,“Hùng”). Khi đó kết quả trả về sẽ là “Nguyễn Hùng Nam”. 2.3.11 Hàm TRIM Cú pháp: TRIM( Hàm này trả về một biểu thức ký tự có nội dung “gần” giống với nhưng được chuẩn hóa theo cách sau: - Xóa toàn bộ các ký tự trắng (dấu cách) ở đầu và cuối của nếu có. - Nếu trong có tồn tại hai ký tự trắng thì hàm TRIM sẽ xóa bớt đi một và chỉ để lại một ký tự trắng. Ví dụ ta có xâu: “ Hôm qua tôi đi học muộn ” (Lưu ý các dấu cách ở đầu và cuối câu, các dấu cách liên tiếp trong thân câu). Khi đó hàm TRIM(“ Hôm qua tôi đi học muộn ”) sẽ trả lại một câu “chuẩn” hơn như sau: “Hôm qua tôi đi học muộn”. 2.3.12 Hàm CONCATENATE Cú pháp: CONCATENATE( , , , ) Hàm CONCATENATE thực hiện việc ghép các lại với nhau thành 1 biểu thức ký tự duy nhất sau khi đã bỏ đi các dấu cách của biểu thức ký tự đứng trước. Ví dụ: Giả sử ta có các biểu thức ký tự như sau: “Nguyễn ” (Có dấu cách ở cuối) “Văn” “Nam” 37
Phần 1: Sử dụng Excel trong quản lý Khi đó: CONCATENATE(“Nguyễn ”, “Văn”, “Nam”) sẽ trả về xâu là “NguyễnVănNam”. Nếu chúng ta muốn xâu cuối có thêm dấu cách giữa các từ, chúng ta có thể đưa trực tiếp dấu cách vào trong hàm CONCATENATE như sau: CONCATENATE(“Nguyễn ”, “ ”, “Văn”, “ ”, “Nam”). Khi đó Excel sẽ đưa các dấu cách “ ” vào trong kết quả đầu ra. Và như vậy ta sẽ được xâu: “Nguyễn Văn Nam”. 2.3.13 Toán tử & Cú pháp: & Toán tử & hoạt động giống với hàm CONCATENATE. Tuy nhiên toán tử & chỉ cho phép chúng ta nối hai biểu thức ký tự thành một biểu thức ký tự duy nhất sau khi bỏ đi dấu cách ở cuối biểu thức ký tự thứ nhất. Nếu chúng ta muốn có một dấu cách giữa 2 biểu thức ký tự thì chúng ta phải đưa dấu cách vào trong toán tử &. Ví dụ: ”Nguyễn” & “Văn” sẽ cho ta biểu thức: “NguyễnVăn” “Nguyễn” & “ ” & “Văn” = “Nguyễn Văn”. 2.4 CÁC HÀM TÀI CHÍNH (FINANCIAL) Ngoài các loại hàm thông dụng ở trên, Excel còn cung cấp một loạt các hàm để thực hiện các thao tác liên quan đến tài chính. Những hàm này sử dụng các nhân tố chung, tùy thuộc vào các giá trị sẽ được tính toán. Hầu hết các hàm này làm việc với các thao tác vay hoặc đầu tư tài chính. Để hiểu được cách làm việc của các hàm tài chính, trước hết ta xét các khái niệm sau: Giá trị hiện tại (Present Value) là giá trị của khoản vay hoặc đầu tư ở thời điểm ban đầu. Tham số này còn được gọi là vốn. Giá trị tương lai (Future Value) là giá trị của khoản đầu tư hoặc vay tại thời điểm nào đó trong tương lai (thường là thời điểm thương vụ đầu tư kết thúc hoặc khoản vay được trả). Lãi suất (Interest Rate) là giá trị phần trăm tăng hoặc giảm của khoản vay hoặc đầu tư. Đây là giá trị cố định áp dụng trong vòng đời của 1 thương vụ đầu tư hoặc cho vay. Kỳ hạn (Number Of Periods) là số lần thanh toán trong vòng đời của 1 thương vụ đầu tư hoặc cho vay. Kỳ hạn có thể được tính theo tháng hoặc năm. Chú ý rằng tham số này phải khớp với tham số lãi suất (ví dụ kỳ hạn tính theo tháng thì lái suất phải là theo tháng, kỳ hạn tính theo năm thì lãi suất phải là theo năm). Tiền trả (Payment) là số lượng tiền đóng góp cho thương vụ đầu tư hoặc vay. Đây thường là khoản tiền góp tại cùng thời điểm với lãi suất được tính Loại trả (Payment Type) ấn định thời điểm thanh toán tại đầu hoặc cuối kỳ. Thông thường, thời điểm thanh toán là cuối mỗi tháng. Ngoài các khái niệm trên, một điều quan trọng nữa là phân biệt khi nào tham số mang dấu âm và khi nào mang dấu dương. 38
Phần 1: Sử dụng Excel trong quản lý Quy tắc như sau: - Nếu là khoản tiền phải đóng, có thể là đóng góp vào tài khoản tiết kiệm hay trả lãi cho 1 khoản vay, thì khoản tiền mang dấu âm. - Nếu là khoản tiền nhận được, có thể là do vay được hoặc khoản đầu tư sinh lãi, thì mang dấu dương. Phần tiếp theo sẽ giới thiệu với bạn đọc về 3 hàm tài chính thông dụng là FV, PV, và PMT. Các hàm khác, bạn đọc có thể tham khảo trong các tài liệu nâng cao về Excel khác. 2.4.1 Sử dụng hàm FV Hàm FV dùng để tính giá trị tương lai của 1 thương vụ đầu tư, với 1 lãi suất cố định. Để hiểu được cách sử dụng hàm này, chúng ta xem xét ví dụ sau: Giả sử ta có 1 khoản tiền gửi tiết kiệm là 10 triệu, mỗi năm có lãi suất 8%. Chúng ta muốn tính xem đến cuối năm, số tiền sẽ là bao nhiêu. Lúc này phép tính rất đơn giản: = 10.000.00 + 10.000.000 x 8% = 10.000.000 x 108 % = 10.800.000 VNĐ Đây chính là giá trị tương lai của khoản tiền đầu tư gửi tiết kiệm. Tiếp theo, ta muốn biết sau 2 năm được bao nhiêu, ta có thể tính như sau: = 10.000.000 x 108% x 108% = 11.664.000 VNĐ Cứ như vậy, ta có thể tính giá trị tương lai sau nhiều năm. Tuy nhiên, trên thực tế, bài toán lại không đơn giản như vậy. Bởi vì: - Thông thường, khoản đầu tư tiết kiệm thường được tính lãi theo tháng. Mặc dù lãi suất theo năm vẫn như thế, nhưng khi lãi tháng được trả sẽ cộng vào khoản vốn trong tài khoản và tạo nên lãi chồng. - Người gửi tiết kiệm thường đóng góp 1 khoản nào đó theo định kỳ vào tài khoản tiết kiệm của mình, qua đó tăng lãi suất tiết kiệm. Trường hợp này không thể tính theo cách đơn giản ở trên, khi đó ta có thể sử dụng hàm FV của Excel để tính giá trị tương lai của khoản đầu tư. Cú pháp hàm FV như sau: FV(Rate, Periods, Payment, PresentValue,[ PaymentType]) Ý nghĩa của các tham số được giải thích như ở trên. Cụ thể, để giải quyết bài toán trên, với khoản vốn ban đầu 10 triệu, lái suất 8%/năm, khoản góp hàng tháng là 300.000 VNĐ, ta có thể tính được giá trị khoản tiết kiệm sau kỳ hạn là 24 tháng. Đầu tiên lập bảng tính với các giá trị như sau: 39
Phần 1: Sử dụng Excel trong quản lý Tiếp theo, áp dụng công thức FV cho các tham số: Trong công thức trên, ta thấy lãi suất năm được chia cho 12 thành lãi suất tính theo tháng. Các tham số Payment và Present Value mang dấu âm vì là các khoản ta phải đóng vào. Khi đó, ta sẽ có giá trị tương lai của khoản tiết kiệm là 19.508.836 VNĐ. 2.4.2 Sử dụng hàm PV Có thể nói, hàm PV là 1 hàm trái ngược với FV, dùng để tính giá trị ban đầu của khoản đầu tư hoặc vay. Cú pháp hàm PV như sau: FV(Rate, Periods, Payment, FutureValue,[ PaymentType]) Sự khác biệt trong tham số giữa hàm FV và PV là tham số Present Value được thay bằng tham số Future Value. Giả sử cũng bài toán ở hàm FV, nhưng yêu cầu mới là cần tính xem khoản đầu tư ban đầu là bao nhiêu để sau 10 năm, có được khoản tiền là 50 triệu. Ta có bảng tính: Áp dụng công thức PV để tính giá trị ban đầu như sau: 40
Phần 1: Sử dụng Excel trong quản lý Vẫn như trên, tham số lãi suất chia cho 12 để tính lãi suất tháng, kỳ hạn nhân với 12 để ra kỳ hạn tháng. Và ta có giá trị ban đầu cần phải đầu tư là 2.200.271 VNĐ. 2.4.3 Sử dụng hàm PMT Hàm PMT dùng để tính số tiền phải trả hoặc đóng góp thường xuyên cho 1 thương vụ vay hoặc đầu tư. Cú pháp hàm PMT như sau: PMT(Rate, Periods, PresentValue, FutureValue, PaymentType) Ví dụ: Để mua xe máy trả góp (giá 30 triệu), người mua phải trả trước 1 khoản tiền 10 triệu. Khoản tiền 20 triệu còn lại sẽ phải trả trong 2 năm, với lãi suất 8% 1 năm. Yêu cầu bài toán là tính ra khoản tiền mà người đó phải trả hàng tháng. Ta có thể sử dụng hàm PMT trong trường hợp này. Chú ý là tổng giá trị phải trả là 30 triêu, do đó có thể coi là giá trị hiện tại (Present Value), Khi đó, giá trị tương lai (Future Value) sẽ là 10 triệu, là khi người mua trả hết nợ (vì họ đã nộp trước 10 triệu). Cũng có thể có cách tính khác là giá trị hiện tại là 20 triệu (số tiền còn nợ), và giá trị tương lai là 0 (hết nợ). Ta có bảng tính: 41
Phần 1: Sử dụng Excel trong quản lý Áp dụng công thức PMT: Ở đây, ta lấy tổng giá tiền xe như giá trị ban đầu, và số tiền đã trả như giá trị tương lai. Giá trị ban đầu mang dấu âm vì là số tiền ta phải trả. Kết quả ta được số tiền phải trả hàng tháng là 971,212 VNĐ. 42
Phần 1: Sử dụng Excel trong quản lý CHƯƠNG 3: SỬ DỤNG CÁC CÔNG CỤ NÂNG CAO 3.1 LỌC DỮ LIỆU Trong thực tế, nhiều khi người dùng phải làm việc với một bảng tính có rất nhiều dòng dữ liệu (Excel có thể hỗ trợ một bảng tính có tối đa 65535 dòng), trong khi chỉ thực sự cần làm việc với một số lượng dòng nhất định trong tổng số dữ liệu của bảng tính. Khi đó, Excel cung cấp một công cụ gọi là lọc dữ liệu. Công cụ này cho phép người dùng có thể giới hạn số lượng dữ liệu muốn làm việc. Để dễ dàng hơn trong việc tìm hiểu về công cụ này, chúng ta xem xét một ví dụ sau: Giả sử ta có một bảng tính chứa dữ liệu về các cuộc gọi điện thoại đi quốc tế của một đơn vị. Bảng tính này như sau: Trong bảng tính trên, chúng ta có thể thấy rất nhiều cuộc gọi đến nhiều nước khác nhau. Bây giờ, nếu người dùng chỉ muốn xem và làm việc với những cuộc gọi đến Mỹ (US) hoặc một nước bất kỳ nào đó, họ có thể dùng chức năng lọc dữ liệu của Excel để làm việc này. Các bước thực hiện việc lọc dữ liệu được tiến hành như sau: - Từ menu chính của Excel, chọn Data > Filter > Auto Filter. Khi đó chức năng Filter của Excel sẽ được bật lên trên bảng tính và bảng tính sẽ chuyển sang dạng sau: 43
Phần 1: Sử dụng Excel trong quản lý - Khi đó, ở các tiêu đề của các cột sẽ xuất hiện các hộp chọn đẩy xuống. Đến đây, dựa vào nhu cầu của mình, người dùng có thể tiến hành lọc dữ liệu theo tiêu chí mong muốn. Ví dụ nếu muốn lọc ra các cuộc gọi đến Mỹ (US), click chuột vào hộp chọn ở cột “Nước gọi đến”. Khi đó ta sẽ thấy ngoài các lựa chọn mà Excel cung cấp, hộp chọn sẽ liệt kê tất cả các nước có cuộc gọi đến. Ở đây ta chọn US. - Khi đó Excel sẽ liệt kê tất cả các cuộc gọi đến US. Với chức năng này, không những tất cả các cuộc gọi đến US được liệt kê, mà ta còn có thể biết được có bao nhiêu cuộc gọi bằng cách nhìn xuống thanh trạng thái ở phía dưới của cửa sổ làm việc của Excel. Ta sẽ thấy một thông báo có dạng: “ of records found”. Trong đó chính là số bản ghi (dòng) các cuộc gọi đến US trong tổng số bản ghi (dòng) các cuộc gọi ban đầu. 44
Phần 1: Sử dụng Excel trong quản lý Bây giờ chúng ta xét thêm 1 tình huống khác. Như chúng ta đã thấy, trong bảng dữ liệu các cuộc gọi điện thoại quốc tế ở trên, có rất nhiều cuộc gọi đến Mỹ, bao gồm US, US – NYC hay US-Hawaii v.v. Với cách làm như ở trên, chúng ta chỉ có thể chọn được những cuộc gọi đến 1 địa điểm nhất định. Tuy nhiên, nhiều khi người dùng lại muốn có thông tin về toàn bộ các cuộc gọi đến Mỹ (US) mà không cần biết nó thuộc về vùng nào trên nước Mỹ. Khi đó, chúng ta có thể sử dụng chức năng lọc số liệu của Excel như sau: - Click chuột vào hộp chọn ở cột “Nước gọi đến”, sau đó lựa chọn mục “Custom”. Khi đó Excel sẽ mở một hộp thoại cho phép chúng ta định nghĩa điều kiện lọc “tùy chọn” của mình để Excel lọc giúp chúng ta: - Từ hộp thoại Custom AutoFilter, trong mục “Nước gọi đến”, click chuột vào hộp chọn đẩy xuống. Khi đó, chúng ta sẽ có các lựa chọn sau cho “Nước gọi đến”. o Equals: Bằng o Does not equal: Không bằng (khác). o Is greater than: Lớn hơn. 45
Phần 1: Sử dụng Excel trong quản lý o Is greater than or equal to: Lớn hơn hay bằng. o Is less than: Nhỏ hơn. o Is less than or equal to: Nhỏ hơn hay bằng. o Begins with: Bắt đầu bằng. o Does not begin with: Không bắt đầu bằng. o Ends with: Kết thúc bằng. o Does not end with: Không kết thúc bằng. o Containts: Chứa. o Does not containt: Không chứa. Ở ví dụ này, chúng ta muốn chọn tất cả các cuộc gọi đến Mỹ (US), bao gồm cả các cuộc gọi đến những vùng khác của Mỹ như: US - NYC, US - Hawaii v.v. Do đó có thể lựa chọn điều kiện lọc: “Nước gọi đến” bắt đầu bằng chữ US. Khi đó, điền vào hộp thoại Custom AutoFilter như sau: Sau khi đã đưa điều kiện lọc xong, bấm nút lệnh OK để Excel thực hiện việc lọc và trả về dữ liệu được lọc ra. Ngoài ra, nhìn vào hộp thoại trên, có thể thấy Excel còn cung cấp nhiều hơn một điều kiện lọc. Nghĩa là, ngoài việc lựa chọn “Nước gọi đến” bắt đầu bằng chữ US, còn có thể lựa chọn thêm 1 điều kiện nữa bằng cách lựa chọn hàng thứ 2 của hộp thoại. Trong đó, nếu click vào lựa chọn “And” thì sẽ có điều kiện “Và”, nếu chọn lựa chọn “Or” ta sẽ có điều kiện “Hoặc”. Một cách tương tự, ta cũng có thể lọc dữ liệu dựa trên các cột khác, chẳng hạn cột “Số bị gọi” ở ví dụ này. 3.2 SẮP XẾP VÀ CỘNG TỔNG, TỔNG CON (SUBTOTAL) Chức năng sắp xếp cho phép sắp lại thứ tự các hàng dữ liệu trong Excel theo một tiêu chí nào đó (ví dụ sắp theo thứ tự alphabe của tên, sắp theo chiều tăng của điểm tổng v.v.). Trong khi hàm cộng tổng chỉ cho phép tính tổng các dữ liệu số 1 cách tự động, thì chức năng tính tổng con (subtotal) còn cho phép tính các tổng theo các nhóm khác nhau. 46
Phần 1: Sử dụng Excel trong quản lý Để nắm được ý nghĩa và ứng dụng của các chức năng này, ta sẽ xem xét 1 ví dụ sau đây: Giả sử 1 cửa hàng bách hóa X cần lập 1 bảng tính để quản lý doanh số bán hàng của các nhân viên bán hàng trong cửa hàng. Các nhân viên bán hàng thuộc các bộ phận khác nhau, như Quần áo, Đồ giá dụng, Mỹ phẩm .v.v Trước hết, cần lập 1 bảng tính Excel như hình vẽ dưới: Trong bảng tính này, các dữ liệu được lưu trữ bao gồm: Họ tên nhân viên bán hàng, Bộ phận bán hàng, Doanh số tháng 1, tháng 2, tháng 3, Tổng doanh số. Cột Tổng sẽ được tính bằng tổng của 3 cột Tháng 1, Tháng 2, và Tháng 3 (sử dụng hàm SUM). Như vậy, nhìn vào bảng tính, ta có thể biết được doanh số bán của từng nhân viên trong từng tháng và tổng doanh số. Tuy nhiên, bài toán đặt ra là cần phải tính tổng doanh số bán theo từng bộ phận của tất cả nhân viên trong bộ phận đó. Để giải quyết vấn đề này, ta có thể sử dụng chức năng tính tổng con của Excel để tính các tổng theo từng nhóm. Các bước thực hiện như sau: Đầu tiên, sắp xếp lại các hàng của bảng tính theo Bộ phận. Chú ý rằng, để thực hiện việc tính tổng con chính xác, phải thực sắp xếp trên cột dữ liệu sẽ được dùng để phân nhóm, nếu không sẽ có nhiều kết quả cho mỗi nhóm. Để sắp xếp theo Bộ phận, thực hiện như sau: - Chọn 1 ô bất kỳ trong cột Bộ phận, tiếp theo chọn menu Data > Sort. Cửa sổ sắp xếp hiện ra. - Như ta thấy, trên cửa sổ này, trong lựa chọn thả xuống Sort by (sắp xếp theo), đã có sẵn tên cột Bộ phận (trường hợp muốn thay đổi, có thể chọn tại hộp chọn thả xuống để thay đổi). Điều này có nghĩa các hàng của bảng tính sẽ được sắp xếp theo cột Bộ phận. 47
Phần 1: Sử dụng Excel trong quản lý - Hai lựa chọn Ascending (tăng dần), Descending (giảm dần) ở phía sau cho phép người dùng lựa chọn sắp tăng dần hay giảm dần. - Các lựa chọn Then by cho phép người dùng chọn các cột muốn sắp xếp tiếp sau Bộ phận. Để trống nếu chỉ muốn sắp xếp theo Bộ phận. - Sau khi thiết lập xong các lựa chọn, bấm OK. Dữ liệu mới được sắp xếp như sau: - Như ta thấy, các hàng của bảng tính đã được sắp xếp lại theo Bộ phận. Việc này giúp cho các hàng có cùng Bộ phận được nhóm vào gần nhau. - Tiếp theo, chọn 1 ô dữ liệu bất kỳ trong vùng dữ liệu và chọn menu Data > Subtotals. Cửa sổ tính tổng con hiện ra như sau: 48
Phần 1: Sử dụng Excel trong quản lý - Trên cửa sổ này, lựa chọn At each change in cho phép ta chọn cột sẽ được dùng để phân nhóm tính tổng con. Thông thường Excel sẽ mặc định chọn cột đầu tiên. Với bài toán trên, ta muốn nhóm theo cột Bộ phận, do vậy phải chọn lại, bằng cách click chuột vào lựa chọn thả xuống và chọn Bộ phận thay vì Họ và tên như trên. - Tiếp theo, trong phần Use function, chọn hàm Sum là chính xác. Trong trường hợp khác, có thể chọn hàm khác (ví dụ muốn tính trung bình ta chọn hàm Avarage). - Phần Add subtotal to cho phép lựa chọn việc tính tổng con cho các cột dữ liệu nào. Hiện tại Excel đã mặc định chọn cột Tổng. Tuy nhiên, yêu cầu của bài toán cũng muốn tính tổng con theo các tháng, do vậy chọn cả các cột Tháng 1, Tháng 2, Tháng 3. - Sau khi chọn xong, cửa sổ Subtotals sẽ như sau: - Đến đây, click OK. Kết quả tính tổng con sẽ hiện ra như sau: 49
Phần 1: Sử dụng Excel trong quản lý Nhìn trên bảng kết quả, ta có thể thấy Excel thêm 1 hàng vào dưới mỗi nhóm Bộ phận và đặt tên là “Tên bộ phận + Total”. Giá trị các cột dữ liệu của hàng này là tổng giá trị các cột trong nhóm. Ở hàng cuối cùng, Excel cũng thêm vào 1 hàng Grand Total để tính tổng của tất cả các nhóm. Có thể thay đổi tên các cột Total để được bảng tính đẹp và dễ hiểu hơn. Cũng có thể thay đổi định dạng của các ô tổng để dễ nhìn hơn (ví dụ chữ đậm). Chú ý rằng, các dấu -, + ở phía bên trái của cửa sổ cho phép thu vào, mở ra các phần dữ liệu được tính tổng. 3.3 SỬ DỤNG CÔNG CỤ GOAL SEEK Công cụ Goal Seek cho phép tìm dữ liệu đầu vào đúng đắn để ra được kết quả đầu ra như mong đợi. Như chúng ta đã biết, quá trình xử lý 1 bài toán thông thường là: dữ liệu đầu vào (input) - xử lý - kết quả đầu ra (output). Đa số các trường hợp, chúng ta biết dữ liệu đầu vào, biết quá trình xử lý (thuật toán), qua đó tính được kết quả đầu ra. Tuy nhiên, trong nhiều trường hợp, chúng ta biết quá trình xử lý, và mong muốn 1 kết quả đầu ra nào đó, khi đó ta muốn biết đầu vào là bao nhiêu. Trong những trường hợp này, chúng ta có thể sử dụng công cụ Goal Seek của Excel. Có thể biểu thị công cụ Goal Seek như sau: “Đây là kết quả đầu ra tôi mong muốn, hãy thay đổi dữ liệu đầu vào để đạt được kết quả đầu ra này”. Để làm được điều này, Excel sẽ tính toán giá trị đầu vào phù hợp bằng cách “thử” cho đến khi tìm được giá trị đầu ra mong muốn. Thiết lập ô này Tới giá trị Bằng cách thay đổi ô này 50
Phần 1: Sử dụng Excel trong quản lý Goal Seek là công cụ dễ sử dụng, tuy nhiên không được linh hoạt và không giải quyết được các trường hợp phức tạp. Ví dụ: Trong một cuộc thi, các thí sinh phải thi 3 môn Toán, Lý, Hóa. Điểm tổng được tính bằng điểm trung bình của 3 điểm trên, trong đó Toán hệ số 3, Lý hệ số 2 và Hóa hệ số 1. Nếu một thí sinh đã biết cả 3 điểm thì sẽ tính được điểm trung bình: Công thức cho ô tính điểm trung bình B4: (B1*3+B2*2+B3)/6 Giả sử một thí sinh đã biết 2 điểm Lý và Hóa. Thí sinh đó cần số điểm trung bình là 7, và muốn biết điểm Toán mình cần được bao nhiêu để có điểm trung bình là 7. Khi đó, có thể dùng công cụ Goal Seek để tìm điểm Toán. Các bước áp dụng công cụ Goal Seek trong bài toán này như sau: - Sử dụng Excel tạo 1 bảng trong hình trên, với công thức cho ô tính điểm trung bình như trên - Chọn ô điểm trung bình bằng cách click chuột vào ô đó - Chọn công cụ Goal Seek bằng cách chọn Menu Tools -> Goal Seek - Đưa giá trị 7 vào ô To value - Click chuột vào ô By changing cell trên cửa sổ Goal Seek, sau đó click chuột vào ô B1. Điều này có nghĩa, khi ta sử dụng công cụ Goal Seek, giá trị của ô này (B1) sẽ được thay đổi để cho ô trung bình (B4) đạt giá trị mong muốn là 7. - Tiếp theo, click OK. Cửa sổ kết quả tìm tính toán hiện ra và ô điểm Toán sẽ thay đổi về 8. Đó là điểm mà thí sinh cần đạt được nhằm có điểm trung bình là 7. 51
Phần 1: Sử dụng Excel trong quản lý - Click OK nếu đồng ý với những tính toàn này, và click Cancel nếu muốn quay về các giá trị cũ. Chú ý: Trong ví dụ trên, công thức tính điểm trung bình không phức tạp, do đó ta cũng có thể dễ dàng tính ngược điểm Toán từ điểm trung bình mà không cần dùng Goal Seek. Tuy nhiên, công cụ Goal Seek của Excel còn cho phép tính ngược từ những công thức rất phức tạp, thậm chí không thể tính bằng tay. Và trong nhiều trường hợp, Goal Seek cho ra kết quả gần đúng (xấp xỉ). 3.3.1 Sử dụng công cụ Goal Seek trên đồ thị Trên đây, chúng ta đã nghiên cứu việc sử dụng công cụ Goal Seek trên các ô văn bản. Phần này, chúng ta sẽ nghiên cứu việc sử dụng Goal Seek trên đồ thị. Cũng với bài toán trên, giả sử thí sinh đó cần điểm trung bình là 6,8. Các bước áp dụng Goal Seek trên đồ thị cho bài toán trên như sau: - Trước hết, ta lại tạo 1 bảng Excel như trong phần trên, với công thức tính điểm trung bình như cũ. Tiếp theo ta bôi đen toàn bộ các ô của bảng để tạo 1 đồ thị. - Sau khi bôi đen, chọn biểu tượng tạo đồ thị (Chart wizard) trên thanh công cụ. Cửa sổ tạo đồ thị hiện ra. Chọn loại đồ thị mặc định (bar chart), và click Finish. Đồ thị được Excel tạo ra như sau: 52
Phần 1: Sử dụng Excel trong quản lý - Tiếp theo, click vào cột biểu thị điểm trung bình. Dạng hiển thị của các cột sẽ thay đổi (có 1 hình vuông hiển thị ở giữa các cột). Click tiếp 1 lần nữa vào cột điểm trung bình, cột sẽ thay đổi dạng hiển thị như hình vẽ dưới. - Đưa con trỏ chuột tới đỉnh cột và sử dụng chức năng kéo thả chuột để dịch chuyển chiều cao của cột. Một hình chữ nhật nhỏ được hiển thị để biểu thị giá trị của cột thay đổi như thế nào. Giả sử ta dịch chuyển chiều cao của cột tới giá trị 6,8. - Khi thả chuột ra, cửa sổ Goal Seek sẽ hiển thị và lúc này ta cần đưa vào ô sẽ được thay đổi để cột điểm trung bình có giá trị là 6,8. Trong trường hợp này là ô B1, do đó ta click cuột vào ô B1. Click OK để hiển thị cửa sổ kết quả tính toán như sau: - Bấm OK để xác nhận những thay đổi trên bảng tính và thoát khỏi cửa sổ trạng thái của Goal Seek. Bấm Cancel nếu muốn hủy bỏ những thao tác vừa thực hiện. 53
Phần 1: Sử dụng Excel trong quản lý 3.4 DATA TABLES Data tables - Các bảng dữ liệu - là chức năng của Excel cho phép tính toán và so sánh các kết quả đầu ra của các giá trị đầu vào khác nhau của một công thức. Thông thường, khi sử dụng 1 công thức, ta đã có sẵn giá trị đầu vào và việc áp dụng công thức cho kết quả đầu ra. Tuy nhiên, trong nhiều trường hợp, chúng ta muốn biết với các đầu vào khác nhau thì đầu ra sẽ thay đổi thế nào. Để dễ dàng cho người dùng có thể tính toán và so sánh các kết quả đầu ra tương ứng với các kết quả đầu vào khác nhau, Excel cung cấp chức năng Data tables cho phép tính toán và hiển thị các kết quả dưới dạng dễ nhìn hơn. Sự kết hợp của các giá trị đầu vào (input) và các kết quả đầu ra tương ứng của một công thức được thể hiện trong một bảng để người dùng có thể dễ dàng so sánh sự sai khác. Tùy vào nhu cầu của mình, chúng ta có thể tạo các bảng dữ liệu có 1 hoặc hai tham số (biến) để tính toán và so sánh. 3.4.1 Sử dụng bảng dữ liệu với 1 tham số Bảng dữ liệu 1 tham số cho phép ta so sánh kết quả của sự thay đổi của 1 đầu vào khi áp dụng 1 công thức. Để hiểu rõ hơn chức năng này, chúng ta xem xét 1 ví dụ. Trở lại với bài toán trong phần Goal Seek, tính điểm trung bình cho 3 môn Toán, Lý, Hóa. Trong bài toán đó, chúng ta giả sử đã biết điểm Lý và Hóa, và chưa biết điểm Toán. Ta muốn biết, điểm Toán phải là bao nhiêu để điểm trung bình là 7. Khi đó, có thể dùng chức năng Goal Seek. Bây giờ, chúng ta muốn biết xem, với các giá trị khác nhau của điểm Toán, điểm trung bình sẽ như thế nào. Như vậy, với bài toán mới, đầu vào là ô điểm Toán (B1). Đầu tiên, bố trí lại dữ liệu trong bảng tính (như hình vẽ dưới) và tạo ra 1 danh sách các giá trị đầu vào thay thế cho điểm Toán hiện tại. Các giá trị thay thế cho đầu vào được đưa vào các ô từ D4 đến D8. Ở đây các giá trị thay thế được đưa vào theo cột. Khi đó, ô dùng để chứa công thức cho kết quả đầu ra được đặt tại ô nằm phía trên ô chứa giá trị thay thế đầu tiên 1 hàng, và lùi về phía bên phải 1 cột. Trong trường hợp này, ô tính điểm trung bình đặt tại ô E3, là ô phía trên 1 hàng, dịch về bên phải 1 cột so với ô D4 là ô chứa giá trị thay thế đầu tiên. 54
Phần 1: Sử dụng Excel trong quản lý Nếu các giá trị thay thế đưa vào theo hàng, thì ô chứa công thức kết quả sẽ đặt tại ô phía dưới 1 hàng, và dịch về bên trái 1 cột so với ô chứa giá trị thay thế đầu tiên. Sau khi bố trí xong dữ liệu, chọn (bôi đen) vùng dữ liệu bao cả ô công thức kết quả và các ô chứa các giá trị thay thế. Trong ví dụ này là vùng D3:E8. Tiếp theo, chọn menu Data > Table. Bảng thiết lập Data Table hiện ra như sau: Trên cửa sổ này, ô Row input cell yêu cầu đưa vào ô dữ liệu đầu vào nếu có các giá trị thay thế theo hàng, và ô Column input cell chứa ô dữ liệu đầu vào với các giá trị thay thế theo cột. Trong ví dụ trên, ô đầu vào của bài toán là điểm Toán (C4), và các dữ liệu thay thế đưa vào theo cột. Vì vậy ô Row input cell bỏ trống, và đưa giá trị $C$4 vào ô Column input cell, hoặc đơn giản hơn, click chuột vào ô này, rồi lại click chuột chọn ô C4, giá trị sẽ tự động được điền vào. Thiết lập xong, chọn OK để tạo bảng dữ liệu. Kết quả như sau: Như ta có thể thấy, tương ứng với mỗi giá trị điểm Toán thay thế, giá trị điểm trung bình tương ứng được tính toán và lưu trên bảng, cho phép người dùng dễ dàng xem và so sánh. Ví dụ, có thể thấy rằng để có điểm trung bình 7.5, điểm môn Toán phải là 9. 3.4.2 Sử dụng bảng dữ liệu với 2 tham số Bảng dữ liệu 2 tham số là chức năng mạnh hơn, cho phép ta thấy được sự thay đổi của 2 tham số đầu vào sẽ ảnh hưởng ra sao tới kết quả đầu ra. Xét ví dụ sau đây: Giả sử 1 công ty điện thoại di động cần tính toán phương án kinh doanh (dạng đơn giản), với các dữ kiện ban đầu như sau: - Công ty hiện có 500.000 thuê bao - Cước thuê bao hàng tháng là 80.000 - Trung bình mỗi thuê bao có cước gọi trung bình 70.000/tháng - Chi phí hàng tháng ước tính khoảng 136.000/ 1 thuê bao 55
Phần 1: Sử dụng Excel trong quản lý Từ các dữ liệu trên có bảng tính sau: Ta phân tích bài toán trên thêm 1 chút để thấy được tình hình thu chi hiện tại. Theo như bài toán, mỗi thuê bao phải trả mỗi tháng 80.000 tiền thuê bao và tiền cước gọi trung bình là 70.000. Tức là mỗi tháng một thuê bao phải trả cho công ty điện thoại trung bình 150.000 đ. Với 500.000 thuê bao, hàng tháng công ty thu được là 500.000 x 150.000 = 75 tỷ. Chi phí trung bình trên 1 thuê bao trong 1 tháng là 136.000. Do vậy, với 500.000 thuê bao, 1 tháng công ty phải chi 500.000 x 136.000 = 68 tỷ. Vậy mỗi tháng công ty có số lãi là 7 tỷ đ. Trong bảng tính trên, công thức cho ô chi phí hàng tháng là Số lượng thuê bao (C4) x 136.000. Công thức cho ô Lãi/tháng là Số lượng thuê bao x (Tiền thuê bao hàng tháng + Tiền cước trung bình/1 thuê bao) - Chi phí hàng tháng. Yêu cầu của bài toán: Ban lãnh đạo công ty muốn đưa ra 1 phương án kinh doanh, theo đó muốn giảm giá cước thuê bao, và như vậy có thể tăng thêm số lượng thuê bao. Giá cước có thể giảm từ 80.000 xuống 75.000, 70.000, 65.000, 60.000. Thuê bao ước tính có thể tăng từ 500.000 lên 520.000, 550.000, 570.000, hoặc 600.000. Yêu cầu là trong mỗi trường hợp như vậy, hãy tính toán và so sánh xem lãi/tháng của công ty sẽ thay đổi thế nào? Ta sẽ sử dụng chức năng bảng dữ liệu 2 tham số để giải quyết bài toán này. Trước hết, xác định 2 tham số đầu vào ở đây là số lượng thuê bao (dự tính sẽ tăng) và giá cước thuê bao (dự kiến sẽ giảm). Tiến hành đưa các giá trị thay thế cho số lượng thuê bao vào theo hàng, ở các ô từ E4 đến H4. Đưa các giá trị thay thế cho giá cước thuê bao vào theo cột, từ ô D5 đến ô D8 (Xem hình vẽ dưới). Chú ý: Đối với bảng dữ liệu 2 tham số, các giá trị thay thế theo cột được đưa vào ngay bên dưới ô chứa công thức kết quả. Các giá trị thay thế theo hàng được đưa vào ngay bên phải ô chứa công thức kết quả. 56
Phần 1: Sử dụng Excel trong quản lý Tiếp theo chọn (bôi đen) vùng chứa công thức và các giá trị thay thế (D4:H8), sau đó chọn menu Data > Table. Trong cửa sổ hiện ra, trong mục Row input cell, chọn ô C4 (số lượng thuê bao). Mục Column input cell chọn ô C5 (tiền thuê bao hàng tháng). Chọn OK để tạo bảng. Kết quả như sau. Với bảng kết quả trên, ta có thể thấy với mỗi phương án giảm giá cước và số thuê bao dự tính sẽ tăng lên, thì khoản lãi/tháng của công ty sẽ thay đổi như thế nào. Lưu ý: Bài toán trên chỉ là 1 ví dụ minh họa cho việc sử dụng chức năng Data Table của Excel. Trong thực tế, bài toán quản lý kinh doanh của 1 công ty sẽ phức tạp hơn rất nhiều. 3.5 QUẢN LÝ TÌNH HUỐNG (SCENARIO MANAGER) Trong quá trình phân tích dữ liệu trong Excel, người dùng thường có nhiều lựa chọn cho 1 bộ dữ liệu, và muốn xem với các bộ dữ liệu khác nhau thì bảng tính của họ sẽ khác nhau ra sao. Chức năng quản lý tình huống cho phép người dùng thay đổi gái trị các ô dữ liệu và xem kết quả của sự thay đổi, đồng thời cũng cho phép người dùng lưu các kết quả đó để có thể xem lại sau này. Chức năng này rất hữu ích trong trường hợp muốn dự đoán kết quả của 1 mô hình dữ liệu. Để làm rõ hơn chức năng này, ta xét ví dụ sau: Giả sử hiện tại hệ đào tạo đại học từ xa của Học viện Công nghệ BCVT có 2.500 sinh viên, phân bố trong các ngành như sau: - Ngành CNTT: 800 - Ngành ĐTVT: 900 - Ngành QTKD: 800 Yêu cầu của bài toán là quản lý số liệu dự báo số lượng sinh viên sẽ tăng trong năm 2006. Với 1 dự báo, có thể lưu số liệu như sau: 57
Phần 1: Sử dụng Excel trong quản lý Tuy nhiên, với cách lưu thông thường này, chỉ có thể lưu được 1 dự báo, trong khi yêu cầu của bài toán muốn Excel quản lý nhiều dự báo về % tăng của số lượng sinh viên mỗi ngành. Để làm được điều này, có thể sử dụng chức năng quản lý tình huống mà Excel cung cấp. - Đầu tiên, chọn menu Tools > Scenario. Cửa sổ Scenario hiện ra: - Thông báo trên cửa sổ cho biết chưa có tình huống nào được lưu lại. Chọn Add để lưu 1 tình huống mới. Đưa vào tên tình huống trong ô Scenario name. Trong ô Changing cells, chọn các ô từ D4 đến D6 là các ô dự báo về % sinh viên sẽ tăng (là các ô sẽ thay đổi trong từng tình huống). Tiếp 58
Phần 1: Sử dụng Excel trong quản lý theo, đưa vào các giải thích trong ô Comment nếu cần và chọn OK. Cửa sổ yêu cầu đưa vào giá trị muốn thay đổi hiện ra. Đưa vào bộ giá trị mới cho các ô. Ví dụ thay đổi dự báo số sinh viên CNTT tăng 10%, số sinh viên ĐTVT tăng 5%, số sinh viên QTKD tăng 5%. Chọn OK để thêm 1 tình huống cho dự báo mới và quay về cửa sổ quản lý tình huống. Trên cửa sổ Scenarios, ta thấy hiện ra tên của tình huống vừa tạo. Tiếp tục chọn Add nếu muốn thêm các tình huống mới. Nếu muốn sửa hoặc xóa 1 tình huống, chọn Edit hoặc Delete. Chọn Close để đóng cửa sổ quản lý tình huống. 3.5.1 Xem 1 tình huống đã tạo Để xem1 tình huống đã tạo, chọn menu Tools > Scenarios. Cửa sổ Scenario Manager hiện ra. Trong ô Scenarios liệt kê danh sách các tình huống được tạo ra trước đó. Chọn 1 tình huống muốn xem và bấm Show. Các dữ liệu mới tương ứng với tình huống được chọn sẽ thay thế dữ liệu cũ trên bảng tính. 3.5.2 Bảng tổng hợp các tình huống Ngoài việc lưu và cho người dùng xem lại các tình huống của 1 vấn đề, Excel còn cho phép người dùng lập bảng tổng hợp các tình huống dưới dạng tổng quát, dễ nhìn hơn. Để làm việc này, trên cửa sổ Scenario Manager, chọn nút Summary. Cửa sổ Summary hiện ra yêu cầu chọn loại báo cáo và xác nhận ô sẽ được dùng làm ô kết quả. Thông thường, Excel đã chọn sẵn các ô kết quả cho ta (ta có thể tự chọn các ô này bằng cách dùng chuột để kéo và chọn các ô). Click OK. 59
Phần 1: Sử dụng Excel trong quản lý Trên bảng tổng hợp, như ta thấy Excel tổng hợp các tình huống lại trong 1 vùng dữ liệu, chỉ ra các ô thay đổi và kết quả của sự thay đổi, giúp cho người dùng có thể phân tích được dễ dàng hơn. 3.6 SỬ DỤNG CHỨC NĂNG SOLVER Solver là một công cụ phân tích nâng cao đa năng rất mạnh của MS Excel. Nó có khả năng xử lý các bài toán có nhiều tham số và có thể cho một tính toán tối ưu. Có thể tóm lược tính năng của Solver qua ví dụ sau: Giả sử tôi có một khoản tiền A, cần mua n loại sản phẩm và đã biết giá của từng loại. Ngoài ra, có thể có thêm một số ràng buộc về từng loại sản phẩm cần mua (ví dụ cần ít nhất x sản phẩm loại 1, y sản phẩm loại 2 .v.v). Hãy cho 1 giải pháp về việc mua các sản phẩm để có thể mua được số lượng nhiều nhất các sản phẩm (đồng thời cũng đáp ứng các điều kiện ràng buộc trên), với số tiền ban đầu. Để sử dụng được chức năng Solver của MS Excel, chúng ta cần nắm được các khái niệm sau: - Target Cell: Ô đích - đây là ô mà người dùng sẽ thiết lập giá trị ở các mức tối đa hay tối thiểu (Thường là giá trị tối đa của thương vụ bạn đang cần phân tích, tính toán tìm ra giải pháp). - Adjustable Cell: Ô có thể thay đổi - là các ô mà Solver sẽ thay đổi giá trị để tìm ra giải pháp đáp ứng được yêu cầu mong muốn của người dùng. - Contraints: Các ràng buộc - Chứa các giới hạn mà Excel phải tham chiếu tới để xử lý. Để minh họa thêm về tính năng này của Excel, ta xem xét ví dụ cụ thể sau: Giả sử một cửa hàng muốn nhập về 3 loại sách để bán với giá cả như sau: Toán - 5000Đ, Vật lý - 4500Đ, Hóa học - 4000Đ. Số tiền mà cửa hàng có là 1 triệu đồng và mong muốn của cửa hàng là với số tiền trên có thể mua được nhiều nhất sách có thể, với ít nhất 80 quyển Toán, 60 quyển Vật lý, và 60 quyển Hóa học. Như vậy bài toán của cửa hàng là: - Tổng số tiền dùng để mua sách: 1 triệu đồng - Số quyển Toán ít nhất cần mua: 80 - Số quyển Vật lý ít nhất cần mua: 60 - Số quyển Hóa học ít nhất cần mua: 60 Yêu cầu: Cần mua được số sách nhiều nhất, với các ràng buộc trên. 60
Phần 1: Sử dụng Excel trong quản lý Để dùng chức năng Solver của Excel giải quyết bài toán này, chúng ta thực hiện các bước sau: Đầu tiên, lập một bảng tính trong Excel như sau: - Trong bảng Excel này, đưa vào loại sách, đơn giá, và số lượng tạm thời (để đơn giản, ta để số lượng tạm thời là 1). Tiếp theo, sử dụng hàm nhân để tính thành tiền (= đơn giá x số lượng) và dùng hàm SUM để tính tổng tiền (lưu trong ô D10). - Bước tiếp theo, tiến hành sử dụng chức năng Solver bằng cách chọn ô tổng tiền (ở đây là ô D10), sau đó chọn menu Tools > Solver. Nếu ta không nhìn thấy menu này có nghĩa là Excel được cài đặt trên máy chưa có chức năng Solver và khi đó phải cài Solver (Xem Cài đặt chức năng Solver ở phần sau). Cửa sổ Solver sẽ hiện ra như sau: - Như đã nói ở trên, chúng ta phải chọn Target Cell để thiết lập giá trị tối đa hay tối thiểu mà mình có. Trong ví dụ này là số tiền tối đa mà chủ cửa hàng dự định sẽ dùng để mua sách 61
Phần 1: Sử dụng Excel trong quản lý (ở đây là ô D10, tổng tiền). Trên cửa sổ các tham số của Solver, ở vùng ràng buộc “Equal To”, chọn từng ràng buộc mà người dùng mong muốn là Max (giá trị lớn nhất), Min (giá trị nhỏ nhất) hay Value of (giá trị bằng) bằng cách click chuột vào từng ô lựa chọn tương ứng. Trong ví dụ này chúng ta lựa chọn Value of và điền vào số tiền có là 1.000.000. Để ý rằng giá trị trong ô Set Target Cell phải là $D$10 (nếu click chọn ô này trước khi chọn chức năng Solver thì Excel sẽ tự động điền giá trị này vào, nếu không phải tự gõ, hoặc click chuột vào ô Set Target Cell, sau đó click chọn ô D10, giá trị sẽ được đưa vào). - Trên vùng lựa chọn các ô sẽ được Excel thay đổi, “By Changing Cells”, lựa chọn các ô mà mình mong muốn có sự thay đổi để đạt được kết quả mong đợi. Trong ví dụ này, chọn các ô về số quyển sách - các ô từ C6 đến C8. Để làm việc này, có thể gõ trực tiếp giá trị $C$6:$C$8 vào ô By Changing Cells, hoặc click chuột vào ô đó, sau đó dùng chuột để kéo chọn vùng các ô từ C6 đến C8. Lưu ý: Nếu không biết chắc những ô nào cần lựa chọn, click vào nút lệnh “Guest” (đoán), khi đó Excel sẽ giúp chúng ta lựa chọn các ô này dựa trên bảng tính vừa tạo ra. - Trên vùng “Subject to the Contraints”, click chuột vào nút lệnh “Add” để mở hộp thoại “Add Contraints”, cho phép đưa vào các ràng buộc. 62
Phần 1: Sử dụng Excel trong quản lý - Trên hộp thoại Add Constraint, trong ô “Cell Reference”, lựa chọn ô mà các ràng buộc sẽ áp dụng lên nó. Hộp chọn đẩy xuống ở bên cạnh sẽ cho phép lựa chọn toán tử so sánh và ô “Contraint” sẽ cho phép điền vào giá trị mong muốn. Cụ thể ở ví dụ đang xét, ta mong muốn là tổng số tiền bỏ ra mua sách không vượt quá 1 triệu, do đó đưa vào các giá trị sau: o Cell Reference: Ô tổng số tiền - D10 o Toán tử lựa chọn là: bằng (=). o Contraint: 1.000.000 (số tiền sẽ đầu tư mua sách) Lưu ý: Để đưa vào giá trị cho ô Cell Reference, ta cũng làm các cách như trên, tức là gõ trực tiếp hoặc click chuột vào ô cần chọn. - Tiếp tục bấm “Add” để đưa thêm ràng buộc khác, như số sách Toán (ô C6) lớn hơn hoặc bằng (>=) 80, số sách Vật lý (ô C7) >=60, số sách Hóa học (ô C8) >=60. - Ngoài ra, cần có thêm 1 ràng buộc nữa, đó là số quyển sách mỗi loại phải là số nguyên. Nếu không đưa vào ràng buộc này, theo lựa chọn mặc định, Excel sẽ có thể cho phép giá trị các ô đó là các số không phải số nguyên, và đó là điều chúng ta không muốn. Với ví dụ đang xét, ta mong muốn Số quyển phải là số nguyên, tức là các ô từ C6 đến C8 phải là số nguyên. Khi đó, chọn Add để thêm 1 ràng buộc và trong phần “Cell Reference” chọn các ô từ C6 đến C8. Trong phần lựa chọn toán tử, lựa chọn “int”. Khi đó bên ô “Constraints” sẽ tự động xuất hiện giá trị “integer”. Điều này có nghĩa là Excel sẽ tính toán để các ô từ C6 đến C8 sẽ phải có giá trị là các số nguyên. - Sau khi đưa vào hết các ràng buộc, đến ràng buộc cuối cùng, bấm “OK” để kết thúc và đóng lại cửa sổ này. Cửa sổ Solver khi đó như sau: 63
Phần 1: Sử dụng Excel trong quản lý - Trong ô Subject to the Contraints, Excel liệt kê ra các ràng buộc cho bài toán như trong hình vẽ trên. Để tiếp tục thêm ràng buộc, chọn Add. Để thay đổi hoặc xóa 1 ràng buộc, chọn ràng buộc đó và chọn Change hoặc Delete. - Khi đã thiết lập xong các tham số, click chuột vào nút lệnh “Solve”. Sau 1 khoảng thời gian ngắn đợi Excel xử lý số liệu, bạn sẽ nhận được một hộp thoại thông báo như sau: - Hộp thoại trên có nghĩa là Excel đã phân tích dữ liệu, tính toán và tìm được 1 giải pháp cho bài toán. Excel yêu cầu chọn một trong 2 lựa chọn là “Keep Solver Solution” để sử dụng giải pháp mà chức năng Solver của Excel cung cấp hay “Restore Original Values” để quay lại các giá trị ban đầu trước khi sử dụng Solver. Trong ví dụ của mình, chúng ta sử dụng lựa chọn thứ nhất và bấm OK. Khi đó Excel sẽ tính toán và cho kết quả như sau: Như chúng ta thấy, trong giải pháp đưa ra, Excel tính ra rằng trong bài toán trên, chủ cửa hàng có thể mua 84 quyển sách Toán, 72 quyển sách Vật lý, và 64 quyển sách Hóa học. Cài đặt chức năng Solver Để cài đặt chức năng Solver trong Excel, thực hiện các bước sau: 64
Phần 1: Sử dụng Excel trong quản lý - Chọn menu Tools > Add-Ins, cửa sổ sau sẽ hiện ra: Trong hộp thoại hiện ra, nếu chức năng Solver chưa được cài đặt trong Excel thì lựa chọn Solver Add-in sẽ không được chọn (ngược lại chức năng Solver đã được cài đăt). - Để cài đặt Solver, click chuột vào hộp chọn “Solver Add-in” và bấm OK. Excel sẽ tiến hành cài đặt chức năng Solver. 3.7 BẢNG PIVOT 3.7.1 Khái niệm bảng Pivot Bảng Pivot - Pivot table - là một bảng làm việc có tương tác được sử dụng để tổng kết, thống kê và phân tích số liệu dựa trên một nguồn dữ liệu có sẵn nào đó. Khi sử dụng bảng Pivot, dữ liệu ban đầu vẫn được giữ nguyên không bị thay đổi gì. 3.7.2 Tạo và sử dụng bảng Pivot Bảng Pivot có thể được tạo từ các nguồn dữ liệu khác nhau như: - Dữ liệu từ chính bảng tính Excel. - Dữ liệu từ Cơ sở dữ liệu bên ngoài. - Các đoạn dữ liệu được hợp nhất trong Excel. - Một bảng Pivot khác trong cùng một trang làm việc. Để tiện cho bạn đọc tìm hiểu về bảng Pivot và các tính năng ưu việt của nó trong việc tổng kết, thống kê và phân tích số liệu, chúng ta xem xét một ví dụ cụ thể như sau: Xét một bảng tính với dữ liệu là các cuộc gọi điện thoại từ nước ngoài về Việt Nam. Dữ liệu cuộc gọi về Việt nam được chia ra theo ngày, theo tỉnh và các mạng di động trong nước. Cụ thể như sau: 65
Phần 1: Sử dụng Excel trong quản lý Bây giờ chúng ta có 1 yêu cầu: Tôi muốn có 1 báo cáo, trong đó thống kê theo ngày, theo tỉnh xem mỗi ngày có bao nhiêu cuộc gọi, bao nhiêu phút gọi về các tỉnh. Để làm được báo cáo này sử dụng chức năng Pivot table của Excel, ta thực hiện các bước như sau: - Lựa chọn 1 ô bất kỳ trong vùng dữ liệu bằng cách click chuột vào ô. - Từ menu hệ thống, chọn Data > Pivot Table and PivotChart Report. - Excel sẽ mở 1 hộp thoại cho phép chọn nguồn dữ liệu để lập bảng pivot như sau: - Ở mục lựa chọn nguồn dữ liệu, chọn “Microsoft Office Excel list or Database”. Ở mục loại báo cáo, lựa chọn “Pivot table” rồi bấm “Next” để tiếp tục. 66
Phần 1: Sử dụng Excel trong quản lý - Excel sẽ mở hộp thoại cho phép đưa vào vùng dữ liệu mà ta muốn Excel tạo bảng Pivot. Nếu ở bước 1 ta đã chọn 1 ô bất kỳ trong vùng dữ liệu, khi đó Excel sẽ lựa chọn cả vùng dữ liệu thay cho ta. - Nếu thấy vùng dữ liệu mà Excel lựa chọn đúng là vùng dữ liệu dự định làm thì bấm Next. Ngược lại, có thể sửa đổi lại vùng dữ liệu bằng cách bấm chọn (bôi đen vùng dữ liệu) trên bảng tính. Sau khi bấm Next, Excel sẽ mở một hộp thoại để lựa chọn bước tiếp theo sẽ làm: - Đến đây, Excel ngầm định sẽ tạo ra một bảng tính mới để lưu trữ bảng pivot mà ta đang định tạo. Nếu muốn bảng Pivot được ghi vào đúng nơi mà dữ liệu nguồn đang được lưu trữ thì có thể chọn “Existing worksheet”. Tiếp theo, có thể chọn Finish để kết thúc quá trình tạo bảng Pivot. Khi đó Excel sẽ tạo một bảng Pivot mà chưa có dữ liệu như sau: - Nếu chọn theo cách này, Excel sẽ tạo một bảng Pivot trống và ta phải “kéo” và “thả” các trường dữ liệu trong “Pivot table field list” sang bảng Pivot trống ở bên để tạo ra bảng Pivot mình mong muốn. Chúng ta sẽ nói kỹ hơn cách “kéo” và “thả” như thế nào để tạo ra bảng Pivot mong muốn. 67
Phần 1: Sử dụng Excel trong quản lý - Nếu không chọn Finish ngay mà chọn “Layout”, Excel sẽ mở ra một hộp thoại cho phép lựa chọn cách thức trình bày dữ liệu trên bảng Pivot. Hộp thoại này như sau: - Hộp thoại này thực chất cũng giống như một bảng Pivot trống để chúng ta lựa chọn cách trình bày. Bây giờ chúng ta sẽ xét chi tiết từng vùng trong bảng Pivot: o Phía bên trái là vùng các dòng dữ liệu “Row”. Đây là vùng chứa các “cột” mà chúng ta muốn báo cáo theo. Ở ví dụ này, chúng ta muốn có báo cáo theo ngày và theo vùng gọi đến, do đó kéo cột dữ liệu “Ngày” và “Vùng gọi đến” rồi thả vào vùng “Row” o Tiếp theo là vùng Data. Đây chính là vùng cần tính toán. Ở ví dụ này, chúng ta muốn biết có bao nhiêu cuộc gọi về từng tỉnh, theo từng ngày và có bao nhiêu phút gọi. Với tổng số phút gọi, kéo cột Thời gian vào vùng “Data”. Excel sẽ tự động tính toán và tạo ra một cột dữ liệu có tên là “Sum of Thời gian liên lạc”. Để có được tổng số cuộc, ta để ý: Mỗi dòng trên bảng dữ liệu ban đầu chính là 1 cuộc gọi, do đó có thể kéo bất kỳ cột nào ở 3 cột trên vào vùng dữ liệu. Ví dụ chọn cột “Ngày”. Excel sẽ tự động tạo ra một cột có tên là “Count of Ngày”. 68
Phần 1: Sử dụng Excel trong quản lý o Đến đây chúng ta có thể kết thúc và bấm OK để Excel thực hiện tính toán và cho kết quả mong đợi. Tuy nhiên, để báo cáo được rõ ràng và đẹp hơn, chúng ta có thể đặt lại tên cho các cột ở vùng Data bằng cách nháy đúp chuột vào cột đó. Khi đó, Excel sẽ mở ra hộp thoại cho phép thay đổi thuộc tính của cột dữ liệu như sau: o Đổi tên của cột “Sum of Thời gian liên lạc” thành “Tổng số phút”, phép toán cần thực hiện là phép toán Sum (tính tổng) nên không cần thay đổi. Nếu muốn sửa đổi định dạng của số, có thể bấm “Number” để sửa lại định dạng số theo mong muốn. o Tiếp theo, như đã nói ở trên, mỗi dòng trong vùng dữ liệu ban đầu chính là 1 cuộc gọi. Do đó, “Count of Ngày” (đếm số ngày) thực chất có thể coi là số cuộc (đếm số 69
Phần 1: Sử dụng Excel trong quản lý cuộc). Như vậy, thay đổi tên của cột “Count of Ngày” thành “Tổng số cuộc gọi” và phép toán là phép Count (đếm) o Đến đây, chúng ta có thể kết thúc việc định nghĩa cách trình bày dữ liệu để Excel tiến hành tạo bảng Pivot. Bấm OK để xác nhận và đóng các cửa sổ lại, bấm Finish để kết thúc. Excel sẽ tạo ra một bảng như sau: 70
Phần 1: Sử dụng Excel trong quản lý Như vậy, chúng ta đã có được một bảng Pivot đáp ứng được các yêu cầu đặt ra là thống kê theo ngày và theo từng tỉnh số lượng cuộc gọi và tổng số phút gọi về. Tuy nhiên, bảng trên vẫn có vẻ “khó nhìn”. Để có một báo cáo rõ ràng và sáng sủa hơn, chúng ta có thể trình bày lại dữ liệu như sau: Rõ ràng, bảng thứ 2 “dễ”nhìn hơn bảng thứ nhất. Để có được bảng dữ liệu như vậy, có thể làm theo cách sau: - Từ bảng Pivot ban đầu, click chuột vào ô Data , sau đó giữ chuột và kéo đè sang bên phía cột Total và thả chuột ra. Khi đó, dữ liệu từ dạng “hàng” sẽ được chuyển sang dạng “cột”. - Để ý trong bảng Pivot, chúng ta thấy dữ liệu được tính toán theo ngày và có tổng con (Sub Total) theo từng ngày. (5/9/2005 Total và 6/9/2005 Total). Nếu không muốn Excel hiển thị các dòng này, có thể làm như sau: - Nhấp đúp chuột vào cột Ngày (ở biểu tượng ) để mở hộp thoại sau: 71
Phần 1: Sử dụng Excel trong quản lý Ở mục lựa chọn “Subtotals”, thay vì để Automatic (tự động), chọn None (không). Khi đó các dòng tính tổng con sẽ không xuất hiện trong bảng Pivot nữa. Như vậy, chúng ta đã biết cách tạo một bảng Pivot để tính toán, thông kê và tạo ra các báo cáo tùy theo nhu cầu của công việc. Pivot là một tính năng có thể nói là rất mạnh và hiệu quả của Excel. Trên đây chỉ là một ví dụ rất đơn giản khi chúng ta làm việc với nguồn dữ liệu ban đầu là dữ liệu tĩnh (không có sự thay đổi). Bây giờ, chúng ta sẽ xem xét việc tạo bảng pivot có dữ liệu động (tức là nguồn dữ liệu có thể thay đổi) và cách làm cho bảng Pivot cập nhật lại dữ liệu theo sự thay đổi của dữ liệu nguồn. Trở lại ví dụ trên, khi chúng ta định nghĩa vùng dữ liệu nguồn để yêu cầu Excel tạo bảng Pivot, chúng ta chỉ ra một vùng dữ liệu cố định (ví dụ từ ô A2 đến ô C100). Tuy nhiên, trong thực tế, với nhiều tình huống, dữ liệu đầu vào tăng lên từng ngày và như thế, những dữ liệu nằm ngoài vùng đã chọn sẽ không được tính toán vào bảng Pivot. Như vậy, báo cáo sẽ không chính xác. Vì vậy, để dự phòng cho trường hợp dữ liệu tăng lên, có thể đưa vào vùng định nghĩa dữ liệu nguồn một khoảng trống đủ lớn để dù dữ liệu nguồn có tăng thì những dữ liệu mới đó vẫn sẽ được cập nhật trong bảng Pivot. Quay trở lại với ví dụ ở trên: Ta thấy nguồn dữ liệu hiện tại gồm có 3 cột và 24 hàng. Nếu chỉ định nghĩa vùng dữ liệu nguồn là các ô từ A1 đến D24 thì khi có thêm 1 hoặc nhiều hàng dữ liệu cuộc gọi mới, những hàng này sẽ không được cập nhập vào bảng Pivot. Để tránh điều này, ta mở rộng vùng dữ liệu để “đón trước” những dữ liệu mới. 72