BAI_GIANG__EXCEL_KE_TOAN pdf

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.

b6. Hàm Proper(text)

- Cú pháp: Proper(text)

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

b7. Hàm Value

- Cú pháp: Value(text)

Công dụng: đổi chuỗi số ra số.

c. Các hàm thống kê

c1 Hàm Sum()

- Cú pháp Sum(n1,n2, . . . )

=> Công dụng: Dùng để tính tổng các ô chứa giá trị.

Ví dụ:

A B C D

1 Thu L1 Thu L2 Thu L3 Tổng cộng

2 150 250 500

3

Tại ô D2 = sum(A2:C2) ⇒ 900

c2 Hàm Count()

- Cú pháp Count(n1,n2, . . . )

=> Công dụng: Dùng để đếm các ô chứa giá trị.

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

Tại ô A1 = Count(A2:C2) ⇒ 3

c3 Hàm Average()

- Cú pháp Average(n1,n2, . . . )

=> Công dụng: Dùng để tính giá trị trung bình.

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

Tại ô A1 = Average(A2:C2) ⇒ 300

c4 Hàm Max()

- Cú pháp Max(n1,n2, . . . )

=> Công dụng : Dùng để lấy ra giá trị lớn nhất.

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

Tại ô A1 = Max(A2:C2) ⇒ 500

c5 Hàm Min()

- Cú pháp Min(n1,n2, . . . )

=> Công dụng: Dùng để lấy ra giá trị nhỏ nhất.

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

Tại ô A1 =Min(A2:C2) ⇒ 150

c6 Hàm Sumif()

- Cú pháp: Sumif(Khối chứa ĐK,Điều kiện,Khối tính tổng )

=> Công dụng: Dùng để tính tổng có điều kiện.

Ví dụ: Tình hình bán hàng trong kỳ của một của hàng điện tử có số liệu như sau:

Sheet 1:

A B C D