Paragraf
Ing laku, asring banget ana kasus nalika sampeyan lan aku kudu nemokake nilai paling cedhak ing pesawat (tabel) ing hubungan kanggo nomer tartamtu. Bisa uga, contone:
- Pitungan diskon gumantung volume.
- Pitungan saka jumlah bonus gumantung ing implementasine saka rencana.
- Pitungan tarif pengiriman gumantung saka jarak.
- Pamilihan wadhah sing cocog kanggo barang, lsp.
Kajaba iku, bunder bisa uga dibutuhake munggah lan mudhun, gumantung saka kahanan.
Ana sawetara cara - ketok lan ora ketok - kanggo ngatasi masalah kasebut. Ayo padha katon ing urutan.
Kanggo miwiti, ayo bayangake supplier sing menehi diskon ing grosir, lan persentase diskon gumantung saka jumlah barang sing dituku. Contone, nalika tuku luwih saka 5 potong, diskon 2% diwenehake, lan nalika tuku saka 20 potong - wis 6%, etc.
Kepiye cara ngetung persentase diskon kanthi cepet lan apik nalika ngetik jumlah barang sing dituku?
Cara 1: Nested IFs
Cara saka seri "apa sing kudu dipikirake - sampeyan kudu mlumpat!". Nggunakake fungsi nested IF (Yen) kanggo sequentially mriksa yen nilai sel tumiba ing saben interval lan nampilake diskon kanggo sawetara sing cocog. Nanging rumus ing kasus iki bisa dadi rumit banget:
Aku iku ketok sing debugging kuwi "boneka monster" utawa nyoba kanggo nambah saperangan saka kahanan anyar kanggo iku sawise sawetara wektu fun.
Kajaba iku, Microsoft Excel duwe watesan nesting kanggo fungsi IF - kaping 7 ing versi lawas lan kaping 64 ing versi anyar. Apa yen sampeyan butuh liyane?
Cara 2. VLOOKUP kanthi tampilan interval
Cara iki luwih kompak. Kanggo ngetung persentase diskon, gunakake fungsi legendaris VPR (VLOOKUP) ing mode panelusuran kira-kira:
ngendi
- B4 – Nilai saka jumlah barang ing transaksi pisanan sing kita looking for diskon
- $G$4:$H$8 - link menyang tabel diskon - tanpa "header" lan alamat tetep nganggo tandha $.
- 2 - nomer ordinal kolom ing tabel diskon saka ngendi kita arep kanggo njaluk regane diskon
- TRUE - iki ngendi "asu" disarèkaké. Yen minangka argumen fungsi pungkasan VPR nemtokake GUSTI (SALAH) utawa 0, banjur fungsi bakal nggoleki match ketat ing kolom jumlah (lan ing kasus kita bakal menehi kesalahan #N / A, amarga ora ana nilai 49 ing tabel diskon). Nanging yen tinimbang GUSTI nulis TRUE (BENER) utawa 1, banjur fungsi bakal katon ora pas, nanging paling cilik paling cedhak regane lan bakal menehi persentase diskon sing dibutuhake.
Kelemahane metode iki yaiku kudu ngurutake tabel diskon kanthi urutan munggah miturut kolom pisanan. Yen ora ana ngurutake (utawa wis rampung ing urutan mbalikke), banjur rumus kita ora bakal bisa:
Mulane, pendekatan iki mung bisa digunakake kanggo nemokake nilai paling cilik sing paling cedhak. Yen sampeyan kudu nemokake sing paling cedhak, sampeyan kudu nggunakake pendekatan sing beda.
Cara 3. Nggoleki sing paling cedhak nggunakake fungsi INDEX lan MATCH
Saiki ayo ndeleng masalah kita saka sisih liyane. Upaminipun kita adol sawetara model pompa industri saka macem-macem kapasitas. Tabel sales ing sisih kiwa nuduhake daya sing dibutuhake dening pelanggan. Kita kudu milih pompa sing paling cedhak utawa daya sing padha, nanging ora kurang saka sing dibutuhake proyek kasebut.
Fungsi VLOOKUP ora bakal mbantu ing kene, mula sampeyan kudu nggunakake analoge - akeh fungsi INDEX (INDEX) lan MORE EXPOSED (PERTANDINGAN):
Ing kene, fungsi MATCH karo argumen pungkasan -1 bisa digunakake ing mode nemokake nilai paling cedhak, lan fungsi INDEX banjur ngekstrak jeneng model sing dibutuhake saka kolom jejer.
Metode 4. New function VIEW (XLOOKUP)
Yen sampeyan duwe versi Office 365 kanthi kabeh nganyari diinstal, tinimbang VLOOKUP (VLOOKUP) sampeyan bisa nggunakake analog - fungsi VIEW (XLOOKUP), sing wis dakanalisis kanthi rinci:
Kene:
- B4 - nilai awal saka jumlah produk sing kita goleki diskon
- $G$4:$G$8 – sawetara ngendi kita looking for cocog
- $H$4:$H$8 – sawetara asil saka sing pengin bali diskon
- argumen kaping papat (-1) kalebu telusuran nomer paling cedhak sing dikarepake tinimbang sing cocog.
Kauntungan saka metode iki yaiku ora perlu ngurutake tabel diskon lan kemampuan kanggo nggoleki, yen perlu, ora mung paling cilik sing paling cedhak, nanging uga nilai paling cedhak. Argumentasi pungkasan ing kasus iki bakal dadi 1.
Nanging, sayangé, ora kabeh wong duwe fitur iki - mung sing nduweni Office 365 sing seneng.
Cara 5. Power Query
Yen sampeyan durung kenal karo tambahan Power Query sing kuat lan gratis kanggo Excel, mula sampeyan ana ing kene. Yen sampeyan wis kenal, banjur coba gunakake kanggo ngatasi masalah kita.
Ayo kerja persiapan dhisik:
- Ayo ngowahi tabel sumber dadi dinamis (pinter) nggunakake trabasan keyboard Ctrl+T utawa tim Ngarep - Format minangka tabel (Ngarep - Format minangka Tabel).
- Kanggo gamblang, ayo menehi jeneng. Sales и diskon tab Constructor (Desain).
- Muat saben tabel dadi Power Query nggunakake tombol Saka Tabel / Range tab Data (Data - Saka tabel/rentang). Ing versi paling anyar saka Excel, tombol iki wis diganti jeneng kanggo Kanthi godhong (Saka sheet).
- Yen tabel duwe jeneng kolom sing beda-beda kanthi jumlah, kaya ing conto kita ("Jumlah barang" lan "Jumlah saka ..."), mula kudu diganti jeneng ing Power Query lan dijenengi padha.
- Sawise iku, sampeyan bisa bali menyang Excel kanthi milih printah ing jendhela editor Power Query Ngarep — Nutup lan Muat — Nutup lan Muat ing… (Ngarep — Tutup&Muat — Tutup&Muat menyang…) lan banjur pilihan Mung nggawe sambungan (Mung nggawe sambungan).
- Banjur sing paling menarik diwiwiti. Yen sampeyan duwe pengalaman ing Power Query, banjur aku nganggep yen baris luwih saka pikirane kudu ing arah nggabungake loro tabel iki karo pitakonan gabung (gabungan) a la VLOOKUP, minangka kasus ing cara sadurunge. Nyatane, kita kudu nggabungake ing mode nambah, sing ora katon ing pandang sepisanan. Pilih ing tab Excel Data - Entuk Data - Gabungan Panjaluk - Tambah (Data - Entuk Data - Gabungan pitakon - Tambah) banjur meja kita Sales и diskon ing jendhela sing katon:
- Sawise ngeklik OK tabel kita bakal terpaku menyang kabèh siji - ing saben liyane. Wigati dicathet yen kolom kanthi jumlah barang ing tabel kasebut ambruk ing saben liyane, amarga. padha duwe jeneng padha:
- Yen urutan baris asli ing tabel dodolan penting kanggo sampeyan, mula sawise kabeh transformasi sabanjure sampeyan bisa mulihake, tambahake kolom nomer menyang tabel kanthi nggunakake perintah kasebut. Nambahake Kolom - Kolom Indeks (Tambah kolom - Kolom indeks). Yen urutan garis ora penting kanggo sampeyan, sampeyan bisa ngliwati langkah iki.
- Saiki, nggunakake dhaptar gulung mudhun ing header tabel, urut miturut kolom Quantity munggah:
- Lan trik utama: klik-tengen ing header kolom Discount milih tim Isi - Ngisor (Isi - Mudhun). sel kosong karo null kanthi otomatis diisi karo nilai diskon sadurunge:
- Iku tetep kanggo mulihake urutan asli saka baris dening ngurutake dening kolom Index (sampeyan bisa mbusak kanthi aman mengko) lan nyisihake garis sing ora perlu nganggo saringan null dening kolom Kode transaksi:
- Nggunakake fungsi VLOOKUP kanggo nggoleki lan nggoleki data
- Nggunakake VLOOKUP (VLOOKUP) sensitif huruf cilik
- VLOOKUP XNUMXD (VLOOKUP)