Как отдельно суммировать валютные ячейки - Мир MS Excel

Регистрация

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

= Мир MS Excel/Как отдельно суммировать валютные ячейки - Мир MS Excel

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

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

Как отдельно суммировать валютные ячейки (Формулы/Formulas)

Как отдельно суммировать валютные ячейки

Дата: Среда, 29.06.2016, 15:53 | Сообщение № 1

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

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

Сообщений: 7

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

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

Excel 2007

Добрый день, господа. Подскажите, пожалуйста, новичку. Задача следующая.
В столбце стоят числа, указанные в рублях, долларах и евро. Надо получить сумму ячеек этого столбца в рублях по заданному в отдельной ячейке курсу.
Перефразирую, каким образом в excel можно обозначить валюту (доллары, евро и рубли) для того, чтобы ячейки с долларами, евро и рублями отдельно сложить? Спасибо.

Ответить

Дата: Среда, 29.06.2016, 15:56 | Сообщение № 2

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

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

Сообщений: 1588

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

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

Excel 2016 х 64

файл где?

вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com

Ответить

Дата: Среда, 29.06.2016, 15:58 | Сообщение № 3

Группа: Проверенные

Ранг: Ветеран

Сообщений: 828

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

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

Excel 2010

В чем проблема сделать рядом столбец, где указывать вид валюты?

Ответить

Дата: Среда, 29.06.2016, 16:03 | Сообщение № 4

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

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

Сообщений: 7

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

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

Excel 2007

в одной строке могут содержаться разные валюты

Ответить

Дата: Среда, 29.06.2016, 16:12 | Сообщение № 5

Группа: Проверенные

Ранг: Ветеран

Сообщений: 828

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

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

Excel 2010

В одной _строке_? ... ага ... ну-ну ...

Я же Вам говорю - сделайте два столбца (рядом) - в одном числа, в другом обозначения валют (например RUR, USD, EUR)

Можно визуализировать валюты и видами денежного формата, но зачем вам этот геморрой?

Давайте файл, что и как Вы хотите видеть

Ответить

Дата: Среда, 29.06.2016, 17:14 | Сообщение № 6

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

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

Сообщений: 7

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

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

Excel 2007

У меня по горизонтали (в строке) расписан понедельно весь год. По вертикали - наименование закупаемого оборудования.
Так вот оборудование в один промежуток времени за евро покупается, за доллары доставляется, а за рубли монтируется., и все эти данные указаны в одной строке, но в разные промежутки времени. НЕЛЬЗЯ вставить в строку отдельным столбцом наименование валюты.
Мне надо вывести потребности в рублях еженедельно.
Позиций разного оборудования много.
[moder]

Цитата XACAH, 29.06.2016 в 17:14, в сообщении № 6

У меня по горизонтали

А у нас ничего нет. Почитайте правила форума, и вложите пример.[/moder]

Сообщение отредактировал SLAVICK - Среда, 29.06.2016, 17:17

Ответить

Дата: Среда, 29.06.2016, 17:17 | Сообщение № 7

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

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

Сообщений: 9000

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

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

Excel 2010

XACAH. Вы файл-то покажите? (весь не надо)

ЮMoney 41001841029809

Ответить

Дата: Среда, 29.06.2016, 17:34 | Сообщение № 8

Группа: Проверенные

Ранг: Ветеран

Сообщений: 828

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

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

Excel 2010

Это, конечно, не моё собачье дело, но у Вас еще и данные неправильно с точки зрения грамотного структурирования располагаются.

По уму, данные должны накапливаться в однородной плоской таблица вида "Оборудование - Вид(код) затрат - Дата - Сумма - Валюта - Что-то еще (например Контрагент) "

Например:
Станок F-111- Закупка - 01.01.16 - 1000000 - USD
Станок F-111- Доставка - 15.01.16 - 2100000 - RUR
Микроскоп АППР - Закупка - ...
Микроскоп АППР - Доставка - ...
Услуги технического перевода - Оплата услуг - ...

И вот с этим "богатством" уже очень легко всё анализировать, сводить в сводные, фильтровать и т.д.

Ответить

Дата: Среда, 29.06.2016, 17:45 | Сообщение № 9

Группа: Проверенные

Ранг: Ветеран

Сообщений: 828

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

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

Excel 2010

Цитата XACAH, 29.06.2016 в 17:14, в сообщении № 6

НЕЛЬЗЯ вставить в строку отдельным столбцом наименование валюты.

Тогда два пути:
1. Обозначать валюту путем форматирования (денежный формат). В этом случает для суммирование в рублях надо будет подписать макрос-UDF (User Defined Function)
2. Вводить суммы как текст, например "1000 USD", "2000 EUR" или "USD 1000", "EUR 2000" (рублевые суммы можно без пре-/постфикса или с RUR). Для такой записи можно просто формулами задать операции суммирования с учетом валют

Сообщение отредактировал abtextime - Среда, 29.06.2016, 17:46

Ответить

Дата: Среда, 29.06.2016, 18:14 | Сообщение № 10

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

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

Сообщений: 7

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

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

Excel 2007

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

Цитата abtextime, 29.06.2016 в 17:45, в сообщении № 9

Тогда два пути:
1. Обозначать валюту путем форматирования (денежный формат). В этом случает для суммирование в рублях надо будет подписать макрос-UDF (User Defined Function)
2. Вводить суммы как текст, например "1000 USD", "2000 EUR" или "USD 1000", "EUR 2000" (рублевые суммы можно без пре-/постфикса или с RUR). Для такой записи можно просто формулами задать операции суммирования с учетом валют

Именно, я думал что денежный формат, по логике должен иметь какую-то метку, отличающую одну валюту от другой, но самостоятельно найти ее я не смог.
Я прикрепил свой образец, посмотрите пожалуйста. У меня в строке итого просто суммируется все подряд

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

Ответить

Дата: Среда, 29.06.2016, 18:55 | Сообщение № 11

Группа: Проверенные

Ранг: Ветеран

Сообщений: 828

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

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

Excel 2010

Смотрите ... Обратите внимание на ячейки, залитые зеленым

[vba]

Код

Public Function CurFormat(RR As Range) As String

F = RR.Cells(1, 1).NumberFormatLocal

CurFormat = "RUR"
If Replace(F, "$", "") <> F Then CurFormat = "USD"
If Replace(F, "ˆ", "") <> F Then CurFormat = "EUR"

End Function

Public Function RURSum(RR As Range, USD As Double, EUR As Double) As Double

Dim R As Range

For Each R In RR
    S = R.Value
    If CurFormat(R) = "USD" Then S = S * USD
    If CurFormat(R) = "EUR" Then S = S * EUR
    RURSum = RURSum + S
Next

End Function

[/vba]

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

Ответить

Дата: Среда, 29.06.2016, 19:10 | Сообщение № 12

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

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

Сообщений: 7

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

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

Excel 2007

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

Ответить

Дата: Среда, 29.06.2016, 19:15 | Сообщение № 13

Группа: Проверенные

Ранг: Ветеран

Сообщений: 828

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

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

Excel 2010

ХАСАН
Я не ставил целью сделать за Вас всю работу.

Написал Вам функцию RURSum (диапазон суммирования, курс USD, курс EUR), которая суммирует числа из заданного диапазона, с учетом валюты (валюты в формате) при заданных курсах).

Поставить эту функцию в нужные Вам ячейки по аналогии Вы можете сами?

Зеленые ячейки - просто, чтобы показать на примерах, как работает функция

Ответить

Дата: Среда, 29.06.2016, 19:24 | Сообщение № 14

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

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

Сообщений: 7

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

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

Excel 2007

Обязательно иметь ячейки USD и EUR?

Теперь возникает у меня 2 вопроса))): 1) Как функцию, которую вы разработали, вставить в мой файл?
2) Каким образом при подсчете суммы в столбцах исключить при суммировании цифру, проставленную в валюте, а остальные (рублёвые суммы) складывать?

Ответить

Дата: Среда, 29.06.2016, 21:24 | Сообщение № 15

Группа: Проверенные

Ранг: Ветеран

Сообщений: 828

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

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

Excel 2010

Цитата XACAH, 29.06.2016 в 19:24, в сообщении № 14

1) Как функцию, которую вы разработали, вставить в мой файл?

1. Сохранить ваш файл с расширением xlsm (файл Excel с поддержкой макросов)
2. Alt+F11 - открыть панель VBA. Встать на Лист 1 в иерархическом дереве (слева), Insert - Module. Через Ctl+C - Ctrl+V вставить туда мой код
3. Всё, можно использовать имеющиеся в коде функции CurFormat и RURSum

Цитата XACAH, 29.06.2016 в 19:24, в сообщении № 14

2) Каким образом при подсчете суммы в столбцах исключить при суммировании цифру, проставленную в валют

Замените кусочек кода
[vba]

Код

If CurFormat(R) = "USD" Then S = S * USD
If CurFormat(R) = "EUR" Then S = S * EUR

[/vba]
на
[vba]

Код

If CurFormat(R) = "USD" Then S = 0
If CurFormat(R) = "EUR" Then S = 0

[/vba]

Разумеется, заголовок функции можно в этом случае поменять с
[vba]

Код

Public Function RURSum(RR As Range, USD As Double, EUR As Double) As Double

[/vba]
на
[vba]

Код

Public Function RURSum(RR As Range) As Double

[/vba]

Если же Вас эта макросятина

напрягает, то воспользуйтесь другой формой записи и другим моим советом

Цитата abtextime, 29.06.2016 в 17:45, в сообщении № 9

2. Вводить суммы как текст, например "1000 USD", "2000 EUR" или "USD 1000", "EUR 2000" (рублевые суммы можно без пре-/постфикса или с RUR). Для такой записи можно просто формулами задать операции суммирования с учетом валют

В этом случае можно обойтись обычной формулой

Цитата XACAH, 29.06.2016 в 19:24, в сообщении № 14

1) Как функцию, которую вы разработали, вставить в мой файл?

1. Сохранить ваш файл с расширением xlsm (файл Excel с поддержкой макросов)
2. Alt+F11 - открыть панель VBA. Встать на Лист 1 в иерархическом дереве (слева), Insert - Module. Через Ctl+C - Ctrl+V вставить туда мой код
3. Всё, можно использовать имеющиеся в коде функции CurFormat и RURSum

Цитата XACAH, 29.06.2016 в 19:24, в сообщении № 14

2) Каким образом при подсчете суммы в столбцах исключить при суммировании цифру, проставленную в валют

Замените кусочек кода
[vba]

Код

If CurFormat(R) = "USD" Then S = S * USD
If CurFormat(R) = "EUR" Then S = S * EUR

[/vba]
на
[vba]

Код

If CurFormat(R) = "USD" Then S = 0
If CurFormat(R) = "EUR" Then S = 0

[/vba]

Разумеется, заголовок функции можно в этом случае поменять с
[vba]

Код

Public Function RURSum(RR As Range, USD As Double, EUR As Double) As Double

[/vba]
на
[vba]

Код

Public Function RURSum(RR As Range) As Double

[/vba]

Если же Вас эта макросятина

напрягает, то воспользуйтесь другой формой записи и другим моим советом

Цитата abtextime, 29.06.2016 в 17:45, в сообщении № 9

2. Вводить суммы как текст, например "1000 USD", "2000 EUR" или "USD 1000", "EUR 2000" (рублевые суммы можно без пре-/постфикса или с RUR). Для такой записи можно просто формулами задать операции суммирования с учетом валют

В этом случае можно обойтись обычной формулойabtextime

Сообщение отредактировал abtextime - Среда, 29.06.2016, 21:28

Ответить

Сообщение

Цитата XACAH, 29.06.2016 в 19:24, в сообщении № 14

1) Как функцию, которую вы разработали, вставить в мой файл?

1. Сохранить ваш файл с расширением xlsm (файл Excel с поддержкой макросов)
2. Alt+F11 - открыть панель VBA. Встать на Лист 1 в иерархическом дереве (слева), Insert - Module. Через Ctl+C - Ctrl+V вставить туда мой код
3. Всё, можно использовать имеющиеся в коде функции CurFormat и RURSum

Цитата XACAH, 29.06.2016 в 19:24, в сообщении № 14

2) Каким образом при подсчете суммы в столбцах исключить при суммировании цифру, проставленную в валют

Замените кусочек кода
[vba]

Код

If CurFormat(R) = "USD" Then S = S * USD
If CurFormat(R) = "EUR" Then S = S * EUR

[/vba]
на
[vba]

Код

If CurFormat(R) = "USD" Then S = 0
If CurFormat(R) = "EUR" Then S = 0

[/vba]

Разумеется, заголовок функции можно в этом случае поменять с
[vba]

Код

Public Function RURSum(RR As Range, USD As Double, EUR As Double) As Double

[/vba]
на
[vba]

Код

Public Function RURSum(RR As Range) As Double

[/vba]

Если же Вас эта макросятина

напрягает, то воспользуйтесь другой формой записи и другим моим советом

Цитата abtextime, 29.06.2016 в 17:45, в сообщении № 9

2. Вводить суммы как текст, например "1000 USD", "2000 EUR" или "USD 1000", "EUR 2000" (рублевые суммы можно без пре-/постфикса или с RUR). Для такой записи можно просто формулами задать операции суммирования с учетом валют

В этом случае можно обойтись обычной формулой

Автор - abtextime
Дата добавления - 29.06.2016 в 21:24

Дата: Четверг, 30.06.2016, 14:13 | Сообщение № 16

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

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

Сообщений: 7

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

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

Excel 2007

Ну спасибо, выручили. Тема закрыта.

Ответить

Как отдельно суммировать валютные ячейки (Формулы/Formulas)

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

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