zunia.vn

Tuyển sinh 2024 dành cho Gen-Z

zunia.vn

» Công Nghệ Thông Tin

» Hệ điều hành

Giáo trình tin học quản lý phần 2

Chia sẻ: Utyew WSFGQWET | Ngày: | Loại File: PDF | Số trang:18

Báo xấu

110
lượt xem 16
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

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 ô...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Giáo trình tin học quản lý phần 2

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),
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
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

CÓ THỂ BẠN MUỐN DOWNLOAD

THÔNG TIN

TRỢ GIÚP

HỖ TRỢ KHÁCH HÀNG

Theo dõi chúng tôi

Chịu trách nhiệm nội dung:

Nguyễn Công Hà - Giám đốc Công ty TNHH TÀI LIỆU TRỰC TUYẾN VI NA

LIÊN HỆ

Địa chỉ: P402, 54A Nơ Trang Long, Phường 14, Q.Bình Thạnh, TP.HCM

Hotline: 093 303 0098

Email: support@tailieu.vn

Giấy phép Mạng Xã Hội số: 670/GP-BTTTT cấp ngày 30/11/2015 Copyright © 2022-2032 TaiLieu.VN. All rights reserved.