Ano ang Mga Pormula at Pag-andar sa Excel at Paano Ito Magagamit?



Ang Mga Formula at Pag-andar ng Excel ay ang mga bloke ng gusali upang pamahalaan ang data. Alamin kung paano sumulat, kopyahin / i-paste, itago ang mga formula. IF, COUNTIF, SUM, DATE, RANK, INDEX, FV, ROUND, atbp

Ang data ay magagamit lamang kapag maaari mo itong aktwal na magtrabaho at Excel ay isang tool na nagbibigay ng isang mahusay na halaga ng kaginhawaan pareho kapag kailangan mong bumalangkas ng mga equation ng iyong sarili o gumawa ng mga built-in na. Sa artikulong ito, matututunan mo kung paano ka makakapagtatrabaho sa mga Excel Formula na ad Function na ito.

Narito ang isang mabilis na pagtingin sa mga paksang tinalakay dito:





Ano ang isang Formula?

Sa pangkalahatan, ang isang pormula ay isang kondensadong paraan ng kumakatawan sa ilang impormasyon sa mga tuntunin ng mga simbolo. Sa Excel, ang mga formula ay mga expression na maaaring mailagay sa mga cell ng isang sheet ng Excel at ang kanilang mga output ay ipinapakita bilang isang resulta.

Ang mga formula ng Excel ay maaaring sa mga sumusunod na uri:



Uri

Halimbawa

Paglalarawan



Mga operator ng matematika (+, -, *, atbp)

Halimbawa: = A1 + B1

Nagdaragdag ng mga halaga ng A1 at B1

Mga Halaga o teksto

Halimbawa: 100 * 0.5 na multiply ng 100 beses na 0.5

Kinukuha ang mga halaga at ibinalik ang output

Sanggunian ng cell

HALIMBAWA: = A1 = B1

Nagbabalik ng TAMA o MALI sa pamamagitan ng paghahambing ng A1 at B1

Mga pagpapaandar sa worksheet

HALIMBAWA: = SUM (A1: B1)

Ibinabalik ang output sa pamamagitan ng pagdaragdag ng mga halagang naroroon sa A1 at B1

Pagsulat ng Mga Formula ng Excel:

Upang makapagsulat ng isang formula sa isang Excel sheet cell, maaari mong gawin ang mga sumusunod:

  • Piliin ang cell kung saan mo nais ipakita ang resulta
  • I-type ang isang tanda na '=' sa pauna upang ipaalam sa Excel na magpapasok ka ng isang formula sa cell na iyon
  • Pagkatapos nito, maaari mong i-type ang mga cell address o tukuyin ang mga halagang nais mong kalkulahin
  • Halimbawa, kung nais mong idagdag ang mga halaga ng dalawang cell, maaari kang mag-type sa cell address tulad ng sumusunod:

ipasok ang formula-Excel Formulas-Edureka

  • Maaari mo ring piliin ang mga cell na ang kabuuan na nais mong kalkulahin sa pamamagitan ng pagpili ng lahat ng kinakailangang mga cell habang pinipindot ang Ctrl key:


Pag-edit ng isang Formula:

Kung sakaling nais mong i-edit ang ilang dating naipasok na pormula, piliin lamang ang cell na naglalaman ng target na formula at sa formula bar maaari kang gumawa ng mga nais na pagbabago. Sa nakaraang halimbawa, kinakalkula ko ang Kabuuan ng A1 at A2. Ngayon, i-edit ko ang pareho at babaguhin ang pormula upang makalkula ang produkto ng mga halagang naroroon sa dalawang cell na ito:


Kapag tapos na ito, pindutin ang Enter upang makita ang nais na output.

Kopyahin o I-paste ang isang Formula:

Ang Excel ay talagang magagamit kapag kailangan mong kopyahin / i-paste ang mga formula. Tuwing kinopya mo ang isang pormula, awtomatikong inaalagaan ng Excel ang mga sanggunian sa cell na kinakailangan sa posisyon na iyon. Ginagawa ito sa pamamagitan ng isang sistemang tinawag bilang Mga Kamag-anak na Address ng Cell .

Upang makopya ang isang pormula, piliin ang cell na naglalaman ng orihinal na pormula at pagkatapos ay i-drag ito hanggang sa cell na iyon na nangangailangan ng isang kopya ng pormulang iyon tulad ng sumusunod:

Tulad ng nakikita mo sa imahe, ang formula ay orihinal na nakasulat sa A3 at pagkatapos ay hinila ko ito pababa sa kahabaan ng B3 at C3 upang makalkula ang kabuuan ng B1, B2 at C1, C2 nang hindi eksklusibong isulat ang mga cell address.

Kung sakaling babaguhin ko ang mga halaga ng alinman sa mga cell, ang output ay awtomatikong maa-update ng Excel alinsunod sa Mga Kamag-anak na Address ng Cell , Ganap na Mga Address ng Cell o Mixed Cell Address .

Itago ang Mga Pormula sa Excel:

Kung sakaling nais mong itago ang ilang pormula mula sa isang excel sheet, magagawa mo ito tulad ng sumusunod:

  • Piliin ang mga cell na ang formula ay balak mong itago
  • Buksan ang window ng Font at piliin ang pane ng Proteksyon
  • Suriin ang Nakatagong pagpipilian at mag-click sa OK
  • Pagkatapos, mula sa tab na laso, piliin ang Suriin
  • Mag-click sa Protect sheet (Hindi gagana ang mga formula kung hindi mo ito gagawin)
  • Hihilingin sa iyo ng Excel na magpasok ng isang password upang maipakita ang mga formula sa paggamit sa hinaharap

Nauuna ang Operator ng Mga Formula ng Excel:

Sinusunod ng mga formula ng Excel ang mga panuntunan sa BODMAS (Mga Bracket Order Division na Pagdagdag ng Pagdagdag ng Pagdagdag) na mga patakaran. Kung mayroon kang isang formula na naglalaman ng mga bracket, malulutas ang expression sa loob ng mga braket bago ang anumang ibang bahagi ng kumpletong pormula. Tingnan ang imahe sa ibaba:

Tulad ng nakikita mo sa halimbawa sa itaas, mayroon akong isang formula na binubuo ng isang bracket. Kaya alinsunod sa mga panuntunan sa BODMAS, unang makikita ng Excel ang pagkakaiba sa pagitan ng A2 at B1 at pagkatapos ay idaragdag nito ang resulta sa A1.

Ano nasa ‘Excel’ ba ang mga ‘Function’?

Sa pangkalahatan, ang isang pagpapaandar ay tumutukoy sa isang pormula na naisakatuparan sa ilang naibigay na pagkakasunud-sunod. Nagbibigay ang Excel ng isang malaking bilang ng built-in na pag-andar maaari itong magamit upang makalkula ang resulta ng iba`t ibang mga formula.

Ang mga formula sa Excel ay nahahati sa mga sumusunod na kategorya:

CatagoryMahalagang Mga Pormula

Petsa at Oras

PETSA, ARAW, BULAN, ORAS, atbp

Pinansyal

ACCI, accinto, Dollard, INTRAATE, atbp

Math at Trig

ano ang ginagawa ng append sa java
SUM, SUMIF, PRODUCT, SIN, COS, atbp

Istatistika

AVERAGE, COUNT, COUNTIF, MAX, MIN, atbp

Paghanap at Sanggunian

COLUMN, HLOOKUP, ROW, VLOOKUP, PILI, etc.

Database

DAVERAGE, DCOUNT, DMIN, DMAX, atbp

Text

BAHTTEXT, DOLLAR, LOWER, UPPER, atbp

Lohikal

AT, O, HINDI, KUNG, TUNAY, MALI, atbp

Impormasyon

INFO, ERROR.TYPE, TYPE, ISERROR, atbp

Engineering

KOMPLEX, CONVERT, DELTA, OCT2BIN, atbp

Cube

CUBESET, CUBENUMBER, CUBEVALUE, atbp

Pagkakatugma

PERCENTILE, RANK, VAR, MODE, atbp

Web

ENCODEURL, FILTERXML, WEBSERVICE

Ngayon, tingnan natin kung paano magagamit ang ilan sa pinakamahalaga at karaniwang ginagamit na Mga Formula ng Excel.

Pinakamahalagang Mga Pag-andar ng Excel:

Narito ang ilan sa mga pinakamahalagang pagpapaandar ng Excel kasama ang kanilang mga paglalarawan at halimbawa.

PETSA:

Ang isa sa pinakamahalaga at malawak na ginagamit na pag-andar ng Petsa sa Excel ay ang DATE function. Ang syntax nito ay ang mga sumusunod:

ay isang has java

DATE (taon, buwan, araw)

Ang pagpapaandar na ito ay nagbabalik ng isang numero na kumakatawan sa ibinigay na petsa sa format ng petsa ng oras ng MS Excel. Ang pagpapaandar ng DATE ay maaaring gamitin tulad ng sumusunod:

HALIMBAWA:

  1. = DATE (2019,11,7)
  2. = DATE (2019,11,7) -7 (ibabalik ang kasalukuyang petsa - pitong araw)

ARAW:

Ibinabalik ng pagpapaandar na ito ang halaga ng araw ng buwan (1-31). Ang syntax nito ay ang mga sumusunod:

DAY (serial_number)

Dito, ang serial_number ay ang petsa kaninong araw nais mong kunin. Maaari itong ibigay sa anumang paraan tulad ng resulta ng ilang iba pang pagpapaandar, na ibinibigay ng pagpapaandar ng DATE, o isang sanggunian ng cell.

HALIMBAWA:

  1. = ARAW (A7)
  2. = DAY (NGAYON ())
  3. = DAY (DATE (2019, 11,8))

BULAN:

Tulad ng pag-andar ng DAY, nagbibigay ang Excel ng isa pang pag-andar ie ang MONTH function upang makuha ang buwan mula sa isang tukoy na petsa. Ang syntax ay ang mga sumusunod:

MONTH (serial_number)

HALIMBAWA:

  1. = MONTH (NGAYON ())
  2. = MONTH (PETSA (2019, 11,8))

Porsyento:

Tulad ng alam nating lahat, Porsyento ay ang ratio na kinakalkula bilang isang maliit na bahagi ng 100. Maaari itong maipahiwatig bilang mga sumusunod:

Porsyento = (Bahagi / Buong) x 100

Sa Excel, maaari mong kalkulahin ang porsyento ng anumang nais na mga halaga. Halimbawa, kung mayroon kang bahagi at buong mga halagang naroroon sa A1 at A2 at nais mong kalkulahin ang porsyento, magagawa mo ito tulad ng sumusunod:

  • Piliin ang cell kung saan mo nais ipakita ang resulta
  • I-type ang tanda na '='
  • Pagkatapos, i-type ang formula bilang A1 / A2 at pindutin ang Enter
  • Mula sa pangkat ng mga numero ng tab ng bahay, piliin ang simbolong '%'

KUNG:

Ang pahayag na KUNG ay isang kondisyunal na pahayag na nagbabalik ng Totoo kapag ang tinukoy na kundisyon ay nasiyahan at Flase kapag ang kundisyon ay hindi. Nagbibigay ang Excel ng built-in na 'KUNG' function na nagsisilbi sa hangaring ito. Ang syntax nito ay ang mga sumusunod:

KUNG (lohikal na pagsubok, halaga_if_true, halaga_if_false)

Dito, lohikal na pagsubok ay ang kundisyon na dapat suriin

HALIMBAWA:

  • Ipasok ang mga halagang ihahambing
  • Piliin ang cell na magpapakita ng output
  • Mag-type sa Formula Bar '= KUNG (A1 = A2,' Oo ',' Hindi ')' at pindutin ang enter

VLOOKUP:

Ang pagpapaandar na ito ay ginagamit upang tumingin at makakuha ng ilang partikular na data mula sa isang haligi mula sa isang sheet ng Excel. Ang 'V' sa VLOOKUP ay nangangahulugang patayo na pagtingin. Ito ay isa sa pinakamahalaga at malawak na ginagamit na mga formula sa Excel at upang magamit ang pagpapaandar na ito, ang talahanayan ay dapat na pinagsunod-sunod sa pataas na pagkakasunud-sunod. Ang syntax ng pagpapaandar na ito ay ang mga sumusunod:

VLOOKUP (lookup_value, table_array, col_index, num_range, lookup)

saan,

lookup_value ay ang halagang hahanapin

table_array ay ang mesa na hahanapin

col_index ay ang haligi kung saan kukunin ang halaga

range_lookup (opsyonal) ay nagbabalik ng TUNAY para sa tinatayang. tugma at MALI para sa isang eksaktong tugma

HALIMBAWA:

Tulad ng nakikita mo sa imahe, ang halagang tinukoy ko ay 2 at ang saklaw ng talahanayan ay nasa pagitan ng A1 at D4. Nais kong kunin ang pangalan ng empleyado, samakatuwid ay binigyan ko ang halagang haligi bilang 2 at dahil nais kong ito ay isang eksaktong tugma, ginamit ko ang Maling para sa pagsubaybay sa saklaw.

Buwis:

Ipagpalagay na nais mong kalkulahin ang kita sa buwis ng isang tao na ang kabuuang suweldo ay $ 300. Kakailanganin mong kalkulahin ang buwis sa kita tulad ng sumusunod:

  • Ilista ang Kabuuang Suweldo, Mga Pagbawas sa Suweldo, Kita sa Buwis at ang porsyento ng Buwis sa Kita
  • Tukuyin ang mga halaga ng Kabuuang Suweldo, Mga Pagbawas sa suweldo
  • Pagkatapos kalkulahin ang Kita sa Buwis sa pamamagitan ng paghahanap ng pagkakaiba sa pagitan ng Kabuuang Salary at Mga Pagkuha ng suweldo
  • Panghuli, kalkulahin ang Halaga ng Buwis

SUM:

Kinakalkula ng pagpapaandar ng SUM sa Excel ang resulta sa pamamagitan ng pagdaragdag ng lahat ng tinukoy na halaga sa Excel. Ang syntax ng pagpapaandar na ito ay ang mga sumusunod:

SUM (number1, number2,…)

Nagdaragdag ng lahat ng mga numero na tinukoy bilang isang parameter dito.

HALIMBAWA:

Kung sakaling nais mong kalkulahin ang kabuuan ng halagang ginastos mo sa pagbili ng mga gulay, ilista ang lahat ng mga presyo at pagkatapos ay gamitin ang SUM formula tulad ng sumusunod:

Compound Interes:

Upang makalkula ang interes ng tambalan, maaari mong gamitin ang isa sa mga Formula ng Excel na tinatawag na FV. Ang function na ito ay ibabalik ang hinaharap na halaga ng isang pamumuhunan batay sa pana-panahon, pare-pareho ang rate ng interes at pagbabayad. Ang syntax ng pagpapaandar na ito ay ang mga sumusunod:

FV (rate, nper, pmt, pv, uri)

Upang makalkula ang rate, kakailanganin mong hatiin ang taunang rate sa bilang ng mga panahon ibig sabihin taunang rate / mga panahon. Ang bilang ng mga panahon o nper ay kinakalkula sa pamamagitan ng pag-multiply ng term (no. Ng mga taon) kasama ang mga panahon na mga term * period. pmt ay nangangahulugang panaka-nakang pagbabayad at maaaring maging anumang halaga kabilang ang zero.

Isaalang-alang ang sumusunod na halimbawa:

Sa halimbawa sa itaas, kinakalkula ko ang Compound Interes para sa $ 500 sa rate na 10% sa loob ng 5 taon at ipinapalagay na ang pana-panahong halaga ng pagbabayad ay 0. Mangyaring tandaan na ginamit ko ang -B1 kahulugan, $ 500 ang kinuha sa akin.

Average:

Ang average, tulad ng alam nating lahat, ay naglalarawan ng median na halaga ng isang bilang ng mga halaga. Sa Excel, ang average ay madaling makalkula gamit ang built-in na function na tinatawag na 'AVERAGE'. Ang syntax ng pagpapaandar na ito ay ang mga sumusunod:

AVERAGE (numero1, numero2,…)

HALIMBAWA:

Kung sakaling nais mong kalkulahin ang average na marka na nakamit ng mga mag-aaral sa lahat ng mga pagsusulit, maaari ka lamang lumikha ng isang talahanayan at pagkatapos ay gamitin ang formula na AVERAGE upang makalkula ang average marka na nakamit ng bawat mag-aaral.

Sa halimbawa sa itaas, kinakalkula ko ang average na mga marka para sa dalawang mag-aaral sa dalawang pagsusulit. Kung sakaling mayroon kang higit sa dalawang mga halaga na ang average ay kailangang matukoy, kailangan mo lamang tukuyin ang saklaw ng mga cell kung saan naroroon ang mga halaga. Halimbawa:

COUNT:

Ang bilangin ang pag-andar sa Excel ay bibilangin ang bilang ng mga cell na naglalaman ng mga numero sa isang naibigay na saklaw. Ang syntax ng pagpapaandar na ito ay ang mga sumusunod:

COUNT (halaga1, halaga2,…)

HALIMBAWA:

Kung sakaling nais kong kalkulahin ang bilang ng mga cell na may hawak na mga numero mula sa talahanayan na nilikha ko sa nakaraang halimbawa, pipiliin ko lamang ang cell kung saan nais kong ipakita ang resulta at pagkatapos ay gamitin ang COUNT na function tulad ng sumusunod:

ROUND:

Upang maiikot ang mga halaga sa ilang mga tukoy na desimal na lugar, maaari mong magamit ang pag-andar ng ROUND. Ang pagpapaandar na ito ay ibabalik ang isang numero sa pamamagitan ng pag-ikot nito sa tinukoy na bilang ng mga desimal na lugar. Ang syntax ng pagpapaandar na ito ay ang mga sumusunod:

ROUND (numero, num_digits)

HALIMBAWA:

Paghahanap ng Grado:

Upang makahanap ng mga marka, kakailanganin mong gamitin ang mga nakapugad na KUNG pahayag sa Excel. Halimbawa, sa Karaniwang halimbawa, kinakalkula ko ang average na mga marka na nakuha ng mga mag-aaral sa mga pagsubok. Ngayon, upang hanapin ang Mga Grado na nakamit ng mga mag-aaral na ito, kakailanganin kong lumikha ng isang naka-salad na pag-andar KUNG tulad ng sumusunod:

Tulad ng nakikita mo, ang mga average marka ay naroroon sa haligi G. Upang makalkula ang marka, gumamit ako ng isang pugad na pormula KUNG. Ang code ay ang mga sumusunod:

= KUNG (G19> 90, ”A”, (KUNG (G19> 75, ”B”, KUNG (G19> 60, ”C”, KUNG (G19> 40, ”D”, ”F”))))

Pagkatapos gawin ito, kakailanganin mo lamang kopyahin ang formula sa lahat ng mga cell kung saan mo nais ipakita ang mga marka.

RANGGO:

Kung sakaling nais mong matukoy ang Ranggo na nakamit ng mga mag-aaral ng isang klase, maaari mong gamitin ang isa sa mga built-in na Formula ng Excel ie RANK. Ibabalik ng pagpapaandar na ito ang Ranggo para sa isang tinukoy na saklaw sa pamamagitan ng paghahambing ng isang naibigay na saklaw sa pataas o sa pababang pagkakasunud-sunod. Ang syntax ng pagpapaandar na ito ay ang mga sumusunod:

RANK (ref, numero, order)

HALIMBAWA:

Tulad ng nakikita mo, sa halimbawa sa itaas, kinakalkula ko ang Ranggo ng mga mag-aaral na gumagamit ng pag-andar ng Ranggo. Dito, ang unang parameter ay ang average marka na nakamit ng bawat mag-aaral at ang array ay ang average na nakamit ng lahat ng iba pang mga mag-aaral ng klase. Hindi ko tinukoy ang anumang order, samakatuwid, ang output ay matutukoy sa pababang pagkakasunud-sunod. Para sa mga pataas na ranggo ng order, kakailanganin mong tukuyin ang anumang hindi halaga na nonzero.

COUNTIF:

Upang mabilang ang mga cell batay sa ilang ibinigay na kundisyon, maaari mong gamitin ang isa sa built-in na Mga Formula ng Excel na tinatawag na 'COUNTIF'. Ang pagpapaandar na ito ay ibabalik ang bilang ng mga cell na nagbibigay-kasiyahan sa ilang kundisyon sa isang naibigay na saklaw. Ang syntax ng pagpapaandar na ito ay ang mga sumusunod:

COUNTIF (saklaw, pamantayan)

HALIMBAWA:

Tulad ng nakikita mo, sa halimbawa sa itaas, natagpuan ko ang bilang ng mga cell na mayroong mga halagang higit sa 80. Maaari ka ring magbigay ng ilang halaga ng teksto sa parameter ng pamantayan.

INDEX:

Ang pagpapaandar ng INDEX ay nagbabalik ng isang halaga o sanggunian ng cell sa ilang partikular na posisyon sa isang tinukoy na saklaw. Ang syntax ng pagpapaandar na ito ay ang mga sumusunod:

INDEX (array, row_num, haligi_num) o

INDEX (sanggunian,row_num, column_num, area_num)

Gumagana ang pagpapaandar ng index tulad ng sumusunod para sa Array form:

  • Kung ang parehong numero ng hilera at numero ng haligi ay ibinibigay, ibabalik nito ang halagang naroroon sa intersection cell
  • Kung ang halaga ng hilera ay nakatakda sa zero, ibabalik nito ang mga halagang naroroon sa buong haligi sa tinukoy na saklaw
  • Kung ang halagang haligi ay nakatakda sa zero, ibabalik nito ang mga halagang naroroon sa buong hilera sa tinukoy na saklaw

Gumagana ang pagpapaandar ng index tulad ng sumusunod para sa Sanggunian form:

  • Ibinabalik ang sanggunian ng cell kung saan ang linya ng mga hilera at haligi ay lumusot
  • Ipapahiwatig ng area_num kung aling saklaw ang gagamitin sakaling maraming mga saklaw ang naibigay

HALIMBAWA:

Tulad ng nakikita mo, sa halimbawa sa itaas, ginamit ko ang pagpapaandar ng INDEX upang matukoy ang halaga na naroroon sa ika-2 hilera at ika-4 na haligi para sa saklaw ng mga cell sa pagitan ng A18 hanggang G20.

Katulad nito, maaari mo ring gamitin ang pagpapaandar ng INDEX sa pamamagitan ng pagtukoy ng maraming sanggunian tulad ng sumusunod:

pagkakakonekta ng database sa java na may MySQL

Dinadala tayo nito sa pagtatapos ng artikulong ito sa Mga Formula at Pag-andar ng Excel. Sana malinaw ka sa lahat ng naibahagi sa iyo. Tiyaking nagsasanay ka hangga't maaari at ibalik ang iyong karanasan.

May tanong ba sa amin? Mangyaring banggitin ito sa seksyon ng mga komento ng 'Excel Formula at Function' na blog na ito at babalikan ka namin sa lalong madaling panahon.

Upang makakuha ng malalim na kaalaman sa anumang mga nagte-trend na teknolohiya kasama ang iba't ibang mga application nito, maaari kang magpatala nang live na may 24/7 na suporta at habambuhay na pag-access.