Vai jums ir Excel formula, kas ligzdo vairākas IF funkcijas? Kad esat nonācis līdz punktam ar pārāk daudziem ligzdotiem IF paziņojumiem, jums jāpārliecinās, vai visa lieta kļūs vienkāršāka, izmantojot vienkāršu funkciju VLOOKUP. Esmu redzējis, ka 1000 rakstzīmju formulas ir vienkāršotas līdz 30 rakstzīmju VLOOKUP formulām. To ir viegli uzturēt, kad vēlāk jāpievieno jaunas vērtības.
Jau sen strādāju pie pārdošanas viceprezidenta darbā. Es vienmēr modelēju kādu jaunu prēmiju programmu vai komisijas plānu. Es diezgan pieradu pie plānu pasūtīšanas ar visādiem nosacījumiem. Zemāk redzamais ir diezgan pieradis.
Normāla pieeja ir sākt veidot ligzdotu IF formulu. Jūs vienmēr sākat vai nu ar diapazona augstāko vai zemāko galu. “Ja pārdošanas apjoms pārsniedz USD 500 000, tad atlaide ir 20%; citādi … ” Trešais IF funkcijas arguments ir pilnīgi jauna IF funkcija, kas pārbauda otro līmeni: ja pārdošanas apjoms pārsniedz 250 000 USD, tad atlaide ir 15%; citādi… ”
Šīs formulas kļūst arvien garākas, jo ir vairāk līmeņu. Šādas formulas visgrūtākais ir atcerēties, cik aizvērtās iekavas likt formulas beigās.
Mini bonusa padoms
Iekavu saskaņošana
Katram jaunajam iekavu līmenim programma Excel pārvietojas dažādās krāsās. Kaut arī Excel atkārtoti izmanto krāsas, tā melno krāsu izmanto tikai sākuma iekavās un atbilstošajās aizvērtajās iekavās. Pabeidzot zemāk esošo formulu, turpiniet ierakstīt aizvērtās iekavas, līdz ierakstāt melnas iekavas.
Atpakaļ pie ligzdotās formulas padoma
Ja izmantojat programmu Excel 2003, jūsu formula jau tuvojas ierobežojumam. Toreiz jūs nevarēja ievietot vairāk par 7 IF funkcijām. Tā bija neglīta diena, kad pilnvaras, kuras mainīja, plāno komisijas maksu, un jums bija nepieciešams veids, kā pievienot astoto IF funkciju. Šodien jūs varat ligzdot 64 IF funkcijas. Jums to nekad nevajadzētu darīt, taču ir patīkami zināt, ka nav problēmu ligzdot 8 vai 9.
Tā vietā, lai izmantotu ligzdoto IF funkciju, mēģiniet izmantot neparasto funkcijas funkciju VLOOKUP. Kad VLOOKUP ceturtais arguments mainās no False uz True, funkcija vairs nemeklē precīzu atbilstību.
Nu, vispirms VLOOKUP mēģina atrast precīzu atbilstību. Bet, ja precīza atbilstība nav atrasta, Excel ierindojas rindā tikai nedaudz mazāk par to, ko meklējat.
Apsveriet tālāk sniegto tabulu. Šūnā C13 Excel tabulā meklēs atbilstību par USD 28 355. Kad tā nevar atrast 28355, Excel atgriezīs atlaidi, kas saistīta ar vērtību, kas ir tikai mazāka. Šajā gadījumā 1% atlaide USD 10K līmenim.
Konvertējot kārtulas tabulā E13: F18, jums jāsāk no mazākā līmeņa un jādodas uz augstāko līmeni. Lai gan noteikumos tas nebija norādīts, ja kāds pārdod mazāk par 10 000 USD, atlaide būs 0%. Jums tas jāpievieno kā tabulas pirmā rinda.
Uzmanību
Kad izmantojat VLOOKUP “True” versiju, tabula ir jāšķiro augošā secībā. Daudzi cilvēki uzskata, ka visas uzmeklēšanas tabulas ir jāšķiro. Bet tabula ir jāšķiro tikai gadījumā, ja tiek veikta aptuvena spēle.
Ko darīt, ja jūsu menedžeris vēlas, lai būtu pilnīgi patstāvīga formula, un nevēlas, lai bonusa tabula būtu labajā pusē? Pēc formulas izveidošanas tabulu var iegult tieši formulā.
Novietojiet formulu rediģēšanas režīmā, veicot dubultklikšķi uz šūnas vai atlasot šūnu un nospiežot F2.
Izmantojot kursoru, atlasiet visu otro argumentu: $ E $ 13: $ F $ 18.
Nospiediet taustiņu F9. Excel iegūs meklēšanas tabulu kā masīva konstanti. Masīva konstante, semikols norāda jaunu rindu, un komats norāda jaunu kolonnu.
Nospiediet Enter. Kopējiet formulu uz citām šūnām.
Tagad jūs varat izdzēst tabulu. Galīgā formula ir parādīta zemāk.
Paldies Maikam Girvinam, kurš man mācīja par atbilstošajām iekavām. VLOOKUP tehniku ieteica Denijs Maks, Boriana Petrova, Andreass Teoss un @mvmcos.
Skatīties video
- Izmantojot daudzpakāpju komisijas, prēmiju vai atlaižu programmu, jums bieži ir jāievieto IF funkcijas
- Excel 2003 ierobežojums bija 7 ligzdoti IF paziņojumi.
- Tagad jūs varat ligzdot 32, bet es nedomāju, ka jums kādreiz vajadzētu ligzdot 32
- Kad jūs kādreiz izmantojat aptuveno VLOOKUP atbilstības versiju? Šis ir laiks.
- Tulkojiet atlaižu programmu uzmeklēšanas tabulā
- VLOOKUP vairumā gadījumu neatradīs atbildi.
- Liekot, True beigās liks VLOOKUP atrast vērtību tikai nedaudz.
- Šī ir vienīgā reize, kad VLOOKUP tabula ir jāšķiro.
- Vai nevēlaties, lai VLOOKUP tabula būtu malā? Iegult to formulā.
- F2, lai rediģētu formulu. Atlasiet uzmeklēšanas tabulu. Nospiediet F9. Enter.
Video atšifrējums
Uzziniet programmu Excel no podcast, 2030. sērijas - Nested IF aizstāj ar VLOOKUP!
Apraidīšu visu šo grāmatu, augšējā labajā stūrī noklikšķiniet uz “i”, lai nokļūtu atskaņošanas sarakstā!
Sveiki, sveicināti atkal netcast ēterā, es esmu Bils Jelens. Labi, tas patiešām ir izplatīts vai nu ar komisijas programmu, vai ar atlaižu programmu, vai ar prēmiju programmu, kur mums ir līmeņi, dažādi līmeņi, vai ne? Ja jūs pārsniedzat 10000 USD, jūs saņemat 1% atlaidi, 50000 USD 5% atlaidi. Veidojot šo ligzdoto IF priekšrakstu, jums jābūt uzmanīgam, ja to meklējat lielākam par, ja sākat to augšējā galā, vai apakšējā galā, ja meklējat mazāk nekā. Tātad B10> 50000, 20%, citādi cits IF, B10> 250000, 25% utt. Šī ir tikai gara un sarežģīta formula, un, ja jūsu tabula ir lielāka, izmantojot programmu Excel 2003, jūs nevarētu ligzdot vairāk nekā 7 IF paziņojumus, mēs šeit gandrīz esam tuvu ierobežojumam. Tagad jūs varat pāriet uz 32, es nedomāju, ka jums kādreiz vajadzētu iet uz 32, un pat tad, ja jūs kliedzat: "Hei, pagaidi,kā ar jauno funkciju, kas tikko parādījās Excel 2016, 2016. gada februāra laidienā, ar IFS funkciju? ” Jā, protams, šeit ir mazāk iekavas, un 87 rakstzīmes, nevis 97 rakstzīmes, tas joprojām ir haoss, atbrīvosimies no tā un darīsim to ar VLOOKUP!
Labi, šeit ir norādīti soļi, jūs ievērojat šos noteikumus un pagriežat tos uz viņu galvas. Pirmais, kas mums jāsaka, ir tas, ka, ja jums bija 0 ASV dolāru pārdošanas, jūs saņemat 0% atlaidi, ja jūs pārdodat 10000 ASV dolāru, jūs saņemat 1% atlaidi, ja jums ir 500 000 ASV dolāru pārdošanas, jūs saņemat 5% atlaidi . 100000 USD, 10% atlaide, 250000 USD, 15% atlaide un, visbeidzot, viss, kas pārsniedz 500 000 USD, saņem 20% atlaidi, labi. Tagad daudzas reizes, katru reizi, kad es runāju par VLOOKUP, es saku, ka katrs jūsu izveidots VLOOKUP beigsies ar FALSE, un cilvēki teiks: "Nu pagaidiet sekundi, kādēļ tur ir PATIESA versija?" Tas ir tieši šim gadījumam, kad mēs meklējam diapazonu, tāpēc mēs meklējam šo vērtību, protams, regulāri VLOOKUP, 2, FALSE neatradīs 550000, atgriezīs # N / A!Tāpēc šajā ļoti īpašajā gadījumā mēs mainīsim FALSE uz TRUE, un tas Excel pateiks: "Ejiet meklēt 550000, ja to nevarat atrast, dodiet mums vērtību, kas ir tikai mazāka." Tātad tas mums dod 20%, tas ir tas, ko dara, labi? Un šī ir vienīgā reize, kad jūsu VLOOKUP tabula ir jākārto, visas pārējās reizes ar FALSE, tā var būt, kā vēlaties. Un jā, jūs varētu atstāt, TRUE izslēgtu, bet es vienmēr to ievietoju, lai tikai sev atgādinātu, ka šis ir viens no šiem dīvainajiem, neticami bīstamajiem VLOOKUP, un es nevēlos šo formulu kopēt kaut kur citur. Labi, tāpēc mēs kopēsim un ielīmēsim īpašās formulas, labi.labi? Un šī ir vienīgā reize, kad jūsu VLOOKUP tabula ir jākārto, visas pārējās reizes ar FALSE, tā var būt, kā vēlaties. Un jā, jūs varētu atstāt, TRUE izslēgtu, bet es vienmēr to ievietoju, lai tikai sev atgādinātu, ka šis ir viens no šiem dīvainajiem, neticami bīstamajiem VLOOKUP, un es nevēlos kopēt šo formulu kaut kur citur. Labi, tāpēc mēs kopēsim un ielīmēsim īpašās formulas, labi.labi? Un šī ir vienīgā reize, kad jūsu VLOOKUP tabula ir jāsakārto, visas pārējās reizes ar FALSE, tā var būt, kā vēlaties. Un jā, jūs varētu atstāt, TRUE izslēgtu, bet es vienmēr to ievietoju, lai tikai sev atgādinātu, ka šis ir viens no šiem dīvainajiem, neticami bīstamajiem VLOOKUP, un es nevēlos kopēt šo formulu kaut kur citur. Labi, tāpēc mēs kopēsim un ielīmēsim īpašās formulas, labi.
Nākamā sūdzība: jūsu menedžeris nevēlas redzēt uzmeklēšanas tabulu, bet to vēlas “Vienkārši atbrīvojieties no šīs uzmeklēšanas tabulas”. Labi, mēs varam to izdarīt, iegulstot uzmeklēšanas tabulu, pārbaudiet šo lielisko triku, ko es saņēmu no Maika Girvina vietnē ExcelIsFun. F2, lai ievietotu formulu rediģēšanas režīmā, un pēc tam ļoti uzmanīgi atlasiet tikai uzmeklēšanas tabulas diapazonu. Nospiediet F9, un tā aizņem šo tabulu un ievieto to masīvu nomenklatūrā, un tad es vienkārši nospiežu Enter tāpat. Nokopējiet to uz leju, Ielīmējiet īpašās formulas, un tad es varu brīvi atbrīvoties no uzmeklēšanas tabulas, kas viss turpina darboties rindā. Tas ir milzīgs apgrūtinājums, ja jums ir jāatgriežas, lai to rediģētu, jums tas patiešām ir jāskatās ar lielu skaidrību. Komats nozīmē, ka mēs ejam uz nākamo kolonnu, ar semikolu nozīmē, ka mēs ejam uz nākamo rindu, labi,bet teorētiski jūs varētu atgriezties tur un mainīt šo formulu, ja mainās kāds no ķēdes numuriem.
Labi, tāpēc, izmantojot ligzdoto IF paziņojumu, VLOOKUP ir padoms Nr. 32, ejot uz 40, “40 visu laiku lieliskākie Excel padomi”, jums var būt visa šī grāmata. Noklikšķiniet uz “i” augšējā labajā stūrī, 10 USD par e-grāmatu, 25 USD par drukāto grāmatu, labi. Tāpēc šodien mēs cenšamies atrisināt daudzpakāpju komisijas prēmiju jeb atlaižu programmu. Ligzdotie IF ir patiešām izplatīti, uzmanieties, 7 ir viss, kas jums var būt programmā Excel 2003, šodien jums var būt 32, bet jums nekad nevajadzētu būt 32. Tātad, kad jāizmanto aptuvenā VLOOKUP MATCH versija, tas ir viss. Paņemiet šo atlaižu programmu, pagrieziet to otrādi, izveidojiet to uzmeklēšanas tabulā, sākot ar mazāko skaitli un dodoties uz lielāko skaitli. VLOOKUP, protams, neatradīs atbildi, taču, mainot VLOOKUP uz, TRUE beigās, tas liks tai atrast vērtību mazāk,šī ir vienīgā reize, kad tabula ir jāšķiro. Ja jūs nevēlaties redzēt šo tabulu, varat to iegult formulā, izmantojot Mike Girvin triku, F2, lai rediģētu formulu, atlasiet uzmeklēšanas tabulu, nospiediet F9 un pēc tam Enter.
Labi, hei, es gribu pateikties, ka apstājies, mēs tiksimies nākamreiz uz citu netcast no!
Lejupielādēt failu
Lejupielādējiet faila paraugu šeit: Podcast2030.xlsx
