Ao traballar con táboas de Excel, moitas veces é necesario seleccionalas segundo un determinado criterio ou en varias condicións. O programa pode facelo de varias maneiras usando varias ferramentas. Imos descubrir como probar en Excel usando unha variedade de opcións.
Mostraxe
A mostraxe de datos consiste no procedemento de selección da matriz xeral dos resultados que satisfán as condicións especificadas, coa súa saída posterior nunha folla nunha lista separada ou no intervalo inicial.
Método 1: usa o filtro automático avanzado
A forma máis sinxela de facer unha selección é usar o filtro automático avanzado. Considere como facelo cun exemplo específico.
- Seleccione a área na folla, entre os datos que desexa probar. Na pestana "Fogar" faga clic no botón "Ordenar e filtrar". Colócase no bloque de configuración. Edición. Na lista que se abre despois disto, faga clic no botón. "Filtro".
É posible facer de xeito diferente. Para facelo, despois de seleccionar a área na folla, vaia á pestana "Datos". Fai clic no botón "Filtro"que se publica nunha cinta dun grupo "Ordenar e filtrar".
- Despois desta acción, as iconas aparecen na cabeceira da táboa para comezar a filtrar en forma de pequenos triángulos envorcados no bordo dereito das celas. Fai clic nesta icona no título da columna na que queremos facer unha selección. No menú Inicio, faga clic no elemento "Filtros de texto". A continuación, seleccione a posición "Filtro personalizado ...".
- Activa a fiestra de filtrado personalizada. É posible establecer un límite no que se fará a selección. Na lista despregable para a columna que contén as celas de formato numérico, que usamos como exemplo, pode escoller un dos cinco tipos de condicións:
- iguais;
- non igual;
- máis;
- maior ou igual;
- menos
Imos establecer a condición como un exemplo para que só poidamos seleccionar valores para os que a cantidade de ingresos exceda os 10.000 rublos. Sitúe o interruptor na posición "Máis". Introduza o valor na marxe dereita "10000". Para realizar unha acción, fai clic no botón. "OK".
- Como podes ver, despois de filtrar, só hai liñas nas que a cantidade de ingresos supera os 10.000 rublos.
- Pero na mesma columna podemos engadir a segunda condición. Para facelo, volva á fiestra de filtro personalizada. Como podes ver, na parte inferior hai outro conmutador de condicións e o campo de entrada correspondente. Imos agora definir o límite superior de selección de 15.000 rublos. Para facelo, configure o cambio na posición "Menos", e no campo á dereita introduza o valor "15000".
Ademais, hai condicións de cambio. Ten dúas posicións "E" e "OR". Por defecto defínese na primeira posición. Isto significa que só permanecerán na selección as liñas que satisfagan as dúas restricións. Se está posto en posición "OR", entón haberá valores que sexan adecuados para calquera das dúas condicións. No noso caso, ten que axustar o cambio "E", é dicir, deixe esta configuración por defecto. Despois de introducir todos os valores, faga clic no botón. "OK".
- Agora a táboa ten só liñas nas que a cantidade de ingresos non é inferior a 10.000 rublos, pero non supera os 15.000 rublos.
- Do mesmo xeito, pode configurar filtros noutras columnas. Ao mesmo tempo, tamén é posible gardar o filtrado polas condicións anteriores que se especificaron nas columnas. Entón, imos ver como se fai a selección empregando o filtro para as celas no formato de data. Fai clic na icona do filtro na columna correspondente. Fai clic en secuencia sobre os elementos da lista. "Filtrar por data" e "Filtro personalizado".
- Iníciase de novo a ventá personalizada do filtro automático. Realiza unha selección de resultados na táboa do 4 ao 6 de maio de 2016 inclusive. No selector de condicións, como podes ver, hai aínda máis opcións que no formato numérico. Escolla unha posición "Despois ou igual". No campo da dereita, estableza o valor "04.05.2016". No bloque inferior, estableza o cambio na posición "Para ou igual a". Introduza o valor no campo dereito "06.05.2016". O cambio de compatibilidade de condicións queda na posición predeterminada - "E". Para aplicar o filtrado en acción, fai clic no botón "OK".
- Como podes ver, a nosa lista diminuíu aínda máis. Agora só quedan liñas nelas, nas que a cantidade de ingresos varía de 10.000 a 15.000 rublos para o período comprendido entre 04.05 e 06.05.2016 inclusive.
- Podemos restablecer o filtrado nunha das columnas. Faino para obter ingresos. Fai clic na icona do filtro automático na columna correspondente. Na lista despregable, faga clic no elemento. "Eliminar filtro".
- Como podes ver, tras estas accións, deshabilitarase a mostra polo importe dos ingresos e só a selección por datas permanecerá (do 04.05.2016 ao 06.05.2016).
- Esta táboa ten outra columna - "Nome". Contén datos en formato de texto. Vexamos como crear unha mostra usando o filtrado por estes valores.
Fai clic na icona do filtro no nome da columna. Ir secuencialmente á lista "Filtros de texto" e "Filtro personalizado ...".
- A ventá de filtro automático de usuario ábrese de novo. Facemos unha mostra por nome. "Patacas" e "Carne". No primeiro bloque, o conmutador de condicións está axustado en "Igual a". No campo á dereita del entra a palabra "Patacas". Tamén se coloca o interruptor do bloque inferior "Igual a". No campo oposto a el facemos unha entrada - "Carne". E entón facemos o que non fixemos antes: definimos o cambio de compatibilidade na posición "OR". Agora a liña que contén calquera das condicións especificadas aparecerá na pantalla. Fai clic no botón "OK".
- Como podes ver, na nova mostra hai limitacións na data (do 04/05/2016 ao 05/06/2016) e polo nome (pataca e carne). Non hai límite na cantidade de ingresos.
- Pode eliminar completamente o filtro empregando os mesmos métodos que se usaron para instalalo. E non importa o método utilizado. Para restablecer o filtrado, estar no separador "Datos" faga clic no botón "Filtro"que está aloxado nun grupo "Ordenar e filtrar".
A segunda opción implica cambiar á pestana "Fogar". Facemos un clic na cinta do botón. "Ordenar e filtrar" en bloque Edición. Na lista activada fai clic no botón. "Filtro".
Ao usar calquera dos dous métodos anteriores, eliminarase o filtrado e borraranse os resultados da mostra. É dicir, a táboa mostrará toda a matriz de datos que ten.
Lección: Función de filtro automático en Excel
Método 2: usa a fórmula de matriz
Tamén pode facer unha selección aplicando unha fórmula de matriz complexa. A diferenza da versión anterior, este método proporciona a saída do resultado nunha táboa separada.
- Na mesma folla, cree unha táboa baleira cos mesmos nomes de columna na cabeceira que o código fonte.
- Selecciona todas as celas baleiras da primeira columna da nova táboa. Sitúe o cursor na barra de fórmulas. Só aquí introducirase a fórmula, mostrándose segundo os criterios especificados. Seleccionaremos as liñas, a cantidade de ingresos que supera os 15.000 rublos. No noso exemplo específico, a fórmula que introduces será a seguinte:
= INDEX (A2: A29; MÁIS BAIXO (IF (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))Por suposto, en cada caso a dirección das celas e os intervalos será diferente. Neste exemplo, pode comparar a fórmula coas coordenadas da ilustración e adaptala ás súas necesidades.
- Xa que se trata dunha fórmula matricial, para poder aplicala en acción, non precisa premer o botón Introe atallo de teclado Ctrl + Maiús + Intro. Nós o facemos.
- Se selecciona a segunda columna con datas e axustando o cursor na barra de fórmulas, introduza a seguinte expresión:
= INDEX (B2: B29; MÁIS BAIXO (IF (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))Prema no atallo de teclado Ctrl + Maiús + Intro.
- Do mesmo xeito, na columna cos ingresos introducimos a seguinte fórmula:
= INDEX (C2: C29; MÁIS BAIXO (IF (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))De novo, teclea o atallo Ctrl + Maiús + Intro.
Nos tres casos só cambia o primeiro valor das coordenadas e o resto das fórmulas son completamente idénticas.
- Como podes ver, a táboa está chea de datos, pero a súa aparencia non é moi atractiva, ademais, os valores de data están enchelos de xeito incorrecto. É necesario corrixir estas deficiencias. A data incorrecta débese ao feito de que o formato das celas na columna correspondente é común e necesitamos definir o formato de data. Seleccione a columna completa, incluíndo as celas con erros, e prema na selección co botón dereito do rato. Na lista que aparece no elemento "Formato de cela ...".
- Na ventá de formatado que se abre, abra a pestana "Número". En bloque "Formatos de número" seleccione o valor "Data". Na parte dereita da ventá, pode seleccionar o tipo de visualización desexado. Despois de definir a configuración, faga clic no botón. "OK".
- Agora a data móstrase correctamente. Pero, como podes ver, toda a parte inferior da táboa está chea de celas que conteñen un valor erróneo. "#NUM!". De feito, estas son as células que non tiñan datos suficientes da mostra. Sería máis atractivo que se mostrasen baleiros. Para estes fins, utilizamos o formato condicional. Selecciona todas as celas da táboa excepto a cabeceira. Estar na pestana "Fogar" faga clic no botón "Formato condicional"que está no bloque de ferramentas "Estilos". Na lista que aparece, seleccione o elemento "Crear unha regra ...".
- Na ventá que se abre, seleccione o tipo de regra "Formata só as celas que conteñen". No primeiro campo baixo a inscrición "Formata só as celas para as que se cumpre a seguinte condición" elixe unha posición "Erros". A continuación, fai clic no botón "Formato ...".
- Na ventá de formatado que se abre, vaia á pestana "Fonte" e selecciona a cor branca no campo correspondente. Despois destas accións, faga clic no botón. "OK".
- Fai clic no botón co mesmo nome despois de volver á ventá de acondicionamento.
Agora temos unha mostra preparada para a restricción especificada nunha táboa separada debidamente ordenada.
Lección: Formato condicional en Excel
Método 3: mostra por varias condicións usando a fórmula
Do mesmo xeito que cando se usa un filtro, usando a fórmula, pode mostrala por varias condicións. Por exemplo, tomamos toda a mesma táboa de fonte, así como unha táboa baleira onde se mostrarán os resultados, con formato numérico e condicional realizado. Estableza o primeiro límite no límite inferior de selección para ingresos de 15.000 rublos, ea segunda condición é o límite superior de 20.000 rublos.
- Introducimos nunha columna separada as condicións de contorno para a mostra.
- Como no método anterior, seleccione alternativamente columnas baleiras da nova táboa e introduza nelas as tres fórmulas correspondentes. Na primeira columna, introduza a seguinte expresión:
= INDEX (A2: A29; MÁIS BAIXO (SI (($ D $ 2 = C2: C29); STRING (C2: C29); ""); STRING (C2: C29) - STRING ($ C $ 1)) - STRING ($ $) C $ 1))Nas columnas posteriores introducimos exactamente as mesmas fórmulas, só cambiando as coordenadas inmediatamente despois do nome do operador. ÍNDICE ás columnas correspondentes que necesitamos, por analogía co método anterior.
Cada vez que ingresas non esquezas escribir as teclas de atallo Ctrl + Maiús + Intro.
- A vantaxe deste método sobre o anterior é que se queremos cambiar os límites de mostraxe, entón non teremos que cambiar a fórmula de matriz en si, que en si é bastante problemática. É suficiente cambiar os números de contorno na columna de condicións da folla a aqueles que o usuario necesita. Os resultados da selección cambiarán inmediatamente automaticamente.
Método 4: mostraxe aleatoria
En Excel cunha fórmula especial SLCIS tamén pode aplicarse unha selección aleatoria. Necesítase que se faga nalgúns casos cando se traballa con grandes cantidades de datos, cando precisa presentar unha imaxe xeral sen unha análise exhaustiva de todos os datos da matriz.
- Á esquerda da táboa, salte unha columna. Na cela da columna seguinte, que está fronte á primeira cela coas datos da táboa, introduza a fórmula:
= RAND ()Esta función mostra un número aleatorio. Para activalo, fai clic no botón ENTER.
- Para facer unha columna completa de números aleatorios, configure o cursor no canto inferior dereito da cela, que xa contén a fórmula. Aparece un marcador de enchemento. Despráceo cara abaixo co botón esquerdo do rato paralelo á táboa cos datos ata o final.
- Agora temos un rango de celas cheos de números aleatorios. Pero, contén a fórmula SLCIS. Necesitamos traballar con valores puros. Para facelo, copia á columna baleira á dereita. Seleccione o intervalo de celas con números aleatorios. Situado na pestana "Fogar", fai clic na icona "Copiar" na cinta.
- Selecciona a columna baleira e fai clic co botón dereito do rato e invoca o menú de contexto. Nun grupo de ferramentas "Opcións de inserción" elixe un elemento "Valores"representado como un pictograma con números.
- Despois diso, estando na pestana "Fogar", faga clic na icona xa coñecida "Ordenar e filtrar". Na lista despregable, pare a selección no elemento "Ordenación personalizada".
- Activa a ventá de configuración de clasificación. Asegúrese de marcar a caixa situada ao lado do parámetro. "Os meus datos conteñen cabeceiras"se hai unha tapa, pero non hai marca de verificación. No campo "Ordenar por" especifique o nome da columna que contén os valores copiados de números aleatorios. No campo "Ordenar" deixe a configuración por defecto. No campo "Orde" pode seleccionar a opción como "Ascendente"así e "Descendente". Para unha mostra aleatoria, isto non importa. Despois de facer a configuración, fai clic no botón. "OK".
- Despois diso, todos os valores da táboa están dispostos en orde ascendente ou descendente de números aleatorios. Pode tomar calquera número de primeiras liñas da táboa (5, 10, 12, 15, etc.) e poden considerarse o resultado dunha mostra aleatoria.
Lección: Ordenar e filtrar datos en Excel
Como podes ver, a mostra na folla de cálculo de Excel pódese facer, como coa axuda dun filtro automático e aplicando fórmulas especiais. No primeiro caso, o resultado aparecerá na táboa orixinal e, no segundo, nunha área separada. Hai unha oportunidade para facer unha selección, tanto nunha condición como en varias. Ademais, pode realizar mostraxe aleatoria usando a función SLCIS.
