Tôi dạy một lớp về Power Excel tại Đại học Akron. Mặc dù được quảng cáo là lớp học nâng cao nhưng luôn có một số khái niệm cơ bản mà học viên dường như không biết. Tôi ngạc nhiên về cách những kỹ thuật đơn giản nhất sẽ gây ra sự phấn khích nhất. Đây là một trong những mẹo đó.
Hôm nay, Sajjad từ Dubai đã viết với một câu hỏi. Anh ta có một cơ sở dữ liệu với tên trong Cột A và Họ trong cột B. Làm thế nào anh ta có thể hợp nhất Tên và Họ thành một cột duy nhất?
Đây là một trong những câu hỏi mà bạn không bao giờ có thể tìm thấy trong trợ giúp của Excel, bởi vì không ai nghĩ đến việc tìm kiếm từ "Nối". Rất tiếc, tôi không nghĩ rằng bất kỳ người bình thường nào sử dụng từ nối. Nếu bạn không biết cách tìm kiếm Concatenate, thì bạn sẽ không bao giờ học được rằng toán tử nối là một ký hiệu và.
Bắt đầu với công thức cơ bản của =A2&B2
Điều này sẽ cung cấp cho bạn kết quả được hiển thị trong C2 bên dưới:
Đây là một khởi đầu tốt. Tuy nhiên, chúng ta thực sự nên ghép tên, khoảng trắng và họ. Hãy thử công thức này:
=A2&" "&B2
Sau đó, câu hỏi là: bạn muốn hét lên MICKEY MANTLE, hay bạn muốn nói Mickey Mantle? Nếu bạn muốn thay đổi tên thành trường hợp thích hợp, hãy sử dụng =PROPER()hàm.
=PROPER(A2&" "&B2)
Tiếp theo, bạn muốn sao chép công thức xuống tất cả các ô trong cột. Một phương pháp tắt để thực hiện việc này là bấm đúp vào ô điều khiển điền khi ô C2 được chọn. Ô điều khiển điền là chấm vuông tối hơn ở góc dưới bên phải của con trỏ ô. Dấu chấm trông như thế này:
Khi con trỏ chuột của bạn gần dấu chấm, con trỏ chuột sẽ chuyển thành dấu thập như thế này.
Nhấp đúp và công thức sẽ được sao chép xuống tất cả các ô trong phạm vi.
Lưu ý: Excel sử dụng cột bên trái khi tìm ra khoảng cách để sao chép ô sau khi nhấp đúp. Nếu bạn tình cờ có một ô trống trong B8, thủ thuật này sẽ dừng lại ở hàng 7. Để nó cho Cher gây ra sự cố.
Nếu đúng như vậy, bạn có thể muốn lấy chốt điền và kéo xuống tất cả các hàng để sao chép công thức.
Lưu ý 2: Hàm Thích hợp là tuyệt vời, nhưng nó không viết hoa đúng các họ như McCartney (Xem ô C7). Bạn sẽ phải điền thủ công và viết hoa chữ C sau Mc. Nó cũng sẽ có vấn đề với VanHalen. Đây có phải là một nỗi đau? Có - nhưng sửa một vài ô sẽ dễ hơn là nhập lại mọi thứ trong trường hợp thích hợp.
Chuyển đổi công thức thành giá trị
Bây giờ bạn có Họ và Tên đầu tiên trong cột C, bạn có thể muốn xóa cột A và B. Bạn chưa thể làm điều này. Nếu bạn xóa cột A & B, tất cả các công thức trong cột C sẽ thay đổi thành lỗi #REF! lỗi. Lỗi này nói rằng, "Này - bạn đã nói với tôi giá trị trong cột này phải từ A2 & B2, nhưng bạn đã xóa những ô đó nên tôi không biết phải đặt gì ở đây!".
Giải pháp là thay đổi công thức thành giá trị trước khi bạn xóa cột A & B. Thực hiện theo các bước sau:
- Đánh dấu phạm vi ô trong cột C
- Sao chép các ô đó vào khay nhớ tạm bằng phương pháp yêu thích của bạn (4 phương pháp để chọn: Ctrl + C, hoặc Chỉnh sửa - Sao chép từ menu hoặc biểu tượng khay nhớ tạm trên thanh công cụ hoặc nhấp chuột phải và chọn sao chép).
- Không bỏ chọn các ô, từ menu, hãy chọn Chỉnh sửa> Dán Đặc biệt. Từ hộp thoại Dán Đặc biệt, chọn Giá trị rồi OK. Bước này sẽ dán giá trị hiện tại của mỗi ô trong phạm vi trở lại ô đó. Thay vì có một công thức, bây giờ bạn sẽ có một giá trị tĩnh. Việc xóa cột A & B là an toàn.
Nối một ô chứa văn bản với một ô chứa một số
Nói chung, điều này sẽ diễn ra khá tốt. Trong hình ảnh bên dưới, tôi đã sử dụng công thức để tạo một cụm từ chứa tên trong cột A với tuổi trong cột C.
Bí quyết là khi số được hiển thị ở một định dạng và bạn muốn nó được sử dụng ở định dạng khác. Ngày là một ví dụ kinh điển về điều này. Ngày 11 tháng 12 năm 1943 thực sự được lưu trữ dưới dạng một số ngày kể từ ngày 1 tháng 1 năm 1900. Nếu tôi cố gắng nối văn bản trong cột A với ngày trong cột B, tôi nhận được một kết quả trông ngớ ngẩn.
Giải pháp là sử dụng =TEXT()chức năng. Hàm văn bản yêu cầu hai đối số. Đối số đầu tiên là một ô chứa một số. Đối số thứ hai là một định dạng số tùy chỉnh cho biết cách số sẽ được hiển thị. Công thức sau sẽ tạo ra một kết quả được định dạng độc đáo.
Có rất nhiều kỹ thuật thú vị đã được đề cập trong mẹo này.
- Một công thức để nối 2 cột văn bản bằng cách sử dụng dấu và làm toán tử nối
- Cách nối một ô với một giá trị văn bản
- Cách sử dụng hàm PROPER để đổi tên thành chữ hoa / thường
- Tại sao bạn nhận được #REF! lỗi
- Cách sử dụng Dán Giá trị Đặc biệt để chuyển đổi công thức thành giá trị.
- Nối một ô chứa văn bản với một ô chứa một số
- Sử dụng chức năng TEXT để kiểm soát việc hiển thị ngày tháng trong công thức.
Khái niệm này được in lại từ Phân tích Dữ liệu Guerilla Sử dụng Microsoft Excel.
