Các Hàm Excel 2007 – Sử Dụng Công Thức Và Hàm Trong Excel

Bài này thuộc chuyên đề Hướng dẫn sử dụng MS Excel 2007, bạn có thể click vào để xem các bài khác cùng chuyên đề này.

Đang xem: Các hàm excel 2007

Công thức là cốt lõi của bảng tính Excel, là cốt lõi và tinh túy của MS Excel, nếu không có các công thức thì bảng tính cũng giống như trình soạn thảo văn bản. Chúng ta dùng công thức để tính toán từ các dữ liệu lưu trữ trên bảng tính, khi dữ liệu thay đổi các công thức này sẽ tự động cập nhật các thay đổi và tính ra kết quả mới giúp chúng ta đỡ tốn công sức tính lại nhiều lần.

Bài này giới thiệu về cách sử dụng công thức và hàm trong excel, cũng như cách sử dụng và ý nghĩa các hàm thông dụng.

Giả sử các ô C2:G2 được đặt tên là DongTien, và ô I2 đặt tên là LaiSuat (Xem lại cách đặt tên vùng ở bài số 1) thì trong quá trình nhập công thức bạn có thể làm như sau:

B1. Tại ô B4 nhập vào =NPV(

B2. Nhấn F3, cửa sổ Paste Name hiện ra

B3. Chọn LaiSuat và nhấn OK

B4. Nhập dấu phẩy (,) và gõ F3

B5. Chọn DongTien và nhấn OK

B6. Nhập dấu đóng ngoặc rồi nhập dấu +

B7. Nhấp chuột vào ô B2

B8. Nhấn phím Enter

Chèn tên vùng vào công thức

Một trong những cách dễ dàng nhất để sử dụng hàm trong Excel là sử dụng thư viện hàm. Khi bạn muốn sử dụng hàm nào chỉ việc vào …Nội dung chỉ dành cho thành viên… chọn hàm cần sử dụng. Ngoài ra bạn có thể nhấn vào nút…Nội dung chỉ dành cho thành viên…để gọi hộp thoại  Insert Function một cách nhanh chóng và khi cần tìm hiểu về hàm này bạn chỉ cần nhấn vào Help on this function.

Hộp thoại Insert Function

1.4. Tham chiếu trong công thức

Các tham chiếu sử dụng trong công thức giúp cho chúng ta khỏi tốn công sửa chữa các công thức khi các giá trị tính toán có sựthay đổi. Có 3 loại tham chiếu sau:

Lưu ý: Dấu $ trước thứ tự cột là cố định cột và trước thứ tự dòng là cố định dòng

Thủ thuật hay, click để xem Thủ thuật hay, click để xem
 Nhấn phím F4 nhiều lần để (tuyệt đối) cố định/ bỏ cố định dòng hoặc cột.

Minh họa địa chỉ tương đối và tuyệt đối

B1. Tại ô D2 nhập vào =B2*C2 và Enter. Sau đó quét chọn cả vùng D2:D14 và gõ phím tắt…Nội dung chỉ dành cho thành viên…Vào các ô D3, D4… D14 ta thấy công thức các dòng tự động được thay đổi tương ứng với khoảng cách so với ô D2. Trường hợp này chúng ta dùng địa chỉ tương đối của B2*C2 là vì chúng ta muốn khi sao chép công thức xuống phía dưới thì địa chỉ các ô tính toán sẽ tự động thay đổi theo.

B2. Tại ô E2 nhập vào …Nội dung chỉ dành cho thành viên…và Enter, sau đó chép công thức xuống các ô E3:E14. Chúng ta cần cố định dòng 17 trong địa chỉ tỷ giá B17 vì ta muốn khi sao công thức xuống thì các công thức sao chép vẫn tham chiếu đến ô B17 để tính toán.

B3. Tại ô D15 nhập vào =Sum(D2:D14) và chép sang ô E15.

Lưu ý:

Nên copy một công thức thay vì tạo ra một công thức mới

(1) Dùng chuột kéo phần dưới của ô công thức đến một ô cũng sử dụng cùng một công thức, sau đó thả chuột ra.(2) Nút Auto Fill Options xuất hiện và kết quả cũng hiện ra

Trong một vài trường hợp thì việc copy công thức dễ thực hiện hơn việc tạo ra một công thức mới. Trong ví dụ trên bạn đã được xem qua việc copy công thức tổng cộng chi tiêu trong tháng 1 để ứng dụng tổng cộng chi tiêu cho tháng 2.

Để làm điều này đầu tiên chọn ô công thức B7, sau đó dùng chuột trỏ vào góc bên dưới bên phải của ô B7 đến khi con trỏ trở thành dấu thập màu đen (+). Kế tiếp kéo phần fill handle

sang ô C7. Khi phần fill handle được thả ra, thì tổng giá trị ô C7 xuất hiện với giá trị 126.93. Công thức xuất hiện trên thanh công thức là =SUM(C3:C6), khi bạn click vào ô C7.

Sau khi copy công thức, nút Auto Fill Options

xuất hiện để bạn có thêm chọn lựa format, nhưng trong trường hợp trên bạn không cần sử dụng chúng. Nút

 sẽ biến mất khi bạn click vào một ô khác.

1.5. Tính toán trong bảng (Table)

Dây là tính năng mới trong Excel 2007, trong bảng biểu ta có thể dùng tên cột để làm tham chiếu tính toán (xem lại bài 1). Muốn chuyển một danh sách thành bảng biểu ta hãy đặt ô hiện hành vào trong vùng danh sách, sau đó vào …Nội dung chỉ dành cho thành viên… Chọn My table has headers nếu có > OK. Lưu ý các bảng được đặt tên tự động.

Minh họa chuyển danh sách thành bảng

B1. Tính cột Thành tiền: chọn ô D2 và nhập vào =* …Nội dung chỉ dành cho thành viên… và Enter. Excel sẽ tự động sao chép công thức xuống giúp bạn.

Dùng tên cột làm tham chiếu tính toán

B2. Tính cột Tiền VND: chọn ô E2 và nhập vào = …Nội dung chỉ dành cho thành viên… và Enter. Excel sẽ tự động sao chép công thức xuống giúp bạn.

B3. Thêm dòng tính Tổng hãy để ô hiện hành vào bảng: …Nội dung chỉ dành cho thành viên… chọn Total Row.

Thủ thuật hay, click xem Thủ thuật hay, click xem
Nhấn để tạo bảng. 

Chọn lựa hàm để tính toán

1.6. Các lỗi thông dụng và một số lưu ý

Các lỗi thông dụng

#####

Một khi dấu ##### xuất hiện trên bang tính thì nó cho biết do bị lỗi hoặc cột quá hẹp để hiển thị kết quả của nó.

Thỉnh thoảng Excel không thể tính toán kết quả vì công thức bị lỗi. Sau đây là 3 lỗi thường gặp nhất:

#####: Cột không đủ rộng để hiện thị kết quả trong ô, nên kéo chiều rộng cho ô.

#REF! : Một giá trị không hợp lệ. Các ô bị xóa hoặc bị dán đè giá trị khác lên.

#NAME?: Tên công thức bị sai và Excel không hiểu công thức đó. Khi bạn click vào ô #NAME? thì nó sẽ hiện ra nút

 để bạn có thể sửa sai.

#DIV/0!

 …Nội dung chỉ dành cho thành viên…#NAME?

 …Nội dung chỉ dành cho thành viên…#N/A

 …Nội dung chỉ dành cho thành viên…#NULL!

 …Nội dung chỉ dành cho thành viên…#NUM!

 …Nội dung chỉ dành cho thành viên…#REF!

 …Nội dung chỉ dành cho thành viên…#VALUE!

 …Nội dung chỉ dành cho thành viên…Tham chiếu vòng

Trong một số trường hợp nhập công thức thì bạn nhận được hộp thoại thông báo tham chiếu vòng (Circular Reference) vì trong công thức có sử dụng giá trị trong ô mà bạn đang nhập công thức.

Ví dụ: Tại ô A3 bạn nhập vào công thức =A1+A2+A3

Cảnh báo tham chiếu vòng

Nếu các phép tính của bạn yêu cầu phải tham chiếu vòng thì bạn vào nút Office > …Nội dung chỉ dành cho thành viên… Enable iterative calculation

Tùy chọn tham chiếu vòng

Maximun Iterations: Số lần tính vòng tối đaMaximun Change: Sự thay đổi tối đa của giá trị

Ví dụ: Ô A1 chứa số 2, ô A2 chứa số 3, ô A3 nhập vào công thức =A1+A2+A3 và với thiết lập tính vòng như trên thì kết quả ô A3 lần đầu tiên sau khi nhập công thức là 15.

Tùy chọn tính toán (Calculation Options)

Trong thực tế đôi khi chúng ta phải làm việc với một bảng tính rất phức tạp và cần rất nhiều phép tính. Theo mặc định, cứ mỗi sự thay đổi trong bảng tính thì Excel sẽ tự động tính lại tất cả các công thức có trong bảng tính, điều này làm giảm hiệu suất làm việc rất nhiều. Do vậy chúng ta nên điều chỉnh tùy chọn tính toán trong Excel. Vào thanh Ribbon > Formulas >Calculation > Calculation Options > Có các tùy chọn sau (cũng có thể làm theo cách ở hình trên)

Manual: …Nội dung chỉ dành cho thành viên…Ctrl + Alt + F9: Tính toán lại các công thức trong tất cả workbook đang mở không cần quan tâm đến nội dung có thay đổi hay không

1.7. Kiểm tra công thức bằng Formulas Auditing

Một ô có dạng #VALUE (có 1 góc nhỏ tô màu) là ô đó đang có lỗi nào đó. Các lỗi thông dụng như: ô chứa công thức mà kết quả có lỗi, số mà lại định dạng như chuỗi, ô bị khóa, nhập liệu không đúng, … Để dò tìm và sữa lỗi chúng ta dùng bộ công cụ Ribbon > Formulas >Formulas Auditing

Thủ thuật hay, click xem Thủ thuật hay, click xem
Để điều chỉnh các lựa chọn kiểm tra lỗi vào Office >Excel Options >nhóm Formulas >Error Checking và Error checking rules.

Minh họa kiểm tra lỗi

Ví dụ: Kết quả tính NPV có lỗi như hình dưới để tìm lỗi ta vào …Nội dung chỉ dành cho thành viên… Error Checking. Excel sẽ kiểm tra lỗi toàn bộ bảng tính và báo cáo về các ô có chứa lỗi. Trường hợp này thì B4 chứa lỗi.

Nhóm lệnh Formulas Auditing

Bạn muốn tìm hiểu thêm về dạng lỗi thì nhấn vào nút Help on this errorMuốn kiểm lỗi từng bước tính toán của công thức thì nhấn vào Show Calculation Steps…Muốn hiệu chỉnh công thức thì nhấn vào Edit in Formula BarNhấn Next đến lỗi kế và Previous về lỗi trước (nếu có)Nhấn vào Option nếu muốn hiệu chỉnh tùy chọn báo lỗi của Excel.

Để kiểm tra các bước tính toán của công thức bị lỗi trên ta chọn Show Calculation Steps…(hoặc nhấn trực tiếp vào nút Evaluate Formula trong nhóm Formulas Auditing). Nhấn vào các nút Evaluate để xem các bước tính toán của công thức.

Kiểm tra các bước tính toán của công thức

Nếu muốn xem ô B4 có quan hệ với các ô khác như thế nào hãy chọn ô B4, sau đó vào …Nội dung chỉ dành cho thành viên…Trace Precedents. Qua hình bên dưới ta thấy kết quả tính toán của ô B4 được tính từ các ô A2, C2:G2 và ô I2, từ đó chúng ta cũng có thể lần ra lỗi trong công thức.

Ví dụ về cách tìm lỗi trong công thức Excel dùng phím F9

2. Hàm luận lý

2.1 Hàm AND

Công dụng:

Trả về TRUE nếu tất cả các điều kiện thỏa (TRUE).

Cú pháp:

=AND(Logical1,Logical2,…)

Logical1,Logical2… có từ 1đến 255 điều kiện cần kiểm tra TRUE hay FALSE

2.2 Hàm FALSE

Công dụng:

Trả về giá trị FALSE (=0)

Cú pháp:

=FALSE()

2.3 Hàm IF

Công dụng:

Trả về giá trị ở đối số thứ 2 nếu điều kiện là TRUE và trả về giá trị ở đối số thứ 3 nếu điều kiện FALSE

Cú pháp:

=IF(logical_test,value_if_true,value_if_false)

Với:

Với trường hợp thao tác trên nhiều điều kiện, hãy sử dụng hàm IFS hoặc các hàm IF lồng nhau. Xem bài này tại đây.

2.4 Hàm IFERROR

Công dụng:

Trả về giá trị do ta nhập vào khi công thức có …Nội dung chỉ dành cho thành viên… thì trả về kết quả. Hàm này dùng để bẫy lỗi trong công thức

Cú pháp:

=IFERROR(value,value_if_error)

Với:

Value là biểu thức cần kiểm tra lỗi

Value_if_error là giá trị trả về khi có lỗi ở công thức kiểm tra. Các lỗi là #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, hay #NULL!.

2.5 Hàm NOT

Công dụng:

Nghịch đảo giá trị luận lý

Cú pháp:

=NOT(Logical)

Với:

Logical là giá trị hay biểu thức mà khi tính sẽ trả về TRUE hoặc FALSE

2.6 Hàm OR

Công dụng:

Trả về TRUE chỉ cần một trong các điều kiện là TRUE

Cú pháp:

=OR(logical1,logical2,…)

Với:

Logical1,logical2,… Có từ 1 đến 255 điều kiện cần kiểmtra xem TRUE hay FALSE

2.7 Hàm TRUE

Công dụng:

Trả về giá trị TRUE (=1)

Cú pháp:

=TRUE()

Ví dụ 1: Kiểm tra ngày ở các dòng có thuộc khoảng thời gian cho trước hay không. Nếu thuộckhoảng thời gian đó thì kết quả trả về là TRUE, không thuộc thì trả về FALSE.

Minh họa hàm And, Or và Not

Ví dụ 2: Tính xem tháng ở cột A thuộc Quí mấy?

Tại ô B2 nhập vào:

=IF(OR(A2=”Tháng 1″, …Nội dung chỉ dành cho thành viên… A2=”Tháng 5″,A2=”Tháng 6″),”Quí 2″,IF …Nội dung chỉ dành cho thành viên… ,”Quí 3″,”Quí 4″)))

Xem chi tiết ở hình vẽ ngay dưới đây:

…Nội dung chỉ dành cho thành viên…

Ví dụ 3: Tính tiền hoa hồng? Biết doanh thu $500 thì hoa hồng 8%.

Tại ô B2 nhập vào =A2*IF(A2>=500,0.08,IF …Nội dung chỉ dành cho thành viên… 0.03))

Xem chi tiết ở hình vẽ ngay dưới đây:

…Nội dung chỉ dành cho thành viên…

Ví dụ 4: Excel phiên bản cũ cho phép lồng tối đa 7 hàm trong hàm IF (Excel 2007 cho phép lồng tới 64 lần). Giải quyết trường hợp này mời các bạn xem ví dụ: tại ô A1 nhập vào số 12 và tại ô B1 nhập vào công thức sau:

=IF(A1=1, …Nội dung chỉ dành cho thành viên…,IF(A1=11,A1*11,IF(A1=12,A1*12)))))

Ví dụ này không có ý nghĩa nào ngoài việc minh họa cách giải quyết vấn đề giới hạn lồng hàm. Nếu có quá nhiều điều kiện rẽ nhánh chúng ta cần nhóm lại 7 hàm lồng nhau thành một nhóm và các nhóm nối nhau là dấu +.

Ở trường hợp này kết quả trả về là 144 vì nhóm 7 hàm đầu trả về FALSE (=0) cộng với nhóm hàm sau trả về số 144.

Ví dụ 5: (Minh họa hàm If dùng tính thuế thu nhập cá nhân) Tính thuế thu nhập cá nhân theo biểu thuế sau

…Nội dung chỉ dành cho thành viên…

Ví dụ 6: Xét một số ví dụ về hàm IfError. Kiểm tra các biểu thức ở cột D nhập vào có lỗi hay không, nếu có lỗi sẽ trả về chuỗi “Có lỗi” còn không thì trả về kết qảu của biểu thức. Hàm minh họa ở dạng thông thường và ở dạng hàm mãng.

Ví dụ về cách sử dụng hàm IfError

3. Các hàm về dò tìm dữ liệu trong bảng Excel

Phần nầy mô tả cách tìm dữ liệu bảng bằng nhiều hàm khác nhau cài sẵn trong Microsoft Excel. Bạn có thể sử dụng các công thức khác nhau để có được kết quả tương tự.

3.1 Hàm VLOOKUP và HLOOKUP

Chức năng:

Hàm VLOOKUP là hàm dò tìm…Nội dung chỉ dành cho thành viên…sẽ trả về giá trị của một ô nằm trên một cột nào đó nếu thỏa mãn điều kiện dò tìm.

Hàm HLOOKUP là hàm dò tìm…Nội dung chỉ dành cho thành viên…sẽ trả về giá trị của một ô nằm trên một cột nào đó nếu thỏa mãn điều kiện dò tìm.

Xem thêm: Khóa Học Tư Duy Thiết Kế Kiến Trúc Nội, Ngoại Thất Với Kỹ Thuật Dựng Hình 3D

Cú pháp hàm:

VLOOKUP(lookup_value,table_array,col_index_num,option_lookup)

HLOOKUP(lookup_value,table_array,col_index_num,option_lookup)

Đối với hàm Vlookup thì các tham số là:

– Lookup_value: là giá trị dùng để dò tìm, giá trị này sẽ được dò tìm…Nội dung chỉ dành cho thành viên… của bảng dữ liệu dò tìm. Giá trị dò tìm có thể là một số, một chuỗi, một công thức trả về giá trị hay một tham chiếu đến một ô nào đó dùng làm giá trị dò tìm.

–Table_array: là bảng dùng để dò tìm, bảng dò tìm có thể là tham chiếu đến một vùng nào đó hay Name trả về vùng dò tìm. Bảng dò tìm gồm có Rj hàng và Ci cột (i,j >=1), trong đó cột …Nội dung chỉ dành cho thành viên…. ( thường chuyển về địa chỉ tuyệt đối bằng cách nhấn phím …Nội dung chỉ dành cho thành viên… để cố định vùng dò tìm, vùng dò tìm này phải bao các giá trị cần trả về, vùng dò được bắt đầu tại cột có giá trị dò tìm)

– Col_index_num: là số thứ tự của cột (tính từ trái qua phải) trong bảng dò tìm chứa giá trị mà ta muốn trả về. Col_index_num phải >=1 và True hoặc 1 hoặc để trống: là kiểu dò tìm tương đối, hàm sẽ lấy giá trị đầu tiên mà nó tìm được trên cột đầu tiên trong bảng dò tìm. Trong trường hợp tìm không thấy, nó sẽ lấy giá trị lớn nhất mà có giá trị nhỏ hơn giá trị dò tìm.False hoặc 0: là kiểu dò tìm chính xác, hàm sẽ lấy giá trị đầu tiên mà nó tìm được trên cột đầu tiên trong bảng dò tìm. Trong trường hợp tìm không thấy, hàm sẽ trả về #N/A.

Đối với hàm Hlookup thì các tham số như Vlookup nhưng thay vì dò tìm theo cột, lấy cột đầu tiên dò tìm thì Hlookup dò tìm theo hàng, lấy hàng đầu tiên làm hàng dò tìm.

Ví dụ về hàm HLOOKUP VÀ VLOOKUP

Dưới đây là một ví dụ về hàm VLOOKUP, HLOOKUP. Bạn có thể xem ví dụ khác đầy đủ hơn tại bài viết Bài Tập Mẫu Hàm Vlookup (Có Lời Giải)

Ví dụ về hàm HLOOKUP VÀ VLOOKUP

Yêu cầu:

1. Căn cứ vào MANV và Bảng tên phòng ban, điền tên phòng ban ở cột P_BAN.

2. Căn cứ vào Chức vụ và Bảng phụ cấp chức vụ, tính tiền phụ cấp chức vụ cho cộtPCCV.

Theo yêu cầu của câu số 1 thì bạn cần thực hiện hàm tại ô H4 (cột P_BAN) như sau:

Áp dụng HLOOKUP để tính lương tháng

Theo câu lệnh trên, Excel sẽ lấy giá trị của ô B4 (cột MANV), đem so sánh với khu vực dãy ô từ…Nội dung chỉ dành cho thành viên…(dãy ô được khóa cứng bởi …Nội dung chỉ dành cho thành viên…để tránh khi dùng chức năng Fill Handle cho các ô phía dưới) và sẽ lấy giá trị dòng thứ 2 trong dãy ô phù hợp với giá trị ô B4 để điền vào ô…Nội dung chỉ dành cho thành viên…Khi xong câu lệnh và nhấp Enter bạn được kết quả như hình sau:

…Nội dung chỉ dành cho thành viên…

Bây giờ bạn dùng chức năng Fill Handle để điền cho toàn bộ dãy ô ở cột P_BAN.

Tiếp theo câu 2, ở ô I4 (cột PCCV) bạn điền nội dung hàm VLOOKUP và được kết quả như hình sau:

…Nội dung chỉ dành cho thành viên…

Tương tự câu lệnh HLOOKUP, nhưng tại câu lệnh VLOOKUP này, Excel sẽ lấy giá trị của ô…Nội dung chỉ dành cho thành viên…so sánh với dãy ô từ B18 đến C22 (được đặt trong dấu $ để khóa cứng), kế đến lấy giá trị tương ứng của cột thứ 2 trong dãy ô này để điền vào ô I4. Xong bạn nhấpEntervà dùng chức năng Fill Handle để điền cho tất cả các ô trong cột PCCV.

 3.2 Hàm Match

4.3 Hàm LEFT

Hàm LEFT trả về một hoặc nhiều ký tự đầu tiên trong một chuỗi, dựa vào số ký tự mà bạn chỉ định.

Cú pháp:

=LEFT(text, )

Cú pháp hàm có các đối số sau đây:

Ví dụ:

…Nội dung chỉ dành cho thành viên…

4.4 Hàm SUBSTITUTE

Thay thế text trong một chuỗi văn bản. Hãy dùng hàm SUBSTITUTE khi bạn muốn thay thế văn bản cụ thể trong chuỗi văn bản.

Cú pháp:

=SUBSTITUTE(text, old_text, new_text, )

Cú pháp hàm SUBSTITUTE có các đối số sau đây: