Excel-piratería vida para los involucrados en el procesamiento de información y datos

Renat no es la primera vez que un autor invitado habla sobre Layfhakere. Anteriormente, hemos publicado un excelente material de él acerca de cómo crear un plan de formación: principal libro y recursos en línea, así como algoritmo incrementales crear un plan de formación.

Este artículo contiene algunas técnicas sencillas que ayudan simplificar el trabajo en Excel. son particularmente útiles para los implicados en los informes de gestión, se prepara una variedad de analítica Los informes basados ​​en los desembarques de informes 1C y otras formas de estas presentaciones y diagramas para liderazgo. No pretende ser una novedad absoluta - de una forma u otra, estos métodos sin duda discuten en foros o se refiere el artículo.

alternativa sencilla a BUSCARV y BUSCARH, si los valores requeridos no están en la primera columna de la tabla: VISTA, ÍNDICE + Match

BUSCARV (CONSULV) Y PGR (BUSCARH) sólo funcionan si los valores de destino están en la primera columna o fila de la tabla de la que desea obtener datos.

En otros casos, hay dos opciones:

1. Use la función VISTA (BUSCAR).
   Ella tiene la siguiente sintaxis: OBSERVACIÓN (buscar_valor Buscar vector; vektor_rezultata). Pero para que funcione correctamente, es necesario que los valores de rango Buscar vector Ellos se clasifican en orden ascendente:
2. PARTIDO utilizar una combinación de funciones (Match) y un índice (INDICE).
   PARTIDO función devuelve el número de serie del elemento de la matriz (con su ayuda se puede encontrar, en una fila de la tabla el elemento deseado) y el índice de función devuelve un elemento de matriz con un número predeterminado (que sabemos a la función PARTIDO).opciones de sintaxis:
   • PARTIDO (buscar_valor massiv_poiska; tipo_de_coincidencia) - en nuestro caso, necesitamos un tipo de comparación "coincidencia exacta", que corresponde al número 0.
   • ÍNDICE (matriz; row_num; [Núm_columna]). En este caso, no es necesario especificar el número de columna como la matriz consiste en una sola línea.

La rapidez con que llenar las celdas vacías en la lista

La tarea - para llenar las células con los valores en la columna por encima (sujeto a pie en cada fila de la tabla, no sólo en la primera fila de bloques de libros sobre el tema):

Seleccione la columna "Asunto", pulse en la cinta en el grupo de botón de "Inicio" "Buscar y seleccionar» → «Resalte el grupo de células» → «en blanco celular "y empiece a introducir la fórmula (es decir, poner un signo igual) y se refiere a la célula desde arriba, simplemente presionando la flecha hacia arriba teclado. A continuación, pulse Ctrl + Enter. Después de eso, los datos se pueden guardar como un valor, porque las fórmulas ya no son necesarios:

¿Cómo encontrar errores en la fórmula

Cálculo fórmula parte separada

Para entender la fórmula compleja (en el que como argumentos de la función usando otro función, es decir, algunas funciones están integradas en el otro), o para encontrar en él una fuente de errores, a menudo necesidad de calcular parte de ella. Hay dos maneras fáciles:

1. Para el cálculo de la parte derecha de la fórmula en la barra de fórmulas, seleccione la pieza, y presione F9:
   
   En este ejemplo, hay un problema con la función de búsqueda (SEARCH) - había argumentos hasta mixtos. Es importante recordar que si no cancela el cálculo de la función y pulse la tecla ENTER, entonces la porción calculada permanecerá número.
2. Pulsar el botón "Calcular la fórmula" en el grupo "fórmula" en la cinta:
   
   
   En la ventana que aparece, se puede calcular la fórmula paso a paso y para determinar en qué momento y en qué función error (si lo hay):
   

Cómo determinar lo que determina que la fórmula o se hace referencia

Para determinar cuál de las células depende de la fórmula en el grupo de la "fórmula" en la cinta, haga clic en el botón "Rastrear precedentes":

Hay flechas apuntando a partir del cual depende el resultado.

Si el símbolo aparece en la imagen seleccionada en rojo, la fórmula depende de las células que se encuentran en otras listas o en otros libros:

Al hacer clic en él, podemos ver exactamente donde la influencia de la celda o rango:

Junto a la "Rastrear precedentes" hay un botón de "dependientes", que trabajan de la misma manera: muestra las flechas de la celda activa con la fórmula a las células que dependen de ella.

Botón "Eliminar" flechas situadas en el mismo bloque, permite eliminar precedentes, dependientes, o de ambos tipos a la vez las flechas:

¿Cómo encontrar los valores de la cantidad (número, media) de las células con varias hojas

Digamos que usted tiene varias hoja similar con los datos que desea agregar, encontrar o procesar de alguna otra manera:

Para ello, la celda en la que desea ver el resultado, introduzca la fórmula estándar, tales como SUM (SUMA), y introducir el argumento seguido de dos puntos y el último nombre de la primera hoja de la lista de hojas que necesite proceso:

Usted recibirá la cantidad de células dirección de B3 con sábanas "Data1", "Data 2", "datos3":

Estas obras de direccionamiento para las hojas dispuestas sucesivamente. La sintaxis es: = Característica (pervyy_list: posledniy_list referencia a la gama!).

¿Cómo construir automáticamente un frases fórmulas

Utilizando los principios básicos de la utilización de texto en Excel y algunas funciones simples, pueden ser frases fórmulas preparadas para los informes. Varios principios de trabajo con el texto:

• Combinar texto con la marca y (se puede sustituir su función concatenar (concatenar), pero esto no sirve de mucho).
• El texto siempre se escribe entre comillas, las referencias de celda con el texto - no siempre.
• Para obtener el carácter "citas" especiales, utilizando la función de caracteres (CHAR) argumento 32.

Ejemplo crear frases plantilla usando fórmulas:

como resultado:

En este caso, además de la función de símbolo (CHAR) (para mostrar las comillas) usando la función SI (SI), que permite cambiar el texto Dependiendo de si hay una tendencia positiva en las ventas y la función del texto (TEXT), lo que permite mostrar un número en cualquier formato. Su sintaxis se describe a continuación:

TEXTO (valor; formato)

El formato se especifica entre comillas en la misma forma que si se ha introducido un formato personalizado en el "Formato de celdas".

Puede automatizar textos más complejos. En mi práctica era automatizar mucho tiempo, pero los comentarios de rutina sobre las cuentas de gestión en el formato "figura cayeron / rosa en el XX en el plan, debido principalmente al aumento / disminución de la FAKTORA1 XX, aumento / disminución de la FAKTORA2 YY... »con la lista cambiando factores. Si se escribe a menudo este tipo de comentarios y el proceso de escribir algoritmización lata - vale la pena una vez desconcertado crear una fórmula o macro que le ahorrará a partir de al menos una parte de la obra.

Cómo guardar los datos en cada celda después de la fusión

Cuando se combinan las células retuvieron sólo un valor. Excel le avisa cuando intenta combinar celdas:

En consecuencia, si tenía una fórmula que depende de cada célula, se dejará de trabajar después de su unión (error # N / A en las líneas 3-4 ejemplos):

Para combinar celdas, preservando al mismo tiempo los datos en cada uno de ellos (tal vez usted tiene una fórmula, como en este ejemplo, el resumen; es posible que desee combinar celdas, pero mantener todos los datos en el futuro, o para ocultar sus intenciones), se combinan en cualquier célula hoja, seleccionarlos, y luego usar el formato de transferencia comando "Copiar formato" en las células que usted necesita y combinar:

¿Cómo construir un resumen de varias fuentes de datos

Si usted necesita para construir un consolidan a partir de múltiples fuentes de datos, es necesario añadir a la cinta o la barra de acceso rápido "Asistente para tablas y gráficos" en la que hay una opción.

Esto se puede hacer de la siguiente manera: "Archivo» → «Ajustes» → «rápida barra de acceso» → «Todo el equipo» → «Asistente para tablas dinámicas y diagramas» → «Añadir»:

Después de eso, la cinta será un icono correspondiente, haciendo clic sobre el que hace que la adición del maestro:

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

En lo que tendrá que seleccionar "Unas bandas de consolidación" y haga clic en "Siguiente". En la siguiente sección, puede seleccionar la opción "Crear un solo campo de página" o "Crear un campo de página." Si quieres a ti mismo para llegar a un nombre para cada una de las fuentes de datos - para seleccionar el segundo elemento:

En el siguiente cuadro, añadir todos los rangos en la base de la cual construir un resumen, y pedirles los nombres:

Después de ese punto, será el anfitrión de un informe de tabla dinámica en el último cuadro de diálogo - en una ya existente o una nueva hoja de cálculo:

Listo informe de tabla dinámica. El filtro "Página 1" se puede elegir sólo una de las fuentes de datos, si es necesario:

Cómo calcular el número de apariciones del texto en el texto de un B (SuperMTS «arancelarias MTS" - dos ocurrencias de los MTS abreviatura)

En este ejemplo, la columna A, hay unas pocas líneas de texto, y nuestra tarea - para averiguar cuántas veces cada uno de ellos cumple con el texto deseado en la celda E1:

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

1. LEN (LEN) - calcula la longitud del texto, un solo argumento - el texto. Ejemplo: LEN ( "máquina") = 6.
2. SUSTITUIR (SUSTITUIR) - sustituye a la cadena de texto de un determinado texto con otro. Sintaxis: SUSTITUTO (texto; star_tekst; nov_tekst). Ejemplo: suplente ( "coche", "coche", "") = "móvil".
3. SUPERIOR (SUPERIOR) - reemplaza todos los caracteres de una cadena a mayúsculas. El único argumento - el texto. Ejemplo: SUPERIOR ( "máquina") = "máquina". será necesaria esta función para hacer sensible a las mayúsculas la búsqueda. Después de todo, SUPERIOR ( "Máquina") = SUPERIOR ( "Máquina")

Para buscar una entrada de una cadena de texto a otro, debe quitar todas las veces que aparece en el original y comparar la longitud de la cadena del original:

LEN ( "Tarifa de Super MTS MTS") - LEN ( "Super Rate") = 6

Y luego divida la diferencia entre la longitud de la línea, que estábamos buscando:

6 / LEN ( "MTS") = 2

Eso es el doble de la línea "MTS" es en el original.

Queda por escrito en el lenguaje de las fórmulas algoritmo (denotado "texto" del texto que estamos buscando la entrada y "deseada" - el número de ocurrencias que nos interesa):

= (LEN (texto) -DLSTR (suplente (SUPERIOR (texto); SUPERIOR (buscada); ""))) / LEN (buscada)

En nuestro ejemplo, la fórmula es la siguiente:

= (LEN (A2) -DLSTR (suplente (SUPERIOR (A2); SUPERIOR ($ E $ 1); ""))) / LEN ($ E $ 1)