Formulas and Functions
การแก้ไขการควบคุมนี้จะเป็นการอัพเดทหน้านี้โดยอัตโนมัติ

XLOOKUP

ฟังก์ชั่น XLOOKUP จะค้นหาช่วงของค่าที่ระบุและจะส่งค่ากลับมาเป็นค่าจากแถวเดียวกันในคอลัมน์อื่น

XLOOKUP(ค่าค้นหา, ช่วงการค้นหา, ส่งกลับช่วง, หากไม่พบ, ประเภทการจับคู่, ประเภทการค้นหา)

ค่าค้นหา: ค่าที่กำลังถูกค้นหาในช่วงการค้นหา ค่าค้นหาสามารถประกอบด้วยค่าใดๆ หรือสตริง REGEX ได้

ช่วงการค้นหา: เซลล์ที่จะค้นหา

ส่งกลับช่วง: เซลล์ที่จะส่งกลับ

หากไม่พบ: อาร์กิวเมนต์ (ไม่บังคับ) ที่ระบุข้อความที่จะแสดงหากไม่พบรายการที่ตรงกัน

ประเภทการจับคู่: อาร์กิวเมนต์ (ไม่บังคับ) ที่ระบุประเภทของรายการที่ตรงกันที่จะใช้ค้นหา

ตรงกันหรือน้อยกว่า (-1): ถ้าไม่มีค่าที่ตรงกัน จะส่งค่ากลับมาเป็นข้อผิดพลาด

ค่าที่ตรงกันทั้งหมด (0 หรือเว้นว่าง): ถ้าไม่มีค่าที่ตรงกันทั้งหมด จะส่งค่ากลับมาเป็นข้อผิดพลาด

ตรงกันหรือมากกว่า (1): ถ้าไม่มีค่าที่ตรงกัน จะส่งค่ากลับมาเป็นข้อผิดพลาด

อักขระตัวแทน (2): *, ? และ ~ มีความหมายเฉพาะ REGEX สามารถใช้ได้เฉพาะใน XLOOKUP หากคุณใช้อักขระตัวแทน

ประเภทการค้นหา: อาร์กิวเมนต์ (ไม่บังคับ) ที่ระบุลำดับที่จะใช้ค้นหาช่วง

การเรียงไบนารีจากมากไปหาน้อย (-2): การค้นหาข้อมูลแบบไบนารีที่ต้องเรียงช่วงตามลำดับจากมากไปหาน้อย หากเป็นอย่างอื่นจะส่งผลกลับมาเป็นข้อผิดพลาด

รายการสุดท้ายถึงรายการแรก (-1): ค้นหาช่วงตั้งแต่รายการสุดท้ายถึงรายการแรก

รายการแรกถึงรายการสุดท้าย (1 หรือเว้นว่าง): ค้นหาช่วงตั้งแต่รายการแรกถึงรายการสุดท้าย

การเรียงไบนารีจากน้อยไปหามาก (2): การค้นหาข้อมูลแบบไบนารีที่ต้องเรียงช่วงตามลำดับจากน้อยไปหามาก หากเป็นอย่างอื่นจะส่งผลกลับมาเป็นข้อผิดพลาด

หมายเหตุ

  • ถ้าช่วงการค้นหาหรือส่งกลับช่วงเป็นการอ้างอิงแบบขยาย (เช่น "B") ส่วนหัวและส่วนท้ายจะไม่ถูกสนใจโดยอัตโนมัติ

ตัวอย่าง

ตารางด้านล่างที่ชื่อว่าผลิตภัณฑ์จะระบุผลิตภัณฑ์และคุณสมบัติของผลิตภัณฑ์เหล่านั้น เช่น ขนาดและราคา:

A

B

C

D

E

1

ผลิตภัณฑ์

ความยาว (ซม.)

ความกว้าง (ซม.)

น้ำหนัก (กก.)

ราคา

2

ผลิตภัณฑ์ 1

16

17

10

$82.00

3

ผลิตภัณฑ์ 2

16

20

18

$77.00

4

ผลิตภัณฑ์ 3

11

11

15

$88.00

5

ผลิตภัณฑ์ 4

15

16

20

$63.00

ค้นหาด้วย XLOOKUP

ด้วย XLOOKUP คุณสามารถแทรกสูตรลงในสเปรดชีตของคุณที่จะส่งกลับค่าใดๆ ที่เกี่ยวข้องได้ โดยใส่ชื่อผลิตภัณฑ์ก่อน แล้วตามด้วยคอลัมน์ที่มีค่าที่คุณต้องการส่งกลับ ตัวอย่างเช่น ถ้าคุณต้องการส่งกลับความกว้างของผลิตภัณฑ์ 1 ในตารางด้านบน คุณสามารถใช้สูตรต่อไปนี้ได้ ซึ่งจะส่งกลับ 17 ซม.:

ตัวแก้ไขสูตรที่แสดงสูตร =XLOOKUP(ผลิตภัณฑ์::$A2,ผลิตภัณฑ์::A,ความกว้าง)

สูตรนี้ใช้อาร์กิวเมนต์ต่อไปนี้:

  • ค่าค้นหา: ผลิตภัณฑ์::$A2 การอ้างอิงแบบสัมบูรณ์ไปยังเซลล์ในตารางผลิตภัณฑ์ที่มีผลิตภัณฑ์ 1

  • ช่วงการค้นหา: ผลิตภัณฑ์::A คอลัมน์ที่จะค้นหาผลิตภัณฑ์ 1

  • ส่งกลับช่วง: ความกว้าง คอลัมน์ที่มีค่าที่จะส่งกลับซึ่งเกี่ยวข้องกับผลิตภัณฑ์ 1

  • ประเภทการจับคู่: ละเว้น ถ้ามีการละเว้นประเภทการจับคู่ XLOOKUP จะค้นหารายการที่ตรงกันทั้งหมดตามค่าเริ่มต้น

ตั้งค่าสตริงหากไม่พบ

ถ้าคุณต้องการค้นหาความยาวของผลิตภัณฑ์ที่เจาะจงและส่งกลับความกว้างที่ตรงกัน รวมถึงสตริงที่จะส่งกลับหากไม่พบรายการที่ตรงกัน คุณสามารถใช้สูตรต่อไปนี้ที่ส่งกลับ "ไม่มีที่ตรงกัน" ได้:

ตัวแก้ไขสูตรที่แสดงสูตร =XLOOKUP(13,ความยาว,ความกว้าง,"ไม่มีที่ตรงกัน",0)

ในสูตรนี้ อาร์กิวเมนต์หากไม่พบถูกใช้เพื่อดำเนินการค้นหาที่เจาะจงยิ่งขึ้น:

  • ค่าค้นหา: 13 ค่าที่จะค้นหาในช่วงที่ระบุในช่วงการค้นหา

  • ช่วงการค้นหา: ความยาว คอลัมน์ที่จะค้นหา

  • ส่งกลับช่วง: ความกว้าง คอลัมน์ที่มีค่าที่จะส่งกลับหากพบรายการที่ตรงกันกับค่าค้นหา

  • หากไม่พบ: "ไม่มีที่ตรงกัน" สตริงที่จะแสดงหากไม่พบผลิตภัณฑ์ที่มีความยาว 13 ซม.

  • ประเภทการจับคู่: รายการที่ตรงกันทั้งหมด (0) อาร์กิวเมนต์นี้จะค้นหาเฉพาะความยาว 13 ซม. เท่านั้น

ค้นหาค่าที่ใกล้เคียงที่สุดรองลงมา

XLOOKUP ยังสามารถให้การค้นหาแบบกว้างๆ โดยอิงจากค่าที่เจาะจงและค่าที่ใกล้เคียงกับค่านั้นได้อีกด้วย ถ้าคุณเปลี่ยนประเภทการจับคู่จากสูตรด้านบน คุณสามารถส่งกลับความกว้างที่ตรงกับความยาว 13 ซม. หรือค่าที่น้อยที่สุดรองลงมาได้ สูตรด้านล่างจะส่งกลับความกว้าง 11 ซม.:

ตัวแก้ไขสูตรที่แสดงสูตร =XLOOKUP(13,Length,Width,"ไม่มีที่ตรงกัน",1,-1)

ในสูตรนี้ อาร์กิวเมนต์จะเหมือนกับด้านบน แต่จะมีการใช้ค่าอื่นสำหรับประเภทการจับคู่เพื่อเปลี่ยนวิธีค้นหาตาราง:

  • ประเภทการจับคู่: ตรงกันหรือน้อยกว่า (-1) อาร์กิวเมนต์นี้จะค้นหาความยาว 13 ซม. และหากไม่พบค่านั้น จะค้นหาค่าที่น้อยที่สุดรองลงมาในคอลัมน์ความยาว

เปลี่ยนลำดับการค้นหา

ในบางกรณี การเปลี่ยนลำดับตารางที่จะค้นหาด้วย XLOOKUP อาจมีประโยชน์ ตัวอย่างเช่น ในตารางด้านบน ผลิตภัณฑ์ที่มีความยาว 16 ซม. มีสองรายการ ดังนั้นจึงเป็นไปได้ที่จะมีรายการที่ตรงกันสองรายการหากคุณค้นหา 16 ซม. ในคอลัมน์ความยาวโดยใช้ค่าค้นหาและช่วงการค้นหา คุณสามารถตั้งค่าลำดับการค้นหาโดยใช้สูตรดังนี้ได้ ซึ่งจะส่งกลับ 20 ซม.:

ตัวแก้ไขสูตรที่แสดงสูตร =XLOOKUP(16,Length,Width,"ไม่มีที่ตรงกัน",1,-1)

ในสูตรนี้ อาร์กิวเมนต์ประเภทการค้นหาจะถูกใช้เพื่อตั้งค่าลำดับที่ XLOOKUP ค้นหาตารางสำหรับรายการที่ตรงกัน:

  • ค่าค้นหา: 16 ค่าที่จะค้นหาในช่วงที่ระบุในช่วงการค้นหา

  • ช่วงการค้นหา: ความยาว คอลัมน์ที่จะค้นหา

  • ส่งกลับช่วง: ความกว้าง คอลัมน์ที่มีค่าที่จะส่งกลับหากพบรายการที่ตรงกันกับค่าค้นหา

  • หากไม่พบ: "ไม่มีที่ตรงกัน" สตริงที่จะแสดงหากไม่พบผลิตภัณฑ์ที่มีความยาว 16 ซม.

  • ประเภทการจับคู่: ตรงกันหรือมากกว่า (1) อาร์กิวเมนต์นี้จะค้นหาความยาว 16 ซม. และหากไม่พบค่านั้น จะค้นหาค่าที่มากที่สุดรองลงมาในคอลัมน์ความยาว

  • ประเภทการค้นหา: รายการสุดท้ายถึงรายการแรก (-1) อาร์กิวเมนต์นี้จะค้นหาคอลัมน์จากค่าสุดท้ายจนถึงค่าแรก

ใช้ XLOOKUP กับฟังก์ชั่นอื่นๆ

XLOOKUP ยังสามารถใช้กับฟังก์ชั่นอื่นๆ ได้อีกด้วย เช่น SUM ตัวอย่างเช่น คุณสามารถใช้สูตรดังตัวอย่างด้านล่างเพื่อส่งกลับ $247 ซึ่งเป็น SUM ของราคาผลิตภัณฑ์ 1, 2 และ 3 ได้:

ตัวแก้ไขสูตรที่แสดงสูตร =SUM(XLOOKUP(ผลิตภัณฑ์::$A2,ผลิตภัณฑ์::A,ราคา):XLOOKUP(ผลิตภัณฑ์::$A4,ผลิตภัณฑ์::A,ราคา))

ในตัวอย่างนี้ XLOOKUP แรกจะค้นหาราคาของผลิตภัณฑ์ 1 และ XLOOKUP ที่สองจะค้นหาราคาของผลิตภัณฑ์ 3 เครื่องหมายทวิภาค (:) ระหว่างฟังก์ชั่น XLOOKUP ระบุว่า SUM ควรส่งกลับไม่เพียงราคารวมของผลิตภัณฑ์ 1 และผลิตภัณฑ์ 3 เท่านั้น แต่ยังควรส่งกลับค่าใดๆ ที่อยู่ระหว่างนั้นอีกด้วย

ในสูตรด้านล่าง XLOOKUP ถูกใช้กับ REGEX เพื่อส่งกลับผลิตภัณฑ์ 2 ซึ่งเป็นผลิตภัณฑ์แรกที่มีความกว้างเริ่มต้นด้วย "2":

ตัวแก้ไขสูตรที่แสดงสูตร =XLOOKUP(REGEX("^2.*"), ผลิตภัณฑ์::C2:C5, ผลิตภัณฑ์::A2:A5, FALSE,2)

ในตัวอย่างนี้ "อักขระตัวแทน (2)" ถูกใช้สำหรับประเภทการจับคู่เพื่อใช้ประโยชน์ของอักขระตัวแทนในฟังก์ชั่น REGEX

ตัวอย่างเพิ่มเติม

กำหนดให้ตารางเป็นดังนี้:

A

B

C

1

ชื่อ

อายุ

เงินเดือน

2

Amy

35

71000

3

Matthew

27

81000

4

Chloe

42

86000

5

Sophia

51

66000

6

Kenneth

28

52000

7

Tom

49

62000

8

Aaron

63

89000

9

Mary

22

34000

10

Alice

29

52000

11

Brian

35

52500

=XLOOKUP(49,B2:B11,C2:C11) จะส่งค่ากลับมาเป็น "62000" ซึ่งเป็นเงินเดือนของลูกจ้างคนแรกที่มีอายุ 49 ปี

=XLOOKUP(60000,C2:C11,B2:B11,"No match") จะส่งค่ากลับมาเป็น "No match" เนื่องจากไม่มีลูกจ้างที่มีเงินเดือน $60,000

=XLOOKUP(REGEX("^C.*"), A2:A11, B2:B11, FALSE, 2) จะส่งค่ากลับมาเป็น "42" ซึ่งเป็นอายุของ "Chloe" ลูกจ้างคนแรกในช่วงที่มีชื่อขึ้นต้นด้วย "C"

ดูเพิ่มเติมXMATCH
Morty Proxy This is a proxified and sanitized view of the page, visit original site.
เป็นประโยชน์ไหม
จำกัดอักขระ: 250
จำกัดอักขระสูงสุดไม่เกิน 250
ขอบคุณที่แสดงความคิดเห็น
Morty Proxy This is a proxified and sanitized view of the page, visit original site.