MỘT SỐ THAO TÁC VỀ BẢNG TÍNH VÀ MỘT SỐ HÀM THƯỜNG DÙNG TRONG EXCEL KẾ TOÁN docx

Bài giảng

EXCEL KẾ TOÁN

PHẦN I:

MỘT SỐ THAO TÁC VỀ BẢNG TÍNH VÀ MỘT SỐ HÀM THƯỜNG DÙNG TRONG

EXCEL KẾ TOÁN

CHƯƠNG I: ĐỊNH DẠNG DỮ LIỆU

I. Định dạng lại các thông số thể hiện số trong excel bằng chương trình control panel.

Khởi động Windows:

- Chọn menu start/setting/Control Panel

(Hoặc chọn Start/Run, gõ tên file thi hành Control.exe vào, chọn OK).

- Định dạng cho tiền tệ với quy ước sử dụng:

Trong Control Panel chọn Regional and language options, chọn customize, chọn Currency,

định dạng các thông sô như sau:

+ Negative number format: “-“ (dấu “-“ biểu diễn dấu âm)

+ Currency Symbol: “Đồng”

+ Decimal Symbol: “,” (dấu phân cách thập phân là dấu “,”).

+ Digit grouping symbol: “.” (dấu phân cách hàng nghìn là dấu “.”)

+ Number of digits in group: 3 (số lượng phân cách nhóm hàng nghìn là 3 số).

- Định dạng số (Number) với quy ước sử dụng:

Trong Control Panel chọn Regional and language options, chọn customize, chọn number,

định dạng các thông số như sau:

+ Negative number format: “-“ (dấu “-“ biểu diễn dấu âm)

+ Decimal Symbol: “,” (dấu phân cách thập phân là dấu “,”).

+ Digit grouping symbol: “.” (dấu phân cách hàng nghìn là dấu “.”)

+ No. of digits after group: 2 (số chữ thập phân là 2 chữ số)

+ Number of digits in group: 3 (số lượng phân cách nhóm hàng nghìn là 3 số).

+ List Separator: “,” (dấu phân cách trong công thức toán học)

II. Định dạng khi nhập dữ liệu

Có thể định dạng dữ liệu ngay khi nhập dữ liệu vào bảng tính hoặc có thể nhập xong dữ liệu

vào bảng tính rồi mới định dạng lại dữ liệu.

1. Dữ liệu kiểu số:

Nếu bắt đầu nhập một số với dấu cộng (+), Excel bỏ dấu cộng khi đưa dữ liệu vào bảng tính.

Nhưng nếu nhập một số với dấu trừ (-), Excel diễn giải mụcnhập như là một số âm và giữ dấu này

trên bảng tính. Có thể sử dụng các ký hiệu E hay e để nhập một số theo dạng khoa học.

Ví dụ: Khi muốn nhập số 30.000.000, có thể nhập 3E07 (tức là 3 x 107

).

Excel diễn giải các số được đặt trong ngoặc đơn như là các số âm. Điều này thường áp dụng

trong nghiệp vụ kế toán.

Ví dụ: Khi nhập (1000) thì Excel hiểu rằng đó là số -1000.

Khi nhập dữ liệu kiểu số, có thể dùng dấu phẩy để tách ra hàng nghìn, triệu cũng như có thể

sử dụng dấu chấm (.) để phân cách với hàng thập phân.

Nếu bắt đầu nhập mộ số với dấu đô la ($), Excel chia phần định dạng cho ô.

Ví dụ: khi nhập số $1234567. Excel hiển thị: $1,234,567. Trong trường hợp này, excel tự

động thêm dấu phẩy vào các vị trí hàng triệu và hàng nghìn vì đây là phần định dạng ngầm định về

tiền tệ của Excel.

Khi nhập vào một số mà độ dài của nó nhiều hơn độ rộng của một ô thì Excel đành chuyển nó

sang dạng khoa học như ví dụ trên. Nhưng nếu ngay cả trong trường hợp này cũng không đủ vị trí,

Excel sẽ đưa các dấu sao (*) vào trong các ô. Nếu mở rộng độ rộng của ô, các giá trị thực tế của nó

sẽ trở lại.

2. Các chuỗi văn bản

Khi nhập một chuỗi văn bản có dạng số.

Ví dụ như một mã vật tư có mã số là 0123, cần tiến hành theo hai cáh sau:

=”123”

‘0123

Giá trị 0123 được đưa vào trong ô tương ứng và được hiểu là dạng ký tự. Nếu nhập theo

dạng số thì số không (0) đầu tiên sẽ bị loại bỏ vì nó hoàn toàn vô nghĩa và như vậy thì không giữ

đúng được mã số của vật tư tương ứng.

Để chỉnh dữ liệu nhập vào sang trái, thêm một dấu nháy (‘) ở trước dữ liệu nhập vào. Để

chỉnh dữ liệu sang phải, thêm hai nháy (“) và để chỉnh giữa dữ liệu nhập vào, thêm dấu mũ (^) ở

trước dữ liệu nhập vào.

III. Định dạng dữ liệu kiểu số

Sau khi nhập dữ liệu và tính toán để đưa ra các thông tin cần thiết, cần phải trình bày các

bảng tính (dữ liệu và kết quả) sao cho phù hợp với yêu cầu. Đối với dữ liệu kiểu số, phải lựa chọ

cách thể hiện số liệu, chọn đơn vị tính, thay đổi kiểu chữ, nhấn mạnh những vị trí quan trọng (chẳng

hạn như các kết quả thì in đậm, hay in nghiêng) …

Để thực hiện các nội dung trên, cần tiến hành các phương pháp sau:

1. Dùng bảng chọn

Một số khi nhập vào một ô được tự động chỉnh phải và được hiển thị phụ thuộc vào hai thành

phần: nhóm và dạng.

Ví dụ: các nhóm gồm Number, Date, Time…

Trong nhóm date lại có dạng: MM/DD/YY, DD/MM/YY…

Để định dạng hiển thị dữ liệu kiểu số khi dùng bảng chọn, tiến hành các bước như sau:

- Chọn vùng dữ liệu cần định dạng dữ liệu kiểu số.

- Vào bảng chọn Format.

- Chọn cells

Khi này một bảng chọn hiện ra, chọn Number. Hộp thoại Format Cells xuất hiện như sau:

Sau đó hãy chọn dạng dữ liệu cần thể hiện trong khung Negative Numbers. Chọn số chữ số

thập phân trong khung Decimal Places. Chọn nút Use 100 Separator (,) để có dấu phẩy phân cách

hàng nghìn, triệu…

2. Dùng biểu tượng trên Formating Toolbar.

Chọn biểu tượng dấu đô la ($) để biểu diễn số kiểu tiền tệ.

Ví dụ: 1,234.56 chuyển thành: $1,234.00

Chọn biểu tượng phần trăm (%) để biểu diễn dữ liệu kiểu phần trăm (%)

Ví dụ: 123456

Chuyển thành 12345600%

Chọn biểu tượng dấu phẩy (,) để biểu diễn dữ liệu kiểu phân nhóm giữa hàng nghìn, hàng

triệu bằng dấu phẩy.

Ví dụ: 12345 chuyển thành 12,345.

Biểu tượng để tăng phần lẻ thập phân một chữ số 0 hoặc lấy lại phần thập phân cũ nếu

bạn đã giảm đi bằng biểu tượng (dưới đây gọi là biểu tượng tăng).

Biểu tượng để giảm đi một số thập phân sau khi đã làm tròn số theo đúng nguyên tắc làm

tròn số (dưới đây gọi là biểu tượng giảm).

Ví dụ:

1,234.56. Nếu nhấn chuột ở biểu tượng tăng, sẽ được kết quả: 1,234.560.

Nếu nhấn biểu tượng giảm, sẽ được kết quả: 1,234.56. Nếu nhấn biểu tượng giảm một lần

nữa, kết quả sẽ là: 1,234.6 (đã làm tròn số theo đúng nguyên tắc làm tròn số.

CHƯƠNG II: ĐỊNH DẠNG WORKSHEET

I. Định dạng Font chữ

Cách 1:

- Chọn vùng muốn định dạng.

- Vào menu Format, chọn Font. Hộp hội thoại Format cells xuất hiện, chọn tab Font.

- Chọn các thuộc tính Font cần thiết.

 Font: danh sách tên các font

 Font style: kiểu font.

• Normal, Regular: kiểu chuẩn

• Italic: kiểu nghiêng

• Bold: kiểu đậm

• Bold – Italic: kiểu vừ nghiêng vửa đậm.

 Size: kích thước font chữ

 Underline

• None: xóa chọn gạch dưới.

• Single: gạch dưới nét đơn.

• Double: gạch dưới nét đôi.

• Single Accounting: tác động lên toàn bộ ô nếu ô chứa văn bản, chỉ gạch dưới (nét đơn) số

nếu ô chứa số. Nếu ô có dấu $ sẽ không gạch dưới dấu $.

• Double Accounting: tác động lên toàn bộ ô nếu ô chứa văn bản, chỉ gạch dưới (nét đôi) số

nếu ô chứa số. Nếu ô có dấu $ sẽ không gạch dưới dấu $.

 Color: bảng màu được mở ra để bạn chọn màu cho màu của ký tự trong cả ô.

 Effects:

• Strikethrough: gạch ngang giữa chữ.

• Superscript: chỉ số trên.

• Subscript: chỉ số dưới.

 Normal Font: lấy các thuộc tính của Font chữ mặc nhiên.

 Preview Window: cho thấy hình mẫu của các thuộc tính được chọn.

- Chọn OK

Cách 2: Chọn vùng muốn định dạng

- Click vào nút Font để chọn font chữ.

- Click vào Font size để chọn kích thước font chữ.

- Click vào nút Bold để chọn kiểu đậm. Click trở lại vào nút Bold để bỏ chọn kiểu đậm.

- Click vào nút Italic để chọn kiểu nghiêng. Click trở lại vào nút Italic để bỏ chọn kiểu nghiêng.

- Click vào nút Underline để chọn kiểu gạch dưới. Click trở lại vào nút Underline để bỏ chọn kiểu

gạch dưới.

- Click vào nút Font Color để chọn màu của ký tự. Click trở lại vào nút Font Color, chọn

Automatic để bỏ chọn màu của ký tự.

II. Kẻ khung

Cách 1

- CHọn vùng cần kẻ khung

- Vào menu Format, chọn Cells, Chọn tab Border.

- Style: Chọn hình thức của các đường kẻ.

- Color: Chọn màu của các đường kẻ.

- Border: chọn các kiểu border cần tạo.

- Preset

 None: Xoá border

 Inside: tạo đường chung quanh vùng được chọn.

 Outline: tạo đường giữa các ô

Cách 2

- Chọn vùng muốn định dạng

- Click vào nút Border, sau đó chọn dạng khung mà bạn cần tạo.

III. Định vị trí dữ liệu

Cách 1

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

- Vào menu Format, chọn Cells, chọn Alignment.

- Horizontal: canh ký tự trong ô theo chiều ngang.

 General: theo mặc định, văn bản canh trái, số canh phải.

 Left: canh dữ liệu trong ô sang trái

 Right: canh dữ liệu trong ô sang phải

 Fill: điền đầy các ô được chọn.

 Justify: dàn điều theo chiều ngang của ô (tự động xuống dòng).

 Center Acrss Selection: canh giữa nhóm ô đã chọn.

- Vertical: canh ký tự trong ô theo chiều dọc.

 Top: nội dung trong ô nằm sát vào phía đỉnh của ô.

 Center: canh giữa theo chiều dọc.

 Bottom: nội dung trong ô nằm sát vào phía đáy của ô.

 Justify: dàn đều theo chiều đứng của ô và tự động xuống dòng.

- Wrap text: cắt ngắn các dòng chữ dài cho phù hợp với độ rộng cột. Một dòng có thể được cắt

thành nhiều dòng ngắn hơn vừa với độ rộng của cột. Chiều cao của dòng tự động điều chỉnh để phù

hợp với nhiều dòng chữ.Shrink to fit: làm thay đổi size của font, co kéo chữ cho vừa ô.

- Merge cells: ghép nhiều ô thành 1 ô, mà không làm thay đổi chiều cao dòng hay độ rộng cột.

- Orientation: xoay văn bản trong ô. Bạn có thể làm thay đổi hướng của văn bản từ -900 đến

+900 so với hướng nằm ngang. Chọn 1 trong 2 cách sau:

 Click chuột và kéo kim chỉ hướng

 Chọn chỉ số góc trong Degrees

Cách 2:

- Chọn vùng muốn định dạng.

- Click vào nút Left để canh trái.

- Click vào nút Center để canh giữa.

- Click vào nút right để canh phải.

- Click vào nút Merge and center để ghép nhiều ô thành 1 ô.

CHƯƠNG III: MỘT SỐ THAO TÁC VỚI BẢNG TÍNH

I. Các thao tác liên quan đến dòng cột, worksheet

1. Chèn thêm column vào bảng tính

+ Đặt con trỏ vào cột muốn chèn.

+ Ra lệnh Insert / Column.

Kết quả: Cột chèn thêm sẽ chèn vào vị trí trước cột hiện tại.

2. Chọn thêm Row vào bảng tính

+ Đặt con trỏ vào dòng muốn chèn.

+ Ra lệnh Insert / Row.

Kết quả: dòng chèn thêm sẽ chèn vào vị trí trước dòng hiện tại

3. Chèn thêm Sheet mới vào work book

+ Ra lệnh Insert / WorkSheet.

4. Đặt tên cho Work Sheet

+ Chọn sheet muốn đặt tên là sheet hiện hành

+ Format/Rename (hoặc double click tại phần tên sheet)

+ Gõ tên worksheet vào.

5. Dấu hoặc hiện Column, Row, Worksheet

Chọn Row hay Column, Worksheet muốn dấu đi.

+ Ra lệnh Format / (Row, Column ahy sheet tương ứng)

+ Chọn Hide

Chọn Row hay Column, Worksheet muốn hiện ra.

+ Ra lệnh Format / Row hay Column

+ Chọn Unhide

6. Copy công thức và giá trị

Chọn vùng chứa công thức hay giá trị cần copy.

+ Ra lệnh : Ctrl + C hay Edit / Copy

+ Chọn địa chỉ cần Copy đến

+ Ra lệnh: Ctrl + V hay Edit / Paste

7. Copy kết quả công thức hay giá trị

Chọn vùng chứa công thức hay giá trị cần copy.

+ Ra lệnh : Ctrl + C hay Edit / Copy

+ Chọn địa chỉ cần Copy đến

+ Ra lệnh Edit / Paste Special / Value.

Hay Click phải chuột chọn Paste Special / Value.

8. Mặc định Font VNI-TIMES cho toàn bảng tính

+ Ra lệnh Tools/Options/General

+ Chọn font VNI-TIMES, size 12 trong khung Standard Font.

+ Thoát khỏi excel, sau đó khởi động lại.

II.

CHƯƠNG IV: LẬP CÔNG THỨC VÀ HÀM

I. Tham chiếu

Tham chiếu tuyệt đối: chỉ đến 1 ô hay các ô cụ thể. Tham chiếu tuyệt đối không thay đổi khi

được sao chép sáng các ô khác. Công thức: $CỘT$DÒNG

Tham chiếu tương đối: chỉ đến 1 ô hay các ô trong sự so sánh với 1 vị trí nào đó. Công thức

CỘTDÒNG.

Tham chiếu hỗn hợp: Một thành phần là tham chiếu tuyệt đối, thành phần kia là tham chiếu

tương đối. Công thức $CỘTDÒNG,CỘT$DÒNG.

II. Các toán tử số học trong công thức

+ (cộng), - (trừ), * (nhân), / (chia), ^ (luỹ thừa), % (phép lấy phần trăm khi đặt sau 1 con số)

III. Hàm trong Worksheet

1. Cú pháp chung các hàm: = TÊN HÀM(CÁC THAM BIẾN)

Công thức trong Excel có các tính chất cơ bản sau:

- Mọi công thức bắt đầu bằng dấu =

- Nhập xong công thức, trong ô sẽ xuất hiện trị kết quả.

- Khi chọn ô có chứa công thức, thanh công thức sẽ xuất hiện bản thân công thức đó.

2. Các hàm số thường dùng trong kế toán excel.

a. Nhóm hàm số (Abs, Int, Mod, Round, Sqrt, Power)

Abs(number): trả ra giá trị tuyệt đối một số number.

Ví dụ: =ABS(2)  2, ABS(-2)  2, ABS(-9)  9

Int(number): trả ra phần nguyên của một số

Ví dụ: Int (2.99)  2

Mod(number, divisor): trả ra phần dư của phép chia nguyên

Ví dụ: Mod(5,3)  2

Round(number,n): làm tròn số number theo con số được chỉ định.

n>0 thì số làm tròn đến vị trị thấp phân được chỉ định.

n<0 thì số làm tròn đến bên trái dấu thập phân.

n=0 thì số làm tròn đến phần nguyên gần nhất.

Ví dụ: Round(12.34567,2)  12.35

Round(12335.67,-2)  12300

Round(12.33567,2)  12.34

Round(12335.67,-3)  12000

Sqrt(number): trả ra căn bậc hai của 1 số.

Ví dụ: SQRT(4)  2

Power(number,power): trả ra lũy thừa của 1 số.

Ví dụ: POWER(3,2)  9

b. Các hàm xử lý chuổi

b1 Hàm Left ( )

- Cú pháp: Left ( Chuổi, n )

+ Chuổi là chuổi một văn bản hoặc một địa chỉ ô chứa giá trị chuổi.

+ n : số ký tự bên trái được lấy ra từ chuổi .

Ví dụ 1:

A B C

1 Van Lang

2 SV009201

Tại ô B1 =Left (A1,3) ⇒ Van

Tại ô B2 =left (A2,6) ⇒ SV0092

b2 Hàm Right ( )

- Cú pháp: Right ( Chuổi, n )

+ Chuổi là chuổi một văn bản hoặc một địa chỉ ô chứa giá trị chuổi.

+ n: số ký tự bên phải được lấy ra từ chuổi.

Ví dụ: lấy lại ví dụ 1 ở trên

Tại ô B1 =Righ (A1,4) ⇒ Lang

Tại ô B2 =Right(A2,6) ⇒ 009201

b3 Hàm Mid ( )

- Cú pháp: Mid( Chuổi,n ,m )

+ Chuổi là chuổi một văn bản hoặc một địa chỉ ô chứa giá trị chuổi.

+ n: Vị trí cần lấy ra

+ m: Số ký tự lấy ra

Ví dụ: lấy lại ví dụ 1 ở trên

Tại ô C1 = Mid(A1,5,3) ⇒ Lan

Tại ô C2 = Mid(A1,3,4) ⇒ 0092

b4. Hàm trim

- Cú pháp: Trim(text)

Công dụng: bỏ tất cả các khoảng trắng dư thừa trong chuỗi text.

b5. Hàm Lower

- Cú pháp: Lower(text)

Công dụng: chuyển tất cả các ký tự của chuỗi text ra chữ thường.

b6. Hàm Upper

- Cú pháp: Upper(text)

Công dụng: chuyển tất cả các ký tự của chuỗi text ra chữ hoa.