Вытащить данные из таблицы Excel можно формулами: значение по ключу находит =ВПР(E2;A2:C100;3;0) или =ИНДЕКС(C2:C100;ПОИСКПОЗ(E2;A2:A100;0)), все строки по условию в Excel 365 выводит =ФИЛЬТР(A2:C100;B2:B100="Москва"), а символы, слова, фразы и атрибуты ячеек из текста извлекают процедуры !SEMTools из раздела ИЗВЛЕЧЬ. Ниже — какие задачи решает каждый подраздел.
Как вытащить данные из таблицы формулами
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Город | Менеджер | Сумма | Город | Строки Москвы | |
| 2 | Москва | Анна | 1200 | Москва | Анна=ФИЛЬТР(B2:B4;A2:A4=E2) | |
| 3 | Казань | Олег | 800 | Иван | ||
| 4 | Москва | Иван | 500 |
ФИЛЬТР в F2 вывел всех менеджеров, у которых в столбце A стоит город из E2, и сам заполнил F3. Поменяйте E2 на «Казань» — список обновится. В Excel 2019 и раньше вместо ФИЛЬТР используйте автофильтр или ВПР для первого совпадения.
В английском Excel: =FILTER(B2:B4,A2:A4=E2)
Если нужно одно значение по ключу (например, сумму по менеджеру), подойдёт ВПР или связка ИНДЕКС и ПОИСКПОЗ, которая умеет искать и влево. А если нужно вытащить часть текста из ячеек — символы, слова, числа, — переходите в подразделы ниже.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Менеджер | Город | Сумма | Кого ищем | Сумма | Город | |
| 2 | Анна | Москва | 1200 | Олег | 800=ВПР(E2;A2:C4;3;0) | Казань=ИНДЕКС(B2:B4;ПОИСКПОЗ(E2;A2:A4;0)) | |
| 3 | Олег | Казань | 800 | ||||
| 4 | Иван | Москва | 500 |
ВПР ищет ключ в первом столбце и возвращает столбец с указанным номером. ИНДЕКС+ПОИСКПОЗ не зависит от порядка столбцов и умеет искать влево. 0 (или ЛОЖЬ) — точное совпадение.
В английском Excel: =VLOOKUP(E2,A2:C4,3,0)
При работе с данными часто требуется выбрать из таблицы или столбца только такие, которые соответствуют определенному критерию. При этом данными могут быть самые разные объекты - как значения ячеек целиком, так и определенные фрагменты ячеек - слова или символы.
Иногда и вовсе, требуются не сами данные, а некоторые их атрибуты. В зависимости от объекта - ячейка сама по себе, её значение, слова и символы внутри этого значения имеют свои характеристики, которые также можно вывести напротив.
Эта страница сориентирует и направит вас на нужную статью, в зависимости от вашей задачи.
Вместе с этим ИЗВЛЕЧЬ - это еще и раздел моей надстройки для Excel, которая позволяет выбирать и извлекать данные по множеству критериев, равно как находить, удалять и трансформировать их.
Извлечь определенные символы
Узнайте, как извлечь символы из диапазонов ячеек по множеству их признаков.
| A | B | C | |
|---|---|---|---|
| 1 | Имя | Домен | |
| 2 | ivan@mail.ru | ivan=ЛЕВСИМВ(A2;ПОИСК("@";A2)-1) | mail.ru=ПСТР(A2;ПОИСК("@";A2)+1;100) |
| 3 | sales@semtools.guru | sales=ЛЕВСИМВ(A3;ПОИСК("@";A3)-1) | semtools.guru=ПСТР(A3;ПОИСК("@";A3)+1;100) |
ПОИСК находит позицию «@», ЛЕВСИМВ берёт всё до неё, ПСТР — всё после. Больше формул — в разделе извлечь символы.
В английском Excel: =LEFT(A2,SEARCH("@",A2)-1)
Извлечь слова
MS Excel ничего не знает о словах и в нём подобных процедур нет.
При этом для человека это одна из весомых структурных единиц текста. !SEMTools дает набор инструментов по извлечению слов из ячеек.
| A | B | C | |
|---|---|---|---|
| 1 | Текст | Первое слово | Последнее слово |
| 2 | купить угловой диван | купить=ЛЕВСИМВ(A2;ПОИСК(" ";A2&" ")-1) | диван=ТЕКСТПОСЛЕ(A2;" ";-1;;;A2) |
| 3 | кресло | кресло=ЛЕВСИМВ(A3;ПОИСК(" ";A3&" ")-1) | кресло=ТЕКСТПОСЛЕ(A3;" ";-1;;;A3) |
Пробел, приписанный к тексту, спасает ячейки из одного слова от ошибки. Другие формулы — в разделе извлечь слова.
В английском Excel: =LEFT(A2,SEARCH(" ",A2&" ")-1)
Извлечь фразы
Ситуация с фразами аналогична словам.
- Извлечь топонимы — с предлогами перед ними и без;
- Свой список фраз.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Запрос | Найденная фраза | Список | |
| 2 | доставка в нижний новгород | Нижний Новгород=ЕСЛИОШИБКА(ПРОСМОТР(2;1/ЕЧИСЛО(ПОИСК($D$2:$D$3;A2));$D$2:$D$3);"") | Нижний Новгород | |
| 3 | отели санкт-петербург центр | Санкт-Петербург=ЕСЛИОШИБКА(ПРОСМОТР(2;1/ЕЧИСЛО(ПОИСК($D$2:$D$3;A3));$D$2:$D$3);"") | Санкт-Петербург | |
| 4 | доставка по Москве | =ЕСЛИОШИБКА(ПРОСМОТР(2;1/ЕЧИСЛО(ПОИСК($D$2:$D$3;A4));$D$2:$D$3);"") |
ПРОСМОТР(2;1/…) возвращает последнюю фразу списка, найденную в тексте. Регистр не важен: в результат попадает написание из списка.
В английском Excel: =IFERROR(LOOKUP(2,1/ISNUMBER(SEARCH($D$2:$D$3,A2)),$D$2:$D$3),"")
Извлечь ячейки
Раздел подробно рассматривает процедуры фильтрации ячеек на основе их значений.
Процедуры меню !SEMTools «Извлечь ячейки» работают следующим образом: содержимое ячеек остается нетронутым, но в диапазоне остаются только ячейки, соответствующие заданным критериям. Ячейки, не соответствующие критериям, становятся пустыми. Они эквивалентны таким же для слов, но все сравнения производятся со всем содержимым ячейки.
| A | B | C | |
|---|---|---|---|
| 1 | Товар | Только с «диван» | |
| 2 | диван угловой | диван угловой=ФИЛЬТР(A2:A5;ЕЧИСЛО(ПОИСК("диван";A2:A5))) | |
| 3 | кресло | диван прямой | |
| 4 | диван прямой | ||
| 5 | стол |
ФИЛЬТР выводит только подходящие ячейки в отдельный диапазон; исходный столбец не меняется. Функция есть в Excel 365 и 2021.
В английском Excel: =FILTER(A2:A5,ISNUMBER(SEARCH("диван",A2:A5)))
Извлечь атрибуты ячеек и шрифта
Здесь речь как раз о тех ситуациях, когда из имеющихся данных извлекаются совсем новые.
Атрибуты есть у самой ячейки, её шрифта и текста в ней. Их вполне можно использовать как критерий выбора данных.
Критериями для самой ячейки будут:
- ширина и высота;
- наличие заливки и её цвет;
- наличие границ и их цвет.
Для шрифта ячейки характерны:
- название (Arial, Verdana, Comic Sans...);
- размер;
- начертание (жирный, курсив, подчеркнутый...);
- цвет.
Для текста ячейки наиболее значимые атрибуты:
- длина текста в пикселях;
- количество символов;
- количество слов.
| A | B | C | |
|---|---|---|---|
| 1 | Текст | Символов | Слов |
| 2 | купить угловой диван | 20=ДЛСТР(A2) | 3=ДЛСТР(СЖПРОБЕЛЫ(A2))-ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A2);" ";""))+1 |
| 3 | кресло | 6=ДЛСТР(A3) | 1=ДЛСТР(СЖПРОБЕЛЫ(A3))-ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A3);" ";""))+1 |
Слов на одно больше, чем пробелов между ними. СЖПРОБЕЛЫ убирает лишние пробелы, чтобы они не исказили подсчёт. Для пустой ячейки формула слов вернёт 1 — добавьте проверку ЕСЛИ(A2="";0;…).
В английском Excel: =LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1
Посчитать повторения и доли
Статьи этого раздела подробно описывают способы извлечения статистической информации о содержимом диапазона, такой, как:
- подсчёт доли значения от общей суммы (для числовых значений);
- подсчёт доли накопительным итогом;
- количества повторений элемента в выделенном диапазоне;
- номера элемента по его встречаемости.
- АВС-анализ (для числовых значений)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Товар | Выручка | Доля | Нарастающая доля |
| 2 | Чай | 500 | 50%=B2/СУММ($B$2:$B$4) | 50%=СУММ($B$2:B2)/СУММ($B$2:$B$4) |
| 3 | Кофе | 300 | 30%=B3/СУММ($B$2:$B$4) | 80%=СУММ($B$2:B3)/СУММ($B$2:$B$4) |
| 4 | Сок | 200 | 20%=B4/СУММ($B$2:$B$4) | 100%=СУММ($B$2:B4)/СУММ($B$2:$B$4) |
Ячейкам с результатом задан процентный формат. В нарастающей доле начало диапазона закреплено ($B$2), а конец сдвигается при протягивании. Подробнее — нарастающий итог и доли.
В английском Excel: =SUM($B$2:B2)/SUM($B$2:$B$4)
Извлечение данных в Excel — частые вопросы
Как вытащить данные из таблицы по условию?
В Excel 365 — функцией ФИЛЬТР, в старых версиях — автофильтром или расширенным фильтром с копированием результата в другое место.
Как найти значение в таблице по ключу?
Функцией ВПР или ИНДЕКС + ПОИСКПОЗ: =ИНДЕКС(C2:C100;ПОИСКПОЗ(E2;A2:A100;0)).
Как извлечь из ячейки часть текста?
Смотрите разделы извлечь символы и извлечь слова.
Как извлечь числа из текста?
Подробно — в статье извлечь числа из текста.
Извлечь символы, слова, фразы, ячейки по условию, атрибуты шрифта и статистику из любого диапазона можно в пару кликов с !SEMTools.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: как извлечь данные в Excel
Надеюсь, на этой странице вы нашли то, что искали. Строки и значения из таблицы достают ФИЛЬТР, ВПР и ИНДЕКС + ПОИСКПОЗ, а части текста и атрибуты ячеек — инструменты подразделов выше.