Cómo encontrar combinaciones que equivalen a una suma dada en Excel? 2a Parte - Formulas

En la entrada anterior vimos la primera parte, de

Cómo encontrar combinaciones que equivalen a una suma dada en Excel? 1a Parte - Solver.

Vimos cómo resolver el problema mediante el complemento Solver.

Cómo encontrar combinaciones que equivalen a una suma dada en Excel? 1a Parte - Solver

¿Cómo encontrar todas las combinaciones que equivalen a una suma dada en Excel?


Por ejemplo, teniendo la siguiente lista de números, y ahora, queremo saber qué combinación de números en la lista suma hasta 480, en la siguiente captura de pantalla que se muestra, puede ver que hay cinco grupos de combinaciones posibles que suman igual a 480, como 300 + 60 + 120, 300 + 60 + 40 + 80, etc. 

En esta entrada, hablaremos sobre algunos métodos para encontrar qué celdas suman un valor específico en Excel.

Also así como BuscarV horizontalmente para múltiples registros

Hola a todos los lectores

Hoy quiero mirar una situación que seguro que nos hemos encontrado bastantes veces, eso, si trabajamos con Excel habitualmente.

Miramos los siguientes datos:

Tenemos una tabla con distintas opciones de ocio y sus precios.

y tenemos otra con la combinación de las actividades.

Lo necesario sería calcular el precio total para cada opción. Veamos algunas maneras de hacerlo.

Cómo desplazar simultáneamente 2 archivos o 2 hojas de Excel

Hola!

La publicación de hoy se trata de uno de esos momentos en los que te das cuenta de que sólo necesitas mirar los datos sin procesar (en lugar de crear un pivottable) y digamos a partir de 2 o más archivos.

Para evitar que continuamente estemos presionando ALT + TAB, se pueden desplazar dos o más archivos de Excel simultáneamente. Sí, en serio..!

Así es como funciona:

Cómo permitir solo la entrada de caracteres alfanuméricos en Excel

Buen día a todos los lectores

Perdonar mi ausencia en estas semanas, pero he estado trabajando intensamente para un proyecto de VBA, en el que una empresa multinacional me contactó y contrató.

Hoy recibí una consulta de un lector el cual quería limitar la entrada mediante validación de datos de qué tipo de caracteres podrían introducirse.

Asi que hoy veremos eso.

Para algunos propósitos especiales, solo puede permitir la entrada de caracteres alfanuméricos o numéricos en una columna, ¿cómo se puede hacerlo? En este artículo, vamos a presentar las formas de resolver este problema en Excel.

Cómo imprimir una lista del administrador de nombres

Hola, en primer lugar perdón por no haber escrito en unas semanas, pero he estado ocupado con un proyecto de VBA para una empresa con la que estoy trabajando.

Hoy vemos un simple truquito.

Cualquiera que haya creado un libro de trabajo sabe que es muy fácil crear una lista muy grande de rangos con nombre. Gestionar esos rangos, especialmente si heredas el libro de otra persona, es una tarea mucho más difícil. Parte del problema es que puede perder la pista de todos sus rangos y a qué se refieren.

:

Tener una lista de los nombres en su libro de trabajo podría ser útil. Para obtener una lista, siga estos pasos:

Cómo importar a Excel una tabla HTML desde Internet- VBA

Hola que tal?

El otro día un usuario me puso una consulta para saber si hay alguna manera de importar una tabla de Internet a Excel.

Y si que la hay. Bueno a veces basta con seleccionar los datos y copiar y pegar, aunque a veces el formato viene con bastantes problemas.

También hay veces que la opción de descargar a Excel existe con lo que es muy fácil.

Pero hay veces que ni lo uno ni lo otro.

Y entonces es cuando viene VBA a nuestro rescate!

Veamos cómo.

Cómo buscar (BUSCARV) registros similares (pero que no coinciden) en Excel?

Buenas a todos. Hace unos meses mirando por la web, en mis foros y demás, descubrí algo genial, una de esas cosas que ojalá hubiera sabido hace tiempo. 

En fin, como ya sabemos NUNCA es tarde.

Veamos el problema de hoy. Algo raro pero muy relevante! No es así? Un aviso rápido sobre cómo surge?

Imaginemos por un momento que la persona encargada de crear clientes nuevos en el sistema de gestión de información no es muy robusta, cada vez que llega un cliente (incluso si es el mismo cliente) se crea un nuevo registro.

Los caprichos de esta persona llevaron a demasiados nombres de empresas similares y ahora es imposible que BUSCARV coincida con nombres de compañías similares. 

Algún analista habrá tenido este problema muchas veces, verdad? Y cada uno lo habremos resuelto a nuestra manera, quitando espacios, extrayendo la primera palabra o la última, usando ENCONTRAR o HALLAR, concatenando, ordenando etc.

Siempre pensé que a lo mejor debería crear en una solución basada en Macro (codigo VBA) pero nunca lo hice. Unos meses más tarde, para mi sorpresa, descubrí que Microsoft ya lo había pensado y le había dado una hermosa solución. Hoy escribo sobre eso (mejor tarde que nunca 😛)

Sin reinventar la rueda, veamos qué tiene para ofrecer Microsoft. Listo para eso?

Cómo agregar leyendas directas como etiqueta de datos a un gráfico de Excel

Hoy vamos a ver cómo agregar una leyenda directamente a la etiqueta de datos en el gráfico. Mira la imagen

Hagámoslo con las estadísticas de los dos mejores jugadores de fútbol de la última década. El número de goles marcados con club y pais en los últimos 5 años.

Trucos para calcular el total acumulado en Excel

Hola

Hablemos de los totales acumulados. Veo por el mundo de internet muchas preguntas relacionadas con los totales acumulados y por eso he decidido realizar esta entrada y mostrarles unos trucos muy válidos para su creación.

Pues allá vamos!

Imaginemos que tenemos unos datos como éstos:

Estos datos están convertidos en tablas, para su mejor uso. Si necesitas una guía para entender las tablas, pincha en este enlace. (Guía sobre las Tablas en Excel)

Ahora hagámosnos 3 preguntas sobre totales acumulados:

1. Cómo obtendría un total acumulado de la cantidad? Incluso ordenando por cualquier columna, el total acumulado no debería romperse
2. El total acumulado ordenado por cliente debe restablecerse cuando se cambie el nombre del cliente.
3. Mantener el total de la pregunta 2,(solo para filas visibles) cuando se filtra la tabla

Pues veamos las soluciones!

Cómo calcular cuántas veces una palabra aparece en una frase en Excel

Hola buenos días a todos.

El otro día un lector me propuso el siguiente problema: cómo calcular el número de veces que una palabra aparece en una frase en Excel. Pensé que a lo mejor había muchos lectores que podrían en un momento dado encontrarse con un problema similar y decidir escribir el post.

Imaginemos que tenemos los siguientes datos.

Y lo que necesitamos es calcular el número de veces que la palabra en la columna B aparece en la frase de la columna C.

Esta situación se puede resolver con una fórmula no muy compleja.

Veamos cómo!

3 errores comunes al escribir la función BUSCARV en Excel

Hola

Has estado alguna vez en una situación en la que tu función BUSCARV no funciona y no sabes porqué?

Déjame rescatarte los 3 errores más comunes usando la función BUSCARV.

1. Tener espacios (fantasmas) en los datos o valores de búsqueda

La función Desref y sus aplicaciones en Excel - 2a parte

Hola buenos días, buenas tardes o buenas noches, según os pille.

La semana pasada escribimos sobre la increíble función DESREF (aquí dejo la entrada en caso de que no pudieras leerla http://elsabiodeexcel.blogspot.com/2017/08/la-funcion-desref-y-sus-aplicaciones-Excel-1.html) y el feedback que he recibido ha sido muy bueno. Muchas gracias! Se agradece trabajar así cuando veo que los lectores aprecian el esfuerzo hecho (también se puede agradecer de otra manera, mira el final de esta entrada). No olviden que requiere mucho esfuerzo sacar tiempo para investigar y escribir para que vosotros los lectores puedan aprender de una manera gratuita.

Bueno, pues hoy vemos cómo podemos aplicar esta incredible función DESREF.

Aplicaciones de DESREF

La función Desref y sus aplicaciones en Excel - 1a parte

Hola buenos días, buenas tardes o buenas noches, según os pille.

La semana pasada puse una entrada sobre cómo hacer que BUSCARV vaya más rápido,  y recibí algunos mensajes en privado, de cómo valoraban la entrada. Igual que yo! Ojalá, y lo digo sinceramente hubiera sabido ese truco en mis años de analista de datos en Londres. La de tiempo que habré perdido esperando a que el BUSCARV actualice...  Eso, es lo bueno de aprender BIEN las funciones y de los usos que te pueden dar.... 

Por eso hoy quiero hablar de otra función que te puede ayudar MUCHISIMO! Esa gran desconocida para muchos llamada función DESREF.


La función de DESREF es una de las más importantes en la familia de las funciones de búsqueda y nos trae nuevas posibilidades en Excel, pero se sugiere utilizarla con precaución debido a su naturaleza volátil. Veamos esta función y sus aplicaciones en profundidad!

Sólo para poder explicarlo mejor vamos a dividir la sintaxis DESREF en 2 partes (el básico y avanzado)

Cómo hacer que BUSCARV vaya más rápido cuantos teneos gran cantidad de datos en Excel

Hola lectores

Hoy les quiero mostrar un super truco, que seguro que a mucho analistas les va a venir muy bien.
Es más, ojalá hubiera sabido yo esto hace tiempo, ya que me hubiera ahorrado mucho pero mucho tiempo.

A veces cuanto tenemos muchos pero que muchos datos, digamos que 100.000, intentar ejecutar un BUSCARV puede llegar a tardar bastante tiempo, desde 10-20 segundos  a incluso minutos, dependiendo del procesador de tu ordenador.

Pues bien, veamos hoy 

Cómo hacer que BUSCARV vaya más rápido cuantos tenemos gran cantidad de datos en Excel

Para aquellos que no andan muy familiarizados con la función BUSCARV, aquí les dejo un pequeño tutorial la función BUSCARV (VLOOKUP en inglés)

Al aplicar BUSCARV en datos grandes (digamos 100 mil filas o más) a menudo se ven agobiados por hojas de cálculo extremadamente lentas o incluso a veces por esta dulce notificación.

Y ya sabemos como nos hace ésto sentir! NOOOOOO!!!  # $% Auytiuyoidqjsjfas AARRGGHHH !.

Una de las maneras de acelerar este proceso sería usar PowerPivot (que es genial!) pero si por algún motivo aún no han hecho la transición a PowerBI, si es eres como muchos, pues en ese caso vamos a explorar juntos este truco ..


La parte increíble es que es sumamente simple! Ya veréis!

Diferentes respuestas a la pregunta de la semana pasada

La semana pasada os puse un test, y he de decir, que estoy un poco desilusionado de la poquita cantidad de personas que tomó parte en él, pero supongo que así es la vida!

Espero que para el próximo haya más personas.

Aún así, recibí algunos comentarios sobre cómo hacerlo y eso es lo que me gustaría comentar hoy.

Para ver el test y descargar el archivo, se puede hacer aquí

Básicamente el test era el siguiente, teniendo una tabla de alumnos y asignaturas con sus correspondientes puntuaciones, la pregunta era: 

Utilizando una sola fórmula,  qué asignatura obtuvo la calificación más baja para cualquier estudiante?

La tabla de datos en cuestión era esta.

Bueno, pues veamos CÓMO resolverlo.

Conteo por color en Excel

*****************************************************************

*****************************************************************

Entrada escrita por nuestro colaborador Miguel Ibarra.

Si te gustaría colaborar mándame un email a:

elsabiodeexcel@gmail.com

*****************************************************************

*****************************************************************

Muchas gracias Miguel!

------------------------------------------------------------------------

Hola, nuestro tutorial de hoy es muy sencillo, pero trata de una acción bastante inusual en excel, contar colores. ¿Quién no ha utilizado el formato condicional para color? y ¿qué tal si además saber utilizarlo podemos contabilizarlo y obtener conclusiones de eso?.

Entrando en materia,hay dos casos en los que se podrían querer aplicar colores y por consiguiente, poder contarlos:

1. (En mi opinión más importante cuando tenemos formatos condicionales, la función dada cuenta las celdas que tienen el color dado por el primer argumento, dentro del rango dado por el segundo. Por ejemplo, en el caso de =CuentaColor(3;A1:A10) cuenta cuántas celdas rojas (teniendo en cuenta que esta función para poder contar los colores los codifica con números, el 3 es roja) hay en el rango A1:A10. La instrucción inicial Application.Volatile hace que la función recalcule su valor cada vez que se modifica la hoja. Esta función la escribimos en la hoja como cualquier otra función de Excel, A continuación les presento esta sencilla fórmula:

Para poder saber que número identifica cada color utilice la fórmula:

Crear una tabla de índice de hoja en Excel con VBA

Hoy vemos otro truco muy muy útil!

Todos estamos acostumbrados a trabajar con varias hojas en un solo libro, verdad? Pues estaría genial poder crear un índice para poder acceder a todas las hojas de una manera eficaz!

Pues hoy veremos una macro que nos ayudará a hacer exactamente eso!

Veremos una manera inteligente de crear un índice para todas las hojas, en el que se pueda hacer clic en el nombre de la hoja para ir directamente a esa hoja.

Así es como lo hacemos

La función FILA vs la función FILAS para la generación de números enteros consecutivos en Excel

Hola, veamos hoy una pequeña aclaración entre las funciones FILA y FILAS, y cómo se pueden aplicar.

A menudo, deseamos incorporar a nuestra fórmula una construcción que, como la fórmula se copia a sucesivas filas, generará una serie de números enteros consecutivos, usualmente comenzando con 1.

Un ejemplo clásico es con la configuración estándar INDICE + K.ESIMO.MENOR para devolver varios valores correspondientes a un cierto conjunto de criterios, por ejemplo:

{=INDICE($B$1:$B$11,K.ESIMO.MENOR(SI($A$1:$A$11="Luis",FILA($A$1:$A$11)),1))}

Esta fórmula obtendría la primera aparición de Luis en el rango A1:A11 y devolvería su valor correspondiente en la columa B.

Hay que tener en cuenta que es una fórmula matricial, asi que si aún no dominas mucho el tema, te recomiendo este enlace: Las fórmulas de matriz

El 1 del extremo de la fórmula resaltado en rojo aquí ({=INDICE($B$1:$B$11,K.ESIMO.MENOR(SI($A$1:$A$11="Luis",FILA($A$1:$A$11)),1))}

representa k para la función K.ESIMO.MENOR. Ahora si se quiese tener todos los valores correspondientes a Luis, se tendría que modificar manualmente este valor a 2, 3, etc. para las fórmulas en filas sucesivas. Pero por supuesto nos gustaría tener esto automatizado, y así copiarlo hacia abajo y no tener que modificar nada manualmente.

Cómo lo hacemos?

Una macro para consolidar datos provenientes de varias hojas

Hola.

Hoy quiero mostrarles una macro para resolver uno de estos problemas que suceden de vez en cuando.

Qué problema?

Uno de los problemas más comunes en la gestión de datos es agrupar todos los datos. Digamos que tenemos algunos datos dispersos en varias hojas que queremos reunir en una sola hoja. ¿Como lo harías?

1. Una forma es copiarlo desde múltiples hojas y pegarlo en una ubicación 
2. O también se puede escribir una simple macro para que haga lo mismo, ahorrando tiempo