Thực hành sử dụng phần mềm bảng tính Excel

[tqminh_csp]

http://books.google.com.vn/books?id=lLwFbFCqjhwC&dq=excel&pg=PP1&ots=kw1azKgTfi&sig=NpeTQ4hPLguCNUhkt_d4nVCiVLQ&prev=http://www.google.com.vn/search%3Fhl%3Dvi%26q%3Dexcel%26meta%3D&sa=X&oi=print&ct=result&cd=1#PPR6,M1

http://books.google.com.vn/books?id=pAFr45u3JjoC&dq=excel&pg=PP1&ots=G6rltXyJKU&sig=INZ2bS3mjU8vkm8tYW0Os9lA5xA&prev=http://www.google.com.vn/search%3Fhl%3Dvi%26q%3Dexcel%26meta%3D&sa=X&oi=print&ct=result&cd=3

www.microsoft.com/Excel/
http://office.microsoft.com/en-us/excel/default.aspx
http://www.usd.edu/trio/tut/excel/
http://www.edu.net.vn/Default.aspx?tabindex=1&tabid=6&mid=52&tid=180

[tqminh_csp]

http://www.techonthenet.com/excel/formulas/vlookup.php

http://www.fithou.edu.vn/TrainingOnline/2_EBOOK/HTML/Lesson04.html

Nhóm hàm tìm kiếm & tra cứu

Nhóm hàm Lookup

VLOOKUP
HLOOKUP
LOOKUP dạng array
LOOKUP dạng vector

Index và Match
INDEX
MATCH

Nhóm hàm LOOKUP cho phép tìm kiếm theo khoá và trả về một thông tin liên quan đến khoá đó.
Hàm INDEX và MATCH để tra cứu một trị cụ thể từ một bảng.

Nhóm hàm LOOKUP

Vertical Lookup
Cú pháp: VLOOKUP(lookup_value,table_array, col_index_num,range_lookup)

Hàm VLOOKUP tìm trên cột bên trái của vùng table_array cho đến khi thấy lookup_value thì dò sang ngang lấy ô nằm ở cột được chỉ ra bởi col_index_num.
Cột đầu có thể chứa văn bản, số, hay các trị logíc và được sắp xếp tăng dần. Không phân biệt chữ hoa chữ thường. Range_lookup là một trị logical (TRUE hay FALSE). Nếu là TRUE hay bỏ qua thì VLOOKUP trả lại kết quả gần đúng; nếu FALSE, VLOOKUP trả lại kết quả chính xác hoặc #N/A khi không tìm thấy. Nếu không thấy lookup_value, VLOOKUP sẽ tìm giá trị lớn nhất nhỏ hơn lookup_value.

Tham số: lookup_value là một trị, địa chỉ hay chuỗi; table_array là địa chỉ vùng bảng tìm kiếm; col_index_num là cột trong bảng có chứa trị tìm kiếm; và range_lookup (tuỳ chọn) là giá trị lôgíc TRUE hay FALSE (1 hoặc 0).

Kết quả: Trị trong ô cùng hàng với lookup_value trong bảng, ở cột chỉ định; hoặc #N/A nếu không tìm thấy.

Horizontal Lookup
Cú pháp: HLOOKUP(lookup_value,table_array, row_index_num,range_lookup)

Hàm HLOOKUP tìm trên hàng đầu của vùng table_array cho đến khi gặp lookup_value thì dò xuống đến hàng chỉ ra bởi row_index_num. Range_lookup là một giá trị kiểu logíc (TRUE hay FALSE). Nếu là TRUE hay bỏ qua, HLOOKUP trả lại kết quả gần đúng. Nếu là FALSE, HLOOKUP trả lại kết quả chính xác hay #N/A nếu không tìm thấy. Hàng đầu của table_array phải được sắp xếp tăng dần, theo chữ cài hay số . Trị tìm kiếm lookup_value và các giá trị trong hàng đầu của table_array có cùng kiểu và có thể là văn bản, số hay trị lôgíc.
Row_index_num bắt đầu từ 1. Để trả lại một trị nằm trong hàng đầu - nhập 1, hàng hai - nhập 2, và tiếp tục. Nếu row_index_num bé hơn 1, HLOOKUP sinh lỗi #VALUE!. Nếu row_index_num lớn hơn số hàng có trong bảng hàm sinh lỗi #REF!.

Tham số: lookup_value là trị, địa chỉ, hay chuỗi văn bản phụ thuộc vào hàng đầu của bảng; table_array là địa chỉ của bảng; row_index_num là số thứ tự của hàng trong bảng có chứa trị cần tìm; range_lookup (tuỳ chọn) có giá trị TRUE hay FALSE (1 hoặc 0).

Kết quả: Giá trị trong ô cùng cột với lookup_value, hàng chỉ định; #N/A nếu không thấy.

Lookup dạng mảng
Cú pháp: LOOKUP(lookup_value,array)

Hàm LOOKUP hoạt động tương tự như HLOOKUP và VLOOKUP. LOOKUP tìm kiếm lookup_value trên hàng đầu hay cột đầu của bảng array, tuỳ thuộc vào hình dạng của bảng array. Nếu bảng array có hình vuông hoặc rộng hơn cao, LOOKUP tìm lookup_value trên hàng đầu. Nếu bảng cao hơn rộng, tìm kiếm thực hiện trên cột đầu. Nếu LOOKUP không tìm thấy lookup_value, nó sẽ lấy giá trị lớn nhất dưới lookup_value. Nếu lookup_value nhỏ hơn giá trị bé nhất trong hàng hay cột được tìm hàm trả về lỗi #N/A. Giá trị trả về được lấy ở hàng cuối hay cột cuối của bảng lookup_value. Cột hay hàng tìm kiếm phải được sắp xếp theo trật tự tăng dần.

Tham số: lookup_value dưới dạng số, chuỗi hay giá trị lôgíc; array là địa chỉ vùng tìm kiếm.

Kết quả: Giá trị của của một ô ở hàng cuối hay cột cuối của bảng tìm kiếm.

Lookup dạng véc tơ
Cú pháp: LOOKUP(lookup_value,lookup_vector,result_vector)

Tìm kiếm trị lookup_value trên hàng hay cột chỉ ra bởi lookup_vector cho đến khi thấy trị cần tìm thì sẽ dò sang ô tương ứng trong result_vector và trả về trị trong ô đó.

Tham số: Lookup_value là giá trị mà LOOKUP tìm kiếm trên véc-tơ thứ nhất. Lookup_value có thể là số, văn bản, giá trị logíc, tên hay địa chỉ. Lookup_vector là cột hay hàng chứa văn bản, số hay kiểu logíc và được sắp xếp tăng dần. Result_vector là hàng hay cột chứa trị trả về có cùng kích thước như lookup_vector .

Cặp hàm INDEX và MATCH

Hàm Index
Cho tham chiếu tới hay trị của một ô hay một mảng

Cú pháp: hai dạng

=INDEX(array,row_num,column_num)

=INDEX(reference,row_num,column_num, area_num)

Dạng đầu trả về trị của một ô hay mảng các trị của các ô. Dạng hai trả về tham chiếu tới một ô hay mảng các ô.

Tham số: Array là địa chỉ mảng. Row_num số thứ tự của hàng, Column_num số tứ tự của cột. Reference là địa chỉ vùng lớn, area_num là số thứ tự của vùng con trong vùng lớn.

Kết quả:Trị của ô, mảng trị cho dạng 1; tham chiếu ô, mảng tham chiếu cho dạng 2

Ví dụ:

Hàm sau cho kết quả là 10:

=INDEX({2,3,4;10,11,12},2,1)

Công thức sau trả về tham chiếu tới ô C5, nằm ở hàng thứ hai và cột thứ ba trong vùng thứ nhất:

=INDEX((A4:D7,F10:J15),2,3,1)



Hàm Match
Cú pháp: =MATCH(lookup_value,lookup_array, match_type)

Hàm MATCH trả về vị trí tương đối của lookup_value trong lookup_array. Kiểu tìm do match_type chi phối. Trị lookup_value có thể là số, chuỗi, giá trị logíc hay địa chỉ ô. MATCH trả lại vị trí của hàng hay cột trong mảng có chứa trị tìm thấy. Thường được sử dụng kết hợp với hàm INDEX để tra một trị trong mảng.

Các dạng so khớp

Match_type
Tìm kiếm

1, hay bỏ qua
Trị lớn nhất nhỏ hơn hoặc bằng lookup_value. Lookup_array phải được sắp xếp tăng

0
Tìm chính xác.

-1
Trị bé nhất nhất lớn hơn hoặc bằng lookup_value. Lookup_array phải được sắp xếp giảm.


Tham số: lookup_value là trị cần tìm trên mảng; lookup_array là mảng chứa các trị; và match_type (tuỳ chọn) là một trong các số 1, 0, hay -1, chi phối cách tìm kiếm.

Kết quả: Vị trí tương đối lookup_value trong lookup_array.

[tqminh_csp]

http://www.fithou.edu.vn/TrainingOnline/2_EBOOK/

Giới thiệu
Excel là phần mềm bảng tính phục vụ công tác quản lý, lưu trữ tầm vừa và nhỏ. Excel tạo và quản lý sổ sách điện tử, có các trang là các bảng kẻ sẵn với khả năng lưu trữ và tính toán.

Workbook
Tài liệu của excel là các sổ điện tử (Workbook)

Workbook Có tối đa 255 worksheets (Bảng tính)

Worksheet Gồm 256 columns (cột) đánh thứ tự từ A..Z,AA..AZ,..IV (nhấn Ctrl+mũi tên sang để đến cột cuối)
Gồm 65536 rows (hàng) đánh số từ 1..65536) (nhấn Ctrl+¯mũi tên xuống để đến hàng cuối

Cell (Ô) Giao của hàng và cột
Mỗi ô có địa chỉ, ví dụ ô A3, C5, AZ335

Range (Vùng) Vùng các ô cạnh nhau để lưu dữ liệu có cùng tính chất
Xác định bởi <Địa chỉ ô đầu>:<Địa chỉ ô cuối>
Ví dụ A3:C5


Các thao tác căn bản
Tạo sổ mới, lưu giữ
Khi chạy Excel mở kèm một sổ bảng tính mới có tên ngầm định là Book1.

Mở sổ mới: Trên thanh công cụ nhấn nút New , hay trên menu File, chọn New
Cất giữ sổ: Lên mục File, chọn Save hoặc Save as hay nhấn lên nút Save trên thanh công cụ

Nhập liệu vào bảng tính
Dữ liệu trong một ô có thể và văn bản hay số

Văn bản (Text) Một ô sẽ nhận kiểu văn bản nếu bạn bắt đầu nhập vào chữ cái. Một ô nhận tối đa 255 ký tự.
Ô chứa văn bản tự động căn sang bên trái. Đôi khi bạn cần nhập số dưới dạng văn bản - ví dụ ($000) - trước hết bạn nhập dấu nháy đơn (') theo sau là số - ví dụ '39,800. Để nới rộng cột chứa hết văn bản, kéo trên tiêu đề cột.
Số (Number) Một ô có kiểu số nếu bạn nhập vào một số. Ví dụ 973, 908.37, 0.72, 3 1/4, 5.87137E+3.
Nếu ô không đủ rộng để chứa hết số, nó sẽ xuất hiện toàn dấu thăn (#######)
Có thể nhập phân số: 3 1/4 hay 0 1/2
Ngày tháng Nhập ngày theo dạng sau: 11/6/97, 6-Nov-97, 6-Nov, Nov-97
Ngày là số tính từ 1/1/1900 đến ngày bạn nhập.
Nhấn Ctrl+; (chấm phảy) để nhập ngày hôm này
Trật tự ngày/tháng/năm do Windows quy định (Settings>Control Panel>Regional Settings)
Giờ Nhập theo đinh dạng: 21:41, 21:41:35, 9:41 PM, 9:41:35 PM
Ctrl+: (hai chấm) để nhập giờ hiện hành

Nhập trên một vùng
Để tăng tốc độ nhập liệu bạn có thể chọn trước vùng nhập liệu. Excel tự chuyển sang ô kế tiếp khi kết thúc nhập một ô

Chọn vùng nhập liệu. Ô hiện hành có nền trắng.
Nhập dữ liệu vào ô hiện hành.
Nhấn Enter-xuống, Shift+Enter - lên, Tab - sang phải, Shift+Tab - sang trái một ô.
Nhập chuỗi dữ liệu
Excel có đặc tính tự điền AutoFill, cho phép tự động nhập chuỗi giá trị liên tiếp. Chỉ cần nhập giá trị đầu tiên, AutoFill phân tích và điền các giá trị khác của chuỗi. Có thể dùng AutoFill cho ngày, tháng, năm, chuỗi số,..

Để điền một chuỗi số, nhập hai số đầu cạnh nhau theo hàng hay cột
Chọn hai ô, đưa con trỏ chuột lại thẻ điền tại góc dưới bên phải của vùng chọn, con trỏ chuột trở thành hình chữ thập.
Kéo trên thẻ điền cho đến khi kết thúc vùng điền.
TIP: Nếu muốn điền ngày thì nhập ngày thay vì số. Excel cũng nhận biết chuỗi văn bản dạng Qtr 1, Qtr 2, ... Bạn cũng có thể điền chuỗi tự tạo tuỳ ý. Để nhập chuỗi số tăng có bước là 1, nhập số đầu, nhấn giữ Ctrl trong khi kéo trên thẻ điền.

Nhập công thức
Công thức bắt đầu bằng dấu bằng "=", sau đó là biểu thức tính toán. Trong biểu thức có các toán tử và các toán hạng. Toán tử là các phép toán số học như cộng, trừ, nhân, chia, luỹ thừa,... Trật tự ưu tiên của các toán tử cũng giống như một biểu thức toán học thông thường. Để ưu tiên một phép toán yếu hơn trước một phép toán có độ ưu tiên cao hơn ta dùng cặp ngoặc tròn (). Toán hạng có thể là hằng xâu, số hay địa chỉ các ô chứa các giá trị đó. Ví dụ của công thức như : =A5*B5, =30000+B5. Toán hạng cũng có thể là các hàm sẽ đề cập đến trong bài sau.

Đứng ở ô cần nhập công thức, gõ dấu bằng, sau đó bắt đầu nhập biểu thức tính toán
Kết thúc gõ Enter hay nhấn lên nút trên thanh công thức.
Sao chép công thức
Trong nhiều trường hợp công thức của các ô cùng cột giống nhau. Ta chỉ cần nhập công thức cho ô đầu tiên, và sao chép công thức xuống các ô còn lại. Địa chỉ của các ô tham gia vào công thức cũng tự thay đổi tương ứng (địa chỉ tương đối). Để địa chỉ các ô tham gia vào công thức không đổi khi copy thì ta phải đánh địa chỉ tuyệt đối (sẽ trình bày ở bài sau).

Chọn ô cần nhập công thức (ô đầu tiên của cột cần nhập công thức)
Nhập công thức
Để sao chép công thức dưới ta kéo trên thẻ điền


Chọn ô, vùng
Rất nhiều lệnh của Excel như sao chép hay định dạng đòi hỏi bạn phải chọn vùng.

Chọn một ô nhấn chuột lên ô đó hay dùng bàn phím để duyệt đến ô cần chọn
Chọn một vùng nhấn chuột lên ô đầu tiên; sau đó nhấn giữ nút chuột trái và kéo tới góc đối xứng của vùng cần chọn. Thả phím chuột
Chọn một vùng lớn chọn ô đầu tiên. Sau đó cuộn bảng tính cho đến khi bạn nhìn thấy góc đối xứng. Nhấn giữ Shift, và nháy chuột lên góc đối xứng của vùng cần chọn. Tất cả các ô nằm giữa hai góc sẽ được chọn
Chọn các ô theo nội dung hay định dạng Bôi đen vùng, lên Edit>Goto>Special: (comments), hằng số (constants), công thức (formulas), định dạng có điều kiện (conditional formats), hay các đối tượng như đồ thị (charts)
Sau khi chọn các ô với Edit, Go To, Special, bạn có thể giữ nguyên vùng chọn và dịch chuyển qua lại giữa các ô bằng cách nhấn Tab, Shift+Tab, Enter, hay Shift+Enter
Chọn Nhiều vùng Chọn vùng đầu tiên; Nhấn giữ phím Ctrl và kéo để chọn vùng tiếp theo. Lặp lại cho các vùng khác
Chọn cột/hàng Chọn một cột/hàng nháy chuột lên tiêu đề cột
Chọn nhiều cột/hàng cạnh nhau, kéo rê con trỏ chuột trên các tiêu đề cột/hàng
Chọn nhiều cột/hàng không liền nhau, nhấn phím Ctrl và nháy chuột lên mỗi tiêu đề cột/hàng
TIP: Shift+space/ Ctrl+space để chọn hàng/cột hiện hành.
Chọn cả bảng tính Nhấn lên nút giao giữa tiêu đề hàng và tiêu đề cột
Chọn nhiều bảng tính Nhấn lên các phiếu bảng tính ở đáy của sổ bảng tính
Chú ý: Nếu bạn chọn toàn bộ bảng tính thì các lệnh thực hiện sẽ tác động lên toàn bảng tính. Ví dụ nếu bạn nhấn phím Delete toàn bộ dữ liệu sẽ bị xoá.

Chỉnh sửa dữ liệu
Để sửa nội dung một ô Chọn ô cần sửa
Nhấn phím F2 hoặc nhấn kép lên ô hay nhấn lên thanh công thức
Hiệu chỉnh dữ liệu, kết thúc nhấn Enter.

Để xoá nội dung một ô Chọn ô cần xoá
Nhấn Delete


Chọn Font tiếng Việt
Thông thường chúng ta hay phải gõ tiếng Việt trong bảng tính. Nếu trước khi chọn định dạng Font bạn chỉ đứng ở một ô đơn lẻ thì định dạng chỉ có tác dụng cho ô đó.

Chọn font tiếng Việt cho toàn bộ bảng tính Chọn toàn bộ bảng tính (nút mầu xám nằm giao giữa tiêu đề hàng và tiêu đề cột)
Chọn Font tiến việt và cỡ chữ từ hộp font trên thanh công cụ định dạng
Nháy vào ô bất kỳ để huỷ chọn

Đặt font tiếng việt ngầm định cho Excel Lên menu Tools, chọn Options, chọn phiếu General
Dưới mục Standard font chọn font bạn muốn đặt ngầm định. Chọn cỡ chữ ngầm định trong hộp Size
Font chữ này sẽ trở thành ngầm định mỗi khi bạn mở EXCEL lần sau

Đặt font cho toàn bộ các bảng của sổ đang mở Lên Format, chọn Style, xuất hiện cửa sổ Style, dưới Style name chọn Normal
Nhấn nút Modify, xuất hiện cửa sổ Format cells, chọn phiếu Font, Chọn Font tiếng việt, cỡ chữ sau đó nhấn OK quay lại cửa sổ Style
Nhấn OK lần nữa để đóng cửa sổ Style

[tqminh_csp]

các hàm Căn bản-
Nhóm hàm toán học & lượng giác
Nhóm hàm thống kê
Nhóm hàm chuỗi
Nhóm hàm ngày giờ
Nhóm hàm logíc
Nhóm hàm toán học - lượng giác (Math & Trig)
ABS(number) Trả lại trị tuyệt đối của tham số number
ABS(-5) ->5
INT(number) Làm tròn một số tới số nguyên gần nhất;
INT(3.2)->3,
INT(-3.2)->-4

MOD(number,divisor) Trả lại số dư của phép chia number cho số chia divisor
MOD(10,3) ->1
MOD(11,3)->2
ROUND(number,num_digits) Làm tròn tham số number đến vị trí thứ num_digits của phần thập phân;
ROUND(123.456,2) -> 123.46
ROUND(123.456,1)->123.5
ROUND(123.456,0)->123
ROUND(123.456,-1)->120
SQRT(number) Tính căn hai của number
SQRT(25)->5
SUMIF(range,criteria,sum_range) Cộng các ô thoả mãn một tiêu chuẩn nào đó.
Range là vùng các ô cần ước lượng.
Criteria là tiêu chuẩn dưới dạng số, biểu thức, hay văn bản xác định các ô sẽ được cộng. Ví dụ về tiêu chuẩn là 32, "32", ">32", "apples".
Sum_range là các ô được cộng. Các ô trong vùng sum_range chỉ được cộng nếu các ô tương ứng trong vùng range phù hợp với tiêu chuẩn criteria. Nếu bỏ qua sum_range thì chính các ô trong vùng range được cộng
Giả sử các ô từ A1:A4 lần lượt chứa các giá bất động sản sau: $100,000, $200,000, $300,000, $400,000. Còn các ô B1:B4 chứa tiền hoa hồng lần lượt ứng với mỗi giá bất động sản: $7,000, $14,000, $21,000, $28,000.
SUMIF(A1:A4,">160000",B1:B4) bằng $63,000

Nhóm hàm thống kê (Statistical)
AVERAGE(number1,number2,...) Tính trung bình cộng của các tham số number1, number2,....
Ví dụ Trong các ô B1,B2,B3 chứa lần lượt 4, 8, 3; thì khi đó AVERAGE(B1:B3)->5
COUNT(value1,value2,...) Đếm các ô kiểu số trong danh sách đối value1, value2,..
Countif(range,criteria) Đếm các ô trên vùng range thoả mãn tiêu chuẩn criteria
Range là vùng các ô cần đếm.
Criteria là tiêu chuẩn dưới dạng số, biểu thức, hay chuỗi văn bản chỉ định ô được đếm. Ví dụ tiêu chuẩn có thể viết dưới dạng sau 32, "32", ">32", "apples"
Giả sử A3:A6 chứa tương ứng: "apples", "oranges", "peaches", "apples". Khi đó COUNTIF(A3:A6,"apples") bằng 2
Giả sử B3:B6 chứa 32, 54, 75, 86. Khi đó COUNTIF(B3:B6,">55") bằng 2

MAX(number1,number2,...) Trả lại trị lớn nhất trong các tham biến
Ví dụ Dữ liệu trong các ô D1,D2,D3 lần lượt là 5,7,9; trong ô D4 nhập công thức =MAX(D1:D3) khi đó D4 bằng 9
MIN(number1,number2,...) Trả lại trị bé nhất trong các tham biến
Ví dụ Dữ liệu trong các ô D1,D2,D3 lần lượt là 5,7,9; trong ô D4 nhập công thức =MIN(D1:D3) khi đó D4 bằng 5
SUM(number1,number2,..) Tính tổng các số trong danh sách tham số
Ví dụ Dữ liệu trong các ô D1,D2,D3 lần lượt là 5,7,9; trong ô D4 nhập công thức =SUM(D1:D3) khi đó D4 bằng 21
Nhóm hàm chuỗi (Text)
LEFT(text,num_char) Kết quả là chuỗi con được trích ra từ bên trái của chuỗi text đủ num_char ký tự
Ví dụ LEFT("Lê Thị Minh Nguyệt",6) bằng "Lê Thị"
RIGHT(text,num_char) Kết quả là chuỗi con được trích ra từ bên phải của chuỗi text đủ num_char ký tự
Ví dụ RIGHT("Lê Thị Minh Nguyệt",11) bằng "Minh Nguyệt"
MID(text,start_num,num_char) Kết quả là chuỗi con được trích từ vị trí start_num, đủ num_char ký tự
Ví dụ MID("Lê Thị Minh Nguyệt",8,4) bằng "Minh"
LEN(text) Trả lại chiều dài của chuỗi text
Ví dụ LEN("Lê Thị Minh Nguyệt") bằng 18
UPPER(text) Đổi chuỗi text thành chữ hoa (không chính xác khi dùng với Font tiếng Việt)
Ví dụ UPPER("Thomas Edison") bằng THOMAS EDISON
LOWER(text) Đổi chuỗi text thành chữ thường (không chính xác khi dùng với Font tiếng Việt)
Ví dụ UPPER("Thomas Edison") bằng thomas edison
PROPER(text) Đổi chữ cái đầu tiên của các từ thành chữ hoa, còn lại là chữ thường (không chính xác khi dùng với Font tiếng Việt)
Ví dụ PROPER("bill gates") bằng Bill Gates
TRIM(text) Cắt bỏ các ký tự trắng ở đầu và cuối chuỗi text.
Ví dụ TRIM(" Lê Quốc Minh ") bằng Lê Quốc Minh

TEXT(value,format_text) Đổi số value thành chuỗi theo khuôn dạng chỉ định trong format_text

Ví dụ TEXT(1234.56,"##,###.###") bằng 1,234.56

VALUE(text) Đổi chuỗi có kiểu số thành số
Ví dụ VALUE("123") bằng 123
Nhóm hàm ngày giờ (Date & Time)
DATE(year,month,day) Trả về ngày ứng với ngày, tháng, năm cung cấp trong tham số.
Ví dụ Date(2000,08,22) bằng 22/08/2000
DAY(serial_number) Trả lại ngày của tham số serial_number kiểu ngày tháng
Ví dụ DAY("24-12-2000") bằng 24
Chú ý: Tham số kiểu ngày có trật tự ngày - tháng - năm tuân theo thiết lập trong Control Panel trong máy của bạn. Trước khi sử dụng các hàm có liên quan đến kiểu ngày hãy kiểm tra xem máy đang đặt trật tự ngày - tháng - năm hay tháng - ngày - năm, hay một trật tự khác.
NOW() Trả lại giờ hiện hánh
TODAY() Trả lại ngày hôm nay
MONTH(serial_number) Trả lại tháng của tham số serial_number kiểu ngày tháng
Ví dụ MONTH("8-3-2000") bằng 3 (giả sử máy đang đặt ngày theo trật tự ngày-tháng-năm)
YEAR(serial_number) Trả lại năm của tham số serial_number kiểu ngày tháng

Ví dụ YEAR("8-3-2000") bằng 2000 (giả sử máy đang đặt ngày theo trật tự ngày-tháng-năm)
Nhóm hàm logíc (Logical)
AND(logical1,logical2,...) Cho kết quả là phép Và của các biểu thức logíc. Hàm AND cho kết quả TRUE nếu tất cả các biểu thức logíc là TRUE, ngược lại hàm AND cho kết quả FALSE
Ví dụ AND(1000>900,LEFT("excel",2)="ex") bằng TRUE
OR(logical1,logical2,...) Cho kết quả là phép hoặc của các biểu thức logíc. Hàm OR cho kết quả TRUE nếu có một trong các biểu thức logíc là TRUE, ngược lại nếu tất cả biểu thức logíc là FALSE thì hàm OR cho kết quả FALSE.
Ví dụ OR(1000>9000,LEFT("excel",2)="ex") bằng TRUE
OR(2>3,5>8) bằng FALSE
NOT(logical) Cho kết quả là phép phủ định của biểu thức logíc. Hàm NOT cho kết quả TRUE nếu biểu thức logíc là FALSE và ngược lại.
Ví dụ NOT(3>2) bằng FALSE
NOT(AND(1000>900,LEFT("excel",2)="ex")) bằng FALSE

IF(logical_test,value_if_true,
value_if_false) Hàm IF cho kết quả:

value_if_true nếu logical_test là TRUE
value_if_false nếu logical_test là FALSE

Ví dụ nhập công thức =IF(D2>5,"Trên TB","Dưới TB") trong ô D3
Nếu nhập 6 trong D2 ta có "Trên TB" trog D3
Nếu nhập 4 trong D2 ta có "Dưới TB" trog D3

[tqminh_csp]

-Quản lý dữ liệu
Giới thiệu
Cơ sở dữ liệu
Tạo một CSDL
Thêm bản ghi
Xoá bản ghi
Tìm kiếm bản ghi
Sắp xếp
Tạo Custom List
AutoFilter
Advanced Filter

Giới thiệu
Một trong các ứng dụng căn bản của một chương trình bảng tính là phân tích dữ liệu. Bên cạnh các công cụ phân tích số liệu đơn giản dễ sử dụng, Excel còn cung cấp nhiều công cụ mạnh khác cho kỹ thuật, khoa học và phân tích tài chính.

Bạn có thể dùng Excel để tạo lập cơ sở dữ liệu hay danh mục thông tin. Các tính năng như Data Form giúp bạn nhanh chóng thêm, bớt và tìm kiếm các bản ghi. Bạn cũng có thể sắp xếp và lọc dữ liệu trong cơ sở dữ liệu. Sử dụng các tính năng như outlining hay pivot table, bạn có thế tổng hợp số liệu theo nhiều góc độ khác nhau. Bạn có thể dùng Goal Seek và Solver để tìm lời giải tối ưu cho các vấn đề tài chính.

Các tính năng khác của Excel như AutoFill, AutoComplete, và Pick From List cho phép bạn tăng tốc nhập liệu. Bạn có thể sử dụng Template Wizard để tạo nên những mẫu nhập liệu có kiểu dạng chuyên nghiệp. Biểu mẫu có thể chứa các hộp điều khiển như hộp kiểm tra, thanh trượt.

Cơ sở dữ liệu
Tạo một CSDL
Một trong những ứng dụng căn bản của Excel là quản trị dữ liệu. Bạn có thể dùng các tính năng của Excel để làm việc với một danh sách đơn giản ví dụ mục các việc cần làm cho đến các cơ sở dữ liệu phức tạp hơn. Trong Excel, khái niệm cơ sở dữ liệu và danh sách đều là một. Chúng ta sử dụng khái nhiệm cơ sở dữ liệu để ám chỉ cả hai: danh sách đơn và cơ sở dữ liệu phức tạp.

Một CSDL chứa các bộ dữ liệu giống nhau như cơ sở dữ liệu khách hàng. Một hàng thông tin trong CSDL được gọi là bản ghi record. Các mục tin được ghi trong các trường, mỗi cột là một trường. Ví dụ tên khách hàng được lưu trong trường tên. Nếu tổ chức dữ liệu dưới dạng cơ sở dữ liệu, bạn có thể nhanh chóng sắp xếp, lọc và tổng hợp thông tin.

Các bước

Nhập tên trường trên một hàng của bảng tính. Các tên trường này (ví dụ Họ Tên, Địa Chỉ, Điện Thoại, ... ) là các tiêu đề cột của cơ sở dữ liệu.
Chọn vùng tên trường và định dạng ví dụ chọn chữ đậm, nghiêng hay chọn Font chữ to hơn để làm nổi bật trường với các bản ghi.
Nhập các bản ghi (như thông tin từng khách hàng) ngay dưới tên trường, mỗi bản ghi trên một hàng. Không để các hàng trắng sau tên trường.
Chú ý: Cơ sở dữ liệu của Excel sự thực chỉ là một dạng bảng tính đặc biệt. Chúng ta vẫn có thể dùng các lệnh bảng tính bình thường để hiệu chỉnh và định dạng cơ sở dữ liệu.

Chèn một bản ghi
Tính năng data form cung cấp cách thuận lợi để chèn, xoá và tìm bản ghi. Bạn cũng có thể chèn, xoá trực tiếp trên bảng tính.

Các bước

Chọn một ô bất kỳ trong CSDL; sau đó gọi Data, Form.
Nhấn nút New ; sau đó nhập bản ghi mới vào các hộp văn bản.
Lặp lại bước 2 cho mỗi bản ghi mới bạn muốn thêm.
Nhấn Close khi kết thúc. Các bản ghi mới được thêm nối đuôi vào CSDL. Bạn có thể sắp xếp lại các bản ghi theo một trật tự khác.
TIP: Để không ghi bản ghi vừa nhập, nhấn nút Restore.
Để chèn trực tiếp bản ghi vào bảng tính, nhấn chuột phải lên tiêu đề hàng nơi bạn muốn hàng mới xuất hiện. Sau đó chọn Insert từ menu tắt để chèn một hàng trắng vào CSDL.

Xoá bản ghi
Bạn có thể xoá trực tiếp ngay trên bảng tính hoặc sử dụng data form để tìm đến và xoá một bản ghi cụ thể.

Các bước

Để xoá một bản ghi sử dụng data form, chọn một ô bất kỳ trong CSDL; sau đó gọi Data, Form.
Nhấn nút Find Next hay Find Prev cho đến khi tới bản ghi cần xoá, nhấn Delete.
Khi một cửa sổ cảnh báo xuất hiện, nhấn OK để xoá bản ghi. (Nhớ rằng bạn không thể Undo một bản ghi bị xoá). Nhấn nút Close để quay lại bảng tính.
TIP: Để xoá trực tiếp một bản ghi từ bảng tính, nhấn chuột phải lên tiêu đề hàng có chứa bản ghi cần xoá. Sau đó chọn Delete từ menu tắt. Với cách này bạn có thể phục hồi lại bản ghi vừa xoá sử dụng Edit, Undo.

Tìm kiếm bản ghi
Khi cần tìm các bản ghi cụ thể, bạn có thể dùng data form để tìm. Chỉ cần nhập tiêu chuẩn tìm kiếm và Excel sẽ tìm các bản ghi thoả mãn tiêu chuẩn.

Các bước

Chọn một ô bất kỳ trong CSDL; sau đó gọi Data, Form.
Nhấn nút Criteria; sau đó nhập tiêu chuẩn cần tìm trong một hay nhièu hộp văn bản. Excel tìm các bản ghi thoả mãn tiêu chuẩn.
Nhấn Find Next cho đến khi gặp bản ghi cần tìm, để thoát khỏi Form nhấn nút Close.
TIP: Trong phần tiêu chuẩn bạn có thể dùng các ký tự thay thế (* và ?) cho trường văn bản và các toá tử so sánh đối với trường số.

Sắp xếp một cơ sở dữ liệu
Bạn có thể sắp xếp bất kỳ dữ liệu nào trên bảng tính, tuy nhiên tổ chức dữ liệu dưới dạng CSDL cho phép sắp xếp dữ liệu nhanh nhất. Bạn có thể sắp xếp tăng hay giảm.

Các bước

Chọn một ô bất kỳ trong CSDL; sau đó gọi Data, Sort.
TRong hộp Sort By, chọn trường khoá sắp xếp chính, sau đó chọn trật tự tăng (Ascending) hay giảm (Descending).
Nếu bạn muốn chỉ định khoá xắp xếp phụ, chọn chúng trong hộp Then By, và chỉ định trật tự cho mỗi khoá phụ. Nhấn OK để thực hiẹn sắp xếp.
TIP: Để nhanh chóng sắp xếp cơ sở dữ liệu theo một trường đơn, chọn một ô bất kỳ trong trường đó. Sau đó nhấn nút Sort Ascending hay Sort Descending trên thanh công cụ chuẩn.

Chú ý: Ngoài trật tự tăng giảm ngầm định bạn cũng có thể yêu cầu Excel sắp xếp CSDL theo một trật tự do bạn chỉ định. Trước hết bạn phải tạo ra trật tự này gọi là Custom List. Sau đó gọi Data, Sort, nhấn mục Option, dưới mục First key sort order chọn mục custom list. Tính năng này hữu ích khi bạn ví dụ như muốn sắp xếp trật tự tiếng Việt

Tạo một Custom List
Excel cho phép tạo và chèn một danh sách người dùng vào một bảng tính. Giá thiết bạn phải thường xuyên nhập tên các công ty hay các ngày trong tuần bằng tiếng Việt. Bạn có thể tạo một custom list.

Các bước

Lên Tools, Options; chọn phiêu Custom Lists.
Trong hộp List Entries, nhập các mục cho custom list. Nhấn Enter sau mỗi mục.
Nhấn Add. Danh sách nhập vào xuất hiện trong Custom Lists box.
Nếu bạn muốn tạo thêm các custom list mới, nhấn NEW LIST trong cửa sổ Custom Lists, lặp lại các bước 2 và 3 trên.
Nhấn OK để đóng cửa sổ.
TIP: Nếu bạn muốn tạo một custom list dựa trên các ô sẵn có trong bảng tính thì hãy nhập địa chỉ vùng ô trong Import List From Cells. Sau đó nhấn nút Import để nhập danh sách.

AutoFilter
Tính năng AutoFilter cho phép bạn lọc và giữ lại một tập con các bản ghi của một danh sách mà không làm ảnh hưởng đến danh sách gốc. Cạnh mỗi trường sẽ xuất hiện một mũi tên sổ xuống để bạn chọn tiêu chuẩn lọc.

Các mục chọn trong AutoFilter

Các mục chọn để đặt tiêu chuẩn lọc được liệt kê trong bảng sau:

Mục
Diễn giải

(All)
Hiển thị toàn bộ bản ghi cho trường này(mục ngầm định).

(Top 10)
Cho phép lọc lại một số chỉ định các bản ghi có trị đứng đầu hoặc đứng cuối danh sách.

(Custom)
Hiển thị cửa sổ Custom AutoFilter để bạn tạo tiêu chuẩn AND hoặc OR.

(Trị cụ thể)
Chỉ hiển thị bản ghi có trị bằng chính xác trị bạn chọn cho trường này.

(Blanks)
Hiển thị tất cả bản ghi trắng trong trường này.

(NonBlanks)
Hiển thị tất cả các bản ghi có dữ liệu trên trường này.


Các bước

Chọn một ô bất kỳ trong danh sách; sau đó chọn Data, Filter, AutoFilter.
Nhấn lên mũi tên bên cạnh trường chứa dữ liệu bạn định lọc.
Chọn tiêu chuẩn lọc cho trường theo các mục chọn trên. Khi lọc, các hàng không thoả mãn sẽ bị ẩn
Để bỏ chế độ lọc AutoFilter, gọi Data, Filter, AutoFilter. Các mũi tên sẽ biến mất.
Advanced Filter
Nếu bạn phải lọc dữ liệu với tiêu chuẩn lọc phức tạp thì bạn nên sử dụng Advanced Filter. Kết quả cũng như lọc với AutoFilter tuy nhiên không có các mũi tên bên cạnh trường. Để sử dụng Advanced Filter bạn phải tạo một vùng tiêu chuẩn lọc. Tốt nhất bạn nên đặt vùng tiêu chuẩn lọc lên trên đỉnh hay dưới đáy danh sách.

Tiêu chuẩn lọc phức tạp có hai loại:Tiêu chuẩn so sánh và Tiêu chuẩn tính toán

Tiêu chuẩn lọc so sánh cho phép lọc lại những bản ghi nằm trong một giới hạn nào đó. Tiêu chuẩn lọc có thể là trị chuỗi, số logíc hay biểu thức so sánh.
Tiêu chuẩn tính toán là tiêu chuẩn lọc mà trị so sánh phải được tính toán hoặc lọc trên cột ảo được tính từ các cột khác.
Tạo vùng tiêu chuẩn lọc

Trên hàng đầu nhập tên trường giống hệt như tên trường cần lọc trên cơ sở dữ liệu.
Vùng tiêu chuẩn tính toán có tên trường khác với mọi tên trường của cơ sở dữ liệu.
Nhập các tiêu chuẩn lọc trên các hàng bên dưới chứa trị hay phép so sánh với một trị.
Các tiêu chuẩn nằm cùng hàng nếu bạn muốn chúng cùng được thoả mãn (AND).
Các tiêu chuẩn nằm khác hàng nếu chỉ cần một trong số đó thoả mãn (OR)
Tiêu chuẩn tính toán bắt đầu bằng dấu bằng, sau đó là biểu thức logíc.
Tip: Để có tên trường của vùng tiêu chuẩn giống hệt như của vùng CSDL, bạn nên copy tên trường.

Chú ý: Vùng tiêu chuẩn và vùng cơ sở dữ liệu phải cách nhau ít nhất một hàng trắng (hay cột trắng)

Các bước

Nếu chưa có vùng tiêu chuẩn lọc, hãy tạo nó theo nguyên tắc nêu trên
Chọn một ô bất kỳ trong cơ sở dữ liệu. Lên Data, Filter, gọi Advanced Filter.
Trong cửa sổ Advanced Filter, khai báo các thông số cần thiết.
Các thông số trong cửa sổ Advanced Filter

Filter the List In Place
Chọn mục này nếu bạn muốn lọc tại chỗ

Copy to another Location
Chọn mục này nếu bạn muốn copy kết quả ra chỗ khác trên cùng bảng tính. Nếu bạn chọn mục này thì mục Copy To sẽ tự động bật sáng

List Range
Khai báo địa chỉ vùng CSDL (cả tên trường)

Criteria Range
Khai báo địa chỉ vùng tiêu chuẩn lọc (cả tên trường)

Copy To
Chỉ định ô đầu tiên của vùng bạn định đưa kết quả ra đó

Unique Record Only
Nếu có nhiều bản ghi giống nhau thì chỉ giữ lại một bản

[tqminh_csp]

Outline & data consolidate
Outline để hiển thị bảng tính hay báo cáo theo các mức chi tiết khác nhau. Ví dụ với một báo cáo bán hàng bạn muốn in mức chi tiết khác biệt tuỳ vào việc báo cáo để gửi cho ai. Với tính năng outline ta có thể tạo đến tám cấp chi tiết theo hàng hay cột
Consolidate để tổng hợp dữ liệu từ nhiều vùng giống nhau. Ví dụ tổng hợp các báo cáo bán hàng hàng tháng thành quý, và hàng quý thành năm.

Outline
Tạo outlines bằng tay
Có thể tạo outline tự động hoặc bằng tay. Bạn cần tạo ouline bằng tay nếu dữ liệu được tổ chức theo cách mà Excel không thể hiểu được. Nhìn chung bạn nên tổ chức bảng tính sao cho các hàng tổng hợp nằm dưới các hàng chi tiết và cột tổng hợp nằm bên phải của cột chi tiết. Bạn có thể áp dụng định dạng đậm hay nghiêng cho các hàng tổng hợp để nhấn mạnh, dù điều này không bắt buộc cho tính năng outline.

Chọn các ô trong các hàng, hay cột mà bạn định outline. Chọn đến nhưng không bao gồm ô chứa công thức tổng hợp. Nếu các hàng hay cột chỉ chứa dữ liệu cần outline, bạn có thể chọn cả các hàng hay cột cần nhóm.
Để nhóm các mục theo một cấp, gọi Data, Group và Outline, Group. Cửa sổ Group xuất hiện.
Trong cửa sổ Group, chọn Rows hay Columns, tuỳ theo mục đích của bạn; sau đó nhấn OK.
Lặp lại các bước 1 đến 3 cho mỗi phần bạn định outline.
Chú ý: Nếu bạn chọn cả hàng hay cột trong bước 1, bạn sẽ không thấy cửa sổ Group. Excel nhóm dữ liệu theo hàng nếu bạn chọn các hàng hay theo cột nếu bạn chọn các cột.

TIP: Nếu bạn mắc lỗi hoặc bạn muốn huỷ thao tác nhóm, bạn có thể dùng lệnh Ungroup. Chọn nhóm bạn muốn huỷ nhóm. Sau đó gọi Data, Group and Outline, Ungroup. Chọn Rows hay Columns; sau đó nhấn OK.

ẩn/ hiện các cấp
Bạn sử dụng các ký hiệu ouline để chọn cấp hiển thị. Sau khi bạn tạo outline, các ký tiệu outline tự động xuất hiện trên vùng xám bên trái tiêu đề hàng.

Nếu không thấy ký hiệu outline, nhấn Ctrl+8. Tổ hợp phím này bật qua lại giữa ẩn và hiện các ký hiệu outline.
Hiện hay ẩn các cấp chi tiết cho hàng hay cột như sau:
Mở một hàng hay cột cụ thể bằng cách nhấn lên ký hiệu hiện (+).
Mở toàn bộ một cấp bằng cách nhấn vào nút số của cấp đó. Các nút chỉ số cấp xuất hiện trên đỉnh vùng xám. Để hiện tất cả các cấp, nhấn lên nút số của cấp cao nhất.
Gập một hàng hay cột cụ thể bằng cách nhấn lên ký hiệu ẩn (-).
Gập lại một cấp bằng cách nhấn lên nút số tương ứng của cấp đó. Để gập tất cả các cấp, nhấn lên nút số của cấp thấp nhất.
Outline tự động
Bạn có thể để Excel tự động tạo outline thay vì tự tạo. Tạo outline tự động hữu ích khi trước đó bạn chưa tạo outline và bảng của bạn có cấu trúc nhất quán.

Nếu bạn muốn tạo outline cho một phần của bảng tính, chọn vùng cần outline. Nếu bạn muốn outline cả bảng, chọn một ô duy nhất.
Chọn Data, Group and Outline, Auto Outline.
Nếu Excel xác định được hàng, cột tổng hợp nó sẽ tạo ra outline. Nếu không tạo được nó hiển thị cảnh báo.

Định dạng cho Outlines
Nếu muốn định dạng cho một outline hiện có, hãy chọn các ô cần áp dụng outline. Nếu không hãy bắt đầu với bước 2.
Chọn Data, Group and Outline, Settings. Xuất hiện cửa sổ Settings.
Để định dạng một outline đã có, chọn Apply Styles. Nếu không, chọn Automatic Styles và nhấn OK.
Làm ẩn các ký hiệu Outlines
Các bước

Để ẩn các ký hiệu outline trong một bảng tính, chọn Tools, Options; chọn phiếu View.
Xoá chọn hộp Outline Symbols; nhấn OK.
TIP: Để chuyển qua lại giữa ẩn và hiện các ký tự outline, hãy thêm nút Show Outline Symbols lên một thanh công cụ. Chọn Tools, Customize; nhấn phiếu Commands. Trong mục Categories, chọn Data. Duyệt tìm biểu tượng Show Outline Symbols. Kéo biểu tượng ra vị trí mong muốn trên thanh công cụ; sau đó nhấn nút Close.

Bỏ chế độ Outlines
Các bước

Để bỏ một phần của outline, chọn các ô trong hàng hay cột tại mức mà bạn muốn bỏ ký hiệu; còn nếu muốn bỏ toàn bộ thì đứng ở một ô bất kỳ.
Gọi Data, Group and Outline, Clear Outline.
Consolidate
Chọn ô của vùng đưa kết quả, trên Data menu, gọi Consolidate.
Trong hộp Function, chọn một hàm tổng hợp,
Trong hộp Reference, nhập hay chọn vùng dữ liệu nguồn (cả tiêu đề) sau đo nhấn Add.
Lặp lại các bước 3 cho các vùng dữ liệu khác.
Chọn mục tiêu đề dưới Use labels in (top row, left column). Để tạo liên kết với nguồn chọn Create links to source data.

[tqminh_csp]

Pivot Table
Tạo một Pivot Table
Hiệu chỉnh một Pivot Table
Thay đổi tên trường
Thêm trường dữ liệu
Thêm trường trang, hàng hay cột
Định dạng Pivot Table
Cập nhật Pivot Table

Pivot table là bảng tổng hợp, phân tích và xử lý dữ liệu từ một danh sách hay một bảng. Với Pivot Table ta có thể tổng hợp dữ liệu theo nhóm, sau đó lại có thể vẽ đồ thị từ dữ liệu tổng hợp này.

Tạo một Pivot Table
Để tạo một Pivot Table sử dụng PivotTable Wizard bạn cần cho Excel biết tổng hợp trường nào theo cột và trường nào theo hàng. Bạn cũng có thể chỉ định trường trang để xếp các nhóm theo trang. Bạn có thể đảo lại vị trí của các trường để có một bảng tổng hợp khác.

Bạn có thể tạo một pivot table từ nhiều nguồn dữ liệu khác nhau. Thường nguồn dữ liệu là các bảng của Excel. Bạn cũng có thể tạo Pivot Table từ nguồn dữ liệu ngoại, từ nhiều vùng hợp lại, hay từ chính các Pivot Table khác.

Các bước

Chọn một ô bất kỳ trong danh sách cần tổng hợp; gọi Data> PivotTable Report.
Trong bước 1 của PivotTable Wizard, chọn nguồn dữ liệu ( Microsoft Excel List hay từ các cơ sở dữ liệu khác); sau đó nhấn nút Next.
Trong bước 2 của PivotTable Wizard, vùng địa chỉ của danh sách xuất hiện trong hộp Range. Nếu địa chỉ sai hãy chỉnh lại. Sau đó nhấn Next.
Trong bước 3 của PivotTable Wizard, bạn trình bày bố cục của pivot table. Các trường trở thành các nút nằm bên phải của cửa sổ. Kéo nút ứng với trường dữ liệu vào vùng DATA. Để sắp xếp một trường theo cột, kéo nút của trường đó len vùng COLUMN. Để sắp xếp một trường theo hàng, kéo đến vùng ROW. Nhấn nút Next.
Trong bước 4 của PivotTable Wizard, bạn chỉ định ô để đặt pivot table. Nếu muốn đặt thêm các tuỳ chọn khác cho pivot table, nhấn nút Options, đặt các tuỳ chọn khác, và nhấn OK. sau đó nhấn Finish.
Hiệu chỉnh một Pivot Table
Thay đổi tên trường
Vì pivot tables chỉ là bảng hiển thị thông tin nên bạn không thể thay đổi thông tin trên bảng. Bạn chỉ có thể thay tên của các trường.

Để hiệu chỉnh tên trường, chọn trường
nhập tên mới.
TIP: Để thay đổi các tuỳ chọn khác cho một trường, nhấn kép lên nút của trường đó. Chọn các tuỳ chọn.

Thêm trường dữ liệu
Để thêm trường dữ liệu, chọn một ô trong bảng pivot table.
Nhấn nút PivotTable Wizard trên thanh PivotTable. (Nhấn chuột phải lên vùng thanh công cụ, chọn PivotTable để hiển thị thanh này nếu chưa hiện)
Trong bước 3 của PivotTable Wizard, kéo thêm nút của trường bạn muốn thêm vào vùng DATA; nhấn nút Finish.
Thêm trường Rows, Columns, hay Pages
Để thêm trường hàng, cột hay trang, chọn một ô bất kỳ trong pivot table.
Nhấn nút PivotTable Wizard trên thanh PivotTable.
Trong bước 3 của PivotTable Wizard, kéo các nút cần thiết vào vùng ROW, COLUMN, hay PAGE; sau đó nhấn Finish.
TIP: Để bỏ một trường hàng, cột hay trang, kéo nút trường ra khỏi pivot table.

Định dạng Pivot Table
Bạn có thể sử dụng tính năng AutoFormat để định dạng

Chọn một ô trong bảng pivot table. Chọn Format, AutoFormat.
Trên Table Format , chọn định dạng mong muốn, nhấn OK.
Chú ý: Để định dạng không bị mất khi ta thay đổi hay cập nhật lại bảng PivotTable cần đảm bảo mục Enable Selection trên PivotTable menu của thanh PivotTable được chọn.

Cập nhật Pivot Table
Một pivot table không tự cập nhật khi bạn thay đổi dữ liệu nguồn.

Để cập nhật pivot table, chọn một ô bất kỳ trong pivot table.
Nhấn nút Refresh Data trên thanh PivotTable.

[tqminh_csp]

Đồ thị
Vẽ đồ thị
Vẽ đồ thị dùng Chart Wizard
Vẽ đồ thị từ các vùng không kề nhau
Vẽ đồ thị trong một bước
Thêm bớt dữ liệu và các đối tượng
Thêm dữ liệu bằng sao chép và dán
Thêm dữ liệu bằng kéo thả
Thêm nhãn dữ liệu
Thêm các đường lưới
Thêm chú giải
Thêm tiêu đề đồ thị
Thêm đường xu hướng
Thêm ảnh nền cho bảng tính
Định dạng dữ liệu cho đồ thị
Định dạng các đường
Chọn Font và Style
Định dạng các mục số
Định dạng các đối tượng
Định dạng tiêu đề và nhãn
Thêm hình ảnh cho các đường
Bài tập
Charts để vẽ đồ thị. Đồ thị được gán với dữ liệu, tự cập nhật lại khi dữ liệu thay đổi. Excel cung cấp Chart Wizard hướng dẫn bạn từng bước vẽ đồ thị. Bạn có thể thay đổi kiểu đồ thị, thêm các mục như tiêu đề hay chú giải, thay đổi định dạng các thành phần của đồ thị. Excel cũng cho phép bạn thêm các hình vẽ do bạn tự vẽ dùng công cụ Drawing hay lấy từ các ảnh, thư viện ảnh.

Vẽ đồ thị
Vẽ đồ thị dùng Chart Wizard
Chọn vùng dữ liệu kể cả hàng tiêu đề, nhấn nút Chart Wizard trên thanh công cụ chuẩn.
Chọn kiểu đồ thị từ Chart Type; sau đó nhấn nút Next.
Kiểm tra địa chỉ vùng dữ liệu và chọn vẽ đồ thị theo hàng (Rows) hay cột (Columns); nhấn Next.
Chọn phiếu tương ứng, sau đó chọn các mục như tiêu đề, nhãn dữ liệu, nhấn Next.
Chọn vẽ đồ thị trên một trang riêng hay nhúng lên bảng tính hiện hành. Nhấn Finish.
TIP: Sau khi vẽ có thể kéo và thay đổi kích thước đồ thị như mọi đối tượng hình vẽ khác

Vẽ đồ thị từ các vùng không kề nhau
Chọn vùng thứ nhất.
Nhấn giữ CTRL, chọn các vùng khác.
Các vùng không liền nhau phải tạo nên một hình chữ nhật.
Nhấn nút Chart Wizard Theo các chỉ dẫn của Chart Wizard
Vẽ một đồ thị ngầm định chỉ trong một bước
Đồ thị dạng ngầm định của Microsoft Excel là đồ thị cột, trừ khi chúng ta thay đổi chúng.

Để tạo đồ thị sử dụng dạng ngầm định, chọn dữ liệu và nhấn F11.
Để tạo một đồ thị nhúng, chọn dữ liệu, nhấn nút Default Chart. Nếu không thấy nút này hãy bổ xung thêm vào thanh công cụ (view>toolbars>customize> command, Categories>Charting).
Thêm bớt dữ liệu và các đối tượng
Thêm dữ liệu vào đồ thị nhờ sao chép và dán
Có thể thêm một đường, thêm điểm hay thay đổi dữ liệu gốc của một đường.
Để thêm dữ liệu bạn có thể chọn đồ thị rồi sử dụng lệnh Chart, Add.
Để sửa địa chỉ vùng dữ liệu của một đường, sử dụng lệnh Chart, Source Data

Chọn các ô có dữ liệu cần thêm vào đồ thị, cả tiêu đề. Nhấn Copy.
Chọn đồ thị.
Để Microsoft Excel tự đưa dữ liệu vào đồ thị, nhấn Paste.
Để chỉ định cách vẽ đồ thị, chọn Paste Special trên Edit menu, chọn các tuỳ chọn.

Thêm dữ liệu bằng kéo thả
Chọn vùng dữ liệu cần đưa thêm vào đồ thị. Các ô phải nằm cạnh nhau.
Đưa con trỏ chuột tới đường viền của vùng chọn, con trỏ có dạng mũi tên.
Kéo vùng chọn thả vào đồ thị.
Nếu Microsoft Excel cần thêm các thông tin khác để vẽ, hộp Paste Special sẽ xuất hiện.

Chú ý: Nếu đồ thị nằm trên một trang riêng thì thêm vùng dữ liệu sử dụng lệnh Chart, Add Data. Nhập vùng dữ liệu mới sau đó nhấn OK.
Nếu không thể kéo thả vùng chọn, kiểm tra xem hộp Allow cell drag and drop có được chọn không. Nhấn Options trên Tools menu, chọn phiếu Edit.

Thêm nhãn dữ liệu (Data Labels)
Chọn đồ thị
Chọn Chart> Chart Options; sau đó nhấn phiếu Data Labels
Chọn các mục Data Labels bạn cần, như Show Value, Show Percent, hay Show Label (Tuỳ theo kiểu đồ thị mà một số mục chọn có thể mờ đi). Nhấn OK.
Để bỏ các nhãn dữ liệu ra khỏi đồ thị, hiện thị lại phiếu Data Labels của cửa sổ Chart Options, chọn mục None và nhấn OK.
Để thêm nhãn chỉ cho một đường đồ thị nhấn chuột phải lên đường đó, chọn Format Data Series, sau đó chọn phiếu Data Labels
Để thêm nhãn chỉ cho một điểm, nhấn chuột hai lần lên điểm đó để chọn nó, sau đó nhấn chuột phải, chọn chọn Format Data Point sau đó chọn phiếu Data Labels
Chú ý: Nhãn dữ liệu số có định dạng như các ô liên quan. Để định dạng lại bạn có thể định dạng các ô liên quan hoặc định dạng trực tiếp trên đồ thị bằng cách chọn nhãn cần định dạng và chọn Format, Selected Labels; sau đó chọn phiếu Number và thay đổi định dạng.

Thêm các đường lưới (Chart Gridlines)
Các đường lưới giúp đọc và so sánh các điểm dữ liệu. Nếu bạn sử dụng Chart Wizard để vẽ đồ thị, Excel cho phép bạn thêm các đường lưới.

Chọn đồ thị. Lên mục Chart, Chart Options; chọn phiếu Gridlines.
Chọn kiểu đường lưới; sau đó nhấn OK.
Để bỏ đường lưới ra khỏi đồ thị, hiển thị phiếu Gridlines của cửa sổ Chart Options và xoá chọn hộp lưới cần bỏ.

Thêm chú giải cho đồ thị (Chart Legends)
Chú giải cho biết đường nào thể hiện mục dữ liệu nào trên đồ thị. Nếu bạn sử dụng Chart Wizard để vẽ đồ thị. Excel tự tạo chú giải dựa trên các nhãn dữ liệu chiều ngắn hơn của bảng dữ liệu.

Chọn đồ thị
Chọn Chart, Chart Options; nhấn phiếu Legend trong cửa sổ Chart Options.
Chọn Show Legend. Sau đó chọn vị trí : Bottom, Corner, Top, Right, hay Left. Nhấn OK.
Có thể kéo, thay đổi kích thước hộp chú giải đến vị trí mong muốn. Để xoá hộp chú giải, chọn nó rồi nhấn nút Delete.

TIP: Để định dạng hộp chú giải, nhấn chuột phải lên nó sau đó chọn Format Legend. Thực hiện các sửa đổi cần thiết và nhấn OK.

Thêm tiêu đề cho đồ thị (Chart Titles)
Có thể thêm tiêu đề khi vẽ đồ thị dùng Chart Wizard, hoặc bổ sung tiêu đề sau.

Chọn đồ thị, sau đó chọn Chart, Chart Options; phiếu Titles trong cửa sổ Chart Options.
Chọn mục tiêu đề cần thêm (như Chart Title) và nhập tiêu đề; sau đó nhấn OK.
Bạn có thể kéo tiêu đề đến vị trí khác hay xoá nó.

TIP: Để định dạng tiêu đề, nhấn chuột phải lên nó rồi chọn Format Title.

Thêm đường xu hướng (Chart Trendlines)
Bạn có thể thêm đường xu hướng vào đồ thị để chỉ ra xu hướng dữ liệu và dự báo. Phương pháp phân tích hồi quy (một kỹ thuật để diễn tả quan hệ giữa các đại lượng) được sử dụng để tạo nên đường xu hướng từ dữ liệu hiện có. Bạn có thể chọn một trong 5 đường hồi quy hoặc chọn đường bình quân dịch chuyển.

Chọn đồ thị, chọn Chart, Add Trendline; sau đó chọn phiếu Type trong cửa sổ Add Trendline.
Chọn đường đồ thị bạn muốn vẽ đường xu hướng cho nó trong danh sách Based On Series.
Chọn một trong sáu kiểu đường xu hướng/hồi quy: tuyến tính (Linear), lôga (Logarithmic), đa thức (Polynomial), luỹ thừa (Power), mũ (Exponential), và bình quân dịch chuyển (Moving Average). Để có thêm thông tin về các loại đường này, nhấn nút hỏi chấm trên thanh tiêu đề của cửa sổ đang mở.
Chọn phiếu Options nếu bạn muốn có thêm các mục chọn khác như tên của đường xu hướng, phương trình cho đường xu hướng. Nhân OK.
Thêm ảnh nền cho bảng tính
ảnh nền, ví dụ biểu tượng công ty làm tăng ấn tượng cho các biểu mẫu và báo cáo.

Mở bảng tính cần thêm ảnh nền; chọn Format, Sheet, Background. Cửa sổ Sheet Background xuất hiện.
Chọn ảnh cần sử dụng (ví dụ từ thư mục \MSOffice\Clipart) . Sau đó nhấn Open.
Để xoá ảnh nền, chọn Format, Sheet, Delete Background.

TIP: Để thấy rõ hơn ảnh nền, bạn có thể bỏ các đường lưới. Chọn Tools, Options; nhấn phiếu View. Bỏ chọn hộp Gridlines và nhấn OK.

Định dạng dữ liệu
Định dạng các đường đồ thị
Bạn có thể tăng ấn tượng bởi thêm các thanh sai số hay các nhãn cho một chuỗi dữ liệu. Bạn cũng có thể thay đổi khe giữa các cột trong đồ thị cột.

Nhấn chuột phải lên đường đồ thị cần định dạng, chọn Format Data Series.
Trong cửa sổ Format Data Series, chọn phiếu chứa các mục (như Error Bars, Data Labels, Series Order, hay Options).
Hiệu chỉnh các mục; sau đó nhấn OK.
Chọn Fonts và Styles
Bạn có thể thay đổi fonts và styles trong đồ thị giống như trong bảng tính.

Chọn đối tượng văn bản bạn cần định dạng. Nếu chỉ định dạng một số ký tự thì hãy chọn chúng.
Nhấn lên nút mong muốn trên thanh công cụ định dạng. Ví dụ co chữ, hay in nghiêng.
TIP: Để có thêm các tuỳ chọn định dạng khác, nhấn chuột phải lên mục văn bản cần định dạng, chọn Format, sau đo phiếu Font, chọn các định dạng mong muốn sau đó nhấn OK.

Định dạng số trên đồ thị
Định dạng các mục trên đồ thị giống như trên bảng tính. Ví dụ thêm ký hiệu tiền tệ hay thay đổi các định dạng.

Nhấn chuột phải lên các mục số cần định dạng, chọn Format.
Trong cửa sổ Format, chọn phiếu Number sau đó chọn định dạng từ danh sách Category.
Cửa sổ Format thay đổi tương ứng với mục chọn. Thực hiện đinh dạng; sau đó nhấn OK.
TIP: Để định dạng số như trong bảng tính, chọn mục Linked to Source trong cửa sổ Format.

Định dạng các đối tượng
Excel cung cấp nhiều lệnh và mục chọn để bạn định dạng các đối tượng trên đồ thị. Các đối tượng của đồ thị gồm tiêu để (titles), chú giải (legends), trục (axes), đường đồ thị (data series), ... Bạn có thể tô mầu, hoa văn, và viền cho các đối tượng trong một đồ thị.

Các bước

Nhấn chuột phải lên đối tượng của đồ thị, chọn Format.
Trong cửa sổ Format, chọn phiếu Patterns.
Trong nhóm Border, chọn kiểu viền. Trong nhóm Area chon mầu mong muốn.
Để tạo hiệu ứng tô, chọn Fill Effects. Chọn một trong các phiếu và chọn mẫu; sau đó nhấn OK hai lần để quay lại bảng tính.
TIP: Nếu bạn muốn thay đổi mầu hay hoa văn của nền đồ thị, nhấn chuột phải lên một góc bất kỳ của đồ thị và chọn Format Chart Area. Chọn lựa các định dạng, sau đó nhấn OK.

Định dạng tiêu đề và nhãn cho đồ thị
Đối với một số đối tượng văn bản trong đồ thị, như tiêu đề, bạn có thể dóng chúng nằm ngang hay thẳng đứng hay xoay. Với một số đối tượng văn bản khác như các nhãn trên trục, bạn chỉ có thể thay đổi định hướng.

Các bước

Nhấn chuột phải lên đối tượng văn bản, chọn Format.
Trong cửa sổ Format, chọn phiếu Alignment; chọn các mục từ danh sách Horizontal và Vertical.
Kéo kim chỉ hướng trong hộp Orientation hoặc nhập một góc giứa -90 và 90 trong hộp Degrees để thay đổi hướng văn bản.
TIP: Để xếp văn bản từ trên xuống, chọn hộp xếp các chữ trên nhau bên trái đồng hồ định hướng

Thêm hình ảnh cho các đường
Excel có thể sử dụng hình ảnh làm điểm mốc (markers) dữ liệu cho các cột, thanh, đường hay nền.

Trong đồ thị, nhấn chuột phải lên đường bạn muốn đưa ảnh lên đó, chọn Format Data Series. Bạn cũng có thể chọn vùng vẽ hay vùng nền đồ thị nếu bạn muốn đưa ảnh vào đó.
Trong cửa sổ Format Data Series, chọn phiếu Patterns; chọn Fill Effects và phiếu Picture.
Chọn Select Picture. trong Look In, tìm đến thư mục có chứa ảnh, chọn ảnh và nhấn OK.
Chọn các mục chọn khác trong phiếu Picture. Thử với các tuỳ chọn khác nhau (Stretch, Stack, hay Stack và Scale) để chọn hiệu ứng bạn cần. Đối với đồ thị 3-D, chọn mục bạn cần từ Apply To group. Nhấn OK hai lần để quay lại bảng tính.

[tqminh_csp]

Goal seek & solver
Goal Seek
Solver
Bài tập
Goal seek để giải các phương trình một ẩn, Solver để giải hệ phương trình và các bài toán tối ưu

Sử dụng Goal seek
Có số liệu của dự án đầu tư khai thác

A
B
C
D
E

1
Năm
Vốn đầu tư ban đầu
Đầu tư bổ sung
Doanh thu dự tính

2
1
500
0
0

3
2
30
50

4
3
30
100

5
4
30
120

6
5
30
120

7
6
30
120

8
7
30
120

9
8
30
120

10
9
30
120

11
10
30
120


Sử dụng Goal Seek để tính lãi suất vay vốn sao cho có thể hoàn vốn trong 10 năm

Trước hết nhập mô hình bài toán, khai báo biến cần tìm, trong ví dụ này là lãi xuất vay vốn tại ô B15
Lên Tool, gọi Goal Seek, xuất hiện hộp hội thoại Goal Seek, điền các thông số:
Set cell: ô chứa hàm mục tiêu
To value: hàm mục tiêu bằng
By changing cell: ô chứa biến

Nhấn OK

Sử dụng solver
Giải bài toán tối ưu

Bước 1: Nhập mô hình bài toán lên bảng tính gồm 3 phần: hàm mục tiêu, biến và các điều kiện ràng buộc

Bước 2 Gọi Tool, Solver, thực hiện các khai báo:

Ô chứa hàm mục tiêu (Set target cell)
Vùng các ô chứa biến (By Changing Cells)
Vùng điều kiện ràng buộc (Subject to the Constraints)
Ymin = 7


Giải bài toán tối ưu

Một nhà máy được đặt sản xuất 2 loại sản phầm có giá bán là P1=14 và P2=26. Tìm số lượng sản phẩm x1 và x2 sao cho doanh thu thu được lớn nhất, biết rằng có 4 tổ sản xuất M1, M2, M3, M4 để sản xuất hai sản phẩm này và quỹ thời gian của 4 tổ này trong năm như sau:

M1: 1800 công, M2: 1600 công, M3: 1000 công, M4: 23140 công

trong đó giờ công để hoàn thành 1 sản phẩm của các tổ theo như sau:

Tổ
Sản phẩm 1
Sản Phẩm 2

M1
A11 = 2.03
A21= 1.23

M2
A12 = 0.14
A22= 0.92

M3
A13 = 0.91
A23= 1.81

M4
A14 = 1.32
A24= 0.18


Nhu cầu tối thiểu đối với hai mặt hàng trên là Bu1=500, Bu2=200; Lượng bán được tối đa theo khảo sát thị trường là: Bo1 = 590, Bo2 = 780.

Tóm tắt đầu bài:

tim max(DoanhThu)

DoanhThu=(P1 * x1) + (P2 * x2)

Điều kiện ràng buộc:

A11 * x1 + A21 * x2 <= 1800
A12 * x1 + A22 * x2 <= 1600
A13 * x1 + A23 * x2 <= 1000
A14 * x1 + A24 * x2 <= 23140
x1 >= Bu1, x1 <= Bo1, x2 >= Bu2, x2 <= Bo2
Kết quả chạy Solver

Doanh Thu = 14912.265

x1 = 589.99999

x2 = 255.85636


Cũng bài toán trên, tìm phương án để chi phí cho sản xuất là tối thiểu cho hai sản phẩm. Giá thành để sản xuất 1 đơn vị sản phầm 1 là K1= 10, sản phẩm 2 là K2 = 16

Tóm tắt đầu bài:

tim min(ChiPhi)

ChiPhi=(K1 * x1) + (K2 * x2)

Điều kiện ràng buộc:

A11 * x1 + A21 * x2 <= 1800
A12 * x1 + A22 * x2 <= 1600
A13 * x1 + A23 * x2 <= 1000
A14 * x1 + A24 * x2 <= 23140
x1 >= Bu1, x1 <= Bo1, x2 >= Bu2, x2 <= Bo2
Kết quả chạy Solver

Chi Phí = 8200.0000

x1 = 500

x2 = 200


Tối ưu thời gian

Có bốn tổ làm việc và thời gian hoàn thành các công việc của từng tổ như sau

Tổ
Thời gian hoàn thành công việc (h)

Tiện
Sơn
Hàn
Gò

Tổ 1
10
33
41
30

Tổ 2
24
17
50
60

Tổ 3
39
32
62
39

Tổ 4
22
27
39
37


lập phương án lao động sao cho tổng thời gian hoàn thành các công việc là nhỏ nhất. Phân công theo nguyên tắc mỗi việc chỉ do một tổ đảm nhận.

[tqminh_csp]

- Định dạng bảng tính -
Copy định dạng Chọn ô /vùng có định dạng cần sao chép.
Nhấn nút Format Painter .
Chọn ô/ vùng cần áp dụng định dạng.
Để sao chép định dạng nhiều lần, nhấn kép lên Format Painter. Nhấn lần nữa để kết thúc.

Font và co chữ Chọn các ô hay các chữ trong một ô cần định dạng.
Trong hộp Font, chọn font mong muốn. Trong hộp Font Size , chọn kích thước.

Co Font cho vừa với ô Chọn các ô cần định dạng.
Trên Format menu, nhấn Cells, chọn phiếu Alignment.
Đánh dấu chọn hộp Shrink to fit.
Để viết nhiều dòng trong một ô, đánh dấu hộp Wrap text trên phiếu Alignment.

Font và co chữ ngầm định trong sổ bảng tính mới Trên Tools menu, nhấn Options, chọn phiếu General.
Trong hộp Standard font, chọn một font.
Trong hộp Size, nhập một kích thước font.
Bạn phải khởi động lại Microsoft Excel để kiểu font và kích thước ngầm định có hiệu lực. Thiết lập này không có hiệu lực với các sổ đã tạo từ trước.

Thay đổi mầu văn bản Chọn toàn bộ các ô hoặc chọn các ký tự trong một ô
Để tô mầu vừa dùng nhấn nút Font Color. Để chọn mầu khác, nhấn mũi tên bên cạnh nút Font Color, chọn một mầu từ bảng mầu.

Tô đậm, nghiêng hay gạch chân Chọn toàn bộ các ô hoặc chọn các ký tự trong một ô
Trên thanh Formatting, nhấn một nút định dạng mong muốn

Định dạng có điều kiện Chọn các ô cần áp dụng định dạng có điều kiện.
Trên Format menu, gọi Conditional Formatting.
Để lấy giá trị trong các ô đang chọn làm điều kiện định dạng, chọn Cell Value Is, chọn cụm từ so sánh và sau đó nhập một hằng hay một biểu thức cho vế phải; trước biểu thức phải có dấu bằng (=).Để sử dụng ô khác trong điều kiện, sử dụng một biểu thức làm điều kiện định dạng. Chọn mục Formula Is bên trái, nhập biểu thức ở hộp bên phải. biểu thức phải trả lại giá trị logíc TRUE hay FALSE.
Nhấn Format.
Chọn kiểu font, mầu font, gạch chân, viền, tô mầu ô, viền ô, bóng hay các mẫu hoa văn khác.Microsoft Excel chỉ áp dụng định dạng nếu các ô đáp ứng điều kiện định dạng.
Để thêm các điều kiện định dạng khác, nhấn Add, lặp lại các bước 3-5.


Nếu chỉ muốn sao chép định dạng có điều kiện, chọn các ô cần định dạng và ít nhất một ô trong các ô đang chọn cũng có định dạng có điều kiện cần sao chép. Nhấn Conditional Formatting trên Format menu, sau đó nhấn OK.
Sửa đổi, thêm, hay xoá các định dạng có điều kiện Chọn ô cần thay đổi định dạng có điều kiện.
Trên Format menu, nhấn Conditional Formatting.
Thay đổi toán tử, giá trị, công thức, hay định dạng.
Để thay định dạng, nhấn Format của mục điều kiện cần thay đổi,
Để xoá định dạng, nhấn Clear trong hộp hội thoại Format Cells.
Để thêm điều kiện định dạng, nhấn Add.
Để xoá một hay nhiều điều kiện, nhấn Delete, và đánh dấu vào hộp các điều kiện cần xoá.


Để xoá toàn bộ các định dạng có điều kiện cũng như các định dạng khác cho ô trở tới Clear trên Edit menu, nhấn Formats.
Tìm các ô có định dạng có điều kiện Để tìm các ô định dạng điều kiện nào đó, chọn một ô có định dạng muốn tìm.
Để tìm mọi ô định dạng có điều kiện, chọn ô bất kỳ
Trên Edit menu, nhấn Go To.
Chọn Special.
Chọn Conditional formats.
Để tìm các ô có cùng định dạng có điều kiện, nhấn chọn Same dưới mục Data validation.
Để tìm mọi ô có định dạng có điều kiện, nhấn chọn All.

Định dạng bảng PivotTable Để giữ lại các định dạng khi cập nhật hay thay đổi bố cục bảng PivotTable, phải trỏ tới mục Select trên PivotTable menu của thanh PivotTable toolbar, và sau đó đảm bảo Enable Selection được nhấn trước khi bạn chọn các dữ liệu cần định dạng.
Chọn phần bảng PivotTable cần định dạng.
Để áp dụng các định dạng, sử dụng các nút trên thanh định dạng hay lệnh trên Format menu.
Để thiết lập các tuỳ chọn cho bảng PivotTable, nháy chọn một ô trong bảng PivotTable, nhấn mục Options trên PivotTable menu của thanh công cụ PivotTable, chọn một tuỳ chọn

Chú ý
Có thể thiết lập các tuỳ chọn khi tạo bảng PivotTable. Trong PivotTable Wizard - bước 4/ 4, nhấn Options.
Để định dạng PivotTable tự động, đảm bảo mục AutoFormat dưới mục Format options được chọn. Mọi định dạng bằng tay trên PivotTable có quyền ưu tiên cao hơn định dạng tự động.
Để loại bỏ mọi định dạng trên PivotTable, xoá hộp AutoFormat table, chọn toàn bộ PivotTable,sau đó áp dụng Normal style.
Để bỏ định dạng mỗi khi cập nhật PivotTable, xoá chọn hộp Preserve formatting.
Để áp dụng định dạng cho tất cả các trang của một PivotTable với các trường trang, chọn All cho mỗi trường trang sau đó áp dụng định dạng. Nếu PivotTable có các trường trang được truy vấn từ dữ liệu ngoại khi bạn chọn từng mục, hiển thị từng trường trang riêng biệt sau đó áp dụng định dạng.
Không thể áp dụng định dạng có điều kiện hay thiết lập kiểm soát dữ liệu (data validation) cho các ô trong một bảng PivotTable.
Microsoft Excel không giữ lại các định dạng viền ô khi chúng ta thay đổi bố cục hay cập nhật dữ liệu của PivotTable.
Để dùng các ô trộn làm nhãn hàng, cột trong PivotTable, nhấn Options trên PivotTable menu của thanh PivotTable, chọn hộp Merge labels.
Trước khi định dạng PivotTable của Microsoft Excel cũ, hãy lưu sổ dưới tệp Microsoft Excel 97 file. Trên File menu, nhấn Save As, chọn Microsoft Excel Workbook (*.xls) trong hộp Save as type.

Kẻ viền Chọn các ô cần kẻ viền.
Để sử dụng kiểu viền gần đây, nhấn nút Borders trên thanh định dạng.
Để áp dụng dạng viền khác, nhấn mũi tên bên cạnh Borders, chọn dạng viền từ bảng

Tip
Để thêm các dạng viền khác, nhấn Cells trên Format menu, chọn phiếu Border. Nhấn chọn một dạng đuờng viền, sau đó chọn vị trí thể hiện nơi kẻ viền.
Để kẻ viền cho các ô đang chọn có chứa văn bản xoay, nhấn Cells trên Format menu, chọn phiếu Border, và sau đó sử dụng nút Outline và Inside dưới mục Presets. Các viền sẽ áp dụng cho các cạnh ô, nó sẽ xoay cùng chiều xoay của văn bản.
Để thay đổi dạng đường viền, chọn các ô. Trên phiếu Border (Cells, Format menu), nhấn chọn kiểu đường mới trong hộp Style, sau đó chọn viền cần thay đổi trong sơ đồ ô dưới mục Border.

Tô mầu ô Chọn các ô cần tô mầu.
Để tô mầu vừa dùng gần đây, nhấn nút Fill Color.
Để tô mầu khác, nhấn nút mũi tên bên cạnh nút Fill Color, chọn một mầu.

Tô ô với mẫu hoa văn Chọn các ô cần tô.
Trên Format menu, nhấn Cells, chọn phiếu Patterns.
Để kèm thêm mầu nền vào mẫu hoa văn, chọn một mầu trong hộp Cell shading.
Nhấn mũi tên cạnh hộp Pattern, chọn một mẫu hoa văn.

Bỏ đường viền Chọn các ô cần xoá viền.
Nhấn mũi tên bên cạnh Borders , chọn nút không viền

Bỏ tô mầu Chọn các ô cần xoá tô mầu
Nhấn mũi tên bên cạnh Fill Color , chọn No Fill.

Thêm hoa văn nền cho cả bảng tính Nhấn chọn bảng tính cần thêm hoa văn nền.
Trên Format menu, trỏ tới Sheet, chọn Background.
Chọn một ảnh đồ hoạ làm mẫu nền.

Loại bỏ hoa văn nền Nhấn chọn bảng có nền cần xoá.
Trên Format menu, trỏ tới Sheet, nhấn Background.
Chọn None.

Để viết nhiều hàng trong một ô Chọn các ô cần ngắt dòng.
Trên Format menu, nhấn Cells, chọn phiếu Alignment.
Dưới hộp Text control, đánh dấu hộp Wrap text.

Tip
Để bắt văn bản xuống dòng tại một điểm nhất định, chọn ô, quay lại chế độ Edit, đặt con trỏ tại điểm ngắt và nhấn ALT+ENTER.
Bề rộng cột Kéo trên đường ranh giới bên phải của tiêu đề cột cho đến khi có được bề rộng mong muốn.
Tip
Để thay đổi cùng lúc nhiều cột, chọn chúng trước khi kéo.
Để chỉnh cho vừa với ô rộng nhất, nháy kép thay vì kéo trên đường ranh giới.

Chiều cao hàng Kéo trên đường ranh giới ngay dưới hàng trên tiêu đề hàng cho tới khi có chiều cao mongmuốn.
Tip
Để thay đổi chiều cao nhiều hàng, chọn chúng trươc khi kéo.
Để hàng tự vừa với ô cao nhất, nháy kép lên ranh giới thay vì kéo.

Bề rộng cột ngầm định Để ấn định bề rộng cột ngầm định cho tất cả các bảng trong sổ, chọn tất cả các bảng
Trên Format menu, trỏ tới Column, chọn Standard Width.
Nhập kích thước mới.

Tip
Để ấn định bè rộng cột ngầm định cho các sổ mới, tạo một mẫu sổ, tham khảo phần toạ các mẫu.
Trộn các ô Chọn các ô cần trộn
Để trộn các ô trong một hàng và căn vào giữa, nhấn nút Merge and Center .
Để trộn với nhiều tuỳ chọn hơn, chọn các ô, nhấn Cells trên Format menu, chọn phiếu Alignment, đánh dấu hộp Merge cells.

Tip
Có thể căn phải, trái hay giữa văn bản trong ô trộn sử dụng các nút Align Left, Center, hay Align Right. Để căn theo chiều thẳng đúng, sử dụng mục options trên phiếu Alignment(Cells, Format menu).
Tách các ô trộn Chọn ô trộn.
Trên Format menu, nhấn Cells, chọn phiếu Alignment.
Xoá hộp Merge cells.

Dạng số, ngày và giờ Chọn các ô cần định dạng.
Nhấn các nút định dạng mong muốn.

Comma Style Dấu phảy ngăn cách hàng ngìn
Currency Style Ký hiệu tiền tệ
Percent Style Số dưới dạng phần trăm
Decrease Decimal Giảm một chữ số thập phân
Increase Decimal Tăng một chữ số thập phân
Để chọn các định dạng khác, nhấn Cells trên Format menu, chọn phiếu Number.
Định dạng tự tạo Chọn các ô cần định dạng
Trên Format menu, nhấn Cells, chọn phiếu Number.
Trong hộp Category, chọn một nhóm, chọn một định dạng có sẵn gần giống với định dạng tự tạo.
Trong hộp Category, chọn Custom.
Trong hộp Type box, hiệu chỉnh lại định dạng để tạo định dạng riêng. Việc sửa một định dạng có sẵn không làm mất định dạng đó.

Mã định dạng Nếu một định dạng có sẵn không thể hiển thị dữ liệu theo cách bạn mong muốn, bạn có thể tự tạo định dạng sử dụng Custom category trên phiếu Number (Cells, Format menu). Định dạng riêng sử dụng các mã định dạng cách thể hiện số, ngày hay giờ. Có thể chỉ định tối đa bốn nhóm mã định dạng. Các nhóm ngăn cách nhau bởi dấu chấm phẩy gồm số dương, số âm, số không, và văn bản theo đúng trật tự này. Nếu chỉ sử dụng hai nhóm, nhóm đầu cho số dương và không, nhóm thứ hai cho số âm. Nếu chỉ chỉ định một nhóm thì tất cả dữ liệu số sử dụng định dạng này. Nếu bở qua một nhóm hay để dấu chấm phảy của nhóm đó.
Mã định dạng cơ bản Để định dạng phân số hay số thập phân,đưa các ký tự giữ chỗ sau vào nhóm. Nếu số có nhiều chữ số thập phân hơn số ký tự giữ chỗ, số sẽ bị làm tròn. Nếu có nhiều chữ số phần nguyên hơn số ký tự giữ chỗ, các số này sẽ được hiển thị. Nếu trong định dạng chỉ có ký tự giữ chỗ cho số (#) bên trái của dấu chấm, các số nhỏ hơn 1 bắt đầu với dấu chấm thập phân.
# hiển thị chỉ các chữ số có nghĩa, bỏ các số không vô nghĩa.
0 (zero) hiển thị cả số 0 vô nghĩa nếu một số có ít chữ số hơn các ký tự 0 trong định dạng.
? thêm các khoảng trắng cho các số không vô nghĩa cả hai phía của dấu chấm thập phân. Có thể dùng ký tự định dạng này cho phân số có số chữ số thay đổ

Để hiển thị
1234.59 thành 1234.6
8.9 thành 8.900
.631 thành 0.6
12 thành 12.0 và 1234.568 thành 1234.57
44.398, 102.65, và 2.8 thập phân thẳng hàng
5.25 thành 5 1/4 và 5.3 thành 5 3/10
12000 thành 12,000
12000 thành 12
12200000 thành 12.2 mã định dạng
####.#
#.000
0.#
#.0#
???.???
# ???/???
#,###
#,
0.0,,


Để tô mầu nhập mầu trong ngoặc vuông. Mã mầu phải là mục đầu tiên trong nhóm
[BLACK] Đen; [CYAN] Xanh nhạt; [MAGENTA] Đỏ tươi hơi tía; [WHITE] Trắng
[BLUE] Xanh nước biển; [GREEN] Xanh lá cây; [RED] Đỏ; [YELLOW] Vàng
Để định dạng một số nếu số đó thoả mãn điều kiện chỉ định, nhập điều kiện trong ngoặc vuông; điều kiện chỉ gồm toán tử so sánh và một giá trị so sánh. Ví dụ định dạng sau hiển thị một mã ZIP năm chữ số với số 0 dẫn đầu, và mã ZIP với 9 chữ số có gạch ngang phân cách 4 số cuối. [<=99999]00000;00000-0000
Để áp dụng định dạng như mầu ô phụ thộc giá trị trong ô, sử dụng lện Conditional Formatting trên Format menu.
Định dạng ngày tháng và thời gian Để hiện thị ngày, tháng và năm thêm các mã định dạng dưới đây vào nhóm. Nếu dùng "m" ngay sau mã "h" hay "hh" hoặc ngay trước mã "ss", Microsoft Excel hiển thị phút thay vì tháng.
Tháng dưới dạng 1-12
Tháng dưới dạng 01-12
Tháng dưới dạng Jan-Dec
Tháng dưới dạng January-December
Tháng theo chứ cái đầu của tháng
Ngày dưới dạng 1-31
Ngày dưới dạng 01-31
Ngày dưới dạng Sun-Sat
Ngày dưới dạng Sunday-Saturday
Năm dưới dạng 00-99
Năm dưới dạng 1900-9999
Giờ dưới dạng 0-23
Giờ dưới dạng 00-23
Phút dưới dạng 0-59
Phút dưới dạng 00-59
Giây dưới dạng 0-59
Giây dưới dạng 00-59
Giờ dưới dạng 4 AM
Thời gian dưới dạng 4:36 pm
Thời gian dưới dạng 4:36:03 p
Thời gian trôi qua theo giờ; ví dụ, 25.02
Thời gian trôi qua theo phút; ví dụ, 63:46
Thời gian trôi qua theo giây
Giây theo dạng phân số m
mm
mmm
mmmm
mmmmm
d
dd
ddd
dddd
yy
yyyy
h
hh
m
mm
s
ss
h AM/PM
h:mm am/pm
h:mm:ss a/p
[h]:mm
[mm]:ss
[ss]
h:mm:ss.00
Nếu trong định dạng có chứa AM hay PM, giờ sẽ tính theo 12-giờ, trong đó "AM," "am," "A," hay "a" chỉ ra thời gian từ nửa đêm đến trưa hôm sau, và "PM," "pm," "P," hay "p" chỉ thời gian từ trưa đến nửa đêm. Nếu bỏ qua, giờ sẽ tính theo 24 giờ. Ký tự "m" hay "mm" phải đứng ngay sau "h" hay "hh" hoặc ngay trước "ss", nếu không Microsoft Excel hiển thị tháng thay vì phút.
Định dạng tiền tệ, phần trăm hay số mũ Để hiển thị số dưới dạng phần trăm, đưa ký tự (%) vào định dạng. Ví dụ .08 thành 8%; 2.8 thành 280%.
Để chỉ định ký tự tiền tệ sau trong định dạng tiền tệ, bật NUM LOCK, nhấn giữ ALT và nhập mã ANSI cho tiền tệ trên phần bàn phím số.
Để nhập Nhấn giữ ALT và gõ
 0162 Ê 0163 Ơ 0165

Chú ý
Các định dạng người dùng được lưu cùng với sổ bảng tính. Để Microsoft Excel luôn sử dụng một loại tiền tệ chỉ định, thay đổi ký hiệu tiền tệ trong mục Regional Settings của Control Panel trước khi chạy Excel.
Để hiển thị các số dưới dạng số khoa học (số mũ), sử dụng, sử dụng "E-," "E+," "e-," hay "e+" trong một nhóm mã. Nếu trong định dạng có 0 hay # bên phải của mũ, Microsoft Excel hiển thị số dưới dạng khoa học và chèn thêm "E" hay "e". Số chứ số 0 hay # bên phải quyết định số chữ số trong mũ. E- hay e- thay cho mũ âm. E+ hay e+ thay thế mũ dương.
Định dạng văn bản và khoảng cách Để hiển thị văn bản kèm theo số nhập trong ô, đặt chúng trong nháy kép, hay đặt dấu gạch ngược (\) đằng trước. Ví dụ định dạng $0.00 "Thừa";$-0.00 "Thiếu" sẽ hiển thị số âm thành "$-125.74 Thiếu.". Ký tự trắng và các ký tự sau không cần đặt trong dấu nháy kép: $ - + / ( ) :
Nếu nhập một trong các ký tự sau thì một dấu gạch chéo ngược sẽ tự động được chèn: ! ^ & ` (ký tự nháy đơn trái) ' (ký tự nháy đơn phải) ~ { } = < >
Nhóm định dạng văn bản, nếu có, luôn là nhóm cuối cùng trong định dạng số. Bao gồm ký tự @ trong nhóm văn bản nếu bạn muốn hiển thị mọi văn bản nhập trong ô. Nếu bỏ qua ký tự @ ra khỏi nhóm, văn bản nhập vào sẽ không xuất hiện. Nếu muốn thêm đoạn văn bản bổ xung để nó luôn xuất hiện cùng với văn bản nhập vào, đặt nó trong dấu nháy kép(" "); ví dụ, "gross receipts for" @.
Nếu mã định dạng không có nhóm định dạng văn bản, thì văn bản nhập vào ô không bị ảnh hưởng bởi định dạng.
Để tạo một khoảng trắng bằng chiều rộng của ký tự trong mã định dạng, thêm dấu gạch dưới trước các ký tự . Ví dụ để số âm đặt trong ngoặc tròn dóng thẳng hàng với số dương, thêm gạch dưới và dấu đóng ngặc tròn ( _) ),
Để lặp lại ký tự tronh định dạng, điền đầy cột, đưa ký tự hoa thị (*) vào định dạng. Ví dụ, nhập, 0*- để điền đầy các dấu gạch đầu dòng sau số nhập vào ô.

Xoá một định dạng người dùng Trên Format menu, nhấn Cells, chọn phiếu Number.
Trong hộp Category, nhấn Custom.
Trong hộp Type, chọn định dạng cần xoá
Nhấn Delete.
Bạn chỉ có thể xoá định dạng người dùng. Microsoft Excel áp dụng định dạng ngầm định General number cho mọi ô có định dạng vừa bị xoá.

Hiện hay giấu các giá trị 0 cho toàn bảng tính Trên Tools menu, nhấn Options, chọn phiếu View.
Để hiển thị giá trị không (0) trong các ô, chọn hộp Zero values.
Để các ô có giá trị không hiển thị thành ô trắng, bỏ chọn hộp này.

Giấu các giá trị 0, hay dữ liệu khác Chọn các ô có chứa không (0)hay các giá trị khác cần ẩn.
Trên Format menu, nhấn Cells, chọn phiếu Number.
Trong hộp Category, nhấn Custom.
Để ẩn các giá trị 0, nhập 0;-0;;@ trong hộp Type.
Để làm ẩn tất cả các giá trị, nhập ;;; (ba dấu chấm phảy) trong hộp Type.
Giá trị ẩn chỉ xuất hiện trên thanh công thức, hoặc trong ô khi ta sửa. Chúng không được in ra.

Tip
Để hiển thị lại giá trị ẩn, chọn các ô, nhấn lệnh Cells trên Format menu, trỏ tới phiếu Number. Trong hộp Category, chọn General để áp dụng định dạng ngầm định. Để hiển thị lại một kiểu ngày, giờ hay giá trị theo một định dạng chỉ định, chọn các phiếu tương ứng.
Hiện công thức hay kết quả trên bản tính Trên Tools menu, nhấn Options, chọn phiếu View.
Để hiển thị công thức trong các ô, chọn hộp Formulas.
Để hiển thị kết quả, xoá chọn hộp này.

Định dạng số thành văn bản Phải định dạng trước khi nhập số.
Chọn các ô cần định dạng.
Trên Format menu, nhấn Cells, chọn phiếu Number.
Trong hộp Category, chọn Text.
Nhập các số trong các ô định dạng.

Định dạng tự động Chọn vùng cần định dạng.
Trên Format menu, chọn AutoFormat.
Trong hộp Table format, chọn định dạng mong muốn
Để chỉ áp dụng một số phần trong định dạng tự động, nhấn Options, xoá các hộp có định dạng bạn không muốn dùng

Mẫu định dạng sẵn có Chọn các ô cần định dạng.
Trên Format menu, chọn Style.
Trong hộp Style name, chọn một mẫu.

Tạo mẫu định dạng Chọn ô có các định dạng cho mẫu mới.
Trên Format menu, chọn Style.
Trong hộp Style name, nhập một tên cho định dạng mới.
Để định nghĩa một mẫu mới và áp dụng mẫu cho các ô đang chọn, nhấn OK.
Để định nghĩa mà không áp dụng, nhấn Add, sau đó nhấn Close.

Phục hồi định dạng chuẩn (Normal style) Chọn các ô cần xoá mẫu định dạng.
Trên Format menu, nhấn Style.
Trong hộp Style name, chọn Normal

Bỏ định dạng tự động Chọn vùng cần xoá định dạng.
Trên Format menu, nhấn AutoFormat.
Trong hộp Table format, nhấn None.

Đổi mẫu định dạng Chọn các ô có định dạng mới bạn muốn áp dụng cho mẫu.
Trên Format menu, nhấn Style.
Trong hộp Style name, nhập tên của mẫu bạn định thay đổi.
Phải nhập chứ không được chọn từ danh sách
Nhấn Add.

Xoá một mẫu Trên Format menu, nhấn Style.
Trong hộp Style name, nhấn chọn mẫu cần xoá.
Nhấn Delete.

Chú ý
Không thể xoá mẫu Normal. Nếu xoá các mẫu Currency, Comma, hay Percent, thì sẽ không thể sử dụng các nút này trên thanh định dạng.
Sao chép mẫu định dạng từ sổ khác Mở sổ có chứa các mẫu định dạng cần sao chép.
Mở sổ sẽ nhận các mẫu, và nhấn Style trên Format menu.
Nhấn Merge.
Trong hộp Merge styles from, nháy kép sổ có chứa các mẫu cần sao chép.

Chú ý
Nếu sổ hiện hành có các mẫu trùng tên mẫu cần sao chép, chúng ta phải xác nhận đồng ý trộn mẫu cùng tên. Để thay thế chọ Yes, để giữ nguyên mẫu cũ, chọn No. Cảnh báo này chỉ xuất hiện một lần, cho dù có nhiều mẫu trùng tên.
Lưu mẫu định dạng để dùng trong sổ mới Mở một sổ trắng.
Mở sổ có các định dạng mẫu bạn muốn lưu.
Sao chép các mẫu định dạng sang sổ trắng.
Trong sổ trắng, nhấn Save As trên File menu.
Trong hộp tên File name, nhập book
Trong hộp Save as type, chọn Template (*.xlt).
Trong hộp Save in, chọn thư mục XLStart trong thư mục Microsoft Excel.
Nhấn Save.
Mẫu định dạng sẽ có trong mọi sổ mới bạn tạo ra khi nhấn New .

[tqminh_csp]

- Bảo vệ dữ liệu -
Các khả năng bảo vệ của Microsoft Excel Microsoft Excel cung cấp các tính năng bảo vệ và an toàn sau:
Hạn chế quyền truy cập bảng tính.
Hạn chế quyền truy cập sổ
Bảo vệ sổ bảng tính dùng chung trên mạng.
Đặt mật khẩu mở, hay mật khẩu chống ghi sửa lên một sổ.
Kiểm tra macros viruses.

Hạn chế xem sửa một bảng tính Bảo vệ một bảng tính sử dụng lệnh Protect Worksheet (Tools, Protection), Để bỏ bảo vệ dùng lệnh Unprotect Worksheet (Tools, Protection). Nếu có mật khẩu bảo vệ thì để bỏ bảo vệ phải có mật khẩu.
Nếu bạn chạy một Macro vi phạm bảng đang bảo vệ, một thông báo xuất hiện và Macro dừng lại.
Bảo vệ một bảng tính sẽ không cho thay đổi các ô và đối tượng mà bạn không mở khoá trong bước 2 và 3.
Các bước
Chuyển sang bảng tính cần bảo vệ.
Bỏ khoá mọi ô bạn cho sửa: Chọn các ô đó, lên Format, Cell chọn phiếu Protection, bỏ hộp Locked.
Bỏ khoá mọi đối tượng đồ hoạ cho phép sửa đổi: Nhấn chuột phải lên đối tượng ,chọn Format Object, chọn phiếu Protection, bỏ mục Locked.
Đặt chế độ ẩn cho mọi công thức trên các ô mà bạn muốn giấu: Chọn các ô đó, lên Format, Cell chọn phiếu Protection, chọn hộp Hide.
Trên Tools menu, trỏ tới Protection, nhấn Protect Sheet.
Để cấm sửa dữ liệu, các mục trên đồ thị, hiện lại các hàng, cột ẩn và công thức, chọn hộp Contents.
Để bảo vệ các hình trên bảng tính hay đồ thị, chọn hộp Objects.
Để bảo vệ các phương án khác nhau, chọn Scenarios.
Để bảo vệ bằng mật khẩu, nhập mật khẩu bảo vệ, nhấn OK, nhập lại mật khẩu lần nữa trong hộp Confirm Password. Mật khẩu phân biệt chữ hoa, chữ thường

Các chế độ bảo vệ
Contents:
Nhập sửa các ô, trừ trường hợp bạn đã bỏ khoá ô (unlocked) trước khi bảo vệ bảng tính. Ví dụ, nếu bạn có một bảng tính khoá các nhãn và các hướng dẫn, bỏ khoá các ô vùng dữ liệu để người khác có thể nhập liệu. Người dùng có thể nhấn phím TAB để qua lại trên các ô không bị khoá.
Hiển thị các hàng, cột bạn ẩn trước khi bảo vệ bảng tính.
Xem công thức của các ô mà bạn đặt chế độ ẩn công thức (hide) trước khi bảo vệ bảng tính.
Đối với đồ thị riêng, không thể thay đổi các mục như đường vẽ chuỗi dữ liệu, các trục, và hộp chú giải. Đồ thị vẫn tiếp tục tự thay khi bạn thay đổi dữ liệu nguồn.

Objects:
Các hình hoạ, cả bản đồ (maps) tạo bởi công cụ Microsoft Excel mapping, đồ thị nhúng, hình vẽ, hộp văn bản, trừ trường hợp bạn bỏ khoá đối tượng trước khi bảo vệ bảng tính. Ví dụ trên bảng tính có một nút lệnh chạy một macro, bạn có thể nhấn lên nút lệnh để chạy macro, nhưng không thể xoá nút đó.
Mọi thay đổi về định dạng trên đồ thị nhúng. Đồ thị tự cập nhật nếu bạn thay đổi dữ liệu nguồn.
Cập nhật bản đồ nếu bản đồ được bảo vệ.
Thêm bớt các chú giải (comments).
Đối với đồ thị trên trang riêng, các thay đổi đối với đối tượng đồ hoạ như hộp văn bản bạn vẽ thêm, trừ trường hợp bạn đã đặt chế độ không khoá cho các đối tượng đó.

Scenarios:
Xem các viễn cảnh bạn ẩn.
Thay đổi viễn cảnh mà bạn không cho sửa, hay xoá các viễn cảnh này. Người dùng vẫn có thể thay đổi các ô biến, nêu sô này không bị khoá, và thêm các viễn cảnh mới.

Chú ý
Những nguời lập trình với Visual Basic Editor, có thể sử dụng thuộc tính EnableSelection của Visual Basic for Applications để bảo vệ các ô không cho người dùng chọn chúng.
Hạn chế các sửa đổi trên sổ Trên Tools menu, trỏ tới Protection, chọn Protect Workbook.
Để bảo vệ cấu trúc một sổ chống xoá, dịch chuyển, làm ẩn hiện, đổi tên hay thêm bảng tính mới. Chọn hộp Structure.
Để mỗi lần mở sổ, các cửa sổ luôn xuất hiện cùng vị trí, và cùng kích thước, chọn hộp Windows.
Để ngăn ngừa người khác bỏ chế độ bảo vệ, nhập mật khẩu, nhấn OK,sau đó nhập lại lần nữa để xác nhận trong hộp Confirm Password. Mật khẩu phân biệt chữa hoa, chữ thường.

Hạn chế quyền sử dụng các sổ chia xẻ trên mạng Nếu sổ đang được chia xẻ và bạn muốn gán một mật khẩu bảo vệ chia xẻ hoặc bạn muốn bảo vệ các bảng khác hay các thành phần khác, hãy bỏ sổ khỏi chế độ dùng chung.
Nếu bạn muốn đặt mật khẩu để người dùng khác phải nhập khi mở, hãy đặt mật khẩu.
Nếu bạn muốn người dùng khác phải nhập mật khẩu khi mở sổ dùng chung, hãy đặt mật khẩu.
Nếu bạn muốn ẩn các hàng, cột để người khác không nhìn thấy, chọn các hàng, cột cần ẩn. Trên Format menu, trỏ tới Row hay Column, chọn Hide.
Nếu bạn muốn cho phép người dùng được nhập dữ liệu trên các vùng nào đó, bỏ kháo các vùng đó, sau đó bảo vệ bảng tính. (Việc này cũng ngăn không cho người dùng khác xem các hàng ẩn, cột ẩn)
Nếu bạn không muốn cho những người dùng khác xem một số các bảng tính nào đó, chọn các bảng đó, trỏ tới Sheet trên Format menu, nhấn Hide.
Sau khi làm ẩn các bảng, bạn cần bảo vệ sổ để những người dùng khác không thể làm hiện các bảng bị ẩn. Trên Tools menu, chọn Protection, nhấn Protect Workbook. Đánh dấu hộp Structure, nhấn OK.
Nếu bạn muốn thay đổi số ngày Microsoft Excel lưu các sửa đổi trên sổ (ngầm định là 30 ngày), nhấn Share Workbook trên Tools menu, sau đó chọn phiếu Editing. Chọn hộp Allow changes by more than one user at the same time, chọn phiếu Advanced. Dưới mục Track changes,chọn Keep change history for, nhập số ngày bạn vào hộp Days. Chọn phiếu Editing, xoá hộp Allow changes by more than one user at the same time, nhấn OK.
Trên Tools menu, chọn Protection, nhấn Protect Shared Workbook hay Protect and Share Workbook.
Chọn hộp Sharing with track changes.
Nếu bạn không muốn người dùng tắt chế độ lưu giữ các thay đổi hay huỷ chế độ dùng chung, hãy nhập mật khẩu trong hộp Password, và tái nhập khi được nhắc.
Khi được hỏi, cất sổ. Sổ sẽ được chia sẻ và các thay đổi sẽ được ghi nhớ.

Chú ý
Bảo vệ một bảng tính chia sẻ, người dùng không thể bỏ chế độ chia sẻ và không thể tắt chế độ ghi nhớ các thay đổi.
Trong một sổ đã được chia sẻ, bạn có thể bật chế độ bảo vệ chia sẻ và chế độ ghi lại các thay đổi, nhưng không thể gán mật khẩu cho việc bảo vệ này. Để gán mật khẩu, trước hết phải bỏ chế độ chia sẻ.

Mật khẩu mở sổ Hãy nhớ mật khẩu, nếu mất bạn sẽ không mở sổ được nữa
Trên File menu, chọn Save As.
Nhấn Options.
Trong hộp Password to open, nhập mật khẩu và nhấn OK. Mật khẩu phân biệt chữ hoa, chữ thường.
Trong hộp Reenter password to open, nhập mật khẩu lần nữa, sau đó nhấn OK.
Nhấn Save.
Nếu được hỏi, chọn Yes để thay thể sổ cũ với sổ đang mở.

Mật khẩu sửa sổ Trên File menu, chọn Save As.
Nhấn Options.
Trong hộp Password to modify, nhập mật khẩu và nhấn OK. Mật khẩu phân biệt chữ hoa, chữ thường.
Trong hộp Reenter password to modify, nhập mật khẩu lần nữa, sau đó nhấn OK.
Nhấn Save.
Nếu được hỏi, chọn Yes để thay thể sổ cũ với sổ đang mở.

Có thể mở, xem và lưu sổ dưới tên khác. Không thể sửa, lưu trên sổ.
Mở một sổ chỉ đọc Trên File menu, nhấn Save As.
Chọn Options.
Chọn hộp Read-only recommended, nhấn OK.
Nhấn Save.
Nếu được hỏi, chọn Yes để thay thể sổ cũ với sổ đang mở.

Có thể viết các macros bằng Visual Basic for Applications để bảo vệ bảng tính hay sổ.
Bỏ bảo vệ một bảng Chọn bảng đó.
Trên Tools menu, trỏ tới Protection, nhấn Unprotect Sheet. Nhập mật khẩu nếu bị hỏi.

Bỏ bảo vệ một sổ TrênTools menu, chọn Protection, nhấn Unprotect Workbook. Nhập mật khẩu bảo vệ sổ nếu bị hỏi.
Bỏ bảo vệ một sổ dùng chung trên mạng Khi bỏ bảo vệ một bảng chia sẻ với mật khẩu, cũng có nghĩa là loại sổ khỏi chế độ dùng chung, Microsoft Excel ngắt tất cả những người dùng khác ra khỉ sổ, tắt chế độ lưu giữ thay đổi, xoá các thay đổi lưu giữ và bạn không dùng lại được nữa. Để tránh mất dữ liệu của người dùng khác, đảm bảo những người khác đã lưu và đóng sổ. Trên Tools menu, nhấn Share Workbook, chọn phiếu Editing, đảm bảo bạn là người duy nhất trong hộp Who has this workbook open now .
Trên Tools menu, gọi Protection, chọn Unprotect Shared Workbook.
Nhập mật khẩu nếu bị hỏi.
Nếu được hỏi về ản hưởng tới những người dùng khác nếu bỏ bảo vệ sổ chia sẻ, chọn Yes.
Nếu bỏ bảo vệ cũng loại sổ khỏi chế độ chia sẻ, đặt sổ quya lại chế dộ chia sẻ.

Chú ý
Nếu chia sẻ không sử dụng mật khẩu thì sổ vấn tiếp tục chia sẻ khi bạn bỏ chế độ bảo vệ
Bỏ mật khẩu sổ Mở sổ
Trên File menu, nhấn Save As.
Nhấn Options.
Trong hộp Password to open hay Password to modify, nhấn kép lên biểu tượng thể hiện mật khẩu hiện tại.
Để thay đổi mật khẩu, nhập mật khẩu mới, nhấn OK.Để xoá mật khẩu, nhấn DELETE, và nhấn OK.
Nếu đổi mật khẩu mới, nhập lại nó, nhấn OK.
Nhấn Save.
Nếu được hỏi, chọn Yes để thay thể sổ cũ với sổ đang mở.

[tqminh_csp]

Visual Basic for Application
Từ Office 95 Microsoft bắt đầu đưa vào một ngôn ngữ lập trình chung, không chỉ áp dụng cho các ứng dụng như Word và Excel mà cho cả các công cụ lập trình như VB. Microsoft gọi ngôn ngữ lập trình này là Visual Basic for Applications (VBA).

VBA for Excel

Microsoft Excel là một công cụ mạnh để phân tích và trình bày thông tin. Một trong những thế mạnh của Excel là ngôn ngữ macro. Kể từ khi ra đời, Excel luôn là chương trình bảng tính có cài ngôn ngữ macro bao quát và linh hoạt nhất trong các phần mềm bảng tính.

Để viết macros trong Excel, bạn thật sự cần học hai công cụ khác biệt. Trước hết, bạn cần học cách làm việc với Visual Basic. Mọi kiến thức bạn học về Visual Basic không những áp dụng được trong Excel mà còn cho các phần mềm Microsoft khác. Thứ hai bạn học cách sử dụng Excel. Càng nắm vững sử dụng bảng tính Excel, bạn càng xây dựng hiệu quả các macros.

Bài 1: Chương trình VBA đầu tiên - Ra quyết định sử dụng VBA for Excel
Giả thiết bạn có bảng doanh số bán hàng như hình 1. Bạn muốn tìm xem những mặt hàng nào bán ra ít hơn 100 bộ và tô mầu đỏ. Chúng ta có thể sử dụng Format có điều kiện để làm việc này. Tuy nhiên để áp dụng linh hoạt, chúng ta sẽ xây dựng một macro làm việc đó.

Doanh số bán hàng của quý I và quí II

Tháng 1
Tháng 2
Tháng 3
Tháng 4
Tháng 5
Tháng 6

áo phông thể thao
30
120
115
122
98
110

Bộ đồ thểthao
220
180
160
140
120
100

Bộ áo mưa
93
90
101
105
200
220

Quần soóc
125
133
199
83
104
93


Hình 1

Xây dựng nội dung VBA sử dụng Record Macro
Excel cung cấp một công cụ tiện lợi để thu một Macro.

Đứng ở một ô cần định dạng
Trên thanh công cụ toolbar, chọn Tools, Macro, chọn Record New Macro.
Hộp hội thoại Record New Macro xuất hiện. Trong hộp Macro Name, nhập tên của Macro cần thu, ví dụ MakeDecision và nhấn nút OK.
Nút Record Macro xuất hiện như trong hình bên cho thấy bộ thu recorder đang hoạt động. Đảm bảo nút Relative Reference được nhấn lõm xuống.
Định dạng ô (tô chữ mầu đỏ), sau đó nhấn mũi tên xuống dưới một lần
Nhấn nút Stop Macro
Lên Tools, Macro, Macros, một hộp hội thoại Macros xuất hiện. Chọn MakeDecision dưới mục Macro Name, sau đó nhấn nút Edit. Như vậy chúng ta chuyển sang môi trường lập trình của Excel, đồng thời chuyển tới macro vừa thu.
Sub MakeDecision()
' MakeDecision Macro
' Macro recorded 2/15/00 by Phu
'
Selection.Font.ColorIndex = 3
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub


Đối tượng ActiveCell tham chiếu đến ô hiện hành. Phương thức Offset của đối tượng ActivelCell chọn vùng nằm lệch đi so với ô hiện hành. Trong macro trên chọn ô nằm dưới vùng đang chọn một hàng và trên cùng cột.

Chạy macro
Chọn Sheet1 nếu nó chưa được chọn
Đứng ở ô dữ liệu đầu tiên (giao của hàng áo phông thể thao với Tháng 1). Trên Tools menu, chọn Macro, Macros, một hộp hội thoại Macro xuất hiện.
Chọn macro MakeDecision từ danh sách và nhấn OK.
Để tiện thử macro trong khi viết có thể gán nó với một nút

Gán macro với một nút
Nếu chưa xuất hiện thanh công cụ Form, hiện nó lên.
Trên thanh công cụ Form chọn nút Button rồi vè một nút lên bảng tính (nhấn giữ chuột trái trong khi vẽ một hình chữ nhật)
Nhả chuột trái, một hộp hội thoại Assign Macro xuất hiện, chọn MakeDecision từ danh sách, sau đó nhấn nút OK
Một nút xuất hiện trên bảng tính, mỗi khi nhấn chuột lên nút thì macro MakeDecision sẽ được thực hiện.
Chú ý: để sửa thuyết minh trên nút, bạn có thể nhấn Ctrl và nháy lên nút, khi đó nút sẽ được chọn, bạn có thể nhập thuyết minh mới.

Định dạng cho toàn cột
Macro trên chỉ định dạng cho một ô. Để định dạng toàn bộ cột, cần thêm hai dòng lệnh ở đầu và cuối thủ tục như sau:

Sub MakeDecision()
' MakeDecision Macro
' Macro recorded 2/15/00 by Phu
Do Until ActiveCell = ""
Selection.Font.ColorIndex = 3
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
End Sub

Để dừng vòng lặp điều kiện Until ActivelCell = "" được đưa vào câu lệnh Do-Loop. Macro sẽ duyệt toàn bộ các ô trong cột và định dạng cho đến khi gặp ô trống.

Để macro ra quyết định
Chúng ta cần phải để macro tự ra quyết định nhằm tự động hoá việc định dạng. Tuy nhiên, bộ ghi macro không thể đưa các quyết định vào macro. Chúng ta phải tự làm lấy việc đó. Bạn muốn macro tự quyết định xem có cần phải tô đỏ ô hiện hành hay không.

Quay sang VBA-Editor, chọn macro (thủ tục) MakeDecision, nhập câu lệnh If ActiveCell > 100 Then để quyết định xem ô nào có giá tị nhỏ hơn.
Nhập End If để kết thúc lệnh If. Macro sẽ như sau:
Sub MakeDecision()
'
' MakeDecision Macro
' Macro recorded 2/15/00 by Phu
Do Until ActiveCell = ""
If ActiveCell < 100 Then
Selection.Font.ColorIndex = 3
End If
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
End Sub

Để quyết định hoạt động trên toàn bảng
Bạn cần phải để macro chuyển tới cột tiếp theo để chạy khắp bảng tính. Thêm các câu lệnh để chuyển đến đỉnh của cột tiếp theo.

Sub MakeDecision()
'
' MakeDecision Macro
' Macro recorded 2/15/00 by Phu
Do Until ActiveCell = ""
If ActiveCell < 100 Then
Selection.Font.ColorIndex = 3
End If
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
ActiveCell.Offset(-1, 1).Range("A1").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

Dòng lệnh mới thêm thứ nhất để Excel lên trên một ô sau đó sang phải một ô.
Dòng thứ hai để Excel lên đỉnh cột.
Dòng cuối cùng để Excel đang từ đỉnh cột, nghĩa là vùng tiêu đề cột, xuống dưói một ô, tức vùng dữ liệu, như vậy là vùng tiêu đề không bị đưa vào tô mầu.
Cuối cùng thêm một câu lệnh lặp Do Loop khác để macro có thể duyệt hết từ cột này sang cột khác cho đến khi gặp phải ô trống.

Phiên bản visual basic macro cuối cùng sẽ như sau:

Sub MakeDecision()
'
' MakeDecision Macro
' Macro recorded 2/15/00 by Phu
Do Until ActiveCell = ""
Do Until ActiveCell = ""
If ActiveCell < 100 Then
Selection.Font.ColorIndex = 3
End If
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
ActiveCell.Offset(-1, 1).Range("A1").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
End Sub

Chạy macro để định dạng toàn bảng tính
Chọn Sheet1 nếu nó chưa được chọn
Đứng ở ô dữ liệu đầu tiên (giao của hàng áo phông thể thao với Tháng 1). Trên Tools menu, chọn Macro, Macros, một hộp hội thoại Macro xuất hiện.
Chọn macro MakeDecision từ danh sách và nhấn OK.
Toàn bộ bảng tính sẽ được duyệt và những doanh số dưới 100 sẽ bị tô đỏ.
Bài 2: VBA for Excel - tính tổng luỹ tiến
Bài toán
Tính cột tổng lũy tiến và tính luỹ tiến tại ô

Excel cung cấp một số phương pháp để tính tổng luỹ tiến. Bạn có thể sử dụng hàm =SUM($A$1:A1) trong đó trên cột A bạn nhập các giá trị và nhập công thức tính tổng luỹ tiến tại ô đầu tiên trên cột B sau đó copy công thức xuống các ô bên dưới.

Để tính tổng luỹ tiến ngay tại một ô, chúng ta có thể sử dụng một macro VBA để lưu một tổng dồn dưới dạng phần chú giải (note) của một ô, khi nhập một số vào trong ô thì giá trị của ô sẽ công thêm với giá trị trong phần chú giải của ô đó.

Tính cột tổng luỹ tiến
A
B


Values
Cumulative Total

2.00
2.00

43.00
45.00

5.00
50.00

43.00
93.00

65.00
158.00

45.00
203.00


Tạo ô cộng luỹ tiến
Lên tool, Macro, VBA-Editor để sang môi trường lập trình của Excel
Trong môi trường lập trình, lên menu insert, chọn module
Nhập các chương trình sau

Trên phần General Declaration đưa vào dòng lệnh
Option Explicit 'Nghĩa là việc khai báo các biến là bắt buộc

Thủ tục Auto_Open
Auto_Open là một tên đặc biệt trong Visual Basic, mỗi lần mở workbook, Auto_Opensẽ tự chạy. Trong ví dụ này Excel sẽ gọi thủ tục "CumTotal" mỗi khi bạn mở sổ bảng tính.
Sub Auto_Open()
Application.OnEntry = "CumulativeTotal"
End Sub

Thủ tục gán tổng luỹ tiến AssignCumulativeTotal
Khi bạn chọn một ô và chạy macro này, Excel sẽ gán trường ghi chú giá trị CumTotal_ với số trong ô hiện hành. Ví dụ nếu nhập số 12 thì trường ghi chú sẽ là "CumTotal_12"

Sub AssignCumulativeTotal()
If Application.IsNumber(ActiveCell) Then
ActiveCell.NoteText Text:="CumTotal_" & ActiveCell
Else
ActiveCell.NoteText Text:="CumTotal_" & "0"
End If
End Sub

Thủ tục cộng dồn CumulativeTotal
Macro Auto_Open đặt Excel sẽ gọi thủ tục này mỗi khi bạn nhập liệu vào bảng tính.

Sub CumulativeTotal()
'Câu lệnh If để kiểm tra xem ô mà bạn vừa
' nhập liệu vào (Application.Caller) có trường ghi chú bắt đầu
' với "CumTotal_" hay không
If Application.Caller.NoteText(length:=9) = "CumTotal_" Then
'Nếu trường note chứa tổng, câu lệnh sau sẽ cộng tổng này với 'số vừa nhập vào ô.
Application.Caller.Value = Application.Caller.Value + _
Application.Caller.NoteText(start:=10)
'Câu lệnh sau lưu tổng cộng dồn mới vào trường note.
Application.Caller.NoteText "CumTotal_" & Application.Caller.Value
End If
End Sub

Thủ tục xoá chức năng cộng luỹ tiến ra khỏi ô hiện hành CancelCumulativeTotal
Sub CancelCumulativeTotal()
ActiveCell.ClearNotes
End Sub

Thủ tục đưa tổng luỹ tiến về không ResetCumulativeTotal
Sub ResetCumulativeTotal()
ActiveCell.NoteText Text:="CumTotal_" + "0"
ActiveCell.Value = 0
End Sub

Bài 3: Hàm tự tạo - Hàm cắt tên
Function CatTen(HoVaTen As String) As String
Dim l, i As Integer
HoVaTen = Trim(HoVaTen)
l = Len(HoVaTen)
CatTen = ""
For i = l To 1 Step -1
If Mid(HoVaTen, i, 1) = Space(1) Then
k = i
Exit For
End If
Next i
CatTen = Mid(HoVaTen, i + 1)
End Function

Bài 4: Hàm tự tạo - Đổi số ra chữ
Function SoToChu(So)
If So = 0 Then
Resp = "Không Đồng"
Else
If Abs(So) > 999999999999.99 Then
Resp = "Số quá lớn"
Else
GoSub ThucHien:
End If
End If
SoToChu = UCase(Left(Resp, 1)) + Mid(Resp, 2)
Exit Function

ThucHien:
If So < 0 Then
Resp = "Trừ "
Else
Resp = Space(0)
End If
Tien = Format(Abs(So), "###########0.00")
Tien = Right(Space(12) + Tien, 15)
Doc = Space(0): Dem = Doc
Doc = Doc + "trăm mươi tỷ "
Doc = Doc + "trăm mươi triệu "
Doc = Doc + "trăm mươi ngàn "
Doc = Doc + "trăm mươi đồng "
Doc = Doc + "trăm mươi xu "
Dem = Dem + "một hai ba bốn năm sáu bảy tám chín "

For i = 1 To 5
Nhom = Mid(Tien, i * 3 - 2, 3)
If Nhom <> Space(3) Then
Select Case Nhom
Case "000"
If i = 4 Then
Chu = "đồng "
Else
Chu = Space(0)
End If
Case ".00"
Chu = "chẵn "
Case Else
So1 = Left(Nhom, 1)
So2 = Mid(Nhom, 2, 1)
So3 = Right(Nhom, 1)
Chu = Space(0)

For J = 1 To 3
Dich = Space(0)
S = Val(Mid(Nhom, J, 1))
If S > 0 Then

Dich = Trim(Mid(Dem, S * 5 - 4, 5)) + " "
If J = 2 Then DichCu = Dich
If J = 3 And Dich = "bốn " And DichCu <> "một " Then Dich = "tư "

'MsgBox Dich

Dich = Dich + Trim(Mid(Doc, (i - 1) * 18 + J * 6 - 5, 6)) + " "

End If
Select Case J
Case 2 And S = 1
Dich = "mười "
Case 3 And S = 0
Dich = Trim(Mid(Doc, (i - 1) * 18 + J * 6 - 5, 6)) + " "
Case 3 And S = 5 And So2 <> Space(1) And So2 <> "0"
Dich = "l" + Mid(Dich, 2)
Case 2 And S = 0 And So3 <> "0"
If (So1 >= "1" And So1 <= "9") Or (So1 = "0" And i = 4) Then
Dich = "lẻ "
End If
End Select
Chu = Chu + Dich
Next J

End Select
ViTri = InStr(1, Chu, "mươi một", 1)
If ViTri > 0 Then Mid(Chu, ViTri, 9) = "mươi mốt"
If Chu <> "đồng " And Chu <> "chẵn " And Resp <> Space(0) Then
Resp = Resp + ", " + Chu
Else
Resp = Resp + Chu
End If
End If
Next i
Return
End Function


Bài 5 Ví dụ về hàm tự tạo
A
B
C
D
E
F
G

1
123
-62
-39
102
98
-50
?

2
18589
-34716
-28310
-10065
-33145
20588
??

3

4

5
2
-50
-16
-96
-57
-8
?

6
19161
38990
5297
-29889
19228
-16365
??

7

8

9
2
3
10
-54
60
75
?

10
-4395
12541
162
-25270
-23856
16377
??

11

12

13
200
-43
-57
-16
-63
-29
?

14
38202
22914
16268
-22760
20385
-22947
??


Yêu Cầu

? bằng tổng của giá trị ngang hàng nằm ở cột đầu với giá trị lớn nhất của cột B+C với các ô trong cột còn lại D, E và F, lấy giá trị tìm được nếu dương, còn không để trắng

?? Cột A cộng với giá trị tương ứng với giá trị lớn nhất tìm thấy ở trên, trường hợp giá trị lớn nhất là tổng B+C thì lấy tổng tương ứng của B+C, nếu tổng của ? là dương, còn không để trắng

Function MySumIf1(Vung As Range)
Dim SoHang, SoCot, i As Byte
SoCot = Vung.Columns.Count
SoHang = Vung.Rows.Count
If SoCot < 4 Or SoHang < 2 Then
MySumIf = "The range must contain at least two rows and four columns"
Exit Function
End If
Dim Max As Double
Max = Vung.Cells(1, 2) + Vung.Cells(1, 3)
For i = 4 To SoCot
If Abs(Vung.Cells(1, i)) > Abs(Max) Then Max = Vung.Cells(1, i)
Next i
MySumIf1 = IIf(Vung.Cells(1, 1) + Max > 0, Vung.Cells(1, 1) + Max, "")
End Function

Function MySumIf2(Vung As Range)
Dim SoHang, SoCot, i As Byte
SoCot = Vung.Columns.Count
SoHang = Vung.Rows.Count
If SoCot < 4 Or SoHang < 2 Then
MySumIf = "The range must contain at least two rows and four columns"
Exit Function
End If
Dim Max, Tong As Double
Max = Vung.Cells(1, 2) + Vung.Cells(1, 3)
Tong = Vung.Cells(2, 2) + Vung.Cells(2, 3)
For i = 4 To SoCot
If Abs(Vung.Cells(1, i)) > Abs(Max) Then Tong = Vung.Cells(2, i)
Next i
Tong = Vung.Cells(2, 1) + Tong
MySumIf2 = IIf(IsNumeric(Vung.Cells(1, SoCot + 1)), Tong, "")
End Function

[tqminh_csp]

http://www.echip.com.vn/echiproot/html/2004/so140/phoihopcacham.html

Phối hợp các hàm tìm kiếm thông dụng trong MS Excel

Các hàm tìm kiếm trong Excel giúp ta rất nhiều trong việc chọn ra những giá trị cần thiết, phù hợp với yêu cầu tính toán. Sử dụng các hàm này không khó, song yêu cầu bạn phải có một chút linh hoạt và để ý đến cách sử dụng các đối số của hàm.


1. HÀM HLOOKUP() VÀ VLOOKUP():
HLOOKUP(Giá trị tìm, Vùng tìm, Dòng thứ mấy, Kiểu) tìm kiếm Giá trị tìm theo hàng ngang tức là hết cột này sang cột khác ở dòng trên cùng của Vùng tìm. Nếu tìm thấy ô có Giá trị tìm thì dời xuống để lấy giá trị tại ô thứ Số dòng kể từ dòng đầu. Nếu Kiểu = 0, bạn không phải bận tâm đến thứ tự các giá trị của dòng trên cùng của Vùng tìm, còn Kiểu = 1 buộc dòng này phải sắp xếp theo thứ tự tăng dần, bằng không hàm sẽ cho giá trị sai bét. Hàm VLOOKUP(Giá trị tìm, Vùng tìm, Cột thứ mấy, Kiểu) cũng tương tự nhưng thay vì tìm theo hàng ngang thì nó tìm theo hàng dọc.
Để sử dụng hai hàm này, bạn cần lưu ý: đối số Giá trị tìm có thể là một giá trị cụ thể hoặc một địa chỉ ô. Địa chỉ sử dụng cho đối số Vùng tìm phải là địa chỉ tuyệt đối để khi sao chép không bị thay đổi. Đối số Kiểu có giá trị mặc định là 1 nên bạn phải luôn đặt = 0 cho chắc ăn.
HLOOKUP() phối hợp với VLOOKUP() sẽ rất hữu dụng để tìm giá trị trên một ô dựa vào các giá trị của tiêu đề dòng và tiêu đề cột. Theo ví dụ, hệ số điểm các môn học tùy thuộc vào từng khối. Để tìm hệ số điểm môn toán của khối A, bạn sử dụng công thức =HLOOKUP(“Toán”, $A$2:$J$6,VLOOKUP(“A”,$A$2:$J$6,2,0),0). Kết quả sẽ là 3. Trong công thức này, hàm HLOOKUP() lấy giá trị của hàm VLOOKUP() để biết phải dời đi mấy dòng mà “moi ra” giá trị cần tìm.
2. HÀM INDEX():
Hàm INDEX(Vùng tìm, Dòng, Cột) được sử dụng để lấy giá trị của một ô trên Vùng tìm là nơi giao nhau giữa 2 đối số Dòng và Cột. Để sử dụng hàm này, bạn phải biết rõ thứ tự dòng và cột của các khối và môn học từ trước. Công thức =INDEX($A$2:$J$6,2,9) cũng sẽ cho cùng kết quả như trên.
3. HÀM MATCH():
Hàm MATCH(Giá trị tìm, Vùng tìm, Cách tìm) được sử dụng để tìm vị trí xuất hiện của Giá trị tìm tại Vùng tìm (chỉ là một dòng hay một cột thôi). Hãy sử dụng Cách tìm = 0 để tìm đích xác Giá trị tìm. Nếu Cách tìm là 1 hay -1, hàm sẽ tìm luôn cả những giá trị lớn hơn hoặc nhỏ hơn Giá trị tìm, chắc là bạn sẽ không mong muốn điều này. Với công thức =MATCH(“Toán”, $A$2:$J$2,0), sẽ được kết quả là 9.
Phối hợp giữa 2 hàm này, cách thức hoạt động và kết quả tìm được cũng tương tự sự phối hợp giữa hai hàm HLOOKUP() và VLOOKUP(). Với công thức =INDEX($A$2:$J$6, M A T C H ( “ A ” , $ A $ 2 : $ A $ 6 , 0 ) , MATCH(“Toán”,$A$2:$J$2,0)), kết quả cũng sẽ là 3.

[tqminh_csp]

http://www.echip.com.vn/echiproot/html/2004/so140/soanbaithiexcel.html

Soạn bài thi trắc nghiệm bằng bảng tính

bạn tạo ra bảng tính Excel gồm hai tờ: tờ Sheet2 chứa đề thi và đáp án, tờ Sheet1 thể hiện những câu hỏi trên tờ Sheet2 và có ô liệt kê "xổ xuống" bên dưới mỗi câu hỏi.

Ta hãy đưa các câu trả lời của câu hỏi thứ nhất vào ô liệt kê thứ nhất trên tờ Sheet1. Bấm-phải vào ô liệt kê đó và chọn Properties trên trình đơn "di động", bạn thấy xuất hiện cửa sổ Properties trình bày mọi thuộc tính của ô liệt kê đang xét. Trên cửa sổ Properties, bạn hãy tìm đến thuộc tính ListFillRange, bấm vào ô bên phải ListFillRange, gõ cau1 và gõ Enter. Bằng cách như vậy, ta đưa các câu trả lời của câu hỏi thứ nhất (thuộc vùng ô mang tên cau1) vào ô liệt kê. Nếu không tin, bạn bấm vào Exit Design Mode để thoát khỏi chế độ vẽ thiết kế và thử bấm vào mũi tên "xổ xuống" của ô liệt kê xem sao.

Nếu bạn... tin rồi thì hãy bấm Design Mode để trở lại chế độ vẽ thiết kế và bấm vào ô liệt kê thứ nhất để chọn. Điều tiếp theo ta cần quan tâm là ghi nhận sự lựa chọn của thí sinh trong ô liệt kê. Trên cửa sổ Properties, bạn tìm đến thuộc tính LinkedCell (nằm ngay trên thuộc tính ListFillRange), bấm vào ô bên phải LinkedCell, gõ Sheet2!C1 và gõ Enter. Điều này nghĩa là ta đưa câu trả lời được chọn trên ô liệt kê thứ nhất vào trang Sheet2, đặt tại ô C1 (nằm trên cùng hàng với câu hỏi thứ nhất).

Vậy là ta "dứt điểm" ô liệt kê thứ nhất. Bạn bấm vào ô liệt kê thứ hai. Cửa sổ Properties lúc này hiển thị các thuộc tính của ô liệt kê thứ hai. Bạn hãy quy định thuộc tính LinkedCell là Sheet2!C7 và thuộc tính ListFillRange là cau2. Bạn biết ngay, ô liệt kê thứ hai sẽ trình bày các câu trả lời nằm trong vùng ô mang tên cau2 và câu trả lời được chọn sẽ xuất hiện ở ô C7 trên tờ Sheet2 (nằm trên cùng hàng với câu hỏi thứ hai). Cứ theo cách như thế, bạn lần lượt "cài đặt" các ô liệt kê tiếp theo.

Xong xuôi, ta bắt đầu "dạy" cho Excel cách chấm điểm. Chuyển qua tờ Sheet2, bạn gõ công thức trong ô D1 như sau: =VLOOKUP(C1,A2:B5,2,FALSE). Với các đối mục như vậy, hàm VLOOKUP đọc nội dung ô C1 (chứa câu trả lời được chọn của ô liệt kê thứ nhất) và dò tìm nội dung đó trong cột đầu tiên của vùng ô A2:B5. Khi tìm được ô cần thiết (chắc chắn là tìm được!), hàm VLOOKUP "liếc" qua cột thứ 2 bên cạnh và cho ta biết điểm số tương ứng. Ta ghi đối mục cuối cùng là FALSE nhằm "nói" rằng cột đầu tiên không có thứ tự vần. Nếu bạn ghi TRUE, hàm VLOOKUP sẽ hiểu lầm rằng cột đầu tiên có thứ tự vần, thế rồi tìm kiếm một cách... bộp chộp và cho kết quả sai.

Với hàm VLOOKUP, Excel thay bạn ghi điểm vào ô D1 cho câu trả lời được chọn. Điểm là 1 hoặc 0 tùy theo câu trả lời là đúng hoặc sai. Để chấm điểm cho câu trả lời tiếp theo tại ô C7, bạn chỉ cần sao chép công thức ở ô D1 vào ô D7. Cụ thể, bạn bấm vào ô D1, ấn Ctrl+C rồi bấm vào ô D7 và ấn Ctrl+V. Excel tự động hiệu chỉnh công thức được sao chép để có công thức thích hợp cho ô D7: =VLOOKUP(C7,A8:B11,2,FALSE). Bạn tiếp tục áp dụng "công nghệ" này để chấm điểm đối với những câu hỏi còn lại.


Dĩ nhiên, ta phải tính tổng số điểm trong cột D. Bạn gõ Điểm: trong ô E1 và gõ công thức =SUM(D:D) trong ô F1 kế bên. Chỉ cần nhìn vào ô F2, bạn biết ngay điểm bài thi của thí sinh.

Trước khi "đưa vào sử dụng", bạn chọn Format > Sheet > Hide để giấu tờ Sheet2 rồi chọn Tools > Protection > Protect Workbook để ngăn cản thí sinh lôi ra "lá bùa" ấy. Trên hộp thoại Protect Workbook, bạn chọn Structure (bảo vệ cấu trúc bảng tính), gõ mật khẩu gì đó trong ô Password và gõ Enter. Vốn tính cẩn thận, Excel sẽ yêu cầu bạn gõ mật khẩu lần nữa cho chắc ăn.

Để tờ Sheet2 tái hiện, trước hết bạn "mở khóa" cho bảng tính bằng cách chọn Tools > Protection > Unprotect Workbook, gõ mật khẩu trên hộp thoại Unprotect Workbook và gõ Enter. Tiếp theo, bạn chọn Format > Sheet > Unhide và gõ Enter.

Ngoài ra, ở đầu tờ Sheet1, bạn nhớ tìm chỗ nào đó để trình bày phần dành cho thí sinh ghi họ tên (cùng các chi tiết khác) và đóng khung vùng ô đó cho rõ ràng.

"Công trình" của bạn thế là hoàn tất rồi. Sau này, khi soạn bài thi trắc nghiệm khác, bạn chỉ cần sửa đổi những câu hỏi và câu trả lời trên tờ Sheet2 là xong.

http://www.echip.com.vn/echiproot/images/2004/so140/excel3.jpg

[tqminh_csp]

Mình sưu tầm được bài giảng Excel bằng PowerPoint

[tqminh_csp]

Hàm Excel tính tuổi nợ và số dư bình quân


Kế toán công nợ thường phải tính tuổi số dư khoản phải thu của khách hàng để biết khoản nào cần được thu trước, ngoài ra còn phải tính số dư bình quân của các khoản phải thu này nhằm "nâng cao chất lượng quản lý tài chính". Nếu không có phần mềm kế toán cung cấp sẵn các chức năng này hoặc phải tác nghiệp bằng Excel một cách thủ công thì tôi đảm bảo bạn sẽ cảm thấy cuộc đời của kế toán công nợ không khác gì "cửu vạn". Hai hàm Excel dưới đây có thể giúp bạn nhanh chóng lấy lại cảm giác yêu đời.

Ví dụ chúng ta có 1 sheet dữ liệu của khách hàng như hình dưới đây.

• Cột thứ nhất là ngày tháng giao dịch với khách hàng (sắp xếp theo ngày tăng dần)

• Cột thứ hai là số tiền phải thu của khách hàng (ghi nợ)

• Cột thứ ba là số đã thu được của khách hàng (ghi có)

• Cột thứ tư là cột thứ 2 trừ cột thứ 3

• Cột thứ năm là cân đối còn phải thu sau từng giao dịch, cột 6 là ghi chú

Việc tính toán chỉ cần cột 1 đến cột 4.

Tạo hàm

1. Function OldOfDebt(mRange As Range, toDate As Date) As Double

Hàm này trả về tuổi của khoản phải thu theo ngày, bằng cách giải đáp số dư còn phải thu tại ngày cuối cùng là các khoản phải thu của các ngày nào theo nguyên tắc khoản đã thu được sẽ là thanh toán cho khoản phải thu đến trước, sau đó tính ra số ngày theo trọng số của từng khoản đối với số dư.

Hàm có hai tham số, thứ nhất mRange chính là vùng tính toán, trong ví dụ là A2:D13; thứ hai toDate là ngày để xác định tuổi (toDate phải lớn hơn ngày cuối cùng phát sinh giao dịch với khách hàng), trong hình là C19. Theo ví dụ hàm tính tuổi của khoản phải thu có giá trị 191.000 là 146,36 ngày trả về tại ô E19.

2. Function AvgBalance(mRange As Range, toDate As Date) As Double

Hàm này có tham số y hệt hàm trên, trả về số dư còn phải thu trung bình của các khách hàng theo tỷ trọng về thời gian. Trong hình, hàm tính ra số dư bình quân trả về ô E21 là 106.791 với mRange là A2:D13 và toDate là 31/12/2005. (Bạn có thể tính được tổn thất do khách hàng này chiếm dụng bằng Hàm x lãi suất trong khoảng thời gian A2 đến A13).

Mã nguồn

Public Function OldOfDebt(mRange As Range, toDate As Date) As Double

Dim rDate As Range Cot ngay

Dim rDebit As Range Cot ghi no

Dim rCredit As Range Cot ghi co

Dim mPaid As Double Tong so da thu duoc

Dim mClose As Double So du cuoi tai ngay toDate

Dim mAccDebit As Double Debit cong don

Dim thisAmount As Double

Dim thisDate As Double

Dim mRow As Long Bien dem so dong

Dim i As Long

Dim ret As Double Gia tri tro ve

mRow = mRange.Rows.Count

Set rDate = mRange.Range(Cells(1, 1), Cells(mRow, 1))

Set rDebit = mRange.Range(Cells(1, 2), Cells(mRow, 2))

Set rCredit = mRange.Range(Cells(1, 3), Cells(mRow, 3))

mPaid = Application.WorksheetFunction.Sum(rCredit)

mClose = Application.WorksheetFunction.Sum(rDebit) - Application.WorksheetFunction.Sum(rCredit)

For i = 1 To mRow

If rDebit.Cells(i, 1).Value <> 0 Then

mAccDebit = mAccDebit + rDebit.Cells(i, 1).Value

If mAccDebit > mPaid Then

thisAmount = Application.WorksheetFunction.Min(mAccDebit - mPaid, rDebit.Cells(i, 1).Value)

thisDate = rDate.Cells(i, 1).Value

ret = ret + thisAmount * (toDate - thisDate) / mClose

End If

End If

Next i

OldOfDebt = ret

End Function

Public Function AvgBalance(mRange As Range, toDate As Date) As Double

Dim rDate As Range

Dim rAmount As Range

Dim mRow As Long

Dim mLenght As Long quang thoi gian tu ngay dau den toDate

Dim i As Long

Dim ret As Double

mRow = mRange.Rows.Count

Set rDate = mRange.Range(Cells(1, 1), Cells(mRow, 1))

Set rAmount = mRange.Range(Cells(1, 4), Cells(mRow, 4))

mLenght = toDate - rDate.Cells(1, 1)

For i = 1 To mRow

ret = ret + rAmount.Cells(i, 1) * (toDate - rDate.Cells(i, 1)) / mLenght

Next i

AvgBalance = ret

End Function

Nguyễn Văn Thắng
Email: thang_via@yahoo.com

[tqminh_csp]

www.giaiphapexcel.com
http://www.giaiphapexcel.com
http://www.giaiphapexcel.com/forum/index.php

[new_volunteer189]

Thank you !!!!!!!!!rất hay !!!!!!!!!

[Vietmedia Group]

Thanks you!