Formulas and Functions
Pengubahsuaian kawalan ini akan memuatkan semula halaman ini

XLOOKUP

Fungsi XLOOKUP mencari julat untuk nilai yang ditentukan dan mengembalikan nilai daripada baris yang sama dalam lajur lain.

XLOOKUP(search-value, search-range, return-range, if-not-found, match-type, search-type)

search-value: Nilai yang dicari dalam search-range. search-value boleh mengandungi any value, atau rentetan REGEX.

search-range: Sel untuk dicari.

return-range: Sel untuk dikembalikan.

if-not-found: Argumen pilihan untuk menentukan mesej paparan jika padanan tidak ditemui.

match-type: Argumen pilihan yang menentukan jenis padanan untuk dicari.

exact or next smallest (-1): Jika tiada padanan, mengembalikan ralat.

exact match (0 atau dikecualikan): Jika tiada padanan tepat, mengembalikan ralat.

exact or next largest (1): Jika tiada padanan, mengembalikan ralat.

wildcard (2): *, ? dan ~ mempunyai maksud tertentu. REGEX hanya boleh digunakan dalam XLOOKUP jika anda menggunakan wildcard.

search-type: Argumen pilihan yang menentukan tertib untuk mencari julat.

Binary descending (-2): Carian perduaan yang memerlukan julat untuk diisih dalam tertib menurun, jika tidak mengembalikan ralat.

Last to first (-1): Cari julat daripada terakhir hingga pertama.

First to last (1 atau dikecualikan): Cari julat daripada pertama hingga terakhir.

Binary ascending (2): Carian perduaan yang memerlukan julat untuk diisih dalam tertib menaik, jika tidak mengembalikan ralat.

Nota

  • Jika sama ada search-range atau return-range ialah rujukan yang menjangkau (seperti "B"), pengepala dan pengaki diabaikan secara automatik.

Contoh

Jadual di bawah yang bertajuk Produk menyenaraikan produk dan atributnya, seperti saiz dan harga:

A

B

C

D

E

1

Produk

Panjang (cm)

Lebar (cm)

Berat (kg)

Harga

2

Produk 1

16

17

10

$82.00

3

Produk 2

16

20

18

$77.00

4

Produk 3

11

11

15

$88.00

5

Produk 4

15

16

20

$63.00

Cari dengan XLOOKUP

Dengan XLOOKUP, anda boleh memasukkan formula dalam hamparan anda yang mengembalikan sebarang nilai yang dikaitkan dengan menyediakan nama produk dahulu, kemudian lajur dengan nilai yang anda mahu kembalikan. Contohnya, jika anda mahu mengembalikan lebar Produk 1 dalam jadual di atas, anda boleh menggunakan formula berikut, yang mengembalikan 17 cm:

Editor Formula menunjukkan formula =XLOOKUP(Produk::$A2,Produk::A,Lebar).

Dalam formula ini, argumen berikut digunakan:

  • search-value: Produk::$A2, rujukan mutlak kepada sel dalam jadual Produk yang mengandungi Produk 1.

  • search-range: Produk::A, lajur untuk dicari bagi Produk 1.

  • return-range: Lebar, lajur yang mengandungi nilai untuk dikembalikan yang dikaitkan dengan Produk 1.

  • match-type: Dikecualikan. Jika match-type dikecualikan, XLOOKUP mencari padanan tepat secara lalai.

Setkan rentetan if-not-found

Jika anda mahu mencari panjang produk tertentu dan mengembalikan lebar yang sepadan serta rentetan untuk dikembalikan jika tiada padanan ditemui, anda boleh menggunakan formula berikut, yang mengembalikan "Tiada padanan":

Editor Formula menunjukkan formula =XLOOKUP(13,Panjang,Lebar,"Tiada padanan",0).

Dalam formula ini, argumen if-not-found digunakan untuk melaksanakan carian yang lebih khusus:

  • search-value: 13, nilai untuk dicari dalam julat yang ditentukan dalam search-range.

  • search-range: Panjang, lajur untuk mencari di dalamnya.

  • return-range: Lebar, lajur yang mengandungi nilai untuk dikembalikan jika padanan untuk search-value ditemui.

  • if-not-found: "Tiada padanan", rentetan untuk dipaparkan jika produk dengan panjang 13 cm tidak ditemui.

  • match-type: padanan tepat (0). Carian ini hanya untuk panjang 13 cm.

Cari nilai terdekat seterusnya

XLOOKUP juga boleh menyediakan carian meluas berdasarkan nilai khusus dan nilai yang hampir dengannya. Jika anda menukar match-type daripada formula di atas, anda boleh mengembalikan lebar yang sepadan dengan panjang 13 cm atau nilai terkecil yang paling hampir. Formula berikut mengembalikan lebar 11 cm:

Editor Formula menunjukkan formula =XLOOKUP(13,Panjang,Lebar,"Tiada padanan",1,-1).

Dalam formula ini, argumen adalah sama dengan di atas, tetapi nilai berlainan digunakan untuk match-type untuk menukar cara jadual dicari:

  • match-type: tepat atau terkecil seterusnya (-1). Ini mencari panjang 13 cm dan jika nilai tersebut tidak ditemui, mencari nilai terkecil seterusnya dalam lajur Panjang.

Tukar tertib carian

Dalam tika tertentu, ia mungkin berguna untuk menukar tertib jadual dicari dengan XLOOKUP. Contohnya, dalam jadual di atas, terdapat dua produk dengan panjang 16 cm, jadi terdapat dua padanan yang mungkin jika anda mencari 16 cm dalam lajur Panjang menggunakan search-value dan search-range. Anda boleh mengesetkan tertib carian menggunakan formula seperti ini, yang mengembalikan 20 cm:

Editor Formula menunjukkan formula =XLOOKUP(16,Panjang,Lebar,"Tiada padanan",1,-1).

Dalam formula ini, argumen search-type digunakan untuk mengesetkan tertib XLOOKUP mencari dalam jadual untuk padanan:

  • search-value: 16, nilai untuk dicari dalam julat yang ditentukan dalam search-range.

  • search-range: Panjang, lajur untuk mencari di dalamnya.

  • return-range: Lebar, lajur yang mengandungi nilai untuk dikembalikan jika padanan untuk search-value ditemui.

  • if-not-found: "Tiada padanan", rentetan untuk dipaparkan jika produk dengan panjang 16 cm tidak ditemui.

  • match-type: tepat atau terbesar seterusnya (1). Ini mencari panjang 16 cm dan jika nilai tersebut tidak ditemui, mencari nilai terbesar seterusnya dalam lajur Panjang.

  • search-type: Terakhir hingga pertama (-1). Ini mencari lajur daripada nilai terakhir hingga nilai pertama.

Gunakan XLOOKUP dengan fungsi lain

XLOOKUP juga boleh digunakan dengan fungsi lain, seperti SUM. Contohnya, anda boleh menggunakan formula seperti di bawah untuk mengembalikan $247, SUM untuk harga Produk 1, 2 dan 3:

Editor formula menunjukkan formula =SUM(XLOOKUP(Produk::$A2,Produk::A,Price):XLOOKUP(Produk::$A4,Produk::A,Price)).

Dalam contoh ini, XLOOKUP yang pertama mencari harga Produk 1 dan XLOOKUP kedua mencari harga Produk 3. Titik bertindih (:) antara fungsi XLOOKUP menandakan yang SUM harus mengembalikan bukan sahaja jumlah harga Produk 1 dan Produk 3, tetapi juga sebarang nilai di antaranya.

Dalam formula di bawah, XLOOKUP digunakan dengan REGEX untuk mengembalikan Produk 2, produk pertama dengan lebar yang bermula dengan "2":

Editor formula menunjukkan formula =XLOOKUP(REGEX("^2.*"), Produk::C2:C5, Produk::A2:A5, FALSE,2).

Dalam contoh ini, "kad bebas (2)" digunakan untuk match-type bagi menggunakan kad bebas dalam fungsi REGEX.

Contoh tambahan

Diberikan jadual berikut:

A

B

C

1

Nama

Umur

Gaji

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) mengembalikan "62000," yang merupakan gaji pekerja pertama yang berumur 49.

=XLOOKUP(60000,C2:C11,B2:B11,"Tiada padanan") mengembalikan "Tiada padanan", kerana tiada pekerja yang bergaji $60,000.

=XLOOKUP(REGEX("^C.*"), A2:A11, B2:B11, FALSE, 2) mengembalikan "42", umur "Chloe", pekerja pertama dalam julat yang nama bermula dengan "C".

Lihat jugaXMATCH
Morty Proxy This is a proxified and sanitized view of the page, visit original site.
Berguna?
Had aksara: 250
Had aksara maksimum ialah 250.
Terima kasih atas maklum balas anda.
Morty Proxy This is a proxified and sanitized view of the page, visit original site.