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

Column_1*Column_2 & “ “ & Column_3

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

Вариант первый — простое сложение

Допустим, у вас есть несколько ячеек, содержащих числа, и вы хотите сложить эти числа вместе:

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

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

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

Также обратите внимание, что Excel не возражает, если вы вводите формулу в нижнем регистре, как показано ниже. Excel автоматически преобразует каждую букву в верхний регистр, когда вы нажимаете Enter:

то же самое, что набрать это:

Самое замечательное в сложении ячеек таким образом, что формула всегда будет показывать текущее значение суммы этих ячеек. Каждый раз, когда вы изменяете одно из значений в ячейках B2, B3, B4, B5 или B6, значение в B8 немедленно изменяется, чтобы показать правильную сумму этих ячеек.

Более быстрый способ сложить значения в нескольких ячейках — выполнить следующие шаги:

1. Щелкните ячейку, в которой вы хотите отобразить результат вычисления.
2. Введите = (нажмите клавишу равенства, чтобы начать писать формулу)
3. Нажмите на первую добавляемую ячейку (B2 в этом примере)
4. Введите + (это плюс)
5. Нажмите на вторую ячейку, которую нужно добавить (в данном примере A3)
6. Тип + снова, и следующая ячейка будет добавлена. ]
7. Повторяйте, пока не будут выбраны все добавляемые ячейки.
8. Нажмите Enter.

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

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

Второй вариант — используйте функцию СУММ() для сложения диапазона ячеек

Функция СУММ() — гораздо более эффективный способ сложения ячеек. Его можно использовать для сложения отдельных ячеек, как мы сделали в последнем примере. Однако он также позволяет вам суммировать диапазон ячеек, просто указав первую и последнюю ячейку в диапазоне ячеек, которые нужно суммировать. Затем функция СУММ() суммирует значения во всех ячейках от начала до конца диапазона. Это особенно мощно, потому что с его помощью можно написать простую формулу, состоящую из сотен или тысяч ячеек.

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

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

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

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

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

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

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

Это подводит нас к последнему примеру использования функции SUM(). Хотя не существует неправильного способа сложения ячеек в Excel, вот пример формулы СУММ, создание которой на самом деле занимает больше времени, чем следовало бы. Это распространенная ошибка новых пользователей Excel, которая может быстро стать плохой привычкой:

Добавить общий префикс ко всем ячейкам в Excel

спросил
12 лет, 4 месяца назад

Изменено
2 года, 6 месяцев назад

Просмотрено
518 тысяч раз

У меня есть столбец с текстом в каждой ячейке.
Я хочу добавить текст, например «X», в начало всех ячеек. Например:

 А Б
----- >>>> ----
1 х 1
2 х 2
3 х 3
 

Как проще всего это сделать?

Введите это в ячейку B1 и скопируйте вниз. ..

 ="X"&A1
 

Это также будет работать:

 =СЦЕПИТЬ("X",A1)
 

И вот один из многих способов сделать это в VBA (Отказ от ответственности: я не очень часто пишу код в VBA!):

 Sub AddX()
    Дим и пока
    С ActiveSheet
    Для i = 1 To .Range("A65536").End(xlUp).Row Шаг 1
        .Cells(i, 2).Value = "X" & Trim(Str(.Cells(i, 1).Value))
    Далее я
    Конец с
Конец сабвуфера
 

6

Выберите ячейку, которая должна быть такой,
Перейти к свойствам ячейки (или CTRL 1)
на вкладке «Число»
на заказ
войти
«Х»#

1

Выберите ячейку, которую вы хотите сделать такой, перейдите к свойствам ячейки (или CTRL 1) на вкладке «Число» в пользовательском поле, введите «X» @

Поместите пробел между «и @, если необходимо

1

Выберите нужную ячейку,

Перейдите к форматированию ячеек (или CTRL+1),

Выберите вкладку «Пользовательский», введите требуемый формат, например: «X» #

при необходимости используйте пробел.

, например, мне нужно было вставить слово «Часы» рядом с моими числами и использовать этот формат: # «часы»

1. Введите функцию = СЦЕПИТЬ («X», A1) в одной ячейке другой чем A сказать D
2. Щелкните ячейку D1 и перетащите маркер заполнения через диапазон, который вы хотите заполнить. Во все ячейки должен быть добавлен определенный текст префикса.

Вы можете увидеть изменения, внесенные в соответствующие ячейки.

Вариант 1:
выберите ячейку (ячейки) в разделе форматирование/число/пользовательское форматирование, введите

«BOB» General

теперь у вас есть префикс «BOB» рядом с числами, датами, логическими значениями, но не рядом с TEXTs

Option2:
Как и раньше, но используйте следующий формат

_ «BOB» @_

теперь у вас есть префикс BOB, это работает, даже если ячейка содержит текст

Cheers, Sudhi

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

Просто щелкните правой кнопкой мыши и выберите «Формат ячейки» в контекстном меню, выберите «Пользовательский», а затем укажите желаемый тип… для приведенного выше примера это будет X0. Здесь «X» — это префикс, а 0 — цифра после него.

Надеюсь, это поможет..

Ура…

Перейти к форматированию ячеек — Пользовательский. Сначала введите нужный формат в список. Чтобы поставить префикс «0» перед текстовыми символами в столбце Excel, используйте формат 0####. Помните, что используйте символ «#», равный максимальному количеству цифр в ячейке этого столбца. Например, если в столбце 4 ячейки с записями — 123, 333, 5665, 7 — используйте формулу 0####. Причина. Одиночный # относится к ссылке только на одну цифру.

Другой способ сделать это:

1. Поместите свой префикс в один столбец, скажем, столбец A в Excel
2. Поместите значения, к которым вы хотите добавить префикс, в другой столбец, скажем, столбец B в Excel
3. В столбце C используйте эту формулу;

«C1=A1&B1»

4. Скопируйте все значения в столбце C и снова вставьте их в тот же выбор, но только как значения.

Введите значение в одну ячейку (НАПРИМЕР:B4 CELL). Для временного использования этой формулы в другой ячейке (после ее удаления). =СЦЕП(XY,B4) . щелкните и перетащите до нужного значения. Скопируйте весь столбец и щелкните правой кнопкой мыши и вставьте только значения (второй вариант).

Я пробовал, работает как положено.

Твой ответ

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

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя адрес электронной почты и пароль

Опубликовать как гость

Электронная почта

Обязательно, но не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Добавление 1 к каждой ячейке в столбце чисел?

фото какарт

Новый член

• 13 октября 2013 г.

• #1

Я пытаюсь добавить 1 (+1) ко всему набору данных чисел, чтобы каждое число в ячейке увеличивалось на 1. У меня есть 3 столбца чисел.

Как мне сделать это для каждого столбца? Спасибо.

Как рассчитать платежи по кредиту в Excel?

Нажмите здесь, чтобы открыть ответ

Используйте функцию PMT: =PMT(5%/12,60,-25000) для кредита в размере 25 000 долларов США, 5% годовых, кредита на 60 месяцев.

бшварц

Известный член

• 13 октября 2013 г.

• #2

Попробуйте =A2+1

Заполните вниз и поперек.

Эндрю Поулсом

MrExcel MVP

• 13 октября 2013 г.

• #3

Добро пожаловать в MrExcel.

Поместите 1 в свободную ячейку и скопируйте ее. Выберите свои данные и выберите «Специальная вставка», установите флажок «Добавить» и нажмите «ОК».

фото какарт

Новый член

• 13 октября 2013 г.

• #4

Эндрю Поулсом сказал:

Добро пожаловать в MrExcel.

Поместите 1 в свободную ячейку и скопируйте ее. Выберите свои данные и выберите «Специальная вставка», установите флажок «Добавить» и нажмите «ОК».

Нажмите, чтобы развернуть…

Ага. это сработало. Я ставлю 1 в ячейку и =(ячейка)+1. Он добавил 1 к ячейке, которую я скопировал, выделил все ячейки в столбце и вставил. Это сработало.

Спасибо.

Вы должны войти или зарегистрироваться, чтобы ответить здесь.

Совет по коду для изменения числа 1 на 2 в определенном столбце и его формате

• ipbr21054
• 13 июля 2022 г.
• Вопросы Excel

2

Ответы

14

просмотров

135

14 июля 2022 г.

mohadin

увеличение числа на 1

• rharri1972
• 17 июля 2022 г.
• Вопросы Excel

Ответы

5

просмотров

207

17 июля 2022 г.

rharri1972

удалить каждую комбинацию без конкретных номеров

• excelNewbie22
• 21 июля 2022 г.
• Вопросы Excel

2

Ответы

14

просмотров

290

27 июля 2022 г.

excelNewbie22

Power Query — процент ответов Да, Нет, Н/Д на человека

• JugglerJAF
• 30 августа 2022 г.
• Вопросы Excel

Ответы

0

просмотров

47

30 августа 2022 г.

JugglerJAF

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

• f00dFights
• 21 августа 2022 г.
• Вопросы Excel

Ответы

1

просмотров

59

21 августа 2022 г.

паротит

Делиться:

Фейсбук

Твиттер

Реддит

Пинтерест

Тамблер

WhatsApp

Эл. адрес

Делиться

Ссылка на сайт

29 способов сэкономить время с помощью формул Excel

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

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

Видео: 20 советов по экономии времени с формулами Excel

1. Не добавляйте последние круглые скобки в функцию

Давайте начнем с чего-нибудь действительно простого! Когда вы вводите одну функцию саму по себе (СУММ, СРЗНАЧ и т. д.), вам не нужно вводить последние закрывающие скобки. Например, вы можете просто ввести:

=СУММ(A1:A10

и нажмите клавишу возврата. Excel добавит за вас закрывающие скобки. Это небольшая вещь, но удобная.

Примечание: это не будет работать, если ваша формула содержит более одного набора

2. Переместите формулу и предотвратите изменение ссылок

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

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

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

Если вы перемещаете формулу в более отдаленное место, используйте вырезание и вставку. (Win: Ctrl + X, Ctrl + V. Mac: Cmd + X, Cmd + V.) Когда вы вырезаете формулу, ее ссылки не меняются.

3. Скопируйте формулу и не допускайте изменения ссылок

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

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

Чтобы скопировать группу формул в новое место, не затрагивая ссылки, можно использовать функцию поиска и замены. Выберите формулы, которые нужно скопировать, затем найдите и замените знак равенства (=) в формулах символом решетки (#). Это преобразует формулы в текст. Теперь скопируйте и вставьте формулы в новое место. После того, как вы вставите формулы и выберете все формулы, отмените процесс поиска и замены. Найдите решетку (#) и замените знак равенства (=). Это восстановит формулы в рабочем состоянии. (Источник: Том Уртис)

4. Дважды щелкните маркер заполнения, чтобы скопировать формулы вниз

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

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

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

Если выбор не слишком велик, вы также можете заполнить формулами лист вниз, используя сочетание клавиш «Заполнить вниз» (Control + D). Просто сначала выберите исходную формулу и целевые ячейки. Это быстрее, чем копирование/вставка, но не так быстро, как дескриптор заполнения, особенно если вы копируете формулу в большую группу ячеек.

Видео: ярлыки для быстрого перемещения по большому списку

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

Еще более быстрый способ ввода формул — сначала преобразовать таблицу в официальную таблицу Excel. Терминология здесь сбивает с толку, поскольку любые данные с более чем одним столбцом технически являются «таблицей», но Excel имеет формальную структуру, называемую таблицей, которая дает много преимуществ. После преобразования данных в таблицу (на обеих платформах: Ctrl + T) все формулы, которые вы вводите в первой строке, будут автоматически скопированы по всей длине таблицы. Это экономит много времени, а также помогает предотвратить ошибки.

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

Примечание. Формулы в таблице будут автоматически использовать структурированные ссылки (например, в приведенном выше примере =[@Hours]*[@Rate] вместо =C4*D4. Однако вы можете ввести адреса ячеек напрямую, и они будут сохранены.

6. Используйте вкладку «Автозаполнение +» для ввода функций

Когда вы вводите знак равенства и начинаете печатать, Excel начнет сопоставлять введенный вами текст с огромным списком доступных функций. см. ниже список функций-кандидатов. Этот список будет сужаться с каждой введенной буквой. После того, как нужная функция выбрана в списке, вы можете «попросить» Excel ввести ее для вас, нажав клавишу Tab. В Windows, функции выбираются автоматически по мере ввода.На Mac параметры представлены, но не выбраны, поэтому вам нужно сделать еще один шаг: используйте клавишу со стрелкой, чтобы выбрать нужную функцию, затем нажмите клавишу Tab или Return, чтобы Excel ввел функцию для ты

7. Используйте Control + щелкните для ввода аргументов

Не любите печатать запятые между аргументами? Вы можете попросить Excel сделать это за вас. Когда вы вводите аргументы в функцию, просто удерживайте нажатой клавишу Control (Mac: Command), когда вы щелкаете каждую ссылку, и Excel автоматически вставит запятые для вас. Например, вы можете ввести такую ​​формулу, как: =СУММ(A1, B10, C5:C10), введя «=СУММ(», затем щелкнув каждую ссылку, удерживая нажатой клавишу Control. Это будет работать с любой функцией, в которой вы предоставляете ссылки в качестве аргументов.

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

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

Хотите узнать больше? Мы предлагаем полный курс по формулам и функциям Excel.

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

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

При вводе функции после того, как Excel распознает имя функции, введите Control + Shift + A (на обеих платформах). Например, если вы наберете «=ДАТА(» и затем нажмете Control + Shift + A, Excel выдаст вам «=ДАТА(год,месяц,день)». Затем вы можете дважды щелкнуть каждый аргумент (или использовать подсказку функции для выбора каждого аргумента) и измените его на нужное значение.0003

10. Уберите «формулу» с пути

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

11. Отображение сразу всех формул

Всякий раз, когда вы редактируете ячейку, содержащую формулу, Excel автоматически отображает формулу вместо ее результата. Но иногда вы можете увидеть все формулы на листе одновременно. Для этого достаточно использовать сочетание клавиш для вывода формул: Control + ~ (это тильда). С помощью этого ярлыка вы можете быстро отключить или отключить отображение всех формул на листе. Это хороший способ увидеть все формулы сразу и проверить формулы на непротиворечивость.

12.

Выбрать сразу все формулы на листе

Другой способ просмотреть все формулы на листе — выделить их. Вы можете сделать это, используя более мощные (и скрытые) функции в Excel: Перейти > Специальные (Ctrl + G). С помощью этой команды вы можете выбирать в Excel всевозможные интересные вещи, включая пустые ячейки, ячейки, содержащие числа, пустые ячейки и многое другое. Один из вариантов — ячейки, содержащие формулы. Чтобы выбрать все ячейки, содержащие формулы на листе, просто нажмите Ctrl + G, чтобы открыть диалоговое окно «Перейти», затем нажмите кнопку «Специальная», затем выберите «Формулы». Когда вы нажмете OK, будут выбраны все ячейки, содержащие формулы.

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

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

Распространенной проблемой в Excel является необходимость предотвратить изменение вычисляемых значений. Например, может быть, вы хотите упростить рабочий лист, удалив «вспомогательные» столбцы, которые вы использовали для создания определенных значений. Но если вы удалите эти столбцы с формулами, которые все еще ссылаются на них, вы получите множество ошибок #REF. Решение состоит в том, чтобы сначала преобразовать формулы в значения, а затем удалить лишние столбцы. Самый простой способ сделать это — использовать Специальную вставку. Сначала выберите формулы, которые вы хотите преобразовать, и скопируйте в буфер обмена. Затем, не снимая выделения с формул, откройте диалоговое окно «Специальная вставка» (Win: Ctrl + Alt + V, Mac: Ctrl + Cmd + V) и используйте параметр «Значения». Это заменит все выбранные вами формулы значениями, которые они вычислили.

14. Используйте Специальную вставку для корректировки значений на месте

Еще одна распространенная проблема в Excel — необходимость изменить множество значений на месте. Например, возможно, у вас есть список из 500 цен на товары, и вам нужно увеличить все цены на 5%. Или, может быть, у вас есть список из 100 дат, которые нужно перенести в будущее на одну неделю? В таких случаях вы можете добавить в таблицу «вспомогательный» столбец, выполнить необходимые вычисления, преобразовать результаты в значения, а затем скопировать их поверх исходного столбца. Но если вам нужен только простой расчет, Специальная вставка намного проще и быстрее, потому что вы можете изменить значение напрямую без каких-либо дополнительных формул.

Например, чтобы преобразовать набор дат на месте в одну неделю позже, сделайте следующее: добавьте число 7 в любую ячейку на листе, затем скопируйте его в буфер обмена. Затем выберите все даты, которые вы хотите изменить. Затем используйте Специальная вставка > Операции > Добавить. Когда вы нажмете «ОК», Excel добавит число 7 к выбранным вами датам, переместив их во времени на 7 дней вперед, без необходимости создавать вспомогательные столбцы.

Чтобы увеличить набор цен на 10%, используйте тот же подход. Введите 1.10 в ячейку и скопируйте ее в буфер обмена. Затем выберите цены, которые вы хотите изменить, и используйте Специальная вставка > Операции > Умножение, чтобы преобразовать все цены на месте. Как только вы освоите этот совет, вы найдете для него множество умных применений.

Примечание: это работает только со значениями. Не пытайтесь использовать формулы!

15. Используйте именованные диапазоны, чтобы сделать формулы более удобочитаемыми

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

=B3 * $A$1
=B4 * $A$1
=B5 * $A$1

Но если вы назовете ячейку A1 «hourly_rate», ваши формулы будут выглядеть так:

=B3 * hourly_rate
=B4 * hourly_rate
=B5 * hourly_rate

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

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

16. Автоматическое применение имен к существующим формулам

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

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

17. Сохранение невыполненной формулы

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

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

18. Помните о функциях, которые предлагает Excel

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

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

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

19. Используйте F4 для переключения относительных и абсолютных ссылок

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

Однако преобразование ссылок туда и обратно между относительными и абсолютными ссылками может доставлять неудобства — вводить все эти знаки доллара ($) утомительно и чревато ошибками. К счастью, есть отличный ярлык, позволяющий быстро переключаться между 4 вариантами, доступными для каждой ссылки: (Windows: F4, Mac: Command + T). Просто поместите курсор в ссылку, используя ярлык. Каждый раз, когда вы его используете, Excel будет «переключаться» на следующий параметр в следующем порядке: полностью относительный (A1) > полностью абсолютный ($A$1) > абсолютная строка (A$1) > абсолютный столбец ($A1).

20. Помните, что формулы и функции возвращают значение. Всегда.

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

Примечание. Слово «возврат» пришло из мира программирования, и оно иногда сбивает с толку людей, плохо знакомых с этим понятием. В контексте формул Excel это используется так: «Функция ДЛСТР возвращает число» или «Формула в ячейке A1 возвращает ошибку». Всякий раз, когда вы слышите слово «возврат» с формулой, просто подумайте о «результате».

21.

Используйте F9 для оценки частей формулы

Сочетание клавиш F9 (fn + F9 на Mac) позволяет решать части формулы в режиме реального времени. Это фантастический инструмент для отладки больших формул, когда вам нужно убедиться, что результат определенной части формулы соответствует вашим ожиданиям. Чтобы использовать этот совет, отредактируйте формулу и выберите выражение или функцию, которую вы хотите вычислить (совет — используйте окно подсказки функции, чтобы выбрать все аргументы). Если у вас есть выбор, нажмите F9. Вы увидите, что эта часть формулы заменена возвращаемым значением.

Примечание. В Windows можно отменить нажатие клавиши F9, но на Mac это не работает. Чтобы выйти из формулы без внесения изменений, просто нажмите Esc.

Видео: Как проверить и отладить формулу с помощью F9

22. Используйте средство оценки формул для пошагового решения формулы

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

Видео: Как оценивать сложные формулы

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

23. Создавайте сложные формулы небольшими шагами

Если вам нужно построить более сложную формулу и вы не знаете, как это сделать, начните с общего подхода и некоторых жестко закодированных значений. Затем добавьте больше логики, чтобы заменять жестко запрограммированные значения шаг за шагом. Например, допустим, вы хотите написать формулу, извлекающую имя из полного имени. Вы знаете, что можете использовать функцию LEFT для извлечения текста слева, но не знаете, как рассчитать количество символов для извлечения. Начните с   LEFT(full_name, 5), чтобы формула заработала. Затем выясните, как заменить число 5 вычисленным значением. В этом случае вы можете определить количество символов для извлечения, используя функцию НАЙТИ, чтобы найти позицию первого символа пробела.

Видео: Пошаговое построение сложной формулы

24. Используйте именованные диапазоны как переменные

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

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

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

Например, обычно у вас может быть этикетка с надписью «Стоимость кофе», за которой следует расчетная стоимость. С помощью конкатенации вы можете сделать так, чтобы на этикетке было написано «Стоимость кофе (2 доллара США за чашку): это дает понять, что вы приняли стоимость кофе в размере 2 долларов США за чашку. ЕСЛИ, чтобы их было легче читать

Когда вы создаете вложенную формулу ЕСЛИ, отслеживание истинных и ложных аргументов в вихре скобок может привести к путанице. Однако есть простой способ сделать формулу с несколькими операторами ЕСЛИ более удобной для чтения: просто добавьте в формулу разрывы строк после каждого аргумента ИСТИНА. Это сделает формулу более похожей на таблицу.0003

Видео: как упростить чтение вложенных ЕСЛИ

27. Ввод функций с помощью автозаполнения

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

28. Используйте автосумму для ввода формул СУММА

У вас не всегда есть возможность использовать эту автосумму, но когда вы это делаете, это приносит удовлетворение. Автосумма работает как для строк, так и для столбцов. Просто выберите пустую ячейку справа или ниже ячеек, которые вы хотите суммировать, и введите Alt + = (Mac: Command + Shift + T). Excel угадает диапазон, который вы пытаетесь суммировать, и вставит функцию СУММ за один шаг. Если вы хотите быть более конкретным, чтобы Excel не догадался, сначала выберите диапазон, который вы собираетесь суммировать, включая ячейку, в которой вы хотите, чтобы функция СУММ находилась.

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

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

29. Введите одну и ту же формулу сразу в несколько ячеек

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

Этот же прием можно использовать для одновременного редактирования нескольких формул. Просто выберите все формулы сразу, внесите необходимые изменения и нажмите Control + Enter.

Как быстро объединить ячейки в Excel

Смотреть видео – Объединение ячеек в Excel (используя формулы)

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

Ваши данные могут находиться в соседних ячейках (строках/столбцах) или далеко друг от друга на том же рабочем листе или даже на другом рабочем листе.

В этом руководстве вы узнаете, как объединять ячейки в Excel в различных сценариях:

• Как объединять ячейки без пробела/разделителя между ними.
• Как объединить ячейки с пробелом/разделителем между ними.
• Как объединить ячейки с разрывами строк между ними.
• Как объединить ячейки с текстом и числами.

Как объединить ячейки без пробела/разделителя

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

Вы можете легко объединить ячейки в столбцах A и B, чтобы получить такую ​​строку, как A11, A12 и т. д.

Вот как это можно сделать:

• Введите следующую формулу в ячейку, где вы хотите получить объединенную строку:

   =A2&B2 

• Скопируйте и вставьте ее во все ячейки.

Это даст вам что-то, как показано ниже:

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

 =СЦЕПИТЬ(A2,B2) 

Как объединить ячейки с пробелом/разделителем между ними

Вы также можете объединять ячейки и иметь указанный разделитель между ними. Это может быть пробел, запятая или любой другой разделитель.

Предположим, у нас есть набор данных, как показано ниже:

Вот шаги для объединения имени и фамилии с пробелом между ними:

• Введите в ячейку следующую формулу:

   =A2&" "&B2 

• Скопируйте и вставьте ее во все ячейки.

В этом случае имя и фамилия будут объединены с пробелом между ними.

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

Как объединить ячейки с разрывами строк между

Помимо разделителей, вы также можете добавлять разрывы строк при объединении ячеек в Excel.

Предположим, у вас есть набор данных, как показано ниже:

В приведенном выше примере разные части адреса находятся в разных ячейках (имя, номер дома, улица, город и страна).

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

Однако, просто объединив эти ячейки, вы получите нечто, как показано ниже:

Это неправильный формат адреса. Вы можете попробовать использовать обтекание текстом, но это тоже не сработает.

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

CHAR(10) — это перевод строки в Windows, что означает, что он заставляет все после него переходить на новую строку.

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

 =A2&CHAR(10)&B2&CHAR(10)&C2&CHAR(10)&D2&CHAR(10)&E2 

Эта формула использует функцию CHAR(10) между каждой ссылкой на ячейку и вставляет разрыв строки после каждой ячейки. Получив результат, примените обтекание текстом к ячейкам с результатами, и вы получите нечто, как показано ниже:

Как объединить ячейки с текстом и числами

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

Сначала рассмотрим простой пример.

Предположим, у вас есть набор данных, как показано ниже:

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

 =A2&"-"&B2 

Здесь я использовал тире в качестве разделителя.

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

 =A2&" регион имеет "&B2&" офисы" 

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

Теперь давайте рассмотрим этот пример и посмотрим, что произойдет, когда вы попытаетесь использовать числа с некоторым форматированием.

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

Теперь давайте объединим ячейки в столбцах A и B, чтобы составить предложение.

Вот формула, которую я буду использовать:

 =A2&" объем продаж в регионе "&B2 

Вот как выглядят результаты:

Вы видите здесь проблему? Посмотрите внимательно на формат значения продаж в результате.

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

Вот как это исправить. Используйте приведенную ниже формулу:

 =A2&" регион сгенерировал продажи "&ТЕКСТ(B2,"$ ###,0.0") 

В приведенной выше формуле вместо использования B2 мы использовали функцию ТЕКСТ. Функция ТЕКСТ заставляет число отображаться в указанном формате и в виде текста. В приведенном выше случае формат равен 9.0384 $ ###,0.0. Этот формат указывает Excel отображать число со знаком доллара, разделителем тысяч и одной десятичной точкой.

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

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

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