Умные таблицы в Excel
Видео
Постановка задачи
Имеем таблицу, с которой постоянно приходится работать (сортировать, фильтровать, считать по ней что-то) и содержимое которой периодически изменяется (добавляется, удаляется, редактируется). Ну, хотя бы, для примера - вот такого вида:
Размер - от нескольких десятков до нескольких сотен тысяч строк - не важен. Задача - всячески упростить и облегчить себе жизнь, превратив эти ячейки в "умную" таблицу.
Решение
Выделяем любую ячейку в таблице и на вкладке Главная (Home) разворачиваем список Форматировать как таблицу (Format as table):
В раскрывшемся списке стилей выбираем любой вариант заливки на наш вкус и цвет и в окне подтверждения выделенного диапазона жмем ОК и получаем на выходе примерно следующее:
В результате после такого преобразования диапазона в "умную" Таблицу (с большой буквы!) имеем следующие радости (кроме приятного дизайна):
- Созданная Таблица получает имя Таблица1,2,3 и т.д. которое, можно изменить на более адекватное на вкладке Конструктор (Design). Это имя можно использовать в любых формулах, выпадающих списках и функциях, например в качестве источника данных для сводной таблицы или массива поиска для функции ВПР (VLOOKUP).
- Созданная один раз Таблица автоматически подстраивается в размерах при добавлении или удалении в нее данных. Если дописать к такой Таблице новые строки - она растянется ниже, если добавить новые столбцы - разойдется вширь. В правом нижнем углу Таблицы можно увидеть автоматически перемещающийся маркер границы и, при необходимости, скорректировать его положение мышью:
- В шапке Таблицы автоматически включается Автофильтр (можно принудительно отключить на вкладке Данные (Data)).
- При добавлении новых строк в них автоматически копируются все формулы.
- При создании нового столбца с формулой - она будет автоматически скопирована на весь столбец - не надо тянуть формулу черным крестом автозаполнения.
- При прокрутке Таблицы вниз заголовки столбцов (A, B, C…) меняются на названия полей, т.е. уже можно не закреплять шапку диапазона как раньше (в Excel 2010 там еще и автофильтр):
- Включив флажок Показать итоговую строку (Total row) на вкладке Конструктор (Design) мы получаем автоматическую строку итогов в конце Таблицы с возможностью выбора функции (сумма, среднее, количество и т.д.) по каждому столбцу:
- К данным в Таблице можно адресоваться, используя имена отдельных ее элементов. Например, для суммирования всех чисел в столбце НДС можно воспользоваться формулой =СУММ(Таблица1[НДС]) вместо =СУММ(F2:F200) и не думать уже про размеры таблицы, количество строк и корректность диапазонов выделения. Также возможно использовать еще следующие операторы (предполагается, что таблица имеет стандартное имя Таблица1):
- =Таблица1[#Все] - ссылка на всю таблицу, включая заголовки столбцов, данные и строку итогов
- =Таблица1[#Данные] - ссылка только на данные (без строки заголовка)
- =Таблица1[#Заголовки] - ссылка только на первую строку таблицы с заголовками столбцов
- =Таблица1[#Итоги] - ссылка на строку итогов (если она включена)
- =Таблица1[#Эта строка] - ссылка на текущую строку, например формула =Таблица1[[#Эта строка];[НДС]] - будет ссылаться на значение НДС из текущей строки таблицы.
(В англоязычной версии эти операторы будут звучать, соответственно, как #All, #Data, #Headers, #Totals и #This row).
P.S.
В Excel 2003 было что-то отдаленно похожее на такие "умные" таблицы - называлось Списком и создавалось через меню Данные - Список - Создать список (Data - List - Create list). Но даже половины нынешнего функционала там не было и в помине. В более древних версиях Excel не было и этого.
При защите листа авторазвёртывание отключается автоматически. Но можно при защите разрешить пользователям вставку строк. Новые строки надо добавлять вовнутрь диапазона таблицы. Защита распространится на новые ячейки.
Ну, а с тем, что автоматически срабатывает автоподбор ширины столбцов, пришлось смириться.
Буду благодарна за помощь.
Помогите пожалуйста решить проблему. В своих книгах я использую "умные таблицы", соответственно все обращения в моих формулах написаны не в виде адреса, а в виде имен столбцов, строк и т.д. (ИНДЕКС(Сводка[#Данные];ПОИСКПОЗ(ОтчетОплатаПоставщику[@Контрагент]...). Я имею таблицу, в которой как выяснилось, периодически повторяются определенные данные, по которым происходит поиск соответствий. С помощью связки функций ИНДЕКС и ПОИСКПОЗ, я имею возможность получить только первое вхождение искомых данных. Не могли бы Вы подсказать, каким образом мне выудить не первое, а последнее вхождение искомого. Раньше я использовал функцию НАИМЕНЬШИЙ для изменения размера диапазона, а как же менять его размер, если он жестко зафиксирован размером столбца, например Таблица1[#Данные][Столбец1]? Заранее благодарю!
подскажите как вытащить имя таблицы?
На вкладке Конструктор есть имя таблицы в которой стоит активная ячейка.
На вкладке Формулы - Диспетчер имен - можно увидеть имена всех таблиц, которые есть в файле.
При использовании умными таблицами возникает необходимость составлять формулы по стандартным форме, например =СУММ(F2:F200) вместо =СУММ(Таблица1[НДС] ).
Подскажите, пожалуйста, как ускорить процесс ввода таких формул, не прибегая к ручному прописыванию (F2:F200), например.
Вопрос 2 - а можно в ней получать две итоговые суммы? Первая по отсортированной дате; Вторая - общая по всей таблице.
2. для итогов после фильтрации нужна функция вида ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;Таблица1[имя_столбца]), а для общих итогов простая СУММ(Таблица1[имя_столбца])
Возможно ли преобразовать "Умную таблицу" Обратно простую. Столкнулась с проблемой. При копирование данных из разных таблиц в одну. Таблица искажает данные и меняет ссылки. А когда я раньше работала с таблицей не преобразовав ее все вставлялось корректно.
Имеется в виду, оттягивание её.
Я правильно понимаю, что при использовании умных таблиц диаграмма построенная на базе такой таблицы будем сама менять диапазон в соответствии с изменением строк/столбцов в умной таблице?
UPD: При добавлении строк таблица расширяется, диаграмма достаивается. А вот при добавлении столбцов только таблица меняется.
Подскажите, возможно, ли вставить в определенном месте (в середине) диапазона / умной таблицы какое-то количество новых строк (формат и количество столбцов одинаковое) таким образом, чтобы они не заменили информацию в уже существующих строках?
Подскажите, можно ли в качестве диапазона для умной таблицы использовать динамический диапазон на функции СМЕЩ (OFFSET) или именованный диапазон с соответствующей функцией.
Идея была "приставить" умную таблицу к сводной таблице с изменяющимся количеством строк, для дополнительных вычислений. Если делать просто с помощью формул, то их приходится протягивать сильно вниз с запасом. Использование умной таблицы на основе динамического диапазона помогло бы решить задачу красиво, уменьшить размер файла, сократить время пересчёта...
Но выбрать имя этого диапазона как источник данных для умной таблицы не получается
Методом "Тыка" не получается, в интернете инструкций тоже не нашёл...
Если такая возможность есть - научите, буду признателен!
Если таковой имеется, подскажите пожалуйста!
У меня есть уже отредактированный прайс цен, там ловольно много объединенных ячеек, но когда я применяю "Форматирование Таблицы", то все объединенные ячейки становятся обычными и заново их объединить не получается. Скажите, можно ли применив "Форматирование Таблицы" сохранить исходный вид прайса?
Для примера прикрепляю два изображение как было и как стало:
Надеюсь на вашу помощь,
Спасибо:)
Скажите, как правильно обратиться к умной таблице в случае,
когда ее выбираем в качестве источника для выпадающего списка
в проверке данных?
Сейчас я просто ссылаюсь так: =Справочники!$B$2:$B$5
При этом я не учитываю, что этот же диапазон определяет умную таблицу Таблица1.
Как обратиться к Таблица1? =Справочники!Таблица1[Столбец1] ?
Выдает ошибку.
Спасибо.
А подскажите пожалуйста, как заставить умную таблицу "забыть" формулу, которую я ввел в вычисляемом столбце.
Допустим, в каком-либо столбце была формула "=1" и она послушно разворачивается при добавлении строк в таблицу. Теперь я решил, что в данном поле должны быть просто данные. Простым удалением формулы из ячеек этого поля ничего не добьешься - при добавлении новых строк Excel упорно будет восстанавливать формулу. Как заставить его ее забыть без удаления поля и создания его заново?
У меня вопрос в ту же тему. Мне надо изменить формулу вычисляемого столбца , но excel упорно вставляет первоначально записанную формулу при вставке новых строк в умную таблицу.
Немного эмоций :
все Ваши материалы КЛАД! и государству ничего не надо). Огромное спасибо за Вашу деятельность. С сайтом знакома давно, но был перерыв в практике использования Excel, и сейчас кое-что вспоминаю, а что-то и вновь осваиваю с помощью Ваших материалов уже на 2013. Более полезного и структурированного ресурса для себя не нахожу.
Вопрос связан с добавлением столбцов в "умную таблицу" - при вставке столбца и условиях, что справа и слева расположены столбцы со списками,
получаем новый столбец, соответственно со списком....Как избавиться от последнего (красиво и правильно избавиться)?
На обычном листе, получается выделить столбец и изменить значение Список на Любое значение через Проверку данных при выделенном столбце, а вот в "умной таблице" при выделенном (добавленном вставкой) столбце - Проверка просто неактивна....( Проверка активна, если выбрана ячейка, но тут либо к одной ячейке применить (что может быть оч затруднительно и трудоемко), либо ко всему диапазону с условием - тогда изменения постигнут и еще один столбец. а в нем - Список необходим.
Возможно есть какие хитрости при вставке, или выделении....Поделитесь пожалуйста - Как получить внутри "умной таблицы" "чистый" столбец? или по аналогии предыдущего вопроса - как заставить вставленный столбец забыть список?
У меня вроде бы работает так валидация...
Выделяются все значения столбца точно так же, как и весь столбец, когда появляется чёрная стрелка вниз, только ловить стрелку нужно в верхней части заголовка соответствующего столбца.
((( до чего ж я темнота )))
...самое очевидное - забыть про шапку .... даже итоги отменяла...эээ
А вот как сделать это с ячейками умной таблицы не знаю. при наборе формуле не получается выбрать ячейку из столбца умной таблицы.
Разобрался.
Подскажите, пожалуйста, разумное решение ситуации.
В книге несколько листов с одинаковыми "умными" таблицами. Вернее "скелет" таблиц одинаковый, данные на каждом листе разные, и разное количество строк. Необходимо на итоговом листе (с тем же "скелетом") разместить итоговые строки по каждому листу и вычислить среднее по каждому столбцу. Есть ли какой-то способ перенести итоги, не тыкая в каждый аргумент на каждом листе? Или, может быть, это как-то быстро умеет делать PLEX?
помогите пожалуйста.. есть столбец с номенклатурами 8 символов есть позиции которые начинаются с 1,2,3,4-ех "0" (например 00001111) мне нужно добавить в этот столбец несколько строк, как присоединить эти строки к имеющейся таблице чтобы они участвовали в дальнейших действиях. проблема в том что добавленные в ручную позиции не сортируются с остальной таблицей и не попадают в дальнейшие действия (например впр выдает по данным номенклатурам Н/Д хотя позиции в списке есть)
Sub Sort_sharj()
ActiveSheet.Unprotect Password:="aa"
Range("a7:h100006").Sort Key1:=Range("b7"), Order1:=xlAscending, Key2:=Range("a7") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers
ActiveSheet.Protect Password:="aa", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True
Range("a7").Select
End Sub
По умным таблицам проясните, пожалуйста.
Есть умная таблица и в одном из столбцов данные вводятся в формате "Проверка данных".
При добавлении новой строки все получается прекрасно формулы копируются, таблица автоматически расширяется, но атрибут "Проверка данных" в нужном столбце не появляется. И приходиться при каждом добавлении новой строки вручную копировать предыдущую ячейку столбца, что бы появилась "Проверка данных". Возможно ли сделать чтобы атрибут "Проверка данных" тоже копировался при добавлении строк в умной таблице.
Спасибо, Владимир