seychellesartprojects.org

Danh sách 10 công thức & hàm nâng cao trong Excel

Bạn có thể tải xuống Mẫu Công thức Excel Nâng cao này tại đây - Mẫu Công thức Excel Nâng cao

# 1 - Công thức VLOOKUP trong Excel

Hàm excel nâng cao này là một trong những công thức được sử dụng nhiều nhất trong excel. Nó chủ yếu là do tính đơn giản của công thức này và ứng dụng của nó trong việc tìm kiếm một giá trị nhất định từ các bảng khác có một biến chung trên các bảng này. Giả sử bạn có hai bảng có thông tin chi tiết về lương và tên Nhân viên của một công ty với ID Nhân viên là cột chính. Bạn muốn lấy lương bảng B ở bảng A.

Bạn có thể sử dụng hàm VLOOKUP như bên dưới.

Nó sẽ có kết quả trong bảng dưới đây khi chúng ta áp dụng công thức excel nâng cao này trong các ô khác của cột Lương nhân viên.

Kéo Công thức vào Phần còn lại của ô.

Có ba cách phân định chính của VLOOKUP:

1. Bạn không thể có cột chính ở bên phải cột mà bạn muốn điền giá trị từ một bảng khác. Trong trường hợp này, cột Lương nhân viên không được đứng trước ID nhân viên.
2. Trong trường hợp các giá trị trùng lặp trong cột chính trong Bảng B, giá trị đầu tiên sẽ được điền vào ô.
3. Nếu bạn chèn một cột mới vào cơ sở dữ liệu (Ví dụ: Chèn một cột mới trước Lương nhân viên trong Bảng B), kết quả đầu ra của công thức có thể khác dựa trên vị trí mà bạn đã đề cập trong công thức (Trong trường hợp trên, kết quả sẽ trống)

# 2 - Công thức INDEX trong Excel

Công thức excel nâng cao này được sử dụng để lấy giá trị của một ô trong một bảng nhất định bằng cách chỉ định số hàng, cột hoặc cả hai. Ví dụ. Để có được tên của một nhân viên ở lần quan sát thứ 5, Dưới đây là dữ liệu.

Chúng ta có thể sử dụng công thức excel nâng cao như sau:

Công thức INDEX tương tự có thể được sử dụng để nhận giá trị dọc theo hàng. Khi sử dụng cả số hàng và số cột, cú pháp sẽ giống như sau:

Công thức trên sẽ trả về "Rajesh Ved".

Lưu ý: Nếu bạn chèn một hàng khác vào dữ liệu ở Hàng thứ 5, công thức sẽ trả về "Chandan Kale". Do đó, kết quả sẽ phụ thuộc vào bất kỳ thay đổi nào xảy ra trong bảng dữ liệu theo thời gian.

# 3 - Công thức MATCH trong Excel

Công thức nâng cao Excel này trả về số hàng hoặc cột khi có một chuỗi hoặc số nhất định trong phạm vi đã cho. Trong ví dụ dưới đây, chúng tôi đang cố gắng tìm vị trí của “Rajesh Ved” trong cột Tên nhân viên.

Công thức sẽ như sau:

Hàm MATCH sẽ trả về giá trị là 5.

Đối số thứ 3 được sử dụng cho đối sánh chính xác. Bạn cũng có thể sử dụng +1 và -1 dựa trên yêu cầu của mình.

Lưu ý: Người ta có thể kết hợp INDEX và MATCH để khắc phục hạn chế của hàm VLOOKUP.

# 4 - Công thức IF AND trong Excel

Có nhiều trường hợp khi người ta cần tạo cờ dựa trên một số ràng buộc. Tất cả chúng ta đều quen thuộc với cú pháp cơ bản của IF. Chúng tôi sử dụng hàm IF nâng cao trong excel này để tạo một trường mới dựa trên một số ràng buộc trên một trường đã có. Nhưng điều gì sẽ xảy ra nếu chúng ta cần cân nhắc sử dụng nhiều cột trong khi tạo cờ. Ví dụ. Trong trường hợp dưới đây, chúng tôi muốn gắn cờ tất cả nhân viên có mức lương lớn hơn 50 nghìn nhưng ID nhân viên lớn hơn 3.

Chúng tôi sẽ sử dụng IF AND trong những trường hợp như vậy. Vui lòng tìm bên dưới ảnh chụp màn hình cho giống nhau.

Nó sẽ trả về kết quả là 0.

Chúng ta có thể có nhiều điều kiện hoặc ràng buộc để tạo cờ dựa trên nhiều cột bằng cách sử dụng AND.

# 5 - Công thức IF HOẶC trong Excel

Tương tự, chúng ta cũng có thể sử dụng hàm OR trong excel thay vì AND nếu chúng ta chỉ cần thỏa mãn một trong các điều kiện của nhiều hàm.

Trong các trường hợp trên, nếu thỏa mãn bất kỳ điều kiện nào, chúng ta sẽ có ô được điền là 1 khác 0. Chúng ta cũng có thể thay thế 1 hoặc 0 bằng một số chuỗi con có dấu ngoặc kép (“”).

# 6 - Công thức SUMIF trong Excel

Trong một số phân tích, bạn có thể cần lọc một số quan sát khi áp dụng hàm tổng hoặc đếm. Trong những trường hợp như vậy, hàm SUMIF nâng cao này trong excel là giải pháp cứu cánh của chúng tôi. Nó lọc tất cả các quan sát dựa trên các điều kiện nhất định được đưa ra trong công thức excel nâng cao này và tổng hợp chúng. Ví dụ. Điều gì sẽ xảy ra nếu chúng ta muốn biết tổng tiền lương của chỉ những nhân viên có ID nhân viên lớn hơn 3.

Bằng cách áp dụng công thức SUMIFS:

Công thức trả về kết quả là 322000.

Chúng tôi cũng có thể đếm số lượng nhân viên trong tổ chức có ID nhân viên lớn hơn 3 khi chúng tôi sử dụng COUNTIF thay vì SUMIF.

# 7 - CONCATENATE Công thức trong Excel

Hàm nâng cao trong excel này là một trong những công thức có thể được sử dụng với nhiều biến thể. Công thức excel nâng cao này giúp chúng ta nối nhiều chuỗi văn bản thành một chuỗi văn bản. Ví dụ: Nếu chúng ta muốn hiển thị ID nhân viên và Tên nhân viên trong một cột duy nhất.

Chúng ta có thể sử dụng công thức CONCATENATE này để làm điều đó.

Công thức trên sẽ cho kết quả là "1Aman Gupta".

Chúng tôi có thể có thêm một biến thể bằng cách đặt một dấu gạch ngang giữa ID và NAME. Ví dụ. CONCATENATE (B3, ”-“, C3) sẽ dẫn đến “1-Aman Gupta”. Chúng ta cũng có thể sử dụng hàm này vào hàm VLOOKUP khi giá trị LOOKUP trong excel là hỗn hợp của nhiều hơn một biến.

# 8 - Công thức LEFT, MID và RIGHT trong Excel

Chúng ta có thể sử dụng công thức excel nâng cao này Nếu chúng ta muốn trích xuất một chuỗi con nhất định từ một chuỗi nhất định. Các công thức được đề cập có thể được sử dụng dựa trên yêu cầu của chúng tôi. Ví dụ. Nếu chúng ta muốn trích xuất 5 ký tự đầu tiên từ Tên nhân viên, chúng ta có thể sử dụng công thức LEFT trong excel với tên cột và tham số thứ hai là 5.

Đầu ra được đưa ra dưới đây:

Việc áp dụng công thức RIGHT trong excel cũng vậy, chỉ khác là chúng ta sẽ xem xét ký tự từ bên phải của chuỗi. Tuy nhiên, trong trường hợp hàm MID trong excel, chúng ta phải cung cấp vị trí bắt đầu của chuỗi văn bản bắt buộc và độ dài của chuỗi.

# 9 - Công thức OFFSET trong Excel

Hàm excel nâng cao này với sự kết hợp của các hàm khác như SUM hoặc AVERAGE có thể hữu ích trong việc tạo ra một liên lạc năng động cho các phép tính. Nó được sử dụng tốt nhất trong trường hợp khi chúng tôi đang chèn các hàng liên tục vào cơ sở dữ liệu hiện có. OFFSET Excel cung cấp cho chúng ta một phạm vi mà chúng ta cần đề cập đến ô tham chiếu, số hàng và cột. Ví dụ. Nếu chúng ta muốn tính trung bình của 5 nhân viên đầu tiên trong công ty mà chúng ta có mức lương của các nhân viên được sắp xếp theo ID nhân viên, chúng ta có thể làm như sau. Phép tính dưới đây sẽ luôn cho chúng ta tiền lương.

• Nó sẽ cho chúng ta tổng tiền lương của 5 nhân viên đầu tiên.

# 10 - Công thức TRIM trong Excel

Công thức excel nâng cao này được sử dụng để xóa các khoảng trắng không quan trọng khỏi văn bản. Ví dụ. Nếu chúng ta muốn xóa khoảng trắng ở đầu tên nào đó, chúng ta có thể sử dụng nó bằng cách sử dụng hàm TRIM trong excel như sau:

Kết quả đầu ra sẽ là “Chandan Kale” không có khoảng trắng trước Chandan.