Многие люди испытывают трудности с извлечением информации из сложных ячеек в Microsoft Excel. Мы рассмотрели три особых случая и здесь объясняем формулы, использованные для извлечения данных из электронной таблицы
Многие люди испытывают трудности с извлечением информации из сложных ячеек в Microsoft Excel. Многочисленные комментарии и вопросы в ответ на мою статью ‘Как извлечь число или текст из Excel с помощью этой функции’ доказывают это. Видимо, не всегда понятно, как выделить нужные данные из листа Excel
Мы выбрали несколько вопросов из этой статьи и рассмотрели их здесь, чтобы вы могли увидеть, как работают решения. Быстро освоить Excel нелегко, но использование реальных задач, подобных этим, очень помогает
1. Извлечение с использованием разделителя
Читатель Адри задал следующий вопрос:
Я хотел бы извлечь первый набор чисел из списка, т.е.(122,90,84,118. 4,128. 9)
Есть идеи, какую формулу я могу использовать?
COIL112X2. 5
COIL90X2. 5
COIL84X2. 0
COIL118. 4X1. 8
COIL128. 9X2. 0
Тот факт, что извлекаемые числа имеют разную длину, делает эту задачу немного сложнее, чем просто использование функции MID, но, комбинируя несколько различных функций, мы можем избавиться от букв слева, а также от ‘X’ и чисел справа, оставив только нужные числа в новой ячейке
Вот формула, которую мы будем использовать для решения этой задачи:
=VALUE(LEFT((RIGHT(A1,(LEN(A1)-4))),FIND('X',A1)-5))
Давайте начнем с середины и посмотрим, как это работает. Сначала мы начнем с функции FIND. В данном случае мы используем FIND(‘X’,A1). Эта функция просматривает текст в ячейке A1 в поисках буквы X. Когда она находит X, она возвращает позицию, в которой она находится. Например, для первой записи, COIL112X2. 5, она возвращает 8. Для второй записи возвращается 7
Далее рассмотрим функцию LEN. Это просто возвращает длину строки в ячейке минус 4. Комбинируя это с функцией RIGHT, мы получаем строку из ячейки минус первые четыре символа, что удаляет ‘COIL’ из начала каждой ячейки. Формула для этой части выглядит следующим образом: RIGHT(A1,(LEN(A1)-4))
Следующий уровень – функция LEFT. Теперь, когда мы определили положение X с помощью функции FIND и избавились от ‘COIL’ с помощью функции RIGHT, функция LEFT возвращает то, что осталось. Давайте разложим это еще немного подробнее. Вот что произойдет, если мы упростим эти два аргумента:
=LEFT('112X2. 5', (8-5))
Функция LEFT возвращает три крайних левых символа строки (символ ‘-5’ в конце аргумента обеспечивает удаление нужного количества символов, учитывая первые четыре символа в строке)
Наконец, функция VALUE гарантирует, что возвращаемое число будет отформатировано как число, а не как текст. Этот пример не так увлекателен, как создание работающей игры ‘Тетрис’ в Excel, но он служит отличным примером того, как можно объединить несколько функций для решения проблемы
2. Извлечение чисел из смешанных строк
Похожий вопрос задал читатель Ядху Нандан:
Я хочу знать, как разделить числовые и буквенные символы
Пример – 4552dfsdg6652sdfsdfd5654
Я хочу, чтобы 4552 6652 5654 были в разных ячейках
Другой читатель, Tim K SW, предложил идеальное решение этой конкретной проблемы:
Предположим, что 4552dfsdg6652sdfsdfd5654 находится в A1, и вы хотите, чтобы эти числа были извлечены в 3 разные ячейки. 4452 в ячейку B1, 6652 в C1 и 5654 в D1, то вы используете следующее
B1:
=MID(A1,1,4)
C1:
=MID(A1,10,4)
D1:
=MID(A1,21,4)
Формулы довольно просты, но если вы не знакомы с функцией MID, вы можете не понять, как она работает.MID принимает три аргумента: ячейку, номер символа, с которого начинается результат, и количество символов, которые нужно вытянуть.MID(A1,10,4), например, говорит Excel взять четыре символа, начиная с десятого символа в строке
Использование трех различных функций MID в трех ячейках позволяет извлечь числа из этой длинной строки цифр и букв. Очевидно, что этот метод работает только в том случае, если в каждой ячейке всегда одинаковое количество символов – букв и цифр. Если количество каждого из них меняется, вам понадобится гораздо более сложная формула
3. Получение числа из смешанной строки с пробелами
Одним из самых сложных запросов, размещенных в статье, был этот, от читателя Дэрила:
Привет, я просто хочу спросить, как отделить очень неформатированную запись, например:
Rp. 487. 500 (нетто 50% OFF)
256. 500 рупий нетто 40% OFF
99. 000 рупий
51. 000 рупий/оранг нетто (скидка 50%)
Я потратил некоторое время, работая над этим вопросом, и не смог придумать решение самостоятельно, поэтому обратился на Reddit. Пользователь UnretiredGymnast предоставил эту формулу, длинную и очень сложную:
=SUMPRODUCT(MID(0&LEFT(A1,IFERROR(SEARCH('%',A1)-4,LEN(A1))),LARGE(INDEX(ISNUMBER(--MID(LEFT(A1,IFERROR(SEARCH('%',A1)-4,LEN(A1))),ROW($1:$99),1))*ROW($1:$99),0),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)
Как видите, расшифровка этой формулы занимает довольно много времени и требует довольно глубоких знаний многих функций Excel. Чтобы понять, что именно здесь происходит, нам потребуется рассмотреть несколько функций, с которыми вы, возможно, не знакомы. Первая из них – IFERROR, которая ловит ошибку (обычно представленную знаком фунта, например #N/A или #DIV/0) и заменяет ее на что-то другое. В примере выше вы увидите IFERROR(SEARCH(‘%’,A1)-4,LEN(A1)). Давайте разберем это
IFERROR просматривает первый аргумент, который является SEARCH(‘%’,A1)-4. Таким образом, если ‘%’ появляется в ячейке A1, возвращается его местоположение и из него вычитается четыре. Если ‘%’ не появляется в строке, Excel выдает ошибку, и вместо него возвращается длина A1
Другие функции, с которыми вы, возможно, не знакомы, немного проще; например, SUMPRODUCT умножает, а затем складывает элементы массивов.LARGE возвращает наибольшее число в диапазоне.ROW в сочетании со знаком доллара возвращает абсолютную ссылку на строку
Увидеть, как все эти функции работают вместе, нелегко, но если вы начнете с середины формулы и будете работать по направлению наружу, вы начнете понимать, что она делает. Это займет некоторое время, но если вы хотите увидеть, как именно она работает, я рекомендую поместить ее в лист Excel и поиграть с ней. Это лучший способ получить представление о том, с чем вы работаете
(Кроме того, лучшим способом избежать подобной проблемы является более аккуратный импорт данных – это не всегда возможно, но это должно быть вашим первым выбором, когда это возможно)
Один шаг за раз
Как видите, лучший способ решения любой проблемы Excel – это один шаг за раз: начните с того, что вы знаете, посмотрите, что это вам даст, и двигайтесь дальше. Иногда в итоге вы получите элегантное решение, а иногда – что-то очень длинное, запутанное и сложное. Но пока это работает, вы преуспели!
И не забывайте просить о помощи. Есть очень талантливые пользователи Excel, и их помощь может оказаться неоценимой при решении сложной задачи
Есть ли у вас какие-либо предложения по решению сложных проблем с извлечением данных? Знаете ли вы какие-либо другие решения проблем, которые мы рассмотрели выше? Поделитесь ими и любыми своими мыслями в комментариях ниже!
Комментировать