Excel как сделать из формулы число

Замена формулы на ее результат

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

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

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

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

Замена формул с помощью вычисляемых значений

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

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

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

Выбор диапазона, содержащего формулу массива

Щелкните ячейку в формуле массива.

На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить, а затем выберите команду Перейти.

Нажмите кнопку Дополнительный.

Нажмите кнопку Текущий массив.

Нажмите кнопку ” копировать “.

Нажмите кнопку вставить .

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

В следующем примере показана формула в ячейке D2, которая умножает ячейки a2, B2 и скидку, полученную из C2, для расчета суммы счета для продажи. Чтобы скопировать фактическое значение вместо формулы из ячейки на другой лист или в другую книгу, можно преобразовать формулу в ее ячейку в ее значение, выполнив указанные ниже действия.

Нажмите клавишу F2, чтобы изменить значение в ячейке.

Нажмите клавишу F9 и нажмите клавишу ВВОД.

После преобразования ячейки из формулы в значение оно будет отображено как 1932,322 в строке формул. Обратите внимание, что 1932,322 является фактическим вычисленным значением, а 1932,32 — значением, которое отображается в ячейке в денежном формате.

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

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

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

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

Щелкните ячейку, содержащую формулу.

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

Чтобы вычислить выделенный фрагмент, нажмите клавишу F9.

Чтобы заменить выделенный фрагмент формулы на вычисленное значение, нажмите клавишу ВВОД.

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

Источник: support.office.com

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

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

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

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

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

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

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


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


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

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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник: www.planetaexcel.ru

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

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

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

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

Чтобы задать формулу для ячейки, необходимо активизировать ее (поставить курсор) и ввести равно (=). Так же можно вводить знак равенства в строку формул. После введения формулы нажать Enter. В ячейке появится результат вычислений.

В Excel применяются стандартные математические операторы:

Оператор Операция Пример
+ (плюс) Сложение =В4+7
– (минус) Вычитание =А9-100
* (звездочка) Умножение =А3*2
/ (наклонная черта) Деление =А7/А8
^ (циркумфлекс) Степень =6^2
= (знак равенства) Равно
Больше
= Больше или равно
<> Не равно

Символ «*» используется обязательно при умножении. Опускать его, как принято во время письменных арифметических вычислений, недопустимо. То есть запись (2+3)5 Excel не поймет.

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

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

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

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

Оператор умножил значение ячейки В2 на 0,5. Чтобы ввести в формулу ссылку на ячейку, достаточно щелкнуть по этой ячейке.

В нашем примере:

  1. Поставили курсор в ячейку В3 и ввели =.
  2. Щелкнули по ячейке В2 – Excel «обозначил» ее (имя ячейки появилось в формуле, вокруг ячейки образовался «мелькающий» прямоугольник).
  3. Ввели знак *, значение 0,5 с клавиатуры и нажали ВВОД.

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

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

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

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

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

  1. Вручную заполним первые графы учебной таблицы. У нас – такой вариант:
  2. Вспомним из математики: чтобы найти стоимость нескольких единиц товара, нужно цену за 1 единицу умножить на количество. Для вычисления стоимости введем формулу в ячейку D2: = цена за единицу * количество. Константы формулы – ссылки на ячейки с соответствующими значениями.
  3. Нажимаем ВВОД – программа отображает значение умножения. Те же манипуляции необходимо произвести для всех ячеек. Как в Excel задать формулу для столбца: копируем формулу из первой ячейки в другие строки. Относительные ссылки – в помощь.

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

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

Ссылки в ячейке соотнесены со строкой.

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

Чтобы указать Excel на абсолютную ссылку, пользователю необходимо поставить знак доллара ($). Проще всего это сделать с помощью клавиши F4.

  1. Создадим строку «Итого». Найдем общую стоимость всех товаров. Выделяем числовые значения столбца «Стоимость» плюс еще одну ячейку. Это диапазон D2:D9
  2. Воспользуемся функцией автозаполнения. Кнопка находится на вкладке «Главная» в группе инструментов «Редактирование».
  3. После нажатия на значок «Сумма» (или комбинации клавиш ALT+«=») слаживаются выделенные числа и отображается результат в пустой ячейке.

Сделаем еще один столбец, где рассчитаем долю каждого товара в общей стоимости. Для этого нужно:

  1. Разделить стоимость одного товара на стоимость всех товаров и результат умножить на 100. Ссылка на ячейку со значением общей стоимости должна быть абсолютной, чтобы при копировании она оставалась неизменной.
  2. Чтобы получить проценты в Excel, не обязательно умножать частное на 100. Выделяем ячейку с результатом и нажимаем «Процентный формат». Или нажимаем комбинацию горячих клавиш: CTRL+SHIFT+5
  3. Копируем формулу на весь столбец: меняется только первое значение в формуле (относительная ссылка). Второе (абсолютная ссылка) остается прежним. Проверим правильность вычислений – найдем итог. 100%. Все правильно.

При создании формул используются следующие форматы абсолютных ссылок:

  • $В$2 – при копировании остаются постоянными столбец и строка;
  • B$2 – при копировании неизменна строка;
  • $B2 – столбец не изменяется.

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

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

Простейшие формулы заполнения таблиц в Excel:

  1. Перед наименованиями товаров вставим еще один столбец. Выделяем любую ячейку в первой графе, щелкаем правой кнопкой мыши. Нажимаем «Вставить». Или жмем сначала комбинацию клавиш: CTRL+ПРОБЕЛ, чтобы выделить весь столбец листа. А потом комбинация: CTRL+SHIFT+”=”, чтобы вставить столбец.
  2. Назовем новую графу «№ п/п». Вводим в первую ячейку «1», во вторую – «2». Выделяем первые две ячейки – «цепляем» левой кнопкой мыши маркер автозаполнения – тянем вниз.
  3. По такому же принципу можно заполнить, например, даты. Если промежутки между ними одинаковые – день, месяц, год. Введем в первую ячейку «окт.15», во вторую – «ноя.15». Выделим первые две ячейки и «протянем» за маркер вниз.
  4. Найдем среднюю цену товаров. Выделяем столбец с ценами + еще одну ячейку. Открываем меню кнопки «Сумма» – выбираем формулу для автоматического расчета среднего значения.

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

Источник: exceltable.com

Преобразование в EXCEL ЧИСЕЛ из ТЕКСТового формата в ЧИСЛОвой (Часть 1. Преобразование формулами)

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

Пусть из ячейки A1 содержащей «101 далматинец» с помощью формулы =ЛЕВСИМВ(A1;3) извлекли число 101. Число 101 будет сохранено в текстовом формате, т.к. эта функция возвращает только текстовые строки. Это дальнейшем может привести к ошибке. Например, при сложении функцией СУММ() такие значения попросту игнорируются (см. статью Функция СУММ() и операция сложения ), так же как и при подсчете функцией СЧЁТ() , СУММЕСЛИ() и пр.

Исправить формат возвращаемого значения можно добавив функцию ЗНАЧЕН() : =ЗНАЧЕН(ЛЕВСИМВ(A1;3)) . Функция ЗНАЧЕН() преобразует значение, где это возможно, в число.

Альтернативным решением является использование формулы =ЛЕВСИМВ(A1;3)+0 или =–ЛЕВСИМВ(A1;3) или =ЛЕВСИМВ(A1;3)*1 . Два подряд знака минус дают + и заставляют EXCEL попытаться сделать вычисления с результатом возвращенным функцией ЛЕВСИМВ() . В случае успеха, если функцией ЛЕВСИМВ() действительно было возвращено число, у значения будет изменен формат на числовой.

Этот вариант позволяет преобразовывать не только в числовой формат, но и формат даты. Введем в ячейку А1 текст « 1.05.2002 продажа », в ячейку B1 формулу =ЛЕВСИМВ(A1;9) , а в ячейку C1 формулу =B1+0 . В итоге, в B1 получим 1.05.2002 в текстовом формате, а в С1 получим уже обычную дату « 01.05.02 », точнее число 37377 (чтобы увидеть дату, формат ячейки нужно установить Дата ).

Некоторые программы бухгалтерского учета отображают отрицательные значения со знаком минус (-) справа от значения. После копирования в EXCEL они сохраняются как текстовые значения. Чтобы преобразовать эти текстовые значения в числа, необходимо извлечь из него все цифры, и умножить результат на -1. Например, если в ячейке A2 содержится строка «4116-» , следующая формула преобразует этот текст в значение -4116 .

СОВЕТ: Неправильный формат значения – это частый тип ошибки, к тому же, который достаточно трудно найти. Подсказкой может служить выравнивание значения в ячейке: если значение выровнено по правой стороне, то это число (или дата), а если по левой, то текст (подразумевается, что в Формате ячеек во вкладке Выравнивание в поле Выравнивание по горизонтали указано По значению ).

Примечание . При разделении содержимого ячеек (например, «101 далматинец») по различным столбцам с помощью инструмента Текст-по-столбцам (на вкладке Данные в группе Работа с данными пункт Текст-по-столбцам ) проблем с определением формата не возникает: если значение может быть преобразовано в числовой формат, то оно будет преобразовано.

Источник: excel2.ru

Вычитание чисел в программе Excel

Среди всех арифметических действий можно выделить четыре основных: сложение, умножение, деление и вычитание. О последнем и пойдет в речь в данной статье. Давайте рассмотрим, какими методами можно выполнять данное действие в Excel.

Процедура вычитания

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

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

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

Пример 1: разность конкретных чисел

Допустим, нам нужно найти разность между конкретными числами: 396 и 264. Выполнить вычитание можно с помощью простой формулы:

  1. Переходим в свободную ячейку таблицы, в которой планируем произвести необходимые расчеты. Печатаем в ней знак “=”, после чего пишем выражение: =365-264 .
  2. После того, как формула набрана, нажимаем клавишу Enter и получаем требуемый результат.

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

Пример 2: вычитание числа из ячейки

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

  1. Как и в первом методе, сначала выбираем свободную ячейку, куда хотим вывести результат вычисления. В ней:
    • пишем знак “=”.
    • указываем адрес ячейки, в которой находится уменьшаемое. Сделать это можно вручную, прописав координаты с помощью клавиш на клавиатуре. Либо можно выбрать нужную ячейку, кликну по ней левой кнопкой мыши.
    • добавляем в формулу знак вычитания (“-“).
    • пишем вычитаемое (если вычитаемых несколько, добавляем их через символ “-“).
  2. После нажатия клавиши Enter, получаем результат в выбранной ячейке.

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

Пример 3: разность между числами в ячейках

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

  1. Встаем в результирующую ячейку, после чего:
    • ставим символ “=”.
    • аналогично примеру 2, указываем ячейку, содержащую уменьшаемое.
    • таким же образом добавляем в формулу ячейку с вычитаемым, не забыв перед ее адресом добавить знак “минус”.
    • если вычитаемых несколько, добавляем их подряд со знаком “-“ впереди.
  2. Нажав клавишу Enter, мы увидим результат в ячейке с формулой.

Пример 4: Вычитание одного столбца из другого

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

Программа предоставляет пользователю такую возможность, и вот как ее можно реализовать:

  1. Переходим в первую ячейку столбца, в котором планируем производить расчеты. Пишем формулу вычитания, указав адреса ячеек, которые содержат уменьшаемое и вычитаемое. В нашем случае выражение выглядит следующим образом: =С2-B2 .
  2. Жмем клавишу Enter и получаем разность чисел.
  3. Остается только автоматически выполнить вычитание для оставшихся ячеек столбца с результатами. Для этого наводим указатель мыши на правый нижний угол ячейки с формулой, и после того, как появится маркер заполнения в виде черного плюсика, зажав левую кнопку мыши тянем его до конца столбца.
  4. Как только мы отпустим кнопку мыши, ячейки столбца заполнятся результатами вычитания.

Пример 5: Вычитание конкретного числа из столбца

В некоторых случаях требуется вычесть из всех ячеек столбца одно и то же конкретное число.

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

  1. Пишем в самой верхней ячейке результирующего столбца формулу вычитания. В нашем случае она выглядит так: =A2-65 .
  2. После нажатия Enter разность отобразится в выбранной ячейке.
  3. С помощью маркера заполнения протягиваем формулу на другие ячейки столбца, чтобы получить в них аналогичные результаты.

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

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

Алгоритм действий в данном случае следующий:

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

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

Заключение

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

Источник: microexcel.ru

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

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

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

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

Чтобы задать формулу для ячейки, необходимо активизировать ее (поставить курсор) и ввести равно (=). Так же можно вводить знак равенства в строку формул. После введения формулы нажать Enter. В ячейке появится результат вычислений.

В Excel применяются стандартные математические операторы:

Оператор Операция Пример
+ (плюс) Сложение =В4+7
– (минус) Вычитание =А9-100
* (звездочка) Умножение =А3*2
/ (наклонная черта) Деление =А7/А8
^ (циркумфлекс) Степень =6^2
= (знак равенства) Равно
Больше
= Больше или равно
<> Не равно

Символ «*» используется обязательно при умножении. Опускать его, как принято во время письменных арифметических вычислений, недопустимо. То есть запись (2+3)5 Excel не поймет.

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

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

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

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

Оператор умножил значение ячейки В2 на 0,5. Чтобы ввести в формулу ссылку на ячейку, достаточно щелкнуть по этой ячейке.

В нашем примере:

  1. Поставили курсор в ячейку В3 и ввели =.
  2. Щелкнули по ячейке В2 – Excel «обозначил» ее (имя ячейки появилось в формуле, вокруг ячейки образовался «мелькающий» прямоугольник).
  3. Ввели знак *, значение 0,5 с клавиатуры и нажали ВВОД.

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

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

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

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

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

  1. Вручную заполним первые графы учебной таблицы. У нас – такой вариант:
  2. Вспомним из математики: чтобы найти стоимость нескольких единиц товара, нужно цену за 1 единицу умножить на количество. Для вычисления стоимости введем формулу в ячейку D2: = цена за единицу * количество. Константы формулы – ссылки на ячейки с соответствующими значениями.
  3. Нажимаем ВВОД – программа отображает значение умножения. Те же манипуляции необходимо произвести для всех ячеек. Как в Excel задать формулу для столбца: копируем формулу из первой ячейки в другие строки. Относительные ссылки – в помощь.

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

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

Ссылки в ячейке соотнесены со строкой.

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

Чтобы указать Excel на абсолютную ссылку, пользователю необходимо поставить знак доллара ($). Проще всего это сделать с помощью клавиши F4.

  1. Создадим строку «Итого». Найдем общую стоимость всех товаров. Выделяем числовые значения столбца «Стоимость» плюс еще одну ячейку. Это диапазон D2:D9
  2. Воспользуемся функцией автозаполнения. Кнопка находится на вкладке «Главная» в группе инструментов «Редактирование».
  3. После нажатия на значок «Сумма» (или комбинации клавиш ALT+«=») слаживаются выделенные числа и отображается результат в пустой ячейке.

Сделаем еще один столбец, где рассчитаем долю каждого товара в общей стоимости. Для этого нужно:

  1. Разделить стоимость одного товара на стоимость всех товаров и результат умножить на 100. Ссылка на ячейку со значением общей стоимости должна быть абсолютной, чтобы при копировании она оставалась неизменной.
  2. Чтобы получить проценты в Excel, не обязательно умножать частное на 100. Выделяем ячейку с результатом и нажимаем «Процентный формат». Или нажимаем комбинацию горячих клавиш: CTRL+SHIFT+5
  3. Копируем формулу на весь столбец: меняется только первое значение в формуле (относительная ссылка). Второе (абсолютная ссылка) остается прежним. Проверим правильность вычислений – найдем итог. 100%. Все правильно.

При создании формул используются следующие форматы абсолютных ссылок:

  • $В$2 – при копировании остаются постоянными столбец и строка;
  • B$2 – при копировании неизменна строка;
  • $B2 – столбец не изменяется.

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

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

Простейшие формулы заполнения таблиц в Excel:

  1. Перед наименованиями товаров вставим еще один столбец. Выделяем любую ячейку в первой графе, щелкаем правой кнопкой мыши. Нажимаем «Вставить». Или жмем сначала комбинацию клавиш: CTRL+ПРОБЕЛ, чтобы выделить весь столбец листа. А потом комбинация: CTRL+SHIFT+”=”, чтобы вставить столбец.
  2. Назовем новую графу «№ п/п». Вводим в первую ячейку «1», во вторую – «2». Выделяем первые две ячейки – «цепляем» левой кнопкой мыши маркер автозаполнения – тянем вниз.
  3. По такому же принципу можно заполнить, например, даты. Если промежутки между ними одинаковые – день, месяц, год. Введем в первую ячейку «окт.15», во вторую – «ноя.15». Выделим первые две ячейки и «протянем» за маркер вниз.
  4. Найдем среднюю цену товаров. Выделяем столбец с ценами + еще одну ячейку. Открываем меню кнопки «Сумма» – выбираем формулу для автоматического расчета среднего значения.

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

Источник: exceltable.com