Một ngày một "tuyệt chiêu" trong Excel

tuy hoa mat!nhung minh se co.


Thank ban nhieu!

Xin Thank nhiều , thiệt là hay quá đi..!!

Chiêu thứ 21: Tô màu dòng xen kẽẮt hẳn bạn vừa từng thấy bảng tính Excel với những dòng tô màu cách nhau, thí dụ dòng chẵn thì tô màu xám, dòng lẻ không tô hay tô màu khác.


Trình bày bảng tính tô màu cách dòng như vậy khiến cho bảng tính có vẻ Pro và làm cho người dùng dễ đọc dữ liệu. Bạn có thể định dạng từ từ bằng tay từng dòng, nếu bạn đủ kiên nhẫn, nhưng bạn biết rồi đấy, kiên nhẫn 1 lần thì chưa đủ. Bạn sẽ phải bực mình khi cần xoá dòng, chèn dòng, cập nhật dữ liệu.


May sao, sử dụng Conditional Formatting có thể giúp bạn thực hiện tô màu nhanh và loại bỏ hết những phiền toái trên sau khi tô màu.



Giả sử bạn có dữ liệu trong vùng A1:H57, và bạn dự trù sẽ cập nhật cho đến hết tháng sẽ khoảng 100 dòng. Vậy bạn đánh dấu chọn khối A1:H100 sao cho ô A1 là ô hiện hành. Dùng cách như chiêu 18, 19, để vào chỗ cần gõ công thức của chức năng Conditional Formatting, và gõ công thức sau:


= Mod(Row(),2)


Nhấn format và chọn màu tô cho dòng lẻ. Nguyên nhân tô dòng lẻ: Công thức trên sẽ cho các giá trị lần lượt là 1, 0, 1, 0, … đến hết dòng 100, tương ứng với True, False, … và Conditional Formatting sẽ chỉ tô dòng True.





Cũng như vậy nếu bạn muốn tô màu cột cách cột, thì dùng công thức =Mod(Column(),2)





Ghi chú: Nếu bạn có sử dụng ASAP Utilities, bạn cũng có thể làm được như sau:


Vào menu ASAP – Row & Column – Color Each n’th row or column in selection





Chọn Row hay column tuỳ ý, chọn màu tuỳ ý, gõ 2 trong ô Steps, đánh dấu vào mục chọn Conditional Formatting.





Kết quả công thức của Condition là =MOD(ROW()-1,2*1)+1<=1


Công thức trên có vẻ hơi phức tạp nhưng nếu rút gọn là: = Mod(Row()-1,2)=0 thì hiệu quả tương tự như công thức trên. Sở dĩ nó phức tạp, là để có thể thay đổi phương pháp tô màu theo steps vừa chọn. Thí dụ khi chọn step = 3, cách 2 dòng tô 1 dòng, nghĩa là như hình sau: Chỉ tô dòng có Mod(Row()-1,3)=0





Mặc dù phương pháp này dễ thực hiện, nhưng không linh hoạt: Tất cả các dòng lẻ từ 1 đến 100 đều bị tô màu, dù có dữ liệu hay không. (nhớ lại, dữ liệu hiện tại của bạn chỉ đến dòng 57). Vậy có thể chỉ tô tự dộng đến hết dòng cuối có dữ liệu, cò những ô chưa có dữ liệu chỉ bị tô màu sau khi nhập liệu không? Hãy xem cách sau đây:

Tô màu động:


Chỉ cần bạn sửa công thức trên lại 1 xíu xiu:


=And( Mod(Row(),2),CountA($A1:$H1))


Chú ý chỗ tham chiếu tuyệt cú đối cột và tương đối dòng. Ý nghĩa công thức này là:


- khi bạn đứng ở dòng 1, đó là dòng lẻ và vùng A1:H1 có dữ liệu => tô màu.


- Nếu bạn đứng ở dòng 10, công thức trở thành =And( Mod(Row(),2),CountA($A10:$H10)) nghĩa là dòng chẵn và có dữ liệu => không tô màu


- Nếu bạn đứng ở dòng 59: dòng lẻ nhưng không có dữ liệu => không tô màu.


Mở rộng: Tô màu cách 2 dòng tô 1, hay tô xen kẽ 3 màu trở lên:


bạn chỉ cần sửa công thức ASAP cho phù hợp, kết hợp với CountA() cho linh hoạt. Thí dụ với các công thức như hình:





bạn sẽ có kết quả như hình, dòng nào không có dữ liệu thì không tô màu







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

Chiêu thứ 22: Tạo hiệu ứng 3D trong các bảng tính hay các ô
Bất cứ khi nào bạn nhìn thấy một hiệu ứng 3D trong một chương trình hay một ứng dụng, chẳng hạn như Excel, ắt hẳn bạn sẽ thấy một sự đánh lừa qua thị giác được làm ra (tạo) ra bởi cách định dạng đặc biệt. Chính bạn cũng có thể làm ra (tạo) ra hiệu ứng này một cách dễ dàng bằng cách định dạng một ô hay một dãy các ô. Phiên bản Excel 2007 vừa đưa vào các styles của ô, vì vậy bạn có thể làm ra (tạo) hiệu ứng 3D và lưu nó lại để sử dụng vào bất cứ khi nào bạn muốn.



Hãy bắt đầu với một ví dụ đơn giản, chúng ta sẽ làm ra (tạo) hiệu ứng 3D cho một ô để nó nhìn nổi lên trên tương tự như một nút lệnh(button).



Trong một bảng tính trống, bạn chọn ô D5. (Bạn chọn ô D5 bởi vì nó không nằm rìa bảng tính). Dưới Cell Options ở tab Home, chọn Format ➝ Format Cells ➝ Border (với phiên bản trước Excel 2007: Format ➝ Cells ➝ Border). hay các bạn có thể click chuột phải, chọn Format cells ➝ Border. Trong hộp line, chọn đường dày nhất thứ 2 (bên phải, thứ 3 từ dưới đếm lên). Chắc chắn rằng màu được chọn là màu đen (hay chọn automatic nếu bạn chưa thay đổi mặc định của lựa chọn này). Bây giờ click chọn đường viền bên phải và đường viền bên dưới ở bảng




Quay trở lại với box color để chọn màu trắng. Vẫn chọn đường viền dày thứ 2, và chọn hai đường viền còn lại là đường viền bên trên và bên trái của ô.




Chọn tab Fill (với phiên bản trước Excel 2007: chọn tab Patterns) trong hộp thoại Format Cells và chọn màu xám. Click chọn OK và thôi không chọn ô D5 nữa (di chuyển con trỏ chuột sang ô khác). Ô D5 sẽ xuất hiện nổi lên tương tự như một button. Bạn vừa làm tất cả điều đó chỉ với các đường viền và bóng đổ.




Nếu muốn vui hơn và đa dạng hơn, bạn làm ra (tạo) một ô nhìn tương tự như là bị lõm hay thụt vào trong thì chọn ô E5 (vì nó kế ô D5 và sẽ sử dụng cho bài tập tiếp theo). Chọn Home ➝ Cells ➝ Format ➝ Format Cells ➝ Bord (với phiên bản trước Excel 2007: Format ➝ Cells ➝ Border) và chọn đường viền dày nhất thứ 2 (thứ 3 bên phải từ dưới đếm lên) trong bảng line và chắc chắn màu được chọn là màu đen cho đường viền ở trên và bên trái. Chọn màu trắng trong bảng màu cho đường viền ở bên phải và bên dưới ô. Chọn tab Patterns và chọn màu xám. Click OK. Ô E5 sẽ bị lõm xuống. Việc này trái ngược với ô D5 là hiệu ứng trội lên.




Nếu bạn cảm giác thích thú với style của ô mà bạn vừa tạo ra. Bạn lưu lại bằng cách chọn Home ➝ Styles ➝ Cell Style ➝ NewCell Style, bạn gõ tên mà bạn muốn đặt cho style này vào và click OK. Chú ý rằng các Styles của ô chỉ được lưu lại ở workbook hiện tại mặc dù bạn có thể kết hợp các Styles này từ những workbooks khác. Lựa chọn này không có trong các phiên bản trước Excel 2007. Nếu bạn muốn lưu một style của ô trong các phiên bản trước thì bạn vào Format➝ Style.

Sử dụng hiệu ứng 3D cho một bảng dữ liệu



Kế tiếp, chúng ta sẽ làm thí nghiệm với công cụ này để thấy được các loại hiệu ứng 3D mà bạn có thể làm ra (tạo) ra cho các bảng hay các sheets của mình. Chọn ô D5 và E5, click chọn công cụ Format Painter (biểu tượng cái chổi quét ) dưới Clipboard options trên tab Home (với phiên bản trước Excel 2007: xem trên thanh standard toolbar). Trong khi nhấn chuột trái, Click chọn ô F5 và rê chuột qua ô J5, sau đó nhả ra.




Bây giờ chọn vùng D5:J5 và click chọn công cụ Format Painter một lần nữa. Cùng lúc nhấn chuột trái, chọn ô D6, kéo chuột ngang sang phải và kéo xuống ô J15, sau đó nhả ra.


Hiệu ứng 3D của một dãy ô


Nếu bạn muốn lưu style của bảng này, bạn chọn Home ➝ Styles ➝Format as Table ➝ NewTable Style. Với các phiên bản trước Excel 2007 thì bạn không thể lưu style của một bảng.



Chúng ta vừa sử dụng một đường viền dày để chắc rằng hiệu ứng được thấy một cách rõ ràng, tuy nhiên, bạn có thể làm ra (tạo) ra một sự tinh tế hơn bằng cách sử dụng một đưởng mảnh hơn.



Bạn cũng có thể sử dụng các đường khác để làm ra (tạo) ra những hiệu ứng hay hơn. Cách dễ nhất để tìm ra những sự kết hợp có hiệu quả là bạn nên thử và kiểm tra sai sót trên một bảng tính trống để làm ra (tạo) ra hiệu ứng mà bạn muốn. Bạn chỉ bị giới hạn bởi sự tưởng tượng và có lẽ là khiếu thẩm mỹ.





Thí dụ dùng màu khác





Thí dụ dùng đường kẻ không liền nét cho những đường kẻ ngang



Hãy luôn luôn nhớ rằng hiệu ứng 3D có thể làm cho bảng tính dễ đọc, nhìn có vẻ chuyên nghề và dễ cảm nhận hơn, nhưng khi chúng được sử dụng quá mức chúng có thể có những ảnh hưởng trái ngược. Nên nhớ, sử dụng tất cả thứ có mức độ.



Nếu bạn muốn làm ra (tạo) ra đi xa hơn và cung cấp những hiệu ứng 3D một cách tự động và sinh động, bạn có thể kết hợp 3D với định dạng có điều kiện để tự động cung cấp nhiều style mà bạn thích.

Bổ sung:



Bạn có thể dùng chính hiệu ứng 3D cho 1 ô (đã thực hiện ở trên) để dùng như 1 button thực thụ, nếu bạn kết hợp với VBA. Giả sử bạn muốn bảng tính của bạn có 1 button (nổi, đương nhiên), khi nhấn vào nó sẽ chìm xuống và thực thi 1 macro nào đó. Khi nhấn 1 lần nữa thì nút này sẽ nổi lên và thực hiện 1 macro khác hay dừng thực hiện macro thứ nhất.



Dùng công cụ ghi macro để ghi lại quá trình lập hiệu ứng 3D cho 1 ô D5, ta được 1 đọan code trong Module, sửa lại chút đỉnh với 1 biến Public IsRun để tuỳ trường hợp gán Border thích hợp:



PHP Code: Sub Change1()

    With Selection.Borders(xlEdgeLeft)

        .LineStyle = xlContinuous

        .Weight = xlMedium

        .ColorIndex = IIf(IsRun, 2, 0)

    End With

    With Selection.Borders(xlEdgeTop)

        .LineStyle = xlContinuous

        .Weight = xlMedium

        .ColorIndex = IIf(IsRun, 2, 0)

    End With

    With Selection.Borders(xlEdgeBottom)

        .LineStyle = xlContinuous

        .Weight = xlMedium

        .ColorIndex = IIf(IsRun, 0, 2)

    End With

    With Selection.Borders(xlEdgeRight)

        .LineStyle = xlContinuous

        .Weight = xlMedium

        .ColorIndex = IIf(IsRun, 0, 2)

    End With

End Sub 




Bây giờ phụ thuộc vào sự kiện SelectionChange của sheet, ta làm cho ô D5 thay đổi border như sau:


PHP Code: Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$D$5" Then

    If Target = "Run" Then

        Target = "Stop"

        IsRun = False

    Else

        Target = "Run"

        IsRun = True

    End If

    Change1

    Cells(1, 1).Select

End If

End Sub 


các bạn xem file đính kèm. Thí dụ này chỉ là minh hoạ, không có đoạn code nào được thực thi, nhưng ô D5 vừa trở thành 1 nút nhấn thực sự.


Code: Tập tin đính kèm:
http://www.mediafire.com/download.php?oaewnjyfkyg mã nguồn: giải pháp Excel (GPE)

Chiêu số 23: Bật, tắt chức năng Conditional Formatting bằng 1 checkbox
Bạn thường dùng Data Validation để giới hạn dữ liệu nhập vào, hay dùng Conditional Formatting để thông báo khi nhập dữ liệu trùng. Nhưng đôi khi bạn muốn tắt chúc năng thông báo của CF. Có 1 cách đơn giản để bật tắt chức năng này bằng cách sử dụng 1 checkbox. Xem thêm bài Chiêu số 18: Điều khiển Conditional Formating bằng checkbox.

Nhưng ở đây là CF tô màu dữ liệu nhập trùng.


Trong thí dụ dưới đây, bạn sẽ dùng CF để đánh dấu (tô màu) những ô có dữ liệu xuất hiện nhiều hơn 1 lần trong vùng dữ liệu. Giả sử trong vùng A1:A100 bạn vừa thiết lập CF với công thức như sau:

=CountIf($A$1:$A$100,$A1)>1


Bây giờ bạn muốn tuỳ ý bật tắt cái CF này. Trước tiên bạn cùng công cu Form hay Control Toolbox, vẽ 1 cái Checkbox lên sheet. Thiết lập Property Cell Link cho cái checkbox này là 1 ô nào đó, thí dụ K1. Ta vào Name box đặt nuôn tên cho ô này là IsCheck.


Bây giờ click chọn vùng dữ liệu A1:A100 sao cho ô A1 là ô hiện hành. Thiết lập Conditional Formatting cho vùng này với công thức sau:

=AND(COUNTIF($A$1:$A$100,$A1)>1,IsCheck)

Nhấn nút Format để tô màu theo ý muốn, rồi OK.


Sau khi trả thành, khi bạn check vào cái checkbox, ô K1 sẽ thành True, và những ô có dữ liệu trùng sẽ bị tô màu.

Trái lại, khi bạn uncheck cái checkbox, ô K1 sẽ có giá trị False, và chẳng có ô nào bị tô màu nữa.


Cũng như chiêu số 18, bạn trả toàn có thể dùng cái toggle Button làm 1 cái công tắc bật tắt y như cái checkbox.


Chắc cũng cần nói lại cách mà công tắc này hoạt động:


Do công thức của bạn là AND(điều kiện 1, điều kiện 2), nên chỉ cần 1 trong 2 điều kiện không thoả (=False), nguyên đk chung sẽ False, CF không hoạt động; vì CF chỉ có tác dụng khi điều kiện chung là True.


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