Видео
Постановка задачи
Имеем таблицу, с которой постоянно приходится работать (сортировать, фильтровать, считать по ней что-то) и содержимое которой периодически изменяется (добавляется, удаляется, редактируется). Ну, хотя бы, для примера — вот такого вида:
Размер — от нескольких десятков до нескольких сотен тысяч строк — не важен. Задача — всячески упростить и облегчить себе жизнь, превратив эти ячейки в «умную» таблицу.
Решение
Выделяем любую ячейку в таблице и на вкладке Главная (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 не было и этого.
Умные таблицы появились в Excel, начиная с 2007 версии. Это привычные многим полосатые таблицы, которые выглядят примерно так:
И несмотря на то, что их используют уже много лет, еще остаются пользователи, которые используют этот крутой инструмент только потому что “красиво” или не используют совсем. Давайте разберемся, какие преимущества дают умные таблицыв Excel, помимо приятно-полосатого внешнего вида. Но сначала узнаем, как ее создать.
- Как создать умную таблицу в Excel
- Плюсы и минусы умной таблицы в Excel
- Форматирование умной таблицы
- Добавляем срезы (фильтры) к умной таблице
- Как убрать умную таблицу
Чтобы из обычной таблицы Excel сделать “умную” таблицу, нужно:
1. Щелкнуть на любой ячейке таблицы.
Обратите внимание, что всю таблицу выделять не обязательно — если в таблице нет пустых строк или столбцов, Excel сам определит границы диапазона.
2. Нажать сочетание клавиш Ctrl + T. Вокруг таблицы появится рамочка- выделение и окно Создать таблицу, в котором указан выделенный диапазон
Если вдруг это выделение не захватило какую-то нужную область, можно выделить область таблицы вручную.
Обратите внимание на предыдущую картинку — если ваша таблица уже имеет заголовки (“шапку”), то обязательно должна быть установлена “галочка” Таблица с заголовками. Иначе заголовки будут добавлены автоматически в формате Столбец1, Столбец2 и т.д.
Если таблица не имеет заголовков, то “галочку” Таблица с заголовками лучше снять и уже потом переименовать заголовки. Иначе в качестве заголовков будут назначены значение из первой строки.
3. Нажать Ок или клавишу Enter. Готово, у нас получилось преобразовать обычный диапазон в умную таблицу Excel.
Также есть второй способ создания умной таблицы.
1. Также щелкаем в любой ячейке таблицы.
2. Вкладка Главная — Блок Стили — Форматировать как таблицу — в Выпадающем окне выбираем стиль таблицы.
3. Далее также смотрим, чтобы выделение захватило весь нужный диапазон и была установлена галочка Таблица с заголовками и нажимаем Ок.
Плюсы и минусы инструмента умные таблицы в Excel
Преимущества инструмента Умные таблицы в Excel
1. Таблица автоматически расширяется при добавлении строк или столбцов.
Главное требование при этом — новый столбец или строка должны добавляться “впритык” к таблице — без пропусков строк или столбцов.
2. Формулы, введенные в ячейку, после нажатия Enter автоматически копируются на весь столбец.
3. Автоматически добавляются автофильтры ко всем столбцам. И на столбцы, добавленные уже после создания таблицы, фильтры также “навешиваются” автоматически. При этом фильтр можно убрать (см. в разделе Форматирование умной таблицы)
При прокрутке таблицы строка с заголовками и фильтрами закреплена вверху страницы, даже если вы отдельно не закрепляли области.
4. Умные таблицы в Excel имеет имя, которое присваивается автоматически, но его можно изменить.
Посмотреть и изменить имя умной таблицы в Excel можно на вкладке Конструктор таблиц (при этом курсор должен быть установлен на любой ячейке в таблице, иначе вкладка Конструктор таблиц не появится на ленте)
5. К умной таблице можно добавить срезы, такие же, как для сводной таблицы. Срезы в умной таблице упрощают ее фильтрацию и делают более отчет более профессиональным.
6. Структурированные формулы, которые легко читать.
В формулах внутри таблицы используются не привычные адреса ячеек и диапазонов, а специальные адреса, отражающие называние столбца или области внутри таблицы. Это облегчает “чтение” формулы (если, конечно, столбцам в таблице были даны релевантные названия).
7. Автоматическое добавление итогов к таблице с возможностью выбора вида итога (сумма, среднее и т.д.).
8. Удобно создавать сводные таблицы на основании умных таблиц. При добавлении новых столбцов или строк в исходную таблицу, они автоматически добавляются в сводную таблицу (после ее обновления) без необходимости настраивать диапазон.
9. Умные таблицы в Excel удобно загружать в Power Query для последующей обработки.
10. Ну и конечно, красивый “полосатый” внешний вид без “танцев с бубнами” с условным форматированием.
Еще одно преимущество “внешности” — легкая настройка форматирования умной таблицы в Excel. На вкладке Конструктор таблиц его можно быстро настроить (см. раздел Форматирование умной таблицы)
Но, конечно, “умные” таблицы в Excel имеют и ряд особенностей, которые могут затруднить их использование.
Минусы инструмента Умные таблицы в Excel
- При достаточно большом размере таблицы и большом количестве формул файл с умной таблицей может тормозить. Это связано с тем, что форматирование умной таблицы сделано по типу условного форматирования, которое “нагружает” файл.
- Если добавить строку итогов, то функция автоматического расширение «умной таблицы» по строкам при добавлении данных перестает работать. Это логично, т.к. не могут же новые строки с данными быть после строки итогов. Расширить таблицу в этом случае можно только растягиванием за уголок.
- Заголовки можно располагать только в первой строке умной таблицы. Это лишает возможность делать сложные «шапки» таблиц, а также использовать одинаковые названия столбцов — если столбцы будут называться одинаково, Excel автоматически добавить к ним цифру 2, 3 и т.д.
- Формулы могут становятся очень длинными, поскольку в формулу прописывается наименование столбца. Особенно если заголовки длинные сами по себе. Этот момент ограничивает возможность давать сложные названия столбцам.
- Нет возможности закрепить ячейки или столбцы абсолютными/относительными ссылками, и при “протягивании” формулы ссылки съезжают.
- Нельзя установить режим совместного доступа в файл, который содержит хотя бы одну “умную” таблицу.
Форматирование умной таблицы
Можно достаточно гибко настраивать форматирование умной таблицы в Excel.
Настройка внешнего вида умной таблицы
- Откроем вкладку Конструктор таблиц (при этом должна быть активна любая ячейка внутри таблицы)
- В блоке Стили таблиц можно выбрать подходящий стиль из выпадающего списка.
Также можно создать собственный стиль таблицы умной таблицы в Excel.
Вкладка Конструктор таблиц — блок Стили таблиц — выпадающий список — Создать стиль таблицы. И далее поочередно настроить форматирование для каждого элемента таблицы. Для этого нужно выбрать элемент, нажать на кнопку Формат и выбрать нужные параметры заливки, шрифта и т.д.
К тому же, никто не запрещает настраивать внешний вид таблицы вручную. Например, сделать заливку определенного заголовки или ячейки другим цветом.
Также можно очистить стиль умной таблицы.
Это отличное решение для таблиц, которые используются в промежуточных расчетах, и находятся не “на виду”, поскольку отсутствие стиля облегчает работу файла.
Для этого нужно щелкнуть на любой ячейке таблицы — вкладка Конструктор таблиц — блок Стили таблиц — выпадающий список — Очистить.
Теперь таблица не имеет форматирование. Но она не перестала быть умной таблицей! Обратите внимание, что у нее так же есть границы и имя, следовательно, это такая же умная таблица, просто не цветная.
Параметры стиля умных таблиц в Excel
Если вы выбрали предустановленный стиль умной таблицы в Excel, то можно сделать его поднастройку. На вкладке Конструктор таблиц в блоке Параметры стилей таблиц можно настроить параметры установкой/снятием “галочек”
- Строка заголовков: включает и отключает заголовки в таблице
- Строка итогов: включает/отключает строку итогов.
- Чередующиеся строки — включает/отключает “полосатость” таблицы, все строки становятся бесцветными.
- Чередующиеся столбцы — включает/отключает чередования цвета столбцов. Со включенным параметром столбцы окрашиваются цветом “через один”
- Первый столбец и Последний столбец — включает/отключает жирный шрифт для первого и последнего столбца в таблице.
- Кнопка фильтра — включает/отключает автофильтр.
Как изменить имя умной таблицы в Excel
Также на вкладке Конструктор таблиц можно изменить имя таблицы. По умолчанию Excel присваивает таблицам имена типа Таблица1, Таблица2… Однако, лучше их менять на более релевантные, это удобно для использования в формулах.
Чтобы изменить имя умной таблицы, заходим на вкладку Конструктор таблиц — Блок Свойства — в поле Имя таблицы пишем новое имя.
Добавляем срезы (фильтры) к умной таблице
Срезы в умных таблицах выглядят аналогично срезам в сводных таблицах. Они упрощают фильтрацию таблиц.
Чтобы добавить срез к умной таблице в Excel, нужно перейти на вкладку Конструктор таблиц (должна быть активна любая ячейка внутри таблицы) — блок Инструменты — Вставить срез.
Далее нужно отметить галочками, срезы по каким столбцам нужны. Для примере выберем срезы по Дате и Адресу магазина.
Срезы добавлены. Теперь можно фильтровать данные в умной таблице Excel с их помощью.
Как убрать умную таблицу
Иногда нужно преобразовать умную таблицу обратно в диапазон — это значит, что нужно убрать умную таблицу и оставить просто данные.
Для этого переходим на вкладку Конструктор таблиц — блок Инструменты — Преобразовать в диапазон
В появившемся окне нажимаем кнопку Да.
Таблица не изменила свой внешний вид — но она перестала быть умной таблицей (т.е. лишилась всех ее функций — см. раздел Плюсы и минусы умной таблицы в Excel).
В этой статье мы узнали, что такое умные таблицы в Excel, а также познакомились с особенностями таких таблиц.
Вам может быть интересно:
Создать «Умную таблицу», которая впервые была представлена в Excel 2007, очень просто. Для ввода данных в нее можно использовать автоформу, которая защитит формулы от случайной перезаписи. В Excel 2010 для «Умной таблицы» был добавлен инструмент «Срезы» для упрощения фильтрации данных.
Общие правила создания и заполнения таблиц
Чтобы ваша таблица, «обычная» или «умная», правильно работала, следует придерживаться при ее создании и заполнении следующих несложных правил:
- не использовать в таблице объединенные ячейки;
- присваивать графам (колонкам) уникальные имена;
- в одной графе использовать один формат данных;
- не оставлять при заполнении таблицы пустых строк;
- в «обычной» таблице следует выделить строку заголовков (шапку таблицы) форматированием (цвет, шрифт) и закрепить на начальном экране.
Создание «Умной таблицы»
Для создания «Умной таблицы» необходимо выбрать любую ячейку внутри таблицы без форматирования или выделить произвольный диапазон, в котором планируется создать такую таблицу, и нажать кнопку «Форматировать как таблицу» на вкладке «Главная». Откроется окно выбора формата будущей «Умной таблицы»:
Выбрать можно любой образец форматирования таблицы и нажать на него, а после создания «Умной таблицы» точнее подобрать форматирование с помощью предпросмотра. После нажатия на образец формата программа Excel предложит проверить диапазон будущей таблицы и выбрать, где будет создана строка заголовков (шапка таблицы) — внутри таблицы, если она уже с заголовками, или над таблицей в новой строке:
В примере заголовки уже присутствуют внутри диапазона с таблицей, поэтому галочку «Таблица с заголовками» оставляем. Нажав «OK», получим следующую «Умную таблицу»:
Теперь при записи формулы создаются адреса с именами колонок, а при нажатии «Enter» формула автоматически копируется во все ячейки этой графы:
Адреса с именами колонок создаются, если аргументы для формулы берутся из той же строки. Аргументы, взятые из других строк, будут отображены обычными ссылками.
Когда «Умная таблица» уже создана, подобрать для нее подходящее цветовое оформление становится легче. Для этого нужно выбрать любую ячейку внутри таблицы и снова нажать кнопку «Форматировать как таблицу» на вкладке «Главная». При наведении курсора на каждый образец форматирования, «Умная таблица» будет менять цветовое оформление в режиме предпросмотра. Остается только выбрать и кликнуть на подходящем варианте.
При выборе любой ячейки внутри «Умной таблицы» на панели инструментов появляется вкладка «Работа с таблицами Конструктор». Перейти в нее можно, нажав на слово «Конструктор».
На вкладке «Конструктор» отображены все инструменты для работы с «Умной таблицей» (неполный перечень):
- редактирование имени таблицы;
- изменение цветового чередования строк на цветовое чередование столбцов;
- добавление строки итогов;
- удаление кнопок автофильтра;
- изменение стиля таблицы (то же, что и по кнопке «Форматировать как таблицу» на вкладке «Главная»);
- удаление дубликатов;
- добавление срезов*, начиная с Excel 2010;
- создание сводной таблицы;
- удаление функционала «Умной таблицы» командой «Преобразовать в диапазон».
*Срезы представляют из себя удобные фильтры по графам в отдельных окошках, работающие аналогично кнопкам автофильтра в строке заголовков. Создается срез (или срезы) нажатием кнопки «Вставить срез» и выбором нужной колонки (или колонок). Чтобы удалить срез, его нужно выбрать и нажать на клавиатуре «Delet» или пункт «Удалить (имя среза)» в контекстном меню.
Работа с «Умной таблицей»
Начните заполнять новую строку или новый столбец, и после заполнения первой ячейки «Умная таблица» автоматически расширится на эту строку или на этот столбец. Если в таблице использовались формулы, то при добавлении новой строки они будут автоматически в нее скопированы.
Для заполнения «Умной таблицы» можно использовать автоформу, которая позволит защититься от случайной перезаписи формул и сделает заполнение более наглядным.
Чтобы автоформа всегда была доступной, ее кнопку необходимо добавить на панель быстрого доступа (вверху слева). Для непосредственного перехода к настройке панели быстрого доступа нажмите на правую кнопку этой панели в виде черточки с треугольником внизу. Откроется следующее меню:
В этом меню необходимо выбрать пункт «Другие команды…» для перехода к окну настройки панели быстрого доступа:
Здесь необходимо выбрать в верхнем раскрывающемся списке «Команды не на ленте», в окне со списком команд выделить пункт «Форма…», нажать кнопку «Добавить >>», а затем кнопку «OK». На панели быстрого доступа появится кнопка вызова автоформы.
Автоформа будет вызываться кнопкой на панели быстрого доступа для той «Умной таблицы», внутри которой находится активная ячейка.
Обратите внимание, что ячейка с формулой не редактируется. Добавление новой записи или сохранение внесенных изменений в существующую строку происходит при нажатии кнопок «Добавить», «Закрыть» или «Enter» на клавиатуре. При нажатии кнопок «Назад» или «Далее» внесенные изменения не сохраняются.
Любую таблицу желательно максимально автоматизировать формулами. Например, в таблице из примера можно вставить формулу для увеличения порядкового номера на единицу.
Самым простым способом было бы вставить формулу =[предыдущая ячейка]+1, но она не копируется автоматически в новую запись. А в ячейках с этой формулой программа Excel выводит замечание: «Несовместимая формула в вычисляемом столбце».
Для нашего примера подойдет следующая формула: =СТРОКА([@Наименование])-1, которая вычисляет номер текущей строки рабочего листа Excel и уменьшает ее на единицу, так как первая запись в нашей «Умной таблице» начинается со второй строки рабочего листа Excel.
Если в отдельных колонках «Умной таблицы» использовать выбор значений из раскрывающихся списков, то добавлять записи придется путем копирования строк или протягиванием. Иначе, при автоматическом добавлении новой записи, формулы проверки данных для раскрывающихся списков скопированы не будут.
Преимущества «Умной таблицы»
К преимуществам «Умной таблицы» можно отнести следующие:
- автоматическое форматирование по выбранному шаблону;
- автоматическое расширение «Умной таблицы» при добавлении новых записей или колонок;
- автоматическое копирование формул в новую запись;
- возможность добавления строки итогов (критерий расчета итога для каждой отдельной графы можно выбрать из раскрывающегося списка);
- возможность использования автоформы* для добавления новых записей и редактирования существующих (такая же возможность существует и для обычных таблиц).
*При использовании автоформы не работают подсказки для ввода повторяющегося текста, при наличии которого удобнее добавлять записи непосредственно в таблицу.
Всем привет! Представим себе ситуацию, что вы создали таблицу в Excel, добавили все данные и формулы, изменили цвет и границы заливки, чтобы было удобно различать столбцы и ячейки. Проблема использования подобной таблицы в том, что если вам нужно добавить дополнительные строки снизу, то вам придется по новой форматировать ячейки и добавлять формулу – вручную или с помощью маркера автозаполнения.
Читаем – что такое маркер автозаполнения.
Одно дело, если количество столбцов и свойств не так много, но вот с большими данными у вас могут возникнуть проблемы. Да и времени вы на это будете тратить куда больше. Именно для этих целей и придумали «Умные таблицы». Умная таблица – это целый объект, объединения ячеек, в которой сохраняется постоянное форматирование и расчет в формулах по выделенным столбцам и ячейкам. Также там есть постоянная шапка, а все расчеты подтягиваются из соседних смежных строк. В сегодняшнем уроке мы посмотрим, как проще всего сделать умную таблицу в Excel.
Содержание
- Как создать умную таблицу в Excel
- Добавление новых строк и столбцов
- Работа с формулами
- Строка итогов
- Сортировка
- Как убрать умную таблицу Excel
- Видео
- Задать вопрос автору статьи
Для создания умной таблицы вам нужно выбрать нужный диапазон ячеек, который уже заполнен. Самое главное, чтобы у этих ячеек уже были заголовки, и они были уникальны – это важно для работы подобного элемента в Excel. После этого перейдите на вкладку «Главная». Далее у нас есть два способа создания умной таблицы.
Первый способ – найдите раздел «Стили» и кликните по кнопке «Форматировать как таблицу».
Выберите стили, которые вам нравится.
Если же вы хотите сохранить цвета, границы и другие стили вашей таблички, перейдите на вкладку «Вставка» и нажмите по кнопке «Таблица». Вы можете воспользоваться и быстрыми клавишами:
Ctrl + T
В обоих случаях вылезет окошко создания таблицы, где будет указан диапазон преобразования. Если в вашей табличке нет заголовков, уберите галочку и нажмите «ОК». После этого будет создана полноценный объект, который будет существовать в программе, как отдельная структура со своими формулами расчета и форматированием.
Добавление новых строк и столбцов
Для того чтобы расширить сетку со всем нужным форматированием, наведите курсор мыши на правый нижний угол, зажмите левую кнопку мыши и тяните границу вниз.
Таким образом можно расширить табличку на любое количество ячеек. Это очень удобно, если вы работаете с большим количеством данных и столбцов.
Если же вы попытаетесь расширить таблицу в право, то увидите создание нового столбца. Вы можете переименовать шапку, заполнить данные или ввести формулы для расчета.
Читаем – как добавить строку в Excel.
Работа с формулами
Еще один момент при работе с умной таблицей в том, что вы можете оперировать не отдельными ячейками, а целыми столбцами. Например, в моем случае мне нужно в столбце «Прибыль» посчитать произведение «Цены» и количество «Всего» проданного товара. Мы можем использовать формулу:
=B2*C2
Или можем воспользоваться перемножением целых столбцов. Например, если вы попробуете начать вводить формулу, при клике левой кнопкой мыши по адресу, будет выделяться весь столбец:
=[@Цена]*[@Всего]
В таком случае вам не нужно использовать маркер автозаполнения, и данные автоматически посчитаются для всех столбцов.
Строка итогов
В умную таблицу можно вставить «Строку итогов» – для этого кликните по любой ячейке таблички, перейдите на вкладку «Конструктора» и поставьте галочку «Строка итогов».
В самом низу автоматически будет добавлена строка. Для каждой ячейки вы можете выбрать свою формулу подсчета. Например, можно посчитать среднее, минимальное или максимальное значение по всем ячейкам столбца. Также можно добавить общую сумму или использовать другую формулу, которую вы можете создать вручную.
Сортировка
Еще одно удобство – это быстрая возможность отсортировать данные по любому столбцу. Для этого достаточно нажать по названию в самом вверху. Далее выбираем ту сортировку, которая вам нужна.
Как убрать умную таблицу Excel
Чтобы преобразовать умную таблицу в обычный вариант в Excel – выделите её и перейдите на вкладку «Конструктора» и в разделе «Инструменты» нажмите по кнопке «Преобразовать в диапазон».
Вам останется подтвердить действие, и табличка вновь станет обычной.
Вроде бы ничего не забыл. Если я все же что-то упустил или вам нужна моя консультация и помощь – опишите подробно свою проблему в комментариях. Всем добра и до новых встреч на портале WiFiGiD.
Видео
О чем речь? Умная таблица в Excel – это особый способ форматирования, который позволяет получить больше возможностей для работы с упорядоченными данными. С ее помощью вы быстрее найдете нужную информацию, а вычисления станут гораздо проще.
Как сделать? Создать умную таблицу не сложно, необходимо нажать буквально пару кнопок. Больше проблем доставляют настройка и форматирование, но, используя нашу инструкцию, вы без проблем справитесь с этой задачей.
В статье рассказывается:
- Суть умной таблицы в Excel
- Задачи и правила работы с умной таблицей
- Создание умной таблицы в Excel
- Настройка умной таблицы в Excel
-
Пройди тест и узнай, какая сфера тебе подходит:
айти, дизайн или маркетинг.Бесплатно от Geekbrains
Умная таблица – это способ форматирования в программе Microsoft Excel, который впервые появился в версии 2007. Суть его в том, что программный алгоритм позволяет обрабатывать данные из нескольких ячеек, расположенных рядом друг с другом, как единый элемент. Если пользователь внесет данные в произвольную ячейку столбца или колонки, располагающихся непосредственно у границы, то эти данные включаются в соответствующий табличный диапазон
Этот вид форматирования имеет отдельные ограничения. В частности, функция объединения ячеек в этом случае может работать некорректно. В первую очередь, эта особенность касается шапки, для которой объединение невозможно в принципе. Также, нередки ситуации, когда нет необходимости включать данные, размещенные в ячейке, находящейся у границ табличного диапазона, в общий массив, однако программа все равно выполнит эту операцию.
В этой связи нужно текстовые данные вводить хотя бы после одного пустого диапазона от массива. Не получится воспользоваться в режиме умной таблицы и функциями формул, станет невозможным совместный просмотр и редактирование книги. Во избежание лишних неполадок нужно следить за именами столбцов, заголовки которых не должны совпадать.
Задачи и правила работы с умной таблицей
Постоянное использование Excel приводит к тому, что при использовании данного приложения необходимо придерживаться определенных требований, позволяющих оптимально использовать возможности программы.
- Каждый столбец должен иметь заголовок, не совпадающий с названиями других столбцов.
- Исключать столбцы и строки, не содержащие данных.
- В одной колонке использовать данные только в одном формате. Например, если в столбце вносятся данные о количестве проданных товаров в штуках, в нем не должны содержаться значения, отражающие сумму продаж в денежном выражении.
Скачать файл
- Избегать объединения ячеек, когда можно обойтись другими способами представления данных.
- Обеспечивать единообразное форматирование таблицы. Необходимо отмечать отдельные разделы, использовать выделение цветом и т.п.
- Использовать закрепление областей, чтобы заголовок не «убегал» из области обзора.
- Устанавливать опцию фильтра по умолчанию.
- Использовать строку промежуточного итога.
- Внимательно размещать относительные и абсолютные ссылки. Тогда их можно будет протягивать, не внося изменений.
Читайте также
Excel позволяет отметить цветом любое количество столбцов и строк. Однако не стоит этого делать, если в таблицу входит десять колонок и двадцать строк. Конечно, удобнее единожды кликнуть по заголовку любой из них и закрасить неограниченный диапазон, но в этом случае размер файла может увеличиться очень значительно. Это создаст неудобства при необходимости передачи книги по сети.
Вышеприведенные правила достаточно просты. Их соблюдение поможет оптимизировать работу и потратить время на освоение сложных полезных опций, а не на приведение в порядок бездумно составленной таблицы, в которой многие важные функции оказываются неработоспособными.
Excel 13 версии (Excel 2007) учитывает эти требования и предлагает пользователю опцию «умной таблицы», которая значительно облегчает или полностью автоматизирует их выполнение. Опытным юзерам эта функция сильно облегчает работу.
Создание умной таблицы в Excel
Чтобы включить режим «умной таблицы» в Excel, необходимо сделать следующее.
- В окне Excel, в котором открыта ранее сохраненная таблица или введены нужные данные, открываем вкладку «Главная», находим на ней раздел «Стили». В нем имеется кнопка «Форматировать как таблицу».
- На экране отобразится окно, где будет написан адрес таблицы, к которой применяется рассматриваемая функция. Если адрес выбран правильно, нужно кликнуть по кнопке «ОК».
- Для удобства не будет лишним задать для нее уникальное название, это поможет избежать путаницы. Для этого нужно кликнуть по любой ячейке выбранного диапазона и открыть вкладку «Конструктор». На ней есть раздел «Свойства», где помимо названия таблицы можно также изменить и ее размер.
Форматирование таблицы по размерам позволяет выставить маркер. Перемещая его, можно изменить формат таблиц целиком, не искажая данных и сохраняя удобство просмотра.
Настройка умной таблицы в Excel
Фильтры и сортировка
В заголовке таблицы по умолчанию присутствует значок фильтра. Если эта опция не требуется, значок можно отключить. Для этого нужно перейти на вкладку «Конструктор» и перевести соответствующий чек-бокс в нужное положение.
Фильтр предоставляет широкие возможности для операций, которые выполняются с частью данных в столбце. Можно сделать сортировку, скрыть ненужные элементы. Например, если интересуют продажи, которые были осуществлены в декабре, можно легко отключить отображение торговых операций, относящихся к остальным 11 месяцам года.
В больших массивах данных количество ячеек, данные в которых попадают в используемый фильтр, может оказаться очень большим. В этом случае поможет поиск, с которым можно вывести только значения, соответствующие поисковому запросу.
При большом объёме данных таблица может не пометиться целиком в один экран просмотра. В этом случае «умная таблица» позволит закрепить заголовки столбцов в верхней части и использовать опции фильтра при первой необходимости. Не нужно прокручивать книгу обратно, чтобы добраться до нужного чек-бокса.
Автоматическая шапка умной таблицы
Умная таблица в Excel позволяет сделать не только стандартную фильтрацию данных, но и срез по заданному критерию, также представляющий собой разновидность фильтра. К примеру, пользователю необходимо последовательно просмотреть данные о по разным группам товара. Можно отмечать в заголовке каждую из них по отдельности, но это не очень удобно.
Топ-30 самых востребованных и высокооплачиваемых профессий 2023
Поможет разобраться в актуальной ситуации на рынке труда
Подборка 50+ ресурсов об IT-сфере
Только лучшие телеграм-каналы, каналы Youtube, подкасты, форумы и многое другое для того, чтобы узнавать новое про IT
ТОП 50+ сервисов и приложений от Geekbrains
Безопасные и надежные программы для работы в наши дни
Уже скачали 20537 
После применения опции «Срез» отображается панель, в которой перечислены все уникальные значения из соответствующей колонки. Клик по любой из этих групп отобразит данные только по ней и скроет сведения по остальным.
Строка итогов
В разделе «Стили» можно найти опцию строки итогов. Она позволяет отобразить суммарное значение данных, внесенных в соответствующий столбец. При наведении курсора мыши на это значение отображается кнопка вызова контекстного меню с доступными инструментами.
Подсчётом суммы виды итогов не ограничиваются. Можно надстроить их показ в виде количества значений, максимальной и минимальной величины, а также в соответствии с произвольной формулой, заданной пользователем.
Вставка итоговых величин для колонок умной таблицы
Режим фильтров или срезов показывает итог по отображаемым на экране значениям. Это позволяет оперативно получить обобщенные данные по выбранным позициям.
Авторасширение таблицы
Полезной опцией умных таблиц является способность автоматически обрабатывать вновь набранные данные. Так, если ввести их в колонке, непосредственно граничащей с умной таблицей, то она расширится до этих данных с использованием к ним аналогичных параметров форматирования.
Аналогичные возможности предусмотрены и для строки. При этом, если в одной из колонок применяется формула, в нее будет автоматически включено значение из новой строки.
Также расширить или сжать умную таблицу в Excel можно вручную. Для этого требуется захватить курсором мыши маркер в правом нижнем углу заданного массива и перетянуть его в нужное место.
Перемещение строк или колонок
Одна из наиболее полезных опций умной таблицы в Excel – это возможность произвольно перемещать колонки и строки внутри таблицы. Для этого нужно навести указатель мыши на заголовок столбца, а точнее – на границу ячейки. Курсор изменит свой вид. После этого нужно нажать левую копку и поместить колонку в нужное место.
Читайте также
Если нужно переместить строку, то порядок действий аналогичен.
Поскольку в режиме умной таблицы массив данных выделяется в автоматическом режиме, это предотвращает случайное изменение записей, не относящихся к таблице и расположенных в других ячейках.
Удаление дубликатов
Поскольку умная таблица представляет собой элемент, состоящий из массива данных, не требуется для совершения какого-то действия с таблицей выделять всю их совокупность. Достаточно поставить курсор на любую ячейку, которая относится к соответствующему массиву.
В автоматическом режиме можно найти и удалить дубликаты, содержащиеся в столбце. Для этого нужно сначала указать нужный инструмент на вкладке Конструктор, а затем указать колонку, в которой нужно выполнить поиск и удаление задвоенных данных.
При этом очистка от дубликатов оставит нетронутыми записи, которые могут располагаются рядом с умной таблицей.
Этот инструмент позволяет, например, легко вывести перечень уникальных значений, содержащихся в таблице. Впрочем, для этой задачи можно использовать и другие опции, в частности, сводные таблицы.
Сводные таблицы
На панели инструментов «Конструктор» имеется опция создания сводных таблиц, для которой используются значения из умной таблицы. В этом случае в поле сводной таблицы будет указываться не массив ячеек, а назначенное пользователем название умной таблицы, например, «сделки в первом квартале».
Когда в умную таблицу вносятся новые записи, они тут же автоматически учитываются и в сводной таблице. Таким образом, отпадает необходимость расширять диапазон каждый раз вручную.
Единственное, что необходимо сделать – это обновить сводную таблицу, чтобы учесть изменения в умной таблице.
Только до 27.04
Скачай подборку тестов, чтобы определить свои самые конкурентные скиллы
Список документов:
Тест на определение компетенций
Чек-лист «Как избежать обмана при трудоустройстве»
Инструкция по выходу из выгорания
Чтобы получить файл, укажите e-mail:
Подтвердите, что вы не робот,
указав номер телефона:
Уже скачали 7503
Описанная опция упрощает работу в том плане, что пользователю не нужно все время концентрировать внимание на диапазоне значений, из которых формируется таблица. Новые записи будут учитываться автоматически.
Аналогичные инструменты можно применять и к диаграммам.
Диаграммы и графики
Excel позволяет создавать наглядные диаграммы. Они составляются на основе заданного пользователем массива данных. При этом указание в этом массиве новых записей не отразится в диаграмме автоматически, для этого необходимо настроить ее соответствующим образом.
Режим умных таблиц позволяет автоматически подхватывать вновь введённые данные и отобразить их в разделах диаграммы. Это освобождает пользователя от необходимости самостоятельно следить за изменениями диапазона.
Многочисленные достоинства умных таблиц вовсе не означают, что они подходят для всех задач без исключения. Так, они не потребуются для работы со стандартным формами, в которых присутствуют многострочные заголовки с объединёнными ячейками. Если же стоит задача быстро и эффективно обработать массив данных по определённому алгоритму, то для этого умные таблицы предлагают целый ряд полезных опций.