Khi sử dụng dữ liệu trong Excel đã được nhập từ một nguồn khác, văn bản thường không phải là cách bạn muốn xem
Bạn có thể thấy rằng các tham chiếu sản phẩm bao gồm mã sản phẩm, tham chiếu mã và kích thước sản phẩm, tất cả được nối thành một đoạn văn bản xuất hiện trong một ô trong trang tính
Yêu cầu của bạn là bạn muốn tách các phần có liên quan của chuỗi văn bản để tất cả chúng xuất hiện trong các ô riêng lẻ trong trang tính của bạn
Điều này có thể là do bạn muốn sắp xếp hoặc nhóm theo một phần cụ thể của văn bản. Bạn cũng có thể muốn sử dụng các công thức khác trên phần chia cụ thể đó. Ví dụ: bạn có thể muốn thực hiện tra cứu trên một bảng dữ liệu khác, sử dụng phần văn bản đó làm giá trị tra cứu. Điều này là không thể với dữ liệu ở dạng ban đầu
Nếu bạn đang sử dụng dữ liệu trong bảng tổng hợp, bạn có thể yêu cầu một trường riêng có dữ liệu được phân tách để cho phép lọc hoặc hợp nhất
Bài viết này mô tả bảy cách mà bạn có thể trích xuất N ký tự đầu tiên hoặc cuối cùng từ một chuỗi dữ liệu văn bản trong Microsoft Excel
Dữ liệu mẫu được sử dụng trong bài đăng này
Các ví dụ trong bài đăng này sẽ trích xuất 2 ký tự đầu tiên và cuối cùng từ ProductSKU trong tập hợp dữ liệu sản phẩm nhỏ ở trên. 2 ký tự đầu trong SKU chứa mã chủng loại sản phẩm và 2 ký tự cuối chứa kích thước sản phẩm
Trích xuất ký tự với hàm LEFT và RIGHT
Excel có một số hàm hữu ích để tách các chuỗi văn bản để lấy văn bản thực mà bạn cần
Chức năng TRÁI
cú pháp
LEFT [ Text, [Number] ]- Văn bản – Đây là chuỗi văn bản mà bạn muốn trích xuất từ. Nó cũng có thể là một tham chiếu ô hợp lệ trong sổ làm việc
- Số [Tùy chọn] – Đây là số ký tự mà bạn muốn trích xuất từ chuỗi văn bản. Giá trị phải lớn hơn hoặc bằng 0. Nếu giá trị lớn hơn độ dài của chuỗi văn bản, thì tất cả các ký tự sẽ được trả về. Nếu giá trị bị bỏ qua, thì giá trị được giả định là một
Điều này sẽ trả về số ký tự đã chỉ định từ chuỗi văn bản, bắt đầu từ phía bên trái của văn bản. Nó sẽ trích xuất số ký tự được chỉ định đầu tiên từ văn bản
QUYỀN Chức năng
cú pháp
RIGHT [ Text, [Number] ]Các tham số hoạt động theo cách tương tự như đối với hàm LEFT được mô tả ở trên
Điều này sẽ trả về số ký tự đã chỉ định từ chuỗi văn bản, bắt đầu từ phía bên phải của văn bản. Nó sẽ trích xuất số ký tự được chỉ định cuối cùng từ văn bản
Trích xuất các ký tự có văn bản thành cột
Văn bản thành Cột cho phép bạn chia cột thành các phần bằng cách xác định các giới hạn cố định cho phần văn bản của bạn. Để sử dụng phương pháp này
- Chọn tất cả dữ liệu trong cột ProductSKU trong dữ liệu mẫu
- Nhấp vào tab Dữ liệu trong dải băng Excel
- Bấm vào biểu tượng Văn bản thành Cột trong nhóm Công cụ Dữ liệu của dải băng Excel và trình hướng dẫn sẽ xuất hiện để giúp bạn thiết lập cách chia văn bản
- Nhấp vào nút tùy chọn Chiều rộng cố định
- Nhấp vào Tiếp theo cho bước tiếp theo của trình hướng dẫn
Bước thứ hai, trong Cửa sổ xem trước, nhấp vào thanh đo trong Xem trước dữ liệu nơi bạn muốn chia văn bản. Điều này sẽ hiển thị một đường thẳng đứng thể hiện sự phân tách. Làm điều này một lần nữa cho các ký tự bên tay phải
Nhấp vào Tiếp theo để chuyển sang bước cuối cùng của trình hướng dẫn
Đặt ô đích thành E3 để dữ liệu hiện tại không bị ghi đè
Nhấp vào Kết thúc và dữ liệu của bạn sẽ được nhập vào ba cột bắt đầu từ ô E3
Trang tính của bạn bây giờ sẽ giống như trên với kết quả được chia thành các ô riêng biệt
Trích xuất các ký tự bằng Flash Fill
Phương pháp Flash Fill này cho phép bạn nhập một vài ví dụ vào các ô mà bạn muốn dữ liệu phân tách của mình xuất hiện. Sau đó, Excel sẽ tìm ra dữ liệu nào cần thiết trong cột dựa trên các ví dụ của bạn
Nhược điểm của phương pháp này là các ví dụ cho biết có bao nhiêu ký tự sẽ được trích xuất. Điều này hoạt động tốt nếu nó luôn là một số thống nhất, nhưng trong một số trường hợp, nó có thể không hoạt động, trong trường hợp đó điều này sẽ không hoạt động
Để sử dụng Flash Fill, bạn cần nhập ít nhất ba ví dụ về văn bản mà bạn muốn xem
Bạn cũng có thể kích hoạt Flash Fill trong nhóm Công cụ dữ liệu của tab Dữ liệu trên dải băng Excel hoặc bằng cách sử dụng phím tắt Ctrl + E
Sau đó, Excel sẽ tự động điền dữ liệu còn lại dựa trên các ví dụ của bạn
Trích xuất ký tự bằng VBA
VBA là ngôn ngữ lập trình nằm sau Excel và cho phép bạn viết mã của riêng mình để thao tác dữ liệu hoặc thậm chí tạo các hàm của riêng bạn
Để truy cập Visual Basic Editor [VBE], bạn sử dụng ALT + F11
Sub SplitText[]
Dim N As Integer
For N = 3 To 12
ActiveSheet.Cells[N, 5].Value = Left[Cells[N, 2], 2]
Next N
End SubBên trong trình chỉnh sửa trực quan cơ bản
- Bấm vào Chèn trong thanh menu
- Nhấp vào Mô-đun và một ngăn mới sẽ xuất hiện cho mô-đun
- Dán vào đoạn mã trên
Cửa sổ của bạn bây giờ sẽ giống như trên
Đặt con trỏ ở bất kỳ đâu trong quy trình mã và sau đó nhấn F5 để chạy mã
Đoạn mã lặp qua các giá trị từ 3 đến 12, là số hàng của dữ liệu mẫu. Nó nhập hai ký tự đầu tiên của văn bản được tìm thấy ở hàng số N và cột 2 vào cột 5 [cột E]
Đây là mã VBA rất đơn giản và nó có nhược điểm là chỉ lấy hai ký tự đầu tiên của mã sản phẩm. Nếu mã sản phẩm dài hơn sẽ cho ra kết quả sai
Tuy nhiên, mã có thể được mở rộng để tìm kiếm ký tự phân cách [-] và sau đó lấy số lượng ký tự cho đến thời điểm đó
Trích xuất các ký tự bằng Power Query
Power Query là một công cụ hữu ích trong Excel để thao tác dữ liệu và có thể dễ dàng sử dụng công cụ này để chia một cột dữ liệu thành các phần
Trước tiên, bạn cần chuyển đổi dữ liệu của mình thành bảng Excel
Tạo truy vấn Từ bảng/Phạm vi
- Chọn một ô bên trong bảng
- Chuyển đến tab Dữ liệu
- Nhấp vào Từ bảng/Phạm vi trong nhóm Nhận và chuyển đổi dữ liệu
Thao tác này sẽ mở trình chỉnh sửa truy vấn nguồn cho phép bạn chuyển đổi dữ liệu
- Nhấp vào cột ProductSKU
- Nhấp vào tab Thêm cột của trình chỉnh sửa truy vấn nguồn
- Nhấp vào Trích xuất trong nhóm Từ văn bản
- Chọn Ký tự đầu tiên trong trình đơn thả xuống
Một cửa sổ bật lên sẽ được hiển thị. Nhập 2 vào hộp Đếm. Nhấp vào OK và một cột mới có tên là Ký tự đầu tiên sẽ được thêm vào. Nhấp đúp vào tiêu đề cột mới và đổi tên nó thành Category
= Table.AddColumn[#"Changed Type", "First Characters", each Text.Start[[ProductSKU], 2], type text]Điều này sẽ dẫn đến công thức mã M ở trên
Nếu bạn cần 2 ký tự cuối cùng, hãy nhấp vào Ký tự cuối cùng trong trình đơn thả xuống Trích xuất
= Table.AddColumn[#"Inserted First Characters", "Last Characters", each Text.End[[ProductSKU], 2], type text]Nó sẽ dẫn đến công thức mã M ở trên
Bấm vào Đóng và Tải trong nhóm Đóng trên tab Trang chủ của dải băng và một trang tính mới sẽ được thêm vào sổ làm việc của bạn với một bảng dữ liệu ở định dạng mới
Trích xuất các ký tự bằng cột được tính trong Power Pivot
Bạn cũng có thể sử dụng Bảng Power Pivot để hiển thị hai ký tự đầu tiên của Mã sản phẩm
Để làm điều này, bạn cần chuyển đổi dữ liệu của mình thành bảng Excel
Để nhập dữ liệu của bạn vào Power Pivot
- Chọn một ô bên trong bảng
- Chuyển đến tab Power Pivot. Nếu bạn không thấy tab Power Pivot, bạn có thể cài đặt nó bằng cách chuyển đến tab Dữ liệu và nhấp vào Quản lý Mô hình Dữ liệu
- Chọn Thêm vào Mô hình Dữ liệu
Thao tác này sẽ mở phần bổ trợ Power Pivot với bảng được tải. Bạn có thể mở phần này bất kỳ lúc nào từ tab Power Pivot bằng cách chọn Quản lý trong nhóm Mô hình dữ liệu trong dải băng Excel
Nhấp chuột phải vào tiêu đề cho Thêm cột và nhấp vào Đổi tên cột trong menu bật lên. Bạn cũng có thể nhấp đúp vào cột để đổi tên nó. Sau đó, bạn có thể đổi tên cái này thành một cái gì đó như Category
= LEFT[Products[ProductSKU], 2]Chọn bất kỳ ô nào bên trong cột Danh mục mới và chèn công thức trên vào thanh công thức. Bất kể bạn nhập công thức này vào ô nào, nó sẽ lan truyền khắp toàn bộ cột
Cột Danh mục hiện hiển thị hai ký tự đầu tiên của mã sản phẩm
= RIGHT[Products[ProductSKU], 2]Bạn có thể làm tương tự với công thức trên cho 2 ký tự cuối cùng
Nhấp vào biểu tượng Bảng Pivot trong dải băng và nhấp vào Bảng Pivot trong menu bật lên để biến dữ liệu nguồn của bạn thành bảng tổng hợp bảng tính
Chỉ định vị trí của bảng tổng hợp của bạn trong cửa sổ bật lên đầu tiên và nhấp vào OK. Nếu ngăn Trường bảng Pivot không tự động hiển thị, nhấp chuột phải vào khung bảng tổng hợp và chọn Hiển thị danh sách trường
Bây giờ bạn có thể tóm tắt dữ liệu của mình theo Danh mục hoặc Kích thước
Trong Ngăn Trường Bảng Pivot, bấm đúp vào tên nguồn dữ liệu của bạn e. g. Sản phẩm và điều này sẽ hiển thị các lĩnh vực có sẵn. Chọn tất cả các hộp để sử dụng tất cả các trường trong bảng tổng hợp của bạn
Trong hộp Hàng, hãy kéo trường Danh mục lên đầu danh sách để bảng tổng hợp sẽ tóm tắt theo các danh mục khác nhau
Tất cả các chức năng của bảng tổng hợp thông thường đều khả dụng dưới dạng đổi tên cột, định dạng số, v.v.
Trích xuất các ký tự bằng thước đo Power Pivot
Để sử dụng thước đo để tách trường ProductSKU, bạn sử dụng cùng hướng dẫn nguồn dữ liệu như đối với phương pháp Cột được tính toán hiển thị ở trên
Khi bạn nhấp vào tab Power Pivot và nhấp vào biểu tượng Quản lý, bạn có thể nhập Số đo vào cửa sổ Trình quản lý Power Pivot
Cửa sổ này được chia thành hai phần. Phần trên cùng hiển thị dữ liệu nguồn trong bảng và phần dưới cùng hiển thị các thước đo. Đây là những công thức sử dụng dữ liệu trong bảng và tạo trường được tính toán có thể được sử dụng trong bảng tổng hợp
Category List :=
CONCATENATEX [
VALUES [ Products[Category] ],
Products[Category],
", "
]Chọn ô đầu tiên trong phần đo và nhập công thức trên vào thanh công thức
Một thước đo luôn cần trả về một giá trị vô hướng. Đây là lý do tại sao hàm CONCATENATEX được sử dụng đầu tiên để tổng hợp kết quả của các cột được tính toán được tạo từ hàm LEFT hoặc RIGHT. Nếu không có hàm CONCATENATEX thì hàm này sẽ trả về giá trị lỗi và không thể sử dụng trong bảng tổng hợp
Bấm vào biểu tượng Bảng tổng hợp trong dải băng và làm theo hướng dẫn để tạo bảng tổng hợp như được minh họa ở trên trong ví dụ về cột được tính toán
Trong danh sách Trường bảng Pivot, hiện có một trường được tính toán có tên là Danh mục, được hiển thị với fx ở phía trước. Điều này trả về hai ký tự đầu tiên của trường ProductSKU
Bởi vì một thước đo luôn là một trường được tính toán, nên nó chỉ có thể được bao gồm dưới dạng một giá trị, mặc dù nó thực sự trả về một giá trị văn bản trong trường hợp này. Nó không thể được sử dụng làm bộ lọc hoặc làm tên hàng hoặc cột