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

Code:

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

Những "tuyệt chiêu" trong Excel
* Lời nói đầu

Trong quá trình thực hiện một bảng tính, chúng ta có thể mắc phải không số lỗi, hay có thể mắc phải những sai lầm ngớ ngẩn, và thường bất để ý tới những cái có thể giúp chúng ta xử lý bảng tính nhanh hơn, gọn gàng hơn... Xin hân hạnh giới thiệu đến các bạn cuốn sách "Một ngày một tuyệt cú chiêu", lược dịch từ cuốn Excel Hacks, của hai tác giả David và Raina Hawley. Những "tuyệt chiêu" này sẽ giúp bạn tránh được những lỗi, và làm chuyện với bảng tính một cách có hiệu quả hơn.

Trước khi nói đến các "tuyệt chiêu" của Excel, xin nói sơ qua một chút về một số thủ thuật và quy tắc cơ bản, những điều sẽ giúp các bạn thực hiện những "tuyệt chiêu" được nói đến trong các phần sau cách dễ dàng hơn. Excel là một phần mềm bảng tính rất mạnh, và bạn có thể làm được những điều bất thể tin nổi với nó. Nhưng bất may, có nhiều người thiết kế bảng tính của mình với quá nhiều sự "lo xa", cẩn thận quá mức cần thiết, và làm cho nó trở nên phức tạp hơn là nó đáng bị như vậy. Phần mở đầu này sẽ mách cho bạn một số thủ thuật và quy tắc mà nếu bạn làm theo, bạn sẽ thấy chúng rất hiệu quả.

Xin nói thêm, tất cả các "chiêu" được trình bày sau đây, (nhiều) đa phần là viết cho Excel 2007. Nếu nó cũng có thể áp dụng cho các phiên bản trước, chúng tui sẽ chú thích ở ngay bên cạnh: (Với Excel 2003 trở về trước, thì...)


Quy tắc 80/20

Có lẽ chuyện quan trọng nhất khi thực hiện một bảng tính là dành một thời (gian) gian để nghiên cứu, xem xét cách bố trí dữ liệu, tính trước những chuyện mình cần làm. Và đừng bao giờ nghĩ rằng sau khi trả thành, tui sẽ bất nên phải thêm vào đó các dữ liệu hay các công thức khác, vì bạn sẽ luôn làm ngược lại. Điều này có nghĩa rằng, bạn nên dành khoảng 80% thời (gian) gian để lên kế hoạch cho một bảng tính và khoảng 20% thời (gian) gian để thực hiện bảng tính đó.

Cho dù rằng, chuyện này có vẻ bất cần thiết với những bảng tính nhỏ, nhưng bảo đảm rằng, nếu bạn quan tâm đến điều tui vừa nói ở trên, thì về lâu về dài, bạn sẽ tránh được những cực nhọc khi phải cứ phải sửa đi sửa lại bảng tính của mình, và dễ dàng thực hiện được những bảng tính lớn sau khi vừa dành cho nó một thời (gian) gian để lên kế hoạch thực hiện. Nên nhớ rằng, các bảng tính phải luôn mang lại cho người dùng những thông tin chính xác, chứ bất phải là thông tin chỉ chính xác trong một trường hợp nào đó.


Những mẹo nhỏ về cấu trúc một bảng tính

Một trong những lỗi mà chúng ta hay mắc phải khi làm ra (tạo) một bảng tính, là chúng ta bất thiết lập và trình bày dữ liệu "theo cái cách mà Excel và các chức năng của nó mong đợi". Sau đây là một số lỗi phổ biến chúng ta hay mắc phải khi thiết lập một bảng tính:

* Bố trí dữ liệu trong nhiều bảng tính (WorkBook) khác nhau
* Bố trí dữ liệu trong nhiều trang tính (WorkSheet) khác nhau
* Bố trí dữ liệu trong nhiều bảng (Table) khác nhau
* Có những hàng trống và cột trống trong khối dữ liệu (database)
* Bỏ trống những ô có dữ liệu tương tự nhau (cùng chung một ngày, cùng chung một đơn vị tính, v.v...)

Ba điểm đầu tiên trên đây chỉ muốn nói đến một điều: Bạn nên luôn luôn cố gắng để giữ các mối liên quan giữa các dữ liệu được liên tục trong cùng một bảng dữ liệu. tui vừa thấy rất nhiều các bảng tính bất được trình bày theo cái quy tắc rất đơn giản này, và chính vì vậy, các bảng tính đó bất thể hết dụng được tối (nhiều) đa các chức năng mạnh mẽ của Excel như PivotTable, SubTotal... Bạn chỉ có thể sử dụng các chức năng này khi bạn gom hết dữ liệu của bạn vào trong một bảng thật đơn giản.

Không phải ngẫu nhiên mà Excel có 1.048.567 hàng (65.536 hàng trong Excel 2003 trở về trước) mà lại chỉ có 16.384 cột (256 cột trong Excel 2003 trở về trước). Điều này có ý nói rằng, bạn nên thiết lập dữ liệu của mình với các tiêu đề cột nằm ở hàng trên cùng, và các dữ liệu có liên quan thì nằm liên tục ở bên dưới tiêu đề của nó. Nếu như bạn có những dữ liệu được lặp lại hai lần hay nhiều lần trong các hàng của cùng một cột (các ngày tháng, các loại đơn vị tính chẳng hạn), bạn hãy chống lại sự cám dỗ bỏ trống các ô đó.

Hãy cố gắng sắp xếp (sort) dữ liệu của bạn bất cứ khi nào có thể. Excel có rất nhiều những công cụ để tìm kiếm và tham chiếu công thức, và một số bất nhỏ trong đó, đòi hỏi dữ liệu phải được sắp xếp theo một thứ tự hợp lý. Việc phân loại cũng sẽ giúp ích đáng kể cho tốc độ xử lý của một số các hàm.



---------- Bài viết vừa được nhập tự động bởi hệ thống ----------

Những mẹo nhỏ khi định dạng

Ngoài chuyện thiết kế cấu trúc của bảng tính cho hợp lý, chuyện định dạng cho nó cũng là một vấn đề cần bàn đến. Mặc dù một bảng tính nên được định dạng sao cho dễ đọc và dễ theo dõi, nhưng chúng ta ít khi nghĩ đến chuyện sử dụng thời (gian) gian cho có hiệu quả. Hãy luôn luôn định dạng thật đơn giản. Rất nhiều người lãng phí thời (gian) gian vào chuyện định dạng một bảng tính mặc dù bất nhất thiết phải làm như vậy, và chính điều này làm ảnh hưởng đến hiệu quả công việc. Việc thường xuyên áp dụng những định dạng phức tạp cho bảng tính còn làm cho nó tăng kích thước, và cho dù bảng tính của bạn có thể trông tương tự như một tác phẩm nghệ thuật, nhưng nó lại có thể là một nỗi sợ hãi cho người khác. Một trong những kiểu phối màu tốt nhất mà bạn nên áp dụng cho một bảng tính, là màu đen, màu trắng và màu xám.

Một gợi ý nữa, là nên bỏ trống vài hàng đầu tiên ở trên cùng (ít nhất là 3 hàng). Những hàng này sẽ được dùng cho những chức năng nâng cao hơn về sau, chẳng hạn như Advanced Filter, hay dùng làm vùng điều kiện cho các công thức xử lý dữ liệu (DSUM, DMAX, v.v...)

Cũng đừng quan tâm đến chuyện canh lề cho dữ liệu. Theo mặc định, Excel canh phải cho những dữ liệu kiểu số, và canh trái cho những dữ liệu kiểu text. Và đó là điều tốt nhất rồi, đừng thay đổi nó. Nếu bạn thay đổi kiểu canh lề, bạn sẽ có thể bất xác định được ngay tức khắc rằng đó là dữ liệu kiểu số hay dữ liệu kiểu text; và nó còn có thể gây nhầm lẫn cho người khác khi tham chiếu đến một ô, vì nhiều khi họ sẽ tưởng rằng dữ liệu trong ô đó là số, nhưng thật ra nó là text. Nếu bạn thay đổi kiểu canh lề mặc định, bạn sẽ bị nhức đầu với nó sau này. Chỉ có tiêu đề của bảng tính là được hưởng ngoại lệ mà thôi.

Chỉ định dạng các ô theo kiểu argument khi thật sự cần thiết. Bởi vì tất cả những dữ liệu nhập vào trong các ô vừa được định dạng trước theo kiểu argument sẽ bị chuyển thành dữ liệu kiểu text, ngay cả khi bạn nghĩ rằng cái bạn nhập vào là dữ liệu kiểu số hay dữ liệu kiểu ngày tháng. Và tồi tệ hơn nữa, bất kỳ ô nào chứa công thức tham chiếu đến một ô vừa được định dạng theo kiểu text, cũng sẽ bị định dạng thành kiểu text. Nói chung, bạn đâu có muốn những ô chứa công thức lại bị định dạng theo kiểu text, phải không.

Trộn các ô (merge cells) cũng là một vấn đề. Có một số công thức hay macro bất thể chạy được với những ô vừa được trộn. Cho nên, thay vì trộn ô, bạn hãy dùng chức năng "Center beyond selection", nằm trong Home ➝ nhóm Alignment. Nhấn vào cái mũi tên ở dưới cùng bên phải, sẽ mở ra hộp thoại Format với tab Alignment được chọn sẵn. Bạn sử dụng thanh trượt dọc của khung Horizontal để chọn Center Beyond Selection. hay bạn cũng có thể right-click rồi chọn Format Beef từ adjustment menu. Với Excel 2003 trở về trước, bạn mở hộp thoại này từ card Format ➝ Beef (Ctrl+1).



Nên sử dụng Center Beyond Selection thay cho Absorb Cells





---------- Bài viết vừa được nhập tự động bởi hệ thống ----------

Những mẹo nhỏ khi dùng công thức

Một sai lầm rất lớn khi sử dụng công thức là cho nó tham chiếu đến toàn bộ các cột trong mảng dữ liệu. Điều này làm cho Excel phải kiểm tra hàng ngàn ô, nếu bất nói là hàng triệu, để chắc chắn bất bỏ sót một dữ liệu nào.

Ví dụ, giả sử bạn có một mảng dữ liệu từ A1 đến H1000, và bạn quyết định sẽ dùng những hàm tìm kiếm của Excel để trích ra những dữ liệu cần thiết. Bởi vì bạn còn phải bổ articulate thêm dữ liệu vào mảng này, nên để chắc ăn, bạn làm ra (tạo) tham chiếu trong công thức đến toàn bộ các hàng của các cột trong mảng này. Khi đó, tham chiếu của bạn có thể sẽ là A:H, hay cụ thể hơn, là A1:H65536. Bạn nghĩ rằng làm như vậy thì bạn sẽ bất e sợ gì khi thêm dữ liệu vào trong mảng.

Đó là một thói quen không cùng tai hại mà bạn phải luôn tránh xa nó. Bởi nó sẽ làm cho bảng tính trở nên ì ạch, thậm chí là bất chạy nổi. Bạn vẫn có thể loại bỏ những phần bất cần tham chiếu ra khỏi công thức, mà lại bảo đảm được những dữ liệu mới thêm vào sẽ tự động được cập nhật trong tham chiếu của công thức, bằng cách dùng Table (List trong Excel 2003 trở về trước) hay sử dụng các Name động.

Một vấn đề nữa thường xảy ra với những bảng tính lớn, mà cấu hình máy lại nhỏ, là Excel tính toán rất chậm khi dữ liệu được cập nhật. Để giải quyết vấn đề này, bạn thường được khuyên là hãy chuyển đổi chế độ tính toán của Excel thành dạng Manual: Nhấn vào nút Office ở góc trái phía trên của bảng tính, rồi chọn Excel Options ➝ Formulas (với Excel 2003 trở về trước, chọn card Tools ➝ Options ➝ Calculations), rồi chọn Manual :



Chọn Manual Caculation với Excel 2007



Chọn Manual Caculation với Excel 2003
Tuy nhiên, lời khuyên đó thì bất hay cho lắm, mà chỉ là mang tính đối phó. Một bảng tính thì bao giờ cũng có những công thức tính toán, nếu bạn đang chạy một bảng tính trong chế độ tính toán Manual, có thể bạn sẽ chỉ có được những thông tin cũ mèm, chưa hề được cập nhật. Nếu muốn có được thông tin chính xác, bạn phải nhấn F9. Nhưng, chắc chắn là bất phải lúc nào bạn cũng nhớ điều này! Vậy bạn thử cân nhắc xem: Nếu như cần thắng chân bị kẹt và làm cho chiếc xe hơi của bạn chạy chậm lại, bạn sẽ sửa lại cần thắng chân, hay là gỡ bỏ cái thắng chân rồi tin tưởng trả toàn vào cái thắng tay? Nghĩa là, khi bảng tính của bạn chạy chậm, bạn sẽ sửa lại công thức cho nó chạy nhanh hơn, hay là đặt nó vào chế độ tính toán bằng tay rồi cứ thế mà làm tiếp? Hầu như chúng tui không bao giờ làm điều này, nhưng rất nhiều người vừa không ngần ngại đặt bảng tính của họ trong chế độ tính toán Manual. Nếu như bạn thấy rằng nên phải đặt bảng tính của mình trong chế độ tính toán Manual thì mới làm chuyện được, bạn nên suy nghĩ đến chuyện thiết kế lại bảng tính.

Công thức mảng là một trong những nguyên nhân của vấn đề trên. Chúng là một lựa chọn tốt khi tham chiếu đến những ô riêng lẻ. Nhưng nếu bạn dùng chúng để tham chiếu đến những dãy dữ liệu lớn, thì hãy sử dụng chúng càng ít càng tốt. Khi có một số lượng lớn các mảng tham chiếu đến một dãy dữ liệu lớn, hiệu suất của bảng tính sẽ bị ảnh hưởng, đôi khi nó dừng hẳn luôn, và bạn bắt buộc phải dùng chế độ tính toán bằng tay.

Excel có nhiều hàm chuyên xử lý những dữ liệu lớn thay cho công thức mảng, mà sẽ được đề cập đến trong chiêu thứ 79: Tránh lỗi #VALUE! khi tính tổng (SUM) hay đếm (COUNT) các ô. Ngoài ra, trong Excel Giúp có một số ví dụ rất hay sẽ giúp bạn cách để dùng công thức trên những bảng dữ liệu lớn để trích ra kết quả phụ thuộc theo những tiêu chuẩn. hay bạn có thể sử dụng Pivot Table, sẽ được đề cập đến trong phần thứ 4 của loạt bài này. Mặc dù PivotTable thì có vẻ phức tạp khi bạn sử dụng nó lần đầu tiên, nhưng chúng tui thành thật khuyên bạn hãy học cách sử dụng nó. Một khi bạn vừa làm chủ được PivotTable, ngày nào đó bạn sẽ tự hỏi rằng làm sao tui có thể sống mà bất có PivotTable!


Trước khi kết thúc phần mở đầu, xin nói điều này: Nếu bạn chẳng nhớ được gì (vì tui nói nhiều quá), bạn chỉ cần nhớ một chuyện thôi: Excel sẽ chạy tốt hơn nếu tất cả các dữ liệu của bạn được xếp liên tục với nhau trong cùng một bảng. Nó sẽ giúp cho bạn thực hành các chiêu sau đây một cách dễ dàng hơn.

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

Trong các chiêu sau đây, sẽ có một số biểu tượng nằm ở đầu mỗi chiêu:

* Áp dụng cho tất cả các phiên bản của Excel

* Chỉ áp dụng cho Excel 2007

* Chỉ áp dụng cho những phiên bản trước Excel 2007

* Chỉ áp dụng cho Excel 2003 và Excel XP

* Chỉ áp dụng cho Excel 2000


---------- Bài viết vừa được nhập tự động bởi hệ thống ----------


mã nguồn: giải pháp Excel (GPE)
--------------------------------------------------
Tạm thời (gian) hum nay coi như đây nha các bác!
Mai em sẽ column tiếp "Chiêu đầu tiên!" .

Thông thường người dùng Excel có những dữ liệu muốn nhập vào nhiều area khác nhau. Bạn có thể dùng công cụ Accumulation các area lại để có thể nhập dữ liệu cùng thời. Nhưng cũng có 1 cách khác nhanh chóng và linh hoạt hơn nếu bạn dùng vài dòng lệnh VBA.

Nhưng phải nói đến công cụ Accumulation cái vừa nhỉ? Công cụ Accumulation dùng để nhập dữ liệu vào nhiều area 1 lúc chắc có nhiều người chưa dùng đến. Nó dùng để kết nối các bedding với nhau trong phạm vi assignment book.

1. Accumulation các area lại bằng tay:

Để nhóm các area lại, các bạn chỉ chuyện bang chọn area thứ nhất, nơi bạn sẽ nhập liệu từ bàn phím vào, rồi vừa nhấn Ctrl, vừa nhấn vào tên những area khác mà bạn muốn nhập liệu cùng thời. Khi bạn gõ bất kỳ cái gì vào area hiện hành, dữ liệu đó cũng sẽ tự động nhập vào các area còn lại trong nhóm. Điệp vụ trả tất nhanh chóng (nghe tương tự 007 nhỉ).

Để thoát khỏi tình trạng group, bạn chỉ chuyện hay bang chọn 1 area khác ngoài nhóm, hay bang chuột phải lên tên 1 area trong nhómbất kỳ, chọn Ungroup Sheets. Hoặn nếu bạn chỉ muốn gỡ 1 trong số các area ra khỏi nhóm, hãy Ctrl bang vào tên area đó.

Khi 2 hay nhiều area Accumulation với nhau, trên thanh tiêu đề của Windows sẽ có chữ [Group] (tronh ngoặc vuông). Nhìn vào đó bạn có thể biết bạn vẫn còn đang trong tình trạng accumulation các sheets.

Trừ khi bạn có cặp mắt sắc như chim ưng, hay bén như dao cạo, thường thì bạn bất để ý đến dấu hiệu này, bạn sẽ quên và bất ungroup khi công chuyện đã xong. Chính vì thế tui khuyên bạn ngay khi thực hiện xong thao tác nhập liệu hàng loạt vào các sheet, phải ungroup chúng ngay.

Dù phương pháp này là dễ nhất, nhưng có nghĩa là bạn phải nhớ và nhớ accumulation và ungoup khi cần, nếu bất bạn sẽ không tình ghi đè lên dữ liệu ở những area mà bạn bất nhìn thấy trên màn hình hiện thời. Thí dụ như bạn chỉ muốn ghi cùng lúc dữ liệu vào 1 vùng nào đó của các bảng tính, còn ngoài vùng đó thì dữ liệu các area phải khác nhau. Thế nhưng khi các area vừa accumulation rồi thì nó có thèm quan tâm bạn đang nhập liệu ở vùng nào đâu?

2. Accumulation các area lại 1 cách tự động:

Bạn có thể giải quyết vấn đề trên bằng cách dùng 1 đoạn cipher VBA hết sức đơn giản. Để đoạn cipher làm chuyện được, nó phải được đặt trong các sự kiện của riêng area nào bạn muốn. Để vào trang soạn thảo cipher của area nào, thì bang phải chuột vào tên area đó và chọn View Code.

Bạn có thể chọn 1 trong các sự kiện của sheet, những sự kiện chỉ liên quan đến area đó như đổi ô chọn, thay đổi nội dung 1 ô, kích hoạt sheet, thoát khỏi sheet, … để thực thi đoạn cipher VBA của mình.

Thường thì bạn được đưa thẳng đến trang soạn thảo cipher riêng của area nếu bạn chọn View Cipher như trên hướng dẫn. Bạn có thể kiểm tra lại quả thực nó thuộc về area bạn chọn hay bất bằng cách nhìn vào cửa sổ VBA Project bên trái, nếu bạn thấy dạng thư mục This Workbook – Sheetname và 1 area đang chọn thì đúng. nếu bất bạn phải tìm đúng area bạn cần và angled bang nó.

Trước tiên bạn phải đặt name cho vùng ô bạn muốn nhập dữ liệu trùng trong các sheet, giả sử là “MyRange”.

Sau đó gõ đoạn cipher này vào khung soạn thảo bên phải:

PHP Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Range("MyRange"), Target) Is Nothing Then
Sheets(Array("Sheet5", "Sheet3", "Sheet1")).Select
Else
Me.Select
End If
End Sub 

Trong đoạn cipher trên, sheet5 được ghi đầu tiên trong mảng Array, vì bạn muốn nó là area mà bạn sẽ gõ dữ liệu nhập vào. Bạn có thể chọn những area khác để accumulation chúng lại bên dưới sheet5. Sau khi gõ cipher xong, hay nhấn nút View Object, hay nhấn nút View Microsoft Excel, hay nhấn Alt- Q, Alt-F11 hay đóng hẳn cửa sổ VBA trở về màn hình Excel. Lưu bảng tính lại.

Cần nhắc lại rằng đoạn cipher trên phải nằm trong area được khai báo đầu tiên của Array, là area mà bạn sẽ nhập dữ liệu từ bàn phím.

Khi bạn vừa hoàn tất, mỗi khi bạn bang chọn 1 ô nào đó trong sheet5, đoạn cipher sẽ kiểm tra xem ô bạn chọn có nằm trong vùng vừa đặt tên “MyRange” không. Nếu đúng, cipher sẽ Accumulation các area lại với nhau để bạn nhập dữ liệu hàng loạt sheets. Nếu bất phải, nó ungroup chúng ra bằng cách đơn giản là kích hoạt area hiện hành. Dùng đoạn cipher trên sẽ tiện lợi ở chỗ nó giải phóng bạn khỏi cái chuyện phải nhớ Accumulation khi bắt đầu và UnGroup khi kết thúc nhập liệu trong vùng chọn.

Bây giờ nếu bạn muốn những dữ liệu như nhau được nhập vào các area chọn, nhưng ở những vị trí khác nhau, hãy dùng đoạn cipher khác:

PHP Code:

Private Sub worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("MyRange"), Target) Is Nothing Then
With Range("MyRange")
.Copy Destination:=Sheets("Sheet3").Range("A1")
.Copy Destination:=Sheets("Sheet1").Range("D10")
End With
End If
End Sub 

Đoạn cipher này cũng phải được đặt trong khung soạn thảo của riêng sheet5.

Đoạn cipher này hoạt động khác với đoạn cipher trên. Khi bạn thay đổi nội dung của 1 ô nào đó, cipher sẽ xác định xem ô hiện hành có nằm trong vùng "MyRange" hay không, nếu phải thì archetype nguyên vùng "MyRange" (của sheet5) và adhesive vào cùng thời ô A1 của sheet1 và D10 của sheet2. Ngược lại thì bất làm gì cả.

Mong rằng tuyệt cú cú chiêu này giúp bạn tiết kiệm thời (gian) gian và bộ nhớ của chính bạn. Hẹn gặp lại ngày mai với tuyệt cú cú chiêu số 3.

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

Mặc dù Excel có hỗ trợ chuyện bảo vệ cho bảng tính (Protect Sheet, Protect Workbook), nhưng công cụ thô sơ này bất bao gồm chuyện chỉ cho phép người sử dụng một số đặc quyền hạn chế - trừ khi bạn làm theo những chiêu sau đây.

Bạn có thể quản lý chuyện tương tác với các bảng tính của bạn bằng cách giám sát và đáp ứng các sự kiện. Các sự kiện (event), là những hành động xảy ra khi bạn làm chuyện với các bảng tính hay các Sheet. Những sự kiện này thường bao gồm chuyện mở (open) một bảng tính, lưu (save) nó, và đóng (close) nó. Bạn có thể bảo Excel chạy những ngựa Visual Basic (macro) một cách tự động khi xảy ra bất kỳ một trong những sự kiện như thế.

Tuy nhiên, bạn nên biết rằng, người sử dụng có thể vượt qua tất cả những ngựa bảo vệ bởi Visual Basic bẳng cách không hiệu hóa chuyện thực thi các macro: Nhấn nút Office ➝ Excel Opions ➝ Trust Center ➝ Trust Center Setting ➝ Macro Settings [E2003: Tools | Macro | Security]. Nếu đặt mức độ an toàn là Disable all macros with notification [E2003: mức Medium], mỗi khi mở một bảng tính có chứa macro, Excel sẽ xuất hiện một hộp thông báo và cho phép người sử dụng có thời cơ để tắt các macro. Nếu đặt mức độ an toàn là Disable all macros after notification [E2003: mức High], Excel sẽ ngăn bất cho chạy tất cả các macro có trong bảng tính. Nói cách khác, khi nên phải dùng các macro có trong bảng tính, người sử dụng sẽ chọn một mức an toàn thích hợp để cho phép sử dụng các macro.

Ngăn chận chuyện lưu lại bảng tính với một tên khác

Bạn có thể thiết lập thuộc tính "Read only" cho bất kỳ một bảng tính nào, bằng cách nhấn Office ➝ Save, nhấn nút Tools, chọn General Options [E2003: Book | Save As | Tools | General Options] và kích hoạt hộp kiểm Read alone recommended. Việc này ngăn chận người sử dụng lưu lại những thay đổi trên bảng tính, trừ bay họ lưu lại bảng tính với một tên khác hay lưu vào một nơi khác.

Tuy nhiên, có thể điều bạn muốn là bất cho lưu lại bảng tính của bạn với tên khác, cũng bất cho lưu vào một nơi khác. Nói cách khác, bạn muốn người khác chỉ có thể lưu lại bảng tính này với chính tên gốc của nó, chứ bất được sao chép nó. Điều này đặc biệt có dụng khi có nhiều người cùng sử dụng chung một bảng tính, và bạn bất muốn trong ổ cứng của mình đầy dẫy những bản sao của bảng tính này, cho dù là với một tên khác, hay ở một thư mục khác.

Sự kiện Before Save mà tui sắp nói đến vừa có từ thời (gian) Excel 97. Đúng như tên gọi của nó, sự kiện này chỉ xuất hiện ngay trước khi bảng tính được lưu, cho phép bạn can thiệp kịp thời (gian) việc lưu lại bảng tính, đưa ra một cảnh báo, và bất cho Excel lưu bảng tính.

Trước khi tự thực hiện điều này vào bảng tính, hãy chắn chắn rằng bạn vừa lưu lại bảng tính của bạn. Bởi vì chuyện đưa đoạn cipher sau đây vào môt bảng tính chưa được lưu, có thể sẽ gây cho bạn nhiều rắc rối.

Để đưa đoạn cipher sau vào bảng tính, bạn hãy chọn Develope ➝ Visual Basic (hay nhấn Alt+F11), và nhấp đúp chuột vào mục ThisWorkbook trong khung

Project Explorer. Nếu trên Ribbon của bạn bất có mục Develope, bạn hãy vào trong Excel Options ➝ Popular, đánh dấu vào tùy chọn Show Develope tab in the Ribbon, rồi nhấn OK. [E2003, nhấn phải chuột vào biểu tượng Excel ở ngay bên trái card Book trên thanh menu, và chọn mục View Code, như minh họa ở hình bên dưới]:

Sử dụng Quick admission card (trong Excel 2003) để nhập cipher áp dụng cho workbook

Bạn hãy nhập đoạn cipher sau đây vào cửa sổ VBE. Sau khi nhập xong, nhấn Alt+Q để trở về Excel, rồi lưu lại bảng tính:

PHP Code:

   Private Sub workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
    Cancel As Boolean)
    Dim lReply As Long
    If SaveAsUI = True Then
    lReply = MsgBox("Sorry, you are not allowed to save this workbook as another name." _ 
    & "Do you wish to save this workbook?", vbQuestion + vbOKCancel)
    Cancel = (lReply = vbCancel)
    If Cancel = False Then Me.Save
    Cancel = True
    End If
    End Sub 

Thử một tí. Bạn nhấn Ctrl+S (hay gọi lệnh Save) xem. Bảng tính của bạn lưu bình thường. Bây giờ bạn nhấn F12 (hay gọi lệnh Save as), bạn sẽ gặp một cảnh báo, nói rằng bạn bất thể lưu lại bảng tính này dưới bất kỳ một tên nào khác, trừ bay bạn không hiệu hóa các macro.

Nói thêm, khi bạn lưu một bảng tính có chứa macro hay cipher trong Excel 2007, bạn sẽ được nhắc nhở rằng bạn phải lưu book ở dạng cho phép macro hoạt động (macro-enable workbook), có phần mở rộng lớn là *.xlsm, chứ bất thể lưu ở dạng bình thường (*.xlsx).

Ngăn chận chuyện in một bảng tính

Đôi khi bạn phải rời bàn làm chuyện đi đâu đó mà quên tắt máy (chuyện này là chuyện thường), cho dù bạn vừa làm công chuyện ngăn chận chuyện có ai đó chép bảng tính của bạn ra ngoài, như vừa nói ở trên, có thể bạn còn lo xa hơn, muốn rằng bảng tính này cũng sẽ bất bị in ra khi bạn vắng mặt. Nếu thật sự bạn muốn như vậy, bạn hãy dùng sự kiện Before Print. Hãy nhập đoạn cipher sau trong cửa sổ VBE:

PHP Code:

Private Sub workbook_BeforePrint(Cancel As Boolean)
    Cancel = True
    MsgBox "Sorry, you cannot Print from this workbook", vbInformation
    End Sub 

Nhấn Ctrl+Q để anchorage về Excel và lưu lại. Từ bây giờ, nếu có ai có muốn ra lệnh in bảng tính này, sẽ bất có gì xảy ra. Dòng lệnh MsgBox ở đoạn cipher trên chỉ là một tùy chọn, nhưng bạn nên sử dụng nó, vì ít nhất thì nó cũng để lại một thông tin gì đó, để người khác sẽ bất tưởng lầm rằng máy in của họ bị hư, hay là chương trình Excel có lỗi!

Nếu bạn chỉ muốn ngăn chận in một phần nào đó trong bảng tính, ví dụ bất cho in Sheet1 và Sheet2, bạn sử dụng đoạn cipher sau:

PHP Code:

 Private Sub workbook_BeforePrint(Cancel As Boolean)
    Select Case ActiveSheet.Name
    Case "Sheet1", "Sheet2"
    Cancel = True
    MsgBox "Sorry, you cannot print this sheet from this workbook", _
    vbInformation
    End Select
    End Sub 

Dĩ nhiên bạn có thể thêm vào bất kỳ Sheet nào có trong bảng tính của bạn. Chỉ chuyện nhập tên của nó vào trong hàng có lệnh Case, và tách biệt Sheet này với Sheet khác bằng dấu phẩy, và nhớ nhập tên Sheet trong một cặp dấu nháy kép. Trong trường hợp bạn chỉ muốn ngăn chận bất cho in một Sheet mà thôi, bạn chỉ cần nhập tên của Sheet đó (trong cặp nháy kép) sau chữ Case, và bất cần gõ dấu phẩy.

Ngăn chận chuyện chèn thêm Sheet vào một bảng tính

Excel có chức năng Protect Workbook, bất cho phép thay đổi cấu trúc của bảng tính như thêm, xóa, di chuyển, hay đổi tên các Sheet. Tuy nhiên, có thể bạn chỉ muốn ngăn chận chuyện thêm Sheet thôi, còn những chuyện khác thì vẫn cho phép. Đoạn cipher sau đây giúp bạn làm chuyện đó:

PHP Code:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Application.DisplayAlerts = False
    MsgBox "Sorry, you cannot add any more sheets to this workbook", _
    vbInformation
    Sh.Delete
    Application.DisplayAlerts = True
    End Sub 

Khi có ai đó chèn thêm một Sheet mới vào trong bảng tính, trước tiên Excel sẽ xuất hiện hộp cảnh cáo: "Xin lỗi, bạn bất thể thêm bất kỳ Sheet nào vào trong bảng tính này", rồi ngay lập tức, Excel sẽ xóa cái Sheet mới thêm vào khi nút OK trong hộp thông báo được nhấn. Công dụng của dòng lệnh Application.DisplayAlerts = False là bất hiển thị hộp thông báo xác nhận chuyện xóa một Sheet nào đó trong Excel.

Một cách khác để ngăn chận chuyện chèn thêm Sheet vào bảng tính là bạn chọn Review trên thanh Ribbon, chọn lệnh Protect Workbook trong nhóm Changes, rồi chọn lệnh Protect Structure and Windows [E2003: Tools | Protection | Protect Workbook... đánh dấu kiểm vào tùy chọn Structure] và nhấn OK (có thể đặt countersign nếu thích). Tuy nhiên, như vừa nói ở đầu bài, chuyện này sẽ ngăn chận hết tất cả thao tác với các Sheet trong bảng tính.

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

Exel luôn luôn thông báo hay nhắc nhở bạn xác nhận các hành động và thao tác của bạn. Bài này sẽ chỉ dẫn bạn cách loại bỏ các thông báo hay nhắc nhở.

Các kiểu thông báo mà tui đề cập ở đây chính là các câu hỏi mà Excel hỏi bạn có cho macro hoạt động hay bất hay là các thông báo về các hành động xóa một trang bảng tính. Sau đây là một số loại thông báo của Excel:

Thông báo kích hoạt Macro trong khi trong workbook bất có macro

Lỗi này phát sinh do bạn vừa từng thực hiện ghi macro trong workbook, và mặc dù bạn vừa xóa tất cả các ngựa lệnh macro nhưng trong các lần mở workbook sau đó Excel vẫn thông báo bạn là trong workbook có chứa macro.

* Xóa macro: vào ngăn Developer | chọn Macro | chọn các macro và nhấn Delete để xóa [E2003: Tools | Macro | Macros].

* Bạn chỉ nhận được thông báo kích hoạt macro khi thiết lập mức an toàn là “Disable all macro with notification” [E2003: mức Medium].

* Nếu mức an toàn được thiết lập là “Enable all macors” [E2003: mức Low] thì các macro sẽ tự động kích hoạt mà bất có tiếng kêu cảnh báo.

* Nếu mức an toàn là “ Disable all macros after notification” [E2003: mức High] thì các macro tự động bị khóa lại bất được phép thực thi.

Khi bạn ghi một macro, Excel chèn một bore vào để chứa các lệnh của bạn và các hàm. Vào lúc mở một bảng tính, Excel kiểm tra xem bảng tính có chứa bore nào bất và chúng rỗng hay đang chứa các ngựa lệnh. Việc xóa các macro của bảng tính sẽ xóa tất cả các ngựa lệnh chứa bên trong bore mà bất xóa các bore – tương tự như chuyện uống cạn lon sửa và bỏ lại vỏ hộp sửa vào lại tủ lạnh. Do vậy, bạn nên phải xóa đi các bore này theo chỉ dẫn sau

Chọn ngăn Developer | chọn nút Visual Basic tại nhóm Cipher hay nhấn tổ hợp phím ALT+F11 [E2003: Tools | Macro | Visual Basic Editor (VBE) và chọn View | Project Explorer].

Các nút lệnh trên ngăn Developer

Nếu bất thấy cửa sổ Project thì bạn vào View và chọn Project Explorer hay nhấn tổ hợp phím Ctrl+R

Cửa sổ Project Explorer

Nhấp chuột lên các ký hiệu (+) để xem các thánh phần bị ẩn của các nhánh con trong cửa sổ Project và hãy tìm đến các module. Bạn nhấp phải chuột lên từng bore và chọn lệnh Remove Bore từ thực đơn ngữ cảnh. Nếu được hỏi có xuất bore ra thành tập tin thì hãy chọn No. Nhấn tổ hợp ALT+Q để đóng cửa sổ VBE.

Thông báo nhắc nhở lưu trữ trong khi bất có sự thay đổi nào

Bạn có thể nhận thấy rằng đôi khi bạn chỉ mở một bảng tính và xem một số thông tin trong đó mà Excel cũng nhắc nhở bạn lưu lại các thay đổi trong Claimed Macro Workbook mặc dù bạn bất hề thay đổi gì trong đó. Có thể bạn vừa biết hay chưa, bạn vừa có sử dụng một hàm airy (là hàm trong đó có sử dụng cách volatile) trong Claimed Macro Workbook của mình.

Một claimed macro workbook là một bảng tính ẩn được làm ra (tạo) ở lần đầu tiên bạn ghi macro (Tools | Macro | Record NewMacro) với tùy chọn “Personal Macro Workbook” tại “Store Macro in”. Macro này sẽ được mở mỗi khi bạn sử dụng Excel. Một hàm airy sẽ làm bảng tính tự động tính toán lại mỗi khi bạn thực hiện một thao tác nào đó trong Excel, bao gồm cả chuyện mở và đóng bảng tính hay ứng dụng Excel. Hai trong số các hàm airy phổ biến là hàm Today() và Now().

Hộp thoại Record Macro

Vì thế, mặc dù bạn chắc chắn là mình bất có thay đổi gì trong bảng tính nhưng các hàm airy này vẫn đang hoạt động âm thầm. Điều này được tínhcoi nhưmột sự thay đổi và Excel sẽ thông báo việc lưu trữ mặc dù bất tồn tại bất kỳ sự thay đổi nào trong bảng tính.

Nếu bạn muốn Excel dừng chuyện thông báo lưu trữ các thay đổi mà bạn bất hề thực hiện, bạn có một vài cách thực hiện. Cách dễ thấy nhất là ta bất lưu các hàm airy trong Claimed Macro Workbook và nên xóa bỏ tất cả các hàm airy đang có trong đó. Hoặc, nếu bạn cần các hàm airy nhưng bất muốn Excel lưu lại các thay đổi bất nhìn thấy này bạn có thể thêm đoạn cipher sau vào Workbook bore của Claimed Macro Workbook để đánh lừa nó là bảng tính vừa được lưu rồi.

PHP Code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Me.Saved = True
  End Sub 

Để mở Claimed Macro Workbook bạn vào View | Unhide trong nhóm Window [E2003: Window | Unhide], chọn Personal.xls từ hộp thoại Unhide và nhấp nút OK. Nhấn tổ hợp phím ALT+F11 để vào cửa sổ VBE, sau đó nhấp phải chuột lên ThisWorkbook từ Personal.xls trong cửa sổ Project rồi chọn View Cipher và nhập đoạn ngựa trên vào. Sau khi nhập xong, bạn nhấn tổ hợp phím ALT+Q để đóng của sổ VBE lại.

Tất nhiên, nếu bạn có sử dụng hàm airy để tính toán lại bảng tính và bạn muốn lưu các thay đổi này thì bạn dùng đoạn ngựa sau thay cho đoạn ngựa nêu trên.

PHP Code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Me.Save
  End Sub 

Đoạn ngựa trên sẽ lưu lại Claimed Macro Workbook một cách tự động mỗi khi nó được mở.

Dừng các thông báo của Excel về các macro vừa ghi

Một trong số nhiều hạn chế của chuyện ghi macro là bất tự thêm vào các lệnh loại bỏ các thông báo khi thực thi. Ví dụ như bạn ghi một macro xóa một worksheet và khi thực thi macro này bạn sẽ nhận được thông báo xác nhận chuyện xóa worksheet. Muốn tắt thông báo này, bạn làm như chỉ dẫn sau:

Chọn ngăn Developer | Macros tại nhóm Cipher hay nhấn ALT+F8 [E2003: Tools | Macro | Macros] để gọi hộp thoại Macro. Tại Macro in chọn “All Open Workbooks”, chọn tên macro mà bạn vừa ghi và nhấn nút Edit. Đặt con trỏ lên trước dòng lệnh đầu tiên của thủ tục và nhập vào dòng lệnh sau:

Application.DisplayAlerts = False

Tại sau dòng lệnh cuối cùng, bạn thêm vào:

Application.DisplayAlerts = True

Macro của bạn sau khi thêm 2 dòng lệnh trên sẽ tương tự như sau:

PHP Code:

Sub MyMacro( 
  '
  ' MyMacro Macro
  
  Application.DisplayAlerts = False
  ActiveSheet.Delete
  Application.DisplayAlerts = True
  End Sub 

Lưu ý rằng, bạn vừa bật chức năng thông báo của Excel lại tại dòng lệnh cuối cùng của macro. Nếu bỏ dòng lệnh này, bạn sẽ bất bao giờ nhận được các thông báo nữa và điều này có thể gây nguy hại vì có thể bạn không tình thực hiện một thao tác nhầm lẫn nghiêm trọng nào đó mà bất hề nhận được thông báo để xác nhận hành động.

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

Hum nay tớ up típ 1 chiêu nữa nak tất cả người.

Chiêu thứ 5: Ẩn area sao cho người dùng bất thể dùng lệnh unhide để hiện ra

Đôi khi bạn có những thông tin trên 1 area nào đó và bạn bất muốn người dùng đọc được. Bạn muốn có 1 khu vực (có thể là 1 sheet) ghi chép những dữ liệu, công thức, những rõ hơn vụn vặt và bất muốn cho ai nhìn thấy.

Thí dụ như 1 area chứa những vùng làm dữ liệu cho Data Validation, Combobox, những tên biến và giá trị biến dùng làm const trong VBA, những thông tin nhạy cảm và những thứ khác. Mặc dù bạn có thể dấu area đó bằng cách chọn View ➝Hide trong mục chọn Window (đối với Excel 2003 là Format ➝Sheet ➝Hide), nhưng sẽ tốt hơn nếu bạn làm cách khác để người dùng bất thể chọn View ➝ Unhide trong mục chọn Window (đối với Excel 2003 là Format ➝Sheet ➝ Unhide).

Bạn cũng có thể dùng chức năng khoá bảng tính, nhưng nó vẫn được nhìn thấy. Hơn nữa bạn bất thể khoá những ô chứa dữ liệu kết nối với những ascendancy làm ra (tạo) bởi thanh công cụ Form.

Thay vì vậy, bạn hãy thiết lập trạng thái cho area dạng xlVeryHidden. Vào Developer ➝Visual Basic hay nhấn Alt-F11 để vào trang soạn thảo VBE (đối với Excel 2003 thì vào card Tools ➝Macro ➝Visual Basic Editor và nhìn bên trái trong khung Project Explorer, nếu bất thấy thì chọn View - Project Explorer. Tìm tên book và bấm vào dấu cộng để hiện ra các tên sheet. Chọn tên sgeet nào bạn muốn dấu, mở khung Property bằng cách vào card View – Property Window (hay nhấn F4). Chọn tab Alphabetic, tìm dòng Visible và chọn 2- xlsSheetVeryHidden. Nhấn Alt-Q để trở về trang tính Excel, lưu sự thay đổi này. Area của bạn sẽ chẳng bao giờ có thể thấy được bằng cách thông thường trên card View – Unhide hay Format – Sgeet – Unhide.

Để thấy trở lại, bạn phải vào chỗ cũ, chọn lại -1 – xlSheetVisible

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

Nếu như bạn thường xuyên phải thực hiện cùng một kiểu trình bày cho bảng tính của mình, bạn có thể tự thiết kế ra một bảng tính mẫu cho riêng bạn, là một bảng tính vừa có sẵn tiêu đề, một số cột, một số hàng nào đó, thậm chí là cả Name và công thức; cùng thời còn có thể làm ra (tạo) ra một hộp thoại riêng vào trong Thư viện các Templates của Excel.

Không biết là, có khi nào bạn sử dụng các Arrangement không? Nghĩa là thay vì nhấn Ctrl+N để làm ra (tạo) một bảng tính mới, thì bạn nhấn vào nút Office, chọn lệnh New [E2003: Book | New], và chọn ra một kiểu bảng tính vừa được thiết kế sẵn trong thư viện các Template, sau đó sửa lại bảng tính này thành cái của mình?

Chọn một bảng tính vừa được thiết kế sẵn trong thư viện Templates của Excel

Nếu bạn vừa từng thấy, vừa từng sử dụng những bảng tính vừa đươc thiết kế sẵn này, ắt bạn sẽ thấy sự có dụng của nó, nhờ nó mà chúng ta tiết kiệm được bao nhiêu là thời (gian) gian cho chuyện thiết kế và trình bày.

Và, trong công việc, nếu như bạn cứ phải thường xuyên thiết kế những bảng tính có cấu trúc tương tự nhau, tui nghĩ rằng chuyện bạn thường làm là mở một bảng tính vừa có, xóa đi phần nội dung và lưu lại với tên khác, phải không? Nhưng cách làm này, đôi khi do sơ ý, bạn sẽ xóa luôn cả những công thức mà bạn vừa mất hàng giờ để nghĩ ra, rồi phải mất thời (gian) gian cho những thao tác xóa... Vậy, có bao giờ bạn nghĩ đến chuyện sẽ làm một cái tương tự như những Arrangement có sẵn của Excel không? Để mỗi khi cần loại một bảng tính nào đó (một bảng lương, một bảng chấm công, một bản báo giá, v.v...) thì bạn chỉ chuyện vào trong Thư viện Templates, thậm chí là vào trong "Thư viện riêng" của bạn, lấy một cái mẫu ra rồi chỉ chuyện nhập số liệu vào? Tiết kiệm được bao nhiêu là thời (gian) gian. Chiêu thứ 6 này sẽ giúp bạn thực hiện điều tui vừa nói.

Tạo một bảng tính mẫu

Những Arrangement cho phép bạn có sẵn một "dự án" (project), có nghĩa là bạn sẽ có sẵn môt bảng tính, bao gồm phần tiêu đề của bảng tính, một số hàng và cột với các tiêu đề của nó, các công thức, và cả các Name (nếu có).. Ví dụ, đây là Arrangement BillingStatement1 có sẵn trong Excel 2007:

Arrangement BillingStatement1 có sẵn trong Excel 2007

Bạn cũng có thể làm ra (tạo) ra một cái tương tự vậy, sau đó lưu lại thành một Template. Việc này bất có gì khó. Bạn mở một bảng tính mới, làm ra (tạo) ra một cái sườn: tô màu, định dạng, đặt Name, thiết kế công thức, v.v... như bình thường bạn vẫn làm, nhưng khoan nhập dữ liệu vào. Bạn hãy xóa hết các Sheet bất cần thiết, chỉ giữ lại những Sheet vừa được thiết kế thôi. hay bạn có thể mở một bảng tính vừa có sẵn, xóa hết những dữ liệu thô, chỉ giữ lại phần trình bày. Sau đó, bạn nhấn F12 (Save as...) rồi chọn Excel Template:

Chọn loại book là Excel Arrangement trong cửa sổ Save

Bạn hãy đặt cho nó một cái tên dễ nhớ, rồi nhấn Save. Theo mặc định thì Excel sẽ lưu bảng tính mẫu này vào Thư mục chứa các Arrangement vừa được làm ra (tạo) ra khi bạn cài đặt Excel 2007:

Excel sẽ tự động chọn thư mục để lưu các Template

Mỗi khi cần sử dụng một bảng tính có cấu trúc y hệt bảng tính bạn vừa lưu với dạng Arrangement như ở trên, bạn nhấn nút Office ➝ New [E2003: Book | New], rồi chọn tiếp My Templates... ở cửa sổ New Workbook:

Chọn My Arrangement để mở các bảng tính mẫu vừa lưu

Các bảng tính mẫu mà bạn vừa tạo ra và lưu lại như tui đã trình bày ở trên, sẽ hiện ra. Bạn chỉ chuyện chọn mẫu bạn cần, rồi nhấn OK.

Excel sẽ tự động chọn thư mục để lưu các Template

Tạo một tab riêng trong cửa sổ My Templates

Như bạn vừa thấy ở hình ngay phía trên đây, khi bạn nhấn My Templates trong cửa sổ New Workbook, một cửa sổ New sẽ mở ra. Để ý một tí, bạn sẽ thấy cửa sổ này có dạng những cái tab. Nhưng hiện giờ nó chỉ nó mỗi một tab My Templates thôi. Giả sử bạn vừa tạo ra rất nhiều bảng tính mẫu, thay vì "nhét" tất cả vào một chỗ, bạn có muốn làm ra (tạo) thêm những tab riêng cho mỗi loại mẫu không? Ví dụ, tab "Báo cáo thuế", tab "Báo giá", v.v... hay lớn loại như thế ?

Khi bạn nhấn F12 để lưu lại với dạng Arrangement như tui đã trình bày ở phần trên, thì thay vì để Excel chọn thư mục lưu tự động, bạn hãy nhấn nút Create New Folder:

Nhấn nút Create New Folder để làm ra (tạo) thêm tab cho các Template

Một hộp thoại sẽ mở ra, và bạn nhập một cái tên cho nhóm biểu mẫu mà bạn muốn. Ở đây, tui tạo thêm một nhóm là "Báo cáo thuế" để lưu những bảng tính sẽ dùng để in ra các báo cáo nộp cho cơ quan thuế, nhấn OK, đặt tên cho Template, và nhấn Save. Vậy là xong rồi đó. tui mở thử ra cho bạn xem nhé:

Bạn thấy không, trong cửa sổ New này, vừa có thêm một tab mới là "Báo cáo thuế". Hay chứ nhỉ!

Một điều lưu ý, chắc là bất thừa. Tất cả những Arrangement và những thư mục mà bạn vừa tự làm ra (tạo) ra trong bài này, đều nằm ở đây:

C:\Documents and Settings\Owner\Application Data\Microsoft\Template

Với Owner là tên của Acount mà bạn vừa tạo ra khi sử dụng Windows. tui vừa thử thay đổi nơi lưu các Arrangement này, nhưng bất được. Khi chọn My Arrangement trong cửa sổ New Workbook, Excel chỉ liệt kê những Arrangement nào nằm ở trong cái đường dẫn ở trên đây mà thôi. Do đó, khi cài đặt lại Windows, có thể bạn sẽ mất hết tất cả những gì nằm trong thư mục C:\Documents and Settings... Vậy, điều lưu ý ở đây là, bạn nên làm ra (tạo) ra những bản sao dựphòng chốngcho các Arrangement này, cất giữ cẩn thận. Sau này, khi có phải cài đặt lại Windows, bạn chỉ chuyện chép các bản sao vào đúng chỗ của nó (chính là cái đường dẫn trên đây), là có thể sử dụng tiếp.

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

Chúc các bạn có thêm thật nhiều thời (gian) gian có ích sau khi áp dụng chiêu này.

Nếu bạn vừa tốn quá nhiều thời (gian) 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 chuyện 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 chuyện đ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à bất lo bị nhầm lẫn. Bạn có thể làm ra (tạo) một chỉ mục trong một vài cách: bằng tay, tự động làm ra (tạo) ra bởi ngựa VBA, hay 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 – bất 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à làm ra (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.

Để làm ra (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] hay 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 Document và chọn tên Area muốn kết nối tới. Nhấn nút OK để trả tất.

Phương pháp này phù hợp khi bảng tính bất có quá nhiều worksheet và tên worksheet bất có sự thay đổi thường xuyên, nếu bất 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 làm ra (tạo) ra các chỉ mục. Đoạn ngựa sau đây sẽ tự động làm ra (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 làm ra (tạo) lại mỗi khi worksheet lưu chỉ mục được chọn.

Đoạn ngựa này phải được đặt trong clandestine bore của Area 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 làm ra (tạo) và chọn ViewCode từ trình đơn ngữ cảnh hay nhấn tổ hợp phím Alt+F11.

Nhập đoạn ngựa 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(1, 1) = "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(lCount, 1), Address:="", SubAddress:= _
     "Start" & wSheet.Index, TextToDisplay:=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 ngựa 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 ngựa 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 vừa được đặt tên, bạn nên cân nhắc đến chuyện thay đổi ô A1 trong đoạn ngựa sang một đất 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 abject (siêu liên kết cơ sở) trong workbook thì các hyperlink được làm ra (tạo) ra từ đoạn ngựa trên sẽ bất thể hoạt động được, do chúng vừa liên kết đến các Name trong workbook hiện hành. Khi thuộc tính hyperlink abject được thiết lập thì các siêu liên kết sẽ trỏ đến hyperlink abject 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

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ẽ chỉ 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 ngựa 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 ngựa sau vào cửa sổ Code của ThisWorkbook:

PHP Code:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel 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:=msoControlButton, Temporary:=True)
    With cCont
        .Caption = "Sheet Index"
        .OnAction = "IndexCode"
    End With
End Sub 

Tiếp theo, bạn vào Insert | Bore và nhập vào đoạn ngựa sau:

PHP Code:

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

Đoạn ngựa 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 Bore 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.

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

Tập tin đính kèm:

Trích:

Nếu bạn bất muốn thanh cuộn kéo bảng tính lên xuống hay qua phải nhiều, hay bạn có dữ liệu ở 1 vùng nào đó mà bất 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 làm ra (tạo) bởi Excel 2007 có số cột tối (nhiều) đa 16.384, Excel trước đó là 256 (từ A đến IV), và có số dòng tối (nhiều) đ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 đó không 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 hay bằng cách định nghĩa 1 vùng cho phép xem hay 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 bất dùng đến:

Trên area 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 Adumbrate & Unhide – Adumbrate Rows trong Excel 2007 hay Format - Row - Adumbrate trong 2003 trở về trước, hay bang phải chuột và chọn Hide.

Làm tương tự như vậy để dấu những cột bất dùng đến: tìm cột cuối, bang chọn cột kế bên, nhấn Ctrl + Shift + mũi tên sang phải, chọn adumbrate columns.

Kết quả như hình, vùng làm chuyện 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 area trong VBA, bạn có thể ấn định vùng làm chuyện trong phạm vi mong muốn. Thực hiện như sau:

Nhấn chuột phải vào tên area trong tab Area Names, chọn View code, hay nhấn Alt + F11, chọn đúng tên area trong cửa sổ Project Explorer, rồi xuống khung Property, tìm dòng ScrollArea gõ vào đất 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, bất cuộn được nữa.

Tuy vậy, Excel bất lưu tính chất này khi lưu bảng tính, nên lần sau mở book lên phải set Property lại. Do đó ta phải viết 1 đoạn cipher thực hiện điều này mỗi khi kích hoạt sheet. Vào vùng soạn thảo cipher của đúng area 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ù bất có vùng bao quanh tăm tối như cách trên, nhưng bạn bất 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 bất đượ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 cipher VBA bạn làm ra (tạo) sau này, trong đó có câu lệnh baddest 1 vùng nằm ngoài vùng cho phép, hay chọn nguyên cột nguyên dòng, cũng bất chọn được.

Để có thể thực thi các đoạn cipher trên bạn phải cho vào cipher 2 dòng lệnh:

Đầu cipher thêm dòng: ActiveSheet.ScrollArea = ""

Cuối cipher 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 cipher trên chọn ô Z100 trong area hiện hành và định dạng in đậm. Sau đó chọn ô T500 trong area khác, định dạng in thường (không đậm). Trước khi thực hiện chọn và định dạng ở area nào, phải set vùng cuộn area đó 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 cipher sau:

PHP Code:

Private Sub Worksheet_Activate(  
Me.ScrollArea =Me.UsedRange. Address 
End Sub 

Đoạn cipher 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 bất thể thêm dữ liệu vào dòng mới hay cột mới. Bạn có thể mở rộng lớn vùng giới hạn ra thêm 5 dòng và 2 cột bằng đoạn cipher 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 cipher nhằm displace vùng cuộn bằng nguyên sheet:

PHP Code:

Sub ResetScrollArea(  
ActiveSheet.ScrollArea = "" 
End Sub 

Bạn có thể gán abbreviate key (phím tắt) cho đoạn cipher 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 area khác xong anchorage lại area này, vùng cuộn lại bị giới hạn bởi đoạncode trên (Worksheet_Activate())

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

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à bất nên phải bảo vệ toàn bộ trang tính của bạn.

Khi làm ra (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 sẻ chia bảng tính cho tất cả người, có thể bạn muốn rằng, bất 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 bất chỉ ngăn bất cho can thiệp vào các ô chứa công thức, mà nó bất cho can thiệp vào tất cả, nghĩa là bất ai có thể làm gì bảng tính của bạn. Đôi khi, bạn lại bất 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ừ bay 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, hay 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 lớn một ô nào đó, chọn Home ➝ Find & Select ➝ Go To Special [E2003: Edit | Go To… | Special]; hay 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 hay bất 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 Beef vừa 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...; hay 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 bound cells, chỉ cho phép Select apart beef (chọn những ô bất khóa), và nhập vào một password, nếu cần thiết:

Bỏ tùy chọn Select bound beef 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ể bất xem thấy được nếu bạn vừa chọn Hidden, bạn bất 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 bất cho ghi đè vào những ô có chứa công thức, nghĩa là bất cho sửa công thức. Tuy nhiên, phương pháp này chỉphòng chốngngừa cho chính bạn, nghĩa là tránh chuyện 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ù vừa được "Validation", bạn vẫn có thể xóa công thức, hay dán vào những ô đó bất kỳ dữ liệu nào bạn thích... Nói chung nó bất 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à tui đã 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ừ card 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, hay bạn muốn sửa lại công thức, sẽ có một thông báo xuất hiện, ngăn bất cho bạn nhập vào. Xin nhắc lại, phương pháp này bất cấm chuyện 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 ô vừa đượ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 ô bất 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) vừa được khóa, còn những ô bất cần bảo vệ thì bất bị khóa, như tui đã 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 ô vừa khóa ở trong đó, rồi nhập vào trong khung soạn thảo đoạn cipher 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 bất cần đến password, bạn bất cần dùng đoạn Password:="Secret", hay nếu muốn Countersign 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 cipher này (không cho xem) bằng cách chọn Tools ➝ VBAProject 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 bất hoạt động trả hảo, mặc dù nó cũng giúp bạn được phần nào chuyện bảo vệ các công thức. Từ khóa Target được sử dụng trong đoạn cipher sẽ chỉ để tham chiếu đến ô đang "active" ngay tại thời (gian) đ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" bất 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 vừa được tự động bật!

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

---------- Bài viết vừa được nhập tự động bởi hệ thống ----------

Trích:

nguyentong1

Nhung bai viet cua ban to thay rat bo ich nhung gia (nhà) nhu không ca buc anh thi anh hoc vao hon. xinh qua troi. du sao cung rat cam on

hjx! nhìn sao mà nói ng ta xinh mới ghia......xấu ùm ak bạn ơi ..... không xinh bằng mấy gỉl vip trong 4r đâu nak.

Đị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 hay một bảng. Chức năng định dạng theo điều kiện trong Excel 2007 vừa được cải tiến, do vậy bài chỉ 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 hay một bảng, và làm thủ công công chuyện này có thể mất nhiều thời (gian) gian và dễ mắc lỗi. Để làm cho công chuyện 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 trả tất chuyện đị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 đất chỉ ô cần kiểm tra trong vùng đất 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 đất 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 vừa có sẵn chức 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 (hay 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

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

Trích:

HOANG NHI 2

Chắc hẳn bạn rất giỏi về tin học văn phòng?

hjx! tớ có giỏi đâu. cái này của 1 thầy giáo viết mà. tớ có ghi rõ ngựa nguồn rùi ak.

---------- Bài viết vừa được nhập tự động bởi hệ thống ----------

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

Dù cho công cụ Conditional Formating vừa 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 chức năng tìm ra những dữ liệu xuất hiện 2 lần hay nhiều hơn.

Nếu bạn muốn xác định những dữ liệu 2 lần hay 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 Use a Blueprint to actuate which beef to format” và bang chọn “Format ethics area this blueprint is true.” Đối với Excel 2003: Format, Conditional Formating, chọn Blueprint 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.

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