Pavlov Nicholas.

En este artículo, me gustaría presentarle las técnicas más efectivas para trabajar en Microsoft Excel, recolectadas por mí durante los últimos 10 años de trabajo en proyectos y capacitación en este maravilloso programa. No hay una descripción de las tecnologías de superstrato, pero hay técnicas para todos los días, sencillas y eficientes, descritas sin "agua", solo el "residuo seco". No tendrá más de uno o dos minutos para desarrollar la mayoría de estos ejemplos, pero le ahorrarán mucho más.

Transición rápida a la hoja deseada.

¿Trabajas con libros de Excel que consisten en una gran cantidad de hojas? Si hay más de una docena, entonces cada transición a la siguiente necesaria para que no se convierta en un pequeño problema. Una solución simple y elegante de tal tarea es hacer clic en la esquina inferior izquierda de la ventana en los botones de desplazamiento de las hojas de hojas no a la izquierda, y el botón derecho del ratón: aparecerá la tabla de contenidos con la lista completa de todos Las hojas y en la hoja deseada se pueden transferir a un movimiento:

Es mucho más rápido que desplazarse de las hojas de las hojas por estos mismos botones en busca de la deseada.

Copia sin formato de daño.

¿Cuántos cientos (miles?) Una vez vi esta foto, detrás de mis oyentes durante los entrenamientos: el usuario ingresa a la fórmula en la primera celda y "se estira" en toda la columna, interrumpiendo el formato de las siguientes líneas, ya que esto El método copia no solo la fórmula, sino el formato celular. En consecuencia, entonces tienes que corregir manualmente los daños. Segundo en la copia y luego 30: para reparar el diseño dañado al copiar.

Comenzando con Excel 2002, hay una solución a este problema, simple y elegante. Inmediatamente después de copiar (arrastrar) fórmulas a toda la columna, debe usar la etiqueta inteligente: un pequeño icono que aparece temporalmente en la esquina inferior derecha del rango. Al presionar, mostrará una lista de las posibles opciones de copia, donde puede seleccionar solo los valores del (Formato de relleno). En este caso, las fórmulas se copian y se formatan, no:

Copia solo celdas visibles

Si trabaja en Microsoft Excel más de una semana, ya debería haberse enfrentado a un problema similar: en algunos casos, al copiar las celdas que inserta, se insertan más de lo que fue, a primera vista, copiado. Esto puede ocurrir si el rango de copia incluyó cadenas / columnas ocultas, agrupaciones, resultados intermedios o filtrado. Considere como ejemplo uno de estos casos:

Los resultados intermedios se cuentan en esta tabla y se realiza una agrupación de cuerdas en las ciudades: es fácil de entender los botones "más-menos" a la izquierda de la mesa y en las brechas en la numeración de líneas visibles. Si selecciona, copie y pegue datos de esta tabla de la manera habitual, entonces recibiremos 24 líneas innecesarias. ¡También queremos copiar e insertar solo los resultados!

Puede resolver el problema, resaltar cuidadosamente a cada fila del resultado y mantener la tecla CTRL, en cuanto a la selección de rangos no negativos. Pero si no hay tres, cinco de esas líneas, sino unos pocos cientos o miles? Hay otra manera más rápida y conveniente:

Resalte el rango copiado (en nuestro ejemplo es A1: C29)

Presione la tecla del teclado F5 y luego seleccione el botón (especial) en la ventana que se abre.
Aparecerá una ventana, permitiendo que el usuario asignara no todo en una fila, sino solo las células necesarias:

En esta ventana, seleccione solo celdas visibles solo (solo celdas visibles) y haga clic en Aceptar.

La selección obtenida ahora se puede copiar e insertar de manera segura. Como resultado, recibiremos una copia de las celdas visibles e insertaremos en lugar de una innecesaria 29 solo 5 líneas que necesita.

Si hay una sospecha de que tal operación tiene que hacer con frecuencia, tiene sentido agregar un botón a la barra de herramientas de Microsoft Excel para llamar rápidamente una función de este tipo. Esto se puede hacer a través del menú Herramientas\u003e Configuración (Herramientas\u003e Personalizar), luego vaya a la pestaña Comandos (Comandos), en la categoría Editar (Editar) Encuentre el botón Seleccionar celdas visibles y transfíralo a la barra de herramientas:

Transformación de filas en columnas y espalda.

Operación simple, pero si no sabe cómo hacerlo bien, puede pasar medio día al arrastrar las celdas individuales manualmente:

De hecho, todo es simple. En esa parte de las matemáticas más altas, que describe las matrices es el concepto de transposición: acciones que cambian las cadenas y columnas en la matriz en algunos lugares entre sí. En Microsoft Excel, se implementa en tres movimientos: copiar la tabla

Haga clic con el botón derecho en una celda vacía y elija un comando especial de inserción (Pegar especial)

En la ventana que se abre, coloque la bandera para transponer (transponer) y haga clic en Aceptar:

Añadir datos rápidos a la tabla

Imagine una situación simple: usted tiene un informe para el último mes con un diagrama visual. Tarea: agregue nuevos datos numéricos a la tabla para este mes. El camino clásico de él es abrir la ventana de origen de datos para el diagrama donde agrega una nueva serie de datos ingresando su nombre y resalte el rango con los datos deseados. Además, a menudo es más fácil decirlo que hacerlo, todo depende de la complejidad de la tabla.

Otra forma es simple, rápida y hermosa: seleccionar celdas con nuevos datos, copiarlos (CTRL + C) y Pegar (CTRL + V) directamente en la tabla. ¡Excel 2003, en contraste con versiones posteriores, incluso la posibilidad de arrastrar el rango dedicado de células con datos y tirarlo directamente a la tabla con el mouse!

Si desea controlar todos los matices y sutilezas, puede usarlo no ordinario, y un inserto especial seleccionando en la edición\u003e Pegar especial (Editar\u003e Pegar especial). En este caso, Microsoft Excel mostrará un cuadro de diálogo que le permite configurar dónde y cómo se agregarán los nuevos datos:

De manera similar, puede crear fácilmente un diagrama con datos de diferentes tablas de diferentes hojas. Para realizar la misma tarea, tomará mucho más tiempo y fuerza.

Relleno de celdas vacías

Después de descargar informes de algunos programas al formato de Excel o al crear tablas consolidadas, los usuarios a menudo reciben tablas con celdas vacías en algunas columnas. Estos saltos no nos permiten aplicarnos a las mesas, herramientas familiares y convenientes, como el autofiltro y la clasificación. Naturalmente, existe la necesidad de llenar los valores de vacío de las células más altas:

Por supuesto, con una pequeña cantidad de datos, se puede hacer fácilmente mediante copia simple, estirando manualmente cada celda de título en la columna y hacia abajo a las celdas vacías. ¿Y si hay varios cientos o miles de filas y varias docenas de ciudades de la mesa?

Hay una manera de resolver esta tarea rápidamente y hermosa con la ayuda de una fórmula:

Resalte todas las celdas en una columna con vacíos (I.E. RANGO A1: A12 en nuestro caso)

Para seleccionar solo celdas vacías, presione la tecla F5 y en la ventana de transición que se abre, el botón de asignación. Vea una ventana que le permita elegir, qué celdas queremos asignar:

Configure el interruptor a la posición vacía (en blanco) y haga clic en Aceptar. Ahora solo las celdas vacías deben permanecer en la selección:

Sin cambiar de asignación, es decir. No es un mouse táctil, introducimos la fórmula en la primera celda dedicada (A2). Haga clic en el teclado al cartel "Igual" y luego en la flecha hacia arriba. Recibimos una fórmula que se refiere a la celda anterior:

Para ingresar a la fórmula creada inmediatamente en todas las celdas vacías seleccionadas, presione la tecla ENTER, pero la combinación de CTRL + ingrese. La fórmula llenará todas las celdas vacías:

Ahora queda solo para reemplazar las fórmulas en los valores para solucionar los resultados. Resalte el rango A1: A12, cópielo e insértelos en las celdas usando un inserto especial.

Descarga

La recepción, que, sin exagerar, debe conocer a nadie que trabaje en Excel. Su uso es capaz de mejorar, casi cualquier mesa, independientemente de su propósito. En todos los entrenamientos, trato de mostrarlo a mis oyentes el primer día.

La idea es muy simple: en todos los casos, cuando debe ingresar datos de cualquier dial, en lugar de ingresar manualmente a una celda desde el teclado, seleccione el mouse deseado en la lista desplegable:

La elección de las mercancías de la lista de precios, el nombre del cliente de la base del cliente, el FIO del empleado del personal de la DISPAZA, etc. Opciones de aplicación para este conjunto de funciones.

Para crear una lista desplegable en la celda:

Seleccione las celdas en las que desea crear una lista desplegable.

Si tiene un Excel 2003 o más, luego seleccione Datos\u003e Validación en el menú Datos\u003e Validación. Si tiene Excel 2007/2010, vaya a la pestaña Datos y haga clic en Validación de datos.

En la ventana que se abre, seleccione la opción de lista (lista) de la lista desplegable.

En el campo FUENTE (Fuente), debe especificar los valores que deben aparecer en la lista. Aquí las opciones son posibles:

Ingrese en este campo Opciones de texto a través de un punto de coma

Si el rango de células con valores de origen está en la hoja actual, es suficiente para resaltarlo con el mouse.

Si está en otra hoja de este libro, tendrá que darle un nombre por adelantado (para resaltar las celdas, presione CTRL + F3, ingrese el nombre del rango sin espacios), y luego registre este nombre en el campo

Si no se muestran algunas células, cadenas o columnas en la hoja, puede copiar todas las celdas (o solo celdas visibles). De forma predeterminada, Excel copia no solo visible, sino también celdas ocultas o filtradas. Si desea copiar solo celdas visibles, siga los pasos que se describen a continuación. Por ejemplo, puede copiar solo datos de resumen de la hoja estructurada.

Siga los pasos a continuación.

Nota: Al copiar, los valores se insertan constantemente en las cadenas y columnas. Si el área de inserción contiene cadenas o columnas ocultas, es posible que deba mostrarlas para ver todos los datos copiados.

Al copiar e insertar células visibles en el rango de datos que contiene células ocultas o a las que se aplica el filtro, se puede observar que las células ocultas se insertan junto con visibles. Desafortunadamente, no puede cambiar este parámetro cuando copie e inserte el rango de celdas en Excel en Internet, ya que la inserción de solo células visibles no está disponible.

Sin embargo, si formatea los datos como una tabla y aplica el filtro, puede copiar y pegar solo celdas visibles.

Si no necesita formatear los datos como tabla y se instala una aplicación Classic Excel, puede abrir un libro en él para copiar y pegar celdas visibles. Para hacer esto, haga clic en Abierto en Excel Y siga los pasos descritos en el artículo de copia e inserte solo celdas visibles.

Información Adicional

Siempre puede hacer una pregunta de Excel Tech Community, solicite ayuda en la comunidad comunitaria de respuestas, así como ofrecer una nueva característica o mejora en el sitio web.

Formato condicional (5)
LISTAS Y RANGOS (5)
Macros (procedimientos VBA) (63)
Misceláneo (39)
Excel Bugs and Glitches (4)

Cómo insertar celdas copiadas solo en celdas visibles / filtradas

En general, el significado del artículo ya, creo, está claro desde el nombre. Simplemente amplíe un poco.

No es un secreto que Excel le permite destacar solo líneas visibles. (Por ejemplo, si algunos de ellos están ocultos o filtros).

Por lo tanto, si copia solo celdas visibles, se copian, ya que debe copiarse. Pero cuando intenta insertar copiado en el rango, el resultado filtrado (o que contiene líneas ocultas) es el resultado de la inserción no será exactamente la forma en que esperaba. Los datos se insertarán incluso en líneas ocultas.

Copie una sola gama de celdas e inserte solo para visible
De modo que los datos se inserten solo en celdas visibles, se puede aplicar una macro.

Opción Explicit Dim RCopyrange como rango "Este macro copiar datos Sub my_copy () si selection.count\u003e 1 luego configura rcopyrange \u003d selection.specialcells (xlvisible) otra cosa: configure rcopyrange \u003d activecell final si finaliza el subs secundario "Esta macro inserta los datos a partir de la celda seleccionada Sub My_Paste () Si RCopyrange no es nada, salga de SIP if rcopyrange.areas.count\u003e 1, luego MsgBox "¡El rango insertado no debe contener más de un área!", vbcritical, "Rango inválido": Salir de Sub Dim RCell como rango, LI tanto tiempo, LE tanto tiempo, LCOUNT, siempre largo, ICOL como entero, iColucción como aplicación Integer.Screenupdating \u003d Falso ICLICULT \u003d Aplication.calculation: aplicación.calculation \u003d -4135 para icol \u003d 1 a rcopirange .Columns.count li \u003d 0: lcount \u003d 0: le \u003d icol - 1 para cada rcell en rcopyrange.columns (icol) .cells do if activecell.offset (li, le) .entirecolumn.hidden \u003d false y _ activecell.offset (Li, LE). Centirerow.Hidden \u003d Falso entonces RCELL.COY ACTIVECEELL.OFFSET (LI, LE): LCOUNT \u003d LCOUNT + 1 END SI LI \u003d LI + 1 Loop Mientras LCOunt\u003e \u003d rcell.row - rcopyrange.cells (1 ) .Row Siguiente RCELL SIGUIENTE SIGUIENTE ICOL Application.ScreenUpdating \u003d True: Aplication.Calculation \u003d Subs del final de la ciclismo

Opción Explicit Dim RCopyrange como rango "Esta macro copia de datos Sub My_Copy () if sell selection.count\u003e 1 luego configura rcopyrange \u003d selection.specialcells (xlvisible) otra cosa: configure rcopyrange \u003d activecell final si final sub", esta macro inserte los datos que comienzan con el Células seleccionadas Sub My_Paste () Si RCopyrange no es nada, entonces salga de SUB si RCopyRange.Areas.Count\u003e 1, luego MsgBox "¡El rango insertado no debe contener más de un área!", VBcritical, "Rango no válido": Salir de Sub Dim RCell como rango , Li tanto tiempo, LE largo, LCOUNT, siempre largo, ICOL como entero, iColucción como aplicación entera. PERSONA DE PRESENTACIÓN \u003d FALSOCIAL ÓSCULA \u003d APLICACIÓN. CALCULACIÓN: APLICACIÓN.CALCULA \u003d -4135 PARA ICOL \u003d 1 A rcopyrange.columns.count li \u003d 0 : lcount \u003d 0: le \u003d icol - 1 para cada rcell en rcopyrange.columns (icol) .cells do if to activecell.offset (li, le). inticrecolumn.hidden \u003d falso y _ activecell.offset (li, le). Centirerow .hidden \u003d false entonces rcell.copy activecell.offset (li, le): lcount \u003d lcount + 1 End SI LI \u003d LI + 1 Loop Mientras LCOUNT\u003e \u003d rcell.row - rcopyrange.cells (1) .Row Siguiente RCELL Siguiente aplicación de la ICOL.Screenupdating \u003d True: Aplication.Calculation \u003d Subcuminar final

Para completar la imagen, los datos de Macros es mejor asignarse a las teclas de acceso rápido (en los códigos a continuación, esto se realiza automáticamente al abrir un libro con código). Para esto, los códigos a continuación se deben copiar al módulo. Este libro Esta librería) :

Opción explícita "Cancelamos la asignación de las teclas de acceso rápido antes de cerrar el libro Sub WorkBook_BeforeClose (Cancelar como Boolean) Application.onkey" ^ Q ": application.onkey" ^ w "final sub", asignamos las teclas en caliente al abrir un libro privado. WorkBook_OPEN () Application.onkey "^ Q", "my_copy": application.onkey "^ w", "my_paste" final subd

Ahora puede copiar el rango deseado presionando las teclas control + p. e insértelo en filtrado - control + w. .

Ejemplo de descarga

(46.5 KIB, 9,622 descargas)

Copia solo celdas visibles e inserte solo en visible
A solicitud de los visitantes al sitio decidió finalizar este procedimiento. Ahora es posible copiar cualquier rangos: con filas ocultas, columnas ocultas e insertar celdas copiadas también en cualquier rangos: con filas ocultas, columnas ocultas. Funciona completamente como la anterior: presionando las teclas control + p. Copia el rango deseado (Con líneas y columnas ocultas / filtradas o no oculta)e inserte la combinación de teclas control + w. . El inserto se realiza también en líneas y columnas ocultas / filtradas o sin ocultas.
Si las fórmulas están presentes en el rango de copia, entonces para evitar el desplazamiento de enlace, solo se pueden copiar los valores de las celdas, es decir, Al insertar valores, no se insertarán fórmulas, sino el resultado de su cálculo. O si necesita guardar los formatos de celda en los que se produce la inserción, solo se copiarán e insertarán los valores de la celda. Para hacer esto, reemplace la línea en el código (en el archivo a continuación):

Rcell.copy rresresell.offset (LR, LC)

rcell.copy rresresell.offset (LR, LC)

a tal:

Rrescell.offset (lr, lc) \u003d rcellue

rrescell.offset (lr, lc) \u003d rcellue

En el archivo a continuación, ambas líneas están presentes, solo necesita dejar el que sea más adecuado para sus tareas.

Ejemplo de descarga:

(54.5 KIB, 7,928 descargas)

Sólo ve:
[]

¿El artículo ayudó? ¡Comparte un enlace con amigos! Lecciones de video

("Barra inferior" :( "Textstyle": "estática", "TextAPositionStatic": "Bottom", "textautohide": true, "textApositionMarginstatic": 0, "TextSitionsitdynamic": "BottingLeft", "TextPositionMarginleft": 24 " TextPositionMarGinRight ": 24," TextPositionMarGItPOP ": 24," TextPositionMarginbottom ": 24," TexteFectift ":" Diapositiva "," TexteFeFitsing "," TexteOutcubic "," TexteFectdurity ": 600," textefffectsLidedirection ":" Izquierda "," textefffectslidence " : 30, "TexteFefectDelay": 500, "TexteFeftSexparate": Falso, "TexteFectEFECT1": "Diapositiva", "TexteFffectsLidirection1": "Derecho", "TexteFffectsLidistance1": 120, "texteffneceasing1": "FacioutCubic", "TexteFftDuration1": 600 , "TexteFectdelay1": 1000, "TexteFeft2": "Diapositiva", "TextefectsLidedirection2": "Derecho", "TexteFffectsLIDETETITANTETETETETETETE2": 120, "texteffneceasing2": "fifutcubic", "texteffectduration2": 600, "texteffectdelay2": 1500, " TextCSSS ":" Pantalla: Bloque; relleno: 12px; Texto-alineado: a la izquierda; "," Textbgcss ":" Pantalla: Bloque; Posición: Absolute; Absoluto; Top: 0px; Izquierda: 0px; Ancho: 100%; Altura: 100% ; Fondo-color: # 333333; Opacidad: 0.6; Filtro: A Lpha (opacidad \u003d 60); "," Titlecsss ":" Pantalla: Bloque; Posición: Relativo; Fuente: Bold 14px \\ "Lucida Sans Unicode \\", \\ "Lucida Grande \\", Sans-Serif, Arial; Color: #fff; "," DescripciónCsss ":" Pantalla: Bloque; Posición: Relativo; Fuente: 12px \\ "Lucida Sans Unicode \\", \\ "Lucida Grande \\", Sans-Serif, Arial; Color: #fff; margen-top: 8px; "," ButtonCSSSS ":" Pantalla: Bloque; Posición: Relativo; margen-top: 8px; "," textefectrepressive ": verdadero," texturefectrefectressize ": 640," titlecsssressive ":" talla de fuente: 12px; "," descriptioncsssresponsive ":" Pantalla: Ninguno! "," ButtonCSSRessive ": "", "AddgoooogleFonts": False, "GoogleFonts": "", "textleftrightpercentstatic": 40))

Selección de datos en Excel gastada usando un filtro o clasificación. Ahora necesitan ser impresos o transferidos a otro lugar. Copiando a Excel Está configurado de modo que las células ocultas se copien.
Considera dos formas a aK copia filas filtradas en Excel.
El primero.
Hay una característica maravillosa en Excel - función "Inserto especial" en Excel.
Así que tenemos una mesa.
Cómo instalar un filtro, mira en el artículo "Filtrar en Excel ".
Retiramos el filtro de la lista de todos Ivanov. Resultó así.
Seleccione la tabla, haga clic en el menú contextual "Copiar". Presione el mouse izquierdo en la celda A9, seleccione "Valores" en el menú contextual.
Haga clic en Aceptar". Voila. No solo el valor de las líneas visibles, sino también el formato de las células.
Hay un matiz - Insertar los datos filtrados no está en la cadena donde está el filtro. Por ejemplo, en nuestro ejemplo, no en la línea 1-7, pero por debajo o en otra hoja, etc. Si se inserta en la cadena donde vale el filtro, luego se insertan los datos filtrados y las líneas ocultas por el filtro. En general, resulta de papilla. La segunda forma.
La mesa es la misma. Seleccione una tabla con datos filtrados. En la pestaña Inicio, haga clic en el botón Editar encontrar y resaltar las funciones en Excel.. Luego, presione el botón "Ir". En el cuadro de diálogo que aparece, presione el botón "Seleccionar ...". En la ventana "Selección de grupo de grupo", ponemos una garrapata en la cadena "Solo celdas visibles". Haga clic en Aceptar". Ahora, en la misma tabla resaltada, el menú contextual se llama la mano derecha. Presione la función "Copiar". En un nuevo lugar (en nuestro ejemplo, esta es una celda A15), haga clic en "Pegar". Todo. Resultó así.
Cómo, no copiar, inmediatamente imprimir datos de filtro en Excel, Mira en el artículo "Bookmark Leaf Excel" Marking ""