Cách tìm kiếm nhiều tab trong Excel

Trong biref, tôi muốn có thể chọn từ trình đơn thả xuống nhà cung cấp trên cột A, trên cột B để có thể chọn sản phẩm của nhà cung cấp đã chọn và trên cột C để tự động điền giá sản phẩm đã chọn

Bạn có bất cứ lời khuyên về điều này?

Vui lòng cho tôi biết nếu bạn cần thêm thông tin

Cảm ơn bạn

Aly Gilchrist nói.
27 tháng 7 năm 2022 lúc 2. 07 giờ tối

Cảm ơn vì bài viết hữu ích này - Tôi đã sử dụng phương pháp có công thức dưới đây trên nhiều trang tính

=VLOOKUP[X2, INDIRECT["'"&INDEX[Lookup_Sheets, MATCH[1, --[COUNTIF[INDIRECT["'" & Lookup_Sheets & "'. R$4. R$27"], X2]>0], 0]] & "'. R$4. AJ$27"], 16, SAI]

Vấn đề tôi đang gặp phải là khi X2 [là một nhận xét/chuỗi văn bản] dài, công thức sẽ trả về lỗi "VALUE"
Khi tôi giảm các ký tự trong .
Tôi cũng đã thử thay đổi thành TRUE và cũng đã thử thêm X2&"*" nhưng không có cách nào hiệu quả.

Để biết thêm ngữ cảnh, X2 là một bảng lấy dữ liệu từ một hệ thống phần mềm, mỗi hàng được phân bổ cho một nhóm cụ thể [các trang riêng biệt] trong đó X2 được sao chép trong R4. R27. Trên mỗi bảng nhóm, một tên được điền vào cột ở bên phải của dữ liệu này. Tôi cần tên của những người đó "khớp" và điền vào bảng kéo dữ liệu gốc để tóm tắt. Không chắc lỗi nằm trong bảng dữ liệu gốc hay các trang riêng biệt mà chúng đang được sao chép trên đó

Có cách nào xung quanh điều này xin vui lòng?

Jaydeep nói.
22 tháng 2 năm 2021 lúc 2. 17 giờ chiều

Xin chào. Alexander,

Tôi gặp sự cố liên quan đến vlookup và sau đó tính tổng cho các giá trị do vlookup cung cấp. như ví dụ có dữ liệu trong ô A1, C1 và E1.
Bằng cách sử dụng vlookup, chúng tôi gọi các giá trị từ trang tính khác đến B1, D1 và F1 tương ứng với A1, C1 và E1. Bây giờ chúng tôi lấy tổng của B1, D1 và F1 cho ô G1.

Bây giờ, vấn đề là nếu có bất kỳ ô trống nào trong cột A, C hoặc E thì giá trị sẽ không xuất hiện trong ô tương ứng bằng vlookup và hàm tổng hiển thị lỗi #value trong cột G

A. b. C. Đ. e. F. g.
1. 001. 2. 002. 2. 003. 4. số 8.
2. 004. 2. 005. 3. 006. 1. 6.
3. 007. 1. 008. 3. Rỗng N/A. #VALUE

à, tôi đang làm việc trên bảng dữ liệu cũ và có 8 cột như thế này và một số dữ liệu khác]].
Tôi đã thử một số công thức nhưng không có kết quả. Có hàm nào bỏ qua ô trống và chỉ tính ô chứa giá trị từ vlookup không.

Christine nói.
22 tháng 2 năm 2021 lúc 11. 52 giờ sáng

Tôi có một sổ làm việc hiển thị danh sách nhân viên và phân tích số giờ làm việc của họ trong mỗi tuần. Một tab sẽ được thêm vào mỗi tuần; . Tôi hiện đang sử dụng công thức VLOOKUP, nhưng nó phải sửa đổi mỗi khi một tab được thêm vào. Điều này đang được thực hiện trên 15 cột, vì vậy việc cập nhật công thức hơi phức tạp. Có cách nào tốt hơn để thực hiện điều này?

Ví dụ. Tên nhân viên được liệt kê trong cột A, nhưng không được ở cùng một hàng, ở mọi trang tính; . Bố cục này giống nhau ở mỗi sheet, kể cả phần Tóm tắt

Tất nhiên, bạn có thể áp dụng phương pháp [gián tiếp] này bên trong các công thức khác

Chỉ cần ghi nhớ rằng INDIRECT là một chức năng dễ bay hơi

Điều này có nghĩa là nó tính toán độc lập với cây phụ thuộc công thức của Excel, do đó có thể ảnh hưởng đến hiệu suất của các bảng tính lớn hơn

Vì hàm INDIRECT[] hiện đã được bao gồm trong công thức, tất cả những gì còn lại phải làm là làm cho tên tab động

Thay thế “Game Div. ” với tham chiếu ô và đảm bảo giữ dấu ngoặc đơn

Tham số table_array bây giờ trở thành

INDIRECT[“’”&B6&”’!$A$4:$B$24”]

Ký hiệu & dùng để kết hợp dấu nháy đơn và nội dung của ô B6

Công thức cuối cùng trở thành

Kéo công thức xuống C8 và bây giờ nó sẽ hiển thị các giá trị số tiền được lập hóa đơn chính xác

Giải pháp 2. Cách tiếp cận INDEX-MATCH sử dụng tên bảng

Cách tiếp cận này liên quan đến việc chuyển đổi tất cả dữ liệu trong các tab Phân chia thành bảng dữ liệu Excel

Nhấp vào bất kỳ ô dữ liệu nào trong tab Bộ phận

Nhấn CTRL + T để hiển thị cửa sổ Tạo bảng

Điều này sẽ nhắc bạn chỉ định khu vực của bảng dữ liệu

Điều này chuyển đổi dữ liệu sang bảng dữ liệu Excel

Để thay đổi định dạng của bảng, hãy nhấp vào bất kỳ ô nào trong bảng và đi tới Thiết kế > Kiểu bảng để chọn lược đồ bạn thích hoặc Xóa để hoàn nguyên về ban đầu

Chỉ định Tên bảng trong tab Thiết kế

Lưu ý rằng không được phép sử dụng khoảng trắng trong Tên bảng, vì vậy bạn có thể muốn thay thế khoảng trắng bằng dấu gạch dưới

Làm tương tự cho hai Division còn lại để bạn có các tên bảng này

Quay lại tab Tóm tắt và xây dựng công thức bằng cách sử dụng phương pháp INDEX-MATCH. [Đây là liên kết đến hướng dẫn sử dụng hàm INDEX[] và MATCH[]. ]

Cú pháp của hàm INDEX[] là

= INDEX[array, row_num,[column_num]]

• mảng. Đây là khu vực có câu trả lời
• row_num. Phải đi xuống bao nhiêu hàng mới tìm được câu trả lời
• cột_num. Cần phải đi bao nhiêu cột bên phải để tìm câu trả lời

Như trước đây, trước tiên hãy bắt đầu đơn giản với công thức cốt lõi

Chúng tôi sẽ cho rằng tab duy nhất của chúng tôi là Game Div

Khi công thức hoạt động cho Game Div. , chúng ta có thể mở rộng nó để tra cứu các giá trị trên các tab khác nhau

Bắt đầu công thức bằng cách nhập

Cell C6 =VLOOKUP[$B$4,’Game Div.’!$A$4:$B$24,2,FALSE]

Chuyển đến Div trò chơi. tab và chọn cột Số tiền đã lập hóa đơn

Thay vì hiển thị tham chiếu ô, tham chiếu này sẽ hiển thị dưới dạng Game_Div. [Số lượng danh sách đơn hàng]

Tham số thứ hai của hàm INDEX[] là row_num

Về cơ bản, đó là số hàng cần di chuyển xuống để tìm câu trả lời

Thay vì mã hóa cứng điều này, hàm MATCH[] được sử dụng để tìm hàng tương ứng với Ngày đã chọn trong tab Tóm tắt và trả lại vị trí  cho hàm INDEX[]

Cú pháp của hàm MATCH[] là

Cell C6 =VLOOKUP[$B$4,’Game Div.’!$A$4:$B$24,2,FALSE]

Công thức Match trở thành

Cell C6 =VLOOKUP[$B$4,’Game Div.’!$A$4:$B$24,2,FALSE]

Công thức cuối cùng trở thành

Cell C6 =VLOOKUP[$B$4,’Game Div.’!$A$4:$B$24,2,FALSE]

Kéo công thức này xuống Div Tiện ích. row sẽ trả về các giá trị giống nhau vì chúng được mã hóa cứng để xem bên trong Game Div. chuyển hướng

Để khắc phục điều này, chúng tôi sẽ sử dụng hàm INDIRECT[] để giúp lấy tên tab động

Tuy nhiên, quy ước đặt tên trong các ô B6. B8 khác với tên bảng vì cái sau sử dụng dấu gạch dưới thay vì khoảng trắng

Chúng ta sẽ cần một công thức lấy các ô B6. B8 và chuyển đổi chúng trông giống hệt tên bảng tương ứng—công thức đó cần thay thế khoảng trắng bằng dấu gạch dưới

Hàm SUBSTITUTE[] sẽ giúp chúng ta thực hiện việc thay thế này

Bước đầu tiên, hãy giới thiệu Hàm INDIRECT

1. Thêm chức năng INDIRECT[] bằng cách thay thế Game_Div. [Số tiền đã lập hóa đơn] với INDIRECT[“Game_Div. [Số lượng danh sách đơn hàng]"]

2. Thay thế Game_Div. với tham chiếu ô B6 và kết hợp nó với tiêu đề bảng [Số tiền đã lập hóa đơn] bằng ký hiệu &

3. Để thay thế khoảng trắng bằng dấu gạch dưới, hãy sử dụng hàm SUBSTITUTE[]

Cú pháp là SUBSTITUTE[text,old_text,new_text,[instance_num]]

• chữ. Ô bạn muốn thay thế diễn ra
• old_text. Bạn muốn thay thế ký tự cụ thể nào. Trong trường hợp này, nó là " "
• văn bản mới. Cái gì để thay thế old_text bằng. Trong trường hợp này, nó là “_”
• Instance_num. Bao nhiêu lần chúng tôi muốn thay thế diễn ra. Đây là một đối số tùy chọn. Chúng tôi có thể bỏ qua nó, điều đó có nghĩa là chúng tôi muốn tất cả các phiên bản của “ “ được thay thế bằng “_”

=SUBSTITUTE[B6,” ”,”_”]&”[Số tiền đã lập hóa đơn]”…

4. Sự thay thế tương tự nên được áp dụng cho tham chiếu của phần MATCH[]. Công thức cuối cùng bây giờ trở thành

Ô D6 = INDEX[INDIRECT[SUBSTITUTE[B6,” ”,”_”]&“[Số tiền đã lập hóa đơn]”], MATCH[Tóm tắt. $B$4, GIÁN TIẾP[SUBSTITUTE[B6,“ ”, “_”&“[Ngày]”],0]]