Как включить формулы в Excel
Если вы читали предыдущие статьи по работе с таблицами Excel, то должны быть в курсе, что запись формулы начинается со знака «Равно». Когда возникает необходимость написать в ячейке этот знак без формулы, программа настойчиво продолжает считать такую запись началом формулы. При клике в другую ячейку после знака прописывается адрес ячейки. В этом случае есть несколько способов перехитрить Excel.
Решение: Перед написанием знака равно, плюс (сложение), минус (вычитание), наклонная черта (деление) или звездочки(умножение) поставить пробел или апостроф.
Использование пользовательского числового формата
Вы можете создать собственный числовой формат для ячеек с положительными значениями. Само значение так и останется в числовом формате и вы сможете производить вычисления с этими ячейками. Это просто изменит форматирование и вывод числа на экран.
Предположим, у вас есть набор данных, как показано ниже, и вы хотите добавить знак плюс перед всеми положительными числами:
Пошаговая инструкция как это сделать:
- Выберите ячейки с числами, в которых необходимо поставить знак плюс.
- Щелкните правой кнопкой мыши, а затем выберите Формат ячеек
- В диалоговом окне «Формат ячеек» на вкладке «Число» выберите категорию «Все форматы».
- В поле Тип введите следующее значение: +# ##0;-# ##0
- Нажмите на ОК.
Вы заметите, что знак плюс был добавлен к положительным числам, а все остальное остается прежним.
Обратите внимание, что это фактическое значение ячейки не изменилось. Например, значение в ячейке D2 по-прежнему равно 5100. Оно просто отображается в виде числа со знаком плюс перед ним.
Также, как и любое другое форматирование, вы можете скопировать и вставить этот формат в другие ячейки. Просто копируйте нужные ячейки, формат также будет скопирован. Или воспользуйтесь функцией формат по образцу.
Другие полезные пользовательские форматы
Положительные числа со знаком плюс и отрицательными числами в скобках: +# ##0;(# ##0)
Положительные числа со знаком плюс и отрицательными числами красного цвета: +0; [Красный] -0
Положительные числа в зеленом и отрицательные числа в красном: [Зеленый] +0; [Красный] -0
Символ после каждого слова в ячейке — формула
Чтобы вставить символ после каждого слова в ячейке (например, запятую или точку с запятой как общепринятые символы-разделители), можно использовать аналогичную формулу в легкой модификации.
Здесь в ячейке D1 указанный символ. Вот как формула работает на примере. Обратите внимание, поскольку в нем адресация на ячейку D1 не абсолютная, символ протянут вниз вместе с формулой, которая на него ссылается.
Вставляем символ после каждого слова в ячейках с помощью формулы.
Как в формуле Excel обозначить постоянную ячейку
Различают два вида ссылок на ячейки: относительные и абсолютные. При копировании формулы эти ссылки ведут себя по-разному: относительные изменяются, абсолютные остаются постоянными.
Все ссылки на ячейки программа считает относительными, если пользователем не задано другое условие. С помощью относительных ссылок можно размножить одну и ту же формулу на несколько строк или столбцов.
- Вручную заполним первые графы учебной таблицы. У нас – такой вариант:
- Вспомним из математики: чтобы найти стоимость нескольких единиц товара, нужно цену за 1 единицу умножить на количество. Для вычисления стоимости введем формулу в ячейку D2: = цена за единицу * количество. Константы формулы – ссылки на ячейки с соответствующими значениями.
- Нажимаем ВВОД – программа отображает значение умножения. Те же манипуляции необходимо произвести для всех ячеек. Как в Excel задать формулу для столбца: копируем формулу из первой ячейки в другие строки. Относительные ссылки – в помощь.
Находим в правом нижнем углу первой ячейки столбца маркер автозаполнения. Нажимаем на эту точку левой кнопкой мыши, держим ее и «тащим» вниз по столбцу.
Отпускаем кнопку мыши – формула скопируется в выбранные ячейки с относительными ссылками. То есть в каждой ячейке будет своя формула со своими аргументами.
Ссылки в ячейке соотнесены со строкой.
Формула с абсолютной ссылкой ссылается на одну и ту же ячейку. То есть при автозаполнении или копировании константа остается неизменной (или постоянной).
Чтобы указать Excel на абсолютную ссылку, пользователю необходимо поставить знак доллара ($). Проще всего это сделать с помощью клавиши F4.
- Создадим строку «Итого». Найдем общую стоимость всех товаров. Выделяем числовые значения столбца «Стоимость» плюс еще одну ячейку. Это диапазон D2:D9
- Воспользуемся функцией автозаполнения. Кнопка находится на вкладке «Главная» в группе инструментов «Редактирование».
- После нажатия на значок «Сумма» (или комбинации клавиш ALT+«=») слаживаются выделенные числа и отображается результат в пустой ячейке.
Сделаем еще один столбец, где рассчитаем долю каждого товара в общей стоимости. Для этого нужно:
- Разделить стоимость одного товара на стоимость всех товаров и результат умножить на 100. Ссылка на ячейку со значением общей стоимости должна быть абсолютной, чтобы при копировании она оставалась неизменной.
- Чтобы получить проценты в Excel, не обязательно умножать частное на 100. Выделяем ячейку с результатом и нажимаем «Процентный формат». Или нажимаем комбинацию горячих клавиш: CTRL+SHIFT+5
- Копируем формулу на весь столбец: меняется только первое значение в формуле (относительная ссылка). Второе (абсолютная ссылка) остается прежним. Проверим правильность вычислений – найдем итог. 100%. Все правильно.
При создании формул используются следующие форматы абсолютных ссылок:
- $В$2 – при копировании остаются постоянными столбец и строка;
- B$2 – при копировании неизменна строка;
- $B2 – столбец не изменяется.
4. Формула СЦЕПИТЬ(А1;“ ”;А2 ;“ ”;А3;…) — Объединяет 2 и более ячеек в одну с добавлением пробела между ними
Разберем формулу подробней:
=СЦЕПИТЬ(А1;» «;А2 ;» «;А3;…), где А1, А2, А3 – ячейки к объединению,
“ ” – пробелы, которые проставятся между содержимым ячеек.
Если сочетание символов “ ” не применить, ячейки «склеятся» без пробела.
Вставка тире в ячейку
Итак, как мы знаем, в программе Эксель предусмотрено использование как длинного, так и короткого тире. Второй вариант иногда обозначают как «среднее тире», что, по большому счету, верно, поскольку самым коротким является знак дефиса “-“.
Зачастую пользователи пробуют вставить длинное тире, используя клавишу “-“, однако в результате у них все равно получается знак минуса, если речь идет про написание формулы, либо дефис. Как быть в этой ситуации?
Есть всего два способа, позволяющих вставить тире в ячейку таблицы. В первом случае результат достигается путем набора комбинации клавиш, а во втором используется окно спецсимволов. Давайте рассмотрим оба метода.
Конкатенация
Формула: =(ячейка1&» «&ячейка2)
За этим причудливым словом скрывается объединение данных из двух и более ячеек в одной. Сделать объединение можно с помощью формулы конкатенации или просто вставив символ & между адресами двух ячеек. Если в ячейке A1 находится имя «Иван», в ячейке B1 – фамилия «Петров», их можно объединить с помощью формулы =A1&» «&B1. Результат – «Иван Петров» в ячейке, где была введена формула. Обязательно оставьте пробел между » «, чтобы между объединёнными данными появился пробел.
Формула конкатенации даёт аналогичный эффект и выглядит так: =ОБЪЕДИНИТЬ(A1;» «; B1) или в англоязычном варианте =concatenate(A1;» «; B1).
Кстати, все перечисленные формулы можно применять и в Google‑таблицах.
Эта статья является лишь верхушкой айсберга в изучении Excel. Для профессионального использования программы рекомендуем учится у профессионалов на курсах по Microsoft Excel.