Vài dòng tâm sự Trước giờ, trong diễn đàn này đã có nhiều bài viết về các hàm Excel, tổng hợp, liệt kê các hàm Excel. Tuy nhiên, nếu gọi là tổng hợp theo hệ thống, phân loại theo từng chủ đề thì chưa có. Bùi Nguyễn Triệu Tường - giaiphapexcel.com Download toàn bộ tài liệu tại http://www.mediafire.com/?77enl7lpon31f7r toan-thpt.blogspot.com Hàm TRIM Xóa tất cả những khoảng trắng vô ích trong chuỗi văn bản, chỉ chừa lại những khoảng trắng nào dùng làm dấu cách giữa hai chữ. Cú pháp: =TRIM(text) Hình sau đây cho thấy công dụng của hàm TRIM. Hàm CLEANDùng để xóa tất cả những ký tự không in ra được (nonprintable characters) trong chuỗi văn bản. Cú pháp: =CLEAN(text) text: chuỗi văn bản cần xóa những ký tự không in ra được. (Những ký tự không in ra được là những ký tự có mã số từ 1 đến 31 trong bảng mã ANSI.)
Hàm REPTDùng để lập đi lập lại (nhân bản) một ký tự hoặc một từ, với số lần được định trước. Cú pháp: =REPT(text, times) text : ký tự (character), một con số hoặc một từ (string) cần được nhân bảntimes: số lần lập đi lập lại của text
Hàm MID Công dụng: Dùng để trích xuất một chuỗi con (substring) từ một chuỗi Công thức: =MID(text, start_num, num_chars]) Trích xuất họ, tên đệm và tên (ví dụ với tên tiếng Anh, phần tên đệm được viết tắt) Đây cũng là dạng bài toán đã được rất nhiều bạn hỏi trên GPE. =LEFT(A2, FIND(” “, A2) – 1) → Karen Công thức FIND(” “, A2) sẽ cho kết quả là 6, là vị trí của khoảng trắng đầu tiên (sau chữ Karen). =FIND(” “, A2, FIND(” “,A2) + 1) Rồi dùng kết quả của công thức này làm
tham số cho hàm RIGHT() để trích ra phần Họ (last name): =RIGHT(A2, LEN(A2) – FIND(” “, A2, FIND(” “, A2) +1)) → Hammond Để trích phần tên đệm, dùng hàm FIND() để tìm vị trí của dấu chấm (.) rồi đưa vào trong công thức của hàm MID() để tìm ký tự đứng trước dấu chấm: =MID(A2, FIND(“.”, A2) – 1, 1) → E
Trong Excel có hàm COLUMN(), cho ra kết quả là số của cột (ví dụ, gõ hàm này trong cột B thì kết quả sẽ là 2). Đây là một vấn đề đòi hỏi sự khôn khéo một chút, vì tên cột trong bảng tính chạy từ A đến Z, từ AA đến AZ… và cho tới tận cùng là XFD (!) Có một hàm giúp chúng ta tìm địa chỉ tuyệt đối của một cell, đó là hàm CELL(“address”), ví dụ $A$2, hoặc $B$10… Hàm CELL(info_type
[,reference]) Mời bạn xem thêm về hàm CELL() Tinh ý một chút, ta thấy tên của cột chính là những chữ cái nằm giữa hai dấu dollar ($) trong cái địa chỉ tuyệt đối này. Bắt đầu làm nhé: dùng hàm MID() trích ra chữ cái từ vị trí thứ 2 trong địa chỉ tuyệt đối của cell: =MID(CELL(“Address”), 2, num_chars) Cái
khó là cái num_charsnày đây, vì tên cột thì có thể là 1, 2, hoặc 3 ký tự (ví dụ: A, AA hoặc AAA). Vận dụng hàm FIND thôi: FIND(“$”, CELL(“address””,A2), 3) – 2 Giải thích chút nhé: Dùng hàm FIND(), tìm vị trí của dấu $ trong cái địa chỉ tuyệt đối của cell, và bắt đầu tìm từ vị trí thứ 3 trong cái địa chỉ này. Tại sao phải trừ đi 2 ? Công thức trên sẽ chỉ ra vị trí (là một con số) của
dấu $ thứ hai trong địa chỉ tuyệt đối của cell, tức là cái dấu $ phía sau tên cột, Bây giờ, công thức hoàn chỉnh sẽ như sau: =MID(CELL(“Address”), 2, FIND(“$”, CELL(“address”), 3) – 2) Công thức này áp dụng cho chính cell chứa công thức. Ví dụ, muốn tìm tên của cell AGH68, bạn gõ: =MID(CELL(“Address”, AGH68), 2, FIND(“$”, CELL(“address”, AGH68), 3) – 2)→ AGH Hàm SUBSTITUTE Công dụng: Dùng để thay thế một chuỗi này bằng một chuỗi khác. Hàm này cũng tương tự hàm REPLACE(), nhưng dễ sử dụng hơn. Công thức: =SUBSTITUTE(text, old_text, new_text [,instance_num]) Ví dụ: đế thay số 2007 bằng 2008 trong câu Expense Budget for 2007 Dùng công thức như sau: =SUBSTITUTE(“Expense Budget for 2007″, “2007”, “2008”) →Expense Budget for 2008 Có nhiều chương trình có khả năng tìm kiếm một số đoạn văn và thay thế nó bằng đoạn văn khác. Hàm REPLACECông dụng: Dùng để thay thế một phần của chuỗi bằng một chuỗi khác, dựa vào số ký tự được chỉ định Công thức: =REPLACE(old_text, start_num, num_chars, new_text) Cái khó của hàm này là xác định được bởi start_num và num_chars. Làm sao biết được bắt đầu từ đâu và thay thế bao nhiêu chữ? Tôi gợi ý nhé: – Bạn dùng hàm FIND() hoặc SEARCH() để xác định vị trí bắt đầu (start_num) – Dùng hàm LEN() để xác định số ký tự của chuỗi sẽ được thay thế (num_chars) Ví dụ: đế thay số 2007 bằng 2008 trong câu Expense Budget for 2007 Dùng công thức như sau: =REPLACE(A1, FIND(“2007”, A1), LEN(“2007”), “2008”)→Expense Budget for 2008 Với A1 = Expense Budget for 2007 |