Посчитайте, сколько раз строка длины N появляется в ячейке

Question

У меня есть ячейка, которая содержит номера заказа, разделенные запятой. (т.е. 1689519145, 2944241, 1689611938).

Я пытаюсь получить формулу для подсчета, сколько раз в ячейке появляется 7-значный порядковый номер, если он присутствует.

Мои исследования привели меня к этой формуле, которая подсчитывает, сколько раз когда-то появляется между разделителями, но я не могу понять, как разобрать ТОЛЬКО 7-значные порядковые числа: =LEN(P9)-LEN(SUBSTITUTE(P9,",",""))+1

Answer 1

Если вы считаете все семь цифр, вы можете использовать что-то вроде этого

{=SUM(IF((1*TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1)))=1),99)))>999999,IF((1*TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1)))=1),99)))<10000000,1)))}

который считает все числа между 999 999 и 10 000 000.

В качестве альтернативы, если вы хотите посчитать, сколько раз появляется конкретное число, вы можете адаптировать формулу к чему-то вроде этого

=SUM(IF((1*TRIM(MID(SUBSTITUTE(A3,",",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(A3)-LEN(SUBSTITUTE(A3,",",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(A3)-LEN(SUBSTITUTE(A3,",",""))+1)))=1),99)))=2944241,1))

где вы также можете заменить конкретные 7-значные числа как (2944241 в данном случае) на ссылку.

Ключи, чтобы заставить формулу работать:

1. Убедитесь, что экземпляры ROW(OFFSET($A$1 остаются ROW(OFFSET($A$1 или хотя бы в первой строке)
2. Введите формулу в виде массива (весь ctl+shift+enter)

Как работает формула:

1. SUBSTITUTE(A1,",",REPT(" ",99) проходит и разбивает каждое число на 99 пробелов
2. LEN(A3)-LEN(SUBSTITUTE(A3,",","")) подсчитывает, сколько предметов
3. ROW(OFFSET($A$1,, создает массив с таким количеством элементов
4. первый массив умножается на 99
5. один добавляется к первому элементу массива
6. Затем MID вырезает массив столько раз, сколько элементов, каждый раз вырезая новый элемент
7. TRIM удаляет лишние пробелы
8. массив умножается на единицу, чтобы превратить их из строк в числа
9. SUM подсчитывает, сколько из массива соответствует критериям IF

Кредит достается Рону Розенфельду за большую часть тяжелой работы

Answer 2

Мне кажется, что вы хотите узнать, сколько раз в целевой ячейке появляется точное число из семи цифр, а не сколько чисел любой длины и цифр в ячейке. (Это также может быть обозначено как количество "элементов" в целевой ячейке, как можно было бы сказать в "элементах массива".)

И из формулы, которую вы получили в результате вашего исследования, я полагаю, что вы видите ответы на оба вопроса и не понимаете, что они различаются, поэтому правильные формулы также должны отличаться, а затем использовать неправильную для ваших нужд.

Во-первых, сколько там элементов? Каждый разделен запятой, поэтому есть много запятых, плюс один (потому что последний элемент не имеет запятой после него, поэтому вам нужно добавить еще одну). Таким образом, если вы заменяете запятую () "вообще ничего" ("" в мире Excel) для каждой запятой, длина целевой строки будет меньше на количество запятых в исходной строке. Ваша формула делает это. Но вы неправильно делаете «+1». Учитывая пару ненужных () пар вокруг функции SUBSTITUTE (), я думаю, что вы знали, что нужно поместить в них «+1», но просто неправильно набрали здесь. Если вы оставите это снаружи, вы должны вычесть его («-1»). Вычтите более короткую длину И 1 из первоначальной длины, и вы получите, сколько элементов.

Но ... если вам действительно нужно знать, сколько раз появляется точная семизначная строка, именно это и ничего более, тогда вам нужно использовать ту же концепцию, но немного по-другому. Начните с использования SUBSTITUTE(), чтобы заменить "ничто" на строку, которую вы хотите сосчитать. Вычтите это из первоначальной длины. Наконец, разделите эту разницу на длину строки, о которой вы хотели знать: семь цифр здесь, может быть, какая-то другая длина, какая-то другая, в другом месте и в другом месте. Так:

= ( LEN(P9) - SUBSTITUTE(P9,"1234567","") ) / 7

Конечно, если у вас есть строка в другой ячейке, вы можете поместить ее в формулу вместо моего "1234567" и сделать вашу формулу более полезной!

Если вы не хотите знать, сколько ровно семизначных, не более, не менее длинных чисел в P9, вы можете использовать функции массива, чтобы посмотреть на каждый элемент по длине и сосчитать только те, которые состоят из семи цифр. Это довольно сложно, и если вам нужно добавить поиск букв и игнорирование этих элементов, это больше так. И ... ну, вы поняли.

Answer 3

Если вы хотите сделать это с помощью Len()/Substitute(), то

=(LEN(A2)+2-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" "&A2&",","0","|"),"1","|"),"2","|"),"3","|"),"4","|"),"5","|"),"6","|"),"7","|"),"8","|"),"9","|")," |||||||,","")))/LEN(" |||||||,")

Вместо | Вы можете поместить любой другой символ, которого нет в строке.

Answer 4

Выполните следующие действия, чтобы подсчитать, сколько раз в ячейке появляется 7-значный номер заказа.

Я предполагаю, что у вас есть данные в диапазоне A2:A4 .

1. Применить текст к столбцу « Команда в диапазоне данных», чтобы разделить числа.

2. Запишите эту формулу в ячейку D2 и заполните.

      =SUMPRODUCT(--(LEN(TRIM(A2:C2))=7))
   

При необходимости измените ссылки на ячейки в формуле.