Công ty TNHH Đào tạo Tư vấn và Đầu tư AFTC
Trang 2/9 đầuđầu 1 2 3 4 5 6 ... cuốicuối
Hiển thị kết quả tìm kiếm từ 11 đến 20 trên tổng số: 83
  1. #11
    Tham gia ngày
    03 2007
    Nơi Cư Ngụ
    TP. HCM
    Bài gởi
    2,025
    Cảm ơn
    2,620
    Được cảm ơn 17,495 lần trong 1,755 bài viết

    Chiêu thứ 7: Tạo chỉ mục cho các Sheet trong Workbook

    Nếu bạn đã tốn quá nhiều thời gian trong một workbook (bảng tính) với rất nhiều worksheet (trang tính), bạn sẽ cảm thông được sự khó khăn trong việc tìm kiếm một trang tính nào đó. Khi đó, có lẽ ta nên lập một chỉ mục các trang tính đang có để thuận tiện cho việc điều hướng trong bảng tính.

    Bằng cách sử dụng một chỉ mục các trang tính sẽ cho phép bạn nhanh chóng và dễ dàng điều hướng trong bảng tính, chỉ bằng một cú nhấp chuột sẽ đưa bạn đến chính xác nơi bạn muốn đến mà không lo bị nhầm lẫn. Bạn có thể tạo một chỉ mục trong một vài cách: bằng tay, tự động tạo ra bởi mã VBA, hoặc là sử dụng trình đơn tùy chọn theo ngữ cảnh (thậm chí có thể dùng các hàm Macro4 – không trình bày ở đây).


    Tạo chỉ mục thủ công


    Cách này rất dễ làm, bạn chỉ cần chèn mới một worksheet và đăt cho nó một cái tên, ví dụ như tên là Index. Sau đó, bạn nhận vào tên của các worksheet có trong workbook và tạo các siêu liên kết (hyperlink) đến các worksheet tương ứng với tên mà bạn nhập.

    Để tạo hyperlink bạn chọn tên sheet, sau đó vào Insert | chọn Hyperlink tại nhóm Links [E2003: Insert | Hyperlinks] hoặc nhấn phím tắt là Ctrl+K để mở hộp thoại Insert Hyperlink.


    Hộp thoại Insert Hyperlink

    Chọn Place in This Documentchọn tên Sheet muốn kết nối tới. Nhấn nút OK để hoàn tất.

    Phương pháp này phù hợp khi bảng tính không có quá nhiều worksheet và tên worksheet không có sự thay đổi thường xuyên, nếu không sẽ gây ra nhiều khó khăn cho công tác bảo trì trang chỉ mục.


    Tạo chỉ mục tự động bằng cách sử dụng VBA


    Một cách thay thế khác là sử dụng VBA để tự động tạo ra các chỉ mục. Đoạn mã sau đây sẽ tự động tạo ra một bảng chỉ mục liên kết đến tất cả các worksheet mà bạn có trong workbook. Bảng chỉ mục này sẽ được tạo lại mỗi khi worksheet lưu chỉ mục được chọn.

    Đoạn mã này phải được đặt trong private module của Sheet chứa chỉ. Chèn một worksheet mới vào workbook và đặt tên cho nó là Index chẳng hạn. Nhấp chuột phải vào tên worksheet vừa tạo và chọn ViewCode từ trình đơn ngữ cảnh hoặc nhấn tổ hợp phím Alt+F11.

    Nhập đoạn mã VBA sau vào cửa sổ Code:

    PHP Code:
    Private Sub Worksheet_Activate()
      
    Dim wSheet As Worksheet
      Dim lCount 
    As Long
      lCount 
    1
          
    With Me
       
    .Columns(1).ClearContents
       
    .Cells(11) = "INDEX"
    End With

    For Each wSheet In Worksheets
       
    If wSheet.Name <> Me.Name Then
         lCount 
    lCount 1
         With wSheet
             
    .Range("A1").Name "Start" wSheet.Index
             
    .Hyperlinks.Add Anchor:=.Range("A1"), Address:=""SubAddress:= _
              
    "Index"TextToDisplay:="Back to Index"
         
    End With
         Me
    .Hyperlinks.Add Anchor:=Me.Cells(lCount1), Address:=""SubAddress:= _
         
    "Start" wSheet.IndexTextToDisplay:=wSheet.Name
      End 
    If    
    Next wSheet
    End Sub 


    Nhấn tổ hợp phím Alt+Q để thoát VBE và trở về cửa sổ bảng tính, sau đó lưu bảng tính lại. Để chạy đoạn mã vừa tạo, bạn dùng chuột chọn sang worksheet khác trong workbook và sau đó chọn lại worksheet Index để kích hoạt sự kiện Worksheet_Activate.

    Lưu ý rằng, đoạn mã sẽ các đặt tên (Name) cho các ô A1 ở mỗi worksheet kèm theo số chỉ mục của worksheet trong bảng tính (worksheet đầu tiên có chỉ mục là 1, kế đó là 2, 3…. n). Điều này bảo đảm rằng ô A1 trên mỗi trang tính có một tên khác nhau. Nếu ô A1 trên worksheet của bạn đã được đặt tên, bạn nên cân nhắc đến việc thay đổi ô A1 trong đoạn mã sang một địa chỉ khác phù hợp hơn.


    Thêm thuộc tính cho workbook

    Lưu ý, nếu bạn có thiết lập Hyperlink base (siêu liên kết cơ sở) trong workbook thì các hyperlink được tạo ra từ đoạn mã trên sẽ không thể hoạt động được, do chúng đã liên kết đến các Name trong workbook hiện hành. Khi thuộc tính hyperlink base được thiết lập thì các siêu liên kết sẽ trỏ đến hyperlink base kết hợp với các Name.
    Thêm thuộc tính Hyperlink base: Vào Office | Prepare | Properties | Document Properties | chọn Advanced Properties [E2003: File | Properties | Summary] và nhập vào hyperlink base. Ví dụ như chúng ta nhập vào http://www.giaiphapexcel.com/

    Siêu liên kết trước và sau khi thêm Hyperlink base


    Thêm lệnh gọi chỉ mục vào trình đơn ngữ cảnh

    Cách thứ ba sẽ hướng dẫn bạn thêm một lệnh vào trình đơn ngữ cảnh để gọi danh sách các worksheet trong workbook và bạn có thể chọn lệnh này bằng cách kích phải chuột vào vùng bảng tính bất kỳ. Đoạn mã sau sẽ gọi thanh lệnh Tabs của workbook như hình sau:



    Thêm lệnh “Sheet Index” vào trình đơn ngữ cảnh

    Nhấn Alt+F11 để mở cửa sổ VBE, sau đó nhập đoạn mã sau vào cửa sổ Code của ThisWorkbook:

    PHP Code:
    Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As ObjectByVal Target As RangeCancel As Boolean)
        
    Dim cCont As CommandBarButton
        On Error Resume Next
        Application
    .CommandBars("Cell").Controls("Sheet Index").Delete
        On Error 
    GoTo 0
        Set cCont 
    Application.CommandBars("Cell").Controls.Add _
                    
    (Type:=msoControlButtonTemporary:=True)
        
    With cCont
            
    .Caption "Sheet Index"
            
    .OnAction "IndexCode"
        
    End With
    End Sub 


    Tiếp theo, bạn vào Insert | Module và nhập vào đoạn mã sau:

    PHP Code:
    Sub IndexCode()
          
    Application.CommandBars("workbook Tabs").ShowPopup
    End Sub 


    Đoạn mã trên sẽ thực thi mỗi khi bạn chọn lệnh “Sheet Index” từ trình đơn ngữ cảnh và bạn phải đặt nó trong Module thì Excel mới có thể thấy được thủ tục này.

    Nhấn Alt+Q để đóng cửa sổ VBE trở về bảnh tính Excel. Kể từ lúc này, bạn nhấp phải chuột tại bất kỳ ô nào trong bất kỳ worksheet nào thì trong trình đơn ngữ cảnh sẽ có thêm lệnh “Sheet Index” giúp bạn điều hướng đến bất cứ worksheet nào trong workbook một cách dễ dàng.


    Tập tin đính kèm Tập tin đính kèm
    thay đổi nội dung bởi: TranThanhPhong, 13-12-08 lúc 08:26 AM


  2. #12
    Tham gia ngày
    11 2007
    Nơi Cư Ngụ
    Gò Vấp
    Bài gởi
    8,623
    Cảm ơn
    4,349
    Được cảm ơn 26,372 lần trong 8,160 bài viết

    Chiêu thứ 8: Giới hạn vùng cuộn của bảng tính

    Nếu bạn không muốn thanh cuộn kéo bảng tính lên xuống hoặc qua phải nhiều, hoặc bạn có dữ liệu ở 1 vùng nào đó mà không muốncho nguời dùng xem, bạn có thể giới hạn vùng nhìn được của bảng tính trong phạm vi bạn cho phép.
    Các bảng tính tạo bởi Excel 2007 có số cột tối đa 16.384, Excel trước đó là 256 (từ A đến IV), và có số dòng tối đa là 1.048.576 (trước đó là 65.536). Nhưng thường thì bạn hiếm khi sử dụng hết. Bạn có thể giới hạn chỉ cho người dùng xem trong 1 phạm vi nào đó, còn dữ liệu nhạy cảm, bạn để ngoài vùng đó. Ngoài ra nó còn hạn chế khi ai đó vô tình kéo thanh cuộn xuống dòng 500.000 và đến lượt người khác la làng rằng kéo mãi chẳng thấy cái gì mà xem.
    Bạn có thể dùng cách đơn giản là dấu (Hide) những dòng và cột muốn dấu hoặc bằng cách định nghĩa 1 vùng cho phép xem hoặc chỉ kích hoạt vùng có dữ liệu.

    1. Dấu dòng và cột:
    Cách dễ nhất là dấu những dòng và cột không dùng đến:
    Trên sheet hiện hành, xác định dòng cuối có dữ liệu, nhấn chọn dòng dưới nó, nhấn thêm cùng lúc Ctrl + Shift + mũi tên xuống để chọn đến cuối dòng cuối cùng (1.048.576). Sau đó trong tab Home, chọn Format Hide & Unhide – Hide Rows trong Excel 2007 hoặc Format - Row - Hide trong 2003 trở về trước, hoặc click phải chuột và chọn Hide.
    Làm tương tự như vậy để dấu những cột không dùng đến: tìm cột cuối, click chọn cột kế bên, nhấn Ctrl + Shift + mũi tên sang phải, chọn hide columns.
    Kết quả như hình, vùng làm việc bị trùm 1 màu tăm tối chung quanh và chẳng ai có thể kéo đi đâu được nữa:





    2. Xác định 1 vùng sử dụng bằng Property:
    Bằng cách sử dụng Property của sheet trong VBA, bạn có thể ấn định vùng làm việc trong phạm vi mong muốn. Thực hiện như sau:
    Nhấn chuột phải vào tên sheet trong tab Sheet Names, chọn View code, hoặc nhấn Alt + F11, chọn đúng tên sheet trong cửa sổ Project Explorer, rồi xuống khung Property, tìm dòng ScrollArea gõ vào địa chỉ vùng mong muốn thí dụ $A$1:$H$50.




    Quay trở lại bảng tính và thử kéo thanh cuộn, ta thấy chỉ có thể cuộn xuống đến dòng 50 và cuộn ngang đến cột H là tối đa, không cuộn được nữa.
    Tuy vậy, Excel không lưu tính chất này khi lưu bảng tính, nên lần sau mở file lên phải set Property lại. Do đó ta phải viết 1 đoạn code thực hiện điều này mỗi khi kích hoạt sheet. Vào vùng soạn thảo code của đúng sheet mình muốn, chọn sự kiện worksheet_activate:
    PHP Code:
    Private Sub Worksheet_Activate ( )
    Me.ScrollArea "A1:H50"
    End Sub 

    Bây giờ mỗi khi kích hoạt bảng tính, VBA sẽ ấn định vùng giới hạn như mong muốn.
    Mặc dù không có vùng bao quanh tăm tối như cách trên, nhưng bạn không thể nào cuộn ra khỏi vùng, bạn cố chọn 1 ô nằm ngoài vùng cho phép cũng không được, kể cả cột I, J, K dù bạn có ngó thấy cũng chỉ để thèm thôi.
    Thậm chí với những đoạn code VBA bạn tạo sau này, trong đó có câu lệnh select 1 vùng nằm ngoài vùng cho phép, hoặc chọn nguyên cột nguyên dòng, cũng không chọn được.
    Để có thể thực thi các đoạn code trên bạn phải cho vào code 2 dòng lệnh:
    Đầu code thêm dòng: ActiveSheet.ScrollArea = ""
    Cuối code thêm dòng: ActiveSheet.ScrollArea = "$A$1:$G$50"
    Thí dụ:

    PHP Code:
    Sub MyMacro( )
    ActiveSheet.ScrollArea ""
    Range("Z100").Select
    Selection
    .Font.Bold True
    ActiveSheet
    .ScrollArea "$A$1:$G$50"
    Sheets("Daily Budget").Select
    ActiveSheet
    .ScrollArea ""
    Range ("T500").Select
    Selection
    .Font.Bold False
    ActiveSheet
    .ScrollArea "$A$1:$H$25"
    End Sub 

    Đoạn code trên chọn ô Z100 trong sheet hiện hành và định dạng in đậm. Sau đó chọn ô T500 trong sheet khác, định dạng in thường (không đậm). Trước khi thực hiện chọn và định dạng ở sheet nào, phải set vùng cuộn sheet đó là “”. Sau khi định dạng,set trả vùng giới hạn cuộn theo mong muốn.

    3. Chỉ kích hoạt vùng dữ liệu hiện hành:

    Phương pháp này linh hoạt hơn, tự động giới hạn vùng cuộn bảng tính vừa bằng vùng dữ liệu của bảng tính mà bạn đặt code sau:
    PHP Code:
    Private Sub Worksheet_Activate( )
    Me.ScrollArea =Me.UsedRangeAddress
    End Sub 

    Đoạn code trên sẽ chạy mỗi khi bạn kích hoạt bảng tính mà bạn đặt code. Dù vậy cũng có hạn chế là bạn không thể thêm dữ liệu vào dòng mới hoặc cột mới. Bạn có thể mở rộng vùng giới hạn ra thêm 5 dòng và 2 cột bằng đoạn code sau:
    PHP Code:
    Private Sub Worksheet_Activate()
    With Me.UsedRange
    Me
    .ScrollArea = .Resize(.Rows.Count 5, .Columns.Count 2).Address
    End With
    End Sub 

    Còn nếu bạn muốn hơn nữa, nhập liệu thêm 1 cách thoải mái, thì dùng 1 đoạn code nhằm reset vùng cuộn bằng nguyên sheet:
    PHP Code:
    Sub ResetScrollArea( )
    ActiveSheet.ScrollArea ""
    End Sub 

    Bạn có thể gán short key (phím tắt) cho đoạn code này bằng cách nhấn Alt F8, chọn macro ResetScrollArea, nhấn nút option, và gán 1 phím tắt thí dụ Ctrl + W.



    Sau này mỗi khi bạn muốn nhập liệu, nhấn Ctrl + W trước khi nhập liệu. Khi nhập liệu xong, chỉ cần bạn kích hoạt 1 sheet khác xong quay lại sheet này, vùng cuộn lại bị giới hạn bởi đoạncode trên (Worksheet_Activate())


    thay đổi nội dung bởi: BNTT, 23-11-08 lúc 08:49 PM
    Cheettit Old Man Thánh tắc ngô bất năng, ngô học bất yếm, nhi giáo bất quyện dã.


  3. #13
    Tham gia ngày
    07 2007
    Nơi Cư Ngụ
    Dalat
    Bài gởi
    4,901
    Cảm ơn
    4,386
    Được cảm ơn 21,266 lần trong 3,973 bài viết

    Chiêu thứ 9: Khóa và bảo vệ những ô có chứa công thức

    Chiêu này giúp bạn cho phép người khác thay đổi các ô có chứa dữ liệu, nhưng cấm họ thay đổi các ô chứa công thức. Bạn cũng có thể bảo vệ các ô có chứa công thức mà không cần phải bảo vệ toàn bộ trang tính của bạn.

    Khi tạo một bảng tính, thường chúng ta sẽ phải dùng đến một số công thức, và khi chia sẻ bảng tính cho mọi người, có thể bạn muốn rằng, không ai có thể can thiệp (xóa, sửa...) những vào những ô có chứa công thức. Cách dễ nhất là cũng phổ biến nhất là Protect (bảo vệ) bảng tính. Tuy nhiên, Protect bảng tính không chỉ ngăn không cho can thiệp vào các ô chứa công thức, mà nó không cho can thiệp vào tất cả, nghĩa là không ai có thể làm gì bảng tính của bạn. Đôi khi, bạn lại không muốn như vậy, bạn chỉ muốn bảo vệ các ô chứa công thức thôi, còn những ô chứa dữ liệu thì không.

    Có ba giải pháp để thực hiện điều này: Khóa những ô chứa công thức, sử dụng chức năng Data-validation cho các ô chứa công thức, và tự động bật tắt chức năng bảo vệ.


    Khóa các ô chứa công thức

    Theo mặc định, tất cả các ô trong bảng tính đều được khóa (locked), tuy nhiên, nó chẳng có tác dụng gì trừ phi bạn áp dụng lệnh Protect bảng tính. Đây là cách dễ nhất để áp dụng lệnh Protect cho bảng tính, nhưng chỉ những ô chứa công thức thì mới bị khóa, và được bảo vệ:

    Chọn toàn bộ bảng tính, bằng cách nhấn Ctrl+A, hoặc nhấn vào cái ô vuông nằm ở giao điểm của cột A và hàng 1. Rồi nhấn nút phải chuột và chọn Format Cells, rồi trong tab Protection, bỏ đánh dấu ở tùy chọn Locked, rồi nhấn OK:


    Mở khóa (Unlock) toàn bộ bảng tính bằng cách bỏ tùy chọn Locked

    Sau đó, bạn chọn đại một ô nào đó, chọn HomeFind & SelectGo To Special [E2003: Edit | Go To… | Special]; hoặc nhấn Ctrl+G hay F5 rồi nhấn vào nút Special.... Hộp thoại sau đây sẽ mở ra:


    Dùng hộp thoại Go To Special để chọn các ô có chứa công thức

    Trong hộp thoại đó, bạn nhấn vào tùy chọn Formulas, và nếu cần thiết thì chọn hoặc không chọn thêm 4 ô nhỏ ở dưới (liệt kê các loại công thức, mặc định thì cả 4 ô này đều được chọn), và nhấn OK. Sau đó, bạn mở lại hộp thoại Format Cells đã nói ở trên, nhưng lần này thì bạn đánh dấu vào tùy chọn Locked, và nếu bạn thích ẩn luôn công thức (không cho thấy) thì đánh dấu vào tùy chọn Hidden, nhấn OK.

    Việc cuối cùng là Protect bảng tính: Chọn Home trên Ribbon, nhấn vào Format trong nhóm Cells, rồi nhấn vào Protect Sheet...; hoặc chọn Review trên Ribbon, rồi nhấn vào Protect Sheet [E2003: Tools | Protection | Protect Worksheet]:


    Chọn Protect Sheet từ Home


    Chọn Protect Sheet từ Review

    Trong hộp thoại Protect Sheet, bỏ đánh dấu ở tùy chọn Select locked cells, chỉ cho phép Select unlocked cells (chọn những ô không khóa), và nhập vào một password, nếu cần thiết:


    Bỏ tùy chọn Select locked cells trong hộp thoại Protect Sheet

    Vậy là xong. Từ bây giờ, những ô chứa công thức của bạn sẽ được bảo vệ, có thể không xem thấy được nếu bạn đã chọn Hidden, bạn không lo những công thức này bị can thiệp nữa.


    Sử dụng Data-validation

    Sử dụng Data-validation, chỉ là đơn giản không cho ghi đè vào những ô có chứa công thức, nghĩa là không cho sửa công thức. Tuy nhiên, phương pháp này chỉ phòng ngừa cho chính bạn, nghĩa là tránh việc táy máy sửa lại cái gì đó trong những ô chứa công thức, chứ thật ra, mặc dù đã được "Validation", bạn vẫn có thể xóa công thức, hoặc dán vào những ô đó bất kỳ dữ liệu nào bạn thích... Nói chung nó không bảo vệ được gì nhiều. Nhưng cũng xin nói sơ qua về phương pháp này:

    Để thực hiện, bạn hãy chọn những ô chứa ô công thức bằng chức năng Go To Specials mà tôi đã nói ở trên. Rồi, với những ô chứa công thức đang được chọn, bạn gọi Data Validation từ menu Data trên Ribbon [E2003: Data | Validation]. Trong hộp thoại này, chọn tab Settings, chọn Custom cho khung Allow, và nhập công thức này: =" " vào khung Formula, rồi nhấn OK, như hình sau:


    Sử dụng Data Validation để bảo vệ những ô chứa công thức

    Kể từ đây, mỗi khi bạn nhập bất kỳ thứ gì vào trong những ô chứa công thức, hoặc bạn muốn sửa lại công thức, sẽ có một cảnh báo xuất hiện, ngăn không cho bạn nhập vào. Xin nhắc lại, phương pháp này không cấm việc xóa hẳn công thức, cũng như dán đè thứ gì đó vào những ô chứa công thức.


    Tự động bật tắt chức năng bảo vệ

    Phương pháp này, sẽ tự động bật chức năng bảo vệ bảng tính (Protect) mỗi khi bạn chọn một ô đã được khóa (locked), nhưng nó cũng sẽ tự động tắt chức năng bảo vệ khi bạn chọn một ô không bị khóa.

    Để bắt đầu, bạn hãy chắc chắn rằng những ô bạn muốn bảo vệ (ô chứa công thức) đã được khóa, còn những ô không cần bảo vệ thì không bị khóa, như tôi đã trình bày ở phương pháp thứ nhất. Sau đó, bạn nhấn Alt+F11, nhấn vào Sheet mà bạn muốn bảo vệ các ô đã khóa ở trong đó, rồi nhập vào trong khung soạn thảo đoạn code sau đây:
    PHP Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If 
    Target.Locked True Then
            Me
    .Protect Password:="Secret"
        
    Else
            
    Me.Unprotect Password:="Secret"
        
    End If
    End Sub 
    Nếu không cần đến password, bạn không cần dùng đoạn Password:="Secret", hoặc nếu muốn Password là thứ gì khác, bạn sửa lại chữ Secret bằng cái bạn muốn.

    Nếu bạn lo rằng người ta có thể vào trong khung soạn thảo VBA để xem password, bạn có thể bảo vệ các code này (không cho xem) bằng cách chọn ToolsVBAProject Properties, chọn tab Properties, chọn Lock Project for Viewing, và nhập vào một password.

    Tuy nhiên, phương pháp này cũng không hoạt động hoàn hảo, mặc dù nó cũng giúp bạn được phần nào việc bảo vệ các công thức. Từ khóa Target được sử dụng trong đoạn code sẽ chỉ để tham chiếu đến ô đang "active" ngay tại thời điểm nó được chọn (xin nói thêm, cho dù bạn chọn một dãy, nhưng trong dãy đó, chỉ có một ô "active" mà thôi, là ô đang có màu khác với những ô còn lại). Vì lý do này, nếu một người nào đó chọn một dãy các ô (với ô "active" không bị khóa), thì người đó có thể xóa toàn bộ dãy ô này, bởi vì khi đó thì chức năng Unprotect đã được tự động bật!


    thay đổi nội dung bởi: BNTT, 23-11-08 lúc 08:49 PM


  4. #14
    Tham gia ngày
    07 2007
    Nơi Cư Ngụ
    Dalat
    Bài gởi
    4,901
    Cảm ơn
    4,386
    Được cảm ơn 21,266 lần trong 3,973 bài viết

    Chiêu thứ 10: Sử dụng định dạng theo điều kiện để tìm dữ liệu trùng (Người dịch: TTPhong2007)
    Định dạng theo điều kiện của Excel thường được sử dụng để xác định giá trị trong phạm vi cụ thể trên bảng tính, nhưng chúng ta có thể cải tiến nó để nhận dạng sự trùng lắp dữ liệu trong một danh sách hoặc một bảng. Chức năng định dạng theo điều kiện trong Excel 2007 đã được cải tiến, do vậy bài hướng dẫn này thích hợp cho các phiên bản từ Excel 2003 về trước.

    Mọi người thường xuyên phải nhận diện sự trùng lắp dữ liệu trong một danh sách hoặc một bảng, và làm thủ công công việc này có thể mất nhiều thời gian và dễ mắc lỗi. Để làm cho công việc này dễ dàng hơn, bạn có thể chế biến công cụ định dạng theo điều của Excel một ít.

    Ví dụ như, bạn có một bảng dữ liệu là vùng $A$1:$H$100. Bạn chọn nó bằng cách chọn ô trên cùng – bên trái của bảng (A1) và kéo chuột đến ô dưới cùng bên phải (H100). Điều này rất quan trọng, vì ta muốn ô A1 phải là ô hiện hành (active) trong vùng lựa chọn. Sau đó vào Format | chọn Conditional Formatting….


    Lệnh định dạng theo điều kiện trong Excel 2003


    Trong hộp thoại Conditional Formatting, bạn chọn Formulas Is tại Condition 1 và nhập công thức sau vào hộp trống bên cạnh:
    =COUNTIF($A$1:$H$100,A1)>1


    Hộp thoại Conditional Formatting


    Nhấn chọn nút Format | chọn màu và kiểu chữ tại ngăn Font và chọn màu nền tại ngăn Patterns. Nhấn OK khi hoàn tất việc định dạng. Nhấn tiếp OK để áp dụng định dạng cho vùng dữ liệu đang chọn.


    Hộp thoại Format Cells


    Theo bảng số liệu minh họa thì hầu hết các ô đều có dữ liệu trùng trừ hai ô H7 và H49.

    Trong công thức định dạng theo điều kiện này, ta sử dụng tham chiếu tương đối cho địa chỉ ô cần kiểm tra trong vùng địa chỉ xác định. Bằng cách sử dụng định dạng theo điều kiện như thế này, Excel sẽ tự động nhận biết địa chỉ ô làm điều kiện trong hàm COUNTIF. Cụ thể thì các công thức định dạng theo điều kiện tại các ô như sau:
    Ô A1 sẽ có công thức là: =COUNTIF($A$1:$H$100,A1)>1
    Ô A2 sẽ có công thức là: =COUNTIF($A$1:$H$100,A2)>1
    Ô A3 sẽ có công thức là: =COUNTIF($A$1:$H$100,A3)>1


    Ô B1 sẽ có công thức là: =COUNTIF($A$1:$H$100,B1)>1
    Ô B2 sẽ có công thức là: =COUNTIF($A$1:$H$100,B2)>1

    Định dạng theo điều kiện trong Excel 2007 đã có sẵn tính năng làm nổi các giá trị trùng trong một vùng dữ liệu. Các bước thực hiện như sau:

    Chọn vùng dữ liệu | vào ngăn Home | chọn Conditional Formatting | chọn Highlight Cells Rules | chọn Duplicate Values…


    Lệnh Duplicate Values của Conditional Formatting trong Excel 2007

    Trong hộp thoại Duplicate Values, bạn chọn kiểu Duplicate (hoặc Unique: duy nhất) và chọn định dạng tại hộp kế bên sau đó nhấn OK.


    Hộp thoại Duplicate Values

    thay đổi nội dung bởi: BNTT, 23-11-08 lúc 08:49 PM


  5. #15
    Tham gia ngày
    11 2007
    Nơi Cư Ngụ
    Gò Vấp
    Bài gởi
    8,623
    Cảm ơn
    4,349
    Được cảm ơn 26,372 lần trong 8,160 bài viết

    Chiêu số 11: Tìm dữ liệu xuất hiện 2 hoặc nhiều lần bằng công cụ Conditional Formating

    Dù cho công cụ Conditional Formating đã cải tiến rất nhiều trong Excel 2007, giúp chúng ta tìm những dữ liệu trùng (Duplicate), nhưng nó vẫn chưa cung cấp những tính năng tìm ra những dữ liệu xuất hiện 2 lần hoặc nhiều hơn.

    Nếu bạn muốn xác định những dữ liệu 2 lần hoặc nhiều hơn, bạn có thể dùng Conditional Formating với số lượng nhiều các điều kiện, trong giới hạn cho phép của bộ nhớ hệ thống. (Nhớ rằng trong Excel 2003 trở về trước, chỉ chấp nhận có 3 điều kiện). Sau đó bạn gán những định dạng màu sắc cho mỗi điều kiện.

    Để thực hiện, chọn ô A1 là ô trên cùng bên trái của vùng dữ liệu, bấm shif và clickvào ô H100. Cần nhắc các bạn 1lần nữa, là phải làmđúng trìnhtự để cho ô chọn (activecell) là ô A1. Bây giờ vào tab Home, Conditional Formating, NewRule trong mục Style. Chọn [FONT=Birka]Use a Formula to determine which cells to format” và click chọn “Format values where this formula is true.” Đối với Excel 2003: Format, Conditional Formating, chọn Formula Is). Trong ô trống gõ công thức:
    =COUNTIF($A$1:$H$100,A1)>3



    __________

    Nhấn nút Format, chọn màu nền và màu chữ cho những ô chứa dữ liệu xuất hiện hơn 3 lần, và OK.

    _____________

    Nhấn New Rule (trong Excel 2003 chọn vào điều kiện thứ 2), làm tiếp 1 điều kiện, lần này công thức là: =COUNTIF($A$1:$H$100,A1)=3.
    Nhấn New Rule (trong Excel 2003 chọn vào điều kiện thứ 3), làm tiếp 1 điều kiện, lần này công thức là: =COUNTIF($A$1:$H$100,A1)=2.
    Nhớ mỗi lần chọn 1 màu khác nhau.
    Kết quả: bạn có những ô có màu khác nhau cho những dữ liệu xuất hiện 2 lần, 3 lần, và nhiều hơn.

    Cần nhắc lại chuyện chọn đúng vùng chọn sao cho ô A1 là ô hiện hành, để Excel hiểu đúng điều kiện trong công thức CountIf. Và: Excel 2003 giới hạn bởi 3 điều kiện, trong khi đó Excel 2007 chỉ bị giới hạn bởi bộ nhớ hệ thống.


    thay đổi nội dung bởi: BNTT, 23-11-08 lúc 08:50 PM
    Cheettit Old Man Thánh tắc ngô bất năng, ngô học bất yếm, nhi giáo bất quyện dã.


  6. #16
    Tham gia ngày
    07 2007
    Nơi Cư Ngụ
    Dalat
    Bài gởi
    4,901
    Cảm ơn
    4,386
    Được cảm ơn 21,266 lần trong 3,973 bài viết

    Chiêu số 12: Tạo riêng một thanh công cụ cho riêng một bảng tính cụ thể
    Bạn đã từng tạo ra thanh công cụ riêng cho mình? Bạn có thấy rằng, thanh công cụ này luôn luôn được nạp vào Excel, và luôn hiển thị, với bất kỳ bảng tính nào, với bất kỳ người sử dụng nào? Có bao giờ, bạn muốn rằng, thanh công cụ tự tạo này chỉ được hiển thị với một bảng tính cụ thể nào đó mà thôi không?

    Ví dụ, bạn tự tạo ra một thanh công cụ có chứa những nút dùng để hỗ trợ việc nhập công thức và xử lý bảng (những dấu =, +, -, *, /, những nút dùng để xóa hàng, xóa cột, v.v...), tạm gọi là thanh công cụ A, phục vụ riêng cho bảng tính B. Và bạn muốn, làm thế nào để chỉ khi bạn mở bảng tính B, thì mới thấy thanh công cụ A của bạn, còn mở bảng tính khác, thì chỉ thấy những thanh công cụ mặc định của Excel? Thậm chí trong cùng một cửa sổ Excel, nhưng khi bạn kích hoạt bảng tính B, thì mới thấy thanh công cụ A, còn khi nhấn Ctrl+Tab để chuyển sang bảng tính khác, thì thanh công cụ A này sẽ biến mất?

    Thiết nghĩ, chắc hẳn đã có lúc bạn muốn điều tôi vừa nói. Vì nó giúp cho thanh công cụ của Excel luôn gọn gàng, ngăn nắp (luôn là thanh công cụ mặc định của Excel), nếu không phải là bạn, mà là người khác, thì không có gì bỡ ngỡ với thanh công cụ lạ hoắc; hoặc là, chỉ khi nào mở bảng tính B ra, thì mới cần đến thanh công cụ A, còn bình thường thì chẳng cần...

    Việc này, không có gì khó. Bạn chỉ cần chèn một đoạn code đơn giản vào trong "Private module" của bảng tính.

    Trước hết, bạn hãy tạo cho riêng mình một thanh công cụ. Đặt tên cho nó là "MyCustomToolbar" chẳng hạn.

    Tiếp theo, bạn nhấn Alt+F11 để mở cửa sổ Microsoft Visual Basic, nhấn đúp vào This workbook trong khung Project. Bạn cũng có thể làm nhanh việc này bằng cách nhấn nút phải chuột vào cái biểu tượng Excel bé tí nằm ngay bên trái menu File, rồi chọn View Code, cửa sổ VBA cũng sẽ mở ra với This workbook được chọn sẵn:

    Nhấn phải chuột vào biểu tượng Excel nhỏ bên trái menu File rồi chọn View Code

    Rồi, nhập vào hai đoạn code sau đây:

    PHP Code:
    Option Explicit
    --------------------------------------------------------------------

    Private 
    Sub Workbook_Activate()
        
    On Error Resume Next
            With Application
    .CommandBars("MyCustomToolbar")
                   .
    Enabled True
                   
    .Visible True
            End With
        On Error 
    GoTo 0
    End Sub
     
    --------------------------------------------------------------------

    Private 
    Sub Workbook_Deactivate()
        
    On Error Resume Next
            Application
    .CommandBars("MyCustomToolbar").Enabled False
        On Error 
    GoTo 0
    End Sub 
    Bạn có thể thay thế "MyCustomToolbar" bằng cái tên mà bạn đã đặt cho thanh công cụ tự tạo của bạn. Sau đó bạn nhấn Alt+Q để trở về Excel.

    Để thử, bạn hãy mở thêm một bảng tính nữa, Book2.xls chẳng hạn. Bạn sẽ thấy, khi nào bạn chọn bảng tính mà có đoạn code ở trên, thì bạn mới thấy "MyCustomToolbar", còn nếu chọn Book2 (chuyển đổi qua lại giữa các bảng tính bằng Ctrl+Tab), "MyCustomToolbar" biến mất! Chọn lại bảng tính ban đầu, "MyCustomToolbar" xuất hiện lại. Vậy là thành công rồi!

    Được voi đòi tiên. Bây giờ, giả sử bạn muốn rằng, trong cùng một bảng tính, nhưng chỉ có Sheet1 thì mới thấy "MyCustomToolbar", còn Sheet khác thì không?

    Được thôi. Bạn có thể viết thêm một "Level" nữa cho đoạn code trên. Hãy bảo đảm là bạn đang chọn bảng tính có chứa code (tốt nhất là bạn đóng cái Book2 đi). Quay trở lại cửa sổ VBA, bạn hãy nhấn phải chuột vào Sheet nào mà bạn muốn thấy "MyCustomToolbar", rồi chọn View Code (hoặc nhấn đúp vào tên Sheet đó cũng được). Ở đây tôi giả sử bạn chọn Sheet1.

    Nhập vào hai đoạn code sau đây:
    PHP Code:
    Option Explicit
    --------------------------------------------------------------------

    Private 
    Sub Worksheet_Deactivate()
        
    On Error Resume Next
            Application
    .CommandBars("MyCustomToolbar").Enabled False
        On Error 
    GoTo 0
    End Sub

    --------------------------------------------------------------------

    Private 
    Sub Worksheet_Activate()
        
    On Error Resume Next
            With Application
    .CommandBars("MyCustomToolbar")
                   .
    Enabled True
                   
    .Visible True
            End With
        On Error 
    GoTo 0
    End Sub 
    Rồi nhấn Alt+Q để quay lại Excel. Bây giờ, bạn hãy dùng chuột hoặc nhấn Ctrl+PgUp/Ctrl+PgDn để chọn qua lại các Sheet và xem thử cái gì xuất hiện trên màn hình.

    Xin đính kèm theo đây bảng tính đã có sẵn những đoạn code nói trên, cùng một "MyCustomToolbar" làm ví dụ.
    Cũng xin nhắc lại, chiêu này chỉ có tác dụng với những phiên bản trước Excel2007.

    Tập tin đính kèm Tập tin đính kèm
    thay đổi nội dung bởi: BNTT, 23-11-08 lúc 08:50 PM


  7. #17
    Tham gia ngày
    11 2007
    Nơi Cư Ngụ
    Gò Vấp
    Bài gởi
    8,623
    Cảm ơn
    4,349
    Được cảm ơn 26,372 lần trong 8,160 bài viết

    Chiêu thứ 13: Sao chép công thức giữ nguyên tham chiếu tương đối (giống như sao chép tham chiếu tuyệt đối)


    Trong Excel, 1 công thức có thể chứa những tham chiếu tương đối hoặc tuyệt đối. Khi 1 công thức có tham chiếu tương đối được sao chép, thì do tính chất, tham chiếu sẽ chạy theo đến chỗ mới. Chỉ có công thức có tham chiếu tuyệt đối mới giữ nguyên khi sao chép.

    Xin nhắc lại 1 tí:

    Khi bạn muốn công thức, dù cho copy đi đâu, cũng tham chiếu đến 1 ô hay 1 vùng cố định nào đó, bạn phải dùng tham chiếu tuyệt đối bằng cách thêm dấu $ vào tham chiếu thí dụ $A$1. Bạn cũng có thể chỉ thêm dấu $ vào tiêu chí dòng hoặc chỉ tiêu chí cột để chỉ cố định 1 chiều khi copy công thức.

    Nhưng đôi khi bạn muốn sao chép 1 công thức tham chiếu tương đối, giữ nguyên tham chiếu, nhưng lại không muốn chuyển thành tuyệt đối? Có nhiếu cách để làm.

    1. Tô đen công thức trong thanh công thức (formula bar) rồi copy, không copy ô, và tại ô đích cũng paste vào thanh công thức. Cách này dùng khi bạn chỉ sao chép 1 ô.

    2. Khi bạn muốn sao chép cả 1 khối ô, thì cùng Find and replace:
    - trước tiên, tô chọn khối muốn copy.
    - Nhấn Ctrl + H là phím tắt của Home – Find & Select – Replace (2007) hoặc Edit – Replace (2003).
    - trong ô find what: gõ dấu =
    - trong ô replace with: gõ dấu “và” (&), hoặc dấu nháy đơn (‘), hoặc bất cứ ký tự nào không dùng trong công thức
    - Nhấn Replace All
    - Để nguyên như vậy, nhấn ctrl + C
    - Qua vị trí cần chuyển đến, nhấn Ctrl + V
    - Để nguyên như vậy, chuyển ngược lại dấu & hoặc dấu nháy thành dấu = với công cụ find and replace.
    - Trở về vùng nguồn hồi nãy, cũng chuyển ngược dấu & thành dấu =

    Nghe thì nhiêu khê, nhưng vẫn nhanh hơn sửa từng tham chiếu tương đối thành tuyệt đối của cả 1 khối ô, rồi chuyển ngược lại.

    Ghi chú: Nếu chỉ 1 vài ô thì dùng cách 1 nhanh hơn. (Nói nhỏ: cách 1 là của Ptm0412, nếu bạn thích thì xin nhấn thank 1 phát)

    thay đổi nội dung bởi: BNTT, 23-11-08 lúc 08:51 PM
    Cheettit Old Man Thánh tắc ngô bất năng, ngô học bất yếm, nhi giáo bất quyện dã.

  8. Có 146 thành viên cảm ơn ptm0412 về bài viết này:

    abc 2009, anhchauvina, anzure, ao_xanh8884, ararechan, bichngoc83, bienthuy, binhan007, binhnvvn, BNTT, botngot, carrotchip, cadafi, chau ngoc, ChiHung84, cinderella_phan, cmc_bluesky, damsan1989, danhnghiep1102, daudaucantho, dinhngoccam, dinhsan, dttm2607, duchoanh, DUNGMINH, dungvtdung, dutrungkien, excel1111111, gakon_Office, haiduongxd06, haminhdung, hanh.mai900, Harry_Potter, heiniken6789, Hienxd.Ecopark, Hinl, hoabillynguyen, hoamattroicoi, hoangoanh3839, Hoàng Trọng Nghĩa, honeyphuc84, hong gam, hongphuongnam2009, hongthuy.tn86, HUE2007, huyhaithao, HUYNHLOAN78, iris1601, kean_bk, Khủng Long Ham Ăn, khiemacc, khoa186, khohieuqua, kieu manh tu, kirinski, knthanhkim, kulyvn, lalang, lam273, lan huynh, Laogia51, lelinh_qn, lemai8103, lemanhha0276, lepzin, lequocanh, levanduy, LikeIt, LKC2111, lnt, Luongtieu, luuhiep, LVM, mailan12, Maria2005, minhduc85, motngaybanvatoi, nam05spkt, namphuongson, namtpnd, nangsommai, ngmaipl, ngocnobita, ngocthegian, nguoi vo hinh, nguyenconghung, nguyenvuxdct, nhataet, nhâm quân, nhutmp, ntat, ongtrungducmx25, pencilstructure, phamvanvinh2015, phnam77, phuongly, phuong_tam74, phuyen89, pink_kittycat, Po_Pikachu, pt1501, quangteo14, quan_lana, quy65, quythankinh, rockaruouaem, rosalee, sihau, songvui, son_sajv, strepone, tacongthang, tanlinh, Tống Văn Đệ, TeThienDaiThanh, TEX, thanhlam591987, thanhsonhanhngo, thehungqnu, thohovietnam, thuypt, tien tri, tieuquyen, tinhcatn, Toán Từ, tocdai12a9, tom2, tranthongrtc5, tranvanthanh119, trinhanhphap, trongntk, TrungChinhs, truongngo198, TranThanhPhong, tuan73, utkhuyenhvtc, vangioipm, vanle33, vanminhdung, viethungbg056, vinhsonghinh, vuminh1601, vuongbeo84, vuongtltd, welcome2mylife, whatif

  9. #18
    Tham gia ngày
    11 2007
    Nơi Cư Ngụ
    Gò Vấp
    Bài gởi
    8,623
    Cảm ơn
    4,349
    Được cảm ơn 26,372 lần trong 8,160 bài viết

    Chiêu thứ 14: Gỡ bỏ những liên kết ma

    Bạn mở một bảng tính, nhận ngay một thông báo: "Update links", nhưng chẳng có link nào! Làm thế nào để Update một link khi nó không tồn tại?


    Những liên kết ngoài (external links) là những liên kết tham chiếu đến một bảng tính khác, chúng có thể nằm trong các công thức, trong các Name, hoặc trong các đối tượng được chèn vào (các đồ thị, các hình...). Khi những liên kết này bị "gãy", phần lớn lý do là do việc di chuyển hoặc sao chép một Sheet đến một bảng tính khác. Và khi đó, chúng trở thành những "liên kết ma", nghĩa là thấy đường link, nhưng không biết chúng được dẫn đến từ đâu. Sau đây là một vài cách giúp bạn xử lý những liên kết ma này.

    Đầu tiên, bạn cần xem lại liệu trong các công thức trong bảng tính của bạn có chứa bất kỳ một liên kết ngoài nào (mà không phải là liên kết ma) không. Nếu bạn không chắc rằng trong các công thức trong bảng tính của bạn có một liên kết ngoài, bạn hãy sử dụng công cụ tìm kiếm. Khi tìm ra rồi, bạn chỉ việc đơn giản là sửa lại cho nó chính xác, hoặc xóa hẳn chúng đi nếu không cần thiết.

    Bạn cũng có thể vào trang web này: http://www.microsoft.com/downloads/s...displaylang=vi, để tải về công cụ Delete Links Wizard, là một công cụ được thiết kế để tìm và xóa tất cả những liên kết như liên kết đến các Name, liên kết đến các Name ẩn, đến các đồ thị, các query, các đối tượng... Tuy nhiên, công cụ này không tìm ra được những liên kết ma.

    Cho dù bạn đã tin chắc rằng, không có một công thức nào có chứa liên kết, bạn vẫn cần phải chắc chắn là không có bất kỳ một liên kết ma nào đang núp ở đâu đó. Để làm việc này, tôi thường mở hộp thoại Name Manager, dò từng Name một, để chắc chắn rằng không có một Name nào chứa một tham chiếu đến một bảng tính khác.

    Với Excel 2003, thay vì nhấn vào từng cái tên để xem tham chiếu của nó trong khung Refers to, bạn có thể sử dụng cách này, nhanh hơn: Chọn menu Insert | Name | Paste, rồi trong hộp thoại Paste Name, nhấn vào nút Paste Link. Excel sẽ tạo ra một danh sách các Name có trong bảng tính, cùng những tham chiếu của nó, ngay trong bảng tính, để bạn kiểm tra.


    Excel 2007 thì dễ hơn, bạn có thể xem một lần tất cả các Name trong hộp thoại Define Name. Nhưng nếu bạn cũng muốn dán chúng ra ngoài bảng tính, bạn hãy chọn Formulas | Use in formula | Paste Name, rồi nhấn vào Paste List trong hộp thoại vừa xuất hiện.

    Tuy nhiên, với tất cả các phiên bản Excel, cách nhanh nhất để gọi hộp thoại Paste Name, là nhấn F3.
    Nếu có bất kỳ một Name nào tham chiếu đến một vùng ngoài bảng tính, có thể bạn sẽ thấy chúng có chứa ít nhất một đường link giống cái đã hiển thị trong hộp thoại cảnh báo Update Link khi bạn mở bảng tính. Và bạn hãy tự quyết định là sửa lại đường link cho đúng, hoặc xóa Name đó đi.

    Có một loại kiên kết khác nữa, nằm trong các biểu đồ. Khi bạn đã kiểm tra các công thức, các Name như tôi vừa trình bày, mà bảng tính của bạn vẫn đòi Update Link, bạn nên kiểm tra các biểu đồ (nếu có), nghĩa là kiểm tra vùng dữ liệu và nhãn X-asis của biểu đồ xem chúng có chứa một liên kết ngoài nào không. Nếu tìm thấy, hãy sửa lại đường link cho đúng.

    Các liên kết ngoài còn có thể núp trong các đối tượng (object) như là các Text box, các hình vẽ, v.v... Các đối tượng này có thể được liên kết đến một bảng tính khác. Cách dễ nhất để chọn nhanh các đối tượng, là bạn đứng tại bất kỳ một ô nào trong bảng tính, chọn Home | Find & Select | Go to Special [E2003: Edit | Go to... | Special], hoặc nhấn F5, rồi kích hoạt tùy chọn Object và nhấn OK. Các thao tác này sẽ chọn tất cả các đối tượng (object) có trong bảng tính. Bạn nên làm những việc sau đây trong 1 bản sao của file: Với tất cả các đối tượng đang được chọn, bạn có thể xóa, lưu bảng tính, đóng bảng tính, và mở ra lại xem thử có còn vấn đề gì nữa không.

    Cuối cùng, nơi mà bạn phải tìm là trong những sheet ẩn mà bạn đã tạo ra, dấu đi rồi quên phứt. Bạn hãy làm cho chúng hiện lên (2007: View Unhide, 2003: Format Sheet Unhide). Nếu mục Unhide này bị mờ đi, nghĩa là không có sheet ẩn. Nếu bạn vẫn còn nghi ngờ rằng co những sheet siêu ẩn, hãy đọc lại chiêu số 5 để tìm và buộc chúng hiện ra.

    Thế là bạn đã xử xong những link có thực mà bị gãy. Bây giờ đến những link ma. Mở fle bị lỗi lên, Chọn Data – Edit links (2007) hoặc Edit Links (2003). Đôi khi bạn chỉ cần nhấn chọn vào cái link ma, nhấn Change Source, và gán trở lại chính cái link đó. Nhưng thường thì bạn bị 1 thông báo lỗi rằng công thức nào đó bị lỗi, và không thực hiện được.

    Nếu bạn làm cách dễ như vậy không xong thì áp dụng cách này:
    Mở cả 2 file lên, file lỗi và file được link đến. Từ 1 ô bất kỳ của file lỗi, đánh dấu bằng (=), rồi qua file kia chọn cũng 1 ô bất kỳ, rồi Enter. Bạn đã tạo được 1 link thiệt, link ngon. Lưu cả 2 bảng tính lại, nhưng đừng đóng. Vào bảng tính lỗi, lại chọn Data – Edit links (2007) hoặc Edit Links (2003), Change source, sửa mọi tham chiếu của link ma vào bảng tính kia. Lưu lại lần nữa rồi xoá ô chứa link bạn mới tạo.

    Cách này thường là có hiệu quả trừ ma, bây giờ Excel nhận ra rằng bạn đã xoá link đến bảng tính còn lại. Nhưng nếu vẫn chưa trừ tận gốc và vẫn bị lỗi, bạn làm bước tiếp theo, nhưng nhớ là làm trên 1 bản sao của file lỗi.

    Cách này sẽ xoá vĩnh viễn dữ liệu của bạn, nên tốt nhất là tạo 1 bản dự phòng trước. Mở file lỗi lên, delete 1 sheet, lưu, đóng, rồi mở lên lại. Nếu không còn thông báo update link ma, thì 100% link ma nằm trong sheet đó. Nếu không, delete tiếp 1 sheet nữa để kiểm tra. Trước khi xoá sheet cuối cùng, phải insert 1 sheet trắng. Nhớ ghi lại những sheet chứa link ma.

    Tiếp theo, mở file backup hồi nãy lên, back up lần nữa, trở vào sheet lỗi hồi nãy bạn đã ghi nhớ. Bây giờ chọn từng vùng 10 x 10 ô dữ liệu, xoá bằng lệnh Clear – Clear All. Mà khoan, bạn backup lần 2 chưa? Nếu chưa thì back up đi nhé. Bây giờ lại lưu, đóng, mở lên lại xem còn lỗi không. Nếu còn, chọn vùng 10 x 10 ô tiếp theo, lại xoá, lưu, đóng, mở lên xem thông báo update link. Đến khi nào mà không còn thông báo link ma thì nghĩa là link ma nằm trong vùng 10 x 10 ô mới xoá. Bạn tập trung tìm kiếm trong khu vực này, từng ô một, sẽ thấy lòi ra con ma.

    Hy vọng bạn thực hiện thành công bằng chiêu này, để không bao giờ bực mình vì những con ma quấy nhiễu nữa.

    thay đổi nội dung bởi: BNTT, 23-11-08 lúc 08:51 PM
    Cheettit Old Man Thánh tắc ngô bất năng, ngô học bất yếm, nhi giáo bất quyện dã.


  10. #19
    Tham gia ngày
    11 2007
    Nơi Cư Ngụ
    Gò Vấp
    Bài gởi
    8,623
    Cảm ơn
    4,349
    Được cảm ơn 26,372 lần trong 8,160 bài viết

    Chiêu thứ 15: Giảm kích thước file Excel bị phình to bất thường
    Bạn có bao giờ có 1 file Excel bị tăng kích thước đến mức báo động chả biết nguyên nhân? Có nhiều lý do làm cho bảng tính bị phình lên về kích thước file và có những cách để loại trừ. Những bí quyết sau đây có thể giúp bạn nếu bạn có 1 file Excel có kích thước lớn bất thường.
    Có khi nào bạn bị bội thực do cố ăn mà chưa tiêu hoá kịp chưa? File Excel cũng thế. Nó bị phình ra là do bạn cố nhồi nhét đủ thứ vào bắt nó phải thực hiện, nó phải nuốt trọng chứ không kịp nhai, hậu quả là nó sẽ không làm việc như ý muốn.

    Tôi đã thử với 1 file Excel chuẩn với khá nhiều dữ liệu. Với dữ liệu thô, nó có kích thước 1,37 Mb. Rồi tôi cho vào 1 Pivot Table, sử dụng dữ liệu nguồn là 4 cột của vùng dữ liệu. Kích thước file tăng lên 2,4 Mb. Tôi thử thiết lập vài định dạng, và kích thước file lập tức bị nhân đôi.

    Một trong những nguyênnhân chủ yếu làm tăng kích thước file ở những file Excel trước 2007, khi chưa có khái niệm table, là các bạn cứ thế định dạng màu, cỡ font, màu chữ, đóng khung, . .. cho cả cột hoặc cho cả dòng, nếu không nói là cả bảng tính; chứ không chỉ định dạng cho khu vực chứa dữ liệu. Tôi từng thấy người hễ mở bảng tính mới lên, là lập tức Ctrl A, tô trắng toàn bộ, đóng khung toàn bộ. Không những thế họ còn xác định vùng dữ liệu nguồn cho biểu đồ, cho Pivot table, . . . là cả nguyên cột. Có khi thấy cái thanh cuộn dọc và ngang của Excel trở nên nhỏ tí tị, kéo hoài không thấy hết mà dữ liệu thì trống trơn.
    Để giải quyết bạn phải sửa, bỏ hết những định dạng thừa trong những dòng cột trống, bỏ cái thói quen xoá màu fill color bằng cách chọn fill trắng. Hãy chọn No Fill! Sau đó điều chỉnh dữ liệu nguồn của biểu đồ, của Pivot Table chỉ vừa với vùng có dữ liệu.

    Hãy làm như sau: trước hết hãy back up bảng tính lại.
    1. Gỡ bỏ những định dạng trên những dòng cột thừa:
    Bước đầu tiên là xác định bằng tay và mắt ô cuối cùng bên phải ở dưới, có chứa dữ liệu. Đừng có mà dùng phím ctrl+ mũi tên xuống hoặc ctrl + mũi tên qua phải, hoặc dùng Find - Select - Go To Special – Last Cells, cách này sẽ chỉ tới ô cuối cùng có định dạng, không phải ô cuối có dữ liệu. Khi đã xác định bằng mắt ô cuối chứa dữ liệu thì click chọn nguyên dòng kề dưới ô đó. Lúc này ới nhấn Ctrl + Shift + mũi tên xuống để chọn toàn bộ những dòng bên dưới vùng có dữ liệu. Sau đó dùng lệnh Clear – All để xoá vùng này.
    Tương tự, khi xác định ô cuối có chứa dữ liệu xong, nhấn chọn nguyên cột liền kề bên phải, nhấn tiếp Ctrl + Shift + mũi tên qua phải để chọn toàn bộ cột bên phải. Sau đó lại dùng lệnh Clear – All để xoá vùng này.

    Không nên dùng lệnh xoá dòng hoặc cột, vì có thể có những công thức tham chiếu đến chúng. Lưu bảng tính lại và thử xem kích thước file trong Property:
    2007: Office button ➝Prepare ➝Properties ➝Document Properties ➝Advanced Properties
    2003: File ➝Properties… ➝General

    2. Xoá Macro: Không phảo là xoá hẳn Macro, mà chỉ gỡ chúng ra ngoài file Excel:
    Có 1 cách nhanh, không gây hại đó là Export tất cả các modules và UserForms vào đĩa cứng. Nhấn Alt F11 vào cửa sổ VBA. nhấn chuột phải vào từng module, chọn remove module (tên môdule). Trong thông báo hiện ra nhấn Yes, và chọn 1 thư mục để lưu lại. làm tiếp cho từng cái Form. đừng quên cả những macro lưu trữ trong sheet hoặc workbook.

    Sau khi đã xoá xong, lưu bảng tính lại. Sau đó, cũng trong cửa sổ VBA, vào menu File – Import File và import hết những modue và form hồi nãy. Làm như vậy, ta vẫn có thể sử dụng form và Macro, nhưng mỗi cái như vậy sẽ tạo1 file text, và ngăn chặn những thứ linh tinh mà Macro mang theo.

    Một số ứng dụng trên Internet có thể làm công việc này, nhưng được biết rằng những tiện ích đó sẽ làm rối tung code và thậm chí làm tăng kích thước file. Tốt nhất trước khi ứng dụng 1 tiện ích nào, hãy backup file của bạn trước.

    3. Điều chỉnh vùng dữ liệu nguồn:
    Nếu sau khi làm những bước trên mà kích thước file của bạn không giảm được bao nhiêu, hãy kiểm tra dữ liệu nguồn của Pivot Table và Pivot Chart. Một vài người thường sử dụng nguyên cả cột dữ liệu để làm nguồn cho Pivot table, chỉ để phòng xa sau này có thêm dữ liệu cập nhật vào dù chỉ vài dòng. Nếu nhất thiết phải phòng xa như vậy, tốt hơn bạn dùng name động. Xem thêm tuyệt chiêu số 47.

    4. Làm sạch các sheet:
    Trong các sheet của bạn có thể tồn tại những định dạng thừa, những style, những autoshape thấy được và không thấy được (có khi lên đến hàng trăm hoặc hàng ngàn).

    a. Xoá object và autoshape thừa. Để tìm ra những autoshape hoặc object ẩn bạn phải vào option của Excel sửa lại như hình sau:
    2007:



    2003:



    Bạn có thể nhấn Ctrl + G, Special – Object để chọn 1 lúc nhiều object.



    b. Làm sạch những cái không nhìn thấy trong sheet:
    Trước tiên, bạn hãy backup bảng tính. Unhide toàn bộ sheet ẩn và cả sheet siêu ẩn. (xem thêm chiêu số 5). Bây giờ bạnthử xoá lần lượt từng sheet một (xoá hẳn), lưu lại, rồi vào Property xem lại kích thước. Nếu sau khi xoá sheet nào, mà kích thước file giảm đáng kể, thì sheet đó chứa nhiều rác (không kể dữ liệu). Vậy, khi đã xác định sheet chứa rác thì bạn phải làm gì?

    Bạn hãy mở file backup hồi nãy lên, tạo 1 sheet mới toanh, chọn vùng dữ liệu của sheet chứa rác, dùng lệnh Cut (Ctrl + X), chứ đừng copy, Paste vào sheet mới. Bằng cách cắt (Cut), Excel sẽ giữ nguyên tham chiếu cho bạn.
    Cuối cùng, delete sheet chứa rác.

    Hy vọng là sau này, các bạn sẽ biết cách xử lý những file có kích thước lớn lạ thường.

    thay đổi nội dung bởi: BNTT, 23-11-08 lúc 08:51 PM
    Cheettit Old Man Thánh tắc ngô bất năng, ngô học bất yếm, nhi giáo bất quyện dã.


  11. #20
    Tham gia ngày
    07 2007
    Nơi Cư Ngụ
    Dalat
    Bài gởi
    4,901
    Cảm ơn
    4,386
    Được cảm ơn 21,266 lần trong 3,973 bài viết

    Chiêu thứ 16: Cứu dữ liệu từ một bảng tính bị lỗi
    Khi bảng tính bị lỗi (corrupt), bị hư, nghĩa là bạn bị mất hết dữ liệu, mà nhiều khi sự mất mát này còn đau hơn là mất tiền. Chiêu này sẽ giúp bạn một vài cách để phục hồi lại phần nào dữ liệu trong những bảng tính bị lỗi.

    Một bảng tính đôi khi gặp phải những lỗi mà bạn chẳng hiểu vì lý do gì. Điều này có thể đem lại cho bạn những vấn đề trầm trọng, nhất là khi bạn bị hư một bảng tính quan trọng mà bạn chưa hề sao lưu dự phòng. Do đó, bài học đầu tiên là: Luôn luôn sao lưu bảng tính của bạn vào một nơi khác. Nhưng trong thực tế thì không phải ai cũng nhớ điều này, và, có thể là bảng tính của bạn sẽ bị hư trước khi bạn nghĩ đến việc sao lưu!

    Tuy nhiên, bạn đừng thất vọng quá, vì cho dù bảng tính của bạn bị lỗi, đôi khi bạn vẫn có thể mở được nó ra và có thể làm được cái gì đó...


    Khi bạn vẫn còn mở được bảng tính

    Khi bạn vẫn còn có thể mở được một bảng tính bị lỗi, thì trước khi làm bất cứ điều gì, bạn hãy sao lưu ngay ra một bản khác, nếu không, có thể bạn sẽ mất luôn nó. Vì nếu còn giữ được bản sao lưu, bạn có thể cầu cứu được một sự giúp đỡ chuyên nghiệp hơn, khi bạn không thể làm gì nữa.

    1. Bạn hãy mở cái bảng tính bị lỗi đó ra, với phiên bản Excel cao nhất có thể, và lại nhấn lưu thêm một lần nữa, tuy nhiên điều này thì không cần thiết nếu như bạn đang dùng Excel 2007.


    2
    . Nếu như bảng tính đã làm bằng cách trên đây không hoạt động, bạn hãy cố gắng mở lại lần nữa và lưu nó dưới dạng HTML (Single File Web Page) hoặc HTM (Web Page). Rồi đóng nó lại, và lại mở ra, nhưng lần này thì lưu lại với dạng bình thường (*.xls chẳng hạn).


    Khi lưu ở dạng HTML hoặc HTM, bảng tính của bạn sẽ bị mất những thứ sau đây:
    • Với những bảng tính tạo ra từ Excel 2007:
      - Những tính năng mới của Excel 2007
      - Các PivotTable và các biểu đồ (chúng vẫn được lưu lại, nhưng sẽ mất hết khi bạn mở ra và lưu lại với dạng bình thường của Excel)
      - Các VBA Project.

    • Với những bảng tính tạo ra từ những phiên bản trước Excel 2007 (Excel 2003, Excel XP,...):
      - Những định dạng số chưa dùng đến
      - Những style chưa dùng đến
      - Các thiết lập Data Consolidation
      - Các Seriano
      - Các công thức sử dụng ngôn ngữ tự nhiên, chúng sẽ được chuyển đổi thành các tham chiếu dãy bình thường
      - Các hàm tự tạo
      - Các định dạng gạch ngang chữ (strikethrough), chỉ số trên (superscript), chỉ số dưới (subscript)
      - Các thay đổi có thể hồi phục (bằng lệnh Undo)
      - Các thiết lập định dạng trang cho các biểu đồ đã được nhúng trong bảng tính
      - Các danh sách cài đặt cho các ListBox và ComboBox
      - Các loại định dạng có điều kiện (Conditional Formatting)

    Ngoài ra, các bảng tính được chia sẻ (shared workbook) trong những phiên bản trước Excel 2007 sẽ không còn chia sẻ được nữa. Với các biểu đồ, những thiết lập cho "Value (Y) axis crosses at category number" trên tab Scale trong hộp thoại Format Axis sẽ không được lưu, nếu như tùy chọn "Value (Y) axis crosses a maximum category" được chọn; những thiết lập "Vary colors by point" trong hộp thoại Format Data Series cũng không được lưu nếu như biểu đồ chứa nhiều hơn một Data Serie.


    3
    . Cuối cùng, nếu như bảng tính đó vẫn không hoạt động, hãy cố gắng mở lại bảng tính đó một lần nữa, và lần này thì lưu nó với dạng SYLK (loại tập tin có đuôi là *.slk - Symbolic Link). Nhưng hãy nhớ rằng, khi bạn lưu ở dạng này, thì chỉ có Sheet hiện hành (active sheet) được lưu, do đó, nếu trong bảng tính có nhiều Sheet, bạn hãy làm thêm vài lần, với mỗi lần một Sheet, nhớ đặt tên cho nó sao cho dễ nhận biết. Rồi lại mở cái bảng tính *.slk đó ra, và lưu lại với dạng bình thường (*.xls chẳng hạn).



    Khi bạn không còn mở được bảng tính

    1. Nếu như bảng tính bị hư của bạn không thể mở ra được nữa, bạn hãy thử dùng Microsoft Word để mở nó. Nghe thì có vẻ buồn cười, nhưng đôi khi, bạn có thể copy được một vài dữ liệu trong bảng tính này (dĩ nhiên tất cả những định dạng, công thức, v.v... thì mất hết).


    2
    . Bạn hãy mở một bảng tính mới, và tạo cho nó một Extenal Link (liên kết ngoài) đến bảng tính bị hư. Ví dụ:
    ='C:\Documents and Settings\BNTT\My Documents\[ChookSheet.xls]Sheet1'!A1
    Copy liên kết này vào những ô khác (số lượng bao nhiêu thì tùy thuộc vào trí nhớ của bạn về cái bảng tính đã bị hư, nó có bao nhiêu hàng, bao nhiêu cột...), và cũng làm tương tự cho những Sheet khác (nếu cần thiết). Nếu bạn không nhớ được bất kỳ tên Sheet nào trong bảng tính đã bị hư, bạn cứ tạo đại một Sheet với đường dẫn chính xác, Excel sẽ hiển thị tên của Sheet khi bạn nhấn Enter. Có thể bạn sẽ thấy được gì đó...


    3
    . Nếu hai cách trên không đem lại cho bạn điều gì, bạn hãy vào trang web Openoffice.org, download phiên bản miễn phí của bộ phần mềm này về. Ngoại trừ các tên trên menu và toolbar, Openoffice.org khá giống Excel, vì nó được tạo ra cùng một cấu trúc bảng tính với Excel (có đến 96% các công thức trong Excel có thể sử dụng trong bảng tính của Openoffice.org).

    Sau khi đã download phiên bản miễn phí của Openoffice.org, bạn cài đặt nó vào máy. Rồi dùng nó để mở bảng tính bị hư của bạn. Trong khá nhiều trường hợp, dữ liệu của bạn sẽ được phục hồi. Tuy nhiên, những VBA code thì không còn gì cả, vì các VBA code của Excel không tương thích với Openoffice.org.


    4
    . Nếu số bạn quá đen, không thể dùng Openoffice.org để cứu dữ liệu, vẫn còn một cách nữa, nhưng bạn phải mất tiền. Một trong những chương trình có thể phục hồi khá tốt những bảng tính bị hư là Corrupt File Recovery. Bạn hãy tải và cài đặt phần mềm này, chạy chương trình ExcelFix, nhấn Select File, chọn bảng tính bị lỗi, và nhấn Diagnose để phục hồi. Bạn sẽ thấy thành quả của mình, và có thể lưu lại bảng tính, nếu như bạn đã trả tiền bản quyền, còn nếu chưa trả tiền bản quyền, thì bạn chỉ có thể xem chứ không thể lưu lại.

    thay đổi nội dung bởi: BNTT, 23-11-08 lúc 08:52 PM


Trang 2/9 đầuđầu 1 2 3 4 5 6 ... cuốicuối

Thông tin về chủ đề này

Users Browsing this Thread

Hiện có 11 người đang xem đề tài này. (0 thành viên và 11 khách)

Đề tài tương tự

  1. Mở MS Excel bị lỗi "Runtime error"13" Type mismatch"
    Viết bởi nvloc1957 trong chuyên mục Hỏi đáp những vấn đề khác
    Trả lời: 8
    Bài mới gởi: 26-01-14, 01:45 PM
  2. Nhờ A/C giúp code "tham chiếu" và "ghi lại"
    Viết bởi timkiem_abc trong chuyên mục Lập Trình với Excel
    Trả lời: 4
    Bài mới gởi: 11-04-11, 07:04 PM
  3. Ấn phẩm "Những tuyệt chiêu về Excel"
    Viết bởi longlt08 trong chuyên mục Học tập Offline
    Trả lời: 5
    Bài mới gởi: 18-01-11, 11:36 PM
  4. Cần hỗ trợ giải quyết lỗi trong hàm "Nội suy 2 chiều"
    Viết bởi salem_hp trong chuyên mục Hỏi đáp về VBA - Macro
    Trả lời: 2
    Bài mới gởi: 03-01-09, 01:43 PM
  5. Hỏi về "bài Lập trình "VBD" trong Excel " nên post ở box nào?
    Viết bởi TrungChinhs trong chuyên mục Những góp ý với diễn đàn
    Trả lời: 1
    Bài mới gởi: 22-11-08, 08:38 PM

Bookmarks

Bookmarks

Quyền Sử Dụng Ở Diễn Ðàn

  • Bạn không thể đăng đề tài mới
  • Bạn không thể đăng trả lời
  • Bạn không thể đăng file đính kèm.
  • Bạn không thể sửa bài viết.
  •  

Mudim v0.8 Tắt VNI Telex Viqr Tổng hợp
Chính tả Bỏ dấu kiểu mới  [Bật/Tắt (F9)]