Bài giảng Microsoft Excel (Bản đẹp)

+ D hoặc copy công thức sang các ô còn lại trong cộtGiả sử có bảng thống kê chi phí như sau. Nếu chi phí thực tế lớn hơn chi phí dự toán thì ghi “Vượt dự toán”, nếu không thì ghi OK.B1. Chọn ô C2, nhập công thức =IF(A2>B2;"Vượt dự toán";"OK")B2. Ấn tổ hợp Ctrl + D hoặc copy công thức sang các ô còn lại trong cộtCác hàm toán học và logicMicrosoft Excel53Ví dụ phức tạp với nhiều hàm IF lồng nhau (tự nghiên cứu)Cần điền số liệu cho cột “Thưởng theo ngày công” trong bảng kê ở bên. Theo quy định của công ty, nếu số ngày công >= 27 thì được hưởng 500000. Nếu số ngày công = 25 được thưởng 300000. Nếu số ngày công =20 thì hưởng 100000. Còn lại không được thưởng.Các hàm toán học và logicMicrosoft Excel54Các hàm toán họcHàm ABS: Lấy trị tuyệt đối của một số. Cú pháp ABS(number) - number: số cần lấy trị tuyệt đối. Hàm INT: Làm tròn một số về số nguyên gần nhất. Cú pháp INT(number) - number: số cần làm trònHàm MOD: Hàm trả về giá trị là phần dư của phép chia a/b. Cú pháp MOD(number,divisor) - number: số bị chia - divisor: số chiaHàm ROUND: Làm tròn một số đến một độ chính xác theo yêu cầu. Cú pháp ROUND(number,num_digits) - number: giá trị cần làm tròn. - num_digits: xác định số lượng chữ số thập phân sau dấu phẩy.Cho bảng số liệu như hình bên. Dùng các hàm tương ứng để tính giá trị cho các ô để trống của mỗi cột.Đây là một bài tập để làm quen với các hàm toán học cơ bản của Excel. Ngoài một số hàm đã trình bày ở phần lý thuyết trên, trong bài tập này gặp thêm một số hàm mới: SQRT(number): tính căn bậc hai của number;POWER(number, power): tính lũy thừa numberpower;PRODUCT(number1, number2, ): tính tích các số.Hàng số 3, các ô C, D, E, F, G, H lần lượt nhập vào các công thức sau: =MOD(A3;B3)=INT(A3/B3)=SQRT(A3+B3)=ROUND(A3/B3;2)=POWER(A3;4)=PRODUCT(A3;B3)Sử dụng phím tắt Ctrl+D hoặc copy công thức để điền cho các ô còn lại trong cột tương ứng.Các hàm toán học và logicMicrosoft Excel55Các hàm tính tổngHàm SUM: Tính tổng tất cả các số trong dãy số. Cú pháp SUM(number1,number2,....) - number1, number2,...: danh sách các giá trị để lấy tổng. Exceo cho phép có 1 đến 255 số trong danh sách này.Lưu ýSố, giá trị logic và chữ số gõ trực tiếp vào công thức thì được tính toán;Nếu một đối số là một mảng hoặc tham chiếu, thì chỉ các số trong mảng hoặc tham chiếu được tính toán;Các ô trống, các giá trị logic hoặc chuỗi ký tự trong mảng hoặc tham chiếu sẽ được bỏ qua;Các đối số là giá trị lỗi hoặc chuỗi ký tự không thể chuyển đổi thành số sẽ gây ra lỗi tính toán. Sự cốĐã xảy ra lỗi gìMột vài số không được thêm vào.Nếu đối số là một phạm vi ô hoặc tham chiếu, chỉ các giá trị là số trong tham chiếu hoặc phạm vi sẽ được đếm. Ô rỗng, giá trị lô-gic như TRUE hoặc văn bản sẽ được bỏ qua.Giá trị lỗi #NAME? xuất hiện thay vì kết quả mong đợi.Lỗi này thường có nghĩa là công thức viết sai chính tả .Các hàm toán học và logicMicrosoft Excel56Các hàm tính tổngHàm SUMIF là một hàm được sử dụng đặc biệt phổ biến, dùng để tính tổng giá trị (số) của các cô theo một điều kiện đặt ra. Hàm SUMIF: Tính tổng các ô theo điều kiện. Cú pháp SUMIF(range,criteria,sum_range) range Bắt buộc. Phạm vi ô bạn muốn đánh giá theo tiêu chí. Các ô trong mỗi phạm vi phải là số hoặc tên, mảng hay tham chiếu chứa số. Giá trị trống và giá trị văn bản bị bỏ qua.criteria Bắt buộc. Tiêu chí ở dạng số, biểu thức, tham chiếu ô, văn bản hoặc hàm xác định sẽ cộng các ô nào. Ví dụ, tiêu chí có thể được biểu thị là 32, ">32", B5, "32", "táo" hoặc TODAY().QUAN TRỌNG Mọi tiêu chí văn bản hoặc mọi tiêu chí bao gồm biểu tượng lô-gic hoặc toán học đều phải được đặt trong dấu ngoặc kép ("). Nếu tiêu chí ở dạng số, không cần dấu ngoặc kép.sum_range Tùy chọn. Các ô thực tế để cộng nếu bạn muốn cộng các ô không phải là các ô đã xác định trong đối số range. Nếu đối số sum_range bị bỏ qua, Excel cộng các ô được xác định trong đối số range (chính các ô đã được áp dụng tiêu chí).Có thể dùng ký tự đại diện—dấu chấm hỏi (?) và dấu sao (*)—làm đối số criteria. Một dấu chấm hỏi khớp bất kỳ ký tự đơn nào; một dấu sao phù hợp với bất kỳ chuỗi ký tự nào. Nếu bạn muốn tìm một dấu chấm hỏi hay dấu sao thực sự, hãy gõ dấu ngã (~) trước ký tự.Lưu ýHàm SUMIF trả về kết quả sai khi bạn dùng nó để khớp các chuỗi dài hơn 255 ký tự hoặc với chuỗi #VALUE!.Đối số sum_range không nhất thiết phải có cùng kích cỡ và hình dạng với đối số range. Các ô thực tế sẽ cộng được xác định bằng cách dùng ô ở ngoài cùng phía trên bên trái trong đối số sum_range làm ô bắt đầu, sau đó bao gồm các ô tương ứng về kích cỡ và hình dạng với đối số range. Khi các đối số range và sum_range trong hàm SUMIF không chứa cùng số ô, thì việc tính toán có thể mất nhiều thời gian hơn.Các hàm toán học và logicMicrosoft Excel57Sử dụng hàm SUMIFSử dụng hàm SumIF tính tổng phụ cấp cho Nhân Viên trong bảng số liệu bên:Công thức cho ô D15: =SUMIF(C5:C14,"Nhân Viên",D5:D14)Ta thu được kết quả như hình dưới:Các hàm toán học và logicMicrosoft Excel58Bài tập tổng hợpA. TRỊ GIÁ = SỐ LƯỢNG * ĐƠN GIÁ B. TIỀN LƯU KHO= (NGÀY XUẤT - NGÀY NHẬP) * 10 + SỐ LƯỢNG * 4,25% C. THUẾ = 5%* TRỊ GIÁ, VỚI TRỊ GIÁ 2500000 D. CHUYÊN CHỞ = SỐ LƯỢNG *1500, đối với kho NBÈ hoặc Bchánh Chuyên chở =Số lượng* 1350, đối với kho TĐỨC hoặc HMÔN E. TỔNG CỘNG= TIỀN LƯU KHO+THUẾ + CHUYÊN CHỞ F. Chèn thêm cột Tiền Giảm và tính theo tiêu chuẩn Nếu kí tự đầu của Mã số là "G": giảm 3% của tổng cộng Nếu kí tự đầu của Mã số là "B": giảm 2.5% của tổng cộng Nếu kí tự đầu của Mã số là "N": giảm 1.75% của tổng cộngHướng dẫn:Cột I: =D3*H3; Cột J: =(G3-F3)*10+D3*4,25%; Cột K: =IF(I3=10,000, Excel trả về lỗi #NUM!.month: số nguyên biểu thị tháng. Nếu month>12 thì hàm DATE tự quy đổi 12 tháng = 1 năm và cộng vào year số năm tăng lên do số tháng. Ví dụ, DATE(2008,16,2) month là 16, thì hàm chuyển đổi 16 tháng = 1 năm + 4 tháng, vậy year là 2009, month là 4. Tương tự như vậy, nếu month23 nó sẽ được chia cho 24, phần dư được hiểu là hour. Ví dụ TIME(24,0,0) = TIME(1,0,0) minute số từ 0 đến 32767 đại diện cho sô phút. Nếu minute>59 nó sẽ được chia cho 60, phần dư là minute. second số từ 0 đến 32767 đại diện cho số giây. Nếu second>59 nó sẽ được chia cho 60, phần dư là second.Hàm NOWTrả về ngày giờ hiện tại của hệ thống. Nếu định dạng ô là General trước khi hàm nhập công thức, kết quả trả về ở định dạng ngày tháng. Cú pháp NOW()Hàm TODAYTrả về ngày hiện tại của hệ thống. Nếu định dạng ô là General trước khi hàm nhập công thức, kết quả trả về ở định dạng ngày tháng. Cú pháp TODAY()Xử lý dữ liệu ngày tháng và văn bảnMicrosoft Excel62Nhóm hàm thời gianHàm DAYTrả về giá trị ngày (day) của một dữ liệu kiểu ngày tháng (date). Cú pháp DAY(serial_number) serial_number là ngày tháng (date) nhập vào bằng hàm DATE hoặc là kết quả của công thức hoặc hàm khác.Hàm MONTHTrả về giá trị tháng (month) của một dữ liệu kiểu ngày tháng (date). Cú pháp MONTH(serial_number) serial_number là ngày tháng (date) của tháng nhập vào bằng hàm DATE hoặc là kết quả của công thức hoặc hàm khác.Hàm YEARTrả về giá trị năm (year) của một dữ liệu kiểu ngày tháng (date). Cú pháp YEAR(serial_number) serial_number là ngày tháng của năm được nhập vào bằng hàm DATE hoặc là kết quả của công thức hoặc hàm khác.Tương tự như hàm DAY, MONTH, YEAR, Excel cũng cung cấp các hàm HOUR, MINUTE, SECOND là các hàm trả về giờ, phút, giây của một thời gian và có cách sử dụng tương tự.Công thứcKết quảDATE(2008,5,23)ngày 23 tháng 5 năm 2008DAY(DATE(2008,5,23))23 (là giá trị ngày trong “23 tháng 5 năm 2008)MONTH(DATE(2008,5,23))5 (là giá trị tháng trong “23 tháng 5 năm 2008)YEAR(DATE(2008,5,23))2008 (là giá trị năm trong “23 tháng 5 năm 2008”)Xử lý dữ liệu ngày tháng và văn bảnMicrosoft Excel63Nhóm hàm thời gianSử dụng các hàm thời gianCho bảng dữ liệu như trong hình. Yêu cầu điền thông tin vào các ô trốngĐể điền giá trị vào các cột B, C, D, E cần dùng các hàm DAY, MONTH, YEAR, WEEKDAY tương ứng với tham số là giá trị serial ở cột A.Để điền giá trị vào cột F cần dùng hàm DATE với tham số lấy từ các cột D, C, B.Để điền vào cột B, C, D (từ dòng dố 14) cần dùng các hàm SECOND, MINUTE, HOUR với tham số là số serial ở cột A.Về bản chất, Excel lưu trữ thông tin về ngày dưới dạng số, là số ngày tính từ thời điểm 1/1/1900. Có nghĩa là, ngày 1/1/1900 có giá trị số tương ứng (số serial) là 1, ngày 2/1/1990 có giá trị serial là 2. Việc lưu trữ ngày tháng dưới dạng số như vậy cho phép thực hiện các phép toán số học với kiểu ngày tháng. Trong ví dụ này, số serial ở cột A bằng 36500 tương đương với ngày thứ 36500 tính từ thời điểm 1/1/1990, cũng tương đương với ngày 6/12/1999 theo cách viết ngày tháng bình thường.Đối với thời gian (giờ, phút, giấy), Excel lưu trữ dưới dạng số thực, tính bằng tỉ lệ đối với tổng thời gian của một ngày. Ví dụ, ô A12 giá trị serial bằng 0,32 tương ứng với 0,32 * 24h * 60m * 60s = 27648 giây, cũng tương đương với 460,8 phút hoặc 7,68 giờ đã qua, hoặc theo cách viết thời gian bình thường là 7h40 sáng.Xử lý dữ liệu ngày tháng và văn bảnMicrosoft Excel64Nhóm hàm chuỗiHàm LEFT: Trả về một ký tự hoặc một nhóm ký tự tính từ trái sang phải của một chuỗi ký tự đã có. Cú pháp LEFT(text,num_chars) - text: chuỗi ký tự ban đầu. - num_chars: số ký tự mà bạn định trích ra từ chuỗi ban đầu. Hàm RIGHT: Trả về một ký tự hoặc một nhóm ký tự tính từ phải sang trái của một chuỗi ký tự đã có.Cú pháp RIGHT(text, num_chars) - text là chuỗi ký tự ban đầu. - num_chars là số ký tự mà bạn định trích ra từ chuỗi ban đầu. Hàm MID: Trích ra một chuỗi con từ chuỗi ban đầu. Cú pháp MID(text,start_num,num_chars) - text là chuỗi ký tự ban đầu. - start_num vị trí bắt đầu trích ra từ chuỗi ban đầu text. - num_chars số ký tự của chuỗi cần trích ra từ chuỗi ban đầu text.- num_chars không nhận giá trị âm - num_chars nếu lớn hơn độ dài của chuỗi thì sẽ trả về toàn bộ chuỗi text. - Nếu không nhập giá trị num_chars thì mặc định là 1. - num_chars không nhận giá trị âm - num_chars nếu lớn hơn độ dài của chuỗi thì sẽ trả về toàn bộ chuỗi text. - Nếu không nhập giá trị num_chars thì mặc định là 1. start_num nếu nhỏ hơn 1 hàm trả về lỗi #VALUE! num_chars nếu lớn hơn chiều dài chuỗi ban đầu text thì hàm trả về toàn bộ chuỗi. num_chars âm MID trả về lỗi #VALUE!Ví dụ: =RIGHT(“Trường Đại học Hà Nội”;6)sẽ cho kết quả là “Hà Nội”.Ví dụ: =LEFT(“Ngành toán tin ứng dụng”;5)sẽ cho kết quả là “Ngành”.Ví dụ: =MID(“Trường Đại học Hà Nội”;7,3)sẽ cho kết quả là “Đại”.Xử lý dữ liệu ngày tháng và văn bảnMicrosoft Excel65Nhóm hàm chuỗiHàm LEN: Trả về độ dài (số ký tự, bao gồm cả khoảng trống) của một chuỗi ký tự. Cú pháp LEN(text) - text: chuỗi ký tự cần xác định độ dài. Hàm TRIM: Chuẩn hóa xâu ký tự bằng cách xóa bỏ các khoảng trắng ở đầu, cuối và những khoảng trắng liên tục bên trong chuỗi chỉ để lại các từ và khoảng trắng đơn phân cách giữa các từ. Cú pháp TRIM(text) - text: chuỗi cần xóa các ký tự trắng. Hàm UPPER: Chuyển tất cả các ký tự trong chuỗi thành ký tự in hoa. Cú pháp UPPER(text) - text là chuỗi văn bản cần chuyển định dạng Hàm LOWER: Chuyển tất cả các ký tự trong chuỗi thành ký tự thường.Cú pháp LOWER(text) - text là chuỗi văn bản cần chuyển định dạng Hàm VALUE: Chuyển đổi một xâu ký tự (chỉ chứa các chữ số) thành dữ liệu kiểu số. Cú pháp VALUE(text) - text là chuỗi ký tự đại diện cho số (nằm trong dấu nháy kép hoặc là tham chiếu). Lưu ý: text là định dạng hằng số, ngày tháng, hoặc thời gian bất kỳ được Microsoft Excel công nhận. Nếu không phải là các định dạng trên sẽ trả về lỗi #VALUE! Ví dụ: =LEN(“Hà Nội”) sẽ trả về 6=LEN(“ Hà Nội ”) sẽ trả về 8Ví dụ: Giả sử có chuỗi text=“ Hà Nội là thủ đô của Việt Nam ”. = TRIM(text) sẽ trả về “Hà Nội là thủ đô của Việt Nam”Ví dụ: = UPPER(“hà nội”) sẽ trả về “HÀ NỘI”.Ví dụ: = LOWER(“Hà Nội”) sẽ trả về “hà nội”.Ví dụ: =VALUE(RIGHT(“HH108”,3)) sẽ trả về số 108.Xử lý dữ liệu ngày tháng và văn bảnMicrosoft Excel66Nhóm hàm chuỗiTheo quy tắc đánh mã mặt hàng thì ký tự cuối cùng là mã loại. Cần dùng hàm RIGHT để lấy ký tự này. Đối với mã hợp đồng cần dùng hàm MID. Đối với cột G cần dùng hàm IF để xác định mức giảm giá. Cụ thể các hàm như sau: Cột C: =RIGHT(B3;1)Cột D: =MID(B3;2;3)Cột G: =IF(C3="2";30/100;50/100)*E3*F3Cột H: =E3*F3-G3Sử dụng phím tắt Ctrl + D hoặc copy công thức để điền giá trị cho các cột tương ứng.Xử lý dữ liệu ngày tháng và văn bảnMicrosoft Excel67Nhóm hàm thống kêHàm COUNT: Đếm số ô dữ liệu trong vùng tham chiếu. Cú pháp COUNT(value1,value2,...) value1,value2,...: các vùng giá trị cần đếm số ô chứa dữ liệu (có thể có tới 255 vùng). Các đối số này có thể là các số, ngày tháng, địa chỉ ô, địa chỉ miền. Hàm COUNTIF: Đếm số ô trong một phạm vi đáp ứng yêu cầu nào đó. Cú pháp COUNTIF(range,criteria) - range: là một hoặc nhiều ô để đếm, bao gồm các số, tên, mảng hoặc tham chiếu chứa các số; Chuỗi ký tự và ký tự trắng bị bỏ qua. criteria: các tiêu chuẩn để Excel so sánh và đếm. Ví dụ: criteria có thể biểu diễn là 32, "32", ">32", "apples", hoặc B4. Công ty cần thống kế số lượng nhân viên có bằng tiến sỹ và thạc sỹ.Thống kê số lượng tiến sỹ: =COUNTIF($D$2: $D$9;"TS")Thống kê số lượng thạc sỹ: =COUNTIF($D$2: $D$9;"ThS")Trong công thức này, vùng $D$2: $D$9 chứa dữ liệu cần thống kê (dữ liệu về bằng cấp của nhân sự). Chuỗi “TS” và “ThS” là tiêu chí tìm kiếm. Excel sẽ tìm trong vùng $D$2: $D$9 những ô chứa xâu “TS” và tính tổng số ô như vậy. Tương tự đối với xâu “ThS”.Xử lý dữ liệu ngày tháng và văn bảnMicrosoft Excel68Nhóm hàm thống kêHàm AVERAGE: Trả về giá trị trung bình (trung bình toán học) của các đối số. Cú pháp AVERAGE(number1,number2,...) Các đối số có thể là các số hoặc tên, mảng hoặc tham chiếu chứa số. Các giá trị logic và chữ số gõ trực tiếp vào danh sách các đối số được tính. Nếu đối số là một mảng hoặc tham chiếu chứa ký tự, giá trị logic hoặc các ô trống thì chúng được bỏ qua khi tính toán. Các đối số là các giá trị lỗi hoặc chuỗi ký tự không thể chuyển thành số gây ra lỗi.Hàm MIN: Trả về giá trị nhỏ nhất trong tập hợp các giá trị. Cú pháp MIN(number1,number2,...) number1,number2,...: danh sách các số cần tìm số nhỏ nhất, có thể có tối đa 255 số.Hàm RANK: Trả về thứ hạng của một số trong một dãy số. Cú pháp RANK(number,ref, order) number là số cần stìm thứ hạng. ref là mảng hoặc vùng tham chiếu đến một danh sách kiểu số. Những giá trị không phải là số được bỏ qua.order là một số xác định cách xếp thứ hạng. Nếu order = 0, Excel xếp thứ hạng số theo danh sách được sắp xếp giảm dần. Nếu order khác 0, Excel xếp thứ hạng số theo danh sách tăng dần. Lưu ý: Nếu 2 số cùng thứ hạng (cùng giá trị) thì vị thứ tiếp theo được bỏ qua. Ví dụ: Có 2 giá trị có thứ hạng 3 thì sẽ không có thứ hạng 4 mà chỉ có thứ hạng số 5.Xử lý dữ liệu ngày tháng và văn bảnMicrosoft Excel69Ví dụ sử dụng hàm thống kêCho bảng thanh toán lương của đơn vị như hình bên. Yêu cầu: 1. Tính cột Lương Tháng = Lương ngày*số ngày công2. Lập công thức cho cột thưởng, với mức thưởng được ấn định như sau: Nếu số ngày công >=25: Thưởng 20%* Lương ThángNếu số ngày công >=22: Thưởng 10%* Lương Tháng Nếu số ngày công =25;20%*F3;IF(E3>=22;10%*F3;0))3. Phụ cấp chức vụ tính theo công thức;=IF(G3=$G$3;250000; IF(G3=$G$4;200000; IF(G3=$G$6;180000;150000)))4. Thực lãnh tính theo công thức: =F3+H3+I35. Lương cao nhất: =MAX(J3: J12)Lương trung bình: =AVERAGE(J3: J12)Lương thấp nhất: =MIN(J3: J12)Số người đi làm hơn 22 ngày: =COUNTIF(E3: E12;">22")Số người có lương trên 1 triệu: =COUNTIF(J3: J12;">1000000")Hàm dò tìm dữ liệuHàm dò tìm dữ liệu là hàm mạnh mẽ và được sử dụng rộng rãi bậc nhất trong Excel. Nếu thiếu các hàm dò tìm dữ liệu, việc xử lý dữ liệu trên nhiều bảng dữ liệu không thể thực hiện được hoặc sẽ đòi hỏi rất nhiều thời gian để hoàn thành. Khi học làm việc với Excel bắt buộc phải nắm bắt được cách sử dụng các hàm dò tìm dữ liệu.Trong phần này sẽ giới thiệu hai hàm dò tìm dữ liệu do Excel cung cấp: HLookUp và VLookUp.Microsoft Excel706Nội dung phần này:Hàm dò tìm dữ liệuHàm VLOOKUPSử dụng VLOOKUPHàm HLOOKUPHàm dò tìm dữ liệuMicrosoft Excel71Vấn đềGiả sử có hai bảng danh sách như ở Hình 3.28. Yêu cầu điền tên ngạch công chức vào cột Tên ngạch. Phương pháp giải quyết thủ công nhất là nhìn vào bảng chứa mã ngạch và tên ngạch rồi điền thông tin vào cột tên ngạch. Tuy nhiên, phương pháp này có một loạt nhược điểm: Nếu 2 danh sách trên dài sẽ phải nhớ mã để nhập (rất khó); Nếu bảng mã ngạch có thay đổi sẽ phải quay lại danh sách bên trên sửa đổi tất cả các mã ngạch;Nếu 2 bảng danh sách đều rất lớn, việc nhập thủ công sẽ rất mất thời gian;Có thể nhầm lẫn khi nhập dữ liệu vào cột tên ngạch, dẫn đến sai sót khi lọc dữ liệu.Xuất phát từ bài toán này chúng ta thấy cần thiết phải có một công cụ để thực hiện điền tự động cho cột Tên ngạch, bằng cách lấy mã ngạch của một người đem so (dò) ở cột đầu tiên của bảng mã, khi tìm thấy thì sẽ lấy giá trị cùng dòng và ở cột thứ 2.Excel cung cấp hai hàm – VLOOKUP và HLOOKUP – giúp giải quyết bài toán trên một cách đơn giản. Hai hàm này có cách sử dụng tương tự nhau. Quyết định sử dụng hàm nào phụ thuộc vào cách bố trí dữ liệu của bảng phụ (bảng lookup). Nếu bảng lookup bố trí theo chiều dọc (như trong ví dụ trên) thì dùng hàm VLOOKUP; Nếu bảng lookup bố trí theo chiều ngang thì dùng hàm HLOOKUP.Hàm dò tìm dữ liệuMicrosoft Excel72Hàm VLOOKUP: Tìm kiếm một giá trị trong cột đầu tiên của một bảng dữ liệu và trả về một giá trị trong cùng một dòng từ một cột khác của bảng dữ liệu. Có thể hiểu VLOOKUP là hàm dò tìm dữ liệu theo cột và trả về giá trị theo hàng.Cú pháp VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) lookup_value là một giá trị để tìm kiếm trong cột đầu tiên của bảng dữ liệu. Nếu lookup_value nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên của bảng dữ liệu, VLOOKUP trả về lỗi #N/A. table_array là 2 hoặc nhiều cột dữ liệu (bảng dữ liệu). Những giá trị trong cột đầu tiên của table_array là các giá trị được tìm kiếm bằng lookup_value. Những giá trị này có thể là chuỗi văn bản, số hoặc giá trị logic. Không phân biệt chữ hoa, chữ thường. col_index_num là chỉ số cột trong bảng dữ liệu mà giá trị tại đó sẽ được trả về. Cột đầu tiên trong bảng dữ liệu (table_array) có chỉ số là 1, các cột tiếp theo là 2, 3, 4, 5, v.v Nếu Col_index_num số cột của bảng dữ liệu (table_array), VLOOKUP trả về giá trị lỗi #REF! range_lookup là một giá trị logic xác định cách tìm kiếm chính xác hay tìm kiếm tương đối. Nếu Range_lookup là TRUE hoặc bỏ qua là tìm kiếm tương đối. Nếu Range_lookup là FALSE, hàm VLOOKUP sẽ tìm kiếm chính xác giá trị bạn cần tìm.Lưu ý Các giá trị ở cột đầu tiên không chứa các khoảng cách ở đầu, cuối, khoảng cách dài và các ký tự đặt biệt khác. Trong trường hợp này VLOOKUP có thể trả lại giá trị không chính xác. Ví dụ: “Hà Nội” và “ Hà Nội ” là khác nhau. Khi tìm kiếm giá trị kiểu số (number), ngày (date) thì cột đầu tiên của vùng dữ liệu không được chứa giá trị kiểu chuỗi (text).Hàm dò tìm dữ liệuMicrosoft Excel73Sử dụng hàm VLOOKUPĐiền tên ngạch vào cột E, dựa vào bảng mã ngạch dùng hàm VLOOKUP. B1. Thiết lập công thức tại ô E3 như sau:=VLOOKUP(D3,$D$14: $E$17,2,FALSE) B2. Copy công thức (kéo) để áp dụng cho các ô còn lại.Giải thích: Tìm giá trị D3 (lookup_value) trong cột đầu tiên (D14: D17) của bảng dữ liệu D14: E17 (table_array), nếu tìm thấy thì dừng lại và trả về giá trị tại cột thứ 2 (col_index_num) cùng dòng với nó ở trong bảng dữ liệu, hãy tìm chính xác giá trị D3 (FALSE). Lưu ý: Vì vùng dữ liệu này là không thay đổi khi áp dụng cho công thức ở các ô trong cột Tên ngạch nên chúng ta dùng địa chỉ cố định (bấm F4). Trong công thức trên vùng dữ liệu là $D$14: $E$17.Hàm dò tìm dữ liệuMicrosoft Excel74Hàm HLOOKUPTìm kiếm một giá trị trong hàng đầu tiên của một bảng dữ liệu (hoặc mảng dữ liệu) và trả về một giá trị trong cùng một cột từ một dòng khác của bảng dữ liệu. Có thể hiểu HLOOKUP là hàm dò tìm dữ liệu theo hàng và trả về giá trị theo cột.Cú phápHLOOKUP(lookup_value,table_array,row_index_num,range_lookup) Các biến và cách dùng tương tự như hàm VLOOKUPQuay trở lại ví dụ trên. Giả sử bảng phụ lookup được bố trí theo chiều ngang. Trong trường hợp này không thể dùng hàm VLOOKUP mà cần dùng hàm HLOOKUP.B1. Nhập công thức sau đây vào ô D2 (lưu ý cách lấy địa chỉ tuyệt đối cho vùng lookup để khi kéo thả công thức, địa chỉ vùng này không bị di chuyển): =HLOOKUP(C2;$H$1: $K$2;2; FALSE)B2. Kéo công thức qua các ô còn lại.Hàm dò tìm dữ liệuMicrosoft Excel75Sử dụng hàm tìm kiếm – bài toánCho bảng số liệu khách hàng của khách sạn. Yêu cầu: 1. Lập công thức tính số liệu cho cột tiền ăn theo công thức: Tiền ăn = số ngày ở * đơn giá khẩu phần ăn. Hai ký tự cuối của Mã số là Mã Phần ăn (Dùng hàm Hlookup, với trị dò là Mã Phần ăn, bảng dò là biểu giá khẩu phần ăn). 2. Thêm cột Số Tuần vào bên trái cột ĐGT. Lập công thức cho cột số tuần, biết số tuần là số ngày ở được đổi ra tuần lễ tuần lễ (không tính các ngày lẻ) (Dùng hàm Int hoặc Trunc để bỏ phần lẻ sau khi chia số ngày ở cho 7) 3. Lập công thức cho cột ĐGT (Đơn Giá Tuần) (Dùng hàm VLOOKUP với trị dò là 3 ký tự đầu bên trái của Mã số, Bảng dò là Biểu giá phòng) 4. Thêm cột số ngày lẻ vào bên trái cột ĐGN. Lập công thức cho cột số ngày lẻ biết số ngày lẻ là số ngày ở còn lại sau khi đã đổi ra tuần (Dùng hàm MOD lấy về số dư của phép chia số ngày ở cho 7) 5. Lập công thức cho cột ĐGN (Đơn Giá Ngày) 6. Chèn thêm cột tiền phòng vào bên trái cột tổng cộng Tính Tiền phòng = ĐGT* Số Tuần + ĐGN * Số ngày lẻ 7. Thêm cột giảm giá vào bên trái cột tổng cộng. Tính Cột giảm giá biết rằng, nếu số ngày ở từ 15 ngày trở lên thì giảm giá 5% Tiền phòng 8. Tính cột tổng cộng bằng = Tiền ăn + Tiền Phòng - Giảm Giá 9. Lập công thức tính doanh thu theo từng phòng ở Bảng Thống kê (Dùng hàm SUMIF)Hàm dò tìm dữ liệuMicrosoft Excel76Giải quyếtCột tiền ăn tính theo công thức: =HLOOKUP(RIGHT(C3;2);$G$16:$I$17;2;0)*(E3-D3)Cột đơn giá tuần (ĐGT) tính theo công thức:=VLOOKUP(LEFT(C3;3);$B$17:$D$20;2;0)Cột đơn giá ngày (ĐGN) tính theo công thức:=VLOOKUP(LEFT(C3;3);$B$17:$D$20;3;0)Thêm cột số tuần (cột H) và tính theo công thức:=INT((E3-D3)/7)Thêm cột số ngày lẻ (cột J) và tính theo công thức:=MOD(E3-D3;7)Thêm cột tiền phòng (cột K) và tính theo công thức:=G3*H3+I3*J3Thêm cột tiền giảm giá (cột L) và tính theo công thức:=IF((E3-D3)>15;5%*K3;0)Thêm cột tổng cộng (cột M) và tính theo công thức:=F3+K3-L3Trong bảng thống kê dùng công thức:=SUMIF($C$3:$C$12;"L1A*";$M$3:$M$12)Hàm dò tìm dữ liệuMicrosoft Excel77Thử sức với bài tập sauĐiền thông tin còn thiếu vào bảng thống kê sau đây theo các yêu cầu:1. Tìm tên hàng trong kí tự đầu của mã hàng trong bảng tra 12. Hãng sản xuất theo hai kí tự cuối của mã hàng, nếu là "TN" thì là "Trong Nước" nếu khác thì tìm ở bảng tra 23. Tính đơn giá theo kí tự đầu của mã hàng trong bảng 14. Nếu hãng sản xuất là trong nước thì thuế bằng 0, nếu không thì dựa vào bảng tra 1 tính thuế =số lượng* đơn giá* thuế xuất5. Thành tiền = số lượng * đơn giá - thuếĐịnh dạng có điều kiện với công thứcĐịnh dạng có điều kiện là một công cụ mạnh trong việc xây dựng các báo cáo. Với việc áp dụng công thức, định dạng có điều kiện sẽ phát huy được khả năng tùy biến cao của mình.Bài này sẽ giới thiệu phương pháp định dạng có điều kiện sử dụng công thức và một số bài tập tổng hợp.Microsoft Excel787 + 8Nội dung phần này:Định dạng có điều kiệnBài tập tổng hợpĐịnh dạng có điều kiện với công thứcMicrosoft Excel79Bài toánPhòng Hành chính – Tổ chức có một bảng dữ liệu nhân sự như dưới đây. Yêu cầu định dạng sao cho những dòng chứa nhân sự có trình độ thạc sỹ và đại học có màu sắc khác để quản lý dễ theo dõi.Phương án: Định dạng sao cho những người có trình độ thạc sỹ được đổ màu xanh, những người có trình độ đại học được đổ màu vàng;Định dạng sao cho tất cả những người có trình độ thạc sỹ hoặc đại học đều được đổ màu xanh.Định dạng có điều kiện với công thứcMicrosoft Excel80Giải quyết theo phương án 1Bước 1. Chọn vùng dữ liệu cần định dạng ($A$3: $M$18) và chọn Home  Conditional Formatting  Manage Rules.Bước 2. Trong hộp thoại Conditional Formatting Rules Manager chọn nút New RuleBước 3. Trong hộp thoại New Formatting Rule chọn Use a formula to determine which cells to format. Ở dòng Format values where this formula is true cần nhập vào điều kiện để kiểm tra. Ở dòng này có thể nhập bất kỳ biểu thức logic nào. Excel sẽ chỉ định dạng những dòng nào mà điều kiện trả về giá trị TRUE.Nhập vào dòng điều kiện: =$J3="x" (những người có trình độ đại học). Điều kiện này quy định rằng, nếu cột J (chú ý cột J là tham chiếu tuyệt đối) trong vùng lựa chọn có giá trị “x” thì trả về giá trị TRUE, và Excel sẽ chỉ định dạng cho những dòng mà cột J có giá trị “x”. Ấn nút Format để mở hộp thoại chọn định dạngĐịnh dạng có điều kiện với công thứcMicrosoft Excel81Định dạng có điều kiện với công thứcMicrosoft Excel82Bước 5. Lặp lại các bước từ 2 đến 4, thay điều kiện bằng =$I3="x", thay màu sắc định dạng khác. Kết quả cuối cùng như dưới đây.Bước 4. Sau khi chọn được định dạng theo nhu cầu, ấn OK để quay về hộp thoại New Formatting Rule. Ấn tiếp OK để đóng cửa sổ này và quay về cửa sổ Conditional Formatting Rules Manager chọn nút New Rule. Lúc này trong hộp thoại đã xuất hiện điều kiện định dạng vừa thiết lập. Ấn nút Apply để thực hiện định dạngĐịnh dạng có điều kiện với công thứcMicrosoft Excel83Giải quyết theo phương án 2Lặp lại tất cả các bước từ 1 đến 5 như đã thực hiện trong phương án 1. Thay đổi điều kiện định dạng thành =(OR($I3="x";$J3="x")). Chúng ta có thể thấy, đây là một điều kiện phức hợp bao gồm hai điều kiện con và được kết hợp bởi hàm OR.Excel cũng cho phép thay đổi vùng chịu định dạng. Trong cửa sổ Conditional Formatting Rules Manager có thể chọn giá trị ở trường Applies to. Khi đó, định dạng chỉ áp dụng cho vùng được lựa chọnĐịnh dạng có điều kiện với công thứcMicrosoft Excel84Bài tập tổng hợpCho bảng thông tin chi tiết mua xe máy và các bảng phụ (bảng phân khối, bảng loại xe, bảng nhãn hiệu).Mã hóa đơn được viết theo quy tắc sau:Ký tự đầu tiên chỉ mã loại xe (bảng loại xe);Hai ký tự tiếp theo chỉ mã phân khối (bảng phân khối);Ký tự cuối cùng chỉ hình thức mua (M) hay bán (B).Yêu cầu điền thông tin vào các cột còn để trống (hãng sản xuất, phân khối, nhãn hiệu, thời gian bảo hành. Cột hết hạn cần điền “Còn hạn” hoặc “Hết hạn” theo thời gian bảo hành và ngày mua.Thực hiện thống kê trong các bảng “Thống kê số lượng xe” và “Tình hình mua bán xe theo loại phân khối”.Định dạng có điều kiện với công thứcMicrosoft Excel85Hướng dẫnĐiền thông tin hãng sản xuất: =VLOOKUP(LEFT(B4;1);$H$28:$I$31;2;0)Thông tin về phân khối: =HLOOKUP(VALUE(MID(B4;2;2));$C$27:$F$28;2;0)Cột Nhãn hiệu: =INDEX($B$35:$E$38;MATCH(C4;$A$35:$A$38;0);MATCH(D4;$B$33:$E$33;0))Cột thời gian bảo hành: =HLOOKUP(D4;$C$28:$F$29;2;0)Cột hết hạn: =IF(NOW()>=EDATE(F4;H4);"Hết hạn";"Còn hạn")Bảng thống kê số lượng xe:Mua: =SUMIF($B$4:$B$23;"H*M";$G$4:$G$23)Bán: =SUMIF($B$4:$B$23;"H*B";$G$4:$G$23)Bảng thống kê theo loại phân khối: =COUNTIF($D$4:$D$23;F41)Bài tập tổng hợp về hàmMicrosoft Excel86Cho bảng số liệu điệnMã Hộ: Trong đó 2 ký tự đầu chỉ loại hộ, ký tự cuối chỉ khu vực đăng ký1. Số trong định mức = Số mới - Số cũ, nếu (số mới - Số cũ) INDEX($I$19:$M$22;MATCH(RIGHT(D3;1);$H$19:$H$22;0);MATCH(LEFT(D3;2);$I$18:$M$18;0));(F3-E3)-INDEX($I$19:$M$22;MATCH(RIGHT(D3;1);$H$19:$H$22;0);MATCH(LEFT(D3;2);$I$18:$M$18;0));0)Cột Tiền trong định mức:=G3*INDEX($C$19:$F$23;MATCH(LEFT(D3;2);$B$19:$B$23;0);MATCH(RIGHT(D3;1);$C$18:$F$18;0))Cột Tiền ngoài định mức:=H3*INDEX($C$19:$F$23;MATCH(LEFT(D3;2);$B$19:$B$23;0);MATCH(RIGHT(D3;1);$C$18:$F$18;0))*1,5Định dạng có điều kiện với công thứcĐịnh dạng có điều kiện là một công cụ mạnh trong việc xây dựng các báo cáo. Với việc áp dụng công thức, định dạng có điều kiện sẽ phát huy được khả năng tùy biến cao của mình.Bài này sẽ giới thiệu phương pháp định dạng có điều kiện sử dụng công thức và một số bài tập tổng hợp.Microsoft Excel887 + 8Nội dung phần này:Định dạng có điều kiệnBài tập tổng hợpĐịnh dạng có điều kiện với công thứcMicrosoft Excel89Bài toánPhòng Hành chính – Tổ chức có một bảng dữ liệu nhân sự như dưới đây. Yêu cầu định dạng sao cho những dòng chứa nhân sự có trình độ thạc sỹ và đại học có màu sắc khác để quản lý dễ theo dõi.Phương án: Định dạng sao cho những người có trình độ thạc sỹ được đổ màu xanh, những người có trình độ đại học được đổ màu vàng;Định dạng sao cho tất cả những người có trình độ thạc sỹ hoặc đại học đều được đổ màu xanh.Định dạng có điều kiện với công thứcMicrosoft Excel90Giải quyết theo phương án 1Bước 1. Chọn vùng dữ liệu cần định dạng ($A$3: $M$18) và chọn Home  Conditional Formatting  Manage Rules.Bước 2. Trong hộp thoại Conditional Formatting Rules Manager chọn nút New RuleBước 3. Trong hộp thoại New Formatting Rule chọn Use a formula to determine which cells to format. Ở dòng Format values where this formula is true cần nhập vào điều kiện để kiểm tra. Ở dòng này có thể nhập bất kỳ biểu thức logic nào. Excel sẽ chỉ định dạng những dòng nào mà điều kiện trả về giá trị TRUE.Nhập vào dòng điều kiện: =$J3="x" (những người có trình độ đại học). Điều kiện này quy định rằng, nếu cột J (chú ý cột J là tham chiếu tuyệt đối) trong vùng lựa chọn có giá trị “x” thì trả về giá trị TRUE, và Excel sẽ chỉ định dạng cho những dòng mà cột J có giá trị “x”. Ấn nút Format để mở hộp thoại chọn định dạngĐịnh dạng có điều kiện với công thứcMicrosoft Excel91Định dạng có điều kiện với công thứcMicrosoft Excel92Bước 5. Lặp lại các bước từ 2 đến 4, thay điều kiện bằng =$I3="x", thay màu sắc định dạng khác. Kết quả cuối cùng như dưới đây.Bước 4. Sau khi chọn được định dạng theo nhu cầu, ấn OK để quay về hộp thoại New Formatting Rule. Ấn tiếp OK để đóng cửa sổ này và quay về cửa sổ Conditional Formatting Rules Manager chọn nút New Rule. Lúc này trong hộp thoại đã xuất hiện điều kiện định dạng vừa thiết lập. Ấn nút Apply để thực hiện định dạngĐịnh dạng có điều kiện với công thứcMicrosoft Excel93Giải quyết theo phương án 2Lặp lại tất cả các bước từ 1 đến 5 như đã thực hiện trong phương án 1. Thay đổi điều kiện định dạng thành =(OR($I3="x";$J3="x")). Chúng ta có thể thấy, đây là một điều kiện phức hợp bao gồm hai điều kiện con và được kết hợp bởi hàm OR.Excel cũng cho phép thay đổi vùng chịu định dạng. Trong cửa sổ Conditional Formatting Rules Manager có thể chọn giá trị ở trường Applies to. Khi đó, định dạng chỉ áp dụng cho vùng được lựa chọnĐịnh dạng có điều kiện với công thứcMicrosoft Excel94Bài tập tổng hợpCho bảng thông tin chi tiết mua xe máy và các bảng phụ (bảng phân khối, bảng loại xe, bảng nhãn hiệu).Mã hóa đơn được viết theo quy tắc sau:Ký tự đầu tiên chỉ mã loại xe (bảng loại xe);Hai ký tự tiếp theo chỉ mã phân khối (bảng phân khối);Ký tự cuối cùng chỉ hình thức mua (M) hay bán (B).Yêu cầu điền thông tin vào các cột còn để trống (hãng sản xuất, phân khối, nhãn hiệu, thời gian bảo hành. Cột hết hạn cần điền “Còn hạn” hoặc “Hết hạn” theo thời gian bảo hành và ngày mua.Thực hiện thống kê trong các bảng “Thống kê số lượng xe” và “Tình hình mua bán xe theo loại phân khối”.Định dạng có điều kiện với công thứcMicrosoft Excel95Hướng dẫnĐiền thông tin hãng sản xuất: =VLOOKUP(LEFT(B4;1);$H$28:$I$31;2;0)Thông tin về phân khối: =HLOOKUP(VALUE(MID(B4;2;2));$C$27:$F$28;2;0)Cột Nhãn hiệu: =INDEX($B$35:$E$38;MATCH(C4;$A$35:$A$38;0);MATCH(D4;$B$33:$E$33;0))Cột thời gian bảo hành: =HLOOKUP(D4;$C$28:$F$29;2;0)Cột hết hạn: =IF(NOW()>=EDATE(F4;H4);"Hết hạn";"Còn hạn")Bảng thống kê số lượng xe:Mua: =SUMIF($B$4:$B$23;"H*M";$G$4:$G$23)Bán: =SUMIF($B$4:$B$23;"H*B";$G$4:$G$23)Bảng thống kê theo loại phân khối: =COUNTIF($D$4:$D$23;F41)Bài tập tổng hợp về hàmMicrosoft Excel96Cho bảng số liệu điệnMã Hộ: Trong đó 2 ký tự đầu chỉ loại hộ, ký tự cuối chỉ khu vực đăng ký1. Số trong định mức = Số mới - Số cũ, nếu (số mới - Số cũ) INDEX($I$19:$M$22;MATCH(RIGHT(D3;1);$H$19:$H$22;0);MATCH(LEFT(D3;2);$I$18:$M$18;0));(F3-E3)-INDEX($I$19:$M$22;MATCH(RIGHT(D3;1);$H$19:$H$22;0);MATCH(LEFT(D3;2);$I$18:$M$18;0));0)Cột Tiền trong định mức:=G3*INDEX($C$19:$F$23;MATCH(LEFT(D3;2);$B$19:$B$23;0);MATCH(RIGHT(D3;1);$C$18:$F$18;0))Cột Tiền ngoài định mức:=H3*INDEX($C$19:$F$23;MATCH(LEFT(D3;2);$B$19:$B$23;0);MATCH(RIGHT(D3;1);$C$18:$F$18;0))*1,5Tạo báo cáo với Pivotable và Biểu đồExcel còn hỗ trợ người dùng vẽ các biểu đồ thể hiện các dữ liệu nhằm truyền đạt thông tin đến người sử dụng, người xem một cách sinh động và dễ hiểu hơn.Bài này sẽ hướng dẫn chi tiết về cách sử dụng pivot table trong excel, pivot table là một tiện ích tự động của excel nhằm giúp người sử dụng thực hiện nhanh cách thao tác như trích lọc dữ liệu, tạo báo cáo, thống kê. Có thể nói một cách khác pivot table là một công cụ giúp cho chúng ta phân tích dữ liệu theo một yêu cầu cụ thể nào đó.Microsoft Excel989 + 10Nội dung phần này:Tạo biểu đồPivottable reportsLọc các dữ liệu trong một pivottableTính toán dữ liệu trong những báo cáo pivottable1. Tạo biểu đồCách tạo biểu đồ trong ExcelTạo một biểu đồ bằng cách nhấp chuột vào một trong các loại biểu đồ nhóm Charts, trên tab Insert1. Chọn dữ liệu mà bạn muốn tạo biểu đồ, bao gồm các cột tiêu đề.     2. Sau đó, click vào tab Insert, và trong nhóm Charts, click vào nút Column. Chọn một loại biểu đồ phù hợp3. Sau khi bạn click vào Column, bạn sẽ thấy một số loại biểu đồ cột để lựa chọn1. Tạo biểu đồMicrosoft Excel100Khi bạn tạo một biểu đồ, Chart Tools xuất hiện trên Ribbon, trong đó bao gồm các tab Design, Layout và tab Format. Nếu bạn muốn thay đổi các loại biểu đồ sau khi bạn tạo biểu đồ của bạn, nhấp chuột vào bên trong các biểu đồ. Trên tab Design, dưới Chart Tools, trong nhóm Type, bấm vào Change Chart Type và chọn một loại biểu đồ khác.Tìm hiểu về biểu đồY-AxisData TableLegendX-Axis1. Tạo biểu đồMicrosoft Excel101Sau khi tạo xong một biểu đồ, bạn có thể thay đổi kiểu biểu đồ, thay đổi chiều dữ liệu, thay đổi vùng dữ liệu và thay đổi các thông số khác của biểu đồ.Sử dụng Chart Tool – tab ngữ cảnh Design để thay đổi1. Tạo biểu đồMicrosoft Excel102Tiêu đề biểu đồ mô tả thông tin về biểu đồ của bạn Để thêm tiêu đề biểu đồ một cách nhanh chóng bằng cách click chuột vào biểu đồ, sau đó đến nhóm Chart Layouts trên tab Design, click vào nút More để xem tất cả các layout (trình bày). Mỗi sự lựa chọn hiện thị mỗi layout khác nhau có thể làm thay đổi những yếu tố của biểu đồ. Trong biểu đồ trên:1. Tiêu đề cho biểu đồ này là Northwind Traders Tea, tên của một sản phẩm.2. Tiêu đề cho các trục dọc bên trái là Cases Sold. 3. Tiêu đề cho các trục ngang ở dưới cùng là First Quarter Sales. Một cách khác để nhập tiêu đề là trên tab Layout, trong nhóm Labels, bạn có thể thêm tiêu đề bằng cách click chuột vào Chart Titles và Axis Titles. 2. Báo cáo dạng PivottableMicrosoft Excel103Hãy tưởng tượng một worksheet Excel của những con số doanh thu với hàng trăm ngàn dòng dữ liệu. Những worksheet trình bày tất cả những dữ liệu về nhân viên kinh doanh ở hai quốc gia và họ bán bao nhiêu hàng hóa trong ngày, có rất nhiều dữ liệu để giải quyết theo từng hàng chia thành nhiều cột khác.Làm thế nào bạn có thể có được thông tin trong worksheet? Làm thế nào có thể hiểu về tất cả những dữ liệu này? Tính tổng thể thì ai được bán được nhiều nhất? Ai bán nhiều nhất theo mỗi quý hay theo mỗi năm? Quốc gia nào có doanh số bán hàng cao nhất? Bạn có thể có câu trả lời bằng các báo cáo PivotTable. Một báo cáo PivotTable chuyển tất cả dữ liệu thành các báo cáo ngắn gọn cho bạn biết chính xác những gì bạn cần phải biết.2. Báo cáo dạng PivottableMicrosoft Excel104Dưới đây là một bài tập thực tế giúp chúng ta hiểu rõ hơn về cách sử dụng pivot table trong excelđể tiện cho việc thực hành pivot table.Dữ liệu trên thể hiện danh sách chi tiết bán hàng của một công ty bán về sản phẩm hàng rau củ quả, thông tin chi tiết của đơn hàng được mô tả ngắn ngọn như sau: Một dòng thể hiện thông tin bán một sản phẩm, ví dụ Banana (chuối), sản phẩm banana thuộc một loại hàng hóa nhất định đó là vegettable (đồ rau củ quả), có một số tiền bán được nhất định (amount) cho đơn hàng này và bán vào ngày (date) ở một đất nước (country).2. Báo cáo dạng PivottableMicrosoft Excel105Bài tập thực hành số 1 về pivot table sẽ tiến hành chèn pivot table vào một sheet khác từ dữ liệu có sẵn.Khi các dữ liệu đã có, đặt con trỏ bất cứ nơi nào trong phần dữ liệu bao gồm tất cả các dữ liệu worksheet trong báo cáo hoặc chọn dữ liệu bạn muốn sử dụng trong các báo cáo, sau đó trên tab Insert, trong nhóm Tables, bấm vào PivotTable và sau đó nhấp vào PivotTable lần nữaHộp thoại Create PivotTable sẽ mở ra, bạn nhấn chọn OK để thiết lập tạo một pivot table theo chế độ mặc định của excel. Chọn Select a table or range, hộp Table/Range cho thấy phạm vi của các dữ liệu đã được chọn, chọn New Worksheet hoặc Existing Worksheet, nếu bạn không muốn báo cáo được đặt trong một worksheet mới.2. Báo cáo dạng PivottableMicrosoft Excel106Bảng pivottable hiện ra. PivotTable Field List. Danh sách này hiển thị các cột tiêu đề từ dữ liệu nguồn.Đầu tiên để tính tổng số tiền bán được của mỗi một sản phẩm, thì bạn làm theo các bướcKéo cột Product vào vùng Row LabelsKéo cột Amount vào vùng Values areaKéo cột Country vào vùng ReportKết quả sau khi hoàn thành các bước trên.2. Báo cáo dạng PivottableMicrosoft Excel107Ngoài chức năng là tính tổng của một dòng theo một nhóm nào đó, như kết quả ở trên là tính tổng số tiền theo mỗi một sản phẩm, thì bạn có thể tùy chỉnh thành đếm số lượng hóa đơn có bán sản phẩm đó như bên dưới.Đếm số lượng hóa đơn có chứa theo mỗi sản phẩmNhấn phải chuột chọn vào giá trị đang được tính tổng Sum of Amout chọn Value Field SettingThay đổi cách tính toán theo yêu cầu ở tab Summarize Value bySau đó nhấn OK, kết quả hiện ra bên dưới sản phẩm Apple (táo) có 16 hóa đơn trên tổng số 28 hóa đơn3. Lọc các dữ liệu trong một pivottableMicrosoft Excel108Lọc để xem ra một trong nhiều sản phẩm Để lọc các báo cáo, bấm vào mũi tên bên cạnh Row Labels, chọn mục Road bikes (xe đạp) trong khu vực của báo cáo.Khi bạn bấm vào mũi tên đó, một trình đơn xuất hiện với hộp Select field để bạn chọn lựa và lọc thông tin. Để lọc thông tin bạn bỏ mục Select All, sau đó chọn Road Bikes cuối cùng nhấp Ok, báo cáo PivotTable sẽ cho thấy dữ liệu của phần road bikes. Các dữ liệu khác sẽ không thay đổi, nhưng chúng không xuất hiện. 3. Lọc các dữ liệu trong một pivottableMicrosoft Excel109Tìm loại sản phẩm có doanh thu caoGiả sử bạn muốn xem loại xe đạp nào có doanh số hơn $100.000, bạn có thể sử dụng Excel để xem thông tin trên, còn các không tin khác thì tạm thời ẩn. để thực hiện việc này click vào mũi tên trên biểu tượng filter nằm sát bên hộp Row Labels, chọn Product Name, tiếp theo chỉ vào ValueFilters. Bộ lọc đọc dữ liệu và chọn những dòng có những ô phù hợp với yêu cầu của bạn, chọn Greater Than Or Equal To và chọn hộp thoại Value Filter, nhập 100,000 trong hộp rỗng. Kết quả bao gồm 13 trong tổng số 38 loại xe đạp có doanh thu hơn $100,000.Lọc thông tin theo thời gian Giả sử bạn muốn xem xe đạp bán như thế trong một khoảng thời gian xác định. Bằng cách sử dụng bộ lọc, bạn có thể xem những thông tin trong khoảng thời gian bạn thích và các thông tin khác tạm thời bị ẩn. Để lọc thông tin theo từng năm chẳng hạn bạn muốn xem dữ liệu trong năm 2011 thôi thì bấm vào mũi tên bên cạnh Column Labels, sau đó click vào hộp Select All và cuối cùng chọn hộp 2011. Excel ẩn các số liệu của 2009 và 2010, chỉ còn lại số liệu năm 2011. Nếu bạn muốn xem một thời gian cụ thể thì bạn hãy làm cách sau: chọn mũi tên bên cạnh Column Labels, sau đó chỉ vào Date Filters, click Between, trong hộp thoại Date Filter nhập dữ liệu ngày tháng vào chẳng hạn 11/8/2011 và 12/8/2011 trong hai hộp rỗng, thì báo cáo sẽ hiện thông tin từ ngày 8 tháng 11 năm 2011 đến ngày 8 tháng 12 năm 2011. 3. Lọc các dữ liệu trong một pivottableMicrosoft Excel110Hủy bỏ bộ lọc1. Xóa bỏ một bộ lọc trong báo cáo PivotTable bằng cách nhấp chuột vào biểu tượng bộ lọc , và sau đó nhấp vào Clear Filter From "Product Category".2. Xoá bỏ một bộ lọc trong PivotTable Field List bằng cách di chuyển con trỏ qua biểu tượng bộ lọc bên cạnh tên field, nhấp chuột vào mũi têr xuất hiện và sau đó nhấp vào Clear Filter From "Product Category".3. Lọc các dữ liệu trong một pivottableMicrosoft Excel111Hủy bỏ một filter trong báo cáo PivotTable Để loại bỏ một bộ lọc từ một field xác định, click vào biểu tượng ở bất cứ nơi nào field bộ lọc xuất hiện trong báo cáo, ngay cả trên Row Labels hoặc trên Column Labels. Sau đó click vào Clear Filter From . Hoặc lựa chọn các hộp kiểm tra bên cạnh (Select All) để làm cho tất cả các dữ liệu trong field nhìn thấy được. Nếu bạn không xem thấy lệnh Clear Filter From cho field lọc: Hãy chắc chắn rằng bạn đã chọn đúng khu vực của báo cáo để xóa bộ lọc: hoặc là hàng hoặc là cột. Phải chọn đúng tên field trong hộp Select field. Bạn thấy rằng hộp khi bạn nhấp chuột vào biểu tượng bộ lọc, tên field trong hộp phải khớp với tên của field mà bạn xóa bộ lọc từ đó. Nếu các tên field trong hộp không đúng, chọn đúng field từ danh sách xuất hiện khi bạn bấm vào mũi tên bên cạnh hộp. Hủy bỏ một bộ lọc trong danh sách PivotTable Field: Di chuyển con trỏ qua các biểu tượng bộ lọc bên cạnh các tên field mà bạn muốn xóa bỏ bộ lọc từ đó. Click vào mũi tên xuất hiện và sau đó nhấp vào Clear Filter From .Xóa bỏ tất cả bộ lọc ngay tức khắc: Trên Ribbon, click vào tab Options dưới PivotTable Tools. Trong nhóm Actions, click vào Clear và sau đó nhấp vào Clear Filters.Bài tập thực hành 10Microsoft Excel112Bài 1: Bài thực hành về pivot table sẽ tiến hành chèn pivot table vào một sheet khác từ dữ liệu có sẵn.Dữ liệu trên thể hiện danh sách chi tiết bán hàng của một công ty bán về sản phẩm hàng rau củ quả, thông tin chi tiết của đơn hàng được mô tả ngắn ngọn như sau: một dòng thể hiện thông tin bán một sản phẩm, ví dụ Banana (chuối), sản phẩm banana thuộc một loại hàng hóa nhất định đó là vegettable (đồ rau củ quả), có một số tiền bán được nhất định (amount) cho đơn hàng này và bán vào ngày (date) ở một đất nước (country).Nhấn vào biểu tượng pivot  table ở menu InsertBài tập thực hành 10Microsoft Excel113Một cửa sổ mới hiện lên, bạn nhấn chọn OK để thiết lập tạo một pivot table theo chế độ mặc định của excel.Bảng pivottable hiện ra. Đầu tiên để tính tổng số tiền bán được của mỗi một sản phẩm, thì bạn làm theo các bướcKéo cột Product vào vùng Row LabelsKéo cột Amount vào vùng Values areaKéo cột Country vào vùng ReportVà đây là kết quả sau khi hoàn thành các bước trên.Bài tập thực hành 10Microsoft Excel114Ngoài chức năng là tính tổng của một dòng theo một nhóm nào đó, như kết quả ở trên là tính tổng số tiền theo mỗi một sản phẩm, thì bạn có thể tùy chỉnh thành đếm số lượng hóa đơn có bán sản phẩm đó như bên dưới.Đếm số lượng hóa đơn có chứa theo mỗi sản phẩm: Nhấn phải chuột chọn vào giá trị đang được tính tổng Sum of Amout chọn Value Field SettingThay đổi cách tính toán theo yêu cầu ở tab Summarize Value bySau đó nhấn OK, kết quả hiện ra bên dưới sản phẩm Apple (táo) có 16 hóa đơn trên tổng số 28 hóa đơnBài tập thực hành 10Microsoft Excel115Cho bảng dữ liệu sau: Tổng hợp doanh số bán hàng của 3 chi nhánh Công ty ABC năm 2014Vẽ đồ thị như sau: Chỉnh sửa đồ thị theo yêu cầu sau: Thay đổi kiểu tô nền cho Series dữ liệuThêm tiêu đề cho đồ thị "Tổng hợp doanh số bán hàng của ba chi nhánh công ty ABC năm 2014".Hiển thị giá trị cho mỗi cộtXoay chiều biểu diễn của đồ thị. Gợi ý: Vào Design/Data/Switch Row/Column.In ấn và một số thủ thuậtMicrosoft Excel11611Nội dung phần này:In ấnMột số thủ thuậtBảo mật dữ liệu1. In ấnMicrosoft Excel117Các chế độ hiển thị trang trong ExcelCó 3 chế độ hiển thị là Nornal View, Page Layout View và Page Break Preview.Normal View: Đây là chế độ bạn sử dụng thường xuyên trong quá trình nhập liệu, tính toán, trên bảng tính và là chế độ mặc định của Excel.Page Layout View: Là chế độ xem trước khi in, trong chế độ này bạn vẫn có thể tính toán và nhập liệu.Page Break Preview: Hiển thị bảng tính Excel với các dấu phân trang, tại đây bạn có thể chia lại trang bằng cách kéo thả các đường chia cách trang.Normal viewPage Layout ViewPage Break Preview1. In ấnMicrosoft Excel118Thiết lập các thông số cho trang inTất cả các tùy chọn thiết lập thông số trang in có tại nhóm Page Layout / nhóm Page Setup.Để vào hộp thoại Page Setup bạn vào nhóm Page Layout / đến nhóm Page Setup / nhấn vào nút Chiều trang in ngangChiều trang in dọcKhổ giấyLề tráiLề phảiLề trênLề dưới1. In ấnMicrosoft Excel119Thiết lập các thông số cho trang inCác mã lệnh và nút lệnh cho Header và Footer&[Page]Hiển thị số trang tài liệu&[Pages]Hiển thị tổng số trang được in&[Date]Hiển thị ngày hiện tại&[Time]Hiển thị giờ hiện tại&[Path]&[File]Hiển thị đường dẫn đầy đủ đến nơi lưu tập tin và Tên tập tin&[File]Hiển thị tên tập tin&[Tab]Hiển thị tên sheetKhông cóChèn hình lựa chọn vào. Bạn phải tìm đến hình cần chèn vào trong hộp thoại Insert Picture, chọn hình và nhấn nút Insert.Không cóThiết lập các thông cố cho hình chèn vào.Chọn vùng cần inDòng tiêu đề cần lặp lại ở các trangTiêu đề cuối trangTiêu đề đầu trangTiêu đề trang đầu khác với trang còn lạiTiêu đề trang chẵn khác với trang lẻ1. In ấnMicrosoft Excel120Thiết lập các thông số cho trang inThiết lập thông số hộp thoại Print: Bạn chọn nút Office Button sau đó chọn Print ( hoặc bấm tổ hợp phím )Selection: Chỉ in vùng đang chọn.Active sheet(s): Chỉ in sheet hiện hành hay các sheet đang chọn.Entire workbook: In toàn bộ workbookTable: Chỉ có tác dụng khi ô hiện hành đang trong một bảng, nếu chọn thì chỉ in bảng này.Ignore print areas: Khi chọn, Excel sẽ bỏ qua tất cả các thiết lập vùng in đã thực hiện.Mỗi trang in bao nhiêu bảnChọn máy in2. Một số thủ thuật Microsoft Excel121Giấu bảng tính ExcelẨn sheet: Bấm chuột phải vào sheet Chọn HideHiển thị sheet ẩn:Bấm chuột phải vào sheet bất kỳBấm chuột phải chọn UnHideChọn sheet cần hiển thịCách 2:Ẩn/Hiện sheet nâng cao1. Trong bảng tính hiện tại, bấm Alt + F112. Chọn Project - VBA Project3. Chọn Microsoft Excel Objects4. Chọn bảng tính muốn giấu, bấm phím F4Tuy chọn Visible: Ẩn sheet: 2-xlSheetVeryHidden, Hiển thị sheet: 1-xlSheetVisibleChon sheet1Chọn thuộc tính ẩn/Hiện sheet2. Một số thủ thuật Microsoft Excel122Khóa các ô chứa công thứcTheo mặc định, tất cả các ô trong bảng tính đều được khóa (locked), tuy nhiên, nó chẳng có tác dụng gì trừ phi bạn áp dụng lệnh Protect bảng tính. Đây là cách dễ nhất để áp dụng lệnh Protect cho bảng tính, nhưng chỉ những ô chứa công thức thì mới bị khóa, và được bảo vệ:Chọn toàn bộ bảng tính, bằng cách nhấn Ctrl+A, hoặc nhấn vào cái ô vuông nằm ở giao điểm của cột A và hàng 1. Rồi nhấn nút phải chuột và chọn Format Cells, rồi trong tab Protection, bỏ đánh dấu ở tùy chọn Locked, rồi nhấn OK:2. Một số thủ thuật Microsoft Excel123Sau đó, bạn chọn đại một ô nào đó, chọn Home ➝ Find & Select ➝ Go To Special [E2003: Edit | Go To | Special]; hoặc nhấn Ctrl+G hay F5 rồi nhấn vào nút Special.... Hộp thoại sau đây sẽ mở ra:Bạn nhấn vào tùy chọn Formulas, và nếu cần thiết thì chọn hoặc không chọn thêm 4 ô nhỏ ở dưới, và nhấn OK. Sau đó, bạn mở lại hộp thoại Format Cells đã nói ở trên, nhưng lần này thì bạn đánh dấu vào tùy chọn Locked, và nếu bạn thích ẩn luôn công thức (không cho thấy) thì đánh dấu vào tùy chọn Hidden, nhấn OK.Việc cuối cùng là Protect bảng tính: Chọn Home trên Ribbon, nhấn vào Format trong nhóm Cells, rồi nhấn vào Protect Sheet...; hoặc chọn Review trên Ribbon, rồi nhấn vào Protect Sheet [E2003: Tools | Protection | Protect Worksheet]: 2. Một số thủ thuật Microsoft Excel124Trong hộp thoại Protect Sheet, bỏ đánh dấu ở tùy chọn Select locked cells, chỉ cho phép Select unlocked cells (chọn những ô không khóa), và nhập vào một password, nếu cần thiết:3. Sử dụng chức năng thay thế (Replace) để gỡ bỏ các ký tự không mong muốn Microsoft Excel125Chức năng thay thế (replace) trong Excel có thể giúp bạn gỡ bỏ các ký tự không mong muốn trong bảng tính, nhưng phải qua một vài bước phụ.Ví dụ như, bạn có thể thay thế những ký tự không mong muốn bằng chuỗi rỗng tựa như nó chưa hề tồn tại. Muốn vậy bạn cần biết mã của từng ký tự mà bạn muốn gỡ bỏ. Tất cả các ký tự đều mang một mã riêng và Excel sẽ cho bạn biết nó là gì khi bạn sử dụng hàm CODE.Hàm CODE sẽ trả về một mã số cho ký tự đầu tiên trong một chuỗi. Mã này tương đương ký tự mà máy tính của bạn đã thiết lập.Để thực hiện điều này, chọn một trong các ô có chứa những ký tự không mong muốn. Từ thanh công thức, bôi đen ký tự và sao chép ký tự đó. Tiếp theo chọn ô trống bất kỳ (A1 chẳng hạn) và dán ký tự đó vào ô đã chọn (A1).Tại ô khác, nhập công thức sau: =CODE($A$1)Công thức này trả về mã của ký tự không mong muốn.Chọn toàn bộ dữ liệu của bạn, chọn Home ➝ Editing ➝ Find & Select ➝ Replace, ở khung Find what: nhấn phim Alt và gõ số 0 kèm theo code đã đưa ra bởi công thức trên. Nếu mã số là 163 thì nhấn Alt và nhấn 0163.Bỏ trống khung Replace With và nhấn Replace all. Việc làm này sẽ xóa bỏ tất cả những ký tự không mong muốn rất nhanh qua việc dò tìm mã ký tự. Lặp lại các bước ở trên cho mỗi ký tự không mong muốn tiếp theo.Bài tập thực hành tổng hợpMicrosoft Excel126Dùng công thức điền giá trị vào các ô có dấu? trong bảng sau (chú ý trình bày đẹp): 1/ Tiền công tơ tính như sau: Hộ Sản xuất: 20000 đ/thángHộ Kinh doanh: 10000 đ/thángHộ Tiêu dùng: 5000 đ/tháng2/ Giá điện: Hộ Sản xuất: 2000 đ/sốHộ Kinh doanh: 800 đ/sốHộ Tiêu dùng: 500 đ/số3/ Tổng số tiền = Tiền điện+Tiền công tơTTChủ hộHình thức sử dụngChỉ số trướcChỉ số sauĐiện tiêu thụTiền điệnTiền công tơTổng số tiền1VânSản xuất0500?????BìnhKinh doanh0200?????KhánhTiêu dùng0150?????DoanhSản xuất0600?????LanTiêu dùng0101?????ThuTiêu dùng050?????QuảngKinh doanh0300????LoạiSố hộTiềnSản xuất??Kinh doanh??Tiêu dùng??Bài tập thực hành tổng hợpMicrosoft Excel127Dùng công thức điền giá trị vào các ô có dấu? trong bảng sau (chú ý trình bày đẹp): 1/ Tuổi tính đến ngày Hôm nay (đủ 365 ngày mới tính là 1 tuổi)2/ Điểm trung bình tính như sau: Toán hệ số 2, Tin hệ số 33/ Xếp loại và Học bổng theo thang điểm sau: TTTênNgày sinhTuổiGTToánTinTrung bìnhXếp thứXếp loạiHọc bổng1Hùng1/30/1978Nam47?????Bình8/21/1974Nữ68?????Vân8/21/1975Nữ89?????Bình8/16/1977Nam910?????Doanh12/5/1976Nam58?????Loan9/18/1977Nữ54?????Anh4/23/1968Nam96?????Thu5/1/1973Nữ35????Khánh2/26/1971Nam67????Ngân5/12/1975Nữ108????LoạiSố lượngĐiểm TBKém??TB??Khá??Giỏi??Xuất sắc??