13 Thủ Thuật Excel 2007 Nâng Cao, Mẹo Hữu Ích Giúp Nâng Cao Kỹ Năng Excel

Bài 2: Các Công Cụ Định Dạng Trong Excel1 Conditional Formatting (Định dạng màu sắc theo điều kiện)B1: Chọn vùng cần định dạng (không chọn tiêu đề)B2: Vào Menu Home Conditional Formatting+ New Rule: Tạo mới 1 định dạng + Clear Rule: Xóa các định dạng+ Manage Rule: Quản lý các định dạng.B3: Chọn New Rule để tạo mới Xuất hiện hộp thoại New Rule Formatting BÀI 1: ƠN TẬP EXCEL CB Bài 2: Các Cơng Cụ Định Dạng Trong Excel 1/ Conditional Formatting (Định dạng màu sắc theo điều kiện) B1: Chọn vùng cần định dạng (không chọn tiêu đề) B2: Vào Menu Home/ Conditional Formatting + New Rule: Tạo định dạng + Clear Rule: Xóa định dạng + Manage Rule: Quản lý định dạng B3: Chọn New Rule để tạo mới/ Xuất hộp thoại New Rule Formatting + Format all Cells pased on their values: Định dạng cho tất ô + Format only cells that contain: Định dạng cho theo điều kiện có sẵn + Format only top or bottom ranked values: Định dạng cho ô liệu cao thấp + Format only value that are above or below average: Định dạng liệu so sánh với trung bình + Format only unique or duplicate values: Định dạng cho liệu or trung + Use a formula to determine which cell to format: Sử dụng công thức hàm để định dạng cho cột dòng 2/ Data Validation: Ràng buộc liệu B1: Chọn vùng liệu cần ràng buộc B2: Vào Menu Data/ Data Validation + Thẻ Setting: Thiết lập điều kiện ràng buộc – Allow: Loại điều kiện Any value: Không giới hạn Whole number: Số nguyên Dicemal: Số thập phân List: Tạo danh sách giới hạn cho phép người dùng chọn liệu có danh sách quy định Date: kiểu ngày Time: kiểu thời gian Text Length: giới hạn số kí tự Custom: giới hạn tùy chỉnh dùng cơng thức hàm… – Data: chọn điều kiện ràng buộc Nhập liệu ràng buộc giá trị, miền giá trị, vùng liệu… + Thẻ Input Message: Tùy chỉnh hộp thoại gợi ý nhắc nhở – Title: Tiêu đề hộp thoại thông báo – Input Message: nội dung thông báo + Thẻ Error Alert: Tùy chỉnh hộp thoại báo lỗi – Style: Kiểu hộp thoại Stop: ngừng thao tác Warning: cảnh báo Information: nội dung lỗi cho phép tiếp tục thao tác – Title: Tiêu đề hộp thoại thông báo – Input Message: nội dung thông báo B1: Tạo ô đại diện cho dòng cột B2: Đặt trỏ vị trí giao tiêu đề bảng thống kê ( ví dụ B30) dùng công thức mảng hàm SUM kết hợp với hàm IF sau: Cú pháp: SUMIFS(Vùng tính tổng, vùng DK 1, Dk1, vùng Dk2, Dk2, ) B3: Bơi đen tồn bảng thống kê bao gồm tiêu đề ô đặt công thức hàm Vào Data -> What-If Analysis -> Data Table B4: Trong hộp thoại Data Table chọn – Row Input Cell: chọn ô đại diện dòng $G$33 – Column Input Cell: chọn đại diện cột $G$32 =>OK Bài 5: CÁC HÀM THỐNG KÊ VÀ DỊ TÌM Hàm dò tìm (khơng có giá trị dò) a Hàm INDIRECT • Chức năng: Hàm INDIRECT trả tham chiếu từ chuỗi ký tự Cũng dùng hàm INDIRECT để thay đổi tham chiếu tới ô bên công thức mà không cần phải thay đổi cơng thức • Cú pháp: INDIRECT(Tham chiếu đến ô) tham chiếu đến ô bảng tính Giá trị đặt tên bảng phụ phải trùng tên với bảng tính •  Nếu tham chiếu không hợp lệ, hàm trả lỗi #REF! •  Nếu tham chiếu chứa tham chiếu đến bảng tính khác bảng tính phải mở, không hàm trả #REF! b Hàm dò tìm theo cột -Vlookup (giá trị dò, INDIRECT(ThamChieuDenO), n , cách dò) c Hàm dò tìm theo cột -Hlookup (giá trị dò,INDIRECT(ThamChieuDenO) , n , cách dò) 3.Hàm MATCH Cơng dụng: Trả vị trí giá trị cần tìm Cú pháp: =MATCH (lookup_value, lookup_array, ) – Lookup_value: giá trị cần dò – Lookup_array : vùng liệu cầ dò tìm – Match_type: kiểu dò tìm (1,-1,0) Hàm INDEX Cơng dụng: Tìm giá trị bảng (hoặc mảng) biết vị trí bảng (hoặc mảng) này, dựa vào số thứ tự hàng số thứ tự cột Cú pháp: = INDEX (array,row_num, col_num) – Array: dãy mảng – Row_num: số dòng giá trị mảng cần trả Nếu bỏ qua row_num buộc phải có col_num – Col_num: số cột giá trị mảng cần trả Nếu bỏ qua col_num buộc phải có row_num Kết hợp hàm INDEX với hàm MATCH để dò tìm cho bảng gồm nhiều dòng nhiều cột Cú pháp: = INDEX (array, MATCH (lookup_value, lookup_array,), MATCH (lookup_value, lookup_array,)) -array: vùng kết + F4 (bảng phụ) – lookup_value: giá trị DK cột (bảng tính) – lookup_array: vùng DK cột +F4 (bảng phụ) – match_type : cách dò – lookup_value : giá trị DK dòng (bảng tính) – lookup_array : vùng DK dòng +F4 (bảng phụ) – match_type: cách dò Vd: câu 15 liệu Thực hành Một Số Hàm Thống Kê – Đếm có DK: COUNTIF(vùng DK, DK ) – Đếm có nhiều DK: COUNTIFS( vùng DK1, DK1, vùng DK2, DK2,…) – Lớn vị trí thứ n: LARGE( vùng cần tính, n) – Nhỏ vị trí thứ n: SMALL ( vùng cần tính, n) 3.Thống kê cơng thức mảng -Tính tổng có DK: cú pháp: Sum(vùng tính tổng*((vùng DK1=DK1) (vùng DK2=DK2)…)) kết thúc: CTRL+SHIFT+ENTER *: +:hoặc -Đếm có DK: cú pháp: SUM( (Vùng DK=DK)) kết thúc: CTRL+SHIFT+ENTER * + Bài 6: Thống Kê Bằng Công Cụ SUBTOTAL B1: Sắp xếp bảng liệu theo thứ tự tăng dần (Data/ Sort) B2: Quét chọn toàn vùng liệu cần thống kê B3: Vào Menu Data/ Subtotal + At each change in: Chọn Field cần nhóm + Use Function: Chọn hàm thống kê + Add Subtotal to: Chọn cột liệu cần thống kê + Replace current Subtotal: Thay thống kê trước (trường hợp thống kê theo nhiều nhóm khác ta phải làm Subtotal nhiều lần, từ lần thứ ta không chọn Replace) + Page break between groups: Ngắt trang nhóm + Summary below data: Tạo dòng thống kê nhóm Bài 7: Thống Kê Tùy Chọn Bằng PivotTable B1: Chọn vùng liệu cần thống kê B2: Vào Menu Insert/ PivotTable B3: Trong hộp thoại PivotTable + Select a table or range: Lấy liệu từ bảng vùng + Use a external data source: Lấy từ nguồn liệu khác + New WorkSheet: Thống kê qua sheet Ok B4: Xuất hộp thoại PivotTables field list + Report filter: Nơi chứa điều kiện lọc + Columns label: Tiêu đề field muốn hiển thị theo cột + Rows label: Tiêu đề field muốn hiển thị theo dòng + Value: Chọn field muốn lấy giá trị thống kê B5: Tiến hành kéo thả field liệu vào vùng tương ứng bên để có bảng thống kê theo ý muốn Bài 8: Thống Kê Tùy Chọn Consolidate B1: Copy tiêu đề cột cần thống kê vùng riêng B2: Bơi đen chọn tồn vùng tiêu đề vừa copy xuống số dòng rỗng phía ( khơng có liệu) Vào Menu Data -> Consolidate -> xuất hộp thoại consolidate Function: chọn hàm cần thống kê – Reference: quét chọn vùng liệu cần thống kê ( quét vừa đủ từ đối tượng cần thống kê, quét tiêu đề vùng liệu)-> Add – All reference: liệt kê tất vùng liệu cần thống kê – Top row: theo hàng từ xuống – Left colunm: theo cột từ bên trái qua – Create links to source data: tạo liên kết vùng liệu vào bảng thống kê, tự động cập nhật bảng thống kê thay đổi bảng liệu …Bài 2: Các Công Cụ Định Dạng Trong Excel 1/ Conditional Formatting (Định dạng màu sắc theo điều kiện) B1: Chọn vùng cần định dạng (không… dạng cho ô theo điều kiện có sẵn + Format only top or bottom ranked values: Định dạng cho ô liệu cao thấp + Format only value that are above or below average: Định dạng liệu so sánh với trung bình