Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

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

 

= Мир MS Excel/Автоматическое заполнение таблицы значениями из других листо - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Автоматическое заполнение таблицы значениями из других листо (Формулы/Formulas)
Автоматическое заполнение таблицы значениями из других листо
Vahmurca Дата: Вторник, 10.11.2020, 00:15 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Имеются расценки различных фирм (листы Расценки 1, Расценки 2, Расценки 3) на строительные работы. Фирмы специализируются на определенном типе работ, например, первая - малярно-штукатурные, вторая - отделочные, третья - общестроительные. Структура расценок в целом схожая, но могут быть отличия в очередности столбцов и их названии.
На основании этих расценок необходимо создать смету, которая включает некоторые работы из расценок строительных фирм. В результате должна получиться единая смета, покрывающая индивидуальный объём работ и включающий работы из разных типов. Список работ в смете должен содержаться в столбце Наименование работ (лист Смета). Для заполнения ячеек в Название работ используются ссылки на выбранные работы соответствующих типов. Например, B2 = ='Расценки 1'!B3.

Задача
При заполнении ячейки в Наименовании работ, автоматически заполнить другие столбцы (Тип ресурса, Ед.изм., Ст-ть за ед.) на основе данных, указанных в соответствующих строках на листах Расценки 1, 2, 3.
При необходимости упростить задачу, можно менять очередность столбцов на вкладках Расценки, а также привести их название к единообразному виду.
Скопировать все расценки на один лист, приведя их к единой структуре, на практике не всегда удобно/возможно.

Как я пытался найти решение
1. Из ссылки ячейки в столбце B (Смета) попробовал выбирать имя листа, но который ссылается конкретная ячейка, пользовался для этого пришлось добавить макрос с переводом содержимого формулы ячейки в текстовую строку.
2. Далее полученный результат обрабатывал функцией ПОИСК, чтобы получить название листа в виде !Расценки 2!.
3. Затем в функции ИНДЕКС и ПОИСКПОЗ передавал название искомой работы из столба B (Смета) и ссылку на нужный лист, по которому нужно провести поиск значений для соответствующих столбцов (Тип ресурса, Ед.изм., Ст-ть за ед.)

Всё заработало, но получилась монструозная формула, которой невозможно управлять. Вот пример из реальной книги.
Код
=ИНДЕКС(  ДВССЫЛ(ПСТР(FText([@[Наименование работ]]);  ПОИСК("'"; FText([@[Наименование работ]]));   ПОИСК("'"; FText([@[Наименование работ]]); ПОИСК("'"; FText([@[Наименование работ]]))+1) - ПОИСК("'"; FText([@[Наименование работ]])) + 2) & "A3:I350");
  ПОИСКПОЗ([@[Наименование работ]];  ДВССЫЛ(ПСТР(FText([@[Наименование работ]]);  ПОИСК("'"; FText([@[Наименование работ]]));   ПОИСК("'"; FText([@[Наименование работ]]); ПОИСК("'"; FText([@[Наименование работ]]))+1) - ПОИСК("'"; FText([@[Наименование работ]])) + 2) & "C3:C350");0);
  ПОИСКПОЗ("Ст-ть за ед.";  ДВССЫЛ(ПСТР(FText([@[Наименование работ]]);  ПОИСК("'"; FText([@[Наименование работ]]));   ПОИСК("'";FText([@[Наименование работ]]); ПОИСК("'"; FText([@[Наименование работ]]))+1) - ПОИСК("'"; FText([@[Наименование работ]])) + 2) & "A3:I3"); 0))


Еще одним недостатком моего решения является необходимость использования макроса для перевода содержимого формулы ячейки в текстовую строку. Аналогичная функция есть в Excel 2013, но к сожалению необходима поддержка Excel 2010.

Вопрос
Есть ли варианты более элегантного и управляемого решения данной задачи?
К сообщению приложен файл: 8885360.xlsx(46.2 Kb)


Сообщение отредактировал Vahmurca - Вторник, 10.11.2020, 09:17
 
Ответить
СообщениеИмеются расценки различных фирм (листы Расценки 1, Расценки 2, Расценки 3) на строительные работы. Фирмы специализируются на определенном типе работ, например, первая - малярно-штукатурные, вторая - отделочные, третья - общестроительные. Структура расценок в целом схожая, но могут быть отличия в очередности столбцов и их названии.
На основании этих расценок необходимо создать смету, которая включает некоторые работы из расценок строительных фирм. В результате должна получиться единая смета, покрывающая индивидуальный объём работ и включающий работы из разных типов. Список работ в смете должен содержаться в столбце Наименование работ (лист Смета). Для заполнения ячеек в Название работ используются ссылки на выбранные работы соответствующих типов. Например, B2 = ='Расценки 1'!B3.

Задача
При заполнении ячейки в Наименовании работ, автоматически заполнить другие столбцы (Тип ресурса, Ед.изм., Ст-ть за ед.) на основе данных, указанных в соответствующих строках на листах Расценки 1, 2, 3.
При необходимости упростить задачу, можно менять очередность столбцов на вкладках Расценки, а также привести их название к единообразному виду.
Скопировать все расценки на один лист, приведя их к единой структуре, на практике не всегда удобно/возможно.

Как я пытался найти решение
1. Из ссылки ячейки в столбце B (Смета) попробовал выбирать имя листа, но который ссылается конкретная ячейка, пользовался для этого пришлось добавить макрос с переводом содержимого формулы ячейки в текстовую строку.
2. Далее полученный результат обрабатывал функцией ПОИСК, чтобы получить название листа в виде !Расценки 2!.
3. Затем в функции ИНДЕКС и ПОИСКПОЗ передавал название искомой работы из столба B (Смета) и ссылку на нужный лист, по которому нужно провести поиск значений для соответствующих столбцов (Тип ресурса, Ед.изм., Ст-ть за ед.)

Всё заработало, но получилась монструозная формула, которой невозможно управлять. Вот пример из реальной книги.
Код
=ИНДЕКС(  ДВССЫЛ(ПСТР(FText([@[Наименование работ]]);  ПОИСК("'"; FText([@[Наименование работ]]));   ПОИСК("'"; FText([@[Наименование работ]]); ПОИСК("'"; FText([@[Наименование работ]]))+1) - ПОИСК("'"; FText([@[Наименование работ]])) + 2) & "A3:I350");
  ПОИСКПОЗ([@[Наименование работ]];  ДВССЫЛ(ПСТР(FText([@[Наименование работ]]);  ПОИСК("'"; FText([@[Наименование работ]]));   ПОИСК("'"; FText([@[Наименование работ]]); ПОИСК("'"; FText([@[Наименование работ]]))+1) - ПОИСК("'"; FText([@[Наименование работ]])) + 2) & "C3:C350");0);
  ПОИСКПОЗ("Ст-ть за ед.";  ДВССЫЛ(ПСТР(FText([@[Наименование работ]]);  ПОИСК("'"; FText([@[Наименование работ]]));   ПОИСК("'";FText([@[Наименование работ]]); ПОИСК("'"; FText([@[Наименование работ]]))+1) - ПОИСК("'"; FText([@[Наименование работ]])) + 2) & "A3:I3"); 0))


Еще одним недостатком моего решения является необходимость использования макроса для перевода содержимого формулы ячейки в текстовую строку. Аналогичная функция есть в Excel 2013, но к сожалению необходима поддержка Excel 2010.

Вопрос
Есть ли варианты более элегантного и управляемого решения данной задачи?

Автор - Vahmurca
Дата добавления - 10.11.2020 в 00:15
китин Дата: Вторник, 10.11.2020, 08:20 | Сообщение № 2
Группа: Модераторы
Ранг: Экселист
Сообщений: 6319
Репутация: 970 ±
Замечаний: 0% ±

Excel 2007;Excel 2010
Vahmurca, - Прочитайте Правила форума
- Приложите файл с исходными данными и желаемым результатом (можно вручную) в формате Excel размером до 500 кб согласно п.3 Правил форума


Не судите строго:я пытаюсь научиться
ЯД 41001877306852/WM R249698041931; Z239672726538
 
Ответить
СообщениеVahmurca, - Прочитайте Правила форума
- Приложите файл с исходными данными и желаемым результатом (можно вручную) в формате Excel размером до 500 кб согласно п.3 Правил форума

Автор - китин
Дата добавления - 10.11.2020 в 08:20
Vahmurca Дата: Вторник, 10.11.2020, 09:21 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
- Приложите файл с исходными данными и желаемым результатом (можно вручную) в формате Excel размером до 500 кб согласно п.3 Правил форума

Браузер вчера вложение не подцепил, а я и не заметил.

Приложил в первое сообщение пример сметы, в которой ссылки на другие листы проставляю вручную.
 
Ответить
Сообщение
- Приложите файл с исходными данными и желаемым результатом (можно вручную) в формате Excel размером до 500 кб согласно п.3 Правил форума

Браузер вчера вложение не подцепил, а я и не заметил.

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

Автор - Vahmurca
Дата добавления - 10.11.2020 в 09:21
alexa1965 Дата: Вторник, 10.11.2020, 11:05 | Сообщение № 4
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 202
Репутация: 12 ±
Замечаний: 0% ±

2003> 2010> 2016
Для листа смета в ячейке С2
Код
=ЕСЛИОШИБКА(ВПР(B2;'Расценки 1'!B$1:J$50;2;0);"")&ЕСЛИОШИБКА(ВПР(B2;'Расценки 2'!B$1:J$50;2;0);"")&ЕСЛИОШИБКА(ВПР(B2;'Расценки 3'!B$1:J$181;0;0);"")
, аналогично для ячейки D2
Код
=ЕСЛИОШИБКА(ВПР(B2;'Расценки 1'!B$1:J$50;3;0);"")&ЕСЛИОШИБКА(ВПР(B2;'Расценки 2'!B$1:J$50;3;0);"")&ЕСЛИОШИБКА(ВПР(B2;'Расценки 3'!B$1:J$181;2;0);"")
, а для ячейки Е2 чуть по другому исходя из порядка поиска данных
Код
=ЕСЛИОШИБКА(ВПР(B2;'Расценки 1'!B$1:J$50;5;0);"")&ЕСЛИОШИБКА(ВПР(B2;'Расценки 2'!B$1:J$50;5;0);"")&ЕСЛИОШИБКА(ВПР(B2;'Расценки 3'!B$1:J$181;6;0);"")
К сообщению приложен файл: 3898737.xlsx(44.8 Kb)


Главное не быть балабастиком

Сообщение отредактировал alexa1965 - Вторник, 10.11.2020, 11:10
 
Ответить
СообщениеДля листа смета в ячейке С2
Код
=ЕСЛИОШИБКА(ВПР(B2;'Расценки 1'!B$1:J$50;2;0);"")&ЕСЛИОШИБКА(ВПР(B2;'Расценки 2'!B$1:J$50;2;0);"")&ЕСЛИОШИБКА(ВПР(B2;'Расценки 3'!B$1:J$181;0;0);"")
, аналогично для ячейки D2
Код
=ЕСЛИОШИБКА(ВПР(B2;'Расценки 1'!B$1:J$50;3;0);"")&ЕСЛИОШИБКА(ВПР(B2;'Расценки 2'!B$1:J$50;3;0);"")&ЕСЛИОШИБКА(ВПР(B2;'Расценки 3'!B$1:J$181;2;0);"")
, а для ячейки Е2 чуть по другому исходя из порядка поиска данных
Код
=ЕСЛИОШИБКА(ВПР(B2;'Расценки 1'!B$1:J$50;5;0);"")&ЕСЛИОШИБКА(ВПР(B2;'Расценки 2'!B$1:J$50;5;0);"")&ЕСЛИОШИБКА(ВПР(B2;'Расценки 3'!B$1:J$181;6;0);"")

Автор - alexa1965
Дата добавления - 10.11.2020 в 11:05
Vahmurca Дата: Вторник, 10.11.2020, 12:17 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Да, понял идею. А еще универсализировать логику возможно, т.е. чтобы при добавлении еще одного листа с расценками не нужно было бы вносить изменения во все формулы?

Думал еще в сторону того, что, поскольку формулы для искомых ячеек в Смета типизированы, то хорошо бы саму большую формулу вывести в содержимое отдельной ячейки и затем в расчётах подтягивать её синтаксис из этой ячейки. Так можно было бы добиться существенного упрощения её администрирования - изменяем в одном месте и новая логика используется во всех соответствующих ячейках. Но пока не придумал такой способ.

В любом случае, большое спасибо! Решение намного компакнее моего и им уже можно пользоваться.
 
Ответить
СообщениеДа, понял идею. А еще универсализировать логику возможно, т.е. чтобы при добавлении еще одного листа с расценками не нужно было бы вносить изменения во все формулы?

Думал еще в сторону того, что, поскольку формулы для искомых ячеек в Смета типизированы, то хорошо бы саму большую формулу вывести в содержимое отдельной ячейки и затем в расчётах подтягивать её синтаксис из этой ячейки. Так можно было бы добиться существенного упрощения её администрирования - изменяем в одном месте и новая логика используется во всех соответствующих ячейках. Но пока не придумал такой способ.

В любом случае, большое спасибо! Решение намного компакнее моего и им уже можно пользоваться.

Автор - Vahmurca
Дата добавления - 10.11.2020 в 12:17
alexa1965 Дата: Вторник, 10.11.2020, 13:12 | Сообщение № 6
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 202
Репутация: 12 ±
Замечаний: 0% ±

2003> 2010> 2016
т.е. чтобы при добавлении еще одного листа с расценками не нужно было бы вносить изменения во все формулы?

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


Главное не быть балабастиком
 
Ответить
Сообщение
т.е. чтобы при добавлении еще одного листа с расценками не нужно было бы вносить изменения во все формулы?

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

Автор - alexa1965
Дата добавления - 10.11.2020 в 13:12
Vahmurca Дата: Вторник, 10.11.2020, 16:43 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Виды работ в основном разделены по вкладкам и только в Расценках 3 содержатся работы разных видов в одном списке. Правильно ли я Вас понял, что предлагаете на листе Смета добавить еже один столбец Вид работ с выпадающим списком (например, малярно-штукатурные, отделочные, общестроительные и т.п.), в котором выбрав значение, можно в столбце Наименование работ получать отфильтрованный список непосредственно самих работ? Или речь идет о том, чтобы в Наименовании работ содержался полный список работ со всех листов Расценок? Но как это сделать, диапазоны значений же не смежные, на разных листах расположены.


Сообщение отредактировал Vahmurca - Вторник, 10.11.2020, 19:32
 
Ответить
СообщениеВиды работ в основном разделены по вкладкам и только в Расценках 3 содержатся работы разных видов в одном списке. Правильно ли я Вас понял, что предлагаете на листе Смета добавить еже один столбец Вид работ с выпадающим списком (например, малярно-штукатурные, отделочные, общестроительные и т.п.), в котором выбрав значение, можно в столбце Наименование работ получать отфильтрованный список непосредственно самих работ? Или речь идет о том, чтобы в Наименовании работ содержался полный список работ со всех листов Расценок? Но как это сделать, диапазоны значений же не смежные, на разных листах расположены.

Автор - Vahmurca
Дата добавления - 10.11.2020 в 16:43
alexa1965 Дата: Среда, 11.11.2020, 06:57 | Сообщение № 8
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 202
Репутация: 12 ±
Замечаний: 0% ±

2003> 2010> 2016
в Наименовании работ содержался полный список работ со всех листов Расценок?

Да это и имел в виду, Вам же проще. сделали доп столбец и туда скинули все виды работ с листов расценок. Притом бы посоветовал листы расценок привести к одному порядку как 1 и 2 для простоты но это в принципе не важно. Удачи еще раз!


Главное не быть балабастиком
 
Ответить
Сообщение
в Наименовании работ содержался полный список работ со всех листов Расценок?

Да это и имел в виду, Вам же проще. сделали доп столбец и туда скинули все виды работ с листов расценок. Притом бы посоветовал листы расценок привести к одному порядку как 1 и 2 для простоты но это в принципе не важно. Удачи еще раз!

Автор - alexa1965
Дата добавления - 11.11.2020 в 06:57
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Автоматическое заполнение таблицы значениями из других листо (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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