Nội dung
- Tìm kiếm trong Excel theo nhiều tiêu chí
- Chúng tôi trích xuất các giá trị thứ 2, thứ 3, v.v. bằng hàm VLOOKUP
- Lấy tất cả các lần lặp lại giá trị mong muốn
- XNUMXD tìm kiếm theo hàng và cột đã biết
- Sử dụng nhiều hàm VLOOKUP trong một công thức
- Thay thế động dữ liệu từ các bảng khác nhau bằng hàm VLOOKUP và INDIRECT
Trong phần thứ hai của hướng dẫn của chúng tôi về hàm VPR (VLOOKUP) trong Excel, chúng tôi sẽ phân tích một vài ví dụ sẽ giúp bạn định hướng tất cả sức mạnh VPR để giải quyết các nhiệm vụ Excel tham vọng nhất. Các ví dụ giả định rằng bạn đã có kiến thức cơ bản về cách hoạt động của tính năng này. Nếu không, bạn có thể quan tâm đến việc bắt đầu với phần đầu tiên của hướng dẫn này, phần này giải thích cú pháp và cách sử dụng cơ bản. VPR. Vâng, chúng ta hãy bắt đầu.
Tìm kiếm trong Excel theo nhiều tiêu chí
Chức năng VPR trong Excel là một công cụ thực sự mạnh mẽ để thực hiện tìm kiếm một giá trị cụ thể trong cơ sở dữ liệu. Tuy nhiên, có một hạn chế đáng kể - cú pháp của nó cho phép bạn chỉ tìm kiếm một giá trị. Nếu bạn muốn tìm kiếm theo nhiều điều kiện thì sao? Bạn sẽ tìm thấy giải pháp bên dưới.
Ví dụ 1: Tìm kiếm theo 2 tiêu chí khác nhau
Giả sử chúng ta có một danh sách các đơn đặt hàng và chúng ta muốn tìm Số lượng hàng hoá (Qty.), Dựa trên hai tiêu chí - Tên khách hàng (Khách hàng) и Название продукта (Sản phẩm). Vấn đề phức tạp bởi thực tế là mỗi người trong số những người mua đặt hàng một số loại hàng hóa, có thể thấy trong bảng dưới đây:
chức năng thường xuyên VPR sẽ không hoạt động trong trường hợp này vì nó sẽ trả về giá trị đầu tiên mà nó tìm thấy khớp với giá trị tra cứu đã cho. Ví dụ, nếu bạn muốn biết số lượng của một mặt hàng Kẹo'do người mua đặt hàng Đồi Jeremy, viết công thức sau:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
- công thức này sẽ trả về kết quả 15tương ứng với sản phẩm Táo, bởi vì đó là giá trị đầu tiên phù hợp.
Có một cách giải quyết đơn giản - tạo một cột bổ sung để kết hợp tất cả các tiêu chí mong muốn. Trong ví dụ của chúng tôi, đây là các cột Tên khách hàng (Khách hàng) и Название продукта (Sản phẩm). Đừng quên rằng cột được hợp nhất phải luôn là cột ngoài cùng bên trái trong phạm vi tìm kiếm, vì nó là cột bên trái mà hàm VPR tra cứu khi tìm kiếm một giá trị.
Vì vậy, bạn thêm một cột bổ trợ vào bảng và sao chép công thức sau trên tất cả các ô của nó: = B2 & C2. Nếu bạn muốn chuỗi dễ đọc hơn, bạn có thể tách các giá trị kết hợp bằng một khoảng trắng: = B2 & »« & C2. Sau đó, bạn có thể sử dụng công thức sau:
=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)
or
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)
Tế bào ở đâu B1 chứa giá trị được nối của đối số lookup_value (lookup_value) và 4 - Tranh luận col_index_num (column_number), tức là số của cột chứa dữ liệu cần lấy.
Ví dụ 2: VLOOKUP theo hai tiêu chí với bảng được xem trên trang tính khác
Nếu bạn cần cập nhật bảng chính (Bảng chính) bằng cách thêm dữ liệu từ bảng thứ hai (Bảng tra cứu), nằm trên một trang tính khác hoặc trong một sổ làm việc Excel khác, thì bạn có thể thu thập giá trị mong muốn trực tiếp trong công thức mà bạn chèn vào bảng chính.
Như trong ví dụ trước, bạn sẽ cần một cột bổ trợ trong bảng Tra cứu với các giá trị được kết hợp. Cột này phải là cột ngoài cùng bên trái trong phạm vi tìm kiếm.
Vì vậy, công thức với VPR có thể như thế này:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Ở đây, cột B và C chứa tên khách hàng và tên sản phẩm, và liên kết Đơn hàng! $ A & $ 2: $ D $ 2 xác định một bảng để tra cứu trong một trang tính khác.
Để làm cho công thức dễ đọc hơn, bạn có thể đặt tên cho phạm vi chế độ xem và khi đó công thức sẽ trông đơn giản hơn nhiều:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Để công thức hoạt động, các giá trị trong cột ngoài cùng bên trái của bảng mà bạn đang xem phải được kết hợp chính xác theo cách giống như trong tiêu chí tìm kiếm. Trong hình trên, chúng tôi đã kết hợp các giá trị u2bu2band đặt một khoảng trắng giữa chúng, giống như cách bạn cần làm trong đối số đầu tiên của hàm (BXNUMX & “” & CXNUMX).
Nhớ lại! Chức năng VPR giới hạn trong 255 ký tự, nó không thể tìm kiếm giá trị dài hơn 255 ký tự. Hãy ghi nhớ điều này và đảm bảo rằng độ dài của giá trị mong muốn không vượt quá giới hạn này.
Tôi đồng ý rằng việc thêm một cột phụ không phải là giải pháp thanh lịch nhất và không phải lúc nào cũng được chấp nhận. Bạn có thể làm điều tương tự mà không cần cột trợ giúp, nhưng điều đó sẽ yêu cầu một công thức phức tạp hơn nhiều với sự kết hợp của các hàm INDEX (INDEX) và Đội hình thi đấu (TIẾP XÚC THÊM).
Chúng tôi trích xuất các giá trị thứ 2, thứ 3, v.v. bằng hàm VLOOKUP
Bạn đã biết điều đó VPR chỉ có thể trả về một giá trị phù hợp, chính xác hơn là giá trị đầu tiên được tìm thấy. Nhưng điều gì sẽ xảy ra nếu giá trị này được lặp lại nhiều lần trong mảng đã xem và bạn muốn trích xuất giá trị thứ 2 hoặc thứ 3 trong số đó? Nếu tất cả các giá trị thì sao? Vấn đề có vẻ phức tạp, nhưng giải pháp tồn tại!
Giả sử một cột của bảng chứa tên của các khách hàng (Customer Name), và cột còn lại chứa các sản phẩm (Product) mà họ đã mua. Hãy thử tìm các mặt hàng thứ 2, 3 và 4 được mua bởi một khách hàng nhất định.
Cách dễ nhất là thêm một cột phụ trước cột tên khách hàng và điền vào tên khách hàng với số lần lặp lại của mỗi tên, ví dụ: John Doe1, John Doe2 vv Chúng tôi sẽ thực hiện thủ thuật với việc đánh số bằng hàm COUNTIF (COUNTIF), cho rằng tên khách hàng nằm trong cột B:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Sau đó bạn có thể sử dụng chức năng bình thường VPRđể tìm đơn đặt hàng cần thiết. Ví dụ:
- Tìm kiếm 2 tháng mặt hàng do khách hàng đặt Dan Brown:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - Tìm kiếm 3 tháng mặt hàng do khách hàng đặt Dan Brown:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
Trên thực tế, bạn có thể nhập một tham chiếu ô làm giá trị tra cứu thay vì văn bản, như thể hiện trong hình sau:
Nếu bạn chỉ đang tìm kiếm 2-e lặp lại, bạn có thể làm điều đó mà không cần cột phụ bằng cách tạo một công thức phức tạp hơn:
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")
Trong công thức này:
- $ F $ 2 - ô chứa tên người mua (không thay đổi, xin lưu ý - liên kết là tuyệt đối);
- $B$ - cột tên khách hàng;
- Bảng4 - Bàn của bạn (nơi này cũng có thể là một phạm vi thông thường);
- $ C16 - ô cuối cùng của bảng hoặc dải ô của bạn.
Công thức này chỉ tìm thấy giá trị phù hợp thứ hai. Nếu bạn cần giải nén các lần lặp lại còn lại, hãy sử dụng giải pháp trước đó.
Nếu bạn cần một danh sách tất cả các kết quả phù hợp - hàm VPR đây không phải là một trình trợ giúp, vì nó chỉ trả về một giá trị tại một thời điểm - khoảng thời gian. Nhưng Excel có một hàm INDEX (INDEX), có thể dễ dàng đối phó với nhiệm vụ này. Công thức như vậy sẽ như thế nào, bạn sẽ tìm hiểu trong ví dụ sau.
Lấy tất cả các lần lặp lại giá trị mong muốn
Như được đề cập ở trên VPR không thể trích xuất tất cả các giá trị trùng lặp từ phạm vi đã quét. Để làm điều này, bạn cần một công thức phức tạp hơn một chút, được tạo thành từ một số hàm Excel, chẳng hạn như INDEX (MỤC LỤC), NHỎ (NHỎ) và HÀNG (HÀNG)
Ví dụ: công thức bên dưới tìm tất cả các lần lặp lại giá trị từ ô F2 trong phạm vi B2: B16 và trả về kết quả từ các hàng giống nhau trong cột C.
{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}
{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}
Nhập công thức mảng này vào nhiều ô liền kề, chẳng hạn như các ô F4: F8như trong hình bên dưới. Số lượng ô phải bằng hoặc lớn hơn số lần lặp lại tối đa có thể của giá trị được tìm kiếm. Đừng quên bấm vào Ctrl + Shift + Enterđể nhập công thức mảng một cách chính xác.
Nếu bạn muốn hiểu cách hoạt động của nó, hãy đi sâu vào chi tiết của công thức một chút:
Phần 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$ F $ 2 = B2: B16 - so sánh giá trị trong ô F2 với từng giá trị trong phạm vi B2: B16. Nếu tìm thấy một kết quả phù hợp, thì biểu thức STRING (C2: C16) -1 trả về số dòng tương ứng (giá trị -1 cho phép bạn không bao gồm dòng tiêu đề). Nếu không có kết quả phù hợp nào, hàm IF (IF) trả về một chuỗi trống.
Kết quả hàm IF (IF) sẽ có một mảng ngang như vậy: {1,"",3,"",5,"","","","","","",12,"","",""}
Phần 2:
ROW()-3
СТРОКА()-3
Đây là chức năng HÀNG (LINE) hoạt động như một bộ đếm bổ sung. Vì công thức được sao chép vào các ô F4: F9, chúng tôi trừ số 3 từ kết quả hàm để nhận giá trị 1 trong phòng giam F4 (dòng 4, trừ 3) để có được 2 trong phòng giam F5 (dòng 5, trừ 3) và như vậy.
Phần 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
Chức năng NHỎ (NHỎ) trả lại n-ồ giá trị nhỏ nhất trong mảng dữ liệu. Trong trường hợp của chúng ta, vị trí nào (từ nhỏ nhất) để trả về được xác định bởi hàm HÀNG (LINE) (xem Phần 2). Vì vậy, đối với một ô F4 chức năng NHỎ ({mảng}, 1) Trả về 1 tháng (nhỏ nhất) phần tử mảng, tức là 1. Đối với ô F5 Trả về 2 tháng phần tử nhỏ nhất trong mảng, đó là 3, Vv
Phần 4:
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
Chức năng INDEX (INDEX) chỉ trả về giá trị của một ô cụ thể trong một mảng C2:C16. Đối với ô F4 chức năng INDEX ($ C $ 2: $ C $ 16) sẽ trở lại TáoTrong F5 chức năng INDEX ($ C $ 2: $ C $ 16) sẽ trở lại Kẹo' và như vậy.
Phần 5:
IFERROR()
ЕСЛИОШИБКА()
Cuối cùng, chúng tôi đặt công thức bên trong hàm SỐ PHIẾU (IFERROR), bởi vì bạn có thể không hài lòng với thông báo lỗi #TẠI (# N / A) nếu số ô mà công thức được sao chép vào đó ít hơn số giá trị trùng lặp trong phạm vi đang được xem.
XNUMXD tìm kiếm theo hàng và cột đã biết
Thực hiện tìm kiếm XNUMXD trong Excel liên quan đến việc tìm kiếm một giá trị theo số hàng và cột đã biết. Nói cách khác, bạn đang trích xuất giá trị ô tại giao điểm của một hàng và cột cụ thể.
Vì vậy, hãy chuyển sang bảng của chúng tôi và viết một công thức với một hàm VPR, sẽ tìm thấy thông tin về chi phí chanh được bán trong tháng Ba.
Có một số cách để thực hiện tìm kiếm XNUMXD. Kiểm tra các tùy chọn và chọn một trong những phù hợp với bạn nhất.
Hàm VLOOKUP và MATCH
Bạn có thể sử dụng một loạt các chức năng VPR (VLOOKUP) và THÊM ĐƯỢC TIẾP XÚC (MATCH) để tìm giá trị tại giao điểm của các trường Название продукта (chuỗi) và tháng (cột) của mảng được đề cập:
=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)
Công thức trên là một hàm thông thường VPR, tìm kiếm một kết quả khớp chính xác của giá trị “Lemons” trong các ô từ A2 đến A9. Nhưng vì bạn không biết doanh số bán hàng trong tháng XNUMX ở cột nào, bạn sẽ không thể đặt số cột cho đối số hàm thứ ba. VPR. Thay vào đó, hàm được sử dụng THÊM ĐƯỢC TIẾP XÚCđể xác định cột này.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Được dịch sang tiếng người, công thức này có nghĩa là:
- Chúng tôi đang tìm kiếm các ký tự “Mar” - đối số lookup_value (giá trị tra cứu);
- Tìm kiếm trong các ô từ A1 đến I1 - đối số tra cứu_array (tra cứu_mảng);
- Trả lại kết quả khớp chính xác - đối số Loại so khớp (Loại so khớp).
Sử dụng 0 trong đối số thứ ba, bạn nói các hàm THÊM ĐƯỢC TIẾP XÚC tìm kiếm giá trị đầu tiên khớp chính xác với giá trị bạn đang tìm kiếm. Điều này tương đương với giá trị KHÔNG ĐÚNG (FALSE) cho đối số thứ tư VPR.
Đây là cách bạn có thể tạo công thức tìm kiếm hai chiều trong Excel, còn được gọi là tìm kiếm hai chiều hoặc tìm kiếm hai chiều.
Hàm SUMPRODUCT
Chức năng SUMPRODUCT (SUMPRODUCT) trả về tổng các tích của các mảng đã chọn:
=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)
=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)
Hàm INDEX và MATCH
Trong bài viết tiếp theo, tôi sẽ giải thích chi tiết các hàm này, vì vậy bây giờ bạn chỉ cần sao chép công thức này:
=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))
Dải ô được đặt tên và toán tử giao lộ
Nếu bạn không thuộc tất cả các công thức Excel phức tạp đó, bạn có thể thích cách trực quan và dễ nhớ này:
- Chọn bảng, mở tab Công thức (Công thức) và nhấp vào Tạo từ Lựa chọn (Tạo từ lựa chọn).
- Kiểm tra các hộp Hàng đầu (ở dòng trên) và Cột bên trái (ở cột bên trái). Microsoft Excel sẽ gán tên cho các phạm vi từ các giá trị ở hàng trên cùng và cột bên trái của bảng tính của bạn. Bây giờ bạn có thể tìm kiếm bằng cách sử dụng những tên này trực tiếp mà không cần tạo công thức.
- Trong bất kỳ ô trống nào, hãy viết = row_name column_name, ví dụ như thế này:
= Lemons Mar
… hoặc ngược lại:
= Mar Lemons
Hãy nhớ rằng tên hàng và cột phải được phân tách bằng dấu cách, trong trường hợp này hoạt động giống như toán tử giao nhau.
Khi bạn nhập tên, Microsoft Excel sẽ hiển thị chú giải công cụ với danh sách các tên trùng khớp, giống như khi bạn nhập công thức.
- Ấn Bản đăng ký hạng mục thi và kiểm tra kết quả
Nói chung, bất kỳ phương pháp nào bạn chọn ở trên, kết quả của tìm kiếm hai chiều sẽ giống nhau:
Sử dụng nhiều hàm VLOOKUP trong một công thức
Nó xảy ra rằng bảng chính và bảng tra cứu không có một cột chung duy nhất và điều này ngăn bạn sử dụng chức năng thông thường VPR. Tuy nhiên, có một bảng khác không chứa thông tin mà chúng ta quan tâm, nhưng có một cột chung với bảng chính và bảng tra cứu.
Hãy xem ví dụ sau. Chúng ta có một bảng Chính với một cột SKU (mới), nơi bạn muốn thêm một cột có giá tương ứng từ một bảng khác. Ngoài ra, chúng tôi có 2 bảng tra cứu. Bảng đầu tiên (Bảng tra cứu 1) chứa các số được cập nhật SKU (mới) và tên sản phẩm, và thứ hai (Bảng tra cứu 2) - tên sản phẩm và số cũ Mã hàng (cũ).
Để thêm giá từ bảng tra cứu thứ hai vào bảng chính, bạn phải thực hiện một hành động được gọi là gấp đôi VPR hoặc lồng vào nhau VPR.
- Viết một hàm VPR, tìm tên sản phẩm trong bảng Bảng tra cứu 1sử dụng Mã hàng, như giá trị mong muốn:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)Đây Mới_SKU - phạm vi được đặt tên $ A: $ B trong bàn Bảng tra cứu 1, 2 - Đây là cột B, chứa tên hàng hóa (xem hình trên)
- Viết công thức để chèn giá từ bảng Bảng tra cứu 2 dựa trên tên sản phẩm nổi tiếng. Để thực hiện việc này, hãy dán công thức bạn đã tạo trước đó làm giá trị tra cứu cho hàm mới VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)Đây Giá cả - phạm vi được đặt tên $ A: $ C trong bàn Bảng tra cứu 2, 3 là cột C chứa giá.
Hình dưới đây cho thấy kết quả được trả về bởi công thức chúng tôi đã tạo:
Thay thế động dữ liệu từ các bảng khác nhau bằng hàm VLOOKUP và INDIRECT
Trước tiên, hãy làm rõ ý của chúng tôi qua cụm từ “Thay thế động của dữ liệu từ các bảng khác nhau” để đảm bảo rằng chúng ta hiểu nhau một cách chính xác.
Có những tình huống khi có một số trang tính với dữ liệu có cùng định dạng và cần phải trích xuất thông tin cần thiết từ một trang tính nhất định, tùy thuộc vào giá trị được nhập vào một ô nhất định. Tôi nghĩ sẽ dễ dàng hơn để giải thích điều này bằng một ví dụ.
Hãy tưởng tượng rằng bạn có báo cáo bán hàng cho một số khu vực với các sản phẩm giống nhau và ở cùng một định dạng. Bạn muốn tìm số liệu bán hàng cho một khu vực cụ thể:
Nếu bạn chỉ có hai báo cáo như vậy, thì bạn có thể sử dụng một công thức đơn giản đáng kinh ngạc với các hàm VPR и IF (NẾU) để chọn báo cáo mong muốn để tìm kiếm:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
Địa điểm:
- $Đ$2 là một ô chứa tên của sản phẩm. Lưu ý rằng chúng tôi sử dụng tham chiếu tuyệt đối ở đây để tránh thay đổi giá trị tra cứu khi sao chép công thức sang các ô khác.
- $ D3 là một ô có tên của vùng. Chúng tôi đang sử dụng tham chiếu cột tuyệt đối và tham chiếu hàng tương đối vì chúng tôi dự định sao chép công thức sang các ô khác trong cùng một cột.
- FL_Sales и CA_Doanh số - tên của các bảng (hoặc phạm vi đã đặt tên) có chứa các báo cáo bán hàng tương ứng. Tất nhiên, bạn có thể sử dụng tên trang tính thông thường và tham chiếu phạm vi ô, ví dụ: 'Trang tính FL'! $ A $ 3: $ B $ 10, nhưng các phạm vi được đặt tên thuận tiện hơn nhiều.
Tuy nhiên, khi có nhiều bảng như vậy, hàm IF không phải là giải pháp tốt nhất. Thay vào đó, bạn có thể sử dụng hàm GIÁN TIẾP (INDIRECT) để trả về phạm vi tìm kiếm mong muốn.
Như bạn có thể biết, hàm GIÁN TIẾP được sử dụng để trả về một liên kết được cung cấp bởi một chuỗi văn bản, đó chính là những gì chúng ta cần bây giờ. Vì vậy, hãy mạnh dạn thay vào công thức trên biểu thức bằng hàm IF liên kết với chức năng GIÁN TIẾP. Đây là một sự kết hợp VPR и GIÁN TIẾP hoạt động tốt với:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
Địa điểm:
- $Đ$2 - đây là ô có tên sản phẩm, nó không thay đổi do liên kết tuyệt đối.
- $ D3 là ô chứa phần đầu tiên của tên vùng. Trong ví dụ của chúng tôi, điều này FL.
- _Việc bán hàng - phần chung của tên của tất cả các phạm vi hoặc bảng được đặt tên. Khi được kết hợp với giá trị trong ô D3, nó tạo thành tên đủ điều kiện của phạm vi bắt buộc. Dưới đây là một số chi tiết cho những người mới sử dụng chức năng GIÁN TIẾP.
Cách INDIRECT và VLOOKUP hoạt động
Đầu tiên, hãy để tôi nhắc bạn cú pháp của hàm GIÁN TIẾP (GIÁN TIẾP):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
Đối số đầu tiên có thể là tham chiếu ô (kiểu A1 hoặc R1C1), tên dải ô hoặc chuỗi văn bản. Đối số thứ hai xác định kiểu liên kết nào được chứa trong đối số đầu tiên:
- A1nếu đối số là MÃ ĐÚNG (TRUE) hoặc không được chỉ định;
- R1C1, Nếu FNHƯ E (SAI).
Trong trường hợp của chúng tôi, liên kết có kiểu A1, vì vậy bạn có thể bỏ qua đối số thứ hai và tập trung vào đối số đầu tiên.
Vì vậy, chúng ta hãy quay lại báo cáo bán hàng của chúng tôi. Nếu bạn nhớ, thì mỗi báo cáo là một bảng riêng biệt nằm trên một trang tính riêng biệt. Để công thức hoạt động chính xác, bạn phải đặt tên cho bảng (hoặc phạm vi) của mình và tất cả các tên phải có một phần chung. Ví dụ, như thế này: CA_Doanh số, FL_Bán hàng, TX_Bán hàng và như thế. Như bạn có thể thấy, “_Sales” có trong tất cả các tên.
Chức năng GIÁN TIẾP kết nối giá trị trong cột D và chuỗi văn bản “_Sales”, từ đó cho biết VPR trong bảng nào để tìm kiếm. Nếu ô D3 chứa giá trị “FL”, công thức sẽ tìm kiếm trong bảng FL_Bán hàng, nếu “CA” - trong bảng CA_Doanh số và như vậy.
Kết quả của các chức năng VPR и GIÁN TIẾP sẽ như sau:
Nếu dữ liệu nằm trong các sách Excel khác nhau, thì bạn cần thêm tên sách trước phạm vi được đặt tên, ví dụ:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Nếu chức năng GIÁN TIẾP tham chiếu đến một sổ làm việc khác, sổ làm việc đó phải đang mở. Nếu đóng lại thì hàm sẽ báo lỗi. #REF! (#SSYL!).