Ordenar por color en hojas de Google

Ordenar datos es una operación útil y de uso bastante común. Después de todo, cuando trabajas con grandes cantidades de información en una tabla, organizar los datos en ella se convierte en una tarea muy importante. Ordenar datos en una hoja de Google ayuda a ordenar los datos e identificar patrones en ellos. Existen diferentes técnicas para ordenar en una hoja de Google o en un rango de selección.

Veremos en qué se diferencia la clasificación de tablas de la clasificación por rangos, cómo describir correctamente los criterios de clasificación y cómo se puede ordenar por varias columnas de una hoja de cálculo de Google a la vez. También nos centraremos en formas de ordenar datos automáticamente si cambian.

En este artículo, aprenderá cómo ordenar datos para que el contenido de su tabla sea más fácil de ver y organizar.

• Cómo ordenar datos en Google Sheets
• ordenar toda la hoja
• ordenar un solo rango
• ordenar por varias columnas a la vez
• clasificación automática

Cómo ordenar datos en Google Sheets

Google Sheets utiliza dos tipos de clasificación:

1. Ordenar por columnas
2. Clasificación de rango.

Ahora hablaremos de cada uno de ellos con más detalle.

Entonces, ordenemos nuestra tabla por columna.

ordenar una hoja

Con este método de clasificación se ordenarán todos los datos de la hoja. ¡Tenga cuidado si tiene varias tablas en su hoja de trabajo!

¡Atención! Antes de ordenar toda la tabla por columna, asegúrese de congelar los encabezados de las columnas; de lo contrario, también se ordenarán y terminarán en el medio de sus datos.

Entonces, para que nos resulte más cómodo ver los datos ordenados, primero arreglemos los encabezados de las columnas para que siempre estén visibles y no ocultos al mirar hacia abajo en la tabla.

Para hacer esto, coloque el cursor en cualquier celda del encabezado de la tabla y seleccione Ver - Congelar en el menú y luego especifique el número requerido de filas.

Luego elija por qué columna ordenaremos nuestra tabla.

Al hacer clic en el triángulo al lado de la letra de la columna, puede elegir dos opciones de clasificación: ascendente o descendente. Esto ordenará toda la tabla.

O puede seleccionar en el menú Datos - Ordenar hoja por columna... y luego la opción de clasificación requerida.

Es importante tener en cuenta aquí que si ha seleccionado la columna completa, tendrá la opción de ordenar el rango de datos (de lo que hablaremos a continuación).

Pero si selecciona "Ordenar rango por columna...", solo se ordenará la columna seleccionada y el resto de los datos permanecerán sin cambios. Esto romperá la estructura de sus datos y no tiene sentido ordenarlos de esta manera. Por tanto, ¡ten cuidado!

Ordenar rango

Veamos el segundo método: clasificación por rango.

Esto será útil si tiene varias tablas pequeñas de datos en sus Hojas de cálculo de Google, pero solo necesita ordenar una de ellas.

Ordenar un rango no afectará los datos de otras tablas ubicadas en la hoja de trabajo.

Utilice el mouse para seleccionar el rango de celdas que desea reorganizar.

Entonces tienes 2 opciones:

1. Haga clic derecho y seleccione "Clasificación de rango" en el menú que aparece.
2. En el menú, seleccione Datos - Ordenar rango.

Se abrirá una ventana adicional con configuraciones de clasificación.

Aquí puede especificar que el rango de datos que seleccionó contenga encabezados de columna. En nuestro ejemplo, no seleccionamos los encabezados de las columnas, por lo que dejamos la casilla vacía. Es decir, ordenaremos todo el rango seleccionado. Si marca esta casilla, la primera fila del rango seleccionado no se ordenará.

También puede seleccionar por qué columna ordenar haciendo clic en el icono desplegable. Elegimos la columna B.

Aquí, el orden de clasificación predeterminado es "ascendente" de la A a la Z. Si es necesario, puede cambiarlo al contrario: de Z a A.

Ordenar datos en una hoja de Google por varias columnas

Si necesita ordenar según varios parámetros, en la configuración de clasificación puede agregar una columna más o incluso varias.

Para hacer esto, use el elemento "Agregar otra columna para ordenar" en la configuración de clasificación. Luego seleccione la columna deseada de la lista desplegable.

Para cada columna, puede seleccionar el orden de clasificación: ascendente o descendente.

Como resultado, la clasificación se realizará secuencialmente: primero por la primera columna, luego por la segunda, y así sucesivamente (en nuestro caso, primero por B, luego por D).

En nuestro ejemplo, ordenamos los pedidos primero por cliente y luego por producto. Como resultado, vemos los pedidos de cada cliente organizados por producto.

Clasificación automática de Google Sheets

Digamos que tenemos una tabla con información ordenada y ordenada. ¿Pero qué pasa si se agregan nuevos datos a la tabla? Después de todo, ¿ya no se clasificarán y tendremos que repetir la clasificación nuevamente?

1. Usando la función ORDENAR.
2. Usando una tabla dinámica.
3. Usando Google Scripts (no lo consideraremos aquí, ya que solo está disponible para desarrolladores).

Para aquellos que no tienen conocimientos de programación, utilizar la función ORDENAR será la solución más sencilla.

Para ello crearemos otra hoja, a la que podemos llamar “Ordenar”. Colocaremos nuestros datos ordenados en esta hoja.

Nuestros datos estarán ubicados en la ficha “Datos”.

Copie los encabezados de las columnas de la hoja "Datos" en la fila A de la hoja "Clasificación".

En la celda A2 de la hoja "Clasificación", escriba la fórmula de la función:

ORDENAR ("Datos"!A2:G, 2, 2)

¡Aquí "Datos"!A2:G son las coordenadas del rango del cual se tomarán los datos para ordenar.

Tenga en cuenta que solo especificamos la celda superior izquierda y la columna más a la derecha. Esto nos brinda la posibilidad de agregar nuevos datos a nuestra tabla original sin cambiar la fórmula de clasificación.

2 es el número ordinal de la columna en el rango especificado por el cual ordenaremos. En nuestro caso, ordenaremos las ventas por nombre de cliente.

2 - orden de clasificación. 2 significa ordenar en orden ascendente, de menor a mayor. 1 significa ordenar en orden descendente, de mayor a menor.

Como resultado, en la hoja “Clasificación” recibimos datos de ventas ordenados por clientes.

Para probar las capacidades de clasificación automática en Google Sheets, agregue otra fila con datos de ventas a la tabla original en la hoja "Datos".

Esta entrada aparecerá inmediatamente automáticamente en la tabla de la hoja "Clasificación".

También puede utilizar una tabla dinámica para ordenar. Tampoco afecta los datos existentes y se coloca en una hoja separada.

Utilice el mouse para resaltar las columnas de la tabla que desea incluir en la tabla dinámica y luego haga clic en Datos - Tabla dinámica en el menú. Puede leer más sobre cómo trabajar con tablas dinámicas y sus capacidades en nuestro material.

Los datos se están apoderando del mundo. Y a menudo tenemos tantos que es bastante difícil descubrir qué significan y dar una valoración.

Las hojas de cálculo son una de las mejores formas de analizar la estructura de los datos y comenzar a utilizarlos. Pero a menudo los conjuntos de datos son demasiado grandes para poder utilizarlos fácilmente. Para hacer esto, necesita herramientas que lo ayuden a encontrar los datos necesarios y reducir su volumen.

En este tutorial, aprenderá cómo utilizar un filtro de Hojas de cálculo de Google para limitar los datos que está viendo. Y luego, aprenderá los secretos del uso de la función de clasificación de Google Sheets para ayudarlo a organizar sus datos de la manera que desee.

A continuación, en el vídeo tutorial, les voy a contar sobre las funciones de clasificación y filtrado de datos en Google Sheets. Verá opciones para filtrar y ordenar datos en Hojas de cálculo.

Mire este tutorial ilustrado para aprender técnicas para utilizar las funciones de clasificación y filtrado de Google Sheets.

Ponlo en practica

A medida que avanza en esta lección, cree ejemplos utilizando los datos de ejemplo. He preparado un conjunto de datos simples que puedes usar para practicar cómo ordenar y filtrar datos en Google Sheets.

Súbelos a Google Sheets y úsalos para trabajar con las técnicas que describo en este tutorial.

1. Ordenar en Google Sheets

Clasificación Los datos en tablas funcionan con el orden en que aparecerán los datos en sus tablas.

A la hora de clasificar tengo en cuenta dos factores:

1. ¿Según qué columna se ordenarán los datos? Por ejemplo, es posible que desee ordenar las ventas según el volumen total de ventas.
2. ¿Cómo se ordenarán los datos? Por ejemplo, los datos se pueden ordenar desde valores grandes hasta valores pequeños.

Una de las cosas importantes acerca de la clasificación es que clasificarás toda la mesa. Normalmente, cada fila de datos puede considerarse un registro o elemento de datos. Cada fila de datos contiene un elemento de datos y cada columna contiene diferentes atributos o formas de describir los datos.

Por ejemplo, cada fila de su tabla podría representar un registro de compras realizadas por los clientes en su tienda en línea. Si bien cada columna (atributo) contendrá información clave sobre esta compra, como la hora de la compra, la dirección del comprador y el volumen de compra.

En este ejemplo de este tutorial, cada fila es un registro de los empleados que se contratan y cada columna contiene atributos que describen al candidato.

Cuando ordena datos, es muy importante que los datos permanezcan agrupados. Si solo ordena la columna que contiene el número de ventas, terminará con un desastre de datos. Una venta de $100 ahora puede coincidir con el comprador equivocado.

Por eso es tan importante ordenar sus datos correctamente. Veamos algunos ejemplos a continuación.

Cómo ordenar datos en Google Sheets

Una de las formas básicas de ordenar en Google Sheets es ordenar los datos alfabéticamente de la A a la Z (o A-Z). Los datos suelen ser más fáciles de leer después de esto y luego es más fácil encontrar las filas que pueda necesitar.

Antes de comenzar a clasificar, mi primer paso es resaltar toda la mesa. Esto minimiza la posibilidad de que accidentalmente ordene la columna incorrecta o seleccione solo una parte de los datos.

Una vez que haya seleccionado el conjunto de datos, vaya al menú Datos > Ordenar rango, como se muestra en la siguiente imagen.

Una vez seleccionados los datos, accede a Datos > Ordenar rango para ordenar sus datos utilizando las herramientas de Google Sheets.

Si bien hay opciones integradas para ordenar con A-Z o Z-A, es mucho más fácil usar la herramienta integrada en Tablas para ordenar. En la siguiente captura de pantalla puede ver un ejemplo típico del uso de la clasificación. Simplemente puede seleccionar la columna a la que desea aplicar la clasificación.

Además, asegúrese de ordenar en la dirección correcta. Para datos de texto, puede ordenar alfabéticamente (A-Z) o invertir alfabéticamente (Z-A).

Seleccione la columna por la que desea ordenar los datos y el orden de clasificación, alfabético o alfabético inverso.

Una vez que haga esto, verá que sus datos están ordenados y sanos y salvos. Puede notar que en la imagen a continuación, ordenamos los datos según el nombre de la empresa para la que trabaja el candidato.

Ahora que hemos ordenado la columna Empresa, observará que los nombres de las empresas aparecen en orden alfabético.

Es un buen hábito revisar los datos que ha ordenado. En el peor de los casos, sólo ordenarás uno columna, mientras que otras permanecerán sin cambios. En la captura de pantalla anterior, según los números de identificación mixtos, que ya no están en orden ascendente (1,2, 3, 4...), podemos concluir que todas las columnas se ordenaron simultáneamente, de manera relacionada.

2. Ordenar Google Sheets usando dos reglas.

¿Qué pasa si quieres ordenar por dos categorías? Por ejemplo, ¿qué sucede si desea ordenar los datos de los empleados por su interés (o más bien por su atractivo para el empleador) y el nombre de la empresa? Intentemos hacer eso.

Con los datos resaltados, ve al menú. Datos > Ordenar rango. Ahora haga clic en el botón Agregar otro rango para ordenar para agregar un segundo criterio para ordenar los datos. Ahora puedes ordenar según múltiples reglas.

En este menú el orden es importante. Tenga en cuenta que existe una primera regla, "Ordenar por" y luego la segunda regla "luego por". En el siguiente ejemplo, configuro el menú para ordenar los intereses alfabéticamente y luego ordenar las empresas alfabéticamente.

Hacer clic en Agregar otra columna para ordenar, lo que le permitirá establecer varias reglas para ordenar sus datos.

Utilice esta clasificación de varios niveles para definir cómo se verán sus datos. Puede agregar tantas reglas como necesite para presentar los datos en el orden requerido.

3. Utilice filtros en Google Sheets

Si la clasificación afecta el orden en que se presentan los datos en una tabla, filtración afecta qué datos se mostrarán en la tabla. Aprendamos a utilizar el filtrado en Google Sheets para reducir la cantidad de datos mostrados.

Para agregar filtros, resalte sus datos. Entonces ve a Datos > Crear filtro en el menú Tablas.

Ahora que ha habilitado el filtrado, verá un pequeño icono en la esquina superior derecha del título de la columna. Haga clic en este icono y verá una lista de valores disponibles en esta columna, en un menú desplegable, como en la captura de pantalla siguiente.

Haga clic en el icono en el título de la columna para seleccionar cómo desea limitar el conjunto de datos.

En este menú emergente, cambie a los criterios que desea que cumplan sus datos. Cuando presionas DE ACUERDO, habrá menos datos en su tabla y solo se mostrarán aquellos que cumplan con las reglas de su filtro de Hojas de cálculo de Google.

Al igual que con la clasificación, puedes establecer varios niveles de filtrado. En el siguiente ejemplo, agregué un filtro al " nivel salarial" (Grado salarial).

Este es un gran ejemplo de lo útil que es el filtrado cuando se trabaja con datos. Combinando el filtrado por "interés" con un filtro por "nivel salarial", recibí una lista de dos candidatos de 100 posibles.

Es importante recordar que el filtrado no elimina datos. Simplemente permanecen detrás de escena, listos para reaparecer cuando cambie las condiciones de filtrado.

Encontrar datos faltantes

Hay algo que debe tener en cuenta cuando trabaja con el filtrado de datos: debe tener cuidado y ser consciente de los datos que deja detrás de escena. No puedo decirles exactamente cuántas veces trabajé con hojas de cálculo y no me di cuenta de que faltaban datos porque se eliminaron del filtrado en Google Sheets.

Siempre estoy atento a dos cosas si los datos no tienen sentido:

1. Icono de filtro en la parte superior de la columna
2. Números de línea en hojas de la izquierda.

Observe en esta captura de pantalla que los números de fila (en el extremo izquierdo) ya no son secuenciales y que también hay un icono de filtro encima de la columna F.

Estos dos factores nos permiten estar seguros de que no te ha faltado ningún dato en tu tabla.

Resumamos y sigamos aprendiendo.

El filtrado y la clasificación son herramientas críticas para la reducción y selección de datos. Mire las otras lecciones a continuación para obtener más información sobre el uso de tablas para ver y analizar datos.

Acaba de aprender a utilizar el filtrado y la clasificación de datos en Google Sheets. ¿Cómo se trabaja con grandes cantidades de datos? ¿Utiliza clasificación y filtrado para simplificar sus datos o utiliza otras herramientas analíticas?

Ordenar y filtrar datos son operaciones útiles y de uso frecuente cuando se trabaja con cualquier editor de hojas de cálculo.

Clasificación le permite organizar el caos de datos e identificar patrones. Al usar filtración Podrás seleccionar sólo la información que te interese en este momento.

Ordenar datos en una tabla

En Google Sheets, la clasificación se puede realizar por todos los datos de la hoja o solo por los valores de un rango designado.

La clasificación "global" sólo tiene sentido si no hay información de texto en la hoja (por ejemplo, encabezados de columna), diagramas y otros objetos. De lo contrario, obtendrás un lío en el que será difícil resolverlo. Pero si aún necesita esta función, utilice los comandos "Ordenar hoja por columna..." del menú "Datos".

Echemos un vistazo más de cerca a la clasificación por rango.

Digamos que tenemos una tabla de ventas por mes y queremos ordenar los datos de enero en orden ascendente.

1. Seleccione todas las celdas con información de ventas, incluidos nombres de productos y datos de otros períodos. Si selecciona solo las celdas en la columna de enero, los cambios afectarán solo a esa columna. Lo cual pierde todo significado, ya que no podremos asociar la cantidad vendida con el producto en sí.
2. A continuación, vaya al menú "Datos" y seleccione el elemento "Rango de clasificación...".
3. Se abrirá una ventana de configuración, donde indicamos la columna por la que ordenar y su dirección (ascendente o descendente).
   Puede agregar otras columnas según sea necesario. Luego el procesamiento se realizará de forma secuencial. Primero en la primera columna, luego en la segunda, etc.
   Haga clic en el botón "Ordenar".

El resultado es una tabla ordenada por la columna seleccionada. En la imagen de arriba, esta es la columna "B". Y tenemos una imagen clara de las ventas de bienes en enero.

Filtrado de datos

Los filtros le permiten ocultar datos innecesarios y así facilitar la navegación por la tabla. Indicas qué información quieres ver y el sistema elimina todo lo innecesario. Veamos cómo trabajar con filtros usando un ejemplo.

Utilicemos la misma tabla de ventas por mes, así como su diagrama.

Dejaremos sólo información sobre aspiradoras y planchas. Para hacer esto:

1. Nos dirigimos a la columna para la que queremos seleccionar datos.
2. En el menú "Datos", seleccione el comando "Filtro" y aparecerá una pequeña flecha en la celda actual, indicando la disponibilidad de filtrado.

1. Haga clic en esta flecha y configure la selección: deje una marca de verificación junto a los valores que desea dejar, indique la opción de clasificación deseada.

1. Configure un filtro para otros campos de la tabla. En este caso, los filtros se superpondrán entre sí.
   El resultado será la visualización de información que satisface la condición. Los campos para los cuales el filtro está habilitado se marcarán con un icono de embudo.

Tenga en cuenta que el sistema ha reconstruido automáticamente el gráfico de acuerdo con los datos de la tabla. Esto es muy útil cuando un diagrama presenta una gran cantidad de información.

Para desactivar el filtro, utilice el comando Desactivar filtro en el menú Datos.

Guardar filtros

Si utiliza ciertos filtros con frecuencia, puede guardarlos y recuperarlos en el momento adecuado. Para hacer esto:

1. Configurar la selección de datos.
2. Vaya al menú “Datos” - “Filtro” - “Guardar como filtro...”.
3. Los encabezados de filas y columnas cambiarán a gris oscuro y las herramientas de filtro aparecerán en la parte superior de la tabla. Con su ayuda, puede especificar un nombre claro para el filtro, cambiar el rango de datos y también eliminar un filtro guardado previamente.
4. Después de todas las configuraciones, haga clic en la cruz a la derecha en el panel de edición de filtros.

Ahora, para acceder al filtro guardado, haga clic en su nombre en el menú Datos. Puedes volver a la vista anterior usando el comando “Original” en el mismo menú en la sección “Filtros...”.

Algoritmos

• Tutorial

Este artículo hablará sobre varias funciones muy útiles de Google Sheets que Excel no tiene (ORDENAR, unir matrices, FILTRO, IMPORTRANGE, IMAGEN, GOOGLETRANSLATE, DETECTLANGUAGE)

Hay muchas cartas, pero hay análisis de casos interesantes, todos los ejemplos, por cierto, se pueden ver más de cerca en el documento de Google goo.gl/cOQAd9 (archivo-> crear una copia para copiar el archivo a su Google Conducir y poder editar).

Tabla de contenido:

Si el resultado de la fórmula ocupa más de una celda

Primero, sobre una característica importante de mostrar resultados de fórmulas en Google Sheets. Si su fórmula devuelve más de una celda, entonces se mostrará toda la matriz a la vez y ocupará tantas celdas y columnas como sea necesario (en Excel, esto requeriría que ingrese una fórmula matricial en todas estas celdas). Veamos cómo funciona esto en el siguiente ejemplo.

CLASIFICAR

Le ayudará a ordenar un rango de datos por una o más columnas y mostrar inmediatamente el resultado.

Sintaxis de la función:

=ORDENAR(datos a ordenar; columna_a_ordenar; ascendente; [columna_a_ordenar_2, ascendente_2; ...])

En el ejemplo de la captura de pantalla siguiente, ingresamos la fórmula solo en la celda D2 y ordenamos los datos por la primera columna (en lugar de VERDADERO/FALSO, puede ingresar VERDADERO/FALSO).

(en adelante, ejemplos de configuración de la tabla regional rusa, la configuración de registro se puede cambiar en el menú archivo → configuración de la tabla)

¿Cómo agregar encabezados de tabla a SORT?

Usando llaves (), creamos una matriz de dos elementos, el encabezado de la tabla A1:B1 y la función ORDENAR, separando los elementos entre sí mediante un punto y coma.

¿Cómo combinar varios rangos de datos y ordenar (y más)?

Veamos cómo podemos combinar rangos para usarlos en funciones. Esto se aplica no solo a ORDENAR, esta técnica se puede utilizar en cualquier función donde sea posible, por ejemplo en BUSCARV o BÚSQUEDA.

Quienes leyeron el ejemplo anterior ya adivinaron qué hacer: abrir la llave y recoger los arrays a combinar, separándolos entre sí con punto y coma y cerrar la llave.

Puede combinar matrices y no usarlas en una fórmula, sino simplemente mostrarlas en una hoja, por ejemplo, recopilando datos de varias hojas de su libro de trabajo. Para la fusión vertical, solo necesita mantener la misma cantidad de columnas en todos los fragmentos (tenemos dos columnas en todas partes).

Y en la captura de pantalla a continuación hay un ejemplo de una unión horizontal, utiliza una barra invertida en lugar de un punto y coma y el número de filas en los fragmentos debe coincidir; de lo contrario, la fórmula devolverá un error en lugar del rango combinado.

(Los puntos y comas y las barras invertidas son separadores de elementos de matriz en la configuración regional rusa, si los ejemplos no funcionan para usted, a través del archivo de configuración de la tabla, asegúrese de que estos sean los que tiene)

Bueno, ahora volvamos a la matriz horizontal e insertémosla en la función ORDENAR. Ordenaremos los datos por la primera columna, en orden descendente.

Una unión se puede utilizar en cualquier función, lo principal es mantener el mismo número de columnas para una unión vertical o filas para una unión horizontal.

Todos los ejemplos discutidos se pueden examinar más de cerca.
Documento de Google.

FILTRAR

Con FILTER podemos filtrar datos por una o más condiciones y mostrar el resultado en una hoja de trabajo o usar el resultado en otra función como rango de datos.

Sintaxis de la función:

FILTRO(rango; condición_1; [condición_2; ...])

una condición

Por ejemplo, tenemos una tabla con las ventas de nuestros empleados, mostremos los datos de un empleado de ella.

Ingresemos la siguiente fórmula en la celda E3:

=FILTRO(A3:C7;B3:B7=“Natalia Chistyakova”)

Tenga en cuenta que la sintaxis es ligeramente diferente de las fórmulas habituales, como SUMMESLIN, donde el rango de la condición y la condición misma se separarían mediante un punto y coma.

Una fórmula ingresada en una celda nos devuelve una matriz de 9 celdas con datos, pero después de los ejemplos con la función ORDENAR, esto ya no nos sorprende.

Además del signo igual (=), también puedes utilizar >, >=,<>(no igual)<, <=. Для текстовых условий подходят только = и <>, y para números o fechas puedes utilizar todos estos signos.

Dos condiciones y trabajar con una fecha.

Compliquemos la fórmula y agreguemos una condición más, según la fecha de ventas, dejando todas las ventas a partir del 01/02/17.

Así es como se verá la fórmula si ingresa los argumentos de la condición directamente en ella, preste atención a la conversión de la entrada de texto de fecha usando DATEVALUE:

=FILTRO(A3:C7;B3:B7=“Natalia Chistyakova”,A3:A7>=FECHAVALOR(“01.02.17”))

O así, si te refieres a celdas con argumentos:

=FILTRO(A3:C7;B3:B7=I6;A3:A7>=J6)

Gráfico interactivo usando FILTER y SPARKLINE

¿Sabes de qué otra manera puedes utilizar la función FILTRO? En lugar de enviar el resultado de la función a una hoja de trabajo, podemos usarlo como datos para otra función, como un minigráfico. Sparkline es una función que construye un gráfico en una celda basándose en nuestros datos. Sparkline tiene muchas configuraciones, como el tipo de gráfico, el color de los elementos, pero ahora no nos detendremos en ellos y usaremos la función sin configuraciones adicionales. . Pasemos a un ejemplo.

Lista desplegable. Nuestro horario cambiará dependiendo del empleado seleccionado en la lista desplegable hacemos la lista así:

• seleccione la celda E2;
• menú Datos → Verificación de datos;
• reglas: Valor del rango y en el rango seleccione la columna con empleados de los datos de origen, no se preocupe que los nombres se repitan, solo quedarán valores únicos en la lista desplegable;

La celda con la lista desplegable se convertirá en una condición para la fórmula FILTRO, escribámosla.

E insertaremos esta fórmula en la función SPARKLINE, que, en base a los datos recibidos, dibujará un gráfico en la celda.

= minigráfico (FILTRO (C3: C7, B3: B7 = E2))

Así es como se ve en dinámica:

Pero qué elegante puede verse SPARKLINE con configuraciones adicionales; en el trabajo real, el diagrama muestra los resultados de la actividad de un día, las columnas verdes son valores positivos, las rosas son negativos.

IMPORTANCIA

Para transferir datos de un archivo a otro, Google Sheets utiliza la función IMPORTRANGE.

¿En qué casos puede resultar útil?

• Necesita datos actualizados del expediente de sus colegas.
• Quiere procesar datos de un archivo al que tiene acceso "Sólo vista".
• Quiere recopilar tablas de varios documentos en un solo lugar para poder procesarlos o verlos.

Esta fórmula le permite obtener una copia de un rango de otro Google Sheets. En este caso, el formateo no se transfiere, solo datos (le diremos qué hacer con el formateo a continuación).

La sintaxis de la fórmula es la siguiente:

IMPORTRANGE(clave de hoja de cálculo; cadena de rango)
IMPORTRANGO(clave; rango)

spreadsheet_key (clave): secuencia de caracteres del atributo "key=" (clave) en el enlace a la tabla (después de "hojas de cálculo/.../").

Ejemplo de fórmula con clave:

=IMPORTRANGO("abcd123abcd123"; "hoja1!A1:C10")

En lugar de una clave de tabla, puede utilizar un enlace de documento completo:

Su archivo mostrará el rango A1:CM500 de la Hoja1 del archivo ubicado en el enlace correspondiente.

Si el número de columnas o filas en el archivo fuente puede cambiar, ingrese un rango abierto en el segundo argumento de la función (consulte también la subsección “Rangos de la forma A2:A”), por ejemplo:

Hoja1!A1:CM (si se agregarán filas)
Hoja1!A1:1000 (si se agregarán columnas)

! Tenga en cuenta que si está cargando un rango abierto (por ejemplo, A1:D), no podrá insertar manualmente ningún dato en las columnas A:D en el archivo donde se encuentra la fórmula IMPORTRANGE (es decir, el ubicación final donde se cargan los datos). Están, por así decirlo, "reservados" para todo el rango abierto; después de todo, su tamaño se desconoce de antemano.

Importar formato desde una tabla fuente

Como ya hemos señalado, IMPORTRANGE sólo carga los datos, pero no el formato de la tabla fuente. ¿Cómo lidiar con esto? Prepare el terreno con anticipación copiando el formato de la hoja de trabajo original. Para hacer esto, vaya a la hoja original y cópiela en su libro de trabajo:

Después de presionar el botón Copiar a… seleccione el libro de trabajo al que importará los datos. Por lo general, la tabla requerida está en la pestaña. Reciente(si realmente has trabajado con ella recientemente).

Después de copiar la hoja, seleccione todos los datos (haciendo clic en la esquina superior izquierda):

Y haga clic Borrar. Todos los datos desaparecerán, pero el formato permanecerá. Ahora puede ingresar a la función IMPORTRANGE y obtener una coincidencia completa de la hoja fuente, tanto en términos de datos como de formato:

IMPORTRANGE como argumento para otra función

IMPORTRANGE puede ser un argumento para otra función si el rango que está importando se ajusta a esa función.

Consideremos un ejemplo sencillo: el valor medio de las ventas de un rango situado en otro documento.

Este es el documento original. Sumemos los datos y necesitamos el promedio de ventas en 2016 (es decir, desde la celda D2 hasta abajo).

Primero importamos este rango:

Y luego usamos esto como argumento para la función PROMEDIO:


Obtenemos el resultado, que se actualizará cuando se agreguen nuevas filas en el archivo fuente en la columna D.

IMAGEN: agregar imágenes a las celdas

La función IMAGEN le permite agregar imágenes a las celdas de Google Sheets.

La función tiene la siguiente sintaxis:

IMAGEN(URL, , , )

La URL es el único argumento obligatorio. Este es un enlace a la imagen. El enlace se puede especificar directamente en la fórmula, usando comillas:

=IMAGEN(“http://shagabutdinov.ru/wp-content/uploads/2015/12/Run-or-Die.jpg”)

La última opción es más conveniente en la mayoría de los casos. Entonces, si tienes una lista de libros y enlaces a portadas, todo lo que necesitas es una fórmula para mostrarlos todos:

El argumento modo puede tomar cuatro valores (si se omite, el valor predeterminado será el primero):

1. la imagen se estira al tamaño de la celda manteniendo la relación de aspecto;
2. la imagen se estira sin mantener la relación de aspecto, llenándose completamente
3. la imagen se inserta con el tamaño original;
4. usted especifica las dimensiones de la imagen en los argumentos tercero y cuarto de y . , respectivamente, son necesarios sólo cuando el modo de argumento = 4. Se especifican en píxeles.

Veamos cómo se ven las imágenes en la práctica con cuatro valores diferentes del argumento modo:

El cuarto modo puede resultar conveniente si necesita seleccionar el tamaño exacto de la imagen en píxeles cambiando los parámetros de alto y ancho. La imagen se actualizará inmediatamente.
Tenga en cuenta que en todos los modos excepto en el segundo, puede haber áreas sin rellenar en la celda y se pueden rellenar con color:

GOOGLETRANSLATE y DETECTLANGUAGE: traducir texto en celdas

Google Sheets tiene una interesante función GOOGLETRANSLATE que permite traducir texto directamente en celdas:

La sintaxis de la función es la siguiente:

GOOGLETRANSLATE (texto,,)

El texto es el texto que necesita ser traducido. Puedes poner el texto entre comillas y escribirlo directamente en la fórmula, pero es más conveniente hacer referencia a la celda en la que está escrito el texto.
– el idioma desde el que traducimos;
– el idioma al que traducimos.

El segundo y tercer argumento se especifican mediante un código de dos dígitos: es, fr, en, ru. También se pueden especificar en la propia función, pero se pueden tomar de una celda e incluso se puede determinar automáticamente el idioma del texto fuente.

Pero ¿y si queremos traducir a diferentes idiomas? Y al mismo tiempo, ¿no queremos especificar el idioma de origen manualmente cada vez?
fórmulas

hojas de google

Agregar etiquetas

(archivo-> crear una copia para copiar el archivo a tu Google Drive y poder editarlo).

Si el resultado de la fórmula ocupa más de una celda

Primero, sobre una característica importante de mostrar resultados de fórmulas en Google Sheets. Si su fórmula devuelve más de una celda, entonces se mostrará toda la matriz a la vez y ocupará tantas celdas y columnas como sea necesario (en Excel, esto requeriría que ingrese una fórmula matricial en todas estas celdas). Veamos cómo funciona esto en el siguiente ejemplo.

CLASIFICAR

Le ayudará a ordenar un rango de datos por una o más columnas y mostrar inmediatamente el resultado.

Sintaxis de la función:

=ORDENAR(datos a ordenar; columna_a_ordenar; ascendente; [columna_a_ordenar_2, ascendente_2; ...])

En el ejemplo de la captura de pantalla siguiente, ingresamos la fórmula solo en la celda D2 y ordenamos los datos por la primera columna (en lugar de VERDADERO/FALSO, puede ingresar VERDADERO/FALSO).

(en adelante, ejemplos de configuración de la tabla regional rusa, la configuración de registro se puede cambiar en el menú Archivo > configuración de la tabla)

¿Cómo agregar encabezados de tabla a SORT?

Usando llaves (), creamos una matriz de dos elementos, el encabezado de la tabla A1:B1 y la función ORDENAR, separando los elementos entre sí mediante un punto y coma.

¿Cómo combinar varios rangos de datos y ordenar (y más)?

Veamos cómo podemos combinar rangos para usarlos en funciones. Esto se aplica no solo a ORDENAR, esta técnica se puede utilizar en cualquier función donde sea posible, por ejemplo en BUSCARV o BÚSQUEDA.

Quienes leyeron el ejemplo anterior ya adivinaron qué hacer: abrir la llave y recoger los arrays a combinar, separándolos entre sí con punto y coma y cerrar la llave.

Puede combinar matrices y no usarlas en una fórmula, sino simplemente mostrarlas en una hoja, por ejemplo, recopilando datos de varias hojas de su libro de trabajo. Para la fusión vertical, solo necesita mantener la misma cantidad de columnas en todos los fragmentos (tenemos dos columnas en todas partes).

Y en la captura de pantalla a continuación hay un ejemplo de una unión horizontal, utiliza una barra invertida en lugar de un punto y coma y el número de filas en los fragmentos debe coincidir; de lo contrario, la fórmula devolverá un error en lugar del rango combinado.

(Los puntos y comas y las barras invertidas son separadores de elementos de matriz en la configuración regional rusa, si los ejemplos no funcionan para usted, a través del archivo de configuración de la tabla, asegúrese de que estos sean los que tiene)

Bueno, ahora volvamos a la matriz horizontal e insertémosla en la función ORDENAR. Ordenaremos los datos por la primera columna, en orden descendente.

Una unión se puede utilizar en cualquier función, lo principal es mantener el mismo número de columnas para una unión vertical o filas para una unión horizontal.

FILTRAR

Con FILTER podemos filtrar datos por una o más condiciones y mostrar el resultado en una hoja de trabajo o usar el resultado en otra función como rango de datos.

Sintaxis de la función:

FILTRO(rango; condición_1; [condición_2; ...])

una condición

Por ejemplo, tenemos una tabla con las ventas de nuestros empleados, mostremos los datos de un empleado de ella.

Ingresemos la siguiente fórmula en la celda E3:

=FILTRO(A3:C7;B3:B7=“Natalia Chistyakova”)

Tenga en cuenta que la sintaxis es ligeramente diferente de las fórmulas habituales, como SUMMESLIN, donde el rango de la condición y la condición misma se separarían mediante un punto y coma.

Una fórmula ingresada en una celda nos devuelve una matriz de 9 celdas con datos, pero después de los ejemplos con la función ORDENAR, esto ya no nos sorprende.

Además del signo igual (=), también puedes utilizar >, >=,<>(no igual)<, <=. Для текстовых условий подходят только = и <>, y para números o fechas puedes utilizar todos estos signos.

Dos condiciones y trabajar con una fecha.

Compliquemos la fórmula y agreguemos una condición más, según la fecha de ventas, dejando todas las ventas a partir del 01/02/17.

Así es como se verá la fórmula si ingresa los argumentos de la condición directamente en ella, preste atención a la conversión de la entrada de texto de fecha usando DATEVALUE:

=FILTRO(A3:C7;B3:B7=“Natalia Chistyakova”,A3:A7>=FECHAVALOR(“01.02.17”))

O así, si te refieres a celdas con argumentos:

=FILTRO(A3:C7;B3:B7=I6;A3:A7>=J6)

Gráfico interactivo usando FILTER y SPARKLINE

¿Sabes de qué otra manera puedes utilizar la función FILTRO? En lugar de enviar el resultado de la función a una hoja de trabajo, podemos usarlo como datos para otra función, como un minigráfico. Sparkline es una función que construye un gráfico en una celda basándose en nuestros datos. Sparkline tiene muchas configuraciones, como el tipo de gráfico, el color de los elementos, pero ahora no nos detendremos en ellos y usaremos la función sin configuraciones adicionales. . Pasemos a un ejemplo.

Lista desplegable. Nuestro horario cambiará dependiendo del empleado seleccionado en la lista desplegable hacemos la lista así:

• seleccione la celda E2;
• menú Datos > Verificación de datos;
• reglas: Valor del rango y en el rango seleccione la columna con empleados de los datos de origen, no se preocupe que los nombres se repitan, solo quedarán valores únicos en la lista desplegable;

La celda con la lista desplegable se convertirá en una condición para la fórmula FILTRO, escribámosla.

E insertaremos esta fórmula en la función SPARKLINE, que, en base a los datos recibidos, dibujará un gráfico en la celda.

= minigráfico (FILTRO (C3: C7, B3: B7 = E2))

Así es como se ve en dinámica:

Pero qué elegante puede verse SPARKLINE con configuraciones adicionales; en el trabajo real, el diagrama muestra los resultados de la actividad de un día, las columnas verdes son valores positivos, las rosas son negativos.

IMPORTANCIA

Para transferir datos de un archivo a otro, Google Sheets utiliza la función IMPORTRANGE.

¿En qué casos puede resultar útil?

• Necesita datos actualizados del expediente de sus colegas.
• Quiere procesar datos de un archivo al que tiene acceso "Sólo vista".
• Quiere recopilar tablas de varios documentos en un solo lugar para poder procesarlos o verlos.

Esta fórmula le permite obtener una copia de un rango de otro Google Sheets. En este caso, el formateo no se transfiere, solo datos (le diremos qué hacer con el formateo a continuación).

La sintaxis de la fórmula es la siguiente:

IMPORTRANGE(clave de hoja de cálculo; cadena de rango)
IMPORTRANGO(clave; rango)

spreadsheet_key (clave): secuencia de caracteres del atributo "key=" (clave) en el enlace a la tabla (después de "hojas de cálculo/.../").

Ejemplo de fórmula con clave:

=IMPORTRANGO("abcd123abcd123"; "hoja1!A1:C10")

En lugar de una clave de tabla, puede utilizar un enlace de documento completo:

Su archivo mostrará el rango A1:CM500 de la Hoja1 del archivo ubicado en el enlace correspondiente.

Si el número de columnas o filas en el archivo fuente puede cambiar, ingrese un rango abierto en el segundo argumento de la función (consulte también la subsección “Rangos de la forma A2:A”), por ejemplo:

Hoja1!A1:CM (si se agregarán filas)
Hoja1!A1:1000 (si se agregarán columnas)

! Tenga en cuenta que si está cargando un rango abierto (por ejemplo, A1:D), no podrá insertar manualmente ningún dato en las columnas A:D en el archivo donde se encuentra la fórmula IMPORTRANGE (es decir, el ubicación final donde se cargan los datos). Están, por así decirlo, "reservados" para todo el rango abierto; después de todo, su tamaño se desconoce de antemano.

Importar formato desde una tabla fuente

Como ya hemos señalado, IMPORTRANGE sólo carga los datos, pero no el formato de la tabla fuente. ¿Cómo lidiar con esto? Prepare el terreno con anticipación copiando el formato de la hoja de trabajo original. Para hacer esto, vaya a la hoja original y cópiela en su libro de trabajo:

Después de presionar el botón Copiar a… seleccione el libro de trabajo al que importará los datos. Por lo general, la tabla requerida está en la pestaña. Reciente(si realmente has trabajado con ella recientemente).

Después de copiar la hoja, seleccione todos los datos (haciendo clic en la esquina superior izquierda):

Y haga clic Borrar. Todos los datos desaparecerán, pero el formato permanecerá. Ahora puede ingresar a la función IMPORTRANGE y obtener una coincidencia completa de la hoja fuente, tanto en términos de datos como de formato:

IMPORTRANGE como argumento para otra función

IMPORTRANGE puede ser un argumento para otra función si el rango que está importando se ajusta a esa función.

Consideremos un ejemplo sencillo: el valor medio de las ventas de un rango situado en otro documento.

Este es el documento original. Sumemos los datos y necesitamos el promedio de ventas en 2016 (es decir, desde la celda D2 hasta abajo).

Primero importamos este rango:

Y luego usamos esto como argumento para la función PROMEDIO:


Obtenemos el resultado, que se actualizará cuando se agreguen nuevas filas en el archivo fuente en la columna D.

IMAGEN: agregar imágenes a las celdas

La función IMAGEN le permite agregar imágenes a las celdas de Google Sheets.

La función tiene la siguiente sintaxis:

IMAGEN(URL, , , )

La URL es el único argumento obligatorio. Este es un enlace a la imagen. El enlace se puede especificar directamente en la fórmula, usando comillas:

=IMAGEN(“http://shagabutdinov.ru/wp-content/uploads/2015/12/Run-or-Die.jpg”)

La última opción es más conveniente en la mayoría de los casos. Entonces, si tienes una lista de libros y enlaces a portadas, todo lo que necesitas es una fórmula para mostrarlos todos:

El argumento modo puede tomar cuatro valores (si se omite, el valor predeterminado será el primero):

1. la imagen se estira al tamaño de la celda manteniendo la relación de aspecto;
2. la imagen se estira sin mantener la relación de aspecto, llenándose completamente
3. la imagen se inserta con el tamaño original;
4. usted especifica las dimensiones de la imagen en los argumentos tercero y cuarto de y . , respectivamente, son necesarios sólo cuando el modo de argumento = 4. Se especifican en píxeles.

Veamos cómo se ven las imágenes en la práctica con cuatro valores diferentes del argumento modo:

El cuarto modo puede resultar conveniente si necesita seleccionar el tamaño exacto de la imagen en píxeles cambiando los parámetros de alto y ancho. La imagen se actualizará inmediatamente.
Tenga en cuenta que en todos los modos excepto en el segundo, puede haber áreas sin rellenar en la celda y se pueden rellenar con color:

GOOGLETRANSLATE y DETECTLANGUAGE: traducir texto en celdas

Google Sheets tiene una interesante función GOOGLETRANSLATE que permite traducir texto directamente en celdas:

La sintaxis de la función es la siguiente:

GOOGLETRANSLATE (texto,,)

El texto es el texto que necesita ser traducido. Puedes poner el texto entre comillas y escribirlo directamente en la fórmula, pero es más conveniente hacer referencia a la celda en la que está escrito el texto.
– el idioma desde el que traducimos;
– el idioma al que traducimos.

El segundo y tercer argumento se especifican mediante un código de dos dígitos: es, fr, en, ru. También se pueden especificar en la propia función, pero se pueden tomar de una celda e incluso se puede determinar automáticamente el idioma del texto fuente.

Pero ¿y si queremos traducir a diferentes idiomas? Y al mismo tiempo, ¿no queremos especificar el idioma de origen manualmente cada vez?

Aquí es donde resulta útil la función DETECTLANGUAGE. Tiene un solo argumento: el texto, cuyo idioma debe determinarse:

Como ocurre con cualquier característica, la belleza aquí está en la automatización. Puedes cambiar rápidamente el texto o el idioma; traduce rápidamente una frase a 10 idiomas y así sucesivamente. Por supuesto, entendemos que se trata de un texto de un traductor en línea; la calidad será la adecuada.

Evgeniy Namokonov y Renat Shagabutdinov, y también tenemos un canal de Telegram donde analizamos diferentes casos con Google Sheets, si está interesado, venga a visitarnos, el enlace se puede encontrar en mi perfil.