Các Hàm Excel Nâng Cao | Công Thức Excel Nâng Cao – Hàm Vlookup

by Shubkids
16 February, 2024

TOP 10 Công Thức Excel Nâng Cao Thông Dụng

Các tin cũ hơn

Excel là phần mềm đóng vai trò quan trọng trong công việc văn phòng với nhiều lợi ích không ngờ. Nhờ đó, chúng ta có thể tạo và quản lý dữ liệu trên các bảng tính, hay tính toán và phân tích dữ liệu bằng các hàm.

Nếu bạn là người thường xuyên dùng Excel để xử lý công việc nhưng chưa biết đến các hàm Excel nâng cao cho người đi làm thì bài viết này dành cho bạn. Cùng Gitiho tìm hiểu nhé!

XEM NHANH BÀI VIẾT

Khi bạn cần thao tác và tính toán dữ liệu về thời gian và ngày tháng, hãy tham khảo một số hàm ngày tháng nâng cao và cách sử dụng chúng dưới đây:

DATEDIF(start_date, end_date, “unit”): Dùng để tính số ngày, tháng hoặc năm giữa hai ngày cụ thể.

Ví dụ: Nếu bạn muốn biết khoảng cách thời gian từ ngày 01/01/2023 đến ngày 31/12/2025 là bao nhiêu tháng, bạn có thể sử dụng hàm:

=DATEDIF(A1, B1, "m")

Trong đó: A1 là ngày bắt đầu, B1 là ngày kết thúc, m là kết quả trả về số tháng.

EDATE(start_date, months): Dùng để trả về ngày sau một số tháng từ ngày bắt đầu.

Ví dụ: Nếu bạn muốn biết ngày sau 6 tháng từ ngày 04/10/2023, bạn có thể sử dụng hàm sau:

=EDATE(A2, 6)

trong đó: A2 là ngày bắt đầu. Và bạn cần định dạng thời gian cho ô kết quả (ô D2)

EOMONTH(start_date, months): là một trong các hàm Excel nâng cao để trả về ngày cuối cùng của tháng sau một số tháng từ ngày bắt đầu.

Ví dụ: Nếu bạn muốn biết ngày cuối cùng trong tháng của 6 tháng sau từ ngày 01/01/2023, bạn có thể sử dụng hàm:

=EOMONTH(A3, 6)

Trong đó: A3 là ngày bắt đầu và bạn cần định dạng ô trả về kết quả (D3)

Kết quả khi sử dụng 3 hàm trên như sau:

Để thực hiện nhanh các thao tác trích xuất và biến đổi các chuỗi ký tự, xem ngay một số hàm xử lý chuỗi cơ bản đến nâng cao mà chúng tôi liệt kê dưới đây:

LEFT(text, num_chars): dùng khi muốn trích xuất một số ký tự từ phía bên trái của một chuỗi.

RIGHT(text, num_chars): để trích xuất một số ký tự từ phía bên phải của một chuỗi.

MID(text, start_num, num_chars): dùng để trích xuất một phần của chuỗi, bắt đầu từ vị trí đã cho và với số lượng ký tự xác định.

LEN(text): Trả về số ký tự trong một chuỗi.

Ví dụ: Giả sử chúng ta có chuỗi “GitihoB2C2023”. Lúc này, bạn muốn trích xuất thông tin từ chuỗi này:

Sử dụng hàm LEFT để lấy 6 ký tự từ bên trái:

=LEFT(A1, 6)

Sử dụng hàm RIGHT để lấy 4 ký tự từ bên phải:

=RIGHT(A1, 4)

Sử dụng hàm MID để lấy 3 ký tự từ vị trí thứ 7:

=MID(A1, 7, 3)

LOWER(text): Chuyển đổi các ký tự trong chuỗi thành chữ thường.

UPPER(text): Chuyển đổi các ký tự trong chuỗi thành chữ hoa.

PROPER(text): Chuyển đổi chuỗi thành dạng chữ hoa đầu tiên của từng từ.

Ví dụ: Giả sử chúng ta có chuỗi “hello GitiHo”. Chúng ta muốn thực hiện các thao tác sau:

Chuyển đổi các ký tự thành chữ thường bằng hàm LOWER:

=LOWER(A7)

Chuyển đổi các ký tự thành chữ hoa với hàm UPPER:

=UPPER(A7)

Chuyển đổi chuỗi thành dạng chữ hoa đầu tiên của từng từ bằng hàm PROPER:

=PROPER(A7)

TRIM(text): Loại bỏ các khoảng trắng không cần thiết từ chuỗi.

=TRIM(" Hello ")

CONCATENATE(text1, text2, …): Ghép nối các chuỗi lại với nhau.

Ví dụ:

=CONCATENATE(A17, " ",A18)

FIND(find_text, within_text, [start_num]): Tìm vị trí của chuỗi con trong chuỗi khác.

Ví dụ:

=FIND("l", A17)

REPLACE(old_text, start_num, num_chars, new_text): Thay thế một phần của chuỗi bằng chuỗi khác.

Ví dụ:

=REPLACE(A20, 7, 5, "Universe")

SUBSTITUTE(text, old_text, new_text, [instance_num]): Thay thế các chuỗi con trong chuỗi khác.

Ví dụ:

=SUBSTITUTE(A21, "H", "J", 2)

TEXT(value, format_text): Định dạng một giá trị dưới dạng chuỗi, sử dụng định dạng được chỉ định.

Ví dụ: Định dạng chuỗi số thành tiền tệ:

=TEXT(A22, "$#,##0.00")

Kết quả khi sử dụng các hàm này như hình dưới:

Ưu điểm của Excel so với các phần mềm tin học văn phòng khác là khả năng thực hiện các phép toán phức tạp và tính toán số học bằng hàm. Bạn có thể tham khảo một số hàm tính toán bên nâng cao bên dưới:

SUMIF(range, criteria, [sum_range]): Tính tổng các ô trong một phạm vi nếu các điều kiện được thỏa mãn.

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …): Tính tổng các ô trong một phạm vi dựa trên nhiều điều kiện.

SUMPRODUCT(array1, [array2, …]): Nhân các phần tử tương ứng của các mảng với nhau và trả về tổng của các kết quả.

Ví dụ: Giả sử tôi có các dữ liệu sau đây:

Hãy tính tổng của các giá trị trong phạm vi B1:B5 nếu giá trị tương ứng trong A1:A5 lớn hơn 1, và giá trị trong B1:B5 lớn hơn 3.

=SUMIF(A1:A5, ">1", B1:B5) + SUMIFS(B1:B5, A1:A5, ">1", B1:B5, ">3")

Trong đó:

Một yêu cầu khác, tính tổng của tích các phần tử tương ứng của A1:A5 và B1:B5, nếu giá trị tương ứng trong A1:A5 lớn hơn 1 và giá trị tương ứng trong B1:B5 lớn hơn 3.

Ta dùng công thức:

=SUMPRODUCT((A1:A5>1)*(B1:B5>3), C1:C5)

Và thu được kết quả là:

ABS(number): Giá trị tuyệt đối của số.

Công thức:

=ABS(-5)

Kết quả: Giá trị tuyệt đối của -5 là 5.

ROUND(number, num_digits): Làm tròn số với số chữ số thập phân xác định.

Công thức:

=ROUND(3.5678, 2)

Kết quả: Số 3.5678 sẽ được làm tròn thành 3.57.

MOD(number, divisor): Trả về phần dư của một phép chia.

Ví dụ:

=MOD(10, 3)

Kết quả: Khi chia 10 cho 3, ta được phần nguyên là 3 và phần dư là 1.

RAND(): Trả về một số ngẫu nhiên từ 0 đến 1.

=RAND()

Kết quả: Excel sẽ trả về một số ngẫu nhiên khác nhau trong phạm vi từ 0 đến 1.

RANDBETWEEN(bottom, top): Trả về một số ngẫu nhiên trong phạm vi từ nhỏ đến lớn.

=RANDBETWEEN(10, 20)

Kết quả: Excel sẽ trả về một số ngẫu nhiên khác nhau trong phạm vi từ 10 đến 20.

INT(number): Trả về phần nguyên của một số.

=INT(5.8)

Kết quả: Phần nguyên của 5.8 là 5.

PRODUCT(number1, number2, …): Trả về tích của các số.

=PRODUCT(2, 3, 4)

Kết quả: Tích của 2, 3, và 4 là 24.

Bạn có thể xem bảng kết quả minh họa sau:

ROUNDUP(number, num_digits): Làm tròn một số lên đến một số chữ số cụ thể.

Ví dụ:

=ROUNDUP(3.14159, 2)

ROUNDDOWN(number, num_digits): Làm tròn một số xuống đến một số chữ số cụ thể.

Ví dụ:

=ROUNDDOWN(3.14159, 2)

MROUND(number, multiple): Làm tròn số tới số gần nhất là bội số của một số khác.

=MROUND(15, 5)

Kết quả: 15 (vì 15 là bội số của 5)

FLOOR(number, significance): hàm sẽ làm tròn số xuống đến bội số gần nhất của một số khác.

=FLOOR(26, 5)

Kết quả: 25 (vì 25 là bội số của 5)

CEILING(number, significance): hàm dùng để làm tròn số lên đến bội số gần nhất của một số khác.

=CEILING(17, 5)

Kết quả: 20 (vì 20 là bội số của 5)

TRUNC(number, [num_digits]): dùng để cắt bỏ phần thập phân của số.

=TRUNC(3.14159, 2)

Kết quả: 3.14

EVEN(number): hàm làm tròn số lên đến số chẵn gần nhất.

=EVEN(7)

Kết quả: 8

ODD(number): Hàm tròn số lên đến số lẻ gần nhất.

=ODD(6)

Kết quả: 7

Bạn có thể xem hình minh họa dưới đây:

Khi làm việc với dữ liệu lớn, việc tìm kiếm và trích xuất thông tin thủ công sẽ tốn rất nhiều thời gian của bạn. Chính vì điều đó, bạn cần có các hàm dò tìm để hỗ trợ bạn dò thông tin theo điều kiện và tiêu chí cụ thể và nhanh chóng:

Ví dụ: Ta có bảng dữ liệu sau:

Hãy sử dụng các hàm dò tìm để xử lý dữ liệu:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]): Tìm kiếm một giá trị trong một phạm vi và trả về giá trị tương ứng từ cột được chỉ định.

Yêu cầu: Tìm giá trị tương ứng với 90 trong cột A và trả về giá trị tương ứng ở cột B.

=VLOOKUP(90, A1:D3, 2, FALSE)

Kết quả: Kết quả sẽ là 100.

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]): Tìm kiếm một giá trị trong một phạm vi và trả về giá trị tương ứng từ hàng được chỉ định.

Yêu cầu: Tìm giá trị tương ứng với 20 trong hàng 1 và trả về giá trị tương ứng ở hàng 2.

=HLOOKUP(20, A1:D3, 2, FALSE)

Kết quả: Kết quả sẽ là 60.

MATCH(lookup_value, lookup_array, [match_type]): Tìm kiếm giá trị trong một mảng và trả về vị trí của giá trị đó.

Yêu cầu: Tìm vị trí của giá trị 50 trong cột A.

=MATCH(50, A1:A3, 0)

Kết quả: Vị trí của giá trị 50 trong cột A là 2 (nằm ở hàng thứ 2).

INDEX(array, row_num, [column_num]): Truy xuất giá trị từ một phạm vi dựa trên số hàng và số cột xác định.

Yêu cầu: Truy xuất giá trị từ phạm vi A1:D3 dựa trên hàng thứ 2 và cột thứ 3.

=INDEX(A1:D3, 2, 3)

Kết quả: Giá trị tại hàng 2, cột 3 là 70.

CHOOSE(index_num, value1, [value2], …): Chọn giá trị từ một danh sách các giá trị dựa trên chỉ số.

Yêu cầu: Chọn giá trị từ danh sách [100, 200, 300] dựa trên chỉ số 2.

=CHOOSE(2, 100, 200, 300)

Kết quả: Chọn giá trị thứ 2 từ danh sách là 200.

SEARCH(find_text, within_text, [start_num]): Tìm kiếm một chuỗi con trong chuỗi khác và trả về vị trí của chuỗi con đó.

Yêu cầu: Tìm vị trí của chuỗi con “20” trong chuỗi “10 20 30”.

=SEARCH("20", "10 20 30")

Kết quả: Vị trí của chuỗi con “20” trong chuỗi “10 20 30” là 4.

Giả sử bạn có danh sách điểm số của sinh viên như sau:

Yêu cầu hãy tính:

Tính trung bình cộng các điểm trong cột B.

Tính trung bình cộng các điểm trong cột C chỉ khi điểm tương ứng trong cột A lớn hơn hoặc bằng 7.

Tìm điểm cao nhất trong danh sách.

Tìm điểm thấp nhất trong danh sách.

Đếm số lượng điểm trong cột D.

Đếm số lượng ô không rỗng trong cột C.

Đếm số lượng điểm trong cột A lớn hơn hoặc bằng 7.

Xếp hạng điểm 9 trong danh sách.

AVERAGE(number1, number2, …): Trung bình cộng của các số.

=AVERAGE(D2:D4)

Kết quả: Trung bình cộng các điểm trong cột B là 7.

AVERAGEIF(range, criteria, [average_range]): Tính trung bình cộng của các ô trong một phạm vi chỉ khi điều kiện được thỏa mãn.

=AVERAGEIF(B2:B4, ">=7", C2:C4)

Kết quả: Trung bình cộng các điểm trong cột C chỉ khi điểm tương ứng trong cột A lớn hơn hoặc bằng 7 là 8.

MAX(number1, number2, …): Giá trị lớn nhất trong danh sách.

=MAX(B2:B4)

Kết quả: Điểm cao nhất trong danh sách là 9.

MIN(number1, number2, …): Giá trị nhỏ nhất trong danh sách.

=MIN(B2:B4)

Kết quả: Điểm thấp nhất trong danh sách là 7.

COUNT(number1, number2, …): Đếm số lượng giá trị trong danh sách.

=COUNT(D2:D4)

Kết quả: Có 3 điểm trong cột D.

COUNTA(value1, value2, …): Đếm số lượng giá trị không rỗng trong danh sách.

=COUNTA(C2:C4)

Kết quả: Có 3 ô không rỗng trong cột C.

COUNTIF(range, criteria): Đếm số lượng ô trong một phạm vi thỏa mãn một điều kiện cụ thể.

=COUNTIF(D2:D4, ">=7")

Kết quả: Có 2 điểm trong cột D lớn hơn hoặc bằng 7.

RANK(number, ref, [order]): Xếp hạng của một số trong một danh sách.

=RANK(9, B2:B4)

Kết quả: Xếp hạng điểm 9 trong danh sách là 1.

Bạn có thể xem hình minh họa bên dưới:

Các hàm Excel này còn được gọi là hàm logic giúp bạn phân tích các dữ liệu dựa trên các điều kiện logic.

Giả sử bạn có bảng điểm của bạn học sinh như sau:

Hãy tính:

Kiểm tra nếu điểm Sinh lớn hơn 8 và điểm Toán lớn hơn hoặc bằng 7, trả về “Học sinh giỏi”, ngược lại trả về “Học sinh khá”.

Kiểm tra nếu có ít nhất một môn nào đó đạt điểm 9 trở lên, trả về “Học sinh xuất sắc”, ngược lại trả về “Học sinh giỏi”.

Đảo ngược kết quả của điều kiện: nếu điểm Lý nhỏ hơn 7, trả về “Cần cố gắng hơn”, ngược lại trả về “Tiếp tục giữ vững”.

Ta dùng các hàm sau:

IF(logical_test, value_if_true, value_if_false): Thực hiện một kiểm tra logic và trả về giá trị dựa trên kết quả kiểm tra.

AND(logical1, [logical2], …): Kiểm tra nhiều điều kiện và trả về TRUE nếu tất cả điều kiện đều đúng.

OR(logical1, [logical2], …): Kiểm tra nhiều điều kiện và trả về TRUE nếu ít nhất một điều kiện đúng.

NOT(logical): Đảo ngược kết quả của một kiểm tra logic.

Để giải bài toán, chúng ta kết hợp các hàm trên thành công thức sau:

=IF(AND(A2>8, B2>=7), "Học sinh giỏi", "Học sinh khá")

Kết quả: Kết quả sẽ là “Học sinh khá” (vì điểm Sinh không lớn hơn 8).

2. Hàm IF và OR

=IF(OR(A2>=9, B2>=9, C2>=9, D2>=9), "Học sinh xuất sắc", "Học sinh giỏi")

Kết quả: Kết quả sẽ là “Học sinh giỏi” (vì không có môn nào đạt điểm 9 trở lên).

3. NOT

=IF(NOT(D2<7), "Tiếp tục giữ vững", "Cần cố gắng hơn")

Kết quả: Kết quả sẽ là “Tiếp tục giữ vững” (vì điểm Lý là 8.0, không nhỏ hơn 7).

IFERROR(value, value_if_error): Hàm trả về một giá trị được chỉ định nếu một lỗi xảy ra, ngược lại trả về giá trị khác.

Ví dụ:

=IFERROR(A1/B1, "Lỗi chia cho 0")

Kết quả: Nếu lỗi xảy ra khi chia A1 cho B1, hàm này sẽ trả về chuỗi “Lỗi chia cho 0”

HYPERLINK(link_location, [friendly_name]): Hàm sẽ tạo một liên kết tới một địa chỉ web hoặc tệp trong máy tính.

Ví dụ:

=HYPERLINK("https://gitiho.com/", "Click here")

OFFSET(reference, rows, cols, [height], [width]): hàm trả về một tham chiếu dịch chuyển từ một ô hoặc phạm vi ô hiện tại.

=OFFSET(A1, 1, 2)

Lấy A1 làm ô gốc, thì kết quả sẽ là giá trị của ô C2 (dịch xuống 1 hàng và qua phải 2 cột).

YEARFRAC(start_date, end_date, [basis]): hàm trả về số năm và thập kỷ tương đương giữa hai ngày.

=YEARFRAC(DATE(2020, 1, 1), DATE(2022, 6, 30))

Kết quả: 2.5 (tương đương 2 năm và nửa năm)

DCOUNTA(database, field, criteria): Hàm dùng để đếm số bản ghi không rỗng trong một cơ sở dữ liệu dựa trên các tiêu chí cung cấp.

=DCOUNTA(A1:C5, "Age", B1:B5)

Kết quả: 2 (có 2 bản ghi có giá trị trong cột “Age”)

DGET(database, field, criteria): Trích xuất một giá trị duy nhất từ một cơ sở dữ liệu dựa trên các tiêu chí cung cấp.

=DGET(A1:C5, "Age", A1:A2)

Kết quả: Công thức trên dùng để trích xuất tuổi của “Chung Đại” là 31.

DSUM(database, field, criteria): Dùng để tính tổng của các giá trị trong một cột cơ sở dữ liệu dựa trên các tiêu chí cung cấp.

=DSUM(A1:C5, "Age", C1:C2)

Kết quả: Tính tổng tuổi của của người thuộc thành phố NY (tức là số tuổi của Chung Đại – 31)

Ví dụ: Bạn đang đầu tư vào một dự án có dòng tiền như sau:

Năm 0: -100,000 (đầu tư ban đầu)

Năm 1: 30,000

Năm 2: 40,000

Năm 3: 50,000

NPV(rate, value1, [value2], …): Dùng để tính giá trị hiện tại của một chuỗi các dòng tiền (tiền vào và tiền ra) dựa trên một tỷ lệ lãi suất.

Với tỷ lệ lãi suất là 5%, công thức NPV sẽ là:

=NPV(0.05, -100000, 30000, 40000, 50000)

IRR(values, [guess]): dùng khi bạn muốn tìm tỷ lệ lãi suất nội bộ mà NPV của chuỗi các dòng tiền là 0.

=IRR(B1:B5)

PV(rate, nper, pmt, [fv], [type]): dùng nếu bạn muốn tính giá trị hiện tại của một khoản tiền trong tương lai.

Ví dụ: Bạn có thể tính giá trị hiện tại của 50,000 đồng sau 5 năm tới (với lãi suất là 6%)

=PV(0.06, 5, 0, 50000)

FV(rate, nper, pmt, [pv], [type]): Hàm dùng để tính giá trị tương lai của một khoản tiền dựa trên lãi suất và số năm.

Ví dụ: Nếu bạn muốn biết giá trị tương của 10,000,000 đồng sau 10 năm với lãi suất 8%, bạn có thể dùng công thức sau:

=FV(0.08, 10, 0, -10000000)

Kết quả khi sử dụng các công thức trên như sau:

Hy vọng qua bài viết tổng hợp các hàm Excel nâng cao này, bạn có thể áp dụng chúng vào thực tế công việc của mình. Việc sử dụng chúng không chỉ hỗ trợ bạn phân tích, xử lý các dữ liệu và yêu cầu phức tạp mà còn giúp bạn tiết kiệm thời gian hơn đấy.

KHÓA HỌC EXCEL ONLINE HOÀN TOÀN MIỄN PHÍ

Khóa học với hơn 14000 HỌC VIÊN đang học và đánh giá trung bình 4.76 SAO

0 thảo luận

Sử dụng ứng dụng Excel thì làm việc với HÀM trong Excel là việc rất thường xuyên. Chúng giúp thực hiện tính toán dữ liệu trực tiếp trên bảng tính nhanh và tiện lợi hơn.

Các hàm Excel là những công thức được định nghĩa trước và đã có sẵn trong Excel, sẽ rất có ích với các bạn thường xuyên phải làm việc trên bảng tính Excel, đặc biệt là trong lĩnh vực kế toán, hành chính nhân sự.

Bài học

Tài liệu cho khóa học
Tải tài liệu cho khóa học	00:00:00
Hàm logic, tính toán, thống kê
Hướng dẫn nhập hàm, công thức tính toán	00:06:30
Phân biệt các loại địa chỉ sử dụng trong công thức	FREE	00:07:58
Hàm tính toán SUM, MAX, MIN, AVERAGE	00:05:32
Hàm logic AND	00:02:55
Hàm logic OR	00:03:06
Hàm logic NOT	00:02:31
Hàm rẽ nhánh IF	00:12:49
Hàm chọn trường hợp CHOOSE	00:05:24
Hàm đếm COUNT, COUNTA, COUNTBLANK	00:04:46
Hàm đếm có điều kiện COUNTIF	00:06:20
Hàm tính tổng có điều kiện SUMIF	00:07:15
Hàm tính trung bình có điều kiện AVERAGEIF	00:04:06
Hàm đếm có nhiều điều kiện COUNTIFS	00:12:16
Hàm tính tổng có nhiều điều kiện SUMIFS	00:06:13
Hàm tính trung bình có nhiều điều kiện AVERAGEIFS	00:05:13
Hàm tính toán dữ liệu ở các worksheet khác nhau	00:08:15
Hàm tính toán sử dụng ký tự đặc biệt	00:14:21
Hàm làm tròn số liệu ROUND, ROUNDUP, ROUNDOWN	00:05:16
Hàm lấy số dư, lấy phần nguyên MOD, INT	00:09:58
Hàm xử lý chuỗi ký tự
Hàm đếm số lượng ký tự trong chuỗi LEN	00:02:44
Hàm xử lý ký tự viết hoa, viết thường UPPER, LOWER, PROPER	00:02:34
Hàm tách chuỗi ký tự LEFT, MID, RIGHT	00:06:05
Hàm nối chuỗi ký tự CONCATENATE, &	00:05:15
Hàm chuẩn hoá chuỗi ký tự CLEAN, TRIM	00:06:10
Hàm chuyển chữ số thành số VALUE	00:03:08
Hàm chuyển hóa dữ liệu về dạng chữ TEXT	00:06:27
Hàm dò tìm ký tự trong chuỗi FIND, SEARCH	00:08:59
Hàm thay thế ký tự trong chuỗi REPLACE, SUBSTITUTE	00:06:57
Hàm so sánh hai chuỗi ký tự EXACT	00:02:08
Đếm số lượng một ký tự nào đó trong chuỗi	00:04:32
Sử dụng hàm xử lý bài toán tách họ tên (Phần 1)	00:13:37
Sử dụng hàm xử lý bài toán tách họ tên (Phần 2)	00:06:32
Hàm xử lý thời gian
Hàm lấy thời gian hiện tại TODAY, NOW	00:01:39
Hàm tách các đơn vị thời gian DAY, MONTH, YEAR, HOUR, MINUTE, SECOND	00:02:15
Hàm nhập thời gian chuẩn xác DATE, TIME	00:07:05
Hàm xác định chỉ số ngày trong 1 tuần WEEKDAY	00:11:14
Hàm xác định số ngày làm việc giữa 2 thời điểm NETWORKDAYS	00:05:40
Hàm xác định số ngày làm việc giữa 2 thời điểm với ngày cuối tuần tuỳ chỉnh NETWORKDAYS.INTL	00:10:57
Hàm xác định thời điểm sau/trước một số ngày làm việc nhất định so với mốc WORKDAY	00:04:41
Hàm xác định thời điểm sau/trước một số ngày làm việc nhất định so với mốc (cuối tuần tuỳ chỉnh) WORKDAY.INTL	00:10:43
Hàm tìm khoảng cách giữa 2 thời điểm DATEDIF	00:14:57
Hàm xác định ngày cuối tháng EOMONTH	00:02:38
Xác định năm nhuận	00:07:42
Hàm dò tìm, tham chiếu
Hàm dò tìm LOOKUP	00:10:44
Hàm dò tìm theo cột VLOOKUP	00:22:45
Hàm dò tìm theo hàng HLOOKUP	00:07:49
Hàm trả về vị trí dữ liệu trong tham chiếu MATCH	00:11:28
Hàm trả về giá trị của một ô trong vùng dò tìm INDEX	00:08:34
Kết hợp dò tìm INDEX MATCH	00:13:20
Tìm giá trị khác 0 đầu tiên	00:16:05
Xác định tháng đầu tiên mất doanh thu	00:28:43
Hàm dịch chuyển tham chiếu đến vị trí khác OFFSET	00:21:47
Hàm biến dữ liệu văn bản thành vùng tham chiếu INDIRECT	00:06:32
Tuỳ chọn, thiết lập dành cho công thức tính toán
Thiết lập hiển thị công thức tính toán	00:02:55
Kiểm tra lỗi công thức tính toán	00:07:41
Thiết lập cửa sổ theo dõi công thức	00:04:30
Những thiết lập cho nhóm Formulas Auditing	00:05:39
Thiết lập tùy chọn tính toán Calculation Options	00:05:35
Tính toán vòng lặp – Những tùy biến thật lạ trong Excel	00:14:19

Đánh giá khóa học

Không có đánh giá nào cho khóa học này.

101 Học viên tham gia

Công thức Excel nâng cao – Hàm DGET

Hàm DGET là một trong những công thức Excel nâng cao được sử dụng nhiều trong quá trình thao tác với Excel, hàm DGET cho phép người dùng trích một giá trị từ cơ sở dữ liệu

Cú pháp: =DGET(database, field, criteria)

Database : Phạm vi dữ liệu bao gồm cột, ô, danh sách, cơ sở dữ liệu
Field : Chỉ rõ cột trong hàm, tiêu đề cột
Criteria : Pham vi ô chứa điều kiện

Lưu ý:

Lỗi #VALUE: Không có giá trị thỏa mãn điều kiện
Lỗi #NUM: Có quá nhiều bản ghi phù hợp điều kiện

Hàm DSUM

Hàm DSUM là hàm excel nâng cao có chức năng tính tổng 1 trường hay 1 cột trong CSDL với điều kiện cho trước

Cú pháp DSUM(database, field, criteria)Database : Phạm vi dữ liệu bao gồm cột, ô, danh sách, cơ sở dữ liệuField : Chỉ rõ cột trong hàm, tiêu đề cộtCriteria : Pham vi ô chứa điều kiện

Ví dụ: Tính tổng điểm của các bạn có giới tính Nam

Kết quả trả về là 108 – là tổng điểm của các bạn có giới tính Nam trong cột tổng điểm

Hàm INDEX

INDEX là một hàm nâng cao trong Excel có khả năng trả về mảng trong excel, lấy kết quả về theo dạng mảng là giao giữa giá trị của ô và dòng.Hàm INDEX có 2 dạng là dạng mảng và dạng tham chiếuINDEX dạng mảng: (Array,Row_num,[Column_num])INDEX dạng tham chiếu: INDEX(Reference,Row_num,[Column_num],[Area_num])Reference: Vùng tham chiếuArray: Phạm vi ô hoặc mảngRow_num: Chọn hàng trong mảng mà từ đó nhập về một giá trịColumn_num: Chọn cột trong mảng mà từ đó nhập về một giá trịArea_num: Số vùng của ô sẽ trả về giá trị

Các bạn có thể xem thêm ví dụ về hàm Index cùng hướng dẫn sử dụng trong từng trường hợp bằng cách tham khảo cách sử dụng hàm INDEX trong Excel

Trên đây là một số hàm nâng cao trong excel cùng các ví dụ cụ thể. Những hàm nâng cao này không những được sử dụng độc lập mà còn có thể kết hợp với các hàm điều kiện khác như COUNTIF, IF, AND… để hỗ trợ các bạn tính toán nhanh hơn với các phép toán phức tạp khi xử lý dữ liệu trên bảng tính Excel. Các bạn cũng có thể tham khảo thêm 7 thủ thuật Excel hỗ trợ tính toán để có thể làm chủ được phần mềm và tiết kiệm thời gian trong công việc

https://thuthuat.taimienphi.vn/cac-ham-nang-cao-trong-excel-co-vi-du-cu-the-22864n.aspx Trong Excel có rất nhiềm hàm tính toán, thống kế khác nhau, có cả những hàm nâng cao và những hàm cơ bản, sử dụng các hàm cơ bản trong Excel thành thạo sẽ giúp bạn học các hàm nâng cao dễ dàng hơn, vậy những hàm cơ bản trong excel bao gồm những hàm nào? Các bạn hãy tìm hiểu trên Taimienphi đê biết câu trả lời nhé.

Công thức Excel nâng cao – Hàm INDEX

INDEX là một hàm nâng cao trong Excel có khả năng trả về mảng trong Excel, lấy kết quả về theo dạng mảng là giao giữa giá trị của ô và dòng.Hàm INDEX có 2 dạng là dạng mảng và dạng tham chiếu.

INDEX dạng mảng: (Array,Row_num,[Column_num])
INDEX dạng tham chiếu: INDEX(Reference,Row_num,[Column_num],[Area_num])

Trong đó:

Reference: Vùng tham chiếu
Array: Phạm vi ô hoặc mảng
Row_num: Chọn hàng trong mảng mà từ đó nhập về một giá trị
Column_num: Chọn cột trong mảng mà từ đó nhập về một giá trị
Area_num: Số vùng của ô sẽ trả về giá trị

Xem thêm: Cách đánh số trang tính Excel cực kỳ đơn giản

Nên học Excel ở đâu uy tín?

Hiện nay, việc học chứng chỉ tin học Excel gần như là điều bắt buộc mà ai cũng phải có. Thế nên có rất nhiều trung tâm được mở ra nhằm phục vụ nhu cầu đó của các bạn. Nhưng các bạn lại không biết trung tâm nào chất lượng, uy tín để có thể yên tâm học và thi chứng chỉ.

Vì vậy, trung tâm EDUSA là một trung tâm đào tạo chứng chỉ tin học Excel mà bạn có thể tin tưởng đấy. Với cam kết 100% học viên sẽ có chứng chỉ ngay lần thi đầu tiên cùng đội ngũ giáo viên có chuyên môn cao, bạn sẽ không phải lo lắng khi học tại trung tâm chúng tôi.

Các bài giảng được đội ngũ giảng viên hệ thống lại kiến thức chi tiết, cụ thể, ngắn gọn, dễ hiểu phù hợp với các trình độ.
Học bất cứ thời gian nào rảnh, tổng thời lượng học kéo dài trong 1-2 giờ.
Lộ trình học cụ thể giúp bạn nắm vững kiến thức và sử dụng thành thạo kỹ năng Excel sau mỗi buổi học.
Cam kết học và thi lại miễn phí 100% nếu học viên thi không đạt chứng chỉ.
Hỗ trợ đăng ký thi, nhận chứng chỉ và giao chứng chỉ đến tận nhà học viên.
Đội ngũ giảng viên chuyên nghiệp, nhiệt tình.

TOP 10 Hàm Excel Quan Trọng Nhất Trong Công Việc

Công thức Excel nâng cao – Hàm Choose

Hàm Choose là một trong những công thức Excel nâng cao có chức năng trả về 1 giá trị trong danh sách các đối số. Hàm Choose có thể sử dụng trên tất cả các phiên bản Excel 2016, 2013, 2010, 2007, 2003.

Cú pháp : =CHOOSE(index_num, value1, [value2], …)

Index_num : Vị trí của giá trị trả về
Value : Các giá trị trong danh sách, tối đa 255 giá trị.

Hàm VLOOKUP

Hàm Vlookup là một trong những hàm nâng cao thông dụng nhất trong excel với nhiều ứng dụng, Hàm VLookup được sử dụng nhiều nhất trong việc tìm và tra cứu dữ liệu trong một bảng tính.

Cú pháp: VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])lookup_value: Giá trị dùng để tìmtable_array: Bảng giá trị để dò, sử dụng giá trị tuyệt đối bằng dấu $col_index: Thứ tự các cột cần lấy dữ liệu trong bảng giá trị dòrange_lookup: Phạm vi tìm kiếmĐể xem tổng hợp các ví dụ khác nhau về các chức năng cơ bản của hàm VLOOKUP, các bạn tham khảo cách sử dụng hàm VLOOKUP trong Excel

Hàm Choose

Hàm Choose là một trong những hàm nâng cao trong excel có chức năng trả về 1 giá trị trong danh sách các đối số. Hàm Choose có thể sử dụng trên tất cả các phiên bản Excel 2016, 2013, 2010, 2007, 2003

Cú pháp : CHOOSE(index_num, value1, [value2], …)Index_num : Vị trí của giá trị trả vềValue : Các giá trị trong danh sách, tối đa 255 giá trị.Ví dụ : Tìm số điểm của bạn đứng thứ 2 trong danh sách các bạn có điểm toánKết quả trả về là 5 – là giá trị của vị trí thứ 2 trong danh sách các ô được chọn

Cách sử dụng hàm index và match nâng cao trong excel

Công thức Excel nâng cao – Hàm DCOUNTA

Hàm DCOUNTA có chức năng đếm các ô không trống trong danh sách hoặc trong bảng với điều kiện xác địnhDCOUNTA là công thức Excel nâng cao thường được sử dụng để đếm các ô không trống trong danh sách hoặc trong bảng với điều kiện xác địnhCú pháp : =DCOUNTA(database, field, criteria)

Database : Phạm vi dữ liệu bao gồm cột, ô, danh sách, cơ sở dữ liệu
Field : Chỉ rõ cột trong hàm, tiêu đề cột
Criteria : Pham vi ô chứa điều kiện.

Xem thêm: Cách làm trang tính Excel online trên máy tính

Công thức Excel nâng cao – Hàm VLOOKUP

Hàm Vlookup là một trong những hàm nâng cao thông dụng nhất trong Excel với nhiều ứng dụng, Hàm VLookup được sử dụng nhiều nhất trong việc tìm và tra cứu dữ liệu trong một bảng tính.

Cú pháp: =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

lookup_value: Giá trị dùng để tìm
table_array: Bảng giá trị để dò, sử dụng giá trị tuyệt đối bằng dấu $
col_index: Thứ tự các cột cần lấy dữ liệu trong bảng giá trị dòrange_lookup: Phạm vi tìm kiếm

Excel nâng cao #25. Thực hành tổng hợp các hàm EXCEL nâng cao - Bài 7 — Excel nâng cao #25. Thực hành tổng hợp các hàm EXCEL nâng cao – Bài 7

Hàm DGET

Hàm DGET là một trong những hàm Excel nâng cao được sử dụng nhiều trong quá trình thao tác với Excel, hàm DGET cho phép người dùng trích một giá trị từ cơ sở dữ liệu

Cú pháp DGET(database, field, criteria)Database : Phạm vi dữ liệu bao gồm cột, ô, danh sách, cơ sở dữ liệuField : Chỉ rõ cột trong hàm, tiêu đề cộtCriteria : Pham vi ô chứa điều kiệnLưu ý: Lỗi #VALUE: Không có giá trị thỏa mãn điều kiệnLỗi #NUM: Có quá nhiều bản ghi phù hợp điều kiệnVí dụ: Lấy giá trị trong bảng với điều kiện tổng điểm 12Kết quả trả về là 12 – là giá trị trong bảng thỏa mãn điều kiện điểm 12 trong cột Tổng điểm

IF kết hợp với AND / OR

Công thức: = IF (VÀ (C2> = C4, C2 <= C5), C6, C7)

Bất cứ ai dành nhiều thời gian trong các loại mô hình tài chính đều biết rằng các công thức IF lồng nhau có thể là một cơn ác mộng. Kết hợp IF với hàm AND hoặc OR có thể là cách tuyệt vời để giữ hoặc các công thức dễ kiểm toán hơn và cho người dùng khác hiểu. Trong ví dụ dưới đây, bạn sẽ thấy cách chúng tôi sử dụng các hàm riêng lẻ kết hợp để tạo ra một công thức nâng cao hơn.

Để biết chi tiết về cách thực hiện chức năng này trong Excel, vui lòng xem hướng dẫn miễn phí của chúng tôi về cách sử dụng IF với AND / OR .

TOP 10 thủ thuật Pivot Table nâng cao trong Excel

OFFSET kết hợp với SUM hoặc AVERAGE

Công thức: = SUM (B4: OFFSET (B4,0, E2-1))

Hàm OFFSET không phải là đặc biệt nâng cao, nhưng khi chúng ta kết hợp nó với các hàm khác như SUM hoặc AVERAGE, chúng ta có thể tạo ra một công thức khá tinh vi. Giả sử bạn muốn tạo một hàm động có thể tổng hợp một số lượng các ô. Với công thức SUM thông thường, bạn bị giới hạn tính toán tĩnh, nhưng bằng cách thêm OFFSET, bạn có thể di chuyển tham chiếu ô xung quanh.

Làm thế nào nó hoạt động. Để làm cho công thức này hoạt động, chúng tôi thay thế ô tham chiếu kết thúc của hàm SUM bằng hàm OFFSET. Điều này làm cho công thức động và ô được tham chiếu là E2 là nơi bạn có thể cho Excel biết có bao nhiêu ô liên tiếp bạn muốn thêm. Bây giờ chúng tôi đã có một số công thức Excel nâng cao!

Dưới đây là một ảnh chụp màn hình của công thức này phức tạp hơn một chút trong hành động.

Như bạn thấy, công thức SUM bắt đầu trong ô B4, nhưng nó kết thúc bằng một biến, là công thức OFFSET bắt đầu từ B4 và tiếp tục bằng giá trị trong E2 (“3”) trừ đi một. Điều này di chuyển kết thúc của công thức tổng trên 2 ô, tổng hợp 3 năm dữ liệu (bao gồm cả điểm bắt đầu). Như bạn có thể thấy trong ô F7, tổng của các ô B4: D4 là 15 là công thức bù trừ và tổng cho chúng ta.

Tìm hiểu cách xây dựng công thức này theo từng bước trong khóa học Excel nâng cao của chúng tôi .

CHOOSE

Công thức: = CHOOSE (lựa chọn, tùy chọn1, tùy chọn2, tùy chọn3)

Hàm CHOOSE rất tốt cho phân tích kịch bản trong mô hình tài chính. Nó cho phép bạn chọn giữa một số tùy chọn cụ thể và trả lại “lựa chọn” mà bạn đã chọn. Ví dụ: hãy tưởng tượng bạn có ba giả định khác nhau về tăng trưởng doanh thu trong năm tới: 5%, 12% và 18%. Sử dụng công thức CHOOSE bạn có thể trả về 12% nếu bạn nói Excel bạn muốn lựa chọn # 2.

Đọc thêm về phân tích kịch bản trong Excel .

XNPV và XIRR

Công thức: = XNPV (tỷ lệ chiết khấu, dòng tiền, ngày tháng)

Nếu bạn là nhà phân tích làm việc trong ngân hàng đầu tư , nghiên cứu cổ phần, hoặc lập kế hoạch và phân tích tài chính ( FP & A ), hoặc bất kỳ lĩnh vực tài chính doanh nghiệp nào khác yêu cầu chiết khấu dòng tiền thì các công thức này là một phao cứu sinh!

Đơn giản chỉ cần đặt, XNPV và XIRR cho phép bạn áp dụng ngày cụ thể cho từng luồng tiền riêng lẻ đang được chiết khấu. Vấn đề với các công thức NPV và IRR cơ bản của Excel là chúng giả sử khoảng thời gian giữa dòng tiền là bằng nhau. Thường xuyên, với tư cách là một nhà phân tích, bạn sẽ có những tình huống mà dòng tiền không được tính theo thời gian đồng đều, và công thức này là cách bạn khắc phục điều đó.

Để xem chi tiết hơn, hãy xem hướng dẫn công thức IRR và XIRR miễn phí của chúng tôi cũng như hướng dẫn XNPV của chúng tôi .

SUMIF và COUNTIF

Công thức: = COUNTIF (D5: D12, ”> = 21 ″)

Hai công thức tiên tiến này là sử dụng tuyệt vời các hàm có điều kiện. SUMIF thêm tất cả các ô đáp ứng các tiêu chí nhất định và COUNTIF tính tất cả các ô đáp ứng các tiêu chí nhất định. Ví dụ: hãy tưởng tượng bạn muốn đếm tất cả các ô lớn hơn hoặc bằng 21 (độ tuổi uống rượu hợp pháp ở Hoa Kỳ) để tìm ra số chai rượu sâm banh bạn cần cho một sự kiện khách hàng. Bạn có thể sử dụng COUNTIF làm giải pháp nâng cao, như được hiển thị trong ảnh chụp màn hình bên dưới.

PMT và IPMT

Công thức: = PMT (lãi suất, số kỳ, giá trị hiện tại)

Nếu bạn làm việc trong ngân hàng thương mại , bất động sản, FP & A hoặc bất kỳ vị trí phân tích tài chính nào đề cập đến lịch trình nợ, bạn sẽ muốn hiểu hai công thức chi tiết này.

Công thức PMT cung cấp cho bạn giá trị của các khoản thanh toán bằng nhau trong suốt thời gian vay. Bạn có thể sử dụng nó cùng với IPMT (cho bạn biết các khoản thanh toán lãi suất cho cùng một loại khoản vay), sau đó thanh toán tiền gốc và tiền lãi riêng.

Dưới đây là ví dụ về cách sử dụng chức năng PMT để nhận khoản thanh toán thế chấp hàng tháng cho khoản vay thế chấp 1 triệu đô la ở mức 5% trong 30 năm.

LEN và TRIM

Công thức: = LEN (văn bản) và = TRIM (văn bản)

Đây là những công thức ít phổ biến hơn, nhưng chắc chắn rất phức tạp. Các ứng dụng này rất tuyệt vời cho các nhà phân tích tài chính cần tổ chức và thao tác với lượng lớn dữ liệu. Thật không may, dữ liệu chúng tôi nhận được không phải lúc nào cũng được tổ chức hoàn hảo và đôi khi có thể có vấn đề như khoảng trống ở đầu hoặc cuối ô

Trong ví dụ dưới đây, bạn có thể xem cách thức công thức TRIM dọn sạch dữ liệu Excel.

CONCATENATE

Công thức: = A1 & ”văn bản khác”

Kết nối không thực sự là một chức năng riêng của nó, nó chỉ là một cách sáng tạo của việc tham gia thông tin từ các tế bào khác nhau, và làm cho bảng tính năng động hơn. Đây là một công cụ rất mạnh mẽ cho các nhà phân tích tài chính thực hiện mô hình tài chính (xem hướng dẫn lập mô hình tài chính miễn phí của chúng tôi để tìm hiểu thêm).

Trong ví dụ bên dưới, bạn có thể xem cách văn bản “New York” cộng “,” được kết hợp với “NY” để tạo “New York, NY”. Điều này cho phép bạn tạo tiêu đề động và nhãn trong trang tính. Bây giờ, thay vì cập nhật ô B8 trực tiếp, bạn có thể cập nhật ô B2 và D2 một cách độc lập. Với một tập dữ liệu lớn, đây là một kỹ năng có giá trị để bạn sử dụng.

Hàm CELL, LEFT, MID và RIGHT

Các hàm Excel nâng cao này có thể được kết hợp để tạo ra một số công thức rất tiên tiến và phức tạp để sử dụng. Hàm CELL có thể trả về nhiều thông tin khác nhau về nội dung của một ô (tên, vị trí, hàng, cột, v.v.). Hàm LEFT có thể trả về văn bản từ đầu ô (trái sang phải), MID trả về văn bản từ bất kỳ điểm bắt đầu nào của ô (trái sang phải) và hàm RIGHT trả về văn bản từ cuối ô (phải sang trái).

Dưới đây là minh họa cho ba công thức này.

Để xem cách chúng có thể được kết hợp một cách mạnh mẽ với hàm CELL, chúng tôi sẽ chia nhỏ nó cho bạn từng bước trong lớp công thức Excel nâng cao của chúng ta .

Chúc bạn thành công 1

Công thức Excel nâng cao – Hàm DSUM

Hàm DSUM là hàm Excel nâng cao có chức năng tính tổng 1 trường hay 1 cột trong CSDL với điều kiện cho trước

Cú pháp: =DSUM(database, field, criteria)

Database : Phạm vi dữ liệu bao gồm cột, ô, danh sách, cơ sở dữ liệu
Field : Chỉ rõ cột trong hàm, tiêu đề cột
Criteria : Pham vi ô chứa điều kiện

Cách sử dụng hàm Sumproduct trong Excel (cơ bản đến nâng cao)

Các khóa học Excel chất lượng tại EDUSA

8.1 Khóa học Excel 100% Online tại EDUSA

Để đáp ứng được tất cả nhu cầu của các bạn, EDUSA có đầy đủ các khóa học từ online đến offline giúp các bạn học viên tự do sắp xếp thời gian rảnh của mình để học và thi chứng chỉ tin học Excel, phục vụ cho nhu cầu công việc của các bạn.
Với xu hướng hiện nay, EDUSA đã tạo ra một khóa học 100% online dành cho các bạn có khả năng tự học và chủ động trong ôn luyện thi chứng chỉ. Khóa học này cung cấp cho bạn đầy đủ kiến thức và các bài tập thực hành trên phần mềm luyện thi trực tuyến. Từ đó, giúp các bạn làm quen và không bỡ ngỡ khi thi chính thức.

8.2 Khóa học Excel Online kèm GV tại EDUSA

Bên cạnh khóa học online 100%, EDUSA cũng có một khóa học online kèm GV giúp cho các bạn học sinh, sinh viên hay những bạn đi làm đang cần chứng chỉ tin học Excel trong thời gian nhanh nhất. Khóa học này tạo điều kiện cho học viên linh hoạt thời gian học phù hợp.
Các bài giảng sẽ được biên soạn, thiết kế theo một lộ trình cụ thể để đảm bảo các học viên có thể hiểu được những kiến thức cơ bản và thi một cách tốt nhất. Với cam kết 100% học viên có được chứng chỉ tin học Excel ngay lần thi đầu tiên, EDUSA cũng chuẩn bị những đề thi thử sát đề thi thật, giúp bạn có thể trải nghiệm và làm quen trước khi thi chính thức.

8.3 Khóa học Excel Offline tại EDUSA

Các lớp học tại EDUSA luôn được trang bị máy lạnh, ti vi,… để tạo một không gian thoải mái cho học viên khi đến với trung tâm. Và đội ngũ GV có chuyên môn cao, luôn vui vẻ, nhiệt tình giải thích những thắc mắc của học viên.
Mỗi học viên sẽ được học 1 chương trình trên máy tính riêng nên lộ trình học của mỗi bạn sẽ không ảnh hưởng đến nhau. Phần mềm ôn luyện được cập nhật thường xuyên phù hợp với từng ngày thi giúp các bạn có thể bám sát các đề thi. Bên cạnh đó, các giáo viên cũng có thể theo dõi và đánh giá chính xác khả năng của từng bạn để đưa ra cách dạy phù hợp, giúp học viên đạt được kết quả tốt nhất.
Trong trường hợp các bạn cảm thấy mình chưa đủ khả năng thi chính thức, các bạn có sẽ được sắp xếp thêm lịch học “miễn phí” để nắm vững kiến thức hơn và tự tin trong kỳ thi tin học văn phòng chính thức.

Hàm DCOUNTA

Hàm DCOUNTA có chức năng đếm các ô không trống trong danh sách hoặc trong bảng với điều kiện xác địnhDCOUNTA là hàm nâng cao trong excel thường được sử dụng để đếm các ô không trống trong danh sách hoặc trong bảng với điều kiện xác địnhCú pháp : DCOUNTA(database, field, criteria)Database : Phạm vi dữ liệu bao gồm cột, ô, danh sách, cơ sở dữ liệuField : Chỉ rõ cột trong hàm, tiêu đề cộtCriteria : Pham vi ô chứa điều kiện.Ví dụ : Đếm số học sinh đạt Xếp loại giỏi trong bảng điểm, cột Xếp loạiKết quả trả về là 2 – là số học sinh xếp loại giỏi trong bảng điểm với điều kiện Giỏi trong cột Xếp loại. Các bạn sử dụng câu lệnh như trong ví dụ dưới đây.

25 phím tắt TUYỆT VỜI trong Excel bạn nên biết (25 AWESOME Excel Keyboard Shortcuts)

10 Công thức Excel nâng cao bạn phải biết – Phần 1

Danh mục bài viết:

3. OFFSET kết hợp với SUM hoặc AVERAGE

Công thức: = INDEX (C3: E9, MATCH (B13, C3: C9,0), MATCH (B14, C3: E3,0))

Đây là một thay thế nâng cao cho các công thức VLOOKUP hoặc HLOOKUP (có một số hạn chế và hạn chế). INDEX MATCH là sự kết hợp mạnh mẽ của các công thức Excel sẽ đưa phân tích tài chính và mô hình tài chính của bạn lên một tầm cao mới.

INDEX trả về giá trị của một ô trong bảng dựa trên số cột và số hàng.
MATCH trả về vị trí của một ô trong một hàng hoặc cột.

Dưới đây là một ví dụ về các công thức INDEX và MATCH kết hợp với nhau. Trong ví dụ này, chúng tôi tìm kiếm và trả lại chiều cao của một người dựa trên tên của họ. Vì tên và chiều cao là cả hai biến trong công thức, chúng ta có thể thay đổi cả hai!

Công thức: = IF (AND (C2> = C4, C2 <= C5), C6, C7)

Bất cứ ai đã dành rất nhiều thời gian để thực hiện các loại mô hình tài chính khác nhau đều biết rằng các công thức IF lồng nhau có thể là một cơn ác mộng. Kết hợp IF với hàm AND hoặc hàm OR có thể là một cách tuyệt vời để giữ cho các công thức dễ kiểm toán hơn và dễ hiểu hơn cho người dùng khác. Trong ví dụ dưới đây, bạn sẽ thấy cách chúng tôi sử dụng các hàm riêng lẻ kết hợp để tạo ra một công thức nâng cao hơn.

3. OFFSET kết hợp với SUM hoặc AVERAGE

Công thức: = SUM (B4: OFFSET (B4,0, E2-1))

Bản thân hàm OFFSET không đặc biệt tiên tiến, nhưng khi chúng ta kết hợp nó với các hàm khác như SUM hoặc AVERAGE, chúng ta có thể tạo ra một công thức khá phức tạp. Giả sử bạn muốn tạo một hàm động có thể tổng hợp một số lượng ô khác nhau. Với công thức SUM thông thường, bạn bị giới hạn trong tính toán tĩnh, nhưng bằng cách thêm OFFSET, bạn có thể di chuyển tham chiếu ô xung quanh.

Cách thức hoạt động: Để làm cho công thức này hoạt động, chúng tôi thay thế ô tham chiếu kết thúc của hàm SUM bằng hàm OFFSET. Điều này làm cho công thức động và ô được tham chiếu là E2 là nơi bạn có thể cho Excel biết bạn muốn thêm bao nhiêu ô liên tiếp. Bây giờ chúng tôi đã có một số công thức Excel nâng cao!Dưới đây là một ảnh chụp màn hình của công thức hơi phức tạp hơn trong hành động.

Như bạn thấy, công thức SUM bắt đầu trong ô B4, nhưng nó kết thúc bằng một biến, đó là công thức OFFSET bắt đầu từ B4 và tiếp tục bằng giá trị trong E2 (Tạm thời 3), trừ đi một. Thao tác này sẽ kết thúc công thức tính tổng trên 2 ô, tổng 3 năm dữ liệu (bao gồm cả điểm bắt đầu). Như bạn có thể thấy trong ô F7, tổng các ô B4: D4 là 15, đây là công thức bù và tổng cho chúng ta.

Công thức: = CHOOSE (lựa chọn, tùy chọn1, tùy chọn2, tùy chọn3)

Hàm CHOOSE là tuyệt vời để phân tích kịch bản trong mô hình tài chính. Nó cho phép bạn chọn giữa một số tùy chọn cụ thể và trả về lựa chọn của bạn mà bạn đã chọn. Ví dụ, hãy tưởng tượng bạn có ba giả định khác nhau để tăng trưởng doanh thu trong năm tới: 5%, 12% và 18%. Sử dụng công thức CHỌN, bạn có thể trả lại 12% nếu bạn nói với Excel bạn muốn lựa chọn số 2.

Công thức: = XNPV (tỷ lệ chiết khấu, dòng tiền, ngày)

Nếu bạn là nhà phân tích làm việc trong ngân hàng đầu tư , nghiên cứu vốn cổ phần, hoặc lập kế hoạch & phân tích tài chính ( FP & A ) hoặc bất kỳ lĩnh vực tài chính doanh nghiệp nào khác yêu cầu chiết khấu dòng tiền, thì những công thức này là cứu cánh!Nói một cách đơn giản, XNPV và XIRR cho phép bạn áp dụng các ngày cụ thể cho từng dòng tiền riêng lẻ đang được chiết khấu. Vấn đề với các công thức NPV và IRR cơ bản của Excel là họ cho rằng khoảng thời gian giữa các dòng tiền là bằng nhau. Thông thường, với tư cách là một nhà phân tích, bạn sẽ có những tình huống mà dòng tiền không được tính thời gian đồng đều và công thức này là cách bạn khắc phục điều đó.Ở phần 1 này chúng ta đã tìm hiểu và nắm được 5 công thức Excel nâng cao và để có thể khai thác thêm về các công thức Excel nâng cao chúng ta cùng theo dõi và đọc bài viết ” 10 Công thức Excel nâng cao bạn phải biết – Phần 2 ”

Bạn đang muốn tìm kiếm 1 công việc với mức thu nhập cao.✅ Hoặc là bạn đang muốn chuyển đổi công việc mà chưa biết theo học ngành nghề gì cho tốt.✅ Giới thiệu với bạn Chương trình đào tạo nhân sự dài hạn trong 12 tháng với những điều đặc biệt mà chỉ có tại IMIC và đây cũng chính là sự lựa chọn phù hợp nhất dành cho bạn:👉 Thứ nhất: Học viên được đào tạo bài bản kỹ năng, kiến thức chuyên môn lý thuyết, thực hành, thực chiến nhiều dự án và chia sẻ những kinh nghiệm thực tế từ Chuyên gia có nhiều năm kinh nghiệm dự án cũng như tâm huyết truyền nghề.👉 Thứ hai: Được ký hợp đồng cam kết chất lượng đào tạo cũng như mức lương sau tốt nghiệp và đi làm tại các đối tác tuyển dụng của IMIC. Trả lại học phí nếu không đúng những gì đã ký kết.👉 Thứ ba: Cam kết hỗ trợ giới thiệu công việc sang đối tác tuyển dụng trong vòng 10 năm liên tục.👉 Thứ tư: Được hỗ trợ tài chính với mức lãi suất 0 đồng qua ngân hàng VIB Bank.👉 Có 4 Chương trình đào tạo nhân sự dài hạn dành cho bạn lựa chọn theo học. Gồm có:1) Data Scientist full-stack2) Embedded System & IoT development full-stack3) Game development full-stack4) Web development full-stack✅ Cảm ơn bạn đã dành thời gian lắng nghe những chia sẻ của mình. Và tuyệt vời hơn nữa nếu IMIC được góp phần vào sự thành công của bạn.✅ Hãy liên hệ ngay với Phòng tư vấn tuyển sinh để được hỗ trợ về thủ tục nhập học.✅ Chúc bạn luôn có nhiều sức khỏe và thành công!

Các công thức Excel nâng cao là công cụ để người dùng có thể thực hiện các phép tính, xử lý dữ liệu trong các bảng. Mỗi hàm trong Excel có nhiệm vụ và chức năng riêng giúp thực hiện các chức năng xử lý dữ liệu khác nhau. Sau đây, hãy cùng EDUSA tìm hiểu về các công thức Excel nâng cao thường dùng để có thể phục vụ tốt cho công việc của bạn nhé.

Lời kết

Trên đây là một số công thức Excel nâng cao mà EDUSA chia sẻ đến bạn. Những hàm nâng cao này không những được sử dụng độc lập mà còn có thể kết hợp với các hàm điều kiện khác như COUNTIF, IF, AND… để hỗ trợ các bạn tính toán nhanh hơn với các phép toán phức tạp khi xử lý dữ liệu trên bảng tính Excel.

Hướng dẫn được thực hiện trên Laptop hệ điều hành Windows 10 với phiên bản Excel 2013. Bạn cũng có thể thực hiện tương tự trên các sản phẩm Excel 2007, 2010, 2013, 2016, 2109 và trên các dòng laptop có cùng hệ điều hành hay dòng MacOS có cùng thao tác khác.

Thống kê là hệ thống các phương pháp thu thập, tổng hợp, trình bày và tính toán nhằm phục vụ cho quá trình phân tích và đưa ra kết luận.

Ứng dụng của các hàm thống kê trong Excel:

Giúp bạn tính toán dễ dàng các giá trị như: Tính tổng, giá trị lớn nhất, giá trị nhỏ nhất,…
Thao tác chuyên nghiệp hơn khi sử dụng Excel.
Kết hợp được với nhiều hàm khác để hỗ trợ công việc.

XEM NGAY bộ phần mềm chính hãng, sử dụng thả ga tại Thế Giới Di Động:

Hàm COUNT là hàm dùng để đếm ô trong một vùng hay trong toàn bộ bảng dữ liệu.

Cú pháp hàm COUNT: =COUNT(Value1, Value2,….).

Trong đó:

Value 1: Là giá trị đầu tiên để tham chiếu hoặc vùng chọn để đếm ô giá trị.
Value 2: Là tham số không bắt buộc, chỉ có thể để đếm tối đa 255 tham số, ô tham chiếu hoặc vùng chọn bổ sung mà bạn muốn đếm giá trị ô.

Hàm COUNTA là hàm đếm những ô tham chiếu có chứa dữ liệu trong một phạm vi nhất định.

Cú pháp hàm COUNTA: =COUNTA(Value1, [value2], [value3],…)

Trong đó:

Value1: Là đối số bắt buộc và là vùng dữ liệu cần đếm.
Value2 và Value3: Là các tùy chọn và tối đa 255 đối số.

Hàm COUNTIF là hàm đếm có điều kiện trong Excel và thường được dùng với những bảng dữ liệu thống kê.

Cú pháp hàm COUNTIF: =COUNTIF(range;criteria)

Trong đó:

Range: Là phần vùng muốn đếm dữ liệu bắt buộc phải có. Có thể chứa số, mảng hoặc tham chiếu có chứa số.
Criteria: Là phần điều kiện bắt buộc phải có để đếm các giá trị trong range. Có thể là số, biểu thức, tham chiếu ô hoặc chuỗi văn bản.

Hàm COUNTIFS là hàm giúp đếm số ô trong phạm vi khớp với điều kiện được cung cấp và có thể áp dụng nhiều hơn một điều kiện với nhiều hơn một phạm vi.

Cú pháp hàm COUNTIFS: =COUNTIFS(Criteria_range1,Criteria1,[Criteria_range2,Criteria2],…)

Trong đó:

range1: Là phạm vi đầu tiên để đánh giá.
criteria1: Là xác định phạm vi đầu tiên áp dụng điều kiện đầu tiên.
range2: Là phạm vi thứ hai để đánh giá, điều kiện bổ sung.
criteria2: Là xác định phạm vi áp dụng điều kiện thứ 2.

Ví dụ: Tìm số học sinh có điểm 6 cả 2 phần thi trong bảng dữ liệu sau:

Bước 1: Mình nhập hàm =COUNTIFS(C4:C8,6,D4:D8,6) vào bảng tính Excel.

Ví dụ minh họa hàm COUNTIFS

Bước 2: Sau đó bạn nhấn phím Enter để hiển thị kết quả.

Nhấn Enter để hiển thị kết quả

Hàm COUNTBLANK là hàm để đếm số các ô rỗng trong một vùng, một mảng được chọn trước.

Cú pháp hàm COUNTBLANK: COUNTBLANK(Range)

Trong đó:

Range: Là mảng được chọn để đếm số các ô rỗng trong đó.

Ví dụ: Tìm sô ô rỗng trong bảng dữ liệu sau:

Bước 1: Mình nhập hàm =COUNTBLANK(E4:E8) vào bảng tính Excel.

Ví dụ minh họa hàm COUNTBLANK

Bước 2: Sau đó bạn nhấn phím Enter để hiển thị kết quả.

Nhấn Enter để hiển thị kết quả

Hàm SUM là hàm tính tổng tất cả các số có trong dãy ô dữ liệu.

Cú pháp hàm SUM: =SUM(number1, [number2],…)

Trong đó:

Number1 và Number2 là những số hoặc dãy số sẽ tính.

Hàm SUMIF là hàm tính tổng có điều kiện được dùng khi cần tính tổng một vùng nào đó trong bảng tính excel theo điều kiện cho trước.

Cú pháp hàm SUMIF: =SUMIF(range, criteria,sum_range)

Trong đó:

Range: Là vùng được chọn có chứa các ô điều kiện.
Criteria: Là điều kiện để thực hiện hàm này.
Sum_range: Vùng cần tính tổng.

Hàm SUMIFS là hàm tính tổng các ô trong vùng, phạm vi lựa chọn thỏa mãn một hay nhiều điều kiện.

Cú pháp hàm SUMIFS: =SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,…)

Trong đó:

Sum_range: :Là các ô cần tính tổng bao gồm các số hoặc tên, phạm vi hoặc tham chiếu ô có chứa các số.
Criteria_range1: Là phạm vi cần được đánh giá bằng điều kiện.
Criteria1: Là điều kiện dưới dạng một số, biểu thức, tham chiếu ô.
Criteria_range2,criteria2,… : tùy chọn các phạm vi và điều kiện bổ sung.

Hàm AVERAGE là hàm tính trung bình trong Excel.

Cú pháp của hàm AVERAGE: =AVERAGE(number1;number2;…numbern)

Trong đó:

Number 1: Là số đầu tiên cần tính trung bình.
Number 2: Là số thứ 2 cần tính trung bình.
Number n: Số cuối cùng cần tính trung bình.

Hàm AVERAGEIF là hàm tính trung bình một điều kiện của tất cả các ô dựa trên điều kiện có sẵn.

Cú pháp hàm AVERAGEIF: =AVERAGEIF(range, criteria, [average_range])

Trong đó:

Range (Bắt buộc): Một hoặc nhiều ô để tính giá trị trung bình, bao gồm các số hoặc tên, mảng hoặc tham chiếu có chứa số.
Criteria (Bắt buộc): Điều kiện lấy các số dùng để tính trung bình. Ví dụ: Các tiêu chí có thể được biểu thị dưới dạng như 32, “32”, “>32”, “táo” hoặc B4.
Average_range (Tùy chọn): Vùng dữ liệu được chọn để dùng hàm AVERAGEIF (nếu bỏ qua thì mặc định chọn toàn bộ).

Hàm AVERAGEIFS là hàm tính giá trị trung bình nhiều điều kiện của tất cả các ô dựa trên các điều kiện có sẵn.

Cú pháp hàm AVERAGEIFS: =AVERAGEIFS(range, criteria, [average_range])

Trong đó:

Range (Bắt buộc): Một hoặc nhiều ô để tính giá trị trung bình, bao gồm các số hoặc tên, mảng hoặc tham chiếu có chứa số.
Criteria_range1 (bắt buộc), các đối số criteria_range tiếp theo (tùy chọn): Phạm vi 1 đến 127 để đánh giá các tiêu chí liên quan.
Criteria1 (bắt buộc), các đối số criteria tiếp theo (tùy chọn): 1 đến 127 tiêu chí dưới dạng số, biểu thức, tham chiếu ô hoặc văn bản xác định các ô sẽ tính giá trị trung bình.

Hàm RANK là hàm được dùng để sắp xếp, phân hạng dữ liệu, số liệu.

Cú pháp hàm RANK: =RANK(number,ref, [order])

Trong đó:

Number: Giá trị cần xếp hạng trong khối.
Ref: Danh sách, khối dữ liệu cần sắp xếp.
Order: Thứ tự cần sắp xếp, kiểu sắp xếp (tăng dần hoặc giảm dần). Trong trường hợp order = 0 (hoặc không có tham số này) thì sẽ tính từ cao xuống thấp. Còn order = 1 sẽ xếp từ thấp lên cao.

Hàm ROW là hàm giúp tìm nhanh vị trí hàng của ô đang tham chiếu hoặc hàng đầu tiên của mảng tham chiếu dễ dàng.

Cú pháp hàm ROW: =ROW([reference])

Trong đó:

Reference: Là ô hoặc phạm vi ô mà bạn muốn trả về số hàng.

Hàm ROWS là hàm giúp tính nhanh số lượng hàng có trong dữ liệu đang tham chiếu.

Cú pháp hàm ROWS: =ROWS(array)

Trong đó:

Array: Là tham chiếu đến phạm vi một ô hoặc một vùng mà bạn muốn lấy số hàng.

Hàm COLUMN là hàm giúp tìm nhanh vị trí cột của ô hoặc vùng đang tham chiếu.

Cú pháp hàm COLUMN: =COLUMN([reference])

Trong đó:

Reference: Là ô hoặc vùng mà bạn muốn trả về số cột.

Hàm COLUMNS là hàm giúp trả về số cột trong một mảng hoặc ô tham chiếu.

Cú pháp hàm COLUMNS: =COLUMNS(array)

Trong đó:

Array: Là tham chiếu đến phạm vi một ô hoặc một vùng mà bạn muốn lấy số cột.

Ví dụ: Đếm số cột có trong bảng dữ liệu sau:

Bước 1: Mình nhập hàm =COLUMNS(A3:F7) vào bảng tính Excel.

Ví dụ minh họa hàm COLUMNS

Bước 2: Sau đó bạn nhấn phím Enter để hiển thị kết quả.

Nhấn Enter để hiển thị kết quả

Hàm MIN là hàm trả về giá trị nhỏ nhất trong tập giá trị cho trước.

Cú pháp hàm MIN: =MIN (number 1, number 2, …)

Trong đó:

Number1, Number2, … Là các đối số hoặc vùng dữ liệu.

Hàm MAX là hàm trả về giá trị lớn nhất trong tập giá trị cho trước.

Cú pháp hàm MAX: =MAX(number 1, number 2, …)

Trong đó:

Number1, Number2, … Là các đối số hoặc vùng dữ liệu.

Nguyên nhân: Sử dụng sai dấu phân cách giữa các phần tử trong hàm

Cách khắc phục: Sử dụng đúng dấu phân cách hoặc chỉnh sửa lại định dạng

Nguyên nhân lỗi: Không có ký tự phụ hoặc chưa chuyển định dạng.

Nguyên nhân viết không được số 0 trong Excel

Cách khắc phục: Thêm ký tự dấu ‘ vào trước số 0 hoặc chuyển sang định dạng Text. Ở đây mình hướng dẫn thêm dấu ‘ như hình.

Khắc phục lỗi viết không được số 0 trong Excel

Mục đích là để dễ dàng thực hiện hay kiểm soát khối lượng dữ liệu trong bảng tính hiệu quả nhất.

Là lỗi dữ liệu kiểu số. Trong Excel thường có 3 nguyên nhân chính gây nên lỗi #NUM!.

Trong công thức có đối số có dữ liệu là kiểu số không phù hợp.
Dùng hàm lặp đi lặp lại dẫn đến hàm không thể tìm ra kết quả trả về.
Dùng hàm trả về kết quả là số quá lớn hoặc quá nhỏ vượt ngoài khả năng tính toán của Excel.

Là lỗi hiển thị giá trị kết quả bị lỗi trong quá trình tính toán. Phần lớn thường gặp là do quá trình nhập công thức hoặc do các ô đang tham chiếu bị lỗi.

Là lỗi phép tính khi chia cho số 0 hay sai hàm khi thực hiện phép chia cho số 0.

Là lỗi không tìm thấy giá trị hay là lỗi được trả về khi công thức không tìm thấy giá trị.

Trên đây là Tổng hợp các hàm thống kê trong Excel cực chi tiết, có bài tập minh họa dễ hiểu. Hy vọng bài viết sẽ giúp bạn thực hiện được các hàm thống kê trong công việc cũng như học tập và nếu bạn có góp ý hãy để lại bình luận bên dưới và đứng quên chia sẻ nếu thấy hữu ích bạn nhé.

Bài viết này có thể xem là bài viết tổng hợp nhiều chức năng của Microsoft Excel, rất cần thiết đối với người mới bắt đầu học về Excel. Trong bài viết này, Học Excel Online sẽ liệt kê các hàm trong excel, các văn bản, phép toán, thống kê, logic và các chức năng khác cần thiết nhất cùng với các ví dụ về công thức và liên kết tới các tài nguyên có liên quan.

Excel cung cấp một số lượng lớn các chức năng để phân tích, kiểm toán và tính toán dữ liệu bằng cách tận dụng tối đa các hàm trong excel cung cấp. Một trong số những chức năng này được sử dụng bởi nhiều người sử dụng Excel hàng ngày và cũng có một số người chỉ sử dụng những phép toán này trong trường hợp cụ thể.

Trong hướng dẫn này, chúng ta sẽ không khám phá từng chức năng trong Microsoft Excel mà chỉ đưa ra cho bạn cái nhìn tổng quan về những chức năng hữu ích trong Excel đặc biệt là đưa ra cái nhìn tổng quan về cách dùng hàm trong excel được sử dụng nhiều nhất.

Hầu hết các chức năng được liệt kê dưới đây là các hàm bảng tính Excel được sử dụng như một phần của công thức trong một ô. Các mục cuối cùng bao gồm các chức năng Excel tùy chỉnh, chức năng người dùng định nghĩa (UDF). Đây là những chức năng dựa trên VBA để mở rộng những điều cơ bản của Excel với chức năng tuỳ chỉnh. Chọn một danh mục để vào danh sách các chức năng với các mô tả ngắn và liên kết đến các ví dụ sử dụng.

Xem ngay: Các hàm thông dụng trong Excel

Xem nhanh

Có rất nhiều chức năng của Microsoft Excel để áp dụng cho chuỗi văn bản. Dưới đây là những điều cơ bản nhất về các hàm cơ bản trong excel:

TEXT(value, format_text) được sử dụng để chuyển đổi một số hoặc một ngày thành một chuỗi kiểm tra ở định dạng được chỉ định, trong đó:

value là một giá trị số bạn muốn chuyển đổi sang văn bản.Format_text là định dạng mong muốn.Các công thức sau đây thể hiện hoạt động của chức năng Excel TEXT:

=TEXT(A1,”mm/dd/yyyy”) – chuyển ngày tháng trong ô A1 sang một chuỗi văn bản theo định dạng ngày truyền thống của Hoa Kỳ, chẳng hạn như “01/01/2015” (tháng / ngày / năm).

=TEXT(A1,”€#,##0.00″) – chuyển một số trong A1 thành một chuỗi ký tự tiền tệ như “€3.00”.

Hàm TEXT dùng để chuyển đổi một ngày sang định dạng văn bảnCông TEXT dùng để chuyển một số thành dạng văn bản

CONCATENATE(text1, [text2], …) được thiết kế để nối nhiều đoạn văn bản lại với nhau hoặc kết hợp các giá trị từ một vài ô vào một ô duy nhất. Một kết quả tương tự có thể đạt được bằng cách sử dụng Excel & operator, như thể hiện trong ảnh chụp màn hình sau.

TRIM(text) được dùng để loại bỏ hàng đầu, dấu cách cũng như các khoảng trống dư thừa giữa các từ. Trường hợp văn bản hoặc một chuỗi văn bản hay tham chiếu đến ô có chứa văn bản mà bạn muốn loại bỏ không gian. Ảnh chụp màn hình sau minh họa một ví dụ về cách sử dụng:

SUBSTITUTE(text, old_text, new_text, [instance_num]) sẽ thay thế một bộ ký tự bằng một ký tự khác trong một ô được chỉ định hoặc một chuỗi văn bản. Cú pháp của hàm SUBSTITUTE như sau:

Text – chuỗi văn bản ban đầu hoặc tham chiếu đến ô mà bạn muốn thay thế một số ký tự nhất định.Old_text – các ký tự bạn muốn thay thế.New_text – các ký tự mà bạn mới bạn muốn dùng để thế chỗ văn bản cũ.Nth_appearance – một tham số tùy chọn cho old_text mà bạn muốn thay thế bằng new_text. Sau đó, mỗi lần xuất hiện của văn bản cũ sẽ được thay thế bằng văn bản mới.Ví dụ: công thức SUBSTITUTE sau thay thế tất cả các dấu phẩy trong ô A1 bằng dấu chấm phẩy:

=SUBSTITUTE(A2, “,”, “;”)

– Loại bỏ ngắt dòng trong một ô– Chuyển đổi chuỗi văn bản với dấu phân cách tùy chỉnh thành ngày

VALUE(text) – chuyển đổi một chuỗi văn bản thành một số.

Hàm này thực sự hữu ích khi chuyển đổi các giá trị định dạng văn bản đại diện cho các con số thành các con số có thể được sử dụng trong các công thức Excel khác và tính toán.

Chuyển đổi chữ số định dạng văn bản thành sốHàm VALUE dùng để chuyển đổi văn bản thành ngàyHàm EXACTEXACT(text1, text2) so sánh hai chuỗi văn bản và trả về giá trị TRUE nếu cả hai giá trị đều giống nhau, và trả về FALSE nếu không giống nhau.

Ví dụ: nếu A2 là “apples” và B2 là “Apples”, công thức =EXACT(A2, B2) sẽ trả về FALSE vì chúng không khớp chính xác.

Hàm EXACT hiếm khi được sử dụng riêng, nhưng lại hữu ích khi kết hợp với những hàm khác để tạo nên tác vụ phức tạp hơn, ví dụ như kết hợp với Vlookup dùng thao tác với văn bản trong Excel.

Microsoft Excel cung cấp 3 hàm văn bản để chuyển đổi giữa UPPER, LOWER, PROPER.

UPPER(text) – chuyển đổi tất cả các ký tự trong một chuỗi ký tự được chỉ định sang chữ hoa.

LOWER(text) – thay đổi tất cả các chữ in hoa trong một chuỗi văn bản thành chữ thường.

PROPER(text) – viết hoa chữ cái đầu tiên của mỗi từ và chuyển đổi tất cả các chữ cái khác sang chữ thường.

Trong cả ba chức năng, đối số văn bản có thể là một chuỗi văn bản kèm theo dấu ngoặc kép, một tham chiếu đến một ô có chứa văn bản hoặc một công thức trả về văn bản.

Nếu bạn cần một công thức để trả về một số ký tự nhất định từ một chuỗi văn bản, sử dụng một trong các hàm Excel sau.

LEFT(text, [num_chars]) – trả về một số ký tự nhất định bắt đầu từ đầu chuỗi văn bản.

RIGHT(text,[num_chars]) – trả về một số ký tự được chỉ định bắt đầu từ cuối chuỗi văn bản.

MID(text, start_num, num_chars) – trả về một số ký tự nhất định từ một chuỗi văn bản, bắt đầu ở bất kỳ vị trí nào bạn chỉ định.

Trong các hàm này, bạn cung cấp các đối số sau:

– Text – một chuỗi văn bản hoặc một tham chiếu đến một ô chứa các ký tự mà bạn muốn trích xuất.– Start_num – chỉ ra nơi bắt đầu (tức là vị trí của ký tự đầu tiên bạn muốn trích xuất).– Num_chars – số ký tự bạn muốn trích xuất.

Microsoft Excel cung cấp một số ít các chức năng logic đánh giá một điều kiện xác định và trả về giá trị tương ứng.

AND, OR, XOR chức năngAND(logical1, [logical2], …) – trả về TRUE nếu tất cả các đối số đánh giá TRUE, FALSE nếu ngược lại.

OR(logical1, [logical2], …) – trả về TRUE nếu ít nhất một trong các đối số là TRUE.

XOR(logical1, [logical2],…) – trả về một giá trị hợp lệ duy nhất trong tất cả các đối số. Chức năng này đã được giới thiệu trong Excel 2013 và không có sẵn trong các phiên bản trước đó.

NOT(logical) – đảo ngược giá trị đối số của nó, nghĩa là nếu đánh giá lôgic thành FALSE, hàm NOT sẽ trả về TRUE và ngược lại.

Ví dụ, cả hai công thức sau đây sẽ trả về FALSE:

=NOT(TRUE)

=NOT(2*2=4)

Hàm IF trong Excel đôi khi được gọi là “hàm điều kiện” vì nó trả về một giá trị dựa trên điều kiện mà bạn chỉ định. Cú pháp của hàm IF như sau:

IF(logical_test, [value_if_true], [value_if_false])Công thức IF kiểm tra các điều kiện được biểu diễn trong đối số logical_test và trả về một giá trị (value_if_true) nếu điều kiện được đáp ứng và một giá trị khác (value_if_false) nếu điều kiện không được đáp ứng.

Ví dụ, công thức = IF =IF(A1<>””, “good”, “bad”) trả về “good” nếu có bất kỳ giá trị nào trong ô A1, “bad” nếu không.

Và đây là một ví dụ về công thức hàm IF kết hợp thể hiện điểm thi trong ô A2:

=IF(A2>80, “Brilliant”, IF(A2>50, “Good”, IF(A2>30, “Fair”, “Poor”)))

Cả hai hàm đều được sử dụng để kiểm tra lỗi trong một công thức tính nhất định, và nếu có lỗi xảy ra, các hàm MS Excel trả lại một giá trị được chỉ định thay thế.

IFERROR(value, value_if_error) – kiểm tra lỗi của công thức hoặc biểu thức đánh giá. Nếu có lỗi, công thức trả về giá trị được cung cấp trong đối số value_if_error, nếu không, kết quả của công thức sẽ được trả về. Hàm này xử lý tất cả các lỗi Excel có thể xảy ra, bao gồm VALUE, N/A, NAME, REF, NUM và những lỗi khác. Nó có sẵn trong Excel 2007 và những phiên bản cao hơn.

IFNA(value, value_if_na) – được giới thiệu trong Excel 2013, nó hoạt động tương tự như IFERROR, nhưng chỉ xử lý lỗi #N/A.

Các ví dụ sau minh họa công thức IFERROR đơn giản nhất:

Excel có vô vàn chức năng cơ bản và tiên tiến để thực hiện các phép toán, tính toán hàm mũ, các đối số, yếu tố và các dạng tương tự. Chúng ta hẳn sẽ mất tới vài trang chỉ để xuất bản danh sách các chức năng. Vì vậy, chúng ta hãy thảo luận về một vài chức năng toán học cơ bản có thể hữu ích cho việc giải quyết các công việc hàng ngày của bạn.

Bốn hàm trong Excel cần thiết để thêm giá trị của các ô trong một phạm vi xác định theo dưới đây.

SUM(number1,[number2],…) trả về tổng các đối số của nó. Các đối số có thể là các số, các tham chiếu ô hoặc các giá trị số bằng công thức.

Ví dụ, công thức toán học đơn giản =SUM(A1:A3, 1) cộng các giá trị trong ô A1, A2 và A3, và thêm 1 vào kết quả.Các hàm SUMIF và SUMIFS (tổng điều kiện)Cả hai hàm này dùng để cộng các ô trong một phạm vi xác định đáp ứng một điều kiện nhất định. Sự khác biệt là SUMIF có thể đánh giá chỉ một tiêu chuẩn, trong khi SUMIFS, được giới thiệu trong Excel 2007, cho phép sử dụng nhiều tiêu chí. Hãy lưu ý rằng thứ tự của các đối số là khác nhau trong mỗi chức năng:

SUMIF(range, criteria, [sum_range]) SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)Range / criteria_range – phạm vi của các ô được đánh giá bởi các tiêu chí tương ứng.criteria – điều kiện cần phải đáp ứng.Sum_range – các ô tổng hợp nếu điều kiện được đáp ứng.Ảnh chụp màn hình sau đây cho biết ý nghĩa của các chức năng SUMIF và SUMIFS trên dữ liệu thực tế:

SUMPRODUCT(array1,array2, …) là một trong số ít các chức năng của Microsoft Excel dùng để xử lý mảng. Nó kết hợp với các thành phần mảng được cung cấp và trả về tổng sản phẩm.

Bản chất của hàm SUMPRODUCT có thể khó nắm bắt vì vậy các bạn có thể theo dõi các ví dụ cụ thể của hàm này trong bài viết dành riêng cho hàm SUMPRODUCT để có được hiểu biết đầy đủ.

Tạo các số ngẫu nhiên (RAND và RANDBETWEEN)Microsoft Excel cung cấp 2 chức năng để tạo ra các số ngẫu nhiên. Cả hai đều là các hàm không ổn định, có nghĩa là một số mới được trả về mỗi khi bảng tính thực hiện tính toán.

RAND() – trả về một số thực (số thập phân) ngẫu nhiên giữa 0 và 1.

RANDBETWEEN(bottom, top) – trả về một số nguyên ngẫu nhiên giữa số dưới cùng và số trên cùng mà bạn chỉ định.

Có một số chức năng để làm tròn số trong Excel, và Hướng dẫn làm tròn của Excel của chúng tôi đã giúp bạn giải thích cách sử dụng những chức năng đó dựa trên tiêu chí của bạn. Vui lòng nhấp vào tên của chức năng để tìm hiểu cú pháp và ví dụ về sử dụng.

ROUND – làm tròn số đến số chữ số được chỉ định.

ROUNDUP – làm tròn lên, đến số ký tự được chỉ định.

ROUNDDOWN – làm tròn xuống, đến số ký tự được chỉ định.

MROUND – làm tròn số đến một bội số được chỉ định.

FLOOR – làm tròn số xuống, đến bội số được chỉ định.

CEILING – tròn số lên, đến bội số được chỉ định.

INT – làm tròn số xuống số nguyên gần nhất.

TRUNC – cắt bớt số số đến số nguyên gần nhất.

EVEN – làm tròn số đến số nguyên chẵn gần nhất.

ODD – làm tròn số lên đến số nguyên lẻ gần nhất.

MOD(number, divisor) trả về số sư sau khi chia.

Hàm này thực sự rất hữu ích trong nhiều trường hợp khác nhau, ví dụ như:

Tính tổng các giá trị trong mỗi hàng, hoặc một hàng được chỉ địnhThay đổi màu sắc của hàng trong Excel

Trong số nhiều hàm thống kê của Excel, có một số hàm có thể áp dụng để sử dụng cho những công việc mang tính chuyên nghiệp cao.

MIN(number1, [number2], …) – trả về giá trị nhỏ nhất từ danh sách các đối số.

MAX(number1, [number2], …) – trả về giá trị lớn nhất từ danh sách các đối số

AVERAGE(number1, [number2], …) – trả giá trị trung bình của các đối số.

SMALL(array, k) – trả về giá trị nhỏ nhất thứ k trong mảng.

LARGE (mảng, k) – trả về giá trị lớn nhất thứ k trong mảng.

Ảnh chụp màn hình sau minh họa các hàm thống kê cơ bản.

Dưới đây là danh sách các hàm Excel cho phép bạn đếm các ô có chứa một kiểu dữ liệu nhất định hoặc dựa trên các điều kiện mà bạn chỉ định.

COUNT(value1, [value2], …) – trả về số lượng các giá trị số (số và ngày) trong danh sách các đối số.

COUNTA(value1, [value2], …) – trả về số ô có chứa dữ liệu trong danh sách các đối số. Nó đếm các ô có chứa bất kỳ thông tin, bao gồm các giá trị lỗi và các chuỗi văn bản rỗng (“”) được trả về bởi các công thức khác.

COUNTBLANK(range) – đếm số ô trống trong một phạm vi xác định. Các ô có chuỗi văn bản trống (“”) cũng được tính là các ô trống.

COUNTIF(range, criteria) – tính số ô trong phạm vi đáp ứng các tiêu chí được chỉ định.

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…) – đếm số ô đáp ứng được tất cả các tiêu chí được chỉ định.

Ảnh chụp màn hình sau minh họa cách hoạt động của các hàm đếm ô:Chú thích. Khi ngày tháng được lưu trữ như số trong Excel, chúng cũng được đếm bởi các chức năng thống kê của Excel. Ví dụ: công thức =COUNTIF(A2:A9, “>5”) đếm ngày trong ô A8 trong ảnh chụp màn hình ở trên vì nó được lưu trữ dưới dạng 42005 trong hệ thống Excel.

Các hàm dạng này của MS Excel có ích khi bạn cần tìm thông tin nhất định trong một bảng dựa trên một giá trị trong một cột hoặc trả về một tham chiếu đến một ô nhất định.

Hàm VLOOKUP tìm một giá trị được chỉ định trong cột đầu tiên và kéo dữ liệu phù hợp từ cùng một hàng trong một cột khác.

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])Lookup_value – giá trị cần tìm kiếm.Table_array – hai hoặc nhiều cột dữ liệu.Col_index_num – số cột để kéo dữ liệu.Range_lookup – xác định tìm kiếm là hoàn toàn chính xác (FALSE) hay chỉ tương đối chính xác (TRUE hoặc bỏ qua).Ví dụ: công thức =VLOOKUP(“apples”, A2:C10, 3) tìm kiếm “apples” trong ô A2 đến A10 và trả về giá trị phù hợp từ cột C:

INDEX(array, row_num, [column_num]) – trả về một tham chiếu đến một ô bên trong mảng dựa trên số hàng và cột mà bạn chỉ định.

Đây là một công thức INDEX đơn giản: =INDEX(A1:C10, 3 ,4) hàm này tìm trong ô A1 đến C10 và trả về giá trị tại giao điểm của hàng thứ 3 và cột 4, đó là ô D3.

MATCH(lookup_value, lookup_array, [match_type]) – tìm kiếm lookup_value trong lookup_array, và sau đó trả về vị trí tương đối của mục đó trong dãy.

Sự kết hợp giữa các hàm MATCH và INDEX có thể được sử dụng như là một thay thế mạnh mẽ và linh hoạt hơn cho Vlookup của Excel.

INDIRECT(ref_text, [a1]) – trả về một tham chiếu ô hoặc dải ô được chỉ định bởi một chuỗi văn bản.

Dưới đây là một ví dụ về công thức đơn giản nhất:

OFFSET(reference, rows, cols, [height], [width]) – trả về một tham chiếu đến một dãy ô được tính từ một ô bắt đầu hoặc một dãy các ô theo số hàng và cột được chỉ định.

Ví dụ, =OFFSET(A1, 1, 2) trả về giá trị trong ô C2 vì nó là 1 hàng xuống và 2 cột bên trái tính từ A1.

TRANSPOSE(array) – biến đổi một khoảng nằm ngang của các ô thành một dải đứng và ngược lại, tức là chuyển hàng thành các cột và cột thành hàng.

HYPERLINK(link_location, [friendly_name]) – Tạo một siêu liên kết đến một tài liệu được lưu trữ trên mạng nội bộ hoặc Internet.

Microsoft Excel cung cấp một loạt các chức năng để đơn giản hóa công việc của các nhà quản lý kế toán, các nhà phân tích tài chính và các chuyên gia về ngân hàng. Dưới đây là một trong số những hàm chính có thể được sử dụng để tính lãi suất.

FV(rate, nper ,pmt ,[pv], [type]) – tính giá trị tương lai của một khoản đầu tư dựa trên lãi suất cố định.

Đối với những người sử dụng Excel thường xuyên, các hàm ngày tháng ngày càng trở nên quen thuộc hơn và được sử dụng trong rất nhiều trường hợp khác nhau.

DATE – trả về một ngày được chỉ định dưới dạng số sê ri.DATEVALUE – chuyển đổi một chuỗi văn bản đại diện cho ngày để định dạng ngày.

TODAY – trả về ngày hiện tại.NOW – trả về ngày và thời gian hiện tại.

DAY – trả về ngày trong tháng.MONTH – trả về tháng của một ngày được chỉ định.YEAR – trả về năm của một ngày nhất định.EOMONTH – trả về ngày cuối cùng của tháng.WEEKDAY – trả về ngày trong tuần.WEEKNUM – trả về số tuần của một ngày.

DATEDIF – trả về sự khác biệt giữa hai ngày.EDATE – trả về một ngày nằm trong tháng đã định trước, có thể đứng trước hoặc sau ngày bắt đầu.YEARFRAC – trả về tỷ lệ của một khoảng thời gian trong một năm.

WORKDAY – Trả về một số tuần tự thể hiện số ngày làm việc, có thể là trước hay sau ngày bắt đầu làm việc và trừ đi những ngày cuối tuần và ngày nghỉ (nếu có) trong khoảng thời gian đó.WORKDAY.INTL – tính ngày tháng là số ngày trong tuần được chỉ định trước hoặc sau ngày bắt đầu, với thông số cuối tuần tùy chỉnh.NETWORKDAYS – trả về số ngày làm việc giữa hai ngày được chỉ định.NETWORKDAYS.INTL – trả về số ngày làm việc giữa hai ngày được chỉ định với ngày cuối tuần tùy chỉnh.

Dưới đây là danh sách các hàm Excel chính dùng làm việc với thời gian.

TIME(hour, minute, second) – trả về thời gian dưới dạng số sê ri.

TIMEVALUE(time_text) – chuyển đổi một thời gian nhập dưới dạng một chuỗi văn bản thành một số thể hiện thời gian dưới dạng số sê ri.

NOW() – trả về số tương ứng với ngày và thời gian hiện tại dạng số sê ri.

HOUR(serial_number) – chuyển đổi một số thành một giờ dạng số sê ri.

MINUTE(serial_number) – chuyển đổi một số thành phút dưới dạng số sê ri.

SECOND(serial_number) – chuyển đổi một số thành giây dưới dạng số sê ri.

Microsoft Excel không có bất kỳ chức năng tích hợp nào để đếm và tính tổng các ô theo màu sắc, vì vậy Học Excel Online đã tạo ra một số tùy chỉnh. Các chức năng này được viết bằng VBA, và bạn có thể thêm chúng vào các bảng tính trong môi trường lập trình của Excel – Visual Basic for Applications. Dưới đây là danh sách các hàm của Excel:

GetCellColor(cell) – trả về mã màu của màu nền thuộc một ô xác định.GetCellFontColor(cell) – trả về mã màu của màu phông chữ thuộc một ô xác định.CountCellsByColor(range, color code) – đếm các ô có màu nền được chỉ định.CountCellsByFontColor(range, color code) – đếm các ô có màu phông chữ được chỉ định.SumCellsByColor(range, color code) – tính tổng của các ô có màu nền nhất định.SumCellsByFontColor(range, color code)) – trả về tổng của các ô với một màu chữ nhất định.WbkCountCellsByColor(cell) – tính các ô có màu nền được chỉ định trong toàn bộ bảng tính.WbkSumCellsByColor(cell) – tính tổng các ô với màu nền được chỉ định trong toàn bộ bảng tính.

Tất nhiên, Microsoft Excel có nhiều hàm hơn những hàm được liệt kê ở đây rất nhiều, tuy nhiên đây là những hàm vô cùng thiết yếu và phục vụ nhiều cho công việc của bạn.

Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…

Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học dưới đây:

admin

4,727

14-07-2021

Muốn sử dụng excel hiệu quả trong công việc tính toán và thống kê số liệu, việc đầu tiên đòi hỏi các bạn cần học đó chính là các hàm logic trong phần mềm này. Có rất nhiều tính năng khác nhau trong excel; và nếu các bạn biết nên áp dụng công thức nào khi gặp trường hợp cụ thể; thì chắc chắn công việc của các bạn sẽ được tiến hành thuận lợi; và đem lại kết quả nhanh chóng.

Để giúp các bạn có thêm kiến thức về các hàm trong excel, sau đây Cole xin trình bày các hàm excel từ cơ bản đến nâng cao được nhân viên sử dụng phổ biến nhất trong tin học văn phòng.

Công dụng: giúp người sử dụng tính tổng giá trị

Cú pháp: =SUM(số 1, số 2,…, số hoặc vùng dữ liệu)

Ví dụ: =SUM(4,5,1) rồi Enter sẽ ra kết quả

Công dụng: giúp trả về trung bình cộng các tham số đưa vào hoặc của một vùng dữ liệu

Ví dụ: =AVERAGE(7,8,9) rồi Enter sẽ ra kết quả là 8

Công dụng: trả về giá trị 1 nếu điều kiện đúng, Hàm trả về giá trị 2 nếu điều kiện sai.

Cú pháp: =If(Điều kiện, Giá trị 1, Giá trị 2).

Ví dụ:

= IF(B2>=4,“DUNG”,“SAI”) = DUNG.

= IF(B2>=5,“DUNG”,“SAI”) = SAI

Công dụng: trả về giá trị dò tìm theo cột đưa từ bảng tham chiếu lên bảng cơ sở dữ liệu theo đúng giá trị dò tìm.

Cú pháp: =VLOOKUP(giá trị dò, bảng dò, cột giá trị trả về, kiểu dò)

X=0 là dò tìm một cách chính xác. X=1 là dò tìm một cách tương đối.

Ví dụ như dùng để:

Các đối số: Logical1, Logical2… là các biểu thức điều kiện.

Công dụng: là phép VÀ, chỉ đúng khi tất cả các đối số có giá trị đúng. Các đối số là các hằng, biểu thức logic. Hàm trả về giá trị TRUE (1) nếu tất cả các đối số của nó là đúng, trả về giá trị FALSE (0) nếu một hay nhiều đối số của nó là sai.

Cú pháp: =AND(đối 1, đối 2,..).

Ví dụ: =AND(D7>0,D7<5000)

Lưu ý:

Công dụng: là phép HOẶC, chỉ sai khi tất cả các đối số có giá trị sai. Hàm trả về giá trị TRUE (1) nếu bất cứ một đối số nào của nó là đúng, trả về giá trị FALSE (0) nếu tất cả các đối số của nó là sai.

Cú pháp: OR(đối 1, đối 2,..).

Các đối số: Logical1, Logical2… là các biểu thức điều kiện.

Ví dụ: =OR(F7>03/02/74,F7>01/01/2013)

Tại ô nhập kết quả phần trăm, gõ cú pháp =giá trị cần tính phần trăm/ giá trị tổng và Enter. Kết quả nhận được sẽ có giá trị thập phân lớn hơn 0, nhỏ hơn 1 và kéo kết quả ô đầu xuống những cô còn lại để copy công thức.

Rất là đơn giản đúng không nào, hãy áp dụng thử và xem kết quả như thế nào nhé.

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:

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ữ

Hàm ROUND sẽ giúp chúng ta làm tròn số như chúng ta mong muốn.

Với cú pháp: =ROUND(number, num_digists)

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 có công thức giống với hàm ROUND.

Tuy nhiên:

Và đó là tất cả hàm trong excel từ cơ bản đến nâng cao mà Cole tổng hợp được và cung cấp cho các bạn. Mong rằng sẽ giúp ích khi các bạn muốn tính toán hay xử lý số liệu một cách nhanh chóng và hiệu quả. Khám phá thêm các hàm Excel tại khóa học tin học văn phòng Cole

>> 12 thủ thuật Excel văn phòng cơ bản

>> Hàm VLOOKUP

Các hàm nâng cao trong Excel là công cụ để người dùng có thể thực hiện các phép tính, xử lý dữ liệu trong các bảng. Mỗi hàm trong excel có nhiệm vụ và chức năng riêng giúp thực hiện các chức năng xử lý dữ liệu khác nhau. Taimienphi sẽ hướng dẫn các hàm nâng cao thường dùng trong excel như Hàm Vlookup, Hàm DCOUNTA, Hàm DGET, Hàm Choose… để có thể phục vụ tốt cho công việc của mình

Excel là chương trình bảng tính được cung cấp bởi Microsoft trong bộ phần mềm Office. Người dùng sẽ nhập dữ liệu lên Excel theo dạng bảng, sử dụng các hàm để tính toán cũng như xử lý dữ liệu. Mỗi hàm sẽ có những chức năng riêng, theo đó có những hàm nâng cao để xử lý những dữ liệu phức tạp hơn. Taimienphi sẽ giới thiệu với các bạn một số hàm nâng cao trong Excel cùng ví dụ cụ thể.

Hàm nâng cao trong Excel

Tự học excel cơ bản cấp tốc qua 15 chuyên đề FULL KHÔNG CHE (127 phút)

Câu hỏi thường gặp

9.1 Chứng chỉ tin học có giá trị trong bao lâu?

Chứng chỉ tin học có giá trị vĩnh viễn và không bị hết hạn.

9.2 Tôi có thể đăng ký thi Excel ở đâu?

Bạn có thể đăng ký thi Excel tại các trung tâm đào tạo hoặc các trường đại học, cao đẳng có chương trình đào tạo chứng chỉ này. Hoặc bạn có thể đăng ký tại trung tâm EDUSA để có được chứng chỉ với kết quả tốt nhất nhé.

9.3 Tôi có thể thi lại nếu không đạt được chứng chỉ tin học Excel?

Có, bạn có thể thi lại nếu không đạt được chứng chỉ tin học Excel. Khi bạn học tại EDUSA thi sẽ được cung cấp các buổi học và thi lại “miễn phí” nếu bạn thi không đạt.

10 Công thức Excel Nâng cao Bạn Phải Biết

Mỗi nhà phân tích tài chính dành nhiều thời gian trong Excel hơn là họ có thể quan tâm để thừa nhận. Dựa trên nhiều năm và nhiều năm kinh nghiệm, chúng tôi đã biên soạn các công thức Excel quan trọng và tiên tiến nhất mà mọi nhà phân tích tài chính đẳng cấp thế giới đều phải biết.

INDEX MATCH

Công thức: = INDEX (C3: E9, MATCH (B13, C3: C9,0), MATCH (B14, C3: E3,0))

Đây là một thay thế nâng cao cho các công thức VLOOKUP hoặc HLOOKUP (một số hạn chế và hạn chế). INDEX MATCH là một sự kết hợp mạnh mẽ của các công thức Excel sẽ đưa phân tích tài chính của bạn và mô hình tài chính lên cấp độ tiếp theo.

Hàm INDEX trả về giá trị của một ô trong bảng dựa trên số cột và hàng.

MATCH trả về vị trí của một ô trong một hàng hoặc cột.

Dưới đây là ví dụ về các công thức INDEX và MATCH được kết hợp với nhau. Trong ví dụ này, chúng tôi tra cứu và trả về chiều cao của một người dựa trên tên của họ. Vì tên và chiều cao là cả hai biến trong công thức, chúng ta có thể thay đổi cả hai biến này!

Để được giải thích từng bước hoặc cách sử dụng công thức này, vui lòng xem hướng dẫn miễn phí của chúng tôi về cách sử dụng MATCH MATCH MATCH trong Excel .