Mēs izceļam šūnas programmā Excel, lai mēs varētu viegli noteikt datu fokusa jomas. Šūnu formatēšana programmā Excel tiek veikta arī datu vērtību vizualizēšanai, pamatojoties uz krāsu un stilu. Piemēram, iespējams, vēlēsities koncentrēties uz 3 mazākajām datu vērtībām. Tas ir grūti izdarāms lielos datos, bet, ja jūs varētu pateikt programmai Excel izcelt 3 zemākās vērtības, tas būtu pavisam vienkārši. Par laimi, mēs to varam izdarīt, izmantojot nosacījuma formatēšanas opciju programmā Excel.
Ir viegli vienkārši izcelt 3 mazākās vērtības programmā Excel, taču, pievienojot kritērijus, kļūst nedaudz sarežģīti. Šajā rakstā mēs iemācīsimies izcelt vismazākās 3 vērtības ar nosacījumu.
Vispārējā formula nosacījuma formēšanā
= UN (pārbaudes šūna = kritēriji, vērtības šūna<= SMALL (IF (kritēriju diapazons = kritēriji, vērtību diapazons),3))
Pārbaudīt šūnu: Tā ir šūna, kuru vēlaties pārbaudīt, vai tā atbilst pirmajiem kritērijiem (kategorija).
Kritēriji: Kritēriji, kurus vēlaties filtrēt.
Vērtības šūna: Šūna satur skaitlisku vērtību. Mēs salīdzināsim to ar trešo mazāko vērtību savā kategorijā vērtību diapazons.
Kritēriju diapazons: Tas ir absolūtais diapazons, kas satur kritērijus.
Vērtību diapazons: Diapazons, kurā ir visas vērtības.
Apskatīsim piemēru, lai lietas būtu skaidras.
Piemērs. Iezīmējiet trīs nodaļas pārdošanas rezultātus, ko veica 1. nodaļa
Šeit man ir darba faila paraugs, kurā ir dažādu departamentu pārdošana dažādos mēnešos.
Es vēlos izcelt pirmās trīs nodaļas pārdošanas apjomus.
Identificēsim šeit esošos mainīgos.
Pārbaudīt šūnu: B2 $ (mēs vēlamies pārbaudīt, vai B2 ir "koncesija 1". Tā kā mēs nevēlamies, lai kolonna mainītos, mēs izmantojam $ zīmi. Tas izceļ visu rindu.)
Kritēriji: "1. nodaļa"
Vērtības šūna: C2 USD
Kritēriju diapazons: $ B $ 2: $ B $ 12
Vērtību diapazons: 2 ASV dolāri: 12 ASV dolāri
Tagad, kad mēs zinām visus argumentus, kas mums jānodod formulai, ir pienācis laiks ieviest iepriekš minēto vispārīgo formulu datu kopā.
- Atlasiet datus. Doties mājās ? nosacījuma formatējums? Jauns noteikums
- Atlasiet opciju "Izmantot formulu, lai noteiktu, kuru šūnu formatēt".
- Tālāk tekstlodziņā ierakstiet šo formulu
= UN ($ B2 = "Tirdzniecības vieta 1", $ C2 <= SMALL (IF ($ B $ 2: $ B $ 12 = "Dīleri 1", $ C $ 2: $ C $ 12), 3))
Nospiediet pogu Labi. Un tas ir izdarīts. Tika izcelti trīs apakšējie vai, teiksim, vismazākie 3 pārdošanas apjomi no 1. nodaļas.
Kā tas darbojas?
Sadalīsim formulu no iekšpuses.
Šī formula ir masīva formula, bet nosacījuma formatēšanā nav nepieciešama taustiņu kombinācija CTRL+SHIFT+ENTER.
Tā kā mums ir divi nosacījumi, pirmais ir Dīleri 1, un otrais ir vērtības, kas ir mazākas vai vienādas ar 3. mazāko vērtību Tirdzniecības centrā 1. Abiem jābūt patiesiem. Šim nolūkam mēs izmantojam funkciju AND.
$ B2 = "Koncesija 1"
Pirmos kritērijus ir viegli pārbaudīt. Mēs vienkārši ievietojām šo paziņojumu $ B2 = "Koncesija 1". Tā būs PATIESA, ja pašreizējā šūna B2 satur Izplatīšana 1.
C2 $ <=MAZS(IF($ B $ 2: $ B $ 12 = "Tirdzniecības vieta 1", $ C $ 2: $ C $ 12), 3)
Otrajiem kritērijiem mums jāfiltrē Dīleri 1 un jāatrod tajā 3. mazākā vērtība. Pēc tam salīdziniet kritēriju šūnu ar 3. mazāko vērtību. Tas tiek darīts, izmantojot paziņojumu $ C2 <= SMALL (IF ($ B $ 2: $ B $ 12 = "Dīleri 1", $ C $ 2: $ C $ 12), 3)
Kad mēs to novērtējam, šī formula atrisināsies šādi:
Mēs pārbaudām diapazonu $ B $ 2: $ B $ 12 un iegūstam šūnas, kas satur "Dīleri 1", izmantojot paziņojumu $ B $ 2: $ B $ 12 = "Koncesija 1". Mums būs daudz patiesu un nepatiesu.
$ C2 <= SMALL (JA ({TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE}, $ C $ 2: $ C $ 12), 3)
Katrai patiesībai vērtība tiks atgriezta no vērtību diapazona $ C $ 2: $ C $ 12, un mums būs cits masīvs.
$ C2 <= MAZS ({12; FALSE; 25; FALSE; FALSE; FALSE; FALSE; 18; FALSE; FALSE; 14}, 3)
Tagad funkcija SMALL atgriezīs trešo mazāko vērtību no šī masīva. (FLASE tiks ignorēts).
Tagad mums ir:
C2 $ <= 18
Visbeidzot, formula ir
= UN ($ B2 = "Koncesija 1", C2 $ <= 18)
Tagad tas pārbauda, vai B2 satur Izplatīšana 1. Tā ir. Tāpēc tas ir PATIESI
= UN (TRUE, C2 $ <= 18)
Tālāk tā pārbauda, vai C2 ir mazāks par vai vienāds ar 18 (trešais mazākais pārdošanas apjoms dīlerā 1). Tā ir PATIESA (12).
= UN (PATIESA, PATIESA)
Tā kā abi apgalvojumi ir TRUE, formula atgriež TRUE, un mēs pirmo rindu izceļam ar krāsu. Tas pats notiek katrā rindā.
Fewww !!! To bija grūti izskaidrot. Es ceru, ka es biju pietiekami skaidrs, lai jūs saprastu metodi. Praksē tas ir viegli.
Šeit mēs izmantojām kodētus kritērijus, bet mēs varam arī norādīt šūnu atsauci. Tas padarīs formulu elastīgāku.
Lūk, priekš Kritēriji Esmu izmantojis E1 šūnu atsauci. Neatkarīgi no tā, kurš izplatītājs jums ir E1, tiks izceltas trīs mazākās vērtības no šī izplatītāja.
Nosacījuma formatējuma formula ir šāda:
= UN ($ B2 = $ E $ 1, $ C2 <= SMALL (JA ($ B $ 2: $ B $ 12 = $ E $ 1, $ C $ 2: $ C $ 12), 3))
Jā, šādā veidā jūs varat izcelt trīs apakšējās vērtības ar nosacījumu Excel. Es ceru, ka raksts bija noderīgs. Ja jums ir kādi jautājumi, lūdzu, izmantojiet zemāk esošo komentāru sadaļu. Jūs varat uzdot jautājumus, kas saistīti ar šo rakstu vai jebkuru citu ar Excel saistītu rakstu. Mēs labprāt jums palīdzēsim.
Nosacījuma formatējums, pamatojoties uz citu šūnas vērtību
IF un nosacījuma formatēšana
Nosacījuma formatēšana ar formulu 2016
Nosacījuma formatēšana, izmantojot VBA programmā Microsoft Excel
Iezīmējiet šūnas, kurās ir noteikts teksts
Kā apkopot vairākas kolonnas ar nosacījumu
Populāri raksti:
50 Excel saīsnes, lai palielinātu produktivitāti
Funkcija VLOOKUP programmā Excel
COUNTIF programmā Excel 2016
Kā lietot SUMIF funkciju programmā Excel
