Top 10 Fórmulas esenciales en #Excel

 FORMULAS ESENCIALES DE EXCEL

En el presente blog te presente 10 fórmulas que deberias manejar para cubrir la mayoría de tus tareas de análisis de datos, análisis financiero, análisis de negocios, adminsitrar negocios, etc.

Top 10 - fórmula SI.ERROR

La función SI.ERROR es utilizada para devolver en el segundo campo (Valor_si_error), en el caso de que la expresión o referencia del Valor es un error.

Valor: es cualquier valor, expresión o referencia

Valor_si_error: es cualquier valor, expresión o referencia

Top 9 - fórmulas IZQUIERDA, EXTRAE y DERECHA

Éstas funciones nos devuelve el valor extraído de un texto, en Texto escribimos o seleccionamos la celda que tiene el texto, y en Núm_de_caracteres escribimos la cantidad de caracteres a extraer.

El uso de extrae y derecha es de manera similar al de izquierda.

Top 8 - fórmula CONCATENAR - &

La fórmula concatenar y & nos realiza una unión de texto o expresiones.

Top 7 - fórmula SECUENCIA

Secuencia nos dá un resultado ordenado según los argumentos que le damos para que nos genere un secuencia de números, caractéres y más formatos según lo configuremos.

Fila: El número de filas que se va a devolver

Columnas: El número de columnas que se va a devolver

Inicio: El primero número de la secuencia

Paso: La cantidad en que se incrementa cada valor subsiguiente de la secuencia

Top 6 - fórmula FECHA.MES

Devuelve el número de serie de la fecha que es el número indicado de meses antes ó después de la fecha inicial

Fecha_inicial: Es un número de fecha de serie que representa la fecha inicial.

Meses: Es el número de meses antes o después de la fecha_incial

Top 5 - fórmula K.ÉSIMO.MAYOR, K.ÉSIMO.MENOR

K.ÉSIMO.MAYOR

Devuelve el valor k.ésimo.mayor de un conjunto de datos. Por ejemplo, el trigésimo número más grande.

Matriz: es la matriz o rango de datos vuyo valor k-ésimo mayor desea determinar

K: representa dentro de la matriz o rango de datos la posición, a partir del valor más alto, del dato a devolver

K.ÉSIMO.MENOR

Devuelve el valor k.ésimo.menor de un conjunto de datos. Por ejemplo, el trigésimo número menor.

Matriz: es la matriz o rango de datos vuyo valor k-ésimo menor desea determinar

K: representa dentro de la matriz o rango de datos la posición, a partir del valor más bajo, del dato a devolver

Top 4 - fórmula SUMAR.SI.CONJUNTO

Suma las celdas que cumplen un determinado conjunto de condiciones o criterios

Rango_suma: son las celdas que se van a sumar

Rango_criterios1: es el rango de celdas que desea evaluar para la condición determinada

Criterio1: es el criterio o condición que determina qué celdas deben sumarse. Puede estar en forma de número, texto o expresión

Top 3 - fórmula FILTRAR

Filtra un rango o matriz

Arrary: el rango o matiz que se va a filtrar

Include: una matriz de valores booleanos donde TRUE representa una fila o columna que se va a conservar

If_empty: se devuelve si no se conserva ningún elemento

Top 2 - fórmula BUSCARX

La función BUSCARX busca en un rango o una matriz y, a continuación, devuelve el elemento correspondiente a la primera coincidencia que encuentra. Si no existe ninguna coincidencia, BUSCARX puede devolver la coincidencia más cercana (aproximada).

Valor_buscado: es el valor que se buscará

Matiz_buscada: es la matriz o rango donde se buscará

Matriz_devuelta: es la matriz o rango donde se devolverá

Si_no_se_ecnuentra: valor devuelto si no se encuentra ningurna coincidencia - opcional

Modo_de_coincidencia: especifica cómo comparar el valor_buscado con los valores de la matriz_buscada

Modo_de_búsqueda: especifica el modo de búsqueda que se usará. De forma predeterminada, se usará una búsqueda de primera a última.

Top 1 - fórmulas INDICE, COINCIDIR

INDICE

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

Matriz: es un rango de celdas o una constante de matriz.

Núm:fila: selecciona, en Matriz o Referencia, la fila desde la cual se devolverá un valor. Si se omite, se requerirá núm_columna.

Núm_columna: selecciona, en Matriz o Referencia, la fila desde la cual se devolverá un valor. Si se omite, se requerirá núm_fila.

COINCIDIR

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

Valor_buscado: es el valor que se usa para encontrar el valor deseado en la matriz y puede ser un número, texto, valor lógico o una referencia a uno de ellos.

Matriz_buscada: es un rango contiguo de celdas que contiene posibles valores de búsquedas, una matriz de valores o una referencia a una matriz.

Tipo_de_coincidencia: es un número 1, 0, -1 que indica el valor que se devolverá.

A continuación te presento un link con ejemplo de las funciones anteriormente explicadas:

Formulas de Análisis V02.xlsx

También un video de su uso:

DAR FORMATO CONDICIONAL

 FORMATO CONDICIONAL

Para dar formato condicional a nuestras celdas podemos seguir los siguientes pasos:

• Seleccionamos la columnas o rangos de celdas que queremos dar el formato condicional, en mi caso elijo la columna de Fecha de Vencimiento, columna D

• Selecciono Formato Condicional

• Dentro de Reglas para resaltar celdas elijo Entre

• En mi ejemplo voy a ingresar dos reglas, 

Primera relga a ingresar formato condicional

• La primera que mi fecha esté entre hoy y 60 dias y la resalto con rojo

• En el lado izquierdo coloco =HOY() y luego coloco =HOY()+60

• Luego hago clic en el deplegable donde dice Relleno rojo con texto rojo oscuro para configurar el color, selecciono formato personalizado
• Dentro de la pestaña Relleno elijo el color que deseo, en mi caso rojo y presiono aceptar

Segunda relga a ingresar formato condicional

• La primera que mi fecha esté entre hoy y 120 dias y la resalto con amarillo

• En el lado izquierdo coloco =HOY() y luego coloco =HOY()+120

• Luego hago clic en el deplegable donde dice Relleno rojo con texto rojo oscuro para configurar el color, selecciono formato personalizado

• Dentro de la pestaña Relleno elijo el color que deseo, en mi caso AMARILLO y presiono aceptar

ADMINISTRAR RELGAS

• Finalmente ordeno mis reglas ya que debe estar la que se cumpla primero en primer lugar y luego las demás..
• Don clic en Formato condicional y luego en Administrar reglas.

• Entonces procedemos a ordenar el que se cumple en 60 dias y luego el de 120 dias, si tenemos mas reglas debemos ordenar en función de la que se cumple mas pronto.

• Debe quedar en mi ejemplo de la siguiente manera, presionamos aplicar y aceptar

• Con ello nuestras celdas se resaltarán de manera automática al momento que ingresamos las fechas dentro de Fecha de Vencimiento en mi ejemplo.

• A continuación puedes ver un video demostrativo sobre el tutorial...

Muchas gracias por leer mi blog y ver mi video demostrativo...

•  

Actualizar Tabla Dinámica en Excel de manera automática

 Actualizar mi hoja de trabajo y/o tabla dinámica en Excel con el uso de VBA

En el presente blog, te explico como puede realizar una actualización de tu tabla dinámica de manera inmediata a medida que vayas realizando cambios ó ingresando nuevos datos en tu tabla de excel.

Primero método

• Ingresamos a la interfaz VBA dando clic derecho en nuestra hoja (mi caso en Hoja1) y seleccionamos Ver código

• Seleccionamos Workbook e ingresamos el código entre la función que nos aparece.

• Usamos comilla simple ' para colocar nuestro texto ó código en comentario, al hacerlo éste no  se ejecuta, solo sirve como comentario.
• Podemos utilizar el siguiente código para actualizar todo nuestro workbook de manera automática: ThisWorkbook.RefreshAll

Segundo método

• También podemos utilizar el siguiente código para actualizar haciendo referencia al nombre de nuestras tablas dinámicas presente en nuestra hoja: ActiveSheet.PivotTables("TablaDinámica1").PivotCache.Refresh

Nuestro código se verá así

Con el uso de cualquiera de los dós códigos podemos realizar algún cambio en nuestros datos y nuestra tabla dinámica se actualizará de manera automática.

A continuación les presento un video tutorial para que visualicen el uso del mismo.

Saludos y gracias por ver mi blog 

Pestaña Programador en Excel

 Habilitar la pestaña programador en Excel

En la cinta de pestañas principales de excel por default no nos aparece la pestaña "Programador", la cual nos permite crear y grabar macros, crear formularios, crear aplicaciones VBA, etc. Para activarla puedes seguir estos pasos:

• Damos clic en menú Archivo

• Nos dirigimos a Opciones

• Seleccionamos Personalizar cinta de opciones

• Visualizamos Pestañas Principales, por default esta deshabilitada Programador

• Seleccionamos ó habilitamos Programador y damos clic en Aceptar

• Finalmente visualizamos en las pestañas principales el menú "Programador"

A continuación puedes visualizar un video sobre la ahbilitación de la pestaña programador:

Excel habilitado para macros

Para utilizar tu archivo con macros debes fuardar el mismo como libro de Excel habilitado para macros.

Para hacerlo, realiza lo siguiente:

• Selecciona Archivo
• Haz clic en Guardar como
• Se visualiza

• Digita un nombre para tu archivo
• En tipo seleccionamos y verificamos que sea del tipo Libro de Excel habilitado para macros es decir .xlsm
• Haz clic en guardar

Con ellos ya tienes habuilitado la pestaña Programador y también tu archivo guradado en .xlsm para utilizarlo con macros.

En el siguiente link te presento una lista de reproducciones videotutoriales del uso de VBA con Excel:

Utilidades de interfaz Programador (VBA) con Excel

Saludos,

Como crear una presentación concurrida "Bar Chart Race" en Google Sheets

 BAR CHART RACE

Cuando tenemos un historial de datos, sabemos que nos pueden proporcionar información valiosa y la mejor forma para sacar provecho de esos datos es utilizando gráficos.

Ahora te voy a explicar cómo podemos insertar un tipo de gráfico animado ó presentación llamado bar chart race, qué significa carrera de gráfico de barras, el cual nos muestra cómo se comportan nuestros datos a lo largo del tiempo de una manera interactiva.

Disponer nuestra data

Todos nuestros datos deben estar organizados, limpios, en otras palabras preparados, y antes que nada debemos pensar primero en lo que queremos investigar en base a nuestra información. La data que vamos a utilizar muestra información del valor de mercado de varias empresas mundiales a lo largo de los años.

Cada fila o dato nos indica el valor en dolares de una empresa asociada a una categoria desde el año 2000 al 2022. La fecha está con formato de dia mes y año, en mi ejemplo usaré el año, pero eso no es obstáculo para que nuestro gráfico muestre el recorrido en dias ó meses, eso depende de como quieres mostrar la animación. La catgoría de nuestra data se usará para distinguir y comparar las industrias que se van mostrando en cada barra horizontal de nuestra gráfica.

Tomar en cuenta el orden de los datos "date, name, category, value"; si tus datos están organizados de diferente forma, debes ordenarlos para adaptarlos el formato de la data de ejemplo para que te funcione correctamente.

Creación de funciones de Apps Script

La interprestación de lo que queremos realizar se resumen en:

• Leer los datos de nuestra hoja de cálculo
• Crear componentes HTML desde un menú, para ello utilizaremos la libreria de graficación D3.js
• Link de código y fuente de ejemplo de Bar Chart Race para D3.js creado por Takanori Fujiwara
• Mostrar la gráfica interactiva

Código.gs

Entonces para empezar, abrimos el menú Extensiones y damos clic en Apps Script.

Reemplazamos el código que aparece por default por el siguiente:

//creación de menú "Gráficas" que llama a la función que genera la gráfica

function onOpen() {

 SpreadsheetApp.getUi()

  .createMenu("Gráficas")

    .addItem("Generar gráfica", "grafica_html")

    .addToUi();

}

// función para leer los datos de nuestra hoja de cálculo

function leer_datos() {

  var ultima_fila = SpreadsheetApp.getActiveSheet().getLastRow();

  var ultima_columna = SpreadsheetApp.getActiveSheet().getLastColumn();

  var dataRange = SpreadsheetApp.getActiveSheet().getRange(fila_inicial=2, columna_inicial=1,ultima_fila, ultima_columna);

  var data_values = dataRange.getValues();

  // se mapea cada elemento del arreglo a una propiedad en el orden que vienen las columnas y se filtran valores de name vacíos

  return data_values.map(([date, name, category, value]) => (

    { date, name, category, value }))

    .filter(d => (d.name == "" ? false : true));

}

//función para graficar los datos

function grafica_html() {

  var html = HtmlService.createTemplateFromFile('grafica');

  html.datos = leer_datos();

  SpreadsheetApp.getUi().showModalDialog(html.evaluate().setHeight(800).setWidth(1000),"Gráfica");

}

Componentes HTML

Ahora tenemos que crear los archivos html necesarios para graficar.

El primer archivo de llama grafica, lo creamos dando clic en el signo +, elejimos la opción HTML y lo nombramos.

Gráfica.html

Dentro de grafica.html reemplazamos el default por el siguiente código:

<!DOCTYPE html>

<html>

  <head>

    <style>

      html, body {

        overflow: hidden;

        font-family: Arial, Helvetica, sans-serif;

        width: 100%;

        height: 100%;

        margin: 0;

        padding: 0;

      }

      #bar-chart-race > text:last-child {

          font-size: 4em !important;

          font-weight: bold !important;

      }

      #bar-chart-race g ~ g ~ g text {

        font-weight: bold !important;

      }

    </style>

    <base target="_top">

  </head>

  <body>

    <script src="https://cdnjs.cloudflare.com/ajax/libs/d3/7.6.1/d3.min.js"></script>

    <?!= HtmlService.createHtmlOutputFromFile('chartjs').getContent(); ?>

    <script>

      const data_sheet = <?!= JSON.stringify(datos) ?>;

      const data = data_sheet.map(x => {

        x.date = new Date(x.date);

        return x;

      });

      const play = () => {

        const chart = barChartRace(data, {

          svgId: 'bar-chart-race'

          });

          d3.select('#bar-chart-race').remove();

          d3.select('body').append(() => chart);

          chart.play();

      }

      const button = d3.select('body').append('div').append('button')

        .attr('type', 'button').attr('class','reiniciar').text('Reiniciar');

      // iniciar animación al cargar la página

      play();

      // reiniciar al dar click en el botón

      button.on('click', () => {

        play();

      });

    

    </script>

  </body>

</html>

El segundo archivo HTML necesario es el que tomaremos del repositorio  de Takanori Fujiwara Archivo chart.js, lo creamos de la misma manera dando clic en el signo +, elejimos la opción HTML y le colocamos el nombre chartjs, tomar en cuenta que no se debe colocar .html ya que apps script lo coloca automaticamente.

En esta parte borramos su contenido y creamos un tipo de atributo script de HTML <script></script> y dentro de este script pegamos el código del archivo chart.js del repositorio de Takanori Fujiwara.

Luego de pegar el código nos dirigimos a la fila donde se define la constante barChartRace y eliminamos la palabra export debido a que Apps Script no soporta los módulos de la especificación ES6 de JavaScript.

También puedes modificar de manera opcional la variable n y width, las cuales nos muestran las cantidades de barras horizontales y el ancho de nuestra gráfica, en mi ejemplo lo dejé en n=10 (diez barras horizontales) y width=1000.

Finalmente damos clic en guardar proyecto

Generar gráfica interactiva Bar Chart Race en mi hoja 

de cálculo de Google Sheets

Finalmente refrescamos nuestra hoja de cálculo y debería aparecer nuestro menú para generar el gráfico animado.

La primera vez que usamos nos pedirá autorización para ejecutar código de apps script, lo permitimos y volvemos a usar el menú, finalmente observamos el resultado.

Resaltar filas y columnas con uso de Visual Basic en Excel

 

RESALTAR FILAS Y COLUMNAS DE MI HOJA ACTIVA EN EXCEL

 

Para resaltar hojas filas y columnas en referencia a la celda que elijo dentro de mi hoja de Excel, podemos realizarlo mediante la interfaz de Programación de Visual Basic para Aplicaciones con Microsoft Excel.

Habilitar pestaña de Programador en Excel

 

Para habilitar la pestaña Programador realizamos lo siguiente:

·         Seleccionamos Opciones dentro de Archivo

 

·         Luego habilitamos con un  visto la herramienta Programador

 

Teniendo habilitado Programador

 

 

·         Elegimos Visual Basic y podemos codificar nuestras hojas de Excel individuales o a su vez un código general que abarque nuestro libro de trabajo de Excel, solo con guardar luego de escribir el código se ejecuta automáticamente en nuestro libro de trabajo.

 

Código para resaltar filas y columnas enteras de Excel

 

El siguiente es el código para resaltar filas y columnas enteras:

 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub

    Application.ScreenUpdating = False

    ' Clear the color of all the cells

    Cells.Interior.ColorIndex = 0

    With Target

        ' Highlight the entire row and column that contain the active cell

        .EntireRow.Interior.ColorIndex = 8

        .EntireColumn.Interior.ColorIndex = 8

    End With

    Application.ScreenUpdating = True

End Sub

Fuente: https://learn.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/highlight-the-active-cell-row-or-column

Video de como realizar el resaltado de filas y columnas enteras en Excel

 

 

Resaltar filas y columnas activas en google sheets

 Resaltar filas y columnas activas en google sheets con AppsScript

Introducción

AppsScript nos permite interactuar con nuestras hojas de cálculo de google para realizar cualquier acción sobre ellas, como dar formato, cálculos automáticos, consultas, etc.

Función AppScript para resaltar filas y columnas activas en google sheets

function myFunction() {

  // creación de variables

  var range = SpreadsheetApp.getActiveRange(); // obtengo el rango activo de mi hoja de cálculo}

  var sheet = range.getSheet();                // obtengo a hoja activa que estoy utilizando

  var filas = SpreadsheetApp.getActiveRange().getSheet().getMaxRows(); // establezco la variable filas, la cual seleccione a todas las filas de mi rango activo en mi hoja de cálculo

  var columnas = SpreadsheetApp.getActiveRange().getSheet().getMaxColumns(); // establezco la variable columnas, la cual seleccione a todas las columnas de mi rango activo en mi hoja de cálculo

  // acciones para resaltado de filas y columnas

  sheet.getRange(1,1,filas,columnas).setBackground(null); // Seteo o borro el formato de mi hoja activa

  sheet.getRange(1,range.getColumn(),filas,range.getNumColumns()).setBackground("yellow"); // establezco la columna activa en color "yellow"

  sheet.getRange(range.getRow(),1,range.getNumRows(),columnas).setBackground("yellow"); // establezco la fila activa en color "yellow"

}

Beneficios y características de formato tabla - Microsoft Excel

 FORMATO TABLA DE EXCEL

Introducción

El uso de formato tabla que nos ofrece Microsoft Excel, nos permite interactuar de una manera mas productiva con nuestro datos, como por ejemplo: seleecionar filas, columnas y tabla entera con un solo clic, ingresar filas y columnas de manera automática, sobrellenar formulas con solo escribirla en la primera fila, etc.

Características y/o Beneficios

Los beneficios y características son muchos y bastante productivos, entre los cuales te puedo resumir los siguientes:

Formato y estilos visuales

Al estar dentro de nuestro rango de datos que queremos darle el formato tabla, nos dirigimos a la ventana de Inicio y buscamos "Dar formato como tabla", luego seleccionamos el estilo que mas nos guste.

Agregar filas y columnas automáticamente

Al estar en la última celda de nuestra tabla podemos ingresar una fila con la tecla TAB, también podemos hacer clic derecho, luego en Insertar podemos elegir Filas de la tabla arriba o abajo.

Para agregar columnas hacemos clic o nos dirigimos en la siguiente columna que queremos ingresar un encabezado o campo, y damos el  nombre a nuestro en cabezado nuevo y presionamos ENTER, también podemos ingresar una formaular o acción lo cual se reflejará o nos permitirá sobrellenar en las siguiente celdas de nuestra columna nueva ingresada.

Selección de filas, columnas y tabla entera

Con solo dirigirnos de manera ligera a nuestra prima fila yó columnas podemos seleccionar toda su fila y/ó columna de manera rápida y eficaz, y aprovecha su uso para dar formato a ciertos datos que querramos, como ejemplo: seleccionamos ciertas columnas que no tengan formato dolar para dárselo.

Movimiento inteligente de columnas de datos

Si queremos modificar la ubicación de columnas podemos realizar de manera fácil y rápida, primero debemos tener una espacion nuevo de columna, luego seleecionar la columna que queremos, manteniendo presionado la arrastramos a nuestro nuevo destino y cambiamos los nombres de los encabezados también.

Interactuar con otra tabla

Podemos tomar referencias o datos de mas tablas para poder realizar consultar y extraer datos dentro de nuestra tabla y fuera de ella, lo cual es bastante productivo, en el ejemplo que observan, buscarco la categoria de cada producto en la TablaCategoria que esta en otra hoja de cálculo.

Fila de totales

Estando dentro de nuestra tabla (cualquier celda) nos dirigimos a Diseño Tabla, dentro de opciones de estilo de tabla seleccionamos ó habilitamos Fila de total

Filtros inteligentes - fila de totales enlazados a esos filtros

Si seleccionamos o filtramos ciertos productos o celdas, nuestro resultado de total siempre nos vá a mostrar el valor respecto a esos filtros.

Segmentación de datos

Podemos ingresar la característica de Segmentar datos- Slicers para poder filtrar de manera interactiva nuestro datos.

Video en Youtube

Conclusiones

El uso de formato tabla hace que nuestro trabajo sobre datos se haga mas facil de usar e interpretar.