Подбор условий на нужную сумму excel
Обновлено: 29.06.2024
В этом кратком руководстве объясняется, что такое автосумма, и показаны наиболее эффективные способы использования автосуммы в Excel. Вы увидите, как автоматически суммировать столбцы или строки с помощью ярлыка «Сумма», суммировать только видимые ячейки, суммировать выбранный диапазон по вертикали и горизонтали за один раз, а также узнаете наиболее распространенную причину, по которой не работает автосумма Excel.
Знаете ли вы, что функция СУММ в Excel — это функция, о которой люди читают больше всего? Чтобы убедиться в этом, просто ознакомьтесь со списком 10 самых популярных функций Excel от Microsoft. Недаром на ленту Excel решили добавить специальную кнопку, которая автоматически вставляет функцию СУММ. Итак, если вы хотите узнать «Что такое автосумма в Excel?» вы уже получили ответ :)
По сути, Excel AutoSum автоматически вводит формулу для суммирования чисел на листе. Дополнительные сведения см. в следующих разделах этого руководства.
Где находится кнопка "Автосумма" в Excel?
Кнопка "Автосумма" доступна в двух местах на ленте Excel.
Как выполнить автосуммирование в Excel
Всякий раз, когда вам нужно суммировать один диапазон ячеек, будь то столбец, строка или несколько смежных столбцов или строк, вы можете использовать функцию автосуммирования Excel, чтобы автоматически создать подходящую формулу СУММА для вас.
Чтобы использовать автосумму в Excel, выполните следующие 3 простых шага:
- Выберите ячейку рядом с числами, которые нужно суммировать:
- Чтобы суммировать столбец, выберите ячейку непосредственно под последним значением в столбце.
- Чтобы суммировать строку, выберите ячейку справа от последнего числа в строке.
В выбранной ячейке появится формула суммы, а диапазон добавляемых ячеек будет выделен (в данном примере B2:B6):
В большинстве случаев Excel выбирает правильный диапазон для суммирования. В редких случаях, когда выбран неправильный диапазон, вы можете исправить его вручную, введя нужный диапазон в формуле или перетащив курсор через ячейки, которые вы хотите суммировать.
Совет. Чтобы суммировать несколько столбцов или строк одновременно, выберите несколько ячеек внизу или справа от таблицы соответственно, а затем нажмите кнопку Автосумма. Дополнительные сведения см. в разделе Как использовать автосумму для нескольких ячеек одновременно.
Теперь вы можете увидеть рассчитанную сумму в ячейке и формулу СУММ в строке формул:
Ярлык для суммы в Excel
Если вы один из тех пользователей Excel, которые предпочитают работать с клавиатурой, а не с мышью, вы можете использовать следующую комбинацию клавиш Excel AutoSum для суммирования ячеек:
Нажатие клавиши "Знак равенства" при нажатой клавише "Alt" вставляет формулу суммы в выбранные ячейки точно так же, как нажатие кнопки Автосумма на ленте, а затем вы нажимаете клавишу "Ввод", чтобы завершите формулу.
Как использовать автосумму с другими функциями
Помимо добавления ячеек, вы можете использовать кнопку AutoSum в Excel для вставки других функций, таких как:
- СРЗНАЧ — для возврата среднего (среднего арифметического) чисел.
- COUNT — для подсчета ячеек с числами.
- MAX — для получения максимального значения.
- MIN — чтобы получить наименьшее значение.
Все, что вам нужно сделать, это выбрать ячейку, в которую вы хотите вставить формулу, щелкнуть стрелку раскрывающегося списка "Автосумма" и выбрать нужную функцию из списка.
Например, вот как можно получить наибольшее число в столбце B:
Если в раскрывающемся списке "Автосумма" выбрать "Дополнительные функции", Microsoft Excel откроет диалоговое окно "Вставить функцию", как если бы вы нажали кнопку Вставить функцию. на вкладке Формулы или с помощью кнопки fx на панели формул.
Как автоматически суммировать только видимые (отфильтрованные) ячейки в Excel
Вы уже знаете, как использовать функцию "Автосумма" в Excel для суммирования столбца или строки. Но знаете ли вы, что его можно использовать для суммирования только видимых ячеек в отфильтрованном списке?
Если ваши данные организованы в виде таблицы Excel (что можно легко сделать, нажав сочетание клавиш Ctrl + T), нажатие кнопки "Автосумма" вставит функцию ПРОМЕЖУТОЧНЫЙ ИТОГ, которая добавляет только видимые ячейки.
Если вы отфильтровали данные, применив один из параметров Фильтрация, нажатие кнопки "Автосумма" также вставит формулу ПРОМЕЖУТОЧНЫЙ ИТОГ, а не СУММ, как показано на снимке экрана ниже:
Более подробное объяснение аргументов функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ см. в разделе Как суммировать отфильтрованные ячейки в Excel.
Советы по автосумме в Excel
По мере того, как вы знаете, как использовать автосумму в Excel для автоматического добавления ячеек, вы можете изучить пару приемов, позволяющих сэкономить время и сделать вашу работу еще более эффективной.
Как использовать автосумму для нескольких ячеек одновременно
Если вы хотите просуммировать значения в нескольких столбцах или строках, выделите все ячейки, в которые вы хотите вставить формулу суммы, а затем нажмите кнопку Автосумма на ленте или нажмите ярлык суммы Excel. .
Например, вы можете выбрать ячейки A10, B10 и C10, нажать Автосумма и получить сразу 3 столбца. Как показано на снимке экрана ниже, значения в каждом из трех столбцов суммируются по отдельности:
Как суммировать выбранные ячейки по вертикали и горизонтали
Чтобы суммировать только определенные ячейки в столбце, выберите эти ячейки и нажмите кнопку Автосумма. Это суммирует выбранные ячейки по вертикали столбец за столбцом и помещает формулу(ы) СУММ под выделенным:
Если вы хотите суммировать ячейки построчно, выберите ячейки, которые вы хотите суммировать, и один пустой столбец справа. Excel просуммирует выбранные ячейки по горизонтали и вставит формулы СУММ в пустой столбец, включенный в выделение:
Чтобы суммировать ячейки по столбцам и по строкам, выберите ячейки, которые вы хотите добавить, а также одну пустую строку ниже и один пустой столбец справа, и Excel суммирует выбранные ячейки по вертикали и горизонтали:
Как скопировать формулу автосуммы в другие ячейки
После того, как AutoSum добавит в выбранную ячейку функцию SUM (или другую), вставленная формула ведет себя как обычная формула Excel. Следовательно, вы можете скопировать эту формулу в другие ячейки обычным способом, например, перетащив маркер заполнения. Дополнительные сведения см. в разделе Как скопировать формулу в Excel.
Имейте в виду, что функция автосуммы Excel использует относительные ссылки на ячейки (без символа $), которые корректируются в соответствии с новым положением формулы в зависимости от относительного положения строк и столбцов.
Например, вы можете использовать автосумму, чтобы вставить в ячейку A10 следующую формулу для суммирования значений в столбце A: =СУММ(A1:A9) . И когда вы скопируете эту формулу в ячейку B10, она превратится в =СУММ(B1:B9) и суммирует числа в столбце B.
В большинстве случаев это именно то, что вам нужно. Но если вы хотите скопировать формулу в другую ячейку, не изменяя ссылки на ячейку, вам нужно исправить ссылки, добавив знак $. Полную информацию см. в разделе Зачем использовать $ в формулах Excel.
Автосумма Excel не работает
Самой распространенной причиной того, что функция автосуммы не работает в Excel, являются числа, отформатированные как текст. На первый взгляд эти значения могут выглядеть как обычные числа, но Excel воспринимает их как текстовые строки и не включает в расчеты.
Наиболее очевидными признаками чисел, отформатированных как текст, являются их выравнивание по левому краю и маленькие зеленые треугольники в верхнем левом углу ячеек. Чтобы исправить такие текстовые числа, выберите все проблемные ячейки, нажмите предупреждающий знак, а затем нажмите «Преобразовать в число».
Числа могут форматироваться как текст по разным причинам, например при импорте набора данных из внешнего источника или заключении числовых значений в двойные кавычки в формулах Excel. В последнем случае в ячейках не появятся ни зеленые треугольники, ни предупреждающий знак, поскольку Excel предполагает, что вы намеренно хотите вывести текстовую строку.
Например, следующая формула ЕСЛИ работает нормально:
Но возвращаемые 1 и 0 являются текстовыми значениями, а не числами!И поэтому, когда вы пытаетесь выполнить автосумму для ячеек, содержащих такие формулы, вы всегда получите в результате «0».
Как только вы удалите "" вокруг 1 и 0 в приведенной выше формуле, Excel AutoSum будет обрабатывать выходные данные как числа, и они будут правильно сложены.
Если текстовые числа не соответствуют действительности, вы можете узнать о других возможных причинах в этом руководстве: Функция SUM в Excel не работает — причины и решения.
Вот как вы выполняете автосумму в Excel. И если кто-то когда-нибудь спросит вас: «Что делает AutoSum?», вы можете отослать его к этому руководству :)
Помимо обычной функции СУММ, знаете ли вы, что в Excel есть несколько других функций для условного суммирования ячеек? Если вам интересно узнать о них, ознакомьтесь с ресурсами в конце этой страницы. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Вас также может заинтересовать
44 комментария к статье "Как использовать автосумму в Excel"
Здравствуйте, я пытаюсь вручную добавить число в столбец A (часы, которые я отработал) и автоматически вычислить разницу от заданного числа (число доступных часов) в столбце B для каждого дня месяца. Я не могу понять, как заставить его автоматически заполнять столбец B после того, как я ввел число для столбца A. Любая помощь будет принята с благодарностью. Я должен делать это для 30 сотрудников каждый месяц, а вручную вводить столбцы A и B для всех занимает очень много времени.
Привет!
Запишите формулу в ячейку B1. =100-A1
100 — заданное число. Если данные находятся в нижних строках, скопируйте эту формулу в нижний столбец B.
Я хочу добавить диапазон чисел в столбец, длина которого может меняться при каждом использовании. Добавьте к этому, что я хочу сделать 25 вычислений, охватывающих равные части столбца. Таким образом, специально вычисляется сумма первых 4% строк, затем следующих 4% строк и т. д.
Пример A
Столбец с глубиной 8000 строк. Я хочу, чтобы первый расчет суммировал строки 1-320 "=Sum(a1:a320)" & "=Sum(a321:a640)" и т.д.
Пример B
В следующий раз, когда я буду использовать лист, столбец будет иметь глубину 12 100 строк. Я хочу, чтобы эта формула автоматически менялась на "=Sum(a1:a484)" & "=Sum(a485:a968)" и т.д.
Сейчас мне приходится менять диапазон суммы каждый раз, когда я вычисляю новые данные. Итак, вы можете видеть мой интерес к способу автоматического изменения диапазона в зависимости от длины столбца.
Спасибо за любой ответ, заранее.
Здравствуйте!
Создайте динамический именованный диапазон в столбце А. Для этого можно использовать формулу:
=СМЕЩЕНИЕ($A$1, 0, 0, СЧЁТ($A:$A), 1)
Если имя этого динамического диапазона "test1", то формула СУММ с использованием функции ДВССЫЛ может быть:
Надеюсь, это поможет
Спасибо, Александр.
Мне пришлось пройти через несколько проб и ошибок, и мне понадобилась фильтрация творческих данных, но ваше решение, похоже, сработало хорошо.
Какую формулу я бы использовал для суммирования чисел, дающих A1 в каждом интервале, и которые будут отображаться на B1.
Например: 1-й раз, когда я ввожу 15 в A1, поэтому B1 отображает 15
2-й раз, когда я ввожу 20 в A1, поэтому B1 должен отображать «35»
3-й раз, когда A1 = 5, тогда B1 должен быть "40"
Привет!
Если я правильно понял, то, что вы хотите сделать, невозможно. Ячейка может хранить только последнее значение.
Я пытаюсь автоматически суммировать черный столбец. Я ввел пример формулы =d4+c5. Затем я перетаскиваю вниз, чтобы скопировать другие ячейки в столбце. Вместо того, чтобы ячейки были пустыми, он будет повторять числа вниз, и я хочу, чтобы они повторяли числа
Привет,
Я пытаюсь сделать формулу для вычитания, но я просто не могу сделать это правильно.
Мне нужны столбцы:
начиная с C4, ЧТОБЫ ВЫЧИТАТЬ E4, РАВНО G4
и для этого следовать за столбцами, например: C5, ЧТОБЫ ВЫЧИТАТЬ E5, РАВНО, G5 и т. д.
Пожалуйста, сообщите как я делаю эту формулу.
Спасибо.
Привет!
Извините, я не совсем понял задачу. Что именно вы хотите посчитать? Ваш вопрос не совсем ясен, уточните пожалуйста.
КАК СДЕЛАТЬ ВЫЧИСЛЕНИЕ ИЗ ТРЕХ РАЗЛИЧНЫХ КОЛОНН?
Для примера: первая колонка - это 1 коробка, эквивалентная 60 шт., вторая колонка - 1 упаковка, эквивалентная 30 шт., затем третья колонка по штукам, а затем общая сумма по всей колонке.
Спасибо за ответ.
Здравствуйте!
Какую формулу вы использовали и какая возникла проблема или ошибка?
Подробнее о функции СУММ в Excel можно узнать из этой статьи в нашем блоге.
Спасибо! Я пытался получить среднее значение, но числа не распознавались как числа.
как рассчитать процент gst, например 347+18%
пожалуйста, ответьте на этот почтовый идентификатор
Я создал таблицу расходов. Я хочу записывать все свои расходы. Я разбил их на категории и подкатегории. Например, «Продукты», а затем «Мясо», «Молочные продукты», «Закуски», «Фрукты и овощи» и т. д.
Я использовал автосумму для подсчета денег, потраченных на каждую подкатегорию, а затем я хочу получить общую сумму для всех продуктов, но я не могу просуммировать ранее столбец автосуммы?
Электронная таблица настроена так, что столбец A – это дата, затем BG – продукты, заголовки подкатегорий в строке 5 – итоговая сумма в строке 6 (сумма (b7:b50)) и я пытаюсь получить общую сумму в строке 4< /p>
Надеюсь, это имеет смысл
Пожалуйста, я использую автосумму, но она игнорирует ячейки с большими цифрами, что усложняет мою работу. Любая помощь?
Здравствуйте!
Я не смог воспроизвести вашу проблему. Пожалуйста, опишите вашу проблему подробнее. Уточните, о каких числах вы говорите.
Привет
Как общий показатель производства в A2 будет автоматически изменен, если я изменю значение A1.
Допустим, вчера A2 был равен 1500, а сегодня A1 вошел как 100. Итак, как A2 будет равен 1600.
Здравствуйте!
Прочитайте, как рассчитать промежуточный итог
Надеюсь, это будет полезно.
46 057,63 0 366
как получить сумму значения целого столбца perticular и вставить его сумму в столбец perticula.
м 1111
м 222
м 333
м 444
м 555
м 12323
м 1324
Если вы знаете результат, который хотите получить от формулы, но не знаете, какое входное значение необходимо формуле для получения этого результата, используйте функцию поиска цели. Например, предположим, что вам нужно занять немного денег. Вы знаете, сколько денег вы хотите, сколько времени вы хотите взять, чтобы погасить кредит, и сколько вы можете позволить себе платить каждый месяц. Вы можете использовать Goal Seek, чтобы определить, какую процентную ставку вам нужно обеспечить, чтобы достичь цели по кредиту.
Если вы знаете результат, который хотите получить от формулы, но не знаете, какое входное значение необходимо формуле для получения этого результата, используйте функцию поиска цели. Например, предположим, что вам нужно занять немного денег. Вы знаете, сколько денег вы хотите, сколько времени вы хотите взять, чтобы погасить кредит, и сколько вы можете позволить себе платить каждый месяц. Вы можете использовать Goal Seek, чтобы определить, какую процентную ставку вам нужно обеспечить, чтобы достичь цели по кредиту.
Примечание. Поиск цели работает только с одним входным значением переменной. Если вы хотите принять более одного входного значения; например, как сумма кредита, так и сумма ежемесячного платежа по кредиту, вы используете надстройку Solver. Дополнительные сведения см. в разделе Определение и решение проблемы с помощью Решателя.
Пошаговое руководство с примером
Давайте рассмотрим предыдущий пример шаг за шагом.
Поскольку вы хотите рассчитать процентную ставку по кредиту, необходимую для достижения вашей цели, вы используете функцию ПЛТ. Функция PMT вычисляет сумму ежемесячного платежа. В этом примере сумма ежемесячного платежа является целью, к которой вы стремитесь.
Подготовьте лист
Откройте новый пустой лист.
Сначала добавьте несколько меток в первый столбец, чтобы упростить чтение листа.
В ячейке A1 введите Сумма кредита.
В ячейке A2 введите Срок в месяцах.
В ячейке A3 введите Процентная ставка.
В ячейке A4 введите Платеж.
Далее добавьте известные вам значения.
В ячейке B1 введите 100000. Это сумма, которую вы хотите занять.
В ячейке B2 введите 180. Это количество месяцев, в течение которых вы хотите погасить кредит.
Примечание. Хотя вам известна сумма платежа, вы не вводите ее как значение, поскольку сумма платежа является результатом формулы. Вместо этого вы добавляете формулу на лист и указываете сумму платежа на более позднем этапе, когда используете поиск цели.
Далее добавьте формулу, для которой у вас есть цель. Например, используйте функцию PMT:
В ячейке B4 введите =PMT(B3/12,B2,B1). Эта формула рассчитывает сумму платежа. В этом примере вы хотите платить 900 долларов каждый месяц. Вы не вводите эту сумму здесь, потому что хотите использовать поиск цели для определения процентной ставки, а поиск цели требует, чтобы вы начали с формулы.
Формула ссылается на ячейки B1 и B2, которые содержат значения, указанные вами на предыдущих шагах. Формула также относится к ячейке B3, где вы укажете, что Goal Seek поместит процентную ставку. Формула делит значение в B3 на 12, поскольку вы указали ежемесячный платеж, а функция ПЛТ предполагает годовую процентную ставку.
Поскольку в ячейке B3 нет значения, Excel предполагает процентную ставку 0% и, используя значения в примере, возвращает платеж в размере 555,56 долларов США. Вы можете пока игнорировать это значение.
Используйте поиск цели для определения процентной ставки
На вкладке "Данные" в группе "Инструменты данных" нажмите "Анализ возможных вариантов", а затем нажмите "Поиск цели".
В поле "Установить ячейку" введите ссылку на ячейку, содержащую формулу, которую вы хотите разрешить. В примере это ссылка на ячейку B4.
В поле Значение "Кому" введите нужный результат формулы. В примере это -900. Обратите внимание, что это число отрицательное, поскольку оно представляет платеж.
В поле Путем изменения ячейки введите ссылку на ячейку, содержащую значение, которое вы хотите изменить. В примере эта ссылка — ячейка B3.
Примечание. На ячейку, которую изменяет поиск цели, должна ссылаться формула в ячейке, которую вы указали в поле "Установить ячейку".
Goal Seek запускается и выдает результат, как показано на следующем рисунке.
Ячейки B1, B2 и B3 содержат значения суммы кредита, срока и процентной ставки.
Ячейка B4 отображает результат формулы =PMT(B3/12,B2,B1).
Наконец, отформатируйте целевую ячейку (B3) так, чтобы она отображала результат в процентах.
На вкладке "Главная" в группе "Число" нажмите "Процент".
Нажмите «Увеличить десятичное число» или «Уменьшить десятичное число», чтобы задать количество знаков после запятой.
Если вы знаете результат, который хотите получить от формулы, но не знаете, какое входное значение необходимо формуле для получения этого результата, используйте функцию поиска цели. Например, предположим, что вам нужно занять немного денег. Вы знаете, сколько денег вы хотите, сколько времени вы хотите взять, чтобы погасить кредит, и сколько вы можете позволить себе платить каждый месяц. Вы можете использовать Goal Seek, чтобы определить, какую процентную ставку вам нужно обеспечить, чтобы достичь цели по кредиту.
Примечание. Поиск цели работает только с одним входным значением переменной. Если вы хотите принять более одного входного значения, например, как сумму кредита, так и сумму ежемесячного платежа по кредиту, используйте надстройку «Поиск решения». Дополнительные сведения см. в разделе Определение и решение проблемы с помощью Решателя.
Пошаговое руководство с примером
Давайте рассмотрим предыдущий пример шаг за шагом.
Поскольку вы хотите рассчитать процентную ставку по кредиту, необходимую для достижения вашей цели, вы используете функцию ПЛТ. Функция PMT вычисляет сумму ежемесячного платежа. В этом примере сумма ежемесячного платежа является целью, к которой вы стремитесь.
Подготовьте лист
Откройте новый пустой лист.
Сначала добавьте несколько меток в первый столбец, чтобы упростить чтение листа.
В ячейке A1 введите Сумма кредита.
В ячейке A2 введите Срок в месяцах.
В ячейке A3 введите Процентная ставка.
В ячейке A4 введите Платеж.
Далее добавьте известные вам значения.
В ячейке B1 введите 100000. Это сумма, которую вы хотите занять.
В ячейке B2 введите 180. Это количество месяцев, в течение которых вы хотите погасить кредит.
Примечание. Хотя вам известна сумма платежа, вы не вводите ее как значение, поскольку сумма платежа является результатом формулы. Вместо этого вы добавляете формулу на лист и указываете сумму платежа на более позднем этапе, когда используете поиск цели.
Далее добавьте формулу, для которой у вас есть цель. Например, используйте функцию PMT:
В ячейке B4 введите =PMT(B3/12,B2,B1). Эта формула рассчитывает сумму платежа. В этом примере вы хотите платить 900 долларов каждый месяц. Вы не вводите эту сумму здесь, потому что хотите использовать поиск цели для определения процентной ставки, а поиск цели требует, чтобы вы начали с формулы.
Формула ссылается на ячейки B1 и B2, которые содержат значения, указанные вами на предыдущих шагах. Формула также относится к ячейке B3, где вы укажете, что Goal Seek поместит процентную ставку. Формула делит значение в B3 на 12, поскольку вы указали ежемесячный платеж, а функция ПЛТ предполагает годовую процентную ставку.
Поскольку в ячейке B3 нет значения, Excel предполагает процентную ставку 0% и, используя значения в примере, возвращает платеж в размере 555,56 долларов США. Вы можете пока игнорировать это значение.
Используйте поиск цели для определения процентной ставки
Выполните одно из следующих действий:
В Excel 2016 для Mac: на вкладке "Данные" нажмите "Анализ возможных вариантов", а затем нажмите "Поиск цели".
В Excel для Mac 2011: на вкладке "Данные" в группе "Работа с данными" нажмите "Анализ возможных вариантов", а затем нажмите "Поиск цели".
В поле "Установить ячейку" введите ссылку на ячейку, содержащую формулу, которую вы хотите разрешить. В примере это ссылка на ячейку B4.
В поле Значение "Кому" введите нужный результат формулы. В примере это -900. Обратите внимание, что это число отрицательное, поскольку оно представляет платеж.
В поле Путем изменения ячейки введите ссылку на ячейку, содержащую значение, которое вы хотите изменить. В примере эта ссылка — ячейка B3.
Примечание. На ячейку, которую изменяет поиск цели, должна ссылаться формула в ячейке, которую вы указали в поле "Установить ячейку".
Goal Seek запускается и выдает результат, как показано на следующем рисунке.
Наконец, отформатируйте целевую ячейку (B3), чтобы она отображала результат в процентах. Выполните одно из следующих действий:
В Excel 2016 для Mac: на вкладке "Главная" нажмите "Увеличить десятичный разряд" или "Уменьшить десятичный разряд" .
В Excel для Mac 2011: на вкладке "Главная" в группе "Числа" нажмите "Увеличить десятичное число" или "Уменьшить десятичное число", чтобы задать количество знаков после запятой.
Вы можете быстро найти и выбрать определенные ячейки или диапазоны, введя их имена или ссылки на ячейки в поле "Имя", расположенном слева от строки формул:
Вы также можете выбрать именованные или неименованные ячейки или диапазоны с помощью команды "Перейти" (F5 или Ctrl+G).
Важно! Чтобы выбрать именованные ячейки и диапазоны, необходимо сначала определить их. Дополнительные сведения см. в разделе Определение и использование имен в формулах.
Чтобы выбрать именованную ячейку или диапазон, нажмите стрелку рядом с полем Имя, чтобы отобразить список именованных ячеек или диапазонов, а затем щелкните нужное имя.
Чтобы выбрать две или более именованных ссылок на ячейки или диапазонов, нажмите стрелку рядом с полем Имя, а затем щелкните имя первой ссылки на ячейку или диапазона, который вы хотите выбрать. Затем, удерживая нажатой клавишу CTRL, щелкните имена других ячеек или диапазонов в поле «Имя».
Чтобы выбрать безымянную ссылку на ячейку или диапазон, введите ссылку на ячейку или диапазон ячеек, которые вы хотите выбрать, а затем нажмите клавишу ВВОД. Например, введите B3, чтобы выбрать эту ячейку, или введите B1:B3, чтобы выбрать диапазон ячеек.
Примечание. Нельзя удалить или изменить имена, определенные для ячеек или диапазонов в поле Имя. Удалить или изменить имена можно только в Диспетчере имен (вкладка «Формулы», группа «Определенные имена»). Дополнительные сведения см. в разделе Определение и использование имен в формулах.
Нажмите F5 или CTRL+G, чтобы открыть диалоговое окно "Перейти".
В списке Перейти нажмите имя ячейки или диапазона, которые вы хотите выделить, или введите ссылку на ячейку в поле Ссылка, а затем нажмите OK.
Совет. Чтобы быстро найти и выбрать все ячейки, содержащие определенные типы данных (например, формулы), или только ячейки, соответствующие определенным критериям (например, только видимые ячейки или последнюю ячейку на листе, содержащую данные или форматирование), нажмите «Специальный» во всплывающем окне «Перейти», а затем выберите нужный вариант.
Выберите Формулы > Определенные имена > Диспетчер имен.
Выберите имя, которое хотите изменить или удалить.
Выберите «Изменить» или «Удалить».
Удерживая нажатой клавишу Ctrl, щелкните левой кнопкой мыши каждую ячейку или диапазон, который вы хотите включить. Если вы выбрали слишком много, вы можете щелкнуть непреднамеренную ячейку, чтобы отменить выбор.
При выборе данных для Bing Maps убедитесь, что это данные о местоположении — названия городов, названий стран и т. д. В противном случае Bing нечего будет сопоставлять.
Чтобы выбрать ячейки, вы можете ввести ссылку в диалоговом окне "Выбор данных" или щелкнуть стрелку свертывания в диалоговом окне и выбрать ячейки, щелкнув их. См. предыдущие разделы для получения дополнительных сведений о выборе нужных ячеек.
Выбирая данные для конвертации в евро, убедитесь, что это данные о валюте.
Чтобы выбрать ячейки, введите ссылку в поле "Исходные данные" или нажмите стрелку свертывания рядом с полем, а затем выберите ячейки, щелкнув их. См. предыдущие разделы для получения дополнительных сведений о выборе нужных ячеек.
Вы можете выбрать соседние ячейки в Excel для Интернета, щелкнув ячейку и перетащив ее, чтобы расширить диапазон. Однако вы не можете выбрать определенные ячейки или диапазон, если они не находятся рядом друг с другом. Если у вас есть настольное приложение Excel, вы можете открыть свою книгу в Excel и выбрать несмежные ячейки, щелкнув их, удерживая нажатой клавишу Ctrl. Дополнительные сведения см. в разделе Выбор определенных ячеек или диапазонов в Excel.
Нужна дополнительная помощь?
Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.
Фото: saracote/iStock
Слишком много финансовых решений принимается без учета временной стоимости денег. Будь то консультирование по финансовому планированию, связанное с выходом клиента на пенсию, консультирование клиента по поводу возможностей для инвестиций в бизнес или расчет приведенной стоимости будущих арендных платежей, среди прочих задач, специалисты по бухгалтерскому учету должны понимать, как эффективно и точно выполнять соответствующие расчеты для анализ "время-ценность-деньги".
В этой статье приводятся примеры сценариев и объясняются различные подходы к расчету временной стоимости денег с помощью Microsoft Excel. Обсуждаемые здесь инструменты Excel включают функции FV , FVSCHEDULE , PV , NPV , PMT , RATE и NPER.
Для простоты в этой статье основное внимание уделяется примерам, связанным с личным финансовым планированием. CPA могут затем применять продемонстрированные функции и методы в других сценариях. Примеры включают расчет приведенной стоимости долгосрочной дебиторской задолженности, оценку обесценения деловой репутации, определение надлежащей цены продажи облигации и оценку внутренней нормы прибыли на решения по планированию капиталовложений.
ФУНКЦИИ ЗНАЧЕНИЯ ВРЕМЕНИ (FV И FVSCHEDULE)
Функции Excel FV и FVSCHEDULE можно использовать для расчета будущей стоимости денег, независимо от того, предполагает ли приложение единовременную выплату (т., один платеж или депозит) или аннуитет (т. е. несколько равных платежей или депозитов, сделанных через равные промежутки времени). Эти функции также можно использовать для определения ожидаемой будущей стоимости денежных вложений, IRA или счета 401(k).
ПРИМЕРЫ ИСПОЛЬЗОВАНИЯ FV И FVSCHEDULE
Вычисление будущей стоимости текущей единичной суммы
Ваш клиент имеет IRA на сумму 500 000 долларов США, и он попросил вас оценить его стоимость, когда клиент достигнет пенсионного возраста через восемь лет, предполагая ежегодный доход в размере 6 %.
Использование функций FV и FVSCHEDULE
Используя функцию FV, как показано в ячейке B12 снимка экрана «Использование функций FV и FVSCHEDULE», формула =FV(0,06,8,0,- 500000 ) используется для расчета IRA клиента. вырастет до 796 924 долларов к концу восьми лет, предполагая доходность 6% в год. Обратите внимание, что иногда переменные в функциях временной стоимости вводятся как отрицательные числа, например отрицательные 500 000 долларов США в этом сценарии, поскольку они представляют собой инвестиции, эквивалентные оттоку денежных средств.
Вычисление будущей стоимости настоящей единой суммы с несколькими процентными ставками
В этом примере показано, как использовать функцию FVSCHEDULE в Excel для расчета будущей стоимости текущей единой суммы с учетом изменения годовой нормы прибыли в течение периода накопления.
У вашего клиента есть сбережения в размере 500 000 долл. США, а до выхода на пенсию осталось восемь лет. В качестве инвестиционной стратегии клиент хотел бы со временем корректировать распределение активов своих инвестиций, переходя от более агрессивной стратегии в первые годы к более консервативному инвестиционному подходу по мере приближения к пенсионному возрасту. Таким образом, она планирует получать годовой доход в размере 10 % в течение первых двух лет инвестиционного периода и 8 %, 6 % и 4 % в течение каждого из следующих двухлетних периодов соответственно. Применяя функцию FVSCHEDULE, как показано в ячейке B30 на снимке экрана «Использование функций FV и FVSCHEDULE», формула = FVSCHEDULE (500000) рассчитывает, что остаток пенсионных сбережений клиента вырастет до 857 593 долларов США через восемь лет с использованием различных процентных ставок за это время. период.
Расчет будущей стоимости серии периодических депозитов (аннуитетных)
Давайте рассмотрим три примера расчета будущей стоимости аннуитета.
Расчет будущей стоимости ренты с помощью функции FV
Пример A: Ваш клиент хотел бы вносить 12 000 долларов на пенсионный счет в начале каждого года в течение следующих 20 лет, получая годовой доход в размере 6%. Формула в ячейке B13 на снимке экрана «Расчет будущей стоимости ренты с помощью функции FV» = FV (0,06,20,- 12000 ,0,1) рассчитывает, что пенсионный счет клиента вырастет до 467 913 долларов США в конце 20 лет. при условии доходности 6% в год. Обратите внимание, что Тип имеет код 1, так как платежи производятся в начале каждого года.
Пример Б. Более вероятным сценарием может быть то, что ваш клиент ежемесячно вносит депозит на свой пенсионный счет. Предположим, ваш клиент вносит депозиты в размере 1000 долларов в конце каждого месяца в течение 20 лет и зарабатывает 6% в год от своих инвестиций в течение этого времени. Применив формулу, как показано в ячейке B26 на снимке экрана «Расчет будущей стоимости ренты с помощью функции FV» = FV(0,005,240,- 1000 ,0,0), на его счете в конце будет 462 041 доллар. 20-летнего периода. Обратите внимание, что тип закодирован иначе, чем в примере A, потому что этот пример представляет обычный аннуитет, а не сценарий выплаты аннуитета.
Пример C. Еще более вероятный сценарий: ваши клиенты уже имеют пенсионные накопления и просят вас спрогнозировать будущую стоимость их текущих сбережений в сочетании с дополнительными ежемесячными взносами. Используя информацию из примера Б, давайте предположим, что ваш клиент уже накопил 200 000 долларов пенсионных сбережений. В этом случае вы должны ввести -200 000 долларов США в качестве суммы PV при использовании функции FV. Формула в ячейке B39 снимка экрана «Расчет будущей стоимости ренты с помощью функции FV» = FV(0,005,240,-1000,-200000,0) вычисляет будущую стоимость сбережений вашего клиента, включая существующие сбережения, составляет 1 124 082 доллара США при годовой доходности 6 %.
В этих примерах CPA просматривает расчеты со своими клиентами и оценивает, насколько быстро клиенты достигают своих пенсионных целей.Например, CPA может потребоваться посоветовать клиентам увеличить норму сбережений, отложить пенсионный возраст или изменить свою инвестиционную стратегию, чтобы соответствовать их планам образа жизни на пенсии. Excel можно легко модифицировать для проведения такого анализа «что, если».
ФУНКЦИИ СТОИМОСТИ ВРЕМЕНИ (PV И NPV)
Функция PV в Excel позволяет пользователям определить, сколько будут стоить будущие денежные потоки в сегодняшних долларах, независимо от того, предполагает ли приложение единовременную выплату или аннуитет. Эта концепция используется при попытке определить сегодняшнюю стоимость стоимости актива, который должен быть оплачен в будущем, или для расчета ежемесячных платежей по кредиту, среди прочих примеров. Функцию NPV можно использовать при расчете текущей стоимости неравных будущих денежных потоков.
ПРИМЕРЫ ИСПОЛЬЗОВАНИЯ PV И NPV
Расчет текущей стоимости будущей единичной суммы
Пример A. Клиент хочет накопить пенсионные накопления в размере 2 млн долл. США через семь лет. Она планирует внести на свой счет только один депозит, и ожидается годовой доход в размере 6% в год. Применяя функцию PV, формула в ячейке B12 снимка экрана «Использование функции PV» =PV(0,06,7,0,- 2000000 ) вычисляет, что клиент должен внести сегодня 1 330 114 долларов на свой пенсионный счет, чтобы достичь своей цели. Цель – 2 млн долларов США за семь лет.
Использование функции PV
Приведенные до сих пор примеры иллюстрируют использование финансовых функций Excel, учитывающих соотношение времени и денег, при предоставлении клиентам услуг по финансовому планированию. Финансовые функции Excel также могут быть полезны для CPA при предоставлении управленческих консультаций и услуг по заверению клиентов, либо при консультировании их о том, как регистрировать транзакции, либо, альтернативно, при оценке уместности бухгалтерских записей и финансовых отчетов клиента. Пример B ниже предоставляет один такой пример.
Пример Б. Клиент приобрел оборудование и подписал расписку, обещающую выплатить производителю оборудования 100 000 долларов США по истечении четырех лет. Клиент попросил вас помочь определить сумму, которую следует отразить в качестве стоимости оборудования, исходя из того, что стоимость капитала (т. е. годовая процентная ставка дисконта) составляет 6%. Результат формулы в ячейке B24 снимка экрана «Использование функции PV» =PV(0,06,4,0,- 100000 ) указывает, что стоимость оборудования должна быть записана как 79 209 долларов США.
Расчет текущей стоимости серии равных платежей (аннуитета)
Вместо единовременного платежа за покупку оборудования клиенту может потребоваться оплата в рассрочку.
Пример A. Ваш клиент подписал договор аренды оборудования, и вам необходимо оценить, капитализировал ли клиент соответствующую сумму, связанную с арендой. Соглашение требует четырех ежегодных арендных платежей в размере 25 000 долларов США в начале каждого года, а годовая процентная ставка составляет 8%. Результат формулы в ячейке B13 снимка экрана "Функция PV и функция NPV" =PV(0,08,4,- 25000 ,0,1) показывает, что арендованный актив должен быть капитализирован в размере 89 427 долларов США.
Функция PV и функция NPV
Расчет приведенной стоимости серии неравных платежей
Во многих случаях у клиента могут быть периодические, но неравные платежи. Если платежи не равны, функция PV в Excel не может быть использована для эффективного решения проблемы. Однако функция чистой приведенной стоимости (т. е. чистой приведенной стоимости) допускает неравные платежи.
Пример Б: предположим тот же сценарий, что и в примере А, за исключением того, что договор аренды предусматривает увеличение арендных платежей на 1000 долларов США каждый год. Применяя соответствующую функцию NPV для этого сценария, =NPV(0,08,26000,27000,28000)+25000 , вы можете рассчитать, что аренда будет капитализирована в размере 94 450 долларов США, как показано в ячейке B26 снимка экрана «Функция PV по сравнению с NPV». Функция."
ФУНКЦИЯ ЗНАЧЕНИЯ ВРЕМЕНИ (PMT)
Функция PMT в Excel может применяться при оказании помощи клиенту в определении суммы ежемесячных взносов для достижения цели выхода на пенсию или расчета ежемесячных платежей для погашения кредитного обязательства, среди прочих примеров.
ПРИМЕРЫ ИСПОЛЬЗОВАНИЯ PMT
Расчет годовых и ежемесячных платежей
Примеры A и B. Ваш клиент хочет, чтобы к концу 20 лет на его пенсионном счете был остаток в размере 1 млн долларов, а вы прогнозируете годовой доход от инвестиций в размере 6 %.Чтобы определить, как ваш клиент может достичь этой цели, можно применить формулу =ПЛТ(0,06,20,0,-1000000,0). Как показано в ячейке B13 снимка экрана «Использование функции PMT», требуемый ежегодный взнос клиента в конце каждого года будет составлять 27 185 долларов США. В качестве альтернативы, если у клиента уже есть пенсионные накопления в размере 100 000 долларов США, ячейка B26 на снимке экрана показывает, что требуемый годовой депозит составит всего 18 466 долларов США, рассчитанный по формуле =ПЛТ(0,06,20,100000,- 1000000 ,0) .
Просмотр таких расчетов может быть очень полезным для более молодых клиентов, которые только начинают откладывать пенсионные накопления. Этот анализ может показать им, насколько важно начать пенсионные накопления раньше, чтобы достичь своих целей.
Использование функции PMT
Пример C: Ваш клиент покупает дом за 300 000 долларов и хочет определить сумму ежемесячного платежа по ипотеке. Клиент может получить финансирование на сумму 240 000 долларов США сроком на 30 лет с фиксированной годовой процентной ставкой 4%. Применяя функцию PMT с формулой =PMT(0,00333,360,- 240000 ,0,0) , вы можете рассчитать, что ежемесячный платеж по ипотеке для вашего клиента составит 1146 долларов США, как показано в ячейке B39 снимка экрана «Использование PMT Функция». Если рассчитанный ежемесячный платеж превышает сумму бюджета вашего клиента, можно использовать функцию PMT в Excel для выполнения простого анализа «что — если» для обсуждения альтернативных вариантов для вашего клиента. Например, вы могли бы убедить своего клиента договориться о более низкой покупной цене, найти альтернативный дом в рамках бюджета или обеспечить более низкую процентную ставку по ипотеке.
ФУНКЦИЯ ЗНАЧЕНИЯ ВРЕМЕНИ (ЧАСТОТ)
Функция СТАВКА в Excel позволяет рассчитать годовую норму прибыли или процентную ставку, связанную со сценарием "время-ценность-деньги".
ПРИМЕРЫ ИСПОЛЬЗОВАНИЯ СТАВКИ
Расчет годовых и месячных процентных ставок
Примеры A и B. Ваш клиент хочет иметь остаток на пенсионном счете в размере 2 млн долларов США через 25 лет. Клиент планирует вносить ежемесячные депозиты в размере 1500 долларов и хочет знать годовой доход, необходимый для достижения ее цели. Пример A на снимке экрана «Использование функции RATE» иллюстрирует, как рассчитать месячный и годовой доход, предполагая, что депозиты на ее счет вносятся в конце каждого месяца. Применение формулы в ячейке B13 = СТАВКА (300,1500,0,- 2000000 ,0) показывает, что для достижения клиентом пенсионной цели потребуется ежемесячный доход в размере 0,836% (или годовой доход в размере 10,03%). $2 млн за 25 лет. В качестве альтернативы, если у клиента уже есть пенсионные накопления в размере 100 000 долларов, формула = СТАВКА (300 1500, 100 000, - 2 000 000, 0) применяется в ячейке B28 для расчета того, что ему нужно будет зарабатывать только месячный доход в размере 0,633% (или годовой доход в размере 7,60%), чтобы достичь своей цели.
Использование функции СТАВКА
Опять же, вы можете выполнить простой анализ «что, если» в Excel при рассмотрении пенсионных планов с клиентом. Например, если вы и ваш клиент согласны с тем, что получение 10% годового дохода в течение инвестиционного периода нереально, то можно изучить альтернативные варианты. Например, возможно, вашему клиенту потребуется рассмотреть вопрос о ликвидации некоторых активов, таких как продажа мотоцикла, на котором она редко ездит, и вложить вырученные средства на свой пенсионный счет сейчас, чтобы уменьшить ежемесячный или годовой доход, который ей необходимо будет получать.
Пример C. Бюджет клиента составляет 1600 долларов США для ежемесячного платежа по ипотеке, и он рассматривает возможность покупки дома, для которого потребуется ипотечный кредит в размере 325 000 долларов США. Он хотел бы рассчитать годовую процентную ставку по ипотеке, которую он должен получить, чтобы купить дом с 30-летней ипотекой. Формула =СТАВКА(360,1600,- 325000 ,0,0) применяется в ячейке B43 снимка экрана «Использование функции СТАВКА» для расчета требуемой месячной ставки 0,355% или годовой процентной ставки 4,26%, чтобы уложиться в его бюджет.
ФУНКЦИЯ ЗНАЧЕНИЯ ВРЕМЕНИ (КПЕР)
Если ваш клиент хочет определить, сколько месяцев или лет потребуется для достижения цели пенсионных накоплений, или рассчитать количество месяцев или лет, которое потребуется для погашения существующего финансового обязательства, можно применить функцию КПЕР в Excel. .
ПРИМЕРЫ ИСПОЛЬЗОВАНИЯ КПЕР
Подсчет количества месяцев или лет
Примеры A и B. Клиент хочет знать, сколько времени потребуется, чтобы он достиг пенсионного остатка в размере 1 миллиона долларов США, если он планирует откладывать 1250 долларов США в месяц и получать 10 % годового дохода (или 0,8333 % месячного дохода). ). Формула =КПЕР(0,008333,1250,0,- 1000000 ,1) применяется в ячейке B13 снимка экрана «Использование функции КПЕР» для расчета того, что для достижения ее пенсионная цель. Если у клиента уже есть 100 000 долларов пенсионных накоплений, его цель будет достигнута примерно через 183 месяца, или 15 лет и четыре месяца, как показано в ячейке B28 и рассчитано по формуле =КПЕР(0,008333,1250,100000,- 1000000 ,1) .
Использование функции КПЕР
Пример C: предположим, что клиент, упомянутый в примере C на снимке экрана «Использование функции PMT», хотел бы выплатить свою ипотеку под 4%, 240 000 долларов быстрее, чем ее 30-летний срок. Она планирует платить дополнительно 100 долларов в месяц сверх необходимого платежа в 1146 долларов и хочет знать, на сколько месяцев сократится срок ее ипотеки. Применяя формулу =КПЕР(0,003333,1246,- 240000 ,0,0) , ячейка B43 снимка экрана «Использование функции КПЕР» показывает, что, платя дополнительные 100 долларов в месяц, она будет делать в общей сложности 309 ежемесячных платежей, сокращение срока ее ипотеки на четыре года и три месяца.
Опять же, каждый из приведенных выше примеров может привести к разговору между вами и вашими клиентами о разумном финансовом планировании. Например, если ваш клиент слишком амбициозен, планируя получать прибыль в размере 10% в год в течение своего инвестиционного периода, это может отсрочить дату выхода на пенсию, если фактическая норма прибыли будет меньше. Excel может помочь вам эффективно продемонстрировать клиентам, как планы изменятся, если будут изменены предположения. Этот анализ «что, если» также может помочь клиентам продемонстрировать преимущества неоткладывания пенсионных инвестиций, а также преимущества дополнительных платежей по ипотечным кредитам.
Excel – это мощный инструмент, который можно использовать для анализа различных приложений с точки зрения затрат времени и денег. Хотя эта статья в основном посвящена вариантам использования личного финансового планирования, бухгалтеры могут применять методы и функции Excel, показанные в этой статье, также для анализа бизнес-ситуаций.
Профессионалы в области бухгалтерского учета должны помнить, что жизнь и бизнес происходят не в Excel. Необходимо постоянно вносить коррективы, чтобы учитывать изменения, которые приносит жизнь. Например, при личном финансовом планировании изменения в доходах клиентов, образе жизни, состоянии здоровья и т. д. потребуют пересмотра планов клиентов, изложенных в Excel. Хотя обсуждение всех обстоятельств, которые могут возникнуть, выходит за рамки этого семинара по технологиям, функции Excel по оценке времени и денег могут справиться со многими более сложными ситуациями.
Читайте также: