La herramienta más poderosa de las hojas de cálculo: las tablas dinámicas (2020)

 (qz.com)
3 puntos por GN⁺ 2023-10-10 | 1 comentarios | Compartir por WhatsApp
  • Las tablas dinámicas son una función clave que convierte las hojas de cálculo en herramientas de análisis, al permitir resumir rápidamente grandes volúmenes de datos sin programar ni usar matemáticas avanzadas
  • Pito Salas, de Lotus, ideó una herramienta basada en clics al ver lo incómodo que era para los usuarios crear estadísticas resumidas por categoría y tablas cruzadas mediante fórmulas complejas
  • Steve Jobs vio el potencial de esta función mientras buscaba un software estrella que impulsara la demanda de las computadoras NeXT, lo que llevó al desarrollo de Lotus Improv
  • Las flexible views del equipo de Lotus se incorporaron más tarde a Lotus 1-2-3 y Excel, y en Excel se consolidó el nombre pivot table
  • Hoy, las tablas dinámicas se usan ampliamente en Excel y Google Sheets, y permiten condensar datos de cientos de miles de filas en un informe resumido de una página con solo unos clics

Una herramienta para resumir datos sin fórmulas

  • Las tablas dinámicas son una función de las hojas de cálculo que permite a los usuarios comunes analizar rápidamente conjuntos de datos grandes
  • Sin necesidad de saber programar ni tener conocimientos avanzados de matemáticas, los usuarios pueden resumir datos apuntando y haciendo clic con el mouse
  • El cofundador de Apple, Steve Jobs, también aparece como una de las personas que reconoció rápidamente el valor de esta función

La historia del desarrollo que llevó a NeXT y Lotus Improv

  • En 1985, después de no lograr vencer a IBM en el mercado de las computadoras de negocios, Jobs fue desplazado de la presidencia del directorio de Apple y enseguida fundó NeXT para volver a desafiar a IBM
  • Mientras preparaba la computadora NeXT, que se lanzaría en 1988, Jobs buscaba un software estrella que generara demanda para el producto
    • Jobs consideraba que VisiCalc, el primer software de hojas de cálculo de uso ampliamente extendido, fue clave en el gran éxito de la Apple II, lanzada en 1979
  • En sus encuentros con Lotus, creadora de la popular hoja de cálculo Lotus 1-2-3 para computadoras IBM, Jobs conoció una forma temprana de las tablas dinámicas
  • En ese momento, Pito Salas, de Lotus R&D, observaba cómo la gente usaba las hojas de cálculo para calcular estadísticas resumidas por categoría, es decir, tablas cruzadas
    • Una empresa de venta de bicicletas podría querer ver la cantidad de ventas por mes o los ingresos por país
    • En esa época había que escribir fórmulas complejas a mano, algo engorroso y propenso a errores
  • Salas concluyó que hacía falta un software que permitiera obtener estadísticas resumidas con clics en lugar de ingresar fórmulas
  • El equipo de Lotus llamó a esta herramienta flexible views, y las funciones similares de Microsoft Excel y Google Sheets hoy se conocen como tablas dinámicas

La reacción de Jobs y su llegada al producto

  • Cuando el equipo de Lotus le mostró a Jobs un prototipo inicial, según Salas, Jobs lo consideró “lo más genial”
  • Jobs convenció a Lotus de desarrollar el software de tablas dinámicas exclusivamente para las computadoras NeXT
  • El resultado fue Lotus Improv
    • Las computadoras NeXT fueron un fracaso comercial, pero Lotus Improv dejó una gran influencia
    • El componente flexible views de Improv se incorporó después a Lotus 1-2-3 y Excel
    • Excel fue, de hecho, el primer producto en usar el término pivot table
  • Bill Jelen, evangelizador de Excel y coautor de Pivot Table Data Crunching, considera a Salas el “padre de las tablas dinámicas”
  • Salas considera que su aporte a las tablas dinámicas es uno de los logros más satisfactorios de su vida, aunque lo ve como un resultado construido sobre la base del trabajo de muchas personas

Uso en Excel y Google Sheets

  • Actualmente, las tablas dinámicas son una de las funciones más importantes y de uso más frecuente en la caja de herramientas de los usuarios avanzados de hojas de cálculo
  • Jelen dice que las tablas dinámicas permiten crear un informe resumido de una página a partir de datos con cientos de miles de filas con solo 4 a 6 clics, y que son la forma más rápida de obtener respuestas a partir de grandes conjuntos de datos
  • Es difícil saber cuántos usuarios diarios exactos tiene Excel, pero los usuarios de Excel en todo el mundo se cuentan por cientos de millones
  • Las tablas dinámicas suelen ubicarse en los primeros lugares, o cerca de ellos, en las listas de las funciones más útiles de Excel
  • Los analistas de datos usan tablas dinámicas para diversos fines, como salud pública, crecimiento económico y análisis de efectividad publicitaria
  • Justine Shakespeare, gerente sénior de programas de la organización sin fines de lucro de derechos laborales Verité, usa tablas dinámicas para analizar datos de encuestas de entrevistas a trabajadores migrantes en cadenas de suministro globales

Ejemplos reales y recursos de aprendizaje

  • Un periodista de Quartz necesitaba un resumen del monto total de remesas del primer semestre de cada año a partir de datos mensuales de remesas desde Estados Unidos hacia México desde 1995
  • La cifra necesaria podía obtenerse insertando una tabla dinámica, seleccionando los datos requeridos y filtrando los meses que debían excluirse
  • Para aprender más sobre cómo usar tablas dinámicas, se puede ver el video de Cody Baldwin, el “Excel ninja”
  • Los usuarios de Google Sheets pueden consultar la introducción a las tablas dinámicas del experto en Sheets Ben Collins

Lecturas relacionadas

1 comentarios

 
GN⁺ 2023-10-10
Opiniones en Hacker News

  • Aprendí este fin de semana que, si quieres agrupar filas/celdas según el valor de una celda, puedes usar la función FILTER
    =FILTER(Stories!B2:D13,Stories!F2:F13=A2)
    El primer argumento, Stories!B2:D13, es el rango de celdas que contiene las historias, y el segundo argumento, Stories!F2:F13=A2, es la columna que compara cada celda con el valor de A2. Las filas que coinciden se copian en la ubicación donde está la fórmula =FILTER
    Lo estoy usando para organizar automáticamente una lista de historias por sprint, y sirve para cosas como la planificación de incrementos de programa
    Otra fórmula útil de Excel que aprendí hace poco es =IF(NOT(ISBLANK(A2)),HYPERLINK("https://jira-instance.atlassian.net/browse/…),"")
    Si A2 no está vacío, agrega ese valor al final de la URL y muestra como enlace el texto PROJECT- con el valor de A2 adjunto. Podría haber hecho algo más elegante con Emacs y org-mode, pero necesitaba compartirlo con los responsables de negocio, así que Excel era lo adecuado

    • Este tipo de ejemplos es realmente útil. La función QUERY de Google Sheets también me ayudó mucho, al igual que IMPORTRANGE
      Ayuda a documentar qué se hizo para obtener la cantidad de filas
    • Me pregunto qué es una “historia” en una hoja de cálculo

  • Las tablas dinámicas basadas en cubos SSAS quizá sean la mejor herramienta de análisis de autoservicio que he visto hasta ahora. Con “mejor” me refiero a cuánto la usan los usuarios reales
    La capacidad de hacer que los usuarios de negocio la usen de inmediato en un entorno donde ya están es enorme para la adopción real, especialmente en comparación con algo como Looker. Looker también es un desastre por otros motivos
    Además, 9 de cada 10 veces la gente quiere agregar transformaciones o cálculos ligeros a los datos, y no hay nada mejor que tenerlo ya dentro de Excel
    Lo lamentable es que prácticamente no hay soporte para OSX, y escribir MDX es realmente doloroso

    • Estoy de acuerdo, y yo hago esto con Power BI. Importo los datos a un reporte de Power BI, creo un modelo de datos con medidas calculadas en DAX y luego lo publico en el servicio en línea; entonces los usuarios pueden hacer clic en “Analyze in Excel” y descargar un libro de Excel con una tabla dinámica conectada a ese modelo de datos
      Se lo doy a los PM de los productos que llevo, y pueden responder muchas preguntas solo con tablas dinámicas, sin escribir SQL aparte
    • En una empresa durante mi primera pasantía usaban la combinación de cubos OLAP + tablas dinámicas, y fue realmente impactante
      Pienso en eso bastante seguido cuando se habla de buenas herramientas analíticas en el mundo de datos. Incluso herramientas de BI bien desarrolladas como metabase no manejan las dimensiones tan bien como una tabla dinámica
    • A mediados de los 2000, nuestra killer app era una aplicación de ejemplo de MSDN Magazine. Era una página web sencilla con Excel embebido, que permitía trabajar con cubos SSAS mediante tablas dinámicas
      Hicimos algo de trabajo web para manejar permisos y guardar vistas, pero el tiempo total de desarrollo probablemente fue de menos de una semana
    • Me pregunto si has probado Easy Data Transform. Es una herramienta ETL ligera que hace transformaciones de datos tipo tabla dinámica para Excel, CSV y varios formatos de archivo, y corre de forma nativa en Windows y Mac
    • Recuerdo que Looker también permitía crear tablas dinámicas en la interfaz Explore
      También se podía descargar desde Looker Explore como CSV o Excel; me pregunto qué les faltaba ahí

  • Las tablas dinámicas son, en realidad, más bien una aproximación pobre a una verdadera hoja de cálculo multidimensional; por ejemplo, estaba Lotus Improv: https://instadeq.com/blog/posts/no-code-history-lotus-improv...

    • Uno de los “crímenes” de Microsoft Office, por exagerarlo un poco, es que dejó muy poco espacio para respirar, en el ámbito de la productividad de oficina que no es para artistas gráficos, a cualquier cosa que no fuera Office o una imitación casi directa
      El modelo de hoja de cálculo es un buen ejemplo. Excel quizá sea probablemente lo mejor de Microsoft Office, pero el resultado es que, si no puedes hacer publicación de escritorio lo suficientemente bien con Word, normalmente tienes que irte a InDesign, y si no eres profesional de la publicación, eso suele ser una opción excesiva
    • No sabía que existía Improv. Hay herramientas con una visión parecida a la de Improv
      En mi trabajo uso Anaplan todos los días, y si hubiera una versión SaaS moderna basada en la nube de Improv, se sentiría exactamente así. Creo que la razón por la que se convirtió en una empresa de miles de millones de dólares es que no apuntó de frente al mercado de las hojas de cálculo, sino que se acercó de una manera que convive bien con ellas
      La conclusión del artículo sobre Improv es que el error estratégico central fue intentar vender Improv al mercado existente de hojas de cálculo. Si lo hubieran vendido a un segmento donde el modelo más estructurado fuera una “funcionalidad” y no un “bug”, Lotus habría ganado tiempo para aprender, mejorar y refinar el modelo, y así satisfacer también a un mercado más grande; Anaplan parece no haber cometido ese error. Creó un nicho en el mercado de EPM, es decir, gestión del desempeño empresarial
    • En sí mismo también es más bien un groupby para pobres
      En algún momento hay que admitir que se eligió mal la abstracción. Tiene una fuerte vibra a Zalgo
    • No he usado hojas de cálculo con frecuencia desde mediados de los 90. Una de las razones fue que me ilusioné con el potencial de Improv, pero me decepcioné al darme cuenta de que no tenía futuro
      En ese momento no sabía que las tablas dinámicas eran otro enfoque del mismo concepto
      Otra razón por la que abandoné las hojas de cálculo fue el rendimiento. No recuerdo qué tan bueno o malo era Improv en ese aspecto, pero los conjuntos de datos con los que trabajaba entonces no encajaban bien en una hoja de cálculo, así que probablemente no lo habría seguido usando
    • Recuerdo haber ido al evento de lanzamiento de Lotus Improv en el Reino Unido cuando era periodista novato. En comparación con otros productos, destacaba bastante por lo vistoso y profesional que era, y en retrospectiva supongo que era por la influencia de Jobs
      Aun así, parece que les costó bastante explicar qué era una tabla dinámica y si esa sola función era motivo suficiente para mudarse a una nueva aplicación y una nueva plataforma de hardware

  • En Google Ads creamos un montón de dashboards que se veían increíbles, pero al hacer investigación con usuarios quedaba bastante claro que, al final, lo único que la gente quería era ver sus propios datos en tablas dinámicas

    • Los dashboards vistosos son demasiado inflexibles. No puedes agregar por tu cuenta agregaciones que vayan más allá de lo trivial, ni colorear un valor específico que te preocupa, ni armar de inmediato un informe legible con texto explicativo.
      Exportar a una hoja de cálculo + tablas dinámicas permite hacer todo eso. Cualquier empleado de oficina más o menos competente puede hacerlo sin pasar por un ciclo interminable de backlog-especificación-sprint-desarrollo-pruebas-reespecificación-sprint
    • En el fondo, los usuarios estaban intentando decirnos que ese dashboard no alcanzaba para responder las preguntas de negocio que su jefe les hacía todos los días, pero suena a que no captamos esa señal.
      Si no sirve para hacer el trabajo, a nadie le importa que se vea bien. Además, lo “vistoso” es muy subjetivo. El equipo estaba perdiendo el tiempo en algo donde creía que agregaba valor, pero en realidad no
    • Trabajaba haciendo dashboards que se veían bien, y un día los usuarios pidieron que agregáramos “columnas donde se pudieran poner funciones como SUM”.
      Poco después renuncié y me pasé al equipo de Google Sheets

  • Microsoft le robó directamente las tablas dinámicas a mi empleador anterior, Brio Technology, y a su producto DataPivot: https://en.wikipedia.org/wiki/Brio_Technology
    Según el CEO, Microsoft lo llamó a Redmond para hacerle una oferta insultantemente baja por la empresa y lo amenazó con crear su propio producto y hundir a Brio si se negaba. Él se negó y, después, MS agregó tablas dinámicas a Excel.
    El artículo también menciona a Lotus, que parece haber creado un producto similar por la misma época. Los fundadores de Brio también estuvieron involucrados en una empresa llamada Metaphor, y quizá algunas ideas se desarrollaron ahí.
    El producto de Brio venía con una base de datos de ejemplo que contenía el inventario de la cava de vinos del CEO, y esos mismos datos aparecieron en las primeras cajas de Microsoft Office.

    • A Stac Electronics le pasó algo parecido: https://en.wikipedia.org/wiki/Stac_Electronics
      Solo que Stac sí tenía una patente y contrató abogados para demandar a Microsoft por infracción de patente. Microsoft tampoco se echó atrás sin más, pero después de que el tribunal determinó que ambas partes necesitaban licencias para lanzar sus productos, Stac fue con grandes clientes OEM a ofrecerles una licencia.
      Era obtener una licencia de patente o no poder despachar el producto; cuando los OEM presionaron a Microsoft, eso terminó en un acuerdo nada malo para Stac. Lo veo como uno de los mejores ejemplos de cómo las patentes de software pueden ayudar a la competencia
    • Me da curiosidad saber exactamente qué parte robó MS. El concepto de tabla dinámica como resumen de datos por categorías de filas existe desde hace muchísimo, así que no creo que se refiera a eso
    • Me pregunto qué es lo que dicen que robó. ¿La tecnología subyacente o el nombre pivot table?
      El concepto de agregar columnas de datos categorizados no fue inventado por Brio Technology
    • También me recuerda a cuando Microsoft intentó licenciar CPM a IBM, pero como no lo poseía, hizo que alguien creara un clon y luego se lo licenció a IBM
    • Lotus Improv en realidad salió antes que el producto de Brio. Recuerdo claramente cuando apareció Improv; en ese momento fue un avance realmente interesante.
      Pero creo que el gran problema fue que no formaba parte de Lotus 123. Por eso, cuando Excel incorporó las tablas dinámicas directamente dentro de la hoja de cálculo, se volvió mucho más útil. Al menos para mí fue así

  • Soy ingeniero y también estoy familiarizado con SQL, pero la capacidad de trabajar rápido en Excel y crear tablas dinámicas sobre la marcha me ayudó mucho en mi carrera, sobre todo a medida que me fui moviendo más hacia producto y gestión de negocio.
    Las tablas dinámicas permiten ver muy rápido desgloses por múltiples dimensiones, cambiar el análisis de un lado a otro y tomar decisiones con rapidez. Fuera de herramientas existentes para dominios bien definidos, no hay muchas cosas que se les comparen

    • A mí me pasa lo contrario. Subo los datos rápidamente a BigQuery y empiezo a trocearlos de inmediato.
      Puedo lanzar consultas SQL mucho más rápido que hacer clics en Excel, y como después es más fácil copiar y pegar una consulta desde el historial que volver a aplicar todo el formato en una hoja de Excel, también es más reproducible
    • Nunca me metí demasiado a fondo con las tablas dinámicas. Durante toda mi carrera, los datos por lo general estuvieron en bases de datos relacionales.
      Pero muchas veces mover los datos de una hoja de cálculo a una base de datos es demasiado trabajo, así que incluso para un ingeniero usar tablas dinámicas no me parece una mala opción

  • Uso esto todo el tiempo en Pandas. Justo acabo de dar una clase relacionada y, una vez que entiendes la sintaxis, es muy útil.
    Herramientas inteligentes como DuckDB reconocieron su utilidad y el dolor que implica hacer esto en SQL común, y agregaron PIVOT a su implementación. Es muy útil

    • No me encanta trabajar con R, pero las funciones de pivoteo/reformateo de tidyr son difíciles de superar. Son mucho más cómodas que las funciones similares de Python
    • Me gusta la implementación de dplyr en R. Tiene pivot_longer y pivot_wider; ojalá Postgres tuviera algo así
    • Recuerdo que antes había un plugin para consultar tablas dinámicas en Postgres, pero era tan doloroso de usar que resultaba más fácil, y quizá hasta más eficiente, hacerlo en dos pasos con código de conexión Python/SQLAlchemy: primero consultar los nombres de las columnas y luego generar una segunda consulta a partir de esos nombres.
      Espero que la situación haya mejorado, porque las tablas dinámicas son realmente útiles
    • Me gustaba la herramienta visual de consultas de Microsoft Access. Era muy intuitiva, aunque quizá un poco demasiado abstracta para usuarios comunes.
      También generaba SQL, y gracias a eso aprendí un poco de SQL
    • Oracle también tiene PIVOT. La última vez que lo usé, había que especificar las columnas manualmente

  • Así que me da curiosidad qué es exactamente una tabla dinámica. Nunca he usado ni visto una, y el video incrustado tampoco cargó en el navegador
    Lo que aprendí de este artículo es que es una forma sencilla de mostrar datos dentro de una hoja de cálculo, y que quizá fue inventada en Lotus
    No tengo ni idea de qué hace realmente ni cómo se usa

    • Es una interfaz de arrastrar y soltar para agrupar los elementos de una tabla por categorías. A los usuarios de negocio normalmente les cuesta entenderla, por eso hay muchos videos cortos en YouTube. Hay un ejemplo aquí: https://www.youtube.com/watch?v=qu-AK0Hv0b4
      Personalmente, paradójicamente, la considero muy útil y al mismo tiempo un desastre total

  • Probablemente esto hable más de implementaciones específicas que de las tablas dinámicas en general, pero cuando intento hacer algo un poco más avanzado, a menudo siento que se quedan cortas
    Por mencionar algunas cosas, tanto Excel como Google Sheets son muy limitados, de maneras distintas, en cómo se generan los “valores”
    La forma en que funcionan el filtrado y el ordenamiento en las tablas dinámicas tampoco es la más intuitiva ni flexible
    Es muy difícil controlar elementos de la UI como encabezados y estilos, y muchas veces termino copiando la tabla dinámica a otro lado después de crearla para arreglar manualmente varias cosas. Entonces ya no puede actualizarse dinámicamente, y eso diluye su propósito
    No soy experto en hojas de cálculo, así que quizá se me esté escapando algo

    • Yo estoy bastante cerca de ser experto en hojas de cálculo, y clasifico las tablas dinámicas como una visualización de datos temporal, rápida y sucia. No las uso para presentaciones ni en lugares donde el estilo importe
      Las uso principalmente para encontrar rápido valores inesperados en las columnas de datos originales. Aunque con la aparición de fórmulas con desbordamiento como =unique(), cada vez las uso menos incluso para eso
      Creo que cuando la gente quiere más de las tablas dinámicas, por lo general lo que realmente está pidiendo se parece más a Power Query. La funcionalidad ya existe, pero a mucha gente le intimida. Puede que no sea el caso en HN, pero sí entre los usuarios comunes
    • Estoy de acuerdo. Las implementaciones actuales son totalmente frágiles y están lejos de la integridad funcional que esperaría un usuario avanzado o con experiencia
      Esto me molestó tanto que decidí escribir mi propio motor de hojas de cálculo, y este es uno de los puntos de dolor que quiero corregir
    • Puedes probar una tabla dinámica en línea como esta: https://www.seektable.com
      Puedes cambiar el orden de filas/columnas con solo hacer clic en los encabezados, y aplicar filtros indicando con entradas simples qué elementos mantener o excluir

  • Las tablas dinámicas sin duda son potentes, pero el nombre no ayuda, y en Excel siempre toma algo de tiempo manejar bien la UI
    Me gusta que la función Categories de Numbers en MacOS sea mucho más fácil de usar, pero cuando llegas a sus límites tiene restricciones bastante molestas
    Hoy en día, para trabajos complejos, muchas veces lo más fácil es exportar la hoja de cálculo a CSV y ejecutar consultas SQL con CSVQ