Học Excel bằng phương pháp QUAY HÌNH

[solomon2211]

Xin phép Salomon2211 cho tôi chuyển loạt bài này ra một topic riêng, vì tôi nhận thấy những bài này rất bổ ích.
Cũng xin thưa cùng các bạn, topic này, xin dành riêng cho anh Salomon2211, các bạn đừng post bài chen ngang vào. Xin cảm ơn.
BNTT.
---------------------------------------------------------------------------------------------------------------------------------



Bài 1: Excel là gì?

Excel là một chương trình ứng dụng phát triển bởi Microsoft, dựa trên chương trình Multiplan vào năm 1982. Để dễ nhớ tên chương trình, Microsoft dùng hai ký tự XL để nói về tên chương trình, nên biểu tượng của Excel có 2 ký tự trên, và phần mở rộng của tập tin là .xls

Mục tiêu chính của chương trình dùng để xử lý bảng tính nên còn gọi là spreadsheet.
Vào đầu năm 1993, thương hiệu Excel bị tranh chấp nhiều. Microsoft quyết định lấy tên là Microsoft Excel trên tất cả các thông tin báo chí.

Excel linh động về dòng và cột như một cuộn giấy mà bạn có thể kéo theo chiều ngang và chiều dọc.

Màn hình hiển thị của Excel là một bảng ô, được xác định vị trí tọa độ dòng cột.
Dòng được đánh số thứ tự từ 1 đến 65536 (đối với Excel 2003) = 65536 dòng
Cột được đánh chữ từ A đến IV = 256 cột

Mỗi ô có tọa độ theo dòng, cột như A5 (cột 1 dòng 5); hoặc R1C5 (trường hợp hiển thì cột bằng số).

------------------
Bài tiếp theo (Bài số 2): Nhập môn và khởi động Excel

 

[solomon2211]

Bài số 2: Nhập môn và khởi động Excel

I. Tổng quan:
1. Định nghĩa cell (ô): Cell hay ô là giao điểm của dòng và cột, là nơi trên bảng tính sẽ thể hiện những gì chúng ta nhập vào hoặc tính toán ra. Cell thường được ký hiệu dòng và cột. Ví dụ: Dòng 5 cột 3 thì ký hiệu là C5. Còn dạng ký hiệu trực diện là R5C3 (Row 5, Column 3).

2. Bắt đầu sử dụng Excel: Khởi động chương trình Excel bằng cách tìm biểu tượng Excel và nhấn Enter (yêu cầu đã cài đặt Excel)

3. Màn hình đầu tiên: Là một vùng trống trên màn hình, được kẻ ô mờ. Phía trên là dãy ký tự theo hàng ngang A, B, C,... là ký hiệu cột. Dọc bên trái là dãy số 1, 2, 3, .... là ký hiệu dòng. Bên dưới hàng ngang la những thẻ (tab) chỉ tên và số lượng bảng tính (sheet).

4. Khi khởi động Excel, tập tin được gọi là workbook, tên khởi đầu là book1. Các bảng tính có tên là sheet1, sheet2, sheet3, ... Bạn có thể sửa tên sheet theo ý thích, và tên tập tin khi lưu tập tin. Bạn có thể thêm bao nhiêu bảng tính tùy ý bạn. Bạn cũng có thể xóa bớt hoặc di chuyển vị trí các bảng tính nữa.

5. Để tạo thêm 1 workbook mới, chọn vào menu File > New > Blank Workbook hoặc bấm Ctrl-N.

Toàn bộ bảng tính bạn có 256 cột x 65,536 dòng = 16,777,216 ô (cell). Ô được chọn sẽ có đường viền xung quanh, tiêu đề cột và dòng sẽ đổi màu.

Góc trái trên là nơi hiển thị địa chỉ (tọa độ) ô, gọi là hộp hiển thị tên (Name Box)

Sau khi tạo bảng tính, muốn lưu lại: Chọn File > Save (Shift+F12) hoặc Save As (F12) rồi nhập tên cần lưu. Chọn nơi cần lưu và chọn OK.

Save: Dùng cho trường hợp có hoặc chưa đặt tên.
Save As: Dùng cho trường hợp chưa đặt tên hoặc muốn lưu thành tên mới.


II. Cách nhập liệu:

1. Nhập liệu:
Bạn có thể nhập liệu ở bất cứ ô nào trong bảng tính. Dữ liệu nhập vào có thể là số hoặc chữ. Bạn nên bắt đầu nhập tiêu đề cho dòng và cột để biết được ý nghĩa của các dữ liệu nhập vào.

Sau khi nhập liệu, bạn nhấn các phím di chuyển để di chuyển qua các ô khác. Mặc nhiên phím Tab để qua phải, Enter để di chuyển xuống.

+ Để nhập dạng ngày, bạn dùng dấu / hoặc dấu -. Ví dụ: 20/12/2007 hoặc 20-12-2007.
+ Để nhập giờ, bạn nhập giờ, sau đó gõ khoảng trắng và gõ "a" hoặc "p" để xác định sáng hay chiều.

Phím tắt: Để nhập ngày hiện tại: Ctrl-;
Để nhập giờ hiện tại: Ctrl+Shift+;
+ Nhập số: nhập từ bàn phím. Nhập số âm bằng cách thêm dấu trừ phía trước hoặc đặt số cần nhập trong dấu ngoặc ()
+ Nhập phân số: nhập số 0 rồi khoảng trắng sau đó nhập phân số. Ví dụ 0 1/4 nếu không sẽ ra 1-Apr (ngày).

2. Sửa chữa dữ liệu:
Bạn có thể sửa chữa dữ liệu bằng cách đế ô cần sửa, nhấn F2 (hoặc dùng chuột nhấp đôi) rồi sửa.
Bạn có thể thêm hoặc bớt dòng, cột, cell bằng cách chọn ô cần thêm hoặc bớt dòng, cột. Nhấp phím phải chuột và chọn Insert hoặc delete.

3. Định dạng ô: Chọn ô cần định dạng (có thể định dạng từng bộ phận của cell bằng cách chuyển sang chế độ chỉnh sửa (F2) rồi nhấn giữ Shift+mũi tên di chuyển để chọn)
- Định dạng kiểu chữ: Chọn Menu Format Cell > Fonts > Chọn Kiểu chữ, màu, cỡ, ... sau đó nhấn OK (hoặc bấm các nút lệnh trên thanh công cụ)
- Định dạng dữ liệu: Chọn Format Cell > Number và chọn kiểu: General (chung), Number (số), Currency (tiền tệ), Accounting (kế toán), Date (ngày), Time (giờ), Percentage (phần trăm), Fraction (phân số), Scientific (khoa học), Text (ký tự), Special (đặc biệt), Custom (tự do)
- Định dạng khung: Chọn Format Cell > Border
- Định dạng tô màu: Chọn Format Cell > Fill
- Định dạnh sắp xếp ký tự: Chọn Format Cell > Alignment

---------------
Bài tiếp theo (Bài 3): Cách nhập công thức và sử dụng hàm

 

[solomon2211]

Bài 3: Cách nhập công thức và sử dụng hàm

Trong các bài trước, bạn đã biết thế nào là Excel và các nhập dữ liệu cùng các định dạng cơ bản cho bảng tính của bạn.

Trong bài này, bạn sẽ tiếp tục tìm hiểu cách nhập công thức và sử dụng hàm.

1. Các phép tính: Như một máy tính tay, bạn sử dụng các toán pháp sau: cộng (+); trừ (-); nhân (*); chia (/); lũy thừa (^). Ví dụ: 3 lũy thừa 2 = 3*3 = 3^2

2. Khởi đầu một công thức: Để bắt đầu 1 công thức, bạn phải nhập dấu bằng (=); hoặc dấu cộng (+) hoặc dấu trừ (-). Ví dụ để nhập công thức 3 nhân 5, bạn nhập =3*5

Trong công thức, bạn có thể xác định phép tính tính trước ngoài phép tính mặc nhiên (lũy thừa; nhân chia trước; cộng trừ sau) bằng cách dùng dấu ngoặc (). Ví dụ: (3+2)*5 = 3*5+2*5

3. Tham chiếu ô: Mỗi ô có địa chỉ là tọa độ dòng cột. Trong công thức: chúng ta dùng địa chỉ ô (sau đây gọi là ô) để thể hiện trong công thức.

+ Ô có vị trí tương đối; tuyệt đối và bán tuyệt đối.
Vị trí tương đối: là vị trí thay đổi theo số dòng hoặc cột. Được thể hiện dạng A5. Áp dụng cho trường hợp tính kết quả cho từng dòng, cột với dữ liệu khác nhau giữ dòng, cột.

Ví dụ:
A B C
1 Số lượng Đơn giá Thành tiền
2 15 20 =A2*B2
3 24 20 =A3*B3
4 8 20 =A4*B4
...

+ Ô có vị trí tuyệt đối: Là ô được cố định giá trị khi áp dụng cho các công thức khác dòng, cột. Được thể hiện bằng dấu $ trước dòng, cột. Ví dụ: $B$2

Ví dụ:
A B C
1 Số lượng Đơn giá Thành tiền
2 15 20 =A2*$B$2
3 24 20 =A3*$B$2
4 8 20 =A4*$B$2
...

+ Ô có vị trí bán tuyệt đối: Là ô được cố định dòng hoặc cột. Ví dụ: B$2; $B2

Trong khi nhập công thức, bạn có thể thay đổi vị trí tương đối <-> tuyệt đối <-> bán tuyệt đối dòng <-> bán tuyệt đối cột <-> tương đối bằng cách bấm tuần tự phím F4.

Trong công thức, chúng ta chọn địa chỉ đơn hoặc vùng địa chỉ.
+ Địa chỉ đơn: A1, B$5 hay $B3 v.v...
+ Vùng địa chỉ: là vùng liên tục có ô bắt đầu là ô trái trên, và ô kết thúc là ô phải dưới. Hai ô được cách nhau bởi dấu hai chấm ). Khi nhập, bạn có thể dùng dấu chấm (.) hoặc có thể chọn vùng như cách chọn nhiều ô dưới đây. Ví dụ: B15:E15

Để sao chép công thức hoặc giá trị của một ô đến vị trí khác, bạn dùng cách Copy/Dán
- Chọn ô/nhiều ô:
+ Chọn 1 ô: di chuyển bằng phím mũi tên hoặc dùng chuột chọn
+ Chọn nhiều ô (vùng): Chọn ô đầu tiên bên trái trên, hoặc bên phải dưới, nhấn giữ phím Shift và dùng phím mũi tên để chọn. Vùng chọn sẽ đổi màu. (hoặc dùng chuột bấm chọn ô đầu tiên, giữ và rê đến ô cuối)
- Copy: Chọn ô/vùng cần copy, nhấn Ctrl+C (hoặc chọn menu Edit/Copy; hoặc bấm chuột phải tại ô cần copy và chọn dòng menu Copy.
- Dán: chọn địa chỉ cần dán đến. Đối với dán 1 vùng, chỉ cần chọn ô bên trái trên vùng cần dán đến. Bấm Enter, hoặc Ctrl+V, hoặc chọn dòng Paste sau khi nhấn chuột phải, hoặc chọn Edit/Paste.

* Nếu ô/vùng cần dán đến nằm kề với ô/vùng nguồn, bạn rê chuột đến góc phải dưới của vùng cần copy đến khi hiện dấu +, bấm và rê chuột đến vị trí cần dán đến (không cần chọn lệnh Copy).
- Di chuyển ô: (Cách 1) Dùng lệnh Cut: Edit/Cut hoặc Ctrl+X, hoặc chọn dòng Cut sau khi nhấn chuột phải. (Cách 2) Rê chuột đến cạnh của ô/vùng cần di chuyển đến khi hiện mũi tên 4 chiều và bấm chuột rồi di chuyển.

(Còn tiếp)

Ghi chú: Sẽ có 1 bài về các phím tắt sử dụng trong Excel

---------------
Bài tiếp theo (Bài 3): Cách nhập công thức và sử dụng hàm (tt)
-------------

 

[solomon2211]

Bài số 3: Cách nhập công thức và sử dụng hàm (tt)

Trong bài này, xin giới thiệu các bạn cách sử dụng các hàm trong excel. Các hàm sẽ được giới thiệu lần lượt theo nhóm để các bạn dễ dàng áp dụng. Sau đó sẽ post thư viện hàm trong file excel để dễ dàng truy cứu sau này.

Đầu tiên, xin giới thiệu các toán pháp logic để các bạn có thể vận dụng.
+ Toán pháp giao (và): trong Excel sẽ dùng hàm AND
Kết quả hàm này sẽ cho kết quả chung của hai (hoặc nhiều) mảng.
Ví dụ: ta có hai hình tròn đặt chồng lên nhau, không khít. Kết quả phép giao của hai hình tròn là phần chung (xem hình minh họa).

Ví dụ khác: ta có mảng A bao gồm các số 1, 5, 7, 12, 15; mảng B gồm các số 5, 15, 16. Kết quả giao A và B cho ra kết quả là 5 và 15.

+ Toán pháp hợp (hoặc): trong Excel sẽ dùng hàm OR
Kết quả hàm này sẽ cho kết quả bao gồm tất cả các thành phần của hai (hoặc nhiều) mảng
Ví dụ: như ví dụ trên, nhưng kết quả là toàn bộ hai hình tròn (xem hình minh họa)
Ví dụ hai mảng A và B, ta có kết quả là 1, 5, 7, 12, 15 và 16.

* Các nhóm hàm số sẽ được trình bày theo thứ tự như sau:
1. Ngày
2. Ký tự
3. Toán học
4. Luận Lý
5. Cơ sở dữ liệu
6. Dò tìm
7. Tài Chính
8. Kỹ Thuật
9. Thống kê
10. Thông tin

-----------------
Bài tiếp theo (Bài 3): Cách nhập công thức và sử dụng hàm (tt)

 

[solomon2211]

Bài số 3: Cách nhập công thức và sử dụng hàm (tt)

I. CÁC HÀM VỀ THỜI GIAN

1. Hàm DATE:
1.1. Công dụng: Là hàm trả giá trị của một ngày.

1.2. Cú pháp:
DATE(year,month,day)
- year: là số chỉ năm
- month: là số chỉ tháng
- day: là số chỉ ngày

Ví dụ: DATE(99,11,30) = 30/11/1999

Nếu số chỉ ngày lớn hơn số ngày trong tháng, hàm sẽ trả về giá trị ngày tương ứng của tháng kế tiếp.

Ví dụ: DATE(99,12,33) = 02/01/2000

1.3. Định dạng: Mặc nhiên kết quả hiển thị dạng dd/mm/yy (ngày/tháng/năm). Bạn có thể thay đổi dạng hiển thị bằng cách chọn menu Format/Cells/Number/Date.

2. Hàm DATEVALUE:
1.1. Công dụng: Dủng để chuyển đổi ngày dạng text sang dạng ngày có thể tính toán được. Những ngày dạng text thường do cập nhật dữ liệu thừ các chương trình khác vào.

1.2. Cú pháp:
DATEVALUE(text)
- text: là chuỗi ký tự hiển thị ngày

Ví dụ: DATEVALUE("12-jan-2008") = 39459 = 12/01/2008

1.3. Định dạng: Kết quả của hàm này thường hiển thị bằng số chỉ ngày, bạn dùng Format Cells để hiển thị đúng.

3. Hàm DAY
3.1. Công dụng: Cho ra giá trị số của ngày

3.2. Cú pháp:
DAY(serial_number)
- serial_number là ngày. Ở dạng số, dữ liệu ngày hiiển thị một số nguyên. Trong Excel, ngày 1/1/1990 = 1. Như vậy, ngày 13/1/2009 = 39826 vì ngày 13/1/2009 cách ngày 1/1/1990 39825 ngày.
Ví dụ: C4=13/1/2009; DAY(C4)=13

4. Hàm DAYS360
4.1. Công dụng: Cho kết quả là số ngày giữa hai ngày theo nguyên tắc một năm có 360 ngày (12 tháng 30 ngày). Hàm này được sử dụng trong một số tính toán về kế toán, tài chính và thống kê.

4.2. Cú pháp:
DAYS360(start_date,end_date,method)
- start_date là ngày bắt đầu
- end_date là ngày kết thúc
- method là phương pháp tính toán theo Mỹ hay Châu Âu.
+ Bỏ trống hoặc FALSE (0) là theo kiểu Mỹ. Nếu ngày bắt đầu là ngày cuối cùng của tháng thì được xem là ngày 30 của tháng đó. Nếu ngày kết thúc là ngày cuối cùng của tháng và ngày bắt đầu trước ngày 30 của tháng thì ngày kết thúc tương đương với ngày 1 của tháng sau.
+ TRUE (1) tính theo kiểu châu Âu, nếu ngày bắt đầu và ngày kết thúc rơi vào ngày 31 thì được xem như ngày 30 của tháng đó.

Ví dụ: A2=30/1/2008; B2=1/2/2008 thì DAYS360(A2,B2) = 1

5. Hàm DATEIF
5.1. Công dụng: dùng để tính số chênh lệch thực tế giữa hai ngày. Kết quả có thể được hiển thị theo tuần, tháng hoặc năm.

5.2. Cú pháp:
=DATEDIF(FirstDate,SecondDate,"Interval")
- FirstDate là ngày sớm nhất trong 2 ngày
- SecondDate là ngày sau ngày đầu tiên. Nếu thứ tự này bị đảo lộn, kết quả sẽ là #NUM!
- “Interval” hiển thị cách tính toán. Đối số này luôn luôn được đặt trong dấu ngoặc kép “ “ nếu không sẽ cho kết quả là #NAME?. Các giá trị của “Interval” là:
+ “d” hiển thị số ngày
+ “m” hiển thị số tháng
+ “y” hiển thị số năm
+ “yd” hiển thị số ngày giống như so sánh hai ngày cùng năm
+ “ym” hiển thị số tháng giống như so sánh hai ngày cùng năm
+ “md” hiển thị số ngày giống như so sánh hai ngày cùng tháng

Ví dụ: C4 = 1/1/1960; D4=10/5/1970
Kết quả như sau:
DATEDIF(C4,D4,"d") = 3782
DATEDIF(C5,D5,"m") = 124
DATEDIF(C6,D6,"y") = 10
DATEDIF(C7,D7,"yd") = 130
DATEDIF(C8,D8,"ym") = 4
DATEDIF(C9,D9,"md") = 9

6. Hàm EDATE
6.1. Công dụng: trả về cùng ngày của ngày cần tìm nhưng trước hoặc sau số tháng tùy chọn

6.2. Cú pháp:
EDATE(start_date,months)
- start_date là ngày làm cơ sở
- months là số tháng trước hoặc sau ngày cơ sở. Nếu >0: sau ngày cơ sở; Nếu <0 : trước ngày cơ sở.
Ví dụ:
A2= 15/5/2008
EDATE(A2,1)=15/6/2008
EDATE(A2,-1)=15/4/2008
EDATE(A2,13)=15/6/2009

7. Hàm EOMONTH
7.1. Công dụng: Là hàm cho biết ngày cuối cùng của tháng trước hoặc sau tháng cơ sở.

7.2. Cú pháp:
EOMONTH(start_date,months)
- start_date là ngày làm cơ sở
- months là số tháng trước hoặc sau ngày cơ sở. Nếu >0: sau ngày cơ sở; Nếu <0 : trước ngày cơ sở.

Ví dụ:
A2= 15/5/2008
EOMONTH(A2,1)=30/6/2008
EOMONTH(A2,-1)=30/4/2008
EOMONTH(A2,14)=31/7/2009

----------------
Bài tiếp theo (Bài số 3): Cách nhập công thức và sử dụng hàm (tt)

 

[solomon2211]

Vì đang bận khóa sổ cuối năm nên xin lỗi các bạn là mình chưa tiếp tục được loại bài sử dụng hàm. Sẽ cố gắng.

 

[BNTT]

Xin lỗi chen ngang môt tí. Có cần thiết phải viết lại tất cả các hàm và cách sử dụng của chúng không?

Bởi vì ở đây: Tra cứu Hàm Excel, đã có đầy đủ tất cả các hàm của Excel rồi.

Đó là chưa nói tới cuốn sách này: Công thức và Hàm trong Excel, hướng dẫn cách sử dụng hàm theo nhóm hàm.