Как мне извлечь список всех ячеек, содержащих текстовую строку?

Вопрос или проблема

Предположим, у меня есть этот список:

A
B

1
Stack East
500

2
Stack West
600

3
Whack One
700

4
Track Yellow
500

5
Stack North
500

6
Stack North
700

Stack North находится в ячейке A; 500 в ячейке B.
Я хочу новый лист, который будет показывать, например, только ячейки, содержащие слово “stack”. И тогда я хочу значения из столбца B, например, справа от “Stack”. Это для списка дебиторов из одной группы, но разных местоположений, и я хочу обобщить извлечение из бухгалтерского программного обеспечения в Excel, чтобы отправить его.
Я пытался использовать индекс+совпадение, но это действительно не работает, поскольку, вероятно, неясно, какое значение поместить в ячейку, и я не могу протянуть вниз. Если я скопирую список и использую найти+заменить, он не сможет автоматически обновляться в будущем, когда я просто скопирую новый извлечение из следующего месяца или так.
Какие-нибудь идеи?

С Excel 2016 и более поздними версиями вы можете использовать PowerQuery. В таблице сделайте «вставить/таблица». Это объявит массив как таблицу, что делает его легко доступным из PowerQuery (в таблицах половина строк закрашена).
Затем «Данные/из таблицы». Вы войдете в редактор PowerQuery. С помощью выпадающего меню в одном из столбцов вы можете создать фильтр. После проверки фильтр запоминается и отображается на правой панели. Предварительный просмотр актуализирован.
Теперь нажмите на иконку в левом верхнем углу «закрыть и загрузить». Результат запроса добавляется на новом листе. Если вы измените исходные данные, перейдите на лист с результатом, щелкните правой кнопкой «актуализировать», и запомненный запрос будет вычислен снова.

Предполагая, что нет ограничений по версии согласно вашим тегам, вы можете использовать функцию FILTER:
=FILTER(myTab,LEFT(myTab[A],5)=”Stack”)

Ответ или решение

Чтобы извлечь список всех ячеек, содержащих текстовую строку «Stack», и соответствующие значения из соседнего столбца в Excel, можно воспользоваться несколькими подходами, включая использование Power Query или функции FILTER.

Использование Power Query

Power Query – это мощный инструмент для обработки и анализа данных в Excel, который позволяет автоматизировать извлечение и фильтрацию данных. Ниже представлено пошаговое руководство:

1. Создание таблицы:

   • Выделите диапазон данных (например, ячейки A1:B6).
   • Перейдите на вкладку «Вставка» и выберите «Таблица». Убедитесь, что флажок «Таблица с заголовками» отмечен.
   • Нажмите «ОК» для создания таблицы.

2. Импорт данных в Power Query:

   • Перейдите на вкладку «Данные» и выберите «Получить данные» → «Из таблицы/диапазона».
   • Это откроет редактор Power Query.

3. Фильтрация данных:

   • В редакторе выберите столбец с названиями (например, Column1).
   • Нажмите на стрелку вниз в заголовке столбца, чтобы открыть меню фильтрации.
   • Установите фильтр, выбрав «Содержит» и введя «Stack».
   • После настройки фильтра нажмите «ОК». Вы увидите отфильтрованные данные.

4. Закрытие и загрузка:

   • Нажмите «Закрыть и загрузить» в левом верхнем углу.
   • Это создаст новую вкладку с отфильтрованными результатами.

5. Обновление данных:

   • Если вы хотите обновить данные в будущем, просто скопируйте новые данные в исходную таблицу и на вкладке с результатами щелкните правой кнопкой мыши и выберите «Обновить».

Использование функции FILTER

Если у вас версия Excel, поддерживающая динамические массивы (Excel 2019 или Office 365), вы можете использовать функцию FILTER для извлечения данных:

1. Определите диапазон данных:

   • Предположим, что ваша таблица называется myTab, где myTab[A] – это столбец с названиями, а myTab[B] – это соответствующие значения.

2. Используйте функцию FILTER:

   • В пустой ячейке введите следующую формулу:

     =FILTER(myTab, ISNUMBER(SEARCH("Stack", myTab[A])), "Нет результатов")

   • Эта формула извлечет все строки из myTab, где в столбце A содержится текст «Stack», и вернет соответствующие значения из столбца B.

Заключение

Оба предложенных метода предоставляют эффективные способы фильтрации данных по заданному критерию. Power Query особенно полезен, если вам нужно регулярно обновлять данные, так как он сохраняет ваши настройки фильтрации. Функция FILTER является отличным выбором для пользователей, предпочитающих динамический подход. Выберите тот метод, который лучше соответствует вашим потребностям и удобству работы с данными в Excel.

Если у вас есть дополнительные вопросы или требуется дальнейшая помощь, пожалуйста, дайте знать!