1
Cùng tham khảo những hướng dẫn học kế toán bằng Excel nhé các bạn. Đây là phần mềm rất quan trong và thường sử dụng trong ngành kế toán đấy.
Học Excel đơn giản dành cho kế toán
Chương 1: GIỚI THIỆU
1.1 Giới thiệu:
Bảng tính điện tử Excel là một phần mền chuyên dụng cho công tác kế toán, văn phòng trên môi trường Windows với các đặt tính và ứng dụng tiểu biểu sau :
- Thực hiện được nhiều phép tính từ đơn giản đến phức tạp
- Tổ chức và lưu trữ thông tin dưới dạng bảng như bảng lương, bảng kế tóan ...
- Khi có thay đổi dữ liệu, bảng tính tự động tính toán lại theo số liệu mới
- Có kèm theo các biểu đồ và hình vẽ minh họa ...
1.2 Làm quen với Microsoft Excel
1.2.1 Khởi động
- Cách 1: Click nút Microsoft Excel trên thanh Shortcut của Microsoft Office
- Cách 2: Chọn Start - Programs - Microsoft Excel
1.2.2 Màn hình giao tiếp của Microsoft Excel
a. Các thành phần của màn hình Excel
- Thanh tiêu đề (Title Bar): dòng chứa tên file văn bản
- Thanh Menu ngang (Menu Bar): chứa các chức menu chức năng (Menu PopUp) của ExcelĐể kích họat thanh menu, ta thực hiện như sau :
• Cách sử dụng chuột :
+ Click chức năng cần chọn trên thanh menu ngang, Menu PopUp tương ứng xuất hiện
+ Click chức năng cần thực hiện trên Menu PopUp tương ứng
• Cách sử dụng bàn phím
+ Nhấn F10
+ Dùng phím Left (ß) hoặc Right (à) để di chuyển giữa các chức năng trên thanh menu ngang
+ Nhấn Enter hoặc Down (â) trên chức năng cần chọn. Menu PopUp tương ứng xuất hiện
+ Dùng phím Up (á) hoặc Down (â) để di chuyển giữa các chức năng trên Menu PopUp tương ứng
+ Nhấn Enter tại chức năng cần thực hiện
- Các thanh công cụ (Tool Bar): Gồm các thanh chứa các nút tương ứng với những chức năng thường sử dụng. Để bật/tắt cácthanh công cụ này, chọn View - Tools bar - tên thanh công cụ cần chọn.
- Thanh công thức (Formular Bar): dùng để đưa dữ liệu và công thức vào. Để bật/tắt thanh công cụ này, chọn View-Formular bar,
- Thanh trạng thái (Status Bar): là dòng cuối cùng hiển thị các chế độ hoạt dộng của Excel:
+ Read: đang sẵn sàng làm việc.
+ Enter: đang nhập dữ liệu hay công thức
+ Pointer: đang ghi công thức tham chiếu đến một địa chỉ
+ Edit : đang điều chỉnh dữ liệu hay công thức trong ô hiện tại.
- Thanh thẻ tên bảng tính (Sheet tabs: là dòng ngay trên thanh Trạng thái hiển thị tên của các bảng tính (khi chúng chưa được đặt tên tại đây ghi sheet1, Sheet2, , Sheet 16). Bên trái là các nút chuyển tới các bảng tính
- Các thanh cuốn (Scroll Bar) : Dùng để xem các vùng khác còn bị che khuất.
- Cửa sổ bảng tính (Wookbook Window) : là cửa sổ chứa nội dung file. Tên file mặc định là Book#.
+ Đường viền ngang (Column): Ghi ác ký hiệu cột từ trái sang phải theo chữ cái A .. Z. Độ rộng mặc định của mỗi cột là 9 ký tự (tuy nhiên có thể thay đổi giá trị này từ 0 .. 25) và có tổng cộng 256 cột.
+ Đường viền dọc (Row Border): Ghi số thứ tự dòng từ trên xuống từ 1.. 16384
+ Ô (Cell): là giao của một dòng và một cột. Mỗi ô được xác định bởi một địa chỉ (ví dụ: A4). Ô có đường viền quanh là ô hiện hành
+ Bảng tính (Sheet): mỗi bảng tính bao gồm 256 cột và 16384 dòng. Tên của bảng tính là Sheet#. Mặc định mỗi Wookbook có 16 Sheet (giá trị này có thể thay đổi từ 1 .. 255)
b. Các khái niệm và định nghĩa thường dùng trong Bảng tính
v Dịch chuyển con trỏ : Theo một trong các cách sau:
- Trỏ chuột vào ô cần chuyển tớ, bấm nút trái
- Ấn các phím mũi tên: chuyển tới các hàng, cột lân cận
• PgUp, PgDn : lên hoặc xuống một màn hình
• Home : về ô A1
• Tab : sang phải một màn hình
• Shift+Tab : sang trái một màn hình
• End+Home : đến ô cuối cùng của bảng tính
- F5, địa chỉ ô, : về ô chỉ định, ví dụ để về nhanh ô h20 ta ấn nút f5 gõ H22 rồi ấn
Chú ý: các thao tác có dấu . chỉ thực hiện được khi chọn lện Tools/Option, Transition điền dấu v ở tuỳ chon Transition Navigation Keys
v Vùng : là một khối hình chữ nhật bao gồm các ô liên tục. Để xác định một khối, người ta kết hợp địa chỉ của ô trên trái và ô dưới phải. (ví dụ : A4 : C6)
- Chọn một ô : Kích chuột tại ô cần chọn
- Chọn một cột : Kích chuột tại ký hiệu cột
- Chọn một dòng : Kích chuột tại số thứ tự dòng
- Chọn một vùng :
§ Đặt con trỏ tại ô đầu vùng, ấn Shift, dùng các phím di chuyển con trỏ đến cuối vùng.
§ Đặt con trỏ chuột tại ô đầu vùng, nhấn nút trái rồi rê đến ô cuối vùng
§ Đặt con trỏ tại ô đầu vùng, ấn Shift và kích chuột tại ô cuối vùng
- Chọn nhiểu vùng : Kết hợp phím Ctrl khi chọn các vùng tiếp theo
- Chọn tòan bộ bảng tính : Ấn Ctrl+Shift+Spacebar
- Để xóa chọn vùng, kích chuột tại mộüt vị trí bất kỳ trong Workbook.
v Công thức và các toán tử sử dụng trong công thức
- Công thức : có dạng như sau
=[]
Ngoài ra chúng ta có thể kết hợp các hàm chuẩn của Excel trong công thức
- Các toán tử số học : +, - , * , / , ^ (luỹ thừa), % ( phần trăm)
- Các toán tử logic : =, <> (khác), >=,<=, >, <
1.3 Ra khỏi Excel: theo một trong các cách sau
- Chọn File, Exit hoặc
- Ấn Alt+F4
- Nháy đúp vào dấu trên cùng ở góc phải của màn hình để trở về Window
Chương 2: CÁC THAO TÁC VỚI BẢNG TÍNH
1. Tạo mới một bảng tính (Ctrl+N)
- Click nút New trên thanh Standard Tool (hoặc File- New), hộp hội thoại New xuất hiện
- Chọn General và Workbook
- Chọn OK
2. Mở một Workbook đã có (Ctrl+O)
- Click nút Open trên thanh Standard Tool (hoặc chọn File - Open), hộp hội thọai Open xuất hiện
- Chọn thư mục (Folder) chứa file tài liệu cần mở trong mục look in
- Chọn tên file tài liệu cần mở rồi chọn OK
- Ngoài ra Excel còn có cách mở bảng tính khác: chọn mục File xuất hiện Menu dọc với danh sách 4 bảng tính mới làm gần nhất ở phía dưới. Ta sẽ chọn tên tệp cần mở từ danh sách này.
3. Lưu trữ Bảng tính (CTRL+S)
- Click nút Save trên thanh Standard Tool (hoặc chọn File - Save). Nếu đây là lần đầu tiên thực hiện thao tác này với bảng tính, ta phải gõ vào tên cho bảng tính trong ô File Name theo quy tắc đặt tên tệp của DOS.
- Sau đó trong quá trình làm việc, ta thường xuyên ghi bảng tính lên đĩa bằng cách trên mà không cần đặt tên cho nó nữa
- Nếu ta cần lưu bảng tính với tên khác, chọn mục Save As và đặt tên mới cho nó.
4. Chèn thêm 1 bảng tính
- Insert/ Wooksheet hoặc
- Nháy nút phải chuột trên thanh thẻ tên bảng tính để gọi Menu tắt (quy ước ta gọi thao tác này là “Menu tắt”), chọn Insert Wooksheet
5. Xoá bớt 1 bảng tính
- Edit, Delete Sheer hoặc
- [Menu tắt], Delete Sheet
6. Đổi tên bảng tính
- Nháy đúp vào thẻ tên (tức là vào tên bảng tính, sau đây chúng ta quy ước gọi là thẻ tên) trên thanh thẻ tên hoặc Format/Sheet/Rename hoặc [Menu tắt]/Rename
- Gõ vào tên mới, hoặc OK
7. Sao chép/ Chuyển bảng tính
- Cách 1: Giữ Ctrl trong khi kéo thẻ thẻ tên tại một thẻ tên khác (Sheet khác). Nếu không giữ Ctrl bảng tính sẽ được di chuyển.
- Cách 2: Edit/Move or Copy Sheet. Chọn vị trí đặt bảng tính hiện tại trước bảng tính nào trong khung Before Sheet. Nếu đánh dấu chọn vào Create a Copy, excel sẽ sao chép bảng tính chứ không di chuyển nó
- Lưu ý: chúng ta chỉ nên dùng cách 2 nếu bảng tính nguồn và đích cách xa nhau (không thể thấy thẻ tên của chúng cùng một lúc được)
8. Tách bảng tính
Có 3 cách giúp ta đồng thời thấy những phần khác nhau của bảng tính
Cách 1: Trỏ chuột vào thanh tách cho xuất hiện mũi tên 2 đầu, kéo thả nó tại vị trí cần tách
Cách 2: Đưa con trỏ ô về vị trí cần tách, chọn mục Window/Split. Sau đó để tách chọn Window/Remove Split
Cách 3: Đưa con trỏ ô về vị trí cần tách, chọn mục Window/Freeze Panes. Sau đó để bỏ tách chọn Window/Unfreeze Panes
9. Ẩn và hiện lại 1 bảng tính
Chọn Format/Sheet/Hide để ẩn bảng tính
Để hiện lại bảng tính Format/Sheet/Unhide
10. Bảo vệ bảng tính
Để những thiết lập bảo vệ và che giấu ô (xem trang 26) có tác dụng , cần bảo vệ bảng tính. Cách làm như sau;
- Tool/Protection
- Chọn Protect Sheet để bảo vệ bảng tính, chọn Protect Workbook để bảo vệ tập bảng tính.
- Nếu cần thiết gõ mật khẩu vào vùng Passwork, 2 lần gõ phải giống nhau và lưu ý rằng mật khẩu trong Excel phải phân biệt chữ hoa với chữ thường.
- Để bỏ tình trạng bảo vệ; chọn Tool/Protection/Unprotect Sheet hay Unprotect Workbook. Nếu có mật khẩu, phải gõ vào, nếu đúng ta mới cập nhật được bảng tính.
11. Chọn nhiều bảng tính
a. Liền kề: nháy chuột vào thẻ tên đầu. giữ Shift trong khi nhay chuột vào thẻ cuối
b. Cách nhau: Giữ Ctrl trong khi lần lượt nhay chuột vào các thẻ tên
c. Để bỏ việc chọn một bảng tính nào: giữ Ctrl trong khi nháy chuột vào thẻ tên của bảng tính đó.
CHƯƠNG 3: XỬ LÝ DỮ LIỆU TRONG BẢNG TÍNH
1. Các Kiểu dữ liệu:
Trong mỗi ô chỉ có thể chứa một kiểu dữ liệu. Kiểu dữ liệu của ô phụ thuộc vào ký tự đầu tiên gõ vào. Các kiểu dữ liệu trong một ô được phân ra như sau:
a. Dạng chuổi (Text)
- Bắt đầu bởi các chữ cái từ a đến z hoặc A đến Z
- Những dữ liệu chuỗi dạng số như: số điện thoại, số nhà, mã số v…v khi nhập vào phải bắt đầu bằng dấu nháy đơn (‘) và không có giá tính toán.
- Theo mặc định, dữ liệu dạng chuỗi được căn sang trái ô
b. Dạng số (Number)
Bắt đầu bởi: - Các số từ 0 đến 9
- Các dấu +, -, (, *, $ (hoặc một dấu đơn vị tiền khác tuỳ thuộc việc đặt các thông số quốc tế của Windows).
- Theo mặc định, dữ liệu dạng số được căn sang phải ô
c. Dạng công thức (Formulas)
Bắt đầu bởi các dấu = hoặc +. Sau khi ấn công thức nhập vào chỉ thể hiện trên thanh công thức còn kết quả của nó được thể hiện trong ô. Trong một số trường hợp ta có một số kết quả như sau:
Kết quả Nguyên nhân
##### Cột quá hẹp
#Div/0! Chia cho O
#NAME? Thực hiện phép tính với một biến không xác định (tên không gắn với một ô hay một vùng nào cả)
#N/A Tham chiếu đến một ô rỗng hoặc không có trong danh sách
#VALUE! Sai về kiểu của toán hạng (ví dụ: lấy số chia cho ký tự hoặc ngày tháng)
d. Dạng Ngày (Date), giờ (Time)
Ta có các quy ước sau:
- DD là 2 con số chỉ Ngày
- MM là 2 con số chỉ Tháng
- YY là 2 con số chỉ Năm
Ta có thể nhập ngày tháng theo dạng MM/DD/YY hoặc DD/MM/YY tuỳ thuộc vào việc đặt các thông số quốc tế của Windows, ví dụ nếu đặt thông số quốc tế kiểu Pháp, ta gõ vào 27/09/04, trường hợp kiểu Mỹ (ngầm định) ta gõ vào 0/27/04. Khi nhập sai dạng thức, Excel tự động chuyển sang dạng chuỗi ( căn sang trái ô) và ta không thể dùng dữ liệu này để tính toán
Có thể nhập ngày bằng cách:
- Nhập hàm = DATE(YY,MM,DD) đây là cách nhập ngày tốt nhất
- Sau đó chọn Format, Cells, Number, Date và chọn dạng thể hiện ngày ở khung bên phải
- Đặc biêt: CTRL+; cho Ngày hệ thống. CTRL+Shift+; cho Giờ hệ thống.
Theo mặc định, dữ liệu dạng ngày tháng được căn sang phải ô
2. Các toán tử trong công thức
a. Toán tử số
+ : cộng
- : trừ
* : nhân (ví dụ = 2*10 cho kết quả là 20)
/ : chia = 12/4.............................. 3
^ : luỹ thừa =5^2................................ 25
% : phần trăm =50%*600...................... 300
- Thứ tự ưu tiên của các phép toán như sau: luỹ thừa trước rồi đến nhân chia và sau cùng mới đến cộng trừ. Các phép toán cùng mức ưu tiên (như nhân chia hoặc cộng trừ) được thực hiện từ trái sang phải.
- Muốn thay đổi thứ tự ưu tiên ta dùng các cặp ngoặc tròn, toán tử trong cặp ngoặc ở sâu nhất sẽ được thực hiện trước.
- Ví dụ: các ô A1, B1, C1 chưa các số 2, 3,4,
+Nếu trong ô D1 gõ =A1+B1*C1 sẽ được kết quả là 14
+Nếu trong ô D1 gõ = (A1+B1)*C1 sẽ được kết quả là 20
b. Toán tử nối chuổi: ký hiệu &
Ví dụ : chuỗi s1=’tin”
chuối s2=’hoc ’
chuỗi s2=s1&s2 cho kết quả s3=’tin hoc’
c. Toán tử so sánh
> : lớn hơn >= lớn hơn hoặc bằng <> khác
< : nhỏ hơn <= nhỏ hơn hoặc bằng
Các toán tử so sánh cho kết quả là True (đúng) hoặc False (sai). Ví dụ trong ô A1 đang có số 26, ô B1 có số là –125. Nếu tại C1 gõ vào công thức
= A1>B1 kết quả là TRUE
= A1<=B1 sẽ nhận được kết quả là FALSE
3. Nhập liệu
a. Dữ liệu bất kỳ
- Đưa con trỏ về ô cần thiết
- Nhập dữ liệu theo loại dạng thức
- Để kết thúc việc nhập dữ liệu, làm theo một trong các cách sau:
o Ấn phím Enter con trỏ sẽ xuống ô dưới
o Ấn một phím mũi tên để đưa con trỏ ô sang ô cần thiết, ví dụ ấn phím à sẽ đưa con trỏ ô sang bên phải
o Chọn nút (màu xanh lá cây)trên thanh công thức
o Trỏ chuột vào ô cần tới, ấn nút trái
- Nhập dữ liệu sử dụng miền nhập dữ liệu
o Đánh dấu khối miền cần nhập dữ liệu (sau này quy định gọi là miền nhập dữ liệu)
o Để nhập dữ liệu theo từng hàng ấn phím Tab sau mỗi lần gõ dữ liệu vào
o Để nhập theo từng cột ấn phím sau mỗi lần gõ dữ liệu vào một ô
b. Dữ liệu trong các ô giống nhau:
- Đánh dấu khối miền nhập dữ liệu
- Gõ vào dữ liệu
- Kết thúc việc nhập bằng cách ấn phím Ctrl+Tab
c. Dữ liệu trong các ô tuân theo một quy luật
- Chuỗi số với bước nhảy là 1:
o Đưa con trỏ về ô đầu tiên của miền, gõ vào số bắt đầu. Ví dụ để đánh số thứ tự cho một số ô bắt đầu từ 1, ta gõ 1
o Trỏ chuột vào mốc điền để xuất hiện dấu + màu đen, giữ phím Ctrl trong khi kéo và thả chuột tại ô của miền (thao tác này gọi là điền tự động). Kết quả ta được chuỗi số 1,2,3….
- Chuỗi số với bước nhảy bất kỳ:
o Đưa con trỏ về ô đầu tiên của miền, gõ vào số bắt đầu, ví dụ để có chuỗi số chẵn ta gõ 2 vào một ô nào đó
o Về ô dưới (hoặc bên phải) của miền, gõ vào số tiếp theo ví dụ 4
o Đánh dấu khối 2 ô này, trỏ chuột vào mốc điền cho xuất hiện dấu + màu đen, kéo và thả chuộ tại ô cuối của miền
- Điền một danh sách tự tạo
Þ Nếu danh sách này chưa có thì phải tạo bằng cách
o Tools/Option/Custom List
o Trong khung List Entries lần lượt nhập các giá trị cho danh sách hết mỗi giá trị bấm enter để xuống dong. Ví dụ : Thứ hai Thứ ba .
o Nhấn OK
Þ Để sử dụng danh sách vừa tạo ra ta thực hiện;
o Nhập một giá trị có trong danh sách tự tạo vào ô đầu
o Điền tự động tới ô cuối miền
4. Sửa, xoá dữ liệu
a. Xoá
- Đưa con trỏ ô về ô hoặc chọn miền cần thiết
- Ấn phím Delete
b. Sửa: thực hiện một trong hai cách sau
- Nhay đúp chuột tại ô (hoặc ấn phím f2) nội dung của ô xuất hiện tại ô đó và tại thanh công thức, đưa con trỏ bàn phím về chỗ cần thiết và sửa. Ta nên bấm chuột tại thanh công thức và sửa tại đó, sửa xong chọn ký hiệu (hoặc ấn emter) để ghi lại, chọn (hoặc ấn Esc) để huỷ bỏ mọi sửa đổi.
- Nhập dữ liệu mới cho ô, sau đó nhấn phím enter
5. Các thao tác với khối
Khối là một miền chữ nhật trên bảng tính
a. Đánh dấu (chọn) khối
- Trỏ chuột vào một góc của khối, bấm nút trái đồng thời kéo chuột về góc đối diện theo đường chéo. Hoặc
- Đưa con trỏ ô về một góc của khối, giữ phím Shift đồng thời sử dụng các phím mũi tên để đưa con trỏ ô về góc đối diện theo đường chéo
- Chú ý: sau khi được chọn toàn bộ khối trừ ở góc xuất phát sẽ được đổi màu
- Một số cách chọn khối đặc biệt:
+ Khối là miền liên tục
§ Chọn một cột: ấn nút trái chuột tại tên cột đó (các chữ A, B,…) hoặc ấn Ctrl+Dấu cách
§ Chọn một hàng: ấn nút trái chuột tại số thứ tự của hàng đó (các chữ 1,2,…) hoặc ấn Shift+Dấu cách
§ Chọn toàn bộ bảng tính: ấn nút trái chuột tai nút chọn toàn bộ bảng tính (bên trái cột A phía trên hàng 1) hoặc ấn tổ hợp phím Ctrl+Shift+dấu cách
+ Chọn các ô rời rạc: đưa con trỏ tới ô đầu định chọn, giữ Ctrl và bấm nút trái chuột tai các ô định chọn tiếp theo
+ Khối là các miền rời rạc: chọn vùng đầu, giữ phím Ctrl bấm nút trái và rê chuột tại các miền khác.
Ví dụ: để chọn được các miền như hình dưới đây, ta làm theo các bước sau:
- Chọn miền liên tục B2:C3
- Ấn giữ phím Ctrl và chọn miền liên tục E4:G7
- Giữ nguyên phím Ctrl và chọn miền liên tục C9:F11
b. Copy, xoá,dán khối dùng bộ nhớ đệm (thướng dùng khi miền đích và miền nguồn cách nhau hoặc để dán một khối vào nhiều chổ khác nhau). Trước khi thực hiện các thao tác sau, ta phải chọn khối cần thiết
- COPY: chọn biểu tượng copy, hoặc ấn Ctrl+C hoặc chọn Edit/Copy
- Xoá: chọn biểu tượng Cut, hoặc ấn Ctrl+X, hoặc chọn mục Edit/Cut
- Lấy khối từ bộ nhớ đếm ra dán vào bảng tính
§ Đưa con trỏ ô tới góc trái trên của miền đích
§ Chọn biểu tượng Paste hoặc ấn Ctrl+V hoặc chọn mục Edit/Paste
c. Copy, chuyển khối dùng chuột
- Chọn khối cần thiết
- Trỏ chuột vào biên của khối sao cho xuất hiện . Sau đó thực hiện một trong các thao tác sau:
§ Giữ Ctrl đồng thời kéo và thả khối tại vị trí đích để Copy khối. Nếu trong bước này không dùng phím Ctrl thì khối sẽ được chuyển tới vị trí đích hoặc
§ Bấm giữ nút phải chuột, kéo và thả khối tại vị trí đích… Xuất hiện menu cho phép chọn một trong các phương án
o Copy : copy toàn bộ, bao gồm cả dữ liệu và khuôn dạng
o Move :chuyển dữ liệu
o Copy value : chỉ copy dữ liệu
o Copy Format :chỉ copy khuôn dạng
Chú ý: khi copy dữ liệu
- Nếu miền nguồn chứa dữ liệu số hoặc chuỗi, kết quả miền đích sẽ giống miền nguồn
- Nếu miền nguồn chứa công thức, kết quả miền đích sẽ thay đổi hay không tuỳ thuộc vào công thức trong miền nguồn tham chiếu đến địa chỉ tương đối hay địa chỉ tuyệt đối
6. Xử lý ô, cột, hàng trong bảng tính
a. Thay đổi kích thước cột, hàng
P Một cột / một hàng
- Cột: trỏ chuột vào vạch đứng ở bên phải của cột sao cho xuất hiện , kéo và thả vạch đó tại vị trí mới
- Hàng: trỏ chuột vào vạch ngang dưới số thứ tự hàng sao cho xuất hiện , kéo và thả vạch đó tại vị trí mới
P Nhiều cột/ nhiều hàng
- Cột:
+ Chọn một số ô của các cột
+ Format/Colum/Width
+ Gõ vào độ rộng mới cho các cột
+ Ok hoặc Enter
- Hàng
+ Chọn một số ô của các hàng
+ Format/Row/Height
+ Gõ vào chiều cao mới cho các hàng
+ Ok hoặc Enter
b. Chèn thêm cột , hàng, ô
P Cột:
- Chọn khối là tên các cột (các chữ A, B,..) tại vị trí cần chèn, cần thêm bao nhiêu cột ta chọn bấy nhiêu.
- Chọn Inserrt/Columns. Excel sẽ chèn thêm các cột trống và đẩy các cột được chọn sang phải
P Hàng :
- Chọn khối là số thứ tự của các hàng (các số 1,2,..) tại vị trí cần chèn, cần thêm bao nhiêu hàng ta chọn bấy nhiêu
- Chọn Inserrt/Rows. Excel sẽ chèn thêm các hàng trống và đầy các được chọn xuống dưới
P Ô
- Chọn khối là các ô tại vị trí cần chèn (như các ví dụ dưới đây là các ô C3:D4), cần thêm bao nhiêu ô ta chọn bấy nhiêu
- Chọn Insert/Cells xuất hiện hộp thoại Insert
Chọn Tác dụng
Shift Cells Right đầy các ô được chọn sang phải
Shift Cells Left đẩy các ô được chọn xuống dưới
Ok hoặc Enter thực hiện lênh
c. Xoá cột, hàng, ô
P Cột:
- Chọn khối là tên các cột (các chữ A, B,…) tại vị trí cần xoá, cần xoá bao nhiêu cột ta chọn bấy nhiêu
- Chọn Edit, Delete
P Hàng
- Chọn khối là số thứ tự các hàng (các số 1,2,…) tại vị trí cần xoá, cần xoá bao nhiêu cột ta chọn bấy nhiêu
- Chọn Edit/Delete
P Ô
- Chọn khối là các ô cần xoá
- chọn Edit/Delete.
Xuất hiện hộp thoại Delete
Chọn Tác dụng
Shift Cells Left Chuyển dữ liệu của các ô bên phải sang vùng bị xoá
Shift Cells Up chuyển dữ liệu của các ô phía dưới lên vùng bị xóa
Entire Row Xoá toàn bộ các hàng chứa vùng được chọn
Entire Column Xoá toàn bộ các cột chứa vùng được chọn
Ok hoặc Enter Thực hiện lệnh
d. Chuyển hàng thành cột và ngược lại
Giả sử cần chuyển dữ liệu đang được xếp thành cột trong các ô A2:A6 thành hàng tại các ô C3:F3, ta làm như sau:
- Chọn các ô muốn cho đổi chổ hay còn gọi là miền nguồn (A2:A6)
- Chọn biểu tượng Copy hoặc Ctrl+C
- Đưa con trỏ về ô đầu tiên của miền đầu (C3)
- [Menu tắt] hoặc chọn mục Edit, past special, đánh dấu chọn ô Transpose
- OK
Lưu ý: miền nguồn và miền dán không được giao nhau (không được có ô chung). Số lượng ô tại miền nguồn phải bằng số lượng ô tai miền dán.
e. Ẩn/ hiện cột hàng
Để tiện cho thao tác, trên các bảng tính nhất là các bảng tính lớn người ta thường cho ẩn các cột (hoặc hàng) không cần thiết. Khi nào cần lại cho chúng hiện trở lại. Cách làm như sau:
- Chọn các cột (hoặc các hàng) cần ẩn đi.
- Chọn Format, Column (hoặc Row)
- Chọn Hide
Tại vị trí các cột (hoặc hàng) ẩn, xuất hiện đường kẻ dọc (hoặc ngang) đậm, các cột hoặc hàng bị ẩn vẫn có tác dụng (vẫn sử dụng để tính toán). Để cho chúng hiện trở lại, ta làm như sau:
- Chọn các cột hoặc các hàng liền kề với chúng. Ví dụ cần cho các cột C,D,E hiện trở lại ta chọn các cột đứng ngay trước và sau chúng: B, C. Cần cho các hàng 4,5,6 hiện trờ lại ta chọn các hàng ở ngay trên và ngay dưới chúng 3,7
- Chọn Format, Column hoặc Row, Unhide.
f. Cố định cột, hàng tiêu đề
Ở các bảng tính lớn, khi cuộn xem hoặc nhập dữ liệu ở phần dưới thì không còn thấy tiêu để cột của chúng ở hàng trên cùng, do đó rất dễ bị nhầm lẫn giữa cột nọ với cột kia. Tương tự như vậy, khi cuộn xem hoặc nhập dữ liệu ở bên phải thì không còn thấy các cột trái nhất rất dễ bị nhầm lẫn hàng nọ với hàng kia, Excel giúp ta cố định cột, hàng tiêu , cách làm như sau:
- Đưa con trỏ về ô chuẩn để cố định. Ô này phải nằm ngay dưới hàng và ngay bên phải cột cần cố định. Ví dụ cần cố định các cột A, B và các hàng 1,2 ta đưa con trỏ về ô C3
- Chọn Window, Freeze Pane. Tại ô chuẩn xuất hiện 2 đường kẻ dọc và ngang
- Để huỷ bỏ việc cố định cột, hàng tiêu đề, chọn Window, Unfreeze Pane
7. Định dạng dữ liệu
a. Định dạng ký tự: tương tự như trong Word
b. Định dạng số: chọn miền dữ liệu cần định dạng, sau đó chọn các biểu tượng tương ứng trên thanh định dạng
Số Dùng biểu tượng Đổi thành
123 Currency Style ( Điền dấu tiền tệ) $123.000
123456 CommStyle (Dấu tách nghìn, triệu, tỷ) 123,456.00
0.156 PercentStyle (Nhân với 100 và điền dấu %) 15.6%
12.346 Increase decimal (lấy thêm 1 số thập phân) 12.3460
12.346 Decrease Decimal (giảm đi 1 số thập phân) 12.35
Trong các ví dụ trên, các thông số quốc tế (dấu tiền tệ, dấu tách nghìn, triệu, tỷ; dấu thập phân) là kiểu Mỹ (ngầm định). Có thể đặt lại các thông số này cho phù hợp..
Để ẩn (che đi) các số không (0)
- Chọn các ô cần thiết
- Chọn Format Cells
- Chọn Number
- Trong ô Code gõ vào 0;0;
- Ok
c. Canh biên (dóng hàng)
- Chọn vùng dữ liệu cần canh biên
- Format/cells. Xuất hiện hộp thoại Format Cell
- Chọn Aligument. Các thông số trong ô bao gồm
+ Horizontal: theo chiều ngang
o General: ký tự được căn về bên trái, số được căn sang phải, các giá trị logic và lỗi được căn vào giữa
o Left, Center, Right: căn sang trái, vào giữa, sang phải
o Fill : tự điền đầy ô (trong ô chỉ cần gõ 1 nhóm ký tự sau đó chọn mục này, Excel sẽ điền đầy miền bằng nhóm này)
o Justiffy: cân thẳng 2 bên. Trường hợp độ rộng của cột nhỏ hơn nội dung bên trog nó, Excel tự động tăng chiều cao của hàng.
o Center Across: căn vào giứa miền đã chọn (tương tự việc chọn biểu tượng )
+ Orientation: hướng phân bố
+ Wrap Text: nếu chọn sẽ cho xuống dòng trong các ô khi nội dung vượt quá chiều ngang ô (như cột trung bình)
d. Kẻ khung
- Chọn vùng dữ liệu cần kẻ khung
- Dùng chuột:
+ Chọn biểu tượg Border
+ Chọn tiếp loai đường kẻ cần thiết
- Dùng Menu
+ Chọn Format, Cells, Boders
+ Chọn tiếp đường cần kẻ trong khung Border
Outline : viền xung quanh khối ô
Left, Right, Top, Bottom : viền trái, phải, trên, dưới mỗi ô
Color : màu của đường kẻ
+ Chọn kiểu đường kẻ trong khung Style
e. Tô màu
- Format, Cell, Patterns
- Chọn Color để đặt màu tô
f. Đinh dạng tự động
Excel có sẵn một số khuôn mẫu được sử dụng để định dạng. Các bước như sau:
- Chọn vùng dữ liệu cần định dạnh
- Format, AutoFormat
- Chọn kiểu tạo khuôn trong danh sách Table Format
- Nút Option cho phép thực hiện việc tạo khuôn tự động hạn chế trên các kiểu dữ liệu nhất định.
8. Đặt tên cho ô
Đặt tên cho ô hay nhóm ô có tác dụng sau:
- Tên dễ đọc, dễ nhớ.
- Khi ta gán tên có thể tham chiếu tới ô hay nhóm ô bằng tên.
- Việc chuyển về một ô (hay miền) đã được đặt tên trở nên nhanh chóng và thuận tiện.
a. Đặt tên cho ô hay nhóm ô bằng tay
Sau khi chọn ô hay nhóm ô cần đặt tên làm theo một trong hai cách sau:
- Cách 1:
+ Nháy chuột tại của hộp Tên trên thanh công thức, địa chỉ của ô đổi sang màu xanh.
+ Xoá địa chỉ đó đi, gõ vào tên rồi ấn
- Cách 2:
+ Chọn mục Insert, Name, Define (hoặc ấn Ctrl+F3)
+ Trong khung Name in Workbook gõ vào tên cho ô hay nhóm ô
+ OK
b. Đặt tên theo tiêu đề của cột hay hàng (tự động)
Các bước như sau:
- Chọn ô hay nhóm ô cần đặt tên gồm cả các tiêu đề cột hoặc hàng
- Chọn Insert, Name, Create (hoặc Ctrl+Shift+F3)
- Ý nghĩa các mục trong hộp Create Năm như sau
+ TopRow: Lấy ô ở hàng đầu (của khối đã chọn) làm tên.
+ Bottom Row: Lấy ô ở hàng cuối (của khối đã chọn) làm tên
+ Left Column: lấy ô ở cột bên trái (của khối đã chọn) làm tên
+ Right Column: lấy ô ở cột bên phải (của khối đã chọn) làm tên
- OK
c. Dán tên vào công thức
Khi nhập hay sửa công thức, thay vì điền vào địa chỉ của ô (hay miền) đã được đặt tên ta
- Ấn F3 (hoặc nháy chuột tại của hộp tên trên thanh công thức, hoặc chọn mục Insert, Name, Paste)
- Chọn tên cần thiết từ danh sách rồi OK
d. Về nhanh một ô (hay miền) đã được đặt tên
Có thể đưa con trỏ về nhanh một ô (hay miền) đã được đặt tên bằng một trong hai cách sau:
Cách 1: nháy chuột tại của hộp tên trên thanh công thức và chọn tên cần thiết từ danh dách (hoặc gõ địa chỉ của ô)
Cách 2: Ấn F5. Chọn tên cần thiết từ danh sách.OK
Đây cũng là phương pháp kiểm tra xem Tên đã được đạt cho một ô hay miền nào.
e. Xoá tên
- Chọn Insert, Name, Define
- Chọn tên cần xoá từ danh sách
- Chọn delete
- Chọn OK hoặc Close
f. Chú ý
- Nếu muốn lấy tên của nhóm ô này để đặt cho nhóm ô khác, trước hết phải xoá tên đó đi
9. Ghi chú cho ô
Có thể thêm phần ghi chú cho từng ô riêng rẻ. Nhưng ghi chú này giúp ta giải thích bảng tính của mình một cách rõ ràng hơn
a. Tạo ghi chú
- Về ô cần tạo ghi chú
- Mở hộp thoại CellNote theo một trong hai cách sau:
+ Cách 1: Shift+F2
+ Cách 2: Chọn mục Insert/ Note
- Gõ vào lời ghi chú trong khung TextNote
- OK để đóng hộp thoại
Một chấm vuông màu đỏ ở góc phải trên của ô chỉ ra rằng ô đó đã được ghi chú. Nếu không thấy chấm vuông này hãy làm như sau:
- Tools/Options/View
- Đánh dấu chọn vào ô Note Indicator
b. Xem/ Sửa. Xoá các ghi chú
- Nếu cần xem ghi chú của một ô: chỉ việc rà chuột vào ô đó, một khung với nội dung ghi chú xuất hiện. Để tắt khung này: rà chuột sang ô khác
- Để sửa hay xoá một ghi chú: mở hộp thoại Note In Sheet
- Tiến hành sửa nội dung của ghi chú trong khung Text Note. Để xóa ghi chú chọn Delete
10 Bảo vệ ô
Trong một số trường hợp, để giới hạn quyền sử dụng của người khác, ví dụ: không cho họ sửa đổi một số ô này hay xem công thức ở một số ô khác vv hãy sử dụng tính năng bảo vệ và che dấu ô. Cách làm như sau:
- Chọn ô hay nhóm ô cần bảo vệ
- Chọn mục Format/Cells/Protection
- Trong hộp thoại đánh dấu chọn vào
+ Locked : để khoá không cho sửa đổi
+ Hidden: để che dấu công thức
- OK
Sau đó cần phải bảo vệ bảng tính thì những thiết lập trên mới có tác dụng (xem phần bảo vệ bảng tính)
Chương IV. HÀM TRONG EXCEL
Hàm được xem như là các công thức định sẵn nhằm thực hiện các tính toán chuyên biệt. Trên ô thực hiện hàm sẽ cho một giá trị hoặc một thông báo lỗi. Excel có trên 300 hàm và được phân loại thành từng nhóm
1. Quy tắc sử dụng hàm
- Các hàm có dạng tổng quát: TÊNHÀM (Các tham biến). Ví dụ
TODAY() : cho kết quả là ngày hiện tại trong máy (hàm không tham biến)
LEN (“Excel 5.0”) : cho kết quả độ dài của chuỗi là 9 (hàm 1 tham chiếu)
AVERAGE (A1,B5,D8): cho kết quả là trung bình cộng các số trong các ô A1, B5, D5 (hàm nhiều tham biến)
- Tên hàm có thể viết thường hay hoa hoặc vừa viết thường vừa biết hoa đều được
- Các tham biến có thể có hoặc không nhưng phải đặt trong hai dấu () và cách nhau bởi dấu phẩy, chấm phẩy hoặc một dấu ngăn cách nào khác tuỳ theo cách đặt các thông số quốc tế. Trong 1 hàm có thể chứa nhiều nhất 30 tham biến nhưng không được vượt quá 255 ký tự
- Trong hàm không được có dấu cách
- Hàm phải được bắt đầu bởi dấu = hoặc dấu của một phép tính. Trường hợp dùng một hàm để làm tham biến cho một hàn khác (hàm lồng nhau, nhiều nhất là 7 mức) không cần viết dấu = trước tên hàm đó. Ví dụ: các ô A1, B1 chứa các số đo các cạnh của tam giác vuông, khi đó công thức =SQRT (SUM(A1^2,B1^2)) gõ tại ô C1 cho số đo cạnh huyền
Ở đây, SQRT là hàm khai căn bậc 2, SUM là hàm tính tổng (bình phương của 2 cạnh góc vuông), ta thấy trước hàm này không có dấu = vì nó được dùng làm tham biến (đối số) cho hàm SQRT
2. Nhập hàm vào bảng tính
Có 3 cách nhập hàm vào bảng tính:
- Gõ vào từ bàn phím
- Dùng biểu tượng (Function Winzard)
- Dùng menu
Để nhập hàm: đưa con trỏ ô về ô cần thiết rồi chọn một trong các cách sau:
a. Gõ từ bàn phím
- Gõ dấu =
- Gõ vào tên hàm, dấu (, các tham biến theo đúng dạng thức quy định, dấu)
b. Dùng biểu tượng Function Winzard. Các bước như sau:
Bước 1
- Chọn biểu tượng trên thanh công cụ. Xuất hiện hộp thoại Function Winzard như sau
Ý nghĩa của các nhóm hàm trong khung Function Category như sau:
- Most Recently Used : các hàm sử dụng gần đây nhất
- All : Tất cả các hàm
- Financial : hàm tài chính
- Date & Time : Hàm Ngày và Giờ
- Math & Trig : Hàm toán và lượng giác
- Statistical : Hàm thống kê
- Lookup & Reference : hàm tìm kiếm và tham chiếu
- Database : hàm cơ sở dữ liệu
- Text : hàm xử lý ký tự
- Logical : hàm Logic
- Information : các hàm thông tin về ô, về bảng tính vv
Bước 2 :Chọn nhóm hàm trong khung Function Category. Khi di chuyển thanh sáng đến nhóm nào, Excel sẽ liệt kê các hàm của nhóm đó theo thứ tự chữ cái trong khung Function Name.
Bước 3: chọn nhóm hàm trong khung Function Name
Bước 4: chọn OK. Xuất hiện hộp thoại Function WinZard liệt kê công dụng, khung các tham biến cần nhập, khung chứa giá trị kết quả của hàm.
Điền các tham biến của hàm bằng cách ấn nút chuột vào khung cần thiết, sau đó nhập từ bàn phím hoặc rê chuột trên miền dữ liệu.
Bước 5: Chọn OK. Excel tắt hộp thoại và ghi kết quả của hàm vào ô
Lưu ý: khi con trỏ ô ở tại ô đang chưa hàm, nếu chọn Function Winzard Excel sẽ chuyển sang việc chỉnh sửa hàm
c. Dùng menu
- Chọn mục Insert, Function. Xuất hiện hộp thoại Function Winzard
- Các bước còn lại giống như khi chọn biểu tượng
3. Một số hàm thường dùng
3.1 Hàm ngày tháng
a. DATE (year, month, day)
Chỉ ra ngày dạn số ứng với ngày tháng năm
Ví dụ: =DATE(05, 09, 09) trả về 09-09-2005
b. DAY (date)
Số ngày trong tháng của biến ngày tháng date
Ví dụ: = DAY (09-09-2005) trả về 09
c. MONTH (date)
Số tháng trong năm của biến ngày tháng date
Ví dụ: = MONTH (09-09-2005) trả về 09
d. TIME (hour, minute, second)
Chỉ ra thời gian dạng số
Ví dụ: =TIME (19,5,14) trả về 19:05:14 hoặc 7:05 PM
e. WEEKDAY (date)
Chỉ ra số thứ tự của ngày trong tuần của biến ngày tháng date (thứ hai là ngày thứ 1, thứ Ba là ngày thứ 2,.., chủ nhật là ngày thứ 7)
f. YEAR(date): số năm của biến ngày tháng date
3.2 Hàm ký tự
a. EXACT (text1, text2)
Nhận giá trị TRUE hay FALSE phụ thuộc vào hai chuỗi Text1, Text2 có giống hệt nhay nhay không
b. FIND (find_text,text, atnum)
Vị trí của chuỗi con Find_text trong chuỗi lớn Text bắt đầu từ vị trí atmun, nếu bỏ qua atnum nó được cho bằng 1. Hàm này phân biệt chữ HOA và THƯỜNG
c. FIXED (number, decimal, no_commas)
Chuyển số number thành chuỗi dạng cố định với decimal số thập phân. Nếu không ghi decimal sẽ cho 2 số thập phân. Nếu no_commas là FALSE (hoặc không ghi), chuỗi in ra sẽ có dấu ngân cách nghìn triệu
d. LEFT (text, number)
Lấy number ký tự bên trái của text
e. LEN (text)
Độ dài của chuổi ký tự text
f. LOWER (text)
Chuyển text thành chữ thường
g. MID (text, numstart, numchar)
Trả lại numchar ký tự của text bắt đầu từ vị trí numstart
h. PROPER(text)
Chuyển các chữ cái đầu của text thành chữ viết hoa
Nếu text là tiếng việt hàm này sẽ cho kết sai
i. REPLACE (oldtext, numstart, numchar, newtext)
Thay newtext vào oldtext bắt đầu từ vị trí numstart và có độ dài numchar
j. REPT(text,number)
Lập lại text liên tiếp number lần
k. RIGHT (text, number)
Lấy number ký tự bên phải của text
l. SEARCH (find_text, text, atnum)
Tương tự hàm FIND nhưng không phân biệt chữ hoa hay thường
m. SUBSTITUTE(text, oldtext, newtext, instance)
Thay thế newtext vào vị trid oldtext trong text ở lần xuất hiện Instance (nếu không có đối số này sẽ thay thế ở mọi vị trí)
Ví dụ:
= SUBSTITUDE (“Hãy xem xem”,”xem”,”nhìn”,1) trả vể Hãy nhìn xem
=SUBSTITUDE((“Hãy xem xem”,”xem”,”đây”,2) trả về Hãy xem đây
n. TRIM (text)
Cắt bỏ các ký tự trống vô nghĩa trong text
o. UPPER (text)
Chuyển text thành chữ in hoa toàn bộ
p. VALUE (text)
Chuyển text sang số
3.3 Hàm toán học
- ABS (x) : giá trị tuyệt đối của số x
- ACOS(x) : hàm arccos của x, x nằmg trong khoảng từ -1 đến 1. Giá trị góc trả về théo radian từ 0 đến p. Nếu muốn chuyển kết quả sang độ, nhân nó với 180/PI().
- ASIN(x) : Hàm arcsin của x, x nằm trong khoảng từ -1 đến 1. Giá trị trả về theo radian từ -p/2 đến p/2.
- ATAN(x) : hàm arctangent của x. Tương tự như ASIN(x)
- COS(x) : Hàm cos của góc x, x là góc theo radian, nếu là độ phải nhân nó với PT()/180
- COUTIF (range, criteria): đếm số ô không thoả tiêu chẩn cho trước
+ Range : là tập hợp các ô mà ta muốn đếm
+ Criteria : tiêu chuẩn có thể là số, chữ hoặc biểu thức, xác định xem sẽ đếm ô nào
+ Hàm COUTIF chỉ đếm theo 1 điều kiện, ví dụ chỉ đến số Nữ hoặc chỉ đếm số tuổi từ 30 trở lê. Nếu cần đếm số nữ có độ tuổi từ 30 trở lên (kết hợp 2 điều kiện) phải dùng hàm khác
- ROUND(x,n): làm tròn số x với độ chính xác đến con số thứ n
+ Nếu n<0 thì x được làm tròn đến chữ số thập phân thứ n
+ Nếu n>0 thì x được làm tròn đến chữ số bên trái thứ n của dấu (chấm) thập phân
- SQRT(x) : hàm căn bậc hai của x
- SUM(n1,n2,…): tổng của các số n1, n2
- SUMIF(range, criteria, sum_range): cộng những ô thoả mãn điều kiện nào đó
+ Range : là vùng ô để so sánh với Criteria
+ Criteria : là điều kiện cộng, có thể là số, chữ hoặc biểu thức. Quyết định ô nào trong vùng Sum_range sẽ được cộng.
+ Sum_range : là vùng ô sẽ được cộng. Các ô trong Sum_range chỉ được cộng nếu các ô tưng ứng với nó trong Range thoả mãn Criteria
3.4 Hàm logic
- AND (Logic1, Logic2,…)
Nhận giá trị TRUE nếu tất cả các biểu thức logic1, logic2,… đều là TRUE, nhận giá trị FALSE nếu có ít nhất một đối số là FALSE
- FALSE : cho giá logic FALSE
- IF (logical_test, value_if_true, value-if_false)
Trả lại giá trị ghi trong value_if_true nếu logical_test là TRUE và giá trị ghi trong value-if_false nếu trong trường hợp ngược lại. Hàm IF có thể lồng nhau đến 7 cấp.
- NOT (logic): hàm phủ định
Ví dụ: =NOT(1+1>2) trả về TRUE
=NOT(1+1=2) trả về FALSE
- OR (logic1, logic2,…)
Nhận giá trị True nếu một trong các biểu thức logic1, logic2,… là TRUE, nhận giá trị FALSE nếu tất cả các biểu thức là FALSE
- TRUE () nhận giá trị logic TRUE
3.5 Hàm thống kê
- AVERAGE (num1, num2,…): tính trung bình cộng của số num1, num2,…
- COUNT (address): tính số các ô dữ liệu kiểu số trong miền Address
- COUNTA (Addr): tính số các ô không rỗng trong vùng Addr
Ví dụ: =COUTA (A1:E5) trả về 23 (có 2 ô rỗng là A5 và D5)
- MAX (num1,num2,…) giá trị lớn nhất của các số num1, num2,…
- MIN (num1, num2,…) giá trị nhỏ nhất của các số num1, num2,…
- RANK (số, danh sách, tuỳ chọn): xác định thứ hạng của số so với chuổi các số trong danh sách, tức là xem số đó đứng thứ mấy trong chuỗi số
+ Sắp xếp giảm dần nếu không có tuỳ chọn hoặc tuỳ chọn bằng 0
+ Sắp xếp tăng dần nếu tuỳ chọn là một số lớn hơn 0
+ địa chỉ của danh sách phải là tuyệt đối
3.6 Hàm tìm kiếm và tham chiếu
a. VLOOKUP (lookup_value, table_array, col_index_num, range_lookup).
- Lookup_value : giá trị đươc tìm kiếm trên cột bên trái của Table_array.
- Table_array : vùng tìm kiếm hay còn gọi là bảng tra cứu, địa chỉ phải là tuyệt đối, nên đặt tên cho vùng này
- Col_index_num : số thứ tự cột trong table_array, nơi VLOOKUP sẽ lấy giá trị trả về
- Range_lookup : giá trị logic xác định việc tìm kiếm là chính xác hay gần đúng, nếu là
o TRUE hay 1: Cột đầu tiên phải được sắp xếp tăng dần (khi đó có thể bỏ qua tham số thứ 4 này). Khi không thấy sẽ lấy kết quả gần đúng vì thế còn gọi là dò tìm không chính xác
o FALSE hay 0 : cột đầu tiên không sắp xếp. Tính chính xác, trả về #N/A nếu không thấy
- Ví dụ : bảng sau là kết quả thi của học sinh, dựa vào thang điểm Trung bình hãy xếp loai học sinh theo thang điểm
Để dùng hàm VLOOKUP ta thự hiện các bước sau:
o Trong miền C15:D19 gõ vào thang điểm trên dưới dạng cột. Vì đây là cách dò tìm không chính xác (trong một khoảng) nên ta chỉ gõ vào cận dưới (theo chiều tăng)của mỗi loại. Như vậy:
§ Lookup_value: là E3 điểm trung bình của học sinh thứ nhất
§ Table_array : là miền $C$1:$D$19 (miền địa chỉ tuyệt đối không đưa hàng tiêu đề C14:D4 vào) c
§ Col_index_num : là 2 vì cần lấy giá trị của cột Loại, cột này có số thứ tự là hai trong miền $C$14:D$19
o Tại ô G3 gõ vào công thức = VLOOKUP (E3, $C$14:D$19 ,2) ta nhận được Trung bình
o Copy công thức ở ô G3 xuống các ô từ G4 đến G12, excel sẽ xếp loại cho các học sinh còn lại
b. HLOOKUP (lookup_value, table_ array,row_index_num, range_lookup)
Hàm này hoạt động giống hàm VLOOKUP, điểm khác là
- Lookup-value: giá trị được tìm kiếm trên hàng dầu tiên của Table_array
- Table_array: vùng tìm kiếm viết thành hàng
- Row_index_num : số thứ tự hàng trong table_array, nơi HLOOKUP sẽ lấy giá trị về
Ví dụ: để thực hiện việc xếp loại cho học sinh ở ví dụ trên ta thực hiện như sau
o Trong miền A21:F22 ta gõ vào thang điểm ở ví dụ trên dưới dạng hàng, chú ý gõ vào cận dưới (theo chiều tăng) của mỗi loại. Như vậy:
§ Lookup-value: là E3 (điểm trung bình của học sinh thứ 1)
§ Table_array : là miền $B$21:$F$22
§ Row_index_num là 2 vì cần lấy giá trị của hàng Loại, hàng này có số thứ tự là 2 trong miền B21:F22
o Tại ô F3 điền vào công thức = HLOOKUP (E3, $B$21:$F$22,2) ta nhận được Trung bình
o Copy công thức ở ô G3 xuống các ô từ G4 đến G12, Excel sẽ xếp loại cho các học sinh còn lại
1.1 Giới thiệu:
Bảng tính điện tử Excel là một phần mền chuyên dụng cho công tác kế toán, văn phòng trên môi trường Windows với các đặt tính và ứng dụng tiểu biểu sau :
- Thực hiện được nhiều phép tính từ đơn giản đến phức tạp
- Tổ chức và lưu trữ thông tin dưới dạng bảng như bảng lương, bảng kế tóan ...
- Khi có thay đổi dữ liệu, bảng tính tự động tính toán lại theo số liệu mới
- Có kèm theo các biểu đồ và hình vẽ minh họa ...
1.2 Làm quen với Microsoft Excel
1.2.1 Khởi động
- Cách 1: Click nút Microsoft Excel trên thanh Shortcut của Microsoft Office
- Cách 2: Chọn Start - Programs - Microsoft Excel
1.2.2 Màn hình giao tiếp của Microsoft Excel
a. Các thành phần của màn hình Excel
- Thanh tiêu đề (Title Bar): dòng chứa tên file văn bản
- Thanh Menu ngang (Menu Bar): chứa các chức menu chức năng (Menu PopUp) của ExcelĐể kích họat thanh menu, ta thực hiện như sau :
• Cách sử dụng chuột :
+ Click chức năng cần chọn trên thanh menu ngang, Menu PopUp tương ứng xuất hiện
+ Click chức năng cần thực hiện trên Menu PopUp tương ứng
• Cách sử dụng bàn phím
+ Nhấn F10
+ Dùng phím Left (ß) hoặc Right (à) để di chuyển giữa các chức năng trên thanh menu ngang
+ Nhấn Enter hoặc Down (â) trên chức năng cần chọn. Menu PopUp tương ứng xuất hiện
+ Dùng phím Up (á) hoặc Down (â) để di chuyển giữa các chức năng trên Menu PopUp tương ứng
+ Nhấn Enter tại chức năng cần thực hiện
- Các thanh công cụ (Tool Bar): Gồm các thanh chứa các nút tương ứng với những chức năng thường sử dụng. Để bật/tắt cácthanh công cụ này, chọn View - Tools bar - tên thanh công cụ cần chọn.
- Thanh công thức (Formular Bar): dùng để đưa dữ liệu và công thức vào. Để bật/tắt thanh công cụ này, chọn View-Formular bar,
- Thanh trạng thái (Status Bar): là dòng cuối cùng hiển thị các chế độ hoạt dộng của Excel:
+ Read: đang sẵn sàng làm việc.
+ Enter: đang nhập dữ liệu hay công thức
+ Pointer: đang ghi công thức tham chiếu đến một địa chỉ
+ Edit : đang điều chỉnh dữ liệu hay công thức trong ô hiện tại.
- Thanh thẻ tên bảng tính (Sheet tabs: là dòng ngay trên thanh Trạng thái hiển thị tên của các bảng tính (khi chúng chưa được đặt tên tại đây ghi sheet1, Sheet2, , Sheet 16). Bên trái là các nút chuyển tới các bảng tính
- Các thanh cuốn (Scroll Bar) : Dùng để xem các vùng khác còn bị che khuất.
- Cửa sổ bảng tính (Wookbook Window) : là cửa sổ chứa nội dung file. Tên file mặc định là Book#.
+ Đường viền ngang (Column): Ghi ác ký hiệu cột từ trái sang phải theo chữ cái A .. Z. Độ rộng mặc định của mỗi cột là 9 ký tự (tuy nhiên có thể thay đổi giá trị này từ 0 .. 25) và có tổng cộng 256 cột.
+ Đường viền dọc (Row Border): Ghi số thứ tự dòng từ trên xuống từ 1.. 16384
+ Ô (Cell): là giao của một dòng và một cột. Mỗi ô được xác định bởi một địa chỉ (ví dụ: A4). Ô có đường viền quanh là ô hiện hành
+ Bảng tính (Sheet): mỗi bảng tính bao gồm 256 cột và 16384 dòng. Tên của bảng tính là Sheet#. Mặc định mỗi Wookbook có 16 Sheet (giá trị này có thể thay đổi từ 1 .. 255)
b. Các khái niệm và định nghĩa thường dùng trong Bảng tính
v Dịch chuyển con trỏ : Theo một trong các cách sau:
- Trỏ chuột vào ô cần chuyển tớ, bấm nút trái
- Ấn các phím mũi tên: chuyển tới các hàng, cột lân cận
• PgUp, PgDn : lên hoặc xuống một màn hình
• Home : về ô A1
• Tab : sang phải một màn hình
• Shift+Tab : sang trái một màn hình
• End+Home : đến ô cuối cùng của bảng tính
- F5, địa chỉ ô, : về ô chỉ định, ví dụ để về nhanh ô h20 ta ấn nút f5 gõ H22 rồi ấn
Chú ý: các thao tác có dấu . chỉ thực hiện được khi chọn lện Tools/Option, Transition điền dấu v ở tuỳ chon Transition Navigation Keys
v Vùng : là một khối hình chữ nhật bao gồm các ô liên tục. Để xác định một khối, người ta kết hợp địa chỉ của ô trên trái và ô dưới phải. (ví dụ : A4 : C6)
- Chọn một ô : Kích chuột tại ô cần chọn
- Chọn một cột : Kích chuột tại ký hiệu cột
- Chọn một dòng : Kích chuột tại số thứ tự dòng
- Chọn một vùng :
§ Đặt con trỏ tại ô đầu vùng, ấn Shift, dùng các phím di chuyển con trỏ đến cuối vùng.
§ Đặt con trỏ chuột tại ô đầu vùng, nhấn nút trái rồi rê đến ô cuối vùng
§ Đặt con trỏ tại ô đầu vùng, ấn Shift và kích chuột tại ô cuối vùng
- Chọn nhiểu vùng : Kết hợp phím Ctrl khi chọn các vùng tiếp theo
- Chọn tòan bộ bảng tính : Ấn Ctrl+Shift+Spacebar
- Để xóa chọn vùng, kích chuột tại mộüt vị trí bất kỳ trong Workbook.
v Công thức và các toán tử sử dụng trong công thức
- Công thức : có dạng như sau
=[]
Ngoài ra chúng ta có thể kết hợp các hàm chuẩn của Excel trong công thức
- Các toán tử số học : +, - , * , / , ^ (luỹ thừa), % ( phần trăm)
- Các toán tử logic : =, <> (khác), >=,<=, >, <
1.3 Ra khỏi Excel: theo một trong các cách sau
- Chọn File, Exit hoặc
- Ấn Alt+F4
- Nháy đúp vào dấu trên cùng ở góc phải của màn hình để trở về Window
Chương 2: CÁC THAO TÁC VỚI BẢNG TÍNH
1. Tạo mới một bảng tính (Ctrl+N)
- Click nút New trên thanh Standard Tool (hoặc File- New), hộp hội thoại New xuất hiện
- Chọn General và Workbook
- Chọn OK
2. Mở một Workbook đã có (Ctrl+O)
- Click nút Open trên thanh Standard Tool (hoặc chọn File - Open), hộp hội thọai Open xuất hiện
- Chọn thư mục (Folder) chứa file tài liệu cần mở trong mục look in
- Chọn tên file tài liệu cần mở rồi chọn OK
- Ngoài ra Excel còn có cách mở bảng tính khác: chọn mục File xuất hiện Menu dọc với danh sách 4 bảng tính mới làm gần nhất ở phía dưới. Ta sẽ chọn tên tệp cần mở từ danh sách này.
3. Lưu trữ Bảng tính (CTRL+S)
- Click nút Save trên thanh Standard Tool (hoặc chọn File - Save). Nếu đây là lần đầu tiên thực hiện thao tác này với bảng tính, ta phải gõ vào tên cho bảng tính trong ô File Name theo quy tắc đặt tên tệp của DOS.
- Sau đó trong quá trình làm việc, ta thường xuyên ghi bảng tính lên đĩa bằng cách trên mà không cần đặt tên cho nó nữa
- Nếu ta cần lưu bảng tính với tên khác, chọn mục Save As và đặt tên mới cho nó.
4. Chèn thêm 1 bảng tính
- Insert/ Wooksheet hoặc
- Nháy nút phải chuột trên thanh thẻ tên bảng tính để gọi Menu tắt (quy ước ta gọi thao tác này là “Menu tắt”), chọn Insert Wooksheet
5. Xoá bớt 1 bảng tính
- Edit, Delete Sheer hoặc
- [Menu tắt], Delete Sheet
6. Đổi tên bảng tính
- Nháy đúp vào thẻ tên (tức là vào tên bảng tính, sau đây chúng ta quy ước gọi là thẻ tên) trên thanh thẻ tên hoặc Format/Sheet/Rename hoặc [Menu tắt]/Rename
- Gõ vào tên mới, hoặc OK
7. Sao chép/ Chuyển bảng tính
- Cách 1: Giữ Ctrl trong khi kéo thẻ thẻ tên tại một thẻ tên khác (Sheet khác). Nếu không giữ Ctrl bảng tính sẽ được di chuyển.
- Cách 2: Edit/Move or Copy Sheet. Chọn vị trí đặt bảng tính hiện tại trước bảng tính nào trong khung Before Sheet. Nếu đánh dấu chọn vào Create a Copy, excel sẽ sao chép bảng tính chứ không di chuyển nó
- Lưu ý: chúng ta chỉ nên dùng cách 2 nếu bảng tính nguồn và đích cách xa nhau (không thể thấy thẻ tên của chúng cùng một lúc được)
8. Tách bảng tính
Có 3 cách giúp ta đồng thời thấy những phần khác nhau của bảng tính
Cách 1: Trỏ chuột vào thanh tách cho xuất hiện mũi tên 2 đầu, kéo thả nó tại vị trí cần tách
Cách 2: Đưa con trỏ ô về vị trí cần tách, chọn mục Window/Split. Sau đó để tách chọn Window/Remove Split
Cách 3: Đưa con trỏ ô về vị trí cần tách, chọn mục Window/Freeze Panes. Sau đó để bỏ tách chọn Window/Unfreeze Panes
9. Ẩn và hiện lại 1 bảng tính
Chọn Format/Sheet/Hide để ẩn bảng tính
Để hiện lại bảng tính Format/Sheet/Unhide
10. Bảo vệ bảng tính
Để những thiết lập bảo vệ và che giấu ô (xem trang 26) có tác dụng , cần bảo vệ bảng tính. Cách làm như sau;
- Tool/Protection
- Chọn Protect Sheet để bảo vệ bảng tính, chọn Protect Workbook để bảo vệ tập bảng tính.
- Nếu cần thiết gõ mật khẩu vào vùng Passwork, 2 lần gõ phải giống nhau và lưu ý rằng mật khẩu trong Excel phải phân biệt chữ hoa với chữ thường.
- Để bỏ tình trạng bảo vệ; chọn Tool/Protection/Unprotect Sheet hay Unprotect Workbook. Nếu có mật khẩu, phải gõ vào, nếu đúng ta mới cập nhật được bảng tính.
11. Chọn nhiều bảng tính
a. Liền kề: nháy chuột vào thẻ tên đầu. giữ Shift trong khi nhay chuột vào thẻ cuối
b. Cách nhau: Giữ Ctrl trong khi lần lượt nhay chuột vào các thẻ tên
c. Để bỏ việc chọn một bảng tính nào: giữ Ctrl trong khi nháy chuột vào thẻ tên của bảng tính đó.
CHƯƠNG 3: XỬ LÝ DỮ LIỆU TRONG BẢNG TÍNH
1. Các Kiểu dữ liệu:
Trong mỗi ô chỉ có thể chứa một kiểu dữ liệu. Kiểu dữ liệu của ô phụ thuộc vào ký tự đầu tiên gõ vào. Các kiểu dữ liệu trong một ô được phân ra như sau:
a. Dạng chuổi (Text)
- Bắt đầu bởi các chữ cái từ a đến z hoặc A đến Z
- Những dữ liệu chuỗi dạng số như: số điện thoại, số nhà, mã số v…v khi nhập vào phải bắt đầu bằng dấu nháy đơn (‘) và không có giá tính toán.
- Theo mặc định, dữ liệu dạng chuỗi được căn sang trái ô
b. Dạng số (Number)
Bắt đầu bởi: - Các số từ 0 đến 9
- Các dấu +, -, (, *, $ (hoặc một dấu đơn vị tiền khác tuỳ thuộc việc đặt các thông số quốc tế của Windows).
- Theo mặc định, dữ liệu dạng số được căn sang phải ô
c. Dạng công thức (Formulas)
Bắt đầu bởi các dấu = hoặc +. Sau khi ấn công thức nhập vào chỉ thể hiện trên thanh công thức còn kết quả của nó được thể hiện trong ô. Trong một số trường hợp ta có một số kết quả như sau:
Kết quả Nguyên nhân
##### Cột quá hẹp
#Div/0! Chia cho O
#NAME? Thực hiện phép tính với một biến không xác định (tên không gắn với một ô hay một vùng nào cả)
#N/A Tham chiếu đến một ô rỗng hoặc không có trong danh sách
#VALUE! Sai về kiểu của toán hạng (ví dụ: lấy số chia cho ký tự hoặc ngày tháng)
d. Dạng Ngày (Date), giờ (Time)
Ta có các quy ước sau:
- DD là 2 con số chỉ Ngày
- MM là 2 con số chỉ Tháng
- YY là 2 con số chỉ Năm
Ta có thể nhập ngày tháng theo dạng MM/DD/YY hoặc DD/MM/YY tuỳ thuộc vào việc đặt các thông số quốc tế của Windows, ví dụ nếu đặt thông số quốc tế kiểu Pháp, ta gõ vào 27/09/04, trường hợp kiểu Mỹ (ngầm định) ta gõ vào 0/27/04. Khi nhập sai dạng thức, Excel tự động chuyển sang dạng chuỗi ( căn sang trái ô) và ta không thể dùng dữ liệu này để tính toán
Có thể nhập ngày bằng cách:
- Nhập hàm = DATE(YY,MM,DD) đây là cách nhập ngày tốt nhất
- Sau đó chọn Format, Cells, Number, Date và chọn dạng thể hiện ngày ở khung bên phải
- Đặc biêt: CTRL+; cho Ngày hệ thống. CTRL+Shift+; cho Giờ hệ thống.
Theo mặc định, dữ liệu dạng ngày tháng được căn sang phải ô
2. Các toán tử trong công thức
a. Toán tử số
+ : cộng
- : trừ
* : nhân (ví dụ = 2*10 cho kết quả là 20)
/ : chia = 12/4.............................. 3
^ : luỹ thừa =5^2................................ 25
% : phần trăm =50%*600...................... 300
- Thứ tự ưu tiên của các phép toán như sau: luỹ thừa trước rồi đến nhân chia và sau cùng mới đến cộng trừ. Các phép toán cùng mức ưu tiên (như nhân chia hoặc cộng trừ) được thực hiện từ trái sang phải.
- Muốn thay đổi thứ tự ưu tiên ta dùng các cặp ngoặc tròn, toán tử trong cặp ngoặc ở sâu nhất sẽ được thực hiện trước.
- Ví dụ: các ô A1, B1, C1 chưa các số 2, 3,4,
+Nếu trong ô D1 gõ =A1+B1*C1 sẽ được kết quả là 14
+Nếu trong ô D1 gõ = (A1+B1)*C1 sẽ được kết quả là 20
b. Toán tử nối chuổi: ký hiệu &
Ví dụ : chuỗi s1=’tin”
chuối s2=’hoc ’
chuỗi s2=s1&s2 cho kết quả s3=’tin hoc’
c. Toán tử so sánh
> : lớn hơn >= lớn hơn hoặc bằng <> khác
< : nhỏ hơn <= nhỏ hơn hoặc bằng
Các toán tử so sánh cho kết quả là True (đúng) hoặc False (sai). Ví dụ trong ô A1 đang có số 26, ô B1 có số là –125. Nếu tại C1 gõ vào công thức
= A1>B1 kết quả là TRUE
= A1<=B1 sẽ nhận được kết quả là FALSE
3. Nhập liệu
a. Dữ liệu bất kỳ
- Đưa con trỏ về ô cần thiết
- Nhập dữ liệu theo loại dạng thức
- Để kết thúc việc nhập dữ liệu, làm theo một trong các cách sau:
o Ấn phím Enter con trỏ sẽ xuống ô dưới
o Ấn một phím mũi tên để đưa con trỏ ô sang ô cần thiết, ví dụ ấn phím à sẽ đưa con trỏ ô sang bên phải
o Chọn nút (màu xanh lá cây)trên thanh công thức
o Trỏ chuột vào ô cần tới, ấn nút trái
- Nhập dữ liệu sử dụng miền nhập dữ liệu
o Đánh dấu khối miền cần nhập dữ liệu (sau này quy định gọi là miền nhập dữ liệu)
o Để nhập dữ liệu theo từng hàng ấn phím Tab sau mỗi lần gõ dữ liệu vào
o Để nhập theo từng cột ấn phím sau mỗi lần gõ dữ liệu vào một ô
b. Dữ liệu trong các ô giống nhau:
- Đánh dấu khối miền nhập dữ liệu
- Gõ vào dữ liệu
- Kết thúc việc nhập bằng cách ấn phím Ctrl+Tab
c. Dữ liệu trong các ô tuân theo một quy luật
- Chuỗi số với bước nhảy là 1:
o Đưa con trỏ về ô đầu tiên của miền, gõ vào số bắt đầu. Ví dụ để đánh số thứ tự cho một số ô bắt đầu từ 1, ta gõ 1
o Trỏ chuột vào mốc điền để xuất hiện dấu + màu đen, giữ phím Ctrl trong khi kéo và thả chuột tại ô của miền (thao tác này gọi là điền tự động). Kết quả ta được chuỗi số 1,2,3….
- Chuỗi số với bước nhảy bất kỳ:
o Đưa con trỏ về ô đầu tiên của miền, gõ vào số bắt đầu, ví dụ để có chuỗi số chẵn ta gõ 2 vào một ô nào đó
o Về ô dưới (hoặc bên phải) của miền, gõ vào số tiếp theo ví dụ 4
o Đánh dấu khối 2 ô này, trỏ chuột vào mốc điền cho xuất hiện dấu + màu đen, kéo và thả chuộ tại ô cuối của miền
- Điền một danh sách tự tạo
Þ Nếu danh sách này chưa có thì phải tạo bằng cách
o Tools/Option/Custom List
o Trong khung List Entries lần lượt nhập các giá trị cho danh sách hết mỗi giá trị bấm enter để xuống dong. Ví dụ : Thứ hai Thứ ba .
o Nhấn OK
Þ Để sử dụng danh sách vừa tạo ra ta thực hiện;
o Nhập một giá trị có trong danh sách tự tạo vào ô đầu
o Điền tự động tới ô cuối miền
4. Sửa, xoá dữ liệu
a. Xoá
- Đưa con trỏ ô về ô hoặc chọn miền cần thiết
- Ấn phím Delete
b. Sửa: thực hiện một trong hai cách sau
- Nhay đúp chuột tại ô (hoặc ấn phím f2) nội dung của ô xuất hiện tại ô đó và tại thanh công thức, đưa con trỏ bàn phím về chỗ cần thiết và sửa. Ta nên bấm chuột tại thanh công thức và sửa tại đó, sửa xong chọn ký hiệu (hoặc ấn emter) để ghi lại, chọn (hoặc ấn Esc) để huỷ bỏ mọi sửa đổi.
- Nhập dữ liệu mới cho ô, sau đó nhấn phím enter
5. Các thao tác với khối
Khối là một miền chữ nhật trên bảng tính
a. Đánh dấu (chọn) khối
- Trỏ chuột vào một góc của khối, bấm nút trái đồng thời kéo chuột về góc đối diện theo đường chéo. Hoặc
- Đưa con trỏ ô về một góc của khối, giữ phím Shift đồng thời sử dụng các phím mũi tên để đưa con trỏ ô về góc đối diện theo đường chéo
- Chú ý: sau khi được chọn toàn bộ khối trừ ở góc xuất phát sẽ được đổi màu
- Một số cách chọn khối đặc biệt:
+ Khối là miền liên tục
§ Chọn một cột: ấn nút trái chuột tại tên cột đó (các chữ A, B,…) hoặc ấn Ctrl+Dấu cách
§ Chọn một hàng: ấn nút trái chuột tại số thứ tự của hàng đó (các chữ 1,2,…) hoặc ấn Shift+Dấu cách
§ Chọn toàn bộ bảng tính: ấn nút trái chuột tai nút chọn toàn bộ bảng tính (bên trái cột A phía trên hàng 1) hoặc ấn tổ hợp phím Ctrl+Shift+dấu cách
+ Chọn các ô rời rạc: đưa con trỏ tới ô đầu định chọn, giữ Ctrl và bấm nút trái chuột tai các ô định chọn tiếp theo
+ Khối là các miền rời rạc: chọn vùng đầu, giữ phím Ctrl bấm nút trái và rê chuột tại các miền khác.
Ví dụ: để chọn được các miền như hình dưới đây, ta làm theo các bước sau:
- Chọn miền liên tục B2:C3
- Ấn giữ phím Ctrl và chọn miền liên tục E4:G7
- Giữ nguyên phím Ctrl và chọn miền liên tục C9:F11
b. Copy, xoá,dán khối dùng bộ nhớ đệm (thướng dùng khi miền đích và miền nguồn cách nhau hoặc để dán một khối vào nhiều chổ khác nhau). Trước khi thực hiện các thao tác sau, ta phải chọn khối cần thiết
- COPY: chọn biểu tượng copy, hoặc ấn Ctrl+C hoặc chọn Edit/Copy
- Xoá: chọn biểu tượng Cut, hoặc ấn Ctrl+X, hoặc chọn mục Edit/Cut
- Lấy khối từ bộ nhớ đếm ra dán vào bảng tính
§ Đưa con trỏ ô tới góc trái trên của miền đích
§ Chọn biểu tượng Paste hoặc ấn Ctrl+V hoặc chọn mục Edit/Paste
c. Copy, chuyển khối dùng chuột
- Chọn khối cần thiết
- Trỏ chuột vào biên của khối sao cho xuất hiện . Sau đó thực hiện một trong các thao tác sau:
§ Giữ Ctrl đồng thời kéo và thả khối tại vị trí đích để Copy khối. Nếu trong bước này không dùng phím Ctrl thì khối sẽ được chuyển tới vị trí đích hoặc
§ Bấm giữ nút phải chuột, kéo và thả khối tại vị trí đích… Xuất hiện menu cho phép chọn một trong các phương án
o Copy : copy toàn bộ, bao gồm cả dữ liệu và khuôn dạng
o Move :chuyển dữ liệu
o Copy value : chỉ copy dữ liệu
o Copy Format :chỉ copy khuôn dạng
Chú ý: khi copy dữ liệu
- Nếu miền nguồn chứa dữ liệu số hoặc chuỗi, kết quả miền đích sẽ giống miền nguồn
- Nếu miền nguồn chứa công thức, kết quả miền đích sẽ thay đổi hay không tuỳ thuộc vào công thức trong miền nguồn tham chiếu đến địa chỉ tương đối hay địa chỉ tuyệt đối
6. Xử lý ô, cột, hàng trong bảng tính
a. Thay đổi kích thước cột, hàng
P Một cột / một hàng
- Cột: trỏ chuột vào vạch đứng ở bên phải của cột sao cho xuất hiện , kéo và thả vạch đó tại vị trí mới
- Hàng: trỏ chuột vào vạch ngang dưới số thứ tự hàng sao cho xuất hiện , kéo và thả vạch đó tại vị trí mới
P Nhiều cột/ nhiều hàng
- Cột:
+ Chọn một số ô của các cột
+ Format/Colum/Width
+ Gõ vào độ rộng mới cho các cột
+ Ok hoặc Enter
- Hàng
+ Chọn một số ô của các hàng
+ Format/Row/Height
+ Gõ vào chiều cao mới cho các hàng
+ Ok hoặc Enter
b. Chèn thêm cột , hàng, ô
P Cột:
- Chọn khối là tên các cột (các chữ A, B,..) tại vị trí cần chèn, cần thêm bao nhiêu cột ta chọn bấy nhiêu.
- Chọn Inserrt/Columns. Excel sẽ chèn thêm các cột trống và đẩy các cột được chọn sang phải
P Hàng :
- Chọn khối là số thứ tự của các hàng (các số 1,2,..) tại vị trí cần chèn, cần thêm bao nhiêu hàng ta chọn bấy nhiêu
- Chọn Inserrt/Rows. Excel sẽ chèn thêm các hàng trống và đầy các được chọn xuống dưới
P Ô
- Chọn khối là các ô tại vị trí cần chèn (như các ví dụ dưới đây là các ô C3:D4), cần thêm bao nhiêu ô ta chọn bấy nhiêu
- Chọn Insert/Cells xuất hiện hộp thoại Insert
Chọn Tác dụng
Shift Cells Right đầy các ô được chọn sang phải
Shift Cells Left đẩy các ô được chọn xuống dưới
Ok hoặc Enter thực hiện lênh
c. Xoá cột, hàng, ô
P Cột:
- Chọn khối là tên các cột (các chữ A, B,…) tại vị trí cần xoá, cần xoá bao nhiêu cột ta chọn bấy nhiêu
- Chọn Edit, Delete
P Hàng
- Chọn khối là số thứ tự các hàng (các số 1,2,…) tại vị trí cần xoá, cần xoá bao nhiêu cột ta chọn bấy nhiêu
- Chọn Edit/Delete
P Ô
- Chọn khối là các ô cần xoá
- chọn Edit/Delete.
Xuất hiện hộp thoại Delete
Chọn Tác dụng
Shift Cells Left Chuyển dữ liệu của các ô bên phải sang vùng bị xoá
Shift Cells Up chuyển dữ liệu của các ô phía dưới lên vùng bị xóa
Entire Row Xoá toàn bộ các hàng chứa vùng được chọn
Entire Column Xoá toàn bộ các cột chứa vùng được chọn
Ok hoặc Enter Thực hiện lệnh
d. Chuyển hàng thành cột và ngược lại
Giả sử cần chuyển dữ liệu đang được xếp thành cột trong các ô A2:A6 thành hàng tại các ô C3:F3, ta làm như sau:
- Chọn các ô muốn cho đổi chổ hay còn gọi là miền nguồn (A2:A6)
- Chọn biểu tượng Copy hoặc Ctrl+C
- Đưa con trỏ về ô đầu tiên của miền đầu (C3)
- [Menu tắt] hoặc chọn mục Edit, past special, đánh dấu chọn ô Transpose
- OK
Lưu ý: miền nguồn và miền dán không được giao nhau (không được có ô chung). Số lượng ô tại miền nguồn phải bằng số lượng ô tai miền dán.
e. Ẩn/ hiện cột hàng
Để tiện cho thao tác, trên các bảng tính nhất là các bảng tính lớn người ta thường cho ẩn các cột (hoặc hàng) không cần thiết. Khi nào cần lại cho chúng hiện trở lại. Cách làm như sau:
- Chọn các cột (hoặc các hàng) cần ẩn đi.
- Chọn Format, Column (hoặc Row)
- Chọn Hide
Tại vị trí các cột (hoặc hàng) ẩn, xuất hiện đường kẻ dọc (hoặc ngang) đậm, các cột hoặc hàng bị ẩn vẫn có tác dụng (vẫn sử dụng để tính toán). Để cho chúng hiện trở lại, ta làm như sau:
- Chọn các cột hoặc các hàng liền kề với chúng. Ví dụ cần cho các cột C,D,E hiện trở lại ta chọn các cột đứng ngay trước và sau chúng: B, C. Cần cho các hàng 4,5,6 hiện trờ lại ta chọn các hàng ở ngay trên và ngay dưới chúng 3,7
- Chọn Format, Column hoặc Row, Unhide.
f. Cố định cột, hàng tiêu đề
Ở các bảng tính lớn, khi cuộn xem hoặc nhập dữ liệu ở phần dưới thì không còn thấy tiêu để cột của chúng ở hàng trên cùng, do đó rất dễ bị nhầm lẫn giữa cột nọ với cột kia. Tương tự như vậy, khi cuộn xem hoặc nhập dữ liệu ở bên phải thì không còn thấy các cột trái nhất rất dễ bị nhầm lẫn hàng nọ với hàng kia, Excel giúp ta cố định cột, hàng tiêu , cách làm như sau:
- Đưa con trỏ về ô chuẩn để cố định. Ô này phải nằm ngay dưới hàng và ngay bên phải cột cần cố định. Ví dụ cần cố định các cột A, B và các hàng 1,2 ta đưa con trỏ về ô C3
- Chọn Window, Freeze Pane. Tại ô chuẩn xuất hiện 2 đường kẻ dọc và ngang
- Để huỷ bỏ việc cố định cột, hàng tiêu đề, chọn Window, Unfreeze Pane
7. Định dạng dữ liệu
a. Định dạng ký tự: tương tự như trong Word
b. Định dạng số: chọn miền dữ liệu cần định dạng, sau đó chọn các biểu tượng tương ứng trên thanh định dạng
Số Dùng biểu tượng Đổi thành
123 Currency Style ( Điền dấu tiền tệ) $123.000
123456 CommStyle (Dấu tách nghìn, triệu, tỷ) 123,456.00
0.156 PercentStyle (Nhân với 100 và điền dấu %) 15.6%
12.346 Increase decimal (lấy thêm 1 số thập phân) 12.3460
12.346 Decrease Decimal (giảm đi 1 số thập phân) 12.35
Trong các ví dụ trên, các thông số quốc tế (dấu tiền tệ, dấu tách nghìn, triệu, tỷ; dấu thập phân) là kiểu Mỹ (ngầm định). Có thể đặt lại các thông số này cho phù hợp..
Để ẩn (che đi) các số không (0)
- Chọn các ô cần thiết
- Chọn Format Cells
- Chọn Number
- Trong ô Code gõ vào 0;0;
- Ok
c. Canh biên (dóng hàng)
- Chọn vùng dữ liệu cần canh biên
- Format/cells. Xuất hiện hộp thoại Format Cell
- Chọn Aligument. Các thông số trong ô bao gồm
+ Horizontal: theo chiều ngang
o General: ký tự được căn về bên trái, số được căn sang phải, các giá trị logic và lỗi được căn vào giữa
o Left, Center, Right: căn sang trái, vào giữa, sang phải
o Fill : tự điền đầy ô (trong ô chỉ cần gõ 1 nhóm ký tự sau đó chọn mục này, Excel sẽ điền đầy miền bằng nhóm này)
o Justiffy: cân thẳng 2 bên. Trường hợp độ rộng của cột nhỏ hơn nội dung bên trog nó, Excel tự động tăng chiều cao của hàng.
o Center Across: căn vào giứa miền đã chọn (tương tự việc chọn biểu tượng )
+ Orientation: hướng phân bố
+ Wrap Text: nếu chọn sẽ cho xuống dòng trong các ô khi nội dung vượt quá chiều ngang ô (như cột trung bình)
d. Kẻ khung
- Chọn vùng dữ liệu cần kẻ khung
- Dùng chuột:
+ Chọn biểu tượg Border
+ Chọn tiếp loai đường kẻ cần thiết
- Dùng Menu
+ Chọn Format, Cells, Boders
+ Chọn tiếp đường cần kẻ trong khung Border
Outline : viền xung quanh khối ô
Left, Right, Top, Bottom : viền trái, phải, trên, dưới mỗi ô
Color : màu của đường kẻ
+ Chọn kiểu đường kẻ trong khung Style
e. Tô màu
- Format, Cell, Patterns
- Chọn Color để đặt màu tô
f. Đinh dạng tự động
Excel có sẵn một số khuôn mẫu được sử dụng để định dạng. Các bước như sau:
- Chọn vùng dữ liệu cần định dạnh
- Format, AutoFormat
- Chọn kiểu tạo khuôn trong danh sách Table Format
- Nút Option cho phép thực hiện việc tạo khuôn tự động hạn chế trên các kiểu dữ liệu nhất định.
8. Đặt tên cho ô
Đặt tên cho ô hay nhóm ô có tác dụng sau:
- Tên dễ đọc, dễ nhớ.
- Khi ta gán tên có thể tham chiếu tới ô hay nhóm ô bằng tên.
- Việc chuyển về một ô (hay miền) đã được đặt tên trở nên nhanh chóng và thuận tiện.
a. Đặt tên cho ô hay nhóm ô bằng tay
Sau khi chọn ô hay nhóm ô cần đặt tên làm theo một trong hai cách sau:
- Cách 1:
+ Nháy chuột tại của hộp Tên trên thanh công thức, địa chỉ của ô đổi sang màu xanh.
+ Xoá địa chỉ đó đi, gõ vào tên rồi ấn
- Cách 2:
+ Chọn mục Insert, Name, Define (hoặc ấn Ctrl+F3)
+ Trong khung Name in Workbook gõ vào tên cho ô hay nhóm ô
+ OK
b. Đặt tên theo tiêu đề của cột hay hàng (tự động)
Các bước như sau:
- Chọn ô hay nhóm ô cần đặt tên gồm cả các tiêu đề cột hoặc hàng
- Chọn Insert, Name, Create (hoặc Ctrl+Shift+F3)
- Ý nghĩa các mục trong hộp Create Năm như sau
+ TopRow: Lấy ô ở hàng đầu (của khối đã chọn) làm tên.
+ Bottom Row: Lấy ô ở hàng cuối (của khối đã chọn) làm tên
+ Left Column: lấy ô ở cột bên trái (của khối đã chọn) làm tên
+ Right Column: lấy ô ở cột bên phải (của khối đã chọn) làm tên
- OK
c. Dán tên vào công thức
Khi nhập hay sửa công thức, thay vì điền vào địa chỉ của ô (hay miền) đã được đặt tên ta
- Ấn F3 (hoặc nháy chuột tại của hộp tên trên thanh công thức, hoặc chọn mục Insert, Name, Paste)
- Chọn tên cần thiết từ danh sách rồi OK
d. Về nhanh một ô (hay miền) đã được đặt tên
Có thể đưa con trỏ về nhanh một ô (hay miền) đã được đặt tên bằng một trong hai cách sau:
Cách 1: nháy chuột tại của hộp tên trên thanh công thức và chọn tên cần thiết từ danh dách (hoặc gõ địa chỉ của ô)
Cách 2: Ấn F5. Chọn tên cần thiết từ danh sách.OK
Đây cũng là phương pháp kiểm tra xem Tên đã được đạt cho một ô hay miền nào.
e. Xoá tên
- Chọn Insert, Name, Define
- Chọn tên cần xoá từ danh sách
- Chọn delete
- Chọn OK hoặc Close
f. Chú ý
- Nếu muốn lấy tên của nhóm ô này để đặt cho nhóm ô khác, trước hết phải xoá tên đó đi
9. Ghi chú cho ô
Có thể thêm phần ghi chú cho từng ô riêng rẻ. Nhưng ghi chú này giúp ta giải thích bảng tính của mình một cách rõ ràng hơn
a. Tạo ghi chú
- Về ô cần tạo ghi chú
- Mở hộp thoại CellNote theo một trong hai cách sau:
+ Cách 1: Shift+F2
+ Cách 2: Chọn mục Insert/ Note
- Gõ vào lời ghi chú trong khung TextNote
- OK để đóng hộp thoại
Một chấm vuông màu đỏ ở góc phải trên của ô chỉ ra rằng ô đó đã được ghi chú. Nếu không thấy chấm vuông này hãy làm như sau:
- Tools/Options/View
- Đánh dấu chọn vào ô Note Indicator
b. Xem/ Sửa. Xoá các ghi chú
- Nếu cần xem ghi chú của một ô: chỉ việc rà chuột vào ô đó, một khung với nội dung ghi chú xuất hiện. Để tắt khung này: rà chuột sang ô khác
- Để sửa hay xoá một ghi chú: mở hộp thoại Note In Sheet
- Tiến hành sửa nội dung của ghi chú trong khung Text Note. Để xóa ghi chú chọn Delete
10 Bảo vệ ô
Trong một số trường hợp, để giới hạn quyền sử dụng của người khác, ví dụ: không cho họ sửa đổi một số ô này hay xem công thức ở một số ô khác vv hãy sử dụng tính năng bảo vệ và che dấu ô. Cách làm như sau:
- Chọn ô hay nhóm ô cần bảo vệ
- Chọn mục Format/Cells/Protection
- Trong hộp thoại đánh dấu chọn vào
+ Locked : để khoá không cho sửa đổi
+ Hidden: để che dấu công thức
- OK
Sau đó cần phải bảo vệ bảng tính thì những thiết lập trên mới có tác dụng (xem phần bảo vệ bảng tính)
Chương IV. HÀM TRONG EXCEL
Hàm được xem như là các công thức định sẵn nhằm thực hiện các tính toán chuyên biệt. Trên ô thực hiện hàm sẽ cho một giá trị hoặc một thông báo lỗi. Excel có trên 300 hàm và được phân loại thành từng nhóm
1. Quy tắc sử dụng hàm
- Các hàm có dạng tổng quát: TÊNHÀM (Các tham biến). Ví dụ
TODAY() : cho kết quả là ngày hiện tại trong máy (hàm không tham biến)
LEN (“Excel 5.0”) : cho kết quả độ dài của chuỗi là 9 (hàm 1 tham chiếu)
AVERAGE (A1,B5,D8): cho kết quả là trung bình cộng các số trong các ô A1, B5, D5 (hàm nhiều tham biến)
- Tên hàm có thể viết thường hay hoa hoặc vừa viết thường vừa biết hoa đều được
- Các tham biến có thể có hoặc không nhưng phải đặt trong hai dấu () và cách nhau bởi dấu phẩy, chấm phẩy hoặc một dấu ngăn cách nào khác tuỳ theo cách đặt các thông số quốc tế. Trong 1 hàm có thể chứa nhiều nhất 30 tham biến nhưng không được vượt quá 255 ký tự
- Trong hàm không được có dấu cách
- Hàm phải được bắt đầu bởi dấu = hoặc dấu của một phép tính. Trường hợp dùng một hàm để làm tham biến cho một hàn khác (hàm lồng nhau, nhiều nhất là 7 mức) không cần viết dấu = trước tên hàm đó. Ví dụ: các ô A1, B1 chứa các số đo các cạnh của tam giác vuông, khi đó công thức =SQRT (SUM(A1^2,B1^2)) gõ tại ô C1 cho số đo cạnh huyền
Ở đây, SQRT là hàm khai căn bậc 2, SUM là hàm tính tổng (bình phương của 2 cạnh góc vuông), ta thấy trước hàm này không có dấu = vì nó được dùng làm tham biến (đối số) cho hàm SQRT
2. Nhập hàm vào bảng tính
Có 3 cách nhập hàm vào bảng tính:
- Gõ vào từ bàn phím
- Dùng biểu tượng (Function Winzard)
- Dùng menu
Để nhập hàm: đưa con trỏ ô về ô cần thiết rồi chọn một trong các cách sau:
a. Gõ từ bàn phím
- Gõ dấu =
- Gõ vào tên hàm, dấu (, các tham biến theo đúng dạng thức quy định, dấu)
b. Dùng biểu tượng Function Winzard. Các bước như sau:
Bước 1
- Chọn biểu tượng trên thanh công cụ. Xuất hiện hộp thoại Function Winzard như sau
Ý nghĩa của các nhóm hàm trong khung Function Category như sau:
- Most Recently Used : các hàm sử dụng gần đây nhất
- All : Tất cả các hàm
- Financial : hàm tài chính
- Date & Time : Hàm Ngày và Giờ
- Math & Trig : Hàm toán và lượng giác
- Statistical : Hàm thống kê
- Lookup & Reference : hàm tìm kiếm và tham chiếu
- Database : hàm cơ sở dữ liệu
- Text : hàm xử lý ký tự
- Logical : hàm Logic
- Information : các hàm thông tin về ô, về bảng tính vv
Bước 2 :Chọn nhóm hàm trong khung Function Category. Khi di chuyển thanh sáng đến nhóm nào, Excel sẽ liệt kê các hàm của nhóm đó theo thứ tự chữ cái trong khung Function Name.
Bước 3: chọn nhóm hàm trong khung Function Name
Bước 4: chọn OK. Xuất hiện hộp thoại Function WinZard liệt kê công dụng, khung các tham biến cần nhập, khung chứa giá trị kết quả của hàm.
Điền các tham biến của hàm bằng cách ấn nút chuột vào khung cần thiết, sau đó nhập từ bàn phím hoặc rê chuột trên miền dữ liệu.
Bước 5: Chọn OK. Excel tắt hộp thoại và ghi kết quả của hàm vào ô
Lưu ý: khi con trỏ ô ở tại ô đang chưa hàm, nếu chọn Function Winzard Excel sẽ chuyển sang việc chỉnh sửa hàm
c. Dùng menu
- Chọn mục Insert, Function. Xuất hiện hộp thoại Function Winzard
- Các bước còn lại giống như khi chọn biểu tượng
3. Một số hàm thường dùng
3.1 Hàm ngày tháng
a. DATE (year, month, day)
Chỉ ra ngày dạn số ứng với ngày tháng năm
Ví dụ: =DATE(05, 09, 09) trả về 09-09-2005
b. DAY (date)
Số ngày trong tháng của biến ngày tháng date
Ví dụ: = DAY (09-09-2005) trả về 09
c. MONTH (date)
Số tháng trong năm của biến ngày tháng date
Ví dụ: = MONTH (09-09-2005) trả về 09
d. TIME (hour, minute, second)
Chỉ ra thời gian dạng số
Ví dụ: =TIME (19,5,14) trả về 19:05:14 hoặc 7:05 PM
e. WEEKDAY (date)
Chỉ ra số thứ tự của ngày trong tuần của biến ngày tháng date (thứ hai là ngày thứ 1, thứ Ba là ngày thứ 2,.., chủ nhật là ngày thứ 7)
f. YEAR(date): số năm của biến ngày tháng date
3.2 Hàm ký tự
a. EXACT (text1, text2)
Nhận giá trị TRUE hay FALSE phụ thuộc vào hai chuỗi Text1, Text2 có giống hệt nhay nhay không
b. FIND (find_text,text, atnum)
Vị trí của chuỗi con Find_text trong chuỗi lớn Text bắt đầu từ vị trí atmun, nếu bỏ qua atnum nó được cho bằng 1. Hàm này phân biệt chữ HOA và THƯỜNG
c. FIXED (number, decimal, no_commas)
Chuyển số number thành chuỗi dạng cố định với decimal số thập phân. Nếu không ghi decimal sẽ cho 2 số thập phân. Nếu no_commas là FALSE (hoặc không ghi), chuỗi in ra sẽ có dấu ngân cách nghìn triệu
d. LEFT (text, number)
Lấy number ký tự bên trái của text
e. LEN (text)
Độ dài của chuổi ký tự text
f. LOWER (text)
Chuyển text thành chữ thường
g. MID (text, numstart, numchar)
Trả lại numchar ký tự của text bắt đầu từ vị trí numstart
h. PROPER(text)
Chuyển các chữ cái đầu của text thành chữ viết hoa
Nếu text là tiếng việt hàm này sẽ cho kết sai
i. REPLACE (oldtext, numstart, numchar, newtext)
Thay newtext vào oldtext bắt đầu từ vị trí numstart và có độ dài numchar
j. REPT(text,number)
Lập lại text liên tiếp number lần
k. RIGHT (text, number)
Lấy number ký tự bên phải của text
l. SEARCH (find_text, text, atnum)
Tương tự hàm FIND nhưng không phân biệt chữ hoa hay thường
m. SUBSTITUTE(text, oldtext, newtext, instance)
Thay thế newtext vào vị trid oldtext trong text ở lần xuất hiện Instance (nếu không có đối số này sẽ thay thế ở mọi vị trí)
Ví dụ:
= SUBSTITUDE (“Hãy xem xem”,”xem”,”nhìn”,1) trả vể Hãy nhìn xem
=SUBSTITUDE((“Hãy xem xem”,”xem”,”đây”,2) trả về Hãy xem đây
n. TRIM (text)
Cắt bỏ các ký tự trống vô nghĩa trong text
o. UPPER (text)
Chuyển text thành chữ in hoa toàn bộ
p. VALUE (text)
Chuyển text sang số
3.3 Hàm toán học
- ABS (x) : giá trị tuyệt đối của số x
- ACOS(x) : hàm arccos của x, x nằmg trong khoảng từ -1 đến 1. Giá trị góc trả về théo radian từ 0 đến p. Nếu muốn chuyển kết quả sang độ, nhân nó với 180/PI().
- ASIN(x) : Hàm arcsin của x, x nằm trong khoảng từ -1 đến 1. Giá trị trả về theo radian từ -p/2 đến p/2.
- ATAN(x) : hàm arctangent của x. Tương tự như ASIN(x)
- COS(x) : Hàm cos của góc x, x là góc theo radian, nếu là độ phải nhân nó với PT()/180
- COUTIF (range, criteria): đếm số ô không thoả tiêu chẩn cho trước
+ Range : là tập hợp các ô mà ta muốn đếm
+ Criteria : tiêu chuẩn có thể là số, chữ hoặc biểu thức, xác định xem sẽ đếm ô nào
+ Hàm COUTIF chỉ đếm theo 1 điều kiện, ví dụ chỉ đến số Nữ hoặc chỉ đếm số tuổi từ 30 trở lê. Nếu cần đếm số nữ có độ tuổi từ 30 trở lên (kết hợp 2 điều kiện) phải dùng hàm khác
- ROUND(x,n): làm tròn số x với độ chính xác đến con số thứ n
+ Nếu n<0 thì x được làm tròn đến chữ số thập phân thứ n
+ Nếu n>0 thì x được làm tròn đến chữ số bên trái thứ n của dấu (chấm) thập phân
- SQRT(x) : hàm căn bậc hai của x
- SUM(n1,n2,…): tổng của các số n1, n2
- SUMIF(range, criteria, sum_range): cộng những ô thoả mãn điều kiện nào đó
+ Range : là vùng ô để so sánh với Criteria
+ Criteria : là điều kiện cộng, có thể là số, chữ hoặc biểu thức. Quyết định ô nào trong vùng Sum_range sẽ được cộng.
+ Sum_range : là vùng ô sẽ được cộng. Các ô trong Sum_range chỉ được cộng nếu các ô tưng ứng với nó trong Range thoả mãn Criteria
3.4 Hàm logic
- AND (Logic1, Logic2,…)
Nhận giá trị TRUE nếu tất cả các biểu thức logic1, logic2,… đều là TRUE, nhận giá trị FALSE nếu có ít nhất một đối số là FALSE
- FALSE : cho giá logic FALSE
- IF (logical_test, value_if_true, value-if_false)
Trả lại giá trị ghi trong value_if_true nếu logical_test là TRUE và giá trị ghi trong value-if_false nếu trong trường hợp ngược lại. Hàm IF có thể lồng nhau đến 7 cấp.
- NOT (logic): hàm phủ định
Ví dụ: =NOT(1+1>2) trả về TRUE
=NOT(1+1=2) trả về FALSE
- OR (logic1, logic2,…)
Nhận giá trị True nếu một trong các biểu thức logic1, logic2,… là TRUE, nhận giá trị FALSE nếu tất cả các biểu thức là FALSE
- TRUE () nhận giá trị logic TRUE
3.5 Hàm thống kê
- AVERAGE (num1, num2,…): tính trung bình cộng của số num1, num2,…
- COUNT (address): tính số các ô dữ liệu kiểu số trong miền Address
- COUNTA (Addr): tính số các ô không rỗng trong vùng Addr
Ví dụ: =COUTA (A1:E5) trả về 23 (có 2 ô rỗng là A5 và D5)
- MAX (num1,num2,…) giá trị lớn nhất của các số num1, num2,…
- MIN (num1, num2,…) giá trị nhỏ nhất của các số num1, num2,…
- RANK (số, danh sách, tuỳ chọn): xác định thứ hạng của số so với chuổi các số trong danh sách, tức là xem số đó đứng thứ mấy trong chuỗi số
+ Sắp xếp giảm dần nếu không có tuỳ chọn hoặc tuỳ chọn bằng 0
+ Sắp xếp tăng dần nếu tuỳ chọn là một số lớn hơn 0
+ địa chỉ của danh sách phải là tuyệt đối
3.6 Hàm tìm kiếm và tham chiếu
a. VLOOKUP (lookup_value, table_array, col_index_num, range_lookup).
- Lookup_value : giá trị đươc tìm kiếm trên cột bên trái của Table_array.
- Table_array : vùng tìm kiếm hay còn gọi là bảng tra cứu, địa chỉ phải là tuyệt đối, nên đặt tên cho vùng này
- Col_index_num : số thứ tự cột trong table_array, nơi VLOOKUP sẽ lấy giá trị trả về
- Range_lookup : giá trị logic xác định việc tìm kiếm là chính xác hay gần đúng, nếu là
o TRUE hay 1: Cột đầu tiên phải được sắp xếp tăng dần (khi đó có thể bỏ qua tham số thứ 4 này). Khi không thấy sẽ lấy kết quả gần đúng vì thế còn gọi là dò tìm không chính xác
o FALSE hay 0 : cột đầu tiên không sắp xếp. Tính chính xác, trả về #N/A nếu không thấy
- Ví dụ : bảng sau là kết quả thi của học sinh, dựa vào thang điểm Trung bình hãy xếp loai học sinh theo thang điểm
Để dùng hàm VLOOKUP ta thự hiện các bước sau:
o Trong miền C15:D19 gõ vào thang điểm trên dưới dạng cột. Vì đây là cách dò tìm không chính xác (trong một khoảng) nên ta chỉ gõ vào cận dưới (theo chiều tăng)của mỗi loại. Như vậy:
§ Lookup_value: là E3 điểm trung bình của học sinh thứ nhất
§ Table_array : là miền $C$1:$D$19 (miền địa chỉ tuyệt đối không đưa hàng tiêu đề C14:D4 vào) c
§ Col_index_num : là 2 vì cần lấy giá trị của cột Loại, cột này có số thứ tự là hai trong miền $C$14:D$19
o Tại ô G3 gõ vào công thức = VLOOKUP (E3, $C$14:D$19 ,2) ta nhận được Trung bình
o Copy công thức ở ô G3 xuống các ô từ G4 đến G12, excel sẽ xếp loại cho các học sinh còn lại
b. HLOOKUP (lookup_value, table_ array,row_index_num, range_lookup)
Hàm này hoạt động giống hàm VLOOKUP, điểm khác là
- Lookup-value: giá trị được tìm kiếm trên hàng dầu tiên của Table_array
- Table_array: vùng tìm kiếm viết thành hàng
- Row_index_num : số thứ tự hàng trong table_array, nơi HLOOKUP sẽ lấy giá trị về
Ví dụ: để thực hiện việc xếp loại cho học sinh ở ví dụ trên ta thực hiện như sau
o Trong miền A21:F22 ta gõ vào thang điểm ở ví dụ trên dưới dạng hàng, chú ý gõ vào cận dưới (theo chiều tăng) của mỗi loại. Như vậy:
§ Lookup-value: là E3 (điểm trung bình của học sinh thứ 1)
§ Table_array : là miền $B$21:$F$22
§ Row_index_num là 2 vì cần lấy giá trị của hàng Loại, hàng này có số thứ tự là 2 trong miền B21:F22
o Tại ô F3 điền vào công thức = HLOOKUP (E3, $B$21:$F$22,2) ta nhận được Trung bình
o Copy công thức ở ô G3 xuống các ô từ G4 đến G12, Excel sẽ xếp loại cho các học sinh còn lại
Một vài mẹo nhỏ trong excel 2007
1. Trong Excel có làm được hình ẩn đằng sau trang văn bản ( watermark ) như ở trong Word có được không ?Trong Excel chức năng Watermark là không sẵn sàng nhưng bạn cũng có thể làm như sau
1.Bấm menu View --> bấm chọn Header and Footer
2.Hộp Page Setup mở ra --> bạn bấm chọn Custom Header hoặc Custom Footer --> bạn có thể bấm trong một trong ba phần
Left section, Center section, hoặc Right section
3.Bạn bấm nút Insert Picture ( nút thứ hai từ bên phải sang ), để chèn hình ảnh bạn muốn đưa
4.Bấm Format Picture - nút cuối cùng trong dãy nút ở Footer hoặc Header --> cửa sổ Format Picture mở ra --> chọn tab Size - để thay đổi kích thước bức hình .
2. Tôi muốn khi mở chương trình EXCEL thì có sẵn font của tôi ,ví dụ : VnTime .
1.Bấm menu Tools --> chọn Options
2.Cửa sổ Options mở ra --> chọn tab General --> chọn font mà bạn muốn ở phần Standard Font --> bấm OK --> bấm OK
3.Thoát khỏi EXCEL và vào lại
3. Khi lưu file trong EXCEL bao giờ file đó cũng nằm trong thư mục My Document , tôi muốn tự động lưu trong thư mục khác thì làm như thế nào ?
1.Bấm menu Tools --> chọn Options
2.Cửa sổ Options mở ra --> chọn tab General --> bạn thay đổi ở
Default file location thư mục của bạn muốn --> bấm OK
4. Khi ở ô công thức , tôi muốn nó hiện công thức mà không hiện kết quả , đồng thời tham chiếu vào những ô liên quan để tiện theo dõi thì làm như thế nào ?
Bạn bấm tổ hợp phím CTRL + ` ( bên trái phím số 1 )
5. Trong Excel đáng lẽ là những cột A,B,C... thì lại là cột 1,2,3 ... Tôi muốn chuyển số 1,2,3 trở về thành A,B,C... như cũ
1.Bấm menu Tools --> chọn Options
2.Cửa sổ Options mở ra --> chọn tab General --> bỏ đánh dấu tích tại ô
R1C1 reference style bấm OK
6. Tôi muốn copy từ Excel sang Word nhưng vẫn muốn giữ lại tất cả kiểu dáng của nó .
1. Đánh dấu những phần mà bạn muốn copy trong Excel
2. Mở sang Word --> chọn menu Edit --> chọn Paste Special
3. Chọn Microsoft Excel Worksheet Object hoặc Formatted Text --> bấm OK
7. Tạo văn bản thẳng đứng trong Excel .
1. Chọn các vùng ( Cells ) mà bạn muốn tạo văn bản thẳng đứng
2. Bấm chuột phải vào khu vực đó --> chọn Format Cells --> chọn tab Alignment
3. Tại phần Orientation --> gõ 90 ở phần Degree
4. Tại phần Text Control --> lựa chọn đánh dấu tích tại phần Merge cells --> bấm OK.
8. Tôi muốn đánh số trang trong Excel , nhưng lại muốn nó hiển thị là Trang 1 , Trang 2 .... chứ không muốn hiển thị là Page 1 , Page 2.....
Tôi sẽ hướng dẫn bạn đặt số trang ví dụ ở góc dưới cùng như sau
1.Bấm menu File --> chọn Page Setup
2.Cửa sổ Page Setup mở ra bạn chọn tab Header/Footer --> bấm chọn Custom Footer
3.Xuất hiện cửa sổ Footer --> bấm chuột trái vào phần
Right Selections --> tại vị trí con trỏ bạn gõ Trang --> tiếp theo bấm chuột trái vào nút bên trái nút chữ A
4.Khi đó bên ô Right Selections xuất hiện như sau
Trang&[Page] --> bấm OK --> bấm OK
9. Khi tôi in trong Excel cứ bị in theo những đường kẻ ngang , kẻ dọc của các ô , tôi phải làm gì để bỏ chức năng này ?
1.Trong Excel bạn chọn menu File --> chọn Page Setup
2.cửa sổ Page Setup mở ra --> chọn tab Sheet --> bỏ ô đánh dấu của Gridlines --> bấm OK
10. Tôi muốn đặt ngầm định của Excel khi sử dụng ngày tháng năm là : 02 con số của ngày , 02 con số của tháng và 04 con số của năm .
1.Bạn đóng Excel đang làm việc lại
2.Bấm nút Start --> chọn Settings --> chọn ControlPanel
3.Cửa sổ ControlPanel mở ra bạn bấm đúp chuột trái của biểu tượng
Regional and Language Opitons
hiện tại xuất hiện tab RegionalOptions --> bấm nút Customize
4.Cửa sổ Customize RegionalOptions mở ra --> chọn tab Date --> bạn sẽ nhìn thấy phần
Short date format --> bạn thay đổi nội dung trong đó như sau
dd/mm/yyyy
5.Bấm Apply --> bấm OK
11. Tôi có một hàng ở trong Excel làm thế nào mà tôi chuyển nó sang dạng cột được bây giờ ?
1.Bạn bôi đen đánh dấu hàng mà bạn muốn chuyển .
2.Đưa con trỏ vào vị trí muốn chuyển .
3.Bấm chuột phải vào vị trí đó --> chọn Paste Special .
4.Đánh dấu tích vào phần Transpose --> bấm OK
12. Tôi sử dụng Excel , khi tôi đưa mũi tên lên , xuống hoặc sang trái , hoặc sang phải thì chẳng thấy con trỏ đâu mà chỉ thấy màn hình của tôi bị trôi lên , xuống , hoặc sang trái , hoặc sang phải .Tôi phải xử lí như thế nào ?
Rất đơn giản thôi bạn tắt phím Scroll Lock đi bằng cách bấm phím Scroll Lock một lần để đèn ScrollLock ( góc bên phải ở trên cùng của bàn phím ) tắt đi là được
13. Tôi có 2 văn bản: * Văn bản 1 tôi để trang giấy xoay ngang * Văn bản 2 tôi để trang giấy xoay dọc Bây giờ tôi muốn dồn 2 trang văn bản này làm một văn bản thôi. Tôi phải làm thế nào mà không bị mất định dang xoay ngang, xoay dọc đó?
Trước tiên bạn mở cả 2 văn bản, sau đó bạn làm theo các bước sau:
1. Bạn bôi đen văn bản 2 để copy tất cả sang văn bản 1
2. Bạn quay trở về văn bản 1 di chuyển con trỏ đến đầu trang dữ liệu thuộc văn bản 2
3. Chọn menu Insert --> Break..
4. Trong cửa sổ Break:
* Bạn chọn Section break types
* Đánh dấu chọn Next page OK
5. Sau khi đã ngắt được 2 phần văn bản, bạn để con trỏ ở đầu trang dữ liệu thuộc văn bản 2
* Chọn vào File
* Chọn Page Setup... Chọn Margins Chọn Orientation để đặt lại giấy xoay ngang hay xoay dọc OK
14. Tôi muốn đánh công thức hóa học ví dụ như H2O thì tôi phải làm như thế nào ?
1-Đầu tiên bạn cứ gõ H2O như bình thường.
2-Bạn bôi đen số 2 --> bấm chuột phải vào số 2 vừa bôi đen đó.
3-Chọn FormatCells
4-Đánh dấu vào ô Subscript
5-OK hoàn tất công việc
15. Khi tôi mở một file mới trong Excel ra thì luôn luôn đi kèm theo Book1 trắng mở ra , đông thời tôi cũng đã thay đổi khi mở Excel thì có 16 sheet nhưng vẫn không được và vẫn chỉ là 3 sheet . Cách giải quyết như thế nào ?
Bạn phải làm những bước như sau:
1-Đóng Excel lại để ra màn hình nền desktop
2-Bấm Start --> Search --> For Files or Folders
3-Chọn All files and folders
4-Gõ trong ô tìm kiếm như sau : xlstart
5-Bấm Search
6-Xuất hiện những Folder là XLSTART bên tay phải của ô cửa sổ
7-Xoá hết những file trong XLSTART
8-Khởi động lại Excel
16. Tôi dùng Excel khi tôi thực hiện việc kéo hand-fill để sử dụng đúng công thức của dòng trên nhưng nó chỉ ra kết quả của dòng trên mà không đưa công thức xuống --> tôi phải sửa như thế nào ?
Bạn chọn Menu Tools
+ Chọn Options --> chọn tab Calculation
+ Trong Calcutation hiện tại đang đánh dấu ở ô Manual , bạn chuyển sang đánh dấu ở Automatic OK
17. Tôi muốn phân cách cứ 3 số bằng dấu chấm (.) vì hiện nay nó là dấu phảy (,) , tôi phải làm như thế nào ?
Trước tiên bạn phsir đống Excel lại , sau đó làm những bước như sau :
1-Bấm Start --> chọn Setting --> chọn ControlPanel
2-Bấm đúp chuột trái và Regional and Language Options --> chọn tab Regional Options .
3-Bấm chuột trái chọn Customize --> chọn tab Number .
4-Trong tab Number ta cần phải đổi như sau :
+ Decimal symbol từ dấu (.) chuyển sang dấu (,)
+ Digit grouping symbol tù dấu (,) chuyển sang dấu (.)
+ List Separator tù dấu (,) chuyển sang dấu (.)
5.Bấm Apply --> bấm OK
18. Trong Excel khi tôi đánh số 0904294334 trong một ô và khi kết thúc cứ mất số 0 ở đầu dòng . Tôi không muốn như vậy mà vẫn cứ muốn giữu số 0 đó thì làm thế nào ?
Ở vị trí ô mà bạn muốn đánh thì trước hết bạn gõ dấu nháy đơn đi kèm với dấu nháy kép trên bàn phím , sau đó bạn gõ 0904294334 , khi bạn ấn Enter xuống dòng thì dấu nháy đơn đó sẽ bị mất đi và số 0904294334 của bạn sẽ được giữ nguyên mà không mất số 0 ở đầu .
19. Làm thế nào để tăng Sheet trong Excel vì khi tôi mở Excel ra thì chỉ có 3 Sheet ?
Bạn mở Excel, làm theo các bước sau:
* Chọn vào Tools
* Chọn Options
* Trong đó chọn vào Tab General
* Bạn sẽ nhìn thấy có mục Sheets in new workbook, hiện tại đang để là 3
* Bạn muốn tăng lên bao nhiêu thì chọn vào mũi tên trỏ lên.--> OK
Để cho lựa chọn vừa rồi được kích hoạt bạn phải khởi động lại Excel.
20. Tôi muốn sắp xếp bằng tiếng Việt trong Excel thì làm thế nào ? Tôi gõ họ và tên người nhưng khi sắp xếp thì bị lỗi . Giúp tôi với !!!
Trong Excel không hỗ trợ cho việc sắp xếp theo bảng chữ cái của tiếng Việt .
Bạn muốn sắp xếp được thì phải cài thêm chương trình hỗ trợ như : VietKeyOFFICE
Khi cài VietKeyOFFICE trong thanh menu của Excel sẽ xuất hiện một nút của VietKey và bạn sẽ sắp xếp trong chức năng đó
21. Tôi làm việc ở khách sạn và quuản lí khách bằng Excel , làm thế nào tôi biết được số ngày mà khách ở lại nếu như biết ngày đến và ngày đi ? Xin cám ơn nhiều .
Bạn có thể làm như sau :
Ví dụ : ô của ngày về là 30/10/2005 , ô của ngày đến là 04/07/2005
1-Tại ô kết quả bạn vẫn lấy ô của ngày về - ô của ngày đến
2-Bạn sẽ thấy ô kết quả ra một giá trị 27/04/1900 , nhưng bạn không phải lo gì cả . Bấm chuột phải vào ô kết quả , bạn chọn Format Cells.
3-Trong đó bạn sẽ thấy có một tab là Number và trong đó bạn lựa chọn dòng Number của phần Category
4-Bấm OK , bạn sẽ thấy số 118 - đúng với kết quả mà mình mong muốn .
22. Em có file kế toán bằng Excel mà lại quên mất PASSWORD , chỉ cho em cách mở file đó . Em xin chân thành cám ơn .
Khi bạn quên mất Password của file dạng Excel hoặc dạng Word thì bạn phải cài thêm chương trình ứng dụng để dò tìm password như PasswareKit , AđvancedOffice PasswordRecovery .... Bản thân trong bộ OFFICE của Microsoft không có chức năng như vậy.
23. Tôi mở một file của Excel ra thì cứ bị mở theo một vài trang khác mở theo , rất khó chịu , tôi phải làm gì để bỏ chúng đi ?
Bạn phải làm những bước như sau:
1-Đóng Excel lại để ra màn hình nền desktop
2-Bấm Start --> Search --> For Files or Folders
3-Chọn All files and folders
4-Gõ trong ô tìm kiếm như sau : xlstart
5-Bấm Search
6-Xuất hiện những Folder là XLSTART bên tay phải của ô cửa sổ
7-Xoá hết những file trong XLSTART
8-Khởi động lại Excel
24. Tôi để ẩn mất cột A trong file của tôi thì làm thế nào cho cột A nó hiện ra ?
Khi bạn ẩn mất cột A thì muốn lấy lại bạn phải làm như sau :
1-Đưa con chuột vào cột B ỏ trên cùng bên tay trái đến khi xuất hiện mũi tên hai chiều .
2-Giữ chuột trái , kéo sang bên phải thì cột A của bạn sẽ xuất hiện .
25. Trong Excel khi tôi đánh công thức toán học thì nó không ra kết quả của phép tính mà nó chi ghi lại đúng công thức tôi vừa đánh là tại sao ?
1-Bạn chọn Menu Tools --> chọn Options.
2-Chọn tab View , trong tab đó có phần Windows Options
3-Bỏ đánh dấu mục Formulas
4-Bấm OK
25. Tôi đang soạn một dòng trong Excel và muốn xuống dòng thì tôi phải bấm phím nào ?
Khi soạn thảo trong một ô (cell) của Excel khi muốn xuống dòng thì tại vị trí đó bạn ấn tổ hợp phím Alt + Enter
26. Làm thế nào đặt được Password cho file excel nhỉ ?
1-Mở file excel mà bạn muốn đặt password ra màn hình .
2-Bấm menu File ,chọn Save As
3-Bấm menu Tools trong mục Save As , chọn General Options
4-Xuất hiện ô Password to Open và password to Modify phải phải gõ tất cả 04 lần .
5-Bấm OK Save
27. Chuột tôi bị hỏng , tôi muốn chuyển sang các Sheeet bằng bàn phím như thế nào
Bạn bấm Ctrl - PageUp để đổi sang trang trước
Ctrl - PageDown để đổi sang trang sau
28. Tôi muốn đánh đơn vị của diện tích thì phải làm thế nào ? Như m2 chẳng hạn .
1-Đầu tiên bạn cứ gõ m2 như bình thường.
2-Bạn bôi đen số 2 --> bấm chuột phải vào số 2 vừa bôi đen đó.
3-Chọn FormatCells
4-Đánh dấu vào ô Supperscript OK
29. Trong Excel không có sẵn tính năng đối chỗ hai hàng mà người dùng phải sử dụng Macro , cách thức tiến hành như sau
• Mở Excel
• Bấm tổ hợp phím Alt-F11 , xuất hiện cửa sổ MS Visual Basic
• Bấm chọn menu Insert , chọn Module và bạn Copy nội dung toàn bộ dưới đây
Sub swapRows()
Dim xlong As Long
If Selection.Areas.Count <> 2 Then
MsgBox "Phai co chinh xac hai vung the doi cho." & Chr(10) _
& "Ban co " & Selection.Areas.Count & " vung."
Exit Sub
End If
If Selection.Areas(1).Columns.Count <> Cells.Columns.Count Or _
Selection.Areas(2).Columns.Count <> Cells.Columns.Count Then
MsgBox "Must select entire Rows, insufficient columns"
Exit Sub
End If
Dim areaSwap1 As Range, areaSwap2 As Range, onepast2 As Range
If Selection.Areas(1)(1).Row > Selection.Areas(2)(1).Row Then
Range(Selection.Areas(2).Address & "," & Selection.Areas(1).Address).Select
Selection.Areas(2).Activate
End If
Set areaSwap1 = Selection.Areas(1)
Set areaSwap2 = Selection.Areas(2)
Set onepast2 = areaSwap2.Offset(areaSwap2.Rows.Count, 0).EntireRow
areaSwap2.Cut
areaSwap1.Resize(1).EntireRow.Insert Shift:=xlShiftDown
areaSwap1.Cut
onepast2.Resize(1).EntireRow.Insert Shift:=xlShiftDown
Range(areaSwap1.Address & "," & areaSwap2.Address).Select
xlong = ActiveSheet.UsedRange.Columns.Count 'correct lastcell
End Sub
• Quay trở lại Excel và đánh dấu chọn 02 hàng dữ liệu cần chuyển . Chú ý phải đánh dấu tất cả hàng
• Chạy Macro có tên là swapRows
Làm thế nào từ ngày tháng năm có thể xác định được thứ trong tuần không ?
Từ ngày tháng năm trong Excel để xác định thứ trong tuần bạn sử dụng hàm WEEKDAY và ngày trong tuần được hiển thị bằng con số . Nếu bạn muốn xác định theo kiểu từ Thứ hai cho tới Chủ nhật thì bạn làm như sau
Ô A1 là ngày . Ví dụ 04/09/2008
Ô B1 sẽ xác định theo công thức như sau
=CHOOSE(WEEKDAY(A1),"Chủ nhật","Thứ hai","Thứ ba","Thứ tư","Thứ năm","Thứ sáu","Thứ bảy")
30. Làm thế nào từ ngày tháng năm có thể xác định được thứ trong tuần không ?
Từ ngày tháng năm trong Excel để xác định thứ trong tuần bạn sử dụng hàm WEEKDAY và ngày trong tuần được hiển thị bằng con số . Nếu bạn muốn xác định theo kiểu từ Thứ hai cho tới Chủ nhật thì bạn làm như sau
Ô A1 là ngày . Ví dụ 04/09/2008
Ô B1 sẽ xác định theo công thức như sau
=CHOOSE(WEEKDAY(A1),"Chủ nhật","Thứ hai","Thứ ba","Thứ tư","Thứ năm","Thứ sáu","Thứ bảy")
31. Dấu công thức nhưng vẫn hiển thị kết quả của trong Excel
Dấu công thức nhưng vẫn hiển thị kết quả của trong Excel bạn làm như sau :
1. Bấm menu Format , chọn Cell
2. Chọn tab Protection , đánh dấu tích để chọn phần Locked và Hidden , bấm OK
3. Bấm menu Tools , chọn Protection , chọn Protect Sheet
Bạn đặt Password nếu như không muốn cho mọi người thay đổi
32. Tạo hàm tính năm , ngay , tháng giữa hai thời gian cho trước
Tạo hàm tính năm , ngay , tháng giữa hai thời gian cho trước cần làm như sau :
1. Mở Excel , bâm tổ hợp phím Alt-F11
2. Cửa sổ Microsoft Visual Basic mở ra , chọn menu Insert , chọn Module
3. Bạn cắt dán theo nội dung dưới đây
Function Age(Date1 As Date, Date2 As Date) As String
Dim Y As Integer
Dim M As Integer
Dim D As Integer
Dim Temp1 As Date
Temp1 = DateSerial(Year(Date2), Month(Date1), Day(Date1))
Y = Year(Date2) - Year(Date1) + (Temp1 > Date2)
M = Month(Date2) - Month(Date1) - (12 * (Temp1 > Date2))
D = Day(Date2) - Day(Date1)
If D < 0 Then
M = M - 1
D = Day(DateSerial(Year(Date2), Month(Date2), 0)) + D
End If
Age = Y & " nam " & M & " thang " & D & " ngay"
End Function
4. Thoát khỏi Microsoft Visual Basic , quay trở về Excel
33.Khi tôi mở một file Excel thì file đó mở ra nhưng lại thông báo lỗi là file có tên như thế đã được mở và không thể mở 02 file có cùng một tên như vậy.
Khi mở một file Excel thì file đó mở ra nhưng lại thông báo lỗi là file có tên như thế đã được mở và không thể mở 02 file có cùng một tên như vậy thì bạn cần làm như sau :
1. Đóng Excel
2. Bấm Start --> chọn Run --> gõ :
"C:\Program Files\Microsoft Office\OfficeXY\Excel.Exe" /regserver --> bấm OK
OfficeXY là tuỳ theo bộ Office mà bạn cài vào máy tính
3. Khởi động lại máy tính
34.Những File Excel của tôi khi mở ra lại báo Read-only , tôi muốn khôi phục trạng thái cũ .
Những file Excel trong một thư mục nào đó lại đánh dấu Read-only , bạn làm hnư sau :
1. Đóng Excel
2. Dùng MyComputer hoặc Windows Explorer tìm theo thư mục
C:\Windows\temp ==> xoá những file trong đó
3. Khởi động lại máy tính
35. Tự động cân chỉnh cho vừa <8/31/2006 4:46:51 AM>
Trong Excel khi chúng ta gõ nội dung của ô hoặc cột nhiều khi sẽ vượt qua kích thước ngầm định , để tự động điều chỉng chúng ta làm như sau :
1. Hình dưới đây là kiểu vượt khỏi kích ô
2. Bạn đưa chuột vào viền bên phải của cột và nhấp đúp chuột trái
36. chào anh, chị cho em hỏi 1. Em có một một file excel gửi cho người khác, em muốn người ta đọc ngưng không sữa cột dữ liệu được. ví dụ thêm cột mới hoặc xoá cột số liệu đang có. thì phải làm thế nào.
Để không cho người nào chỉnh sửa file , bạn chỉ cần đặt Password vàchỉ cho phép xem mà không sửa , quá trình thực hiện như sau :
1. Mở file trong Excel mà bạn muốn.
2. Bấm menu File --> chọn Save as
3. Trong cửa sổ Save As --> chọn Tools --> chọn General Options
4. Bạn đặt password cho mục Password to modify
Bấm dấu tích để chọn Read-only recommended --> bấm OK
5. Bạn gõ tiếp đúng password trước vào mục Reenter password to modify --> bấm OK --> bấm Save
6. Muốn sửa file Excel bạn dùng chương trình Excel Recover
37.Tức là trong Excel mặc định khi mình muốn đánh số trang thì số trang lại bắt đầu từ 1 trở đi và bây giờ mình muốn đánh số trang bắt đầu từ 15 hay bất kỳ một số nào mình muốn thì phải làm thế nào?
Để đánh số trang lại , ví dụ từ trang 15 trở đi ban làm như sau :
1. Bấm menu File --> chọn Page Setup
2. Chọn tab Page --> phần Firt page number , ban thay Auto bằng số 15 --> bấm OK
38.Tôi muốn các văn bản đã làm tự động save trong excel thì phải làm như thế nào?
Để tự động Save (AutoSave) trong văn bản Excel , ban cần làm như sau :
1. Mở file Excel mà bạn muốn .
2. Bấm menu File --> chọn Save As
3. Bấm Tools --> chọn General Options
4. Đánh dấu tích để chọn phần Always create backup --> bấm OK --> bấm Save
39. Trong Excel mình làm một bảng dài 5 trang. Mình phải làm sao để khi in ra giấy (mà không làm xuất hiện trong bảng), dòng đầu tiên trong bảng ở trang 1 cũng có ở trong 4 trang sau để cho dễ biết các chi tiết trong bảng.
Để in một số dòng ( hoặc một số cột ) cố dịnh vào các trang sau thì bạn làm như sau :
1. Mở file Excel mà bạn định in .
2. bấm menu File --> chọn Page Setup --> chọn tab Sheet
3. Bạn muốn in cố định dòng nào đó trong tất cả các trang thì chọn Rows to repeat at top
Bạn muốn in cố định cột nào đó thì bạn chọn Columns to repeat at left
4. Bạn đưa chuột vào những hàng hoặc cột mà muốn cố định khi in , như hình dưới đây
5. Bấm OK để hoàn tất
40. Tôi muốn đổi tất cả thành chữ hoa ?
Để đổi tất cả thành chữ hoa bạn cắt dán theo nội dung dưới đây
Sub MakeUpper()
Dim MyText As String
Dim MyRange As Range
Dim CellCount As Integer
Set MyRange = ActiveSheet.Range(ActiveWindow.Selection.Address)
For CellCount = 1 To MyRange.Cells.Count
If Not MyRange.Cells(CellCount).HasFormula Then
MyText = MyRange.Cells(CellCount).Value
MyRange.Cells(CellCount).Value = UCase(MyText)
End If
Next CellCount
End Sub
Giới thiệu công thức và hàm trong excel 2010
Giới thiệu công thức và hàm:
Giới thiệu công thức (Formula)
Công thức giúp bảng tính hữu ích hơn rất nhiều, nếu không có các công thức thì bảng tính cũng giống như trình soạn thảo văn bản. Chúng ta dùng công thức để tính toán từ các dữ liệu lưu trữ trên bảng tính, khi dữ liệu thay đổi các công thức này sẽ tự động cập nhật các thay đổi và tính ra kết quả mới giúp chúng ta đỡ tốn công sức tính lại nhiều lần. Vậy công thức có các thành phần gì?
Công thức trong Excel được nhận dạng là do nó bắt đầu là dấu = và sau đó là sự kết hợp của các toán tử, các trị số, các địa chỉ tham chiếu và các hàm.
Ví dụ:
Các toán tử trong công thức
|
Toán tử |
Chức năng |
Ví dụ |
Kết quả |
|
+ |
Cộng |
=3+3 |
3 cộng 3 là 6 |
|
- |
Trừ |
=45-4 |
45 trừ 4 còn 41 |
|
* |
Nhân |
=150*.05 |
150 nhân 0.50 thành 7.5 |
|
/ |
Chia |
=3/3 |
3 chia 3 là 1 |
|
^ |
Lũy thừa |
=2^4 =16^(1/4) |
2 lũy thừa 4 thành 16 Lấy căn bậc 4 của 16 thành 2 |
|
& |
Nối chuỗi |
=”Lê” & “Thanh” |
Nối chuỗi “Lê” và “Thanh” lại thành “Lê Thanh” |
|
= |
Bằng |
=A1=B1 |
Ví dụ ô A1=3, ô B1=6 Kết quả: FALSE |
|
> |
Lớn hơn |
=A1>B1 |
Ví dụ ô A1=3, ô B1=6 Kết quả: FALSE |
|
< |
Nhỏ hơn |
=A1 |
Ví dụ ô A1=3, ô B1=6 Kết quả: TRUE |
|
>= |
Lớn hơn hoặc bằng |
=A1>=B1 |
Ví dụ ô A1=3, ô B1=6 Kết quả: FALSE |
|
<= |
Nhỏ hơn hoặc bằng |
=A1<=B1 |
Ví dụ ô A1=3, ô B1=6 Kết quả: TRUE |
|
<> |
Khác |
=A1<>B1 |
Ví dụ ô A1=3, ô B1=6 Kết quả: TRUE |
|
, |
Dấu cách các tham chiếu |
=Sum(A1,B1) |
Ví dụ ô A1=3, ô B1=6 Kết quả: 9 |
|
: |
Tham chiếu mãng |
=Sum(A1:B1) |
Ví dụ ô A1=3, ô B1=6 Kết quả: 9 |
|
khoảng trắng |
Trả về các ô giao giữa 2 vùng |
=B1:B6 A3:D3 |
Trả về giá trị của ô |
Thứ tự ưu tiên của các toán tử
|
Toán tử |
Mô tả |
Ưu tiên |
|
: (hai chấm) (1 khoảng trắng) , (dấu phẩy) |
Toán tử tham chiếu |
1 |
|
– |
Số âm (ví dụ –1) |
2 |
|
% |
Phần trăm |
3 |
|
^ |
Lũy thừa |
4 |
|
* và / |
Nhân và chia |
5 |
|
+ và – |
Cộng và trừ |
6 |
|
& |
Nối chuỗi |
7 |
|
= < > <= >= <> |
So sánh |
8 |
Giới thiệu hàm (Function)
Hàm trong Excel được lập trình sẵn dùng tính toán hoặc thực hiện một chức năng nào đó. Việc sử dụng thành thạo các hàm sẽ giúp chúng ta tiết kiệm được rất nhiều thời gian so với tính toán thủ công không dùng hàm. Các hàm trong Excel rất đa dạng bao trùm nhiều lĩnh vực, có những hàm không yêu cầu đối số, có những hàm yêu cầu một hoặc nhiều đối số, và các đối số có thể là bắt buộc hoặc tự chọn.
Ví dụ:
=Rand(): hàm không có đối số
=If(A1>=5,”Đạt”,”Rớt”): hàm 3 đối số
=PMT(10%,4,1000,,1): hàm nhiều đối số và đối số tùy chọn
Trong Excel 2010 có các hàm chính như sau:
•Hàm ngoại: Call, Registed.ID,…
•Hàm lấy dữ liệu từ SSAS: Cubeset, Cubevalue,…
•Hàm dữ liệu: Dmin, Dmax, Dcount,…
•Hàm ngày và thời gian: Time, Now, Date,….
•Hàm kỹ thuật: Dec2Bin, Dec2Hex, Dec2Oct,…
•Hàm tài chính: Npv, Pv, Fv, Rate,…
•Hàm thông tin: Cell, Thông tin, IsNa,…
•Hàm luận lý: If, And, Or,…
•Hàm tham chiếu và tìm kiếm: Choose, Vlookup, OffSet,…
•Hàm toán và lượng giác: Log, Mmult, Round,…
•Hàm thống kê: Stdev, Var, CountIf,…
•Hàm văn bản: Asc, Find, Text,…
Chúng ta sẽ cùng nghiên cứu cách sử dụng từng hàm trong các nhóm hàm trên ở các phần sau.
Nhập công thức và hàm
Nhập công thức trong Excel rất đơn giản, muốn nhập công thức vào ô nào bạn chỉ việc nhập dấu = và sau đó là sự kết hợp của các toán tử, các trị số, các địa chỉ tham chiếu và các hàm. Bạn có thể nhìn vào thanh Formula để thấy được trọn công thức. Một điều hết sức lưu ý khi làm việc trên bảng tính là tránh nhập trực tiếp các con số, giá trị vào công thức mà bạn nên dùng đến tham chiếu.
Ví dụ:
Minh họa dùng tham chiếu trong hàm
Trong ví dụ trên, ở đối số thứ nhất của hàm NPV chúng ta không nhập trực suất chiết tính 10% vào hàm mà nên tham chiếu đến địa chỉ ô chứa nó là I2, vì nếu lãi suất có thay đổi thì ta chỉ cần nhập giá trị mới vào ô I2 thì chúng ta sẽ thu được kết quả NPV mới ngay không cần phải chỉnh sửa lại công thức.
Giả sử các ô C2:G2 được đặt tên là DongTien, và ô I2 đặt tên là LaiSuat (Xem lại cách đặt tên vùng ở bài số 1) thì trong quá trình nhập công thức bạn có thể làm như sau:
B1. Tại ô B4 nhập vào =NPV(
B2. Nhấn F3, cửa sổ Paste Name hiện ra
B3. Chọn LaiSuat và nhấn OK
B4. Nhập dấu phẩy (,) và gõ F3
B5. Chọn DongTien và nhấn OK
B6. Nhập dấu đóng ngoặc rồi nhập dấu +
B7. Nhấp chuột vào ô B2
B8. Nhấn phím Enter
Chèn tên vùng vào công thức
Một trong những cách dễ dàng nhất để sử dụng hàm trong Excel là sử dụng thư viện hàm. Khi bạn muốn sử dụng hàm nào chỉ việc vào thanh Ribbon -> chọn nhóm Formulas -> Function Library -> chọn nhóm hàm -> chọn hàm cần sử dụng. Ngoài ra bạn có thể nhấn vào nút để gọi hộp thoại Insert Function một cách nhanh chóng và khi cần tìm hiểu về hàm này bạn chỉ cần nhấn vào Help on this function.
Hộp thoại Insert Function
Tham chiếu trong công thức
Các tham chiếu sử dụng trong công thức giúp cho chúng ta khỏi tốn công sửa chữa các công thức khi các giá trị tính toán có sự thay đổi. Có 3 loại tham chiếu sau:
•Tham chiếu địa chỉ tương đối: Các dòng và cột tham chiếu sẽ thay đổi khi chúng ta sao chép hoặc di dời công thức đến vị trí khác một lượng tương ứng với số dòng và số cột mà ta di dời. Ví dụ A5:B7, C4
•Tham chiếu địa chỉ tuyệt đối: Các dòng và cột tham chiếu không thay đổi khi ta di dời hay sao chép công thức. Ví dụ $A$5:$B$7, $C$4
•Tham chiếu hỗn hợp: Phối hợp tham chiếu địa chỉ tương đối và tuyệt đối. Ví dụ A$5 nghĩa là cột A tương đối và dòng 5 tuyệt đối.
Lưu ý: Dấu $ trước thứ tự cột là cố định cột và trước thứ tự dòng là cố định dòng. Nhấn phím F4 nhiều lần để (tuyệt đối) cố định/ bỏ cố định dòng hoặc cột.
Nhấn phím F4 nhiều lần để (tuyệt đối) cố định/ bỏ cố định dòng hoặc cột.
Ví dụ: Tính thành tiền bằng Số lượng nhân Giá. Đổi sang giá trị Thành tiền sang VND. Tính tổng các cột Thành tiền và cột VND.
Minh họa địa chỉ tương đối và tuyệt đối
B1. Tại ô D2 nhập vào =B2*C2 và Enter. Sau đó quét chọn cả vùng D2:D14 và gõ . Vào các ô D3, D4... D14 ta thấy công thức các dòng tự động được thay đổi tương ứng với khoảng cách so với ô D2. Trường hợp này chúng ta dùng địa chỉ tương đối của B2*C2 là vì chúng ta muốn khi sao chép công thức xuống phía dưới thì địa chỉ các ô tính toán sẽ tự động thay đổi theo.
B2. Tại ô E2 nhập vào =D2*B$17 và Enter, sau đó chép công thức xuống các ô E3:E14. Chúng ta cần cố định dòng 17 trong địa chỉ tỷ giá B17 vì ta muốn khi sao công thức xuống thì các công thức sao chép vẫn tham chiếu đến ô B17 để tính toán.
B3. Tại ô D15 nhập vào =Sum(D2:D14) và chép sang ô E15.
Lưu ý:
•Tham chiếu đến địa chỉ ở worksheet khác nhưng cùng workbook thì có dạng
•Tên_sheet!Địa_chỉ_ô. Ví dụ:
=A2*Sheet2!A2
=A2*’Thong so’!B4
Khi tên sheet có chứa khoảng trắng thì để trong cặp nháy đơn ‘ ’
•Tham chiếu đến địa chỉ trong workbook khác thì có dạng
[Tên_Workbook]Tên_sheet!Địa_chỉ_ô.
Ví dụ:
=A2*[Bai2.xlsx]Sheet3!A4
=A2*’[Bai tap 2.xlsx]Sheet3’!A4
Khi tên Sheet hay Workbook có chứa khoản trắng để trong cặp nháy đơn ‘ ’
=A2*’C:\Tai lieu\[Bai tap 2.xlsx]Sheet3’!A4
Khi tham chiếu đến workbook khác mà workbook này không mở
=A2*’\\DataServer\Excel\[Bai tap 2.xlsx]Sheet3’!A4
Khi tham chiếu đến tài nguyên chia sẽ trên máy chủ trong mạng
Các lỗi thông dụng (Formulas errors)
Các lỗi thông dụng
|
Lỗi |
Giải thích |
|
#DIV/0! |
Trong công thức có chứa phép chia cho 0 (zero) hoặc chia ô rỗng |
|
#NAME? |
Do dánh sai tên hàm hay tham chiếu hoặc đánh thiếu dấu nháy |
|
#N/A |
Công thức tham chiếu đến ô mà có dùng hàm NA để kiểm tra sự tồn tại của dữ liệu hoặc hàm không có kết quả |
|
#NULL! |
Hàm sử dụng dữ liệu giao nhau của 2 vùng mà 2 vùng này không có phần chung nên phần giao rỗng |
|
#NUM! |
Vấn đề đối với giá trị, ví dụ như dùng nhầm số âm trong khi đúng phải là số dương |
|
#REF! |
Tham chiếu bị lỗi, thường là do ô tham chiếu trong hàm bị xóa |
|
#VALUE! |
Công thức tính toán có chứa kiểu dữ liệu không đúng. |
Cách chọn in 2 mặt trong Excel nhanh chóng và đơn giản nhất
Cách sắp xếp dữ liệu trong excel chuyên nghiệp nhất
Cách trình bày văn bản trong Excel chuẩn nhất
Cách khắc phục File Excel bị lỗi bằng các bước đơn giản
Hướng dẫn làm giảm dung lượng file Excel
Công thức tính ngày excel nhanh nhất
(St)
(St)
