В excel изменить значение ячейки по формуле

На чтение 9 мин Просмотров 7.5к.

Содержание

  1. Функция ПОДСТАВИТЬ при условии подставляет значение
  2. Автозамена значения в текстовых ячейках с помощью функции ПОДСТАВИТЬ
  3. Формула с макросом регулярного выражения и функция ПОДСТАВИТЬ
  4. Особенности использования функции ПОДСТАВИТЬ в Excel
  5. Быстрая замена значений по условию в Excel
  6. Задача
  7. Замена текстовых значений
  8. Чтобы заменить текстовые значения:
  9. Замена числовых, логических значений или значений даты и времени
  10. Чтобы заменить числовые, логические значения или значения даты и времени:

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

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

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

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

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

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

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

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

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

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

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

Примечание: в данном примере ПОДСТАВИТЬ также используется в массиве 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. Для решения аналогичных задач по замене части символов текстовой строки можно использовать функцию ЗАМЕНИТЬ. Однако, в отличие от функции ПОДСТАВИТЬ, для ее использования необходимо явно указывать позицию начального символа для замены, а также количество символов, которые необходимо заменить. Функция автоматически выполняет поиск указанной части строки и производит ее замену, поэтому в большинстве случаев предлагает более удобный функционал для работы с текстовыми строками.

Быстрая замена значений по условию в Excel

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

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

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

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

  • вкладка «Разработчик», блок кнопок «Код»;
  • жмём «Visual Basic»;
  • добавляем новый модуль Insert -> Module.

Вставляем следующий код.

Sub Result()
Dim cell As Range
‘ Проверка каждой ячейки диапазона на прохождение
For Each cell In Range(«I2:L10»).Cells
If cell.Value
cell.Value = «Не прошёл»
ElseIf cell.Value >= 4 Then
cell.Value = «Прошёл»
End If
Next
End Sub

В этом случае используется связка из команд IF и ElseIf, что позволяет нам несколько расширить понимание формулы ЕСЛИ. Напомню, что макрос будет применён только к ячейкам из диапазона I2:L10!

Запускаем выбор макросов с помощью сочетания клавиш Alt+F8 и нажимаем кнопку «Выполнить».

Получаем результат. Во второй таблице я сразу вижу кто из учеников прошёл дальше, а кто нет. Ну и кому что необходимо подтянуть:)

Таким образом решается некая ограниченность формулы ЕСЛИ, ведь условия в макросе можно продолжить и подобрать свои, всего лишь изменив значения.

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

Примечание: Компонент Power Query в Excel 2016 называется Скачать и преобразовать. Информация в этой статье относится к обоим компонентам. Дополнительные сведения см. в статье Функция «Скачать и преобразовать» в Excel 2016.

Примечание: В конце этой статьи есть небольшое видео о том, как вывести редактор запросов.

В Power Query можно заменить одно значение другим в выбранных столбцах.

Задача

Замена текстовых значений

С помощью ленты редактора запросов

На ленте Редактора запросов выберите на вкладке Преобразование Замена значений.

С помощью контекстного меню Редактора запросов:

Щелкните текстовую ячейку правой кнопкой мыши и выберите команду Замена значений.

Чтобы заменить текстовые значения:

В диалоговом окне Замена значений введите значения в поля Значение для поиска и Заменить на.

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

Нажмите кнопку ОК.

Замена числовых, логических значений или значений даты и времени

С помощью ленты редактора запросов

На ленте Редактора запросов, на вкладке « преобразовать » выберите Замена значений.

С помощью контекстного меню Редактора запросов:

Щелкните ячейку с числовым значением или значением даты и времени правой кнопкой мыши и выберите команду Замена значений.

Чтобы заменить числовые, логические значения или значения даты и времени:

В диалоговом окне Замена значений введите значения в поля Значение для поиска и Заменить на.

Нажмите кнопку ОК.

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

Источник

В этой статье описаны синтаксис формулы и использование ЗАМЕНИТЬ и ЗАМЕНИТЬБ в Microsoft Excel.

Описание

Функция ЗАМЕНИТЬ заменяет указанное число символов текстовой строки другой текстовой строкой.

Функция ЗАМЕНИТЬ заменяет часть текстовой строки, соответствующую заданному числу байтов, другой текстовой строкой.

Эти функции могут быть доступны не на всех языках.

Функция ЗАМЕНИТЬ предназначена для языков с однобайтовой кодировкой, а ЗАМЕНИТЬБ — для языков с двухбайтовой кодировкой. Язык по умолчанию, заданный на компьютере, влияет на возвращаемое значение следующим образом.

Функция ЗАМЕНИТЬ всегда считает каждый символ (одно- или двухбайтовый) за один вне зависимости от языка по умолчанию.

Функция ЗАМЕНИТЬБ считает каждый двухбайтовый символ за два, если включена поддержка ввода на языке с двухбайтовой кодировкой, а затем этот язык назначен языком по умолчанию. В противном случае функция ЗАМЕНИТЬБ считает каждый символ за один.

К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.

Синтаксис

Аргументы функций ЗАМЕНИТЬ и ЗАМЕНИТЬБ описаны ниже.

Стар_текст Обязательный. Текст, в котором требуется заменить некоторые символы.

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

Число_знаков Обязательный. Число символов в старом тексте, которые требуется ЗАМЕНИТЬ новым текстом.

Число_байтов Обязательный. Число байтов старого текста, который требуется ЗАМЕНИТЬБ новым текстом.

Нов_текст Обязательный. Текст, который заменит символы в старом тексте.

Пример

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

Функция ЗАМЕНИТЬ() в EXCEL

Функция ЗАМЕНИТЬ( ) , английский вариант REPLACE(), замещает указанную часть знаков текстовой строки другой строкой текста. «Указанную часть знаков» означает, что нужно указать начальную позицию и длину заменяемой части строки. Функция используется редко, но имеет плюс: позволяет легко вставить в указанную позицию строки новый текст.

Синтаксис функции

ЗАМЕНИТЬ ( исходный_текст ; нач_поз ; число_знаков ; новый_текст )

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

Функция ЗАМЕНИТЬ() vs ПОДСТАВИТЬ()

Функция ПОДСТАВИТЬ() используется, когда нужно заменить определенный текст в текстовой строке; функция ЗАМЕНИТЬ() используется, когда нужно заменить любой текст начиная с определенной позиции.

При замене определенного текста функцию ЗАМЕНИТЬ() использовать неудобно. Гораздо удобнее воспользоваться функцией ПОДСТАВИТЬ() .

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

=ЗАМЕНИТЬ(A2;10;6;»февраль») =ПОДСТАВИТЬ(A2; «январь»;»февраль»)

т.е. для функции ЗАМЕНИТЬ() потребовалось вычислить начальную позицию слова январь (10) и его длину (6). Это не удобно, функция ПОДСТАВИТЬ() справляется с задачей гораздо проще.

Кроме того, функция ЗАМЕНИТЬ() заменяет по понятным причинам только одно вхождение строки, функция ПОДСТАВИТЬ() может заменить все вхождения или только первое, только второе и т.д. Поясним на примере. Пусть в ячейке А2 введена строка Продажи (январь), прибыль (январь) . Запишем формулы: =ЗАМЕНИТЬ(A2;10;6;»февраль») =ПОДСТАВИТЬ(A2; «январь»;»февраль») получим в первом случае строку Продажи (февраль), прибыль (январь) , во втором — Продажи (февраль), прибыль (февраль) . Записав формулу =ПОДСТАВИТЬ(A2; «январь»;»февраль»;2) получим строку Продажи (январь), прибыль (февраль) .

Кроме того, функция ПОДСТАВИТЬ() чувствительна к РЕгиСТру. Записав =ПОДСТАВИТЬ(A2; «ЯНВАРЬ»;»февраль») получим строку без изменений Продажи (январь), прибыль (январь) , т.к. для функции ПОДСТАВИТЬ() «ЯНВАРЬ» не тоже самое, что «январь».

Использование функции для вставки нового текста в строку

Функцию ЗАМЕНИТЬ() удобно использовать для вставки в строку нового текста. Например, имеется перечень артикулов товаров вида » ID-567(ASD) «, необходимо перед текстом ASD вставить новый текст Micro , чтобы получилось » ID-567(MicroASD) «. Для этого напишем простую формулу: =ЗАМЕНИТЬ(A2;8;0;»Micro»).

Как сделать массовую замену формул

Скопированные формулы повторяют имеющиеся, но ссылаются не на те ячейки, которые нам нужны. Это происходит из-за того, что в одном листе (Форма2) для расчета показателя за год используется всего одна ячейка, а в другом листе (Форма1) — 5 ячеек.

Т.е. в части ссылок на лист «Форма2» ссылки при копировании автоматически идут на нужные ячейки, а в листе «Форма1» нам придется передвинуть формулы на 4 ячейки (причем речь идет о буквенных обозначениях, цифровые меняться не будут).

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

В нашем случае, второй столбик после копирования формулы ссылается в листе «Форма1» на ячейки J, K, L, M, N, в то время как для правильного результата должны ссылаться на M, N, O, P, Q. Как видим, две буквы повторяются. Их мы оставим. Значит, нам останется поменять J, K, L на O, P, Q.

А дальше все проще простого. Выделяем нужный столбец по серому полю (обязательно, иначе мы, не желая того, можем поменять значения других ячеек), вызываем функцию «поиска и замены» и меняем J на O, K на P, L на Q и т.д.

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

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

Источник

Skip to content

Формула ЗАМЕНИТЬ и ПОДСТАВИТЬ для текста и чисел

В статье объясняется на примерах как работают функции Excel ЗАМЕНИТЬ (REPLACE в английской версии) и ПОДСТАВИТЬ (SUBSTITUTE по-английски). Мы покажем, как использовать функцию ЗАМЕНИТЬ с текстом, числами и датами, а также как вложить несколько функций ЗАМЕНИТЬ или ПОДСТАВИТЬ в одну формулу.

Функции Excel ЗАМЕНИТЬ и ПОДСТАВИТЬ используются для замены одной буквы или части текста в ячейке. Но делают они это немного по-разному. Об этом и поговорим далее.

Как работает функция ЗАМЕНИТЬ  

Функция ЗАМЕНИТЬ  позволяет заместить слово, один или несколько символов в текстовой строке другим словом или символом.

ЗАМЕНИТЬ(старый_текст; начальная_позиция; число_знаков, новый_текст)

Как видите, функция ЗАМЕНИТЬ имеет 4 аргумента, и все они обязательны для заполнения.

  • Старый_текст — исходный текст (или ссылка на ячейку с исходным текстом), в котором вы хотите поменять некоторые символы.
  • Начальная_позиция — позиция первого символа в старый_текст, начиная с которого вы хотите сделать замену.
  • Число_знаков — количество символов, которые вы хотите заместить новыми.
  • Новый_текст – текст замены.

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

=ЗАМЕНИТЬ(«кит»;2;1;»о»)

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

=ЗАМЕНИТЬ(А2;2;1;»о»)

Примечание. Если аргументы начальная_позиция или число_знаков отрицательные или не являются числом, формула замены возвращает ошибку #ЗНАЧ!.

Использование функции ЗАМЕНИТЬ с числами

Функция ЗАМЕНИТЬ предназначена для работы с текстом. Но безусловно, вы можете использовать ее для замены не только букв, но и цифр, являющихся частью текстовой строки, например:

=ЗАМЕНИТЬ(A1; 9; 4; «2023»)

как заменить значения в ячейке Эксель

Обратите внимание, что мы заключаем «2023» в двойные кавычки, как вы обычно делаете с текстовыми значениями.

Аналогичным образом вы можете заменить одну или несколько цифр в числе. Например формула:

=ЗАМЕНИТЬ(A1;3;2;»23″)

И снова вы должны заключить значение замены в двойные кавычки («23»).

Примечание. Формула ЗАМЕНИТЬ всегда возвращает текстовую строку, а не число. На скриншоте выше обратите внимание на выравнивание по левому краю возвращаемого текстового значения в ячейке B1 и сравните его с исходным числом, выровненным по правому краю в A1. А поскольку это текст, вы не сможете использовать его в других вычислениях, пока не преобразуете его обратно в число, например, умножив на 1 или используя любой другой метод, описанный в статье Как преобразовать текст в число.

Как заменить часть даты

Как вы только что видели, функция ЗАМЕНИТЬ отлично работает с числами, за исключением того, что она возвращает текстовую строку :) Помните, что во внутренней системе Excel даты хранятся в виде чисел. Поэтому нельзя пытаться заменить часть даты, работая с ней как с текстом.

Например, у вас есть дата в A3, скажем, 15 июля 1992г., и вы хотите изменить «июль» на «май». Итак, вы пишете формулу ЗАМЕНИТЬ(A3; 4; 3; «Май»), которая предписывает Excel поменять 3 символа в ячейке A3, начиная с четвертого. Мы получили следующий результат:

Почему так? Потому что «15-июл-92» — это только визуальное представление базового серийного номера (33800), представляющего дату. Итак, наша формула замены заменяет цифры начиная с четвертой (а это два нуля) в указанном выше числе на текст «Май» и возвращает в результате текстовую строку «338Май».

Чтобы заставить функцию ЗАМЕНИТЬ правильно работать с датами, вы должны сначала преобразовать даты в текстовые строки, используя функцию ТЕКСТ. Кроме того, вы можете встроить функцию ТЕКСТ непосредственно в аргумент старый_текст функции ЗАМЕНИТЬ:

=ЗАМЕНИТЬ(ТЕКСТ(A3; «дд-ммм-гг»); 4; 3; «Май»)

Помните, что результатом приведенной выше формулы является текстовая строка, и поэтому это решение работает только в том случае, если вы не планируете использовать измененные даты в своих дальнейших расчетах. Если вам нужны даты, а не текстовые строки, используйте функцию ДАТАЗНАЧ , чтобы преобразовать значения, возвращаемые функцией Excel ЗАМЕНИТЬ, обратно в даты:

=ДАТАЗНАЧ(ЗАМЕНИТЬ(ТЕКСТ(A3; «дд-ммм-гг»); 4; 3; «Май»))

Как заменить сразу несколько букв или слов

Довольно часто может потребоваться выполнить более одной замены в одной и той же ячейке Excel. Конечно, можно было сделать одну замену, вывести промежуточный результат в дополнительный столбец, а затем снова использовать функцию ЗАМЕНИТЬ. Однако лучший и более профессиональный способ — использовать вложенные функции ЗАМЕНИТЬ, которые позволяют выполнить сразу несколько замен с помощью одной формулы. В этом смысле «вложение» означает размещение одной функции внутри другой.

Рассмотрим следующий пример. Предположим, у вас есть список телефонных номеров в столбце A, отформатированный как «123456789», и вы хотите сделать их более похожими на привычные нам  телефонные номера, добавив дефисы. Другими словами, ваша цель — превратить «123456789» в «123-456-789».

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

=ЗАМЕНИТЬ(A3;4;0;»-«)

Результат приведенной выше формулы замены выглядит следующим образом:

А теперь нам нужно вставить еще один дефис в восьмую позицию. Для этого вы помещаете приведенную выше формулу в еще одну функцию Excel ЗАМЕНИТЬ. Точнее, вы встраиваете её в аргумент старый_текст другой функции, чтобы вторая функция ЗАМЕНИТЬ обрабатывала значение, возвращаемое первой формулой, а не первоначальное значение из ячейки А3:

=ЗАМЕНИТЬ(ЗАМЕНИТЬ(A3;4;0;»-«);8;0;»-«)

В результате вы получаете номера телефонов в нужном формате:

Аналогичным образом вы можете использовать вложенные функции ЗАМЕНИТЬ, чтобы текстовые строки выглядели как даты, добавляя косую черту (/) там, где это необходимо:

=ЗАМЕНИТЬ(ЗАМЕНИТЬ(A3;3;0;»/»);6;0;»/»)

Кроме того, вы можете преобразовать текстовые строки в реальные даты, обернув приведенную выше формулу ЗАМЕНИТЬ функцией ДАТАЗНАЧ:

=ДАТАЗНАЧ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A3;3;0;»/»);6;0;»/»))

И, естественно, вы не ограничены в количестве функций, которые вы можете последовательно, как матрёшки, вложить друг в друга в одной формуле (современные версии Excel позволяют использовать до 8192 символов и до 64 вложенных функций в одной формуле).

Например, вы можете попробовать 3 вложенные функции ЗАМЕНИТЬ, чтобы число отображалось как дата и время:

=ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A3;3;0;»/»);6;0;»/»);9;0;» «);12;0;»:»)

Как заменить текст в разных местах

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

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

Но проблема в том, что имена почтовых ящиков имеют разную длину, и поэтому нельзя указать, с какой именно позиции начинается название домена. Другими словами, вы не знаете, какое значение указать в аргументе начальная_позиция функции Excel ЗАМЕНИТЬ. Чтобы узнать это, используйте функцию Excel НАЙТИ, чтобы определить позицию, с которой начинается доменное имя в адресе электронной почты:

=НАЙТИ(«@abc»; A3)

Затем вставьте указанную выше функцию НАЙТИ в аргумент начальная_позиция формулы ЗАМЕНИТЬ:

=ЗАМЕНИТЬ(A3; НАЙТИ(«@abc»;A3); 4; «@bca»)

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

Как вы видите на скриншоте ниже, у формулы нет проблем, чтобы поменять символы в разных позициях. Однако если заменяемая текстовая строка не найдена и менять в ней ничего не нужно, формула возвращает ошибку #ЗНАЧ!:

Excel как заменить буквы в адресе

И мы хотим, чтобы формула вместо ошибки возвращала исходный адрес электронной почты без изменения.  Для этого заключим нашу формулу НАЙТИ И ЗАМЕНИТЬ в функцию ЕСЛИОШИБКА:

=ЕСЛИОШИБКА(ЗАМЕНИТЬ(A3; НАЙТИ(«@abc»;A3); 4; «@bca»);A3)

И эта доработанная формула прекрасно работает, не так ли?

Заменить заглавные буквы на строчные и наоборот

Еще один полезный пример – заменить первую строчную букву в ячейке на прописную (заглавную). Всякий раз, когда вы имеете дело со списком имен, товаров и т.п., вы можете использовать приведенную ниже формулу, чтобы изменить первую букву на ЗАГЛАВНУЮ. Ведь названия товаров могут быть записаны по-разному, а в списках важно единообразие.

Таким образом, нам нужно заменить первый символ в тексте на заглавную букву. Используем формулу

=ЗАМЕНИТЬ(СТРОЧН(A3);1;1;ПРОПИСН(ЛЕВСИМВ(A3;1)))

excel заменить первые буквы на заглавные

Как видите, эта формула сначала заменяет все буквы в тексте на строчные при помощи функции СТРОЧН, а затем первую строчную букву меняет на заглавную (прописную).

Быть может, это будет полезно.

Описание функции ПОДСТАВИТЬ

Функция ПОДСТАВИТЬ в Excel заменяет один или несколько экземпляров заданного символа или текстовой строки указанными символами.

Синтаксис формулы ПОДСТАВИТЬ в Excel следующий:

ПОДСТАВИТЬ(текст, старый_текст, новый_текст, [номер_вхождения])

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

  • Текст – исходный текст, в котором вы хотите заменить слова либо отдельные символы. Может быть тестовой строой, ссылкой на ячейку или же результатом вычисления другой формулы.
  • Старый_текст – что именно вы хотите заменить.
  • Новый_текст – новый символ или слово для замены старого_текста.
  • Номер_вхождения — какой по счёту экземпляр старый_текст вы хотите заменить. Если этот параметр опущен, все вхождения старого текста будут заменены новым текстом.

Например, все приведенные ниже формулы подставляют вместо «1» – цифру «2» в ячейке A2, но возвращают разные результаты в зависимости от того, какое число указано в последнем аргументе:

=ПОДСТАВИТЬ(A3;»1″;»2″;1) — Заменяет первое вхождение «1» на «2».

=ПОДСТАВИТЬ(A3;»1″;»2″;2) — Заменяет второе вхождение «1» на «2».

=ПОДСТАВИТЬ(A3;»1″;»2″) — Заменяет все вхождения «1» на «2».

На практике формула ПОДСТАВИТЬ также используется для удаления ненужных символов из текста. Вы просто меняете их на пустую строку “”.

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

=ПОДСТАВИТЬ(A3;» «;»»)

Примечание. Функция ПОДСТАВИТЬ в Excel чувствительна к регистру . Например, следующая формула меняет все вхождения буквы «X» в верхнем регистре на «Y» в ячейке A2, но не заменяет ни одной буквы «x» в нижнем регистре.

=ПОДСТАВИТЬ(A3;»Х»;»Y»)

Замена нескольких значений одной формулой

Как и в случае с функцией ЗАМЕНИТЬ, вы можете вложить несколько функций ПОДСТАВИТЬ в одну формулу, чтобы сделать несколько подстановок одновременно, т.е. заменить несколько символов или подстрок при помощи одной формулы.

Предположим, у вас есть текстовая строка типа « пр1, эт1, з1 » в ячейке A3, где «пр» означает «Проект», «эт» означает «этап», а «з» означает «задача». Вы хотите заместить три этих кода их полными эквивалентами. Для этого вы можете написать 3 разные формулы подстановки:

=ПОДСТАВИТЬ(A3;»пр»;»Проект «)

=ПОДСТАВИТЬ(A3;»эт»;»Этап «)

=ПОДСТАВИТЬ(A3;»з»;»Задача «)

А затем вложить их друг в друга:

=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A3;»пр»;»Проект «); «эт»;»Этап «);»з»;»Задача «)

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

Другие полезные применения функции ПОДСТАВИТЬ:

  • Замена неразрывных пробелов в ячейке Excel обычными
  • Убрать пробелы в числах
  • Удалить перенос строки в ячейке
  • Подсчитать определенные символы в ячейке

Что лучше использовать – ЗАМЕНИТЬ или ПОДСТАВИТЬ?

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

  • ПОДСТАВИТЬ замещает один или несколько экземпляров данного символа или текстовой строки. Итак, если вы знаете тот текст, который нужно поменять, используйте функцию Excel ПОДСТАВИТЬ.
  • ЗАМЕНИТЬ замещает символы в указанной позиции текстовой строки. Итак, если вы знаете положение заменяемых символов, используйте функцию Excel ЗАМЕНИТЬ.
  • Функция ПОДСТАВИТЬ в Excel позволяет добавить необязательный параметр (номер_вхождения), указывающий, какой по счету экземпляр старого_текста следует заместить на новый_текст.

Вот как вы можете заменить текст в ячейке и использовать функции ПОДСТАВИТЬ и ЗАМЕНИТЬ в Excel. Надеюсь, эти примеры окажутся полезными при решении ваших задач. 

Источник

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel для Mac 2011 Еще…Меньше

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

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

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

Пошаговый анализ примера

Рассмотрим предыдущий пример шаг за шагом.

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

Подготовка листа

  1. Откройте новый пустой лист.

  2. Прежде всего добавьте в первый столбец эти подписи, чтобы сделать данные на листе понятнее.

    1. В ячейку A1 введите текст Сумма займа.

    2. В ячейку A2 введите текст Срок в месяцах.

    3. В ячейку A3 введите текст Процентная ставка.

    4. В ячейку A4 введите текст Платеж.

  3. Затем добавьте известные вам значения.

    1. В ячейку B1 введите значение 100 000. Это сумма займа.

    2. В ячейку B2 введите значение 180. Это число месяцев, за которое требуется выплатить ссуду.

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

  4. Теперь добавьте формулу, результат которой вас интересует. Например, используйте функцию ПЛТ.

    1. В ячейке B4 введите =ПЛТ(B3/12;B2;B1). Эта формула вычисляет сумму платежа. В данном примере вы хотите ежемесячно выплачивать 900 ₽. Это значение здесь не вводится, поскольку вам нужно определить процентную ставку с помощью средства подбора параметров, а для этого требуется формула.

      Формула ссылается на ячейки B1 и B2, значения которых вы указали на предыдущих этапах. Она также ссылается на ячейку B3, в которую средство подбора параметров поместит процентную ставку. Формула делит значение из ячейки B3 на 12, поскольку был указан ежемесячный платеж, а функция ПЛТ предусматривает использование годовой процентной ставки.

      Поскольку в ячейке B3 нет значения, Excel полагает процентную ставку равной 0 % и в соответствии со значениями из данного примера возвращает сумму платежа 555,56 ₽. Пока вы можете игнорировать это значение.

Использование средства подбора параметров для определения процентной ставки

  1. На вкладке Данные в группе Работа с данными нажмите кнопку Анализ «что если» и выберите команду Подбор параметра.

  2. В поле Установить в ячейке введите ссылку на ячейку, в которой находится нужная формула. В данном примере это ячейка B4.

  3. В поле Значение введите нужный результат формулы. В данном примере это -900. Обратите внимание, что число отрицательное, так как представляет собой платеж.

  4. В поле Изменяя значение ячейки введите ссылку на ячейку, в которой находится корректируемое значение. В данном примере это ячейка B3.
     

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

  5. Нажмите кнопку ОК.

    Выполняется и создается результат, как показано на рисунке ниже.

    Подбор параметров

    Ячейки B1, B2 и B3 — это значения для суммы займа, длины срока и процентной ставки.

    Ячейка B4 отображает результат формулы =PMT(B3/12;B2;B1).

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

    1. На вкладке Главная в группе Число нажмите кнопку Процент.

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

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

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

Пошаговый анализ примера

Рассмотрим предыдущий пример шаг за шагом.

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

Подготовка листа

  1. Откройте новый пустой лист.

  2. Прежде всего добавьте в первый столбец эти подписи, чтобы сделать данные на листе понятнее.

    1. В ячейку A1 введите текст Сумма займа.

    2. В ячейку A2 введите текст Срок в месяцах.

    3. В ячейку A3 введите текст Процентная ставка.

    4. В ячейку A4 введите текст Платеж.

  3. Затем добавьте известные вам значения.

    1. В ячейку B1 введите значение 100 000. Это сумма займа.

    2. В ячейку B2 введите значение 180. Это число месяцев, за которое требуется выплатить ссуду.

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

  4. Теперь добавьте формулу, результат которой вас интересует. Например, используйте функцию ПЛТ.

    1. В ячейке B4 введите =ПЛТ(B3/12;B2;B1). Эта формула вычисляет сумму платежа. В данном примере вы хотите ежемесячно выплачивать 900 ₽. Это значение здесь не вводится, поскольку вам нужно определить процентную ставку с помощью средства подбора параметров, а для этого требуется формула.

      Формула ссылается на ячейки B1 и B2, значения которых вы указали на предыдущих этапах. Она также ссылается на ячейку B3, в которую средство подбора параметров поместит процентную ставку. Формула делит значение из ячейки B3 на 12, поскольку был указан ежемесячный платеж, а функция ПЛТ предусматривает использование годовой процентной ставки.

      Поскольку в ячейке B3 нет значения, Excel полагает процентную ставку равной 0 % и в соответствии со значениями из данного примера возвращает сумму платежа 555,56 ₽. Пока вы можете игнорировать это значение.

Использование средства подбора параметров для определения процентной ставки

  1. Выполните одно из указанных ниже действий.

    In Excel 2016 для Mac: On the Data tab, click What-If Analysis, and then click Goal Seek.

    В Excel для Mac 2011: на вкладке Данные в группе Инструменты для работы с данными нажмите кнопку Анализ «что если» ивыберите «Поиск окна».

  2. В поле Установить в ячейке введите ссылку на ячейку, в которой находится нужная формула. В данном примере это ячейка B4.

  3. В поле Значение введите нужный результат формулы. В данном примере это -900. Обратите внимание, что число отрицательное, так как представляет собой платеж.

  4. В поле Изменяя значение ячейки введите ссылку на ячейку, в которой находится корректируемое значение. В данном примере это ячейка B3.
     

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

  5. Нажмите кнопку ОК.

    Выполняется и создается результат, как показано на рисунке ниже.

    Анализ "что если" - средство подбора параметров

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

    • In Excel 2016 для Mac: On the Home tab, click Increase Decimal Увеличение числа десятичных замеровor Decrease Decimal Decrease Decimal.

    • В Excel для Mac 2011: на вкладке Главная в группе Число нажмите кнопку Увеличить десятичность Увеличение числа десятичных разрядов или Уменьшить число десятичных Уменьшение числа десятичных разрядов , чтобы установить количество десятичных десятичных заметок.

Нужна дополнительная помощь?

Источник

Преобразование формул в значения

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

  • Вы хотите зафиксировать цифры в вашем отчете на текущую дату.
  • Вы не хотите, чтобы клиент увидел формулы, по которым вы рассчитывали для него стоимость проекта (а то поймет, что вы заложили 300% маржи на всякий случай).
  • Ваш файл содержит такое больше количество формул, что Excel начал жутко тормозить при любых, даже самых простых изменениях в нем, т.к. постоянно их пересчитывает (хотя, честности ради, надо сказать, что это можно решить временным отключением автоматических вычислений на вкладке Формулы – Параметры вычислений).
  • Вы хотите скопировать диапазон с данными из одного места в другое, но при копировании «сползут» все ссылки в формулах.

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

Способ 1. Классический

Этот способ прост, известен большинству пользователей и заключается в использовании специальной вставки:

  1. Выделите диапазон с формулами, которые нужно заменить на значения.
  2. Скопируйте его правой кнопкой мыши – Копировать (Copy).
  3. Щелкните правой кнопкой мыши по выделенным ячейкам и выберите либо значок Значения (Values):

    преобразование формул в значения в Excel
    либо наведитесь мышью на команду Специальная вставка (Paste Special), чтобы увидеть подменю:

    formulas-to-values2.png
    Из него можно выбрать варианты вставки значений с сохранением дизайна или числовых форматов исходных ячеек.

    В старых версиях Excel таких удобных желтых кнопочек нет, но можно просто выбрать команду Специальная вставка и затем опцию Значения (Paste Special — Values) в открывшемся диалоговом окне:

    formulas-to-values3.png

Способ 2. Только клавишами без мыши

При некотором навыке, можно проделать всё вышеперечисленное вообще на касаясь мыши:

  1. Копируем выделенный диапазон Ctrl+C
  2. Тут же вставляем обратно сочетанием Ctrl+V
  3. Жмём Ctrl, чтобы вызвать меню вариантов вставки
  4. Нажимаем клавишу с русской буквой З или используем стрелки, чтобы выбрать вариант Значения и подтверждаем выбор клавишей Enter:

Замена формул на значения соч.клавиш

Способ 3. Только мышью без клавиш или Ловкость Рук

Этот способ требует определенной сноровки, но будет заметно быстрее предыдущего. Делаем следующее:

  1. Выделяем диапазон с формулами на листе
  2. Хватаем за край выделенной области (толстая черная линия по периметру) и, удерживая ПРАВУЮ клавишу мыши, перетаскиваем на пару сантиметров в любую сторону, а потом возвращаем на то же место
  3. В появившемся контекстном меню после перетаскивания выбираем Копировать только значения (Copy As Values Only).

formulas-to-values4.gif

После небольшой тренировки делается такое действие очень легко и быстро. Главное, чтобы сосед под локоть не толкал и руки не дрожали ;)

Способ 4. Кнопка для вставки значений на Панели быстрого доступа

Ускорить специальную вставку можно, если добавить на панель быстрого доступа в левый верхний угол окна кнопку Вставить как значения. Для этого выберите Файл — Параметры — Панель быстрого доступа (File — Options — Customize Quick Access Toolbar). В открывшемся окне выберите Все команды (All commands) в выпадающем списке, найдите кнопку Вставить значения (Paste Values) и добавьте ее на панель:

Добавление кнопки вставки значений на панель быстрого доступа

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

Кнопка вставки значений на панели быстрого доступа

Кроме того, по умолчанию всем кнопкам на этой панели присваивается сочетание клавиш Alt + цифра (нажимать последовательно). Если нажать на клавишу Alt, то Excel подскажет цифру, которая за это отвечает:

Подсветка горячих клавиш

Способ 5. Макросы для выделенного диапазона, целого листа или всей книги сразу

Если вас не пугает слово «макросы», то это будет, пожалуй, самый быстрый способ.

Макрос для превращения всех формул в значения в выделенном диапазоне (или нескольких диапазонах, выделенных одновременно с Ctrl) выглядит так: 

Sub Formulas_To_Values_Selection()
'преобразование формул в значения в выделенном диапазоне(ах)
    Dim smallrng As Range
    For Each smallrng In Selection.Areas
        smallrng.Value = smallrng.Value
    Next smallrng
End Sub

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

Sub Formulas_To_Values_Sheet()
'преобразование формул в значения на текущем листе
    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
End Sub

И, наконец, для превращения всех формул в книге на всех листах придется использовать вот такую конструкцию: 

Sub Formulas_To_Values_Book()
'преобразование формул в значения во всей книге
    For Each ws In ActiveWorkbook.Worksheets
        ws.UsedRange.Value = ws.UsedRange.Value
    Next ws
End Sub

Код нужных макросов можно скопировать в новый модуль вашего файла (жмем Alt+F11 чтобы попасть в Visual Basic, далее Insert — Module). Запускать их потом можно через вкладку Разработчик — Макросы (Developer — Macros) или сочетанием клавиш Alt+F8. Макросы будут работать в любой книге, пока открыт файл, где они хранятся. И помните, пожалуйста, о том, что действия выполненные макросом невозможно отменить — применяйте их с осторожностью. 

Способ 6. Для ленивых

Если ломает делать все вышеперечисленное, то можно поступить еще проще — установить надстройку PLEX, где уже есть готовые макросы для конвертации формул в значения и делать все одним касанием мыши:

formulas-to-values.png

В этом случае:

  • всё будет максимально быстро и просто
  • можно откатить ошибочную конвертацию отменой последнего действия или сочетанием Ctrl+Z как обычно
  • в отличие от предыдущего способа, этот макрос корректно работает, если на листе есть скрытые строки/столбцы или включены фильтры
  • любой из этих команд можно назначить любое удобное вам сочетание клавиш в Диспетчере горячих клавиш PLEX

Ссылки по теме

  • Что такое макросы, как их использовать, копировать и запускать
  • Как скопировать формулы без сдвига ссылок
  • Как считать в Excel без формул
Источник

Редактирование формул в Excel

​Смотрите также​ рассмотрены на следующих​Копируем столбец E и​ даты. Если промежутки​ общей стоимости. Для​ единицу умножить на​ операторы математических вычислений​ сохранения книги в​Дополнительно​ ограничено 15 разрядами.​ отображается дата «22.06.2008»,​ ли они изменены​Формулы​

Как изменить формулу в Excel

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

  1. ​ выделяем столбец B.​ между ними одинаковые​ этого нужно:​ количество. Для вычисления​ и сразу получать​Редактирование формул в Excel
  2. ​ формате текущей версии​.​ Для положительных чисел​ также содержит порядковый​ после прошлого пересчета.​в группе​и выберите категорию​ условия. Excel не​Редактирование формул в Excel
  3. ​V​ Вы также можете​ уже существующую формулу​Неправильно округленные числа приводят​ Выбираем инструмент: «Главная»-«Вставить»-«Специальная​ – день, месяц,​Разделить стоимость одного товара​ стоимости введем формулу​ результат.​ Microsoft Excel книга​Для включения или отключения​ предельное значение равно​ номер, который является​CTRL+SHIFT+ALT+F9​Вычисление​Редактирование формул в Excel
  4. ​формулы​​ может автоматически рассчитать​​(или букву З​ использовать команду​​ в Excel. Это​​ к существенным ошибкам​Редактирование формул в Excel
  5. ​ вставка» (или нажимаем​ год. Введем в​Редактирование формул в Excel

​ на стоимость всех​ в ячейку D2:​​Но чаще вводятся адреса​​ будет открываться быстрее.​ возможности использования для​​ 9,99999999999999E+307, а для​​ хранимым значением даты​На вкладке​нажмите кнопку​

​Выполните одно из указанных​ значение по формуле,​ – в русском​Показать формулы​​ может произойти по​​ даже при самых​ CTRL+SHIFT+V). В появившимся​ первую ячейку «окт.15»,​ товаров и результат​ = цена за​ ячеек. То есть​​Формула предписывает программе Excel​​ вычислений нескольких процессоров​ отрицательных значений —​​ в этой ячейке.​​Файл​​Произвести вычисления​​ ниже действий.​

​ которая ссылается (прямо​

office-guru.ru

Как в Excel заменить формулы на их значения

​ Excel), чтобы вставить​, которая находится в​ многим причинам, например,​ простых вычислениях. Особенно​ окне отмечаем опцию​ во вторую –​ умножить на 100.​ единицу * количество.​ пользователь вводит ссылку​ порядок действий с​

​ в разделе​ –9.99999999999999E+307. Эти числа​ Можно изменить отображение​

  • ​нажмите кнопку​.​Для пересчета всех зависимых​ или косвенно) на​ только значения обратно​ группе команд​ допущена ошибка, опечатка​
  • ​ если речь идет​ «значения» и нажимаем​ «ноя.15». Выделим первые​ Ссылка на ячейку​ Константы формулы –​ на ячейку, со​ числами, значениями в​
  • ​Формулы​ приблизительно равны 1​ даты на другой​Параметры​
  • ​Совет:​​ формул при каждом​​ ячейку, содержащую формулу​
  • ​ в те же​Зависимости формул​ или необходимо изменить​ о больших объемах​ Ок. Таким образом,​ две ячейки и​ со значением общей​ ссылки на ячейки​

Преобразуем формулы в значения при помощи комбинаций клавиш в Excel

​ значением которой будет​ ячейке или группе​установите или снимите​ или –1 с​ формат (например, «22-июнь-2008»),​и выберите категорию​

Заменить формулы значениями в Excel

​ Многие из этих параметров​

  1. ​ изменении значения, формулы​ (это называется циклической​ ячейки Excel.​
  2. ​на вкладке​​ ссылки на ячейки.​​ данных. Всегда нужно​​ сохранился финансовый формат​​ «протянем» за маркер​ стоимости должна быть​ с соответствующими значениями.​
  3. ​ оперировать формула.​​ ячеек. Без формул​​ флажок​​ 308 нулями.​​ но хранимое в​Формулы​ можно изменять и​ или имени в​ ссылкой). Если формула​Shift+F10 + V​

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

​ электронные таблицы не​Включить многопоточные вычисления​

Заменить формулы значениями в Excel

​Точность в Excel означает,​ ячейке значение не​
​.​
​ вне диалогового окна​

​ разделе​

office-guru.ru

Изменение пересчета, итерации или точности формулы

​ содержит обратную ссылку​​(З) – это​.​ мы научимся изменять​ при округлении, особенно​ обновились.​Найдем среднюю цену товаров.​ копировании она оставалась​ отображает значение умножения.​ ячейках формула автоматически​ нужны в принципе.​.​ что любое число​ изменится.​В Excel 2007 нажмите​Параметры Excel​Параметры вычислений​ на одну из​ быстрейший способ воспользоваться​Автор: Антон Андронов​ формулы в Excel​ когда нужно подготовить​

​Удаляем уже ненужный столбец​ Выделяем столбец с​ неизменной.​

​ Те же манипуляции​​ пересчитывает результат.​Конструкция формулы включает в​Примечание​ с разрядностью более​С осторожностью изменяйте точность​Кнопку Microsoft Office​. Откройте вкладку​в группе​ своих собственных ячеек,​ в Excel командой​Представляем Вашему вниманию полезный​ на базе очень​ точный отчет.​ E. Обратите внимание,​ ценами + еще​Чтобы получить проценты в​ необходимо произвести для​Ссылки можно комбинировать в​ себя: константы, операторы,​    Этот флажок по умолчанию​

​ 15 хранится и​​ вычислений​, выберите пункт​Формулы​Вычисления в книге​ необходимо определить, сколько​Paste special​ совет, который поможет​ простого примера.​Михаил пак​ что благодаря формулам​ одну ячейку. Открываем​ Excel, не обязательно​ всех ячеек. Как​ рамках одной формулы​ ссылки, функции, имена​ установлен, и для​ отображается только с​   ​Параметры Excel​и в группе​выберите пункт​ раз следует пересчитывать​

​(Специальная вставка) >​​ сэкономить время –​В приведенном ниже примере​: Если изменения однотипные​ значения в столбцах​ меню кнопки «Сумма»​ умножать частное на​ в Excel задать​ с простыми числами.​ диапазонов, круглые скобки​ вычислений используются все​ точностью в 15​При пересчете формулы Excel​и выберите категорию​

Изменение при пересчете листа или книги

​Вычисления​Автоматически​ формулу. Циклические ссылки​Values only​ самый быстрый способ​ мы ввели в​ (прибавить, разделить, отнять​ C и D​ — выбираем формулу​ 100. Выделяем ячейку​ формулу для столбца:​Оператор умножил значение ячейки​ содержащие аргументы и​ процессоры. Число процессоров​ десятичных разрядов. Десятичная​ обычно использует значения,​формулы​щелкните элемент​. Это — способ​ могут пересчитываться до​(Значения).​ заменить в Excel​ формулу неправильную ссылку​ или умножить на​

​ изменились автоматически.​​ для автоматического расчета​ с результатом и​ копируем формулу из​

  1. ​ В2 на 0,5.​​ другие формулы. На​​ на компьютере определяется​​ запятая может стоять​​ хранящиеся в ячейках,​​В разделе​​Параметры вычислений​

    ​ вычислений по умолчанию.​​ бесконечности. Однако существует​ Изображение кнопки Office​Готово! Достаточно быстрый способ,​​ формулы на их​​ на ячейку, и​​ одно число) ,​

  2. ​Вот и все у​ среднего значения.​

    • ​ нажимаем «Процентный формат».​ первой ячейки в​ Чтобы ввести в​ примере разберем практическое​ автоматически и отображается​​ перед любым из​​ на которые она​​Параметры вычислений​​, а затем —​​Для пересчета всех зависимых​​ возможность управления максимальным​ не так ли?​

    • ​ значения. Этот способ​ нам необходимо исправить​ то делается так.​ нас прайс с​Чтобы проверить правильность вставленной​ Или нажимаем комбинацию​ другие строки. Относительные​​ формулу ссылку на​​ применение формул для​​ рядом с параметром​​ этих разрядов. Все​​ ссылается. Например, если​​установите флажок​

    • ​Выполнять автоматически​ формул, за исключением​ числом итераций и​Урок подготовлен для Вас​ работает в Excel​ это.​ Сначала = ,​​ новыми ценами, которые​​ формулы, дважды щелкните​​ горячих клавиш: CTRL+SHIFT+5​​ ссылки – в​​ ячейку, достаточно щелкнуть​​ начинающих пользователей.​

      ​использовать все процессоры данного​​ разряды справа от​​ в двух ячейках​​Включить итеративные вычисления​.​​ таблиц данных, при​​ количеством допустимых изменений.​ командой сайта office-guru.ru​ 2013, 2010 —​Выделите ячейку, формулу в​​ потом выделяете столбец,​​ увеличенные на 7%.​

    • ​ по ячейке с​Копируем формулу на весь​ помощь.​ по этой ячейке.​Чтобы задать формулу для​ компьютера​​ 15-го обнуляются. Например,​​ содержится значение 10,005,​​.​​Примечание:​​ каждом внесении изменений​​Точность​

      Группа

    • ​Источник: https://www.ablebits.com/office-addins-blog/2013/12/13/excel-convert-formula-to-value/​ 2003.​ которой необходимо изменить.​ затем знак действия​ Столбец E и​​ результатом.​​ столбец: меняется только​​Находим в правом нижнем​​В нашем примере:​​ ячейки, необходимо активизировать​​.​

​ у числа 1234567,890123456​​ а формат ячеек​Чтобы установить максимальное число​ Если лист содержит формулу,​​ в значение, формулу​​     — это показатель​​Перевел: Антон Андронов​​Может быть несколько причин​​ В нашем примере​​ и число. Ентер.​​ F можно удалить.​​В прайсах Excel очень​​ первое значение в​​ углу первой ячейки​

​Поставили курсор в ячейку​​ ее (поставить курсор)​Дополнительно, при выборе параметра​ 16 разрядов (7​ предполагает отображение значений​ пересчетов, введите в​ связанную с непересчитанным​ или имя, в​ степени сходимости вычислений.​Автор: Антон Андронов​ для замены формул​ мы выбрали ячейку​ В самой верхней​В любую отдельную от​​ часто приходится изменять​​ формуле (относительная ссылка).​ столбца маркер автозаполнения.​ В3 и ввели​ и ввести равно​Включить многопоточные вычисления​​ до запятой и​​ в виде денежных​

Пересчет листа или книги вручную с помощью сочетаний клавиш

​ поле​

​ листом, и эта​

​ разделе​ Excel хранит и​Примечание:​ на их значения:​ B3.​ ячейке появится результат​ таблицы ячейку (например,​ цены. Особенно в​ Второе (абсолютная ссылка)​

​ Нажимаем на эту​

​ =.​ (=). Так же​можно задавать число​ 9 после). В​ единиц, в каждой​

​Предельное число итераций​

​ связь обновляется, появится​Параметры вычислений​ выполняет вычисления с​Мы стараемся как​Чтобы быстро вставить значения​Щелкните по Строке формул,​

​ для этой строки.​

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

​ Excel это число​

Изменение числа итераций формулы Excel

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

    ​ чтобы приступить к​​ Наводите курсор на​ Изображение кнопки Office​ 1,07 и скопируйте​​Рассмотрим 2 простых и​​ правильность вычислений –​​ мыши, держим ее​

  2. ​ – Excel «обозначил»​​ равенства в строку​​ Например, можно ограничить​​ хранится и отображается​​ отображаться значение 10,01 ₽.​

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

  4. ​ быстрых способа одновременного​ найдем итог. 100%.​ и «тащим» вниз​ ее (имя ячейки​​ формул. После введения​​ число процессоров, используемых​ как 1234567,89012345 (в​ При сложении этих​ тем больше времени​ полностью не пересчитан.​

​щелкните элемент​​ возможность изменить точность​ материалами на вашем​ другой лист, не​ также можете дважды​ ячейки. Появится крестик,​Выделите диапазон B2:B5. Снова​ изменения всех цен​ Все правильно.​ по столбцу.​ появилось в формуле,​ формулы нажать Enter.​ для пересчета, если​ строке формулы и​ значений результат будет​ потребуется для пересчета​ Для обновления связи​Автоматически, кроме таблиц данных​ вычислений, так что​

Изменение точности вычислений в книге

​ языке. Эта страница​ теряя времени на​ щелкнуть по ячейке,​ зажмите кнопку мыши​

​ выберите инструмент «Специальная​ с увеличением наценки​При создании формул используются​​Отпускаем кнопку мыши –​

​ вокруг ячейки образовался​ В ячейке появится​ нужно выделить процессорное​ ячейке). Если установить​ равен 20,01 ₽, поскольку​ листа.​ с текущим значением,​.​ Excel при пересчете​ переведена автоматически, поэтому​ копирование и специальную​ чтобы посмотреть и​ и тащите вниз.​ вставка» (или нажимаем​ в процентах. Показатели​ следующие форматы абсолютных​

​ формула скопируется в​ «мелькающий» прямоугольник).​​ результат вычислений.​

​ время другим исполняемым​ для ячейки формат,​ Excel складывает хранимые​Для установки максимальной разности​ записанном в исходном​Для отключения автоматического пересчета​ формул будет использовать​ ее текст может​ вставку.​ отредактировать формулу прямо​ По всем строкам​ CTRL+SHIFT+V). В появившемся​ НДС будут перечитываться​ ссылок:​ выбранные ячейки с​Ввели знак *, значение​В Excel применяются стандартные​ на компьютере программам.​ отображающий все разряды​ значения 10,005 и​

​ между результатами пересчетов​ листе, даже если​ и выполнения пересчета​ для вычислений не​ содержать неточности и​Чтобы сохранить в тайне​ в ней.​ появляются результаты вычисления​ окне, в разделе​ автоматически. А так​$В$2 – при копировании​ относительными ссылками. То​ 0,5 с клавиатуры​

  1. ​ математические операторы:​​Чтобы задать количество процессоров,​​ (вместо экспоненциального формата,​​ 10,005, а не​​ введите ее в​​ оно неверно, нажмите​​ открытых книг только​

    ​ хранимое, а отображаемое​​ грамматические ошибки. Для​ Изображение кнопки Office​ расчетные формулы при​​Все ячейки, на которые​​Светланочка​​ «Вставить» выберите опцию​

  2. ​ же мы узнаем,​​ остаются постоянными столбец​​ есть в каждой​ и нажали ВВОД.​​Оператор​​ в группе​

Дополнительные сведения о точности в Excel

​ такого как 1,23457E+06),​ отображаемые значения.​ поле​ кнопку​ при явном требовании​ значение.​ нас важно, чтобы​ отправке рабочей книги​ ссылается формула, будут​: в самой вверхней​ «значения». В разделе​ как посчитать наценку​ и строка;​ ячейке будет своя​Если в одной формуле​

​Операция​Число потоков вычислений​ вы увидите, что​При изменении точности вычислений​Относительная погрешность​ОК​ ( с помощью​Во время вычислений можно​ эта статья была​ другому человеку (например,​ выделены разноцветными границами.​ строке, там где​ «Операции» выберите опцию​ в процентах.​B$2 – при копировании​ формула со своими​ применяется несколько операторов,​Пример​щелкните элемент​ число показывается как​ в книге путем​. Чем меньше это​. Для прекращения обновления​ клавиши F9) в​ выбирать команды и​ вам полезна. Просим​ Ваши розничные цены​ В нашем примере,​ формула пишется…​ «умножить» и нажмите​В исходной таблице, которая​ неизменна строка;​ аргументами.​ то программа обработает​

Изменение числа процессоров, используемых для вычисления формул

​+ (плюс)​Вручную​ 1234567,890123450. 6 в​ использования отображаемых (форматированных)​ число, тем точнее​ связи и использования​ разделе​ выполнять ввод чисел​ вас уделить пару​ с наценкой к​ мы изменим вторую​Артём сергеевич​ ОК. Все числа​ условно представляет собой​$B2 – столбец не​Ссылки в ячейке соотнесены​ их в следующей​Сложение​

  1. ​и введите число​​ конце (16-й разряд)​​ значений Excel всегда​​ результат, и тем​​ предыдущего значения, полученного​​Параметры вычислений​​ или формул. Для​

    ​ секунд и сообщить,​​ оптовой цене).​ Изображение кнопки Office​ часть формулы, чтобы​​: выдели ячейку относительно​​ в колонке «цена​​ расходную накладную нужно​​ изменяется.​

  2. ​ со строкой.​ последовательности:​=В4+7​ процессоров (до 1024).​​ отбрасывается и заменяется​​ меняет точность хранимых​ больше времени требуется​​ из исходного листа,​​в группе​

    ​ выполнения команд или​​ помогла ли она​Чтобы защитить результат от​ ссылка вела на​ которой нужно менять​ без НДС» увеличились​ сделать наценку для​Чтобы сэкономить время при​​Формула с абсолютной ссылкой​%, ^;​​- (минус)​

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

  4. ​ значения в строке​ на 7%.​​ всех цен и​​ введении однотипных формул​​ ссылается на одну​​*, /;​Вычитание​

Пересчет книг, созданных в более ранних версиях Excel

​ старых книг, приложение​ является 15-й разряд,​ от полной (15​Примечание:​Отмена​выберите параметр​ прерываются, а затем​ кнопок внизу страницы.​ значения в связанных​

  • ​ C2. Для этого​ формул появится формула​Внимание! Заметьте, в ячейке​ НДС на 7%.​ в ячейки таблицы,​

  • ​ и ту же​+, -.​=А9-100​ Excel при первом​ а все последующие​ цифр) до любого​ Команды «Поиск решения» и​.​Вручную​ возобновляются снова. Если​ Для удобства также​ ячейках.​

  • ​ выделите в формуле​ в ней и​ D2 отображается ошибочное​ Как вычисляется Налог​ применяются маркеры автозаполнения.​ ячейку. То есть​Поменять последовательность можно посредством​* (звездочка)​ открытии книги, сохраненной​ равны нулю.​ другого числа десятичных​ «Подбор параметра» являются​

support.office.com

Работа в Excel с формулами и таблицами для чайников

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

​ значение: вместо 1,05​ на Добавленную стоимость​ Если нужно закрепить​ при автозаполнении или​ круглых скобок: Excel​Умножение​ в более старой​Компьютер может содержать более​ знаков отображаемого формата.​

Формулы в Excel для чайников

​ частью блока команд,​Сочетание клавиш​Примечание:​ число формул либо​ оригинал (на английском​RAND​ отредактировать, а затем​Алекс куха​ там 1,04. Это​ видно на рисунке:​ ссылку, делаем ее​

Ввод формул.

​ копировании константа остается​ в первую очередь​

​=А3*2​ ​ версии Excel, и​ ​ одного процессора (несколько​
​ После такого изменения​ ​ иногда называемого инструментами​ ​Пересчет измененных после последнего​
​  При выборе параметра​ ​ листы содержат таблицы​ ​ языке) .​
​(СЛЧИС).​ ​ выберите мышью требуемую​ ​: в 1й ячейке​
​ ошибки округлений.​ ​«Цена с НДС» рассчитывается​ ​ абсолютной. Для изменения​
​ неизменной (или постоянной).​ ​ вычисляет значение выражения​ ​/ (наклонная черта)​
​ книги, созданной в​ ​ физических процессоров) или​
​ возврат к исходной​
​ анализ «что если».​ ​ вычисления формул и​
​Вручную​
​ данных или функции,​ ​Для эффективного использования формул​
​Если в рабочей книге​ ​ ячейку или измените​

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

​ поддерживать многопоточность (несколько​ точности будет невозможен.​ Обе команды для​ формул, зависящих от​Excel автоматически устанавливает​ автоматически пересчитываемые при​ необходимо ознакомиться с​

Математическое вычисление.

​ содержится много сложных​ адрес вручную.​ формулу, выделить её​ правильными, необходимо округлять​ без НДС» +​ относительной ссылки.​

Ссылки на ячейки.

​ абсолютную ссылку, пользователю​​=А7/А8​

Изменение результата.

​ себя по-разному.​ логических процессоров). На​На вкладке​

Умножение ссылки на число.

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

​По окончании нажмите​

  1. ​ и за правый​ числа, перед тем​ «НДС». Это значит,​
  2. ​Простейшие формулы заполнения таблиц​ необходимо поставить знак​Различают два вида ссылок​^ (циркумфлекс)​При открытии книги, созданной​ таких компьютерах можно​
  3. ​Файл​ используют управляемые итерации.​ открытых книгах. Если​

​Пересчитывать книгу перед сохранением​ процесс вычислений может​Вычисление​ пересчёт очень медленным.​Enter​

  • ​ нижний угол протащить​
  • ​ как их суммировать.​
  • ​ что нам достаточно​

​ в Excel:​ доллара ($). Проще​ на ячейки: относительные​Степень​ в текущей версии,​

​ уменьшить или контролировать​

Как в формуле Excel обозначить постоянную ячейку

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

​на клавиатуре или​ до конца столбца​В ячейке C2 добавим​ увеличить на 7%​Перед наименованиями товаров вставим​ всего это сделать​ и абсолютные. При​=6^2​ в Excel пересчитываются​ время, требуемое для​

  1. ​Параметры​ оптимальное значение для​ книги, нажимать клавишу​ занимает много времени,​Исходный прайс-лист.
  2. ​ Он также может​ по формулам и​ не можете переключить​ воспользуйтесь командой​Новое значение, конечно,​ к формуле функцию:​ только первую колонку.​ еще один столбец.​ с помощью клавиши​ копировании формулы эти​= (знак равенства)​ только формулы, зависящие​ пересчета книг, содержащих​Формула для стоимости.
  3. ​и выберите категорию​ одной ячейки, изменяя​ F9 для пересчета​ для его экономии​ быть длительным, если​ последующего отображения значений​ пересчёт книги Excel​Ввод​ будет в новом​ =ОКРУГЛ(B2*0,22;2). Проверяем D2​​ Выделяем любую ячейку​

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

Автозаполнение формулами.

​ снимите флажок​ листы содержат связи​ результатов в ячейках,​ в ручной режим.​в Cтроке формул.​ столбце.​ = B2 +​В колонке E мы​

Ссылки аргументы.

​ в первой графе,​Создадим строку «Итого». Найдем​

​ по-разному: относительные изменяются,​Меньше​При открытии книги, созданной​ определения числа процессоров,​.​ ячейках, или установить​F9​

​Пересчитывать книгу перед сохранением​ с другими листами​ содержащих формулы. Во​Предположим, у Вас есть​Формула обновится, и Вы​Татьяна фёдорова​ C2 (1,05 =​

  1. ​ вычислим новые цены​ щелкаем правой кнопкой​ общую стоимость всех​ абсолютные остаются постоянными.​>​ в более ранней​ используемых для пересчета.​Диапазон.
  2. ​В Excel 2007 нажмите​ конкретные ограничения для​Пересчет измененных после последнего​.​Инструмент Сумма.
  3. ​ или книгами. Можно​ избежание ненужных вычислений,​ формула для извлечения​ увидите новое значение.​: На листе 4​ 0,86 + 0,19).​

Результат автосуммы.

​ без НДС+7%. Вводим​ мыши. Нажимаем «Вставить».​ товаров. Выделяем числовые​Все ссылки на ячейки​Больше​

  1. ​ версии Excel, пересчитываются​ Во многих случаях​Кнопку Microsoft Office​ одного или нескольких​ вычисления формул и​Для ручного пересчета всех​ изменить способ выполнения​ которые забрать время​ доменных имён из​Если Вы передумаете, можно​Формула доли в процентах.
  2. ​ изменить сумму в​ Теперь скопируем содержимое​ формулу: =B2*1,07. Копируем​ Или жмем сначала​ значения столбца «Стоимость»​ программа считает относительными,​Меньше или равно​ все формулы в​Процентный формат.
  3. ​ пересчет может выполняться​, выберите пункт​ значений при вычислениях,​ формул, зависящих от​ открытых книг, включая​ вычислений, установив параметр​ и замедлить работу​ URL-адресов. Вы хотите​ нажать клавишу​

Сумма процентов.

​ последнем столбце путем​ C2 в целый​ это формулу во​

  • ​ комбинацию клавиш: CTRL+ПРОБЕЛ,​ плюс еще одну​ если пользователем не​
  • ​>=​ книге. Это гарантирует​
  • ​ частями одновременно. Распределение​Параметры Excel​

Как составить таблицу в Excel с формулами

​ воспользуйтесь командой «Поиск​ них, в активном​ таблицы данных, и​ пересчета вручную.​ компьютера, Microsoft Office​ заменить её результаты​Esс​ изменения значений в​ диапазон C2:C5.​

​ все соответствующие ячейки​ чтобы выделить весь​

  1. ​ ячейку. Это диапазон​ задано другое условие.​Больше или равно​ полную оптимизацию книги​ нагрузки между несколькими​и выберите категорию​ решения».​ листе.​ обновления всех открытых​Важно:​ Excel автоматически пересчитывает​ на значения.​
  2. ​на клавиатуре или​ столбце D.​Примечание. Так как у​ таблицы колонки E.​ столбец листа. А​ D2:D9​ С помощью относительных​<>​ для использования в​Новая графа.
  3. ​ процессорами может уменьшить​Дополнительно​Изменяя точность вычислений в​SHIFT+F9​ листов диаграмм на​ Изменение любого из этих​ формулы только при​Выполните следующие простые действия:​ щелкнуть команду​№ п/пНаименование товараст-ть​ нас только 2​ Так же скопируем​Дата.
  4. ​ потом комбинация: CTRL+SHIFT+»=»,​Воспользуемся функцией автозаполнения. Кнопка​ ссылок можно размножить​Не равно​ текущей версии Excel.​ общее время пересчета.​В разделе​ книге, необходимо учитывать​

Среднее. Результат.

​Пересчет всех формул во​ вкладке​ параметров влияет на​ изменении влияющих на​

exceltable.com

Пример как изменить сразу все цены в Excel

​Выделите все ячейки с​Отмена​ ед. ст-ть доб.​ слагаемых нам не​

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

Как сделать наценку на товар в Excel?

​Формулы​ все открытые книги.​ формулу ячеек. Это​ формулами, которые Вы​в Строке формул,​ Итого​ нужно округлять значения​ B1 в E1.​Назовем новую графу «№​

НДС 22%.

​ «Главная» в группе​ же формулу на​ при умножении. Опускать​ когда книга находится​Файл​выберите нужную книгу​ ниже.​

​ независимо от того,​

Способ 1 изменение цен в Excel

  1. ​в группе​На вкладке​ является стандартной процедурой​ хотите преобразовать.​ чтобы избежать случайных​1товар 115045635057​ в колонке B.​ Как посчитать процент​ п/п». Вводим в​ инструментов «Редактирование».​ несколько строк или​ его, как принято​ в другом режиме​нажмите кнопку​ и установите флажок​По умолчанию в вычислениях​ были ли они​Вычисление​Файл​ при первом открытии​НДС 22%.
  2. ​Нажмите​ изменений.​2товар 22002461001​ Ошибок уже 100%​ повышения цены в​ первую ячейку «1»,​После нажатия на значок​ столбцов.​ во время письменных​ вычислений, например ручном.​Параметры​
  3. ​Задать указанную точность​ используются хранимые, а​ изменены после прошлого​нажмите кнопку​нажмите кнопку​ книги и ее​

Цены увеличены на 7%.

​Ctrl+C​Чтобы показать все формулы​3товар 34501291245​ не будет.​ Excel, чтобы проверить​ во вторую –​

Способ 2 позволяет сразу изменить цены в столбце Excel

  1. ​ «Сумма» (или комбинации​Вручную заполним первые графы​ арифметических вычислений, недопустимо.​Поскольку полный пересчет требует​и выберите категорию​
  2. ​.​ не отображаемые значения​ пересчета.​Пересчет​Параметры​ редактировании. Однако тем,​или​ в электронной таблице​4товар 43207641117​Ошибки округлений в Excel​ результат? Очень просто!​ «2». Выделяем первые​ клавиш ALT+«=») слаживаются​

Специальная вставка, умножить. Увеличение цен.

​ учебной таблицы. У​ То есть запись​ больше времени, чем​Дополнительно​Хотя Excel ограничивает точность​

​   ​CTRL+ALT+F9​.​и выберите категорию​

​ как и когда​Ctrl+Ins​ Excel, вы можете​5товар 5653246977​ вовсе не удивляют​ В ячейке F2​ две ячейки –​ выделенные числа и​ нас – такой​

Результат функции ОКРУГЛ.

​ (2+3)5 Excel не​ частичный, открытие книги,​.​ до 15 десятичных​Отображаемые и печатаемые значения​Проверка зависимых формул, а​Для ручного пересчета активного​

​Формулы​ Excel будет пересчитывать​, чтобы скопировать формулы​ воспользоваться комбинацией клавиш​6товар 687389983​ опытных пользователей. Уже​ задайте процентный формат​ «цепляем» левой кнопкой​ отображается результат в​

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

exceltable.com

Как изменить в Excel числа в столбце по формуле

​Ctrl + `​​7товар 72765631295​ не раз рассматривались​ и введите формулу​ мыши маркер автозаполнения​ пустой ячейке.​Вспомним из математики: чтобы​Программу Excel можно использовать​ сохранена в формате​Кнопку Microsoft Office​ означает, что максимальное​ формата и отображения​ формул во всех​ и листов диаграмм,​В Excel 2007 нажмите​Итерация​ в буфер обмена.​(апостроф). При нажатии​8товар 817415691775​ примеры по данному​

​ наценки на товар:​​ – тянем вниз.​Сделаем еще один столбец,​ найти стоимость нескольких​

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

​Кнопку Microsoft Office​​     — это многократный​Нажмите​ этой комбинации еще​9товар 979524514136​ вопросу и еще​ =(E2-B2)/B2. Наценка составляет​
​По такому же принципу​ где рассчитаем долю​ единиц товара, нужно​

​ есть вводить в​​ Excel, может потребовать​Параметры Excel​ хранить в Excel,​ ячейка, в которой​ от того, были​
​ листом, на вкладке​, выберите пункт​ пересчет листа до​
​Shift+F10​
​ раз, все вернется​
​10товар 1019385433727​
​ не рас будут​
​ 7%.​
​ можно заполнить, например,​
​ каждого товара в​
​ цену за 1​
​ формулу числа и​
​ больше времени. После​

​и выберите категорию​

Источник

Замена значения в формуле

jocker229

 Дата: Среда, 30.10.2013, 14:57 |
Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 2


Репутация:
0
±

Замечаний:
0% ±


Excel 2003

Прошу помощи, следующий вопрос:
Имеется столбец содержащий n-ое количество однотипных формул, нужно заменить одно значение во всех формулах.
Например формула B1=100*A1/12 и таких формул весь столбец B. 100 — значение постоянное, 12 — переменное (то есть может быть и не 12).
Нужно, к примеру, 100 заменить на 200.
Как сделать это быстро???
 

Ответить

китин

 Дата: Среда, 30.10.2013, 14:59 |
Сообщение № 2

Группа: Модераторы

Ранг: Экселист

Сообщений: 6973


Репутация:
1063
±

Замечаний:
0% ±


Excel 2007;2010;2016

Сtrl+H.найти 100.заменит 200

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

 

Ответить

Pelena

 Дата: Среда, 30.10.2013, 15:02 |
Сообщение № 3

Группа: Админы

Ранг: Местный житель

Сообщений: 18797


Репутация:
4284
±

Замечаний:
±


Excel 2016 & Mac Excel

Можно попробовать через диалоговое окно Найти/Заменить

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

«Черт возьми, Холмс! Но как??!!»
Ю-money 41001765434816

 

Ответить

jocker229

 Дата: Среда, 30.10.2013, 15:05 |
Сообщение № 4

Группа: Пользователи

Ранг: Прохожий

Сообщений: 2


Репутация:
0
±

Замечаний:
0% ±


Excel 2003

Спасибо за советы!
 

Ответить

_Boroda_

 Дата: Среда, 30.10.2013, 15:07 |
Сообщение № 5

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16618


Репутация:
6465
±

Замечаний:
0% ±


2003; 2007; 2010; 2013 RUS

Сtrl+H.найти 100.заменит 200

Не прокатит.
В строке № 100 находится такая формула

и после замены получится

А вот если в «Найти» написать «=100» (без кавычек), а в «Заменить на» «=200», тогда прокатит.
а грамотнее, конечно же, последовать совету Лены и вынести константу или в имена, или в отдельную ячейку.

Скажи мне, кудесник, любимец ба’гов…
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

 

Ответить

китин

 Дата: Среда, 30.10.2013, 15:09 |
Сообщение № 6

Группа: Модераторы

Ранг: Экселист

Сообщений: 6973


Репутация:
1063
±

Замечаний:
0% ±


Excel 2007;2010;2016


точно,спасибо :(

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

 

Ответить

Alex_ST

 Дата: Среда, 30.10.2013, 21:55 |
Сообщение № 7

Группа: Друзья

Ранг: Участник клуба

Сообщений: 3176


Репутация:
604
±

Замечаний:
0% ±


2003

вынести константу или в имена, или в отдельную ячейку

лучше всё-таки, наверное, в имена или в ячейку скрытого листа чтобы на рабочем листе не «светилась» и кем-то случайно не покоцалась


С уважением,
Алексей
MS Excel 2003 — the best!!!

 

Ответить

Евгений

 Дата: Среда, 16.04.2014, 14:45 |
Сообщение № 8

Здравствуйте Уважаемые форумчане!!! Подскажите пожалуйста как сделать ту же операцию, но уже с датой. Например в формулах типа =СЧЁТЕСЛИ(I:I;»>16.02.2014″), заменить дату на указанную в отдельной ячейке. Решение через Найти/Заменить не подходит, а при ссылке на ячейку с произвольной датой выдает неправильное значение. Спасибо Всем заранее.
 

Ответить

китин

 Дата: Среда, 16.04.2014, 14:58 |
Сообщение № 9

Группа: Модераторы

Ранг: Экселист

Сообщений: 6973


Репутация:
1063
±

Замечаний:
0% ±


Excel 2007;2010;2016

в вашей формуле замените на в W1 ваша дата

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Сообщение отредактировал китин — Среда, 16.04.2014, 15:02
 

Ответить

John

 Дата: Среда, 16.04.2014, 16:08 |
Сообщение № 10

Группа: Пользователи

Ранг: Прохожий

Сообщений: 1


Репутация:
0
±

Замечаний:
0% ±


Excel 2007

Спасибо огромное!!! Все работает!!!! hands
 

Ответить

Oleol

 Дата: Четверг, 09.03.2017, 12:47 |
Сообщение № 11

Группа: Пользователи

Ранг: Прохожий

Сообщений: 1


Репутация:
0
±

Замечаний:
0% ±


Excel 2007

Здравствуйте. Можно ли как-то упростить работу. Каждый месяц мне приходиться в ручную менять цифры во множестве таблиц, например: есть сумма значений по одной строке =+'[Seadmed 2017a.xls]JAAN. 17′!$E$10+'[Seadmed 2017a.xls]JAAN. 17′!$F$10+'[Seadmed 2017a.xls]JAAN. 17′!$H$10+'[Seadmed 2017a.xls]JAAN. 17′!$I$10+'[Seadmed 2017a.xls]JAAN. 17′!$K$10+'[Seadmed 2017a.xls]JAAN. 17′!$L$10, в следующей строке должна быть формула =+'[Seadmed 2017a.xls]JAAN. 17′!$E$11+'[Seadmed 2017a.xls]JAAN. 17′!$F$11+'[Seadmed 2017a.xls]JAAN. 17′!$H$11+'[Seadmed 2017a.xls]JAAN. 17′!$I$11+'[Seadmed 2017a.xls]JAAN. 17′!$K$11+'[Seadmed 2017a.xls]JAAN. 17′!$L$11 , и так далее по возрастанию. Можно как-нибудь облегчить работу?
 

Ответить

Pelena

 Дата: Четверг, 09.03.2017, 14:38 |
Сообщение № 12

Группа: Админы

Ранг: Местный житель

Сообщений: 18797


Репутация:
4284
±

Замечаний:
±


Excel 2016 & Mac Excel

Oleol, прочитайте Правила форума и создайте свою тему. Эта тема закрыта

«Черт возьми, Холмс! Но как??!!»
Ю-money 41001765434816

 

Ответить
Источник

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

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

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

Таблица данных:

Таблица данных.

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

Для замены и подстановки.

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

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

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

подстановка значений во всех ячейках.

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



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

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

Таблица данных:

Таблица.

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

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

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

В результате.

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

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

Пример 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

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

= RegExpExtract(текст;регулярное_выражение;[номер_вхождения])

Регулярные выражения могут быть различными. Например, для выделения любого символа из текстовой строки в качестве второго аргумента необходимо передать значение «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. Для решения аналогичных задач по замене части символов текстовой строки можно использовать функцию ЗАМЕНИТЬ. Однако, в отличие от функции ПОДСТАВИТЬ, для ее использования необходимо явно указывать позицию начального символа для замены, а также количество символов, которые необходимо заменить. Функция автоматически выполняет поиск указанной части строки и производит ее замену, поэтому в большинстве случаев предлагает более удобный функционал для работы с текстовыми строками.
Источник

Понравилась статья? Поделить с друзьями:

А вот еще интересные статьи:

  • В excel изменение номера ячейке
  • В excel значок доллара что это такое
  • В excel значение ячейки за единицу
  • В excel значение меняется на дату
  • В excel значение заменить на во всех столбцах

    • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии