Paano Gumawa ng Mga Conditional Formula sa Excel: Kumpletong Gabay na may Mga Halimbawa, Trick, at Application

Master ang mga kondisyong formula sa Excel Ito ay, ngayon, isang mahalagang kasanayan para sa mga nagtatrabaho sa data sa pang-araw-araw na batayan gayundin para sa mga gustong pataasin ang kanilang antas ng organisasyon at pagiging produktibo sa anumang larangan. Malamang na natagpuan mo ang iyong sarili na nangangailangan na magdagdag lamang ng ilang mga halaga, magbilang ng mga tala na nakakatugon sa isang partikular na kundisyon, o awtomatikong i-highlight ang impormasyon sa isang spreadsheet. Ang magandang balita ay pinapayagan ka ng Excel na gawin ang lahat ng ito—at higit pa—sa isang simple, mabilis, at napaka-flexible na paraan salamat sa mga kondisyong pag-andar.

Sa artikulong ito, matutuklasan mo kung paano gumagana ang mga formula na ito, para saan talaga ang mga ito, at kung paano mo maisasama ang mga ito sa iyong daloy ng trabaho upang makatipid ng hindi mabilang na oras. Magpapakita kami sa iyo ng malinaw at praktikal na mga halimbawa, sasagutin ang mga karaniwang tanong, at magbubunyag ng mga trick na ginagamit ng mga eksperto upang dalhin ang pamamahala ng data sa susunod na antas. Maging komportable at buksan ang Excel—nangangako ang tour na ito na makakatulong! para sa mga nagsisimula at pareho silang advanced!

Ano ang conditional formula sa Excel at bakit dapat mong matutunan kung paano gamitin ang mga ito?

ang mga kondisyong formula sa Excel ay nagpapahintulot sa iyo na gumanap mga kalkulasyon o aksyon lamang kung ang isang partikular na kundisyon ay natutugunan. Gumagana ang mga ito bilang isang uri ng lohikal na tanong: kung ang ibinibigay ay totoo, ang isang resulta ay ginawa; kung mali, iba. Hindi lamang nito ginagawang mas matalino ang iyong mga data sheet, ngunit nagbibigay-daan din sa iyong i-automate ang mga proseso, maiwasan ang mga manu-manong error, at pag-aralan ang malalaking volume ng data nang may kahanga-hangang kahusayan.

Kapag ang isang tao ay nagsimulang matutong magprograma, ang mga kondisyonal na pahayag (tulad ng IF-ELSE) ay karaniwang ang unang bagay na kanilang pinag-aaralan. Sa Excel, ang katumbas ay ang mga kundisyon na maaari nating itatag gamit ang mga formula, na nagreresulta sa mahalaga para sa pagsusuri ng datos, paggawa ng desisyon at propesyonal na visualization ng mga resulta. Ang IF function Ito ang pinakakilala sa lahat, ngunit sa katotohanan ang uniberso ng mga kondisyon ay may kasamang marami pang iba: COUNTIF, SUMIF, AVERAGEIF at ang kanilang mga variant ayon sa mga set, bilang karagdagan sa mga kumbinasyong may mga lohikal na function (AT, O, HINDI).

Paano makakatulong sa iyo ang pag-alam kung paano gawin ang mga formula na ito? Mula sa pag-automate ng mga ulat, pag-uuri ng mga tala, pagkontrol sa imbentaryo, pag-detect ng mga error, pag-highlight ng mga pattern at trend, hanggang sa pagtaas ng iyong mga pagkakataong makakuha ng mas magandang trabaho! Ang Excel ay isa sa mga pinaka-in-demand na tool sa market ng trabaho, at ang pag-master ng mga conditional function nito ay maaaring gumawa ng lahat ng pagkakaiba.

Ano nga ba ang ginagamit ng mga conditional function?

ang mga kondisyong pag-andar Idinisenyo ang mga ito upang magsagawa ng matematika, istatistika, o pang-organisasyong pagpapatakbo sa isang hanay ng data, ngunit sa ilalim lamang ng ilang pamantayan na iyong tinutukoy. Ang mga ito ay kapaki-pakinabang para sa parehong mga simpleng pang-araw-araw na kaso at kumplikadong propesyonal na mga proyekto sa pagsusuri. Tingnan natin ang ilan sa mga pinakakaraniwang application nito:

• I-automate ang mga paulit-ulit na kalkulasyon: Pinaliit ng mga ito ang mga error at nakakatipid ng oras sa pamamagitan ng pagpapahintulot sa iyong magsagawa ng mga operasyon sa ilang segundo na kung hindi man ay kailangan mong manu-manong gumanap nang paulit-ulit.
• I-filter at suriin ang nauugnay na data: Gamit ang mga ito, posibleng kunin lamang ang impormasyong nakakatugon sa ilang partikular na kundisyon, halimbawa, mga benta sa itaas ng isang tiyak na limitasyon, mga empleyadong kabilang sa isang partikular na departamento, mga paparating na petsa ng pag-expire, atbp.
• Kilalanin ang mga pattern at uso: Kapag pinagsama mo ang mga conditional na formula sa mga visual na tool tulad ng conditional formatting, maaari mong i-highlight ang mga extreme value, outlier, kagustuhan ng customer, kritikal na petsa, at higit pa sa isang sulyap.
• gumawa ng matalinong mga desisyon: Sa pamamagitan ng pagpapahintulot sa iyong pag-aralan ang malaking halaga ng impormasyon mula sa iba't ibang anggulo, tinutulungan ka nitong ibase ang iyong mga konklusyon at aksyon sa data, hindi sa mga pagpapalagay.
• Gumawa ng mga dashboard at scorecard: Ang pinakakapaki-pakinabang at visual na mga dashboard ay halos palaging umaasa sa mga kondisyon upang ipakita ang mga pangunahing tagapagpahiwatig sa isang dynamic at automated na paraan.

Hindi nakakagulat na, ngayon, sila ay itinuturing na isang pangunahing kasanayan sa opisina, administratibo, pananalapi, logistik, kontrol sa kalidad, mapagkukunan ng tao, at iba pang mga posisyon. Sa loob at labas ng trabaho, ang pag-alam kung paano gumamit ng mga kondisyon ay magpapadali sa iyong buhay..

Anong mga uri ng conditional formula ang umiiral sa Excel?

Ang Excel ay nagsasama ng ilan mga function ng conditional series, ang bawat isa ay idinisenyo upang malutas ang mga pang-araw-araw na problema gaya ng pagdaragdag, pagbibilang, pag-a-average, paghahanap, paghahambing, o pagbabalik ng impormasyon batay sa naka-customize na pamantayan. Maaari din silang pagsamahin sa isa't isa upang makamit ang mas advanced na mga resulta at umangkop sa lalong hinihingi na mga gawain.

Ito ang mga pangunahing:

• IF function: Nagsasagawa ng lohikal na pagsusuri at nagbabalik ng isang resulta kung ito ay TAMA at isa pa kung ito ay MALI.
• COUNTIF function: Binibilang kung gaano karaming mga cell sa isang hanay ang nakakatugon sa isang kundisyon.
• SUMIF Function: Nagsusuma ng mga halaga ng cell na nakakatugon sa isang tinukoy na kinakailangan.
• AVERAGEIF function: Kinakalkula ang average ng mga halaga ng cell na nakakatugon sa isang pamantayan.
• Mga function ayon sa set: Binibigyang-daan ka ng COUNTIFS, SUMIFS at AVERAGEIFS na maglapat ng ilang pamantayan nang sabay-sabay, lubhang kapaki-pakinabang sa mga kumplikadong pagsusuri.
• Mga lohikal na function at operator: Y, O, HINDI nagbibigay-daan sa iyo na bumuo ng marami o higit pang mga sopistikadong kondisyon.

Bukod pa rito, maaari kang mag-nest ng mga function—iyon ay, gamitin ang isa sa loob ng isa upang subukan ang mga naka-chain na kundisyon o mga hierarchy ng desisyon—isang pangunahing kasanayan pagdating sa pag-uuri o pagpapangkat ng data sa iba't ibang kategorya.

Pag-unawa sa pagkakaiba sa pagitan ng "pamantayan" at "kondisyon" sa Excel

Sa konteksto ng Excel, mahalagang makilala ang pagitan kondisyon y pamantayan:

• Condición: ay tumutukoy sa isang kumpletong lohikal na expression na may operator at halaga (halimbawa: A2 <= 3). Ginagamit ito sa mga function tulad ng IF, AND, OR, NOT, atbp.
• Pamantayan: ay ang partikular na bahagi na binibilang ng Excel bilang isang filter kapag nagbibilang, nagsusuma, o nag-a-average (halimbawa, ">50" sa COUNTIF).

Ang mga pamantayan ay karaniwang nakapaloob sa mga panipi., at maaaring kasing simple ng isang eksaktong numero o kasing paliwanag ng isang reference at isang lohikal na operator na magkasama.

Ang function ng IF: ang panimulang punto para sa mga conditional na formula

La IF function Ito ay, walang alinlangan, ang batayan ng lahat ng mga kondisyong formula sa Excel. Ito ay ginagamit sa isang malaking iba't ibang mga konteksto, at ito ay katumbas ng IF ng programming klasiko.

Ang syntax nito ay:

=IF(logical_test;value_if_true;value_if_false)

Ang bawat isa sa mga elementong ito ay gumaganap ng isang papel:

• logical_test: kung saan mo tutukuyin ang kundisyon na susuriin (halimbawa, A1 > 50).
• halaga_kung_totoo: ano ang dapat mangyari kung totoo ang kundisyon (maaaring text, numero, operasyon, o kahit isa pang function).
• halaga_kung_mali: ano ang mangyayari kung mali ang kundisyon.

Simpleng halimbawa: Kung mayroon kang column ng mga marka (B2) at gusto mong markahan bilang "Pass" ang mga may 60 o higit pa, at "Fail" ang iba:

=IF(B2>=60; "Pass"; "Fail")

Maaari mong kopyahin ang formula na ito pababa upang suriin ang isang buong listahan ng mga marka, at baguhin ang kundisyon o teksto kung kinakailangan. At kung ang kailangan mo ay mag-classify sa higit sa dalawang kategorya, basta pugad ng higit sa isang IF function sa loob ng isa pa:

=IF(B2>=90; "Excellent"; IF(B2>=60; "Pass"; "Fail"))

Ang pinakakaraniwang mga error kapag gumagamit ng IF ay kadalasang dahil sa hindi saradong mga panaklong, maling mga sanggunian, o hindi kumpletong mga argumento. Kung nakatanggap ka ng hindi inaasahang 0 o isang #NAME? error, suriing mabuti ang formula.

Kaugnay na artikulo:

Karamihan sa mga karaniwang error sa mga formula ng Excel at kung paano ayusin ang mga ito

Pinagsasama-sama ang mga conditional function: KUNG may AT, O at HINDI

Sa mas kumplikadong mga sitwasyon, maaaring kailanganin mong pag-aralan ang higit sa isang kundisyon sa parehong oras. Ito ay kung saan maaari mong pagsamahin KUNG may mga lohikal na function tulad ng AT, O at HINDI.

• =IF(AND(cond1; cond2); value1; value2): Kung ang parehong mga kundisyon ay totoo lamang ang aksyon ay isinasagawa.
• =IF(OR(cond1; cond2); value1; value2): : Sapat na ang isang kundisyon ay totoo para mailapat ito.
• =IF(NOT(cond1); value1; value2): Ibinabalik ang kabaligtaran ng tinukoy na lohikal na pagsubok.

Halimbawa, ang pumasa lamang kung ang grado ay mas mataas sa 60 at ang pagdalo ay mas mataas sa 80%:

=IF(AND(B2>=60;C2>80);»Pumasa»;»Hindi pumasa»)

Ang kakayahang umangkop ng mga lohikal na function ay nagbibigay-daan sa iyo na lumikha ng mga hierarchy ng desisyon at mga advanced na panuntunan na nagdaragdag ng napakalaking kapangyarihan sa iyong mga spreadsheet.

Ang COUNTIF at COUNTIFS ay gumagana para sa pagbibilang batay sa mga kundisyon

Kapag kailangan mong malaman kung gaano karaming mga tala ang nakakatugon sa isang kinakailangan, ang COUNTIF function ay ang perpektong kaalyado. Ang syntax nito ay:

=COUNTIF(saklaw; pamantayan)

Halimbawa, upang mabilang kung ilang beses lumalabas ang apelyido na "Martínez" sa isang listahan:

=COUNTIF(A2:A10; "Martinez")

Maaari mong gamitin ang mga cell reference sa halip na direktang text, at maglapat pa ng mas advanced na pamantayan, gaya ng mas malaki kaysa, mas mababa sa, katumbas ng cell, partikular na text, atbp.

Kung gusto mong bilangin ang mga cell na nagsalubong ilang pamantayan nang sabay-sabay, ang naaangkop na function ay SET NG COUNTIF. Ang istraktura nito ay:

=COUNTIFS(range1; criterion1; range2; criterion2; …)

Halimbawa, upang malaman kung gaano karaming mga produkto ang "Beige" at mayroon ding dami na katumbas ng 1:

=COUNTIFS(A2:A10; "Beige"; B2:B10; 1)

SUMIF at SUMIFS function para sa kondisyonal na karagdagan

Kapag ang tanong ay Ano ang kabuuan ng mga halaga na nakakatugon sa isang kundisyon? ang SUMIF function ay pumapasok:

=SUMIF(saklaw; pamantayan; )

El rango ay kung saan ilalapat ang filter, ang pamantayan ang itinatag na kondisyon, at sum_range (opsyonal) ay ang lugar na talagang idadagdag. Kung hindi tinukoy, idaragdag ang mismong hanay ng paghahanap. Halimbawa, upang idagdag ang mga benta ng salesman na si Perez:

=SUM.IF(A2:A20; «Pérez»; B2:B20)

Kung mayroong higit sa isang kundisyon, ang naaangkop na function ay SUM.IF.SET. Ang istraktura nito ay:

=SUMIF(sum_range; criteria_range1; criteria1; criteria_range2; criteria2; …)

Sa ganitong paraan maaari mong dagdagan, halimbawa, ang lahat ng mga benta na higit sa 500 euro sa buwan ng Marso:

=SUMIF(B2:B20; C2:C20; ">500"; D2:D20; "Marso")

AVERAGEIF at AVERAGEIFS: pagkalkula ng kondisyonal na paraan

Ang mga formula para sa pagkalkula ang average ng isang hanay ayon sa isang kondisyon Sinusunod nila ang parehong lohika:

=AVERAGEIF(saklaw; pamantayan; )

Halimbawa, upang kalkulahin ang average na suweldo ng mga empleyado na may apelyido na "Perez":

=AVERAGE.IF(A2:A100; «Pérez»; B2:B100)

At kung kailangan mo ng higit sa isang kundisyon upang matugunan, gamitin ang AVERAGEIFS.

Mga lohikal na function AT, O, at HINDI na may kondisyong pag-format

Bilang karagdagan sa paggamit ng mga ito upang kalkulahin o pag-uri-uriin, ang mga lohikal na pag-andar na AT, O at HINDI ay malawakang ginagamit din awtomatikong i-format ang mga cell na may kondisyon. Maaari kang matuto nang higit pa tungkol sa kung paano ilapat ang mga trick na ito sa aming tutorial sa Mga tip at trick para masulit ang Windows calculator.

Mula sa tab pagtanggap sa bagong kasapi - Kondisyunal na format - Bagong panuntunan, maaari mong piliin ang "Gumamit ng formula upang matukoy kung aling mga cell ang ipo-format" at magsulat ng formula na tulad nito:

• =AT(A2>10;B2<5)
• =O(A2=»Oo»;B2=»Hindi»)
• =HINDI(A2>50)

Nagbibigay-daan ito, halimbawa, na awtomatikong mag-highlight sa mga pulang selula na nakakatugon sa ilang kundisyon nang sabay-sabay. Ito ang pinakamahusay na paraan upang matukoy ang pangunahing data nang walang kahirap-hirap.

Mga karaniwang problema at kung paano malutas ang mga ito

Ano ang gagawin ko kung ang aking function ay nagbabalik ng 0 o isang error? Sa maraming pagkakataon, ang mga error ay dahil sa mga maling sanggunian, hindi wastong pagsasara ng mga panaklong, o inalis na mga argumento. Kung makakita ka ng hindi inaasahang zero, tingnan kung wala sa tatlong argumento sa SI ang nawawala. Kung lilitaw ang isang #NAME?, kadalasang nagpapahiwatig ng error sa pag-type o ang paggamit ng mga maling kuwit/semicolon depende sa mga setting ng rehiyon.

Upang maiwasan ito, palaging sumulat ng mga function gamit ang function wizard. mga formula ng excel at isa-isang suriin ang mga argumento.

Kaugnay na artikulo:

Mahahalagang formula at function sa Excel

Isaac

Masigasig na manunulat tungkol sa mundo ng mga byte at teknolohiya sa pangkalahatan. Gustung-gusto kong ibahagi ang aking kaalaman sa pamamagitan ng pagsusulat, at iyon ang gagawin ko sa blog na ito, ipakita sa iyo ang lahat ng mga pinaka-kagiliw-giliw na bagay tungkol sa mga gadget, software, hardware, teknolohikal na uso, at higit pa. Ang layunin ko ay tulungan kang mag-navigate sa digital na mundo sa simple at nakakaaliw na paraan.