Формула автозамены в excel

Формула автозамены в excel

Автор: rf-webmaestro · Опубликовано 18.05.2018 · Обновлено 08.02.2019

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

Настройка автозамены

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

  1. Перейдите на вкладку «Файл».

  1. Перейдите в категорию «Правописание».
  2. Нажмите на кнопку «Параметры автозамены».

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

Автозамена

На этой вкладке вы сможете включить или отключить следующие функции:

  • исправление двух прописных букв в начале слова;
  • автоматическая замена первых букв на заглавные;
  • вывод наименований дней недели с большой буквы;
  • корректировка ввода из-за случайно нажатой клавиши Caps Lock ;
  • активация функции замены указанных выражений.

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

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

Список замены при вводе

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

Для этого достаточно сделать следующее:

  1. Введите нужную комбинацию.
  2. Укажите результат замены.
  3. Нажмите на кнопку «Добавить».
  4. Теперь после этого каждый раз при вводе исходного сочетания символов будет происходить замена на целое слово.

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

Автоформат при вводе

Здесь вы сможете настроить:

  • замену сетевых и интернет-адресов на гиперссылки;
  • добавление новых строк и столбцов;
  • создание вычисляемого столбца с автоматическим заполнением формулой.

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

Автозамена математическими символами

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

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

  1. Укажите исходное слово. Как правило, оно начинается с символа бэкслеш «».
  2. Введите нужный результат.
  3. Нажмите на кнопку «Добавить».
  4. Для сохранения настроек нужно будет кликнуть на кнопку «OK».

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

Проблемы с изменением чисел на дату

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

  1. Попробуйте ввести какое-нибудь число с точкой. Например, «1.1».

  1. Нажмите на клавишу Enter .
  2. В итоге вы увидите, что в ячейке число «1.1» изменилось на «01.янв».

Обратите внимание на то, что, если бы вы написали не «1.1», а «1,1», то ничего бы не произошло. То есть запятая по умолчанию служит для указания дробных чисел. А «точка» – нет.

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

  1. Первым делом делаем активной какую-нибудь клетку.
  2. Затем кликаем по ней.
  3. После этого выбираем пункт «Формат ячеек».

Либо просто нажмите на горячие клавиши Ctrl + 1 . В обоих случаях результат будет одинаковый.

  1. В появившемся окне нужно указать либо числовой, либо текстовый формат. То есть ваше значение может быть цифрой или обычной строчкой. В этом случае автозамена на дату происходить не будет.
  2. Для сохранения изменений нажмите на кнопку «OK».
Читайте также:  Как убрать пробел в тексте между словами

  1. Попробуйте еще раз ввести какое-нибудь числовое значение с использованием точки. Вы увидите, что никаких изменений не произойдет.

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

Отличие версий Excel

Описанные выше инструкции подходят только для современных программ 2007, 2010, 2013 и 2016 годов. В старом редакторе 2003 года процесс настройки автозамены происходит немного иначе. Более того, даже нужные разделы меню находятся совсем в другом месте.

  1. Нажмите на меню «Сервис».
  2. Выберите пункт «Параметры».

  1. Перейдите на вкладку «Орфография».

  1. Здесь вы сможете настроить:
    • пропуск слов из заглавных букв;
    • пропуск слов, в состав которых входят цифры;
    • пропуск различных ссылок.

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

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

    Заключение

    В этой статье мы рассказали, какими способами можно настроить автозамену в редакторе Excel. Так же показали, как нужно менять формат ячеек. Если у вас что-то не получается, возможно, вы допустили опечатку во время добавления значений в таблицу «Заменять при вводе». Если вбить хотя бы один неправильный символ, никакая замена происходить не будет. Будьте очень внимательны.

    Видеоинструкция

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

    При наборе текста в различных документах можно совершить опечатку или допустить ошибку по незнанию. Кроме того, некоторые знаки на клавиатуре просто отсутствуют, а как включаются спецсимволы, и как ими пользоваться, не каждый знает. Поэтому, пользователи заменяют подобные знаки наиболее очевидными, по их мнению, аналогами. Например, вместо «©» пишут «(с)», а вместо «€» — (e). К счастью, в Microsoft Excel имеется функция автозамены, которая автоматически заменяет вышеописанные примеры на правильные соответствия, а также исправляет наиболее распространенные ошибки и опечатки.

    Принципы работы автозамены

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

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

    Отключение и включение автозамены

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

      Переходим во вкладку «Файл»;

    Выбираем раздел «Параметры».

    Кликаем по кнопке «Параметры автозамены».

  2. В открывшемся окне параметров ищем пункт «Заменять при вводе». Снимаем с него галочку и жмем на кнопку «OK».
  3. Для того, чтобы опять включить автозамену, соответственно, устанавливаем галочку обратно и снова жмем на кнопку «OK».

    Проблема с автозаменой даты

    Бывают случаи, когда пользователь вводит число с точками, а оно автоматически исправляется на дату, хотя это ему не нужно. В этом случае, совсем не обязательно полностью отключать автозамену. Чтобы исправить это, выделяем область ячеек, в которой собираемся писать цифры с точками. Во вкладке «Главная» ищем блок настроек «Число». В выпадающем списке, расположенном в данном блоке, устанавливаем параметр «Текстовый».

    Теперь числа с точками заменяться на даты не будут.

    Редактирование списка автозамены

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

    1. Открываем знакомое уже нам окно параметров автозамены.
    2. В поле «Заменять» указываем набор символов, который будет восприниматься программой как ошибочный. В поле «На» пишем слово или символ, на которое будет происходить замена. Жмем на кнопку «Добавить».

    Таким образом, можно добавлять в словарь собственные варианты.

    Читайте также:  Как убрать аватарку в ватсапе

    Кроме того, в этом же окне существует вкладка «Автозамена математическими символами». Тут представлен список значений, при вводе заменяемых на математические символы, в том числе на те, которые используются в формулах Excel. Действительно, не каждый пользователь сможет ввести знак α (альфа) на клавиатуре, но каждый сможет ввести значение «alpha», которое автоматически преобразится в нужный символ. По аналогии пишется бета (eta), и другие знаки. В этот же список каждый пользователь может добавить свои собственные соответствия, так же как это было показано в основном словаре.

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

    Удаление будет выполнено моментально.

    Основные параметры

    В основной вкладке параметров автозамены расположены генеральные настройки данной функции. По умолчанию включены следующие функции: исправление двух прописных букв подряд, установка первой буквы в предложении прописной, название дней недели с прописной, исправление случайного нажатия Caps Lock. Но, все эти функции, как и отдельные из них, можно отключить, просто сняв галочки около соответствующих параметров и нажав на кнопку «OK».

    Исключения

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

    Для перехода в этот словарь жмем на кнопку «Исключения…».

    Открывается окно исключений. Как видим, в нем имеется две вкладки. В первой из них расположены слова, после которых точка, ещё не означает конец предложения, и то, что следующее слово должно начинаться с прописной буквы. Это, главным образом, различные сокращения (например, «руб.»), или части устойчивых выражений.

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

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

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

    Функция ПОДСТАВИТЬ при условии подставляет значение

    Пример 1. В результате расчетов, произведенных в некотором приложении, были получены некоторые значения, записанные в таблицу Excel. Некоторые величины рассчитать не удалось, и вместо числового представления была сгенерирована ошибка “NaN”. Необходимо заменить все значения “NaN” на число 0 в соответствующих строках.

    Для замены и подстановки используем рассматриваемую формулу в качестве массива. Вначале выделим диапазон ячеек C2:C9, затем введем формулу через комбинацию Ctrl+Shift+Enter:

    Функция ЧЗНАЧ выполняет преобразование полученных текстовых строк к числовым значениям. Описание аргументов функции ПОДСТАВИТЬ:

    • B2:B9 – диапазон ячеек, в которых требуется выполнить замену части строки;
    • “NaN” – фрагмент текста, который будет заменен;
    • 0 – фрагмент, который будет вставлен на место заменяемого фрагмента.

    Для подстановки значений во всех ячейках необходимо нажать Ctrl+Shift+Enter, чтобы функция была выполнена в массиве. Результат вычислений:

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

    Автозамена значения в текстовых ячейках с помощью функции ПОДСТАВИТЬ

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

    Читайте также:  При вставке текста в word иероглифы

    Для выполнения заданного условия используем формулу:

    Примечание: в данном примере ПОДСТАВИТЬ также используется в массиве Ctrl+Shift+Enter.

    В результате получим:

    Формула с макросом регулярного выражения и функция ПОДСТАВИТЬ

    Пример 3. При составлении таблицы из предыдущего примера была допущена ошибка: все номера домов на улице Никольская должны быть записаны как «№№-Н», где №№ — номер дома. Как быстро исправить ошибку?

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

    1. Открыть редактор макросов (Ctrl+F11).
    2. Вставить исходный код функции (приведен ниже).
    3. Выполнить данный макрос и закрыть редактор кода.

    Public Function RegExpExtract(Text As String , Pattern As String , Optional Item As Integer = 1) As String
    On Error GoTo ErrHandl
    Set regex = CreateObject( "VBScript.RegExp" )
    regex.Pattern = Pattern
    regex. Global = True
    If regex.Test(Text) Then
    Set matches = regex.Execute(Text)
    RegExpExtract = matches.Item(Item — 1)
    Exit Function
    End If
    ErrHandl:
    RegExpExtract = CVErr(xlErrValue)
    End Function

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

    Регулярные выражения могут быть различными. Например, для выделения любого символа из текстовой строки в качестве второго аргумента необходимо передать значение «w», а цифры – «d».

    Для решения задачи данного Примера 3 используем следующую запись:

    1. Функция ЕСЛИОШИБКА используется для возврата исходной строки текста (B2), поскольку результатом выполнения функции RegExpExtract(B2;"Никольская") будет код ошибки #ЗНАЧ!, если ей не удалось найти хотя бы одно вхождение подстроки «Никольская» в строке B2.
    2. Если результат выполнения сравнения значений RegExpExtract(B2;"Никольская")="Никольская" является ИСТИНА, будет выполнена функция ПОДСТАВИТЬ(B2;RegExpExtract(B2;"d+");RegExpExtract(B2;"d+")&"-Н"), где:
    • a. B2 – исходный текст, содержащий полный адрес;
    • b. RegExpExtract(B2;"d+") – формула, выделяющая номер дома из строки с полным адресом;
    • c. RegExpExtract(B2;"d+")&"-Н" – новый номер, содержащий исходное значение и символы «-Н».

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

    Особенности использования функции ПОДСТАВИТЬ в Excel

    Функция ПОДСТАВИТЬ имеет следующую синтаксическую запись:

    • текст – обязательный аргумент, характеризующий текстовую строку, в которой необходимо выполнить замену части текста. Может быть задан как текстовая строка («некоторый текст») или ссылка на ячейку, которая содержит текстовые данные.
    • стар_текст – часть текстовой строки, принимаемой в качестве первого аргумента данной функции, которую требуется заменить. Аргумент обязателен для заполнения.
    • нов_текст – обязательный для заполнения аргумент, содержащий текстовые данные, которые будут вставлены на место заменяемой части строки.
    • [номер_вхождения] – числовое значение, характеризующее номер вхождения старого текста, который требуется заменить на фрагмент нового текста. Возможные варианты записи:
    1. Аргумент явно не указан. Функция ПОДСТАВИТЬ определит все части текстовой строки, соответствующие фрагменту текста стар_текст, и выполнит их замену на нов_текст;
    2. В качестве аргумента передано числовое значение. Функция ПОДСТАВИТЬ заменит только указанное вхождение. Отсчет начинается слева направо, число 1 соответствует первому вхождению. Например, функция =ПОДСТАВИТЬ(«текст №1, №2, №3»;«текст»;«новый»;1) вернет значение «новый_текст №1, №2, №3».

    Примечания:

    1. Аргумент [номер_вхождения] должен быть задан из диапазон целых положительных чисел от 1 до n, где n определяется максимально допустимой длиной строки, содержащейся в объекте данных (например, в ячейке).
    2. Если в текстовой строке, представленной в качестве аргумента текст не содержится фрагмент, переданный в качестве аргумента стар_текст, функция ПОДСТАВИТЬ вернет строку текст без изменений.
    3. Если число вхождений заменяемого фрагмента в обрабатываемой строке меньше, чем числовое значение, переданное в качестве аргумент [номер_вхождения], функция ПОДСТАВИТЬ вернет текстовую строку в исходном виде. Например, аргументы функции («а 1 а 2 а 3»;«а»;«б»;4) вернут строку «а 1 а 2 а 3».
    4. Рассматриваемая функция чувствительная к регистру, то есть строки «Слово» и «слово» не являются тождественными.
    5. Для решения аналогичных задач по замене части символов текстовой строки можно использовать функцию ЗАМЕНИТЬ. Однако, в отличие от функции ПОДСТАВИТЬ, для ее использования необходимо явно указывать позицию начального символа для замены, а также количество символов, которые необходимо заменить. Функция автоматически выполняет поиск указанной части строки и производит ее замену, поэтому в большинстве случаев предлагает более удобный функционал для работы с текстовыми строками.
    Ссылка на основную публикацию
    Усики для автомобильной антенны
    Убираясь в бардачке я наткнулся на ремкомплект антенных усиков — лежит наверно уже полгода, всё наклеить не могу, то забываю,...
    Телефонный шлюз что это
    VoIP-шлюз — это межсетевой шлюз, предназначенный для перевода трафика между сетями различных типов. VoIP-шлюзы можно разделить на многоканальные и одноканальные:...
    Телефонная клавиатура на компьютере
    Виртуальная клавиатура выручит Вас, когда выйдет из строя основное физическое устройство ввода, полностью или частично ( поломается несколько клавиш )....
    Усиление сигнала интернета на даче своими руками
    С наступление дачного сезона, я озадачился установкой хорошего скоростного интернет на даче, у нас голосовая связь работает без проблем, а...
    Adblock detector