Categoría: Office

Categorías
Excel Sin categoría

Añadir columnas en misma fila basado en mismo valor otra columna

Si necesitais mover el valor de las columnas en una misma celda de una fila basandose en otro valor de otra columna, a continuación podreis ver como lo podreis conseguir.

Veamos un ejemplo. Si partimos de una tabla de datos como la que se aprecia en la imagen. Nuestra intención es poner los valores de la tercera columna en la primera fila de cada uno de los grupos de fila que nos viene dado por la columna «Valor1»

De modo que el resultado que queremos conseguir, es el siguiente. Fijaros en las filas en negrita, en ellas hemos pasado los valores de la columna «ValorAPonerEnFila» y los hemos pasado a la primera celda de cada grupo (en este caso serparados por , pero podriamos poner cualquier caracter como separador.

La fórmula que nos va a permitir hacer esto será la siguiente =SI(B3<>B4;D3;CONCAT(E4;»,»;D3)). Con el simple uso de el SI y CONCAT, conseguiremos el objetivo buscado. Vamos a verlo con más detalle.

Primero, ponemos la condición donde evaluamos si estamos en el mismo grupo o es un grupo diferente B3<>B4, si es el caso, elegiremos el valor de la columna correspondiente sin concatenar, D3 si por el contrario es el mismo grupo, concatenaremos el valor que ya hemos puesto en la fila, luego una coma y por último el valor de la columna. CONCAT(E4;»,»;D3). Porúltimo, arrastraremos la formula hacia abajo por todas las columnas. Así conseguimos que se vaya añadiendo una columna en nuestra celda final, en cada paso que damos.

Una vez conseguido nuestro propósito, facilmente podemos variar el orden de nuestra celda resultado simplemente cambiando el orden de nuestra concatenación. De este modo, si cambiamos el E4 por el D3 dentro del concat, conseguiremos que cambie el orden de nuestra celda resultado.

En la imagen podeis ver como ahora el orden del resultado ha cambiado.

Lo mismo sucede, con el delimitador, simplemente cambiando la coma por un espacio conseguimos que el resultado tenga este aspecto.

Dejad vuestros comentarios.

Categorías
Excel Sin categoría

Repetir valor en filas vacias

Alguna vez habeis tenido un excel donde varias filas tienen en común el valor de la primera fila de su grupo pero las demas celdas estan vacias y necesitais «relllenarlo». Es el típico caso Cabecera-Líneas, donde tienes ciertos valores de la cabecera que no están en las líneas y necesitas repetirlo para luego hacer operaciones en las lineas. En la imagen de más abajo podeis ver un ejemplo. Como podreis ver, debajo del número de pedido no se repite el valor de mismo, por lo que se pueden hacer operaciones basadas en el valor del pedido.

Un modo de solucionarlo es añadir una columna y poner la siguiente fórmula =SI(ESBLANCO(C2);D1;C2) en ella con lo que nos rellenará el valor del pedido en todas las líneas.

El resultado será el siguiente:

Como podeis ver ahora todas lineas tienen el valor del pedido.

Dejad vuestros comentarios.

Categorías
Excel Office

Como comparar dos hojas de excel

Muchas veces es útil comparar dos libros excel uno al lado del otro. De este modo podemos comparar datos, sin tener que ir minimizando un libro y maximizando el otro cada vez que lo queremos consultar.

Depende de las versión de excel que utilicemos, veremos que el resultado es un poco diferente, aunque practicamente es lo mismo.. A continuación, vamos a ver como lograr nuestro propósito, en cada uno de los casos.

Comparativa entre libros de excel

Tenemos que ir a la pestaña Vista(1), y una vez allí, pulstamos en «Ver en paralelo»(2), si tenemos dos abiertos, se pondrán una debajo de la otra, si tenemos más nos pedirá que elijamos cual de ella queremos comparar.

Otra vez desde la pestaña de vista(1), haremos clic en el icono de «Organizar todo» (2) y luego cuando nos salga la ventana emergente, seleccionamos «Vertical» (3)

El resultado es el siguiente, tenemos las dos hojas, una al lado de la otra y encima por defecto, están sincronizadas, es decir cuando bajemos o subamos con la barra vertical, se moverán al unisono.

En el ejemplo hemos bajado a la fila 42 en el Libro1 y el Libro2 ha bajado a la misma fila.

Si queremos que no sea así, simplemente tenemos que pulsar el icono de «Desplazamiento síncrono» que se encuentra en el subapartado Ventana.

De este modo al subir a la fila 18 en el Libro1 el Libro2 sigue estando en la fila 42

En versiones anteriores de excel, el aspecto puede ser diferente, puesto que en la version 2010, por ejemplo, la comparación se realiza dentro de una misma istancia del programa, en vez de instancias diferentes como en la de 2016.

Dejad vuestros comentarios.

Categorías
Excel Powershell

Combinar varios excels en uno

Alguna vez has querido combinar varios excels en uno?. Una solución sería abrir un nuevo excel y luego todos los demas e ir moviendo en cada uno de los excels la primera hoja al excel que hemos creado para tal proposito. Esta solución puede ir bien cuando tenemos un par de excels que «unir» pero cuando son mas o lo tenemos que hacer repetidas veces, es más facil utilizar el script que os voy a enseñar a continuación.

Supongamos que en una carpeta tenemos varios excel como podeis ver en la imagen de más abajo. En el ejmplo tenemos que en la carpeta (1) c:\tmp\archivosexcel tenemos (2) tres archivos excel (3)(4) y (5), los cuales los queremos unir en uno solo.

Con el siguiente script de powershell podremos unirlos fácil y rapidamente.

#Abrimos una aplicación excel
$ExcelObject=New-Object -ComObject excel.application
$ExcelObject.visible=$true

#Cogemos los excels que queremos unir.
$ExcelFiles=Get-ChildItem -Path C:\tmp\archivosexcel

$Workbook=$ExcelObject.Workbooks.add()
$Worksheet=$Workbook.Sheets.Item("Hoja1")

foreach($ExcelFile in $ExcelFiles){
    #Abrimos cada hoja de cada fichero y la copiamos en nuestro nuevo excel.
    $Everyexcel=$ExcelObject.Workbooks.Open($ExcelFile.FullName,0,$true)
    $Everysheet=$Everyexcel.sheets.item(1)
    $Everysheet.Copy($Worksheet)
    $Everyexcel.Close()
 
}
#Grabamos nuestro excel que contiene los demas.
$Workbook.SaveAs("C:\tmp\archivosexcel\ExcelUnido.xlsx")
$ExcelObject.Quit()

Como podeis ver, el script es bastante sencillo. Hay cuatro partes diferenciadas. Primero abrimos una instancia del excel

Luego por una parte cogemos los ficheros que queremos unir y añadimos una hoja de excel en el archivo excel que habiamos creado en el primer paso.

A continuación, para cada uno de los ficheros de la carpeta, abrimos su primera hoja y la copiamos en libro que hemos creado al inicio. Una vez hecho esto cerramos el fichero excel del cual hemos copiado la hoja.

Por último, guardamos el libro que hemos utilizado para unir todos los demás excel y cerramos la instancia de excel que hemos utilizado.

El resultado de todo es un excel con todas las primeras hojas de los excels originarios.

Como habeis visto, este script solo copia la primera hoja de cada libro, con una pequeña modificación os podeis adaptar el script para que copie todas las hojas de cada de uno de los ficheros involucrados.

A continuación podeis ver un video con todo el proceso.

Dejad vuestra opinión sobre este script.

Categorías
Excel

Como añadir ceros a la izquierda en excel

A veces, es realmente complicado añadir ceros a la izquierda en Excel. Cuando intentamos poner un 0 delante de un número al inicio de una celda, «automaticamente» el excel lo reconoce y los elimina. Generalemnte este comportamiento es correcto y nos evita tener 0s innecesarios en nuestras celdas, pero otras veces, es realmente molesto ya que si queremos tener ceros a la izquierda se convierte en una tarea complicada.

Imaginaros la situación donde tenenmos unos códigos de producto como por ejemplo 0034098 y 0897838, y que, al copiarlos en el excel nos aparecerían tal que así (imagen de más abajo), después de copiarlos con ctrl+c y ctrl+v.

Como podeis observar esto tiene dos problemas, primero no se trata de los códigos que queríamos, ya que faltan los ceros y ademas, no tienen el mismo número de carácteres puesto que en el primero ha quitado dos ceros y en el segundo uno.

Para evitar este comportamiento, tenemos varias opciones aunque aquí os mostraré dos de ellas. La primera podemos decir que se trata de una solución visual, puesto que, aunque veremos los ceros no «estarán» allí. Es decir, cambiaremos el formato pero no los datos. Esta solución tiene resuelto el tema del aspecto pero no el de los datos en si, puesto que si los intentáramos utilizar más adelante, los ceros no «estarán» alli.

Veamos dicha solución. Primero nos pondremos en la celda que nos interesa, en este caso la A1 (1), luego con el boton derecho, elegimos la opción «Formato de celdas….», que nos abrirá una ventana como la de abajo. A continuación vamos a la categoría «Personalizada»(2) y en Tipo(3), ponemos tantos ceros como nos interese. En este caso siete, por lo que pondremos 0000000 en (3). Finalmente le damos a aceptar(4).

finalmente, copiaremos el formato de la celda poniendonos encima de ella y haciendo clic en «Copiar formato»(5).

Por último, seleccionaremos las celdas donde queramos aplicar dicho formato, en nuestro caso la A2 y veremos el resultado final.

Como podeis observar ahora, ya tenemos ceros a la izquierda visualmente, pero observad lo que pasa cuando pulsamos sobre una de las dos celdas.

Efectivamente, visualmente tenemos ceros, pero en el valor de la celda seguimos sin tener el cero esperado. De este modo, si copiáramos la celda y la utilizaramos en cualquier otro lugar de la excel o fuera de el, no nos saldría el cero a la izquierda.

Ahora veamos otro método de hacer lo mismo, pero esta vez no solo aparecerán en la vista los ceros, sino que tambien los datos «tendrán» estos ceros. Con esta solución, si copiamos los valores, (para por ejemplo luego filtrar en una base de datos de productos), si nos aparecerán los ceros a la izquierda.

Empezaremos, pues, por ir a una casilla en blanco, B2 (1) y nos iremos a la barra de fórmulas(2), allí escribiremos =TEXTO(A1;»0000000″)

Con esta fórmula, estamos diciendo que se coja el texto de la casilla A1 y se le rellene con 0 todos aquellos caractares vacios hasta llegar a 7, que es el numero de ceros que hemos escrito.

El resultado será añadir dos ceros a la izquierda hasta completar los siete caracteres como queriamos.

Luego arrastrando la fórmula, conseguiremos lo mismo para A2.

Fijaros que esta vez solo añade un cero, puesto que hasta llegar a siete caracteres, solo hacia falta un cero.

Bueno espero que alguna de las dos formas os sirva para vuestros propósitos.

Categorías
Excel Ofimatica

Color de fondo de filas de excel según grupo valores

Si quereis formatear las filas segun un grupo de valores, por ejemplo, cambiar fondo de las filas que tengan el mismo documento de factura o todas las filas que tengan la misma fecha, etc. en este entrada, vamos a ver como poder llevarlo a cabo.

Pongamos que tenemos unos datos como los de mas abajo, donde nos interesa «agrupar» por colores segun el Nº de documento, en este caso facturas.

Lo que pretendemos, es que las filas cambien su color de fondo, cuando a su vez cambien los valores de las facturas, de forma alternada, es decir, queremos que las filas 2 y 3 tengan un fondo de un color, las filas 4,5 y 6 otro, las 7,8,9 y 10 igual que el primer grupo y así una y otra vez para cada grupo.

Para conseguir nuestro propósito, primero deberemos preparar las filas para poder formatearlas más tarde. La idea es dar valores iguales a las diferentes filas con el mismo valor de factura en nuestro ejemplo.

Lo que haremos es añadir una columna mas en nuestra hoja donde pondremos el valor 0 en la celda H1 (1), después en H2 (2), pondremos la siguiente fórmula (3), =SI(A2=A1;H1;H1+1) de este modo lo que conseguiremos es poner valores iguales en las filas con el mismo numero de factura. El resultado lo podeis ver en la imagen de más abajo.

Una vez preparadas las filas, ya podremos empezar con el formateo de las filas, primero (1) seleccionamos todos los datos sin la cabecera, luego desde inicio, vamos a formato condicional(2) y pulsamos en nueva regla(3)

Nos aparecerá la ventana de Nueva regla de formato, y allí, seleccionaremos «Utilice una fórmula que determine las celdas para aplicar formato»(1), nos aparecerá el campo donde podremos poner nuestra fórmula(2), nuestra fórmula es =Y(LARGO($A2)>0;RESIDUO($H2;2)=0) que quiere decir verdadero si el valor de la celda $A2 tiene valor y si el valor de $H2 (el grupo), es par, de lo contrario no se cumplirá la formula y dará falso. Ahora nos queda dar formato, a las filas pares, pulsaremos en formato(3), se abrirá la venta de formato de celdas, elegiremos el color que más nos guste (4), y luego daremos aceptar a las dos ventanas (5) y (6).

Como podeis ver el resultado es el esperado, las filas pares tienen un color y las impares otro.

Os dejo el fichero excel por si quereis ver el ejemplo insitu.

Formato-diferente-para-filas-pares-e-impraresDescarga

Para avanzar mas os propongo como tarea el que las filas impares, tengan otro color que no sea el blanco. Ya me decís en los comentarios si lo conseguís.

Categorías
Excel Office Sin categoría

Buscarx tutorial básico con ejemplos

En este tutorial vamos a ver como se utiliza la nueva fución de excel Buscarx, con ella se facilita de manera notable tanto la busqueda de valores en filas, columnas y rangos como la obtención de los mismos. Ahora la función es muy mas potente y flexible.

Empecemos por su sintaxis, =BUSCARX(valor_buscado; matriz_buscada; matriz_devuelta; [si_no_se_encuentra]; [modo_de_coincidencia]; [modo_de_búsqueda]) 

Como podeis ver, cambia bastante respecto las «antiguas» BuscarV y BuscarH, ahora para podemos devolver rangos enteros, devolver valores si no se encuentra el valor buscado, muchas mas opciones en cuanto a valores de coincidencia y varios «modos de busqueda» que nos permiten incluso hacer ordenaciones de los datos antes de «encontrarlos». Aquí podeis ver una explicación de los argumentos.

argumentoDescripción
valor_buscado RequeridoEl valor buscado
matriz_buscada RequeridoLa matriz o rango para buscar
matriz_devuelta RequeridoLa matriz o rango para devolver
[si_no_se_encuentra] OpcionalSi no se encuentra una coincidencia válida, devolver el texto [if_not_found] que proporcione. Si no se encuentra una coincidencia válida y no hay [if_not_found], se devolverá #N/A.
[modo_de_coincidencia] OpcionalEspecifique el tipo de coincidencia: 0: coincidencia exacta. Si no se encuentra ninguno, devolver #N/A. Este valor es el predeterminado. -1: coincidencia exacta. Si no se encuentra ninguna, devolver el siguiente elemento más pequeño. 1: coincidencia exacta. Si no se encuentra ninguna, devolver el siguiente elemento más grande. 2: una coincidencia comodín donde *, ? y ~ tienen significado especial.
[modo_de_búsqueda] OpcionalEspecifique el modo de búsqueda que se usará: 1: realizar una búsqueda empezando por el primer elemento. Este valor es el predeterminado. -1: realizar una búsqueda inversa empezando por el último elemento. 2: realizar una búsqueda binaria que se base en que lookup_array se ordene en orden ascendente. Si no está ordenada, se devolverán resultados no válidos. -2: realizar una búsqueda binaria que se base en que lookup_array se ordene en orden descendente. Si no está ordenada, se devolverán resultados no válidos.

Pasemos a ver 3 ejemplos.

1. – El uso más sencillo, como si fuera el antiguo Buscarv, ahora es mas intuitivo, hay que poner el valor a buscar, el rango donde buscar y el rango de los datos a obtener. Por defecto la búsqueda es exacta, al contrario que en buscarv. Abajo podeis ver una comparativa de las dos fórmulas para obtener el mismo resultado.

En las imágenes se utilizan los equivalentes en inglés buscar(xlookup) y buscarv(vlookup), aunque son totalmente análogos a la versión española.

2.- Seguimos con el segundo ejemplo, en este caso veremos la potencia de la nueva fórmula respecto buscarv, en este ejemplo devolveremos varios valores en vez de uno solo como es el caso de buscarv.

Como vemos en la imágen, al poner el rango de valores encontrados, vemos como en (3), no solo nos da el nombre del vendedor sino que también el apellido!!!, sin tener que volver a escribir otra vez la fórmula para obtener cada celda.

Este es el resultado final de utilizar la fórmula en este ejemplo.

3.- Utilización del parámetro [si_no_se_encuentra], ahora en buscarx podemos poner nosotros el valor que queramos que se devuelva cuando no se encuentra el elemento buscado. Recordad que esto no era posible con buscarx y buscarv que devolvián el N/A#.

En el ejemplo vemos como al no encontrar a SALES5 (1) puesto que no existe, se devuelve «No Encontrado», que es el texto que hemos puesto en la fórmula(2)

Aquí tenéis el excel con los ejemplos por si os hiciese falta.

BuscarxDescarga

Espero que os haya gustado, en entradas posteriores, veremos que buscarx tiene mucho más potencia que lo que hemos visto hasta ahora.

Categorías
Office Outlook

Reparar fichero ost o pst de outlook

Se te ha estropeado tu fichero pst o ost de outlook??? no te preocupes, puede que aun haya solución. Lo primero que puedes hacer es probar con las herramientas oficiales. Es decir, con las propias herramientas de Microsoft.

Para la reparación del fichero que contiene los mensajes del outlook, microsoft sacó la herramienta scanpst.exe. Con esta herramienta podrás solucionar los problemas más basicos que hayas tenido con tu almacén de emails, como que el fichero esté dañado o corrupto.

Vamos a ver como procederiamos para reparar el fichero. Primero lanzamos la aplicacion de recuperacion automaticamente desde este link http://download.microsoft.com/download/8/b/8/8b889bfe-9118-4f74-9fc4-ebc4c163b02c/microsofteasyfix20101.mini.diagcab

Sino arranca automáticamente, podeis buscar el fichero local desde la ubicación de donde tengais el outlook instalado. Aquí os dejo las posibles alternativas.

Ubicación típicaComentarios
<unidad de disco>:\Archivos de programa (x86)\Microsoft Office\root\Office16Para una instalación para hacer clic y ejecutar de Outlook 2016 en una versión de 64 bits de Windows
<unidad de disco>:\Archivos de programa\Microsoft Office\root\Office16Para una instalación para hacer clic y ejecutar de Outlook 2016 en una versión de 32 bits de Windows
<unidad de disco>:\Archivos de programa (x86)\Microsoft Office\Office16Para una instalación MSI de Outlook 2016 en una versión de 64 bits de Windows
<unidad de disco>:\Archivos de programa\Microsoft Office\Office16Para una instalación MSI de Outlook 2016 en una versión de 32 bits de Windows
<unidad de disco>:\Archivos de programa\Microsoft Office 15\root\office15Para una instalación para hacer clic y ejecutar de Outlook 2013 en una versión de 32 bits de Windows
<unidad de disco>:\Archivos de programa (x86)\Microsoft Office 15\root\office15Para una instalación para hacer clic y ejecutar de Outlook 2013 en una versión de 64 bits de Windows
<unidad de disco>:\Archivos de programa\Microsoft Office\Office15Para una instalación MSI de Outlook 2013 en una versión de 32 bits de Windows
<unidad de disco>:\Archivos de programa (x86)\Microsoft Office\Office15Para una instalación MSI de Outlook 2013 en una versión de 64 bits de Windows
<unidad de disco>:\Archivos de programa\Microsoft Office\Office14Para Outlook 2010 una la versión de 32 bits de Windows
<unidad de disco>:\Archivos de programa\Microsoft Office(x86)\Office14Para Outlook 2010 en la versión de 64 bits de Windows
<unidad de disco>:\Archivos de programa\Microsoft Office\Office12Para Outlook 2007 en la versión de 32 bits de Windows
<unidad de disco>:\Archivos de programa (x86)\Microsoft Office\Office12Para Outlook 2007 en la versión de 64 bits de Windows
<unidad de disco>:\Archivos de programa\Common Files\System\Mapi\1033\Otras ubicaciones típicas
<unidad de disco>:\Archivos de programa\Common Files\System\MSMAPI\1033Otras ubicaciones típicas
<unidad de disco>:\Archivos de programa\Common Files\System\Mapi\1033\NTOtras ubicaciones típicas (para Windows NT y Windows 2000)
<unidad de disco>:\Archivos de programa\Common Files\System\Mapi\1033\95Otras localizaciones típicas (para Windows 95 y Windows 98)

Una vez en el directorio en cuestión, ya podeis ejecutar el scanpst.exe y os saldra una ventana como esta:

El último paso seria elegir el fichero pst o ost e iniciar la reparación.

Las ubicaciones por defecto de los ficheros pst o ost son las siguientes (aunque puede que hayais puesto el fichero en otra ruta, en ese caso tendriais que buscar el fichero adecuado)

  • Windows 10 unidad: \ usuarios\AppData\Local\Microsoft\Outlook
  • Windows 10 unidad: \ usuarios\roaming\local\microsoft\outlook
  • Versiones anteriores de Windows unidad: \ Documents and Settings \ Local Settings\Application Data\Microsoft\Outlook

Es posible que sea necesario ejecutar varias veces la Herramienta de reparación de la Bandeja de entrada para reparar complemente el archivo de carpeta personal (.pst). Debeis de tener en cuenta, que la Herramienta de reparación de la Bandeja de entrada no puede reparar todos los problemas que se detectan. En ocasiones, los elementos no se pueden recuperar si se eliminaron definitivamente o el daño era irreparable.

Espero que os haya servidor de ayuda.

Categorías
Office

La activación telefónica ya no es posible en Office

Si os aparece un mensaje en Office 2010, Office 2013 y Office 2016 diciendo que la activación telefónica para estos productos ya no se admite. Y, aunque pongais el país no os aparece ningún telefono, no os preocupeis, se debe a un error que está confirmado por parte de microsoft. Fijaros como en la imagen pone el mensaje «Este producto ya no se puede activar por teléfono.

La activación por teléfono todavía funciona. Aún podeis contactar con el sistema de activación de Microsoft en el número siguiente: 900 150 889.

Por lo demás el resto de la activación es como siempre. Espero que os sirva, no dudeis en deja un comentario.

Categorías
Excel Sin categoría

Como añadir una linea dentro de una celda de excel

Aunque pueda parecer una tontería, a veces se puede necesitar el tener mas de una linea en una celda de excel. Con este pequeño «truco», es muy fácil conseguirlo. Simplemente teneis que pulsar la tecla alt+enter. De este modo podremos añadir lineas dentro de la celda.

Como podeis ver en la imagen de más abajo, en las celdas (1) y (2), he puesto un enter sin más por lo que he bajado a la siguiente celda. En cambio, en (3) he pulsado alt+enter y entonces he podido escribir otra linea dentro de la misma celda sin que saltara de celda.