Многие люди рассматривают Excel как инструмент, который хорош только для бизнес-приложений. , но когда вы увидите простые и полезные формулы ниже, вы увидите, что Excel также может помочь вам решить реальные проблемы дома.
Если вы покупаете новую ссуду на покупку автомобиля, хотите выяснить, какие инвестиционные фонды лучше всего подходят вам, или если вы просто пытаетесь найти смысл в своем банковском счете, Excel — это мощный инструмент, который может вам помочь.
15 формул, приведенных ниже, были тщательно отобраны как для простоты, так и для того, насколько они эффективны, помогая обычным людям решать сложные проблемы. Если вы учитель, который пытается выяснить оценки учеников, родитель, который пытается справиться с вашими расходами на домашнее хозяйство, или молодая пара, делающая покупки для вашего первого займа на дому, приведенные ниже формулы могут помочь.
Финансовые формулы
Ходить по магазинам для нового дома и смутили все ипотечный жаргон? Ищете новый автомобиль и запутываетесь в условиях автокредитования, которые вам постоянно подбрасывает продавец?
Не бойся Прежде чем начать делать покупки, сделайте свое исследование в Excel на вашей стороне!
PMT — Оплата
Всякий раз, когда вы сравниваете какие-либо условия кредитования и хотите быстро рассчитать фактический ежемесячный платеж с учетом различных вариаций условий, воспользуйтесь мощной (и простой) формулой PMT .
Вот что вам нужно, чтобы использовать эту формулу:
- процентная ставка по кредиту
- срок кредита (сколько платежей?)
- начальный принцип кредита
- будущая стоимость, если по какой-то причине кредит будет считаться погашенным до того, как он достигнет нуля (необязательно)
- тип кредита — 0, если платежи должны быть выполнены в конце каждого месяца, или 1, если они должны быть выполнены в начале (необязательно)
Вот крутой способ быстро сравнить различные кредиты, чтобы увидеть, как будут выглядеть ваши платежи; просто создайте лист Excel, в котором перечислены все потенциальные кредиты и вся доступная информация о них. Затем создайте столбец «Платежи» и используйте формулу PMT.
Просто возьмите правый нижний угол ячейки PMT, которую вы только что создали, и перетащите ее вниз, чтобы вычислить общую сумму платежей для всех условий кредитования, перечисленных в листе.
Как вы можете видеть, когда вы можете сравнить условия кредитования бок о бок, проявляются некоторые суровые реалии. Например, для автокредита в размере 19 500 долларов, если вы потратите время, чтобы осмотреться и получить кредит, который всего на несколько процентных пунктов ниже, это может сэкономить вам много денег каждый месяц и тысячи долларов в течение жизни кредит.
(Огромное спасибо Марку Джонсу (@redtexture в Twitter), который указал, что для формул PMT и FV нужно быть очень осторожным при использовании одного и того же периода — в этом случае использование ежемесячных платежей требует деления процентного условия к 12 месяцам)
Вот почему наши читатели так хороши — спасибо за помощь в этом исправлении, Марк!
FV — будущая стоимость
Следующая формула пригодится, когда вы хотите вложить деньги в что-то вроде компакт-диска (депозитный сертификат), и вы хотите знать, сколько будет стоить компакт-диск в конце срока.
Вот что вам нужно знать, чтобы использовать формулу FV :
- процентная ставка по кредиту
- количество платежей (или срок инвестирования в месяцах)
- оплата за каждый период (обычно ежемесячно)
- текущий начальный баланс (необязательно)
- тип кредита — 0, если платежи должны быть выполнены в конце каждого месяца, или 1, если они должны быть выполнены в начале (необязательно)
Итак, давайте сравним несколько банковских компакт-дисков, используя термины, которые вы знаете из информации, которую вам дали банки. В приведенном ниже примере предположим, что у вас есть наследство в 20 000 долларов, которое вы можете инвестировать в CD.
Как видите, процентные ставки снова представлены в десятичном формате (возьмите процентную ставку, которую банк дал вам, и разделите на 100), а платежи равны нулю, потому что CD обычно основаны на начальном и выплаченном будущем значении. Вот как выглядит сравнение, когда вы используете формулу FV для каждого рассматриваемого CD.
Без сомнения, CD с более высоким интересом за более длительный период окупается значительно больше. Единственным недостатком, конечно, является то, что вы не можете касаться своих денег в течение трех лет. Для многих людей это слишком долго, но такова природа инвестиций!
Логические функции (IF или AND)
Большинство банков в наши дни дают вам возможность загружать банковские транзакции почти на год в таком формате, как CSV. Это идеальный формат для анализа ваших расходов с помощью Excel, но иногда данные, которые вы получаете от банков, очень дезорганизованы.
Использование логических функций — отличный способ определить перерасход .
В идеале банк либо автоматически классифицирует ваши расходы, либо вы настроили свою учетную запись так, чтобы вещи были распределены по категориям расходов. Например, любые рестораны, в которые мы идем, помечают ярлыком «DiningOut».
Это облегчает использование логической функции для определения того, когда мы идем кушать и тратить более 20 долларов.
Для этого просто создайте логическую функцию в новом столбце и найдите любое значение, для которого столбец категории имеет значение «DiningOut», а столбец транзакции больше — $ 20 (приведенное ниже сравнение показывает «<«, меньше, чем, потому что значения в столбце C все отрицательные. Вот как это выглядит:
Совместное использование IF и AND в одной формуле выглядит сложно, но если подумать, это имеет смысл. Оператор IF выведет сумму в долларах (C2), если инструкция AND верна, или FALSE, если это не так. Оператор AND проверяет, является ли категория «DiningOut», а сумма транзакции превышает 20 долларов США.
Вот оно! Без необходимости вручную просеивать все эти транзакции, вы теперь точно знаете, когда вы перерасходовали деньги в определенной категории. Как обычно, формулы Excel оказываются огромной экономией времени.
Осмысление списков
Списки — большая часть повседневной жизни. Если вы управляете домохозяйством, вы постоянно используете списки.
Может быть, вы находитесь в комитете родителей и учителей, и вам был отправлен длинный список пожертвований сообщества. Вы сами должны выяснить, кто в сообществе пожертвовал на конкретный школьный проект и сколько. Или, может быть, вы управляете семейным бизнесом и выплачиваете заработную плату полудюжине сотрудников, которых вы нанимаете, и должны суммировать их индивидуальные расходы.
COUNT и COUNTIF
Независимо от списка, Excel может помочь вам быстро организовать и отсортировать значения. Давайте возьмем пример с PTC. Вот список пожертвований от членов сообщества.
Если вы хотите увидеть, сколько раз имя человека появляется в списке, вы можете объединить формулу COUNT с формулой IF. Итак, сначала создайте столбец, чтобы проверить, является ли человек Мишель или нет. Если это так, сделайте ячейку равной 1. Если это не так, сделайте ее пробелом »«.
Затем создайте еще один столбец, который подсчитывает, сколько раз вы нашли Мишель Джонсон в списке.
Это дает вам счет каждого места в столбце E, где есть 1, а не пробел.
Итак, это самый простой способ сделать это, но он требует двух шагов.
Если вы не возражаете против использования немного более продвинутой функции, вы можете рассмотреть возможность использования одной из множества комбинированных функций «IF», таких как SUMIF , COUNTIF или AVERAGEIF . Они позволяют выполнять функцию (COUNT, SUM или AVERAGE) только в том случае, если логическое условие истинно. Вот как это работает, используя приведенный выше пример.
Эта формула смотрит на столбец A, который содержит все имена доноров, и если ячейка в диапазоне соответствует критерию в кавычках, то она считается на единицу. Это дает вам подсчет всех случаев, когда имя донора равно «Мишель Джонсон» за один шаг.
Это гораздо быстрее, чем использование двух столбцов, но для некоторых людей это может быть слишком сложно — поэтому используйте подход, который лучше всего подходит для вашей ситуации.
Функция LEN
Еще одна функция, которую вы иногда можете использовать творчески, это функция LEN. Эта функция сообщает, сколько символов содержится в строке текста.
Один интересный способ использовать это в приведенном выше примере — выделить доноров, которые пожертвовали более 1000 долларов, подсчитав количество цифр в столбце пожертвований.
Затем, если вы хотите, вы можете даже отформатировать другой столбец на основе значения в столбце E; результат функции LEN.
Для этого вам нужно выделить все ячейки в столбце «Пожертвование», выбрать вкладку « Главная » в меню и нажать « Условное форматирование» на панели инструментов. Затем выберите « Использовать формулу», чтобы определить ячейки для форматирования .
Установите диапазон в поле « Значения формата», где эта формула верна: для столбца / диапазона, где отображаются все выходные данные вашей функции LEN.
В этом примере, если вы зададите условие «> 3», то все, что превышает 1000 долларов, получит специальное форматирование. Не забудьте нажать кнопку « Форматировать…» и выбрать, какое специальное форматирование вы хотите для них.
Также быстрая заметка. Вы заметите, что мой диапазон определяется как «$ E2: $ E11», а не «$ E $ 2: $ E $ 11». По какой-то причине, когда вы выбираете диапазон, по умолчанию используется первый, который не будет работать. Вам необходимо использовать относительную адресацию, как показано на рисунке выше. Тогда ваше условное форматирование будет работать в зависимости от условия второго диапазона.
Организация банковских и финансовых загрузок
Иногда, когда вы загружаете информацию с предприятий — будь то ваш банк, ваша страховая медицинская компания или где-то еще, где вы ведете бизнес, формат входящих данных не всегда соответствует тому, что вам нужно.
Например, допустим, что в экспортированных данных из вашего банка вам дается дата в стандартном формате.
Если вы хотите добавить собственный новый столбец со своим собственным, который начинается с года и включает информацию о получателе (для ваших собственных целей сортировки), извлечь части информации из столбца очень просто.
ВПРАВО, ВЛЕВО, ТЕКСТ И СОГЛАСОВАТЬ
Вы можете извлечь год из текста в этом столбце, используя функцию RIGHT .
Функция выше говорит Excel, чтобы взять текст в столбце D и извлечь четыре символа с правой стороны. Функция CONCATENATE объединяет эти четыре цифры с текстом получателя из следующего столбца.
Имейте в виду, что если вы хотите извлечь текст из даты, вам нужно будет преобразовать его в текстовый формат (вместо даты), используя функцию «= TEXT (D2,« mm / dd / yyyy »)». Затем вы можете использовать ПРАВИЛЬНУЮ функцию, чтобы выбрать год.
CONCATENATE действительно пригодится, когда у вас есть текст из нескольких разных столбцов, которые вы хотите объединить в одну длинную строку. Например, вы можете объединить имя, фамилию, почтовый адрес, город и штат человека в поле, содержащее полный адрес человека в одном столбце.
Выбор случайных имен из шляпы
Последняя забавная функция — та, которую вы можете использовать, если вам нужно что-то сделать, например выбрать несколько имен из шляпы. на рождественскую вечеринку. Уберите эту шляпу и эти клочки бумаги, вытащите ноутбук и запустите Excel!
Используя функцию RANDBETWEEN, вы можете сделать так, чтобы Excel случайным образом выбрал число в диапазоне заданных вами чисел.
Два значения, которые вам нужно использовать, это наименьшее и наибольшее числа, которые должны быть в конце диапазона чисел, которые вы применили к имени каждого человека.
Как только вы нажмете клавишу Enter, функция случайным образом выберет одно из чисел в диапазоне.
Он настолько случайный и защищенный от взлома, насколько это возможно. Поэтому вместо того, чтобы выбирать номер из шапки, выберите номер из Excel!
Использование Excel для повседневных задач
Как видите, Excel не только для гуру анализа данных и профессионалов бизнеса. Любой может извлечь выгоду из множества функций, которые вы найдете в Excel .
Дала ли вам эта статья креативные идеи о том, как вы можете использовать Excel для решения проблем в своей жизни? Вы когда-нибудь использовали некоторые из этих функций Excel ранее творчески? Поделитесь своими идеями и историями в разделе комментариев ниже, я хотел бы услышать их!
Кредит изображения: Goodluz через Shutterstock.com