seychellesartprojects.org

10 geriausių „Excel“ formulių ir funkcijų sąrašas

Šį „Advanced Excel Formulas“ šabloną galite atsisiųsti čia - „Advanced Excel Formulas Template“

1 - „VLOOKUP“ formulė „Excel“

Ši pažangi „Excel“ funkcija yra viena iš dažniausiai naudojamų „Excel“ formulių. Tai daugiausia dėl šios formulės paprastumo ir pritaikymo ieškant tam tikros vertės iš kitų lentelių, turinčių vieną bendrą kintamąjį šiose lentelėse. Tarkime, kad turite dvi lenteles, kuriose yra išsami informacija apie įmonės darbuotojų atlyginimą ir vardą, o pagrindinis stulpelis yra darbuotojo ID. Jūs norite gauti atlyginimą iš A lentelės B lentelės.

Galite naudoti VLOOKUP, kaip nurodyta toliau.

Tai bus pateikta žemiau esančioje lentelėje, kai pritaikysime šią išplėstinę „Excel“ formulę kitose stulpelio „Darbuotojų atlyginimas“ langeliuose.

Vilkite formulę į likusias langelius.

Yra trys pagrindiniai VLOOKUP atribojimai:

1. Dešinėje stulpelio, kurio vertę norite užpildyti iš kitos lentelės, negali būti pirminio stulpelio. Tokiu atveju stulpelis „Darbuotojo atlyginimas“ negali būti prieš darbuotojo ID.
2. Jei lentelės B lentelėje yra pasikartojančios vertės, pirmoji reikšmė bus užpildyta langelyje.
3. Jei į duomenų bazę įterpsite naują stulpelį (Ex įterpkite naują stulpelį prieš Darbuotojų atlyginimą B lentelėje), formulės išvestis gali skirtis, atsižvelgiant į poziciją, kurią paminėjote formulėje (Minėtu atveju išvestis būtų tuščias)

2 - „INDEX“ formulė „Excel“

Ši išplėstinė „Excel“ formulė naudojama norint gauti langelio vertę pateiktoje lentelėje nurodant eilučių, stulpelių ar abiejų skaičių. Pvz. Norėdami gauti darbuotojo vardą per 5 stebėjimą, žemiau pateikiami duomenys.

Mes galime naudoti pažangią „Excel“ formulę taip:

Ta pati INDEX formulė gali būti naudojama norint gauti vertę iš eilės. Naudojant eilutės ir stulpelio numerį, sintaksė atrodys maždaug taip:

Pirmiau pateikta formulė grąžins „Rajesh Ved“.

Pastaba: Jei į 5 eilutės duomenis įterpsite dar vieną eilutę, formulė grąžins „Chandan Kale“. Taigi išvestis priklausys nuo bet kokių pokyčių, vykstančių duomenų lentelėje per tam tikrą laiką.

# 3 - „MATCH“ formulė „Excel“

Ši „Excel“ išplėstinė formulė grąžina eilutės arba stulpelio numerį, kai nurodytame diapazone yra tam tikros eilutės ar skaičiaus atitiktis. Žemiau pateiktame pavyzdyje mes bandome rasti „Rajesh Ved“ poziciją stulpelyje „Darbuotojo vardas“.

Formulė būtų tokia, kaip nurodyta žemiau:

Funkcija MATCH grąžins 5 kaip vertę.

3 tikslus argumentas naudojamas tiksliajai atitikčiai. Taip pat galite naudoti +1 ir -1 atsižvelgdami į savo reikalavimus.

Pastaba: Norint įveikti VLOOKUP apribojimą, galima derinti INDEX ir MATCH.

# 4 - IF IR Formulė „Excel“

Yra daug atvejų, kai reikia sukurti vėliavas, remiantis tam tikrais apribojimais. Mes visi esame susipažinę su pagrindine IF sintakse. Mes naudojame šią išplėstinę „Excel IF“ funkciją, kad sukurtume naują lauką, pagrįstą tam tikrais jau esamo lauko apribojimais. Bet ką daryti, jei kurdami vėliavą turime atsižvelgti į kelis stulpelius. Pvz. Toliau nurodytu atveju norime pažymėti visus darbuotojus, kurių atlyginimas yra didesnis nei 50 tūkst., Bet darbuotojo pažymėjimas didesnis nei 3.

Tokiais atvejais naudotume IF IR. Žemiau rasite tą patį ekrano kopiją.

Rezultatas būtų 0.

Mes galime turėti daug sąlygų ar apribojimų, norėdami sukurti žymę pagal kelis stulpelius naudodami AND.

# 5 - JEI ARBA formulė „Excel“

Panašiai mes taip pat galime naudoti OR funkciją excel vietoj AND, jei mums tereikia įvykdyti vieną iš daugelio sąlygų.

Pirmiau nurodytais atvejais, jei kuri nors iš vienos sąlygos bus patenkinta, langelį užpildysime kaip dar vieną 0. Taip pat 1 arba 0 galime pakeisti kai kuriomis pakaitomis dvigubomis kabutėmis („“).

# 6 - SUMIF formulė „Excel“

Kai kuriose analizėse gali reikėti filtruoti kai kuriuos stebėjimus taikant sumos ar skaičiavimo funkciją. Tokiais atvejais mums padeda ši pažangi „Excel“ SUMIF funkcija. Jis filtruoja visus stebėjimus pagal tam tikras sąlygas, pateiktas šioje pažangioje „Excel“ formulėje, ir juos apibendrina. Pvz. Ką daryti, jei norime sužinoti tik tų darbuotojų, kurių darbuotojo pažymėjimas didesnis nei 3, atlyginimų sumą.

Taikydami SUMIFS formulę:

Formulė pateikia rezultatus kaip 322000.

Taip pat galime suskaičiuoti organizacijos darbuotojų, turinčių didesnį nei 3 darbuotojo ID, skaičių, kai vietoj SUMIF naudojame COUNTIF.

# 7 - „CONCATENATE“ formulė „Excel“

Ši „Excel“ išplėstinė funkcija yra viena iš formulių, kurią galima naudoti su keliais variantais. Ši pažangi „Excel“ formulė padeda mums sujungti kelias teksto eilutes į vieną teksto eilutę. Pvz., Jei norime rodyti darbuotojo ID ir darbuotojo vardą viename stulpelyje.

Mes galime naudoti šią CONCATENATE formulę tik tam.

Pagal pirmiau pateiktą formulę bus pateiktas „1Aman Gupta“.

Mes galime turėti dar vieną variantą, įdėdami vieną brūkšnį tarp ID ir NAME. Pvz. CONCATENATE (B3, “-“, C3) rezultatas bus „1-Aman Gupta“. Mes taip pat galime tai naudoti VLOOKUP, kai „Excel“ vertės LOOKUP yra daugiau nei vieno kintamojo mišinys.

# 8 - kairė, pusiaukelė ir dešinė „Excel“ formulė

Mes galime naudoti šią išplėstinę „Excel“ formulę, jei norime iš tam tikros eilutės ištraukti tam tikrą substringą. Minėtos formulės galėtų būti naudojamos pagal mūsų reikalavimus. Pvz. Jei norime išskirti pirmuosius 5 simbolius iš „Darbuotojo vardo“, galime naudoti „LEFT“ formulę „Excel“ su stulpelio pavadinimu ir antruoju parametru kaip 5.

Rezultatas pateikiamas žemiau:

„RIGHT“ formulės taikymas „Excel“ programoje taip pat yra tas pats, tiesiog mes pažvelgtume į simbolį iš eilutės dešinės. Tačiau, jei „MID“ funkcija yra „Excel“, turime nurodyti reikalingos teksto eilutės pradinę padėtį ir eilutės ilgį.

# 9 - „OFFSET“ formulė „Excel“

Ši pažangi „Excel“ funkcija kartu su kitomis funkcijomis, tokiomis kaip SUM arba AVERAGE, gali būti naudinga dinamiškai prisidedant prie skaičiavimų. Geriausia jį naudoti tais atvejais, kai į esamą duomenų bazę įterpiame ištisines eiles. OFFSET „Excel“ pateikia diapazoną, kuriame turime paminėti atskaitos langelį, eilučių ir stulpelių skaičių. Pvz. Jei norime apskaičiuoti pirmųjų 5 darbuotojų vidurkį įmonėje, kurioje turime darbuotojų atlyginimą, surūšiuotą pagal darbuotojo pažymėjimą, galime atlikti šiuos veiksmus. Žemiau pateiktas skaičiavimas visada duos mums atlyginimą.

• Tai suteiks mums pirmųjų 5 darbuotojų atlyginimų sumą.

# 10 - „TRIM“ formulė „Excel“

Ši pažangi „Excel“ formulė naudojama nesvarbioms teksto erdvėms išvalyti. Pvz. Jei norime pašalinti tarpus kurio nors vardo pradžioje, galime jį naudoti naudodami „TRIM“ funkciją „Excel“ programoje, kaip nurodyta toliau:

Rezultatas būtų „Chandan Kale“ be vietos prieš Chandan.