seychellesartprojects.org

Top 10 uzlaboto Excel formulu un funkciju saraksts

Šo Advanced Excel Formulas veidni varat lejupielādēt šeit - Advanced Excel Formulas Template

# 1 - VLOOKUP formula programmā Excel

Šī uzlabotā Excel funkcija ir viena no visbiežāk izmantotajām formulas programmā Excel. Tas galvenokārt ir saistīts ar šīs formulas vienkāršību un tās izmantošanu, meklējot noteiktu vērtību no citām tabulām, kurām šajās tabulās ir viens kopīgs mainīgais. Pieņemsim, ka jums ir divas tabulas, kurās ir detalizēta informācija par uzņēmuma darbinieku algu un vārdu, un galvenā sleja ir darbinieka ID. Jūs vēlaties saņemt algu no A tabulas B tabulas.

Varat izmantot VLOOKUP, kā norādīts zemāk.

Tas tiks iegūts zemāk esošajā tabulā, kad mēs izmantosim šo uzlaboto Excel formulu citās kolonnas Darbinieku alga šūnās.

Velciet formulu uz pārējām šūnām.

VLOOKUP ir trīs galvenie norobežojumi:

1. Kolonnas labajā pusē nevar būt primārā kolonna, kuras vērtību vēlaties aizpildīt no citas tabulas. Šajā gadījumā sleja Darbinieku alga nevar būt pirms Darbinieka ID.
2. B tabulas primārajā kolonnā dublētu vērtību gadījumā pirmā vērtība tiks aizpildīta šūnā.
3. Ja datubāzē ievietojat jaunu kolonnu (Ex B ievietojiet jaunu kolonnu pirms darbinieku algas B tabulā), formulas izeja var būt atšķirīga, pamatojoties uz pozīciju, kuru esat pieminējis formulā (iepriekšminētajā gadījumā būtu tukšs)

# 2 - INDEX formula Excel

Šo uzlaboto Excel formulu izmanto, lai iegūtu šūnas vērtību dotajā tabulā, norādot rindu, kolonnu vai abu skaitu. Piem. Lai iegūtu darbinieka vārdu 5. novērojumā, zemāk ir dati.

Mēs varam izmantot uzlaboto Excel formulu, kā norādīts zemāk:

To pašu INDEX formulu var izmantot, lai iegūtu vērtību gar rindu. Izmantojot gan rindas, gan kolonnas numuru, sintakse izskatās apmēram šādi:

Iepriekš minētā formula atgriezīs “Rajesh Ved”.

Piezīme. Ja 5. rindā esošajos datos ievietojat citu rindu, formula atgriezīs “Chandan Kale”. Tādējādi produkcija būtu atkarīga no izmaiņām, kas laika gaitā notiek datu tabulā.

# 3 - MATCH formula Excel

Šī Excel uzlabotā formula atgriež rindas vai kolonnas numuru, ja norādītajā diapazonā ir noteikta virkne vai skaitlis. Zemāk esošajā piemērā mēs mēģinām atrast “Rajesh Ved” pozīciju slejā Darbinieka vārds.

Formula būtu šāda:

Funkcija MATCH atgriezīs vērtību 5.

Precīzai atbilstībai tiek izmantots 3. arguments. Varat arī izmantot +1 un -1, pamatojoties uz savām prasībām.

Piezīme: Lai pārvarētu VLOOKUP ierobežojumus, var apvienot INDEX un MATCH.

# 4 - IF UN Formula programmā Excel

Ir daudz gadījumu, kad nepieciešams izveidot karodziņus, pamatojoties uz dažiem ierobežojumiem. Mēs visi esam pazīstami ar IF pamata sintaksi. Mēs izmantojam šo uzlaboto Excel IF funkciju, lai izveidotu jaunu lauku, pamatojoties uz zināmu ierobežojumu jau esošam laukam. Bet ko tad, ja, veidojot karodziņu, ir jāizmanto vairākas kolonnas. Piem. Šajā gadījumā mēs vēlamies atzīmēt visus darbiniekus, kuru alga ir lielāka par 50 000, bet darbinieka ID ir lielāka par 3.

Šādos gadījumos mēs izmantotu IF UN. Lūdzu, zemāk atrodiet to pašu ekrānuzņēmumu.

Tas atgrieztu rezultātu kā 0.

Mums var būt daudz nosacījumu vai ierobežojumu, lai izveidotu karodziņu, pamatojoties uz vairākām kolonnām, izmantojot AND.

# 5 - JA VAI formula Excel

Līdzīgi mēs varam arī izmantot OR funkciju Excel, nevis AND, ja mums vienkārši jāatbilst vienam no daudzu nosacījumiem.

Iepriekš minētajos gadījumos, ja kāds no nosacījumiem tiks izpildīts, šūna tiks aizpildīta kā 1 cits 0. Mēs varam arī aizstāt 1 vai 0 ar dažiem apakšvirsrakstiem ar divkāršām pēdiņām (“”).

# 6 - SUMIF formula programmā Excel

Dažās analīzēs, lietojot summas vai skaitīšanas funkciju, jums, iespējams, būs jāfiltrē daži novērojumi. Šādos gadījumos mums palīdz šī uzlabotā excel SUMIF funkcija programmā Excel. Tas filtrē visus novērojumus, pamatojoties uz noteiktiem nosacījumiem, kas norādīti šajā uzlabotajā Excel formulā, un tos apkopo. Piem. Ko darīt, ja mēs vēlamies uzzināt tikai to darbinieku algu summu, kuru darbinieka ID ir lielāks par 3.

Lietojot SUMIFS formulu:

Formula atgriež rezultātus kā 322000.

Varam arī uzskaitīt darbinieku skaitu organizācijā, kuru darbinieka ID ir lielāks par 3, ja SUMIF vietā izmantojam COUNTIF.

# 7 - Formulas pievienošana programmā Excel

Šī Excel uzlabotā funkcija ir viena no formulām, ko var izmantot ar vairākiem variantiem. Šī uzlabotā Excel formula palīdz mums apvienot vairākas teksta virknes vienā teksta virknē. Piemēram, ja mēs vēlamies vienā kolonnā parādīt darbinieka ID un darbinieka vārdu.

Lai to izdarītu, mēs varam izmantot šo CONCATENATE formulu.

Iepriekš minētās formulas rezultāts būs “1Aman Gupta”.

Mums var būt vēl viens variants, ievietojot vienu defisi starp ID un NAME. Piem. CONCATENATE (B3, ”-“, C3) rezultāts būs “1-Aman Gupta”. Mēs to varam izmantot arī VLOOKUP, ja LOOKUP Excel vērtībā ir vairāk nekā viena mainīgā maisījums.

# 8 - LEFT, MID un RIGHT Formula programmā Excel

Mēs varam izmantot šo uzlaboto Excel formulu, ja no konkrētās virknes vēlamies iegūt noteiktu apakšvirkni. Minētās formulas varētu izmantot, pamatojoties uz mūsu prasībām. Piem. Ja mēs vēlamies iegūt pirmās 5 rakstzīmes no darbinieka vārda, mēs varam izmantot kreiso formulu programmā Excel ar kolonnas nosaukumu un otro parametru kā 5.

Rezultāts ir norādīts zemāk:

Formulas RIGHT piemērošana arī Excel ir vienāda, vienkārši mēs skatītos uz rakstzīmi no virknes labās puses. Tomēr, ja programmā Excel ir MID funkcija, mums jānorāda vajadzīgās teksta virknes sākuma pozīcija un virknes garums.

# 9 - OFFSET formula programmā Excel

Šī uzlabotā Excel funkcija ar citu funkciju kombināciju, piemēram, SUM vai AVERAGE, varētu būt noderīga, dinamiski pieskaroties aprēķiniem. Vislabāk to izmantot gadījumos, kad esošajā datu bāzē mēs ievietojam nepārtrauktas rindas. OFFSET Excel dod mums diapazonu, kur mums jāpiemin atsauces šūna, rindu skaits un kolonnas. Piem. Ja mēs vēlamies aprēķināt vidējo pirmo 5 darbinieku skaitu uzņēmumā, kurā mums ir darbinieku alga, kas sakārtota pēc darbinieka ID, mēs varam rīkoties šādi. Šis aprēķins vienmēr dos mums algu.

• Tas mums dos pirmo 5 darbinieku algu summu.

# 10 - TRIM formula programmā Excel

Šī uzlabotā Excel formula tiek izmantota, lai attīrītu no teksta nesvarīgās atstarpes. Piem. Ja mēs vēlamies noņemt atstarpes kāda nosaukuma sākumā, mēs to varam izmantot, izmantojot Excel funkciju TRIM, kā norādīts zemāk:

Rezultāts būtu “Chandan Kale” bez vietas pirms Chandan.