Содержание:
Видео
Постановка задачи
У нас есть таблица, с которой нам постоянно приходится работать (сортировать, фильтровать, что-то по ней считать) и содержимое которой периодически меняется (добавлять, удалять, редактировать). Ну хотя бы для примера – вот так:
Размер – от нескольких десятков до нескольких сотен тысяч строк – не имеет значения. Задача — всячески упростить и облегчить вам жизнь, превратив эти ячейки в «умную» таблицу.
Решения
Выделите любую ячейку в таблице и на вкладке Главная (Главная) расширить список Форматировать как таблицу (В формате таблицы):
В выпадающем списке стилей выбираем любой вариант заливки на наш вкус и цвет и в окне подтверждения выбранного диапазона нажимаем кнопку OK и получаем следующий результат:
В результате после такого превращения линейки в «умную» Настольные (с большой буквы!) имеем следующие радости (кроме приятного дизайна):
- Создано Настольные получает имя Таблица 1,2,3 и т.д. который можно поменять на более адекватный на вкладке Конструктор (Дизайн). Это имя можно использовать в любых формулах, раскрывающихся списках и функциях, таких как источник данных для сводной таблицы или массив поиска для функции ВПР.
- Создано один раз Настольные автоматически подстраивается под размер при добавлении или удалении в него данных. Если добавить к такому Настольные новые строки — вытянется ниже, если добавить новые столбцы — расширится вширь. В правом нижнем углу таблицы вы можете увидеть автоматически перемещающийся маркер границы и при необходимости отрегулировать его положение с помощью мыши:
- В шляпе таблицы автоматически Автофильтр включается (можно принудительно отключить на вкладке Данные (Дата)).
- При автоматическом добавлении в них новых строк все формулы копируются.
- При создании нового столбца с формулой – он будет автоматически скопирован на весь столбец – не нужно перетаскивать формулу черным крестом автозаполнения.
- При прокрутке таблицы вниз заголовки столбцов (A, B, C…) заменяются на имена полей, т.е. вы больше не сможете исправить заголовок диапазона, как раньше (в Excel 2010 также есть автофильтр):
- Включив флажок Показать общую строку (Общая строка) таб Конструктор (Дизайн) в конце мы получаем строку автоматических итогов таблицы с возможностью выбора функции (сумма, среднее, количество и т.д.) для каждого столбца:
- К данным в Настольные можно адресовать используя названия его отдельных элементов. Например, чтобы просуммировать все числа в столбце НДС, можно использовать формулу =СУММ(Таблица1[НДС]) вместо = СУММ (F2: F200) и не думать о размере таблицы, количестве строк и правильности выбора диапазонов. Также можно использовать следующие операторы (при условии, что таблица имеет стандартное имя Таблица 1):
- =Таблица1[#Все] — ссылка на всю таблицу, включая заголовки столбцов, данные и итоговую строку
- =Таблица1[#Данные] – ссылка только для данных (без заголовка)
- =Таблица1[#Заголовки] — ссылка только на первую строку таблицы с заголовками столбцов
- =Таблица1[#Итого] — ссылка на итоговую строку (если она включена)
- =Таблица1[#Эта строка] — ссылка на текущую строку, например, формула =Таблица1[[#Эта строка];[НДС]] будет ссылаться на значение НДС из текущей строки таблицы.
(В английской версии эти операторы будут звучать соответственно как #All, #Data, #Headers, #Totals и #This row).
PS
В Excel 2003 было что-то отдаленно похожее на такие «умные» таблицы – оно называлось Список и создавалось через меню. Данные – Список – Создать список (Данные — Список — Создать список). Но даже половины текущего функционала там вообще не было. В старых версиях Excel этого тоже не было.