Метод поиска решения в excel

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

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

В окне параметров кликаем по пункту «Надстройки». После перехода, в нижней части окна, напротив параметра «Управление» выбираем значение «Надстройки Excel», и кликаем по кнопке «Перейти».

Открывается окно с надстройками. Ставим галочку напротив наименования нужной нам надстройки – «Поиск решения». Жмем на кнопку «OK».

После этого, кнопка для запуска функции Поиска решений появится на ленте Excel во вкладке «Данные».

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

Функция ВПР (VLOOKUP) в Excel для чайников.

• На вкладке Файл выберите команду Параметры, а затем — категорию Надстройки;
• В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти;
• В поле Доступные надстройки установите флажок рядом с пунктом Поиск решения и нажмите кнопку ОК.

Финансовые функции ПЛТ() , ОСПЛТ() , ПРПЛТ() , КПЕР() , СТАВКА() , БС() , ПС() , а также ОБЩДОХОД() и ОБЩПЛАТ() тесно связаны между собой, т.к. все они вычисляют параметры аннуитета и, соответственно, используют один и тот же набор аргументов. В этом можно убедиться, перечислив все функции вместе с аргументами:

Функция ПЛТ в Excel

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

Действительно в Excel есть минимально необходимый набор функций. Например, ПЛТ (платёж). То есть мы должны узнать сумму кредита и минусовать с неё платёж первого периода, считать процент, минусовать процент следующего платежа и т.д. Условие одно — платежи должны быть равными.

Давайте попробуем воспользоваться данной функцией. Построим небольшую таблицу:

Аргументов много (в принципе каждый аргумент ПЛТ это отдельная функция):

Ставка — это ставка для периода (если ставка квартальная то 13% я делю на 4 квартала, если ставка месячная то 13% делим на 12 и т.д), в нашем случае берём именно второй вариант.

Пс — текущая стоимость займа (в нашем случае 700000 рублей).

Тип — принимает значения 0 или 1 в зависимости от платежа вначале или в в конце периода (в конце 0, в начале 1).

В итоге получим. Оставим «Бс» и «Тип» пустыми, они примут значение 0, он то нам и нужен!

Результат со знаком минус — мы теряем эти деньги. Если хочется видеть положительную сумму — сумму кредита нужно ввести со знаком минус (-700000).

Результат налицо! Это будет наш ежемесячный платёж. Нетрудно посчитать, что за весь период мы выплатим банку 750365,12 рублей.

Идём дальше, давайте проведём небольшой анализ по процентной ставке и сроку кредита. Возьмём ставки — 13%, 15%, 19% и 25%. Периоды кредитования — 12, 24, 36, 48 и 60 месяцев.

Из формул массивов мы знаем, что можно умножать диапазон на диапазон, но нам также нужно учесть и первоначальную сумму кредита. Поэтому воспользуемся возможностью программы «Анализ что если?». Предварительно выделим всю таблицу данных (от А8 до F12):

Теперь нужно указать куда (в какие ячейки подставлять) наши показания по количеству месяцев (столбцы) и процентную ставку (строки). Укажем соответствующие ячейки — B4 и B5. Нажимаем «ОК»

Как видно из строки формул — появились фигурные скобки (признак массива) и функция ТАБЛИЦА. Не ищите её просто так, она появится только при использовании «Таблицы данных» из «Анализ «что если?».

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

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

Функция ВПР в Excel. Как использовать? ЭКСЕЛЬ ХАК

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

Проверка ошибок

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

• Текст вместо чисел
• Отрицательные числа там, где их быть не может
• Числа с дробной частью там, где должны быть целые
• Текст вместо даты
• Разные варианты написания одного и того же значения. Например, сокращения («ЭБ» вместо «Электронная библиотека»), лишние пробелы в конце текстового значения или между словами — всего этого достаточно, чтобы превратить текстовые значения в разные и, соответственно, чтобы они обрабатывались Excel некорректно.

Инструмент проверки данных

Чтобы использовать инструмент проверки данных, нужно выделить ячейки, к которым хотите его применить, выбрать на ленте «Данные» → «Проверка данных» и настроить параметры проверки в диалоговом окне:

Если в графе «Сообщение об ошибке» вы выбрали вариант «Остановка», то после проверки в ячейки нельзя будет ввести значения, не соответствующие заданному правилу.

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

Еще неверные данные можно обвести, чтобы точно увидеть, где есть ошибки:

Удаление пробелов

Для удаления лишних пробелов (в начале, в конце и всех кроме одного между слов) используйте функцию СЖПРОБЕЛЫ / TRIM. Ее единственный аргумент — текст (ссылка на ячейку с текстом, как правило).

Если после очистки данных функцией СЖПРОБЕЛЫ или другой обработки вам не нужен исходный столбец, вставьте данные, полученные в отдельном столбце с помощью функций, как значения на место исходных данных, а столбец с формулой удалите:

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

Магия Excel: 10 самых полезных «фишек» для работы с таблицами — БизнесБизнес

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

Персональный сайт учителя информатики

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

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

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

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

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

В ячейку D7 вводим формулу периодических постоянных выплат по займу при условии, что сумму необходимо погасить в течении срока займа: = ПЛТ (C4/12;C3*12;C2)

Процентную ставку делим на 12 в случае ежемесячных платежей и формат ячейки выбираем процентный – процентная ставка в этом случае записывается т.о.: 12% – 0,0125 – формат ячейки – процентный.

Кпер – число периодов выплат. Если период в годах, то для вычисления ежемесячных выплат умножаем на 12.

Пс – указываем сумму, которую берем взаймы (в нашем случае – это 100000).

Бс и Тип – необязательные параметры. Бс – будущая стоимость или баланс наличности, который нужно достичь после последней выплаты; принимается равной 0, если значение не указано. Тип – логическое значение (0 или 1), обозначающее, должна ли производится выплата в конце периода или в начале периода.

Выделяем диапазон ячеек, содержащий значения процентных ставок и формулы для расчета – C7:D18.

Выполните команду Данные – Анализ “что если” – Таблица данных. На экране появится диалоговое окно Таблица данных. (см.рис). Это окно используется для задания рабочей ячейки, на которую ссылается формула расчета. В нашем примере это ячейка С4, которую необходимо указать в поле Подставлять значения по строкам в:.

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

Допустим, что вам захотелось определить, какая часть платежа идет на погашение процента по кредиту, а какая – проценты по кредиту. Для этого в следующий столбец, в ячейку Е7 необходимо ввести формулу: = ПРОЦПЛАТ (C4/12;1;C3*12;C2) (см.рис).

Затем опять выполните команду Данные – Анализ “что если” – Таблица данных, предварительно выделив необходимый диапазон ячеек. После нажатия кнопки ОКпоявляется таблица Плата по процентам за 1 мес. (см.рис). Если вас не испугают эти цифры, то можете смело отправляться в банк за ссудой.

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

Применение функций ПЛТ (ППЛАТ) и ПРОЦПЛАТ (ПЛПРОЦ) в табличном процессоре MS Excel 2010 | Персональный сайт учителя информатики

Автоматически изменить формат критерия во второй таблице нельзя, т.к. ссылка ведет на целый диапазон. Придется вмешиваться в ссылку на критерий в первой таблице. Для этого потребуется дописать функцию ТЕКСТ, которая изменит числовой формат на текстовый. Синтаксис функции ТЕКСТ предполагает обязательное указание формата. Достаточно задать формат #. Ниже картинка с готовой формулой.

Синтаксис

• совпадение искомых данных может быть точным и приблизительным;
• при сопоставлении по приблизительной точности данных убедитесь, что данные в таблицах отсортированы в убывающем порядке (от большого к малому). Иначе, результат сопоставления будет некорректным;
• при сопоставлении данных по приблизительной точности:
  — если функция не найдет искомое значение, она выдаст наибольшую величину, которая будет меньше чем значения поиска;
  — если функция при сопоставлении выдает ошибку #N/A, то искомое значение меньше чем самая маленькая величина в искомом диапазоне;
  — вы можете использовать подстановочные знаки для искомых значений.

Пример 1. Ищем результат экзамена для студента

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

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

• “Петров” — фамилия, данные по которой, ищем;
• $A$3:$E$10 — диапазон данных с результатами экзаменов;
• “3” — порядковый номер столбца диапазона данных со значением оценки по Физике;
• “0” — точное совпадение искомого значения.

Ниже, наглядно изображено как работает по примеру наша функция.

Сначала, она ищет в крайней левой колонке диапазона данных фамилию “Петров”. Поиск происходит сверху вниз.

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

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

Например, чтобы найти результаты экзамена для студента Пескова по Химии, нам нужна формула:

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

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

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

Пример 2. Двухфакторный поиск данных

На примере выше мы указывали вручную в качестве атрибута «номер_столбца» порядковый номер колонки с данными.

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

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

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

Так будет выглядеть формула для двухфакторного поиска:

В формуле выше используется функция MATCH (ПОИСКПОЗ) , которая отвечает за порядковый номер колонки с данными. MATCH (ПОИСКПОЗ) принимает название учебного предмета как значение поиска (в ячейке “H4”) и возвращает его позицию в диапазоне ячеек “A2:E2”.

Если указать “Математика”, Excel вернет “2”, поскольку “Математика” находится в ячейке “B2” (вторая по счету в этом массиве).

Пример 3. Используем выпадающий список при двух факторном поиске

Используя способ из примера №2, нам требуется делать много ручной работы. Высок риск допустить ошибку и потратить много времени, особенно, если вы работаете с большим объемом данных.

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

Основываясь на тех данных, что вы выберете из выпадающего списка, функция автоматически выведет необходимые данные, как это указано на примере ниже:

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

Данные поиска были преобразованы в выпадающие списки.

• Выбрать ячейку, в которой вы хотите создать выпадающий список. В нашем примере в ячейке “G4” мы указали фамилии студентов.
• Перейти к вкладке Данные -> Проверка данных.
• В диалоговом окне «Проверка данных» на вкладке «Настройки» выбрать «Список» и в раскрывающемся списке выберите «Разрешить».
• В источнике выбрать $A$3:$A$10
• Нажать «ОК».

Теперь у вас появится раскрывающийся список в ячейке “G4”. Аналогично, вы можете создать его в “H3” для предметов.

Пример 4. Трех факторный поиск данных ВПР

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

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

Трехсторонний поиск — это возможность получить отметки студента по предмету с определенным уровнем экзамена.

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

Эта формула использует функцию CHOOSE (ВЫБОР), чтобы убедиться, что данные выбраны из правильной таблицы. Давайте проанализируем часть формулы CHOOSE (ВЫБОР):

Первый аргумент формулы (IF(H3=»Вступительный»,1,IF(H3=»Полугодовой»,2,3) или (ЕСЛИ(H3=»Вступительный»;1;ЕСЛИ(H3=»Полугодовой»;2;3) проверяет ячейку “H3” и определяет, на какой уровень экзамена ссылаются. Если это «Вступительный», функция возвращает данные из диапазона $A$4:$E$12, который содержит оценки для вступительного экзамена.

Если выбран уровень «Полугодовой», то система возвращает данные из диапазона $A$16:$E$24, в другом случае она возвращает данные из диапазона $A$28:$E$36.

Такой подход делает массив таблиц динамическим и трех факторным.

Пример 5. Получаем последнее значение колонки с помощью функции VLOOKUP (ВПР)

С помощью ВПР вы можете вычислить последнее число из списка.

Наибольшее положительное число, которое вы можете использовать в Excel, равно 9.99999999999999E + 307 . Это также означает, что самый большой номер поиска в номере VLOOKUP также 9.99999999999999E + 307 .

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

Предположим, у вас есть набор данных (в диапазоне ячеек A1:A14), как показано ниже, и вы хотите получить число из последней ячейки в списке.

Обратите внимание, что в формуле выше используется приблизительная точность совпадения данных (несмотря на это нет необходимости упорядочивать список).

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

• Если он находит точное совпадение, она возвращает это значение;
• Если она находит число, превышающее значение поиска => возвращает число в ячейке над ним;
• Если значение поиска больше всех чисел в списке, функция возвращает последнее число из списка.

Число 9.99999999999999E + 307 это самое большое число, которое можно использовать в Excel, когда оно используется как критерий поиска, тогда функция VLOOKUP (ВПР) возвращает последнее число из списка.

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

Здесь действует та же логика. Система просматривает все названия из списка. Так как “яяя” самое большое текстовое значение => результатом вычисления будет самое крайнее значение из списка данных.

Пример 6. Частичный поиск с использованием символов подстановочных знаков и ВПР

Символы подстановки в Excel могут быть полезными во многих ситуациях.

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

Например, у вас есть набор данных, как показано ниже, и вы хотите найти компанию “ABC” в списке, но в списке есть только “ABC Ltd” вместо “ABC”.

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

Однако, вы можете использовать подстановочный знак в VLOOKUP (ВПР) для более точного соответствия данных.

Введите следующую формулу в ячейку “D2” и перетащите ее в другие ячейки:

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

Таким образом, осуществляя поиск, Excel будет понимать, что необходимо искать значение, которое содержит слово из ячейки “С2” (в нашем примере).

Например, в ячейке “C2” указано название компании “ABC”, поэтому функция просматривает значения в диапазоне данных “A2:A8” и ищет “ABC”. Она находит соответствие в ячейке “A2”, так как та содержит слово “ABC” в “ABC Ltd”. Не имеет значения, есть ли какие-либо символы слева или справа от “ABC”.

Примечание: ВПР всегда возвращает первое совпадающее значение и перестает искать дальше. Поэтому, если у вас есть название компании “ABC Ltd”. и “ABC Corporation” в списке, она вернет первый по порядку и проигнорирует остальные.

Пример 7. Функция ВПР возвращает ошибку, несмотря на совпадение в значении поиска

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

Например, у вас в таблице есть очевидное соответствие (Иван), но система по-прежнему возвращает ошибку.

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

Решение есть, и зовут его — функция TRIM. Она удаляет все лишние пробелы в значениях.

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

Для того, чтобы функция TRIM (СЖПРОБЕЛЫ) заработала при использовании VLOOKUP (ВПР) , следует вводить формулу после набора не с помощью клавиши Enter на клавиатуре, а с помощью сочетания клавиш Ctrl + Shift + Enter.

Пример 8. Функция ВПР в Excel с несколькими условиями

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

Но часто нам необходимо использовать VLOOKUP с поиском по несколькими критериям.

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

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

Осуществить поиск по студенту + названию предмета + уровню экзамена можно с помощью создания вспомогательного столбца, как показано на примере ниже:

Теперь, нам необходимо создать уникальный ID для каждого студента и уровня экзамена с помощью формулы во вспомогательном столбце: =A2&»|»&B2 .

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

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

Теперь вы можете использовать значения вспомогательного столбца в качестве значений поиска.

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

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

Пример 9. Обработка ошибок с помощью функции ВПР

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

В Excel есть возможность заменить сообщения об ошибке на заданное вами текстовое или числовое значение типа “Нет”, “Не доступно”, “Нет данных” и.т.д.

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

Для того, чтобы заменить сообщение ошибки на какое-то более формальное значение нам поможет функция ЕСЛИОШИБКА(IFERROR) :

Функция ЕСЛИОШИБКА(IFERROR) проверяет, если в результате вычислений по формуле выдается ошибка, то она выдает, то значение, которое вы указали в функции ЕСЛИОШИБКА(IFERROR), например “Нет данных”.

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе «От новичка до мастера Excel«. Успей зарегистрироваться по ссылке!

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

Функция осплт в excel

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