Noticias

funciones de busqueda
30 Abr

Buscando datos en Excel – Inicial Experto

¿Porqué un taller de búsqueda de datos?

La mayoría de personas a nivel empresarial deben conocer el manejo de funciones de búsqueda. No conocer su manejo los pone en una posición muy básica en el manejo de Excel.

En ese sentido, BUSCANDO DATOS EN EXCEL es un taller que ofrece los detalles de dos funciones de tratamiento básico e intermedio de Excel, pero llevadas a un nivel Experto, para afrontar cualquier situación o caso empresarial.

La función BUSCARV y la función BUSCARH, tienen la misma cantidad de argumentos y la diferencia entre ambas es que una realiza una búsqueda vertical y la otra, una búsqueda horizontal.

Por lo tanto, el taller tratará sus 3 principales argumentos, pero hará un profundización en el argumento de aproximación, para que el participante tenga una herramienta adicional y sobre todo eficiente, para solucionar problemas que se presentan en las empresas.

Buscando datos en Excel Inicial: Contenido 12 horas

VALOR BUSCADO
• Sin usar celda de apoyo
• Usando celda de apoyo
• Usando comodines
• Corrigiendo el valor_buscado_(1)
• Corrigiendo el valor_buscado_(2)
• Valores repetidos
• Aproximación de números
• Aproximación de fechas
• Aproximación de texto_(1)
• Aproximación de texto_(2)_avanzado

INDICADOR DE COLUMNA / FILA
• Columna o fila fija
• Columna o fila calculada_usando celda de apoyo (1)
• Columna o fila calculada_sin usar celda de apoyo (1)
• Columna o fila calculada_usando celda de apoyo (2)
• Columna o fila calculada_sin usar celda de apoyo (2)
• Columna o fila calculada_usando celda de apoyo caso especial (1)
• Columna o fila calculada_usando celda de apoyo caso especial (2)

MATRIZ BUSCAR EN
• Matriz sin celdas de apoyo
• Buscando en dos matrices
• Buscando en más de dos matrices
• Buscando en más de dos matrices_corrección manual
• Buscando en más de dos matrices_corrección formulada
• Matriz en reversa

Expositor:

MOS Expert CPC Martín Cruz

Por Noticias , 12 Comments Read more...
numeros a letras
20 Oct

Convertir Números a Letras en Excel [formulado y editable]

Convertir Números a letras en Excel

Convertir números a letras en Excel es una tarea bastante utilizada, ya que al realizar algún tipo de cotización o comprobante, es relevante obtener con practicidad el nombre del números (letras) que estamos digitando o calculando.

descarga

¿Cómo convertir número a letras en Excel?

El archivo permite obtener el nombre de un número de hasta 9 dígitos más 2 decimales, y permite, indicar un nombre de moneda para ser adecuado a cualquier documento.

convertir numeros a letras

Las celdas de color son las celdas modificables para un usuario básico, pero el archivo puede ser editable ya que las formulas están abiertas.

convertir numeros a letras

Las formulaciones de la columna C, D, E y F son editables.

La fórmula de la columna F captura los nombres de los números generados en la columna E para desarrollar el nombre final del número indicado en la celda C5.

Fórmula para convertir números a letras

La fórmula primero extrae cada posición del número indicado en la celda C5.

=COCIENTE(RESIDUO(C$5,10^($C$4-(FILAS(E$12:E12)-1))),10^(($C$4-(FILAS(E$12:E12)-1))-1))

Y luego, en función a esos números, aplicamos funciones de lógicas y de búsqueda para obtener los nombres de cada número en la posición de centena, decena o unidad que se encuentre.

=SI($C$4-(FILAS(E$12:E12)-1)>LARGO($C$5),””,

SI(D12<>””,

SI(B12=”DECENA”,””,

BUSCARV(VALOR(DERECHA(D12)),$L$12:$N$20,COINCIDIR(VALOR(IZQUIERDA(D12)),$L$11:$N$11,0),0)

&” “&C12),

SI(E12=0,””,

SI(O(C12=”UNO”,C12=”CIENTO”),C12,

BUSCARH(B12,$H$11:$J$20,E12+1,0)&” “&C12))))

Importante

Tener en cuenta que el número máximo puede contener 9 dígitos en la parte entera, si deseas cambiarlo, deberás reformularlo.

descarga

Autor

MOS Expert CPC Martín Cruz

 

Por Noticias , , , , , , , , , , 4 Comments Read more...
15 Oct

Función TEXTO en Excel [formatos y ejemplos]

¿Qué es la función TEXTO?

La función TEXTO en Excel convierte una valor, número, fecha y hora en un texto, con un formato específico.

Sintaxis de la función TEXTO

Nombre en inglés: TEXT

La función TEXTO tiene dos argumentos o parámetros por completar.

Argumento valor. Es un valor numérico, fecha u hora, una fórmula que genera un valor numérico o una referencia que contiene un valor numérico, pero en cualquier caso un número.

Argumento formato. Es un código de formato de número, formato de fecha o formato de hora, que podemos ubicar en la categoría personalizada de formato de celdas.

¿Cómo usar la función TEXTO?

La función TEXTO en Excel normalmente se utiliza para generar títulos o comentarios con resultados móviles o dinámicos.

Debemos tener claro que el argumento valor siempre es un número, de cualquier tipo, y el argumento formato, si lo digitamos, siempre va entre comillas.

Función TEXTO: formato de fecha

funcion texto

Tenemos un fecha en la celda C13, por cierto la fecha es un valor numérico, el formato de la fecha en esa celda es dd/mm/yyyy.

Queremos aplicarlo un formato de fecha distinto, para que se muestre en la celda D15.

Como se ve, le aplicamos un formato de fecha “yyyy-mm-dd” y lo ponemos entre comillas.

Función TEXTO: número a texto

funciion texto

Tenemos un número en la celda D22, queremos armar un comentario. Los ingresos del mes fueron $18,000.

Para transformarlo en un número con formato de moneda, le aplicamos el formato entre comillas “$#,##0” y lo concatenamos con el texto “Los ingresos del mes fueron”.

Función TEXTO Ejemplos

Función TEXTO como obtener el año de una fecha. 

El formato de fecha que utilizaremos será “yyyy” (4 veces “y”). Puede pasar en alguna computadoras, básicamente por la configuración, que el formato a aplicar será “aaaa” (4 veces “a”).

función texto

Función TEXTO como obtener el año y mes de una fecha

Usaremos el formato “mmmm” para obtener el mes de la fecha y luego, para año, aplicaremos el formato “yyyy”, como se muestra en la imagen.

funcion TEXTO

Función TEXTO formato de fecha personalizada

Queremos obtener un resultado de fecha, que indique “miércoles 31 de octubre del 2018”. Usaremos un formato personalizado, en el argumento formato, “dddd dd”” de “”mmmm”” del “”yyyy”

función texto

Función TEXTO Diferencia entre dos fechas en formato de días y horas

Queremos determinar el tiempo transcurridos entre dos fechas, tanto en días como en horas. Entonces combinaremos formato de fechas y horas.

Para el argumento valor, determinamos una diferencia que representa un valor numérico. A esa diferencia le aplicaremos el formato

“d”” dias “”h”” horas”” m”” minutos”””

funcion texto

Función TEXTO formato de número con tres dígitos

Queremos presentar un número a 3 dígitos, tenemos en la celda D73 el número 6 pero queremos que se muestre 006 entonces aplicaremos un formato “000”.

funcion texto

Función TEXTO días transcurridos del año

Queremos calcular cuantos días han transcurridos a la fecha del 15/08/2018. A esto se le llama formato juliano. Año 2018 días transcurridos 226.

funcion texto

 

Artículos relacionados

Autor

MOS Expert CPC Martín Cruz

Por Noticias 4 Comments Read more...
14 Oct

función DIASEM en Excel [guía detallada]

¿Qué es la función DIASEM?

La función DIASEM en una función de Excel que nos permite determinar el número de día de la semana a la que pertenece una fecha.

En términos generales, los días de la semana son del 1 al 7, siendo lunes el número 1 y domingo el número 7, aunque no es determinante, ya que para esta función, esto es un argumento que lo define el usuario.

Nombre en inglés: WEEKDAY

Sintaxis de la función DIASEM

Cuando hablamos de sintaxis de la función, estamos hablando de los argumentos que necesita completar esta función para entregar un valor.

Veamos entonces cuantos argumentos tiene:

funcion DIASEM

Argumento núm_de_serie. Este argumento es una fecha, si se encuentra en una celda, hacemos referencia a la celda, pero sino, lo podemos digitar entre comillas.

Argumento [tipo]. Este argumento es un número de la tabla, el cual define el número de inicio y número de fin para el día de la semana.

funcion DIASEM

Por decir, si elegimos el número 1, estamos determinando que el día 1 corresponde al domingo y el día 7 corresponde a sábado. En caso elijamos número 2, estamos determinando que el día 1 corresponde a lunes y día 7 corresponde a domingo.

¿Cómo usar la función DIASEM?

Como ya conocemos los argumentos, veremos como utilizar la función DIASEM.

¿Qué día de la semana fue el 31/12/1999?

En la celda E12 hemos puesto la fecha 31/12/1999 y aplicaremos la función DIASEM, utilizaré 3 diferentes tipos.

Cuando aplicamos tipo 1, el resultado es 6 lo que indica que es viernes, ya que el tipo 1 representa a domingo como 1 y  sábado como 7.

Al aplicar tipo 2, el resultado es 5 lo que indica que es viernes, ya que el tipo 2 representa a lunes como 1 y domingo como 7.

Por otro lado, cuando aplicamos tipo 3, el resultado es 4 lo que indica que es viernes, ya que el tipo 3 representa a lunes como 0 (cero) y domingo como 6.

funcion DIASEM

Aunque el resultado numérico será diferente, cada número indica el mismo día, es decir viernes.

Ejemplos de la función DIASEM

DIASEM para determinar fin de semana

Podemos determinar el número de día con la función DIASEM y usaremos el tipo 2. Si el resultado es mayor a 5, es decir, 6 ó 7, es el indicador de que estamos ante una fecha que pertenece al fin de semana, si consideramos que fin de semana es sábado y domingo.

funcion DIASEM

DIASEM para determinar el último domingo del mes

Tenemos una fecha 15/01/2018 y queremos averiguar, a partir de esa fecha, cual es el último domingo de ese mes-año.

Vamos a aplicar la función FIN.MES para determinar el último día del mes que por cierto cae miércoles.

=FIN.MES(R28,0) = 31/01/2018

Una vez que obtengamos la fecha de fin de mes, utilizaremos la función DIASEM para obtener el número de día de la semana.

=DIASEM(S28,2) = 3 (miércoles)

A la fecha de fin de mes obtenida, le restaremos el resultado de DIASEM

= S28 – DIASEM(S28,2)

Listo, llegamos al resultado, 28/01/2018. Dicha fecha corresponde al último domingo del mes.

funcion DIASEM

DIASEM para determinar el nombre (no el número) de día de semana

La función DIASEM entrega un número de día de semana y NO ENTREGA UN NOMBRE, sin embargo podemos apoyarnos en la función ELEGIR, para que, según el número que genere la función DIASEM, elija un nombre de día de semana.

funcuon DIASEM

DIASEM para determinar el primer miércoles del mes.

Tenemos la fecha 15/03/2018 y queremos averiguar, a partir de esa fecha, cual es el primer miercoles de ese mes-año.

Vamos a aplicar la función FIN.MES para determinar el último día del mes anterior.

=FIN.MES(R43,-1) = 28/02/2018

Una vez que obtengamos la fecha de fin de mes del mes anterior, utilizaremos la función DIASEM para obtener el número de día de la semana. Para este caso usaremos el Tipo 14, inicio jueves (1), fin miércoles (7).

=DIASEM(S43,14) = 7 

A la fecha de fin de mes obtenida, le sumaremos el resultado de DIASEM

= S43 + DIASEM(S43,14)

Listo, llegamos al resultado, 07/03/2018. Dicha fecha corresponde al primer miercoles del mes.

funcion DIASEM

Autor

MOS Expert CPC Martín Cruz

Por Noticias , , , , 2 Comments Read more...
funcion SUMA
05 Oct

Función SUMA en Excel [uso avanzado]

¿Qué es la función SUMA?

La función SUMA en Excel retorna la suma de una lista de valores. Para definir los valores podemos digitarlos como constantes, hacer referencia a una celda, hacer referencias a un rango.

descarga

Sintaxis de la función

funcion SUMA sintaxis

Argumento número1: Es obligatorio, puede seleccionar una celda o rango, o digitar una constante.

Argumento número2: Es opcional, puede seleccionar una celda o rango, o digitar una constante.

La función SUMA en Excel tiene hasta 255 argumentos, llamados número 1, número 2 …, cada argumentos puede contener una cantidad ilimitada de números.

¿Para qué sirve la función SUMA?

La función SUMA tiene un objetivo principal, la de sumar, pero entre sus principales características tenemos que:

  • Si dentro del rango existen valores lógicos (VERDADERO – FALSO), los omite.
  • Si dentro del rango existen textos, los considera como CERO.

Esto, en muchos casos, define su verdadero alcance.

¿Cómo utilizar la función?

La primera opción de cómo usarla, es digitarla en una celda y cubrir los argumentos solicitados, pero debemos verificar cual es el separador de argumentos, la “coma” o el “punto y coma”.

funcion SUMA

Otra opción sería escribir en una celda = SUMA(  y luego presionar SHIFT + F3 donde se abrirá la siguiente ventana…

funcion SUMA

Otra opción sería utilizar la combinación de teclas ALT  +”=”

funcion SUMA

Ejemplos de la función SUMA

Como puede ver, puede usar la función suma de cualquier forma inclusive combinando estas opciones.

funcion SUMA

Sumar celdas con números y textos en Excel

Si tuviera dentro de un rango texto, la función SUMA sería la mejor opción para poder sumar el rango, sin necesidad de ser selectivos con las celdas.

Como ven en la imagen, cualquier opción generará el mismo resultado.

funcion SUMA

Error de la función SUMA

Cuando usamos el operador de SUMA dentro de la función SUMA podríamos tener un error de #¡VALOR! si dentro de los valores tenemos textos.

Lo adecuado es usar la “como” como separador de argumentos y no el simbolo +.

descarga

funcion SUMA

Artículos relacionados

Funciones básicas de Excel para el Contador

Autor

MOS Expert CPC Martín Cruz

Por Noticias 0 Comments Read more...
validacion de datos
02 Oct

Validación de datos Excel [guía completa]

La herramienta Validación de Datos de Excel permite tener el control de la información que se debe ingresar a una celda.

¿Para qué sirve la herramienta Validación de Datos de Excel?

El objetivo principal de la herramienta Validación de Datos es, RESTRINGIR el ingreso de información, limitándola a una lista o a la aplicación de una fórmula, que desarrolle la restricción.

Por decir, a una celda le aplico validación de datos para restringir el ingreso de números, sólo queremos que se ingresen números enteros.

¿Dónde está la herramienta Validación de Datos de Excel?

La herramienta Validación de Datos la ubicamos en la ficha Datos, en el grupo Herramientas de Datos.

Para usar esta herramienta no es necesario desplegar la lista, donde podemos apreciar 3 comandos, siendo el primero el mismo de forma repetida.

donde esta validacion de datos

¿Cuándo es útil la herramienta Validación de Datos de Excel?

Dado que esta herramienta limita o restringe el ingreso de información, es útil cuando desarrollamos cuadros en Excel para que otro usuario los manipule o ingrese datos, la idea es limitarlo a que el ingreso de datos sea de forma uniforme,para evitar correcciones posteriores innecesarias.

Tipos de Validación de Datos

La herramienta Validación de Datos tiene varios Opciones de Criterio de Validación, entre ellos tenemos

tipos de validacion de datos

  • Cualquier valor. No realiza ninguna validación. Esta opción se encuentra por defecto en la celda.
  • Número entero. Solo permite el ingreso de números enteros.

Por ejemplo, entre 10 y 100, mayor que 200, menor que 150, entre otros.

validacion de datos

  • Decimal. Permite el ingreso de número decimales, incluida los enteros, no textos.

Por ejemplo, entre 10 y 100 pero podemos ingresar con decimales.

validacion de datos

  • Lista. Permite el ingreso de datos siempre que este en una lista predeterminada. Aquí podemos señalar un rango, digitarlo directamente o formular.

Ver Lista Despegable con INDIRECTO

validacion de datos

  • Fecha. Sólo permite el ingreso de fecha.

Por ejemplo. Fecha entre =HOY() y HOY()+30

validacion de datos fecha

  • Hora. Sólo permite el ingreso de hora. Tener en cuenta que la hora es generalmente un número decimal.

validacion de datos

  • Longitud del texto. Permite el ingreso de una cantidad de caracteres o dígitos

validacion de datos

  • Personalizada. Permite el ingreso de información en función a una fórmula personalizada. Una fórmula personalizada es la opción mas potente de validación de datos, ya que dependerá exclusivamente de las funciones que maneje y el objetivo de su formulación.

Validación de Datos Omitir Blancos

Esta casilla de verificación está presente en todas los tipos de criterio de validación. Por defecto siempre activa pero si deseas que el usuario al momento de ingresar un dato no lo deje vacío debes desactivarlo.

validacion de datos

Configurar Mensajes de entrada

Esta opción permite agregarle a la restricción, un mensaje que ayude al usuario saber que tipo de información debe ingresar.

Es muy útil para celdas individuales, no tanto para rangos.

validacion de datos

Configurar Mensaje de Error

Si el usuario ingresara un dato que está fuera de las restricciones, podemos agregarle un mensaje personalizado y se mostrará cada vez que se ingrese un dato erróneo.

validacion de datos

Validación de datos Borrar todos

Si queremos eliminar alguna restricción generada por la herramienta Validación de Datos, basta ubicarse en la celda que lo contiene e ingresar a la misma opción donde fue generada. La opción tiene un botón exclusivo para la eliminación.

validacion de datos

Lista Desplegable usando Validación de Datos

Es la opción más usado de Validación de Datos, ya que permite seleccionar un valor sin digitarlo, ahorrando tiempo al usuario.

Tenemos 3 opciones de lista desplegable:

  • Lista desplegable independiente

Aquí seleccionamos un rango que contiene la lista que usamos que crear restricción.

Por ejemplo seleccionamos el rango D7:D11 para que nos restringir el ingreso de sólo los 5 primeros días de la semana.

validacion de datos

  • Lista desplegable incrustada

Los datos se digitan dentro de la casilla. No digitar con comillas ni usar el símbolo = (igual)

Por ejemplo digitamos los 5 primeros días de la semana.

validacion de datos

  • Lista desplegable dependiente

Aquí el resultado de la lista depende del contenido de una celda. Regularmente se usa la función INDIRECTO en estos casos. Puede aplicar otras funciones sin duda, dependerá de su objetivo.

Por ejemplo. Seleccionamos una de dos lista, depende del contenido de la celda H7.

= SI ( $H$7 = “Semestre 1” , $D$6:$D$11 , $F$6:$F$11 )

validacion de datos

Rodear con un Círculo Datos No Válidos

Muchas veces, primero tenemos los datos luego aplicamos Validación de Datos son dichos datos. La idea es detectar lo que muchas veces de forma visual puede ser complicado.

validacion de datos

Ubicar celdas con Validación de Datos

Para ubicar o seleccionar celdas que contengan restricciones usaremos la herramienta de Selección Especial.

Presionar la tecla F5 y luego clic en el botón Especial.

Validacion de datos

Copiar y Pegar Validación de Datos a otra celda

Es probable que algunas circunstancias necesitemos copiar sólo la validación de datos aplicada a un celda o un rango de celdas.

La herramienta Pegado Especial nos ayuda con ese propósito.

validacion de datos

Validación de Datos en Excel Ejemplos

Validación de Datos para número de RUC

Validar el rango D8:D12 para digitar números tributarios.

validacion de datos

Validación de Datos para código de almacén

Validar el rango D8:D12 para que permita ingresar un código de almacén que tenga 8 caracteres, los dos primeros AR y el resto que sean números.

= Y ( LARGO ( D8 ) = 8 , IZQUIERDA( D8 , 2 ) = “AR” , ESNUMERO ( VALOR ( EXTRAE ( D8 , 3 , 5 ) ) ) )

validacion de datos

Validación de datos para fechas del mes

Validar el rango D8:D12 para digitar fechas de hoy hasta fin de mes.

validacion de datos

Validación de datos para Valores Unicos

Validar el rango D8:D12 para digitar sólo valores únicos.

validacion de datos

Validación de datos para digitar páginas web

Validar el rango D8:D12 para digitar sólo páginas web.

validacion de datos

Validación de datos para digitar números que no contengan unidades

Validar el rango D8:D12 para digitar sólo números que no contengan unidades.

validacion de datos

 

Autor

MOS Expert CPC Martín Cruz

 

 

 

 

 

 

 

Por Noticias 1 Comment Read more...
funcion INDIRECTO
01 Oct

función INDIRECTO Excel [uso avanzado]

La función INDIRECTO de Excel es una de las funciones búsqueda más potentes que tenemos disponible en Excel.

Su combinación con otras funciones no tiene límite y siempre que encontremos un argumento matriz en otra función, podemos pensar en usar la función INDIRECTO.

Función INDIRECTO Excel ¿para qué sirve?

La función INDIRECTO sirve para hacer referencia a una celda, rango o matriz, mediante dos opciones principales:

  1. Uso de nombres predeterminados
  2. Uso de nombres personalizados

En nombres predeterminados tenemos a A1, F5, B6:B20, B6:F6, B6:B20.

En nombres personalizados tenemos: Zona, Vendedor, Meses o cualquier otro nombre que se asigne a una celda o rango.

¿Cómo usar la función INDIRECTO?

Cómo usar o cómo utilizar la función INDIRECTO pasa por revisar sus 2 argumentos:

Argumento ref. Normalmente aquí hacemos referencia a una celda que contiene el nombre de una referencia, ya sea predeterminada o personalizada

Argumento [a1]. Normalmente este argumento se omite, pero no lo emitimos cuando el argumento ref es de tipo F1C1.

Veamos un ejemplo

funcion INDIRECTO

Función INDIRECTO Excel Validación de datos

La combinación de la función con la herramienta validación de datos crea dinamismo en Excel, de forma tal, que el rango que utilizamos en validación de datos se dinamiza con la función INDIRECTO, creando una lista desplegable dinámica sin necesidad de seleccionar un rango cada vez.

Veamos un ejemplo

Queremos tener dos opciones, una, la de seleccionar un mes, la otra, de seleccionar una provincia, en ambos casos queremos obtener un total.

Validación de datos incrustada

Sobre la celda D8 usaremos la herramienta validación de datos para incorporar dos nombres: Provincia y mes, que permita seleccionar uno de estos elementos.

validacion de datos

Creación de nombres personalizados

Aplicaremos, a los rangos con marco azul (ver imagen superior), nombres personalizados, para el rango I7:M7 (Mes) y el rango H8:H11 (Provincia).

Son los mismos nombres que usamos en la validación de datos de la celda D8.

nombre de rangos

Validación de datos dependiente

Ahora usaremos la función INDIRECTO como fórmula dentro de validación de datos, para establecer un vinculo con lo que seleccionemos en la celda D8.

validacion de datos

Ahora asignaremos nombres personalizados a cada columna y fila del cuadro, usaremos los nombres de los meses y de las provincias como nombres de rangos.

Crear desde la selección

Usaremos la herramienta “Crear desde la selección” como herramienta para crear nombres masivos.

crear desde la seleccion

Formulación con la función INDIRECTO

Ahora trabajaremos una fórmula sobre la celda D14 para que, cuando se seleccione un mes o una provincia, se genere el resultado totalizado.

Anidaremos la función INDIRECTO con la función SUMA.

funcion SUMA e INDIRECTO

Función INDIRECTO Excel Ejemplos

Búsqueda dinámica con INDIRECTO

Veamos este caso. Tenemos 4 tablas pertenecientes a la zona Norte, Sur, Este y Oeste. Cada zona tiene los mismos 4 productos.

Tenemos una base de datos donde queremos traer las cantidades en stock.

Al utilizar la función BUSCARV, el argumento valor_buscado sería la celda K12, pero tendríamos un problema con la matriz, ya que para el primer registro la matriz_tabla sería el rango D12:E15 pero para el segundo registro la matriz_tabla tendría que ser el rango G24:H27.

Como ya sabemos, la función INDIRECTO nos sirve para hacer referencia a una celda que contiene un nombre predeterminado o personalizado.

En este caso, tenemos que nombrar los rangos indicados en la imagen, para poder incorporar la función INDIRECTO y lea el contenido de la celda J12 como un nombre de rango y lograr la búsqueda dinámica que necesitamos.

Combinar la función INDIRECTO con la función BUSCARV es viable ya que esta última solicita un matriz, que INDIRECTO puedo capturar.

Descarga archivo de trabajo Búsqueda dinámica con funcion INDIRECTO

funcion INDIRECTO y BUSCARV

 

Autor

MOS Expert CPC Martín Cruz

Por Noticias , , 7 Comments Read more...
buscarv objetivo
30 Sep

Comando BUSCAR OBJETIVO de EXCEL [uso avanzado]

 

El comando BUSCAR OBJETIVO de EXCEL es una herramienta de tanteo, un tanto que lo hace el Excel pero a la velocidad de la computadora.

Lo que nosotros como usuarios podemos hacer manualmente, tanteando un número para conseguir un resultado, lo hace esta herramienta BUSCAR OBJETIVO con mucha facilidad.

¿Cómo funciona la opción BUSCAR OBJETIVO en EXCEL?

La herramienta BUSCAR OBJETIVO en Excel 2019 se ubica en el Comando Análisis de Hipótesis (antes Análisis Y Si).

Esta herramienta tiene que cubrir 3 argumentos:

Definir la celda: Esta casilla está diseñada para seleccionar una referencia, una celda. La celda que debemos seleccionar, es una celda que CONTIENE UNA FORMULA, la cual se ve afectada de forma directa o indirecta por otra celda.

Con el valor: Esta casilla está diseñada para digitar un valor (un número) que deseamos obtener como resultado en la celda del argumento anterior (“Definir la celda”).

Cambiando la celda: Esta casilla está diseñada para seleccionar una referencia. una celda. La celda que debemos seleccionar, es una celda que NO CONTIENE UNA FORMULA, que afecta de forma directa o indirecta a la celda del argumento “Definir la celda”

Herramienta BUSCAR OBJETIVO Ejemplos

Ejemplo 1:

Por decir, queremos solicitar un préstamo de $10,000 para pagarlo en 36 meses. El banco nos calcula la cuota mensual a una TEM de 1.49%.

calculo de cuota

La cuota mensual asciende a $361, pero nos damos cuenta que no disponemos de ese dinero mensualmente, que lo máximo que podríamos disponer para pagar el préstamo es $250.

Entonces, ¿en cuanto tiempo podríamos pagarlo?

Aunque podríamos tantearlo nosotros mismos, nuestro tiempo de tanteo podría se mayor al de la máquina.

Aplicamos entonces la herramienta BUSCAR OBJETIVO, de acuerdo al siguiente detalle.

Buscar objetivo

Veamos entonces el resultado. BUSCAR OBJETIVO encontró un resultado para una cuota de $250. El préstamo lo podríamos pagar en un poco más de 5 años, en un total de 61 cuotas.

buscar objetivo

Ejemplo 2:

Veamos ahora, definiremos que el préstamo lo pagaremos en 60 cuota, dando un valor de la cuota de $253.

¿Cuanto en intereses en total pagaremos?

Bueno multiplicamos (Total de cuotas x Valor de cuota ) – Préstamo y obtenemos el total de intereses que pagaríamos, dándonos un total de $5,197.

Queremos negociar la tasa con el banco, para cubrir un total de $5,000 en intereses.

¿Cuál es la tasa efectiva mensual que deberíamos negociar para lograr ese objetivo?

buscar objetivo

Como verás, la celda E15, se afecta de forma indirecta por la tasa, ya que la tasa primero afecta a la cuota y luego, la cuota, afecta al total de intereses, en esa secuencia.

Pero no importa eso, el comando BUSCAR OBJETIVO lo solucionará, siempre que exista vinculación.

Herramienta BUSCAR OBJETIVO Ejercicios

Con el resultado de la tasa efectiva mensual anterior, que debe haber llegado a 1.44%, usa el archivo de trabajo y responde las siguientes preguntas:

  1. Calcula los intereses del primer año y define para buscar objetivo un máximo de $1,600 de intereses. ¿Cuál sería la nueva tasa efectiva mensual?
  2. Calcula los intereses del segundo año y define para buscar objetivo un máximo de $1,300 de intereses. ¿Cuál sería la nueva tasa efectiva mensual?

Comenta tus resultados!!!

BUSCAR OBJETIVO en Excel Macro

Podemos, sin duda, usar la grabadora de macros para realizar una grabación sencilla, usando BUSCAR OBJETIVO, para que Excel grabe los códigos que necesitamos.

Supongamos que queremos saber, con un total de intereses de $6,000, ¿Cuál sería el valor del préstamo?

Si tenemos un constante cálculo de esta situación, deberíamos pensar en agregarle un código para hacerlo en automático.

Sugiero, asignarle nombres a las celdas que trabajarán con la herramienta BUSCAR OBJETIVO para luego editar el código grabado y dejarlo según la imagen.

buscar objetivo macro

BUSCAR OBJETIVO en Excel para varias celdas

La herramienta BUSCAR OBJETIVO, sólo permite el cambio de una celda, argumento “Cambiando la celda”.

Si tienes más celdas que deseas cambiar, deberías pensar en usar la herramienta SOLVER de Excel, la que explicaré en una futura publicación.

Autor

MOS Expert CPC Martin Cruz

Por Noticias , 0 Comments Read more...
29 Sep

Combinar función BUSCARV y COINCIDIR [guía detallada]

Combinar la función BUSCARV y COINCIDIR en Excel es uno de los objetivos que debemos trazarnos para optimizar nuestra búsqueda de datos.

La función COINCIDIR es una función que nos ayuda mucho a determinar posiciones relativas tanto de fila como de columna, de un elemento dentro de un rango, ya sea de forma vertical u horizontal.

Si ya conocemos el uso de la función BUSCARV, debemos saber que, el argumento indicador_columnas, puede ser un número calculado.

Combinar la función BUSCARV y COINCIDIR, es una opción eficiente cuando el número de columna se convierte en un número variable.

 

¿Cómo usar la función BUSCARV y COINCIDIR?

La función BUSCARV y COINCIDIR pueden trabajar juntas para propósitos de crear dinamismo en el cálculo.

Ejemplo 1: Busque el valor de los ingresos del año pasado de acuerdo al mes y a la semana.

Como puede ver en la imagen, tenemos una tabla de ingresos $ del año anterior y nuestro objetivo es traer a nuestro cuadro actual (verde) los valores del año anterior.

buscarv y coincidir

Por decir, si queremos saber cuanto se vendió el año anterior en el mes de febrero de la semana 2, veríamos nuestro cuadro que el valor es $1,700.

Queremos compararlo con lo que hemos vendido este año, en ese mismo mes y semana, veríamos que es $1,580, por lo que sabríamos que hemos vendido $120 menos que el año anterior.

Entonces, primero trabajaremos la funcion BUSCARV  y luego determinaremos la posición de la columna, “digitado”, para obtener el resultado esperado.

»Descargar archivo de trabajo«

buscarv y coincidir

Entonces, el valor de la celda K12 (valor_buscado), se buscará en el rango D6:H18 (matriz_tabla), en la primera columna de la matriz, y, una vez ubicada, le solicitamos el valor de la columna 3 (indicador_columnas). Tomemos en cuenta que el tipo de coincidencia es EXACTA.

¿Cómo usar la función COINCIDIR para el cálculo de columna?

Ahora usaremos la función BUSCARV y COINCIDIR juntas, esta último nos ayudará a calcular el número de columna, la cual nos servirá como argumento indicador_columnas de la función BUSCARV.

funcion buscarv y coincidir

Entonces, el número 3 lo reemplazamos por la función COINCIDIR para que, al arrastre hacia arriba o hacia abajo, determine los números que necesitamos.

Por lo que la solución de este ejercicio sería

= BUSCARV ( K12 , $D$6:$H$18 , COINCIDIR( L12 , $D$6:$H$6 , 0 ) , 0 )

Formato condicional Barra de Datos

Realizada la búsqueda, aplicaremos formato condicional, la opción Barra de Datos y la editaremos.

La idea es mostrar un efecto gráfico de la variación, si la variación es positiva (mayor que el año anterior), entonces la barra apuntará hacia la derecha, de lo contrario hacia la izquierda.

formato condicional

 

Autor

MOS Expert CPC Martín Cruz

Por Noticias , 4 Comments Read more...
numeros aleatorios
29 Sep

Números aleatorios en Excel

Para crear números aleatorios en Excel disponemos de dos funciones:

  1. Función ALEATORIO()
  2. Función ALEATORIO.ENTRE(Inferior , Superior)

Estas funciones nos sirven para hacer simulaciones.

»Descarga el archivo de trabajo«

 

La función ALEATORIO

La función ALEATORIO es una función que no necesita ningún argumento, se escribe en la celda =ALEATORIO() ; sin incluir nada entre los paréntesis.

Esta función permite generar números aleatorios decimales, los cuales son mayores iguales que CERO y menores que UNO.

Nombre de la función ALEATORIO en inglés RAND

funcion aleatorio

La función ALEATORIO.ENTRE

La función ALEATORIO.ENTRE, es una función que si necesita argumentos. Requiere un número entero inferior y un número entero superior.

Esta función permite generar números aleatorios enteros, comprendido entre el número inferior y número superior; y su resultado, siempre será un entero.

Nombre de la función ALEATORIO en inglés RANDBETWEEN

funcion aleatorio.entre

descarga

¿Cómo generar números aleatorios en Excel con decimales?

Si queremos generar números aleatorios con decimales, tenemos muchas opciones, dependerá de la lógica de cada persona.

Ejemplo 1: Generar un número aleatorio entre 10 y 100 con 2 decimales

numero aleatorio

¿Cómo generar letras aleatorias en Excel?

Para generar letras aleatorias en Excel, debemos tomar en cuenta los códigos ASCII, por decir si nos ubicamos en una celda y usamos la combinación ALT+65, se generará la letra A mayúscula y, si usas la combinación ALT+90, se generará la letra Z mayúscula.

Ejemplo 2: Generar las letras vocales

letras aleatorias

¿Cómo generar números aleatorios en Excel dentro de un rango?

Ahora veamos como podemos generar números aleatorios en Excel dentro de un rango.

Primero veamos que cualquier de que tamaño es el rango, dependerá de eso para que nuestras funciones aleatorias se pongan en practica.

Ejemplo 3: Generar un número aleatorio del rango D79:D84

aleatorios en un rango

¿Cómo generar números aleatorios en Excel sin repetir?

La función ALEATORIO.ENTRE, tiene la posibilidad de generar número aleatorios repetidos, sin embargo, la función ALEATORIO, su probabilidad de generar un número positivo es sumamente remota.

Dependerá del objetivo que se tenga, debemos apoyarnos en otras funciones que den soporte a algunas funciones de búsqueda que podamos necesitar.

Ejemplo 4: Generar un dato aleatorio sin que se repitan

aletorios no repetidos

descarga

Autor

MOS Expert CPC Martín Cruz

Por Noticias , , , , , , 0 Comments Read more...