Manual Excel Capítulo 3

Asesoría y Soluciones Informáticas

1 

Asesoría y Soluciones Informáticas

Capítulo 3 FUNCIONES DEL MICROSOFT EXCEL SIN EL ASISTENTE DE FORMULAS

El Microsoft Excel, nos brinda una serie de funciones predefinidas, que nos permitirán realizar cálculos y operaciones para nuestros trabajos de índole contable, financiero, estadístico, de fecha, lógicas, de cadenas, de búsquedas, entre otros.

Las funciones del Microsoft Excel, se pueden combinar con las formulas simple, que hemos venido aplicando en nuestros ejemplos anteriores.

En Este capítulo, aprenderemos a como trabajas con la funciones más utilizadas en el Microsoft Excel y su combinación con otras, sin uso del asistente de fórmulas.

Como sabemos el Microsoft Excel, permite elaborar cuadros de índole contable, financiero, estadístico, de ingeniería, etc. El objetivo del presente manual, es el desarrollo de la lógica en el empleo de las formulas, a fin que cada profesional lo aplique según su carrera y entorno de trabajo.

2 

Asesoría y Soluciones Informáticas

6. FUNCIONES EN MICROSOFT EXCEL. Como se explicó en la introducción, El Microsoft Excel, cuenta con una serie de funciones predefinidas, que nos facilitan el uso de operaciones para los cálculos y resultados de nuestros diferentes trabajos elaborados en la Hoja Electrónica.

6.1. Clasificación.

Las funciones por su uso y propósito se clasifican en grupos específicos, es decir, para elaborar cuadros de proyecciones se emplean funciones estadísticas, para cuadros que permiten calcular y hallar amortizaciones de préstamos o hipotecas, entre otros, se emplean funciones financieras, etc.

Las funciones del Microsoft Excel, se clasifican en las siguientes categorías: 6.1.1. Funciones Matemática y Trigonométrica Son las funciones más usadas en Excel, utilizadas para hacer operaciones numéricas simples o complejas.

Las funciones matemáticas se utilizan para realizar operaciones matemáticas con los valores numéricos contenidos en las celdas deseadas. Éstos cálculos pueden ser la suma, producto, obtener números enteros, logaritmos, redondeos, etc.

Las funciones trigonométricas sirven para calcular las razones trigonométricas, tales como seno, coseno hiperbólico, arcotangente.

Las fórmulas matemáticas y trigonométricas que permite Excel son las siguientes: ABS, Devuelve el valor absoluto de un número (prescindiendo del signo).

ACOS, Devuelve el arcocoseno de un número (en radianes).

ACOSH, Devuelve el coseno hiperbólico inverso de un número.

ALEATORIO, Devuelve un número aleatorio mayor o igual a 0 y menor que 1. También podemos obtener cada vez un número aleatorio entre 0 y 100.

ALEATORIO.ENTRE, Devuelve un número aleatorio entre dos números que se eligen.

ASENO, Devuelve el arcoseno de un número (en radianes).

ASENOH, Devuelve el seno hiperbólico inverso de un número.

ATAN, Devuelve la arcotangente de un número (en radianes).

ATAN2, Devuelve la arcotangente de un número en dos dimensiones, con coordenadas x e y.

ATANH, Devuelve la tangente hiperbólica inversa de un número.

COCIENTE, Devuelve el cociente (parte entera) de la división de dos números especificados.

COMBINAT, Número combinatorio. Combinaciones de n elementos tomados de m en m.

COS, Devuelve el coseno de un ángulo (en radianes).

COSH, Devuelve el coseno hiperbólico de un número.

ENTERO, Redondea un número hacia el entero menor más próximo.

EXP, Devuelve el número e elevado a una potencia especificada.

FACT Devuelve el factorial de un número.

FACT.DOBLE Devuelve el factorial doble de un número.

GRADOS Convierte radianes en grados.

LN Devuelve el logaritmo neperiano de un número.

LOG Devuelve el logaritmo de un número en la base deseada. 3 

Asesoría y Soluciones Informáticas

LOG10 Devuelve el logaritmo en base 10 de un número.

M.C.D Devuelve el máximo común divisor de un conjunto de números.

M.C.M Devuelve el mínimo común múltiplo de un conjunto de números.

MDETERM Devuelve el determinante de una matriz.

MINVERSA Devuelve la matriz inversa de una matriz.

MMULT Devuelve el producto matricial de dos matrices. La primera matriz debe tener el mismo número de columnas que filas la segunda matriz.

MULTINOMIAL Devuelve el coeficiente polinómico de un conjunto de números.

MULTIPLO.INFERIOR Redondea un número al múltiplo inferior más cercano de otro número, llamado cifra significativa. El número y la cifra significativa deben tener el mismo signo.

MULTIPLO.SUPERIOR Redondea un número al múltiplo superior más cercano de otro número, llamado cifra significativa. El número y la cifra significativa deben tener el mismo signo.

NUMERO.ROMANO Convierte un número en número romano (en formato de texto).

PI Devuelve el número π (con catorce decimales).

POTENCIA Devuelve el resultado de elevar un número a la potencia deseada.

PRODUCTO Devuelve el producto de los valores especificados.

RADIANES Convierte grados sexagesimales en radianes.

RAIZ Devuelve la raíz cuadrada positiva de un número.

RAIZ2PI Devuelve la raíz cuadrada positiva del producto de un número y π.

REDOND.MULT Devuelve el redondeo de un número al múltiplo deseado.

REDONDEA.IMPAR Redondea un número (positivo o negativo) hacia abajo al entero impar más próximo.

REDONDEA.PAR Redondea un número (positivo o negativo) hacia abajo al entero par más próximo.

REDONDEAR Redondea un número al número de decimales deseados.

REDONDEAR.MAS Redondea un número hacia arriba, al entero mayor más próximo (dirección contraria a cero), especificando los decimales deseados.

REDONDEAR.MENOS Redondea un número hacia abajo, al entero menor más próximo (dirección a cero), especificando los decimales deseados..

RESIDUO Devuelve el residuo de la división de un número entre un divisor. El cociente será entero y la función RESIDUO nos da el residuo o resto, también entero y del mismo signo que el divisor.

SEN Devuelve el seno de un ángulo (en radianes).

SENH Devuelve el seno hiperbólico de un número.

SIGNO Devuelve el signo de un número. 1 si es positivo, 0 si es cero y -1 si es negativo.

SUBTOTALES Devuelve los subtotales (operaciones parciales) resultantes a aplicar a determinados rangos una función o cálculo determinado. Se puede elegir entre una lista de 11 funciones SUMA Devuelve la suma de un rango de valores.

SUMA.CUADRADOS Devuelve la suma de los cuadrados de un rango de valores.

SUMA.SERIES Devuelve la suma de una serie de potencias.

SUMAPRODUCTOS Devuelve la suma de los productos de rangos o matrices numéricas.

SUMAR.SI Devuelve el sumatorio de los valores de un rango que cumplen una condición lógica determinada.

SUMAR.SI.CONJUNTO Devuelve el sumatorio de los valores de un rango que cumplan determinados criterios aplicados a los valores de otros rangos de valores.

SUMAX2MASY2 Devuelve la suma de los cuadrados de dos rangos o matrices de valores. En ambos rangos pueden haber números, texto, valores lógicos, etc. Las celdas que 4 

Asesoría y Soluciones Informáticas

no contengan números su cuadrado se evalúa como cero. El número de valores de los dos rangos debe ser idéntico.

SUMAX2MENOSY2 Devuelve la diferencia del sumatorio de los cuadrados de dos rangos o matrices de valores. Las condiciones son las mismas de la función anterior.

SUMAXMENOSY2 Devuelve la suma de los cuadrados de las diferencias de dos rangos o matrices de valores. Las condiciones son las mismas de la función anterior. Aquí suma el cuadrado de las diferencias. En cambio, en la anterior, suma la diferencia de los cuadrados.

TAN Devuelve la tangente de un ángulo (en radianes).

TANH Devuelve la tangente hiperbólica de un número.

TRUNCAR Elimina la parte decimal de un número, dejando sólo un número entero.

6.1.2. Funciones Estadísticas.

Permiten realizar cálculos estadísticos como calcular mediana, varianza mediana, desviación estándar, valores máximos, valores mínimos y más funciones relacionadas.

DESVPROM, Devuelve el promedio de las desviaciones absolutas de la media de los puntos de datos.

PROMEDIO, Devuelve el promedio de sus argumentos.

PROMEDIOA, Devuelve el promedio de sus argumentos, incluidos números, texto y valores lógicos.

PROMEDIO.SI, Devuelve el promedio (media aritmética) de todas las celdas de un rango que cumplen unos criterios determinados.

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

DISTR.BETA, Devuelve la función de distribución beta acumulativa.

DISTR.BETA.INV, Devuelve la función inversa de la función de distribución acumulativa de una distribución beta especificada.

DISTR.BINOM, Devuelve la probabilidad de una variable aleatoria discreta siguiendo una distribución binomial.

DISTR.CHI, Devuelve la probabilidad de una variable aleatoria continua siguiendo una distribución chi cuadrado de una sola cola.

PRUEBA.CHI.INV, Devuelve la función inversa de la probabilidad de una variable aleatoria continua siguiendo una distribución chi cuadrado de una sola cola.

PRUEBA.CHI, Devuelve la prueba de independencia.

INTERVALO.CONFIANZA, Devuelve el intervalo de confianza de la media de una población.

COEF.DE.CORREL, Devuelve el coeficiente de correlación entre dos conjuntos de datos.

CONTAR, Cuenta cuántos números hay en la lista de argumentos.

CONTARA, Cuenta cuántos valores hay en la lista de argumentos.

CONTAR.BLANCO, Cuenta el número de celdas en blanco de un rango.

CONTAR.SI, Cuenta el número de celdas, dentro del rango, que cumplen el criterio especificado.

CONTAR.SI.CONJUNTO, Cuenta el número de celdas, dentro del rango, que cumplen varios criterios.

COVAR, Devuelve la covarianza, que es el promedio de los productos de las desviaciones para cada pareja de puntos de datos.

BINOM.CRIT, Devuelve el menor valor cuya distribución binomial acumulativa es menor o igual a un valor de criterio. 5 

Asesoría y Soluciones Informáticas

DESVIA2, Devuelve la suma de los cuadrados de las desviaciones.

DISTR.EXP, Devuelve la distribución exponencial.

DISTR.F, Devuelve la distribución de probabilidad F. DISTR.F.INV, Devuelve la función inversa de la distribución de probabilidad F. FISHER, Devuelve la transformación Fisher.

PRUEBA.FISHER.INV, Devuelve la función inversa de la transformación Fisher.

PRONOSTICO, Devuelve un valor en una tendencia lineal.

FRECUENCIA, Devuelve una distribución de frecuencia como una matriz vertical.

PRUEBA.F, Devuelve el resultado de una prueba F. DISTR.GAMMA, Devuelve la distribución gamma.

DISTR.GAMMA.INV, Devuelve la función inversa de la distribución gamma acumulativa.

GAMMA.LN, Devuelve el logaritmo natural de la función gamma, G(x).

MEDIA.GEOM, Devuelve la media geométrica.

CRECIMIENTO, Devuelve valores en una tendencia exponencial.

MEDIA.ARMO, Devuelve la media armónica.

DISTR.HIPERGEOM, Devuelve la distribución hipergeométrica.

INTERSECCION.EJE, Devuelve la intersección de la línea de regresión lineal.

CURTOSIS, Devuelve la curtosis de un conjunto de datos.

K.ESIMO.MAYOR, Devuelve el k-ésimo mayor valor de un conjunto de datos.

ESTIMACION.LINEAL, Devuelve los parámetros de una tendencia lineal.

ESTIMACION.LOGARITMICA, Devuelve los parámetros de una tendencia exponencial.

DISTR.LOG.INV, Devuelve la función inversa de la distribución logarítmico-normal.

DISTR.LOG.NORM, Devuelve la distribución logarítmico-normal acumulativa.

MAX. Devuelve el valor máximo de una lista de argumentos.

MAXA, Devuelve el valor máximo de una lista de argumentos, incluidos números, texto y valores lógicos.

MEDIANA, Devuelve la mediana de los números dados.

MIN, Devuelve el valor mínimo de una lista de argumentos.

MINA, Devuelve el valor mínimo de una lista de argumentos, incluidos números, texto y valores lógicos.

MODA, Devuelve el valor más común de un conjunto de datos.

NEGBINOMDIST, Devuelve la distribución binomial negativa.

DISTR.NORM, Devuelve la distribución normal acumulativa.

DISTR.NORM.INV, Devuelve la función inversa de la distribución normal acumulativa.

DISTR.NORM.ESTAND, Devuelve la distribución normal estándar acumulativa.

DISTR.NORM.ESTAND.INV, Devuelve la función inversa de la distribución normal estándar acumulativa.

PEARSON, Devuelve el coeficiente de momento de correlación de producto Pearson.

PERCENTIL, Devuelve el k-ésimo percentil de los valores de un rango.

RANGO.PERCENTIL, Devuelve el rango porcentual de un valor de un conjunto de datos.

PERMUTACIONES, Devuelve el número de permutaciones de un número determinado de objetos.

POISSON, Devuelve la distribución de Poisson.

PROBABILIDAD, Devuelve la probabilidad de que los valores de un rango se encuentren entre dos límites.

CUARTIL, Devuelve el cuartil de un conjunto de datos.

JERARQUIA, Devuelve la jerarquía de un número en una lista de números.

COEFICIENTE.R2, Devuelve el cuadrado del coeficiente de momento de correlación de producto Pearson.

COEFICIENTE.ASIMETRIA, Devuelve la asimetría de una distribución. 6 

Asesoría y Soluciones Informáticas

PENDIENTE, Devuelve la pendiente de la línea de regresión lineal.

K.ESIMO.MENOR, Devuelve el k-ésimo menor valor de un conjunto de datos.

NORMALIZACION, Devuelve un valor normalizado.

DESVEST, Calcula la desviación estándar a partir de una muestra.

DESVESTA, Calcula la desviación estándar a partir de una muestra, incluidos números, texto y valores lógicos.

DESVESTP, Calcula la desviación estándar en función de toda la población.

DESVESTPA, Calcula la desviación estándar en función de toda la población, incluidos números, texto y valores lógicos.

ERROR.TIPICO.XY, Devuelve el error estándar del valor de “y” previsto para cada “x” de la regresión.

DISTR.T, Devuelve la distribución de t de Student.

DISTR.T.INV, Devuelve la función inversa de la distribución de t de Student.

TENDENCIA, Devuelve valores en una tendencia lineal.

MEDIA.ACOTADA, Devuelve la media del interior de un conjunto de datos.

PRUEBA.T, Devuelve la probabilidad asociada a una prueba t de Student.

VAR, Calcula la varianza en función de una muestra.

VARA, Calcula la varianza en función de una muestra, incluidos números, texto y valores lógicos.

VARP, Calcula la varianza en función de toda la población.

VARPA, Calcula la varianza en función de toda la población, incluidos números, texto y valores lógicos.

DIST.WEIBULL, Devuelve la distribución de Weibull.

PRUEBA.Z, Devuelve el valor de una probabilidad de una cola de una prueba z.

6.1.3. Funciones de Fecha y Hora.

Se utilizan cuando se requiere colocar en la hoja de trabajo la fecha y hora actual. Es posible obtener el día de la semana, año, número de la semana y más.

Además permiten calcular la diferencia entre en días entre dos fechas.

Las funciones de fecha y hora están divididas en dos grupos: Funciones que calculan el número de días, meses o años entre dos fechas.

Funciones que se pueden utilizar para agregar la fecha, hora o día de la semana a una hoja de cálculo.

Las funciones de fecha y hora son muy importantes para desarrollar informes financieros o estadísticos.

Las principales funciones de fecha y hora comúnmente utilizadas en Excel son: HOY, HORA, DIA, MES, AÑO. FECHA, Devuelve el número de serie correspondiente a una fecha determinada.

FECHANUMERO, Convierte una fecha con formato de texto en un valor de número de serie.

DIA, Convierte un número de serie en un valor de día del mes.

DIAS360, Calcula el número de días entre dos fechas a partir de un año de 360 días.

FECHA.MES, Devuelve el número de serie de la fecha equivalente al número indicado de meses anteriores o posteriores a la fecha inicial.

FIN.MES, Devuelve el número de serie correspondiente al último día del mes anterior o posterior a un número de meses especificado. 7 

Asesoría y Soluciones Informáticas

HORA, Convierte un número de serie en un valor de hora.

MINUTO, Convierte un número de serie en un valor de minuto.

MES, Convierte un número de serie en un valor de mes.

DIAS.LAB, Devuelve el número de todos los días laborables existentes entre dos fechas.

AHORA, Devuelve el número de serie correspondiente a la fecha y hora actuales.

SEGUNDO, Convierte un número de serie en un valor de segundo.

HORA, Devuelve el número de serie correspondiente a una hora determinada.

HORANUMERO, Convierte una hora con formato de texto en un valor de número de serie.

HOY, Devuelve el número de serie correspondiente al día actual.

DIASEM, Convierte un número de serie en un valor de día de la semana.

NUM.DE.SEMANA, Convierte un número de serie en un número que representa el lugar numérico correspondiente a una semana de un año.

DIA.LAB, Devuelve el número de serie de la fecha que tiene lugar antes o después de un número determinado de días laborables.

AÑO, Convierte un número de serie en un valor de año.

FRAC.AÑO, Devuelve la fracción de año que representa el número total de días existentes entre el valor de fecha_inicial y el de fecha_final.

6.1.4. Funciones Lógicas.

Son utilizadas para comparar valores y obtener un resultado de verdadero o falso de acuerdo a la comparación.

Las funciones lógicas permiten decidir si el curso de una acción si se cumplen o no ciertas condiciones. Puede que queramos aplicar una acción cuando se cumplen todas las restricciones o con que se cumpla una es suficiente.

Podemos utilizar las diferentes funciones lógicas para proceder en la base de datos según si los sujetos cumplen o no con ciertas pruebas lógicas. Estas pruebas podrán establecerse de manera directa o mediante referencia a otras celdas.

Las fórmulas lógicas son las siguientes: =FALSO(), Devuelve el valor lógico FALSO. =NO(), Cambia FALSO por VERDADERO y VERDADERO por FALSO =O(), Comprueba si un conjunto de pruebas lógicas son verdaderas. Si alguna lo es, devuelve VERDADERO, sino FALSO. =SI(), Comprueba si se cumple una prueba lógica que le especificamos. Devuelve un valor si esa prueba es verdadera y otro si es falsa. =SI.ERROR(), Devuelve un valor si la expresión da error y el valor original si no lo hay. =VERDADERO(), Devuelve el valor lógico VERDADERO. =Y( ), Comprueba si un conjunto de pruebas lógicas son verdaderas. Si todas lo son, devuelve VERDADERO, sino FALSO.

8 

Asesoría y Soluciones Informáticas

6.1.5. Funciones Financieras.

Sirven para realizar cálculos e informes financieros, incluyendo los cálculos de rendimiento, evaluaciones de inversión, tasas de interés, tasa de retorno, depreciación de activos y los pagos.

Las funciones financieras de Excel permiten organizar más fácilmente el cálculo, administración y análisis de las finanzas tanto personales como para negocios.

Las funciones financieras también ayudan en la contabilidad de pequeñas empresas y grandes. Las funciones financieras de Excel se pueden utilizar para determinar los cambios en la moneda durante el análisis de inversiones o préstamos.

Las principales funciones financieras comúnmente utilizadas en Excel son: VF, PAGO, VA, TASA, NPER INT.ACUM, Devuelve el interés acumulado de un valor bursátil con pagos de interés periódicos.

INT.ACUM.V, Devuelve el interés acumulado de un valor bursátil con pagos de interés al vencimiento.

AMORTIZ.PROGRE, Devuelve la amortización de cada período contable mediante el uso de un coeficiente de amortización.

AMORTIZ.LIN, Devuelve la amortización de cada uno de los períodos contables.

CUPON.DIAS.L1, Devuelve el número de días desde el principio del período de un cupón hasta la fecha de liquidación.

CUPON.DIAS, Devuelve el número de días del período (entre dos cupones) donde se encuentra la fecha de liquidación.

CUPON.DIAS.L2, Devuelve el número de días desde la fecha de liquidación hasta la fecha del próximo cupón.

CUPON.FECHA.L2, Devuelve la fecha del próximo cupón después de la fecha de liquidación.

CUPON.NUM, Devuelve el número de pagos de cupón entre la fecha de liquidación y la fecha de vencimiento.

CUPON.FECHA.L1, Devuelve la fecha de cupón anterior a la fecha de liquidación.

PAGO.INT.ENTRE, Devuelve el interés acumulado pagado entre dos períodos.

PAGO.PRINC.ENTRE, Devuelve el capital acumulado pagado de un préstamo entre dos períodos.

DB, Devuelve la amortización de un bien durante un período específico a través del método de amortización de saldo fijo.

DDB, Devuelve la amortización de un bien durante un período específico a través del método de amortización por doble disminución de saldo u otro método que se especifique.

TASA.DESC, Devuelve la tasa de descuento de un valor bursátil.

MONEDA.DEC, Convierte una cotización de un valor bursátil expresada en forma fraccionaria en una cotización de un valor bursátil expresada en forma decimal.

MONEDA.FRAC, Convierte una cotización de un valor bursátil expresada en forma decimal en una cotización de un valor bursátil expresada en forma fraccionaria.

DURACION, Devuelve la duración anual de un valor bursátil con pagos de interés periódico.

INT.EFECTIVO, Devuelve la tasa de interés anual efectiva.

VF, Devuelve el valor futuro de una inversión.

VF.PLAN, Devuelve el valor futuro de un capital inicial después de aplicar una serie de tasas de interés compuesto. 9 

Asesoría y Soluciones Informáticas

TASA.INT, Devuelve la tasa de interés para la inversión total de un valor bursátil.

PAGOINT, Devuelve el pago de intereses de una inversión durante un período determinado.

TIR, Devuelve la tasa interna de retorno para una serie de flujos de efectivo periódicos.

INT.PAGO.DIR, Calcula el interés pagado durante un período específico de una inversión.

DURACION.MODIF, Devuelve la duración de Macauley modificada de un valor bursátil con un valor nominal supuesto de 100 $.

TIRM, Devuelve la tasa interna de retorno donde se financian flujos de efectivo positivos y negativos a tasas diferentes.

TASA.NOMINAL, Devuelve la tasa nominal de interés anual.

NPER, Devuelve el número de períodos de una inversión.

VNA, Devuelve el valor neto actual de una inversión en función de una serie de flujos periódicos de efectivo y una tasa de descuento.

PRECIO.PER.IRREGULAR.1, Devuelve el precio por un valor nominal de 100 $ de un valor bursátil con un primer período impar.

RENDTO.PER.IRREGULAR.1, Devuelve el rendimiento de un valor bursátil con un primer período impar.

PRECIO.PER.IRREGULAR.2, Devuelve el precio por un valor nominal de 100 $ de un valor bursátil con un último período impar.

RENDTO.PER.IRREGULAR.2, Devuelve el rendimiento de un valor bursátil con un último período impar.

PAGO, Devuelve el pago periódico de una anualidad.

PAGOPRIN, Devuelve el pago de capital de una inversión durante un período determinado.

PRECIO, Devuelve el precio por un valor nominal de 100 $ de un valor bursátil que paga una tasa de interés periódico.

PRECIO.DESCUENTO, Devuelve el precio por un valor nominal de 100 $ de un valor bursátil con descuento.

PRECIO.VENCIMIENTO, Devuelve el precio por un valor nominal de 100 $ de un valor bursátil que paga interés a su vencimiento.

VALACT, Devuelve el valor actual de una inversión.

TASA, Devuelve la tasa de interés por período de una anualidad.

CANTIDAD.RECIBIDA, Devuelve la cantidad recibida al vencimiento de un valor bursátil completamente invertido.

SLN, Devuelve la amortización por método directo de un bien en un período dado.

SYD, Devuelve la amortización por suma de dígitos de los años de un bien durante un período especificado.

LETRA.DE.TES.EQV.A.BONO, Devuelve el rendimiento de un bono equivalente a una letra del Tesoro (de EE.UU.) LETRA.DE.TES.PRECIO, Devuelve el precio por un valor nominal de 100 $ de una letra del Tesoro (de EE.UU.) LETRA.DE.TES.RENDTO, Devuelve el rendimiento de una letra del Tesoro (de EE.UU.) DVS, Devuelve la amortización de un bien durante un período específico o parcial a través del método de cálculo del saldo en disminución.

TIR.NO.PER, Devuelve la tasa interna de retorno para un flujo de efectivo que no es necesariamente periódico.

VNA.NO.PER, Devuelve el valor neto actual para un flujo de efectivo que no es necesariamente periódico.

RENDTO, Devuelve el rendimiento de un valor bursátil que paga intereses periódicos.

RENDTO.DESC, Devuelve el rendimiento anual de un valor bursátil con descuento; por ejemplo, una letra del Tesoro (de EE.UU.)  10 

Asesoría y Soluciones Informáticas

RENDTO.VENCTO, Devuelve el rendimiento anual de un valor bursátil que paga intereses al vencimiento.

6.1.6. Funciones de Búsqueda y Referencia.

Permiten localizar datos de acuerdo a criterios establecidos en la búsqueda.

Las funciones de búsqueda y referencia permiten encontrar valores que coincidan con los criterios establecidos en la búsqueda.

Las fórmulas de búsqueda y referencia son las siguientes: AREAS, Devuelve el número de áreas de una referencia. Un área es un rango de celdas contiguas o una única celda.

BUSCAR, Busca valores de un rango de una columna o una fila o desde una matriz.

CONSULTAH, Busca en la primera fila de una tabla o matriz de valores y devuelve el valor en una misma columna de la fila especificada. La utilizamos para buscar en una tabla cuando los campos son filas y los registros columnas.

CONSULTAV, Busca un valor en la primera columna de la izquierda de una tabla y devuelve el valor en la misma fila desde una columna especificada. De forma predeterminada, la tabla se ordena de forma ascendente. La utilizamos para buscar en una tabla cuando los campos son columnas y los registros filas.

COINCIDIR, Devuelve la posición relativa de un elemento en la matriz, que coincide con un valor dado en un orden especificado.

COLUMNA, Devuelve el número de columna de una referencia.

COLUMNAS, Devuelve el número de columnas en una matriz o referencia.

DESREF, Devuelve una referencia a una celda o un rango de celdas que ha sido desplazada en base a otra referencia dada.

DIRECCION, Crea una referencia de celda en forma de texto una vez especificados los números de fila y columna.

ELEGIR, Elige un valor o una acción de una lista de valores a partir de una número de índice.

FILA, Devuelve el número de fila de una referencia.

FILAS, Devuelve el número de filas de una referencia o matriz.

HIPERVINCULO, Crea un acceso directo o salto que abre un documento guardado en el disco duro, en un servidor de red o en Internet.

IMPORTARDATOSDINAMICOS, Extrae datos almacenados en una tabla dinámica.

INDICE, Devuelve un valor o referencia de la celda en la intersección de una fila y columna en particular, en un rango especificado.

INDIRECTO, Devuelve una referencia especificada por un valor de texto.

RDTR, Recupera datos en tiempo real de un programa compatible con automatizaciones COM. TRANSPONER, Devuelve un rango vertical de celdas como un rango horizontal, o viceversa.

 11 

Asesoría y Soluciones Informáticas

6.1.7. Funciones de Texto.

Las funciones de texto permiten trabajar con datos alfanuméricos, permiten la comparación de texto, cambio de formato, cambiar mayúsculas o minúsculas, concatenar texto y más.

Las principales funciones de texto utilizadas en Excel son:: CONCATENAR, IGUAL, LIMPIAR, MINUSC y MAYUSC. ASC, Convierte las letras inglesas o katakana de ancho completo (de dos bytes) dentro de una cadena de caracteres en caracteres de ancho medio (de un byte).

TEXTOBAHT, Convierte un número en texto, con el formato de moneda ß (Baht).

CARÁCTER, Devuelve el carácter especificado por el número de código.

LIMPIAR, Quita del texto todos los caracteres no imprimibles.

CODIGO, Devuelve un código numérico del primer carácter de una cadena de texto.

CONCATENAR, Concatena varios elementos de texto en uno solo.

MONEDA, Convierte un número en texto, con el formato de moneda $ (dólar).

IGUAL, Comprueba si dos valores de texto son idénticos.

ENCONTRAR, ENCONTRARB, Busca un valor de texto dentro de otro (distingue mayúsculas de minúsculas).

DECIMAL, Da formato a un número como texto con un número fijo de decimales.

JIS, Convierte las letras inglesas o katakana de ancho medio (de un byte) dentro de una cadena de caracteres en caracteres de ancho completo (de dos bytes).

IZQUIERDA, IZQUIERDAB, Devuelve los caracteres del lado izquierdo de un valor de texto.

LARGO, LARGOB, Devuelve el número de caracteres de una cadena de texto.

MINUSC, Pone el texto en minúsculas.

EXTRAE, EXTRAEB, Devuelve un número específico de caracteres de una cadena de texto que comienza en la posición que se especifique.

FONETICO, Extrae los caracteres fonéticos (furigana) de una cadena de texto.

NOMPROPIO, Pone en mayúscula la primera letra de cada palabra de un valor de texto.

REEMPLAZAR, REEMPLAZARB, Reemplaza caracteres de texto.

REPETIR, Repite el texto un número determinado de veces.

DERECHA, DERECHAB, Devuelve los caracteres del lado derecho de un valor de texto.

HALLAR, HALLARB, Busca un valor de texto dentro de otro (no distingue mayúsculas de minúsculas).

SUSTITUIR, Sustituye texto nuevo por texto antiguo en una cadena de texto.

T, Convierte sus argumentos a texto.

TEXTO, Da formato a un número y lo convierte en texto.

ESPACIOS, Quita los espacios del texto.

MAYUSC, Pone el texto en mayúsculas.

VALOR, Convierte un argumento de texto en un número.

 12 

Asesoría y Soluciones Informáticas

6.1.8. Funciones de Información.

Indican las características del estado de diferentes partes de tu hoja de cálculo, permite comprobar si una celda está vacía o no y otras funciones más. Las principales funciones de información comúnmente utilizadas en Excel son: ESNUMERO, ESTEXTO, ESLOGICO y ESERROR. CELDA, Devuelve información acerca del formato, la ubicación o el contenido de una celda.

TIPO.DE.ERROR, Devuelve un número que corresponde a un tipo de error.

INFO, Devuelve información acerca del entorno operativo en uso.

ESBLANCO, Devuelve VERDADERO si el valor está en blanco.

ESERR, Devuelve VERDADERO si el valor es cualquier valor de error excepto #N/A. ESERROR, Devuelve VERDADERO si el valor es cualquier valor de error.

ES.PAR, Devuelve VERDADERO si el número es par.

ESLOGICO, Devuelve VERDADERO si el valor es un valor lógico.

ESNOD, Devuelve VERDADERO si el valor es el valor de error #N/A. ESNOTEXTO, Devuelve VERDADERO si el valor no es texto.

ESNUMERO, Devuelve VERDADERO si el valor es un número.

ES.IMPAR, Devuelve VERDADERO si el número es impar.

ESREF, Devuelve VERDADERO si el valor es una referencia.

ESTEXTO, Devuelve VERDADERO si el valor es texto.

N, Devuelve un valor convertido en un número.

ND, Devuelve el valor de error #N/A. TIPO, Devuelve un número que indica el tipo de datos de un valor.

6.1.9. Funciones de Ingeniería Permiten realizar cálculos relacionados al campo de la ingeniería, así como modelos basados en fórmulas utilizadas en ingeniería.

BESSELI, Devuelve la función Bessel In(x) modificada.

BESSELJ, Devuelve la función Bessel Jn(x).

BESSELK, Devuelve la función Bessel Kn(x) modificada.

BESSELY, Devuelve la función Bessel Yn(x).

BIN.A.DEC, Convierte un número binario en decimal.

BIN.A.HEX, Convierte un número binario en hexadecimal.

BIN.A.OCT, Convierte un número binario en octal.

COMPLEJO, Convierte coeficientes reales e imaginarios en un número complejo.

CONVERTIR, Convierte un número de un sistema de medida a otro.

DEC.A.BIN, Convierte un número decimal en binario.

DEC.A.HEX, Convierte un número decimal en hexadecimal.

DEC.A.OCT, Convierte un número decimal en octal.

DELTA, Comprueba si dos valores son iguales.

FUN.ERROR, Devuelve la función de error.

FUN.ERROR.COMPL, Devuelve la función de error complementario.

MAYOR.O.IGUAL, Comprueba si un número es mayor que un valor de umbral.

HEX.A.BIN, Convierte un número hexadecimal en binario.

HEX.A.DEC, Convierte un número hexadecimal en decimal.  13 

Asesoría y Soluciones Informáticas

HEX.A.OCT, Convierte un número hexadecimal en octal.

IM.ABS, Devuelve el valor absoluto (módulo) de un número complejo.

IMAGINARIO, Devuelve el coeficiente imaginario de un número complejo.

IM.ANGULO, Devuelve el argumento theta, un ángulo expresado en radianes.

IM.CONJUGADA, Devuelve la conjugada compleja de un número complejo.

IM.COS, Devuelve el coseno de un número complejo.

IM.DIV, Devuelve el cociente de dos números complejos.

IM.EXP, Devuelve el valor exponencial de un número complejo.

IM.LN, Devuelve el logaritmo natural (neperiano) de un número complejo.

IM.LOG10, Devuelve el logaritmo en base 10 de un número complejo.

IM.LOG2, Devuelve el logaritmo en base 2 de un número complejo.

IM.POT, Devuelve un número complejo elevado a una potencia entera.

IM.PRODUCT, Devuelve el producto de números complejos.

IM.REAL, Devuelve el coeficiente real de un número complejo.

IM.SENO, Devuelve el seno de un número complejo.

IM.RAIZ2, Devuelve la raíz cuadrada de un número complejo.

IM.SUSTR, Devuelve la diferencia entre dos números complejos.

IM.SUM, Devuelve la suma de números complejos.

OCT.A.BIN, Convierte un número octal en binario.

OCT.A.DEC, Convierte un número octal en decimal.

OCT.A.HEX, Convierte un número octal en hexadecimal.

6.1.10. Funciones de Complementos y Automatización Permiten manejar datos de tablas dinámicas o vínculos dinámicos.

La función EUROCONVERT convierte un convierte un número dado de una moneda a de otra moneda utilizando el euro como moneda intermedia.

Las principales funciones Complementos y Automatización más comúnmente utilizados en Excel son: LLAMAR, EUROCONVERT y SQL.REQUEST LLAMAR, Llama a un procedimiento de una biblioteca de vínculos dinámicos o de un recurso de código.

EUROCONVERT, Convierte un número determinado a euros; convierte un número determinado de euros a la moneda de un estado miembro; o convierte un número dado de una moneda de un estado miembro a la de otro con el euro como moneda intermedia (triangulación) IMPORTARDATOSDINAMICOS, Devuelve los datos almacenados en un informe de tabla dinámica.

ID.REGISTRO, Devuelve el número de identificación del registro de la biblioteca de vínculos dinámicos (DLL) especificada o del recurso de código previamente registrado.

SQL.REQUEST, Establece conexión con un origen de datos externo, ejecuta una consulta desde una hoja de cálculo y, a continuación, devuelve el resultado en forma de matriz sin necesidad de programar una macro

 14 

Asesoría y Soluciones Informáticas

6.1.11. Funciones de Base de Datos Permiten realizar operaciones con los datos localizados en una base de datos que puede estar almacenada en una hoja en Excel o en alguna otra fuente externa. Es posible realizar operaciones para obtener el total, promedio, mínimo o máximo, además de contar o sumar el contenido del campo (columna o fila) de una base de datos en particular, esto es siempre que y cuando los criterios de verificación se cumplan.

Si te das cuenta, Excel utiliza una versión de funciones para base de datos que realizan operaciones definidas en las funciones matemáticas y estadísticas como: Suma, Min, Max, Promedio y Contar.

Cada una de estas funciones comienza con “DB” que proviene de base de datos: DBsuma, DBmin, DBmax, DBPromedio y DBContar.

En otras palabras, las funciones de base de datos en Excel se utilizan para interrogar, comparar, verificar o calcular los datos contenidos en una hoja de cálculo.

Las principales funciones de Base de Datos más comúnmente utilizadas en Excel son: DBPROMEDIO, DBCONTAR, DBMAX, DBMIN, DBSUMA BDPROMEDIO, Devuelve el promedio de las entradas seleccionadas en la base de datos.

BDCONTAR, Cuenta el número de celdas que contienen números en una base de datos.

BDCONTARA, Cuenta el número de celdas no vacías en una base de datos.

BDEXTRAER, Extrae de una base de datos un único registro que cumple los criterios especificados.

BDMAX, Devuelve el valor máximo de las entradas seleccionadas de la base de datos.

BDMIN, Devuelve el valor mínimo de las entradas seleccionadas de la base de datos.

BDPRODUCTO, Multiplica los valores de un campo concreto de registros de una base de datos que cumplen los criterios especificados.

BDDESVEST, Calcula la desviación estándar a partir de una muestra de entradas seleccionadas en la base de datos.

BDDESVESTP, Calcula la desviación estándar en función de la población total de las entradas seleccionadas de la base de datos.

BDSUMA, Suma los números de la columna de campo de los registros de la base de datos que cumplen los criterios.

BDVAR, Calcula la varianza a partir de una muestra de entradas seleccionadas de la base de datos.

BDVARP, Calcula la varianza a partir de la población total de entradas seleccionadas de la base de datos.

 15 

Asesoría y Soluciones Informáticas

6.1.12. Funciones de Cubo Utilizadas para el análisis avanzado en cubos de información.

Recordemos que un cubo de información es una matriz de datos entendidas, es decir que los datos están organizados multidimensionalmente pero almacenados en una sola fuente o base de datos.

El término “cubo” en informática se refiere a un método para analizar el contenido de una base de datos para aprender más acerca de su negocio.

Las funciones de Cubo son utilizadas en Excel avanzado y sirven en gran medida en la toma de decisiones a nivel gerencial.

MIEMBROKPICUBO, Devuelve un nombre, propiedad y medida de indicador de rendimiento clave (KPI) y muestra el nombre y la propiedad en la celda. Un KPI es una medida cuantificable, como los beneficios brutos mensuales o la facturación trimestral por empleado, que se usa para supervisar el rendimiento de una organización.

MIEMBROCUBO, Devuelve un miembro en una jerarquía de cubo. Se usa para validar la existencia del miembro en el cubo.

PROPIEDADMIEMBROCUBO, Devuelve el valor de una propiedad de miembro del cubo Se usa para validar la existencia de un nombre de miembro en el cubo y para devolver la propiedad especificada para este miembro.

MIEMBRORANGOCUBO, Devuelve el miembro n, o clasificado, de un conjunto. Se usa para devolver uno o más elementos de un conjunto, por ejemplo, el representante con mejores ventas o los diez mejores alumnos.

CONJUNTOCUBO, Define un conjunto calculado de miembros mediante el envío de una expresión de conjunto al cubo en el servidor, lo que crea el conjunto y, después, devuelve dicho conjunto a Microsoft Office Excel.

RECUENTOCONJUNTOCUBO, Devuelve el número de elementos de un conjunto.

VALORCUBO, Devuelve un valor agregado de un cubo.

6.1.13 Funciones Definidas por el usuario Son funciones creadas utilizando el VBA Excel, conocidas como macros. Cabe recordar que estas funciones pueden ser empleadas desde cualquier parte del proyecto, ya sean las hojas, ThisWorbook, módulos, y formularios, y si son empleadas desde las hojas la invocación se realiza de la misma forma que se procede para las funciones ya existentes como ConsultarV, Concatenar, etc.

 16 

Asesoría y Soluciones Informáticas

6.2. APLICANDO LAS FUNCIONES MÁS UTILIZADAS. Como explicamos al inicio del presente capítulo, aplicaremos el uso de funciones más empleadas del Microsoft Excel, sin uso del asistente de fórmulas, para nuestros cuadros que hemos realizado anteriormente. Las funciones a emplear no tendrá un orden alfabético, ni lógico, sino será por necesidad de acuerdo a la exigencia y necesidad de los cuadros a desarrollar.

Caso Práctico Nº4.

Para el desarrollo de la presente práctica, emplearemos nuestro cuadro elaborado anteriormente, en el caso Nº 1 (planilla de notas). Ha este cuadro le agregaremos una columna nueva para la “Condición final” y asimismo dos campos nuevos “Alumnos aprobados” y “Alumnos desaprobados”. Recupere el cuadro y agregue los nuevos, para que se muestre como en la siguiente figura:

6.2.1. Funciones a utilizar en la presente práctica.

Se emplearan dos funciones: =Si(), para evaluar y mostrar los mensajes de alumnos aprobado y alumno desaprobado respectivamente. Asimismo =Promedio(), para calcular el promedio de notas (=Promedio()).

6.2.1.1. Consideraciones generales: Condición final, En esta columna, el Microsoft Excel debe de mostrar de forma automática, la condición del alumno, tomando en cuenta la siguiente evaluación: Si la celda de Promedio final es mayor e igual a 11, entonces que se muestre el mensaje “Alumnos aprobado”, de no ser así, que muestre el mensaje “Alumnos desaprobado” Si observas detenidamente la evaluación, puedes observar que se emplea una condicional llamada “Si”, que en Microsoft Excel, viene a ser una función lógica llamada =Si().

Luego una comparación, es decir se empleara un operador de comparación. Por consecuencia para la solución se empleara la función lógica =Si(), la cual incluirá un operador de comparación.  17 

Asesoría y Soluciones Informáticas

Pago x sustitutorio, Si el alumno está aprobado no paga nada, de lo contrario pagara S/.50.00 por derecho de examen.

6.2.1.2. Función Lógica =Si().

Esta función, se emplea para solucionar problemas en donde la evaluación, contenga una condicional “Si”. Es de tipo lógico. Esta función cuenta con un apartado de condición que se evalúa mediante una comparación al contenido de una celda y realiza una acción si la condición evaluada es verdadera y otra si es falsa.

Sintaxis de uso para mostrar cadenas. =Si(Condición,”Mensaje1”,”Mensaje2”) Se lee como: Si la condición es verdadera, entonces mostrara “Mensaje1”, de lo contrario mostrara “Mesaje2”.

Condición, es la evaluación, mediante la comparación de una celda con un determinado numérico o de tipo cadena.

Primera coma “,”. Para una mejor comprensión de la lectura de la función, la primera coma se le como “entonces”.

“Mensaje1”, es la cadena de caracteres que se mostrara si el resultado de la condición sea verdadero.

Segunda coma “,”.Para una mejor comprensión de la lectura de la función, la segunda coma se le como “de lo contrario”.

“Mesaje2”, es la cadena de caracteres que se mostrara si el resultado de la condición sea falso.

Sintaxis de uso para mostrar valores. =Si(Condición,Valor1,Valor2) Se lee como: Si la condición es verdadera, entonces mostrara “Valor1, de lo contrario mostrara Valor2.

Condición, es la evaluación, mediante la comparación de una celda con un determinado numérico o de tipo cadena.

Primera coma “,”. Para una mejor comprensión de la lectura de la función, la primera coma se le como “entonces”.

Valor1, es el valor numérico que se mostrara si el resultado de la condición sea verdadero.

Este valor puede ser también un grupo de operaciones u otra función.

Segunda coma “,”.Para una mejor comprensión de la lectura de la función, la segunda coma se le como “de lo contrario”.

Valor2, es el valor numérico que se mostrara si el resultado de la condición sea falso. Este valor puede ser también un grupo de operaciones u otra función.

 18 

Asesoría y Soluciones Informáticas

6.2.1.3. Función Matemática =Promedio().

Esta función, se emplea para hallar el promedio aritmético de los valores de un determinado grupo de celdas, sumando el contenido de las celdas indicadas y lo divide entre el número de la cantidad de celdas que se están sumando.

Sintaxis del uso de la Función =Promedio(). =Promedio(rangoDeCeldas) rangoDeCeldas, representa las direcciones de celdas en donde se encuentran los valores numéricos de quienes se desea hallar el promedio aritmético.

6.2.1.3.1. Aplicando lo aprendido en nuestro cuadro ejemplo.

Recupere o abra su trabajo de la planilla de notas e ingrese las funciones para mostrar el promedio, los mensajes y asimismo los pagos respectivamente.

6.2.1.3.2. Hallar Promedio final, estando en la celda de Promedio final (I6), debemos de ingresar la fórmula para hallar el promedio de las celdas que conforman las evaluaciones. a Ubica el cursor en esta celda y digitamos la función para hallar el promedio de las notas obtenidas, de la siguiente manera: =Promedio(D6:H6) y pulsa enter..

b

d

Ubica el cursor en esta celda y digitamos la función para hallar mostrar los mensajes, evaluando la celda del promedio final, de la siguiente manera:

=Si(I6>=11,”Alumno Aprobado”,”Alumno Desaprobado”) Luego pulsa enter.

Ubica el cursor en esta celda y digitamos la función para hallar el pago sustitutorio, de la siguiente manera:

=Si(I6>=11,0,50) Luego pulsamos enter

No olvide que después de ingresar cada formula, copie la formula hacia abajo, de la manera ya aprendida anteriormente, de no ser así, tendría que estar ingresando la fórmula para cada celda que la necesite.

 19 

Asesoría y Soluciones Informáticas

6.2.1.3.3. Hallar Reporte de Totales, como se aprecia en la figura anterior, ahora tenemos que calcular el número total de alumnos aprobados y desaprobados respectivamente, para ello emplearemos el uso de una función Estadística =Contar.si().

6.2.1.3.4. Función Estadística =Contar.si().

Esta función es tipo estadístico. Se emplea para contar en un rango de celdas, el número de coincidencias, que cumplan con la condición.

6.2.1.3.4.1. Sintaxis del uso de la Función =Contar.si(). =Contar.si(rangoDeCeldas;”Condición”) rangoDeCeldas, representa al conjunto de celdas que contiene los valores numéricos o alfabéticos, que se contaran, cuando la condición sea verdadera, es decir, se cumpla.

Condición, Es la condición que se debe cumplir, para que esta función pueda contar en el rango de celdas indicadas.

6.2.1.3.4.2. Ejemplos: =Contar.si(A5:A20;”>=18”), Este ejemplo contara en el rango de celdas A5:A20, solo aquellos valores que sean mayores e iguales al valor 18. =Contar.si(G5:G20;”=Alumno Aprobado”), Este ejemplo contara en el rango de celdas G5:G20, solo aquellas celdas que contengas la cadena “Alumno Aprobado”. Recuerde que la cadena debe ser exacta.

Como tenemos que hallar los totales de aprobados y desaprobados, entonces debemos observar que el rango de celdas que contiene dichos caracteres, es el campo de “Condición final” y está comprendida de desde la celda I6 hasta la celda I10 (I6:I10). Para aplicar esta función realice lo que se le indica en la siguiente imagen: Ubica el cursor en esta celda y digitamos la función para contar las celdas cuyo contenido sea igual a: “Alumno Aprobado” de la siguiente manera:

=Contar.si(I6:I10;”=Alumnos Aprobado”) Luego pulsa enter.

e

f Ubica el cursor en esta celda y digitamos la función para contar las celdas cuyo contenido sea igual a: “Alumno Aprobado” de la siguiente manera:

=Contar.si(I6:I10;”=Alumnos Aprobado”) Luego pulsa enter.

 20 

Asesoría y Soluciones Informáticas

6.2.1.3.5. Función Estadística =Contara().

Esta función hace un recuento de aquellas celdas que no están vacías dentro de un rango especificado. Por ejemplo, considera el siguiente caso:

Dentro del rango A1:A10 existen 3 celdas que están vacías por lo que la función CONTARA regresa el valor 7, que corresponde al número de celdas que no están vacías.

6.2.1.3.6. Función Estadística =Contar().

Trabaja de forma similar a la función anterior, esta nos permite contar las celdas ocupadas por valores numéricos, en un rango de celdas indicadas.

A diferencia del ejemplo anterior, en esta ocasión todas las celdas están llenas pero no todas tienen un valor numérico por lo que la función CONTAR regresa el valor 5 que es el número de celdas dentro del rango que tienen un valor numérico.

6.2.1.3.7. Función Estadística =Contar.blanco().

La última función que mencionaré en esta ocasión es la función CONTAR.BLANCO la cual cuenta las celdas que están vacías dentro de un rango. Esta función regresa el número opuesto al regresado por la función CONTARA. Observa con detenimiento el siguiente ejemplo el cual es el mismo que para la función CONTARA solo que ahora utilizando la función CONTAR.BLANCO:

Observa cómo ahora la función regresa el valor 3 que es el número de celdas en blanco

 21 

Asesoría y Soluciones Informáticas

7. Caso Práctico Nº5.

La importadora de autos “Pana Autos S.A.C.”,}, nos encarga elabora una formato de boleta de ventas, en donde se ingrese el código del vendedor y de forma automática se muestre el nombre del vendedor y su sueldo. Asimismo se ingrese el código del vehículo y se muestre el modelo, el precio del vehículo. El formato a realizar será el siguiente:

7.1. Consideraciones generales:   

   

Los datos del cliente de ingresaran normalmente.

El código del vendedor se ingresara normalmente (1, 2 o 3) El nombre del vendedor y el sueldo, se mostraran de forma automática de acuerdo al código del vendedor ingresado. Para esto se empleara la funciona lógica =Si(), si bien es cierto tiene limitaciones pero a amanera de aprendizaje emplearemos esta función.

El código del vehículo, se ingresara de forma normal.

El modelo y el precio del vehículo se mostraran automáticamente, de acuerdo al código del vehículo ingresado.

La cantidad de venta se ingresara de forma normal.

El total, se calculara multiplicando la dirección de celda del: (=Precio*Cantidad)

Los datos se encuentran en la parte superior “base de datos del vehículo y del vendedor”.

 22 

Asesoría y Soluciones Informáticas

7.2. Fórmula para hallar el nombre del vendedor.

Como sabemos, el nombre del vendedor se mostrara automáticamente, dependiendo del código ingresado. Para ello debemos de tener en cuenta lo siguiente:   

La celda que contiene el código a evaluar, es la dirección D17.

La función que mostrara el nombre del vendedor de acuerdo al código ingresado será la celda D18.

La celda D17, podrá contener los códigos 1, 2 o 3. Por lo tanto son 03 posibilidades a evaluar, es por esta razón que no podemos aplicar la función lógica =Si() normal, sino empelar el =Si() anidados.

7.2.1. =Si() anidados.

Se emplea cuando la celda evaluar, tiene más de una valor, es decir tendrá mas de una condición a evaluar y mostrara valores o cadenas de acuerdo a las condiciones que se cumplan.

7.2.1.2. Sintaxis para mostrar caracteres: =Si(Cond1,”Mensaje1”,Si(Cond2,”Mensaje2”, .... ,Si(CondX,”MensajeX”,”MensajeFalso”))) Esto se puede leer de la siguiente manera: Si la condicion1 es verdadera, entonces que muestre el “Mensaje1”, Si la condición2 es verdadera, entonces que se muestre el “Mensaje2”,…, Si la condiciónX es verdadera, entonces que se muestre el “MensajeX”, sino es ninguna de las anteriores, mostrara el “MensajeFalso”.

7.2.1.2.1. Ejemplo: Suponiendo que la celda B5 se ingresa el tipo de contratación (1, 2 0 3), en la celda C5, debe mostrase la modalidad de contrato:

=Si(B5=1,”Funcionario”,Si(B5=2,”C.A.S.”,Si(B5=3,”Locación de Servicios”,“No existe”))) 7.2.1.3 Sintaxis para mostrar valores: =Si(Condición1,Valor1,Si(Condición2,Valor2, .... ,Si(CondiciónX,ValorX,ValorFalso))) Esto se puede leer de la siguiente manera: Si la condicion1 es verdadera, entonces que muestre el Valor1, Si la condición2 es verdadera, entonces que se muestre el Valor2,…, Si la condiciónX es verdadera, entonces que se muestre el ValorX, sino es ninguna de las anteriores, mostrara el ValorFalso.

 23 

Asesoría y Soluciones Informáticas

7.2.1.3.1. Ejemplo: Según el tipo de contratación (1, 2, o 3), en la celda D5 debe mostrarse el sueldo del trabajador:

=Si(B5=1,1500,Si(B5=2,1300,Si(B5=3,1000,0)))

7.2.1.4. Aplicando el =Si() anidados para hallar los datos del vendedor.

A Si(código(D17)=1, entonces que muestre “Carlos Valenzuela”(G3), si(código(D17)=2, entonces que muestre “Diana Villalobos”(G4), si(código(D17)=3, entonces que muestre “Miluska Reckars”(G5), de no ser así, que muestre “No Registrado”))), la formula seria la siguiente: =Si(D17=1,G3,Si(D17=2,G4,Si(D17=3,G5,”No Registrado”)))

Recuerda.

Si ponemos el nombre de los vendedores directamente en la formula, no habría problema de error de sintaxis al ingresar dicha fórmula, pero si habría un error de lógica, póngase en el caso, si un trabajos (por no decir todos) dejen de laborar en la importadora, ¿qué harías?. Empleando la tabla auxiliar, solo será necesario cambiar los nombres en la tabla y la formula como trabaja con las direcciones de celda, siempre mostrara el contenido de dichas celdas.

 24 

Asesoría y Soluciones Informáticas

7.2.1.5. Fórmula para hallar el sueldo del vendedor.

B Si(código(D17)=1, entonces que muestre el sueldo de Carlos (H3), si(código(D17)=2, entonces que muestre el sueldo de Diana(H4), si(código(D17)=3, entonces que muestre el sueldo Miluska(G5), de no ser así, que muestre 0 (cero)))), la formula seria la siguiente: =Si(D17=1,H3,Si(D17=2,H4,Si(D17=3,H5,0)))

Recuerda.

Si ponemos el nombre de los vendedores directamente en la formula, no habría problema de error de sintaxis al ingresar dicha fórmula, pero si habría un error de lógica, póngase en el caso, si un trabajos (por no decir todos) dejen de laborar en la importadora, ¿qué harías?. Empleando la tabla auxiliar, solo será necesario cambiar los nombres en la tabla y la formula como trabaja con las direcciones de celda, siempre mostrara el contenido de dichas celdas.

 25 

Asesoría y Soluciones Informáticas

7.2.1.6. Fórmula para hallar el modelo del vehículo.

C Si(código(D22)=1, entonces que muestre “Yaris”(D3), si(código(D22)=2, entonces que muestre “Tercel”(D4), si(código(D22)=3, entonces que muestre “Carina”(H5), de no ser así, que muestre “No Registrado”))), la formula seria la siguiente: =Si(D22=1,D3,Si(D22=2,D4,Si(D22=3,D5,0)))

 26 

Asesoría y Soluciones Informáticas

7.2.1.7. Fórmula para hallar el precio del vehículo.

D Si(código(D22)=1, entonces que muestre 25000(E3), si(código(D22)=2, entonces que muestre 21000(E4), si(código(D22)=3, entonces que muestre 18000(E5), de no ser así, que muestre 0))), la formula seria la siguiente: =Si(D22=1,D3,Si(D22=2,D4,Si(D22=3,D5,0)))

 27 

Asesoría y Soluciones Informáticas

7.2.1.8. Fórmula para hallar el total.

Lo único que tendríamos que hacer es multiplicar la dirección de celda de precio (D24) por la dirección del precio (D25), tal como se muestra en la siguiente imagen:

E Estando aquí, procedemos a digitar la fórmula para hallar el total de la venta, de la siguiente manera:

=D24*D25

Observación.

Como podemos apreciar, el empleo de los =Si() anidados, son muy útiles y prácticos.

Cuando elaboremos cuadros, en donde se evalúa en más de 3 condiciones, es decir 15 o 20 condiciones, el =Si() anidado cuenta con limitaciones, la más determinante es que puede evaluar hasta 64 condiciones, para estos casos el Microsoft Excel, nos brinda el empleo de funciones de búsquedas a nivel vertical y horizontal, como son: =BuscarV() y BuscarH() respectivamente.

 28 

Asesoría y Soluciones Informáticas

8. FUNCIONES DE BÚSQUEDAS. El Microsoft Excel, nos brinda una serie de funciones que nos permiten realizar búsquedas de cadena y/o valores en un rango de celdas, la cual es considerada como una matriz e datos. La búsqueda se realiza, a través de una condición (dato a buscar), la se busca en la matriz de datos y si se encuentra, extrae la cadena y/valor que corresponda a la columna indicada (columna de donde se desea extraer el dato) condición establecida. Entre las más empleadas, tenemos a las funciones =ConsultaV() y =ConsultaH().

8.1. Función de Búsqueda =ConsultaV().

Esta función, permite realizar una búsqueda de un dato, en una matriz y si se encuentra extraerá el dato buscado en la columna en donde se encuentra el dato a extraer. Para aplicar esta unción es necesario que la infromación de la matriz, se encuentre ingresada por columnas (en forma vertical).

8.1.1. Sintaxis de la Función =ConsultaV(): =ConsultaV(DatoBuscar,MatrizDeDatos,

,FALSO(ó VERDADERO)

DatoBuscar, Es la información (cadena ó valor) que se desea buscar en la matriz de datos.

MatrizDeDatos, Es el rango de celdas, que contiene información, en donde el cual se encuentra el dato a buscar. , Es la columna de la matriz de datos, de la cual se tomara en cuenta para la extracción del dato a buscar.

FALSO ó VERDADERO, Si se especifica FALSO, el Microsoft Excel, hará la búsqueda idéntica al dato a buscar. Si se especifica VERDADERO, el Microsoft Excel, buscara la información más similar al dato a buscar.

8.1.2. Ejemplo de la Función =ConsultaV():

Columna1

Columna2

Matriz de datos.

Aquí, ingresaras la fórmula para realizar la búsqueda de la información que se ingrese en la celda “C9”. =ConsultaV(C9,A2:B4,2,FALSO)

 29 

Asesoría y Soluciones Informáticas

8.2. Caso Práctico Nº6.

La Universidad Señor de Sipan, nos encarga desarrollar un cuadro que nos permita buscar los nombres, curso y promedio de sus alumnos, a través del código del alumno. El formato será como se muestra en la siguiente imagen:

Aquí, ingresaras la fórmula para realizar la búsqueda del nombre del alumno. =ConsultaV(D20,A3:D12,2,FALSO)

Aquí, ingresaras la fórmula para realizar la búsqueda del curso del alumno. =ConsultaV(D20,A3:D12,3,FALSO)

Aquí, ingresaras la fórmula para realizar la búsqueda del promedio del curso del alumno. =ConsultaV(D20,A3:D12,4,FALSO)

Observación.

Como habrás podido observar, la función ConsultaV(), nos permite realizar búsquedas más directas y con más campos de información en una matriz. Esta función, a diferencia del =Si() anidado que es muy limitado, es que no tiene límites de evaluación, debido a que no emplea una condición a evaluar, sino un dato a buscar. La diferencia existente con la función ConsultaH(), es que la extracción del dato buscado y encontrado, ya no será por columnas sino por filas.

 30 

Asesoría y Soluciones Informáticas

8.3. Celdas Relativas y Absolutas.

Hasta el momento hemos venido trabajando con la información existente en las celdas en operaciones matemáticas, estadísticas, lógicas, entre otras; pero de forma relativa, es decir al copiar la formula, las direcciones de filas también se incrementan, para una mejor comprensión, veamos el siguiente ejemplo:

A Se cuenta con una tabla de entrada de datos para el código, nombre de la facultad y monto de la matriculas de las facultades y asimismo un formato de planilla de matriculados, la cual se llenara hasta los códigos de las facultades, tal como se muestra en esta figura:

B Estando en esta celda, procedemos a digitar la fórmula para buscar el código (D8), en la matriz de datos (A2:C4) y cuando lo encuentre extraiga el dato de la segunda columna, de la siguiente manera: =ConsultaV(D8,A2:c4,2,FALSO)

En este momento el Microsoft Excel, tomo el dato de la celda D8 (en este caso el código 1) y lo busco en la matriz (A2:C4) y al encontrarlo hizo el cruce de la fila encontrada con la columna 2, que en este caso en la facultad “sistemas” y se estará mostrando en la celda, tal como se muestra en esta imagen.

 31 

Asesoría y Soluciones Informáticas

B Ahora mueve el cursor en la celda en donde ingresamos la formula (E8), luego ubica el puntero del mouse justo en este lugar que te estamos indicando y presiona el click y mueve el puntero hacia abajo hasta la celda E15 y deja de presionar el click y se mostrara el siguiente resultado:

Observa con detenimiento, que al copiar la formula, se han llenado con los resultados de la búsqueda, pero a partir de la celda E11 sale un mensaje de “#N/A” que da entender que no encuentra el dato a buscar e el matriz indicada. Esto se debe a que al copiar la fórmula hacia abajo, de forma automática el Microsoft Excel, incrementa las filas en una unidad a fin de que recalcule el resultado, para darnos cuenta ubica el cursos en la celda E9 y vas bajando de uno en uno y mirando en el indicador de contenido y observaras que la fórmula de forma automática a incrementado las filas:

Ubicando el cursor en la formula, observa en el indicador de contenido las filas.

La formula muestra la celda D8, al cual está bien por aquí se encuentra el código que se desea buscar, la matriz es A2 hasta C4, porque ahí están los datos en donde se realizara la búsqueda, es por esta razón, que la formula analiza y busca correctamente y devuelve la cadena “sistemas”. Tal como lo ingresamos nuestra formula las filas no cambian. Pero en la siguiente imagen las filas van a variar.

 32 

Asesoría y Soluciones Informáticas

Ubicando el cursor en la formula, observa en el indicador de contenido las filas.

Ahora en el indicador de contenido, se muestra la formula, en donde las filas sean incrementado en una unidad. D9 (contiene el código a buscar), A3:C5 (es la matriz de datos, aquí hay un error porque la matriz siempre debe ser A2 hasta C4, esto vario debido a que copiamos la formula hacia abajo y aparentemente muestra correctamente como resultado la cadena “industrial”, porque el código 2 coincide en la matriz su posición a la cadena “industrial”, pero si en este momento el valor 2 lo cambiaras por 1, observaras que no devolverá la cadena “sistemas” lo cual ya es un error grave. Sigamos abalizando la siguiente imagen.

Ubicando el cursor en la formula, observa en el indicador de contenido las filas.

AL igual que en la imagen anterior, en el indicador de contenido, se muestra la formula, en donde las filas sean incrementado en una unidad. D10 (contiene el código a buscar), A4:C6 (es la matriz de datos, aquí ya con más claridad la matriz se ha desbordado por completo y siempre debe ser A2 hasta C4. Se está mostrando la cadena “electrónica”, porque coincidentemente el código 2 coincide en la matriz su posición a la cadena “electrónica”, pero si en este momento el valor 3 lo cambiaras por 1 o 2, observaras que no devolverá la cadena “sistemas” o “industrial” respectivamente, lo cual ya es un error grave. Sigamos analizando la siguiente imagen.

 33 

Asesoría y Soluciones Informáticas

Ubicando el cursor en la formula, observa en el indicador de contenido las filas.

Aquí, observa con detenimiento que en la formula, la celda del código es D11, la cual está bien porque sigue siendo la celda que contiene el dato a buscar, pero la matriz se inicia en A5 (en esta celda no hay nada) hasta la celda C7 (coge celdas que no tiene nada que ver), es por ello que el Microsoft Excel nos muestra el mensaje de error. En este momento la matriz de datos, ya es completamente otra, cogiendo celdas que no tienen nada que ver con nuestra matriz de búsqueda original. Este problema de incremento de filas al copiar la formula (para este caso y por la forma de los cuadros no deben incrementarse las flas al copiar la formula), debemos de mantener fijas las filas que comprenden nuestra matriz de datos (A2:C4) y a eso lo llamamos celdas Absolutas.

8.3.1. Aplicando Celdas Absolutas a nuestro cuando ejemplo.

Como ya se explicó, las direcciones de celdas de la matriz de datos, deben permanecer finas, esto en Microsoft Excel se llaman celdas absolutas. Para que el Microsoft Excel, reconozca a una celda como absoluta, tan solo será necesario anteponer a la columna y fila de la celda el signo del dólar “$”.

A Haga doble click en la celda en donde ingresamos la primera fórmula (celda E8) para editarla.

B Ahora procede a ingresar el signo del dólar “$” antes de la columna y fila de la matriz de datos de la siguiente manera: =ConsultaV(D8,$A$2:$C$4,2FALSO), luego pulsas enter.

 34 

Asesoría y Soluciones Informáticas

C Ahora mueve el cursor hacia la celda, en donde acabamos de modificar la formula (E8), luego ubica el puntero del mouse justo en este lugar que te estamos indicando y presiona el click y mueve el puntero hacia abajo hasta la celda E15 y deja de presionar el click y se mostrara el siguiente resultado:

Las dos últimas celdas se muestra el mensaje “#N/A”, porque no existen los códigos 4 y 5. Si ingresar cualquier código registrado en la tabla (1, 2 o 3) se mostrara su respectiva facultad.

A manera de práctica, aplica lo aprendido para que ingreses la fórmula para buscar y extraer el monto del pago de las matriculas, empleando celdas absolutas.

Recuerda.

A partir ahora, en todo cuadro que nos exige mas de dos condiciones, lo indicado será emplear la función =ConsultaV() ó ConsultaH() para buscar y extraer datos de una matriz de datos.

La información existente de una matriz de datos, se les llama tablas y las tablas contiene registros de: personas, artículos, objetos, etc.

Nuestros cuadros, como ejemplos básicos, hemos emplea solo de 05 a 10 registros, pero recuerda, que nuestra hoja electrónica puede almacenar hasta 1’048,576 filas. Esto quiere decir que existen filas suficientes para registrar los vehículos, alumnos, vendedores, etc. (revisa la página 07 del capitulo 01).

 35 

Asesoría y Soluciones Informáticas

8.3. Función Matemática =Suma().

Esta función, se emplea cuando se desea sumar los valores numéricos existentes en un rango de celas indicadas, obteniendo su totalidad. También podemos mencionar que el Microsoft Excel, no necesariamente trabajara con rango de celdas, sino con el nombre que represente a un rango de celdas, esto lo veremos más adelante.

8.3.1. Sintaxis de la Función =Suma().

(Rango_de_celdas) Rengo_de_celdas, representa al conjunto de celdas o nombre de rango de celdas, que contiene los valores numéricos que se desean sumar tu totalidad.

8.3.1.2. Ejemplo de la Función =Suma().

(A5:A20), Obtendrá la sumatoria de los valores numéricos existentes, a partir de la celda “A5” hasta la celda “A20”.

(Totales), Obtendrá la sumatoria de los valores numéricos existentes, en el rango de celdas, representadas con el nombre “Totales”.

(A5,E5,H5), Obtendrá la sumatoria de los valores numéricos existentes, solo en las direcciones de celdas A5,E5 y H5.

8.4. Función Estadística =Max().

Se emplea esta función, para hallar el valor máximo numérico existente, en un rango de celdas o del nombre de rango de celdas que representa al conjunto de celdas.

8.4.1. Sintaxis de la Función =Max().

(Rango_de_celdas) Rengo_de_celdas, representa al conjunto de celdas o nombre de rango de celdas, que contiene los valores numéricos, de quien se desea obtener el valor mayor.

8.4.1.2. Ejemplo de la Función =Max().

(A5:A20), Obtendrá el valor máximo existente, a partir de la celda “A5” hasta la celda “A20”.

(Totales), Obtendrá el valor máximo existente, en el rango de celdas, representadas con el nombre “Totales”.

(A5,E5,H5), Obtendrá el valor máximo de los valores numéricos existentes, solo en las direcciones de celdas A5, E5 y H5.

 36 

Asesoría y Soluciones Informáticas

8.5. Función Estadística =Min().

Se emplea esta función, para hallar el valor mínimo numérico existente, en un rango de celdas o del nombre de rango de celdas que representa al conjunto de celdas.

8.5.1. Sintaxis de la Función =Min().

(Rango_de_celdas) Rengo_de_celdas, representa al conjunto de celdas o nombre de rango de celdas, que contiene los valores numéricos, de quien se desea obtener el valor mínimo.

8.5.1.2. Ejemplo de la Función =Min().

(A5:A20), Obtendrá el valor mínimo existente, a partir de la celda “A5” hasta la celda “A20”.

(Totales), Obtendrá el valor mínimo existente, en el rango de celdas, representadas con el nombre “Totales”.

(A5,E5,H5), Obtendrá el valor mínimo de los valores numéricos existentes, solo en las direcciones de celdas A5, E5 y H5.

8.6. Función Estadística =Promedio().

Se emplea esta función, cuando se desea hallar el promedio aritmético, de los valores numéricos existentes en un rango de celdas o del nombre de rango que los representa.

8.6.1. Sintaxis de la =Promedio().

(Rango_de_celdas) Rengo_de_celdas, representa al conjunto de celdas o nombre de rango de celdas, que contiene los valores numéricos, de quien se desea obtener el promedio aritmético.

8.6.1.2. Ejemplo de la Función =Promedio().

(A5:A20), Obtendrá el promedio aritmético, de los valores numéricos existentes, a partir de la celda “A5” hasta la celda “A20”.

(Totales), Obtendrá el promedio aritmético, de los valores numéricos existentes, en el rango de celdas, representadas con el nombre “Totales”.

(A5,E5,H5), Obtendrá el valor mínimo de los valores numéricos existentes, solo en las direcciones de celdas A5, E5 y H5.

 37 

Asesoría y Soluciones Informáticas

9. Caso Práctico Nº7.

La Importadora La Tela Mejor S.A.C, nos encarga desarrollar un cuadro que nos permita calcular y hallar los totales en ventas expresadas en soles y la venta mayor, menor y promedio durante la semana.

A Proceda a elaborar, el cuadro ejemplo, aplicando combinación de celdas, bordes, sombreados, fuentes, tamaños de letras y fijar el formato de presentación de moneda a: total, venta mayor, venta menor ,promedio y se muestre como la siguiente imagen:

B Ubica el cursor en esta celda y procedamos a ingresar la formula, para hallar la sumatoria (total) de las ventas de la semana de “Carlos”, de la siguiente manera: =Suma(B5:F5)

C =Suma(B6:F6)

D =Suma(B7:F7)

E Ubica el cursor en esta celda y procedamos a ingresar la formula, para hallar la venta mayor, de la siguiente manera: =Max(G5:G7)

G Ubica el cursor en esta celda y procedamos a ingresar la formula, para hallar la Promedio de venta, de la siguiente manera: =Promedio(G5:G7)

F Ubica el cursor en esta celda y procedamos a ingresar la formula, para hallar la venta menor, de la siguiente manera: =Suma(G5:G7)

 38 

Asesoría y Soluciones Informáticas

H Por último ingresa los datos hasta la última venta del día viernes del vendedora “Lucero” , si ingresas los valores de la siguiente imagen, los resultados serán os mismos que se muestran en dicha imagen:

Recuerda.

Recuerda que nuestros ejemplos son con pocos registros, pero debes aplicarlo a la realidad de tu centro de trabajos, imaginemos que no se trate de una pequeña importadora sino una que importe y distribuya a nivel nacional y que cuente con 50 vendedores, entonces la fórmula que halla el total (=suma()) en el lugar de digitarlas 50 veces, debes aplicar el copiado de las formulas, tal como hemos aprendido en los casos anteriores.

 39 

Asesoría y Soluciones Informáticas

10. Caso Práctico Nº8.

La Municipalidad Distrital de el Agustino, nos encarga desarrollar un cuadro que nos permita registrar las infracciones cometidas por los vehículos menores (mototaxis), para el formato a utilizar, se debe de tener en cuenta las siguientes consideraciones:

10.1. Consideraciones Generales: 10.1.1. Formato principal.

La obtener la presentación del formato principal se emplea los bordes, combinación de celdas, tamaño y fuente y color de relleno y tendrá la siguiente presentación:

En este formato, el usuario, solo podrá ingresar el número de la placa del vehículo menor, el nombre del propietario o infractor y el tipo de la multa. La descripción de la multa y el monto a pagar, se mostrará de forma automática, a través del ingreso de la formula =ConsultaV().

Esta parte, es el reporte que mostrara de forma automática, los totales por cada tipo de multa, tanto en cantidades como en soles respectivamente.

10.1.2. Tabla de entrada de Datos.

La tabla de entrada de datos, es la que el Microsoft Excel, lo conoce como el rango de datos y contiene la información que se extraerán a través de las función =ConsultaV()os valores, que serán buscadas y tendrá la siguiente presentación:

Una vez elaborado el cuadro de entrada de datos, los datos a ingresar serán solo: el tipo, descripción, factor%, múltiplo y la UIT. El monto se obtendrá empleando una fórmula que se explica a continuación:

 40 

Asesoría y Soluciones Informáticas

10.1.2.1. Consideraciones para el obtener el Monto.

La municipalidad distrital de El Agustino, ha establecido que el monto dependerá del tipo de multa y se obtiene del resultado de la operación del Factor% con la multiplicación del múltiplo con el UIT, de la siguiente manera:

1 Monto = Factor%*(múltiplo*UI)

10.1.2.2. Fórmula para hallar el Monto.

Debemos recordar, que el monto dependerá del tipo de multa, es decir si es de tipo=1, se obtiene del 10%*(3*3,800), si fuera tipo=2, se obtiene 9%*(3*3,800) y así sucesivamente, tal como se observa en la tabla de entrada de datos completa. Asimismo, debemos recordar que al obtener la formula y después copiarla (salvo que quieras digitar 10 veces la formula), las filas se incrementara en una unidad, para que no suceda ello, empelaremos celdas absolutas con el signo del dólar “$”, tal como explicamos en los casos anteriores.

2 Ubicando el curso en esta celda, digitamos la fórmula para hallar el monto según las consideraciones explicadas y será la siguiente: =D3*($E$3*$F$3)

La celda D3 que contiene el factor%, varia para cada caso del tipo de la multa, por lo tanto no debe fijarse como celda absoluta, ya que al copiarse la formula, las filas deben incrementarse en una unidad, mientras que las celdas E3 (múltiplo) y F3(UIT) si deben fijarse como absolutas, para que sus filas no varíen cuando se copie la formulas filas abajo.

Al ingresar la formula, observaras que se obtuvo el monto S/.1,1,40.00 porque ya sea dado formato de moneda a las celdas y si multiplicas el 10% de (3*3,800) se obtiene dicha cantidad.

 41 

Asesoría y Soluciones Informáticas

10.1.2.3. Copiando la fórmula del Monto.

Ahora debemos de copiar la formula ingresada hacia abajo, para ello ubica el curso en la celda de la formula ingresada (C3) y estando en dicha celda, realiza lo siguiente:

3 Ubica el puntero del mouse en esta celda y presiona el click y baja el mouse hasta la celda C12 y deja de presionar el click y se mostrara los resultados, de la siguiente imagen:

La tabla de entrada de datos y el formato principal se estará mostrando como en la siguiente figura:

 42 

Asesoría y Soluciones Informáticas

10.1.3. Fórmula para hallar la descripción de la multa.

Como sabemos los datos orígenes, se encuentran en la tabla de entrada de datos (Clasificación de Multas). Para esto debemos emplear la función de búsqueda de datos =ConsultaV(), teniendo en cuenta las siguiente consideraciones:    

Los datos se encuentran en la matriz: A3:C12 Para el ingreso de la formula, el cursor debe estar en la celda K7 (Descripción de la multa).

El dato que será considerado para la búsqueda es la celda J7 (tipo de la multa).

Una vez ingresada la formula, debemos de copiar filas abajo dicha fórmula, para ello debemos de emplear celdas absolutas con el signo del $, a fin que cuando se copie el rango de datos (matriz) siempre se mantengan en sus misma dirección.

En esta celda digitamos la fórmula para hallar la descripción de la multa, tomando en cuenta el tipo de la multa: =ConsultaV(J7,$A$3:$C$13,2,FALSO) y pulsamos enter.

4 10.1.3.1. Copiando la fórmula de la Descripción de la Multa.

Ubicamos el cursor en la celda K/ (donde ingresamos la formula) y realizamos lo siguiente:

Ubica en puntero en este lugar y presiona el click y mueve el puntero hacia abajo, hasta la celda K20 y deja de presiona el click.

5  43 

Asesoría y Soluciones Informáticas

10.1.4. Fórmula para hallar el Monto de la multa.

Al igual que en el caso de la “Descripción de la multa”, el Monto de la multa ya está calculada y se encuentra en la tabla de entrada de datos (Clasificación de Multas), lo único que tenemos que hacer es buscarla y extraerla. Para esto debemos emplear la función de búsqueda de datos =ConsultaV(), teniendo en cuenta las siguiente consideraciones:    

6

Los datos se encuentran en la matriz: A3:C12 Para el ingreso de la formula, el cursor debe estar en la celda L7 (Descripción de la multa).

El dato que será considerado para la búsqueda es la celda J7 (tipo de la multa).

Una vez ingresada la formula, debemos de copiar filas abajo dicha fórmula, para ello debemos de emplear celdas absolutas con el signo del $, a fin que cuando se copie el rango de datos (matriz) siempre se mantengan en sus misma dirección.

En esta celda digitamos la fórmula para hallar buscar y extraer el monto de la multa, tomando en cuenta el tipo de la multa: =CONSULTAV(J7,$A$3:$C$12,3,FALSO) y pulsamos enter.

10.1.4.1. Copiando la fórmula del Monto de la Multa.

Ubicamos el cursor en la celda K/ (donde ingresamos la formula) y realizamos lo siguiente:

Ubica en puntero en este lugar y presiona el click y mueve el puntero hacia abajo, hasta la celda L20 y deja de presiona el click.

7  44 

Asesoría y Soluciones Informáticas

10.1.5. Hallar los totales por Descripción y Monto de las multas.

Al final, se puede observar un cuadro de reportes, en donde debe figurar la cantidad total por cada tipo de multa y el monto total registrado por cada tipo de multa. Para obtener estos totales, será necesario emplear las función estadística =Contar.Si() para hallar los totales por multa y la función matemática trigonométrica =Sumar.Si() para hallar la suma de los totales por multa respectivamente.

10.1.5.1. Hallar cantidad total por choque y fuga.

Aquí debemos de hallar y mostrar la cantidad total de infracciones registradas por el tipo de multa “1”, es decir “Choque y Fuga“. Para el ingreso de la formula debemos de considerar lo siguiente: 

Para el ingreso de la formula, el curso debe estar en la celda “K22”.



Que el rango en donde se encuentran los tipos de las multas es (J7:J20).



La función =Contar.Si() debe buscar y contar en el rango (J7:J20), cuantos números “1”.

El resultado de la fórmula ingresada, no necesitara que sea copiada, porque el rango de búsqueda será la misma, pero el criterio son diferentes para cada tipo de multa.

Como la fórmula no será copiada, entonces no será necesario emplear celdas absolutas, sino relativas (sin el uso del signo del “$”).

 

 45 

Asesoría y Soluciones Informáticas

Estando en esta celda, procedemos a digitar la función que permite contar cuantos números “1” existen en el rango de celdas: =CONTAR.SI(J7:J20,"=1") y pulsamos enter.

1

Para hallar los demás totales “Luz Roja, Sin Licencia, …, Sin Cinturón de Seguridad”, se empleara la misa función y el rango de celdas será la misma, pero cambiara el criterio de búsqueda, debido a que debe cambiar de acuerdo al tipo de multa, tal como se muestra en la siguiente figura:

=CONTAR.SI(J7:J20,"=2")

2

=CONTAR.SI(J7:J20,"=4")

4

3

5

=CONTAR.SI(J7:J20,"=3")

=CONTAR.SI(J7:J20,"=5")

9 =CONTAR.SI(J7:J20,"=9")

1 =CONTAR.SI(J7:J20,"=10")0 6 =CONTAR.SI(J7:J20,"=6")

8 7

=CONTAR.SI(J7:J20,"=7")

=CONTAR.SI(J7:J20,"=8")

 46 

Asesoría y Soluciones Informáticas

Si todo fue realizado correctamente, el formato se estará mostrando como en la siguiente figura:

10.1.5.2. Hallar el total en Soles por choque y fuga.

Aquí debemos de hallar y mostrar la totalidad en soles, correspondientes a laa infracciones registradas por el tipo de multa “1”, es decir “Choque y Fuga“. Para el ingreso de la formula debemos de considerar lo siguiente: 

P ara el ingreso de la formula, el curso debe estar en la celda “L22”.



Que el rango en donde se encuentran los tipos de las multas es (J7:J20).

 47 

Asesoría y Soluciones Informáticas

  

Para este caso, le indicaremos a la función =Sumar.Si() que busque en el rango (J7:J20), cuantos números “1”, existen en dicho rango.

Asimismo a esta función, le debemos de indicar que vaya sumando los totales, en donde coinciden con el tipo de multa “1” del rango de celdas buscado.

Como la fórmula no será copiada, entonces no será necesario emplear celdas absolutas, sino relativas (sin el uso del signo del “$”).

1

Estando en esta celda, procedemos a digitar la función que permitirá sumar los totales en soles por el tipo de multa “1”: =SUMAR.SI(J7:J20,"=1",L7:L20) y pulsamos enter.

Para hallar los demás totales en soles “Luz Roja, Sin Licencia, …,Sin Cinturón de Seguridad”, se empleara la misa función y el rango de celdas será la misma, pero cambiara el criterio de búsqueda, debido a que debe cambiar de acuerdo al tipo de multa, tal como se muestra en la imagen d la página siguiente:

 48 

Asesoría y Soluciones Informáticas

3

=SUMAR.SI(J7:J20,"=2",L7:L20)

2

=SUMAR.SI(J7:J20,"=3",L7:L20)

4 =SUMAR.SI(J7:J20,"=4",L7:L20)

5

=SUMAR.SI(J7:J20,"=8",L7:L20)

8

=SUMAR.SI(J7:J20,"=5",L7:L20)

9 =SUMAR.SI(J7:J20,"=9",L7:L20)

6 =SUMAR.SI(J7:J20,"=6",L7:L20)

10

7

=SUMAR.SI(J7:J20,"=7",L7:L20)

=SUMAR.SI(J7:J20,"=10",L7:L20)

Si todo fue realizado correctamente, el formato general, se estará mostrando como en la siguiente figura:

¡Felicitaciones! Por haber realizado correctamente todo el procedimiento. Recuerda que la tabla de entrada de datos, el formato principal y el reporte, lo hemos elaborado juntos por motivos de una mejor explicación.  49 

Asesoría y Soluciones Informáticas

11. Caso Práctico Nº9.

La Municipalidad Distrital de el Agustino, a través de la Sub gerencia de personal y racionalización, nos encarga registrar a los trabajadores (funcionarios, obreros y empleados de planta) y desarrollar una boleta de pagos mensual. Para la implementación de la aplicación, debemos de tener en cuenta las siguientes consideraciones: consideraciones:

11.1. Consideraciones: 

Para la implementación de la solución, se contara con una tabla de entrada de datos para los trabajadores y sus beneficios y descuentos. Esta tabla debe tener la siguiente presentación:



El formato de la boleta de pagos mensual, tendrá la siguiente presentación:

Nota: Como bien sabemos, el formato principal, en este caso el formato de la boleta de pagos mensual, no debe estar debajo de la tabla de entrada de datos, porque en cualquier momento de podrá incrementar trabajadores y ahí tendríamos problemas, lo recomendable es hacerla a la derecha de la tabla de entrada de datos o que el formato principal se elabore a partir de la celda “A1” y la tabla de entrada de datos se encuentre a su derecha.

Para efectos de una mejor explicación, nosotros los estamos elaborando uno debajo de otro. Debemos precisar que lo óptimo es que la tabla de entrada de datos y los formatos principales, y otros se encuentren en Hojas diferentes.

 50 

Asesoría y Soluciones Informáticas



Hasta este punto la tabla de entrada de datos y el formato principal (boleta e pagos mensual), se estarán mostrando como en la imagen siguiente:



La boleta de pagos deberá trabajar de la siguiente forma: al ingresar el código del trabajador (D15), el Microsoft Excel, debe buscar el código en la tabla de ingreso de datos (A2:k10) y mostrar sus apellidos y nombres (D16), Fecha de ingreso (D17), Cargo (D18), DNI (D19), Nº ESSALUD (D20), la Remuneración Básica (D22). Como ya nos estamos imaginando la función a emplear será =ConsultaV().

Para Tiempo de servicio, por familia, Movilidad, total de ingresos se obtendrán también de las bonificaciones y asimismo para ONP, Adelantos, Faltas, se obtienen de los descuentos. Se empleara también la función =ConsultaV().

El total de descuento se obtendrá sumando todos los descuentos.

Las portaciones del empleador se obtendrá del 9% de la remuneración del trabajador y n será afecto al empleado, sino al empleador.

El Neto a Recibir se obtienes sumando la Remuneración básica + bonificación y todo ellos restándole el total de descuentos.



  

Estas son las consideraciones generales, a tomar en cuenta para el desarrollo de la solución.

Los cálculos y formulas iremos desarrollando conforme avancemos a partir de este punto.

 51 

Asesoría y Soluciones Informáticas

11.2. Calculando las Bonificaciones: 11.2.1. Bonificación por Tiempo de servicio.

Es una bonificación que se le da al trabajador, por los años que viene laborando en la municipalidad y se extraer de la remuneración básica. Los 5 de bonificación varían, según lo siguiente: Si tiene hasta 5 años, se le asignara el 5% Si tiene de 6 a 10 años, se le asignara el 8% Si tiene de 11 a 15 años, se le asignara el 11% Si tiene de 16 a más, se le asignara el 14%

Recuerda que el rango de los años y el % de la bonificación varían de acuerdo al criterio de cada empresa.

Cada vez que se tenga una evaluación con “Si”, se debe emplear la función lógica =Si().

Desarrollando lógicamente, el cuadro de rango y %, el desarrollo seria la siguiente: Si es hasta 5 años se le asigna (Básico*5%), si es de 6 a 10 se le asigna (Básico*8%), si es de 11 a 15 se le asigna (Básico*11%), si es de 11 a más (básico*14%) en otro caso 0.

Aquí nos damos cuenta que debemos emplear la función =Si() anidaos por tiene más de una condición a evaluar, pero debemos notar que se evalúa rangos de años, para ello se empleara las funciones de fechas: =Hoy() y la función =Sifecha().

11.2.1.1 Función de Fecha =Hoy().

Esta Función permite obtener la fecha actual de sistema. Tan solo será necesario ingresar en la celda deseada esta función y el Microsoft Excel, de forma automática muestra la fecha actual de sistema (la fecha siempre debe ser la actual, salvo que usted en forma intencional la tenga en una fecha diferente). Ejemplo:

En esta celda ingresaremos la función para hallar la fecha actual: =Hoy()

 52 

Asesoría y Soluciones Informáticas

11.2.1.1.1 Función de Fecha =Sifecha().

Esta función se emplea cuando se desea hallar el tiempo trascurrido entre dos fechas, permitiendo obtener el valor del número del año, el valor del número del mes y de día respectivamente.

11.2.1.1.2 Sintaxis de función =Sifecha(). =SiFecha(argumento1,argumento2,”formato”), Argumento1, es la fecha directa o la dirección de celda que contiene la fecha inicial, es decir siempre debe ser menor o anterior a la fecha final.

Argumento2, es la fecha directa o la dirección de celda que contiene la fecha final, es decir siempre debe ser mayor o posterior a la fecha inicial.

Formato, es el tipo de resultado del tiempo transcurrido entra ambas fechas especificadas como argumento1 y 2. Aquí debemos de indica si queremos obtener los años, los meses o los días. Los indicadores a utilizar son los siguientes: “y”, indica que se obtendrá el número en años. “ym”, indica que se obtendrá el número en meses. “md”, indica que se obtendrá el número en días.

Para hallar el número de años: =SIFECHA(A2,B2,"y")

Para hallar el número de años: =SIFECHA(A2,B2,"ym" )

Para hallar el número de años: =SIFECHA(A2,B2,"md" )

Para la fecha actual, ingresaremos: =Hoy()

También podemos combinar la función con texto para obtener una cadena de información con fechas: aquí la fecha actual es el 21 de agosto del 2015.

=SIFECHA(A2,B2,"y") & " años " & SIFECHA(A2,B2,"ym") & " Meses " & SIFECHA(A2,B2,"md")&" días"

 53 

Asesoría y Soluciones Informáticas

11.2.1.1.3 Consideraciones para implementar la fórmula: La evaluación de la bonificación x tiempo de servicio, se obtiene según los años de servicio del trabajador, considerando que, si es hasta 5 años se le asigna (Básico*5%), si es de 6 a 10 se le asigna (Básico*8%), si es de 11 a 15 se le asigna (Básico*11%), si es de 11 a más (básico*14%) en otro caso 0.

“F3”, contiene la fecha inicial, la función Hoy() obtendrá siempre la fecha actual, el indicador “y” define que se trabajara con cantidades para años. La función sería algo así: =Sifecha(F3,Hoy(),”y”), obtiene el numero en años restando la fecha actual con la fecha de ingreso. =Sifecha(F3,Hoy(),”y”)<6, comparamos que el número de años obtenidos sea menor a 5, es decir de 0 hasta 5 años. =Sifecha(F3,Hoy(),”y”)<6,L3*5%, aquí verificamos si los años estuviera en el rango de 0 a 5 años, entonces se asignaría el 5% del Básico (celda L3).

De igual manera se tendría que evaluar para el segundo rango de años (6 a 10) y asimismo de 11 a 16 y de 16 a más años. Como son varias comparaciones, debemos de anidarlos en una sola función lógica. Aplicando lo aprendido en el cálculo de la bonificación x tiempo de servicio, realicemos lo siguiente:  

Ubica el curso en la de la Bonifacio x Tiempo de servició celda “L3”.

Seguidamente se debe ingresar la función para hallar la bonificación de acuerdo a lo explicado, según se muestra en la siguiente imagen:

=SI(SIFECHA(F3,HOY(),"y")<6,L3*5%,SI(SIFECHA(F3,HOY(),"y")<11,L3*8%,SI(SIFECHA(F3,HOY(),"y")<16,L3*11%,L3*14%)) )

1 Si realizaste correctamente el procedimiento, se estará mostrando como bonificación 737 soles, debido a que al restar al año actual (2015) obtenida por la función Hoy(), menos el año de ingreso del trabajador (2002) que está en la celda “F3”, se obtiene 13 años y esta cantidad está en el rango de 10 a 15 años, es por ello la función =Si() ejecuta la operación 11% del básico (6,700) celda “L3” que viene hacer 737.00 soles.

 54 

Asesoría y Soluciones Informáticas

11.2.1.1.4. Copiando la formula.  Ubica el curso en la celda de la bonificación obtenida, es decir en la celda “M3”.  Ahora ubica el puntero en el nodo de la parte inferior derecha de la celda “M3” tal como te muestro en la siguiente imagen y realiza lo que se te indica:

2 Ubica el puntero en este lugar y presiona el click del mouse y sin dejar de presionar el click, mueve el puntero haca abajo hasta la celda “M10” y dejas de presionar el click y se mostraran los resultados.

11.2.2. Bonificación por concepto de familia.

Cada empresa tiene su propio criterio para una asignación familiar, que si bien es cierto en el sector ´publico existen montos que se toman en cuenta para hallar este tipo de asignación de bonificación. En nuestro caso, solo se asignara un concepto por el estado civil (“K3”), si es soltero el 10% del básico (“L3”) y de no serlo se le asignara 0.

11.2.2.1. Evaluando el criterio.

Si estado civil es casado, se asignara el 10% del básico, de lo contrario 0.

11.2.2.2. Aplicando lo evaluado.

Como ya sabemos cada vez que se evalúa empleando el “Si”, necesariamente se utilizara la función lógica =Si(). La fórmula será la siguiente:

Aquí ingresamos la fórmula para hallar la bonificación por el concepto familiar, de la siguiente manera: =SI(K3="C",10%*L3,0)

3

Se obtiene el valor 0, porque observamos que en la celda del estado civil “K3”, se encuentra el valor “S” que representa a la condición “Soltero”.

 55 

Asesoría y Soluciones Informáticas

11.2.2.2. Copiado la fórmula de la bonificación de estado civil.  Ubica el curso en la celda de la bonificación obtenida, es decir en la celda “N3”.  Ahora ubica el puntero en el nodo de la parte inferior derecha de la celda “N3” tal como te muestro en la siguiente imagen y realiza lo que se te indica:

Ubica el puntero en este lugar y presiona el click del mouse y sin dejar de presionar el click, mueve el puntero haca abajo hasta la celda “N10” y dejas de presionar el click y se mostraran los resultados.

4

11.2.3. Bonificación por concepto de movilidad.

Para esta bonificación, se tomara en cuenta del cargo que tiene cada empleado dentro de la empresa. En este caso, se está considerando como ejemplo, la siguiente tabla:

Todos los gerentes reciben el 12% del básico, los Sub gerentes el 7% del básico, los Jefes el 5% del básico y los Supervisores el 3% del básico.

11.2.3.1. Evaluando el criterio.

Si es Gerente, se asignara el 12% del básico, si es Sub gerente, se asigna el 7% del básico, si es Jefe, se le asigna el 5% del básico, si es supervisor, se le asigna el 3% del básico, cualquier otro caso, se le asigna 0.

11.2.3.2. Aplicando lo evaluado.

Como ya sabemos cada vez que se evalúa empleando el “Si”, necesariamente se utilizara la función lógica =Si(). Asimismo como son varias condiciones, se aplicara el =Si() anidados

=SI(G3="Gerente",12%*L3,SI(G3="Sub gerente",7%*L3,SI(G3="Jefe",5%*L3,SI(G3="Supervisor",3%*L3,0))))

5  56 

Asesoría y Soluciones Informáticas

11.2.3.3. Copiado la fórmula de la bonificación x movilidad.  Ubica el curso en la celda de la bonificación obtenida, es decir en la celda “O3”.  Ahora ubica el puntero en el nodo de la parte inferior derecha de la celda “O3” tal como te muestro en la siguiente imagen y realiza lo que se te indica:

6

Ubica el puntero en este lugar y presiona el click del mouse y sin dejar de presionar el click, mueve el puntero haca abajo hasta la celda “O10” y dejas de presionar el click y se mostraran los resultados.

11.2.4. Hallar el total de bonificaciones.

Es la suma de todas la bonificaciones y se emplea la función matemática =Suma(), tal como se muestra en la siguiente imagen:

7 Aquí ingresamos la fórmula para hallar el total de bonificaciones, de la siguiente manera: =SUMA(M3:O3)

11.2.4.1. Copiado la fórmula de la bonificación x movilidad.

Ubica el puntero en este lugar y presiona el click del mouse y sin dejar de presionar el click, mueve el puntero haca abajo hasta la celda “P10” y dejas de presionar el click y se mostraran los resultados.

8

 57 

Asesoría y Soluciones Informáticas

11.2.5. Hallar el aporte por concepto de ONP 13%.

Como sabemos este concepto es un descuent0 que se le hace al empleado del 13% de su remuneración básica, considerándolo como una retención.

9

Aquí ingresamos la fórmula para hallar el ONP de la siguiente manera: =L3*13%

11.2.5.1. Copiado la fórmula de la ONP.

10

Ubica el puntero en este lugar y presiona el click del mouse y sin dejar de presionar el click, mueve el puntero haca abajo hasta la celda “Q10” y dejas de presionar el click y se mostraran los resultados.

11.2.6. Descuentos x adelantos.

Aquí las cantidades se ingresaran directamente, debido a que representan los adelantes que los emplearon solicitaron durante el mes.

11.2.7. Faltas.

Aquí se ingresaran las cantidades numéricas que representa los días que el empleado a faltado durante el mes. Estas faltas serán descontadas hallando el sueldo diario del trabajador.

11.2.8. Hallando el sueldo diario.

En nuestro formato el sueldo diario no está figurando, pero se obtiene dividiendo el básico entre 30, es decir (Básico/30).

Observación.

Como te habrás dado cuenta, en esta práctica estamos colocando directamente los 5 en las fórmulas para los cálculos respectivos y eso no debe ser así, se debe de crear un tabla de entrada de datos y en ella ingresar los % para descuentos, bonificaciones y cualquier otro valor, de tal manera que cualquier cambio de incremento o decremento se haga solo en dicha tabla y no en las formulas.

 58 

Asesoría y Soluciones Informáticas

11.3. Trabajando con el formato principal.

11.3.1. Código.

Estando en la celda “D15”, se ingresara el código del trabajador de forma manual y los demás campos del formato principal se deben de mostrar automáticamente con los datos que correspondan al trabajador del código ingresado. A manera de ejemplo ingresaremos el código “1” correspondiente al trabajador “Víctor Garay”.

11.3.2. Hallar apellidos y nombres.

Se empleara la función de búsqueda =ConsultaV(), para ello ubica el cursor en la celda “D16” en donde ingresaremos la función para buscar y extraer el dato correspondiente al código “1” ingresado anteriormente.

Ahora procedamos a digitar la función, tal como se muestra en la siguiente imagen:

=CONSULTAV(D15,A3:K10,2,FALSO)

11

Busca el código (D15), en la matriz (A3:k10) y al encontrarlo extrae el apellido y nombre del trabajador (columna 2).  59 

Asesoría y Soluciones Informáticas

11.3.3. Hallar la Fecha de ingreso.

Al igual que en apellidos y nombres, ubica el cursor en la celda “D17” en donde ingresaremos la función =ConsultaV(), tal como se muestra en la siguiente imagen:

12

=CONSULTAV(D15,A3:K10,6,FALSO)

Si la fecha obtenida, se muestra como un número convencional, debes de asignarle el formato de fecha, la cual ya hemos aprendido anteriormente.

Aclaraciones: La función =ConsultaV(), extrae el dato encontrado de la columna que se le indique. En el caso anterior la columna fue la columna “2”, debido a que los apellidos y nombres se encuentran justamente en la comuna “B” (comuna “2”).

En este caso, la columna especificada es la columna “6” y corresponde a la columna “F”, debido a que en dicha columna se encuentra la “Fecha de ingreso”, para una mejor apreciación, recordemos con la siguiente imagen: Columnas

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

 60 

17

18

19

Asesoría y Soluciones Informáticas

11.3.4. Hallar el cargo.

Ubica el cursor en la celda “D18” en donde ingresaremos la función =ConsultaV(), tal como se muestra a continuación:

13

=CONSULTAV(D15,A3:K10,7,FALSO)

11.3.4. Hallar el DNI. Ubica el cursor en la celda “D19” en donde ingresaremos la función =ConsultaV(), tal como se muestra a continuación:

14

=CONSULTAV(D15,A3:K10,8,FALSO)

 61 

Asesoría y Soluciones Informáticas

11.3.4. Hallar el Nº ESSALUD. Ubica el cursor en la celda “D20” en donde ingresaremos la función =ConsultaV(), tal como se muestra a continuación:

=CONSULTAV(D15,A3:K10,10,FALSO)

11.3.4. Hallar las Remuneraciones.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

16 =CONSULTAV(D15,A3:S10,12,FALSO)

17 =CONSULTAV(D15,A3:S10,13,FALSO)

=CONSULTAV(D15,A3:S10,14,FALSO)

18

=CONSULTAV(D15,A3:S10,15,FALSO)

19

=SUMA(D22:F25 )

20

 62 

19

Asesoría y Soluciones Informáticas

11.3.4. Hallar los Descuentos.

1

2

3

4

5

6

7

8

9

10

21

11

12

13

14

15

16

17

18

19

22 23

=CONSULTAV(D15,A3:S10,17,FALSO) =CONSULTAV(D15,A3:S10,18,FALSO)

24 =(CONSULTAV(D15,A3:S10,19,FALSO)*(CONSULTAV(D15,A3:S10,12,FALSO)/30))

Busca el código (D15) en la matriz (A3:S10), extrayendo el valor de la columna 19, es decir el valor 3.

Esta parte de la función mixta, busca el código (D15) en la matriz (A3:S10), extrayendo el valor 6,700 y este valor se dividirá entre 30, obteniendo (6700/30) = 233.333

Estos dos valores se multiplican entre ambos dando como resultado 670.00 = (3*233.333)

Observación.

Recordemos que estos valores que se van obteniendo, a través del análisis que se está haciendo, es considerando el código del empleado es “1”, si usted ingresa otro código, la formula automáticamente se ajustara a los valores correspondientes a ese nuevo código del empleado.

 63 

Asesoría y Soluciones Informáticas

25 =SUMA(J22:K24)

26 =CONSULTAV(D15,A3:S10,12,FALSO)*9%

27 =D26-J26

Hasta el próximo capítulo “Tablas – base de datos en Microsoft Excel”. ¿Si deseas el documento completo en formato Word (.doc) y en .PDF de este manual? ¿Si deseas los ejemplos originales de los ejercicios en Microsoft Excel en formato .XLS? Deposita la cantidad de S/.50.00 a la cuenta en Soles del Banco Continental Nº 0011-0269-0200159598-88 Víctor Garay Yauri.

Confirma el depósito, cualquier consulta o sugerencia hazla a mi correo electrónico, que es el mismo para mi Skype profesional: garaysoft@hotmail.com

 64 