Как привести суммы в столбце в разных валютах к одной валюте - Мир MS Excel

Регистрация

Напомнить пароль

= Мир MS Excel/Как привести суммы в столбце в разных валютах к одной валюте - Мир MS Excel

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Как привести суммы в столбце в разных валютах к одной валюте (Формулы/Formulas)

Как привести суммы в столбце в разных валютах к одной валюте

Дата: Вторник, 03.06.2014, 11:33 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 6

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

Добрый день! Помогите пожалуйста со следующим вопросом.
Есть столбец с денежными суммами в разных валютах. Нужно написать такую формулу, чтобы в соседнем столбце все приводилось к одной валюте, например к $. Я так понимаю, что должна быть логическая формула типа ЕСЛИ A1 формат рубль, то A1/35, иначе A1, ЕСЛИ A1 формат AED, то A1/3.66, иначе A1 и т.д. Только как это все правильно записать? Или может еще какой-нибудь способ существует?

К сообщению приложен файл: 3098551.xlsx (11.5 Kb)

Сообщение отредактировал OlgaP - Вторник, 03.06.2014, 11:50

Ответить

Дата: Вторник, 03.06.2014, 11:34 | Сообщение № 2

Группа: Друзья

Ранг: Старожил

Сообщений: 1394

Репутация: 320 ±

Замечаний: 0% ±

Excel 2010

Файлик нужен

QIWI 9173973973

Ответить

Дата: Вторник, 03.06.2014, 11:35 | Сообщение № 3

Группа: Модераторы

Ранг: Экселист

Сообщений: 7025

Репутация: 1076 ±

Замечаний: 0% ±

Excel 2007;2010;2016

Цитата OlgaP, 03.06.2014 в 11:33, в сообщении № 1

Или может еще какой-нибудь способ существует?

существует.а если правила прочитать,то и решение будет

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Ответить

Дата: Вторник, 03.06.2014, 11:50 | Сообщение № 4

Группа: Пользователи

Ранг: Прохожий

Сообщений: 6

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

Russel, прикрепила!

Ответить

Дата: Вторник, 03.06.2014, 12:51 | Сообщение № 5

Группа: Друзья

Ранг: Участник клуба

Сообщений: 3569

Репутация: 775 ±

Замечаний: 0% ±

365

Думаю в таблице не хватает даты сделки. И ещё мы не знаем страну, где всё это считается, вернее банк. (Ну я могу предположить что IP не врёт - но зачем там рубли?

)
Далее т.к. курс нужно брать из банка, следовательно нужна UDF, следовательно можно ею же вытянуть символ/ы валюты из ячейки с валютой, далее по этому символу из таблицы соответствий (которую кто-то должен подготовить где-то..) получаем аббревиатуру/номер валюты для банка, далее идём в банк за курсом на дату сделки, ну и перемножаем.

Но есть некоторые вредные банки, которые курс так просто не дают... И Reuters тоже не бесплатен - тогда нужно как-то выкручиваться подготавливая данные вручную, или платить за подключение.
Конечно если курс не привязывать к дате и брать что-то примерно среднее - то можно вытянув обозначение валюты из ячейки, далее с помощью ВПР() из составленной заранее таблицы взять курс и умножить.

Вот код UDF:
[vba]

Код

Public Function ExtractCurrency(r As Range)
     Dim i As Integer, s As String, str As String
     s = r.Text
     For i = 1 To Len(s)
         If InStr(1, ChrW(8364) & "Р$QWERTYUIOPASDFGHJKLZXCVBNM", UCase(Mid(s, i, 1))) <> 0 Then str = str & Mid(s, i, 1)
     Next
     ExtractCurrency = Application.Trim(str)
End Function

[/vba]

webmoney: E265281470651 Z422237915069
USDT TRC20: TN8XeEF17o5KPBD9pNwYzNyruycuAc2mVD

Ответить

СообщениеДумаю в таблице не хватает даты сделки. И ещё мы не знаем страну, где всё это считается, вернее банк. (Ну я могу предположить что IP не врёт - но зачем там рубли?

)
Далее т.к. курс нужно брать из банка, следовательно нужна UDF, следовательно можно ею же вытянуть символ/ы валюты из ячейки с валютой, далее по этому символу из таблицы соответствий (которую кто-то должен подготовить где-то..) получаем аббревиатуру/номер валюты для банка, далее идём в банк за курсом на дату сделки, ну и перемножаем.

Но есть некоторые вредные банки, которые курс так просто не дают... И Reuters тоже не бесплатен - тогда нужно как-то выкручиваться подготавливая данные вручную, или платить за подключение.
Конечно если курс не привязывать к дате и брать что-то примерно среднее - то можно вытянув обозначение валюты из ячейки, далее с помощью ВПР() из составленной заранее таблицы взять курс и умножить.

Вот код UDF:
[vba]

Код

Public Function ExtractCurrency(r As Range)
     Dim i As Integer, s As String, str As String
     s = r.Text
     For i = 1 To Len(s)
         If InStr(1, ChrW(8364) & "Р$QWERTYUIOPASDFGHJKLZXCVBNM", UCase(Mid(s, i, 1))) <> 0 Then str = str & Mid(s, i, 1)
     Next
     ExtractCurrency = Application.Trim(str)
End Function

[/vba]

Автор - Hugo
Дата добавления - 03.06.2014 в 12:51

Дата: Вторник, 03.06.2014, 13:10 | Сообщение № 6

Группа: Друзья

Ранг: Старожил

Сообщений: 1394

Репутация: 320 ±

Замечаний: 0% ±

Excel 2010

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

К сообщению приложен файл: 6111491.xlsx (12.9 Kb)

QIWI 9173973973

Ответить

Дата: Вторник, 03.06.2014, 13:21 | Сообщение № 7

Группа: Друзья

Ранг: Участник клуба

Сообщений: 3569

Репутация: 775 ±

Замечаний: 0% ±

365

Формат ячейки может вытянуть макрофункция GET.CELL() - но т.к. всё равно будут пугающие запросы на дозволение этих макросов, да и вытянет не вполне удобные для обработки значения - то проще парсить текст ячейки с помощью своей собственной функции.
Ну и вот - если в файл Russel'а интегрировать мою UDF - то можно обойтись без допстолбцов, только нужна таблица курсов. Ну и в "р." убрать точку, или добавить точку в код.

webmoney: E265281470651 Z422237915069
USDT TRC20: TN8XeEF17o5KPBD9pNwYzNyruycuAc2mVD

Ответить

Дата: Вторник, 03.06.2014, 13:44 | Сообщение № 8

Группа: Пользователи

Ранг: Прохожий

Сообщений: 6

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

курс фиксированный. Его можно тупо забить в формулу

Ответить

Дата: Вторник, 03.06.2014, 13:48 | Сообщение № 9

Группа: Друзья

Ранг: Участник клуба

Сообщений: 3569

Репутация: 775 ±

Замечаний: 0% ±

365

Можно и в формулу - если валют/курсов будет только эти 5 или около этого. Ну а если 50 - уже сложнее.
И я как-то смутно представляю фиксированный курс например рубля к доллару сегодня...

webmoney: E265281470651 Z422237915069
USDT TRC20: TN8XeEF17o5KPBD9pNwYzNyruycuAc2mVD

Ответить

Дата: Вторник, 03.06.2014, 13:51 | Сообщение № 10

Группа: Пользователи

Ранг: Прохожий

Сообщений: 6

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

Russel, большое спасибо. Но массив данных огромен, поэтому вариант "с костылями" потребует много ручного труда.

Ответить

Дата: Вторник, 03.06.2014, 13:52 | Сообщение № 11

Группа: Пользователи

Ранг: Прохожий

Сообщений: 6

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

Hugo, спасибо большое за ответ. Но он сложен для моего понимания

Ответить

Дата: Вторник, 03.06.2014, 13:56 | Сообщение № 12

Группа: Друзья

Ранг: Участник клуба

Сообщений: 3569

Репутация: 775 ±

Замечаний: 0% ±

365

Моя UDF вытягивает то, что нужно делать вручную в варианте Russel'а.
Как применить - в файле показать не могу, но на сайте где-то есть статья... Должна быть!

webmoney: E265281470651 Z422237915069
USDT TRC20: TN8XeEF17o5KPBD9pNwYzNyruycuAc2mVD

Ответить

Дата: Вторник, 03.06.2014, 14:03 | Сообщение № 13

Группа: Друзья

Ранг: Старожил

Сообщений: 1394

Репутация: 320 ±

Замечаний: 0% ±

Excel 2010

Скрестил свой файл с UDF от Hugo

К сообщению приложен файл: 3098551.xlsm (17.6 Kb)

QIWI 9173973973

Ответить

Дата: Вторник, 03.06.2014, 14:13 | Сообщение № 14

Группа: Друзья

Ранг: Участник клуба

Сообщений: 3569

Репутация: 775 ±

Замечаний: 0% ±

365

Ну вот спасибо.
Практически можно полностью удалить столбцы "Валюта" и "Сумма в руб." - если в "Сумма в $" записать

Код

=VLOOKUP(ExtractCurrency(A2),$F$2:$G$7,2,0)*C2/$G$2

Да и таблицу курсов валют тоже можно всю запихнуть в эту формулу. Если постараться

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

Только с EUR там неувязка (нужно доработать) - моя UDF будет возвращать не EUR, а €, как в формате.

webmoney: E265281470651 Z422237915069
USDT TRC20: TN8XeEF17o5KPBD9pNwYzNyruycuAc2mVD

Ответить

Дата: Вторник, 03.06.2014, 14:28 | Сообщение № 15

Группа: Пользователи

Ранг: Прохожий

Сообщений: 6

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

Russel, Hugo, отличное решение!! Огромное вам спасибо! hands

hands

Ответить

Дата: Вторник, 03.06.2014, 15:10 | Сообщение № 16

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4517

Репутация: 1129 ±

Замечаний: 0% ±

Excel 2003

Не так давно была тема "Как извлечь из ячейки тип формат валюты..."
Мне больше понравилось функция Александра (ikki)

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Ответить

Дата: Вторник, 03.06.2014, 16:11 | Сообщение № 17

Группа: Друзья

Ранг: Участник клуба

Сообщений: 3569

Репутация: 775 ±

Замечаний: 0% ±

365

Да, вариант ikki лучше. Я его видел, но не сохранил и забыл... Теперь сохранил

Но принцип у нас одинаковый - инструменты разные.

webmoney: E265281470651 Z422237915069
USDT TRC20: TN8XeEF17o5KPBD9pNwYzNyruycuAc2mVD

Ответить

Как привести суммы в столбце в разных валютах к одной валюте (Формулы/Formulas)

Страница 1 из 1
1

© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!