Hàm Excel Nâng Cao | Mẹo Và Thủ Thuật Nâng Cao – Hàm Index & Match

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

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

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

10 Công thức Excel Nâng cao Bạn Phải Biế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 .

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!

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á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é.

Mẹo và thủ thuật nâng cao – Data Analysis

Là công cụ chyên phân tích dữ liệu theo nhiều dạng một cách nhanh chóng và chính xác .

Ví dụ: Mình sẽ áp dụng vào để phân tích và thống kê dữ liệu sau:

Đầu tiên, vào thẻ Home => Analyze Data. Hiển thị ra các dạng phân tích theo dữ liệu

Tiếp theo, bạn sẽ chọn một dạng phù hợp theo yêu cầu và chọn Insert Chart ở dưới dạng phân tích đó.

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

Mẹo và thủ thuật nâng cao – Hàm DAX

Là hàm cho phép ta tạo các phép tính cực kỳ mạnh mẽ và linh hoạt trong bảng tổng hợp.

Ví dụ: Mình sử dụng bảng PivotTable được liên kết hai bảng trên.

Yêu cầu: Dùng tính năng DAX trong excel để đếm giá trị khác biệt và duy nhất trong excel.

Bạn làm như sau:

Bước 1: Chọn vào trường Table11 trong bảng PivotTable và nhấn chuột phải, chọn Add Measure.

Bước 2: Hiển thị ra hộp thoại Measure. Tại đây, bạn ghi tên measure trong ô Measure Name, xuống mục Formulas: Bạn sẽ thiết lập công thức như sau: =DISTINCTCOUNT(Table11[ID])

Bước 3: Measure mình thiết lập đã hiển thị trong Table11 ở bảng PivotTable. Tiếp theo bạn sẽ kéo Measure đó xuống dưới mục Values.

Vậy là mình đã hướng dẫn xong 30 mẹo và thủ thuật nâng cao hàng đầu trong Excel. Hi vọng bạn sẽ thực hiện thành công. Nếu thấy bài viết có ích, vui lòng đánh giá bài viết bên dưới! Trân trọng.

Có thể bạn quan tâm:

CHO ĐIỂM BÀI VIẾT NÀY

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.

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

Mẹo và thủ thuật nâng cao – Kết hợp từ nhiều file lại thành 1 file

Để gộp nhiều file lại với nhau, bạn cần có các bước sau:

Bước 1: Đưa tất cả các file lại vào cùng một thư mục như hình dưới:

Bước 2: Mở file Excel trống ra, tại đây bạn vào Data => Get Data => Chọn From File => From Folder

Bước 3: Truy cập vào thư mục chứa các file để gộp và nhấn Open để mở.

Bước 4: Tiếp theo, bạn chọn vào Combie and tranform data

Bước 5: Hiển thị hộp thoại Combie Files. Tại đây, bạn chọn vào Sheet1 là Sheet chứa dữ liệu gộp và nhấn OK

Bước 6: Hiển thị giao diện Power Query Editor

Bước 7: Tại đây, bạn cần thực hiện các thao tác sau:

– Để xóa cột bị thừa, bạn chọn vào cột đó, nhấn chuột phải và chọn Remove- Để xóa dòng bị thừa như Null: Bạn chọn vào cột bất kì chứa null => tích chọn vào bộ lọc bên cạnh cột => bỏ tích chọn Null và nhấn OK.

– Để đưa tiêu đề lên dòng đầu, ta vào thẻ Home => Use First Row as Headers- Để xóa tiêu đề bị thừa từ các file phía sau, bạn tích chọn bộ lọc bên cạnh 1 cột bất kì => tích bỏ tiêu đề => OK.

Bước 8: Sau khi hoàn thành xong, bạn vào thẻ Home => Close & Load để ra ngoài file Excel nhé!

Bước 9: Các file đã được gộp thành một file hoàn chỉnh.

Lưu ý: Để cập nhật file mới thêm vào thư mục, tại file đã gộp bạn chọn một ô bất kì, nhấn chuột phải => Refresh.

Mẹo và thủ thuật nâng cao – Mảng động

Cho phép chúng ta trả về nhiều giá trị từ một hàm duy nhất, vì vậy trước đây chúng ta chỉ trả về một kết quả từ một hàm và điều đó không còn đúng nữa nên có 6 hàm mới bộ lọc hiển thị một thứ tự sắp xếp và duy nhất : Filter, Randarray, Sequence, Sort, Sortby, Unique,…

• Filter: cho phép bạn lọc giá trị dựa theo điều kiện
• Sort: Sắp xếp các mảng theo thứ tự.
• Unique: nhận danh sách các giá trị duy nhất
• Sortby: sắp xếp theo chức năng ví dụ tăng cột này và giảm cột khác.
• Sequence: cho phép bạn tạo một danh sách các số liên tiếp trong một mảng, chẳng hạn như 1, 2, 3, 4
• Randarray: tạo một phạm vi giá trị ngẫu nhiên.
• Show Formulas: Hiển thị tất cả công thức và trang tính.

Ví dụ: Cho dữ liệu như sau:

• Sử dụng hàm Filter:

Yêu cầu: Lọc ra các sản phẩm 01 dựa vào dữ liệu trên:

Bạn gõ như sau: =FILTER($A$2:$D$20,$C$2:$C$20=”SP 01″,””)

Giải thích: $A$2:$D$20: Vùng dữ liệu$C$2:$C$20: Cột dữ liệu chứa điều kiện và điều kiện trong yêu cầu trên là SP 01.

• Sử dụng hàm Sort:

Yêu cầu: Hãy sắp xếp thứ tự tăng dần theo cột Sản phẩm:

Bạn gõ như sau: =SORT($A$3:$D$20,3,1)

Giải thích: $A$3:$D$20: Phạm vi vùng dữ liệu.3: Vị trí cột trong vùng dữ liệu muốn sắp xếp.1: Sắp xếp tăng dần.

• Sử dụng hàm Unique:

Yêu cầu: Hiển thị giá trị duy nhất trong cột Sản phẩm:

Bạn gõ như sau: =UNIQUE($C$3:$C$20,FALSE)

Giải thích: $C$3:$C$20: Phạm vi dữ liệu trong cột Sản phẩm.FALSE: Sắp xếp theo hàng.

• Sử dụng hàm Sortby:

Yêu cầu: Hiển thị dữ liệu được sắp xếp theo tăng giá trị trong cột sản phẩm và giảm giá trị trong cột Doanh thu.

Bạn gõ như sau: =SORTBY($A$3:$D$20,$C$3:$C$20,1,$D$3:$D$20,-1)

Giải thích: $A$3:$D$20: Phạm vi vùng dữ liệu.$C$3:$C$20: Phạm vi dữ liệu của cột Sản phẩm.1: Sắp xếp giá trị theo tăng dần.$D$3:$D$20: Phạm vi dữ liệu trong cột Doanh thu.-1: Sắp xếp giá trị theo giảm dần.

• Sử dụng hàm Sequence:

Yêu cầu: Tạo ra một mảng gồm 5 hàng và 4 cột

Bạn gõ công thức sau: =SEQUENCE(5,4)

• Sử dụng hàm Randarray:

Yêu cầu: Tạo ra mảng giá trị nguyên ngẫu nhiên gồm 5 hàng, 3 cột, bắt đầu từ giá trị 1, kết thúc là giá trị 100.

Bạn gõ công thức sau: =RANDARRAY(5,3,1,100,TRUE)

Mẹo và thủ thuật nâng cao – Tables

Tables là một đối tượng được đặt tên cho phép bạn quản lý nội dung bên trong một cách độc lập với phần còn lại của dữ liệu trang tính.

Ví dụ: Mình sẽ thực hiện tạo bảng trong vùng dữ liệu dưới đây:

Các bước thực hiện:

Bước 1: Chọn một ô bất kì trong vùng dữ liệu.

Bước 2: Vào thẻ Insert => Table. Hiển thị hộp thoại Create Table chứa phạm vi vùng dữ liệu.

Bước 3: Nhấn Ok để tạo bảng dữ liệu.

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.

Mẹo và thủ thuật nâng cao – Hàm Index & Match

Index là hàm trả về giá trị của một phần tử mảng hoặc một mảng.

Match là hàm tìm kiếm vị trí của một dữ liệu trong vùng dữ liệu.

Ví dụ: Mình sẽ sử dụng hai hàm trên vào ví dụ sau:

Yêu cầu: Tìm Số lượng dựa vào điều kiện sau:

Để tìm số lượng dựa vào điều kiện trên, bạn gõ công thức sau: =INDEX($E$4:$E$12,MATCH(1,(I6=B4:B12)*(I7=C4:C12)*(I8=D4:D12),0),1)

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.

Mẹo và thủ thuật nâng cao – Sử dụng Flash Fill

Flash Fill là công cụ giúp bạn thực hiện những thao tác lặp lại các thao tác mà bạn đã làm một cách nhanh chóng.

Ví dụ: Mình sẽ áp dụng Flash Fill cho bảng dữ liệu dưới đây:

Ở bảng dữ liệu thứ nhất, để tách họ tên ra 2 cột riêng biệt. Bạn làm như sau:

Tại Họ tên đầu tiên, bạn viết họ ra trong cột Họ. Để hiển thị các họ các ô sau, bạn nhấn tổ hợp phím CTRL + E

Tương tự, ở cột tên. Bạn cũng gõ tên người thứ nhất ra và nhấn CTRL + E để hiện thị các tên sau.

Tương tự bảng thứ hai bạn cũng làm tương tự vậy.

Lưu ý: Trường hợp ở cột tháng vì thời gian dòng đầu tiên ngày và tháng trùng nhau. Vì vậy, bạn sẽ viết tiếp tháng thứ 2 và nhấn CTRL + E.

Mẹo và thủ thuật nâng cao – Sử dụng Freeze Panes

Đây là công cụ giúp cố định cột dòng theo yêu cầu.

Ví dụ: Mình sẽ sử dụng công cụ này cho dữ liệu bên dưới:

Để cố định dòng tiêu đề, bạn làm như sau:

Đầu tiên, bạn chọn dòng dưới dòng tiêu đề, sau đó vào thẻ View => Freeze Panes

Để cố định cột đầu tiên, bạn làm như sau:

Đầu tiên, chọn cột bên phải cột đầu tiên, sau đó vào thẻ View => Freeze Panes

Để cố định cả dòng tiêu đề và cột đầu tiên, bạn chọn vào ô giao giữa dòng sau tiêu đề và cột bên phải cột đầu tiên. Trong ví dụ là ô B4, sau đó vào View, chọn Freeze Panes.

Mẹo và thủ thuật nâng cao – Hàm so sánh logic (Hàm IF)

Là hàm cho phép bạn thực hiện phép tính so sánh logic giữa một giá trị với giá trị bạn mong muốn. (Xem chi tiết về hàm)

Ví dụ: Mình có bảng dữ liệu bên dưới:

Yêu cầu: Tính đơn giá mặt hàng, biết rằng: Nếu mặt hàng thuộc nhóm X thì giá là 2000đ, còn lại là giá 2500.

Để xử lý dữ liệu, tại ô E4 mình gõ công thức sau: =IF(C4=”X”,2000,2500)

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

Mẹo và thủ thuật nâng cao – Sử dụng hàm Filter lọc dữ liệu.

Ví dụ: Cho vùng dữ liệu như sau:

Yêu cầu: Lọc ra điểm số phần Thực hành lớn hơn 7.

Để lọc ra điểm số theo như yêu cầu đề bài. Bạn thực hiện các bước sau:

Bước 1: Bạn bôi đen vùng dữ liệu muốn lọc. Tiếp theo vào thẻ Data => Filter.

Bước 2: Chọn vào bộ lọc bên cạnh ô Thực hành.

Bước 3: Chọn vào Number Filters => Greater Than Or Equal To…

Bước 4: Hiển thị ra hộp thoại Custom AutoFilter. Tại đây, bạn gõ điều kiện mà đề yêu cầu.

Bước 5: Nhấn Ok để hoàn thành.

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

30 Mẹo Và Thủ Thuật Excel Nâng Cao Hàng Đầu

Trong bài viết này, mình sẽ chia sẻ cho các bạn 30 mẹo và thủ thuật cực hay và rất quan trọng trong công việc. Hãy cùng mình tham khảo bên dưới nhé!

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

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á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

Mẹo và thủ thuật nâng cao – Sử dụng Custom Lists

Là công cụ để sắp xếp các dữ liệu trong bảng tính.

Ví dụ: Mình sẽ áp dùng công cụ này để hiển thị và sắp xếp dữ liệu bên dưới:

Để sử dụng công cụ này, bạn vào File => Options => Advances => Chọn vào mục General => click vào Edit Custom Lists…

Hiển thị hộp thoại Custom Lists, ở mục Custom lists bạn chọn NEW LIST, mục Import list from cells bạn bôi đen vùng dữ liệu

Tiếp theo, chọn Import và nhấn Ok để hoàn thành.

Sau khi đã cài đặt công cụ Custom Lists xong, bạn chọn một ô bất kì và gõ họ tên bất kì trong vùng dữ liệu.

Tiếp theo, di chuyển con chuột tới cuối ô mới gõ họ tên bất kì, hiển thị dấu cộng và bạn kéo xuống để hiển thị các tên khác.

Mẹo và thủ thuật nâng cao – Dữ liệu quan hệ (Relationships)

Đây là cách giúp bạn nhóm nhiều dữ liệu có mối quan hệ với nhau thành một bảng. Từ đó, sẽ phân tích và tóm tắt các giá trị trong bảng đó.

Ví dụ: Mình có 2 bảng dữ liệu như dưới.

Để nhóm 2 bảng dữ liệu trên, bạn thực hiện các bước như sau:

Bước 1: Bạn chọn 1 ô bất kì ở bảng dữ liệu thứ nhất.

Bước 2: Vào thẻ Insert => PivotTable. Hộp thoại Create PivotTable hiện ra, bạn sẽ thiết lập vùng dữ liệu (giống hướng dẫn trên) và tích chọn Add this data to the Data Model.

Bước 3: Vào thẻ PivotTable Analyze, chọn vào Relationships và chọn New

Bước 4: Tại bảng Create Relationships. Ở mục Table, bạn chọn tên bảng dữ liệu thứ nhất và chọn tên bảng dữ liệu thứ hai trong mục Relates Table. Hai bảng đều có chung quan hệ là cột ID.

Bước 4: Nhấn Ok. Nếu liên kết được thì sẽ hiện bảng sau:

Bước 5: Nhấn Close để hoàn thành.

Bước 6: Trong bảng PivotTable, chọn mục All. Kéo trường SL bán ở Table 11 xuống ô Values và kéo trường ID ở Table 13 xuống ô Rows.

Mẹo và thủ thuật nâng cao – Tạo Drop list (danh sách thả xuống)

Để tạo danh sách dữ liệu thả xuống trong Excel, mình sẽ sử dụng công cụ Data Validation.

Ví dụ: Mình muốn tạo drop list dựa vào dữ liệu sau:

Các bước thực hiện:

Bước 1: Chọn một ô trống bất kì để chứa danh sách dữ liệu thả xuống.

Bước 2: Vào thẻ Data => Data Validation. Hộp thoại Data Validation hiện ra

Bước 3: Tại mục Allow, chọn vào List. Mục Source, bôi đen vùng dữ liệu muốn hiển thị list danh sách. Và nhấn OK.

Bước 4: Danh sách dữ liệu Họ và Tên sau khi đã tạo

Mẹo và thủ thuật nâng cao – Tạo thông báo ô xác thực dữ liệu

Ví dụ: Tạo thông báo khối lượng nhập vào phải lớn hơn 0 trong dữ liệu sau:

Các bước thực hiện:

Bước 1: Bôi đen các dòng trong cột khối lượng.

Bước 2: Vào thẻ Data => Data Validation. Hiển thị hộp thoại Validation, bạn vào mục Input Message

Bước 3: Tích chọn vào ô Show input message when cell is selected. Mục Tilte, bạn ghi tiêu đề thông báo. Mục Input message, bạn ghi nội dung thông báo

Bước 4: Nhấn Ok để hoàn thành hiển thị thông báo.

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

Mẹo và thủ thuật nâng cao – Lọc dữ liệu theo nhiều điều kiện (Hàm Advanced Filter).

Ví dụ: Mình có bảng dữ liệu như bên dưới:

Yêu cầu: Dựa vào điều kiện dưới đây để lọc ra dữ liệu theo yêu cầu.

Để lọc dữ liệu dựa theo nhiều điều kiện đề cho như trên bạn làm như sau:

Đầu tiên, vào thẻ Data => Advanced. Hiển thị hộp thoại Advanced Filter

Tiếp theo, Hộp thoại Advanced Filter hiện ra. Tại đây, bạn thực hiện như sau:

Tích chọn vào mục Copy to another location, mục List range: Bạn bôi đen vùng dữ liệu, mục Criteria range: Bạn bôi đen vùng chứa điều kiện dữ liệu, mục Copy to: Bạn chọn ô hiển thị kết quả.

Nhấn Ok để hoàn thành.

Mẹo và thủ thuật nâng cao – Hàm đếm dữ liệu có điều kiện (Hàm Countifs)

Ví dụ: Đếm các điều kiện được yêu cầu từ dữ liệu sau:

Đếm các sản phẩm có số tiền trên 300: =COUNTIFS($D$3:$D$13,”>300″)

Đếm các sản phẩm sữa có số tiền trên 300: =COUNTIFS($C$3:$C$13,”Milk”,$D$3:$D$13,”>300″)

Đếm các sản phẩm sữa của cửa hàng MiniMart có số tiền trên 300: =COUNTIFS($C$3:$C$13,”Milk”,$B$3:$B$13,”MiniMart”,$D$3:$D$13,”>300″)

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

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 .