1

У меня есть таблица Excel.  В колонке А есть несколько слов. В некоторых случаях в одной ячейке есть несколько слов, разделенных десятичными точками (точками); например, university.of.california school.house или школа . Все, что следует за первым пунктом, включая саму точку, следует игнорировать; Например, с university.of.california Калифорнии следует обращаться так, как если бы он был просто university .

В столбцах с B по R есть триграфы - группы по три буквы в каждой.  Но в этих столбцах также есть пустые ячейки.

Я хочу проверить, появляются ли триграфы в столбцах с B по R в (первом) слове в столбце A той же строки.  Например, если столбцы AF в какой-то строке содержат название university.of.california , cal , rev , sit , uni и uny , эта строка должна считаться как 2, потому что в uni появляются sit и universitycal не считается, потому что california идет после периода, rev не считается, потому что это ver в неправильном порядке, а uny не считается, потому что буквы u , n и y не встречаются вместе в university .

Я хочу, чтобы столбец U в каждой строке указывал количество триграфов в столбцах с B по R в этой строке, которые соответствуют первому слову в столбце A. Как я могу это сделать?

И какую формулу использовать в столбце T, так что это ИСТИНА (зеленым цветом), если U равно или больше 1 совпадения, найденного в этой строке, и ЛОЖЬ (не окрашена), если U равно 0 в этой строке?

Вот пример набора данных.  Как описано выше, столбцы с A по R содержат входные данные, которые будут предоставлены.  Столбцы T и U содержат результаты, которые я хочу, чтобы Excel создал из этого ввода.  В этом примере ячейка T6 истинна, потому что "ice" существует до первой точки, а U6 равна 1, потому что это единственное совпадение до первой точки, когда "hou" и "col" существуют только после первой точки, поэтому игнорируются (в красный).  На фото желтые - это правильные совпадения, которые необходимо учитывать.

+---+--------------------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+---+-------+---+
|   |         A          |  B  |  C  |  D  |  E  |  F  |  G  |  H  |  I  |  J  |  K  |  L  |  M  |  N  |  O  |  P  |  Q  |  R  | S |   T   | U |
+---+--------------------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+---+-------+---+
| 1 | university         | abd | ity | sfd | fgh | tyu | kjg | sdf | jhg | bnm | hjk |     |     |     |     |     |     |     |   | TRUE  | 1 |
| 2 | school             | bnm | sdf | hoo | tyu | kjg | sdf | jhg | ool | hjk | sdf | fgh | tyu | kjg | sch | jhg | bnm | hjk |   | TRUE  | 3 |
| 3 | college            | sdf | fgh | tyu | kjg | sdf | jhg | bnm | sdf | fgh | tyu | kjg | sdf | jhg | bnm | hjk |     |     |   | FALSE | 0 |
| 4 | home               | ome | fgh | tyu | kjg | sdf | jhg |     |     |     |     |     |     |     |     |     |     |     |   | TRUE  | 1 |
| 5 | nice.colored.house | hou | col | ice |     |     |     |     |     |     |     |     |     |     |     |     |     |     |   | TRUE  | 1 |
| 6 | super.market       | etr | etr | sdf | fsd | sdf | iuj | mar | ket |     |     |     |     |     |     |     |     |     |   | FALSE | 0 |
+---+--------------------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+   +-------+---+

Вот те же данные (возможно, включая ошибки транскрипции) с цветовым кодированием для освещения, как описано выше:

таблица

Если возможно, формула должна быть без учета регистра. Например, ooL и OOL должны учитываться как совпадения для school .

1 ответ1

2

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

Мое решение требует вспомогательной ячейки для каждой реальной ячейки, вплоть до столбца R Вы можете поместить их в столбцы AA AR в тех же строках.  Или вы можете поместить их в столбцы с A по R в строках с 11 по 16 или с 101 по 106.  Я решил поместить их в параллельные ячейки на другом листе; это облегчает последующее расширение.

Примечание. Если вы хотите иметь возможность сортировать данные позже, поместите вспомогательные ячейки на тот же лист, что и основные данные, в те же строки, но (очевидно) в разные столбцы (например, от AA до AR).

В Sheet2!A1 , введите

=IFERROR(LEFT(Sheet1!A1,SEARCH(".",Sheet1!A1)-1), Sheet1!A1)

Это извлекает значение Sheet1!A1 до первого периода (десятичная точка), если есть.  В частности, он ищет первый . в Sheet1!A1 .  Если он находит его, он использует LEFT() для извлечения текста перед ним; в противном случае он просто принимает всю стоимость.

В Sheet2!B1 , введите

=IF(AND(Sheet1!B1<>"",NOT(ISERROR(SEARCH(Sheet1!B1, $A1)))), 1, 0)

Это проверяет, если Sheet1!B1 не является пустым, и если он появляется в Sheet2!A1 (часть Sheet1!A1 до первого знака после запятой).  Если да и да, он оценивается в 1; в противном случае он оценивается в 0.

Выберите Sheet2!B1 и перетащите / заполните вправо, в столбец R Затем выберите ячейки A1:R1 и перетащите / заполните вниз в строку 6.  Вот результат:

Sheet2

Теперь все остальное легко.  В Sheet1!U1 , введите

=SUM(Sheet2!B1:R1)

который считает совпадения в строке 1.  И в Sheet1!T1 , введите

=U1>0

Выберите ячейки T1:U1 и перетащите / заполните вниз в строку 6.  И вы сделали:

Лист1

Если вы хотите покрасить ячейки, вы можете сделать это легко с помощью условного форматирования.  Если вы хотите отсортировать данные, и вы поместили вспомогательные ячейки в те же строки, что и реальные данные, затем выберите реальные данные и вспомогательные ячейки вместе (т A1:AR6) и отсортируйте весь блок.

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .