(Xóa dòng trống trong Excel)

- Khi bạn phải xử lý dữ liệu được Copy từ một nguồn khác như: Internet hoặc từ phần mềm trong Excel, thông thường dữ liệu này thường không theo ý muốn. Và thường xảy ra vấn đề các dòng dữ liệu không liền nhau và bị các dòng trống xen kẽ.

Với trường hợp này nếu dữ liệu ít thì có thể thực hiện bằng phương pháp thủ công để xóa, nhưng đối với dữ liệu lớn thì cách làm này sẽ không khả thì vì mất quá nhiều thời gian. Vậy có cách nào để loại bỏ nhanh các dòng trống ? Hôm nay Kênh phần mềm việt sẽ giới thiệu với mọi người 2 phương pháp đơn giản để có thể xóa nhanh các dòng dữ liệu trắng và dưới đây là các cách thực hiện.

1. Sử dụng các chức năng có sẵn

a. Cách 1: 

- B1: Chọn toàn bộ dữ liệu cần xóa (Trong trường hợp dữ liệu nhiều Excel sẽ không cho phép chọn toàn bộ, khi đó bắt buộc phải chọn từng phần)

- B2: Ấn tổ hợp phím Ctrl + G, hộp thoại xuất hiện

- B3: Kích nút Special

- B4: Kích chọn Blanks

- B5: Chọn Tab Home, kích biểu tượng Delete trên thanh Toolbar

- B6: Kích chọn Delete Sheet Rows

Sau khi thực hiện xong các bước trên, toàn bộ các dòng trống đã được chọn sẽ được xóa bỏ.

b. Cách 2:

- B1: Chọn toàn bộ dữ liệu

- B2: Vào Tab Data\Filter

- B3: Bỏ chọn Select All

- B4: Kéo thanh trượt xuống phía dưới cùng

- B5: Tích chọn (Blanks) và kích OK, toàn bộ các dòng trống sẽ được lọc

- B6:  Chọn Tab Home, kích biểu tượng Delete trên thanh Toolbar

2. Sử dụng VBA

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

- B2: Vào menu Inser\Module

- B3: Nhập toàn bộ đoạn mã sau vào màn hình soạn thảo Code

Sub DeleteBlankRows()

    Dim i As Long

    If WorksheetFunction.CountA(Selection) = 0 Then

        MsgBox "Chua co vung du lieu nao duoc chon", vbInformation, "kenhphanmemviet.blogspot.com"

        Exit Sub

    End If

    With Application

        .Calculation = xlCalculationManual

        .ScreenUpdating = False

        For i = Selection.Rows.Count To 1 Step -1

            If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then

                Selection.Rows(i).EntireRow.Delete

            End If

        Next i

        .Calculation = xlCalculationAutomatic

        .ScreenUpdating = True

    End With

End Sub

- B4: Quay lại màn hình làm việc Excel với tổ hợp Alt + Q

- B5: Chọn toàn bộ dữ liệu cần xóa bỏ dòng trống

- B6: Chọn Tab View\Macros\View Macros

- B7: Lựa chọn Macro DeleteBlankRows và kích nút Run

Sau khi thực hiện xong Macro này, toàn bộ các dòng trống trong vùng dữ liệu được chọn sẽ tự động được xóa bỏ.

Trên đây là các cách để có thể xóa các dòng dữ liệu trống, tùy vào thói quen của từng người mà lựa chọn cách thực hiện cho phù hợp.

Tin học văn phòng

Read more…

(Thủ thuật VBA trong Excel)

- Đối với những người làm công việc tính toán,  thống kê thì việc phải thường xuyên làm việc với những con số trùng nhau trong 1 vùng, 1 sheet là chuyện thường xuyên gặp phải. Để tiện cho việc theo dõi, người sử dụng thường đánh dấu các giá trị trùng nhau này theo các riêng của mình. Nhưng cách thông thường mọi người thường sử dụng đó là sử dụng Conditional Formatting để tìm và định dạng các giá trị trùng nhau, nhưng với cách làm này người sử dụng chỉ có thể sử dụng được 1 màu duy nhất để phân biệt các giá trị trùng nhau. Với việc sử dụng Conditional Formatting, người sử dụng vẫn khó theo dõi trong 1 vùng có nhiều giá trị giống nhau do chỉ có 1 màu để phân biệt. Vậy có cách nào để có thể thể hiện mỗi giá trị trùng nhau là một màu riêng biệt không?

Hôm nay Kênh phần mềm việt sẽ giới thiệu với mọi người sử dụng VBA để đánh dấu các giá trị trùng nhau bằng những màu khác nhau.

- B1: Khởi động Microsoft Excel.
- B2: Nhấn ALT + F11 để bắt đầu Visual Basic Editor.
- B3: Vào Menu Inser\Module

- B4: Nhập toàn bộ đoạn mã sau vào

Sub Highlight_Duplicate()
    Dim ws As Worksheet
    Dim cell As Range
    Dim myrng As Range
    Dim clr As Long
    Dim lastcell As Range
    Dim i As Long
    Dim lastrow As Long

    Set ws = ThisWorkbook.ActiveSheet

    'Vung can danh dau gia tri trung nhau
    Set myrng = ws.Range("C4:F" & Range("C" & ws.Rows.Count).End(xlUp).Row)

    With myrng
        Set lastcell = .Cells(.Cells.Count)
    End With

    myrng.Interior.ColorIndex = xlNone
    clr = 3

    For Each cell In myrng
        'Kiem tra so gia tri trung nhau trong vung, neu co hai gia tri trung nhau tro len thi thuc hien
        If Application.WorksheetFunction.CountIf(myrng, cell) > 1 Then
            'Neu la o dau tien cua cac gia tri trung nhau trong vung
            If myrng.Find(what:=cell, lookat:=xlWhole, MatchCase:=False, after:=lastcell).Address = cell.Address Then
                'Thiet lap mau
                cell.Interior.ColorIndex = clr
                clr = clr + 1
                i = i + 1
            Else
                'Thiet lap mau tu o thu 2 voi cac gia tri trung nhau
                cell.Interior.ColorIndex = myrng.Find(what:=cell, lookat:=xlWhole, MatchCase:=False, after:=lastcell).Interior.ColorIndex
            End If
        End If
    Next

     'Lay dong cuoi cung cua vung du lieu
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    Range("A" & lastrow + 2).Value = "Tong so co " & i & " gia tri trung nhau"
End Sub

Trong ví dụ này, Kênh phần mềm đang để vùng cần đánh dấu từ cột C: F, các bạn có thể thay đổi địa chỉ của vùng này tùy vào dữ liệu thực tế.

- B5: Chạy Macro có tên Highlight_Duplicate

Tin học văn phòng

Read more…

(Thủ thuật VBA trong Excel)

-Trong Excel khi nhập những giá trị số và ấn Enter, Excel sẽ tự động căn chỉnh các giá trị này sang bên phải của ô. Đối với những giá trị số khi nhập xong, Excel sẽ tự động loại bỏ số 0 đằng trước các dãy số, ví dụ như số điện thoại chẳng hạn. Để Excel không loại bỏ số 0 đằng trước, người sử dụng thường thêm dấu ‘ trước khi đánh dãy số. Đối với những dãy số như vậy người ta thường gọi là ký tự số. Hoặc khi ta nhập các số xen lẫn giữa các ký tự thì đây cũng được coi là các ký tự số, hoặc các số được ngăn cách bởi các ký tự đặc biệt.

Trong công việc thường ngày ta sẽ phải gặp một số tình huống phải thao tác hoặc xử lý với các ký tự số, nếu như sử dụng các hàm có sẵn trong Excel có thể sẽ không thể giải quyết triệt để được mong muốn của chúng ta. Hôm nay Kênh phần mềm việt xin giới thiệu với mọi người một vài trường hợp sử dụng VBA để thao tác với ký tự số trong Excel.

1. Đếm số chữ số trong chuỗi ký tự số

Giả sử bạn có 1 chuỗi các số như sau: 17ABC6D8H432

Bây giờ bạn muốn đếm xem trong chuỗi  này có bao nhiêu số, để làm được việc này bạn sử dụng hàm VBA dưới đây.

Function CountNumber(str As String)
    Dim mlen As Long
    Dim i As Long
    Dim iCount As Long
    'Neu chuoi =0 thi khong xu ly
    If Len(str) = 0 Then Exit Function
    'Xoa bo cac ky tu trang o dau va cuoi
    str = Trim(str)
    'Dem so ky tu chuoi
    mlen = Len(str)
    iCount = 0
    For i = 1 To mlen
        If IsNumeric(Mid(str, i, 1)) Then
            iCount = iCount + 1
        End If
    Next
    CountNumber = iCount
End Function

2. Tìm giá trị lớn nhất trong chuỗi ký tự số

Giả sử bạn có 1 chuỗi các số như sau: 12,45,66,7888,34,56,39,17,98,25

Bây giờ bạn muốn tìm số lớn nhất trong chuỗi các số này, để làm được việc này bạn sử dụng hàm VBA dưới đây.

Function HighestNumber(R As Range)
    Dim x As Variant, M As Double, i As Long, ct As Long
    Set R = R.CellS(1, 1)
    x = Split(R.Value, ",")
    For i = LBound(x) To UBound(x)
        If IsNumeric(x(i)) Then
            ct = ct + 1
            If x(i) > M Then M = x(i)
        End If
    Next i
    If ct = 0 Then
        HighestNumber = CVErr(xlErrNA)
    Else
        HighestNumber = M
    End If
End Function

Nếu các ký tự số trong chuỗi được ngăn cách bởi dấu cách 12 45 66 7888 34 56 39 7 98 25
Thì đoạn Code  x = Split(R.Value, ",") được thay thế bằng  x = Split(R.Value, " ")

Tin học văn phòng

Read more…

(Thủ thuật VBA trong Excel)

- Đối với những ai đã làm việc trên Excel chắc không lạ gì thanh Status, nhờ có thanh Status ta có thể biết được trạng thái của một số phím trên bàn phím như: CapsLock, ScrollLock, NumLock. Và đặc biệt nhờ thanh công cụ này ta có thể biết trạng thái của ô đang làm việc. Nếu dòng trạng thái xuất hiện dòng chữ "Ready" tức là ta có thể làm mọi thao tác bình thường, nếu xuất hiện dòng chữ "Enter" nghĩa là chỉ khi ta ấn phím Enter  ta mới có thể làm được các thao tác khác, nếu xuất hiện dòng chữ "Edit" tức là đang ở trạng thái sửa dữ liệu.

Nhưng đó là những thông tin mà Microsoft Excel đã mặc định, vậy tại sao chúng ta không thử tìm cách thay đổi những thông tin này theo cách của mình nhỉ? Ví dụ như thêm thông tin ngày giờ hiện tại chẳng hạn? Do nhiều người khi làm việc thường không biết hôm  nay là thứ mấy, ngày bao nhiêu? Và để biết được các thông tin này, người sử dụng thường xem tại thanh Taskbar ở góc dưới bên phải màn hình. Vậy tại sao chúng ta không thêm ngay thông tin này ngay tại Workbook đang làm việc mà không cần phải mất thêm thao tác nào nhỉ.

Hôm nay Kênh phần mềm việt sẽ giới thiệu với các bạn một thủ thuật để hiển thị ngày và giờ hiện tại ở trên thanh công cụ Status của Excel  bằng cách viết Macro.

1. Khởi động Microsoft Excel.
2. Nhấn ALT + F11 để bắt đầu Visual Basic Editor.
Vào Menu Inser\Module

3. Nhập toàn bộ đoạn mã  sau vào màn hình soạn thảo Code bên phải

Dim OK As Boolean
Sub Auto_Open()
    OK = True
    Update
End Sub

Sub Update()
Dim StatBarMsgString As String
StatBarMsgString = "Ngay gio hien tai: "
If OK Then
        Application.StatusBar = StatBarMsgString & Format(Now, "dd.mm.yyyy  hh:mm:ss")
        Application.OnTime Now + TimeValue("00:00:01"), "Update", , True
    Else
        Application.StatusBar = False
    End If
End Sub

Sau khi nhập xong đoạn Code trên, bạn ghi, đóng và mở lại Workbook vừa tạo. Bây giờ bạn quan sát ở thanh Status, bạn sẽ thấy xuất hiện dòng thông tin về Ngày và giờ hiện tại và bạn sẽ luôn luôn biết được chính xác của ngày hiện tại ngay tại cửa sổ làm việc của Excel.

Tin học văn phòng

Read more…

(Thủ thuật VBA trong Excel)

- Trong bài viết “Hàm chuẩn hóa chuỗi trong Excel”, Kênh phần mềm việt đã giới thiệu với mọi người cách viết hàm để chuẩn hóa chuỗi trong Excel. Hàm này sẽ rất có ích đối với người dùng khi Copy dữ liệu từ một nguồn khác và dữ liệu này chưa được chuẩn hóa theo mong muốn của người sử dụng. Đó là trường hợp Copy dữ liệu từ một nguồn khác, còn việc tự động chuẩn hóa ký tự nhập vào mỗi khi ấn Enter hoặc chuyển ô?. Việc tự động chuẩn hóa này sẽ giúp người sử dụng  giúp ngắn được thời gian nhập liệu khi phải làm việc với các danh sách học sinh, học viên, nhân sự, khách hàng….  khi không còn phải quan tâm đến việc đánh ký tự chữ hoa ở đầu mỗi từ hoặc bị thừa các khoảng trắng.

Hôm nay Kênh phần mềm việt sẽ giới thiệu với các bạn cách sử dụng hàm chuẩn hóa chuỗi để tự động hóa trong quá trình nhập liệu. Hàm này sẽ thực hiện các công việc: xóa bỏ các ký tự trống ở đầu và cuối, Xóa bỏ từ 2 ký tự trống liên tiếp ở giữa các từ, Viết hoa chữ cái đầu tiên tại mỗi từ mỗi khi người sử dụng ấn Enter hoặc chuyển ô. Nếu muốn kiểm soát việc tự động này theo những vùng hoặc những cột mong muốn, bạn hoàn toàn có thể thực hiện được.

1. Khởi động Microsoft Excel.
2. Nhấn ALT + F11 để bắt đầu Visual Basic Editor.
Vào Menu Inser\Module

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

Function Chuanhoachuoi(str As String) As String
    Dim sChuoi As String
    Dim mlen As Long
    Dim i As Long
    'Neu chuoi =0 thi khong xu ly
    If Len(str) = 0 Then Exit Function
    'Xoa bo cac ky tu trang o dau va cuoi
    str = Trim(str)
    'Dem so ky tu chuoi
    mlen = Len(str)

    'Loai bo hai ki tu trong lien tiep
    For i = 1 To mlen
        If Mid(str, i, 1) = " " And Mid(str, i + 1, 1) = " " Then
            str = Replace(str, "  ", " ")
            i = i - 1
        End If
    Next

     For i = 1 To mlen
        ' Chuyen cac ky tu dau tien mot tu sang chu hoa
        If Mid(str, i, 1) = " " Then
            sChuoi = sChuoi & " " & UCase(Mid(str, i + 1, 1))
            i = i + 1
        Else
            'Chuyen chu cai dau tien cua cau sang chu hoa
            If i = 1 Then
                sChuoi = UCase(Mid(str, 1, 1))
            Else
                sChuoi = sChuoi & LCase(Mid(str, i, 1))
            End If
        End If
     Next
    Chuanhoachuoi = sChuoi
End Function

Kích nút Save để lưu lại, bạn có thể xem thêm bài viết cách lưu file Excel khi chứa Macro hoặc hàm.

4. Kích đúp vào tên Sheet mà bạn muốn thực hiện công việc tự động chuẩn hóa

- Tại mục (General) chọn WorkSheet
- Tại mục (Declarations) chọn Change

Nhập đoạn Code phía dưới vào màn hình soạn thảo

Private Sub Worksheet_Change(ByVal Target As Range)
     Dim str As String
     If Not (Intersect(Target, Range("$F:$F")) Is Nothing) Then
str = Chuanhoachuoi(Target.Value)
Target = str
     End If
End Sub

Trong đó:
- Range("$F:$F"): Chỉ cho phép tự động chuẩn hóa tại cột F
- Range("$F:$G"): Cho phép tự động chuẩn hóa từ cột F đến cột G
- Range("$F:$F,$I:$J"): Cho phép tự động chuẩn hóa cột F và cột J
- Range("E8:E9,E11:E14"): Cho phép tự động chuẩn hóa từ ô E8 đến E9 và từ ô E11 đến ô E14

Bây giờ mỗi khi người sử dụng nhập các chuỗi ký tự ở những vùng cho phép tại Sheet vừa nhập đoạn Code trên, Excel sẽ tự động chuẩn hóa chuỗi ký tự đã nhập vào.

Tin học văn phòng

Read more…

(Thủ thuật VBA Excel) - Trong Excel khi làm việc với một vài Sheet thì việc di chuyển khá dễ dàng, người sử dụng không phải mất quá nhiều thời gian để lựa chọn Sheet cần thao tác. Tuy nhiên khi Workbook có nhiều Sheet thì việc chọn Sheet để làm việc thì quả thực rất khó và mất thời gian. Có 1 cách để di chuyển qua lại giữa các Sheet bằng sử dụng phím tắt đó là: Ctrl-Page Up: Tiến lên 1 sheet (Sheet 1 sang Sheet 2), Ctrl-Page Down: Lùi về 1 sheet (Sheet 3 về Sheet 2). 

Hoặc cũng có nhiều người sử dụng 1 Sheet chính, trong Sheet này sẽ tạo danh mục các Sheet dạng mục lục và liên kết đến đây khi kích vào. Với cách làm này người sử dụng có thể dễ dàng trong việc làm việc và di chuyển giữa các Sheet và quay về Sheet chính.  Hiện mọi người vẫn sử dụng phương pháp thủ công để làm công việc này, hôm nay Kênh phần mềm việt sẽ giới thiệu với mọi người 1 phương pháp để làm nhanh công việc này bằng cách sử dụng VBA.

Hàm này sẽ có chức năng như sau:
- Kiểm tra xem đã có Sheet mục lục chưa, nếu chưa có thì sẽ tạo mới và chèn vào vị trí đầu tiên
- Thiết lập độ rộng cột
- Định dạng các vùng dữ liệu
- Đánh số thứ tự và chèn tên Sheet vào các ô
- Chèn liên kết đến các Sheet tương ứng với từng Sheet
- Chèn một liên kết để quay về Sheet Mucluc

Dưới đây là các bước để tạo Macro này.
1. Khởi động Microsoft Excel.
2. Nhấn ALT + F11 để bắt đầu Visual Basic Editor.
Vào Menu Inser\Module

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

Private Sub CreateTableOfContents()
    Dim wsSheet As Worksheet
    Dim ws As Worksheet
    Dim Counter As Long

    On Error Resume Next
    Set wsSheet = Sheets("Mucluc")
    'Kiem tra su ton tai cua Sheet
    On Error GoTo 0
    If wsSheet Is Nothing Then
        'Neu chua co thi them vao vi tri dau tien cua Workbook
        Set wsSheet = ActiveWorkbook.Sheets.Add(Before:=Worksheets(1))
        wsSheet.Name = "Mucluc"
    End If

    With wsSheet
        .Cells(2, 1) = "DANH SACH CAC SHEET"
        .Cells(2, 1).Name = "Index"
        .Cells(4, 1).Value = "STT"
        .Cells(4, 2).Value = "Ten Sheet"
    End With

    'Merge Cell
    With Range("A2:B2")
        .Merge
        .HorizontalAlignment = xlCenter
        .Font.Bold = True
    End With

    'Set ColumnWidth
    With Columns("A:A")
        .ColumnWidth = 8
        .HorizontalAlignment = xlCenter
    End With

     With Range("A4")
        .HorizontalAlignment = xlCenter
        .Font.Bold = True
    End With

    Columns("B:B").ColumnWidth = 30
    With Range("B4")
        .HorizontalAlignment = xlCenter
        .Font.Bold = True
    End With

    Counter = 1
    For Each ws In Worksheets
        If ws.Name <> wsSheet.Name Then
            'Gan gia tri cot thu tu
            wsSheet.Cells(Counter + 4, 1).Value = Counter
            'Tao lien ket
            wsSheet.Hyperlinks.Add Anchor:=wsSheet.Cells(Counter + 4, 2), _
                                            Address:="", _
                                            SubAddress:=ws.Name & "!A1", _
                                            ScreenTip:=ws.Name, _
                                            TextToDisplay:=ws.Name
            'Them nut Quay ve Sheet Muc luc tai moi Sheet
            With ws
                .Hyperlinks.Add Anchor:=.Range("H1"), Address:="", SubAddress:="Index", TextToDisplay:="Quay ve"
            End With
            Counter = Counter + 1
        End If
    Next ws
   Set xlSheet = Nothing
End Sub

4. Chạy Marco vừa tạo, bạn sẽ có 1 danh sách toàn bộ các Sheet có trên Workbook hiện tại được đưa vào trong Sheet Mucluc, và từ đây bạn dễ dàng đến bất kỳ một Sheet nào khi cần.

Tin học văn phòng

Read more…

(Thủ thuật VBA Excel) - Việc hỗ trợ sắp xếp trong Excel rất mạnh, ngoài việc cung cấp chức năng sắp xếp thứ tự theo giá trị, người sử dụng còn có thể chọn cho mình những lựa chọn khác như: màu chữ, màu nền. Với công cụ sắp xếp này người sử dụng có thể dễ dàng sắp xếp thứ tự bảng, danh sách theo mong muốn của mình.

Vậy tôi có 1 Workbook có chứa nhiều Sheet, bây giờ tôi muốn sắp xếp thứ tự các Sheet này theo thứ tự ABC để tiện cho việc truy cập và làm việc. Hiện trong Excel chưa có tính năng có sẵn để hỗ trợ thực hiện công việc này! Tuy nhiên ta vẫn có thể sắp xếp các Sheet theo mong muốn bằng cách sử dụng VBA.

1. Khởi động Microsoft Excel.

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

Vào Menu Inser\Module

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

Sub SortSheetOnWorkbook()
Dim i As Integer
Dim j As Integer
Dim iMsg As VbMsgBoxResult
   iMsg = MsgBox("Ban co muon sap xep thu tu cac Sheet khong?" & Chr(10) & "Chon Yes de sap xep tang dan" & Chr(10) & "Kich No de sap xep giam dan" & Chr(10) & "Kich Cancel de huy", vbYesNoCancel + vbInformation + vbDefaultButton2, "Sap xep Sheet")
   For i = 1 To Sheets.Count
      For j = 1 To Sheets.Count - 1
        Select Case iMsg
            Case vbYes
                If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
                   Sheets(j).Move After:=Sheets(j + 1)
                End If
            Case vbNo
                If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then
                   Sheets(j).Move After:=Sheets(j + 1)
                End If
        End Select
      Next j
   Next i
End Sub

4. Quay trở lại màn hình Excel và sử dụng VBA vừa tạo
- Vào Menu View\Macros\View Macros

- Chọn Macro SortSheetOnWorkbook
- Kích nút Run

Khi chạy Macro, nếu chọn Yes thì sẽ sắp xếp theo thứ tự tăng dần, nếu No là theo thứ tự giảm dần.
Sau khi chạy xong, Macro sẽ tự động sắp xếp các Sheet theo thứ tự tăng hoặc giảm dần do người dùng lựa chọn. Dưới đây là kết quả sau khi thực hiện Macro.

Tin học văn phòng

Read more…

(VBA Excel) - Phần mềm Office đã trở nên quá quen thuộc với người dùng, với nhiều lĩnh vực từ: giáo dục, tài chính, ngân hàng, CNTT, xây dựng, kinh doanh… Mặc dù mỗi lĩnh vực có những đặc thù khác nhau nhưng Office vẫn có thể đáp ứng tất cả các yêu cầu của người dùng. Đặc biệt đối với Excel, nó được sử dụng vào những công việc từ đơn giản đến phức tạp  như: tính toán lương, bảng giá, kế toán, dự toán xây dựng. Với Excel người sử dụng không những có thể sử dụng những hàm có sẵn do Microsoft viết mà tự mình viết ra các hàm để xử lý, giải quyết những công việc, bài toán thực tế.

Bài viết này sẽ giới thiệu với mọi người cách tận dụng sức mạnh của VBA khi làm việc với các siêu liên kết (Hyperlink).

1. Xóa toàn bộ các siêu liên kết các ô được chọn

Sao chép đoạn mã  phía dưới vào Workbook, sau đó chọn các ô cần xóa liên kết và chạy Macro này.

Sub RemoveHyperlinks()
     Selection.Hyperlinks.Delete
End Sub

2. Xóa toàn bộ các siêu liên kết trong Sheet hiện tại

Nếu bạn muốn loại bỏ tất cả các siêu liên kết trên Sheet hiện tại , sử dụng các Macro sau đây

Sub RemoveHyperlinksOnActiveSheet()
Cells.Hyperlinks.Delete
End Sub

3. Lấy ra địa chỉ của siêu liên kết điền vào ô liền kề bên phải

Bạn có thể sử dụng một Macro để trích xuất địa chỉ từ mỗi siêu liên kết trên một bảng tính, và lưu  nó vào ô liền kề bên phải.

Sub ExtractHyperlink()
    Dim HL As Hyperlink
    For Each HL In ActiveSheet.Hyperlinks
        HL.Range.Offset(0, 1).Value = HL.Address
    Next
End Sub

4. Trích xuất các URL hoặc địa chỉ email từ một siêu liên kết
Nếu bạn có một siêu liên kết, để biết được chính xác URL đó là gì ta phải để chuột vào siêu liên kết đó hoặc kích chuột phải chọn Edit Hyperlink. Nhưng nếu bạn có một danh sách các siêu liên kết thì ta không thể thực hiện thủ công được vì sẽ mất thời gian, vậy có hàm nào trong Excel để làm được việc này không?
Hiện trong Excel không có hàm để trích xuất các URL hoặc địa chỉ email từ một siêu liên kết. Bạn có thể tạo ra hàm riêng để trích xuất địa chỉ.

Sao chép đoạn mã phía dưới vào trong bảng tính của bạn, sau đó sử dụng hàm GetURL trong bảng tính, giống như bất kỳ các hàm khác.

Function GetURL(cell As Range)
    GetURL = cell.Hyperlinks(1).Address
End Function

Ví dụ, để tìm URL cho một siêu liên kết trong ô B10, sử dụng công thức này:
= GetURL (B10)

5. Chuyển đổi văn bản URL để liên kết có thể click
Giả sử bạn có nhiều URL trong bảng tính của bạn, nhưng các URL không có liên kết, và bây giờ bạn cần phải chuyển đổi tất cả các url đến các siêu liên kết để có thể click, Bạn có thể nhấp đúp chuột vào từng cái một để chuyển đổi, nhưng điều này sẽ tốn thời gian nếu có rất nhiều các URL. Làm thế nào bạn có thể chuyển đổi nhiều URL đến các siêu liên kết để có thể click trong Excel?


Nếu bạn muốn loại thực hiện việc này , sử dụng các Macro sau đây

Sub AddHyperlinks()
    Dim Cell As Range
    For Each Cell In Intersect(Selection, ActiveSheet.UsedRange)
        If Cell <> "" Then
            ActiveSheet.Hyperlinks.Add Cell, Cell.Value
        End If
    Next
End Sub

Sau khi đã có Macro bên trên, quay trở lại màn hình làm việc Excel, bôi đen toàn bộ các danh sách cần chuyển đổi và chạy Macro.

Tin học văn phòng

Read more…

(Bỏ dấu tiếng việt trong Excel) - Hiện  nay Excel đã trở nên rất thông dụng đối với mọi đối tượng từ: nhân viên văn phòng, kế toán, thủ quỹ, giáo viên, kỹ sư, học sinh… Excel được dùng để giải quyết những công việc đơn giản đến phức tạp. Trong công việc có những người chỉ cần sử dụng các hàm có sẵn trong Excel cũng có thể giải quyết được tất cả các công việc, nhưng cũng có người phải tự viết hàm riêng để xử lý công việc được nhanh chóng và hiệu quả.

Trong thực tế có đôi khi bạn cần phải xử lý một danh sách hay một chuỗi ký tự sang tiếng việt không dấu, để thực hiện công việc này nếu sử dụng hàm có sẵn thì chưa có nhưng nếu xử lý bằng tay thì mất nhiều thời gian. Vậy có cách nào để có thể xử lý công việc này một cách nhanh chóng không? Hôm nay Tin học văn phòng xin giới thiệu với các bạn một giải pháp, đó là tự viết hàm loại bỏ dấu tiếng việt.

1. Khởi động Microsoft Excel

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

Vào menu Inser\Module

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

Function ConvertToUnSign(ByVal sContent As String) As String
     Dim i As Long
     Dim intCode As Long
     Dim sChar As String
     Dim sConvert As String
     ConvertToUnSign = AscW(sContent)
 
     For i = 1 To Len(sContent)
        sChar = Mid(sContent, i, 1)
        If sChar <> "" Then
            intCode = AscW(sChar)
        End If
        Select Case intCode
            Case 273
                sConvert = sConvert & "d"
            Case 272
                sConvert = sConvert & "D"
            Case 224, 225, 226, 227, 259, 7841, 7843, 7845, 7847, 7849, 7851, 7853, 7855, 7857, 7859, 7861, 7863
                sConvert = sConvert & "a"
            Case 192, 193, 194, 195, 258, 7840, 7842, 7844, 7846, 7848, 7850, 7852, 7854, 7856, 7858, 7860, 7862
                sConvert = sConvert & "A"
            Case 232, 233, 234, 7865, 7867, 7869, 7871, 7873, 7875, 7877, 7879
                sConvert = sConvert & "e"
            Case 200, 201, 202, 7864, 7866, 7868, 7870, 7872, 7874, 7876, 7878
                sConvert = sConvert & "E"
            Case 236, 237, 297, 7881, 7883
                sConvert = sConvert & "i"
            Case 204, 205, 296, 7880, 7882
                sConvert = sConvert & "I"
            Case 242, 243, 244, 245, 417, 7885, 7887, 7889, 7891, 7893, 7895, 7897, 7899, 7901, 7903, 7905, 7907
                sConvert = sConvert & "o"
            Case 210, 211, 212, 213, 416, 7884, 7886, 7888, 7890, 7892, 7894, 7896, 7898, 7900, 7902, 7904, 7906
                sConvert = sConvert & "O"
            Case 249, 250, 361, 432, 7909, 7911, 7913, 7915, 7917, 7919, 7921
                sConvert = sConvert & "u"
            Case 217, 218, 360, 431, 7908, 7910, 7912, 7914, 7916, 7918, 7920
                sConvert = sConvert & "U"
            Case 253, 7923, 7925, 7927, 7929
                sConvert = sConvert & "y"
            Case 221, 7922, 7924, 7926, 7928
                sConvert = sConvert & "Y"
            Case Else
                sConvert = sConvert & sChar
        End Select
     Next
     ConvertToUnSign = sConvert
  End Function

4. Quay trở lại màn hình Excel và sử dụng hàm vừa tạo
Có 2 cách để sử dụng hàm vừa tạo
- Cách 1:
+ Cú pháp: =ConvertToUnSign(Chuỗi cần chuẩn hóa)
+ Ví dụ: =ConvertToUnSign(“Cộng  hòa xã hội chủ nghĩa Việt Nam”)
- Cách 2:
+Cú pháp: = ConvertToUnSign(Địa chỉ ô cần chuyển)
+ Ví dụ: = ConvertToUnSign(A2)

Nếu bạn nào sau khi đã tạo hàm xong nhưng khi sử dụng thì gặp phải trường hợp giá trị trả về #NAME? thì phải thực hiện theo các bước dưới đây nếu sử dụng Office 2007.

- Kích nút Office Button\Excel Options
- Chọn Trust Center\Trust Center Settings

- Chọn Macro Settings\Enable all macros (not recommended; potentially dangerous code can run)

- Chọn OK

Hi vọng hàm loại bỏ dấu tiếng việt sẽ giúp ích các bạn nhiều trong công việc.

Tin học văn phòng

Read more…

(Hàm chuẩn hóa xâu trong Excel) - Bạn vừa Copy một danh sách học viên từ trên mạng vào Excel, tuy nhiên khi Copy vào Excel thì danh sách này xuất hiện nhiều khoảng trắng ở đầu và cuối mỗi học viên. Ngoài ra các chữ cái đầu tiên ở phần họ đệm và tên vẫn là chữ thường. Bạn đã chuẩn hóa lại danh sách này bằng tay, tuy nhiên với cách làm thủ công này tốn nhiều thời gian. Vậy có cách nào đó để có thể tự động xử lý chuẩn hóa toàn bộ danh sách này không?

Hôm nay Tin học văn phòng sẽ giới thiệu với các Bạn cách viết hàm chuẩn hóa trong Excel, hàm này sẽ thực hiện các công việc: xóa bỏ các ký tự trống ở đầu và cuối, Xóa bỏ từ 2 ký tự trống liên tiếp ở giữa các từ, Viết hoa chữ cái đầu tiên tại mỗi từ.

1. Khởi động Microsoft Excel

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

Vào menu Inser\Module

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

Function Chuanhoachuoi(str As String) As String

    Dim sChuoi As String

    Dim mlen As Long

    Dim i As Long

    'Neu chuoi =0 thi khong xu ly

    If Len(str) = 0 Then Exit Function

    'Xoa bo cac ky tu trang o dau va cuoi

    str = Trim(str)

    'Dem so ky tu chuoi

    mlen = Len(str)

    'Loai bo hai ki tu trong lien tiep

    For i = 1 To mlen

        If Mid(str, i, 1) = " " And Mid(str, i + 1, 1) = " " Then

            str = Replace(str, "  ", " ")

            i = i - 1

        End If

    Next

     For i = 1 To mlen

        ' Chuyen cac ky tu dau tien mot tu sang chu hoa

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

            sChuoi = sChuoi & " " & UCase(Mid(str, i + 1, 1))

            i = i + 1

        Else

            'Chuyen chu cai dau tien cua cau sang chu hoa

            If i = 1 Then

                sChuoi = UCase(Mid(str, 1, 1))

            Else

                sChuoi = sChuoi & LCase(Mid(str, i, 1))

            End If

        End If

     Next

    Chuanhoachuoi = sChuoi

End Function

Kích nút Save để lưu lại, bạn có thể xem thêm bài viết cách lưu file Excel khi chứa Macro hoặc hàm.

4. Quay trở lại màn hình Excel và sử dụng hàm vừa tạo

Có 2 cách để sử dụng hàm vừa tạo

- Cách 1:

+ Cú pháp: =Chuanhoachuoi(Chuỗi cần chuẩn hóa)

+ Ví dụ: =Chuanhoachuoi (“cong  hoa xa hoi  chu nghia viet nam”)

- Cách 2:

+Cú pháp: = Chuanhoachuoi (Địa chỉ ô cần chuyển)

+ Ví dụ: = Chuanhoachuoi (A2)

Nếu bạn nào sau khi đã tạo hàm xong nhưng khi sử dụng thì gặp phải trường hợp giá trị trả về #NAME? thì phải thực hiện theo các bước dưới đây nếu sử dụng Office 2007.

- Kích nút Office Button\Excel Options

- Chọn Trust Center\Trust Center Settings

- Chọn Macro Settings\Enable all macros (not recommended; potentially dangerous code can run)

- Chọn OK

Hi vọng hàm chuẩn hóa chuỗi sẽ giúp ích các bạn nhiều trong công việc.

Tin học văn phòng

Read more…

(Hàm chuyển số thành chữ trong Excel) - Đối với những người thường xuyên phải sử dụng Excel vào trong công việc tính toán, báo cáo hay thống kê thì việc chuyển số tiền thành chữ là công việc tốn khá nhiều thời gian do phải làm thủ công và thường phải  làm lặp đi lặp lại. Hiểu được những khó khăn này, đã có nhiều tiện ích (Add-ins) được viết để hỗ trợ việc chuyển số sang chữ, những tiện ích này đã hỗ trợ người dùng làm việc hiệu quả hơn, rút ngắn thời gian làm việc.

Những tiện ích này được phát triển bởi một nhóm lập trình hay một công ty nào đó, những tiện ích này đều đã được biên dịch nên người sử dụng không thể xem được Code hay chỉnh sửa lại theo ý muốn. Vậy bạn có muốn viết riêng 1 hàm chuyển đổi số sang chữ cho riêng mình không? Và viết ngay trên phần mềm Excel quen thuộc mình vẫn hàng ngày làm việc. Hôm nay Tin học văn phòng sẽ hướng dẫn các bạn viết hàm chuyển số sang chữ và hàm này sẽ được sử dụng như các hàm thông dụng trong Excel như: SUM, AVERAGE, COUT, IF…

1. Khởi động Microsoft Excel

2. Đổi tên 1 Sheet bất kỳ thành Chuso

Do việc nhập các ký tự tiếng việt tại màn hình soạn thảo Code bị lỗi, nên Sheet này có nhiệm vụ chứa toàn bộ các ký tự phục vụ việc chuyển số sang chữ. Với việc những chữ số không cần đưa trực tiếp vào Code mà được để trong Sheet, người sử dụng có thể dễ dàng thay đổi các mã font như: Unicode, VNI, TCVN3 tùy theo thói quen của từng người. Sau khi nhập toàn bộ dữ liệu,  Sheet Chuso sẽ có thông tin như phía dưới. 

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

Vào menu Inser\Module

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

Function ReadNumber(ByVal MyNumber)
    Dim VND_Dong, VND_Xu, Temp
    Dim DecimalPlace, Count
    ReDim Place(9) As String

    'Tham chieu den cac o trong Sheet Chuso de lay gia tri
    Place(2) = ActiveWorkbook.Sheets("Chuso").Range("D2").Value
    Place(3) = ActiveWorkbook.Sheets("Chuso").Range("D3").Value
    Place(4) = ActiveWorkbook.Sheets("Chuso").Range("D4").Value
    Place(5) = ActiveWorkbook.Sheets("Chuso").Range("D5").Value

    MyNumber = Trim(Str(MyNumber))
    DecimalPlace = InStr(MyNumber, ".")

    If DecimalPlace > 0 Then
        VND_Xu = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
        MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
    End If

    Count = 1
    Do While MyNumber <> ""
        Temp = GetHundreds(Right(MyNumber, 3))
        If Temp <> "" Then VND_Dong = Temp & Place(Count) & VND_Dong
        If Len(MyNumber) > 3 Then
            MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Else
            MyNumber = ""
        End If
        Count = Count + 1
    Loop
    Select Case VND_Dong
        Case ""
            VND_Dong = ActiveWorkbook.Sheets("Chuso").Range("D8").Value  'Khong Dong
        Case "One"
            VND_Dong = ActiveWorkbook.Sheets("Chuso").Range("D9").Value  'Mot Dong
         Case Else
            VND_Dong = VND_Dong & ActiveWorkbook.Sheets("Chuso").Range("D7").Value  'Dong
    End Select
    'Doi voi Xu
    Select Case VND_Xu
        Case ""
            VND_Xu = ActiveWorkbook.Sheets("Chuso").Range("D11").Value  'Khong xu
        Case "One"
            VND_Xu = ActiveWorkbook.Sheets("Chuso").Range("D12").Value  'Mot xu
              Case Else
            VND_Xu = " và " & VND_Xu & ActiveWorkbook.Sheets("Chuso").Range("D10").Value  'Xu
    End Select
    'Cat bo khoang trang dau tien
    VND_Dong = Right(VND_Dong, Len(VND_Dong) - 1)
    'Viet hoa chu cai dau tien
    VND_Dong = UCase(Left(VND_Dong, 1)) & Right(VND_Dong, Len(VND_Dong) - 1)
    ReadNumber = VND_Dong & VND_Xu
End Function
 
'Chuyen doi so tu 100->999 sang chu
Function GetHundreds(ByVal MyNumber)
    Dim Result As String
    If Val(MyNumber) = 0 Then Exit Function
    MyNumber = Right("000" & MyNumber, 3)
    ' Chuyen doi noi hang tram
    If Mid(MyNumber, 1, 1) <> "0" Then
        Result = GetDigit(Mid(MyNumber, 1, 1)) & ActiveWorkbook.Sheets("Chuso").Range("D6").Value 'Tram
    End If
    ' Chuyen doi hang chuc
    If Mid(MyNumber, 2, 1) <> "0" Then
        Result = Result & GetTens(Mid(MyNumber, 2))
    Else
        Result = Result & GetDigit(Mid(MyNumber, 3))
    End If
    GetHundreds = Result
End Function
 
'Chuyen doi so tu 10->99 sang chu
Function GetTens(TensText)
    Dim Result As String
    Result = ""
    If Val(Left(TensText, 1)) = 1 Then
        'Gia tri nam trong khoang tu 10->19
        Select Case Val(TensText)
            'Tham chieu den O B2 de lay chu: muoi
            Case 10: Result = ActiveWorkbook.Sheets("Chuso").Range("B2").Value
            Case 11: Result = ActiveWorkbook.Sheets("Chuso").Range("B3").Value
            Case 12: Result = ActiveWorkbook.Sheets("Chuso").Range("B4").Value
            Case 13: Result = ActiveWorkbook.Sheets("Chuso").Range("B5").Value
            Case 14: Result = ActiveWorkbook.Sheets("Chuso").Range("B6").Value
            Case 15: Result = ActiveWorkbook.Sheets("Chuso").Range("B7").Value
            Case 16: Result = ActiveWorkbook.Sheets("Chuso").Range("B8").Value
            Case 17: Result = ActiveWorkbook.Sheets("Chuso").Range("B9").Value
            Case 18: Result = ActiveWorkbook.Sheets("Chuso").Range("B10").Value
            Case 19: Result = ActiveWorkbook.Sheets("Chuso").Range("B11").Value
            Case Else
        End Select
    Else
        'Gia tri trong khoang tu 20->99
        Select Case Val(Left(TensText, 1))
         'Tham chieu den O C2 de lay chu: hai muoi
            Case 2: Result = ActiveWorkbook.Sheets("Chuso").Range("C2").Value
            Case 3: Result = ActiveWorkbook.Sheets("Chuso").Range("C3").Value
            Case 4: Result = ActiveWorkbook.Sheets("Chuso").Range("C4").Value
            Case 5: Result = ActiveWorkbook.Sheets("Chuso").Range("C5").Value
            Case 6: Result = ActiveWorkbook.Sheets("Chuso").Range("C6").Value
            Case 7: Result = ActiveWorkbook.Sheets("Chuso").Range("C7").Value
            Case 8: Result = ActiveWorkbook.Sheets("Chuso").Range("C8").Value
            Case 9: Result = ActiveWorkbook.Sheets("Chuso").Range("C9").Value
            Case Else
        End Select
        Result = Result & GetDigit(Right(TensText, 1))
    End If
    GetTens = Result
End Function

'Chuyen so tu 1->9 sang chu
Function GetDigit(Digit)
    Select Case Val(Digit)
        'Tham chieu den O A2 de lay chu: mot
        Case 1: GetDigit = ActiveWorkbook.Sheets("Chuso").Range("A2").Value
        'So hai
        Case 2: GetDigit = ActiveWorkbook.Sheets("Chuso").Range("A3").Value
        'So ba
        Case 3: GetDigit = ActiveWorkbook.Sheets("Chuso").Range("A4").Value
        'So bon
        Case 4: GetDigit = ActiveWorkbook.Sheets("Chuso").Range("A5").Value
        'So nam
        Case 5: GetDigit = ActiveWorkbook.Sheets("Chuso").Range("A6").Value
        'So sau
        Case 6: GetDigit = ActiveWorkbook.Sheets("Chuso").Range("A7").Value
        'So bay
        Case 7: GetDigit = ActiveWorkbook.Sheets("Chuso").Range("A8").Value
        'So tam
        Case 8: GetDigit = ActiveWorkbook.Sheets("Chuso").Range("A9").Value
        'So chin
        Case 9: GetDigit = ActiveWorkbook.Sheets("Chuso").Range("A10").Value
        Case Else: GetDigit = ""
    End Select
End Function

Kích nút Save để lưu lại, bạn có thể xem thêm bài viết cách lưu file Excel khi chứa Macro hoặc hàm.

5. Quay trở lại màn hình Excel và sử dụng hàm vừa tạo
Có 2 cách để sử dụng hàm vừa tạo
- Cách 1:
Cú pháp: =readnumber(Số cần chuyển)
Ví dụ: =readnumber(13456356)
- Cách 2:
Cú pháp: =readnumber(Địa chỉ ô cần chuyển)
Ví dụ: =readnumber(A2)
Nếu bạn nào sau khi đã tạo hàm xong nhưng khi sử dụng thì gặp phải trường hợp giá trị trả về #NAME? thì phải thực hiện theo các bước dưới đây nếu sử dụng Office 2007.

- Kích nút Office Button\Excel Options
- Chọn Trust Center\Trust Center Settings

- Chọn Macro Settings\Enable all macros (not recommended; potentially dangerous code can run)

- Chọn OK

Hi vọng hàm chuyển số sang chữ sẽ giúp ích các bạn nhiều trong công việc.

Tin học văn phòng

Read more…

(Cách ghi Workbook khi có Macro trong Excel 2007) - Đối với các phiên bản từ Office 2003 trở xuống, khi người dùng viết các Macro, các hàm (Function) trong Excel để phục vụ cho công việc nhanh và hiệu quả, Microsoft vẫn cho phép lưu File với định dạng XLS. Nhưng từ phiên bản Office 2007, nếu trong File Excel có Macro hay các hàm nếu người dùng vẫn lưu file dưới định dạng XLS hoặc XLSX thì toàn bộ các Macro cũng như hàm đều không được lưu. Nếu ai đó đã mất rất nhiều công sức cũng như thời gian để viết các Macro mà bị mất hoàn toàn thì thật là lãng phí.

Vậy làm sao để có thể lưu được File Excel nếu File có chứa Macro hay các hàm? Hôm  nay Kênh phần mềm việt sẽ hướng dẫn các bạn các bước để có thể lưu thành công File Excel nếu trong File có Macro. Và dưới đây là các bước để thực hiện.

- B1: Nhấp vào nút Office Button và sau đó nhấp vào Save As. Hộp thoại Save As xuất hiện.

- B2: Nhập tên và chọn vị trí lưu bảng tính.

- B3: Nhấp vào Save as Type mũi tên thả xuống. Một danh sách các loại tập tin sẽ xuất hiện.

- B4: Chọn kiểu File Excel Macro-Enabled Workbook. Khi lưu File dưới dịnh dạng này,  Excel thêm xlsm. phần mở rộng của tên tập tin.

- B5: Nhấp vào Lưu.

Trong trường hợp bạn lưu File dưới dạng XLS hoặc XLSX, bạn sẽ thấy một thông điệp cảnh báo cho bạn biết rằng các Macro sẽ không được lưu lại.

Với các bước thực hiện ở trên, bây giờ ta đã có thể lưu được các Macro, các hàm trong File Excel rồi.

Tin học văn phòng

Read more…

(Tin học văn phòng) - Như bạn biết, trong Excel để tìm kiếm một giá trị thoả mãn điều kiện nào đó, ta có thể dùng hàm Vlookup hay Hlookup. Tuy nhiên, hai hàm này chỉ cho phép tìm kiếm theo 1 điều kiện mà thôi. Ví dụ chúng ta có một bảng dữ liệu như hình 1, yêu cầu đặt ra là tìm điểm toán của một học sinh trong danh sách theo 2 điều kiện giới tính và tên. Vậy ta phải làm thế nào đây?

Tôi sẽ giới thiệu với các bạn một hàm tự tạo để làm việc này.
Trước tiên, bạn vào menu Tools\Macro\Visual Basic Editor (Alt + F11). Tại cửa sổ Microsoft Visual Basic, vào menu Insert\Module và nhập đoạn mã sau vào module vừa tạo.

Function FindTwoCondition(Table As Range, Val1 As Variant, _
Val1Occrnce As Integer, Val2 As Variant, Val2Col As Integer, ResultCol As Integer)
'Tabel la bang du lieu
'Val1 Dieu kien thu nhat
'Val1Occrnce gia tri thu n cua dieu kien trong cot
'Val2 dieu kien thu hai
'Val2Col cot thu n cua dieu kien thu 2
'ResultCol cot thu n can tim

Dim i As Integer, iCount As Integer
Dim rCol As Range

  For i = 1 To Table.Rows.Count
    If Table.Cells(i, 1) = Val1 And _
      Table.Cells(i, Val2Col) = Val2 Then
      iCount = iCount + 1
    End If
 
    If iCount = Val1Occrnce Then
      FindTwoCondition = Table.Cells(i, ResultCol)
      Exit For
    End If
  Next i
End Function

Sau khi đã nhập xong đoạn mã trên, bạn quay trở lại màn hình làm việc Excel bằng cách ấn Alt+Q. Bây giờ ta có thể sử dụng hàm vừa tạo như những hàm mà Excel đã hỗ trợ. Ví dụ tôi cần tìm Điểm Toán của người có tên là "Sơn" và có giới tính là "Nữ".
Trước tiên tôi lập bảng điều kiện như hình 2, tại ô I6 tôi nhập công thức sau:
=FindTwoCondition($B$4:$F$13,I4,1,I5,3,4)

Trong đó:
 -  $B$4:$F$13: Vùng dữ liệu
 -  I4: Tên cần tìm
 -   1: Tìm tên Sơn đầu tiên
 -  I5: Giới tính cần tìm
 -    3: Số thứ tự của cột Giới tính trong vùng dữ liệu
 -    4: Số thứ tự của cột Điểm Toán trong vùng dữ liệu

Kết quả sẽ trả về là 7. Chú ý ở đây hàm không phân biệt chữ thường chữ hoa.

Tin học văn phòng

Read more…

(Tin học văn phòng) - Tôi giới thiệu với các bạn cách viết các hàm Cắt tên, Cắt họ, Cắt họ đệm trong Excel bằng ngôn ngữ VBA.

+    Bước 1: Mở chương trình Microsoft Excel.

+    Bước 2: Vào Menu Tool\Macro\Visual Basic Editor. (Hoặc ấn tổ hợp phím Alt + F11). Xuất hiện cửa sổ Microsoft Visual Basic.

          

+    Bước 3: Vào Menu Insert\Module. Xuất hiện cửa sổ Code

+    Bước 4: Nhập đoạn Code phía dưới vào trong cửa sổ Code (Hình 2)

‘Bắt đầu đoạn 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 ‘Kết thúc đoạn Code 

+    Bước 5: Quay trở lại màn hình làm việc chính của Excel bằng cách vào Menu File\Close And Return to Microsoft Excel (Hoặc ấn tổ hợp phím Alt + Q).

+    Bước 6: Sử dụng những hàm chúng ta đã tạo

-   Tại ô B1 ta có Họ và tên 1 người như ở Hình 3

-    Bây giờ ta chỉ muốn cắt tên của người đó, ta sẽ làm như sau:

-    Đưa vệt sáng đến ô B2, nhập vào hàm =Catten(B1) và ấn Enter kết quả ô B2 có giá trị là Thảo

-    Nếu chúng ta muốn cắt Họ thì dùng hàm Catho, muốn cắt Họ đệm thì dùng hàmCathodem

Lâm Quang Bình - khampha1234@gmail.com

Read more…