Cách viết công thức Excel sẽ tra cứu giá trị trên một trang tính khác dựa trên sản phẩm được chọn. Cách lấy dữ liệu từ một trang tính khác nhau cho từng sản phẩm.
Xem video
- Rhonda từ Cincinnati: Làm thế nào để tra cứu cả hàng và trang tính?
- Sử dụng cột Ngày để tìm ra trang tính nào sẽ sử dụng
- Bước 1: Xây dựng một VLOOKUP thông thường và sử dụng FORMULATEXT để xem tham chiếu sẽ như thế nào
- Bước 2: Sử dụng Concatenation và hàm TEXT để tạo một tham chiếu giống như tham chiếu mảng bảng trong công thức
- Bước 3: Tạo VLOOKUP của bạn, nhưng đối với mảng bảng, hãy sử dụng INDIRECT (kết quả từ bước 2)
- Bước 4: Sao chép công thức từ Bước 2 (không có dấu bằng) và dán vào công thức từ bước 3
Bản ghi video
Học Excel từ Podcast, Tập 2173: Tra cứu Trang tính và Hàng.
Chào mừng bạn trở lại netcast, tôi là Bill Jelen. Tôi đã ở Cincinnati vào tuần trước, và Rhonda ở Cincinnati có câu hỏi tuyệt vời này. Rhonda cần tra cứu sản phẩm này nhưng bảng Tra cứu khác nhau, tùy thuộc vào tháng đó. Hãy xem, chúng tôi có các bảng Tra cứu khác nhau ở đây cho tháng 1 đến tháng 4 và có lẽ là cho các tháng khác. Ổn thỏa.
Vì vậy, tôi sẽ sử dụng INDIRECT để giải quyết vấn đề này, nhưng trước khi thực hiện INDIRECT, tôi luôn thấy dễ dàng hơn chỉ cần thực hiện một hàm VLOOKUP thẳng. Vì vậy, chúng ta có thể thấy biểu mẫu sẽ trông như thế nào. Vì vậy, chúng tôi đang tìm kiếm A1 trong bảng này vào tháng 1 và chúng tôi muốn cột thứ bảy, dấu phẩy FALSE, tất cả các VLOOKUP kết thúc bằng FALSE. (= VLOOKUP (A2, 'Tháng 1 năm 2018'! A1: G13,7, FALSE)). Ổn thỏa.
Và, đó là câu trả lời đúng. Điều tôi thực sự quan tâm là nhận được văn bản công thức của điều đó. Vì vậy, nó cho tôi thấy công thức sẽ trông như thế nào. Và toàn bộ mẹo ở đây là, tôi đang cố gắng tạo một cột Người trợ giúp trông giống hệt như Tham chiếu này, phải không? Vì vậy, phần này-- chỉ phần đó ngay tại đó. Ổn thỏa. Vì vậy, cột Người trợ giúp này phải trông giống như thứ đó. Và điều đầu tiên tôi muốn làm, là chúng ta sẽ sử dụng hàm TEXT của Ngày-- hàm TEXT của ngày-- để lấy mmm, khoảng trắng, yyyy-- vậy, "mmm yyyy" như thế- - sẽ trả về, đối với mỗi ô, chúng ta đang tra cứu tháng nào. Bây giờ, tôi cần gói nó trong dấu nháy đơn. Nếu không có tên khoảng trắng trong đó, tôi sẽ không cần dấu nháy đơn, nhưng tôi thì có. Vì vậy, chúng ta sẽ Concactenate trước,dấu nháy đơn, vậy đó là dấu ngoặc kép - dấu nháy đơn, dấu ngoặc kép - dấu và và sau đó ở đây, một dấu ngoặc kép khác, dấu nháy đơn và dấu chấm than, A1: G13, dấu ngoặc kép, dấu và ở đó.
Ổn thỏa. Vì vậy, bây giờ, những gì chúng tôi đã thực hiện thành công ở đây trong cột Trình trợ giúp, là chúng tôi đã xây dựng một cái gì đó trông giống hệt như mảng bảng trong VLOOKUP. Ổn thỏa. Vì vậy, câu trả lời của chúng tôi, sau đó, nó sẽ là = VLOOKUP của ô này, A2, dấu phẩy, và sau đó khi chúng tôi đến mảng bảng, chúng tôi sẽ sử dụng INDIRECT. INDIRECT là một hàm thú vị này nói rằng, "Này, đây là một ô trông giống như một tham chiếu ô và tôi muốn bạn chuyển đến F2, lấy thứ giống như một tham chiếu ô, sau đó sử dụng bất kỳ thứ gì có trong tham chiếu ô đó như câu trả lời, "dấu phẩy, 7, dấu phẩy, FALSE," như vậy. (= VLOOKUP (A2, 'Jan 2018'! A1: G13,7, FALSE)) Được rồi, bây giờ, chúng tôi đang chọn một bảng Tra cứu khác nhau và trả về các giá trị tùy thuộc vào việc đó là tháng 4 hay tháng gì.
Ổn thỏa. Vì vậy, hãy lấy ngày 24 tháng 4 này, tôi sẽ thay đổi nó thành 17 tháng 2 năm 2018, như vậy, và chúng ta sẽ thấy rằng 403 thay đổi thành 203-- hoàn hảo. Nó đang làm việc. Ổn thỏa. Tất nhiên, chúng ta không cần hai cột này ở đây và thực sự, nếu bạn nghĩ về nó, chúng ta không cần toàn bộ cột này. Chúng tôi có thể lấy toàn bộ thứ đó, ngoại trừ dấu bằng, Ctrl + C để sao chép nó, và sau đó ở nơi chúng tôi có D2, chỉ cần dán, như vậy. Hoàn hảo. Nhấp đúp để bắn nó xuống và loại bỏ nó. Có câu trả lời của chúng tôi. Được rồi, chúng tôi sẽ sử dụng văn bản công thức của chúng tôi ở đây, chỉ để xem câu trả lời cuối cùng.
Tôi phải nói với bạn, nếu tôi phải xây dựng công thức đó từ đầu, tôi sẽ không làm điều đó. Tôi sẽ không thể làm được. Tôi chắc chắn sẽ làm hỏng nó. Đó là lý do tại sao tôi luôn xây dựng nó theo từng bước - tôi tìm ra công thức sẽ trông như thế nào và sau đó kết hợp cột Trình trợ giúp sẽ được sử dụng bên trong INDIRECT, và cuối cùng, có thể ở đây ở phần cuối, đặt mọi thứ lại với nhau.
Này, nhiều mẹo như mẹo này trong cuốn sách Power Excel với. Đây là phiên bản 2017 với 617 bí ẩn Excel được giải quyết. Nhấp vào "Tôi" ở góc trên cùng bên phải để biết thêm thông tin.
Được rồi, tóm tắt từ Tập này: Rhonda từ Cincinnati-- cách tra cứu cả hàng và trang tính. Tôi sử dụng cột Ngày để tìm ra trang tính nào sẽ sử dụng; vì vậy tôi xây dựng một VLOOKUP thông thường và sử dụng văn bản công thức để xem tham chiếu sẽ như thế nào; và sau đó xây dựng một cái gì đó giống như tham chiếu đó bằng cách sử dụng hàm văn bản để chuyển đổi Ngày thành Tháng và Năm; sử dụng Concactenation để xây dựng một cái gì đó giống như tham chiếu; và sau đó khi bạn xây dựng hàm VLOOKUP cho đối số thứ hai, mảng bảng, hãy sử dụng INDIRECT; và sau đó chỉ đến kết quả từ bước 2; và sau đó đến bước thứ tư tùy chọn ở đó, sao chép công thức từ bước 2, không có dấu bằng và dán nó vào công thức từ Bước 3, vì vậy bạn sẽ có một công thức duy nhất.
Tôi muốn cảm ơn Rhonda vì đã đến tham dự hội thảo của tôi ở Cincinnati, và 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 tập tin
Tải xuống tệp mẫu tại đây: Podcast2173.xlsm
