Tổng hợp những "tuyệt chiêu" trong Excel

[kyo]

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:

​

MỤC LỤC Những tuyệt chiêu trong Excel
​

 

[kyo]

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.

MỤC LỤC Những tuyệt chiêu trong Excel
​

 

[kyo]

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.

​

MỤC LỤC Những tuyệt chiêu trong Excel
​

 

[kyo]

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.

MỤC LỤC Những tuyệt chiêu trong Excel​

 

[kyo]

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:

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.

MỤC LỤC Những tuyệt chiêu trong Excel​

 

[hoangexcell]

Những tuyệt chiêu này có lưu file ở đâu không các Anh!

 

[Tường_Vi]

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

 

[iloveit]

Chiêu thứ 2: Nhập dữ liệu đồng thời vào nhiều sheet.

Thông thường người dùng Excel có những dữ liệu muốn nhập vào nhiều sheet khác nhau. Bạn có thể dùng công cụ Group các sheet lại để có thể nhập dữ liệu đồ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ụ Group cái đã nhỉ? Công cụ Group dùng để nhập dữ liệu vào nhiều sheet 1 lúc chắc có nhiều người chưa dùng đến. Nó dùng để kết nối các sheets với nhau trong phạm vi work book.

1. Group các sheet lại bằng tay:

Để nhóm các sheet lại, các bạn chỉ việc click chọn sheet 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 sheet khác mà bạn muốn nhập liệu đồng thời. Khi bạn gõ bất kỳ cái gì vào sheet hiện hành, dữ liệu đó cũng sẽ tự động nhập vào các sheet còn lại trong nhóm. Điệp vụ hoàn tất nhanh chóng (nghe giống 007 nhỉ).

Để thoát khỏi tình trạng group, bạn chỉ việc hoặc click chọn 1 sheet khác ngoài nhóm, hoặc click chuột phải lên tên 1 sheet trong nhómbất kỳ, chọn Ungroup Sheets. Hoặn nếu bạn chỉ muốn gỡ 1 trong số các sheet ra khỏi nhóm, hãy Ctrl click vào tên sheet đó.
Khi 2 hay nhiều sheet Group 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 group các sheets.

View attachment 224450

Trừ khi bạn có cặp mắt sắc như chim ưng, hoặc bén như dao cạo, thường thì bạn không để ý đến dấu hiệu này, bạn sẽ quên và không ungroup khi công việc đã xong. Chính vì thế tôi 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ớ group và ungoup khi cần, nếu không bạn sẽ vô tình ghi đè lên dữ liệu ở những sheet mà bạn không 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 sheet phải khác nhau. Thế nhưng khi các sheet đã group rồi thì nó có thèm quan tâm bạn đang nhập liệu ở vùng nào đâu?

2. Group các sheet 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 code VBA hết sức đơn giản. Để đoạn code làm việc được, nó phải được đặt trong các sự kiện của riêng sheet nào bạn muốn. Để vào trang soạn thảo code của sheet nào, thì click phải chuột vào tên sheet đó 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 sheet đó 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 code VBA của mình.

Thường thì bạn được đưa thẳng đến trang soạn thảo code riêng của sheet nếu bạn chọn View Code như trên hướng dẫn. Bạn có thể kiểm tra lại quả thực nó thuộc về sheet bạn chọn hay không 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 sheet đang chọn thì đúng. nếu không bạn phải tìm đúng sheet bạn cần và doubled click 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 code này vào khung soạn thảo bên phải:

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

View attachment 224451

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

Khi bạn đã hoàn tất, mỗi khi bạn click chọn 1 ô nào đó trong sheet5, đoạn code sẽ kiểm tra xem ô bạn chọn có nằm trong vùng đã đặt tên “MyRange” không. Nếu đúng, code sẽ Group các sheet lại với nhau để bạn nhập dữ liệu hàng loạt sheets. Nếu không phải, nó ungroup chúng ra bằng cách đơn giản là kích hoạt sheet hiện hành. Dùng đoạn code trên sẽ tiện lợi ở chỗ nó giải phóng bạn khỏi cái việc phải nhớ Group 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 sheet chọn, nhưng ở những vị trí khác nhau, hãy dùng đoạn code khác:

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 code này cũng phải được đặt trong khung soạn thảo của riêng sheet5.

View attachment 224452

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

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

MỤC LỤC Những tuyệt chiêu trong Excel​

nếu sheet5 có sử dụng Data Validation ở cột B chẳng hạn, thì cột B ở sheet khác không có dữ liệu tương ứng.

Xin hỏi mọi người cách khắc phục như thế nào. Xin cám ơn