Dod o hyd i'r rhif agosaf

Cynnwys

Yn ymarferol, yn aml iawn mae yna achosion pan fydd angen i chi a minnau ddod o hyd i'r gwerth agosaf mewn set (tabl) mewn perthynas â rhif penodol. Gallai fod, er enghraifft:

Cyfrifo gostyngiad yn dibynnu ar gyfaint.
Cyfrifo swm y taliadau bonws yn dibynnu ar weithrediad y cynllun.
Cyfrifo cyfraddau cludo yn dibynnu ar y pellter.
Detholiad o gynwysyddion addas ar gyfer nwyddau, ac ati.

Ar ben hynny, efallai y bydd angen talgrynnu i fyny ac i lawr, yn dibynnu ar y sefyllfa.

Mae sawl ffordd – amlwg a ddim mor amlwg – i ddatrys problem o’r fath. Gadewch i ni edrych arnynt yn olynol.

I ddechrau, gadewch i ni ddychmygu cyflenwr sy'n rhoi gostyngiadau ar gyfanwerthu, ac mae canran y gostyngiad yn dibynnu ar faint o nwyddau a brynwyd. Er enghraifft, wrth brynu mwy na 5 darn, rhoddir gostyngiad o 2%, ac wrth brynu o 20 darn - eisoes yn 6%, ac ati.

Sut i gyfrifo canran y gostyngiad yn gyflym ac yn hyfryd wrth nodi maint y nwyddau a brynwyd?

Dull 1: IFs nythu

Dull o’r gyfres “beth sydd yna i feddwl – mae angen neidio!”. Defnyddio swyddogaethau nythu IF (OS) i wirio yn ddilyniannol a yw gwerth y gell yn disgyn i bob un o'r cyfnodau ac arddangos gostyngiad ar gyfer yr ystod gyfatebol. Ond gall y fformiwla yn yr achos hwn fod yn feichus iawn:

Dod o hyd i'r rhif agosaf

Dwi’n meddwl ei bod hi’n amlwg bod dadfygio’r fath “ddol anghenfil” neu geisio ychwanegu cwpl o amodau newydd iddo ar ôl peth amser yn hwyl.

Yn ogystal, mae gan Microsoft Excel derfyn nythu ar gyfer y swyddogaeth IF - 7 gwaith mewn fersiynau hŷn a 64 gwaith mewn fersiynau mwy newydd. Beth os oes angen mwy arnoch chi?

Dull 2. VLOOKUP gyda golygfa egwyl

Mae'r dull hwn yn llawer mwy cryno. I gyfrifo canran y gostyngiad, defnyddiwch y ffwythiant chwedlonol VPR (VLOOKUP) yn y modd chwilio bras:

Dod o hyd i'r rhif agosaf

lle

B4 – gwerth nifer y nwyddau yn y trafodiad cyntaf yr ydym yn chwilio am ddisgownt ar ei gyfer
$G$4:$H$8 – dolen i’r tabl disgownt – heb “bennawd” a’r cyfeiriadau wedi’u gosod gyda’r arwydd $.
2 — rhif trefnol y golofn yn y tabl disgownt yr ydym am gael gwerth y disgownt ohoni
TRUE – dyma lle mae'r “ci” wedi'i gladdu. Os fel y ddadl swyddogaeth olaf VPR nodwch GORWEDD (GAU) neu 0, yna bydd y swyddogaeth yn edrych am gêm gaeth yn y golofn maint (ac yn ein hachos ni bydd yn rhoi gwall # N/A, gan nad oes gwerth 49 yn y tabl disgownt). Ond os yn lle hynny GORWEDD ysgrifennu TRUE (CYWIR) neu 1, yna bydd y swyddogaeth yn edrych nid am yr union, ond lleiaf agosaf gwerth a bydd yn rhoi canran y gostyngiad sydd ei angen arnom.

Anfantais y dull hwn yw'r angen i ddidoli'r tabl disgownt mewn trefn esgynnol yn ôl y golofn gyntaf. Os nad oes didoli o'r fath (neu os caiff ei wneud mewn trefn wrthdro), ni fydd ein fformiwla yn gweithio:

Dod o hyd i'r rhif agosaf

Yn unol â hynny, dim ond i ddod o hyd i'r gwerth lleiaf agosaf y gellir defnyddio'r dull hwn. Os oes angen i chi ddod o hyd i'r mwyaf agosaf, yna mae'n rhaid i chi ddefnyddio dull gwahanol.

Dull 3. Darganfod y mwyaf agosaf gan ddefnyddio'r ffwythiannau INDEX a MATCH

Nawr, gadewch i ni edrych ar ein problem o'r ochr arall. Tybiwch ein bod yn gwerthu sawl model o bympiau diwydiannol o wahanol alluoedd. Mae'r tabl gwerthu ar y chwith yn dangos y pŵer sydd ei angen ar y cwsmer. Mae angen i ni ddewis pwmp o'r pŵer uchaf neu gyfartal agosaf, ond nid yn llai na'r hyn sy'n ofynnol gan y prosiect.

Ni fydd y swyddogaeth VLOOKUP yn helpu yma, felly bydd yn rhaid i chi ddefnyddio ei analog - criw o swyddogaethau MYNEGAI (MYNEGAI) a MWY ANGHOLIEDIG (MATCH):

Dod o hyd i'r rhif agosaf

Yma, mae'r ffwythiant MATCH gyda'r ddadl olaf -1 yn gweithio yn y modd o ddarganfod y gwerth mwyaf agosaf, ac mae'r ffwythiant MYNEGAI wedyn yn tynnu'r enw model sydd ei angen arnom o'r golofn gyfagos.

Dull 4. Swyddogaeth newydd VIEW (XLOOKUP)

Os oes gennych fersiwn o Office 365 gyda'r holl ddiweddariadau wedi'u gosod, yna yn lle VLOOKUP (VLOOKUP) gallwch ddefnyddio ei analog - y swyddogaeth VIEW (XLOOKUP), yr wyf eisoes wedi'i ddadansoddi'n fanwl:

Dod o hyd i'r rhif agosaf

yma:

B4 – gwerth cychwynnol maint y cynnyrch yr ydym yn chwilio am ddisgownt ar ei gyfer
$G$4:$G$8 – yr ystod lle rydym yn chwilio am barau
$H$4:$H$8 – yr ystod o ganlyniadau yr ydych am ddychwelyd y gostyngiad ohonynt
pedwerydd dadl (-1) yn cynnwys y chwiliad am y nifer lleiaf agosaf yr ydym ei eisiau yn lle cyfatebiaeth union.

Manteision y dull hwn yw nad oes angen didoli'r tabl disgownt a'r gallu i chwilio, os oes angen, nid yn unig y lleiaf agosaf, ond hefyd y gwerth mwyaf agosaf. Y ddadl olaf yn yr achos hwn fydd 1.

Ond, yn anffodus, nid oes gan bawb y nodwedd hon eto - dim ond perchnogion hapus Office 365.

Dull 5. Pŵer Ymholiad

Os nad ydych eto'n gyfarwydd â'r ategyn Power Query pwerus a hollol rhad ac am ddim ar gyfer Excel, yna rydych chi yma. Os ydych chi eisoes yn gyfarwydd, yna gadewch i ni geisio ei ddefnyddio i ddatrys ein problem.

Gadewch i ni wneud rhywfaint o waith paratoi yn gyntaf:

Gadewch i ni drosi ein tablau ffynhonnell i ddeinamig (clyfar) gan ddefnyddio llwybr byr bysellfwrdd Ctrl+T neu dîm Cartref - Fformat fel bwrdd (Cartref - Fformat fel Tabl).
Er eglurder, gadewch i ni roi enwau iddynt. Sales и gostyngiadau tab Constructor (Dylunio).
Llwythwch bob un o'r tablau yn eu tro i mewn i Power Query gan ddefnyddio'r botwm O'r Tabl / Ystod tab Dyddiad (Data - O'r tabl/ystod). Mewn fersiynau diweddar o Excel, mae'r botwm hwn wedi'i ailenwi i Gyda dail (O ddalen).
Os oes gan y tablau enwau colofnau gwahanol gyda meintiau, fel yn ein hesiampl (“Swm y nwyddau” a “Swm o …”), yna rhaid eu hail-enwi yn Power Query a'u henwi yr un peth.
Ar ôl hynny, gallwch ddychwelyd yn ôl i Excel trwy ddewis y gorchymyn yn ffenestr golygydd Power Query Cartref - Cau a Llwythwch - Cau a Llwythwch i mewn… (Cartref - Cau a Llwyth - Cau a Llwythwch i…) ac yna opsiwn Dim ond creu cysylltiad (Dim ond creu cysylltiad).
Yna mae'r mwyaf diddorol yn dechrau. Os oes gennych brofiad yn Power Query, yna tybiaf y dylai'r trywydd meddwl pellach fod i'r cyfeiriad o uno'r ddau dabl hyn ag ymholiad uno (uno) a la VLOOKUP, fel oedd yn wir yn y dull blaenorol. Mewn gwirionedd, bydd angen i ni uno mewn modd ychwanegu, nad yw'n amlwg o gwbl ar yr olwg gyntaf. Dewiswch yn y tab Excel Data - Cael Data - Cyfuno Ceisiadau - Ychwanegu (Data - Cael Data - Cyfuno ymholiadau - Atodi) ac yna ein byrddau Sales и gostyngiadau yn y ffenestr sy'n ymddangos:
Ar ôl clicio ar OK bydd ein byrddau'n cael eu gludo i mewn i un cyfanwaith - o dan ei gilydd. Sylwer fod y colofnau gyda swm y nwyddau yn y tablau hyn yn disgyn o dan eu gilydd, oherwydd. mae ganddyn nhw'r un enw:
Os yw'r dilyniant gwreiddiol o resi yn y tabl gwerthu yn bwysig i chi, yna fel y gallwch chi wedyn ei adfer ar ôl pob trawsnewidiad dilynol, ychwanegwch golofn wedi'i rhifo i'n tabl gan ddefnyddio'r gorchymyn Ychwanegu Colofn - Colofn Fynegai (Ychwanegu colofn - colofn fynegai). Os nad yw dilyniant y llinellau o bwys i chi, yna gallwch hepgor y cam hwn.
Nawr, gan ddefnyddio'r gwymplen ym mhennyn y tabl, trefnwch ef fesul colofn Nifer Esgynnol:
A'r prif gamp: de-gliciwch ar bennawd y golofn Disgownt dewis tîm Llenwch - I lawr (Llenwi - i lawr). Celloedd gwag gyda null llenwi'n awtomatig gyda'r gwerthoedd disgownt blaenorol:
Mae'n aros i adfer y dilyniant gwreiddiol o resi trwy ddidoli fesul colofn mynegai (gallwch ei ddileu yn ddiogel yn nes ymlaen) a chael gwared ar linellau diangen gyda hidlydd null wrth golofn Cod trafodiad: