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

[Alexcostariha]

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

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

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

Код Выделить Развернуть

=$Прайс_валюта.B4*курс_обмена

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

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

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

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

[JohnSUN]

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

Выборочно вставлять формулу в ячейки, конечно, можно, но это довольно муторное занятие. Проще всего сделать формулу сразу такой, чтобы её не было видно в строчках, где речь не идёт о деньгах.
Это очень старый приём, описан во всех руководствах по электронным таблицам едва ли не первым из примеров использования функции IF (ЕСЛИ)

Код Выделить Развернуть

=IF(<ячейка>="";"";<результат>)
Сравнение <ячейка>="" проверяет не пустая ли ячейка. И если в ячейке нет видимого значения (только пустая строка), то и результат формулы будет пустая строка (другими словами - ячейка, в которой вроде бы ничего нет)
В чистом виде в твоём случае этот приём может не подойти - в ячейке может быть "что-то", но это будет не валютная цена, а какое-нибудь "бла-бла-бла" (описание товара).
Возможно, тебя устроит вариант вида

Код Выделить Развернуть

=IF(ISNUMBER($Прайс_валюта.B4);$Прайс_валюта.B4*курс_обмена;"")
А может быть потребуется и более сложная формула, если окажется, что нежелательные ноли все-таки вылазят в результатах.

[Alexcostariha]

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

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

[JohnSUN]

Извините - лень-матушка.

Ой, не правда ваша: была бы настоящая лень - не стал бы форматировать заголовки через объединение ячеек, поленился бы ;-)

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

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

Плюс нежелательное форматирование.

Ну, ничего не попишешь. Значит для каждой из подтаблиц прайса нужно будет выделять только диапазон данных (без заголовков) и только его заменять на формулу. Но это не больно трудно: в одну из ячеек вписываем

Код Выделить Развернуть

=Цена_в_рубляхкопируем эту ячейку, выделяем диапазон за диапазоном и просто вставляем Ctrl+V.
Я другого способа, увы, не знаю.

[Alexcostariha]

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

[JohnSUN]

Спасибо. Однако, при изменении курса обмена не происходит пересчёта цены в ячейках.

Ответь на вопрос с картинки.

Кроме того я не совсем понял как это у вас получилось. Увы.

Не "выкай". Нажми Ctrl+F3 и увидишь формулу, которую просто затолкали в именованный диапазон. Где бы мы теперь не использовали это имя, Калк пересчитает адреса ячеек и вычислит значение.

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

О! А вот это уже действительно похоже на лень-матушку...

[Alexcostariha]

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

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

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

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

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

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

Код Выделить Развернуть

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

Код Выделить Развернуть

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

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

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

╚ ╚ ╚ ╚ ╚ ╚ ╚ ╚ ╚  {｡^◕‿◕^｡} ╝ ╝ ╝ ╝ ╝ ╝ ╝ ╝

[JohnSUN]

Так можно лупить не линейно, а прямоугольно по каждой из подтаблиц,

Для знатоков штучка... В смысле - для достаточно ленивых 

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

Код Выделить Развернуть

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

Код Выделить Развернуть

=IF ( ISNUMBER($Прайс_Евро.B10) ;$Прайс_Евро.B10*курс_обмена;"")

Ну, почему вместо $Прайс_Евро появилась $Цена_валюта понятно и так - лист с исходными цифрами так называется. А по поводу IF - тоже не сложно. Сама формула на человеческий язык переводится как

Код Выделить Развернуть

Когда мы соберемся вычислять значение ячейки B10, мы проверим
ЕСЛИ такая же ячейка B10 на листе $Цена_валюта содержит число
ТО ВЕРНУТЬ В КАЧЕСТВЕ РЕЗУЛЬТАТА число из $Цена_валюта.B10 умноженное на значение именованной ячейки курс_обмена
ИНАЧЕ ВЕРНУТЬ В КАЧЕСТВЕ РЕЗУЛЬТАТА содержимое ячейки $Цена_валюта.B10
Но тут ведь какая штука. Если ячейка $Цена_валюта.B10 пустая, то формула вернула бы 0 - просто чтобы показать, что формула посчитана. Чтобы не видеть эти стройные ряды нулей, оборачиваем ячейку в еще одну функцию T()
Вроде как и не сложно, да?

Крутая штука хочу тебе сказать !!!!

Ты про формулу в именованном диапазоне? Что да, то да, шикарная вещь.
Подробнее - здесь https://gfycat.com/ColorfulUglyCrossbill


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