Умные таблицы в 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 не было и этого.
Есть задача в которой имеется два Листа. На первом Листе имеется умная Таблица в которую автоматически подставляются данные с второго Листа. Во второй Лист данные попадают из автоматического экспорта данных из текстового файла при открытии документа. Суть задачи в том чтобы размерность по количеству строк в первой таблице подстраивался под размерность данных экспортируемых на второй лист.
Подскажите пожалуйста как добиться данного функционала?
Даже приблизительно не имею представления как подобраться к данной задаче. Размерность на втором листе может варьироваться от 10 строк, и до 1000+ и вручную править размер умной таблицы крайне неудобно. Есть ли возможность работать с умной Таблицей посредством макросов?
подскажите! в умной таблице есть ячейка с выпадающем списком. при добавлении строки с новыми значениями приходится постоянно копировать ячейку с выпадающим списком. как сделать, что бы при добавлении строки эта ячейка была уже с выпадающим списком.
я работаю я программой bCAD и создаю отчет о всех деталях проекта мебели в экселе путем экспорта XML. Открываю новый документ, Разработчик, Источник, и выбираю нужный источник, поставляемый программой bCAD, с расширением .xsd. Далее из дерева источника накидываю заголовки колонок своей будущей умной таблицы, т.е. создаю шапку таблицы куда и пойдут все детали мебели. Так вот в данной таблице есть несколько колонок с названием Материал, которая в последствии будет содержать название материала детали (например, Белая). Все хорошо, но проблема в том, что в денной колонке мне необходимо не только просто материала (Белая), но и его путь к по папкам к этому материалу (например, ЛДСП/Egger/толщ 16мм/Белая). Путь по папкам уже идет откуда-то из каталога программы.
Можете ли Вы помочь мне в этом? очень надеюсь, потому что техподдержка bCAD утверждает, что в этом мне могут помочь только специалисты по Excel.
есть защищеный лист
в нем несколько умных таблиц. В строках (в ячейках) умной таблицы есть выпадающие списки. Ячейка из выпадающего списка имеет текст допустим из 40 символов текста или из 150 символов текста
прописан макрос на добавления строк в умною таблицу.
Теперь задача, как добиться авто подстравания строки, так как если мы выбираем из ячейки текст уже с более большим кол -вом символов он не виден.
Объеденых ячеек в умной таблице нет.
напомню на защищено листе нет выбора автоподстройки строки на панели.
Подскажите, что изменилось в этой статье, что она поднялась в топ?
1. в умных таблицах не работают промежуточные итоги
2. перестают быть "умными", как только ставишь защиту листа
3. в таблицах нельзя использовать формулы для массивов с несколькими ячейками
еще какие?
как это обходить?
Возможно, я чего-то не знаю? Где-то есть "птичка"?
Ссылка прописана в тупую, примерно так: =W:\\Анализ\2021\Журнал АГ.xlsx'!Таблица2[@РезультатИБ]
Прошу помочь. Необходимо создать срез в "умной таблице" для фильтрации по месяцу, но столбец содержит даты и соответственно отражает данные в срезе только в формате краткой даты. В целом, должно получиться по принципу группировки дат в сводной таблице.
Такой вопрос: есть умная таблица (преобразована из простой со всеми формулами), есть в ней столбец с формулами. И вот потребовалось изменить только одну формулу в столбце. Эксель тут же вносит изменения во все формулы столбца. А можно как-то внести изменения только в одну ячейку, чтобы формулы выше и ниже не менялись?
В Excel 2003 — главное преимущество: простота, позволяющая быстро создавать таблицы-прототипы с именованными диапазонами…
МОЙ ВОПРОС ОТНОСИТСЯ К СОЗДАННОЙ ВАМИ ДИНАМИЧЕСКОЙ (УМНОЙ) ТАБЛИЦЕ В ВИДЕОУРОКЕ "СИМУЛЯТОР ЛОТЕРЕИ В MICROSOFT EXCEL" (РЕЧЬ НЕ ИДЕТ О ТОМ, ЧТО Я ЛЮБИТЕЛЬ ЛОТЕРЕЙ), ПРОСТО ПРОСМОТРЕВ ВИДЕО И ВНИМАТЕЛЬНО ПРОЧИТАВ ВАШУ СТАТЬЮ, Я РЕШИЛ САМ ПОПРОБОВАТЬ СОЗДАТЬ ПО ВАШЕМУ ПРИМЕРУ ТО, ЧТО СОЗДАЛИ ВЫ И ПОСМОТРЕТЬ, КАК ЭТО РАБОТАЕТ. ЗАДАЧА – 1 (ВЕРОЯТНОСТЬ ВЫГРЫША), ПО УКАЗАННОЙ ВАМИ ФУНКЦИИ, СРАБОТАЛА БЕЗ ПРОБЛЕМ. А ВОТ С ЗАДАЧЕЙ – 2 (ЧАСТОТА ВЫПАДЕНИЯ КАЖДОГО ЧИСЛА) У МЕНЯ ПРОБЛЕМА, ФОРМУЛА; =СЧЁТЕСЛИ(ТаблАрхивТиражей[Числа];ТЕКСТ([@Число];"\*00\*")) УПОРНО ДАЕТ ЗНАЧЕНИЯ, НАЧИНАЯ С 11 СТРОКИ И ДАЛЕЕ (ЧИСЛО 11), А ЧИСЛА (1 – 10) ДАЕТ 0. Я ПРОЧИТАЛ ОБ (УМНЫХ ТАБЛИЦАХ) ИНФОРМАЦИЮ И У ВАС И У МНОГИХ ДРУГИХ СПЕЦИАЛИСТОВ, ПРОСМОТРЕЛ И ВАШ ВИДЕОРОЛИК, И ДРУГИЕ РОЛИКИ И НЕ НАШЕЛ РЕШЕНИЯ ВОЗНИКШЕЙ ПРОБЛЕМЫ. БЫЛ БЫ БЛАГОДАРЕН ВАМ, ЕСЛИ БЫ ВЫ ПОМОГЛИ МНЕ РЕШИТЬ ЕЕ, ОТПРАВИВ МНЕ ПРАВИЛЬНОЕ РЕШЕНИЕ ИЛИ УКАЗАВ МНЕ, ГДЕ Я МОГ СОВЕРШИТЬ ОШИБКУ. (МОЖЕТ ОНА В НАСТРОЙКАХ МОЕГО EXCEL ИЛИ В ФОРМАТИРОВАНИИ ЯЧЕЕК)???. Я СТАРАЛСЯ ДЕЛАТЬ ВСЕ СКУРПУЛЕЗНО ПО ВАШИМ РЕКОМЕНДАЦИЯМ.
УВАЖЕНИЕМ
ЮРИЙ
29.05.2022
Вопрос следующий. Можно ли вывести в определенную ячейку название "умной таблицы", под которым она названа в Конструкторе?
Формулой СЦЕП это выполняется только для одной колонки и результат размещается в другую колонку. А как бы сделать разом на много колонок и результат на том же месте получить?