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

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

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

Как прописать начальные понятия

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

Дело в том, что машина ввод сочетания вроде «2 х 2» или вынесение общего компонента за скобки («2+2)5») не понимает. Для этого предусмотрено использование нескольких типов символов, которые представлены в таблице ниже, не считая логических операторов.

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

Сумма и разность

Как в Excel прописать формулу суммы или разности? Итак, начнем с самого простого, когда требуется вычислить сумму. В строку формул (причем, для всех операций) сначала вводится знак равенства, после чего вписывается нужная формула. В случае с обычным калькулятором можно указать для установленной ячейки «=2+2».

Если же суммирование производится для значений, введенных непосредственно в других ячейках (например, A1 и A2), формула приобретает вид «=A1+A2». Нередко для использования дополнительных операторов сумма может заключаться в скобки. Для разности - то же самое, только с минусом вместо плюса.

Когда нужно указать номера ячеек или их диапазон, может применяться специальная команда суммы (в русском варианте "СУММ", в английском - SUM). При указании нескольких ячеек это выглядит так: «=СУММ(A1;A2)», для диапазона - «СУММ(A1:A10)» при условии того, что нужно вычислить сумму всех чисел, находящихся в ячейках с первой по десятую. В принципе, если установить активной ячейку, которая находится сразу же после последней в столбце с исходными значениями, формулу можно не вводить, а просто нажать на кнопку автоматического суммирования.

Умножение, деление и возведение в степень

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

Для произведения используется вид «=A1*A2», для частного - «A1/A2». Это, кстати, точно такие же команды, которые можно встретить при использовании стандартного Windows-калькулятора.

Для возведения в степень применяется символ «^». Для значения в ячейке A1, которое, например, нужно возвести в квадрат, применяется формула «=A1^2».

Процентные вычисления

С процентами, если не затрагивать сложные вычисления, тоже все просто. Как в Excel прописать формулу с процентами?

Достаточно ввести формулу вида «=A1*5%», после чего вы и получите те самые пять процентов от значения в соответствующей ячейке.

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

Но все это относилось к ручному заданию или так называемому директ-вводу формул (прямой или непосредственный ввод). На самом деле иногда бывает полезно использовать мышь и клавишу Ctrl.

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

Абсолютные, относительные и смешанные типы ячеек

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

Абсолютная ячейка является неизменной и обозначается, как $A$1, относительная представляет собой ссылку на обычное местоположение (A1), смешанная - есть комбинация ссылок и на абсолютную, и на относительную ячейку ($A1 или A$1). Обычно такие форматы применяются при создании когда в формулах задействованы данные, находящиеся на разных листах книги или даже в разных файлах.

Формулы ВПР

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

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

В этом варианте сначала через «Мастер функций» используется диапазон значений исходной (первой) таблицы, в поле «Таблица» указывается второй диапазон с фиксацией содержимого (F4), далее указывается номер столбца, а в поле интервального просмотра устанавливается значение «ЛОЖЬ», если действительно при фильтрации действительно нужно получить только точные, а не приблизительные значения. Как правило, такие формулы используются больше в складском или бухгалтерском учете, когда установить какие-то специализированные программные продукты не представляется возможным.

Заключение

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

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

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

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

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

Чтобы ввести любую формулу в excel, нужно сначала вставить знак «=». Если перед вводом любого значения указать этот знак, программа поймет, что вводится формула. Если указать формулу без этого символа, то программа не сможет выполнить вычисление.

Простые вычисления

Выделите любую ячейку, например А2;
В строчке со знаком Fx (расположена она вверху над таблицей) введите «=1+3» и нажмите «Enter»;
В выделенной ячейке вы увидите результат (4). В строчке формул останется введенное значение.

Также в excel можно использовать и другие арифметические символы: «-» – для вычитания, «*» – для умножения, «/» – для деления. Попробуйте вводить разные формулы в строчке Fx.

Основные правила ввода:

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

Формулы с ячейками

В excel вы можете применять формулы с указанием ячеек. Допустим, в ячейке В2 введите число 7, в ячейке В3 – число 20. В ячейке В4 укажите формулу «=В2+В3». В ячейке В4 вы увидите итог сложения в ячейках В2 и В3 – 27. Можно поменять значения в них, тогда автоматически изменится и результат в ячейке В4.

Применение мыши

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

Кликните по ячейке В4 левой клавишей мышки и вставьте знак «=»;
Выделите ячейку В2 и вставьте знак «+» на клавиатуре;
Кликните по ячейке В3 левой клавишей мыши два раза и нажмите «Enter».

Типы ссылок

Всего бывает 3 их разновидности:

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

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

Относительные ссылки

Укажите в полях таблицы:

В2 - 10 С2 – 100
В3 - 70 С3 – 700

В ячейке В4 укажите «=В2+В3», потом «Enter».

Далее установите курсор в правом нижнем углу В4, удержите правую клавишу мыши, протяните на ячейку С4, и отпустите клавишу манипулятора. В открывшемся контекстном меню выберите «Копировать ячейки». Значение из поля В4 скопируется в ячейку С4. Активируйте ячейку С4, вы увидите в ней формулу – «С2+С3». То есть, вроде вы скопировали формулу из поля В4, но значение получилось другое. Этим и отличается данный тип ссылок. Например, если вы возьмете ячейку В4 и скопируете в ячейку А54, например, формула будет получаться сложением ячеек над ней, то есть «А52+А53».

Абсолютные ссылки

Укажите в ячейках:

В2 - 10 С2 – 100
В3 - 70 С3 – 700
Введите в поле D2 значение 10.

В поле В4 укажите формулу «=В2+В3+$D$2». Скопируйте формулу из В4 в С4. Что в итоге получилось? Обычные ссылки изменились, а вот абсолютные не поменялись.

Допустим, чтобы ввести в поле В2 (Лист 1) ссылку на поле В7 (Лист2), выполните следующие шаги:

Выделите В2 и вставьте знак«=»;
Кликните по ярлыку «Лист 2»;
Выделите В7 и нажмите клавишу ввода;
Снова откроется Лист 1, а в поле В2 появится формула «=Лист2!В7».

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

Текст в формулах

Текстовые значения использовать для формул, если в них есть следующие символы: «0 – 9», «+», «-», «е», «Е», «/». Также можно использовать следующие символы форматирования: «$», «%», «(», «)», «пробел», «";;».

Например, если в ячейке В1 значение "Иван", а в ячейке В2 – "Иванов", а в ячейке В4 будет формула «=В2&В3», то результат получится "ИванИванов".

Знак «&;» – это текстовый оператор, амперсанд, который объединяет текст.

Чтобы вставить пробел, используется «&";; "&», то есть формула будет такой – «=В2&" "&В3».

Амперсанд используется и для объединения чисел с текстом. Например, если в первой ячейке число5, а во второй ячейке – ведер, то в результате получится «5ведер».

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

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

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

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

— «*» - произведение (=4*3)
— «/» - деление (=4/3)
— «+» - сложение (=4-3)
— «-» - вычитание (=4+3)
— «^» - возведение в степень (=4^3)
— «%» - нахождение процента (для нахождения 4% от 156 используется такое выражение «=156*4%», то-есть при дописывании «%» к числу, оно делится на 100 и «4%» преобразуется в «0.04»).

Пример. Формула суммы в экселе:
Для ячейки A1 ввели выражение «=12+3». Программа автоматически провела исчисление и указала в ячейке не введенную формулу, а решение примера. Реализация других арифметических операций выполняется таким же образом.
В формуле можно использовать одновременно несколько операторов: «=56 + 56*4%» — данное выражение добавляет к числу 56 четыре процента.

Для правильной работы с операторами, следует знать правила приоритетности:
1) подсчитываются выражения в скобочках;
2) после произведения и деления считается сложение и вычитание;
3) выражения выполняются слева направо, если имеют одинаковый приоритет.

Формулы «=5*4+8» и «=5*(4+8)» имеют разные значения, потому что в первом случае изначально выполняется умножение «5*4», а в другом вычисляется выражение стоящее в скобках «4+8».

Формула excel среднего значения

Полезная формула, которая позволяет найти среднее значение нескольких введенных чисел и обозначается «=СРЗНАЧ()». Среднее значение чисел: 5, 10, 8 и 1 – это результат деления их суммы на количество, то-есть 24 на 4. Реализуется данная функция в excel таким образом:

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

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

Ячейки A1, A2 содержат числа 12, 15 относительно. При добавлении в ячейку A3 формулы «=A1+A2» в ней появится значение суммы чисел находящихся в ячейках A1 и A2.

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

Диапазон ячеек

В excel можно оперировать определенным диапазоном ячеек, что упрощает заполнение формул. Например для подсчета суммы ячеек от A1 до A6 необязательно вводить последовательность «=A1+A2+A3…”, а достаточно будет ввести оператор сложения «СУММ()» и зажав левую кнопку мыши провести от A1 до А6.

Результатом работы будет формула «=СУММ(A1:A6)».

Текст в формулах

Для использования текста в формулах, его необходимо заключать в двойные кавычки — «текст». Чтобы объединить 2-а текстовых значения используется оператор амперсанд «&», который соединяет их в одну ячейку с присвоением типа «текстовое значение».

Чтобы вставить пробел между словами нужно записать так » =А1&» «&А2 «.

Данный оператор может объединять текстовые значения и числовые, так например можно быстро заполнить подобную таблицу, соединив числа 10, 15, 17, 45 и 90 с текстом «шт.»

Добрый день.

Когда-то, написать самостоятельно формулу в Excel - для меня было чем-то невероятным. И даже, несмотря на то, что часто приходилось работать в этой программе, ничего кроме текста не набивал…

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

И так, начнем…

1. Основные операции и азы. Обучение основам Excel.

Все действия в статье будут показываться в Excel версии 2007г.

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

Формула должна начинаться со знака «=». Это обязательное условие. Дальше вы пишите то, что вам нужно посчитать: например, «=2+3» (без кавычек) и нажимаете по клавише Enter - в результате вы увидите, что в ячейке появился результат «5». См. скриншот ниже.

Важно! Несмотря на то, что в ячейке А1 написано число «5» - оно считается по формуле («=2+3»). Если в соседней ячейке просто текстом написать «5» - то при наведении курсора на эту ячейку - в редакторе формулы (строка сверху, Fx ) - вы увидите простое число «5».

А теперь представьте, что в ячейку вы можете писать не просто значение 2+3, а номера ячеек, значения которых нужно сложить. Допустим так «=B2+C2».

Естественно, что в B2 и C2 должны быть какие-нибудь числа, иначе Excel покажет нам в ячейке A1 результат равный 0.

И еще одно важное замечание…

Когда вы копируете ячейку, в которой есть формула, например A1 - и вставляете ее в другую ячейку - то копируется не значение «5», а сама формула!

Причем, формула изменится прямо-пропорционально: т.е. если A1 скопировать в A2 - то формула в ячейке A2 будет равна «=B3+C3». Excel сам меняет автоматически вашу формулу: если A1=B2+C2, то логично, что A2=B3+C3 (все цифры увеличились на 1).

Результат, кстати, в A2=0, т.к. ячейки B3 и С3 не заданы, а значит равны 0.

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

Если вы не хотите, чтобы B2 и С2 изменялись при копировании и всегда были привязаны к этим ячейкам, то просто добавьте к ним значок «$». Пример ниже.

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

2. Сложение значений в строках (формула СУММ и СУММЕСЛИМН)

Можно, конечно, каждую ячейку складывать, делая формулу A1+A2+A3 и т.п. Но чтобы так не мучатся, есть в Excel специальная формула, которая сложит все значения в ячейках, которые вы выделите!

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

Для этого переходим в ячейку, в которой будет показываться результат и пишем формулу: «=СУММ(C2:C5)». См. скриншот ниже.

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

2.1. Сложение с условием (с условиями)

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

Для этого есть замечательная формула «СУММЕСЛИМН «. Сразу же пример, а затем пояснение каждого символа в формуле.

=СУММЕСЛИМН(C2:C5 ;B2:B5 ;«<100» ) , где:

C2:C5 - та колонка (те ячейки), которые будут суммироваться;

B2:B5 - колонка, по которой будет проверяться условие (т.е. цена, например, менее 100);

«<100» - само условие, обратите внимание, что условие пишется в кавычках.

Ничего сложного в этой формуле нет, главное соблюдать соразмерность: C2:C5;B2:B5 - правильно; C2:C6;B2:B5 - неправильно. Т.е. диапазон суммирования и диапазон условий должны быть соразмерны, иначе формула вернет ошибку.

Важно! Условий для суммы может быть много, т.е. можно проверять не по 1-й колонке, а сразу по 10, задав множество условий.

3. Подсчет количества строк, удовлетворяющих условиям (формула СЧЁТЕСЛИМН)

Довольно часто-встречающаяся задача: подсчитать не сумму значений в ячейках, а количество таких ячеек, удовлетворяющих определенным условиям. Иногда, условий очень много.

И так… начнем.

Для подсчета товаров в нужной ячейке написали следующую формулу (см. выше):

=СЧЁТЕСЛИМН(B2:B5 ;«>90» ) , где:

B2:B5 - диапазон, по которому будут проверять, по заданному нами условию;

«>90» -само условие, заключается в кавычки.

Теперь попробуем немного усложнить наш пример, и добавим счет еще по одному условию: с ценой больше 90 + количество на складе менее 20 кг.

Формула приобретает вид:

=СЧЁТЕСЛИМН(B2:B6;»>90″;C2:C6 ;«<20» )

Здесь все осталось таким же, кроме еще одного условия (C2:C6;»<20″ ). Кстати, таких условий может быть очень много!

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

4. Поиск и подстановка значений из одной таблицы в другую (формула ВПР)

Представим, что к нам пришла новая таблица, с новыми ценниками для товара. Хорошо, если наименований 10-20 - можно и в ручную их все «перезабить». А если таких наименований сотни? Гораздо быстрее, если бы Excel самостоятельно нашел в совпадающие наименования из одной таблицы в другой, а затем скопировал новые ценники в старую нашу таблицу.

Для такой задачи используется формула ВПР . В свое время сам «мудрил» с логическими формулами «ЕСЛИ» пока не встретил эту замечательную штуку!

И так, начнем…

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

Ставим курсор в ячейку B2 - т.е. в первую ячейку, где нам нужно изменить ценник автоматически. Далее пишем формулу, как на скриншоте ниже (после скриншота будет подробное пояснение к ней).

=ВПР(A2 ;$D$2:$E$5 ;2 ) , где

A2 - то значение, которое мы будем искать, чтобы взять новый ценник. В нашем случае ищем в новой таблице слово «яблоки».

$D$2:$E$5 - выделяем полностью нашу новую таблицу (D2:E5, выделение идет от верхнего левого угла к правому нижнему по диагонали), т.е. там, где будет производится поиск. Знак «$» в этой формуле необходим для того, чтобы при копировании этой формулы в другие ячейки - D2:E5 не менялись!

Важно! Поиск слова «яблоки» будет вестись только в первой колонке вашей выделенной таблицы, в данном примере «яблоки» будет искаться в колонке D.

2 - Когда слово «яблоки» будет найдено, функция должна знать, из какого столбика выделенной таблицы (D2:E5) скопировать нужное значение. В нашем примере копировать из колонки 2 (E), т.к. в первой колонке (D) мы производили поиск. Если ваша выделенная таблица для поиска будет состоять из 10 колонок, то в первой колонке производится поиск, а со 2 по 10 колонки - вы можете выбрать число для копирования.

Чтобы формула =ВПР(A2;$D$2:$E$5;2) подставила новые значения и для других наименований товара - просто скопируйте ее в другие ячейки столбца с ценниками товара (в нашем примере копируйте в ячейки B3:B5). Формула автоматически произведет поиск и копирование значения из нужной вам колонки новой таблицы.

5. Заключение

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

Надеюсь что кому-то пригодятся разобранные примеры и помогут ускорить его работу. Удачных экспериментов!

А какие формулы используете вы, можно ли как-то упростить формулы приведенные в статье? Например, на слабых компьютерах, при изменении каких-то значений в больших таблицах, где производятся автоматически расчеты - компьютер зависает на пару секунд, пересчитывая и показывая новые результаты…

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

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

Пример использования знаков "умножение" и "равно"

Почему в экселе формула не считает

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

Неверный формат ячеек или неправильные настройки диапазонов ячеек

В Excel возникают различные ошибки с хештегом (#), такие как #ЗНАЧ!, #ССЫЛКА!, #ЧИСЛО!, #Н/Д, #ДЕЛ/0!, #ИМЯ? и #ПУСТО!. Они указывают на то, что что-то в формуле работает неправильно. Причин может быть несколько.

Вместо результата выдается #ЗНАЧ! (в версии 2010) или отображается формула в текстовом формате (в версии 2016).

Примеры ошибок в формулах

В данном примере видно, что перемножается содержимое ячеек с разным типом данных =C4*D4.

Исправление ошибки: указание правильного адреса =C4*E4 и копирование формулы на весь диапазон.

Ошибка #ССЫЛКА! возникает, когда формула ссылается на ячейки, которые были удалены или заменены другими данными.
Ошибка #ЧИСЛО! возникает тогда, когда формула или функция содержит недопустимое числовое значение.
Ошибка #Н/Д обычно означает, что формула не находит запрашиваемое значение.
Ошибка #ДЕЛ/0! возникает, когда число делится на ноль (0).
Ошибка #ИМЯ? возникает из-за опечатки в имени формулы, то есть формула содержит ссылку на имя, которое не определено в Excel.
Ошибка #ПУСТО! возникает, если задано пересечение двух областей, которые в действительности не пересекаются или использован неправильный разделитель между ссылками при указании диапазона.

Примечание: #### не указывает на ошибку, связанную с формулой, а означает, что столбец недостаточно широк для отображения содержимого ячеек. Просто перетащите границу столбца, чтобы расширить его, или воспользуйтесь параметром Главная - Формат - Автоподбор ширины столбца .

Ошибки в формулах

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

Исправление: Выделите ячейку или диапазон ячеек. Нажмите знак "Ошибка" (смотри рисунок) и выберите нужное действие.

Пример исправления ошибок в Excel

Включен режим показа формул

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

Отключен автоматический расчет по формулам

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

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

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

Выполнить сложение в электронных таблицах достаточно просто. Нужно написать формулу, в которой будут указаны все ячейки, содержащие данные для сложения. Конечно же, между адресами ячеек ставим плюс. Например, =C6+C7+C8+C9+C10+C11 .

Пример вычисления суммы в Excel

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

Пример использования функции Автосумма

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

Формула округления в Excel до целого числа

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

Уменьшение разрядности не округляет число

Для округления числа по математическим правилам необходимо использовать встроенную функцию =ОКРУГЛ(число;число_разрядов).

Математическое округление числа с помощью встроенной функции

Написать её можно вручную или воспользоваться мастером функций на вкладке Формулы в группе Математические (смотрите рисунок).

Мастер функций Excel

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

Как считать проценты от числа

Для подсчета процентов в электронной таблице выберите ячейку для ввода расчетной формулы. Поставьте знак «равно», затем напишите адрес ячейки (используйте английскую раскладку), в которой находится число, процент от которого будете вычислять. Можно просто кликнуть мышкой в эту ячейку и адрес вставится автоматически. Далее ставим знак умножения и вводим число процентов, которое необходимо вычислить. Посмотрите на пример вычисления скидки при покупке товара.
Формула =C4*(1-D4)

Вычисление стоимости товара с учетом скидки

В C4 записана цена пылесоса, а в D4 – скидка в %. Необходимо вычислить стоимость товара с вычетом скидки, для этого в нашей формуле используется конструкция (1-D4) . Здесь вычисляется значение процента, на которое умножается цена товара. Для Excel запись вида 15% означает число 0.15, поэтому оно вычитается из единицы. В итоге получаем остаточную стоимость товара в 85% от первоначальной.

Вот таким нехитрым способом с помощью электронных таблиц можно быстро вычислить проценты от любого числа.

Шпаргалка с формулами Excel

Шпаргалка выполнена в виде PDF-файла. В нее включены наиболее востребованные формулы из следующих категорий: математические, текстовые, логические, статистические. Чтобы получить шпаргалку, кликните ссылку ниже.

PS: Интересные факты о реальной стоимости популярных товаров

Дорогой читатель! Вы посмотрели статью до конца.
Получили вы ответ на свой вопрос? Напишите в комментариях пару слов.
Если ответа не нашли, укажите что искали .