Đây là vấn đề ngân sách của lính cứu hỏa. Những người trong một nhà cứu hỏa đã làm sai ngân sách của họ trong Excel. Một chuyển đổi Power Query tuyệt vời cung cấp giải pháp.
Xem video
- Steve phải tính tổng các số đã được nhập vào một cột văn bản
- Có nhiều dòng trong mỗi ô, được phân tách bằng alt = "" + Enter
- Cần chia các dòng đó thành các hàng, sau đó phân tích cú pháp số tiền từ giữa mỗi ô
- Tổng hợp bởi Trung tâm Chi phí
- Xây dựng bảng tra cứu
- Nhận tổng số từ bảng tra cứu, sử dụng IFNA để bỏ qua các lỗi trong hàng trống
- Phần thưởng: Thêm macro Sự kiện để cập nhật trang tính khi chúng thay đổi ô.
Bản ghi video
Học Excel từ, Podcast Tập 2160: Dữ liệu SUM Đã được Alt + Nhập.
Chào. Chào mừng bạn trở lại netcast. Tôi là Bill Jelen. Tôi không bịa ra điều này. Tôi nhận được một câu hỏi từ một người có dữ liệu - dữ liệu ngân sách - trông như thế này. Bây giờ, tôi đặt những từ giả vào đây để chúng tôi không có thông tin ngân sách của họ, nhưng một người mới vào bộ phận kế toán, đã đến một công ty, và công ty này trong nhiều năm đã làm ngân sách của họ như thế này. Họ không phải là nhân viên kế toán thực hiện ngân sách, họ là những người đứng đầu, nhưng đây là cách họ đã làm và anh ấy không thể khiến họ thay đổi. Vì vậy, đây là mục tiêu của chúng tôi. Anh ấy nói điều này cũng tệ như nhập ngân sách vào Word.
Chà, gần như vậy, nhưng may mắn thay, nhờ truy vấn nguồn, nó sẽ cứu được sự cố của chúng ta. Đây là mục tiêu của chúng tôi. Đối với mỗi TRUNG TÂM CHI PHÍ ở đây, chúng tôi muốn báo cáo tổng của tất cả những con số đó. Vì vậy, có tên chi phí, a -, thường là a -, sau đó là dấu $, và sau đó, chỉ để làm cho cuộc sống thú vị, thỉnh thoảng, một ghi chú ngẫu nhiên sau đó; không phải mọi lúc, chỉ một số thời gian. Hàng trống giữa mỗi hàng. Tấn và tấn dữ liệu.
Vì vậy, đây là những gì tôi sẽ làm. Tôi sẽ xuống dưới cùng, ô cuối cùng, tôi sẽ chọn tất cả những thứ này, bao gồm cả các tiêu đề. Tôi sẽ tạo một NAME. Tôi sẽ gọi nó là MyData. MyData, như vậy, được không? Ổn thỏa. Bây giờ chúng ta sẽ sử dụng truy vấn nguồn miễn phí trong năm 2010 hoặc 2013, được tích hợp vào Office 365 2016 và 2016. Điều đó sẽ đến từ BẢNG HOẶC RANGE. Ổn thỏa. Điều đầu tiên, bất cứ khi nào chúng ta có những khoảng trống đó trong COLUMN A, tất cả các NULL mà chúng ta muốn loại bỏ. Vì vậy, tôi sẽ bỏ chọn NULL. Tuyệt vời. Được chứ. Thực sự, trong dữ liệu này, trong phiên bản dữ liệu này, vì tôi sẽ xây dựng một VLOOKUP nên chúng tôi không cần cột này. Vì vậy, tôi sẽ nhấp chuột phải và thoát khỏi cột đó, vì vậy hãy XÓA cột.
Ổn thỏa. Bây giờ, đây là nơi mà điều kỳ diệu sẽ xảy ra. Chọn cột này, SPLIT COLUMN BY A DELIMITER, và chúng tôi chắc chắn sẽ chuyển sang NÂNG CAO. Dấu phân tách sẽ là một ký tự đặc biệt và chúng ta sẽ tách từng lần xuất hiện của dấu phân cách. Vì vậy, ở đây, tôi nghĩ rằng họ thực sự đã tìm ra nó vì tôi đã mở rộng nó, nhưng tôi sẽ cho bạn thấy. CHÈN KÝ TỰ ĐẶC BIỆT. Tôi sẽ nói rằng đó là LINE FEED, được rồi, vì vậy, tại mỗi lần xuất hiện LINE FEED, và tôi sẽ TIÊU THỤ VÀO ROWS. Được rồi, và những gì sắp xảy ra ở đây là, 1, 2, 3, 4, 5, tôi sẽ có 5 hàng hoặc tôi sẽ nói 1001, nhưng, ở mỗi hàng, nó sẽ có một dòng từ ô này. Thật đáng kinh ngạc. Có 1, 2, 3, 4, 5, 1001. Được rồi. Bây giờ chúng ta chỉ cần phân tích cú pháp của cậu bé xấu này. Ổn thỏa,vì vậy, hãy chọn cột đó, SPLIT COLUMN BY A DELIMITER. Lần này, dấu phân cách sẽ là dấu $. Điều đó hoàn hảo, một lần, ở ký hiệu $ đầu tiên mà chúng tôi tìm thấy, đề phòng trường hợp có ký hiệu $ xuất hiện ở đó trong phần sau. Chúng ta sẽ TIÊU THỤ VÀO CỘT. Bấm OK. Ổn thỏa. Vì vậy, có những chi tiết. Đây là tiền của chúng tôi.
Bây giờ, tôi sẽ chia nó tại SPACE. Vì vậy, hãy chọn cột này, SPLIT COLUMN BY A DELIMITER, và dấu phân cách sẽ là một SPACE, vâng, khi ở LEFT-MOST DELIMITER, hãy nhấp vào OK, và tôi không cần những nhận xét đó ngoài đó nên chúng tôi ' sẽ BỎ QUA. Thực ra cũng không cần cái này vì tôi chỉ đang cố gắng kiếm tổng cộng tất cả những thứ đó, nên tôi sẽ BỎ QUA.
Bây giờ, hãy biến đổi. GROUP BY COST CENTER, TÊN CỘT MỚI sẽ được gọi là TOTAL, OPERATION sẽ là SUM, và cột nào chúng ta sẽ là SUM? CÁC CHI TIẾT 2.1. Xinh đẹp. Nhấp vào OK, được rồi, và kết quả của chúng tôi là một dòng cho mỗi TRUNG TÂM CHI PHÍ với TỔNG CỘNG của tất cả các mục hàng đó. NHÀ, ĐÓNG & TẢI. Nó có thể sẽ chèn một trang tính mới. Tôi hy vọng nó sẽ chèn một trang tính mới và nó thực hiện được, và trang tính đó được gọi là MYDATA_1. MYDATA_1.
Ổn thỏa. Bây giờ chúng ta sẽ quay lại đây vào dữ liệu ban đầu và thực hiện các bước sau. Ở lần đầu tiên, = VLOOKUP của 1001 vào kết quả của chúng tôi. Điều này giống như thiết lập một tham chiếu vòng tròn nhưng nó sẽ không cung cấp cho chúng ta một tham chiếu vòng tròn. , 2, SAI. Tôi muốn kết hợp chính xác. Được rồi, nhưng chúng tôi sẽ không muốn làm điều đó cho các ô trống. Vì vậy, tôi sẽ nói, thực ra, hãy cứ sao chép nó xuống. CONTROL + C, hãy xem tất cả những gì chúng tôi nhận được. Có lẽ chúng tôi đang nhận được N / As và tôi có thể loại bỏ nó bằng IFNA. Yeah, đẹp, được rồi. Vì vậy, chúng ta hãy loại bỏ N / As. Nếu N / A, thì chúng ta chỉ muốn "". Chúng tôi không muốn bất cứ thứ gì trong đó. CONTROL + ENTER. Ổn thỏa. Bây giờ, đó sẽ là TỔNG. Hãy xem nếu chúng ta có thể tìm thấy một câu hỏi ngắn và chỉ cần làm toán. = 627,37 + 7264,25 + 6066.01 + 4010,66 + 9773,94 và TỔNG CỘNG, 27742,23 là đó. Tuyệt vời. (= IFNA (VLOOKUP (A2, MyData_1,2, FALSE), “”))
Bây giờ, đây là thỏa thuận. Vì vậy, chúng tôi có những người ở ngoài này đang thay đổi nội dung, được rồi, và vì vậy, giả sử rằng họ hoàn thành và họ thay đổi ngân sách, 40294.48, và họ đến đây và thay đổi ngân sách này thành 6000, như vậy, và họ thêm một cái mới, ALT + ENTER, SOMETHING - $ sign, $ 1000 vừa được thêm vào. Ổn thỏa. Tất nhiên, bây giờ, khi tôi nhấn ENTER, số này, 40294.48, sẽ không cập nhật, được thôi, nhưng những gì chúng ta phải làm là chuyển đến tab DỮ LIỆU và chúng ta muốn LÀM MỚI TẤT CẢ. Vì vậy, 40294.48. Xem, xem, xem, xem. LÀM MỚI TẤT CẢ. Hoảng tuyệt vời.
Tôi thích truy vấn nguồn. Truy vấn nguồn là điều tuyệt vời nhất. Dữ liệu này, về cơ bản chỉ giống như dữ liệu từ trong một ô, giờ chúng tôi đã cập nhật nó. Bạn thậm chí có thể tạo một số loại macro nói rằng mỗi khi ai đó thay đổi điều gì đó trong COLUMN C, chúng tôi tiếp tục và nhấp vào LÀM MỚI TẤT CẢ bằng cách sử dụng macro và chỉ có những kết quả đó liên tục, liên tục làm mới.
Thật là một câu hỏi kinh khủng được gửi đến. Tôi cảm thấy tồi tệ cho Steve khi phải giải quyết vấn đề này, nhưng bây giờ, sử dụng truy vấn nguồn trong Office 365 hoặc được tải xuống cho năm 2010 hoặc 2013, bạn có một cách rất, rất dễ dàng để giải quyết vấn đề này.
Chờ đợi. Được rồi, một phụ lục: hãy làm cho nó tốt hơn nữa. Trang tính này được gọi là DATA và tôi đã lưu sổ làm việc dưới dạng hỗ trợ macro, vì vậy xlsm. Nếu bạn là xlsx, đừng bỏ qua lưu dưới dạng xlsm. ALT + F11. Tìm sổ làm việc có tên DATA, bấm đúp, trên cùng bên trái, WORKSHEET, sau đó THAY ĐỔI bất cứ khi nào chúng ta thay đổi trang tính và chúng ta sẽ nói ACTIVEWORKBOOK.REFRESHALL, rồi đóng, được rồi, và bây giờ hãy thử. Hãy chỉnh sửa một cái gì đó. Vì vậy, chúng tôi sẽ lấy những quả mâm xôi hiện là 8.000 và chúng tôi sẽ thay đổi nó thành 1000, vì vậy chúng tôi sẽ giảm 7000. Khi tôi nhấn ENTER, tôi muốn thấy rằng 42.000 giảm xuống 35.000. Ah. Tuyệt vời.
Chà, này. Đây là nơi tôi thường cầu xin bạn mua sách của tôi nhưng hôm nay, tôi sẽ đề nghị bạn mua sách của bạn tôi - Ken Puls và Miguel Escobar - M là (DỮ LIỆU) MONKEY. Tất cả những gì tôi học được về truy vấn nguồn, tôi đã học được từ cuốn sách này. Đó là một cuốn sách tuyệt vời. Kiểm tra đó ra.
Tóm tắt tập: Steve có các số để tính tổng đã được nhập vào một cột văn bản; nhiều dòng trong mỗi ô, được phân tách bằng ALT + ENTER; cần chia các dòng đó thành các hàng, sau đó phân tích cú pháp số tiền từ giữa mỗi ô; tổng hợp bởi COST CENTER; xây dựng bảng tra cứu; lấy tổng số từ bảng tra cứu, sử dụng IFNA để bỏ qua các lỗi trong hàng trống; và sau đó, phần thưởng, macro ở cuối, macro sự kiện để cập nhật trang tính khi chúng thay đổi ô.
Tôi muốn cảm ơn Steve vì đã gửi câu hỏi đó và tôi rất vui vì tôi đã có câu trả lời - trước khi truy vấn nguồn, nó sẽ thực sự rất khó khăn - và tôi muốn cảm ơn bạn đã ghé qua. Chúng tôi sẽ gặp bạn lần sau cho một netcast khác từ.
Tải tập tin
Tải xuống tệp mẫu tại đây: Podcast2160.xlsm
