Excel2010表格中進(jìn)行內(nèi)容只讀不能修改的操作方法
如何使excel表格內(nèi)容只讀不能修改及破解，具體該怎么去進(jìn)行操作的呢?今天，學(xué)習(xí)啦小編就教大家在Excel2010表格中進(jìn)行內(nèi)容只讀不能修改的操作技巧。
Excel2010表格中進(jìn)行內(nèi)容只讀不能修改的操作步驟
只讀不能修改方法:
在EXCEL里，選擇工具——選項(xiàng)——安全性——分別設(shè)置打開(kāi)權(quán)限密碼和修改權(quán)限密碼，2個(gè)密碼要不一樣，然后確定，會(huì)彈出對(duì)話框讓你重新輸入密 碼，第一個(gè)框輸入你設(shè)置打開(kāi)權(quán)限密碼，第二個(gè)框輸入你修改權(quán)限密碼，然后保存文件。然后你把打開(kāi)權(quán)限密碼給別人就OK了，別人打開(kāi)就是只讀，不能修改。要 修改需要你修改權(quán)限密碼。
補(bǔ)充一下：如果不想只讀打開(kāi)需要密碼，可以不設(shè)置打開(kāi)權(quán)限密碼，只設(shè)置修改權(quán)限密碼。
破解Excel保護(hù)方法
Excel對(duì)數(shù)據(jù)有很強(qiáng)的保護(hù)功能，可以對(duì)整個(gè)或部分?jǐn)?shù)據(jù)隱藏、禁止復(fù)制和修改等，但有時(shí)候，由于忘記了密碼，或者記不起以前自己到底做了哪些改動(dòng)，這種保護(hù)反而會(huì)為難自己。針對(duì)這種情況，我總結(jié)了幾種解除保護(hù)的方法，供大家參考，并希望能夠以此拋磚引玉。
一、解除單元格的保護(hù)
1.取消隱藏的行或列
被隱藏的行或列有一個(gè)明顯的標(biāo)志，那就是在行標(biāo)題欄或列標(biāo)題欄上有一條粗的黑線。取消隱藏的行或列的方法有兩種，一種是直接拖動(dòng)隱藏處的行或列標(biāo)題使之變 寬，也可以選中包含隱藏區(qū)域的行或列，通過(guò)“格式”菜單中的行列項(xiàng)目輸入非0數(shù)值調(diào)整行高或列寬，使數(shù)據(jù)有足夠的空間顯示出來(lái);另一種方法是選中包含隱藏 內(nèi)容的行或列區(qū)域，再通過(guò)“格式”菜單行列選項(xiàng)中的“取消隱藏”命令來(lái)恢復(fù)全部行列區(qū)域。
2.取消隱藏單元格或區(qū)域中的數(shù)據(jù)
為了不讓他人直接看到單元格的內(nèi)容，一種簡(jiǎn)單的方法就是把數(shù)據(jù)顏色設(shè)置成與背景色相同，這時(shí)我們只要拖選整個(gè)工作表即可讓數(shù)據(jù)原形畢露;如果用圖片、矩形 框之類的非字符內(nèi)容將重要數(shù)據(jù)遮蓋，需要先用拖選方式找出工作表中的圖片位置，再將其移開(kāi)，激活被遮住的單元格，一般在公式編輯欄中就會(huì)顯示其內(nèi)容。
若在單元格格式中使用了三個(gè)分號(hào)或空格之類的自定義格式，一般只要選中此單元格，在公式編輯欄中即可顯示其內(nèi)容，要想取消這種隱藏，可重新設(shè)置此單元格格式，在“數(shù)字”選項(xiàng)卡中選擇“常規(guī)”即可。
如果選中懷疑有數(shù)據(jù)的單元格后并沒(méi)有在公式編輯欄中顯示數(shù)據(jù)，不一定它就真的沒(méi)有數(shù)據(jù)，我們只要看一下單元格的格式能否設(shè)置，若不能的話說(shuō)明工作表被保 護(hù)，如果不用密碼可以取消工作表保護(hù)的話，再在單元格格式設(shè)置中先查看數(shù)據(jù)格式是否為自定義，然后查看“保護(hù)”選項(xiàng)卡，看看是否已勾選了“隱藏”項(xiàng)，將這 些都取消就可以知道單元格是否真的沒(méi)有內(nèi)容。當(dāng)然，如果在撤消工作表保護(hù)時(shí)需要密碼的話，我們將無(wú)可奈何，除非用戶設(shè)置的密碼很簡(jiǎn)單，那我就告訴你一個(gè)秘 密，當(dāng)密碼的形式是“*123*”或“*abc*”時(shí)，我們可以分別用“*333*”或“*ccc*”來(lái)破解，星號(hào)表示任意字符，到底是什么只有靠猜了， 這種方法對(duì)工作表和工作簿保護(hù)的密碼也適應(yīng)。
3.取消單元格數(shù)據(jù)的只讀屬性
有時(shí)單元格中的數(shù)據(jù)雖然可見(jiàn)，但是我們既不能修改，也不能在當(dāng)前工作表中復(fù)制，對(duì)于這種保護(hù)措施，取消的方法跟上面的第2點(diǎn)基本相同，只要取消單元格格式 設(shè)置對(duì)話框中“保護(hù)”選項(xiàng)卡中的“鎖定”一項(xiàng)即可。當(dāng)然還需要通過(guò)密碼取消工作表的保護(hù)才行。 如果我們無(wú)法通過(guò)密碼取消怎么辦?你可以將其復(fù)制粘貼到其他工作表中，再在新的工作表中進(jìn)行修改。
二、解除工作表的保護(hù)
查看是否有被隱藏的工作表，若菜單中有“格式→工作表→取消隱藏”項(xiàng)，則可通過(guò)此項(xiàng)取消隱藏的工作表。
如果在菜單中也沒(méi)有有效的“取消隱藏”項(xiàng)，仍然可能有被隱藏的工作表，這時(shí)我們可以任選一工作表右擊，在彈出菜單中選擇“查看代碼”，然后在左窗格中的 “Microsoft Excel 對(duì)象”列表中選擇被隱藏的工作表，將其“Visible”屬性值取“-1”退出即可，如圖所示。
如果工作表被密碼保護(hù)，則可復(fù)制當(dāng)前表全部?jī)?nèi)容到另一張未保護(hù)的工作表中編輯。
三、VBA宏代碼破解法：
第一步：打開(kāi)該文件，先解除默認(rèn)的“宏禁用”狀態(tài)，方法是點(diǎn)擊工具欄下的“選項(xiàng)”狀態(tài)按鈕，打開(kāi)“Microsoft Office安全選項(xiàng)”窗口，選擇其中的“啟用此內(nèi)容”，“確定”退出(圖2);
Excel2010表格中進(jìn)行內(nèi)容只讀不能修改的操作步驟圖1
再切換到“視圖”選項(xiàng)卡，點(diǎn)擊“宏”→“錄制宏”，出現(xiàn)“錄制新宏”窗口，在“宏名”定義一個(gè)名稱為：PasswordBreaker(圖3)，點(diǎn)擊“確定”退出;
Excel2010表格中進(jìn)行內(nèi)容只讀不能修改的操作步驟圖2
第二步：再點(diǎn)擊“宏”→“查看宏”，選擇“宏名”下的“PasswordBreaker”并點(diǎn)擊“編輯”，打開(kāi)“Microsoft Visual Basic”編輯器，用如下內(nèi)容替換右側(cè)窗口中的所有代碼：
Sub PasswordBreaker()
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)
If ActiveSheet.ProtectContents = False Then
MsgBox "One usable password is " & Chr(i) & Chr(j) & _
Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
ActiveWorkbook.Sheets(1).Select
Range("a1").FormulaR1C1 = Chr(i) & Chr(j) & _
Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub
第三步：再點(diǎn)擊“宏”→“查看宏”，選擇“宏名”下的“PasswordBreaker”并點(diǎn)擊“執(zhí)行”，密碼就現(xiàn)形了(圖4)。
Excel2010表格中進(jìn)行內(nèi)容只讀不能修改的操作步驟圖3
第四步：切換“審閱”選項(xiàng)卡，點(diǎn)擊“撤消工作表保護(hù)”，然后輸入密碼即可解除鎖定。
另一個(gè)能解除Excel工作表保護(hù)的VBA腳本
Option Explicit
Public Sub AllInternalPasswords()
' Breaks worksheet and workbook structure passwords. Bob McCormick
' probably originator of base code algorithm modified for coverage
' of workbook structure / windows passwords and for multiple passwords
'
' Norman Harker and JE McGimpsey 27-Dec-2002 (Version 1.1)
' Modified 2003-Apr-04 by JEM: All msgs to constants, and
' eliminate one Exit Sub (Version 1.1.1)
' Reveals hashed passwords NOT original passwords
Const DBLSPACE As String = vbNewLine & vbNewLine
Const AUTHORS As String = DBLSPACE & vbNewLine & _
"Adapted from Bob McCormick base code by" & _
"Norman Harker and JE McGimpsey"
Const HEADER As String = "AllInternalPasswords User Message"
Const VERSION As String = DBLSPACE & "Version 1.1.1 2003-Apr-04"
Const REPBACK As String = DBLSPACE & "Please report failure " & _
"to the microsoft.public.excel.programming newsgroup."
Const ALLCLEAR As String = DBLSPACE & "The workbook should " & _
"now be free of all password protection, so make sure you:" & _
DBLSPACE & "SAVE IT NOW!" & DBLSPACE & "and also" & _
DBLSPACE & "BACKUP!, BACKUP!!, BACKUP!!!" & _
DBLSPACE & "Also, remember that the password was " & _
"put there for a reason. Don't stuff up crucial formulas " & _
"or data." & DBLSPACE & "Access and use of some data " & _
"may be an offense. If in doubt, don't."
Const MSGNOPWORDS1 As String = "There were no passwords on " & _
"sheets, or workbook structure or windows." & AUTHORS & VERSION
Const MSGNOPWORDS2 As String = "There was no protection to " & _
"workbook structure or windows." & DBLSPACE & _
"Proceeding to unprotect sheets." & AUTHORS & VERSION
Const MSGTAKETIME As String = "After pressing OK button this " & _
"will take some time." & DBLSPACE & "Amount of time " & _
"depends on how many different passwords, the " & _
"passwords, and your computer's specification." & DBLSPACE & _
"Just be patient! Make me a coffee!" & AUTHORS & VERSION
Const MSGPWORDFOUND1 As String = "You had a Worksheet " & _
"Structure or Windows Password set." & DBLSPACE & _
"The password found was: " & DBLSPACE & "$$" & DBLSPACE & _
"Note it down for potential future use in other workbooks by " & _
"the same person who set this password." & DBLSPACE & _
"Now to check and clear other passwords." & AUTHORS & VERSION
Const MSGPWORDFOUND2 As String = "You had a Worksheet " & _
"password set." & DBLSPACE & "The password found was: " & _
DBLSPACE & "$$" & DBLSPACE & "Note it down for potential " & _
"future use in other workbooks by same person who " & _
"set this password." & DBLSPACE & "Now to check and clear " & _
"other passwords." & AUTHORS & VERSION
Const MSGONLYONE As String = "Only structure / windows " & _
"protected with the password that was just found." & _
ALLCLEAR & AUTHORS & VERSION & REPBACK
Dim w1 As Worksheet, w2 As Worksheet
Dim i As Integer, j As Integer, k As Integer, l As Integer
Dim m As Integer, n As Integer, i1 As Integer, i2 As Integer
Dim i3 As Integer, i4 As Integer, i5 As Integer, i6 As Integer
Dim PWord1 As String
Dim ShTag As Boolean, WinTag As Boolean
Application.ScreenUpdating = False
With ActiveWorkbook
WinTag = .ProtectStructure Or .ProtectWindows
End With
ShTag = False
For Each w1 In Worksheets
ShTag = ShTag Or w1.ProtectContents
Next w1
If Not ShTag And Not WinTag Then
MsgBox MSGNOPWORDS1, vbInformation, HEADER
Exit Sub
End If
MsgBox MSGTAKETIME, vbInformation, HEADER
If Not WinTag Then
MsgBox MSGNOPWORDS2, vbInformation, HEADER
Else
On Error Resume Next
Do 'dummy do loop
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
With ActiveWorkbook
.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)
If .ProtectStructure = False And _
.ProtectWindows = False Then
PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & _
Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
MsgBox Application.Substitute(MSGPWORDFOUND1, _
"$$", PWord1), vbInformation, HEADER
Exit Do 'Bypass all for...nexts
End If
End With
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
Loop Until True
On Error GoTo 0
End If
If WinTag And Not ShTag Then
MsgBox MSGONLYONE, vbInformation, HEADER
Exit Sub
End If
On Error Resume Next
For Each w1 In Worksheets
'Attempt clearance with PWord1
w1.Unprotect PWord1
Next w1
On Error GoTo 0
ShTag = False
For Each w1 In Worksheets
'Checks for all clear ShTag triggered to 1 if not.
ShTag = ShTag Or w1.ProtectContents
Next w1
If ShTag Then
For Each w1 In Worksheets
With w1
If .ProtectContents Then
On Error Resume Next
Do 'Dummy do loop
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
.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)
If Not .ProtectContents Then
PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & _
Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
MsgBox Application.Substitute(MSGPWORDFOUND2, _
"$$", PWord1), vbInformation, HEADER
'leverage finding Pword by trying on other sheets
For Each w2 In Worksheets
w2.Unprotect PWord1
Next w2
Exit Do 'Bypass all for...nexts
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
Loop Until True
On Error GoTo 0
End If
End With
Next w1
End If
MsgBox ALLCLEAR & AUTHORS & VERSION & REPBACK, vbInformation, HEADER
End Sub
-----------------------------------
把上面的代碼保存為一個(gè)宏，運(yùn)行，點(diǎn)兩次確定，等到它運(yùn)行結(jié)束，工作表密密碼就解除了。
★如果出現(xiàn)提示“工程不可查看”不能錄制新宏，可以先使用“VBA工程加鎖解鎖器”來(lái)解除其“工程不可查看”后再用上面的方法找出保護(hù)密碼。
當(dāng)然了，“VBA工程加鎖解鎖器”顧名思義，這是一個(gè)既可以解除Excel保護(hù)密碼，又可以為Excel加保護(hù)密碼的軟件，實(shí)為一個(gè)優(yōu)秀的Excel輔助工具。
下載地址：http://pickup.mofile.com/9306322777229726
四、解除工作簿的保護(hù)
如果工作簿被密碼保護(hù)不能打開(kāi)，我們可以也使用一些專用的破解軟件進(jìn)行破解
Intelore Excel Password Recovery V1.0c
下載地址：http://pickup.mofile.com/6508444219536677
Passware Kit V7.1.1411 漢化版.rar
下載地址 http://pickup.mofile.com/1349573336951801
如果只能以只讀方式打開(kāi)，我們打開(kāi)后另存為一個(gè)不設(shè)密碼的副本，即可解除只讀限制。
有時(shí)雖然工作簿在打開(kāi)時(shí)沒(méi)有任何密碼，但如果我們不能對(duì)工作表進(jìn)行復(fù)制、移動(dòng)和插入等操作，那是保護(hù)了工作簿，可以通過(guò)依次選擇“工具→保護(hù)→撤消工作簿 保護(hù)”菜單命令來(lái)解除，但如果有密碼的話，能夠猜出來(lái)更好，否則只有新建一工作簿，將被保護(hù)的工作表內(nèi)容用拖選的方法復(fù)制到新的工作簿中進(jìn)行編輯。
相信有了上面這些方法，Excel中應(yīng)該沒(méi)有多少東西能夠瞞過(guò)你的火眼金睛，也沒(méi)有多少內(nèi)容讓你不能左右了。
Excel2010表格中進(jìn)行內(nèi)容只讀不能修改的操作相關(guān)文章：
1.Excel2010中設(shè)置只讀修改權(quán)限的操作方法
2.Excel2010中進(jìn)行設(shè)置取消只讀模式的操作方法
3.Excel2010中進(jìn)行表格保護(hù)不能編輯的操作方法