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

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

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

Ниже приведен пример формулы, умножающей 2 на 3 и прибавляющей к результату 5, чтобы получить 11.

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

Части формулы Excel

Формула также может содержать один или несколько таких элементов, как функции , ссылки , операторы и константы .

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

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

Использование функций и вложенных функций в формулах Excel

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

Использование ссылок в формулах Excel

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

Использование имен в формулах Excel

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

Пример 1

Пример 2

Скопируйте образец данных из приведенной ниже таблицы и вставьте его в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем - клавишу ВВОД. Кроме того, вы можете настроить ширину столбцов в соответствии с содержащимися в них данными.

Примечание: В формулах в столбцах C и D определенное имя "Продажи" заменяется ссылкой на диапазон A9:A13, а имя "ИнформацияОПродажах" заменяется диапазоном A9:B13. Если же в книге не будет этих имен, формулы в D2:D3 вернут ошибку #ИМЯ?.

Тип примера	Пример, в котором не используются имена	Пример, в котором используются имена	Формула и результат с использованием имен
	"=СУММ(A9:A13)	"=СУММ(Продажи)	СУММ(Продажи)
	"=ТЕКСТ(ВПР(МАКС(A9:13),A9:B13,2,ЛОЖЬ),"dd/mm/yyyy")	"=ТЕКСТ(ВПР(МАКС(Продажи),ИнформацияОПродажах,2,ЛОЖЬ),"дд.мм.гггг")	ТЕКСТ(ВПР(МАКС(Продажи),ИнформацияОПродажах,2,ЛОЖЬ),"дд.мм.гггг")


	Дата продажи

Дополнительные сведения см. в статье Определение и использование имен в формулах .

Использование формул массива и констант массива в Excel

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

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

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

Примечание: При вводе формулы массива Excel автоматически заключает ее в фигурные скобки { и }. При попытке вручную ввести фигурные скобки Excel отобразит формулу как текст.

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

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

Константы массива могут содержать числа, текст, логические значения, например ИСТИНА или ЛОЖЬ, либо значения ошибок, такие как #Н/Д. В одной константе массива могут присутствовать значения различных типов, например {1,3,4;ИСТИНА,ЛОЖЬ,ИСТИНА}. Числа в константах массива могут быть целыми, десятичными или иметь экспоненциальный формат. Текст должен быть заключен в двойные кавычки, например "Вторник".

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

Константы заключены в фигурные скобки ({ } ).

Столбцы разделены запятыми (, ). Например, чтобы представить значения 10, 20, 30 и 40, введите {10,20,30,40}. Эта константа массива является матрицей размерности 1 на 4 и соответствует ссылке на одну строку и четыре столбца.

Значения ячеек из разных строк разделены точками с запятой (; ). Например, чтобы представить значения 10, 20, 30, 40 и 50, 60, 70, 80, находящиеся в расположенных друг под другом ячейках, можно создать константу массива с размерностью 2 на 4: {10,20,30,40;50,60,70,80}.

Удаление формулы

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

У функции могут быть обязательные и необязательные аргументы (последние обозначаются в синтаксисе квадратными скобками). Необходимо ввести все обязательные аргументы. Старайтесь также не вводить слишком много аргументов.

В формуле не больше 64 уровней вложенности функций

Уровней вложенности не может быть более 64.

Имена книг и листов заключены в одинарные кавычки

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

Указан путь к внешним книгам

Числа введены без форматирования

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

Важно: Вычисляемые результаты формул и некоторые функции листа Excel могут несколько отличаться на компьютерах под управлением Windows с архитектурой x86 или x86-64 и компьютерах под управлением Windows RT с архитектурой ARM.

У вас есть вопрос об определенной функции?

Помогите нам улучшить Excel

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

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

Формула начинается знаком "равно" и в простейшем случае состоит из чисел, имен ячеек и знаков арифметических операций.

=B2*C2
=D2+D3+D4+D5
=D7*0,1

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

=(D2+D3+D4)/3

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

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

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

Можно значительно облегчить процесс набора формул за счет автоматической подстановки в набираемую формулу адреса ячейки, в которой сделан щелчок левой кнопкой мыши. Например, в ячейку "D4" формулу "=B4*C4" можно ввести следующим образом. Сначала надо щелкнуть левой кнопкой мыши в ячейке "D4" и нажать клавишу со знаком "равно". Будет активизирован процесс ввода формулы в ячейку. Затем надо щелкнуть левой кнопкой в ячейке "B4". Ячейка B4 будет выделена рамкой, и ее адрес появится в ячейке D4, после знака "равно". После этого надо ввести символ умножения - звездочку, и щелкнуть левой кнопкой мыши в ячейке C4 (имя ячейки появится в ячейке D4 после звездочки). Формула введена.

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

Если в формуле есть ошибка, например в имени ячейки будет использована русская буква, то после нажатия клавиши в ячейке с формулой появится сообщение об ошибке: #ИМЯ?, а ячейка, в которой сделана ошибка, будет помечена восклицательным знаком.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Объединение текстовых значений

Для объединения ячеек с текстовым значением можно использовать разные формулы, однако они имеют свои нюансы. Например, команда =СЦЕПИТЬ(D4;E4) успешно объединит две ячейки, равно как и более простая функция =D4&E4, однако никакого разделителя между словами добавлено не будет – они отобразятся слитно.

Избежать данного недочета можно добавляя пробелы, либо в конце текста каждой ячейки, что вряд ли можно назвать оптимальным решением, либо непосредственно в самой формуле, куда в любое место можно вставить набор символов в кавычках, в том числе и пробел. В нашем случае формула =СЦЕПИТЬ(D4;E4) получит вид =СЦЕПИТЬ(D4;» «;E4). Впрочем, если вы объединяете большое количество текстовых ячеек, то аналогичным образом пробел вручную придется прописывать после адреса каждой ячейки.

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

Другой типовой формулой для склеивания ячеек с текстом является команда ОБЪЕДИНИТЬ. По своему синтаксису она по умолчанию содержит два дополнительных параметра – сначала идет конкретный символ разделения, затем команда ИСТИНА или ЛОЖЬ (в первом случае пустые ячейки из указанного интервала будут игнорироваться, во втором – нет), и потом уже список или интервал ячеек. Между ячейками также можно использовать и обычные текстовые значения в кавычках. Например, формула =ОБЪЕДИНИТЬ(» «;ИСТИНА;D4:F4) склеит три ячейки, пропустив пустые, если таковые имеется, и добавит между словами по пробелу.

Применение: Данная опция часто используется для склеивания ФИО, когда отдельные составные части находятся в разных колонках и есть общая сводная колонка с полным именем человека.

Выполнение условия ИЛИ

Простой оператор ИЛИ определяет выполнение заданного в скобках условия и на выходе возвращает одно из значений ИСТИНА или ЛОЖЬ. В дальнейшем данная формула может использоваться в качестве составного элемента более сложных условий, когда в зависимости от того, что выдаст значение ИЛИ будет выполняться то или иное действие.

При этом сравниваться могут как численные показатели, применяя знаки >, <, =, так и поиск конкретного значения для ячейки, которое может быть текстовым. В частности, для поиска слова «Сдал» в конкретных ячейках будет использоваться формула =ИЛИ(D4= «Сдал»; E4= «Сдал»; F4= «Сдал»)

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

Поиск и использование значения

По горизонтали

Используя функцию ГПР мы можем задать поиск по конкретной строке таблицы, а на выходе получить значение из другой ячейки того же столбца (на одну или несколько строк ниже), что соответствует заданному условию. Причем поиск задается либо на точное значение (используется оператор ЛОЖЬ), либо на приблизительное (с оператором ИСТИНА), что позволяет использовать интервалы. Синтаксис =ГПР(искомое_значение; таблица; номер_строки; интервальный_просмотр)

Применение: Для расчета бонуса конкретному сотруднику можно задать интервалы, начиная от которых действует тот или иной процент от прибыли. Скажем, формула =ГПР(E5;$D$1:$G$2;2;ИСТИНА) будет искать в первой строке таблицы из интервала D1:G2 значение, приблизительно схожее со значением из ячейки E5, а результатом формулы станет вывод ячейки со второй строки соответствующего столбца.

По вертикали

Аналогичным образом действует функция ВПР – только логика действия немного другая. Поиск будет вестись не по горизонтали, а по вертикали, то есть по ячейкам одного столбца, а результат браться из указанной ячейки найденной строки.

То есть для формулы =ВПР(E4;$I$3:$J$6;2;ИСТИНА) будет сравниваться значение ячейки E4 с ячейками столбца I из таблицы интервала I3:J6, а значение будет выдаваться из соседней ячейки столбца J.

Выполнение условия ЕСЛИ

При использовании данной функции задается конкретное условие, а следом два результата – один для случаев, если условия выполнено, а другое – наоборот. Скажем для сравнения денежных средств из двух колонок может использоваться такая формула =ЕСЛИ(C2>B2; «Превышение бюджета»; «В пределах бюджета»).

Кроме того, в качестве условия может использоваться другая функция, например, условие ИЛИ и даже еще одно условие ЕСЛИ. При этом у воженных функций ЕСЛИ может быть от 3 до 64 возможных результатов). Как пример, =ЕСЛИ(D4=1; «ДА»;ЕСЛИ(D4=2; «Нет»; «Возможно»)).

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

Формула ранжирования

Для значения чисел можно использовать формулу РАНГ, которая выдаст величину каждого числа относительно других в заданном списке. При этом ранжирование может быть как от меньшего значения в сторону увеличения, так и обратно.

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

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

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

Максимум из выбранных значений

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

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

Минимум из выбранных значений

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

Среднее из выбранных значений

Для получения среднего арифметического из выбранного списка значений также есть своя формула. Однако написание ее в русском языке не столь очевидно. Звучит она как СРЗНАЧ, после чего в скобках указываются либо конкретные значения, либо ссылки на ячейки.

Сумма выбранных значений

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

Куда более интересным вариантом является суммирование ячеек, отвечающих конкретным критериям. Для этого используется оператор СУММЕСЛИ с аргументами диапазон, условие, диапазон суммирования.

Применение: Например, есть список школьников, согласившихся поехать на экскурсию. У каждого есть статус – оплатил он мероприятие или нет. Таким образом, в зависимости от содержимого столбца «Оплатил» значение из столбца «Стоимость» будет считаться или нет. =СУММЕСЛИ(E5:E9; «Да»; F5:F9)

Примечание: Подробную информацию об использовании каждой функции Excel можно найти