Cập nhật thông tin chi tiết về Cách Lấy Dữ Liệu Từ Bảng Này Sang Bảng Khác Trong Excel mới nhất trên website Theindochinaproject.com. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất.
Trong quá trình làm việc, bạn sẽ cần phải lấy dữ liệu từ sheet này sang sheet khác theo điều kiện thì bạn có thể dùng hàm Vlookup để tham chiếu và lấy dữ liệu cần thiết theo điều kiện của công việc.
Tuy nhiên để lấy dữ liệu từ bảng này sang bảng khác trong Excel thì ngoài hàm Vlookup thì bài viết này sẽ chia sẻ cho các bạn cách lấy dữ liệu này bằng cách thiết lập các thao tác 1 lần duy nhất bằng áp dụng Power Query trong Excel. Lần sau các bạn chỉ cần ấn Refresh dữ liệu sẽ được cập nhập mà không phải viết hàm Vlookup quá nhiều lần.
Lấy dữ liệu từ bảng này sang bảng khác trong Excel
Cách dùng hàm Vlookup trong Excel
Để tham chiếu dữ liệu giữa 2 bảng khác nhau thuộc 2 sheet hoặc 2 file khác nhau, bạn vẫn có thể dùng hàm Vlookup. Theo Microsoft thì hàm Vlookup có cấu trúc như sau:
= VLOOKUP(Lookup_value, Table_array, Col_index_num, [Range_lookup])
Diễn giải các thành phần trên:
Lookup_value: giá trị cần dò tìm
Table_array: Bảng giới hạn để dò tim, dùng phím F4 để cố định bảng dò tìm cho mục đích copy công thức.
Col_index_num: Số thứ tự của cột dữ liệu trong bảng cần dò tìm
Range_lookup: Là giá trị logic (True=1, False=0)
+ Nếu Range_lookup = 1 (True): So sánh tương đối
+ Nếu Range_lookup = 0 (False): So sánh chính xác
+ Nếu bỏ qua thành phần này khi viết công thức, thì Excel tự động hiểu là Range_lookup = 1
Trong ví dụ trên, tại ô C20 ta viết công thức: =VLOOKUP(B20,$B$2:$I$15,8,0)
Trong đó:
Vlookup: là hàm dùng để tìm kiếm, lấy dữ liệu từ bảng 1 sang bảng 2 theo điều kiện
B20: Giá trị là Mã nhân viên cần tìm ở đây là 988, 766, 395, 410…
$B$2:$I$15: Bảng giới hạn dò tìm, chính là B2:I15 nhưng được F4 để cố định bảng dò tìm và cố định copy công thức xuống các dòng phía dưới B21, B22,…
8: thứ tự cột cần lấy trong bảng 1, trong trường hợp này chính là cột Nhóm nhân viên trong bảng 1.
0: trả về giá trị chính xác của Nhóm nhân viên theo Mã nhân viên. Nếu chọn 1 thì cột sẽ trả về giá trị tương đối
Do vậy, bạn có thể dùng hàm Vlookup này để tìm kiếm, tham chiếu giá trị hay lấy dữ liệu từ bảng này sang bảng khác, từ sheet này sang sheet khác theo nhu cầu bằng cách tương tự như trên.
Xử lý dữ liệu bằng Power Query
Việc dùng Vlookup như trên dường như khá thuận tiện cho các bạn khi làm việc. Tuy nhiên nếu dữ liệu của bạn có quá nhiều cột hoặc nhiều dòng thì việc dùng hàm Vlookup sẽ làm nặng file mà bạn đang xử lý. Dẫn đến việc phải đợi rất lâu để xử lý hàm này. Thêm nữa, việc dùng Vlookup chỉ nên áp dụng khi công việc bạn đang làm chỉ dùng 1 lần.
Còn nếu công việc bạn lặp đi lặp lại như trên, các bạn nên áp dụng tính năng của Power Query trong Excel để tự động hóa công việc trong các lần sau.
Trong Power Query, tính năng vlookup tương đương việc dùng Merge. Mình sẽ đưa ví dụ phía dưới như sau:
Bước 2: Excel sẽ tự động mở cửa sổ tính năng Power Query, sau đó bạn chỉ cần Close& Load như hình dưới, sau đó chọn Only Create Connection, và ấn OK
Sau khi tạo connection trong Power Query. Bạn tiếp tục làm thao tác như phía dưới để thiết lập tính năng tự động cho file Excel của bạn
Bước 2: Chọn bảng 2, dùng tính năng Merge Queries, Merge Queries. Đây có thể coi như là hàm Vlookup trong excel thông thường. Nhưng ưu việt và vượt trội hơn hẳn.
Bước 4: Sau khi merge queries như trên thì bạn chỉ cần thao tác lựa chọn tên cột dữ liệu cần tìm kiếm và ấn OK.
Bước 5: Hoàn tất thao tác. Bằng cách ấn load bảng dữ liệu ra bên ngoài. Vậy là bạn đã thiết lập xong các thao tác tự động hóa trên file Excel của chính bạn. Lần tới bạn chỉ cần copy dữ liệu bỏ vào bảng 1, sau đó ấn Refresh dữ liệu sẽ tự động cập nhập vào bảng thứ 2 của bạn.
Học Power Query ở đâu?
Uniace chúng tôi tự hào là đơn vị tiên phong trong việc đào tạo Power Query, Power Pivot và Power BI tại Việt Nam. Khóa học của chúng tôi bao gồm lý thuyết và bài tập thực hành, giúp cho người học có thể ứng dụng ngay vào công việc hiện tại. Với cam kết chất lượng là trên hết, ngoài việc cung cấp các kiến thức cho học viên.
Người học còn được tham gia vào cộng đồng phân tích, và được chúng tôi hỗ trợ 24/7, trả lời mọi thắc mắc trong quá trình học. Bên cạnh đó, còn hỗ trợ các bạn hướng xử lý trong công việc hiện tại.
Các bạn có thể tham khảo khóa học nền tảng của chúng tôi và những quyền lợi khi tham gia khóa học tại đây. Nếu có bất kỳ thắc mắc nào các bạn có thể chat với chúng tôi để được giải đáp.
Tham khảo các bài viết hữu ích khác tại chuyên đề excel:
Các Cách Để Xoay Bảng Dữ Liệu Dọc Sang Sang Và Ngược Lại Trong Excel
Trong bài viết này, chúng tôi sẽ hướng dẫn các bạn một số cách để Transform dữ liệu trong Excel và bạn đã biết được bao nhiêu cách để Transpose trong Excel.
Bạn biết bao nhiêu cách để Transpose trong Excel
Sử dụng Copy, Paste để xoay bảng dữ liệu trong Excel
Cách đầu tiên và chắc có lẽ là cách đơn giản nhất là việc Copy và Paste Transpose. Tính năng Copy và Paste không chỉ trong Excel mà trong các ứng dụng khác của bộ Office có rất nhiều điều tuyệt vời như Copy và Paste chỉ lấy giá trị, chỉ lấy công thức, chỉ lấy định dạng,….Và Copy, Paste Transform cũng không ngoại lệ
Bạn Copy (Ctrl + C), di chuyển chuột sang 1 vị trí mà bạn muốn Paste, ấn Ctrl + Alt + V (để mở hộp thoại Paste Special), tại cửa sổ này bạn chọn tới Transform, ấn OK và bạn đã có được dữ liệu được Transpose từ dữ liệu mà bạn đã Copy.
Sử dụng hàm TRANSPOSE để xoay bảng dữ liệu
Hàm này với đối số truyền vào là 1 mảng, cú pháp hàm như sau:
TRANSPOSE(array)
Ở đây mình dùng Excel 365 nên mọi việc sẽ dễ dàng với với tính năng Array resize. Tuy nhiên, nếu như bạn không dùng Excel 365 thì bạn có thể sẽ phải thực hiện như sau:
Data cần Transpose của mình ở đây là vùng từ B2:C6 (có 5 hàng và 2 cột). Với Excel không có tính năng Array Resize bạn cần chọn 1 vùng với 2 hàng (tương ứng với 2 cột của Data gốc) và 5 cột (tương ứng với 5 hàng của Data gốc) sau đó gõ công thức TRANSPOSE(B2:C6) và kết thúc bằng Ctrl + Shift + Enter (CSE)
Bạn cần chọn 1 vùng để đặt công thức với số hàng bằng số cột của Data gốc và số cột bằng số hàng của Data gốc.
Sử dụng Power Query để xoay bảng dữ liệu
Nếu như bạn có tìm hiểu về Power Query thì chắc hẳn cũng đã biết về tính năng này trong Power Query Editor.
Bạn chỉ cần xuất kết quả ra Excel, dữ liệu của chúng ta lúc này trông sẽ như sau
Ngoài các cách trên thì bạn cũng hoàn toàn có thể Transpose với việc áp dụng Code VBA. chúng tôi để yêu cầu này cho các bạn cùng suy nghĩ và xử lý và nó cũng không quá khó đối với những bạn đã có kiến thức cơ bản về VBA. Goodluck! 😁
Power query là gì? tại sao cần học power query?
Xoá tất tần tật các ký tự khoảng trắng đặc biệt trong excel không cần dùng hàm
Chuẩn hoá kiểu dữ liệu “khó đỡ” với công cụ power query trong excel
Đánh giá bài viết này
Lọc Dữ Liệu Giữa Các Sheet, Bảng Tính Trong Excel
Có giải pháp nào để áp dụng cho việc lọc dữ liệu giữa các Sheet trong Excel khi mà dữ liệu của công ty bạn nó nằm ở nhiều Sheet khác nhau. Tính năng này hoàn toàn cơ bản và rất hay được sử dụng trong công việc hằng ngày. Với hướng dẫn sau đây sẽ hướng dẫn bạn cách làm quen cũng như sử dụng lọc dữ liệu giữa các Sheet trong Excel.
lọc dữ liệu giữa các Sheet trong Excel
Hướng dẫn Lọc dữ liệu giữa các Sheet trong Excel
Giả sử bạn có bảng dữ liệu Danh sách nhân viên trong Sheet 1.
Bảng dữ liệu mã chức vụ và tiền lương ứng với vị trí đó.
Yêu cầu: Dò tìm mã vị trí ở bảng dữ liệu trong Sheet 1 với mã vị trí ở bảng dữ liệu trong Sheet 2. Nếu giống nhau thì trả về tiền lương tương ứng.
– Để dò tìm giá trị của một cột bạn sử dụng hàm VLOOKUP để dò tìm. Vấn đề là bây giờ 2 bảng dữ liệu chính và bảng chứa kết quả dò tìm nằm ở 2 sheet khác nhau, làm thế nào để có thể kết nối dữ liệu của 2 bảng ở 2 sheet khác nhau được?
– Trước tiên các bạn cứ áp dụng hàm dò tìm VLOOKUP cho ô giá trị E4 trong bảng Danh sách nhân viên. Công thức cho ô giá trị E4 như sau:
+ Lookup_value: giá trị dò tìm. Ở đây bạn sẽ chọn D4. Nhưng vì đây là ô dữ liệu nằm trong Sheet1 nên bạn sẽ nhập là Sheet1!D4. Công thức bây giờ là =VLOOKUP(Sheet1!D4)
+ Table_array: bảng dò tìm. Bạn mở qua sheet2 và chọn bảng dò tìm ở Sheet2. Là vùng giá trị A4:B8. Nhưng đây là bảng giá trị ở Sheet2 nên bạn sẽ phải nhập là Sheet2!A4:B8. Thêm ký tự $ để cố định bảng dò tìm này lại. Công thức bây giờ sẽ là =VLOOKUP (Sheet1!D4, Sheet2!$A$4:$B$8)
+ Col_index_num: cột chứa giá trị tham chiếu. Ở dây là cột Lương là cột thứ 2 của bảng lương nên bạn nhập là 2. Công thức bây giờ là =VLOOKUP(Sheet!D4,Sheet2!$A$4:$B$8,2)
+ Range_lookup: ta cần kết quả trả về chính xác với dữ liệu trong bảng dò tìm cho nên Range_lookup sẽ là 0. Công thức sẽ là: =VLOOKUP(Sheet1!D4, Sheet2!$A$4:$B$8,2,0)
Ta có kết quả thu được là:
Sao chép công thức cho các ô còn lại. Ta có kết quả cuối cùng là:
Vậy để lấy giá trị từ các Sheet khác ta sẽ nhập ô giá trị hoặc mảng dữ liệu tham chiếu để kết nối dữ liệu với các sheet khác nhau.
Bài viết này giới thiệu với các bạn một thủ thuật nhỏ khi kết hợp dữ liệu giữa các Sheet khác nhau bằng cách nhập ô giá trị hoặc mảng dữ liệu tham chiếu vào công thức tính toán thay vì chỉ nhập ô giá trị hoặc mảng dữ liệu đơn thuần. Ngoài ra, đối với bảng tính chứa nhiều dữ liệu thì việc trùng lặp dữ liệu là khó tránh khỏi. Và nếu như bạn đọc đang gặp phải tình huống như vậy thì bằng bài viết hướng dẫn các bạn sử dụng công cụ tích hợp sẵn trong Excel để lọc dữ liệu trùng trong Excel trước đó, Taimienphi hy vọng giúp các bạn phần nào hoàn thành công việc của mình một cách nhanh nhất.
https://thuthuat.taimienphi.vn/loc-du-lieu-giua-cac-sheet-trong-excel-2199n.aspx Nếu bạn có 1 bảng dữ liệu với nhiều dữ liệu có giá trị khác nhau, bạn cần lọc ra những dữ liệu theo điều kiện của bài toán đưa ra, lúc này, bạn sẽ cần sử dụng chức năng Advance Filter trong Excel, đây là chức năng khá hữu ích, bạn dùng Advance Filter trong Excel để lọc dữ liệu theo điều kiện cho trước mà không cần tới những thao tác rườm rà, mất thời gian khác nữa.
Tạo Thư Tự Động Từ Dữ Liệu Excel Sang Word
Trộn thư () là một công cụ vô cùng tiết kiệm thời gian khi phải gửi một lượng thư lớn. Nó giúp bạn nhanh chóng tạo một lá thư, email hay tựa thư trong Word bằng cách hợp nhất thông tin bạn có trong trang tính Excel. Học excel nâng cao Online sẽ cung cấp cho bạn tổng quan của đặc tính này và hướng dẫn trộn thư theo từng bước.
Trộn thư có thể trông khó khăn, nhưng trên thực tế quá trình này khá đơn giản.
Dể giúp bạn hiểu sâu hơn, quá trình trộn thư gồm 3 tài liệu:
Văn bản Word chứa mã ứng với từng phần cụ thể.
Tệp nguồn trộn thư Excel chứa thông tin người nhận, mỗi hàng là một người nhận.
Bản Word cuối cùng với thư, email, bì thư hoàn chỉnh.
Chuẩn bị trang tính Excel cho trộn thư:
Khi bạn trộn thư, tệp Excel sẽ được kết nối với tài liệu trộn thư của Word. Microsoft Word sẽ lấy tên, địa chỉ và các chi tiết khác của người nhận trực tiếp từ bảng tính Excel.
Vì vậy, trước khi bắt đầu, hãy chắc chắn rằng tệp Excel đã có tất cả thông tin bạn muốn bao gồm tên, họ, lời chào, mã bưu điện, địa chỉ… Nếu bạn muốn thêm nhiều địa chỉ liên hệ hoặc thông tin khác, bạn nên thay đổi hoặc bổ sung bên trang tính Excel trước khi trộn thư.
Những điều quan trọng cần kiểm tra:
Các cột trong bảng tính Excel phải khớp với các phần bạn muốn sử dụng khi trộn thư. Ví dụ: nếu bạn muốn xưng hô với người đọc bằng Tên, hãy chắc chắn đã tạo hai cột họ và tên riêng biệt. Nếu bạn muốn sắp xếp tên người nhận theo bang hoặc thành phố, xác định rằng bạn đã có hai cột Bang và Thành phố riêng biệt.
Nếu tệp Excel bao gồm ngày, giờ, giá trị tiền tệ hoặc mã bưu điện bắt đầu hoặc kết thúc bằng 0, hãy định dạng đúng số, số ngày và đơn vị tiền tệ.
Nếu bạn tạo trang tính Excel bằng cách nhập thông tin từ tệp .csv hoặc tệp .txt, hãy sử dụng Text Import Wizard.
Cách trộn thư từ Excel sang Word:
Khi hoàn thành và kiểm tra lại trang tính Excel, bạn đã sẵn sàng để trộn thư. Trong ví dụ này, chúng ta sẽ trộn thư với một danh sách người nhận trong Wrd 2010, tương tự cho Word 2013.
Nếu bạn đã soạn thư sẵn một lá thư, bạn có thể mở một tài liệu Word hiện có, hoặc tạo một tài liệu mới.
Kết nối trang tính Excel và bản Word. Bằng việc thiết lập kết nối giữa Excel và Word, hãy đảm bảo rằng nguồn dữ liệu trộn thư sẽ được tự động cập nhật mỗi lần bạn thay đổi dữ liệu người nhận trên tệp Excel.
Tìm hiểu thêm: Khóa học excel online
Trong hộp thoại Select Data Source, tìm trang tính Excel và nhấp . Nếu Word yêu cầu bạn chọn từ bảng, hãy chọn và nhấp OK.
Như vậy, chúng ta đã hoàn thành danh sách người nhận và sẵn sang điền chúng vào thư. Gõ thư mẫu vào Word hoặc sao chép/dán từ một nguồn khác.
Thêm chỗ điền. Bạn cần xác định vị trí điền địa chỉ Address Block và dòng chào hỏi Greeting Line. Tới phần Write & Insert Fields trong thẻ .
Trong nhiều trường hợp khác, bạn có thể đặt dữ liệu của người nhận vào nội dung thư để tùy chỉnh chúng theo cá nhân. Để thực hiện việc này, nhấp Insert Merge Field và chọn dữ liệu bạn muốn chèn từ danh sách tùy chọn.
Xem trước lá thư. Chắc chắn rằng dữ liệu người nhận xuất hiện chính xác trong lá thư, nhấn nút Preview Results trên thẻ .
Hoàn tất trộn thư. Khi đã hài lòng, nhấn nút Finish & Merge trong nhóm để chọn in thư hoặc gửi mail chúng.
Sao lưu văn bản trộn thư. Bạn sao lưu tệp trộn thư như tệp Word thông thường bằng cách nhấn nút hoặc nhấn phím .
Một khi đã sao lưu, tệp sẽ luôn kết nối với danh sách trong Excel. Khi bạn muốn sử dụng văn bản trộn thư lần nữa, mở nó ra và nhấn Yes khi Word yêu cầu bạn tiếp tục kết nối.
Khi trộn thư, bạn cần chú ý giá trị số như ngày tháng, tiền tệ và số. Trong phần này, chúng tôi sẽ hướng dẫn bạn cách định dạng những giá trị như vậy.
Định dạng zip codes và các giá trị khác chứa số 0 trong Excel
Để không làm mất số 0 đứng đầu khi trộn thư, bạn chỉ cần định dạng cột ZIP code thành dạng text trong Excel.
Chọn cột ZIP code, nhấp phải, chọn Format Cells… từ danh sách tùy chọn.
Nếu trang tính Excel chứa ngày tháng, số thập phân, đơn vị tiền tệ, bạn có thể sử dụng Dynamic Data Exchange để đảm bảo rằng các giá trị này được định dạng đúng sau khi trộn thư.
Khi Dynamic Data Exchange được kích hoạt, bạn có thể nhận được một vài yêu cầu, chỉ cần nhấn Yes hoặc Ok. điểm khác biệt duy nhất do với trộn thư thông thường là chọn nguồn dữ liệu, tiến hành theo các bước sau.
Dò tìm trên trang tính, nhấn đúp chuột chọn MS Excel Worksheets via DDE (*.xls) và nhấn OK.
Định dạng ngày tháng, giờ, số và tiền tệ khi trộn thư trong Word
Nếu bạn muốn định dạng số, ngày tháng hay tiền tệ theo nhiều cách khác với tệp nguồn trộn thư Excel, bạn có thể thực hiện trực tiếp trong văn bản Word.
Chọn vùng trộn thư muốn thay đổi định dạng. Chúng có thể là Date, Currency, Percent hoặc các vùng khác.
Nhấn để hiển thị mã vùng. Ví dụ, bạn chọn vùng , bạn sẽ thấy {MERGEFIELD CURRENCY}.
Định dạng số lượng, đơn vị tiền tệ và phần trăm
Chú ý. Thay vì kí hiệu $, bạn có thể thay bằng ký hiệu tiền tệ khác € hoặc £.
Bạn có thể sử dụng d, M, y cho ngày tháng và giờ, nhưng nhớ rằng M đại diện cho tháng còn m đại diện cho phút.
Thay đổi định dạng của ngày tháng và giờ hiện tại:
Nếu bạn thêm vùng DATE và TIME hiển thị thời gian hiện tại trong văn bản trộn thư, bạn có thể thay đổi định dạng của chúng.
Nhấn để hiển thị mã vùng. Nếu bạn chọn vùng , bạn sẽ thấy {DATE @ “M/d/yyyy”}
Nhấp phải vào vùng và chọn Edit Fields từ danh sách tùy chọn.
Alt + F9
Mở tất cả vùng cần điền và kết quả trong văn bản trộn thư
Shift + F9
Hiện ra mã của những chỗ trống
F9
Cập nhật chỗ trống được chọn. đặt con trỏ vào bất cứ ô trống nào và nhấn F9 để cập nhật
F11
Đến chỗ trống cần điền sau
Shift + F11
Đến chỗ trống cần điền trước
Alt + Shift + E
Chỉnh sửa văn bản trộn thư. Chú ý: điều này sẽ ngắt kết nối giữa tệp Excel và văn bản Word, nguồn trộn thư sẽ không tự động cập nhật nữa.
Alt + Shift + F
Chèn vùng trộn thư mới từ nguồn trộn thư
Alt + Shift + M
In văn bản trộn thư
Ctrl + F9
Chèn thêm một chỗ trống
Ctrl + F11
Khóa chỗ trống cần điền. Kết quả sẽ không được cập nhật khi thông tin nguồn của Excel thay đổi.
Ctrl + Shift + F11
Mở khóa chỗ trống cần điền. Kết quả chỗ trống sẽ được cập nhật lần nữa.
Ctrl + Shift + F9
Bỏ liên kết chỗ trống cần điền. Chỗ trống sẽ được xóa vĩnh viễn khỏi văn bản và thay thế bằng giá trị hiện tại hiển thị dưới định dạng bình thường.
Alt + Shift + D
Chèn ngày tháng hiển thị theo ngày tháng hiện tại
Alt + Shift + P
Chèn số trang
Alt + Shift + T
Chèn thời gian hiển thị theo thời gian hiện tại
Alt + Shift + L
Chèn số thứ tự
Mong rằng với bài viết này, sẽ giúp cho các bạn có thể tạo thư tự động từ dữ liệu Excel sang Word – Tính năng trộn thư Mail Merge một cách dễ dàng nhất. Để có thể trau dồi kiến thức từ cơ bản đến nâng cao về Excel các bạn hãy đăng kí tham gia ngay những khóa học word excel cấp tốc với các chuyên gia có nhiều năm kinh nghiệm về Excel sẽ hướng dẫn các bạn một cách tận tình nhất với giá thành cực hấp dẫn.
Ngoài ra để ứng dụng Excel vào công việc một cách hiệu quả thì bạn còn phải sử dụng tốt các hàm, các công cụ khác của Excel:
Một số hàm cơ bản thường gặp như:
SUMIF, SUMIFS để tính tổng theo 1 điều kiện, nhiều điều kiện
COUNTIF, COUNTIFS để thống kê, đếm theo một điều kiện, nhiều điều kiện
Các hàm xử lý dữ liệu dạng chuỗi, dạng ngày tháng, dạng số…
Các hàm dò tìm tham chiếu Index+Match, hàm SUMPRODUCT…
Một số công cụ hay sử dụng như:
Định dạng theo điều kiện với Conditional formatting
Thiết lập điều kiện nhập dữ liệu với Data Validation
Cách đặt Name và sử dụng Name trong công thức
Lập báo cáo với Pivot Table…
Bạn đang xem bài viết Cách Lấy Dữ Liệu Từ Bảng Này Sang Bảng Khác Trong Excel trên website Theindochinaproject.com. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!