Noticias

El calendario 2019 Perú en Excel es un archivo que contiene información relacionada a:

1. Días feriados del año 2019.
2. Fechas de obligaciones mensuales (como ejemplo).
3. Lista de fechas festivas de un país (diferente a Perú) para las personas que trabajan en una empresa multinacional.

Por otro lado contiene 3 hojas con formatos de calendarios para posibles escenarios, con el siguiente detalle:

• Calendario Perpetuo – Editable
• Calendario Actual con feriados
• Calendario de obligaciones mensuales

»Descarga el calendario 2019 editable«

Calendario 2019 Perú con feriados en Excel

El calendario 2019 Perú tiene una hoja llamada DIAS la cual contiene una lista de fechas, entre días feriados y no laborables, para el uso de cualquier usuario.

Por otro lado contiene una hoja llamada Calendario Actual. totalmente imprimible a necesidad.

El calendario actual, tiene un formato para definir con que día de la semana inicia tu calendario, hay dos opciones:

Inicio lunes:

Inicio domingo:

Finalmente, esta hoja muestra el mes previo al mes actual. Cuando el archivo lo abras en octubre, el primer mes que se mostrará será septiembre.

Calendario 2019 Perú en Excel Plantilla

Este calendario de tipo plantilla, que no sólo contiene el calendario 2019 o 2020 sino que tiene una CELDA para indicar el año que deseamos ver como calendario, por lo que es editable y lo tienes disponible de forma perpetua.

Podrás revisar la formulación realizada a cada cuadro, así como el cuadro numérico base para realizar futuros calendarios personales.

 =(FECHANUMERO(“01/”&H9&”/”&B5)-DIASEM(FECHANUMERO(“01/”&H9&”/”&B5),2)+1)+Z11

Celda B5 es la celda relacionada al año

Celda H9 es la celda relacionada al mes.

Celda Z11 es la celda que forma parte de cuadro numérico para elaborar el calendario.

Las formulas se encuentran muy cerca con la que fue construida el cuadro numerico

=(FILAS(Z$20:Z20)-1)*7+COLUMNAS($Z20:Z20)-1

Calendario 2019 de obligaciones mensuales [feriados locales y casa matriz]

El calendario 2019 de obligaciones mensuales, te permite controlar tus obligaciones, tanto de declaración tributaria, como de obligación de reporting a la casa matriz.

Por otro lado, puede ver información a detalle de los propios feriados y obligaciones, relacionados al mes seleccionado.

En la parte inferior se muestra: el mes anterior, mes actual y el mes próximo.

Autor

MOS Expert CPC Martín Cruz

Para desarrollar FRECUENCIA en Excel, tenemos varios caminos, quizás donde podemos ejercer mejor control de los calculos sería utilizando la función FRECUENCIA de Excel.

Función FRECUENCIA de Excel

La función FRECUENCIA de Excel cuenta las veces que un valor se repite en un conjunto de datos.

Argumentos de la función FRECUENCIA

Argumento datos. Es el conjunto de datos de donde se desea evaluar las repeticiones.

Importante. Los datos deben ser numéricos, la función frecuencia omite los textos.

Argumento grupos. Es un rango de datos, de resumen, de los que se desea obtener la frecuencia o repeticiones.

La funcion FRECUENCIA de Excel entregará una matriz vertical de datos, la cantidad de elementos de grupos + 1.

FRECUENCIA en Excel Ejemplos

Queremos establecer la cantidad de pedidos por la cantidad que representa cada uno de ellos, para saber que cantidad es la que piden con mas frecuencia.

Veamos los datos

Los pedidos van de CERO a MIL, pero he desarrollado un cuadro que me informe sólo hasta pedidos de 700 y luego uno más, los mayores a 700, donde se incluirán los pedidos entre 700 y 1000 unidades.

Cómo hacer un Histograma de Frecuencia

La función FRECUENCIA al entregar una matriz vertical, necesita que se seleccione un rango para que los resultados que se generen se posicionen en este rango.

Frecuencia en Excel Descarga el archivo de trabajo

Una vez seleccionado el rango, donde se ubicaran los resultados, debe cubrir los argumentos de la función para finalmente presionar la combinación de teclas CTRL + SHIFT + ENTER, para realizar un trabajo matricial.

Finalmente, como se muestra en la imagen, los pedidos más frecuentes se dan entre 400 y 500 unidades, alcanzando la cantidad de 4 pedidos.

Debe tener en cuenta que si desea contar repeticiones de texto, la funcion FRECUENCIA no sería un funcion recomendada, ya que omite los textos. Alternativamente podría usar la funcion CONTAR.SI o CONTAR.SI.CONJUNTO dependiente de la cantidad de criterios.

Autor

MOS Expert CPC Martín Cruz

¿Qué es el administrador de escenarios de Excel?

El administrador de escenarios de Excel es una herramienta que nos permite realizar un análisis de nuestros datos, en el escenario del  “que pasaría si”.

Puede ser que me encuentre analizando información para intentar predecir el futuro. Dicho futuro que puede verse afectado por muchas situaciones y cada una de ellas es probable que suceda.

Entonces, podemos acudir a esta herramienta que nos permite revisar esos cambios en nuestras variables y mostrarnos como afectarían nuestros resultados.

>>Descarga archivo de trabajo<<

Ejemplo de administrador de escenarios de Excel

Tenemos información relacionada con los resultados de una operación. Pero estamos evaluando posibles cambios que podrían darse en los precios, costos y gastos.

• Escenario_1: Nuestro precio unitario es de 150 um pero tenemos una posibilidad de mejorar nuestro precio a 180 um desarrollando mejores estrategias de ventas, lo que nos llegará a incrementar nuestros Gastos de ventas a 25,000 um
• Escenario_2: Puede que la estrategia del escenario 1 no funcione y el precio de venta descaiga a 140 um manteniendo el incremento de Gastos de Ventas a 25,000 um
• Escenario_3: Con el escenario 2, pensamos que puede venir un reducción de nuestros Gastos Administrativos a 10,000 y el ajuste de la calidad del producto, por lo que el costo unitario del producto cambiaría a 75 um

Evaluemos los resultados

Bueno, en principio revisemos la ubicación de la opción. Administrador de escenarios se ubica en la ficha Datos, de acuerdo a como se muestra en la imagen.

Pasemos a la carga del primer escenario

Damos clic en Agregar e incorporamos los cambios que tendrán nuestras variables. A este escenario lo llamaremos

“Escenario 01: Estrategia de ventas Optimista”

Y las celdas cambiantes serán el precio unitario y los gastos de ventas.

Aquí definimos los valores que tendrán las celdas cambiantes. Quedamos que el precio subiría a 180 y los Gastos de Ventas a 25,000

Ahora vamos a agregar el segundo escenario

A este escenario lo llamaremos:

“Escenario 02: Estrategia de ventas Pesimista”

Aquí definimos los valores que tendrán las celdas cambiantes precio unitario y gastos de ventas. Quedamos que el precio bajaría a 140 y los Gastos de Ventas se mantendrían en 25,000.

Ahora vamos a agregar el tercer escenario

A este escenario lo llamaremos:

“Escenario 03: Reducción de calidad y gastos administrativos”

En este tercer escenario tenemos 4 variables cambiantes, el precio de venta, los gastos de ventas, los gastos administrativos y el costo unitario.

Los valores que tendrán las celdas cambiantes serán: para el precio de venta 140; los gastos de ventas 25,000; gastos administrativos 10,000; y costo unitario 75.

Finalmente, hemos cargado nuestros 3 escenarios.

Veamos como se muestra en el Administrador de Escenarios

Allí se muestran los 3 escenarios cargados.

Ahora presionaremos el botón Resumen y seleccionaremos el rango H5:H11, donde se encuentran los resultados que vamos a revisar.

Listo, ahora veremos los resultados.

Como se muestra en la imagen, obtenemos un resultado comparativo por Escenario. Las celdas no están vinculadas a la fuente de información pero se encuentra cargada en el Administrador de Escenarios.

Lo que esta sombreado significa que son las celdas cambiantes. No necesariamente los escenarios tienen que compartir variables, este es un caso.

>>Descarga archivo de trabajo<<

Artículos relacionados

• Tabla de datos guia completa

MOS Expert CPC Martín Cruz

¿Qué es tabla de datos de Excel?

Tabla de datos de Excel es una herramienta que permite analizar los resultados que podría generar el cambio de una variable hacia los resultados de nuestro negocio.

>>Descarga archivo de trabajo<<

¿Cómo hacer una tabla de datos en Excel?

En principio tenemos que saber que la herramienta tabla de datos tiene para analizar hasta un máximo de 2 variables (variables independientes), por lo que tendríamos que definir entre dos tipos de análisis:

1. Tabla de datos con una variables
2. Tabla de datos con dos variables

1. Tabla de datos con una variable

Esta opción permite analizar el efecto que generará su cambio, sobre los resultados que desee evaluar (los que necesite).

Importante. Cuando hablamos de una variable, nos referimos a una variable independiente y bajo este esquema se pueden analizar N variables dependientes.

Estructura de tabla de datos con una variable

Aquí no hay lugar a la creatividad, debemos trabajar siempre una estructura para este esquema.

Por decir, deseo saber…

¿que pasaría con mi utilidad operativa?

Si el costo unitario tiende a variar un 20%.

Primero observemos en que celda se encuentra nuestro costo unitario. Efectivamente, es la celda E13.

Debemos verificar si los cambios de valor de la celda E13 generan algún tipo de cambio sobre la Utilidad Operativa. Si no es de forma directa quizás de forma indirecta, pero el cambio de valor debe generar cambios en la celda H16 donde se encuentra el resultado de la utilidad operativa.

En este análisis vamos a incluir otros resultados, no tenemos un límite de variables dependientes, así que optaré por incluir:

• Utilidad bruta H13
• Utilidad operativa H16
• % UO / Ingresos H18

Dato importantes

1. Para la tabla de datos con una variable, debemos vincular las celdas donde se encuentran los resultados, tal como se muestra la imagen.
2. La variación del costo de venta unitario, del rango M12:M18 no debe estar vinculada por ningún motivo con la celda E13. Será mejor si lo trabajan como dato sin formulación.

Celda de entrada (columna)

Veamos que la variable costo unitario se encuentra en el rango M12:M18, en UNA COLUMNA, por lo que al momento de ejecutar tabla de datos deberá usar dicha casilla, tal como la imagen lo muestra.

Ejecute el siguiente procedimiento:

• Seleccione el área M11:P18.
• Vaya a la opción tabla de datos
• Use la casilla Celda de entrada (columna) y seleccione la celda E13.
• Clic en Aceptar

2. Tabla de datos con dos variables

Esta opción permite analizar el efecto que generará el cambio de estas dos variables, sobre un resultado que desee evaluar (sólo uno).

Importante. Cuando hablamos de dos variables, nos referimos a dos variables independientes y bajo este esquema se puede analizar sólo una variable independiente.

Estructura de tabla de datos con dos variables

Aquí tampoco no hay lugar a la creatividad, debemos trabajar siempre una estructura para este esquema.

Por decir, deseo saber…

¿que pasaría con mi utilidad operativa?

Si el costo unitario tiende a variar un 20% y mi cantidad vendida varia otro 20%.

En este escenario tenemos dos cambios. Primero mi costo unitario y segundo mi cantidad vendida, ambas son variables independientes.

Identifiquemos las celdas que contienen dichas variables independientes. Efectivamente, el costo unitario se encuentra en la celda E39 y la cantidad de ventas en E38.

Verifique en ambas celdas tengan efecto en el resultado que desea analizar.

Para este análisis no será posible analizar más que una variable dependiente, que en este caso será la Utilidad Operativa, cuya celda de resultado es H42.

Datos importantes

Para la tabla de datos con dos variables, debemos vincular las celda donde se encuentran el resultado, en la posición donde se muestra en la imagen.

La variación del costo de venta unitario, del rango M38:M44 no debe estar vinculada por ningún motivo con la celda E39, así como el rango N37:T37 no debe estar vinculado a la celda E38. Será mejor si lo trabajan como dato sin formulación.

Celda de entrada (fila) y celda de entrada (columna)

Veamos que la variable costo unitario se encuentra en el rango M38:M44, en UNA COLUMNA, por lo que al momento de ejecutar tabla de datos deberá usar dicha casilla, tal como la imagen lo muestra.

Ahora veamos que la variable cantidad se encuentra en el rango N37:T37, en UNA FILA, por lo que al momento de ejecutar tabla de datos deberá usar dicha casilla, tal como lo muestra la imagen.

Ejecute el siguiente procedimiento:

• Seleccione el área M37:T44.
• Vaya a la opción tabla de datos
• Use la casilla Celda de entrada (fila) y seleccione la celda E38.
• Use la casilla Celda de entrada (columna) y seleccione la celda E39.
• Clic en Aceptar

No hay mejor herramienta que tabla de datos para poder visualizar o proyectar variaciones.

>>Descarga archivo de trabajo<<

Artículos relacionados

• Desref y coincidir guía practica

MOS Expert CPC Martin Cruz

Extraer datos en Excel es una de las actividades más comunes que se hace en Excel, fuera de las actividades de búsqueda.

Extraer datos en Excel

Para la extracción de datos tenemos disponibles 3 funciones que pertenecen a la categoría de funciones de texto, las cuales son:

1. Izquierda (LEFT en inglés)
2. Derecha (RIGHT en inglés)
3. Extrae (MID en inglés)

IZQUIERDA se encarga de extraer datos desde la posición izquierda inicial de una cadena de texto.

DERECHA se encarga de extraer datos desde la posición derecha de una cadena de texto, cuya último caracter de una palabra, para la funcion DERECHA representa el primero.

EXTRAE tiene una actividad similar a la funcion IZQUIERDA, ya que extrae caracteres desde la posición izquierda de la cadena de texto, pero con una diferencia, que la funcion EXTRAE permite definir cuál es la posición inicial desde donde comenzará la extracción, a diferencia de la funcion IZQUIERDA que inicia la extracción, SI o SI, desde el primer caracter de la izquierda.

Dicho todo esto, veamos como funciona.

¿Cómo extraer datos en Excel?

Por decir tenemos una cadena de texto de 3 palabras unidas por espacios entre ellas.

Primera situación

Nuestro primer objetivo es extraer la palabra EJERCICIO y como la palabra se encuentra al inicio de la cadena de texto, usaremos la funcion IZQUIERDA. Como se muestra la imagen, la funcion IZQUIERDA requiere 2 argumentos, el primero sería de donde se va a extraer y el segundo que cantidad de caracteres va a extraer.

Allí vemos que la palabra EJERCICIO está compuesta de 9 caracteres, por lo que para la extracción usaremos

= IZQUIERDA (D4 , 9)

Segunda situación

Nuestro segundo objetivo, es extraer la palabra TEXTO y como esta palabra se encuentra al final de la cadena de texto, usaremos la funcion DERECHA.

Tal como se muestra en la imagen, la funcion DERECHA, al igual que la anterior funcion, requiere 2 argumentos, el primero sería la ubicación de donde extraerá y el segundo cuantos caracteres extraerá.

Allí vemos que la palabra TEXTO está compuesta de 5 caracteres, por lo que para la extracción usaremos

=DERECHA( D7 , 5 ).

Los 5 caracteres son contados de derecha a izquierda, tal como lo indica la marca en la imagen.

Tercera situación

Finalmente, nuestro tercer objetivo es extraer la palabra FUNCIONES y diferencia de los objetivos anteriores, esta palabra se encuentra en el centro de la cadena de texto, ninguna de las dos funciones anteriores podrá resolver este ejercicio.

Pero, como indique anteriormente, tenemos disponible la funcion EXTRAE, que nos permite definir un punto de extracción y una cantidad de caracteres a partir de ese punto.

Como se puede ver en la imagen, toda la cadena de texto tiene 25 caracteres y se cuentan inclusive los espacios en blanco.

La palabra FUNCIONES inicia en la posición 11 de toda la cadena de texto y tiene un total de 9 caracteres, desde ese punto.

Así pues, utilizaremos

= EXTRAE (D10 , 11 , 9 ) para extraer la palabra FUNCIONES.

Como vemos en los ejemplos, todas estas funciones de extracción, requieren una cantidad de caracteres o una posición, para poder extraer, por lo que si quisiéramos dinamizar el cálculo tendríamos que acudir a otras funciones que calculen dichos números y no realizarlo de manera visual.

Calculando posiciones y cantidad de caracteres

Extraer datos en Excel necesita el anidamiento de funciones para darle dinamismo al cálculo.

Para esta situación tenemos disponible las siguientes funciones:

1. funcion LARGO (LEN en ingles)
2. funcion HALLAR ( SEARCH en inglés)

La funcion LARGO determina la cantidad de caracteres que tiene una cadena de texto. Por decir, si la cadena de texto es “lunes”, la funcion LARGO nos dará como resultado 5, que indica 5 caracteres.

La funcion HALLAR, determina la posición de un caracter o cadena de texto, dentro de otra cadena de texto. Por decir, podríamos querer determinar la posición de la letra “t” dentro de “martes” en la celda A1, diríamos entonces =HALLAR( “t”, A1 , 1 ) y obtendríamos como resultado el número 4, que sería la posición de esa letra dentro de la cadena de texto.

Ahora pasemos a lo nuestro.

Veamos como calcularemos el número 9 que la función IZQUIERDA necesita.

En principio tenemos que definir la posición de un caracter común, separador de las palabras, en ese caso, el espacio.

Como usar la funcion HALLAR

Si vemos la imagen, el primer espacio se ubica en la posición 10 de la cadena de texto, por lo que utilizaremos la función HALLAR para determinar ese número para luego del resultado restarle 1 y así llegaremos al número 9 que necesita IZQUIERDA.

= HALLAR (” “, D19 , 1 ) – 1

Hallar el espacio dentro de la celda D19 desde la posición 1. Resultado 9

Ahora veamos como sería para la funcion DERECHA, necesitamos calcular el número 5 que necesita la funcion para extraer la palabra TEXTO.

Como funciona la funcion LARGO

Bueno aquí realizaremos debemos realizar una operación matemática. Vamos a restar la cantidad de caracteres que tiene la cadena de texto que en total son 25 para luego restarle la posición del segundo espacio, el cual se encuentra en la posición 20 de la cadena de texto.

= LARGO (D24)

Largo de D24 determina la cantidad de caracteres de la cadena de texto

= HALLAR (” ” , D24 , HALLAR (” “, D24 , 1) + 1)

Esta sería una versión anidada de la funcion HALLAR, de esta forma calcularíamos la posición del segundo espacio.

Hacemos la resta de ambos resultados y obtendremos el número 5.

Ahora veamos el último.

Con la función EXTRAE tenemos que pensar en sus dos argumentos, posición 11 y número de caracteres 9.

La posición 11 lo hallaremos con la funcion HALLAR,que ya hemos usado antes, para calcular la posición del primer espacio.

= HALLAR (” “, D29 , 1 )

Esta fórmula calculará la posición del primer espacio.

Veamos ahora el número de caracteres.

Usaremos el hallar anidado, que usamos anteriormente

= HALLAR (” ” , D29 , HALLAR (” “, D29 , 1) + 1)

Aquí calcularíamos la posición del segundo espacio.

En términos lógicos estamos haciendo lo siguiente:

= EXTRAE (D29 , pos. primer espacio + 1 (11) , pos. segundo espacio (20) menos pos. primer espacio (11) )

Listo, como verás una práctica bastante usada es el anidamiento de funciones, y no es exclusiva de la funcion SI.

Artículos relacionados

Como calcular el tiempo entre dos fechas

MOS Expert CPC Martín Cruz