Xây dựng hàm làm việc với màu trong Excel

Làm việc với Excel thì chúng ta đã quá quen thuộc với các hàm tính toán, thống kê theo giá trị số, ký tự, ngày tháng, … như SUMIF, AVERAGEIF, COUTIF. Chúng ta cũng hay gặp trường hợp thực tế làm việc phải tính toán, thống kê theo giá trị màu chữ hay màu nền ô. Tuy nhiên Excel chưa có sẵn hàm để thực hiện công việc này.

1. Khởi động Microsoft Excel

2. Nhấn ALT + F11 để bắt đầu Visual Basic Editor

Trong menu Insert chọn Module

3. Nhập toàn bộ đoạn mã sau

‘Ham dem so luong theo mau nen chu
Function CountCellsByBackColor(rData As Range, cellRefColor As Range) As Long
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long
cntRes = 0
indRefColor = cellRefColor.CellS(1, 1).Interior.ColorIndex
For Each cellCurrent In rData
If indRefColor = cellCurrent.Interior.ColorIndex Then
cntRes = cntRes + 1
End If
Next cellCurrent
CountCellsByBackColor = cntRes
End Function’Ham tinh tong gia tri theo mau nen chu
Function SumCellsByBackColor(rData As Range, cellRefColor As Range)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes
sumRes = 0
indRefColor = cellRefColor.CellS(1, 1).Interior.ColorIndex
For Each cellCurrent In rData
If indRefColor = cellCurrent.Interior.ColorIndex Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
End If
Next cellCurrent
SumCellsByBackColor = sumRes
End Function

‘Ham tinh trung binh gia tri theo mau nen chu
Function AverageCellsByBackColor(rData As Range, cellRefColor As Range)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes
Dim i As Long
sumRes = 0
indRefColor = cellRefColor.CellS(1, 1).Interior.ColorIndex
For Each cellCurrent In rData
If indRefColor = cellCurrent.Interior.ColorIndex Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
i = i + 1
End If
Next cellCurrent
AverageCellsByBackColor = sumRes / i
End Function

‘Ham dem so luong theo mau  chu
Function CountCellsByFontColor(rData As Range, cellRefColor As Range) As Long
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long
cntRes = 0
indRefColor = cellRefColor.CellS(1, 1).Font.ColorIndex
For Each cellCurrent In rData
If indRefColor = cellCurrent.Font.ColorIndex Then
cntRes = cntRes + 1
End If
Next cellCurrent
CountCellsByFontColor = cntRes
End Function

‘Ham tinh tong gia tri theo mau chu
Function SumCellsByFontColor(rData As Range, cellRefColor As Range)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes
sumRes = 0
indRefColor = cellRefColor.CellS(1, 1).Font.ColorIndex
For Each cellCurrent In rData
If indRefColor = cellCurrent.Font.ColorIndex Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
End If
Next cellCurrent
SumCellsByFontColor = sumRes
End Function

‘Ham tinh trung binh gia tri theo mau chu
Function AverageCellsByFontColor(rData As Range, cellRefColor As Range)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes
Dim i As Long
sumRes = 0
indRefColor = cellRefColor.CellS(1, 1).Font.ColorIndex
For Each cellCurrent In rData
If indRefColor = cellCurrent.Font.ColorIndex Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
i = i + 1
End If
Next cellCurrent
AverageCellsByFontColor = sumRes / i
End Function

4. Quay lại màn hình Excel

a. Thao tác với màu chữ

Để đếm số lượng, bạn dùng hàm: = CountCellsByFontColor (Vùng cần đếm, Vùng điều kiện cần đếm).
Ví dụ: =CountCellsByFontColor($B$2:B$10,A15) với $B$2:B$10 là vùng dữ liệu cần đếm và A15 là vùng điều kiện màu chữ.

Để tính tổng, bạn dùng hàm: = SumCellsByFontColor(Vùng cần tính tổng, Vùng điều kiện cần tính tổng).

Để tính trung bình, bạn dùng hàm: = AverageCellsByFontColor (Vùng cần tính TB, Vùng điều kiện cần tính TB).

b. Thao tác với màu nền

Để đếm số lượng, bạn dùng hàm: = CountCellsByBackColor (Vùng cần đếm, Vùng điều kiện cần đếm).
Ví dụ: =CountCellsByBackColor($D$2:DC$10,C17) với $D$2:DC$10 là vùng dữ liệu cần đếm và C17 là vùng điều kiện màu chữ.

Để tính tổng, bạn dùng hàm: = SumCellsByBackColor(Vùng cần tính tổng, Vùng điều kiện cần tính tổng).

Để tính tổng, bạn dùng hàm: = AverageCellsByBackColor (Vùng cần tính TB, Vùng điều kiện cần tính TB).

Tạo hàm cắt họ, tên, họ đệm trong VBA

Khi bạn sử dụng Excel, thao tác với danh sách họ tên khách hàng, học sinh, học viên, … nhưng danh sách này lại bao gồm họ tên đầy đủ trong khi bạn lại muốn tách riêng cột tên khỏi cột họ tên này.

• Bước 1: Khởi động chương trình Microsoft Excel.
• Bước 2: Trong Menu Tool chọn Macro Visual Basic Editor. (Có thể sử dụng tổ hợp phím Alt + F11). Cửa sổ Microsoft Visual Basic xuất hiện.

• Bước 3: Trong Menu Insert chọnModule. Cửa sổ Code xuất hiện.

• Bước 4: Copy toàn bộ đoạn Code sau vào trong cửa sổ Code

Function CatTen(str As String) As String

    Dim mlen As Long

    Dim i As Long

    mlen = Len(str)

    For i = mlen To 1 Step -1

        If Mid(str, i, 1) = ” ” Then

            Exit For

        End If

    Next

    If i <> 0 Then

        CatTen = Trim(Mid(str, i + 1, mlen – i))

    Else

        CatTen = Trim(str)

    End If

End Function

Function CatHo(str As String) As String

    Dim mlen As Long

    Dim i As Long

    mlen = Len(str)

    For i = 1 To mlen

        If Mid(str, i, 1) = ” ” Then

            Exit For

        End If

    Next

    If i <> 0 Then

        CatHo = Trim(Mid(str, 1, i – 1))

    Else

        CatHo = Trim(str)

    End If

End Function

 Function CatHoDem(str As String) As String

    Dim mlen As Long

    Dim i, j, k As Long

    mlen = Len(str)

    k = 0

    For i = mlen To 1 Step -1

        If Mid(str, i, 1) = ” ” Then

            Exit For

        End If

        k = k + 1

    Next

    For j = 1 To mlen

        If Mid(str, j, 1) = ” ” Then

            Exit For

        End If

        k = k + 1

    Next

    If i <> 0 Then

        CatHoDem = Trim(Mid(str, j, mlen – k))

    Else

        CatHoDem = Trim(str)

    End If

End Function

• Bước 5: Để quay lại màn hìnhlàm việc chính của Excel, bạn vào Menu File chọn Close And Return to Microsoft Excel (Hoặc sử dụng tổ hợp phím Alt + Q).
• Bước 6: Sử dụng những hàm đã tạo ở trên

Tại ô B1 ta có Họ tên đầy đủ của 1 người (Hình 3). Để cắt tên của người đó, bạn cần thực hiện các bước sau:

•  Đưa vệt sáng đến ô B2, nhập vào hàm =Cathodem(B1) và nhấn Enter. Kết quả ô B2 có giá trị là Thị Ngọc.
• Tương tự, nếu bạn muốn cắt Họ thì dùng hàm =Catho(B1), cắt Tên thì dùng hàm Catten(B1)

Hàm DPRODUCT trong Excel

Nếu hàm PRODUCT() chỉ giúp các bạn nhân các giá trị thì hàm DPRODUCT() giúp các bạn nhân các giá trị theo điều kiện được chỉ định.

Bài viết dưới đây mô tả cú pháp và cách sử dụng hàm DPRODUCT() trong Excel.

Mô tả

Hàm DPRODUCT() trả về kết quả là giá trị của phép nhân các giá trị trong một cột của một danh sách hay một cơ sở dữ liệu theo một điều kiện được chỉ định.

Cú pháp

=DPRODUCT(database,field,criteria)

Trong đó:

- database: là một dãy các ô tạo nên danh sách hay cơ sở dữ liệu bao gồm các dữ liệu cần xử lý bằng hàm DPRODUCT() bao gồm các tên trường (Tiêu đề cột).

- field: trường (cột) dữ liệu được sử dụng trong hàm. Các bạn có thể viết ở dạng text bằng tên cột đặt trong dấu ngoặc kép (ví dụ "Tuổi", "Lương"), field cũng có thể là số đại diện cho vị trí của cột (như 1 cho cột thứ nhất, 2 cho cột thứ hai…) hoặc các bạn có thể nhập trực tiếp tên ô chứa tiêu đề cột (A1, A5…).

- criteria: là phạm vi các ô chứa điều kiện, các bạn có thể dùng phạm vi bất kỳ cho criteria nhưng nó phải chứa ít nhất một Tiêu đề cột và một ô bên dưới chứa điều kiện.

Ghi chú

- Các bạn nên đặt phạm vi điều kiện criteria trên trang tính để khi thêm dữ liệu thì phạm vi chứa điều kiện không thay đổi.

- Phạm vi điều kiện cần tách rời không chèn lên danh sách hay cơ sở dữ liệu cần xử lý.

- criteria bắt buộc phải chứa ít nhất Tiêu đề cột và một ô chứa điều kiện dưới tiêu đề cột.

Ví dụ

Tính tích các số trong cột Giá trị với STT <=4.

Tạo criteria:

Áp dụng hàm DPRODUCT(): =DPRODUCT(C8:D14,D8,C5:C6)

C8:D14 là bảng cơ sở dữ liệu chứa cả tiêu đề cột.

D8 là tên Tiêu đề cột cần tính.

C5:C6 là phạm vi điều kiện criteria.

Ta sẽ được kết quả như sau:

Trên đây bài viết đã giới thiệu cú pháp và ví dụ cụ thể về cách sử dụng hàm DPRODUCT() trong Excel. Tùy theo yêu cầu của từng bài toán mà các bạn áp dụng hàm sao cho phù hợp. Chúc các bạn thành công!

Hàm AVERAGEIF (trả về giá trị trung bình cộng theo điều kiện) trong Excel

Hàm AVERAGEIF() giúp các bạn tính giá trị trung bình cộng của các ô dữ liệu với điều điện cho trước. Nếu bạn nào chưa biết cách sử dụng hàm thì các bạn có thể theo dõi bài viết dưới đây.

Bài viết hướng dẫn cú pháp và cách sử dụng hàm AVERAGEIF() trong Excel.

Mô tả

Hàm AVERAGEIF() trả về giá trị trung bình cộng của tất cả các ô được chọn thỏa mãn điều kiện mà các bạn chỉ định.

Cú pháp

=AVERAGEIF(range,criteria,average_range)

Trong đó:

- range: là một hoặc nhiều ô cần tính giá trị trung bình, range có thể là các con số, các tên vùng, các mảng hay các tham chiếu đến có chứa số.

- criteria: là điều kiện dưới dạng con 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.

- average_range: tập hợp các ô thực sự cần tính giá trị trung bình, nếu average_range bỏ trống thì range sẽ được dùng để tính giá trị trung bình.

Ghi chú

- Những ô trống trong average_range được bỏ qua.

- Trong range chứa những giá trị logic (TRUE hoặc FALSE) cũng được bỏ qua.

- Nếu range rỗng hoặc chứa chuỗi thì hàm AVERAGEIF() sẽ báo lỗi

. - Trong criteria chứa ô trống (rỗng), thì hàm sẽ xem nó bằng 0.

- Nếu không có ô nào trong range thỏa mãn điều kiện criteria thì hàm AVERAGEIF() báo lỗi.

- Các bạn có thể sử dụng các ký tự đại diện như dấu hỏi chấm (?) thay cho một ký tự nào đó, dấu sao (*) thay cho một chuỗi nào đó trong criteria. Khi điều kiện trong criteria là dấu ? hay dấu * thì các bạn gõ thêm dấu ~ trước nó.

- Average_range và range không nhất thiết phải cùng kích thước với nhau, các ô thực sự được tính trung bình sẽ dùng ô trên cùng bên trái của average_range làm ô bắt đầu, và bao gồm thêm các ô tương ứng với kích thước của range. Để hiểu rõ hơn các bạn xem ví dụ dưới đây:

Ví dụ

Ví dụ 1:

Ví dụ 2:

Hi vọng với hai ví dụ trên các bạn sẽ hiểu rõ hơn về cách sử dụng hàm AVERAGEIF() trong Excel. Chúc các bạn thành công!

Hàm DATEDIF() (tính tổng số năm, tổng số tháng hay tổng số ngày từ hai khoảng thời gian cho trước) trong Excel.

Xử lý, tính toán dữ liệu với các dữ liệu là thời gian (kiểu date) rất khó khăn vì các bạn không thể sử dụng các phép toán bình thường được. Nhưng hàm DATEDIF() sẽ giúp các bạn thực hiện điều này, hàm sẽ giúp các bạn tính toán các dữ liệu có kiểu định dạng là thời gian trong Excel.

Bài viết mô tả cú pháp và cách sử dụng hàm DATEDIF() trong Excel.

Mô tả

Hàm DATEDIF() giúp các bạn tính tổng số năm, tổng số tháng hay tổng số ngày từ hai khoảng thời gian cho trước.

Các bạn có thể sử dụng hàm này từ Excel 2003 trở lên các phiên bản mới, hàm DATEDIF() không có trong danh sách các hàm của Excel nên khi nhập =DATEDIF thì hàm cũng không được hiển thị trong danh sách hàm của Excel.

Cú pháp

=DATEDIF(start_day,end_day,unit)

Trong đó:

- start_day: ngày bắt đầu cần tính toán.

- end_day: ngày kết thúc, end_day cần lớn hơn start_day.

- unit: loại kết quả trả về của việc tính toán, cần viết trong dấu ngoặc kép.

Dưới đây là danh sách các tùy chọn của unit.

y: số năm chênh lệch giữa start_day và end_day.

m: số tháng chênh lệch giữa start_day và end_day.

d: số ngày chênh lệch giữa start_day và end_day.

md: số ngày chênh lệch giữa start_day và end_day, không phụ thuộc vào số năm, số tháng.

ym: số tháng chênh lệch giữa start_day và end_day, không phụ thuộc vào số năm, số ngày.

yd: số ngày chênh lệch giữa start_day và end_day, không phụ thuộc vào số năm.

Ví dụ

Ví dụ 1: Sử dụng hàm DATEDIF để tính tuổi của các nhân viên.

Áp dụng hàm cho nhân viên cuối cùng:=DATEDIF(D11,NOW(),"y")

- D11: là ngày bắt đầu (năm sinh).

- NOW() là hàm để lấy ngày tháng năm hiện tại của máy tính.

- y để xác định giá trị trả về là khoảng chênh lệch giữa năm hiện tại và năm sinh.

Kết quả như sau:

Ví dụ 2: Cho start_day là 01/02/2013 và end_day là 28/12/2014, áp dụng tất cả các tùy chọn unit sẽ được các kết quả như sau:

Như vậy các bạn đã biết cách sử dụng hàm DATEDIF() trong Excel. Với sự hỗ trợ của hàm DATEDIF() thì việc xử lý tính toán dữ liệu ngày tháng năm sẽ dễ dàng hơn rất nhiều. Chúc các bạn thành công!

Hàm ISNA trong Excel

Sử dụng các tài liệu Excel được chia sẻ từ bạn bè hay trên internet, các bạn đôi khi bắt gặp hàm ISNA() được kết hợp với các hàm khác như VLOOKUP(), hàm IF(). Các bạn thắc mắc không hiểu lý do vì sao họ sử dụng hàm ISNA() như vậy?

Bài viết dưới đây sẽ hướng dẫn cú pháp và cách sử dụng hàm ISNA() trong Excel.

Mô tả

Hàm ISNA() giúp các bạn nhận biết giá trị đó có phải là #N/A (giá trị không sẵn sàng) hay không.

Hàm ISNA() trả về là TRUE nếu ô đó chứa giá trị #N/A. Trả về FALSE nếu ô đó không chứa giá trị #N/A.

Hàm này thường được sử dụng cùng với hàm VLOOKUP() và hàm IF().

Cú pháp

=ISNA(giá trị)

Với giá trị là giá trị của ô, biểu thức các bạn cần kiểm tra.

Ví dụ

Ví dụ đơn giản như sau: Các bạn có ô B6 có giá trị là 35, ô B7 có giá trị #N/A, vì vậy khi kiểm tra ô B6 và ô B7 bằng hàm ISNA sẽ có kết quả khác nhau:

Các bạn có thể sử dụng giá trị là một biểu thức, ví dụ biểu thức VLOOKUP như ví dụ dưới đây:

Giải thích cho ví dụ trên:

1. Hàm VLOOKUP() không tìm thấy giá trị của L11 trong bảng Mã Giá nên kết quả trả về là giá trị #N/A vì thế hàm ISNA() sẽ trả về giá trị TRUE.

2. Hàm VLOOKUP() tìm thấy giá trị của L13 trong bảng Mã Giá nên kết quả trả về sẽ là giá trị tương ứng với mã L13. Vì thế hàm ISNA() sẽ trả về giá trị FALSE vì giá trị của hàm đó khác giá trị #N/A.

Ngoài kết hợp với hàm VLOOUP các bạn có thể kết hợp với hàm IF để có kết quả hoàn chỉnh hơn.

Các hàm cơ bản trong Excel - Hàm tính toán thông dụng cần biết trong Excel

1. Hàm And

- Cấu trúc: And (logiccal1, logical 2,….)

+ Trong đó logical 1, logical 2 là các biểu thức điều kiện.

+ Có tối đa 256 biểu thức logical.

- Giá trị trả về của hàm And:

+ Trả về giá trị True: Nếu tất cả các điều kiện mang giá trị đúng.

+ Trả về giá trị False: Nếu tồn tại 1 điều kiện mang giá trị sai.

- Bảng giá trị:

And (Logical 1, logical 2, logical 3)
Logical 1	Logical 2	Logical 3
True	True	True
True	False	False
False	True	False
False	False	False

2. Hàm Or

- Cấu trúc: And (logical 1, logical 2, ….)

+ Trong đó logical 1, logical 2, … là các biểu thức điều kiện.

+ Có tối đa 256 giá trị logical

- Giá trị trả về của hàm Or:

+ Trả về giá trị True: nếu có ít nhất 1 điều kiện mang giá trị đúng.

+ Trả về giá trị False: Nếu tất cả các điều kiện đều sai.

Bảng giá trị của hàm Or:

Or (Logical 1, logical 2)
Logical 1	Logical 2	Logical 3
True	True	True
True	False	True
False	True	True
False	False	False

3. Hàm Not

- Cú pháp: Not (Logical)

+ Trong đó : Logical bắt buộc phải có

+ Hàm Not là hàm lấy giá trị nghịch đảo hay sử dụng trong các hàm lượng giác.

- Giá trị trả về của hàm Or:

+ Trả về giá trị True nếu Logical mang giá trị False.

+ Trả về giá trị False nếu Logical mang giá trị True.

Bảng giá trị của hàm Not:

Giá trị điều kiện	Kết quả phép Or (Điều kiện)
True	False
False	True

4. Hàm Sum

Là hàm giúp tính tổng các giá trị trong ô hay trong 1 danh sách trong excel. Là công cụ giúp tính toán nhanh chóng.

- Cú pháp: Sum (giá trị 1, giá trị 2, ….)

+ Trong đó: giá trị 1, giá trị 2 là các giá trị cần tính tổng.

+ Giá trị trả về: Là giá trị số.

- Ví dụ sử dụng hàm Sum.

Ta sử dụng hàm Sum để tính lương thực lĩnh cho nhân viên:

Kết quả:

5. Hàm Count

- Cú pháp: Count (value 1, value 2, …)

- Hàm Count sử dụng đếm số ô có thỏa mãn điều kiện nào đó.

- Ví dụ đếm số ô có giá trị số trong bảng sau:

- Sử dụng hàm Count: Cấu trúc Count (F9:J12).

Trên đây mình giới thiệu một số hàm cơ bản trong excel. Chúc các bạn thành công !