Función Contar. SI. Conjunto., que se copie en otra hoja
fejoal
Por favor su ayuda,
Tengo una hoja con unos datos y una fórmula:
=CONTAR.SI.CONJUNTO(B10:ZZ10;(">480100000000&<48019999999"))En la hoja se copia siempre en la fila "B" datos continuamente; el problema que tengo es que cada vez que copia datos en la columna B, la formula comienza a contar desde la siguiente fila, es decir, C10, D10 y así sucesivamente con esto no cumple el objetivo que necesito.
Como puedo hacer para que siempre comience a contar desde la Fila "B10" a pesar que copie información de forma seguida.
1 respuesta
.06.02.17
Buenos días, Oscar
Para que considere siempre el mismo rango, independientemente de la celda donde copias la fórmula, utiliza el signo $ para fijar filas/columnas.
Es decir, tu fórmula debería ser como la siguiente:
=CONTAR.SI.CONJUNTO($B$10:$ZZ$10;(">480100000000&<48019999999"))
$ Colocado antes de las letras hace que no varíen las columnas al copiar la fórmula a otra celda, mientras que, antes de los números, mantiene fijo el número de fila.
.
Hola Fernando,
Gracias por tu ayuda, hice lo que me indicaste en la respuesta; pero sigo con el mismo inconveniente. Comienza a contar después de la ultima fila. Como podría hacer para que esto no me ocurra.
Muchas gracias.
Saludos,
Oscar
.
Buenas, Oscar
Puede ser que no te esté entendiendo correctamente, pero con lo sugerido no debería modificar el rango que considera para contar.
De todos modos, me parece que tienes un par de problemas en la definición del criterio de esa función.
Por concepto, la función CONTAR. SI. CONJUNTO, permite contar de acuerdo a más de un criterio.
En tu fórmula anterior, sólo veo uno. Además el segundo valor tiene una cifra menos que la primera
Prueba con esta variante para ver si te funciona correctamente:
=CONTAR.SI.CONJUNTO($B$10:$ZZ$10;">480100000000";$B$10:$ZZ$10;"<480199999999")
Como ves, aquí operan las dos condiciones: contar en el rango B10:ZZ10 los valores mayores que 480100000000 y menores que 480199999999. (Nota que agregué un 9 al final).
Tal vez por ello no obtenías el resultado esperado.
Ojalá que esto resuelva tu inconveniente.
Un saludo
Fer
.
Hola Fernando,
Tienes razón me faltaba un "9" al final, pero a pesar de eso sigo teniendo el mismo error. Cada vez que se copia nueva información comienza a contar una fila después.
Tal vez hay otra formula que pueda usar o como podría solucionar este inconveniente.
Gracias nuevamente,
Saludos,
Oscar
.
Hola, Oscar
¿Podrías subir una imagen de tu planilla? Sigo sin entender por qué cuenta una fila después. Hasta donde veo esa fórmula sólo cuenta en la fila 10.
Saludos
Fer
.
Hola Fernando,
Adjunto te envió la imagen. En esta hoja siempre se pega una información en la Columna B, por lo tanto tendría que iniciar a contar desde la celda "B10" en adelante. El problema es que cada vez que se copia información. Es decir que cada vez que automaticmaente se crea la Fila B para pegar la nueva información, en la fórmula cambia desde donde inicia la cuenta.
POr ejemplo en la imagen después de pegar varias veces la información comienza a contar desde la columna F.
Muchas gracias por tu ayuda,
Saludos,
Oscar
.
Ok, Oscar
Me parece entender. Pero cuando dices "... se crea la Fila B..." creo que quieres decir "... se crea la Columna B..."
Entonces puedes probar iniciando el rango en la columna A para que la fórmula no desplace el origen a medida que insertas columnas.
Prueba entonces con esta variante:
=CONTAR.SI.CONJUNTO($A$10:$ZZ$10;">480100000000";$A$10:$ZZ$10;"<480199999999")
Entonces la fórmula contará desde la columna A que no será un valor, por lo tanto no la tendrá en cuenta.
Creo que esto resolverá el problema.
Abrazo
Fer
.
Hola Fer,
Muchas gracias por tu ayuda. Eso soluciono mi problema.
Una consulta más, ahora comienza a contar siempre desde la columna A y con eso no tengo problemas, podrías ayudarme ya que siempre que se copia la información la celda B10 que es la que va a contar se queda con formato "General" y no lo cuenta, cuando lo cambio de forma manual a numero comienza a contar. COmo puedo hacer para que siempre lo cuente o que lo cambie de forma automática a formato "Numero".
Gracias de antemano.
Un abrazo,
Oscar
.
Hola, Oscar
Las funciones de xxxxxx. SI. CONJUNTO aparecieron en versiones recientes de MS Excel. Antes no existían como tales.
De todos, los viejos usuarios contabamos (y aún funcionan) con una herramienta poco conocida, llamada Formula Matricial. Esta, además de operar con varias condiciones permiten hacer transformaciones "en el aire" antes de arrojar el resultado deseado.
Entonces, si aquellos valores de la fila 10 vienen como "textos", la siguiente fórmula los convertirá a valores y los comparará con los límites que le diste para determinar si debe contarlos o no.
Para ello, ingresa la siguiente fórmula, pero NO DES ENTER AUN:
=SUMA(SI(ESNUMERO($A$10:$ZZ$10);SI(VALOR($A$10:$ZZ$10)>480100000000;SI(VALOR($A$10:$ZZ$10)<480199999999;1))))
Luego presiona conjuntamente: Control+Shift+Enter
Estas teclas hay que presionar simultáneamente después de tipear la fórmula (no basta con Enter). Un par de llaves {} se agregarán automáticamente, encerrando la fórmula. Esto es FUNDAMENTAL para que funcione.
Esta formula evalúa primero que las celdas a considerar tengan un valor, luego que ese rango convertido a valor sea mayor que 480100000000 y que, de esa selección, sean menores que 480199999999. Cumplidas esas tres condiciones asigna un 1 y finalmente los suma.
Un poco más sofisticada, pero hace todo lo que quieres en un único paso.
Otro abrazo
Fer
.
Hola Fer,
Intente lo que me indicaste pero a pesar de eso no logro que cuente, ya que el texto que se pega viene con formato "General" y por esa razón no logra contarlo.
Tal vez si desde que lo copia siempre lo cambia de formato podría ser una opción?, o tal vez existe otra opción?
Podrías tu ayudarme modificando la macro que copia y pega la información.
De antemano muchas gracias.
Un abrazo
Oscar
'Copiar informacion de Reporte a Bitacora
Sub Copiar_adjuntos()
Application.ScreenUpdating = False
Set l1 = ThisWorkbook
Ruta = "C:\Users\z003bpca\Desktop\Bitacora\"
arch = "copy_Reporte.xls"
If Dir(Ruta & arch) = "" Then
MsgBox "El archivo Reporte no existe en la ruta", vbCritical
Exit Sub
End If
'
Set l2 = Workbooks.Open(Ruta & arch)
Set h2 = l2.Sheets("Sheet0")
Num = h2.Range("D5").Text
If Num = "" Then
MsgBox "La celda D5 no contiene datos", vbExclamation
l2.Close False
Exit Sub
End If
If IsNumeric(Num) Then
Num = "" & Val(Num)
End If
'
existe = False
For Each h In l1.Sheets
If h.Name = Num Then
existe = True
Set h1 = h
Exit For
End If
Next
'
If existe = False Then
l1.Sheets.Add after:=l1.Sheets(l1.Sheets.Count)
Set h1 = l1.ActiveSheet
'copia de columna A de Hoja Datos
Sheets("Datos").Visible = True
Sheets("Datos").Columns("A").Copy h1.Columns("A")
'Sheets("Datos").Visible = False
h1.Name = Num
End If
'
'uc = h1.Cells(1, Columns.Count).End(xlToLeft).Column + 1
'If uc < Columns("B").Column Then uc = Columns("B").Column
'h2.Range("O42:O104").Copy h1.Cells(1, uc)
h1.Columns("B").Insert
h2.Range("O42:O104").Copy h1.Cells(8, "B")
'ajusta columnas de B en adelante a 30
h1.Columns.ColumnWidth = 30
h1.Columns("A:A").EntireColumn.AutoFit
l2.Close False
l1.Save
Application.ScreenUpdating = True
MsgBox "Copia realizada", vbInformation
End Sub
.
Buenas noches, Oscar
El formato de la celda no afecta al contenido de ella. Al asignar cualquiera que elijas, sólo cambias la apariencia, pero dentro de ella sigue estando o un texto con aspecto de número o un número real.
Por ello, la fórmula convierte texto o número a su valor. Es extraño que no obtengas el contéo correcto.
De todos modos, puedes intentar reemplazando esta parte de aquel código:
... If IsNumeric(Num) Then Num = "" & Val(Num) End If ...
con esta otra:
If IsNumeric(Num) Then Num = Val(Num) End If
De esta manera no será un texto sino un número natural.
Un abrazo
Fer
.
Hola Fernando,
¿Cómo vas?; Te cuento que realice todo lo que me has sugerido pero lamentablemente no puedo lograr que realice la cuenta de lo que necesito.
Estuve investigando pero aun no logro encontrar otra forma para hacerlo. De igual forma muchas gracias por tu ayuda.
Un abrazo,
Oscar
Hola Fernando,
Espero que te encuentres bien, por este problema que he tenido para realizar que cuente la información que necesito aun no he logrado encontrar la solución a mi inconveniente.
Seria posible que tu me ayudes para que cada vez que se copie la información desde el otro documento se sume un "0" a todas las celdas.
Muchas gracias.
Un abrazo
Oscar
'Copiar informacion de Reporte a Bitacora
Sub Copiar_adjuntos()
Application.ScreenUpdating = False
Set l1 = ThisWorkbook
Ruta = "C:\Users\z003bpca\Desktop\Bitacora\"
arch = "copy_Reporte.xls"
If Dir(Ruta & arch) = "" Then
MsgBox "El archivo Reporte no existe en la ruta", vbCritical
Exit Sub
End If
'
Set l2 = Workbooks.Open(Ruta & arch)
Set h2 = l2.Sheets("Sheet0")
Num = h2.Range("D5").Text
If Num = "" Then
MsgBox "La celda D5 no contiene datos", vbExclamation
l2.Close False
Exit Sub
End If
If IsNumeric(Num) Then
Num = "" & Val(Num)
End If
'
existe = False
For Each h In l1.Sheets
If h.Name = Num Then
existe = True
Set h1 = h
Exit For
End If
Next
'
If existe = False Then
l1.Sheets.Add after:=l1.Sheets(l1.Sheets.Count)
Set h1 = l1.ActiveSheet
'copia de columna A de Hoja Datos
Sheets("Datos").Visible = True
Sheets("Datos").Columns("A").Copy h1.Columns("A")
'Sheets("Datos").Visible = False
h1.Name = Num
End If
'
'uc = h1.Cells(1, Columns.Count).End(xlToLeft).Column + 1
'If uc < Columns("B").Column Then uc = Columns("B").Column
'h2.Range("O42:O104").Copy h1.Cells(1, uc)
h1.Columns("B").Insert
h2.Range("O42:O104").Copy h1.Cells(8, "B")
'ajusta columnas de B en adelante a 30
h1.Columns.ColumnWidth = 30
h1.Columns("A:A").EntireColumn.AutoFit
l2.Close False
l1.Save
Application.ScreenUpdating = True
MsgBox "Copia realizada", vbInformation
End Sub
.
Hola, Oscar
He reproducido tu ejemplo en mi equipo y modifiqué la fórmula como sigue, logrando que cuente correctamente:
=SUMA(SI(ESNUMERO(VALOR($A$10:$ZZ$10));SI(VALOR($A$10:$ZZ$10)>480200000000;SI(VALOR($A$10:$ZZ$10)<480299999999;1))))
Recuerda ingresarla con Ctrl + Shift + Enter.
De esta manera, entiendo que no necesitarás modificar la rutina que trae los datos.
Saludos
Fer
.
¡Gracias!
Hola Fernando,
Muy valiosa tu ayuda como siempre, funciona a la perfección.
Necesito tu ayuda en un teme similar en el mismo documento, te puedo realizar otra pregunta?
Un abrazo.
Oscar
.
Me alegro, Oscar
Por supuesto, puedes enviarme otra pregunta, si el tema es distinto al de esta pregunta, para ordenar las búsquedas.
Abrazo
Fer
.
