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

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

Курс по таблицам Excel
28
Аватар автора

Дмитрий Шаров

король таблиц

Страница автора

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

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

Соединить текст из разных ячеек

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

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

Соединение текста экономит скорее время, чем деньги, но при правильном подходе это легко конвертировать
Соединение текста экономит скорее время, чем деньги, но при правильном подходе это легко конвертировать

Подобрать значения для нужного результата

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

Для этого на вкладке «Данные» надо выбрать «Анализ „Что если“», с помощью функции «Подбор параметра» задать целевое значение и выбрать ячейку, которую нужно изменить для получения желаемой цифры.

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

«Подбор параметра» подойдет и для составления бизнес-плана. Введите желаемую прибыль и посчитайте, сколько единиц товара и с какой накруткой нужно продавать
«Подбор параметра» подойдет и для составления бизнес-плана. Введите желаемую прибыль и посчитайте, сколько единиц товара и с какой накруткой нужно продавать

Обновить курс валют

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

Чтобы использовать эту функцию, на вкладке «Данные» выберите кнопку «Из интернета» и вставьте адрес надежного источника, например cbr.ru. Эксель предложит выбрать, какую именно таблицу нужно загрузить с сайта — отметьте нужную галочкой.

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

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

Планировать действия

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

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

Вот так: =ЕСЛИ (ячейка с ценой акции ≥ цена выгодной продажи; "продавать"; ЕСЛИ (ячейка с ценой акции ≤ цена выгодной покупки; "покупать"; "ничего")).

Таких значений и условий можно добавить в одну формулу до 255 штук, а последнее «ничего» выпадает, когда цена акции не соответствует ни одному из условий.

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

Выделить цветом нужные данные

Когда таблицы большие и многое происходит автоматически, легко пропустить что-то важное. От этого спасает функция выделения цветом. Для ее активации на главной вкладке выберите «Условное форматирование» и задайте условия и цвет выделения.

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

Также можно выделить значения, которые находятся в определенном интервале (в условиях форматирования — «между»), содержат нужный текст («текст содержит»), или задать сразу несколько условий
Также можно выделить значения, которые находятся в определенном интервале (в условиях форматирования — «между»), содержат нужный текст («текст содержит»), или задать сразу несколько условий

Суммировать только нужное

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

Мы попробуем узнать, сколько Аня тратит на еду в офисе. Для этого в таблице создаем формулу =СУММ((А2:А16=F2)*(B2:B16=F3)*C2:С16) и получаем 915 рублей. Теперь постепенно.

В первой скобке программа ищет значение из ячейки F2 («Аня») в столбце с именами. Во второй скобке — значение из ячейки F3 («Еда на работе») из столбца с категориями расходов. А после считает сумму ячеек из третьего столбца, которые выполнили эти условия.

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

Расставить по порядку

В экселе можно быстро узнать максимальное, минимальное и среднее значение для любого массива ячеек. Для этого в скобках формул =МАКС(), =МИН() и =СРЗНАЧ() нужно указать диапазон ячеек, в которых будет искать программа. Это пригодится для таблицы, в которую вы записываете все расходы: вы увидите, на что потратили больше денег, а на что — меньше. Еще этим тратам можно присвоить «места» — и отдать почетное первое место максимальной или минимальной сумме.

Например, вы считаете зарплаты сотрудников и хотите узнать, кто заработал больше за определенный срок. Для этого в скобках формулы =РАНГ() через точку с запятой укажите ячейку, порядок которой хотите узнать; все ячейки с числами; 1, если нужен номер по возрастанию, или 0, если нужен номер по убыванию.

При помощи четырех формул мы узнали минимальную, максимальную и среднюю зарплату промоутеров, а также расположили всех сотрудников по возрастанию оклада
При помощи четырех формул мы узнали минимальную, максимальную и среднюю зарплату промоутеров, а также расположили всех сотрудников по возрастанию оклада

Уверены, теперь вы сможете прокачать наши таблицы до максимального уровня:

  1. Экселька, которая ведет семейный бюджет.
  2. Помогает выбрать что угодно.
  3. И считает доходность по вкладам.
Дмитрий ШаровА для чего вы используете эксель?
  • Андрей КуксовЗабыли написать про чудесные функции поиска по индексу типа ВПР, работает как поиск по внешнему ключу для связи двух таблиц.5
  • Бежит_орётЗаписываю платежи по ипотеке. Оцениваю, сколько уже вложил в квартиру суммарно кэша и какой фактический процент годовых получается с учетом досрочек. Бесполезно, но любопытно.9
  • AlenaStribog FВ пункте "суммировать только нужное" формула работать не должна без еще одной скобки и без комбинации клавиш shift+ctrl+enter. Ну или я не знаю каких-то новшеств в excel2020 :)3
  • Rostislav Lushnikov"Суммировать только нужно" в данном описании будет работать только функией массива, через Ctr+Shift+Enter, что уже advanced level. И да, пропустили открывающую скобку по тексту (на скриншоте всё ок). С данной задачей прекрасно справится =SUMIFS (=СУММЕСЛИМН)0
  • Удивительно, ноИз действительно полезного только подтягивание курсов валют и стоимости акций. Остальное достаточно базовое или мало кому пригодится. Хотя, возможно, это мне слишком много приходится работать с Экселем.3
  • Екатерина ТабатчиковаAlenaStribog, спасибо, Алена! Скобочка-беглянка схвачена и возвращена на место)2
  • Дмитрий ШаровAlenaStribog, у меня все запускается одним enter. Это Microsoft Office 365, Версия 19110
  • Павел ГоршенинПо первой функции практичнее команда "СЦЕПИТЬ", где можно задать еще и пробелы между ячейками. Например, =СЦЕПИТЬ(F02;" ";G02;" ";H02), где " " - это и есть пробел.2
  • VVVasПавел, с пробелами и там можно =A1&" "&A2&" "&A34
  • Павел ГоршенинVVVas, да, всё верно, так тоже работает. Спасибо!0
  • Денис Улановскийтема получения данных, не раскрыта :) ещё можно, суммеслимн, еслимн, индекс, и многое другое рассмотреть :) сводные таблицы. эх.1
  • Elena KrasnovaА как же power pivot??2
  • Georgy BaturinAlenaStribog, можно проще, через суммеслимн. Тогда конструкция будет не такой страшной1
  • Georgy BaturinАндрей, но ищут только права) индекс+Поискпоз лучше в этом плане, да и работает на 25% быстрее, на больших массивах (100к строк). А вообще для многих ситуаций с числами можно через суммеслимн или суммесли дергать значения😉1
  • serj bronВеду управленчиский учёт компании0
  • AlenaStribog FGeorgy, суммеслимн довольно ограничена в использовании. Если уж на то пошло, то лучше пользоваться суммпроизв. Она более универсальна0
  • Якушев ДмитрийНееееет, только не очередная хрень из серии 7 приёмов, которые не поймут новички и дадут понять, что автор вообще не в теме Excel. Вы взяли самый популярный пост в ВКонтакте и бездумно сделали рерайт. Причём очень плохого качества. Вот список самых необходимых функций: - суммесли/мн ; счётесли/мн - впр (индекс + поискпоз для среднячков) - левсимв; правсимв; пстр - чистрабдни; рабдень Инструменты: - сводные таблицы - условное форматирование - текст по столбцам Вот их изучите и цены вам как специалистам не будет!)18
  • Georgy BaturinЯкушев, к левсимв и правсимв тогда уж сразу можно разобрать длстр и поиск2
  • Афанасий ФетТаймер до определённого события, самозаподняющийся календарь. Да вообще кучу всего где нужно прибавить/умножить/разделить или расчётно спрогнозировать0
  • Романтик из подворотни"в таблице создаем формулу =СУММ((А2:А16=F2)*(B2:B16=F3)*C2:С16)" -- СУММ здесь зачем?)0
  • Irina PotopalskayaSergei, так и не поняла, как подтянуть курс валют0
  • Александра КочановаТолько что поняла, что хотела поставить лайк статье, а есть только функция репост. Ну что же, тогда вот мой вам словесный лайк :)1
  • Танюшка Зубрилина255 условий ЕСЛИ удобнее заменить на ВПР или ГПР0
  • Николай НеводничIrina, вот так =IMPORTXML("https://ru.investing.com/currencies/usd-rub";"//span[@id='last_last']")1
  • Наталья Мечиевадля финансовых моделей при разработке бизнес-планов, ТЭО и т.п.0
  • Андрей АндрейДля примера в журнале Тинькова подскажите как подтянуть курс доллара обычный и премиум из самого Тиньков банка. Не уверен что у Вас получится - рад буду ошибиться0
  • Victoria KrasyukПриветствую. Была бы благодарна за подсказку формул для автоматического расчета сколько акций нужно докупить и какую сумму инвестировать. (формула данная в поле не работает).0
  • ЕленаЯ веду бюджет уже много лет в гугл таблицах, у меня один документ и на каждый месяц новый лист. есть ли какая-то возможность за какой-то период собрать итоговые данные с одного столбца, например за последние три года за все месяцы хозрасходы?0