Formula Formal Formulas Excel

Ƙara yanayin tsarawa a Excel ba ka damar amfani da zaɓuɓɓukan tsarin tsarawa zuwa tantanin halitta ko kewayon kwayoyin da ke haɗu da ƙayyadadden yanayin da ka saita.

Za'a iya amfani da zaɓuɓɓukan tsarawa ne kawai lokacin da aka zaɓa waɗannan haɗuwar waɗannan yanayi.

Zaɓuɓɓukan tsarawa waɗanda za a iya amfani da su sun haɗa da lakabi da launi na launi, canje-canje, sassan launi, da kuma ƙara yawan lambobi zuwa bayanai.

Tun da Excel na 2007, Excel ta sami dama da zaɓuɓɓukan shigarwa don yanayin da aka saba amfani dashi kamar neman lambobi waɗanda suka fi girma ko žasa fiye da wani darajar ko gano lambobin da suke sama ko žasa da adadin kuɗi .

Bugu da ƙari da waɗannan zaɓuɓɓukan da aka riga aka saita, yana iya yiwuwar ƙirƙirar ka'idojin tsarin ka'ida ta al'ada ta hanyar amfani da takardun Excel don gwada don yanayin da aka ƙayyade masu amfani.

Aiwatar da Dokoki da yawa

Ƙari fiye da ɗaya mulki za a iya amfani da wannan bayanin don gwada yanayin daban-daban. Alal misali, bayanai na kasafin kuɗi na iya kasancewa yanayi wanda ya dace da canje-canjen tsarawa lokacin da wasu matakai - kamar 50%, 75%, da 100% - na kasafin kuɗi duka an kashe.

A irin waɗannan yanayi, Excel na farko ya ƙayyade idan ƙungiyoyi daban-daban na rikici, kuma, idan haka ne, shirin ya bi tsarin umarni na ƙayyadaddun don sanin wane doka ta tsara tsari yana amfani da bayanai.

Misali: Nemi Bayanan da ya wuce 25% da 50% Ƙara da Tsarin Yanayin

A cikin misali mai biyowa, za a yi amfani da ka'idojin tsara ka'idojin al'adu guda biyu zuwa ga kewayon sel B2 zuwa B5.

• Dokar farko ta duba idan bayanan da ke cikin sassan A2 zuwa A5 ya fi girma daidai a B2 zuwa B5 ta fiye da 25%.
• Tsarin mulki na biyu shine duba idan daidai wannan data a A2: A5 ya wuce adadin daidai a B2: B5 ta fiye da 50%.

Kamar yadda aka gani a cikin hoton da ke sama, idan ko dai daga cikin yanayin da ke sama ya zama gaskiya, launin launi na tantanin halitta ko sassan dake cikin kewayon B1: B4 zai canza.

• don bayanai inda bambanci ya fi 25%, launi na bayanan tantanin halitta zai canza zuwa blue.
• idan bambancin ya wuce 50% adadin layin salula zai canza zuwa ja.

Dokokin da ake amfani dashi don cika wannan aiki,

= (A2-B2) / A2> 25% = (A2-B2) / A2> 50%

za a shiga ta amfani da tsari na kwaskwarima na Sabuwar Sanya Sake .

Shigar da Bayanan Tutorial

1. Shigar da bayanai a cikin sel A1 zuwa C5 kamar yadda aka gani a cikin hoton da ke sama

Lura: Mataki na 3 na koyawa zai ƙara ƙwayoyin zuwa Kwayoyin C2: C4 wanda ke nuna ainihin bambancin bambancin tsakanin dabi'u a cikin kwayoyin A2: A5 da B2: B5 don duba daidaitattun ka'idojin tsarawa.

Tsayar da Dokokin Tsarin Sharuɗɗa

Kamar yadda aka ambata, za a shigar da ka'idodin tsarin ka'idojin da za'a bincika yanayin biyu ta amfani da tsari na kwakwalwa na New Formatting Policy.

Shirya tsarin tsarawa don samun fiye da kashi 25%

1. Sanya sassa B2 zuwa B5 a cikin takardun aiki.
2. Danna kan shafin shafin shafin rubutun.
3. Danna kan maɓallin Tsarin Yanayi a cikin rubutun don buɗe menu na saukewa.
4. Zabi Sabuwar Dokar don buɗe sabon maganganun Shafin zane kamar yadda aka gani a cikin hoto a sama.
5. A cikin rabin rabin zauren maganganu, danna kan zaɓin karshe: Yi amfani da wata mahimmanci don sanin wane ɓangaren ke tsarawa.
6. A cikin rabin rabi na akwatin maganganu, danna a cikin Tsarin dabi'un inda wannan ma'anar gaskiya ne: layi.
7. Rubuta ma'anar : = (A2-B2) / A2> 25% a cikin sarari da aka samar
8. Danna maɓallin Tsarin don buɗe akwatin maganganun Siffofin Siffar.
9. A cikin wannan maganganu, danna kan Fill tab kuma zaɓi launin launi mai launi.
10. Danna Ya yi sau biyu don rufe maganganun maganganu kuma komawa cikin aikin aiki.
11. A wannan batu, launin launi na sel B3 da B5 ya zama blue.

Ƙaddamar tsarin tsarawa don samun fiye da kashi 50%

1. Tare da sel B2 zuwa B5 har yanzu an zaba, maimaita matakan 1 zuwa 6 a sama.
2. Rubuta ma'anar: = (A2-B2) / A2> 50% a cikin sararin da aka samar.
3. Danna maɓallin Tsarin don buɗe akwatin maganganun Siffofin Siffar.
4. Danna kan Fill tab kuma zaɓi launin ja mai launi.
5. Danna Ya yi sau biyu don rufe maganganun maganganu kuma komawa cikin aikin aiki .
6. Sakamakon launi na tantanin halitta B3 ya kamata ya zama blue yana nuna cewa bambancin kashi tsakanin lambobi a cikin kwayoyin A3 da B3 ya fi 25% amma kasa da ko daidai da 50%.
7. Sakamakon launi na cell B5 ya kamata ya canza zuwa ja yana nuna cewa bambancin kashi tsakanin lambobi a cikin kwayoyin A5 da B5 ya fi 50%.

Binciken Dokokin Tsarin Yanayi

Kira% Difference

Don duba cewa ka'idodin tsarin sharaɗɗa sun shiga daidai, zamu iya shigar da samfurori cikin sassan C2: C5 wanda zai lissafta ainihin kashi bambanci tsakanin lambobi a cikin jeri A2: A5 da B2: B5.

1. Danna kan cell C2 don sa shi tantanin halitta mai aiki.
2. Rubuta a cikin tsari = (A2-B2) / A2 kuma danna maɓallin Shigar da ke keyboard.
3. Amsar 10% ya kamata ya bayyana a cell C2, yana nuna cewa adadin a cikin salula A2 shine 10% ya fi girma fiye da lamba a cell B2.
4. Yana iya zama wajibi don canja tsarin a kan cell C2 don nuna amsa azaman kashi.
5. Yi amfani da maɓallin cikawa don kwafe ƙwayar daga cell C2 zuwa kwayoyin C3 zuwa C5.
6. Amsoshin tambayoyin C3 zuwa C5 ya kamata: 30%, 25%, da 60%.
7. Amsoshin wadannan kwayoyin sun nuna cewa ka'idodin tsarin sharaɗɗa sunyi daidai tun lokacin bambancin tsakanin kwayoyin A3 da B3 ya fi 25% kuma bambancin tsakanin kwayoyin A5 da B5 ya fi 50%.
8. Cell B4 bai canja launi ba saboda bambancin dake tsakanin sel A4 da B4 daidai da 25%, kuma tsarin mulkin mu na ƙayyadaddun ƙayyade cewa kashi fiye da 25% ana buƙata don launin launi don canzawa zuwa blue.

Dokar Shirin Tsarin Dokokin Tsarin Yanki

Aiwatar da Tsarin Dokar Tsarin Yanayin Yanayi

Idan ana amfani da dokoki masu yawa zuwa wannan nau'in bayanai, Excel na farko ya yanke shawarar idan rikici ya shafi.

Sharuɗɗan rikice-rikice sune wadanda inda zaɓin zaɓuɓɓukan zaɓaɓɓe ga kowace mulkin bazai iya amfani da waɗannan bayanai ba .

A misali da aka yi amfani da shi a cikin wannan koyo, ka'idoji sunyi rikici tun lokacin da waɗannan dokoki sunyi amfani da wannan tsari - wanda ke canza launin launi na baya.

A halin da ake ciki inda doka ta biyu ta kasance gaskiya (bambanci a darajar ya fi 50% tsakanin kwayoyin halitta) to, ka'idar farko (bambancin da yake da ita fiye da 25%) gaskiya ne.

Dokar Excel ta Tsayawa

Tun da tantanin halitta ba zai iya samun launin ja da launin shudi ba a lokaci guda, Excel yana bukatar ya san wane tsarin mulki ya kamata ya yi amfani.

Wadanne mulkin da aka yi amfani da ita shine tsari na Excel ya tsara, wanda ya nuna cewa tsarin da ya fi girma cikin jerin a cikin akwatin maganganu na Ƙaddamarwa na Dokokin Yanayi yana da daidaito.

Kamar yadda aka nuna a cikin hoton da ke sama, ka'idoji na biyu da aka yi amfani da shi a cikin wannan koyawa (= (A2-B2) / A2> 50%) ya fi girma a cikin jerin kuma, sabili da haka, yana da daidaito a kan tsarin farko.

A sakamakon haka, canza launi na cell B5 ya canza zuwa ja.

Ta hanyar tsoho, ana ƙara sababbin dokoki zuwa saman jerin kuma, sabili da haka, suna da matsayi mafi girma.

Don canza umarnin ƙaddamarwa amfani da maɓallin arrow da Down a cikin akwatin maganganu kamar yadda aka gano a cikin hoto a sama.

Aiwatar da Dokokin Kuskuren

Idan ka'idodi biyu masu sauƙi ko sauƙaƙe ba rikici ba duka ana amfani da su idan yanayin kowace mulki shine gwaji ya zama gaskiya.

Idan ka'idojin tsari na farko da ke cikin misalinmu (= (A2-B2) / A2> 25%) tsara tsarin tarin sel B2: B5 tare da launi mai launin baka maimakon launin launi mai launin shudi, ka'idodin tsari guda biyu ba zai rikice ba tun duka samfurorin za a iya amfani da su ba tare da tsangwama tare da sauran ba.

A sakamakon haka, sel B5 yana da launi mai launi da launin ja, saboda bambanci tsakanin lambobi a cikin sel A5 da B5 ya fi duka 25 zuwa 50 bisa dari.

Tsarin Yanayi vs. Sauya Tsarin

Idan akwai rikice-rikice tsakanin ka'idojin tsarin sharaɗi da kuma zaɓuɓɓukan tsarawa da hannu, tsarin mulki na matsakaici yana daukan ci gaba kuma za a yi amfani da shi maimakon kowane zaɓi da aka tsara da hannu.

Idan an fara amfani da launin launi na launin rawaya a cikin sassan B2 zuwa B5 a misali, da zarar an kara ka'idojin gyaran yanayin, kawai kwayoyin B2 da B4 za su kasance rawaya.

Saboda ka'idodin yanayin gyaran yanayi sun shafi sel B3 da B5, launuka masu launin su canza daga rawaya zuwa launin shudi da ja kamar haka.