Excel платный или нет

Обновлено: 21.11.2024

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

Можно ли создать формулу для каждого пользователя, которая будет искать в столбце B имя "Джон Смит" и, если оно присутствует, в столбце C создавать текст "Джон Смит заплатил" или что-то в этом роде?

Я пробовал играть с функциями поиска и поиска, но пока безуспешно.

Любая помощь приветствуется!

Я только что попробовал использовать: =IF(B1:B500 = "HEGARTY", "Не оплачено", "Оплачено"), но это не работает. Я основывал его на версии >0, которую я видел. Он просто продолжает говорить «Оплачено», поэтому формула не работает.

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

В настоящее время: A1 — это дата транзакции. B1 — реквизиты счета получателя — все в одной строке. Имя, номер счета, код сортировки, время. C1 — дебет, D1 — кредит, E1 — общий баланс. Я хочу получить имя из B1, поэтому пример Payee: JOHN SMITH SMITH 0344553699632BBBKLYN 090128 10 16JUN18 20:06 Исходя из этого, я бы хотел, чтобы формула искала все B, находила «JOHN SMITH» и ставила « Джон Смит заплатил в конце столбца. Таким образом, я могу просмотреть список и отметить их в своей учетной книге. Надеюсь, это поможет?

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

2 ответа 2

Я решил это, используя комбинацию ЕСЛИ() и СЧЁТЕСЛИ():

Для этого вопроса значения в A, C, D и E не важны, однако предполагается, что ваше ИМЯ идентично тому, которое вы получаете в банке.

EDIT: исправлена ошибка в формуле

Вот решение, использующее стандартный метод ИНДЕКС(…,ПОИСКПОЗ()):

Введите следующую формулу в I3 и нажмите Ctrl-Enter/скопировать-вставить/заполнить столбец:

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

Пояснение:

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

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

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

Синтаксис

ПЛТ(коэффициент, nper, pv, [fv], [type])

Примечание. Более полное описание аргументов PMT см. в описании функции PV.

Синтаксис функции PMT имеет следующие аргументы:

Требуется ставка. Процентная ставка по кредиту.

Количество обязательных. Общее количество платежей по кредиту.

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

Fv Необязательный. Будущая стоимость или денежный баланс, который вы хотите получить после последнего платежа. Если fv опущено, предполагается, что оно равно 0 (ноль), то есть будущая стоимость кредита равна 0.

Введите Необязательно. Число 0 (ноль) или 1 указывает, когда должны быть произведены платежи.

Установить тип равным

Если платежи должны быть выполнены

В конце периода

В начале периода

Примечания

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

Убедитесь, что вы используете одинаковые единицы измерения для указания скорости и числа. Если вы делаете ежемесячные платежи по четырехлетнему кредиту с годовой процентной ставкой 12 процентов, используйте 12%/12 для ставки и 4 * 12 для nper. Если вы делаете ежегодные платежи по одному и тому же кредиту, используйте 12 процентов для ставки и 4 для nper.

Совет. Чтобы найти общую сумму, выплаченную в течение срока кредита, умножьте возвращенное значение ПЛТ на nper.

Пример

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

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

Вставьте следующее в ячейку рядом с ячейкой, содержащей имя первого клиента:

=IF(Лист1!F3 = 8, "Истина", "Ложь")

Замените "Лист1!F3" адресом ячейки, которую Excel должен проверить. Например, если вы хотите, чтобы функция ЕСЛИ проверяла ячейку G4 на листе "Оплата", измените формулу на:

=IF(Billing!G4 = 8, "Истина", "Ложь")

Замените первый компонент формулы, в настоящее время "Billing!G4 = 8", на утверждение, истинность которого вы хотите проверить с помощью функции IF. Например, если вы хотите проверить, больше ли содержимое ячейки 0, что указывает на непогашенный остаток клиента, который не оплатил, измените формулу на:

=IF(Billing!G4 > 0, "Истина", "Ложь")

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

=IF(Billing!G4 > 0, "Не оплачено", "Ложь")

Измените значение "False" на сообщение, которое должно отображаться, когда функция IF обнаружит, что ячейка не соответствует вашим критериям. Например, если вы хотите, чтобы появилось сообщение "Оплачено", измените формулу на:

=IF(Billing!G4 > 0, "Не оплачено", "Оплачено")

Нажмите клавишу "Ввод" на клавиатуре. Теперь в ячейке отображается «Не оплачено» или «Оплачено».

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

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

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

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

Синтаксис

Более полное описание аргументов в FV и дополнительную информацию о функциях аннуитета см. в PV.

Синтаксис функции FV имеет следующие аргументы:

Требуется ставка. Процентная ставка за период.

Количество обязательных. Общее количество периодов платежей в аннуитете.

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

Pv Необязательно. Текущая стоимость, или единовременная сумма, которую представляет собой ряд будущих платежей прямо сейчас. Если параметр pv опущен, предполагается, что он равен 0 (нулю), и вы должны включить аргумент pmt.

Введите Необязательно. Число 0 или 1 и указывает, когда должны быть произведены платежи. Если тип не указан, предполагается, что он равен 0.

Установить тип равным

Если платежи должны быть выполнены

В конце периода

В начале периода

Примечания

Убедитесь, что вы используете одинаковые единицы измерения для указания скорости и числа. Если вы делаете ежемесячные платежи по четырехлетнему кредиту под 12% годовых, используйте 12%/12 для ставки и 4*12 для кпер. Если вы делаете ежегодные платежи по одному и тому же кредиту, используйте 12% для ставки и 4 для nper.

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

Примеры

Читайте также: