Как выделить текст в экселе

Выделение

• Выделение вправо на одну ячейку. - Shift + →
• Выделение влево на одну ячейку. - Shift + ←
• Выделить вверх на одну ячейку. - Shift + ↑
• Выделить на одну ячейку вниз. - Shift +↓
• Расширьте выделение вправо до последней ячейки. - Ctrl + Shift + →
• Расширьте выделение влево до последней ячейки. - Ctrl + Shift + ←
• Расширить выделение вверх до последней ячейки. - Ctrl + Shift + ↑
• Расширить выделение вверх на один экран. - Shift + PageUp
• Расширить выделение на один экран вниз. - Shift + PageDown
• Расширить выделение вправо на один экран. - ALT + Shift + PageUp
• Расширить выделение влево на один экран. - ALT + Shift + PageDown
• Расширить выделение до начала строки. - Shift + Home
• Расширить выделение до первой ячейки на листе. - Ctrl + Shift + Home
• Расширить выделение до последней ячейки на листе. - Ctrl + Shift + End
• Переключить режим расширения выделения. - F8

Выравнивание

• Выровняйте содержимое по центру ячейки. - Alt + H + A + C
• Выровняйте содержимое по левому краю ячейки. - Alt + H + A + L
• Выровняйте содержимое по правому краю ячейки. - Alt + H + A + R
• Выровняйте содержимое по середине ячейки. - Alt + H + A + M

Скрыть / Показать (строки, столбцы, объекты)

• Скрыть выбранные строки. - Ctrl + 9
• Скрыть выбранные столбцы. - Ctrl + 0
• Показать скрытые строки в выделении. - Ctrl + Shift + 9
• Показать скрытые столбцы в выделении. - Ctrl + Shift + 0
• Показать / скрыть объекты. - Ctrl + 6

Вставка

• Вставить новую строку в той же ячейке - Alt + Enter
• Вставить новый лист. - Shift + F11
• Вставить строку / ячейку (показывает диалоговое окно). - Ctrl + Shift + =
• Вставить текущую дату. - Ctrl + Shift + ;
• Вставить текущее время. - Ctrl + Shift + :
• Вставить таблицу. - Ctrl + Т
• Вставить гиперссылку. - Ctrl + K
• Вставьте имена аргументов в формулу. - Ctrl + Shift +A
• Вставить / изменить комментарий к ячейке. - Shift+ F2
• Удалить строку / ячейку (показывает диалоговое окно). - Ctrl + -

Форматирование

Связанные с формулами

• Вставить формулу автосуммы. - Alt + =
• При вводе формулы переключает ссылку на ячейку с абсолютной на относительную. - F4
• Развернуть / свернуть панель формул. - Ctrl + Shift + U
• Откройте диалоговое окно «Вставить функцию». - Shift + F3
• Введите формулу как формулу массива. - Ctrl + Shift + Enter
• Оцените часть формулы. - F9
• Выберите массив, содержащий активную ячейку. - CTRL + /
• Выберите все ячейки, на которые прямо или косвенно ссылаются формулы в выделенном фрагменте. - CTRL+ [
• Выберите ячейки, содержащие формулы, которые прямо или косвенно ссылаются на активную ячейку. - CTRL + ]
• Переключить отображение значения / формулы. - Ctrl + `
• Повторно проверяет зависимые формулы, а затем вычисляет все ячейки во всех открытых книгах. - Ctrl+ Alt + Shift + F9

Поиск и замена

• Отображение диалогового окна «Найти и заменить» («Найти выбранное»). - Ctrl + F
• Отображение диалогового окна «Найти и заменить» («Заменить выбранное»). - Ctrl + H
• Найти следующее совпадение. - Shift + F4
• Найдите предыдущее совпадение. - Ctrl + Shift + F4

Условное форматирование

• Откройте диалоговое окно условного форматирования. - Alt + O + D
• Очистить условное обозначение из выбранных ячеек. - Alt + H + L + C + S
• Очистить условное обозначение на всем листе. - Alt + H + L + C + E

Диаграммы

• Вставляет диаграмму в рабочий лист (используя выбранные данные). - Alt + F1

Имена, диапазоны

• Получите список всех определенных именованных диапазонов. - F3
• Создать именованный диапазон из выделенного. - Ctrl + Shift + F3
• Диалоговое окно определения имени. - Ctrl + F3

Сводная таблица

• Отобразить диалоговое окно «Вставить сводную таблицу». - Alt + N + V
• Откройте мастер сводной таблицы. - Alt + D + P
• Выберите всю сводную таблицу (за исключением фильтров отчета). - Ctrl + A
• Добавить / удалить галочку для выбранного поля в списке полей сводной таблицы. - Space
• Сгруппируйте выбранные элементы сводной таблицы. - Alt + Shift + →
• Разгруппируйте элементы сводной таблицы. - Alt + Shift + ←
• Выберите следующий элемент в списке полей сводной таблицы или списке элементов. - ↓
• Выберите предыдущий элемент в списке полей сводной таблицы или списке элементов. ↑
• Выберите последний видимый элемент в списке. - End
• Выберите первый видимый элемент в списке. - Home
• Откройте список полей для активной ячейки. - Alt + ↓
• Скрыть выбранный элемент или поле. - Ctrl + -
• Открывает диалоговое окно «Расчетное поле» (когда выбрано поле данных). - Shift + Ctrl + =

Прочее

• Восстановить размер окна. - Ctrl + F5
• Переместить окно. - Ctrl + F7
• Изменить размер окна. - Ctrl + F8
• Предыдущее окно. - Ctrl + Shift + F6
• Следующая панель. - F6
• Предыдущая панель. - F8
• Расширенный режим. - Shift + F10
• Показать контекстное меню. - Shift + F6
• Включает режим добавления к выделенному. - Shift + F8

Макросы

• Переключайтесь между Excel Worksheet и редактором VBA. - Alt + F11
• VB Help. - F1
• Просмотр обозревателя объектов. - F2
• Просмотр свойств. - F4
• Просмотр окна кода. - F7
• Просмотр немедленного окна. - Ctrl + G
• Просмотр контекстного меню. - Shift + F10
• Запустите Sub / UserForm. - F5
• Шаг - F8
• Переступить. - Shift + F8
• Выйти. - Ctrl + Shift + F8
• Перейти к курсору. - Ctrl + F8
• Переключить точку останова. - F9
• Очистить все точки останова. - Ctrl + F9
• Закройте редактор VBA и вернитесь к таблице Excel. - Alt + Q

Отлично! Раз вы дочитали или просто долистали статью до конца, значит вам это нужно. Поэтому чтобы каждый раз не возвращаться к списку, просто скачайте его и распечатайте. СКАЧАТЬ . Так вы всегда будет видеть его перед глазами и запоминать сочетания если будете ими пользоваться.

На этом у меня всё. 🏁 Если вам понравился сегодняшний урок, ставьте лайки 👍 и подписывайтесь на канал. Если хотите посмотреть еще уроки загляните в СОДЕРЖАНИЕ , обязательно еще что-нибудь присмотрите )) Спасибо!

Всем привет. Сегодняшний пост хочу посвятить способам выделения ячеек в Майкрософт Эксель. Если вы уже знаете, как выделять ячейки – бегло просмотрите текст, возможно некоторые возможности программы вам не известны, и вы не пользуетесь их преимуществами.

И так, выделение групп ячеек нужно для того, чтобы выполнить с ними какое-то общее действие: внести значение или формулу, изменить формат, скрыть, удалить и т.д. Как же можно выделить ячейки в Эксель? Разбираемся!

Как выделить все ячейки листа Excel

Сразу отвечу на самый задаваемый вопрос по теме статьи. Как же выделить сразу все ячейки листа? Предлагаю Вам два способа, какой больше нравится, тот и используйте:

Выделение прямоугольного диапазона ячеек

Предположим, вам нужно выделить прямоугольную группу ячеек, чтобы отформатировать их под свой тип данных. Я могу предложить 5 способов выделения, а вы выбирайте, какой будет уместен для вас в данный момент:

1. Выделение мышью. Самый распространенный способ – зажмите левую кнопку мыши внутри одной из клеток (не на рамке) и растягивайте выделение в нужном направлении по строкам и столбцам
2. Зажатием клавиши Shift . Поставьте курсор в одну из угловых ячеек будущего выделения. Зажмите Shift и выделяйте область клавишами стрелок. Когда закончите – отпустите Shift .
3. Нажмите клавишу F8 . Команда аналогична предыдущей, но клавишу не нужно зажимать. Установите курсор в одну из угловых ячеек массива, нажмите F8 , чтобы включить режим выделения. Клавишами стрелок расширяйте выделение, либо кликните мышью в противоположном углу будущего выделения. Когда массив выделен – нажмите F8 еще раз, чтобы выйти из этого режима.

Еще один способ – установите курсор в верхней левой клетке диапазона, зажмите Shift и кликните мышью на правую нижнюю (см. рис.)

Выделить строки и столбцы полностью

Если нужно выделить весь столбец или строку – вот несколько вариантов выполнения:

• Кликните мышью на номере строки или имени столбца. Если нужно выбрать несколько смежных строк – зажмите левую кнопку мыши на номере строки и тяните выделение на те строки, которые нужно выделить. Аналогично поступаем со столбцами
• Поставьте курсор в любую ячейку строки, которую нужно выделить и нажмите комбинацию Shift+Пробел . Для выделения столбца пользуйтесь комбинацией Ctrl+Пробел
• Чтобы выделить несмежные строки и столбцы – зажмитеCtrl и кликайте по именам столбцов и номерам строк. После завершения – отпустите Ctrl .

Выделение несмежных диапазонов

Если нужно выделить несколько не стоящих рядом ячеек, сделайте это одним из предложенных способов:

• Зажмите клавишу Ctrl и выделяйте мышью нужные диапазоны
• Выделите первую ячейку или диапазон, нажмите комбинацию Shift+F8 для перехода в режим выделения. Мышью выделяйте те диапазоны, которые нужны. По окончанию, повторно нажмите Shift+F8
• Введите адреса ячеек или диапазоны ячеек (через «;») в строке «Имя»
  Выделение несмежных ячеек через строку имени
• Выполните Главная – Редактирование – Найти и выделить – Перейти . В поле «Ссылка» введите адреса ячейки и диапазонов через точку с запятой. Нажмите Ок

Выделение на нескольких листах одновременно

Если на рабочих листах одного документа находятся одинаковые таблицы с разными данными, мы можем выполнять операции на всех вкладках одновременно. Это экономит время и снижает вероятность ошибки. Например, нам нужно залить жёлтым цветом шапки каждой из таблиц на нескольких листах. Не нужно делать это на каждой вкладке отдельно – выделите все листы и сделайте всё за один раз.

Чтобы выделить одну и ту же область на нескольких листах, сначала выделите нужные листы. Активируйте первый лист из списка, зажмите Ctrl и кликайте по ярлыкам всех листов для выделения.

Когда выделены все нужные листы – можно выполнять операции. Обратите внимание, в строке имени возле имени файла появилась надпись «[Группа]». Это значит, что Эксель готов обрабатывать группу листов.

Изменение названия при выделении группы листов

Далее, на активном листе выделяйте нужные диапазоны, вносите изменения, заполняйте общие данные и формулы. Всё, что вы сделаете, будет применено ко всем выделенным листам. После завершения групповой обработки не забудьте снять выделение листов. Для этого кликните на любой ярлык группированного листа правой кнопкой мыши, в контекстном меню выберите Разгруппировать .

Условное выделение ячеек

Microsoft Excel может выделить группу ячеек в зависимости от их содержимого. Не все пользователи знают об этой возможности, хотя её применение может быть очень полезным.

Выполните команду Главная – Редактирование – Найти и выделить – Перейти . В появившемся окне нажмите Выделить… . Появится диалоговое окно «Выделение группы ячеек», в котором доступны такие опции выделения:

Выделение ячеек через окно поиска

Чтобы открыть окно поиска значения – выполните Главная – Редактирование – Найти и выделить – Найти (или нажмите комбинацию клавиш Ctrl+F ). Появится немодальное окно поиска.

Использование поиска для выделения ячеек

В поле Найти: внесите символы или числа, которые нужно найти и нажмите Найти все . В нижней части окна появится список ячеек, содержащих выделенные данные. Выберите одну или несколько ячеек (с зажатым Ctrl ) в списке, чтобы Эксель их выделил. Чтобы выделить все найденные ячейки – выделите одну из них и нажмите комбинацию Ctrl+A .

Для поиска можно использовать специальные символы:

Вот и всё о выделении ячеек в MS Excel, а следующий пост хочу посвятить копированию данных. Как всегда, эта статья будет содержать много полезностей, которые обязательно пригодятся в работе. Так что, прочтите, не пожалеете!

Кстати, жду Ваших вопросов касательно этой статьи в комментариях!

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

12 комментариев

Спасибо.Очень полезная информация. Подскажите пожалуйста, возможно есть такая функция, выделение ячеек (цветом), на которые ссылается другой файл.Допустим из оворотной ведомости данные переносили в бюджет и те ячейки, которые уже отразились окрашивались в другой цвет, или менялся шрифт (что угодно), но выделялось?
Благодарю за ответ!

Подскажите, как в Эксель одновременно и быстро выделить несколько фигур (квадратов/кубиков и тд)? Чтобы это можно было сделать как в ПоверПоинт. То есть, чтобы курсор превратился в стрелку и ею можно было обвести несколько фигур и сразу их выделить одним махом.
Большое спасибо!

Главная/ найти/ стрелка. Можно на ней нажать ПКМ и добавить на панель бістрого доступа

Виталий, спасибо за уточнение. На блоге есть отдельная статья о добавлении команд на панель быстрого доступа.

Горячие клавиши Excel способствуют более эффективной работе с программой.

Табличный процессор Excel сочетает в себе массу функций и возможностей для работы даже с самыми сложными таблицами и документами.

Наличие горячих клавиш облегчает работу пользователей. Им не приходится отвлекаться на нажатие кнопки на панели инструментов.

К сожалению, небольшое количество юзеров осведомлены со всеми возможностями программы, в частности, и с самыми популярными сочетаниями на клавиатуре.

Вычисление

Создаете вы сложный отчет или простую таблицу в программе, функции вычисления одинаково необходимы в обоих случаях.

С помощью горячих функций можно проводить все расчеты в несколько раз быстрее и эффективнее.

Прописав любую формулу, пользователь самостоятельно определяет порядок действий, которые будут произведены над ячейкой.

Операторы – это символьные или условные обозначения действий, которые будут выполнены в ячейке.

Список горячих клавиш и операторов, которые они вызывают:

Редактирование

С помощью горячих клавиш редактирования можно легко заполнить данными таблицу. Заполнить диапазон и редактировать ячейку можно, не отрываясь от работы с клавиатурой:

Форматирование

Форматирование данных с помощью горячих клавиш позволяет без вызова дополнительного меню настроить формат каждой ячейки для проведения дальнейших расчетов.

Ввод данных

Быстрые сочетания клавиш для ввода данных сэкономят время, ведь нет необходимости постоянно переключатся между ячейками вручную и выделять текст.

Навигация и выделение

Горячие клавиши навигации помогут быстрее сориентироваться в положении всех элементов на листе и эффективнее получать доступ к каждому из них.

Работа с файлами

С помощью всего нескольких комбинаций клавиш можно открыть, сохранить или создать файл.

Используйте горячую клавишу сохранения периодически, чтобы не потерять данные документа в случае сбоя в программе.

Также вам могут быть интересны такие статьи:

• Формулы EXCEL с примерами — Инструкция по применению
• Сводные таблицы Excel — Примеры создания
• Округление в Excel — Пошаговая инструкция

Разное

Данные комбинации горячих клавиш позволяют создать дополнительные режимы работы с таблицами и другие функции, необходимые для быстрой работы в программе.

Также пользователи табличного процессора могут использовать горячие клавиши для функции поиска. Поиск в экселе стилизован под специальную клавишу на главной панели инструментов.

Меню выделения и поиска по документу

Окно поиска с последующей заменой поможет сократить время на редактирование документа.

Вызвать меню поиска можно с помощью сочетания горячих клавиш CTRL+F, как и в других программах пакета MS Office. Таки образом используется только клавиатура.

Чтобы найти или заменить элементы, следуйте инструкции:

• Нажмите CTRL+F или меню поиска на панели инструментов;
• В открывшемся перейдите на вкладку поиска, если вам просто нужно найти объект или на вкладку «найти-заменить», если необходимо осуществить поиск в документе с последующей заменой найденных данных;

Диалоговое окно «найти-заменить»

• С помощью дополнительных функций можно указать особые параметры поиска, к примеру, формат ячеек, место и область поиска;
• После того, как ячейка с исходными данными была найдена, кликните на клавишу заменить.

12 способов для ускорения работы в Microsoft Excell

№1. Копирование элементов

Чтобы не копировать вручную одну и ту же формулу в каждую ячейку одного столбца, просто поднесите к первой ячейке указатель и два раза нажмите на черный крестик, как показано на рисунке.

Процесс быстрого копирования элементов одного столбца таблицы

№2. Автоматическое определение необходимой ширины каждого столбца

Чтобы настроить правильную ширину столбика, щелкните два раза мышью на границе одного из них.

Способ выравнивания ширины столбца

№3. Быстрое создание выпадающего списка в одной из ячеек

Чтобы быстро создать такой список, следуйте инструкции:

№4. Копирование элементов с сохранением форматирования

Совет! Чтобы не настраивать форматирование после каждого копирования, в параметрах заполнения выберите пункт, который заполняет форматированием только появившееся значение, как показано на рисунке.

Копирование без потери формата

№5. Моментальный переход на новый лист документа

Быстро перемещаться между листами можно, щелкнув правой кнопкой мышки по оглавлению книги (левый нижний угол рабочего окна).

Быстрое перемещение между листами книги

№6. Создание умной таблицы

Обычный список в таблице можно превратить в умную таблицу с помощью сочетания горячих клавиш CTRL+L.

Таким образом будет подключен фильтр и автоматическая настройка всех входящих данных в таблицу (выравнивание по размеру).

№7. Моментальное добавление информации на диаграмму

Чтобы не вписывать раз за разом в диаграмму координаты ячеек, можно поступить следующим образом: выделите все необходимые ячейки и перетащите их на диаграмму.

Быстрое добавление ячеек в диаграмму

№8. Автоматическое заполнение пустой ячейки (ячеек)

Выделите необходимый диапазон и с помощью клавиши F5 выберите «пустые ячейки». Нажмите на клавиатуре на CTRL+Enter.

№9. Копирование только тех ячеек, которые видимы

Выделите ячейки (необходимый диапазон). С помощью комбинации клавиш ALT+; можно выделить видимые ячейки.

Затем скопируйте и вставьте данные в нужное место, как показано на рисунке ниже.

Копирование данных в ячейке

№10. Ввод недавно используемых функций

Если вам нужно применить к ячейке функцию, которая недавно была использована, следуйте инструкции:

Ввод последней формулы

№11. Преобразование столбцов в строки и наоборот

Скопируйте строку или столбец. Вставьте данные в нужное место с помощью комбинации клавиш CTRL+ALT+V.

Транспонируйте выделенную область с помощью окна вставки.

№12. Мгновенное копирование листов книги

Чтобы выполнить копирование быстро нажмите на иконку листа и нажмите на клавишу CTRL.

Видеоролики:

Презентация Горячих клавиш при работе в Excel

Горячие клавиши Excel — Самые необходимые варианты

10 редкоиспользуемых сочетаний клавиш в Excel

Рассматриваем малоизвестные, но очень полезные горячие сочетания клавиш в табличном процессоре MS Excel.

Горячие клавиши. Введение

Выделяем ячейки, содержащие искомый текст. Рассмотрим разные варианты: выделение ячеек, содержащих значения в точности совпадающих с искомым текстом; выделение ячеек, которые содержат искомый текст в начале, в конце или середине строки. Также научимся выделять ячейку, в случае если ее значение совпадает с одним из нескольких заданных значений.

Эта статья создана для пользователей, уже уверенно применяющих Условное форматирование. Для тех, кто только начинает использовать этот инструмент, рекомендуется ознакомиться с базовой статьей по этой теме Условное форматирование в MS EXCEL .

Исходная таблица с текстовыми значениями

Пусть в диапазоне А9:A17 имеется список с перечнем инструментов (см. файл примера ). Список содержит как ячейки с одним словом, так и текстовые строки (несколько строк, разделенных пробелами).

Задача 1 (поиск значений в списке, совпадающих с единственным критерием)

Выделим ячейки, содержащих значения, удовлетворяющих 1 критерию (без учета РЕгиСТра ). Критерий введем в ячейку B 6 . Кроме того, зададим 4 Типа поиска:

• Точно совпадает (выделение ячеек, содержащих значения в точности совпадающих с искомым текстом);
• Содержит (выделение ячеек, которые содержат искомый текст в начале, в конце или середине строки);
• Начинается (выделение ячеек, которые содержат искомый текст в начале строки);
• Заканчивается (выделение ячеек, которые содержат искомый текст в конце строки).

В качестве примера Критерия используем текст « Дрель », который введем в ячейку B 6 . Естественно, для критерия можно использовать любой другой текст.

Решение

Выбор Типа поиска организуем с помощью Элементов управления формы Группа и Переключатель. Свяжем все Переключатели с ячейкой B9 . Теперь при выборе Типа поиска Точно совпадает в этой ячейке будет содержаться число 1, при выборе Содержит - 2, Начинается - 3, Заканчивается - 4 (см. файл примера Лист 1 критерий (текст) ).

Настроим Условное форматирование для выделения ячеек, удовлетворяющих критерию. Для этого:

• выделите ячейки, в которые введен перечень инструментов ( А9:A17 );
• вызовите инструмент Условное форматирование ( Главная/ Стили/ Условное форматирование/ Создать правило )
• выберите Использовать формулу для определения форматируемых ячеек;
• в поле « Форматировать значения, для которых следующая формула является истинной » введите =ВЫБОР($B$9;$A$6=A9;ПОИСК($A$6;A9);ПОИСК($A$6;A9)=1;$A$6=ПРАВСИМВ(A9;ДЛСТР($A$6)))
• выберите требуемый формат, например, красный цвет фона .

Если в качестве примера Критерия используется текст « Дрель », а Тип поиска выбран Точно совпадает (1), то красным фоном выделится только ячейка A14 , содержащая значение в точности совпадающее с искомым текстом.

Разберем работу формулы из вышеуказанного правила Условного форматирования .

Функция ВЫБОР() позволяет в одной формуле совместить 4 формулы для всех Типов поиска:

• Точно совпадает: $A$6=A9 ;
• Содержит: ПОИСК($A$6;A9) ;
• Начинается: ПОИСК($A$6;A9)=1 ;
• Заканчивается: $A$6=ПРАВСИМВ(A9;ДЛСТР($A$6)) .

В зависимости от значения в ячейке B9 , связанной с Переключателями, функция ВЫБОР() возвращает соответствующую формулу.

Формула ПОИСК($A$6;A9) возвращает номер позиции текстовой строки из ячейки А9 , с которой начинается текст, указанный в критерии (если он найден).

Например, для ячейки А14 формула ПОИСК($A$6;A14) вернет 1, а для А15 формула ПОИСК($A$6;A15) вернет 8 и обе ячейки будут выделены. Если в ячейке не содержится искомый текст, то формула возвращает ошибку и ячейка не выделяется.

Примечание : Обратите внимание, что будет выделена ячейка со словом Мотодрель , как ячейка содержащая критерий. Но, если нужно найти ячейки содержащие слово Дрель (т.е. текст, отделенный от других слов пробелами), то нужно обратиться к решению Задачи 2 (см. ниже).

Формула $A$6=ПРАВСИМВ(A9;ДЛСТР($A$6)) возвращает правую часть каждого значения перечня длиной равной критерию, т.е. в нашем случае 5 (т.к. в критерии "Дрель" 5 букв), а затем сравнивает ее с критерием. Если оба выражения совпадают, то это означает, что значение перечня заканчивается на текст, указанный в Критерии.

Примечание : Обратите внимание, что будет выделена ячейка со словом Мотодрель , как ячейка заканчивающаяся на критерий. Но, если нужно найти ячейки содержащие слово Дрель (т.е. текст, отделенный от других слов пробелами), то нужно обратиться к решению Задачи 2 (см. ниже).

Если критерий содержится в начале текстовой строки, то ПОИСК() вернет 1. Следовательно, формула ПОИСК($A$6;A9)=1 вернет ИСТИНА только для ячеек начинающихся с искомого текста.

Совет : Для настройки поиска ячеек, значения которых удовлетворяют критерию с учетом РЕгиСТРА, читайте статью Выделение ячеек c ТЕКСТом с применением Условного форматирования (с учетом РЕгиСТРА) .

Задача 2 (поиск СЛОВ в списке, совпадающих с единственным критерием)

Определить, есть ли слово в списке несколько сложнее, чем определить содержится ли в списке некий текст. Разница в этих подходах проявляется при наличии в списке слов, содержащих искомый текст, но не совпадающих с ним. Например, слово в списке Мотодрель содержит текст Дрель, но слово Дрель не содержится в этой ячейке.

Чтобы выделить ячейки в списке, которые содержат слово Дрель, нужно воспользоваться формулами в файле примера на Листе "1 критерий (слово)".

Понятно, что нужно выделить ячейки, в которых:

• точно содержится слово Дрель ИЛИ;
• текстовая строка начинается со слова Дрель ИЛИ;
• текстовая строка заканчивается словом Дрель ИЛИ;
• слово Дрель содержится в середине текстовой строки (критерий обрамлен пробелами).

В принципе можно записать одну большую формулу и не использовать дополнительные столбцы (C:F)

=ИЛИ(ИЛИ(ЕСЛИОШИБКА(A9=$A$6;0));ИЛИ(ЕСЛИОШИБКА(ПОИСК(" "&$A$6&" ";A9);0));ИЛИ(ЕСЛИОШИБКА(ПОИСК($A$6&" ";ЛЕВСИМВ(A9;ДЛСТР($A$6)+1));0));ИЛИ(ЕСЛИОШИБКА(" "&$A$6=ПРАВСИМВ(A9;ДЛСТР($A$6)+1);0)))

Задача 3 (поиск нескольких слов в списке)

Рассмотрим списки, элементами которых являются отдельные слова (не фразы). Выделять будем только ту ячейку, которая содержит любое из слов-критериев (см. файл примера Лист Неск критериев (слово точно) ).

Это можно сделать несколькими способами.

Вариант с использованием Массива констант и имени. В этом случае в правиле Условного форматирования нужно ввести формулу =ИЛИ($A2=Слова) , где Слова - это Имя массива констант .

Этот способ не очень удобен, т.к. при изменении критериев придется изменять массив констант в Диспетчере имен.

Вариант с использованием именованного диапазона. Формула =ИЛИ($B2=Слова_в_диапазоне) аналогична предыдущей, но имя Слова_в_диапазоне теперь ссылается на диапазон ячеек листа (см. статью Именованный диапазон ). При добавлении новых слов в ячейки в столбце F, придется переопределять именованный диапазон.

Вариант с использованием динамического диапазона . Динамический диапазон позволяет добавлять новые слова-критерии в столбец F и при этом не требуется модифицировать формулы и имена.

Вариант без использования имен . Формула =ИЛИ($D2="слово1";$D2="слово2";$D2="слово3") позволяет отказаться от использования имен. Платой за это станет необходимость изменения правила Условного форматирования при каждом изменении слов-критериев.

Задача 4 (поиск нескольких слов в списке, общий случай)

Рассмотрим списки, элементами которых являются фразы. Выделять будем только ту ячейку, которая содержит любое из слов-критериев: в начале, в середине или конце фразы или если фраза целиком совпадает с одним из слов-критериев (см. файл примера Лист Неск критериев (слово) ).

Слово Разряд в списке не встречается (склонения не считаются). А вот слово Техник встречается как в начале фраз, так и в середине и конце.

Не смотря на то, что формулы похожи на те, что использовались в Задаче 2, они от них принципиально отличаются, т.к. являются формулами массива .

ПСТР - одна из текстовых функций, которые Microsoft Excel предоставляет для управления текстовыми строками. На самом базовом уровне она используется для извлечения подстроки из середины текста.

В этом руководстве мы обсудим синтаксис и особенности функции Excel ПСТР (в английской версии – MID), а затем вы узнаете несколько вариантов творческого её использования для выполнения сложных задач.

Cинтаксис.

Функция ПСТР возвращает указанное количество знаков, начиная с указанной вами позиции.

Функция Excel ПСТР имеет следующие аргументы:

ПСТР(текст; начальная_позиция; количество_знаков)

Где текст - это исходная текстовая строка. Далее следует позиция первого символа, который вы хотите извлечь, и количество их для извлечения.

Все 3 аргумента обязательны.

Например, чтобы извлечь 6 знаков из A2, начиная с 17-го, используйте эту формулу:

Результат может выглядеть примерно так:

5 вещей, которые вы должны знать о функции Excel ПСТР

Как вы только что убедились, в использовании функции ПСТР в Excel нет ничего страшного. И помня следующие простые факты, вы избежите наиболее распространенных ошибок.

При решении реальных задач в Excel вам чаще всего потребуется использовать ПСТР в сочетании с другими функциями, как показано в следующих примерах.

Как извлечь имя и фамилию.

Если у вас была возможность прочитать наши недавние уроки, вы уже знаете, как вытащить имя с помощью функции ЛЕВСИМВ и получить фамилию с помощью ПРАВСИМВ. Но, как это часто бывает в Excel, одно и то же можно сделать разными способами.

Получаем имя.

Предполагая, что полное имя находится в ячейке A2, имя и фамилия разделены интервалом, вы можете извлечь имя, используя следующую формулу:

ПОИСК используется для сканирования исходного значения на предмет пробела (" ") и возврата его позиции, из которой вы вычитаете 1, чтобы избежать пробелов после имени. Затем вы используете ПСТР, чтобы вернуть подстроку, начинающуюся с первого знака и заканчивая предшествующим пробелу, таким образом извлекая первое имя.

Получаем фамилию.

Чтобы извлечь фамилию из A2, используйте эту формулу:

Опять же, вы используете ПОИСК, чтобы определить начальную позицию (пробел). Нам не нужно точно рассчитывать конечную позицию (как вы помните, если вместе взятые начальная позиция и количество символов больше, чем общая длина текста, возвращаются просто все оставшиеся). Итак, в аргументе количество символов вы просто указываете общую первоначальную длину , возвращаемую функцией ДЛСТР . Впрочем, вместо этого вы можете просто ввести число, представляющее самую длинную фамилию, которую вы ожидаете найти, например 100. Наконец, СЖПРОБЕЛЫ удаляет лишние интервалы, и вы получаете следующий результат:

Как выделить подстроку между двумя разделителями.

Продолжим предыдущий пример. А если, помимо имени и фамилии, ячейка A2 также содержит отчество, то как его извлечь?

Технически задача сводится к определению позиций двух пробелов в исходном тексте, и вы можете сделать это следующим образом:

• Как и в предыдущем примере, используйте ПОИСК, чтобы определить позицию первого (" "), к которому вы добавляете 1, потому что вы хотите начать с символа, следующего за ним. Таким образом, вы получаете адрес начальной позиции: ПОИСК (" "; A2) +1
• Затем вычислите позицию 2- го интервала, используя вложенные функции поиска, которые предписывают Excel начать поиск именно со 2-го: ПОИСК (" "; A2, ПОИСК (" "; A2) +1)

Чтобы узнать количество извлекаемых знаков, вычтите позицию первого пробела из положения второго. И затем еще вычтите 1 из результата, поскольку вам не нужны лишние интервалы в получившемся результате. Таким образом, у вас есть второй аргумент для формулы:

ПОИСК(" "; A2; ПОИСК(" "; A2) +1) - ПОИСК(" "; A2)

Соединив все аргументы, мы получаем формулу для извлечения подстроки между двумя пробелами:

На следующем скриншоте показан результат:

Аналогичным образом вы можете извлечь текст между любыми другими разделителями:

ПСТР( строка ; ПОИСК( разделитель ; строка ) +1; ПОИСК( разделитель ; строка ; ПОИСК( разделитель ; строка ) +1) - ПОИСК( разделитель ; строка ) -1)

Например, чтобы извлечь отрезок, выделенный запятой с пробелом после неё, используйте следующее выражение:

На следующем рисунке эта формула используется для извлечения из адреса названия города, и она отлично справляется со своей задачей:

Как получить N-е слово из текста.

Этот пример демонстрирует оригинальное использование сложной формулы ПСТР в Excel, которое включает 5 различных составных частей:

• ДЛСТР - чтобы получить общую длину.
• ПОВТОР - повторение определенного знака заданное количество раз.
• ПОДСТАВИТЬ - заменить один символ другим.
• ПСТР - извлечь подстроку.
• СЖПРОБЕЛЫ - удалить лишние интервалы между словами.

Общая формула выглядит следующим образом:

СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ( строка ; " "; ПОВТОР (" "; ДЛСТР( строка ))); ( N -1) * ДЛСТР( строка ) +1; ДЛСТР( строка )))

• Строка - это исходный текст, из которого вы хотите извлечь желаемое слово.
• N – порядковый номер слова, которое нужно получить.

Например, чтобы вытащить второе слово из A2, используйте это выражение:

Или вы можете ввести порядковый номер слова, которое нужно извлечь (N) в какую-либо ячейку, и указать эту ячейку в формуле, как показано на скриншоте ниже:

Как работает эта формула?

По сути, Excel «оборачивает» каждое слово исходного текста множеством пробелов, находит нужный блок «пробелы-слово-пробелы», извлекает его, а затем удаляет лишние интервалы. Чтобы быть более конкретным, это работает по следующей логике:

• ПОДСТАВИТЬ и ПОВТОР заменяют каждый пробел в тексте несколькими. Количество этих дополнительных вставок равно общей длине исходной строки: ПОДСТАВИТЬ($A$2;" ";ПОВТОР(" ";ДЛСТР($A$2)))

Вы можете представить себе промежуточный результат как «астероиды» слов, дрейфующих в пространстве, например: слово1-пробелы-слово2-пробелы-слово3-… Эта длинная строка передается в текстовый аргумент ПСТР.

• Затем вы определяете начальную позицию для извлечения (первый аргумент), используя следующее уравнение: (N-1) * ДЛСТР(A1) +1. Это вычисление возвращает либо позицию первого знака первого слова, либо, чаще, позицию в N-й группе пробелов.
• Количество букв и цифр для извлечения (второй аргумент) - самая простая часть - вы просто берете общую первоначальную длину: ДЛСТР(A2).
• Наконец, СЖПРОБЕЛЫ избавляется от начальных и конечных интервалов в извлечённом тексте.

=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ($A$2);" "; ПОВТОР(" ";ДЛСТР($A$2))); (B2-1)*ДЛСТР($A$2)+1; ДЛСТР($A$2)))

Следующий рисунок демонстрирует улучшенный вариант (2) в действии:

Если ваш исходный текст содержит несколько пробелов между словами, а также очень большие или очень короткие слова, дополнительно вставьте СЖПРОБЕЛЫ в каждое ДЛСТР, чтобы вы были застрахованы от ошибки:

=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ($A$2);" "; ПОВТОР(" ";ДЛСТР(СЖПРОБЕЛЫ ($A$2)))); (B2-1)*ДЛСТР(СЖПРОБЕЛЫ($A$2))+1; ДЛСТР(СЖПРОБЕЛЫ($A$2))))

Я согласен с тем, что это выглядит немного громоздко, но зато безупречно обрабатывает все возможные варианты.

Извлекаем слово, содержащее определенный символ.

В этом примере показана еще одна нестандартная формула Excel ПСТР, которая извлекает слово, содержащее определенную букву или цифру, из любого места:

СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ ( строка , " ", ПОВТОР(" ", 99)), МАКС(1, НАЙТИ( символ , ПОДСТАВИТЬ( строка , " ", ПОВТОР(" ", 99))) - 50), 99))

Предполагая, что исходный текст находится в ячейке A2, и вы хотите получить слово, содержащее символ «$» (цена), выражение принимает следующую форму:

=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A2;" ";ПОВТОР(" ";99)); МАКС(1;НАЙТИ("$";ПОДСТАВИТЬ(A2;" ";ПОВТОР(" ";99)))-50);99))

Аналогичным образом вы можете извлекать адреса электронной почты (на основе знака «@»), имена веб-сайтов (на основе «www») и так далее.

Теперь разберём пошагово:

Как и в предыдущем примере, ПОДСТАВИТЬ и ПОВТОР превращают каждый пробел в исходном тексте в несколько, точнее, в 99.

НАЙТИ находит позицию нужного символа (в данном примере $), из которой вы вычитаете 50. Это возвращает вас на 50 позиций назад и помещает где-то в середине блока из 99 пробелов, который предшествует слову, содержащему указанный символ.

МАКС используется для обработки ситуации, когда нужное значение появляется в начале исходного текста. В этом случае результат ПОИСК() - 50 будет отрицательным числом, а МАКС(1, ПОИСК() - 50) заменяет его на 1.

С этой начальной точки ПСТР отбирает следующие 99 знаков и возвращает интересующее нас слово, окруженное множеством пробелов. Как обычно, СЖПРОБЕЛЫ помогает избавиться от лишних из них, оставив только один.

Совет. Если извлекаемый отрезок очень большой, замените 99 и 50 на более крупные числа, например 1000 и 500.

Как заставить ПСТР возвращать число?

Как и другие текстовые функции, Excel ПСТР всегда возвращает текст, даже если он содержит только цифры и очень похож на число. Вы можете убедиться с этом, взглянув на пример чуть выше, когда мы получили число «20%» как текст.

Чтобы преобразовать результат в число, просто передайте полученный результат в функцию ЗНАЧЕН (VALUE в английской версии), которая преобразует текстовое значение, состоящее из цифр, в число.

Например, чтобы извлечь подстроку из 3 символов, начинающуюся с 7- го символа, и преобразовать ее в число, используйте:

На скриншоте ниже показан результат. Обратите внимание, что числа с выравниванием по правому краю помещены в столбец B, в отличие от исходных текстовых значений с выравниванием по левому краю в столбце A:

Тот же подход работает и для более сложных случаев. В приведенном выше примере, предполагая, что коды ошибок имеют переменную длину, вы можете извлечь их с помощью ПСТР, которая получает подстроку между двумя разделителями, вложенную в ЗНАЧЕН:

Вот как можно использовать функцию ПСТР в Excel.

Как извлечь текст из ячейки с помощью Ultimate Suite

Как вы только что видели, Microsoft Excel предоставляет набор различных функций для работы с текстовыми строками. Если вам нужно извлечь какое-то слово или часть текста из ячейки, но вы не уверены, какая функция лучше всего подходит для ваших нужд, передайте работу Ultimate Suite for Excel. Заодно не придётся возиться с формулами.

Вы просто переходите на вкладку Ablebits Data > Текст, выбираете инструмент Split Text и в выпадающем списке нажимаете Извлечь (Extract) :

Теперь вы выбираете исходные ячейки, и какие бы сложные строки они ни содержали, извлечение из них подстроки сводится к этим двум простым действиям:

1. Укажите, сколько символов вы хотите получить из начала, конца или середины строки; или выберите извлечение всего текста до или после определенного символа.
2. Щелкните Вставить результаты(InsertResults). Готово!

Кроме того, вы можете извлечь любое число символов с начала или в конце текста, из середины текста, между какими-то символами. Например, чтобы извлечь доменные имена из списка адресов электронной почты, вы выбираете чекбокс Все после текста (All after text) и вводите @ в поле рядом с ним. Чтобы извлечь имена пользователей, выберите переключатель Все до текста (All before text), как показано на рисунке ниже.

Помимо скорости и простоты, инструмент «Извлечь текст» имеет дополнительную ценность - он поможет вам изучить формулы Excel в целом и функции подстроки в частности. Как? Выбрав флажок Вставить как формула (Insert as formula) в нижней части панели, вы убедитесь, что результаты выводятся в виде формул, а не просто как значения. Естественно, эти формулы вы можете использовать в других таблицах.

В этом примере, если вы выберете ячейки B2 и C2, вы увидите следующие формулы соответственно:

Сколько времени вам потребуется, чтобы самостоятельно составить эти выражения? ;)

Поскольку результаты представляют собой формулы, извлеченные подстроки будут обновляться автоматически, как только в исходные ячейки будут внесены какие-либо изменения. Когда в ваш набор данных добавляются новые записи, вы можете скопировать формулы в другие ячейки как обычно, без необходимости заново запускать инструмент «Извлечь текст».

Если вам интересно попробовать это, а также множество других полезных функций, включенных в Ultimate Suite for Excel, вы можете загрузить ознакомительную версию. Если вам нравятся инструменты, вы можете получить лицензию по очень специальной цене, которая доступна только для наших русскоязычных пользователей.

Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Читайте также:

  
• A plague tale innocence ps4 сколько весит
  
• Pci 64 bit resource handling что это
  
• Компьютер самый необычный инструмент из всех придуманных человеком
  
• Замена экрана ноутбука перово
  
• Как установить моды на гта вай сити на компьютер