Nội dung
Nhiều quy trình kinh doanh (và thậm chí toàn bộ hoạt động kinh doanh) trong cuộc sống này liên quan đến việc thực hiện các đơn đặt hàng của một số lượng người thực hiện hạn chế theo một thời hạn nhất định. Việc lập kế hoạch trong những trường hợp như vậy xảy ra, như họ nói, “từ lịch” và thường cần phải chuyển các sự kiện được lên kế hoạch trong đó (đơn đặt hàng, cuộc họp, giao hàng) sang Microsoft Excel – để phân tích thêm bằng công thức, bảng tổng hợp, biểu đồ, vân vân.
Tất nhiên, tôi muốn thực hiện việc chuyển giao như vậy không phải bằng cách sao chép ngu ngốc (điều này không khó) mà bằng việc cập nhật dữ liệu tự động để trong tương lai, tất cả các thay đổi được thực hiện đối với lịch và các đơn đặt hàng mới sẽ được hiển thị nhanh chóng trong Excel. Bạn có thể thực hiện quá trình nhập như vậy chỉ trong vài phút bằng cách sử dụng bổ trợ Power Query được tích hợp trong Microsoft Excel, bắt đầu từ phiên bản 2016 (đối với Excel 2010-2013, có thể tải xuống từ trang web của Microsoft và cài đặt riêng từ liên kết) .
Giả sử chúng ta sử dụng Lịch Google miễn phí để lập kế hoạch, trong đó, để thuận tiện, tôi đã tạo một lịch riêng (nút có dấu cộng ở góc dưới bên phải bên cạnh Các lịch khác) với tiêu đề Công việc. Tại đây chúng ta nhập tất cả các đơn hàng cần hoàn thành và giao cho khách hàng theo địa chỉ:
Bằng cách nhấp đúp vào bất kỳ đơn hàng nào, bạn có thể xem hoặc chỉnh sửa chi tiết của đơn hàng đó:
Lưu ý rằng:
- Tên của sự kiện là giám đốcngười thực hiện mệnh lệnh này (Elena) và số thứ tự
- Chỉ ra địa chỉ giao hàng
- Ghi chú chứa (trong các dòng riêng biệt, nhưng theo bất kỳ thứ tự nào) các thông số của đơn đặt hàng: loại thanh toán, số tiền, tên khách hàng, v.v. ở định dạng Tham số=Giá trị.
Để rõ ràng, mệnh lệnh của mỗi người quản lý được đánh dấu bằng màu riêng của họ, mặc dù điều này không cần thiết.
Bước 1. Nhận liên kết tới Lịch Google
Đầu tiên chúng ta cần lấy một liên kết web đến lịch đặt hàng của mình. Để thực hiện việc này, hãy nhấp vào nút có ba dấu chấm Tùy chọn lịch làm việc bên cạnh tên lịch và chọn lệnh Cài đặt và chia sẻ:
Trong cửa sổ mở ra, nếu muốn, bạn có thể đặt lịch ở chế độ công khai hoặc mở quyền truy cập vào lịch cho người dùng cá nhân. Chúng tôi cũng cần một liên kết để truy cập riêng vào lịch ở định dạng iCal:
Bước 2. Tải dữ liệu từ lịch vào Power Query
Bây giờ hãy mở Excel và trên tab Ngày (nếu bạn có Excel 2010-2013 thì trên tab Truy vấn nguồn) chọn lệnh Từ trên mạng (Dữ liệu - Từ Internet). Sau đó dán đường dẫn đã sao chép vào lịch và nhấn OK.
iCal Power Query không nhận dạng được định dạng nhưng rất dễ trợ giúp. Về cơ bản, iCal là một tệp văn bản đơn giản có dấu hai chấm làm dấu phân cách và bên trong nó trông giống như thế này:
Vì vậy, bạn chỉ cần nhấp chuột phải vào biểu tượng của tệp đã tải xuống và chọn định dạng gần nghĩa nhất CSV – và dữ liệu của chúng tôi về tất cả các đơn hàng sẽ được tải vào trình soạn thảo truy vấn Power Query và được chia thành hai cột bằng dấu hai chấm:
Nếu nhìn kỹ, bạn có thể thấy rõ rằng:
- Thông tin về mỗi sự kiện (thứ tự) được nhóm thành một khối bắt đầu bằng từ BEGIN và kết thúc bằng END.
- Ngày giờ bắt đầu và kết thúc được lưu trữ trong các chuỗi có nhãn DTSTART và DTEND.
- Địa chỉ giao hàng là LOCATION.
- Ghi chú đơn hàng – trường MÔ TẢ.
- Tên sự kiện (tên người quản lý và số đơn đặt hàng) — trường TÓM TẮT.
Việc còn lại là trích xuất thông tin hữu ích này và chuyển nó thành một bảng tiện lợi.
Bước 3. Chuyển đổi sang chế độ xem bình thường
Để thực hiện việc này, hãy thực hiện chuỗi hành động sau:
- Hãy xóa 7 dòng trên cùng mà chúng ta không cần trước lệnh BEGIN đầu tiên Trang chủ - Xóa hàng - Xóa hàng trên cùng (Trang chủ — Xóa hàng — Xóa các hàng trên cùng).
- Lọc theo cột Column1 các dòng chứa các trường chúng ta cần: DTSTART, DTEND, DESCRIPTION, LOCATION và SUMMARY.
- Trên tab Nâng cao Thêm một cột chọn Cột chỉ mục (Thêm cột - Cột chỉ mục)để thêm cột số hàng vào dữ liệu của chúng tôi.
- Ngay trên tab. Thêm một cột chọn một đội Cột có điều kiện (Thêm cột — Cột có điều kiện) và ở đầu mỗi khối (thứ tự) chúng tôi hiển thị giá trị của chỉ mục:
- Điền vào các ô trống trong cột kết quả Chặnbằng cách nhấp chuột phải vào tiêu đề của nó và chọn lệnh Điền vào (Điền vào).
- Xóa cột không cần thiết Chỉ số.
- Chọn một cột Column1 và thực hiện tích chập dữ liệu từ cột Column2 sử dụng lệnh Chuyển đổi - Cột xoay vòng (Chuyển đổi - Cột xoay vòng). Hãy chắc chắn để chọn trong các tùy chọn Không tổng hợp (Không tổng hợp)để không có hàm toán học nào được áp dụng cho dữ liệu:
- Trong bảng hai chiều (chéo) kết quả, hãy xóa dấu gạch chéo ngược trong cột địa chỉ (nhấp chuột phải vào tiêu đề cột – Thay thế các giá trị) và xóa cột không cần thiết Chặn.
- Để chuyển nội dung của các cột DTBẮT ĐẦU и DTEND trong một ngày đầy đủ, đánh dấu chúng, chọn trên tab Phân tích Chuyển đổi – Ngày – Chạy (Chuyển đổi — Ngày tháng — Phân tích cú pháp). Sau đó chúng ta sửa mã trong thanh công thức bằng cách thay thế hàm Từ ngày on Ngày giờ.Từđể không làm mất giá trị thời gian:
- Sau đó, bằng cách nhấp chuột phải vào tiêu đề, chúng tôi chia cột MÔ TẢ với tham số thứ tự bằng dấu phân cách – ký hiệu n, nhưng đồng thời, trong phần tham số, chúng ta sẽ chọn cách chia thành hàng chứ không phải chia thành cột:
- Một lần nữa, chúng ta chia cột kết quả thành hai cột riêng biệt – tham số và giá trị, nhưng bằng dấu bằng.
- Chọn một cột MÔ TẢ.1 thực hiện phép tích chập, như chúng ta đã làm trước đó, bằng lệnh Chuyển đổi - Cột xoay vòng (Chuyển đổi - Cột xoay vòng). Cột giá trị trong trường hợp này sẽ là cột có các giá trị tham số − MÔ TẢ.2 Hãy chắc chắn chọn một chức năng trong các tham số Không tổng hợp (Không tổng hợp):
- Vẫn còn phải đặt định dạng cho tất cả các cột và đổi tên chúng theo ý muốn. Và bạn có thể tải kết quả lên Excel bằng lệnh Trang chủ - Đóng và tải - Đóng và tải vào… (Trang chủ - Đóng & Tải - Đóng & Tải vào…)
Và đây là danh sách các đơn hàng được tải vào Excel từ Lịch Google:
Trong tương lai, khi thay đổi hoặc thêm đơn hàng mới vào lịch, chỉ cần cập nhật yêu cầu của chúng tôi bằng lệnh là đủ Dữ liệu - Làm mới tất cả (Dữ liệu - Làm mới tất cả).
- Lịch nhà máy trong Excel được cập nhật từ internet thông qua Power Query
- Chuyển một cột thành một bảng
- Tạo cơ sở dữ liệu trong Excel