Microsoft thông báo Excel vừa cập nhật thêm 14 hàm mới cho phiên bản Office Insider 365. Cùng UniTrain tìm hiểu về các hàm Excel mới này và cách chúng được thiết kế để giúp bạn dễ dàng thao tác văn bản và mảng trong trang tính của mình hơn.
Các chức năng thao tác với văn bản trong Excel
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ự. Bạn có thể thực hiện việc này với tổ hợp các 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.
Để giúp dễ dàng hơn trong việc trích xuất văn bản từ đầu hoặc cuối nội dung của ô, 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.
Cú pháp: =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
Cú pháp: =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
Cú pháp: =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
Xem ví dụ dưới đây để hiểu hơn:
Các chức năng thao tác trên mảng trong Excel
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. Để giúp dễ dàng hơn trong việc xây dựng các bảng tính hấp dẫn bằng cách sử dụng mảng động, Microsoft sẽ phát hành một bộ sưu tập gồm 11 hàm thao tác mảng mới.
Kết hợp các 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:
Cả hai đều có cú pháp đơn giản như sau:
=VSTACK(array1,[array2],…)
=HSTACK(array1,[array2],…)
Trong đó: array1, [array2],… là các mảng cần kết hợp.
Xem ví dụ dưới đây để hiểu hơn:
Mảng định hình
Thật khó để thay đổi “hình dạng” của dữ liệu trong Excel, đặc biệt là từ mảng thành danh sách và ngược lại. Nếu bạn thấy mình có mảng hai chiều mà bạn muốn chuyển đổi thành danh sách đơn giản, hãy sử dụng TOROW và TOCOL để chuyển đổi mảng 2D thành một hàng hoặc cột dữ liệu.
Sử dụng các hàm WRAPROWS và WRAPCOLS, hãy làm ngược lại: tạo một mảng 2D có chiều rộng hoặc chiều cao được chỉ định bằng cách “gói” dữ liệu vào dòng tiếp theo (giống như văn bản trong tài liệu này) sau khi đạt đến giới hạn chiều rộng/chiều cao đã chọn của bạn.
Cú pháp của hai hàm như sau:
=TOROW(array, [ignore], [scan_by_column])
=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.
Cú pháp của 2 hàm Excel này như sau:
=WRAPROWS(vector, wrap_count, [pad_with])
=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
Xem ví dụ cả 4 hàm:
Thay đổi kích thướ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 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 giúp mở rộng mảng dữ liệu, bạn chỉ cần điền thêm các trường dữ liệu mới.
Cú pháp của 2 hàm Excel này như sau:
=TAKE(array, rows,[columns])
=DROP(array, rows,[columns])
Trong đó:
– array: Mảng để lấy hàng hoặc cột.
– rows: Số hàng cần lấy.
– columns: Số lượng cột cần lấy.
Hàm CHOOSEROWS – Giữ lại các hàng cụ thể trong mảng
Cú pháp: =CHOOSEROWS(array,row_num1,[row_num2],…)
Trong đó:
– array: mảng dữ liệu cần giữ lại (bắt buộc)
– row_num1: số hàng được trả về (bắt buộc)
– [row_num2]: số hàng bổ sung sẽ được trả lại (không bắt buộc)
Hàm CHOOSECOLS – Giữ lại các cột cụ thể trong mảng
Cú pháp: =CHOOSECOLS(array,col_num1,[col_num2],…)
Trong đó:
– col_num1: số cột được trả về (bắt buộc)
– col_num2: các số cột bổ sung sẽ được trả lại (không bắt buộc)
– rows: số hàng trong mảng được mở rộng. Nếu thiếu, các hàng sẽ không được mở rộng.
– columns: số cột trong mảng được mở rộng. Nếu thiếu, các cột sẽ không được mở rộng.
– pad_with: giá trị điền bù, mặc định là #N/A
UniTrain lược dịch