Cómo seleccionar solo celdas visibles en Excel. Copie los datos filtrados a Excel. Copiar sin dañar el formato
Pávlov Nikolai
En este artículo me gustaría presentarles las técnicas más efectivas para trabajar en Microsoft Excel, recopilado por mí durante los últimos 10 años de trabajo en proyectos y realización de capacitaciones sobre este maravilloso programa. Aquí no hay una descripción de tecnologías súper complejas, pero hay técnicas para todos los días: simples y efectivas, descritas sin "agua", solo "residuos secos". La mayoría de estos ejemplos no le llevarán más de uno o dos minutos dominarlos, pero le ayudarán a ahorrar mucho más.
Salta rápidamente a la hoja deseada
¿Trabaja usted con libros de excel que consta de una gran cantidad de hojas? Si hay más de una docena, cada transición a la siguiente hoja requerida se convierte en un pequeño problema en sí mismo. Una solución simple y elegante a este problema es hacer clic en la esquina inferior izquierda de la ventana en los botones para desplazarse por las pestañas de las hojas, no con el botón izquierdo, sino con el derecho; aparecerá una tabla de contenido del libro con Lista llena todas las hojas y podrás ir a la hoja deseada en un solo movimiento:
Esto es mucho más rápido que desplazarse por las pestañas de las hojas usando los mismos botones en busca de lo que necesita.
Copiar sin dañar el formato
¿Cuántos cientos (¿miles?) de veces he visto esta imagen, de pie detrás de mis alumnos durante los entrenamientos: el usuario ingresa una fórmula en la primera celda y luego la "estira" a lo largo de toda la columna, violando el formato de las filas siguientes. ya que este método copia no solo la fórmula, sino también el formato de la celda. En consecuencia, deberá corregir manualmente el daño. Un segundo para copiar y luego 30 para reparar un diseño dañado por la copia.
A partir de Excel 2002, existe una solución a este problema que es simple y elegante. Inmediatamente después de copiar (arrastrar) la fórmula a toda la columna, debe usar una etiqueta inteligente, un pequeño ícono que aparece temporalmente en la esquina inferior derecha del rango. Al hacer clic en él aparecerá una lista. opciones posibles copiando, donde puede seleccionar Rellenar sin formatear. En este caso, las fórmulas se copian, pero el formato no:
Copiar solo celdas visibles
Si ha estado trabajando en Microsoft Excel durante más de una semana, probablemente ya se haya encontrado con un problema similar: en algunos casos, al copiar y pegar celdas, se insertan más celdas de las que, a primera vista, se copiaron. Esto puede ocurrir si el rango copiado incluía filas/columnas, agrupaciones, subtotales o filtrado ocultos. Tomemos como ejemplo uno de estos casos:
En esta tabla, se calculan los subtotales y las filas se agrupan por ciudad; esto es fácil de entender por los botones más y menos a la izquierda de la tabla y por los saltos en la numeración de las filas visibles. Si seleccionamos, copiamos y pegamos datos de esta tabla de la forma habitual, acabaremos con 24 filas extra. ¡Solo queremos copiar y pegar los resultados!
Puede resolver el problema seleccionando minuciosamente cada fila de los totales mientras mantiene presionada la tecla CTRL, como lo haría para seleccionar rangos no adyacentes. Pero ¿qué pasa si no son tres o cinco líneas de este tipo, sino varios cientos o miles? Existe otra forma más rápida y cómoda:
Seleccione el rango a copiar (en nuestro ejemplo es A1:C29)
Presione la tecla F5 en su teclado y luego el botón Seleccionar en la ventana que se abre.
Aparecerá una ventana que permitirá al usuario seleccionar no todo lo que hay en una fila, sino sólo las celdas necesarias:
En esta ventana, seleccione la opción Sólo celdas visibles y haga clic en Aceptar.
La selección resultante ahora se puede copiar y pegar de forma segura. Como resultado, obtendremos una copia exactamente células visibles e inserte en lugar de los 29 innecesarios solo las 5 líneas que necesitamos.
Si sospecha que tendrá que realizar dicha operación con frecuencia, entonces tiene sentido agregar un botón a la barra de herramientas de Microsoft Excel para llamar rápidamente a dicha función. Esto se puede hacer a través del menú Herramientas>Personalizar, luego vaya a la pestaña Comandos, en la categoría Editar, busque el botón Seleccionar celdas visibles y arrástrelo a la barra de herramientas con el mouse:
Convertir filas en columnas y viceversa
Una operación sencilla, pero si no sabes hacerla correctamente, puedes dedicar medio día a arrastrar celdas individuales manualmente:
En realidad es simple. En esa parte de las matemáticas superiores que describe matrices, existe el concepto de transposición, una acción que intercambia filas y columnas en una matriz entre sí. En Microsoft Excel, esto se implementa en tres pasos: Copiar la tabla
Haga clic derecho en una celda vacía y seleccione Pegado especial.
En la ventana que se abre, marque la bandera Transponer y haga clic en Aceptar:
Agregar datos rápidamente a un gráfico
Imaginemos una situación sencilla: tienes un informe del último mes con un diagrama visual. La tarea es agregar nuevos datos numéricos al gráfico de este mes. La forma clásica de resolver esto es abrir la ventana de fuente de datos del gráfico, donde agrega una nueva serie de datos ingresando su nombre y resaltando el rango con los datos deseados. Además, a menudo es más fácil decirlo que hacerlo: todo depende de la complejidad del diagrama.
Otra forma, simple, rápida y hermosa, es seleccionar las celdas con datos nuevos, copiarlas (CTRL+C) y pegarlas (CTRL+V) directamente en el gráfico. Excel 2003, a diferencia de versiones posteriores, incluso admite la capacidad de arrastrar un rango seleccionado de celdas de datos y soltarlo directamente en el gráfico usando el mouse.
Si desea controlar todos los matices y sutilezas, puede utilizar no un pegado normal, sino especial, seleccionando Editar> Pegado especial en el menú. En este caso, Microsoft Excel mostrará un cuadro de diálogo que le permitirá configurar dónde y cómo exactamente se agregarán los nuevos datos:
De manera similar, puede crear fácilmente un gráfico utilizando datos de diferentes tablas de diferentes hojas. Realizar la misma tarea de la forma clásica requerirá mucho más tiempo y esfuerzo.
Llenando celdas vacías
Después de descargar informes de algunos programas en formato Excel o al crear tablas dinámicas, los usuarios suelen recibir tablas con celdas vacías en algunas columnas. Estas omisiones no le permiten aplicar herramientas familiares y convenientes como el filtro automático y la clasificación de tablas. Naturalmente, es necesario llenar los vacíos con valores de celdas de nivel superior:
Por supuesto, con una pequeña cantidad de datos, esto se puede hacer fácilmente mediante una simple copia: arrastrando manualmente cada celda del encabezado de la columna A hacia las celdas vacías. ¿Qué pasa si la tabla tiene varios cientos o miles de filas y varias docenas de ciudades?
Hay una manera de resolver este problema de forma rápida y hermosa usando una fórmula:
Seleccione todas las celdas en una columna con espacios en blanco (es decir, rango A1:A12 en nuestro caso)
Para mantener solo celdas vacías en la selección, presione la tecla F5 y en la ventana de navegación que se abre, presione el botón Seleccionar. Verás una ventana que te permite seleccionar qué celdas queremos seleccionar:
Establezca el interruptor en En blanco y haga clic en Aceptar. Ahora solo deberían quedar celdas vacías en la selección:
Sin cambiar la selección, es decir. Sin tocar el mouse, ingrese la fórmula en la primera celda seleccionada (A2). Presione el signo igual en su teclado y luego la flecha hacia arriba. Obtenemos una fórmula que hace referencia a la celda anterior:
Para ingresar la fórmula creada en todas las celdas vacías seleccionadas a la vez, no presione la tecla ENTER, sino la combinación CTRL + ENTER. La fórmula llenará todas las celdas vacías:
Ahora solo queda sustituir las fórmulas por valores para registrar los resultados. Seleccione el rango A1:A12, cópielo y pegue sus valores en las celdas usando Pegado especial.
Lista desplegable en una celda
Una técnica que, sin exagerar, todo aquel que trabaja en Excel debería conocer. Su uso puede mejorar casi cualquier mesa, independientemente de su finalidad. En todos los entrenamientos, trato de mostrárselo a mis alumnos el primer día.
La idea es muy simple: en todos los casos en los que deba ingresar datos de cualquier conjunto, en lugar de ingresar manualmente una celda desde el teclado, seleccione el valor deseado con el mouse de la lista desplegable:
Seleccionar un producto de la lista de precios, el nombre del cliente de la base de datos de clientes, el nombre completo del empleado de la plantilla, etc. Hay muchas opciones para utilizar esta función.
Para crear una lista desplegable en una celda:
Seleccione las celdas en las que desea crear una lista desplegable.
Si tiene Excel 2003 o anterior, seleccione Datos>Validación en el menú. Si tiene Excel 2007/2010, vaya a la pestaña Datos y haga clic en el botón Validación de datos.
En la ventana que se abre, seleccione la opción Lista de la lista desplegable.
En el campo Fuente, debes especificar los valores que deben estar en la lista. Aquí están las posibles opciones:
Ingrese opciones de texto en este campo separadas por punto y coma
Si el rango de celdas con los valores originales está en la hoja actual, solo necesita seleccionarlo con el mouse.
Si está ubicado en otra hoja de este libro, deberá darle un nombre por adelantado (seleccione celdas, presione CTRL+F3, ingrese el nombre del rango sin espacios) y luego escriba este nombre en el campo
Si algunas celdas, filas o columnas de una hoja de cálculo no son visibles, puede copiar todas las celdas (o solo las visibles). De forma predeterminada, Excel copia no sólo las celdas visibles, sino también las ocultas o filtradas. Si solo desea copiar celdas visibles, siga los pasos a continuación. Por ejemplo, puede copiar sólo datos resumidos de una hoja de trabajo estructurada.
Siga los pasos a continuación.
Nota: Al copiar, los valores se insertan secuencialmente en filas y columnas. Si el área para pegar contiene filas o columnas ocultas, es posible que deba mostrarlas para ver todos los datos que copió.
Cuando copia y pega celdas visibles en un rango de datos que contiene celdas ocultas o que tiene un filtro aplicado, puede notar que las celdas ocultas se pegan junto con las celdas visibles. Desafortunadamente, no puede cambiar esta configuración cuando copia y pega un rango de celdas en Excel para la web porque pegar solo celdas visibles no está disponible.
Sin embargo, si formatea sus datos como una tabla y aplica un filtro, puede copiar y pegar solo las celdas visibles.
Si no necesita formatear los datos como una tabla y tener instalado el clásico aplicacion excel, puede abrir un libro en él para copiar y pegar celdas visibles. Para hacer esto, haga clic en el botón Abrir en Excel y siga los pasos en Copiar y pegar solo celdas visibles.
información adicional
Siempre puede hacer una pregunta a un especialista de Excel Tech Community, pedir ayuda en la comunidad de Respuestas y también sugerir nueva caracteristica o mejora en el sitio web
Pegar sólo en las líneas visibles enSobresalir números, fórmulas y texto se pueden hacer de varias maneras. Cuando necesite insertar números, fórmulas o texto en no todas las filas de la tabla, puede utilizar un filtro. Cómo instalar un filtro y cómo filtrar en Excel, consulte el artículo “Filtro en Excel”. Pero para insertar datos sólo en celdas visibles, necesita sus propios métodos, especialmente si hay muchas filas.
La primera manera es común .
Tomemos una mesa como esta. La tabla será la misma para todos los ejemplos.
Usemos un filtro para eliminar todos los dígitos 2 de la tabla. En las celdas visibles restantes ponemos el número 600. En la celda B2 ponemos el número 600, luego lo copiamos hacia abajo en la columna (tira de la esquina inferior derecha de la celda B2). Los valores se copiaron sólo en las celdas visibles. También puedes insertar fórmulas de la misma forma. Escribimos la siguiente fórmula en la celda C2. =A2*10
Resultó así. 
Cancelemos el filtro. El resultado es una tabla como esta.
La fórmula y los números se insertaron sólo en las filas filtradas.
Segunda vía.
También filtraremos los datos. En la primera celda escribimos un número, fórmula, texto, etc. Ahora, si hay miles de filas, seleccione las celdas de esta manera: presione las teclas "Ctrl" + "Shift" + el botón de flecha hacia abajo (o el botón hacia arriba, dependiendo de dónde queramos seleccionar las celdas, abajo o arriba). la celda en la que se escribió el número).
Ahora, o presione la combinación de teclas “Ctrl” + G, o la tecla F5. Aparecerá el cuadro de diálogo Transición. Haga clic en el botón "Seleccionar...". Y, en el nuevo cuadro de diálogo "Seleccionar un grupo de celdas", marque la casilla junto a las palabras "Solo celdas visibles".
Haga clic en Aceptar". Luego insértelo como de costumbre.
Otra forma de abrir el cuadro de diálogo Seleccionar grupo de celdas.En la pestaña "Inicio", en la sección "Edición", haga clic en el botón "Buscar y seleccionar". En la lista que aparece, haga clic en la función “Seleccionar un grupo de celdas”.
A llenar las celdas visibles en las seleccionadas columnas de excel , presione la combinación de teclas “Ctrl” + D. Y todas las columnas seleccionadas se completarán con datos o una fórmula, como en la primera celda. En nuestro ejemplo, escribimos el número 800 en la celda D2, columna D.
Tercera vía.
En una nueva columna (en nuestro ejemplo, columna E), seleccione las celdas. Presione la tecla F5. Aparecerá el cuadro de diálogo Transición. Haga clic en el botón "Seleccionar...". Y, en el nuevo cuadro de diálogo "Seleccionar un grupo de celdas", marque la casilla junto a las palabras "Solo celdas visibles". Haga clic en Aceptar". Ahora, sin cancelar la selección, en la primera celda de la columna (la nuestra es E2), ingresa una fórmula, número, etc. Presione la combinación de teclas “Ctrl” + “Entrar”.
Listas y rangos (5)
Macros (procedimientos VBA) (63)
Varios (39)
Errores y fallos de Excel (4)
Cómo pegar celdas copiadas solo en celdas visibles/filtradas
En general, creo que el significado del artículo ya se desprende del título. Lo ampliaré un poco.
No es ningún secreto que Excel te permite seleccionar solo filas visibles (por ejemplo, si algunos de ellos están ocultos o se aplica algún filtro).
Por lo tanto, si copia sólo las celdas visibles de esta manera, se copiarán como se esperaba. Pero cuando intentas pegar algo copiado en un rango filtrado (o que contiene filas ocultas), el resultado del pegado no será exactamente el que esperabas. Los datos se insertarán incluso en filas ocultas.
Copie un solo rango de celdas y péguelo solo en las visibles
Para insertar datos solo en celdas visibles, puede utilizar la siguiente macro:
| Opción explícita Dim rCopyRange como rango "Con esta macro copiamos los datos Sub My_Copy() Si Selection.Count > 1 Luego establezca rCopyRange = Selection.SpecialCells(xlVisible) De lo contrario: establezca rCopyRange = ActiveCell End If End Sub "Con esta macro insertamos datos comenzando desde la celda seleccionada Sub My_Paste() Si rCopyRange no es nada, entonces salga de Sub si rCopyRange.Areas.Count> 1, luego MsgBox "¡El rango pegado no debe contener más de una región!",vbCrítico, "Rango no válido": Salga de Sub Dim rCell como rango, li como largo, le como largo, lCount como largo, iCol como entero, iCalculation como entero Application.ScreenUpdating = False iCalculation = Application.Calculation: Application.Calculation = -4135 For iCol = 1 To rCopyRange .Columns.Count li = 0: lCount = 0: le = iCol - 1 para cada rCell en rCopyRange.Columns(iCol).Cells Do If ActiveCell.Offset(li, le).EntireColumn.Hidden = False y _ ActiveCell.Offset (li, le).EntireRow.Hidden = False Entonces rCell.Copy ActiveCell.Offset(li, le): lCount = lCount + 1 Fin si li = li + 1 Bucle mientras lCount >= rCell.Row - rCopyRange.Cells(1 ).Fila Siguiente rCell Siguiente iCol Aplicación.ScreenUpdating = Verdadero: Aplicación.Calculación = iCalculation End Sub |
Opción Explícita Dim rCopyRange As Range "Utilice esta macro para copiar los datos Sub My_Copy() Si Selection.Count > 1 Entonces establezca rCopyRange = Selection.SpecialCells(xlVisible) De lo contrario: Establezca rCopyRange = ActiveCell End If End Sub "Utilice esta macro para pegar los datos a partir de las celdas seleccionadas Sub My_Paste() Si rCopyRange no es nada, entonces salga Sub Si rCopyRange.Areas.Count > 1 Entonces MsgBox "¡El rango pegado no debe contener más de un área!", vbCritical, "Rango no válido": Salir Sub Dim rCell como rango, li como largo, le como largo, lCount como largo, iCol como entero, iCalculation como entero Application.ScreenUpdating = False iCalculation = Application.Calculation: Application.Calculation = -4135 For iCol = 1 To rCopyRange.Columns .Count li = 0: lCount = 0: le = iCol - 1 para cada rCell en rCopyRange.Columns(iCol).Cells Do If ActiveCell.Offset(li, le).EntireColumn.Hidden = False y _ ActiveCell.Offset(li , le).EntireRow.Hidden = False Entonces rCell.Copy ActiveCell.Offset(li, le): lCount = lCount + 1 Fin si li = li + 1 Bucle mientras lCount >= rCell.Row - rCopyRange.Cells(1). Fila Siguiente rCell Siguiente iCol Application.ScreenUpdating = Verdadero: Application.Calculation = iCalculation End Sub
Para completar la imagen, es mejor asignar estas macros a teclas de acceso rápido (en los códigos siguientes, esto se hace automáticamente al abrir un libro con el código). Para hacer esto, solo necesita copiar los códigos a continuación en el módulo Este libro (Este libro de trabajo) :
Opción explícita "Cancelar la asignación de teclas de acceso rápido antes de cerrar el libro Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.OnKey "^q": Application.OnKey "^w" End Sub "Asignar teclas de acceso rápido al abrir el libro Private Sub Workbook_Open() Aplicación .OnKey "^q", "My_Copy": Aplicación.OnKey "^w", "My_Paste" End Sub
Ahora puedes copiar el rango deseado presionando las teclas Control + q , e insértelo en el filtrado - Control + w .
Descargar ejemplo
(46,5 KiB, 9.622 descargas)
Copie solo las celdas visibles y pegue solo en las visibles
A petición de los visitantes del sitio, decidí mejorar este procedimiento. Ahora es posible copiar cualquier rango: con filas ocultas, columnas ocultas y pegar celdas copiadas también en cualquier rango: con filas ocultas, columnas ocultas. Funciona exactamente igual que el anterior: pulsando teclas Control
+
q
copiar el rango deseado (con filas y columnas ocultas/filtradas o no ocultas) y pegar con un atajo de teclado Control
+
w
. La inserción también se realiza en filas y columnas ocultas/filtradas o sin ocultas.
Si el rango copiado contiene fórmulas, para evitar el desplazamiento de referencia, puede copiar solo los valores de la celda, es decir, Al insertar valores, no se insertarán fórmulas, sino el resultado de su cálculo. O si es necesario conservar los formatos de las celdas en las que se produce la inserción, solo se copiarán y pegarán los valores de las celdas. Para hacer esto, necesita reemplazar la línea en el código (en el archivo a continuación):
| rCell.Copiar rResCell.Offset(lr, lc) |
rCell.Copiar rResCell.Offset(lr, lc)
a esto:
| rResCell.Offset(lr, lc) = rCell.Value |
rResCell.Offset(lr, lc) = rCell.Value
Ambas líneas están presentes en el archivo a continuación; solo necesita dejar la que sea más adecuada para sus tareas.
Descargar ejemplo:
(54,5 KiB, 7.928 descargas)
Ver también:
[]
("Barra inferior":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss":"display:bloque; padding:12px; text-align:left;","textbgcss":"display:block; posición:absoluta; arriba:0px; izquierda:0px; ancho:100%; alto:100% ; color de fondo:#333333; opacidad:0.6; filtro:alfa(opacidad=60);","titlecss":"display:block; posición: relativa; fuente: negrita 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; posición: relativa; fuente:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:bloque; posición: relativa; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))
La selección de datos en Excel se realizó mediante un filtro o clasificación. Ahora es necesario imprimirlos o trasladarlos a otra ubicación. Copiar a Excel configurado para que las celdas ocultas también se copien.
Consideremos dos formas, A
Cómo copiar filas filtradas en Excel.
Primera manera.
Hay una gran función en Excel: Función Pegar especial en Excel.
Entonces tenemos una mesa.
Cómo instalar un filtro, consulte el artículo " Filtrar en Excel ".
Usamos un filtro para eliminar todos los Ivanov de la lista. Resultó así.
Seleccione la tabla y haga clic en "Copiar" en el menú contextual. Haga clic izquierdo en la celda A9 y seleccione "valores" en el menú contextual.
Haga clic en Aceptar". Listo. No sólo se copió el valor de las filas visibles, sino también el formato de las celdas.
Hay un matiz- insertar datos filtrados no en las filas donde se encuentra el filtro. Por ejemplo, en nuestro ejemplo, no en las líneas 1 a 7, sino debajo o en otra hoja, etc. Si insertamos en las filas donde se encuentra el filtro, los datos filtrados también se insertarán en las filas ocultas por el filtro. En general, resultará un desastre. 
Segunda vía.
La mesa es la misma. Seleccione la tabla con datos filtrados. En la pestaña “Inicio”, haga clic en la sección “Edición” Buscar y resaltar funciones en Excel. Luego, haga clic en el botón "Ir". En el cuadro de diálogo que aparece, haga clic en el botón "Seleccionar...". En la ventana "Seleccionar un grupo de celdas", marque la casilla junto a "solo celdas visibles". Haga clic en Aceptar". Ahora en la misma tabla seleccionada con el botón derecho del mouse llamamos Menú de contexto. Haga clic en la función "Copiar". En una nueva ubicación (en nuestro ejemplo, esta es la celda A15), haga clic en "Insertar". Todo. Resultó así.
Cómo, sin copiar, inmediatamente. imprimir datos del filtro en excel, consulte el artículo "Marcar hoja de Excel"Diseño de página""
