Tổng hợp thủ thuật, mẹo vặt Excel hữu ích, hay dùng dành cho dân văn phòng giúp hoàn thành công việc nhanh hơn và hiệu quả hơn

Excel là phần mềm văn phòng phổ dụng được dân văn phòng sử dụng khá phổ biến, nắm vững & sử dụng thông thạo phần mềm Excel sẽ giúp bạn hoàn thành công việc nhanh hơn, đồng thời giúp các bạn làm công việc văn phòng hiệu quả hơn, Trong bài viết lần này Gia Bảo Blog sẽ giúp bạn tổng hợp các thủ thuật, mẹo vặt Excel hữu ích mà các bạn làm việc trong văn phòng thường hay dùng giúp chúng ta tiết kiệm được nhiều thời gian, công sức.

Trong bài viết về thủ thuật & mẹo vặt Excel hữu ích cho dân văn phòng Gia Bảo Blog chia ra là 3 nhóm thủ thuật - mẹo vặt Excel bao gồm : Mục A : "Danh sách Thủ Thuật, mẹo vặt, công thức trong Excel", Mục B : " Sử Dụng Phím Tắt" , Mục C : ứng Dụng code VBA.

A. Danh Sách Thủ thuật, Mẹo vặt & công thức trong Excel :

A.01 Hàm thường dùng trong Excel :

- Hàm đếm COUNTIF
- Hàm tính tổng SUM
- Hàm tính tổng SUMIF có điều kiện
- Hàm tính giá trị trung bình AVERAGE
- Hàm MIN, MAX
- Hàm IF
- Hàm AND
- Hàm OR
- Hàm YEAR, MONTH, DAY
- Hàm tính số ngày giữa 2 mốc thời gian DAYS
- Dấu & nối chuỗi văn bản
- Hàm LEFT
- Hàm RIGHT
- Hàm MID
- Hàm LEN
- Hàm VLOOKUP
- Hàm INDEX

A.02 Chuyển đổi những ô có số 0 thành dấu gạch ngang :

Để làm được điều này, trong ô cần chuyển đổi số 0 thành dấu gạch ngang bạn click chuột phải, chọn Format Cells chọn Tab Number, chọn Custom trong mục Category : Trong ô giá trị "Type" bạn tạo công thức sau : 0;-0;-;@

A.03 Chuyển đổi dòng thành cột và ngược lại :

Chọn cột hoặc hàng cần chuyển đổi click chuột phải chọn lệnh Copy, sau đó đến ô vị trí cần chuyển đổi, click chuột phải chọn lệnh Paste Special, trong tab tích chọn transpose sau đó chọn Ok, xem kết quả.

A.04 Cố định dòng / cột trong sheet Excel :

Đầu tiên chọn vị dòng hoặc cột & dòng cần cố định, kế đến trên MeNu Excel bạn chọn mục view, chọn tiếp FreeZe Panes chọn tiếp FreeZe Panes để cố định vị trí dòng và cột

A.05 Chọn vùng cần In :

Bước 1 bạn chọn vùng cần in, sau đó trên thanh menu Excel bạn chọn mục Page layOut sau đó chọn Print Area sau đó chọn tiếp Print Area - nếu muốn hủy vùng in bạn chọn Clear Print Area

A.06 Cách căn chỉnh trang in vừa với khổ giấy trong Excel :

B. Sử dụng phím tắt hay dùng trong Excel :

Ctrl + phím mũi tên : Di chuyển nhanh đến ô chứa dữ liệu theo hướng mũi tên
Ctrl + z : Undo
Ctrl + c : Copy
Ctrl + x : Cắt nội dung
Ctrl + v : Dán nội dung
Alt + Enter : Xuống dòng dữ liệu trong ô tính

C. Ứng dụng Code VBA trong việc tự động hóa công việc văn phòng bằng File Excel :

VBA là chữ viết tắt của Visual Basic for Applications, đây là ngôn ngữ lập trình của Excel nói riêng và các ứng dụng khác của Microsoft Office nói chung. VBA được sử dụng để viết tạo các macro trong Excel, giúp tự động hóa công việc dành cho người sử dụng Excel làm công việc văn phòng, kế toán, dân kiến trúc.

C.1 Tổng hợp câu lệnh & cú pháp - code VBA hay dùng trong Excel :

Worksheets.Cells([RowIndex], [ColumnIndex])
Range.Cells([RowIndex], [ColumnIndex])

C.2 Ứng dụng VBA tạo mục lục danh sách các Sheet trong Excel

Bạn Copy đoạn code sau rồi chép vào Module trong MicroSoft Visua Basic trong Ecel, Trong Sheet VBA Danh sach Sheet bạn tạo Shape sau đó gán Macro TaoDanhSachSheetExcel vào Shape vừa tạo để cập nhật danh sách Sheet Excel của mình, giúp bạn dễ dàng di chuyển nhanh chóng đến Sheet trong bảng tính.

Sub TaoDanhSachSheetExcel ()
Dim wsSheet As Worksheet
Dim ws As Worksheet
Dim Counter As Long

On Error Resume Next
Set wsSheet = Sheets("VBA Danh sach Sheet")
'Kiểm tra sự tồn tại của Sheet - VBA Danh sach Sheet

On Error GoTo 0
If wsSheet Is Nothing Then
'Nếu chưa có sheet "VBA Danh sach Sheet" thì tạo mới Sheet "VBA Danh sach Sheet" chèn vào vị trí đầu tiên của bảng tính Workbook
Set wsSheet = ActiveWorkbook.Sheets.Add(Before:=Worksheets(1))
wsSheet.Name = "VBA Danh sach Sheet"
End If

'Tạo tiêu đề
With wsSheet
.Cells(9, 1) = "DANH SÁCH CÁC SHEET"
.Cells(9, 1).Name = "Index"
.Cells(11, 1).Value = "STT"
.Cells(11, 2).Value = "Tên Sheet"
End With

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

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

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

Columns("B:B").ColumnWidth = 30
With Range("B11")
.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 + 11, 1).Value = Counter
'Tao lien ket
wsSheet.Hyperlinks.Add Anchor:=wsSheet.Cells(Counter + 11, 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

Nhận xét Code VBA này khá hữu ích trong trường hợp bảng tính của bạn có nhiều Sheet, Danh sách Sheet sẽ giúp bạn quản lý hiệu quả từng trang bảng tính.

C.3 Sử dụng VBA - viết hàm tự tạo thay thế ký tự trong văn bản trong Excel :

Public Function f_doikytu(chuoikytu1 As String, kytucu1 As String, kytumoi1 As String)

    solanxuathien = 0

If UCase(kytucu1) = UCase(kytumoi1) Then
    GoTo thoat
End If

    dodaikytucu1 = Len(kytucu1)
    dodaikytumoi1 = Len(kytumoi1)
    dodaichuoi1 = Len(chuoikytu1)

If dodaichuoi1 = 0 Or dodaikytucu1 = 0 Then
    GoTo thoat
End If

    For i = 1 To dodaichuoi1
        For m = i To dodaichuoi1
            kytucat1 = Mid(chuoikytu1, m, dodaikytucu1)
            If UCase(kytucat1) = UCase(kytucu1) Then
                solanxuathien = solanxuathien + 1
            End If
        Next m
    Next i

    For i = 1 To solanxuathien
    dongtext = f_laplai_chuoi(chuoikytu1, kytucu1, kytumoi1)
    chuoikytu1 = dongtext
    Next

If solanxuathien = 0 Then
    dongtext = chuoikytu1
End If

    f_doikytu = dongtext

thoat:

End Function

Public Function f_laplai_chuoi(chuoikytu As String, kytucu As String, kytumoi As String)

If UCase(kytucu) = UCase(kytumoi) Then
    GoTo thoat
End If

    Dim chuoikytumoi As String

    dodaikytucu = Len(kytucu)
    dodaikytumoi = Len(kytumoi)
    dodaichuoi = Len(chuoikytu)

    chuoikytumoi = ""

If dodaichuoi = 0 Or dodaikytucu = 0 Then
    GoTo thoat
Else

stt = 0

    For i = 1 To dodaichuoi
    kytucat = Mid(chuoikytu, i, dodaikytucu)
If UCase(kytucat) = UCase(kytucu) Then
    stt = stt + 1

    If i = 1 Then

    chuoikytumoi = kytumoi & Right(chuoikytu, dodaichuoi - dodaikytucu)
    GoTo thoat
    End If

    If i = dodaichuoi Then
    chuoikytumoi = Left(chuoikytu, dodaichuoi - dodaikytucu) & kytumoi
    GoTo thoat
    End If

   chuoikytumoi = Left(chuoikytu, i - 1) & kytumoi & Right(chuoikytu, dodaichuoi - dodaikytucu - (i - 1))

End If
    Next
End If

If stt = 0 Then
    chuoikytumoi = chuoikytu
End If

thoat:
f_laplai_chuoi = chuoikytumoi

End Function

Ứng dụng hàm tự tạo giúp bạn tự động thay thế ký tự trong chuỗi văn bản, được ứng dụng trong việc xử lý mã hàng hóa, mã khách,.... đáp ứng nhu cầu dành cho các bạn làm nghề kế toán dịch vụ.

Đang cập nhật...

Phụ Lục :

Tiện Ích & Addin Excel hỗ trợ công việc văn phòng - hỗ trợ trường học :

Phần mềm Excel :

Phần mềm kế toán

Web chuyên đề VBA & Excel :

https://lequocthai.com/code-vba-excel-doc-so-ra-chu/