%3A.png)
У меня есть 2 листа Excel. На одном из них отмечены транзакции со столбцом различных ежемесячных банковских/кредитных операций. Другой лист — это таблица сопоставления, состоящая из 2 столбцов, первый — это подтекст (строка?) транзакций, перечисленных на листе транзакций, а второй столбец — идентификатор транзакции. Мне нужна формула на листе транзакций, с помощью которой любая транзакция, перечисленная со строкой (подтекстом), возвращала бы классификацию на листе сопоставления. Есть ли для этого формула без написания кода, которая применялась бы к нескольким транзакциям, т. е. ко всем столбцам?
решение1
Формула поиска с SEARCHи в обертке IFERRORрешает проблему:
Как это работает:
- Это похоже на частичное совпадение в столбце.
Формула в ячейке
L31:=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(Mapping!$M$31:$M$39,K31),Mapping!$N$31:$N$39),"")
Примечание.
9.99999999999999E+307, рассматривается как наибольшее число (длина), которое может быть в любом столбце, и используется для возвратаRow Numberискомого значения в диапазоне ИНДЕКСА, а также помогает формуле находить и возвращать значение.Функция
SEARCHвозвращает расположение одной текстовой строки внутри другой.При необходимости вам необходимо скорректировать имя листа и ссылки на ячейки в формуле.
решение2
Да, это хорошо проясняет ситуацию.
При существующем положении дел вы не можете с уверенностью сделать то, что хотите.
Основная проблема заключается в том, как связать что-то вроде "TOYOTA CREDIT RLS" с "LEXUS FINANCIAL RLS"? Конечно, это МОЖНО было бы сделать с помощью длинных и сложных формул, но тогда в игру вступает слово "надежно". И после этого рассмотрения эти данные кажутся грузом дерьмовых данных, которые кредитные карты предлагают для загрузки, с идеей, что можно было бы легко потом прочесать данные за много месяцев, и почему они соответствуют этому в соседней строке, а не этому другому, находящемуся на пару строк дальше или на 263 строки дальше?
Второй лист можно было бы назвать листом сопоставления, но если так, то это не потому, что он выполняет какое-либо сопоставление. И он на самом деле не позволяет сопоставление. Было бы удобно иметь структуру, в которой первый лист, возможно, такой, какой он есть, а второй лист имеет ТРИ столбца: столбец с ТОЧНЫМ материалом в первом столбце первого листа, затем два текущих столбца. Идея заключается в том, чтобы добавить данные первого листа, посмотреть, есть ли какие-либо ошибки, и действовать соответственно. Ошибки? Да, у вас будет столбец подстановок, куда попадают коды (подстановки будут показывать, как эти коды попали на первый лист).
Если бы возникли какие-либо ошибки, это означало бы, что элементы этих строк отсутствуют в списке на листе два. Поэтому вы бы добавили их прямо сейчас, заполнив для них второй и третий столбцы на листе два. Как только ошибки будут устранены, все будет в порядке. Ошибок нет? Означает, что ничего нового нет, и вы можете двигаться дальше после добавления данных. (Формула поиска заполнит столбец кода за вас.)
Ловушка? Убедитесь, что таблица, на которую ссылаются в поиске, никогда не меньше, чем полный объем материала на листе два! Напишите так, чтобы включить несколько десятков дополнительных строк или несколько сотен. Так, когда вы вставите новый материал и заполните строку, он все еще будет в таблице. Проверяйте ее время от времени. ИЛИ, что лучше, используйте динамическую формулу ссылки, чтобы сделать таблицу всегда идеальной, всегда точной.
Однако, глядя на это, трудно увидеть необходимость в существующем столбце A на листе сопоставления. Он не кажется полезным. Но если он полезен, его не повредит сохранить.
Альтернативой было бы сделать этот столбец материалом, который более полезно связывает его со столбцом A первого листа. Это могло бы сделать прямые поиски более надежными. Если нет, ну, нет. Но если это делает поиски действительно сложными или невозможными в практическом мире, то это не кажется особенно полезным для фактического отображения чего-либо.
Длинный и сложный? Ну, один из подходов для каждого поиска может заключаться в написании вложенных FIND(), SEARCH(), или SUBSTITUTE()формул, которые берут часть записи первого листа и просматривают каждый столбец второго листа, пока не найдут совпадение. Но это огромная задача даже для максимум 20 символов во входных данных, и если "Toyota" (например) встречается в двух разных типах записей, а не только в одном типе (например, платеж за автомобиль и техническое обслуживание автомобиля, оба из которых содержат "Toyota" в своем тексте), то возникает проблема надежности, поскольку он может вернуть неправильный on.
В настоящее время мне больше всего нравится использование MID()с SEQUENCE()беря строку по одному символу за раз, или два, или шестнадцать и т. д. за раз и используя полученный текст. Наиболее полезно при использовании по одному символу за раз, но это может использовать их все для проверки частей на втором листе, все quintinliyhuge-illion возможностей... или загружать компьютер каждый раз, когда вы открываете файл...
О, поиски, о которых я говорю, были бы стандартными вещами, вероятно, приятными новыми XLOOKUP()сейчас. Все эти тесты, о которых я упоминал выше? Квинтинлийхьюдж-иллион возможностей? Каждый из них должен быть введен в выбранный поиск, так что первый успешный имел бы результат, который вы бы использовали. Так что в два раза больше операций или больше...
Итог? Похоже, что материал придется переработать.
Не имеет большого значения, если это не ваша таблица, и вы даже не загружаете данные. Вы все равно можете реструктурировать ее, пока работаете. Сохраните второй файл с таблицей, которую я описал. Добавьте в него формулы на том или ином листе, которые фактически выполняют поиск. Затем скопируйте и вставьте их результаты ( Copy|Paste|Special: Values) обратно в текущую электронную таблицу, где им и место. Вы обошли стороной идею дизайнера о совершенстве. Они никогда не узнают, так как это не меняет их электронную таблицу, и все же они получают желаемые результаты. Выигрыш-выигрыш.