Darbe.ru

Быт техника Дарби
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Формулы в Excel; создание простых формул

Формулы в Excel — создание простых формул

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

Простые формулы

Формула – это равенство, которое выполняет вычисления. Как калькулятор, Excel может вычислять формулы, содержащие сложение, вычитание, умножение и деление.

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

Создание простых формул

Excel использует стандартные операторы для уравнений, такие как знак плюс для сложения (+), знак минус для вычитания (-), звездочка для умножения (*), a косая черта для деления (/), и знак вставки (^) для возведения в степень. Ключевым моментом, который следует помнить при создании формул в Excel, является то, что все формулы должны начинаться со знака равенства (=). Так происходит потому, что ячейка содержит или равна формуле и ее значению.

Формулы в Excel - cоздание простых формул

Чтобы создать простую формулу в Excel:
  1. Выделите ячейку, где должно появиться значение формулы (B4, например).
  2. Введите знак равно (=).
  3. Введите формулу, которую должен вычислить Excel. Например, «120х900».
    Ввод формулы в ячейку
  4. Нажмите Enter. Формула будет вычислена и результат отобразится в ячейке.
    Результат формулы

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

Создание формул со ссылками на ячейки

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

Чтобы создать формулу со ссылками на ячейки:
  1. Выделите ячейку, где должно появиться значение формулы (B3, например).
    Выделение ячейки B3
  2. Введите знак равно (=).
  3. Введите адрес ячейки, которая содержит первое число уравнения (B1, например).
    Ввод формулы в ячейку B3
  4. Введите нужный оператор. Например, знак плюс (+).
  5. Введите адрес ячейки, которая содержит второе число уравнения (в моей таблице это B2).
    Ввод формулы в ячейку B3
  6. Нажмите Enter. Формула будет вычислена и результат отобразится в ячейке.
    Результат в ячейке B3

Если вы измените значение в ячейке B1 или B2, итог будет автоматически пересчитан.

Более легкий и быстрый способ создания формул «Наведи и кликни»
  1. Выделите ячейку, где должно появиться значение (B3, например).
    Выделение ячейки B3
  2. Введите знак равно (=).
  3. Кликните по первой ячейке, которую нужно включить в формулу (B1, например).
    Клик по ячейке B1
  4. Введите нужный оператор. Например, знак деления (*).
  5. Кликните по следующей ячейке в формуле (B2, например).
    Клик по ячейке B2
  6. Нажмите Enter. Формула будет вычислена и результат отобразится в ячейке.
    Результат в ячейке B3
Читайте так же:
Добавление ячеек в excel
Чтобы изменить формулу:

Изменение формулы

  1. Кликните по ячейке, которую нужно изменить.
  2. Поместите курсор мыши в строку формул и отредактируйте формулу. Также вы можете просматривать и редактировать формулу прямо в ячейке, дважды щелкнув по ней мышью.
  3. Когда закончите, нажмите Enter на клавиатуре или нажмите на команду Ввод в строке формул.

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

Расчеты в Excel

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

Как сделать расчеты в Excel

Произвести расчеты в Excel легко. Вот как:

  • Введите символ равенства (=) в ячейку. Это говорит Excel, что вы вводите формулу, а не только цифры.
  • Введите уравнение, которое вы хотите посчитать. Например, чтобы посчитать сумму 5 и 7, введите =5+7
  • Нажмите клавишу Enter, чтобы завершить расчет. Готово!

Вместо того, чтобы вводить цифры непосредственно в формулу расчета, вы можете поместить их в отдельные ячейки, а затем сослаться на эти ячейки в своей формуле, например, =A1+A2+A3

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

Суммирует числа в ячейках A1иA2

Вычитает число A2 из числа в A1.

Умножает числа из A1 и A2.

Делит число A1 на число A2.

Находит 10% от числа A1.

Возведение в степень

Возвращает число А2 в 3 степени.

Находит квадратный корень из числа A1.

^(1/n), где n – корень, который нужно найти

Находит кубический корень из числа А1.

Результаты приведенных выше формул расчета в Excel могут выглядеть примерно так:

1-raschety-v-excel

Расчеты в Excel – Формулы расчетов в Excel

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

Символ пробела (» «) используется для разделения слов:

2-raschety-v-excel

Расчеты в Excel – Объединение значений из нескольких ячеек

Вы также можете сравнивать ячейки с помощью логических операторов, таких как «больше» (>), «меньше» (<), «больше или равно» (> =) и «меньше или равно» (<= ). Результатом сравнения являются логические значения ИСТИНА и ЛОЖЬ:

3-raschety-v-excel

Расчеты в Excel – Сравнение ячеек с использованием логических операторов

Порядок выполнения расчетов в Excel

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

Отрицание, т.е. изменение знака числа, например, -5, или -A1

Читайте так же:
Как восстановить закрепленные вкладки в хроме

Возведение в степень (^)

Умножение (*) и деление (/), в зависимости от того, что наступит раньше

Сложение (+) и вычитание (-), в зависимости от того, что наступит раньше

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

Как изменить порядок расчета в Excel

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

Например, расчет =2*4+7 сообщает Excel умножить 2 на 4, а затем добавить 7 к значению. Результат этого вычисления равен 15. Заключив операцию сложения в скобках =2*(4+7), вы даете команду Excel сначала просуммировать 4 и 7, а затем умножить сумму на 2. И результатом этого расчета является значение 22.

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

Как правильно использовать формулы в таблицах Excel

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

Я ввел данные, а затем преобразовал диапазон в таблицу с помощью команды Вставка ► Таблицы ► Таблица. Обратите внимание на то, что я не определял никаких имен, а область данных в таблице названа Таблица1 по умолчанию.

Рис. 160.1. Простая таблица стремя столбцами

Рис. 160.1. Простая таблица стремя столбцами

Работа с итоговой строкой

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

  1. Активизируйте любую ячейку в таблице.
  2. Выполните команду Работа с таблицами ► Параметры стилей таблиц и установите флажок Строка итогов.
  3. Активизируйте ячейку в строке итогов и выберите результирующую формулу в раскрывающемся списке (рис. 160.2).

Например, для расчета суммы значений столбца Реальный показатель выберите Сумма в раскрывающемся списке в ячейке D15 . Excel создаст следующую формулу:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;[Реальный показатель]) .

Для функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ 109 является перечисленным аргументом, который представляет сумму. Второй аргумент функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ — это имя столбца в квадратных скобках. Использование названий столбцов в квадратных скобках — способ создания структурированных ссылок внутри таблиц.

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

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

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

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

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

  1. Активизируйте ячейку Е2 и введите Разница в заголовок столбца. Excel автоматически расширит таблицу.
  2. Перейдите к ячейке Е3 и введите знак равенства, обозначающий начало формулы.
  3. Щелкните на левой стрелке, указав на соответствующее значение н столбце Реальный показатель.
  4. Введите знак минус (-), а затем щелкните два раза на левой стрелке, указывающей на соответствующее значение в столбце По плану.
  5. Нажмите Enter, чтобы завершить формулу (рис. 160.3).
Читайте так же:
Интерполяция данных в excel

Формула вводится в другие ячейки столбца, и строка формул отображает ее:
=[@[Реальный показатель]]-[@[По плану]]

Рис. 160.3. Столбец Разница содержит формулу

Рис. 160.3. Столбец Разница содержит формулу

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

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

Ранее в этой статье для создания формулы применялись имена столбцов. Однако вы можете ввести формулу, используя стандартные ссылки на ячейки. Например, попробуйте ввести следующую формулу в ячейку Е3: =D3-C3 . Если ввести ссылки на ячейки, то Excel по-прежнему автоматически скопирует формулу в другие ячейки столбца.

Ссылки на данные в таблице

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

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

Обратитесь к таблице, показанной на рис. 160.1. Ей было присвоено имя Таблица1 при создании. Для вычисления суммы всех данных в таблице укажите такую формулу: =СУММ(Таблица1) . Эта формула всегда возвращает сумму всех данных, даже если строки или столбцы добавляются или удаляются. Если же вы измените название Таблица1, Excel автоматически откорректирует формулы, которые ссылаются на нее. Например, если переименовать Таблица1 в AnnualData (используя Диспетчер имен), предыдущая формула изменится так: =СУММ(Annual Data) .

Во многих случаях нужно обратиться к конкретному столбцу в таблице. Следующая формула возвращает сумму данных столбца Реальный показатель (но игнорирует итоговую строку): =СУММ(Таблица1[Реальный показатель]) . Обратите внимание, что имя столбца заключено в квадратные скобки. Снова формула автоматически подстраивается, если вы изменяете текст в заголовке столбца.

Читайте так же:
Как в word поставить нумерацию страниц

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

Рис. 160.4. Формула автозаполнения полезна при создании формулы, которая относится к данным в таблице

Рис. 160.4. Формула автозаполнения полезна при создании формулы, которая относится к данным в таблице

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

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

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

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

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

  • Использование сочетания клавиш.
  • Использование специальной техники вставки.
  • Использование классного трюка с маневром мышки.

Использование сочетания клавиш

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

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

  • Выделите ячейки, для которых вы хотите преобразовать формулы в значения.
  • Скопируйте ячейки (Control + C).
  • Вставить как значения — Сочетание клавиш — ALT + ESV

Это мгновенно преобразует все формулы в статические значения.

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

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

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

Вот шаги для преобразования формул в значения с помощью специальной вставки:

  • Выделите ячейки, для которых вы хотите преобразовать формулы в значения.
  • Скопируйте ячейки (Control + C).
  • Перейдите на главную -> Буфер обмена -> Вставить -> Специальная вставка.

Вы также можете получить доступ к той же специальной опции вставки, если щелкните правой кнопкой мыши скопированную ячейку. Наведите указатель мыши на опцию «Специальная вставка», и вы увидите все имеющиеся в ней опции.

Использование трюка с покачиванием мыши

Это одна уловка с мышью, о которой не многие люди знают.

  • Выделите ячейки, для которых вы хотите преобразовать формулы в значения.
  • Наведите курсор мыши на контур выбранных ячеек. (Вы увидите значок с четырьмя стрелками, указывающими в четырех направлениях).
  • Нажмите ПРАВУЮ кнопку мыши. Удерживая кнопку, перетащите контур вправо и верните его в исходное положение. Как только вы оставите кнопку мыши, появится меню.
  • Щелкните Копировать здесь только как значения.
  • Вот и все. Он мгновенно преобразует формулы в значения в выбранных ячейках.
Читайте так же:
Знак евро в excel

Надеюсь, вы нашли этот урок полезным.

Как сделать формулу в Экселе?

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

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

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

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

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

Использование адресов ячеек в формулах Эксель

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

Применение в формуле адреса ячейки и числа

Применение записанный формулы к диапазону ячеек

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector