Quan es treballa amb taules d'Excel, sovint és necessari seleccionar-les segons un determinat criteri o en diverses condicions. El programa pot fer-ho de diverses maneres utilitzant diverses eines. Anem a esbrinar com es mostra a Excel utilitzant diverses opcions.
Mostreig
El mostreig de dades consisteix en el procediment de selecció de la matriu general d'aquests resultats que compleixen les condicions especificades, amb la seva sortida posterior en un full en una llista separada o en el rang inicial.
Mètode 1: utilitzeu el filtre automàtic avançat
La manera més senzilla de fer una selecció és utilitzar el filtre automàtic avançat. Penseu en com fer-ho amb un exemple específic.
- Seleccioneu l'àrea de la fulla, entre les dades que voleu provar. A la pestanya "Inici" feu clic al botó "Ordenar i filtrar". Es col·loca al bloc de configuració. Edició. A la llista que s’obre després d’aquesta, feu clic al botó. "Filtre".
És possible fer-ho d'una altra manera. Per fer-ho, després de seleccionar l'àrea de la fulla, aneu a la pestanya "Dades". Feu clic al botó "Filtre"que es publica en una cinta d'un grup "Ordenar i filtrar".
- Després d’aquesta acció, apareixeran les icones a l’encapçalament de la taula per començar a filtrar-se en forma de petits triangles al revés a la vora dreta de les cel·les. Feu clic a aquesta icona al títol de la columna en la qual volem fer una selecció. Al menú d'inici, feu clic a l'element "Filtres de text". A continuació, seleccioneu la posició "Filtre personalitzat ...".
- S'activa la finestra de filtratge personalitzada. És possible establir un límit sobre el qual es farà la selecció. A la llista desplegable de la columna que conté les cel·les de format numèric, que utilitzem com a exemple, podeu triar un dels cinc tipus de condicions:
- iguals;
- no igual;
- més;
- major o igual;
- menys
Posem la condició com a exemple per tal que només puguem seleccionar valors pels quals la quantitat d’ingressos supera els 10.000 rubles. Poseu el commutador en posició "Més". Introduïu el valor al marge dret "10000". Per realitzar una acció, feu clic al botó. "D'acord".
- Com podeu veure, després de filtrar, només hi ha línies en què la quantitat d’ingressos supera els 10.000 rubles.
- Però a la mateixa columna podem afegir la segona condició. Per fer-ho, torneu a la finestra de filtre personalitzada. Com podeu veure, a la part inferior hi ha un altre commutador de condicions i el camp d’entrada corresponent. Ara fixem el límit de selecció superior de 15.000 rubles. Per fer-ho, configureu el commutador a la posició "Menys", i al camp de la dreta introduïu el valor "15000".
A més, hi ha condicions de commutació. Té dues posicions "I" i "O". Per defecte, es posa a la primera posició. Això vol dir que només es conservaran a la selecció les línies que compleixin ambdues restriccions. Si es posa en posició "O", llavors hi haurà valors adequats per a qualsevol de les dues condicions. En el nostre cas, heu de canviar a "I", és a dir, deixeu aquesta configuració per defecte. Un cop introduïts tots els valors, feu clic al botó. "D'acord".
- Ara la taula només té línies en què la quantitat d’ingressos no és inferior a 10.000 rubles, però no supera els 15.000 rubles.
- De la mateixa manera, podeu configurar filtres en altres columnes. Al mateix temps, també es pot guardar el filtrat per les condicions anteriors que es van especificar a les columnes. Per tant, anem a veure com es fa la selecció utilitzant el filtre de cel·les en el format de data. Feu clic a la icona del filtre a la columna corresponent. Feu clic secuencialment sobre els elements de la llista. "Filtra per data" i "Filtre personalitzat".
- S'inicia la finestra personalitzada del filtre automàtic. Realitzeu una selecció de resultats a la taula del 4 al 6 de maig de 2016 ambdós inclosos. Al selector de condicions, com podeu veure, hi ha encara més opcions que per al format numèric. Trieu una posició "Després o igual". Al camp de la dreta, establiu el valor "04.05.2016". Al bloc inferior, canvieu el commutador a la posició "A o igual a". Introduïu el valor al camp de la dreta "06.05.2016". El commutador de compatibilitat de condicions es deixa a la posició predeterminada - "I". Per aplicar el filtratge en acció, feu clic al botó "D'acord".
- Com podeu veure, la nostra llista s’ha reduït encara més. Ara només queden línies en què l’import dels ingressos varia entre 10.000 i 15.000 rubles per al període comprès entre el 04.05 i el 06.05.2016.
- Podem restablir el filtrat en una de les columnes. Feu això per obtenir valors d’ingressos. Feu clic a la icona del filtre automàtic a la columna corresponent. A la llista desplegable, feu clic a l’element. "Elimina el filtre".
- Com podeu veure, després d’aquestes accions, la mostra per la quantitat d’ingressos es desactivarà i només es conservarà la selecció per dates (del 04.05.2016 al 06.05.2016).
- Aquesta taula té una altra columna: "Nom". Conté dades en format de text. Vegem com es pot crear una mostra mitjançant el filtratge per aquests valors.
Feu clic a la icona del filtre al nom de la columna. Passar per la llista de forma secuencial "Filtres de text" i "Filtre personalitzat ...".
- S'obre de nou la finestra del filtre automàtic d'usuari. Fem una mostra per nom. "Patates" i "Carn". Al primer bloc, el commutador de condició està definit a "Igual que". Al camp, a la dreta, introduïu-ne la paraula "Patates". El commutador del bloc inferior també es posiciona "Igual que". Al camp oposat a ell fem una entrada - "Carn". I llavors fem el que no hem fet abans: establim el canvi de compatibilitat a la posició "O". Ara la línia que conté qualsevol de les condicions especificades es mostrarà a la pantalla. Feu clic al botó "D'acord".
- Com podeu veure, a la nova mostra hi ha limitacions a la data (del 04/05/2016 al 05/06/2016) i per nom (patata i carn). No hi ha cap límit en l’import dels ingressos.
- Podeu eliminar el filtre completament utilitzant els mateixos mètodes que s’utilitzaven per instal·lar-lo. I no importa el mètode utilitzat. Per restablir el filtrat, que es troba a la pestanya "Dades" feu clic al botó "Filtre"que està allotjat en un grup "Ordenar i filtrar".
La segona opció consisteix en canviar a la pestanya "Inici". Allà fem un clic a la cinta del botó. "Ordenar i filtrar" en bloc Edició. A la llista activada, feu clic al botó. "Filtre".
Quan s'utilitza qualsevol dels dos mètodes anteriors, s'eliminarà el filtrat i es netejaran els resultats de la mostra. És a dir, la taula mostrarà tota la matriu de dades que té.
Lliçó: Funció de filtre automàtic a Excel
Mètode 2: utilitzeu la fórmula de matriu
També podeu fer una selecció aplicant una fórmula de matriu complexa. A diferència de la versió anterior, aquest mètode proporciona la sortida del resultat en una taula separada.
- Al mateix full, creeu una taula buida amb els mateixos noms de columna a la capçalera que el codi font.
- Seleccioneu totes les cel·les buides de la primera columna de la nova taula. Situeu el cursor a la barra de fórmules. Només aquí s’introduirà la fórmula, mostrant els criteris especificats. Seleccionarem línies, la quantitat d’ingressos en què superi els 15.000 rubles. En el nostre exemple específic, la fórmula que introduïu tindrà aquest aspecte:
= INDEX (A2: A29; MÉS BAIX (IF (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))Naturalment, en cada cas, l’adreça de les cel·les i dels rangs serà diferent. En aquest exemple, podeu comparar la fórmula amb les coordenades de la il·lustració i adaptar-la a les vostres necessitats.
- Com que es tracta d’una fórmula matricial, per aplicar-la en acció, no cal prémer el botó Introduïui drecera de teclat Ctrl + Maj + Retorn. Ho fem.
- Seleccionant la segona columna amb dates i establint el cursor a la barra de fórmules, introduïu l’expressió següent:
= INDEX (B2: B29; MÉS BAIX (IF (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))Premeu la drecera de teclat Ctrl + Maj + Retorn.
- De la mateixa manera, a la columna amb els ingressos introduïm la següent fórmula:
= INDEX (C2: C29; MÍNIM (SI (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))Un cop més, escrivim la drecera Ctrl + Maj + Retorn.
En els tres casos, només canvia el primer valor de les coordenades i la resta de fórmules són completament idèntiques.
- Com podeu veure, la taula està plena de dades, però la seva aparença no és molt atractiva. A més, els valors de data s’omplen incorrectament. Cal corregir aquestes deficiències. La data incorrecta es deu al fet que el format de les cel·les a la columna corresponent és comú i que necessitem establir el format de data. Seleccioneu tota la columna, incloses les cel·les amb errors, i feu clic a la selecció amb el botó dret del ratolí. A la llista que apareix a l’element "Format de cel·la ...".
- A la finestra de format que s'obre, obriu la pestanya "Nombre". En bloc "Formats de nombre" seleccioneu el valor "Data". A la part dreta de la finestra, podeu seleccionar el tipus de pantalla desitjat. Un cop establerts els paràmetres, feu clic al botó. "D'acord".
- Ara la data es mostra correctament. Però, com podeu veure, tota la part inferior de la taula està plena de cel·les que contenen un valor erroni. "#NUM!". De fet, aquestes són les cèl·lules que no tenien dades suficients de la mostra. Seria més atractiu que es mostressin buits. A aquests efectes, fem servir el format condicional. Seleccioneu totes les cel·les de la taula excepte la capçalera. Estar a la pestanya "Inici" feu clic al botó "Format condicional"que es troba al bloc d’eines "Estils". A la llista que apareix, seleccioneu l’element "Crea una regla ...".
- A la finestra que s'obre, seleccioneu el tipus de regla "Format només les cel·les que continguin". Al primer camp sota la inscripció "Format només les cel·les per a les quals es compleix la condició següent" tria una posició "Errors". A continuació, feu clic al botó "Format ...".
- A la finestra de format que s'obre, aneu a la pestanya "Font" i seleccioneu el color blanc al camp corresponent. Després d’aquestes accions, feu clic al botó. "D'acord".
- Feu clic al botó amb el mateix nom després de tornar a la finestra de condicionament.
Ara tenim una mostra preparada per a la restricció especificada en una taula separada correctament.
Lliçó: Formatació condicional a Excel
Mètode 3: mostrau diverses condicions utilitzant la fórmula
Igual que quan utilitzeu un filtre, utilitzant la fórmula, podeu provar-les per diverses condicions. Per exemple, prenem tota la mateixa taula de fonts, així com una taula buida on es mostraran els resultats, amb format numèric i condicional ja executat. Establiu el primer límit al límit inferior de selecció per als ingressos de 15.000 rubles, i la segona condició és el límit superior de 20.000 rubles.
- Introduïm en una columna separada les condicions de contorn de la mostra.
- Com en el mètode anterior, seleccioneu alternativament columnes buides de la nova taula i introduïu-hi les tres fórmules corresponents. A la primera columna, introduïu l’expressió següent:
= INDEX (A2: A29; MÍNIM (SI (($ D $ 2 = C2: C29); STRING (C2: C29); ""); STRING (C2: C29) - STRING ($ C $ 1)) - STRING ($ $) C $ 1))A les columnes posteriors introduïm exactament les mateixes fórmules, només canviant les coordenades immediatament després del nom de l’operador. ÍNDEX a les columnes corresponents que necessitem, per analogia amb el mètode anterior.
Cada vegada que entra, no oblideu escriure les tecles de drecera Ctrl + Maj + Retorn.
- L’avantatge d’aquest mètode sobre l’anterior és que si volem canviar els límits de mostreig, llavors no haurem de canviar la mateixa fórmula de la matriu, la qual cosa en si mateixa és molt problemàtic. És suficient canviar els números de contorn de la columna de condicions del full a aquells que l’usuari necessiti. Els resultats de la selecció canviaran immediatament automàticament.
Mètode 4: mostreig aleatori
En Excel amb una fórmula especial SLCIS també es pot aplicar una selecció aleatòria. S'ha de fer en alguns casos quan es treballa amb grans quantitats de dades, quan cal presentar una imatge general sense una anàlisi exhaustiva de totes les dades de la matriu.
- A l'esquerra de la taula, salteu una columna. A la cel·la de la columna següent, que es troba enfront de la primera cel·la amb les dades de la taula, introduïu la fórmula:
= RAND ()Aquesta funció mostra un nombre aleatori. Per activar-lo, feu clic al botó ENTER.
- Per fer una columna sencera de números aleatoris, establiu el cursor a la cantonada inferior dreta de la cel·la, que ja conté la fórmula. Apareix un marcador d’ompliment. Desplaceu-lo cap avall amb el botó esquerre del ratolí premut paral·lel a la taula amb les dades al final.
- Ara tenim un rang de cèl·lules plenes de números aleatoris. Però, conté la fórmula SLCIS. Hem de treballar amb valors purs. Per fer-ho, copieu a la columna buida de la dreta. Seleccioneu l'interval de cel·les amb números aleatoris. Situat a la pestanya "Inici", feu clic a la icona "Còpia" a la cinta.
- Seleccioneu la columna buida i feu clic amb el botó dret del ratolí, invocant el menú contextual. En un grup d’eines "Opcions d'inserció" tria un element "Valors"representat com un pictograma amb números.
- Després d'això, estar a la pestanya "Inici", feu clic a la icona ja coneguda "Ordenar i filtrar". A la llista desplegable, atureu la selecció de l’element "Ordre personalitzat".
- S'ha activat la finestra de configuració d'ordenació. Assegureu-vos de marcar la casella situada al costat del paràmetre. "Les meves dades contenen capçaleres"si hi ha una tapa, però no hi ha cap marca de selecció. Al camp "Ordenar per" especifiqueu el nom de la columna que conté els valors copiats de números aleatoris. Al camp "Ordena" deixeu la configuració predeterminada. Al camp "Ordre" podeu seleccionar l’opció com a "Ascendent"així i "Descendent". Per a una mostra aleatòria, això no importa. Un cop feta la configuració, feu clic al botó. "D'acord".
- Després d'això, tots els valors de la taula es disposen en ordre ascendent o descendent de números aleatoris. Podeu prendre qualsevol nombre de primeres línies de la taula (5, 10, 12, 15, etc.) i es pot considerar el resultat d’una mostra aleatòria.
Lliçó: Ordenar i filtrar les dades a Excel
Com podeu veure, es pot fer la mostra del full de càlcul d’Excel, igual que amb l’ajut d’un filtre automàtic, i aplicant fórmules especials. En el primer cas, el resultat es mostrarà a la taula original i, en el segon, en una àrea separada. Hi ha una oportunitat per fer una selecció, tant en una condició com en diverses. A més, podeu realitzar un mostreig aleatori mitjançant la funció SLCIS.
