3 сложные проблемы извлечения данных в Excel решены и объяснены
Продуктивность

3 сложные проблемы извлечения данных в Excel решены и объяснены

Многие люди испытывают трудности с извлечением информации из сложных ячеек в 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, и их помощь может оказаться неоценимой при решении сложной задачи

Есть ли у вас какие-либо предложения по решению сложных проблем с извлечением данных? Знаете ли вы какие-либо другие решения проблем, которые мы рассмотрели выше? Поделитесь ими и любыми своими мыслями в комментариях ниже!

Об авторе

Алексей Белоусов

Привет, меня зовут Филипп. Я фрилансер энтузиаст . В свободное время занимаюсь переводом статей и пишу о потребительских технологиях для широкого круга изданий , не переставая питать большую страсть ко всему мобильному =)

Комментировать

Оставить комментарий