Trucos de vida de Excel para aquellos involucrados en informes y procesamiento de datos

2024 Autor: Malcolm Clapton | [email protected]. Última modificación: 2024-01-13 01:16

En esta publicación, Renat Shagabutdinov, subdirector general de Mann, Ivanov y Ferber Publishing House, comparte algunos trucos geniales de Excel. Estos consejos serán útiles para cualquier persona involucrada en varios informes, procesamiento de datos y creación de presentaciones.

Este artículo contiene técnicas simples para simplificar su trabajo en Excel. Son especialmente útiles para quienes se dedican a la elaboración de informes de gestión, preparan varios informes analíticos basados en descargas de 1C y otros informes, forman presentaciones y diagramas de ellos para la gestión. No pretendo ser una novedad absoluta; de una forma u otra, estas técnicas probablemente se discutieron en los foros o se mencionaron en artículos.

Alternativas simples a BUSCARV y BUSCARH, si los valores deseados no están en la primera columna de la tabla: BUSCAR, ÍNDICE + BÚSQUEDA

Las funciones BUSCARV y BUSCARH solo funcionan si los valores deseados están en la primera columna o fila de la tabla de la que planea obtener datos.

De lo contrario, hay dos opciones:

1. Utilice la función BUSCAR.

   Tiene la siguiente sintaxis: LOOKUP (lookup_value; lookup_vector; result_vector). Pero para que funcione correctamente, los valores del rango view_vector deben ordenarse en orden ascendente:

   

2. Utilice una combinación de funciones COINCIDIR e ÍNDICE.

   La función COINCIDIR devuelve el número ordinal de un elemento en la matriz (con su ayuda puede encontrar en qué fila de la tabla está el elemento buscado), y la función INDICE devuelve un elemento de matriz con un número dado (que descubriremos utilizando la función COINCIDIR).

   

   Sintaxis de la función:

   • BÚSQUEDA (valor_búsqueda; matriz_búsqueda; tipo_coincidencia) - para nuestro caso, necesitamos un tipo de coincidencia "coincidencia exacta", corresponde al número 0.

   • ÍNDICE (matriz; número_línea; [número_columna]). En este caso, no es necesario especificar el número de columna, ya que la matriz consta de una fila.

Cómo llenar rápidamente celdas vacías en una lista

La tarea es completar las celdas de la columna con los valores en la parte superior (para que el tema esté en cada fila de la tabla, y no solo en la primera fila del bloque de libros sobre el tema):

Seleccione la columna "Asunto", haga clic en la cinta en el grupo "Inicio", el botón "Buscar y seleccionar" → "Seleccionar un grupo de celdas" → "Celdas en blanco" y comience a ingresar la fórmula (es decir, ponga un igual signo) y consulte la celda en la parte superior, simplemente haciendo clic en la flecha hacia arriba en su teclado. Después de eso, presione Ctrl + Enter. Después de eso, puede guardar los datos recibidos como valores, ya que las fórmulas ya no son necesarias:

Cómo encontrar errores en una fórmula

Cálculo de una parte separada de una fórmula

Para comprender una fórmula compleja (en la que otras funciones se utilizan como argumentos de función, es decir, algunas funciones están anidadas en otras) o para encontrar la fuente de errores en ella, a menudo es necesario calcular parte de ella. Hay dos formas sencillas:

1. Para calcular parte de una fórmula directamente en la barra de fórmulas, seleccione esa parte y presione F9:

   

   En este ejemplo, hubo un problema con la función BÚSQUEDA: los argumentos se intercambiaron en ella. Es importante recordar que si no cancela el cálculo de la parte de la función y presiona Enter, la parte calculada seguirá siendo un número.

2. Haga clic en el botón Calcular fórmula en el grupo Fórmulas en la cinta:

   

   En la ventana que aparece, puede calcular la fórmula paso a paso y determinar en qué etapa y en qué función se produce un error (si lo hubiera):

   

Cómo determinar de qué depende o se refiere una fórmula

Para determinar de qué celdas depende una fórmula, en el grupo Fórmulas de la cinta, haga clic en el botón Celdas que afectan:

Aparecen flechas para indicar de qué depende el resultado del cálculo.

Si se muestra el símbolo resaltado en la imagen en rojo, entonces la fórmula depende de las celdas en otras hojas o en otros libros:

Al hacer clic en él, podemos ver exactamente dónde se encuentran las celdas o rangos que influyen:

Junto al botón "Celdas de influencia" está el botón "Celdas dependientes", que funciona de la misma manera: muestra flechas desde la celda activa con una fórmula a las celdas que dependen de ella.

El botón "Eliminar flechas", ubicado en el mismo bloque, le permite eliminar flechas para influir en las celdas, flechas para las celdas dependientes o ambos tipos de flechas a la vez:

Cómo encontrar la suma (número, promedio) de los valores de celda de varias hojas

Digamos que tiene varias hojas del mismo tipo con datos que desea agregar, contar o procesar de alguna otra manera:

Para hacer esto, en la celda en la que desea ver el resultado, ingrese una fórmula estándar, por ejemplo SUM (SUM), y especifique el nombre de la primera y última hoja de la lista de aquellas hojas que necesita procesar en el argumento, separado por dos puntos:

Recibirá la suma de las celdas con la dirección B3 de las hojas "Data1", "Data2", "Data3":

Este direccionamiento funciona para hojas ubicadas consecuentemente … La sintaxis es la siguiente: = FUNCTION (first_list: last_list! Range reference).

Cómo crear frases de plantilla automáticamente

Usando los principios básicos para trabajar con texto en Excel y algunas funciones simples, puede preparar frases de plantilla para informes. Varios principios para trabajar con texto:

• Concatenamos el texto usando el signo & (puedes reemplazarlo con la función CONCATENAR, pero eso no tiene mucho sentido).
• El texto siempre se escribe entre comillas, las referencias a celdas con texto siempre están sin él.
• Para obtener el carácter de servicio "comillas", utilice la función CHAR con el argumento 32.

Un ejemplo de cómo crear una frase de plantilla usando fórmulas:

Resultado:

En este caso, además de la función CHAR (para mostrar las cotizaciones), se utiliza la función SI, que permite cambiar el texto en función de si hay una tendencia de ventas positiva, y la función TEXTO, que permite visualizar el número en cualquier formato. Su sintaxis se describe a continuación:

TEXTO (valor; formato)

El formato se especifica entre comillas, como si estuviera ingresando un formato personalizado en la ventana Formato de celdas.

También se pueden automatizar textos más complejos. En mi práctica, hubo la automatización de comentarios largos pero rutinarios a los informes de gestión en el formato INDICADOR disminuyó / aumentó en XX en relación con el plan, principalmente debido al crecimiento / disminución de FACTOR1 en XX, crecimiento / disminución de FACTOR2 en YY …”con una lista cambiante de factores. Si escribe tales comentarios con frecuencia y el proceso de escribirlos puede ser algorítmico, vale la pena, una vez desconcertante, crear una fórmula o una macro que le ahorre al menos parte del trabajo.

Cómo almacenar datos en cada celda después de la concatenación

Cuando combina celdas, solo se conserva un valor. Excel advierte sobre esto cuando intenta fusionar celdas:

En consecuencia, si tenía una fórmula en función de cada celda, dejará de funcionar después de combinarlas (error # N / A en las líneas 3-4 del ejemplo):

Para fusionar celdas y aún conservar los datos en cada una de ellas (tal vez tenga una fórmula como en este ejemplo abstracto; tal vez desee fusionar celdas, pero conservar todos los datos para el futuro u ocultarlos intencionalmente), combine cualquier celda en la hoja, selecciónelos y luego use el comando Copiar formato para transferir el formato a las celdas que necesita combinar:

Cómo construir un pivote a partir de múltiples fuentes de datos

Si necesita crear un pivote a partir de varias fuentes de datos a la vez, tendrá que agregar el "Asistente para tablas dinámicas y gráficos" a la cinta o al panel de acceso rápido, que tiene esa opción.

Puede hacer esto de la siguiente manera: "Archivo" → "Opciones" → "Barra de herramientas de acceso rápido" → "Todos los comandos" → "Asistente para tablas dinámicas y gráficos" → "Agregar":

Después de eso, aparecerá un icono correspondiente en la cinta, haciendo clic en el que llama al mismo asistente:

Al hacer clic en él, aparece un cuadro de diálogo:

En él, debe seleccionar el elemento "En varios rangos de consolidación" y hacer clic en "Siguiente". En el siguiente paso, puede seleccionar "Crear un campo de página" o "Crear campos de página". Si desea crear de forma independiente un nombre para cada una de las fuentes de datos, seleccione el segundo elemento:

En la siguiente ventana, agregue todos los rangos sobre cuya base se construirá el pivote y déles nombres:

Después de eso, en el último cuadro de diálogo, especifique dónde se colocará el informe de la tabla dinámica, en una hoja nueva o existente:

El informe de la tabla dinámica está listo. En el filtro "Página 1", puede seleccionar solo una de las fuentes de datos, si es necesario:

Cómo calcular el número de apariciones del texto A en el texto B ("tarifa MTS SuperMTS" - dos apariciones de la abreviatura MTS)

En este ejemplo, la columna A contiene varias líneas de texto, y nuestra tarea es averiguar cuántas veces cada una de ellas contiene el texto de búsqueda ubicado en la celda E1:

Para resolver este problema, puede utilizar una fórmula compleja que consta de las siguientes funciones:

1. DLSTR (LEN): calcula la longitud del texto, el único argumento es el texto. Ejemplo: DLSTR ("máquina") = 6.
2. SUSTITUIR: reemplaza un texto específico en una cadena de texto por otro. Sintaxis: SUBSTITUTE (texto; antiguo_texto; nuevo_texto). Ejemplo: SUBSTITUTE (“coche”; “auto”; “”) = “móvil”.
3. SUPERIOR: reemplaza todos los caracteres de una cadena con mayúsculas. El único argumento es el texto. Ejemplo: SUPERIOR (“máquina”) = “COCHE”. Necesitamos esta función para realizar búsquedas que no distingan entre mayúsculas y minúsculas. Después de todo, SUPERIOR ("coche") = SUPERIOR ("Máquina")

Para encontrar la ocurrencia de una determinada cadena de texto en otra, debe eliminar todas sus ocurrencias en la original y comparar la longitud de la cadena resultante con la original:

DLSTR ("Tariff MTS Super MTS") - DLSTR ("Tariff Super") = 6

Y luego divida esta diferencia por la longitud de la cadena que estábamos buscando:

6 / DLSTR ("MTS") = 2

Es exactamente dos veces que la línea "MTS" se incluye en la original.

Queda por escribir este algoritmo en el lenguaje de las fórmulas (denotemos por "texto" el texto en el que buscamos ocurrencias, y por "buscado" - aquel cuyo número de ocurrencias nos interesa):

= (DLSTR (texto) -LSTR (SUSTITUIR (SUPERIOR (texto); SUPERIOR (búsqueda), ""))) / DLSTR (búsqueda)

En nuestro ejemplo, la fórmula se ve así:

= (DLSTR (A2) -LSTR (SUSTITUTO (SUPERIOR (A2), SUPERIOR ($ E $ 1), “”))) / DLSTR ($ E $ 1)