2. Tham gia — nơi các cột bảng tính được nối theo chiều ngang dựa trên cùng giá trị của các cột bạn đã chọn
Hai bảng tính được nối thành một dựa trên các giá trị Tên giống nhau
Trong hướng dẫn này, chúng ta sẽ tập trung vào Appending. Tôi sẽ viết một hướng dẫn khác về Tham gia
từ chối trách nhiệm
Tôi đã xây dựng một bảng tính hợp nhất công cụ. com nối các tệp dễ dàng. Vui lòng dùng thử nếu bạn cần thêm nhanh
Điều kiện tiên quyết
Nếu bạn không biết cách sử dụng Terminal và Python, hoặc cách đọc và ghi tệp bằng Python và Pandas, thì hãy đọc và xem qua hướng dẫn này trước. https. //Trung bình. com/@lovespreadsheets/giới thiệu-xử lý-bảng tính-với-python-c6a35e0d8de8
Các tập tinChúng tôi sẽ sử dụng ba tệp cho hướng dẫn này. Bạn có thể tải chúng xuống bằng cách nhấp vào liên kết. Tên nhà phân tích tiếp thị. xlsx, Tên đại diện bán hàng. xlsx, SeniorLeadershipNames. xlsx
Mã đầy đủĐây là toàn bộ mã để nối ba tệp
Mã đầy đủ cho hướng dẫn nàyI. Đọc cả ba bảng tính
Tạo một tệp python trong cùng thư mục với ba bảng tính của bạn và đặt tên cho nó là nối thêm. py
Trước tiên, chúng tôi sẽ nhập thư viện gấu trúc của mình và viết tắt cho nó là pd. Chữ viết tắt pd là quy ước và về mặt kỹ thuật, bạn có thể sử dụng import pandas và thay thế mọi thứ pd trong mã bằng pandas
import pandas as pdChúng ta sẽ đọc trang tính đầu tiên mà không có bất kỳ tham số bổ sung nào vì chúng ta chỉ có dữ liệu văn bản và dòng đầu tiên là tên cột để chúng ta có thể đọc cả ba tệp bằng lệnh pandas read_excel mà không cần bất kỳ tham số nào
marketing_analyst_names = pd.read_excel["MarketingAnalystNames.xlsx"]sales_rep_names = pd.read_excel["SalesRepNames.xlsx"]senior_leadership_names = pd.read_excel["SeniorLeadershipNames.xlsx"]Bây giờ chúng tôi có ba biến chứa khung dữ liệu gấu trúc chứa tất cả các giá trị trong tệp excel ở định dạng hàng và cột
II. Hợp nhất cả ba khung dữ liệuBây giờ chúng ta sẽ hợp nhất cả ba khung dữ liệu
- Chúng tôi sẽ tạo một danh sách chứa cả ba khung dữ liệu. Thứ tự của các khung dữ liệu trong danh sách này sẽ xác định cách các tệp được thêm vào
all_df_list = [marketing_analyst_names, sales_rep_names, senior_leadership_names]2. Lệnh nối cả ba khung dữ liệu là hàm 1 dòng. Chúng tôi sẽ gọi pd. concat với danh sách của chúng ta, nó sẽ nối tất cả các khung dữ liệu của chúng ta theo thứ tự đó và gán khung dữ liệu đã nối vào một biến appended_df
appended_df = pd.concat[all_df_list]Việc nối thêm được thực hiện bằng cách ghép các tên cột tương tự. Nếu các tên cột không giống nhau thì phần nối thêm sẽ không xếp chồng các giá trị lên nhau
Tôi khuyến khích các bạn tạo các bảng tính giả với các tên cột khác nhau để xem phần bổ sung bị ảnh hưởng như thế nào
III. Ghi vào tệp ExcelBây giờ chúng ta đã có khung dữ liệu được nối thêm, chúng ta có thể ghi nó vào tệp Excel. Chúng tôi thực hiện việc này bằng hàm to_excel trên khung dữ liệu được nối thêm. Chúng tôi cũng sẽ thêm tham số index=False sẽ KHÔNG xuất bất kỳ số hàng nào. [chơi xung quanh với các thông số để xem cái nào phù hợp với bạn]
appended_df.to_excel["AllCompanyNames.xlsx", index=False]Bây giờ chúng ta đã hoàn thành mã, chúng ta cần mở Terminal và chuyển đến thư mục nơi chúng ta có tất cả ba tệp và phần bổ sung của chúng ta. đã lưu tệp py
Nếu bạn bị mất thiết bị đầu cuối, vui lòng xem hướng dẫn này trước. https. //Trung bình. com/@lovespreadsheets/giới thiệu-xử lý-bảng tính-với-python-c6a35e0d8de8
Chúng tôi sẽ chạy tập lệnh này từ thiết bị đầu cuối của chúng tôi
$ python append.pythì đấy. Bây giờ chúng ta sẽ có một tệp có tên AllCompanyNames. xlsx trong cùng thư mục với ba bảng tính của chúng tôi và tập lệnh Python chứa tất cả ba tệp được thêm vào
Nếu bạn hỏi những người làm việc với dữ liệu, bạn sẽ biết rằng việc kết hợp các tệp Excel hoặc hợp nhất sổ làm việc là một phần công việc hàng ngày của họ
Đồng ý không?
Một ví dụ đơn giản. Giả sử bạn muốn tạo một báo cáo bán hàng và bạn có dữ liệu của 4 khu vực khác nhau trong 4 tệp khác nhau
Hiện nay
Điều đầu tiên bạn cần làm là kết hợp các tệp đó trong một sổ làm việc duy nhất và chỉ khi đó bạn mới có thể tạo thêm báo cáo của mình
Điểm mấu chốt là. Bạn phải có một phương pháp mà bạn có thể sử dụng để hợp nhất các tệp này. Nói “CÓ” trong phần bình luận nếu bạn muốn biết phương pháp tốt nhất cho việc này
Hôm nay trong bài đăng này, tôi sẽ chia sẻ với bạn cách tốt nhất để hợp nhất dữ liệu từ nhiều tệp Excel vào một sổ làm việc DUY NHẤT
Nhưng, đây là kicker
Bài đăng này sẽ dạy cho bạn những điều bạn cần học để sử dụng trong vấn đề dữ liệu trong thế giới thực, vì vậy hãy đảm bảo đọc toàn bộ bài đăng
Cách tốt nhất có thể để kết hợp các tệp Excel bằng cách hợp nhất dữ liệu vào MỘT sổ làm việc - POWER QUERY
Power Query là cách tốt nhất để hợp nhất hoặc kết hợp dữ liệu từ nhiều tệp Excel trong một tệp. Bạn cần lưu trữ tất cả các tệp trong một thư mục và sau đó sử dụng thư mục đó để tải dữ liệu từ các tệp đó vào trình chỉnh sửa truy vấn nguồn. Nó cũng cho phép bạn chuyển đổi dữ liệu đó cùng với việc kết hợp
Nó hoạt động một cái gì đó như thế này
- Lưu tất cả các tệp vào một thư mục
- Kết hợp chúng bằng Power Query
- Hợp nhất dữ liệu vào một bảng duy nhất
Đảm bảo tải xuống các tệp mẫu này từ đây để làm theo và xem hướng dẫn này để tìm hiểu truy vấn nguồn
Ghi chú. Để kết hợp dữ liệu từ các tệp Excel khác nhau, dữ liệu của bạn phải được cấu trúc theo cùng một cách. Điều đó có nghĩa là số lượng cột và thứ tự của chúng phải giống nhau
Để hợp nhất các tệp, bạn có thể sử dụng các bước sau
- Trước hết, giải nén tất cả các tệp từ thư mục mẫu và lưu thư mục đó trên màn hình nền [hoặc bất cứ nơi nào bạn muốn lưu nó]
- Bây giờ, việc tiếp theo là mở một sổ làm việc Excel mới và mở “POWER Query”
- Đối với điều này, hãy chuyển đến Tab Dữ liệu ➜ Nhận & Chuyển đổi Dữ liệu ➜ Nhận Dữ liệu ➜ Từ Tệp ➜ Từ Thư mục
- Ở đây bạn cần xác định vị trí thư mục chứa tệp
- Cuối cùng, nhấp vào OK và khi bạn nhấp vào OK, bạn sẽ nhận được một cửa sổ liệt kê tất cả các tệp từ thư mục, giống như bên dưới
- Bây giờ, bạn cần kết hợp dữ liệu từ các tệp này và để thực hiện việc này, hãy nhấp vào “Kết hợp & Chỉnh sửa”
- Từ đây, việc tiếp theo là chọn bảng mà bạn có dữ liệu trong tất cả các sổ làm việc và vâng, bạn sẽ nhận được bản xem trước của bảng này ở bên cạnh cửa sổ
- Khi bạn chọn bảng, bấm OK. Tại thời điểm này, bạn đã hợp nhất dữ liệu từ tất cả các tệp vào trình soạn thảo truy vấn nguồn của mình và nếu nhìn kỹ, bạn có thể thấy một cột mới có tên sổ làm việc mà dữ liệu được trích xuất từ đó
- Vì vậy, nhấp chuột phải vào tiêu đề cột và chọn “Thay thế giá trị”
- Tại đây, trong “Giá trị cần thay thế”, hãy nhập văn bản “. xlsx” và để trống “Replace With” [ý tưởng ở đây là xóa phần mở rộng tệp khỏi tên của sổ làm việc]
- Sau đó, nhấp đúp vào tiêu đề và chọn “Đổi tên” để nhập tên cho cột i. e. Vùng
. - Tại thời điểm này, dữ liệu được hợp nhất của bạn đã sẵn sàng và tất cả những gì bạn cần là tải nó vào sổ làm việc mới của mình. Vì vậy, hãy chuyển đến Tab Trang chủ và nhấp vào “Đóng và tải”
Bây giờ bạn có dữ liệu kết hợp của mình [từ tất cả các sổ làm việc] vào một sổ làm việc
Đây là khoảnh khắc của NIỀM VUI, hãy viết “Niềm vui” trong phần nhận xét nếu bạn thích sử dụng “Power Query để kết hợp dữ liệu từ nhiều tệp”
Tâm điểm
Trong các bước trên, chúng tôi đã sử dụng tên bảng để kết hợp dữ liệu từ tất cả các tệp và thêm tất cả dữ liệu đó vào một sổ làm việc. Nhưng không phải lúc nào bạn cũng có cùng một tên bảng trong tất cả các tệp Excel và tại thời điểm đó, bạn có thể sử dụng tên bảng tính làm chìa khóa để tóm tắt tất cả dữ liệu đó
Một điều nữa
Như tôi đã nói, bạn có thể sử dụng tên trang tính để kết hợp dữ liệu với truy vấn nguồn nhưng có một vài điều nữa mà tôi muốn chia sẻ với bạn và bạn cần quan tâm đến những điều đó. Power Query phân biệt chữ hoa chữ thường, vì vậy, khi kết hợp các tệp, hãy đảm bảo tên của các trang tính trong tất cả các sổ làm việc có cùng một chữ cái
Điều tiếp theo là đặt cùng tên cho các tiêu đề cột, nhưng ở đây kicker. Thứ tự của các cột không quan trọng. Nếu cột1 ở phía bắc. xlsx là cột2 ở phía tây. xlsx, Power Query sẽ khớp với nó, nhưng bạn phải đặt tên cột giống nhau
Vì vậy, bây giờ, trong khi kết hợp các tệp bằng truy vấn nguồn, bạn có thể sử dụng tên trang tính thay vì tên bảng và ở đây bạn có "SalesData" làm tên trang tính trong tất cả các tệp
Bạn chọn nó và nhấp vào "Kết hợp & Chỉnh sửa" và làm theo tất cả các bước mà tôi đã đề cập trong phương pháp trên
Tại sao Power Query là cách tốt nhất để hợp nhất dữ liệu vào một tệp?
Vâng, tôi rất hâm mộ Power Query và bạn cũng sẽ như vậy nếu bạn biết điều này
Đó là thời gian thực
Khi bạn tạo một truy vấn kết hợp dữ liệu từ nhiều sổ làm việc thành một, bạn không cần thực hiện lại nhiều lần
Hãy cùng nói nào
Nếu bạn thêm tệp mới vào thư mục đó, bạn chỉ cần làm mới bảng,
hoặc, làm mới truy vấn,
hoặc, sử dụng nút làm mới
Và truy vấn nguồn sẽ cập nhật bảng của bạn và bạn sẽ nhận được tất cả dữ liệu vào bảng từ tất cả sổ làm việc bạn có trong thư mục đó
Nó nhanh
Nếu kết hợp các tệp theo cách thủ công hoặc thậm chí bằng bất kỳ phương pháp nào khác thì bạn sẽ mất thời gian để sao chép-dán dữ liệu vào một bảng
Nhưng truy vấn nguồn thực hiện nó ở chế độ nền và bạn không cần phải tốn thêm giây
Hợp nhất dữ liệu từ nhiều sổ làm việc Khi bạn không có cùng tên cho các trang tính và dữ liệu trong bảng
Đây là sự thật phũ phàng…
…rằng trong một số trường hợp, bạn sẽ không có cùng tên cho các trang tính và không phải lúc nào tất cả dữ liệu trong bảng cũng giống nhau
Bây giờ, bạn nên làm gì trong trường hợp đó?
Tốt…
…trong trường hợp này, bạn phải biết cách kết hợp dữ liệu từ tất cả các tệp và tôi không muốn bỏ lỡ để chia sẻ điều này với bạn
Đảm bảo tải xuống các tệp mẫu này để làm theo và lưu nó trên máy tính để bàn của bạn
Vì vậy, không có bất kỳ rắc rối nào nữa, hãy bắt đầu
- Trước hết, mở hộp thoại “Từ thư mục” để định vị thư mục chứa tất cả các tệp.
- Bây giờ trong hộp thoại này, tìm thư mục và nhấp vào OK.
- Sau đó, nhấp vào “Chỉnh sửa” để chỉnh sửa bảng.
- Tại thời điểm này, bạn sẽ có một bảng như bên dưới trong trình chỉnh sửa truy vấn nguồn của mình
- Tiếp theo, chọn hai cột đầu tiên của bảng và nhấp vào “Xóa các cột khác” từ menu chuột phải.
Từ đây, chúng ta cần thêm một cột tùy chỉnh để tìm nạp dữ liệu từ các trang tính của sổ làm việc.
- Đối với điều này, hãy chuyển đến Thêm tab cột và nhấp vào nút “Cột tùy chỉnh” . Thao tác này sẽ mở hộp thoại “Cột tùy chỉnh”.
- Trong hộp thoại, nhập =Excel. Sổ làm việc[[Nội dung]] và nhấp vào OK.
… lúc này bạn có một cột mới trong bảng nhưng tiếp theo, bạn cần trích xuất dữ liệu từ nó
- Bây giờ, hãy mở bộ lọc từ cột tùy chỉnh mới thêm đó và nhấp vào OK để mở rộng tất cả dữ liệu vào bảng.
- Ở đây bạn có bảng mới được mở rộng với một số cột mới
- Bây giờ từ bảng mới này, xóa tất cả các cột trừ thứ ba và thứ tư
- Vì vậy, hãy mở bộ lọc cho cột “Tùy chỉnh. Dữ liệu” để mở rộng và nhấp vào OK.
Thời điểm bạn nhấp vào OK, bạn sẽ nhận được tất cả dữ liệu từ tất cả các tệp vào một bảng duy nhất…
… bạn cần thực hiện một số thay đổi để làm cho nó HOÀN HẢO.
Nếu bạn để ý thì tất cả các tiêu đề của cột đều nằm trong dữ liệu
vì vậy bạn cần thêm tiêu đề cột
- Để làm điều này, bạn cần nhấp đúp vào tiêu đề và thêm tên hoặc bạn có thể nhấp chuột phải và chọn đổi tên nó
Tiếp theo, bạn cần loại trừ các tiêu đề mà bạn có trong bảng dữ liệu
- Bây giờ hãy mở tùy chọn bộ lọc của bất kỳ cột nào và bỏ chọn tên tiêu đề mà bạn có trong dữ liệu cột và nhấp vào OK sau đó
Bây giờ dữ liệu của chúng tôi đã sẵn sàng để tải vào trang tính, vì vậy, hãy chuyển đến Tab Trang chủ và nhấp vào đóng và tải
Xin chúc mừng. bạn vừa kết hợp dữ liệu từ các sổ làm việc khác nhau [với tên các trang tính khác nhau và không có bất kỳ bảng nào].
Điều này cũng quan trọng
Tại thời điểm này, bạn đã hợp nhất dữ liệu thành một bảng
Nhưng có một điều bạn cần làm…
…và đó là áp dụng một số định dạng cho nó và đảm bảo rằng nó sẽ không biến mất khi bạn cập nhật dữ liệu của mình
Đây là những gì bạn cần làm…
- Trước hết, hãy chọn cột mà bạn có ngày tháng [vì hiện tại cột này được định dạng là số] và định dạng cột đó là ngày tháng
- Sau đó, làm cho tất cả các cột rộng theo dữ liệu bạn có trong đó
- Tại đây, bạn cũng có thể định dạng số tiền và giá thành “Tiền tệ”.
Nhưng điều tiếp theo là sửa lỗi định dạng này
- Đối với điều này, hãy chuyển đến “Tab thiết kế” và mở thuộc tính
- Bỏ chọn chiều rộng “Điều chỉnh cột” và đánh dấu chọn “Giữ nguyên định dạng ô”
- Vâng, đó là nó
Bây giờ bạn có một truy vấn trong sổ làm việc của mình có thể kết hợp dữ liệu từ nhiều tệp
và hợp nhất nó vào một sổ làm việc duy nhất
ngay cả khi tên trang tính không giống nhau hoặc nếu bạn không có bảng
Và vâng, bạn cũng đã sửa lỗi định dạng. ?
Đến cuối cùng,
Như tôi đã nói, POWER QUERY là có thật và nếu bạn thường xuyên sử dụng để kết hợp dữ liệu từ nhiều tệp thì bạn phải sử dụng phương pháp này…
…vì đây là thiết lập MỘT LẦN
Điều quan trọng nhất là bạn khi bạn sử dụng truy vấn nguồn, bạn thậm chí có thể xóa dữ liệu khỏi các tệp đó
Tôi hy vọng hướng dẫn này sẽ giúp bạn sử dụng Excel tốt hơn. Nhưng bây giờ, bạn cần phải nói với tôi một điều
Bạn sử dụng phương pháp nào để kết hợp dữ liệu từ nhiều tệp?
Đảm bảo chia sẻ quan điểm của bạn với tôi trong phần bình luận, tôi rất muốn nghe ý kiến từ bạn. Và xin đừng quên chia sẻ bài đăng này với bạn bè của bạn, tôi chắc chắn họ sẽ đánh giá cao nó
Chia sẻ0
Tweet0
Bạn phải đọc những điều này Tiếp theo
- Hợp nhất dữ liệu từ nhiều trang tính. Tùy chọn này có thể giúp bạn kết hợp dữ liệu từ nhiều trang tính thành một trang tính duy nhất
- Hủy xoay vòng dữ liệu bằng Power Query. Trong tình huống này, bạn cần nỗ lực và dành thời gian quý báu của mình để làm cho nó có thể sử dụng lại được
- Tạo một Pivot từ nhiều tệp. Trong bài đăng này, tôi muốn chỉ cho bạn quy trình 3 bước để tạo bảng tổng hợp bằng cách sử dụng dữ liệu từ nhiều
Giới thiệu về tác giả
Puneet đang sử dụng Excel từ những ngày còn học đại học. Anh ấy đã giúp hàng nghìn người hiểu được sức mạnh của bảng tính và học Microsoft Excel. Bạn có thể tìm thấy anh ấy trực tuyến, tweet về Excel, trên đường chạy hoặc đôi khi đi bộ lên núi