Как вставить формулу в ячейку excel: Как вставить формулу в ячейку (таблицу) «Excel».

excel — Вставить формулу в первую пустую ячейку и протянуть до последней строки

Задать вопрос

Вопрос задан
3 года 7 месяцев назад

Изменён
3 года 7 месяцев назад

Просмотрен
455 раз

Sub CommandButton1_Click()
    Dim lCol As Long
    Dim LR As Long
'до последнеи строки и столбца   
    LR = Cells(Rows.Count, "A").End(xlUp).Row
    lCol = Cells(10, Columns.Count).End(xlToLeft).Column
'вставить формулу в первии пустои столбец
    Columns(lCol).Select
    Range(Cells(10, lCol + 1), Cells(10, lCol + 1)).Formula = "=IFERROR(INDEX(Sas!$A$10:$AC200,MATCH(h20,Sas!N$10:N$200,0),5),0)"
'протянуть до конца 
    Range("J10").Select
    Selection.AutoFill Destination:=Range("J10:J" & LR), Type:=xlFillDefault
'протянуть следуищи
    Range("K10"). Select
    Selection.AutoFill Destination:=Range("K10:K" & LR), Type:=xlFillDefault
'протянуть следуищи    
    Range("L10").Select
    Selection.AutoFill Destination:=Range("L10:L" & LR), Type:=xlFillDefault
    Range("M10").Select
    Selection.AutoFill Destination:=Range("M10:M" & LR), Type:=xlFillDefault
'столбцов будет много,потому надо штобы формула протянулась автоматищески
'то есть  "J10","K10"...заменить в переменную,никак не получяетца    
End Sub

• excel
• vba

5

Если вставка в каждый новый столбец по нажатию кнопки, не нужно в процедуре указывать все, ведь код должен обработать только один столбец.

Sub CommandButton1_Click()
    Dim LR As Long, lCol As Long
    LR = Cells(Rows.Count, "A").End(xlUp).Row  ' последняя строка с данными'
    lCol = Cells(10, Columns.Count).End(xlToLeft).Column ' последний столбец с данными'
    ' вставить формулу в первую строку таблицы'
    Cells(10, lCol + 1). Formula = "=IFERROR(INDEX(Sas!$A$10:$AC200,MATCH(h20,Sas!N$10:N$200,0),5),0)"
    ' протянуть формулу по строкам'
    If LR > 10 Then Cells(10, lCol + 1).Resize(LR - 9, 1).FillDown
End Sub

4

Зарегистрируйтесь или войдите

Регистрация через Google

Регистрация через Facebook

Регистрация через почту

Отправить без регистрации

Почта

Необходима, но никому не показывается

Отправить без регистрации

Почта

Необходима, но никому не показывается

Нажимая на кнопку «Отправить ответ», вы соглашаетесь с нашими пользовательским соглашением, политикой конфиденциальности и политикой о куки

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

Функциональные возможности программы Excel позволяют не только структурировать и работать с большими объемами данных, но и производить различные расчеты. Зачастую после того, как получен результат, рассчитанный по формуле, в ней больше нет необходимости, и в ячейке требуется оставить именно само значение. Более того, в некоторых случаях наличие формулы будет только мешать в последующей работе. Например, если попробовать перенести или скопировать данные из ячейки с формулой в другое место таблицы, результат вычислений будет утерян или изменится, т.к. при выполнении данной процедуры будут изменены ссылки на ячейки, указанные в формуле, за исключением тех случаев, когда вместо относительные ссылок (по умолчанию) использовались абсолютные.

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

Содержание

1. Удаление формул
   • Метод 1: копируем значения с помощью параметров вставки
   • Метод 2: используем специальную вставку
   • Метод 3: удаляем формулы в исходной таблице
   • Метод 4: удаляем формулы без копирования в другое место
   • Метод 5: применяем макрос
   • Метод 6: удаляем формулу вместе с результатом вычислений
2. Заключение

Удаление формул

Смотрите также: “Как разъединить объединенные ячейки в Excel”

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

Метод 1: копируем значения с помощью параметров вставки

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

1. Для начала с помощью зажатой левой кнопки мыши нужно выделить область ячеек, которую мы хотим скопировать.
2. Правой кнопкой мыши щелкаем по любой точке выделенного диапазона и в открывшемся контекстном меню выбираем пункт “Копировать”. Также, можно вместо этого действия можно просто нажать сочетание клавиш Ctrl+C (после того, как выполнено выделение).Для копирования данных можно также воспользоваться кнопкой “Копировать”, которая расположена на лента программы во вкладке “Главная”.
3. Переходим в ячейку, начиная с которой мы хотим вставить скопированные данные (эта ячейка станет самой верхней левой точкой диапазона данных, который мы будем вставлять из буфера обмена). Затем правой кнопкой мыши щелкаем по выбранному элементу, в появившемся меню в группе “Параметры вставки” кликаем по варианту “Значения” (значок в виде цифр “123”).
4. В результате скопированная область данных (только конкретные числовые значения, без формул) будет вставлена в новое место.

Метод 2: используем специальную вставку

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

1. Выделяем и копируем требуемый диапазон данных. На этот раз давайте отметим всю таблицу, а для копирования используем кнопку на ленте инструментов.
2. Переходим в ячейку, начиная с которой планируем вставить скопированные элементы. Щелкаем правой кнопкой мыши по ней, в раскрывшемся контекстном меню кликаем (или наводим указатель мыши) по небольшой стрелке вправо рядом с командой “Специальная вставка”. Всплывет дополнительное подменю, в котором щелкаем по пункту “Значения и исходное форматирование”.
3. Мы получим исходную таблицу в новом месте с охранением форматирования и конкретными числовыми значениями вместо формул.

Метод 3: удаляем формулы в исходной таблице

Смотрите также: “Как вычитать числа в Экселе: формула, примеры”

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

1. Копируем нужный диапазон ячеек любым удобным способом, например, воспользовавшись контекстным меню.
2. Как и в ранее рассмотренном методе, вставляем скопированную область в новое место с сохранением исходного форматирования. После этого, не снимая выделение, копируем только что вставленные данные, например, нажав комбинацию клавиш Ctrl+C (или любым другим удобным способом).
3. Переходим в самую верхнюю левую ячейку исходной таблицы (или выделяем в ней тот диапазон ячеек, который был скопирован в 1 шаге), кликом правой кнопки мыши вызываем меню, в котором выбираем вставку “Значений”.
4. Теперь, когда содержимое ячеек без формул скопировано в исходное место на листе, удаляем созданные дубликаты. Для этого, выделяем нужный диапазон данных, щелкаем по нему правой кнопкой мыши и в раскрывшемся меню выбираем пункт “Удалить”.
5. Появится окно удаления ячеек. Выбираем то, что нужно удалить. Исходя из нашего примера, ставим отметку напротив пункта “строку”, после чего жмем кнопку OK.Также, так как справа от выделенного диапазона нет заполненных ячеек, можно выбрать вариант – “ячейки, со сдвигом влево”.
6. Задублированный диапазон данных удален. На этом работа по замене формул на конкретные значения в исходной таблице завершена.

Метод 4: удаляем формулы без копирования в другое место

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

1. Как обычно, сперва нужно выделить область ячеек, в которых нужно удалить формулы. Затем копируем данные любым удобным способом. Проще всего это сделать, нажав на кнопку “Копировать” на ленте программы (вкладка “Главная”).
2. Теперь, оставляя скопированную область выделенной, щелкаем по ней правой кнопкой мыши и в появившемся меню в группе команд “Параметры вставки” кликаем по варианту “Значения”.
3. В итоге, мы в том же самом месте таблицы в выделенных ячейках вместо формул вставим конкретные значения (результаты вычислений).

Метод 5: применяем макрос

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

1. Щелкаем по меню “Файл”.
2. В открывшемся окне в боковом перечне слева в самом низу выбираем раздел “Параметры”.
3. В параметрах программы переходим в подраздел “Настроить ленту”. В правой части окна ставим галочку напротив пункта “Разработчик”, после чего щелкаем OK.

Теперь все готово, чтобы выполнить поставленную задачу:

1. Переключаемся во вкладку “Разработчик”, в которой щелкаем по кнопке “Visual Basic” (группа “Код”).
2. Выбрав нужный лист книги нажимаем на кнопку “View Code” (или дважды щелкаем левой кнопкой мыши по выбранному листу), чтобы запустить редактор макросов, куда вставляем код ниже, после чего закрываем данное окно.
   Sub Удаление_формул()
Selection.Value = Selection.Value
End Sub

3. Выделяем диапазон ячеек, содержащих формулы, и нажимаем кнопку “Макросы”, которая расположена во вкладке “Разработчик” (блок инструментов “Код”).
4. В открывшемся окне выбора макросов отмечаем нужный и щелкаем по кнопке “Выполнить”.
5. После того, как процедура будет выполнена, все формулы в выбранных ячейках будут заменены результатами расчетов по ним.

Метод 6: удаляем формулу вместе с результатом вычислений

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

1. Начинаем с того, что выделяем область ячеек, содержащих формулы. Кликом правой кнопки мыши по выделенному диапазону вызываем меню, в котором щелкаем по команде “Очистить содержимое”.Также, вместо этого, после того, как нужные элементы отмечены, можно просто нажать клавишу Del (Delete) на клавиатуре.
2. В итоге все данные в ячейках, в том числе, формулы будут стерты.

Заключение

Смотрите также: “Как сделать сумму прописью с копейками в Эксель: надстройка, формула”

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

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

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

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

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

Самый распространенный и простой способ добавить формулу и текст в одну и ту же ячейку — использовать формулу, состоящую из оператора амперсанда. Шаги этой техники упомянуты ниже. Этот процесс применим для версий 2016/2019/mac/online.

1. Сначала мы выберем диапазон ячеек, который мы хотим объединить с формулой. В этом случае мы объединим ячейки A2, B2 и C2 и получим текст «ежедневный доход»

.

2. Затем мы перейдем в поле формул и введем формулу =A2& ежедневный доход: &СУММ(B2,C2).

3. Это приведет к созданию комбинированных данных, состоящих как из текста, так и из формулы.

Этот процесс работает с версиями 2016/2019/mac/online.

Получите бесплатную подписку WPS Premium на 7 дней Загрузить сейчас

Как добавить текст и формулу в одну ячейку с помощью текстовой функции?

Еще одна важная функция объединения текста и формулы — использование функции «Текст». Это быстрый и простой способ объединения текста и формулы в одной ячейке. Шаги для доступа к функции «Текст» следующие. Эта функция совместима с 2016/2019/mac/онлайн версии.

1. Сначала мы выберем диапазон ячеек, который мы хотим добавить с формулой. В этом случае диапазон ячеек — от A2 до C2, а текст — «общая зарплата».

2. После выбора диапазона ячеек мы введем формулу в поле формулы =A2& общая зарплата: &ТЕКСТ(B2*C2,h).

3. Это даст нам сумму общей зарплаты выбранных ячеек, включая текст и формулу.

Эта функция работает с версиями 2016/2019/mac/online.

Как добавить текст и формулу в одну ячейку с помощью функции СЦЕПИТЬ?

Функция конкатенации также является важной функцией для объединения текста и формулы. Это очень полезная функция, которая помогает пользователям Excel добавлять формулу и текст в одну и ту же ячейку с помощью формулы. Шаги для активации этой функции приведены ниже. Эта функция работает с версиями 2016/2019/mac/online.

1. Сначала мы выделим диапазон ячеек. В этом случае текст «Общая зарплата Али составляет».

2. Затем мы введем формулу в поле формулы = СЦЕПИТЬ (общая зарплата Али: , B2 * C2).

3. Это приведет к добавлению как текста, так и формулы, что приведет к объединению данных.

Этот метод применим для версий 2016/2019/mac/online.

Узнали ли вы, как добавить текст и формулу в одну и ту же ячейку в Excel, используя разные методы? Вы можете подписаться на WPS Academy, чтобы узнать больше о возможностях документов Word, электронных таблиц Excel и слайдов PowerPoint.

Вы также можете бесплатно загрузить WPS Office для редактирования документов Word, Excel и PowerPoint. Скачать сейчас! И получить легкий и приятный опыт работы.

Добавление текста и формулы в одну ячейку в Excel (4 примера)

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

Скачать практическую рабочую тетрадь

4 примера добавления текста и формулы в одну и ту же ячейку в Excel

Пример 1: добавление текста и формулы в одну и ту же ячейку в Excel

Добавить текст и формулу в любой последовательности

Пример 2. Добавление текста и формулы в одну и ту же ячейку в Excel с помощью функции ТЕКСТ

Пример 3: добавление текста и формулы в одну и ту же ячейку в Excel

Пример 4. Использование функции СЦЕПИТЬ для добавления текста и формулы в одну и ту же ячейку в Excel

То, что нужно запомнить

Заключение

Статьи по Теме

Загрузить рабочую тетрадь

Вы можете загрузить файл Excel по следующей ссылке и попрактиковаться вместе с ним.

4 примера добавления текста и формулы в одну и ту же ячейку в Excel

Пример 1. Добавление текста и формулы в одну и ту же ячейку в Excel

Посмотрите на следующую картинку. Список доходов и расходов состоит из 4 столбцов. Мы подсчитали ежемесячную экономию для каждого человека в столбце Имя.

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

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

Мы можем объединить как описательный текст, так и формулы, чтобы сделать эти числа более читабельными.

Чтобы добавить текст и формулу в одну и ту же ячейку в Excel,

❶ Введите следующую формулу в ячейку E5 и нажмите кнопку ENTER .

=B5&" ежемесячная экономия: "&C5-D5

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

Например, в приведенной выше формуле сначала мы вставили ячейку B5 . Затем мы вставили текст «ежемесячные сбережения:» . Чтобы отделить эту текстовую строку от ссылки на ячейку и формулы, мы использовали символ и .

❷ Чтобы применить ту же формулу к остальной части ячейки, наведите курсор мыши на правый нижний угол ячейки E5 и перетащите значок Ручка заполнения вверх до ячейки E10 . Вот и все.

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

Добавить текст и формулу в любой последовательности

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

Например, следующая формула сначала добавит текст, а затем формулу:

="Ежемесячная экономия Роберта: "&C5-D5

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

=C5-D5 &" - ​​ежемесячные сбережения Роберта."

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

Подробнее: Заменить текст ячейки на основе условия в Excel (5 простых методов)

Пример 2.

Добавление текста и формулы в одну и ту же ячейку в Excel с помощью функции ТЕКСТ

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

Итак, приступим.

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

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

❶ Сначала введите в ячейку следующую формулу: E5, и нажмите кнопку ENTER .

=B5&" общее количество рабочих часов: "&TEXT(D5-C5,"h")

В эту формулу мы сначала вставили ячейку B5 . Затем мы использовали текстовую строку. Чтобы отделить текстовую строку от значения ячейки, B5 , мы использовали символ и и двойную кавычку ( «»). Наконец, мы использовали функцию TEXT с ее аргументами.

❷ Перетащите значок Ручка заполнения из ячейки E5 в E10 .

После этого таблица данных будет выглядеть так:

Подробнее: Как заменить текст в формуле Excel (7 простых способов)

Аналогичные показания:

• Поиск и замена текста в диапазоне с помощью Excel VBA (макрос и пользовательская форма)
• Как найти и заменить с помощью формулы в Excel (4 примера)
• Поиск и замена значений в нескольких файлах Excel (3 метода)
• Как найти и заменить символ звездочки (*) в Excel
• Как заменить специальные символы в Excel (6 способов)

Пример 3. Добавление текста и формулы в одну и ту же ячейку в Excel

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

Для этого

❶ Вставьте следующую формулу в ячейку, Е5 .

=B5&" из "&ТЕКСТ(C5,"дд-ммм-гггг")&" в "&ТЕКСТ(D5,"дд-ммм-гггг")

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

После этого идет другой текст, также разделенный символом и и двойные кавычки ( » ). Затем приходит вторая функция TEXT с соответствующими аргументами.

❷ Затем нажмите кнопку ВВОД .

❸ Чтобы применить указанную выше формулу ко всем ячейкам, перетащите значок Ручка заполнения из ячейки E5 в ячейку E10 .

Это все, что вам нужно сделать.

Подробнее: Как добавить текст перед формулой в Excel (4 простых способа)

Пример 4. Использование функции СЦЕПИТЬ для добавления текста и формулы в одну и ту же ячейку в Excel

Вы можете использовать функцию СЦЕПИТЬ , чтобы добавить текст и формулу в одну и ту же ячейку в Excel.

Для этого выполните следующие действия:

❶ Прежде всего введите в ячейку следующую формулу: E5 .

=СЦЕПИТЬ("Ежемесячная экономия Роберта: ",C5-D5)

❷ После этого нажмите ВВОД кнопка.

❸ Перетащите значок Ручка заполнения из ячейки E5 в ячейку E10 , и все готово.

Связанное содержимое: Excel VBA: как найти и заменить текст в документе Word

Что нужно помнить

📌 Чтобы разделить текст и формулу, используйте символы и и двойные кавычки ( « ) между ними.