Содержание

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

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

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

[expert_bq id=»1570″]Это ограничивает сколько-нибудь серьезное использование новых возможностей для отслеживания доходности ценной бумаги или набора ценных бумаг портфеля. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Обратите внимание, что не все курсы валют берутся из расчета за 1 единицу. Например, для Армянских драммов расчет ведется за 100 единиц. Если необходимо, чтобы курс вставлялся за 1 единицу, то установите опцию В расчете за 1 единицу валюты в нижнем левом углу.

Пишем функцию Excel для получения курса валют на указанную дату

Подобные знания помогают решать множество математических, экономических задач, физических и других задач. Допустим, у нас есть таблица с продажами обуви (в парах) за 1 квартал, и мы планируем в следующем продать на 10% больше. Нужно определить, какому количеству пар для каждого наименования соответствуют эти 10%.

Находим процент от числа

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

Математическая формула для расчета выглядит следующим образом:

Например, давайте узнаем, какое число составляет 15% от 90.

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

Автоматическая загрузка курсов валют и котировок акций: новые функции EXCEL

Как и в первой варианте, нам нужно зафиксировать цифру по итоговым продажам, однако, так как в расчетах не принимает участие отдельная ячейка с нужным значением, нам нужно проставить знаки “$” перед обозначениями строк и столбцов в адресах ячеек диапазона суммы: =D2/СУММ($D500:$D$15) .

Загрузка курсов валют в Excel

Разбираем загрузку валютных курсов с сайта ЦБ РФ в MS Excel. Подобная статья есть и для Power BI (ссылка).

Этап 1: Сгенерим ссылку на сайте ЦБ РФ.

Зайдём на сайт по адресу: https://www.cbr.ru/
В меню найдём раздел — “Динамика официального курса заданной валюты” (“Документы и данные” — “Базы данных” — “Базы данных по курсам валют” — “Динамика официального курса заданной валюты”)

Выберем табличный вид, выберем валюту, период дат. Нажмём — “Получить данные”. Увидим табличку с курсами валюты за выбранный диапазон дат:

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

В Excel перейдём на панели вкладку “Данные”, выберем получение данных с web-страницы (“Создать запрос” — “Из других источников” — “Из интернета”):

В появившемся окошке вставим полученную на предыдущем этапе ссылку на сайт ЦБ РФ:

В навигаторе увидим, что на странице распознано четыре табличных элемента. Нам нужен последний. Нажмём “Преобразовать данные”:

Далее удалим первую строку с указанием на валюту:

И поднимем оставшуюся строку до уровня заголовков:

Получим вот такую вполне приличную табличку:

Этап 3: Сделаем диапазон дат подстраивающимся под реальность:

Отправимся в “Расширенный редактор” (“Advanced Editor”):

Там увидим вот такой код, который описывает на языке M все действия, которые мы произвели ранее:

Для желающих сократить путь, вот этот код:

#»Повышенные заголовки» = Table.PromoteHeaders(#»Удаленные верхние строки», [PromoteAllScalars=true]),

Вспомним, что в ссылке у нас есть даты. Воспользуемся этим и заменим их на те, которые нам нужны. Для этого перед подключением к источнику данных две переменных — дату начала диапазона для выгрузки курсов валюты и дату окончания диапазона. Дату начала определим для начала так:
start_date = #date(2022,1,1)

Так или иначе, наша задача — получить две даты в текстовом формате «dd.MM.yyyy» с тем, чтобы после вставить их в ссылку для подключения к web-странице.

После получения дат преобразуем запрос к источнику так, чтобы можно было заменить часть текстовой ссылки на переменные. Для этого разобьём ссылку на части, заменим даты на переменные и соберём обратно с помощью Text.Combine:

[expert_bq id=»1570″]Если вы хотите узнать, какую часть общей суммы составляют несколько различных товаров, сложите результаты, возвращаемые несколькими функциями СУММЕСЛИ, а затем разделите это число на итоговую сумму. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] В первой скобке программа ищет значение из ячейки F2 («Аня») в столбце с именами. Во второй скобке — значение из ячейки F3 («Еда на работе») из столбца с категориями расходов. А после считает сумму ячеек из третьего столбца, которые выполнили эти условия.

Как посчитать процент от числа в Excel — примеры формул | Mister-Office

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

Недостатки

Они тоже, на мой взгляд, имеются. Например, нельзя посмотреть дивиденды по бумаге. Нет цены типа Adjusted Close, которая бы учитывала дивидендную доходность. Это ограничивает сколько-нибудь серьезное использование новых возможностей для отслеживания доходности ценной бумаги или набора ценных бумаг (портфеля).

Кроме того нет возможности посмотреть историю изменения цены или других параметров (TimeSeries).

В целом все изменения очень полезные и удобные, но новый функционал пока уступает аналогу из Google Spreadsheets. Будем надеяться, что это только первый шаг Microsoft в нужном направлении.

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

[expert_bq id=»1570″]- В этом руководстве показано множество различных способов преобразования текста в число в Excel опция проверки ошибок в числах, формулы, математические операции, специальная вставка и многое другое. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Во время работы с таблицами нередко возникает потребность производить расчеты с процентами. К счастью, функционал программы Эксель позволяет выполнять их с легкостью, причем, если речь идет об однотипных вычислениях в больших таблицах, процесс можно автоматизировать, что позволит сэкономить немало времени.