Học Excel 2010: May 2013

Friday, May 31, 2013

Giới thiệu công thức và hàm:
Giới thiệu công thức (Formula)
Công thức giúp bảng tính hữu ích hơn rất nhiều, nếu không có các công thức thì bảng tính cũng giống như trình soạn thảo văn bản. Chúng ta dùng công thức để tính toán từ các dữ liệu lưu trữ trên bảng tính, khi dữ liệu thay đổi các công thức này sẽ tự động cập nhật các thay đổi và tính ra kết quả mới giúp chúng ta đỡ tốn công sức tính lại nhiều lần. Vậy công thức có các thành phần gì?
Công thức trong Excel được nhận dạng là do nó bắt đầu là dấu = và sau đó là sự kết hợp của các toán tử, các trị số, các địa chỉ tham chiếu và các hàm.
Ví dụ:
công thức và hàm trong excel

Các toán tử trong công thức

*Toán tử*	*Chức năng*	*Ví dụ*	*Kết quả*
+	Cộng	=3+3	3 cộng 3 là 6
-	Trừ	=45-4	45 trừ 4 còn 41
*	Nhân	=150*.05	150 nhân 0.50 thành 7.5
/	Chia	=3/3	3 chia 3 là 1
^	Lũy thừa	=2^4 =16^(1/4)	2 lũy thừa 4 thành 16 Lấy căn bậc 4 của 16 thành 2
&	Nối chuỗi	=”Lê” & “Thanh”	Nối chuỗi “Lê” và “Thanh” lại thành “Lê Thanh”
=	Bằng	=A1=B1	Ví dụ ô A1=3, ô B1=6 Kết quả: FALSE
>	Lớn hơn	=A1>B1	Ví dụ ô A1=3, ô B1=6 Kết quả: FALSE
<	Nhỏ hơn	=A1<B1	Ví dụ ô A1=3, ô B1=6 Kết quả: TRUE
>=	Lớn hơn hoặc bằng	=A1>=B1	Ví dụ ô A1=3, ô B1=6 Kết quả: FALSE
<=	Nhỏ hơn hoặc bằng	=A1<=B1	Ví dụ ô A1=3, ô B1=6 Kết quả: TRUE
<>	Khác	=A1<>B1	Ví dụ ô A1=3, ô B1=6 Kết quả: TRUE
,	Dấu cách các tham chiếu	=Sum(A1,B1)	Ví dụ ô A1=3, ô B1=6 Kết quả: 9
:	Tham chiếu mãng	=Sum(A1:B1)	Ví dụ ô A1=3, ô B1=6 Kết quả: 9
khoảng trắng	Trả về các ô giao giữa 2 vùng	=B1:B6 A3:D3	Trả về giá trị của ô

Thứ tự ưu tiên của các toán tử

*Toán tử*	*Mô tả*	*Ưu tiên*
: (hai chấm) (1 khoảng trắng) , (dấu phẩy)	Toán tử tham chiếu	1
–	Số âm (ví dụ –1)	2
%	Phần trăm	3
^	Lũy thừa	4
* và /	Nhân và chia	5
+ và –	Cộng và trừ	6
&	Nối chuỗi	7
= < > <= >= <>	So sánh	8

Giới thiệu hàm (Function)
Hàm trong Excel được lập trình sẵn dùng tính toán hoặc thực hiện một chức năng nào đó. Việc sử dụng thành thạo các hàm sẽ giúp chúng ta tiết kiệm được rất nhiều thời gian so với tính toán thủ công không dùng hàm. Các hàm trong Excel rất đa dạng bao trùm nhiều lĩnh vực, có những hàm không yêu cầu đối số, có những hàm yêu cầu một hoặc nhiều đối số, và các đối số có thể là bắt buộc hoặc tự chọn.
Ví dụ:
=Rand(): hàm không có đối số
=If(A1>=5,”Đạt”,”Rớt”): hàm 3 đối số
=PMT(10%,4,1000,,1): hàm nhiều đối số và đối số tùy chọn
Trong Excel 2010 có các hàm chính như sau:
• Hàm ngoại: Call, Registed.ID,…
• Hàm lấy dữ liệu từ SSAS: Cubeset, Cubevalue,…
• Hàm dữ liệu: Dmin, Dmax, Dcount,…
• Hàm ngày và thời gian: Time, Now, Date,….
• Hàm kỹ thuật: Dec2Bin, Dec2Hex, Dec2Oct,…
• Hàm tài chính: Npv, Pv, Fv, Rate,…
• Hàm thông tin: Cell, Thông tin, IsNa,…
• Hàm luận lý: If, And, Or,…
• Hàm tham chiếu và tìm kiếm: Choose, Vlookup, OffSet,…
• Hàm toán và lượng giác: Log, Mmult, Round,…
• Hàm thống kê: Stdev, Var, CountIf,…
• Hàm văn bản: Asc, Find, Text,…
Chúng ta sẽ cùng nghiên cứu cách sử dụng từng hàm trong các nhóm hàm trên ở các phần sau.

Nhập công thức và hàm
Nhập công thức trong Excel rất đơn giản, muốn nhập công thức vào ô nào bạn chỉ việc nhập dấu = và sau đó là sự kết hợp của các toán tử, các trị số, các địa chỉ tham chiếu và các hàm. Bạn có thể nhìn vào thanh Formula để thấy được trọn công thức. Một điều hết sức lưu ý khi làm việc trên bảng tính là tránh nhập trực tiếp các con số, giá trị vào công thức mà bạn nên dùng đến tham chiếu.
Ví dụ:
nhập công thức và hàm trong excel

Minh họa dùng tham chiếu trong hàm

Trong ví dụ trên, ở đối số thứ nhất của hàm NPV chúng ta không nhập trực suất chiết tính 10% vào hàm mà nên tham chiếu đến địa chỉ ô chứa nó là I2, vì nếu lãi suất có thay đổi thì ta chỉ cần nhập giá trị mới vào ô I2 thì chúng ta sẽ thu được kết quả NPV mới ngay không cần phải chỉnh sửa lại công thức.
Giả sử các ô C2:G2 được đặt tên là DongTien, và ô I2 đặt tên là LaiSuat (Xem lại cách đặt tên vùng ở bài số 1) thì trong quá trình nhập công thức bạn có thể làm như sau:
B1. Tại ô B4 nhập vào =NPV(
B2. Nhấn F3, cửa sổ Paste Name hiện ra
B3. Chọn LaiSuat và nhấn OK
B4. Nhập dấu phẩy (,) và gõ F3
B5. Chọn DongTien và nhấn OK
B6. Nhập dấu đóng ngoặc rồi nhập dấu +
B7. Nhấp chuột vào ô B2
B8. Nhấn phím Enter

Chèn tên vùng vào công thức

Một trong những cách dễ dàng nhất để sử dụng hàm trong Excel là sử dụng thư viện hàm. Khi bạn muốn sử dụng hàm nào chỉ việc vào thanh Ribbon -> chọn nhóm Formulas -> Function Library -> chọn nhóm hàm -> chọn hàm cần sử dụng. Ngoài ra bạn có thể nhấn vào nút để gọi hộp thoại Insert Function một cách nhanh chóng và khi cần tìm hiểu về hàm này bạn chỉ cần nhấn vào Help on this function.
chèn tên vùng vào công thức

Hộp thoại Insert Function

Tham chiếu trong công thức
Các tham chiếu sử dụng trong công thức giúp cho chúng ta khỏi tốn công sửa chữa các công thức khi các giá trị tính toán có sự thay đổi. Có 3 loại tham chiếu sau:
• Tham chiếu địa chỉ tương đối: Các dòng và cột tham chiếu sẽ thay đổi khi chúng ta sao chép hoặc di dời công thức đến vị trí khác một lượng tương ứng với số dòng và số cột mà ta di dời. Ví dụ A5:B7, C4
• Tham chiếu địa chỉ tuyệt đối: Các dòng và cột tham chiếu không thay đổi khi ta di dời hay sao chép công thức. Ví dụ $A$5:$B$7, $C$4
• Tham chiếu hỗn hợp: Phối hợp tham chiếu địa chỉ tương đối và tuyệt đối. Ví dụ A$5 nghĩa là cột A tương đối và dòng 5 tuyệt đối.

Lưu ý: Dấu $ trước thứ tự cột là cố định cột và trước thứ tự dòng là cố định dòng. Nhấn phím F4 nhiều lần để (tuyệt đối) cố định/ bỏ cố định dòng hoặc cột.
Nhấn phím F4 nhiều lần để (tuyệt đối) cố định/ bỏ cố định dòng hoặc cột.
Ví dụ: Tính thành tiền bằng Số lượng nhân Giá. Đổi sang giá trị Thành tiền sang VND. Tính tổng các cột Thành tiền và cột VND.

Minh họa địa chỉ tương đối và tuyệt đối

B1. Tại ô D2 nhập vào =B2*C2 và Enter. Sau đó quét chọn cả vùng D2:D14 và gõ <Ctrl+D>. Vào các ô D3, D4... D14 ta thấy công thức các dòng tự động được thay đổi tương ứng với khoảng cách so với ô D2. Trường hợp này chúng ta dùng địa chỉ tương đối của B2*C2 là vì chúng ta muốn khi sao chép công thức xuống phía dưới thì địa chỉ các ô tính toán sẽ tự động thay đổi theo.

B2. Tại ô E2 nhập vào =D2*B$17 và Enter, sau đó chép công thức xuống các ô E3:E14. Chúng ta cần cố định dòng 17 trong địa chỉ tỷ giá B17 vì ta muốn khi sao công thức xuống thì các công thức sao chép vẫn tham chiếu đến ô B17 để tính toán.
B3. Tại ô D15 nhập vào =Sum(D2:D14) và chép sang ô E15.

Lưu ý:
• Tham chiếu đến địa chỉ ở worksheet khác nhưng cùng workbook thì có dạng
• Tên_sheet!Địa_chỉ_ô. Ví dụ:
=A2*Sheet2!A2
=A2*’Thong so’!B4
Khi tên sheet có chứa khoảng trắng thì để trong cặp nháy đơn ‘ ’
• Tham chiếu đến địa chỉ trong workbook khác thì có dạng
[Tên_Workbook]Tên_sheet!Địa_chỉ_ô.

Ví dụ:
=A2*[Bai2.xlsx]Sheet3!A4
=A2*’[Bai tap 2.xlsx]Sheet3’!A4
Khi tên Sheet hay Workbook có chứa khoản trắng để trong cặp nháy đơn ‘ ’
=A2*’C:\Tai lieu\[Bai tap 2.xlsx]Sheet3’!A4
Khi tham chiếu đến workbook khác mà workbook này không mở
=A2*’\\DataServer\Excel\[Bai tap 2.xlsx]Sheet3’!A4
Khi tham chiếu đến tài nguyên chia sẽ trên máy chủ trong mạng
Các lỗi thông dụng (Formulas errors)
Các lỗi thông dụng

*Lỗi*	*Giải thích*
#DIV/0!	Trong công thức có chứa phép chia cho 0 (zero) hoặc chia ô rỗng
#NAME?	Do dánh sai tên hàm hay tham chiếu hoặc đánh thiếu dấu nháy
#N/A	Công thức tham chiếu đến ô mà có dùng hàm NA để kiểm tra sự tồn tại của dữ liệu hoặc hàm không có kết quả
#NULL!	Hàm sử dụng dữ liệu giao nhau của 2 vùng mà 2 vùng này không có phần chung nên phần giao rỗng
#NUM!	Vấn đề đối với giá trị, ví dụ như dùng nhầm số âm trong khi đúng phải là số dương
#REF!	Tham chiếu bị lỗi, thường là do ô tham chiếu trong hàm bị xóa
#VALUE!	Công thức tính toán có chứa kiểu dữ liệu không đúng.

Một số hàm thống kê nâng cao

Hàm thống kê nâng cao trong excel 2010
Bao gồm các hàm số giúp bạn giải quyết các bài toán thống kê từ đơn giản đến phức tạp.
=AVEDEV Tính bình quân độ phân cực
=AVERAGE(vùng) Tính trung bình cộng các số.
=AVERAGEA Tính trung bình cộng các giá trị
=DAVERAGE (vùng dữ liệu, cột cần tính, vùng tiêu chuẩn) Hàm tính trung bình các giá trị trong cột thoả mãn điều kiện của vùng tiêu chuẩn trong vùng dữ liệu.
Hàm AVERAGEIF()
Trả về trung bình cộng (số học) của tất cả các ô được chọn thỏa mãn một điều kiện cho trước.
Cú pháp: = AVERAGEIF(range, criteria, average_range)
range : Là một hoặc nhiều ô cần tính trung bình, có thể bao gồm các con số, các tên vùng, các mảng hoặc các tham chiếu đến các giá trị...
criteria : Là điều kiện dưới dạng một số, một biểu thức, địa chỉ ô hoặc chuỗi, để qui định việc tính trung bình cho những ô nào...
average_range : Là tập hợp các ô thật sự được tính trung bình. Nếu bỏ trống thì Excel dùng range để tính.
Lưu ý:
* Các ô trong range nếu có chứa những giá trị luận lý (TRUE hoặc FALSE) thì sẽ được bỏ qua.
* Những ô rỗng trong average_range cũng sẽ được bỏ qua.
* Nếu range rỗng hoặc có chứa dữ liệu text, AVERAGEIF sẽ báo lỗi #DIV/0!
* Nếu có một ô nào trong criteria rỗng, AVERAGEIF sẽ xem như nó bằng 0.
* Nếu không có ô nào trong range thỏa mãn điều kiệu của criteria, AVERAGEIF sẽ báo lỗi #DIV/0!
* Bạn có thể các ký tự đại diện như ?, * trong criteria (dấu ? thay cho một ký tự nào đó, và dấu * thay cho một chuỗi nào đó). Khi điều kiện trong criteria là chính các dấu ? hoặc *, thì bạn gõ thêm dấu ~ trước nó.
* average_range không nhất thiết phải có cùng kích thước với range, mà các ô thực sự được tính trung bình sẽ dùng ô trên cùng bên trái của average_range làm ô bắt đầu, và bao gồm thêm những ô tương ứng với kích thước của range.
Hàm AVERAGEIFS()
Trả về trung bình cộng (số học) của tất cả các ô được chọn thỏa mãn nhiều điều kiện cho trước.
Cú pháp: = AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
average_range : Vùng cần tính trung bình, có thể bao gồm các con số, các tên vùng, các mảng hoặc các tham chiếu đến các giá trị...
criteria_range1, criteria_range2, ... : Vùng chứa những điều kiện để tính trung bình. Có thể khai báo từ 1 đến 127 vùng.
criteria1, criteria2, ... : Là các điều kiện để tính trung bình. Có thể khai báo từ 1 đến 127 điều kiện, dưới dạng số, biểu thức, tham chiếu hoặc chuỗi...
Lưu ý:
* Nếu average_range rỗng hoặc có chứa dữ liệu text, AVERAGEIFS sẽ báo lỗi #DIV/0!
* Nếu có một ô nào trong những vùng criteria_range rỗng, AVERAGEIFS sẽ xem như nó bằng 0.
* Những giá trị logic: TRUE sẽ được xem là 1, và FALSE sẽ được xem là 0.
* Mỗi ô trong average_range chỉ được tính trung bình nếu thỏa tất cả điều kiện quy định cho ô đó
* Không giống như AVERAGEIF(), mỗi vùng criteria_range phải có cùng kích thước với average_range
* Nếu có một ô nào trong average_range không thể chuyển đổi sang dạng số, hoặc nếu không có ô nào thỏa tất cả các điều kiện, AVERAGEIFS sẽ báo lỗi #DIV/0!
* Có thể các ký tự đại diện như ?, * cho các điều kiện (dấu ? thay cho một ký tự nào đó, và dấu * thay cho một chuỗi nào đó). Khi điều kiện trong criteria là chính các dấu ? hoặc *, thì bạn gõ thêm dấu ~ trước nó.
Hàm COUNT() Đếm ô dữ liệu chứa số (đếm số ô không trống) - đếm số ô có trong vùng. Nhưng chỉ đếm những ô có kiểu dữ liệu là kiểu số.
Cú pháp=COUNT(vùng)
=COUNTA Đếm số ô chứa dữ liệu
=COUNTIF(Vùng cần đếm, ô điều kiện) Hàm đếm có điều kiện - đếm số ô có trong vùng với điều kiện đã chỉ ra ở ô điều kiện.
Hàm COUNTIF đếm ô có kiểu dữ liệu là kiểu số và cả kiểu ký tự.
=DCOUNT(vùng dữ liệu, cột cần đếm, vùng tiêu chuẩn) Đếm số ô chứa lượng giá trị số trong cột thoả mãn đk trong vùng tiêu chuẩn tìm kiếm trong vùng dữ liệu.
VD: Đếm xem có bao nhiêu người có mức lương là 500000.
=MAX(vùng) Tìm số lớn nhất trong vùng
=MAXA Tìm giá trị lớn nhất
Hàm DMAX()
Cú pháp: = DMAX(database, field, criteria)=DMAX (vùng dữ liệu, cột giá trị, vùng tiêu chuẩn)
Hàm tính giá trị cao nhất trong cột giá trị thoả mãn điều kiện của vùng tiêu chuẩn trong vùng dữ liệu.
VD: Tìm xem những người 26 tuổi ai cao lương nhất.
=MIN(vùng) Tìm số nhỏ nhất trong vùng
=MINA Tìm giá trị nhỏ nhất
Hàm DMIN()
Cú pháp: =DMIN(database, field, criteria)=DMIN (vùng dữ liệu, cột giá trị, vùng tiêu chuẩn)
Hàm tính giá trị nhỏ nhất trong cột giá trị thoả mãn điều kiện của vùng tiêu chuẩn trong vùng dữ liệu.
VD: Tìm xem những người 26 tuổi ai thấp lương nhất.
=RANK(ô cần xếp thứ, vùng cần so sánh) Tìm vị thứ của một số trong dãy số.
Vùng cần so sánh: Thường để ở chế độ giá trị tuyệt đối: $
Hàm DVARP()
Cú pháp: = DVARP(database, [field,] criteria)
Tính toán sự biến thiên của một tập hợp dựa trên toàn thể tập hợp, bằng cách sử dụng các số liệu trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.
Hàm DVAR()
Cú pháp: = DVAR(database, [field,] criteria)
Ước lượng sự biến thiên của một tập hợp dựa trên một mẫu, bằng cách sử dụng các số liệu trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.
Hàm DSUM()
Cú pháp: = DSUM(database, field, criteria)
Cộng các số trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.
Hàm DSTDEVP()
Cú pháp: = DSTDEVP(database, field, criteria)
Tính độ lệch chuẩn của một tập hợp theo toàn thể các tập hợp, bằng cách sử dụng các số liệu trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.
Hàm DSTDEV()
Cú pháp: = DSTDEV(database, field, criteria)
Ước lượng độ lệch chuẩn của một tập hợp theo mẫu, bằng cách sử dụng các số liệu trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.
Hàm DPRODUCT()
Cú pháp: = DPRODUCT(database, field, criteria)
Nhân các giá trị trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định.
Đếm số ô rỗng trong bảng tính
Dùng công thức mảng: {=SUM(IF(ISBLANK(range), 1, 0))} với range là vùng dữ liệu cần kiểm tra.
Đếm số ô chứa những giá trị không phải là kiểu số
Dùng công thức mảng: {=SUM(IF(ISNUMBER(range), 0, 1))} với range là vùng dữ liệu cần kiểm tra.
Đếm số ô bị lỗi
Dùng công thức mảng: {=SUM(IF(ISERROR(range), 1, 0))} với range là vùng dữ liệu cần kiểm tra.

Friday, May 31, 2013

Học Excel 2010

Giới thiệu công thức và hàm trong excel 2010

Một số hàm thống kê nâng cao