[Hướng dẫn] Một ngày một "tuyệt chiêu" trong Excel

[jpow_vn]

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 vừa từng làm ra (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ự làm ra (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ự làm ra (tạo) ra một thanh công cụ có chứa những nút dùng để hỗ trợ chuyện 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 vừa có lúc bạn muốn điều tui 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 bất phải là bạn, mà là người khác, thì bất có gì bỡ ngỡ với thanh công cụ lạ hoắc; hay 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, bất có gì khó. Bạn chỉ cần chèn một đoạn cipher đơn giản vào trong "Private module" của bảng tính.

Trước hết, bạn hãy làm ra (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 chuyện 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 card 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 card File rồi chọn View Code

Rồi, nhập vào hai đoạn cipher 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 vừa đặt cho thanh công cụ tự làm ra (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 cipher ở 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 cipher trên. Hãy bảo đảm là bạn đang chọn bảng tính có chứa cipher (tốt nhất là bạn đóng cái Book2 đi). Anchorage 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 Cipher (hay nhấn đúp vào tên Sheet đó cũng được). Ở đây tui giả sử bạn chọn Sheet1.

Nhập vào hai đoạn cipher 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 để anchorage lại Excel. Bây giờ, bạn hãy dùng chuột hay 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 vừa có sẵn những đoạn cipher 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.

Trích:

You must be registered for see links

mã nguồn: giải pháp Excel (GPE)

 

[Arthur]

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 cú cú cú cú cú cú đối)

Trong Excel, 1 công thức có thể chứa những tham chiếu tương đối hay tuyệt cú cú cú cú cú cú đố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 cú cú cú cú cú cú đố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 archetype đ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 cú cú cú cú cú cú đố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 hay chỉ tiêu chí cột để chỉ cố định 1 chiều khi archetype 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 bất muốn chuyển thành tuyệt cú cú cú cú cú cú đố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, bất archetype ô, và tại ô đích cũng adhesive 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 Acquisition 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 – Acquisition & Select – Alter (2007) hay Edit – Alter (2003).

- trong ô acquisition what: gõ dấu =

- trong ô alter with: gõ dấu “và” (&), hay dấu nháy đơn (‘), hay bất cứ ký tự nào bất dùng trong công thức

- Nhấn Alter 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 & hay dấu nháy thành dấu = với công cụ acquisition 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 cú cú cú cú cú cú đố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 acknowledge 1 phát)

mã nguồn: giải pháp Excel (GPE)

 

[Kenjiro]

Thấy thì cũng hay thật và cũng rất bổ ích nữa nhưng mà chưa đoc hết vừa muốn ngủ rồi.... Bạn muốn chỉ dẫn như vậy thì bạn nên giới thiệu đôi chút rồi làm ra (tạo) đường dẫn down về chứ bất ai để thời (gian) gian cả buổi học những thứ của bạn đâu. Rút kinh nghiệm nhé!

 

[Beanon]

Trích:

cua_dong

You must be registered for see links

Thấy thì cũng hay thật và cũng rất bổ ích nữa nhưng mà chưa đoc hết vừa muốn ngủ rồi.... Bạn muốn chỉ dẫn như vậy thì bạn nên giới thiệu đôi chút rồi làm ra (tạo) đường dẫn down về chứ bất ai để thời (gian) gian cả buổi học những thứ của bạn đâu. Rút kinh nghiệm nhé!

cái đó mình cũng nghĩ rồi. Nhưng mình muốn column từng chiu 1 để có những ng muốn xem phần nào thì xem. Sau đó mình sẽ column tổng thể cho ai đó muốn down về.

 

[suzuki_vn]

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 để Amend một link khi nó bất 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, hay 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 chuyện di chuyển hay sao chép một Area đế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 bất 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à bất phải là liên kết ma) không. Nếu bạn bất 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ỉ chuyện đơn giản là sửa lại cho nó chính xác, hay xóa hẳn chúng đi nếu bất cần thiết.

Bạn cũng có thể vào trang web này:

You must be registered for see links

, để tải về công cụ Annul 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 bất tìm ra được những liên kết ma.

Cho dù bạn vừa tin chắc rằng, bất có một công thức nào có chứa liên kết, bạn vẫn nên phải chắc chắn là bất có bất kỳ một liên kết ma nào đang núp ở đâu đó. Để làm chuyện này, tui thường mở hộp thoại Name Manager, dò từng Name một, để chắc chắn rằng bất 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 card Admit | Name | Paste, rồi trong hộp thoại Paste Name, nhấn vào nút Paste Link. Excel sẽ làm ra (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 blueprint | 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 tương tự cái vừa hiển thị trong hộp thoại thông báo Amend 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, hay 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 vừa kiểm tra các công thức, các Name như tui vừa trình bày, mà bảng tính của bạn vẫn đòi Amend 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)coi như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], hay nhấn F5, rồi kích hoạt tùy chọn Article 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 chuyện 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 area ẩn mà bạn vừa 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à bất có area ẩn. Nếu bạn vẫn còn nghi ngờ rằng co những area 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 vừa 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) hay 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à bất thực hiện được.

Nếu bạn làm cách dễ như vậy bất xong thì áp dụng cách này:

Mở cả 2 book lên, book lỗi và book được link đến. Từ 1 ô bất kỳ của book lỗi, đánh dấu bằng (=), rồi qua book kia chọn cũng 1 ô bất kỳ, rồi Enter. Bạn vừa 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) hay Edit Links (2003), Change source, sửa tất cả 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 vừa xoá link đến bảng tính còn lại. Nhưng nếu vẫn chưa trừ hết 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 book 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à làm ra (tạo) 1 bản dựphòng chốngtrước. Mở book lỗi lên, annul 1 sheet, lưu, đóng, rồi mở lên lại. Nếu bất còn thông báo amend link ma, thì 100% link ma nằm trong area đó. Nếu không, annul tiếp 1 area nữa để kiểm tra. Trước khi xoá area cuối cùng, phải admit 1 area trắng. Nhớ ghi lại những area chứa link ma.

Tiếp theo, mở book advancement hồi nãy lên, aback up lần nữa, trở vào area lỗi hồi nãy bạn vừa 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 advancement lần 2 chưa? Nếu chưa thì aback 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 amend link. Đến khi nào mà bất 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.

mã nguồn: giải pháp Excel (GPE)

 

[Caine]

bạn thật là kỹ lưỡng nhưng minh nghỉ.... người ta ít khi nào chuyên sâu vào Excel lam! Chi co những người chuyên ngành thôi. Nhưng mình nghĩ vừa làm một chuyện có ích vì nếu có người cần thì vừa có tài liệu chỉ dẫn của bạn rồi. Mà bạn có chuyên sâu vào Word không? Nếu mình cần bạn có vui lòng giúp mình không?

 

[123_letsvi]

Trích:

cua_dong

You must be registered for see links

bạn thật là kỹ lưỡng nhưng minh nghỉ.... người ta ít khi nào chuyên sâu vào Excel lam! Chi co những người chuyên ngành thôi. Nhưng mình nghĩ vừa làm một chuyện có ích vì nếu có người cần thì vừa có tài liệu chỉ dẫn của bạn rồi. Mà bạn có chuyên sâu vào Word không? Nếu mình cần bạn có vui lòng giúp mình không?

ngành tớ học chuyên về phần mềm - lập trình bạn ak.

Word và Excel thì chủ yếu tụi tớ phải tự mò mẫn thui.

nhưng nếu giúp dc mình cũng sẽ giúp!

 

[Piero]

Mình rất hay thường gặp trường hợp liên kết ma này.Nhưng nếu bảng tính do mình lập ra thì tìm dễ.còn bảng tính của người khác nhờ mình thì tìm hơi bị mệt.

 

[Kirkland]

Chiêu thứ 15: Giảm kích thước book Excel bị phình to bất thường

Bạn có bao giờ có 1 book 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 book 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 book 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? Book 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ứ bất kịp nhai, sau quả là nó sẽ bất làm chuyện như ý muốn.

tui vừa thử với 1 book 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 tui 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 book tăng lên 2,4 Mb. tui thử thiết lập vài định dạng, và kích thước book 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 book ở những book 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 hay cho cả dòng, nếu bất nói là cả bảng tính; chứ bất chỉ định dạng cho khu vực chứa dữ liệu. tui 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 bất 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 ample blush bằng cách chọn ample 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 aback 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 hay ctrl + mũi tên qua phải, hay 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, bất phải ô cuối có dữ liệu. Khi vừa xác định bằng mắt ô cuối chứa dữ liệu thì bang 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 hay 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 book trong Property:

2007: Office button ➝Prepare ➝Properties ➝Document Properties ➝Advanced Properties

2003: Book ➝Properties… ➝General

2. Xoá Macro: Không phảo là xoá hẳn Macro, mà chỉ gỡ chúng ra ngoài book Excel:

Có 1 cách nhanh, bất 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 abolish bore (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 area hay workbook.

Sau khi vừa xoá xong, lưu bảng tính lại. Sau đó, cũng trong cửa sổ VBA, vào card Book – Acceptation Book và acceptation hết những modue và anatomy hồi nãy. Làm như vậy, ta vẫn có thể sử dụng anatomy và Macro, nhưng mỗi cái như vậy sẽ tạo1 book 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 chuyện này, nhưng được biết rằng những tiện ích đó sẽ làm rối tung cipher 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 advancement book 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 book của bạn bất 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 chốngxa 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ảiphòng chốngxa như vậy, tốt hơn bạn dùng name động. Xem thêm tuyệt cú chiêu số 47.

4. Làm sạch các sheet:

Trong các area của bạn có thể còn tại những định dạng thừa, những style, những autoshape thấy được và bất thấy được (có khi lên đến hàng trăm hay hàng ngàn).

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

2007:

2003:

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

b. Làm sạch những cái bất nhìn thấy trong sheet:

Trước tiên, bạn hãy advancement bảng tính. Unhide toàn bộ area ẩn và cả area siêu ẩn. (xem thêm chiêu số 5). Bây giờ bạnthử xoá lần lượt từng area 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á area nào, mà kích thước book giảm đáng kể, thì area đó chứa nhiều rác (không kể dữ liệu). Vậy, khi vừa xác định area chứa rác thì bạn phải làm gì?

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

Cuối cùng, annul area chứa rác.

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

mã nguồn: giải pháp Excel (GPE)

 

[Amott]

Trích:

congainhahopham

You must be registered for see links

ngành tớ học chuyên về phần mềm - lập trình bạn ak.

Word và Excel thì chủ yếu tụi tớ phải tự mò mẫn thui.

nhưng nếu giúp dc mình cũng sẽ giúp!

Vậy à, bạn học lập trình thì mình lại càng muốn nhờ bạn nữa nè! bạn có học chương trình .NET không? Mình đang có rất nhiều thế bí vì bài tập được giao đây. Bạn giúp mình được chứ!

 

[Briggebam]

Trích:

cua_dong

You must be registered for see links

Vậy à, bạn học lập trình thì mình lại càng muốn nhờ bạn nữa nè! bạn có học chương trình .NET không? Mình đang có rất nhiều thế bí vì bài tập được giao đây. Bạn giúp mình được chứ!

Ý bạn nói Asp.NET phải không?

Mình gà CNTT lắm. câu nào mà khó chắc mình bó dò....

aK! Mà bạn cứ muốn hỏi gì thì đăng lên 4r ý, có gì tất cả người cùng sẻ chia như thế sẽ có nhìu ý kiến hơn cho bạn đó.

 

[Cedrik]

Trích:

congainhahopham

You must be registered for see links

Ý bạn nói Asp.NET phải không?

Mình gà CNTT lắm. câu nào mà khó chắc mình bó dò....

aK! Mà bạn cứ muốn hỏi gì thì đăng lên 4r ý, có gì tất cả người cùng sẻ chia như thế sẽ có nhìu ý kiến hơn cho bạn đó.

Chọc bạn cho vui thôi. Mình bất đi sâu về .NET lắm. Cái mình đi sâu là C++ kia nhưng đôi lúc cũng... hụt chân. Kiến thức đúng là không bờ bến thật nếu có dịp mình cùng chia sẽ nha.

 

[conginuakhongem]

hix không bít đến bao giờ mình mới có thể học hết những trương trình này được nhỉ ước ao khao khát

 

[Yong]

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ì bất 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 chuyện 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 nghề hơn, khi bạn bất 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ì bất cần thiết nếu như bạn đang dùng Excel 2007.

2. Nếu như bảng tính vừa làm bằng cách trên đây bất 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) hay 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 hay 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 làm ra (tạo) ra từ Excel 2007:

- Những chức 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 làm ra (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 appearance 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 đồ vừa đượ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 sẻ chia (shared workbook) trong những phiên bản trước Excel 2007 sẽ bất còn sẻ chia được nữa. Với các biểu đồ, những thiết lập cho "Value (Y) arbor crosses at class number" trên tab Scale trong hộp thoại Format Arbor sẽ bất được lưu, nếu như tùy chọn "Value (Y) arbor crosses a best category" được chọn; những thiết lập "Vary colors by point" trong hộp thoại Format Data Series cũng bất đượ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 bất 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ó Area 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 bất còn mở được bảng tính

1. Nếu như bảng tính bị hư của bạn bất 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ể archetype đượ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à làm ra (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

Archetype 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 vừa 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 Area khác (nếu cần thiết). Nếu bạn bất nhớ được bất kỳ tên Area nào trong bảng tính vừa bị hư, bạn cứ làm ra (tạo) đại một Area với đường dẫn chính xác, Excel sẽ hiển thị tên của Area khi bạn nhấn Enter. Có thể bạn sẽ thấy được gì đó...

3. Nếu hai cách trên bất đ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 card và toolbar, Openoffice.org khá tương tự Excel, vì nó được làm ra (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 vừa 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 cipher thì bất còn gì cả, vì các VBA cipher của Excel bất tương thích với Openoffice.org.

4. Nếu số bạn quá đen, bất 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à Base 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 vừa trả trước bản quyền, còn nếu chưa trả trước bản quyền, thì bạn chỉ có thể xem chứ bất thể lưu lại.

mã nguồn: giải pháp Excel (GPE)

 

[Federico]

Chiêu thứ 17: Sử dụng Data-Validation khi danh sách nguồn nằm trong một Area khác

Sử dụng Data-Validation là một cách dễ nhất để áp dụng một quy tắc nhập liệu cho một dãy dữ liệu. Theo mặc định, Excel chỉ cho phép Data-Validation sử dụng những danh sách nguồn nằm trong cùng một Area với dãy dữ liệu sẽ được áp dụng quy tắc này. Tuy nhiên, vẫn có cách để lách khỏi chuyện đó.

Chiêu này sẽ giúp bạn làm cho Data-Validation có thể sử dụng những danh sách nguồn nằm trong một Area khác. Cách thứ nhất là lợi dụng chính chuyện đặt tên cho một dãy của Excel, cách thứ hai là sử dụng một hàm để gọi ra danh sách đó.

Cách 1: Sử dụng Name cho dãy nguồn

Có lẽ cách nhanh nhất và dễ nhất để vượt qua rào cản Data-Validation của Excel là đặt tên cho dãy mà bạn sẽ dùng làm quy tắc nhập liệu. Để biết cách đặt tên cho dãy, bạn xem ở loạt bài này:

Sử dụng tên cho dãy:

Code:

http://www.mediafire.com/download.php?2o2dkmmlndn

Giả sử bạn vừa đặt tên cho dãy sẽ dùng làm quy tắc nhập liệu là MyRange. Bạn chọn ô (hay dãy) trong bất kỳ Area nào mà bạn muốn có một danh sách xổ ra để nhập liệu, rồi trong card Data trên Ribbon, bạn chọn Data Tools | Data Validation [E2003: Data | Validation]. Chọn List trong danh sách các Allow, và trong khung Source, bạn nhập vào =MyRange. Nhấn OK. Bởi vì bạn vừa sử dụng một Name để làm List, nên bạn có thể áp dụng Data-Validation này cho bất kỳ Area nào.

Cách 2: Sử dụng hàm INDIRECT

Hàm INDIRECT() cho phép bạn tham chiếu đến ô chứa dữ liệu argument thay mặt cho một đất chỉ ô. Và rồi bạn có thể sử dụng ô đó như môt tham chiếu cục bộ, cho dù nó tham chiếu đến dữ liệu trong một Area khác. Bạn có thể sử dụng chức năng này để tham chiếu đến nơi chứa dãy mà bạn sẽ dùng làm danh sách nguồn cho quy tắc Data-Validation.

Giả sử, dãy chứa danh sách nguồn này nằm ở Sheet1, trong dãy $A$1:$A$8. Để làm ra (tạo) một Dala-Validation, bạn cũng làm những bước như tui đã nói ở cách 1, nhưng thay vì gõ tên dãy vào trong Source, thì bạn nhập vào đó công thức: =INDIRECT("Sheet1!$A$1:$A$8"). Hãy chắc chắn rằng tùy chọn In-cell drop-down đang được kích hoạt, và nhấn OK.

Nếu tên Area của bạn có chứa khoảng trắng, hay có dấu tiếng Việt, bạn phải đặt tên Area trong một cặp nháy đơn ('). Ví dụ, giả sử tên Area chứa danh sách nguồn là Area 1 (chứ bất phải Sheet1), thì bạn sửa công thức trên lại như sau: =INDIRECT("'Sheet 1'!$A$1:$A$8"). Chỗ khác nhau so với công thức hồi nãy là có thêm một dấu nhấy đơn (') sau dấu nháy kép ("), và một dấu nháy đơn (') nữa trước dấu chấm than (!).

Xin mở một ngoặc đơn: Nếu như có thể được, khi gặp những tham chiếu đến tên Sheet, bạn nên tập thói quen luôn luôn bỏ nó vào trong cặp dấu nháy đơn. Điều này, tuy chẳng có tác dụng gì với những tên area như Sheet1, DMHH... nhưng nó sẽ giúp bạn bất bao giờ gặp lỗi, khi bạn hay đặt tên Area có khoảng trắng, hay là có bỏ dấu tiếng Việt...

Ưu điểm và Khuyết điểm của cả hai cách vừa nêu trên

Đặt tên cho dãy, và dùng hàm INDIRECT, đều có cái tiện lợi và cả cái bất tiện.

Tiện lợi của chuyện đặt tên cho dãy, là chuyện bạn thay đổi tên Area chẳng có ảnh hưởng gì đến Data-Validation. Và đó chính là cái bất tiện của chuyện dùng INDIRECT, khi bạn đổi tên Sheet, tên mới sẽ bất tự động cập nhật trong công thức dùng INDIRECT, cho nên nếu vẫn muốn dùng công thức này, bạn phải mở Data-Validation ra và sửa lại tên Area trong công thức.

Tiện lợi của chuyện dùng INDIRECT, là dãy dùng làm danh sách nguồn của bạn luôn luôn nằm yên chỗ vừa chọn (A1:A8 trong ví dụ trên chẳng hạn). Còn nếu bạn dùng Name, mà bạn lỡ tay xóa mất vài hàng (hay cột) ngay chỗ chứa Name, thì bạn phải điều chỉnh lại cho đúng...

mã nguồn: giải pháp Excel (GPE)

 

[quevo]

Chiêu số 18: Điều khiển Conditional Formating bằng checkbox.

Mặc dù Conditional Formating là 1 trong những chiêu mạnh của Excel, nhưng muốn bật hay tắt nó bằng award hay card thì khá bực bội. Bây giờ ta biến hoá bằng cách điều khiển bằng 1 checkbox tương tự như 1 công tắc (hay 1 cái toggle Button càng tương tự hơn).

Conditional Formating có từ đời Excel 97, gán định dạng cho những ô nào thoả 1 số điều kiện nào đó. Điều kiện có thể là 1 điều kiện về giá trị, nhưng ta có thể tuỳ biến nhiều hơn khi dùng điều kiện là công thức, phụ thuộc vào đó ta có thể thay đổi định dạng cho những ô này, khi có sự thay đổi giá trị của ô khác.

1. Dùng 1 Checkbox hay 1 Toggle Button để xem và ẩn dữ liệu:

Bạn muốn một vùng dữ liệu nào đó chỉ hiện ra lúc cần xem, xem xong thì biến đi cho rảnh. Trước tiên bạn phải gán lên area 1 Checkbox hay 1 Toggle Button. Trong Excel 2007, vào tab Developer, nhấn Insert trong Controls - chọn Checkbox hay Toggle Button trong Ascendancy Toolbox, trong Excel 2003 chọn trong appearance – Toolbar – Ascendancy Toolbox, vẽ lên area 1 cái. Trong hình, tui làm thử 2 cái.

Nhấn vào nút design, bang chọn cái ascendancy bạn vừa vẽ, nhấn thêm nút Property. Trong cửa sổ Property, sửa dòng Caption thành View/ Hide, sửa dòng Linked Cell thành $C$2. (cả 2007 và 2003 như nhau, cả checkbox và Toggle Button như nhau).

Bây giờ khi bạn bang chọn checkbox hay nhấn nút Toggle, ô C2 sẽ lần lượt có các giá trị TRUE và FALSE.

Bây giờ giả sử vùng dữ liệu của bạn gồm 4 fields, trong đó bạn chỉ muốn 3 fields hiện thường xuyên, còn acreage thứ 4 thì khi nào cần mới hiện ra để xem, bất cần thì dấu đi. Bạn đánh dấu chọn vùng chứa acreage 4, trong 2007 bạn vào tab Home, Conditional Formating, New Rule, chọn tiếp “use a blueprint to actuate which beef to format”, trong 2003 là Fornat - Conditional Formating - chọn tiếp “Formula is”. Trong ô kế bến, bạn gõ: = $C$2=FALSE.

Nhấn vào nút Format, định dạng chantry chữ màu trắng. Nhấn OK và OK. Nhấn nút architecture 1 lần nữa để thoát ta khõi chế độ architecture Mode. Và nhấn nút toggle hay bang chọn cái checkbox xem kết quả.

Nếu bạn bất thích thì định dạng ô C2 chữ trắng luôn, để khỏi thấy chữ TRUE, FALSE hiện lên.

mã nguồn: giải pháp Excel (GPE)

(Còn tiếp)