Habilidades de aplicación de Excel de Tao Zeyu n.° 39: trabajar con nombres y definir nombres

1. Trabajar con nombres

1. Usar nombres en fórmulas

Si necesita llamar el nombre en la fórmula de la celda, puede hacer clic en pestaña de fórmula y uso Haga clic en el botón desplegable de fórmula y seleccione el nombre correspondiente. También puede ingresarlo manualmente en el estado de edición de fórmula. El nombre también aparecerá en la lista "Autocompletar fórmula".

Como se muestra en la Figura 1, el nombre de la tasa de impuesto comercial "Taxtate" se define en el libro. Ingrese sus iniciales "T" o "Ta" en la celda y el nombre aparecerá en la Fórmula Autocompletar. en la lista.

Consejo: En la lista de Autocompletar fórmula, todas las funciones integradas usan letras mayúsculas y los nombres o listas definidos se mostrarán de acuerdo con el método definido por el usuario. Independientemente de si se usan mayúsculas o minúsculas, una vez ingresada en la fórmula, cambiará automáticamente a la forma definida y se usará normalmente.

2. Nombres utilizados en el formato condicional y la validación de datos

El formato condicional y la validación de datos se utilizan ampliamente en el trabajo práctico, pero no admiten el uso directo de matrices constantes y áreas fusionadas referencias y referencias cruzadas, por lo que debe definirse como un nombre antes de llamar.

Además, en Excel 97~2003, no se admiten referencias directas a rangos de celdas de otras hojas de cálculo en formato condicional y validez de datos, y también se puede resolver definiendo nombres. La versión Excel 2010 no tiene este problema.

Nota: No puede utilizar "nombres a nivel de hoja" de otras hojas de trabajo en formato condicional y validación de datos.

3. Utilice nombres en gráficos

Excel admite el uso de nombres para dibujar gráficos, pero al formular la fuente de datos del gráfico, se debe utilizar el formato de nombre completo. Por ejemplo, el nombre de nivel de libro "Ventas" se define en un libro llamado "Nombrado".

En el cuadro de edición del valor de la serie del cuadro de diálogo Editar serie de datos, ingrese el formato del nombre completo, es decir, el nombre del libro de trabajo con el signo de exclamación:

= naming.xlsx!Sales

Si ingresa "=Ventas" directamente en el cuadro de edición del valor de la serie, aparecerá un cuadro de diálogo de advertencia.

2. Consejos para definir nombres

1. Defina nombres usando referencias relativas y referencias mixtas

Al ingresar referencias de celda usando clics del mouse en los nombres, de forma predeterminada , se utiliza el método de referencia absoluta con el nombre de la hoja de trabajo. Por ejemplo, haga clic en el botón doblar en el lado derecho del cuadro de diálogo de ubicación de referencia y luego haga clic para seleccionar la celda A1 en la hoja de trabajo Hoja1, lo que equivale a ingresar "Hoja1". $A$1". Cuando necesite utilizar referencias relativas o referencias mixtas, puede cambiar presionando la tecla F4.

Usar una referencia relativa en una fórmula en una celda forma una relación de posición relativa con la celda donde se encuentra la fórmula: usar una referencia relativa en un nombre forma una relación de posición relativa con la celda activa cuando el nombre está definido. Por ejemplo, cuando la celda B1 es la celda actualmente activa, cree un nombre de definición

Ejemplo 1 Evitar la eliminación de la fila anterior en el diario

Como se muestra en la Figura 2, en la columna E del diario financiero familiar, use "=E3 C7-D7" comenzando de la celda E4 Fórmula simple para calcular el saldo actual. Pero cuando se elimina una determinada fila de los datos, se producirá un error de referencia #REF en la fórmula debajo, como la celda E7.

Usar una referencia relativa o un nombre de definición de referencia mixta para hacer referencia a la fila anterior puede resolver este problema. He aquí cómo hacerlo.

Paso 1 Seleccione la celda E4 (normalmente se selecciona la primera fila de la fórmula) y haga clic en el botón Definir nombre.

Paso 2 Como se muestra en la Figura 5, ingrese "Saldo anterior" en el cuadro de edición de nombre del cuadro de diálogo Nuevo nombre, ingrese la siguiente fórmula en el cuadro de edición de posición de referencia y haga clic en el botón Aceptar para salir del cuadro de diálogo: = Hoja1!$E3

Paso 3 Ingrese la siguiente fórmula en la celda E4 y haga doble clic en el "controlador de relleno" para copiar hacia abajo:

= Saldo anterior C4-D4

2. Omita el nombre de la definición del nombre de la hoja de cálculo

De forma predeterminada, cuando utiliza el mouse para especificar una referencia de celda en el cuadro de edición de posición de referencia, la fórmula de definición se generará como un complete la referencia absoluta con el nombre de la hoja de trabajo, en forma de Por ejemplo:

=¡Enero!$A$1

Cuando necesite hacer referencia a un rango de celdas específico en diferentes hojas de trabajo, como Febrero, marzo, etc. En la tabla, también debe eliminar manualmente el nombre de la hoja de trabajo, pero conservar el signo de exclamación para lograr una referencia relativa al "nombre de la hoja de trabajo".

Ejemplo 2 La aplicación obtiene el nombre de esta hoja de trabajo en cada tabla

Como se muestra en la Figura 3, es el informe de avance diario de un determinado proyecto de ingeniería, en el cual se muestra el avance acumulado. el progreso acumulativo del día anterior. La suma del progreso y el progreso del día actual, los nombres de las hojas de trabajo están representados por números como 1, 2 y 3 respectivamente. Entre ellos, la hoja de trabajo 1 es la entrada de datos inicial manualmente. Será problemático repetir las referencias entre tablas en nuevos informes todos los días. Puede usar el "nombre de la hoja de trabajo predeterminado" en el nombre para obtener el nombre de la hoja de trabajo y usarlo como. Función INDECT. Datos de referencia.

El método de operación específico es el siguiente.

Paso 1 Como se muestra en la Figura 4, haga clic en el botón Definir nombre en la pestaña Fórmula para definir el nombre "sht", y su fórmula de posición de referencia es:

=REPLACE(CELL ("nombre de archivo,!A1),1,FIND(")",CELL("nombre de archivo",!A1)),"")

Paso 2 Ingrese la siguiente fórmula en la celda D3 de la hoja de trabajo "2 ", Y haga doble clic en el controlador de relleno para copiar hacia abajo:

=INDIRECT(sht-1”!RC”,0) C3

Paso 3 Utilice la misma fórmula para configurar el hoja de trabajo "3" y más Muchos otros informes diarios

En este ejemplo, el nombre "!A1" se usa para referirse a la celda A1. Cuando este nombre se usa en la hoja de trabajo 2, equivale a ". 2!A1" cuando se usa en la hoja de trabajo 3. Es equivalente a "3!A1". Por lo tanto, sht puede devolver 3 cuando la hoja de trabajo es "3", y luego usar sht-1 para obtener el nombre de la hoja de trabajo "2" del día anterior, y use el estilo de referencia R1CI de la función INDIRECTA como referencia relativa Método para obtener la misma área de la hoja de trabajo correspondiente (la estructura de cada hoja de trabajo es consistente, es decir, la celda donde se realiza la acumulación)

3. Defina el nombre de la referencia permanente

en el nombre. La referencia al rango de celdas, incluso si es una referencia absoluta, puede cambiar debido a la inserción de filas (columnas). ), eliminar filas (columnas), operaciones de corte, etc. en el rango de celdas donde se encuentran los datos, lo que hace que el nombre sea inconsistente con el rango de referencia real esperado.

Como se muestra en la Figura 5. , defina las celdas B3:B12 como el nombre "Idioma", y el valor predeterminado es una referencia absoluta. Después de cortar el rango de celdas A8:B8 o toda la fila 8, inserte la celda cortada en la línea 13 y luego abra el administrador de nombres. encontrará que el rango de celdas al que hace referencia "Idioma" cambia a B3:B11.

Si desea hacer referencia a la Hoja1 de forma permanente en el rango de celdas B3:B12 de la tabla, puede cambiar la referencia. posición del nombre "Idioma" a:

=INDIRECT("Hoja1!B3:B12")

Si desea este nombre, puede hacer referencia a los respectivos rangos de celdas B3:B12 en cada hoja de trabajo como 0. Puede cambiar la fórmula de posición de referencia de "chino" a:

=INDIRECT("B3:B12")

Dado que "B3:B12" es Si se usa aquí como constante de texto, no hay necesidad de preocuparse por la eliminación real de la celda y no hay necesidad de usar una referencia absoluta como "$B$3:$B$12".

4. Defina el nombre de la referencia dinámica

En el trabajo real, necesitamos agregar continuamente más datos a la tabla, aunque podemos usar un rango fijo preestablecido en la fórmula. por ejemplo, si los datos no superan las 100 filas, utilice referencias como "$A$1:$A$100" en la fórmula, pero cuando el número máximo posible de filas de datos es muy diferente del número inicial, por ejemplo, no Puede haber solo unas pocas o docenas de filas de datos, puede haber decenas de miles de filas de datos, por lo que ajustar esta referencia en la fórmula será problemático.

Al utilizar la función de referencia para definir el nombre, se puede "hacer referencia dinámicamente" al área de datos en tiempo real de acuerdo con los cambios en el área real utilizada.

Ejemplo 3 Defina el nombre de referencia dinámica del área extendida con entrada de datos

Como se muestra en la Figura 6, es el registro de ventas de un determinado departamento comercial de sucursal en la celda G2. de acuerdo con los valores de E2 y F2 Al calcular las ventas por mes y nombre del producto, se deben utilizar referencias dinámicas porque los datos de ventas se complementarán continuamente.

Puedes utilizar la función DESREF para hacer referencia dinámicamente al área de datos definiendo un nombre basado en el número de fechas en la columna A. Las operaciones específicas son las siguientes.

Paso 1 Haga clic en el botón Definir nombre y cree nuevos nombres "Fecha", "Producto" y "Ventas" respectivamente. Las fórmulas en las posiciones de referencia son las siguientes:

=OFFSET. (Hoja1 !$A$2,,,COUNT(Hoja1!$A：$A))

=OFFSET(Fecha,,1)

=OFFSET(Fecha,,2)

Paso 2 Ingresa la siguiente fórmula en la celda G2:

=SUMAPRODUCTO((MES(Fecha)=E2)*(Producto=E2)*Ventas)

En este ejemplo, la función COUT cuenta el número de valores en la columna A (las fechas son valores) y utiliza la función OFFSET para devolver un rango de celdas que comienza en la celda A2 y con la misma altura que el número de fechas. Se continúan ingresando nuevos datos. El área de referencia aumentará automáticamente con los resultados devueltos por COUNT. Las áreas de referencia de productos y volumen de ventas se obtienen utilizando el área de referencia dinámica definida "fecha" y las columnas de compensación 1 y 2 respectivamente.

Nota: 1. CONTAR se utiliza para contar el número de valores. Si necesita contar texto, utilice la función CONTARA y deduzca el número de líneas de título.

2. Cuando hay una fila en blanco en el área de la celda, al usar este método para hacer referencia al área no se podrá hacer referencia a la última fila de datos real. Puede definir el nombre ubicando el último valor. y el último texto a la izquierda.

?