[РЕШЕНО] Пересчёт валюты в рубли в прайсе с пропуском „лишних“ ячеек

Автор Alexcostariha, 26 марта 2017, 09:38

0 Пользователи и 1 гость просматривают эту тему.

Alexcostariha

Добрый день!
У меня имеется прайс в валюте. Мне нужно  делать пересчёт в  рубли по текущему курсу.
Для этого я в одном и том же файле создаю две страницЫ: ,,Прайс_валюта" и ,,Прайс_рубли".
,,Прайс_рубли" поначалу- точная копия ,,Прайс_валюта". На странице ,,Прайс_рубли" делаю ячейку
,,курс_обмена" для пересчёта валюты в рубли, куда вставляю текущий курс рубля к валюте.

Теперь мне нужно использовать данные со страницы ,,Прайс_валюта" для пересчёта в рубли
на странице ,,Прайс рубли".

Я делаю так: на странице  ,,Прайс рубли" в первой же ячейке с ценой, допустим ,,B4" пишу формулу:
=$Прайс_валюта.B4*курс_обмена

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

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

В документе много строк - порядка 5000 и работа довольно трудоёмкая. А если её делать несколько раз, то вообще.
 С нулями я как-то научился справляться через Найти-Заменить и регулярные выражения
(найти - ^[0]$ и заменить - пусто; если не работает ставим найти - ^[0]), но всё остальное....

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

JohnSUN

Добро пожаловать на форум!
Жаль, что приложенный файл попал в сообщение по ошибке - во всяком случае ни именованной ячейки "курс_обмена", ни листа Прайс_валюта в этом файле не видно... Ну да и ладно.

Выборочно вставлять формулу в ячейки, конечно, можно, но это довольно муторное занятие. Проще всего сделать формулу сразу такой, чтобы её не было видно в строчках, где речь не идёт о деньгах.
Это очень старый приём, описан во всех руководствах по электронным таблицам едва ли не первым из примеров использования функции IF (ЕСЛИ)
=IF(<ячейка>="";"";<результат>)
Сравнение <ячейка>="" проверяет не пустая ли ячейка. И если в ячейке нет видимого значения (только пустая строка), то и результат формулы будет пустая строка (другими словами - ячейка, в которой вроде бы ничего нет)
В чистом виде в твоём случае этот приём может не подойти - в ячейке может быть "что-то", но это будет не валютная цена, а какое-нибудь "бла-бла-бла" (описание товара).
Возможно, тебя устроит вариант вида
=IF(ISNUMBER($Прайс_валюта.B4);$Прайс_валюта.B4*курс_обмена;"")
А может быть потребуется и более сложная формула, если окажется, что нежелательные ноли все-таки вылазят в результатах.
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

Alexcostariha

#2
 Спасибо, я исправил файл, по вашим замечаниям.
Извините - лень-матушка.

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

JohnSUN

#3
Цитата: Alexcostariha от 26 марта 2017, 11:46
Извините - лень-матушка.
Ой, не правда ваша: была бы настоящая лень - не стал бы форматировать заголовки через объединение ячеек, поленился бы ;-)
Цитата: Alexcostariha от 26 марта 2017, 11:46
Ваша формула помогает избежать прописывания нулей, однако напрочь удаляет строки заголовков
при копировании-протягивании.
Ну, а как иначе формула могла бы сработать? Натыкается Калк на значение ячейки, скажем, 3 или 6, понимает, что речь идёт о числе и честно множит его на курс. Откуда же ему знать, что это такой заголовок колонки?
Цитата: Alexcostariha от 26 марта 2017, 11:46Плюс нежелательное форматирование.
Ну, ничего не попишешь. Значит для каждой из подтаблиц прайса нужно будет выделять только диапазон данных (без заголовков) и только его заменять на формулу. Но это не больно трудно: в одну из ячеек вписываем
=Цена_в_рубляхкопируем эту ячейку, выделяем диапазон за диапазоном и просто вставляем Ctrl+V.
Я другого способа, увы, не знаю.
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

Alexcostariha

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

JohnSUN

Цитата: Alexcostariha от 26 марта 2017, 13:12
Спасибо. Однако, при изменении курса обмена не происходит пересчёта цены в ячейках.
Ответь на вопрос с картинки.
Цитата: Alexcostariha от 26 марта 2017, 13:12
Кроме того я не совсем понял как это у вас получилось. Увы.
Не "выкай". Нажми Ctrl+F3 и увидишь формулу, которую просто затолкали в именованный диапазон. Где бы мы теперь не использовали это имя, Калк пересчитает адреса ячеек и вычислит значение.
Цитата: Alexcostariha от 26 марта 2017, 13:12
В принципе это не имеет большого значения, поскольку сути не меняет - всё-равно работу делать ручками.
О! А вот это уже действительно похоже на лень-матушку...
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

Alexcostariha

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

Что существенно облегчает жизнь.  :beer:

Да и вид приятен глазу.

Спасибо огромное !!!

Не мог бы ты пояснить коротко смысл твоей формулы —хочется понимать, что делается?

Кстати твоя формула для ,,Цена_в_рублях"отличается от той которую написал я для  ячейки B10. Твоя:

=IF(ISNUMBER($Цена_валюта.B10);$Цена_валюта.B10*курс_обмена;T($Цена_валюта.B10))
Моя:
=IF ( ISNUMBER($Цена_валюта.B10) ;$Цена_валюта.B10*курс_обмена;"")

Непонятна последняя (впрочем как и первые две !!!) опции.

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

╚ ╚ ╚ ╚ ╚ ╚ ╚ ╚ ╚  {。^◕‿◕^。} ╝ ╝ ╝ ╝ ╝ ╝ ╝ ╝

JohnSUN

Цитата: Alexcostariha от 26 марта 2017, 15:07
Так можно лупить не линейно, а прямоугольно по каждой из подтаблиц,
Для знатоков штучка... В смысле - для достаточно ленивых  ;D
Цитата: Alexcostariha от 26 марта 2017, 15:07
Не мог бы ты пояснить коротко смысл твоей формулы —хочется понимать, что делается?
Кстати твоя формула для ,,Цена_в_рублях"отличается от той которую написал я для  ячейки B10. Твоя:
=IF(ISNUMBER($Цена_валюта.B10);$Цена_валюта.B10*курс_обмена;T($Цена_валюта.B10))
Моя:
=IF ( ISNUMBER($Прайс_Евро.B10) ;$Прайс_Евро.B10*курс_обмена;"")
Ну, почему вместо $Прайс_Евро появилась $Цена_валюта понятно и так - лист с исходными цифрами так называется. А по поводу IF - тоже не сложно. Сама формула на человеческий язык переводится как
Когда мы соберемся вычислять значение ячейки B10, мы проверим
ЕСЛИ такая же ячейка B10 на листе $Цена_валюта содержит число
ТО ВЕРНУТЬ В КАЧЕСТВЕ РЕЗУЛЬТАТА число из $Цена_валюта.B10 умноженное на значение именованной ячейки курс_обмена
ИНАЧЕ ВЕРНУТЬ В КАЧЕСТВЕ РЕЗУЛЬТАТА содержимое ячейки $Цена_валюта.B10

Но тут ведь какая штука. Если ячейка $Цена_валюта.B10 пустая, то формула вернула бы 0 - просто чтобы показать, что формула посчитана. Чтобы не видеть эти стройные ряды нулей, оборачиваем ячейку в еще одну функцию T()
Вроде как и не сложно, да?

Цитата: Alexcostariha от 26 марта 2017, 15:07
Крутая штука хочу тебе сказать !!!!
Ты про формулу в именованном диапазоне? Что да, то да, шикарная вещь.
Подробнее - здесь https://gfycat.com/ColorfulUglyCrossbill


Но с ней нужно быть осторожным - то, что работает под Либрой, вполне может отказаться работать под OpenOffice или Excel.
Побочный эффект от использования этого приема - размер файла уменьшается. И порой - существенно уменьшается.
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне