Как в Google Sheets узнать, есть ли подстрока в заданном диапазоне ячеек? 

Как можно отфильтровать лист_Б, чтобы оставить только "сирот" – значения, которые не имеют соответствующих пар в листе_А? В листе_А ячейки могут содержать несколько значений, например: 
<pre><code>A B C
F
D E</code></pre>
А лист_Б содержит строго одно значение в каждой ячейке, например: 
<pre><code>B
C
X
W
F</code></pre>
Так как функции вроде <code>MATCH</code> и <code>VLOOKUP</code> работают только с точными совпадениями или требуют, чтобы лист_А был отсортирован, а мой не отсортирован, какие альтернативные методы можно использовать для этой задачи? Например, <code>FIND</code> или <code>SEARCH</code> не позволяют искать во всем наборе ячеек, а только внутри одной конкретной ячейки.
  • 3 января 2025 г. 19:03
Ответы на вопрос 2 
Чтобы отфильтровать значения на листе Б, чтобы оставить только «сироты», то есть значения, которые не имеют соответствующих пар в листе А, можно использовать формулы в Google Sheets.

1. **Подготовка данных**: Убедитесь, что у вас есть два листа: **лист_А** и **лист_Б**.

2. **Создание вспомогательного столбца**: На листе Б вы можете создать вспомогательный столбец, который будет проверять, содержится ли каждое значение из листа Б в диапазоне на листе А.

3. **Использование формул**:
   - Используйте формулу `ARRAYFORMULA` вместе с `ISERROR` и `SEARCH` для проверки каждого значения из листа Б.
   
   Предположим, что данные на листе Б начинаются с ячейки A1, а на листе А данные находятся в диапазоне A1:A3. Вы можете ввести следующую формулу в столбце B (например, в B1) листа Б:

   ```plaintext
   =ARRAYFORMULA(IF(A1:A = "", "", IF(ISERROR(SEARCH(A1:A, JOIN(" ", лист_А!A1:A))), "Сирота", "Есть пара")))
   ```

   Эта формула выполняет следующие действия:
   - `JOIN(" ", лист_А!A1:A)` объединяет все значения из листа А в одну строку, разделяя их пробелами.
   - `SEARCH(A1:A, JOIN(...))` ищет каждое значение из листа Б в объединенной строке.
   - `ISERROR` проверяет, не возникла ли ошибка, что означает, что значение не было найдено.
   - В результате вы получите либо "Сирота", либо "Есть пара" в столбце B в зависимости от наличия соответствия.

4. **Фильтрация данных**: Теперь вы можете отфильтровать столбец B по значению "Сирота", чтобы увидеть только те значения, которые не имеют соответствующих пар в листе А.

5. **Использование фильтрации**: Вы можете использовать встроенные функции фильтрации Google Sheets, чтобы скрыть или выделить строки, которые содержат "Сирота".

Это простой и эффективный способ найти «сирот» в ваших данных, используя возможности Google Sheets.
Делайте пример в таблице с доступом на редактирование
Похожие вопросы
    Показать ещё