Viet' Library

Hơn tất cả những gì bạn có!

Các thủ thuật Excel

Xem bai viet bang font ABC
* Làm đẹp hình ảnh nền
Khi dùng hình ảnh làm nền cho sơ đồ trong Excel đôi khi bạn nhận thấy hình ảnh này trông rất xấu. Làm sao có thể cải thiện để nó đẹp hơn?
Nguyên nhân là vì Excel chưa làm gì để điều chỉnh sơ đồ có hình ảnh. Hãy nạp Excel và nhập số liệu vào. Chọn số liệu này rồi nhấn Insert.Chart. Khi khung hội thoại Chart Wizard xuất hiện, nhấn Finish. Nhấn phím phải vào vùng sơ đồ rồi chọn Format Chart Area. Nhấn Fill Effects trong khung hội thoại vừa xuất hiện rồi nhấn tiếp mục Pictures trong khung hội thoại Fill Effects. Bây giờ nhấn Select Pictures rồi nhấn đúp chuột vào hình ảnh bạn muốn dùng làm nền cho sơ đồ.
Trở về khung hội thoại Fill Effects, nhấn OK và OK lần nữa trong khung hội thoại Format Chart Area. Cuối cùng, dùng chuột điều chỉnh kích thước sơ đồ để hình ảnh được đẹp hơn.

* Hàm Proper trong Excel hoạt động như thế nào?
Tôi thử dùng hàm PROPER của Excel và nghĩ rằng nó bị lỗi. Ví dụ, tôi nhập vào ô A1 nội dung THử DùNG HàM PROPER rồi nhập vào ô A2 =proper(A1). Tôi muốn có kết quả trong ô A2 là “Thử dùng hàm proper” nhưng kết quả lại là Thử Dùng Hàm Proper. Đây có phải là lỗi không?
Không. Hàm Proper có chức năng chuyển ký tự đầu tiên của một từ thành chữ hoa và chuyển những ký tự còn lại thành chữ thường. Vì thế kết quả trên là thích hợp.

* Tìm kiếm trong Excel
Chắc bạn đã biết cách dùng nhiều lần khung hội thoại Find trong Word nhưng chưa bao giờ nghĩ đến việc dùng khung hội thoại này trong Excel. Khung hội thoại Find trong Word và Excel có phần khác nhau. Giả sử bạn cần tìm tất cả các hàm SUM xuất hiện trong bảng tính. Chọn Edit.Find (hoặc nhấn <Ctrl>-F). Gõ SUM vào khung hội thoại Find rồi bỏ chọn mục “Find entire cells only”. Nhấn Find Next để tiếp tục.
Lưu ý rằng bạn có thể tìm theo cột, theo dòng. Khi lệnh Find tìm thấy chuỗi ký tự cần tìm, nó chọn ô chứa chuỗi ký tự đó.
Nếu cần tìm một chuỗi cụ thể ví dụ như =SUM(A1:A5), bạn có thể dùng khung hội thoại Find để tìm theo công thức hoặc giá trị bằng cách chọn Formulas hay Values trong khung Look in.

* Vẽ hình trùng với đường kẻ ô trong Excel
Chức năng AutoShapes của Excel giúp bạn xếp những hình bạn vẽ trùng với những đường kẻ ô trong bảng tính. Cách dễ nhất để thực hiện điều này là giữ phím <Alt> trong khi vẽ.
Muốn thử chức năng này, chạy Excel rồi mở một bảng tính mới. Nếu chưa thấy công cụ Drawing trên màn hình, chọn View.Toolbars. Drawing. Khi đã có công cụ Drawing, nhấn vào hình tượng hình chữ nhật (giả sử bạn muốn vẽ một hình chữ nhật).
Bây giờ giữ phím <Alt> trong khi kéo chuột để vẽ một hình chữ nhật. Bạn sẽ thấy các cạnh của hình chữ nhật trùng với các đường kẻ ô trong bảng tính.

* Vẽ hình vuông và bầu dục nằm trong cell
Khi sử dụng chức năng vẽ hình vuông và bầu dục trên thanh công cụ Draw, hình vẽ sẽ không nằm trên bất cứ một vùng cell nào trên bảng tính. Để hình vẽ nằm trong cell, khi vẽ bạn nhấn giữ phím Alt, hình vẽ sẽ đi theo từng cell khi bạn rê chuột.

* Sao chép Formula
Khi bạn sử dụng công thức tính toán nào đó trong một cell bên trên và lại muốn sao chép tiếp xuống cell bên dưới thì bạn chỉ cần nhấn tổ hợp phím Ctrl – D để thực hiện

* Hiển thị thời gian hiện hành
Nếu muốn hiển thị thời gian hiện hành lên một cell nào đó thì bạn có thể sử dụng công thức tính là Now() – Today(). Nếu bạn muốn hiển thị thời gian trong một cell nào đó cùng với dòng thông báo là “Bây giờ là : “ thì bạn nhập vào cell như sau : =”Bây giờ là : “ &TEXT( NOW ( ) ,”h:mm AM/PM”)

* Các tham số giả lập trong Excel
Bạn cảm thấy khó khăn khi phải nhớ tất cả cú pháp của các hàm trong Excel? Để không phải nhớ bạn có thể chọn Insert/Function, chọn một hàm và điền tham số vào khung hội thoại. Muốn mọi việc nhanh hơn, gõ tên hàm rồi nhấn CTRL + A để dán các tham số giả lập vào. Ví dụ: nếu gõ “=PMT” vào một ô rồi nhấn CTRL + A, bạn sẽ có công thức =PMT(rate,nper,pv,fv,type). Thay thế các hàm giả lập bằng tham số thực tế sau đó thực hiện hàm.

* Tập lái máy bay với Excel 97
Nếu bạn quá mệt mỏi khi phải làm việc với các bảng tính Excel97, và những con số khô khan luôn làm bạn nhức đầu, tại sao không thử thư giản bằng cách sau đây: 1/Mở một file trong Excel97; 2/ Nhấn phím F5 (để bật hộp thoại Goto lên); 3/Nhập vào “X97:L97” vào trong khung Reference và nhấn OK; 4/ Nhấn phím TAB một lần. (lúc này bạn phải ở ô M97); 5/ Giữ CTRL-SHIFT, tay kia dùng chuột nhấn vào nút Chart Wizard trên thanh công cụ (nút có vẽ hình một biểu đồ có màu xanh vàng đỏ).Bạn sẽ thấy một chương trình mô phỏng tập lái máy bay với màu sắc tương đối đẹp của Excel97 sẽ được chạy ở chế độ toàn màn hình. Có thể dùng chuột hoặc các phím mũi tên để điều khiển. Hãy thư giãn và khám phá thế giới bay của Excel97. Nếu không muốn bay nữa bạn nhấn phím ESC.

* Đổi giá trị mà không cần dùng công thức
Bạn phải thường xuyên cập nhật bản giá bằng bảng tính Excel. Đôi lúc, bạn cần tăng một nhóm các giá trị lên số phần trăm nhất định nào đó, bạn sẽ tạo công thức rồi dùng lệnh Edit/Paste Special để thay giá trị ban đầu bằng kết quả mới tính toán và cuối cùng, bạn phải xóa công thức đó đi. Giải pháp sau có thể giúp bạn tiết kiệm được nhiều thời gian. Giả sử bạn muốn tăng 5% cho các giá trị trong vùng A2:A100. Hãy thực hiện các bước sau:
1. Nhập 1,05 vào một ô trống bất kỳ và chọn Edit/Copy.
2. Chọn vùng bạn muốn sửa đổi (ở đây là A2:A100).
3. Chọn Edit/Paste Special.
4. Trong khung hội thoại Paste Special, chọn Multiply rồi nhấn OK.
5. Xóa giá trị đã nhập vào ở bước 1 vì không đến cần nó nữa.
Thao tác này đã nhân các giá trị trong vùng được chọn lên 1,05 lần, tương đương với việc tăng lên 5%.

* Nhân bản giá trị trong danh sách Excel
Bạn có thường thấy một danh sách trong đó mỗi giá trị ở cột A có nhiều giá trị tương ứng ở cột B như ví dụ trong hình 1 không? Khi sắp xếp danh sách theo tên, những ô trống sẽ không thể biết được ai đã bán hàng trong tháng Hai và Ba.
Nếu danh sách nhỏ, bạn có thể nhập vào những tên bị thiếu. Nhưng nếu cơ sở dữ liệu quá lớn, hãy thực hiện cách sau để điền tên vào những ô còn trống:

1. Chọn vùng có dữ liệu cần điền giá trị (trong ví dụ này là A3:A14).
2. Nhấn <F5> để mở khung hội thoại Go To.
3. Vào khung hội thoại Go To, nhấn Special.
4. Chọn Blanks.
5. Gõ = và địa chỉ của ô đầu tiên trong cột A (trong trường hợp này là =A3) rồi nhấn <Ctrl>-<Enter>.
6. Chọn lại vùng dữ liệu này và chọn lệnh Edit.Copy. Sau đó chọn Edit.Paste Special, chọn Values rồi nhấn OK.

* Chia sẻ shortcut
Giả sử bạn đã thiết đặt khoảng 200 chữ viết tắt (shortcut) thể hiện cho các sản phẩm và dịch vụ mà công ty bạn cung cấp trong AutoCorrect của Excel. Có cách nào tốt nhất để chuyển những shortcut này vào các máy khác để đồng nghiệp của bạn có thể cùng sử dụng.
AutoCorrect, có từ Excel 95, có thể sửa những lỗi chính tả thông dụng một cách nhanh chóng. Nhiều người dùng Excel đã khám phá được rằng bạn có thể gán “shortcut tốc ký” cho một chuỗi ký tự dài hơn. Ví dụ, bạn có thể định nghĩa “awc” như một shortcut AutoCorrect của “Amalgamated Widget Corporation of America”. Khi gõ awc vào một ô, Excel sẽ thay thế bằng chuỗi ký tự tương ứng. Bạn có thể định nghĩa và quản lý shortcut bằng cách dùng lệnh Tools.AutoCorrect.
Excel và mọi ứng dụng Microsoft Office khác lưu các shortcut AutoCorrect trong một file *.acl trong folder Windows (tên file có thể khác nhau). Do đó, khi thực hiện một thay đổi nào đó trong Excel, nó cũng được phản ánh trong Word. Tuy nhiên, không có công cụ nào sửa được file nhị phân *.acl và việc chuyển một file như thế từ máy này sang máy khác sẽ không có tác dụng gì. Giải pháp là dùng macro VBA để tạo các shortcut.
Khởi động Excel bằng một bảng tính mới, sau đó nhập các shortcut và chuỗi ký tự tương ứng vào cột A và B. Nhập bao nhiêu thì tùy bạn nhưng giữa các dòng đừng để dòng nào trống (hình 2). Lưu và đặt tên cho bảng tính này.
Với Excel 97, chọn Tools.Macro. Macros để hiển thị khung hội thoại Macros. Gõ Create-Shortcuts trong mục Macro Name rồi nhấn Create.
Sau đó, nhập macro sau vào VBA, nhấn <Alt>-<F11> để trở về Excel. (Lưu ý rằng với các version Excel cũ, bạn phải chọn Insert.Module để thêm một module VBA).
Sub CreateShortcuts()
ItemCount=Application._
CountA(Range(“Sheet1!A:A”))
For Row=1 To ItemCount
ShortText=Cells(Row,1)
LongText=Cells(Row,2)
Application.AutoCorrect._
AddReplacement ShortText, LongText
Next Row
End Sub
Lưu bảng tính và chép cho các đồng nghiệp của bạn. Muốn bổ sung shortcut cho AutoCorrect, mở bảng tính, chọn Tools.Macro.Macros rồi thi hành macro CreateShortcuts. Nên nhớ rằng những macro nào trùng tên sẽ bị ghi chồng mà không có nhắc nhở gì cả.

Loạt bài về các thủ thuật ứng dụng Excel trong công việc
Excel là mênh mang. Người giỏi ứng dụng Excel nhất thế giới cũng chỉ sử dụng được khoảng 30%. Nên các bạn không cần phải biết quá nhiều, chúng ta chỉ cần nắm những gì cần thiết nhất (ứng dụng khoảng 29,5% – tôi nghĩ là vậy). Hãy kích vào read more để tham gia.

– Để nhập cùng một số liệu vào nhiều ô trong bảng tính. Bạn giữ ctrl và kích lần lượt vào các ô cần nhập số liệu, gõ giá trị số liệu vào, giữ Ctrl nhấn Enter. Dữ liệu sẽ nhập vào tất cả các ô. Bạn xem hình minh hoạ:

– Định dạng dữ liệu trong Excel. Sau đây là một vài mẹo nhỏ giúp bạn “thuần phục” Excel, buộc nó phải định dạng dữ liệu đúng như ý bạn ngay từ lúc nhập liệu.
• Để nhập một giá trị với định dạng tiền tệ (currency), gõ dấu đô la ($) phía trước giá trị cần nhập.
• Để nhập một giá trị với định dạng phân số (fraction), gõ số 0 và một dấu phân cách phía trước phân số đó. Ví dụ: nhập “0 1/2” thay vì “1/2”, Excel sẽ hiểu cần phải hiển thị giá trị theo định dạng phân số thay vì định dạng ngày tháng “01-Feb”.
• Để nhập một giá trị với định dạng phần trăm (percentage), gõ dấu % phía sau giá trị của bạn (ví dụ như 100%).
• Để nhập một chuỗi số với định dạng văn bản (Text), hãy gõ một dấu nháy đơn trên (‘) ở phía trước chuỗi số đó. Mẹo này đặc biệt hữu ích vì Excel luôn “thông minh thái quá” khi hiểu các chuỗi toàn chữ số là kiểu số và tự động định dạng chúng theo kiểu số. Những chuỗi số có số 0 ở đầu sẽ bị cắt hết những số 0 cần thiết. Nếu bạn nhập mã 08053, Excel sẽ định dạng theo kiểu số và chỉ hiển thị 8053. Để buộc Excel định dạng chuỗi đó theo kiểu text, bạn hãy gõ ‘08053. (Với những chuỗi số quá dài, Excel chuyển sang hiển thị dạng mũ còn khó coi hơn, ví dụ chuỗi 123456789012 sẽ được chuyển thành 1,23457E+11).Biệnpháp “chữa cháy” Với những ô Excel đã định dạng theo kiểu số, dù bạn có định dạng lại theo kiểu text thì cách hiển thị của chúng vẫn không thay đổi. Để tránh phải gõ lại từ đầu, hãy làm như sau (lưu ý là cách làm này chỉ giúp bạn phá vỡ định dạng mũ “ngứa mắt” chứ không cứu lại được những số 0 ở phần đầu đã bị Excel cắt):
– Chọn (bôi đen) ô cần chuyển sang định dạng kiểu text; chọn thực đơn Format/Cells, chuyển sang thẻ Number.
– ChọnTexttrong danh mục Category rồi nhấn nút OK.
– Nhấn vào ô cần chuyển định dạng, nhấn F2 rồi nhấn ENTER

* Mở tệp tin thường dùng
Phải thao tác thường xuyên trên một tệp tin (ví dụ bảng quyết toán), bạn có thể chỉ định Excel để chương trình mở tệp tin này (ví dụ quyettoan.xls) mỗi khi chương trình khởi động. Để thực hiện điều này, chỉ cần lưu tệp tin (File > Save As) vào thư mục XLStart nằm trong \Program files\Microsoft Office\Office.

* Chèn nhiều dòng trong một ô (cell)
Trong quá trình nhập dữ liệu, ở chế độ mặc định mỗi lần bạn nhấn Enter thì con chỏ chuột sẽ chọn ô (cell) ngay dưới ô bạn đang thao tác. Chính vì vậy đối với những người “mới vào nghề”, rất khó có thể nhập dữ liệu nhiều dòng trong cùng một ô. Thủ thuật hết sức đơn giản, bạn chỉ việc nhấn tổ hợp phím Alt + Enter.

* Thêm màu cho bảng tính
Để thêm màu hoặc ảnh nền cho bảng tính, bạn chọn Format > Sheet > Background. Tiếp đến, tìm tới ảnh nền (hoặc font màu) bạn thích và nhấn Open.

* “Đóng băng” dòng tiêu đề
Trong mỗi bảng tính thường có dòng tiêu đề cố định vị trí cho từng cột (column). Nếu bạn nhập dữ liệu trong nhiều dòng và tràn quá màn hình, công việc nhập dữ liệu sẽ rất khó khăn vì dòng tiêu đề “trôi” mất. Để “đóng băng” dòng tiêu đề, nhấn chuột chọn một ô ngay dưới dòng tiêu đề; chọn Window > Freeze Panes. Giờ bạn có thể thoải mái nhập dữ liệu với thanh tiêu đề luôn hiển thị ở phía trên bảng tính..
Để giúp các bạn khai thác các tính năng ưu việt trong công tác xử lý bảng tinhd đối với phần mềm Microsoft Excel, xin giới thiệu một số thủ thuật được coi là hay nhất của phần mềm này:

* Hiển thị đối số của các hàm
Để xem đối số trong một công thức, hãy nhấn Ctrl- Shift- A. Ví dụ, nếu bạn gõ =RATE và nhấn Ctrl- Shift -A, bạn có thể nhìn thấy tất cả các đối số cho hàm này (ví dụ =RATE (nper, pmt, pv, fv, type, guess)).
Nếu bạn muốn biết chi tiết hơn, gõ theo: =RATE
Và nhấn Ctrl+A để hiển thị theo Function Wizard.

* Tham chiếu các hàm
Để tham chiếu các hàm ứng dụng trong Excel, bạn hãy giữ phím Shift-F3, Excel sẽ đưa ra hộp thoại chứa tất cả các hàm ứng dụng và hướng dẫn các cú pháp cụ thể cho từng hàm khi bạn di chuyển con trỏ điểm sáng đến hàm muốn tham chiếu. Đây cũng là cách nhập công thức nhanh mà không cần gõ toàn bộ cú pháp.

* Dùng F9 để tính tạm thời
Nếu bạn tạo một bản tính có chứa công thức quá dài nên không thể biết ngay kết quả, bạn có thể kéo con trỏ để chọn một phần của công thức trong thanh công thức, và ấn phím F9. Lập tức, kết quả của một công thức con trong dãy công thức của bạn sẽ hiện trên màn hình. Quan trọng hơn, là bạn không được ấn Enter, một phần của công thức đó sẽ bị mất, nên để chắc chắn bạn phải ấn phím ESC. Tuy nhiên nếu bạn nhỡ ấn Enter, thì hãy thử ấn tổ hợp phím Ctrl- Z để phục hồi lại các thay đổi.

* Liên kết text box tới dữ liệu trong ô
Bạn có thể liên kết một text box tới dữ liệu trong một ô của bản tính bằng cách tạo ra một text box và liên kết công thức trả lại kết quả của ô đó tới text box.
1. Nhắp vào biểu tượng tạo một text box trên thanh công cụ Drawing. Nhắp vào bảng tính và kéo con trỏ để tạo một text box.
2. Đưa con trỏ tới thanh công thức, gõ công thức đã cho kết quả tới ô cần liên kết vào text box. (Ví du: trong ô A1 bạn có số liệu là 2. Trên thanh công thức, ban gõ =A1). Và ấn Enter.
3. Text hay số liệu bạn gõ trong ô đã liên kết (ví dụ A1) sẽ xuất hiện trong text box. Trong ví dụ trên thì text box sẽ có giá trị ở trong là 2.
Bạn có thể di chuyển text box tới một vài bản tính khác trong tập bảng tính nếu bạn muốn.

* Liên kết một hình ảnh tới một dãy ô
Bạn có thể copy một dãy ô và dán nhanh chúng như một hình ảnh trong một bản tính. Đây cũng là một cách tinh xảo để dễ dàng nhìn thấy ô nào đó tại một vài nơi trong bảng tính của bạn. Bạn có thể sử dụng phương pháp này để in các ô trong một trang. Khi các ô liên kết thay đổi thì các định dạng này cũng được áp dụng cho các ô được liên kết. Để tạo một hình ảnh được liên kết, bạn theo các bước:
1. Chọn các ô có chứa dữ liệu gốc.
2. Nhắp vào Copy trên menu E(bay.)
3. Chọn ô mà bạn muốn dán hình ảnh vừa chọn xuất hiện.
4. Trong khi giữ phím Shift, nhắp vào Paste Picture Link trên menu E(bay.) Kết quả sẽ cho nhanh chóng.
Sử dụng Advanced Filter
Nếu bạn tạo một danh sách Shift trong Microsoft Excel và muốn chọn tại dữ liệu đó và copy chúng tới bảng tính khác, hãy sử dụng lênh Advanced Filter. Để bắt đầu sử dụng lệnh này, nhắp vào Filter trong menu Dat, nhắp vào Advanced Filter và làm theo các chỉ dẫn.
Sử dụng hàm Sum+ If để tính tổng dữ liệu Advanced Filter
Giả sử bạn tạo một danh sách dữ liệu trong ô từ A1 đến A10 và muốn tính tổng tất cả các giá trị lớn hơn 50 và nhỏ hơn 200. Để làm được việc này, sử dụng theo dòng công thức dưới đây:
=SUM( IF( A1:A10 >=50, IF( A1: A10 <=200, A1:A10,0),0))
Để chắc chắn bạn nhập công thức như là một dãy, bạn hãy ấn Ctrl- Shift- Enter. Sau đó bạn sẽ nhìn thấy dấu ngoặc {} trong công thức. Nhưng không được ấn Enter khi đang gõ công thức.

Sử dụng hàm Sum+ If để đếm dữ liệu
Bạn đã có một danh sách dữ liệu trong các ô A1: A10, và muốn đếm tất cả các giá trị lớn hơn 50 và nhỏ hơn 200. Bạn sử dụng công thức sau:
=SUM( IF( A1:A10 >=50, IF( A1: A10 <=200, 1,0),0))

Điền nhanh dữ liệu bằng các ô giống nhau
Bằng cách nhắp đúp chuột vào góc phải dưới của một ô để làm xuất hiện con trỏ hình dấu cộng sẽ tạo ra một chuỗi dữ liệu giống hệt ô gốc trong các ô tiếp theo của cột. Ví dụ, nếu bạn gõ dữ liệu trong các ô A1: A20, gõ một công thức hay text vào trong ô B1. Nhắp hai lần chuột vào góc dưới của ô B1. Microsoft Excel sẽ điền dữ liệu xuống phía dưới cột từ ô B1 tới ô B20.

Sắp xếp một danh sách đẵ được lọc
Để sắp xếp một danh sách đã được lọc, chọn Data\ Sort, và chọn cột thích hợp từ hộp điều khiển Sort by. Đặc biệt bạn cần lưu ý là sắp xếp theo thứ tự tăng dần (Ascending) và giảm dần (Descending) và nhắp vào OK.

Lấy các bộ lọc khi công việc đã hoàn thành
Nếu bạn sử dụng AutoFilter để lọc các bản ghi, bạn đã kết thúc việc hiển thị các record đó, nhưng sau đó bạn lại muốn xem lại tất cả các bản ghi của bạn một lần nữa. Để nhận được các bản ghi đó, đơn giản bạn chọn All từ danh sách thả xuống của bộ lọc hiện tại. Nếu bạn muốn tắt chức năng AutoFilter, chọn Data\ Filter và xoá chọn trong AutoFilter.

Làm vừa dữ liệu trong một trang
Excel đã rất “cố gắng” để đưa thật nhiều dữ liệu cho vừa một trang, nhưng bạn có thể giảm bớt hay làm tăng thêm cho các dữ liệu bảng tính của bạn bằng cách thay đổi lựa chọn Adjust To % Normal Size. Hay bạn có thể sử dụng lựa chọn Fit To Pages để nén dữ liệu cho đầy số trang riêng biệt. Đơn giản, bạn chọn File\ Page Setup và thử nghiệm với hai lựa chọn đó bằng cách thay đổi các thiết đặt của chúng. Cuối cùng, nhắp vào nút Print Preview để xem kết quả.

Hiển thị các ô hay dùng
Để tìm ra bất cứ ô nào mà bạn hay dùng, chọn Auditing từ menu Tools và chọn Show Auditing Toolbar. Nhắp vào một ô cùng với một công thức, khi đó chọn Trace Precedents (nút đầu tiên trên thanh công cụ Auditing), và đưa mũi tên tới các ô mà bạn đã sử dụng để tính toán các giá trị của riêng ô đó, lúc này hình mũi tên màu xanh sẽ xuất hiện có liên kết từ các ô dữ liệu tới ô kết quả. Để xoá các mũi tên, nhắp vào nút Remove All Arrows.

Tìm nhanh các ô có chứa công thức
Để tìm ra nơi các ô chứa công thức một cách nhanh chóng trong bảng tính, chọn Go To từ menu E(bay.) Trong hộp thoại xuất hiện, chọn Special\ Formulas, và nhắp vào OK. Khi đó, mỗi ô có chứa một công thức sẽ được lựa chọn.
Đánh dấu vào Formulas trong hộp thoại Go To để chọn ô có công thức.

Bổ sung Shift nền web vào bảng tính
Để bổ sung dữ liệu “sống” từ các bảng tính nền web tới bảng tính hiện tại của bạn: mở bảng tính Web, gõ URL vào trong hộp thoại File Open sau đó chọn và copy các ô bạn muốn. Trong bảng tính của ban, chọn Paste Special từ menu Edit và nhắp vào nút Paste Link.

Sử dụng ô tham chiếu và nhãn text trong các công thức
Để sử dụng các tham chiếu ô cùng với nhăn text trong một công thức, bạn chỉ việc gõ một ký hiệu (&) ở giữa tham chiếu và text. Ví dụ, để hiển thị dòng dữ liệu là “25 Departments”, gõ (=A1 & “Departments”), trong đó A1 chứa số 25.
Làm thế nào để ấn định một macro tới một nút?
Bạn muốn chạy macro hay hàm chỉ bằng một thao tác nhắp vào một nút? Bằng cách gán một hàm hay một macro tới một nút tuỳ biến bạn có thể thực hiện rất nhanh chóng mà không phải chọn Tools\Macro. Để ấn định một macro tới một nút, chọn View\ Toolbars\ Customize. Nhắp vào tab Commands và chọn Macros trong điều khiển Categories. K

Nếu bạn nào có hứng thú muốn làm mục lục trong Word xin hãy làm như sau
(Ít nhất tài liệu của bạn cũng vài trăm trang thì làm mục lục mới xướng tay. (Xin dành bài viết này đặc biệt cho các cô cậu sinh viên đang làm đồ án).
Bước 1: Hãy bắt đầu tạo một File Word (bình thường thôi).
Bước 2: Định dạng các tiêu đề của các đề mục bằng Heading 1 cho tiêu đề lớn nhất, Heading 2 cho tiêu đề vừa vừa, Heading 3 cho tiêu đề hơi nhỏ…
Bước 3: Để các tiêu đề được thể hiển số thứ tự tự động. Bạn hãy dùng Outline Numbered trong menu Format/ Bullet and Numbering. Xin coi kỹ phần này.
Bước 4: Sau khi chuẩn bị 3 bước trên xong, bạn hãy thao tác như sau để tạo mục lục:
– Đặt con trỏ tại nơi cần thể hiện mục lục.
– Vào menu Insert/Reference/ Index and Tables.
– Trên Tab Table of Contents. bạn hãy Check vào 2 mục Show page number và Rights align page numbers, Chọn From template trong mục Formats bên dưới mục General.
– Click nút OK, máy sẽ tặng ngay cho bạn một bảng mục lục, nếu không ra tiếng việt thì tự nghĩ cách làm cho nó ra nhé.

XHTTWebsite: Với những người thường xuyên phải sử dụng nhiều đến Excel hoặc các nhân viên kế toán họ muốn một số hàm riêng biệt để tính toán, chuyển đổi mà trong Excel không hỗ trợ. Đơn giản, giả dụ như bạn muốn in ấn một bảng kê có ghi tiền bằng chữ cho các bảng kê hoặc có thể sử dụng để tính cước phí chỉ cần thông qua một cột đơn vị, dung lượng, thời lượng sử dụng thì chẳng có hàm (function) nào trong Excel hỗ trợ. Lúc đó bạn sẽ phải lập trình cho mình một số hàm riêng trong công việc của mình. Các bước thực hiện bạn sẽ phải làm như sau.
Tạo các macro cho worksheet.
Vào Excel sau đó bạn tạo một worksheet mới. Sau đó vào menu Tools -> Macro -> Visual Basic Editor.

Sau khi cửa sổ chương trình Visual Basic Editor được mở ra bạn hãy chọn menu Insert -> Module. Tại đây bạn có thể khai báo các hàm riêng cho bạn. Chúng ta thử làm ví dụ cho một bài toán đơn giản như sau: Một công ty bán phần mềm, nếu người mua mua của công ty các phiên bản đăng ký bản quyền thì dưới 10 phiên bản giá 5.000.000đ/phiên bản, nếu trên 10 bản thì giá từ bản thứ 11 trở đi là 4.000.000đ/phiên bản và từ bản thứ 31 trở đi thì giá là 2.500.000đ/phiên bản.
Trong cửa sổ module của bạn hãy nhập đoạn code như dưới đây để tạo function tính tiền theo số lượng:
CODE
Public Function Tinhtien(soluong As Integer) As Double
Tinhtien = 0
If soluong > 30 Then Tinhtien = 2.5 * (soluong – 30)
If soluong > 10 Then
If soluong > 30 Then Tinhtien = Tinhtien + 80
‘do tu phien ban 11 den 20 gia la 4tr/1 phien ban = 80tr
If soluong <= 30 Then Tinhtien = Tinhtien + 4 * (soluong – 10)
Tinhtien = Tinhtien + 50 ’10 phien ban dau gia 50tr
Else
Tinhtien = Tinhtien + soluong * 5
End If
Tinhtien = Tinhtien * 10 ^ 6 ‘quy ra tien trieu
End Function

Sau đó bạn hãy lưu công việc của bạn, chọn menu File sau đó chọn Save, trong mục lưu giữ bạn hãy chọn tại FileType là Microsoft Office Excel Add-In (*.xla) rồi lưu lại với một tên file nào đó có thể lưu với tên Test.xla rồi nhấn Save.

Kích hoạt functions riêng của bạn
Sau khi lưu giữ xong file Test.xla bạn hãy quay trở lại cửa sổ của Excel, chọn menu Tools -> Add-Ins, cửa sổ Add-Ins xuất hiện:

Nhấn Browse rồi chọn tới file Test.xla của bạn vừa được lưu giữ khi đó trên cửa sổ hộp thoại sẽ xuất hiện Test ở trong danh sách. Đánh dấu vào ô vuông phía trước Test để kích hoạt các hàm có trong file đó. Nhấn OK để thoát.

Sử dụng hàm của bạn
Vào cell nào đó trong worksheet để thử nghiệm kết quả của bạn, lựa chọn menu Insert -> Functions sẽ thấy hộp thoại như hình dưới đây:

Chọn tại phần Category hãy chọn User Defined sau đó chọn các hàm riêng của bạn ở danh sách phía dưới. Kết quả thử nghiệm với hàm TinhTien như ví dụ trên sẽ cho bạn kết quả như ý. Kết quả ở hình dưới đây được sử dụng thêm hàm chuyển đổi từ số sang chữ.

Hỏi về Excel
Hỏi: 1. Tôi tạo một thủ tục VBA xoá hàng sử dụng phím tắt trên cửa sổ Excel, chạy thủ tục này và hàng phím tắt bị mất. Xin hỏi cách phục hồi lại (menu Edit/undo trong Excel đã bị mờ).

2. Xin cho biết cách ghi 1 chuỗi vào một ô trong Excel để phần đầu của chuỗi được gạch dưới, phần còn lại thì bình thường.
3. Bảng tính nhiều trang, xin cho biết cách in đường phân trang bằng một đường gạch dưới (thay cho việc dùng Format/border để kẻ cho từng trang tại vị trí nét đứt phân trang).
4. Xin cho biết cách hạn chế hiển thị số hàng trong bảng tính (ví dụ tối đa 100 hàng/sheet).
Đáp:
1. Excel (và các ứng dụng Office khác) cho phép người dùng cá nhân hóa (customize) các phần tử giao diện trong cửa sổ Excel. Người dùng có thể thêm/bớt/hiệu chỉnh từng option menu, từng menu pop-up hay cả thanh menu-bar. Tương tự, người dùng có thể thêm/bớt/hiệu chỉnh từng toolbar, từng icon trong toolbar. Với các khả năng trên, người dùng có thể thiết kế lại giao diện của Excel hoàn toàn mới theo yêu cầu riêng của mình.

Nếu bạn đã lỡ chạy macro xóa hàng sử dụng phím tắt (chúng tôi không hiểu bạn muốn nói hàng phím tắt là toolbar nào của Excel) và muốn khôi phục lại, bạn hãy viết macro khác để tạo lại nó.

2. Bạn có thể chỉnh dạng hình thức hiển thị cho từng chuỗi con trong chuỗi nội dung của 1 cell bất kỳ trong bảng tính Excel. Việc chỉnh dạng có thể thực hiện thủ công hay lập trình bằng đoạn code VBA. Thí dụ đoạn code VBA sau đây sẽ dời cursor về cell A1, thêm chuỗi “Nguyen Van Hiep” vào cell này rồi gạch dưới phần đầu “Nguyen Van”, phần còn lại để bình thường.

Sub Macro1()
‘ Macro demo việc chỉnh dạng chuỗi
‘chọn cell A1
Range(“A1”).Select
‘nhập nội dung vào cell A1
ActiveCell.FormulaR1C1 = “Nguyen Van Hiep”
‘chỉnh dạng 10 ký tự đầu “Nguyen van”
With ActiveCell.Characters(Start:=1, Length:=10).Font
.Name = “Arial”
.FontStyle = “Regular”
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
‘gạch dưới 10 ký tự đầu “Nguyen van”
.Underline = xlUnderlineStyleSingle
.ColorIndex = xlAutomatic
End With
‘chỉnh dạng 5 ký tự cuối ” Hiep”
With ActiveCell.Characters(Start:=11, Length:=5).Font
.Name = “Arial”
.FontStyle = “Regular”
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
End Sub

3. Có rất nhiều cách khác nhau để tạo đường gạch ngang dưới mỗi trang in, cách đơn giản nhất là dùng Footer. Bạn hãy vào menu File.Page Setup, chọn tab Header/Footer, chọn button Customize Footer rồi chọn 1 trong 2 cách sau:

– Tạo nội dung Footer như là 1 chuỗi gồm nhiều ký tự Space (trắng) rồi chỉnh dạng gạch dưới chuỗi trắng này.
– Tạo nội dung Footer như là 1 ảnh bitmap, ảnh này đã được bạn vẽ và cất lên 1 file nào đó (dùng Paint, Corel Draw, Photoshop…), nó chỉ chứa 1 lằn gạch ngang với độ rộng theo yêu cầu.

4. Mặc định, mỗi sheet Excel chứa tối đa 65536 hàng dữ liệu, hiện chúng tôi chưa tìm thấy tài liệu nào miêu tả cách thức hạn chế số hàng trong từng Sheet.

Chèn hình vào bảng tính Excel
Em muốn insert file hình vào Excel (file hình này phải chìm xuống như trong Word khi ta chọn chức năng Behind Text Layout).
Em đã thử dùng tính năng Format/ Sheet/ Background nhưng do diện tích của file hình nhỏ nên nó fill đầy hết một sheet mà em chỉ muốn nó hiện đúng với diện tích của file hình đó. Các anh chị có cách nào chỉ giúp giùm em không nhỉ? Em chân thành cảm ơn.
Đáp:
Bạn phải dùng Forrmat Picture như sau:
Forrmat Picture > Picture > Color > Washout.

Thêm lệnh thường dùng vào nút phải chuột trong Excel
Hỏi: Làm thế nào để thêm một số lệnh thường dùng vào nút phải chuột trong Excel?
Đáp: Excel (hay các ứng dụng khác trong bộ Microsoft Office) quản lý nhiều menu khác nhau, mỗi menu được nhận dạng thông qua tên riêng. Thí dụ menu pop-up được hiển thị mỗi khi bạn ấn phải chuột trên 1 cell bảng tính có tên là menu “Cell”. Microsoft cho phép người dùng được toàn quyền truy xuất, hiệu chỉnh nội dung của từng menu. Thí dụ macro VBA sau đây cho phép bạn thêm 1 lệnh (control) mới vào menu pop-up “Cell”:

Sub AddMenuItem()
‘định nghĩa biến tham khảo đến menu.
Dim objCommandBar As Office.CommandBar
‘định nghĩa biến tham khảo đến control option.
Dim objCommandBarButton As Office.CommandBarButton
‘Xác định menu “Cell”
Set objCommandBar = Application.CommandBars.Item(“Cell”)
‘thêm 1 option (lệnh) mới vào menu “Cell”
Set objCommandBarButton = objCommandBar.Controls.Add(msoControlButton)
‘thiết lập các thuộc tính cho option mới
With objCommandBarButton
.Caption = “Chuc nang 1”
.FaceId = 43
.Style = msoButtonIconAndCaption
.OnAction = “OnChucNang1”
End With
End Sub
‘thủ tục xử lý option mới
Sub OnChucNang1()
‘thí dụ thông báo cho người dùng biết
MsgBox “Bạn mới ấn chức năng 1”
End Sub

Thủ thuật EXCEL (P4)
Trong khi cày xới ruộng vườn và cày xới Windows tôi có nhận được một câu hỏi của một học sinh dân tộc. “Em đang thiết kế công thức cho lớp em để phân loại các mức giỏi, khá, trung bình, kém. Em muốn Giỏi mà đỏ, Khá màu hồng, trung bình màu xanh, kém màu đen. Nhưng em muốn sắp xếp bằng lệnh Data/Sort cột này thì, phải xếp theo thứ tự Giỏi đến Khá, đến Trung bình, đến Kém”.
Tôi có thể khuyên cô bé này thêm một cột để nhận giá trị bằng số theo giá trị của cột này, rồi xếp theo cột mới như người ta thường làm. Hoặc chọn thực đơn Format/Conditional Forrmating… Điều này quá bình thường. Tôi muốn nhắc đến một cách làm khác. Đó chính là ý tưởng của chính tập đoàn Microsoft đề cập trong các bài phát biểu khi công bố các bộ OFFICE mới.
Trong bài Trung bình cộng theo màu chữ, tôi có nhắc đến một vấn đề mà có lẽ ít bạn để ý. Đó là việc thể hiện của màu sắc tuỳ thuộc vào giá trị của ô. Bài viết này tôi xin đề cập đến vấn đề ứng dụng sâu hơn. Ta sẽ làm được gì và từ đó phát triển thêm dựa vào VBA (Ngôn ngữ lập trình Visual Basic For Application – Quà tặng cho những người thích tìm hiểu).
Trong một ô của Excel sẽ đựng giá trị của ô. Khi ta để hộp sáng tại ô đó, giá trị hoặc công thức tính ra giá trị, sẽ hiển thị trên thanh công thức Formular. Đó chính là giá trị thực của ô. Giá trị thực này tham gia tính toán khi công thức chỉ tới địa chỉ ô đó. Giá trị này còn gọi là phần thứ cấp của ô.
Hiển thị trong ô không những chỉ có giá trị thực đó, nó còn một dạng giá trị khác. Đó là giá trị giả, giá trị làm vỏ bọc, không tham gia tính toán mà chỉ giúp cho việc hiển thị giá trị thực thứ cấp rõ ràng hơn. Đó là phần sơ cấp của ô.
Đơn giản nhất như bảng sau:
(4) Họ tên Sản phẩm (D) Mức khoán (E) So với định mức (F)
Ông A 500 300
Bà B 400 200
Anh C 700 500
Chị D 800 500
Sản phẩm, mức khoán, định mức chính là ô số. (Phần mở ngoặc tôi muốn thuyết minh cho tiêu đề cột của ô ở hàng 4)
Ta thấy, khi nhập vào các số, giá trị của ô F5 được tính theo:F5 = D5 – E5. Nếu D5 và E5 chỉ là số, thì đương nhiên ô sẽ có kết quả phép tính. Nhưng nếu bạn đánh thêm vào ô D5 để thành “500 cái”. Bạn sẽ bị lỗi #Value. Điều đó là đương nhiên vì các ô kiểu xâu ký tự không thể thực hiện phép tính của toán được.
Khi đó, muốn đánh được “500 cái”, bạn hãy vào FormatCell, chọn Number, chọn Custom và đánh vào hộp Type: #.### ” cái”. Bạn sẽ thấy, phép tính vẫn thực hiện bình thường và trên ô vẫn hiện đủ “500 cái”.
Tôi mô tả tương đối tỉ mỉ để bạn hiểu chữ ” cái” chính là phần sơ cấp của ô. Phần sơ cấp được thực hiện như trên, nhưng nhớ là luôn phải có dấu ngoặc kép ở 2 đầu để tránh lỗi Format.
Copy phần sơ cấp này bằng cách để hộp sáng tại ô nguồn rồi chọn nút Format Painter (Hình cái chổi) rồi “quét” vào các ô đích.
Tôi chép lại phần định dạng Code của các loại gía trị trong ô trong bài Trung bình cộng theo màu chữ để bạn tiện theo dõi. Tuỳ thuộc vào giá trị thứ cấp của ô, Excel phân chia thành 2 loại định dạng (2 Code chính).
Code 1: Phần số dương ; Phần số âm ; Phần số 0 ; Phần không phải số.
Code 2: [ > mốc] Phần lớn hơn mốc; Phần số âm; Phần từ 0 đến mốc; Phần không phải số.
Bạn căn cứ vào code trên để định dạng từng phần (Lưu ý, máy của tôi cài theo kiểu Việt Pháp nên dấu phân cách là dấu chấm phảy, còn máy của bạn định dạng kiểu Mỹ Anh thì dấu phân cách chính là dấu phảy).
Tại mỗi phần, nếu bạn điều chỉnh màu sắc thì đặt tên màu sắc vào trong hộp có dấu [ ], sau đó đánh định dạng theo khuôn mẫu quy ước, dấu # (không bắt buộc hiển thị) và số 0 (Bắt buộc hiển thị đủ số chữ số, nếu không đủ thì điền số 0 vào cho đủ số chữ số). Phần sơ cấp được đặt trong dấu ngoặc kép.
PCWorld VN số tháng 4/2001 có hướng dẫn không cho hiển thị trong ô bằng ba dấu ; chính là không cho hiện cả sơ và thứ cấp của ô.
Có nghĩa là ta cũng có thể chỉ cho hiển thị phần sơ cấp mà không hiển thị phần thứ cấp. Còn các lệnh của EXcel đương nhiên chỉ tác động tới phần thứ cấp rồi.
Bây giờ xét tới bài toán của cô học trò dân tộc. Ta hãy đặt mức sau đây để cho máy sắp xếp:
Mức Giá trị Thể hiện
1 a Giỏi [Màu đỏ]
2 2 Khá [Màu hồng]
3 0 Trung bình [Xanh]
4 -1 Kém [Đen]
Giá trị tôi sẽ đặt làm thứ cấp, còn thể hiện, tôi sẽ đặt làm sơ cấp.
Ta lựa chọn phần xếp loại rồi đánh vào FormatCell/Number/Type theo code 2 như sau:
[>1] “Khá” [Magenta] ; “Kém” ; “Trung bình” [blue]; “Giỏi” [red]
Khi đó tuỳ thuộc giá trị thứ cấp của ô nếu:
> 1 Ô sẽ hiện chữ “Khá” màu hồng.
Từ 0 – 1 Ô sẽ hiện chữ “Trung bình” màu xanh
Số âm Ô hiện chữ “Kém” màu đen
Chữ bất kỳ Ô hiện chữ giỏi màu đỏ
Ta lập công thức để thứ cấp là các giá trị trên. Khi đó nếu sắp xếp giảm dần, ta sẽ thấy các hàng đều theo thứ tự: Giỏi – Khá – Trung bình – Kém
Tương tự bạn có thể làm 4 mức trên với việc khác.
Ta có thể thiết kế một hàm mới để mở rộng chức năng này lên, phần này xin viết ở bài viết khác.

Trung bình Cộng theo màu chữ của EXCEL
Hỏi: Làm thế nào tạo Trung bình cộng theo màu chữ của Excel?
Trong Excel, ngoài các hàm có sẵn ta có thể xây dựng các hàm mới. Tôi xin giới thiệu một cách vận dụng Visual Basic để tính hàm trung bình cộng các ô có cùng màu chữ với ô mẫu, trong vùng EXCEL. Muốn sử dụng hàm ta phải truyền 2 biến: Tên vùng và ô làm mẫu.

Từ EXCEL ta khởi động Visual Basic bằng nhiều cách, tôi chọn Alt+F11. Rồi gõ Ctrl +R để hiện Project EXplore.

Ta Click để chọn tệp của mình làm dự án. Rồi Click Insert\Module để hiện cửa sổ soạn thảo hàm:

Đánh vào như sau:
Option Explicit
Function TBCONGMAU(VungDL, OMau)
Dim Tong, OHT, MauHT, MauChuan, dem, KQ

‘Lấy màu chuẩn
MauChuan=OMau.Font.Color
‘Xử lý từng ô một trong vùng
For Each OHT in VungDL

‘Lấy màu của ô hiện tại
MauHT=OHT.Font.Color

‘Nếu là ô số có màu chữ trùng màu thì cộng vào biến tong, và tăng biến dem
If IsNumeric(OHT) AND (MauHT=MauChuan) Then
Tong=Tong+OHT
dem = dem +1
End If
Next
if dem =0 then
KQ=”Không có ô nào trùng màu ô mẫu”
Else
KQ=Tong/dem
End if
TBCONGMAU=KQ
End Function

Tương tự, ta có thể thiết kế các hàm liên quan tới các thuộc tính của ô. Như cộng theo màu, đếm theo màu…. Sử dụng các thuộc tính của ô thông dụng như:
Màu chữ = Tên_Ô.Font.Color
Tên Font chữ = Tên_Ô.Font.Name
Cỡ chữ = Tên_Ô.Font.Size
Màu nền = Tên_Ô.Interior.Color
……..

Để sử dụng các hàm đã thiết kế, ta cần biên dịch thành các tệp Add-In thường có đuôi .XLA bằng cách cất luôn tệp thành dạng Add-In. (Khi cất, hộp File of Type: chọn kiểu Add-In). Sau đó tiến hành Add-In tệp vào Excel bằng cách chọn Tools\Add-In, rồi Browse để tìm tệp.

Khi sử dụng hàm thì sử dụng như một hàm bình thường đã có của Excel.
Ví dụ: =CONGMAU($E$5:$F$200; $a$3)
Nếu trong vùng $E$5:$F$200 có ô số trùng màu chữ với ô $a$3 thì cộng để đưa ra kết quả.

Thiết lập tự động màu sắc cho các ô tuỳ thuộc giá trị của ô:

* Thường sử dụng một trong 2 cách:

Cách 1: Sử dụng Code trong Format Cell:
Chọn ô, vào Format Cell\Number đánh trực tiếp vào hộp Code (Hoặc hộp Type tuỳ theo Version của Excel) theo mã Code thứ tự sau:

Code 1: Số dương ; Số âm ; Số 0; Chuỗi ký tự
Ví dụ: “Lãi ” #.### ” đồng” [red];”Lõm ” #.### ” đồng” [blue]; “Hoà vốn”;”Nhập sai”
(Nếu giá trị nhập vào là số dương thì chữ màu đỏ, số âm, chữ màu xanh.)

Code 2: [> Mốc] Số lớn hơn mốc; Số âm ; Số từ 0 đến mốc ; Chuỗi ký tự
Ví dụ: [>=200] #.### “cái dư” [red]; “thiếu “#.### ” cái”; #.### “cái dư” [blue]; “Nhập sai”
(Nếu số hơn 200 thì hiện màu đỏ, số dương nhỏ hơn 200 sẽ màu xanh…)

Như vậy, ta chỉ việc nhìn lướt qua bảng tính từ màu sắc của các ô, ta sẽ biết được những vùng nào có số phù hợp mà mình đã đặt.

Cách 2: Dùng cho những máy cài Excel 97 trở lên:
Bạn hãy lựa chọn ô rồi chọn từ Format\Conditional Formatting… tuỳ theo dấu so sánh và giá trin so sánh để ta Format lấy màu sắc của ô.

Sử dụng các hàm thiết lập để tính đối với màu sắc các ô. Nếu máy sử dụng OFFICE2000 không thực hiện được xin bạn cài bổ sung OFFICE2000 ở chế độ FULL (Run All From My Computer)

Chuyển dòng thành cột (và ngược lại)
Hỏi: Trong MS Excel làm thế nào có thể chuyển dòng thành cột và ngược lại?
Bạn đã hoàn tất việc nhập liệu, đặt công thức và định dạng cho một bảng tính. Khi nghĩ rằng mình đã hoàn thành công việc một cách tốt đẹp thì phát hiện rằng dữ liệu trong bảng tính bị đảo ngược từ dòng thành cột. Điều này rất quan trọng nếu bạn dự định sẽ thực hiện một số lệnh về cơ sở dữ liệu. Trước khi nhập lại dữ liệu, hãy kiểm xem bạn có tính năng Transpose không. Ví dụ ở Hình 1 cho thấy dữ liệu trong vùng A1:E5 được chuyển sang vùng A7:E11.

Hình 1. Chuyển đổi một vùng dữ liệu thì dễ dàng nhưng chỉ có Excel mới giữ lại được công thức.
Trong Excel, đánh dấu vùng muốn chuyển, chọn Edit.Copy. Nhấn chuột vào góc trên trái của vùng mới rồi chọn Edit.Paste Special. Trong khung hội thoại Paste Special, chọn tuỳ chọn Transpose rồi nhấn OK. Excel tự động điều chỉnh công thức trong vùng ban đầu.
Nhận dạng công thức
Đã bao nhiêu lần bạn vô tình xóa hoặc ghi chồng lên những ô có chứa công thức? Có giải pháp chống ghi lên những ô quan trọng và giải pháp khác là đặt cờ hiệu cho những ô này.
Có một kỹ thuật thông minh dùng tính năng định dạng có điều kiện của Excel 97 để định dạng đặc biệt những ô có chứa công thức, nhưng vài trường hợp lại không thực hiện được.
Ví dụ, với kỹ thuật này, bạn có thể thiết đặt sao cho ô chứa công thức có màu vàng hoặc ô chứa giá trị âm thì được làm đậm.
Với Excel 97 hoặc mới hơn, thực hiện những bước sau:
1. Chọn Insert.Name. Define.
2. Trong khung hội thoại Define Name, nhập CellHasFormula vào khung là ‘Names in workbook’ rồi gõ =GET.CELL(48,INDIRECT (“rc”,FALSE)) trong khung `Refers to’.
3. Nhấn Add rồi OK.
4. Đánh dấu những ô bạn muốn áp dụng định dạng có điều kiện.
5. Chọn Format. Conditional Formatting.
6. Trong khung hội thoại Conditional Formatting, chọn Formula Is từ danh sách thả xuống rồi gõ =CellHasFormula vào ô kế bên (hình 2). Thao tác này sẽ áp dụng định dạng có điều kiện bất cứ lúc nào công thức có giá trị là ‘True’.

Hình 2. Dùng định dạng có điều kiện để định dạng tự động cho các ô trong một vùng được chọn.
7. Nhấn nút Format rồi chọn loại định dạng bạn muốn. Nhấn OK.
Khi đã thực hiện xong các bước trên, những ô có chứa công thức trong vùng bạn đã đánh dấu ở bước 4 sẽ hiển thị định dạng bạn chọn. Thế kỹ thuật này làm việc như thế nào? Điểm mấu chốt là tạo một công thức như ở bước 2. Công thức này không có mặt trong ô như những công thức chuẩn nhưng vẫn hoạt động như một công thức bằng cách trả về giá trị, trong trường hợp này là ‘True’ hoặc ‘False’. Công thức dùng hàm GET.CELL là một phần của ngôn ngữ macro XLM và không thể dùng trực tiếp trong bảng tính. Tham số đầu tiên của hàm dùng giá trị 48 để trả về AàTrue’ nếu ô có chứa công thức. Hàm INDIRECT về căn bản là tạo tham chiếu đến từng ô trong vùng đã chọn.

Các thủ thuật hay nhất trong Excel

Để giúp các bạn khai thác các tính năng ưu việt trong công tác xử lý bảng tinhd đối với phần mềm Microsoft Excel, xin giới thiệu một số thủ thuật được coi là hay nhất của phần mềm này:
* Hiển thị đối số của các hàm
Để xem đối số trong một công thức, hãy nhấn Ctrl- Shift- A. Ví dụ, nếu bạn gõ =RATE và nhấn Ctrl- Shift -A, bạn có thể nhìn thấy tất cả các đối số cho hàm này (ví dụ =RATE (nper, pmt, pv, fv, type, guess)).
Nếu bạn muốn biết chi tiết hơn, gõ theo: =RATE
Và nhấn Ctrl+A để hiển thị theo Function Wizard.
* Tham chiếu các hàm
Để tham chiếu các hàm ứng dụng trong Excel, bạn hãy giữ phím Shift-F3, Excel sẽ đưa ra hộp thoại chứa tất cả các hàm ứng dụng và hướng dẫn các cú pháp cụ thể cho từng hàm khi bạn di chuyển con trỏ điểm sáng đến hàm muốn tham chiếu. Đây cũng là cách nhập công thức nhanh mà không cần gõ toàn bộ cú pháp.
* Dùng F9 để tính tạm thời
Nếu bạn tạo một bản tính có chứa công thức quá dài nên không thể biết ngay kết quả, bạn có thể kéo con trỏ để chọn một phần của công thức trong thanh công thức, và ấn phím F9. Lập tức, kết quả của một công thức con trong dãy công thức của bạn sẽ hiện trên màn hình. Quan trọng hơn, là bạn không được ấn Enter, một phần của công thức đó sẽ bị mất, nên để chắc chắn bạn phải ấn phím ESC. Tuy nhiên nếu bạn nhỡ ấn Enter, thì hãy thử ấn tổ hợp phím Ctrl- Z để phục hồi lại các thay đổi.
* Liên kết text box tới dữ liệu trong ô
Bạn có thể liên kết một text box tới dữ liệu trong một ô của bản tính bằng cách tạo ra một text box và liên kết công thức trả lại kết quả của ô đó tới text box.
1. Nhắp vào biểu tượng tạo một text box trên thanh công cụ Drawing. Nhắp vào bảng tính và kéo con trỏ để tạo một text box.
2. Đưa con trỏ tới thanh công thức, gõ công thức đã cho kết quả tới ô cần liên kết vào text box. (Ví du: trong ô A1 bạn có số liệu là 2. Trên thanh công thức, ban gõ =A1). Và ấn Enter.
3. Text hay số liệu bạn gõ trong ô đã liên kết (ví dụ A1) sẽ xuất hiện trong text box. Trong ví dụ trên thì text box sẽ có giá trị ở trong là 2.
Bạn có thể di chuyển text box tới một vài bản tính khác trong tập bảng tính nếu bạn muốn.
* Liên kết một hình ảnh tới một dãy ô
Bạn có thể copy một dãy ô và dán nhanh chúng như một hình ảnh trong một bản tính. Đây cũng là một cách tinh xảo để dễ dàng nhìn thấy ô nào đó tại một vài nơi trong bảng tính của bạn. Bạn có thể sử dụng phương pháp này để in các ô trong một trang. Khi các ô liên kết thay đổi thì các định dạng này cũng được áp dụng cho các ô được liên kết. Để tạo một hình ảnh được liên kết, bạn theo các bước:
1. Chọn các ô có chứa dữ liệu gốc.
2. Nhắp vào Copy trên menu Edit.
3. Chọn ô mà bạn muốn dán hình ảnh vừa chọn xuất hiện.
4. Trong khi giữ phím Shift, nhắp vào Paste Picture Link trên menu Edit. Kết quả sẽ cho nhanh chóng.
* Sử dụng Advanced Filter
Nếu bạn tạo một danh sách Shift trong Microsoft Excel và muốn chọn tại dữ liệu đó và copy chúng tới bảng tính khác, hãy sử dụng lênh Advanced Filter. Để bắt đầu sử dụng lệnh này, nhắp vào Filter trong menu Dat, nhắp vào Advanced Filter và làm theo các chỉ dẫn.
* Sử dụng hàm Sum+ If để tính tổng dữ liệAdvanced Filter
Giả sử bạn tạo một danh sách dữ liệu trong ô từ A1 đến A10 và muốn tính tổng tất cả các giá trị lớn hơn 50 và nhỏ hơn 200. Để làm được việc này, sử dụng theo dòng công thức dưới đây:
=SUM( IF( A1:A10 >=50, IF( A1: A10 <=200, A1:A10,0),0))
Để chắc chắn bạn nhập công thức như là một dãy, bạn hãy ấn Ctrl- Shift- Enter. Sau đó bạn sẽ nhìn thấy dấu ngoặc {} trong công thức. Nhưng không được ấn Enter khi đang gõ công thức.
* Sử dụng hàm Sum+ If để đếm dữ liệu
Bạn đã có một danh sách dữ liệu trong các ô A1: A10, và muốn đếm tất cả các giá trị lớn hơn 50 và nhỏ hơn 200. Bạn sử dụng công thức sau:
=SUM( IF( A1:A10 >=50, IF( A1: A10 <=200, 1,0),0))
* Điền nhanh dữ liệu bằng các ô giống nhau
Bằng cách nhắp đúp chuột vào góc phải dưới của một ô để làm xuất hiện con trỏ hình dấu cộng sẽ tạo ra một chuỗi dữ liệu giống hệt ô gốc trong các ô tiếp theo của cột. Ví dụ, nếu bạn gõ dữ liệu trong các ô A1: A20, gõ một công thức hay text vào trong ô B1. Nhắp hai lần chuột vào góc dưới của ô B1. Microsoft Excel sẽ điền dữ liệu xuống phía dưới cột từ ô B1 tới ô B20.
Sắp xếp một danh sách đẵ được lọc
Để sắp xếp một danh sách đã được lọc, chọn Data\ Sort, và chọn cột thích hợp từ hộp điều khiển Sort by. Đặc biệt bạn cần lưu ý là sắp xếp theo thứ tự tăng dần (Ascending) và giảm dần (Descending) và nhắp vào OK.
* Lấy các bộ lọc khi công việc đã hoàn thành
Nếu bạn sử dụng AutoFilter để lọc các bản ghi, bạn đã kết thúc việc hiển thị các record đó, nhưng sau đó bạn lại muốn xem lại tất cả các bản ghi của bạn một lần nữa. Để nhận được các bản ghi đó, đơn giản bạn chọn All từ danh sách thả xuống của bộ lọc hiện tại. Nếu bạn muốn tắt chức năng AutoFilter, chọn Data\ Filter và xoá chọn trong AutoFilter.
* Làm vừa dữ liệu trong một trang
Excel đã rất “cố gắng” để đưa thật nhiều dữ liệu cho vừa một trang, nhưng bạn có thể giảm bớt hay làm tăng thêm cho các dữ liệu bảng tính của bạn bằng cách thay đổi lựa chọn Adjust To % Normal Size. Hay bạn có thể sử dụng lựa chọn Fit To Pages để nén dữ liệu cho đầy số trang riêng biệt. Đơn giản, bạn chọn File\ Page Setup và thử nghiệm với hai lựa chọn đó bằng cách thay đổi các thiết đặt của chúng. Cuối cùng, nhắp vào nút Print Preview để xem kết quả.
* Hiển thị các ô hay dùng
Để tìm ra bất cứ ô nào mà bạn hay dùng, chọn Auditing từ menu Tools và chọn Show Auditing Toolbar. Nhắp vào một ô cùng với một công thức, khi đó chọn Trace Precedents (nút đầu tiên trên thanh công cụ Auditing), và đưa mũi tên tới các ô mà bạn đã sử dụng để tính toán các giá trị của riêng ô đó, lúc này hình mũi tên màu xanh sẽ xuất hiện có liên kết từ các ô dữ liệu tới ô kết quả. Để xoá các mũi tên, nhắp vào nút Remove All Arrows.
* Tìm nhanh các ô có chứa công thức
Để tìm ra nơi các ô chứa công thức một cách nhanh chóng trong bảng tính, chọn Go To từ menu Edit. Trong hộp thoại xuất hiện, chọn Special\ Formulas, và nhắp vào OK. Khi đó, mỗi ô có chứa một công thức sẽ được lựa chọn.
Đánh dấu vào Formulas trong hộp thoại Go To để chọn ô có công thức.
* Bổ sung Shift nền web vào bảng tính
Để bổ sung dữ liệu “sống” từ các bảng tính nền web tới bảng tính hiện tại của bạn: mở bảng tính Web, gõ URL vào trong hộp thoại File Open sau đó chọn và copy các ô bạn muốn. Trong bảng tính của ban, chọn Paste Special từ menu Edit và nhắp vào nút Paste Link.
* Sử dụng ô tham chiếu và nhãn text trong các công thức
Để sử dụng các tham chiếu ô cùng với nhăn text trong một công thức, bạn chỉ việc gõ một ký hiệu (& ở giữa tham chiếu và text. Ví dụ, để hiển thị dòng dữ liệu là “25 Departments”, gõ (=A1 & “Departments”), trong đó A1 chứa số 25.
* Làm thế nào để ấn định một macro tới một nút?
Bạn muốn chạy macro hay hàm chỉ bằng một thao tác nhắp vào một nút? Bằng cách gán một hàm hay một macro tới một nút tuỳ biến bạn có thể thực hiện rất nhanh chóng mà không phải chọn Tools\Macro. Để ấn định một macro tới một nút, chọn View\ Toolbars\ Customize. Nhắp vào tab Commands và chọn Macros trong điều khiển Categories. K𠄢ustom Button trong Commands tới một thanh công cụ. Khi bạn đã bổ sung xong nút macro, nhắp vào nút có biểu tượng hình mặt cười màu vàng và nhắp vào nút Modify Selection trong hộp thoại Customize. Chọn Assign Macro từ menu shortcut mới xuất hiện. Cuối cùng, để đồng nhất macro bạn muốn tới gắn vào nút macro, nhắp vào OK, và nhắp vào Close.
* Hiển thị công thức một cách an toàn
Bạn nhớ phải luôn luôn ghi bảng tính trước khi chuyển tới công thức hiển thị bằng cách ấn Ctrl – ~. Khi bạn quay lại bảng tính sau khi hiển thị các công thức, bạn có thể tìm thấy một vài định dạng khác nhau. Ví dụ, Excel có thể đã thay đổi độ rộng cột cùng một vài cột khác của bạn. Nếu điều này xảy ra, đơn giản mở lại bảng tính để quay lại định dạng đã mất. Phương pháp khác là chuyển bảng tính theo cách ở trên và tắt bằng cách vào menu Tools\Options chọn Views và đánh dấu chọn vào ô Formulas. Nhắp và OK để kết thúc.
* Sắp xếp các ký tự in hoa và in thường
Excel không quan tâm đến trường hợp các ký tự chữ cái khi sắp xếp, trừ khi bạn thông báo tới nó. Nếu bạn muốn phân biệt giữa chữ in hoa và in thường trong quá trình sắp xếp, chọn dữ liệu bạn muốn sắp xếp, chọn Data\Sort. Nhắp vào Options trong hộp thoại Sort và chọn lựa chọn Case Sensitive. Nhắp vào OK hai lần. Bạn sẽ phải nhớ để chọn lựa chọn này mỗi lần vì Excel sẽ không giữ lại thiết đặt này.
* Chuyển đổi các nhăn tới các số
Text dạng số là một kiểu dữ liệu hợp lệ. Ví dụ một số phone không hoàn toàn là một số. Khi lưu trữ kiểu dữ liệu này, bạn có thể thêm vào dữ liệu nhập vào bằng ký tự (‘). Tuy nhiên, thỉnh thoảng các giá trị số được nhập vào được nhập như là text số và điều này có thể xảy ra rắc rối. May thay, có thể dễ dàng chuyển đổi text số tới dạng số. Bạn theo các bước sau: chọn các ô dữ liệu bạn muốn chuyển đổi. Chọn Data\ Text To Column. Nhắp vào Next hai lần. Chọn General format và nhắp vào Finish. Excel sẽ chuyển đổi text số thành dữ liệu số.

Hỏi: Xin hướng dẫn cách tạo 1 buton trong Excel, khi nhấn chuột vào nó sẽ làm thay đổi nội dung của 1 cell được chỉ định trước.
Đáp: Để giải quyết yêu cầu của bạn, bạn hãy thực hiện lần lượt các công việc sau:
1. Chạy Excel, chọn menu Tools.Macro.Macros để hiển thị cửa sổ quản lý macro của Excel. Nhập tên macro mới của bạn (td. Mymacro1), ấn button Create để tạo mới macro này. Khi cửa sổ soạn code VBA của macro hiển thị, bạn hãy viết code miêu tả chức năng mong muốn. Dĩ nhiên bạn phải nắm vững cú pháp ngôn ngữ VBA và yêu cầu cụ thể mà mình muốn thực hiện bởi macro. Sau khi viết code macro xong, bạn chọn menu File.Close and return to Excel để quay về cửa sổ Excel. Thí dụ macro sau sẽ tăng giá trị của cell hiện hành lên 1 đơn vị:

Sub MyMacro1()
ActiveCell.Value = 1+ CInt(ActiveCell.Value)
End Sub

2. Vào menu View.Toolbars.Customize để hiển thị cửa sổ Customize, chọn tab Commands để hiển thị danh sách Command, duyệt tìm trong danh sách và chọn mục Macros để hiển thị các macro hiện có. Duyệt, chọn mục “Custom Button” trong danh sách, ấn giữ nó và drag về vị trí mong muốn trong Toolbar nào đó của Excel để tạo button mới, đóng cửa sổ customize lại.

3. Khi bạn ấn trái chuột lần đầu trên button vừa tạo, Excel sẽ hiển thị danh sách macro hiện hành để bạn chọn 1 macro kết hợp với button. Từ đây trở đi, mỗi lần bạn ấn chuột trên button mới, macro tương ứng sẽ tự động chạy.

* Khoá ô Shell
1. Select the entire worksheet by clicking the Select All button (the gray rectangle directly above the row number for row 1 and to the left of column letter A).
2. Click Cells on the Format menu, click the Protection tab, and then clear the Locked check box.
This unlocks all the cells on the worksheet
Note If the Cells command is not available, parts of the worksheet may already be locked. On the Tools menu, point to Protection, and then click Unprotect Sheet.
3. Select just the cells you want to lock and repeat step 2, but this time select the Locked check box.
4. On the Tools menu, point to Protection, click Protect Sheet, and then click OK.
Note In the Protect Sheet dialog box, you have the option to specify a password and select the elements that you want users to be able to change. See Protect worksheet or workbook elements for more information

* ĐỊNH DẠNG DỮ LIỆU TỐT HƠN TRONG EXCEL
Một trong những việc mà người mới dùng Excel ngại nhất là định dạng dữ liệu, thường người dùng phải dò đến từng ô để đặt lại định dạng sai. Dưới đây là một vài mẹo nhỏ giúp bạn “thuần phục” Excel, buộc nó phải định dạng dữ liệu đúng như ý bạn ngay từ lúc nhập liệu.
• Để nhập một giá trị với định dạng tiền tệ (currency), hãy gõ một dấu đô la ($) phía trước giá trị cần nhập.
• Để nhập một giá trị với định dạng phân số (fraction), hãy gõ một số 0 và một dấu cách phía trước phân số đó. Ví dụ: nhập “0 1/2” thay vì “1/2”, Excel sẽ hiểu cần phải hiển thị giá trị theo định dạng phân số thay vì định dạng ngày tháng “01-Feb”.
• Để nhập một giá trị với định dạng phần trăm (percentage), hãy gõ dấu % phía sau giá trị của bạn (ví dụ như 70%).
• Để nhập một chuỗi số với định dạng văn bản (Text), hãy gõ một dấu ‘ ở phía trước chuỗi số đó. Mẹo này đặc biệt hữu ích vì Excel luôn “thông minh thái quá” khi hiểu các chuỗi toàn chữ số là kiểu số và tự động định dạng chúng theo kiểu số. Những chuỗi số có số 0 ở đầu sẽ bị cắt hết những số 0 cần thiết.

Nguồn: Internet

Advertisements

Thứ Sáu, 10,Tháng Mười Hai, 2010 - Posted by | Tin học ứng dụng

Không có bình luận

Trả lời

Mời bạn điền thông tin vào ô dưới đây hoặc kích vào một biểu tượng để đăng nhập:

WordPress.com Logo

Bạn đang bình luận bằng tài khoản WordPress.com Log Out / Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Log Out / Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Log Out / Thay đổi )

Google+ photo

Bạn đang bình luận bằng tài khoản Google+ Log Out / Thay đổi )

Connecting to %s

%d bloggers like this: