Khóa và mở khóa (Protect/Unprotect) vùng dữ liệu trong bảng tính excel

Ketoan.biz 18.7.19 2 Comments

Ketoan.biz

Tính năng Protect sheet trong bảng tính excel cho phép bạn bảo vệ, khóa, không cho người khác sửa đổi các ô chứa công thức hay vùng dữ liệu nhạy cảm khá dễ dàng.

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:

Đầu tiên, bạn cần mở file excel bị khóa lên, vào Visual Basic Editor bằng cách nhấn phím tắt ALT + F11. Bạn click chọn vào sheet cần mở khóa:

(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 '" & 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

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.

Chúc các bạn thành công.

Ketoan.biz

Canh đều, ngắt dòng trong Excel như thế nào?

Ketoan.biz 5.12.18 Add Comment

Ketoan.biz

Như chúng ta biết, mặc định Excel sẽ tràn sang ô khác hoặc bị ẩn khi chúng ta gõ text tương đối dài, khá khó chịu, không đẹp mắt cho lắm.

Cách tính lãi kép bằng Excel

Ketoan.biz 8.11.18 Add Comment

Ketoan.biz

Tính lãi suất một khoản đầu tư, hay một khoản vay nào đó là công việc khá quen thuộc đối với dân tài chính, kế toán.

Chèn nhanh bảng tính excel vào file word

Ketoan.biz 4.6.18 Add Comment

Ketoan.biz

Word và Excel là "anh em song sinh" tích hợp trong bộ Microsoft Office nên chúng có sự tương tác, hỗ trợ qua lại lẫn nhau khá tốt.

Chúng ta biết rằng bản thân word chuyên về soạn thảo văn bản, nên việc thực hiện các phép tính đơn thuần trên word khá khó khăn. Trong khi đó, excel lại mạnh về tính toán, có thể thực hiện nhiều phép tính từ đơn giản đến phức tạp thông qua các hàm, công thức.

>> Cập nhật tỷ giá tự động vào bảng tính excel

Liệu có cách nào khi chúng ta đang soạn thảo văn bản trên word, cần nhu cầu tính toán thì có thực hiện được các phép tính như trên bảng tính excel- vốn rất quen thuộc hay không? Câu trả lời là hoàn toàn có thể khi bạn thực hiện chèn bảng tính excel vào word với một vài thao tác khá đơn giản.

Bài viết sau sẽ được trình bày trên Office 2016 theo 2 cách: Chèn cả bẳng tính excel vào word bằng Object và chèn một phần bảng bảng tính excel vào word bằng Paste Special.

1. Chèn file excel vào word bằng Object

Đầu tiên bạn mở file word cần chèn bảng tính excel lên. Tại menu Insert, đặt trỏ chuột tại vị trí muốn chèn bảng tính, click chọn mũi tên trỏ xuống của tab Object, chọn Object ...

Hộp thoại Object xuất hiện:

Tại đây, chúng ta có hai lựa chọn: Create New nếu muốn chèn bảng tính mới (trắng), Create from File nếu muốn chèn bảng tính từ một file có sẵn.

Trong hình trên chúng ta đang chọn  Create New, và chọn Microsoft Excel 97-2003 Worksheet. Xong, nhấn OK để chèn bảng tính vào word.

Nếu bạn cần chèn file excel có sẵn vào word, hãy chọn Create from File, như hình sau:

Tại ô File name gõ đường dẫn chứa file hoặc click chọn Browse để tìm nơi chứa file excel cần chèn vào word, nhấn Insert hoặc Open. Xong nhấn OK.

Ở chế độ soạn thảo văn bản word (như trên), bạn có thể điều chỉnh khối bảng tính khi rê chuột vào các 6 nút chấm, kéo thả để tăng/ giảm kích thước khối.

Muốn bảng tính chuyển sang chế độ excel, bạn nhấp đúp chuột trái lên bảng tính. Lúc này, bạn có thể thao tác bảng tính như trên excel, như hình sau:

Muốn trở về chế độ soạn thảo văn bản của word, bạn chỉ cần nhấp chuột ngoài vùng bảng tính excel là được.

2. Chèn một phần bảng tính excel vào word bằng Paste Special

Khác trường hợp trên là chèn cả (file) bảng tính vào văn bản word, trường hợp sau chúng ta chỉ chèn một vùng (lựa chọn) của bảng tính excel đưa vào word.

Trường hợp này, bạn copy một vùng bảng tính excel để chèn vào văn bản word nhưng vẫn thực hiện được các tính toán như trên excel.

Tại bảng tính excel, chọn vùng dữ liệu cần copy, nhấn Ctr + C (như hình sau):

Trở về phần soạn thảo văn bản word, tại Menu Home → Chọn Paste → Paste Special, như sau:

Xuất hiện hộp thoại Paste Special:

Chọn Microsoft Excel 2003 Worksheet Object (như hình trên) trong vùng As, xong nhấn OK. Ngay sau đó, vùng bảng tính sẽ được đưa vào văn bản word:

Nếu thao tác đúng, bạn có thể thực hiện các phép tính như trên excel. Việc chuyển sang chế độ excel, bạn chỉ việc nhấp đúp chuột lên vùng bảng tính như cách Chèn excel vào word bằng Object trình bày ở trên.

Việc chèn bảng tính excel vào word khá thuận tiện cho các bạn khi lập các báo cáo, giải trình trên word mà có "đụng" đến excel. Ứng dụng này được kế toán áp dụng khi lập Bản thuyết minh báo cáo tài chính trên file word khá hiệu quả.

Chúc các bạn thành công.

Ketoan.biz

Cách thêm hộp check box vào excel, word

Ketoan.biz 8.3.18 Add Comment

Ketoan.biz

Thường khi thêm check box, chúng ta hay vào Tab Insert → Symboy để chèn hộp kiểm cố định vào văn bản. Tuy nhiên cách này chỉ phù hợp cho dạng văn bản in ra giấy, việc cần khảo sát online sẽ gây khá bất tiện do người dùng không thể tự tích chọn được.

Giải pháp tốt nhất có lẻ là dùng hộp check box tích hợp trong Developer, cả excel và word đều có thể áp dụng được. Hộp kiểm dạng này cho phép người dùng có thể tích chọn / bỏ chọn dễ dàng, thích hợp cho các cuộc khảo sát, thăm dò ý kiến online, tăng tính hiệu quả, chuyên nghiệp.

Bài viết sau sẽ trình bày việc chèn hộp check box vào bảng tính, văn bản với Office 2016.

Cập nhật tỷ giá tự động vào bảng tính excel

Ketoan.biz 19.1.18 Add Comment

Ketoan.biz

Việc trích xuất dữ liệu trực tuyến (XML) về bảng tính excel trong các phiên bản excel gần đây thực hiện khá dễ dàng, phục vụ đắc lực cho công việc của cho dân văn phòng, kế toán.

Thủ thuật tách chuỗi thành số trong excel

Ketoan.biz 17.7.17 1 Comment

Ketoan.biz

Số dạng chuỗi (text) trong excel không thực hiện được các phép tính, cần chuyển chúng thành dạng số (number) để tính toán.

Remove Duplicates: lượt bỏ nhanh giá trị trùng lặp trong excel

Ketoan.biz 26.6.17 Add Comment

Ketoan.biz

Remove Duplicates là một công cụ khá mạnh, lượt bỏ các giá trị trùng lặp để lọc ra một báo cáo hoàn chỉnh, nhanh chóng.

Một số công thức tính tổng "độc", "lạ" trong excel

Ketoan.biz 19.6.17 Add Comment

Ketoan.biz

>> Ứng dụng Excel trong kế toán với các hàm tính tổng

Excel là một công cụ tính toán quen thuộc nhưng khá thú vị cho dân văn phòng, kế toán.

Sử dụng Consolidate để tính toán nhanh trong excel

Ketoan.biz 24.5.17 1 Comment

Ketoan.biz

>> Thủ thuật tách gộp họ tên trong Excel không cần phần mềm

>> Tách dữ liệu nhanh chóng trong Excel với tính năng Text to Columns

Consolidate là một công cụ hỗ trợ tính toán khá mạnh tích hợp trong excel cho phép thực hiện nhanh các phép tính tổng (sum), trung bình (evergage), max, min ... trong bảng tính.

9 thủ thuật excel hay dành cho dân văn phòng

Ketoan.biz 16.5.17 Add Comment

Ketoan.biz

>> 9 phím tắt vàng trong excel mà dân văn phòng phải biết

Sử dụng thủ thuật excel sẽ giúp cho người dùng tiết kiệm được thời gian và tăng hiệu quả công việc.

Hướng dẫn soạn thảo Hợp đồng lao động tự động với Mail Merge

Ketoan.biz 2.4.17 12 Comments

Ketoan.biz

>> Mẫu hợp đồng lao động mới nhất

Mail Merge (trộn thư) là một ứng dụng rất hay được tích hợp trong Microsort Word.

9 phím tắt vàng trong excel mà dân văn phòng phải biết

Ketoan.biz 10.1.17 Add Comment

Ketoan.biz

>> Một số hàm cơ bản thường dùng trong kế toán

>> Ứng dụng Excel trong kế toán với các hàm tính tổng

Làm việc trên Excel nếu sử dụng các phím tắt sẽ giúp bạn thao tác chính xác và nhanh chóng hơn.

Data Validation: Thuộc tính hay thiết lập cố định giá trị trong ô excel

Ketoan.biz 29.9.16 Add Comment

Ketoan.biz

Đôi khi làm việc một bảng tính excel, các mẫu biểu với những trường định sẳn, việc cố định những trường này với một giá trị được chỉ định trước là cần thiết, nhằm tránh một số sai sót khi nhập liệu.

Data Validation- Một tính năng hay của excel được tích hợp trong menu DATA, giúp chúng ta cố định nhanh giá trị của một cell (hoặc một số cell được lựa chọn) với một giá trị được chỉ định trước, tức nhập liệu vào ô phải thỏa mãn điều kiện được định trước nào đó.

Trong ví dụ minh họa dưới đây, chúng ta thiết lập số tiền "Văn phòng phẩm" tại ô C3 với thiết lập là không quá 2 triệu đồng khi nhập liệu vào ô này. Để làm được điều này, chúng ta tiến hành một số thao tác như sau:

Đặt trỏ chuột tại ô C3 (có thể chọn nhiều ô), nhấp chuột vào menu DATA và nhấp tiếp tab Data Validation → chọn Data Validation:

Hộp thoại Data Validation xuất hiện như hình bên dưới:

Nhấp chọn tab Settings, trong hộp Allow là các tùy chọn điều kiện để chúng ta lựa chọn. Do điều kiện của ô C3 là số tiền không quá 2 triệu đồng, nên chúng ta chọn Whole nembers.

Mục Data cho ta nhiều lựa chọn, ở đây chúng ta chọn between, mục Minimun nhập vào số 0, mục Maximun nhập vào 2000000, tức điều kiện hợp lệ trong ô C3 mà chúng ta cần xác lập là số từ 0 đến 2.000.000.

(Lưu ý, mục Minimun, Maximun có thể tham chiếu đến dữ liệu của sheet khác vẫn được)

Tiếp theo, tại Tab Input Message chúng ta nhập thông báo nhắc nhở người nhập liệu nhập đúng điều kiện đã thiết lập. Mục Error Alert nhập vào lời cảnh báo khi người nhập liệu nhập sai điều kiện. Các bạn xem hình bên dưới:

Lưu ý: mục Style chúng ta chọn stop (như hình trên) thì bắt buộc phải nhập đúng điều kiện, chọn 2 thuộc tính còn lại thì Excel chỉ xuất hiện cảnh báo khi nhập sai điều kiện.

Đến đây, việc thiết lập điều kiện nhập liệu cho ô C3 đã hoàn tất, các bạn nhấp chọn nút OK để xem kết quả:

Nếu các bạn nhập liệu vào ô C3 đúng điều kiện (từ dưới 2 triệu) thì mọi việc có vẻ "êm xuôi", nếu không (>2 triệu) thì bạn sẽ nhận ngay một cảnh báo như trên. Lúc này, chỉ có nước nhấp chọn Retry để sửa lại số cho đúng mà thôi!

Hy vọng với tính năng Data Validation đã trình bày ở trên sẽ hữu ích cho các bạn khi vận dụng vào công việc.

>>Xem thêm: Tách dữ liệu nhanh chóng trong Excel với tính năng Text to Columns

Ketoan.biz

Tách dữ liệu nhanh chóng trong Excel với tính năng Text to Columns

Ketoan.biz 15.8.16 Add Comment

Ketoan.biz

Trong công việc của dân văn phòng, kế toán thường phải làm việc trên các files excel được trích xuất ra từ phần mềm quản lý như phần mềm quản lý nhân sự, phần mềm kế toán ... Tuy nhiên các files này dữ liệu lại "dính" chung với nhau được ngăn cách bởi các dấu "/". "*", "-", ":" ... việc cần làm là phải tách riêng dữ liệu trước khi xử lý, lập các báo cáo phân tích số liệu.

Có nhiều cách tách dữ liệu ra trước và sau các dấu "/". "*", "-", ":" ... như sử dụng công thức kết hợp các hàm tích hợp trong excel để tách chúng. Việc sử dụng hàm nâng cao trong excel để xử lý dữ liệu không phải ai cũng làm được bởi tính phức tạp của nó. May mắn thay, excel đã tích hợp sẳn tính năng tách dữ liệu rất nhanh và cực kỳ đơn giản, đó là tính năng Text to Columns.

Tính năng Text to Columns là một ứng dụng hay của Excel ẩn trong tab Data chuyên tách các dữ liệu thành cột riêng được chỉ định cụ thể.

Trong ví dụ minh họa bên dưới, chúng ta cần tách số liệu (ngăn cách bởi dẫu "-") sang cột riêng, ngày tháng sang cột riêng.

Đầu tiên, bạn chọn vùng dữ liệu cần xử lý, đồng thời click chuột chọn Tab Data → Text to Columns, xuất hiệu hộp thoại sau:

Tiếp theo, các bạn chọn Dellimited rồi click Next để chuyển sang bước kế tiếp:

Ở hình trên, các bạn nhấp chọn dấu kiểm mục Other và nhập vào ô vuông dấu "-", xong nhấp Next → Finish để xem kết quả:

Nếu thao tác dúng, chúng ta sẽ được kết quả như hình bên dưới: Tách dữ liệu số và ngày tháng ra 2 cột E và F.

Lưu ý:

Việc tách dữ liệu trước và sau dấu "," bạn nhấp chọn hộp Comma, tách dữ liệu giữa các khoản trắng bạn chọn hộp Space như hình trên.

Ngoài ra, tách dữ liệu trước và sau dấu bất kỳ ("/". "*", "-", ":" ...), bạn nhấp chọn mục Other rồi nhập vào ô vuông dấu mà mình muốn tách chúng.

Hy vọng với thủ thuật đơn giản trên sẽ hữu ích cho các bạn trong công việc khi phải xử lý một file excel với các tình huống, trường hợp tương tự.

Ketoan.biz

Thủ thuật tách gộp họ tên trong Excel không cần phần mềm

Unknown 27.6.16 2 Comments

Unknown

Trong công việc, đôi khi chúng ta cần phải tách họ và tên riêng trong một danh sách Excel nào đó, từ file Word chuyển qua Excel chẳng hạn.

Trong trường hợp sau, chúng ta cần tách tên riêng một cột và họ (tên lót) riêng một cột trong một bảng tính excel. Công việc sẽ trở nên đơn giản khi chúng ta thực hiện một số thủ thuật sau đây:

1. Đầu tiên, cần copy danh sách họ tên tại cột B sang cột khác, cột E. Chọn vùng danh sách cột E (bôi đen vùng chọn cột E) như hình bên dưới;

2. Nhấn tổ hợp phím Ctrl + F để mở để mở hộp tìm kiếm và thay thuế (Find and Replace), chọn tab Replace để tách tên.

Trong hộp Find what, đánh dấu * và một khoản trắng (nhấn phím * và phím spacebar); Hộp Relace with thì để trống (không làm gì cả);

Mục đích là excel sẽ xóa từ ký tự trắng đầu tiên bên trái cell bảng tính, tức chỉ giữ lại tên.

Chọn Replace All, bạn sẽ thấy hiệu quả ngay lập tức.

Nếu thao tác đúng, bạn sẽ được kết quả như hình sau: Tách được tên riêng trong danh sách tại cột E.

3. Tách họ và tên lót vào cột D:

Công việc còn lại cũng khá đơn giản, tại ô D3 nhập vào công thức: =LEFT(B3,LEN(B3)-LEN(E3))

Copy xuống dưới với nhứng ô còn lại, kết quả chúng ta đã tách được danh sách với tên và họ thành hai cột riêng.

4. Gộp tên họ từ nhiều cột thành một cột

Tức là yêu cầu công việc, quy trình làm ngược lại như đã trình bày ở trên. Chẳng hạn, chúng ta có một danh sách với tên họ nằm ở hai cột D và E và giờ muốn gộp chúng lại thì phải làm thế nào?

Công việc trở nên khá đơn giản: Đó là sử dụng "&" hoặc hàm Concat(Text1,...) để nối chúng lại với nhau:

Tại ô F3, gỏ vào công thức: =D3&" "&E3

Hoặc công thức: =CONCAT(D3," ",E3)

Copy công thức xuống dưới cho những ô còn lại, ta sẽ được kết quả như hình trên.

Lưu ý: 

Nếu tách riêng Họ thành một cột, Tên và Tên lót riêng riêng một cột thì chúng ta thực hiện tương tự như trên,  nhưng tại bước 2 trong hộp Find what, chúng ta nhập khoản trắng trước rồi mới đến dấu * (chỉ để tách họ riêng một cột); Tại bước 3, tại ô F3, chúng ta gỏ vào công thức =RIGHT(B3,LEN(B3)-LEN(E3)).

Nếu bản danh sách ban đầu khi trích xuất mà có các khoản cách giữa các chữ không đồng nhất (trước và sau các chữ có khoản trắng thừa) thì chúng ta buộc phải xử lý loại bỏ khoản trắng thừa rồi mới áp dụng các thủ thuật trên. Loại bỏ khoản trắng bị thừa bằng công thức sau =TRIM(SUBSTITUTE(B3,CHAR(160),"")) với B3 là ô cần xử lý.

Hy vọng với chia sẻ trên sẽ hữu ích cho các bạn trong công việc khi phải "tự tay" tách hay gộp nhiều cột với nhau mà không phải sử dụng đến các phần mềm phức tạp can thiệp.

Ketoan.biz