7 полезных формул для тех, кто считает деньги в эксель-таблице
Курс по таблицам Excel
Мы много писали о том, как вести бюджет в эксель-таблицах, но не о самих функциях программы. Пришла пора собрать инструменты, которые помогут составить идеальную таблицу — чтобы подтягивала актуальный курс валют и показывала, на чем сэкономить, чтобы быстрее накопить нужную сумму.
Вот семь формул, которые открывают только малую часть величия программы, но зато понятны не только экономистам. Они помогут вести бюджет, составлять бизнес-планы и экономить время.
Соединить текст из разных ячеек
Иногда надо быстро собрать данные из разных ячеек в одной. Поочередно копировать долго и неудобно, поэтому лучше использовать формулу с амперсандом — знаком «&».
Для этого выберите ячейку, в которой хотите соединить информацию, и перечислите через & номера нужных ячеек.
Подобрать значения для нужного результата
Эксель помогает не только собирать данные, но и планировать достижение целей. Например, если вы ведете в программе бюджет, можно задать сумму, которую планируете накопить за год, и в одно действие посчитать, насколько нужно уменьшить траты на кофе.
Для этого на вкладке «Данные» надо выбрать «Анализ „Что если“», с помощью функции «Подбор параметра» задать целевое значение и выбрать ячейку, которую нужно изменить для получения желаемой цифры.
Эта же формула работает в обратную сторону: если вы решили открыть магазин и рассчитываете на определенную прибыль, то функция покажет, сколько товаров и по какой цене нужно продавать.
Обновить курс валют
Если планируете в таблицах путешествие или копите деньги в разных валютах, неудобно все пересчитывать при каждом колебании курса. Для этого эксель научили подтягивать актуальный курс валют с профильных сайтов.
Чтобы использовать эту функцию, на вкладке «Данные» выберите кнопку «Из интернета» и вставьте адрес надежного источника, например cbr.ru. Эксель предложит выбрать, какую именно таблицу нужно загрузить с сайта — отметьте нужную галочкой.
После вставки таблицы вы можете использовать ячейку с данными для формул и настроить автоматическое обновление курса.
Планировать действия
Теперь, когда курсы валют и стоимость акций подгружаются автоматически, эксель становится динамичным рабочим инструментом. Но можно пойти дальше и требовать от него реакций — и даже советов! — при определенных изменениях. Для этого понадобится функция «Если». Она заполняет ячейки заданными значениями в зависимости от того, что происходит в остальной таблице.
Например, она может подсказать, когда выгодно продавать и покупать акции, если вы настроите нужный уровень цен. Для этого в формулу через точку с запятой добавляем условие — цена акции выросла до определенного значения — и нужную реакцию программы — подсказку «продавать».
Вот так: =ЕСЛИ (ячейка с ценой акции ≥ цена выгодной продажи; "продавать"; ЕСЛИ (ячейка с ценой акции ≤ цена выгодной покупки; "покупать"; "ничего")).
Таких значений и условий можно добавить в одну формулу до 255 штук, а последнее «ничего» выпадает, когда цена акции не соответствует ни одному из условий.
Выделить цветом нужные данные
Когда таблицы большие и многое происходит автоматически, легко пропустить что-то важное. От этого спасает функция выделения цветом. Для ее активации на главной вкладке выберите «Условное форматирование» и задайте условия и цвет выделения.
Например, можно подсветить месяцы, когды вы тратили больше, чем зарабатывали, или задать цветовое кодирование для каждой категории расхода. В этом случае ячейка может быть зеленой, пока вы вписываетесь в бюджет, желтой, если на грани, и красной, когда вы вышли за лимит.
Суммировать только нужное
Если вы ведете в экселе бюджет семьи или бухгалтерию бизнеса, иногда нужно сделать небольшую выборку. Например, подсчитать, кто из семьи тратит больше на спонтанные покупки, или выяснить, сколько капучино продается в определенной точке.
Мы попробуем узнать, сколько Аня тратит на еду в офисе. Для этого в таблице создаем формулу =СУММ((А2:А16=F2)*(B2:B16=F3)*C2:С16) и получаем 915 рублей. Теперь постепенно.
В первой скобке программа ищет значение из ячейки F2 («Аня») в столбце с именами. Во второй скобке — значение из ячейки F3 («Еда на работе») из столбца с категориями расходов. А после считает сумму ячеек из третьего столбца, которые выполнили эти условия.
Расставить по порядку
В экселе можно быстро узнать максимальное, минимальное и среднее значение для любого массива ячеек. Для этого в скобках формул =МАКС(), =МИН() и =СРЗНАЧ() нужно указать диапазон ячеек, в которых будет искать программа. Это пригодится для таблицы, в которую вы записываете все расходы: вы увидите, на что потратили больше денег, а на что — меньше. Еще этим тратам можно присвоить «места» — и отдать почетное первое место максимальной или минимальной сумме.
Например, вы считаете зарплаты сотрудников и хотите узнать, кто заработал больше за определенный срок. Для этого в скобках формулы =РАНГ() через точку с запятой укажите ячейку, порядок которой хотите узнать; все ячейки с числами; 1, если нужен номер по возрастанию, или 0, если нужен номер по убыванию.
Уверены, теперь вы сможете прокачать наши таблицы до максимального уровня:
17.12.19, 18:39
Нееееет, только не очередная хрень из серии 7 приёмов, которые не поймут новички и дадут понять, что автор вообще не в теме Excel.
Вы взяли самый популярный пост в ВКонтакте и бездумно сделали рерайт. Причём очень плохого качества.
Вот список самых необходимых функций:
- суммесли/мн ; счётесли/мн
- впр (индекс + поискпоз для среднячков)
- левсимв; правсимв; пстр
- чистрабдни; рабдень
Инструменты:
- сводные таблицы
- условное форматирование
- текст по столбцам
Вот их изучите и цены вам как специалистам не будет!)
18.12.19, 01:18
Якушев, к левсимв и правсимв тогда уж сразу можно разобрать длстр и поиск
16.12.19, 15:07
Записываю платежи по ипотеке. Оцениваю, сколько уже вложил в квартиру суммарно кэша и какой фактический процент годовых получается с учетом досрочек. Бесполезно, но любопытно.
16.12.19, 14:40
Забыли написать про чудесные функции поиска по индексу типа ВПР, работает как поиск по внешнему ключу для связи двух таблиц.
17.12.19, 13:08
Андрей, но ищут только права) индекс+Поискпоз лучше в этом плане, да и работает на 25% быстрее, на больших массивах (100к строк). А вообще для многих ситуаций с числами можно через суммеслимн или суммесли дергать значения😉
16.12.19, 17:29
В пункте "суммировать только нужное" формула работать не должна без еще одной скобки и без комбинации клавиш shift+ctrl+enter. Ну или я не знаю каких-то новшеств в excel2020 :)
17.12.19, 04:46
AlenaStribog, спасибо, Алена! Скобочка-беглянка схвачена и возвращена на место)
17.12.19, 13:05
AlenaStribog, можно проще, через суммеслимн. Тогда конструкция будет не такой страшной
17.12.19, 17:09
Georgy, суммеслимн довольно ограничена в использовании. Если уж на то пошло, то лучше пользоваться суммпроизв. Она более универсальна
17.12.19, 06:37
AlenaStribog, у меня все запускается одним enter.
Это Microsoft Office 365, Версия 1911
16.12.19, 18:18
Из действительно полезного только подтягивание курсов валют и стоимости акций. Остальное достаточно базовое или мало кому пригодится.
Хотя, возможно, это мне слишком много приходится работать с Экселем.
11.01.20, 12:54
Sergei, так и не поняла, как подтянуть курс валют
21.01.20, 09:57
Irina, вот так
=IMPORTXML("https://ru.investing.com/currencies/usd-rub";"//span[@id='last_last']")
17.12.19, 07:17
По первой функции практичнее команда "СЦЕПИТЬ", где можно задать еще и пробелы между ячейками. Например, =СЦЕПИТЬ(F02;" ";G02;" ";H02), где " " - это и есть пробел.
17.12.19, 07:45
Павел, с пробелами и там можно =A1&" "&A2&" "&A3
17.12.19, 07:51
VVVas, да, всё верно, так тоже работает. Спасибо!
17.12.19, 10:53
А как же power pivot??
17.12.19, 08:05
тема получения данных, не раскрыта :)
ещё можно, суммеслимн, еслимн, индекс, и многое другое рассмотреть :) сводные таблицы.
эх.
13.01.20, 22:04
Только что поняла, что хотела поставить лайк статье, а есть только функция репост. Ну что же, тогда вот мой вам словесный лайк :)
16.12.19, 17:38
"Суммировать только нужно" в данном описании будет работать только функией массива, через Ctr+Shift+Enter, что уже advanced level.
И да, пропустили открывающую скобку по тексту (на скриншоте всё ок).
С данной задачей прекрасно справится =SUMIFS (=СУММЕСЛИМН)
17.12.19, 14:39
Веду управленчиский учёт компании
18.12.19, 02:31
Таймер до определённого события, самозаподняющийся календарь. Да вообще кучу всего где нужно прибавить/умножить/разделить или расчётно спрогнозировать
21.12.19, 20:16
"в таблице создаем формулу =СУММ((А2:А16=F2)*(B2:B16=F3)*C2:С16)" -- СУММ здесь зачем?)
20.01.20, 12:07
255 условий ЕСЛИ удобнее заменить на ВПР или ГПР
27.12.20, 15:16
для финансовых моделей при разработке бизнес-планов, ТЭО и т.п.
11.04.21, 14:28
Для примера в журнале Тинькова подскажите как подтянуть курс доллара обычный и премиум из самого Тиньков банка. Не уверен что у Вас получится - рад буду ошибиться
01.07.21, 17:34
Приветствую. Была бы благодарна за подсказку формул для автоматического расчета сколько акций нужно докупить и какую сумму инвестировать. (формула данная в поле не работает).
22.01.23, 11:36
Я веду бюджет уже много лет в гугл таблицах, у меня один документ и на каждый месяц новый лист. есть ли какая-то возможность за какой-то период собрать итоговые данные с одного столбца, например за последние три года за все месяцы хозрасходы?