Câu hỏi hôm nay từ Quentin, người đã có mặt tại hội thảo Atlanta Power Excel của tôi. Quentin phải tạo ra 7 câu hỏi khảo sát giống nhau cho mỗi trong số hơn 1000 khách hàng trong Excel.
Như bạn có thể thấy trong hình này, khách hàng ở A. Các câu hỏi được lặp lại nằm trong cột D.
Bạn có thể giải quyết vấn đề này bằng VBA hoặc công thức, nhưng đây là tuần lễ Power Query lúc, vì vậy tôi sẽ sử dụng một thủ thuật thú vị trong Power Query.
Nếu bạn muốn có một hàng trống giữa mỗi khảo sát, hãy thêm số thứ tự và thêm số 7 vào sau câu hỏi cuối cùng.
Nhấn Ctrl + T từ cả hai tập dữ liệu. Đặt tên cho tập dữ liệu thứ hai bằng một cái tên mà bạn có thể nhớ được, chẳng hạn như Câu hỏi hoặc Khảo sát.
Từ tập dữ liệu thứ hai, sử dụng Dữ liệu, Từ Bảng.
Trình chỉnh sửa Power Query sẽ mở ra. Từ tab Trang đầu, chọn trình đơn thả xuống Đóng & Tải và chọn Đóng & Tải Đến…. Trong hộp thoại tiếp theo, chọn Chỉ Tạo kết nối.
Bây giờ bạn đã trở lại Excel. Chọn bất kỳ ô nào trong bảng khách hàng ở cột A. Dữ liệu, Từ Bảng. Khi Trình soạn thảo truy vấn mở ra, hãy nhấp vào tab Thêm cột trong ruy-băng và sau đó chọn Cột tùy chỉnh. Công thức là =#"Questions"(bao gồm dấu # và dấu ngoặc kép).
Một cột mới xuất hiện trong trình chỉnh sửa với Bảng giá trị được lặp lại trong mỗi hàng. Nhấp vào biểu tượng Mở rộng trong tiêu đề cột.
Chọn cả hai trường trong Bảng. Từ tab Trang đầu, chọn Đóng & Tải.
Một bảng tính mới sẽ xuất hiện với 7 câu hỏi được lặp lại cho mỗi trong số hơn 1000 khách hàng.
Xem video
Bản ghi video
Học Excel Từ Podcast Tập 2205: Vụ nổ Khảo sát Khoản vay.
Chào mừng bạn trở lại netcast, tôi là Bill Jelen. Bây giờ, mới hôm qua trong tập 2204, Kaylee đến từ Nashville phải thực hiện một vụ nổ VLOOKUP - đối với mỗi mục ở đây trong cột D, chúng ta có một loạt các mục phù hợp ở cột G và cần phải phát nổ chúng. Vì vậy, nếu Cung C có 8 mặt hàng, chúng ta sẽ có 8 hàng.
Bây giờ, hôm nay, chúng ta có Quentin. Bây giờ, Quentin đang tham gia hội thảo của tôi ở Atlanta, nhưng anh ấy thực sự đến từ Florida, và Quentin có gần 1000 khách hàng ở đây-- tốt, hơn 1000 khách hàng - trong cột A và đối với mọi khách hàng, anh ấy cần tạo bản khảo sát này- - khảo sát này gồm 1, 2, 3, 4, 5, 6 câu hỏi. Và những gì tôi sẽ làm ở đây là tôi sẽ thêm một số Sequence chỉ với các số từ 1 đến 7, vì vậy, bằng cách đó, tôi có thể tạo một hàng trống đẹp mắt ở giữa. Tôi sẽ tạo cả hai tập dữ liệu này thành một bảng; vì vậy, chúng tôi đang cố gắng làm cho 7 hàng này bùng nổ cho mỗi một trong số 1000 khách hàng này. Đó là mục tiêu.
Bây giờ, tôi có thể làm điều này với VPA; Tôi có thể làm điều này với các công thức; nhưng đây là loại "Tuần truy vấn nguồn" ở đây, chúng tôi đang thực hiện đây là ví dụ về Truy vấn nguồn thứ ba của chúng tôi liên tiếp, vì vậy tôi sẽ sử dụng Power Query. Tôi sẽ làm cho cái bên trái này thành một cái bàn. Tôi sẽ rất cẩn thận để đặt tên cho bảng này không phải là Bảng 1. Tôi sẽ đặt tên cho nó. Chúng tôi sẽ phải sử dụng lại tên đó sau này, vì vậy tôi sẽ gọi nó là Câu hỏi-- như thế. Và đây sẽ là Bảng 2, nhưng tôi sẽ đổi tên đó thành Khách hàng - không quá quan trọng nên tôi đổi tên bảng này vì đó là bảng thứ hai phải có tên. Vì vậy, chúng tôi sẽ chọn điều này; Dữ liệu; và chúng ta sẽ nói Từ Bảng / Phạm vi. Nhận và chuyển đổi dữ liệu-- đây được gọi là Power Query. Nó được tích hợp vào Excel 2016. Nếu bạn có 2010 hoặc 2013, trên Windows,không phải Mac, không phải iOS, không phải Android, bạn có thể tải xuống Power Query miễn phí từ Microsoft.
Vì vậy, chúng ta sẽ lấy dữ liệu từ Bảng / Phạm vi; đây là bảng của chúng tôi-- chúng tôi sẽ không làm bất cứ điều gì với nó, chỉ cần Đóng và Tải; Đóng & Tải vào; chỉ Tạo kết nối; được rồi, và hãy xem, tên của Truy vấn đó là Câu hỏi. Nó sử dụng cùng một tên như ở đây. Và sau đó chúng ta quay lại phần này, và, Dữ liệu; Từ Bảng / Phạm vi; vì vậy, có một danh sách 1000 khách hàng trở lên của chúng tôi.
Xin chào, đây là lời cảm ơn đến Miguel Escobar, bạn của tôi, đồng tác giả của M Is For (DỮ LIỆU) MONKEY). Tôi sẽ đặt một liên kết đến điều đó trong video-- cuốn sách hay về Power Query-- đã giúp tôi điều này. Chúng tôi sẽ đưa vào một Cột tùy chỉnh hoàn toàn mới và công thức Cột tùy chỉnh ở ngay đây: = # "tên của truy vấn". Tôi sẽ không bao giờ hiểu được điều đó nếu không có Miguel, vì vậy cảm ơn Miguel vì điều đó.
Và khi tôi nhấp vào OK, vâng, có vẻ như nó không hoạt động - chúng tôi chỉ nhận được bàn, bàn, bàn, nhưng đó chính xác là những gì chúng tôi đã có ngày hôm qua với Kaylee và việc bán vé. Và tất cả những gì tôi phải làm là mở rộng điều này, và tôi thực sự sẽ nói rằng tôi có thể không cần Trình tự … tốt, hãy đặt nó trong trường hợp. Chúng tôi có thể lấy nó ra sau khi chúng tôi nhìn thấy nó. Ngay bây giờ, chúng ta có 1000 hàng, và bây giờ chúng ta có 7000 hàng-- đẹp. Bây giờ tôi có thể thấy rằng nó xuất hiện trong Trình tự, vì vậy tôi không cần điều đó. Tôi sẽ nhấp chuột phải và Chỉ xóa một cột đó. Và sau đó tôi có thể Về nhà; Đóng & Tải; và BAM! - bây giờ chúng tôi sẽ có hơn 7000 hàng với 6 câu hỏi và một khoảng trống cho mọi khách hàng. Quentin đã rất phấn khích với điều đó trong buổi hội thảo. Mẹo hay và thú vị-- tránh VBA, tránh cả đống công thức bằng cách sử dụng Chỉ mục,và những thứ tương tự-- một cách tuyệt vời để đi.
Nhưng, này, hôm nay, hãy để tôi gửi đến bạn với M Is For (DỮ LIỆU) MONKEY. Ken Puls và Miguel Escobar đã viết cuốn sách hay nhất về Power Query. Tôi yêu cuốn sách đó; trong 2 giờ, bạn sẽ trở thành chuyên gia với cuốn sách đó.
Được rồi, vậy hãy kết thúc ngay hôm nay-- Quentin cần tạo một cuộc khảo sát giống hệt nhau cho 1000 khách hàng khác nhau. Có 6 hoặc 7 hoặc 8 câu hỏi cho mỗi khách hàng. Bây giờ, chúng ta có thể làm điều này với VBA hoặc macro, nhưng, vì chúng ta đang chạy Power Query ở đây, hãy thực hiện Power Query. Tôi đã thêm một câu hỏi trống thừa vào Câu hỏi; Tôi đã thêm Số thứ tự, để đảm bảo rằng ô trống vẫn ở đó; làm cho khách hàng vào một bàn; làm cho các câu hỏi thành một bảng; điều thực sự quan trọng là bạn đặt tên cho Câu hỏi mà bạn có thể nhớ-- Tôi đã gọi là "Câu hỏi". Thêm câu hỏi vào Power Query, Chỉ như một kết nối; và sau đó, khi bạn thêm khách hàng vào Power Query, hãy tạo một cột tùy chỉnh mới có công thức là: # "tên của truy vấn đầu tiên", sau đó Mở rộng cột đó trong trình soạn thảo Power Query; Đóng &Tải lại bảng tính và bạn đã hoàn tất. Một thủ thuật tuyệt vời-- Tôi thích Power Query-- điều tuyệt vời nhất sẽ xảy ra với Excel trong 20 năm nữa.
Tôi muốn cảm ơn Quentin đã thể hiện trong buổi hội thảo của tôi. Anh ấy đã đến hội thảo của tôi một vài lần trước đây-- anh chàng tuyệt vời. 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 xuống tệp Excel
Để tải xuống tệp excel: loan-Survey-boom.xlsx
Power Query tiếp tục làm tôi ngạc nhiên. Hãy xem cuốn sách M is for Data Monkey để tìm hiểu thêm về Power Query.
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:
"Bạn có thể làm bất cứ điều gì với AGGREGATE ngoại trừ việc hiểu nó."
Liam Bastick
