Сложные формулы в excel примеры

Формулы EXCEL с примерами — Инструкция по применению

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

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

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

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

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

Содержание:

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

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

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

В программе можно вычислить целый комплекс показателей, в числе которых:

• максимальные и минимальные значения;
• средние показатели;
• проценты;
• критерий Стьюдента и многое другое.

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

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

При этом необходимость вводить дополнительные данные и параметры отпадает.

Как применять простые формулы в программе?

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

Для этого необходимо ввести в одну ячейку одно число, а во вторую – другое.

Например, В Ячейку А1 – число 5, а в ячейку В1 – 3. Для того чтобы в ячейке А3 появилось суммарное значение необходимо ввести формулу:

Вычисление суммарного значения двух чисел

Определить сумму чисел 5 и 3 может каждый человек, но вводить число в ячейку С1 самостоятельно не нужно, так как в этом и замысел расчета формул.

После введения итог появляется автоматически.

При этом если выбрать ячейку С1, то в верхней строке видна формула расчета.

Если одно из значений изменить, то перерасчет происходит автоматически.

Например, при замене числа 5 в ячейке В1 на число 8, то менять формулу не нужно, программа сама просчитает окончательное значение.

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

Сумма дробных чисел

В Excel можно производить любые арифметические операции: вычитание «-», деление «/», умножение «*» или сложение «+».

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

Любая формула должна начинаться знаком «=».

Если вначале не поставить «равно», то программа не сможет выдать необходимое значение, так как данные введены неправильно.

Создание формулы в Excel

В приведенном примере формула =СУММ(A1;B1) позволяет определить сумму двух чисел в ячейках, которые расположены по горизонтали.

Формула начинается со знака «=». Далее задана функция СУММ. Она указывает, что необходимо произвести суммирование заданных значений.

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

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

Формула суммы трех заданных чисел

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

=СУММ(A1:A10). На рисунке арифметическая операция будет выглядеть следующим образом:

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

Также можно определить произведение этих чисел. В формуле вместо функции СУММ необходимо выбрать функцию ПРОИЗВЕД и задать диапазон ячеек.

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

Комбинированные формулы

Диапазон ячеек в программе указывается с помощью заданных координат первого и последнего значения. В формуле они разделяются знаком «:».

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

Если нужно найти сумму трех чисел и умножить сумму на коэффициенты 1,4 или 1,5, исходя из того, меньше ли итог числа 90 или больше.

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

Вся правда о формулах программы Microsoft Excel 2007

Формулы EXCEL с примерами — Инструкция по применению

Функция ЕСЛИ — вложенные формулы и типовые ошибки

Функция ЕСЛИ позволяет выполнять логические сравнения значений и ожидаемых результатов. Она проверяет условие и в зависимости от его истинности возвращает результат.

=ЕСЛИ(это истинно, то сделать это, в противном случае сделать что-то еще)

Поэтому у функции ЕСЛИ возможны два результата. Первый результат возвращается в случае, если сравнение истинно, второй — если сравнение ложно.

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

* «Вложенность» означает объединение нескольких функций в одной формуле.

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

ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])

Условие, которое нужно проверить.

Значение, которое должно возвращаться, если лог_выражение имеет значение ИСТИНА.

Значение, которое должно возвращаться, если лог_выражение имеет значение ЛОЖЬ.

Примечания

Excel позволяет использовать до 64 вложенных функций ЕСЛИ, но это вовсе не означает, что так и надо делать. Почему?

Нужно очень крепко подумать, чтобы выстроить последовательность из множества операторов ЕСЛИ и обеспечить их правильную отработку по каждому условию на протяжении всей цепочки. Если при вложении вы допустите в формуле малейшую неточность, она может сработать в 75 % случаев, но вернуть непредвиденные результаты в остальных 25 %. К сожалению, шансов отыскать эти 25 % немного.

Работа с множественными операторами ЕСЛИ может оказаться чрезвычайно трудоемкой, особенно если вы вернетесь к ним через какое-то время и попробуете разобраться, что пытались сделать вы или, и того хуже, кто-то другой.

Если вы видите, что ваш оператор ЕСЛИ все разрастается, устремляясь в бесконечность, значит вам пора отложить мышь и пересмотреть свою стратегию.

Давайте посмотрим, как правильно создавать операторы с несколькими вложенными функциями ЕСЛИ и как понять, когда пора переходить к другим средствам из арсенала Excel.

Примеры

Ниже приведен пример довольно типичного вложенного оператора ЕСЛИ, предназначенного для преобразования тестовых баллов учащихся в их буквенный эквивалент.

97;»A+»;ЕСЛИ(B2>93;»A»;ЕСЛИ(B2>89;»A-«;ЕСЛИ(B2>87;»B+»;ЕСЛИ(B2>83;»B»;ЕСЛИ(B2>79;»B-«;ЕСЛИ(B2>77;»C+»;ЕСЛИ(B2>73;»C»;ЕСЛИ(B2>69;»C-«;ЕСЛИ(B2>57;»D+»;ЕСЛИ(B2>53;»D»;ЕСЛИ(B2>49;»D-«;»F»))))))))))))» xmlns_AntiXSS=»urn:AntiXSSExtensions» />

Этот сложный оператор с вложенными функциями ЕСЛИ следует простой логике:

Если тестовых баллов (в ячейке D2) больше 89, учащийся получает оценку A.

Если тестовых баллов больше 79, учащийся получает оценку B.

Если тестовых баллов больше 69, учащийся получает оценку C.

Если тестовых баллов больше 59, учащийся получает оценку D.

В противном случае учащийся получает оценку F.

Этот частный пример относительно безопасен, поскольку взаимосвязь между тестовыми баллами и буквенными оценками вряд ли будет меняться, так что дополнительных изменений не потребуется. Но что если вам потребуется разделить оценки на A+, A и A– (и т. д.)? Теперь ваши четыре условных оператора ЕСЛИ нужно переписать с учетом 12 условий! Вот так будет выглядеть ваша формула:

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

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

Дополнительные примеры

Ниже приведен распространенный пример расчета комиссионных за продажу в зависимости от уровней дохода.

15000;20%;ЕСЛИ(C9>12500;17,5%;ЕСЛИ(C9>10000;15%;ЕСЛИ(C9>7500;12,5%;ЕСЛИ(C9>5000;10%;0)))))» xmlns_AntiXSS=»urn:AntiXSSExtensions» />

Эта формула означает: ЕСЛИ(ячейка C9 больше 15 000, то вернуть 20 %, ЕСЛИ(ячейка C9 больше 12 500, то вернуть 17,5 % и т. д.

Несмотря на то, что в примере более ранних оценок, эта формула является прекрасным примером того, насколько сложно поддерживать большое количество операторов Если — что вам нужно сделать, если ваша организация решила добавить новые уровни компенсаций и, возможно, даже изменить Существуют ли значения в долларах или процентах? Ты поработал на руках много усилий!

Совет: Чтобы сложные формулы было проще читать, вы можете вставить разрывы строк в строке формул. Просто нажмите клавиши ALT+ВВОД перед текстом, который хотите перенести на другую строку.

Перед вами пример сценария для расчета комиссионных с неправильной логикой:

5000;10%;ЕСЛИ(C9>7500;12,5%;ЕСЛИ(C9>10000;15%;ЕСЛИ(C9>12500;17,5%;ЕСЛИ(C9>15000;20%;0)))))» xmlns_AntiXSS=»urn:AntiXSSExtensions» />

Видите, что это не так? Сравните порядок сравнения доходов с предыдущим примером. Каким образом это происходит? Это правильно, и это не помещается снизу вверх ($5 000 – $15 000), а не наоборот. Но зачем это большая? Это существенная проблема, так как формула не может пройти первое вычисление для любого значения за пределами $5 000. Предположим, что у вас есть $12 500 в доход — инструкция если вернет 10%, так как она больше $5 000, и она прекратит свою работу. Это может быть чрезвычайно проблематичным, так как в некоторых случаях ошибки, возникающие при возникновении отрицательных появления негативного воздействия на эти типы, оказываются незамеченными. Что нужно знать о некоторых серьезных возможностях при использовании сложных вложенных операторов Если. В большинстве случаев вместо создания сложной формулы с помощью функции если вы можете использовать функцию ВПР. Для использования функции ВПРнеобходимо сначала создать ссылочную таблицу.

В этой формуле предлагается найти значение ячейки C2 в диапазоне C5:C17. Если значение найдено, возвращается соответствующее значение из той же строки в столбце D.

Эта формула ищет значение ячейки B9 в диапазоне B2:B22. Если значение найдено, возвращается соответствующее значение из той же строки в столбце C.

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

ФУНКЦИЯ ВПР более подробно описана, но это является намного проще, чем12-уровневая, сложная вложенная инструкция если! Есть и другие, менее очевидные, преимущества:

Таблицы ссылок функции ВПР открыты и их легко увидеть.

Значения в таблицах просто обновлять, и вам не потребуется трогать формулу, если условия изменятся.

Если вы не хотите, чтобы люди видели вашу таблицу ссылок или вмешивались в нее, просто поместите ее на другой лист.

Вы знали?

Теперь есть функция УСЛОВИЯ, которая может заменить несколько вложенных операторов ЕСЛИ. Так, в нашем первом примере оценок с 4 вложенными функциями ЕСЛИ:

можно сделать все гораздо проще с помощью одной функции ЕСЛИМН:

Функция ЕСЛИМН — просто находка! Благодаря ей вам больше не нужно переживать обо всех этих операторах ЕСЛИ и скобках.

Примечание: Эта функция доступна, только если у вас есть подписка на Office 365. Если вы являетесь подписчиком Office 365, убедитесь, что у вас установлена последняя версия Office.

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

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

См. также:

Видео: расширенные функции если
функция СЧЁТЕСЛИ (Office 365, Excel 2016 и более поздние версии)
. функция функции ЭКСПРАСП подсчитывает значения на основеодного условия
. функция СУММЕСЛИ вычислит значения на основенескольких критериев
, которые будут суммироваться с учетомодного условия
,
для которого вычисляется сумма на основе нескольких условийи
функций
VLOOKUP function

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

Сложные формулы в excel примеры

Эта статья будет интересна тем, кто пытается понять работу сложных формул.

В Excel есть инструменты, которые позволяют отследить работу формулы по шагам. Первый из них называется Вычислить формулу и находится на вкладке Формулы — Зависимости формул в версиях Excel, начиная с Excel 2007, и в меню Сервис — Зависимости формул в более ранних версиях. Второй, менее известный, но от этого не менее удобный, — функциональная клавиша F9.

Разберём работу этих инструментов на нескольких примерах.

Пример 1. Дана таблица, содержащая сведения о персонале предприятия. Требуется по введённому табельному номеру определить фамилию сотрудника.

Для решения этой задачи в ячейку H3 ведём табельный номер, а в ячейку I3 формулу =ИНДЕКС($B$2:$B$25;ПОИСКПОЗ(H3;$E$2:$E$25;0))

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

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

Теперь посмотрим, как с этой же формулой поможет разобраться клавиша F9.

Выделим в строке формул ссылку I3, нажмём F9, выделим фрагмент $E$2:$E$37 и снова нажмём F9. Клавиша F9 вычисляет выделенные фрагменты формулы, и мы можем видеть не только результат функции, но и аргументы в виде массивов. Согласитесь, что при таком подходе формула становится «прозрачной», и становится очевиден результат функции ПОИСКПОЗ()

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

Ещё немного потренируемся: выделим фрагмент $B$2:$B$37, нажмём F9, затем выделим функцию ПОИСКПОЗ(I3;$E$2:$E$37;0) и снова F9. Видим массив фамилий, среди которых будет выбрана третья по счёту

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

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

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

Пример 2. На основе таблицы из Примера 1 создать список табельных номеров и фамилий сотрудников одного из отделов, указанного в отдельной ячейке. Формула, решающая эту задачу, выглядит так: =ИНДЕКС($E$2:$E$25;НАИМЕНЬШИЙ(ЕСЛИ($A$2:$A$25=$H$7;СТРОКА($A$1:$A$24));СТРОКА(A1))), причём это — формула массива, которую следует вводить сочетанием клавиш + +

Основой этой формулы является функция ИНДЕКС(), которая позволяет вывести элемент массива по указанному индексу (порядковому номеру). Первым аргументом этой функции указывается диапазон ячеек с табельными номерами. Выделив в формуле фрагмент $E$2:$E$25 и нажав F9, мы увидим значения исходного массива

Порядковый номер для выбора элемента массива вычисляется с помощью функции НАИМЕНЬШИЙ(ЕСЛИ(.

Разобьём эту часть формулы на составляющие. Выделим фрагмент $A$2:$A$25=$H$7 и нажмём F9. Это логическое выражение даёт значение ИСТИНА, если значение ячейки диапазона $A$2:$A$25 равно выбранному названию отдела $H$7, и ЛОЖЬ, если не равно.

Выделим фрагмент СТРОКА($A$1:$A$24) и нажмём F9, получим массив чисел, идущих по порядку от 1 до 24.

Теперь предсказуем результат функции ЕСЛИ() — это массив, в котором значения ИСТИНА заменятся на порядковые номера, а значения ЛОЖЬ останутся на месте. Увидеть это можно, выделив функцию ЕСЛИ целиком с закрывающей скобкой и нажав F9

Далее в действие вступает функция НАИМЕНЬШИЙ(), которая первым аргументом имеет вышеуказанный массив, а вторым — функцию СТРОКА(A1). Обращаем внимание, что во всей формуле это единственная относительная ссылка, которая будет изменяться при копировании формулы по строкам, а именно в первом случае даст 1, на следующей строке 2 и т.д. по порядку. В итоге в ячейке I7 формула, «расшифрованная» с помощью клавиши F9, будет иметь вид

А скопированная в ячейку I8

Если понадобится применить данную формулу для другого диапазона, изменится исходный диапазон в функции ИНДЕКС(), а также изменится верхняя граница диапазона функции СТРОКА(), в то время как нижняя граница остаётся всегда $A$1. Важно, чтобы количество строк исходного диапазона совпадало с количеством строк в функции СТРОКА().

Фамилии в столбец J можно вставить с помощью формулы, разобранной в Примере 1.

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

Создание сложных формул в Microsoft Excel

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

Как создать сложную формулу в Excel

В приведенном ниже примере, мы продемонстрируем, каким образом Excel вычисляет сложные формулы, опираясь на порядок выполнения операций. В данном примере мы хотим вычислить величину налога с продаж за услуги по питанию. Чтобы это осуществить, запишем следующее выражение в ячейке D4: =(D2+D3)*0,075. Эта формула сложит стоимость всех позиций счета, а затем умножит на размер налога с продаж 7,5% (записанный как 0,075).

Excel придерживается порядка действий и сначала складывает значения в скобках: (44.85+39.90)=$84.75. Затем умножает это число на налоговую ставку: $84.75*0.075. Результат вычислений показывает, что налог с продаж составит $6.36.

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

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

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

1. Выделите ячейку, которая будет содержать формулу. В нашем примере мы выбрали ячейку C4.
2. Введите в нее следующее выражение: =B2*C2+B3*C3. Действия в формуле будут выполняться в соответствии с правилами порядка, следовательно, первым идет умножение: 2.29*20=45.80 и 3.49*35=122.15. Затем эти значения будут суммированы для вычисления полной стоимости: 45.80+122.15.
3. Выполните проверку, затем нажмите Enter на клавиатуре. Формула вычислит и отобразит результат. В нашем случае результат вычислений показывает, что полная стоимость заказа составляет $167.95.

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

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