Bài giảng Excel cơ bản

KHỞI ĐỘNG EXCEL Từ màn hình Desktop hãy kích đúp chuột vào đây CỬA SỔ ỨNG DỤNG CỦA EXCEL Thanh tiêu đề Nút điều khiển Nút cực tiểu Nút cực đại Nút đóng Thanh Menu lệnh Thanh nút lệnh Thanh nút lệnh Thanh nút lệnh Thanh công thức Địa chỉ ô hiện thời Dữ liệu trong ô hiện thời Thanh trạng thái Cửa sổ bảng tính Tên hàng Tên cột BOOK (CUỐN SÁCH) Khi khởi động Excel sẽ được ngay một Book mới có tên là “Book1” Mỗi Book có 255 Sheet (trang) Mỗi trang có 256 cột (Column) và 65536 dòng (Row) Giao giữa 1 dòng và 1 cột là 1 ô (Cell). Mỗi ô chứa được tối đa là 256 ký tự, địa chỉ ô được ghi là tên cột trước và tên dòng sau. Thí dụ B23 ĐỊA CHỈ Ô Có 3 loại địa chỉ ô: Địa chỉ tương đối: B23, AB123, …(Khi copy công thức từ ô này sang ô khác thì cột và dòng trong công thức đều thay đổi) Địa chỉ tuyệt đối: $B$23, $AB$123, … …(Khi copy công thức từ ô này sang ô khác thì cột và dòng trong công thức đều không thay đổi) Địa chỉ hỗn hợp: $B23, AB$123, … …(Khi copy công thức từ ô này sang ô khác thì cột không thay đổi và dòng trong công thức thay đổi, hoặc ngược lại – Có dấu $ ở trước thì không thay đổi). Thí dụ “Bai tap Excel\Thong ke” CHỌN PHẠM VI CẦN THỰC HIỆN Chọn 1 ô: Kích chuột vào ô cần chọn hoặc di chuyển ô hiện hành đến ô cần chọn bằng các phím: , ,,  Chọn một số ô liền nhau: Chọn một số côt: Kích chuột vào tên cột đầu, giữ và di chuột cho đủ các cột cần chọn; Chọn một số dòng: Kích chuột vào tên dòng đầu, giữ và di chuột cho đủ các dòng cần chọn; Chọn một số ô: Kích chuột vào ô góc trên bên trái của khu vực cần chọn (KVCC), giữ chuột và kéo xuống ô ở góc dưới bên phải KVCC. Chọn một số không liền nhau: Chọn vùng 1, ấn và giữ phím Ctrl và chọn vùng 2, ấn và giữ phím Ctrl và chọn vùng 3, … Thí dụ: NHẬP DỮ KIỆN VÀO Ô Dữ kiện dạng chuỗi (Text): dạng thể hiện là căn thẳng bên trái. Chuỗi không tính toán được. Dữ kiện dạng số: dạng thể hiện là căn thẳng bên phải. Số có giá trị tính toán. Dữ kiện dạng công thức: khi nhập phải bắt đầu bởi dấu “=“ hoặc dấu “+”, dạng thể hiện là kết quả của công thức. Dữ kiện dạng ngày, giờ: Thông thường là dạng: dd/mm/yyyy, mm/dd/yyyy, h:mm:ss AM/PM, dd/mm/yyy h:mm, … tuỳ theo ta chọn trong Format\Cell\Cuptom. Thí dụ: NHẬP DỮ KIỆN VÀO Ô Nhập dữ kiện vào 1 ô: Nhập đúng dạng dữ kiện; Gõ phím Enter hoặc một trong các các phím , ,,  để chuyển sang ô khác. (Khi nhập dữ kiện vào ô đã có dữ kiện thì dữ kiện mới sẽ thay thế dữ kiện cũ) Nhập cùng một dữ kiện vào nhiều ô: Chọn các ô cần nhập; Nhập dữ kiện vào ô hiện hành; Ấn và giữ phím Ctrl gõ phím Enter. Thí dụ: CHỈNH SỬA DỮ KIỆN Cách 1: Chọn ô cần sửa dữ kiện; Gõ phím F2 con trỏ sẽ nhảy vào ô cần sửa; Di chuyển con trỏ đến vị trí cần sửa bằng các phím ,  và sửa dữ kiện. Cách 2: Chọn ô cần sửa dữ kiện; Kích chuột vào ô bên phải của thanh công thức để sửa. Thí dụ: DÁN GIÁ TRỊ Chọn vùng cần copy dữ kiện Chọn ô đầu (góc trên bên trái) của vùng cần dán dữ kiện Kích chuột vào Paste Special… Kích chuột vàoValue Kích chuột vào OK Thí dụ DÁN CỘT THÀNH HÀNG Chọn vùng cần copy trên hàng (cột) Chọn ô đầu tiên của vùng cần dán dữ kiện Kích chuột vào Paste special… Kích chuột vào Transpose Kích chuột vào OK Thí dụ ĐIỀN DỮ KIỆN VÀO PHẠM VI Điền dữ kiện giống nhau vào một vùng: Như phần 5.2.2 Nếu vùng liền nhau trên nhiều dòng hay nhiều cột thì: Chọn khu vực cần copy dữ kiện (đã điền dữ kiện)  Đặt trỏ chuột vào góc dưới bên phải khu vực vừa chọn để con trỏ trở thành dấu “+”  Kích chuột và di đến khi đủ thì nhả chuột. Điền dữ kiện khác nhau vào một vùng: Thực hiện các bước như cách hai vừa trình bày. Chú ý nếu dữ kiện dạng số thì phải nhập 2 ô. Thí dụ: CHÈN (XOÁ) MỘT SỐ DÒNG (CỘT) Chèn n dòng hoặc n cột: Chọn n dòng (n cột) dưới dòng (bên phải cột) cần chèn; Kích phím phải chuột vào khu vực vừa chọn; Kích chuột vào Insert. Xoá m dòng (m cột): Chọn m dòng (m cột) cần xoá; Kích phím phải chuột vào khu vực vừa chọn; Kích chuột vào Delete. Thí dụ:”Bai tap Excel\QL du lieu THAY ĐỔI ĐỘ RỘNG CỦA CỘT ĐỘ CAO CỦA DÒNG 1. Cách 1: Đặt trỏ chuột vào biên phải của cột (hoặc biên dưới của dòng) trên dòng tên cột (hoặc cột tên dòng) để trỏ chuột biến thành mũi tên hai chiều” ” hoặc “” Bấm, giữ phím trái chuột dê cho đủ độ rộng của cột (hoặc độ cao của dòng) thì nhả chuột. Thí dụ:”Bai tap Excel\QL du lieu THAY ĐỔI ĐỘ RỘNG CỦA CỘT ĐỘ CAO CỦA DÒNG 2. Cách 2: Chọn cột cần thay đổi độ rộng Vào Format  Colums (Row)  Width (Height) Nhập độ rộng (độ cao) vào ô Nhấn OK Thí dụ:”Bai tap Excel\QL du lieu LÀM CHO ĐỘ RỘNG CỦA NHIỀU CỘT(ĐỘ CAO CỦA NHIỀU DÒNG) BẰNG NHAU Chọn các cột (các dòng) cần làm cho độ rộng (độ cao) bằng nhau Đặt trỏ chuột vào biên phải của cột (biên dưới của dòng) nào đó trong vùng đã được chọn Khi trỏ chuột biến thành mũi tên hai chiều hãy dê đến khi đủ độ rộng (độ cao) thì nhả chuột. Thí dụ:”Bai tap Excel\QL du lieu TỰ LÀM VỪA ĐỘ RỘNG CỦA CỘT HAY ĐỘ CAO CỦA DÒNG Cách 1: Chọn tất cả các cột (hay các dòng) Nháy đúp vào biên của cột ở ô tên cột (hay biên của đòng ở ô tên dòng) Cách 2: Chọn tất cả các cột (hay các dòng) Vào Format  Colum (Row)  Auto Fit Selection (Auto Fit ) Thí dụ:”Bai tap Excel\QL du lieu THAY ĐỔI ĐỊNH DẠNGDỮ KIỆN TRONG Ô Chọn các ô cần thay đổi định dạng Vào Format  Cell Chọn thẻ Number Chọn loại định dạng trong hộp Category Nếu không có thì chọn Custom Gõ định dạng vào ô Type và nhấn OK. Thí dụ:”Bai tap Excel\bang lương CANH BIÊN DỮ KIỆN TRONG Ô Chọn các ô cần canh biên Vào Format  Cell Chọn thẻ Alignmen Chọn biểu tượng thíc hợp trong: Horizontal (phân bố theo chiều ngang) Vertica (phân bố theo chiều dọc) Orien Tation (Trình bày dữ kiện theo hướng) Wrap Text (Tự xuống dòng khi đến biên phải) Thí dụ:”Bai tap Excel\bang lương TẠO ĐƯỜNG VIỀN Chọn vùng cần tạo đường viền Vào Format  Cell Chọn thẻ Border Chọn đường kẻ thích hợp trong ô Style Chọn vị trí cần kẻ đường viền trong ô Presets và Border Nhấn OK. Thí dụ:”Bai tap Excel\bang lương ẨN/HIỆN CỘT (DÒNG) Ẩn cột (hoặc dòng): Chọn các cột cần ẩn Vào Format  Colum (Row)  Hide Hiện các cột (hoặc dòng) đang ẩn: Chọn cả bảng tính Vào Format  Colum (Row)  Unhide. Thí dụ:”Bai tap Excel\QL du lieu ĐẶT LỀ, CỠ GIẤY, HƯỚNG IN Vào File  Page Setup Trong thẻ Page: Chọn hướng in ở Orientation, tỷ lệ % in ở Adjuct to, cỡ giấy ở Pager Size. Trong thẻ Margins đặt lề Trong thẻ Header/Footer đặt tiêu đề đầu trang hoặc cuối trang Trong thẻ Sheet đặt vùng in, những dòng xuất hiện trong tất cả các trang, những cột xuất hiện trong tất cả các trang. Thí dụ:”Bai tap Excel\bang lương HÀM TRONG EXCEL Hàm là 1 công thức định sẵn phục vụ tính toán Kết quả của hàm là một số hoặc chuỗi hoặc một thông báo lỗi: # VALUE : Kh«ng tÝnh ®­îc # N/A : Kh«ng hîp lÖ # Name : Kh«ng hiÓu tªn # Num : Kh«ng ph¶i d÷ kiÖn sè # DIV/ 0 : Chia cho sè 0 #REF : Kh«ng hiÓu ®Þa chØ # Null : Rçng DẠNG TỔNG QUÁT CỦA HÀM =(Đối số 1, đối số 2, …, đối số n) Tên hàm theo quy định của Excel Đối số có thể là: số, toạ độ ô, tên vùng, công thức hoặc hàm Mỗi hàm có tối đa 30 đối số; mỗi đối số cách nhau bởi dấu ”,” hay dấu “;” Trong hàm không được chứ ký tự trắng (dấu cách) Có thể lấy một hàm làm đối số cho hàm khác, nhưng trước hàm đối số không có dấu “=“ CÁCH NHẬP HÀM Cách 1: nhập từ bàn phím: Chọn ô cần nhập hàm Gõ: “=0: lµm trßn ®Õn n chữ sè sau dÊu " . " thËp ph©n, n0 = 0 nÕu biÓu thøc sè =0 = -1 nÕu biÓu thøc sè <0 SUM (trÞ 1,trÞ 2,trÞ 3,...,trÞ n) cho tæng c¸c trÞ SUMIF(Vïng kiÓm tra ®iÒu kiÖn, vïng ®iÒu kiÖn hay ®iÒu kiÖn, vïng chøa c¸c sè h¹ng cña tæng) : Hµm tÝnh tæng theo ®iÒu kiÖn. DSUM(Vïng c¬ së dữ liÖu, tªn tr­êng cÇn tÝnh tæng, vïng ®iÒu kiÖn): Hµm tÝnh tæng theo ®iÒu kiÖn trong c¬ së dữ liÖu. Hµm SUMPRODUCT(Vïng1,Vïng2):Hµm tÝnh tæng c¸c tÝch t­¬ng øng cña 2 vïng. Thí dụ “Bai tap ExcelThi thu” MỘT SỐ HÀM THÔNG DỤNG 3. Hµm dß tìm tham chiÕu Vlookup(trÞ dß, b¶ng tham chiÕu, cét tham chiÕu, c¸ch dß) * TrÞ dß: lµ gi¸ trÞ ta muèn dß tìm trªn cét ®Çu cña b¶ng tham chiÕu. * B¶ng tham chiÕu: lµ khèi « mµ cét ®Çu tiªn chøa c¸c dữ kiÖn ®Ó dß vµ c¸c cét sau chøa c¸c dữ kiÖn ®Ó tham chiÕu * Cét tham chiÕu : lµ sè thø tù (tõ tr¸i sang ph¶i) cña cét trong b¶ng tham chiÕu. * C¸ch dß: True (gÇn ®óng), False (chÝnh x¸c) Chó ý : B¶ng tham chiÕu ph¶i lµ ®Þa chØ tuyÖt ®èi ThÝ dô : D11= Vlookup(B11,$B$4: $D$8,3, False)*F11 E11= Vllookup(B11,$B$4:$D$8,4,False)*F11 F11= Vllookup(B11,$B$4:$D$8,2,False)*F11 Hlookup(trÞ dß,b¶ng tham chiÕu,dßng tham chiÕu,c¸ch dß). Thí dụ “Bai tap Excel\hoc them” MỘT SỐ HÀM THÔNG DỤNG 4. C¸c hµm sö lý chuçi: LEFT(chuçi,n) : TrÝch n ký tù phÝa tr¸i cña chuçi RIGHT(chuçi,n) : TrÝch n ký tù phÝa ph¶i cña chuçi MID(chuçi,k,n) trÝch n ký tù cña chuçi b¾t ®Çu tõ ký tù thø k TRIM(chuçi) lo¹i bá c¸c ký tù trèng v« nghÜa Proper (chuçi) chuyÓn c¸c ký tù ®Çu c¸c tõ cña chuçi sang in hoa . Thí dụ “Bai tap Excel\Thu chi ngoai NS” MỘT SỐ HÀM THÔNG DỤNG 5. C¸c hµm luËn lý: IF(BiÓu thøc logic,BT1,BT2): NÕu tho¶ m·n biÓu thøc l«gÝc thì tr¶ vÒ BT1, tr¸i l¹i tr¶ vÒ BT2 AND(BT1,BT2,....,BTn)= ®óng nÕu mäi biÓu thøc ®óng = sai nÕu Ýt nhÊt 1 biÓu thøc sai OR(BT1,BT2,....... BTn) = ®óng nÕu Ýt nhÊt 1 biÓu thøc ®óng = sai nÕu mäi biÓu thøc ®Òu sai NOT(BT) = ®óng nÕu BT sai = sai nÕu BT ®óng Thí dụ “Bai tap Excel\Thi thu” MỘT SỐ HÀM THÔNG DỤNG 6. C¸c hµm thèng kª : AVERAGE (trÞ 1,trÞ 2,trÞ 3,...,trÞ n) cho trung bình céng cña c¸c trÞ. COUNT(khèi)= Sè « cã chøa trÞ trong khèi COUNTIF (khèi, ®iÒu kiÖn ) = sè « trong khèi tho¶ m·n ®iÒu kÖn nªu ra COUNTA(khèi): ĐÕm c¸c « kh«ng rçng trong khèi COUNTBLANK(Khèi): ĐÕm c¸c « rçng trong khèi MAX(trÞ 1,trÞ 2,trÞ 3,...,trÞ n) : cho biÓu thøc sè lín nhÊt trong c¸c biÓu thøc sè . Thí dụ “Bai tap Excel\Thi thu” MỘT SỐ HÀM THÔNG DỤNG 6. C¸c hµm thèng kª : MIN (trÞ 1,trÞ 2,trÞ 3,...,trÞ n ) : cho biÓu thøc sè nhá nhÊt trong c¸c biÓu thøc sè SMALL(Ph¹m vi,n): cho sè xÕp thø n (XÕp tõ nhá ®Õn lín) trong ph¹m vi lµ sè nµo. NÕu COPY c«ng thøc thì ph¹m vi ph¶i lµ ®Þa chØ tuyÖt ®èi LARGE(Ph¹m vi,n): cho sè xÕp thø n (XÕp tõ lín ®Õn nhá) trong ph¹m vi lµ sè nµo. NÕu COPY c«ng thøc thì ph¹m vi ph¶i lµ ®Þa chØ tuyÖt ®èi DCOUNTA(Vïng c¬ së dữ liÖu,tªn tr­êng cÇn ®Õm, vïng ®iÒu kiÖn): ĐÕm sè b¶n ghi tho¶ m·n mét sè ®iÒu kiÖn nµo ®ã) MỘT SỐ HÀM THÔNG DỤNG 7. C¸c hµm kh¸c: RANK(to¹ ®é « ®­îc xÕp thø, ph¹m vi, c¸ch xÕp) Ph¹m vi : Ph¶i lµ ®Þa chØ tuyÖt ®èi C¸ch xÕp : B»ng 0 nÕu muèn xÕp tăng dÇn, B»ng 1 nÕu muèn xÕp gi¶m dÇn Thí dụ “Bai tap Excel\Thi thu” CƠ SỞ DỮ LIỆU Cơ sở dữ liệu (CSDL) là tập hợp thông tin được tổ chức thành bảng hai chiều (cột/dòng). Từ CSDL có thể tìm kiếm, sắp xếp, trích thông tin theo một yêu cầu nào đó. Mỗi cột là một vùng hay một trường, tên trường bắt đầu phải bằng chuỗi. Dòng đầu tiên của CSDL là tên trường.Dữ liệu trong một trường phải thống nhất cùng một kiểu. Mỗi dòng là một bản ghi, không được để bản ghi rỗng. Thí dụ “Bai tap Excel\QL du lieu” SẮP XẾP CSDL Chän toµn bé CSDL trõ dßng chøa tªn tr­êng Vµo Đata  Sort... Chän cét ­u tiªn sè 1 ë « Sort By Chän cét ­u tiªn 2 ( nÕu cÇn ) ë « Then By Chän cét ­u tiªn 3 ( nÕu cÇn ) ë « Then By Chän c¸ch s¾p xÕp t¨ng dÇn (Ascending ), hay gi¶m dÇn ( Decending ) OK Thí dụ “Bai tap Excel\QL du lieu” LỌC DỮ LIỆU 1. Läc b»ng Auto Finter: Chän « bÊt kú trong CSDL Vµo Data  Finter  Auto Finter KÝch chuét vµo tªn tr­êng CSDL cÇn läc Chän ®iÒu kiÖn cÇn läc Chó ý : ĐÓ trë l¹i CSDL tr­íc khi läc vµo Data  Finter  Auto Finter Thí dụ “Bai tap Excel\QL du lieu” LỌC DỮ LIỆU 2. Läc b»ng Avanced Finter: T¹o vïng ®iÒu kiÖn gåm : Dßng 1 chøa tªn c¸c tr­êng Dßng 2 trë ®i chøa c¸c ®iÒu kiÖn C¸c ®iÒu kiÖn t­¬ng øng ngay d­íi tªn tr­êng §Æt « hiÖn hµnh vµo trong vïng CSDL Vµo Data  Finter  Advanced Finter xuÊt hiÖn hép héi tho¹i + Chän dßng Copy To Another Location: läc ®­îc sao chÐp ®Õn ®Ých + NhËp to¹ ®é CSDL cÇn khai th¸c vµo « Lit Range (®Þa chØ tuyÖt ®èi) + NhËp to¹ ®é vïng ®iÒu kiÖn vµo « Criteria Range (®Þa chØ tuyÖt ®èi) + NhËp to¹ ®é ®Ých vµo « Copy to ( chØ cÇn nhËp ®Þa chØ tuyÖt ®èi cña « ®Çu tiªn ) (NÕu chän Unique records only tøc lµ läc vµ lo¹i bá những b¶n ghi trïng nhau) Thí dụ “Bai tap Excel\QL du lieu” BIỂU ĐỒ Biểu đồ là một đồ thị biểu diễn dữ liệu của một bảng; Biểu đồ làm cho dữ liệu của một bảng phức tạp trở thành trực quan và dễ hiểu hơn; Biểu đồ biến dữ liệu từ các hàng, các cột thành hình khối, đồ thị; Biểu đồ có các trục, trên đó có các giá trị tương ứng như trong bảng. Thí dụ “Bai tap Excel (DA)\Bieu do” CÁCH TẠO BIỂU ĐỒ Muốn tạo biểu đồ trước hết phải có 1 bảng tính. Thí dụ: BẢNG TỔNG HỢP XẾP LOẠI VĂN HOÁ CÁC BƯỚC TẠO BIỂU ĐỒ KÝch chuét vµ nót (Chart Wizard) trªn thanh c«ng cô chuÈn ®­îc cöa sæ Chart Wizard – Step 1 of 4 – Chart Type: Trong thÓ Standard Types chän lo¹i biÓu ®å vµ chän mÉu biÓu ®å. KÝch chuét vµo nót Next ®­îc cöa sæ Chart Wizard – Step 2 of 4 – Chart Seurce Data: Thí du: “Biểu đồ” CÁC BƯỚC TẠO BIỂU ĐỒ Trªn thÎ Data Range chän vïng chøa dữ liÖu cho biÓu ®å vµ x¸c ®Þnh chuçi dữ liÖu bè trÝ theo hµng hay cét. Trªn thÎ Series ®Ó: chän tªn biÓu ®å; chuçi lµm biÓu ®å (trong vÝ dô nµy lµ Giái, Kh¸. T.Bình, YÕu. NÕu thªm kÝch nót add, nÕu bít h·y chän vµ kÝch nót Remove); chän vïng gi¸ tÞ cña biÓu ®å (trong vÝ dô nµy lµ $M$6:$Q$9); chän vïng chøa c¸c gi¸ trÞ trªn trôc X (Trong vÝ dô nµy chän $M$5:$Q$5) KÝch vµo nót Next ®Ó chuyÓn sang cö sæ Chart Wizard – Step 3 of 4 – Chart Options: Thí du: “Biểu đồ” CÁC BƯỚC TẠO BIỂU ĐỒ Gâ tªn ®å thÞ vµo « Chart Tyle thÝ dô “XÕp lo¹i văn ho¸” Gâ tªn trôc X vµo « Category (X) Axis thÝ dô “Năm” Gâ tªn trôc Z vµo « Value (Z) Axis thÝ dô “Sè l­îng” KÝch vµo nót ®Ó chuyÓn sang cö sæ Chart Wizard – Step 4 of 4 – Chart Location: Thí du: “Biểu đồ” CÁC BƯỚC TẠO BIỂU ĐỒ Chän as new sheet lµ t¹o ®å thÞ vµo Sheet míi. Chän as object in lµ t¹o ®å thÞ ngay trªn Sheet ®ã Cuèi cïng kÝch chuét vµo Finish ®­îc biÓu ®å cÇn t¹o Thí du: “Biểu đồ” TỔNG HỢP XẾP LOẠI VĂN HOÁ THÊM, BỚT, CHỈNH SỬA BIỂU ĐỒ Thªm 1 d·y míi hoÆc bít mét d·y trong biÓu ®å: Muèn thªm 1 d·y n÷a vµo biÓu ®å ta nhËp thªm d·y ®ã vµo b¶ng d÷ liÖu  chän d·y ®ã  ®­a chuét vµo ®­êng bao quang d·y kÐo vµo biÓu ®å. Muèn lo¹i bá mét d·y ra khái biÓu ®å ta kÝch chuét chän d·y muèn lo¹i bá  Gâ phÝm Delete ChØnh söa biÓu ®å: Ta cã thÓ thay ®æi c¸c thµnh phÇn cña biÓu ®å b¨ng c¸ch kÝch chuét ph¶i lªn biÓu ®å, xu¸t hiÖn Menu t¾t  chän Chart Options ®­îc cö sæ Chart Options Vµo c¸c thÎ cña cöa sæ nµy ®Ó chØnh sö biÓu ®å Thí du “Thi du\Bieu do”