Excel online самоучитель умная таблица

Как нам это может пригодиться?

Часто случается так, что в какой-то из колонок вашей таблицы нужно вводить одинаковые повторяющиеся значения. К примеру, фамилии сотрудников, названия товаров. Что может случиться? Конечно, в первую очередь будут ошибки при вводе. Человеческий фактор ведь никто не отменял. Чем нам сие грозит? К примеру, когда мы решим подсчитать, сколько заказов выполнил каждый из менеджеров, то окажется, что фамилий больше, чем сотрудников. Далее придётся искать ошибки, исправлять их и вновь повторять расчет.

Ну и конечно же, все время руками вводить одни и те же слова – просто бессмысленная работа и потеря времени. Вот здесь-то выпадающие списки нам и пригодятся. При нажатии выпадает перечень заранее определённых значений, из которых необходимо указать только одно.

Важно то, что вы теперь будете не вводить, а выбирать их с помощью мыши или клавиатуры. Это значительно ускоряет работу, а также гарантирует защиту от случайных ошибок

Проверка того, что мы вписали в таблицу, теперь уже не нужна.

ПРИЕМ #1. Изменение размера

Обычно умная таблица создается на основе некоторого диапазона ячеек.

Создание умной таблицы

При этом размер таблицы Эксель определяет автоматически, основываясь на заполненности ячеек данными.

Если в массиве данных есть пустая строка, то диапазон может быть создан неверно. В этом случае его можно откорректировать вручную. Для этого захватываем мышью нижний правый край таблицы и перемещаем его вниз. При необходимости мы можем уменьшить таблицу, исключив из нее строки точно таким же образом.

Изменение размера таблицы

Если массив данных значителен, то изменять размеры таблицы мышью будет неудобно. Проще откорректировать размер таблицы вручную с помощью диалогового окна, которое можно открыть через контекстную вкладку Конструктор.

Изменение размеров таблицы через вкладку Конструктор

Как в Экселе сделать обычную или умную таблицу

Создание таблиц в специальных программах, текстовых или графических редакторах, значительно упрощает восприятие текста, который имеет любые числовые данные. И в каждой программе есть свои особенности. В статье мы рассмотрим, как сделать таблицу в программе Эксель.

По сути, рабочий лист Эксель изначально представлен в виде таблицы. Он состоит из большого количества ячеек, у которых есть свой определенных адрес – название столбца и строки. Например, выделим любой блок, находится он в столбце В, в четвертой строке – адрес В4. Он также указан в поле «Имя».

При выводе на печать рабочих листов Excel с данными, не будут напечатаны ни границы ячеек, ни названия строк и столбцов. Именно по этому, имеет смысл разобраться, как создать таблицу в Excel, чтобы данные в ней были ограничены и разделены.

В дальнейшем, ее можно будет использовать для построения диаграмм и для отображения информации в виде графика по имеющимся данным.

Делаем шапку

Начнем с создания шапки. Введите нужные названия для столбцов. Если они не помещаются и налазиют друг на друга, ячейки можно расширить. Для этого переместите курсор к названию столбца, он примет вид черной стрелочки, направленной в разные стороны, и передвиньте на нужное расстояние.

Подробнее про это можно прочесть в статье: как сделать заголовки в таблице Эксель.

Еще один способ, чтобы поместить текст в ячейке – перенести его. Выделите блок с текстом и на вкладке «Главная» в группе «Выравнивание» нажмите на кнопочку «Перенос текста».

Теперь введем нужные данные. В D8 я воспользовалась переносом текста, давайте сделаем, чтобы он был виден полностью и ячейки были одинаковой высоты. Выделяем ячейки в нужном столбце, и на вкладке «Главная» в группе «Ячейки» кликаем на кнопочку «Формат». Выберите из списка «Высота строки».

В диалоговом окне введите подходящее значение. Если не обязательно, чтобы строки были одинаковые по высоте, можно нажать на кнопочку «Автоподбор высоты строки».

Создаем границы

На вкладке «Главная», в группе «Шрифт» и «Выравнивание», Вы найдете кнопки, для форматирования таблицы. Здесь же будет кнопка для создания границ. Выделите диапазон ячеек, нажмите на черную стрелочку возле кнопки и выберите из списка «Все границы».

Вот так быстро мы сделали таблицу в программе Эксель.

Если таблица очень большая и нужно, чтобы названия заголовков были всегда видны, можно закрепить их. Об этом написано в статье: как закрепить область в Эксель.

Сделать таблицу в Excel можно и с помощью встроенного редактора. В этом случае, она будет называться умной.

Выделим нашу таблицу целиком вместе с шапкой и данными. Затем на вкладке «Главная» в группе «Стили» нажмите на кнопочку «Форматировать как таблицу». Из списка выберите подходящий стиль.

Появится диалоговое окно, в котором будет указан нужный диапазон ячеек. Поставьте галочку в поле «с заголовками». Нажмите «ОК».

Таблица примет вид в соответствии с выбранным стилем. У меня этого не произошло, поскольку до этого я отформатировала выбранный диапазон ячеек.

Теперь уберу границы и заливку для названия столбцов – те параметры, которые выбрала раньше. В результате отобразится выбранный стиль.

Если помните, мы сделали умную таблицу в Эксель. Для того чтобы добавить в нее новый столбец или строку, начните вводить данные в любую ячейку, которая прилегает к таблице и нажмите «Enter»– она автоматически расширится.

При выделении таблицы, на ленте появляется новая вкладка «Работа с таблицами» – «Конструктор». Здесь можно задать нужное имя, сделать сводную, добавить строку итогов, выделить цветом строки и столбцы, изменить стиль.

Умные таблицы Excel хорошо использовать для построения круговых диаграмм, различных других графиков и для создания выпадающих списков. Так как при добавлении в нее новых данных, они тут же отображаются, например, в виде построения нового графика на диаграмме.

Умную таблицу можно создать и другим способом. Выделите нужный диапазон ячеек, перейдите на вкладку «Вставка» и кликните по кнопочке «Таблица». В появившемся диалоговом окне будет указан выбранный диапазон, поставьте галочку в поле «… с заголовками» и нажмите «ОК».

Вот так, всего в пару кликов, можно сделать обычную или умную таблицу в программе Excel.

КАК СОЗДАТЬ ТАБЛИЦУ В EXCEL ДЛЯ ЧАЙНИКОВ. Пошагово

Работа с таблицами в Excel для чайников не терпит спешки. Создать таблицу можно разными способами и для конкретных целей каждый способ обладает своими преимуществами. Поэтому сначала визуально оценим ситуацию.

Посмотрите внимательно на рабочий лист табличного процессора:

Это множество ячеек в столбцах и строках. По сути – таблица. Столбцы обозначены латинскими буквами. Строки – цифрами. Если вывести этот лист на печать, получим чистую страницу. Без всяких границ.

Сначала давайте научимся работать с ячейками, строками и столбцами.

Как выделить столбец и строку

Чтобы выделить весь столбец, щелкаем по его названию (латинской букве) левой кнопкой мыши.

Для выделения строки – по названию строки (по цифре).

Чтобы выделить несколько столбцов или строк, щелкаем левой кнопкой мыши по названию, держим и протаскиваем.

Для выделения столбца с помощью горячих клавиш ставим курсор в любую ячейку нужного столбца – нажимаем Ctrl + пробел. Для выделения строки – Shift + пробел.

Как изменить границы ячеек

Если информация при заполнении таблицы не помещается нужно изменить границы ячеек:

Передвинуть вручную, зацепив границу ячейки левой кнопкой мыши.

Когда длинное слово записано в ячейку, щелкнуть 2 раза по границе столбца / строки. Программа автоматически расширит границы.

Если нужно сохранить ширину столбца, но увеличить высоту строки, воспользуемся кнопкой «Перенос текста» на панели инструментов.

Для изменения ширины столбцов и высоты строк сразу в определенном диапазоне выделяем область, увеличиваем 1 столбец /строку (передвигаем вручную) – автоматически изменится размер всех выделенных столбцов и строк.

Примечание. Чтобы вернуть прежний размер, можно нажать кнопку «Отмена» или комбинацию горячих клавиш CTRL+Z. Но она срабатывает тогда, когда делаешь сразу. Позже – не поможет.

Чтобы вернуть строки в исходные границы, открываем меню инструмента: «Главная»-«Формат» и выбираем «Автоподбор высоты строки»

Для столбцов такой метод не актуален. Нажимаем «Формат» — «Ширина по умолчанию». Запоминаем эту цифру. Выделяем любую ячейку в столбце, границы которого необходимо «вернуть». Снова «Формат» — «Ширина столбца» — вводим заданный программой показатель (как правило это 8,43 — количество символов шрифта Calibri с размером в 11 пунктов). ОК.

Как вставить столбец или строку

Выделяем столбец /строку правее /ниже того места, где нужно вставить новый диапазон. То есть столбец появится слева от выделенной ячейки. А строка – выше.

Нажимаем правой кнопкой мыши – выбираем в выпадающем меню «Вставить» (или жмем комбинацию горячих клавиш CTRL+SHIFT+»=»).

Отмечаем «столбец» и жмем ОК.

Совет. Для быстрой вставки столбца нужно выделить столбец в желаемом месте и нажать CTRL+SHIFT+»=».

Все эти навыки пригодятся при составлении таблицы в программе Excel. Нам придется расширять границы, добавлять строки /столбцы в процессе работы.

Умные таблицы Excel 2007-2013

Постановка задачи

Имеем таблицу, с которой постоянно приходится работать (сортировать, фильтровать, считать по ней что-то) и содержимое которой периодически изменяется (добавляется, удаляется, редактируется). Ну, хотя бы, для примера — вот такого вида:

Размер — от нескольких десятков до нескольких сотен тысяч строк — не важен. Задача — всячески упростить и облегчить себе жизнь, превратив эти ячейки в «умную» таблицу.

Выделяем любую ячейку в таблице и на вкладке Главная (Home) разворачиваем список Форматировать как таблицу (Format as table) :

В раскрывшемся списке стилей выбираем любой вариант заливки на наш вкус и цвет и в окне подтверждения выделенного диапазона жмем ОК и получаем на выходе примерно следующее:

В результате после такого преобразования диапазона в «умную» Таблицу (с большой буквы!) имеем следующие радости (кроме приятного дизайна):

1. Созданная Таблицаполучает имяТаблица1,2,3 и т.д. которое, можно изменить на более адекватное на вкладке Конструктор (Design) . Это имя можно использовать в любых формулах, выпадающих списках и функциях, например в качестве источника данных для сводной таблицы или массива поиска для функции ВПР (VLOOKUP).
2. Созданная один раз Таблицаавтоматически подстраивается в размерах при добавлении или удалении в нее данных. Если дописать к такой Таблице новые строки — она растянется ниже, если добавить новые столбцы — разойдется вширь. В правом нижнем углу Таблицы можно увидеть автоматически перемещающийся маркер границы и, при необходимости, скорректировать его положение мышью:

В шапке Таблицы автоматически включается Автофильтр (можно принудительно отключить на вкладке Данные (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).

В Excel 2003 было что-то отдаленно похожее на такие «умные» таблицы — называлось Списком и создавалось через меню Данные — Список — Создать список (Data — List — Create list) . Но даже половины нынешнего функционала там не было и в помине. В более древних версиях Excel не было и этого.

Создание умной таблицы в Excel

Суть в том, что разрозненные ячейки в диапазоне одного или нескольких столбцов приобретают свойства системы, целостного элемента. Если к столбцу добавить еще одну ячейку, она автоматически принимает форматирование и формулы всего столбца.

Создадим таблицу для вымышленного сайта, где нам нужно посчитать, например, заработанную сумму на кликах по рекламе.

Для начала применим обычное форматирование, чтобы сделать нашу таблицу: а) красивой, б) с единым форматированием. Выделяем любой элемент таблицы (можно просто кликнуть по первой ячейке), кликаем на «Форматировать как таблицу…» и выбираем любой понравившийся формат.

Или же нажимаем комбинацию клавиш Ctrl+T и выбираем стиль оформления из быстрого списка вверху.

Теперь добавленные к таблице новые строки будут автоматически отформатированы. При этом будут автоматически рассчитываться заданные для трех столбцов формулы – общая сумма за общее число кликов.

Кстати, интересная фишка. Когда ваша таблица достигнет больших размеров и не будет помещаться на одной странице, верхняя шапка с наименованиями останется. Вы будете прокручивать таблицу – и всегда видеть наименования столбцов.

2.Форматирование

Если до того, как поумнеть, ваша таблица имела форматирование (рамки, цвета букв и фона и т.п.), то возможно вам стоит это форматирование сбросить, чтобы оно не “конфликтовало” с форматированием умной таблицы. Для этого:

1. Выделите таблицу целиком – проще всего 2 раза нажать Ctrl-A (латинская “A”!)
2. На ленте ГЛАВНАЯ щёлкните Стили ячеек, далее стиль Обычный

При этом все проблемы с форматированием сразу решаются. Однако вам придётся восстанавливать форматы столбцов ячеек: формат даты, времени, нюансы числового формата (типа количества знаков после точки), но это не очень сложно. В любом случае вам решать – сбрасывать форматирование этим способом, либо каким-то другим, менее “разрушительным”, но знать о нём надо.

Создание и ведение таблиц Excel

Итак, таблица в Excel – это прямоугольная область листа, в которой каждая строка представляет собой набор данных, а в ячейке на пересечении данной строки и каждого столбца находится единица данных. Каждому столбцу присваивается уникальное имя. Столбцы таблицы называются полями, а строки – записями. В таблице не может быть записей, в которых нет данных ни в одном поле.

Если на листе Excel выбран прямоугольный диапазон ячеек, его легко преобразовать в таблицу, и, наоборот, с записями таблицы можно работать как с обычным диапазоном ячеек. В таблицах можно использовать функции и формулы Excel , в частности, можно вставить строку (запись), в которой подсчитываются общие или промежуточные итоги.

Создание динамической (умной) таблицы

Как не странно звучит, но в обычной таблице можно создать «Умную» таблицу. Суть ее заключается в большем функционале. В ней можно задавать более интересные формулы, при помощи которых таблица станет автоматизированной.

Создается она простым нажатием на кнопку «Форматировать как таблицу». Далее выбираете дизайн (можете выбрать любой, в будущем его можно будет менять). Многие считают, что эта функция лишь раскрашивает таблицу, но это не так. После этого ваша таблица становится умной.

Работа с динамической таблицей

Спектр функций динамических таблиц огромен, потому мы остановимся на самых основных.

Основной функцией таблицы является задавание формулы расчетов. Для удобства, снова воспользуемся примерами.

Нам нужно узнать выручку за месяц, имея данные расходов и прибыли.

1. Нажимаете на первую ячейку столбца «Выручка» и вписываете туда знак «=» и нажимаете на ячейку «Прибыль».

Ставим знак «-» и нажимаем на столбец «Расходы».

Жмем «Enter» и смотрим результат.

Используя умную таблицу, у нас появляется раздел «Конструктор». В нем тоже присутствует одна интересная функция.

Эта функция называется «Строка итогов». Поставив возле нее галочку, мы получим еще одну строчку, где указаны итоги.

Теперь вы узнали об основных функциях таблицы Excel: разобрались с применением, и научились правильно использовать скрытые и явные функции данной программы. Если на ваши вопросы статья не ответила, напишите нам. Вместе мы найдем ответ.

Настройки Таблицы

В контекстной вкладке Конструктор находятся дополнительные инструменты анализа и настроек.

С помощью галочек в группе Параметры стилей таблиц

можно внести следующие изменения.

— Удалить или добавить строку заголовков

— Добавить или удалить строку с итогами

— Сделать формат строк чередующимися

— Выделить жирным первый столбец

— Выделить жирным последний столбец

— Сделать чередующуюся заливку строк

— Убрать автофильтр, установленный по умолчанию

В видеоуроке ниже показано, как это работает в действии.

В группе Стили таблиц можно выбрать другой формат. По умолчанию он такой как на картинках выше, но это легко изменить, если надо.

В группе Инструменты можно создать сводную таблицу, удалить дубликаты, а также преобразовать в обычный диапазон.

Однако самое интересное – это создание срезов.

Срез – это фильтр, вынесенный в отдельный графический элемент. Нажимаем на кнопку Вставить срез, выбираем столбец (столбцы), по которому будем фильтровать,

и срез готов. В нем показаны все уникальные значения выбранного столбца.

Для фильтрации Таблицы следует выбрать интересующую категорию.

Если нужно выбрать несколько категорий, то удерживаем Ctrl или предварительно нажимаем кнопку в верхнем правом углу, слева от снятия фильтра.

Попробуйте сами, как здорово фильтровать срезами (кликается мышью).

Для настройки самого среза на ленте также появляется контекстная вкладка Параметры. В ней можно изменить стиль, размеры кнопок, количество колонок и т.д. Там все понятно.

Примеры использования умных таблиц

Теперь настало время поговорить о том, в каких ситуациях нужны умные таблицы Excel и о том, какие действия можно предпринять, которые невозможны при работе со стандартным диапазоном.

Предположим, мы имеем таблицу, где приводятся денежные поступления от покупки футболок. В первой колонке расположены имена членов группы, а в других – сколько футболок было продано, и какой их размер. Давайте на примере этой таблицы посмотрим, какие возможные действия можно предпринять, каких нельзя в случае с обычным диапазоном.

Подведение итогов с помощью функционала Excel

На скриншоте, который приводится выше, можно увидеть нашу таблицу. Давайте сначала подведем итоги по всем размерам футболок по отдельности. Если использовать диапазон данных для того, чтобы достичь этой цели, то придется вручную вводить все формулы. Если же создать таблицу, то этой обременительной ноши уже не будет. Достаточно просто включить один пункт, и после этого строка с итогами будет сгенерирована сама.

Далее делается правый клик по любому месту. Появляется всплывающее меню, где есть пункт «Таблица». В нем есть опция «строка итогов», которую и нужно включить. Также ее добавление возможно через конструктор.

Далее в нижней части таблицы появляется строка с итогами. Если открыть выпадающее меню, там можно увидеть следующие настройки:

1. Среднее арифметическое.
2. Количество.
3. Максимум.
4. Смещенное отклонение.

И многое другое. Чтобы получить доступ к функциям, не вошедшим в список выше, нужно нажать по пункту «Другие функции». Здесь удобно то, что диапазон автоматически определяется. Мы выбрали функцию СУММ, потому что в нашем случае надо знать, сколько всего футболок было продано.

Автоматическая вставка формул

Excel – это действительно очень умная программа. Пользователь может даже не знать, что она пытается предугадать его дальнейшие действия. Нами в конец таблицы была добавлена колонка с целью проанализировать результаты продаж по каждому из покупателей. После вставки формулы в первую строку, она сразу копируется во все остальные ячейки, и далее весь столбец становится заполненным нужными нам значениями. Удобно?

Функция сортировки

Очень много людей используют контекстное меню с целью использования той или иной функции. Там есть почти все действия, которые в большинстве случаев нужно выполнять. Если использовать умные таблицы, то функционал еще больше расширяется.

Например, нам нужно проверить, кем уже была переведена предоплата. Для этого необходимо отсортировать данные по первому столбцу. Давайте отформатируем текст таким образом, чтобы была возможность понять, кто уже осуществил оплату, кто нет, и кто не предоставил необходимые для этого документы. Первые будут обозначены зеленым цветом, вторые – красным, а третьи – синим. И предположим, перед нами стоит задача сгруппировать их вместе. 

Причем все Excel может сделать вместо вас. 

Сперва необходимо нажать по выпадающему меню, расположенному возле заглавия столбца «Name» и кликнуть по пункту «Сортировка по цвету» и выбрать красный цвет шрифта.

Все, теперь информация о том, кто совершил оплату, представлена наглядно. 

Фильтрация

Есть также возможность настраивать отображение и скрытие определенной информации таблицы. Например, если необходимо отобразить только тех людей, которые не заплатили, то можно отфильтровать данные по этому цвету. Также возможна фильтрация по другим параметрам.

Основы создания умной таблицы: фото-инструкция для начинающих

Для преобразования обычной таблицы в автоматическую, необходимо выполнить следующий алгоритм действий.

1. Открываем файл в Excel, где имеется нужная таблица, или создаем ее. Можно даже обрабатывать таблицу, не имеющую границ. После чего переходим во вкладку «Главная», в блоке «Стили» находите «Форматировать, как таблицу».

1. Перед вами появляется окошко, в котором указан адрес таблицы, где она будет размещена. Если все верно, подтвердите свое действие кнопкой «ОК».

1. Чтобы удобно было пользоваться оформленной таблицей, вы можете отредактировать ее название и никогда не путаться с данными. Если потребовалось переименовать ее, вам необходимо нажать на любую ячейку в таблице и перейти во вкладку «Конструктор». Найдите блок «Свойства», в котором вы можете не только изменить название таблицы, но и отредактировать ее размер.

Удобство форматирования таблицы по ее размерам заключается в установленном по умолчанию маркере, который позволит растянуть таблицу вниз и вправо без ущерба для внешнего вида и информации.

Форматирование таблицы в Excel

Для настройки формата таблицы в Экселе доступны предварительно настроенные стили. Все они находятся на вкладке “Конструктор” в разделе “Стили таблиц”:

Если 7-ми стилей вам мало для выбора, тогда, нажав на кнопку, в правом нижнем углу стилей таблиц, раскроются все доступные стили. В дополнении к предустановленным системой стилям, вы можете настроить свой формат.

Помимо цветовой гаммы, в меню “Конструктора” таблиц можно настроить:

• Отображение строки заголовков – включает и отключает заголовки в таблице;
• Строку итогов – включает и отключает строку с суммой значений в колонках;
• Чередующиеся строки – подсвечивает цветом чередующиеся строки;
• Первый столбец – выделяет “жирным” текст в первом столбце с данными;
• Последний столбец – выделяет “жирным” текст в последнем столбце;
• Чередующиеся столбцы – подсвечивает цветом чередующиеся столбцы;
• Кнопка фильтра – добавляет и убирает кнопки фильтра в заголовках столбцов.

ПРИЕМ #1. Изменение размера

Обычно умная таблица создается на основе некоторого диапазона ячеек.

При этом размер таблицы Эксель определяет автоматически, основываясь на заполненности ячеек данными.

Если в массиве данных есть пустая строка, то диапазон может быть создан неверно. В этом случае его можно откорректировать вручную. Для этого захватываем мышью нижний правый край таблицы и перемещаем его вниз. При необходимости мы можем уменьшить таблицу, исключив из нее строки точно таким же образом.

Если массив данных значителен, то изменять размеры таблицы мышью будет неудобно. Проще откорректировать размер таблицы вручную с помощью диалогового окна, которое можно открыть через контекстную вкладку Конструктор.

Настройки и возможности умной таблицы в Excel.

Стили таблиц. Умная таблица в Excel.

Первое, что можно сделать, это выбрать стиль умной таблицы. Для этого необходимо левой кнопкой мыши нажать на любую ячейку умной таблицы. После этого появиться закладка, которая называется Конструктор.

В закладке Конструктор есть блок, который называется Стили таблиц. В нем представлено множество готовых вариантов стилей оформления умной таблицы. А так же есть возможность самостоятельно создать стиль умной таблицы.

Кнопка фильтра. Умная таблица в Excel.

Кнопка фильтра, при создании умной таблицы, появляется по умолчание возле каждого заголовка столбца.  Она позволяет фильтровать информацию в умной таблице исходя из задачи. Нажмем на кнопку фильтра в столбце Сотрудник (ФИО) и отфильтруем таблицу по ФИО сотрудника.

Фильтр можно отключить убрав галочку с пункта Кнопка фильтра.

Вставить срез. Умная таблица в Excel.

Данная функция позволяет создать вариант фильтра, который ускоряет и упрощает фильтрацию данных при работе с умной таблицей. Кроме того, за счет визуализации, такой фильтр удобней в работе. Что бы воспользоваться данной функцией необходимо в закладке Конструктор нажать на кнопку Вставить срез.

Появится диалоговое окно Вставка срезов.

В этом диалоговом окне уже есть все возможные варианты для фильтрации умной таблицы. Допустим, нам необходимо отфильтровать таблицу по фамилиям сотрудников. Соответственно в диалоговом окне Вставка срезов ставим галочку напротив пункта Сотрудник (ФИО). Нажимаем ОК.

У нас появился фильтр. Его можно перемещать по Листу, делать больше и меньше. Он работает в двух режимах. Первый режим, когда при нажатие на элемент в списке фильтра (в нашем примере это ФИО сотрудника) в умной таблице будет отображаться только этот элемент и связанная с ним информация (строка). Второй режим, когда при выборе элемента в списке фильтра, можно одновременно выбирать несколько элементов и эти элементы, и связанная с ними информация, будут отображаться в умной таблице. Переключение между этими режимами осуществляется кнопкой в виде списка. Она показана красной стрелкой на картинке выше.

Конечно, данный вариант фильтрации данных более эффективен, если речь идет о больших массивах информации.

Удалять такие фильтры (срезы) можно с помощью контекстного меню, вызванного на самом фильтре. Или кнопкой Delete, предварительно нажав левой кнопкой мыши на удаляемый фильтр.

Чередующиеся строки и чередующиеся столбцы. Умная таблица в Excel.

В закладке Конструктор есть два пункта, которые называются: Чередующиеся строки и Чередующиеся столбцы. По умолчанию, возле пункта Чередующиеся строки стоит галочка, поэтому четные и не четные строки в умной таблице залиты разными цветами. Если галочку убрать, то заливка строк станет одинаковой. Если в пункте Чередующиеся столбцы поставить галочку, то четные и не четные столбцы в умной таблице будут залиты разными цветами. Если галочку не ставить, то заливка столбцов будет одинаковой.

Такая заливка столбцов и строк помогает более эффективно воспринимать большие массивы данных в умной таблице.

Строка итогов. Умная таблица в Excel.

Если поставить галочку напротив пункта Строка итогов, умная таблица добавляет внизу таблицы строку Итог. В этой строке, под каждым столбцом будет доступен выбор разнообразных функции. Например: сумма, среднее, максимум и другие функции. Выбрать необходимую функцию можно нажав на кнопку (стрелочку), которая будет находиться в каждой ячейки строки Итог.  Для каждого столбца, в ячейки Итог, можно выбрать индивидуальную функцию.

Первый столбец и Последний столбец. Умная таблица в Excel.

Если поставить галочку возле пунктов Первый столбец и Последний столбец, умная таблица выделит их цветом.

Это позволит визуально отделить данные столбцы от остальной таблицы.

Имя таблицы. Умная таблица в Excel.

Имя умной таблице можно задать просто написав нужное имя в соответствующее поле — Имя таблицы (закладка Конструктор). Назовем нашу таблицу — Пример.

Теперь, в определенных функциях Excel, можно использовать данное имя таблицы вместо диапазона ячеек.

Структура и ссылки на Таблицу Excel

Каждая Таблица имеет свое название. Это видно во вкладке Конструктор, которая появляется при выделении любой ячейки Таблицы. По умолчанию оно будет «Таблица1», «Таблица2» и т.д.

Если в вашей книге Excel планируется несколько Таблиц, то имеет смысл придать им более говорящие названия. В дальнейшем это облегчит их использование (например, при работе в Power Pivot или Power Query). Я изменю название на «Отчет». Таблица «Отчет» видна в диспетчере имен Формулы → Определенные Имена → Диспетчер имен.

А также при наборе формулы вручную.

Но самое интересное заключается в том, что Эксель видит не только целую Таблицу, но и ее отдельные части: столбцы, заголовки, итоги и др. Ссылки при этом выглядят следующим образом.

=Отчет – на всю Таблицу=Отчет – только на данные (без строки заголовка)=Отчет – только на первую строку заголовков=Отчет – на итоги=Отчет – на всю текущую строку (где вводится формула)=Отчет – на весь столбец «Продажи»=Отчет – на ячейку из текущей строки столбца «Продажи»

Для написания ссылок совсем не обязательно запоминать все эти конструкции. При наборе формулы вручную все они видны в подсказках после выбора Таблицы и открытии квадратной скобки (в английской раскладке).

Выбираем нужное клавишей Tab. Не забываем закрыть все скобки, в том числе квадратную.

Если в какой-то ячейке написать формулу для суммирования по всему столбцу «Продажи»

=СУММ(D2:D8)

то она автоматически переделается в

=Отчет

Т.е. ссылка ведет не на конкретный диапазон, а на весь указанный столбец.

Это значит, что диаграмма или сводная таблица, где в качестве источника указана Таблица Excel, автоматически будет подтягивать новые записи. 

А теперь о том, как Таблицы облегчают жизнь и работу.

Как сделать выпадающий список в Excel?

Начнем с основ. Для того, чтобы создать выпадающий список потребуется список с данными и инструмент «Проверка данных».

Выбираем ячейку, в которой будем создавать выпадающий список. Далее переходим к инструменту «Проверка данных», тип данных – «Список». В поле «Источник» указываем диапазон списка.

Выпадающий список готов!

Такой способ позволяет представить обычный диапазон в виде выпадающего списка. Повторы данных остались в списке (в диапазоне A2:A16 названия городов повторяются и в выпадающем списке они также повторяются). Это, конечно, не удобно. О том, как сделать выпадающий список уникальных значений в Excel мы поговорим далее, пока остановимся на этом варианте.

Таблица и именованный диапазон

Один из аналогов, который позволит преодолеть некоторые из описанных выше ограничений – именованный диапазон. Конечно, это уже несколько другая тема, поскольку таблицы и именованные диапазоны пересекаются в определенной части функционала, но не дублируют его. 

На него также можно ссылаться в формуле, а также обновлять информацию в нем. Правда, последнее придется делать вручную через Диспетчер имен.

Именованные диапазоны могут использоваться как в простых, так и сложных формулах. И в некоторых аспектах могут повторять функционал таблиц. Например, у нас есть такая формула.

= СУММ(E2:E8)+СРЗНАЧ(E2:E8)/5+10/СУММ(E2:E8)

Видим, что здесь один и тот же диапазон (не таблица и не именованный) используется сразу несколько раз. Предположим, нам нужно этот диапазон поменять на какой-то другой. В таком случае изменения придется вносить сразу в трех местах.

Если же этому диапазону присвоить имя или превратить его в таблицу, достаточно просто указать его название один раз, а потом в случае чего просто поменять привязку к определенному диапазону также один раз. 

Настройка Таблицы

Для начала необходимо открыть вкладку «Конструктор», где располагаются параметры таблицы. Настраивать их можно путем добавления или снятия конкретных флажков, расположенных в группе «Параметры стилей таблиц»

Предусмотрены следующие возможности:

1. Добавление или удаление строки заголовков.
2. Добавление или удаление строки с итогами.
3. Сделать строки чередующимися.
4. Выделение крайних колонок жирным начертанием.
5. Включение или отключение чередующейся заливки строк.
6. Отключение автофильтра.

Можно также настроить другой формат. Это можно сделать с помощью опций, расположенных в группе «Стили таблиц». Изначально формат отличается от того, который расположен выше, но в случае чего всегда можно настроить такой внешний вид, какой хочется.

Также можно найти группу «Инструменты», где возможно создание сводной таблицы, удаление копий и преобразование таблицы в стандартный диапазон.

Но наиболее занимательная функция – создание срезов.

Под срезом подразумевается разновидность фильтра, которая выводится в отдельный графический элемент. Чтобы его вставить, необходимо нажать на одноименную кнопку «Вставить срез», после чего выбрать те столбцы, которые необходимо оставить.

Все, теперь появляется панель, где перечислены все уникальные значения, содержащиеся в ячейках этой колонки.

Чтобы отфильтровать таблицу, необходимо выбрать категорию, которая наиболее интересна в данный момент.

Возможен выбор нескольких категорий с помощью среза. Чтобы это сделать, необходимо нажать клавишу Ctrl или перед началом выделения кликнуть по кнопке, расположенной в верхнем правом углу слева от снятия фильтра.

Чтобы выставлять параметры непосредственно на ленте, можно воспользоваться одноименной вкладкой. С ее помощью возможно редактирование разных свойств среза: внешнего вида, размера кнопок, количества и так далее.

ПРИЕМ #2. Строка итогов

Быстро подвести итоги по столбцу значений можно с помощью соответствующей опции вкладки Конструктор. При этом из выпадающего списка можно выбрать одну из стандартных функций, либо сформировать свою формулу.

Аналогичного эффекта можно добиться, установив табличный курсор под ячейку, итоги для которой нужно вывести, и нажав сочетание Alt + =

Подставится формула, вычисляющая промежуточные итоги для столбца значений, находящихся выше. При этом мы можем задать параметры.

В данном случае для нас может быть актуальным, будет ли строка с итогами являться частью таблицы, либо будет ли это ячейка с вычисленным значением, но не относящаяся к таблице. Если мы делаем ячейку частью таблицы, то получаем доступ к меню с возможностью выбора необходимой функции.

Также стоит отметить, что сочетание Alt + = работает не только с таблицами. Аналогичным образом можно вывести промежуточные итоги по любому столбцу с числовыми значениями. В этом случае будет посчитаться сумма всех значений в столбце выше ячейки с формулой.