Как сделать листинг программы в эксель
Добавил пользователь Евгений Кузнецов Обновлено: 04.10.2024
Выполнил студент ТМЦДО гр.: з-472-27б специальности 061000 Маркелова А.А.
Томский государственный университет систем управления и радиоэлектроники (ТУСУР)
Кафедра автоматизации обработки информации.
Программа должна удовлетворять следующим требованиям:
Приложение выполнить с использованием языка программирования VBA для MS Excel.
При открытии книги Excel, содержащей проект, обеспечить один рабочий лист, на котором оформить рекламную заставку проекта и поместить элемент запуска проекта на исполнение.
Проект должен обеспечивать следующие режимы работы:
ввод исходных данных и формирование базы;
корректировка данных (исправление, добавление, удаление);
работа с данными (поиск, сортировка, просмотр);
общее количество абонентов телефонной сети;
количество телефонов на указанной улице;
количество телефонов в указанном доме.
Для каждого режима работы определить кнопку запуска и пользовательскую форму.
На экране разместить одну панель инструментов, обеспечивающую работу проекта. Остальные панели скрыть.
Данные проекта сохранять в файле.
При вводе числовых данных обеспечить обработку ошибок некорректного ввода.
Введение
В дополнение к огромным возможностям MS Excel, входящего в состав Microsoft Office квалифицированному пользователю доступен полноценный язык программирования Visual Basic for Applications (VBA), позволяющий оживить страницы электронных таблиц, превратив их в сложное Windows – приложение, осуществляющее многогранную обработку данных под управлением оператора. При этом, большинство часто используемых операций программируется заранее, что упрощает работу конечного пользователя и значительно снижает вероятность возникновения ошибки как на этапе ввода исходных данных, так и при их последующей обработке.
В настоящей работе используются наиболее часто употребляемые элементы проектирования приложений для MS Excel. В том числе: процедуры (подпрограммы и функции), модули и формы. В коде VBA применялись различные типы данных (включая определенные пользователям описания типов), многоуровневые условные операторы и операторы циклов. Для реализации хранения информации во внешнем файле использованы встроенные функции работы с файлами (последовательный доступ).
Диалог с пользователем реализован при помощи функций ввода/вывода информации InputBox и MsgBox, а также ряда специально созданных форм. В указанных формах применены элементы управления типа: надпись, поле, рамка, переключатель, кнопка.
Поскольку программирование на VBA построено на основе событийной модели, то и в данной работе обработка событий является основой для выполнения тех или иных действий, направленных на решение конкретных задач.
MS Excel имеет богатую объектную модель. При решении поставленной задачи использовались объекты следующих типов: Application, Workbook, Worksheet, Range, CommandBar, а также коллекции Workbooks и Worksheets.
Работа выполнена в среде Microsoft Excel 2002.
Структура программного комплекса
Состав программного комплекса
"Телефонный справочник.xls" – основной файл, содержащий таблицу просмотра базы данных и инструменты, необходимые для работы с ней.
"phones.db" – вспомогательный текстовый файл, используемый для хранения базы данных.
Работа программы осуществляется под управлением Microsoft Excel, входящего в состав пакета Microsoft Office, поэтому для ее использования необходимо наличие указанного пакета на клиентском компьютере.
Описываемое Excel-приложение в своем составе содержит:
Worksheet Лист1 (Старт) + программный код VBA
Worksheet Лист2 (База данных) + программный код VBA
Пять форм + программный код VBA:
addRowForm – для режима добавления новой записи
delRowForm – для режима удаления записи
editRowForm – для режима редактирования записи
reportForm – для режима формирования статистики
sortForm – для режима сортировки базы данных
Модуль Module1, содержащий описание типа данных Record для одной записи об абоненте, а также функции работы (чтения/записи) с такими данными и внешним файлом.
Панель инструментов “Phones”, обеспечивающую работу программы и состоящую из девяти кнопок:
чтение базы данных;
запись базы данных;
выход из программы.
Руководство пользователя
Для запуска программы необходимо в MS Excel открыть книгу:
В результате, рекламная страница исчезнет, а вместо нее появится основной лист, содержащий шапку базы данных и панель инструментов для работы.
Чтение / запись базы данных
Режимы корректировки данных
В целях безопасности, листы книги защищены от корректировки обычными способами. Поэтому для добавления, изменения и удаления информации предусмотрены соответствующие режимы работы, инициируемые второй группой кнопок панели инструментов.
При нажатии на любую из них, будет предложено диалоговое окно, в котором в соответствующие поля нужно занести новую (откорректировать существующую) информацию и нажать кнопку подтверждения операции.
Если перед выбором режима удаления записи были отмечены несколько строк базы данных, то вместо отображения формы с удаляемой информацией будет просто предложено подтвердить удаление отмеченных записей.
Внимание. В режиме добавления/корректировки записи обязательны для заполнения поля: Фамилия, Имя, Отчество, Улица, Дом, Телефон. Кроме того, в поле Телефон допускается ввод только числовых данных (не более 10 цифр).
В программе предусмотрено три режима сортировки данных:
По абоненту (фамилия + имя + отчество);
По адресу (улица + дом + квартира);
Для выполнения сортировки достаточно нажать соответствующую кнопку на панели инструментов и выбрать один из трех предложенных режимов.
Согласно заданию программа позволяет рассчитать следующую статистику:
Общее количество абонентов телефонной сети;
Количество телефонов на указанной улице;
Количество телефонов в указанном доме.
Завершение работы с программой
Внимание. Не забывайте сохранять информацию во внешнем файле, иначе последние корректировки могут быть утеряны.
Заключение
В ходе выполнения работы были закреплены знания по работе в MS Excel и основам программирования на VBA, а также приобретены практические навыки создания завершенных программных приложений для MS Excel.
Разумеется, выполненный проект не является завершенным в полной мере. В качестве направлений для развития проекта можно упомянуть, например, более конкретизированный механизм поиска информации или реализация оптимальных методов сортировки (что может быть более эффективным на больших объемах информации).
Список литературы
Потахова И.В. Компьютерная подготовка. Офисное программирование: Учебное пособие. – Томск: Томский межвузовский центр дистанционного образования, 2004. – 181с.
Справочное руководство по MS Excel и Visual Basic for Applications: Microsoft Corp., 2001.
Демидова Л.А., Пылькин А.Н. Программирование в среде Visual Basic for Applications: Практикум – М.: Горячая линия – Телеком, 2004. – 175с.
Будем считать, что наша условная организация занимается поставками покупателям дорогостоящей строительной техники. Кроме непосредственно поставок, еще один из участков деятельности связан с ее ремонтом. Разрабатываемая далее книга Microsoft Excel будет включать несколько листов данных и несколько листов управления. Исходная ситуация такова: мы располагаем рядом клиентов (заказчиков), которые обращаются к нам по поводу ремонта проданной им техники (ремонт в течение гарантийного срока является вполне нормальным делом).
Технически сам ремонт, как правило, связан с заменой большого количества деталей. С учетом этого, очевидно, что существуют определенные трудности работы персонала, связанные с обеспечением хранения и извлечения необходимой информации по договорам с клиентами, а также с формированием отчетов по клиентам и договорам. В рабочей книге Microsoft Excel мы разработаем единую информационную базу, которая позволит существенно облегчить труд сотрудников офиса. Это облегчение работы будет достигнуто за счет размещения в книге элементов управления и последующего их программирования.
Итак, начнем работу с создания новой рабочей книги Microsoft Excel. Один из ее листов показан на рис. 3.1. В нем размещается справочная информация по нашим заказчикам (клиентам). Каждая строка на листе Клиенты представляет запись об одной из наших организаций-партнеров. Обратим внимание на то, что в дальнейшем в программных процедурах обращение к данному листу будет производиться по имени, поэтому следует присвоить ему имя Клиенты. Столбец Код предназначен для присвоения каждой фирме уникального кода (для того, чтобы однозначно идентифицировать каждую фирму). В целом информация, представленная в строках листа (см. рис. 3.1), достаточно стандартная: название, адрес, телефон, факс и ряд финансовых реквизитов.
Рис. 3.1. Справочная информация о клиентах
В верхней части листа располагается кнопка для ввода очередной записи о новой фирме. Конечно, пользователь, при появлении у нашей организации очередного партнера, может непосредственно внести информацию о нем в очередную свободную строку. Однако для удобства, с одной стороны, и контроля возможных технических ошибок, с другой, мы разработаем более удобную технологию.
К кнопке ввода нового клиента мы еще вернемся, а пока разберем следующий шаг, связанный с созданием формы ввода, которая приведена на рис. 3.2. От пользователя требуется внести данные в необходимые поля и щелкнуть на кнопке Внести для фиксации введенной информации на листе Клиенты. Это приведет к тому, что в очередную свободную строку листа будут внесены сведения по новой фирме. При этом процедура обработки щелчка на кнопке Внести предварительно проверит, встречался ли уже такой код у фирм. Если да, то ввод будет отменен. Также ввод будет отменен, если пользователь оставит поле пустым. Кроме того, процедура посмотрит: не является ли данная запись дублирующей (если уже имеется строка, включающая данное название фирмы, а также указанный адрес). И в этом случае ввод информации на лист Клиенты будет отменен.
Рис. 3.2. Форма ввода информации о клиентах
Таким образом, наша ближайшая цель — создать форму ввода (рис. 3.2) и расположить на ней необходимые элементы управления. С формами мы еще не сталкивались, поэтому рассмотрим процесс их создания более подробно. Для создания пользовательской формы необходимо перейти в окно редактора Visual Basic. Здесь требуется воспользоваться разделом UserForm в меню Insert (рис. 3.3). В результате на экране появится новая форма, которая фактически представляет собой контейнер для размещения на ней необходимых элементов управления.
Рис. 3.3. Пользовательская форма в среде Microsoft Visual Basic
У формы, как и у любого элемента управления, есть свойства. Для того чтобы открыть окно свойств, следует воспользоваться разделом Properties Window из меню View. Изменим значение Name на Client, а также значение свойства Caption — Форма для ввода нового клиента. В результате в заголовке формы будет отражен новый текст. Что касается свойства Name, то оно нам понадобится при работе с формой (при активизации формы).
Далее расположим на форме необходимые элементы управления. Для этого сначала необходимо отобразить на экране панель инструментов (View ► Toolbox). В соответствии с рис. 3.2 на форме нам следует разместить 7 элементов управления типа Label (надпись) и 7 элементов TextBox (текстовое окно). При этом значения свойства Name для надписей принципиального значения не имеют, так как программно мы обращаться к ним не будем. Для этих элементов выберите рассматриваемые значения произвольно (главное, чтобы не было повторяющихся имен — иначе среда Microsoft Visual Basic обратит на это внимание). Аналогичное свойство для текстовых окоп лам потребуется в программных процедурах, поэтому в табл. 3 приведены значения свойства Name текстовых окон, которые следует установить.
Таблица 3.1. Значения свойства Name текстовых окон
| Подпись | Name |
|---|---|
| Код | Cod |
| Название фирмы | Firma |
| Адрес | Adress |
| Телефон | Tel |
| Факс | Fax |
| ИНН | Inn |
| КПП | Kpp |
От пользователя требуется внести информацию в текстовые окна, расположенные на форме, после чего щелкнуть на кнопке Внести. Это приводит к выполнению процедуры, которая должна предварительно проверить корректность вводимых данных. Если на листе запись с указанным кодом фирмы уже существует, то процедура обратит на это внимание. Также процедура обратит внимание, если на листе уже присутствует фирма с аналогичным названием и с тем же указанным адресом. Текст данной процедуры приведен в листинге 3.1.
' Листинг 3.1. Обработка щелчка на кнопке Внести на форме Private Sub CommandButton1_Click() If Cod.Text = "" Then MsgBox ("Поле КОД необходимо заполнить") Exit Sub End If Nom = 0 While Worksheets("Клиенты").Cells(Nom + 2, 2).Value <> "" Nom = Nom + 1 Wend For i = 1 To Nom If CStr(Worksheets("Клиенты").Cells(i + 1, 2).Value = _ CStr(Firma.Text) And _ CStr(Worksheets("Клиенты").Cells(i + 1, 3).Value) = _ CStr(Adress.Text)) Then MsgBox ("Такой код фирмы уже встречался") Exit Sub End If Next Worksheets("Клиенты").Cells(i + 1, 1).Value = Cod.Text Worksheets("Клиенты").Cells(i + 1, 2).Value = Firma.Text Worksheets("Клиенты").Cells(i + 1, 3).Value = Adress.Text Worksheets("Клиенты").Cells(i + 1, 4).Value = Tel.Text Worksheets("Клиенты").Cells(i + 1, 5).Value = Fax.Text Worksheets("Клиенты").Cells(i + 1, 6).Value = Inn.Text Worksheets("Клиенты").Cells(i + 1, 7).Value = Kpp.Text MsgBox ("Информация внесена") Client.Hide End Sub
Новый момент связал с предпоследней строкой текста в листинге 3.1. Здесь для формы Client применяется метод Hide, который приводит к закрытию этой формы. Разумеется, ее необходимо сначала отобразить, и об этом мы еще не упоминали. В листинге 3.2 приведена процедура, которая выполняется по щелчку на кнопке Ввести нового клиента (см. рис. 3.1). Здесь для отображения на экране формы Client используется метод Show.
' Листинг 3.2. Обработка щелчка на кнопке Ввести нового клиента Private Sub CommandButton1_Click() Client.Show End Sub
На рис. 3.4 приведен результат заполнения формы данными об очередной фирме. Теперь щелчком на кнопке Внести это данные переносятся на текущий рабочий лист (в очередную свободную строку).
Рис. 3.4. Внесение информации о новом клиенте
Перейдем к разработке еще двух листов. Один из них под названием Номенклатура показан на рис. 3.5. В каждой строке листа располагается название конкретной запасной части. Значение в столбце Номер запчасти позволяет однозначно ее идентифицировать.
Рис. 3.5. Информация о номенклатуре
Лист Номенклатура является справочным и не содержит элементов управления. Технически пользователь просто вручную заполняет данный прайс-лист. Еще один лист, который также не содержит элементов управления, показан на рис. 3.6. Здесь содержится информация о заказах — названиях и их составе. Столбец А предназначен для уникального кода каждого заказа. Далее располагаются поля для отображения даты заказа и кода фирмы (по коду фирмы легко определить необходимые реквизиты организации).
Рис. 3.6. Лист для хранения информации о заказах
Щелчком на определенной запчасти она включается в заказ, который формируется с 11-й строки на данном листе. Столбцы Номер запчасти, Наименование и Цена заполняются исходя из имеющейся информации на листе Номенклатура. Пользователю следует лишь внести количество единиц указанной детали в перечне запасных частей. Для этого предназначено текстовое окно (Name — Col) с надписью выше — Количество. После этого осталось щелкнуть на кнопке Включить (Name — InputNom), и в очередную свободную строку на данном листе запишется новая позиция заказа.
Рис. 3.7. Лист для формирования заказа
После того как список запасных частей таким образом заполнен и номер заказа указан, осталось щелкнуть на кнопке Включить в список заказов. В качестве значения свойства Name этой кнопки выбрано InputSpk. За счет программной процедуры это приводит к переносу информации о заказе на лист Названия заказов. Таким образом, теперь можно перейти к рассмотрению процедур, которые обеспечивают выполнение описанных действий. Когда пользователь в процессе работы с книгой переходит на лист Бланк для нового заказа, списки заказчиков и запасных частей должны быть заполнены. Проще всего это обеспечить с помощью процедуры, выполняемой при активизации листа, которая представлена в листинге 3.3.
' Листинг 3.3. Процедура, выполняемая при активизации листа Private Sub Worksheet_Activate() Firma.Clear N = 0 While Worksheets("Клиенты").Cells(N + 2, 1).Value <> "" N = N + 1 Wend For i = 1 To N Firma.AddItem Worksheets("Клиенты").Cells(i + 2, 2).Value Next Spk.Clear N = 0 While Worksheets("Nomen").Cells(N + 2, 1).Value <> "" N = N + 1 Wend For i = 1 To N Spk.AddItem Worksheets("Nomen").Cells(i + 1, 1).Value + _ "" + Worksheets("Номенклатура").Cells(i + 1, 2).Value + "" + _ CStr(Worksheets("Номенклатура").Cells(i + 1, 3).Value) + "руб." Next End Sub
Приведенная процедура заполнения полей со списками организаций и запчастей похожа на рассмотренные ранее примеры и не требует комментария. Более интересна следующая процедура (листинг 3.4), которая выполняется при щелчке на кнопке Включить.
' Листинг 3.4. Процедура обработки щелчка на кнопке Включить Private Sub InputNom_Click() Nom = Spk.ListIndex N = 0 While Cells(N + 11, 1).Value <> "" N = N + 1 Wend Cells(N + 11, 1) = Worksheets("Номенклатура").Cells(Spk.ListIndex + 2, 1) Cells(N + 11, 2) = Worksheets("Номенклатура").Cells(Spk.ListIndex + 2, 2) Cells(N + 11, 3) = Worksheets("Номенклатура").Cells(Spk.ListIndex + 2, 3) Cells(N + 11, 4) = Col.Text End Sub
Теперь на очереди другая процедура (листинг 3.5), которая переносит информацию с листа Бланк для нового заказа на лист Названия заказов, о котором мы уже говорили. В начале процедуры InputSpk_Click() предварительно проверяется — внесена ли информация в ячейку C1. Далее производится поиск присутствия заказа с указанным на листе номером. В случае положительного ответа на этот вопрос процедура не производит запись, а возвращает пользователя к работе с листом формирования нового заказа. В случае уникальности нового номера заказа процедура последовательно переносит имеющуюся информацию в каталог заказов. Для формирования даты используется стандартная функция VBA Date.
' Листинг 3.5. Процедура обработки щелчка на кнопке Включить в список заказов Private Sub InputSpk_Click() ' Номер потенциального заказа VerZakaz = CStr(Range("C1").Value) If VerZakaz = "" Then MsgBox ("Поле кода заказа необходимо заполнить") Exit Sub End If If Firma.ListIndex = -1 Then MsgBox ("Необходимо указать фирму") Exit Sub End If ' Извлечение информации о коде фирмы CodFirma = Worksheets("Клиенты").Cells(Firma.ListIndex + 2, 1).Value ' Подсчет числа имеющихся заказов N = 0 While Worksheets("Названия заказов").Cells(N + 2, 1).Value <> "" N = N + 1 Wend ' Проверка возможного повтора номера заказа For i = 1 To N CodList = Worksheets("Названия заказов").Cells(i + 1, 1).Value If CStr(CodList) = VerZakaz Then MsgBox ("Такой номер заказа уже встречался") Exit Sub End If Next ' Запись информации о данном заказе Worksheets("Названия заказов").Cells(N + 2, 1).Value = Range("C1").Value ' Использование стандартной функции для получения даты Worksheets("Названия заказов").Cells(N + 2, 2).Value = Date Worksheets("Названия заказов").Cells(N + 2, 3).Value = CodFirma ' Вводим переменную для подсчета суммы NDetal = 0 While Cells(NDetal + 11, 2).Value <> "" NDetal = NDetal + 1 Wend ' Подсчет числа деталей в заказе SymmaItog = 0 For i = 1 To NDetal Worksheets("Названия заказов").Cells(N + 2, 5 + (i - 1) * 2).Value = _ Cells(i + 10, 1).Value Worksheets("Названия заказов").Cells(N + 2, 5 + (i - 1) * 2 + 1).Value = _ Cells(i + 10, 4).Value SymmaItog = SymmaItog + CLng(Cells(i + 10, 4).Value) * _ CLng(Cells(i + 10, 3).Value) Next Worksheets("Названия заказов").Cells(N + 2, 4).Value = SymmaItog MsgBox ("Заказ включен") End Sub
Таким образом, мы обеспечили удобный механизм внесения информации в лист Названия заказов. На рис. 3.8 показан результат ввода двух заказов на запасные части.
Рис. 3.8. Пример формирования заказов
Перейдем к отчетам, и один из них показан на рис. 3.9. На этом листе часть информации статична (заголовок и подписи столбцов), а ряд ячеек заполняется исходя из содержания конкретного заказа, которое зафиксировано на листе Названия заказов.
Рис. 3.9. Отчет о заказе
Нам требуется программно обеспечить заполнение ячеек листа, отводимых для номера заказа, заказчика, а также заполнение адреса, телефона и факса. Табличная часть на рис. 3.9 используется в случае, если число позиций в заказе не превышает трех. В противном случае данная табличная часть не заполняется, а создается другой документ — Приложение (рис. 3.10). Наша задача — обеспечить данные функциональные особенности разрабатываемой книги.
Рис. 3.10. Приложение к отчету о заказе
Для заполнения отчета о заказе (и при необходимости приложения к нему) па листе Названия заказов создадим кнопку Создать отчет (см. рис. 3.8). По нажатию этой кнопки будет открываться форма, где от пользователя требуется выбрать заказ, по которому необходимо сформировать отчет. На рис. 3.11 показана данная форма ввода в окне редактора Visual Basic.
Процедура, вызываемая щелчком на кнопке Создать отчет, представлена в листинге 3.6. Ее назначение — открыть форму, обеспечивающую необходимый интерфейс для формирования отчета (см. рис. 3.11).
' Листинг 3.6. Процедура обработки щелчка на кнопке Создать отчет Private Sub CommandButton1_Click() Zakaz.Show End Sub
Рис. 3.11. Форма выбора заказа для отчета
' Листинг 3.7. Процедура, выполняемая при активизации формы Private Sub UserForm_Activate() ' Подсчет числа заказов Nom = 0 While Worksheets("Названия заказов").Cells(Nom + 2, 1).Value <> "" Nom = Nom + 1 Wend ' Подсчет числа фирм Nfir = 0 While Worksheets("Клиенты").Cells(Nfir + 2, 1).Value <> "" Nfir = Nfir + 1 Wend ' Очистка и последующее заполнение поля со списком Spk.Clear ' Перебор числа заказов For i = 1 To Nom ' Извлечение кода фирмы NomFirma = Worksheets("Названия заказов").Cells(i + 1, 3).Value For j = 1 To Nfir If NomFirma = Worksheets("Клиенты").Cells(j + 1, 1).Value Then Firma = Worksheets("Клиенты").Cells(j + 1, 2).Value ' При нахождении фирмы выход из цикла Exit For End If Next Spk.AddItem CStr(Worksheets("Названия заказов").Cells(i + 1, 1).Value) _ + " " + CStr(Worksheets("Названия заказов").Cells(i + 1, 2).Value) _ + " " + CStr(Firma) Next End Sub
Теперь пользователь должен выбрать интересующую его фирму (рис. 3.12). Следующая процедура — обработка щелчка на кнопке Заполнить акт и приложение. Учитывая сложность полного программного кода, приведем его сначала для варианта заполнения только листа АКТ (листинг 3.8).
Рис. 3.12. Выбор заказа для отчета
В зависимости от количества позиций заказа процедура позволяет заполнить или только лист АКТ, или параллельно с актом еще и лист Приложение. В листинге 3.8 приведен первоначальный вариант, который приводит к заполнению только листа АКТ (в случае количества заявок не более трех).
Сегодня я покажу, как заполнить однотипные листы, по шаблону Excel на основе списка.
Смотрите видео: Листы в Excel из списка по шаблону
Переименуем в книге первый лист, это у нас будет список, допустим сотрудников.
Второй лист назовём шаблон, в нем будет находиться таблица, которую сотрудники будут заполнять либо вы будите заполнять для данных сотрудников из списка.
Вот такая будет табличка, потом покажу вам одну формулу в ячейке, как имя листа поместить в ячейку.
Вот список из трех сотрудников, три фамилии они у нас уникальные, отличаются друг от друга это важное условие для работы данного способа заполнения.
Сохраним наш документ как книга Excel с поддержкой макросов с расширением так xlsm, на ленте должен находиться в последнем пункте - пункт Разработчик, если его нет, то в файл, параметры нужно будет в настройках ленты добавить этот пункт, отметьте его галочкой.
В пункте Разработчик, выбираем вкладку Вставить, выбираем кнопку, рисуем кнопку, пока не создаем и не присваиваем никакой макрос. По нажатию кнопки будет формироваться набор листов в книге по списку.
Перейдём на вкладке Разработчик в Visual Basic либо нажмем Alt+F11 , для того чтобы открылся редактор макросов.
Нажмем в меню insert – module, напишем модуль Sub ListTempl() , объявим переменную tmpName.
Получим данные нашего списка из листа список в переменную tmpName, объявив диапазон А1:А3. Но если нужно будет больше, то будем менять это значение. Пока в примере оставим так.
Напишем цикл for от 1 до 3, по сколько мы знаем конечное количество записей нашего списка и теперь сделаем копию листа шаблон, создадим новый лист копии листа с шаблоном, шаблон и присвоим имя каждому листу, значение то которое у нас находится в списке. Закончим цикл next i .
Теперь нажмем правой кнопкой на кнопке, назначить макрос, выберем макрос ListTempl , OK, сохранить.
Нажмем на кнопку, сформировались три листа с название по списку сотрудников, которые были в списке. И можно будет заполнять или печатать.
Вот такой простой код позволяет быстро создать и заполнить книгу листами по определенным данным.
Если нажать на кнопку повторно, то Excel выдаст ошибку, что подобные листы уже используется, надо учитывать этот момент, просто удалите дубли, в этом примере я не буду делать повторную проверку или добавление каких-то случайных значений к имени листов.
Давайте добавим теперь в шапку шаблона формулу, которая будет в ячейку присваивать имя листа, сейчас формула находится на листе с названием шаблон.
Эта формула позволяет получить из значение листа имя листа в ячейку, происходит разбор пути файла до листа.
Нажмем на кнопку, и как видите, сформировались все листы, и в каждом листе, в этой ячейки у нас будет находиться имя сотрудника.
Ну либо того, что у вас будет находилась в списке, при условии, что эти значения уникальны.
Если же всё-таки вам нужно будет сделать два листа для одной уникальной записи, тут могу предложить следующее.
Изменим в коде диапазон на А4 , так как у нас ещё один пункт добавился и в цикле исправим на четыре.
Удалим всё снова и нажмем на кнопку. Опять ошибка, как вариант предлагаю добавить пробел в конце названия дублируемого пункта списка.
В итоге у нас получилось два Ивановых, конечно лучше использовать уникальные значения, тогда этот пример будет достаточно хорошо вам подходить.
Листам в книгах Excel можно дать имена, соответствующие содержимому. Из них было бы удобно составить оглавление, но не все знают, как это сделать. Существуют несложные способы сформировать список листов и методы, требующие усилий, например установки сторонних дополнений. С помощью инструментов Excel пользователи также могут подсчитать количество листов в крупной книге. Выясним, как получить оглавление для чтения или перехода к каждому листу, какими формулами для этого нужно воспользоваться.
Список листов с помощью формулы
Этот способ основан на использовании функции, которую нельзя найти в Менеджере. Она связана с макросами Excel 4.0. Чтобы применить формулу на практике, необходимо пройти дополнительный шаг, редко встречающийся в работе с функциями – зайти в диспетчер имен и добавить туда выражение.
- Открываем лист, где будет расположен список. Выбираем ячейку и записываем в ней формулу с только что созданным именем: =ИНДЕКС(Список_листов;СТРОКА()). Нажмите Enter, и в ячейке появится название первого листа.
- Необходимо вывести все названия листов в столбец. Для этого зажимаем маркер заполнения, который находится в правом нижнем углу выбранной ячейки, и выделяем нужное количество ячеек. В таблице-примере 4 листа столько и было выделено ячеек.
Обратите внимание! Ячейка A1 прописывается в формуле, чтобы пользователи могли перейти на конкретную ячейку каждого листа. После нажатия Enter появится кликабельное название листа.
- Полный список с гиперссылками можно создать так же, с помощью маркера заполнения.
- Если название листа изменено, придется перезаполнить списки – для этого нужно просто удалить их, снова вставить те же формулы и выделить ячейки ниже.
Как составить список листов через VBA
Существует другой способ составления списка листов из книги – можно подключить пользовательскую функцию через редактор Visual Basic. Такой метод может показаться сложным, но это не так, если воспользоваться шаблоном для добавления функции в программу.
Function SheetList(N As Integer)
SheetList = ActiveWorkbook.Worksheets(N).Name
End Function
- Далее можно закрыть окно Visual Basic, потому что этот инструмент больше не понадобится, а функция уже добавлена в программу.
- Открываем лист для списка и вводим формулу в начальную ячейку. Теперь не нужно длинное выражение, чтобы создать список листов. Новая формула выглядит так: =SheetList(СТРОКА()).
- Нажимаем Enter и получаем название листа в ячейке. Маркером заполнения создаем список.
Надстройки для составления списка листов
Надстройки – это дополнения для Microsoft Excel, которые создаются продвинутыми пользователями. Компания Microsoft рассказывает на официальном сайте о возможности подключить надстройки, но не предлагает скачать дополнения, поэтому обычно их загружают из других источников.
Всегда проверяйте загрузки на вредоносные элементы с помощью антивирусной программы.
Как подсчитать количество листов в книге
Иногда в книгах Excel появляется много листов, например если документ относится к крупному проекту. Выяснить, сколько в файле страниц, можно с помощью функции ЛИСТЫ.
Обратите внимание! Функция работает только в версиях Microsoft Excel от 2013.
Читайте также: