Ian ở Nashville nhận dữ liệu mỗi ngày từ bản tải xuống hệ thống. Cột ngày chứa Ngày + Giờ. Điều này làm cho bảng tổng hợp có nhiều hàng mỗi ngày thay vì tóm tắt một ô mỗi ngày.
Bài viết này chỉ ra ba cách để giải quyết vấn đề.
Hình dưới đây cho thấy cột Ngày với Ngày cộng với thời gian.
Một giải pháp là sửa nó trong bảng tổng hợp.
- Kéo trường Ngày vào vùng Hàng. Nó hiển thị với Ngày + Giờ.
- Chọn bất kỳ ô nào chứa ngày và giờ.
- Trên tab Phân tích Công cụ Bảng Pivot, hãy chọn Trường Nhóm.
- Trong hộp thoại Nhóm, chọn Ngày. Bỏ chọn Tháng. Khi bạn nhấp vào OK, bảng tổng hợp sẽ hiển thị một hàng mỗi ngày.
Một giải pháp tẻ nhạt khác là thêm một cột mới sau khi tải xuống. Như thể hiện trong hình này, công thức trong cột J là =INT(D2).
Ngày + giờ là một ngày nguyên theo sau là thời gian thập phân. Bằng cách sử dụng hàm INT để lấy phần nguyên của ngày + giờ, bạn tách riêng ngày.
Điều này sẽ hoạt động tốt, nhưng bây giờ bạn phải thêm cột mới mỗi khi tải xuống dữ liệu.
Giải pháp ưa thích của tôi là sử dụng Power Query. Power Query chỉ hoạt động trong các phiên bản Windows của Excel. Bạn không thể sử dụng Power Query trên Android, IOS hoặc Mac.
Power Query được tích hợp sẵn trong Excel 2016. Đối với Excel 2010 và 2013, bạn có thể tải xuống Power Query từ Microsoft.
Đầu tiên, chuyển đổi dữ liệu đã tải xuống thành Bảng bằng Ctrl + T. Sau đó, trên tab Dữ liệu, chọn Từ Bảng / Phạm vi.
Trong lưới Power Query, hãy chọn cột Ngày. Chuyển đến tab Chuyển đổi. Mở menu thả xuống Loại ngày và chọn Ngày. Nếu bạn nhận được thông báo về Bước Thay thế hoặc Bước Mới, hãy chọn Bước Thay thế.
Trên tab Trang đầu của Power Query, chọn Đóng và Tải.
Kết quả của bạn sẽ xuất hiện trên một trang tính mới. Tạo bảng tổng hợp từ trang tính đó.
Lần tới khi bạn nhận được dữ liệu mới, hãy làm theo các bước sau:
- Dán dữ liệu mới lên tập dữ liệu ban đầu.
- Chuyển đến trang tính có kết quả của Power Query. Mở rộng bảng Truy vấn để bạn có thể thấy Làm mới. Nhấp vào Làm mới.
- Làm mới bảng tổng hợp.
Xem video
Bản ghi video
Học Excel từ Podcast, Tập 2203: Rút gọn ngày và giờ thành một ngày.
Chào mừng bạn trở lại netcast, tôi là Bill Jelen. Câu hỏi hôm nay của Ian trong buổi hội thảo Nashville Power Excel của tôi. Hãy xem này: Ian đang tải xuống từ hệ thống của anh ấy mỗi ngày và nó có-- trong cột Ngày - nó có Ngày và Giờ, và điều đó làm mọi thứ trở nên rối ren vì khi Ian tạo Bảng xoay vòng và đặt Ngày phía bên trái, thay vì chỉ nhận được một hàng cho mỗi Ngày, anh ấy nhận một hàng cho mỗi Ngày và Giờ.
Bây giờ, có thể vào đây và nói, Trường Nhóm, và nhóm cái này thành Ngày, bấm OK. Và, bạn biết đấy, đó là một vài cú nhấp chuột bổ sung mà tôi không nghĩ rằng chúng ta phải làm. Và, vì vậy, đối với Podcast này, một vài Podcast tiếp theo, chúng ta sẽ xem xét Power Query. Bây giờ, nó được tích hợp vào Excel 2016 tại đây, trong Lấy & Chuyển đổi Dữ liệu. Nếu bạn đang chạy phiên bản 2010 hoặc 2013 trong Windows, không phải trên Mac, bạn có thể tải xuống Power Query miễn phí từ Excel. Và Power Query muốn hoạt động trên Bảng hoặc Phạm vi.
Vì vậy, tôi sẽ chọn một ô dữ liệu, nhấn Ctrl + T cho Bảng, hoàn hảo. Và sau đó, trên tab Power Query, Dữ liệu, Từ Bảng / Phạm vi, tôi sẽ chọn cột Ngày và Chuyển đổi, và nói rằng tôi muốn đây không phải là Ngày và Giờ, mà chỉ là Ngày. Tôi sẽ chọn Thay thế hiện tại, sau đó Trang chủ, Đóng & Tải và chúng tôi nhận được một biểu mẫu trang tính hoàn toàn mới có dữ liệu được chuyển đổi. Tất nhiên, từ đây, chúng ta có thể tóm tắt bằng một trục và nó sẽ rất hoàn hảo.
Tôi nghĩ, lợi thế của phương pháp này, Power Query, là khi tôi nhận được nhiều dữ liệu hơn… Vì vậy, ở đây tôi có dữ liệu mới, tôi sẽ lấy dữ liệu mới này, sao chép dữ liệu và chỉ cần đến đây với dữ liệu cũ của tôi và dán ngay bên dưới. Và, hãy lưu ý rằng điểm đánh dấu cuối bảng hiện đang ở Hàng 474. Khi tôi dán, điểm đánh dấu cuối bảng sẽ di chuyển xuống cuối dữ liệu mới. Ổn thỏa? Và truy vấn này được viết dựa trên bảng. Ổn thỏa? Vì vậy, khi bảng phát triển.
Bây giờ, một rắc rối ở đây là, nếu bạn đang sử dụng Power Query lần đầu tiên, Power Query bắt đầu rộng như vậy - chỉ rộng một nửa - và bạn không thể nhìn thấy biểu tượng Làm mới. Vì vậy, bạn phải kéo nó ra đây như thế này và nhận được biểu tượng Làm mới tuyệt vời đó và chỉ cần nhấp vào Làm mới. Hiện tại, chúng ta có 473 hàng được tải và bây giờ là 563 hàng được tải. Tạo bảng tổng hợp của bạn và cuộc sống thật tuyệt vời.
Bây giờ, này, tôi biết có thể thêm một cột mới ở đây, = INT (D2) - - của ngày-- sao chép nó xuống, sao chép và Dán các Giá trị Đặc biệt. Nhưng sau đó bạn đang say mê làm điều đó mỗi ngày. Ổn thỏa? Vì vậy, mặc dù có ít nhất ba cách khác nhau mà chúng ta đã đề cập trong video này để giải quyết nó, đối với tôi, Power Query và khả năng nhấp vào Làm mới là cách để thực hiện.
Power Query, tôi nói về nó trong cuốn sách mới của tôi, MrExcel LIVe, 54 mẹo Excel hay nhất mọi thời đại.
Tóm tắt của Tập ngày hôm nay: Ian ở Nashville lấy dữ liệu từ một hệ thống mỗi ngày, cột Ngày gây phiền nhiễu có cả Ngày cộng với Giờ - nó làm hỏng bảng tổng hợp của anh ấy, vì vậy anh ấy phải… Chà, ba điều khả thi: Một, sử dụng hàm INT, sao chép, dán các giá trị; thứ hai, chỉ cần để nó là Ngày và Giờ và sau đó nhóm bảng tổng hợp theo Ngày; hoặc giải pháp tốt hơn, Power Query, làm cho tập dữ liệu đã tải xuống thành một bảng bằng Ctrl + T, sau đó trên tab Dữ liệu, chọn Từ Bảng, chọn cột Ngày và giờ, chuyển đến tab Chuyển đổi trong Power Query, chuyển đổi nó thành Ngày, Đóng & Tải, xây dựng bảng tổng hợp từ đó. Lần tới khi bạn lấy dữ liệu, hãy dán vào bảng gốc, chuyển đến bảng Truy vấn, nhấp vào Làm mới và bạn đã sẵn sàng.
Này, để tải xuống sổ làm việc từ video hôm nay, hãy truy cập URL trong mô tả YouTube.
Tôi muốn cảm ơn Ian vì đã xuất hiện trong buổi hội thảo của tôi ở Nashville, tôi muốn cảm ơn bạn đã ghé qua. Hẹn gặp lại bạn lần sau cho một netcast khác từ.
Tải xuống tệp Excel
Để tải xuống tệp excel: date-time-to-date.xlsx
Để cải thiện hơn nữa quy trình Power Query:
- Đảm bảo rằng sổ làm việc tải xuống luôn được lưu trong cùng một thư mục có cùng tên.
- Bắt đầu từ một sổ làm việc trống. Lưu dưới dạng với tên như DownloadedFileTransformed.xlsx
- Từ sổ làm việc trống, Dữ liệu, Từ tệp, Từ sổ làm việc. Trỏ đến tệp từ Bước 1.
- Chọn Dữ liệu sạch. Chuyển đổi Ngày / Giờ thành Ngày. Đóng và tải.
- Bấm chuột phải vào Bảng truy vấn và chọn Thuộc tính. Chọn Làm mới dữ liệu khi mở tệp.
- Mỗi ngày, đi làm. Lấy cà phê của bạn. Đảm bảo rằng một tệp mới cho # 1 nằm trong thư mục.
- Mở sổ làm việc DownloadFileTransformed.xlsx của bạn. Nó sẽ có dữ liệu mới và ngày tháng sẽ chính xác.
Suy nghĩ của Excel trong ngày
Tôi đã hỏi những người bạn Excel Master của mình để được tư vấn về Excel. Hôm nay cần suy ngẫm:
"Xác thực là bạn của bạn."
Duane Aubin
