por Hoy es el primer post que escribo después del nacimiento de mi hija Alicia, sin duda la mejor experiencia de mi vida!!. Han sido unos días muy intensos y felices y por fortuna todo ha salido muy bien.
Como comprenderéis no tengo demasiado tiempo últimamente, y ese el motivo de tardar tanto tiempo en publicar una actualización.
El tema de hoy es una consulta que me envían al blog sobre la función “Contar.Si” y la posibilidad de automatizarla en una hoja Excel mediante macros.
Me ha parecido un ejercicio interesante, sobre todo ver la forma en la que podemos introducir una fórmula en el código que vamos a utilizar. ¿Empezamos?
Como viene siendo habitual intentaré ilustrar el proceso con un ejemplo, que es la mejor forma de aprender. Imaginad que tenéis un listado en una columna con 199 nombres, (en el ejemplo he utilizado las bases de datos del INE en donde podéis encontrar, por ejemplo, los nombres más usados en los últimos nacimientos). Estos son los nombres:
Y nos piden dos consultas:
– Contar las veces que se repiten cada uno de los nombres y colocar el resultado en la columna B.
– Contar las veces que se repiten unos nombres en particular, y colocar el resultado en la columna C.
Para la primera consulta utilizaremos la siguiente macro:
Sub ContarSi()
Dim i As Double
Dim final As Double
'Limpiamos los contenidos de la columna B
Worksheets("DATOS").Range("B2:B65000").Select
Selection.ClearContents
'Calculamos el rango de los datos en la columna A
Worksheets("DATOS").Select
final = Application.CountA(Worksheets("DATOS").Range("a:a"))
For i = 2 To final
'Contamos las veces que se repiten cada uno de los nombres en el rango seleccionado
Nombres = Worksheets("DATOS").Cells(i, 1).Value
Worksheets("DATOS").Cells(i, 2).Value = Application.CountIf(Worksheets("DATOS").Range("A1:A" & final), Nombres)
Next
End Sub
Como podéis apreciar, incluimos la función Contar.Si mediante la siguiente sentencia:
Application.CountIf(Worksheets("DATOS").Range("A1:A" & final), Nombres)
De esta forma hacemos referencia a la sintaxis del rango establecido en el que contaremos Range(«A1:A» & final) y el criterio que es «Nombres» y que es igual a Worksheets(«DATOS»).Cells(i, 1).Value
El resultado de la macro lo colocaremos en la segunda columna, quedando así la información:
Para la segunda consulta utilizaremos la siguiente macro:
Sub ContarSiV()
Dim i As Double
Dim final As Double
'Limpiamos los contenidos de la columna C
Worksheets("DATOS").Range("C2:C65000").Select
Selection.ClearContents
'Calculamos el rango de los datos en la columna A
Worksheets("DATOS").Select
final = Application.CountA(Worksheets("DATOS").Range("a:a"))
For i = 2 To final
Nombres = Worksheets("DATOS").Cells(i, 1).Value
'Mediante una condición contamos las veces que se repiten los nombres elegidos
If Nombres = "IGNACIO" Or Nombres = "MARIA" Or Nombres = "TERESA" Then
Worksheets("DATOS").Cells(i, 3).Value = Application.CountIf(Worksheets("DATOS").Range("A1:A" & final), Nombres)
End If
Next
End Sub
En este caso estamos delimitando que la función solamente cuente aquellos nombres que hemos establecido en la condición (IGNACIO, MARIA Y TERESA), el resto quedan en blanco. El resultado es el siguiente:
Para facilitar las pruebas os he colocado dos botones para ejecutar cada uno de los códigos por separado.
Este ejemplo también puede ser muy útil para introducir cualquier tipo de función, tan solo tendréis que adaptar la sintaxis a VBA, por ejemplo: sumar.si, sumaproducto, contar.si.conjunto … etc.
Espero que os haya resultado de utilidad y que lo podáis integrar con éxito en vuestros proyectos de VBA
Descarga el archivo de ejemplo pulsando en: FUNCION CONTAR.SI EN VBA
Excelente aplicativo estimado……..Le comento se puede adaptar este aplicativo o se puede hacer uno que lo pueda utilizar en consulta de 2 hojas en Excel en el mismo libro y/o dos libros distintos. Gracias
Hola Jose;
Pues sí que se puede hacer lo que comentas, puedes verlo en el ejemplo que he subido a Google Drive pinchado en el siguiente enlace:
https://drive.google.com/folderview?id=0B2MZVcPxjhyPflgtNDB5aHVUSkxPVkd1QzZyME9TZmFKa2FscUJNWTMwVklpblFVcWZ4YTA&usp=sharing
Encontrarás una carpeta con dos archivos, uno de ellos es FUNCION CONTAR.SI EN VBA contiene las macros y los ejemplos para ir a consultar a otra página dentro del archivo. El otro archivo es parte del ejemplo para que pruebes a contar en «OTRO_LIBRO».
Para contar en otro libro, la forma de hacerlo y que me parece la mejor, es traerte la información que quieres contar al archivo, en nuestro caso lo pegaríamos en la pestaña BASE2
Para traer la información he usado una macro con SQL que copia la info de otro archivo. Lo único que debes hacer es poner en la macro denominada «contarsi_otro_libro()» la ruta en la que guardes el archivo «OTRO_LIBRO», en la macro he dejado la ruta en mi PC: C:\Users\USUARIO\Documents\FUNCION CONTAR.SI EN VBA\OTRO_LIBRO.xls
Si no modificas esta ruta en la macro, no funcionará y saltará un error.
Para tu información, la macro para importar los datos de otro libro está en esta entrada: https://excelsignum.wordpress.com/2015/04/25/realizar-consulta-sql-desde-vba-en-excel-hacer-una-consulta-de-access-en-excel/
Espero te sirvan de ayuda.
Saludos.
Gracias por la ayuda
Gracias a ti Elena. Saludos!!
Saludos amigo, soy muy novato en el tema de las macros pero recién estoy fascinado con ellas… el caso es esta macro que usted publica en el articulo es «exactamente» lo que estaba buscando salvo que lo que deseo contar son números, usted me podría ayudar con ello, gracias de de antemano
Hola Luis:
Pues contar números se hace de la misma forma que letras, es decir, en lugar de nombres pon datos numéricos y verás como la macro cuenta cuantas veces se repiten esos números. Solo debes cambiar los datos a contar.
Saludos.
Mil gracias por tan pronta respuesta, estoy seguro que así de fácil como usted indica, pero por mas que intento remplazar los nombre por los números no consigo que funcione, le estaría eternamente agradecido si me ayuda con este problema (tome en cuenta que solo tengo una semana en este mundillo) mi problema es que deseco contar las veces que se repite un numero en la columna (M3:M108) y el resultado se refleje en la columna (N3:N108), Nota: los números que me enteriza contar es del 0 al 99, en verdad hermano le estaré muy agradecido si es de su agrado ayudarme.
Hola Luis:
Te he enviado un correo con ejemplo con las columnas y modificando la macro para que funcione con las columnas en las que tienes datos.
Saludos.
Amigo segu, gracias por su atención pero lamentablemente la macro que me envió no la recibí debido a que apunte mal mi correo, le agradecería me la reenviar a: bunkerlock2015@gmail.com
Saludos.
Ya está reenviado, saludos
Que pena con usted amigo, me equivoque en el correo ya lo corregí, mil disculpas y gracias.
pido de su apoyo, todo con lo la Funcion contar esta entendido, pero ahora, como se sacaria el resultado, de esas dos columnas, pero ejemplo, tengo grupos de escuelas de 1ro, 2do y 3er anios, lo mismo pero se repite el en una columna 3 primeros, 2 segundos y 4 terceros, nunca en orden como lo puse separados por el tema de orden de lista, bueno y en otras dos columnas, se encuentran altas y bajas por separado, y en altas hay 3 ninios, en bajas 6 ninios, como saco el resultado por separa de los tres grados en altas y bajas de 1ro, 2do y 3ro
nombre alumnos altas bajas resultados=
alumno 1ros x grupos altas bajas
aumno 2dos x 1ro
alumno 2 x 2do
alumno 1 x 3ro
aumno 3ros
alumno 3 x
ETC….
Hola Benjy:
Para poder contestar a tu pregunta, necesitaría que me enviases un archivo con los datos como los tienes ahora, y en otra hoja que quieres que te queden. Y una explicación detallada de lo que debe realizar la macro. Saludos.
Muy interesante y loable de tu parte compartir tus conocimientos, mis respetos !!!
llegue aquí por que hace días me trae complicado una macro que realice la función SUMAR.SI con varios documentos que están en una misma carpeta.
Actualmente tengo una macro que suma una celda «x» en diferentes archivos que es esta:
Sub M2porMes()
Dim MyBook As String, myForm As String
Const Celda As String = «i26»
With ThisWorkbook
MyBook = Dir(.Path & «\*.Xls»)
Do
If MyBook .Name Then
myForm = myForm & » + ‘» & .Path & «\[» & MyBook & «]Hoja1’!» & Celda
End If
MyBook = Dir
Loop Until MyBook = «»
End With
ActiveCell.Formula = «=» & myForm
Rem — La siguiente línea es optativa —-
ActiveCell = ActiveCell.Value
End Sub
funciona a la perfeccion pero ahora se han sumado variables a los productos que se utilizan, es por eso que necesito que sume solo si se cumple «x» variable, como por ejemplo:
=SUMAR.SI.CONJUNTO(I11:I25;L11:L25;»INTERCOAT»)
espero me puedas ayudar
Muchas Gracias
Hola Manuel:
Tendrías que pasar la fórmula a VBA, que es esta: Application.WorksheetFunction.SumIfs(Rango de la suma, rango criterio, criterio).
Por ejemplo:
Dim i As Integer
With Sheets(1)
For i = 2 To 12
.Cells(i, 4).Value = Application.WorksheetFunction.SumIfs(.Range(«a1:a5000»), .Range(«b1:b5000»), «x»)
Next
End With
Saludos.
Segu:
me ha gustado tu manera de utilizar laa función Contar, por lo que m atrevo a consultarte: Cómo utilizar la función Contar.Si, si el rango de celdas que voy a contar varía al realizar modificaciones; se me ocurre ejemplificártelo conla disponibilidad de un lote de vehículos, alinicio TODOS los vehículos están disponibles, pero de acuerdo a como han salido de viaje o por desperfectos, algunos están indisponibles;pero al retornar o repararlos, nuevamente están disponibles.
Espero haberme hecho entender,
Gracias de antemano
Loroloco (Guatemala)
Hola Armando:
No he comprendido tu consulta, necesitaría que me enviases el ejemplo de lo que quieres hacer contando con mayor detalle el problema.
Saludos.
Hola, tengo una consulta. El countif no diferencia entre mayusculas y minusculas. Si tengo el nombre «CASA» y en otra celda «casa» dirá que es duplicado pero yo necesito que lo diferencie. Alguna idea?
muchas gracias
Hola Alonso:
Debes utilizar otro tipo de función. Sumaproducto.
=SUMAPRODUCTO(IGUAL(A1:A5; «casa»)*1)
o asi:
=SUMAPRODUCTO(–IGUAL(A1:A5; «casa»))
De esta forma obtendrás la información que necesitas.
Saludos
Buenas tardes que modificaciones uno debe realizar para realizar el contar si pero con mas de dos criterios y también teniendo en cuenta que en un libro pueden estar varios datos de consulta y en otro libro las bases de datos.
Hola Manuel:
Debes utilizar la función CONTAR.SI.CONJUNTO (no CONTAR.SI). Un ejemplo en VBA haciendo referencia a la base de datos en otro libro sería así:
contar = Application.WorksheetFunction.CountIfs(Workbooks(«MI_OTRO_LIBRO.xls»).Sheets(«Hoja1»).Range(«A:A»), ThisWorkbook.Sheets(«Hoja1»).Range(«A2»), Workbooks(«MI_OTRO_LIBRO.xls»).Sheets(«Hoja1»).Range(«B:B»), «>20»)
Saludos.
Hola buenas.
Yo busco una macro que cuente coincidencias de nuemros, pero por filas de 6 celdas.
Ejemplo: datos B2:G20000 Resultado en I
Gracias.
Un Saludo
La función contar.si trabaja con rangos, por lo que en la macro con solo modificar el rango donde se cuentan los criterios, debería hacer lo que indicas: por ejemplo Range(«A1:E» & final aquí seleccionas desde la columna A a la E.
Saludos.
eso ya lo intente,pero lo que da es por columnas y no correctamente lo que pido que es por filas
El resultado esperado seria en hoja RESULTADO OK.
Se repiten 2 veces 3 números resultado a mano 14,17,42/23, 27,38 y 23,27,36
Una imagen más que mil palabras
https://www.dropbox.com/s/52l2b9lrinbn7yt/REPETICIONES%20POR%20FILA%20DE%20VALORES%20para%20foro%202.xlsx?dl=0
Un Saludo.
Hola Eugenio:
Lo que indicas requiere un desarrollo mediante programación. En estos momentos tengo varias consultas que estoy programando, cuando las finalice intento ponerme con ello.
Saludos.
Buenas tardes,
La fórmula me ha ayudado muchísimo! Tengo una consulta, si por ejemplo tengo una dos datos duplicados en la columna «A» con esta macro, lo que me aparece en la celda de la derecha, es decir en la columna «B», es el número total de veces que este dato se repite. Mi consulta es si al igual que con la función contar.si, quede el primer registro de ese dato, lo cuente como «1» y el segundo como «2».
Para explicarme mejor, si el valor de la celda A1 y A2 son iguales, utilizando la macro, en la celda B1 saldía 2, y en la celda B2 también saldría 2, por el número de veces que se ha contado; sin enbargo usando solo la fórmula sin macro, me figura en la celda B1 el valor de 1 y en la celda B2 el valor de 2, quisiera saber si con esta magnífica macro se puede lograr ese detalle, ya que para mi es importante que cada fila y registro tenga un número independiente, y si se repite que el siguiente registro salga con2, luego con 3 y luego con 4 en caso de que en el rango se repita 4 veces ese mismo registro.
No sé si me dejé explicar, pero me auidaría muchísimo.
Muchas Gracias!!!
Hola Martínez:
Eso lo puedes realizar de dos maneras:
Desde el mismo excel del post, en la columna D pon esta fórmula en la celda D2 (previamente debes ordenar la columna A)
=SI(A2=A1;D1+1;1)
También lo puedes hacer con CONTAR.SI, en la columna E y en la celda E2 por esta fórmula
=CONTAR.SI($A$2:A2;A2)
Aquí no es necesario ordenar.
Saludos.
Hola, Segu, gracias por responder!
Estuve intentando e intentando y bueno, siento no expresarme bien, lo que pasa es que yo lo necesitaba en macro, y de tanto intentar, lo conseguí de la siguiente forma:
************************
Sub ContarSi()
Dim Lastrow As Long
Application.ScreenUpdating = False
Lastrow = Range(«D» & Rows.Count).End(xlUp).Row
Range(«E2»).formula = «=COUNTIF(RC[-1]:R[70000]C[-1],RC[-1])»
Range(«E2»).AutoFill Destination:=Range(«E2:E» & Lastrow)
ActiveSheet.AutoFilterMode = False
Columns(«E»).AutoFilter Field:=1, Criteria1:=»#DIV/0!»
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True
End Sub
************************
Según la macro, la columna E se rellenaría o con «2» o con «1» ya que los registros que existen en la columna D, se pueden repetir máximo dos veces.
Sabiendo que mi excel es una base que puede aumentar o disminuir en filas, quería saber si con esa macro que coloco arriba, no habría ningún tipo de problemas. Mi base de datos es la compra de productos para un stock, al cual se le añade estatus (en la columna G) correspondiente a los movimientos que se realicen de un determinado producto.
Otra consulta, Segu, no sé si me podrías ayudar u orientarme. Tengo que hallar la forma que se eliminen filas con condiciones en dos columnas, mi conocimiento en excel es básico, y he visto tutoriales como el tuyo, en donde se elimina filas según la condición de una sola columna, o si es en varias columnas, es porque las celdas tienen el mismo valor, o son vacías o son 0. Lo que yo quiero, es que en una columna, la celda tenga un valor y en la otra columna, tenga otro valor estipulado para recién eliminar, y si se cumple la condición solo en una columna, no eliminarla, ya que debe cumplirse en ambas. Además, si se elimina una fila, que la que viene inmediatamente después, se elimine
Esto es masomenos lo que quiero:
A D E G
1 18/01/2019 15 2 P
2 17/03/2019 15 1 R
3 20/03/2016 20 2 H
4 02/05/2019 20 1 R
5 03/10/2008 21 1 R
6 20/10/2019 25 1 H
Columna A = fechas de compra
Columna D = código de un producto en específico
Columna E = se llena con la macro ContarSi teniendo la columna D como guía
Columna G = estatus de compra
*Las columnas que no figuran están ocultas.
**Lo que yo hice antes de ejecutar la macro ContarSi es hacer orden personalizado; como primer nivel que me ordene el código del producto de menor a mayor y en segundo nivel, que la fecha sea de la más antigüa a la más reciente.
>>Lo que necesito es crear una macro que identifique el número 2 en la columna E, y a su vez en la columna G, identifique si el valor es diferente a «P» o a «E», para recién eliminar esa fila y la fila siguiente. En caso que en en la columna E sea 2, pero en la columna G, el estatus sea «P» o sea «E», no eliminar nada, y asu vez si en la columna G, el valor es diferente a «P» o a «E», pero en la columna E el valor es 1, no se elimine nada.
Es decir, al ejecutar la macro debe quedar así:
A D E G
1 18/01/2019 15 2 P
2 17/03/2019 15 1 R
5 03/10/2008 21 1 R
6 20/10/2019 25 1 H
Se eliminan las filas 3 y 4, la 3 por la condición de ambas columnas, y la 4 porque era la columana siguiente a la que fue eliminada.
Yo creo que al programar la macro con la condición, debería pedirle que primero elimine la columna siguiente y luego la columna que cumpla la condición, o me sugieres otro método?
En serio muchas gracias si pudieses guiarme o solo decirme que tutorial debo ver para entender mejor, me ayudarías muchísimo.
Que tengas un excelente día, y gracias por crear tutoriales así y subirlos! No sabes la gran ayuda que me has dado!
Hola martinez:
Te he mandado un correo, revísalo.
Saludos.
Hola Segu!
Muchas gracias, revisé el correo y te respondí.
Que tengas un excelente día.
Saludos
Hola, Segu, podras ayudarme? No se que fórmula aplicar para conseguir arrojar en estas celdas
el total de ABARROTES, y en la otra celda el total de MASCOTAS:
https://i.ibb.co/c3YBx48/excel.jpg
no lo consigo con función y con macro no tengo ni idea.
Deberías utilizar Contar.si o contar.si.conjunto. Saludosl
Hola, si quiero utilizar una macro de sumar.si.conjunto pero los datos estan en la hoja 2 y el resultado lo quiero en la hoja uno.. como lo aplicaria??
Hola Graciela:
Tendría que ser algo así, en VBA (obviamente).
Hoja1.range(«A1»)= Application.WorksheetFunction.SumIfs(Hoja2.Range(“a1:a5000”), .Hoja2.Range(“b1:b5000”), «TUCRITERIO”)
Saludos.
Buenas, consulta: tengo una tabla en la cual necesito buscar, en una columna donde solo hay números, cuantas filas contienen un número (no es necesariamente el numero completo). Vendría a ser «*» & texto & «*» pero de números, estoy programando con vba en Excel y usando WorksheetFunction.CountIf . Gracias!
Hola Equi:
Hoy me han preguntado una consulta similar en otro post.
Si estás programando, y quieres contar las «filas», o más bien las celdas, de un columna. Debes usar una instrucción for each para recorrer la columna y luego contar la aparición del número.
Así sería una forma de hacerlo.
Copia la macro, selecciona el rango de datos en la columna, y en la celda (1,3) lo que es lo mismo «C1» indica el número a buscar. te devolverá el número de celdas en las que se repite.
Saludos,
Sub Macro1()With Selection
For Each celda In Selection
contar = 0
On Error Resume Next
If celda <> Empty Then contar = IIf(IsNumeric((Application.WorksheetFunction.Search(Cells(1, 3), celda))), 1, 0)
On Error GoTo 0
cont = cont + contar
Next celda
End With
End Sub
Buen día
Un saludo a la distancia, quisiera saber si tienes alguna macro para extraer la información de un comentario y copiarlo en una celda específica.
Hola Marco:
Tu consulta no tiene nada que ver con el post de referencia. En tu pregunta a qué te refieres, a un comentario donde, en una web? a una comentario en una celda?, saludos
Buen día Segu
El post me orientó para realizar un informe diario. De esta manera pude gracias automatizar las cuentas diarias en un reporte de Excel.
A lo que me refiero es si tienes alguna macro para extraer la información del comentario de una celda de Excel pegarlo en una celda específica.
Ejemplo en una celda de Excel tengo las matrículas de varios vehículos. En los comentarios se encuentra la descripción de la Tripulación. Necesito extraer la tripulación del comentario para integrarlo a una celda en un informe posterior.
He buscado en muchos post de Excel pero no logro dar con la macro.
Si puedes ayudar con el tema.
Saludos a la distancia
Con esta macro puedes extraer los comentarios de las celdas que están en la columna A.
Los comentarios se pasan a la columna B. La columna A debe tener datos, sino debes especificarlo en el rango.
Saluds
Sub Nota()
Dim i As Long, celda As Object, Nota As String
With Sheets(«Hoja1»)
fin = Application.CountA(.Range(«A:A»))
For Each celda In .Range(«A1:A» & fin)
On Error Resume Next
Nota = celda.Comment.Text
On Error GoTo 0
If Nota <> vbNullString Then
celda.Offset(0, 2) = Nota
End If
Nota = vbNullString
Next celda
End With
End Sub