10 основных функций Microsoft Excel для анализа данных

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

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

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

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

Функция ЕСЛИ в Excel | MS Office | Учебные статьи

Допустим, имеем уравнение: 15x+18x=46. Записываем его левую часть, как формулу, в одну из ячеек. Как и для любой формулы в Экселе, перед уравнением ставим знак «=». Но, при этом, вместо знака x устанавливаем адрес ячейки, куда будет выводиться результат искомого значения.

Использование функции

В Microsoft Excel подобные задачи решаются с помощью стандартного меню. Подбор параметра в Excel 2007, 2010, 2013 и новее находится во вкладке «Данные», в группе «Работы с данными».

Третий шаг это подтверждение операции и вывод результата.

Офисный пакет приложений помогает выполнять множество задач в разных областях. Так, MS Excel, являющийся табличным процессором, способен решить разнообразные задачи. Достаточно ввести функцию, и вот он – результат. Как правило, это приложение используют в бухгалтерском учете, но и не только. А во всем многообразии функция «ЕСЛИ» занимает далеко не последнее место.

Функции программы Microsoft Excel: подбор параметра

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

Определение процентной ставки

Рассмотрим теперь обратную задачу. Банк выдает ссуду в 100 тыс. рублей на 2 года и хочет получить доход в 10 тыс. рублей. Какую минимальную процентную ставку нужно установить для получения такой прибыли?

К уже имеющейся на листе информации добавляем строку »Прибыль». Она рассчитывается по формуле:

Устанавливаем срок 24 месяца. Обратите внимание на поле «Ставка». Числовое значение должно выражаться в процентах. Для этого выберите числовой формат «Процент» в Excel: вкладка «Главная» — панель инструментов «Число» — кнопка с изображением процента.

Вызываем функцию подбора и задаем ее аргументы. Ожидаемый результат будет записан в поле «Прибыль» изменением значения в ячейке «Ставка» и составлять 10 000 рублей. После запуска программа показывает необходимый процент, равный 9,32354423334073 %.

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

Функция Подбор параметра в Эксель. Анализ что если — RUUD

• Ставка – проценты по займу.
• Кпер – число оплат (для годового кредита в случае ежемесячной оплаты это 12 раз).
• ПС – первоначальная сумма.
• БС – будущая стоимость (если вы намерены выплатить не всю сумму, а лишь ее часть, здесь указывается, какой долг должен остаться). Это необязательный аргумент, по умолчанию он равен 0.
• Тип – когда производится оплата – в начале месяца или в конце. Этот параметр не обязательно указывать, если он не заполнен, принимается равным 0, что означает оплату в конце месяца.

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

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

1. Установить в ячейке – место, куда необходимо вставить ссылку на формулу.
2. Значение – сюда вводится числовое значение, которое необходимо получить в ходе расчетов.
3. Изменяя значение ячейки – ссылка на число, которое и будет решением задачи.

Важно! Отметим несколько моментов, на которые стоит обратить внимание при работе с этим инструментом:

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

Найти решение уравнения с одной неизвестной 2*x^2 — x/3=12

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

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

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

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

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

Рассчитать процентную ставку по кредиту в 10000$ сроком на два с половиной года.

Чтобы посчитать сумму платежа, воспользуемся встроенной функцией excel – ПЛТ. Она состоит из процентной ставки, периода выплат и величины кредита. Значением процента задаемся произвольно.

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

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

Жми «Нравится» и получай только лучшие посты в Facebook ↓

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

Функция ЕСЛИ в Excel с примерами.

• ежемесячные отчисления – 1000 руб.;
• период уплаты дополнительных страховых взносов – расчетная величина (пенсионный возраст (в примере – для мужчины) минус возраст участника программы на момент вступления);
• пенсионные накопления – расчетная величина (накопленная за период участником сумма, увеличенная государством в 2 раза);
• ожидаемый период выплаты трудовой пенсии – 228 мес.;
• желаемая прибавка к пенсии – 2000 руб.

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

Формула «ЕСЛИ». Тонкости ввода нескольких условий

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

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

Второй способ: вызвать функцию через меню или панель инструментов. В меню необходимо зайти в пункт «Вставка» – «Функции». Уже здесь в категории «Логические» находится функция «ЕСЛИ» в Excel, с несколькими условиями или одним параметром. Открывается диалоговое окно, в котором вводятся необходимые показатели для получения результата.

Если условие истинно, тогда выполняется первое указанное выражение; если же ложно – тогда результатом станет второй параметр.

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

Подбор параметра в Excel 2007, 2010, 2016 – где находится и где найти функцию, анализ «что-если», примеры использования инструмента

• финансовые: ПРОЦПЛАТ, ПУО, ПС, СТАВКА, ЧПС и т.д.;

• даты и времени – ВРЕМЯ, ДЕНЬНЕД, ГОД и т.д.;

• математические – ABS, ATAN, LOG, LN, SIN и т.д.;

• статистические – ДИСП, ГИПЕРГЕОМЕТ, КВАРТИЛЬ и т.д.;

• ссылок и массивов – АДРЕС, ВЫБОР, ОБЛАСТИ, ПОИСКПОЗ, ЧСТРОК и т.д.;

• баз данных – БДДИСП, БСЧЁТА, БДСУММ, ДСРЗНАЧ и т.д.;

• текстовые – ЗАМЕНИТЬ, НАЙТИ, ПОВТОР, ПОДСТАВИТЬ, ФИКСИРОВАННЫЙ и т.д.;

• логические – ЕСЛИ, ИСТИНА, НЕ, ЛОЖЬ и т.д.;

• проверки свойств и значений – ЕОШИБКА, ИНФОРМ, ЕЧИСЛО, ЯЧЕЙКА и т.д.

Допустим, имеем уравнение: 15x+18x=46. Записываем его левую часть, как формулу, в одну из ячеек. Как и для любой формулы в Экселе, перед уравнением ставим знак «=». Но, при этом, вместо знака x устанавливаем адрес ячейки, куда будет выводиться результат искомого значения.

Синтаксис функции ЕСЛИ и виды логических выражений

Синтаксис функции ЕСЛИ предлагает нам ввести 3 аргумента, первым из которых является само логическое условие, а вторые два задают варианты результата, то есть что мы будем вставлять в ячейку. Напоминаем как именно работает логическое условие (не обязательно в Excel — где угодно).

Что такое логическое выражение:

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

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

• 1 — В Excel в качестве оператора сравнения используется знак равенства «=», причём именно одиночный, а не двойной или тройной, как принято в некоторых языках программирования.
• 2 — выражение «ЛОЖЬ» в Excel применяется для обозначения случая, когда выражение неверно (в данном случае 1 не может быть равно 2, поэтому результат сравнения отрицательный). Выражение «ЛОЖЬ» не является ошибкой — это именно результат логического выражения, просто его так принято обозначать в Эксель.
• 3 — В Excel результат «ИСТИНА» обозначает что выражение верно (в данном случае 15 равно 15, так как число всегда равно самому себе). В реальных случаях сравнивать константы бессмысленно, поэтому по обе стороны знака равенства обычно стоят какие-то математические или текстовые выражения, часто представляющие собой результат обработки других данных при помощи функций.
• 4 — В данном случае выполняется сравнение двух строк. Если они совпадают, то выражение верно.
• 5 — Выражение «не равно» в Excel обозначается именно таким образом, как показано в примере.

В приведённых примерах показаны варианты сравнения. Таким образом, можно использовать четыре основных оператора:

• «=» — знак равенства (выполняется («ИСТИНА»), если аргументы совпадают);
• «» — знак неравенства (выполняется, если аргументы НЕ совпадают);
• «>» — больше (выполняется, если аргумент слева больше, чем аргумент справа);
• «

Также есть комбинированные операторы, про которые почему-то часто забывают, поэтому выделим их отдельно:

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

Сам синтаксис у формулы ЕСЛИ выглядит так:
ЕСЛИ(лог_выражение, [значение_если_истина], [значение_если_ложь]).

• «лог_выражение»
  Это собственно само логическое выражение, то есть сравнение чего-то с чем-то как было указано выше. В результате выполнения сравнения получается логический результат «ИСТИНА» или «ЛОЖЬ» и в зависимости от этого показывается разный результат в ячейке (см. следующие 2 аргумента). Логическое выражение в функции ЕСЛИ является обязательным, пропустить этот аргумент нельзя.
• «значение_если_истина»
  То что нужно вставить в ячейку, если результатом выполнения логического выражения (сравнения) является «ИСТИНА». Это может быть число, строка, переменная или другая функция.
• «значение_если_ложь»
  То что нужно вставить в ячейку, если результат сравнения отрицательный («ЛОЖЬ»). Как и предыдущий аргумент, это может быть число, текстовая строка, другая формула Excel или переменная.

Не обязательные аргументы функций в Excel

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

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

Если в функции ЕСЛИ не указан какой-то необязательный аргумент, то в качестве результата выполнения логического выражения Excel будет применять стандартные значения: «ИСТИНА» вместо аргумента 2 и «ЛОЖЬ» вместо аргумента 3. Таким образом, простейшей записью для функции ЕСЛИ является выражение вида «ЕСЛИ(лог_выражение;;)» (обратите внимание на символы «;»).

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

Типичные ошибки при вводе формул

Допустим у нас есть столбец с числами и нужно сравнить каждое число с константой (пусть это будет число 30). Для каждого из чисел в столбце у нас будут два варианта результата в зависимости от сравнения:

Ячейка	Число	Формула	Результат
A5 1	10	=ЕСЛИ(A5>30;»Больше 30″;»Меньше или равно 30″)	«Меньше или равно 30»
A6	20	=ЕСЛИ(A6>30;»Больше 30″;»Меньше или равно 30″)	«Меньше или равно 30»
A7	30	=ЕСЛИ(A7>30;»Больше 30″;»Меньше или равно 30″)	«Меньше или равно 30»
A8	40	=ЕСЛИ(A8>30;»Больше 30″;»Меньше или равно 30″)	«Больше 30»
A9	50	=ЕСЛИ(A9>30;»Больше 30″;»Меньше или равно 30″)	«Больше 30»

Заключение

В данной статье были рассмотрены различные способы применения функции «ЕСЛИ»:

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

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

Общие сведения об электронной таблице MS Excel

1. Теперь в этой строке будет написано новое логическое выражение. То есть у нас будет новое вложенное условие. Для начала введите условие «Если значение ячейки С1 больше 1000». Для этого вводим следующий код. Обязательно в конце поставьте символ точки с запятой.

Вызываем функцию подбора и задаем ее аргументы. Ожидаемый результат будет записан в поле «Прибыль» изменением значения в ячейке «Ставка» и составлять 10 000 рублей. После запуска программа показывает необходимый процент, равный 9,32354423334073 %.