Mafomula ofunikira kwambiri a Excel kuti ayambirenso ngati pro

Kuphunzira Excel kuyambira poyambira ndikosavuta kuposa momwe zimawonekera ngati mukudziwa koyambira. Ogwiritsa ntchito ambiri, ngakhale omwe amawagwiritsa ntchito tsiku ndi tsiku, amakonda kungokhala ndi njira zoyambira, motero amaphonya mphamvu zambiri za pulogalamuyi. Kuphunzira mafomu ofunikira a Excel sikungokupulumutsirani nthawi ndi zolakwika, komanso Idzakuthandizani kusanthula, kukonza ndikuwona deta ngati katswiri weniweni..

Mu bukhuli, mupeza njira zofunika kwambiri, kuyambira zosavuta mpaka zotsogola kwambiri zomwe zingakuthandizeni kuthana ndi vuto lililonse. Cholinga chake ndi chakuti inuyo, kaya ndinu oyamba kapena odziwa kale, kuti muthane ndi spreadsheet iliyonse mosavuta, mwanzeru, komanso mwaluso. Kuphatikiza apo, Malangizo othandiza, zidule zothandiza, ndi zothandizira kuti maphunziro anu akhale osalala momwe mungathere.

¿Qué es una fórmula en Excel y para qué sirve?

Fomula mu Excel ndi mndandanda womwe umawerengera, kusanthula deta, kapena kubweza ma values ​​mkati mwa spreadsheet. Mafomu onse amayamba ndi chizindikiro chofanana (=), yomwe imauza Excel kuti iwerengere zotsatira zake potengera zomwe zikuphatikizidwa, maumboni, ndi ogwiritsa ntchito.

Mafomu amakulolani kuchita chilichonse kuyambira masamu osavuta mpaka kusaka, kusanthula deta, kuwerengera ndalama, kusokoneza masiku, ndi zina zambiri. Kusinthasintha kwawo kumawapangitsa kukhala ofunikira m'malo aliwonse, kaya maphunziro, bizinesi kapena ntchito zaumwini.

Por ejemplo, si escribes =A1+B1 mu cell, Excel idzawonjezera zomwe zili m'maselo A1 ndi B1. Koma zotheka zimapita patsogolo kwambiri, kukulolani kuti mupange machitidwe ovuta okhala ndi mikhalidwe yambiri, maumboni osinthika, ntchito zokhazikika, komanso kuwerengera zokha kwa data yayikulu.

Kusiyana pakati pa mafomu ndi ntchito mu Excel

Mu Excel, mawu akuti formula ndi ntchito nthawi zambiri amagwiritsidwa ntchito mosiyanasiyana, koma sizofanana ndendende. Kumvetsetsa kusiyanaku kudzakuthandizani kupanga ndikuphatikiza machitidwe apamwamba kwambiri:

• Fórmula: Lili ndi mawu opangidwa pamanja pogwiritsa ntchito mfundo, maumboni a ma cell ndi masamu (+, -, *, /, ^). Chitsanzo: =A1+B2-3
• Ntchito: Ndi ntchito yofotokozedweratu mu Excel yomwe imathandizira mawerengedwe ovuta. Ntchito iliyonse ili ndi mawu ake enieni ndi mfundo zake, zomwe zimayikidwa m'makolo. Chitsanzo: =SUM(A1:A10)

M'malo mwake, mafomu amatha kukhala ndi ntchito, ndipo izi zitha kuphatikizidwa kuti apange ntchito zapamwamba kwambiri..

Elementos de una fórmula en Excel

Mafomu a Excel amapangidwa ndi zinthu zingapo zofunika zomwe muyenera kudziwa:

• Constantes: Ndi zikhalidwe zokhazikika, monga manambala (50) kapena malemba (“January”).
• Maumboni: Imawonetsa cell kapena kuchuluka kwa ma cell komwe Excel idzafufuza zomwe mungagwiritse ntchito. Chitsanzo: A1, B2:B8.
• Operadores: Zizindikiro za masamu kuti muwonjezere (+), kuchotsa (-), kuchulukitsa (*), kugawa (/), kuwonjezera (^), concatenate (&) ndi kuyerekezera (=, >, <, <>, etc.).
• Ntchito: Mawu osakiratu omwe amawerengera mwachindunji (SUM, AVERAGE, VLOOKUP, IF, etc.).

Kuphunzira kuphatikiza zinthu izi ndiye chinsinsi chopangira mafomu omwe amasintha kwambiri ntchito yanu ya tsiku ndi tsiku..

Mitundu ya mafomula mu Excel: kuyambira zoyambirira mpaka zapamwamba

Pali mitundu ingapo ya mafomula mu Excel, kuyambira pazida zosavuta mpaka zapamwamba zowunikira akatswiri:

• Njira zosavuta: Kuonjezera mwachindunji, kuchotsa, kuchulukitsa ndi kugawa.
• Mafomula okhala ndi maumboni: Zochita zomwe zimagwiritsa ntchito deta yomwe ili m'maselo osiyanasiyana.
• Mafomu okhala ndi ntchito: Zowerengera zokha, ma avareji, zosaka, zowerengera, kuzungulira, ndi zina.
• Fórmulas condicionales: Amawerengera kutengera ngati vuto lakwaniritsidwa (ngati, NDI, KAPENA, OSATI ntchito).
• Mafomula okhala ndi njira zingapo: Kugwira ntchito ndi zofunikira zingapo pogwiritsa ntchito SUMIFS, COUNTIFS.
• Njira zachuma ndi ziwerengero: Kuwerengera ngongole, chiwongola dzanja, zomwe zikuchitika, ndi zina.
• Njira zosinthira mawu: Kujowina, kuchotsa kapena kuyeretsa zingwe.
• Masiku ndi nthawi: Kuwerengera nthawi, kusiyana kwa masiku, kapena kuchotsa zinthu monga mwezi kapena chaka.
• Fórmulas matriciales: Kuwerengera pamagulu akuluakulu a data.

Operaciones matemáticas básicas en Excel

Wogwiritsa ntchito aliyense wa Excel ayenera kudziwa bwino masamu oyambira, chifukwa ndiwo maziko omwe amapangira njira zovuta kwambiri. Mutha kuwalowetsa mwachindunji mu cell kapena kuphatikiza maumboni ndi ntchito.

• Sumar: =A1+B1 onjezani zomwe zili mu cell A1 ndi B1.
• Restar: =A2-B2 Chotsani mtengo wa B2 kuchokera ku A2.
• Multiplicar: =A3*B3 kuchulukitsa mitengo ya A3 ndi B3.
• Dividir: =A4/B4 Gawani mtengo wa A4 ndi wa B4.
• Potenciar: =A5^2 kwezani mtengo wa A5 pa lalikulu.

Excel imalemekeza dongosolo la masamu: mphamvu zoyamba, kenako kuchulukitsa ndi kugawa, ndipo pamapeto pake kuwonjezera ndi kuchotsa. Ngati mukufuna kuwongolera dongosolo, gwiritsani ntchito mabatani: = (A1+B1)*C1.

Essential Excel formulas ndi ntchito zawo

Pansipa pali njira zofunika kwambiri zomwe muyenera kudziwa kuti muyambe kugwira ntchito ngati katswiri weniweni wa Excel:

SUMA: Fomula par kupambana

SUM ndiye ntchito yomwe imagwiritsidwa ntchito kwambiri ndipo imakupatsani mwayi wowonjezera mwachangu komanso mosavuta ma cell angapo kapena angapo osalumikizana.

• Chitsanzo: =SUMA(A1:A10) idzawerengera mitengo yonse mumtundu A1 mpaka A10.
• Kuphatikiza apo, mutha kuwonjezera ma cell ena: =SUM(A1,B2,B3)
• Mbali ya AutoSum, yomwe ili pa tabu Yanyumba, imapangitsa kuti ntchitoyi ikhale yosavuta.

AVERAGE: Werengetsani avareji ya data yanu

Ntchito AVEREJI Imabweza mtengo wapakati pagulu la manambala, oyenerera kusanthula zomwe zikuchitika, kuzindikira kuchuluka kwa malonda, magiredi, mitengo, ndi zina.

• Chitsanzo: =PROMEDIO(B1:B12) adzawerengera zapakati pa B1 ndi B12.
• Kwa ma avareji ovomerezeka alipo PROMEDIO.SI y AVERAGE NGATI JOINT.

COUNT, COUNTIF, ndi COUNTIF: Luso lowerengera deta

Ndi Excel, mutha kuwerengera zonse manambala ndi zolemba, ma cell opanda kanthu kapena ma cell okhala ndi zomwe zili, kutengera ntchito yomwe yasankhidwa.

• CONTAR: Imawerengera ma cell omwe ali ndi manambala okha. =CONTAR(C1:C10)
• CONTARA: Imawerengera ma cell onse okhala ndi data (manambala kapena zolemba), kunyalanyaza ma cell opanda kanthu. =CONTARA(C1:C10)
• CONTAR.SI: Imawerengera ma cell omwe amakwaniritsa muyeso wina. =CONTAR.SI(D1:D20,"Aprobado")
• CONTAR.SI.CONJUNTO: Amalola zinthu zingapo. =CONTAR.SI.CONJUNTO(A1:A10,">10",B1:B10,"Hombre")
• COUNTBLANK: Imawerengera ma cell opanda kanthu munjira zosiyanasiyana. =COUNTBLANK(A2:D2)

MIN ndi MAX: Pezani zochulukira mu data yanu

Ntchito za MIN ndi MAX zimazindikira mwachangu mtengo wotsika kwambiri kapena wapamwamba kwambiri pagulu, wothandiza pakuwunika mitengo, zolemba, nsonga zogulitsa, ndi zina zambiri.

• Chitsanzo chochepa: =MIN(A2:A100)
• Chitsanzo chachikulu: =MAX(A2:A100)

RUND, RUNDUP, ndi RUNDDOWN: Kulondola powerengera

Izi zimakulolani kuti musinthe zotsatira kukhala nambala yeniyeni ya malo kapena kuzungulira kapena kutsika malinga ndi zosowa zanu.

• ZONSE: =REDONDEAR(A1,2) imasintha kukhala magawo awiri a decimal.
• ROUND.ZAMBIRI: Nthawi zonse zungulirani. Chitsanzo: =REDONDEAR.MAS(3.14159,2) → 3.15
• ROUND.MINUS: Nthawi zonse zungulirani pansi. Chitsanzo: =REDONDEAR.MENOS(3.14159,2) → 3.14

Zoyenera Kutsatira: Kupanga zisankho Zanzeru

Kuti muwerenge kapena kuwonetsa zotsatira zosiyana malinga ndi momwe zinthu ziliri, monga ngati wina wapambana kapena walephera, muyenera kugwiritsa ntchito njira zovomerezeka. Ntchito zomveka zomwe zimagwiritsidwa ntchito kwambiri ndi:

• SI: Imawunika momwe zinthu zilili ndikubweza mtengo wina ngati uli wowona ndipo wina ngati uli wabodza.
  Chitsanzo: =SI(B2>=60, "Aprobado", "Reprobado")
• Y: Imabwezera TRUE pokhapokha ngati zonse zakwaniritsidwa.
  Chitsanzo: =SI(Y(A1>10, B1<50), "Ok", "No")
• O: Imabwezera TRUE ngati chinthu chimodzi chakwaniritsidwa.
  Chitsanzo: =SI(O(A1>100, B1>100), "Revisar", "Correcto")
• NO: Imatembenuza mtengo womveka.
  Chitsanzo: =SI(NO(A1=15), "Diferente de 15", "Es 15")
• SI.ERROR: Imakulolani kuti muwonetse mtengo wina ngati fomula yalephera.
  Chitsanzo: =SI.ERROR(A2/B2, "No calculable")

Sakani ndi kuchotsa zambiri: VLOOKUP, HLOOKUP, INDEX, MATCH ndi XLOOKUP

Chimodzi mwazinthu zamphamvu kwambiri za Excel ndikutha kusaka deta m'matebulo ndikubweza zambiri zokhudzana nazo. Ngati mukufuna kukumba mozama pazinthu izi, phunzirani zambiri ndi bukhuli momwe mungagwiritsire ntchito mafomu a Excel.

• BUSCARV: Imapeza mtengo mugawo loyamba la masinthidwe ndi kubweza data kuchokera mugawo lina pamzere womwewo. Chitsanzo: =BUSCARV(102,A2:C10,3,FALSO)
• BUSCARH: Zofanana ndi VLOOKUP koma zimagwira mopingasa ndi mizere.
• INDEX + MATCH: Amakulolani kuti mufufuze mbali iliyonse (osati kumanja ngati VLOOKUP) ndikupereka kusinthasintha kwakukulu.
  Chitsanzo: =ÍNDICE(B2:B20, COINCIDIR("Producto",A2:A20,0))
• XLOOKUP: Ntchito yamakono yomwe imalowa m'malo mwa VLOOKUP ndi HLOOKUP, kulola kusaka kosunthika komanso kosinthika m'mizere ndi mizere yonse.

SUMIF, SUMIFS, ndi AVERAGEIFS: Mawerengero ndi ma avareji okhala ndi mfundo

• SUMAR.SI: Imawerengera ziwerengero zomwe zimakwaniritsa zofunikira. Chitsanzo: =SUMAR.SI(A1:A10,">10")
• SUM NGATI KUKHALA: Amalola zambiri zofunika. =SUMAR.SI.CONJUNTO(C2:C12,B2:B12,">500",D2:D12,"Región1")
• AVERAGEIFS: Imawerengetsa avareji ya zinthu zomwe zimakwaniritsa zofunikira zingapo. Chitsanzo: =AVERAGEIFS(C3:C15, A3:A15, "Manzana", B3:B15, "Sur")

Kusintha mawu mu Excel: CONCAT, CONCATENATE, JOIN, KUmanzere, Kumanja, MINT, LEN, SPACES, SUBSTITUTE, REPLACE

• CONCAT ndi CONCATENATE: Phatikizani zomwe zili m'maselo angapo kukhala selo limodzi. Chitsanzo: =CONCAT(A1, " ", B1).
• OJONGANA: Amagwirizanitsa malemba ndi malire enieni. Chitsanzo: =UNIRCADENAS(", ", VERDADERO, A1:A3).
• KUmanzere ndi kumanja: Chotsani zilembo zoyambirira kapena zomaliza palemba. Chitsanzo: =IZQUIERDA(B2, 3) amachotsa zilembo zitatu zoyambirira.
• EXTRAE: Imachotsa zilembo zingapo kuchokera pamalo omwe mwasankha. Chitsanzo: =EXTRAE(C3, 2, 5).
• LARGO: Imabweza utali wa mawu. Chitsanzo: =LARGO(D4).
• ESPACIOS: Chotsani mipata yosafunikira palemba. Chitsanzo: =ESPACIOS(E5).
• M'MALO: Bwezerani mbali zina za mawuwo ndi zina. Chitsanzo: =SUSTITUIR(B15, "Madrid", "Barcelona").
• REEMPLAZAR: Imalowetsa zilembo pamalo enaake. Chitsanzo: =REEMPLAZAR("Feliz Navidad", 6, 8, "Hanukkah").

Mafomu a Tsiku ndi Nthawi: Konzani ndikuwerengera Nthawi Mosavuta

Kuwongolera masiku ndi nthawi ndikofunikira pa lipoti lililonse, mapulani, kapena kusanthula. 

• HOY: Devuelve la fecha actual. =HOY()
• AHORA: Imabweza tsiku ndi nthawi yomwe ilipo. =AHORA()
• DAY, MONTH, YEAR: Chotsani tsiku, mwezi, kapena chaka kuchokera pa deti. Chitsanzo: =DIA(B2)
• FECHA: Amapanga deti kuyambira chaka, mwezi, ndi tsiku lomwe laperekedwa. Chitsanzo: =FECHA(2025,6,10)
• DIAS: Werengani kuchuluka kwa masiku pakati pa madeti awiri. =DIAS(B1,A1)
• NGATI TSIKU: Imatsimikizira kusiyana kwenikweni pakati pa masiku awiri muzaka, miyezi, kapena masiku. Chitsanzo: =SIFECHA(A1,B1,"Y")
• DIASEM: Imabwezera tsiku la sabata la deti loperekedwa. Chitsanzo: =DIASEM(B2,2)

Zida zowunikira deta: matebulo a Pivot, kusanja ndi zosefera, zosefera, ma chart

Kuti muwongolere kusanthula ndi kuwonera deta yanu, in Nkhani iyi Mutha kupeza zida zabwino kwambiri za Excel zoyendetsedwa ndi AI, kuphatikiza matebulo a pivot ndi ma chart ochezera.

• Tablas dinámicas: Amakulolani kuti mupange gulu, kufotokozera mwachidule ndi kusanthula deta yovuta m'njira yowonjezereka komanso yowonekera.
• Ntchito monga SORT ndi UNIQUE: Amathandizira kuchotsa mindandanda yapadera kapena kusanja deta yokha.
• Zithunzi: Amathandizira kutanthauzira kowoneka bwino kwa zotsatira (mizere, mipiringidzo, mizere, madera, etc.).
• Magawo: Zosefera zowoneka komanso zolumikizirana zama pivot table.

Kuphatikizira zida izi ndi ma formula omwe afotokozedwa kumakupatsani mwayi wopeza zidziwitso zoyenera mumasekondi ndikupanga zisankho motengera deta yolimba.

Zofunikira zandalama ndi bizinesi

• NPV (Net Present Value): Unikani mabizinesi poganizira za tsogolo la ndalama zomwe zikuyenda.
• IRR (Mlingo Wamkati Wobwerera): Werengetsani phindu la ndalama.
• PAYMENT ndi PMT: Amazindikira kubweza ngongole nthawi ndi nthawi.
• NOMINAL VALUE/NOMINAL RATE: Kutembenuka pakati pa mitundu yosiyanasiyana ya chiwongola dzanja.
• DIAS360: Zothandiza pamaakaunti azachuma omwe amagwiritsa ntchito zaka 360.
• ZOTSATIRA, ZOCHITIKA: Zothandiza pakuyerekeza ndi kusanthula zochitika.

Kuwongolera ndi kukonza zolakwika: Njira zoyendetsera zolakwika ndikuwongolera deta

Kuti ma spreadsheets anu azikhala opanda zolakwika ndikuwongolera kudalirika kwa kuwerengera kwanu, mupeza malangizo owongolera zolakwika mu Excel apa.

• IF.ERROR / IFERROR: Amabwezeranso mtengo wina ngati fomula yalephera.
• ZOLAKWA: Imawonetsa ngati selo ili ndi cholakwika (mwachitsanzo, kugawa ndi ziro).
• ISWHITE: Imazindikira ngati foni ilibe kanthu.
• ISNUMBER/TEXT: Amasonyeza mtundu wa deta yomwe ili mu selo.
• Utali/LONGB: Imabwezeranso kuchuluka kwa zilembo zomwe zili m'mawu, zothandiza pakuwunikanso data yomwe yatumizidwa kunja.
• CELL: Imabweza zambiri za selo linalake, mtundu wake, mtundu wa data, ndi zina.

Njira zabwino kwambiri, zidule, ndi njira zazifupi kuti muphunzire ma fomula a Excel

• Gwiritsani ntchito njira zazifupi za kiyibodi: Monga Ctrl+C/V kwa copy/paste, Ctrl+1 for cell format, Ctrl+Shift+% percentage, Alt+= for quick sum.
• Kokani mafomu: Gwiritsani ntchito chogwirira chodzaza (malo ang'onoang'ono pakona ya selo) kukopera mafomu popanda kuwalemba pamanja.
• Onani maumboni: Onetsetsani kuti mukugwiritsa ntchito zolozera mtheradi ($A$1) ndi wachibale (A1) molondola, makamaka pokopera mafomu.
• Tetezani mafomu anu: Tsekani ma cell okhala ndi ma formula ofunikira kuti asachotsedwe mwangozi kapena kusinthidwa (Cell Format > Tetezani > Tsekani).
• Bisani mafomula ngati kuli kofunikira: Mutha kubisa mawonekedwe a fomula kuti muteteze ena kuti asawone mawerengedwe omwe ali pansi pake.
• Yeretsani deta: Gwiritsani ntchito SPACES, SUBSTITUTE, ndi ntchito zina kuyeretsa zambiri musanagwiritse ntchito zofunikira.

Malangizo ena owonjezera luso lanu la Excel

• Nthawi zonse yambani kuwerengera kwanu ndi zosavuta: Phunzirani zoyambira musanapitirire ku zida zapamwamba.
• Phunzirani ndi zochitika zenizeni: Pangani maspredishiti anu kuti muzitha kuyang'anira ndalama, zinthu, mndandanda wa zochita, kapena kulemba.
• Onani zida zaulere: Pali unyinji wamaphunziro apaintaneti, ma tempulo, mabwalo, ndi maphunziro omwe angakuthandizeni tsiku lililonse.
• Mantente al tanto de las novedades: Zomasulira zaposachedwa zikuphatikiza ntchito monga XLOOKUP, matrices osinthika kapena Power Query pakusanthula kwakukulu kwa data.
• Gwiritsani ntchito zigawo zapamwamba ndi zosefera: Kuti mudziwe zambiri, gwiritsani ntchito mwayi wa magawo a data ndi zosefera kuti muwone zofunikira zokha.
• Ikani masanjidwe okhazikika: Imaunikira zokha zofunikira, zomwe zikuchitika, kapena zolakwika kuti muwonetsetse deta yofunika.

Chidziwitso chapamwamba ichi ndi malangizo othandiza adzakuthandizani kuyendetsa bwino ntchito yanu ya tsiku ndi tsiku Excel, zochita zokha, kuchepetsa zolakwika, ndi kupanga malipoti olondola kwambiri. Kuyeserera kosalekeza ndikuwunika zatsopano kudzakulitsa luso lanu mokulirapo, ndikukuikani ngati katswiri pantchitoyo. Tikukhulupirira kuti tsopano mukudziwa mafomu ofunikira a Excel kuti akuthandizeni kuyambira pachiyambi ndikukhala katswiri.