Microsoft Excel no és només un editor de fulls de càlcul, sinó també l'aplicació més potent per a diversos càlculs. Finalment, però no per això menys important, aquesta característica venia amb funcions integrades. Amb l'ajuda d'algunes de les funcions (operadors), és possible especificar fins i tot les condicions del càlcul, que se solen anomenar criteris. Apreneu més informació sobre com es poden utilitzar quan es treballa a Excel.
Aplicació de criteris
Els criteris són les condicions en què un programa realitza determinades accions. S'utilitzen en diverses funcions integrades. El seu nom més sovint conté l'expressió "SI". Aquest grup d’operadors, en primer lloc, s’hauria d’atribuir COUNTES, COMPARTIR, Suma, SUMMESLIMN. A més dels operadors integrats, els criteris en Excel també s'utilitzen en format condicional. Penseu en el seu ús quan es treballa amb diverses eines d’aquest processador de taula amb més detall.
COUNTES
La tasca principal de l'operador COUNTESpertanyent a un grup estadístic, és el recompte ocupat pels diferents valors de les cèl·lules que satisfan una determinada condició especificada. La seva sintaxi és la següent:
= COMPTES (rang, criteri)
Com podeu veure, aquest operador té dos arguments. "Interval" és l’adreça de la matriu d’elements de la fulla en què s’hauria de fer el recompte.
"Criteri" - Aquest és l’argument que estableix la condició que les cel·les de l’àrea especificada han de contenir per tal d’incloure-les. Com a paràmetre, es pot utilitzar una expressió numèrica, un text o una referència a la cel·la que conté el criteri. En aquest cas, per indicar el criteri, podeu utilitzar els següents caràcters: "<" ("menys"), ">" ("més"), "=" (igual), "" ("no igual"). Per exemple, si especifiqueu l’expressió "<50", llavors el càlcul tindrà en compte només els elements especificats per l’argument "Interval"en què hi ha valors numèrics inferiors a 50. L'ús d'aquests caràcters per especificar els paràmetres serà rellevant per a totes les altres opcions, que es tractaran en aquesta lliçó a continuació.
I ara fem una ullada a un exemple específic de com funciona aquest operador a la pràctica.
Per tant, hi ha una taula que mostra els ingressos de cinc botigues per setmana. Hem de conèixer el nombre de dies durant aquest període, en què els ingressos per vendes de la botiga 2 van superar els 15.000 rubles.
- Seleccioneu l’element de full en el qual l’operador mostrarà el resultat del càlcul. Després fes clic a la icona "Insereix la funció".
- Inicia Màsters de funcions. Mou a bloc "Estadística". Allà trobem i seleccionem el nom "COUNTES". Aleshores haureu de fer clic al botó "D'acord".
- Activació de la finestra d’argument de l’operador anterior. Al camp "Interval" indiqui l'àrea de cèl·lules entre les quals es farà el recompte. En el nostre cas, seleccioneu el contingut de la línia. "Botiga 2"en què es troben els valors dels ingressos per dia. Poseu el cursor al camp especificat i, mantenint el botó esquerre del ratolí, seleccioneu la matriu corresponent a la taula. L'adreça de la matriu seleccionada apareix a la finestra.
Al camp següent "Criteri" només heu d'establir el paràmetre de selecció immediat. En el nostre cas, només heu de comptar els elements de la taula en què el valor supera els 15.000. Per tant, utilitzant el teclat, introduïm una expressió al camp especificat. ">15000".
Després de fer totes les manipulacions anteriors, feu clic al botó. "D'acord".
- El programa calcula i mostra el resultat a l’element del full seleccionat abans de l’activació. Màsters de funcions. Com podeu veure, en aquest cas el resultat és igual al número 5. Això significa que en la matriu seleccionada en cinc cel·les hi ha valors superiors a 15.000, és a dir, podem concloure que a la botiga 2 en cinc dies des dels set analitzats, els ingressos van superar els 15.000 rubles.
Lliçó: Màster de funcions a Excel
COMPARTIR
La següent funció que gestiona els criteris és COMPARTIR. També pertany al grup estadístic d'operadors. Tasca COMPARTIR és el recompte de cel·les de la matriu especificada que satisfan un conjunt de condicions específiques. És el fet que podeu especificar no un, sinó diversos paràmetres, i distingeix aquest operador de l’anterior. La sintaxi és la següent:
= COUNTRY (condition_range1; condition1; condition_range2; condition2; ...)
"Rang de condicions" és idèntic al primer argument de la declaració anterior. És a dir, és un enllaç a l'àrea on es comptaran les cel·les que compleixin les condicions especificades. Aquest operador us permet especificar diverses àrees alhora.
"Condició" és un criteri que determina quins elements del conjunt de dades corresponents es comptaran i quins no. Cada regió de dades donada ha d’especificar una condició per separat, encara que coincideixi. És imprescindible que totes les matrius utilitzades com a àrees condicionals tinguin el mateix nombre de files i columnes.
Per tal de configurar diversos paràmetres de la mateixa àrea de dades, per exemple, per comptar el nombre de cel·les en què es troben els valors superiors a un nombre determinat, però inferiors a un altre, s’utilitza l’argument següent: "Rang de condicions" diverses vegades especifiqueu la mateixa matriu. Però al mateix temps que els arguments corresponents "Condició" ha d’especificar diferents criteris.
Utilitzant l’exemple de la mateixa taula amb vendes setmanals de botigues, vegem com funciona. Cal esbrinar el nombre de dies de la setmana en què els ingressos en tots els punts de venda especificats van arribar a la norma establerta per a ells. Els estàndards d’ingressos són els següents:
- Botiga de 1 a 14.000 rubles;
- Botiga de 2 a 15.000 rubles;
- Botiga de 3 a 24.000 rubles;
- Botiga de 4 a 11.000 rubles;
- Emmagatzemeu entre 5 i 32.000 rubles.
- Per dur a terme la tasca anterior, seleccioneu l'element del full de treball amb el cursor on es mostrarà el resultat del processament de dades. COMPARTIR. Fem clic a la icona "Insereix la funció".
- Anar a Auxiliar de funcions, tornant a bloquejar-se "Estadística". La llista hauria de trobar el nom COMPARTIR i fer-ne una selecció. Després de realitzar l’acció especificada, heu de fer clic al botó. "D'acord".
- Després de l’execució de l’algoritme d’acció anterior, s’obre una finestra d’argument. COMPARTIR.
Al camp "Condition Range1" heu d’inscriure l’adreça de la línia en què es troben les dades sobre els ingressos de la botiga 1 de la setmana. Per fer-ho, poseu el cursor al camp i seleccioneu la fila corresponent de la taula. Les coordenades es mostren a la finestra.
Tenint en compte que per a la taxa d’ingressos diaris de la botiga 1 és de 14.000 rubles, després al camp "Condició 1" introduïu l’expressió ">14000".
Als camps "Rang de condició2 (3,4,5)" S'han d'introduir les coordenades de les línies amb ingressos setmanals respectivament de Store 2, Store 3, Store 4 i Store 5. Realitzem l'acció segons el mateix algorisme que per al primer argument d'aquest grup.
Als camps "Condició2", "Condició3", "Condició4" i "Condició5" portem respectivament valors ">15000", ">24000", ">11000" i ">32000". Com podeu suposar, aquests valors corresponen a l’interval d’ingressos, superant la norma per al magatzem corresponent.
Després d'haver introduït totes les dades necessàries (10 camps en total), feu clic al botó "D'acord".
- El programa calcula i mostra el resultat a la pantalla. Com veiem, és igual al número 3. Això vol dir que en tres dies a partir de la setmana analitzada, els ingressos de tots els punts de venda superen la taxa establerta per a ells.
Ara canviem una mica la tasca. Cal calcular el nombre de dies en què Shop 1 va rebre ingressos superiors als 14.000 rubles, però menys de 17.000 rubles.
- Poseu el cursor a l’element on es mostrarà la sortida en un full de resultats de recompte. Fem clic a la icona "Insereix la funció" sobre l’àrea de treball del full.
- Des de fa poc hem aplicat la fórmula COMPARTIR, ara no cal anar al grup "Estadística" Màsters de funcions. El nom d’aquest operador es pot trobar a la categoria "10 usats recentment". Seleccioneu-lo i feu clic al botó. "D'acord".
- S'obre la finestra d’arguments de l’operador que ja ens és familiar. COMPARTIR. Poseu el cursor al camp "Condition Range1" i, després de fixar el botó esquerre del ratolí, seleccioneu totes les cel·les que contenen els ingressos per dies de la botiga 1. Es troben a la fila, que es diu "Botiga 1". Després d'això, es mostraran les coordenades de l'àrea especificada a la finestra.
A continuació, establiu el cursor al camp "Condició1". Aquí hem d’especificar el límit inferior dels valors de les cel·les que participaran en el càlcul. Especifiqueu una expressió ">14000".
Al camp "Rang de condicions 2" introduïm la mateixa adreça de la mateixa manera que vam entrar al camp "Condition Range1", és a dir, tornem a introduir les coordenades de les cèl·lules amb el resultat de la primera sortida.
Al camp "Condició2" especifiqueu el límit superior de selecció: "<17000".
Després de realitzar totes les accions especificades, feu clic al botó. "D'acord".
- El programa proporciona el resultat del càlcul. Com veiem, el valor total és de 5. Això significa que en 5 dies dels set estudiats, els ingressos de la primera botiga van ser d'entre 14.000 i 17.000 rubles.
Suma
Un altre operador que utilitza criteris és Suma. A diferència de les funcions anteriors, es refereix al bloc matemàtic dels operadors. La seva tasca és afegir dades a les cel·les que compleixin una condició específica. La sintaxi és:
= SUMMERS (rang; criteri; sum_range)
Argument "Interval" apunta a l'àrea de les cel·les que es comprovarà per complir. De fet, es defineix en el mateix principi que l’argument de la funció del mateix nom. COUNTES.
"Criteri" - és un argument obligatori que estableix el paràmetre per seleccionar cel·les de l’àrea de dades especificada que s’afegirà. Els principis d’especificar el mateix que els arguments similars d’operadors anteriors, que s’han considerat anteriorment.
"Interval de sumatori" - Aquest és un argument opcional. Indica la regió específica de la matriu en què es realitzarà la suma. Si ho ometeu i no ho especifiqueu, per defecte es considera que és igual al valor de l’argument requerit "Interval".
Ara, com sempre, considerem l’aplicació d’aquest operador a la pràctica. Basant-nos en la mateixa taula, ens trobem davant de la tasca de calcular l'import dels ingressos a la botiga 1 per al període que comença a partir de l'11.03.2017.
- Seleccioneu la cel·la on es mostrarà el resultat. Feu clic a la icona "Insereix la funció".
- Anar a Auxiliar de funcions en bloc "Matemàtica" busqueu i seleccioneu el nom "SUMMESLI". Fem clic al botó "D'acord".
- S'inicia la finestra d'argument de la funció. Suma. Té tres camps corresponents als arguments de l’operador especificat.
Al camp "Interval" entrem a l’àrea de la taula on es localitzaran els valors que s’han comprovat per complir les condicions. En el nostre cas, serà una sèrie de dates. Poseu el cursor en aquest camp i seleccioneu totes les cel·les que continguin dates.
Ja que només cal afegir els ingressos de l’11 de març, després al camp "Criteri" Conduïm en valor ">10.03.2017".
Al camp "Interval de sumatori" heu d’especificar l’àrea, els valors dels quals compleixin els criteris especificats es resumiran. En el nostre cas, aquests són els valors d’ingressos de la línia. "Shop1". Seleccioneu la matriu corresponent d'elements de full.
Després de la introducció de totes les dades especificades, feu clic al botó "D'acord".
- Després d'això, el resultat de la funció de processament de dades es mostrarà a l'element especificat anteriorment del full de treball. Suma. En el nostre cas, és igual a 47921,53. Això significa que a partir de l’11.03.2017 i fins al final del període analitzat, els ingressos totals per a la botiga 1 van ascendir a 47.921,53 rubles.
SUMMESLIMN
Completarem l’estudi dels operadors que utilitzen criteris, centrant-nos en la funció SUMMESLIMN. La tasca d’aquesta funció matemàtica és resumir els valors de les àrees indicades de la taula, seleccionades segons diversos paràmetres. La sintaxi d’aquest operador és:
= SUMMESLIMN (sum_range_range; condition_range1; condició1; condició_sala2; condició2; ...)
"Interval de sumatori" - Aquest és un argument que és l'adreça de la matriu, les cèl·lules en les quals, corresponents a un determinat criteri, s’afegiran.
"Rang de condicions" - l’argument que representa una matriu de dades, verificat el compliment de la condició;
"Condició" - L’argument que representa el criteri de selecció per a l’addició.
Aquesta funció implica operacions amb diversos conjunts d'operadors similars alhora.
Vegem com aquest operador és aplicable a la resolució de problemes en el context de la nostra taula de vendes en punts de venda. Haurem de calcular els ingressos aportats per la Botiga 1 per al període comprès entre el 9 de març i el 13 de març de 2017. En aquest cas, la suma dels ingressos només hauria de tenir en compte aquells dies, els ingressos dels quals van superar els 14.000 rubles.
- Seleccioneu de nou la cel·la per mostrar el total i feu clic a la icona. "Insereix la funció".
- In Auxiliar de funcionsPrimer de tot, ens movem al bloc. "Matemàtica", i allà seleccionem un element anomenat "SUMMESLIMN". Feu clic al botó "D'acord".
- Es posa en marxa la finestra dels arguments de l’operador, el nom del qual s’ha especificat anteriorment.
Situeu el cursor al camp "Interval de sumatori". A diferència dels arguments següents, es tracta d’un tipus i apunta a la matriu de valors on es farà la suma de les dades que coincideixin amb els criteris especificats. A continuació, seleccioneu l'àrea de la línia "Shop1"En què es col·loquen els valors dels ingressos de la sortida corresponent.
Després de mostrar l’adreça a la finestra, aneu al camp "Condition Range1". Aquí haurem de mostrar les coordenades de la cadena amb les dates. Fem un clip del botó esquerre del ratolí i seleccionarem totes les dates a la taula.
Poseu el cursor al camp "Condició1". La primera condició és que resumirem les dades abans del 9 de març. Per tant, introduïm el valor ">08.03.2017".
Mou a l’argument "Rang de condicions 2". Aquí heu d’introduir les mateixes coordenades que es van registrar al camp "Condition Range1". Ho fem de la mateixa manera, és a dir, ressaltant una línia amb les dates.
Situeu el cursor al camp "Condició2". La segona condició és que els dies per als quals s'afegiran ingressos no haurien de ser posteriors al 13 de març. Per tant, escriviu l’expressió següent: "<14.03.2017".
Aneu al camp "Rang de condicions 2". En aquest cas, hem de seleccionar la mateixa matriu, la direcció de la qual s’ha introduït com a matriu de sumatoris.
Després que l’adreça de la matriu especificada es mostri a la finestra, aneu al camp "Condició3". Tenint en compte que només s’aplicaran els valors que superen els 14.000 rubles, farem l’entrada següent: ">14000".
Després de l'última acció, feu clic al botó "D'acord".
- El programa mostra el resultat en un full. És igual a 62491,38. Això significa que per al període comprès entre el 9 i el 13 de març de 2017, l'import dels ingressos en afegir-los durant els dies en què supera els 14.000 rubles va ascendir a 62.491,38 rubles.
Format condicional
L’última, descrita per nosaltres, una eina, quan es treballa amb quins criteris s’utilitzen, és el format condicional. Realitza el tipus especificat de cel·les de format que compleixen les condicions especificades. Mireu un exemple de treballar amb format condicional.
Ressalteu les cel·les de la taula en blau, on els valors del dia superin els 14.000 rubles.
- Seleccioneu tota la matriu d’elements de la taula, que mostra els ingressos dels punts de venda al dia.
- Vés a la pestanya "Inici". Fem clic a la icona "Format condicional"situat en un bloc "Estils" a la cinta. S'obrirà una llista d'accions. Vam fer una posició en ell "Crea una regla ...".
- S'activa la finestra de generació de regles de format. Al camp de regles de selecció de tipus, seleccioneu el nom "Format només les cel·les que continguin". Al primer camp del bloc de condicions, seleccioneu la llista d’opcions possibles "Valor de la cel·la". Al camp següent, seleccioneu la posició "Més". En aquest darrer lloc, indiquem el valor en si, el més gran del qual és formatar els elements de la taula. Tenim 14.000. Per seleccionar el tipus de format, feu clic al botó. "Format ...".
- S'activa la finestra de format. Vés a la pestanya "Omplir". Des de les opcions de color de farciment proposades, seleccioneu blau fent clic amb ell amb el botó esquerre del ratolí. Després de mostrar el color seleccionat a la pàgina "Mostra"feu clic al botó "D'acord".
- Torna automàticament a la finestra de generació de regles de format. També a la zona "Mostra" es mostra en blau. Aquí hem de realitzar una sola acció: feu clic al botó "D'acord".
- Després de l'última acció, totes les cel·les de la matriu seleccionada, que conté un nombre superior a 14.000, s'ompliran de color blau.
Per obtenir més informació sobre les possibilitats de format condicional es descriu en un article separat.
Lliçó: Format condicional a Excel
Com veiem, amb l'ajut d'eines que utilitzen criteris en el seu treball, a Excel es poden resoldre tasques bastant diverses. Això pot ser comptar quantitats i valors i formatar, així com realitzar moltes altres tasques. Les principals eines que funcionen en aquest programa amb criteris, és a dir, amb determinades condicions en què aquesta acció està activada, són un conjunt de funcions integrades, així com formatació condicional.
