Редактирование группы ячеек в excel

Обновлено: 04.07.2024

Процедура поиска и замены данных — одна из самых востребованных в Excel. Базовая процедура позволяет заменить за один заход только одно значение, но зато множеством способов. Рассмотрим, как эффективно работать с ней.

Горячие клавиши

Сочетания клавиш ниже заметно ускорят работу с инструментом:

Для запуска диалогового окна поиска — Ctrl + F
Для запуска окна поиска и замены — Ctrl + H
Для выделения всех найденных ячеек (после нажатия кнопки «найти все» — Ctrl + A
Для очистки всех найденных ячеек — Ctrl + Delete
Для ввода одних и тех же данных во все найденные ячейки — Ввод текста, Ctrl + Enter

Смотрите gif-примеры: здесь мы производим поиск ячеек с дальнейшим их редактированием. В отличие от замены, редактирование найденных ячеек позволяет быстро менять их содержимое целиком.

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

Процедура «Найти и заменить» не работает

Я сам когда-то неоднократно впадал в ступор в подобных ситуациях. Уверен и видишь своими глазами, что искомый паттерн в данных есть, но Excel при выполнении процедуры поиска сообщает:

Не удалось ничего найти по вашему запросу

Мы не нашли ничего, что нужно было заменить

Статус опций «Учитывать регистр» и «Ячейка целиком» виден после нажатия кнопки «Параметры».

Подстановочные знаки, или как найти «звездочку»

Сухая официальная справка по Excel сообщает, что можно использовать подстановочные символы «*» и «?». Что они означают несколько символов, включая их отсутствие, и один любой символ. И что их можно использовать для соответствующих процедур поиска.

Чего не говорит справка — это того, что в комбинации с опцией «ячейка целиком» эти символы позволяют, не прибегая к помощи расширенного фильтра и процедуры поиска группы ячеек:

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

На примере ниже мы находим все двузначные числа, затем числа, заканчивающиеся и начинающиеся на 7, и, наконец, все непустые ячейки. Напомню, выделить все результаты поиска помогает горячее сочетание клавиш Ctrl + A

Так а как найти звездочку?

Действительно, забыл. Чтобы найти «звездочку», нужно в окошке поиска ставить перед ней знак

(тильда), находится обычно под клавишей Esc . Это позволяет экранировать «звездочку», как и вопросительный знак, и не воспринимать их как служебные символы.

Замена нескольких значений на несколько

Массовая замена в Excel — довольно частая потребность. Очень часто нужно массово и при этом быстро заменить несколько символов, слов и т.д. на другие. При этом на текущий момент простого инструмента в стандартном функционале Excel нет.

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

Эта задача более сложная, чем замена на одно значение. Как ни странно, функция «ЗАМЕНИТЬ» здесь не подходит — она требует явного указания позиции заменяемого текста. Зато может помочь функция «ПОДСТАВИТЬ«.

Массовая замена с помощью функции «ПОДСТАВИТЬ»

Используя несколько условий в сложной формуле, можно производить одновременную замену нескольких значений. Excel позволяет использовать до 64 уровней вложенности — свобода действий высока. Например, вот так можно перевести кириллицу в латиницу:

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

Но у решения есть и свои недостатки:

Функция ПОДСТАВИТЬ регистрозависимая, что заставляет при замене одного символа использовать два его варианта — в верхнем и нижнем регистрах. Хотя, в некоторых случаях, как пример на картинке выше, это и преимущество.
максимум 64 замены — хоть и много, но все же ограничение.
формально процедура замены таким способом будет происходить массово и моментально, однако, длительность написания таких формул сводит на нет это преимущество. За исключением случаев, когда они будут использоваться многократно.

Файл-шаблон с формулой множественной замены

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

Файл доступен по ссылке, но можно и не скачивать его, а просто скопировать текст формулы ниже и вставить ее в любую ячейку, кроме диапазона A1:B64. Формула заменяет в ячейке C1 значения в столбце A стоящими напротив в столбце B.

А вот и она сама (тройной клик по любой части текста = выделить всю формулу). Обращается к ячейке D1, делая 64 замены по правилам, указанным в ячейках A1-B64. При этом в столбцах можно удалять значения — это не нарушит ее работу.

Заменить несколько значений на одно

С помощью функции «ПОДСТАВИТЬ«

При замене нескольких значений на одно и то же механика работы формул на основе нескольких уровней вложенности не будет отличаться от замены нескольких на несколько. Просто третий аргумент (на что заменить) на всех уровнях вложенности будет один и тот же. Кстати, если оставить его пустым (кавычки без символов между ними), то это позволит удалить определенные символы. Пример — удалить цифры из ячейки путем замены на пустоту:

С помощью регулярных выражений

Важно: регулярные выражения не поставляются в Excel «из коробки», но формулы ниже доступны бесплатно, если установить надстройку !SEMTools

Регулярные выражения (RegEx, регулярки) — наиболее удобное решение, когда нужно заменить несколько символов на один. Все эти несколько символов обычным способом безо всяких разделителей нужно перечислить внутри квадратных скобок. Примеры формул:

Массовая замена символов регулярными выражениями при установленной надстройке !SEMTools

Если же нужно заменять не символы, а несколько значений, состоящих в свою очередь из нескольких букв, цифр или знаков — синтаксис предполагает уже использование круглых скобок и вертикальной черты «|» в качестве разделителя.

Массовая замена в !SEMTools

Надстройка для Excel !SEMTools позволяет в пару кликов производить замены на всех уровнях:

символов и их сочетаний
паттернов регулярных выражений
слов!
целых ячеек (В некоторой степени аналог ВПР)

Меню инструментов массовой замены в !SEMTools

При этом процедуры изменяют исходный диапазон, что экономит время. Все что нужно — предварительно выделить его, определиться с задачей, вызвать нужную процедуру и выделить 2 столбца сопоставления заменяемых и замещающих значений (предполагается, что если вы знаете, что на что менять, то и такие списки есть).

Пример: замена символов по вхождению

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

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

Массовая замена символов по вхождению на примере Leet Language (некоторые английские буквы заменяются на похожие цифры)

Пример: замена списка слов на другой список слов

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

С версии !SEMTools 9.18.18 появилась опция — при замене списка слов не учитывать пунктуацию в исходных предложениях, а регистр слов теперь сохраняется:

Инструменты находятся в группе макросов «ИЗМЕНИТЬ» в отдельном меню и для удобства продублированы в меню «Изменить символы«, «Изменить слова» и «Изменить ячейки«.

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

Настраиваем параметры функции
Группируем данные по строкам
Группируем столбцы
Создаем многоуровневую группировку
Разгруппировываем данные
Заключение

Настраиваем параметры функции

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

Группируем данные по строкам

Для начала давайте рассмотрим, как можно сгруппировать строки:

Вставляем новую строку над или под строками, которые хотим сгруппировать (зависит от того, какой вид расположения итогов по строкам мы выбрали). Как это сделать, читайте в нашей статье – “Как добавить новую строку в Excel“.
В самой левой ячейке добавленной строки пишем название, которое хотим присвоить группе.
Любым удобным способом, например, с помощью зажатой левой кнопки мыши производим выделение ячеек строк (кроме итоговой), которые требуется сгруппировать. Во вкладке “Данные” щелкаем по кнопке “Структура” и в открывшемся списке выбираем функцию “Группировать”. Щелкнуть нужно именно по значку команды, а не по ее названию. Если же нажать на последнее (со стрелкой вниз), откроется еще одно подменю, в котором следует нажать на одноименную кнопку.
В появившемся окошке отмечаем пункт “строки” (должен быть выбран по умолчанию) и подтверждаем действие нажатием OK. Примечание: Если вместо ячеек выделить все строки целиком на вертикальной панели координат, а затем применить группировку, то промежуточного окна с выбором строки или столбца не будет, так как программа сразу понимает, что именно ей необходимо сделать.

Группируем столбцы

Чтобы сгруппировать столбцы, придерживаемся примерно такого же алгоритма действий, описанного выше:

Создаем многоуровневую группировку

Возможности программы позволяют выполнять как одноуровневые, так и многоуровневые группировки. Вот как это делается:

Разгруппировываем данные

Когда ранее выполненная группировка столбцов или строк больше не нужна или требуется выполнить ее иначе, можно воспользоваться обратной функцией – “Разгруппировать”:

Заключение

Группировка данных выполняется в несколько кликов и не требует особых навыков в работе с программой, однако, данный прием позволяет существенно сэкономить время, когда приходится иметь дело с большим объемом информации. Это делает функцию одной из самых полезных и незаменимых в Excel.

Еще раз здравствуйте! Продолжим предыдущий урок № 20, в котором мы начали изучать редактирование данных, а именно удаление и замену. В этом уроке поговорим непосредственно уже о редактировании.

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

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

■ Дважды щелкните на ячейке. Это позволит изменить содержимое прямо в ячейке.

■ Нажмите клавишу <F2>. Это тоже позволит изменить содержимое сразу в ячейке.

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

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

Все эти методы приводят к тому, что программа переходит в режим редактирования. (В левой части строки состояния появляется слово Правка.) В этом режиме в строке формул появляются две новые кнопки-пиктограммы.

Щелкнув на кнопке, на которой изображен символ Х , можно отменить редактирование, и содержимое ячейки останется прежним (нажатие клавиши <Esc> приводит к тому же результату). После щелчка на кнопке с галочкой V редактирование завершается, и измененные данные сохраняются в ячейке (нажатие клавиши <Enter> приводит к такому же результату).

На этом у меня на сегодня все. Если у вас остались вопросы, можете их написать у меня в группе прямо на стене во Вконтакте - Уроки Excel , постараюсь на них ответить )

Традиционно, если вам понравился урок ставьте лайк и подписывайтесь на канал чтобы не пропустить следующий :) Спасибо!

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

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

Редактирование строки текста в ячейках

Редактировать содержимое ячеек можно двумя способами:

1 Из строки формул. Щелкните по ячейке, в которой необходимо отредактировать данные. В строке формул отобразиться содержимое, которое доступно для редактирования. Сделайте необходимые изменения, после чего нажмите Enter или кликните по кнопке «Ввод», которая размещена в начале строки формул. Для отмены изменений можно нажать клавишу «Esc» или кнопку «Отмена» (возле кнопки «Ввод»).
2 Из самой ячейки. Перейдите на ячейку и нажмите клавишу F2 или сделайте по ней двойной щелчок мышкой. Тогда в ячейке появится курсор клавиатуры, а ее размер изменится на время редактирования. После всех изменений нажмите Enter или Tab или кликните мышкой на любую другую ячейку. Для отмены редактирования нажмите клавишу «Esc».

Примечание. При редактировании не забывайте о кнопках отмены / повтора действий на панели быстрого доступа. Или о комбинациях клавиш CTRL+Z и CTRL+Y.

Как сделать несколько строк в ячейке Excel?

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

Внимание!
Чтобы разбить текст на строки в одной ячейке Excel нужно нажать комбинацию клавиш Alt+Enter. Тогда вы сможете перейти на новую строку в ячейке. В том месте текста, где находится курсор клавиатуры, появится перенос строки и соответственно начало новой.

В обычных редакторах текст делится на строки нажатием клавиши Enter, но в Excel данное действие выполняет функцию подтверждения вода данных и переход на следующую ячейку. Поэтому как написать несколько строк в ячейке Excel жмите Alt+Enter.

Обратите внимание, что после разделения одной строки в ячейке на две и более с помощью клавиш Alt+Enter, то автоматически активируется опция «формат ячеек»-«выравнивание»-«перенос по словам». Притом что сама эта функция не разбивает строку на слова, а оптимизирует ее отображение.

Режим редактирования

В режиме редактирования работают все стандартные комбинации клавиш управления курсором клавиатуры, как и в других программах Windows:

Клавиша «DELETE» удаляет символ справа, а «Backspace» слева.
CTRL+ «стрелка влево» переход на начало слова, а CTRL+ «стрелка вправо» – в конец слова.
«HOME» смещает курсор клавиатуры в начало строки, а «END» – в конец.
Если в тексте более одной строки то комбинации CTRL+HOME и CTRL+END перемещают курсор в начало или в конец целого текста.

Примечание. Точно так же редактируются: формулы, функции, числа, даты и логические значения.

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

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

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