Изменить данные в ячейках Excel формулами можно на любом уровне: регистр букв меняют =ПРОПИСН(A2) и =ПРОПНАЧ(A2), замену фрагмента делает =ПОДСТАВИТЬ(A2;"старое";"новое"), текст к ячейке добавляет =A2&" руб.", а весь столбец на одно число умножает =B2*$D$2. Результат формулы появляется в соседнем столбце; чтобы заменить исходные данные, его вставляют поверх как значения. Ниже — пример для каждого подраздела.
Макросы в данной группе отвечают за изменения, которые вы хотите произвести с заданными сущностями - символами, словами, ячейками или диапазонами. Перед использованием рекомендуется использовать макросы из групп «ОБНАРУЖИТЬ» или «ИЗВЛЕЧЬ», чтобы заранее было понимание, какие элементы подлежат изменению, а какие нет. Например, если в ваших ячейках весь текст в нижнем регистре, а вы хотите найти в нем топонимы и изменить их регистр на корректный (с заглавной буквы), то сначала рекомендуется распознать топонимы.
Меню внутри группы:
Изменить символы
Меню изменений символов включает в себя 2 подменю:
- Продвинутые макросы изменения регистра букв в Excel. Сюда входят изменение регистра топонимов (города), первых букв предложений, первых букв каждого слова, автоматическое распознавание аббревиатур;
- Макросы замены кириллицы на латиницу;
- Сумма прописью с копейками.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Текст | ПРОПИСН | ПРОПНАЧ | ё → е |
| 2 | ёлки в москве | ЁЛКИ В МОСКВЕ=ПРОПИСН(A2) | Ёлки В Москве=ПРОПНАЧ(A2) | елки в москве=ПОДСТАВИТЬ(A2;"ё";"е") |
| 3 | новый год | НОВЫЙ ГОД=ПРОПИСН(A3) | Новый Год=ПРОПНАЧ(A3) | новый год=ПОДСТАВИТЬ(A3;"ё";"е") |
Подробнее — разделы изменить регистр и изменить символы.
Изменить слова
Данное меню включает в себя макросы, позволяющие:
- Поставить символ перед каждым словом - или после них;
- Сделать каждое слово, топонимы или собственный список слов с большой буквы;
- Просклонять по падежам существительные и прилагательные в ячейках.
| A | B | |
|---|---|---|
| 1 | Текст | Хештеги |
| 2 | купить диван недорого | #купить #диван #недорого="#"&ПОДСТАВИТЬ(A2;" ";" #") |
| 3 | скидки | #скидки="#"&ПОДСТАВИТЬ(A3;" ";" #") |
Первый символ приклеивается отдельно, перед остальными словами — заменой пробела. Подробнее — изменить слова.
Изменить ячейки
Изменение ячеек, а точнее, их содержимого с помощью !SEMTools, включает в себя:
- Добавить символ в каждую ячейку - в начало, конец ячейки или с обеих сторон. Или любую последовательность символов;
- Можно заполнить пустые ячейки диапазона выше- либо нижестоящими данными;
- Поменять местами слова в ячейке;
- Сортировка слов внутри ячейки в алфавитном порядке (нормализация);
- Перевести текст ячеек на другой язык.
| A | B | C | |
|---|---|---|---|
| 1 | Текст | С единицей | Слова местами |
| 2 | Иванов Иван | Иванов Иван (менеджер)=A2&" (менеджер)" | Иван Иванов=ПСТР(A2;ПОИСК(" ";A2)+1;100)&" "&ЛЕВСИМВ(A2;ПОИСК(" ";A2)-1) |
| 3 | Петрова Анна | Петрова Анна (менеджер)=A3&" (менеджер)" | Анна Петрова=ПСТР(A3;ПОИСК(" ";A3)+1;100)&" "&ЛЕВСИМВ(A3;ПОИСК(" ";A3)-1) |
Подробнее — добавить текст во все ячейки и изменить порядок слов.
Изменить числа
Зачастую бывает нужно быстро изменить числовые диапазоны ячеек, поскольку им свойственна своя специфика, я выделил их в отдельное меню. С его помощью можно производить арифметические операции:
- Прибавить число разом ко всему столбцу или строке
- Вычесть число из столбца или строки
- Умножить столбец или строку на число
- Аналогично, поделить столбец или строку на число
- Возвести числа в степень
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Цена | +100 | ×1,2 | Множитель | |
| 2 | 500 | 600=A2+100 | 600=A2*$E$2 | 1,2 | |
| 3 | 1200 | 1300=A3+100 | 1440=A3*$E$2 |
Множитель в отдельной ячейке закреплён знаками $, чтобы при протягивании ссылка не сдвигалась. Подробнее — умножить столбец на число.
В английском Excel: =A2*$E$2
Другие математические действия:
- Округлить столбец чисел
- Посчитать долю от общей суммы
- Посчитать долю накопительным итогом
- Посчитать количество повторений
- Посчитать, который раз встречается элемент в диапазоне
| A | B | C | |
|---|---|---|---|
| 1 | Выручка | Округлено до сотен | Доля |
| 2 | 1234 | 1200=ОКРУГЛ(A2;-2) | 31%=A2/СУММ($A$2:$A$3) |
| 3 | 2766 | 2800=ОКРУГЛ(A3;-2) | 69%=A3/СУММ($A$2:$A$3) |
Отрицательное число разрядов округляет до десятков, сотен, тысяч. Доле задан процентный формат. Подробнее — округление.
В английском Excel: =ROUND(A2,-2)
Помимо математических операций, бывают проблемы при переводе текста в числа и наоборот. Поэтому здесь же доступны процедуры, которые помогут
- Преобразовать числа, выглядящие как текст, в настоящие числа
- Написать числа прописью
- Написать суммы прописью (с копейками) в различных вариантах
| A | B | |
|---|---|---|
| 1 | Число как текст | Настоящее число |
| 2 | 1200 | 1200=--A2 |
| 3 | 12,5 | 12,5=--A3 |
Два минуса заставляют Excel пересчитать текст как число. Если в тексте пробелы-разделители, сначала уберите их ПОДСТАВИТЬ. Подробнее — числа как текст.
В английском Excel: =--A2
Изменить диапазоны
Целые диапазоны ячеек тоже можно подвергать трансформациям:
- Можно сортировать содержимое диапазона как обычной сортировкой, так и сортировкой по окончаниям;
- А можно преобразовывать таблицы в сводки в 1 клик - просуммировать данные или склеить данные строк (произвести конкатенацию).
| A | B | C | |
|---|---|---|---|
| 1 | Фрукт | По алфавиту | |
| 2 | груша | апельсин=СОРТ(A2:A5) | |
| 3 | апельсин | банан | |
| 4 | банан | груша | |
| 5 | яблоко | яблоко |
СОРТ (Excel 365, 2021) выводит отсортированную копию, исходный порядок не меняется. Подробнее — изменить диапазоны.
В английском Excel: =SORT(A2:A5)
Массовая замена значений
Меню предоставляет возможности массовой замены значений в выделенном диапазоне:
- слов и списков слов;
- любых паттернов по вхождению;
- по регулярному выражению.
| A | B | |
|---|---|---|
| 1 | Статус | После замены |
| 2 | new | новый=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(" "&A2&" ";" new ";" новый ");" paid ";" оплачен ")) |
| 3 | paid | оплачен=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(" "&A3&" ";" new ";" новый ");" paid ";" оплачен ")) |
| 4 | new paid | новый оплачен=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(" "&A4&" ";" new ";" новый ");" paid ";" оплачен ")) |
Каждая пара «старое — новое» — ещё один уровень ПОДСТАВИТЬ. Для длинных списков удобнее массовая замена.
Изменение данных в Excel — частые вопросы
Как изменить сразу много ячеек?
Формулой в соседнем столбце с последующей вставкой значений, через Ctrl+H для замен или мгновенным заполнением Ctrl+E по образцу.
Как поменять регистр букв?
Функциями =ПРОПИСН(A2), =СТРОЧН(A2) и =ПРОПНАЧ(A2) — подробнее в статье изменить регистр.
Как умножить весь столбец на число?
Формулой =B2*$D$2 или специальной вставкой «Умножить».
Как заменить несколько значений сразу?
Вложенными ПОДСТАВИТЬ или массовой заменой по списку.
Изменить регистр, слова, ячейки, числа и диапазоны, массово заменить значения по списку или регулярному выражению — прямо на месте, без формул — можно в пару кликов с !SEMTools.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: как изменить данные в Excel
| Что меняем | Формула |
|---|---|
| Регистр | ПРОПИСН, СТРОЧН, ПРОПНАЧ |
| Символ или фрагмент | ПОДСТАВИТЬ |
| Содержимое ячейки | &, ПСТР + ЛЕВСИМВ |
| Числа | +, *, ОКРУГЛ, -- |
| Порядок в диапазоне | СОРТ, СОРТПО |
| Список замен | вложенные ПОДСТАВИТЬ |
Надеюсь, на этой странице вы нашли то, что искали. Формулы удобны, когда исходные данные нужно сохранить, а для правки на месте и регулярных задач быстрее процедуры раздела ИЗМЕНИТЬ.