☰

Создать таблицу путем условного копирования строк из другой таблицы — возможно ли это в MS-Excel?

tag-icon • tag-icon microsoft-excel microsoft-excel-2007 worksheet-function
Создать таблицу путем условного копирования строк из другой таблицы — возможно ли это в MS-Excel?

Каждую неделю CRM-система создает новый файл Excel, в имени которого указана дата, но который помещается в папку с фиксированным местоположением (URL-адрес SharePoint).

Этот файл довольно большой и обычно содержит где-то 25-30 тысяч строк. Из этого мне нужно создать новую таблицу, соответствующую моим целям.

Строки, соответствующие моей цели, должны соответствовать одному (или нескольким) ключевым словам/ключевым фразам из списка из 30-40 таких ключевых слов/ключевых фраз. Этот список ключевых слов/ключевых фраз, однако, также растет, но медленно, добавляя новые ключевые слова раз в несколько месяцев.

Есть ли способ автоматизировать эту ручную, утомительную и подверженную ошибкам задачу?

решение1

Поскольку необходимо проверить три разных столбца (предполагается, что ниже это B, Dи ), функция выглядит подходящей:FOR

=OR(ISNUMBER(MATCH(B1,keyarray,0)),
    ISNUMBER(MATCH(D1,keyarray,0)),
    ISNUMBER(MATCH(F1,keyarray,0)))

так что совпадение для любого из трех столбцов вернет TRUE. Поместите это во «вспомогательный» столбец в первой строке, занятой данными (скажем, строка 1 или отрегулируйте B1, D1и F1выше соответственно).

MATCHпроверяет, B1существует ли, например, значение в keyarray, где это заданное имя диапазона, содержащего список ключевых слов/ключевых фраз (около 30–40) — не обязательно на том же листе или в той же рабочей книге, но если нет, необходимо указать полный путь и рекомендуется открыть «другую» рабочую книгу при применении формулы к новому пакету данных.

0принудительно устанавливает только точное совпадение (или -1для наименьшего значения, которое больше или равно B1, или 1для наибольшего).

MATCHвозвращает местоположение найденного значения в массиве (в противном случае с параметром 0, #N/A). Это числовое значение, поэтому ISNUMBERпроверяется на число (любое число) – для исключения #N/Aрезультатов.

Следовательно, при условии, что любой из B1, D1или F1находится в keyarrayрезультате будет TRUE– в противном случае FALSE.

Чтобы удобно скопировать формулу вниз на 25-30 тысяч строк, где некоторые содержат объединенные ячейки, поместите что-нибудь (например, «end») на пересечении последней занятой строки и столбца «helper» (чтобы предотвратить ненужное увеличение размера электронной таблицы). Скопируйте ячейку, содержащую формулу, выберите ячейку непосредственно под ней, затем нажмите Ctrl+Shift+Down/Paste, чтобы заполнить вспомогательный столбец вниз для всех занятых строк, не продолжая за последней занятой строкой и перезаписав «end».

Фильтр по столбцу «помощник» для TRUE, выберите все занятые столбцы, скопируйте и вставьте в новый лист/книгу. Удалите пустые строки в новом листе/книге и сохраните. (Можно также выбрать удаление столбца «помощник» из источника.)

Убедитесь, что при периодическом добавлении элементов в keyarrayуказанный диапазон учитываются дополнения.

решение2

Это более масштабная задача, которую можно решить множеством способов. Но, если коротко, ДА, вы можете это автоматизировать.

Для начала:

Вы абсолютно уверены, что ваша CRM-система не может предоставить вам информацию, которую вы хотите извлечь, напрямую?

Обычно они основаны на системе баз данных, например SQL, и поскольку вы уже извлекаете из нее данные, вы можете изменить этот вывод в соответствии со своими потребностями.

Теперь о возможностях Excel:

  1. Импорт базовых данных из вашей CRM

вы можете установить подключение к данным вашей CRM

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

вы можете открыть новейший файл через VBA и скопировать нужные данные в нужный рабочий лист

...

  1. Если все еще необходимо, фильтрация данных

Я бы посоветовал вам взглянуть на фильтры и расширенные фильтры, vlookup, countifs и примеры vba-кода. Здесь и в stackoverflow есть несколько вопросов по обработке multicondition-filtering, но вам придется предоставить больше подробностей, чтобы решить это правильно.

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

Связанный контент