Excel создание листов по шаблону - Word и Excel - помощь в работе с программами

Сегодня я покажу, как заполнить однотипные листы, по шаблону Excel на основе списка.

Переименуем в книге первый лист, это у нас будет список, допустим сотрудников.

Второй лист назовём шаблон, в нем будет находиться таблица, которую сотрудники будут заполнять либо вы будите заполнять для данных сотрудников из списка.

Вот такая будет табличка, потом покажу вам одну формулу в ячейке, как имя листа поместить в ячейку.

Вот список из трех сотрудников, три фамилии они у нас уникальные, отличаются друг от друга это важное условие для работы данного способа заполнения.

Сохраним наш документ как книга Excel с поддержкой макросов с расширением так xlsm, на ленте должен находиться в последнем пункте — пункт Разработчик, если его нет, то в файл, параметры нужно будет в настройках ленты добавить этот пункт, отметьте его галочкой.

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

Перейдём на вкладке Разработчик в Visual Basic либо нажмем Alt+F11, для того чтобы открылся редактор макросов.

Нажмем в меню insert – module, напишем модуль Sub ListTempl(), объявим переменную tmpName.

Получим данные нашего списка из листа список в переменную tmpName, объявив диапазон А1:А3. Но если нужно будет больше, то будем менять это значение. Пока в примере оставим так.

Напишем цикл for от 1 до 3, по сколько мы знаем конечное количество записей нашего списка и теперь сделаем копию листа шаблон, создадим новый лист копии листа с шаблоном, шаблон и присвоим имя каждому листу, значение то которое у нас находится в списке. Закончим цикл next i.

Теперь нажмем правой кнопкой на кнопке, назначить макрос, выберем макрос ListTempl, OK, сохранить.

Нажмем на кнопку, сформировались три листа с название по списку сотрудников, которые были в списке. И можно будет заполнять или печатать.

Sub ListTempl()
Dim tmpName
tmpName = Sheets("Шаблон").Range("a1:a3")

For i = 1 To 3
Sheets("Шаблон").Copy Before:=Sheets(i)
Sheets(i).Name = tmpName(i, 1)
Next i

End Sub

Вот такой простой код позволяет быстро создать и заполнить книгу листами по определенным данным.

Если нажать на кнопку повторно, то Excel выдаст ошибку, что подобные листы уже используется, надо учитывать этот момент, просто удалите дубли, в этом примере я не буду делать повторную проверку или добавление каких-то случайных значений к имени листов.

Давайте добавим теперь в шапку шаблона формулу, которая будет в ячейку присваивать имя листа, сейчас формула находится на листе с названием шаблон.

Эта формула позволяет получить из значение листа имя листа в ячейку, происходит разбор пути файла до листа.

=ПРОПНАЧ(ПСТР(ЯЧЕЙКА("имяфайла";A1);ПОИСК("]";ЯЧЕЙКА("имяфайла";A1))+1;99))

Давайте допишет для примера как это бывает в таблицах, «Список для », а далее будет фамилия поставляться из названия листа.

Нажмем на кнопку, и как видите, сформировались все листы, и в каждом листе, в этой ячейки у нас будет находиться имя сотрудника.

Ну либо того, что у вас будет находилась в списке, при условии, что эти значения уникальны.

Если же всё-таки вам нужно будет сделать два листа для одной уникальной записи, тут могу предложить следующее.

Изменим в коде диапазон на А4, так как у нас ещё один пункт добавился и в цикле исправим на четыре.

Sub ListTempl()
Dim tmpName
tmpName = Sheets("Шаблон").Range("a1:a4")

For i = 1 To 4
Sheets("Шаблон").Copy Before:=Sheets(i)
Sheets(i).Name = tmpName(i, 1)
Next i

End Sub

Удалим всё снова и нажмем на кнопку. Опять ошибка, как вариант предлагаю добавить пробел в конце названия дублируемого пункта списка.

В итоге у нас получилось два Ивановых, конечно лучше использовать уникальные значения, тогда этот пример будет достаточно хорошо вам подходить.

Смотрите видео: Листы в Excel из списка по шаблону

Понравилась статья? Поделитесь ею с друзьями и напишите отзыв в комментариях!

Источник

Константин

Пользователь

Сообщений: 61
Регистрация: 26.04.2014

Уважаемые специалисты!
Помогите, пожалуйста, с решением такой задачи.

В файле 2 листа:
— список клиентов;
— шаблон отчета.

Как только пользователь добавляет в список еще одного клиента — автоматически создается новый лист:
— Название клиента в списке клиентов становится ссылкой на соответствующий созданный лист.
— создаваемый лист = скопированный шаблон отчета.
— название создаваемого листа = название клиента.
— в создаваемый лист вставляются данные из списка клиентов.

Подскажите, как такое сделать?

Пример во вложении.

Sanja Пользователь Сообщений: 14838 Регистрация: 10.01.2013	Я бы сделал ТАК . Зачем хранить одинаковые бланки, когда их можно формировать динамически Изменено: Sanja — 20.06.2015 20:28:39 Согласие есть продукт при полном непротивлении сторон.

CAHO

Пользователь

Сообщений: 2183
Регистрация: 25.02.2013

20.06.2015 20:22:11

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

Код
Sheets("Шаблон отчета").Copy Before:=Sheets(1) Sheets(1).Name = "Клиент"

Изменено: CAHO — 20.06.2015 20:29:37

Мастерство программиста не в том, чтобы писать программы, работающие без ошибок.
А в том, чтобы писать программы, работающие при любом количестве ошибок.

Константин

Пользователь

Сообщений: 61
Регистрация: 26.04.2014

20.06.2015 20:30:56

Цитата
Sanja написал: Я бы сделал ТАК. Зачем хранить одинаковые бланки, когда их можно формировать динамически

Мне немного не подходит. Мне именно нужно создание листа при каждом добавлении в список клиентов.

Цитата
CAHO написал: Тут я так понимаю всё равно надо создавать какую-то кнопочку для создания листа.

Наверное, Вы правы. тогда добавляется еще одна задача:
В список клиента внес — и автоматически в строке созданного клиента должна появиться кнопочка «Сформировать отчет»

Юрий М

Модератор

Сообщений: 60570
Регистрация: 14.09.2012

Контакты см. в профиле

20.06.2015 20:34:22

Цитата
Константин написал: Как только пользователь добавляет в список еще одного клиента — автоматически создается новый лист:

Именно автоматически? Какой момент принять за создание новой записи — заполнены оба столбца? А если, как заметил САНО, при вводе данных произогшла ошибка и нужно подкорректировать запись немного — что тогда?

Sanja

Пользователь

Сообщений: 14838
Регистрация: 10.01.2013

20.06.2015 20:37:55

Цитата
Константин написал: создание листа при каждом добавлении

тогда неизбежны

Цитата
CAHO написал: И многие другие проблемки

нужно продумать тщательнее.
ИМХО. Все равно не пойму зачем хранить в книге заполненные КАРТОЧКИ, храните все что относится к клиенту в общей базе и при необходимости формируйте карточки. ИМХО

Изменено: Sanja — 20.06.2015 21:04:57

Согласие есть продукт при полном непротивлении сторон.

Константин Пользователь Сообщений: 61 Регистрация: 26.04.2014	Ок, понял. Спасибо за советы. Действительно, решил пересмотреть подход к формированию карточек.

Юрий М Модератор Сообщений: 60570 Регистрация: 14.09.2012 Контакты см. в профиле	Предлагаю по нажатию кнопки формировать карточку.

Юрий М Модератор Сообщений: 60570 Регистрация: 14.09.2012 Контакты см. в профиле	Если всё же решите заводить каждому клиенту свой лист, то см. файл. Но возможны ошибки из-за имён листов…

Константин

Пользователь

Сообщений: 61
Регистрация: 26.04.2014

Юрий!
как всегда Вам огромное спасибо!!!

Я так и хочу решить!
Есть один момент. Создали «Петров», далее «Бымц!», лист Петров готов.
Проходит время, создаются «Сидоров» и «Фролов», но «Бымц!» протестует, говорит, что лист с таким именем уже существует (имеет ввиду Петрова). И есссно, листы Сидоров и Фролов не создаются.

Как лучше:
— отдельный «Бымц!» на каждую строку (но тогда она должна появляться напротив строки, где появился хот один символ в колонках B и С)
— один общий «Бымц!» на всю таблицу, но идет проверка на дублирование имен_клиентов/листов
?

Юрий М Модератор Сообщений: 60570 Регистрация: 14.09.2012 Контакты см. в профиле	Не смог воспроизвести у себя подобную ошибку, когда создаётся лист с НОВОЙ фамилией. Уточните проблему. P.S. Но сам подход не очень хороший: при большой клиентской базе будет куча листов…

Константин Пользователь Сообщений: 61 Регистрация: 26.04.2014	Юрий, пардон, действительно я ошибся. Сначала поигрался, потом код посмотрел. все ок, спасибо. Но в целом я с Вами согласен, много листов выходит. Надо менять подход.

Юрий М Модератор Сообщений: 60570 Регистрация: 14.09.2012 Контакты см. в профиле	#13 21.06.2015 00:08:22 Вариант: добавлять записи в базу, а если нужна карточка — выводить по запросу (поиску) данные в лист «Шаблон».

Источник

Шаблон рабочего листа очень полезен для наших пользователей Excel, он может помочь нам сэкономить много времени, когда нам нужно создавать одно и то же форматирование рабочего листа снова и снова. После создания шаблона рабочего листа мы можем быстро многократно вставлять его в любые книги. В этой статье я расскажу о том, как создать и использовать шаблон листа в Excel.

Создайте шаблон листа и примените его в Excel

Создайте шаблон листа и примените его в Excel

Для создания шаблона рабочего листа выполните следующие действия:

1. Создайте новый файл Excel и удалите другие листы, но оставьте только один лист.

2. Отредактируйте и отформатируйте рабочий лист по своему усмотрению. Смотрите скриншот:

3. Затем нажмите Файл > Сохранить как в Excel2007 / 2010, а в Excel 2013 щелкните Файл > Сохранить как > Приложения. А потом в Сохранить As, введите имя рабочего листа шаблона в поле Имя файла поле, а затем щелкните раскрывающийся список из Сохранить как поле и выберите Шаблон Excel вариант, см. снимок экрана:

Функции: В Сохранить как диалоговое окно, вам не нужно изменять путь к файлу, новый файл будет сохранен в шаблон по умолчанию.

4, И нажмите Сохраните кнопку, чтобы сохранить этот лист шаблона.

5. После создания шаблона рабочего листа откройте файл Excel, щелкните правой кнопкой мыши вкладку рабочего листа и выберите Вставить, см. снимок экрана:

6. И в поп-оуt Вставить диалоговом окне выберите шаблон листа, который вы создали в Общие вкладку, см. снимок экрана:

7, Наконец, нажмите OK кнопку, и ваш шаблон рабочего листа будет быстро вставлен в книгу.

Примечание: Если вы хотите удалить шаблон рабочего листа, вам просто нужно выбрать лист шаблона в Вставить диалоговое окно и щелкните правой кнопкой мыши, затем выберите Удалить, см. снимок экрана:

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Комментарии (4)

Оценок пока нет. Оцените первым!

Источник

Очень часто бывает такое, что нужно сформировать документы по определенному шаблону, на основе каких-то данных, например, по каждому сотруднику или по каждому лицевому счету. И делать это вручную бывает достаточно долго, когда этих самых сотрудников или лицевых счетов много, поэтому сегодня мы рассмотрим примеры реализации таких задач в Excel с помощью макроса написанного на VBA Excel.

Немного поясню задачу, допустим, нам необходимо сформировать какие-то специфические документы по шаблону массово, т.е. в итоге их получится очень много, как я уже сказал выше, например, по каждому сотруднику. И это нужно сделать непосредственно в Excel, если было бы можно это сделать в Word, то мы бы это сделали через «Слияние», но нам нужно именно в Excel, поэтому для этой задачи мы будем писать макрос.

Мы с Вами уже выгружали данные по шаблону через клиент Access из базы MSSql 2008 в Word и Excel вот в этой статье — Выгрузка данных из Access в шаблон Word и Excel. Но сейчас допустим, у нас данные располагаются в базе, в клиенте которой нельзя или слишком трудоемко реализовать такую задачу, поэтому мы просто выгрузим необходимые данные в Excel и на основе таких данных по шаблону сформируем наши документы.

В нашем примере мы, конечно, будем использовать простой шаблон, только для того чтобы это было просто наглядно и понятно (только в качестве примера), у Вас в свою очередь шаблон будет, как мне кажется намного сложней.

Напомню, что на данном сайте тема VBA Excel уже затрагивалась, например, в материале – Запрет доступа к листу Excel с помощью пароля

И так приступим!

Реализовывать нашу задачу будем на примере «Электронной карточке сотрудника» (я это просто придумал:), хотя может такие и на самом деле есть), т.е. документ в котором хранится личные данные сотрудника вашего предприятия, в определенном виде, именно в Excel.

Примечание! Программировать будем в Excel 2010.

И для начала приведем исходные данные, т.е. сами данные и шаблон

Данные.

Лист, на котором расположены эти данные так и назовем «Данные»

Шаблон.

Лист, на котором расположен шаблон, тоже так и назовем «Шаблон»

Далее, нам необходимо присвоить имена полей для вставки, так более удобней к ним обращаться чем, например, по номеру ячейки.

Это делается очень просто, выделяете необходимую ячейку или диапазон, и жмете правой кнопкой мыши и выбираете «Присвоить имя», пишите имя ячейки и жмете «ОК»

Свои поля я назвал следующим образом:

ФИО – fio;
№ — number;
Должность – dolgn;
Адрес проживания – addres;
Тел. № сотрудника – phone;
Комментарий – comment.

Код макроса на VBA Excel

Для того чтобы написать код макроса, открывайте на ленте вкладку «Разработчик», далее макросы.

Примечание! По умолчанию данной вкладке в Excel 2010 может и не быть, чтобы ее отобразить нажмите правой кнопкой по ленте пункт меню «Настройка ленты»

затем, в правой области поставьте галочку напротив пункта «Разработчик»

После вкладка разработчик станет отображаться на ленте.

Далее, когда Вы откроете вкладку разработчик и нажмете кнопку «Макросы» у Вас отобразится окно создания макроса, Вы пишите название макросы и жмете «создать».

После у Вас откроется окно редактора кода, где собственно мы и будем писать свой код VBA. Ниже представлен код, я его как обычно подробно прокомментировал:

Sub Карточка()
'Книга
NewBook = ""
' Путь, где будут храниться наши карточки
' Т.е. в той папке, откуда запустился файл с макросом
Path = ThisWorkbook.Path
' Выбираем лист с данными
Sheets("Данные").Select
' Запускаем цикл, скажем на 100000 итераций
' Начиная со второй строки, не учитывая заголовок
For i = 2 To 100000
' Выйдем из него, когда фамилии закончатся, т.е. строки
If Cells(i, 1).Value = "" Then
        i = 100000
    Exit For
End If
' Имя файла карточки, назовем по фамилии
Name_file = Path & "" & Sheets("Данные").Cells(i, 1).Value & ".xls"
‘Выбираем лист с шаблоном
Sheets("Шаблон").Select
' Присваиваем значения нашим ячейкам, по именам которые мы задавали
    Range("fio").Value = Sheets("Данные").Cells(i, 1).Value & " " & _
        Sheets("Данные").Cells(i, 2).Value & " " & Sheets("Данные").Cells(i, 3).Value
    Range("number").Value = Sheets("Данные").Cells(i, 4).Value
    Range("addres").Value = Sheets("Данные").Cells(i, 5).Value
    Range("dolgn").Value = Sheets("Данные").Cells(i, 6).Value
    Range("phone").Value = Sheets("Данные").Cells(i, 7).Value
    Range("comment").Value = Sheets("Данные").Cells(i, 8).Value
    ' Копируем все
    Cells.Select
    Selection.Copy
    ' Создаем новую книгу или делаем ее активной 
    If NewBook = "" Then
        Workbooks.Add
        NewBook = ActiveWorkbook.Name
    Else
        Workbooks(NewBook).Activate
        Cells(1, 1).Select
    End If
    ' Вставляем данные в эту книгу
    Application.DisplayAlerts = False
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ' Сохраняем с нашим новым названием
    ActiveWorkbook.SaveAs Filename:= _
    Name_file, FileFormat:=xlExcel8, _
    Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
    CreateBackup:=False
    NewBook = ActiveWorkbook.Name
    Application.DisplayAlerts = True
    ' Снова активируем файл с макросом и выбираем лист
    Workbooks("Макрос.xls").Activate
    Sheets("Данные").Select
' Переходим к следующей строке
Next i
' Закроем книгу
Workbooks(NewBook).Close
' Выведем сообщение об окончании
MsgBox ("Выполнено!")
End Sub

Теперь осталось выполнить этот макрос, для этого откройте вкладку разработчик->макросы->выполнить наш макрос:

и после выполнения у Вас в той же папке появится вот такие файлы

Вот с таким содержимым:

Для удобства можете на листе с данными создать кнопку и задать ей событие выполнить наш только что созданный макрос, и после чего простым нажатием выполнять этот макрос. Вот и все! Удачи!

Источник

Шаблон формата Excel может быть как библиотекой наборов стилей форматирования, так и готовым документом с таблицами, данными, форматами и графиками. В шаблоны-документы достаточно только ввести свои данные, чтобы получить готовый, тематически стильно оформленный, презентабельный отчет. Например, «Семейный бюджет» – очень полезный встроенный шаблон в Excel.

Создание шаблона в Excel

Создадим свой новый шаблон, в котором следует использовать стили форматирования. Как форматировать с помощью стилей мы знаем из предыдущего урока: стили ячеек и средства управления ими. Чтобы сделать шаблон в Excel, выполним определенный ряд действий, которые послужат практическим примером для решения данной задачи:

Сначала нужно сделать шаблон таблицы Excel и график, как показано ниже на рисунке. И присвойте ей пользовательский стиль форматирования.

Сохраняем документ как шаблон: «Файл»-«Сохранить как». В окне сохранения можно указать любую папку, так как Excel потом автоматически перенесет этот файл в специальную директорию для хранения нестандартных шаблонов.
В поле тип файла следует указать «Шаблоны формата Microsoft Office Excel *.xltx». Заметьте, как только мы выбираем этот тип файла, автоматически открывается папка «Шаблоны» в окне сохранения документа.

В поле: «Имя файла» введите любое название шаблона. Например, «мои расходы». И жмите СОХРАНИТЬ.
После сохранения закройте документ.

Теперь чтобы использовать наш шаблон достаточно выбрать меню: «Файл»-«Создать»-«Мои шаблоны». Выберите свой шаблон «мои расходы».

Откройте и сохраните этот документ как обычный файл в формате *.xlsx. А теперь обратите внимание на то, что наш пользовательский стиль так же сохранен в данной книге.

Таким образом, у вас есть готовая форма для быстрого создания отчетов, в которой нужно только изменять данные и не нужно ничего форматировать. Это очень удобно и продуктивно. Используйте возможности шаблонов по максимуму!

Создание файла по шаблону Excel

Можно установить свой пользовательский шаблон, по которому будет оформлен и отформатирован документ при каждом запуске программы Excel. Для этого следует сохранить файл с расширением *.xlsx в папке XLSTART. Данная папка создается при установке программы Excel. Чтобы легко найти путь к ней следует перейти в режим VisualBasic (Alt+F11) и открыть окно: «View»-«Immediate». А там следует набрать код: ? application.StartupPath и нажать Enter. Ниже отобразиться путь:

C:Usersимя пользователяAppDataRoamingMicrosoftExcelXLSTART

Примечание. В разных версиях Windows путь к XLSTART может отличаться, поэтому лучше узнать его через выше описанные действия.

Теперь при каждой загрузке программы будет открыт файл который лежит в этой папке. Более того если вы сохраните там несколько документов, то все они будут открыты при каждой запуске программы.

Полезный совет! Если в папке XLSTART сохранить отформатированный шаблон с именем Лист.xltx. То каждый новый созданный лист будет иметь такой же формат как Лист.xltx. При том не зависимо, в какой способ создан новый лист, через меню или комбинацией горячих клавиш SHIFT+F11. Формат будет один и тот же. А чтобы отменить такую функцию нужно просто удалить этот шаблон из XLSTART.

Форматирование документов – это важный, но очень утомительный процесс. Для его автоматизации существует несколько полезных инструментов такие как: темы, стили и шаблоны. Каждый из них обладает своими преимуществами и недостатками. Например, самый простой способ автоматизации форматирования – это стили. Они облегчаю пользователю процесс форматирования, но могут быть сохранены только в рамках одной книги. Да созданные или измененные пользователем стили можно скопировать в другую книгу. Или создать отдельную, специальную книгу с целым набором свих пользовательских стилей, чтобы копировать их в другие документы, но это крайне неудобно. Для решения такой задачи лучше использовать шаблоны.

Создание темы документа для автоматического форматирования

Тема Excel – это собрание стилей, цветов, шрифтов и много разных графических свойств форматирования, которые легко можем менять в пару кликов мышкой для изменения стильного оформления документа. Темы стали доступны начиная с 2007-й версии и новее. Их можно встретить и в других продуктах входящих в пакет MS Office.

Для примера возьмем простой не форматированный документ и будем присваивать ему разные темы пока не выберем подходящую к данным. Заодно посмотрим, что произойдет выбрать другую тему Excel:

Создайте новую книгу и заполните ее данными (начиная со столбца H) так как показано на рисунке:

Выберите инструмент: «Разметка страницы»-«Темы».
В выпадающем списке наводите курсор мышки на разные темы и обратите внимание на то, как меняется стиль оформления данных документа.

Щелкните по подходящей теме, которая на Ваш взгляд наиболее подходит к данному документу.

Внимание! Темы присваиваются к целому документу. Поэтому нельзя для разных листов задавать разные темы форматирования.

Пользователь имеет возможность самостоятельно создавать и редактировать любую тему. Для этого доступны 3 инструмента, которые находятся рядом возле тем:

цвета;
шрифты;
эффекты.

Каждый из этих инструментов открывает целые предопределенные наборы для настройки темы. Можно создавать новые наборы шрифтов или наборы цветов, только нельзя создать новые эффекты. Приходится использовать только те, которые предложены программой по умолчанию.

Как только вы сохраните новый набор шрифтов и/или цветов под своим названием, тогда она будет доступна в разделе тем как для Excel, так и для других программ из пакета MS Office (Word, PowerPoint и др.).

Источник

4 / 4 / 2

Регистрация: 04.05.2013

Сообщений: 62

03.04.2015, 08:25. Показов 8829. Ответов 12

Доброго времени суток.
Прошу помочь с вопросом: Есть некоторая книга Ecxel из 2-х листов (а и b) с определенным форматированием. Необходимо в этой книге создать новый лист c по шаблону существующего листа b. В итоге должно быть в книге 3 листа a b c, причем b=c.

Модератор

3878 / 3200 / 482

Регистрация: 27.01.2014

Сообщений: 5,841

03.04.2015, 15:00

Andrey72, сделась это надо на VB.NET?

4 / 4 / 2

Регистрация: 04.05.2013

Сообщений: 62

03.04.2015, 18:59

[ТС]

да именно в vb.net

Yury Komar

Модератор

3878 / 3200 / 482

Регистрация: 27.01.2014

Сообщений: 5,841

03.04.2015, 19:53

Сообщение было отмечено Памирыч как решение

Решение

Andrey72, Как то делал на VBA это дело, вот код, под VB.NET сейчас времени нет пределывать, сам сделаешь?

Visual Basic

Public Sub CopySheetToNewSheet()
    ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Select 'Выделяем текущий лист для копирования
    Sheets(ThisWorkbook.Sheets.Count).Copy After:=Sheets(ThisWorkbook.Sheets.Count) 'Делает копию текущего листа
    Sheets(ThisWorkbook.Sheets.Count).Select 'Выделяем только что созданный лист
    Sheets(ThisWorkbook.Sheets.Count).Name = "Имя Нового Листа"
End Sub

Добавлено через 54 секунды
хотя из всего кода тебе нужна только эта строка

Visual Basic

1	Sheets(ThisWorkbook.Sheets.Count).Copy After:=Sheets(ThisWorkbook.Sheets.Count) 'Делает копию текущего листа

4 / 4 / 2

Регистрация: 04.05.2013

Сообщений: 62

03.04.2015, 19:59

[ТС]

Благодарю, буду пробовать.

Pavel55

971 / 353 / 135

Регистрация: 27.10.2006

Сообщений: 764

04.04.2015, 00:57

Приблизительно так

1) создаёте проект, на форму кидаете кнопку
2) Необходимо добавить ссылку на библиотеку Microsoft.Office.Interop.Excel, для этого идёте в меню Проект — Добавить ссылку… — слева выбрать Сборки — Расширения. В списке найти Microsoft.Office.Interop.Excel и поставить слева от неё галочку

далее вставляете этот код

VB.NET

Imports Excel = Microsoft.Office.Interop.Excel
 
Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim xlApp As New Excel.Application
        Dim xlWB As Excel.Workbook
        Dim xlShtB As Excel.Worksheet 'лист b
        Dim xlShtC As Excel.Worksheet 'лист c
 
        xlWB = xlApp.Workbooks.Open("G:Excel file.xlsx") 'путь к нашему Excel файлу
        'проверка на наличие листа "b2" в файле
        Try
            xlShtB = xlWB.Worksheets("b")
        Catch ex As Exception
            MessageBox.Show("В файле '" & xlWB.Name & "' отсутствует лист 'b'", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            xlWB.Close(SaveChanges:=False) 'закрываем файл не сохраняя
            xlApp.Quit() 'закрываем Excel
            Exit Sub
        End Try
 
        'проверка на наличие листа "с" в файле, если он есть, то удаляем его
        xlShtC = xlWB.Worksheets("c")
        If Not IsNothing(xlShtC) Then
            xlApp.DisplayAlerts = False
            xlShtC.Delete()
            xlApp.DisplayAlerts = True
        End If
 
        xlShtB.Copy(After:=xlWB.Sheets(xlWB.Sheets.Count)) 'копируем лист b в самый конец справа
        xlApp.ActiveSheet.name = "c" 'даём новому листу имя "с"
 
        xlWB.Close(SaveChanges:=True) 'сохраняем файл и закрываем его
        xlApp.Quit() 'закрываем Excel
 
        MessageBox.Show("Шаблон скопирован/создан!", "Конец", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End Sub
End Class

4 / 4 / 2

Регистрация: 04.05.2013

Сообщений: 62

14.07.2018, 20:23

[ТС]

Всем доброго времени! Вот и пришлось воскресить тему)
Проблема создания листа — решена успешно, но сейчас возникла новая «напасть», при создании нового листа — форматирование сохраняется, но вот при забивке в него данными — они вставляются по «умолчанию». А если писать в самом экселе — то все хорошо, вся проблема как раз при программной вставке значений.

Миниатюры

Andrey72

4 / 4 / 2

Регистрация: 04.05.2013

Сообщений: 62

17.07.2018, 23:59

[ТС]

Нашел проблему. В моем документе сначала забиваются данные в первый лист, а потом создается новый — методом копирования — стираются данные и забиваются новые. Дак вот, функция очистки

VB.NET

.Clear()

, оказывается очищает не только данные, но и форматирование.

Элд Хасп

Модератор

13628 / 9862 / 2636

Регистрация: 21.04.2018

Сообщений: 29,369

Записей в блоге: 2

19.07.2018, 11:05

Сообщение было отмечено Orlangur1991 как решение

Решение

Сообщение от Andrey72

VB.NET

.Clear()

, оказывается очищает не только данные, но и форматирование.

Очищает содержимое

VB.NET

1	.ClearContents

Модератор

3878 / 3200 / 482

Регистрация: 27.01.2014

Сообщений: 5,841

24.07.2018, 20:59

Andrey72, а зачем стирать что-то? после вставки значения в ячейку, сразу же и примените форматировани (выравнивание) текста в этой ячейке… и все

4 / 4 / 2

Регистрация: 04.05.2013

Сообщений: 62

24.07.2018, 22:40

[ТС]

Сообщение от Yury Komar

Andrey72, а зачем стирать что-то? после вставки значения в ячейку, сразу же и примените форматировани (выравнивание) текста в этой ячейке… и все

Yury Komar, у меня есть шаблон документа в ресурсах и в нем настроено все форматирование, в процессе работы необходимо добавлять новые листы, но уже после вставок значений в предыдущие, поэтому при копировании нового листа он очищается, а каждый раз форматировать ячейки — не вариант, так как весь лист должен быть отформатирован определенным образом.

Модератор

3878 / 3200 / 482

Регистрация: 27.01.2014

Сообщений: 5,841

25.07.2018, 05:53

Andrey72, а как вы очищаете??? вам выше Элд Хасп уже дал наводку. Вы очищаете все, включая форматирование.
Используйте .ClearContents вместо .Clear

4 / 4 / 2

Регистрация: 04.05.2013

Сообщений: 62

25.07.2018, 08:13

[ТС]

Yury Komar, да в этом и была проблема, не знал что .Clear может удалять и форматирование. Применил подсказку Элд Хасп и все стало как и должно быть, за что ему +!

Источник

Содержание

- 0.0.1 Создание шаблона Excel

1 Создание шаблона в Excel
2 Создание файла по шаблону Excel
3 Создание темы документа для автоматического форматирования
4 Что такое шаблоны Эксель
5 Как загрузить шаблон в рабочую книгу
6 Как изменить шаблон Excel
7 Как сделать собственный шаблон Эксель

Создание шаблона в Excel

Создание шаблонов Excel

В случае если ни один из готовых шаблонов вам не подошел, вы можете создать свой собственный шаблон, в дальнейшем работая с ним, как и с любым шаблоном Excel.

Создание шаблона Excel

Для того чтобы создать шаблон, требуется в меню Файл выбрать команду Создать -> Новая книга и щелкнуть на кнопке Создать. В рабочем окне программы появится новая книга под названием Книга1 или Книга2, Книга3 и т.п. Затем требуется создать таблицу, которую вы в дальнейшем будете использовать в качестве шаблона.

Очевидно, что таблица будет содержать заголовки и формулы и иметь оформление, соответствующее вашим требованиям.

Как только таблица подготовлена, ее необходимо сохранить в формате шаблона Excel. Для этого в меню Файл выберите команду Сохранить, а также можно воспользоваться панелью быстрого доступа. Следует обратить внимание, если шаблон создается на основе документа, у которого уже есть имя, то в меню Файл надо выбрать команду Сохранить как.

В окне Сохранение документа в списке Тип файла выбрать формат сохранения таблицы — Шаблон Excel. Следует обратить внимание, что автоматически будет выбрана папка C:UsersWжяAppDataRoamingMicrosoftШаблоны, в которую по умолчанию сохраняются шаблоны пользователя. Имя пользователя соответствует имени зарегистрированного пользователя, с которым в этом сеансе вы вошли в операционную систему. После этого в поле Имя файла надо ввести имя шаблона и сделать щелчок на кнопке Сохранить.

Шаблон сохранен, теперь можно закрыть документ, на основе которого мы его создавали. Следует обратить внимание, если шаблон требуется изменить, то его можно редактировать, как и обычную книгу.

Опубликовано: 09.12.2015

Сначала нужно сделать шаблон таблицы Excel и график, как показано ниже на рисунке. И присвойте ей пользовательский стиль форматирования.
Сохраняем документ как шаблон: «Файл»-«Сохранить как». В окне сохранения можно указать любую папку, так как Excel потом автоматически перенесет этот файл в специальную директорию для хранения нестандартных шаблонов.
В поле тип файла следует указать «Шаблоны формата Microsoft Office Excel *.xltx». Заметьте, как только мы выбираем этот тип файла, автоматически открывается папка «Шаблоны» в окне сохранения документа.
В поле: «Имя файла» введите любое название шаблона. Например, «мои расходы». И жмите СОХРАНИТЬ.
После сохранения закройте документ.

Создание файла по шаблону Excel

C:Usersимя пользователяAppDataRoamingMicrosoftExcelXLSTART

Создание темы документа для автоматического форматирования

Создайте новую книгу и заполните ее данными (начиная со столбца H) так как показано на рисунке:
Выберите инструмент: «Разметка страницы»-«Темы».
В выпадающем списке наводите курсор мышки на разные темы и обратите внимание на то, как меняется стиль оформления данных документа.
Щелкните по подходящей теме, которая на Ваш взгляд наиболее подходит к данному документу.

цвета;
шрифты;
эффекты.

Дата: 9 декабря 2015 Категория: Excel Поделиться, добавить в закладки или статью

Здравствйте, уважаемые читатели. У многих из вас есть регулярные отчеты, бланки, документы, которые приходится часто заполнять. Кто-то постоянно делает накладные и документы по одной и той же форме, а кто-то большие корпоративные отчеты. В этом посте я расскажу, как создать шаблон часто используемого документа Эксель и больше никогда не делать одну и ту же работу по несколько раз. Вы будете открывать шаблон, заполнять его данными и получать результат.

Почему я считаю это удобным? Например, многие просто открывают последний вариант документа, вносят туда новые данные и сохраняют под новым именем. Конечно, это привычно для всех. Но что если вы случайно сохраните новый документ под старым именем? Исходный документ будет потерян. Так зачем рисковать результатами напряжённой работы, проделанной ранее?

А вот при использовании шаблона, никакие данные не теряются, ведь вы работаете в новой рабочей книге, которая уже при создании выглядит так, как указано в шаблоне. А если форма документа изменяется – вы всегда можете поправить шаблон под новые требования. Удобно, неправда ли? Мне удобно и Вам тоже будет!

Что такое шаблоны Эксель

Шаблоны в Microsoft Excel – это особый вид рабочих листов и книг, для которых заданы стили и оформление. Открывая такой объект, вы можете сразу заполнять его данными, уделяя меньше внимания форматированию. Таким образом, вы поддерживаете единый стиль своих документов и экономите огромное количество времени. Применение шаблонов позволяет концентрироваться на качественной составляющей расчетов, без постоянного однотипного оформления.

Чтобы было понятнее, представьте какой-нибудь бланк. Например, отчета о полученной прибыли. Сам по себе — это всего лишь пустой лист с разметкой. Он ничего не значит и ни на что не влияет. Это и есть шаблон в Эксель. Но как только Вы в очередной раз внесли в него информацию — это уже документ. Бланк сам по себе практически никогда не изменяется, а данные в нем всегда разные. Так зачем каждый раз рисовать бланк и заполнять его данными? Думаю, такое сравнение Вам понятно.

Нужно понимать, шаблон – это не рабочая книга в том понятии, к которому вы привыкли. Это только информация о разметке листа и исходных данных. Обратите внимание:

Когда мы загружаем шаблон, программа создаёт новую рабочую книгу с форматированием, как в шаблоне
А когда мы открываем шаблон – можно менять его оформление и содержание для последующей загрузки в рабочую книгу и заполнения данными.

Как загрузить шаблон в рабочую книгу

Когда вы создаёте новую книгу Excel, программа отображает окно Создать, в котором предлагает выбрать шаблон, либо открыть пустую книгу.

Стандартные шаблоны Эксель

Здесь вы можете найти один из шаблонов по теме и назначению, программа отображает миниатюру листа для большей наглядности. Для загрузки шаблонов, Эксель использует интернет – позаботьтесь о наличии подключения.

В строке поиска вы можете ввести назначение шаблона, а программа предложит шаблоны из своей огромной базы данных. Кликайте на том варианте, который вам подходит, чтобы загрузить его из базы данных в рабочую книгу. На рисунке ниже я искал шаблон календаря. Смотрите, как много разных шаблонов найдено.

Шаблоны по запросу «Календарь»

Всё, рабочая книга на базе шаблона создана, можете работать!

Как изменить шаблон Excel

Скорее всего, вам захочется что-то изменить в загруженном шаблоне, чтобы он подходил вам на 100%.

Для начала, нужно открыть файл шаблона (не создать книгу на основе шаблона, а именно открыть шаблон). Для этого откройте файл шаблона любым из известных способов. Установите тип открываемого файла «Шаблоны» (будут показаны файлы с расширением .xltx, .xltm или .xlt). В проводнике найдите место, где хранятся шаблоны, обычно это: C:UsersAppDataRoamingMicrosoftШаблоны. Выберите в списке нужный шаблон и откройте его.

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

Как сделать собственный шаблон Эксель

Вы можете сделать и собственные, пользовательские шаблоны. Для этого, создайте новую книгу Эксель и отформатируйте её как нужно, внесите информацию. Можете даже задать формулы, и создать диаграммы. Прописывайте все данные и расчёты, которые будут повторяться в каждом экземпляре, созданном по шаблону.

Когда вёрстка шаблона окончена, сохраните его на жёстком диске компьютера, присвоив информативное имя вместо предложенного программой. При сохранении выберите один из форматов файла

Шаблон Excel (расширение .xltx)
Шаблон Excel с поддержкой макросов (расширение .xltm)
Шаблон Excel 97-2003 (старый формат, расширение .xlt)

Теперь можно использовать свой шаблон. Выполните Файл – Создать – Личные для выбора и загрузки одного шаблона.

Открытие пользовательского шаблона

Созданный шаблон можно использовать по умолчанию для открытия Эксель. Если вы хотите заменить стандартный шаблон Excel, который применяется к новым книгам – создайте свой и сохраните под именем «Книга.xlts» в папке XLStart. Чаще всего, эта папка располагается по адресу: C:UsersAppDataRoamingMicrosoftExcelXLStart.

Теперь программа будет создавать новые рабочие книги по вашему шаблону. Чтобы вернуться к стандартному оформлению новых документов – удалите файл Книга.xlts из папки XLStart.

Вот так просто можно создать и использовать шаблоны в Excel. Мы еще раз убеждаемся, что Microsoft Office – это легко! Чем больше работы делает за Вас программа, тем Вам легче. И было бы странным не пользоваться теми удобствами, которые предоставляют нам разработчики из Майкрософт. Что ж, на этом буду заканчивать статью о шаблонах. Если у Вас что-то не получилось — задавайте вопросы в комментариях.

А что дальше? Дальше учимся распечатывать документы! Почему нельзя пропустить посты о печати? Потому что напечатанный документ — это готовый документ, то есть — это апогей и конечный результат Ваших стараний. И если он выглядит неидеально — какие же мы тогда профессионалы?

Поделиться, добавить в закладки или статью

Таблицы в Excel представляют собой ряд строк и столбцов со связанными данными, которыми вы управляете независимо друг от друга.

Работая в Excel с таблицами, вы сможете создавать отчеты, делать расчеты, строить графики и диаграммы, сортировать и фильтровать информацию.

Если ваша работа связана с обработкой данных, то навыки работы с таблицами в Эксель помогут вам сильно сэкономить время и повысить эффективность.

Как работать в Excel с таблицами. Пошаговая инструкция

Прежде чем работать с таблицами в Эксель, последуйте рекомендациям по организации данных:

Данные должны быть организованы в строках и столбцах, причем каждая строка должна содержать информацию об одной записи, например о заказе;

Первая строка таблицы должна содержать короткие, уникальные заголовки;

Каждый столбец должен содержать один тип данных, таких как числа, валюта или текст;

Каждая строка должна содержать данные для одной записи, например, заказа. Если применимо, укажите уникальный идентификатор для каждой строки, например номер заказа;

В таблице не должно быть пустых строк и абсолютно пустых столбцов.

1. Выделите область ячеек для создания таблицы

Выделите область ячеек, на месте которых вы хотите создать таблицу. Ячейки могут быть как пустыми, так и с информацией.

2. Нажмите кнопку “Таблица” на панели быстрого доступа

На вкладке “Вставка” нажмите кнопку “Таблица”.

3. Выберите диапазон ячеек

В всплывающем вы можете скорректировать расположение данных, а также настроить отображение заголовков. Когда все готово, нажмите “ОК”.

4. Таблица готова. Заполняйте данными!

Поздравляю, ваша таблица готова к заполнению! Об основных возможностях в работе с умными таблицами вы узнаете ниже.

Форматирование таблицы в Excel

Для настройки формата таблицы в Экселе доступны предварительно настроенные стили. Все они находятся на вкладке “Конструктор” в разделе “Стили таблиц”:

Если 7-ми стилей вам мало для выбора, тогда, нажав на кнопку, в правом нижнем углу стилей таблиц, раскроются все доступные стили. В дополнении к предустановленным системой стилям, вы можете настроить свой формат.

Помимо цветовой гаммы, в меню “Конструктора” таблиц можно настроить:

Отображение строки заголовков – включает и отключает заголовки в таблице;
Строку итогов – включает и отключает строку с суммой значений в колонках;
Чередующиеся строки – подсвечивает цветом чередующиеся строки;
Первый столбец – выделяет “жирным” текст в первом столбце с данными;
Последний столбец – выделяет “жирным” текст в последнем столбце;
Чередующиеся столбцы – подсвечивает цветом чередующиеся столбцы;
Кнопка фильтра – добавляет и убирает кнопки фильтра в заголовках столбцов.

Как добавить строку или столбец в таблице Excel

Даже внутри уже созданной таблицы вы можете добавлять строки или столбцы. Для этого кликните на любой ячейке правой клавишей мыши для вызова всплывающего окна:

Выберите пункт “Вставить” и кликните левой клавишей мыши по “Столбцы таблицы слева” если хотите добавить столбец, или “Строки таблицы выше”, если хотите вставить строку.

Если вы хотите удалить строку или столбец в таблице, то спуститесь по списку в сплывающем окне до пункта “Удалить” и выберите “Столбцы таблицы”, если хотите удалить столбец или “Строки таблицы”, если хотите удалить строку.

Как отсортировать таблицу в Excel

Для сортировки информации при работе с таблицей, нажмите справа от заголовка колонки “стрелочку”, после чего появится всплывающее окно:

В окне выберите по какому принципу отсортировать данные: “по возрастанию”, “по убыванию”, “по цвету”, “числовым фильтрам”.

Как отфильтровать данные в таблице Excel

Для фильтрации информации в таблице нажмите справа от заголовка колонки “стрелочку”, после чего появится всплывающее окно:

“Текстовый фильтр” отображается когда среди данных колонки есть текстовые значения;
“Фильтр по цвету” также как и текстовый, доступен когда в таблице есть ячейки, окрашенные в отличающийся от стандартного оформления цвета;
“Числовой фильтр” позволяет отобрать данные по параметрам: “Равно…”, “Не равно…”, “Больше…”, “Больше или равно…”, “Меньше…”, “Меньше или равно…”, “Между…”, “Первые 10…”, “Выше среднего”, “Ниже среднего”, а также настроить собственный фильтр.
В всплывающем окне, под “Поиском” отображаются все данные, по которым можно произвести фильтрацию, а также одним нажатием выделить все значения или выбрать только пустые ячейки.

Если вы хотите отменить все созданные настройки фильтрации, снова откройте всплывающее окно над нужной колонкой и нажмите “Удалить фильтр из столбца”. После этого таблица вернется в исходный вид.

Как посчитать сумму в таблице Excel

Для того чтобы посчитать сумму колонки в конце таблицы, нажмите правой клавишей мыши на любой ячейке и вызовите всплывающее окно:

В списке окна выберите пункт “Таблица” => “Строка итогов”:

Внизу таблица появится промежуточный итог. Нажмите левой клавишей мыши на ячейке с суммой.

В выпадающем меню выберите принцип промежуточного итога: это может быть сумма значений колонки, “среднее”, “количество”, “количество чисел”, “максимум”, “минимум” и т.д.

Как в Excel закрепить шапку таблицы

Таблицы, с которыми приходится работать, зачастую крупные и содержат в себе десятки строк. Прокручивая таблицу “вниз” сложно ориентироваться в данных, если не видно заголовков столбцов. В Эксель есть возможность закрепить шапку в таблице таким образом, что при прокрутке данных вам будут видны заголовки колонок.

Для того чтобы закрепить заголовки сделайте следующее:

Перейдите на вкладку “Вид” в панели инструментов и выберите пункт “Закрепить области”:

Выберите пункт “Закрепить верхнюю строку”:

Теперь, прокручивая таблицу, вы не потеряете заголовки и сможете легко сориентироваться где какие данные находятся:

Как перевернуть таблицу в Excel

Представим, что у нас есть готовая таблица с данными продаж по менеджерам:

На таблице сверху в строках указаны фамилии продавцов, в колонках месяцы. Для того чтобы перевернуть таблицу и разместить месяцы в строках, а фамилии продавцов нужно:

Выделить таблицу целиком (зажав левую клавишу мыши выделить все ячейки таблицы) и скопировать данные (CTRL+C):

Переместить курсор мыши на свободную ячейку и нажать правую клавишу мыши. В открывшемся меню выбрать “Специальная вставка” и нажать на этом пункте левой клавишей мыши:

В открывшемся окне в разделе “Вставить” выбрать “значения” и поставить галочку в пункте “транспонировать”:

Готово! Месяцы теперь размещены по строкам, а фамилии продавцов по колонкам. Все что остается сделать – это преобразовать полученные данные в таблицу.

В этой статье вы ознакомились с принципами работы в Excel с таблицами, а также основными подходами в их создании. Пишите свои вопросы в комментарии!

Источник

Смотрите видео: Листы в Excel из списка по шаблону

Понравилась статья? Поделитесь ею с друзьями и напишите отзыв в комментариях!

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Код макроса на VBA Excel

Создание шаблона в Excel

Создание файла по шаблону Excel

Создание темы документа для автоматического форматирования

Решение

Решение

Создание шаблона Excel

Создание файла по шаблону Excel

Создание темы документа для автоматического форматирования

Что такое шаблоны Эксель

Как загрузить шаблон в рабочую книгу

Как изменить шаблон Excel

Как сделать собственный шаблон Эксель

А вот еще интересные статьи: