PDA

View Full Version : Một số kinh nghiệm trong bảng tính Excel


oitroi
04-24-2009, 09:14 PM
Trình soan thảo Excel đã trỏ nên khá quen thuộc với bất kỳ ai làm văn phòng. Nhưng đôi khi, gặp một số trở ngại là điều không thể tránh đối với những người mới vào nghề. Bài viết này muốn cung cấp một số kinh nghiệm giúp người làm Excel thấy công việc văn phòng trở nên nhẹ nhàng hơn rất nhiều

1. Chúng ta cùng bắt đầu cách giấu số 0 trong Excel nhé

Tùy thuộc vào mục đích sử dụng, thói quen cũng như sở thích của mình,các bạn có thể sử dụng một trong các phương pháp sau đây để làm biến mất số 0 trong bảng Excel.

- Giấu tất cả số 0 trong bảng tính:

Nhấn vào menu Tools-->Options, chọn thẻ View. Xoá hộp kiểm Zero values.

Có phải tất cả các số 0 trên bàn tính sẽ biến mất? bạn đừng vội lo bởi chỉ có kết quả có giá trị là 0 thực sự mất thôi, còn những số 0 (ví dụ 10) tất nhiên là không sao rồi.

- Dùng dạng số để giấu các số 0, chúng ta thực hiện như sau:

[Only registered and activated users can see links]

- Dùng định dạng có điều kiện để giấu số 0 được trả về từ kết quả của công thức:

+ Chọn ô có số 0 cần giấu.

+ Nhấn menu Format-->Conditional Formatting. Ở hộp bên trái chọn Cell Value Is, hộp thứ hai tiếp theo chọn equal to, hộp kế tiếp gõ số 0.

+ Nhấn nút Format, chọn thẻ Font. Trong hộp Color, chọn màu trắng (hay trùng với màu nền của bảng tính). Bấm OK

- Dùng công thức để giấu số 0 hoặc thay bằng dấu gạch nối (-):

Giả sử tại ô A1, A2 bạn có các số tương ứng là 5, 5. Khi lấy A1-A2 thì kết quả sẽ là 0. Bạn có thể dùng các công thức sau để giấu số 0 hoặc thay bằng dấu gạch nối (-):

=IF(A1-A2=0,”-”,A1-A2)

2. Chèn nhiều dòng trong một ô (cell)

Mỗi lần nhấn Enter con trỏ nhảy một phát sang ô khác phía dưới. Làm thế nào để con trỏ nghe lời nằm yên trên ô đang nhập liệu đây? 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ế là bạn có thể viết tiếp trong ô đang làm việc mà dòng lại được ngắt xuống dưới theo ý người dùng mong muốn.

3. Thêm màu, ảnh nền cho toàn bộ 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.

4. Đó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 . 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…

Để thành thạo, sử dụng tốt và khai thác hết tính năng tuyệt vời của chương trình Excel chắc hẳn chúng ta cần phải sử dụng và làm thật nhiều trình soạn thảo này. Hi vọng, với những thao tác đơn giản này có thể giúp ích được cho các bạn trong công việc.

Thủ thuật EXCEL


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.

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ả.