Cong thuc excel v3
Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (461.54 KB, 27 trang )
Sử dụng công thức trong
excel
Academic Computing Services
2007
Công thức
Là các phương trình toán học
Bắt đầu bởi dấu =
Dữ liệu được lưu trữ trên các bảng sẽ được sử
dụng trong các công thức và được tham chiếu
thông qua địa chỉ các ô
=A1+A2/(A3-A4)
Academic Computing Services
2007
Ví dụ về công thức đơn
giản
Academic Computing Services
2007
Các hàm số
Là các công thức được định nghĩa trước
Công thức:=A1+A2+A3+A4+A5
Hàm số: =SUM(A1:A5)
Academic Computing Services
2007
Ví dụ một công thức phức
tạp
Academic Computing Services
2007
Các thành phần cấu tạo của
công thức?
=(3*$A$2*SUM(A3:A9))^1/3
Bắt đầu bởi=
Các hằng số
Địa chỉ các ô
Các toán tử
Các hàm
Academic Computing Services
2007
Hằng số
Giá trị được đánh trực tiếp từ công thức
Đơn giản
Chính xác
Uyển chuyển
2, 3, 12, 14.32
Academic Computing Services
2007
Địa chỉ tham chiếu
Địa chỉ tương đối (Column Row)
A1, C18, ZZ65536
Địa chỉ tuyệt đối ($Column$Row)
$A$1, $C$18,$ZZ$65536
Pha trộn (Fixed Column or Row)
$A1, C$18
Tên
Int_Rate, Grade_Scale
Academic Computing Services
2007
Các toán tử số học
Số học
- (negative)
%
^ (Exponent) *
/
+
So sánh
=<
>
<= >= <>
Văn bản
&
Tham chiếu
: (Colon)
_ (Space)
, (Comma)
Academic Computing Services
2007
Thứ tự các toán tử số học
Công thức toán được đọc từ trái qua phải
1. ( )
2. Negation
3. %
4. ^
5. * or /
6. + or –
7. &
8. = < > <= >= <>
Academic Computing Services
2007
Các toán tử logic
• AND
• OR
• NOT
• =AND(A2>A3, A2
• =NOT(A5="Sprockets")
Academic Computing Services
2007
Các thông báo lỗi (Tham
chiếu ô)
#########
Cột không đủ rộng
#VALUE!
Sai tham chiếu hoặc tham số
#DIV/0!
Lỗi chia cho 0
#Name?
Tên hàm không được định nghĩa
#N/A!
Thiếu dữ liệu hoặc dữ liệu sai khi tìm
kiếm với VLOOKUP
#REF
Địa chỉ ô không tồn tại
#NUM!
Không phải giá trị số
#NULL!
Các ô không giao nhau
Academic Computing Services
2007
Sao chép công thức?
AutoFill : sử dụng để sao chép công thức cho
các ô kề nhau
AutoFill tự động thay đổi địa chỉ ô theo vị trí tiếp
theo của công thức
Academic Computing Services
2007
Một số hàm xử lý văn bản
• REPLACE, REPLACEB Replaces characters within text
• REPT Repeats text a given number of times
• RIGHT, RIGHTB
Returns the rightmost characters from a
text value
• SEARCH, SEARCHB
Finds one text value within another (not
case-sensitive)
• SUBSTITUTE
Substitutes new text for old text in a text
string
• T Converts its arguments to text
• TEXT Formats a number and converts it to text
• TRIM Removes spaces from text
• UPPER
Converts text to uppercase
• VALUE
Converts a text argument to a number
Academic Computing Services
2007
Academic Computing Services
2007
Bảng ví dụ
SalesRep
Jones
Jones
Rogers
Rogers
Rogers
Franklin
Franklin
Jones
Franklin
Rogers
Franklin
Jones
Sum:
Average:
Count:
Month
Jan
Jan
Jan
Jan
Jan
Jan
Feb
Feb
Feb
Feb
Feb
Feb
Amount
100
225
400
150
250
800
200
350
1200
900
750
800
Academic Computing Services
2007
MAX, MIN
• MAX
Cú pháp: MAX(đối số 1, đối số 2,…)
Trả lại số lớn nhất trong danh sách
Ví dụ: MAX(6,7,2,9,13)=13
• MIN
Cú pháp: MIN(đối số 1, đối số 2,…)
Trả lại số nhỏ nhất trong danh sách
Ví dụ: MIN(6,7,2,9,13)=2
Academic Computing Services
2007
Tính tổng
Trong một khoảng:
=SUM(C2:C13)
Toàn bộ:
=SUM(C:C)
Academic Computing Services
2007
Đếm
Đếm trong một khoảng và toàn bộ:
=COUNT(C2:C13)
=COUNT(C:C)
Đếm có điều kiện:
=COUNTIF(A2:A13, “Jones”) hoặc
=COUNTIF(A2:A13, “=Jones”) – bằng
=COUNTIF(A2:A13, “<> Jones”) – khác
Academic Computing Services
2007
Đếm…
Đếm lượng bán lớn hơn giá trị cho trước
=COUNTIF(C2:C13, “>”&D1)
Note: toán tử (&) dùng để nối (>) và địa chỉ ô
Đếm lượng bán hàng lớn hơn giá trị trung bình:
=COUNTIF(C2:C13, “>”&AVERAGE(C2:C13))
Academic Computing Services
2007
Tổng có điều kiện
Tổng lượng bán trong tháng 1:
=SUMIF(B2:B13, “=Jan”, C2:C13)
Tổng lượng bán của các đại lý khác:
=SUMIF(A2:A13, “<> Rogers”, C2:C13)
Tổng lượng bán lớn hơn một giá trị nhất định:
=SUMIF(C2:C13, “>500”,C2:C13)
Range
Criteria
Sum range
Academic Computing Services
2007
Trung bình có điều kiện
Trung bình lượng bán trong tháng 1:
=ABERAGEA(B2:B13, “=Jan”, C2:C13)
Trung bình lượng bán của các đại lý khác:
=AVERAGEA(A2:A13, “<> Rogers”, C2:C13)
Trung bình lượng bán lớn hơn một giá trị nhất
định:
=AVERAGEA(C2:C13, “>500”,C2:C13)
Academic Computing Services
2007
Sử dụng hàm IF VLOOKUP
Hiển thị giá trị phụ thuộc điều kiện
Trả lại một giá trị nếu điều kiện đung, giá trị khác
nếu điều kiện sai
=IF(logical_test,value_if_true,value_if_fal
se)
=IF(OR(A5<>"Sprockets",
A6<>"Widgets"), "OK", "Not OK")
Academic Computing Services
2007
VLOOKUP
• VLOOKUP
Cú pháp: VLOOKUP(giá trị tìm kiếm, vùng bảng đối chiếu,
cột trả lại kết quả, sắp xếp vùng đối chiếu)
Trả lại kết quả trên cùng hàng với giá trị tìm kiếm trong
vùng bảng tham chiếu tại cột trả lại kết quả
Chú ý:
Vùng bảng đối chiếu để ở địa chỉ tuyệt đối
Cột trả lại kết quả phải nhỏ hơn tổng số cột
trong vùng bảng tham chiếu
Academic Computing Services
2007
VLOOKUP
• VLOOKUP
Chú ý:
Sắp xếp vùng đối chiếu chỉ nhận giá trị logic 0
hoặc 1, nếu bỏ qua thì nhận giá trị 1
Nếu để giá trị 0: tham chiếu chính xác và vùng
bảng tham chiếu không cần sắp xếp
Nếu để giá trị 1: tham chiếu tương đối và vùng
bảng tham chiếu phải được sắp xếp theo thứ tự
tăng dần của cột tham chiếu (cột trái ngoài
cùng của vùng bảng tham chiếu)
Academic Computing Services
2007
