Умные таблицы в 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 не было и этого.
Например, если скопировать в обычной таблице столбец "С", а вставить скопированные данные в умную таблицу в столбец "D", то произойдет вставка данных из столбца "D" обычной таблицы, а не из "C", как планировалось.
Как этого избежать и как заставить Excel вставлять данные, те которые мне нужны, а не ему
Заранее спасибо
Таблица1[Сосед], а надо чтобы осталась Таблица1[НДС].
вместо ДВССЫЛ("Таблица1[НДС]")
пишем: [Таблица1[НДС]:[НДС]]
т.е. теперь при протягивании по месяцам вправо например на формуле Суммеслимн столбец с Условием "НДС" остается фиксированным;)
Если нужно ограничить область данных, то Таблица1[[#Данные];[Закупки]:[Продажи]]
Не получается использовать "Промежуточный итог" с умной таблицей(кнопка неактивна)
Работает тут промежуточный итог...у меня очень удобненькл выведен наверх..автоматом изменяется...при фильтрации показывает только то, что зафильтровано.
Проблема с защитой листа...не работает автозаполнение, увы. Хочется закрыть именно столбцы с формулами, чтобы их корявыми ручками не редактировали, но тогда смысл вообще теряется.
Надеюсь, что здесь кто-то уже нашел решение и готов поделиться.
Я в свою очередь про промежуточный итог выдам "страшную" тайну. )
вы меня заинтриговали! что значит "более изящное"?
а по защите таблицы нет информации? или мне теперь нечего выдавать?
Не улавливаю проблему с промежуточными итогами.. Ведь при включенной строке Итогов в Конструкторе при фильтрации подводятся все промежуточные итоги, или речь о чём-то другом?
С благодарностью к Вам за все Ваши уроки, Дарья
Можно еще функцию ПОИСКПОЗ использовать для поиска в шапке нужного названия, но это не проще будет.
Таблица1[[Столбец1]: [Столбец1]]
При протягивании формулы влево-вправо он не будет смещаться.
Пробовал даже сводную преобразовать в умную таблицу, не помогает
Предполагаю, что я что-то делаю неправильно...
В частности интересует использование данной возможности в функции ВПР():
Заранее благодарен.
Возможно есть другое решение?
Надо на Лист2 копию этой таблицы, без некоторых столбцов, при этом чтобы новые строчки тоже добавлялись.
Сделал во второй таблице в столбце формулу вида =ЕСЛИОШИБКА(Таблица1[Магазин];"")
Но в первой таблице когда добавляются новые строчки - во второй не добавляются. Можно ли сделать, чтобы добавлялись?
=ЕСЛИОШИБКА(ДВССЫЛ("Таблица1[Магазин]");"")
и при добавлении новых строк на другом листе должно быть видно все строки, включая новые. Я подобное при проверке вводимых данных в ячейке "по списку" из столбца "умной" таблицы из другого листа использую: =ДВССЫЛ("Таблица3[аа]"), а иначе =Таблица3[аа] - выдаёт ошибку, хотя и видимость Таблица3 вся книга
Если ли решение?
спасибо
Многострочные шапки совсем нельзя. Можно попытаться имитировать или подставлять на время работы однострочную, а "для шефа" подставлять красивую многоэтажную.
=ЕСЛИ(СЧЁТЗ(A2:E2)>0;СУММ(A2:E2);0)
Т.Е. как поставить защиту листа, при этом с отдельных столбцов снять "галку" защищаемая ячейка, и сохрать функционал "умной таблицы"?
Имеется несколько умных таблиц, абсолютно идентичных по формату (число столбцов, заголовки), но с разным содержанием, каждая таблица на отдельном листе, наименование таблицы соответственно характеру содержания (типа Деревья, Звери, Рыбы и т.д.)
На первом листе умная таблица аналогичного формата (число столбцов, заголовки), но содержание формируется из остальных таблиц следующим образом:
1 колонка - все ячейки с выпадающими списками с полным перечислением названий таблиц (типа Деревья, Звери, Рыбы и т.д.)
4 колонка - все ячейки с выпадающими списками с формулой =ДВССЫЛ([@1]&"[4]")
2 колонка с формулой =ИНДЕКС([@1];ПОИСКПОЗ([@4];[@1]&"["&"4"&"]";0);2)
3 колонка с формулой =ИНДЕКС([@1];ПОИСКПОЗ([@4];[@1]&"["&"4"&"]";0);3)
Ошибка возникает из-за невозможности прочитать имя диапазона(умной таблицы) из ячейки на которую дана ссылка типа [@1]
Я как-то и забыл про ДВССЫЛ, а ведь решение было почти на поверхности:)
терзает меня вопрос - таблица создается как объект, и к нему можно обратиться в ВБА:
ActiveWorkbook.Worksheets("Лист1").ListObjects("Таблица1").
в самой таблице заголовки должны иметь уникальное имя, подозреваю они будут являться объектами внутри таблицы, так вот можно ли у ним обращаться к их имени, или только адресом внутри?
Может это поможет
У меня есть две умные таблицы на одном листе, одна изменяющаяся другая нет. Располагаются так: изменяющаяся сверху на всю ширину листа (печатной области), а вторая маленькая ниже на две строки. Проблема возникает при добавлении новых строк в первую верхнюю таблицу, она растет вниз захватывая пустые строки и приближается ко второй таблице. Как сделать так чтобы это расстояние между таблицами оставалось неизменным.
Во второй таблице есть формулы которые ссылаются на первую таблицу. Обязательное условие: расположение обеих таблиц на одном листе для дальнейшей распечатки.
Подскажите, можно ли организовать выпадающий список который будет брать значения из определенного столбца умной таблицы?
=ДВССЫЛ("Таблица1[Имя_столбца]")При защите листа авторазвёртывание отключается автоматически. Но можно при защите разрешить пользователям вставку строк. Новые строки надо добавлять вовнутрь диапазона таблицы. Защита распространится на новые ячейки.
Ну, а с тем, что автоматически срабатывает автоподбор ширины столбцов, пришлось смириться.
Буду благодарна за помощь.
Помогите пожалуйста решить проблему. В своих книгах я использую "умные таблицы", соответственно все обращения в моих формулах написаны не в виде адреса, а в виде имен столбцов, строк и т.д. (ИНДЕКС(Сводка[#Данные];ПОИСКПОЗ(ОтчетОплатаПоставщику[@Контрагент]...). Я имею таблицу, в которой как выяснилось, периодически повторяются определенные данные, по которым происходит поиск соответствий. С помощью связки функций ИНДЕКС и ПОИСКПОЗ, я имею возможность получить только первое вхождение искомых данных. Не могли бы Вы подсказать, каким образом мне выудить не первое, а последнее вхождение искомого. Раньше я использовал функцию НАИМЕНЬШИЙ для изменения размера диапазона, а как же менять его размер, если он жестко зафиксирован размером столбца, например Таблица1[#Данные][Столбец1]? Заранее благодарю!
подскажите как вытащить имя таблицы?
На вкладке Конструктор есть имя таблицы в которой стоит активная ячейка.
На вкладке Формулы - Диспетчер имен - можно увидеть имена всех таблиц, которые есть в файле.
При использовании умными таблицами возникает необходимость составлять формулы по стандартным форме, например =СУММ(F2:F200) вместо =СУММ(Таблица1[НДС] ).
Подскажите, пожалуйста, как ускорить процесс ввода таких формул, не прибегая к ручному прописыванию (F2:F200), например.
Вопрос 2 - а можно в ней получать две итоговые суммы? Первая по отсортированной дате; Вторая - общая по всей таблице.
2. для итогов после фильтрации нужна функция вида ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;Таблица1[имя_столбца]), а для общих итогов простая СУММ(Таблица1[имя_столбца])
Возможно ли преобразовать "Умную таблицу" Обратно простую. Столкнулась с проблемой. При копирование данных из разных таблиц в одну. Таблица искажает данные и меняет ссылки. А когда я раньше работала с таблицей не преобразовав ее все вставлялось корректно.
Имеется в виду, оттягивание её.
Я правильно понимаю, что при использовании умных таблиц диаграмма построенная на базе такой таблицы будем сама менять диапазон в соответствии с изменением строк/столбцов в умной таблице?
UPD: При добавлении строк таблица расширяется, диаграмма достаивается. А вот при добавлении столбцов только таблица меняется.
Подскажите, возможно, ли вставить в определенном месте (в середине) диапазона / умной таблицы какое-то количество новых строк (формат и количество столбцов одинаковое) таким образом, чтобы они не заменили информацию в уже существующих строках?