CÁC
HÀM THƯỜNG DÙNG
Trong
Excel
Chúa nhật, ngày
26 tháng 7 năm 2015 vừa qua, chúng tôi có chia sẻ bài viết “Tách tên bằng Hàm Mid trong Excel” và nhận
được đề nghị của một độc giả (Minh Thùy?): xin nói rõ cú pháp và ứng
dụng của các hàm: hàm Mid, hàm Len, hàm Find, hàm Substitute...
Dĩ nhiên chúng tôi sẽ đáp ứng yêu cầu
của bạn. Nhưng với đề nghị này thì hình như bạn chưa am hiểu về “HÀM”
trong Excel lắm phải không? Vậy thì mong bạn Thùy và các bạn theo dõi các bài
viết chia sẻ của trang Web Gx Vinh An về các HÀM trong Excel từ đơn giản đến phức
tạp.
Bài
1:Hàm và Công thức
A/ I.Giới thiệu Hàm và Công thức cho các bạn mới học:
a/Hàm: Trong phần mềm
Excel, người ta đã xây dựng sẵn rất nhiều Hàm
(Function) để thực hiện chức năng tính toán nào đó.... Nói rõ hơn là người
ta đã lập trình sẵn cho chúng
ta “xài”. Vậy bạn hoặc tôi có thể soạn một Hàm trong Excel được không? Trả
lời: Được chứ! Nhưng phải học một khóa “Công Nghệ Thông Tin” ở Đại
học đã nhé!?
Ví dụ:
b/Công thức: Để tính Tổng số
tiền đóng góp trong Danh sách trên, bạn có thể lập công thức:
*Tại ô C12, bạn
gõ CT: =C4+C5+C6+C7+C8+C9+C10+C11
*Hoặc tại ô C12,
Bạn gõ Hàm: =SUM(C4:C11)
-Kết quả như
nhau nhưng gõ Hàm thì nhanh gấp trăm lần, thậm chí ngàn lần nếu Danh sách dài.
-Trường hợp cộng, máy có thể cộng
tự động nếu bạn biết chọn công cụ: bạn đặt chuột ô C12 rồi nhấp trái 1 lần (để
chọn) , bạn đưa trỏ chuột lên công cụ AutoSum, cũng nhấp trái 1 lần. Máy thiết
lập Hàm cho chúng ta, bạn nhấn Enter sẽ
có kết quả ngay.
- Num_chars phải lớn hơn hoặc bằng không.
- Nếu num_chars lớn hơn độ dài của văn bản, hàm
LEFT trả về toàn bộ văn bản.
- Nếu num_chars được bỏ qua, thì nó được giả
định là 1.
Yêu cầu: Điền Dữ liệu vào Cột Mã số,
Tên Hàng và cột Thành tiền biết rằng:
c/Công thức và Hàm: Trong tính toán phức tạp, ta
thường dùng Hàm để viết Công thức
chứ không phải tách rời nhau như “mặt Trời và mặt Trăng” hoặc như “ chó với
mèo” (đùa một chút –Just for laugh).
Ví dụ: (Các công
thức chúng tôi đã chia sẽ như trong bài: “Tách tên bằng Hàm MID”
=MID(B3,FIND("*",SUBSTITUTE(B3,"
","*",LEN(B3)-LEN(SUBSTITUTE(B3,"
",""))))+1,10)
d/Nhập Hàm trong Công thức:
-Tên Hàm có thể
viết HOA, viết thường hoặc cả THường và hOA đều được.
-Có 3
cách nhập Hàm:
+Cách 1:Dùng bàn phím: đặt
chuột vào ô muốn gõ Hàm, nếu Hàm đứng đầu tiên trong Công thức thì phải gõ dấu
bằng “=” trước rồi gõ tên Hàm, gõ
dấu mở ngoặc đơn “(”, gõ các tham biến
(tham số) ngăn cách nhau bởi dấu phẩy, gõ dấu đóng ngoặc đơn “)” nếu hết.
*Ghi chú:
-Các tham biến:
có thể là địa chỉ ô, địa chỉ vùng, một giá trị số, một hàm (hàm lồng trong
hàm)...
-Ngăn cách dấu
phẩy “,” hoặc dấu chấm phẩy “;” (tùy cài đặt)
Cách 2 +Dùng chuột: Nhấn chuột (mouse) tại tại biểu tượng fx =Insert Function
Xuất hiện cửa sổ:
*Rà chuột vào
Hàm cần tìm theo nhóm:
-Most Recently
Used : Các hàm sử dụng gần đây
-All : Tất cả các
hàm
-Financial : Hàm tài chính
-Date & Time : Hàm ngày và giờ
-Math & Trig : Hàm toán và lượng giác
-Statistical : Hàm thống kê
-Lookup &
Reference : Hàm tìm kiếm và tham chiếu
-Databasa : Hàm cơ sở dữ liệu
-Text : Hàm xử lý ký
tự
-Logical : Hàm Logic
-Information : Hàm thông tin
....
*Khi ta chọn một
trong các nhóm trên, trong mục Function sẽ trình các tên Hàm của nhóm, ta nhấp
chuột vào hàm cần tìm, nhớ OK.
Ví dụ: Bạn chọn nhóm:
Text và hàm Left, sẽ
xuất hiện tiếp cửa sổ:
-Tại Ô Text bạn
gõ hoặc nhấp
-Tại ô Num
_chars: bạn gõ 4 , nhấp OK
Minh họa:
Giải thích: Tại Text, tôi chọn Text là
B4 ; tại Num_chars: tôi lấy 4 ký tự, OK
*Kết quả:
Cách 3:
a/Excel
2003: Dùng Menu: Insert/ Function
b/Excel
2007:-Bạn vào Formulas, -Chọn biểu tượng Text-Chọn Left
-Tiến hành như trên
II.Các
hàm trong nhóm Text thường dùng:
A.Hàm LEFT:
1.Cấu trúc của hàm Left
trong Excel
LEFT(text,[num_chars])
*Giải thích:
-text:
Chuỗi cần tách, hoặc địa chỉ ô chứa Text cần tách
-num chars: Viết tắt của number
chars: số ký tự cần tách
*Chú ý:
2. Chức năng của hàm Left
trong Excel
Trích ra n ký tự từ bên trái của chuỗi Text.
3. Ví dụ về hàm Left trong Excel
*Ví
dụ 1: Tìm Năm sinh trong Mã Học viên: Tại D4 gõ
=Left(B4,4), Enter rồi Copy (kéo) Công thức xuống.
Giải
thích:
-Tại
D4, tôi muốn ghi năm sinh của HV lấy từ Mã Học viên.
-B4
là ô chứa Mã Học viên
-số
4 là tôi lấy 4 lý tự
*Ví dụ 2: Tại bất cứ Ô nào trong
Bảng Excel, bạn gõ:
=LEFT(“Giáo xứ Vinh An”,7)
Enter sẽ có kết quả: Giáo xứ
Giải thích:
-Tôi chỉ lấy 7 ký
tự trong chuỗi text: “Giáo xứ Vinh An”
-Thực ra chuỗi: “Giáo xứ” chỉ có 6 ký tự. Nhưng gõ 7 vì
Excel xem khoảng
cách giữa “Giáo xứ”
cũng là ký tự nên nó có 7 ký tự
-Khi gõ hàm Excel phải gõ liền nhau, không gõ “phím cách”
!!!!!
Ví
dụ 3: Cho bảng Dữ liệu BẢNG 1 và
Bảng Dữ liệu chưa hoàn thành phía dưới.
+ Ký tự đầu của Mã hàng chính
là Mã số
+ Dựa vào Mã số tìm được để
Tham chiếu lên BẢNG 1, Tìm Tên hàng.
+ Dựa vào Mã số tìm được tham
chiếu lên BẢNG 1 điền dữ liệu vào cột Thành tiền biết rằng:
Thành tiền
= Số lượng x Đơn giá (ĐG)
a/Để điền Dữ liệu vào cột Mã số, ta áp dụng công thức của hàm LEFT trong EXCEL
để trích Ký tự.
-Vì ký tự đầu tiên của Mã hàng
chính là Mã số nên công thức cho Hàm LEFT tại ô C12 sẽ là: C12=LEFT(B12,1);
Enter và kéo công thức xuống.
B.Phần
nâng cao cho các bạn “Lính cũ”
*Điền Tên hàng và Thành tiền
(tham khảo cho biết)
-Dựa vào cột Mã hàng vừa tìm được, ta dễ dàng điền được Kết quả
vào cột Tên hàng & Thành tiền nhờ
sử dụng hàm VLOOKUP.
*Công thức tại ô Kết quả D12 như sau:
D12=VLOOKUP(C12,$B$5:$D$9,2,FALSE)
*Trong trường hợp ta kết hợp trực tiếp với hàm Left, công thức sẽ là:
D12 =VLOOKUP(LEFT(B12,1),$B$5:$D$9,2,FALSE)
*Sử dụng thao tác sao chép công thức, ta được Kết quả ở những ô còn
lại bên dưới.
-Cột Thành tiền tính tương tự:
F12=VLOOKUP(C12,$B$5:$D$9,3,FALSE)*E12
Ví dụ 4: Nhiều khi phải tách Tên Thánh thành một
cột riêng ta cũng dùng hàm LEFT kết hợp với hàm FIND (tham
khảo cho biết)
Giải thích: Tôi tìm Tên
Thánh ở Ô B4, nhưng tôi chưa biết phải lấy bao nhiêu ký tự, vì các Tên Thánh có
số ký tự khác nhau. Nên tôi phải dùng hàm FIND
tìm “khoảng cách” đầu tiên trong ô B4 là ký tự thứ mấy trong chuỗi “Anna Nguyễn
Thị Ái” rồi trừ đi 1 vì tôi cần lấy Tên Thánh không lấy “khoảng cách” giữa Tên
Thánh và Họ.
Minh họa Hàm FIND:
*Thực ra bạn
dùng Hàm TRIM
lồng hàm RIGHT
kết hợp với hàm FIND
và bỏ “trừ 1” (-1) cũng được.
Vậy
bài này chúng ta học HÀM RIGHT và ứng
dụng của nó. Nếu bạn là “lính mới” thì đừng quan tâm đến phần B dành cho “lính cũ”.Chúng tôi sẽ tiếp tục
chia sẻ từ dễ đến khó trong các bài tiếp theo.
Pet Trần
Bạn có thích bài viết này...?
|
|
|
Nhận tin miễn phí hằng ngày!
|
Follow us!
|
Comments[ 0 ]
Đăng nhận xét
Giáo xứ đã nhận được góp ý, nhận xét, bài gửi của Quý vị
Xin Thiên Chúa chúc lành cho quý vị.