Tin học văn phòng - Chương 4: Phần mềm bảng tính điện tử MS Excel 2010

PHẦN MỀM BẢNG TÍNH ĐIỆN TỬ MS EXCEL 2010Chương 4Bài giảng của Khoa Hệ thống thông tin Quản lýNội dungChương 4 - PM bảng tính điện tử MS EXCEL2010218-Oct-1512 2 Các hàm số và phân tích dữ liệu với Excel 201061 Làm quen với môi trường làm việc Excel 2010 1Tổng quan bố cục giao diện trong Excel Định dạng trong sổ tính excel 2010Tổ chức trang tính và bảngCác đối tượng đồ họa trong excelTùy chỉnh và nâng cao môi trường excel 2010 - Sổ tính và trang tính là gì?1 - Các khái niệm cơ bản318-Oct-15Sổ tính có tên ngầm định là Book1, Book2 Trang tính có tên ngầm định là Sheet1, Sheet2 2 – Các thành phần giao diện trong ExcelCác thành phần giao diệnMô tảThe Quick Access toolbarCông cụ truy xuất nhanhThe RibbonPhần chứa các thẻ, nhóm lệnh, lệnh thực thi các tác vụ khác nhauThe Formula BarChứa dữ liệu của một ô (có thể là công thức hay hàm)The task paneVùng xuất hiện khi sử dụng một số tính năng xác định của Excel (clipboard, recovery)The status barLà vùng hiển thị một số thông tin hữu ích cho bạn như thanh trượt phóng to/thu nhỏ hay các trạng thái tùy chỉnh.2 - Các thành phần giao diện trong ExcelChương 4 - PM bảng tính điện tử MS EXCEL2010518-Oct-15Sổ/bảng tínhCác trang tínhBộ công cụ Ribbon gồm các nhóm chức năng chính của EXCEL Customize Quick Access Toolbar là một thanh công cụ tùy chỉnhKhu vực trang tínhTrang tính hiện thờiCác trang tính có cấu trúc giống nhau.Mỗi trang tính đều: Có 16 384 cột: từ A đến XFD (Ctrl + ↑) Có 1 048 576 dòng: từ 1 đến 1 048 576 (Ctrl + →)Khu vực Zoom trang tính2 - Các thành phần giao diện trong Excel18-Oct-15Thanh công thức: hiện nội dung ô hiện thờiHủy bỏ nội dung vừa nhậpLưu nội dung vừa nhậpChèn hàm2 - Các thành phần giao diện trong Excel18-Oct-15Chương 4 - PM bảng tính điện tử MS EXCEL201072.1 – Giao diện RibbonTab ribbon: Home, Insert, Page Layout, Formulas, Data, Review, View, Thẻ ngữ cảnh (Contextual tab)Nhóm ribbon: Font, Alignment, Number, Command: Paste, Conditional Formating, Format as table, Sort & Filter, Chương 4 - PM bảng tính điện tử MS EXCEL2010818-Oct-15Các thẻ trên thanh RibbonDùng để hiển thị Backstage, chứa các câu lệnh thông dụng nhất trong Excel như in ấn, lưu, tạo mới hay chia sẻ sổ tính.Các thẻ trên thanh RibbonThẻ Home: Thực hiện các chức năng cơ bản như định dạng bảng, thêm/sửa style, thêm các định dạng căn bản cho ô dữ liệu.Các thẻ trên thanh RibbonThẻ Insert: Thêm các đối tượng vào trong sổ tính như bảng, biểu đồ, ký hiệu, các hình minh họa, văn bản, liên kết, Các thẻ trên thanh RibbonThẻ Page Layout: Dùng để thiết lập trang tính, thay đổi bố cục, hướng của trang tính và các tùy chọn liên quan.Các thẻ trên thanh RibbonThẻ Formulas: Tạo các công thức sử dụng các hàm có sẵn trong Excel 2010 và được phân loại thành các nhóm công thức.Các thẻ trên thanh RibbonThẻ Data: Dùng để kết nối với nguồn dữ liệu bên ngoài và tải dữ liệu từ các nguồn đó vào bảng tính Excel.Các thẻ trên thanh RibbonThẻ Review: Cho phép xem lại các trang tính Excel, cung cấp các công cụ như kiểm tra chính tả, dịch,Các thẻ trên thanh RibbonThẻ View: Cho phép ẩn/hiện các đường ngăn cách (gridlines) các ô trong trang tính, cung cấp các công cụ để hiển thị cửa sổ trang tính và cửa sổ ứng dụng Excel.Các thẻ trên thanh RibbonThẻ Xuất hiện theo ngữ cảnh: Đây là các thẻ không xuất hiện mặc định trong Excel 2010, nó sẽ xuất hiện khi bạn chọn một đối tượng xác định như bảng, biểu đồ, các đối tượng đồ họa,Tùy vào loại đối tượng bạn chọn thì các thẻ theo ngữ cảnh sẽ hiển thị tương ứng.2.2 – Backstage viewChương 4- PM bảng tính điện tử MS EXCEL20101818-Oct-152.2 - Backstage ViewInfo: hiển thị các thông tin liên quan đến các thuộc tính của sổ tính và các công cụ tra soát thông tin, bảo mật.2.2 - Backstage ViewRecent: hiển thị thông tin về lịch sử mở file của Excel.2.2 - Backstage ViewNew: Tạo mới một sổ tính từ các mẫu (template) có sẵn trong máy hoặc tải về online từ thư viện của Microsoft.2.2 - Backstage ViewPrint: Hiển thị tùy chọn ở chế độ xem trước hoặc in ấn trang tính.2.2 - Backstage ViewSave & Send: Cung cấp tùy chọn để lưu sổ tính theo định dạng của các phiên bản, chia sẻ sổ tính qua email hoặc SharePoint, xuất bản sổ tính lên server.2.2 - Backstage ViewHelp: Hiển thị thông tin liên quan đến trợ giúp trong Excel, các bản cập nhật cũng như thông tin về bản quyền phần mềm.2.2 - Backstage ViewLưu lại file đang sử dụng(lưu thành file mới nếu chưa tồn tại)Lưu lại file đang sử dụng dưới tên mới, đường dẫn mớiLưu lại file dưới định dạng PDFMở một sổ tính đã tồn tạiĐóng sổ tính hiện hành2.2 - Backstage ViewOption: Hiển thị hộp thoại Excel Options, cho phép tùy chỉnh giao diện Excel.3 - Định dạng sổ tính Excel 2010Định dạng ô tính/miền ô tínhĐịnh dạng trang inĐịnh dạng Sheet backgroundĐịnh dạng Theme Chương 5 - PM bảng tính điện tử MS EXCEL20072718-Oct-153.1 - Định dạng ô tính/miền ô tính: Bước 1: nhấn chọn ô/miền ô chứa dữ liệu cần định dạng  Bước 2: nhấn chọn Home – Format – Format Cells: - Chọn thẻ Number để định dạng cho từng kiểu dữ liệu tương ứng. Chương 4 - PM bảng tính điện tử MS EXCEL20102818-Oct-15 Dạng ban đầu3.1 – Định dạng ô tính, miền ô tính Bước 1: nhấn chọn ô/miền ô chứa dữ liệu cần định dạng  Bước 2: nhấn chọn Home – Format – Format Cells: - Chọn thẻ Number để định dạng cho từng kiểu dữ liệu tương ứng. 2918-Oct-15 Dạng ban đầu Định dạng dữ liệu kiểu sốĐịnh dạng dữ liệu kiểu tiền tệĐịnh dạng dữ liệu kiểu ngày/tháng/nămĐịnh dạng dữ liệu kiểu giờ/phút/giâyĐịnh dạng dữ liệu kiểu %Định dạng dữ liệu kiểu chuỗi3.1- Định dạng ô tính/miền ô tính: Bước 1: nhấn chọn ô/miền ô chứa dữ liệu cần định dạng  Bước 2: nhấn chọn Home – Format – Format Cells: - Chọn thẻ Aligment để thiết lập căn bám lề.3018-Oct-153.1- Định dạng ô tính/miền ô tính:Bước 1: nhấn chọn ô/miền ô chứa dữ liệu cần định dạng Bước 2: nhấn chọn Home – Format – Format Cells: - Chọn thẻ Font để thiết lập phông chữ/cỡ chữ/kiểu chữ.3118-Oct-15Chọn màu chữChọn phông chữChọn hiệu ứng chữChọn cỡ chữKhông kẻ khungKhung bao ngoàiKhung bên trong Chọn từng kiểu đường kẻ Chọn kiểu đường kẻChọn màu đường kẻ3.1- Định dạng ô tính/miền ô tính:Bước 1: nhấn chọn ô/miền ô chứa dữ liệu cần định dạng Bước 2: nhấn chọn Home – Format – Format Cells:- Chọn thẻ Border để tạo khung viền xung quanh cho ô tính/miền ô tính3218-Oct-153.1 - Định dạng ô tính/miền ô tính: Bước 1: nhấn chọn ô/miền ô chứa dữ liệu cần định dạng  Bước 2: nhấn chọn Home – Format – Format Cells: - Chọn thẻ Fill để tô màu nền cho ô tính/miền ô tính đã chọn.3318-Oct-15Loại bỏ màu nền (trở lại nền màu trắng)Chọn màu nềnChọn kiểu nền3.1 - Định dạng ô tính/miền ô tính:Bước 1: nhấn chọn ô/miền ô chứa dữ liệu cần định dạng Bước 2: nhấn chọn Home – Format – Row Height để cân chỉnh độ cao dòng. 3418-Oct-15Chú ý: có thể đưa chuột đến vạch ranh giới giữa 2 dòng để xuất hiện rồi nhấn rê chuột để nới lỏng/thu hẹp kích thước.3.1 - Định dạng ô tính/miền ô tính:3518-Oct-15Bước 1: nhấn chọn ô/miền ô chứa dữ liệu cần định dạng Bước 2: nhấn chọn Home – Format – Column Width để cân chỉnh độ rộng cột. Chú ý: có thể đưa chuột đến vạch ranh giới giữa 2 cột để xuất hiện  rồi nhấn rê chuột để nới rộng/thu hẹp kích thước.3.2 - Thiết lập trang inThiết lập vùng in trên trang tính: Chọn miền ô tính chứa dữ liệu cần in rồi vào Page Layout- Print Area- Set Print Area.Thiết lập trang in: nhấn chọn Page Layout – Page Setup .Thẻ Page: Thẻ Page cho phép người dùng thiết lập điều hướng trang in (in dọc hay in ngang), khổ giấy in.3618-Oct-153.2 - Thiết lập trang inThẻ Margin: cho phép thiết lập lề trái, lề phải, lề trên, lề dưới, khoảng cách từ mép giấy đến tiêu đề trang (Header) và chân trang (Footer).3718-Oct-153.2 - Thiết lập trang inThẻ Header/Footer: Tạo tiêu đề và thiết lập tiêu đề và chân trang. 3818-Oct-153.2 - Thiết lập trang inThẻ Sheet: Tùy chọn thiết lập tiêu đề in, vùng in và tùy chọn in.3918-Oct-153.2 – Thiết lập trang in4018-Oct-15Xem trước bản inSố lượng bản inChọn máy inChọn trang in3.3 – Các định dạng khácĐịnh dạng Sheet backgroundĐịnh dạng Theme Theme là tập hợp định dạng và các thiết lập giao diện thống nhất có thể áp dụng cho toàn bộ sổ tính. Các thiết lập của Theme sẽ xác định font chữ, màu sắc, hiệu ứng cho các thành phần trong sổ tính.Người dùng có thể chỉnh sửa các Theme theo ý muốn bằng cách lựa chọn tập hợp màu sắc, font chữ và các hiệu ứng sử dụng trong Theme. Sau đó người dùng có thể lưu lại Theme này bằng tên mới và sử dụng lại cho sổ tính khác. Chương 4 - PM bảng tính điện tử MS EXCEL20104118-Oct-153.3 - Các thao tác cơ bảnChương 4 - PM bảng tính điện tử MS EXCEL20104218-Oct-15Chọn ô/miền ô/cột/dòng/trang tínhChọn dòng/cột: nhấn tại ô chứa số hiệu dòng/số hiệu cột thì toàn bộ dòng/cột đó sẽ được chọn. Chọn miền ô: nhấn rê trên miền ô tính từ ô đầu nằm ở góc trái trên đến ô cuối nằm ở góc phải dưới, nếu các ô đó là liền kề nhau; hoặc nhấn giữ phím CTRL rồi nhấn trên từng ô chọn , nếu các ô đó là không nằm liền kề nhau.Chọn toàn trang tính: nhấn vào ô nằm góc trái trên trang tính tức ô là giao của cột và dòng chứa số hiệu cột/dòng thì toàn bộ trang tính sẽ được chọn.4318-Oct-15NhấnvàođâyNhấn vào đâyNhấnvào đâyCác phím dịch chuyển con trỏ trong ô, , ,  dịch chuyển 1 ô theo hướng mũi tênPage Up dịch con trỏ lên 1 trang màn hình.Page Down dịch chuyển xuống 1 trang màn hình.Home về cột đầu tiên (cột A) của dòng hiện tạiCtrl +  về cột cuối cùng (cột IV) của dòng hiện tại.Ctrl +  về cột đầu tiên (cột A) của dòng hiện tại.Ctrl +  về dòng cuối cùng (dòng 65536) của cột hiện tại.Ctrl +  về dòng đầu tiên (dòng 1) của cột hiện tại.Ctrl +  +  về ô trái trên cùng (ô A1).Ctrl +  +  về ô phải trên cùng (ô IV1).Ctrl +  +  về ô trái dưới cùng (ô A65536).Ctrl +  +  về ô phải dưới cùng (ô IV65536).4418-Oct-15Nhập/Sửa dữ liệu Nhập dữ liệu: nhấn chuột tại ô tính cần nhập rồi nhập vào nội dung dữ liệu. Nếu độ dài dữ liệu vượt quá độ rộng ô thì nó tự động “lấn” sang ô bên cạnh, còn nếu bạn muốn dữ liệu tự động “bẻ gẵy” xuống dòng để phù hợp với độ rộng ô thì nhấn phải chuột, chọn Format Cells- Alignment rồi chọn  Wrap text. Sửa dữ liệu: nhấn chọn ô chứa dữ liệu cần sửa rồi nhấn F2, khi đó bạn sửa trực tiếp dữ liệu trên ô tính.4518-Oct-151/ Nhập đồng thời các ô cùng 1 nội dung dữ liệu: nhấn chọn các ô tính cần nhập, nhập vào nội dung dữ liệu rồi nhấn đồng thời Ctrl + Enter. 2/ Nhập nhanh một dãy số theo qui luật: - Nhập 2 số liên tiếp của dãy vào 2 ô tính cạnh nhau - Chọn 2 ô tính vừa nhập - Đưa trỏ chuột đến góc phải dưới ô để xuất hiện dấu  (FillHand ) rồi nhấn rê chuột đến ô cuối cùng của dãy số.3/ Nhập nhanh một dãy ngày tháng tăng dần: - Nhập biểu thức ngày vào ô bất kỳ trong dãy. - Chọn ô vừa nhập và nhấn rê chuột tại dấuFillHand đến ô cuối cùng của dãy.18-Oct-1546Nhập nhanh dữ liệu trong một số trường hợp đặc biệt4/ Tạo 1 dãy dữ liệu có qui luật và tự động điền khi cần: - Nhập các giá trị của dãy dữ liệu vào các ô liền kề, rồi nhấn chọn dãy các ô đó. Xong, nhấn Ctrl +C. - Nhấn File, chọn , chọn Advanced -> Edit Custom Lists 18-Oct-1547Nhập nhanh dữ liệu4/ Tạo 1 dãy dữ liệu có qui luật và tự động điền khi cần: - Nhấn sẽ hiện dãy trong khung List entries, xong nhấn, , OK. - Khi muốn điền dãy đã tạo vào vị trí nào thì chỉ việc nhập giá trị đầu tiên của dãy, rồi nhấn rê tại Fillhand đến ô cuối cùng của dãy. 18-Oct-1548+Nhập nhanh dữ liệuXóa/Sao chép/Di chuyển dữ liệu/trang tính Xóa ô tính: bôi đen dữ liệu, nhấn vào Home – Delete, chọn tiếp Delete Cells 4918-Oct-15Các ô bên phải sẽ dồn sang trái, chiếm vị trí ô bị xóa. Các ô phía dưới sẽ đẩy lên trên, chiếm vị trí ô bị xóa. Xóa dòng chứa ô đang chọnXóa cột chứa ô đang chọnXóa/Sao chép/Di chuyển dữ liệu/trang tính  Xóa trang tính: đưa chuột đến tên trang tính cần xóa, nhấn chuột phải, chọn Delete5018-Oct-15Xóa/Sao chép/Di chuyển dữ liệu/trang tính  Sao chép/di chuyển dữ liệu: Chọn ô tính hoặc miền ô chứa dữ liệu cần sao chép/di chuyển  Nhấn chuột phải, chọn Copy/Cut  Nhấn vào ô tính sẽ sao chép/di chuyển sang  Nhấn chuột phải, chọn Paste.  Sao chép/di chuyển trang tính: nhấn giữ phím Ctrl/Shift rồi nhấn rê chuột tới tên trang tính hiện thời sang vị trí mới, xong thả chuột. 5118-Oct-15 Trang tính ban đầuTrang tính sao chép Nhấn giữCtrlNhấn giữ Shift4. Tổ chức trang tính bằng bảngBảng là cấu trúc dữ liệu gồm các cột và các dòng liền kề cho phép người dùng thiết lập các tùy chọn định dạng để làm cho dữ liệu thống nhất và chuyên nghiệp. Bảng trong tranh tính Excel có thể được định dạng, quản lý và phân tích như một đối tượng riêng biệt. Sử dụng bảng giúp thao tác với dữ liệu dễ hơn, đồng thời cung cấp các hàm chuyên sử dụng cho bảng và các tùy chọn định dạng bảng. Bảng chứa tiêu đề dòng và tiêu đề cột để giúp người dùng tổ chức dữ liệu dễ nhìn hơn.Chương 4 - PM bảng tính điện tử MS EXCEL20105218-Oct-154.1 - Tạo bảngĐể tạo bảng chọn thẻ Insert -> Table -> Xuất hiện hộp thoại Creat TableHộp thoại Creat Table cho phép người dùng chọn vùng dữ liệu để tạo bảng và có tùy chọn thêm tiêu đề cho bảng.Chương 4 - PM bảng tính điện tử MS EXCEL20105318-Oct-154.1 – Tạo bảng với Table Quick StyleĐể truy cập thư viện Table Quick Style, chọn thẻ Home – Format as Table5418-Oct-154.2 – Định dạng bảngKhi nhấn chọn bảng sẽ xuất hiện thẻ ngữ cảnh Table Tool Design chứa các công cụ để thiết kế bảng trong trang tính5518-Oct-15Nhóm ribbonMô tảPropertiesCho phép người dùng thay đổi kích thước của bảng và chỉnh sửa tên bảng.ToolsCung cấp cho người người dùng các tùy chỉnh để tạo PivotTables, loại bỏ dữ liệu trùng lặp và chuyển một bảng thành dải các ô tính.External Table DataCho phép người dùng chia sẻ dữ liệu với các chương trình bên ngoài cũng như tự động cập nhật sổ tính với dữ liệu bên ngoài. Ngoài ra người dùng có thể ngắt liên kết với nguồn dữ liệu ngoài bảng.Table Stye OptionsCó các tùy chọn như: thêm/bỏ dòng tiêu đề, dòng tổng kết, thêm/bỏ định dạng cột đầu tiên, cột cuối cùng, thêm/bớt kẻ khung các dòng, các cột.Table StyleCung cấp các định dạng bảng được thiết kế sẵn.4.3 – Sắp xếp và lọc dữ liệuSắp xếp: Sắp xếp là phương thức hiển thị dữ liệu được liệt kê the thứ tự xác định. Dữ liệu có thể được sắp xếp theo chiều tăng hay giảm phụ thuộc vào kiểu dữ liệu mà nó chứa. Dữ liệu có thể sắp xếp dựa trên một hoặc nhiều điều kiện.5618-Oct-154.3 – Sắp xếp và lọc dữ liệuVí dụ: tạo ra danh sách cán bộ theo vần alphabel của trình độ, những người cùng trìnhđộ thì thu nhập tăng dần. 5718-Oct-15 Ý nghĩa: sau sắp xếp thì tạo ra các nhóm dữ liệu.4.3 – Sắp xếp và lọc dữ liệuCách thực hiện: chọn miền CSDL (A2:I10), vào Data - Sort5818-Oct-15Cột ưu tiên thứ nhất trong điều kiện sắp xếpCột ưu tiên thứ 2Thêm vào các mức ưu tiênSao chép mức ưu tiên đang chọnXóa mức ưu tiên đang chọn Thiết lập 1 số tùy chọnVí dụ: sắp xếp danh sách cán bộ tăng dần theo giới tính, những người cùng giới tính thì tăng dần theo trình độ.4.3 – Sắp xếp và lọc dữ liệu5918-Oct-15Xếp từ trên xuống dướiXếp từ trái sang phảiPhân biệt chữ hoa với chữ thườngMiền dữ liệu có chứa dòng tiêu đềNếu nhấn chọn Options thì hiện tiếp hộp thoại Sort Options:4.3 – Sắp xếp và lọc dữ liệu6018-Oct-154.3 – Sắp xếp và lọc dữ liệuLọc dữ liệu: Là phương thức hiển thị dữ liệu phù hợp với tập hợp các tiêu chuẩn cụ thể.Trường hợp 1: Lọc tự động (AutoFilter) - Excel hỗ trợ điều kiện lọcBước 1: chọn miền dữ liệu cần lọc (kể cả dòng tiêu đề)Bước 2: vào menu Data - chọn Filter 6118-Oct-154.3 – Sắp xếp và lọc dữ liệu6218-Oct-15Ví dụ: Lọc những mặt hàng là Đĩa cứng có số lượng bán ra trong khoảng [50,100)Nhấn chọn mục Đĩa cứngCác dạng điều kiện cho việc chọn dữ liệu kiểu chuỗi4.3 – Sắp xếp và lọc dữ liệu6318-Oct-15Ví dụ: lọc những mặt hàng là Đĩa cứng có số lượng bán ra trong khoảng [50,100)Chọn và nhập cho biểu thức điều kiện 4.3 – Sắp xếp và lọc dữ liệu6418-Oct-15Ví dụ: Lọc những mặt hàng là Đĩa cứng có số lượng bán ra trong khoảng [50,100)Kết quả như sau:4.3 – Sắp xếp và lọc dữ liệu Trường hợp 2: Lọc nâng cao (Advance Filter) - được dùng khi điều kiện lọc là phức tạp. Bước 1: biểu diễn vùng tiêu chuẩn mô tả điều kiện lọc (cách thức giống như biểu diễn vùng tiêu chuẩn của hàm CSDL).Dòng đầu ghi tên trường liên quan đến điều kiện (chú ý phải giống hệt tên trường của CSDL - tốt nhất là copy từ tên trường CSDL).Các dòng tiếp dưới ghi điều kiện: các điều kiện cùng dòng là phép AND, các điều kiện khác dòng là phép OR.6518-Oct-15Miền đ/k để lọc các bản ghi có số SP bán ra trong tháng 1 là 400Miền đ/k để lọc các bản ghi có số SP bán ra trong tháng 1 là lớn hơn 150Miền đ/k để lọc các bản ghi có số SP bán ra trong tháng 1 là: 150 >= Mức ưu tiên 3 phép toán trên theo thứ tự sau: Dấu ngoặc đơn ( ) 1 (cao nhất) Lũy thừa (^) 2 Nhân (*) và chia (/ ) 3 Cộng (+) và trừ (-) 4 Quan hệ 5 Logic 6 Nếu các phép toán trong một biểu thức có cùng mức ưu tiên thì sẽ thực hiện từ trái sang phải.18-Oct-1582Chương 5 - PM bảng tính điện tử MS EXCEL2007Các phép toán đối với từng kiểu dữ liệuKiểu ngày thángLà một bộ phận của dữ liệu kiểu số, trong dạng hiển thị có dấu phân cách ngày, tháng, năm (bằng dấu / hoặc – hoặc bằng chữ tiếng Anh). Ngầm định thì dữ liệu kiểu ngày tháng luôn căn bám lề phải của ô tínhPhép toán cộngPhép toán trừPhép toán quan hệ dựa trên nguyên tắc: ngày hôm sau lớn hơn ngày hôm quaChương 4 - PM bảng tính điện tử MS EXCEL20108318-Oct-15Các phép toán đối với từng kiểu dữ liệuKiểu chuỗi: là bắt đầu bằng chữ cái. Ở chế độ ngầm định thì dữ liệu kiểu chuỗi luôn căn bám lề trái của ô tính. Phép toán quan hệ, bao gồm: =, , ==, Phép toán ghép chuỗi: &Chú ý: nếu dùng phép toán & trên các dữ liệu kiểu số thì kết quả cho ra là chuỗi chữ số.Chương 4 - PM bảng tính điện tử MS EXCEL20108418-Oct-15Công thức trong excelCông thức trong Excel là một tập hợp các câu lệnh toán học sử dụng để tính toán dữ liệu trong trang tính Excel. Các công thức trong Excel thường bắt đầu với dấu “=”, sau đó là các thành phần như toán tử, hàm, tham chiếu, hằng số. Một công thức trong Excel có thể chứa một vài hoặc tất cả các thành phần trên. Khi dữ liệu thay đổi thì kết quả trong công thức cũng tự động thay đổi theo.Chương 4 - PM bảng tính điện tử MS EXCEL20108518-Oct-15Các hàm cơ bản trong excelHàm là công thức được xây dựng sẵn trong Excel dùng để xử lý các thao tác tính toán.Dạng tổng quát của hàm: Tên hàm (Danh sách các đối số)Tên hàm viết chữ hoa hoặc chữ thường. Tên hàm thường gắn với ý nghĩa của các thao tác mà nó cung cấp.Danh sách các đối số là các giá trị đầu vào cung cấp dữ liệu để hàm xử lýChương 4 - PM bảng tính điện tử MS EXCEL20108618-Oct-15Một số nhóm hàm thông dụngFinancial: hàm tài chínhDate & Time: hàm ngày thángMath & Trig: hàm toán và lượng giácStatistical: hàm thống kêLookup & Reference: hàm tìm kiếm và tham chiếuDatabase: hàm cơ sở dữ liệuText: hàm xử lý văn bảnLogic: hàm luận lýInformation: hàm thông tinChương 4 - PM bảng tính điện tử MS EXCEL20108718-Oct-1518-Oct-1588Chương 4 - PM bảng tính điện tử MS EXCEL2010Ký hiệuÝ nghĩaNhững trường hợp gây lỗi#####Lỗi độ rộng- Khi cột thiếu độ rộng. - Khi bạn nhập giá trị ngày tháng hoặc thời gian là số âm.#VALUE!Lỗi giá trị Nhập vào 1 chuỗi trong khi hàm yêu cầu nhập 1 số/ 1 giá trị logic. Đang nhập 1 số hoặc các đối số mà nhấn Enter. Nhập quá nhiều tham số cho một toán tử hoặc một hàm trong khi chúng chỉ dùng một tham số. #DIV/0!Lỗi chia cho 0 Nhập vào công thức số chia là 0. Số chia trong công thức là một tham chiếu đến ô trống.#NAME!Sai tên Nhập sai tên một hàm số. Dùng những ký tự không được phép trong công thức. Nhập một chuỗi trong công thức mà không đặt trong dấu “ ”.- Trong công thức không có dấu 2 chấm : trong dãy địa chỉ ô.Một số thông báo lỗi thường gặp18-Oct-1589Chương 4 - PM bảng tính điện tử MS EXCEL2010Ký hiệuÝ nghĩaNhững trường hợp gây lỗi#N/ALỗi dữ liệu Không đồng nhất dữ liệu Giá trị trả về không tương thích từ các hàm dò tìm #REF!Sai vùng tham chiếu Xóa những ô đang được tham chiếu bởi công thức. Dán những giá trị tạo ra từ công thức lên chính vùng tham chiếu đó.- Tham chiếu đến 1 ứng dụng không thể chạy được.#NUM!Lỗi dữ liệu kiểu số Dùng một đối số không phù hợp trong công thức. Dùng hàm lặp đi lặp lại dẫn đến hàm không tìm được kết quả trả về. Dùng hàm trả về 1số quá lớn/nhỏ so với khả năng tính toán của Excel#NULL!Lỗi dữ liệu rỗng Dùng một dãy toán tử không phù hợp Một số thông báo lỗi thường gặpNhóm hàm ngày (Date & Time)HÀMNOW()Cho biết ngày, giờ hiện thờiTODAY()Cho biết ngày hiện thờiDAY(serial_number)Cho biết ngày của serial_numberMONTH(serial_number)Cho biết tháng của serial_numberYEAR(serial_number)Cho biết năm của serial_numberChương 4 - PM bảng tính điện tử MS EXCEL20109018-Oct-15Nhóm hàm toán học (Math & trig)HÀMVÍ DỤFACT(number)Tính giai thừa của number= FACT(4) Trả về kết quả 24INT(number)Lấy phần nguyên của number= INT(123.7) Trả về kết quả 123MOD(number,divisor)Phần dư của phép chia number cho divisor= MOD(23,5)Trả về kết quả 3SQRT(number)Căn bậc hai của number=SQRT(144)Trả về kết quả 12PRODUCT(number1, number2,...)Tích các số number1, number2,...=PRODUCT(9,8,7)Trả về kết quả 504SUM(number1, number2,...)Tổng các số number1, number2,...=SUM(15.8,35.6) Trả về kết quả 51.4ROUND(number, num_digits)Làm tròn số number với num_digits chữ số phần thập phân= ROUND(35.67, 1)Trả về kết quả 35.7LN(number)Tính ln=LN(1)Trả về kết quả 1Chương 4 - PM bảng tính điện tử MS EXCEL20109118-Oct-15Nhóm hàm luận lý (Logical)HÀMVÍ DỤAND(logical1,logical2,..) Cho trị TRUE nếu mọi đối số là TRUE, ngược lại cho trị là FALSE=AND(3=0) Trả về kết quả TRUE=AND(3>7,2>=0) Trả về kết quả FALSEOR(logical1,logical2,..) Cho trị TRUE nếu bất kỳ đối số nào là TRUE, cho trị là FALSE nếu tất cả đối số là FALSE=OR(3>7, 2>=0)Trả về kết quả TRUENOT(logical) Cho trị logic ngược lại với trị logic của đối số=NOT(6=5, "Trung bình”, "Kém”)Trả về kết quả "Trung bình”.Chương 4 - PM bảng tính điện tử MS EXCEL20109218-Oct-15Nhóm hàm xử lý chuỗi (Text)HÀMVÍ DỤLEFT(text, num_chars) Giữ lại num_chars ký tự bên trái chuỗi text= LEFT(“Tin học ứng dụng”, 7)Trả về kết quả “Tin học”RIGHT(text, num_chars)Giữ lại num_chars ký tự bên phải chuỗi text=RIGHT(“Tin học ứng dụng”,8)Trả về kết quả “ứng dụng”MID(text, start_num,num_chars) Trả về num_chars ký tự kể từ ký tự thứ start_num của chuỗi=MID(“Tin học ứng dụng”,4,3)Trả về kết quả “học”LEN(text)Độ dài chuỗi= LEN(“Tin học ứng dụng”)Trả về kết quả 16PROPER(text)Trả về chuỗi có chữ in hoa ở đầu mỗi từ=PROPER(“Tin học ứng dụng”)Trả về kết quả “Tin Học Ứng Dụng”UPPER(text) Đổi thành chuỗi in hoa=UPPER(“Tin học”)Trả về kết quả “TIN HỌC”LOWER(text)Đổi thành chuỗi in thườngLOWER(“HA NOI”) = “ha noi”VALUE(text) Đổi chuỗi các chữ số thành dãy các con sốVALUE(“0834”) = 834Chương 4 - PM bảng tính điện tử MS EXCEL20109318-Oct-15Nhóm hàm thống kê (Statistical)HÀMÝ NGHĨAMIN(number1,[number2],)Trả về giá trị nhỏ nhất trong các đối sốMAX(number1,[number2],) Trả về giá trị lớn nhất trong các đối sốAVERAGE(number1,[number2],)Trả về giá trị trung bình của các đối sốCOUNT(value1,[value2],)Đếm số lượng các ô kiểu số trong miền ôCOUNTA(value1,[value2],)Đếm số lượng các ô khác rỗng có trong miền ôCOUNTBLANK(range)Đếm số lượng các ô trống trong miền ôCOUNTIF(range,criteria) Đếm số lượng các ô thoả điều kiện criteria đưa ra có trong miền ô rangeSUM(number1,[number2],) Tính tổng các giá trị số trong miền ôSUMIF(range,criteria,[sum_range]) Tính tổng các giá trị số trong sum_range thoả điều kiện criteria có trong miền ô chứa điều kiện rangeChương 4 - PM bảng tính điện tử MS EXCEL20109418-Oct-15Nhóm hàm dò tìm và tham chiếu (Lookup and reference)HÀMÝ NGHĨAINDEX(Array,Row_num,Column_num)Cho nội dung ô tính trong miền ô dò tìm Array, ô tính này có chỉ số dòng là Row_num và chỉ số cột là Column_numMATCH(lookup_value,lookup_array, match_type)Cho vị trí tương đối của lookup_value trong dãy dò tìm lookup_array (dãy chỉ là dòng hay cột) tương ứng với kiểu tìm match_type. LOOKUP(lookup_value,lookup_vector, result_vector)Tìm kiếm và trả về giá trị có trong dãy result_vector ở vị trí tương ứng với lookup_value trong dãy dò tìm lookup_vector.HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)Tìm giá trị lookup_value ở dòng đầu tiên trong miền ô dữ liệu table_array có chứa lookup_value. Nếu tìm thấy sẽ trả về giá trị ô tương ứng ở dòng thứ row_index_num. Nếu không tìm thấy sẽ đưa ra thông báo #N/A (Not Available). Range_lookup là tham số tuỳ chọn.VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)Tìm giá trị lookup_value ở cột đầu tiên trong miền ô dữ liệu table_array có chứa lookup_value. Nếu tìm thấy sẽ trả về giá trị ô tương ứng ở cột thứ col_index_num. Nếu không tìm thấy sẽ đưa ra thông báo #N/A (Not Available). Range_lookup là tham số tuỳ chọn.Chương 4 - PM bảng tính điện tử MS EXCEL20109518-Oct-15Nhóm hàm cơ sở dữ liệu (Database)TÊN_HÀM_CSDL (database,field,criteria)Database: Vùng CSDL là toàn bộ hoặc một phần CSDL ban đầu.Field: Tên trường (thông qua địa chỉ ô chứa tên trường) hoặc tương ứng là cột thuộc tính thứ n trong vùng CSDL đã chọn.Criteria: Vùng tiêu chuẩn là miền ô chứa tên trường và biểu thức chứa điều kiện của các bản ghi chịu tác động của hàm.Chương 4 - PM bảng tính điện tử MS EXCEL20109818-Oct-15Một số hàm CSDLTên hàmÝ nghĩaDAVERAGE(database,field,criteria)Tính trung bình các giá trị thuộc tên trường/cột n trong vùng CSDL theo điều kiện được diễn tả ở vùng tiêu chuẩn. DMAX/DMIN(database,field,criteria)Lấy ra giá trị lớn nhất/nhỏ nhất thuộc tên trường/ cột n trong vùng CSDL theo điều kiện được diễn tả ở vùng tiêu chuẩn.DGET(database,field,criteria)Lấy ra giá trị thuộc tên trường/ cột n trong vùng CSDL theo điều kiện được diễn tả ở vùng tiêu chuẩn.DCOUNT(database,field,criteria)Đếm số lượng các giá trị kiểu số thuộc tên trường/ cột n trong vùng CSDL theo điều kiện được diễn tả ở vùng tiêu chuẩn.DCOUNTA(database,field,criteria)Đếm số lượng ô không trống thuộc tên trường/ cột n trong vùng CSDL theo điều kiện được diễn tả ở vùng tiêu chuẩn.DSUM(database,field,criteria)Tính tổng cộng các giá trị thuộc tên trường/ cột n trong vùng CSDL theo điều kiện được diễn tả ở vùng tiêu chuẩn.Chương 4 - PM bảng tính điện tử MS EXCEL20109918-Oct-15Ví dụChương 4 - PM bảng tính điện tử MS EXCEL201010018-Oct-15Phân nhóm dữ liệu (SUBTOTAL)Bước 1: tiến hành sắp xếp dữ liệu (tăng dần hoặc giảm dần) trên trường cần phân nhóm dữ liệu. Bước 2: lựa chọn vùng CSDL.Bước 3: Data – Subtotal. Chương 4 - PM bảng tính điện tử MS EXCEL201010118-Oct-15Ví dụTính tổng thu nhập theo từng nhóm giới tínhChương 4 - PM bảng tính điện tử MS EXCEL201010218-Oct-15Liên kết dữ liệu (tính năng GROUP and OUTLINE)Tình huống 1: Người dùng chủ động thực hiện liên kết các dòng/cột liền kề vào thành từng nhómBước 1: (chỉ thực hiện khi muốn liên kết các dòng dữ liệu vào từng nhóm): sắp xếp dữ liệu trên trường sẽ phân nhóm dữ liệu. Bước 2: chọn các dòng/ cột dữ liệu cần liên kết trong một nhóm. Bước 3: chọn Data – Group – Group Chương 4 - PM bảng tính điện tử MS EXCEL201010318-Oct-15Ví dụChương 4 - PM bảng tính điện tử MS EXCEL201010418-Oct-15Liên kết dữ liệu (tính năng GROUP and OUTLINE)Tình huống 2: Excel sẽ tự động thực hiện liên kết cột khi phát hiện thấy dữ liệu trên các cột liền kề có liên quan lẫn nhau qua công thức/hàmBước 1: Đưa chuột đến ô bất kì trong vùng dữ liệu.Bước 2: Nhấn chọn Data - Group - Auto OutLine => sẽ tự động xuất hiện các liên kết dòng/cột theo các cấp có thể được. Chú ý: nếu trong CSDL không có mối liên kết giữa các cột liền kề thì Excel sẽ từ chối thực hiện tính năng Auto OutLine.Chương 4 - PM bảng tính điện tử MS EXCEL201010518-Oct-15Bảng tham chiếu chéo - Pivot TableLựa chọn CSDLInsert - PivotTable/ PivotChart Chương 4 - PM bảng tính điện tử MS EXCEL201010618-Oct-15Cấu trúc bảng PivotTableChương 4 - PM bảng tính điện tử MS EXCEL201010718-Oct-15Mức tổng hợp dữ liệu cao nhất Thống kê/tính toán trên dữ liệu Phân tích dữ liệu Report FilterRowLabelsColumn Labels∑ ValuesMức tổng hợp dữ liệu cao thứ nhì Ví dụChương 4 - PM bảng tính điện tử MS EXCEL201010818-Oct-15Với mỗi Tên đơn vị vay, cho biết Ngày vay và Thời hạn vay tương ứng cùng với Tổng số tiền phải trả của đơn vị vay đóVí dụChương 4 - PM bảng tính điện tử MS EXCEL201010918-Oct-15Biểu diễn dữ liệu bằng biểu đồChương 4 - PM bảng tính điện tử MS EXCEL201011018-Oct-15Phân tích xu hướng dữ liệu SparklineTrendlineChương 4 - PM bảng tính điện tử MS EXCEL201011118-Oct-15