Очевидно, что работать с таблицами удобнее в Excel, а вот оформление (основная надпись — штамп и рамка) лучше делать в AutoCAD. Сегодня расскажу, как вставить таблицу Excel в AutoCAD, как связать эти два файла так, чтобы при изменении данных в Excel, в AutoCAD автоматически всё обновлялось. А главное, покажу настройки, с которыми AutoCAD не тормозит при обновлении данных.
Чтобы информация была максимально полезной и понятной, буду делать на примере спецификации. Аналогичным образом можно сделать связь для кабельного журнала.
Шаг 1. Заполняем таблицу в Excel
Для начала скачайте файлы шаблонов спецификации по ГОСТ и заполните таблицу в Excel. Тут всё как обычно, но есть один очень важный момент: в первом столбике Поз. не должно быть пустых ячеек. Иначе дальше не сработает.
Спецификация в Excel
Шаг 2. Умные формулы в Excel
Далее на ленте Excel переходим на вкладку Формулы и нажимаем кнопку Диспетчер имён.
Кнопка Диспетчер имён на ленте Excel
Появится окошко. Там уже всё настроено, просто проверьте, что ничего не сломалось. Заодно я объясню, как это работает. Если будете работать со своими таблицами, сможете сделать аналогично.
Header — это диапазон «шапки» нашей таблицы. Там должна быть такая формула:
=Спецификация!$A$1:$D$1
Спецификация — название листа. $A$1:$D$1 — диапазон данных на листе (шапка таблицы).
Окно диспетчер имён в Excel. Формула для диапазона Header
Data — это диапазон данных, который формируется автоматически.
Там должна быть такая формула:
=СМЕЩ(Спецификация!$A$2;0;0;СЧЁТЗ(Спецификация!$A:$A)+1;9)
Окно диспетчер имён в Excel. Формула для диапазона Data
В этой формуле одна функция вложена в другую.
Функция СЧЁТЗ подсчитывает число непустых ячеек в диапазоне (в нашем случае столбик «А»). Как только обнаружится пустая ячейка в столбике «А», то формула считает её концом таблицы. Именно поэтому выше акцентировал внимание на том, что в столбике А не должно быть пропусков ячеек.
Функция СМЕЩ возвращает ссылку на диапазон, смещённый относительно указанной ячейки (диапазона ячеек) на нужное число строк и столбцов. У неё пять аргументов:
- Ссылка — ячейка, от которой нужно отсчитывать. Берём верхнюю левую ячейку с данными таблицы. В нашем случае это ячейка «А2» на листе «Спецификация».
- Смещ_по_строкам. Мы будем отсчитывать от ячейки А2. Поэтому в формуле 0.
- Смещ_по_столбцам. Мы будем отсчитывать от ячейки А2. Поэтому в формуле 0.
- Высота — высота в строках результирующего диапазона (т.е. нашей таблицы). Сюда вставляем формулу СЧЁТЗ, прибавляя ещё единицу. Таким образом, у нас в конце спецификации будут ещё две пустых ячейки, т.к. СЧЁТЗ посчитала ещё и первую строчку — шапку таблицы.
- Ширина — ширина в столбиках результирующего диапазона (т.е. нашей таблицы). Указываем 9, т.к. в нашей спецификации фиксированная ширина — 9 столбиков.
Когда в спецификацию будут добавлены новые строки, то диапазон данных автоматически расширится, т.к. функция СЧЁТЗ посчитает число заполненных ячеек в первом столбике листа и автоматически расширит таблицу. Когда лишние строки из таблицы будут удалены, диапазон данных автоматически уменьшится. В итоге, для передачи в AutoCAD у нас всегда будет таблица необходимого размера.
Кстати, если в каком-то другом случае ширина таблицы тоже может меняться, то в аргумент Ширина прописываем аналогичную формулу СЧЁТЗ, только указывая подсчёт по первой строке: $1:$1.
Шаг 3. Настройка в AutoCAD
Открываем шаблон спецификации в AutoCAD с пустой таблицей.
3.1. Настройка связи для шапки таблицы
Выделяем ячейки шапки таблицы (надо выделить всю строку заголовков). Появляется панель с кнопками редактирования таблицы.
Спецификация в AutoCAD. Настройка связи с Excel. Заголовок таблицы
Нажимаем кнопку «Связь ячейки...» — она вторая справа. Появляется окошко «Выбор связи с данными».
Окно создания связи AutoCAD и Excel
Нажимаем «Создайте новую связь с данными Excel» и вводим название новой связи — Header. Появляется окно настройки связи с данными Excel.
Выбираем наш Excel-файл cо спецификацией. И устанавливаем ниже «Относительный путь». В параметрах связи выбираем из списка нужный лист (в нашем случае единственный лист «Спецификация»), устанавливаем переключатель в положение «Связь с именованным диапазоном» и выбираем из списка диапазон «Header».
Настройка в AutoCAD новой связи с данными в Excel. Настройка диапазона данных
Дальше в правом нижнем углу этого окошка нажимаем на кнопку со стрелкой, чтобы появились настройки форматирования и вносим некоторые изменения:
- В блоке «Содержимое ячеек» устанавливаем переключатель в положение «Сохранить форматы данных, вычислить формулы в Excel».
- Флажок «Разрешить запись в исходный файл» снимаем. Так получится односторонняя связь — изменения вносим только в Excel, а в AutoCAD будет редактирование будет недоступно.
- В блоке «Форматирование ячеек» флажок «Использование форматирования Excel» отключаем, переключатели ниже должны быть неактивны.
Должно получиться как на картинке.
Настройка в AutoCAD новой связи с данными в Excel. Настройка форматирования
Нажимаем кнопку «Ок». Связь для шапки таблицы настроена.
3.2. Настройка связи для данных таблицы
Устанавливаем курсор на первую пустую ячейку в первом столбике, сразу под «шапкой» таблицы.
Спецификация в AutoCAD. Настройка связи с Excel. Данные таблицы
Далее действуем аналогично настройке связи для заголовка таблицы. Нажимаем кнопку «Связь ячейки...». Создаём новую связь с именем Data, выполняя аналогичные настройки, только выбираем именованный диапазон Data. Настройки форматирования делаем точно такие же, как для заголовка.
Нажимаем Ок. Связь с данными установлена. AutoCAD сразу же загрузит данные из таблицы Excel в таблицу AutoCAD. Остаётся удалить лишние строки, которые были в таблице шаблона. При этом строки, которые охватывает связь с Excel, удалить не получится.
Заполненная спецификация в AutoCAD после настройки связи с Excel
Шаг 4. Обновление данных
При изменении данных в таблице Excel, сразу после сохранения файла, в AutoCAD в правом нижнем углу появится уведомление.
Уведомление в AutoCAD об изменении данных в связанной таблице Excel
Для импорта обновлённых данных из таблицы Excel в AutoCAD просто нажмите на синюю ссылку в уведомлении. AutoCAD импортирует и перезапишет изменённые данные в связанной таблице.
Спасибо, очень полезное, подробное и понятное руководство.
Подскажите, пожалуйста как связать таблицы excel и autocad для кабельного журнала? Аналогично как для спецификации не получается... можно также показать на примере кабельного журнала 7?
Со спецификацией у вас получилось сделать?
Да, со спецификацией получилось, только первая строка в автокаде становится шире чем остальные, но это мелочи. А вот с кабельным журналом не выходит, я также в exel делаю header data, только меняю в формуле а2 на а4 и вместо +1 делаю смещение на 3, но не выходит...
Смещение на 3 добавит слишком много пустых строчек.
Вероятно, вы не учли, что в формуле словом «Спецификация» — это название листа книги Excel.
Для КЖ по ГОСТ 21.608-2014 форма 6 формула для диапазона Data будет вот так выглядеть:
=СМЕЩ(КЖ!$A$4;0;0;СЧЁТЗ(КЖ!$A:$A);9)
КЖ — название листа в книге.
Этот диапазон охватывает данные + одну пустую строку. Если надо больше или меньше — добавляйте + или — после СЧЁТЗ.
Название листа учитывала конечно же, формулу сейчас попробую, для формы 7 такая же будет? А как быть с header? Если делать как в спецификации, то ячейки в шапке не объединяются...
9 в самом конце формулы — число столбиков. Для разных форм КЖ оно разное.
$A$4 — первая ячейка диапазона с данными.
Для Header с объединенными ячейками не получается. Оставьте Header в AutoCAD как есть, без связи. Он же не меняется, в конце концов.
Да, спасибо, так работает, только первая строка после шапки и все строки, начиная с третьего столбика, с маленькой высотой текста. При передаче в автокаде... с этим можно что нибудь сделать?
Сначала настройте шрифт в автокадовской таблице такой, какой должен быть.
После этого при создании связи установите настройки как в статье указано.
И всё будет хорошо — данные из Excel, форматирование из AutoCAD.
Да.Спасибо вам огромное.
1. Для чего вообще связывть Excel и AutoCAD в плане спецификации? Спецификация прекрасно делается штатной таблицей AutoCAD и вставляется на лист видовым экраном. То же и с кабельным журналом.
2. Для чего ограничение на нумерацию? Его нельзя обойти вспомогательным столбцом, например? ГОСТ вообще вроде бы разрешает нумерацию только при использовании этих материалов, скажем, в сборочном чертеже, где они маркированы.
1. На этот счёт есть пояснение в статье. Таблицу в Excel редактировать гораздо удобнее, чем таблицу в AutoCAD. В Excel можно сделать дополнительные столбики, в которых записывать данные по этажам, щитам, чертежам и т.п., а в столбик таблицы спецификации выводить сумму. В AutoCAD это сделать на порядок сложнее. А связь нужно для того, чтобы каждый раз вручную не переносить таблицу из Excel в AutoCAD при изменении данных.
2. Да, можно использовать вспомогательный столбик для этих целей. Требованиям к нему будут аналогичные — иначе формула на вычисление строк не сработает так, как нужно.
1. Добавлять столбики в каде можно одной кнопкой. Сумму — в 3 клика. В чём «на порядок сложнее»? Или проще возиться с настройками в Excel? Я понимаю, если сделан расчёт щита или ещё что-то подобное, где используются формулы или макросы, но для спецификаци... Да и то сомнительно, часто щитов много. Проще сделать экспорт из Excel в AutoCAD, средствами VBA, например. В общем, извините, но для КЖ и СП связь — это масло масляное, круто, но бесполезно, временные затраты будут выше, чем при использовании встроенных таблиц. Да и при сдаче заказчику чем меньше файлов — тем лучше. Не все такие продвинутые, знают, что такое связь.
Редактирование таблиц в AutoCAD гораздо сложнее, чем в Excel. Это очевидный факт и нет смысла его оспаривать.
Чтобы сделать экспорт из Excel в AutoCAD средствами VBA, надо владеть средствами VBA. Настроить связь гораздо проще, чем научиться программировать и написать макрос по такому экспорту. Или у вас другое мнение на этот счёт?
Да, другое. Если это было непонятно при моём первом ответе — я Вам сочувствую...
И в чём сложнее? Касательно СП и КЖ. Я уже ведь спрашивал. Или проще ответить «это же факт»? Почему факт? Кто это сказал? Вы? И от этого это стало фактом? VBA я написал в контексте серьёзных расчётных таблиц, которые могут делать уже сам щит, его наполнение. Знаете, у Вас, пожоже, 2 проблемы: 1. Вы даже не вчитываетесь в комметарии, которые Вас не устраивают. 2. Отсюда проистекает, что для Вас есть 2 мнения — Ваше и неправвильное. ИМХО. На этом отписывают от Вашего сайта))
«Да, другое» означает, что обычному пользователю AutoCAD проще и быстрее научиться программировать чтобы перенести данные из таблицы Excel в таблицу AutoCAD, чем настроить связь встроенными средствами? Вы серьёзно так считаете?
Берём шаблоны с сайта, открываем эту статью, настраиваем связь за несколько минут — абсолютно ничего не понимая в этом до прочтения статьи.
Сколько уйдёт времени на изучение программирования и написания программы по переносу?
Только на написание уйдёт точно больше времени, чем на настройку связи. А на изучение вообще не один день.
На редактирование ячеек таблицы AutoCAD требуется больше времени, чем на редактирование ячеек таблицы Excel.
И времени больше надо и телодвижений. Так уж устроены таблицы в AutoCAD. Или у вас особенная версия AutoCAD, в которой таблицы редактируются так же, как в Excel? Я сомневаюсь.
Даже добавление столбиков в AutoCAD занимает больше времени и требует больше действий.
Возьмём одинаковые исходные данные:
В AutoCAD открыт файл, активно пространство с таблицей. В Excel открыт файл, активен лист с нужной таблицей.
Добавляем столбик в AutoCAD:
1. Выделяем таблицу
2. Выделяем столбик
3. Нажимаем кнопку добавления столбика
Добавляем столбик в Excel: сразу переходим к п.2, т.к. таблицу выделять не надо. Выходит быстрее, чем в AutoCAD. А вы утверждали, что так же просто и быстро.
Я попытался придумать, что можно с таблицей AutoCAD сделать быстрее и проще, чем с таблицей Excel. И не придумал ничего, кроме как удалить.
Добрый день. (комент через социалки не получается, поэтому здесь добавил вопрос).
— как в шаблоне АКАД на 1-ом листе изменить «шапку» штампа (АС и она выше в 2 раза)?
— нижние строки в АКАДе не будут накладываться?
В шаблоне есть динамический блок с большим штампом. Меняете на него.
Потом выделяете таблицу, на первом листе у неё треугольник посередине нижнего края, — тянете его вверх до нужной высоты.
Добрый день, огромное спасибо за статью. Отдельно хотел уточнить. Можно ли как-нибудь отредактировать формулу таким образом что бы переносились только те позиции (строки) которые мне необходимы. Например: у меня есть перечень оборудование, и там где кол-во больше 0 то это и нужно экспортировать в автокад, а то оборудование где кол-во равно 0, не трогать?
Если у вас получится сделать формулой диапазон ячеек, состоящий из нескольких несвязанных диапазонов, то сможете решить свою задачу. Обычное скрытие строк или фильтр строк по значению ячейки ситуацию не решают.
Добрый день!
Вопрос: - как можно поменять стиль основной надписи?
— цвет текста синий, как по умолчанию сделать белый?
Спасибо!
Изменить цвет слоя, в котором находится основная надпись.
Привет. Спасибо за Ваш труд. Подскажите пожалуйста, при формировании КЖ на листе 5 и далее формируются только 10 строк, остальные переносятся на следующий лист. При этом на листах 2-4 по 12 строк. Как можно пофиксить этот момент?
Речь же про КЖ в AutoCAD? В автокадовской таблице есть ручки снизу. Нужно их потянуть до нужной высоты. К сожалению, автокадовские таблицы так устроены, что индивидуальные высоты задаются не так, как хотелось бы. Логичнее было бы выставить высоту таблицы для всех листов, а для первого сделать меньше. Но в AutoCAD работает наоборот.
Вроде пробовал так, но не тянул, а нажимал. Ваш совет помог. Огромное спасибо!