Hàm subtitute là gì? Sử dụng hàm Substitute có khó như bạn nghĩ?

Các hàm excel cơ bản là phần kiến thức quan trọng trong tin học văn phòng. Trong số đó có hàm substitute, như một trợ thủ đắc lực giúp mọi nhân viên văn phòng giải quyết những yêu cầu phức tạp…

Hàm substitute hay bất cứ các hàm excel cơ bản nào đều có một chức năng riêng của nó. Khi vận dụng những hàm tính quan trọng này vào trong thao tác công việc hàng ngày, nó sẽ giúp chúng ta tiết kiệm được phần lớn thời gian và công sức, cũng góp phần làm cho hiệu quả công việc tăng lên đáng kể. Vậy các hàm substitute là gì? Nó có gì giống và khác so với các hàm excel cơ bản khác? Hãy cùng đến với bài viết sau để hiểu được năng lực đặc biệt của hàm substitute là gì nhé! 

I. Tổng quan về hàm substitute

1. Công thức tính của hàm Substitute 

Công thức của hàm substitute là gì? Nó sẽ được trình bày như sau: 

=SUBSTITUTE (text, old_text, new_text, [instance_num])

hay rút gọn công thức hàm excel phức tạp trên ta sẽ được: 

=SUBSTITUTE(text,a,a1,a2,a3)

Trong đó: 

• Text: Văn bản sẵn của hàm excel có lúc đầu mà bạn muốn thay thế. Text trong các hàm excel cơ bản có thể là công thức, ký tự, địa chỉ, đường link...
• a: Văn bản trước đó bạn muốn được thay mới thông qua hàm substitute
• a1: Văn bản mới hoàn toàn trong hàm excel mà bạn muốn thay  thế cho văn bản cũ trước đó
• a2: Vị trí mà văn bản cũ xuất hiện mà bạn muốn thay bằng văn bản mới bằng hàm substitute
• a3: Vị trí thực hiện thay thế văn bản cũ trước đó (a1)

2.    Ví dụ về hàm Substitute trong các hàm excel cơ bản

Cho bảng dữ liệu, cùng với yêu cầu: Sử dụng hàm SUBSTITUTE để thực hiện thay thế các dữ liệu có trong văn bản bằng khoảng trắng

Cú pháp của hàm excel sẽ như sau: =SUBSTITUTE(A5,"www.","") sẽ cho kết quả trả về là hoctinhocmienphi.vn

Tương tự, tiếp tục kéo thả chuột xuống ô phía dưới ta được kết quả như bảng sau:

Tiếp tục kéo thả chuột xuống ô phía dưới

Từ bảng dữ liệu sau đây, hãy tiến hành thay thế một văn bản trước đó thành văn bản mới.

Công thức tính như sau: =SUBSTITUTE(A7,“1”,“4”,1) sẽ được kết quả là các thông tin bắt đầu từ ngày 4 tháng 9

Như vậy qua ví dụ hàm substitute trên có thể hiểu: 

• Ký tự cũ của hàm excel là tại địa chỉ ô A7: bắt đầu từ ngày 1 tháng 9
• Ký tự được thay thế hoàn toàn mới tại địa chỉ ô B7: bắt đầu từ ngày 4 tháng 9

Như vậy, lấy ra 1 ký tự trong bảng dữ liệu excel ta được kết quả như bảng bên dưới:

Lấy ra 1 ký tự trong bảng excel

3. Công dụng  tiện ích mang lại của hàm Substitute là gì

• Hàm SUBSTITUTE là một trong các hàm excel hữu dụng nhất, được ứng dụng để thực hiện thay đổi hay rút ngắn cột họ tên học sinh, tên nhân viên...
• Ngoài ra, hàm SUBSTITUTE được sử dụng để thực hiện công việc lập các bản báo cáo theo tuần, tháng, năm… từ đó rút ngắn thời gian lập nhiều bản báo cáo khác nhau.
• Thay vì việc hàng ngày bạn thường xuyên phải cập nhật liên tục các báo cáo mới bằng những cách làm truyền thống, thông thường thì việc sử dụng hàm SUBSTITUTE trong các hàm excel cơ bản để rút ngắn thời gian, từ đó quỹ thời gian của bạn sẽ phục vụ rất nhiều lợi ích.

II. Cách sử dụng hàm substitute là gì

1. Thay thế các thứ tự khác nhau trong bảng tính excel bằng một công thức (thực hiện lồng các hàm substitute)

Như trường hợp với hàm REPLACE, bạn có thể lồng các hàm SUBSTITUTE là gì trong một công thức nhất định để thực hiện một số thay thế cùng lúc, tức là tiến hành thay thế nhiều ký tự bằng một công thức.

Giả sử bạn có một chuỗi văn bản cụ thể như “PR1, ML1, T1” trong ô A2, trong đó ký tự “PR” là viết tắt của “Project”, ML thể hiện viết tắt của “Milestone” và “T” có nghĩa là “Task”. Theo đó, bạn muốn thay thế 3 ba mã này với ý nghĩa và tên đầy đủ. Để đạt được điều này, bạn có thể thực hiện viết 3 công thức hàm SUBSTITUTE khác nhau:

= SUBSTITUTE (A2, “PR”, “Project “)
= SUBSTITUTE (A2, “ML”, “Milestone “)
= SUBSTITUTE (A2, “T”, “Task “)

Và sau đó tiếp tục bằng việc lồng với nhau trong 1 công thức:

= SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (A2, “PR”, “Project “), “ML”, “Milestone “), “T”, “Task “)

Lưu ý rằng chúng ta sẽ cần thêm một dấu cách vào cuối mỗi đối số new_text để có thể đọc được dữ liệu tốt hơn.

T

hêm một dấu cách vào cuối mỗi đối số new_text

2. Sử dụng hàm Substitute để bỏ dấu cách trong danh sách dữ liệu số điện thoại hiệu quả 

Ta sử dụng hàm SUBSTITUTE trong excel để làm liền khoảng cách của các số trong một số điện thoại cụ thể như sau:

Sử dụng hàm SUBSTITUTE trong excel

=SUBSTITUTE(A2,“ ”,“”)

Hàm SUBSTITUTE sẽ tiến hành thực hiện tham chiếu tới ô A2, sau đó lần lượt thay thế những ký tự khoảng trắng bằng cách không nhập gì cả. Đối số Instant_num ở đây ta cố tình không nhập để hàm SUBSTITUTE thay thế toàn bộ dấu cách thể hiện trong chuỗi.

Sau khi xử lý, để chuyển toàn bộ số về định dạng number, ta nhập lần lượt như sau:
=–SUBSTITUTE(A2,” “,””) hoặc =VALUE(SUBSTITUTE(A2,” “,””))

III. 7 lỗi Excel thường gặp nhất đối với dân văn phòng

1. #VALUE!

Excel có thể hiển thị lỗi #VALUE khi nó phát hiện trong công thức hàm substitute có khoảng trống, ký tự hoặc text mà đáng lẽ ra vị trí tương ứng đó phải được nhập số.

Lỗi #VALUE

Trừ trường hợp sử dụng nhập vào dấu ngoặc kép, nếu không, Excel chỉ cho phép nhập số. Do vậy, nếu như sau khi bạn nhập các dữ liệu mà Excel không có phản hồi hoặc báo lỗi trong hàm substitute thì chứng tỏ công thức đó đã chứa đối số "lạ".

Cách đơn giản để sửa lỗi này trong hàm substitute đó là nhấp chuột hai lần vào công thức tương ứng và chắc chắn là bạn chỉ nhập các con số. Nếu hàm excel vẫn báo lỗi, hãy kiểm tra các Cell khác, xem thử liệu bạn đã thực hiện nhập giá trị vào những Cell đó chưa hoặc chúng có chứa những ký tự lạ hay không?

Trong ví dụ trên, ta có thể thấy cột "Sum" ám chỉ các cell trống (chưa được nhập giá trị vào). Theo đó thì hàm Excel, hàm substitute không thể tính toán tổng của những ô không có chứa giá trị, thế nên, nó sẽ lập tức báo lỗi.

2. #####

Lỗi #####

Khi nhìn thấy ký tự ##### được hiển thị trong Cell, một vài người mới bắt đầu sử dụng hàm Excel hay hàm substitute sẽ cảm thấy có chút bối rối. Tuy nhiên, tin tốt là lỗi này đơn giản chỉ có nghĩa là cột giá trị đó không đủ rộng để chứa và hiển thị hết các giá trị mà bạn đã nhập vào và tất nhiên, cách giải quyết thì lại vô cùng dễ.

Cách sửa: Đưa con trỏ chuột vào mép phải của phần tên cột, tương ứng với cột hoặc dòng mà bạn muốn chỉnh độ rộng. Khi con trỏ chuột chuyển đã được chuyển thành hình mũi tên giao với đường kẻ như hình dưới thì bạn chỉ việc nhấn và giữ chuột.

Nhấn và giữ chuột

Ngoài ra, bạn có thể chỉ cần nhấp chuột hai lần vào vị trí này để excel có thể tự động điều chỉnh chiều rộng của ô.

3. #DIV/0!

Khi xảy ra lỗi #DIV/0! Trong hàm substitute hay bất cứ hàm excel nào nghĩa là bạn nhập sai công thức (chẳng hạn là “=4/0”) hoặc cũng có thể khi công thức bạn vừa nhập tham chiếu đến một ô bằng 0 hoặc để trống. Trong Excel, biểu thức này là công thức không thể thực hiện.

Cách sửa:

Cách sửa

Hãy chắc chắn là số chia xuất hiện trong hàm substitute hoặc công thức không phải là ô có giá trị bằng 0 hoặc ô trống. 

4. #REF!

Lỗi #REF! xuất hiện khi công thức của hàm substitute tham chiếu đến một ô giá trị không hợp lệ, thường xảy ra nhất khi ô được tham chiếu bởi công thức đã bị xóa đi khỏi excel hoặc dán.

Lỗi #REF!

Chẳng hạn, trong hình ảnh dưới, cột có giá trị Outcome trong hàm substitute được tham chiếu bởi công thức: =SUM(A2,B2,C2). Nếu như bạn thực hiện xóa cột "Number 2" thì Excel sẽ lập tức báo lỗi #REF!

Excel sẽ lập tức báo lỗi

Cách sửa:

• Nếu bạn đã vô tình xóa hàng hoặc cột, hãy thực hiện bấm nút Undo (hoàn tác) trên thanh công cụ truy cập nhanh (chính là Quick Access Toolbar) hoặc nhấn tổ hợp phím lệnh trên bàn phím Ctrl + Z (đối với Windows) hoặc Command + Z (đối với các phiên bản Mac).
• Điều chỉnh công thức substitute để nó sử dụng một tham chiếu phạm vi thay vì những phần ô riêng lẻ, chẳng hạn như =SUM(A1;B1). Lúc này, khi thực hiện xóa bất kỳ cột nào trong phạm vi tổng thì Excel sẽ lập tức tự động điều chỉnh công thức tương ứng.

5. #NULL!

Lỗi #NULL! trong hàm substitute xảy ra khi bạn dùng toán tử phạm vi ô không đúng trong công thức hoặc khi bạn thực hiện dùng toán tử giao (ký tự khoảng trống) giữa tham chiếu phạm vi ô để có thể xác định giao điểm của hai phạm vi không giao cắt.

Lỗi #NULL!

Để có thể dễ hiểu hơn, bạn cần nhớ hai điều sau:

• Dấu hai chấm (:) là phần ký tự để phân tách ô đầu tiên khỏi ô cuối cùng khi bạn tham chiếu đến phạm vi ô liên tục được hiển thị trong công thức, chẳng hạn như =SUM (A1:A5) có ý nghĩa tham chiếu đến phạm vi bao gồm các ô A1 đến A5.
• Dấu phẩy (,) là toán tử liên kết khi bạn thực hiện tham chiếu đến hai vùng không giao nhau. Chẳng hạn, nếu công thức tính tổng hai phạm vi (=SUM (A1:A5, B11:B5)), hãy chắc chắn bạn đã sử dụng dấu phẩy phân tách giữa chúng.

Chắc chắn bạn đã sử dụng dấu phẩy phân tách giữa chúng

Cách sửa:

• Đầu tiên, hãy thực hiện kiểm tra xem thử bạn đã sử dụng đúng cú pháp trong công thức substitute hay chưa.
• Thứ hai, nếu hàm excel vẫn xuất hiện lỗi này thì chứng tỏ bạn đã sử dụng dấu cách giữa các phạm vi không giao nhau, lúc này, hãy thay đổi tham chiếu hoặc cẩn thận sử dụng đúng các dấu.

6. #N/A

Khi xuất hiện lỗi #N/A trong hàm substitute, nghĩa là các con số đang tham chiếu trong công thức không thể tìm thấy. Ngoài ra, một nguyên nhân phổ biến khác của lỗi này đó là khi sử dụng hàm VLOOKUP, HLOOKUP, LOOKUP hoặc MATCH và một công thức bất kỳ không thể tìm một giá trị có thể được tham chiếu, chẳng hạn như giá trị cần thực hiện tra cứu không tồn tại trong dữ liệu nguồn.

Cách sửa:

Tiến hành kiểm tra lại tất cả các công thức và xem thử có sheet hoặc hàng trong excel nào đã bị xóa hoặc được tham chiếu không chính xác. 

7. #NUM!

Nếu công thức hàm substitute chứa các giá trị số không hợp lệ, lỗi #NUM! sẽ ngay lập tức xuất hiện. Thông thường, tình huống lỗi này xảy ra khi bạn đã thực hiện nhập giá trị số dùng kiểu dữ liệu hoặc định dạng số không được hỗ trợ trong phần đối số của công thức, chẳng hạn như không thể nhập dữ liệu giá trị $1,000 theo định dạng tiền tệ.

Cách sửa:

Kiểm tra liệu rằng bạn có thực hiện nhập định dạng tiền tệ, ngày hay biểu tượng đặc biệt vào công thức hay không? Sau đó, hãy loại bỏ chúng khỏi công thức và chú ý chỉ giữ lại các con số.

IV. 10 Tips để tạo một report xuất sắc trên excel

1. Chọn định dạng file cho workbook – giúp tăng tốc độ tính toán và tiết kiệm đến 73,65% bộ nhớ.

Bạn có biết rằng chọn đúng loại file workbook là gì cũng có thể bảo vệ dữ liệu của bạn khỏi những lỗi hư hỏng của dữ liệu trong tương lai. Điều này thực sự đúng và nó đã được kiểm chứng kỹ càng bởi các chuyên gia.

Theo kinh nghiệm quý báu của rất nhiều online reviews thì việc lưu excel report với định dạng file có đuôi “.xlsb” (excel binary workbook) sẽ làm giảm kích cỡ workbook của bạn và quan trọng hơn nữa là nó hỗ trợ cho tất cả.

Ví dụ, nếu bạn có một lượng dữ liệu excel khổng lồ trong report của mình và bạn muốn sử dụng macros để có thể khiến nó linh hoạt hơn thì lựa chọn sao lưu lý tưởng của bạn nên là định dạng binary workbook (dạng nhị phân).

2. Version control – Học cách sao lưu dữ liệu

Bạn chỉ cần đơn giản tạo một folder trong máy tính, nơi chứa tất cả các bản excel đã lưu và đặt tên cho nó để hiểu và dễ nhớ là “bản sao lưu”. Rồi hãy thực hiện lưu bản copy mới nhất vào folder đó. Tương tự, bất cứ khi nào bạn tiến hành thay đổi dữ liệu hay updates, bạn phải nhấn vào phần “Save as” và lưu theo thứ tự v1 hay v2 hay v3…

Bằng cách làm này, bạn sẽ có thể lưu được hầu hết các bản theo thứ tự, vì vậy bạn có thể ngay lập tức xem lại và chọn bản mình muốn bất cứ lúc nào.

3. Dùng màu để phân biệt ô dữ liệu và ô công thức

Hầu hết các chuyên gia hiện nay thường sử dụng màu vàng “light yellow” để chỉ ô chứa dữ liệu, nơi người dùng có thể thực hiện nhập dữ liệu.

Dùng màu để phân biệt ô dữ liệu và ô công thức

Và cũng có thể sử dụng màu bạc “light gray’ để bôi vào các ô công thức, không được sửa.

4. Xóa bỏ những formulas mà bạn không mong muốn

Sử dụng quá nhiều công thức trong hàm có thể gây ra lỗi trình bày nặng nề trong bản báo cáo của bạn. Ví dụ, bạn dùng hàm vlookup trong excel để kéo dữ liệu từ workbook khác nhưng lại lơ đãng quên xóa nó đi.

Những formulas theo đó không cần thiết sẽ làm report hay các hàm cơ bản như hàm substitute chạy chậm hơn và mất thời gian tính toán. Tệ nhất là có thể crash report khi nó đang làm việc.

5. Excel Array formulas- chỉ sử dụng khi không còn lựa chọn nào khác

Các công thức mảng (hay là array formulas) là những công thức tinh vi nhất tồn tại trong hàm excel, được sử dụng để thực hiện biểu diễn các phép tính phức tạp với một hoặc hai điều kiện đi kèm.

Khoảng 95% người dùng không biết đến các công cụ như CSE (Ctrl+Shift+Enter) or Array formulas. Vì vậy, để tốt cho bản thân, hãy dành thời gian học về nó

Có rất nhiều lợi ích và tác dụng khi sử dụng array formulas, nhưng không giống như các hàm khác, chúng có thể mất kiểm soát nếu bạn sử dụng nó không cẩn thận.

Khi dùng hàm này thực hiện thao tác trong report, nó có thể khiến máy bạn mất 3-4 phút để update hay refresh

Vì vậy, sẽ tốt hơn nếu bạn xác định được và biết khi nào và ở đâu nên sử dụng hàm này để không phải tốn quá nhiều thời gian và công sức.

6. Name ranges

Rất nhiều người dùng excel cảm thấy việc sử dụng tên cho dãy thì hiệu quả hơn là việc cứ để các ô mặc định. Bởi vì con người thường khó nhớ thông tin về các dãy số và chữ không liên quan đến nhau. Nhưng với Excel thì chúng thậm chí có thể khó nhớ hơn bởi vì các ô sẽ chứa cả chữ cái, số và ký hiệu.

Name ranges

Ví dụ: dãy ký tự “Revenue – Cost = Margin” (Doanh thu – Chi phí= Lợi nhuận) luôn dễ nhớ hơn là hàm như “A1-B1=C1”.

Những bản báo cáo được áp dụng named ranges trong formulas sẽ được người đọc đánh giá cao hơn. Đặc biệt nó sẽ trực tiếp giúp đơn giản hóa công thức của bạn.

7. Dùng “Sort” để tăng tốc độ tính toán

Excel sort thực hiện sẽ sắp xếp dữ liệu theo hệ thống dựa trên điều kiện mà bạn chọn.

Vì khi chúng cần phải xóa dữ liệu theo một thứ tự nào đó, các đối tượng trong Excel sẽ không cần mất công tìm kiếm mà thay vào đó chỉ cần thực hiện xóa dữ liệu đã được sắp xếp một cách nhanh chóng.

8. Calculation options (lựa chọn tính toán)

Hiểu đơn giản, nó là lựa chọn được đưa ra cho người dùng để có thể quyết định xem excel sẽ thực hiện tính toán khi có update hay nó sẽ thực hiện các thao tác này thường xuyên.

Bạn có thể tìm thấy calculations options dưới phần tiện ích tab Formulas. Nếu ở phần cài đặt mặc định để tùy chọn “automatic”, có nghĩa là mỗi khi bạn tạo sự thay đổi dữ liệu với các file excel thì nó sẽ được phản ánh trong đó một cách tự động.

Còn nếu là “manual”, có nghĩa là sự thay đổi mà bạn thực hiện sẽ không phản ánh tự động mà thay vào đó người dùng phải nhấn vào refresh mọi lúc để update kết quả.

9. Xử lý lỗi

Một trong những thách thức lớn nhất là đảm bảo report hay hàm substitute của bạn không bị dính phải các lỗi thông thường.

Các loại lỗi

• Excel defined errors: Hầu hết người dùng excel cơ bản đều nhận ra được các lỗi này bởi chúng được định nghĩa trong đó sẵn. Ví dụ, nếu bạn thấy #DIV0! trong report, nó có nghĩa là công thức của bạn đã bị chia cho giá trị 0. Mà chia cho 0 thì không có ý nghĩa và hàm excel không tính được. Trong trường hợp này, bạn có thể dùng công cụ IFERROR or ISERROR formulas để xác định lỗi
• User specific errors: Những lỗi này xuất hiện là do sai lầm trong thao tác của người dùng. Tác động của những lỗi này thường cao hơn so với lỗi được xác định sẵn trong Excel. Vấn đề lớn nhất là không có hệ thống nào trong excel phát hiện và thay đổi người dùng.

10. Home page, user guide & presentation

Cách bạn trình bày như thế nào trong hàm exel hay hàm substitute cũng quan trọng không kém formulas, VBA code & logic’s trong report. Nó không chỉ là cách thể hiện màu sắc và hình ảnh, mà còn là cách bạn tạo bảng, cách sắp xếp, tận dụng tất cả các tính năng trong đó để đưa ra kết quả tốt nhất.

Tips để làm đẹp bản report

• Nếu bạn có nhiều worksheet trong report của mình thì nên sử dụng Home page và dùng công cụ hyperlink để link chúng lại với nhau.
• Hãy thêm những cách hướng dẫn sử dụng report (bạn không cần viết từng điểm một, chỉ cần một bản tóm tắt đi kèm)
• Trình bày dữ liệu của bạn với đồ thị, biểu đồ
• Sử dụng conditional formatting khi biểu diễn các thông tin trend.

Lưu ý: Sử dụng quá nhiều định dạng cũng có thể làm tăng kích thước file workbook và dẫn đến crash, vì vậy hãy  xem xét thật kỹ để sử dụng tối thiểu format với tương ứng tối đa lợi ích.

V. Kết luận

Trên đây là những thông tin về hàm substitute trong excel và bạn có thể thấy rằng thực sự thì nó không hề khó. Việc vận dụng các hàm excelthành thạo sẽ là chìa khóa giải quyết khá nhiều rắc rối của dân văn phòng ngày nay. Hãy đến với các bài viết về các hàm excel cơ bản sau của 123job để tích lũy thêm cho mình những kiến thức về Excel bạn nhé!