En ocasiones, nececsitaremos 1. Saber si mi dato se repite. 2. Donde está e dato que se repite.
Dada esta necesidad, emplearemos las funciones contar.si y coincidir.

La funcion contar.si, como ya sabemos nos ayuda a contar dependiendo de una condicion dada.
La funcion coincidir, me devolverá una posicion dependiendo de una condicion dada.
COINCIDIR devuelve la posición del valor coincidente dentro de matriz_buscada, no el valor en sí. Por ejemplo, COINCIDIR("b",{"a","b","c"},0) devuelve 2, la posición relativa de "b" dentro de la matriz {"a","b","c"}.



Sintaxis:


COINCIDIR(valor_buscado,matriz_buscada, [tipo_de_coincidencia])

   Valor_buscado    Obligatorio. Es el valor que desea buscar en matriz_buscada.
   Matriz_buscada    Obligatorio. Es el rango de celdas en que se realiza la búsqueda.

CONTAR.SI(matriz_buscada, valor_buscado)

  Valor_buscado    Obligatorio. Es el valor que desea buscar en matriz_buscada.
  Matriz_buscada    Obligatorio. Es el rango de celdas en que se realiza la búsqueda.


Ahora para nuestro ejemplo, tenemos este listado de nombres.

 


Deseamos si se repite, cuantas veces y donde.

Primero usaremos la funcion contar.si, para saber cuantas veces. En la celda B1 escribiremos la formula: =CONTAR.SI($A$1:$A$26;A1) . Copiaremos la formula en toda la columna B


Ya con esta sabemos cuantas veces se repite mi dato.

Ahora para saber donde usaremos la funcion coincidir en la celda C1 escribiremos =COINCIDIR(A1;$A$1:$A$26;0)



 Ahora debemos escribir estas dos funciones en una sola celda, además tendremos que usar la funcion SI, para que cuando se repite me diga donde, y en caso que no me coloque cero.


La funcion completa quedaria asi. =SI(CONTAR.SI($A$1:$A$26;A1);COINCIDIR(A1;$A$1:$A$26;0);0)



Esta fórmula arrojará la fila donde está el valor repetido. Es importante recordar que el valor arrojado es el número de la fila dentro del rango fijado, de modo que si el rango es A2:A26 y el valor se repite en la fila 3, en realidad estará en la fila 4, puesto que el rango inicia en la fila 2.





Si te gustó, síguenos en Twitter  para recibir nuestras actualizaciones y trucos

Con bastante frecuencia nos sucede que nos envian un archivo de excel, protegido y desconocemos su clave. O tambien que le colocamos clave a nuestros archivos y luego la olvidamos. Nos preguntamos ¿ Y ahora que hacemos??

Hoy tendremos la oportunidad de ver como quitar la clave a un libro u hoja de excel, de manera sencilla, rapida y practica.


Necesitaremos:  
1. El archivo de excel al que vamos a quitar la clave
2. WinRar o 7Zip

Paso 1:  Realice un backup de su archivo, ya que no nos responsabilizamos de perdidas por este procedimiento.  Como buena practica, le invitamos a crear una copia de seguridad del archivo.



Paso 2: Validar el formato de mi archivo de excel, ya que solo permitirá este procedimiento a los que tengan extension xlsx o xlsm.
En caso que su archivo sea de extension XLS. Debe abrirlo y guardarlo con extension xlsx para poder continuar.
 . 
Paso 3: Como el formato de excel se basa en una estructura xml, podremos ingresar con winrar y realizar un cambio Interno.

*Abrimos Winrar o 7zip  y luego clic en el menu Abrir Archivo,




















*En la parte inferior desplegamos la lista de Todos los archivos y elegimos Todos los ficheros. Aqui buscamos el archivo .XLSX que deseamos quitar la clave.




















*Veremos  una serie de carpetas de esta manera.




















*Doble clic a la que dice XL. Sigue los pasos a continuacion dependiendo

SI LA CLAVE ESTA EN TODO EL LIBRO

*DobleClic en Workbook.xml  que está en la carpeta XL del paso anterior. Si te pregunta con cual aplicacion abrir selecciona Notepad o bloc de notas




*  CTRL + B y buscamos la palabra  Protection.

Debe salir algo asi: 




*Aqui cambiaremos en la ultima linea lockStructure=”1″ por lockStructure=”0″

Guarda y cierra el archivo. 

WinRAR le dirá que "workbook.xml" fue modificado y le pedirá que lo actualice en el archivo. Seleccione SÍ

 Cierre de WinRAR y abra su archivo. Ya no debe tener clave. 


SI LA CLAVE ESTA EN UNA HOJA DEL LIBRO 

*DobleClic en la carpeta WorkSheets que está en la carpeta XL y luego en Sheets1, o la hoja que requieras quitar la proteccion.






















 *DobleClic en Sheets1.xml que está en la carpeta XL\WorksSheets. Si te pregunta con cual aplicacion abrir selecciona Notepad o bloc de notas. 


 CTRL + B y buscamos la palabra  Protection o sheetProtection

Debe salir algo asi: 



Borraremos toda esta linea completa, incluidos caracteres

Es decir borraremos desde sheetProtection...... hasta scenarios="1" ojo incluidos caracteres mayor y menor.

Guarda y cierra el archivo. 

WinRAR le dirá que "Sheets1.xml" fue modificado y le pedirá que lo actualice en el archivo. Seleccione SÍ


 Cierre de WinRAR y abra su archivo. Ya no debe tener clave.

 NOTA: Quizas en office 2010 sea necesario ir a la pestaña Revisar y dar clic en desproteger hoja o libro, pero ya no pedirá clave.

Espero les sea de utilidad, dejen sus comentarios.




Si te gustó, síguenos en Twitter  para recibir nuestras actualizaciones y trucos

El formato condicional ofrece una visualización de sus datos bien sea numéricos o de texto información gráfica adicional, resalta celdas interesantes, enfatiza valores poco usuales y visualiza datos usando barras de datos, escalas de color y conjuntos de íconos basados en criterios.




 1. Escriba en una hoja de cálculo nueva, la siguiente información

 2. Seleccione el rango de celdas desde B2 hasta E5




 5. De aquí en adelante, modificaremos la condición del formato que cumpla las características nuestras. Para ello, con este rango de celdas seleccionadas, haga clic nuevamente en "Formato condicional"; y seleccione la opción "Administrar reglas"
 


 6. En la ventana Administrador de reglas de formato condicional,

  



7. En la lista desplegable de la opción "Estilo de formato" seleccione el ítem "Conjunto de íconos"

8. Para nuestro ejemplo de empleados y sus ventas, le diremos que coloque el ícono

 9. Para el caso de
 evaluaremos como alerta los vendedores que deberán reforzar su plan de ventas, para lo cual, tendremos las ventas superiores a 500.000 y menores que 900.000. En este caso, digite el número 500000 en el campo "Valor", y en la opción "Tipo", seleccione Número.


 10. Y para el caso de






 Si te gustó, síguenos en Twitter  para recibir nuestras actualizaciones y trucos

En esta ocasion veremos varias funciones avanzadas, para realizar nuestro informe.

Aprenderemos como elegir de una lista desplegable el grafico que deseemos (previamente parametrizado.)




Digitaremos en una hoja unos datos para generar un gráfico pero que a su vez este tenga la posibilidad de seleccionar el tipo de gráfico.

1. Abra un libro nuevo en Excel.

2. Cree una lista con los siguientes datos:




 3. Cree el primer gráfico de tipo Columnas Agrupadas.








4. Seleccione el gráfico y córtelo (CTRL + X)

5. Péguelo en la Hoja2 (CTRL + V). Asegúrese que el área del gráfico coincida exactamente con un rango de celdas, para este caso A1:F16.

6. Copie el gráfico y péguelo en otro rango de la Hoja2, por ejemplo A18:F33.

7. Repita el mismo procedimiento

NOTA: En la Hoja2 debe tener 3 gráficos, Por favor cambie el estilo de gráficos: El primero en columnas, el segundo en líneas y el tercero en áreas. Para cambiar el tipo de gráfico: Haga clic en el gráfico y haga clic en cambiar tipo de gráfico




8. Cree un rango para cada una de las áreas de los gráficos así:

Columnas = Hoja2!$A$1:$F$16
Líneas = Hoja2!$A$18:$F$33
Area = Hoja2!$A$35:$F$50





9. En la Hoja2 cree una lista en el rango I1:I3 escribiendo en cada celda: Columnas, Lineas, Areas. Esta lista nos servirá más adelante para crear la lista desplegable.

10. Haga clic en la Hoja1, ubíquese en la celda H1  y cópiela (Ctrl+C). Ahora ubíquese en la celda D1 y presione la tecla MAYUSCULA, haga clic en la Ficha "Inicio", despliegue la opción "Pegar" - "Pegar como Imagen" y posteriormente seleccione la opción "Pegar vínculos de imagen"


11. Aparecerá una imagen vacía y en la barra de fórmulas verá la referencia a la celda que hemos copiado ($H$1)






13. Cree una lista desplegable usando el control  "Cuadro combinado" de la Ficha "Programador" (no controles ActiveX).

14. Dibuje el control y ubíquese sobre la celda  A12.

El rango de entrada es de la Hoja2 I1:I3

Vincule con la celda A12.



15. Cree el nombre "Tipo3" que contenga la siguiente fórmula:
=ELEGIR(Hoja1!$A$12;Columnas;Líneas;Areas)




16. Seleccione la celda D1 y en la barra de Fórmulas escriba =Tipo3

A partir de este momento el tipo de gráfico cambiará de acuerdo a la selección que usted haga en la lista desplegable

Puede descargar el archivo del ejercicio en este link (archivo comprimido)


Si aún no estás suscrito al Video Curso Gratuito "10 Temas de Excel que todo usuario debe conocer" puedes hacerlo en: https://www.visiontecnologica.com/Eventos/excel2016/10temasdeexcel.html  y también te invito a suscribirte a este canal de Youtube para recibir todas nuestras novedades.

Si te gustó, síguenos en Twitter  para recibir nuestras actualizaciones y trucos