Daptar eusi
Kabéh pilarian klasik sarta fungsi substitusi tipe VPR (VLOOKUP), GPR (HLOOKUP), LEUWIH DIBUKA (PERTANDINGAN) sarta maranéhanana kawas aranjeunna gaduh hiji fitur penting - aranjeunna neangan ti mimiti nepi ka ahir, nyaéta kénca-ka-katuhu atawa luhur-ka-handap dina data sumber. Pas pertandingan cocog munggaran kapanggih, pilarian eureun sarta ngan lumangsungna mimiti unsur urang kudu kapanggih.
Naon anu kudu dilakukeun upami urang kedah milarian sanés anu munggaran, tapi anu terakhir? Contona, urus panungtungan pikeun klien nu, pembayaran panungtungan, urutan panganyarna, jsb?
Métode 1: Milarian Baris Terakhir sareng Rumus Array
Upami tabel aslina henteu ngagaduhan kolom sareng tanggal atanapi nomer séri barisan (urutan, pamayaran ...), maka tugas urang nyaéta, kanyataanna, milarian baris terakhir anu nyugemakeun kaayaan anu dipasihkeun. Ieu tiasa dilakukeun nganggo rumus array ieu:
Ieuh:
- fungsi IF (lamun) mariksa sadaya sél dina kolom hiji-hiji langganan tur mintonkeun nomer garis lamun ngandung ngaran urang kudu. Jumlah garis dina lambaran dibikeun ka urang ku fungsi garis (JARINGAN), Tapi saprak urang kudu nomer baris dina tabél, urang Sajaba kudu subtract 1, sabab urang boga lulugu dina tabél.
- Lajeng fungsi MAX (MAX) milih nilai maksimum ti set kabentuk tina angka baris, ie jumlah garis panganyarna tina klien nu.
- fungsi Indéks (INDEKS) mulihkeun eusi sél jeung nomer panungtungan kapanggih tina sagala kolom tabel diperlukeun séjén (Kode pesenan).
Sadaya ieu kudu diasupkeun salaku rumus susunan, nyaéta:
- Dina Office 365 sareng apdet panganyarna dipasang sareng ngadukung arrays dinamis, anjeun tiasa pencét asup.
- Dina sadaya vérsi anu sanés, saatos ngalebetkeun rumus, anjeun kedah pencét potong kompas keyboard Ctrl+ngarobah+asup, anu otomatis bakal nambihan kurung keriting kana éta dina bar rumus.
Métode 2: Balikkeun lookup sareng fungsi LOOKUP énggal
Kuring parantos nyerat tulisan panjang kalayan pidéo ngeunaan fitur énggal Témbongkeun (XLOOKUP), nu mucunghul dina versi panganyarna tina Office pikeun ngaganti VLOOKUP heubeul (VLOOKUP). Kalayan bantuan BROWSE, tugas urang direngsekeun rada elementarily, sabab. pikeun fungsi ieu (teu kawas VLOOKUP), anjeun tiasa sacara eksplisit nyetel arah pilarian: luhur-handap atawa handap-up - argumen panungtungan na (-1) jawab ieu:
Métode 3. Milarian string sareng tanggal panganyarna
Upami dina sumber data urang ngagaduhan kolom kalayan nomer séri atanapi tanggal anu maénkeun peran anu sami, maka tugasna dirobih - urang kedah milarian sanés garis anu terakhir (panghandapna) sareng pertandingan, tapi garis anu paling anyar ( maksimum) titimangsa.
Kuring geus dibahas di jéntré kumaha ngalakukeun ieu ngagunakeun fungsi klasik, tur ayeuna hayu urang cobaan ngagunakeun kakuatan fungsi Asép Sunandar Sunarya dinamis anyar. Pikeun kageulisan tur genah gede, urang ogé ngarobah tabel aslina kana tabel "pinter" ngagunakeun potong kompas keyboard Ctrl+T atawa paréntah Imah - Format salaku méja (Imah - Format salaku Méja).
Kalayan bantosanana, "pasangan pembunuh" ieu ngarengsekeun masalah urang kalayan anggun:
Ieuh:
- Fungsi heula saringan (FILTER) milih ngan maranéhanana baris ti tabel kami dimana dina kolom langganan – ngaran urang kudu.
- Lajeng fungsi kelas (SORT) sorts baris dipilih dumasar titimangsa dina urutan nurun, jeung deal panganyarna di luhur.
- fungsi Indéks (INDEKS) extracts baris kahiji, ie mulih dagang panungtungan urang kudu.
- Sareng, tungtungna, fungsi FILTER éksternal ngaleungitkeun kolom ka-1 sareng ka-3 tambahan tina hasil (Kode pesenan и langganan) jeung ngan ninggalkeun tanggal jeung jumlah. Pikeun ieu, hiji Asép Sunandar Sunarya konstanta dipaké. {0;1;0;1}, nangtukeun mana kolom kami hoyong (1) atanapi teu hayang (0) pikeun nembongkeun.
Métode 4: Milarian Pertandingan Terakhir dina Power Query
Nya, demi kasampurnaan, hayu urang tingali solusi pikeun masalah panéangan sabalikna urang nganggo tambihan Power Query. Kalayan pitulungna, sadayana direngsekeun gancang pisan sareng saé.
1. Hayu urang ngarobih tabel asli urang janten "pinter" nganggo potong kompas keyboard Ctrl+T atawa paréntah Imah - Format salaku méja (Imah - Format salaku Méja).
2. Muatkeun kana Power Query nganggo tombol Ti Table / Rentang tab data (Data — Tina Tabél/Rentang).
3. Urang nyortir (ngaliwatan daptar turun-handap tina filter dina lulugu) tabel kami dina urutan nurun tina tanggal, ku kituna transaksi panganyarna aya di luhur.
4… Dina tab transformasi milih tim Kelompok ku (Transformasi - Grup Ku) tur nyetel grup ku konsumén, tur salaku fungsi aggregating, pilih pilihan Kabéh garis (Sadaya baris). Anjeun tiasa ngaranan kolom anyar naon anjeun resep - contona rincian.
Saatos ngagolongkeun, kami bakal nampi daptar nami unik klien kami sareng dina kolom rincian - tabél sareng sadaya transaksi masing-masing, dimana baris kahiji bakal janten transaksi panganyarna, anu kami peryogikeun:
5. Tambahkeun kolom diitung anyar jeung tombol kolom custom tab Tambihkeun kolom (Tambahkeun kolom - Tambihkeun kolom khusus)sareng lebetkeun rumus ieu:
di dieu rincian - ieu kolom ti mana urang nyandak tabel ku konsumén, jeung 0 {} nyaéta jumlah baris anu urang hoyong nimba (panomeran baris dina Power Query dimimitian ti enol). Kami nampi kolom kalayan rékaman (catetan), dimana unggal éntri mangrupikeun baris kahiji tina unggal méja:
Tetep dilegakeun eusi sadaya rékaman kalayan tombol sareng panah ganda dina lulugu kolom deal panungtungan milih kolom anu dipikahoyong:
... teras pupus kolom anu henteu diperyogikeun deui rincian ku ngaklik katuhu dina judulna - Leupaskeun kolom (Leupaskeun kolom).
Sanggeus unggah hasilna kana lambaran ngaliwatan Imah - Tutup sareng beban - Tutup sareng muatkeun (Imah - Tutup & Muat - Tutup & Muat ka…) kami bakal kéngingkeun méja anu saé kalayan daptar transaksi panganyarna, sakumaha anu dipikahoyong:
Lamun anjeun ngarobah data sumber, anjeun teu kudu poho pikeun ngapdet hasil ku ngaklik katuhu dina eta - paréntah Apdet & Simpen (Refresh) atawa potong kompas keyboard Ctrl+Alt+F5.
- Fungsi LOOKUP mangrupikeun turunan VLOOKUP
- Kumaha ngagunakeun fungsi array dinamis anyar SORT, FILTER, sareng UNIC
- Manggihan sél non-kosong panungtungan dina baris atawa kolom jeung fungsi LOOKUP