Giáo trình giảng dạy EXCEL
Chia sẻ bởi Nguyễn Trường Vĩnh |
Ngày 16/10/2018 |
71
Chia sẻ tài liệu: Giáo trình giảng dạy EXCEL thuộc Tư liệu tham khảo
Nội dung tài liệu:
CÁC THAO TÁC VỚI BẢNG TÍNH
1. Lưu bảng tính trên đĩa
2. Mở bảng tính đã có trên đĩa
3. Đóng bảng tính
4. Thêm một bảng tính
5. Xoá bảng tính
6. Đổi tên bảng tính
7. Sao chép/Di chuyển một bảng tính
CÁC THAO TÁC VỚI BẢNG TÍNH
1. Lưu bảng tính trên đĩa
Cách 1: Click nút Save trên thanh Standard Tool
Cách 2: Vào menu File, chọn Save, hoặc nhấn tổ hợp phím Ctrl+S)
CÁC THAO TÁC VỚI BẢNG TÍNH
2. Mở bảng tính đã có trên đĩa
Cách 1: Click nút Open trên thanh Standard Tool
Cách 2: Vào menu File chọn Open , hoặc nhấn tổ hợp phím Ctrl+O)
CÁC THAO TÁC VỚI BẢNG TÍNH
3. Đóng bảng tính
Cách 1: Click nút Close
Cách 2: Vào menu File chọn Close
CÁC THAO TÁC VỚI BẢNG TÍNH
4. Thêm một bảng tính
Cách 1: Vào menu Insert chọn Worksheet.
Cách 2: Click chuột phải trên thanh thẻ tên bảng tính và chọn Insert… Hộp thoại Insert hiện ra, trên tab General ta chọn mục Worksheet và nhấn OK.
CÁC THAO TÁC VỚI BẢNG TÍNH
5. Xóa một bảng tính
Cách 1: Vào menu Edit chọn Delete sheet.
Cách 2: Click chuột phải trên thanh thẻ tên bảng tính và chọn Delete
CÁC THAO TÁC VỚI BẢNG TÍNH
6. Đổi tên một bảng tính
Cách 1: Vào menu Format chọn Sheet/Rename.
Cách 2: Click đúp chuột phải vào thẻ tên bảng tính và nhập tên mới.
CÁC THAO TÁC VỚI BẢNG TÍNH
7. Sao chép/Di chuyển một bảng tính
Cách 1:
Di chuyển: rê chuột kéo thả thẻ tên (Sheet cần di chuyển) đến vị trí mới (Sheet khác).
Sao chép: Thực hiện tương tự như Di chuyển nhưng sử dụng thêm phím Ctrl
Cách 2: Vào menu Edit chọn Move or Copy Sheet… sau đó chọn Workbook cần di chuyển
tới khung To book. Vị trí đặt bảng tính hiện tại trước bảng tính trong khung Before sheet.
Nếu thực hiện sao chép thì đánh dấu chọn vào Create a copy.
CÁC THAO TÁC VỚI BẢNG TÍNH
8. Tách 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, trên menu Window chọn mục Split. Để bỏ tách thì trên menu Window chọn Remove Split.
Cách 3: Đưa con trỏ ô về vị trí cần tách, trên menu Window chọn mục Freeze Panes. Để bỏ tách thì trên menu Window chọn Unfreeze Panes.
CÁC THAO TÁC VỚI BẢNG TÍNH
9. Ẩn và hiện lại bảng tính
Trên menu Format chọn Sheet/Hide để ẩn bảng tính.
Để hiện lại bảng tính, trên menu Format chọn Sheet/Unhide… và chọn sheet cần hiển thị trong hộp thoại Unhide, sau đó nhấn Enter.
CÁC THAO TÁC VỚI BẢNG TÍNH
10. Bảo vệ bảng tính
Cách làm như sau:
Trên menu Tools chọn Protection.
Chọn Protect Sheet… để bảo vệ bảng tính.
Chọn Protect Workbook… để bảo vệ tập các bảng tính.
Nếu cần thiết có thể gõ mật khẩu vào khung Password, hai 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ệ, trên menu Tools chọn Protection/Unprotect Sheet… hay Unprotect Workbook…. Nếu có mật khẩu, phải gõ vào, nếu gõ đúng ta mới cập nhật được bảng tính.
CÁC THAO TÁC VỚI BẢNG TÍNH
11. Chọn nhiều bảng tính
Liền kề: Click chuột vào thẻ tên đầu, giữ phím Shift trong khi click chuột vào thẻ cuối.
Cách nhau: Giữ Ctrl trong khi lần lượt click chuột vào các thẻ tên.
Để bỏ việc chọn một bảng tính nào đó, giữ Ctrl trong khi click chuột vào thẻ tên của bảng tính đó.
CÁC THAO TÁC VỚI BẢNG TÍNH
12. Đặt và thay đổi mật khẩu mở file
Một file sau khi tạo ra có thể được bảo vệ bằng mật khẩu, chỉ có người biết được từ khoá mới làm việc được với file này. Sau khi tạo xong bảng tính, trên menu File chọn Save As…, click chuột vào nút Tools, một menu tắt hiện ra, click chuột vào mục General Options…
Đánh mật khẩu vào mục Password to open, việc đặt mật khẩu này đòi hỏi mỗi khi cần mở file bạn phải đánh vào đúng mật khẩu đã đặt.
Password to open : mật khẩu chống việc mở file.
Password to modify: mật khẩu chống việc hiệu chỉnh file.
Gõ mật khẩu hai lần giống nhau sau đó nhấn OK và Save.
XỬ LÝ DỮ LIỆU VỚI BẢNG TÍNH
Dữ liệu trong các ô giống nhau
Đánh dấu khối miền nhập dữ liệu (ví dụ: A1:C5)
Gõ vào dữ liệu (ví dụ: Hello)
Kết thúc việc nhập bằng cách nhấn phím Ctrl + Enter
Khi đó toàn bộ miền nhập dữ liệu sẽ có giá trị giống nhau (cùng chữ Hello).
Điền chuỗi ngày tháng tăng dần
Đưa con trỏ về ô đầu tiên của miền, gõ vào ngày, tháng, năm bắt đầu.
Trỏ chuột vào góc phải bên dưới của ô để xuất hiện dấu +, bấm giữ chuột phải, kéo thả tại ô cuối của miền.
Một menu xuất hiện, chọn:
Copy Cells: Để điền dữ liệu giống nhau cho tất cả các ô trong miền.
Fill Series: Để tăng lên 1 ngày ở các ô kế tiếp.
Fill Formatting Only: Chỉ copy định dạng sang các ô khác trong miền mà không copy giá trị.
Fill Without Formatting: Điền giá trị cho các ô tiếp theo nhưng
không có định dạng. Ví dụ: Ô đầu tiên của miền có giá trị 5/21/2006
(định dạng ngày tháng), các ô kế tiếp sẽ là: 38859, 38860, 38861, … (không có định dạng).
Fill Days: Tăng lên 1 ngày ở các ô kế tiếp
Fill Weekdays: Tăng lên 1 ngày ở các ô kế tiếp
nhưng chỉ thuộc các ngày trong tuần (từ thứ 2
đến thứ 6).
Ví dụ: Ô đầu tiên của miền là 8/5/2006 (ứng với
thứ 7), các ô kế tiếp sẽ là 8/7/2006 (thứ 2),
8/8/2006 (thứ 3), …, 8/11/2006 (thứ 6), 8/14/2006
(thứ 2), …
Fill Months: Tăng lên 1 tháng (không tăng ngày) ở các ô kế tiếp.
Fill Years: Tăng lên 1 năm ở các ô kế tiếp (không tăng ngày tháng).
Đ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:
Vào menu Tools/Option/Custom List
Trong khung Custom lists bên trái chọn mục NEW LIST
Trong khung List entries bên phải lần lượt nhập các giá trị cho danh sách, hết mỗi giá trị nhấn Enter để xuống dòng.
Ví dụ : Thứ hai Thứ ba
Nhấn nút Add để đưa các phần tử trong khung List entries vào danh sách trong khung Custom lists.
Tiếp tục chọn mục NEW LIST khung
Custom lists để nhập tiếp các giá trị mới.
Nhấn OK để hoàn thành
Để sử dụng danh sách vừa tạo ra ta thực hiện
Nhập một giá trị có trong danh sách tự tạo vào ô đầu
Điền tự động tới ô cuối miền
Sửa, xóa dữ liệu
Xóa dữ liệu trong một ô hoặc một miền
Đưa con trỏ ô về ô hoặc chọn miền cần thiết.
Ấn phím Delete.
Sửa dữ liệu trong ô
Click đúp chuột tại ô (hoặc nhấ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 nhấn Enter) để ghi lại, chọn (hoặc nhấn Esc) để huỷ bỏ mọi sửa đổi.
Hoặc nhập dữ liệu mới cho ô cần sửa chữa, sau đó nhấn Enter.
Các thao tác với khối
Đánh dấu (chọn) khối
Giữ, kéo chuột. Hoặc:
Giữ phím Shift + các phím mũi tên.
Chú ý: Sau khi được chọn toàn bộ khối trừ ô ở góc xuất phát sẽ được đổi màu.
Các thao tác với khối
Một số cách chọn khối đặc biệt:
Chọn một cột: Click chuột tại tên cột đó (các chữ A, B,…) hoặc nhấn tổ hợp phím Ctrl + Space (dấu cách) tại vị trí của cột đó.
Chọn một hàng: Click chuột tại số thứ tự của hàng đó (các số 1, 2, …) hoặc nhấn tổ hợp phím Shift + Space (dấu cách) tại vị trí của dòng đó.
Chọn toàn bộ bảng tính: Click chuột tại 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 nhấn tổ hợp phím Ctrl + Shift + Space.
Các thao tác với khối
Chọn các ô rời rạc: Đưa con trỏ tới ô đầu cần chọn, giữ Ctrl và click chuột tại các ô cần 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, giữ chuột trái và rê chuột tại các miền khác.
Các thao tác với khối
Copy, cắt, dán khối dùng bộ nhớ đệm
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 nhấn tổ hợp phím Ctrl+C hoặc trên menu Edit chọn Copy.
Xoá: Chọn biểu tượng cut , hoặc nhấn tổ hợp phím Ctrl+X, hoặc trên menu Edit chọn 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 nhấn tổ hợp phím Ctrl+V hoặc trên menu Edit chọn Paste.
Các thao tác với khối
Copy, di 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 mũi tên 4 chiều . 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.
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 sau:
Các thao tác với khối
Move Here: di chuyển dữ liệu và định dạng.
Copy Here: copy toàn bộ, bao gồm cả dữ liệu và định
dạng.
Copy Here as Values Only: chỉ copy dữ liệu.
Copy Here as Formats Only: chỉ copy định dạng.
Link Here: Giá trị miền đích sẽ tham chiếu đến địa chỉ
miền nguồn.
Shift Down and Copy: Dịch chuyển các ô xuống dưới và thực hiện copy.
Shift Right and Copy: Dịch chuyển các ô sang bên phải và thực hiện copy.
Shift Down and Move: Dịch chuyển các ô xuống dưới và thực hiện di chuyển dữ liệu.
Shift Right and Move: Dịch chuyển các ô sang phải và thực hiện di chuyển dữ liệu.
Cancel: Hủy bỏ lệnh này.
Các thao tác với khối
Các dạng copy đặc biệt
Đánh dấu các ô cần copy.
Nhấn nút copy trên thanh công cụ (hoặc Ctrl+C hoặc vào menu Edit/Copy).
Nhấn chọn ô ở góc trái của vùng sẽ được dán.
Trên menu Edit chọn Paste Special …
Nhấn chọn lựa trong phần mục Paste và nhấn OK
Trong đó
All: dán toàn bộ nội dung và định dạng của ô
Formulas: chỉ dán công thức.
Values : chỉ dán giá trị được hiển thị trên ô.
Formats : chỉ dán định dạng của ô.
Comments : chỉ dán chú thích trong ô.
Validation : chỉ dán các quy định về điều kiện của dữ liệu.
All except borders: dán toàn bộ nội dung và định dạng trừ đường viền.
Column widths: chỉ dán chiều rộng của ô (lấy chiều rộng của ô nguồn)
Formulas and number formats: dán định dạng số và công thức.
Values and number formats: dán định dạng số và giá trị
Xử lý ô, cột, hàng trong bảng tính
Thay đổi kích thước cột, hàng
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 dấu mũi tên hai chiều , kéo và thả vạch đó tại vị trí mới. Nếu muốn độ rộng của cột chứa đủ dữ liệu có trong ô bất kỳ thuộc cột đó thì nhấn kép chuột vào mũi tên hai chiều ở trên.
Hàng: Trỏ chuột vào vạch ngang dưới số thứ tự hàng sao cho xuất hiện dấu mũi tên hai chiều thẳng đứng , kéo và thả vạch đó tại vị trí mới. Nếu muốn chiều cao của hàng chứa đủ dữ liệu có trong ô bất kỳ thuộc hàng đó thì nhấn kép chuột vào mũi tên hai chiều thẳng đứng ở trên.
Nhiều cột / nhiều hàng:
Cột:
Chọn một số ô của các cột
Vào menu Format chọn Colum->Width
Gõ vào độ rộng mới cho các cột
Nhấn OK hoặc Enter
Hàng:
Chọn một số ô của các hàng
Vào menu Format chọn Row->Height
Gõ vào chiều cao mới cho các hàng
Nhấn OK hoặc Enter
Chèn thêm cột, hàng, ô
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 menu Insert/Columns hoặc nhấn chuột phải và chọn Insert, Excel sẽ chèn thêm các cột trống và đẩy các cột được chọn sang phải.
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 menu Insert/Rows hoặc nhấn chuột phải và chọn Insert, Excel sẽ chèn thêm các hàng trống và đẩy các hàng được chọn xuống dưới.
Ô:
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 ô B3:C6), cần thêm bao nhiêu ô ta chọn bấy nhiêu.
Chọn menu Insert/Cells (hoặc nhấn chuột phải, chọn Insert…)xuất hiện hộp thoại Insert như sau:
Xoá cột, hàng, ô
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 xóa bao nhiêu cột ta chọn bấy nhiêu.
Trên menu Edit chọn Delete hoặc nhấn chuột phải và chọn Delete.
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 xóa bao nhiêu hàng ta chọn bấy nhiêu.
Trên menu Edit chọn Delete hoặc nhấn chuột phải và chọn Delete.
Ô:
Chọn khối là các ô cần xoá
Trên menu Edit chọn Delete… hoặc nhấn chuột phải và chọn Delete….
Xuất hiện hộp thoại Delete như sau:
Chuyển hàng thành cột và ngược lại
Chọn các ô muốn cho đổi chỗ hay còn gọi là miền nguồn (B3:B8)
Chọn biểu tượng Copy hoặc nhấn tổ hợp phím Ctrl+C
Đưa con trỏ về ô đầu tiên của miền đích (D3)
Vào menu Edit/Paste Special… (hoặc nhấn chuột phải và chọn Paste Special…), đánh dấu chọn ô Transpose sau đó nhấn chọn OK.
Khi đó ở miền nguồn sẽ có vòng nhấp nháy xung quanh, nhấn phím ESC để loại bỏ vòng nhấp nháy này. Không nên nhấn phím Enter vì sẽ có kết quả như sau:
Việc chuyển từ hàng sang cột cũng được thực hiện hoàn toàn tương tự.
Ẩn/hiện cột hàng
Chọn các cột (hoặc các hàng) cần ẩn đi.
Chọn menu Format/Column (hoặc Format/Row) rồi chọn mục 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, F. 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 menu Format/Column (hoặc Format/Row) rồi chọn mục Unhide.
7. Định dạng dữ liệu
Định dạng ký tự
Dùng thanh định dạng:
Dùng hộp định dạng:
Chọn ô hoặc vùng dữ liệu cần định dạng.
Chọn menu Format/Cells… (hoặc nhấn chuột phải và chọn Format Cells…, hoặc nhấn tổ hợp phím Ctrl+1) sau đó chọn tab Font bạn sẽ có hộp thoại Format Cells như sau:
Lưu ý: Nếu muốn hủy bỏ tất
cả các định dạng trên (màu chữ,
in đậm, gạch chân, …) thì đánh
dấu chọn mục Normal font trên
hộp thoại này.
Định dạng số
Dùng thanh công cụ:
Dùng hộp định dạng:
Trong hộp Category có các tùy
chọn cho định dạng số. Trong hộp
Sample bên cạnh hiển thị định
dạng mà bạn đã chọn tương ứng
với từng mục trong Category.
Mục Custom cho phép bạn tạo
ra các định dạng theo yêu cầu
riêng, ví dụ như mã sản phẩm
Canh biên (dóng hàng)
Dùng thanh công cụ
Dùng hộp định dạng
Horizontal: Canh lề theo chiều ngang
General (mặc định): Ký tự được canh lề
bên trái, số được canh sang phải, các giá
trị logic và lỗi được canh vào giữa.
Left, Center, Right: Dữ liệu tương ứng được canh lề trái, vào giữa, sang phải.
Fill: Tự điền đầy ô (trong ô chỉ cần gõ 1 ký tự hoặc 1 chuỗi ký tự sau đó chọn mục này, Microsoft Excel sẽ tự điền đầy ô hoặc vùng này bằng ký tự hoặc chuỗi ký tự được gõ vào). Tuy nhiên giá trị thực tế là giá trị mà ta đã gõ vào ban đầu.
Ví dụ: Giả sử tại ô B5 ta gõ vào số 15 sau đó định dạng Horizontal là Fill ta được chuỗi số 15151515. Tại ô B6 ta gõ công thức =B5+1 thì ta sẽ có kết quả là 16 chứ không phải là 15151516
Justiffy: Canh đều hai bên trái và phải. Trường hợp độ rộng của cột nhỏ hơn nội dung bên trong nó, Excel sẽ tự động tăng chiều cao của hàng.
Center Across Selection: Canh vào giữa vùng được chọn; tuy nhiên các ô này không trộn lại với nhau như khi ta chọn mục Merge and Center trên thanh định dạng.
Distributed: Được phân bố hợp lý nhất. Nếu dữ liệu trong ô là 1 từ thì nó được canh vào giữa. Nếu dữ liệu trong ô là 1 câu gồm nhiều từ thì dữ liệu được phân bố đều nhau. Tuy nhiên nếu độ rộng của cột nhỏ hơn nội dung bên trong nó thì Excel sẽ tự động tăng chiều cao của hàng cho hợp lý.
Hãy xem ví dụ sau đây:
Vertical: Canh lề theo chiều dọc
Top, Center, Justify: Dữ liệu tương ứng được canh lên trên, vào giữa và canh đều trên dưới theo chiều dọc của ô.
Bottom (mặc định): Dữ liệu được canh phía dưới cùng của ô.
Distributed: Lựa chọn Distributed cho phép phân bố dữ liệu hợp lý theo chiều cao của ô.
Nếu độ cao của dòng lớn hơn số dòng trong ô và độ rộng của cột chứa đủ nội dung dữ liệu thì dữ liệu được canh vào giữa ô.
Nếu độ cao của dòng lớn hơn số dòng trong ô và độ rộng của cột nhỏ hơn nội dung dữ liệu thì dữ liệu được phân bố đều nhau theo hai phía trên và dưới của ô.
Tuy nhiên nếu độ cao của dòng nhỏ hơn số dòng trong ô và độ rộng của cột nhỏ hơn nội dung dữ liệu thì Excel sẽ tự động cắt bỏ những dòng sau của dữ liệu.
Indents: Thụt lề vào
Thụt nội dung của ô hoặc vùng được đánh dấu từ bất kỳ cạnh nào của ô hoặc vùng, phụ thuộc vào tùy chọn của bạn ở mục Horizontal và Vertical. Mỗi khi tăng một giá trị trong ô Indent tương đương với chiều rộng của một ký tự.
Text Control
Wrap text: Dữ liệu trong ô tự động xuống hàng khi chiều dài dữ liệu lớn hơn chiều rộng cột. Nếu bạn muốn xuống hàng tại vị trí nào đó trong dữ liệu thì đặt con trỏ vào vị trí đó và nhấn tổ hợp phím Alt+Enter.
Shrink to fit: Tự động điều chỉnh kích cỡ chữ trong ô theo chiều ngang sao cho thấy được toàn bộ dữ liệu trong ô mà không cần phải điều chỉnh lại độ rộng cột. Sau đó, nếu tăng độ rộng cột thì kích cỡ chữ trong ô tự động tăng theo kích cỡ quy định ban đầu.
Merge cells: Trộn các ô trong khối được đánh dấu thành một ô.
Trong phần Orientation cho phép xác định góc quay của dữ liệu trông ô. Bạn có thể khai báo góc quay trong ô Degree hoặc di chuyển cây kim độ đến góc cần quay.
Text direction: Điều khiển hướng ký tự
Kẻ khung
Dùng thanh định dạng
Dùng hộp định dạng
None : Không có đường viền
Outline: Viền xung quanh khối hoặc ô
Inside: Viền lưới các ô bên trong
Left, Right, Top, Bottom : Viền trái, phải, trên, dưới mỗi ô
Color : Màu của đường viền
Style : Chọn kiểu đường viền
Tô màu nền cho các khối hoặc ô
Dùng thanh định dạng
Dùng hộp định dạng
Bạn chọn màu nền không mục Color.
Chọn kiểu dáng cho nền trong
mục Pattern.
Cuối cùng nhấn nút OK để chấp
nhận.
Định dạng tự động
Chọn vùng dữ liệu cần định dạng.
Vào menu Format/AutoFormat… Trong hộp thoại AutoFormat liệt kê một số mẫu định dạng có sẵn.
8. Đặt tên cho ô hoặc khối
Đặt tên cho ô hoặc khối bằng tay
Cách 1:
Nháy chuột tại nút tại ô Name Box trên thanh công thức.
Xóa địa chỉ hiện tại của ô hoặc khối, gõ tên cần đặt rồi nhấn phím Enter.
Cách 2:
Vào menu Insert, chọn mục Name/Define… (hoặc nhấn tổ hợp phím Ctrl+F3)
Trong khung Names in workbook của hộp thoại Define Name ta gõ vào tên cho ô hoặc khối đã chọn.
Sau đó nhấn nút Add bên cạnh để đưa tên mới đặt này vào danh sách.
Nhấn nút OK để chấp nhận hoặc Close để đóng hộp thoại này.
Đặt tên theo tiêu đề của cột hay hàng (tự động)
Chọn ô hay khối cần đặt tên gồm cả các tiêu đề hàng hoặc cột. Ví dụ: Đánh dấu khối B2:B10 là cột chứa MÃ_SỐ.
Vào menu Insert rồi chọn Name/Create…
Trong hộp thoại Create Names ta có các mục như sau:
Top row: Lấy ô ở hàng đầu (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
Bottom row: Lấy ô ở hàng cuố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
Nhấn nút OK để chấp nhận hoặc Cancel để hủy bỏ.
Kết quả khối B3:B10 có tên là MÃ_SỐ - là tiêu đề của cột này.
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
Nhấn phím F3 (hoặc nháy chuột tại nút Name Box trên thanh công thức, hoặc vào menu Insert, chọn mục Name/Paste…)
Chọn tên cần thiết từ danh sách rồi nhấn OK.
Về nhanh một ô đã đượ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 nút Name Box trên thanh công thức và chọn tên cần thiết từ danh sách (hoặc gõ địa chỉ của ô vào hộp này)
Cách 2: Nhấn phím F5. Chọn tên cần thiết từ danh sách và nhấn OK.
Đây cũng là phương pháp kiểm tra xem tên đã được đặt cho một ô hay miền hay chưa
Xoá tên
Vào menu Insert, chọn mục Name/Define… (hoặc nhấn tổ hợp Ctrl+F3)
Chọn tên cần xóa từ danh sách.
Nhấn nút Delete.
Nhấn nút OK hoặc Close.
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 ô
Ghi chú thích cho ô
Chuyển con trỏ về ô cần ghi chú thích
Mở hộp Comment theo một trong ba cách sau:
Cách 1: Nhấn tổ hợp phím Shift+F2.
Cách 2: Trên menu Insert chọn Comment
Cách 3: Nhấn chuột phải và chọn Insert Comment
Một Text Box có nền màu vàng (gọi là Comment: chú thích) xuất hiện bên cạnh ô cùng với nút màu đỏ ở phía trên bên phải của ô (gọi là Indicator: dấu chỉ thị).
Gõ vào lời ghi chú vào ô Comment đó.
Nếu không thấy dấu chỉ thị này hãy làm như sau:
Vào menu Tools chọn Options rồi chọn tab View.
Trong phần Comments, chọn:
None: Ẩn cả dấu chỉ thị lẫn chú thích.
Comment indicator only: Luôn luôn hiển thị dấu chỉ thị, phần chú thích chỉ hiển thị khi rê chuột đến ô đó.
Comment & indicator: Luôn luôn hiển thị cả phần chú thích và dấu chỉ thị.
Nhấn nút OK để chấp nhận.
Sửa/Xoá các chú thích
Để sửa hay xóa một chú thích:
Chuyển con trỏ về ô chú thích
Trên menu Insert chọn Edit Comment
Hoặc:
Bấm chuột phải vào ô có chú thích, chọn Edit Comment để sửa hoặc Delete Comment để xóa chú thích.
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 sửa đổi nội dung hay xem công thức ở một số ô khác, … 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 khối cần bảo vệ
Ở menu Format chọn Cells, trong hộp thoại Format Cells chọn tab Protection.
Trong hộp thoại này đánh dấu chọn vào:
Locked: để khoá không cho sửa đổi dữ liệu
Hidden: để che dấu công thức
Nhấn OK để chấp nhận.
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 26
HÀM TRONG EXCEL
1. Quy tắc sử dụng hàm
Các hàm có dạng tổng quát:
TÊN_HÀM([Các đối số]).
Ví dụ:
TODAY(): cho kết quả là ngày hiện tại trong máy (hàm không đối số)
LEN (“Excel 5.0”): cho kết quả độ dài của chuỗi là 9 (hàm 1 đối số)
AVERAGE(A1,B5,D8): cho kết quả là trung bình cộng các số trong các ô A1, B5 và D5 (hàm nhiều đối số)
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 đối số 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 tùy theo cách đặt các thông số quốc tế. Trong một hàm có thể chứa nhiều nhất 30 đối số 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 đối số cho một hàm khác (hàm lồng nhau, nhiều nhất là 7 mức) thì 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 của tam giác đó.
Ở đâ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 đối số (đối số) cho hàm SQRT.
2. Nhập hàm vào bảng tính
Gõ từ bàn phím
Gõ dấu =
Gõ vào tên hàm, dấu (, các đối số theo đúng định dạng quy định, dấu)
2. Nhập hàm vào bảng tính
Dùng biểu tượng Insert Function
Bước 1:
Đặt con trỏ tại ô cần nhập công thức.
Chọn biểu tượng trên thanh công thức. Xuất hiện hộp thoại Insert Function 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 học 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, …
Bước 2:
Chọn nhóm hàm trong khung Or select a 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 Select a function.
Bước 3:
Chọn hàm thích hợp trong khung Select a function.
Bước 4:
Nhấn Chọn OK. Xuất hiện hộp thoại Function Arguments liệt kê ô nhập các đối số, công dụng của hàm khung chứa giá trị kết quả của hàm.
Điền các đối số của hàm bằng cách nhấ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: Nhấn chọn OK, Excel sẽ 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 Arguments, Excel sẽ chuyển sang việc chỉnh sửa hàm.
Dùng menu
Trên menu Insert chọn mục Function. Xuất hiện hộp thoại Insert Function.
Các bước còn lại giống như trên.
3. Một số hàm thông dụng
Hàm ngày tháng
a. Hàm DATE
Cú pháp: DATE(year, month, day)
Ví dụ:
=DATE(2006, 09, 10) trả về ngày 10-09-2006.
b. Hàm DATEVALUE
Cú pháp: DATEVALUE(date_text)
Ví dụ:
=DATEVALUE(“9/10/2006”) trả về giá trị 38970.
=DATEVALUE(“10-Sep-2006”) trả về giá trị 38970.
Lưu ý:
Để xem ngày dạng số của ví dụ trên, click chuột phải tại ô đó và chọn Format Cells…. Chọn tab Number, trong hộp Category chọn mục Date rồi click OK.
c. Hàm DAY
Cú pháp: DAY(serial_number)
Ví dụ:
=DAY(“10-Sep-2006”) trả về giá trị là ngày 10.
d. Hàm MONTH
Cú pháp: MONTH(serial_number)
Ví dụ:
=MONTH(“10-Sep-2006”) trả về giá trị là tháng 9.
i. Hàm YEAR
Cú pháp: YEAR(serial_number)
Trong đó:
serial_number là một số theo định dạng ngày-tháng của MS Excel.
Ý nghĩa:
Trả về một số chỉ năm, có giá trị từ 1900 đến 9999.
Ví dụ:
=YEAR(“15-Sep-2006”) trả về giá trị là năm 2006.
e. Hàm NOW
Cú pháp: NOW()
Ví dụ:
=NOW() trả về giá trị 9/10/2006 20:02
Lưu ý: Hàm này không có đối số.
f. Hàm TIME
Cú pháp: TIME(hour, minute, second)
Ví dụ:
=TIME(6,30,50) trả về giá trị 6:30:50 AM.
=TIME(16,30,50) trả về giá trị 4:30:50 PM.
g. Hàm TODAY
Cú pháp: TODAY()
Ý nghĩa:
Trả về ngày hiện tại của hệ thống theo định dạng ngày-tháng.
Ví dụ:
= TODAY() trả về giá trị 9/10/2006.
Lưu ý: Hàm này không có đối số
h. Hàm WEEKDAY
Cú pháp:
WEEKDAY(serial_number, [return_type])
Ví dụ:
=WEEKDAY(“9/10/2006”,3) trả về giá trị 6 tương ứng với ngày Chủ nhật.
Lưu ý:
Nếu giá trị return_type để trống thì mặc định nó là giá trị 1.
Ví dụ:
=WEEKDAY(“9/10/2006”) trả về giá trị 1 tương ứng với ngày Chủ nhật.
3.2 Hàm ký tự
a. Hàm CONCATENATE
Cú pháp: CONCATENATE(text1, [text2], [text3], …)
Ý nghĩa: Nối nhiều chuỗi lại với nhau thành một chuỗi ký tự duy nhất.
Ví dụ:
=CONCATENATE(“Hôm nay ”, “là ngày”, “ 15/09/2006”) trả về chuỗi “Hôm nay là ngày 15-09-2006”.
=CONCATENATE(“Địa chỉ: ”, 2, “ Lê Lợi”) sẽ trả về chuỗi “Địa chỉ: 2 Lê Lợi”.
b. Hàm EXACT
Cú pháp: EXACT(text1, text2)
Ý nghĩa: Kiểm tra xem hai chuỗi text1 và text2 có giống hệt nhau hay không. Nếu giống nhau, hàm trả về giá trị TRUE, ngược lại trả về giá trị FALSE. Hàm này có phân biệt chữ HOA và chữ thường.
Ví dụ: =EXACT(“MS Excel”, “MS EXCEL”) trả về giá trị FALSE.
=EXACT(“MS EXCEL”, “MS EXCEL”) trả về giá trị TRUE.
c. Hàm FIND
Cú pháp: FIND(find_text, within_text, [start_num])
Ý nghĩa: Trả về vị trí bắt đầu của chuỗi find_text trong chuỗi within_text. Hàm này phân biệt chữ HOA và chữ thường.
Ví dụ: =FIND(“e”,“MS Excel”) trả về giá trị 7.
=FIND(“E”,“MS Excel”) trả về giá trị 4.
=FIND(“chào”, “Xin chào bạn”,10) trả về giá trị #VALUE! (lỗi không tìm thấy giá trị).
d. Hàm FIXED
Cú pháp: FIXED(number, [decimals], [no_commas])
Ý nghĩa: Làm tròn số number đến decimals số thập phân. Kết quả trả về là một chuỗi số có hoặc không có dấu phẩy phân tách hàng ngàn.
Ví dụ:=FIXED(1234.567,1) sẽ trả về kết quả là 1,234.6.
=FIXED(1234.567,-1) sẽ trả về kết quả là 1,230.
=FIXED(1234.567,-1,TRUE) sẽ trả về kết quả là 1230.
=FIXED(44.332) sẽ trả về kết quả là 44.33.
Lưu ý: Kết trả về ở các ví dụ trên đều là các chuỗi số (nghĩa là nó tự động căn trái trong ô)
e. Hàm LEFT
Cú pháp: LEFT(text, [num_chars])
Ý nghĩa:
Trả về chuỗi con có num_chars ký tự trong chuỗi văn bản text tính từ trái qua.
Ví dụ:
=LEFT(“Microsoft Excel”, 9) trả về chuỗi “Microsoft”.
=LEFT(“A123”) trả về ký tự “A”.
f. Hàm LEN
Cú pháp: LEN(text)
Trong đó:
text là chuỗi văn bản có độ dài cần đếm. Các dấu cách (spaces) cũng được tính như các ký tự.
Ý nghĩa:
Trả về số lượng các ký tự có trong chuỗi text.
Ví dụ:
=LEN(“Đại học Huế”) trả về giá trị 11.
g. Hàm LOWER
Cú pháp: LOWER(text)
Ý nghĩa: Chuyển tất cả các ký tự trong chuỗi văn bản text thành in thường.
Ví dụ:
=LOWER(“HAPPY NEW YEAR”) trả về chuỗi “happy new year”.
=LOWER(“Xin CHÀO BẠN”) trả về chuỗi “xin chào bẠn”.
Lưu ý: Một số từ tiếng Việt có dấu có thể không chuyển đổi thành công như trong ví dụ thứ hai ở trên.
h. Hàm MID
Cú pháp: MID(text, start_num, num_chars)
Ý nghĩa:
Trả về một chuỗi con trong chuỗi text tính từ vị trí start_num và có num_chars ký tự.
Ví dụ:
=MID(“Xin chào bạn”, 5, 4) trả về chuỗi “chào”.
i. Hàm PROPER
Cú pháp: PROPER(text)
Ví dụ:
=PROPER(“danh sách Học viên”) trả về chuỗi “Danh Sách Học Viên”.
=PROPER(“Microsoft EXCEL”) trả về chuỗi “Microsoft Excel”.
Lưu ý: Đối với tiếng Việt có dấu thì một số ký tự có thể không được chuyển đổi thành công.
Ví dụ:
=PROPER(“KẾt quẢ học tậP”) trả về chuỗi “KẾt QuẢ Học Tập”.
j. Hàm REPLACE
Cú pháp:
REPLACE(old_text, start_num, num_chars, new_text)
Ý nghĩa:
Thay thế một phần chuỗi văn bản old_text bởi một chuỗi văn bản mới là new_text.
Ví dụ:
=REPLACE(“Trung tâm CNTT”,11,4,“Công nghệ Thông tin”) trả về chuỗi “Trung tâm Công nghệ Thông tin”.
k. Hàm REPT
Cú pháp: REPT(text, number_times)
Trong đó:
text là chuỗi văn bản cần lặp lại.
number_times là số lần lặp lại của chuỗi text.
Ý nghĩa: In lặp lại chuỗi text đến number_times lần.
Ví dụ:
=REPT(“Vui quá! ”,3) trả về chuỗi “Vui quá! Vui quá! Vui quá! ”.
l. Hàm RIGHT
Cú pháp: RIGHT(text, [num_chars])
Ý nghĩa:
Trả về chuỗi con có num_chars ký tự trong chuỗi văn bản text tính từ phải qua.
Ví dụ:
=RIGHT(“Microsoft Excel”, 5) trả về chuỗi “Excel”.
=RIGHT(“A123”) trả về ký tự “3”.
m. Hàm SEARCH
Cú pháp:
SEARCH(find_text, within_text, [start_num])
Ý nghĩa: Trả về vị trí bắt đầu của chuỗi find_text trong chuỗi within_text. Hàm này không phân biệt chữ HOA và chữ thường.
Ví dụ:
=SEARCH(“e”,“MS Excel”) trả về giá trị 4.
=SEARCH(“e”,”MS Excel”,5) trả về giá trị 7.
=SEARCH(“~*”, “Ngôi * cô đơn”,1) trả về giá trị 6.
=SEARCH(“*”, “Ngôi * cô đơn”,1) trả về giá trị 1.
n. Hàm SUBSTITUTE
Cú pháp:
SUBSTITUTE(text, old_text, new_text, [instance_num])
Ý nghĩa: Thay thế chuỗi old_text xuất hiện trong chuỗi text bởi chuỗi new_text tại vị trí instance_num.
Ví dụ:
=SUBSTITUTE(“Hãy xem xem”, “xem”, “nhìn”,1) trả về chuỗi “Hãy nhìn xem”.
=SUBSTITUTE(“Hãy Xem xem”, “xem”, “nhìn”,1) trả về chuỗi “Hãy Xem nhìn”. (Chữ “Xem” thứ nhất không được thay thế).
=SUBSTITUTE(“Hãy xem xem”, “xem”, “đây”, 2) trả về chuỗi “Hãy xem đây”.
o. Hàm T
Cú pháp: T(value)
Ý nghĩa: Kiểm tra xem giá trị value có phải là một chuỗi văn bản hay không. Nếu là chuỗi văn bản thì kết quả sẽ trả về chuỗi đó, ngược lại thì sẽ trả về một chuỗi rỗng.
Ví dụ: =T(“Chào buổi sáng”) trả về chuỗi “Chào buổi sáng”.
=T(123) trả về chuỗi rỗng vì đây là một số.
=T(“123”) trả về chuỗi “123”.
=T(TRUE) trả về chuỗi rỗng vì đây là giá trị logic.
p. Hàm TEXT
Cú pháp: TEXT(value, format_text)
Ý nghĩa:
Chuyển một số sang dạng text (văn bản) theo định dạng được chỉ định.
Ví dụ:
=TEXT(0.5, “0%”) trả về chuỗi “50%”.
q. Hàm TRIM
Cú pháp: TRIM(text)
Ý nghĩa:
Xóa tất cả các khoảng trắng dư thừa bên trái, bên phải và giữa các từ trong chuỗi text.
Ví dụ:
=TRIM(“ Xin chào bạn ”) trả về chuỗi “Xin chào bạn”.
r. Hàm UPPER
Cú pháp: UPPER(text)
Ý nghĩa:
Chuyển tất cả các ký tự trong chuỗi văn bản text thành in HOA.
Ví dụ:
=UPPER(“Xin chào bạn”) trả về chuỗi “XIN CHÀO BạN”.
Lưu ý: Một số từ tiếng Việt có dấu có thể không chuyển đổi thành công như trong ví dụ trên.
s. Hàm VALUE
Cú pháp: VALUE(text)
Ý nghĩa: Chuyển đổi chuỗi ký tự số thành một số.
Ví dụ:
Ô A3 có chuỗi “Trung tâm CNTT – SĐT: 054.828423” thì hàm
=VALUE(RIGHT(A3, 6)) trả về số 828423.
3.3 Hàm toán học
a. Hàm ABS
Cú pháp: ABS(number)
Ý nghĩa:
Trả về giá trị tuyệt đối của số number.
Ví dụ:
=ABS(-12) có kết quả là 12.
b. Hàm EXP
Cú pháp: EXP(number)
Trong đó:
number là số mũ của cơ số e. Hằng số e=2.71828182845904 là cơ số của logarit tự nhiên.
Ý nghĩa:
Trả về e lũy thừa number.
Ví dụ:
=EXP(1) trả về giá trị 2.718281828
c. Hàm INT
Cú pháp: INT(number)
Ý nghĩa:
Trả về số nguyên lớn nhất không nhỏ hơn number.
Ví dụ:
=INT(2.789) trả về kết quả là 2.
d. Hàm MOD
Cú pháp: MOD(number, divisor)
Ý nghĩa:
Trả về số dư của phép chia nguyên của số number cho divisor.
Ví dụ:
=MOD(11,3) trả về kết quả là 2.
e. Hàm ROUND
Cú pháp: ROUND(number, num_digits)
Ý nghĩa:
Làm tròn số number với độ chính xác đến con số thứ num_digits.
Ví dụ: Ô A1 chứa số 347645.546, khi đó:
= ROUND(A1,0) cho kết quả 347646
= ROUND(A1,2) cho kết quả 347645.55
=ROUND(A1,1) cho kết quả 347645.5
=ROUND(A1,-3) cho kết quả là 348000
f. Hàm SQRT
Cú pháp: SQRT(number)
Ý nghĩa:
Trả về giá trị căn bậc hai của số number
Ví dụ:
=SQRT(4) cho kết quả là 2.
g. Hàm SUM
Cú pháp: SUM(number1, [number2], …)
Ý nghĩa:
Tính tổng của dãy số number1, number2, …
Ví dụ:
=SUM(4, 5, 6) cho kết quả là 15
h. Hàm SUMIF
Cú pháp: SUMIF (range, criteria, [sum_range])
Trong đó:
range là vùng ô để so sánh với tiêu chuẩn (điều kiện) criteria.
criteria là điều kiện tính tổng, có thể là số, chữ hoặc biểu thức. Quyết định ô nào trong vùng sum_range sẽ được tính tổng.
sum_range là vùng sẽ được tính tổng. Các ô trong vùng sum_range chỉ được cộng vào nếu các ô tương ứng với nó trong range thỏa mãn điều kiện criteria.
Lưu ý: Hàm SUMIF chỉ tính tổng theo một điều kiện. Nếu cần tính tổng theo hai điều kiện trở lên phải dùng công thức mảng hoặc hàm DSUM (xem phần hàm cơ sở dữ liệu).
Ví dụ: Cho bảng tính trong ví dụ sau:
=SUMIF(D2:D11, “nữ”, H2:H11) cho kết quả là 1080000
i. Hàm TRUNC
Cú pháp: TRUNC(number, [num_digits])
Ý nghĩa: Lấy phần nguyên của số number bằng cách cắt xén phần thập phân, phần phân số hoặc một phần của nó.
Ví dụ:
=TRUNC(474.546,0) trả về kết quả 474.
=TRUNC(474.546,2) trả về kết quả 474.54 (trong khi đó hàm =ROUND(474.546,2) cho kết quả 474.55).
=TRUNC(474.546,-2) trả về kết quả 400.
3.4 Hàm logic
a. Hàm AND
Cú pháp: AND(logical1, [logical2],…)
Ý nghĩa:
Cho kết quả TRUE (đúng) nếu tất cả các biểu thức đều là TRUE, cho kết quả là FALSE (sai) nếu có ít nhất một biểu thức logic có giá trị là FALSE.
Ví dụ:
=AND(5>3, 6>4) cho kết quả là TRUE
=AND(5>3, 6<=4) cho kết quả là FALSE
b. Hàm IF
Cú pháp:
IF(logical_test, [value_if_true], [value_if_false])
Ý nghĩa: 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.
Ví dụ: Ô A2 có giá trị 50. Khi đó:
=IF(A2<=100, “Nằm trong kế hoạch”, “Vượt kế hoạch”) sẽ trả về giá trị là chuỗi “Nằm trong kế hoạch”.
=IF(A2=100, SUM(B5:B15), “”) sẽ trả về chuỗi rỗng “”
c. Hàm NOT
Cú pháp: NOT(logical)
Ý nghĩa:
Thay đổi giá trị TRUE thành FALSE và ngược lại.
Ví dụ:
=NOT(1+1>2) trả về TRUE
=NOT(TRUE) trả về FALSE
d. Hàm OR
Cú pháp: OR(logical1, [logical2], …)
Ý nghĩa:
Cho kết quả là TRUE (đúng) nếu một trong các biểu thức logic có giá trị là TRUE, cho kết quả là FALSE (sai) nếu tất cả các biểu thức là FALSE.
Ví dụ:
=OR(LEFT(“Welcome”,2)=“We”, LEN(“Welcome”)<>7) trả về TRUE
=OR(5=3, LEN(“Welcome”)<>7) trả về FALSE
3.5 Hàm thống kê
a. Hàm AVERAGE
Cú pháp: AVERAGE(number1, [number2], …)
Ý nghĩa:
Tính trung bình cộng của các số number1, number2, …. Đối số của hàm AVERAGE có thể là số hoặc địa chỉ của các ô chứa giá trị số.
Ví dụ:
=AVERAGE(5, 8, 2) có kết quả là 5.
b. Hàm COUNT
Cú pháp: COUNT(value1, [value2], …)
Trong đó:
value1, value2, … là các giá trị hoặc tham chiếu đến địa chỉ các ô chứa dữ liệu. Nhưng chỉ những giá trị số mới được đếm. Có tối đa 30 đối số.
Ý nghĩa:
Đếm số lượng các ô chứa dữ liệu kiểu số và các số có mặt trong danh sách các đối số.
Ví dụ:
=COUNT(3, “Good morning”, 0, “”) trả về giá trị 2.
c. Hàm COUNTA
Cú pháp: COUNTA(value1, [value2], …)
Trong đó:
value1, value2, … là các giá trị hoặc tham chiếu đến địa chỉ các ô chứa bất kỳ dữ liệu nào. Có tối đa 30 đối số.
Ý nghĩa:
Tương tự hàm COUNT ở trên nhưng hàm này có thể đếm bất kỳ kiểu dữ liệu nào.
Ví dụ:
=COUNTA(3, “Good morning”, 0, “”) trả về giá trị 4.
d. Hàm COUNTBLANK
Cú pháp: COUNTBLANK(range)
Trong đó:
range là miền cần đếm các ô rỗng.
Ý nghĩa:
Đếm số lượng các ô rỗng (không chứa dữ liệu) trong miền range.
e. Hàm COUNTIF
Cú pháp: COUNTIF(range, criteria)
Trong đó: range là miền chứa các ô có dữ liệu cần đếm.
criteria là điều kiện của các số, biểu thức hoặc chuỗi văn bản.
Ý nghĩa: Đếm các ô trong miền range thỏa mãn điều kiện criteria.
Lưu ý: Hàm COUNTIF chỉ đếm theo một điều kiện. Nếu cần đếm kết hợp nhiều hơn một điều kiện thì phải dùng hàm DCOUNT (xem phần hàm cơ sở dữ liệu).
Ví dụ:
Cho ví dụ như hình bên dưới, khi đó: =COUNTIF(B3:B6, “apples”) cho kết quả là 2.
f. Hàm LARGE
1. Lưu bảng tính trên đĩa
2. Mở bảng tính đã có trên đĩa
3. Đóng bảng tính
4. Thêm một bảng tính
5. Xoá bảng tính
6. Đổi tên bảng tính
7. Sao chép/Di chuyển một bảng tính
CÁC THAO TÁC VỚI BẢNG TÍNH
1. Lưu bảng tính trên đĩa
Cách 1: Click nút Save trên thanh Standard Tool
Cách 2: Vào menu File, chọn Save, hoặc nhấn tổ hợp phím Ctrl+S)
CÁC THAO TÁC VỚI BẢNG TÍNH
2. Mở bảng tính đã có trên đĩa
Cách 1: Click nút Open trên thanh Standard Tool
Cách 2: Vào menu File chọn Open , hoặc nhấn tổ hợp phím Ctrl+O)
CÁC THAO TÁC VỚI BẢNG TÍNH
3. Đóng bảng tính
Cách 1: Click nút Close
Cách 2: Vào menu File chọn Close
CÁC THAO TÁC VỚI BẢNG TÍNH
4. Thêm một bảng tính
Cách 1: Vào menu Insert chọn Worksheet.
Cách 2: Click chuột phải trên thanh thẻ tên bảng tính và chọn Insert… Hộp thoại Insert hiện ra, trên tab General ta chọn mục Worksheet và nhấn OK.
CÁC THAO TÁC VỚI BẢNG TÍNH
5. Xóa một bảng tính
Cách 1: Vào menu Edit chọn Delete sheet.
Cách 2: Click chuột phải trên thanh thẻ tên bảng tính và chọn Delete
CÁC THAO TÁC VỚI BẢNG TÍNH
6. Đổi tên một bảng tính
Cách 1: Vào menu Format chọn Sheet/Rename.
Cách 2: Click đúp chuột phải vào thẻ tên bảng tính và nhập tên mới.
CÁC THAO TÁC VỚI BẢNG TÍNH
7. Sao chép/Di chuyển một bảng tính
Cách 1:
Di chuyển: rê chuột kéo thả thẻ tên (Sheet cần di chuyển) đến vị trí mới (Sheet khác).
Sao chép: Thực hiện tương tự như Di chuyển nhưng sử dụng thêm phím Ctrl
Cách 2: Vào menu Edit chọn Move or Copy Sheet… sau đó chọn Workbook cần di chuyển
tới khung To book. Vị trí đặt bảng tính hiện tại trước bảng tính trong khung Before sheet.
Nếu thực hiện sao chép thì đánh dấu chọn vào Create a copy.
CÁC THAO TÁC VỚI BẢNG TÍNH
8. Tách 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, trên menu Window chọn mục Split. Để bỏ tách thì trên menu Window chọn Remove Split.
Cách 3: Đưa con trỏ ô về vị trí cần tách, trên menu Window chọn mục Freeze Panes. Để bỏ tách thì trên menu Window chọn Unfreeze Panes.
CÁC THAO TÁC VỚI BẢNG TÍNH
9. Ẩn và hiện lại bảng tính
Trên menu Format chọn Sheet/Hide để ẩn bảng tính.
Để hiện lại bảng tính, trên menu Format chọn Sheet/Unhide… và chọn sheet cần hiển thị trong hộp thoại Unhide, sau đó nhấn Enter.
CÁC THAO TÁC VỚI BẢNG TÍNH
10. Bảo vệ bảng tính
Cách làm như sau:
Trên menu Tools chọn Protection.
Chọn Protect Sheet… để bảo vệ bảng tính.
Chọn Protect Workbook… để bảo vệ tập các bảng tính.
Nếu cần thiết có thể gõ mật khẩu vào khung Password, hai 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ệ, trên menu Tools chọn Protection/Unprotect Sheet… hay Unprotect Workbook…. Nếu có mật khẩu, phải gõ vào, nếu gõ đúng ta mới cập nhật được bảng tính.
CÁC THAO TÁC VỚI BẢNG TÍNH
11. Chọn nhiều bảng tính
Liền kề: Click chuột vào thẻ tên đầu, giữ phím Shift trong khi click chuột vào thẻ cuối.
Cách nhau: Giữ Ctrl trong khi lần lượt click chuột vào các thẻ tên.
Để bỏ việc chọn một bảng tính nào đó, giữ Ctrl trong khi click chuột vào thẻ tên của bảng tính đó.
CÁC THAO TÁC VỚI BẢNG TÍNH
12. Đặt và thay đổi mật khẩu mở file
Một file sau khi tạo ra có thể được bảo vệ bằng mật khẩu, chỉ có người biết được từ khoá mới làm việc được với file này. Sau khi tạo xong bảng tính, trên menu File chọn Save As…, click chuột vào nút Tools, một menu tắt hiện ra, click chuột vào mục General Options…
Đánh mật khẩu vào mục Password to open, việc đặt mật khẩu này đòi hỏi mỗi khi cần mở file bạn phải đánh vào đúng mật khẩu đã đặt.
Password to open : mật khẩu chống việc mở file.
Password to modify: mật khẩu chống việc hiệu chỉnh file.
Gõ mật khẩu hai lần giống nhau sau đó nhấn OK và Save.
XỬ LÝ DỮ LIỆU VỚI BẢNG TÍNH
Dữ liệu trong các ô giống nhau
Đánh dấu khối miền nhập dữ liệu (ví dụ: A1:C5)
Gõ vào dữ liệu (ví dụ: Hello)
Kết thúc việc nhập bằng cách nhấn phím Ctrl + Enter
Khi đó toàn bộ miền nhập dữ liệu sẽ có giá trị giống nhau (cùng chữ Hello).
Điền chuỗi ngày tháng tăng dần
Đưa con trỏ về ô đầu tiên của miền, gõ vào ngày, tháng, năm bắt đầu.
Trỏ chuột vào góc phải bên dưới của ô để xuất hiện dấu +, bấm giữ chuột phải, kéo thả tại ô cuối của miền.
Một menu xuất hiện, chọn:
Copy Cells: Để điền dữ liệu giống nhau cho tất cả các ô trong miền.
Fill Series: Để tăng lên 1 ngày ở các ô kế tiếp.
Fill Formatting Only: Chỉ copy định dạng sang các ô khác trong miền mà không copy giá trị.
Fill Without Formatting: Điền giá trị cho các ô tiếp theo nhưng
không có định dạng. Ví dụ: Ô đầu tiên của miền có giá trị 5/21/2006
(định dạng ngày tháng), các ô kế tiếp sẽ là: 38859, 38860, 38861, … (không có định dạng).
Fill Days: Tăng lên 1 ngày ở các ô kế tiếp
Fill Weekdays: Tăng lên 1 ngày ở các ô kế tiếp
nhưng chỉ thuộc các ngày trong tuần (từ thứ 2
đến thứ 6).
Ví dụ: Ô đầu tiên của miền là 8/5/2006 (ứng với
thứ 7), các ô kế tiếp sẽ là 8/7/2006 (thứ 2),
8/8/2006 (thứ 3), …, 8/11/2006 (thứ 6), 8/14/2006
(thứ 2), …
Fill Months: Tăng lên 1 tháng (không tăng ngày) ở các ô kế tiếp.
Fill Years: Tăng lên 1 năm ở các ô kế tiếp (không tăng ngày tháng).
Đ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:
Vào menu Tools/Option/Custom List
Trong khung Custom lists bên trái chọn mục NEW LIST
Trong khung List entries bên phải lần lượt nhập các giá trị cho danh sách, hết mỗi giá trị nhấn Enter để xuống dòng.
Ví dụ : Thứ hai Thứ ba
Nhấn nút Add để đưa các phần tử trong khung List entries vào danh sách trong khung Custom lists.
Tiếp tục chọn mục NEW LIST khung
Custom lists để nhập tiếp các giá trị mới.
Nhấn OK để hoàn thành
Để sử dụng danh sách vừa tạo ra ta thực hiện
Nhập một giá trị có trong danh sách tự tạo vào ô đầu
Điền tự động tới ô cuối miền
Sửa, xóa dữ liệu
Xóa dữ liệu trong một ô hoặc một miền
Đưa con trỏ ô về ô hoặc chọn miền cần thiết.
Ấn phím Delete.
Sửa dữ liệu trong ô
Click đúp chuột tại ô (hoặc nhấ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 nhấn Enter) để ghi lại, chọn (hoặc nhấn Esc) để huỷ bỏ mọi sửa đổi.
Hoặc nhập dữ liệu mới cho ô cần sửa chữa, sau đó nhấn Enter.
Các thao tác với khối
Đánh dấu (chọn) khối
Giữ, kéo chuột. Hoặc:
Giữ phím Shift + các phím mũi tên.
Chú ý: Sau khi được chọn toàn bộ khối trừ ô ở góc xuất phát sẽ được đổi màu.
Các thao tác với khối
Một số cách chọn khối đặc biệt:
Chọn một cột: Click chuột tại tên cột đó (các chữ A, B,…) hoặc nhấn tổ hợp phím Ctrl + Space (dấu cách) tại vị trí của cột đó.
Chọn một hàng: Click chuột tại số thứ tự của hàng đó (các số 1, 2, …) hoặc nhấn tổ hợp phím Shift + Space (dấu cách) tại vị trí của dòng đó.
Chọn toàn bộ bảng tính: Click chuột tại 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 nhấn tổ hợp phím Ctrl + Shift + Space.
Các thao tác với khối
Chọn các ô rời rạc: Đưa con trỏ tới ô đầu cần chọn, giữ Ctrl và click chuột tại các ô cần 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, giữ chuột trái và rê chuột tại các miền khác.
Các thao tác với khối
Copy, cắt, dán khối dùng bộ nhớ đệm
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 nhấn tổ hợp phím Ctrl+C hoặc trên menu Edit chọn Copy.
Xoá: Chọn biểu tượng cut , hoặc nhấn tổ hợp phím Ctrl+X, hoặc trên menu Edit chọn 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 nhấn tổ hợp phím Ctrl+V hoặc trên menu Edit chọn Paste.
Các thao tác với khối
Copy, di 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 mũi tên 4 chiều . 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.
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 sau:
Các thao tác với khối
Move Here: di chuyển dữ liệu và định dạng.
Copy Here: copy toàn bộ, bao gồm cả dữ liệu và định
dạng.
Copy Here as Values Only: chỉ copy dữ liệu.
Copy Here as Formats Only: chỉ copy định dạng.
Link Here: Giá trị miền đích sẽ tham chiếu đến địa chỉ
miền nguồn.
Shift Down and Copy: Dịch chuyển các ô xuống dưới và thực hiện copy.
Shift Right and Copy: Dịch chuyển các ô sang bên phải và thực hiện copy.
Shift Down and Move: Dịch chuyển các ô xuống dưới và thực hiện di chuyển dữ liệu.
Shift Right and Move: Dịch chuyển các ô sang phải và thực hiện di chuyển dữ liệu.
Cancel: Hủy bỏ lệnh này.
Các thao tác với khối
Các dạng copy đặc biệt
Đánh dấu các ô cần copy.
Nhấn nút copy trên thanh công cụ (hoặc Ctrl+C hoặc vào menu Edit/Copy).
Nhấn chọn ô ở góc trái của vùng sẽ được dán.
Trên menu Edit chọn Paste Special …
Nhấn chọn lựa trong phần mục Paste và nhấn OK
Trong đó
All: dán toàn bộ nội dung và định dạng của ô
Formulas: chỉ dán công thức.
Values : chỉ dán giá trị được hiển thị trên ô.
Formats : chỉ dán định dạng của ô.
Comments : chỉ dán chú thích trong ô.
Validation : chỉ dán các quy định về điều kiện của dữ liệu.
All except borders: dán toàn bộ nội dung và định dạng trừ đường viền.
Column widths: chỉ dán chiều rộng của ô (lấy chiều rộng của ô nguồn)
Formulas and number formats: dán định dạng số và công thức.
Values and number formats: dán định dạng số và giá trị
Xử lý ô, cột, hàng trong bảng tính
Thay đổi kích thước cột, hàng
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 dấu mũi tên hai chiều , kéo và thả vạch đó tại vị trí mới. Nếu muốn độ rộng của cột chứa đủ dữ liệu có trong ô bất kỳ thuộc cột đó thì nhấn kép chuột vào mũi tên hai chiều ở trên.
Hàng: Trỏ chuột vào vạch ngang dưới số thứ tự hàng sao cho xuất hiện dấu mũi tên hai chiều thẳng đứng , kéo và thả vạch đó tại vị trí mới. Nếu muốn chiều cao của hàng chứa đủ dữ liệu có trong ô bất kỳ thuộc hàng đó thì nhấn kép chuột vào mũi tên hai chiều thẳng đứng ở trên.
Nhiều cột / nhiều hàng:
Cột:
Chọn một số ô của các cột
Vào menu Format chọn Colum->Width
Gõ vào độ rộng mới cho các cột
Nhấn OK hoặc Enter
Hàng:
Chọn một số ô của các hàng
Vào menu Format chọn Row->Height
Gõ vào chiều cao mới cho các hàng
Nhấn OK hoặc Enter
Chèn thêm cột, hàng, ô
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 menu Insert/Columns hoặc nhấn chuột phải và chọn Insert, Excel sẽ chèn thêm các cột trống và đẩy các cột được chọn sang phải.
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 menu Insert/Rows hoặc nhấn chuột phải và chọn Insert, Excel sẽ chèn thêm các hàng trống và đẩy các hàng được chọn xuống dưới.
Ô:
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 ô B3:C6), cần thêm bao nhiêu ô ta chọn bấy nhiêu.
Chọn menu Insert/Cells (hoặc nhấn chuột phải, chọn Insert…)xuất hiện hộp thoại Insert như sau:
Xoá cột, hàng, ô
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 xóa bao nhiêu cột ta chọn bấy nhiêu.
Trên menu Edit chọn Delete hoặc nhấn chuột phải và chọn Delete.
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 xóa bao nhiêu hàng ta chọn bấy nhiêu.
Trên menu Edit chọn Delete hoặc nhấn chuột phải và chọn Delete.
Ô:
Chọn khối là các ô cần xoá
Trên menu Edit chọn Delete… hoặc nhấn chuột phải và chọn Delete….
Xuất hiện hộp thoại Delete như sau:
Chuyển hàng thành cột và ngược lại
Chọn các ô muốn cho đổi chỗ hay còn gọi là miền nguồn (B3:B8)
Chọn biểu tượng Copy hoặc nhấn tổ hợp phím Ctrl+C
Đưa con trỏ về ô đầu tiên của miền đích (D3)
Vào menu Edit/Paste Special… (hoặc nhấn chuột phải và chọn Paste Special…), đánh dấu chọn ô Transpose sau đó nhấn chọn OK.
Khi đó ở miền nguồn sẽ có vòng nhấp nháy xung quanh, nhấn phím ESC để loại bỏ vòng nhấp nháy này. Không nên nhấn phím Enter vì sẽ có kết quả như sau:
Việc chuyển từ hàng sang cột cũng được thực hiện hoàn toàn tương tự.
Ẩn/hiện cột hàng
Chọn các cột (hoặc các hàng) cần ẩn đi.
Chọn menu Format/Column (hoặc Format/Row) rồi chọn mục 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, F. 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 menu Format/Column (hoặc Format/Row) rồi chọn mục Unhide.
7. Định dạng dữ liệu
Định dạng ký tự
Dùng thanh định dạng:
Dùng hộp định dạng:
Chọn ô hoặc vùng dữ liệu cần định dạng.
Chọn menu Format/Cells… (hoặc nhấn chuột phải và chọn Format Cells…, hoặc nhấn tổ hợp phím Ctrl+1) sau đó chọn tab Font bạn sẽ có hộp thoại Format Cells như sau:
Lưu ý: Nếu muốn hủy bỏ tất
cả các định dạng trên (màu chữ,
in đậm, gạch chân, …) thì đánh
dấu chọn mục Normal font trên
hộp thoại này.
Định dạng số
Dùng thanh công cụ:
Dùng hộp định dạng:
Trong hộp Category có các tùy
chọn cho định dạng số. Trong hộp
Sample bên cạnh hiển thị định
dạng mà bạn đã chọn tương ứng
với từng mục trong Category.
Mục Custom cho phép bạn tạo
ra các định dạng theo yêu cầu
riêng, ví dụ như mã sản phẩm
Canh biên (dóng hàng)
Dùng thanh công cụ
Dùng hộp định dạng
Horizontal: Canh lề theo chiều ngang
General (mặc định): Ký tự được canh lề
bên trái, số được canh sang phải, các giá
trị logic và lỗi được canh vào giữa.
Left, Center, Right: Dữ liệu tương ứng được canh lề trái, vào giữa, sang phải.
Fill: Tự điền đầy ô (trong ô chỉ cần gõ 1 ký tự hoặc 1 chuỗi ký tự sau đó chọn mục này, Microsoft Excel sẽ tự điền đầy ô hoặc vùng này bằng ký tự hoặc chuỗi ký tự được gõ vào). Tuy nhiên giá trị thực tế là giá trị mà ta đã gõ vào ban đầu.
Ví dụ: Giả sử tại ô B5 ta gõ vào số 15 sau đó định dạng Horizontal là Fill ta được chuỗi số 15151515. Tại ô B6 ta gõ công thức =B5+1 thì ta sẽ có kết quả là 16 chứ không phải là 15151516
Justiffy: Canh đều hai bên trái và phải. Trường hợp độ rộng của cột nhỏ hơn nội dung bên trong nó, Excel sẽ tự động tăng chiều cao của hàng.
Center Across Selection: Canh vào giữa vùng được chọn; tuy nhiên các ô này không trộn lại với nhau như khi ta chọn mục Merge and Center trên thanh định dạng.
Distributed: Được phân bố hợp lý nhất. Nếu dữ liệu trong ô là 1 từ thì nó được canh vào giữa. Nếu dữ liệu trong ô là 1 câu gồm nhiều từ thì dữ liệu được phân bố đều nhau. Tuy nhiên nếu độ rộng của cột nhỏ hơn nội dung bên trong nó thì Excel sẽ tự động tăng chiều cao của hàng cho hợp lý.
Hãy xem ví dụ sau đây:
Vertical: Canh lề theo chiều dọc
Top, Center, Justify: Dữ liệu tương ứng được canh lên trên, vào giữa và canh đều trên dưới theo chiều dọc của ô.
Bottom (mặc định): Dữ liệu được canh phía dưới cùng của ô.
Distributed: Lựa chọn Distributed cho phép phân bố dữ liệu hợp lý theo chiều cao của ô.
Nếu độ cao của dòng lớn hơn số dòng trong ô và độ rộng của cột chứa đủ nội dung dữ liệu thì dữ liệu được canh vào giữa ô.
Nếu độ cao của dòng lớn hơn số dòng trong ô và độ rộng của cột nhỏ hơn nội dung dữ liệu thì dữ liệu được phân bố đều nhau theo hai phía trên và dưới của ô.
Tuy nhiên nếu độ cao của dòng nhỏ hơn số dòng trong ô và độ rộng của cột nhỏ hơn nội dung dữ liệu thì Excel sẽ tự động cắt bỏ những dòng sau của dữ liệu.
Indents: Thụt lề vào
Thụt nội dung của ô hoặc vùng được đánh dấu từ bất kỳ cạnh nào của ô hoặc vùng, phụ thuộc vào tùy chọn của bạn ở mục Horizontal và Vertical. Mỗi khi tăng một giá trị trong ô Indent tương đương với chiều rộng của một ký tự.
Text Control
Wrap text: Dữ liệu trong ô tự động xuống hàng khi chiều dài dữ liệu lớn hơn chiều rộng cột. Nếu bạn muốn xuống hàng tại vị trí nào đó trong dữ liệu thì đặt con trỏ vào vị trí đó và nhấn tổ hợp phím Alt+Enter.
Shrink to fit: Tự động điều chỉnh kích cỡ chữ trong ô theo chiều ngang sao cho thấy được toàn bộ dữ liệu trong ô mà không cần phải điều chỉnh lại độ rộng cột. Sau đó, nếu tăng độ rộng cột thì kích cỡ chữ trong ô tự động tăng theo kích cỡ quy định ban đầu.
Merge cells: Trộn các ô trong khối được đánh dấu thành một ô.
Trong phần Orientation cho phép xác định góc quay của dữ liệu trông ô. Bạn có thể khai báo góc quay trong ô Degree hoặc di chuyển cây kim độ đến góc cần quay.
Text direction: Điều khiển hướng ký tự
Kẻ khung
Dùng thanh định dạng
Dùng hộp định dạng
None : Không có đường viền
Outline: Viền xung quanh khối hoặc ô
Inside: Viền lưới các ô bên trong
Left, Right, Top, Bottom : Viền trái, phải, trên, dưới mỗi ô
Color : Màu của đường viền
Style : Chọn kiểu đường viền
Tô màu nền cho các khối hoặc ô
Dùng thanh định dạng
Dùng hộp định dạng
Bạn chọn màu nền không mục Color.
Chọn kiểu dáng cho nền trong
mục Pattern.
Cuối cùng nhấn nút OK để chấp
nhận.
Định dạng tự động
Chọn vùng dữ liệu cần định dạng.
Vào menu Format/AutoFormat… Trong hộp thoại AutoFormat liệt kê một số mẫu định dạng có sẵn.
8. Đặt tên cho ô hoặc khối
Đặt tên cho ô hoặc khối bằng tay
Cách 1:
Nháy chuột tại nút tại ô Name Box trên thanh công thức.
Xóa địa chỉ hiện tại của ô hoặc khối, gõ tên cần đặt rồi nhấn phím Enter.
Cách 2:
Vào menu Insert, chọn mục Name/Define… (hoặc nhấn tổ hợp phím Ctrl+F3)
Trong khung Names in workbook của hộp thoại Define Name ta gõ vào tên cho ô hoặc khối đã chọn.
Sau đó nhấn nút Add bên cạnh để đưa tên mới đặt này vào danh sách.
Nhấn nút OK để chấp nhận hoặc Close để đóng hộp thoại này.
Đặt tên theo tiêu đề của cột hay hàng (tự động)
Chọn ô hay khối cần đặt tên gồm cả các tiêu đề hàng hoặc cột. Ví dụ: Đánh dấu khối B2:B10 là cột chứa MÃ_SỐ.
Vào menu Insert rồi chọn Name/Create…
Trong hộp thoại Create Names ta có các mục như sau:
Top row: Lấy ô ở hàng đầu (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
Bottom row: Lấy ô ở hàng cuố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
Nhấn nút OK để chấp nhận hoặc Cancel để hủy bỏ.
Kết quả khối B3:B10 có tên là MÃ_SỐ - là tiêu đề của cột này.
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
Nhấn phím F3 (hoặc nháy chuột tại nút Name Box trên thanh công thức, hoặc vào menu Insert, chọn mục Name/Paste…)
Chọn tên cần thiết từ danh sách rồi nhấn OK.
Về nhanh một ô đã đượ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 nút Name Box trên thanh công thức và chọn tên cần thiết từ danh sách (hoặc gõ địa chỉ của ô vào hộp này)
Cách 2: Nhấn phím F5. Chọn tên cần thiết từ danh sách và nhấn OK.
Đây cũng là phương pháp kiểm tra xem tên đã được đặt cho một ô hay miền hay chưa
Xoá tên
Vào menu Insert, chọn mục Name/Define… (hoặc nhấn tổ hợp Ctrl+F3)
Chọn tên cần xóa từ danh sách.
Nhấn nút Delete.
Nhấn nút OK hoặc Close.
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 ô
Ghi chú thích cho ô
Chuyển con trỏ về ô cần ghi chú thích
Mở hộp Comment theo một trong ba cách sau:
Cách 1: Nhấn tổ hợp phím Shift+F2.
Cách 2: Trên menu Insert chọn Comment
Cách 3: Nhấn chuột phải và chọn Insert Comment
Một Text Box có nền màu vàng (gọi là Comment: chú thích) xuất hiện bên cạnh ô cùng với nút màu đỏ ở phía trên bên phải của ô (gọi là Indicator: dấu chỉ thị).
Gõ vào lời ghi chú vào ô Comment đó.
Nếu không thấy dấu chỉ thị này hãy làm như sau:
Vào menu Tools chọn Options rồi chọn tab View.
Trong phần Comments, chọn:
None: Ẩn cả dấu chỉ thị lẫn chú thích.
Comment indicator only: Luôn luôn hiển thị dấu chỉ thị, phần chú thích chỉ hiển thị khi rê chuột đến ô đó.
Comment & indicator: Luôn luôn hiển thị cả phần chú thích và dấu chỉ thị.
Nhấn nút OK để chấp nhận.
Sửa/Xoá các chú thích
Để sửa hay xóa một chú thích:
Chuyển con trỏ về ô chú thích
Trên menu Insert chọn Edit Comment
Hoặc:
Bấm chuột phải vào ô có chú thích, chọn Edit Comment để sửa hoặc Delete Comment để xóa chú thích.
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 sửa đổi nội dung hay xem công thức ở một số ô khác, … 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 khối cần bảo vệ
Ở menu Format chọn Cells, trong hộp thoại Format Cells chọn tab Protection.
Trong hộp thoại này đánh dấu chọn vào:
Locked: để khoá không cho sửa đổi dữ liệu
Hidden: để che dấu công thức
Nhấn OK để chấp nhận.
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 26
HÀM TRONG EXCEL
1. Quy tắc sử dụng hàm
Các hàm có dạng tổng quát:
TÊN_HÀM([Các đối số]).
Ví dụ:
TODAY(): cho kết quả là ngày hiện tại trong máy (hàm không đối số)
LEN (“Excel 5.0”): cho kết quả độ dài của chuỗi là 9 (hàm 1 đối số)
AVERAGE(A1,B5,D8): cho kết quả là trung bình cộng các số trong các ô A1, B5 và D5 (hàm nhiều đối số)
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 đối số 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 tùy theo cách đặt các thông số quốc tế. Trong một hàm có thể chứa nhiều nhất 30 đối số 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 đối số cho một hàm khác (hàm lồng nhau, nhiều nhất là 7 mức) thì 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 của tam giác đó.
Ở đâ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 đối số (đối số) cho hàm SQRT.
2. Nhập hàm vào bảng tính
Gõ từ bàn phím
Gõ dấu =
Gõ vào tên hàm, dấu (, các đối số theo đúng định dạng quy định, dấu)
2. Nhập hàm vào bảng tính
Dùng biểu tượng Insert Function
Bước 1:
Đặt con trỏ tại ô cần nhập công thức.
Chọn biểu tượng trên thanh công thức. Xuất hiện hộp thoại Insert Function 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 học 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, …
Bước 2:
Chọn nhóm hàm trong khung Or select a 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 Select a function.
Bước 3:
Chọn hàm thích hợp trong khung Select a function.
Bước 4:
Nhấn Chọn OK. Xuất hiện hộp thoại Function Arguments liệt kê ô nhập các đối số, công dụng của hàm khung chứa giá trị kết quả của hàm.
Điền các đối số của hàm bằng cách nhấ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: Nhấn chọn OK, Excel sẽ 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 Arguments, Excel sẽ chuyển sang việc chỉnh sửa hàm.
Dùng menu
Trên menu Insert chọn mục Function. Xuất hiện hộp thoại Insert Function.
Các bước còn lại giống như trên.
3. Một số hàm thông dụng
Hàm ngày tháng
a. Hàm DATE
Cú pháp: DATE(year, month, day)
Ví dụ:
=DATE(2006, 09, 10) trả về ngày 10-09-2006.
b. Hàm DATEVALUE
Cú pháp: DATEVALUE(date_text)
Ví dụ:
=DATEVALUE(“9/10/2006”) trả về giá trị 38970.
=DATEVALUE(“10-Sep-2006”) trả về giá trị 38970.
Lưu ý:
Để xem ngày dạng số của ví dụ trên, click chuột phải tại ô đó và chọn Format Cells…. Chọn tab Number, trong hộp Category chọn mục Date rồi click OK.
c. Hàm DAY
Cú pháp: DAY(serial_number)
Ví dụ:
=DAY(“10-Sep-2006”) trả về giá trị là ngày 10.
d. Hàm MONTH
Cú pháp: MONTH(serial_number)
Ví dụ:
=MONTH(“10-Sep-2006”) trả về giá trị là tháng 9.
i. Hàm YEAR
Cú pháp: YEAR(serial_number)
Trong đó:
serial_number là một số theo định dạng ngày-tháng của MS Excel.
Ý nghĩa:
Trả về một số chỉ năm, có giá trị từ 1900 đến 9999.
Ví dụ:
=YEAR(“15-Sep-2006”) trả về giá trị là năm 2006.
e. Hàm NOW
Cú pháp: NOW()
Ví dụ:
=NOW() trả về giá trị 9/10/2006 20:02
Lưu ý: Hàm này không có đối số.
f. Hàm TIME
Cú pháp: TIME(hour, minute, second)
Ví dụ:
=TIME(6,30,50) trả về giá trị 6:30:50 AM.
=TIME(16,30,50) trả về giá trị 4:30:50 PM.
g. Hàm TODAY
Cú pháp: TODAY()
Ý nghĩa:
Trả về ngày hiện tại của hệ thống theo định dạng ngày-tháng.
Ví dụ:
= TODAY() trả về giá trị 9/10/2006.
Lưu ý: Hàm này không có đối số
h. Hàm WEEKDAY
Cú pháp:
WEEKDAY(serial_number, [return_type])
Ví dụ:
=WEEKDAY(“9/10/2006”,3) trả về giá trị 6 tương ứng với ngày Chủ nhật.
Lưu ý:
Nếu giá trị return_type để trống thì mặc định nó là giá trị 1.
Ví dụ:
=WEEKDAY(“9/10/2006”) trả về giá trị 1 tương ứng với ngày Chủ nhật.
3.2 Hàm ký tự
a. Hàm CONCATENATE
Cú pháp: CONCATENATE(text1, [text2], [text3], …)
Ý nghĩa: Nối nhiều chuỗi lại với nhau thành một chuỗi ký tự duy nhất.
Ví dụ:
=CONCATENATE(“Hôm nay ”, “là ngày”, “ 15/09/2006”) trả về chuỗi “Hôm nay là ngày 15-09-2006”.
=CONCATENATE(“Địa chỉ: ”, 2, “ Lê Lợi”) sẽ trả về chuỗi “Địa chỉ: 2 Lê Lợi”.
b. Hàm EXACT
Cú pháp: EXACT(text1, text2)
Ý nghĩa: Kiểm tra xem hai chuỗi text1 và text2 có giống hệt nhau hay không. Nếu giống nhau, hàm trả về giá trị TRUE, ngược lại trả về giá trị FALSE. Hàm này có phân biệt chữ HOA và chữ thường.
Ví dụ: =EXACT(“MS Excel”, “MS EXCEL”) trả về giá trị FALSE.
=EXACT(“MS EXCEL”, “MS EXCEL”) trả về giá trị TRUE.
c. Hàm FIND
Cú pháp: FIND(find_text, within_text, [start_num])
Ý nghĩa: Trả về vị trí bắt đầu của chuỗi find_text trong chuỗi within_text. Hàm này phân biệt chữ HOA và chữ thường.
Ví dụ: =FIND(“e”,“MS Excel”) trả về giá trị 7.
=FIND(“E”,“MS Excel”) trả về giá trị 4.
=FIND(“chào”, “Xin chào bạn”,10) trả về giá trị #VALUE! (lỗi không tìm thấy giá trị).
d. Hàm FIXED
Cú pháp: FIXED(number, [decimals], [no_commas])
Ý nghĩa: Làm tròn số number đến decimals số thập phân. Kết quả trả về là một chuỗi số có hoặc không có dấu phẩy phân tách hàng ngàn.
Ví dụ:=FIXED(1234.567,1) sẽ trả về kết quả là 1,234.6.
=FIXED(1234.567,-1) sẽ trả về kết quả là 1,230.
=FIXED(1234.567,-1,TRUE) sẽ trả về kết quả là 1230.
=FIXED(44.332) sẽ trả về kết quả là 44.33.
Lưu ý: Kết trả về ở các ví dụ trên đều là các chuỗi số (nghĩa là nó tự động căn trái trong ô)
e. Hàm LEFT
Cú pháp: LEFT(text, [num_chars])
Ý nghĩa:
Trả về chuỗi con có num_chars ký tự trong chuỗi văn bản text tính từ trái qua.
Ví dụ:
=LEFT(“Microsoft Excel”, 9) trả về chuỗi “Microsoft”.
=LEFT(“A123”) trả về ký tự “A”.
f. Hàm LEN
Cú pháp: LEN(text)
Trong đó:
text là chuỗi văn bản có độ dài cần đếm. Các dấu cách (spaces) cũng được tính như các ký tự.
Ý nghĩa:
Trả về số lượng các ký tự có trong chuỗi text.
Ví dụ:
=LEN(“Đại học Huế”) trả về giá trị 11.
g. Hàm LOWER
Cú pháp: LOWER(text)
Ý nghĩa: Chuyển tất cả các ký tự trong chuỗi văn bản text thành in thường.
Ví dụ:
=LOWER(“HAPPY NEW YEAR”) trả về chuỗi “happy new year”.
=LOWER(“Xin CHÀO BẠN”) trả về chuỗi “xin chào bẠn”.
Lưu ý: Một số từ tiếng Việt có dấu có thể không chuyển đổi thành công như trong ví dụ thứ hai ở trên.
h. Hàm MID
Cú pháp: MID(text, start_num, num_chars)
Ý nghĩa:
Trả về một chuỗi con trong chuỗi text tính từ vị trí start_num và có num_chars ký tự.
Ví dụ:
=MID(“Xin chào bạn”, 5, 4) trả về chuỗi “chào”.
i. Hàm PROPER
Cú pháp: PROPER(text)
Ví dụ:
=PROPER(“danh sách Học viên”) trả về chuỗi “Danh Sách Học Viên”.
=PROPER(“Microsoft EXCEL”) trả về chuỗi “Microsoft Excel”.
Lưu ý: Đối với tiếng Việt có dấu thì một số ký tự có thể không được chuyển đổi thành công.
Ví dụ:
=PROPER(“KẾt quẢ học tậP”) trả về chuỗi “KẾt QuẢ Học Tập”.
j. Hàm REPLACE
Cú pháp:
REPLACE(old_text, start_num, num_chars, new_text)
Ý nghĩa:
Thay thế một phần chuỗi văn bản old_text bởi một chuỗi văn bản mới là new_text.
Ví dụ:
=REPLACE(“Trung tâm CNTT”,11,4,“Công nghệ Thông tin”) trả về chuỗi “Trung tâm Công nghệ Thông tin”.
k. Hàm REPT
Cú pháp: REPT(text, number_times)
Trong đó:
text là chuỗi văn bản cần lặp lại.
number_times là số lần lặp lại của chuỗi text.
Ý nghĩa: In lặp lại chuỗi text đến number_times lần.
Ví dụ:
=REPT(“Vui quá! ”,3) trả về chuỗi “Vui quá! Vui quá! Vui quá! ”.
l. Hàm RIGHT
Cú pháp: RIGHT(text, [num_chars])
Ý nghĩa:
Trả về chuỗi con có num_chars ký tự trong chuỗi văn bản text tính từ phải qua.
Ví dụ:
=RIGHT(“Microsoft Excel”, 5) trả về chuỗi “Excel”.
=RIGHT(“A123”) trả về ký tự “3”.
m. Hàm SEARCH
Cú pháp:
SEARCH(find_text, within_text, [start_num])
Ý nghĩa: Trả về vị trí bắt đầu của chuỗi find_text trong chuỗi within_text. Hàm này không phân biệt chữ HOA và chữ thường.
Ví dụ:
=SEARCH(“e”,“MS Excel”) trả về giá trị 4.
=SEARCH(“e”,”MS Excel”,5) trả về giá trị 7.
=SEARCH(“~*”, “Ngôi * cô đơn”,1) trả về giá trị 6.
=SEARCH(“*”, “Ngôi * cô đơn”,1) trả về giá trị 1.
n. Hàm SUBSTITUTE
Cú pháp:
SUBSTITUTE(text, old_text, new_text, [instance_num])
Ý nghĩa: Thay thế chuỗi old_text xuất hiện trong chuỗi text bởi chuỗi new_text tại vị trí instance_num.
Ví dụ:
=SUBSTITUTE(“Hãy xem xem”, “xem”, “nhìn”,1) trả về chuỗi “Hãy nhìn xem”.
=SUBSTITUTE(“Hãy Xem xem”, “xem”, “nhìn”,1) trả về chuỗi “Hãy Xem nhìn”. (Chữ “Xem” thứ nhất không được thay thế).
=SUBSTITUTE(“Hãy xem xem”, “xem”, “đây”, 2) trả về chuỗi “Hãy xem đây”.
o. Hàm T
Cú pháp: T(value)
Ý nghĩa: Kiểm tra xem giá trị value có phải là một chuỗi văn bản hay không. Nếu là chuỗi văn bản thì kết quả sẽ trả về chuỗi đó, ngược lại thì sẽ trả về một chuỗi rỗng.
Ví dụ: =T(“Chào buổi sáng”) trả về chuỗi “Chào buổi sáng”.
=T(123) trả về chuỗi rỗng vì đây là một số.
=T(“123”) trả về chuỗi “123”.
=T(TRUE) trả về chuỗi rỗng vì đây là giá trị logic.
p. Hàm TEXT
Cú pháp: TEXT(value, format_text)
Ý nghĩa:
Chuyển một số sang dạng text (văn bản) theo định dạng được chỉ định.
Ví dụ:
=TEXT(0.5, “0%”) trả về chuỗi “50%”.
q. Hàm TRIM
Cú pháp: TRIM(text)
Ý nghĩa:
Xóa tất cả các khoảng trắng dư thừa bên trái, bên phải và giữa các từ trong chuỗi text.
Ví dụ:
=TRIM(“ Xin chào bạn ”) trả về chuỗi “Xin chào bạn”.
r. Hàm UPPER
Cú pháp: UPPER(text)
Ý nghĩa:
Chuyển tất cả các ký tự trong chuỗi văn bản text thành in HOA.
Ví dụ:
=UPPER(“Xin chào bạn”) trả về chuỗi “XIN CHÀO BạN”.
Lưu ý: Một số từ tiếng Việt có dấu có thể không chuyển đổi thành công như trong ví dụ trên.
s. Hàm VALUE
Cú pháp: VALUE(text)
Ý nghĩa: Chuyển đổi chuỗi ký tự số thành một số.
Ví dụ:
Ô A3 có chuỗi “Trung tâm CNTT – SĐT: 054.828423” thì hàm
=VALUE(RIGHT(A3, 6)) trả về số 828423.
3.3 Hàm toán học
a. Hàm ABS
Cú pháp: ABS(number)
Ý nghĩa:
Trả về giá trị tuyệt đối của số number.
Ví dụ:
=ABS(-12) có kết quả là 12.
b. Hàm EXP
Cú pháp: EXP(number)
Trong đó:
number là số mũ của cơ số e. Hằng số e=2.71828182845904 là cơ số của logarit tự nhiên.
Ý nghĩa:
Trả về e lũy thừa number.
Ví dụ:
=EXP(1) trả về giá trị 2.718281828
c. Hàm INT
Cú pháp: INT(number)
Ý nghĩa:
Trả về số nguyên lớn nhất không nhỏ hơn number.
Ví dụ:
=INT(2.789) trả về kết quả là 2.
d. Hàm MOD
Cú pháp: MOD(number, divisor)
Ý nghĩa:
Trả về số dư của phép chia nguyên của số number cho divisor.
Ví dụ:
=MOD(11,3) trả về kết quả là 2.
e. Hàm ROUND
Cú pháp: ROUND(number, num_digits)
Ý nghĩa:
Làm tròn số number với độ chính xác đến con số thứ num_digits.
Ví dụ: Ô A1 chứa số 347645.546, khi đó:
= ROUND(A1,0) cho kết quả 347646
= ROUND(A1,2) cho kết quả 347645.55
=ROUND(A1,1) cho kết quả 347645.5
=ROUND(A1,-3) cho kết quả là 348000
f. Hàm SQRT
Cú pháp: SQRT(number)
Ý nghĩa:
Trả về giá trị căn bậc hai của số number
Ví dụ:
=SQRT(4) cho kết quả là 2.
g. Hàm SUM
Cú pháp: SUM(number1, [number2], …)
Ý nghĩa:
Tính tổng của dãy số number1, number2, …
Ví dụ:
=SUM(4, 5, 6) cho kết quả là 15
h. Hàm SUMIF
Cú pháp: SUMIF (range, criteria, [sum_range])
Trong đó:
range là vùng ô để so sánh với tiêu chuẩn (điều kiện) criteria.
criteria là điều kiện tính tổng, có thể là số, chữ hoặc biểu thức. Quyết định ô nào trong vùng sum_range sẽ được tính tổng.
sum_range là vùng sẽ được tính tổng. Các ô trong vùng sum_range chỉ được cộng vào nếu các ô tương ứng với nó trong range thỏa mãn điều kiện criteria.
Lưu ý: Hàm SUMIF chỉ tính tổng theo một điều kiện. Nếu cần tính tổng theo hai điều kiện trở lên phải dùng công thức mảng hoặc hàm DSUM (xem phần hàm cơ sở dữ liệu).
Ví dụ: Cho bảng tính trong ví dụ sau:
=SUMIF(D2:D11, “nữ”, H2:H11) cho kết quả là 1080000
i. Hàm TRUNC
Cú pháp: TRUNC(number, [num_digits])
Ý nghĩa: Lấy phần nguyên của số number bằng cách cắt xén phần thập phân, phần phân số hoặc một phần của nó.
Ví dụ:
=TRUNC(474.546,0) trả về kết quả 474.
=TRUNC(474.546,2) trả về kết quả 474.54 (trong khi đó hàm =ROUND(474.546,2) cho kết quả 474.55).
=TRUNC(474.546,-2) trả về kết quả 400.
3.4 Hàm logic
a. Hàm AND
Cú pháp: AND(logical1, [logical2],…)
Ý nghĩa:
Cho kết quả TRUE (đúng) nếu tất cả các biểu thức đều là TRUE, cho kết quả là FALSE (sai) nếu có ít nhất một biểu thức logic có giá trị là FALSE.
Ví dụ:
=AND(5>3, 6>4) cho kết quả là TRUE
=AND(5>3, 6<=4) cho kết quả là FALSE
b. Hàm IF
Cú pháp:
IF(logical_test, [value_if_true], [value_if_false])
Ý nghĩa: 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.
Ví dụ: Ô A2 có giá trị 50. Khi đó:
=IF(A2<=100, “Nằm trong kế hoạch”, “Vượt kế hoạch”) sẽ trả về giá trị là chuỗi “Nằm trong kế hoạch”.
=IF(A2=100, SUM(B5:B15), “”) sẽ trả về chuỗi rỗng “”
c. Hàm NOT
Cú pháp: NOT(logical)
Ý nghĩa:
Thay đổi giá trị TRUE thành FALSE và ngược lại.
Ví dụ:
=NOT(1+1>2) trả về TRUE
=NOT(TRUE) trả về FALSE
d. Hàm OR
Cú pháp: OR(logical1, [logical2], …)
Ý nghĩa:
Cho kết quả là TRUE (đúng) nếu một trong các biểu thức logic có giá trị là TRUE, cho kết quả là FALSE (sai) nếu tất cả các biểu thức là FALSE.
Ví dụ:
=OR(LEFT(“Welcome”,2)=“We”, LEN(“Welcome”)<>7) trả về TRUE
=OR(5=3, LEN(“Welcome”)<>7) trả về FALSE
3.5 Hàm thống kê
a. Hàm AVERAGE
Cú pháp: AVERAGE(number1, [number2], …)
Ý nghĩa:
Tính trung bình cộng của các số number1, number2, …. Đối số của hàm AVERAGE có thể là số hoặc địa chỉ của các ô chứa giá trị số.
Ví dụ:
=AVERAGE(5, 8, 2) có kết quả là 5.
b. Hàm COUNT
Cú pháp: COUNT(value1, [value2], …)
Trong đó:
value1, value2, … là các giá trị hoặc tham chiếu đến địa chỉ các ô chứa dữ liệu. Nhưng chỉ những giá trị số mới được đếm. Có tối đa 30 đối số.
Ý nghĩa:
Đếm số lượng các ô chứa dữ liệu kiểu số và các số có mặt trong danh sách các đối số.
Ví dụ:
=COUNT(3, “Good morning”, 0, “”) trả về giá trị 2.
c. Hàm COUNTA
Cú pháp: COUNTA(value1, [value2], …)
Trong đó:
value1, value2, … là các giá trị hoặc tham chiếu đến địa chỉ các ô chứa bất kỳ dữ liệu nào. Có tối đa 30 đối số.
Ý nghĩa:
Tương tự hàm COUNT ở trên nhưng hàm này có thể đếm bất kỳ kiểu dữ liệu nào.
Ví dụ:
=COUNTA(3, “Good morning”, 0, “”) trả về giá trị 4.
d. Hàm COUNTBLANK
Cú pháp: COUNTBLANK(range)
Trong đó:
range là miền cần đếm các ô rỗng.
Ý nghĩa:
Đếm số lượng các ô rỗng (không chứa dữ liệu) trong miền range.
e. Hàm COUNTIF
Cú pháp: COUNTIF(range, criteria)
Trong đó: range là miền chứa các ô có dữ liệu cần đếm.
criteria là điều kiện của các số, biểu thức hoặc chuỗi văn bản.
Ý nghĩa: Đếm các ô trong miền range thỏa mãn điều kiện criteria.
Lưu ý: Hàm COUNTIF chỉ đếm theo một điều kiện. Nếu cần đếm kết hợp nhiều hơn một điều kiện thì phải dùng hàm DCOUNT (xem phần hàm cơ sở dữ liệu).
Ví dụ:
Cho ví dụ như hình bên dưới, khi đó: =COUNTIF(B3:B6, “apples”) cho kết quả là 2.
f. Hàm LARGE
* Một số tài liệu cũ có thể bị lỗi font khi hiển thị do dùng bộ mã không phải Unikey ...
Người chia sẻ: Nguyễn Trường Vĩnh
Dung lượng: 3,72MB|
Lượt tài: 0
Loại file: ppt
Nguồn : Chưa rõ
(Tài liệu chưa được thẩm định)