Имеются расценки различных фирм (листы Расценки 1, Расценки 2, Расценки 3) на строительные работы. Фирмы специализируются на определенном типе работ, например, первая - малярно-штукатурные, вторая - отделочные, третья - общестроительные. Структура расценок в целом схожая, но могут быть отличия в очередности столбцов и их названии. На основании этих расценок необходимо создать смету, которая включает некоторые работы из расценок строительных фирм. В результате должна получиться единая смета, покрывающая индивидуальный объём работ и включающий работы из разных типов. Список работ в смете должен содержаться в столбце Наименование работ (лист Смета). Для заполнения ячеек в Название работ используются ссылки на выбранные работы соответствующих типов. Например, B2 = ='Расценки 1'!B3.
Задача При заполнении ячейки в Наименовании работ, автоматически заполнить другие столбцы (Тип ресурса, Ед.изм., Ст-ть за ед.) на основе данных, указанных в соответствующих строках на листах Расценки 1, 2, 3. При необходимости упростить задачу, можно менять очередность столбцов на вкладках Расценки, а также привести их название к единообразному виду. Скопировать все расценки на один лист, приведя их к единой структуре, на практике не всегда удобно/возможно.
Как я пытался найти решение 1. Из ссылки ячейки в столбце B (Смета) попробовал выбирать имя листа, но который ссылается конкретная ячейка, пользовался для этого пришлось добавить макрос с переводом содержимого формулы ячейки в текстовую строку. 2. Далее полученный результат обрабатывал функцией ПОИСК, чтобы получить название листа в виде !Расценки 2!. 3. Затем в функции ИНДЕКС и ПОИСКПОЗ передавал название искомой работы из столба B (Смета) и ссылку на нужный лист, по которому нужно провести поиск значений для соответствующих столбцов (Тип ресурса, Ед.изм., Ст-ть за ед.)
Всё заработало, но получилась монструозная формула, которой невозможно управлять. Вот пример из реальной книги.
Еще одним недостатком моего решения является необходимость использования макроса для перевода содержимого формулы ячейки в текстовую строку. Аналогичная функция есть в Excel 2013, но к сожалению необходима поддержка Excel 2010.
Вопрос Есть ли варианты более элегантного и управляемого решения данной задачи?
Имеются расценки различных фирм (листы Расценки 1, Расценки 2, Расценки 3) на строительные работы. Фирмы специализируются на определенном типе работ, например, первая - малярно-штукатурные, вторая - отделочные, третья - общестроительные. Структура расценок в целом схожая, но могут быть отличия в очередности столбцов и их названии. На основании этих расценок необходимо создать смету, которая включает некоторые работы из расценок строительных фирм. В результате должна получиться единая смета, покрывающая индивидуальный объём работ и включающий работы из разных типов. Список работ в смете должен содержаться в столбце Наименование работ (лист Смета). Для заполнения ячеек в Название работ используются ссылки на выбранные работы соответствующих типов. Например, B2 = ='Расценки 1'!B3.
Задача При заполнении ячейки в Наименовании работ, автоматически заполнить другие столбцы (Тип ресурса, Ед.изм., Ст-ть за ед.) на основе данных, указанных в соответствующих строках на листах Расценки 1, 2, 3. При необходимости упростить задачу, можно менять очередность столбцов на вкладках Расценки, а также привести их название к единообразному виду. Скопировать все расценки на один лист, приведя их к единой структуре, на практике не всегда удобно/возможно.
Как я пытался найти решение 1. Из ссылки ячейки в столбце B (Смета) попробовал выбирать имя листа, но который ссылается конкретная ячейка, пользовался для этого пришлось добавить макрос с переводом содержимого формулы ячейки в текстовую строку. 2. Далее полученный результат обрабатывал функцией ПОИСК, чтобы получить название листа в виде !Расценки 2!. 3. Затем в функции ИНДЕКС и ПОИСКПОЗ передавал название искомой работы из столба B (Смета) и ссылку на нужный лист, по которому нужно провести поиск значений для соответствующих столбцов (Тип ресурса, Ед.изм., Ст-ть за ед.)
Всё заработало, но получилась монструозная формула, которой невозможно управлять. Вот пример из реальной книги.
Еще одним недостатком моего решения является необходимость использования макроса для перевода содержимого формулы ячейки в текстовую строку. Аналогичная функция есть в Excel 2013, но к сожалению необходима поддержка Excel 2010.
Вопрос Есть ли варианты более элегантного и управляемого решения данной задачи?Vahmurca
Vahmurca, - Прочитайте Правила форума - Приложите файл с исходными данными и желаемым результатом (можно вручную) в формате Excel размером до 500 кб согласно п.3 Правил форума
Vahmurca, - Прочитайте Правила форума - Приложите файл с исходными данными и желаемым результатом (можно вручную) в формате Excel размером до 500 кб согласно п.3 Правил форумакитин
Не судите очень строго:я пытаюсь научиться ЯД 41001877306852
Да, понял идею. А еще универсализировать логику возможно, т.е. чтобы при добавлении еще одного листа с расценками не нужно было бы вносить изменения во все формулы?
Думал еще в сторону того, что, поскольку формулы для искомых ячеек в Смета типизированы, то хорошо бы саму большую формулу вывести в содержимое отдельной ячейки и затем в расчётах подтягивать её синтаксис из этой ячейки. Так можно было бы добиться существенного упрощения её администрирования - изменяем в одном месте и новая логика используется во всех соответствующих ячейках. Но пока не придумал такой способ.
В любом случае, большое спасибо! Решение намного компакнее моего и им уже можно пользоваться.
Да, понял идею. А еще универсализировать логику возможно, т.е. чтобы при добавлении еще одного листа с расценками не нужно было бы вносить изменения во все формулы?
Думал еще в сторону того, что, поскольку формулы для искомых ячеек в Смета типизированы, то хорошо бы саму большую формулу вывести в содержимое отдельной ячейки и затем в расчётах подтягивать её синтаксис из этой ячейки. Так можно было бы добиться существенного упрощения её администрирования - изменяем в одном месте и новая логика используется во всех соответствующих ячейках. Но пока не придумал такой способ.
В любом случае, большое спасибо! Решение намного компакнее моего и им уже можно пользоваться.Vahmurca
т.е. чтобы при добавлении еще одного листа с расценками не нужно было бы вносить изменения во все формулы?
Логика формулы позволяет добавить много листов с расценками, только заметьте в расценках 3 данные отображены в других столбцах и поэтому отличаются в формуле. И еще для упрощения ввода в смету конкретного вида работ, рекомендую сделать выпадающий список и выбирать конкретный вид работ. Удачи!!!
т.е. чтобы при добавлении еще одного листа с расценками не нужно было бы вносить изменения во все формулы?
Логика формулы позволяет добавить много листов с расценками, только заметьте в расценках 3 данные отображены в других столбцах и поэтому отличаются в формуле. И еще для упрощения ввода в смету конкретного вида работ, рекомендую сделать выпадающий список и выбирать конкретный вид работ. Удачи!!!alexa1965
Виды работ в основном разделены по вкладкам и только в Расценках 3 содержатся работы разных видов в одном списке. Правильно ли я Вас понял, что предлагаете на листе Смета добавить еже один столбец Вид работ с выпадающим списком (например, малярно-штукатурные, отделочные, общестроительные и т.п.), в котором выбрав значение, можно в столбце Наименование работ получать отфильтрованный список непосредственно самих работ? Или речь идет о том, чтобы в Наименовании работ содержался полный список работ со всех листов Расценок? Но как это сделать, диапазоны значений же не смежные, на разных листах расположены.
Виды работ в основном разделены по вкладкам и только в Расценках 3 содержатся работы разных видов в одном списке. Правильно ли я Вас понял, что предлагаете на листе Смета добавить еже один столбец Вид работ с выпадающим списком (например, малярно-штукатурные, отделочные, общестроительные и т.п.), в котором выбрав значение, можно в столбце Наименование работ получать отфильтрованный список непосредственно самих работ? Или речь идет о том, чтобы в Наименовании работ содержался полный список работ со всех листов Расценок? Но как это сделать, диапазоны значений же не смежные, на разных листах расположены.Vahmurca
Сообщение отредактировал Vahmurca - Вторник, 10.11.2020, 19:32
в Наименовании работ содержался полный список работ со всех листов Расценок?
Да это и имел в виду, Вам же проще. сделали доп столбец и туда скинули все виды работ с листов расценок. Притом бы посоветовал листы расценок привести к одному порядку как 1 и 2 для простоты но это в принципе не важно. Удачи еще раз!
в Наименовании работ содержался полный список работ со всех листов Расценок?
Да это и имел в виду, Вам же проще. сделали доп столбец и туда скинули все виды работ с листов расценок. Притом бы посоветовал листы расценок привести к одному порядку как 1 и 2 для простоты но это в принципе не важно. Удачи еще раз!alexa1965
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
