Как то ко мне, обратился знакомый с просьбой помочь настроить Excel, а именно, в столбцах поменять цифры на буквы . В общем, с флешки на его компьютер скинули файлик.xsls, который в дальнейшем нужно было отредактировать и посчитать все нужные цифры с помощью формул. Но первое удивление было в том, что при открытие документа вместо привычных букв в название столбца были цифры, но не придав этому никакого значения он продолжал работать дальше.
Когда пришло время вбивать формулы для расчётов, ничего не получалось. Excel ничего считать не хотел, а в формулах при вводе ставились не те цифры которые были нужны. Нажимая на столбик №5 в формуле показывалась цифра «–7», после чего вылетало сообщение о том, что в формулах нельзя использовать знак «-». Обратившись ко мне, помочь вернуть былой вид, что бы все было как раньше, я начал искать как же поменять цифры на буквы, но так и не нашёл в настройках пункт «Изменение заголовка столбцов».
Поковырявшись в настройках я решил переустановить Excel, результатов это не принесло, хотя, возможно, причиной было то что, я не почистил папку Aplication Data\Microsoft в профиле пользователя. Разочаровавшись, что переустановка не помогла я вернулся к подробному изучению настроек. Спустя некоторое время, настройки которые меняют названия столбцов были найдены. И для того, что бы на все это потратить несколько минут, смотрите ниже где они находятся.
Меняем имена столбцов с цифр на буквы в Еxcel 2003 и 2007—2013.
Для изменения в Microsoft Office 2003 нам нужно, открыв офис перейти на верхнюю панель окна и нажав «Сервис » выбрать «Параметры » после чего в открывшемся окне перейти на вкладку «Общие ». Именно в этом меню снимаем галочку с пункта «Стиль ссылок R1C1 ».
В Excel начиная с 2007 по 2013, меню было немного изменено, но сам принцип замены цифр на буквы не был затронут. В общем нажав на «Файл » –> «Параметры » –> «Формулы » и перейдя к параметрам работы с формулами так же убираем галочку с «Стиль ссылок R1C1 ».
На самом деле все достаточно просто и делается в несколько кликов.
Для тех кто любит прописывать вручную или возможно по какой-то причине Вы не можете проклацать пункты меню которые рассматривались выше, приведу пример как это можно сделать указав нужную команду.
Для возвращения к цифровому виду набираем следующее: Application.ReferenceStyle=xlR1C1. Какой вариант использовать, выбор за вами. Если что-то не получилось оставляйте Ваши вопросы в комментариях и не забудьте
Excel обладает мощным инструментом поиска и замены, которая позволяет быстро находить необходимую информацию на листах или во всей рабочей книге. Ко всему прочему существует возможность поиска текста, и замены его другим.
Чтобы воспользоваться диалоговым окном Найти и заменить, выделите диапазон ячеек, в котором вы хотите произвести поиск (если будет выделена только одна ячейка, Excel будет искать во всем листе). Перейдите по вкладке Главная в группу Редактирование, выберите Найти и выделить -> Найти (или нажмите сочетание клавиш Ctrl + F).
В появившемся диалоговом окне Найти и заменить, вы можете воспользоваться вкладкой Найти, если вам просто требуется найти какую-нибудь информацию, либо вкладкой Заменить, если вы хотите заменить текст. Обратите внимание, что вы можете переключиться в расширенный режим поиска, нажав на кнопку Параметры.
Поиск информации
Введите искомую информацию в поле Найти и укажите требуемые параметры поиска:
- Выберите место поиска в выпадающем меню Искать (на листе, в книге)
- В выпадающем меню Просматривать, укажите Excel вариант просмотра по строкам или по столбцам.
- Задайте Область поиска в формулах, значениях или примечаниях
- С помощью опций Учитывать регистр и Ячейка целиком, вы можете указать, следует ли производить поиск с учетом регистра символов и ищется ли все содержимое ячейки или только фрагмент.
Кнопка Найти далее будет находить и выделять по одной совпавшей ячейке за одно нажатие. Если вы нажмете кнопку Найти все, Excel отобразить список с адресами всех найденных ячеек. Выделяя элементы в этом списке, будут выделяться соответствующие им ячейки на листе.
Замена информации
Чтобы заменить текст в ячейке Excel другим текстом, воспользуйтесь вкладкой Заменить в диалоговом окне Найти и заменить. В поле Найти введите текст, который необходимо заменить, а в поле Заменить на текст, на который требуется поменять. При необходимости, воспользуйтесь расширенными параметрами замены, описанными выше.
Щелкните по кнопке Найти далее, чтобы Excel выделил первую совпавшую ячейку, затем Заменить для замены. После щелчка, Excel выделит следующую совпавшую ячейку. Чтобы пропустить замещение, щелкните Найти далее. Для замены всех элементов без проверки,щелкните Заменить все. Если замещениепошлоне по плану, вы можете отменить действие с помощью сочетания клавиш Ctrl + Z.
Поиск форматов
Помимо текста вы можете найти ячейки отформатированные определенным образом. Как вариант, вы также можете заменить форматирование ячейки на другое. Предположим, что необходимо определить все ячейки с жирным текстом и заменить их на жирный и курсив. Перейдите по вкладке Главная в группу Редактирование, выберите Найти и выделить -> Заменить (или нажмите сочетание клавиш Ctrl + H). В появившемся диалоговом окне перейдите во вкладку Заменить и щелкните по кнопке Параметры, чтобы отобразить расширенные опции замены. Щелкните по верхней кнопке Формат.
В появившемся диалоговом окне, перейдите во вкладку Шрифт и выберите Полужирное начертание. Щелкните ОК.
Те же шаги проделайте для замещающего формата (нижняя кнопка Формат ), отличием будет только выбор начертания Полужирный курсив в диалоговом окне Найти формат.
Щелкните по кнопке Заменить все, Excel заменит текст с жирным начертанием на жирный курсив.
Хитрости использования Найти и заменить Excel
Замена переноса строки (Alt+Enter) в ячейке
Предположим, у вас имеется таблица с ячейками, данные в которых перенесены по строкам с помощью сочетания клавиш Alt+Enter и требуется заменить символ переноса строк (каретку) на пробел.
Для этого вызываем диалоговое окно Найти и заменить сочетанием клавиш Ctrl+H. В поле Найти вводим Ctrl+J (внешне поле останется пустым), в поле Заменить на вводим Пробел (или любой другой символ).
Щелкаем Заменить все и, о чудо, все наши каретки превратились в пробелы!
Мы уже разбирали как быстро данные в Excel по группе ячеек — в столбце или массиве данных. Но как это сделать в одной ячейке и желательно формулой? К примеру у вас есть список машин, и каждый день вам присылают файл с новым списком машин, нужно проверить есть ли такие номера в вашем основном списке. Но как обычно в номера машин добавляют пробелы или слеши, а самый худший вариант букву О вместо нуля Если вы регулярно выполняете одну и ту же операцию, то вручную нажимать Ctrl + H каждый раз неудобно, плюс придется запоминать какие именно символы нужно заменять. Функции ПОДСТАВИТЬ и ЗАМЕНИТЬ в Excel это отличный выход из ситуации.
Эти функции немного обделены вниманием, не знаю почему, но уверен они супер полезны для обработки и сведения больших массивов данных, ведь ими можно легко настроить автозамену. Ниже расскажу поподробнее о каждый и чем все же они различаются. Разбирать будем на примере.
Есть номер А 777 АА 01 из него надо формулой сделать А777АА01 формулой.
Как работает формула показано на первой картинке. Состав разберем ниже:
ПОДСТАВИТЬ(текст;стар_текст;нов_текст;[номер_вхождения])
- Текст — адрес ячейки или сам текст в котором надо сделать замену символов, в примере А 777 АА 01
- Стар_текст — что нам нужно заменить? Пробел.
- Нов_текст — на что нужно заменить? Меняем на ничего, т.е. на пусто в формуле, это будет «»
- Номер_вхождения — заполнять необязательно, но при этом реквизит очень полезный, в официальной справке говорится о «вхождении символа», но объясню простым языком. Здесь вы можете указать номер символа в строке (пробела в нашем случае). Т.е. если нужно заменить только пробел между 777 и АА (это второй по счету пробел в номере), формула будет выглядеть как
И в итоге вы получите А 777АА 01
Но как правило я использую вот такие конструкции, чтобы убрать все ненужные символы, которые уже понаставили при заполнении.
ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A:A;" ";"");".";"");"/";"")
В данном случае мы убираем пробел, точку, и слэш в номере «А 777 АА/01.»
Функция ЗАМЕНИТЬ и ЗАМЕНИТЬБ в Excel
Функция ЗАМЕНИТЬ делает почти все тоже самое, что и подставить, но делает это с определенного символа. Аргументы такие:
ЗАМЕНИТЬ(стар_текст;начальная_позиция;число_знаков;нов_текст)
Пример. Необходимо первую букву номера машины заменить на другую. Из А777АА01 сделать Б777АА01
Сначала описание аргументов:
- Стар_текст — как и в ПОДСТАВИТЬ, текст в котором нужно заменить т.е. А777АА01
- Начальная_позиция — с какого символа производить замену, с первого.
- Число_знаков сколько знаков меняем — один
- Нов_текст — и на что меняем? На букву Б
Очень похоже на , честно говоря:)
ЗАМЕНИТЬБ — это маленькая загадка Excel, даже в иностранных интернетах мало информации о ней. Если вкратце, то просто ЗАМЕНИТЬ меняет любой символ (даже если он размером 2 байта), ЗАМЕНИТЬБ меняет только однобайтные символы (аргумент число_знаков меняется на). Практического применения я для себя не нашел:) Если кто-нибудь подскажет, буду благодарен!
UPD. Как мне подсказали, к двухбайтовым символам относятся большинство языков с ироглифами!
ПОДСТАВИТЬ и ЗАМЕНИТЬ в Excel. Что еще?
Обе функции учитывают регистр! Т.е. если вы указали большую букву А, маленькая буква заменяться не будет!
Функция ПОДСТАВИТЬ доступна только в версиях 2007 и выше, будьте аккуратны если конечный пользователь будет использовать более ранние версии!
Поделитесь нашей статьей в ваших соцсетях:Функция ЗАМЕНИТЬ() , английский вариант REPLACE(), замещает указанную часть знаков текстовой строки другой строкой текста. "Указанную часть знаков" означает, что нужно указать начальную позицию и длину заменяемой части строки. Функция используется редко, но имеет плюс: позволяет легко вставить в указанную позицию строки новый текст.
Синтаксис функции
ЗАМЕНИТЬ (исходный_текст ;нач_поз ;число_знаков ;новый_текст )
Исходный_текст
- текст, в котором требуется заменить некоторые знаки.
Нач_поз
- позиция знака в Исходном_тексте
, начиная с которой знаки заменяются текстом новый_текст
.
Число_знаков
- число знаков в Исходном_тексте
, которые заменяются текстом новый_текст
.
Новый_текст
- текст, который заменяет знаки в Исходном_тексте
.
Функция ЗАМЕНИТЬ() vs ПОДСТАВИТЬ()
Функция ПОДСТАВИТЬ() используется, когда нужно заменить определенный текст в текстовой строке; функция ЗАМЕНИТЬ() используется, когда нужно заменить любой текст начиная с определенной позиции.
При замене определенного текста функцию ЗАМЕНИТЬ() использовать неудобно. Гораздо удобнее воспользоваться функцией ПОДСТАВИТЬ() .
Пусть в ячейке А2 введена строка Продажи (январь) . Чтобы заменить слово январь , на февраль , запишем формулы:
т.е. для функции ЗАМЕНИТЬ() потребовалось вычислить начальную позицию слова январь (10) и его длину (6). Это не удобно, функция ПОДСТАВИТЬ() справляется с задачей гораздо проще.
Кроме того, функция ЗАМЕНИТЬ()
заменяет по понятным причинам только одно вхождение строки, функция ПОДСТАВИТЬ()
может заменить все вхождения или только первое, только второе и т.д.
Поясним на примере. Пусть в ячейке А2
введена строка . Запишем формулы:
=ЗАМЕНИТЬ(A2;10;6;"февраль")
=ПОДСТАВИТЬ(A2; "январь";"февраль")
получим в первом случае строку Продажи (февраль), прибыль (январь)
, во втором - Продажи (февраль), прибыль (февраль)
.
Записав формулу =ПОДСТАВИТЬ(A2; "январь";"февраль";2)
получим строку Продажи (январь), прибыль (февраль)
.
Кроме того, функция ПОДСТАВИТЬ() чувствительна к РЕгиСТру. Записав =ПОДСТАВИТЬ(A2; "ЯНВАРЬ";"февраль") получим строку без изменений Продажи (январь), прибыль (январь) , т.к. для функции ПОДСТАВИТЬ() "ЯНВАРЬ" не тоже самое, что "январь".
Использование функции для вставки нового текста в строку
Функцию ЗАМЕНИТЬ()
удобно использовать для вставки в строку нового текста. Например, имеется перечень артикулов товаров вида "ID-567(ASD)
", необходимо перед текстом ASD
вставить новый текст Micro
, чтобы получилось "ID-567(MicroASD)
". Для этого напишем простую формулу:
=ЗАМЕНИТЬ(A2;8;0;"Micro").
Если вам прислали файл, а там вместо букв в столбцах цифры, а ячейки в формулах задаются странным сочетанием чисел и букв R и C, это несложно поправить, ведь это специальная возможность — стиль ссылок R1C1 в Excel. Бывает, что он устанавливается автоматически. Поправляется это в настройках. Как и для чего он пригодиться? Читайте ниже.
Стиль ссылок R1C1 в Excel. Когда вместо букв в столбцах появились цифры
Если вместо названия столбцов (A, B, C, D…) появляются числа (1, 2, 3 …), см. первую картинку — это тоже может вызвать недоумение и опытного пользователя. Так чаще всего бывает, когда файл вам присылают по почте. Формат автоматически устанавливается как R1C1, от R ow=строка, C olumn=столбец
Так называемый стиль ссылок R1C1 удобен для работы по программированию в VBA, т.е. для написания .
Как же изменить обратно на буквы? Зайдите в меню - Левый верхний угол - Параметры Excel - Формулы - раздел Работа с формулами - Стиль ссылок R1C1 - снимите галочку.
Для Excel 2003 Сервис — Параметры — вкладка Общие — Стиль ссылок R1C1.
R1C1 как можем использовать?
Для понимания приведем пример
Вторая удобная возможность записать адрес в зависимости от ячейки, в которой мы это запишем. Добавим квадратные скобки в формулу
Как мы видим, ячейка формулы отстаит от ячейки записи на 2 строки и 1 столбец
Согласитесь возможность может пригодиться.
Для чего это нужно?
Как мы сказали раньше, формат удобен для программирования. Например, при записи сложения двух ячеек в коде вы можете не видеть саму таблицу, удобнее будет записать формулу по номеру строки/столбца или отстаящую от этой ячейки (см пример выше).
Если ваша таблица настолько огромна, что количество столбцов перевалило за 100, то вам удобнее будет видеть номер столбца 131, чем буквы EA, как мне кажется.
Если привыкнуть к такому типу ссылок, то найти ошибку при включении режима просмотра найти ошибку будет значительно проще и нагляднее.