Hướng dẫn phá Pass Protect sheet trong Excel không cần phần mềm

Cách phá Password Excel không cần phần mềm, phá pass sheet excel, phần mềm phá pass excel hiệu quả, phá mật khẩu excel

Cách làm trong Excel 2007/2010 như sau: bấm Alt + F11 để hiện ra bảng Visual Basic Editor rồi sau đó thực hiện theo bước 3 như phía dưới.Cách làm trong Excel 2003 như sau:

1. Mở file Excel có chứa các Sheet có pass
2 Chọn menu Tools -> Macro -> Visual Basic Editor
3 Nhấp đúp vào Sheet mà bạn muốn phá pass
Nếu bạn chưa thấy các Sheet bên phía trái của màn hình thì bấm: Ctrl + R để hiện các Sheet.
( Nhiều máy khi bấm Alt + F11 thì không thấy cửa sổ bên trái hiện các Sheet )
4. Copy đoạn mã sau vào và nhấn menu Run -> Run Sub/UserForm (hoặc nhấn F5)

5. Máy hỏi bạn có muốn xử lý nó không ? Nếu muốn bấm YES

6. Bạn chờ một lúc máy sẽ phá Pass cho bạn. Và có thông báo sau khi phá Pass thành công.

Mã hoạt động:

Sub PasswordBreaker() If ActiveSheet.ProtectContents = False Then MsgBox "Sheet '" & ActiveSheet.Name & "' is unprotected!", vbInformation Else If MsgBox("Sheet '" & ActiveSheet.Name & "' is protected, do you want to unprotect it?", _ vbYesNo + vbQuestion, "Unprotect Active Sheet") = vbNo Then Exit Sub Dim i As Integer, j As Integer, k As Integer Dim l As Integer, m As Integer, n As Integer Dim i1 As Integer, i2 As Integer, i3 As Integer Dim i4 As Integer, i5 As Integer, i6 As Integer On Error Resume Next For i = 65 To 66: For j = 65 To 66: For k = 65 To 66 For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66 For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66 For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126 ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _ Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _ Chr(i4) & Chr(i5) & Chr(i6) & Chr(n) Next: Next: Next: Next: Next: Next Next: Next: Next: Next: Next: Next If ActiveSheet.ProtectContents = False Then MsgBox "Sheet '" & ActiveSheet.Name & "' is unprotected!", vbInformation End If End Sub

Xong!

Lưu ý quan trọng:

- Lưu file dưới dạng (save as) sau khi hoàn thành, hoặc lưu ngay sang file khác từ khi mới mở: lý do là một số bạn tải file từ mạng về nên mặc định file chỉ đọc (Read only), khi đó mình đã gặp lỗi: mở lại file thì báo nhập mật khẩu file excel, công cốc!  Save as file sang tên mới cũng là để backup 1 file khác, đề phòng trong quá trình làm mất điện, máy yếu –>gây ra lỗi file gốc ngoài ý muốn.

- Bài hướng dẫn này dành cho việc phá mật khẩu password ở sheet: File ở chế độ protect readonly: Trường hợp này bạn cần bỏ protect readonly mới đổi được. Có thể cần mật khẩu mới có thể bỏ được protect. Lưu ý dạng khoá file này khác hoàn toàn với sheet protected.

– File có quá nhiều sheet, định dạng, dữ liệu và công thức trên Excel 2003 trở về trước: Các excel phiên bản cũ (2003 về trước) không hỗ trợ file quá nhiều sheet, định dạng, dữ liệu và công thức, nó sẽ có nhiều lỗi xảy ra, nhẹ nhất là không copy hay edit được, nặng thì mất toàn bộ định dạng và công thức. Trong trường hợp này cách duy nhất là dùng Excel 2007 hoặc mới hơn.

Theo chuongduong.net

Xem tiếp ...

Bảo mật file excel

Bài viết sẽ trình bày các bước cách thức “đóng gói” và tạo giao diện như một phần mềm bằng ngôn ngữ VB và người dùng phải cài đặt hoặc điền CD key khi mở tập tin.

1. Trong file Excel, bạn có thể viết một số đoạn code bằng ngôn ngữ VBA để xử lý dữ liệu trong file Excel hiện hành (và các file Excel khác nếu muốn). Các đoạn code VBA này sẽ truy xuất dữ liệu của file Excel thông qua mô hình hướng đối tượng.
Thí dụ bạn có thể dùng các đối tượngActiveWorkbook, Workbooks(<tên>) để truy xuất workbook hiện hành hay workbook với tên xác định, hay các đối tượng ActiveSheethay Sheets(<tên>) để truy xuất worksheet hiện hành hay worksheet với tên xác định. Thí dụ thủ tục CreateSheet sau đây sẽ tạo 1 worksheet mới (trong workbook hiện hành) có tên là “YourSheet“, rồi tự động nhập thông tin tọa độ của từng cell trong vùng cell từA1 tới D10:
Sub CreateSheet()
Dim rg As Range
Dim row As Integer, col As Integer
‘tạo worksheet mới
Sheets.Add
‘đặt tên cho worksheet mới
ActiveSheet.Name = “YourSheet”
‘thiết lập vùng cell cần xử lý
Set rg = Range(“A1:D10″)
‘nhập thông tin tọa độ cho từng cell
‘duyệt theo hàng
For row = 1 To 10
‘duyệt theo cột
For col = 1 To 4
rg.Cells(row, col).Value = “(” & row & “,” & col & “)”
Next col
Next row
‘lưu kết quả lên file
ActiveWorkbook.SaveAs “c:YourWB.xls”
End Sub
Thật ra các đối tượng ActiveWorkbook,Workbooks, ActiveSheet hay Sheets được truy xuất trong thủ tục trên chỉ là đối tượng con của đối tượng Application, đối tượng Application miêu tả ứng dụng Excel đang chạy. Do đó nếu muốn dùng đường dẫn tuyệt đối để truy xuất các đối tượng, thủ tụcCreateSheet() trên có thể được viết lại như sau:
Sub CreateSheet()
Dim rg As Range
Dim row As Integer, col As Integer
‘tạo worksheet mới
Application.Sheets.Add
‘đặt tên cho worksheet mới
Application.ActiveSheet.Name = “YourSheet”
‘thiết lập vùng cell cần xử lý
Set rg = Range(“A1:D10″)
‘nhập thông tin tọa độ cho từng cell
‘duyệt theo hàng
For row = 1 To 10
‘duyệt theo cột
For col = 1 To 4
rg.Cells(row, col).Value = “(” & row & “,” & col & “)”
Next col
Next row
‘lưu kết quả lên file
Application.ActiveWorkbook.SaveAs “c:YourWB.xls”
End Sub
Bây giờ nếu muốn chuyển các đoạn code VBA đã chạy tốt trong file Excel thành ứng dụng VB 6.0 độc lập, bạn cần thực hiện một số công việc sau:
– Sử dụng thư viện “Microsoft Excel x.0 Object Library” chứa các đối tượngExcel.Application,ActiveWorkbook,Workbooks, ActiveSheet, Sheets… trong Project quản lý ứng dụng VB 6.0 của bạn.
– Viết code khởi tạo đối tượng Excel.Application miêu tả ứng dụng Excel, trước khi sử dụng đối tượng này để truy xuất các đối tượng con của nó.
– Copy đoạn code VBA dùng các đối tượng bằng đường dẫn tuyệt đối đã chạy tốt vào đây.
– Sau khi xử lý dữ liệu của file Excel, viết code đóng đối tượng Excel.Application lại.
2. Muốn bảo vệ phần mềm bởi người dùng bất hợp pháp, bạn có thể viết thêm đoạn code kiểm tra password, chèn đoạn code này vào đầu phần mềm.
Để thấy rõ cách giải quyết các vấn đề vừa trình bày, chúng tôi xin giới thiệu qui trình điển hình để xây dựng 1 ứng dụng VB 6.0, nó sẽ yêu cầu người dùng nhập password, kiểm tra password nhập với password hợp pháp được lưu trong ứng dụng (hay trên file dữ liệu), nếu không đúng thì ứng dụng sẽ dừng, còn nếu đúng chương trình sẽ chạy tiếp. Chương trình gồm 1 form chính chứa 1 button Create, khi người dùng click chuột button này, ứng dụng sẽ chạy Excel, tạo 1 sheet mới có tên là “YourSheet“, nhập tọa độ vào từng cell trong vùng cell từ A1 tới D10, lưu kết quả lên file “c:YourWB.xls“:
1. Chạy VB 6.0, khi cửa sổ New project hiển thị, chọn icon Standard EXE rồi button Open để tạo mới Project chứa Form đơn giản.
2. Chọn menu Project.Components để hiển thị cửa sổ Components, duyệt tìm và chọn thư viện có tên làMicrosoft Excel x.0 Object Library rồi OK để thêm các đối tượng trong thư viện này vào ToolBox của Project hiện hành.
3. Vẽ 1 button vào form, thíết lập lại thuộc tính (Name)=btnCreate, thuộc tínhCaption=”Create”.
4. Ấn kép chuột vào button vừa tạo để tạo thủ tục xử lý sự kiện click chuột vào button rồi viết code cho thủ tục này như sau:
Option Explicit
‘khai báo hàm API cần dùng
Private Declare Sub ExitProcess Lib “kernel32″ (ByVal uExitCode As Long)
‘định nghĩa hằng miêu tả password đã mật mã
Const ENPASS = “zpvsqbtt”
‘hàm mật mã chuỗi password đơn giản
Function Encryph(pass As String) As String
Dim bytes() As Byte
Dim str As String
Dim i As Integer
bytes = StrConv(pass, vbFromUnicode)
str = “”
For i = 0 To UBound(bytes)
str = str & Chr(bytes(i) + 1)
Next i
Encryph = str
End Function
‘thủ tục khởi tạo form
Private Sub Form_Load()
Dim pass As String
‘hiển thị form nhập password
frmPassword.Show vbModal
pass = frmPassword.txtPassword
Unload frmPassword
‘kiểm tra password nhập
If ENPASS <> Encryph(pass) Then
‘nếu sai thì dừng ứng dụng
ExitProcess (1)
End If
‘nếu đúng thì chạy tiếp bình thường
End Sub
‘thủ tục phục vụ click button Create
Private Sub btnCreate_Click()
‘định nghĩa các biến cần dùng
Dim Application As Excel.Application
Dim WorkBook As Excel.WorkBook
Dim wksSheet As Excel.Worksheet
Dim rg As Range
Dim row As Integer, col As Integer
‘tạo ứng dụng Excel
Set Application = New Excel.Application
‘tạo workbook Excel
Set WorkBook = Application.Workbooks.Add
‘đoạn code VBA đã có sẵn từ file Excel
‘tạo worksheet mới
Application.Sheets.Add
‘đặt tên cho worksheet mới
Application.ActiveSheet.Name = “YourSheet”
‘thiết lập vùng cell cần xử lý
Set rg = Range(“A1:D10″)
‘nhập thông tin tọa độ cho từng cell
‘duyệt theo hàng
For row = 1 To 10
‘duyệt theo cột
For col = 1 To 4
rg.Cells(row, col).Value = “(” & row & “,” & col & “)”
Next col
Next row
‘lưu kết quả lên file
Application.ActiveWorkbook.SaveAs “c:YourWB.xls”
‘dừng ứng dụng Excel
Application.Quit
Set Application = Nothing
End Sub
5. Dời chuột về cửa sổ Project (thường nằm ở phía trên phải của màn hình), ấn phải chuột vào mục Project(phần tử gốc của cây Project) để hiển thị menu chức năng. Chọn mục Add.Form để hiển thị cửa sổ tạo form, chọn icon Form rồi button Open để tạo 1 form trống mới. Thiết lập lại thuộc tínhCaption=”Nhập password sử dụng”, thuộc tính (Name)=frmPassword.
6. Vẽ 1 label, 1 textbox nhập password và 1 button OK vào form như hình vẽ:
Thiết lập thuộc tính (Name) cho TextBox làtxtPassword, thuộc tính (Name) cho button là btnOK. Ấn kép chuột vào button OK để tạo thủ tục xử lý sự kiện click chuột trên button này rồi viết code cho thủ tục như sau :
Private Sub btnOk_Click()
Me.Visible = False
End Sub
7. Chọn menu File.Save Project As… để lưu Project lên đĩa.
8. Chọn menu Run.Start để chạy thử ứng dụng.
9. Khi ứng dụng chạy, form yêu cầu nhập password sẽ hiển thị, nếu người dùng nhập sai password qui định bởi ứng dụng, ứng dụng sẽ dừng. Nếu người dùng nhập đúng password thì form chính của ứng dụng sẽ hiển thị, lúc này nếu người dùng ấn vào button Create thì đoạn code tạo đối tượng Excel và xử lý dữ liệu sẽ chạy.

Theo bikipvanphong

Xem tiếp ...

Nhập dữ liệu đồng thời vào nhiều sheet

Thông thường người dùng Excel có những dữ liệu muốn nhập vào nhiều sheet khác nhau. Bạn có thể dùng công cụ Group các sheet lại để có thể nhập dữ liệu đồng thời. Nhưng cũng có 1 cách khác nhanh chóng và linh hoạt hơn nếu bạn dùng vài dòng lệnh VBA.
Nhưng phải nói đến công cụ Group cái đã nhỉ? Công cụ Group dùng để nhập dữ liệu vào nhiều sheet 1 lúc chắc có nhiều người chưa dùng đến. Nó dùng để kết nối các sheets với nhau trong phạm vi work book.

1. Group các sheet lại bằng tay:

Để nhóm các sheet lại, các bạn chỉ việc click chọn sheet thứ nhất, nơi bạn sẽ nhập liệu từ bàn phím vào, rồi vừa nhấn Ctrl, vừa nhấn vào tên những sheet khác mà bạn muốn nhập liệu đồng thời. Khi bạn gõ bất kỳ cái gì vào sheet hiện hành, dữ liệu đó cũng sẽ tự động nhập vào các sheet còn lại trong nhóm. Điệp vụ hoàn tất nhanh chóng (nghe giống 007 nhỉ).

Để thoát khỏi tình trạng group, bạn chỉ việc hoặc click chọn 1 sheet khác ngoài nhóm, hoặc click chuột phải lên tên 1 sheet trong nhómbất kỳ, chọn Ungroup Sheets. Hoặn nếu bạn chỉ muốn gỡ 1 trong số các sheet ra khỏi nhóm, hãy Ctrl click vào tên sheet đó.
Khi 2 hay nhiều sheet Group với nhau, trên thanh tiêu đề của Windows sẽ có chữ [Group] (tronh ngoặc vuông). Nhìn vào đó bạn có thể biết bạn vẫn còn đang trong tình trạng group các sheets.

Trừ khi bạn có cặp mắt sắc như chim ưng, hoặc bén như dao cạo, thường thì bạn không để ý đến dấu hiệu này, bạn sẽ quên và không ungroup khi công việc đã xong. Chính vì thế tôi khuyên bạn ngay khi thực hiện xong thao tác nhập liệu hàng loạt vào các sheet, phải ungroup chúng ngay.

Dù phương pháp này là dễ nhất, nhưng có nghĩa là bạn phải nhớ và nhớ group và ungoup khi cần, nếu không bạn sẽ vô tình ghi đè lên dữ liệu ở những sheet mà bạn không nhìn thấy trên màn hình hiện thời. Thí dụ như bạn chỉ muốn ghi cùng lúc dữ liệu vào 1 vùng nào đó của các bảng tính, còn ngoài vùng đó thì dữ liệu các sheet phải khác nhau. Thế nhưng khi các sheet đã group rồi thì nó có thèm quan tâm bạn đang nhập liệu ở vùng nào đâu?

2. Group các sheet lại 1 cách tự động:

Bạn có thể giải quyết vấn đề trên bằng cách dùng 1 đoạn code VBA hết sức đơn giản. Để đoạn code làm việc được, nó phải được đặt trong các sự kiện của riêng sheet nào bạn muốn. Để vào trang soạn thảo code của sheet nào, thì click phải chuột vào tên sheet đó và chọn View Code. 

Bạn có thể chọn 1 trong các sự kiện của sheet, những sự kiện chỉ liên quan đến sheet đó như đổi ô chọn, thay đổi nội dung 1 ô, kích hoạt sheet, thoát khỏi sheet, … để thực thi đoạn code VBA của mình.

Thường thì bạn được đưa thẳng đến trang soạn thảo code riêng của sheet nếu bạn chọn View Code như trên hướng dẫn. Bạn có thể kiểm tra lại quả thực nó thuộc về sheet bạn chọn hay không bằng cách nhìn vào cửa sổ VBA Project bên trái, nếu bạn thấy dạng thư mục This Workbook – Sheetname và 1 sheet đang chọn thì đúng. nếu không bạn phải tìm đúng sheet bạn cần và doubled click nó.

Trước tiên bạn phải đặt name cho vùng ô bạn muốn nhập dữ liệu trùng trong các sheet, giả sử là “MyRange”.
Sau đó gõ đoạn code này vào khung soạn thảo bên phải:

PHP Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Range("MyRange"), Target) Is Nothing Then
Sheets(Array("Sheet5", "Sheet3", "Sheet1")).Select
Else
Me.Select
End If
End Sub  

Trong đoạn code trên, sheet5 được ghi đầu tiên trong mảng Array, vì bạn muốn nó là sheet mà bạn sẽ gõ dữ liệu nhập vào. Bạn có thể chọn những sheet khác để group chúng lại bên dưới sheet5. Sau khi gõ code xong, hoặc nhấn nút View Object, hoặc nhấn nút View Microsoft Excel, hoặc nhấn Alt- Q, Alt-F11 hoặc đóng hẳn cửa sổ VBA trở về màn hình Excel. Lưu bảng tính lại.

Cần nhắc lại rằng đoạn code trên phải nằm trong sheet được khai báo đầu tiên của Array, là sheet mà bạn sẽ nhập dữ liệu từ bàn phím.

Khi bạn đã hoàn tất, mỗi khi bạn click chọn 1 ô nào đó trong sheet5, đoạn code sẽ kiểm tra xem ô bạn chọn có nằm trong vùng đã đặt tên “MyRange” không. Nếu đúng, code sẽ Group các sheet lại với nhau để bạn nhập dữ liệu hàng loạt sheets. Nếu không phải, nó ungroup chúng ra bằng cách đơn giản là kích hoạt sheet hiện hành. Dùng đoạn code trên sẽ tiện lợi ở chỗ nó giải phóng bạn khỏi cái việc phải nhớ Group khi bắt đầu và UnGroup khi kết thúc nhập liệu trong vùng chọn.

Bây giờ nếu bạn muốn những dữ liệu như nhau được nhập vào các sheet chọn, nhưng ở những vị trí khác nhau, hãy dùng đoạn code khác:

PHP Code:

Private Sub worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("MyRange"), Target) Is Nothing Then
With Range("MyRange")
.Copy Destination:=Sheets("Sheet3").Range("A1")
.Copy Destination:=Sheets("Sheet1").Range("D10")
End With
End If
End Sub  

Đoạn code này cũng phải được đặt trong khung soạn thảo của riêng sheet5.

Đoạn code này hoạt động khác với đoạn code trên. Khi bạn thay đổi nội dung của 1 ô nào đó, code sẽ xác định xem ô hiện hành có nằm trong vùng "MyRange" hay không, nếu phải thì copy nguyên vùng "MyRange" (của sheet5) và paste vào đồng thời ô A1 của sheet1 và D10 của sheet2. Ngược lại thì không làm gì cả.

Viết bài: Quân Phạm tại forum giaiphapexcel

Xem tiếp ...

VBA với siêu liên kết (Hyperlink) trong Excel

(Thủ thuật 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

Xem tiếp ...

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

(Thủ thuật VBA trong Excel)

- Để tính tổng,  trung bình, đếm số lượng theo một điều kiện ta thường sử dụng các hàm như: SUMIF, AVERAGEIF, COUTIF. Tuy nhiên những hàm này được sử dụng để tính toán, thống kê theo điều kiện là các giá trị như: số, ký tự, ngày tháng…  Trong thực tế công việc có những trường hợp ta phải tính toán, thống kê không theo giá trị mà theo định dạng  như: màu chữ, màu nền ô. Ví dụ như bạn muốn tính tổng các ô trong một vùng có màu chữ là đỏ, hay tính trung bình của những ô có màu nền là vàng. Với những trường hợp này  trong Excel hiện chưa có hàm nào để thực hiện công việc này. Vậy phải tính toán thủ công nếu gặp phải trường hợp như này sao? 

Hôm nay Kênh phần mềm việt sẽ giới thiệu với mọi người cách tự viết hàm để có thể tính tổng, tính trung bình, đếm số lượng theo màu chữ, màu nền ô. Dưới đây là các bước thực hiện.

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ã phía dưới sau vào màn hình bên phải

'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 trở lại màn hình Excel và sử dụng các hàm vừa tạo
a. Thao tác với màu chữ
- Đếm số lượng
Cú pháp: = CountCellsByFontColor (Vùng cần đếm, Vùng điều kiện cần đếm)
Ví du: =CountCellsByFontColor($C$3:C$12,B16)
Trong đó:
+ $C$3:C$12: Là vùng dữ liệu cần đếm
+ B16: Vùng điều kiện màu chữ
- Tính tổng
Cú pháp: = 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
Cú pháp: = 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
Cú pháp: = CountCellsByBackColor (Vùng cần đếm, Vùng điều kiện cần đếm)
Ví du: =CountCellsByBackColor($C$3:C$12,B16)
Trong đó:
+ $C$3:C$12: Là vùng dữ liệu cần đếm
+ B16: Vùng điều kiện màu chữ
- Tính tổng
Cú pháp: = SumCellsByBackColor(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
Cú pháp: = AverageCellsByBackColor (Vùng cần tính TB, Vùng điều kiện cần tính TB)

Tin học văn phòng

Xem tiếp ...

VBA với ký tự số trong Excel

(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

Xem tiếp ...

Tự động chuẩn hóa chuỗi khi Enter trong Excel

(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

Xem tiếp ...

Sao chép thiết lập Page Setup sang Sheet khác trong Excel 2007

(Sao chép thiết lập Page Setup trong Excel)

- Các công việc thiết lập thông số trong mục Page Setup như: cỡ giấy, lề trái, lề phải, hướng giấy, Header trên, Header dưới không phải là việc làm khó. Tuy nhiên nếu như ta phải thiết lập chúng ở nhiều Sheet khác nhau thì ngoài việc mất thời gian, nó còn tạo sự nhàm chán cho người sử dụng.

Vậy có cách nào chỉ cần thiết lập chuẩn các thông số trong mục Page Setup ở một Sheet rồi sao chép các thông số này áp dụng cho 1 hoặc nhiều Sheet mong muốn không? Giống như chức năng sao chép định dạng Format Painter.

Hôm nay Kênh phần mềm việt sẽ hướng dẫn mọi người cách sao chép các thiết lập Page Setup từ một Sheet sang các Sheet khác.

- B1: Thiết lập thông số Page Setup chuẩn cho 1 Sheet

- B2: Giữ phím Ctrl, nhấp vào tên các Sheet sao chép thiết lập, để chọn toàn bộ các Sheet  trên File, kích chuột phải Select All Sheets.

- B3: Chọn Tab Page Layout\Margins\OK

Như vậy các thiết lập Page Setup của Sheet nguồn sẽ được sao chép cho tất cả các Sheet đã được chọn.

Chú ý: Nếu như bạn chỉnh sửa lại các thông số trong mục Page Setup tại bất kỳ một Sheet, bao gồm cả Sheet nguồn lẫn Sheet được sao chép thì các thông số này sẽ được tự động thay đổi tại tất cả các Sheet.

Tin học văn phòng

Xem tiếp ...

Các thủ thuật khi làm việc với hàm trong Excel

(Các thủ thuật với hàm trong Excel)

- Hàm là những công thức định sẵn của Excel nhằm thực hiện một chức năng tính toán riêng biệt nào đó, hoặc để thực hiện các thao tác trên bảng tính. Trong Excel ngoài việc sử dụng các hàm có sẵn đã được tích hợp thì người sử dụng cũng có thể tự định nghĩa các hàm cho riêng mình. Dưới đây là một vài thủ thuật khi làm việc với hàm, với những thủ thuật này sẽ giúp bạn tiết kiệm được nhiều thời gian khi làm việc trong Excel.

1. Sao chép, di chuyển công thức không thay đổi địa chỉ ô

Khi người sử dụng sao chép công thức sang địa chỉ khác thì toàn bộ các địa chỉ ô tham gia tính toán sẽ tự động được thay đổi. Trong trường hợp bạn muốn sao chép công thức từ ô trên xuống ô dưới mà không muốn thay đổi các địa chỉ ô tham gia tính toán bạn chỉ cần thực hiện theo cách sau. Di chuyển con trỏ đến ô phía dưới cần sao chép, rồi ấn tổ hợp phím Ctrl + ‘ (dấu nháy đơn).

2. Chuyển đổi kết quả của công thức thành giá trị

Khi muốn người khác không thể xem hoặc chỉnh sửa công thức trong một Workbook nào đó người sử dụng thường dùng các phương pháp như: đặt password, ẩn công thức… hôm nay kênh phần mềm việt xin giới thiệu tiếp 1 cách cũng khá hiệu quả đó là chuyển đổi kết quả công thức thành giá trị. Với cách làm này, người khác chỉ có thể xem được các kết quả cuối cùng đã được tính toán mà không thể biết được nó được tính toán như nào và những hàm nào đã tham gia. Dưới đây là các bước thực hiện:

- B1: Chọn những ô có chứa công thức mà bạn muốn chuyển đổi. 

- B2: Kích đúp hoặc nhấn phím F2.

- B3: Nhấn F9. Các công thức sẽ chuyển đổi thành giá trị của chính nó. 

- B4: Nhấn Enter

3. Tính tổng nhanh  với tổ hợp phím Alt + =

Để sử dụng hàm SUM theo cách thông thường, người sử dụng thường phải gõ hoặc dùng chuột để có được địa chỉ ô đầu và địa chỉ ô cuối của cùng cần tính tổng. Tuy nhiên với tổ hợp phím Alt + = , người sử dụng sẽ tiết kiệm được tối đa thao tác cũng như thời gian để thực hiện.  Chỉ cần đặt trỏ chuột tại ô cần tính tổng và ấn tổ hợp phím Alt+= và ấn Enter việc tính tổng đã được thực hiện xong.

4. Hiển thị công thức với các tổ hợp phím CTRL + ` 

Trong quá trình viết giáo trình, đôi khi bạn cần minh họa cách sử dụng một hàm nào đó trong Excel, tuy nhiên khi tính toán và di chuyển con trỏ ra khỏi thì Excel sẽ tự động thay hàm bằng các con số. Vì là con số nên không thể trực quan để người xem có thể hiểu được, vậy có cách nào đó để những ô sử dụng các hàm không chuyển thành con số mà hiển thị hàm đã sử dụng không? Để làm được việc này, bạn chỉ cần sử dụng tổ hợp phím Ctrl + ` (phím này nằm ở bên trái các phím số, khi giữ Shift sẽ  là dấu ngã (~))

5. Lặp lại một công thức cho nhiều ô

Thông thường người sử dụng thường sử dụng con chuột để làm công việc này bằng cách kéo công thức gốc đến các ô tiếp theo cần sao chép đến hoặc kích đúp vào ô chứa công thức gốc. Hôm nay kênh phần mềm việt sẽ giới thiệu với các bạn một cách khác để thực hiện việc này mà không phải dùng con chuột.

- B1: Tính toán cho ô đầu tiên

- B2: Di chuyển con trỏ đến ô đầu tiên đã tính toán, ấn tổ hợp phím Ctrl + C

- B3: Di chuyển con trỏ xuống ô phía dưới, giữ phím Shift và ấn phím di chuyển (lên, xuống, phải, trái)

- B4: Ấn tổ hợp phím Ctrl + V

Tin học văn phòng

Xem tiếp ...

Sử dụng Watch Window để theo dõi, giám sát các ô quan trọng trong Excel

(Cách sử dụng Watch Window)

- Khi phải làm việc với các bảng tính dữ liệu lớn và quan trọng như: danh sách sách hàng, kế toán, hóa đơn… bạn phải liên tục di chuyển giữa các ô để xem thông tin cần thiết. Điều khiến bạn cảm thấy khó chịu vì mất quá nhiều thời gian do những ô này không nằm cạnh nhau và có thể ở cuối Sheet hoặc ở Sheet khác. Vậy có cách nào để có thể di chuyển và đến đúng địa chỉ cần thiết một cách dễ dàng và nhanh chóng giống như chức năng Bookmark trong Word không? Chức năng Watch Window trong Excel sẽ giúp bạn làm công việc này,  Watch Window sẽ giúp bạn: hiển thị giá trị, công thức sử dụng, tên Sheet, địa chỉ ô tại các ô bạn muốn theo dõi trong một cửa sổ riêng biệt, giúp công việc di chuyển và chỉnh sửa thuận tiện và nhanh chóng gấp bội.

Giả sử rằng bạn muốn theo dõi các thông tin trong ô C13 tại Sheet Sanpham, ô B15 để tổng hợp dữ liệu từ C3:C12 và ô D15 tại Sheet2, ô D66 để tính trung bình dữ liệu từ D2:D65.

- B1: Kích Tab Formulars

- B2: Bấm vào biểu tượng Watch Window và Excel sẽ mở ra hộp thoại Watch Window tùy chọn

- B3: Kích nút Add Watch

Trong hộp thoại Add Watch, bạn có thể nhập địa chỉ các ô bằng một trong hai cách. Bạn có thể gõ trực tiếp địa chỉ ô hoặc có thể chọn bằng cách sử dụng con chuộc.  Trong trường hợp bạn muốn theo dõi 1 hoặc 2 ô thì cách gõ trực tiếp sẽ nhanh hơn. Sau khi chọn xong địa chỉ ô cần theo dõi kích nút Add.

Đối với địa chỉ D12 của Sheet2 bạn cũng thực hiện giống như trên. Bây giờ trong cửa sổ Watch Window bạn đã có 2 địa chỉ ô để theo dõi rồi.

Bây giờ khi  cần theo dõi và di chuyển qua lại giữa các vùng hoặc các ô, bạn chỉ cần sử dụng Watch Window, chỉ với một thao tác kích chuột bạn đã có thể nhanh chóng và dễ dàng đến được những nơi cần đến mà không phải mất quá nhiều thời gian để di chuyển nữa rồi.

Nếu làm việc với các bảng tính lớn, Watch Window là một tiện ích giúp người sử dụng  có khả năng tăng năng suất lao động, giảm thời gian làm việc.

Tin học văn phòng

Xem tiếp ...

Lưu và chia sẻ file trực tuyến trong Excel 2013

(Chia sẻ file trực tuyến trong Excel)

- Bắt đầu từ phiên bản Excel 2013, Microsoft đã cho phép người dùng có thể dễ dàng lưu và chia sẻ file trực tuyến bằng các dịch vụ: SkyDrive miễn phí hoặc Office 365. Với việc chia sẻ các Workbook cho người khác, mọi người sẽ cùng làm việc cộng tác với nhau bằng một phiên bản mới nhất của bảng tính cho dù họ sử dụng thiết bị gì hoặc họ đang ở đâu miễn là có Internet và thậm chí bạn còn có thể hợp tác trong thời gian thực. Với tính năng chia sẻ  này mọi người không còn phải gửi Email qua lại các phiên bản khác nhau, ghép nối và tổng hợp lại.

Để lưu và chia sẻ file trực tuyến, bạn sẽ cần một tài khoản Microsoft. Nếu bạn dùng Hotmail, Messenger hoặc Xbox Live, thì bạn đã có tài khoản rồi. Nếu bạn chưa có tài khoản, bạn có thể tạo tài khoản ngay trên Excel. Dưới đây là các bước thực hiện.

- B1: Vào Menu File\Save As
- B2: Kích chọn Add a Place

- B3: Kích chọn SkyDrive

- B4: Nhập địa chỉ Email và kích nút Next

- B5: Nhập thông tin đăng nhập và kích nút Sign in

Nếu bạn dùng Hotmail, Messenger hoặc Xbox Live, thì bạn đã có tài khoản Microsoft rồi. Nếu bạn chưa có tài khoản, hãy bấm Sign up now để tạo tài khoản Microsoft mới.

- B6: Kích nút Browse để chọn đường dẫn lưu File

- B7: Nhập tên và vị trí thư mục

- B8: Kích nút Save để hoàn thành việc lưu file trực tuyến

Chia sẻ File

Khi upload thành công, bạn vào Menu FILE > Share để xem các tùy chọn chia sẻ bảng tính trên SkyDrive. 

Tại đây gồm tùy chọn: 

- Invite People (chia sẻ bảng tính cho người khác qua email và cấp quyền cho người đó chỉ xem hay được phép chỉnh sửa nội dung)

- Get a Sharing Link (lấy đường dẫn truy cập trực tiếp tập tin)

- Post to Social Networks (chia sẻ lên Twitter)

- Email (chia sẻ qua email).

Khi bấm Get a Sharing Link để lấy đường dẫn truy cập bảng tính trên SkyDrive, bạn chú ý bấm Create tại dòng View Link hay Edit Link. Nếu chọn View Link thì khi bấm vào liên kết, người khác chỉ được xem bảng tính. Nếu chọn Edit Link, người xem được phép chỉnh sửa bảng tính.

Tin học văn phòng

Xem tiếp ...

Danh sách các phím tắt hữu ích trong Excel

(Các phím tắt quan trọng trong Excel)

- Khi người sử dụng đang thao tác hoàn toàn trên bàn phím, một thao tác nào đó phải sử dụng đến con chuột điều này sẽ làm gián đoạn công việc, kéo dài thời gian hoàn thành công việc. Chính vì điều này mà các nhà sản xuất phần mềm luôn luôn phải cài đặt các phím tắt để thực hiện một công việc nào đó. Phím tắt có rất nhiều lợi ích như:

- Tiết kiệm thời gian bằng cách cho phép bạn không phải rời tay mình khỏi bàn phím để sử dụng chuột.
- Thành thạo được phím tắt.
- Hoàn thành công việc một cách nhanh chóng.
- Không bị gián đoạn công việc khi không phải chuyển đổi từ bàn phím sang chuột.

Trên Excel có rất nhiều các phím tắt, để có thể nhớ hết tất cả các phím này thì quả là một điều khó khăn đối với người sử dụng. Sau đây Kênh phần mềm việt xin giới thiệu với độc giả một số phím tắt thiết yếu nhất, những phím tắt này nếu được sử dụng hàng ngày sẽ giúp ích nhất nhiều trong công việc hàng ngày. Dưới đây là danh sách các phím tắt đó.

Hi vọng các phím tắt này sẽ giúp ích mọi người trong công việc hàng ngày.

STT	PHÍM TẮT	MÔ TẢ
1	Ctrl + PgDn	Di chuyển giữa các tab bảng tính, từ trái sang phải
2	Ctrl + PgUp	Di chuyển giữa các tab bảng tính, từ phải sang trái
3	Ctrl + Shift + &	Áp dụng đường viền cho các ô, vùng được chọn
4	Ctrl + Shift_	Loại bỏ đường viền cho các ô, vùng được chọn
5	ALT + =	Tự động tính SUM
6	Ctrl + Shift + 4	Định dạng tiền tệ kiểu $
7	Ctrl + Shift + 5	Định dạng tiền tệ kiểu %
8	Ctrl + Shift + 1	Định dạng tiền tệ kiểu thập phân (.)
9	CTRL + `	Hiển thị công thức của ô
10	Ctrl + Shift + 3	Định dạng tiền tệ kiểu ngày tháng năm
11	Ctrl + Shift + 2	Áp dụng các định dạng thời gian với giờ, phút
12	Ctrl + Shift +:	Chèn thời gian hiện tại
13	Ctrl + D	Sao chép giá trị ô trên xuống ô dưới
14	Ctrl + R	Sao chép giá trị ô bên trái sang bên phải
15	Ctrl + ↓	Chuyển đến ô cuối cùng trong cột
16	Ctrl + ↑	Chuyển đến ô đầu tiên trong cột
17	Ctrl + SHIFT + =	Chèn thêm cột hoặc dòng mới
18	Ctrl + -	Xóa cột hoặc dòng
19	ALT + ENTER	Xuống dòng trong một ô
20	Shift + F2	Chèn/sửa nhận xét
21	Shift + F11	Chèn Worksheet mới
22	Alt I R / Alt I C	Chèn hàng/cột mới

Tin học văn phòng

Xem tiếp ...

Hướng dẫn cách tạo và sử dụng Add-in trong Excel 2007, 2010

(Cách tạo Add-ins trong Excel) - Trong Excel ngoài các hàm có sẵn, người sử dụng có thể tự viết các hàm cho riêng mình để thực hiện các công việc được nhanh chóng và hiệu quả. Tuy nhiên khi viết các hàm trong  Workbook thì người sử dụng chỉ có thể sử dụng những hàm tự viết này tại chính nó, không sử dụng được trong các Workbook khác. Để sử dụng các hàm tự viết trong workbook khác, chúng ta phải sao chép các hàm này  sang workbook đó.

Vậy có cách nào để có thể sử dụng được các hàm tự tạo mỗi khi tạo Workbook mới không? Chức năng Add-Ins trong Excel sẽ giúp chúng ta thực hiện công việc này, khi sử dụng Add-Ins người sử dụng có thể sử dụng chúng như các hàm có sẵn trong Excel.

Hôm nay Kênh phần mềm việt sẽ giới thiệu với mọi người cách tạo và sử dụng Add-in và dưới đây là các bước thực hiện.

1. Tạo thư viện Add-in

- B1: Tạo một bảng tính  (Workbook) mới.

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

Vào menu Inser\Module

- B3: Tự xây dựng hàm mới hoặc copy các hàm đã lập sẵn vào cửa sổ soạn code.

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

- B5: Kích nút Office Button\Save As

- File name: Nhập tên file

- Save As type: Chọn Excel Add-In (*.xlam) hoặc Excel97-2003 Add-in (*.xla)

- Bấm Save để lưu lại thư viện dưới dạng tập tin Add-Ins

- B6: Thoát khỏi Excel

2. Tích hợp thư viện Add-in vào Excel

Sau khi đã tạo được thư viện Add-in rồi, nhiệm vụ tiếp theo là phải tích hợp thư viện này vào Excel.

- B1: Khởi động Excel

- B2: Kích nút Office Button\Excel Options\Add-ins

- B3: Kích nút Go

- B4: Kích nút Browse

- B5: Lựa chọn file .xlam hoặc .xla đã tạo trước

- B6: Kích nút OK

Sau khi tích hợp thư viện Add-in vào Excel, chúng ta có thể dùng các hàm mới này như những hàm đã được định nghĩa sẵn trong Excel. Khi cần chỉnh sửa hoặc bổ xung các hàm mới vào thư viện, chúng ta mở file .xlam hoặc .xla rồi soạn thảo bình thường, sau khi hoàn thành kích Save để lưu lại thay đổi.

Tin học văn phòng

Xem tiếp ...

Tổng hợp các cách để xóa dòng trống trong Excel 2007, 2010, 2013

(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

Xem tiếp ...