Giới thiệu 14 hàm Excel mới cho Office 365

Nếu bạn là người dùng Office 365 Insider, hôm nay Gitiho có tin vui dành cho bạn: Excel vừa cập nhật thêm 14 hàm mới. Cùng chúng mình tìm hiểu về các hàm Excel mới này và cách chúng sẽ giúp bạn đẩy nhanh tốc độ công việc trên trang tính của mình nhé.

Xem thêm: Giới thiệu các hàm trong Excel và các ví dụ minh họa dễ hiểu

Các hàm Excel mới trong Office 365 Insider

Tiếp nối sự thành công của các bản cập nhật dành cho người dùng Office 365 Insider, mới đây, Microsoft đã chính thức giới thiệu 14 hàm mảng động mới trong Excel. Với 14 hàm Excel này, bạn sẽ tiết kiệm được đáng kể thời gian khi phải làm việc với những công thức phức tạp trên trang tính. Quan trọng hơn, các hàm Excel này cực kì dễ dùng. Điều này nghĩa là bất kể trình độ tin học văn phòng của bạn ở level nào, bạn đều có thể thành thạo sử dụng các hàm Excel mới chỉ trong nháy mắt. Vậy thì chúng ta cùng làm quen ngay với các hàm này thôi.

Nhóm hàm Excel xử lý văn bản

Một trong các tác vụ xử lý văn bản chúng ta thường xuyên sử dụng nhất trong Excel chính là tách các chuỗi ký tự. Thông thường, chúng ta sẽ phải nhờ đến các hàm Excel như hàm SEARCH, hàm FIND, hàm LEFT, hàm RIGHT, hàm MID,… Tuy nhiên, đối với nhiều người, việc sử dụng cách hàm văn bản này là một thử thách không hề dễ dàng.

Thấu hiểu được khó khăn này của người dùng, Microsoft giờ đây đã cập nhật thêm các hàm Excel giúp xử lý văn bản dễ dàng hơn. Xin giới thiệu đến các bạn 3 hàm Excel mới: hàm TEXTBEFORE, TEXTAFTER và TEXTSPLIT.

• Hàm TEXTBEFORE - Trích xuất chuỗi ký tự đứng trước một ký tự nhất định trong cùng văn bản
• Hàm TEXTAFTER - Trích xuất chuỗi ký tự đứng sau một ký tự nhất định trong cùng văn bản
• Hàm TEXTSPLIT - Tách riêng các từ trong văn bản vào các cột hoặc hàng, sử dụng các ký tự ngăn cách

Hàm TEXTBEFORE

Hàm TEXTBEFORE có cú pháp như sau:

=TEXTBEFORE(text, delimiter, [instance_num], [ignore_case])

Trong đó:

• text - Văn bản cần trích xuất chuỗi trong đó
• delimiter - Ký tự ngăn cách
• [instance_num] - Sử dụng cho các ký tự ngăn cách xuất hiện nhiều lần trong văn bản, tham số này xác định số thứ tự của ký tự ngăn cách cần chỉ định
• [ignore_case] - Điền TRUE để hàm Excel phân biệt chữ hoa chữ thường, điền FALSE để không phân biệt chữ hoa chữ thường

Ví dụ mình có một danh sách tên tại cột A, mình muốn trích xuất riêng giá trị tên và bỏ đi giá trị họ, tên đệm. Để làm được điều này, mình sẽ sử dụng hàm TEXTBEFORE với ký tự ngăn cách là khoảng trắng đầu tiên trong văn bản tại ô A4 như sau:

=TEXTBEFORE(A4," ",1)

Trong đó:

• A4 - Tham chiếu đến ô chứa tên
• “ ” - Ký tự ngăn cách là ký tự khoảng trắng (đặt trong cặp dấu ngoặc đơn)
• 1 - Ký tự chỉ định để trích xuất là ký tự khoảng trắng đầu tiên

Có thể đến đây, bạn sẽ thấy công dụng của hàm TEXTBEFORE không khác gì hàm LEFT vì chúng đều trích xuất ký tự bên trái của một ký tự được chỉ định. Vậy thì thay vì trích xuất tên, bây giờ chúng ta sẽ thử trích xuất cả tên và tên đệm. Nếu như sử dụng hàm LEFT, bạn sẽ cần một công thức khá phức tạp để hoàn thành công việc này. Tuy nhiên, với hàm Excel TEXTBEFORE mới trong Office 365, tất cả những gì bạn cần là đổi tham số [instance_num] từ 1 thành 2.

Xem thêm: Hướng dẫn 4 cách tách tên trong Excel để tách riêng họ, tên và tên đệm

Hàm TEXTAFTER

Tương tự như hàm TEXTBEFORE, hàm TEXTAFTER có cú pháp như sau:

=TEXTAFTER(text, delimiter, [instance_num], [ignore_case])

Trong đó:

• text - Văn bản cần trích xuất chuỗi trong đó
• delimiter - Ký tự ngăn cách
• [instance_num] - Sử dụng cho các ký tự ngăn cách xuất hiện nhiều lần trong văn bản, tham số này xác định số thứ tự của ký tự ngăn cách cần chỉ định
• [ignore_case] - Điền TRUE để hàm Excel phân biệt chữ hoa chữ thường, điền FALSE để không phân biệt chữ hoa chữ thường

Mình sẽ thử tách tên trong Excel bằng hàm TEXTAFTER như sau:

=TEXTAFTER(A4," ",1)

Trong đó:

• A4 - Tham chiếu đến ô chứa tên
• “ ” - Ký tự ngăn cách là ký tự khoảng trắng (đặt trong cặp dấu ngoặc đơn)
• 1 - Ký tự chỉ định để trích xuất là ký tự khoảng trắng đầu tiên

Như bạn thấy, 2 hàm Excel TEXTBEFORE và TEXTAFTER có thể được coi là phiên bản nâng cao của các hàm Excel LEFT và RIGHT bởi độ linh hoạt được cải thiện hơn rất nhiều. Nhờ đó mà bạn sẽ không cần phải sử dụng các công thức phức tạp khi cần trích xuất văn bản nữa.

Hàm TEXTSPLIT

Nếu như các hàm Excel phía trên giúp bạn trích xuất chuỗi ký tự trong văn bản, thì hàm TEXTSPLIT lại có một tính năng hoàn toàn mới là tách riêng các từ trong văn bản vào các cột hoặc hàng.

Cú pháp hàm Excel đặc biệt này như sau:

=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [pad_with]) 

Trong đó:

• text - Văn bản cần tách ký tự
• col_delimiter - Ký tự xác định vị trí cần tách văn bản vào các cột khác nhau
• [row_delimiter] - Ký tự xác định vị trí cần tách văn bản vào các hàng khác nhau
• [ignore_empty] - Điền TRUE để tạo một ô tính trống giữa các phần được tách, bỏ qua hoặc điền FALSE để hủy tính năng này
• [pad_with] - Giá trị điền bù, mặc định là #N/A

Dưới đây là một ví dụ về tính năng tách văn bản vào các cột khác nhau của hàm TEXTSPLIT. Chỉ với một công thức đơn giản với hàm Excel này, bạn đã có thể tách riêng từng từ trong tên vào các cột khác nhau.

Xem thêm: Hàm TEXT trong Excel và những ứng dụng cụ thể

Nhóm hàm Excel xử lý mảng

Kể từ khi giới thiệu mảng động trong Excel vào năm 2019, Microsoft đã cập nhật rất nhiều công thức hàm và tính năng mới để giúp người dùng tối ưu trải nghiệm với mảng động. Trong bản cập nhật lần này, bạn cũng sẽ tìm thấy 11 hàm Excel mới giúp xử lý mảng nhanh chóng và dễ dàng hơn. Hãy cùng mình tìm hiểu về các hàm Excel này nhé.

Các hàm Excel gộp dữ liệu mảng

Khi bạn muốn gộp dữ liệu trong Excel, có thể bạn sẽ gặp ít nhiều khó khăn khi các mảng dữ liệu không cố định về kích cỡ. Vậy thì hàm VSTACK và hàm HSTACK chính là giải pháp dành cho bạn. Đây là 2 hàm Excel kết hợp dữ liệu từ các mảng theo chiều dọc và chiều ngang.

• Hàm VSTACK - Kết hợp các mảng theo chiều ngang
• Hàm HSTACK - Kết hợp các mảng theo chiều dọc

Cú pháp của 2 hàm Excel này cực kì đơn giản như sau:

=VSTACK(array1,[array2],…)

và

=VSTACK(array1,[array2],…)

Trong đó array1, [array2],… là các mảng cần kết hợp.

Giả sử mình có 2 mảng dữ liệu trên trang tính như trong hình dưới đây và mình sẽ thực hiện gộp dữ liệu trong 2 mảng này thành một mảng lớn. Nhờ có hàm Excel VSTACK và HSTACK, bạn có thể thấy thao tác gộp dữ liệu trở nên cực kỳ đơn giản.

Xem thêm: Hướng dẫn sử dụng công thức mảng trong Excel qua ví dụ cụ thể

Các hàm Excel sắp xếp dữ liệu mảng

Tiếp theo, mình sẽ giới thiệu đến bạn các hàm Excel mới với chức năng sắp xếp dữ liệu mảng theo một form nhất định. Giả sử bạn cần chuyển một mảng dữ liệu thành một danh sách trong một cột hoặc một hàng duy nhất, bạn sẽ làm thế nào? Có vẻ đây sẽ là một thử thách thật sự, nhưng đừng lo, vì giờ đây bạn đã có các hàm Excel giúp hoàn thành công việc này chỉ với một nút enter.

• Hàm TOROW - Sắp xếp dữ liệu mảng thành một hàng duy nhất
• Hàm TOCOL - Sắp xếp dữ liệu mảng thành một hàng duy nhất

Cú pháp của hàm Excel TOROW và TOCOL tương tự nhau như sau:

=TOROW(array, [ignore], [scan_by_column])

và

=TOCOL(array, [ignore], [scan_by_column])

Trong đó:

• array - Mảng dữ liệu cần chuyển đổi
• [ignore] - Định dạng dữ liệu cần bỏ qua. Theo mặc định, hàm Excel không bỏ qua bất kỳ định dạng dữ liệu nào.
• [scan_by_column] - Scan mảng theo các cột. Theo mặc định, hàm Excel sẽ scan mảng theo hàng.

Với yêu cầu chuyển đổi một mảng dữ liệu trên Excel về một cột hoặc một hàng nhất định, chúng ta chỉ cần sử dụng hàm TOROW và TOCOL như trong hình dưới đây.

Vậy nếu cần chuyển đổi dữ liệu từ một cột hoặc một hàng thành một mảng thì phải làm thế nào? Xin giới thiệu 2 hàm Excel mới sẽ giúp bạn làm được điều này một cách dễ dàng.

• Hàm WRAPROWS - Sắp xếp dữ liệu trong một hàng thành một mảng 2D
• Hàm WRAPCOLS - Sắp xếp dữ liệu trong một cột thành một mảng 2D

Cú pháp của 2 hàm Excel này như sau:

=WRAPROWS(vector, wrap_count, [pad_with])

và

=WRAPCOLS(vector, wrap_count, [pad_with])

Trong đó:

• vector - Vector hoặc tham chiếu tới cột và hàng cần thao tác
• wrap_count - Số lượng tối đa các giá trị cho một cột/hàng trong mảng mới
• pad_with - Giá trị điền bù, mặc định là #N/A

Giả sử mình có một dải các giá trị được trình bày chỉ trong một hàng Excel. Để sắp xếp lại các giá trị này vào một mảng 2D với tối đa 3 giá trị trong một hàng, mình sử dụng công thức hàm WRAPROWS như trong hình dưới đây:

=WRAPROWS(A3:I3,3)

Trong đó:

• A3:I3 - Tham chiếu tới hàng cần thao tác
• 3 - Số lượng giá trị tối đa trong một hàng khi tạo mảng mới

Tương tự như vậy, mình có một cách sắp xếp dữ liệu khác khi sử dụng hàm Excel WRAPCOLS:

=WRAPCOLS(A3:I3,3)

Trong đó:

• A3:I3 - Tham chiếu tới hàng cần thao tác
• 3 - Số lượng giá trị tối đa trong một cột khi tạo mảng mới

Các hàm Excel điều chỉnh kích cỡ mảng

Mảng dữ liệu lớn quá, phải làm thế nào bây giờ? Chớ vội lo lắng, bởi bạn đã có thể sử dụng hàm TAKE và DROP. Đây là các hàm Excel giúp bạn thu gọn mảng dữ liệu của mình bằng cách xác định số dòng cần giữ lại hoặc bỏ đi các dòng cụ thể trong mảng. Tương tự như vậy, các hàm mới CHOOSEROWS và CHOOSECOLS sẽ giúp bạn chọn ra các hàng hoặc các cột cụ thể để giữ lại trong mảng.

Thay vì thu nhỏ kích cỡ mảng, nếu bạn muốn thêm các hàng và cột vào trong mảng, bạn cũng có thể sử dụng hàm Excel để làm điều đó. Hàm EXPAND sẽ giúp bạn mở rộng mảng dữ liệu của mình, tất cả những gì bạn cần làm là điền thêm các trường dữ liệu mới.

Tóm lại, dưới đây là các hàm Excel giúp bạn điều chỉnh kích cỡ mảng dữ liệu:

• Hàm TAKE - Giữ lại n hàng hoặc cột trong mảng tính từ đầu hoặc cuối mảng
• Hàm DROP - Bỏ đi n hàng hoặc cột cụ thể trong mảng tính từ đầu hoặc cuối mảng
• Hàm CHOOSEROWS - Giữ lại các hàng cụ thể trong mảng
• Hàm CHOOSECOLS - Giữ lại các cột cụ thể trong mảng
• Hàm EXPAND - Mở rộng mảng đến một kích cỡ nhất định

Tổng kết

Vậy là chúng ta đã làm quen với 14 hàm Excel mới cập nhật trong Excel 365. Như bạn có thể thấy, tất cả các hàm mới này đều sở hữu cú pháp vô cùng đơn giản, giúp cho tất cả người dùng đều có thể sử dụng trơn tru mà không gặp phải bất kỳ trở ngại nào. Nếu như bạn đang gặp phải các rắc rối khi công thức hàm Excel quá phức tạp, chắc chắn các hàm mới này là giải pháp dành cho bạn đấy.

Hiện tại, các hàm Excel này mới chỉ hoạt động với phiên bản Beta của Office 365 Insider. Tuy nhiên, chắc chắn trong tương lai gần, Microsoft sẽ cập nhật các hàm hữu dụng này tới toàn bộ người dùng của các phiên bản Excel. Trong thời gian chờ đợi, bạn hãy tham khảo các bài viết về chủ đề Excel trên blog Gitiho để tích lũy kiến thức về hàm Excel và các thủ thuật hữu ích nhất nhé.

Gitiho xin cảm ơn và hẹn gặp lại bạn đọc trong các bài viết tiếp theo!