Bài giảng excel

Microsoft Excel - Microsoft Excel được sử dụng là phần mềm bảng tính điện tử mạnh nhất, phức tạp nhất đồng thời có nhiều ứng dụng nhất hiện nay. - Ngoài ra Microsoft Excel còn được sử dụng để quản trị cơ sở dữ liệu. - Phiên bản mới nhất hiện nay của Excel là Microsoft Excel XP, nó đáp ứng được nhiều hơn nhu cầu của người sử dụng đồng thời bổ sung các công cụ mới với những tính năng mạnh mẽ và thuận tiện, các thao tác đơn giản đỡ rắc rối hơn trong công thức và in ấn. Bài 1: cách quản lý dữ liệu trong Microsoft Excel 1/ Khởi động và thoát khỏi Microsoft Excel: - Khởi động: Tìm đến biểu tượng Microsoft Excel và chạy chương trình. - Thoát khỏi: Chọn chọn Menu File và chọn Exit. 2/ Hệ thống bản chọn trong Microsoft Excel: Sau khi khởi động cửa sổ Microsoft Excel xuất hiện có đầy đủ các thuộc tính của một cửa sổ trong môi trường Window. - Thanh tiêu đề (Tile bar) - Bảng chọn chính (Menu bar) - Thanh công cụ (Tool bar) - Thanh thanh công thức (Formula bar) - Bảng tính (Workbook) - Thanh cuốn màn hình (Scroll bar) - Thanh trạng thái (Status) 3/ Cách tổ chức trong Microsoft Excel: Để quản lý tốt dữ liệu Microsoft Excel chia ra các thành phần: - Một File trong Microsoft Excel được gọi là một Workbook. - Trong một Workbook chứa nhiều bảng tính, mỗi bảng tính được gọi là một WorkSheet. - Trong một WorkSheet chứa các cột và các hàng, giao điểm giữa các cột và các hàng được tạo thành địa chỉ những ô dữ liệu. - Cách xác định địa chỉ ô dữ liệu: Ta chiếu từ cột sang hàng, giao điểm giữa các đường lưới của cột và hàng tạo thành địa chỉ ô dữ liệu. Ví dụ: B5 (cột B và hàng thứ 5). 4/ Kiểu dữ liệu và các toán tử trong Microsoft Excel: - Để quản lý tốt dữ liệu Microsoft Excel chia ra các kiểu dữ liệu để quản lý: có 5 kiểu dữ liệu chính: + Dữ liệu dạng ký tự (Text): Dữ liệu dạng này tự động căn lề trái ô dữ liệu sau khi nhập, trong trường hợp ô dữ liệu không đủ độ rộng để chứa thì dữ liệu tạm thời tràn sang các ô tiếp theo nếu các ô kế bên chưa có dữ liệu hoặc tạm thời khuất đi nếu các ô kế bên đã có dữ liệu. + Dữ liệu dạng số (Number): Dữ liệu dạng này tự động căn lề phải ô dữ liệu sau khi nhập, trong trường hợp ô dữ liệu không đủ độ rộng để chứa thì dữ liệu tạm thời để dưới dạng khoa học (Scientific) + Dữ liệu dạng ngày tháng năm (Date): Được nhập theo dạng ngày tháng định dạng trong Control Panel. Dữ liệu dạng này tự động canh sang phải ô dữ liệu sau khi nhập, trong trường hợp ô dữ liệu không đủ độ rộng để chứa thì dữ liệu tạm thời để dưới ### + Dữ liệu dạng tiền tệ (Currency): Được định dạng theo dạng tiền tệ trong Control Panel. + Dữ liệu dạng công thức (Formula): Được bắt đầu bởi dấu “=” sau đó đến các phép toán. - Các toán tử và phép tính trong Microsoft Excel: > lớn hơn  + Cộng dữ liệu   < nhỏ hơn  - Trừ dữ liệu   >= lớn hơn hoặc bằng  * Nhân dữ liệu   <= nhỏ hơn hoặc bằng  / Chia dữ liệu.   khác  ^ luỹ thừa   = bằng  % phần trăm   5/ Thao tác lựa chọn vùng, nhập dữ liệu cho bảng tính: 5.1/ Thao tác chọn vùng dữ liệu: - Nếu chọn 1 ô duy nhất thì bấm chuột vào ô cần chọn. - Nếu chọn nhiều ô dữ liệu liền kề nhau thì bấm giữ chuột tại ô đầu tiên và kéo lê đến ô cuối cùng thì nhả chuột. - Nếu chọn nhiều vùng dữ liệu cách nhau: Bấm chọn bình thường nhưng từ vùng thứ 2 trở đi phải bấm giữ Ctrl trong quá trình lựa chọn. - Nếu chọn cả cột hay cả hàng thì bấm chuột vào tên cột hoặc tên hàng (bấm giữ chuột vào kéo lê nếu chọn nhiều). - Nếu chọn cả bảng tính thì bấm Ctrl + A 5.2/ Nhập dữ liệu: - Bấm chọn ô cần nhập dữ liệu - Đưa dữ liệu vào từ bàn phím - Kết thúc có các hướng lựa chọn: + Bấm Enter, Tab, các phím mũi tên hoặc bấm chuột sang ô khác để xác nhận nhập và chuyển con trỏ sang ô tiếp theo. + Bấm Esc để huỷ nhập. 5.3/ Thao tác sửa dữ liệu: Di chuyển hộp sáng tới ô cần sửa dữ liệu, sau đó nhấn phím F2, sửa dữ liệu và kết thúc bằng cách nhấn phím Enter. 6/ Thao tác quản lý File: 6.1/ Các thành phần cơ bản: Windows quản lý File nói chung bằng cách đưa ra các khái niệm: + Trạm làm việc (Trường hợp trong mạng) + ổ đĩa + Thư mục + File Khi nói đến một file cần chỉ ra nơi cất giữ file VD: C:\Baihoc\Excel.xls - File có 2 thành phần: + Tên file + Kiểu file 6.2/ Thao tác ghi lại một File với tên mới: Chọn menu File, sau đó chọn mục Save As màn hình xuất hiện: - Tại mục Save in: Chọn ổ đĩa, thư mục sẽ chứa File. - Tại mục File Name: Nhập tên File cần lưu. - Bấm chọn nút Save. Trong Microsoft Excel sau khi lưu tên tệp tạo thành có phần mở rộng *.XLS 6.3/ Thao tác lưu tệp với tên cũ: - Vào menu File - Chọn Save Hoặc nhấn tổ hợp phím Ctrl + S - Bản chất của thao tác là ghi lại toàn bộ nội dung File hiện có lên đĩa nhằm tránh trường mất dữ liệu do máy bị sự cố khi đang làm việc. Thao tác này cần thực hiện thường xuyên (5 - 10 phút / lần) 6.4/ Thao tác mở File mới: - Vào menu File - Chọn mục New - Chọn WorkSheet - Chọn OK 6.5/ Thao tác mở File đã có tên: Vào menu File Chọn Open (hoặc nhấn tổ hợp phím Ctrl + O) màn hình xuất hiện: - Tại mục Look in: Chọn ổ đĩa hoặc thư mục có chứa File cần mở. - Tại màn hình hiển thị: Kích c huột chọn tên File cần mở. - Bấm chọn nút Open * Chú ý: Trong Microsoft Excel cùng lúc có thể mở được nhiều File, do đó để chuyển đổi giữa các File đang được mở ta chọn menu Window và chọn tên File cần chuyển tới. 6.6/ Thao tác đóng một File: - Đảm bảo File cần đóng đang được làm việc trực tiếp (Active File) - Vào menu File - Chọn Close Hoặc nhấn tổ hợp phím Ctrl + W 6.7/ Tìm kiếm một file: - Chọn Find trong nút Start Menu - Vào tên file cần tìm tại mục Name, có thể sử dụng dấu * để bỏ qua những kí tự không quan tâm, VD: *.tmp, *.* - Vào đoạn văn bản chứa trong file tại mục Containing text - Chọn phạm vi tìm ở mục Look in. - Bấm nút Find Now Danh sách các file tìm được sẽ được hiện ra. Ta có thể lựa chọn để xoá hoặc copy.... Bài 2: Thao tác với dữ liệu 1/ Thao tác sửa, nhập dữ liệu trên nhiều dòng và nhập dữ liệu đặc biệt: 1.1/ Thao tác sửa dữ liệu: - Bấm chọn ô dữ liệu cần sửa - Sau đó nhấn phím F2. - Sửa dữ liệu, kết thúc nhấn phím Enter. 1.2/ Thao tác nhập dữ liệu trên nhiều dòng: - Lựa chọn ô cần nhập dữ liệu, - Nhập dữ liệu bình thường - Kết thúc một dòng ta nhấn tổ hợp phím Alt + Enter. - Kết thúc nhập dữ liệu nhấn phím Enter. 1.3/ Nhập dữ liệu đồng loạt: - Chọn vùng dữ liệu cần nhập (chọn nhiều ô), - Nhập dữ liệu bình thường - Kết thúc bấm tổ hợp phím Ctrl + Enter. 2/ Thao tác Copy - dán: - Thao tác Copy: Lựa chọn vùng dữ liệu cần sao chép, sau đó chọn menu Edit chọn Copy (hoặc nhấn tổ hợp phím Ctrl +C). - Thao tác dán: Lựa chọn vị trí cần dán, lựa chọn menu Edit chọn mục Paste (hoặc nhấn tổ hợp phím Ctrl + V). 3/ Thao tác cắt - dán: - Thao tác cắt: Lựa chọn vùng dữ liệu cần cắt, sau đó chọn Menu Edit chọn Cut (hoặc nhấn tổ hợp phím Ctrl + X). - Thao tác dán: Lựa chọn vị trí cần dán, sau đó chọn menu Edit chọn mục Paste (hoặc nhấn tổ hợp phím Ctrl + V). 4/ Thao tác tìm kiếm dữ liệu: Lựa chọn menu Edit chọn Find màn hình xuất hiện: Find What: Ta nhập từ cần tìm kiếm. Kích chuột vào nút Find Next. 5/ Thao tác tự động tìm kiếm và thay thế: Lựa chọn menu Edit chọn Replace màn hình xuất hiện: - Find What: Nhập từ cần tìm kiếm. - Replace With: Nhập từ cần thay thế - Lựa chọn nút Find Next để tìm kiếm. - Lựa chọn nút Replace để thay thế. 6/ Thao tác xóa dữ liệu: + Lựa chọn vùng bảng tính cần xoá dữ liệu + Bấm phím Delete 7/ Thao tác tham chiếu hoặc Copy: - Mục đích: Khi tham chiếu toạ độ ô dữ liệu cũng được thay đổi theo quá trình tham chiếu. - Thao tác: Di chuyển trỏ chuột tới góc dưới bên phải của ô dữ liệu, trỏ chuột biến thành dấu cộng đen, ta kích nhấn giữ và kéo rê. 8/ Thao tác chèn cột, hàng, vùng bảng tính và WorkSheet: - Mục đích: Chèn thêm cột, hàng, vùng bảng tính hoặc WorkSheet tại vị trí lựa chọn. - Thao tác chèn cột: Lựa chọn cột cần chèn lựa chọn menu Insert chọn mục Columns. - Thao tác chèn hàng: Lựa chọn hàng cần chèn, vào Menu Insert chọn Rows. - Thao tác chèn vùng bảng tính: Lựa chọn vùng bảng tính cần chèn, vào Menu Insert chọn Cells màn hình xuất hiện ta chọn mục cần chèn: + Shift Cell Right: Đẩy các ô dữ liệu sang bên phải. + Shift Cell Down: Đẩy các ô dữ liệu xuống phía dưới. + Entire Row: Đẩy tất cả các ô xuống dòng dưới + Entire Column: Đẩy tất cả các cột sang bên phải. - Thao tác chèn WorkSheet: Chọn vị trí Sheet cần chèn thêm Sheet mới, vào menu Insert chọn mục WorkSheet. 9/ Thao tác xoá cột, hàng và vùng bảng tính: - Thao tác xoá cột, hàng: Lựa chọn cột hoặc hàng cần xoá vào menu Edit chọn mục Delete. - Thao tác xoá vùng bảng tính: Lựa chọn vùng bảng tính cần chèn, vào menu Edit chọn Delete màn hình xuất hiện ta chọn mục cần chèn: + Shift Cell Right: Kéo các ô dữ liệu về bên trái. + Shift Cell Down: Kéo các ô dữ liệu lên phía trên. + Entire Row: Xoá tất cả dòng dữ liệu và kéo tất cả các ô dữ liệu lên dòng trên. + Entire Column: Xoá tất cả cột dữ liệu và kéo tất cả các cột dữ liệu về phía trái. - Thao tác xoá một Sheet: Lựa chọn Sheet cần xoá, vào menu Edit chọn mục Delete Sheet chọn nút OK. * Chú ý: Khi xoá một Sheet ta không sử dụng được lệnh khôi phục. 10/ Thao tác với hàng trong bảng tính: - Thao tác thay đổi độ rộng: Lựa chọn hàng cần thay đổi độ rộng, vào menu Format chọn mục Row chọn mục Height... sau đó nhập độ cao của hàng tại mục Row Height chọn OK. - Thao tác tự động căn vừa dữ liệu của hàng: Lựa chọn hàng cần tự động căn vừa vùng dữ liệu, sau đó vào menu Format chọn Row chọn mục AutoFit. - Thao tác ẩn hàng: Lựa chọn hàng cần ẩn, vào menu Format chọn Row chọn mục Hide. - Thao tác khôi phục hàng đã ẩn: Lựa chọn vị trí hàng cần khôi phục, vào menu Format chọn Row và chọn mục Unhide. 11/ Thao tác với cột trong bảng tính: - Thay đổi độ rộng bằng cách kéo chuột: Di chuyển trỏ chuột tới đường viền của cột hoặc dòng cần thay đổi độ rộng, khi đó trỏ chuột biến thành mũi tên 2 chiều ta nhấn giữ và kéo rê chuột. - Thao tác thay đổi độ rộng: Lựa chọn cột cần thay đổi độ rộng, vào menu Format chọn Column chọn mục Width. Tại mục Columns Width: Nhập độ rộng, sau đó chọn nút OK. - Thao tác tự động căn vừa dữ liệu của cột: Lựa chọn cột cần tự động căn vừa vùng dữ liệu, sau đó vào menu Format chọn Column chọn mục AutoFit. - Thao tác ẩn cột: Lựa chọn cột cần ẩn, vào menu Format chọn Column chọn mục Hide. - Thao tác khôi phục cột đã ẩn: Lựa chọn vị trí cột cần khôi phục ẩn, vào menu Format chọn Column và chọn mục Unhide. 12/ Thao tác với WorkSheet: - Thao tác đổi tên Sheet: Lựa chọn Sheet cần đổi tên, vào menu Format chọn Sheet chọn mục Rename. Gõ tên Sheet cần đổi và nhấn phím Enter. - Thao tác ẩn Sheet: Lựa chọn Sheet cần ẩn, vào menu Format chọn Sheet chọn mục Hide. - Thao tác khôi phục Sheet đã ẩn: Lựa chọn menu Format, chọn Sheet chọn mục Unhide màn hình xuất hiện ta kích chọn tên Sheet cần khôi phục ẩn sau đó chọn OK. - Thao tác đặt màu nền cho Sheet: Lựa chọn menu Format chọn Sheet chọn mục Background màn hình xuất hiện ta chọn ra ảnh cần làm màu nền cho Sheet, sau đó chọn Open. Bài 3: Thao tác định dạng bảng tính 1/ Thao tác định dạng dữ liệu: - Mục đích: Chọn kiểu dữ liệu cần sử dụng cho bảng tính khi nhập dữ liệu. - Thao tác: Lựa chọn vùng dữ liệu cần định dạng, sau đó kích chuột vào menu Format chọn Cells... (hoặc nhấn tổ hợp phím Ctrl + 1) màn hình xuất hiện: + Tại mục chọn Number ta chọn kiểu dữ liệu cần sử dụng tại mục Category: Dữ liệu kiểu ngầm định: chọn mục General Dữ liệu kiểu số: Chọn mục Number. Decimal places: Chọn số chữ số cho phần thập phân. Use 1000 Separater (.): sử dụng dấu chấm cho 1 nhóm 3 số Dữ liệu kiểu tiền tệ: Chọn mục Currency Decimal places: Chọn số chữ số cho phần thập phân. Symbol: Chọn kiểu tiền tệ cần sử dụng. Dữ liệu kiểu ngày tháng: Chọn mục Date và định dạng tại khung chọn Type. Dữ liệu kiểu giờ: Chọn mục Time và định dạng tại khung Type. Dữ liệu kiểu ký tự: Chọn mục Text. Dữ liệu kiểu tự định dạng: Chọn mục Custom và định dạng kiểu dữ liệu tự định nghĩa tại mục Type. Ví dụ: Định dạng kiểu ngày tháng năm theo dạng Việt: dd/mm/yyyy. 2/ Thao tác căn lề ô dữ liệu: - Mục đích: Chọn kiểu căn lề cho dữ liệu trong bảng tính. - Thao tác: Lựa chọn vùng dữ liệu cần định dạng, sau đó kích chuột vào menu Format chọn Cells... (hoặc nhấn tổ hợp phím Ctrl + 1) màn hình xuất hiện: + Tại phiếu chọn Alignment định dạng dữ liệu theo chiều ngang hoặc dọc trong bảng tính: Chọn mục Horizontal để điều chỉnh dữ liệu theo chiều ngang trong ô dữ liệu: General: Giữ nguyên dữ liệu khi nhập từ bàn phím. Left: Căn dữ liệu phẳng theo mép trái tại ô dữ liệu. Center: Căn dữ liệu giữa ô dữ liệu. Right: Căn dữ liệu phẳng theo mép phải ô dữ liệu. Fill: Điền đầy ô dữ liệu bởi dữ liệu có trong ô. Justify: Căn thẳng hai mép ô dữ liệu. Center acrross selection: Căn giữa vùng dữ liệu đã được lựa chọn. Wrap Text: Độ rộng cố định, dữ liệu nhập vào tự động tràn qua nhiều dòng. Meger cells: Nối các cột hoặc ô dữ liệu thành một ô dữ liệu. Chọn mục Vertical để điều chỉnh dữ liệu cho từng ô theo chiều dọc. Top: Căn dữ liệu lên trên đỉnh của ô. Bottom: Căn dữ liệu xuống dưới ô. Center: Căn dữ liệu lên giữa ô. Justify: Căn đều dòng dữ liệu (Nếu dòng trong ô bao gồm nhiều dòng). Quay nghiêng dữ liệu trong ô tính tại mục Orientation: chọn góc quay tại mục Degrees. 3/ Thao tác định dạng phông chữ: - Mục đích: Chọn kiểu phông chữ cần sử dụng. - Thao tác: Lựa chọn vùng dữ liệu cần định dạng, sau đó kích chuột vào menu Format chọn Cells... (hoặc nhấn tổ hợp phím Ctrl + 1) màn hình xuất hiện: + Tại phiếu chọn Font định dạng phông chữ cho dữ liệu: Font: Chọn phông chữ. Font Style: Chọn kiểu chữ. Size: Chọn kích thước chữ. Underline: Chọn kiểu gạch chân. Color: Chọn màu chữ . Normal font: Chọn kiểu phông chữ ngầm định. Effect: chọn kiểu cần sử dụng: Strikethrough: Kiểu chữ bị ngạch ngang. Superscrip: Chữ chỉ số trên (x2). Subscrip: Chữ chỉ số dưới (x2). 4/ Thao tác định dạng khung bảng tính: - Mục đích: Định dạng khung cho bảng tính đang lựa chọn. - Thao tác: Lựa chọn vùng dữ liệu cần định dạng, sau đó kích chuột vào menu Format chọn Cells... (hoặc nhấn tổ hợp phím Ctrl + 1) màn hình xuất hiện: + Tại phiếu chọn Border định dạng khung bảng tính: None: Không hiển thị khung bảng tính. Outline: Định dạng được viền ngoài cho khung bảng tính. Inside: Định dạng đường viền trong cho khung bảng tính. Border: Thay đổi các đường trong bảng tính. Style: Chọn kiểu đường cần sử dụng trong khung. Color: Chọn màu sử dụng trong khung. 5/ Thao tác định dạng nền dữ liệu: - Mục đích: Chọn kiểu màu nền cho dữ liệu cần sử dụng cho bảng tính. - Thao tác: Lựa chọn vùng dữ liệu cần định dạng, sau đó kích chuột vào menu Format chọn Cells... (hoặc nhấn tổ hợp phím Ctrl + 1) màn hình xuất hiện: + Tại phiếu chọn Patterns: Chọn màu nền cho bảng tính tại mục Color. - Kích chuột vào OK để kết thúc quá trình định dạng. Bài 4: Các tính năng đặc biệt của Excel 1/ Thao tác liên kết dữ liệu: 1.1/ Kết nối bảng tính trong cùng một Workbook: - Mục đích: dùng để tham chiếu tới bảng tính khác trong cùng một tệp bảng tính. - Cú pháp: = 'Tên bảng tính' ! Địa chỉ ô Ví dụ: Giả sử bạn đang ở bảng tính " Nhập vật tư", bạn muốn đưa giá trị của ô A4 trong bảng tính "Kế toán" vào ô bảng tính D4 bạn cho công thức vào. = 'Kế toán' ! A4 - Sau đó tham chiếu đến các ô bảng tính còn lại. 1.2/ Kết nối các bảng tính trong các tệp bảng tính khác nhau: - Mục đích: Dùng để kết nối các bảng tính trong các tệp bảng tính khác nhau để lấy ra các thông tin cần thiết và tham chiếu tới bảng tính khác. - Cú pháp: = 'Tên ổ đĩa:\Đường dẫn\[Tên tệp]Tên bảng tính' ! Địa chỉ ô Ví dụ: Giả sử bạn có tệp bảng tính "BCVATTU.XLS" trong thư mục Excel trong thư mục Msoffice trong ổ đĩa C ta muốn kết nối cột B4 của bảng tính "NhapVT" vào bảng tính "BCNHAPVT.XLS" ta làm như sau: - Ta bấm chuột vào ô bảng tính cần nhập công thức ta viết. = 'C:\Msoffice\Excel\[NhapVT.xls]Nhập VT' ! B4 * Chú ý: Thông thường khi liên kết các Sheet hoặc các Workbook với nhau ta nên thực hiện dùng chuột chọn trên ô dữ liệu sẽ thuận tiện hơn và không phải nhớ công thức. Khi thực hiện bằng chỏ chuột trực tiếp, các ô dữ liệu khi liên kết các Workbook sẽ có địa chỉ tuyệt đối. 2/ Khái niệm các dạng địa chỉ ô dữ liệu: - Trong Excel có 3 dạng địa chỉ ô: 2.1/ Địa chỉ ô tương đối: - Địa chỉ ô tương đối là loại địa chỉ ô mà sau mỗi bước nhảy, giá trị của ô sẽ tham chiếu đến ô tương ứng trong bảng tính. - Để nhập địa chỉ ô dạng này, ta nhập vào theo dạng: Tên cột tên hàng + Ví dụ: Trong bảng dưới, nếu ô A2 nhập vào công thức = A1*2, khi dùng lệnh Fill hoặc Copy sang ô B2 ta sẽ có công thức = B1 *2, khi dùng lệnh Fill hoặc Copy sang ô A3 ta sẽ có công thức = A2 *2  A  B  C  D   1  100  200  300  400   2  =A1*2  B2*2     3       4       5       2.2/ Địa chỉ ô tuyệt đối: + Địa chỉ ô tuyệt đối là loại địa chỉ ô mà sau mỗi bước nhảy, giá trị của ô sẽ tham chiếu đến một ô nhất định được khai báo theo dạng tuyệt đối. + Để nhập địa chỉ ô dạng này, ta nhập vào theo dạng: $ Tên cột $tên hàng + Ví dụ: Trong bảng dưới, nếu ô A2 nhập vào công thức = $A$1*2, khi dùng lệnh Fill hoặc Copy sang ô B2 ta sẽ có công thức = $A$1 *2, khi dùng lệnh Fill hoặc Copy sang ô A3 ta sẽ có công thức = $A$1 *2  A  B  C  D   1  100  200  300  400   2  =$A$1*2      3       1.3/ Địa chỉ hỗn hợp: + Địa chỉ hỗn hợp là dạng địa chỉ vừa tương đối, vừa tuyệt đối. Có hai dạng địa chỉ hỗn hợp là tương đối hàng, tuyệt đối cột và tuyệt đối hàng, tương đối cột. + Địa chỉ tương đối hàng, tuyệt đối cột có đặc tính khi Fill hay Copy theo hàng thì sẽ tham chiếu đến ô tương ứng của bảng tính, nhưng khi Fill hay Copy theo cột thì ô tham chiếu vẫn giữ nguyên. Để nhập địa chỉ dạng này, ta nhập theo dạng: $ Tên cột Tên hàng Ví dụ: $A4 + Địa chỉ tuyệt đối hàng, tương đối cột có đặc tính khi Fill hay Copy theo hàng thì ô tham chiếu vẫn giữ nguyên, nhưng khi Fill hay Copy theo cột thì giá trị tham chiếu sẽ tham chiếu đến ô tương ứng trong bảng tính. Để nhập địa chỉ dạng này, ta nhập theo dạng: Tên cột $ Tên hàng + Ví dụ: A$5 Trong bảng dưới, nếu ô A2 nhập vào công thức = $A1*2, khi dùng lệnh Fill hoặc Copy sang ô B2 ta sẽ có công thức = $A1 *2, khi dùng lệnh Fill hoặc Copy sang ô A3 ta sẽ có công thức = $A2 *2  A  B  C  D   1  100  200  300  400   2  =$A$1*2      3       2/ Thao tác với Paste Special - Mục đích: Sau khi sao chép hoặc cắt dữ liệu, ta có thể thực hiện theo những tuỳ chọn của bảng tính như: chỉ sao chép dữ liệu, chỉ sao chép định dạng, chỉ sao chép chú thích.. - Thao tác: Vào menu Edit chọn Paste Special màn hình xuất hiện: Paste + All: Sao chép toàn bộ dữ liệu từ ô nguồn tới ô đích + Formulas: Sao chép công thức sang ô đích + Values: Sao chép phần hiển thị trên màn hình tới ô đích. + Format: Sao chép phần định dạng sang ô đích + Comments: Sao chép chú thích + Validation: Sao chép định dạng nhập dữ liệu trong vùng giá trị quy định. + All except borders: Sao chép cả bảng tính trừ không sao chép khung Operation: + None: Không lưu trữ phần dữ liệu trong ô hiện thời + Add: Cộng dữ liệu ô hiện tại với dữ liệu được dán vào + Subtract: Trừ ô dữ liệu hiện tại với dữ liệu được dán vào + Multiply: Nhân ô dữ liệu hiện tại với dữ liệu được dán vào + Divice: Chia ô dữ liệu hiện tại cho dữ liệu được dán vào - Skip Blanks: Bỏ qua những ô dữ liệu trống - Transpose: Dán dữ liệu đổi cột thành hàng hoặc ngược lại. Kích chuột vào OK * Chú ý: Muốn dán dữ liệu được liên kết với bảng tính chủ ta kích chuột chọn Paste Link 3/ Thao tác Copy dữ liệu và chèn vào vị trí được lựa chọn: - Đánh dấu phần dữ liệu cần Copy - Vào menu Edit chọn Copy Sau thao tác này sẽ có một vùng chấm chuyển động xung quanh phần dưa liệu được lựa chọn. - Đưa con trỏ tới vùng cần chèn dữ liệu. Vào menu Insert - Bấm chọn Copy Cells Sau thao tác này có hai tuỳ chọn sau: + Shift cells Right: Dữ liệu hiện tại được đẩy sang bên phải. + Shift Cells Down: Dữ liệu hiện tại được đẩy xuống dưới. 4/ Xoá dữ liệu: - Mục đích: Sau khi xoá dữ liệu ta có thể xoá theo những tuỳ chọn của bảng tính như: chỉ sao chép dữ liệu, chỉ sao chép định dạng, chỉ sao chép chú thích.. - Thao tác: Kích chuột chọn vùng dữ liệu cần xoá, vào menu Edit chọn Clear và chọn một trong các tuỳ chọn sau: + All: Xoá tất cả + Format: Chỉ xoá phần định dạng + Contents: Xoá phần hiển thị trên màn hình. + Comment: Xoá đi chú thích 5/ Thao tác tạo vùng dữ liệu: 5.1/ Thao tác tạo vùng dữ liệu: - Mục đích: Đặt tên vùng dữ liệu khi cần gọi đến hoặc tính toán ta có thể sử dụng tên đã đặt. - Thao tác: Đánh dấu vị trí cần tạo vùng, vào menu Insert chọn Name chọn Define màn hình xuất hiện hộp thoại Define Name + Names is WorkBook: Ghi tên vùng cần tạo Bấm chọn Add Kết thúc bấm chọn OK 5.2/ Thao tác chuyển nhanh tới vùng dữ liệu đã tạo: Kích chuột vào menu Edit chọn Goto màn hình xuất hiện ta kích chuột chọn tên vùng cần chuyển tới và chọn OK Bài 4: Các hàm cơ bản trong Microsoft Excel - Mục đích sử dụng: Các hàm trong Microsoft Excel được sử dụng là những công cụ hữu hiệu để tính toán nhanh và chính xác. Khi sử dụng hàm ta chỉ cần thực hiện một công thức cho những phần giống nhau về tính toán, sau đó có thể tham chiếu tới các vùng của dữ liệu. - Cú pháp: = Tên hàm(Danh sách tham số) + Tên hàm: Là danh sách các hàm có trong Excel hoặc người dùng tự định nghĩa. + Danh sách đối số: ( Các giá trị. ( Danh sách các ô. ( Địa chỉ các ô. ( Tên các vùng. ( Các công thức. ( Các hàm. 1/ Hàm SUM(): - Mục đích: Sử dụng để tính tổng vùng dữ liệu. - Cú pháp: = SUM(vùng dữ liệu) Vùng bảng tính có thể xác định bằng 2 dạng: + Dạng liệt kê: Liệt kê ra tất cả danh sách các ô dữ liệu cần tính tổng. Ví dụ: =Sum(D3,D5,D7,D14) + Dạng danh sách: Xác định chỉ số đầu, chỉ số cuối để xác định toàn bộ vùng dữ liệu. Ví dụ: = SUM(D5:D20) 2/ Hàm Average(): - Mục đích: Tính trung bình cộng vùng dữ liệu. - Cú pháp: = Average(vùng dữ liệu) - Ví dụ: Tính trung bình cộng cột lương từ ô C5 đến C20. = Average(C5:C20) 3/ Hàm Min(): - Mục đích: Tìm giá trị nhỏ nhất trong vùng dữ liệu. - Cú pháp: = Min(vùng dữ liệu) - Ví dụ: Tìm giá trị nhỏ nhất trong vùng C5 đến C20. = Min(C5:C20) 4/ Hàm Max(): - Mục đích: Tìm giá trị lớn nhất trong vùng dữ liệu. - Cú pháp: = Max(vùng dữ liệu) - Ví dụ: Tìm giá trị lớn nhất trong vùng C5 đến C20. = Max(C5:C20) 5/ Hàm SUMIF(): - Mục đích: Tính tổng theo điều kiện - Cú pháp: = SUMIF(,,) + Phạm vi: Vùng dữ liệu tương ứng để đặt điều + Điều kiện: Điều kiện xét + Phạm vi tính tổng: Phạm vi tính cho các bản ghi có thoả mãn tương ứng Ví dụ: =SUMIF(A2:A4,”C”,B2:B4) 5/ Hàm Counta(): - Mục đích: Đếm các ô có chứa dữ liệu trong vùng bảng. - Cú pháp: = Count(Danh sách ô dữ liệu) Ví dụ: Đếm các ô có dữ liệu từ C5 đến C20 = Counta(C5:C20) 6/ Hàm CountBlank(): - Mục đích: Đếm các ô không có dữ liệu trong vùng bảng. - Cú pháp: = CountBlank(vùng dữ liệu) Ví dụ: Đếm các ô không có dữ liệu từ C5 đến C20 = CountBlank(C5:C20) 7/ Hàm Count(): - Mục đích: Đếm các ô có dữ liệu dạng số trong vùng bảng. - Cú pháp: = Count(vùng dữ liệu) - Ví dụ: Đếm các ô có dữ liệu dạng số từ C5 đến C20 = Count(C5:C20) 8/ Hàm CountIf(): - Mục đích: Đếm các ô thoả mãn điều kiện đặt ra. - Cú pháp: =CountIf(vùng dữ liệu;"Điều kiện") + Vùng dữ liệu: Là vùng chứa dữ liệu cần đếm + Điều kiện: Có thể sử dụng các toán tử so sanh để thực hiện điều kiện Ví dụ: Đếm các ô dữ liệu thoả mãn lương >=500000 =CountIf(C5:C20;”>=500000”) Bài 6: Thao tác với Các hàm (tiếp) 1/Hàm LTRIM (): - Mục đích: Bỏ các kí tự trắng thừa bên trái của ô dữ liệu khi tính toán - Cú pháp: = LTRIM() Ví dụ: = B2 & “ “ & LTRIM(C2) 2/ Hàm RTRIM(): - Mục đích: Bỏ các kí tự trắng thừa bên phải của ô dữ liệu khi tính toán - Cú pháp: = RTRIM() Ví dụ: = RTRIM(B2) & “ “ & LTRIM(C2) * Chú ý: Có thể sử hàm TRIM thay cho cả hai LTRIM, RTRIM 3/ Hàm LEFT(): - Mục đích: Trích một số kí tự bên trái xâu kí tự - Cú pháp: = LEFT(,n) + Xâu ký tự: là xâu chứa ký tự cần trích ra + n: Số ký tự cần trích ra phía trái của xâu đó Ví dụ: B2="A0001" = LEFT(B2,2) cho giá trị là A0 4/ Hàm RIGHT(): - Mục đích: Trích ra một số kí tự bên phải xâu kí tự - Cú pháp: = RIGHT(,n) Ví dụ: B2="A0001" = RIGHT(B2,2) cho giá trị là 01 5/ Hàm MID(): - Mục đích: Trích ra một số kí tự kể từ vị trí i. - Cú pháp: = MID(, i, n) + Xâu ký tự: là xâu chứa số ký tự cần trích ra. + i: Là vị trí bắt đầu cần trích ra số ký tự trong chuỗi đó + n: là số ký tự cần trích ra Ví dụ: B2="AHVT01" =Mid(B2,3,3) cho giá trị là VT0 6/ Hàm UPPER(): - Mục đích: Chuyển xâu kí tự thành chữ in hoa - Cú pháp: = UPPER() - Ví dụ: = UPPER(B2) 7/ Hàm LOWER(): - Mục đích: Chuyển xâu kí tự về chữ in thường - Cú pháp: = LOWER() Ví dụ: = LOWER(B2) 8/ Hàm LEN(): - Mục đích: Lấy độ dài xâu kí tự - Cú pháp: = LEN() Ví dụ: B2="A0001" = LEN(B2) cho giá trị là 5 9/ Hàm ROUND(): - Mục đích: Làm tròn số theo phần thập phân - Cú pháp: = ROUND(Giá trị, Số chữ số i) + Giá trị: Là giá trị số thực chứa phần thập phân cần làm tròn + Số chữ số i: là số lượng chữ số phần thập phân cần làm tròn Ví dụ: B2=34.654 =ROUND(B2,1) cho giá trị là 34.7 10/ Hàm INT(): - Mục đích: Chuyển giá trị từ dạng số thực sang số nguyên. - Cú pháp: INT (Giá trị) Ví dụ: =INT(5.67456) cho giá trị là 5 11/ Hàm DAY(): - Mục đích: Trích ra số ngày của biểu thức dạng ngày tháng - Cú pháp: = DAY() Ví dụ: = DAY(A3) hoặc DAY(“10/20/2000”) cho giá trị là 20 12/ Hàm MONTH(): - Mục đích: Trích ra số tháng của biểu thức dạng ngày tháng - Cú pháp: = MONTH() Ví dụ: = MONTH(“10/20/2000”) cho giá trị là 10 13/ Hàm YEAR(): - Mục đích: Trích ra số ngày của biểu thức dạng ngày tháng - Cú pháp: =YEAR() Ví dụ: YEAR(“24/11/2000”) cho giá trị là 2000 14/ Hàm Now(): - Mục đích: Trích ra ngày tháng năm của ngày giờ hệ thống máy tính. - Cú pháp: =Now() Bài 6: Hàm Logic - tìm kiếm 1/ Hàm If(): - Mục đích: Kiểm tra biểu thức điều kiện đối với ô dữ liệu được tham chiếu, biểu thức điều kiện được thoả mãn thì hàm trả về giá trị đúng. Giá trị đúng được thực hiện, nếu biểu thức trả về giá trị sai thì hàm thực hiện công việc sai. Cú pháp: =IF(,,) + Biểu thức Logic: là điều kiện đưa ra để kiểm tra. + Câu lệnh 1: Nếu điều kiện kiểm tra đúng máy thực hiện lệnh tại và bỏ qua không thực hiện . + Câu lệnh 2: Nếu điều kiện kiểm tra sai máy thực hiện lệnh tại và bỏ qua không thực hiện . - Ví dụ 1: Thưởng cho những người có ngày công >=25 với mức thưởng 30% của lương. Nếu ngày công <25 thì thưởng là 0. =IF(C2>=25,D2*30/100,0) * Chú ý: Khi sử dụng hàm IF ta có thể lồng các hàm IF với nhau. Khi sử dụng điều kiện dạng ký tự thì ký tự so sánh được để trong dấu nháy kép. - Ví dụ 2: Thưởng cho nhân viên trong công ty với mức thưởng như sau: + Nếu ngày công >=26 mức thưởng 40% + Nếu ngày công >= 20 và <25 mức thưởng 30% + Nếu ngày công >=18 và <20 mức thưởng 15%. Thưởng bằng 0 cho những người có ngày công <18 =IF(C2>=26,D2*40/100,IF(C2>=20,D2*30/100,IF(C2>=18,D2*20/100,0))) 2/ Hàm And(): - Mục đích: Kết hợp các hàm trong biểu thức điều kiện. Nếu tất cả các điều kiện trong biểu thức đúng thì hàm trả về giá trị đúng. Nếu một trong các điều kiện sai, hàm trả về giá trị sai. - Cú pháp: = And(Điều kiện 1; Điều kiện 2; Điều kiện 3;...) * Chú ý: Hàm And thường được kết hợp sử dụng trong các hàm có điều kiện như hàm IF. Ví dụ: Thưởng cho những người có Ngày công>=25 và có hệ số lương>=3 với mức thưởng 30%. =IF(And(C2>=25;E2>=3);D2*30/100;0) 3/ Hàm Or(): - Mục đích: Chọn một trong các điều kiện của biểu thức điều kiện. Nếu một trong các điều kiện trong biểu thức đúng thì hàm trả về giá trị đúng. Nếu tất cả các điều kiện trong biểu thức điều kiện sai hàm trả về giá trị sai. - Cú pháp: = OR(Điều kiện 1; Điều kiện 2; Điều kiện 3;...) * Chú ý: Hàm Or thường được kết hợp sử dụng trong các hàm có điều kiện như hàm IF. - Ví dụ: Thưởng cho những người có Ngày công>=25 hoặc có hệ số lương>=3 với mức thưởng 30%. =IF(Or(C2>=25;E2>=3);D2*30/100;0) * Ví dụ tổng quát: Xếp mức học cho những học sinh với mức xếp như sau: + Nếu Điểm trung bình >=8 với hạnh kiểm hoặc bằng Khá hoặc bằng tốt xếp loại Giỏi. + Nếu Điểm trung bình >=7 và <8 với hạnh kiểm hoặc bằng Khá hoặc bằng Tốt xếp loại Khá. + Nếu Điểm trung bình >=5 và nhỏ hơn 7 với hạnh kiểm hoặc bằng Tốt hoặc bằng Khá hoặc bằng Trung bình với xếp loại Trung bình. + Còn lại các trường hợp cho xếp loại Yếu. =IF(And(C2>=8,Or(D2="Tốt”,D2="Khá”),"Giỏi”,IF(And(And(C2>=7,C2=5, C2<7), Or (D2="Tốt”, D2="Khá”, D2="Trung bình")), "Trung bình”; "Yếu”))) Bài 7: cơ sở dữ liệu trong excel Khái niệm CSDL: - CSDL trong Excel là một vùng bảng tính, trong đó hàng đầu tiên là tiêu đề và bên dưới là dữ liệu phản ánh theo tiêu đề đó. - Trong CSDL, mỗi một cột được gọi là 1 trường, mỗi một hàng gọi là 1 bản ghi. 1/ Thao tác sắp xếp dữ liệu: - Mục đích: Sắp xếp lại dữ liệu theo một trật tự nhất định. - Thao tác: Kích chuột bôi đen vùng bảng tính cần sắp xếp dữ liệu, sau đó menu Data chọn Sort sau thao tác này xuất hiện bảng chọn Sort. + Sort By: ta chọn ra khoá sắp xếp thứ nhất. + Chọn thứ tự sắp xếp (Ascending: tăng, Descending: giảm). + Then By: Chọn khoá sắp xếp thứ hai (nếu có). * Có thể chọn Options để điều tìm hiểu sau phần sắp xếp, khi đó hộp thoại Sort Options xuất hiện. Khi này bạn chọn một trong hai khả năng sau: + Sort Top to Bottom: sắp xếp theo hàng. + Sort Left to Right: sắp xếp theo cột. Kết thúc bấm chọn OK. 2/ Thao tác lọc dữ liệu. - Mục đích: Lọc ra những dữ liệu thoả mãn điều kiện lựa chọn. - Thao tác: Kích chuột bôi đen vùng bảng cần lọc dữ liệu, vào menu Data chọn Filter chọn AutoFilte sau thao tác này xuất hiện các nút mũi tên tại tiêu đề của các trường, ta có thể kích chuột vào các nút đó để lọc ra dữ liệu thoả mãn điều kiện: + All: Lọc ra tất cả các bản ghi trong trường đó. + Top 10: Lọc ra các bản ghi đứng đầu hoặc cuối trường đó + Custom…: Lựa chọn các điều kiện của các toán tử và kết hợp And hoặc Or cho các điều kiện đó. + Blank: Lọc ra các bản ghi trắng + Nonblank: Lọc ra các bản ghi có chứa dữ liệu. Sau thao tác này muốn huỷ bỏ lệnh tự động lọc ta kích chuột vào menu Data chọn Filter chọn Autofilter. 3/ Hàm VLOOKUP(): - Mục đích: Tìm kiếm giá trị trong một bảng tiêu chuẩn theo chiều cột, khi đó máy trả về giá trị tương ứng với giá trị tìm kiếm trong bảng tiêu chuẩn đó. - Cú pháp: = VLOOKUP(Giá trị, Bảng tiêu chuẩn, Số cột) + Giá trị: Là giá trị cần tìm kiếm trong bảng tiêu chuẩn + Bảng tiêu chuẩn: Là vùng bảng chứa các giá trị tương ứng với giá trị cần tìm kiếm trong bảng tiêu chuẩn. + Số cột: Là số thứ tự của cột cần lấy ra giá trị tương ứng tính từ phía trái của bảng. - Ví dụ: = VLOOKUP(B2,$A$35:$D$43,3) * Chú ý: Bảng tiêu chuẩn thường phải sắp xếp theo cột chứa giá trị cần tham chiếu trước khi tham chiếu. Hàm VLookup thường được sử dụng kết hợp với các hàm khác như If() để tạo thành biểu thức điều kiện. 4/ Hàm HLOOKUP(): - Mục đích: Tìm kiếm giá trị trong một bảng tiêu chuẩn theo chiều cột, khi đó máy trả về giá trị tương ứng với giá trị tìm kiếm trong bảng tiêu chuẩn đó. - Cú pháp: = VLOOKUP(Giá trị, Bảng tiêu chuẩn, Số hàng) + Giá trị: Là giá trị cần tìm kiếm trong bảng tiêu chuẩn + Bảng tiêu chuẩn: Là vùng bảng chứa các giá trị tương ứng với giá trị cần tìm kiếm trong bảng tiêu chuẩn. + Số hàng: Là số thứ tự của hàng cần lấy ra giá trị tương ứng tính từ phía trên của bảng. - Ví dụ: = HLOOKUP(B2,$A$35:$D$43,2) * Chú ý: Bảng tiêu chuẩn thường phải sắp xếp trước khi tham chiếu. Hàm HLookup thường được sử dụng kết hợp với các hàm khác như If() để tạo thành biểu thức điều kiện. 2/ Thao tác nhập dữ liệu theo Form. - Bấm chuột vào vùng CSDL - Vào Menu Data. - Bấm chọn Form. - Sau thao tác này xuất hiện một hộp hội thoại Form. - Ta có thể nhập dữ liệu vào theo mẫu đã có và sử dụng các phím mũi tên lên xuống để xem các bản ghi. - Phím Enter: dùng để đến bản ghi sau bản ghi hiện thời. - PgUp: đến bản ghi đầu tiên của vùng dữ liệu. - PgDown: Đến bản ghi cuối cùng của vùng dữ liệu. - Công dụng của các nút trong hộp hội thoại sau: + New: Nhập bản ghi mới. + Delete: Xoá bản ghi hiện thời. + Restore: Khôi phục dữ liệu vừa nhập, xoá. + Find Prev: Đến bản ghi trước bản ghi hiện thời. + Find Next: Đến bản ghi sau bản ghi hiện thời. + Criteria: Nhập điều kiện truy tìm. + Close: Đóng cửa sổ Data Form. + Help: Xem hướng dẫn sử dụng. *********** hộp hội thoại Advanced Filter. + Tại mục Action, chọn Filter the List in place. Xác định việc hiển thị những bản ghi thoả mãn điều kiện ngay trên vùng cơ sở dữ liệu. + Tại mục List Range cho địa chỉ vùng dữ liệu vào. + Tại mục Criteria Range cho địa chỉ vùng tiêu chuẩn vào. + Kết thúc bấm chọn OK. * Chú ý: Muốn xuất hiện tất cả các bản ghi ta làm như sau: - Vào Menu Data. - Chọn Filter. - Chọn Show All.5/ Trích dữ liệu: - Tạo ra vùng điều kiện ở bất kỳ đâu trên bản tính - Gõ vào dưới vùng điều kiện các điều kiện trích rút (nếu gõ trên 1 hàng được hiểu là phép toán AND, nếu gõ trên 2 hay nhiều hàng được hiểu là phép toán OR) - Đưa con trỏ vào vùng dữ liệu. - Vào Menu Data. - Chọn Filter. - Chọn Advanced Filter. - Sau thao tác này xuất hiện hộp hội thoại Advanced Filter. + Tại mục Action, chọn Filter the List in place. Xác định việc hiển thị những bản ghi thoả mãn điều kiện ngay trên vùng cơ sở dữ liệu. + Tại mục List Range cho địa chỉ vùng dữ liệu vào. + Tại mục Criteria Range cho địa chỉ vùng tiêu chuẩn vào. + Tại mục Copy To cho địa chỉ vùng được trích ra. + Kết thúc bấm chọn OK. 6/ Thao tác tính tổng theo nhóm. 6.1/ Tính tổng nhóm - Trước khi tính tổng nhóm cần phải sắp xếp dữ liệu theo trường làm khoá tính tổng nhóm. - Đưa con trỏ vào vùng CSDL. - Vào Menu Data. - Chọn Subtotals. - Sau thao tác này hộp thoại Subtotals xuất hiện. + Tại mục At Each Change in: chọn trường cần tạo nhóm tổng hợp. + Tại mục Use Function: Chọn hàm cần tính toán thống kê. + Tại mục Add Subtotals to: Chọn những trường cần tính toán. + Kết thúc bấm chọn OK. Ta thấy bên trái bảng chọn xuất hiện các nút điều khiển thứ bậc. + Nút số 1: chỉ hiện dòng tổng của tất cả các nhóm. + Nút số 2: Chỉ hiện dòng tổng của từng nhóm và của tất cả các nhóm. 6.2/ Huỷ bỏ tính tổng nhóm - Vào Menu Data. - Chọn Subtotals. - Chọn Remove All 7/ Điều kiện nhập dữ liệu Mục đích: Hạn chế việc nhập sai dữ liệu Các bước thực hiện: - Chọn vùng nhập dữ liệu - Chọn Validation trong Menu Data - Chọn settings để nhập các điều kiện cho dữ liệu + Loại dữ liệu + Điều kiện ràng buộc + Chọn Error Alert để ra thông báo khi sai dữ liệu + Chọn Input Message để tạo các thông báo hướng dẫn. 8/ Consolidate Mục đích: Kiết xuất thông tin từ nhiều vùng dữ liệu Thao tác thực hiện: - Chọn Consolidate trong Menu Data - Chọn các vùng dữ liệu tham gia lệnh Consolidate - Chọn hàm thực hiện: SUM, MIN, MAX, COUNT... - Có thể chọn các Option cho thực hiện lệnh Consolidate + Top Row + Left Column Dùng để gộp các dòng cùng tên Chọn Create link data source để liên kết kết quả. Trong trường hợp này phải đặt kết quả Cosolidate ở trang không chứa vùng dữ liệu. 9/ Pivotable Mục đích: Kiết xuất thông tin theo chiều ngang Thao tác thực hiện: - Chọn Pivotable trong Menu Data - Chọn kiểu dữ liệu (Thông thường là Excel) - Chọn vùng dữ liệu là nguồn cho Pivotable - Chọn các tiêu mục cho Pivotable - Chọn các thuộc tính (Option) cho Pivotable - Bấm Finish để kết thúc - Có thể thê thêm nhiều truong cho row hoặc column khi đó các trường sẽ có thứ tự ưu tiên từ trái sang phải, từ trên xuống dưới - Công thức mặc định trong vùng dữ liệu là SUM, ta có thể thay đổi công thức sau khi tạo lập bằng cách: + Chọn vùng Pivotable + Bấm chuột phải + Chọn Field + Chọn công thức muốn thay thế Trong trường hợp thay đổi dữ liệu ở vùng nguồn thì Pivotable không tự động cập nhập, để cập nhập dữ liệu ta chọn Refresh Data trong Menu Data hoặc trong Popup Menu bởi việc bấm chuột phải. Ta có thể tạo ra công thức cho vùng dữ liệu bằng cách: + Chọn vùng Pivotable + Bấm chuột phải + Chọn Fomulas + Chọn Calculated field + Vào tên cho Field + Nhập công thức + Chọn Add 10/ Nhập dữ liệu từ CSDL khác Mục đích: Nhập dữ liệu từ các CSDL khác như FOXPRO, ACCESS... vào Excel Thao tác thực hiện: - Chọn Get External Data trong Menu Data - Chọn New Query - Chọn file CSDL: ổ đĩa, thư mục, file - Chọn dữ liệu sẽ tham gia query - Tạo điều kiện lọc cho Query 11/ Xuất dữ liệu sang CSDL khác Mục đích: Lưu dữ liệu kết quả sang dạng DL khác Thao tác thực hiện: - Chọn Save As trong Menu File - Chọn kiểu dữ liệu cần lưu ra - Chọn tên file, ổ đĩa, thư mục nơi sẽ lưu - Bấm OK *) Chú ý: Từ chương trình khác có thể đọc được Excel, chẳng hạn từ ACCESS: - Mở file Database - Chọn Table - Chọn GetExternal Data trong Menu File - Chọn Import - Chọn loại dữ liệu - Chọn File dữ liệu (Excel) - Bấm Next, chọn các thuộc tính phân định: Trường, tiêu đề... - Bấm Finish 12/ Sử dụng Comments Mục đích: Chèn các chú thích cho các vùng Thao tác thực hiện: - Chọn Comments trong Menu Insert - Vào thông tin comments *) Chú ý: Ta có thể hiển thị cố định Comments, xoá, Hide comment bằng cách: + Chuyển đến vùng comment + Bấm chuột phải + Chọn lệnh thích hợp 13/ Text to Column Mục đích: Tách một cột thành nhiều cột theo dấu hiệu nào đó Thao tác thực hiện: - Chọn cột cần tách - Chọn Text to column trong Menu Data - Bấm Next và chọn dấu hiệu để tách (;, “ “, Tab...) - Bấm Finish * Chú ý: Có thể sử dụng Replace để chuyển dấu hiệu sạng dạng kí tự khác để dùng cho việc chuyển đổi 14/ Column to Text Mục đích: Ghép nhiều cột thành một cột Thao tác thực hiện: - Sử dụng phép toán & cùng với “ “ để tạo ra cột kết quả - Ví dụ: = HO & “ “ & TEN Bài: Sử dụng các hàm dữ liệu: Trước khi thực hiện các hàm của CSDL cần phải thiết lập các vùng sau: - Vùng dữ liệu: Là vùng đưa các dữ liệu cần thiết và ít nhất phải có hai dòng. Dòng đầu tiên chứa tiêu đề cột gọi là tên vùng hay tên trường và được phép đưa vào các ký tự bình thường của một chuỗi và không được trùng lập nhau. Các dòng còn lại chứa các dữ liệu. Mỗi dòng được gọi là một bản ghi. - Vùng tiêu chuẩn: Là vùng chứa dữ liệu để tìm kiếm, xoá, rút, trích... ít nhất phải có hai dòng. Dòng đầu chứa các tiêu đề mà mỗi tiêu đề phải trùng với tiêu đề vùng dữ liệu. 1/ Hàm DSUM() Mục đích: Tính tổng theo vùng điều kiện Cú pháp: = DSUM(, ,) Tính tổng của cột của các bản ghi trong thoả mãn điều kiện Các bước thực hiện: - Tạo vùng điều kiện - Gõ điều kiện vào vùng điều kiện - Chọn hàm DSUM: - Chọn Vùng dữ liệu - Vào chỉ số cột cần tính tổng (từ 1) - Chọn vùng điều kiện Ví dụ: Name  Loai  Salary   Nguyen Van A  VN  2,000,000   Paul Crowe  NN  2,000   Hoang Hoa Anh  VN  10,000,000   Do Nang Toan  VN  20,000,000   Tony Bailey  NN  5,000   Nguyen Thuy Hong  VN  8,000,000   Mara Moustafine  NN  10,000               Name  Loai  Salary   Nguyen*  VN    Paul*  NN    = DSUM(A2:C9,3,A13:C15) 2/ Hàm DMax(): Mục đích: Tìm giá trị lớn nhất trên cột n trong vùng cơ sở dữ liệu thoả mãn điều kiện của vùng tiêu chuẩn. Thao tác thực hiện: = DMax(, ,). Ví dụ: Tìm mức thu nhập lớn nhất của những người là nhân viên. = DMax(A2:E7;5;H2:H3) 3/ Hàm DMin(): Mục đích: Tìm giá trị nhỏ nhất trên cột n trong vùng cơ sở dữ liệu thoả mãn điều kiện của vùng tiêu chuẩn. Thao tác thực hiện: = DMin((, ,). Ví dụ: Tìm mức thu nhập nhỏ nhất của những người là nhân viên. = DMax(A2:E7;5;H2:H3) 4/ Hàm DCount(): Mục đích: Đếm số ô chứa giá trị số trên cột n trong vùng cơ sở dữ liệu thoả mãn điều kiện của vùng tiêu chuẩn. Cú pháp: = DCount(, ,). Ví dụ: Đếm số ô của những người có mức lương là trưởng phòng. = DCount(A2:E7;5;H4:H5). 5/ Hàm DCounta(): Mục đích: Đếm số ô chứa dữ liệu số trên cột n trong vùng cơ sở dữ liệu thoả mãn điều kiện của vùng tiêu chuẩn. Cú pháp: = DCounta(, ,). Ví dụ: Đếm số ô của những người có mức lương là trưởng phòng. = DCounta(A2:E7;5;H4:H5). 6/ Hàm DAverage(): Mục đích: Tìm giá trị trung bình trên cột n trong vùng cơ sở dữ liệu thoả mãn điều kiện của vùng tiêu chuẩn. Cú pháp: = DAverage(, ,). Ví dụ: Tính tiền lương trung bình cho những cán bộ là trưởng phòng. = DAverage(A2:E7;5;H3:H4). Bài: Thao tác vẽ biểu đồ 1/ Thao tác vẽ biểu đồ: Mục đích: Biểu diễn giá trị bằng hình ảnh giúp ta quan sát dữ liệu trực quan. Khi thay đổi dữ liệu thì biểu đồ biểu diễn kiểu dữ liệu đó cũng được thay đổi theo. Thao tác: - Bôi đen phần dữ liệu cần dùng để vẽ biểu đồ - Vào Menu Insert chọn Chart màn hình xuất hiện ta chọn kiểu biểu đồ ở bên trái và biểu đồ cụ thể ở bên phải màn hình. - Bấm chọn Next - Chọn ra vẽ biều đồ theo cột hay theo dòng + Rows: Phân tích dữ liệu theo dòng. + Columns: Phân tích dữ liệu theo cột. - Bấm chọn Next - Cho các tiêu đề của biểu đồ vào các mục tương ứng (Có thể bỏ qua) - Bấm chọn Next - Chọn để dữ liệu ở 1 Sheet riêng biệt hoặc để ở 1 Sheet đã có sẵn. + As a new sheet: để biểu đồ ở 1 Sheet mới, tên Sheet do ta tự gõ vào. + As object in: để biểu đồ ở 1 Sheet bất kỳ đã có sẵn - Chọn Finish 2/ Thao tác sửa biểu đồ: Sau khi vẽ biểu đồ màn hình xuất hiện thanh công cụ Chart, theo thứ tự từ trái qua phải, các công cụ có chức năng: - Chọn đối tượng cần thay đổi tính chất theo các tên qui định của Excel - Chọn tính chất cho đối tượng được chọn. * Thay cho 2 nút này ta có thể nháy kép chuột vào đối tượng của biểu đồ cần sửa tính chất. - Đổi kiểu biểu đồ. - Hiển thị phần ghi chú của biểu đồ - Hiển thị phần bảng dữ liệu dưới chân biểu đồ (chỉ áp dụng được với biểu đồ hình trụ) - Đổi phản ánh dữ liệu theo hàng. - Đổi phản ánh dữ liệu theo cột - Xoay tiêu đề bài: định dạng dữ liệu bằng Style - liên kết dữ liệu 1/ Thao tác với Style 1.1/ Thao tác tạo Style: - Định dạng phần dữ liệu cần tạo lập Style. - Vào Menu Format. - Chọn Style. - Sau thao tác này xuất hiện hộp hội thoại Style. - Tại mục Style Name ta ghi tên Style vào. - Bấm chọn Add. - Kết thúc bấm chọn OK. 1.2/ Thao tác áp dụng Style: - Lựa chọn ra những Style cần áp dụng. - Vào Menu Format. - Chọn Style. - Tại mục Style Name ta chọn ra Style cần áp dụng. - Kết thúc bấm chọn OK. 1.3/ Thao tác sửa Style: - Chọn ra Style cần sửa. - Vào Menu Format. - Chọn Style. - Sau thao tác này xuất hiện hộp thoại Style. - Bấm chọn Modify. - Sau thao tác này xuất hiện hộp thoại Format Cells ta định dạng lại các kiểu dữ liệu cần thiết. - Kết thúc bấm chọn OK. - Bấm chọn OK. 2.4/ Thao tác xoá Style. - Vào Menu Format. - Chọn Style. - Sau thao tác này xuất hiện một hộp hội thoại Style. - Tại mục Style Name ta chọn ra Style cần xoá. - Bấm chọn Delete. - Kết thúc bấm chọn OK. Thao tác với MACRO - các câu lệnh điều khiển 1/ Khái niệm: Macro là một đoạn chương trình cho phép ta thực hiện một nhóm lệnh nào đó hoặc theo một chương trình đã sắp đặt trước. 2/ Tạo lập Macro Có hai cách tạo lập: 2.1. Tạo lập bằng Macro Record - Bật ghi Macro và thực hiện các thao tác mẫu cho đến ghi tắt ghi Macro. - Chọn Record New Macro trong Popup Macro trong Menu Tool - Vào tên cho Macro - Thực hiện các thao tác - Bấm Stop để dừng việc ghi Macro *) Chú ý: Ta có thể gắn các phím tắt cho Macro. 2.2. Tạo lập Macro bằng chương trình - Chọn Macros trong Popup Macro trong Menu Tool - Chọn tên Macro - Chọn Edit - Soạn thảo nội dung hoạt động của Macro 3/ Sử dụng Macro - Chọn Macros trong Popup Macro trong Menu Tool - Chọn tên Macro - Bấm Run *) Chú ý: Nếu Macro có gắn phím tắt thì ta có thể bấm phím này khi sử dụng 4/ Câu lệnh điều kiện IF: Mục đích: Thực hiện việc rẽ nhánh theo điều kiện Cú pháp : If Then //Câu lệnh 1 Else //Câu lệnh 2 End If Hoặc If Then //Câu lệnh End If Ví dụ : If ActiveCell.Font.ColorIndex=3 Then S= S+ActiveCell.Value Else S= S+1 End If Chương trình ví dụ: Viết chương trình tính thuế thu nhập theo biểu thuế: Ngưỡng  Thuế   < 200  0%   >= 200 và < 300  10%   >= 300 và < 400  20%   >= 400 và < 500  30%   >= 500  40%   Thu nhập sau khi tính thuế > 600  30%   Viết hàm tính thuế: Function TaxCalulation(Salary As Double) As Double If Salary < 200 Then TaxCalulation= 0 Else If Salary < 300 Then TaxCalulation= (Salary-200) * 0.1 Else If Salary < 400 Then TaxCalulation= 10+ (Salary-300) * 0.2 Else If Salary < 500 Then TaxCalulation= 10+20+(Salary-400) * 0.3 Else TaxCalulation= 10+20+30+(Salary-500) * 0.4 TheRest= Salary- TaxCalculation If TheRest > 600 Then TaxCalulation= TaxCalulation+ (TheRest – 600)* 0.3 End If End If End If End If EndIf End Function Chú ý: 1- Một số kiểu dữ liệu cơ bản: Double : Thập phân Integer : Kiểu nguyên với giá trị < 65000 Long : Kiểu nguyên với giá trị hàng tỷ String : Kiểu text Khi khai báo: Dim As VD: Dim Therest As Long 2- Khi xây dựng hàm ta nên đặt ở các modul để có tác dụng trong cả workbook, và có thể sử dụng nó trong mục insert function trong mục user defined. Cú pháp khi xây dựng hàm: Function ( As , As , ....) As //Nội dung hàm End Funtion Ví dụ: Function TaxPayment(Loai As String, Salary As Double) As Double If Loai = "VN" Then If Salary < 2500000 Then TaxPayment = 0 Else If Salary < 4000000 Then TaxPayment = (Salary - 2500000) * 0.1 Else If Salary < 6000000 Then TaxPayment = (4000000 - 2500000) * 0.1 + (Salary - 4000000) * 0.2 Else If Salary < 8000000 Then TaxPayment = 150000 + 400000 + (Salary - 6000000) * 0.3 Else If Salary < 12000000 Then TaxPayment = 150000 + 400000 + 600000 + (Salary - 8000000) * 0.4 Else If Salary < 15000000 Then TaxPayment=150000+400000+600000+1600000+(Salary - 12000000) * 0.5 Else TaxPayment=150000+400000+600000+1600000+1500000+(Salary-15000000)* 0.6 End If End If End If End If End If End If Else If Salary < 2500 Then TaxPayment = 0 Else If Salary < 4000 Then TaxPayment = (Salary - 2500) * 0.1 Else If Salary < 6000 Then TaxPayment = (4000 - 2500) * 0.1 + (Salary - 4000) * 0.2 Else If Salary < 8000 Then TaxPayment = 150 + 400 + (Salary - 6000) * 0.3 Else If Salary < 12000 Then TaxPayment = 150 + 400 + 600 + (Salary - 8000) * 0.4 Else If Salary < 15000 Then TaxPayment = 150 + 400 + 600 + 1600 + (Salary - 12000) * 0.5 Else TaxPayment = 150 + 400 + 600+1600+ 1500+(Salary - 15000) * 0.6 End If End If End If End If End If End If End If End Function 3- Trong trường hợp muốn sử dụng các hàm đã viết nhiều lần ta có thể lưu file Excel dưới dạng Template 5/ Câu lệnh rẽ nhánh Mục đích: Thực hiện việc rẽ với nhiều nhánh trong chương trình Cú pháp: Select Case Case //Statement 1 Case //Statement 2 …. Case //Statement n Case Else //Statement default End Select Ví dụ: Select Case Country Case “USA” TaxLevel= 0.5 Case “Vietnam” TaxLevel= 0.1 Case “China” TaxLevel= 0.15 Case Else TaxLevel= 0.2 End Select 6/ Lệnh DO WHILE.. LOOP Mục đích: Thực hiện vòng lặp theo điều kiện Cú pháp: DO WHILE //Statement 1 LOOP Chừng nào còn đúng thì //Statement 1 còn được thực hiện Ví dụ: I= 0 DO WHILE I<= 100 S= S+I LOOP