3.2: Funciones lógicas y de búsqueda

Objetivos de aprendizaje

• Utilice una función IF para hacer comparaciones lógicas entre un valor y lo que espera.
• Cree un cálculo VLOOKUP para buscar información en una tabla.
• Entender los mensajes de error.
• Entender cómo ingresar y formatear Funciones de Fecha/Hora.

Además de hacer aritmética, Excel puede hacer otro tipo de funciones basadas en los datos de tu hoja de cálculo. En esta sección, utilizaremos una función =IF para determinar si un estudiante está aprobando o reprobando la clase. Luego, utilizaremos una función =BUSCARV para determinar qué calificación ha obtenido cada alumno.

Función IF

La función IF es una de las funciones más populares en Excel. Te permite hacer comparaciones lógicas entre un valor y lo que esperas. En su forma más simple, la función IF dice algo así como:
Si el valor en una celda es lo que esperas (true) — haz esto. Si no, haz eso.

La función IF tiene tres argumentos:

• Prueba lógica — Aquí, podemos probar para ver si el valor en una celda seleccionada es lo que esperamos. Podrías usar algo como “B7=14" o “B7>12" o “B7<6"
• VALOR_IF_VERDADERO — Si se cumplen los requisitos en la prueba lógica —si B7 es igual a 14— entonces se dice que es cierto. Para este argumento, se puede escribir texto — “Verdadero”, o “¡En presupuesto!” O podrías insertar un cálculo, como B7*2 (Si B7 hace igual a 14, multiplíquelo por 2). O bien, si quieres que Excel no ponga nada en la celda, escribe “” (dos comillas).
• Value_si_falso — Si no se cumplen los requisitos en la prueba lógica —si B7 no es igual a 14— entonces se dice que es falso. Puede ingresar las mismas instrucciones aquí que lo hizo anteriormente. Digamos que tecleas las comillas dobles aquí. Entonces, si B7 no es igual a 14, no se mostrará nada en esta celda.

En la columna Q nos gustaría que Excel nos dijera si un estudiante está aprobando —o reprobando la clase. Si el alumno puntúa 70% o mejor, aprobará la clase. Pero, si anota menos del 70%, está fallando.

1. Asegúrate de que Q5 sea tu celular activo.
2. En la pestaña Fórmulas, en el grupo Biblioteca de funciones, busque la función IF en el menú desplegable Lógico (consulte la Figura 3.9).
   Usuarios de Mac: No hay un grupo “Biblioteca de funciones” para Excel para Mac. Los usuarios de Mac deben hacer clic en la pestaña Fórmulas, luego hacer clic en la flecha de lista de herramientas “Lógicas” y elegir IF (ver Figura 3.9).

Figura 3.9 Función IF

Ahora verá el cuadro de diálogo Función IF, con un lugar para ingresar cada uno de los tres argumentos.
Usuarios de Mac: No hay un “cuadro de diálogo”. El panel “Formula Builder” se mostrará en el lado derecho de la ventana de Excel. Tiene el mismo diseño que la Figura 3.10 a continuación.

1. Haga clic en la casilla correspondiente a Prueba lógica. Tenemos que probar si la puntuación de un estudiante es inferior a .7. Entonces, en esta caja, escriba P5<.7
2. Haga clic en la casilla de Value_if_true. Si la puntuación del alumno es inferior a .7, entonces están reprobando la clase. En este cuadro, escriba Fail.
3. Haga clic en la casilla de Value_if_false. Si la puntuación del alumno NO es inferior a .7, entonces están superando la clase. En este cuadro, escriba Pase.
4. Asegúrese de que su cuadro de diálogo coincida con la Figura 3.10.

Figura 3.10 Cuadro de diálogo Función IF

Mientras estamos aquí, echemos un vistazo al cuadro de diálogo. Observe que a medida que da clic en cada cuadro, Excel le da una breve explicación del contenido (en el medio debajo de las casillas). En la esquina inferior izquierda, se pueden ver los resultados del cálculo. En este caso, DeShae está pasando la clase. Debajo de eso hay un enlace a Ayuda sobre esta función. Al seleccionar este enlace, accederá a la ayuda de Excel para esta función, con información detallada sobre cómo funciona.

5. Una vez que haya escrito los argumentos requeridos y revisado para asegurarse de que son correctos, presione OK.
   Los usuarios de Mac deben hacer clic en el botón “Listo” y luego cerrar el panel de Formula Builder.
   (El texto Pass debe mostrarse en el Q5 porque DeSHAE está pasando la clase).
6. Utilice el identificador de Relleno para copiar la función IF hacia abajo a través de la fila 24.
7. Haga clic en Q5. Al mirar en la barra de fórmulas, verá el cálculo IF: =IF (P5<0.7,” Fail”,” Pass”) (ver Figura 3.11).

Figura 3.11 Resultados de la función IF

Función BUSCARV

Es necesario utilizar una función BUSCARV para buscar información en una tabla. A veces esa mesa está en una hoja diferente en tu libro de trabajo. A veces está en otro archivo enteramente. En este caso, necesitamos saber qué calificación obtiene cada alumno en función de su puntaje porcentual. Encontrarás la tabla que define las puntuaciones y las calificaciones en A28:B32.

Hay cuatro piezas de información que necesitará para construir la sintaxis VLOOKUP. Estos son los cuatro argumentos de una función BUSCARV:

• El valor que desea buscar, también llamado Lookup_value. En nuestro ejemplo, el valor de búsqueda será la puntuación porcentual del estudiante en la columna P.
• El Table_array es el rango (tabla) donde se encuentran los valores de búsqueda y los valores que desea devolver por la función. En nuestro ejemplo, esta es la tabla de porcentajes y calificaciones de letras correspondientes en el rango A28:B32. El valor de búsqueda siempre debe estar en la primera columna de la matriz de tablas para que BUSCARV funcione correctamente. Por ejemplo, en nuestro table_array el valor de búsqueda está en la celda A28, por lo que el rango debería comenzar con A.
• El Col_index_num es el número de columna en el rango que contiene el valor a devolver. En nuestro ejemplo, cuando especifica A28:B32 como el rango Table_array, debe contar A como la primera columna (1), B como la segunda columna (2), y así sucesivamente. Ingresará el número de columna correspondiente en esta casilla como 1, 2 o 3 y así sucesivamente.
• En Range_Lookup, puede especificar opcionalmente VERDADERO si desea una coincidencia aproximada o FALSE si desea una coincidencia exacta del valor devuelto. Si deja este espacio en blanco, el valor predeterminado siempre será VERDADERO, o coincidencia aproximada.

Vamos a crear el BUSCARV para mostrar la calificación correcta de la letra en la columna R.

1. Asegúrate de que R5 sea tu celular activo.
2. En la pestaña Fórmulas, en la Biblioteca de funciones, busque la función BUSCARV en el menú desplegable Búsqueda y referencia (consulte la Figura 3.12).
   Los usuarios de Mac deben hacer clic en la flecha de lista de herramientas de búsqueda y referencia para encontrar la función BUSCARV.

Figura 3.12 Función BUSCARV

3. Rellene el cuadro de diálogo para que se vea como la imagen de la Figura 3.13.

Los usuarios de Mac usarán el panel “Creador de fórmulas” en el lado derecho de la ventana de Excel.

4. • Lookup_value — En este caso, utilizaremos la puntuación de Porcentaje. Entonces, P5 para el primer valor de búsqueda.
   • Table_array — Este es el rango que contiene el valor que desea que devuelva la función. En este caso, ese rango es A28:B32. Tenga en cuenta que este rango NO incluye la etiqueta en la fila 27; solo los datos reales. Las referencias de celda para Table_array deben ser absolutas — $ A $28: $ B $32. Cuando copiamos esta función a las otras celdas, no queremos que cambien estas referencias de celda. Siempre debe ser $ A $28: $ B $32. ¡Esto es muy importante! Deben tener los símbolos de referencia absolutos o los cálculos no funcionarán.
   • Col_index_number — Esta es la columna en el rango de matriz de tablas que incluye la información que estamos buscando. En nuestro caso, las calificaciones reales están en la 2da columna del rango. Entonces, el índice de columna será 2.
   • Range_lookup — En algunos casos, necesitará algo en el cuadro Range_lookup. Ya que estamos buscando una coincidencia aproximada para los porcentajes, queremos el valor predeterminado de TRUE, por lo que no necesitamos ingresar nada para este argumento.
5. Mientras esté en el cuadro de diálogo, asegúrese de ver todas las definiciones útiles que ofrece Excel.
6. Cuando haya rellenado el cuadro de diálogo, presione OK.
   Los usuarios de Mac deben hacer clic en el botón “Listo” y luego cerrar el panel de Formula Builder.
7. El cálculo que verá en la barra de fórmulas es: =BUSCARV (P5, $ A $28: $ B $32,2)
8. Utilice el identificador de relleno para copiar la función hacia abajo a través de la fila 24. Los resultados mostrados deben coincidir con la Figura 3.14.

Figura 3.13 Cuadro de diálogo VLOOKUP completado

Figura 3.14 BUSCARV Completo

Nota

¿Y si no funcionó? ¿Y si obtienes un resultado diferente al predicho? En este caso, o has cometido un error previo, resultando en puntuaciones de% diferentes a las previstas en este ejercicio, o has cometido un error al ingresar tu función BUSCARV.

Para hacer reparaciones en la función, asegúrate de que R5 sea tu celda activa. En la barra de fórmulas, presione el botón Insertar función (ver Figura 3.15). Eso volverá a abrir el cuadro de diálogo para que pueda hacer sus reparaciones. ¿Olvidaste hacer absolutas las referencias de celda para Table_array? ¿Usaste la celda equivocada para el valor de búsqueda? Presiona OK cuando hayas terminado y vuelve a copiar la función corregida.

Figura 3.15 Función de inserción

Mensajes de error

A veces Excel nota que has cometido errores en tus cálculos antes que tú. En esos casos Excel te alerta con algunos mensajes de error un poco misteriosos. Una lista de mensajes de error comunes se puede encontrar en la Tabla 3.1 a continuación.

Tabla 3.1 — Mensajes de error comunes

Mensaje	Lo que salió mal
#DIV /0!	Intentaste dividir un número por un cero (0) o una celda vacía.
#NAME	Usó un nombre de rango de celdas en la fórmula, pero el nombre no está definido. En ocasiones este error ocurre porque escribes el nombre incorrectamente.
#N /A	La fórmula se refiere a una celda vacía, por lo que no hay datos disponibles para computar la fórmula. A veces las personas ingresan N/A en una celda como marcador de posición para señalar el hecho de que aún no se ingresaron datos. Revisar la fórmula o introducir un número o fórmula en las celdas vacías.
#NULL	La fórmula se refiere a un rango de celdas que Excel no puede entender. Asegúrese de que el rango esté ingresado correctamente.
#NUM	Un argumento que usas en tu fórmula no es válido.
#REF	La celda o rango de celdas a las que se refiere la fórmula no están ahí.
#VALUE	La fórmula incluye una función que se usó incorrectamente, toma un argumento no válido o está mal escrita. Asegúrese de que la función use el argumento correcto y esté deletreada correctamente.

Esta tabla fue copiada de internet. Busque aquí para obtener información adicional.
http://www.dummies.com/software/microsoft-office/excel/how-to-detect-and-correct-formula-errors-in-excel-2016/

Funciones de fecha

Muy a menudo las fechas y horas son una parte importante de los datos de Excel. Los números que hoy son correctos pueden no ser exactos mañana. Entonces, frecuentemente es útil incluir fechas y horas en tus hojas de cálculo.

Estas fechas y horarios se dividen en dos categorías, las que:

• Permanecer igual. Por ejemplo, si una hoja de cálculo incluye datos del 15 de mayo, no quieres que la fecha cambie cada vez que vuelvas a abrir la hoja de cálculo.
• Cambiar para reflejar la fecha/hora actual. Cuando es importante tener la fecha u hora actual en una hoja de cálculo, desea que Excel actualice la información regularmente.

Eche un vistazo a la lista de funciones de Fecha y Hora que se ofrecen en la Biblioteca de Funciones en la pestaña Fórmulas (ver Figura 3.16).

Figura 3.16 Funciones de fecha y hora

Para nuestro libro de calificaciones, queremos que la fecha y la hora se muestren en A2, y debe actualizarse cada vez que se abra el archivo del libro de trabajo.

1. Haz de A2 tu celular activo. Observe que A2 se extiende desde la columna A hasta la columna R. Anteriormente, alguien ha utilizado la herramienta Fusionar y centrar en esta celda para que coincida con el título anterior.
2. En la pestaña Fórmulas, en la Biblioteca de funciones, seleccione AHORA en la lista desplegable Fecha y hora y luego haga clic en Aceptar.
   Los usuarios de Mac hacen clic en el botón “Listo” en el panel “Formula Builder” en el lado derecho de la ventana de Excel; luego cierran el panel.
3. El resultado que verás en la barra de fórmulas es: =NOW (). El resultado que verás en A2 depende de la fecha y hora actuales. La función NOW es una función muy práctica. ¡No se necesitan argumentos y es Volátil! Eso no es tan alarmante como puede parecer. Esto solo significa que no necesitas darle más información para hacer su trabajo y que tus resultados cambiarán con frecuencia. Puedes actualizar la fecha y la hora cuando quieras; no tienes que esperar hasta que vuelvas a abrir el libro de trabajo.
4. Asegúrate de que A1 sea tu celda activa y presiona la tecla de función F9 (en la parte superior de tu teclado). El tiempo se actualizará.

Excel actualizará este campo de forma independiente cada vez que guarde y vuelva a abrir el archivo, o imprimirlo. Puede suceder con más frecuencia que eso, dependiendo de cómo lo haya configurado en su instalación de Excel.

Otra variación de la fecha actual es la función HOY. Vamos a probar ese siguiente.

1. Asegúrate de que A2 sea tu celular activa. Presiona Eliminar para eliminar la función AHORA.
2. En la lista desplegable Fecha y hora en la Biblioteca de funciones en la pestaña Fórmulas (consulte la Figura 3.16), seleccione HOY y luego haga clic en Aceptar.
   Los usuarios de Mac hacen clic en el botón “Listo” en el panel “Formula Builder”; luego cierran el panel.
3. El resultado que verás en la barra de fórmulas es: =HOY (). El resultado que verás en A2 depende de la fecha actual. Como no hemos pedido la hora, la hora que estás viendo es probable a las 12:00. Eso no es muy útil por lo que necesitamos cambiar el formato de la fecha.
4. En la pestaña Inicio, en el grupo Número, presione el botón Iniciador de Formato de Número (ver Figura 3.17).
5. En el cuadro de diálogo Formato de celdas, haga clic en la ficha Número. Elija la categoría Fecha y seleccione Miércoles 14 de Marzo de 2012 (este formato se llama Fecha Larga).
   Usuarios de Mac: no hay ningún botón Number Format Launcher ni un cuadro de diálogo “Formatear celdas”.
   Haga clic en la flecha de lista junto a “Fecha” y elija “Fecha larga”
6. El día y la fecha actuales se mostrarán en A2.

Figura 3.17 Iniciador de formato de número

Atajos de teclado

A veces quieres que la fecha o la hora aparezcan en tu hoja de cálculo, pero no quieres que cambie. Simplemente puedes escribir la fecha u hora. O bien, puedes usar teclas de método abreviado.

• CTRL; (punto y coma) le traerá la fecha actual Usuarios de
  Mac: igual que arriba

• CTRL: (dos puntos o CTRL MAYÚS;) le traerá la hora actual.
  Usuarios de Mac: SHIP COMMAND

Principales conclusiones

• Las funciones no siempre tienen que ser sobre aritmética. Excel proporciona funciones que le ayudarán a realizar evaluaciones lógicas, buscar cosas y trabajar con fechas y horas.
• Excel muestra mensajes de error cuando sus fórmulas y funciones no están construidas correctamente.

Atribución

3.2 Funciones lógicas y de búsqueda de Noreen Brown y Mary Schatz, y Art Schneider, Portland Community College, tiene licencia CC BY 4.0