Si encuentra una situación en la que necesita actualizar manualmente sus datos,
es probable que haya una fórmula que pueda hacerlo por usted. Antes de pasar horas
y horas contando células o copiando y pegando datos, busque una solución rápida en
Excel, probablemente encontrará una.
2) Filtros
3) Eliminar duplicados
Los conjuntos de datos más grandes tienden a tener contenido duplicado. Usted puede tener una lista de múltiples contactos en una empresa y sólo quiere ver el número de empresas que tiene. En situaciones como esta, la eliminación de los duplicados es muy útil.
Para eliminar sus duplicados, resalte la fila o columna de la que desea eliminar los duplicados. A continuación, vaya a la ficha Datos y seleccione "Eliminar duplicados" (en Herramientas). Aparecerá una ventana emergente para confirmar con qué datos desea trabajar. Seleccione "Eliminar duplicados" y estará listo.
También puede utilizar esta característica para eliminar una fila completa en función de un valor de columna duplicado. Así que si tienes tres filas con la información de Harry Potter y solo necesitas ver una, entonces puedes seleccionar el conjunto de datos completo y luego eliminar los duplicados basados en el correo electrónico. La lista resultante sólo tendrá nombres únicos sin duplicados.
Cuando tiene filas bajas de datos en su hoja de cálculo, puede decidir que realmente desea transformar los elementos de una de esas filas en columnas (o viceversa). Se necesitaría mucho tiempo para copiar y pegar cada encabezado individual, pero lo que la función de transposición le permite hacer es simplemente mover los datos de fila en columnas, o al revés.
Comience destacando la columna que desea transponer en filas. Haga clic con el botón
secundario en él y, a continuación, seleccione "Copiar".
A continuación, seleccione las celdas en la hoja de cálculo donde desea que
comience su primera fila o columna. Haga clic con el botón secundario en la
celda y, a continuación, seleccione "Pegado especial". Aparecerá un módulo -
en la parte inferior, verá una opción para transponer. Marque esta casilla y
seleccione Aceptar. Su columna se transferirá ahora a una fila o viceversa.
5) Texto a las columnas
¿Qué pasa si desea dividir la información que se encuentra en una celda en dos celdas diferentes? Por ejemplo, tal vez quiera sacar el nombre de la empresa de alguien a través de su dirección de correo electrónico. O tal vez desee separar el nombre completo de alguien en un nombre y apellido para sus plantillas de marketing por correo electrónico.
Gracias a Excel, ambos son posibles. En primer lugar, resalte la columna que desea dividir. A continuación, vaya a la ficha Datos y seleccione "Texto a Columnas". Aparecerá un módulo con información adicional.
Primero, debe seleccionar "Delimitado" o "Ancho fijo".
"Delimitado" significa que desea dividir la columna en función de caracteres como comas,
espacios o pestañas.
"Anchura fija" significa que desea seleccionar la ubicación exacta en todas las
columnas que desea que se produzca la división.
En el caso de ejemplo a continuación, seleccione "Delimitado"
para que podamos separar el nombre completo en nombre y apellido.
Entonces, es hora de elegir los Delimitadores. Esto podría ser una pestaña,
punto y coma, coma, espacio, o algo más. ("Algo más" podría ser el signo "@"
utilizado en una dirección de correo electrónico, por ejemplo.) En nuestro ejemplo,
vamos a elegir el espacio. Excel le mostrará una vista previa de cómo serán sus nuevas columnas.
Cuando esté satisfecho con la vista previa, presione "Siguiente". Esta página le
permitirá seleccionar Formatos Avanzados si lo desea. Cuando haya terminado,
haga clic en "Finalizar".
FORMULAS DE ÉXCEL
6) Cálculos simples
Además de realizar cálculos bastante complejos, Excel puede ayudarle a hacer aritmética simple como sumar, restar, multiplicar o dividir cualquiera de sus datos.
- Para agregar, use el signo +.
- Para restar, use el signo -.
- Para multiplicar, utilice el signo *.
- Para dividir, utilice el signo /.
También puede usar paréntesis para asegurarse de que se realizan ciertos cálculos primero.
En el ejemplo siguiente (10 + 10 * 10), el segundo y el tercer 10 se multiplicaron juntos antes
de añadir el adicional 10. Sin embargo, si lo hicimos (10 + 10) * 10, el primero y el segundo
10 se sumarían juntos primero .
.gif?t=1541718076239&width=648&height=364&name=Simple_Math_GIF%20(1).gif)
Bonus: Si desea que el promedio de un conjunto de números, puede utilizar la fórmula =PROMEDIO (rango de celda). Si desea resumir una columna de números, puede utilizar la fórmula =SUM (Rango de celdas).
7) Fórmula de formateo condicional
El formato condicional le permite cambiar el color de una celda en función de la información dentro de la celda. Por ejemplo, si desea marcar ciertos números que están por encima del promedio o en el 10% superior de los datos de su hoja de cálculo, puede hacerlo. Si desea corregir los puntos comunes de código entre diferentes filas en Excel, puede hacerlo. Esto le ayudará a ver rápidamente la información que es importante para usted.
Para empezar, resalte el grupo de celdas en las que desea utilizar el formato condicional.
A continuación, elija "Formato condicional" en el menú Inicio y seleccione su lógica en el
menú desplegable. (También puede crear su propia regla si desea algo diferente.) Aparecerá
una ventana que le pedirá que proporcione más información acerca de su regla de formato.
Seleccione "Aceptar" cuando haya terminado y verá que sus resultados aparecen automáticamente.
8) Declaración IF
A veces, no queremos contar el número de veces que aparece un valor. En su lugar, queremos introducir información diferente en una celda si hay una celda correspondiente con esa información.
Por ejemplo, en la situación a continuación, quiero otorgar diez puntos a todos los que pertenezcan a la casa de Gryffindor. En lugar de escribir manualmente en 10's al lado del nombre de cada estudiante de Gryffindor, puedo usar la fórmula de SI ENTONCES Excel para decir que si el estudiante está en Gryffindor, entonces deben obtener diez puntos.
La fórmula: SI(logical_test, value_if_true, valor de false)
Ejemplo mostrado abajo: =SI(D2 = "Gryffindor", "10", "0")
En términos generales, la fórmula sería SI(Logical Test, valor de true, valor de false).
Vamos a cavar en cada una de estas variables.
- Logical_Test: La prueba lógica es la parte "SI" de la sentencia.
- En este caso, la lógica es D2= "Gryffindor" porque queremos asegurarnos de
- que la celda correspondiente al estudiante diga "Gryffindor".
- Asegúrese de poner a Gryffindor entre comillas aquí.
- Value_if_True: Esto es lo que queremos que la celda muestre si el valor es true.
- En este caso, queremos que la celda muestre "10" para indicar que el estudiante
- recibió los 10 puntos. Utilice sólo comillas si desea que el resultado sea texto en
- lugar de un número.
- Value_if_False: Esto es lo que queremos que la celda muestre si el valor es false.
- En este caso, para cualquier estudiante que no esté en Gryffindor,
- queremos que la celda muestre "0" para mostrar 0 puntos.
- Utilice sólo comillas si desea que el resultado sea texto en lugar de un número.
Nota: En el ejemplo anterior, otorgué 10 puntos a todos en Gryffindor. Si más tarde quería sumar el número total de puntos, no sería capaz de porque los 10 están en comillas, lo que hace que el texto y no un número que Excel puede sumar.
09) Signos de Dólar
¿Alguna vez has visto un signo de dólar en una fórmula de Excel? Cuando se usa en una fórmula, no representa un dólar americano;
en su lugar, se asegura de que la columna exacta y la fila se mantienen iguales incluso
si copia la misma fórmula en filas adyacentes.
Verá, una referencia de celda - cuando se refiere a la celda A5 de la celda C5, por ejemplo -
es relativa por defecto. En ese caso, se está refiriendo a una celda que tiene cinco columnas a
la izquierda (C menos A) y en la misma fila (5). Esto se llama fórmula relativa. Cuando
copia una fórmula relativa de una celda a otra, ajustará los valores en la fórmula en función de
dónde se mueve. Pero a veces, queremos que esos valores permanezcan igual sin importar si
se mueven o no - y podemos hacerlo haciendo la fórmula en la célula en lo que se llama fórmula
absoluta.
Para cambiar la fórmula relativa (= A5 + C5) en una fórmula absoluta, precederíamos los
valores de fila y columna por signos de dólar, como esto: (= $ A $ 5 + $ C $ 5).
(Obtenga más información en la página de asistencia de Microsoft Office aquí.)
FUNCIONES DE ÉXCEL
10) Función VLOOKUP
¿Alguna vez ha tenido dos conjuntos de datos en dos hojas de cálculo diferentes que desea
combinar en una sola hoja de cálculo?
Por ejemplo, es posible que tenga una lista de nombres de personas junto a sus direcciones
de correo electrónico en una hoja de cálculo y una lista de direcciones de correo electrónico
de las mismas personas al lado de los nombres de su empresa en la otra, pero desea que los
nombres, direcciones de correo electrónico y empresa nombres de esas personas para que
aparezcan en un solo lugar.
Tengo que combinar conjuntos de datos como este mucho - y cuando lo hago, el VLOOKUP es
mi ir a la fórmula. Antes de utilizar la fórmula, asegúrese de tener al menos una columna que
aparezca idénticamente en ambos lugares. Revise sus conjuntos de datos para asegurarse de que
la columna de datos que utiliza para combinar su información es exactamente la misma, sin
incluir espacios adicionales.
La fórmula: =VLOOKUP (valor de búsqueda, matriz de tabla, número de columna,
[búsqueda de rango])
La fórmula con variables de nuestro ejemplo a continuación:
=VLOOKUP (C2, Sheet2! A: B, 2, FALSE)
En esta fórmula, hay varias variables. Lo siguiente es cierto cuando desea combinar
información en la Hoja 1 y la Hoja 2 en la Hoja 1.
- Valor de búsqueda: este es el valor idéntico que tiene en ambas hojas de cálculo.
- Elija el primer valor en su primera hoja de cálculo. En el ejemplo que sigue,
- significa la primera dirección de correo electrónico de la lista o la celda 2 (C2).
- Tabla Array: El rango de columnas de la Hoja 2 de la que va a extraer sus datos,
- incluida la columna de datos idéntica a su valor de búsqueda (en nuestro ejemplo,
- direcciones de correo electrónico) en la Hoja 1, estamos intentando copiar a la Hoja 1.
- En nuestro ejemplo, esto es "Sheet2! A: B." "A" significa Columna A en la Hoja 2,
- que es la columna en la Hoja 2 donde se enumeran los datos idénticos a nuestro valor
- de búsqueda (correo electrónico) en la Hoja 1. La "B" significa Columna B, que
- contiene la información que sólo está disponible en la Hoja 2 que desea traducir a l
- a Hoja 1.
- Número de columna: Si la matriz de tabla (el rango de columnas que acaba de indicar)
- indica a Excel qué columna se encuentra en los nuevos datos que desea copiar a la
- Hoja 1. En nuestro ejemplo, esta sería la columna que se encuentra "Casa" "Casa"
- es la segunda columna de nuestro rango de columnas (tabla matriz), por lo que nuestro
- número de columna es 2. [Nota: Su rango puede ser más de dos columnas. Por ejemplo,
- si hay tres columnas en la Hoja 2 - Correo electrónico, Edad y Casa - y todavía quiere
- traer Casa en la Hoja 1, todavía puede usar una VLOOKUP. Sólo tiene que cambiar el
- "2" a un "3" por lo que retira el valor en la tercera columna: = VLOOKUP (C2: Sheet2!
- A: C, 3, falso).]
- Búsqueda de rangos: Utilice FALSE para asegurarse de que sólo extraiga
- coincidencias de valores exactos.
En el ejemplo siguiente, Hoja 1 y Hoja 2 contienen listas que describen información
diferente sobre las mismas personas, y el hilo común entre los dos es su dirección de
correo electrónico. Digamos que queremos combinar ambos conjuntos de datos para
que toda la información de la casa de la Hoja 2 se traduzca a la Hoja 1.
Así que cuando escribimos la fórmula = VLOOKUP (C2, Sheet2! A: B, 2, FALSE), traemos todos los datos de la casa en la Hoja 1.
Tenga en cuenta que VLOOKUP sólo extraerá valores de la segunda hoja que están a la derecha de la columna que contiene sus datos idénticos. Esto puede llevar a algunas limitaciones, por lo que algunas personas prefieren usar las funciones INDEX y MATCH en su lugar.
11) PARTIDO DEL ÍNDICE
Al igual que VLOOKUP, las funciones INDEX y MATCH extraen datos de otro conjunto de datos a una ubicación central. Aquí están las principales
diferencias:
- VLOOKUP es una fórmula mucho más simple. Si está trabajando con conjuntos de datosç
- grandes que requerirían miles de búsquedas, el uso de la función INDEX MATCH
- reducirá significativamente el tiempo de carga en Excel.
- Las fórmulas de MATCH de ÍNDICE funcionan de derecha a izquierda, mientras que las
- fórmulas de VLOOKUP sólo funcionan como una búsqueda de izquierda a derecha.
- En otras palabras, si necesitas hacer una búsqueda que tenga una columna de
- búsqueda a la derecha de la columna de resultados, entonces tendrías que reorganizar
- esas columnas para hacer una VLOOKUP. Esto puede ser tedioso con grandes conjuntos
- de datos y / o conducir a errores.
Así que si quiero combinar la información en la Hoja 1 y la Hoja 2 en la Hoja 1, pero los valores de
las columnas en las Hojas 1 y 2 no son los mismos, entonces para hacer un VLOOKUP, tendría que
cambiar alrededor de mis columnas. En este caso, yo elegiría hacer un INDEX MATCH en su lugar.
Veamos un ejemplo. Digamos que la Hoja 1 contiene una lista de los nombres de las personas y
sus direcciones de correo electrónico de Hogwarts, y la Hoja 2 contiene una lista de las direcciones
de correo electrónico de las personas y el Patronus que cada estudiante tiene. (Para los fans que no
son de Harry Potter, cada bruja o mago tiene un guardián animal llamado Patronus asociado con él
o ella.) La información que vive en ambas hojas es la columna que contiene las direcciones de
correo electrónico, pero esta columna de dirección de correo electrónico está en diferentes números
de columna en cada hoja. Utilizaría la fórmula INDEX MATCH en lugar de VLOOKUP, así que no
tendría que cambiar ninguna columna.
Entonces, ¿cuál es la fórmula, entonces? La fórmula INDEX MATCH es en realidad la fórmula
MATCH anidada dentro de la fórmula INDEX. Verá que diferencié la fórmula MATCH usando un
color diferente aquí.
La fórmula: = INDEX (matriz de tabla, fórmula MATCH)
Esto se convierte en: = INDEX (matriz de tablas, MATCH (lookup_value, lookup_array))
La fórmula con variables de nuestro ejemplo a continuación: = ÍNDICE (Sheet2! A: A, (MATCH (Sheet1! C: C, Sheet2! C: C, 0)))
Aquí están las variables:
- Tabla Matriz: El rango de columnas en la Hoja 2 que contiene los nuevos datos que desea
- traer a la Hoja 1. En nuestro ejemplo, "A" significa Columna A, que contiene la información
- "Patronus" para cada persona.
- Valor de búsqueda: es la columna de la hoja 1 que contiene valores idénticos en ambas
- hojas de cálculo. En el ejemplo que sigue, esto significa que la columna "correo electrónico" en la hoja 1, que es la columna C. Así: Sheet1! C: C.
- Matriz de búsqueda: es la columna de la hoja 2 que contiene valores idénticos en ambas hojas de cálculo. En el ejemplo que sigue, esto se refiere a la columna "correo electrónico" en la hoja 2, que pasa a ser también la columna C. Así: Sheet2! C: C.
Una vez que tenga sus variables rectas, escriba la fórmula INDEX MATCH en la celda superior de la
columna Patronus en blanco en la Hoja 1, donde desea que la información combinada se mantenga.
12) Función COUNTIF
En lugar de contar manualmente la frecuencia con la que aparece un determinado valor o número, deje que Excel haga el trabajo por usted. Con la función COUNTIF, Excel puede contar el número de veces que una palabra o número aparece en cualquier rango de celdas.
Por ejemplo, digamos que quiero contar el número de veces que aparece la palabra "Gryffindor" en mi conjunto de datos.
La fórmula: =COUNTIF (rango, criterios)
La fórmula con variables de nuestro ejemplo a continuación: = COUNTIF (D: D, "Gryffindor")
En esta fórmula, hay varias variables:
- Rango: El intervalo que queremos que cubra la fórmula. En este caso, ya que sólo nos
- centramos en una columna, usamos "D: D" para indicar que la primera y la última columna
- son ambas D. Si estuviera mirando las columnas C y D, usaría "C: D . "
- Criterios: Cualquier número o fragmento de texto que desee contar con Excel.
- Utilice sólo comillas si desea que el resultado sea texto en lugar de un número.
- En nuestro ejemplo, el criterio es "Gryffindor".
Simplemente escribiendo la fórmula COUNTIF en cualquier celda y presionando "Enter" me
mostrará cuántas veces aparece la palabra "Gryffindor" en el dataset.
13) Combinar células usando "&"
Bases de datos tienden a dividir los datos para que sea lo más exacto posible. Por ejemplo, en lugar de tener datos que muestren el nombre completo de una persona, una base de datos podría tener los datos como nombre y luego un apellido en columnas separadas. O, puede tener la ubicación de una persona separada por ciudad, estado y código postal. En Excel, puede combinar las celdas con diferentes datos en una celda mediante el signo "&" en su función.
La fórmula con variables de nuestro ejemplo a continuación: = A2 & "" & B2
Vamos a pasar por la fórmula junto con un ejemplo. Imagina que queremos combinar nombres y
apellidos con nombres completos en una sola columna. Para hacer esto, primero colocamos nuestro
cursor en la celda en blanco donde queremos que aparezca el nombre completo. A continuación,
destacaremos una celda que contenga un nombre, escribiremos un signo "&" y resaltaremos una
celda con el apellido correspondiente.
Pero no has terminado - si todo lo que escribes es = A2 & B2, entonces no habrá un espacio entre el
nombre y apellido de la persona. Para añadir ese espacio necesario, utilice la función = A2 & ""
& B2. Las comillas alrededor del espacio indican a Excel que coloque un espacio entre el nombre y
el apellido.
Para que esto sea cierto para varias filas, simplemente arrastre la esquina de esa primera celda hacia
abajo como se muestra en el ejemplo.
