viernes, 9 de marzo de 2012

Número de página en una celda de Excel.

Hacía muchos años, desde que usaba la versión de Excel 2000, que no utilizaba las funciones GET., o INDICAR. en español, tanto que las tenía totalmente olvidadas. Me las hizo recordar la cuestión planteada por un lector sobre la forma de incluir en una celda de Excel la numeración de las páginas del Área de impresión configurada. Agradecer a officefull.es la explicación dada a este problema y que me ha permitido recordar viejos tiempos y herramientas.
La cuestión planteada decía:

...quisiera saber si hay alguna forma de obtener el número de página en una celda de excel...

Estas funciones GET. (o macrofunciones como en algunos sitios les llaman), son una reminiscencia, un trozito de historia viva de la versión Excel 4.0; son parte del lenguaje de programación específico de aquella versión, que aún hoy, siguen entre nosotros. Estas funciones son algo especiales, ya que no pueden ser utilizadas directamente en nuestras hojas de cálculo (por su particularidad de ser funciones de VBA), pero con las que si podremos interactuar a través de la definición de nombres.
Por concretar y responder al lector, se trata de asignar mediante funciones la numeración de las páginas configuradas dentro del área de impresión:

Número de página en una celda de Excel.


Como se puede observar, en cada página aparece en diferentes celdas, la numeración (según la ordenación indicada en la Configuración de página - abajo>derecha ó derecha>abajo) correspondiente.

Nuestro trabajo empieza por comentar cuales serán las funciones INDICAR. que usaremos en este ejercicio:
INDICAR.DOCUMENTO: que asignándole diferentes valores aportará diferente información sobre el documento; por ejemplo:
  • =INDICAR.DOCUMENTO(50;Hoja) nos indica el número total de páginas dentro del Área de impresión configurada de la Hoja definida como segundo argumento.
  • =INDICAR.DOCUMENTO(64;Hoja) nos indica los números de fila donde están los saltos de página horizontales.
  • =INDICAR.DOCUMENTO(65;Hoja) los números de columna donde están los saltos de página verticales.
  • =INDICAR.DOCUMENTO(61;Hoja) el sentido de la impresión de las páginas. Si es 1 el sentido es Abajo > Derecha, y si es 2 Derecha > Abajo.

INDICAR.CELDA: de manera similar aporta información sobre la celda indicada. Emplearemos sólo:
  • =INDICAR.CELDA(32;celda) que nos dirá cuál es el nombre de la hoja donde se encuentra dicha celda.

Con estos aspectos aclarados, procederemos a Asignar nombres a unas cuantas macrofunciones (o funciones de Excel 4.0):
Área_de_impresión =pagcelda!$A$1:$I$15
AreaImpr =LARGO(INDICAR.DOCUMENTO(81;Hoja))=0
FinalXdeY ="Pagina "&PagN&" de "&PagsN+0*AHORA()
Hoja =EXTRAE(INDICAR.CELDA(32;!$A$1);ENCONTRAR("]";INDICAR.CELDA(32;!$A$1))+1;31)
numCols =CONTAR(SaltosVert)
numFils =CONTAR(SaltosHor)
NumSaltosHor =SI(ESERROR(COINCIDIR(FILA();SaltosHor));1;1+COINCIDIR(FILA();SaltosHor))
NumSaltosVert =SI(ESERROR(COINCIDIR(COLUMNA();SaltosVert));1;1+COINCIDIR(COLUMNA();SaltosVert))
PagN =SI(SentidoImpr=1;(NumSaltosVert-1)*(numFils+AreaImpr)+NumSaltosHor;(NumSaltosHor-1)*(numCols+AreaImpr)+NumSaltosVert)
PagsN =INDICAR.DOCUMENTO(50;Hoja)
SaltosHor =INDICAR.DOCUMENTO(64;Hoja)
SaltosVert =INDICAR.DOCUMENTO(65;Hoja)
SentidoImpr =INDICAR.DOCUMENTO(61;Hoja)


La tarea es, sin duda, laboriosa, por el resultado merece la pena.
La función que resume todo el trabajo es la que hemos llamado FinalXdeY.

Explicar qué hace o qué obtenemos con cada una de estas funciones, en un orden lógico:
Área_de_impresión: Configura el área de impresión, así como el número de páginas incluídas en ella.
AreaImpr: determina si se ha definido una área de impresión personalizada.
Hoja: extraemos el nomnbre de la Hoja Activa.
SaltosHor: Números de fila donde están los saltos de página horizontales.
saltosVert: Números de columna donde están los saltos de página verticales.
numCols: cuantos saltos existen por columnas o en vertical.
numFils: cuantos saltos existen por filas o en horizontal.
NumSaltosHor:la posición en número, de la fila en que se encuentra la función, dentro de la cuadrícula de páginas del área de impresión.
NumSaltosVert:la posición en número, de la columna en que se encuentra la función, dentro de la cuadrícula de páginas del área de impresión.
SentidoImpr: devuelve 1 ó 2, según el criterio de ordenación de impresión dado.
PagsN: número total de páginas dentro del área de impresión.
PagN: obtenemos la ordenación correcta, según la ordenación configurada, de arriba a abajo, o de izquierda a derecha.
FinalXdeY: resumen final de todo lo calculado; obtenemos un texto con la numeración adecuada.

Con todo esto definido, sólo es necesario introducir en cada región/página del área de impresión la fórmula:
=FinalXdeY
que además, al formar parte de las macros del Libro, se desplegará como el resto de funciones personalizadas o estándar de Excel.

Número de página en una celda de Excel.


El resultado final es el que veíamos al comienzo de la entrada.

47 comentarios:

  1. Hola, muy interesante para imprimir con saltos horizontales y verticales. Sin embargo si quiero imprimir titulos en la hoja e indicar en una de las celdas de los titulos el número de página siempre se repite; es decir, siempre sale pagina 1 de mi total de páginas. como puedo hacerlo?

    ResponderEliminar
    Respuestas
    1. Hola,
      es lógico que te aparezca siempre (en este caso página 1), ya que realmente te aparece al principio de todas las páginas esa porción de celdas que corresponde a la hoja 1.
      Te recomendaría en este caso, por hacerlo sencillo, incluyas un encabezado de página numerado; así además de tener esas celdas de la hoja 1 repetidas en todas las demás, tendrás una numeración de la página impresa.
      Un saludo

      Eliminar
    2. sencillo .. en configuracion de pagina ..... pagina ..... primer numero de pagina.... esta por defaul ( automatica) osea te cojera desde 1 .... sencillo embes de automatica escribe el numero que quieras que comience la impresion y arrancara desde hay en tu pie de pagina .. pilas espero les sirva

      Eliminar
  2. Hola, me ha parecido interesantísima la creación de la función FinalXdeY y muy aplicable. En relación con esto me surge una duda: para no tener que definir todos los nombres en cada nuevo libro de escel, ¿de qué manera puedo guardar esta función para importarla a otros libros? Muchas gracias y muchos ánimos para que sigas con el excelente trabajo que estas haciendo en este foro. Saludos.

    ResponderEliminar
    Respuestas
    1. Hola,
      muchas gracias por tus palabras.
      Respecto a la cuestión que planteas, hasta donde yo sé, no es posible.
      Existiría una posibilidad grabándolo como una plantilla de Excel, pero estas funciones incluidas como Nombres definidos (Macros 4.0) no lo permiten...
      Slds

      Eliminar
  3. Hola de nuevo y muchas gracias por tu respuesta. Siguiendo con mi pregunta, ¿tampoco existe la posibilidad de crear una función parecida a FinalXdeY (o la misma) a través de vba? Muchas gracias de nuevo y saludos.

    ResponderEliminar
    Respuestas
    1. Hola,
      claro, en VBA grabándolo en el libro de macros personal, estaría visible en todos nuestros libros.
      Lo que pasa es que sería más sencillo emplear los objetos HPageBreaks y VPageBreaks para intentar identificar esos Saltos de página.
      Intentaré subir un ejemplo al blog explicando cómo.
      Slds

      Eliminar
  4. Amigo, genial la idea. Es perfecta, salvo por una cosa. Es muy útil, al menos para mí, aunque No tengo ni idea de aplicarla a una hoja que yo tengo. Siempre me aparece página 4 de 1, ponga lo que ponga. Me puedes echar una mano, por favor?.
    Gracias.

    ResponderEliminar
    Respuestas
    1. Hola Amartinomonis,
      es fácil olvidar uno de los nombres definidos, revisa que no has olvidado ninguno.
      Si sigue fallándote, envíame el fichero a:
      excelforo@gmail.com
      y lo reviso.
      Slds

      Eliminar
  5. hola que tal llevo toda la tarde tratando de meter la funciones y no puedo hacer que me salga :( me podrian explicar paso por paso como meterla sy configurarlas me urge para crear un documetno con bastantes hojas donde tengo q poner en cada una hoja # de # ... muchisimas gracias espero su respuesta y su ayuda

    ResponderEliminar
    Respuestas
    1. Hola Ali,
      la cosa es sencilla pero pesada. Sólo hay que repetir una y otra vez el mismo proceso para cada función.
      Sólo accede al administrador de Nombres ( en le ficha Fórmulas > Nombres definidos) y presiona el botón Nuevo. En esa ventana vas dándo los nombres y pegando las fórmulas explicadas en el cuadro de 'referencia a'.
      Dispones de varios ejemplos de cómo trabajar con Nombres definidos en
      http://excelforo.blogspot.com.es/search/label/Asignar%20nombres%20a%20rangos
      Espero te resulte sencillo.
      Un cordial saludo

      Eliminar
    2. Gracias por la respuesta ya entendi como meter los valores, ya lo hice, pero al intentar aplicar la formula en una celda para ver si funciona me da error y no me muestra resultado alguno,no se que este haciedno mal.. esocjo funcion =fnalxdey y como resultado me arroja lo siguiente: ="Pagina "&PagN&" de "&PagsN+0*AHORA()

      Eliminar
    3. correccion no meti los datos bien puesto que se qedo un espacio antes del sigo = por eso me regresa el valor en texto, pero ahora al tratar de meter la formula sin ese espacio me sale el anuncio de advertencia de error en la formula y no me deja ponerlo

      Eliminar
    4. Hola Alí,
      envíame el fichero a
      excelforo@gmail.com
      y le echo un vistazo.
      Slds

      Eliminar
    5. LISTOO!!! despues de tanto buscar cual era el error por fin pude lograrlo, lo que pasa que en tus formulas que me das tiene ";" , solamente tenia q cambiar ese signo por una "," en todas las formulas donde estuviera y listo asunto arreglado

      Eliminar
    6. Me alegro quede solucionado,
      es cierto que en ocasiones, según la configuración del equipo, las fórmulas/funciones se completan con , en vez de ;
      Pero siempre siguiendo el mismo criterio, si normalmente las completas con , siempre será con ,

      Slds

      Eliminar
  6. una consulta tengo una hoja de excel pero solo a una celda especifica quisero que salga solo salga el numero de pagina como puedo hacerlo

    ResponderEliminar
    Respuestas
    1. Hola, muy buenas... espero estés bien.
      Echa un vistazo a esta entrada
      http://excelforo.blogspot.com.es/2012/12/vba-una-funcion-personalizada-para.html
      En principio está definida cuando existen varias páginas, y tiene sentido numerarlas... ya que si sólo existe una página a imprimir, lo más sencilla es escribirlo manualmente, en lugar de complicarnos la vida con funciones VBA o como lo explicado en esta entrada.
      Saludos cordiales

      Eliminar
  7. hola buenos días, quisiera saber por que al introducir la formula me dice función no valida?

    Saludos

    ResponderEliminar
    Respuestas
    1. Hola!
      todas estas funciones INDICAR.CELDA o INDICAR.DOCUMENTO son funciones-macro, por lo que, siguiendo las indicaciones del post, debes añadirlas dentro de un Nombre Definido para que funcionen...
      ya que de lo contrario (escribirlas directamente en la hoja de cálculo) te aparecerá el error que comentas...puesto que no son funciones estándar de Excel.

      Saludos cordiales

      Eliminar
  8. Hola buen día:
    tengo un formato en el cual el encabezamiento se repite en todas las hojas, y en este encabezamiento inserte "Pagina", pero independientemente de la hoja en la que me encuentre, siempre muestra la página 1 de n. Ya revise los nombres asignado y todos están bien y completos. Me puede ayudar??

    ResponderEliminar
    Respuestas
    1. Hola,
      si has seguido la explicación tal cual, no sé a qué corresponde la fórmula 'Pagina'...
      para conseguir la numeración debes insertar
      =FinalXdeY

      Otra posibilidad es que realmente tu hoja tenga n páginas dispuestas a impresión.. si es el caso, redimensiona el área de impresión, para ajustarlo al tamaño de tu informe.

      Un saludo

      Eliminar
  9. hola amigo, buenos días.
    disculpa pero no se si podrías ayudarme, crees que sea posible importar datos de un archivo cerrado de excel a otro abierto aplicado criterios.
    si fuese así podrías explicarme como.

    muchas gracias de antemano.

    Andres Bambarén Alcalá

    ResponderEliminar
    Respuestas
    1. Hola Andrés,
      si es posible, pero lógicamente, la primera vez que realices la conexión para dicha importación SÍ deberás tener abierto los dos ficheros.
      Aquí puedes ver cómo:
      <a href="http://excelforo.blogspot.com.es/2009/11/importacion-de-datos-excel-2007.html>http://excelforo.blogspot.com.es/2009/11/importacion-de-datos-excel-2007.html</a>

      Espero te sirva
      Slds

      Eliminar
    2. Gracias Ismael, revise lo que me indicaste y funciona ok, disculpa pero mi pregunta es si puedo aplicar criterios para importar solo datos que con ciertas características.
      o quizá existe algún código en VBA para hacer esto con criterios

      Gracias nuevamente por tu atención.

      Eliminar
    3. Hola,
      bueno.. el asunto no es fácil, ya que estas conexiones se mueven por SQL.. asi que si quieres hacerlo con VBA tendrás que componer en VBA unas instrucciones en lenguaje SQL para poder aplicarle los criterios que necesites..

      No es algo sencillo de componer...

      Te recomendaría, si es posible, lo hicieras mediante el asistente.

      Slds

      Eliminar
  10. Hola Ismael: muchas gracias, ya realice las correcciones, pero dado que la formula la tengo en una celda que se repite en el encabezamiento, me sale en todas: "Pagina 1 de 3". Ademas el área de impresión ya esta definida, pero las páginas pueden variar al cambiar las filas de altura. ¿qué puedo hacer?

    ResponderEliminar
    Respuestas
    1. Claro,
      tal cual se explica se recoge la página en la que se encuentra la fórmula y el total de hoja dentro del área de impresión...
      si la fórmula está en la misma celda dentro del área de impresión, obviamente te devolverá 1 de 3, 1 de 4, etc...
      Solo te queda redimensionar el área de impresión y ajustarlo a tu necesidad.

      Recuerda que la fórmula sólo te devuelve el dato reflejado....

      Eliminar
  11. Hola! ¿Qué tal? Tengo un listado muy largo de productos en una misma hoja de Excel, y quisiera hacer un indice al estilo Word indicando los títulos y en que hoja está. Todo el listado lo tengo un una sola hoja, no utilizo varias hojas. ¿Cómo se puede hacer? Otra pregunta: Como puedo indicar en determinada celda el número de página donde está esa celda. Es decir, yo quiero que en la celda c5 aparezca el nro. de página donde está esa celda, lo mismo en la celda c255, en lac632, y así sucesivamente hasta el c 3900. Mil gracias! Juana

    ResponderEliminar
  12. Hola Juana,
    no es fácil el asunto.. ya que intentarlo mediante funciones podría colapsar Excel (si el número de hojas es alto), ya que habría que buscar cada 'producto' en todas las hojas hasta dar con él, para a continuación trasladar el nombre de la hoja para ese producto.

    Algo más ágil sería hacer la misma operación con macros...

    Un ejemplo con funciones, suponiendo en la columna A de la Hoja1 el listado de 'productos':
    =SI(ESERROR(BUSCARV(A1;Hoja2!B:B;1;0));SI(ESERROR(BUSCARV(A1;Hoja3!B:B;1;0));"no existe código";"Hoja3");"Hoja2")

    vemos que vamos buscando hoja por hoja...

    Espero te oriente.
    Un saludo

    ResponderEliminar
  13. Estimado Ismael:
    Gracias por el conocimiento, a mi me ha resultado muy bien, aunque no sin problemas. Yo he utilizado estos comandos para generar un índice al estilo Word de 28 títulos que están dentro de una misma hoja (que tiene 40 pag, es decir 41 saltos verticales). Para identificar la página en la que esta cada titulo, le he acompañado una celda con la función PagN, pero resulta que cada vez que hago un cambio, se recalcula los 28 marcadores PagN y tarda un monton (incluso se cuelga a veces). Yo me imagino que Excel debe tener una variable donde guarda el numero de página de un rango dado y que se pueda llamar para que no sea necesario calcularla cada vez (de alguna parte sale el numero de página que muestra en el fondo) ¿Existirá tal variable? Gracias,

    Héctor

    ResponderEliminar
    Respuestas
    1. Hola Héctor,
      date cuenta que con lo que trabajamos son fórmulas.. y por tanto están sujetas al método de cálculo (automático, manual...), esto es, sujetos al recálculo cuando cambie algo de la hoja.

      Es raro que se te cuelgue sólo por estas 40 funciones (son muy pocas), quizá el problema sea otro.

      Puedes de todas formas probar con esta otra posibilidad con macros, podría ser algo más eficiente
      http://excelforo.blogspot.com.es/2012/12/vba-una-funcion-personalizada-para.html

      Espero te resulte
      Slds cordilaes

      Eliminar
  14. disculpa no es sobre poner el numero de pagina en una celda pero quisiera saber si se puede imprimir con numeros de pagina solo que mi documento es largo hacia la derecha y quisiera q al momento de imprimir hacia la derecha las paginas salieran con el mismo numero y haci sucesivamente hacia abajoo y ala derecha....nose si me explique...de ante mano gracias por que lo que publicaste si me sirvio para otras cosas jejeje

    ResponderEliminar
    Respuestas
    1. Hola!
      aunque si es controlable el sentido de la paginación (izquierda-derecha o arriba-abajo), el número de cada página va a función de los Saltos de página (horizontales y verticales).. así, por tanto, no es posible mantener un número de página constante con la herramienta Pié de página.

      Te quedaría solo la opción de numerarlos manualmente.

      Saludos

      Eliminar
  15. buenas tardes me podria ayudar para que en las hojas de calculo del libro de excel los numeros de pagina sea automaticamente gracias

    ResponderEliminar
    Respuestas
    1. Hola gustavo,
      puedes configurar la numeración en el pié de página en la Configuración de página, así se dispondrá la autonumeración a tus impresiones...

      Para otros casos aplicar lo descrito en esta misma entrada.

      Saludos

      Eliminar
    2. HABRIA LA POSIBILIDAD DE HACER LO MISMO CON EL CODIGO DESCRITO EN LA ENTRADA GRACIAS

      Eliminar
  16. Buenas a todos.

    Después de pelearme unas horas con este código, os quería dar dos consejos, por si a alguien le sirven.

    1. En EXCEL 2010 han cambiado el nombre de la función EXTRAE por MED, por lo que habría que definir la "celda" Hoja de teniéndolo en cuenta. De lo contrario, el programa da el error "NOMBRE?".

    2. Existen otras funciones, que hacen lo mismo, por si a alguien le sirve: http://dailydoseofexcel.com/archives/2004/12/22/page-of-pages-in-a-cell/ (no soy el propietario de la página, solo lo pongo porque a mi me ha resultado útil).

    Saludos para el autor del tutorial

    ResponderEliminar
    Respuestas
    1. BTW,

      Estas funciones las busco para poner el número de la hoja en las filas de "encabezado" que se repiten en cada página impresa. (En Excel 2010: Diseño de página -> Imprimir títulos -> Repetir filas en extremo superior) Sin embargo, no consigo hacer que el número de la página (función "PagN") se cambie según el encabezado se repite. ¿Conocería el autor alguna solución para eso? Muchas gracias de antemano.

      Eliminar
    2. Hola Martín,
      respecto a tu primer cometario
      (punto 1). En realidad no es del todo cierto lo que comentas.. en la primera versión de Excel 2010 que sacaron sí modificaron los nombres de algunas funciones (CONSULTAV por BUSCARV, MED por EXTRAE, etc...), pero inmediatamente corrigieron esta desfase, y en siguientes 'subversiones' de Excel 2010 volvieron a sus aguas, y recuperaron los nombres de las funciones de siempre.

      Punto 2: Puedes ver en español aquí:
      http://excelforo.blogspot.com.es/2012/03/numero-de-pagina-en-una-celda-de-excel.html
      un ejemplo (igual que el del link).
      ;-)

      En cuanto a tu última cuestión (segundo comentario) no me queda claro qué necesitas hacer y a qué te refieres con:
      no consigo hacer que el número de la página (función "PagN") se cambie según el encabezado se repite
      ????

      Saludos

      Eliminar
  17. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  18. Hola:
    estoy tratando colocar en un libro en excel 2010, el numero de pagina, he intentado con los pasos que hay en el foro, pero no logro enterder nada, ademas los links que salen ya no existen...ojala me pudieran responder ya que he preguntado sobre el mismo tema en otros foros y nadie responde.... un saludo

    ResponderEliminar
    Respuestas
    1. Hola María,
      en realidad la forma explicada en este post es bastante laboriosa, pero efectiva sin emplear macros... el trabajo consiste en ir creando todos los nombres definidos que se especifican.
      Otra manera más simple con macros la puedes ver aquí:
      http://excelforo.blogspot.com.es/2012/12/vba-una-funcion-personalizada-para.html

      Espero te sea más fácil de ver...
      P.D.: ¿qué links no funcionan?.. no hay vínculos en este post?

      Eliminar
  19. Estimado, tengo Excel 2016 (En Office 365 Home), cuando quiero crear los nombres tal como describes en el foro, copio las formulas que pones, por ejemplo: en Nombre: PagsN y en Se Refiere a: =INDICAR.DOCUMENTO(50;Hoja) pero me sale una pantalla de error que dice "Hay un problema con esta formula. No intenta introducir una formula? Cuando el primer caracter es un signo igual (=)........" Sabes si esas funciones existen en Excel 2016? Si no es así como podría hacer lo mismo en Excel 2016?

    ResponderEliminar
    Respuestas
    1. Hola Jesús,tengo que confirmarlo...
      pero parece han dejado de estar disponible.
      Lo mejor es tirar de programación.
      Echa un vistazo a:
      http://excelforo.blogspot.com.es/2012/12/vba-una-funcion-personalizada-para.html
      Saludos

      Eliminar
  20. no me queda el comando finalx de y

    ResponderEliminar
    Respuestas
    1. Hola Carlos,
      un placer saludarte igualmente

      Revisa todos los pasos, son muchos los nombres definidos a crear y es fácil olvidar alguno...

      Un saludo

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.