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 Show
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 viết nàyCá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à RIGHTExcel 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ÁIcú pháp
Đ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ăngcú pháp
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ộtVă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
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 FillPhươ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 VBAVBA 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
Bên trong trình chỉnh sửa trực quan cơ bả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. Trích xuất các ký tự bằng Power QueryPower 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
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
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
Đ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
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 PivotBạ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
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
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
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
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ừ các 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 |