Tổng hợp các hàm, công thức Excel cơ bản đến nâng cao đầy đủ

Các thủ thuật Excel sẽ giúp chúng ta làm việc nhanh hơn, chính xác và khoa học hơn. Tuy nhiên một số người dùng thường gặp khó khăn khi sử dụng các công thức Excel vì không thể nhớ được hết cũng như một số hàm nâng cao sẽ khó sử dụng hơn. Nếu bạn đang gặp khó khăn, hãy cùng tìm hiểu các công thức Excel từ cơ bản đến nâng cao qua bài viết sau do Máy Tĩnh Vui tổng hợp nhé.

Các công thức Excel từ cơ bản đến nâng cao

Trong Excel có rất nhiều hàm và các công thức Excel đều là các hàm được định nghĩa trước, có sẵn trong Excel nên nếu biết cách áp dụng công thức trong Excel, bạn sẽ cảm thấy tiện dụng hơn rất nhiều khi sử dụng bảng tính.

Các công thức Excel cơ bản đến các công thức Excel nâng cao nếu bạn hiểu được công thức thì sẽ rất đơn giản để sử dụng. Sau đây là một số công thức tính trong Excel được sử dụng phổ biến nhất mà chúng tôi muốn giới thiệu đến bạn:

Công thức Excel các hàm IF

Công thức Excel hàm IF là một công thức giúp bạn kiểm tra điều kiện có được đáp ứng hay không và trả về giá trị đúng/sai theo điều kiện mà bạn đưa ra.

Hàm IF có công thức như sau: =IF(điều kiện,"giá trị đúng","giá trị sai")

Tuy nhiên, đây là công thức cho 1 điều kiện và nếu bạn muốn áp dụng cho nhiều điều kiện hơn thì bạn sẽ phải dùng hàm IF lồng nhau.

Trong ví dụ ở hình trên, chúng ta sẽ kiểm tra xem ô A có thỏa mãn được điều kiện đưa ra là lớn 10 hay không bằng công thức =IF(A1>10,”Correct”,”Incorrect”) và do A có giá trị là 12>10 nên kết quả nhận được sẽ trả về Correct.

Công thức tính tổng các hàm SUM

Một công thức Excel đơn giản, là một hàm cơ bản trong Excel mà người dùng mới nào cũng biết đến đó là công thức Excel tính tổng hàm SUM. Hàm SUM giúp tính tổng các con số lại với nhau và công thức hàm SUM là

=SUM(ô cần tính 1,ô cần tính 2,ô cần tính 3,...)

Trong ví dụ trên, chúng ta tính tổng của ô A2 và B2 và kết quả sẽ xuất hiện tại ô B3. Để tính nhiều ô, bạn chỉ cần nhấn Ctrl để thêm ô muốn tính vào nội dung của hàm.

Công thức trên giúp bạn tính tổng các ô mà bạn muốn tính còn nếu bạn muốn tính tổng một cột hay một dòng, bạn chỉ cần nhập =SUM sau đó quét tất cả các ô của dòng/cột cần tính.

>> Xem thêm: Cách xoay 1 trang bất kỳ trong Word có thể bạn chưa biết

Công thức hàm VLOOKUP

Công thức Excel VLOOKUP là một dạng hàm tra cứu và tham chiếu giúp tìm kiếm các giá trị trong cột ngoài cùng bên trái của bảng sau đó trả về giá trị trong hàng/cột được chỉ định. Hàm VLOOKUP có công thức:

=VLOOKUP(ô chứa giá trị tìm kiếm, vùng bảng chứa giá trị tìm kiếm và trả về, cột chứa giá trị trả về)

Trong ví dụ trên, sử dụng hàm VLOOKUP để tìm số IDD (104) trong cột ngoài cùng bên trái của bảng $E$4:$G$7 rồi trả về giá trị trong cùng một hàng từ cột thứ 3 (đối số là 3). Đối số thứ 4 đặt là FALSE để trả lại kết quả đúng hoặc lỗi #N/A nếu không tìm được giá trị.

Vùng tham chiếu cần giữ nguyên nên bạn cần sử dụng ký hiệu $ để tạo tham chiếu tuyệt đối. Cả 3 hàm IF, SUM và VLOOKUP đều là các hàm được ứng dụng như công thức Excel tính lương cho nhân viên.

Công thức tính thuế TNCN bằng Excel

Để tính thuế TNCN trên Excel, bạn có thể áp dụng công thức Excel tính thuế TNCN theo biểu thuế lũy tiến từng phần như bảng sau:

Ví dụ: Tổng thu nhập 20.000.000, đóng bảo hiểm 5.000.000, số người phụ thuộc là 1 và giảm trừ bản thân 9.000.000, không có khoản miễn thuế khác và bạn sẽ tính được thu nhập phải chịu thuế TNCN (A) như sau:

• A = 20.000.000 – 5.000.000*10.5% -11.000.000 – 1*4.400.000 = 4.075.000.

Sau khi có A, chúng ta sử dụng hàm IFS để tính thuế cần đóng với G4 là ô chứa A, ta có công thức:

=IF(G4<0,0, G4<=5000000,G4*5%, G4<=10000000,G4*10%-250000, G4<=18000000,G4*15%-750000, G4<=32000000,G4*20%-1650000, G4<=52000000,G4*25%-3250000, G4<=80000000,G4*30%-5850000, G4→80000001,G4*35%-9850000)

Và với ví dụ trên thì thuế TNCN phải đóng là 203.750 VND.

>> Xem thêm: Hàm DATEIF là gì? Hướng dẫn sử dụng hàm DATEIF cực nhanh và đơn giản.

Các công thức Excel tính phần trăm

Chúng tôi sẽ giúp bạn lập công thức Excel tính phần trăm đơn giản, lấy ví dụ theo bảng dữ liệu sau đây và yêu cầu là tìm % số sách bán ra so với số sách tổng:

Tại ô nhập kết quả phần trăm, lấy số hàng đã bán chia cho tổng số hàng =D2/C2 và kết quả nhận được sẽ có giá trị thập phân lớn hơn 0, nhỏ hơn 1 như hình bên dưới và kéo kết quả ô đầu xuống những ô còn lại để copy công thức.

Tiến hành đổi định dạng số thập phân sang % bằng cách bôi đen giá trị vừa thu được → Chuột phải → Format Cells.

Trong bảng vừa hiện ra tại phần Category chọn Percentage định dạng % và trong phần Decimal places (số đằng sau dấu phẩy) bạn chọn số muốn hiển thị phù hợp và ở đây chúng tôi chọn 2.

Phần Sample chính là kết quả xem trước nên bạn có thể điều chỉnh sao cho thích hợp với bảng tính. Nhấn OK để lưu lại và kết quả thu được sẽ như hình bên dưới.

Ngoài ra bạn cũng có thể thiết lập định dạng Percentage thay vì General trước khi thực hiện phép tính.

Công thức đọc số tiền bằng chữ

Trong Excel cũng có một công thức giúp người dùng có thể chuyển số tiền thành chữ để bạn không phải nhập quá nhiều khi cần thực hiện thao tác này. Công thức Excel đọc số tiền bằng chữ chúng ta sẽ sử dụng Add-In.

Để sử dụng công cụ hỗ trợ Add-In bạn sẽ cần cài đặt Add-In trước và sau khi được cài đặt, khi bạn mở Excel thì Add-In sẽ tự động tích hợp thẻ Add-Ins.

Để sử dụng Add-In, bạn chọn ô chứa số cần chuyển thành chữ → Add-Ins → Tiện ích Tiếng Việt → Chèn hàm đọc số thành chữ.

Sau đó sẽ xuất hiện hộp thoại ĐỌC SỐ và bạn có thể tùy chỉnh một số tùy chọn như sau:

• Văn bản đầu: Xây dựng chuỗi văn bản trước khi đọc số bằng chữ.
• Văn bản cuối: Xây dựng chuỗi văn bản sau khi đọc số bằng chữ xong.
• Số cần đọc: Chọn ô chứa số cần chuyển thành chữ.
• Địa chỉ cần dán: Chọn ô để dán chuỗi văn bản sau khi đọc số bằng chữ.
• Đọc ra dạng công thức: Hiển thị công thức khi chọn ô chữ. Bạn chọn ô này nếu muốn copy công thức để đọc nhiều số thành chữ hơn vì nó sẽ cập nhật tự động khi dữ liệu nguồn thay đổi.
• Đọc ra dạng chuỗi: Chỉ hiển thị chuỗi văn bản sau khi đọc số thành chữ.
• Đọc hàng nghìn thành ngàn bạn có thể tích vào dòng chữ đỏ hoặc không thì bỏ qua lựa chọn này.

Sau khi hoàn tất các tùy chỉnh phù hợp với mục đích của bạn, chọn Thực hiện để bắt đầu đọc số thành chữ.

Công thức làm tròn số

Một công thức khác chúng tôi muốn chia sẻ đến bạn chính là công thức Excel làm tròn số hàm ROUND. Hàm ROUND công thức là

=ROUND(number,num_digits)

Trong đó number là số cần làm tròn, num_digits là số chữ số cần làm tròn.

Ngoài hàm ROUND thì chúng ta còn có hàm ROUNDUP, ROUNDDOWN với ROUNDUP là làm tròn lên có công thức

=ROUNDUP(number,num_digits)

Với num_digits là giá trị dương được làm tròn sau phần thập phân còn giá trị âm sẽ làm tròn đến hàng chục, trăm,….

ROUNDDOWN là hàm làm tròn xuống có công thức =ROUNDDOWN(number,num_digits) và hàm ROUNDDOWN sẽ ngược lại với ROUNDUP với kết quả sẽ là giá trị nhỏ hơn giá trị gốc của số.

Các công thức Excel không tự nhảy và cách xử lý

Khi làm việc trên Excel và sử dụng các công thức Excel, đôi khi bạn cũng gặp phải trường hợp công thức Excel không tự nhảy và khiến bạn cảm thấy hoang mang. Tuy nhiên với một số cách sau chúng tôi sẽ giúp bạn chủ động hơn khi gặp phải trường hợp này:

Dùng phím tắt để sửa lỗi

Bạn có thể nhấn phím F9 và nút này hiệu lệnh cho Excel phải tự hiện ra kết quả các công thức trên toàn bảng tính.

Tuy nhiên đây lại không phải là cách tối ưu do sau mỗi lần nhập công thức bạn sẽ cần nhấn phím F9 thêm lần nữa chứ không tự cập nhật nên đây chỉ là cách tạm thời giúp bạn xử lý khi công thức không tự nhảy.

Sửa lỗi trong cài đặt

Cách khắc phục tiếp theo chính là kích hoạt lại chế độ tự nhảy công thức bằng cách vào Menu → File → Options và tùy chọn theo hình bên dưới.

Nếu bạn sử dụng bản Excel 2003 thì bạn vào Tool → Options và chọn theo ảnh bên dưới.

Qua bài viết, chúng tôi đã giới thiệu đến bạn các công thức Excel từ cơ bản đến nâng cao cũng như cách khắc phục nếu công thức Excel không nhảy. Chúng tôi sẽ tiếp tục mang đến cho bạn các công thức Excel khác trong những bài viết sau nên hãy theo dõi chúng tôi nhé!