Evolcom.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как подсчитать частичное совпадение строки / подстроки в Excel?

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

У меня есть лист Excel с подстроками в каждой ячейке, разделенными символом #. Например:

ABC
F #G #H #I #J #K #LM # N # O # PA # B # C # D #E
F #G #H #IN # O # PA # B # C ## E

Как найти 5 наиболее часто встречающихся подстрок в диапазоне ячеек?

Это ожидаемый результат для выборки данных:

ПодстрокаСчет
А2
Б2
C2
E2
F2

Всего 2 ответа

Если у вас есть доступ к функции Microsoft365 LET(), это можно сделать при помощи комбинации функций:

enter image description here

Объяснения :

L ET() позволяет использовать повторно используемые переменные в формуле. Мы назвали массив X и присвоили ему ряд значений. Массив вытягивается:

  • "<t><s>"&SUBSTITUTE(TEXTJOIN("#",,A1:C2),"#","</s><s>")&"</s></t>" — используется для создания допустимой XML-строки и;
  • "//s[.!=&#039&#039]" — действительный xpath для получения всех непустых строк.
  • TRIM() удалит все начальные и конечные пробелы. SORT() затем сортирует массив по возрастанию.

Если вы хотите узнать больше о механизмах «нарезки» строки на элементы с помощью функции FILTERXML(), вы можете прочитать здесь.

Предполагая, что теперь у нас есть переменная, мы можем использовать ее в третьем параметре функции LET():

Используя INDEX(), вы можете «вырезать» строки/столбцы массива, где

  • SORT(CHOOSE(<1,2>,UNIQUE(X),MMULT(—(UNIQUE(X)=TRANSPOSE(X)),SEQUENCE(COUNTA(X). 0))),2,-1) — немного сложная конструкция, но идея здесь в том, что CHOOSE( <1,2>допускает 2D-массив, в котором 1-й столбец заполнен значениями UNIQUE() из «X»; а второй — это количество каждого уникального элемента в общем массиве. Подсчет выполняется с использованием MMULT() , где каждый уникальный элемент сравнивается с TRANSPOSE() «d» X ». Затем 2d-массив сортируется по 2-му столбцу.

Учитывая вышесказанное, мы можем просто использовать функцию INDEX() для получения 5 нужных нам строк (и двух столбцов) с помощью функции

Вы можете использовать недопустимый ADODB.Recordset для хранения номера каждой подстроки. Затем отсортируйте набор записей по номеру в порядке убывания, извлеките только первые 5 строк таблицы и вставьте таблицу в другой лист Excel.

Преимущества использования набора реестров :

Тривиальная сортировка — установите свойство Black (черный).

Когда данные не нужно сортировать (например, когда мне нужно подсчитать количество подстрок), я бы использовал сценарии. Вместо набора записей лучше использовать словарь.

Простое преобразование в массив с использованием GetRows method, которое затем можно использовать для заполнения Excel Range object с помощью его &#039Value property.

В идеале нужно использовать метод Excel Range.CopyFromRecordset, который не требует транспонирования; но я не нашел способа ограничить количество записей в наборе записей или создать дубликат, содержащий только указанное количество записей.

Добавьте ссылку ( Инструменты — > Ссылки .) на библиотеку Microsoft ActiveX Data Object Library; используйте последнюю версию (обычно 6,1).

Тогда код можно записать следующим образом:

Кроме того, вы можете генерировать длинную строку из данных, используя метод GetString набора записей.

Разбор текстовых строк в EXCEL

Часто текстовая строка может содержать несколько значений. Например, адрес компании: «Москва, Тверская ул. 13», т.е. название города, улица и номер дома. Если вы хотите определить все предприятия в данном городе, вам необходимо «разбить» адрес на несколько элементов. Аналогичный подход потребуется, если вы захотите разложить имя и фамилию, артикул товара или извлечь число или дату из текстовой строки.

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

Самый простой случай — это адрес, состоящий из названия города, улицы и т.д., который импортируется в ячейку MS EXCEL из другой информационной системы. В этом случае адрес имеет определенную структуру (если элементы адреса были сохранены в отдельных полях), и в нем, скорее всего, будет (немного) опечаток. Поняв структуру, вы сможете быстро разложить адрес по колонкам. Например, адрес «Москва, Тверская улица, 13», очевидно, состоит из 3 блоков: город, улица, дом, разделенных пробелами и запятыми. Кроме того, перед названием можно встретить сокращения Y, St, h и т.д. Эту задачу можно довольно легко решить с помощью инструмента MS EXCEL Columnar Text. Как это сделать, вы можете узнать в статье Колоночный текст (мастер текста) в MS EXCEL.

Конечно, адрес не всегда четко структурирован, например, могут отсутствовать пробелы (запятые сохраняются). В этом случае вам могут помочь функции, работающие с текстовыми строками. Это функции :

L EFTIMB() в MS EXCEL позволяет получить желаемое число левых символов в строке;

— Функция MS EXCEL ПРАВЫЙ ЗНАК СИМВ() — выдает нужное количество символов справа от строки;

— Функция PSTR() в MS EXCEL — выдает полустрочную часть текста.

Эти функции позволяют обрабатывать любую строку с определенной структурой, используя их комбинации. О том, как разделить текстовые строки на столбцы в MS Excel, вы можете узнать, прочитав статью Разделение столбцов текстовых строк в MS Excel.

Перед использованием этих функций необходимо понять структуру текстовой строки, которую вы хотите разобрать. Давайте попробуем извлечь номер дома из приведенного выше адреса. Понятно, что нужно использовать функцию RETURNSIMB(), но сколько символов мы должны извлечь? Два? Номера домов в других адресах обычно состоят из 1 или 3 цифр? Номер дома можно найти с помощью поиска подстроки «e.». Это можно сделать с помощью функции ПОИСКПОЗ() (см. статью Поиск позиции n-го вхождения символа в слове в MS EXCEL). Затем необходимо вычислить количество цифр номера дома. Это делается в файле примера, ссылка на который находится внизу статьи.

Усложним ситуацию. Пусть подстрока «д.» может встречаться в адресе несколько раз, например, когда в названии населенного пункта используется сокращение «д.», то есть совпадает с префиксом номера дома. В этом случае необходимо выделить все строки с названием деревни (первые два символа, так как они являются адресом населенного пункта) и исключить их. Можно также извлечь цифры из текстовой строки в отдельные диапазоны (см. статью Извлечь число из текстовой строки в MS Excel). Но что делать, если в названии улицы есть цифры? Например, «26 Бакинских Комиссаров». Здесь начинается творческий процесс.

Не забывайте о пробелах! Для каждого пробела существуют свои символы. Иногда они печатаются двойками или тройками, что не то же самое, что один пробел. Избавиться от лишних пробелов можно с помощью функции SPLOSES() в MS Excel.

Подробнее см. в разделе «Извлечение имени файла из полного пути». С помощью MS Excel можно извлечь имя файла.

Английские буквы часто встречаются в русских текстовых строках. В MS Excel их также можно обнаружить и извлечь с помощью его функций. Подробнее см. в разделе Если в слове в Excel есть латинские буквы, цифры, ЦИФРЫ.

Все эти статьи относятся к преобразованию текстовых строк (значений).

Артикул товара

Пусть имеется список номеров элементов: 2-3657; 3-4897; ….

Как видите, статья состоит из двух числовых частей, разделенных дефисом. Кроме того, цифровые части имеют строго определенный размер: первое число состоит из 1 цифры, второе — из 4 цифр.

голоса
Рейтинг статьи
Читайте так же:
Как предотвратить ввод данных в определенные ячейки листа в Excel?
Ссылка на основную публикацию
Adblock
detector