Những Hàm cơ bản trong Excel
Yüklə 165,86 Kb.
|
Những Hàm cơ bản trong ExcelHÀM CƠ BẢN TRONG EXCEL Microsoft Excel, là một phần mềm ứng dụng rất hay. Nó là một công cụ để thống kê, tính toán và quản lý nhân sự và tài chính. Và là một công cụ hỗ trợ đắc lực cho giới kế toán. Chính vì vậy nó được áp dụng và sử lý rộng rãi trong các công ty, xí nghiệp hiện nay. Do giới hạn của quyển sách này, cũng như để cho đúng với cái tiêu đề của nó. Chính vì vậy tôi chỉ để cập đến một số phần quan trọng, trước hết tôi xin giới thiệu 60 hàm cơ bản của Excel:
Đếm số từ trong một ơ (cell) của Excel 2003 Bạn dng cơng thức sau để đếm số từ trong một ô: =IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1) BẢNG MÃ LỖI
SAO CHÉP: Edit/Paste Special…
q Skip blanks: bỏ qua những ô rỗng(không chưa dữ liệu) q Transpose: Sao chép chuyển vị(cột thành dòng, và ngược lại)  Paste Link: Sao chép có liên kết, nếu dữ liệu thay đổi, dữ liệu đích cũng thay đổi theo.
ĐỊA CHỈ:
Là cách gọi để phân biệt ô hoặc khối ô giữa các ô và khối ô khác trong bảng tính. Địa chỉ ô: là sự kết hợp giữa ký hiệu cột và dòng Địa chỉ khối: là sự kết hợp giữa ô đầu và ô cuối, ngăn cách nhau bởi dấu 2 chấm(:). PHÂN LOẠI: Có 3 loại: Địa chỉ tương đối: khi thay đổi ô kết quả thì địa chỉ của các ô tham chiếu cũng thay đổi theo. Địa chỉ tuyệt đối: khi thay đổi ô kết quả thì địa chỉ của ô tham chiếu không thay đổi theo. Vd:$Column $row Địa chỉ hỗn hợp: là sự kết hợp giữa địa chỉ tương đối và tuyệt đối. Vd:$Column row CÁC KIỂU DỮ LIỆU: Kiểu chuỗi: Bao gồm các ký tự :A-Z, a-z, 0-9 Đặc điểm: Luôn được canh trái, khi sử dụng kiểu chuỗi trong công thức luôn được đặt trong dấy nháy kép(“”). Các phép toán: & Kiểu số: Bao gồm 0-9 Đặc điểm: luôn được canh phải trong ô Các phép toán: +, -, *, /, ^, %. Kiểu lý luận: Là kiểu logic, thường được dùng làm điều kiện trong các hàm so sánh. Đặc điểm: Luôn trả về 1 trong 2 giá trị là: True hoặc False CÁC PHÉP TOÁN:
Kiểu ngày giờ:Date, Time: Kiểu ngày: Date Đặc điểm: Luôn được canh phải trong ô, không có phép cộng ngày Các phép toán: “Ngày”-“Ngày” = Số “Ngày” ± Số = Ngày Kiểu giờ: Time Đặc điểm: luôn được canh phải trong ô, không có phép trừ cho số Các phép toán: “Giờ “ ± “Giờ” = Giờ Kiểu công thức: Bao gồm các toán tử, toán hàm, địa chỉ, hàm… Đặc điểm: Luôn bắt đầu bằng dấu bằng( = ), mức độ ưu tiên trong công thức giống như trong toán học: nhân chia trước, cộng trừ sau. Trong ngoặc trước, ngoài ngoặc sau. Công thức mảng: là lập công thức để tính kết quả cho khối cột hoặc khối dòng. Khi xây dựng công thức mảng luôn được kết thúc bởi :Ctrl+Shift+Enter Vd: Tổng số mặt hàng tivi: =Countif(A2:A5),”tv.*” = count(if(left(A2:A5,2)=”TV”,1,””) Vd2: tính tổng thành tiền các mặt hàng TV có số lượng <> =Sum(if(left(A2:A5,2)=”TV”, IF(B2<8,> ĐỊNH DẠNG: Format/Cells…Tab Number: General: Định dạng chung cho kiểu dữ liệu Number: Định dạng cho các dữ liệu số +Decimal Place(2) – quy định mấy số lẻ +Use 100 Suparator(,) – phân cách hàng ngàn Currency: Quy định kiểu tiền tệ Date: Lựa chọn cách biểu diễn dữ liệu dạng ngày Time: Lựa chọn cách biểu diễn dữ liệu dạng ngày Percentage:Quy định cách biểu diễn dữ liệu kiểu phần trăm(%) Scientifie: Biểu diễn dữ liệu số kiểu khoa học. Vd:1.000.000=1*106=1E+6 Vd2: -1.000.000= -1*106= -1E+6 Fraction: Biểu diễn theo kiểu phân số: 0.5 – ½ - 2/4 Zipcode: Biểu diễn 1 mã số Custom: Tùy ý định dạng cho các kiểu dữ liệu. Chú ý: 0 – đại diện cho 1 ký tự số, nếu không có thì được thay thế bởi số 0. # - đại diện cho 1 ký tự số, nếu không có thì được bỏ trống. ĐỊNH DẠNG ĐIỀU KIỆN: Là định dạng dữ liệu mà bảng tính căn cứ vào điều kiện: Tô khối vùng dữ liệu, chọn: Format/Conditional formatting Add: thêm điều kiện Delete: xóa điều kiện Format: định dạng bảng tính căn cứ vào điều kiện Trong Condtion1 có hai lựa chọn: 1-Cell Value is : Định dạng theo giá trị trong ô 2-Formula is : Định dạng theo công thức Chú ý: thiết lập công thức luôn có dấu “=”, luôn trả về kết quả True hoặc False Sử dụng địa chỉ đầu tiên của khối để thiết lập công thức. Trong trường hợp căn cứ vào điều kiện 1 dòng(mẫu tin)thì cố định địa chỉ cột cho các địa chỉ sử dụng trong công thức. SẮP XẾP: Tô khối bảng dữ liệu, chọn: Data/Sort Sort by: chọn khóa · Ascending: xếp tăng dần · Descending:xếp giảm dần Trong mục My list has: xếp hay không xếp dòng tiêu đề PHÂN NHÓM CSDL: Là việc phân chia CSDL ra thành từng nhóm và lựa chọn tính toán trên đó: Sắp xếp bảng CSDL theo cột được phân nhóm, tô khối bảng CSDL, chọn: Data/Subtotals At each change in: chọn cột phân nhóm Use function : chọn hàm tính toán Add subtotal to: chọn những cột nào muốn thực hiện thống kê, tính toán trên đó q Replace current subtotals: thay thế Subtotal hiện hành trước đó q Page break between groups: ngắt trang cho từng nhóm q Summary below data: tạo dòng tổng cộng cho tất cả các nhóm Ø Remove All: xóa tất cả các SubTotal hiện có. KIỂM TRA NHẬP DỮ LIỆU TRONG EXCELL: Tô khối vùng bảng tính cần kiểm tra trước khi nhập liệu, chọn Data/Validation Tab Settings: thiết lập việc kiểm tra, trong đó mục Allow: An ny value: kiểu dữ liệu bất ký Whole number: kiểu số Decimal: kiểu thập phân Data: kiểu ngày Time: kiểu giờ List: kiểu danh sách Custom: thiết lập công thức để kiểm tra vùng dữ liệu cho các kiểu tùy ý: trong đó công thức luôn trả về giá trị True hoặc False, công thức không được dài quá 500 ký tự. Vd:thiết lập công thức kiểm tra dữ liệu nhập cho cột mã nhân viên như sau: Chiều dài 5 ký tự, 2 ký tự đầu là số thứ tự, 2 ký tự tiếp là mã phân ban, ký tự cuối là A hoặc B Công thức: =And(Len(A2)=5, Code(Left(A2,1) >= 48, Code(Mid(A2,2,1)) >= 48, Isna(Match(Value(Mid(A2,3,1)),A11:A13,0))=False, Or(Code(Right(A2,1)=65, Code(Right(A2,1)=66)) Data: lựa chọn phép toán q Ignore blank: bỏ qua những ô trống q Apply these changes to all other cells with the same settings: chọn, nếu muốn tác động các thay đổi, nếu có, đến những phạm vi khác nếu chúng có cùng kiểu quy định. Ø Clear All: xóa tất cả các quy định Tab Input Message: để xây dựng ToolTip hướng dẫn cho địa chỉ, trong đó: Title: xây dựng tiêu đề Input message: xây dựng nội dung q Show input message when cell is selected: bật/tắt thông báo về điều kiện quy định khi ô được chọn. Tab Error Alert: Để xây dựng cửa sổ báo lỗi, trong đó: Style: biểu tượng thông báo Title: xây dựng tiêu đề Error message: xây dựng nội dung báo lỗi q Show error alert affter invalid data is entered: bật/tắt thông báo lỗi ngay sao khi dữ liệu được nhập vào. TRÍCH LỌC TRÊN CSDL: Là việc lọc ra các mẫu tin từ bảng CSDL dựa trên điều kiện,trong đó: Trích lọc tự động ngay trên bảng CSDL: Tô khối, chọn: Data/Filter/Auto filter, nhấp mũi tên để chọn kiểu lọc, trong đó: All: liệt kê tất cả các dữ liệu Top 10: liêt kê 10 dòng đầu tiên Custom: thiết lập điều kiện tùy ý, trong đó: Begin with: bắt đầu với End with: kết thúc với Not begien with: không bắt đầu với Not end with: không bắt đầu với Contain: chứa đựng giá trị Not contain: không chứa đựng giá trị Chú ý: Đối với trường hợp có từ 2 điều kiện trở lên hoặc với các cột khác nhau thì chức năng này không thực hiện được Trích lọc ra một bảng riêng: dựa và bảng điều kiện được thiết lập tại vị trí khác trên bảng tính Thiết lập bảng điều kiện: giống như bảng điều kiện của hàm CSDL, đã đề cập ở bước trước. Tô khối bảng CSDL, chọn: Data/Filter/Advanced filter Filter the list, in-place: trích lọc ngay trên bảng CSDL
List range: địa chỉ CSDL Criteria range: địa chỉ vị trí cần copy ra q Unique records only: loại bỏ những mẫu tin trùng lặp TRÍCH LỌC CSDL CHỈ VỚI MỘT SỐ CỘT QUY ĐỊNH: Xây dựng bảng kết quả với một số cột (fiell) quy đinh, có tên fiell trùng với tên fiell của bảng CSDL. Xử dụng chức năng Advanced như bình thường, nhưng Copy to:được lấy địa chỉ của bảng kết quả . Chú ý:ta nên lấy dư 1 vài dòng trống để quá trình lọc được đầy đủ. THỐNG KÊ TỔNG HỢP: Dùng để thống kê tổng hợp từ 1 hoặc nhiều bảng CSDL thành 1 bảng tổng hợp dựa trên các bảng dữ liệu: Tạo cấu trúc của bảng tổng hợp, có tiêu đề trùng với tiêu đề của các cột tổng hợp Tô khối bảng tổng hợp và các dòng trống để lưu trữ kết quả, chọn: Data/Consolidate Reference: địa chỉ của các bảng CSDL được tham chiếu, cột đầu tiên của vùng tham chiếu phải trùng với cột đầu tiên của bảng tổng hợp All references: lưu chưa tất cả các vùng dữ liệu được tham chiếu thông qua Add Chọn þ Top row, þ Left column Browse…:dùng để mở tập tin CSDL khác của bảng excell q Create links to source data: tạo liên kết giữa bảng tổng hợp và bảng tham chiếu khi khác Sheft hoặc khác tập tin. ĐỒ THỊ
Là cách biểu diễn dữ liệu dưới dạng hình ảnh. Được thiết lập qua 4 bước:Insert/Chart… Bước 1:Chart Type: Lựa chọn kiểu đồ thị Bước 2: Data Source: Xác định vùng dữ liệu vẽ đồ thì(nếu tô khối trước đó rồi thì bỏ qua bước này. Bước 3: Chart Option: Xác định thông số cho đồ thị: Trong đó Tab Titles, với những quy định sau: Char title: Tên đề đồ thị Category(x)axis:Tên trục hoành Value(y)axis: Tên trục tung Tab Axes, với những quy định sau: q Category(x)axis: Bật/Tắt giá trị trục hoành q Value(y)axis: Bật/Tắt giá trị trục tung
Tab Gridlines, với nhưng quy định sau: Category(x)axis: Bật/Tắt/Mật độ đường lưới ngang Value(y)axis: Bật/Tắt/Mật độ đường lưới dọc Tab Legend, với những quy định sau: q Show Legend: Hiện/Ẩn bảng chú thích Mục Placement: Quy định vị trí đặt bảng chú thích Tab Data Labels, với nhũng quy định sau: Mục data labels: Các thông số về nhãn của dữ liệu, trong đó None: không dùng nhãn Show value: hiển thị giá trị làm nhãn Show percent: hiển thị giá trị dạng phần trăm Show label: hiển thị hiển thị nhóm nhãn trên trục x lên đồ thị Show label and percent: hiển thị nhóm nhãn và giá trị dạng phần trăm Show bubble size: hiển thị kích thước bọt của đồ thị q Legen key next to label: Bật/Tắt các ký hiệu của chuỗi dữ liệu trong hộp chú thích. Tab Data Table: Hiện/Ẩn bảng dữ liệu Bước 4: Chart Location: Xác định vị trí đặt độ thị, trong đó: As new sheet: Nằm trên 1 Sheet riêng As Object in: Nằm trên Sheet hiện hành ĐỊNH DẠNG: Do đồ thị được tạo ra bởi kết hợp nhiều thành phần, nên định dạng đồ thị là định dạng trên từng thành phần. Nhấp chuột phải vào thành phần, chọn Format… IN ẤN TRONG EXCELL Xem Tổng Quat Trang In: File/Print Preview, xuất hiện hộp thoại, trong đó: Next: Hiển thị trang in kế tiếp(nếu có) Previous: Hiển thị trang in trước Zoom: Phóng to/Thu nhỏ Print: Chuyển sang hộp thoại Print Setup: Chuyển sang hộp thoại Page Setup Margin: Bật/Tắt các đường biên của lề và cột Page Break Preview: xem các dấu ngắt trang In Ra Máy In: File/Print:
Properties: xem các thông số kỹ thuật về máy in. Print to file: in bảng tính thành một file dưới dạng nhị phân. Number of copies: Xác định số bản in ra bao nhiêu Collate: Bật/Tắt việc chọn in theo từng bộ hay từng trang. Preview: Trở lại cửa sổ Preview Quy Định Các Thông Số Về Trang In: File/Page Setup:
Portrait: in dọc Landscape: in ngang Adjust to: xác định tỉ lệ phóng/thu so với kích thước chuẩn, hoặc Fit to: in vừa vặn với bao nhiêu trang do ta chỉ định. Paper Size: chọn khổ giấy(A3=297 x 420, A4=210 x 297, A5 = 148.5 x 210). Print Quality: Chọn độ phân giải khi in(phụ thuộc vào từng máy in cụ thể). First page number: chọn cách đánh số cho trang đầu tiên(nên chọn Auto) Print: trở về cửa sổ để bắt đầu in. Print preview: xem lại trang in Options: xác định thêm 1 số thông số khác. Tab Margins, bao gồm: Quy định và thiết lập những thông số về lề của giấy, trong đó: q Horizontally: canh giữa theo chiều ngang của trang in. q Vertically: canh giữa theo chiều dọc của trang in. Tab Header/Footer, bao gồm: Các thiết đặt cho tiêu đề trang in Tab Sheet, bao gồm: Print area: xác định vùng in bằng địa chỉ. Prin titles: chọn những dòng trên (Rows to repeat at top)hoặc cột trái (Columns to repeat at left)nào sẽ được in lặp lại ở mỗi đầu trang mới. Prin: chỉ định các yếu tố trên bảng tính có được in ra hay không, trong đó: q Gridlines: bật/tắt in đường lưới. q Back and white: In trắng đen q Draft quality: In nháp q Row and column headings: bật/tắt in heading(dòng/cột địa chỉ) Comments: bật/tắt in chú thích trong ô(nếu có) Page order: chọn thứ tự in: in xuống rồi sang trái(Down, then over), hoặc in sang trái rồi xuống(Over, then down). Yüklə 165,86 Kb. Dostları ilə paylaş:
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||