Manggihan nomer pangdeukeutna

Dina prakna, sering pisan aya kasus nalika anjeun sareng kuring kedah milarian nilai anu pangdeukeutna dina set (méja) dina hubungan sareng nomer anu dipasihkeun. Bisa jadi, contona:

• Itungan diskon gumantung kana volume.
  
• Itungan jumlah bonus gumantung kana palaksanaan rencana.
  
• Itungan ongkos pengiriman barang gumantung kana jarak.
• Pamilihan wadah anu cocog pikeun barang, jsb.

Leuwih ti éta, rounding bisa jadi diperlukeun duanana luhur jeung ka handap, gumantung kana kaayaan.

Aya sababaraha cara - écés sareng henteu écés - pikeun ngabéréskeun masalah sapertos kitu. Hayu urang nempo aranjeunna sequentially.

Pikeun mimitian ku, hayu urang ngabayangkeun supplier anu masihan diskon on borongan, sarta persentase diskon gumantung kana kuantitas barang dibeuli. Salaku conto, nalika ngagaleuh langkung ti 5 potongan, diskon 2% dipasihkeun, sareng nalika ngagaleuh tina 20 potongan - parantos 6%, jsb.

Kumaha gancang sareng éndah ngitung persentase diskon nalika ngalebetkeun kuantitas barang anu dipésér?

Métode 1: IFs Nested

Métode tina séri "naon anu dipikiran - anjeun kedah luncat!". Ngagunakeun fungsi nested IF (lamun) pikeun sequentially mariksa lamun nilai sél ragrag kana unggal interval jeung nembongkeun diskon pikeun rentang pakait. Tapi rumus dina hal ieu tiasa janten pajeulit: 

Jigana écés yén debugging sapertos "boneka monster" atanapi nyobian nambihan sababaraha kaayaan énggal saatos sababaraha waktos anu pikaresepeun.

Sajaba ti éta, Microsoft Excel boga wates nyarang pikeun fungsi IF - 7 kali dina versi heubeul jeung 64 kali dina versi anyar. Kumaha upami anjeun peryogi langkung?

Métode 2. VLOOKUP kalawan tempoan interval

Metoda ieu leuwih kompak. Pikeun ngitung persentase diskon, nganggo fungsi legendaris VPR (VLOOKUP) dina modeu pilarian perkiraan:

di mana

• B4 - nilai kuantitas barang dina transaksi munggaran nu urang keur pilari diskon
  
• $G$4:$H$8 - tautan kana tabel diskon - tanpa "header" sareng alamatna dibereskeun sareng tanda $.
  
• 2 - jumlah ordinal kolom dina tabel diskon ti mana urang hayang meunang nilai diskon
• BENER - ieu dimana "anjing" dikubur. Lamun salaku argumen fungsi panungtungan VPR nangtukeun bohong (PALSU) atawa 0, teras fungsina bakal milarian patandingan ketat dina kolom kuantitas (jeung bisi urang bakal masihan kasalahan #N / ​​A, saprak euweuh nilai 49 dina tabel diskon). Tapi lamun gantina bohong nulis BENER (BENER) atawa 1, lajeng fungsi bakal néangan teu pasti, tapi pangleutikna pangdeukeutna nilai na bakal masihan kami persentase diskon kami butuh.

The downside tina metoda ieu kudu nyortir tabel diskon dina urutan naek ku kolom kahiji. Upami teu aya asihan sapertos kitu (atanapi dilakukeun dina urutan sabalikna), maka rumus kami moal jalan:

Sasuai, pendekatan ieu ngan bisa dipaké pikeun manggihan nilai pangleutikna pangcaketna. Upami anjeun kedah milarian anu pangcaketna anu panggedéna, maka anjeun kedah nganggo pendekatan anu béda.

Métode 3. Manggihan pangcaketna panggedena ngagunakeun fungsi INDEX jeung MATCH

Ayeuna hayu urang tingali masalah urang ti sisi séjén. Anggap urang ngajual sababaraha model pompa industri rupa-rupa kapasitas. Méja penjualan di kénca nunjukkeun kakuatan anu diperyogikeun ku palanggan. Urang kudu milih hiji pompa tina kakuatan maksimum atawa sarua pangcaketna, tapi teu kirang ti naon anu diperlukeun ku proyék.

Fungsi VLOOKUP moal ngabantosan di dieu, janten anjeun kedah nganggo analogna - sakumpulan fungsi INDEX (INDEKS) jeung LEUWIH DIBUKA (PERTANDINGAN):

Di dieu, fungsi MATCH jeung argumen panungtungan -1 jalan dina modeu manggihan nilai pangcaketna pangcaketna, sarta fungsi INDEX lajeng extracts nami model urang kudu ti kolom padeukeut.

Métode 4. Némbongkeun fungsi anyar (XLOOKUP)

Upami Anjeun gaduh versi Office 365 sareng sadaya apdet dipasang, teras tinimbang VLOOKUP (VLOOKUP) anjeun tiasa nganggo analogna - fungsi VIEW (XLOOKUP), anu ku kuring parantos dianalisis sacara rinci:

Ieuh:

• B4 – nilai awal kuantitas produk nu urang keur pilari diskon
• $G$4:$G$8 – rentang dimana urang keur pilari patandingan
  
• $H$4:$H$8 - rentang hasil nu Anjeun hoyong balikkeun diskon
• argumen kaopat (-1) kalebet milarian nomer pangleutikna pangcaketna anu kami pikahoyong tibatan pertandingan anu pasti.
  

Kaunggulan tina metoda ieu nyaeta teu kudu nyortir tabel diskon jeung kamampuhan pikeun neangan, upami diperlukeun, teu ngan pangcaketna pangleutikna, tapi ogé pangcaketna nilai panggedena. Argumen terakhir dina hal ieu bakal 1.

Tapi, hanjakalna, teu sadaya jelema boga fitur ieu - ngan boga senang tina Office 365.

Métode 5. Power Query

Upami anjeun henteu acan wawuh sareng tambihan Power Query anu kuat sareng lengkep gratis pikeun Excel, teras anjeun ka dieu. Upami anjeun parantos wawuh, teras cobian nganggo éta pikeun ngarengsekeun masalah urang.

Hayu urang ngalakukeun sababaraha pagawéan préparasi heula:

1. Hayu urang ngarobah tabel sumber urang kana dinamis (pinter) ngagunakeun potong kompas keyboard Ctrl+T atawa tim Imah - Format salaku méja (Imah - Format salaku Méja).
2. Pikeun kajelasan, hayu urang masihan aranjeunna nami. penjualan и diskon tab Constructor (Desain).
3. Muat unggal tabel dina gilirannana kana Power Query nganggo tombol Ti Table / Rentang tab data (Data - Tina tabel/rentang). Dina versi panganyarna tina Excel, tombol ieu geus diganti jadi Kalayan daun (Ti lambaran).
   
4. Upami tabél gaduh nami kolom anu béda sareng jumlah, sapertos dina conto urang ("Jumlah barang" sareng "Jumlah tina ..."), maka aranjeunna kedah diganti nami dina Power Query sareng namina sami.
5. Saatos éta, anjeun tiasa uih deui ka Excel ku milih paréntah dina jandela pangropéa Power Query Imah - Tutup sareng Muatan - Tutup sareng Muat dina… (Imah — Tutup&Muat — Tutup&Muat ka…) lajeng pilihan Ngan nyieun sambungan (Ngan nyieun sambungan).

   
   

6. Lajeng paling metot dimimitian. Upami Anjeun gaduh pangalaman dina Power Query, teras kuring nganggap yén garis salajengna pamikiran kudu di arah merging dua tabel ieu kalawan query gabung (ngagabung) a la VLOOKUP, sakumaha anu kasebut dina metoda saméméhna. Nyatana, urang kedah ngagabung dina modeu tambihan, anu henteu écés dina pandangan anu kahiji. Pilih dina tab Excel Data - Kéngingkeun Data - Gabungkeun Paménta - Tambihkeun (Data - Kéngingkeun Data - Gabungkeun patarosan - Nambihkeun) lajeng tabel kami penjualan и diskon dina jandela nu nembongan:

   

7. Sanggeus ngaklik OK tabél urang bakal glued kana sakabeh tunggal - handapeun silih. Punten dicatet yén kolom sareng kuantitas barang dina tabel ieu murag dina silih, sabab. aranjeunna gaduh nami anu sami:

   

8. Upami sekuen asli barisan dina tabel penjualan penting pikeun anjeun, teras saatos sadayana transformasi anu salajengna anjeun tiasa ngabalikeun deui, tambahkeun kolom wilanganana kana méja kami nganggo paréntah. Nambahkeun Kolom - Kolom Indéks (Tambahkeun kolom - Kolom indéks). Upami sekuen garis henteu masalah pikeun anjeun, maka anjeun tiasa ngalangkungan léngkah ieu.
9. Ayeuna, ngagunakeun daptar turun-handap dina lulugu tabel, nyortir eta dumasar kolom kuantitas Naek:

   

10. Jeung trik utama:-klik katuhu dina lulugu kolom potongan harga milih tim Eusian - Turun (Eusian - Turun). Sél kosong kalawan null otomatis dieusian ku nilai diskon saméméhna:

    

11. Tetep mulangkeun sekuen asli baris ku asihan dumasar kolom daptar eusi (anjeun tiasa aman ngahapus engké) sareng nyingkirkeun garis anu teu perlu sareng saringan null ku kolom Kodeu transaksi:

    
    

• Nganggo pungsi VLOOKUP pikeun milarian sareng milarian data
• Ngagunakeun VLOOKUP (VLOOKUP) nyaeta case-sensitive
• VLOOKUP XNUMXD (VLOOKUP)