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

BNTT · 29/10/08

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ptm0412 · 30/10/08

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

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

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

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

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

.................

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

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

..................

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

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

............

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

(Còn tiếp)

ptm0412 · 30/10/08

Chiêu thứ 18 (tiếp theo)

2. Tắt mở định dạng màu cho ô:

Dùng Conditional Formating nhằm tô màu ô theo điều kiện giúp ta dễ tìm được những ô có giá trị đặc biệt cho trước. Excel 2007 có nhiều định dạng khác nhau cho giá trị số nằm trong khoảng cho trước. Nhưng biện pháp để mở tắt bằng checkbox là không có sẵn.

Tương tự như phần trên, ta tạo ra 1 checkbox hoặc 1 Toggle Button link tới ô $C$2. Nhưng lần này ta đặt name cho nó là IsFill chẳng hạn. Ta cũng đặt name cho ô $A$2 là BeginNum và $B$2 là EndNum, với A2 là giới hạn dưới thí dụ 100, và B2 là giới hạn trên thí dụ 1.000.

Trong vùng dữ liệu B5:B16, ta muốn giá trị nào nằm trong khoảng BeginNum và EndNum sẽ được tô màu. Vậy dùng conditional Formating như trên, chọn vùng C8:C18, lần này công thức là:

=AND($C8>=BeginNum,$C8<=EndNum,IsFill)

Chọn cho nó 1 định dạng màu theo ý muốn.

Kết quả: khi nhấn button hoặc click checkbox thay đổi trạng thái thành True, các ô chứa số trong khoảng (100, 1.000) sẽ được tô màu, các ô còn lại không tô. Khi thay đổi thành False, các ô trở lại bình thường.

Đồng thời, vì bạn đặt công thức liên quan đến BeginNum và EndNum, nên khi thay đổi 2 số này, kết quả tô màu cũng thay đổi.

............

Bạn thấy đấy, nếu bạn chưa xem bài này mà thấy 1 file tương tự của người khác, bạn có thể lầm tưởng người ta sử dụng code của VBA. Sự thực thì quá đơn giản phải không?

(Hết chiêu 18)

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

ptm0412 · 1/11/08

Chiêu số 19: Đánh dấu những ô chứa công thức bằng Conditional Formatting

Khi một ô có chứa dữ liệu, bạn có thể muốn biết dữ liệu trong ô đơn thuần là dữ liệu nhập vào, hay dữ liệu là kết quả của 1 công thức. Bạn có thể chỉ cần click chọn ô đó và xem trên thanh công thức. Bạn cũng có thể dùng phím tắt Ctrl + ~ để chuyển qua lại giữa chế độ xem giá trị và xem công thức.

Chiêu số 19 này sẽ giới thiệu với bạn 1 hàm tự tạo, kết hợp với Conditional Formatting để đánh dấu ô chứa công thức. Bằng cách này có thể giúp bạn tìm ra tất cả những ô chứa công thức trong số 10.000 ô mà không phải ngó từng ô một.

Mặc dù bạn có thể dùng 1 hàm có sẵn của Macro4 trong Conditional Formatting, như sau:
Trong hộp thoại Conditional Formatting, chọn công thức, gõ công thức này: = CELL(“type”,A1). Nhưng hạn chế của việc dùng hàm Cell() là công thức sẽ tự tính lại mỗi khi có sự thay đổi nhỏ xíu trong bảng tính. Vì Cell() là 1 hàm thuộc loại volatile. Khi Excel tính lại Cell() cho 10.000 ô như trên sẽ khiến cho bạn bực mình vì chờ đợi.

Do đó bạn hãy dùng tuyệt chiêu sau đây, đơn giản, dễ làm và không phải hàm loại volatile:

Bạn hãy nhấn Alt – F11 để vào cửa sổ VBA, nhấn chuột phải vào This Workbook để insert vào 1 module. Nhập đoạn code sau vào khung soạn thảo:

PHP:

 Function IsFormula (CheckCells As Range)
IsFormula = CheckCells.HasFormula
End Function

Do tính chất của Property HasFormula, hàm bạn mới tạo sẽ trả về các giá trị luận lý True, False. Nghĩa là khi bạn gõ vào ô bất kỳ công thức = IsFormula(A1) sẽ cho kết quả True nếu A1 chứa công thức và cho kết quả False nếu A1 chứa giá trị.

Đóng cửa sổ VBA lại, trở về bảng tính. Bây giờ đánh dấu toàn bộ vùng dữ liệu của bạn (có thể chọn dư ra một số cột và dòng, phòng khi bạn cập nhật thêm dữ liệu) sao cho ô A1 là ô hiện hành.

Bằng cách như chiêu số 18, bạn vào được chỗ cần thiết để gõ công thức trong hộp thoại Conditional Formatting, và gõ vào:
=IsFormula(A1), sau đó định dạng tô màu hoặc đổi màu chữ cho khác những ô còn lại.
Sau khi nhấn OK bạn sẽ được kết quả là tất cả những ô chứa công thức sẽ được tô màu. Nếu bạn thêm hoặc thay đổi 1 ô, nếu ô đó trở thành công thức thì lập tức ô đó đổi màu.

Đôi khi bạn không thấy kết quả, vì anh Bill lanh chanh và chậm hiểu, anh ta cho rằng công thức sử dụng hàm của bạn là 1 text nên ảnh tự sửa thành : =”IsFormula(A1)”. Vậy bạn phải vào chỗ cũ sửa lại.

Bây giờ mỗi khi bạn sửa hoặc thêm 1 ô trở thành công thức, ô đó sẽ có màu. ngược lại, nếu bạn sửa 1 công thức thành giá trị hoặc thêm giá trị vào 1 ô, ô đó sẽ không có màu.

Cái CF và cái UDF này đơn giản mà thực sự hữu ích, bạn nhỉ!

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

BNTT · 2/11/08

Chiêu thứ 20: Đếm hoặc cộng những ô đã được định dạng có điều kiện

Chúng ta thường hỏi: "Làm thế nào để tính toán với những ô đã được tô một màu cụ thể nào đó?" Câu hỏi này thường được nêu ra, bởi vì Excel không có một hàm bình thường nào để thưc hiện được nhiệm vụ này; tuy nhiên, nó có thể được thực hiện bằng một hàm tự tạo.

Vấn đề duy nhất xảy ra với việc sử dụng hàm tự tạo, là nó không thể lọc ra bất kỳ một loại định dạng nào đã được áp dụng bởi việc định dạng có điều kiện (conditional formatting). Tuy nhiên, suy nghĩ một tí, bạn vẫn có thể có được kết quả tương tự mà không phải cần đến một hàm tự tạo.

Giả sử rằng bạn có một danh sách dài những con số trong dãy $A$2:$A$100. Và bạn đã áp dụng định dạng có điều kiện cho dãy đó: đánh dấu những ô nào có giá trị nằm trong khoảng từ 10 đến 20. Bây giờ, bạn muốn lấy ra giá trị của những ô thỏa mãn điều kiện mà bạn đã thiết lập, và tính tổng của những ô đã được áp dụng định dạng đó. Không có gì khó! Bạn đừng để những kiểu định dạng đã được áp dụng chi phối bạn, nói cách khác, bạn không cần quan tâm những ô đó được định dạng kiểu gì. Bạn chỉ cần quan tâm đến điều kiện để áp dụng định dạng cho chúng (trong trường hợp này, là những ô có giá trị trong khoảng từ 10 đến 20).

Bạn có thể dùng hàm SUMIF() để tính tổng của những ô thỏa mãn điều kiện nào đó, nhưng chỉ một điều kiện mà thôi! Nếu muốn có nhiều điều kiện, bạn phải dùng hàm SUMIFS() trong Excel 2007, hoặc là dùng một công thức mảng. Ở đây tôi sẽ nói đến công thức mảng, vì nó có thể sử dụng trong hầu hết các phiên bản của Excel.

Với trường hợp đã ví dụ trong bài này, bạn sử dụng một công thức mảng giống như sau:

=SUM(IF($A$2:$A$100>10, IF($A$2:$A$100<20, $A$2:$A$100)))

Khi nhập một công thức mảng, bạn đừng nhấn Enter, hãy nhấn Ctrl+Shift+Enter. Khi đó, Excel sẽ tự động thêm một cặp dấu ngoặc ở hai đầu công thức, giống như vầy:

{=SUM(IF($A$2:$A$100>10, IF($A$2:$A$100<20, $A$2:$A$100)))}

Nếu bạn tự gõ cặp dấu ngoặc đó, thì công thức sẽ không chạy. Bạn phải để Excel làm việc này cho bạn.
Và bạn cũng nên biết điều này: sử dụng công thức mảng có thể làm cho Excel tính toán chậm hơn, nếu như có quá nhiều tham chiếu đến những dãy lớn.

Trên diễn đàn này có rất nhiều các bài viết về công thức mảng, bạn có thể tham khảo thêm. Hoặc nếu bạn giỏi tiếng Anh và thích nghiên cứu sâu hơn về công thức mảng, bạn hãy ghé thăm trang web này: http://www.ozgrid.com/Excel/arrays.htm.

Một cách khác

Ngoài việc sử dụng công thức mảng, bạn có thể dùng một cột phụ để tham chiếu đến những ô bên cột A. Những tham chiếu này sẽ trả về những giá trị của cột A mà thỏa mãn điều kiện bạn đã đặt ra (ví dụ: > 10, < 20). Để làm điều này, bạn theo các bước sau:

Chọn ô B2 và nhập vào đó công thức:

=IF(AND(A2>10, A2<20), A2, "")

Kéo công thức này xuống cho đến ô B100. Khi các công thức đã được điền vào, bạn sẽ có những giá trị nằm trong khoảng 10 đến 20 (xuất hiện trong cột B).

Thêm một chiêu phụ: Để nhanh chóng "kéo" các công thức vào trong một cột xuống đến ô cùng hàng với ô cuối cùng đã được sử dụng của cột ngay bên cạnh (trong trường hợp này, là "kéo" từ ô B2 đến ô B100, là ô tương ứng với ô cuối cùng đã được sử dụng trong cột A, ô A100), sau khi nhập công thức trong ô đầu tiên (ô B2), hãy chọn ô đó, rồi nhấp đúp chuột (double click) vào cái Fill handle (là cái núm chút xíu nằm ở góc dưới bên phải của ô được chọn, mà bạn vẫn thường dùng để "kéo" công thức)

Bây giờ, bạn có thể chọn bất kỳ một ô nào mà bạn muốn xuất hiện tổng của những giá trị thỏa mãn điều kiện đã đề ra, và sử dụng một hàm SUM bình thường (=SUM(B2:B100) chẳng hạn). Bạn có thể ẩn (Hide) cột B đi nếu bạn muốn.

Một cách khác nữa

Cách dùng cột phụ như tôi vừa nói, chắc chắn là chạy tốt rồi. Nhưng, Excel còn có một hàm cho phép bạn sử dụng hai hoặc nhiều điều kiện cho một dãy. Đó làm hàm DSUM().

Để thử nó, bạn dùng lại ví dụ ở trên: tính tổng của những giá trị trong dãy $A$2:$A$100 thỏa mãn điều kiện lớn hơn 10 và nhỏ hơn 20. Bạn hãy chọn các ô C1

2, đặt tên cho nó là SumCriteria. Rồi chọn ô C1, nhập vào đó công thức: =$A$1, tham chiếu đến ô đầu tiên của Sheet. Copy công thức đó sang ô D1, bạn sẽ có hai bản sao cho ô tiêu đề của cột A, và những ô này (C1, D1) sẽ được dùng như những ô tiêu đề của vùng điều kiện của hàm DSUM, vùng mà bạn đã đặt tên là SumCriteria (C1

2).

Trong ô C2, nhập vào biểu thức >10. Trong ô D2, nhập vào biểu thức <20. Rồi tại ô mà bạn muốn có kết quả là tổng của những giá trị thỏa mãn điều kiện vừa nêu, nhập vào công thức sau:

=DSUM($A$1:$A$100, $A$1, SumCriteria)

DSUM là một hàm có hiệu quả nhất khi bạn làm việc với những ô thỏa mãn nhiều điều kiện; và không giống như mảng, các Hàm cơ sở dữ liệu được thiết kế riêng cho những trường hợp này. Thậm chí khi chúng tham chiếu đến những dữ liệu rất lớn, làm việc với những con số lớn, thì ảnh hưởng của chúng đến tốc độ tính toán là rất nhỏ so với việc dùng công thức mảng.

Thêm một cách khác nữa

Cách này, tôi học được trên Giải pháp Excel: Dùng hàm SUMPRODUCT().

Cũng với bài toán tính tổng của những giá trị trong dãy $A$2:$A$100 thỏa mãn điều kiện lớn hơn 10 và nhỏ hơn 20. Bạn hãy chọn ô mà bạn muốn có kết quả là tổng của những giá trị thỏa mãn điều kiện vừa nêu, nhập vào công thức sau:

=SUMPRODUCT(($A$2:$A$100>10) * ($A$2:$A$100<20) * $A$2:$A$100)

Hoặc:

=SUMPRODUCT(--($A$2:$A$100>10), --($A$2:$A$100<20), $A$2:$A$100)

Diễn một cách bình dân, thì hàm SUMPRODUCT sẽ copy khối $A$2:$A$100 ra thành 3 mảng (trong bộ nhớ máy tính): Mảng thứ nhất, nếu giá trị trong một ô mà > 10, ô đó sẽ có giá trị là 1 (TRUE), còn không thì bẳng 0 (FALSE); Mảng thứ hai, cũng tương tự như vậy, nhưng áp dụng cho những ô có giá trị <20; và Mảng thứ 3 có giá trị trong mỗi ô bằng các giá trị tương ứng trong $A$2:$A$100.

Tiếp theo, SUMPRODUCT sẽ nhân từng nhóm 3 giá trị tương ứng trong mỗi mảng với nhau. Bạn sẽ thấy, chỉ khi nào giá trị trong mảng 1 và mảng 2 là 1, thì giá trị được nhân ra mới bằng giá trị tương ứng trong $A$2:$A$100; còn nếu có một giá trị nào đó trong mảng 1 hoặc mảng 2 mà bằng 0, thì kết quả của phép nhân này sẽ bẳng 0. Nói cách khác, chỉ những giá trị nào trong $A$2:$A$100 thỏa mãn điều kiện >10 và <20 thì mới được lấy ra.

Cuối cùng, SUMPRODUCT sẽ cộng hết các kết quả của phép nhân ở trên (SUM là phép tính tổng, PRODUCT là phép tính nhân, SUMPRODUCT là tổng của các tích), và đó chính là kết quả mà ta muốn có.

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

ptm0412 · 3/11/08

Chiêu thứ 21: Tô màu dòng xen kẽ

Ắt hẳn bạn đã 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ô hoặc 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 hoặc 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 đã 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 đố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, hoặc 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ỤC LỤC Những tuyệt chiêu trong Excel

ptm0412 · 8/11/08

All

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 hoặc 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 tạo ra bởi cách định dạng đặc biệt. Chính bạn cũng có thể tạo ra hiệu ứng này một cách dễ dàng bằng cách định dạng một ô hoặc một dãy các ô. Phiên bản Excel 2007 đã đưa vào các styles của ô, vì vậy bạn có thể 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ẽ tạo hiệu ứng 3D cho một ô để nó nhìn nổi lên trên giống 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). Hoặc 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 (hoặc 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 giống như một button. Bạn đã 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 tạo một ô nhìn giống như là bị lõm hoặc 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 thấy thích thú với style của ô mà bạn đã 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ể tạo ra cho các bảng hoặc 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 đã 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ể 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 để 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 để 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 nghiệp 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 mọi thứ có mức độ.

Nếu bạn muốn 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 hoặc 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:

Sub Change1()[/COLOR][/INDENT]
[COLOR=Navy]
[INDENT]    With Selection.Borders(xlEdgeLeft)[/INDENT]

[INDENT]        .LineStyle = xlContinuous[/INDENT]

[INDENT]        .Weight = xlMedium[/INDENT]

[INDENT]        .ColorIndex = IIf(IsRun, 2, 0)[/INDENT]

[INDENT]    End With[/INDENT]

[INDENT]    With Selection.Borders(xlEdgeTop)[/INDENT]

[INDENT]        .LineStyle = xlContinuous[/INDENT]

[INDENT]        .Weight = xlMedium[/INDENT]

[INDENT]        .ColorIndex = IIf(IsRun, 2, 0)[/INDENT]

[INDENT]    End With[/INDENT]

[INDENT]    With Selection.Borders(xlEdgeBottom)[/INDENT]

[INDENT]        .LineStyle = xlContinuous[/INDENT]

[INDENT]        .Weight = xlMedium[/INDENT]

[INDENT]        .ColorIndex = IIf(IsRun, 0, 2)[/INDENT]

[INDENT]    End With[/INDENT]

[INDENT]    With Selection.Borders(xlEdgeRight)[/INDENT]

[INDENT]        .LineStyle = xlContinuous[/INDENT]

[INDENT]        .Weight = xlMedium[/INDENT]

[INDENT]        .ColorIndex = IIf(IsRun, 0, 2)[/INDENT]

[INDENT]    End With[/INDENT]

[INDENT]End Sub

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

PHP:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)[/COLOR][/INDENT]
[COLOR=Navy]
[INDENT]If Target.Address = "$D$5" Then[/INDENT]

[INDENT]    If Target = "Run" Then[/INDENT]

[INDENT]        Target = "Stop"[/INDENT]

[INDENT]        IsRun = False[/INDENT]

[INDENT]    Else[/INDENT]

[INDENT]        Target = "Run"[/INDENT]

[INDENT]        IsRun = True[/INDENT]

[INDENT]    End If[/INDENT]

[INDENT]    Change1[/INDENT]

[INDENT]    Cells(1, 1).Select[/INDENT]

[INDENT]End If[/INDENT]

[INDENT]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 đã trở thành 1 nút nhấn thực sự.

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

ptm0412 · 10/11/08

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, hoặc dùng Conditional Formatting để cảnh báo khi nhập dữ liệu trùng. Nhưng đôi khi bạn muốn tắt chúc năng cảnh 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 đã 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 hoặc 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 hoàn 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 hoàn 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ỤC LỤC Những tuyệt chiêu trong Excel

ptm0412 · 11/11/08

Chiêu số 24: Dùng nhiều List cho 1 Combobox

Excel cung cấp cho bạn những cách để chọn 1 thứ trong 1 danh sách để nhập liệu vào 1 ô. Trong đó có công cụ combobox, từ danh sách xổ xuống của combobox, bạn có thể chọn 1 ngày trong tuần, 1 tháng trong năm, hoặc 1 sản phẩm trong danh sách. Nếu bạn cần chọn lựa trong 3 danh sách khác nhau, bạn sẽ nghĩ rằng bạn cần 3 cái combobox.

Thay vì vậy, bạn dùng chiêu sau đây, kết hợp 1 combobox với 3 option button. Trong thí dụ sau, bạn hãy tạo 1 bảng tính, điền số từ 1 đến 7 vào vùng A1:A7, điền từ chủ nhật đến thứ hai vào vùng B1:B7, và điền tháng từ tháng 1 đến tháng 7 vào vùng C1:C7.

Một cách nhanh nhất để điền 21 ô này là hãy gõ 1 vào A1. Trong khi nhấn giữ Ctrl, nắm cái fill handle của ô A1 và kéo xuống A7. Tiếp theo, gõ Sunday vào B2, doubled-click vào fill handle của B1. Cuối cùng gõ Jan vào ô C1, doubled-click vào fill handle của C1. Xong.

Bây giờ bạn vào Developer ➝ Controls ➝ Insert ➝ Form Controls (với Excel 2003, View ➝ Toolbars ➝ Forms) và nhấn icon Option Button. Vẽ 3 cái lên sheet. Vẽ thêm 1 cái Groupbox bao quanh 3 cái Option cho đẹp. Bây giờ vẽ 1 cái Combobox ở 1 chỗ thích hợp.

Nhấn chuột phải vào các Option, chọn Edit Text, sửa các từ Option1, Option 2, Option 3, Groupbox1 như hình:

Nhấn chuột phải vào 1 Option bất kỳ, chọn Format Control, trong tab Control, gõ $F$1 vào ô Cell Link.

Ở ô D6 gõ công thức =ADDRESS(1;$F$1)&":"&ADDRESS(7;$F$1)

Vào Define Name, đặt mới 1 name MyRange, công thức là =INDIRECT($D$6)

Nhấn chuột phải vào cái combobox, vào Format control, trong Cell link gõ MyRange, Cell link là $G$1.

Bây giờ khi bạn chọn 1 trong các option, ô F1 thay đổi các giá trị từ 1 đến 3, ô D6 thay đổi với các giá trị $A$1:$A$7, $B$1:$B$7, $C$1:$S$7. Và list trong combobox cũng đổi theo.

Bổ sung:

Có 1 vấn đề là 3 list không phải lúc nào cũng dài bằng nhau (bằng 7 trong thí dụ trên), thí dụ số từ 1 đến 10, thứ trong tuần từ Chủ nhật đến thứ bảy, tháng từ 1 đến 12. Vậy ta sẽ phải làm gì?

Ta hãy làm từ từ nhé, trước tiên là cách dùng ô phụ (tác giả cũng dùng 2 ô phụ).

Gõ công thức sau vào ô G1:

=COUNTA(INDIRECT(ADDRESS(1;$F$1)&":"&ADDRESS(100;$F$1)))

Với công thức trên, G1 sẽ có các giá trị là 10, 7, 12 tương ứng với F1 là 1, 2, 3.

Sửa tiếp số 7 chết trong công thức ô D6 thành $G$1 cho động:

=ADDRESS(1;$F$1)&":"&ADDRESS($G$1;$F$1)

Kết quả hoàn toàn mỹ mãn. Ô D6 sẽ lần lượt là $A$1:$A$10, $B$1:$B$7, $C$1:$C$12

Bây giờ là cách GPE: dấu (bỏ) các ô phụ.

Hiện Name MyRange của ta là =INDIRECT($D$6),

Sửa bước thứ nhất là thay $D$6 bằng công thức của D6:

=INDIRECT(ADDRESS(1;$F$1)&":"&ADDRESS($G$1;$F$1))

Kế đó thay $G$1 bằng công thức của G1:

=INDIRECT(ADDRESS(1;$F$1)&":"&ADDRESS(COUNTA(INDIRECT(ADDRESS(1;$F$1)&":"&ADDRESS(100;$F$1)));$F$1))

Ta có thể đàng hoàng xoá ô D6 và ô G1. Chỉ còn 1 ô F1 là link cell của 3 options, ta format nó thành chữ trắng là OK.

Trong file đính kèm dưới đây, tôi chưa xoá ô D6 và ô G1, để lại cho các bạn xem chơi, rồi từ từ xoá sau.

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

TranThanhPhong · 12/11/08

Chiêu thứ 25: Tạo một danh sách xác thực thay đổi theo sự lựa chọn từ một danh sách khác

Trước tiên, chúng ta cần chuẩn bị một số dữ liệu để thực hành bài này. Bạn hãy tạo một sheet mới và đặt tên là List và tại ô A1 nhập vào tiêu đề “Objects”. Tại ô B1 nhập vào tiêu đề “Corresponding List”. Vùng A2:A5 bạn nhập vào từ “Can”. Vùng A2:A9 nhập vào từ “Sofa”. Vùng A10:A13 nhập vào từ “Shower”. Vùng A14:A17 nhập vào từ “Car”. Sau đó, trong vùng B2:B17 bạn nhập vào các từ sau: Tin, Steel, Opener, Lid, Bed, Seat, Lounge, Cushion, Rain, Hot, Cold, Warm, Trip, Journey, Bonnet, và Boot.

Tại ô C1 nhập vào tiêu đề “Validation List”. Kế đó, bạn nhập vào các từ sau tại các ô C2:C5: Can, Sofa, Shower và Car. Đây là danh sách chứa các từ duy nhất từ vùng A2:A17.

Bạn có thể dùng chứa năng Advanced Filter để lọc ra danh sách duy nhất này bằng cách chọn vùng A2:A17 ➝ Data ➝ Sort & Filter ➝ Advanced (E2003: Data ➝ Filter ➝➝ chọn Unique Records Only ➝ chọn Filter the List in Place ➝ OK. Sau đó chép danh sách kết quả lọc duy nhất và dán vào vùng C2:C5. Advanced Filter)

Sau khi nhập xong bạn có danh sách như hình sau:

Bước tiếp theo là chúng ta sẽ định nghĩa một số Name cho các List trên. Bạn vào Formulas ➝➝ Name Manager ➝ chọn New (E2003: Insert ➝ Name ➝ Define). Trong hộp Name bạn nhập vào từ “Objects”. Tại hộp Refer To bạn nhập vào công thức sau: Define Names

=OFFSET($A$2,0,0,COUNTA($A$2:$A$20),1)

Nhấn nút OK sau khi nhập xong công thức trên. Tiếp theo, bạn nhấn New (E2003: nhấn Add) để tạo Name mới. Trong hộp Name nhập vào tên là “ValList” và tại Refer To nhập vào =$C$2:$C$5. Nhấn Close để đóng hộp thoại lại.

Bạn chèn thêm một sheet mới có tên là “Sheet1”. Bạn vào Formular ➝ chọn Define Names ➝Manager ➝ chọn New (E2003: Insert ➝ Name ➝ Define). Trong hộp Name bạn nhập vào “CorrespondingList” và tại Refer To nhập vào công thức sau: Name

=OFFSET(INDIRECT(ADDRESS(MATCH(Val1Cell,Objects,0)+1,2,,,"Lists")),0,0,COUNTIF(Objects,Val1Cell),1)

Tiếp tục nhấn New (hoặc Add trong Excel 2003) để thêm Name mới. Trong hộp Name bạn đặt tên là Val1Cell và tại Refer To nhập vào =$D$6 và nhấn OK.

Tiếp tục nhấn New để thêm Name mới với tên là Val2Cell và Refer To là =$E$6 nhấn OK sau khi hoàn tất.

Các Name mà bạn đã tạo như hình sau:

Chúng ta đã chuẩn bị xong các thứ cần thiết, bây giờ đã đến lúc áp dụng chức năng Data Validation. Bạn chọn ô $D$6 trên Sheet1, sau đó vào ngăn Data ➝ tại nhóm Data Tools bạn chọn Data Validation ➝ chọn ngăn Settings trong hộp thoại Data Validatin (E2003: Data ➝ Validation ➝ Settings). Bạn chọn loại List tại hộp Allow và tại hộp Source nhập vào =ValList, đánh dấu chọn In-cell dropdown, sau đó nhấn OK để hoàn tất.

Chọn ô E6, sau đó vào ngăn settings trong hộp thoại Data Validation như trên. Bạn chọn List tại Allow và nhập vào =CorrespondingList tại hộp Source, đánh dấu chọn vào In-cell dropdown và nhấn OK để hoàn tất.

Trong quá trình thiết lập Data Validation cho ô E6, bạn sẽ nhận được thông báo lỗi “The source currently evaluates to an error. Do you want to continue?”. Bạn hãy chọn Yes. Lỗi này xuất hiện là do ô D6 đang rỗng.

Bạn thử chọn một tên trong danh sách tại ô D6 và xem sự thay đổi tương ứng trong danh sách tại ô E6 như hình minh họa sau:

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

ptm0412 · 13/11/08

Chiêu 26: Sử dụng chức năng thay thế (Replace) để gỡ bỏ các ký tự không mong muốn.

Khi nhập dữ liệu hay khi sao chép và dán dữ liệu từ nguồn khác vào Excel thì các ký tự không mong muốn sẽ xuất hiện trong toàn bộ bảng tính của bạn. Sử dụng chiêu này bạn có thể khắc phục được những rắc rối khi gỡ bỏ các ký tự không mong muốn bằng tay. Chức năng thay thế (replace) trong Excel có thể giúp bạn gỡ bỏ các ký tự không mong muốn trong bảng tính, nhưng phải qua một vài bước phụ.

Ví dụ như, bạn có thể thay thế những ký tự không mong muốn bằng chuỗi rỗng tựa như nó chưa hề tồn tại. Muốn vậy bạn cần biết mã của từng ký tự mà bạn muốn gỡ bỏ. Tất cả các ký tự đều mang một mã riêng và Excel sẽ cho bạn biết nó là gì khi bạn sử dụng hàm CODE.

Hàm CODE sẽ trả về một mã số cho ký tự đầu tiên trong một chuỗi. Mã này tương đương ký tự mà máy tính của bạn đã thiết lập.

Để thực hiện điều này, chọn một trong các ô có chứa những ký tự không mong muốn. Từ thanh công thức, bôi đen ký tự và sao chép ký tự đó. Tiếp theo chọn ô trống bất kỳ (A1 chẳng hạn) và dán ký tự đó vào ô đã chọn (A1).

Tại ô khác, nhập công thức sau:

=CODE($A$1)

Công thức này trả về mã của ký tự không mong muốn.

Chọn toàn bộ dữ liệu của bạn, chọn Home ➝ Editing ➝ Find & Select ➝ Replace (với phiên bản trước Excel 2007: chọn Edit ➝ Replace…), ở khung Find what: nhấn phim Alt và gõ số 0 kèm theo code đã đưa ra bởi công thức trên. Nếu mã số là 163 thì nhấn Alt và nhấn 0163. (Hoặc bạn có thể để con trỏ chuột tại ô có ký tự không mong muốn, sao chép ký tự đó và dán vào ô Find what cũng được)

Bỏ trống khung Replace With và nhấn Replace all. Việc làm này sẽ xóa bỏ tất cả những ký tự không mong muốn rất nhanh qua việc dò tìm mã ký tự. Lặp lại các bước ở trên cho mỗi ký tự không mong muốn tiếp theo.

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

TranThanhPhong · 15/11/08

Chiêu thứ 27: Chuyển đổi con số dạng văn bản sang số thực

Các giá trị số (number) trong Excel được mặc định canh lề phải và văn bản (text) thì canh lế trái. Do vậy, cách đơn giản để nhận biết các giá trị số và văn bản trong một cột trên bảng tính là bạn thiết lập chế độ canh lề mặc định cho cột đó. Bạn vào Home ➝ nhóm Alignment ➝ chọn Format Cells ➝ vào ngăn Alignment (E2003: Format ➝ Cells ➝ Alignment) ➝ chọn General tại hộp Horizontal để thiết lập việc canh lề mặc định cho cột đang chọn ➝ nhấn OK để đóng hộp thoại Format Cells lại.

Bạn kéo cột rộng ra một ít để đễ phân biệt việc canh lề, khi đó bạn sẽ thấy các giá trị số, ngày tháng sẽ được canh lề phải và văn bản sẽ được canh lề trái.

Dùng Paste Special để chuyển giá trị số dạng văn bản sang số thực

Đây là cách nhanh và dễ dàng nhất để chuyển các giá trị số dang văn bản sang số thực. Các bước thực hiện như sau:

Chọn một ô trống nào đó và nhấn lệnh Copy (Ctrl + C)➝ quét chọn vùng số liệu dạng văn bản định chuyển đổi (ví dụ như vùng A1:A9 ở hình trên) ➝ nhấp phải chuột và chọn Paste Special… ➝ chọn Add tại nhóm Operation ➝ nhấn OK để hoàn tất.

Việc làm trên sẽ giúp chuyển toàn số các con số dạng văn bản sang số thực, vì ô rỗng có giá trị là 0 và khi bạn cộng bất kỳ số nào vào một con số lưu dưới dạng văn bản trong Excel thì bạn đã làm cho con số dạng văn bản chuyển thành số thực.

Dùng các hàm TEXT để chuyển đổi

Bạn có thể áp dụng nguyên tắc như cách trên vào một số hàm có sẵn của Excel để thực hiện việc chuyển đổi. Thông thường, khi bạn dùng một hàm thuộc nhóm TEXT và kết quả trả về dưới dạng con số thì Excel vẫn xem con số đó là giá trị dạng văn bản.

Giả sử bạn có một vùng dữ liệu A1:A7 như hình sau:

Bạn dùng hai hàm trong nhóm TEXT là LEFT và FIND để tách các giá trị ra khỏi các chuỗi văn bản như sau:

=LEFT(A1,FIND(" ",A1)-1)

Sau khi dùng hàm để tách phần giá trị ra thì các kết quả trả về vẫn được Excel xem như là văn bản vì chúng được canh lề trái như hình sau:

Do vậy, bạn cần phải hiệu chỉnh công thức tác chuỗi trên một ít để kết quả trả về là các con số thực sự bằng cách cộng thêm số 0 vào sau công thức trên:

=LEFT(A1,FIND(" ",A1)-1) + 0

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

ptm0412 · 16/11/08

Chiêu 28: Trích xuất dữ liệu số trong 1 chuỗi bằng VBA

Bạn thường lấy dữ liệu từ nguồn ngoài, chẳng hạn từ Internet, trong đó có dữ liệu số xen lẫn chữ như: “1,254.00VND” hoặc “USD 2,500.00”, thậm chí còn phức tạp hơn.

Và cũng có khi bạn đã nhập liệu hỗn hợp text và số không theo quy luật nào để có thể lấy riêng số ra bằng các hàm tách chuỗi thông thường.

Dùng 1 hàm tự tạo viết bằng VBA, bạn có thể trích xuất riêng phần số ra, dù cho chuỗi có kiểu dạng gì đi nữa.

Bạn hãy nhấn Alt-F11 để vào cửa sổ VBA, insert 1 module và dán đoạn code sau vào:

PHP:

Function ExtractNumber(rCell As Range)[/INDENT]
[INDENT]Dim lCount As Long[/INDENT]
[INDENT]Dim sText As String[/INDENT]
[INDENT]Dim lNum As String[/INDENT]
[INDENT]sText = rCell[/INDENT]
[INDENT]For lCount = Len(sText) To 1 Step -1[/INDENT]
[INDENT]If IsNumeric(Mid(sText, lCount, 1)) Then[/INDENT]
[INDENT]lNum = Mid(sText, lCount, 1) & lNum[/INDENT]
[INDENT]End If[/INDENT]
[INDENT]Next lCount[/INDENT]
[INDENT]ExtractNumber = CLng(lNum)[/INDENT]
[INDENT]End Function

Vào lại Excel, trong ô B1 gõ dữ liệu số xen lẫn text tuỳ ý, trong ô kế bên C1 gõ công thức:

=ExtractNumber(B1)

Ta sẽ có kết quả như hình:

Bổ sung:

Vẫn còn chút vấn đề: nếu dữ liệu là số thập phân như ô B5, hoặc dữ liệu gồm 2 nhóm số riêng biệt trở lên như ô B4, kết quả sẽ không như ý muốn.

1. Để giải quyết vấn đề số thập phân, ptm0412 có 1 hàm khác:

PHP:

Function CtoN(Mystr As String, Optional Dautp As String) As Double[/INDENT]
[INDENT]Dim Kqng, Kqtp, Neg  As Double, Kqtam As String[/INDENT]
[INDENT]Dim Sotp As Double, Le As Byte[/INDENT]
[INDENT]Neg = 1[/INDENT]
[INDENT]Le = 0[/INDENT]
[INDENT]For i = 1 To Len(Mystr)[/INDENT]
[INDENT]    tam = Mid(Mystr, i, 1)[/INDENT]
[INDENT]    Select Case tam[/INDENT]
[INDENT]        Case 0 To 9[/INDENT]
[INDENT]            Kqtam = Kqtam & tam[/INDENT]
[INDENT]        Case "-"[/INDENT]
[INDENT]            Neg = -1[/INDENT]
[INDENT]        Case Dautp[/INDENT]
[INDENT]            Kqng = Kqtam[/INDENT]
[INDENT]            Le = 1[/INDENT]
[INDENT]            Mystr = Right(Mystr, Len(Mystr) - i)[/INDENT]
[INDENT]            Kqtp = CtoN(Mystr)[/INDENT]
[INDENT]            Sotp = Kqtp * 10 ^ (-Len(Kqtp))[/INDENT]
[INDENT]        End Select[/INDENT]
[INDENT]Next i[/INDENT]
[INDENT]Select Case Le[/INDENT]
[INDENT]Case 0[/INDENT]
[INDENT]CtoN = IIf(Kqtam = "", 0, Kqtam)[/INDENT]
[INDENT]Case 1[/INDENT]
[INDENT]CtoN = Kqng + Sotp[/INDENT]
[INDENT]End Select[/INDENT]
[INDENT]CtoN = CtoN * Neg[/INDENT]
[INDENT]End Function

Ghi chú:- Khi sử dụng hàm này, bạn sẽ thêm vào hàm 1 tham số cho biết dấu thập phân là dấu nào, “,” hay “.”. Thí dụ =CtoN(“USD 14255.20”,”.”), và nếu bạn biết chắc là số nguyên thì không cần thêm.

- Hàm này đọc được cả số âm nếu ký hiệu số âm là dấu trừ và đứng trước số.

2. Để giải quyết vấn đề nhiều nhóm số khác nhau trong chuỗi, Ptm0412 cũng có 1 hàm:

PHP:

 Function CtoNPlus(Mystr As String, sttchuoi As Byte, Optional Dautp As String) As Double[/INDENT]
[INDENT]Newstr = Mystr[/INDENT]
[INDENT]For i = 1 To sttchuoi[/INDENT]
[INDENT]If Len(Newstr) < 2 Then Exit For[/INDENT]
[INDENT]CtoNPlus = CtoN1st(Newstr, Dautp)[/INDENT]
[INDENT]Next i[/INDENT]
[INDENT]Newstr = ""[/INDENT]
[INDENT]End Function

PHP:

Function CtoN1st(ByVal Mystr As String, Optional Dautp As String) As Double[/INDENT]
[INDENT]Dim Kqng, Kqtp, Neg  As Double, Kqtam As String[/INDENT]
[INDENT]Dim Sotp As Double, Le As Byte, NewStr2 As String[/INDENT]
[INDENT]Neg = 1[/INDENT]
[INDENT]Le = 0[/INDENT]
[INDENT]For i = 1 To Len(Mystr)[/INDENT]
[INDENT]    tam = Mid(Mystr, i, 1)[/INDENT]
[INDENT]    Select Case tam[/INDENT]
[INDENT]        Case 0 To 9[/INDENT]
[INDENT]            Kqtam = Kqtam & tam[/INDENT]
[INDENT]        If IsNumeric(Mid(Mystr, i + 1, 1)) = False And _[/INDENT]
[INDENT]        Mid(Mystr, i + 1, 1) <> "," And Mid(Mystr, i + 1, 1) <> "." Then[/INDENT]
[INDENT]      Newstr = Right(Mystr, Len(Mystr) - i)[/INDENT]
[INDENT][/INDENT]
[INDENT]        Exit For[/INDENT]
[INDENT]        End If[/INDENT]
[INDENT]        Case "-"[/INDENT]
[INDENT]            Neg = -1[/INDENT]
[INDENT]        Case Dautp[/INDENT]
[INDENT]            Kqng = Kqtam[/INDENT]
[INDENT]            Le = 1[/INDENT]
[INDENT]            NewStr2 = Right(Mystr, Len(Mystr) - i)[/INDENT]
[INDENT]            Kqtp = CtoN1st(NewStr2)[/INDENT]
[INDENT]            Sotp = Kqtp * 10 ^ (-Len(Kqtp))[/INDENT]
[INDENT][/INDENT]
[INDENT]        End Select[/INDENT]
[INDENT]Next i[/INDENT]
[INDENT]Select Case Le[/INDENT]
[INDENT]Case 0[/INDENT]
[INDENT]CtoN1st = IIf(Kqtam = "", 0, Kqtam)[/INDENT]
[INDENT]Case 1[/INDENT]
[INDENT][/INDENT]
[INDENT]CtoN1st = Kqng + Sotp[/INDENT]
[INDENT]End Select[/INDENT]
[INDENT]CtoN1st = CtoN1st * Neg[/INDENT]
[INDENT]End Function

Cú pháp hàm: CtoNPlus(Mystr , sttchuoi, [Dautp])

Sttchuoi là số thứ tự nhóm số trong chuỗi, Dautp là ký tự dấu phân cách thập phân.

Xem file kèm theo.

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

TranThanhPhong · 17/11/08

Chiêu thứ 29: Tùy biến chú thích của ô bảng tính

Khi chúng ta chèn chú thích cho ô bằng lệnh Review ➝ Comments ➝ New Comment (E2003: Insert ➝ Comment). Excel sẽ mặc định chèn tên người dùng của máy tính vào hộp chú thích. Bạn có thể thay đổi điều này bằng cách vào Office ➝ Excel Options ➝ Popular (E2003: Tools ➝ Options ➝ General). Bạn nhập lại đoạn văn bản mặc định mong muốn tại hộp User name:

Mặc dù chú thích cho ô có mục đích chính là hiển thị các thông báo cho bạn hoặc cho người dùng khác, bạn có thể tùy biến nó nhằm mục đích làm rõ hơn chủ ý của bạn.

Trước tiên, bạn cần tùy biến thanh Quick Access Toolbar (QAT) để thêm một nút lệnh vào đó, các bước thực hiện như sau: Office ➝ Excel Options ➝ Customize. Tại hộp Choose commands from bạn chọn Smart Art Tools | Format Tab ➝ nhấn nút Add để thêm vào QAT ➝ nhấn OK hoàn tất (E2003: gọi thanh Drawing bằng cách View ➝ Toolbars ➝ Drawing).
[FONT=&quot]
[/FONT][FONT=&quot]

[/FONT] Chèn một chú thích cho ô bằng cách vào ngăn Review ➝ tại nhóm Comments ➝ chọn New Comment (E2003: Insert ➝ Comment). Một hộp chú thích sẽ xuất hiện và bạn có thể nhập nội dung chú thích vào đó.

Để thay đổi hình dạng của hộp chú thích này bạn nhấp trái chuột lên khung viền của nó để thoát khỏi chế độ nhập liệu ➝ chọn lệnh Change AutoShape từ thanh QAT ➝ chọn kiểu hình dạng mong muốn trong các nhóm hình Basic Shapes, Block Arrow, Flow Chart, Callouts và Stars and Banners ➝ sau khi chọn hình thì hình dạng hộp chú thích của ô sẽ thay đổi tức thì.

Đối với phiên bản Excel 2007 thì việc hiệu ứng 3-D cho hộp chú thích được thiết lập mặc định và các tùy chọn Shadow Setting đã bị loại bỏ. Bạn có thể thay đổi các tùy chọn đổ bóng, hiệu ứng 3-D trong phiên bản Excel trước đó như sau: chọn khung viền của hộp chú thích ➝ vào thanh Drawing ➝ chọn nút lệnh Shadow Settings ➝ chọn kiểu mong muốn:

Thêm ảnh vào chú thích

Một điều cũng khá thú vị là bạn có thể dùng các hộp chú thích để hiển thị các ảnh mà không làm ảnh hưởng đến các nội dung khác. Thí dụ như, bạn có thể chèn một đồ thị vào hộp chú thích nhằm minh họa tốt hơn cho các số liệu mà không cần phải lúc nào cũng hiện đồ thị lên.

Để thêm hình, bạn chọn ô đang có chú thích ➝ nhấp phải chuột lên ô ➝ chọn Edit Comment ➝ chọn khung viền của hộp chú thích ➝ nhấp phải chuột ➝ chọn Format Comment (E2003: nhấp chuột 2 lần liên tiếp lên khung viền của hộp chú thích) ➝ chọn ngăn Colors and Lines ➝ tại hộp Color, chọn Fill Effects ➝ chọn nút Select Picture ➝ chọn hình và nhấn Insert ➝ OK ➝ OK.

[FONT=&quot]

[/FONT] Trích xuất nội dung chú thích

Để trích xuất nội dung trong hộp chú thích, chúng ta cần viết một hàm người dùng đơn giản. Bạn nhấn tổ hợp ALT+F11 để vào cửa sổ VBE hoặc vào ngăn Developer ➝ Code ➝ Visual Basic (E2003: Tools ➝ Macro ➝ Visual Basic Editor), sau đó vào Insert ➝ chọn Module ➝ nhập vào đoạn mã sau:

[highlight=VB]
Function GetCommentText(rCommentCell As Range)
Dim strGotIt As String
On Error Resume Next
strGotIt = WorksheetFunction.Clean(rCommentCell.Comment.Text)
GetCommentText = strGotIt
On Error GoTo 0
End Function
[/highlight]

Vào File ➝ Save (Ctrl+S) để lưu Module, sau đó vào File ➝ Close and Return to Microsoft Excel (ALT+Q) để trở về cửa sổ bảng tính. Bạn vào một ô trống nào đó và nhập vào:

=GetCommentText(B2)

[FONT=&quot]Với B2 là ô đang có chú thích. Nhấn Enter để xem kết quả.[/FONT]

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

ptm0412 · 18/11/08

Chiêu 30: Sort thứ tự dựa trên nhiều hơn ba cột

Chức năng sort của Excel bị giới hạn với sort trong phạm vi 3 cột. Trong hầu hết các trường hợp, sort theo 3 cột là đủ nhưng đôi khi bạn cần sort nhiều hơn 3 cột dữ liệu. Chiêu này sẽ giúp bạn vượt qua hạn chế này.

Với ví dụ sau, chúng tôi giả sử bạn có các cột dữ liệu liên quan với nhau A, B, C, D và E, và bạn muốn sort dữ liệu này đầu tiên theo cột A, tiếp theo là cột B, kế đến là cột C, tiếp nữa là cột D và cuối cùng là cột E.

Để làm điều này, bạn sort lần lượt theo thứ tự ngược: sort theo cột cuối cùng trước và tiếp theo tuần tự ngược đến cột đầu tiên. Chọn các cột từ A đến E, sau đó chọn Data ➝ Sort. Chọn để sort theo thứ tự cột C đầu tiên, tiếp theo là đến cột D và sau đó đến cột E. Nhấn vào sort. Bây giờ chọn các cột từ A đến E và chọn Data ➝ Sort. Lúc này, sort theo thứ tự cột A trước rồi mới đến cột B. Click sort và mọi thứ sẽ được sắp xếp theo thứ tự.

Excel đã sort theo 5 cột thay vì 3 cột. Nếu bạn muốn Excel tự động làm điều này, bạn có thể sử dụng một macro mà sẽ sort theo vùng chọn và giả định rằng dữ liệu của bạn có các tiêu đề cột được xác định và định dạng ở hàng đầu tiên của vùng chọn. Nếu các tiêu đề được in đậm, Excel sẽ cho biết chúng là các tiêu đề và sẽ không sort chúng. Thay vào đó, nó sẽ sort cột đầu tiên bên trái trước rồi mới tới cột bên phải cho đến tối đa là 256 cột.

Đoạn code macro bạn cần sử dụng phải được đặt trong một module chuẩn. Để làm điều này, bạn chọn Tools ➝ Macro ➝ Visual Basic Editor (Hoặc nhấn Alt+F11), sau đó chọn Insert ➝ Module và gõ đoạn code sau vào:

PHP:

Sub SortByX( )
Dim i As Long
For i = Selection.Columns.Count To 1 Step -1
Selection.Sort Key1:=Selection.Cells(2, i), _
Order1:=xlAscending, Header:=xlGuess, Orientation:=xlTopToBottom
Next i
End Sub

Để quay lại excel, bạn đóng cửa sổ code này lại hoặc nhấn Alt+ Q. Ngay khi bạn dán đoạn code này vào, bạn đã có thể thực hiện được việc sort này: Tô chọn vùng cần sort, kể cả tiêu đề, rồi chạy code.

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

TranThanhPhong · 19/11/08

Chiêu thứ 31: Sắp xếp ngẫu nhiên

Bạn có thể dùng Excel để chọn ra một cách ngẫu nhiên ba người thắng cuộc – 1, 2 và 3 từ một danh sách trên bảng tính. Để thực hiện điều này một cách dễ dàng nhất và công bằng nhất, bạn nên dùng hàm RAND kết hợp với tính năng sắp xếp của Excel.

Giả sử bạn có một bảng dữ liệu như hình sau:

Tại ô A2 bạn nhập vào hàm =RAND và sao chép xuống đến ô A10. Bạn có thể sắp xếp các cột Name, Age và ID No. theo cột A và từ đó chọn ra được ngẫu nhiên 3 người thắng cuộc.

Hàm RAND sẽ được tự động tính lại mỗi khi có sự thay đổi trên bảng tính, do vậy chúng ta có thể nhấn F9 để ép Excel tính toán lại và từ đó chọn ra được những phần tử trong danh sách một cách ngẫu nhiên. Tuy nhiên chúng ta cần phải tạo một thủ tục sắp xếp lại dữ liệu sau khi Excel tạo ra bộ số ngẫu nhiên mới.

Để việc chọn lựa được dễ dàng, chúng ta sẽ ghi một Macro và gán nó vào một nút lệnh trên bảng tính. Mỗi khi muốn chọn ra nhóm người thắng cuộc thì bạn chỉ cần nhấn nút lệnh này. Bạn làm theo hướng dẫn sau:

Bước này bạn sẽ tiến hành ghi Macro, bạn chọn một ô bất kỳ và vào Developer ➝ Code ➝ Record Macro (E2003: Tools ➝ Macro ➝ Record New Macro…). Chọn 4 cột A, B, C và D rồi nhấn F9. Vào Data ➝ Sort & Filter ➝ Sort ➝ chọn sắp xếp cột A theo thứ tự tùy ý (nhỏ ➝ lớn hoặc lớn ➝ nhỏ).

Bước tiếp theo, bạn hãy vào ngăn Developer ➝ nhóm Controls ➝ Insert (E2003: View ➝ Toolbars ➝ Forms) ➝ chọn Button (Form Control) và vẽ thành một nút trên bảng tính ➝ chọn tên Macro vừa ghi ➝ nhấn OK để hoàn tất.

Bạn chọn cột A và ẩn nó để người dùng không thấy các số ngẫu nhiên. Mỗi lần bạn nhấp chuột lên nút Pick Winner thì dữ liệu của bạn sẽ được sắp xếp một cách ngẫu nhiên và bạn có thể dễ dàng chọn ra 3 người thắng cuộc.

Lưu ý: Hàm RAND trong các phiên bản trước Excel 2003 có tồn tại lỗi trong việc phát số ngẫu nhiên. Về mặt lý thuyết, hàm RAND chỉ trả về các giá trị từ 0 đến 1, điều này không phải luôn luôn đúng khi phát nhiều số ngẫu nhiên vì đôi khi hàm trả về giá trị nhỏ hơn 0. Xem thêm thông tin về việc Microsoft thay đổi giải thuật phát số ngẫu nhiên trong các phiên bản Excel 2003 & 2007 tại http://support.microsoft.com/default.aspx?kbid=828795.

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

ptm0412 · 21/11/08

Chiêu 32: Thao tác trên dữ liệu với Advanced Filter

Nếu bạn đã quen với công cụ Auto Filter, bạn hẳn đã thấy những hạn chế của AutoFilter. Nếu bạn cần xử lý chọn lọc dữ liệu ngoài giới hạn này, Advanced Filter là 1 chọn lựa tốt.

Dù cho bị hạn chế, AutoFilter cũng đã là 1 công cụ hữu ích để chỉ thể hiện 1 phần dữ liệu lên màn hình theo 1 vài điều kiện nào đó. Nhưng đôi khi bạn không thể chọn lọc thông tin cần thiết khi dùng AutoFilter.

Xin nói thêm, Excel 2003 và kể cả Excel 2007 chỉ có thể lọc 1 lần 2 điều kiện (And hoặc Or) cho 1 cột. Hơn thế, nếu bạn muốn lọc dữ liệu theo 2 tiêu chí trên 2 cột, thì chỉ có thể kết hợp bằng toán tử And giữa các điều kiện của 2 cột.

Advanced Filter sẽ hữu dụng hơn nhiều. Tuy nhiên nếu bạn muốn dùng AdF, bạn phải sắp xếp dữ liệu đúng chuẩn như lời nói đầu (bài 1, 2, 3):

- chừa ít nhất 3 dòng trống trên cùng bảng dữ liệu

- Dùng 1 dòng duy nhất làm tiêu đề bảng dữ liệu

- Không merge bất cứ ô nào của bảng dữ liệu

Khi sử dụng AdF, bạn sẽ phải dùng tiêu đề cột làm tiêu chí lọc, nên bạn sẽ phải copy tiêu đề cột dán vào chỗ nào đó trên dòng 1. Nhưng tốt nhất bạn nên dùng 1 công thức chẳng hạn như =A4 để bảo đảm rằng bất cứ lúc nào điều kiện lọc cũng đúng, dù cho bạn có thay đổi tiêu đề cột.

Dùng công thức này cho tất cả các cột mà bạn muốn làm tiêu chí lọc.

Sau đó ngay dưới mỗi tiêu đề (dòng 2, và 3) bạn để những điều kiện lọc. Những điều kiện lọc sẽ dùng những toán tử so sánh như: =, >, <, >=, <=, <>.

Ghi nhớ 1 điều rằng những điều kiện Or cần sắp xếp theo chiều đứng, những điều kiện And sẽ sắp theo hàng ngang. Do đó nếu bạn cần lọc theo 2 điều kiện And và cùng 1 tiêu chí, thì phải dùng 1 tiêu đề cột trên 2 ô.

Sau đây là các hình minh hoạ:

Các điều kiện Or:

Các điều kiện And:

Kết hợp 2 And và 1 Or: (MLuong<700 And MLuong>400 And BPhan=”KT”) Or BPhan=”HC”

Kết hợp 2 And và 2 Or: (MLuong<700 And MLuong>400 And BPhan=”KT”) Or (MLuong<700 And MLuong>400 And BPhan=”HC”)

Trên đây chỉ là những thí dụ đơn giản, mà bạn có thể sử dụng AutoFilter.

Bây giờ ta áp dụng AdF cho vài thí dụ mà AutoFilter không làm được

(Còn tiếp)

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

ptm0412 · 21/11/08

Chiêu 32: Thao tác trên dữ liệu với Advanced Filter (tiếp theo)

Bây giờ ta áp dụng AdF cho vài thí dụ mà AutoFilter không làm được:

Bất cứ khi nào áp dụng AdF cho điều kiện là công thức như các trường hợp dưới đây, bạn phải nhớ không dùng tiêu đề của bảng dữ liệu làm tiêu đề cho điều kiện. Bạn hãy đặt 1 tên cho điều kiện, hoặc để trống.

Thí dụ 1: Bạn muốn lọc dữ liệu cột mức lương với những giá trị thoả điều kiện nhỏ hơn mức lương trung bình, thì dùng công thức sau: =D6<AVERAGE($D$6:$D$22), kết quả sẽ là True hoặc False, bạn đừng quan tâm, mà cứ dùng AF, ô D2 là giá trị trung bình tính sẵn 468,8235 để bạn tiện theo dõi:

Bạn cũng có thể kết hợp với điều kiện bộ phận là KT:

Lưu ý rằng D6 là 1 tham chiếu tương đối trong khi $D$6:$D$22 là 1 tham chiếu tuyệt đối. Điều này là cần thiết để Excel dò tìm lần lượt D6, D7, … trong vùng cố định $D$6:$D$22, hễ giá trị nào của D6, D7, … mà có kết quả True thì cho hiện, False thì ẩn dòng. Đó là cách mà anh Bill ta thực thi AdF.

Thí dụ 2: Nhận thấy rằng có những mức lương mà nhiều người hưởng, và có những mức lương mà chỉ 1 người có, ta muốn lọc ra những người có mức lương trùng với 1 người nào khác, ta dùng công thức như sau:

=COUNTIF($D$6:$D$22;D6)>1

Còn nếu muốn lọc những người có mức lương độc, chỉ 1 người có, ta dùng công thức:

=COUNTIF($D$6:$D$22;D6)=1

Bây giờ nếu bạn muốn lọc ra chỉ những mức lương không trùng nhau, cho ra 1 cột khác thì làm như sau: khi mở hộp thoại AF, hãy click vào ô Copy to another location, click chọn vào ô Unique records only, chọn thêm ô sẽ copy sang ở ô Copy To, và để trống vùng Criteria:

Kết quả:

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

ptm0412 · 23/11/08

Chiêu 33: Tạo các định dạng số cho riêng bạn

Excel đã có những định dạng số của riêng nó nhưng thỉnh thoảng bạn cần sử dụng một định dạng số không có sẵn trong Excel. Sử dụng chiêu này, bạn có thể tạo ra các định dạng số mà bạn có thể điều chỉnh cho nhu cầu riêng của bạn.

Trước khi bạn thử những chiêu này, sẽ có ích cho bạn nếu bạn hiểu được làm thế nào Excel nhận biết được các định dạng của ô. Excel nhận biết một định dạng của ô khi có 4 phần sau (từ trái sang phải): số dương, số âm, giá trị 0, và giá trị chuỗi. Mỗi phần được ngăn cách bởi một dấu chấm phẩy (

.

Khi bạn tạo ra một định dạng số cho riêng bạn, bạn không cần phải định dạng tất cả 4 phần này. Nói cách khác, nếu định dạng của bạn chỉ có hai phần thì phần đầu tiên được sử dụng cho cả các số dương và các giá trị 0, trong khi phần thứ 2 sẽ được sử dụng cho số âm.

Nếu định dạng của bạn chỉ bao gồm một phần, tất cả các loại số sẽ dùng một định dạng. Chuỗi bị ảnh hưởng bởi các định dạng riêng chỉ khi bạn sử dụng tất cả 4 phần, phần cuối sẽ sử dụng cho chuỗi .

Đừng cho rằng những định dạng riêng chỉ áp dụng chỉ cho các dữ liệu kiểu số. Các định dạng số cũng áp dụng cho các dữ liệu kiểu chữ.

Định dạng riêng cho số được hiển thị ở hình 2-18 là định dạng tiền tệ chuẩn của Excel, nó chỉ ra số tiền âm là màu đỏ.

Nếu bạn gõ một số dương tương ứng với một giá trị tiền tệ, Excel sẽ định dạng nó ngay lập tức bao gồm dấu phẩy (,) ngăn cách hàng ngàn, theo sau hai số lẻ thập phân.

Excel sẽ định dạng tương tự cho giá trị âm, ngoại trừ số âm được hiển thị là màu đỏ. Bất kỳ giá trị 0 nào cũng sẽ không có ký hiệu tiền tệ và sẽ được hiển thị hai số lẻ thập phân (0.00). Nếu bạn gõ một chuỗi vào một ô, Excel sẽ hiển thị dòng chữ “Chuỗi hiển thị,” bất kể giá trị thật sự của chuỗi bạn gõ vào là gì.

Điều quan trọng là phải chú ý, việc định dạng một giá trị của ô không ảnh hưởng đến giá trị đích thực của ô.

Ví dụ, gõ bất kỳ số nào vào ô A1. Nhấn chuột phải và chọn Format Cells ➝ Number ➝ Custom, và sử dụng bất kỳ định dạng nào ngay tại con trỏ chuột đầu tiên (trong hộp "Type), gõ "Hello" (với dấu ngoặc kép ("")). Sau đó nhấn OK.

Mặc dù ô hiển thị từ "Hello" nhưng bạn có thể thấy giá trị thật sự của ô bằng cách chọn ô và xem trên thanh công thức (Formula bar), hoặc nhấn F2. Nếu bạn tham chiếu ô này trong một công thức - ví dụ, =A1+20 - kết quả của ô sẽ dẫn đến định dạng lúc nãy, nghĩa là sẽ trả về kết quả là "Hello" (bạn hãy làm thử, bạn sẽ thấy kết quả ngay tức khắc!)

Nếu bạn đã tham chiếu ô A1 cùng với những ô khác mà có bất kỳ định dạng chuẩn Excel nào - ví dụ, =Sum(A1:A10)- kết quả trả về sẽ vẫn theo định dạng riêng của ô A1.

Excel đưa ra một giả thiết đã được thiết lập trước rằng bạn muốn kết quả của ô đã được định dạng giống như cách tham chiếu đến một hay nhiều ô. Nếu các ô tham chiếu chứa nhiều hơn một loại định dạng thì bất cứ định dạng riêng nào cũng sẽ được ưu tiên trước.

Điều này có nghĩa là, bạn phải luôn luôn nhớ rằng Excel sử dụng một giá trị thật sự của ô để tính toán, và sẽ không lấy giá trị thể hiện của nó.

Điều này có thể tạo ra ngạc nhiên khi Excel tính toán dựa trên các ô đã được định dạng không có số lẻ thập phân hoặc có một vài số lẻ thập phân, thí dụ:

Để thấy được điều này, bạn gõ 1.4 vào ô A1 và 1.4 vào ô A2, định dạng cho cả hai ô là không có số lẻ thập phân theo sau bằng cách bạn chọn vùng A1:A2, click chuột phải, chọn Format Cells ➝ Number, trong khung "decimal places" chọn 0. Sau đó gõ công thức =A1+A2 vào một ô khác. Dĩ nhiên kết quả trả về sẽ là 3 vì Excel đã làm tròn.

Excel có một lựa chọn được gọi là “Precision as Displayed”, bạn có thể tìm thấy bằng cách chọn Office button ➝ Excel Options ➝ Advanced (Với phiên bản trước Excel 2007, chọn Tools ➝ Options ➝ Calculation), nhưng bạn nên biết rằng định dạng này sẽ thay đổi vĩnh viễn các giá trị trong các ô từ đầy đủ các số lẻ thập phân (gồm 15 ký số) đến bất kỳ định dạng nào, bao gồm các số lẻ thập phân, được thể hiện. Nói cách khác, khi bạn đánh dấu chọn “Precision as Displayed” và nhấn OK thì nó sẽ không trả lại kết quả ban đầu. (Bạn có thể thử nhưng các thông tin về các số lẽ thập phân mở rộng sẽ bị mất đi.)

Ghi chú thêm: để hiểu thêm điều này, bạn làm ví dụ như sau ô A1 bạn gõ 1.53, A2: 2.75, A3: 5.68, A4: 3.25, A5: sum(A1:A4). Kết quả bạn thấy được ở ô A5 là 13.21. Giờ bạn vào Office button ➝ Excel Options ➝ Advanced và chọn "Set precision as display". Chọn vùng A1:A4, bạn nhấn nút Decrease Decimal , bạn sẽ thấy phần thập phân bị làm tròn dần lên. Khi còn 1 số thập phân thì tổng là 13.30; khi không còn chữ số thập phân nào nữa thì tổng là 14. Bây giờ, bạn lại dùng nút Increase Decimal, bạn sẽ thấy phần số lẻ ở sau các số ban đầu bạn gõ vào đã trở thành sô 0 hết. Đây là điều bạn nên chú ý khi lựa chọn tùy chọn này. Tuy nhiên nếu ở các ô A1:A4 là công thức thì dữ liệu ban đầu của bạn vẫn còn nguyên. Ví dụ, thay vì gõ trực tiếp ô A1 là 1.53, bạn gõ =1.53 và tương tự như thế cho các ô khác và làm theo các bước hồi nãy và kiểm tra kết quả bạn sẽ thấy có sự khác biệt.

(Còn tiếp)

ptm0412 · 23/11/08

Chiêu 33: Tạo các định dạng số cho riêng bạn (Tiếp theo)

Định dạng mặc định cho tất cả các ô trong Excel là General. Nếu bạn gõ một con số vào một ô, Excel thường sẽ đoán định dạng số nào là phù hợp nhất. Ví dụ, nếu bạn gõ 10% vào một ô, Excel sẽ định dạng ô đó là kiểu Percentage. Trong hầu hết các trường hợp thì Excel đoán khá chính xác nhưng thỉnh thoảng bạn cần thay đổi lại.

Khi sử dụng định dạng cho các ô, hãy tránh việc bị cám dỗ để định dạng canh trái, canh phải hay canh giữa. Theo mặc định các số sẽ được canh phải và chuỗi sẽ được canh trái. Nếu bạn bỏ điều này, bạn có thể nhận biết thoáng qua dù cho ô đó là chuỗi hay số vì trong trường hợp ví dụ trước, ô A1 bạn thấy là chuỗi ("Hello") nhưng thực sự nó là một số.

Mỗi phần của một định dạng được đưa ra sử dụng những mã định dạng riêng của nó. Các mã này ảnh hưởng đến Excel trong việc thể hiện dữ liệu theo cách mà bạn muốn. Vì vậy, thí dụ, giả sử bạn muốn số âm xuât hiện trong ngoặc đơn(), và tất cả các số, số dương, số âm và số 0 đều hiển thị 2 số lẻ thập phân.

Để làm điều này, bạn click chuột phải chọn Format Cells, tab Number mục Custom, ở khung Type bạn gõ: 0.00_ ;(-0.00)

Nếu bạn muốn số âm màu đỏ, bạn làm tương tự như trên nhưng trong khung Type bạn gõ: 0.00_ ;[Red](-0.00)

Chú ý là hãy sử dụng dấu ngoặc vuông ([]) trước code. Code sử dụng cho định dạng nói cho Excel biết để tạo ra số màu đỏ. Bạn có thể sử dụng nhiều code định dạng khác nhau trong các mục ở Format cells/number/custom.

Bảng 2-1 đến bảng 2-5 được lấy từ tài liệu của Microsoft đã giải thích những code này.

Bảng 2.1: các mã về định dạng số:

Bảng 2.2: Các mã định dạng chuỗi

Bảng 2.3: Các mã về định dạng ngày tháng năm

(Còn tiếp)

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

Bùi Nguyễn Triệu Tường

Bad Excel Member

Bad Excel Member

Bad Excel Member

Bùi Nguyễn Triệu Tường

Bad Excel Member

Bad Excel Member

File đính kèm

Bad Excel Member

Bad Excel Member

File đính kèm

Ngày mai trời lại sáng!

File đính kèm

Bad Excel Member

Ngày mai trời lại sáng!

File đính kèm

Bad Excel Member

File đính kèm

Ngày mai trời lại sáng!

Bad Excel Member

Ngày mai trời lại sáng!

File đính kèm

Bad Excel Member

Bad Excel Member

Bad Excel Member

Bad Excel Member