Что определяет результат? Формула, над которой стоит подумать

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

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

Один из них – это оператор ЕСЛИ, который предусматривает выполнение определенных действий при соблюдении конкретных условий. 

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

Функция ЕСЛИ в Excel (общие сведения)

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

  1. Проверить всю колонку А на предмет наличия четных чисел.
  2. Если четное число обнаружено, сложить такие-то значения.
  3. Если четное число не обнаружено, то выдать надпись «не обнаружено».
  4. Проверить получившееся число, является ли оно четным. 
  5. Если да, то сложить его со всеми четными числами, отобранными в пункте 1.

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

Синтаксис функции ЕСЛИ с одним условием

Любая функция в Ecxel выполняется с помощью формулы. Шаблон, по которому нужно передавать функции данные, называется синтаксисом. В случае с оператором ЕСЛИ, формула будет в таком формате.

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

Давайте рассмотрим синтаксис более подробно:

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

Вот пример для наглядности.

Что определяет результат? Формула, над которой стоит подумать1

Здесь функция осуществляет сравнение ячейки А1 с числом 20. Это первый пункт синтаксиса. Если содержимое оказывается больше этого значения, в ячейку, где формула была прописана, выводится значение «больше 20». Если же этому условию ситуация не соответствует – «меньше или равно 20».

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

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

Что определяет результат? Формула, над которой стоит подумать2

Поскольку нам нужно проверить текст, а не число, в первом аргументе указывается B2=”зач.”.

Синтаксис функции ЕСЛИ с несколькими условиями

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

Чтобы было более наглядно, вот синтаксис.

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

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

Вот пример.

Что определяет результат? Формула, над которой стоит подумать3

А с помощью такой формулы (показана на скриншоте ниже) можно сделать анализ успеваемости каждого студента.

Что определяет результат? Формула, над которой стоит подумать4

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

Как расширить функционал ЕСЛИ, используя операторы “И” и “ИЛИ”

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

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

Давайте более детально рассмотрим эти критерии.

Функция ЕСЛИ с условием «И»

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

Функция ЕСЛИ с условием «ИЛИ»

Функция ИЛИ работает аналогичным образом, но в этом случае достаточно истинности только одного из условий. Максимально так можно осуществить проверку до 30 условий. 

Вот варианты, как можно применять функции И и ИЛИ как аргумент функции ЕСЛИ.

Что определяет результат? Формула, над которой стоит подумать5
Что определяет результат? Формула, над которой стоит подумать6

Сравнение данных в двух таблицах

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

Для сравнения двух таблиц используется функция СЧЕТЕСЛИ. Давайте рассмотрим ее более подробно.

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

Наша таблица выглядит так.

Что определяет результат? Формула, над которой стоит подумать7

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

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

Что определяет результат? Формула, над которой стоит подумать8

В виде формулы для форматирования записываем функцию =СЧЕТЕСЛИ (сравниваемый диапазон; первая ячейка первой таблицы)=0. В качестве сравниваемого диапазона используется таблица с особенностями второго кухонного комбайна.

Что определяет результат? Формула, над которой стоит подумать9

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

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

Что определяет результат? Формула, над которой стоит подумать10

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

Функция СУММЕСЛИ в Excel

А теперь перейдем к разновидностям функции ЕСЛИ, которые помогут выполнить заменить сразу два пункта алгоритма. Первая из них – СУММЕСЛИ, которая складывает два числа, которые соответствуют определенному условию. Например, перед нами стоит задача определить, сколько денег нужно платить в месяц всем продавцам. Для этого необходимо.

  1. Добавить ряд с общими доходами всех продавцов и нажать по ячейке, в которой будет находиться результат после ввода формулы. 
  2. Находим кнопку fx, которая находится рядом со строчкой для формул. Далее появится окошко, где посредством поиска можно отыскать необходимую функцию. После выбора оператора нужно нажать кнопку «ОК». Но всегда возможен ручной ввод.
    11
  3. Далее появится окно ввода аргументов функции.  Все значения можно указать в соответствующих полях, а диапазон ввести через кнопку рядом с ними.
    12
  4. Первый аргумент – диапазон. Сюда вводятся ячейки, которые нужно проверять на предмет соответствия критериям. Если говорить про нас – это должности работников. Вводим диапазон D4:D18. Или же просто осуществить выделение интересующих ячеек.
  5. В поле «Критерий» следует ввести должность. В случае с нами – «продавец». Как диапазон суммирования, указываем те ячейки, где перечислены заработные платы сотрудников (это делается как вручную, так и выделить их с помощью мыши). Нажимаем «ОК», и получаем готовую посчитанную заработную плату всех сотрудников, являющихся продавцами.

Согласитесь, что это очень удобно. Не так ли?

Функция СУММЕСЛИМН в Excel

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

Читайте также:  Как заставить отдел продаж работать на полную мощность в кризис

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

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

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

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

Рассмотрим главные аргументы более подробно:

  1. Диапазон суммирования. Ячейки, которые будут суммироваться.
  2. Диапазон условия 1 – диапазон, который будет проверяться на предмет соответствия определенному критерию. 
  3. Условие 1 – непосредственно условие.
  4. Диапазон условия 2 – второй диапазон, который будет проверяться на предмет соответствия критерию.
  5. Условие 2 – второе условие.

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

13

Функция СЧЁТЕСЛИ в Excel

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

  1. Сначала добавляем строку, где приводится количество продавцов. После этого нужно нажать по ячейке, где будет выводиться результат.
  2. После этого нужно нажать на кнопку «Вставить функцию», которую можно найти во вкладке «Формулы». Появится окно, где есть перечень категорий. Нам нужно выбрать пункт «Полный алфавитный перечень». В списке нас интересует формула СЧЕТЕСЛИ. После того, как мы ее выберем, нужно нажать кнопку «ОК».
    14
  3. После этого у нас появляется количество продавцов, трудоустроенных в этой организации. Оно было получено методом подсчета количества ячеек, в которых написано слово «продавец». Все просто. 

Функция СЧЕТЕСЛИМН в Excel

Аналогично формуле СУММЕСЛИМН, эта формула считает количество ячеек, которые подпадают под несколько условий. Синтаксис похож, но немного отличается от формулы СУММЕСЛИМН:

  1. Диапазон условия 1. Это диапазон, который будет проверяться на соответствие первому критерию.
  2. Условие 1. Непосредственно первый критерий.
  3. Диапазон условия 2. Это диапазон, который будет проверяться на соответствие второму критерию. 
  4. Условие 2.
  5. Диапазон условия 3.

И так далее.

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

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

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

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

Оцените качество статьи. Нам важно ваше мнение:

Намерение, действие, результат. Формула успеха

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

Кажется, что такого необычного есть в этом наборе? Любой человек, желая чего-то сделать, сначала ставит намерение, потом действует и в итоге получает какой-то результат. Все просто и вполне обыденно. Однако у этого правила есть одна интересная тонкость. В книге «Хакеры сновидений» А. Реутов хорошо описал суть работы этой троицы.

Две сильных и одна слабая

Намерение – Действие – Результат. Все это представляет собой равносторонний треугольник. Однако в действительности, одна сторона этого треугольника всегда остается слабой. То есть одна сторона сильная, вторая сильная, а третья слабая. Есть варианты такого проявления:

1.Это может быть сильное намерение,  сильная работа и в итоге слабый результат

Ситуация описывается фразой «Гора родила мышь». А Реутов приводит в пример инженера, который имеет сильное намерение создать некую конструкцию. И он начинает рисовать всевозможные чертежи.

Просчитывать варианты, прикидывать возможности. Тратится на это много времени, а как итог, весь его потенциал на этом и заканчивается. К результату он не привел.

Здесь мы имеем сильное намерение, сильное действие и слабый результат.

2.Слабое намерение, сильное действие и сильный результат

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

Что значит слабое намерение? Это не равнодушие и не безразличие. Слабое намерение – это очень тонкое намерение, практически незаметное. Когда человек знает, чего хочет и просто начинает работать над достижением цели. Подобный закон очень гармоничен.

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

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

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

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

3.Сильное намерение, слабое действие и сильный результат

И именно такая раскладка, по мнению А.Реутова, является главной для магов, для тех, кто осознанно подходит к своей жизни и сам управляет ее проявлениями.

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

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

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

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

Люди это называют совпадениям или случайностью. Однако на самом деле они неосознанно включили магический механизм реализации задуманного.

***

Намерение – Действие — Результат. Объяснить эту формулу можно и на примере из физики. Если все элементы будут равными, то тогда откуда течь силе? Сила берется из разницы потенциалов. Имея один элемент слабым, мы создаем разницу потенциалов, которая позволяет энергии течь, приводя формулу в движение. 

Читайте также:  Как «раздавать» публичный wi-fi клиентам так, чтобы бизнес не пострадал

Хотите, что бы ваша цель была достигнута? Выбирайте правильную формулу – либо сильное действие, либо сильное намерение. Одно из двух. И тогда результат будет непременно хорошим!

Функции Excel ЕСЛИ (IF) и ЕСЛИМН (IFS) для нескольких условий

Логическая функция ЕСЛИ в Экселе – одна из самых востребованных. Она возвращает результат (значение или другую формулу) в зависимости от условия.

Функция ЕСЛИ в Excel

Функция имеет следующий синтаксис.

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

лог_выражение – это проверяемое условие. Например, A230) не выполняется и возвращается альтернативное значение, указанное в третьем поле. В этом вся суть функции ЕСЛИ. Протягивая расчет вниз, получаем результат по каждому товару.

Однако это был демонстрационный пример. Чаще формулу Эксель ЕСЛИ используют для более сложных проверок. Допустим, есть средненедельные продажи товаров и их остатки на текущий момент. Закупщику нужно сделать прогноз остатков через 2 недели. Для этого нужно от текущих запасов отнять удвоенные средненедельные продажи.

Пока все логично, но смущают минусы. Разве бывают отрицательные остатки? Нет, конечно. Запасы не могут быть ниже нуля. Чтобы прогноз был корректным, нужно отрицательные значения заменить нулями. Здесь отлично поможет формула ЕСЛИ.

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

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

В прогнозе запасов больше нет отрицательных значений, что в целом очень неплохо.

Формулы Excel ЕСЛИ также активно используют в формулах массивов. Здесь мы не будем далеко углубляться.

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

в Excel 2016 появились функции МИНЕСЛИ и МАКСЕСЛИ. Но для примера очень полезно ознакомиться – пригодится в другой ситуации.

Формула ЕСЛИ в Excel – примеры нескольких условий

Довольно часто количество возможных условий не 2 (проверяемое и альтернативное), а 3, 4 и более. В этом случае также можно использовать функцию ЕСЛИ, но теперь ее придется вкладывать друг в друга, указывая все условия по очереди. Рассмотрим следующий пример.

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

Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура.

Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим.

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

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

Подбор (подгонка) результатов расчёта под нужные значения

Вы когда-нибудь подбирали входные значения в вашем расчёте Excel, чтобы получить на выходе нужный результат? В такие моменты чувствуешь себя матёрым артиллеристом, правда? Всего-то пара десятков итераций «недолёт — перелёт», и вот оно, долгожданное «попадание»!

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

Для этого нажмите на вкладке «Вставка» кнопку «Анализ „что если“» и выберите команду «Подбор параметра» (Insert — What If Analysis — Goal Seek). В появившемся окне задайте ячейку, где хотите подобрать нужное значение, желаемый результат и входную ячейку, которая должна измениться.

После нажатия на «ОК» Excel выполнит до 100 «выстрелов», чтобы подобрать требуемый вами итог с точностью до 0,001.

  • Эта функция позволяет подобрать такое значение ячейки, при котором формула, ссылающаяся на эту ячейку, будет принимать нужное значение.
  • Например, если есть формула в ячейке А1 вида: =A2+B2*C2-D2^3, то с помощью подбора параметра можно задать в ячейке C2 такое значение, при котором А1 будет возвращать 100.

Ячейка А1 обязательно должна содержать формулу, ссылающуюся на С2

ПОДБОР ПАРАМЕТРА В EXCEL И ПРИМЕРЫ ЕГО ИСПОЛЬЗОВАНИЯ

«Подбор параметра» — ограниченный по функционалу вариант надстройки «Поиск решения». Это часть блока задач инструмента «Анализ «Что-Если»».

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

ГДЕ НАХОДИТСЯ «ПОДБОР ПАРАМЕТРА» В EXCEL

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

Необходимо подобрать процентную ставку по займу, если известна сумма и срок. Заполняем таблицу входными данными.

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

Когда условия задачи записаны, переходим на вкладку «Данные». «Работа с данными» — «Анализ «Что-Если»» — «Подбор параметра».

В поле «Установить в ячейке» задаем ссылку на ячейку с расчетной формулой (B4). Поле «Значение» предназначено для введения желаемого результата формулы. В нашем примере это сумма ежемесячных платежей.

Допустим, -5 000 (чтобы формула работала правильно, ставим знак «минус», ведь эти деньги будут отдаваться). В поле «Изменяя значение ячейки» — абсолютная ссылка на ячейку с искомым параметром ($B$3).

После нажатия ОК на экране появится окно результата.

Чтобы сохранить, нажимаем ОК или ВВОД.

Функция «Подбор параметра» изменяет значение в ячейке В3 до тех пор, пока не получит заданный пользователем результат формулы, записанной в ячейке В4. Команда выдает только одно решение задачи.

РЕШЕНИЕ УРАВНЕНИЙ МЕТОДОМ «ПОДБОРА ПАРАМЕТРОВ» В EXCEL

Функция «Подбор параметра» идеально подходит для решения уравнений с одним неизвестным. Возьмем для примера выражение: 20 * х – 20 / х = 25. Аргумент х – искомый параметр. Пусть функция поможет решить уравнение подбором параметра и отобразит найденное значение в ячейке Е2.

В ячейку Е3 введем формулу: = 20 * Е2 – 20 / Е2.

А в ячейку Е2 поставим любое число, которое находится в области определения функции. Пусть это будет 2.

  1. Запускам инструмент и заполняем поля:
  2. «Установить в ячейке» — Е3 (ячейка с формулой);
  3. «Значение» — 25 (результат уравнения);
  4. «Изменяя значение ячейки» — $Е$2 (ячейка, назначенная для аргумента х).

Результат функции:

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

Решение уравнения: х = 1,80.

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

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

ПРИМЕРЫ ПОДБОРА ПАРАМЕТРА В EXCEL

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

Пример 1. Метод подбора начальной суммы инвестиций (вклада).

Известные параметры:

  • срок – 10 лет;
  • доходность – 10%;
  • коэффициент наращения – расчетная величина;
  • сумма выплат в конце срока – желаемая цифра (500 000 рублей).

Внесем входные данные в таблицу:

Читайте также:  Как правильно ставить грандиозные цели и не бояться их достигать?

Начальные инвестиции – искомая величина. В ячейке В4 (коэффициент наращения) – формула =(1+B3)^B2.

Вызываем окно команды «Подбор параметра». Заполняем поля:

После выполнения команды Excel выдает результат:

Чтобы через 10 лет получить 500 000 рублей при 10% годовых, требуется внести 192 772 рубля.

Пример 2. Рассчитаем возможную прибавку к пенсии по старости за счет участия в государственной программе софинансирования.

Входные данные:

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

С какого возраста необходимо уплачивать по 1000 рублей в качестве дополнительных страховых взносов, чтобы получить прибавку к пенсии в 2000 рублей:

  • Ячейка с формулой расчета прибавки к пенсии активна – вызываем команду «Подбор параметра». Заполняем поля в открывшемся меню.
  • Нажимаем ОК – получаем результат подбора.

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

Функция «Подбор параметра» работает правильно, если:

  • значение желаемого результата выражено формулой;
  • все формулы написаны полностью и без ошибок.

Пошаговый анализ примера

Рассмотрим предыдущий пример шаг за шагом.

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

Подготовка листа

  • Откройте новый пустой лист.
  • Прежде всего добавьте в первый столбец эти подписи, чтобы сделать данные на листе понятнее.
  • В ячейку A1 введите текст Сумма займа.
  • В ячейку A2 введите текст Срок в месяцах.
  • В ячейку A3 введите текст Процентная ставка.
  • В ячейку A4 введите текст Платеж.
  • Затем добавьте известные вам значения.
  • В ячейку B1 введите значение 100 000. Это сумма займа.
  • В ячейку B2 введите значение 180. Это число месяцев, за которое требуется выплатить ссуду.Примечание: Хотя вам известна необходимая сумма платежа, не вводите ее как значение, поскольку она получается в результате вычисления формулы. Вместо этого добавьте формулу на лист и укажите значение платежа на более позднем этапе при использовании средства подбора параметров.
  • Теперь добавьте формулу, результат которой вас интересует. Например, используйте функцию ПЛТ.
  • В ячейке B4 введите =ПЛТ(B3/12;B2;B1). Эта формула вычисляет сумму платежа. В данном примере вы хотите ежемесячно выплачивать 900 ₽. Это значение здесь не вводится, поскольку вам нужно определить процентную ставку с помощью средства подбора параметров, а для этого требуется формула.Формула ссылается на ячейки B1 и B2, значения которых вы указали на предыдущих этапах. Она также ссылается на ячейку B3, в которую средство подбора параметров поместит процентную ставку. Формула делит значение из ячейки B3 на 12, поскольку был указан ежемесячный платеж, а функция ПЛТ предусматривает использование годовой процентной ставки.Поскольку в ячейке B3 нет значения, Excel полагает процентную ставку равной 0 % и в соответствии со значениями из данного примера возвращает сумму платежа 555,56 ₽. Пока вы можете игнорировать это значение.

Использование средства подбора параметров для определения процентной ставки

  • На вкладке Данные в группе Работа с данными нажмите кнопку Анализ «что если» и выберите команду Подбор параметра.
  • В поле Установить в ячейке введите ссылку на ячейку, в которой находится нужная формула. В данном примере это ячейка B4.
  • В поле Значение введите нужный результат формулы. В данном примере это -900. Обратите внимание, что число отрицательное, так как представляет собой платеж.
  • В поле Изменяя значение ячейки введите ссылку на ячейку, в которой находится корректируемое значение. В данном примере это ячейка B3.Примечание:  Формула в ячейке, указанной в поле Установить в ячейке, должна ссылаться на ячейку, которую изменяет средство подбора параметров.

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

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

ТРИЗ. Идеальность системы. Идеальный конечный результат (ИКР) + примеры задач

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

Есть такое выражение: «Прежде чем построить дом для себя, нужно выстроить минимум три дома для других». И это более чем верно. Повторяя действия, мы учитываем ошибки, промахи, и стараемся не допускать их вновь.

Идеальность системы ТРИЗ заключается в том, чтобы миновать строительство трех предыдущих домов и возвести первый, не допуская ошибок. Благодаря разработанным алгоритмам вы получаете идеальный конечный результат (ИКР).

Формула идеальности в ТРИЗ звучит так: системы нет, а ее функция выполняется. Например, контролёра нет, а функция проверки билетов выполняется.

Говоря простым языком это такие манипуляции, благодаря которым система сама, используя существующие ресурсы, решает поставленную задачу. Здесь прекрасно подойдет пословица «Лень – двигатель прогресса».

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

— система сама решает поставленную задачу;

— задачи решаются в отсутствии системы;

— необходимости в задаче нет.

То есть в итоге мы получаем результат без затрат.

Результативность ТРИЗ зависит от правильно поставленных вопросов и, вполне естественно, найденных на них всех возможных вариантов ответов. Здесь хорошо играет свою роль принцип – если нет, то.

Например, при усовершенствовании работы сайта бассейна следуют такие вопросы:

— Каждый посетитель сайта заинтересован в плавании. Если нет, то…

— Каждый посетитель сайта записывается на пробное занятие. Если нет, то…

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

И так далее. Постановка вопроса «Если нет, то…» заставляет искать решения, которые побудят посетителя сделать нужные нам действия.

Основатель теории ТРИЗ Генрих Альтшуллер выделял пять этапов решения любой задачи:

— правильная постановка самой задачи;

— предвидение идеального конечного результата;

— выявление противоречий, которые мешают прийти к желаемому;

— выявление причины противоречий, почему именно они мешают получить результат;

— выявление условий, при которых противоречие снимается.

Задача – настроить ведение отчетности посещаемости бассейна, учета медицинских осмотров и продаж бара в одном отчете.

ИКР – бухгалтер в одном отчете видит все произведенные операции за сутки.

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

Причина противоречия – различаются языки программирования.

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

Можно много спорить от том, стоит, или не стоит заниматься изучением ТРИЗ. Подойдут ли предлагаемые методы в конкретной ситуации. А можно взять и попробовать.

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

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

Оригинал статьи размещен здесь: http://blagih.ru/triz._idealnost_sistemy/

ТРИЗ Алексей Благих

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *