Общий вопрос заключается в сортировке большого списка Excel 2007 для поиска записей, соответствующих меньшему подмножеству списка.
У меня есть пара идей, как подойти к этой проблеме, но мне не хватает технической продвинутости, чтобы реализовать эти идеи. Я изложу свои конкретные требования к варианту использования, чтобы сделать вопрос более понятным.
Конкретный пример:
У меня есть основной список названий компаний, которыми я управляю на своей территории продаж (примерно 1000 клиентских счетов). Каждую неделю моя компания публикует список всех транзакций по всем территориям продаж в США (моей и сотням других территорий). Этот журнал транзакций содержит более 10 000 строк, поэтому просмотреть его на глаз и найти транзакции, связанные с моими счетами, практически невозможно.
Мое текущее неадекватное решение — выделить список моих счетов желтым цветом, скопировать этот выделенный список, затем вставить этот выделенный список в конец еженедельного журнала транзакций, затем отсортировать по алфавиту, затем вручную прокрутить до выделенных элементов. Если журнал транзакций содержит один из моих счетов, запись журнала транзакций будет прямо над или под выделенной записью, которую я вставил. Этот метод эффективен, но чрезвычайно трудоемок.
Я знаю, как удалить дубликаты в Excel. Есть ли способ удалить все, кроме дубликатов? Это облегчит визуальное сканирование списка.
Другая проблема остается, поскольку несогласованность данных ограничила использование простых макросов, фильтров или кнопки «найти дубликаты». Имена журналов транзакций часто пишутся немного иначе, чем в моем основном списке.
Пример: Acme Widget Company, Inc.; Acme Widget Inc; Acme Widget; Пример: Организация гандбола США; Организация гандбола США; Гандбол США; USHO
Я знаю, что есть некоторые сторонние приложения, которые могут использовать нечеткую логику для сопоставления неточных записей. Однако я не могу запускать плагины на своей корпоративной машине. (Если только не будет очень веских оснований...)
Есть ли макрос, который мог бы «нормализовать» журнал транзакций, исключив пробелы и знаки препинания? Есть ли макрос, который может сопоставить первые X символов (больше символов = выше точность, но больше шансов пропустить почти дублирующую запись...)? Есть ли макрос, который может вывести или отфильтровать полученный список «соответствий»?
Если эти задачи слишком сложны, у меня есть гораздо более простая идея. После объединения моего выделенного списка счетов в журнал транзакций было бы неплохо иметь возможность скрыть все остальные строки журнала транзакций, которые находятся менее чем на 5 строк выше или ниже моих выделенных элементов. Это дало бы некоторую гибкость для нестандартных написаний, но значительно упростило бы задачу визуального осмотра по списку.
Любой вклад в то, как реализовать эти идеи - или совершенно другие подходы - будет весьма признателен. Я думаю, что общий ответ на этот вопрос будет ценен для других, выходящих за рамки узкого варианта использования, который я описал.
Спасибо!
решение1
Определенно, здесь слишком много вопросов, на которые нужно ответить (как комментирует hyperslug). У меня очень похожая ситуация, и я обнаружил, что для поиска дубликатов мне просто нужно было сделать это вручную, поскольку было слишком много разнообразия для кодирования.
Все предложенные вами макросы можно написать, если вы решите, какой из них будет наиболее эффективным, то задайте его в качестве отдельного вопроса, и мы сделаем все, что сможем. Последний прост в реализации и сэкономит вам время прокрутки. Я бы создал этот макрос, а затем, после того как дубликаты будут скрыты, просто щелкните и перетащите «стандартную» запись поверх других.
решение2
Я бы использовал функцию ПОИСКПОЗ в Excel, чтобы получить нужные данные, вместо копирования и сортировки.
Допустим, ваш основной список находится вименованный диапазонназывается Master, а название компании в журнале транзакций находится в столбце D. Где-нибудь в строке транзакции введите следующую формулу: =IF(ISNA(MATCH(D1,Master,0)),0,1)и скопируйте ее во все строки в таблице транзакций. Эта формула даст результат 1, если название компании совпадает, и 0 в противном случае.
Это будет соответствовать только точным именам. Вам нужно будет добавить альтернативные имена в диапазон Master (не забудьте отсортировать его после добавления имен), чтобы получить все возможные версии.
решение3
Я согласен с подходом добавления альтернативных вариантов написания в ваш основной список (у вас может быть второй столбец, который будет показывать, какой формат является предпочтительным для почтовой рассылки и т. д., а какой просто соответствует данным компании). Вы можете добиться некоторого успеха, используя последовательные функции SUBSTITUTE для генерации альтернативной версии имен. Например
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(НИЖНИЙ(A1)," inc",""),".","")," ","")...
Таким образом, каждая подстановка заменяет любой экземпляр выбранного текста на замену — ничего в нашем случае. Из моего опыта подобных нечетких соответствий между именами из разрозненных систем, вам, возможно, придется отбросить такие вещи, как inc, corp, plc и т. д., чтобы получить совпадения. Хотя вы можете использовать SUBSTITUTE для этого, вы можете получить некоторые странные результаты, когда такие вещи, как "Income Corporation" становятся "omeorporation", поэтому, возможно, безопаснее использовать что-то вроде этого:
ЕСЛИ(ПРАВСИМВ(нижняя(A1),4)="корп",левая(нижняя(A1),длинна(A1)-4)),нижняя(A1)).
Замену пробелов делайте в последнюю очередь.
Вы можете использовать функции ПОИСКПОЗ или СЧЁТЕСЛИ с аналогичными результатами, чтобы получить столбец, показывающий, какие транзакции соответствуют вашему списку.
Альтернативой было бы использование вашего основного списка в качестве критерия для создания расширенного фильтра, что позволило бы вам очень легко сделать копию записей списка транзакций, которые соответствуют именам ваших клиентов, и разместить эту отфильтрованную копию в другом месте (например, в стороне или на другом листе). Как и в случае с вышеизложенным, вам все равно нужно будет добавить варианты, которые слишком далеки от вашего исходного имени.
решение4
Просто интересно, пробовали ли вы использовать сводную таблицу. Я обрабатываю много данных с помощью PT, и они помогают мне очень быстро и с полной целостностью данных рассматривать проблемы несколькими способами.
Выделите все данные и выберите «Вставить сводную таблицу». Теперь вы сможете просматривать свои данные множеством интерактивных способов, которые позволят вам сузить любые надоедливые двойные записи, опечатки и т. д. Затем вы можете сортировать с помощью пользовательских сортировок и т. д., а также AZ.