Как добавить число в каждую ячейку столбца в Excel

Обновлено: 24.11.2024

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

Хорошо, что вам не нужно делать это вручную.

Excel предоставляет несколько действительно простых способов добавления текста в начало и/или конец текста в диапазоне ячеек.

В этом уроке мы увидим 4 способа сделать это:

  • Использование оператора амперсанда (&)
  • Использование функции СЦЕПИТЬ
  • Использование функции быстрой заливки
  • Использование VBA

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

Оглавление

Способ 1. Использование оператора амперсанда

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

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

Амперсанд (&) – это оператор, который в основном используется для объединения нескольких текстовых строк в одну.

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

Ниже приведены шаги для добавления текста перед текстовой строкой в ​​Excel:

  1. Нажмите на первую ячейку столбца, в котором должны отображаться преобразованные имена (B2).
  2. Введите знак равенства (=), а затем текст «Prof. “, за которым следует амперсанд (&).
  3. Выберите ячейку, содержащую имя (A2).
  4. Нажмите клавишу возврата.
  5. Вы заметите, что заголовок «Prof.» добавляется перед первым именем в списке.
  6. Пришло время скопировать эту формулу в остальные ячейки столбца. Просто дважды щелкните маркер заполнения (расположенный в правом нижнем углу ячейки B2). Кроме того, вы можете перетащить маркер заполнения вниз, чтобы добиться того же эффекта.

    7. Вот и все, все ваши ячейки в столбце B теперь должны содержать заголовок «Профессор». перед каждым именем.

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

    Теперь давайте посмотрим, как добавить текст в конец каждого имени в наборе данных. Допустим, вы хотите добавить текст «(MD)» в конце каждого имени. В этом случае выполните следующие действия:

    1. Нажмите на первую ячейку столбца, в котором должны отображаться преобразованные имена (в нашем случае C2).
    2. Введите знак равенства (=)
    3. Выберите ячейку, содержащую имя (в нашем случае B2).
    4. Далее вставьте амперсанд (&), а затем текст "(MD)".
    5. Нажмите клавишу возврата.
    6. Вы заметите, что текст «(MD)». добавляется после первого имени в списке.
    7. Пришло время скопировать эту формулу в остальные ячейки столбца. Просто дважды щелкните маркер заполнения (расположенный в правом нижнем углу ячейки C2). Кроме того, вы можете перетащить маркер заполнения вниз, чтобы добиться того же эффекта.

      8. Все ваши ячейки в столбце C теперь должны содержать текст "(MD") в конце каждого имени.

      Способ 2. Использование функции СЦЕПИТЬ

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

      Давайте посмотрим, как это сделать с помощью команды СЦЕПИТЬ.

      Использование функции СЦЕПИТЬ для добавления текста в начало всех ячеек

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

      Общий синтаксис функции СЦЕПИТЬ:

      Где text1, text2 и т. д. — подстроки, которые вы хотите объединить.

      Давайте применим функцию СЦЕПИТЬ к тому же набору данных, что и выше:

      1. Нажмите на первую ячейку столбца, в котором должны отображаться преобразованные имена (B2).
      2. Введите знак равенства (=).
      3. Введите функцию СЦЕПИТЬ, за которой следует открывающая квадратная скобка (.
      4. Введите заголовок «Prof. ” в двойных кавычках с последующей запятой (,).
      5. Выберите ячейку, содержащую имя (A2)
      6. Поместите закрывающую скобку. В нашем примере ваша формула теперь должна быть: = СЦЕПИТЬ («Профессор», A2).
      7. Нажмите клавишу возврата.
      8. Вы заметите, что заголовок «Prof.» добавляется перед первым именем в списке.
      9. Пришло время скопировать эту формулу в остальные ячейки столбца. Просто дважды щелкните маркер заполнения (расположенный в правом нижнем углу ячейки B2). Кроме того, вы можете перетащить маркер заполнения вниз, чтобы добиться того же эффекта.

        10. Вот и все, все ваши ячейки в столбце B теперь должны содержать заголовок «Профессор». перед каждым именем.

        Использование функции СЦЕПИТЬ для добавления текста в конец всех ячеек

        Теперь давайте посмотрим, как добавить текст в конец каждого имени в наборе данных. Допустим, вы хотите добавить текст «(MD)» в конце каждого имени. В этом случае выполните следующие действия:

        1. Нажмите на первую ячейку столбца, в котором должны отображаться преобразованные имена (в нашем примере C2).
        2. Введите знак равенства (=).
        3. Введите функцию СЦЕПИТЬ, за которой следует открывающая квадратная скобка (.
        4. Выберите ячейку, содержащую имя (в нашем примере B2).
        5. Далее вставьте запятую, а затем текст «(MD)».
        6. Поместите закрывающую скобку. В нашем примере ваша формула теперь должна быть такой: =СЦЕПИТЬ(B2,"(MD)").
        7. Нажмите клавишу возврата.
        8. Вы заметите, что текст «(MD)». добавляется после первого имени в списке.
        9. Пришло время скопировать эту формулу в остальные ячейки столбца. Просто дважды щелкните маркер заполнения (в правом нижнем углу ячейки C2).

          10. Все ваши ячейки в столбце C теперь должны содержать текст "(MD") в конце каждого имени.

          Обратите внимание, что, поскольку вы используете формулу, ваш столбец C зависит от столбцов A и B. Поэтому, если вы вносите какие-либо изменения в исходные значения в столбце A, они отражаются в столбце C.

          Если вы решите сохранить только преобразованные имена и удалить столбцы A и B, вы получите сообщение об ошибке, как показано ниже:

          Чтобы этого не произошло, лучше всего сначала преобразовать результаты формулы в постоянные значения, скопировав их и вставив как значения в тот же столбец (щелкните правой кнопкой мыши и выберите Параметры вставки- >Значения во всплывающем меню).

          Теперь вы можете при необходимости удалить столбцы A и B.

          Способ 3. Использование функции быстрой заливки

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

          Вы также можете использовать Flash fill для обработки текста, как мы увидим в следующих примерах.

          Использование быстрой заливки для добавления текста в начало всех ячеек

          Функция быстрого заполнения Excel похожа на волшебную кнопку. Он доступен, если вы используете любую версию Excel, начиная с 2013 года.

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

          Вот как можно использовать мгновенную заливку для добавления текста в начало всех ячеек в столбце:

          1. Нажмите на первую ячейку столбца, в котором должны отображаться преобразованные имена (B2).
          2. Вручную введите текст Prof. , а затем первое имя из вашего списка.
          3. Нажмите клавишу возврата.
          4. Снова нажмите на ячейку B2.
          5. На вкладке Данные нажмите кнопку Быстрая заливка (в группе Инструменты данных). Кроме того, вы можете просто нажать CTRL+E на клавиатуре (Command+E, если вы работаете на Mac).

            6. Тот же шаблон будет скопирован в остальные ячейки столбца… мгновенно!

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

            Если вы хотите добавить текст «(MD)» в конец имен, выполните те же действия:

            Вот и все, каждая ячейка заполняется одним и тем же шаблоном!

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

            Поэтому они останутся неизменными, даже если вы удалите строки A и B!

            Способ 4. Использование кода VBA

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

            Использование VBA для добавления текста в начало всех ячеек в столбце

            Если кодирование с помощью VBA вас не пугает, этот метод также поможет вам быстро выполнить работу.

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

            Чтобы использовать приведенный выше код, выполните следующие действия:

            1. На ленте меню Разработчик выберите Visual Basic.
            2. Когда откроется окно VBA, нажмите Вставить->Модуль. Теперь можно приступать к кодированию. Введите или скопируйте и вставьте приведенные выше строки кода в окно модуля. Теперь ваш код готов к запуску.
            3. Выделите диапазон ячеек, содержащих текст, который вы хотите преобразовать. Убедитесь, что столбец рядом с ним пуст, потому что именно здесь код будет отображать результаты.
            4. Перейдите к Разработчик->Макросы-> add_text_to_beginning->Выполнить.

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

            Примечание. Вы можете изменить текст в строке 6 с «Prof. ” к любому тексту, который нужно добавить в начало всех ячеек.

            Использование VBA для добавления текста в конец всех ячеек в столбце

            Что делать, если вы хотите добавить текст в конец всех ячеек, а не в начало? Это включает в себя только настройку строки 6 приведенного выше кода. Поэтому, если вы хотите добавить текст «(MD)» в конец всех ячеек, измените строку 6 на:

            cell.Offset(0, 1).Value = cell.Value & "(MD)"

            Теперь ваш полный код должен выглядеть так:

            Вот окончательный результат:

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

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

            В этом руководстве мы показали четыре способа добавления текста в начало и/или конец всех ячеек в диапазоне.

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

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

            Если вы уже ввели число в ячейку или группу ячеек, как быстро добавить что-то к этой сумме? Вот как вы можете добавить число в несколько ячеек в Excel.

            Список дел на день

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

            Итак, я ввожу потраченное время — 0,75 часа — и перехожу к следующей задаче.

            Позже в тот же день я могу потратить еще 45 минут на задачи администратора, поэтому я хочу добавить это к предыдущей сумме в ячейке D5.
            Если сейчас конец дня, мне может быть трудно прибавить 0,75 + 0,75 в уме (или в начале дня, если я еще не выпил кофе!)

            Использовать специальную вставку

            Один из способов сделать это и избежать основных арифметических ошибок — использовать Специальная вставка — Добавить.

            • Введите число в ячейку и скопируйте эту ячейку.
            • Затем используйте Специальная вставка – Добавить, чтобы вставить эту сумму в другую ячейку.

            Этот метод работает хорошо, но требует нескольких шагов, и это увеличивает время моих задач администратора!

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

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

            • Макрос 1 добавляет установленный номер к выбранным ячейкам.
            • Макрос 2 просит вас ввести число, а затем добавляет это число в выбранные ячейки.

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

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

            Видео: добавление чисел в несколько ячеек в Excel

            Посмотрите это видео, чтобы узнать, как использовать команду "Специальная вставка" и как изменить код макроса, чтобы изменить числа.

            Загрузить образец файла

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

            Заархивированный файл имеет формат xlsm и содержит макросы. Включите макросы при открытии файла, если хотите протестировать код.
            ____________________

            5 мыслей о «Добавить число в несколько ячеек в Excel»

            спасибо, я легко решил свой вопрос с помощью вашего видео.

            Спасибо, мне помогло……

            Привет,
            Я использовал ваш макрос, чтобы добавить 1 к выбранным ячейкам, но он не работает, если числа отфильтрованы, также Ctrl+Z не работает после запуска макроса.
            Пожалуйста, посмотрите и помогите.

            Sub AddNumber()
            Dim ws As Worksheet
            Dim rngSel As Range
            Dim rng As Range
            Dim Num As Double
            Dim i As Long
            Dim j As Long
            Dim lAreas As Long
            Dim lRows As Long
            Dim lCols As Long
            Dim Arr() As Variant
            Set rngSel = Selection

            Для каждого rng ​​в rngSel.Areas
            Если rng.Count = 1, то
            rng = rng + Num
            иначе
            lRows = rng.Rows.Count
            lCols = rng.Columns.Count
            Arr = rng
            For i = 1 To lRows
            For j = 1 To lCols
            Arr(i, j) = Arr(i, j) ) + Num
            Следующий j
            Следующий i
            rng.Value = Arr
            End If
            Следующий rng

            Спасибо, я просмотрел все, чтобы найти это решение. Спасибо!

            Оставить ответ Отменить ответ

            Этот сайт использует Akismet для уменьшения количества спама. Узнайте, как обрабатываются данные ваших комментариев.

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

            Например, теперь я хочу добавить 30 к оценкам всех учащихся в столбце C.

            Введите число 30 в любую из ячеек рядом со столбцом C.

            Нажмите Ctrl+C, чтобы выбрать эту ячейку, или щелкните ее правой кнопкой мыши и выберите Копировать в меню. Затем выберите все оценки в столбце C.

            Перейдите на вкладку Главная и нажмите Вставить — Специальная вставка… или щелкните правой кнопкой мыши выделенные ячейки в столбце C и выберите Специальная вставка в разделе Параметры вставки.

            Выберите Добавить в разделе Операция во всплывающем окне Специальная вставка. Затем нажмите ОК, чтобы применить его.

            Введенное вами число будет сразу же добавлено ко всем ячейкам в столбце C.

            Подходящее чтение

            Вам также может понравиться

            Заявление об авторских правах. Любое копирование или использование любых публикаций на этом веб-сайте должно осуществляться с письменного разрешения или авторизации Myofficetricks.

            Нумерация ячеек — это задача, которую вы часто выполняете в Excel. Но написание числа вручную в каждой ячейке занимает много времени.

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

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

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

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

            Итак, если вы хотите начать с числа 5 и увеличивать его на 3 до 38, вы должны написать 5 в первой ячейке и 8 в следующей ячейке. Затем вы должны выбрать ячейки, содержащие 5 и 8, щелкнуть маркер и перетащить, чтобы выбрать другие ячейки, пока не увидите 38 во всплывающей подсказке. Затем вы можете отпустить, и числа будут заполнены автоматически.

            1) Выберите ячейки. 2) Перетащите ручку на контур (вы также можете увидеть всплывающую подсказку с последним числом в серии) 3) Отпустите

            Числа также можно отформатировать в порядке убывания: если вы начнете с 7, а затем введете 5, шаблон продолжится с 3, 1, -1 и т. д.

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

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

            Примечание: при этом всегда будут добавляться числа, равномерно распределенные по шаблону, который вы начали. Он не будет работать с другими типами числовых последовательностей.

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

            Чтобы избежать этого, вы можете динамически нумеровать строки с помощью функции ROW().

            В ячейке, с которой вы хотите начать нумерацию, напишите =СТРОКА(A1) . В результате в ячейке появится число 1.

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

            1) Напишите =СТРОКА(A1) в первой ячейке, 2) Она появится как число 1, 3) Щелкните и перетащите или дважды щелкните, чтобы заполнить все остальные ячейки. 4) Теперь, если вы отсортируете данные, номера строк останутся в порядке.

            Если вы хотите использовать другой обычный шаблон, вы можете использовать немного математики: чтобы числа разделялись на 2, вы можете написать =СТРОКА(A1) * 2 в первой ячейке, а затем продолжить те же шаги. как указано выше. Это даст числа 2, 4, 6 и т. д.

            Если вы хотите изменить начальную точку шаблона, чтобы, например, иметь только нечетные числа, вы можете вычесть единицу: =СТРОКА(A1) * 2 - 1 , это даст числа 1, 3, 5. , 7.

            В качестве общей формулы, чтобы получить любой шаблон, вы можете написать =СТРОКА(A1) * a + b . a используется для определения шага, а b (это может быть как положительное, так и отрицательное число) используется для изменения начальной точки шаблона.

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

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

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

            Есть несколько других способов нумерации ячеек в Excel, но эти самые простые.

            Читайте также: