MS EXCEL NÂNG CAO

Giới thiệu công thức (Formula) Công thức giúp bảng tính hữu ích hơn rất nhiều, nếu không có các công thức thì bảng tính cũng giống như trình soạn thảo văn bản. Chúng ta dùng công thức để tính toán từ các dữ liệu lưu trữ trên bảng tính, khi dữ liệu thay đổi các công thức này sẽ tự động cập nhật các thay đổi và tính ra kết quả mới g
Nội dung trích xuất từ tài liệu:
MS EXCEL NÂNG CAOTrung tâm ngoại ngữ - tin học Úc Anh - APT 1 II. MS EXCEL NÂNG CAO BÀI 1: GIỚI THIỆU VỀ HÀM1. Giới thiệu công thức (Formula) Công thức giúp bảng tính hữu ích hơn rất nhiều, nếu không có các công thức thì bảng tính cũnggiống như trình soạn thảo văn bản. Chúng ta dùng công thức để tính toán từ các dữ liệu lưu trữ trênbảng tính, khi dữ liệu thay đổi các công thức này sẽ tự động cập nhật các thay đổi và tính ra kết quảmới giúp chúng ta đỡ tốn công sức tính lại nhiều lần. Vậy công thức có các thành phần gì?Công thức trong Excel được nhận dạng là do nó bắt đầu là dấu = và sau đó là sự kết hợp của các toántử, các trị số, các địa chỉ tham chiếu và các hàm.Ví dụ:Ví dụ về công thứcBảng 1. Các toán tử trong công thứcTrung tâm ngoại ngữ - tin học Úc Anh - APT 2Bảng 2. Thứ tự ưu tiên của các toán tử2. Giới thiệu hàm (Function) Hàm trong Excel được lập trình sẵn dùng tính toán hoặc thực hiện một chức năng nào đó. Việcsử dụng thành thạo các hàm sẽ giúp chúng ta tiết kiệm được rất nhiều thời gian so với tính toán thủcông không dùng hàm. Các hàm trong Excel rất đa dạng bao trùm nhiều lĩnh vực, có những hàm khôngyêu cầu đối số, có những hàm yêu cầu một hoặc nhiều đối số, và các đối số có thể là bắt buộc hoặc tựchọn.Trung tâm ngoại ngữ - tin học Úc Anh - APT 3Ví dụ:=Rand() hàm không có đối số=If(A1>=5,”Đạt”,”Rớt”) hàm 3 đối số=PMT(10%,4,1000,,1) hàm nhiều đối số và đối số tùy chọnTrong Excel 2007 có tổng cộng 12 hàm mới: AverageIf, AverageIfs, CountIfs, IfError, SumIfs vànhóm hàm Cube. Excel 2007 có các nhóm hàm chính như:  Hàm ngoại: Call, Registed.ID,…  Hàm lấy dữ liệu từ SSAS: Cubeset, Cubevalue,…  Hàm dữ liệu: Dmin, Dmax, Dcount,…  Hàm ngày và thời gian: Time, Now, Date,….  Hàm kỹ thuật: Dec2Bin, Dec2Hex, Dec2Oct,…  Hàm tài chính: Npv, Pv, Fv, Rate,…  Hàm thông tin: Cell, Thông tin, IsNa,…  Hàm luận lý: If, And, Or,…  Hàm tham chiếu và tìm kiếm: Choose, Vlookup, OffSet,…  Hàm toán và lượng giác: Log, Mmult, Round,…  Hàm thống kê: Stdev, Var, CountIf,…  Hàm văn bản: Asc, Find, Text,…3. Các lỗi thông dụng (Formulas errors) Các lỗi thông dụng Lỗi Giải thích #DIV/0! Trong công thức có chứa phép chia cho 0 (zero) hoặc chia ô rỗng #NAME? Do dánh sai tên hàm hay tham chiếu hoặc đánh thiếu dấu nháy #N/A Công thức tham chiếu đến ô mà có dùng hàm NA để jiễm tra sự tồn tại của dữ liệu hoặc hàm không có kết quả #NULL! Hàm sử dụng dữ liệu giao nhau của 2 vùng mà 2 vùng này không có phần chung nên phần giao rỗng #NUM! Vấn đề đối với giá trị, ví dụ như dùng nhầm số âm trong khi đúng phải là số dươngTrung tâm ngoại ngữ - tin học Úc Anh - APT 4 #REF! Tham chiếu bị lỗi, thường là do ô tham chiếu trong hàm bị xóa #VALUE! Công thức tính toán có chứa kiểu dữ liệu không đúng.4.Tên vùng và sử dụng tên vùng trong công thứcGiải thích thuật ngữ:Range: có thể chỉ là 1 cell, có thể là 1 dãy các cell liền nhau theo hàng hoặc theo cột, có thể một vùngchọn hình chữ nhật(hình vuông). Ngoài ra Range lại có thể bao gồm các cell, các dãy cell không liêntiếp hoặc Range 3D nằm rải rác ở các sheet khác nhau. Trong bài này tôi dùng từ “vùng chọn” để thaythế cho range.Tọa độ của 1 vùng chọn (range): là địa chỉ từ ô đầu tiên đến ô cuối cùng của 1 vùng chọn. Ví dụ:A1:C10, F1:F10…a. Giới thiệu về Name-Mặc dù các vùng chọn vẫn cho phép bạn làm việc một cách có hiệu quả với các nhóm ô lớn, nhưng cómột số khuyết điểm khi sử dụng các tọa độ của một vùng chọn:  Bạn không thể làm việc với nhiều hơn một tập hợp các dãy ô trong một lần. Mỗi lần bạn muốn sử dụng một vùng chọn, bạn phải định nghĩa lại tọa độ của nó.  Để biết một công thức chẳng hạn như =SUM(E6:E10) đang cộng những giá trị gì, bạn phải xem chính vùng chọn đó mới biết.Trung tâm ngoại ngữ - tin học Úc Anh - APT 5  Một sai sót nhỏ trong việc định nghĩa các tọa độ của vùng chọn có thể dẫn đến kết quả tệ hại, nhất là khi bạn xóa một vùng chọn.-Bạn có thể khắc phục những vấn đề trên bằng cách sử dụng tên cho vùng chọn (range name), là cáctên được gán cho một ô hoặc một dãy ô. Với một tên đã được định nghĩa, bạn có thể sử dụng nó thaythế cho các tọa độ của vùng chọn.Ví dụ: để đưa vùng chọn vào công thức, bạn sử dụng tên của vùng chọn đó thay vì chọn vùng chọnhoặc nhập tọa độ của nó. Bạn có thể tạo bao nhiều tên cho ...