Добрый день, планетяне! Сразу прошу прощения за много букв. Но не знаю уже что тут урезать. Имеется куча смет малоэтажных домов. В разных книгах, и их уже за десяток. Сама же структура Сметы постепенно "эволюционирует" (если можно так сказать) - добавляются материалы, цены на которые бежбожно меняются (от доллара и проч. - позже в планах и парсинг на цены), Добавляются столбцы/коэффициенты, ну и я формулами допиливаю способы вычисления того или иного параметра (например кол-ва досок такой-то ширины чтобы замостить такую-то площадь), прочее. Поэтому вносить все последние изменения в работу каждой отдельной книги на каждый отдельный дом - становится уже накладно. Идея: в какую-то одну (крайнюю версию) моего этого убожества загружать данные по другим домам. А в этой крайней версии(пусть это будет Таблица1) все наиболее последние способы считать, то что считаем, ну и база с материалами. Типа как в сметную программу загружается смета-файл по какому-либо отдельному дому. Возможно это звучит бредово ( прошу, не предлагайте мне купить ГрандСмету/СметуWizard/A0 или что-либо другое - в них будут свои затыки и трудности, и не будет той гибкости что даёт эксель, и без экселя всё равно будет не обойтись. А нужно считать уже сейчас). Хочется чтобы: 1. по нажатию кнопки "Загрузить" написанного макроса , открывалась формочка в которой через обзор (или как-то присанный путь) выбиралась бы нужная книга на компе, и далее в мою горемычную таблицу1 попадали бы данные и относительные формулы соответствующего домика которые; Предполагаю что это можно сделать создав что-то типа доп. системы "координат" из столбца с "кодами"-"ключами, коим я сделал столбец А, и названия заголовков (хотя тут нет умных таблиц) в строке2... см. (рис. 1.) Почему не сами экселевы номера строк и столбцов - потому что будет в будущем изменяться число и строк и столбцов.
Сразу моменты: 1) да нужно чтобы там где значение ячейки равны формуле прилетала именно относительная формулы, а не значение. (рис. 2). Т.к. кол-во строк в вышестоящих разделах и само кол-во разделов может меняться - то тут я ратую именно за относительность формулы - уж не знаю как это реализовать - через формулы в стиле R1C1 (это тоже приемлемо) или что-либо другое. Формула может указывать и на ячейки в других листах. Имена нынешних листов уже не будут меняться. И новые листы пока не появятся. 2) Да, данные должны загружаться и на другие листы... (рис. 7,8,9,10 ). (типа как фундамент, фасады, окна). Данные рисунки не демонстрируют все случаи ссылок на другие листы. Все ячейки в которых должна прилетать относительная формула я уже обозначил светложелтым цветом, На не главных листах (не лист 'ALL') ячейка котороя должна заполниться значением выделена черной заливкой с белым фрифтом. Поможет это или нет - я их расставил почти на все целевые ячейки других листов. Их совсем немного.
3) По количеству строк. Так как кол-во позиций по одно и тому же подразделу в разных домах у может быть разное (рис. 3 и рис 4) и я не хочу сразу набирать/держать строки с запасом, чтобы висела куча пустых - то, да, кол-во строк будет... должно то есть меняться в соответствии с загруженным "файлом сметы" от сметы к смете. Такой вот сатанизм. Окончание каждого подраздела я пока обозначил двойным нижним прочерком в конце "кода соответствия"-тот что в столбце А. Например "инж1__" - окончание подраздела инж1. Начало же подраздела именуется как "инж1"... Итоги разделов тоже наблюдаются явно. Эту структуру "кодов" как я их сейчас в своём дилетанстве придумал, можно и изменить по вашему предложения, главное чтобы работало и мне понятно было ПОТОМ С НИМИ УДОБНО РАБОТАТЬ. Имена разделов, примеры: "основной", "Ф" (фундамент это), "ФАСАД", "КРОВЛЯ", "ТЕРРАСА"... Добавится со временем ещё парочка. Но это потом. Так как присутвуют 5 вариантов по кровле, 4 по фундаменту, 4 по фасаду - то архитектуру кодов я пока предлагаю так... Опять-таки: если есть идея получше предлагайте.
4) Ну и в макросе должен быть быть механизм "сохранения"/выгрузки отредактированных данных обратно в файл сметы... Причем чтобы добавленные строки разделов подразделов тоже "вошли в сохраняемый файл".
5). На строке с именем 'имя подраздела' (например "инж2") есть ячейки суммирующая: стоимость заюзанных материалов по данному подразделу. (рис. 5) И такая же ячейка суммирующая стоимость всех работ. Диапазон того, что они суммируют будет меняться взависимости от кол-ва позиций данного подраздела... Сейчас это реализованно формулой и через именнованные диапазоны. И так как всё это уже начинает притормаживать...
Аналогично мог бы формулой реализовать подсчёт строк "прочие расходы" - которые есть просто денежный запас, выражаемый через произведение указанного процента на суммарную стоимость материалов по подразделу/разделу (рис.6). Количество этих строк, влияющих на эти прочие разделы - может меняться). Надо тоже сделать так чтобы макрос задавал ему правильную формулу. Чтобы руками не перетягивать. В слачаях с итогами подразделов/раздела (рис. 10) так же.
5.1) Ещё нюанс. Если в загружаемом файле дома нет какого либо раздела (рис12) - например нет раздела "инженерные системы ВК" , а в основном файле сметы оный раздел есть(рис.13) - то после загрузки этим макросом - столбцы i и j раздела должны стать пустыми. Сам же раздел должен остаться. Как и его холостые уже формулы в столбцах L и N. В будущем порядок разделов буду менять.
5.2) В столбцах L и N присутствует в большинстве как бы универсальная формула подсчета стоимости по позиции. Нужно чтобы макрос её раскопировал на большинство ячеек столбца L и N кроме строк где считается "Прочие расходы"(запас фактически), и строк итогов разделов/подразделов. Чтобы не приходилось руками копировать.
Вроде как напрашивается вариант через Power Query, но с неё я бы сдернул лишь значения, а не относительные формулы. Ведь так? Или уже нет.. Всё-таки хотелось бы лучше ждать прогрузки макроса а не обновлений запросов PQ (хоть она и замечательна) и потом ещё прогрузки макроса.
Вообще приходится многократно подгонять сметы под кошель заказчика и нужды конторы - поэтому ищутся способы для гибкости. Поверьте, не с пустого места написал тут этот странный запрос и много буковок. Многие строительные конторы ещё более убого считают. В книгах "смета домА", "смета дом Б" -более ранние версии такого же сметного расчёта... И их можно использовать в качестве прототипов загружаемых файлов в главную "Таблицу1"-смету. Всё в приложении в архиве. В Дом Б - чуть попозже довнесу имена диапазонов , если надо... Дом А в общем готов к экспериментам.
Ещё раз прошу прощения что не сделал ещё более кратко. Всё то, что здесь написано сложно - могу объяснить наглядно, за секунды, если открыть на мониторе, скажем, через TeamViewer. Кто желает поработать по данному странному заданию - напишите свои предложения по цены и качества в личку.
Только просьба - если собрались - то идти до конца - то есть если всплывут хотелки/моменты по части доработки того, что я описал здесь (мне хочется чтобы всё работало в разумных пределах универсально и надежно) - разумно договориваемся о цене на данный доп и работаем дальше.Нужен будет какой-то период обкатки. Общаться можно по TeamViewer/hangouts/zoom/skype.