Как в Экселе Сделать Справочник в Excel
Надо признать, что термин умные таблицы достаточно хорошо ухватывает суть явления. Действительно, иметь дело в Excel с умными таблицами куда проще и приятнее, чем с обычными. Однако не все о них знают. Давайте восполним этот пробел, потому что умные таблицы воистину изменят вашу жизнь и вы не захотите возвращаться после этого к обычным таблицам.
- Всем столбцам давать уникальные названия колонок.
- Не допускать пустых столбцов и строк в таблице.
- Не допускать разнородных данных в пределах одной колонки. Если уж решили, что, например, в колонке E должен хранится объем продаж в штуках, то не надо туда же вносить объём продаж, скажем, в деньгах у части строк таблицы.
- Не объединять ячейки без самой крайней необходимости.
- Форматировать таблицу, чтобы она выглядела одинаково во всех своих частях. То есть элементарно рисовать сетку, выделять цветом заголовки столбцов.
- Закреплять области, чтобы заголовок был всегда виден на экране.
- Ставить фильтр по умолчанию.
- Вставлять строку подитогов.
- Грамотно использовать абсолютные и относительные ссылки в формулах, чтобы их можно было протягивать без необходимости внесения изменений.
- При рабте с таблицей не выделять цветом строки/столбцы за пределами таблицы. Это поветрие, кстати очень сильно распространено, — взять выделить всю строку или весь столбец одним кликом мыши и закрасить. И наплевать, что в таблице 100 строк, а закрасилось помимо них ещё 1 000 000 строк. А потом невинно интересоваться: «Почему мои файлы так много весят?»
Соблюдение этих простых правил поможет вам, если не уходить пораньше с работы домой, так хотя бы работать более продуктивно и осмысленно, осваивая действительно интересные и сложные вещи, а не воюя с последствиями своей неаккуратности на каждом шагу.
Так вот к 13-й версии (Excel 2007) его разработчики пригляделись к типовым действиям квалифицированных пользователей Excel и падарили нам функционал умных таблиц, за что им огромное спасибо. Потому что большую часть того, что я только что перечислил умные таблицы либо делают сами автоматически, либо очень сильно облегчают настройку оного.
Итак, давайте познакомимся, как создаются умные таблицы и какими полезными свойствами обладают.
Автоматизация расчётов в электронных таблицах Excel
- Способ 1 — на ленте ГЛАВНАЯ выбираем Форматировать как таблицу , выбираем понравишейся дизайн (при этом вам доступны 60 стандартных способа форматирования)
- Способ 2 — Нажимаем Ctrl-T
- Способ 3 — На ленте ВСТАВКА выбрать Таблица
Необходимо иметь в виду, что при копировании происходит транспонирование адресов. При копировании формулы из одной ячейки в другую Excel по-разному реагирует на формулы с относительными и абсолютными ссылками. Для относительных Excel по умолчанию производит транспонирование адресов, в зависимости от позиции ячейки, в которую копируется формула.
Листы в 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 из списка по шаблону
После нажатия на кнопку «ОК» Microsoft Excel создаст их в указанных ячейках. На появившейся вкладке «Конструктор» (Design) можно дополнительно настроить их цвет, тип, включить отображение минимальных и максимальных значений и т. д.
