BÀI 1: ĐỊNH DẠNG BẢNG TÍNH MS EXCEL

I. THIẾT LẬP CẤU HÌNH CHUNG BẢNG TÍNH

Để thiết lập cấu hình chung cho toàn bảng tính MS EXCEL giúp người dùng tương tác bảng tính phù hợp hơn:

B1: Mở MS EXCEL

B2: Vào File ( hoặc vào biểu tượng office) / option (hoặc Excel option) / xuất hiện hộp thoại

B3: Lần lượt chọn các thẻ:

+ General: Cấu hình chung

+ Formulas: Cấu hình cho công thức Excel hay còn gọi hàm

+ Proofing: Cấu hình văn bản

+ Advanced: Thiết lập nâng cao

+ Add-ins: Cài đặt mở rộng

…

II. THIẾT LẬP CHẾ ĐỘ BẢO VỆ BẢNG TÍNH

1.  Thiết lập bảo vệ vùng dữ liệu

B1: Chọn cả bảng tính MS EXCEL ( nhấn vào góc trên bên trái bảng tính)

B2: Home / Format / Format cell / hiển thị hộp thoại

Hình 1

+ Chọn thẻ Protection

+ Bỏ chọn Locked / Ok

B3: Chọn vùng dữ liệu cần bảo vệ

B4: Vào Home / Format / Format cell / Xuất hiện hộp thoại như hình 1 / chọn thẻ Protection / Chọn lại Locked / Ok

B5: Vào Thẻ REVIEW / PROTECT SHEET / xuất hiện hộp thoại

Hình 2

+ Tại Password to unprotect sheet: thiết lập Password

+ Allow all user of this worksheet to: Chọn định dạng không muốn khóa

 => OK => Thiết lập Password lại lần nữa =>OK

2.  Thiết lập bảo vệ Công thức

B1: Chọn cả bảng tính MS EXCEL ( nhấn vào góc trên bên trái bảng tính)

B2: Home / Format / Format cell / hiển thị hộp thoại ( hình 1)

+ Chọn thẻ Protection

+ Bỏ chọn Locked / Ok

B3: Chọn vùng công thức cần bảo vệ

Hướng dẫn chọn nhanh: Vào Home / Chọn Find & Select / Chọn Go to Special

=> Xuất hiện hộp thoại

+ Chọn Formulas / OK

B4: Vào Home / Format / Format cell / Xuất hiện hộp thoại ( hình 1 ) / chọn thẻ Protection

+ Chọn lại Locked

+ Chọn Hidden

=> OK

B5: Vào Thẻ REVIEW / PROTECT SHEET / xuất hiện hộp thoại( Hình 2 )

+ Tại Password to unprotect sheet: thiết lập Password

+ Allow all user of this worksheet to: Chọn định dạng không muốn khóa

 => OK => Thiết lập Password lại lần nữa =>OK

3.  Thiết lập bảo vệ bảng tính

– Vào Review / Protect Workbook / Xuất hiện hộp thoại

+ Structure:  Không đổi tên,  di chuyển,  chèn, xóa …

+ Windows:  Không thay đổi kích thước, vị trí bảng tính

+ Password: Nhập password => OK => Nhập lại password => OK

4.  Thiết lập hàm tự định nghĩa ( add-in)

Mục đích tích hợp thêm các hàm tự định nghĩa bên ngoài vào EXCEL, các hàm này được xây dựng dựa trên ngôn ngữ VBA trong OFFICE, ví dụ để chuyển đổi số thành chữ trong các hóa đơn, thì ta cần xây dựng hàm vnd, rồi tích hợp hàm này vào Excel để xử dụng

B1: Xây dựng tập tin dạng VBA có dạng mở rộng xlam/xla ( có thể tìm kiếm trên internet)

B2: Mở Excel / File ( hoặc biểu tượng Office) / option ( excel option ) / xuất hiện hộp thoại / chọn thẻ Add-ins /

=> Chọn vào Go / xuất hiện hộp thoại / 

=> Chọn vào Browse.. / tìm đến tập tin / Ok / OK

III. KẾT XUẤT DỮ LIỆU

Mục đích chức năng này để đưa dữ liệu từ nhiều dạng khác bên ngoài vào bảng tính để tiếp tục xử lý, ở đây Phương Nam sử dụng là tập tin.

+ Vào Thẻ Data  => Tại Nhóm Get External Data

=> Chọn Existing Connections => xuất hiện hộp thoại

=> Chọn Browse  for more…( tìm đến file cần lấy dữ liệu) => chọn open => xuất hiện hộp thoại

=> Tiếp tục thực hiện từng bước theo hướng dẫn chương trình

IV. ĐỊNH DẠNG BẢNG TÍNH

1.  đặt tên vùng

B1: Chọn vùng cần đặt tên

B2: Nhấn chuột phải lên vùng vừa chọn / chọn Name a range ( define name ) / Xuất hiện hộp thoại

B3: Tại Name: đặt tên vùng / OK

Chú ý: để thay đổi tên vùng hoặc xóa tên vùng vừa đặt  nhấn tổ hợp phím ctrl + f3 hoặc vào Formulas / chọn Name manager

2.  Thiết lập liên kết

B1: Đặt con trỏ tại ô cần thiết lập liên kết

B2: Vào INSERT / Chọn HYPERLINK / Xuất hiện hộp thoại

+ Existing file or web page: thiết lập liên kết đến tập tin hoặc đến địa chỉ web

+ Place in this document: Liên kết đến 1 thành phần của chính tập tin như: đến tên vùng, đến 1 sheet,…

+ Create New document: Mở mới 1 tập tin

+ E-mail Address: Thiết lập đến địa chỉ email

=> OK

3.  Một số hàm thường dùng

a. Hàm lấy giá trị lớn thứ K (LARGE)

CP=LARGE( array, k)

GT: array: vùng giá trị

k: thứ k

Công dụng: Hàm lấy giá trị lớn thứ k trong vùng (array)

VD:  cho vùng dữ liệu

=LARGE(A1:A6, 3)=7

b. Hàm lấy giá trị nhỏ thứ K (SMALL)

CP=SMALL( array, k)

Công dụng: Hàm lấy giá trị nhỏ thứ k trong vùng (array)

VD:  lấy ví dụ trên

=SMALL(A1:A6, 3)=6

c. Hàm kiểm tra lỗi ( ISERROR / ISNA)

CP=ISNA(value)

Gt: value: là giá trị

Công dụng: hàm trả về true(đúng) thì value lỗi, ngược lại hàm trả về false(sai)

d. Hàm kiểm tra lỗi ( IFERROR)

CP=IFERROR(value, value_if_error)

Gt: value: là giá trị

value_if_error: giá trị thay thế khi value lỗi

Công dụng: hàm trả về value nếu value không phải là lỗi, ngược lại hàm trả về giá trị value_if_error.

e. Hàm EDATE

CP=EDATE(start_date, months)

Gt: start_date: thời gian bắt đầu

months: số tháng

Công dụng: hàm trả về thời gian kết thúc tương ứng thời gian bắt đầu cộng thêm số tháng

VD: =EDATE(“28/06/2018”, 4) hàm trả về: 28/10/2018 

f. Hàm COLUMN / ROW

CP =COLUMN() / ROW()

Công dụng: hàm trả chỉ số cột, dòng

g. Hàm trả về vùng tham chiếu ( INDIRECT )

CP =INDIRECT( ref_text, [a1] )

GT: ref_text: chuỗi tên vùng

[a1]: kiểu địa chỉ mặc định A1, nếu muốn sử dụng kiểu R1C1, chuỗi ref_text phải có dạng R1C1

Công dụng: hàm trả vùng tham chiếu, vùng tham chiếu đó có tên bằng chuỗi ref_text.

Chú ý: Trước khi sử dụng hàm này thì cần phải đặt tên vùng

VD:  cho vùng sau và đặt tên vùng ” Vung_1″ (B4:E10)

=INDIRECT(“VUNG_1”) Kết quả hàm trả về là  B4:E10

4.  định dạng có điều kiện

a. Định dạng theo mẫu

B1: Chọn vùng cần định dạng

B2: Vào Home / Conditional formatting / hiển thị danh sách 

B3: Từ danh sách các kiểu định dạng điều kiện theo mẫu => chọn 1 mẫu phù hợp

b. Định dạng riêng

B1: Chọn vùng cần định dạng

B2: Vào Home / Conditional formatting / New rule / hiển thị hộp thoại

Hình A

Tại Select a rule type: chọn 1 dạng định dạng mong muốn

+ Format only cell that contain : định dạng từng ô

+ Format only top or bottom ranked values : định dạng từng ô theo giá trị lớn nhất / hoặc nhỏ nhất trong vùng chon

+ Format only values that are above or below average : định dạng từng ô giá trị lớn hơn hoặc nhỏ hơn giá trị trung bình vùng chọn

+ Format only unique or duplicate values : định dạng từng ô giá trị di nhất hoặc giá trị trùng lặp trong vùng chọn

+ Use a formula to determine which cells to format : định dạng cả dòng cho những dòng thỏa điều kiện thiết lập

B3: Chọn 1 trong các dạng định dạng trên  => thiết lập điều kiện định dạng => chọn chọn định dạng(format) phù hợp

Ví dụ: cho bảng dữ liệu sau

1. Format only cell that contain để định dạng :Tô màu nền vàng cho ô có đơn giá  bằng 5500

B1: Chọn vùng Đơn giá

B2: Home / Conditional formatting / New rule / hộp thoại (Hình A)

B3: Chọn format only cell that contain / xuất hiện hộp thoại

Thiết lập như hình => chọn format / xuất hiện hộp thoại 

Hình B

=> chọn định dạng phù hợp: ở ví dụ này chọn thẻ fill / chọn màu vàng => ok =>ok => kết quả

2. Use a formula to determine which cells to format :Tô màu nền vàng cho những dòng có đơn giá  bằng 5500

B1: Chọn vùng A3:E23

B2: Home / Conditional formatting / New rule / hộp thoại (Hình A)

B3: Chọn Use a formula to determine which cells to format / xuất hiện hộp thoại

Tại: format values where this formula is true: tại ô này thiết lập điều kiện đúng, ở ví dụ này =$D3=5500 => chọn format => hộp thoại Hình B => chọn định dạng phù hợp

=> kết quả hình sau:

Chú ý: Để quản lý các điệu kiện và định dạng đã thiết lập Vào Home / Conditional formatting / Manager rules / Xuất hiện hộp thoại sau:

Từ hộp thoại này ta điều chỉnh phù hợp

V. KIỂM TRA DỮ LIỆU VÀ TẠO DANH SÁCH NHẬP

1. Ràng Buộc dữ liệu nhập ( Validation)

B1: Chọn vùng dữ liệu cần ràng buộc

B2: Vào Data / Data validation / xuất hiện hộp thoại

Hình C

B3: Thiết lập ràng buộc:

+ Thẻ Setting

Tại Validation criteria – Allow: Chọn Kiểu ràng buộc dữ liệu nhập vào

– Any value: mọi giá trị ( không ràng buộc)

– Whole number: Kiểu số nguyên

– Decimal: Kiểu số thập phân

– List: Kiểu danh sách

– Date / Time : Kiểu thời gian

– Text lenght: Qui định chiều dài chuỗi nhập vào

– Custom: Tự thiết lập kiểu ràng buộc

+ Thẻ Input Message: Thiêt lập thông bào người dùng biết khi nhập liệu

Hình D

Tại Title: tiêu đề thông báo

Tại Input message: nội dung thông báo

+ Thẻ Error Alert: thiết lập thông báo khi có lỗi nhập liệu

Hình E

Tại Style: Chọn Kiểu hộp thoại thông báo

Tại Title: tiêu đề thông báo

Tại Error message: nội dung thông báo

=> OK

VD: Giả sử trong bảng tính ta có cột số lượng, yêu cầu người dùng nhập vào cột này phải là số nguyên >=0

B1: Chọn vùng số lượng

B2: Data / Data validation / hộp thoại ( Hình C)

B3: Tại thẻ Setting – allow / Chọn whole number 

Tại Data: chon greater than or equal to

Tại Minimum: nhập số 0

+ Thẻ Input message: thiết lập thông báo nhập

+ Thẻ Error alert: thiết lập thông báo khi có lỗi nhập

2. Tạo data list nhập liệu

B1: Chọn vùng cần tạo datalist

B2: Data / data validation / hộp thoại hình c

B3: Thẻ setting – allow / chọn List / 

Tại Source: nhập danh sách muốn tạo list VD:NO, CK, TM / hoặc chọn vùng dữ liệu cần lấy để tạo list

VD: TẠO LIST ĐÊ NGƯỜI DÙNG CHỌN HÌNH THỨC THANH TOÁN ( NO, CK, TM)

 

———————————hết Phần II – Bài 1———————————-