Những "tuyệt chiêu" trong Excel

kyo

Nguyễn Khắc Duy
Thành viên BQT
Administrator
Tham gia ngày
4 Tháng sáu 2006
Bài viết
900
Thích
2,597
Điểm
910
#81

Chiêu thứ 72: Tạo số tổng cộng cho biểu đồ cột nhiều thành phần (Stacked Column Chart)

Khi bạn sử dụng biểu đồ cột nhiều thành phần (Stacked Column Chart), thật không dễ dàng gì để thể hiện số tổng cộng lên từng cột. Và chiêu thứ 72 ra đời để giúp bạn hoàn thành công việc này. Tuy nhiên, trước khi bắt đầu, chúng ta cần dữ liệu mẫu và biểu đồ cột nhiều thành phần. Bạn hãy tạo dữ liệu và biểu đồ như hình dưới đây:



Tiếp theo, bạn hãy chọn series “Tổng cộng” -> nhấn chuột phải -> chọn Change Chart Type (với Excel 2003, nhấn vào biểu đồ để chọn nó -> Chart -> Chart Type) -> chọn biểu đồ đường đầu tiên. Thế là biểu đồ mới của bạn sẽ có dạng như sau:



Kế đó, bạn cần đưa số liệu lên biểu đồ bằng cách nhấn vào biểu đồ để chọn đường biểu đồ bạn vừa tạo -> nhấn chuột phải -> Add Data Labels (với Excel 2003, nhấn chuột phải -> Format Data Series -> Labels -> Show Value -> OK).



Tuy nhiên, bạn sẽ thấy các số liệu hình như nằm hết ở bên phải chứ không phải phía trên. Do đó, bạn nhấn chọn mấy con số vừa hiện ra -> nhấn chuột phải -> Format Data Labels -> tại thẻ Label Options, phần Label Position, chọn Above -> Close (với Excel 2003, chọn một trong các số -> nhấn chuột phải -> Format Data Labels -> Alignment và chọn Outside End tại Label Position).



Cuối cùng, bạn cần xóa đi đường trên biểu đồ. Một lần nữa bạn nhấn chuột phải vào đường trên biểu đồ -> Format Data Series -> chọn thẻ Line Color -> chọn No Line -> Close (với Excel 2003, nhấn chuột phải vào đường biểu đồ -> Format Data Series -> chọn thẻ Patterns -> Line -> None -> OK).

Để đẹp hơn, bạn hãy nhấn chuột từ từ hai lần vào series “Tổng cộng” và xóa nó đi. Như vậy, biểu đồ của bạn sau khi hoàn thành sẽ như thế này:


 

kyo

Nguyễn Khắc Duy
Thành viên BQT
Administrator
Tham gia ngày
4 Tháng sáu 2006
Bài viết
900
Thích
2,597
Điểm
910
#82

Chiêu thứ 73: Thêm mô tả vào công thức của bạn

Kể khi bạn tự viết những công thức hay hàm tự tạo cho mình thì khi bạn xem lại vào một dịp nào sau đó, bạn sẽ cảm thấy rất khó khăn để hiểu ý nghĩa công thức của bạn. Bạn sẽ phải dò từng ô, từng vùng và xem mối liên kết của nó với các ô hay vùng khác để biết công thức của bạn đang làm gì. Và chắc chắn, bạn sẽ thầm ước có một chỉ dẫn nho nhỏ nào đó để mỗi lần như vậy, bạn nhìn vào và hiểu được bạn đã từng làm gì.

Có một vấn đề là, khi bạn thêm chữ vào công thức, đa phần các hàm sẽ trả về kết quả là lỗi hoặc nó sẽ bỏ qua những dòng chữ đó. Tuy vậy, đừng vội lo lắng, Excel có cung cấp cho bạn một hàm chuyên giúp bạn thêm các mô tả vào công thức, đó là hàm N. Khi bạn sử dụng hàm này, Excel đơn thuần là biến chuỗi của bạn thành số. Giả sử nếu bạn dùng =N(“GPE”) thì kết quả bạn nhận được là 0.

Ví dụ, bạn có thể kết hợp sử dụng hàm N như thế này: =A1*B1+N("Tính doanh thu sản phẩm A").

Tất nhiên, bạn cần lưu ý hàm N sẽ chuyển chuỗi của bạn thành số 0 nên nếu bạn sử dụng =A1*B1*N("Tính doanh thu sản phẩm A"), bạn sẽ gặp rắc rối vì kết quả luôn bằng 0.

Tóm lại, bạn có thể sử dụng hàm N như một cách để gợi nhớ nhằm giúp bạn dễ thở hơn trong việc dò tìm sau này.
 

kyo

Nguyễn Khắc Duy
Thành viên BQT
Administrator
Tham gia ngày
4 Tháng sáu 2006
Bài viết
900
Thích
2,597
Điểm
910
#83

Chiêu thứ 74: Di chuyển công thức chứa địa chỉ tương đối mà không làm thay đổi tham chiếu.

Khi công thức của bạn chứa địa chỉ tuyệt đối (được biểu thị bằng dấu $, như $A$1), bạn có thể thoải mái di chuyển công thức mà không phải lo tham chiếu bị thay đổi. Tuy nhiên, vấn đề là, nhiều lúc bạn quên đặt địa chỉ tương đối và sau khi thao tác rất nhiều công đoạn, dữ liệu của bạn đã lên đến con số hàng ngàn, bạn sẽ không thể từ từ đến từng ô để thêm dấu $ được. Vậy bạn sẽ làm gì?



Có một cách giúp bạn di chuyển công thức nhưng không làm thay đổi tham chiếu, bạn có thể làm như sau:

1/ Chọn vùng hay ô bạn muốn copy.
2/ Chọn thẻ Home -> tại Editting, chọn Find & Select -> Replace (Excel 2003, chọn Edit -> Replace) hoặc nhấn Ctrl + H.
3/ Tại dòng Find What:, gõ =
4/ Tại dòng Replace With, gõ & hay bất kỳ một ký hiệu nào mà bạn chắc chắn là không có trong công thức.
5/ Nhấn nút Option bên dưới, bạn phải bảo đảm rằng bạn không đánh dấu check vào Match entire cell contents -> nhấn Replace All.



6/ Tất cả công thức của bạn sẽ được thay đổi từ dấu = sang dấu &. Bây giờ, bạn dễ dàng và thoải mái copy vùng bạn vừa thay đổi đến một nơi khác tùy thích.



7/ Sau đó, công việc cuối cùng là bạn hãy quét chọn vùng bạn vừa chuyển qua, vùng công thức chứa dâu & và sử dụng Replace để chuyển ngược lại từ dấu & thành dấu =.



Khi bạn kết thúc công việc, các tham chiếu của bạn sẽ vẫn được giữ như lúc ban đầu.


 
Lần chỉnh sửa cuối:

kyo

Nguyễn Khắc Duy
Thành viên BQT
Administrator
Tham gia ngày
4 Tháng sáu 2006
Bài viết
900
Thích
2,597
Điểm
910
#84

Chiêu thứ 75: Đối chiếu dữ liệu cũ và mới.

Bạn vừa cập nhật bảng tính và bạn cần xác định sự khác biệt giữa bản cập nhật và bản gốc. Để có thể làm được điều này, có 2 cách giúp bạn vừa thực hiện tốt vừa tiết kiệm thời gian cho việc kiểm tra thủ công tẻ nhạt, nhưng quan trọng hơn hết, bạn sẽ loại trừ được khả năng sai sót trong công việc.

Chúng ta sẽ bắt đầu với ví dụ sau đây.
Để dễ dàng theo dõi, dữ liệu mới đã được sao chép vào cùng 1 bảng tính với dữ liệu cũ trước đó. Các ô tô đậm trong bảng 2 là các ô đã thay đổi so với bảng 1.


Cách 1: Sử dụng True/False

Ở cách 1, bạn sẽ nhập công thức mảng vào 1 vùng bất kì có cùng kích thước và định dạng với bảng tính của bạn. Ưu điểm của cách này là bạn có thể thêm công thức trong 1 bước mà không cần phải Copy và Paste.

Giả sử, bạn chọn vùng E1:G7 và bắt đầu với ô E1 (cần đảm bảo rằng E1 là ô đang được chọn trong vùng). Nhấn chuột vào thanh Formula và gõ công thức : =A1=A9. Sau đó, nhấn Ctrl-Enter.


Vùng lựa chọn E1:G7 được lấp đầy với True, nếu giá trị ở 2 bảng giống nhau và ngược lại, False nếu khác nhau.

Vì sao phải nhấn Ctrl-Enter cùng lúc? Bằng cách nhấn cùng lúc 2 phím Ctrl và Enter, bạn sẽ có công thức tham chiếu tương đối vào từng ô của vùng chọn. Đây là cách thông thường để nhập 1 công thức vào 1 mảng các ô. Ta sẽ nhận được kết quả là các số tham chiếu thay đổi cách thích hợp. Mảng các ô đó gọi là mảng nhập công thức.

Nếu dữ liệu mới và cũ của bạn nằm trên 2 bảng tính khác nhau, bạn có thể sử dụng 1 bảng tính thứ 3 để chứa các giá trị True/False, chỉ với công thức mảng đơn giản. Ví dụ, giả sử bảng dữ liệu mới nằm ở Sheet 2 và bắt đầu bằng ô A9. Bảng dữ liệu cũ vẫn ở Sheet 1 và bắt đầu bằng ô A1. Trên Sheet 3, bạn có thể nhập công thức mảng sau : =Sheet1!A1=Sheet2!A9

Cách 2: Sử dụng Conditional Formatting

Conditional Formatting thường được ưa thích, vì khá dễ dùng và dễ so sánh mỗi khi có bất kì sự thay đổi nào. Tuy nhiên, cách làm này đòi hỏi cả 2 dữ liệu mới – cũ phải nằm trên cùng 1 bảng tính.

Lấy lại ví dụ trên, giả sử, vùng A1 : C7 là vùng bạn chọn. và bạn bắt đầu từ ô A1. Cách làm như sau :
1/ Chọn thẻ Home -> Styles -> Conditional Formatting -> New Rule.


2/ Trong New Formatting Rule, chọn Use a formula to determine which cells to format.
Ở bên dưới, tại Format values where this formula is true, bạn gõ công thức sau : =NOT(A1=A9).
Nhấn nút Format để định dạng theo ý thích, nhằm làm nổi bật sự khác biệt.


3/ Nhấn OK. Và kết quả bạn nhận được sẽ là:


Bạn thấy đó, tất cả sự khác biệt giữa 2 bảng dữ liệu mới – cũ đã thay đổi theo định dạng bạn chọn.
Từ bây giờ, mỗi lần bạn thay đổi dữ liệu, định dạng của các ô sẽ tự động thay đổi theo. Muốn định dạng của ô tự động trở lại bình thường, bạn chỉ việc thay đổi nội dung chứa trong các ô sao cho giống với các ô trong bảng dữ liệu lúc ban đầu.
 

kyo

Nguyễn Khắc Duy
Thành viên BQT
Administrator
Tham gia ngày
4 Tháng sáu 2006
Bài viết
900
Thích
2,597
Điểm
910
#85

Chiêu thứ 76: Không bỏ sót ô rỗng.

Mọi người thường sẽ bỏ trống 1 ô nào đó khi dữ liệu cho ô đó giống với dữ liệu của ô trên nó. Việc này làm cho danh sách dễ nhìn, dễ đọc, nhưng về mặt cấu trúc thì không được tốt lắm. Chiêu thứ 76 sẽ giúp bạn điền vào tất cả những ô trống theo danh sách cách nhanh chóng và dễ dàng.

Đa phần các chức năng của Excel được thiết kế để sử dụng trên các danh sách. Để các chức năng có thể làm việc chính xác, danh sách không được chứa bất kỳ ô trống, và tiêu đề cột nên được định dạng khác với các dữ liệu trong danh sách. Công việc thiết lập dữ liệu trong Excel sẽ trôi chảy hơn nếu bạn thực hiện đúng những yêu cầu trên. Tuy nhiên, nhiều danh sách lại được thiết lập như hình dưới đây.


Trong khi Giá cả được lặp đi lặp lại nhiều lần, các loại trái cây trong cột Trái cây lại không được lặp lại như vậy. Như đã nói ở trên, điều này sẽ tạo ra nhiều vấn đề khi sử dụng các tính năng như Subtotals và PivotTables. Trong hầu hết các trường hợp, Excel hy vọng các dữ liệu của bạn sẽ được thiết lập trong 1 danh sách liên tục hoặc trong 1 bảng, và không có ô trống. Vậy làm thế nào để điền hết các ô trống mà không bỏ sót?

Bạn có thể điền vào các ô trống trong danh sách trên bằng cách sử dụng công thức hoặc bằng macro.

Cách 1: Sử dụng Công thức

Lấy lại ví dụ trên, bạn sẽ làm như sau:

1/ Chọn tất cả các dữ liệu trong cột A. Sau đó chọn Home -> Editing -> Find & Select -> Go To Special (có thể dùng phím tắt: Ctrl-G, nhấn vào Special).


2/ Chọn tùy chọn Blanks và nhấn OK.


Và kết quả bạn sẽ chọn được các ô trống trong danh sách như hình.


3/ Nhấn phím =, rồi lại nhấn phím mũi tên lên, và cuối cùng Ctrl-Enter. Bạn có thể hiểu hành động này nhằm mục đích cho Excel hiểu đối với những ô rỗng thì sẽ mang công thức = giá trị của ô liền kề phía trên, giả sử như trong hình dưới đây thì ô A3 sẽ mang công thức =A2.


4/ Bây giờ để chuyển đổi từ công thức thành giá trị, bạn chọn cột A, nhấn chuột phải và chọn Copy (Ctrl-C). Nhấn chuột phải lần nữa và chọn Paste Special…, nhấn vào ô Values, sau đó nhấn OK.

Cách 2 : Sử dụng Marco

Công việc đòi hỏi bạn phải điền vào ô trống thường xuyên, tốn nhiều thời gian quá. Bạn cần phương án tối ưu hơn. Tại sao chúng ta không xử lý vấn đề này thông qua macro? Đoạn macro sau đây sẽ giúp bạn xử lý công việc dễ dàng hơn.

1/ Để sử dụng macro, bạn nhấn đồng thời 2 phím Alt-F11, sau đó chọn Insert -> Module.

2/ Nhập đoạn mã sau vào hộp thoại:

Mã:
Sub FillBlanks()
    Dim rRange1 As Range, rRange2 As Range
    Dim lReply As Integer
        If Selection.Cells.Count = 1 Then
            MsgBox "Ban can chon mot vung va vung do can co o rong"
            Exit Sub
        ElseIf Selection.Columns.Count > 1 Then
            MsgBox "Vung cua ban chi co the trong 1 cot duy nhat"
            Exit Sub
        End If
        Set rRange1 = Selection
        On Error Resume Next
        Set rRange2 = rRange1.SpecialCells(xlCellTypeBlanks)
        On Error GoTo 0
        If rRange2 Is Nothing Then
            MsgBox "Khong co o rong duoc tim thay"
            Exit Sub
        End If
        rRange2.FormulaR1C1 = "=R[-1]C"
        lReply = MsgBox("Ban co muon Paste Value cho cac o rong?", vbYesNo + vbQuestion)
        If lReply = vbYes Then rRange1 = rRange1.Value
End Sub
3/ Sau khi nhập mã xong, đóng lại và trở về Excel, lưu lại Bảng tính này.

4/ Bây giờ, vào thẻ Developer, chọn Code -> Macros hoặc dùng phím tắt Alt-F8. Sau đó, chọn Macro FillBlanks và Run.


LƯU Ý: Phải chọn danh sách có chứa những ô trống trước khi mở hộp thoại Macro và nhấn nút Run, và chỉ chọn duy nhất 1 cột có chứa những ô trống để thực hiện
lệnh.
 

Tường_Vi

Thành viên tích cực
Tham gia ngày
19 Tháng tư 2010
Bài viết
479
Thích
120
Điểm
395
#87
Em đã xem code VBA trong #11, tạo chỉ mục, nhưng tốc độ rất chậm mỗi lần active sheet INDEX. Mình có đoạn code nào nhanh hơn không ạ?

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
 
Top