Вставка данных таблицы excel в шаблон
Шаблон формата Excel может быть как библиотекой наборов стилей форматирования, так и готовым документом с таблицами, данными, форматами и графиками. В шаблоны-документы достаточно только ввести свои данные, чтобы получить готовый, тематически стильно оформленный, презентабельный отчет. Например, «Семейный бюджет» – очень полезный встроенный шаблон в Excel.
Создание шаблона в Excel
Создадим свой новый шаблон, в котором следует использовать стили форматирования. Как форматировать с помощью стилей мы знаем из предыдущего урока: стили ячеек и средства управления ими. Чтобы сделать шаблон в Excel, выполним определенный ряд действий, которые послужат практическим примером для решения данной задачи:
- Сначала нужно сделать шаблон таблицы Excel и график, как показано ниже на рисунке. И присвойте ей пользовательский стиль форматирования.
- Сохраняем документ как шаблон: «Файл»-«Сохранить как». В окне сохранения можно указать любую папку, так как Excel потом автоматически перенесет этот файл в специальную директорию для хранения нестандартных шаблонов.
- В поле тип файла следует указать «Шаблоны формата Microsoft Office Excel *.xltx». Заметьте, как только мы выбираем этот тип файла, автоматически открывается папка «Шаблоны» в окне сохранения документа.
- В поле: «Имя файла» введите любое название шаблона. Например, «мои расходы». И жмите СОХРАНИТЬ.
- После сохранения закройте документ.
Теперь чтобы использовать наш шаблон достаточно выбрать меню: «Файл»-«Создать»-«Мои шаблоны». Выберите свой шаблон «мои расходы».
Откройте и сохраните этот документ как обычный файл в формате *.xlsx. А теперь обратите внимание на то, что наш пользовательский стиль так же сохранен в данной книге.
Таким образом, у вас есть готовая форма для быстрого создания отчетов, в которой нужно только изменять данные и не нужно ничего форматировать. Это очень удобно и продуктивно. Используйте возможности шаблонов по максимуму!
Создание файла по шаблону Excel
Можно установить свой пользовательский шаблон, по которому будет оформлен и отформатирован документ при каждом запуске программы Excel. Для этого следует сохранить файл с расширением *.xlsx в папке XLSTART. Данная папка создается при установке программы Excel. Чтобы легко найти путь к ней следует перейти в режим VisualBasic (Alt+F11) и открыть окно: «View»-«Immediate». А там следует набрать код: ? application.StartupPath и нажать Enter. Ниже отобразиться путь:
Примечание. В разных версиях Windows путь к XLSTART может отличаться, поэтому лучше узнать его через выше описанные действия.
Теперь при каждой загрузке программы будет открыт файл который лежит в этой папке. Более того если вы сохраните там несколько документов, то все они будут открыты при каждой запуске программы.
Полезный совет! Если в папке XLSTART сохранить отформатированный шаблон с именем Лист.xltx. То каждый новый созданный лист будет иметь такой же формат как Лист.xltx. При том не зависимо, в какой способ создан новый лист, через меню или комбинацией горячих клавиш SHIFT+F11. Формат будет один и тот же. А чтобы отменить такую функцию нужно просто удалить этот шаблон из XLSTART.
Форматирование документов – это важный, но очень утомительный процесс. Для его автоматизации существует несколько полезных инструментов такие как: темы, стили и шаблоны. Каждый из них обладает своими преимуществами и недостатками. Например, самый простой способ автоматизации форматирования – это стили. Они облегчаю пользователю процесс форматирования, но могут быть сохранены только в рамках одной книги. Да созданные или измененные пользователем стили можно скопировать в другую книгу. Или создать отдельную, специальную книгу с целым набором свих пользовательских стилей, чтобы копировать их в другие документы, но это крайне неудобно. Для решения такой задачи лучше использовать шаблоны.
Создание темы документа для автоматического форматирования
Тема Excel – это собрание стилей, цветов, шрифтов и много разных графических свойств форматирования, которые легко можем менять в пару кликов мышкой для изменения стильного оформления документа. Темы стали доступны начиная с 2007-й версии и новее. Их можно встретить и в других продуктах входящих в пакет MS Office.
Для примера возьмем простой не форматированный документ и будем присваивать ему разные темы пока не выберем подходящую к данным. Заодно посмотрим, что произойдет выбрать другую тему Excel:
- Создайте новую книгу и заполните ее данными (начиная со столбца H) так как показано на рисунке:
- Выберите инструмент: «Разметка страницы»-«Темы».
- В выпадающем списке наводите курсор мышки на разные темы и обратите внимание на то, как меняется стиль оформления данных документа.
- Щелкните по подходящей теме, которая на Ваш взгляд наиболее подходит к данному документу.
Внимание! Темы присваиваются к целому документу. Поэтому нельзя для разных листов задавать разные темы форматирования.
Пользователь имеет возможность самостоятельно создавать и редактировать любую тему. Для этого доступны 3 инструмента, которые находятся рядом возле тем:
Каждый из этих инструментов открывает целые предопределенные наборы для настройки темы. Можно создавать новые наборы шрифтов или наборы цветов, только нельзя создать новые эффекты. Приходится использовать только те, которые предложены программой по умолчанию.
Как только вы сохраните новый набор шрифтов и/или цветов под своим названием, тогда она будет доступна в разделе тем как для Excel, так и для других программ из пакета MS Office (Word, PowerPoint и др.).
Программа заполнения документов позволяет вставлять в шаблоны Word не только значения из ячеек выделенной строки, но и целые таблицы, которые могут храниться на другом листе (или в другом файле Excel).
Что для этого нужно:
1. Включаем в настройках программы опцию вставки таблиц:
2. В исходной таблице Excel создаем столбец, где у нас будут храниться ссылки на вставляемые таблицы (столбец может называться как угодно),
выделяем ячейку в этом столбце (в строке с данными),
и нажимаем комбинацию клавиш Ctrl + Shift + T для вызова диалогового окна выбора таблицы:
3. Из выпадающего списка выбираем имя листа, содержащего таблицу
(предварительно, можно выбрать другой файл, - если вставляемая таблица расположена во внешнем файле),
выбираем диапазон ячеек, и один из 4 доступных режимов вставки таблицы Excel в Word:
4. В выделенную ячейку исходной таблицы будет вставлена запись примерно такого вида:
По этой записи надстройка FillDocuments понимает, что в шаблон Word, на место метки ,
надо вставлять не текст ячейки, а таблицу, параметры вставки которой сохранены в этой ячейке.
Что обозначают параметры (разделенные слешем):
- это метка, которая говорит надстройке, что надо вставлять таблицу, а не текст ячейки
платежи - имя листа, с которого будет взята таблица
график - адрес или имя диапазона ячеек, который будет скопирован в шаблон Word
Excel - название режима вставки
Поэкспериментировав с выбором различных диапазонов и режимов, вы можете получить другие варианты метки, например:
34.xlsm/Спецификация/A2:F3/Word
Эта метка означает, что таблица будет скопирована из файла 34.xlsm, расположенного в папке Таблицы
(папка должна иметь именно такое название - Таблицы, и располагаться там же, где находится файл надстройки FillDocuments),
копироваться будет диапазон ячеек с A2 по F3, и при вставке будет выбран режим «использовать форматирование Word»
Что же делать, если количество строк во вставляемой таблице меняется, и задать фиксированный диапазон таблицы не получится?
Есть 2 варианта решения проблемы:
Способ 1: вместо выбора диапазона, поставить галочку «все заполненные ячейки»
Обратите внимание - кнопка выбора диапазона становится недоступной, а в поле адреса высвечивается,
какой диапазон ячеек Excel считает заполненным на этом листе.
В диапазон могут попасть «пустые» строки или столбцы, - на самом деле, для Excel они не совсем пустые,
а, например, содержат какое-то форматирование или формулы.
Потому, чтобы этот способ корректно работал, необходимо выделить целиком лишние («пустые») строки и столбцы,
и нажать в меню Excel «Очистить - Очистить всё»
После этого (сохранения, закрытия и повтороного открытия файла со вставляемой таблицей),
в диапазон «заполненных ячеек» попадут только нужные ячейки.
Способ 2: сделать динамическую таблицу
Этот вариант посложнее (надо немного разбираться в формулах) - но он более универсальный,
и позволяет автоматизировать заполнение подставляемой таблицы (например, графика платежей)
Во вложении к статье - пример такой таблицы.
В первой строке таблицы прописаны формулы, и протянуты (с запасом) на 200 строк вниз
(т.е. во всех строках - одни и те же формулы)
Рамки ячеек в заполненных строках выполнены при помощи условного форматирования
(назначено правило для 5 столбцов - рисуем рамки, если ячейка в первом столбце непустая)
А имя нужного диапазона ячеек задано в диспетчере имён
(чтобы имя график всегда ссылалось на таблицу целиком,. независимо от количества строк, и чтобы в диапазон не попадали лишние строки и столбцы)
Имени график назначена формула =СМЕЩ(платежи!$A$1;0;0;платежи!$H$5+2;5)
Как и для любых других типов шаблонов, подготовка шаблона сводится к подстановке кодов полей типа , в ячейки файла шаблона, где ФИО и должность — название столбцов из исходной таблицы Excel с данными для подстановки.
Подготовить шаблон очень просто:
- берём за основу готовый файл документа (образец)
- заменяем в нём в нужных местах текст на коды полей
(например, заменяем Иванов Сергей Петрович на , чтобы потом при заполнении на место программа подставила данные из таблицы Excel) - сохраняем полученный файл под любым именем (например, акт.xlsx) в папку с шаблонами
Особенности настройки шаблонов Excel:
1. Проблема с искажением подставленных значений
Поскольку Excel любит автоматически форматировать введенные в ячейку значения (например, введите в любую ячейку текст 12-06, увидите в ячейке дату 12 июля), то при подстановке некоторых значений (артикулов, цен) они могут искажаться.
Например, артикул может потерять нули в начале (вставьте в ячейку текст 000123, увидите в ней 123, — Excel обрезал «лишние» нули). И не всегда установка текстового формата ячейки решает проблему (Excel всё равно проявляет инициативу)
Решается проблема просто, - добавлением одинарного апострофа ' перед кодом поля.
Т.е. в ячейку шаблона пишем не , а '
Этот апостроф не будет виден в ячейке, — он даёт экселю понять, что в ячейке находится текстовое значение, и что не надо его никак коверкать автоформатированием.
2. Подстановка чисел, с последующим их суммированием
Когда программа подставляет данные в шаблон, она всегда использует отображаемый текст из ячейки исходной таблицы:
Таким образом, в шаблон Excel будет подставлено не хранящееся в ячейке значение (число 12345,6789), а ТЕКСТ (возможно, с пробелом, и другим десятичным разделителем) «12 345,68»
Соответственно, на ячейку с таким текстом потом в сформированном документе не получится сослаться из формулы вроде =СУММ(), т.к. формула будет выдавать ошибку.
- РЕКОМЕНДУЕМОЕ: использовать модификатор кода поля value.
Это позволит подставлять в шаблон именно значение ячейки, а не отображаемый в ячейке текст. - убрать форматирование числа с разделителем десятичных разрядов в исходной таблице Excel, или же сделать дополнительный столбец в исходной таблице, где будут отображаться те же числа, но без форматирования (формат ячеек Общий)
- для суммирования подставленных числовых значений, вместо формулы вида =СУММ(G$6:G10), использовать формулу массива вида =СУММ(ЗНАЧЕН(G$6:G10))
ВНИМАНИЕ: формула массива вводится нажатием Ctrl + Shift + Enter вместо Enter
3. Подстановка значений в формулы
В шаблонах Excel коды полей (например, ) можно не только вставлять в ячейки в качестве значений, но и использовать в формулах.
Правда, использование этой возможности категорически не рекомендуется.
Гораздо проще (и правильнее) поместить код поля в невидимую ячейку (например, в скрытом столбце, под картинкой, или с белым цветом шрифта), и сделать так, чтобы формулы ссылались на эту «невидимую» ячейку.
Пример такого использования описан в инструкции по заполнению сложных шаблонов Excel
4. Поставлять данные только на определённые листы шаблона
Если шаблон Excel состоит из множества листов, и его заполнение занимает много времени, — есть возможность указать конкретные листы шаблона, в которых программа должна выполнять подстановку.
Такая опция есть в настройках на вкладке Дополнительно:
Комментарии
Возможно ли как то решить вопрос с заполнением шаблона excel на защищенный паролем лист (при этом подстановка в ячейки редактирование которых разрешено)
Тем, кто постоянно работает с Microsoft Excel, часто приходится задаваться вопросом правильного копирования данных таблицы с сохранением форматирования, формул или их значений.
Как вставить формулу в таблицу Excel и сохранить формат таблицы? Ведь при решении данной задачи можно экономить вагон времени. Для этого будем использовать функцию «Специальная вставка» – это простой инструмент, который позволяет быстро решить сложные задачи и освоить общие принципы его использования. Использование этого простого инструмента позволяет вам быстро приводить все таблицы к однообразному формату и получать тот результат, который необходим.
Как скопировать таблицу с шириной столбцов и высотой строк
Допустим, у нас есть такая таблица, формат которой необходимо сохранить при копировании:
При копировании на другой лист привычным методом Ctrl+C – Ctrl+V. Получаем нежелательный результат:
Поэтому приходится вручную «расширять» ее, чтобы привести в удобный вид. Если таблица большая, то «возиться» придется долго. Но есть способ существенно сократить временные потери.
Способ1:Используем специальную вставку
- Выделяем исходную таблицу, которую нам необходимо скопировать, нажимаем на Ctrl+C.
- Выделяем новую (уже скопированную) таблицу, куда нам необходимо формат ширины столбцов и нажимаем на ячейку правой кнопкой мыши, после чего в выпадающем меню находим раздел «Специальная вставка».
- Выбираем в нем необходимый пункт напротив опции «ширина столбцов», нажимаем «ОК».
Она получила исходные параметры и выглядит идеально точно.
Способ 2: Выделяем столбцы перед копированием
Секрет данного способа в том, что если перед копированием таблицы выделить ее столбцы вместе с заголовками, то при вставке ширина каждого столбца будет так же скопирована.
Для каждого случая рационально применять свой способ. Но стоит отметить, что второй способ позволяет нам не только быстро переносить таблицу вместе с форматом, но и копировать высоту строк. Ведь в меню специальной вставки нет опции «высоту строк». Поэтому для решения такой задачи следует действовать так:
Полезный совет! Самый быстрый способ скопировать сложную и большую таблицу, сохранив ее ширину столбцов и высоту строк – это копировать ее целым листом. О том, как это сделать читайте: копирование и перемещение листов.
Вставка значений формул сохраняя формат таблицы
Специальная вставка хоть и не идеальна, все же не стоит недооценивать ее возможности. Например, как вставить значение формулы в таблицу Excel и сохранить формат ячеек.
Чтобы решить такую задачу следует выполнить 2 операции, используя специальную вставку в Excel.
Так как скопированный диапазон у нас еще находится в буфере обмена после копирования, то мы сразу еще раз вызываем специальную вставку где выбираем опцию «форматы». Жмем ОК.
Мы вставили значения формул в таблицу и сохранили форматы ячеек. Как вы догадались можно сделать и третью операцию для копирования ширины столбцов, как описано выше.
Полезный совет! Чтобы не выполнять вторую операцию можно воспользоваться инструментом «формат по образцу».
Microsoft Excel предоставляет пользователям практически неограниченные возможности для подсчета простейших функций и выполнения ряда других процедур. Использование программы позволяет устанавливать форматы, сохранять значения ячеек, работать с формулами, переносить и изменять их, таким образом, как это удобно для пользователей.
Имеем базу данных (список, таблицу - называйте как хотите) с информацией по платежам на листе Данные:
Задача: быстро распечатывать приходно-кассовый ордер (платежку, счет-фактуру. ) для любой нужной записи выбранной из этого списка. Поехали!
Шаг 1. Создаем бланк
На другом листе книги (назовем этот лист Бланк) создаем пустой бланк. Можно самостоятельно, можно воспользоваться готовыми бланками, взятыми, например, с сайтов журнала "Главный Бухгалтер" или сайта Microsoft. У меня получилось примерно так:
В пустые ячейки (Счет, Сумма, Принято от и т.д.) будут попадать данные из таблицы платежей с другого листа - чуть позже мы этим займемся.
Шаг 2. Подготовка таблицы платежей
Прежде чем брать данные из таблицы для нашего бланка, таблицу необходимо слегка модернизировать. А именно - вставить пустой столбец слева от таблицы. Мы будем использовать для ввода метки (пусть это будет английская буква "икс") напротив той строки, данные из которой мы хотим добавить в бланк:
Шаг 3. Связываем таблицу и бланк
Для связи используем функцию ВПР (VLOOKUP) - подробнее про нее можно почитать здесь. В нашем случае для того, чтобы вставить в ячейку F9 на бланке номер помеченного "x" платежа с листа Данные надо ввести в ячейку F9 такую формулу:
Т.е. в переводе на "русский понятный" функция должна найти в диапазоне A2:G16 на листе Данные строку, начинающуюся с символа "х" и выдать нам содержимое второго столбца этой строки, т.е. номер платежа.
Аналогичным образом заполняются все остальные ячейки на бланке - в формуле меняется только номер столбца.
В итоге должно получиться следующее:
Шаг 4. Чтобы не было двух "х".
Если пользователь введет "х" напротив нескольких строк, то функция ВПР будет брать только первое найденное значение. Чтобы не было такой многозначности, щелкните правой кнопкой мыши по ярлычку листа Данные и выберите Исходный текст (Source Code) . В появившееся окно редактора Visual Basic скопируйте следующий код:
Этот макрос не дает пользователю ввести больше одного "х" в первый столбец.
Читайте также: