Cập nhật thông tin chi tiết về √ Khóa Và Mở Khóa (Protect/Unprotect) Vùng Dữ Liệu Trong Bảng Tính Excel mới nhất trên website Bothankankanhatban.com. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất.
Protect sheet bảo vệ bảng tính, chống sửa đổi các ô vùng chọn bằng thiết lập mật khẩu. Tuy nhiên, nếu không may bạn quên mật khẩu, hay bạn cần Unprotect sheet bảng tính khác mà không có mật khẩu thì làm thế nào?
Bài viết sau sẽ trình bày cách khóa một bảng tính, khóa một vùng chọn của bảng tính và cách ‘phá pass’ Unprotect sheet mà không cần phải dùng phần mềm can thiệp.
Cách bảo vệ bảng tính bằng Protect sheet
Nếu khóa toàn bộ bảng tính trừ vùng chọn: đầu tiên bạn cần chọn (bôi đen) vùng chọn không bị khóa (người dùng khác có thể chỉnh sửa dữ liệu), mở tab Protection trong Format Cells (nhấp phải chuột/Format Cells/Protection):
Nhấp bỏ chọn dấu stick trong mục Locked, nhấp OK.
Nếu chỉ khóa vùng chọn: Bạn chọn toàn bộ bảng tính (nhấn tổ hợp phím Ctr + A) rồi thao tác các bước như trên.
Kế tiếp, bạn chọn vùng dữ liệu cần khóa, cũng thao thao tác tương tự như trên nhưng chọn lại dấu kiểm Locked trong tab Protection của Format Cells trước khi nhấn OK.
Nếu khóa toàn bộ bảng tính: bạn không cần phải thực hiện các bước trên vì Excel mặc định là Locked.
Tiếp theo, bạn nhấp Tab Review, chọn Protect sheet:
Menu Protect sheet xuất hiện như sau:
Trong phần Allow all uers of this worksheet to bạn chọn các tính năng cần khóa, xong nhập mật khẩu vào ô Password to unprotect sheet. Nhấp OK và lập lại mật khẩu lần nữa khi được nhắc.
Lúc này phần chọn của sheet của bạn đã bị khóa, khi nhấp chuột (hoặc nhấn F2) để sửa đổi sẽ không thực hiện được và excel hiện ra một thông báo “xám xịt” về tình trạng của sheet (sheet bị khóa, cần pass để mở).
Mở khóa bảng tính (Unprotect sheet)
Nếu bạn có mật khẩu để mở khóa vùng chọn bị khóa (Unprotect sheet) thì mọi việc quá dễ dàng, chỉ việc thao tác: nhấp Tab Review, chọn Unprotect sheet nhập mật khẩu vào hộp Unprotect sheet khi nó xuất hiện và nhấn OK là xong.
Tuy nhiên, nếu chẳng may bạn quên mật khẩu hoặc cần cập nhật thông tin trên sheet bị khóa của một file excel nào đó mà không pass thì phải xử lý thế nào?
May mắn là chúng ta vẫn có thể xử lý được tình huống này mà không cần phải dùng phần mềm của bên thứ 3 can thiệp, đó là sử dụng Visual Basic Editor.
Các bước thao tác như sau:
(Trong hình trên là flie phantichcongno_mau, sheet2 (tonghop))
Tiếp theo, bạn copy đọan code sau dán vào phần General để phá pass cho sheet cần mở khóa:
Sub PasswordBreaker()
If ActiveSheet.ProtectContents = False Then
MsgBox “Sheet ‘” & chúng tôi & “‘ is unprotected!”, vbInformation
Else
If MsgBox(“Sheet ‘” & chúng tôi & “‘ 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 ‘” & chúng tôi & “‘ is unprotected!”, vbInformation
End If
End Sub
Truy cập vào menu Run → Run Sub/UserForm (hoặc nhấn F5) để chạy lệnh trên, nhấn Yes:
Nếu thành công, bạn cần xác nhận lại lệnh trên, nhấn OK để hoàn tất.
Như vậy, pass Protect sheet đã được gở bỏ, bạn hoàn toàn thao tác trên bảng tính này một cách bình thường.
Cách Cố Định Vùng Dữ Liệu Trong Excel
Khi sử dụng các hàm trong Excel bạn sẽ rất thường xuyên phải tham chiếu đến 1 vùng dữ liệu nào đó như A1:D5 chẳng hạn. Mặc định khi bạn tham chiếu đến vùng chọn như vậy thì nó sẽ ở dạng tham chiếu tương đối. Vậy để cố định vùng dữ liệu trong Excel hay còn gọi là tham chiếu tuyệt đối đến vùng dữ liệu nào đó thì phải làm như nào?
Trong bài viết này, mình sẽ chỉ cho các bạn hiểu tham chiếu tương đối, tuyệt đối là như nào và giải thích cho bạn hiểu ý nghĩa của ký tự $ trong Excel từ đó bạn sẽ biết cách khi nào cần cố định hàng, cột hay cố định tất cả vùng dữ liệu.
Ký hiệu $ trong excel có nghĩa là gì
Ký hiệu $ là 1 trong những ký tự thường dùng khi sử dụng các công thức Excel. Tác dụng của nó là để cố định hàng, cột hay 1 vùng dữ liệu trong Excel.
Trước khi tìm hiểu về các tham chiếu chúng ta cần hiểu rõ cách định nghĩa 1 ô, 1 vùng dữ liệu trong Excel như sau:
Định nghĩa 1 ô trong Excel
Ô trong Excel sẽ được thể hiện ở dạng: Tên cột + Số hàng
Ví dụ
ô A5 – là ô nằm ở cột A và hàng thứ 5
ô H10 – là ô nằm ở cột H và hàng thứ 10
Định nghĩa 1 vùng dữ liệu trong Excel
Vùng dữ liệu trong Excel sẽ có dạng: Ô trên cùng bên trái : Ô dưới cùng bên phải
Ví dụ
Vùng dữ liệu A5:H10 – là vùng chọn được xác định bởi ô trên cùng bên trái là A5 và ô dưới cùng bên phải là H10
Tham chiếu tương đối là tham chiếu mặc định khi chọn vùng dữ liệu trong Excel. Với tham chiếu tương đối thì giá trị tham chiếu sẽ thay đổi theo hàng hay cột, tức là khi bạn thay đổi vị trí theo hàng hay cột thì giá trị tham chiếu cũng sẽ thay đổi theo.
Ví dụ
– Tại ô D4 nhập vào công thức =C4 rồi ấn Enter
– Lúc này giá trị hiển thị trong ô D4 sẽ là giá trị của ô tham chiếu C4
– Khi bạn kéo chuột để copy công thức từ ô D4 xuống các ô ở hàng dưới hoặc cột bên thì tham chiếu đến các ô sẽ thay đổi
Tham chiếu tuyệt đối
Tham chiếu tuyệt đối là tham chiếu sẽ cố định giá trị tham chiếu theo hàng, cột hoặc cả hàng và cột. Có nghĩa là khi bạn đã cố định giá trị tham chiếu theo hàng, cột hoặc cả hàng và cột thì khi thay đổi vị trí nó vẫn giữ nguyên giá trị tham chiếu đó.
Có 3 kiểu tham chiếu tuyệt đối như sau:
Tham chiếu tuyệt đối theo hàng (cố định hàng)
Tham chiếu tuyệt đối theo hàng là nó sẽ cố định giá trị hàng không thay đổi. Khi đặt tham chiếu tuyệt đối theo hàng thì dấu $ sẽ đứng trước Số hàng như sau: =C$4
Khi đó, nếu ta copy công thức sang 1 ô khác thì giá trị hàng không thay đổi tức là nó đã bị cố định theo hàng đã chọn ban đầu.
Ví dụ
– Tại ô D4 ta nhập vào công thức =C$4 rồi ấn Enter
– Lúc này giá trị hiển thị trong ô D4 sẽ là giá trị của ô tham chiếu C4
– Khi đó, ta copy công thức từ ô D4 sang các ô khác mặc dù khác hàng nhưng giá trị hàng vẫn cố định không thay đổi.
Tham chiếu tuyệt đối theo cột (cố định cột)
Tham chiếu tuyệt đối theo cột là nó sẽ cố định giá trị cột. Khi đặt tham chiếu tuyệt đối theo cột thì dấu $ sẽ đứng trước Tên cột như sau: =$C4
Khi đó, nếu ta copy công thức sang 1 ô khác thì giá trị cột vẫn không thay đổi tức là nó đã bị cố định theo cột đã chọn ban đầu.
Ví dụ
– Tại ô D4 ta nhập vào công thức =$C4 rồi ấn Enter
– Lúc này giá trị hiển thị trong ô D4 sẽ là giá trị của ô tham chiếu C4
– Khi đó, ta copy công thức từ ô D4 sang các ô khác mặc dù khác cột nhưng giá trị cột khi đó vẫn cố định không thay đổi.
Tham chiếu tuyệt đối theo hàng và cột (cố định cả hàng và cột)
Tham chiếu tuyệt đối theo hàng và cột là tham chiếu sẽ cố định giá trị hàng và cột không thay đổi. Khi đặt tham chiếu tuyệt đối theo hàng và cột thì dấu $ sẽ đứng trước Tên cột và Số hàng như sau: =$C$4
Khi đó, nếu ta copy công thức sang 1 ô khác thì cho dù nó có Số hàng và Tên cột khác nó vẫn sẽ cố định hàng cột không thay đổi.
Ví dụ
– Tại ô D4 ta nhập vào công thức =$C$4 rồi ấn Enter
– Lúc này giá trị hiển thị trong ô D4 sẽ là giá trị của ô tham chiếu C4
– Khi đó, ta copy công thức từ ô D4 sang bất kỳ 1 ô nào khác thì giá trị hàng và cột khi đó vẫn cố định không thay đổi.
Cách cố định vùng dữ liệu trong Excel
Dựa vào các tham chiếu ở trên thì bạn có thể áp dụng tương tự khi muốn cố định vùng dữ liệu nào đó trong Excel như sau
Phím tắt chuyển đổi nhanh các dạng tham chiếu trong Excel
Ta có công thức như sau: =SUM(C4:D8)
Lưu ý: Để cố định ô nào thì bạn đặt con trỏ chuột vào vị trí ô đó rồi ấn F4.
Cách Sử Dụng Advanced Filter Trong Excel, Lọc Dữ Liệu Bảng Tính Theo Đ
Mặc dù là Advanced Filter nhưng cách sử dụng của nó lại hoàn toàn cơ bản, dễ dàng sử dụng ngay cả với người mới. Để sử dụng Advanced Filter
Khi thao tác trên bảng tính Excel, thao tác lọc dữ liệu theo điều kiện rất quan trọng và không thể thiếu. Ở bài viết trước chúng ta đã được tìm hiểu cách Lọc dữ liệu trùng nhau trong bảng tính thì ở bài viết này các bạn sẽ có thêm kinh nghiệm sử dụng chức năng Advanced Filter trong Excel.
Cách sử dụng chức năng Advanced Filter trong Excel
Trước tiên để dùng chức năng Advance Filter thì bảng dữ liệu của bạn phải theo các yêu cầu sau:
1. Chừa ít nhất 3 dòng trống trên cùng bảng dữ liệu.2. Chỉ sử dụng một dòng duy nhất để làm tiêu đề bảng dữ liệu.3. Không merge bất cứ ô nào của bảng dữ liệu.
Khi sử dụng Advance Filter thì bạn sẽ phải dùng tới một bảng phụ làm điều kiện so sánh để lọc dữ liệu từ bảng dữ liệu chính của các bạn. Và yêu cầu của bảng phụ này phải dùng tiêu đề của cột dữ liệu bạn cần lọc từ bảng chính làm tiêu chí lọc, cho nên bạn phải copy tiêu đề cột dữ liệu bạn cần lọc trong bảng chính và dán vào dòng tiêu đề điều kiện cần lọc trong bảng phụ. Và Advance Filter chỉ có thể lọc 1 lần 2 điều kiện cho 1 cột dữ liệu. Các bạn có thể theo dõi ví dụ dưới để nắm bắt trực quan hơn về chức năng Advance Filter.
Ví dụ: Ta có bảng dữ liệu như sau:
Đầu tiên bạn cần phải tạo một bảng phụ dùng để lọc dữ liệu.
Bước 2: Dán vào một ô trống bất kỳ. Và đặt điều để lọc cho nó. Ở ví dụ này mình đặt điều kiện là lọc các mặt hàng có giá lớn hơn 50$.
Như vậy là bạn đã có được một bảng phụ để lọc dữ liệu từ bảng chính.
Bước 1: Đặt trỏ chuột vào ô bảng dữ liệu chính. Vào Data → Advance
Bước 2: Trong ô List Range là vùng dữ liệu chính bạn muốn lọc. Khi bạn đặt trỏ chuột và chọn chức năng Advance Filter thì Excel sẽ tự động chọn bảng dữ liệu cho bạn (vùng được bao quanh bởi các đường nhấp nháy).
Bước 3: Trong ô Criteria range, bạn chọn bảng phụ vừa tạo dùng để lọc dữ liệu.
Bước 4: Bạn để ý trong bảng Advance Filter sẽ có 2 mục chọn là:
– Filter the list, in-place: lọc dữ liệu và cho ra kết quả ở chính bảng lọc. Các dữ liệu không phù hợp sẽ bị mất đi.– Copy to another location: lọc dữ liệu và copy chúng trong một khu vực khác.
Kết quả sau khi lọc dữ liệu:
Bài viết trên giới thiệu với các bạn về cách sử dụng Advanced Filter cũng như chức năng chính của nó. Tính năng này giúp bạn lọc dữ liệu giống chức năng Auto Filter nhưng không bị hạn chế về các điều kiện lọc như Auto Filter.
Chú ý Ngoài ra các bạn cũng cần biết thêm về các ký tự đặc biệt khi lọc: 1. Ký tự *: ví dụ ở cột Name. Bạn muốn lọc tất cả các ký tự có bắt đầu bằng chữ M thì bạn sẽ nhập điều kiện lọc là M*. Tức là lọc chuỗi ký tự bắt đầu bằng chữ M, ký tự * đại diện cho các ký tự còn lại trong chuỗi. 2. Ký tự ?: tương tự như ký tự * nhưng nó chỉ đại diện cho một ký tự duy nhất. Ví dụ bạn lọc M? tức là lọc chuỗi gồm 2 ký tự bắt đầu bằng M. 3. Ký tự ~: để loại bỏ 2 ký tự đặc biệt trên khi lọc dữ liệu. Ví dụ trong bảng dữ liệu của bạn có chuỗi là M*D, khi bạn lọc nếu nhập điều kiện như vậy thì nó sẽ lọc tất cả các chuỗi có bắt đầu bằng M và kết thúc bằng D.
Cách Lọc Dữ Liệu Trong Excel
Cách lọc dữ liệu trong excel hay cách sử dụng autofiler – đây là tính năng tuyệt vời của excel. Sử dụng Autofiler trong excel 2007 2010 tuyệt vời hơn excel 2003 tương đối nhiều.
Một số điểm nổi bật của cách lọc dữ liệu trong excel 2007 2010 (autofilter)
Giao diện đẹp hơn
Tính năng lọc được nâng cấp
Hiển thị nhiều tính năng hơn excel 2003
Tên tiếng anh của chức năng lọc dữ liệu trong excel là: Autofilter
Trong autofilter có 2 phần chính:
Autofilter mặc định của excel
Advanced filter: chức năng lọc nâng cao của excel sẽ giúp các bạn lọc được theo nhiều điều kiện khác nhau, tùy ý của bạn. Thậm chí có thể di chuyển kết quả lọc được sang một vùng hoàn toàn mới.
1. Chức năng autofilter mặc định của excel (lọc dữ liệu mặc định trong excel)
1.1. Ứng dụng của chức năng lọc dữ liệu trong excel.
Theo yêu cầu quản trị của quản lý cửa hàng, giám đốc công ty, khi các bạn làm kế toán bán hàng, nhân viên bán hàng hoặc nhân viên thống kê: Các bạn sẽ phải tổng hợp tình hình bán hàng của cửa hàng theo ngày, theo tuần theo tháng hoặc theo năm. Các tiêu chí dùng để lập báo cáo bán hàng là khác nhau.
Và đặc biệt, vào một thời điểm bất kỳ nào đó trong ngày, cửa hàng trưởng yêu cầu bạn:
“Em thống kê cho anh xem, tổng doanh số của quầy Rau quả tính đến thời điểm này là bao nhiêu?”
Để làm được điều đó, bạn hãy sử dụng tính năng lọc dữ liệu trong excel (autofilter). Đây chính là cứu cánh là giải pháp cho bạn. Bạn sẽ không cần phải lo lắng gì về việc làm thế nào để lọc ra dữ liệu nữa.
– Nhìn nhanh tổng doanh số của Ngành hàng D
– Liệt kê top 10 quầy hàng có doanh số bán hàng tốt nhất
– Liệt kê những quầy hàng có doanh số trên 400.000 KVND
1.2. Bật hoặc tắt tính năng lọc dữ liệu trong excel 2007 2010
Trước khi thành thạo cách lọc dữ liệu trong excel, bạn cần phải biết làm thế nào để kích hoạt chức năng autofilter trong excel 2007 2010.
Việc xuất hiện một hình vuông nền trắng bên trong đó là một hình tam giác màu đen xuất hiện ở góc phía dưới bên phải của mỗi ô được chọn.
– Để tắt tính năng lọc dữ liệu (autofilter) trong excel đi, ta chỉ cần vào Data và chọn filter là được.
Yêu cầu 1: Cách lọc dữ liệu trong excel 2007 2010 để tìm tổng doanh số của ngành hàng D
Bước 1:
Nhấn vào hình vuông bên cạnh chữ “Ngành hàng”
Bỏ tick “select all”
Tiếp tục chọn “D”
Nhấn “OK”
Bạn tiếp tục dùng chuột bôi đen doanh số của tất cả các quầy thuộc Ngành hàng D. Khi đó, tổng doanh số của ngành hàng D sẽ hiện lên trên thanh status bar như hình sau:
Yêu cầu 2: Cách lọc dữ liệu trong excel để tìm ra top 10 quầy hàng có doanh số tốt nhất.
Với yêu cầu liệt kê TOP 10 quầy hàng có doanh số tốt nhất, thì dường như còn dễ hơn cả yêu cầu số 1.
B2: Chọn “Number filter”
Trong yêu cầu, sếp bạn muốn liệt kê 10 quầy có doanh số tốt nhất (top 10). Do đó, bạn không cần phải chỉnh sửa gì trong bảng này mà nhấn “OK” luôn.
Đây có lẽ là tùy chọn lọc dữ liệu phổ biến nhất trong excel. Ta thực hiện như sau:
Bước 1: Chọn hình vuông bên cạnh “Doanh số (KVND) “
B2: Chọn Number filters
B3: Chọn “Greater than”
Và nhấn “OK”
Bạn đang xem bài viết √ Khóa Và Mở Khóa (Protect/Unprotect) Vùng Dữ Liệu Trong Bảng Tính Excel trên website Bothankankanhatban.com. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!