Pregunta ¿Por qué mi función COUNTIF de Excel siempre devuelve un valor de "0"?


Tengo un libro de trabajo de Excel en el cual cada celda en la columna B contiene una fábula corta (historia). Estas celdas (que tienen el formato "General") contienen valores de texto que son grandes según los estándares de la hoja de cálculo: todos menos uno supera el límite de 255 caracteres para la visualización de texto, una celda contiene más de 3.000 caracteres, y supongo que la mayoría tienen entre 500 y 700 caracteres. La segunda ilustración a continuación muestra dicha celda.

En otro libro de trabajo, tengo (meta) datos sobre las fábulas. En particular, estoy interesado en la frecuencia de ocurrencia de algunas palabras. En la ilustración inmediatamente debajo, La columna C contiene una lista de las palabras que me interesan. La columna B contiene el total número de apariciones de la palabra correspondiente (contando múltiples ocurrencias dentro de una fábula); es irrelevante para esta pregunta. También quiero contar el número de fábulas en las que aparece cada palabra (al menos una vez). Yo llamo a esto "Dispersión" (ver la explicación en el comentario a continuación). he estado usando Ctrl+F ("Encontrar y reemplazar") y haciendo clic en "Buscar todo", que informa un recuento de las celdas que contienen la cadena de búsqueda (ver la segunda ilustración a continuación). Hice esto uno a la vez, por cada palabra, e ingresó manualmente los números en la Columna N.


(Probablemente puedas omitir este párrafo). Necesito los recuentos para instancias de palabras completas, y no formas derivadas de palabras raíz (ni siquiera plurales). Por ejemplo, mi recuento para "animal" necesita devolver recuentos solo por "animal", no por "animales" o cualquier otra variación de ese tipo. Al principio, me di cuenta de que simplemente buscaba una palabra podría llevar a un recuento falso porque incluiría palabras que contenido la palabra que estaba buscando Lo solucioné rellenando mis términos de búsqueda con espacios al principio y al final, en la Columna E (por ejemplo, "animal"), que contiene =" "&C2&" " - y asegurar que la columna contra la cual estoy verificando estas palabras también aísle tales palabras. Dondequiera que un signo de puntuación cayera junto a la última o la primera letra de una palabra en una fábula, inserté un espacio para eliminar dicha adyacencia. Por ejemplo, "todo esto, porque siendo" se convirtió en "todo esto, porque siendo". (Esto fue parcialmente inspirado por JNevill's comentar sobre esta pregunta: El conteo de Excel no funciona.) Mi Ctrl+F luego, las búsquedas devuelven recuentos para instancias individuales de las palabras que busqué.


Por supuesto, esto es tedioso, lento y propenso a errores, así que me pregunté si alguna fórmula no podría hacer lo mismo, sino más rápido. Un par de publicaciones / páginas web sugirieron que COUNTIF La función podría ser capaz de lograr esto, así que he estado experimentando con él, pero hasta ahora, mis intentos han fallado cada vez. En la ilustración de arriba, la columna M contiene

=COUNTIF('[OTHER WORKBOOK.xlsx]SHEET'!$B:$B,E2)

y como puede ver, muestra un "0" cuando debería coincidir con el número en la Columna N (es decir, el conteo que se encontró usando Ctrl+F) (Como se indicó anteriormente, la columna E contiene el cuerda Busco, Cuál es el palabra Busco, acolchado con espacios al principio y al final.) Cada configuración usando el COUNTIF la función que he intentado ha fallado.

A continuación se muestra una imagen de la celda B23 en mi hoja de trabajo de fábulas (es decir, una fábula). La vista está parcialmente obstruida por un cuadro de diálogo "Buscar y reemplazar", mostrando que 13 celdas (fábulas) contienen la palabra "animal" (es decir, contienen la cadena "animal", con espacios iniciales y finales) al menos una vez.

(Es posible que necesite Agrande su vista de la imagen para ver esto claramente.)

Lo último que voy a mostrarte es lo que sucede cuando uso el COUNTIF función. Creo que puede ser la clave de por qué no puedo hacer que esto funcione, porque tan pronto como agregue el rango de la columna necesaria, incluso antes de presionar Entrar, Ya veo #VALUE!; #VALUE!; … al lado del rango. Se parece a esto:

Cuando presiono Entrar, Obtengo el "0" que ves para las celdas en esa columna.

¿Qué estoy haciendo mal? ¿Cómo puedo contar la cantidad de fábulas en las que aparece cada palabra? (al menos una vez)?

Si no puede ver por las capturas de pantalla, estoy usando Excel 2007.


1


origen


Solo una suposición, pero el criterio de conteo cuenta las coincidencias exactas en el nivel de celda. No funciona como una función "contiene". La búsqueda funciona porque opera sobre la base de "contiene". Por otro lado, col N no coincide con el coll B. Si eso es importante y ya lo describí, lo eché de menos porque la pregunta es bastante larga. - fixer1234
Su pregunta podría beneficiarse de más explicación. No sé a qué te refieres con "Dispersión", y no veo ningún lugar que describa concisamente lo que intentas hacer. (Creo que lo he reconstruido). - Scott
@ fixer1234 Tienes un ojo agudo. Tienes razón, N no coincide B. B puede contener más de N o unirlo pero no puede excederlo. Cada celda de la hoja de cálculo que tengo contiene una fábula y es por eso que estoy tratando de obtener un buen recuento. B representa el número total de veces que aparece la palabra. Algunas palabras aparecen más de una vez en una fábula. - Lisa Beck
@Scott Realmente sentí que los detalles más minuciosos fueron un poco demasiado profundos para la mayoría, pero si mis nombres cortos de columna son realmente de interés y crees que me serían útiles, realmente estoy tratando de obtener la frecuencia y la amplitud de las palabras en una conjunto de documentos. La frecuencia es solo eso: la cantidad de veces que la palabra aparece en un conjunto de documentos; amplitud se refiere a cuántos de los documentos aparece la palabra. Uso "dispersión" en lugar de "amplitud" simplemente porque "dispersión" me parece una palabra más precisa que "amplitud". ¿Eso ayuda? - Lisa Beck
He encontrado una analogía que puede ayudar a otros a entender esto mejor y quizás usar cualquier fórmula (s) que se genere a partir de esta publicación para otros fines. Digamos que desea examinar qué tan seguros están los controladores en varias áreas metropolitanas. Naturalmente, le gustaría recopilar datos no solo en el frecuencia accidentes de tráfico (el número total) pero también el dispersión o amplitud de tales accidentes - en cuántas calles diferentes / en cuántas intersecciones diferentes, etc. - Lisa Beck


Respuestas:


@ fixer1234 tiene razón - COUNTIF cuenta las celdas que están igual a un valor, no células que Contiene una cuerda. Para eso, necesitas usar FIND o SEARCH. (Son idénticos, excepto FIND es sensible a mayúsculas y SEARCH es insensible a mayúsculas y minúsculas Asumiré que quieres el que no distingue entre mayúsculas y minúsculas).

Comience haciendo

=SEARCH(E2, '[OTHER WORKBOOK.xlsx]SHEET'!B1)

Esto buscará el valor de E2 (en tu ejemplo, "animal") en la celdaB1 de la otra hoja de trabajo. Si ese valor de cadena está presente en esa celda, esto devolverá la ubicación de la primera aparición de la cadena de búsqueda en el texto de la celda (con el primer personaje siendo 1). Si la cadena no está presente, regresará #VALUE!.

Siguiente, hacer

=IF(ISERROR(SEARCH(E$2, '[OTHER WORKBOOK.xlsx]SHEET'!B1)), 0, 1)

Esto evaluará a 1 si la cadena está presente y 0 si no lo está. El siguiente paso es:

=SUM(IF(ISERROR(SEARCH(E2, '[OTHER WORKBOOK.xlsx]SHEET'!$B:$B)), 0, 1))

Esto suma la fórmula anterior a lo largo de la columnaB de la otra hoja de trabajo, dándole la cuenta que desea. Tenga en cuenta que lo anterior es un fórmula de matriz. Esto significa que, para que funcione, debes escribir Ctrl+Cambio+Entrar después de escribir la fórmula. Ahora puedes poner esto en la celdaM2 y arrastra hacia abajo.

Realmente no necesitas tener una columnaE - puedes manejarlo dentro de tu SEARCH fórmula:

=SUM(IF(ISERROR(SEARCH(" "&C2&" ", '[OTHER WORKBOOK.xlsx]SHEET'!$B:$B)), 0, 1))

Probé esto en Excel 2013, pero he hecho cosas como esta antes, y espero que esta solución funcione en Excel 2007. (Y probé con células con más de 750 caracteres, y con un nombre de archivo de libro que contiene un espacio).

PD No sé por qué los tienes #VALUE! errores en el cuadro de diálogo "Argumentos de funciones"; funcionó para mí:

“Function Arguments” dialog

(Lo probé aunque mi respuesta no usa COUNTIF.) ¿Tiene abierto el otro libro mientras hace esto?


2



Realmente deseando experimentar con tu sugerencia. Antes de hacerlo, abordaré un par de cosas muy rápido aquí. Para empezar, sí, su suposición de que necesitaría usar una búsqueda insensible a las mayúsculas y minúsculas era correcta. Y para abordar la pregunta que planteaste al final, sí, tengo el otro libro abierto cuando intento experimentar con varias fórmulas. Antes de irme a experimentar con los suyos, gracias por tomarse el tiempo para probar esto tan exhaustivamente y así responder claramente esto. Incluso si la fórmula no acaba de funcionar para mí, h / t para usted por la calidad de su respuesta. - Lisa Beck
Admitiré que era un poco escéptico de que esto funcionaría después de que completara el primer paso, pero mantuve la fe y lo llevé a cabo. No he hecho uno de esos controles tan minuciosos y escrupulosos, pero una comprobación rápida e inmediata indica que su fórmula funcionó como un amuleto. Absolutamente brillante, @Scott. Es fácil darle la marca de verificación para esto. - Lisa Beck
Me alegro de poder ayudar. - Scott


Tengo problemas para ver tus capturas de pantalla, pero como intentas contar el resultado de otras funciones, intentaré usar la función value () dentro de tu instrucción countif. Value () le pedirá a Excel que mire el resultado (lo que ve en las celdas) en lugar de la función subyacente que usted ha escrito en ellos.

Su declaración de countif se parecería a "= countif (valor (rango de [otra hoja de cálculo]), criterios)"


0



Una respuesta interesante, aunque algo contradictoria con lo que otros han afirmado que son limitaciones de la función CONTABILIDAD. Si estoy malinterpretando algo aquí, no se ofenda. No estoy tratando de ser grosero ... simplemente expreso una observación. Aprecio el hecho de que haya intentado responder a esto y si mi experimentación con la sugerencia de Scott no funciona, definitivamente le daré una oportunidad al suyo. Diablos, incluso puedo experimentar con los tuyos incluso si funciona. Gracias de nuevo por sugerirlo. - Lisa Beck
Admitiré que no intenté con demasiadas variaciones de su fórmula para ver si podía hacer que funcionara, pero el único mensaje que recibí consistentemente después de intentarlo fue que tenía demasiados argumentos en la fórmula. Aunque su sugerencia no funcionó para mí, gracias por hacer el esfuerzo de ayudarme con esto. - Lisa Beck


Si la cadena larga está en B2, y la palabra que está buscando está en C2, la siguiente fórmula da el recuento de cuántas veces la palabra está en la cadena

  • = + IFERROR ((LEN (B2) -LEN (SUSTITUTO (B2, C2, ""))) / LEN (C2), "")

Las partes son

  1. Iferror (opcional) lo deja en blanco si hay un error
  2. Len (b2) longitud de la celda original
  3. LEN (SUSTITUTO (B2, C2, "") borra todas las apariciones del patrón. Esto será más corto si está en los datos.
  4. La longitud de Len (c2) del patrón si tiene 6 caracteres de largo y hay 3 ocurrencias luego 18/6 resultará en un 3

0





Puede usar COUNTIF () para contar si una cadena contiene o no otra explotando el uso de comodines, tal como lo proporciona Microsoft. El asterisco (*) coincide con cero o más caracteres, mientras que el signo de interrogación (?) Coincide con un solo carácter.

Entonces, en lugar de =COUNTIF('[OTHER WORKBOOK.xlsx]SHEET'!$B:$B,E2) utilizar =COUNTIF('[OTHER WORKBOOK.xlsx]SHEET'!$B:$B,"*"&E2&"*").

Los asteriscos permiten cualquier otro texto antes y después de su cadena. Como su cadena ya tiene espacios iniciales y finales, no debería haber problemas con los plurales y otras formas de palabras.


0