10 geriausių „Excel“ formulių ir funkcijų sąrašas
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ų, kurios turi vieną bendrą kintamąjį šiose lentelėse. Tarkime, kad turite dvi lenteles su išsamia informacija apie įmonės darbuotojų atlyginimą ir pavadinimą, o pagrindinis ID 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:
- 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.
- B lentelės pirminio stulpelio kopijuojamose reikšmėse pirmoji vertė bus užpildyta langelyje.
- 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 (aukščiau nurodytu atveju rezultatas būtų tuščias)
2 - „INDEX“ formulė „Excel“
Ši išplėstinė „Excel“ formulė naudojama norint gauti nurodytos lentelės langelio vertę nurodant eilučių, stulpelių ar abiejų skaičių. Pvz. Norėdami gauti darbuotojo vardą 5 -ojo stebėjimo metu, ž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:
Aukščiau pateikta formulė grąžins „Rajesh Ved“.
Pastaba: jei į 5 -ąją eilutę įterpsite dar vieną eilutę , formulėje bus rodoma „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 konkreti eilutė ar skaičius. Žemiau pateiktame pavyzdyje mes bandome rasti „Rajesh Ved“ darbuotojo vardo stulpelyje.
Formulė būtų tokia, kaip nurodyta žemiau:
Funkcija MATCH grąžins 5 kaip vertę.
3 -iasis argumentas naudojamas tikslią rungtynių. 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 esamo lauko apribojimais. Bet ką daryti, jei kurdami vėliavą turime atsižvelgti į kelis stulpelius. Pvz. Šiuo 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 - IF ARBA formulė „Excel“
Panašiai galime naudoti OR funkciją „Excel“ vietoje „AND“, jei mums reikia įvykdyti vieną iš daugelio sąlygų.
Pirmiau nurodytais atvejais, jei kuri nors iš vienos sąlygos bus įvykdyta, langelį užpildysime kaip dar 1 0. Taip pat 1 arba 0 galime pakeisti kai kuriomis pakaitomis dvigubomis kabutėmis („“).
# 6 - SUMIF formulė „Excel“
Kai kuriose analizėse jums gali tekti 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.
Tam galime naudoti šią CONCATENATE formulę.
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ė
Šią pažangią „Excel“ formulę galime naudoti tam, kad iš tam tikros eilutės ištrauktume konkretų substringą. Jis galėtų būti naudojamas pagal mūsų reikalavimus. Pvz. Jei norime išgauti pirmuosius 5 simbolius iš „Darbuotojo vardo“, „Excel“ galime naudoti kairę formulę 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 į personažą iš eilutės dešinės. Tačiau, jei „MID“ funkcija yra „Excel“, turime nurodyti pradinę reikalingos teksto eilutės 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 dinamiškai prisiliesti 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.
