No arrastre fórmulas en Google Sheets! - Parte 2 - ArrayFormula avanzada
HTML-код
- Опубликовано: 12 сен 2024
- En este video vemos como aprovechar al máximo la maravillosa función ArrayFormula.
👍 Si te ha gustado este video, te encantará el curso gratis "Aprende Google Sheets en 2 horas". Aquí puedes obtener acceso juansguzman.co...
📋 Puedes encontrar el código y la plantilla de este video (y los más de 350 videos del canal) inscribiéndote en un plan mensual o anual en mi membresía JuanSGuzman - juansguzman.com
🎓Allí mismo, encontrarás un curso completo de Funciones Matriciales (donde hay toda una sección dedicada a ArrayFormula asi como más de 75 cursos completos de Sheets, Forms y Apps Script. Aquí puedes verlo juansguzman.co...
📝 Puedes encontrar este tutorial en forma escrita aquí
juansguzman.co...
☎️ Si necesitas asesoría personalizada, ofrezco algunas horas a la semana de este servicio. juansguzman.co...
🔔 Suscríbete al canal donde recibirás nuevos videos semanales de Sheets, Forms, Apps Script y más.
Aquí algunos canales donde me puedes seguir y apoyar:
📨 - Newsletter: juansguzman.co...
➤Patreon: juansguzman.co...
➤Telegram: juansguzman.co...
➤Instagram: juansguzman.co...
ⓕ - Facebook: juansguzman.co...
➤Tiktok: juansguzman.co...
𝕏 - (Twitter): juansguzman.co...
Cualquier sugerencia, pregunta o inquietud, te invito a comentar más abajo
Videos Relacionados
Introducción a ArrayFormula - • ArrayFormula - No tene...
Muchas gracias, hay muy poca información de Sheets en linea. Tus explicaciones son muy claras y precisas. Bendiciones
Mil gracias David!
instaBlaster...
La verdad es que el ArrayFormula me ha funcionado genial. Logré "esconderlo" en el encabezado tal y como sugieres, para que esté 100% funcional desde la primera a la última fila. Y además, para "proteger" la columna de errores involuntarios que podrían dañar la fórmula, le hice una validación de datos condicional y le di a rechazar entrada. Así que funciona perfecto y sin posibilidad de que se dañe! (Sin tus vídeos ni se me hubiera ocurrido, porque no tenía ni idea, así que de nuevo muchas gracias!!)
Muy buena idea lo de la validacion de datos!
Felicitaciones!
Muchas gracias como siempre Juan. Tu trabajo es comprometido y yo le pondría imperdible. Marcas muchas diferencias. Te vuelvo a felicitar.
Hola!
Que amables palabras! Mil gracias!
Saludos!
Excelente, me ayuda demasiado el proceso del ArrayFormula desde el Emcabezado.
Excelente!
Como siempre Excelente, Gracias!
Gracias a ti!
Juan eres un crack!!!!, mil gracias, me funcionó perfecto, tengo una pregunta, funcionará con sumar si conjunto, contar si conjunto, max si conjunto????? , tienes algún video?
Hola Luis
Me falta ese video!
LA verdad es que no funciona con sumar.si.conjunto, pero si con SUMAR.SI
Hay que ponernos creativos, pongo el video en la lista
Un gran saludo!
Gracias por la info
Gracias por comentar
Parte 1: ruclips.net/video/04mgqvCAffk/видео.html
Parte 3: ruclips.net/video/A0sdycQsx98/видео.html
Mil gracias!
5:41 Otro ejemplo de ArrayFormula desde el ENCABEZADO: ruclips.net/video/lzlmoOzXRR4/видео.html
Mil gracias!
Estimado Juan, muy agradecido con la información que facilitas es de gran ayuda para muchos de nosotros, deseo saber si hay alguna forma de ayudarme con el siguiente problema, uso la siguiente fórmula para obtener la última celda de una serie de FILAS con información, el problema es que no he logrado agregar hacer que funcione el Arrayformula con ella, quedó agradecido con tu ayuda.
=DESREF(P2,0,COUNTA(P2:FT2)-1)
La verdad es que tengo que estudiar mas y practicar mas con desref y arrayformula porque creo que hay muchas posibilidades pero esta dificil. Me lo dejo de tarea. Saludos!
Hola Juan excelente video, muy buena la info que suma el ARRAYFORMULA! Hoy vengo con una pregunta difícil, desde el video anterior de ARRAYFORMULA vengo reemplazando toneladas de formulas que tengo en algunas planillas por una sola y en general todo super OK, pero me empezó a pasar en una puntual que se me volvió muy lenta, es una hoja de resumen que tiene 300 filas y suma en una columna varios sumar.si que traen info de 11 solapas diferentes. La pregunta, sabes como afecta ARRAYFORMULA al rendimiento de la hoja? O mejor, hay algun documento donde se hable de como optimizar las formulas para que las planillas funcionen mas rápido? Hace muchos años atras cuando empece a usar la primera version de sheets (que estaba limitada por todos lados) no me acuerdo donde te decia que no podias usar muchos BUSCARV entre otras cosas. Desde ya te agradezco mucho. Abrazo
ESTIMADO JUAN BUEN VIDEO, me doy cuenta que no se puede proteger columnas sin que los demas pierdan su filtro permanente pues solo les habilita el temporal. sabes alguna manera de corregir esto?
Hola Juan, como sería la formula si quisieras ir sumando en una columna la diferencia mensual acumulada? Quizas en este ejemplo no tiene tanta utilidad, pero si sería similar la solucion para ir llevando un saldo de caja por ejemplo... Muchas gracias por tus videos, excelentes 👌👍
Hola Jua, me puedes decir si es posible husar Arrayformula para hacer sumas con valores que son arrojados por otra formula usando Arrayformula. Por ejemplo: el numero total de vacaciones es 25, si alguien pide tomar dos dias de vacaciones, me gustaria que usando Arrayformula pueda deducir los dos dias de vacaciones y demostrar un total de 23 dias restantes.
Hola!
Se deberia poder
Si te esta marcando error, dejame ver tus formulas
Saludos!
Hola Juan! Existe alguna posibilidad de ocupar arrayformula con un contar.si o un contara ??
Hola!
Se que el CONTAR.SI si funciona. SUMAR.SI.CONJUNTO no funciona, SUMA tampoco. Yo creeria que CONTARA tampoco funciona
Saludos!
@@JuanSGuzman muy interesante e instructivo todos los aportes que he visto en su canal.
Por añadir, CONTAR.SI.CONJUNTO tampoco funciona, en este sentido ¿se le ocurre alguna forma de hacer un arreglo usando el CONTAR.SI ?
Muchas gracias por todo lo que nos ayuda, apenas hay lugares en la red donde profundizar en google sheets, su aportación es de gran ayuda.
Saludos!!
@@ricardoade411 Hola Ricardo
Mil gracias por tus palabras!
He estado pensándolo, pero no se me ocurre porque nos tocaría usar indice o FILTER, otras funciones que tampoco funcionan con ArrayFormula
Tengo pendiente algún video donde profundice en el tema de compatibilidad de ArrayFormula con otras funciones
Un gran saludo!
@@JuanSGuzman muchas gracias por tu respuesta. Ya estoy suscrito a tu canal y con la campanita activada 🔔 pendiente de tus publicaciones.
@@ricardoade411 Hola Ricardo
Mil gracias por tus palabras!
Y mil gracias por suscribirte!
Un gran saludo!
Una duda Juan. ¿Tienes algún ejemplo de conexión con bases de datos externas? Voy a intentar conectar una hoja sheets con SQL. Creo que con App scipts se podría hacer. Con esta integración tendría una herramienta poderosa, podría extraer y actualizar automáticamente los datos de nuestros sistemas para el análisis, la generación de informes y mucho más. Por ejemplo, todos los días envío un whatsapp a los clientes que salen de viaje. Es un mensaje muy personalizado. Al regresar del viaje le envío una encuesta. Ahora obtengo un listado que convierto en excel y vuelco en sheets. Sería más simple hacer la consulta a SQL y tener los datos directamente en sheets
Hola!
No tengo el ejemplo exacto, pero estoy trabajandolo para subir videos sobre el tema pronto
Mil gracias por la sugerencia
Saludos!
Hola Juan, muy buenos tus aportes, estoy muy agradecido con tus videos. tengo una duda, arrayformula es compatible con esta formula? =INDICE(filter(BU:BU;$BQ:$BQ=U:U);CONTARA(filter(BU:BU;BT:BT=U:U))), esta formula la vi en un video tuyo pero no me permite usar el arrayformula, quisiera saber que estoy haciendo mal, mil gracias
Hola
HAy algunas formulas que no van bien con arrayformula. INDICE y CONTAR son 2 de ellas
Tengo pendiente un video de estas incompatibilidades
Un gran saludo!
@@JuanSGuzman pues me parece una idea genial. Ya tengo activada la campanita. ;)
¿Cuál es la opción más eficiente para el Arrayformula con sumar.si.conjunto en los títulos si esta primera no funciona?
Una duda ¿cómo se combina con concatenar?
Gracias por compartir tu conocimiento
Hola Eduardo
Usa &
Con CONCATENAR no funciona bien el ArrayFormula
Saludos!
@@JuanSGuzman he probado con & pero me genera un error al agregar espacios, estoy tratando de concatenar nombres y apellidos
@@eduardoromero5767 Hola!
Déjame ver como esta tu formula
Saludos!
@@JuanSGuzman gracias por el apoyo, ya pude hacer la concatenación usando &, ahora voy a aplicarla desde el encabezado
@@eduardoromero5767 Excelente!
Hola Juan, es genial tu soporte, tengo una consulta como hago un arrayformula, para enumerar datos duplicados
Interesante!
Y dificil!
Haré un video de eso algún día
Saludos!
Juan, tengo una duda y es cuando hago un SI(Y(XXX);xx;xx) y le aplico el arrayformula no me funciona por que utilizo la función Y() dentro de la prueba lógica, no se como puedo solucionarlo, gracias.
Hola Diego
Desafortunadamente hay varias funciones que no funcionan con arrayformula
Una de esas es Y
Espero hacer un video de eso pronto
Un gran saludo!
Hola, una pregunta como sería si en vez de ser dos filas seguidas son salteadas?
Gran Video¡¡¡
una pregunta, estoy buscando "concatenar" los datos (en si dias / mes / año) de 3 columnas, pero estas tienen diferentes valores para cada fila. (tengo al rededor de 2000 filas).
he intentado con esta formula, tratando de seleccionar un "rango" *no se si aquello es correcto*
=CONCATENAR(G7;"/";H7;"/";I7)
Con esta anterior, he logrado unir los datos en una sola celda. Pero tengo 2000 celdas..que hago? :c
He pobado con esta pero no resulta
=CONCATENAR(G7:G2000;"/";H7:H2000);"/";I7:I2000)
Podrias hacer un video de eso? :`)) es para un proyecto de predicción de casos covid.
Hola!
Buena idea de video!
Intenta usar & en vez de concatenar
Algo asi como
=ArrayFormula(G7:G2000&H7:H2000)&I7:I2000)
Me cuentas si te sirve!
@@JuanSGuzman Muchas Gracias Juan!!
De maravilla, gracias por la idea
le añadí algunos retoque que aprendí de tus videos y funcionó :`))
=ArrayFormula(G1:G&"/"&H1:H&"/"&I4:I)
Luego le aumenté el condicional Si, para que no se mueva cuando las ordene
=ArrayFormula(SI(I1:I="AÑO";"Fecha texto";G1:G&"/"&H1:H&"/"&I4:I))
@@EscobarPerezYuri Hola!
Excelente! Se ve muy bien!
Saludos!
Buenas tardes Juan, una consulta... intento usar el array formula con el desref.... Pero se me forma un bucle interminable...Mi formula original es esta =ArrayFormula((SI(F5:F="INGRESO",(I6:I+ H5:H),(I6:I- H5:H))))
Como puedo corregir esto para que funcione? Esta formula basicamente detecta si es un "INGRESO". Si lo es, suma a la casilla anterior , pero si no lo es, lo resta.... Gracias!
Hola tengo una duda, con esta fórmula =SI('Hoja 1'!C6=123;'Hoja 1'!D6;""), genero que me pase a mi hoja 2 lo que hay en la celda D6 de la hoja 1, hasta ahí perfecto, pero si quiero hacerlo en varias celdas no me deja, ejemplo: =SI('Hoja 1'!C:C=123;'Hoja 1'!D:D;""), entiendo que falta algo seguro, he probado con ARRAYFORMULA y no consigo nada, me podrías ayudar en como hacerlo con mas de una celda?, gracias!
Hola Juan! Sabes si hay alguna manera de eliminar filas completas de un sheets? Con una macro o formula? O que se elimine un valor de una celda y eso haga que se elimine toda la fila con ese valor?
Hola!
Si, claro que si, las filas se eliminan con el método hoja.deleteRow(numFila)
Si quieres hacemos un video mas adelante
Saludos!
Hay posibilidad de utilizar ArrayFormula con INDEX y MATCH? Si tienes un ejemplo
Hola!
Desafortunadamente no
El COINCIDIR si funciona, pero el INDICE al tener la posibilidad de ser una función arreglo, no funciona.
BUSCARV por su lado, si funciona.
Podrías usar BUSCARV con COINCIDIR y hasta buscar columnas a la izquierda usando arreglos
Un gran saludo!
Hola
Gracias por ayudarnos!
Quisiera saber cómo hacer si no quiero que el 0 aparece en las filas vacías?
Gracias por su ayuda
Hola!
Puedes usar un condicional que diga, por ejemplo
=ArrayFormula(SI(A1:A="";"";el resto del comando))
Me cuentas si te funciona
Saludos!
@@JuanSGuzman algo que me gusta usar para estos casos es un formato condicional para que si hay un cero en la hoja lo muestra del mismo color que la casilla (generalmente blanco) asi el 0 esta ahi pero no lo veo 😉
@@oscarlovarvo Muy válido Oscar. Mil gracias por el aporte
Saludos!
¿Se puede usar array formula combinada con concatenación?
Hola
Claro que si
Voy a preparar un video para ver que funciones van bien y cuales no con ArrayFormula
Un gran saludo!
@@JuanSGuzman ¡Muchas gracias por atender; saludos cordiales desde México!
@@filibertodurangarcia1975 Gracias a ti!
@@JuanSGuzman yujuuuuu
Buenas tardes Juan muchas gracias por todos los videos me han sido de mucha ayuda. El tema es que tengo que sumar varias filas para sacar subtotales en las columnas determino donde empieza y donde termina dejando en otra columna los valores ="SUMA(N"&D3&":N"&E3&")" esa funcion la quiero aplicar con arrayformula pero no se que estoy haciendo mal =ARRAYFORMULA(SI(INDIRECTO("A3:A" & $C$1) = "X" ; D3:D;"")) pero no logro hacerlo posible.
Esta parte no te va a funcionar en AF. Es mas, yo creo que INDIRECTO no te va a funcionar con AF
Saludos!
Hola Juan. Disculpa; estoy usando el ArrayFormula con la función Split pero me genera el ingreso indiscriminado de filas en la hoja de cálculo... ¿Cómo puedo hacer para que este error no ocurra? Gracias de Antemano por tu ayuda.
Hola!
Hay fórmulas que no te van a funcionar con ArrayFormula
No me acuerdo si SPLIT no, pero creo que esa no te va a funcionar
Saludos!
Hola buenas noches, gracias de antemano por los conocimientos tan valiosos que nos imparte, he avanzado mucho, sin embargo tengo un problema, quiero sumar tres celdas I2+AD2+AT2, este resultado debe estar en la celda I3 columnas(es un saldo diario), la formula que estoy haciendo es la siguiente =ArrayFormula(SI.ERROR(DESREF(I2:I-1,0,FILAS(A:A)-1))+(DESREF(AD2:AD-1,0,FILAS(A:A)-1))-(DESREF(AT2:AT-1,0,FILAS(A:A)-1))) y no me da ningún resultado, me sale que solo es un argumento y tengo 2, o luego me sale que hay un error en análisis de formula, por favor le pido me ayude. Gracias.
No me funciona :( hay forma de tener una consultoría contigo y pagarte honorarios ?
Hola Oscar
Claro que si
No me las quiero dar de muy ocupado, pero me demoro un poco en poderte ayudar, pero si me escribes, te pongo en fila
Un gran saludo!
como sacar promedio con arrayformula porfavor
Hola
Aquí te dejo un video donde lo hago
ruclips.net/video/sQud3mv3sbY/видео.html
Saludos!
buenisimo! te hago una consulta por si me podes ayudar. necesito algo asi pero que lo haga con lista de validacion de datos tambien. como lo hace excel al poner formato tabla. No estoy pudiendo hacer eso. Estoy por volverme al excel por ese tema. O como podria solucionar este tema? gracias!
Hola!
Te refieres a que cuando agregue nuevas filas, automáticamente cree las validaciones?
Saludos!
@@JuanSGuzman correcto. como en excel. que al aplicar el formato tabla. cada vez que agregamos una fila. se copia todos los formatos, calculos y validaciones de la fila anterior. se podria hacer esto en sheets? gracias por la pronta respuesta!!!!!
@@Quike-1988 Hola!
Desafortunadamente no se puede. Un "workaround" de esto es eliminar todas las filas, y cuando agregas nuevas filas te trae los formatos y validaciones de la última fila. Lo único que si no te trae son las fórmulas. Ahi tendrías que usar algo como ArrayFormula
Saludos!
COMO PUEDO HACER PARA QUE NO ME SALGA EL CERO?
Hola!
Puedes usar un condicional que diga, por ejemplo
=ArrayFormula(SI(A1:A="";"";el resto del comando))
Me cuentas si te funciona
Saludos!
Esto existe para excel?
Hola Ignacio
También existen los ArrayFormulas en Excel pero funcionan un poco diferente
Se usan más para hacer sumas de múltiples filas y columnas, u hacer múltiples operaciones.
El mejor ejemplo es SUMARPRODUCTO.
Se que en Sheets son un poco más fácil de implementar y si buscas en Google ArrayFormulas en Excel te puedes confundir un poco.
Un gran saludo!
Tengo un problema, la hoja de sheet recbibe informacion desde internet y se llena automaticamente desde la columna A hasta la F, dejando la columna G ( ESTADO) vacia, hasta ahi todo bien. Yo quisiera que cada vez que la hoja reciba info y se complete desde la A:F aparezca en la columna G la palabra PENDIENTE. Supuse que si utilizaba la formula SI , =SI(B2:B)>=0;"PENDIENTE";"PENDIENTE" podia funcionar . ( b2 son los numeros de ID de quienes llenan los datos) . Pero resulta que cuando termino de escribir toda la función y pongo lo de array formula y queda así =ArrayFormula(si(b2:b>=0;"pendiente";"pendiente")) la palabra PENDIENTE aparece en toooooooooodas las celdas de G, y eso es justamente lo que no quiero. No se me ocurre que otra formula puedo utilizar, estare haciendo algo mal ? Gracias
Hola Alfredo
Es que estas poniendo "PENDIENTE en las dos partes del condicional
Debes cambiar esto
=SI(B2:B)>=0;"PENDIENTE";"PENDIENTE" por esto
=SI(B2:B>=0;"PENDIENTE";"" )
Me cuentas si te sirve
Saludos!
Consulta: ¿Como podría repetir el texto HOLA en toda una columna usando ArrayFormula?....de modo que cuando añado nuevas filas este aparezca automáticamente.
Lo intenté con esto en la primera celda de la columna pero no me funciona: =ARRAYFORMULA({"HOLA"})
Saludos!
Hola!
Puedes inventarte un SI que siempre sea cierto y que involucre un rango, para que arrayformula sepa hasta donde ir
Algo como
=ArrayFormula(SI(A2:A=A2:A;"HOLA"))
Otra opción es con REPETIR
=TRANSPONER(split(REPETIR("HOLA,";CONTARA(A2:A));","))
Saludos!
@@JuanSGuzman funcionando perfecto. Muchas gracias.
Consulta adicional.....habrá una forma de meter este código dentro de una fórmula de modo que lo lea solo como texto. Las comillas que contiene me dan problemas.
Código :
SOLUCIONADO!
Se reemplazan las comillas por comillas simples dobles.
=ArrayFormula(SI(A2:A=A2:A;""))
@@AgendaBioBioCL Excelente!
Cómo puedo quitar el resultado de los ceros de los registros que están vacíos?
Hola Gabriela
Algo asi
=ArrayFormula(SI(A1:A="";"";Funcion))
Saludos!
Hola gracias por tus videos, al parecer con importrange no funciona 😢
Hola!
Gracias por las palabras
La verdad, mejor que no funciones, porque el importrange es una función muy pesada
Y ArrayFormula tambien
Si las tuvieras las dos, te explota Sheets, jaja
Saludos!
Pregunta: Cuando aplico arrayF resulta que todo lo que no tienes datos lo completa con "0" ... como podria evitar que me ponga esos Ceros porque yo ocupo usar la ultima file de mi proyecto y me manda al final
Hola!
Hay dos formas
Una es con un SI.ERROR envolviendo tu formula
Otra es con un SI(A1:A=0
Esto iria dentro del ArrayFormula
Saludos!
Porqué a mi no me funciona el autocompletado haciendo doble click en la esquina? Estoy utilizando Google Sheets
Hola! Es mejor la funcionalidad de Excel en este cntexto.
En Sheets, solo te funciona si los de abajo estan vacios, y tienes datos en la clumna de la derecha o de la izquierda. No es tan util la verdad
Te recomiendo mejor aprender a usar los comandos de teclado
Ctrl + Shift + Abajo
Ctrl + D
Etc
Saludos!
=ArrayFormula(K2:K-J2:J) no me sirve no se porue
Raro! Debería
Que te sale
Saludos!
Se puede un video de ArrayFomula y contar.si? 🥺
Claro que si, ya lo anoto
Saludos!
La llorona tambien entendio jaja XD vayan al min 15:28
jajaja...Tully
PARA HACERLO CON PROMEDIO?????
Hay varias funciones que no van bien con Array Formula, sumar si conjunto, promedio y más
Voy a hacer un video mas avanzado de esto porque ArrayFormula es espectacular pero desafortunadamente no es compatible con todas las funciones
Saludos!
BUENAS TARDES, SOLICITO TU AYUDA PARA SABER COMO PUEDO USAR LA FUNCION ARRAYFORMULA EN LA SIGUENTE FORMULA PARA NO TENER QUE COPIAR O ARRASTRAR PARA ABAJO.
DESREF(B3,0,CONTARA(B3:G3)-1,1,1)
docs.google.com/spreadsheets/d/16krRZ83PxOwgez4N6CdJ-dog3AaNTvohUChWwswhJxA/edit?usp=sharing
EL OBJETIVO ES OBTENER EL ULTIMO DATO QUE SE ENCUENTRE EN UN RANGO DE COLUMNAS DE IZQUIERDA A DERECHA, EN ESTE CASO ES DE LA COLUMNA B A LA G EN CADA FILA.
B C D E F G
1 a b c d R DESREF(B3,0,CONTARA(B3:G3)-1,1,1) A
2 1 2 3 G G
3 a b f f
SI ARRASTRO LA FOMULA ME DA EL RESULTA CORRECTO, PERO NO SE COMO AGREGAR LA FUNCION ARRAYFORMULA PARA NO TENER QUE ARRASTRAR LA FORMULA.
muchas gracias de nuevo, anteriormente con casi 5000 filas me funcionó perfecto, pero ahora con 312 mil y algo no me está funcionando, sencillamente no se muestran los resultados como esperaba y mi fórmula es muy sencilla: =ArrayFormula(countif(csv!$B$1:$B$396022;A2)). Espero puedas ayudarme
yo de nuevo, solo quería aportar que posiblemente hay un límite de filas para expandir la fórmula, al limitar a por ejemplo 9999 filas mi matriz si funcionó, igual creo que con esa cantidad de datos mejor usar un motor SQL
Hola!
Gracias por el aporte
En realidad el límite no lo dan las filas, sino las celdas. Por tanto te puedo ir bien con una tabla de 30.000 filas y una sola columna, o 5.000 con 6 columnas.
Tienes razón con más de 20 o 30 mil registros, lo mejor es pasarse a sql o bigquery
Saludos!