¿Cuáles son algunas de las funciones / trucos de Excel menos conocidos pero útiles?

ELEGIR funciones: increíblemente fácil e intuitivo de usar, pero impresionante para alguien que no está familiarizado con Excel (por ejemplo, un ejecutivo) cuando lo ven alternar entre escenarios con un solo golpe de tecla.

Celdas autorreferenciadas: no es exactamente una función per se, solo una forma creativa de usar una instrucción IF. Útil si, por ejemplo, desea crear una salida que compare todos los escenarios. Sin celdas de autorreferencia, si tiene una tabla que muestra los resultados de cada escenario, la salida cambiará cada vez que cambie de escenario. Sin embargo, si los usa, las celdas de referencia automática retendrán el valor incluso si cambia a otro escenario.
Si Scenario = 1, extraiga el valor en el caso Down (que designamos como Scenario 1), de lo contrario, simplemente muestre el valor que está mostrando actualmente. Del mismo modo, para la salida del Escenario 2, sería “Si el Escenario = 2, extraiga el Caso base … etc., etc., etc.”

Función INDEX-MATCH : muy útil para extraer datos (especialmente datos que están fuera de servicio) de una ubicación a otra.
Vea aquí: la respuesta de Peter Recson a ¿Cómo puedo extraer datos de una hoja de Excel a otra automáticamente? Quiero hacer una comparación de productos que he vendido un trimestre versus otro. ¿Hay alguna forma de obtener la cantidad de ventas de la otra hoja y agregarla a la fila para comparar?

Ctrl + Shift + A ingresa marcadores de posición de fórmula

Cuando presiona Ctrl + Shift + A mientras ingresa una fórmula, Excel ingresará argumentos de marcadores de posición y le facilitará terminar su fórmula. Use este truco siempre que construya fórmulas complejas y necesite ayuda para dar sentido a todas las combinaciones.

Seleccione celdas en blanco con Ir a especial

Con ir a especial, puede seleccionar todas las celdas en blanco en una hoja (o dentro de una selección).

Herramienta de “depuración” de fórmula o F9

Este es uno de mis consejos favoritos; Cuando se rompe una fórmula compleja, nada es más frustrante que tener que depurar. Podrías estar allí un rato:

Pero aquí hay una herramienta que puede ayudar:

Esta herramienta le muestra, paso a paso, cómo Excel está calculando su fórmula detrás de escena. ¡Es como mirar tu fórmula en cámara lenta!

Para activarlo, vaya a Fórmulas> Evaluar fórmulas

Use tablas de Excel para rellenar automáticamente fórmulas

Convertir sus datos en una tabla de Excel puede ahorrarle mucho tiempo al ingresar fórmulas. Si agrega una fórmula a una celda adyacente, Excel completará automáticamente esa fórmula en todas las filas de la tabla. Simplemente resalte sus datos, presione Ctrl + T en su teclado y deje que comience la magia.

Leer más aquí

Hay muchas respuestas geniales aquí. Permítanme agregar algunos … e incluiré algunos enlaces a videos gratuitos que muestran cómo usarlos.

Aplicar el pintor de formato a más de un área.

Usar el pintor de formatos es una excelente manera de aplicar un formato utilizado en una parte de una hoja de cálculo a otra parte de una hoja de cálculo. Pero a veces necesitará aplicar un formato a más de una parte de su hoja de cálculo. ¿No sería genial si el pintor de formatos se mantuviera activo incluso después de haberlo usado? Bueno, si puede.

Repita una acción anterior rápidamente con la tecla F4

Usar la tecla F4 para repetir una acción previa mejorará tu flujo de trabajo y te hará ver como un campeón de Excel. ¡Solo mire a sabiendas en respuesta al asombro que provocará en otros cuando use esta técnica simple!

La forma más rápida de hacer que su hoja de cálculo se vea mejor es desactivar las líneas de cuadrícula

Desactivar las líneas de cuadrícula es la forma más rápida de hacer que una hoja de cálculo se vea más limpia y profesional. La buena noticia es que se puede hacer con solo dos clics de su mouse.

La forma más rápida de copiar filas de fórmula hacia abajo es hacer doble clic en la esquina inferior derecha de la celda activa

Por lo tanto, tiene una columna larga llena de valores y ha creado cuidadosamente una excelente fórmula adyacente a la primera fila de esa columna. ¿Cuál es la forma más rápida de copiar eso hasta el último valor en esa celda? Haga doble clic en la esquina inferior derecha de la celda activa.

Navegue fácilmente por hojas de cálculo grandes con métodos abreviados de teclado

¿Usa flechas para desplazarse por su hoja de cálculo? ¿Qué hay de página arriba y página abajo? Tedioso mucho? Usar métodos abreviados de teclado es una mejor manera de trabajar.

¡Feliz aprendizaje de Excel!

Veo muchas respuestas excelentes, pero me gustaría agregar algunas que uso con mucha frecuencia:

– f4: ya señaló que puede cambiar en relación con referencias absolutas, pero otro uso interesante de f4 es que repite el último comando utilizado … (por ejemplo, para insertar una fila, presione. F4 y se agrega otra fila)

– Encuentro el uso de match & vlookup / hlookup / loolup ampliamente juntos … porque match puede hacer que la columna de retorno sea un valor variable.

– indirectos, los índices son funciones muy potentes y se pueden usar en varios escenarios con matrices

– Linest usado con índice puede devolver la pendiente (y = mx + c) o interceptar una línea de regresión para una matriz … El resultado obtenido por esto es ligeramente diferente cuando uno dibuja un gráfico y dibuja una línea de regresión … (en la práctica encontré el valor del gráfico para ser más preciso, pero no tengo suficiente evidencia). Esto ayuda a conectar los valores de pendiente / intersección directamente a través de fórmulas en lugar de dibujar un gráfico para averiguar el valor.

– solucionador agregar

Otra herramienta poderosa que se puede agregar para regresiones, pruebas estadísticas y problemas de programación lineal.

– formato condicional
Se puede usar para crear algunas cosas realmente inspiradoras.

– vba (visual básico para aplicaciones)
Aquí se pueden crear funciones personalizadas, pero si la función se va a utilizar en el sistema de otra persona (que generalmente es), manténgase alejado de ella.

Palabras de despedida:
Un usuario promedio no usa más del 7% de las opciones de Excel. Hay muchas maneras de reducir el trabajo pesado y también aumentar la calidad del trabajo.
Hay varios foros en línea que pueden ayudar … También donde uno puede hacer preguntas y obtener algunas respuestas estupendas …
A menudo visito
Mrexcel.com – Bill Jelen el “Sr. Excel” también ha escrito varios libros que son muy buenos.

Peltiertech.com

Espero que ayude.

Siguiendo el ejemplo de la respuesta en el enlace de Saito y el hecho de que la mayoría de las necesidades de Excel son específicas de la naturaleza de su trabajo, me atendré a algunos de los atajos de teclado básicos. Reducen en gran medida el tiempo de procesamiento de cualquier hoja de cálculo y eso le da más tiempo para pensar en las mejores cosas (como lógica o VBA, tal vez)

Seleccionar columna: Ctrl + Espacio
Seleccionar fila: Mayús + espacio
Añadir columna / fila: Ctlr + ‘+’
Eliminar columna / fila: Ctlr + ‘-‘
Agregar hoja de trabajo: Shift + F11
Eliminar hoja de trabajo: Alt + E + L
Desplazarse por las hojas de trabajo: Ctrl + Tab
Poner filtros: Alt + D + F + F
Crear pivote: Seleccionar datos + Alt + D + P + P + Enter + Enter
Navegue a los extremos de datos: Ctrl + Teclas de dirección
Pegar (como valores / formatos / etc.): Alt + E + S
Maximizar el libro de trabajo / Bloc de notas: Alt + Espacio + X (me siento muy feliz cuando veo una ventana ‘no maximizada’ porque puedo hacer esta increíble combinación)

Y, por supuesto, herramientas como vlookup son increíbles y si las usas lo suficiente, eventualmente se convertirá en una segunda naturaleza para ti.

Hice esa pregunta exacta en una entrevista reciente con Excel Gurus.

Primera entrevista con Mynda Treacy: propietaria de un sitio web de capacitación en Excel myonlinetraininghub, com.

Puedes encontrar la entrevista en VIDEO aquí. Avance rápido al marcador 4:50 para la respuesta a esta pregunta

Mynda Treacy, Excel Trainer y Blogger: ayuda de los expertos de Excel

Mynda mencionó dos de su experiencia como entrenador

ÍNDICE y SUMPRODUCTO

También le pregunté al MVP de Excel Mourad Louha

Puedes encontrar su entrevista de texto aquí. desplácese aproximadamente 1/3 hacia abajo en la página donde se hicieron las preguntas.

Mourad Louha, MVP de Excel – Ayuda de los expertos de Excel

La respuesta de Mourad fue …

ÍNDICE, SUMPRODUCTO y ELEGIR
También proporcionó ejemplos de cómo usarlos.

Haré preguntas similares a otros Excel Gurus que estoy entrevistando en las próximas semanas.

Rick Grantham
Ayuda de Excel – Recursos y tutoriales

Las funciones de matriz son muy potentes y se usan muy raramente. Puede ver la curva de aprendizaje para “Curva de aprendizaje de Excel”

Por ejemplo, si desea comparar dos rangos y contar los que coincidan, puede hacerlo en una sola celda con
{= SUMA (- (A2: A21 = B2: B21))}
No agregue llaves rizadas manualmente. Simplemente escriba la fórmula restante y presione Ctrl + Shift + Enter
Al ingresar por encima de la fórmula dada a continuación, se obtienen los resultados dados

Para obtener más información sobre Excel, únase al grupo Linkedin “Excel for Engineers”.

La “ventana de vigilancia”

Esta función le permite ver el cambio en tiempo real en el resultado / resultados de salida que está / está vinculado a otras hojas de trabajo y tal vez a otros libros de trabajo.

1. Seleccione la celda / celdas que es el resultado de sus cálculos / ecuaciones

2. Luego vaya a “Fórmulas”> “Ventana de observación”> agregue la celda / celdas

3. Ahora puede seguir cambiando las ecuaciones principales, los valores y ver el cambio en el resultado de salida en tiempo real sin intercambiar hojas o libros de trabajo.

Usar el teclado es la mejor manera de acelerar las cosas en Excel. Y hay una manera de descubrir cómo realizar cualquier tarea en un teclado, es decir, sin la ayuda de un mouse. Practica eso y verás la diferencia por ti mismo.

Tomaré un ejemplo de una operación simple: aplicar filtro

Paso 1: búscalo en la barra de menú
Todo lo que desea hacer en Excel, se puede hacer desde las opciones de la barra de menú. Sólo tienes que encontrarlo.
Los filtros, por ejemplo, están disponibles en la sección ‘Datos’ de la barra de menú.

Paso 2: ¡Presiona Alt!
Al presionar la tecla ‘Alt’ se muestra la ‘entrada de teclado’ para cualquier operación en particular.
Por ejemplo, presione ‘H’ para Inicio, ‘N’ para insertar, ‘A’ para Datos, etc.

Una vez que da la primera ‘entrada de teclado’ (‘A’ para aplicar filtros), muestra la siguiente lista de entradas entre las que puede elegir.
Por ejemplo, ‘S’ para ordenar, ‘M’ para eliminar duplicados, ‘T’ para aplicar filtros.

Entonces, si hacemos estos pasos en rápida sucesión, aplicar filtros es lo mismo que “ALT -> A -> T”

Estos son algunos otros ejemplos:
ALT-> A-> M: Eliminar duplicados
ALT-> H-> B-> A: bordear todas las celdas seleccionadas
ALT-> H-> O-> I: ancho de columna de ajuste automático
ALT-> H-> M-> M: combinar celdas
ALT-> N-> N-> Enter: Insertar un gráfico lineal simple
ALT-> N-> V-> T: Ingrese la tabla dinámica

¡Puedes crear tus propios atajos usando este método! ¿No es genial?

Esto es para usuarios avanzados:

¿No se molesta cuando intenta escribir una fórmula en un cuadro de nombre / pantalla de validación de datos / cuadro de referencia de gráfico y cuando presiona las flechas del teclado, selecciona celdas de hoja en lugar de moverse por la fórmula?

Quiero decir esto:

Ok, la solución es simple: presione F2

“Ohh, conocía F2 pero ¿pudimos usar esto aquí también? ¡Maldita sea!”

** F2 cambia entre la fórmula actual y la referencia de hoja. Esto también es válido para el cuadro de fórmula normal . Si presiona nuevamente, el estado cambia nuevamente.

Una nueva característica agregada a Excel incluida en Office 2013 es Flash Fill. Manipula los datos ingresados ​​en una celda y su entrada correspondiente en otra columna para rellenar automáticamente las celdas debajo de ella automáticamente
por ejemplo, si tenemos una columna que contiene nombres en forma de , si ingreso un nombre para una de las filas y uso relleno flash con Ctrl + E, entonces se rellenarán automáticamente las celdas debajo del texto ingresado con todo el nombre del puño. Este y otros ejemplos se muestran en este video de YouTube:

También visite: http://research.microsoft.com/en … para echar un vistazo a la investigación detrás de la función

Algunas excelentes cosas de Excel que aprendí de un ex chico de E&Y:

• Vaya al final de la columna o fila de datos -> Do Alt + = “” => Se inserta la fórmula de suma y se muestra el resultado.
• Después de agregar una referencia de celda, presione “f4” => Se establece la referencia absoluta, esto es mucho más rápido que agregar dos veces los signos “$” a la referencia de celda.
• Cuando trabaje en varias hojas, seleccione todas las hojas y realice todos los cambios en una hoja. ejemplo mes o fórmula general => Todas las hojas se templan.

También hay sumas verticales, es decir, hacer la suma de una celda en particular a través de múltiples celdas … y también hacer referencias cruzadas de varias hojas de cálculo para mantenerlas todas sincronizadas y vinculadas dinámicamente.

Usando INDEX y MATCH juntos. Crea una alternativa potente y más flexible a BUSCARV y BUSCARH. Utiliza la función ÍNDICE para identificar la matriz de la tabla y luego utiliza COINCIDIR para devolver las coordenadas vertical y horizontal del elemento deseado dentro de esa matriz. Excelente para usar cuando necesita funcionalidad de búsqueda bidimensional.

Otro buen truco, pero menos conocido, es usar INDIRECTO para hacer referencia a celdas de otras hojas. Es útil cuando configura la salida a lo largo de una fila donde los elementos individuales en cada columna provienen de diferentes hojas. Solo asegúrese de usar la sintaxis correcta para hacer referencia a otras hojas (es decir, ‘hoja1’! A1); debe incluir el apóstrofe y el signo de exclamación.

Encuentro muchas cosas útiles en los comentarios, incluidos algunos de mis favoritos de todos los tiempos, como INDIRECTO, SUBTOTAL, SUMARIOS, CONDADOS, PROMEDIO, diferentes combos de BÚSQUEDAS (V / H), PARTIDO, ÍNDICE, SUSTITUTO, etc., junto con Una gran cantidad de teclas de acceso directo para navegar / insertar / eliminar / maximizar, etc.

Además de todo lo anterior, se debe hacer una mención especial de las funciones de fecha y cadena junto con las funciones TEXTO A COLUMNA y ELIMINAR DUPLICAR.

Mi adición a esta lista de funciones menos conocidas (la ayuda de Excel no le daría ni siquiera la sintaxis) sería la función DATEDIF versátil que puede darle la diferencia entre 2 fechas en forma de
1 El número de años completos.
2 El número de meses completos.
3 El número de días.
4 La diferencia entre los días (se ignoran los meses y los años).
5 La diferencia entre los meses (los días y los años se ignoran).
6 La diferencia entre los días (se ignoran los años y las fechas).

Visite MS Excel: Función DATEDIF (WS).

Cheerios!

La función sumproduct () de Excel es extremadamente poderosa, especialmente cuando se usa de formas no previstas originalmente. Ver ejemplo de uso en http://www.excel-examples.com/su …
Puede crear informes resumidos con múltiples criterios. Funciona en matrices pero no es una fórmula de matriz.

Puede aumentar y disminuir el tamaño de fuente con un método abreviado de teclado en Excel para Mac. El método abreviado de teclado es COMANDO + MAYÚS + COMMA (disminuir fuente) y COMANDO + MAYÚS + PERÍODO (aumentar fuente).

Ayudé a desarrollar un producto de cubierta de teclado que lo ayuda a aprender estos atajos y más para Excel en la Mac. La cubierta del teclado muestra los 20 accesos directos más comúnmente utilizados por los banqueros, analistas y estudiantes de MBA. ¡Espero que esto te ayude a ser más productivo cuando uses Excel! Cubierta del teclado Excel con atajos de teclado para Mac Excel • KeyCuts

Recientemente creé una publicación en el blog titulada 5 consejos de Excel poco conocidos que te harán ver más inteligente que tus compañeros de trabajo .

Coloqué el primer consejo a continuación, pero tengo una serie de videos que puedes seguir desde la publicación. Estos son los consejos que aprenderá en el artículo:

___________________________________________________

# 1 Aprenda a crear secuencias / listas de relleno personalizadas

# 2 Aprenda a usar el cuadro de nombre de Excel

# 3 Aprenda a usar imágenes en sus gráficos de Excel

# 4 Aprenda a usar la función de cámara de Excel

# 5 Aprenda a crear gráficos transparentes y formas de capa en Excel

___________________________________________________

Consejo Excel n. ° 1: aprenda a crear secuencias / listas de relleno personalizadas

PASO # 1 :

Lo primero que debe hacer es obtener acceso a la sección de edición de listas personalizadas de la pantalla de opciones de Excel. Para hacer esto, siga estos pasos.

• Vaya al archivo en la esquina superior izquierda de su pantalla de Excel.
• Luego, seleccione Opciones.
• Luego, deberá seleccionar la sección Avanzado de las Opciones de Excel.
• Desplácese hacia abajo hasta la sección General de la sección Avanzado y luego haga clic en Editar listas personalizadas.

Usted lo ha hecho!

PASO # 2 :

El segundo paso es crear una lista manual agregando elementos en la sección de entradas de la lista o puede usar la opción de importar lista seleccionando un rango de celdas y luego importando la lista. Una vez que se importa la lista o se agrega una lista creada manualmente, básicamente está listo para comenzar a usar estas listas con su controlador de relleno en Excel.

PASO # 3 :

El tercer y último paso es fácil. Puede tomar y usar el controlador de relleno en la esquina inferior derecha de su celda (comenzando con el primer elemento en su lista de relleno personalizada) y luego arrastrarlo hacia abajo con el mouse. Notará que la secuencia de relleno imita exactamente la lista que configuró utilizando la pantalla Editar listas personalizadas en el paso 2.

F2 para editar el contenido de una celda.

Ctrl + PgUp y Ctrl + PgDn para cambiar entre hojas.

Ctrl + Tab para cambiar entre libros de Excel.

Además, todas las opciones del menú se pueden activar utilizando la tecla Alt con uno o más accesos directos de letras. Hace mi vida mucho más fácil cada vez que estoy oxidada.

Sumproduct es la función más subestimada, creo. Puede sustituir algunas funciones bien conocidas, como sumif, countifs, averageifs, etc. Será muy útil si puede utilizar algunas funciones menos importantes (texto, información, etc.) dentro de él.
Nunca uso Vlookup, sino que uso la combinación de coincidencia de índice. Es más efectivo y menos propenso a errores. Por ejemplo, si ingresa el número de columna manualmente en la función vlookup y luego inserta una columna en la tabla, se mostrará un error incorrecto.
Además, las tablas de Excel son muy útiles. Especialmente cuando usa la función Sumproduct y donde se requiere un rango limitado en las funciones.