Kaley từ Nashville đang làm việc trên một bảng tính bán vé. Đối với mỗi sự kiện, cô chọn một kế hoạch bán vé. Kế hoạch bán vé đó có thể chỉ ra bất kỳ đâu từ 4 đến 16 loại vé cho sự kiện. Kaley muốn một công thức sẽ chuyển đến bảng tra cứu và trả về các kết quả phù hợp * tất cả *, chèn các hàng mới nếu thích hợp.
Mặc dù tôi không có hàm VLOOKUP có thể giải quyết vấn đề này, nhưng các công cụ Power Query mới được tích hợp trong Excel 2016 có thể giải quyết vấn đề này.
Ghi chú
Nếu bạn có phiên bản Windows của Excel 2010 hoặc Excel 2013, bạn có thể tải xuống Power Query miễn phí từ Microsoft. Rất tiếc, Power Query vẫn chưa khả dụng cho Excel cho Android, Excel cho ios hoặc Excel cho Mac.
Để minh họa mục tiêu: Mike McCann và Thợ máy đang xuất hiện ở Nhà hát Allen với vé loại C. Vì có bốn hàng phù hợp trong bảng tra cứu, Kaley muốn bốn hàng có nội dung Mike McCann và Thợ máy, mỗi hàng có một kết quả phù hợp với bảng tra cứu.
Chọn một ô trong bảng gốc. Nhấn Ctrl + T để đánh dấu dữ liệu đó dưới dạng bảng. Trên tab Công cụ bảng, đổi tên bảng từ Table1 thành Shows. Lặp lại cho bảng tra cứu, gọi nó là Vé.
Chọn một ô trong bảng Hiển thị. Từ tab Dữ liệu, chọn Từ Bảng / Phạm vi.
Sau khi trình chỉnh sửa Power Query mở ra, hãy mở menu thả xuống Đóng & Tải và chọn Đóng và Tải vào….
Trong hộp thoại Nhập dữ liệu, chọn Chỉ tạo kết nối.
Chuyển đến bảng Vé. Lặp lại các bước để Chỉ tạo kết nối với vé. Bạn sẽ thấy cả hai kết nối trong ngăn Truy vấn:
Chọn bất kỳ ô trống nào. Chọn dữ liệu, lấy dữ liệu, kết hợp truy vấn, hợp nhất.
Có sáu bước trong hộp thoại Hợp nhất. Thứ 3 và thứ 4 có vẻ không trực quan với tôi.
- Chọn Chương trình từ trình đơn thả xuống trên cùng
- Chọn Vé từ menu thả xuống thứ hai.
- Nhấp vào tiêu đề Gói vé ở trên cùng để chọn cột đó làm khóa ngoại trong bảng Chương trình.
- Nhấp vào tiêu đề Gói vé ở dưới cùng để chọn cột đó làm trường chính trong bảng tra cứu.
- Mở loại Tham gia và chọn Bên trong (chỉ các hàng phù hợp).
- Bấm OK
Kết quả ban đầu thật đáng thất vọng. Bạn thấy tất cả các trường từ bảng 1 và một cột cho biết Bảng, Bảng, Bảng.
Nhấp vào biểu tượng Mở rộng ở đầu cột Vé.
Bỏ chọn Gói vé vì bạn đã có trường đó. Trường còn lại sẽ được gọi là Tickets.Ticket Type trừ khi bạn bỏ chọn Use Original Name as Prefix.
Sự thành công! Mỗi hàng cho mỗi chương trình bùng nổ thành nhiều hàng.
Tôi đặc biệt không hài lòng với việc sắp xếp dữ liệu. Việc sắp xếp theo ngày khiến các Loại vé sắp xếp theo một cách khác lạ.
Xem video
Trong trường hợp hôm nay, video được quay sau khi bài báo được viết. Tôi đề nghị thêm cột trình tự vào Loại vé để kiểm soát thứ tự sắp xếp.
Bản ghi video
Học Excel từ Podcast, Tập 2204: Trả lại tất cả các VLOOKUP.
Chào mừng bạn trở lại netcast, tôi là Bill Jelen. Câu hỏi hôm nay từ Nashville Music City. Tôi ở dưới đó ở Nashville, ai đó chịu trách nhiệm lên lịch tải vé vào hệ thống bán vé và vì vậy đây là những gì chúng tôi có: Chúng tôi có danh sách các sự kiện - các sự kiện sắp tới - chúng tôi có ngày, địa điểm và kế hoạch bán vé. Vì vậy, giống như, mặc dù một cái gì đó được tổ chức tại Cung điện, có thể có các gói vé khác nhau - như, có thể sàn được định hình, bạn biết đấy, có chỗ ngồi hoặc có thể chỉ là phòng đứng, phải không?
Vì vậy, tùy thuộc vào loại gói vé nào, bạn phải truy cập vào bảng Tra cứu ở đây và tìm tất cả các sự kiện phù hợp, và về cơ bản chúng ta sẽ thực hiện điều mà tôi gọi là bùng nổ VLOOKUP. Vì vậy, nếu có thứ gì đó ở Hannah C, chúng sẽ chuyển xuống Hannah C và nếu có-- 1, 2, 3, 4, 5, 6-- 7 mặt hàng ở Hannah C, chúng ta sẽ có để trả về bảy hàng - có nghĩa là bạn sẽ phải chèn thêm sáu hàng và sao chép dữ liệu đó xuống. Ổn thỏa.
Bây giờ, chúng tôi sẽ không làm điều này với một VLOOKUP, nhưng bạn có khái niệm - chúng tôi đang thực hiện một hàm VLOOKUP và chúng tôi đang trả lại tất cả các câu trả lời dưới dạng các hàng mới. Được rồi, tôi sẽ lấy cả hai bảng này và biến chúng thành một bảng thực bằng Ctrl + T. Cái đầu tiên được gọi là Bảng 1-- cái tên kinh khủng, hãy gọi đây là Sự kiện hoặc Chương trình, hãy gọi nó là Chương trình, như vậy-- và cái thứ hai, bây giờ, này, đây là những gì tôi đã học được bởi vì tôi đã thực hành điều này - chúng ta phải có một trường trình tự ở đây. Vì vậy, = ROW (A1), nhấp đúp và sao chép nó xuống, sau đó sao chép và dán các giá trị đặc biệt. Ổn thỏa. Bây giờ chúng ta làm điều đó sẽ tạo thành một bảng-- Ctrl + T, và chúng ta sẽ gọi đó là Vé.
Ổn thỏa. Vì vậy, chúng tôi có chương trình, chúng tôi có vé. Tôi sẽ đi đến tab Dữ liệu và tôi đang ở đây trong chương trình, tôi muốn nói rằng tôi muốn lấy dữ liệu của mình từ Bảng hoặc Phạm vi - nhân tiện đây là Power Query. Nếu bạn quay lại Excel 2010 hoặc 2013, bạn có thể tải xuống miễn phí phần mềm này từ Microsoft, tải xuống công cụ Power Query. Rất tiếc, nếu bạn đang sử dụng Mac hoặc iOS hoặc Android, không có Power Query nào dành cho bạn. Được rồi, từ Table hoặc Range… hãy tìm một người có-- tìm một người bạn có-- PC Windows và nhờ họ thiết lập điều này. Ổn thỏa. Đây là một bảng, chúng ta sẽ không làm gì với điều này, chỉ cần Đóng & Tải, Đóng & Tải và sau đó nói "Chỉ Tạo Kết nối", hoàn hảo. Chúng ta sẽ đến đây với bảng thứ hai: Lấy Dữ liệu, Từ một Bảng hoặc Phạm vi, chúng ta không làm gì với bảng này, Đóng & Tải,Đóng và tải vào, "Chỉ tạo kết nối", OK. Vì vậy, những gì chúng ta có bây giờ, là chúng ta có một kết nối với bảng đầu tiên và một kết nối với bảng thứ hai. Chúng tôi sẽ không hợp nhất hai thứ này, về cơ bản giống như thực hiện hàm VLOOKUP, hoặc một phần Cơ sở dữ liệu, tôi đoán, thực sự là như thế. Kết hợp Truy vấn, chúng ta sẽ Hợp nhất. Ổn thỏa.
Bây giờ, bảy điều bạn phải làm trong hộp thoại này - và nó hơi khó hiểu - chúng ta sẽ chọn Shows làm bảng đầu tiên; chọn Vé là bảng thứ hai; chọn trường mà chúng có điểm chung và đây có thể là nhiều trường - bạn có thể kiểm soát khi nhấp - nhưng trong trường hợp này chỉ có một gói vé; và sau đó là Gói vé; và sau đó, chúng tôi sẽ thay đổi loại Tham gia thành liên kết Bên trong với "chỉ các hàng phù hợp". Ổn thỏa. Bây giờ, bạn nhấp vào OK và bạn nghĩ rằng toàn bộ vấn đề của bạn sẽ được giải quyết, nhưng bạn chỉ bị nghiền nát bởi vì đây là tất cả dữ liệu từ A-- họ không chèn bất kỳ hàng mới nào - và ở đây, chỉ một lĩnh vực ngu ngốc nhàm chán được gọi là Vé chỉ có Bảng, Bảng, Bảng, hah.
Nhưng, may mắn thay, ở trên cùng là biểu tượng Mở rộng và chúng tôi sẽ mở rộng biểu tượng đó-- Tôi không cần phải lập kế hoạch, tôi đã có sẵn điều đó - Loại vé và Trình tự. Tôi không muốn nó được gọi là Tickets.TicketType, đó là những gì Power Query muốn làm-- vì vậy tôi bỏ chọn hộp này. Ổn thỏa. Ngay bây giờ chúng ta có 17 hàng dữ liệu; khi tôi bấm OK, BAM! Có vụ nổ. Vì vậy, Michael Seeley và Starlighter xuất hiện với tất cả các loại vé khác nhau, như thế này. Được rồi, và thấy các loại vé này xuất hiện theo thứ tự, thật tuyệt. Nhưng Michael Seeley không phải là chương trình tiếp theo, chương trình tiếp theo là vào ngày 5 tháng 6. Vì vậy, khi tôi thử và sắp xếp điều này theo Ngày-- điều này khiến tôi phát điên, tôi không thể giải thích điều này. Sắp xếp theo ngày, và Mike Man and the Mechanics lên đến 65, nhưng sau đó tất cả các vé đều đã hết sạch. Họ 'đang sai trình tự, và đó là lý do tại sao tôi phải làm theo trình tự này-- cảm thấy như vậy. Tôi có thể sắp xếp theo Trình tự. Vì vậy, bây giờ, 6, 5, đẹp, và trong đó, Vé là chính xác. Và thực sự, tại thời điểm này, chúng ta không cần cột này nữa. Vì vậy, tôi có thể nhấp chuột phải và loại bỏ, sau đó Đóng & Tải-- lần này tôi thực sự sẽ Đóng & Tải, không phải Đóng & Tải để-- và chúng ta có kết quả. Ổn thỏa.
Vì vậy, chúng tôi đã đi từ một danh sách các sự kiện đến toàn bộ danh sách lớn này, nhưng đây là phần tuyệt vời: Tôi đã nhầm lẫn điều này, Mike Man và Mechanics không phải là Cung B, Cung C. Vì vậy, tôi quay lại bản gốc ở trên cùng bên phải -góc tay để biết thêm thông tin về cuốn sách.
Ổn thỏa. Các chủ đề trong tập này: Kaley ở Nashville cần thực hiện một hàm VLOOKUP để trả về tất cả các kết quả phù hợp, thường là chèn các hàng mới. Và đó là một cơ sở dữ liệu bán vé, được chứ? Vì vậy, tôi sẽ gọi đây là Vụ nổ VLOOKUP vì mỗi chương trình sẽ phát nổ thành tối đa 16 hàng. Chúng tôi sẽ sử dụng Power Query để giải quyết vấn đề này và tôi đã biết rằng Ngày sẽ xuất hiện trên Trình tự sai trừ khi chúng tôi thêm trường Trình tự vào loại vé. Tạo cả hai tập hợp thành một Bảng bằng Ctrl + T; đặt tên chúng thành Chương trình và Vé; và sau đó từ mỗi bảng, Lấy dữ liệu, Từ bảng, Đóng & Tải, để Chỉ tạo kết nối; lặp lại cho bảng khác; sau đó Dữ liệu, Lấy dữ liệu, Kết hợp Truy vấn, Hợp nhất; và sau đó là hộp thoại đó, tôi khá khó hiểu-- chọn Sự kiện, chọn Vé, nhấp vào Loại vé ở cả hai, thay đổi liên kết thành liên kết bên trong,bấm OK, và sau đó bạn nhận được kết quả đáng thất vọng khủng khiếp đó chỉ là một cột có ghi Bảng, Bảng, Bảng, Bảng; nhấp vào biểu tượng Mở rộng ở đầu trang đó; chọn trường Trình tự vé; không đặt tiền tố với tên của bảng; và bạn có thể Sắp xếp theo Ngày, Sắp xếp theo Trình tự; Đóng và tải vào bảng tính. Điều tuyệt vời là nếu dữ liệu cơ bản thay đổi - chỉ cần Làm mới và bạn sẽ có kết quả.
Bây giờ, này, để tải xuống sổ làm việc được sử dụng từ video hôm nay, hãy truy cập URL dưới đó trong mô tả YouTube. Ngoài ra còn có danh sách các hội thảo sắp tới-- Tôi rất muốn gặp bạn tại một trong các hội thảo Power Excel trực tiếp của tôi.
Tôi muốn cảm ơn Kaley vì đã xuất hiện ở Nashville và cho tôi câu hỏi tuyệt vời đó. Tôi muố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: return-all-vlookups.xlsx
Power Query tiếp tục làm tôi ngạc nhiên. Đây là phần thứ hai trong chuỗi ba ngày với câu trả lời là Power Query:
- Thứ ba: Chuyển đổi cột Ngày / Giờ thành chỉ ngày
- Hôm nay: Trả lại tất cả các VLOOKUP
- Thứ năm: Tạo một cuộc khảo sát cho mỗi 1100 mục
Tôi có toàn bộ Danh sách phát trên YouTube về những thứ mà tôi đã giải quyết bằng 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:
"Khi nghi ngờ, hãy sử dụng Hàm ROUND!"
Mike Girvin
