Листы в 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 , так как у нас ещё один пункт добавился и в цикле исправим на четыре.
Удалим всё снова и нажмем на кнопку. Опять ошибка, как вариант предлагаю добавить пробел в конце названия дублируемого пункта списка.
В итоге у нас получилось два Ивановых, конечно лучше использовать уникальные значения, тогда этот пример будет достаточно хорошо вам подходить.
Инструкция по созданию опросов в Google Формах
Вы находитесь в режиме дизайна сейчас. Вы можете вставить элементы управления в пользовательскую форму и написать код для соответствующих действий. Элементы управления доступны в панели инструментов. Свойства UserForm находятся в окне свойств. UserForm1 (подпись UserForm) указывается в разделе «Формы» в проводнике проектов.
Как сделать выпадающий список в Excel?
Начнем с основ. Для того, чтобы создать выпадающий список потребуется список с данными и инструмент «Проверка данных».
Выбираем ячейку, в которой будем создавать выпадающий список. Далее переходим к инструменту «Проверка данных», тип данных – «Список». В поле «Источник» указываем диапазон списка.
Такой способ позволяет представить обычный диапазон в виде выпадающего списка. Повторы данных остались в списке (в диапазоне A2:A16 названия городов повторяются и в выпадающем списке они также повторяются). Это, конечно, не удобно. О том, как сделать выпадающий список уникальных значений в Excel мы поговорим далее, пока остановимся на этом варианте.
Магия Excel: 10 самых полезных «фишек» для работы с таблицами — БизнесБизнес
Если вы уже работаете с таблицей Google и вам необходимо создать форму, которая будет автоматически с ней связываться, просто нажмите Инструменты → Создать форму. Ответы формы будут сохраняться в этой таблице отдельным листом без дополнительных настроек.
Проверка ошибок
Проверка данных позволяет избежать ошибок при вводе информации в ячейки.
- Текст вместо чисел
- Отрицательные числа там, где их быть не может
- Числа с дробной частью там, где должны быть целые
- Текст вместо даты
- Разные варианты написания одного и того же значения. Например, сокращения («ЭБ» вместо «Электронная библиотека»), лишние пробелы в конце текстового значения или между словами — всего этого достаточно, чтобы превратить текстовые значения в разные и, соответственно, чтобы они обрабатывались Excel некорректно.
Инструмент проверки данных
Чтобы использовать инструмент проверки данных, нужно выделить ячейки, к которым хотите его применить, выбрать на ленте «Данные» → «Проверка данных» и настроить параметры проверки в диалоговом окне:
Если в графе «Сообщение об ошибке» вы выбрали вариант «Остановка», то после проверки в ячейки нельзя будет ввести значения, не соответствующие заданному правилу.
Если же вы выбрали «Предупреждение» или «Сообщение», то при попытке ввести неверные данные будет появляться предупреждение, но его можно будет проигнорировать и все равно ввести что угодно.
Еще неверные данные можно обвести, чтобы точно увидеть, где есть ошибки:
Удаление пробелов
Для удаления лишних пробелов (в начале, в конце и всех кроме одного между слов) используйте функцию СЖПРОБЕЛЫ / TRIM. Ее единственный аргумент — текст (ссылка на ячейку с текстом, как правило).
Если после очистки данных функцией СЖПРОБЕЛЫ или другой обработки вам не нужен исходный столбец, вставьте данные, полученные в отдельном столбце с помощью функций, как значения на место исходных данных, а столбец с формулой удалите:
Макросы Excel — пользовательские формы.
Под таблицей появится новая строка «Итог». Чтобы узнать сумму для конкретного столбца, нажмите на ячейку под ним (в строке «Итог»). Появится список возможных итогов: среднее значение чисел в столбце, общая сумма, количество чисел, минимальное или максимальное значение в столбце и т. д. Выберите нужный параметр – таблица посчитает результат.
Конструктор форм
Приступим к созданию самого опроса. В Google Forms есть возможность не только создавать вопросы, а и вставлять изображения, видео и делить форму на разделы. Сделать это можно при помощи панели инструментов, которая находится справа от блока с вопросом:
Пункт меню «Импортировать вопросы» позволяет повторно использовать вопросы из ранее созданных форм, причем вы можете выбрать, какие именно вопросы перенести. Это удобно для тех, кто часто работает с Google Forms.
Добавить изображение можно прямо с устройства, с камеры, по ссылке, с Google Диска или через поиск Google. Видео встраиваются с YouTube: вы можете указать ссылку либо найти видео на данной платформе, не покидая своей формы.
Добавление разделов удобно использовать, если опрос получается слишком длинным или содержит вопросы, которые можно объединить в одну тему. Длинный опрос, разбитый на разделы, визуально воспринимается проще. Вы можете добавить название и краткое описание для каждого раздела, а также копировать разделы для создания похожих.
- поле для вопроса;
- поле для описания (по умолчанию скрыто, но можно добавить при помощи меню в виде трех вертикальных точек в правом нижнем углу);
- кнопка для загрузки изображения;
- меню для выбора типа вопроса;
- кнопки «Сделать копию» и «Удалить»;
- опция «Обязательный вопрос» (не ответив на данный вопрос, респондент не сможет отправить форму);
- меню с дополнительными функциями в зависимости от типа вопроса.
Эксель конструктор работа с таблицами
- перенаправить респондента к следующей группе вопросов в зависимости от его ответа;
- выводить ответы в случайном порядке при новом открытии формы;
- задать правильный ответ и пояснения.
Если вы выбрали пункт «Числовые фильтры», то в следующем окне укажите значения ячеек, которые нужно отобразить на экране. Я выбрала значение «больше». Во второй строке указала число и нажала «ОК». Ячейки с цифрами ниже указанного значения в итоге «удалились» (не навсегда) из таблицы.
Элементы управления в панели инструментов
Пользовательская форма будет иметь разные компоненты. Когда вы нажимаете на любой из компонентов, вам либо будут предоставлены инструкции о том, что и как должна быть предоставлена информация, либо вам будут предоставлены варианты (варианты) для выбора. Все это обеспечивается с помощью элементов управления ActiveX в панели инструментов пользовательской формы.
Excel предоставляет два типа элементов управления – элементы управления формы и элементы управления ActiveX. Вы должны понимать разницу между этими двумя типами элементов управления.
Элементы управления формой
Элементы управления формой – это оригинальные элементы управления Excel, которые совместимы с более ранними версиями Excel, начиная с Excel версии 5.0. Элементы управления формой также предназначены для использования на листах макроса XLM.
Элементы управления ActiveX
Элементы управления ActiveX могут использоваться в пользовательских формах VBA. Элементы управления ActiveX имеют широкие свойства, которые можно использовать для настройки их внешнего вида, поведения, шрифтов и других характеристик.
У вас есть следующие элементы управления ActiveX в UserForm ToolBox –
- Указатель
- этикетка
- Текстовое окно
- Поле со списком
- ListBox
- CheckBox
- OptionButton
- Рамка
- Кнопка-переключатель
- CommandButton
- TabStrip
- MultiPage
- Полоса прокрутки
- в полях ввода
- Образ
В дополнение к этим элементам управления Visual Basic предоставляет функцию MsgBox, которую можно использовать для отображения сообщений и / или запроса пользователя о действии.
В следующих нескольких разделах вы поймете эти элементы управления и MsgBox. Затем вы сможете выбрать, какой из этих элементов управления требуется для разработки пользовательской формы.
этикетка
Вы можете использовать ярлыки для идентификации, отображая описательный текст, такой как заголовки, подписи и / или краткие инструкции.
Текстовое окно
Вы можете использовать TextBox в виде прямоугольника для ввода, просмотра или редактирования текста. Вы также можете использовать TextBox в качестве статического текстового поля, которое представляет информацию только для чтения.
Список
Вы можете использовать список для отображения списка одного или нескольких элементов текста, из которых пользователь может выбирать. Используйте список для отображения большого количества вариантов, которые различаются по количеству или содержанию.
Поле со списком с одним выбором – Поле со списком с одним выбором включает только один выбор. В этом случае поле списка напоминает группу кнопок выбора, за исключением того, что поле списка может более эффективно обрабатывать большое количество элементов.
Список выбора множественного выбора – Список списка множественного выбора включает либо один выбор, либо смежные (смежные) варианты.
Поле со списком расширенного выбора – Поле со списком расширенного выбора позволяет выбрать один, непрерывный и несмежный (или несвязанный) выбор.
Поле со списком с одним выбором – Поле со списком с одним выбором включает только один выбор. В этом случае поле списка напоминает группу кнопок выбора, за исключением того, что поле списка может более эффективно обрабатывать большое количество элементов.
Список выбора множественного выбора – Список списка множественного выбора включает либо один выбор, либо смежные (смежные) варианты.
Поле со списком расширенного выбора – Поле со списком расширенного выбора позволяет выбрать один, непрерывный и несмежный (или несвязанный) выбор.
Вы можете выбрать один из этих типов списков в окне свойств.
Далее вы можете написать код для действий по выбору элемента в списке. В противном случае вы можете просто отобразить выбранный текст, как в случае заполнения кода проекта в отчете.
Поле со списком
Вы можете использовать ComboBox, который объединяет текстовое поле со списком для создания выпадающего списка. Поле со списком является более компактным, чем поле со списком, но требует, чтобы пользователь щелкнул стрелку вниз, чтобы отобразить список элементов. Используйте поле со списком, чтобы выбрать только один элемент из списка.
Нажмите стрелку вниз, чтобы отобразить список элементов.
Нажмите на нужный элемент, скажем, Project2016-5. Выбранная опция будет отображаться в поле со списком.
CheckBox
Вы можете использовать флажки, чтобы выбрать один или несколько параметров, которые отображаются, щелкая в полях. Параметры будут иметь метки, и вы сможете четко визуализировать, какие параметры выбраны.
Вы можете использовать флажки для выбора параметров в комбинированном окне, чтобы сэкономить место. В этом случае флажок также может иметь третье состояние –
Смешанный, означающий комбинацию включенных и выключенных состояний, обозначенную черной точкой в поле. Это будет отображаться, чтобы указать несколько вариантов выбора в комбинированном окне с флажками.
Установите флажки в пользовательской форме, как показано ниже.
Смешанный, означающий комбинацию включенных и выключенных состояний, обозначенную черной точкой в поле. Это будет отображаться, чтобы указать несколько вариантов выбора в комбинированном окне с флажками.
Установите флажки в пользовательской форме, как показано ниже.
OptionButton
Вы можете использовать кнопку выбора, также называемую переключателем, чтобы сделать один выбор в пределах ограниченного набора взаимоисключающих вариантов. Кнопка выбора обычно содержится в групповом поле или рамке.
Кнопка выбора представлена маленьким кружком. Кнопка выбора может иметь одно из следующих двух состояний:
Рамка
Вы можете использовать элемент управления кадром, также называемый групповым блоком, для группировки связанных элементов управления в один визуальный блок. Как правило, кнопки выбора, флажки или тесно связанное содержимое группируются в элементе управления фреймом.
Элемент управления фреймом представлен прямоугольным объектом с необязательной меткой.
Вставьте две кнопки с надписями «Да» и «Нет» в элементе управления кадром. Опции Да и Нет являются взаимоисключающими.
Вставьте две кнопки с надписями «Да» и «Нет» в элементе управления кадром. Опции Да и Нет являются взаимоисключающими.
Кнопка-переключатель
Вы можете использовать кнопку переключения, чтобы указать состояние, например, Да или Нет, или режим, например, включить или выключить. Кнопка чередуется между включенным и отключенным состоянием при нажатии.
Вставьте кнопку переключения на пользовательскую форму, как показано ниже –
Выберите Run Sub / UserForm из выпадающего списка. По умолчанию кнопка переключения будет в активированном состоянии.
Выберите Run Sub / UserForm из выпадающего списка. По умолчанию кнопка переключения будет в активированном состоянии.
Нажмите кнопку переключения. Кнопка переключения будет отключена.
Если вы снова нажмете кнопку переключения, она будет включена.
CommandButton
Вы можете использовать командную кнопку для запуска макроса, который выполняет некоторые действия, когда пользователь нажимает на него. Вы уже научились использовать командную кнопку на рабочем листе для запуска макроса.
Командная кнопка также называется кнопкой. Вставьте командную кнопку в пользовательскую форму, как показано ниже –
Нажмите кнопку команды. Откроется выпадающий список со списком, так как это действие, которое вы написали в коде.
TabStrip
Вы можете вставить вкладку, которая напоминает вкладки Excel в пользовательской форме.
Полоса прокрутки
Вы можете использовать полосу прокрутки для прокрутки диапазона значений, щелкая стрелки прокрутки или перетаскивая поле прокрутки.
Вставьте полосу прокрутки в пользовательскую форму, нарисовав ее в нужном месте, и отрегулируйте длину полосы прокрутки.
Перетащите поле прокрутки. Текст – Значения прокрутки будут отображаться в текстовом поле, как вы указали его в качестве действия для прокрутки полосы прокрутки.
MsgBox ()
Вы можете использовать функцию MsgBox () для отображения сообщения, когда вы нажимаете на что-то. Это может быть руководство или некоторая информация или предупреждение или предупреждение об ошибке.
Например, вы можете отобразить сообщение о том, что значения прокручиваются, когда вы начинаете прокручивать поле прокрутки.
Зависимый выпадающий список в Excel и Google таблицах · BIRDYX
Сортировка отличается от фильтрации тем, что в первом случае количество строк и столбцов таблицы сохраняется, во втором – не обязательно. Просто ячейки выстраиваются в другом порядке: от меньшего к большему или наоборот. При фильтрации некоторые ячейки могут удаляться (если их значения не соответствуют заданным фильтрам).
