Manual Alumno

Excel Avanzado

versión Excel 365 /2023

índice de Contenidos

Contenido
Introducción ........................................................................................................................................ 4
Unidad 1. Valores y Formato condicional ........................................................................................... 5
Encontrar Determinados Valores ................................................................................................... 5
Buscar Objetivo ........................................................................................................................... 5
Configuración Y Definición De SOLVER ....................................................................................... 7
Herramienta SOLVER ................................................................................................................... 9
Administrador De Escenarios .................................................................................................... 13
Validación De Datos ..................................................................................................................... 18
Validación De Datos .................................................................................................................. 18
Aplicación de la Validación de Datos ........................................................................................ 20
Validación Personalizada........................................................................................................... 22
Configuración De Mensajes....................................................................................................... 23
Borrar una validación de datos ................................................................................................. 24
Formato Condicional .................................................................................................................... 25
Formato Condicional ................................................................................................................. 25
Con Fórmulas............................................................................................................................. 31
Con Funciones ........................................................................................................................... 33
Unidad 2. Funciones y Tablas ............................................................................................................ 35
Funciones De Búsqueda. .............................................................................................................. 35
Función BuscarV ........................................................................................................................ 35
Función BuscarX ........................................................................................................................ 37
Función BuscarH ........................................................................................................................ 39
Funciones Para El Manejo De Bases De Datos En Tablas. ........................................................... 41
Filtros Avanzados ...................................................................................................................... 42
Funciones de Base de Datos en Excel........................................................................................ 45
Función Bdcontar ...................................................................................................................... 45
Función BDpromedio................................................................................................................. 46
Función Bdsuma ........................................................................................................................ 46
Funciones .Conjunto.................................................................................................................. 47
Función Contar.Si.Conjunto ...................................................................................................... 47
Función Promedio.Si.Conjunto.................................................................................................. 48
Función Sumar.Si.Conjunto ....................................................................................................... 49
Tablas Dinámicas Y Ejecutar Datos Externos. .............................................................................. 51
 Ejecutar Tablas Dinámicas Con Query Desde Otros Libros ....................................................... 51
Opciones Avanzadas De Td ....................................................................................................... 53
Crear, cargar o editar una consulta en Excel (Power Query) .................................................... 60
Crear Nueva Consulta................................................................................................................ 60
Trabajo Con Ms-Query .............................................................................................................. 64
Unidad 3. Macros .............................................................................................................................. 72
¿Qué Son Las Macros? .............................................................................................................. 72
Grabador De Macros ................................................................................................................. 74
Introducción A Visual Basic ....................................................................................................... 80
Mi primera macro en Excel ....................................................................................................... 82
Conclusiones ..................................................................................................................................... 90
Bibliografía y Referencias. ................................................................................................................. 91
Introducción

En este nivel de Excel Avanzado ya podemos utilizar las capacidades de Excel en el tratamiento de
los datos, mediante el uso de herramientas que permiten analizar estos para lograr más y mejor
información.

El uso de funciones y herramientas de análisis se hacen claves en un manejo más eficiente de Excel
a la hora de obtener provecho de la información.

Excel es una herramienta muy completa para la gestión de datos, cálculo de valores y análisis de
información. Entre sus muchas funcionalidades destacan las herramientas para el análisis de
datos. Con Excel es posible analizar datos estadísticos o técnicos ahorrando tiempo y energía. Para
obtener el mayor beneficio de estas herramientas es necesario conocerlas en profundidad y saber
qué nos permiten realizar.

Conocer todas las funciones y usos de las hojas de cálculo de Excel puede ayudarte a automatizar
tareas y procesos de empresas de mercados muy diferentes, pudiendo incluso llevar todo el plan
de marketing de una empresa
Unidad 1. Valores y Formato condicional

Encontrar Determinados Valores

La búsqueda de ciertos valores que apoyen nuestros resultados de formulas se puede facilitar al
momento de probar distintas posibilidades. Las siguientes herramientas que revisaremos nos
permiten formular hipótesis del tipo Y Si… es decir si cambio ciertos elementos, que resultado
puedo obtener para evaluar asi el mejor resultado o encontrar valores específicos. Estas
herramientas son Buscar Objetivo, SOLVER y Escenarios.

Buscar Objetivo
Si conoce el resultado que desea de una fórmula, pero no está seguro del valor de entrada que
necesita la fórmula para obtener ese resultado, use la característica Buscar objetivo.

Por ejemplo, supongamos que necesita un préstamo. Ya sabe cuánto dinero desea, cuánto tiempo
quiere tardar en pagar el préstamo y cuánto puede permitirse pagar cada mes. Puede usar Buscar
objetivo para determinar la tasa de interés que necesitará asegurar para cumplir con su objetivo
de préstamo.

En la hoja podríamos ingresar los siguientes datos:

En la celda B5 hemos ingresado la siguiente función: =PAGO(B5/12;B4;B3)

Como no tenemos una tasa de interés aun, asume que es 0, y nos arroja el pago indicado.

Usar Buscar objetivo para determinar la tasa de interés

En la pestaña Datos, en el grupo Previsión de datos, haga clic en Análisis de hipótesis

continuación, haga clic en Buscar objetivo.
En el cuadro Definir la celda, escriba la referencia de la celda que contiene la fórmula que desea
reSOLVER. En el ejemplo, esta referencia es la celda B6.

En el cuadro Con el Valor, escriba el resultado de la fórmula que desee. En el ejemplo, esto es -
900. Tenga en cuenta que este número es negativo porque representa un pago.

En el cuadro Cambiando la celda, escriba la referencia de la celda que contiene el valor que desea
ajustar. En el ejemplo, esta referencia es la celda B5.

Haga clic en Aceptar.

Buscar objetivo se ejecuta y genera un resultado, como se muestra en la siguiente ilustración.

La tasa de interés mostrada inicialmente no tiene formato, pero podemos aplicar % con 2
decimales, para que se muestre

Nota: La celda a la que busca objetivo cambia debe hacer referencia a la fórmula de la celda que
especificó en el cuadro Establecer celda.
Configuración Y Definición De SOLVER

SOLVER es un programa de complemento de Microsoft Excel que puede usar para llevar a cabo
análisis y si. Use SOLVER para encontrar un valor óptimo (mínimo o máximo) para una fórmula en
una celda, la celda objetivo, que está sujeta a restricciones o limitaciones en los valores de otras
celdas de fórmula de una hoja de cálculo. SOLVER trabaja con un grupo de celdas llamadas celdas
de variables de decisión o, simplemente, celdas de variables que se usan para calcular fórmulas en
las celdas objetivo y de restricción. SOLVER ajusta los valores de las celdas de variables de decisión
para que cumplan con los límites de las celdas de restricción y den el resultado deseado en la
celda objetivo.

En resumidas cuentas, puede usar SOLVER para determinar el valor máximo o mínimo de una
celda cambiando otras celdas.

Por ejemplo, revisaremos el siguiente caso para reSOLVER con SOLV ER

Un fabricante con 80Kg de acero y 120Kg de aluminio, quiere hacer bicicletas de paseo y de
montaña para vender, respectivamente a 200.000 y 150.000 pesos cada una para sacar el máximo
beneficio. Para la de paseo, empleara 1 kg de acero y 3kg de aluminio, y para la de montaña 2kg
de ambos materiales. ¿Cuántas bicicletas de paseo y de montaña deberá fabricar para maximizar
las utilidades?

Entonces, tengamos en cuenta que un modelo de programación lineal siempre se plantea bajo dos
supuestos. Uno es, establecer la función objetivo y dos tener en cuenta las restricciones del
problema.

Lo primero que debemos hacer es organizar la información del problema de forma ordenada y
formulada, para prepararla al momento en que ejecutemos el SOLVER Excel.

¿Porque necesitamos utilizar el SOLVER para responder a este ejercicio?

Cualquiera podría decir que, si vendemos la bicicleta de montaña por mayor valor, todo el material
que tenemos, lo vamos a utilizar en producir bicicletas de montaña. Pero lo que se busca, es una
función objetiva que maximice el beneficio.

¿Por qué? Porque si para la bicicleta de montaña necesito 1kg de acero y 3kg de aluminio y nos
gastamos los 80kg de acero en producir bicicletas de montañas, podemos hacer 80 bicicletas. Pero
resulta que de aluminio por cada bicicleta vamos a utilizar 3kg del material y tenemos 120kg, con
lo cual nos daría solo para producir solo 40 bicicletas.
Es decir, que nos estaría sobrando un material si decidiéramos producir solo en acero o en
aluminio. Entonces, tendríamos un material que podríamos utilizar en la producción de las
bicicletas de paseo.

La idea es encontrar un modelo que nos pueda satisfacer la producción de los dos modelos de
bicicletas, maximizando los ingresos y minimizando los recursos ociosos, es decir que no nos sobre
la mitad del aluminio o la mitad del acero.

Y es aquí donde el SOLVER entra en acción. Lo primero es plantear el ejercicio en el Excel y ahora
vamos a formular de alguna manera el problema.

La sección de Producción, son las variables que no sabemos y que queremos hallar, para poder
generar la producción del producto. Ahora necesitamos saber el total de materiales. Y ¿a qué es
igual el total de materiales? pues básicamente a la multiplicación de lo que emplee de acero
versus lo que se produzca, para esto utilizamos la función SUMAPRODUCTO en las celdas D6 y E6
ingresamos las respectivas formulas:

=SUMAPRODUCTO(D3:D4;$H$3:$H$4)

=SUMAPRODUCTO(E3:E4;$H$3:$H$4)

Y abajo en amarillo tenemos las restricciones, que nos la da el problema, la cual dice que el
herrero tiene 80kg de acero y tiene 120kg de aluminio, es decir que nosotros no podemos superar
esa cantidad de material, podemos utilizar menos pero no más.

Por último, lo más importante es plantear la Función Objetivo (F.O). Y ¿Cuál es la función objetivo
de este ejercicio? Si la hacemos matemáticamente, la función de ejercicio seria (20.000 X + 15.000
Y) ósea el precio de venta por lo que produzca bicicletas de montaña y por lo que produzca
bicicletas de paseo

Queremos encontrar el máximo valor que eso nos pueda generar, es decir las variables X y Y que
nos produzca los mayores beneficios, teniendo en cuenta las restricciones.

La Función Objetivo es la matriz de precio venta por la matriz de producción, que es nuestra
variable independiente e incognita que tenemos en el momento. La ubicaremos en la celda D10

=SUMAPRODUCTO(G3:G4;H3:H4)

Entonces, nuestro problema se plantea en la siguiente forma

Herramienta SOLVER

Vamos a reSOLVER el ejercicio con el SOLVER. Lo primero que debemos hacer es verificar si esta
activo, de lo contrario primero activar el SOLVER, recuerde que el SOLVER es un complemento y
debemos activarlo en el Excel.

Puedes verificar si esta activo mirando en la pestaña datos si aparece el complemenbto, si no

aparece sigue los siguientes pasos:

Archivo – Opciones – Complementos – Complementos de Excel – Ir

Aparece la siguiente ventana

.
Marcamos el completo SOLVER y Aceptar.

Ahora nos vamos a la ficha datos y ya debe aparecer.

Ya disponible entonces le damos clic en SOLVER

Establecemos el objetivo, recordemos que es la Función Objetivo que en el archivo del ejercicio la
hemos ingresado en la celda D10.

¿Maximizar o minimizar los objetivos?

La función objetivo puede ser para maximizar, para minimizar o para llegar a un valor X.
Normalmente las funciones objetivo para maximizar se pueden aplicar a opciones como ingresos
de utilidades, que generan valor y de minimizar son aplicables a costos de operaciones.

Para el ejercicio dejamos marcado Max y pasamos a la siguiente opción

cambiando las celdas de variables. Donde las celdas que nos van a hacer mover todo el modelo y
que nos van a arrojar un valor en la función objetivo son las variables incógnitas, ósea lo que
debemos producir de X y Y, es decir de bicicletas de montaña y bicicletas de paseo.

En síntesis, lo que vamos a poner son las variables que desconocemos y que nos van a ayudar a
calcular el SOLVER para poder conocer cuál es el máximo ingreso que podemos obtener del
ejercicio.

Ahora vamos a agregar las restricciones, en las cuales tenemos que en acero hay 80 kg y en
aluminio 120 kg

Tenemos que ser claros en señalar que no podemos usar mas material del que tenemos, entonces
damos clic en el botón agregar

Y nos mostrara lo siguiente

En la referencia de celda indicamos la celda de producción en acero, que no debe superar la
cantidad de material disponible,

luego damos clic en Agregar y lo mismo con el aluminio.

Luego agregamos una restricción para que las unidades determinadas no permitan decimales, o
dicho de otra manera solo entregue un numero entero.

Para las unidades de bicicletas de montaña

Y para las unidades de bicicletas de paseo

Finalizamos con Aceptar

Las restricciones lucirán asi en la hoja de SOLVER

Ahora ya tenemos todo el modelo listo, con nuestro objetivo, que es la celda Función Objetivo, las
celdas que debe cambiar que serían prácticamente las variables que no conocemos.

Como ya tenemos nuestro ejercicio formulado, cuando el SOLVER calcule las variables óptimas de
producción, todo el modelo nos va a calcular la F.O en base al cálculo de producción y precio de
venta.

Para lograr el calculo damos clic en el botón Resolver y SOLVER analizará el problema y resolverá
las variables para darnos el resultado.
Esta ventana de mensaje nos da la opción de aceptar la propuesta del resultado y conservarla en la
hoja de cálculo. O bien, volver a los valores originales.

Aca se ven los resultados logrados

Podemos observar que el máximo ingreso será de $8.500.000 si se producen 20 bicicletas de

montaña y 30 bicicletas de paseo. Además, se ha utilizado el 100% del material.

En conclusión, SOLVER nos permite definir Objetivo; maximizar o minimizar o alcanzar un valor
especifico; establecer las variables; definir restricciones y asi, establecer la mejor solución al
problema planteado.

Es importante identificar cuáles serán las variables y las restricciones que tendremos que imponer
al modelo para lograr un resultado ideal.
Administrador De Escenarios

Un escenario es un conjunto de valores que Excel guarda y puede sustituir automáticamente en la

hoja de cálculo. Puede crear y guardar diferentes grupos de valores como escenarios y, a
continuación, cambiar entre estos escenarios para ver los diferentes resultados.

Si varias personas tienen información específica que desea usar en escenarios, puede recopilar la
información en libros independientes y, a continuación, combinar los escenarios de los distintos
libros en uno.

Después de tener todos los escenarios que necesita, puede crear un informe de resumen del
escenario que incorpore información de todos los escenarios.

Los escenarios se administran con el asistente administrador de escenarios desde la pestaña Datos
en el grupo Previsión / Análisis de hipótesis / Administrador de Escenarios.

Los escenarios y las tablas de datos llevan conjuntos de valores de entrada y proyecto hacia
adelante para determinar los posibles resultados.

Un escenario puede tener un máximo de 32 valores diferentes, pero puede crear tantos
escenarios como desee.
Crear escenarios en Excel es realmente simple, no obstante, pueden surgir complicaciones de
acuerdo con la naturaleza del documento que estás creando. De esta manera, podemos decir que
la herramienta es fácil de usar, aunque los escenarios planteados sean complejos.

Definir las Variables

Lo primero que debemos hacer para generar escenarios en Excel es definir nuestras variables. Las
variables no son más que los datos que cambian el resultado de nuestro cálculo y que, por ende,
presentan una nueva situación. Continuando con el ejemplo del préstamo, las variables pueden
ser tanto el plazo, como la tasa de interés.

Completar datos de las variables

Una vez definidas las variables con las que vamos a trabajar, debemos completar sus datos. Esto
quiere decir que, necesitamos otorgarles valores a estas variables a fin de generar las operaciones
que darán vida al escenario que estamos creando.

Creando el Escenario

Veamos el siguiente caso, solicitas un préstamo $5.000.000 a 12 meses, puedes generar distintos
escenarios tomando el plazo como variable o el interes. De esta manera, tendrás la posibilidad de
ver el monto mensual a pagar con rangos de tiempo distintos, de forma automática.

Se han cotizado a distintos plazos diferentes tasas de intereses, entonces, plantearemos el

escenario para ver cual es la más conveniente.

Nos valemos de la función Pago para determinar el valor de la cuota a pagar por mes, con la
siguiente formula

=PAGO(C5/12;C4;C3)

Abrimos el administrador de escenarios y damos clic en Agregar y nos mostrara:

El nombre del escenario es que queramos para identificar en que consiste, por ejemplo “Plazo a 12
meses”
las celdas cambiantes se refieren al rango de celdas que se modificaran por causa del escenario. En
este ejemplo, será la celda del plazo y el interés.

Comenzaremos asi:

Damos clic en aceptar y nos pedirá indicar los valores a usar como variables, por defecto tomara
los que actualmente se encuentran en las celdas.

Mantenemos estos primeros datos y damos clic en Agregar

Esta vez nos pedirá indicar un nuevo escenario, que nombraremos Plazo a 18 meses, las celdas
cambiantes serán las mismas. Al Aceptar, modificaremos los valores del nuevo escenario

Repetimos agregar para el ultimo escenario que nombraremos Plazo a 24 meses, las celdas
cambiantes serán las mismas. Al Aceptar, modificaremos los valores del nuevo escenario

AL no haber mas variables, terminamos con Aceptar.

Nos mostrara la ventana principal del administrador y los escenarios creados.

Resultados del Escenario

Para ver el resultado del escenario podemos seleccionar el que queremos ver y dar clic en
Mostrar, asi, los valores en la hoja de cálculo cambiaran.

Esto seria de una forma manual. Pero también podemos obtener los resultados como un informe.

Si damos clic en el botón resumen, nos mostrara algo como esto:

Podemos obtener un resumen como una tabla, o podemos generar un resumen como una tabla
dinámica.

Como Resumen. En la Celda de resultado debemos indicar la celda que contiene el valor
expresado por la formula. En este ejemplo, C6 y C8, que representan la cuota a pagar y el valor
total del crédito, al Aceptar presentara un informe como este en una nueva hoja.

Asi, podemos evaluar de una vez las posibilidades del escenario.

Las celdas que aparecen como referencias, indican de donde se obtiene el dato. Si se desea se
pueden modificar manualmente y cambiarlas por un título, tal como Plazo, Interés o similar para
dar más claridad.

Como Informe de Tabla dinámica de escenario, en caso de seleccionar esta opción, esta lucirá asi:

Los encabezados de referencias pueden ser cambiados por texto. Y pueden verse asi:
Validación De Datos
La validación de datos en Excel es una herramienta usada para tener control sobre los datos que
se pueden escribir en una celda. Consiste en restringir el registro de información dentro de una
celda o rango de celdas, de esa manera solo se tiene acceso a ellas si se ingresan los datos que
cumplen con los criterios o condiciones preestablecidas.

De acuerdo con el tipo de información registrada en las celdas de Excel, un comando de validación
de datos puede delimitar el registro de un texto, número, porcentaje, fecha y hora. Con
una validación de datos en Excel personalizada podrás presentar informes, cuadros o bases de
datos libre de errores de digitación con los que tendrás un mejor desempeño laboral o en tus
actividades académicas.

En atención al tipo de datos que desees fijar, con la validación de datos es posible:

• Determinar pautas para establecer una cantidad máxima y una mínima.

• Omitir un intervalo de números.
• Omitir intervalos de fechas u horarios.
• Limitar cifras menores, mayores o iguales a cierto valor.

De igual manera, con la validación de datos en Excel es posible establecer condiciones de registros
de información en el “mensaje de entrada”, detallando el motivo por el cual no es posible apuntar
el dato ingresado y por el que aparecerá en un “mensaje de error”. De esta manera, se les dará
instrucciones a los usuarios de cómo introducir correctamente la información.

Validación De Datos
Para Utilizar la validación de datos debemos utilizar el icono de validación desde el menú Datos /
Grupo Herramientas de Datos

Al dar clic en Validación de datos, o en el icono nos mostrara la venta de validación con las
pestañas Configuración, Mensaje de Entrada y Mensaje de error.
Existen diferentes tipos de validación de datos en Excel, cada uno cumple una función que implica
que la información manejada sea exacta, con datos incontrovertibles en los cuales se puede
apoyar una decisión.

Al dar clic en el recuadro permitir nos mostrara los tipos de validaciónes permitidas.

Cualquier valor:

Está incluida por defecto en la celda, no realiza ninguna validación

Validación de números enteros y decimales:

En la validación de datos de números enteros y decimales se establece un límite máximo y un

límite mínimo.

Números enteros

Se puede permitir solo el ingreso de números enteros, estos son los número negativos y positivos
entre los cuales se incluye el cero y su estructura no tiene una parte decimal.

Decimales

Igualmente, se puede seleccionar un límite de datos con número decimal, el cual está compuesto
por una parte entera y una decimal, usado para representar porcentajes y números que son más
pequeños que una unidad, como 0.5

Validación de fecha y hora:

Se usa para establecer que la fecha para el pago de una cuenta sea entre hoy y dentro de tres días.
O que las citas para asignar estén dentro de un rango que contempla nuestro horario laboral.

Validación de la longitud de una cadena de texto:

El criterio de validación permite seleccionar una longitud de texto que, al ser introducido dentro
de la celda de una hoja de cálculo, tenga una cantidad máxima de caracteres. Es decir, que si el
límite de letras o números es ocho, no existe la posibilidad de ingresar nueve o más.

Validación de datos personalizada:

Cuando estés ante una situación en la que no encuentras utilidad en los tipos de validación vistos,
puedes usar una validación de datos personalizada; con una fórmula introducida en la celda de la
hoja de cálculo, es posible validar los datos registrados en ella.

Lista de validación de datos en Excel

La lista no necesita un valor máximo ni un valor mínimo, lo que la diferencia de los demás criterios
de validación. Basta con indicar la lista de los valores que permitimos dentro de una celda.

Por ejemplo, si deseamos que la validación nos muestre únicamente los sábados, domingos y
lunes, solo en necesario introducir los valores separados por una coma y guardar: sábado,
domingo, lunes

La coma (,) es el carácter, por defecto, de separación, pero varía de acuerdo a la configuración del
equipo. Es así como la separación también se puede dar por punto y coma (;): sábado; domingo;
lunes

Cuando confirmamos los valores, la celda habilitará un desplegable para ver las opciones posibles.

Aplicación de la Validación de Datos

Como primer paso será el de seleccionar la celda o rango de celdas sobre el que deseamos aplicar
la validación de datos.

Tomaremos como ejemplo la siguiente planilla

Numero Entero menor a 1.000

• Seleccionamos el rango B4:B6

• Validación de Datos/ Permitir /Numero Entero
• Seleccionar en Datos: menor que
• Se mostrará un recuadro Máximo donde ingresamos el valor 1.000

• Aceptar

Si intentamos ingresar un valor, se aplicará las condiciones que hemos definido, por lo que si el
valor es menor a 1.000 lo aceptara, pero de lo contrario indicara un mensaje de error indicando
que no es posible ya que no cumple con la validación establecida.

Con Reintentar podemos ingresar un nuevo valor, Cancelar borra el ingreso

El procedimiento para otras de las validaciónes es similar, donde siempre debemos identificar que
vamos a Permitir, Como se validaran los datos, e indicar los valores límites para validar.

Veamos algunos ejemplos con los otros casos.

Numero con Decimal

• Seleccionamos el rango C4:C6

• Validación de Datos/ Permitir /Decimal
• Seleccionar en Datos: Entre
• Se mostrará un recuadro Mínimo y Máximo
• ingresamos los valores limites 0 y 10
• Aceptar

Esta validación permitirá el ingreso de valores entre 0 y 10 permitiendo el uso de decimales, tal
como 5,5. No hay limitación del número de decimales.

Fechas

• Seleccionamos el rango D4:D6

• Validación de Datos/ Permitir /Fecha
• Seleccionar en Datos: menor que
• Se mostrará un recuadro Fecha final
• ingresamos la fecha límite 1/1/2023
• Aceptar

Esta validación permitirá el ingreso de fechas anteriores al 1-1-2023

Horas

• Seleccionamos el rango E4:E6

• Validación de Datos/ Permitir /Hora
• Seleccionar en Datos: Entre
• Se mostrará un recuadro Hora inicial y Hora final
• ingresamos las horas límites 8:00 y 18:00
• Aceptar

Esta validación permitirá el ingreso de horas que se encuentren dentro del rango horario
establecido.

Longitud de Texto

• Seleccionamos el rango F4:F6

• Validación de Datos/ Permitir /Longitud de texto
• Seleccionar en Datos: mayor o igual que
• Se mostrará un recuadro Mínimo
• ingresamos la longitud mínima del texto, 5
• Aceptar

Esta validación permitirá solo el ingreso de datos con una longitud mínima de 5 caracteres.
 Lista de Validación

• Seleccionamos el rango G4:G6

• Validación de Datos/ Permitir /Lista
• Seleccionar en Datos: no requerido
• Se mostrará un recuadro Origen
• Ingresamos el nombre de las ciudades separados por ;
• Aceptar

Esta validación permitirá que al posicionarse en una de las celdas pueda

desplegar la lista con las ciudades, de modo de seleccionar con clic y que el dato
quede en la celda, también es posible escribirlo manualmente, pero debe ser tal
cual fue ingresado en la lista.

Otra posibilidad es usar como origen los datos que se encuentren en un rango
de celdas, indicando este o el nombre de rango si ha sido asignado, en este ultimo caso, se debe
indicar el nombre anteponiendo el signo igual (=)

Origen con rango Origen con nombre de rango

Validación Personalizada
El tipo de validación de datos en Excel personalizada está diseñado para condicionar los datos a
través de una fórmula. Debemos utilizar la validación Personalizada

En este caso vamos a utilizar la fórmula de CONTAR.SI para indicarle a la hoja de cálculo que no
queremos duplicados a lo largo de la columna «A», la cual contiene los nombres de los
trabajadores de una empresa.

Lo escribimos en el recuadro Fórmula como aparece en la imagen y así ya no permitirá nombres

repetidos.

La fórmula va a depender de que queremos validar, como poner la restricción cuando los
parámetros predefinidos no cubran lo deseado.
Configuración De Mensajes

Una configuración que podemos ajustar es la de los mensajes de la validación, podemos

personalizar el contenido de los mensajes en las pestañas Mensaje de entrada y Mensaje de error.

Utilizaremos el rango de validación de los números enteros vistos antes para ver los efectos

Mensaje de Entrada

• Seleccionamos la pestaña Mensaje de

entrada
• En el recuadro Titulo escribimos un texto
• En el recuadro Mensaje de entrada podem os
escribir una instrucción, un mensaje, o cualquier
indicación al usuario.
• Aceptar

Cuando el usuario da clic sobre la celda donde ingresará el dato, le mostrará una ventana con las
indicaciones.

Mensaje de Error

• Seleccionamos la pestaña Mensaje de error

• En el recuadro Titulo escribimos un texto
• En el recuadro Mensaje de error podemos escribir una instrucción, un mensaje, o
cualquier indicación al usuario respecto al error cometido.
• Aceptar

Cuando el usuario ingresa un dato que no cumpla las restricciones de la validación, le mostrará
una ventana con el mensaje de error preparado.
Es posible ajustar las alertas cambiando el nivel del error según se indica en el recuadro Estilo:

Este ajuste influirá en los botones de acción que mostrará el mensaje de error.

Borrar una validación de datos

Para quitar la validación establecida en una celda o rango de celdas, bastara con seleccionar este,
abrir la validación de datos y en la parte inferior de la ventana encontraras un botón Borrar todos.
Basta un clic y la validación se eliminará.
Formato Condicional

El formato condicional puede ayudar a que los patrones y tendencias en sus datos sean más
visibles. Para usarlo, cree reglas que determinen el formato de las celdas en función de sus
valores, con colores de celda vinculados a valores de celda, o estilos de fuente, tamaños, etc.

Puede aplicar formato condicional a un rango de celdas (tanto a una selección como a un rango
con nombre), a una tabla de Excel y, en Excel para Windows, incluso a un informe de tabla
dinámica.

Utilice el formato condicional para destacar información, analizar datos y tendencias, resaltar
celdas o rango de celdas, detectar problemas y variaciones específicas de los datos trabajados.

El formato condicional en Excel nos permite mediante una condición dada, resaltar una celda o un
rango de celdas, aplicar colores cuando haya variaciones específicas en los datos, así como aplicar
iconos según las condiciones que se especifique.

Si las condiciones dadas en el formato condicional son verdaderas este aplicará el formato a la
celda o rango de celdas. Si son falsas, la celda o el rango de celdas no recibirán el formato
establecido.

Revisaremos varias condiciones que puedes emplear en un formato condicional, también te

mostraremos como crear una regla o condición propia. Y como un formato condicional en Excel
puede aplicarse no solo a una celda o rango de celdas, sino también a una tabla de Excel e incluso
a una tabla dinámica en Excel.

Formato Condicional
Para aplicar el formato condicional siempre como primer paso será seleccionar la celda o rango de
celdas donde aplicarlo, luego desde el menú Inicio, en el grupo Estilos, encontraras el icono de
formato condicional.

Al dar clic en formato condicional te mostrará la ventana con las opciones definidas

Cada una de las opciones te entrega diversas formas de aplicar el

formato condicional.

Revisaremos cada una de las opciones predeterminadas y también

revisaremos la posibilidad de aplicar formatos condicionales
personalizados basados en reglas o formulas

Nos basaremos en una planilla ejemplo a la que aplicaremos los

casos que revisaremos.
 Reglas para resaltar Celdas

Estas reglas aplican de manera simplificada al valor de las

celdas, usando comparadores del tipo mayor que, menor
que, entre, igual a.

Además, puede verificar contenido de las celdas, fechas y

verificar si hay valores duplicados.

Ejemplo:

Para los datos de la imagen, queremos que muestre las celdas de color verde cuando la edad sea
menor a 21

Entonces, seleccionamos el rango, buscamos en formato condicional la opción Es menor que y le

indicamos la edad límite, 21. En el recuadro con podemos seleccionar como destacar las celdas.

Al aceptar, las celdas que cumplan la condición se muestran destacadas.

Lo interesante, es que si los valores cambian el formato condicional revisa el nuevo dato y aplica o
no si corresponde.

EL tipo de formato que queremos aplicar podemos seleccionarlo en el recuadro desplegando

Y si queremos darle nuestro propio formato personalizado usamos la opción final que nos abrirá la
ventana de formatos de celda

Aca podemos seleccionar aplicar formatos

a los valores numéricos, a la Fuente, a los
Bordes de las celdas y el Relleno, según la
pestaña que seleccionamos.

En la imagen vemos las opciones para

Fuente
Opciones para Número

Opciones para Borde

Opciones para Relleno

Entonces, podemos definir las características que queramos que muestre el formato condicional a
los datos cuando cumplan las condiciones estipuladas

Reglas para valores superiores e Inferiores

Estas reglas permiten para un rango seleccionado de

valores identificar por defecto los 10 más altos o bajos,

También pueden identificar x% de valores superiores o

inferiores (por defecto busca el 10%)

Ejemplo:

Primero seleccionaremos los datos sobre los cuales aplicar el formato condicional.

Luego, abrimos formato condicional / Reglas para valores superiores e inferiores / 10 superiores

En la ventana que nos muestra modificaremos la cantidad de 10 a 5, y seleccionaremos un

formato de relleno verde con texto verde oscuro, y Aceptar.
En este caso, hemos destacado los 5 valores más altos del rango

Barras de Datos

Este formato de manera muy simple, crea un grafico de

barra dentro de la celda con el valor.

Aplicado a un rango, determina el tamaño de la barra

proporcionando según el valor. Mientras mas alto el
numero la barra ocupa toda la celda y se ajusta
automáticamente.

Da la opción de una barra de color degradado y otra de

relleno sólido. El color a elección.

Ejemplo:

Primero seleccionar el rango de celdas al que aplicaremos el formato condicional.

Escoger de Formato Condicional / Barra de Datos / Color deseado.

Las barras lucirán asi, en el ejemplo se muestra una barra degradado azul y una sólida en verde

Escalas de Color

Este tipo de formato, aplicado a un rango de valores,

organiza los datos en tramos de color, clasificando los
valores mas bajos en un tono, los intermedios en otro y
los mas altos en otro. Pasando por una escala de colores
de uno a otro.

Es una manera de segmentar los valores.

Ejemplo:

Primero seleccionar el rango de datos a usar. Luego, abrir formato condicional / Escalas de color /
seleccionar modelo de colores.

En este ejemplo se seleccionó el tipo Escala de colores Azul, blanco, Rojo. Donde el Rojo
representa los valores más bajos y el azul los más alto. El rango de valores lucirá asi

Conjunto de Iconos

Similar el caso anterior, los conjuntos de iconos se usan para

clasificar los valores de un rango en tramos bajo, medio y
alto, esto por valores predeterminado que pueden ser
modificados.

Hay iconos del tipo flecha (Direccional), del tipo semáforo

(Formas), indicadores y de valoración.

Se pueden establecer los tramos desde 3 (por defecto) hasta

5

Ejemplo:

Primero seleccionaremos el rango de datos sobre el que se aplicara el formato condicional.

Seleccionamos formato condicional / Conjunto de Iconos / Direccional (3
flechas)

En este ejemplo, las flechas verdes representan el segmento alto de los

valores, la flecha amarilla el segmento medio y la roja los valores bajos.

Estos tramos vienen predeterminados y pueden ser modificados en la opción

administrar reglas.
Borrar Reglas

Para quitar formatos condicionales aplicados a un rango o a una hoja completa, al abrir el icono de
formato condicional encontraremos en la parte inferior la opción Borrar Reglas, con las
alternativas indicadas.

Administrar Reglas

Esta opción es la que permite administrar las reglas que regulan las condiciones que se han
aplicado. Incluye Eliminar regla, Editar regla, Duplicar regla y Nueva regla.

Al seleccionar un rango con formato condicional aplicado mostrará las reglas activas, se puede
seleccionar la deseada para aplicar una de las tareas que ofrece el administrador de reglas.

Si seleccionamos el conjunto de iconos y luego damos clic en Editar Regla

Aca podríamos modificar el rango que se aplica para segmentar los valores, por ejemplo, podemos
ver que la flecha verde se ubica en los valores que estén sobre el 67% de la muestra, la flecha
amarilla entre el 33% y 67% de la muestra y la flecha roja cuando los valores están bajo el 33% de
la muestra. EL tipo también puede ser cambiado a número, formula o percentil.

Estas modificaciones son aplicables en la mayoría de las reglas predeterminadas y también cuando
se construyen reglas personalizadas.
Con Fórmulas
Podemos construir nuestra propia regla condicional para aplicar a nuestros datos.

Explicaremos este caso con un ejemplo práctico. Tenemos la siguiente planilla con datos que
representan la fecha de cumpleaños de algunas personas. Queremos que el formato condicional
pueda destacar con una celda de tono rojo cuando el cumpleaños de alguno este próximo.
También queremos que si ingreso en la columna regalo la palabra “SI” me destaque la misma con
celda verde y letra blanca.

Entonces comenzaremos por crear una nueva regla para el rango de las fechas.

Seleccionar rango A3:A10

Formato Condicional / Nueva Regla / Seleccionar Tipo de Regla:

Utilice una fórmula que determine las celdas para aplicar formato: =A2>HOY()

La fórmula usa la función HOY para ver si las fechas de la columna A son posteriores a hoy
(en el futuro). De ser así, se aplica formato a las celdas.

Ahora clic en el botón Formato. Y En la venta de Formato de celdas aplicaremos lo

siguiente:

En la pestaña Fuente usaremos color Rojo y en estilo Negrita, en la pestaña Relleno

seleccionaremos un tono rojo claro.

Aceptar y Aceptar para que se aplique el formato en la columna A

Esta debe lucir asi:

Agregaremos una segunda regla para la columna Regalo

Seleccionar rango c3:c10

Formato Condicional / Nueva Regla / Seleccionar Tipo de Regla:

Utilice una fórmula que determine las celdas para aplicar formato: =C2 = "Y"

Ahora clic en el botón Formato. En la ventana de Formato de celdas aplicaremos lo

siguiente:

En la pestaña Fuente usaremos color Blanco y en estilo Negrita, en la pestaña Relleno

seleccionaremos un tono verde.

Aceptar y Aceptar para que se aplique el formato en la columna A

Esta debe lucir asi:

Al escribir en cualquiera de las celdas de la columna C la palabra “Si” la celda se pinta de verde con
la palabra en blanco y negrita.

Al crear una regla basada en formulas, puede ser cualquiera que cumpla con la lógica del resultado
esperado.

Una sugerencia si no estas seguro de la formula que aplicaras, es probarla antes fuera y luego
copiarla al crear el formato condicional
Con Funciones
La aplicación del formato condicional usando funciones, en lo práctico, es lo mismo, esto significa
que la formula va a estar compuesta por funciones que apliquen para obtener el resultado a
condicionar para la aplicación del formato.

Se siguen los mismos pasos anteriores, pero veamos un ejemplo

Supongamos que tengo una lista de fechas y necesito que identifique con un color rojo y fuente
blanca aquellas que correspondan a Domingo, y con celda verde y fuente blanca los dias sábado,
tenemos lo siguientes datos:

Primera condición: Seleccionaremos el rango A3:A14

Formato Condicional / Nueva Regla / Utilice una formula…

Formato: Fuente color blanco, Relleno Rojo

Segunda Condición: Seleccionaremos el rango A3:A14

Formato Condicional / Nueva Regla / Utilice una formula…

Formato: Fuente color blanco, Relleno Verde

La función DIASEM() obtiene el número de día de la semana al que

corresponde una fecha.

En la función =DIASEM(A3;2) A3 corresponde a la primera celda de la

columna y el valor 2 le indica a la función que se usara el día lunes
como día 1 al domingo como día 7

El formato condicional aplicado entonces nos entrega el sábado en

verde y el domingo en rojo

Para comprobar, podemos ingresar en la celda B3 la siguiente formula:

=TEXTO(A3;"dddd")

y luego copiarla al resto de las celdas, podremos verificar en texto el día de cada fecha.
Unidad 2. Funciones y Tablas

Funciones De Búsqueda.

Función BuscarV
Sintaxis: =BUSCARV(valor_buscado; matriz_buscar_en; indicador_columnas; (ordenado) )

Busca un valor en la primera columna de una tabla o matriz y devuelve un valor que está en la
misma fila donde se encontró la coincidencia, pero en la columna especificada. Es posible indicar
una búsqueda exacta o aproximada.

El nombre BUSCARV, se refiere a una búsqueda vertical en la matriz

Queremos obtener ingresando un código en la celda A4, me muestre a que producto corresponde.

La función BUSCARV nos permite ejecutar esta acción mediante la formula expresada de la
siguiente forma:

Donde A4 es el valor buscado, la celda donde se encuentra el dato que queremos buscar,

$E$3:$G$8 corresponde al rango de la matriz o base de datos donde se encuentran los datos, se
ha usado referencia absoluta para que este no cambie al copiar la formula hacia las celdas
inferiores de la tabla de búsqueda.

2, es el número de columna en la matriz de donde queremos extraer el dato a devolver (en este
caso, el nombre del producto)

FALSO o 0, es la indicación par que la búsqueda sea exacta, es decir, busque el dato de A4 idéntico.
Cuando se hace búsqueda aproximada se utiliza Verdadero o 1

El resultado será:

Copiamos la formula a las celdas inferiores y agregamos algunos códigos, para que realice la
búsqueda:
Si se ingresa un código que no este presente en la matriz de búsqueda, devolverá un error del tipo
#N/D

Esto no significa que la formula sea incorrecta necesariamente, simplemente, el valor buscado no
está presente en la matriz.

Hemos revisado la estructura básica de BuscarV, pero como resolvemos casos mas complejos, por
ejemplo, que sean 2 o mas condiciones, o buscar en una tabla cruzada.

BUSCARV con 2 condicionales

Tenemos los siguientes datos representados en la hoja

Vemos que se ha buscado el nombre de Carlos para saber su calificación, la cual es correcta
respecto al primer Carlos Perez, pero hay un segundo Carlos Donoso con una nota diferente, ¿cual
es que quiero buscar efectivamente?

Buscarv solo muestra el primer dato encontrado y no nos da una forma evidente de aplicar un
segundo criterio, por lo que tocara recurrir a un apoyo a la función para casos como este.

Solución con columna auxiliar

Agregaremos una columna después del apellido, y lo que haremos será concatenar (unir) el
nombre con el apellido, creando una cadena única. Con la expresión =B2&C2

Luego modificaremos la zona de búsqueda agregando el apellido y modificaremos la formula de

Buscarv para que utilice la columna auxiliar.

=BUSCARV(H1&H2;D1:E9;2;0)

De este modo podremos buscar con estos 2 criterios.

En la imagen podemos ver el cambio aplicado y el resultado exacto

En caso de 3 o mas condiciones, se aplica el mismo concepto de concatenar las celdas para crear
un dato único para la búsqueda.

BuscarV en tablas cruzadas

Tenemos la siguiente situación representada en la imagen

AL ingresar la ciudad y Tipo de Tarifa, Buscarv debe entregarme el valor correspondiente de la

tabla, por ejemplo, si busco Rancagua y Tarifa C, debe mostrarme 272.

Una solución en este tipo de casos es complementar BuscarV con la función Coincidir

=BUSCARV(H2;A2:E8;COINCIDIR(H3;A2:E2;0);0)

BuscarV inicia la búsqueda de la ciudad ingresada en H2 dentro del rango A2:A8, y cuando
debemos indicar de que columna, se introduce la función Coincidir con el dato en H3, dentro del
rango A2:E2, que devuelve el número de columna que coincide con la Tarifa ingresada. Ambas
búsquedas usan la opción 0 (exacta).

Función BuscarX
Buscar a la izquierda de la columna clave

BuscarV busca en la columna clave la coincidencia del valor buscado y luego puede traer cualquier
valor a la derecha de la columna. Pero que sucede si el dato que queremos traer de vuelta se
encuentra a la izquierda de la columna clave, en la imagen tenemos la siguiente situación:
Se ha ingresado el Rut 300 en la celda J1, y se pide traer el Nombre, Apellido y Sueldo. Los 2
primeros datos se encuentran a la izquierda de la columna del Rut.

La solución a esta problemática es utilizar la nueva función actualizada BUSCARX, que permite
hacer búsquedas y traer datos sin importar su ubicación:

La sintaxis es asi:

=BUSCARX(valor_buscado; matriz_buscada; matriz_devuelta; [si_no_se_encuentra];

[modo_de_coincidencia]; [modo_de_búsqueda])

De manera simple basta con indicar la columna de búsqueda y la columna que traerá el dato de
vuelta. Pero también tiene opciones para indicar que hacer si no se encuentra por ejemplo
indicando un texto (“No existe”) y si la búsqueda debe ser exacta o aproximada (0 o 1). En el modo
búsqueda se puede indicar si busca de arriba hacia abajo, o viceversa)

Nuestra formula quedaría asi para el Nombre:

=BUSCARX(J1;E1:E10;B1:B10;"No existe";0)

En caso de que se ingrese un valor que no esta en la tabla, en la formula ya ha quedado

establecido que responda con la frase “No existe” y asi evitar un error del tipo #N/A
Función BuscarH

Sintaxis: BUSCARH(valor_buscado; matriz_buscar_en; indicador_filas; (ordenado) )

Busca un valor en la fila superior de una tabla o una matriz de valores y devuelve un valor en la
misma columna de una fila especificada en la tabla o matriz. Use BUSCARH cuando los valores de
comparación se encuentren en una fila en la parte superior de una tabla de datos y desee
encontrar información que se halle dentro de un número especificado de filas. Use BUSCARV
cuando los valores de comparación se encuentren en una columna a la izquierda de los datos que
desea encontrar.

En general, ya sabemos que funciona similar a BUSCARV, solo que la búsqueda se realiza en filas.

El nombre BUSCARH, se refiere a una búsqueda horizontal en la matriz

Veamos el siguiente ejemplo: Ingresando el nombre de la ocupación, queremos obtener el resto

de los datos solicitados en la ficha.

En la parte inferior tenemos una tabla con la información de cada ocupación. Esta tabla esta
dispuesta de modo horizontal, por lo que la ocupación podemos encontrarla en la primera fila, y
luego, llevar el dato solicitado contando cuantas filas desde la primera se ubica.

Poe ejemplo, el sueldo lo encontramos en la segunda fila de la tabla, p la categoría se encuentra

en la fila 4 de la tabla.

Al ingresar las fórmulas nos quedaría asi:

La función formulada, solo va cambiando el numero de la fila de donde extraer el dato a mostrar
en la ficha.
Revisaremos un par de ejemplos más complejos para buscar Horizontalmente

En este caso necesitamos identificar la sucursal con Saldo mas alto y con el saldo más bajo,
además queremos saber el nombre de esa sucursal

Entonces, para buscar el nombre usaremos la siguiente formula, para máximo y minimo:

=BUSCARH(MAX(B4:F4);B4:F5;2;0) y =MAX(B4:F4)

=BUSCARH(MIN(B4:F4);B4:F5;2;0) y =MIN(B4:F4)

La función Max o Min se encarga de identificar el valor y asi luego, bajo esta, la fila con el nombre
de la sucursal

Veamos este nuevo caso, aplicado con BuscarH

A partir de identificar el nombre del vendedor y el año, se debe buscar las ventas
correspondientes:

Apicaremos la formula

=BUSCARH(H1;A1:E6;COINCIDIR(H2;A1:A6;0);0)
Funciones Para El Manejo De Bases De Datos En Tablas.

Las bases de datos son sistemas que permiten almacenar información de forma ordenada y
estructurada para su posterior consulta. La mayor parte de las bases de datos actuales son
relacionales, es decir, usan tablas que dividen su contenido en registros y campos.

Seguramente has observado que una Hoja de Excel también divide su contenido en celdas
organizadas por filas y columnas. Es decir, su estructura es similar a la de una tabla y por eso es
una herramienta muy utilizada para la creación de bases de datos.

Por tanto, una base de datos en Excel no es más que una Hoja de Excel en la que se han insertado
datos en forma de registros y campos, tal y como se haría en cualquier base de datos relacional.

Las bases de datos creadas con Excel son una alternativa a las bases de datos en Access, otra de
las herramientas de Microsoft para creación de bases de datos.

Una base de datos organiza la información relacionada en tablas las cuales están compuestas por
columnas que se identifican como Campos y filas que corresponden a los Registros. Una tabla
tendrá un número específico de columnas, pero tendrá cualquier número de filas.

Para trabajar los contenidos de una Base de Datos en Excel existen diversas funciones y
herramientas que nos permitirán consultar los datos de esta, consideraremos:

Filtros Avanzados

Funciones de Base de Datos

Funciones de Calculo

Para las actividades nos basaremos en una tabla común en la que aplicaremos las distintas
herramientas y funciones para conocer su funcionamiento:
La tabla de datos es la siguiente:

Seleccionaremos de la tabla el rango A2:G19 y le asignaremos el nombre “Ventas”

Filtros Avanzados
Un filtro avanzado en Excel es un filtro en el que los criterios se encuentran en otro lado de la hoja
de Excel y cuyo resultado del filtro avanzado podemos llevar a otro lugar de la hoja de Excel o,
filtrar en la misma tabla de los datos. Este tipo de filtrado puede ser útil si queremos seleccionar
sólo una parte de la información en un rango diferente al de nuestra tabla. O ver la tabla con la
información filtrada.

Revisaremos un ejemplo trasladando el resultado a un rango distinto a la tabla de origen.

En un costado de la tabla, o en otra hoja, copiaremos el encabezado de la tabla de la siguiente

manera:

Desde la pestaña datos, en el grupo Ordenar y Filtrar, activaremos la herramienta Avanzadas, la

cual abrirá una ventana para completar:
Debajo de los encabezados de criterio debemos escribir el dato que usaremos para filtrar, en el
ejemplo indicaremos Que el Vendedor sea Juan y el Canal que sea Web.

En el recuadro Acción debemos indicar si filtraremos en la misma tabla o si queremos hacer una
copia de los datos en otro lugar. Seleccionaremos copia

En el rango de Lista se indica el rango, o nombre de rango de la tabla de datos (recomendado) , en

nuestro ejemplo será Ventas. ($A$1:$G$19)

El rango de criterios debe incluir los encabezados y al menos una fila hacia abajo, en nuestro
ejemplo será I2:O3

En el recuadro Copiar a: se debe indicar la celda inicial de donde se copiarán los datos, en el
ejemplo sería $I$8.

Al aceptar, el resultado será como se muestra en la imagen

Se pueden, por tanto, agregar bajo los encabezados de criterio todo lo que se desea filtrar. Se
debe entender que cada criterio puesto en la misma fila se convierte en obligatorio tipo Y, en el
ejemplo, solo trae los registros de Juan y Web.

Si se agregan mas de un criterio a un campo, estos son del tipo O, por ejemplo veamos este caso
agregando el vendedor Luis debajo de Juan

Al abrir la ventana de aplicación de filtro avanzado se debe ampliar el rango de criterios para que
pueda leer el segundo dato. Es decir, el rango debe quedar como $I$2:$O$4
 Debemos notar que para Luis no solo trajo las ventas Web, también
muestra las ventas por Local.

Esto es porque en el criterio de Canal para Luis no se indicó nada, por lo

que no aplica ningún filtro a Luis y muestra todas sus ventas.

Entonces, si quiero aplicar igual filtro, debo repetir al lado de Luis el dato
Web, para lograr filtrar las ventas para ese canal especifico.

Así, al aplicar otra vez el filtro avanzado nos entregará la siguiente lista

Los criterios pueden ser únicos o múltiples como hemos visto en los ejemplos anteriores.

En el caso de valores numéricos puede usarse criterios usando los

signos del tipo:

>100.000 <=100.000

Si aplicamos este criterio, ejecutamos el filtro avanzado y obtendremos:

El filtro aplica a las ventas de Juan menores a $100.000, pero Luis no es afectado por el filtro, ya
que no se incluyó ningún criterio en su fila.

Recuerda, los criterios en la misma fila se hacen obligatorios, pero los que se anotan en columna
se hacen flexible ya que permite uno o el otro.
Funciones de Base de Datos en Excel
Las funciones de base de datos de Excel nos permiten realizar algunos cálculos a partir de los datos
de la tabla.

Para aplicar estas funciones se debe contar con un rango de criterios que la función pueda leer

La sintaxis de las funciones de Base de Datos de Excel es:

BDfuncion(base_de_datos;nombre_de_campo;criterios)

Donde:

Base_de_datos: es la tabla donde están los datos (se selecciona en su totalidad)

Nombre_de:campo: es el campo sobre el que se realizara la acción de la funcion (contar, sumar,

etc.)

Criterios: son los criterios o condiciones que se aplicaran. Debe cointar al menos con un
encabezado idéntico al de la tabla.

El campo de criterios debe tener como encabezado el mismo nombre que tenga el campo en la
base de datos, y debajo de este se pueden anotar los criterios requeridos.

Recordemos que la base de datos a utilizar la hemos nombrado como Ventas.

Función Bdcontar
Queremos contar cuantas transacciones de venta se hicieron para el producto Impresora,
entonces, organizaremos en un lugar de nuestra hoja la siguiente forma:

=BDCONTAR(Ventas;C1;I5:I6)

Donde: Ventas es el nombre de rango asignado a la tabla; C1 corresponde al campo sobre el que
se hará la cuenta; I5:I6 es el rango donde se encuentra el criterio aplicado.

Si quisiera contar cuantas transacciones de Impresoras en el canal Local se hicieron, entonces

haríamos la siguiente forma:

=BDCONTAR(Ventas;C1;I5:J6)

Podemos darnos cuenta que debemos tener los mismos encabezados de la tabla para poder
aplicar criterios en alguna parte de nuestra hoja.
Función BDpromedio
Queremos obtener el promedio de ventas realizados por el Canal Web

Formula: =BDPROMEDIO(Ventas;E1;I9:I10)

Ahora, si queremos obtener el promedio de las ventas hechas en LOCAL mayores a 100.000,
tendríamos que organizar lo siguiente:

=BDPROMEDIO(Ventas;E1;I9:J10)

Función Bdsuma
También podemos sumar desde la base de datos valores, por ejemplo, si quisiera saber que
cantidad de pantallas se han vendido, organizaremos lo siguiente:

=BDSUMA(Ventas;C1;I13:I14)

Ahora, si quisiera sumar la cantidad de productos vendidos para Pantalla e Impresora, tendríamos
que organizar algo asi:

=BDSUMA(Ventas;C1;I13:I15)

Como hemos visto, las funciones de base de datos son simples de usar ya que tienen una sintaxis
común, solo debemos identificar cual queremos usar.
Funciones .Conjunto
Este grupo de funciones nos permiten realizar algunas operaciones sobre los datos de una tabla
aplicando 2 o mas criterios simultáneamente. A diferencia de las funciones anteriores no
requieren obligatoriamente un rango de criterios, pero si puede aprovecharse esa capacidad para
facilitar su construcción.

Son prácticas para la construcción de tablas de resumen donde pode mos aprovechar su poder.

Revisaremos 3 de las mas utilizadas, siempre basados en la tabla Base de Datos Ventas.

Función Contar.Si.Conjunto
Sintaxis

CONTAR.SI.CONJUNTO(rango_criterios1; criterios1; [rango_criterios2; criterios2];…)

La función CONTAR.SI.CONJUNTO aplica criterios a las celdas de varios rangos y cuenta el número
de veces que se cumplen todos los criterios.

Construiremos una tabla que resuma la cantidad de transacciones que realizó cada uno de los
vendedores de cada producto, según este modelo:

La función Contar.Si.Conjunto nos permitirá realizar esta acción:

=CONTAR.SI.CONJUNTO($F$2:$F$19;$J3;$B$2:$B$19;K$2)

El primer rango $F$2:$F$19 corresponde a la columna de Vendedor, la referencia $J3 indica el

criterio del vendedor, el segundo rango corresponde a la columna Producto y la referencia K$2
indica el criterio del producto. Esta fórmula aplicada en la primera celda K3 puede ser luego
copiada al resto de las celdas y auto completar el conteo.

El resultado se muestra a continuación

Función Promedio.Si.Conjunto

Sintaxis

PROMEDIO.SI.CONJUNTO(rango_promedio; rango_criterio1; criterio1; [rango_criterio2; criterio2];

...)

Devuelve el promedio (media aritmética) de todas las celdas que cumplen múltiples criterios.

El rango Promedio se refiere a los valores que serán promediados, aplicando los rangos de citerios.

Aplicaremos la misma tabla anterior para obtener el promedio de ventas por vendedor y
producto.

=PROMEDIO.SI.CONJUNTO($E$2:$E$19;$F$2:$F$19;$J10;$B$2:$B$19;K$9)

El resultado es el siguiente:

Aquellos vendedores que no vendieron algunos productos nos muestra un error #DIV/0! Que
significa que se está realizando una división en 0.

Para evitar esa forma del error, podemos complementar la formula usando la función SI.ERROR()
que puede dar una alternativa al error.

Sintaxis:

SI.ERROR(valor;valor_si_error)

Entonces, nuestra fórmula quedara asi:

=SI.ERROR(PROMEDIO.SI.CONJUNTO($E$2:$E$19;$F$2:$F$19;$J10;$B$2:$B$19;K$9);"")

Y la tabla resumen ahora se ve asi:

Función Sumar.Si.Conjunto

Sintaxis

SUMAR.SI.CONJUNTO(rango_suma; rango_criterios1; criterios1; [rango_criterios2; criterios2];...)

La función SUMAR.SI.CONJUNTO, una de las funciones matemáticas y trigonométricas, agrega

todos los argumentos que cumplen varios criterios

El primer argumento es para indicar el rango a sumar, luego se suceden rango para aplicar criterio
y criterio a usar.

Aplicaremos la misma tabla anterior para obtener la suma de ventas por vendedor y producto.

=SUMAR.SI.CONJUNTO($E$2:$E$19;$F$2:$F$19;$J17;$B$2:$B$19;K$16)

Las tablas anteriores las hemos utilizado pensando en mostrar una aplicación. Ahora, usaremos
otra presentación que nos permita usar los mismas funciones, pero esta vez con la posibilidad de
seleccionar el vendedor y el producto que queremos saber cantidad de transacciones, Suma de
ventas y Promedio de Ventas.

Usaremos la validacion de datos para crear una lista de selección de vendedor y producto. Y en
otras celdas obtendremos los resultados de las funciones de calculo.

Usaremos el siguiente modelo:

seleccionaremos la celda K24 y usaremos la herramienta de Validacion de Datos, creando una lista
seleccionable con los nombres de los vendedores, cada nombre se separa con punto y coma (;)

Datos / Herramientas de Datos / Validacion de Datos

Aceptar
Asi, ya podemos seleccionar el vendedor

Repetimos los mismos pasos anteriores ahora para Producto, esta vez seleccionando la celda K25.

Activamos la validacion de datos, permitir Lista y en el origen ingresamos los nombres de los
productos separando cada uno con punto y coma (;).

Aceptar

Ahora ya podemos seleccionar el producto

Entonces, ahora nos queda nadas más seleccionar vendedor y producto y aplicar las fórmulas de
cálculo en las celdas correspondientes, que serían:

N° Transacciones: =CONTAR.SI.CONJUNTO(F2:F19;K24;B2:B19;K25)

Suma Ventas: =SUMAR.SI.CONJUNTO(E2:E19;F2:F19;K24;B2:B19;K25)

Promedio Ventas: =PROMEDIO.SI.CONJUNTO(E2:E19;F2:F19;K24;B2:B19;K25)

Si cambiamos el Vendedor o Producto nos entrega de inmediato los valores para los nuevos
criterios aplicados.

La aplicación de las formulas y el diseño en nuestra hoja de calculo debe facilitarnos comprender
el resultado de los datos.
Tablas Dinámicas Y Ejecutar Datos Externos.
Una tabla dinámica es una herramienta avanzada para calcular, resumir y analizar datos que le
permite ver comparaciones, patrones y tendencias en ellos. Las tablas dinámicas funcionan
de forma un poco distinta dependiendo de la plataforma que use para ejecutar Excel.

Esta puede obtener datos desde la misma hoja de trabajo actual pero también es posible acceder
a datos externos, desde otro archivo y traerlos a una tabla dinámica.

Poder analizar todos los datos puede ayudarle a tomar mejores decisiones empresariales. Pero a
veces resulta difícil saber por dónde empezar, especialmente cuando tiene una gran cantidad de
datos almacenados fuera de Excel, como en una base de datos de Microsoft Access o Microsoft
SQL Server, o en un archivo de cubo de procesamiento analítico en línea (OLAP).

Revisaremos en esta sección algunas características del uso de la Tabla dinámica asi como sus
conexiones.

Ejecutar Tablas Dinámicas Con Query Desde Otros Libros

Utilizaremos como fuente de datos un archivo Excel al que nos conectaremos para tomar sus
datos y traerlos como tabla dinámica usando una conexión externa.

El archivo se nombra BD Ventas.xlsx. Este archivo no se encuentra abierto, al conectarnos

accederemos a sus datos a través de la tabla dinámica para hacer el análisis de datos que
necesitamos.

El archivo de origen tiene la siguiente forma:

Se compone de una tabla con 12 columnas (Campos) y 4.400 filas (Registros)

Entonces, abrimos un nuevo libro en blanco desde donde realizaremos la conexión, siguiendo los
siguientes pasos:

1° Desde la pestaña Insertar, desplegamos Tabla dinámica y clic en Desde Datos externos de
origen.
2° en la ventana seleccionaremos donde colocar la tabla dinámica, puede ser en una nueva hoja de
cálculo o una existente y luego clic en el botón Elegir conexión…:

3° se abre la venta de Conexiones existentes donde se puede conectar con cualquiera que se
hubiese usado previamente. En este ejemplo será la primera conexión asi que tendremos que
establecerla previamente, dando clic en el botón Examinar en busca de más…

4° Tras esto se abrirá la ventana de Windows para buscar el archivo en la ubicación local, una vez
localizado lo seleccionamos y abrimos este.

5° Al abrir nos mostrara la ventana para seleccionar la tabla que usaremos como origen, en este
caso es una sola asi que la seleccionamos y Aceptar
6° nos mostrará la venta inicial donde ya se ha cargado el nombre de la conexión a realizar, y
Aceptar.

7° Ya tenemos la Tabla dinámica en nuestra hoja de calculo conectada de manera externa al

archivo BD Ventas.

Opciones Avanzadas De Td
Trabajar con tablas dinámicas nos permite generar informes o reportes a modo de resumen de los
datos que manejamos en un archivo de gran extensión. No revisaremos la construcción de la tabla
dinámica ya que es un concepto conocido previamente, por lo que nos enfocaremos en el uso de
algunas herramientas avanzadas al momento de analizar nuestros datos.

Revisaremos 5 usos avanzados de la tabla dinámica.

• Segmentación de Datos
• Escalas de Tiempo
• Formato Tabular
• Campo Calculado
• Tablas dinámicas recomendadas.

Comencemos.
Segmentación de Datos

Segmentación de Datos son herramientas de punto y clics para refinar los datos incluidos en la
Tabla Dinámica de Excel. Inserta una segmentación y puedes cambiar fácilmente los datos que
están incluidos en tu Tabla Dinámica.

Su comportamiento es similar a un filtro de datos.

Consideremos la siguiente tabla dinámica, creada a partir de la conexión vista anteriormente:

Para activarlo bastará con dar clic dentro de la Tabla Dinámica, en la Pestaña Analizar tabla
dinámica seleccionar el grupo Filtrar y clic en Insertar Segmentación de datos.

Entonces nos mostrará esta ventana para seleccionar que datos queremos utilizar para filtrar, en
el ejemplo seleccionaremos Segmento y Vendedor. Al aceptar aparecen los
segmentadores asi:

Ahora solo nos queda organizarlos y acomodarlos individualmente

arrastrándolos y ajustando sus tamaños. Algunos segmentadores pueden
tener muchos elementos entonces podemos ajustar para que se muestren
los datos en columnas. Por ejemplo, el Segmentador Vendedor.

Ajuste de la forma del Segmentador.

Seleccionamos el Segmentador Vendedor y se activa la pestaña Segmentación.

En el grupo Botones, casilla Columnas modificaremos a 3 el valor y luego ajustaremos el tamaño
del Segmentador

Entonces, desde ahora al dar clic en una de las opciones del Segmentador la tabla dinámica se
filtrará automáticamente mostrando la información según el filtro aplicado.

Si doy clic en el segmento Empresa, nos mostrara los valores para asociados a este.

Si seleccionamos a un vendedor, por ejemplo, Adriana Bossio, quedará asi:

Usando esta herramienta podemos organizar un tablero de control (dashboard) para administrar
los datos y como analizarlos.

Al desarrollar reportes de Tablas Dinámicas que serán usados por otros, añadir segmentación de
datos puede ayudar al usuario final personalizar el reporte a su gusto.

Conexiones de Filtro

Esta herramienta administra los filtros a los que esta conectada la tabla dinámica. Un filtro puede
afectar a una tabla dinámica en exclusiva o a varias simultáneamente.
En el mismo grupo de Filtrar se encuentra Conexiones a Filtro. Al activar nos muestra una ventana
con las tablas dinámicas y los filtros asociados (segmentadores). SE pueden activar o desactivar las
conexiones logrando asi que un Segmentador, por ejemplo, no afecte a una tabla pero si a otra.

Escalas de Tiempo

Las Escalas de Tiempo son un tipo especial de segmentación, usadas para retocar los datos
incluidos como parte de tus datos de Tabla Dinámica. Si tus datos incluyen fechas, necesitas
probar Escalas de Tiempo como una manera de seleccionar datos de un período de tiempo.

Para insertar una Escala de tiempo, seguiremos los mismos pasos

del Segmentador, ya que se encuentra en la misma ubicación.

Entonces, seleccionamos la tabla dinámica, pestaña Analizar tabla

dinámica, grupo Filtrar, clic en Insertar escala de tiempo.

Nos mostrara la ventana de selección de datos que contienen fechas, en nuestro modelo el campo
FechaFactura

Con esta herramienta podemos además ver la información para periodos de fecha específicos. En
la esquina derecha se puede cambiar la escala de tiempo, por defecto mostrará Meses, pero al
desplegar podemos escoger la que sea mas adecuada a la forma de análisis que queremos ver. La
barra de desplazamiento nos permite movernos en la escala de tiempo de un extremo a otro e
intermedios

En la imagen se ve aplicada la escala de tiempo al mes de Agosto 2014 para el segmento Persona,
y la tabla dinámica entrega la información para tales datos.
Al seleccionar la escala de tiempo, se activa la pestaña del mismo nombre donde podremos aplicar
todas las posibilidades de ajuste al aspecto de esta.

Formato Tabular

La vista por defecto de la Tabla Dinámica de Excel parece una clase de cascada; a medida que
arrastras más niveles de campos en las filas, Excel crea más "capas" en los datos.

El problema es que las Tablas Dinámicas en vista estándar son difíciles para escribir formulas. Si
tienes tus datos en una Tabla Dinámica, pero quieres verla más como una hoja de cálculo
tradicional, debes usar un formato tabular para tu Tabla Dinámica.

Quitaremos cualquier filtro o Segmentador de datos aplicado y modificaremos la tabla dinámica

de la siguiente manera para ver su forma por defecto cuando arrastramos varios datos.

Pondremos en Fila Vendedor, Segmento y Canal. Y en valores MontoFactura

Podemos observar lo comentando antes, los datos se muestran en cascada en la tabla.

Modificaremos su presentación utilizando el ajuste a Formato Tabular.

Para esto, seleccionamos la tabla dinámica, se activará la pestaña Diseño, en el grupo diseño
damos clic en Diseño de informe y Mostrar en formato tabular, y podemos ver el cambio de la
tabla.

Cuando hay varios datos en fila puede ser más fácil de usar un formato tabular en Excel la mayoría
de tiempo. Se mira más como una vista de hoja de cálculo estándar y se siente más fácil escribir
formulas y trabajar con los datos dentro de la misma. Podría tomar esta vista y pegarla en una
nueva pestaña más fácilmente.

Campo Calculado

El Campo Calculado es una manera de añadir una columna a tu Tabla Dinámica que no está en tus
datos originales. Puedes usar una operación matemática estándar para crear un campo
enteramente nuevo con el cual trabajar. Toma dos columnas existentes y usa matemática para
crear nuevas.

Digamos que tenemos el MontoFactura en una hoja de cálculo. Y queremos que al segmento
Personas le aplique un descuento del 5% y al segmento Empresas un 3%. Este es el momento
perfecto para usar un campo calculado para realizar la clasificación de todas las ventas.

Crearemos un nuevo modelo de tabla dinámica que muestre inicialmente el Segmento y Canal con
el MontoFactura. Con formato tabular. Debe lucir asi:

Para comenzar, hacemos clic dentro de una Tabla Dinámica, se activará la pestaña Analizar tabla
dinámica. En el grupo Cálculos, selecciona Campos, Elementos y Conjuntos, y luego clic en Campo
Calculado.

Nos mostrará la ventana de preparación:

En la casilla Nombre identificamos el nuevo campo, Descuento.

Al indicar el campo que forma parte de la formula lo seleccionamos con un doble clic desde la
ventana Campos y este se agregará.

= MontoFactura- Descuento=SI( Segmento="Persona";5%;3%)* MontoFactura

Al Aceptar se agregará a nuestra tabla el dato creado como campo calculado. Y ya tenemos una
nueva información para el análisis de nuestros datos.

Repetimos los pasos anteriores y crearemos un nuevo campo calculado que nombraremos “Monto
final”, con la formula:

= MontoFactura- Descuento

Al Aceptar se agregará a la tabla dinámica, que nos debe quedar asi:

Entonces, ahora podemos ver más información gracias a los campos calculados.

La aplicación de cálculos puede ser cualquiera que se pueda obtener a partir de los campos
existentes.

Tablas Dinámicas Recomendadas

La característica de Tablas Dinámicas Recomendadas es tan buena que se siente como hacer
trampa. En vez de gastar tiempo arrastrando y soltando tus campos, podemos sacar ventaja
comenzando con una de las configuraciones Recomendadas.

Esta característica es tan fácil de usar que no hay mucho que decir. Puedes usarla para hacer
Tablas Dinámicas avanzadas en Excel rápidamente. Simplemente resalta tus datos, busca la
pestaña Insertar en la cinta de Excel, y escoge Tablas
Dinámicas Recomendadas.
La ventana emergente incluye una variedad de opciones para crear una Tabla Dinámica en tus
datos originales. Da clic a través de las miniaturas en el lado izquierdo de la ventana para ver las
opciones de Tabla Dinámica Recomendada que Excel generó.

Para este ejemplo, seleccionamos la sugerencia Suma MontoFactura, Aceptar y nos entrega una
tabla dinámica armada que bien nos puede servir de base o usarla tal cual.

Y ya podríamos agregarle segmentadores de datos, escala de tiempo, modificar su formato, etc.

Aunque esta es una característica avanzada en la que no muchos usuarios piensan, es también una
herramienta genial para comenzar a usar Tablas Dinámicas. No hay nada que te detenga de
modificar una Tabla Dinámica cambiando los campos por tu propia cuenta, pero esto es cuestión
de ahorrar tiempo.

También lo bueno de esta característica como una manera de explorar datos. Si no sabemos lo
que buscamos para cuando comenzamos a explorar datos, las Tablas Dinámicas Recomendadas de
Excel son a veces más perspicaces de lo que podíamos imaginar.

Las herramientas de uso avanzado entonces nos permiten administrar y dar mas valor a la tabla
dinámica facilitando el análisis que entregan en su informe.

Crear, cargar o editar una consulta en Excel (Power Query)

Power Query ofrece varias formas de crear y cargar consultas de Power en el libro. También puede
establecer la configuración de carga de consulta predeterminada en la ventana Opciones
de consulta.

¿Qué es PowerQuery?

Power Query es un motor de transformación y preparación de datos. Power Query viene con una
interfaz gráfica para obtener datos de fuentes y un editor de Power Query para aplicar
transformaciones. Debido a que el motor está disponible en muchos productos y servicios, el
destino donde se almacenarán los datos depende de dónde se haya utilizado Power Query. Con
Power Query, puede realizar el procesamiento de datos de extracción, transformación y carga
(ETL).

Para acceder a la interfaz de Power Query en Excel primeramente se deben obtener los datos
desde los que se creara la consulta a la cual acceder.

Crear Nueva Consulta.

Puedes crear una consulta a partir de datos importados o crear una consulta en blanco.

Crear una consulta a partir de datos importados. Esta es la forma más común de crear una
consulta.

Para crear la consulta seleccionaremos la pestaña Datos y en el grupo Obtener y Transformar

datos damos clic en Obtener Datos.
También puedes acceder rápidamente usando los iconos al costado que permiten

• Importar Datos de Texto/CSV

• Desde una imagen
• De la Web
• Fuentes Recientes
• De una tabla o rango
• Obtener datos con una conexión existente

Entonces, al dar clic en Obtener datos nos mostrara una ventana donde podemos selecciones de
que tipo de origen obtendremos los datos. Por ejemplo, aca seleccionamos De un archivo. Que
incluye archivos de Excel, de texto, XML, JSON, PDF, de una carpeta.

Seleccionaremos de texto/CSV y nos llevara a ubicar la ubicación del archivo para importarlo.
Usaremos un archivo de texto que se llama MiniMarket_xtx, lo seleccionamos y clic en Importar

Nos mostrara una vista previa del contenido del archivo que ha organizado y en la parte inferior
nos mostrara 3 opciones de acción a realizar.

Cargar – Transformar Datos - Cancelar

Cargar: Importa los datos a nuestra hoja de Excel dejando establecida una conexión al origen.

Transformar Datos: abre el Editor de Power Query desde donde podremos realizar algunas tareas
de transformación de los datos.

Cancelar: deja sin efecto la importación de datos.

Por ahora, clic en Cargar.

Los datos quedan cargados en nuestra hoja de Excel. También en el costado derecho podemos ver
la información de la conexión de la consulta.

Al examinar los datos, podemos ver que hay algunas inconsistencias como los encabezados de
columnas y otros detalles que podemos mejorar desde el Editor de Power Query sin afectar al
archivo de origen.

Saber en qué entorno se encuentra

Power Query está bien integrado en la interfaz de usuario de Excel, especialmente cuando importa
datos, trabaja con conexiones y edita tablas dinámicas, tablas Excel y rangos con nombre. Para
evitar confusiones, es importante saber en qué entorno se encuentra actualmente, Excel o Power
Query, en cualquier momento.
Podemos notarlo al dar clic en una celda de la tabla de datos. Y podremos ver que en el menú se
habilitan un par de pestañas Diseño de tabla y Consulta

Esto nos indica que estamos en entorno de Query.

En cambio, si hacemos un clic en una celda en la hoja de cálculo, estas pestañas ya no se verán,
con lo podemos tener certeza que estamos en el ambiente de Excel
Trabajo Con Ms-Query

Para iniciar Power Query, podríamos haber comenzado al momento de importar los datos usando
el botón Transformar Datos mencionado antes.

Pero en este caso, nos hemos percatado que hay detalles que corregir y ya hemos importado los
datos.

Procederemos de la siguiente manera:

Clic en cualquier celda de la tabla importada / clic en pestaña Consulta / clic en Editar

Esto abrirá el editor de Power Query mostrándonos la siguiente pantalla

Debemos reconocer las partes de esta pantalla:

En la parte superior tenemos el menú con la cinta de opciones.

En el costado izquierdo el bloque de Consultas, donde se muestran cada una de las consultas del
modelo de datos actual

Al centro se encuentra la vista de datos de la tabla. Podemos ver el contenido y alguna

información adicional de cada columna, como el tipo de dato, y estado de los datos.

Sobre la vista de tabla, se muestra una barra de formulas que mostrara las expresiones aplicadas
de las acciones de transformación en lenguaje M. Este es el lenguaje que usa Power Query para
ejecutar las tareas que le solicitamos.

A la derecha, esta el bloque Configuración de la consulta donde podemos apreciar el nombre de la

tabla y el recuadro Pasos aplicados, que es la historia de acciones y transformaciones que se
realicen.

Revisaremos algunas tareas comunes de realizar en Power Query y los pasos a aplicar.
Transformaciones sobre la tabla

En la cinta de opciones encontramos muchas acciones para realizar transformaciones a los datos,
muchas de estas también están disponibles de manera rápida dando un clic sobre el nombre de la
columna.

Asi también en el botón desplegable al costado derecho de cada columna podemos acceder a
filtros y ordenamiento de los datos

Eliminando Filas
Eliminaremos las 2 primeras filas ya que no entregan información relevante y además, no
permiten identificar los encabezados correctamente.

En menú inicio identificamos el icono Quitar filas /Quitar filas Superiores

Se abrirá la siguiente ventana en la que le indicaremos que sean 2 las

filas, y Aceptar
Nuestros datos ahora se ven asi:

Usar Primera fila como encabezados

Ahora ajustaremos la primera fila para que se convierta en los encabezados de la tabla. Esto lo
haremos dando clic al botón Usar primera fila como encabezado. Este se encuentra disponible en
la pestaña Inicio y también en la pestaña Transformar.

El resultado nos mostrara nuestra tabla asi:

Separar datos
La segunda columna de nuestra tabla muestra un conjunto de datos unidos, tal como un código de
país, nombre del cliente, el país y la ciudad. Estos datos deberían poder mostrarse separadamente
para su uso adecuado.

Utilizaremos algunas herramientas que nos permiten efectuar estas transformaciones

Dividir Columna

Seleccionaremos la columna con un clic en el encabezado y luego

en la pestaña Inicio hacemos clic en el botón Dividir Columna.

Tras esto nos mostrara una ventana en la que nos preguntara

cual es el delimitador a considerar.

Un delimitador es un carácter que genera una separación de

datos. Por ejemplo un espacio, un guion, o similares.
 la herramienta ha detectado el uso del guión en esta columna, por
lo cual mantendremos la indicación y le indicaremos Aceptar.

Entonces, nuestra columna se separa de la siguiente manera por

ese delimitador:

Podemos apreciar el cambio.

Repetiremos los mismos pasos para separar la columna donde están mezclados el Cliente y el país.

Recuerda primero seleccionar la columna, el delimitador en este caso será el /.

Si ejecutas bien la acción te quedara asi:

Muy bien, entonces ahora nada mas nos queda ajustar los nombres de encabezados para cerrar
esta parte.

Los nombraremos como CodPais, Cliente, País y ciudad respectivamente. Entonces debe quedar
asi:

Quitar columnas
Al final de nuestra tabla hay 2 columnas que no entregan datos validos a la tabla,

las quitaremos. Para esto las seleccionamos primero, para seleccionar ambas puedes usar la tecla
control mientras das clic a estas para seleccionar varias.

En la pestaña inicio encontramos un botón quitar columnas que podemos usar directamente y
quitar las seleccionadas.
Tras esto ya no mostrara dichas columnas.

Tipos de Datos en Power Query

Al observar cada una de las columnas podemos identificar el tipo de dato que se ha detectado a
través del icono. Si miramos las primeras columnas observamos

La columna Fecha Venta es del tipo fecha

La columna CodPais es de tipo número entero

La columna Cliente es de tipo texto.

Según el tipo de dato podemos saber que acciones podemos realizar con estos.

Podemos identificar todos los tipos de datos que acepta Power Query
con un clic en uno de los iconos de tipo de cualquier columna y nos
desplegara la venta de tipo de datos:

Esto nos permite si fuera oportuno cambiar el tipo de dato de una

columna simplemente seleccionado el nuevo.

Cuando se hace esta acción en algunos casos nos alertará y pedirá

confirmar el cambio antes de proceder. En otros lo hará de inmediato.

Por ejemplo, si quisiera cambiar el tipo de dato de la columna CodPais de Numero entero a texto,
seleccionamos en la ventana el tipo Texto.

Todos los cambios o transformaciones que realizamos en Power

Query quedan registrados en la Configuración de la consulta, en la
ventana Pasos Aplicados.

Esto nos permite deshacer cualquier cambio o también agregar

cambios entre los que ya se han ejecutado.
Para quitar un paso, basta con dar clic en la x al costado izquierdo del paso realizado. Este se
elimina y la tabla recupera su condición precia a ese paso.

Hay que tener cuida al quitar pasos entre otros pasos, ya que podría afectar cambios posteriores.

Transformaciones en contexto de columna

Al hacer clic derecho sobre una columna nos mostrara una

venta de contexto con muchas opciones para realizar
transformaciones a los datos.

Por ejemplo, al hacer clic sobre la columna Cliente.

Luego Transformar / Mayúsculas

Todos los datos de la columna se convierten a mayúsculas.

Reemplazar Valores.
En la columna ciudad el nombre Córdova aparece escrito como C¢rdova, esto puede haber
ocurrido al no reconocer el acento al importar los datos, entonces procedemos similar al caso
anterior.

Seleccionamos la columna ciudad con un clic derecho, luego Reemplazar valores…

Nos mostrará una ventana donde debemos indicar que buscar y con que reemplazar, ingresamos
la palabra mal escrita y el reemplazo como se muestra, luego Aceptar.

Los datos se reemplazan y ahora están bien escritos.

Muchas de las opciones de transformaciones de esta ventana contextual se encuentran en la cinta
de opciones, por lo que es una vía rápida de acceder a los cambios mas habituales de realizar
sobre los datos.

Agregar Columna Personalizada

A veces, los datos no entregan algún dato útil que si puede obtenerse de los datos existentes. En
nuestro modelo no hay un resultado de la venta que me diga el producto entre la cantidad y el
Precio Unidad.

Podemos agregar una columna con dicho

calculo. Procederemos asi.

Seleccionaremos la pestaña Agregar columna /

Columna Personalizada

Esto mostrará una ventana en la que le indicaremos el nombre que tendrá la columna (Venta
Total) y la fórmula que hará el cálculo que se aplicará a cada fila de la tabla.

=[Cantidad]*[Precio Unidad]

Para armar la fórmula bastará con hacer

doble clic sobre el nombre del campo a
usar (igual que un campo calculado de
tabla dinámica). El nombre del campo
siempre se indicará entre corchetes [xx]

En la parte inferior podremos ver una

verificación en verde que nos dice que
no hay errores en la sintaxis de la
formula ingresada.

Le indicamos Aceptar.

En la tabla se agrega la columna al final de esta:

Modificaremos el tipo de dato para que quede como Numero entero.

Cerrar y cargar.
Cuando se han finalizado los cambios requeridos, usaremos el botón Cerrar y cargar, que guardará
las transformaciones realizadas y cargará nuevamente los datos en la tabla de la hoja Excel.

Volvemos a Excel con nuestra hoja ya transformada gracias a Power Query

Podemos regresar a Power Query en cualquier momento si necesitamos una nueva

transformación.

Conclusión

La experiencia de usuario de Power Query se proporciona a través de la interfaz de usuario de

Power Query Editor. El objetivo de esta interfaz es ayudarlo a aplicar las transformaciones que
necesita simplemente interactuando con un conjunto de cintas, menús, botones y otros
componentes interactivos fáciles de usar.

Power Query Editor es la experiencia principal de preparación de datos, donde puede conectarse a
una amplia gama de fuentes de datos y aplicar cientos de transformaciones de datos diferentes al
obtener una vista previa de los datos y seleccionar transformaciones desde la interfaz de
usuario. Estas capacidades de transformación de datos son comunes a todas las fuentes de datos,
independientemente de las limitaciones subyacentes de la fuente de datos.

Cuando crea un nuevo paso de transformación interactuando con los componentes de la interfaz
de Power Query, Power Query crea automáticamente el código M necesario para realizar la
transformación, por lo que no necesita escribir ningún código.
Unidad 3. Macros

Si hay tareas de Microsoft Excel que realiza reiteradamente, puede grabar una macro para
automatizarlas. Una macro es una acción o un conjunto de acciones que se puede ejecutar todas
las veces que desee. Cuando se crea una macro, se graban los clics del mouse y las pulsaciones de
las teclas. Después de crear una macro, puede modificarla para realizar cambios menores en su
funcionamiento.

Imagine que todos los meses crea un informe para el gerente del departamento contable. Desea
que los nombres de los clientes con cuentas vencidas aparezcan en rojo y en negrita. Puede crear y
luego ejecutar una macro que aplique rápidamente estos cambios de formato en las celdas
seleccionadas.

¿Qué Son Las Macros?

Una Macro en Excel es una instrucción automática que se ejecuta para un procedimiento o
acción personalizada y que se pueden potenciar entre sí. Es decir, que una Macro puede invocar a
otras para lograr operaciones o soluciones mucho más robustas.

En concreto, una macro se trata de una aplicación programada mediante un lenguaje de

programación, conocido como Visual Basic. Sin embargo, los usuarios pueden desarrollar una sin
necesidad de codificar, propiamente, el programa.

Una Macro permite desarrollar varias tareas mediante un solo comando, logrando automatizar
aquellos procesos que, aunque son necesarios para cumplir nuestros objetivos específicos, pueden
ser automatizados.

¿Para qué sirve una Macro en Excel?

Imagina que, al trabajar con Excel, tienes que ejecutar la misma acción, varias veces, de forma
mecánica, lo cual te puede llevar bastante tiempo. Con las Macros puedes automatizar todo
ello, de forma que se realicen, en orden, todas las acciones que sueles hacer de manera
repetitiva.

Al crear una Macro en Excel podrás grabar las acciones que realices en tu trabajo de forma
repetitiva, las cuales podrás ejecutar, nuevamente, en un solo paso. Además, con Visual Basic,
podrás crear las aplicaciones o realizar ajustes de las Macros que se tengan disponibles.

Resumiendo un poco, las Macros en Excel y Visual Basic nos ayudan a automatizar tareas como:
copiar o llevar información a otros campos, enviar un correo, crear un módulo o sistema de
inventarios, una plantilla de facturación, una plantilla contable o tesorería, etc. Todo esto lo
puedes crear con la ayuda del lenguaje de programación de Excel. Así mismo, podemos crear
funciones o fórmulas personalizadas, comandos y aplicaciones más robustas con soluciones más
precisas.

La forma mas simple de crear una macro es utilizando la grabadora de macros y luego podemos
mejorar las instrucciones desde el editor de Visual Basic donde podemos modificar el código de
instrucciones.
Las macros y las herramientas de VBA se pueden encontrar en la pestaña Programador, pero está
oculta de forma predeterminada por lo que el primer paso consiste en habilitarla. Entonces, si no
aparece esta pestaña en tu menú la activaremos.

Para activar esta sección en el menú seguiremos los siguientes pasos.

Archivo / Opciones / Personalizar cinta de opciones

Bastará con activar la casilla Programador con un clic y luego Aceptar

Ahora en nuestro menú se vera la pestaña Programador con su cinta de opciones habilitada.

La grabadora de macros predeterminada se encuentra en el menú Vista / Macros

Grabador De Macros
La grabadora de macros es una herramienta que nos permite grabar todo el proceso que
realizamos en pantalla hasta que la pausemos. Todas las acciones que llevemos a cabo se traducen
a Visual Basic simultáneamente.

De esta manera, al grabar una acción, esta se puede ejecutar nuevamente mediante un atajo, sin
tener que realizar todos los pasos manualmente ni de programar en el editor de Visual Basic.

Los pasos básicos son:

Desde la pestaña Programador, grupo Código, Iniciar Grabar Macro

En la ventana asigne un nombre a la macro, por defecto la llamará Macro1.

También se puede asignar una letra de ejecución rápida juntamente con la tecla Ctrl.

En la descripción puede indicar que uso u objetivo tiene la macro.

Aceptar para iniciar la grabación.

A partir de este momento, todos los pasos o acciones que realizas se grabaran secuencialmente,
por lo que recomendable es tener una idea clara de lo que deseas hacer para evitar pasos
innecesarios o repetitivos.

Una vez finalizada la acción se da clic en Detener grabación y la macro quedara finalizada.

Uso de la macro

Para dar uso a la macro podemos tomar algunas opciones.

Podemos dar clic en menú Programador /Macros

También podemos hacerlo desde el menú Vista / Macros

En ambos casos, abrirá la ventana donde podremos seleccionar la macro deseada y dar clic al
botón Ejecutar.

Esta ventana tambien puede abrir directamente con la secuencia de teclas Alt + F8

Al Ejecutar la macro ejecutará y repetirá todas las acciones grabadas.

Revisemos un ejemplo practico.

Queremos automatizar con una macro el llenado de una pequeña base de datos que nos pide
Nombre, Apellido y Edad.

Queremos ingresar los datos en un area de ingreso y luego que esos datos se guarden en la Base
de Datos.

Considerearemos la siguiente estructura de hoja para trabajar:

En las celdas B2, B3 y B4 se hara el ingreso, luego al guardar los datos pasaran a la base de datos
ubicandose al comienzo de esta, es decir en la fila 8. Desplazando una fila hacia abajo cualquier
dato anterior.
Luego, se limpiaran los datos ingresados y quedara a la espera de nuevos datos

Paso a Paso

Desde el menu programador o Vista, damos clic en Grabar macro e indicaremos como se muestra:

Nombre de la macro: Archivar

Tecla de metodo abreviado: ingresaremos la “a”

Descripcion: ingresaremos una frase que describa lo que hace la macro

Aceptar. La macro comenzará a grabar los siguientes pasos:

1. Ingresamos los datos

2. Seleccionamos el rango A8:C8

3. Moveremos esa fila hacia abajo con la instrucción clic derecho / Insertar / Desplazar las
celdas hacia abajo
 4. Seleccionaremos los datos ingresados en el rango B2:B4, y le indicaremos Copiar

5. Nos ubicamos en la celda A8 e indicamos clic derecho / Transponer y se copiaran los datos
de manera horizontal en la fila 8.

6. Borramos los datos desde el rango B2:B4 y luego seleccionamos la celda B2

7. Detener la macro.

Entonces, ahora después de ingresar los datos, ejecutaremos la macro y esta se encarga de
realizar todo el proceso anterior de manera automática y veloz.

Para ejecutar la macro podemos ir a la ventana de selección de macros donde podemos ejecutarla.
O podemos crear un botón que la ejecute de manera más cómoda al usuario.

Asignar la macro a un botón

Desde el menú Insertar / Grupo Ilustraciones / Iconos, buscaremos un icono para guardar.

Lo insertaremos sobre la celda C3 y ajustaremos su tamaño.

Con un clic derecho sobre la imagen, en el menú contextual damos clic en Asignar macro…

Seleccionaremos la macro Archivar y Aceptar.

Al posicionar el puntero del mouse sobre el icono veremos que aparece una mano indicando que
ahora ese icono tiene la macro asignada.

Prueba de la macro

Entonces, ingresaremos un nuevo conjunto de datos y luego, damos clic a nuestro icono para
guardar y veremos en acción la macro. Podemos asi usarla en cada oportunidad para esta tarea.

La creación de macros tiene por objeto automatizar tareas que pueden ser repetitivas o largas de
realizar. LA macro puede guardar todo este conjunto de instrucciones o acciones y luego
ejecutarlas cada vez que necesitemos realizar la misma tarea.

Guardar un archivo con macros.

Cuando creamos macros en un libro de Excel, llegado el momento de guardar este nos puede dar
algunas advertencias respecto a ciertas características de Proyecto de VB no se puede guardar en
un libro sin macros.

Para poder guardar nuestro libro y conservar las macros haremos clic en el botón No

Esto nos lleva a la venta de Guardar como

donde haremos el siguiente ajuste en la casilla de Tipo, desplegándola y seleccionando a Libro de

Excel habilitado para macros

Luego, bastara con asignar el nombre de archivo deseado y Guardar.

Asi, nuestro libro ya queda habilitado para el funcionamiento de nuestras macros.

Apertura de un libro con Macros

Al abrir un libro que contenga macros, Excel por defecto bloquea el uso de las macros, esto por
seguridad ante código sospechoso. Nos mostrara la siguiente indicación:

Si tenemos confianza de nuestro archivo bastara con dar clic en el botón Habilitar contenido y ya
nuestras macros quedaran activas y disponibles para ser usadas.
Introducción A Visual Basic

La construcción de las macros se realiza en un lenguaje de programación llamado Microsoft Visual

Basic para Aplicaciones (VBA). Al grabar la macro este código se crea automáticamente.

Para conocer el código generado por nuestra macro podemos activar la ventana del editor de VBA.

Podemos activar dando clic en el menú programador / Visual Basic

o con la secuencia de teclas Alt + F11

Se vera asi:

Al abrir nos muestra enseguida el código generado en la macro Archivar que se inicia en la línea
que dice Sub Archivar() y finaliza en la línea que dice End Sub. Entre estas se escribe todo el código
que ejecuta las distintas acciones de la macro.

Podemos observar que en las primeras líneas, que se ven de color verde, comienzan con un
apostrofe (‘) todo lo escrito en esa línea se considera un comentario que no se ejecuta. Sirve para
hacer comentarios de la macro respecto a su uso, o comentar que hace una línea de código, o algo
asi. Estos comentarios son opcionales, la macro no los requiere.

El resto de código nos muestra los pasos ejecutados efectivamente por la macro.
En resumen, una macro tiene la siguiente estructura:

SUB Nombre_macro()

´ Comentario 1 (opcional)

‘ Comentario 2 (opcional)

Código a ejecutar 1

Código a ejecutar 2

Código a ejecutar 3

Código a ejecutar …

END SUB

El código se compone de instrucciones predefinidas que realizan diversas acciones. Si examinamos

las líneas de la macro creada podremos distinguir algunas especificas. SE indica en verde la acción
a realizar y en negrita la línea de código VBA

Selecciona el rango A8:C8

Range("A8:C8").Select

Selecciona el rango B2:B4 y activa la copia

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Range("B2:B4").Select

Selection.Copy

Selecciona la celda A9

Range("A9").Select

Pega el rango copiado usando Transponer a partir de la celda A9

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=True
Selecciona el rango B2:B4

Range("B2:B4").Select

Borra los datos del rango seleccionado

Application.CutCopyMode = False

Selection.ClearContents

Selecciona la celda B2

Range("B2").Select

Al conocer este código, y otras instrucciones, también es posible programar directamente en el

editor de VBA una macro.

A continuación revisaremos un ejemplo de programación directa con Lenguaje VBA aplicado en

Excel

Mi primera macro en Excel

Para poder escribir tu primera macro, lo primero que debes hacer es activar el botón
programador, si es que no lo tienes ya activo.

Una vez activada la ficha Programador, podrás tener acceso al botón Visual Basic y Macros dentro
del grupo Código.

Una vez activada la ficha Programador, podrás tener acceso al botón Visual Basic y Macros dentro
del grupo Códigos

Algo importante que debes saber es que existen dos formas de hacer una macro, la primera es a
través del botón “Grabar Macros” con lo cual podrás grabar una serie de pasos realizados
directamente en la hoja de Excel y usarla para simplificar la actividad grabada requerida. La
segunda forma es escribir la macro o subrutina dentro de módulos creados por Excel.

En esta parte nos interesa explicar la segunda forma: Cómo escribir tu primera macro usando un
módulo de Excel.

Primeramente, agregaremos una hoja nueva que llamaremos Macros2.

Para realizar nuestra macro, en la parte superior damos clic en Visual Basic.

Ahora procedemos a crear un módulo en la que vamos a programar nuestra macro.

Hacemos clic en el Menu Insertar y seleccionamos insertar modulo.

Nos aparecerá una ventana en blanco en la que podremos iniciar nuestra programación.

En el módulo creado se elaborará un ejemplo sencillo de programación, nuestra primera macro:

Instrucciones:

Realice una macro en la hoja Macros2 que permita sumar dos valores previamente establecidos,
deberá indicar al usuario que digite los valores a sumar en las celas c5 y c6 y crear las respectivas
etiquetas ( valor1, valor2, suma=) en las celdas b5, b6, b8 respectivamente. Finalmente se deberá
mostrar la suma en una ventana de resultados y el valor de esta en la celda c8.

Código de mi primera macro:

Los comentarios se mostrarán en verde (recuerda, estos son opcionales y no afectan ni se ejecutan
en la macro).

Puedes copiar y pegar este código, dentro del módulo creado, pero te recomiendo lo vayas
generando paso a paso para que puedas conocer las instrucciones y entender como estas van
realizando las tareas solicitadas.

'Toda subrutina o Macro empieza con la función Sub ……. (nombre de la subrutina)
Sub misuma()
'Fijamos la hoja Macros2
Sheets("Macros2").Select
'Nos va interesar que los valores se reseten para poder ingresar un Nuevo valor cada vez que se
ejecuta la macro por ello usamos el comando clearcontents

Range("c5:c8").ClearContents
'fijamos las variables valor1 y valor2 como números enteros. Estas sirven para almacenar valores
de manera temporal.
Dim valor1 As Integer
Dim valor2 As Integer
'creamos las etiquetas de los valores a ingresar y la suma.
Range("b5") = "Valor1"
Range("b6") = "Valor2"
Range("b8") = "Suma ="

'Le pedimos al usuario que ingrese su primer valor de suma , para ellos usamos el comando
inputbox y fijamos el valor en la celda c5
valor1 = InputBox("ingrese primer valor")
Range("c5").Value = valor1
'Le pedimos al usuario que ingrese su segundo valor de suma , para ellos usamos el comando
inputbox y fijamos el valor en la celda c6
valor2 = InputBox("ingrese segundo valor")
Range("c6").Value = valor2
'ahora realizamos la operación de suma y la guardamos en una variable llamada sumita, para
hacer la suma usamos la función worsheetsfunction.sum
sumita = WorksheetFunction.Sum(valor1 + valor2)
' ahora creamos que la ventana de mensaje y alojamos el valor obtenido en sumita en la celda c8
Range("c8") = sumita
MsgBox("el valor de la suma es:" & sumita)
'usamos end sub al finalizar la programación.
End Sub

Para verificar que nuestra macro funciona podemos presionar F5 en las subrutinas de códigos o
simplemente presionando el botón ejecutar.

Si queremos probar paso a paso nuestra macro usamos el botón F8.

Esta acción es muy practica para ir viendo el efecto de cada una de las líneas de código y resulta
muy útil para detectar errores o fallas en la construcción de la macro.
Al ejecutar la macro comienza su trabajo

Ingresaremos los valores solicitados en la ventana emergente, y Aceptar. Los valores pasan a la
hoja, realiza el cálculo y nos muestra el resultado en una nueva ventana.

Recuerda que, para usar la macro, puedes crear un botón o un elemento al que podemos asignar
la macro para ejecutarla.

Agregaremos una imagen desde el menú Insertar / Ilustraciones / Iconos.

Buscaremos un icono de nombre sumar y lo insertamos en la hoja. Y le asignaremos la macro con

un clic derecho y seleccionamos Misuma. ¡Esta lista para usar, Pruébala!
Lista de comandos para modificar una hoja con macros VBA

</> Comando SHEET

</> Comando RANGE

</> Comando OFFSET

</> Comando CELLS

</> Comando WORKBOOKS

</> Comando INTERIOR.COLOR

</> Comando FONT

</> Comando ACTIVECELL

</> Comando CLEARCONTENTS

¿Qué son las macros y para que me sirven?

Trabajando en Excel tienes la posibilidad de modificar una hoja de trabajo, haciendo uso de los
comandos desarrollados para tal fin.

A continuación, presentamos los comandos más comunes para programar una hoja con vba:

</> Comando SHEET

Sheets es una colección que puede contener objetos Chart o Worksheet. Esta colección es de gran
utilidad para trabajar con hojas de diferentes tipos.

Ejemplos:

Para imprimir las hojas del libro activo:

Sheets.PrintOut

Para agregar una hoja al final del libro actual:

Sheets.Add After:=ActiveSheet

Para renombrar la hoja 1:

Sheets(«Hoja1»).Name = «Nuevo Nombre Hoja»

Para agregar dos hojas de gráfico al libro actual, y colocarlas detrás de la hoja dos del libro:

Sheets.Add type:=xlChart, count:=2, after:=Sheets(2)

Para activar la hoja denominada Sheet1:

Sheets(«Sheet1»).Activate
</> Comando RANGE

El objeto Range es un objeto que hace referencia a una celda específica, a un conjunto de celdas
(juntas o separadas), a una fila entera o a una columna.

Ejemplos:

Selecciona la celda A1 de la hoja activa:

Range(«A1»).select

Selecciona el rango desde A1 hasta B20 en la hoja activa:

Range(«A1: B20»).Select

Para seleccionar la celda A1 en la hoja 2:

Worksheets(2).Range(«A1»).Select

Para seleccionar celdas separadas en la hoja actual:

Range(«A1,A3,C5»).Select

Inserta el valor 35 en el la celda A1 de la hoja activa:

Range(«A1») = 35

Copia el valor en la celda A1 de la hoja activa:

Range(«A1») .Copy

Pega el valor copiado en el la celda A2 de la hoja 2:

Sheets(«Hoja2»).Range(«A2»).Select
ActiveSheet.Paste

</> Comando OFFSET

La función OFFSET devuelve una referencia a un rango que es un número específico de filas y
columnas de otro rango o celda.

Sintaxis

Range(«A1»).OFFSET(RowOffset, ColumnOffset)

RowOffset indica el número de filas que deseas moverte desde la fila seleccionada.

ColumnOffset es un entero que señala cuántas columnas deseas moverte desde la fila
seleccionada.

Ejemplo:

Selecciona la celda C6, después de desplazarse 5 filas hasta A6, y luego 2 columnas a la derecha.

Range(«A1»).OFFSET(5, 2).Select

Dos columnas a la derecha de la celda C6, escribe la palabra APROBADO.

Range(«C6»).OFFSET(0, 2) = «Aprobado»

Incrementa en 1, el valor de la celda A1 y lo escribe una fila abajo

Range(«A1»).OFFSET(1, 0) = Range(«A1») + 1

</> Comando CELLS

Cells es una propiedad que se emplea para especificar una celda o todas las celdas en la hoja de
trabajo.

Sintaxis

Cells(RowIndex, ColumnIndex)

RowIndex indica el número de la fila al que deseas referirte.

ColumnIndex señala el número de columna al que deseas referirte.

Ejemplo:

Devuelven la celda «B4» en una hoja de trabajo activa.

Expresión = Cells (4, 2)

</> Comando WORKBOOKS

Workbook es un objeto de la colección Workbooks. Esta colección contiene los objetos Workbook
abiertos actualmente en Microsoft Excel.

Ejemplo:

En el siguiente ejemplo se activa el libro uno:

Workbooks(1).Activate

Para activar la Hoja1 en el libro Factura.xls:

Workbooks(«Factura.xls»).Worksheets(«Sheet1»).Activate

</> Comando INTERIOR.COLOR

La propiedad Interior.Color devuelve o define el color de un objeto. Se debe utilizar en conjunto

con la función RGB para crear el valor de un color.

Sintaxis

expresión.Color

expresión devuelve un objeto Interior.

Ejemplo

Aplica letra color ROJO al contenido de la celda D6:

Range(«D6»).Font.Color = vbRed

Aplica fondo color AZUL al contenido de la celda D5:

Range(«D5»).Interior.Color = vbBlue

Para establecer el color de las etiquetas de marca de graduación en el eje de valores de Chart1:

Charts(«Chart1»).Axes(xlValue).TickLabels.Font.Color = RGB(0, 255, 0)

</> Comando FONT

El comando FONT abarca los atributos de fuente tales como el nombre, tamaño, el color, etc., de
un objeto.

Ejemplo:

Para dar formato de negrita a las celdas A1:B5

Worksheets(«Sheet1»).Range(«A1:B5»).Font.Bold = True

</> Comando ACTIVECELL

El comando ActiveCell se refiere a la celda seleccionada actualmente en una hoja de cálculo. Una
vez que la celda se encuentre seleccionada se le pueden asignar valores o funciones.

Ejemplo:

Para asignar el valor de 5 a la celda activa:

ActiveCell.Value = 5

</> Comando CLEARCONTENTS

ClearContents es una función que se utiliza para borrar el contenido en un rango dado de celdas o
grupo de celdas.

Ejemplo:

Se borra el contenido del rango de celdas A1:C3.

Range(«A1:C3»).ClearContents

Ahora ya conoces los comandos básicos, toca sentarse a programar, así que mucho éxito.
Conclusiones

Gracias a los avances que ha tenido Microsoft Excel, hoy podemos usar esta herramienta para
un sinnúmero de cosas, tanto en el ámbito personal, profesional, así como dentro de cualquier
empresa, que es donde vemos, que más se utiliza o aplica el uso de esta herramienta.

Excel se distingue de los demás programas ofimáticos porque nos permite organizar datos en filas
y columnas, y al introducir datos numéricos y alfanuméricos en las hojas de cálculo de
Excel, podemos realizar cálculos aritméticos básicos o aplicar funciones matemáticas de mayor
complejidad y utilizar funciones de estadísticas o funciones de tipo lógica en Excel.

La hoja de cálculo de Excel nos facilita en gran medida, trabajar con información que podamos
analizar, generar reportes mediante herramientas de gráficos y las tablas dinámicas.

Excel tiene gran cantidad de aplicaciones en el comercio, en la ingeniería, en la contabilidad,

incluso en el hogar, para organizar un presupuesto, llevar un control de gastos, etc.

En este nivel, hemos revisado herramientas y funciones que nos permiten ingresar al mundo del
análisis de los datos, las funciones de fechas y horas aplican para análisis de tiempos, funciones
lógicas para revisar contenidos de celdas y proponer opciones de acción como cálculos o ingresos
de datos, búsquedas de datos para complementar información, llenar fichas u otros, ingresar al
análisis mediante la creación de informes de tabla dinámica y sus herramientas.

Esperamos que con todo lo estudiado puedas tener ya un conocimiento más amplio y puedas usar
esta herramienta en el desarrollo de tu trabajo o actividades.

Gracias por tu tiempo y dedicación.

Bibliografía y Referencias.
Software Microsoft Office 365 – Excel
Actualización 2023
Entrenamiento de Excel 2013 - Soporte técnico de Microsoft

Tareas Básicas en Excel

Soporte Técnico de Microsoft 365
https://support.microsoft.com/es-es/office/tareas-b%C3%A1sicas-en-excel-dc775dd1-fa52-430f-
9c3c-d998d1735fca

Tareas intermedias en Excel

Soporte Técnico de Microsoft 365
https://support.microsoft.com/es-es/office/aprendizajes-en-v%C3%ADdeo-de-excel-9bc05390-
e94c-46af-a5b3-d7c22f6990bb

Links de Referencia

Ayuda y aprendizaje de Excel (microsoft.com)

https://support.microsoft.com/es-es/office/crear-una-tabla-din%C3%A1mica-para-analizar-datos-
de-una-hoja-de-c%C3%A1lculo-a9a84538-bfe9-40a9-a8e9-f99134456576

https://support.microsoft.com/es-es/office/crear-una-tabla-din%C3%A1mica-con-un-origen-de-
datos-externo-db50d01d-2e1c-43bd-bfb5-b76a818a927b

https://business.tutsplus.com/es/tutorials/advanced-excel-pivot-table-techniques--cms-28572

https://support.microsoft.com/es-es/office/crear-cargar-o-editar-una-consulta-en-excel-power-
query-ca69e0f0-3db1-4493-900c-
6279bef08df4#:~:text=Crear%20una%20consulta%20en%20blanco,-
Es%20posible%20que&text=Seleccione%20Datos%3E%20Obtener%20datos%20%3E%20de,el%20
Editor%20de%20Power%20Query.

https://learn.microsoft.com/en-us/power-query/power-query-what-is-power-query

Compilación y Desarrollo y Ejercicios

Esteban R. Martin - 2023

Microsoft Office Specialist

Excel Expert Certified

Actualización al viernes, 23 de junio de 2023